
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_acc_cts_get_chain_params_by_table_id]
	@table_id type_table_id READONLY		-- liste de situations contrat
as
	/*
	DGE 
	Cette procédure renvoie le paramétrage de la chaîne de mise en demeure 
	pour une liste de situations contrat (type_table_id).

	On cherche (en cascade):
		a) dans pr_products_branches;
		b) dans pr_products (code de la chaîne);
		c) dans act_branches (code de la chaîne).
	*/

	set nocount on;
	
	select	ps.pols_policy_situation_id,
			ctsch_mvt_code__fn	= coalesce(pb.prdb_mvt_code__fn, ch2.ctsch_mvt_code__fn, ch.ctsch_mvt_code__fn),
			ctsch_mvt_code__fnr	= coalesce(pb.prdb_mvt_code__fnr, ch2.ctsch_mvt_code__fnr, ch.ctsch_mvt_code__fnr),
			ctsch_mvt_code__cs	= coalesce(pb.prdb_mvt_code__cs, ch2.ctsch_mvt_code__cs, ch.ctsch_mvt_code__cs),
			ctsch_mvt_code__ct	= coalesce(pb.prdb_mvt_code__ct, ch2.ctsch_mvt_code__ct, ch.ctsch_mvt_code__ct),
			ctsch_cts_fee		= coalesce(pb.prdb_cts_fee, ch2.ctsch_cts_fee, ch.ctsch_cts_fee)
	from	pol_policies_situations ps
			inner join pol_policies	p			on	p.pol_policy_id = ps.pols_policy_id
			inner join @table_id t				on	t.id = ps.pols_policy_situation_id 
			inner join pr_products prd			on	prd.prd_product_code = p.pol_product_code 
			inner join act_branches br			on	br.br_actor_id = p.pol_branch_id
			left join cts_chains ch			on	ch.ctsch_chain_code = br.br_chain_code 
			left join cts_chains ch2			on	ch2.ctsch_chain_code = prd.prd_chain_code 

			left join pr_products_branches pb	on	pb.prdb_product_code = p.pol_product_code
												and	pb.prdb_branch_id = p.pol_branch_id
			

GO
