
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_acc_cts_bordereaux_details_by_id]
	@bordereau_id int
AS

	/*
	DGE
	
	Cette procédure renvoie les détails d'un bordereau CTX afin de réaliser la fusion 
	des documents.
	
	*/

	set nocount on;

    -- détails du bordereau
	SELECT	pp.pol_product_code, 
			pp.pol_client_id, cli_culture_code, mc.clt_language_code,
			pp.pol_policy_id, pols_policy_situation_id, 
			pp.pol_company_id, 
			ai.inv_invoice_id, 
			pp.pol_branch_id
	FROM	dbo.acc_cts_bordereaux_accounts ba
			INNER JOIN dbo.acc_invoices ai ON ai.inv_invoice_id = ba.ctsa_account_id
			INNER JOIN dbo.pol_policies_situations pps ON pps.pols_policy_situation_id = ai.inv_policy_situation_id
			INNER JOIN dbo.pol_policies pp ON pp.pol_policy_id = pps.pols_policy_id
			INNER JOIN dbo.act_clients ac ON ac.cli_actor_id = pp.pol_client_id
			INNER JOIN dbo.md_cultures mc ON mc.clt_culture_code = ac.cli_culture_code

	where	ba.ctsa_bordereau_id = @bordereau_id
	ORDER BY pp.pol_product_code, pp.pol_client_id, pps.pols_policy_situation_id, pp.pol_company_id, ai.inv_invoice_id


	-- renvoyer les rissk_id & risk_situation_id pour chaque invoice_id 
	select	ba.ctsa_account_id, rss.riss_risk_id, rss.riss_risk_situation_id
	from	acc_cts_bordereaux_accounts ba
			inner join acc_invoices_risks_situations irs on irs.invris_invoice_id = ba.ctsa_account_id
			inner join ris_risks_situations rss on rss.riss_risk_situation_id = irs.invris_risk_situation_id
	where	ba.ctsa_bordereau_id = @bordereau_id


	-- renvoyer les acomptes (si présents), pour la MED

	select	adv.adv_policy_id, adv.adv_advance_id
	from	acc_cts_bordereaux_accounts ba
			inner join acc_advances adv on adv.adv_advance_id = ba.ctsa_account_id
	where	ba.ctsa_bordereau_id = @bordereau_id




GO
