SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_stat_monthly_payments_exports_accountant]
(
	--@currency_code VARCHAR(10) = NULL,  
	@date_de DATETIME = NULL,  
	@date_a DATETIME = NULL  
)
AS

BEGIN;
	
	SET NOCOUNT ON;

		; WITH cte_variables_taxes
		AS	(
				SELECT	invoice_id = invpolvt_invoice_id, 
						tax_id = invpolvt_tax_id,
						tax_value = invpolvt_tax_value
				FROM	acc_invoices_policies_variables_taxes
				UNION ALL
				SELECT	invrisvt_invoice_id, 
						invrisvt_tax_id, 
						invrisvt_tax_value
				FROM	acc_invoices_risks_variables_taxes
			)


		select	[vir_no] = ROW_NUMBER() OVER(ORDER BY a.a_account_id),
				[TYPE] = pmvt.pmvt_l1,
				[vir_debit_note_id] = a.a_account_id,
				[vir_debit_note_date] = inv_db_date,
				[vir_client_name] = cli.adr_complete_name,
				[vir_department_name] = bd.brdep_l1,
				[vir_policy_no] = ps.pols_number,
				[vir_company_name] = cie.adr_complete_name,
				[vir_product] = pr.prd_l1,
				[vir_due_date] = i.inv_start_date,
				[vir_expiry_date] = i.inv_end_date,
				[vir_tf] = tot.invt_tf,
				[vir_stamp_duty] = stamdut.total,
				[vir_vat] = vat.total,
				[vir_ti] = tot.invt_ti,
				[vir_brokerage] = tot.invt_commission_ti,
				[vir_payment_date] = CAST(ISNULL(i.inv_ec_date, i.inv_rmb_date) AS DATE),
				[vir_amount_payd] = ABS(trn.a_amount),
				[vir_currency_code] = a.a_currency_code,
				[vir_payment_mode] = pm.pm_l1,
				[vir_bank_name] = trn.acc_bank_name__branch,
				[IBAN] = trn.acc_account_number__branch
		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_mvts pmvt					ON	pmvt.pmvt_mvt_code = ps.pols_mvt_code
				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 act_branches_departments bd		ON 	bd.brdep_branch_id = p.pol_branch_id
															AND bd.brdep_department_code = p.pol_department_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

				LEFT JOIN	(
							SELECT	bd.bdrd_invoice_id ,CAST(MAX(ab2.a_operation_utc_date) AS DATE) AS date_reversement_cie
							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

									INNER JOIN acc_accounts ab2					ON	ab2.a_actor_id = ab.a_actor_id
																				AND ab2.a_lettering = ab.a_lettering

							WHERE	b.bdr_operation_code = 'P'
							GROUP BY bd.bdrd_invoice_id 
							) AS rcie											ON	rcie.bdrd_invoice_id = i.inv_invoice_id


				INNER JOIN	(
							SELECT invt_invoice_id, invt_tf = SUM(invt_tf), invt_ti = SUM(invt_ti), invt_commission_ti = SUM(invt_commission_ti)
							FROM	vw_acc_invoices_totals
							GROUP BY invt_invoice_id
							) as tot											ON	tot.invt_invoice_id = i.inv_invoice_id


				-- stamp duty
				LEFT JOIN	(

							select	invoice_id, SUM(tax_value) as total
							FROM	cte_variables_taxes vt
									INNER JOIN acc_taxes t ON t.acctax_tax_id = vt.tax_id
							WHERE	t.acctax_tax_code ='STAMDUT'
							GROUP BY invoice_id
							) as stamdut										ON	stamdut.invoice_id = i.inv_invoice_id
				-- TVA
				LEFT JOIN	(
							SELECT	invoice_id, SUM(tax_value) as total
							FROM	cte_variables_taxes vt
									INNER JOIN acc_taxes t ON t.acctax_tax_id = vt.tax_id
							WHERE	t.acctax_tax_code IN ( 'THA_VAT', '-TH_VAT' )
							GROUP BY invoice_id
							) as vat											ON	vat.invoice_id = i.inv_invoice_id

				-- transaction d'encaissement
				LEFT JOIN	vw_acc_transactions trn								ON	trn.a_actor_id = a.a_actor_id
																				AND trn.a_lettering = a.a_lettering
				LEFT JOIN nm_payment_modes pm									ON	pm.pm_payment_code = trn.atrn_payment_code

	
		WHERE	inv_operation_code__last IN ('EC', 'RF', 'RMB', 'S', 'SF')
		AND		ISNULL(inv_ec_date, inv_rmb_date) IS NOT NULL
		AND		CAST(a.a_operation_utc_date AS DATE) BETWEEN ISNULL(@date_de, '01/01/1900') AND ISNULL(@date_a, '01/01/2100')
		AND		inv_an_date IS NULL

END;

GO
