SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_invoice_introducer_variables]
	@invoice_id int,
	@introducer_id int
as

	select	prdv_variable_code, 
			nmpv_l1, nmpv_l2, nmpv_l3, nmpvt_l1, nmpvt_l2, nmpvt_l3,
			invpolvi_value = amount, 
			t2.bdr_operation_code, t2.bdr_bordereau_id,
			vir_commission_sharing_deduced = t.commission_sharing_deduced
	from ( 
				select	variable_id = invpolvi_variable_id,  
						amount = invpolvi_value,
						commission_sharing_deduced = invpolvi_commission_sharing_deduced
				from acc_invoices_policies_variables_introducers i
				where		invpolvi_invoice_id = @invoice_id
						and invpolvi_introducer_id = @introducer_id
						
				union all
						
				select	variable_id = invrisvi_variable_id,  
						amount = invrisvi_value,
						commission_sharing_deduced = invrisvi_commission_sharing_deduced
				from acc_invoices_risks_variables_introducers i
					inner join pr_products_variables p on prdv_variable_id = invrisvi_variable_id
				where		invrisvi_invoice_id = @invoice_id
						and invrisvi_introducer_id = @introducer_id) as t 
			
			inner join pr_products_variables on prdv_variable_id = t.variable_id
			inner JOIN nm_products_variables on prdv_variable_code = nmpv_variable_code
			inner join nm_products_variables_types on nmpv_type_code = nmpvt_type_code
	
	
			-- déterminer si la variable se trouve déjà sur un bordereau APP
			left join	(
						select	*
						from	acc_bordereaux_variables	bv	
								inner join acc_bordereaux	b	on b.bdr_bordereau_id = bv.bdrv_bordereau_id
								inner join acc_accounts		a	on a.a_account_id = b.bdr_bordereau_id				
						)	as t2	on	t2.a_actor_id = @introducer_id
									and	t2.bdrv_invoice_id = @invoice_id
									and	t2.bdrv_variable_id = t.variable_id

	

GO
