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

	set nocount on;

	select	client_name = c.adr_complete_name, 
			ps.pols_number, 
			i.inv_start_date, i.inv_end_date, i.inv_invoice_id,

			tf = tv.tf,
			ttc = tv.ti,
			retro = retro.tf,
			taxes = tv.taxes

			-- net à payer - à calculer au niveau de l'état
	from	acc_bordereaux b
			inner join acc_bordereaux_details bd				on	bd.bdrd_bordereau_id = b.bdr_bordereau_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	(
						select	bdrv_bordereau_id, bdrv_invoice_id, tf = sum(bdrv_tf)
						from	acc_bordereaux_variables 
						group by bdrv_bordereau_id, bdrv_invoice_id
						) as retro	on	retro.bdrv_bordereau_id = bd.bdrd_bordereau_id
									and	retro.bdrv_invoice_id = bd.bdrd_invoice_id


			inner join (
						select	invoice_id, 
								tf = sum(tf), taxes = sum(taxes), ti = sum(ti)
						from	(
								select		invoice_id = invpolv_invoice_id, 
											tf = sum(invpolv_tf),
											taxes = sum(invpolv_taxes),
											ti = sum(invpolv_ti)
								from		acc_invoices_policies_variables
								group by	invpolv_invoice_id

								union

								select		invoice_id = invrisv_invoice_id,
											tf = sum(invrisv_tf),
											taxes = sum(invrisv_taxes),
											ti = sum(invrisv_ti)
								from		acc_invoices_risks_variables
								group by	invrisv_invoice_id
								) as t
						group by invoice_id
						) as tv		on	tv.invoice_id = retro.bdrv_invoice_id
	where	b.bdr_bordereau_id = @bordereau_id



GO
