
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_acc_cts_bordereaux_get_merge_params_by_id]
	@bordereau_id		int
as

	set nocount on;
	
	/*
	DGE

	Cette procédure renvoie les paramètres de fusion d'un bordereau CTX:
	- code du produit;
	- document modèle (le chemin rélatif);
	- type de sortie (word, pdf);
	- type de rupture (client, contrat, quittance);
	- code de la numérotation.
	*/


	select	distinct p.pol_product_code,

			vir_document_relative_path =	
				CASE 
				WHEN b.ctsb_type_code = 'PN'	THEN coalesce(ppb.prdb_document_relative_path_pn, cc.ctsch_document_relative_path_pn, '')
				WHEN b.ctsb_type_code = 'PR'	THEN coalesce(ppb.prdb_document_relative_path_pr, cc.ctsch_document_relative_path_pr, '')
				WHEN b.ctsb_type_code = 'UR'	THEN coalesce(ppb.prdb_document_relative_path_ur, cc.ctsch_document_relative_path_ur, '')
				WHEN b.ctsb_type_code = 'FN'	THEN coalesce(ppb.prdb_document_relative_path_fn, cc.ctsch_document_relative_path_fn, '')
				WHEN b.ctsb_type_code = 'FNR' THEN coalesce(ppb.prdb_document_relative_path_fnr, cc.ctsch_document_relative_path_fnr, '')
				WHEN b.ctsb_type_code = 'CS'	THEN coalesce(ppb.prdb_document_relative_path_cs, cc.ctsch_document_relative_path_cs, '')
				WHEN b.ctsb_type_code = 'CT'	THEN coalesce(ppb.prdb_document_relative_path_ct, cc.ctsch_document_relative_path_ct, '')
				ELSE ''
				end,

			vir_output_type_code =
				CASE 
				WHEN b.ctsb_type_code = 'PN'	THEN coalesce(ppb.prdb_output_type_code__pn, cc.ctsch_output_type_code__pn, '')
				WHEN b.ctsb_type_code = 'PR'	THEN coalesce(ppb.prdb_output_type_code__pr, cc.ctsch_output_type_code__pr, '')
				WHEN b.ctsb_type_code = 'UR'	THEN coalesce(ppb.prdb_output_type_code__ur, cc.ctsch_output_type_code__ur, '')
				WHEN b.ctsb_type_code = 'FN'	THEN coalesce(ppb.prdb_output_type_code__fn, cc.ctsch_output_type_code__fn, '')
				WHEN b.ctsb_type_code = 'FNR' THEN coalesce(ppb.prdb_output_type_code__fnr, cc.ctsch_output_type_code__fnr, '')
				WHEN b.ctsb_type_code = 'CS'	THEN coalesce(ppb.prdb_output_type_code__cs, cc.ctsch_output_type_code__cs, '')
				WHEN b.ctsb_type_code = 'CT'	THEN coalesce(ppb.prdb_output_type_code__ct, cc.ctsch_output_type_code__ct, '')
				ELSE ''
				end,

			vir_pagebreak_code =
				CASE 
				WHEN b.ctsb_type_code = 'PN'	THEN coalesce(ppb.prdb_pagebreak_code__pn, cc.ctsch_pagebreak_code__pn, '')
				WHEN b.ctsb_type_code = 'PR'	THEN coalesce(ppb.prdb_pagebreak_code__pr, cc.ctsch_pagebreak_code__pr, '')
				WHEN b.ctsb_type_code = 'UR'	THEN coalesce(ppb.prdb_pagebreak_code__ur, cc.ctsch_pagebreak_code__ur, '')
				WHEN b.ctsb_type_code = 'FN'	THEN coalesce(ppb.prdb_pagebreak_code__fn, cc.ctsch_pagebreak_code__fn, '')
				WHEN b.ctsb_type_code = 'FNR' THEN coalesce(ppb.prdb_pagebreak_code__fnr, cc.ctsch_pagebreak_code__fnr, '')
				WHEN b.ctsb_type_code = 'CS'	THEN coalesce(ppb.prdb_pagebreak_code__cs, cc.ctsch_pagebreak_code__cs, '')
				WHEN b.ctsb_type_code = 'CT'	THEN coalesce(ppb.prdb_pagebreak_code__ct, cc.ctsch_pagebreak_code__ct, '')
				ELSE ''
				end,

			vir_sequence_code =
				CASE 
				WHEN b.ctsb_type_code = 'PN'	THEN coalesce(ppb.prdb_sequence_code__pn, cc.ctsch_sequence_code__pn, '')
				WHEN b.ctsb_type_code = 'PR'	THEN coalesce(ppb.prdb_sequence_code__pr, cc.ctsch_sequence_code__pr, '')
				WHEN b.ctsb_type_code = 'UR'	THEN coalesce(ppb.prdb_sequence_code__ur, cc.ctsch_sequence_code__ur, '')
				WHEN b.ctsb_type_code = 'FN'	THEN coalesce(ppb.prdb_sequence_code__fn, cc.ctsch_sequence_code__fn, '')
				WHEN b.ctsb_type_code = 'FNR' THEN coalesce(ppb.prdb_sequence_code__fnr, cc.ctsch_sequence_code__fnr, '')
				WHEN b.ctsb_type_code = 'CS'	THEN coalesce(ppb.prdb_sequence_code__cs, cc.ctsch_sequence_code__cs, '')
				WHEN b.ctsb_type_code = 'CT'	THEN coalesce(ppb.prdb_sequence_code__ct, cc.ctsch_sequence_code__ct, '')
				ELSE ''
				end,

			vir_mail_code = 
				CASE 
				WHEN b.ctsb_type_code = 'PN'	THEN coalesce(ppb.prdb_mail_code__pn, cc.ctsch_mail_code__pn, '')
				WHEN b.ctsb_type_code = 'PR'	THEN coalesce(ppb.prdb_mail_code__pr, cc.ctsch_mail_code__pr, '')
				WHEN b.ctsb_type_code = 'UR'	THEN coalesce(ppb.prdb_mail_code__ur, cc.ctsch_mail_code__ur, '')
				WHEN b.ctsb_type_code = 'FN'	THEN coalesce(ppb.prdb_mail_code__fn, cc.ctsch_mail_code__fn, '')
				WHEN b.ctsb_type_code = 'FNR' THEN coalesce(ppb.prdb_mail_code__fnr, cc.ctsch_mail_code__fnr, '')
				WHEN b.ctsb_type_code = 'CS'	THEN coalesce(ppb.prdb_mail_code__cs, cc.ctsch_mail_code__cs, '')
				WHEN b.ctsb_type_code = 'CT'	THEN coalesce(ppb.prdb_mail_code__ct, cc.ctsch_mail_code__ct, '')
				ELSE ''
				end

	from	acc_cts_bordereaux_policies bp
			inner join acc_cts_bordereaux b			on	b.ctsb_bordereau_id = bp.ctsp_bordereau_id
			inner join pol_policies p				on	p.pol_policy_id = bp.ctsp_policy_id
			INNER JOIN pr_products_branches ppb		on	ppb.prdb_product_code = p.pol_product_code 
													AND ppb.prdb_branch_id = p.pol_branch_id
			-- recupérer les paramètres par défaut de la chaine CTX
			INNER JOIN dbo.act_branches ab			ON ab.br_actor_id = b.ctsb_branch_id
			LEFT JOIN dbo.cts_chains cc				ON cc.ctsch_chain_code = ab.br_chain_code
	where	bp.ctsp_bordereau_id = @bordereau_id

GO
