SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_acc_invoices_find_by_table_id]
	@table_id type_table_id READONLY
as 
	set nocount on;
	
	select	a.*, ac.cur_use_decimals
	from	acc_accounts a
			inner join @table_id t on t.id = a.a_account_id
			inner join acc_currencies ac on ac.cur_currency_code = a.a_currency_code
	where	a.a_type_code = 'I'

	SELECT	i.*
	FROM	acc_invoices i
			INNER JOIN acc_accounts a on a.a_account_id = i.inv_invoice_id 
			INNER JOIN act_branches b on b.br_actor_id = a.a_branch_id 
			INNER JOIN @table_id t on t.id = i.inv_invoice_id 
	
	SELECT	o.*, 
			u.usr_name, 
			nmo.op_l1, nmo.op_l2, nmo.op_l3
	FROM	acc_invoices_operations o
			INNER JOIN nm_invoices_operations nmo on nmo.op_operation_code = o.invop_operation_code 
			INNER JOIN usr_users u on u.usr_user_code = o.invop_user_code 
			INNER JOIN @table_id t on t.id = o.invop_invoice_id 
	
	SELECT	i.*, n.*
	FROM	vw_acc_invoices_totals i 
			INNER JOIN nm_products_variables_types n on n.nmpvt_type_code = i.invt_variable_type_code
			INNER JOIN @table_id t on t.id = i.invt_invoice_id 
	
	-- policies	
	SELECT  i.*, n.*
	FROM    vw_acc_invoices_policies_situations_totals i
			INNER JOIN nm_products_variables_types n on n.nmpvt_type_code = i.invpolt_variable_type_code
			INNER JOIN @table_id t on t.id = i.invpolt_invoice_id 
	
	SELECT  a.*, n.*,
			-- bordereau de type "prime"
			invpolv_used_bordereau__p	= convert(bit, isnull(vabvbc.bdrv_bdr_id__p, 0)),
			invpolv_bordereau_id__p		= vabvbc.bdrv_bdr_id__p,
			-- bordereau de type "commission"
			invpolv_used_bordereau__c	= convert(bit, isnull(vabvbc.bdrv_bdr_id__c, 0)),
			invpolv_bordereau_id__c		= vabvbc.bdrv_bdr_id__c,
			-- bordereau de type "prime et commissions déduites"
			invpolv_used_bordereau__pc	= convert(bit, isnull(vabvbc.bdrv_bdr_id__pc, 0)),
			invpolv_bordereau_id__pc	= vabvbc.bdrv_bdr_id__pc
	FROM    acc_invoices_policies_variables a
			INNER JOIN pr_products_variables p on p.prdv_variable_id = a.invpolv_variable_id
			INNER JOIN nm_products_variables n on n.nmpv_variable_code = p.prdv_variable_code 
			INNER JOIN @table_id t on t.id = a.invpolv_invoice_id 

			LEFT JOIN dbo.vw_acc_bordereaux_variables_bordereaux_cie vabvbc ON	vabvbc.bdrv_invoice_id = a.invpolv_invoice_id
																			AND	vabvbc.bdrv_variable_id = a.invpolv_variable_id
	
	SELECT  a.*, n.*
	FROM    acc_invoices_policies_variables_taxes a
			INNER JOIN acc_taxes t on t.acctax_tax_id = a.invpolvt_tax_id 
			INNER JOIN nm_taxes n on n.nmtax_tax_code = t.acctax_tax_code 
			INNER JOIN @table_id ti on ti.id = a.invpolvt_invoice_id 
	
	SELECT  a.*, v.act_internal_reference, v.adr_name, v.adr_complete_name
	FROM    acc_invoices_policies_variables_introducers a
			inner join vw_act_introducers v on v.act_actor_id = a.invpolvi_introducer_id 
			INNER JOIN @table_id t on t.id = a.invpolvi_invoice_id 

	SELECT	a.*,nt.*
	FROM	acc_invoices_policies_variables_commission_taxes a
			inner join @table_id t on t.id = a.invpolvct_invoice_id
			INNER JOIN acc_taxes acct on acct.acctax_tax_id = a.invpolvct_tax_id 
			INNER JOIN nm_taxes nt on nt.nmtax_tax_code = acct.acctax_tax_code 
	
	-- risks
	SELECT  a.*, r.riss_number 
	FROM    acc_invoices_risks_situations a
			INNER JOIN ris_risks_situations r on r.riss_risk_situation_id = a.invris_risk_situation_id 
			INNER JOIN @table_id t on t.id = a.invris_invoice_id 
	
	SELECT  i.*, n.*
	FROM    vw_acc_invoices_risks_situations_totals i
			INNER JOIN nm_products_variables_types n on n.nmpvt_type_code = i.invrist_variable_type_code
			INNER JOIN @table_id t on t.id = i.invrist_invoice_id 
	
	SELECT  a.*, n.*,
			-- bordereau de type "prime"
			invrisv_used_bordereau__p	= convert(bit, isnull(vabvbc.bdrv_bdr_id__p, 0)),
			invrisv_bordereau_id__p		= vabvbc.bdrv_bdr_id__p,
			-- bordereau de type "commission"
			invrisv_used_bordereau__c	= convert(bit, isnull(vabvbc.bdrv_bdr_id__c, 0)),
			invrisv_bordereau_id__c		= vabvbc.bdrv_bdr_id__c,
			-- bordereau de type "prime et commissions déduites"
			invrisv_used_bordereau__pc	= convert(bit, isnull(vabvbc.bdrv_bdr_id__pc, 0)),
			invrisv_bordereau_id__pc	= vabvbc.bdrv_bdr_id__pc
	FROM    acc_invoices_risks_variables a
			INNER JOIN pr_products_variables p on p.prdv_variable_id = a.invrisv_variable_id
			INNER JOIN nm_products_variables n on n.nmpv_variable_code = p.prdv_variable_code 
			INNER JOIN @table_id t on t.id = a.invrisv_invoice_id 

			INNER JOIN ris_risks_situations rs		ON rs.riss_risk_situation_id = a.invrisv_risk_situation_id
			INNER JOIN pol_policies pol				ON pol.pol_policy_id = rs.riss_policy_id

			LEFT JOIN dbo.vw_acc_bordereaux_variables_bordereaux_cie vabvbc ON	vabvbc.bdrv_invoice_id = a.invrisv_invoice_id
																			AND	vabvbc.bdrv_variable_id = a.invrisv_variable_id
																			AND	vabvbc.a_actor_id = pol.pol_company_id

	
	SELECT  a.*, n.*
	FROM    acc_invoices_risks_variables_taxes a
			INNER JOIN acc_taxes t on t.acctax_tax_id = a.invrisvt_tax_id  
			INNER JOIN nm_taxes n on n.nmtax_tax_code = t.acctax_tax_code 
			INNER JOIN @table_id ti on ti.id = a.invrisvt_invoice_id 
	
	SELECT  a.*, v.act_internal_reference, v.adr_name, v.adr_complete_name
	FROM    acc_invoices_risks_variables_introducers a
			inner join vw_act_introducers v on v.act_actor_id = a.invrisvi_introducer_id 
			INNER JOIN @table_id t on t.id = a.invrisvi_invoice_id 

	SELECT	a.*, nt.*
	FROM	acc_invoices_risks_variables_commission_taxes a
			INNER JOIN @table_id t on t.id = a.invrisvct_invoice_id
			INNER JOIN acc_taxes acct on acct.acctax_tax_id = a.invrisvct_tax_id 
			INNER JOIN nm_taxes nt on nt.nmtax_tax_code = acct.acctax_tax_code 
	

	-- coassurance
	--select	vc.*, c.act_internal_reference, c.adr_complete_name
	--from	acc_invoices_policies_variables_cies vc
	--		inner join @table_id t on t.id = vc.invpolvc_invoice_id
	--		inner join vw_act_cies c on c.act_actor_id = vc.invpolvc_company_id

	--select	vc.*, c.act_internal_reference, c.adr_complete_name
	--from	acc_invoices_policies_variables_taxes_cies vc
	--		inner join @table_id t on t.id = vc.invpolvtc_invoice_id
	--		inner join vw_act_cies c on c.act_actor_id = vc.invpolvtc_company_id

	--select	vc.*, c.act_internal_reference, c.adr_complete_name
	--from	acc_invoices_policies_variables_commission_taxes_cies vc
	--		inner join @table_id t on t.id = vc.invpolvctc_invoice_id
	--		inner join vw_act_cies c on c.act_actor_id = vc.invpolvctc_company_id

	select	vc.*, c.act_internal_reference, c.adr_complete_name
	from	acc_invoices_risks_variables_cies vc
			inner join @table_id t on t.id = vc.invrisvc_invoice_id
			inner join vw_act_cies c on c.act_actor_id = vc.invrisvc_company_id

	--select	vc.*, c.act_internal_reference, c.adr_complete_name
	--from	acc_invoices_risks_variables_taxes_cies vc
	--		inner join @table_id t on t.id = vc.invrisvtc_invoice_id
	--		inner join vw_act_cies c on c.act_actor_id = vc.invrisvtc_company_id

	--select	vc.*, c.act_internal_reference, c.adr_complete_name
	--from	acc_invoices_risks_variables_commission_taxes_cies vc
	--		inner join @table_id t on t.id = vc.invrisvctc_invoice_id
	--		inner join vw_act_cies c on c.act_actor_id = vc.invrisvctc_company_id

	-- bordereaux mensualisation
	select	distinct b.*, bnk.adr_complete_name, d.mpaybd_invoice_id
	from	acc_mpay_bordereaux b
			inner join vw_act_banks bnk on bnk.act_actor_id = b.mpayb_bank_id
			inner join acc_mpay_bordereaux_details d on d.mpaybd_bordereau_id = b.mpayb_bordereau_id
			inner join @table_id t on t.id = d.mpaybd_invoice_id
	order by b.mpayb_date desc





GO
