SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE procedure [dbo].[p_tt_policies_situations]
	@policies_situations_table type_table_id readonly
AS
	SET NOCOUNT on;

	select	pols.*, pol.*, cpl.*, cli.*,clipr.*, bra.*,brapr.*, class.*, sclass.*, prd.*, cie.*,ciepr.*,
			s.*, prf.*,

			pols_policy_situation = RANK() over (order by pols.pols_policy_situation_id), 
			pols_policy_amendment = (	select count(*) from pol_policies_situations x
									where	x.pols_policy_id = pols.pols_policy_id
									and		x.pols_operation_code like 'AMENDMENT%' ),

			bracc.*, bk__adr_complete_name = bnk.adr_complete_name,

			pols_var_all_ann_tf, pols_var_all_ann_taxes, pols_var_all_ann_ti,
			pols_var_fee_ann_tf, pols_var_fee_ann_taxes, pols_var_fee_ann_ti,
			pols_var_per_ann_tf, pols_var_per_ann_taxes, pols_var_per_ann_ti,

			pols_var_bpc_ann_tf,
			pols_var_bpf_ann_tf,
			pols_var_brc_ann_tf,
			pols_var_brf_ann_tf,
			pols_var_cpf_ann_tf,
			pols_var_crf_ann_tf,
							
			pols_var_bpc_ann_taxes,
			pols_var_bpf_ann_taxes,
			pols_var_brc_ann_taxes,
			pols_var_brf_ann_taxes,
			pols_var_cpf_ann_taxes,
			pols_var_crf_ann_taxes,
							
			pols_var_bpc_ann_ti,
			pols_var_bpf_ann_ti,
			pols_var_brc_ann_ti,
			pols_var_brf_ann_ti,
			pols_var_cpf_ann_ti,
			pols_var_crf_ann_ti,

			pols_var_all_inv_tf, pols_var_all_inv_taxes, pols_var_all_inv_ti,
			pols_var_fee_inv_tf, pols_var_fee_inv_taxes, pols_var_fee_inv_ti,
			pols_var_per_inv_tf, pols_var_per_inv_taxes, pols_var_per_inv_ti,

			pols_var_bpc_inv_tf,
			pols_var_bpf_inv_tf,
			pols_var_brc_inv_tf,
			pols_var_brf_inv_tf,
			pols_var_cpf_inv_tf,
			pols_var_crf_inv_tf,
							
			pols_var_bpc_inv_taxes,
			pols_var_bpf_inv_taxes,
			pols_var_brc_inv_taxes,
			pols_var_brf_inv_taxes,
			pols_var_cpf_inv_taxes,
			pols_var_crf_inv_taxes,
							

			pols_var_bpc_inv_ti,
			pols_var_bpf_inv_ti,
			pols_var_brc_inv_ti,
			pols_var_brf_inv_ti,
			pols_var_cpf_inv_ti,
			pols_var_crf_inv_ti,

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


			pol_date_last_fn = fn.fn_date,
			pol_date_last_cs = cs.cs_date,
			pol_date_mvt_prec = prec.pols_mvt_date
			
	from pol_policies_situations pols
		inner join @policies_situations_table t on pols_policy_situation_id = t.id
		inner join pol_policies pol on pol.pol_policy_id = pols.pols_policy_id
		left join pol_cpl cpl on cpl.pcpl_policy_situation_id = pols.pols_policy_situation_id
		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
		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 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
		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)
		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

		-- 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

		left join f_policies_situations_active_invoices_totals(@policies_situations_table) fpsait on fpsait.policy_situation_id =  pols_policy_situation_id

		left join f_policies_situations_annual_totals(@policies_situations_table) fpsat on fpsat.policy_situation_id =  pols_policy_situation_id

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



		-- mise en demeure
		left join	(
					select	ctsbp.ctsp_policy_id, fn_date = max(ctsb.ctsb_reference_date)
					from	acc_cts_bordereaux ctsb
							inner join acc_cts_bordereaux_policies ctsbp on ctsbp.ctsp_bordereau_id = ctsb.ctsb_bordereau_id
					where	ctsb_type_code = 'FN'
					group by ctsbp.ctsp_policy_id
					) as fn on fn.ctsp_policy_id = pol.pol_policy_id

		

		-- suspension suite à la mise en demeure
		left join	(
					select	ctsbp.ctsp_policy_id, cs_date = max(ctsb.ctsb_reference_date)
					from	acc_cts_bordereaux ctsb
							inner join acc_cts_bordereaux_policies ctsbp on ctsbp.ctsp_bordereau_id = ctsb.ctsb_bordereau_id
					where	ctsb_type_code = 'CS'
					group by ctsbp.ctsp_policy_id
					) as cs on cs.ctsp_policy_id = pol.pol_policy_id

		-- avenant précédent 
		left join	pol_policies_situations prec on prec.pols_policy_situation_id = pols.pols_policy_situation_id_old

GO
