SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_acc_bordereaux_select]
	@branch_id			int, 
	@actor_id			int,
	@option				int,
	@bdr_cie_type_code	ais_code_short,			-- (C)ommission, (P)rime, (PC) Prime + Commission
	@db_date__from		date, 
	@db_date__to		date,
	@start_date__from	date,
	@start_date__to		date,
	@ec_date__from		date,
	@ec_date__to		date,
	@table_products		type_table_code_short READONLY,
	@table_variables	type_table_code_short READONLY

as

	SET XACT_ABORT, NOCOUNT ON;

	DECLARE @actor_type_code ais_code_short ;
	
	select	@actor_type_code = a.act_type_code,
			@bdr_cie_type_code = isnull(@bdr_cie_type_code, '')
	from	act_actors a
	where	a.act_actor_id = @actor_id ;
	
	/*	
		Sélectionner les quittances par branche, dates et opérations

		Si les utilisateurs ne spécifient pas des dates, on risque de générer ici pas mal de lignes.
		Cela nous évite d'appliquer plusieurs fois les conditions branche, dates, opérations
		On va optimiser, si nécessaire
	*/

	CREATE TABLE #invoices
	(
		invoice_id int not null primary key
	) ;
	
	
	INSERT INTO #invoices 
	SELECT	distinct i.inv_invoice_id 
	FROM	acc_invoices i
			inner join acc_accounts a on a.a_account_id = i.inv_invoice_id
			inner join pol_policies_situations ps on ps.pols_policy_situation_id = i.inv_policy_situation_id 
			inner join pol_policies p on p.pol_policy_id = ps.pols_policy_id
			inner join @table_products tp on tp.code_short = p.pol_product_code 
	where	a.a_branch_id = @branch_id

	and		inv_operation_code__last in ('S', 'DB', 'EC', 'RMB') 

	--AND		inv_invoice_id = 50021941 
	



	and		
			-- payement direct par la compagnie
			/* pour les bordereaux CIE on a les options: 
				- quittances émises (@optio = 0);
				- quittances encaissées (@option = 1);
				- quittances encaissées directement par la compagnie (@option = 2)

				pour les autres bordereaux (apporteurs & taxation) on a que les options 
				- quittances émises (@optio = 0);
				- quittances encaissées (@option = 1);

			*/
			(
				-- ne pas tester payement direct par la compagnie pour @actor_type_code <> 'CIE'
				@actor_type_code <> 'CIE' 

				OR
				-- tester payement direct par la compagnie uniquement pour @actor_type_code = 'CIE'
				1 =		case
						when @option IN (0 , 1) and not exists	(	select	1
																	from	acc_transactions t
																			inner join nm_payment_modes p on p.pm_payment_code = t.atrn_payment_code
																	where	t.atrn_transaction_id = a.a_lettering
																	and		p.pm_dir != 0 ) then 1

						when @option = 2 and exists				(	select	1
																	from	acc_transactions t
																			inner join nm_payment_modes p on p.pm_payment_code = t.atrn_payment_code
																	where	t.atrn_transaction_id = a.a_lettering
																	and		p.pm_dir != 0 ) then 1
						else 0
						end 
			)
	-- 


	AND		1 =		CASE 

					WHEN @option = 0 AND inv_operation_code__last = 'DB' THEN 1

					-- si bordereau de quittances émises - on affiche la quittance si la transaction n'est pas validée
					WHEN		@option = 0 
							AND inv_operation_code__last IN ('S', 'EC', 'RMB') 
							AND NOT EXISTS	(	SELECT		1
												from	acc_transactions t
														inner join acc_trn_bordereaux_details bd on bd.trnbd_transaction_id = t.atrn_transaction_id
														inner join acc_trn_bordereaux b on b.trnb_bordereau_id = bd.trnbd_bordereau_id
												where	t.atrn_transaction_id = a.a_lettering
												and		b.trnb_tmp = 0 
											) 
										
							AND NOT EXISTS	(	SELECT	1
												FROM	acc_mpay_bordereaux_details bd 
														INNER JOIN acc_mpay_bordereaux b ON b.mpayb_bordereau_id = bd.mpaybd_bordereau_id
												WHERE	bd.mpaybd_invoice_id = a.a_account_id
												AND		b.mpayb_status_code IN ('A', 'C', 'S') -- A = archivé, C = confirmé , S = envoyé
											)

										THEN 1

					WHEN		@option <> 0 
							AND inv_operation_code__last IN ('S', 'EC', 'RMB') 
							AND (

									EXISTS	(
											SELECT	1
											from	acc_transactions t
													inner join acc_trn_bordereaux_details bd on bd.trnbd_transaction_id = t.atrn_transaction_id
													inner join acc_trn_bordereaux b on b.trnb_bordereau_id = bd.trnbd_bordereau_id
											where	t.atrn_transaction_id = a.a_lettering
											and		b.trnb_tmp = 0
											) 
								OR	
									EXISTS	(
											SELECT	1
											FROM	acc_mpay_bordereaux_details bd 
													INNER JOIN acc_mpay_bordereaux b ON b.mpayb_bordereau_id = bd.mpaybd_bordereau_id
											WHERE	bd.mpaybd_invoice_id = a.a_account_id
											AND		b.mpayb_status_code IN ('A', 'C')  -- A = archivé, C = confirmé 
											)
								)THEN 1

					ELSE 0
					END


					
	-- test inv_db_date
	and		i.inv_db_date is not null
	and		( @db_date__from is null or @db_date__from <= i.inv_db_date )
	and		( @db_date__to is null or @db_date__to >= i.inv_db_date )
	-- test inv_start_date
	and		( @start_date__from is null or @start_date__from <= i.inv_start_date )
	and		( @start_date__to is null or @start_date__to >= i.inv_start_date)
	-- test inv_ec_date
	and		( @ec_date__from is null or @ec_date__from <= isnull(i.inv_ec_date, @ec_date__from))
	and		( @ec_date__to is null or @ec_date__to >= isnull(i.inv_ec_date, @ec_date__from))

	;


	/*
	DGE 09/03/2020
	0000971: Quittances soldées & bdx

	Quittances soldées: ne pas sorit les deux quittances (positive et négative) dans le même bordereau
	*/

	; WITH cte_join
	AS	(
			SELECT	t.invoice_id, a.a_actor_id, i.inv_lettering
			FROM	#invoices t
					INNER JOIN acc_accounts a ON a.a_account_id = t.invoice_id
					INNER JOIN acc_invoices i ON i.inv_invoice_id = a.a_account_id
			WHERE	i.inv_lettering IS NOT NULL
		),
	cte_dbl
	AS	(
			SELECT	a_actor_id, inv_lettering
			FROM	cte_join
			GROUP BY a_actor_id, inv_lettering
			HAVING COUNT(*) = 2
		),
	cte_to_delete
	AS	(
			SELECT	j.invoice_id
			FROM	cte_join j
					INNER JOIN cte_dbl d	ON	d.a_actor_id = j.a_actor_id
											AND	d.inv_lettering = j.inv_lettering
					
		)
	DELETE	t
	FROM	#invoices t
			INNER JOIN cte_to_delete d ON d.invoice_id = t.invoice_id



	-- traitement des variables
	DECLARE @result TABLE 
	(
		bdrv_invoice_id				int not null,
		bdrv_variable_id			int not null,
		bdrv_tf						ais_decimal2 null,
		bdrv_taxes					ais_decimal2 null,
		bdrv_taxes_broker			ais_decimal2 null,
		bdrv_ti						ais_decimal2 null,
		bdrv_comm					ais_decimal2 null,
		bdrv_comm_taxes				ais_decimal2 null,
		bdrv_comm_taxes_broker		ais_decimal2 null,
		bdrv_umr_value				varchar(30) null,

		PRIMARY KEY
		(
			bdrv_invoice_id, 
			bdrv_variable_id
		)
	) ;
	
	DECLARE @result_taxes TABLE 
	(
		bdrvtx_invoice_id		int not null,
		bdrvtx_variable_id		int not null,
		bdrvtx_tax_id			int not null,
		bdrvtx_tax_value		ais_decimal2 null,
		bdrvtx_payable_company	bit not null default(0),
		bdrvtx_net_included		bit not null default(0),


		PRIMARY KEY
		(
			bdrvtx_invoice_id, 
			bdrvtx_variable_id,
			bdrvtx_tax_id
		)
	) ;	

	DECLARE @result_comm_taxes TABLE
	(
		bdrvctx_invoice_id		int not null,
		bdrvctx_variable_id		int not null,
		bdrvctx_tax_id			int not null,
		bdrvctx_tax_value		ais_decimal2 null,
		bdrvctx_payable_company	bit not null default(0),

		PRIMARY KEY
		(
			bdrvctx_invoice_id, 
			bdrvctx_variable_id,
			bdrvctx_tax_id
		)
	) ;


	if @actor_type_code = 'CIE' 
	begin



		-- variables contrat
		insert into @result(bdrv_invoice_id, bdrv_variable_id, bdrv_tf, bdrv_taxes, bdrv_taxes_broker, bdrv_ti, bdrv_comm, bdrv_umr_value)
		select	ipv.invoice_id, ipv.variable_id, 
				bdrv_tf			=	case 					
									when @bdr_cie_type_code = 'P' then ipv.tf 
									when @bdr_cie_type_code = 'PC' and ISNULL(v.bdrv_bdr_id__p, 0) = 0 then ipv.tf 
									else 0 
									end,
				bdrv_taxes		=	0,
				bdrv_taxes_broker	=	0,
				invpolv_ti		=	0,
				bdrv_comm		=	case 
									when @bdr_cie_type_code = 'C' then ipv.commission_ti 
									when @bdr_cie_type_code = 'PC' and ISNULL(v.bdrv_bdr_id__c, 0) = 0 then ipv.commission_ti 
									else 0 
									end, 
				bdrv_umr_value	=	polv.polv_umr_value

		from	vw_acc_invoices_policies_variables_cies ipv
				-- pour réduire la sélection
				inner join #invoices i				on	i.invoice_id = ipv.invoice_id 
				inner join pr_products_variables pv on pv.prdv_variable_id = ipv.variable_id 
				inner join @table_variables tv		on tv.code_short = pv.prdv_variable_code 

				-- join pour la récupération de la valeur polv_umr_value
				inner join pol_variables polv		on	polv.polv_policy_situation_id = ipv.policy_situation_id
													and	polv.polv_variable_id = ipv.variable_id

				-- vérifier la présence sur d'autres bordereaux compagnie
				left join vw_acc_bordereaux_variables_bordereaux_cie v	on	v.bdrv_invoice_id = ipv.invoice_id
																		and	v.bdrv_variable_id = ipv.variable_id
																		AND v.a_actor_id = ipv.company_id
		where	ipv.company_id = @actor_id 
		and		1 =		case
						-- bordereau de type "commission", sélectionner la variable si elle n'apparaît pas dans un bdr C ou PC
						when @bdr_cie_type_code = 'C' and ISNULL(v.bdrv_bdr_id__c, 0) = 0 and ISNULL(v.bdrv_bdr_id__pc, 0) = 0 then 1
						-- bordereau de type "prime", sélectionner la variable si elle n'apparaît pas dans un bdr P ou PC
						when @bdr_cie_type_code = 'P' and ISNULL(v.bdrv_bdr_id__p, 0) = 0 and ISNULL(v.bdrv_bdr_id__pc, 0) = 0 then 1
						-- bordereau de type "prime + commission", sélectionner la variable si elle n'apparaît pas dans un bdr PC
						when @bdr_cie_type_code = 'PC' and ISNULL(v.bdrv_bdr_id__pc, 0) = 0 then 1
						else 0
						end

		-- variables des risque
		insert into @result(bdrv_invoice_id, bdrv_variable_id, bdrv_tf, bdrv_taxes, bdrv_taxes_broker, bdrv_ti, bdrv_comm, bdrv_umr_value) 
		select	invoice_id, variable_id, 
				sum(invrisv_tf), sum(invrisv_taxes), sum(invrisv_taxes_broker), sum(invrisv_ti), sum(invrisv_commission_ti), max(risv_umr_value)

		from (		
				select	irv.invoice_id, irv.risk_situation_id, irv.variable_id, 
						invrisv_tf				=	case 					
													when @bdr_cie_type_code = 'P' then irv.tf 
													when @bdr_cie_type_code = 'PC' and ISNULL(v.bdrv_bdr_id__p, 0) = 0 then irv.tf 
													else 0 
													end,
						invrisv_taxes			=	0,
						invrisv_taxes_broker	=	0,
						invrisv_ti				=	0,
						invrisv_commission_ti	=	case 
													when @bdr_cie_type_code = 'C' then irv.commission_ti
													when @bdr_cie_type_code = 'PC' and ISNULL(v.bdrv_bdr_id__c, 0) = 0 then irv.commission_ti
													else 0 
													end, 
						risv_umr_value			=	risv.risv_umr_value
				from	vw_acc_invoices_risks_variables_cies irv

						-- pour réduire la sélection
						inner join #invoices i				on	i.invoice_id = irv.invoice_id 
						inner join pr_products_variables pv on pv.prdv_variable_id = irv.variable_id 
						inner join @table_variables tv		on tv.code_short = pv.prdv_variable_code 

						-- join pour la récupération de la valeur risv_umr_value
						inner join ris_variables risv		on	risv.risv_risk_situation_id = irv.risk_situation_id
															and	risv.risv_variable_id = irv.variable_id

						-- vérifier la présence sur d'autres bordereaux compagnie
						left join vw_acc_bordereaux_variables_bordereaux_cie v	on	v.bdrv_invoice_id = irv.invoice_id
																				and	v.bdrv_variable_id = irv.variable_id
																				AND v.a_actor_id = irv.company_id
						
				where	irv.company_id = @actor_id 
				
				and		1 =		case
								-- bordereau de type "commission", sélectionner la variable si elle n'apparaît pas dans un bdr C ou PC
								when @bdr_cie_type_code = 'C' and ISNULL(v.bdrv_bdr_id__c, 0) = 0 and ISNULL(v.bdrv_bdr_id__pc, 0) = 0 then 1
								-- bordereau de type "prime", sélectionner la variable si elle n'apparaît pas dans un bdr P ou PC
								when @bdr_cie_type_code = 'P' and ISNULL(v.bdrv_bdr_id__p, 0) = 0 and ISNULL(v.bdrv_bdr_id__pc, 0) = 0 then 1
								-- bordereau de type "prime + commission", sélectionner la variable si elle n'apparaît pas dans un bdr PC
								when @bdr_cie_type_code = 'PC' and ISNULL(v.bdrv_bdr_id__pc, 0) = 0 then 1
								else 0
								end 
			) as t

		group by t.invoice_id, t.variable_id

		IF @bdr_cie_type_code <> 'P'
		BEGIN 
				
			-- traitement des commissions 

			WITH cte_comm_invoices_variables
			AS	(
				SELECT	i.invoice_id, r.bdrv_variable_id AS variable_id
				FROM	#invoices i
						INNER JOIN @result r ON r.bdrv_invoice_id = i.invoice_id
				),
			cte_comm_variable_taxes
			AS	(
				SELECT	ct.invoice_id		AS invoice_id,  
						ct.variable_id		AS variable_id, 
						ct.tax_id			AS tax_id, 
						ct.tax_value		AS tax_value
				FROM	cte_comm_invoices_variables i
						INNER JOIN vw_acc_invoices_policies_variables_commission_taxes_cies ct	ON	ct.invoice_id = i.invoice_id
																								AND ct.variable_id = i.variable_id
				WHERE	ct.company_id = @actor_id

				UNION ALL
				-- attention, tous risques confondus
				SELECT	ct.invoice_id, 
						ct.variable_id, 
						ct.tax_id, 
						ct.tax_value
				FROM	cte_comm_invoices_variables i
						INNER JOIN vw_acc_invoices_risks_variables_commission_taxes_cies ct		ON	ct.invoice_id = i.invoice_id
																								AND ct.variable_id = i.variable_id
				WHERE	ct.company_id = @actor_id
				),
			cte_comm_totals 
			AS	(
				SELECT	t.invoice_id, t.variable_id, t.tax_id, SUM(t.tax_value) as tax_value
				FROM	cte_comm_variable_taxes t
				GROUP BY t.invoice_id, t.variable_id, t.tax_id
				)
			INSERT INTO @result_comm_taxes(bdrvctx_invoice_id, bdrvctx_variable_id, bdrvctx_tax_id, bdrvctx_tax_value, bdrvctx_payable_company)
			SELECT	t.invoice_id, t.variable_id, t.tax_id, t.tax_value, pvt.prdvct_payable_company
			FROM	cte_comm_totals t
					INNER JOIN acc_taxes at							ON	at.acctax_tax_id = t.tax_id
					INNER JOIN pr_products_variables pv				ON	pv.prdv_variable_id = t.variable_id
					INNER JOIN pr_products_variables_commission_taxes pvt	ON	pvt.prdvct_variable_id = pv.prdv_variable_id 
																			AND	pvt.prdvct_tax_code = at.acctax_tax_code	
																			AND	pvt.prdvct_branch_id = @branch_id

		END



		if @bdr_cie_type_code = 'C'
		BEGIN
			update	r
			set		r.bdrv_comm_taxes = tc.total,
					r.bdrv_comm_taxes_broker = tc.total_broker

			from	@result r
					
					LEFT JOIN	(
								SELECT	rt.bdrvctx_invoice_id, rt.bdrvctx_variable_id, 
										-- taxes payables par la compagnie
										total = sum(case when bdrvctx_payable_company != 0 then rt.bdrvctx_tax_value else 0 end),
										-- taxes payables par le courtier
										total_broker = sum(case when bdrvctx_payable_company = 0 then rt.bdrvctx_tax_value else 0 end)
								FROM	@result_comm_taxes rt
								GROUP BY rt.bdrvctx_invoice_id, rt.bdrvctx_variable_id
								) as tc		ON	tc.bdrvctx_invoice_id = r.bdrv_invoice_id
											AND tc.bdrvctx_variable_id = r.bdrv_variable_id
		END
		ELSE
		begin

			; 
			WITH cte_invoices_variables
			AS	(
				SELECT	i.invoice_id, r.bdrv_variable_id AS variable_id
				FROM	#invoices i
						INNER JOIN @result r ON r.bdrv_invoice_id = i.invoice_id
				),
			cte_variable_taxes
			AS	(
					SELECT	t.invoice_id, 
							t.variable_id,
							t.tax_id,
							t.tax_value
					FROM	cte_invoices_variables iv
							INNER JOIN vw_acc_invoices_policies_variables_taxes_cies t	ON	t.invoice_id = iv.invoice_id
																						AND	t.variable_id = iv.variable_id
					WHERE	t.company_id = @actor_id

					UNION ALL

					-- attention, tous risques confondus
					SELECT	t.invoice_id, 
							t.variable_id,
							t.tax_id, 
							t.tax_value
					FROM	cte_invoices_variables iv
							INNER JOIN vw_acc_invoices_risks_variables_taxes_cies t		ON	t.invoice_id = iv.invoice_id
																						AND	t.variable_id = iv.variable_id
					WHERE	t.company_id = @actor_id
				),
			cte_totals
			AS	(
					SELECT	c.invoice_id, c.variable_id, c.tax_id, SUM(c.tax_value) AS tax_value
					FROM	cte_variable_taxes c
					GROUP BY c.invoice_id, c.variable_id, c.tax_id
				) 

			INSERT INTO @result_taxes(bdrvtx_invoice_id, bdrvtx_variable_id, bdrvtx_tax_id, bdrvtx_tax_value, bdrvtx_payable_company, bdrvtx_net_included)
			SELECT	t.invoice_id, t.variable_id, t.tax_id, t.tax_value, ISNULL(pvtc.prdvtc_payable_company, pvt.prdvt_payable_company), at.acctax_net_included
			FROM	cte_totals t
					INNER JOIN pr_products_variables pv				ON	pv.prdv_variable_id = t.variable_id
					INNER JOIN acc_taxes at							ON	at.acctax_tax_id = t.tax_id
					INNER JOIN nm_taxes nt							ON	nt.nmtax_tax_code = at.acctax_tax_code
					INNER JOIN pr_products_variables_taxes pvt		ON pvt.prdvt_variable_id = pv.prdv_variable_id 
																	AND	pvt.prdvt_tax_code = at.acctax_tax_code	
																	AND	pvt.prdvt_branch_id = @branch_id
					LEFT JOIN pr_products_variables_taxes_cies pvtc	ON pvtc.prdvtc_variable_id = pvt.prdvt_variable_id
																	AND	pvtc.prdvtc_tax_code = pvt.prdvt_tax_code
																	AND	pvtc.prdvtc_branch_id = pvt.prdvt_branch_id
																	AND pvtc.prdvtc_company_id = pv.prdv_company_id
			WHERE	at.acctax_net_included = 0

			-- MAJ des taxes dans la table @result
			update	r
			set		r.bdrv_taxes = t.total,
					r.bdrv_taxes_broker = t.total_broker,
					r.bdrv_comm_taxes = tc.total,
					r.bdrv_comm_taxes_broker = tc.total_broker,

					r.bdrv_ti = ISNULL(r.bdrv_tf, 0) + ISNULL(t.total, 0) + ISNULL(t.total_broker, 0)

			from	@result r
					LEFT JOIN	(
								select	rt.bdrvtx_invoice_id, rt.bdrvtx_variable_id, 
										-- taxes payables par la compagnie
										total = sum(case when bdrvtx_payable_company != 0 then rt.bdrvtx_tax_value else 0 end),
										-- taxes payables par le courtier
										total_broker = sum(case when bdrvtx_payable_company = 0 then rt.bdrvtx_tax_value else 0 end)
								from	@result_taxes rt
								group by rt.bdrvtx_invoice_id, rt.bdrvtx_variable_id
								) as t	on	t.bdrvtx_invoice_id = r.bdrv_invoice_id
										and	t.bdrvtx_variable_id = r.bdrv_variable_id
					LEFT JOIN	(
								SELECT	rt.bdrvctx_invoice_id, rt.bdrvctx_variable_id, 
										-- taxes payables par la compagnie
										total = sum(case when bdrvctx_payable_company != 0 then rt.bdrvctx_tax_value else 0 end),
										-- taxes payables par le courtier
										total_broker = sum(case when bdrvctx_payable_company = 0 then rt.bdrvctx_tax_value else 0 end)
								FROM	@result_comm_taxes rt
								GROUP BY rt.bdrvctx_invoice_id, rt.bdrvctx_variable_id
								) as tc		ON	tc.bdrvctx_invoice_id = r.bdrv_invoice_id
											AND tc.bdrvctx_variable_id = r.bdrv_variable_id
		end

							
	end


	-- organismes de taxation
	if @actor_type_code = 'TAX' 
	begin


		insert into @result_taxes(bdrvtx_invoice_id, bdrvtx_variable_id, bdrvtx_tax_id, bdrvtx_tax_value)
		select	ipvt.invoice_id, ipvt.variable_id, ipvt.tax_id, 
	
				/*
				0000180: Bdx de taxation - taxes fixes: 
				Pour les taxes fixes, recupérer la valeur qui se trouve au niveau du paramétrage (taxes exprimées en devise différente)
				*/
				case	
				when at.acctax_fixed_value != 0 and len(isnull(at.acctax_payout_currency_code, '')) > 0 then at.acctax_tax_value
				else ipvt.tax_value 
				end
		from	vw_acc_invoices_policies_variables_taxes ipvt
				inner join #invoices i on i.invoice_id = ipvt.invoice_id 
				inner join pr_products_variables pv on pv.prdv_variable_id = ipvt.variable_id 
				inner join @table_variables tv on tv.code_short = pv.prdv_variable_code 
				inner join acc_taxes at on at.acctax_tax_id = ipvt.tax_id 
				inner join nm_taxes nt on nt.nmtax_tax_code = at.acctax_tax_code
				inner join pr_products_variables_taxes pvt	on	pvt.prdvt_variable_id = pv.prdv_variable_id 
															and	pvt.prdvt_tax_code = at.acctax_tax_code	
															and	pvt.prdvt_branch_id = @branch_id

				left join pr_products_variables_taxes_cies pvtc		on	pvtc.prdvtc_variable_id = pvt.prdvt_variable_id
																	and	pvtc.prdvtc_tax_code = pvt.prdvt_tax_code
																	and	pvtc.prdvtc_branch_id = pvt.prdvt_branch_id
																	and pvtc.prdvtc_company_id = pv.prdv_company_id

		where	nt.nmtax_taxent_id = @actor_id 
		and		isnull(pvtc.prdvtc_payable_company, pvt.prdvt_payable_company) = 0
		-- exclure les variables déjà présentes dans d'autres bdx
		and		not exists	(
							select	1
							from	acc_bordereaux_variables bv
									inner join acc_accounts a on a_account_id = bdrv_bordereau_id
									inner join act_actors act on act.act_actor_id = a_actor_id
							where	act_type_code = @actor_type_code
							and		bv.bdrv_invoice_id = ipvt.invoice_id
							and		bv.bdrv_variable_id = ipvt.variable_id)
					
		insert into @result_taxes(bdrvtx_invoice_id, bdrvtx_variable_id, bdrvtx_tax_id, bdrvtx_tax_value)
		select	irvt.invoice_id, irvt.variable_id, irvt.tax_id, sum(irvt.tax_value)
		from	vw_acc_invoices_risks_variables_taxes irvt
				inner join #invoices i on i.invoice_id = irvt.invoice_id 
				inner join pr_products_variables pv on pv.prdv_variable_id = irvt.variable_id 
				inner join @table_variables tv on tv.code_short = pv.prdv_variable_code 
				inner join acc_taxes at on at.acctax_tax_id = irvt.tax_id 
				inner join nm_taxes nt on nt.nmtax_tax_code = at.acctax_tax_code
				inner join pr_products_variables_taxes pvt	on	pvt.prdvt_variable_id = pv.prdv_variable_id 
															and	pvt.prdvt_tax_code = at.acctax_tax_code	

				left join pr_products_variables_taxes_cies pvtc		on	pvtc.prdvtc_variable_id = pvt.prdvt_variable_id
																	and	pvtc.prdvtc_tax_code = pvt.prdvt_tax_code
																	and	pvtc.prdvtc_branch_id = pvt.prdvt_branch_id
																	and pvtc.prdvtc_company_id = pv.prdv_company_id

		where	nt.nmtax_taxent_id = @actor_id 
		and		isnull(pvtc.prdvtc_payable_company, pvt.prdvt_payable_company) = 0
		-- exclure les variables déjà présentes dans d'autres bdx
		and		not exists	(
							select	1
							from	acc_bordereaux_variables bv
									inner join acc_accounts a on a_account_id = bdrv_bordereau_id
									inner join act_actors act on act.act_actor_id = a_actor_id
							where	act_type_code = @actor_type_code
							and		bv.bdrv_invoice_id = irvt.invoice_id
							and		bv.bdrv_variable_id = irvt.variable_id)
		group by irvt.invoice_id, irvt.variable_id, irvt.tax_id


		insert into @result( bdrv_invoice_id, bdrv_variable_id, bdrv_tf, bdrv_taxes, bdrv_ti, bdrv_comm)
		select	bdrvtx_invoice_id, bdrvtx_variable_id, 0,  SUM(bdrvtx_tax_value), 0, 0
		FROM	@result_taxes
		GROUP BY bdrvtx_invoice_id, bdrvtx_variable_id

		
	end	
	
	if @actor_type_code = 'INT' 
	begin
		
		insert	into @result( bdrv_invoice_id, bdrv_variable_id, bdrv_tf, bdrv_taxes, bdrv_ti, bdrv_comm)
		select	ipvi.invpolvi_invoice_id, ipvi.invpolvi_variable_id, SUM(ipvi.invpolvi_value), 0, 0, 0
		from	acc_invoices_policies_variables_introducers ipvi
				inner join #invoices i on i.invoice_id = ipvi.invpolvi_invoice_id
				inner join pr_products_variables pv on pv.prdv_variable_id = ipvi.invpolvi_variable_id
				inner join @table_variables tv on tv.code_short = pv.prdv_variable_code 
		where	ipvi.invpolvi_introducer_id = @actor_id
		and		ipvi.invpolvi_commission_sharing_deduced = 0		-- exclure les variables "rétrocession déduite"
		-- exclure les variables déjà présentes dans d'autres bdx
		and		not exists	(
							select	1
							from	acc_bordereaux_variables bv
									inner join acc_accounts a on a_account_id = bdrv_bordereau_id
									inner join act_actors act on act.act_actor_id = a_actor_id
							where	act_type_code = @actor_type_code
							and		bv.bdrv_invoice_id = ipvi.invpolvi_invoice_id
							and		bv.bdrv_variable_id = ipvi.invpolvi_variable_id)
		group by ipvi.invpolvi_invoice_id, ipvi.invpolvi_variable_id
		

		insert	into @result( bdrv_invoice_id, bdrv_variable_id, bdrv_tf, bdrv_taxes, bdrv_ti, bdrv_comm)
		select	irvi.invrisvi_invoice_id, irvi.invrisvi_variable_id, SUM(irvi.invrisvi_value), 0, 0, 0
		from	acc_invoices_risks_variables_introducers irvi
				inner join #invoices i on i.invoice_id = irvi.invrisvi_invoice_id
				inner join pr_products_variables pv on pv.prdv_variable_id = irvi.invrisvi_variable_id
				inner join @table_variables tv on tv.code_short = pv.prdv_variable_code 
		where	irvi.invrisvi_introducer_id = @actor_id
		and		irvi.invrisvi_commission_sharing_deduced = 0		-- exclure les variables "rétrocession déduite"
		-- exclure les variables déjà présentes dans d'autres bdx
		and		not exists	(
							select	1
							from	acc_bordereaux_variables bv
									inner join acc_accounts a on a_account_id = bdrv_bordereau_id
									inner join act_actors act on act.act_actor_id = a_actor_id
							where	act_type_code = @actor_type_code
							and		bv.bdrv_invoice_id = irvi.invrisvi_invoice_id
							and		bv.bdrv_variable_id = irvi.invrisvi_variable_id)

		group by irvi.invrisvi_invoice_id, irvi.invrisvi_variable_id

	end	


	-- suppression des détails à zéro
	delete	r
	from	@result r
	where	isnull(r.bdrv_comm, 0) = 0
	AND		isnull(r.bdrv_comm_taxes, 0) = 0
	AND		isnull(r.bdrv_comm_taxes_broker, 0) = 0
	and		isnull(r.bdrv_taxes, 0) = 0
	and		isnull(r.bdrv_taxes_broker, 0) = 0
	and		isnull(r.bdrv_tf, 0) = 0
	and		isnull(r.bdrv_ti, 0) = 0

	-- suppression des taxes à zéro
	delete	t
	from	@result_taxes t
	where	not exists(
						select	1
						from	@result v
						where	v.bdrv_invoice_id = t.bdrvtx_invoice_id
						and		v.bdrv_variable_id = t.bdrvtx_variable_id )

	-- suppression des taxes sur la comm à zéro
	delete	t
	from	@result_comm_taxes t
	where	not exists(
						select	1
						from	@result v
						where	v.bdrv_invoice_id = t.bdrvctx_invoice_id
						and		v.bdrv_variable_id = t.bdrvctx_variable_id )
	
	
	-- renvoyer les détails par invoice 
	select	
			-- table acc_bordereaux_details
			bdrd_bordereau_id	= -1,
			bdrd_invoice_id		= t.bdrv_invoice_id,
			bdrd_tf				= t.tf,
			bdrd_taxes			= t.taxes,
			bdrd_taxes_broker	= t.taxes_broker,
			bdrd_ti				= t.ti,
			bdrd_comm			= t.comm,
			bdrd_comm_taxes		= t.comm_taxes,
			bdrd_comm_taxes_broker = t.comm_taxes_broker,
			-- autres information à afficher dans la liste (sur le client).
			-- ces informations seront supprimées avant d'envoyer au serveur la commande de validation 
			i.inv_start_date, i.inv_end_date,i.inv_operation_code__last,
			a.act_internal_reference, adr.adr_complete_name, ps.pols_number, inv_ec_date
			

	from	(
				select	r.bdrv_invoice_id, 
						tf = sum(r.bdrv_tf), 
						taxes = sum(r.bdrv_taxes), taxes_broker = sum(r.bdrv_taxes_broker), ti = sum(r.bdrv_ti), 
						comm = SUM(r.bdrv_comm), comm_taxes = SUM(r.bdrv_comm_taxes), comm_taxes_broker = SUM(bdrv_comm_taxes_broker)
				from	@result r
				group by r.bdrv_invoice_id 
			) as t
			inner join acc_invoices i on i.inv_invoice_id = t.bdrv_invoice_id
			-- recupération des informations concernant le client
			inner join acc_accounts ac on ac.a_account_id = i.inv_invoice_id
			inner join act_actors a on a.act_actor_id = ac.a_actor_id
			inner join adr_addresses adr on adr.adr_address_id = a.act_address_id 				
			inner join pol_policies_situations ps on ps.pols_policy_situation_id = i.inv_policy_situation_id 
	
	
	-- renvoyer les détails par variable
	select	
			--table acc_bordereaux_variables
			bdrv_bordereau_id	= -1,
			bdrv_invoice_id		= r.bdrv_invoice_id,
			bdrv_variable_id	= pv.prdv_variable_id,
			bdrv_tf				= r.bdrv_tf,
			bdrv_taxes			= r.bdrv_taxes,
			bdrv_taxes_broker	= r.bdrv_taxes_broker,
			bdrv_ti				= r.bdrv_ti,
			bdrv_comm			= r.bdrv_comm,
			bdrv_comm_taxes		= r.bdrv_comm_taxes,
			bdrv_comm_taxes_broker = r.bdrv_comm_taxes_broker,
			bdrv_umr_value		= r.bdrv_umr_value,

			-- autres information à afficher dans la liste (sur le client).
			-- ces informations seront supprimées avant d'envoyer au serveur la commande de validation 
			npv.nmpv_variable_code, npv.nmpv_l1, npv.nmpv_l2, npv.nmpv_l3
	from	@result r
			inner join pr_products_variables pv on pv.prdv_variable_id = r.bdrv_variable_id 
			inner join nm_products_variables npv on npv.nmpv_variable_code = pv.prdv_variable_code 
			
	-- renvoyer les détails des taxes par variable
	select	
			--table acc_bordereaux_variables_taxes
			bdrvtx_bordereau_id	= -1,
			bdrvtx_invoice_id	= r.bdrvtx_invoice_id ,
			bdrvtx_variable_id	= r.bdrvtx_variable_id,
			bdrvtx_tax_id		= r.bdrvtx_tax_id,
			bdrvtx_tax_value	= r.bdrvtx_tax_value,
			-- autres information à afficher dans la liste (sur le client).
			-- ces informations seront supprimées avant d'envoyer au serveur la commande de validation 
			nt.nmtax_tax_code, nt.nmtax_l1, nt.nmtax_l2, nt.nmtax_l3, r.bdrvtx_payable_company
	from	@result_taxes r
			inner join acc_taxes at on at.acctax_tax_id = r.bdrvtx_tax_id 
			inner join nm_taxes nt on nt.nmtax_tax_code = at.acctax_tax_code 

	-- renvoyer les détails des taxes par commission, pour chaque variable
	select	
			--table acc_bordereaux_variables_commission_taxes
			bdrvctx_bordereau_id	= -1,
			bdrvctx_invoice_id	= r.bdrvctx_invoice_id ,
			bdrvctx_variable_id	= r.bdrvctx_variable_id,
			bdrvctx_tax_id		= r.bdrvctx_tax_id,
			bdrvctx_tax_value	= r.bdrvctx_tax_value,
			-- autres information à afficher dans la liste (sur le client).
			-- ces informations seront supprimées avant d'envoyer au serveur la commande de validation 
			nt.nmtax_tax_code, nt.nmtax_l1, nt.nmtax_l2, nt.nmtax_l3, r.bdrvctx_payable_company
	from	@result_comm_taxes r
			inner join acc_taxes at on at.acctax_tax_id = r.bdrvctx_tax_id 
			inner join nm_taxes nt on nt.nmtax_tax_code = at.acctax_tax_code 

			
			
	-- renvoyer la structure de la table acc_bordereaux
	select	*
	from	acc_bordereaux
	where	1 = 2 
				
	-- renvoyer la structure de la table acc_bordereaux_products
	select	*
	from	acc_bordereaux_products
	where	1 = 2 	
	

	-- renvoyer la structure de la table acc_accounts
	select	*
	from	acc_accounts
	where	1 = 2 	



GO
