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

	set nocount on;

	select	client_name = c.adr_complete_name, 
			ps.pols_number, 
			nv.nmpv_l1, nv.nmpv_l2, nv.nmpv_l3,
			i.inv_start_date, i.inv_end_date,
			bv.bdrv_ti, -- ttc
			bv.bdrv_comm, -- commission
			ISNULL(bv.bdrv_taxes, 0) + ISNULL(bv.bdrv_comm_taxes, 0) AS bdrv_taxes,							-- taxes
			ISNULL(bv.bdrv_taxes_broker, 0) + ISNULL(bv.bdrv_comm_taxes_broker, 0) AS bdrv_taxes_broker,	-- taxes courtier

			-- net à payer
			ISNULL(bv.bdrv_ti, 0) - ISNULL(bv.bdrv_comm, 0) AS bdrv_net_a_payer

	from	acc_bordereaux b
			INNER JOIN acc_accounts a							ON	a.a_account_id = b.bdr_bordereau_id
			inner join acc_bordereaux_details bd				on	bd.bdrd_bordereau_id = b.bdr_bordereau_id
			inner join acc_bordereaux_variables bv				on	bv.bdrv_bordereau_id = bd.bdrd_bordereau_id
																and	bv.bdrv_invoice_id = bd.bdrd_invoice_id


			inner join acc_invoices i							on	i.inv_invoice_id = bd.bdrd_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 vw_act_clients c							on	c.act_actor_id = p.pol_client_id


			inner join pr_products_variables prdv				on	prdv.prdv_variable_id = bv.bdrv_variable_id
			inner join nm_products_variables nv					on	nv.nmpv_variable_code = prdv.prdv_variable_code
	where	b.bdr_bordereau_id = @bordereau_id







GO
