SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE procedure [dbo].[p_tt_policies_last_situation]
	@policies_table type_table_id readonly,
	@invoices_table type_table_id readonly,
	@advances_table type_table_id readonly
as

	set nocount on;



	select	
			pol.*,								-- table contrat
			pols.*,								-- situation contrat
			cli.*, clipr.*,						-- clients: adr principale & correspondance et facturation
			bra.*, brapr.*,						-- branches: adr principale & correspondance et facturation
			cie.*, ciepr.*,						-- compagnies: adr principale & correspondance et facturation
			prd.*, prf.*,						-- produit, famille de produits
			class.*, sclass.*,					-- classes & sousclasses
			s.*,								-- fractionnement
			bracc.*, bk__adr_complete_name = bnk.adr_complete_name,		-- compte du contrat

			-- totaux des variables contrat par policy_id
			plipolsum.*,
			
			-- totaux des variables contrat par client_id
			plipsumc.*,
			
			-- totaux des variables contrat + risque par policy_id
			pliallsum.*,
			
			-- totaux des variables contrat + risque par client_id
			pliallsmc.*,

			-- totaux des acomptes par policy_id
			plasum.*,

			-- totaux des acomptes par client_id
			plasumc.*,

			pols_next_principal_expiry = case 
											when pols_next_principal_renewal_date is not null then DATEADD(dd,-1,pols_next_principal_renewal_date)
											else pols_end_date
										end,
			ppb.prdb_notice_period


	from	@policies_table t 
			inner join pol_policies pol							on	pol.pol_policy_id = t.id
			inner join pol_policies_situations pols				on	pols.pols_policy_situation_id = pol.pol_last_policy_situation_id

			inner join pr_products prd							on	pol.pol_product_code = prd.prd_product_code
			inner join pr_products_branches prdb					on	prdb.prdb_product_code = prd.prd_product_code
																and	prdb_branch_id = pol.pol_branch_id
			inner join pr_families prf							on	prd.prd_family_code = prf.pf_family_code


			inner join pol_classes class						on	class.cls_class_code = prf.pf_class_code
			inner join pol_subclasses sclass					on	sclass.scls_class_code = prf.pf_class_code
																and sclass.scls_subclass_code = prf.pf_subclass_code
			inner join nm_pol_renew_types_split s				on	s.nmprts_renew_type_code = pols.pols_renew_type_code 
																and s.nmprts_renew_split_code = pols.pols_renew_split_code



			-- adresse clients
			inner join vw_tt_act_clients cli					on pol.pol_client_id = cli.cli__act_actor_id
			inner join vw_tt_act_clients_pr clipr				on clipr__act_actor_id = pol.pol_client_id
			-- adresse entité
			inner join vw_tt_act_branches bra					on pol_branch_id = bra.br__act_actor_id
			inner join vw_tt_act_branches_pr brapr				on pol_branch_id = brapr.brpr__act_actor_id
			-- adresse cie
			inner join vw_tt_act_cies cie						on cie.cie__act_actor_id = pol_company_id -- isnull(prdb_company_id, prd_company_id)
			inner join vw_tt_act_cies_pr ciepr					on ciepr.ciepr__act_actor_id = pol_company_id -- isnull(prdb_company_id, prd_company_id)

			-- compte du contrat

			inner join act_accounts bracc						on	bracc.acc_actor_id = pol.pol_branch_id
																and	bracc.acc_account_number = prdb.prdb_account_number
			inner join vw_act_banks bnk							on bnk.act_actor_id = bracc.acc_bank_id

			-- somme des variables contrat group by policy_id
			left join (		select	plipolsum_policy_id			= ps.pols_policy_id,
									plipolsum_tf_per			= sum(invpolvtot_tf_per),
									plipolsum_taxes_per			= sum(invpolvtot_taxes_per),
									plipolsum_ti_per			= sum(invpolvtot_ti_per),
									plipolsum_cion_tf_per		= sum(invpolvtot_cion_tf_per),
									plipolsum_cion_taxes_per	= sum(invpolvtot_cion_taxes_per),
									plipolsum_cion_ti_per		= sum(invpolvtot_cion_ti_per),
									plipolsum_tf_bpc			= sum(invpolvtot_tf_bpc),
									plipolsum_taxes_bpc			= sum(invpolvtot_taxes_bpc),
									plipolsum_ti_bpc			= sum(invpolvtot_ti_bpc),
									plipolsum_cion_tf_bpc		= sum(invpolvtot_cion_tf_bpc),
									plipolsum_cion_taxes_bpc	= sum(invpolvtot_cion_taxes_bpc),
									plipolsum_cion_ti_bpc		= sum(invpolvtot_cion_ti_bpc),
									plipolsum_tf				= sum(invpolvtot_tf),
									plipolsum_taxes				= sum(invpolvtot_taxes),
									plipolsum_ti				= sum(invpolvtot_ti),
									plipolsum_cion_tf			= sum(invpolvtot_cion_tf),
									plipolsum_cion_taxes		= sum(invpolvtot_cion_taxes),
									plipolsum_cion_ti			= sum(invpolvtot_cion_ti)

							from	vw_acc_invoices_policies_variables_totals ipvt 
									inner join @invoices_table t on t.id = ipvt.invpolvtot_invoice_id
									inner join acc_invoices i on i.inv_invoice_id = t.id
									inner join pol_policies_situations ps on ps.pols_policy_situation_id = i.inv_policy_situation_id
							group by ps.pols_policy_id) as plipolsum on plipolsum.plipolsum_policy_id = pol.pol_policy_id

			-- somme des variables contrat group by client_id plipsumc
			left join (		select	plipsumc_client_id			= a.a_actor_id,
									plipsumc_tf_per				= sum(invpolvtot_tf_per),
									plipsumc_taxes_per			= sum(invpolvtot_taxes_per),
									plipsumc_ti_per				= sum(invpolvtot_ti_per),
									plipsumc_cion_tf_per		= sum(invpolvtot_cion_tf_per),
									plipsumc_cion_taxes_per		= sum(invpolvtot_cion_taxes_per),
									plipsumc_cion_ti_per		= sum(invpolvtot_cion_ti_per),
									plipsumc_tf_bpc				= sum(invpolvtot_tf_bpc),
									plipsumc_taxes_bpc			= sum(invpolvtot_taxes_bpc),
									plipsumc_ti_bpc				= sum(invpolvtot_ti_bpc),
									plipsumc_cion_tf_bpc		= sum(invpolvtot_cion_tf_bpc),
									plipsumc_cion_taxes_bpc		= sum(invpolvtot_cion_taxes_bpc),
									plipsumc_cion_ti_bpc		= sum(invpolvtot_cion_ti_bpc),
									plipsumc_tf					= sum(invpolvtot_tf),
									plipsumc_taxes				= sum(invpolvtot_taxes),
									plipsumc_ti					= sum(invpolvtot_ti),
									plipsumc_cion_tf			= sum(invpolvtot_cion_tf),
									plipsumc_cion_taxes			= sum(invpolvtot_cion_taxes),
									plipsumc_cion_ti			= sum(invpolvtot_cion_ti)

							from	vw_acc_invoices_policies_variables_totals ipvt 
									inner join @invoices_table t on t.id = ipvt.invpolvtot_invoice_id
									inner join acc_accounts a on a.a_account_id = t.id
							group by a.a_actor_id) as plipsumc on plipsumc.plipsumc_client_id = pol.pol_client_id

			-- somme de toutes les variables (contrat et risque) by policy_id pliallsum
			left join (		select	pliallsum_policy_id = ps.pols_policy_id,
									pliallsum_amount = isnull(sum(a_amount), 0), 
									pliallsum_cts_fee = isnull(sum(i.inv_cts_fee), 0)
							from	acc_invoices i
									inner join pol_policies_situations ps on ps.pols_policy_situation_id = i.inv_policy_situation_id
									inner join @invoices_table t on t.id = i.inv_invoice_id
									inner join acc_accounts a on a.a_account_id = i.inv_invoice_id
							group by ps.pols_policy_id) as pliallsum on pliallsum.pliallsum_policy_id = pol.pol_policy_id

			-- somme de toutes les variables (contrat et risque) by client_id pliallsmc
			left join (		select	pliallsmc_client_id = a.a_actor_id,
									pliallsmc_amount = isnull(sum(a.a_amount), 0), 
									pliallsmc_cts_fee = isnull(sum(i.inv_cts_fee),0)
							from	acc_invoices i
									inner join @invoices_table t on t.id = i.inv_invoice_id
									inner join acc_accounts a on a.a_account_id = i.inv_invoice_id
							group by a.a_actor_id) as pliallsmc on pliallsmc.pliallsmc_client_id = pol.pol_client_id

			-- somme des acomptes by policy_id plasum
			left join (		select	plasum_policy_id =  adv_policy_id, 
									plasum_amount = isnull(sum(a.a_amount), 0)
							from	@advances_table t 
									inner join acc_advances adv on adv.adv_advance_id = t.id
									inner join acc_accounts a on a.a_account_id = t.id
							where	adv_policy_id is not null
							group by adv_policy_id) as plasum on plasum.plasum_policy_id = pol.pol_policy_id

			-- somme des acomptes by client_id plasum
			left join (		select	plasumc_client_id =  a_actor_id, 
									plasumc_amount = isnull(sum(a.a_amount), 0)
							from	@advances_table t 
									inner join acc_advances adv on adv.adv_advance_id = t.id
									inner join acc_accounts a on a.a_account_id = t.id
									inner join @policies_table p on p.id = adv_policy_id
							group by a_actor_id) as plasumc on plasumc_client_id = pol.pol_client_id

			left join pr_products_branches ppb on		ppb.prdb_product_code = pol_product_code
													and ppb.prdb_branch_id = pol_branch_id




GO
