SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_stat_outstanding_company]
	@currency_code VARCHAR(10) = NULL,
	@date_extraction DATETIME = NULL
AS
BEGIN 
	SET NOCOUNT ON;


	SELECT	[Devise]					= a.a_currency_code, 
			[Département]				= p.pol_department_code,
			[Compagnie]					= cie.adr_name, 
			[Famille de produit]		= f.pf_family_code,
			[ID Quittance]				= i.inv_invoice_id,
			[Numéro de police]			= ps.pols_number,
			[ID Risque]					= r.riss_number,
			[Nom]						= cli.adr_complete_name,
			[Début d'effet]				= CAST(i.inv_start_date AS DATE),
			[Fin d'effet]				= CAST(i.inv_end_date AS DATE),
			[Date encaissement]			= CAST(ISNULL(i.inv_ec_date, i.inv_rmb_date) AS DATE),
			[Prime TTC]					= a.a_amount,
			[Prime nette]				= tf.total,
			[Taxes]						= tx.total,
			[Commissions]				= cions.total,
			[Frais de gestion]			= fg.total,
			[Référence bdr Cie]			= rcie.ref_bdr_cie/*, 
			[Statut bdr Cie]			= rcie.bdr_operation_code
			*/
 
	FROM	acc_invoices i
			INNER JOIN acc_accounts a					ON	a.a_account_id = i.inv_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 pr_products pr					ON	pr.prd_product_code = p.pol_product_code
			INNER JOIN pr_families f					ON	f.pf_family_code = pr.prd_family_code
		
			INNER JOIN vw_act_cies cie					ON	cie.act_actor_id = p.pol_company_id
			INNER JOIN vw_act_clients cli				ON	cli.act_actor_id = p.pol_client_id

			OUTER APPLY	(
						SELECT	TOP (1) b.bdr_bordereau_id, b.bdr_reference AS ref_bdr_cie, b.bdr_operation_code
						FROM	acc_bordereaux b
								INNER JOIN acc_accounts ab					ON	ab.a_account_id = b.bdr_bordereau_id
								INNER JOIN acc_bordereaux_companies bc		ON	bc.bdrcie_bordereau_id = b.bdr_bordereau_id
								INNER JOIN acc_bordereaux_details bd		ON	bd.bdrd_bordereau_id = b.bdr_bordereau_id

						WHERE	bd.bdrd_invoice_id = i.inv_invoice_id
						ORDER BY b.bdr_bordereau_id DESC
						) AS rcie

			-- prime nette
			INNER JOIN	(
						SELECT	invt_invoice_id, SUM(invt_tf) as total
						FROM	vw_acc_invoices_totals
						GROUP BY invt_invoice_id
						) as tf												ON	tf.invt_invoice_id = i.inv_invoice_id
			-- taxes
			INNER JOIN	(
						SELECT	invt_invoice_id, SUM(invt_taxes) as total
						FROM	vw_acc_invoices_totals
						GROUP BY invt_invoice_id
						) as tx												ON	tx.invt_invoice_id = i.inv_invoice_id
			-- commissions
			INNER JOIN	(
						SELECT	invt_invoice_id, SUM(invt_commission_ti) as total
						FROM	vw_acc_invoices_totals
						GROUP BY invt_invoice_id
						) as cions											ON	cions.invt_invoice_id = i.inv_invoice_id
			-- frais de gestion (Frais courtier contrat BPC, Frais courtier risque BRC, Honoraire courtier risque BPF, Honoraire courtier contrat BRF)
			LEFT JOIN	(
						SELECT	invt_invoice_id, SUM(invt_tf) as total
						FROM	vw_acc_invoices_totals
						WHERE	invt_variable_type_code IN ('BPC', 'BRC', 'BPF', 'BRF')
						GROUP BY invt_invoice_id
						) as fg											ON	fg.invt_invoice_id = i.inv_invoice_id
						
			LEFT JOIN 	(
						SELECT	ir.invris_invoice_id, 
								CASE 
								WHEN MIN(rs.riss_number) != MAX(rs.riss_number) THEN '-'
								ELSE MIN(rs.riss_number)
								END AS riss_number
						FROM	acc_invoices_risks_situations ir
								INNER JOIN ris_risks_situations rs ON rs.riss_risk_situation_id = ir.invris_risk_situation_id
						GROUP BY ir.invris_invoice_id
						) AS r 											ON 	r.invris_invoice_id = i.inv_invoice_id

							
	WHERE	a.a_currency_code = @currency_code
	AND		inv_operation_code__last IN ('EC', 'RMB')
	AND		inv_db_date IS NOT NULL AND inv_db_date BETWEEN '01/01/1900' AND ISNULL(@date_extraction, '01/01/2100')
	AND		inv_an_date IS NULL
	AND		ISNULL(rcie.bdr_operation_code , '') <> 'P'
	--AND		inv_rmb_date IS NULL 

	ORDER BY	[Département], 
				[Compagnie], 
				[ID Quittance]



END
GO
