
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_acc_cts_bordereaux_find_by_id]
	@bordereau_id		int,
	@return_details		bit = 0,
	@return_introducers bit	= 0,
	@return_details_count bit = 0
as

	set nocount on;

	-- acc_bordereaux
	select	b.*
	from	acc_cts_bordereaux b
	where	b.ctsb_bordereau_id = @bordereau_id


	-- détails - liste des contrats & quittances
	IF @return_details != 0 
	BEGIN

		if exists (	select	1
					from	acc_cts_bordereaux b
					where	b.ctsb_bordereau_id = @bordereau_id
					and		b.ctsb_type_code in ('PN', 'PR', 'UR') )
		begin
			-- traitement par quittance
			select	icp.act_internal_reference,
					icp.adr_complete_name,
					icp.pols_number,
					icp.inv_operation_code__last, 
					icp.inv_start_date, 
					icp.inv_end_date, 
					icp.inv_invoice_id, 
					a.a_amount
			from	acc_cts_bordereaux_accounts ba
					inner join vw_acc_invoices_clients_policy icp	on icp.inv_invoice_id = ba.ctsa_account_id
					INNER JOIN acc_accounts a						ON a.a_account_id = icp.inv_invoice_id
			where	ba.ctsa_bordereau_id = @bordereau_id

		end
		else
		begin
			-- traitement par contrat
				select	bp.*,

						c.act_internal_reference, 
						c.adr_complete_name,
						s.pols_number

				from	acc_cts_bordereaux_policies bp
						inner join pol_policies	p				on p.pol_policy_id = bp.ctsp_policy_id
						inner join vw_act_clients c				on c.act_actor_id = p.pol_client_id
						inner join pol_policies_situations s	on s.pols_policy_situation_id = p.pol_last_policy_situation_id

				where	bp.ctsp_bordereau_id = @bordereau_id


				-- retourner les quittances
				select	
		
						ctsa_bordereau_id,
						ctsa_policy_id,
						ctsa_account_id,
		
						icp.act_internal_reference,
						icp.adr_complete_name,
						icp.pols_number,
						icp.inv_operation_code__last, 
						icp.inv_start_date, 
						icp.inv_end_date, 
						icp.inv_invoice_id,
						icp.act_actor_id,
						icp.pols_policy_id,
						icp.inv_policy_situation_id,
						a.a_amount

				from	acc_cts_bordereaux_accounts ba
						inner join vw_acc_invoices_clients_policy icp on icp.inv_invoice_id = ba.ctsa_account_id
						inner join acc_accounts a on a.a_account_id = ba.ctsa_account_id
				where	ba.ctsa_bordereau_id = @bordereau_id


				-- retourner les advances
				select	
						ctsa_bordereau_id,
						ctsa_policy_id,
						ctsa_account_id,
				
						v.a_operation_utc_date, 
						a_amount = abs(v.a_amount),
						v.adv_lettering, 
						v.adv_obs
				from	acc_cts_bordereaux_accounts ba
						inner join vw_acc_advances v on v.a_account_id = ba.ctsa_account_id						
				where	ba.ctsa_bordereau_id = @bordereau_id

		end



	END

	-- liste des apporteurs (utilisés pour filtrer)
	IF @return_introducers != 0
	BEGIN

		SELECT	distinct ctsbi_introducer_id, vai.adr_complete_name
		FROM	dbo.acc_cts_bordereaux_introducers bi
				INNER JOIN dbo.vw_act_introducers vai ON vai.int_actor_id = bi.ctsbi_introducer_id
		WHERE	bi.ctsbi_bordereau_id = @bordereau_id
	END


	-- nombre de quittances du bordereau
	IF @return_details_count != 0
	BEGIN

		SELECT	count(*)
		FROM	dbo.acc_cts_bordereaux_accounts acbd
				-- join avec acc_invoices pour ne pas renvoyer les acomptes
				inner join acc_invoices i on i.inv_invoice_id = acbd.ctsa_account_id
		WHERE	acbd.ctsa_bordereau_id = @bordereau_id

	END








GO
