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

	set nocount on;
	
	-- acc_bordereaux, acc_accounts
	select	b.*, a.*, bdr_net = a.a_amount, aa.a_actor_id, act.act_type_code
	from	acc_bordereaux b
			inner join acc_accounts a on a.a_account_id = b.bdr_bordereau_id
			inner join vw_acc_account_actors aa on aa.a_account_id = a.a_account_id
			inner join act_actors act on act.act_actor_id = a.a_actor_id
	where	b.bdr_bordereau_id = @bordereau_id ;

	-- acc_bordereaux_products
	select	bdrp_bordereau_id,
			p.prd_product_code, p.prd_l1, p.prd_l2, p.prd_l3
	from	acc_bordereaux_products bp
			inner join pr_products p on p.prd_product_code = bp.bdrp_product_code
	where	bp.bdrp_bordereau_id = @bordereau_id ; 

	-- vw_acc_bordereaux_variables_totals
	select	bdrvt_bordereau_id,
			pv.nmpv_variable_code, pv.nmpv_l1, pv.nmpv_l2, pv.nmpv_l3,
			bvt.bdrvt_tf, bvt.bdrvt_taxes, bvt.bdrvt_taxes_broker, bvt.bdrvt_ti, 
			bvt.bdrvt_comm, bvt.bdrvt_comm_taxes, bvt.bdrvt_comm_taxes_broker
	from	vw_acc_bordereaux_variables_totals bvt
			inner join nm_products_variables pv on pv.nmpv_variable_code = bvt.bdrvt_variable_code 
	where	bvt.bdrvt_bordereau_id = @bordereau_id ; 
	
	-- acc_bordereaux_details
	select	
			-- table acc_bordereaux_details
			bd.bdrd_bordereau_id,
			bd.bdrd_invoice_id,
			bd.bdrd_tf,
			bd.bdrd_taxes,
			bd.bdrd_taxes_broker,
			bd.bdrd_ti,
			bd.bdrd_comm,
			bd.bdrd_comm_taxes,
			bd.bdrd_comm_taxes_broker,
			-- autres information à afficher dans la liste (sur le client).
			i.inv_start_date, i.inv_end_date,i.inv_operation_code__last,
			a.act_internal_reference, adr.adr_complete_name, ps.pols_number
	from	acc_bordereaux_details bd
			inner join acc_invoices i on i.inv_invoice_id = bd.bdrd_invoice_id 
			-- recupérer les informations concernant le client
			inner join acc_accounts acc on acc.a_account_id = i.inv_invoice_id 
			inner join act_actors a on a.act_actor_id = acc.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 
	where	bd.bdrd_bordereau_id = @bordereau_id ;
			
			
	-- acc_bordereaux_variables
	select	
			--table acc_bordereaux_variables
			bdrv_bordereau_id,
			bdrv_invoice_id,
			bdrv_variable_id,
			bdrv_tf,
			bdrv_taxes,
			bdrv_taxes_broker,
			bdrv_ti,
			bdrv_comm,
			bdrv_comm_taxes,
			bdrv_comm_taxes_broker,
			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	acc_bordereaux_variables bv
			inner join pr_products_variables pv on pv.prdv_variable_id = bv.bdrv_variable_id 
			inner join nm_products_variables npv on npv.nmpv_variable_code = pv.prdv_variable_code 
	where	bv.bdrv_bordereau_id = @bordereau_id ;
	
	-- acc_bordereaux_variables_taxes
	select	
			--table acc_bordereaux_variables_taxes
			bdrvtx_bordereau_id,
			bdrvtx_invoice_id,
			bdrvtx_variable_id,
			bdrvtx_tax_id,
			bdrvtx_tax_value,
			bdrvtx_payable_company,
			-- autres information à afficher dans la liste (sur le client).
			nt.nmtax_tax_code, nt.nmtax_l1, nt.nmtax_l2, nt.nmtax_l3
	from	acc_bordereaux_variables_taxes bvtx
			inner join acc_taxes at on at.acctax_tax_id = bvtx.bdrvtx_tax_id 
			inner join nm_taxes nt on nt.nmtax_tax_code = at.acctax_tax_code 
	where	bvtx.bdrvtx_bordereau_id = @bordereau_id ;

	SELECT	--table acc_bordereaux_variables_taxes
			bdrvctx_bordereau_id,
			bdrvctx_invoice_id,
			bdrvctx_variable_id,
			bdrvctx_tax_id,
			bdrvctx_tax_value,
			bdrvctx_payable_company, 
			-- autres information à afficher dans la liste (sur le client).
			nt.nmtax_tax_code, nt.nmtax_l1, nt.nmtax_l2, nt.nmtax_l3

	FROM	acc_bordereaux_variables_commission_taxes bvctx
			inner join acc_taxes at on at.acctax_tax_id = bvctx.bdrvctx_tax_id 
			inner join nm_taxes nt on nt.nmtax_tax_code = at.acctax_tax_code 
	where	bvctx.bdrvctx_bordereau_id = @bordereau_id;
			

	-- pour les bordereaux compagnie
	SELECT	*
	FROM	dbo.acc_bordereaux_companies abc
	WHERE	abc.bdrcie_bordereau_id = @bordereau_id ;
















GO
