SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE view [dbo].[vw_acc_invoices_policies_variables_totals]
as


	/*
	DGE 11/03/2016

	Pour les variables CONTRAT d'une quittance, cela renvoie (tf, taxes, ti, cions_tf, cions_taxes, cions_ti)
	- la somme des variables de type PER;
	- la somme des variables de type BPC (frais contrat);
	- la somme des variables, sans tenir compte du type.
	*/

	SELECT	invpolvtot_invoice_id = aipv.invpolv_invoice_id,
			--------------------------------------------
			-- variables
			invpolvtot_tf_per			= sum(	CASE npv.nmpv_type_code
												WHEN 'PER' THEN aipv.invpolv_tf ELSE 0
												end),
			invpolvtot_taxes_per		= sum(	CASE npv.nmpv_type_code
												WHEN 'PER' THEN aipv.invpolv_taxes ELSE 0
												end),
			invpolvtot_ti_per			= sum(CASE npv.nmpv_type_code
												WHEN 'PER' THEN aipv.invpolv_ti ELSE 0
												end),
			invpolvtot_cion_tf_per		= sum(CASE npv.nmpv_type_code
												WHEN 'PER' THEN aipv.invpolv_commission_tf ELSE 0
												end),
			invpolvtot_cion_taxes_per	= sum(CASE npv.nmpv_type_code
												WHEN 'PER' THEN aipv.invpolv_commission_taxes ELSE 0
												end),
			invpolvtot_cion_ti_per		= sum(CASE npv.nmpv_type_code
												WHEN 'PER' THEN aipv.invpolv_commission_ti ELSE 0
												end),
			--------------------------------------------
			-- frais police
			invpolvtot_tf_bpc			= sum(CASE npv.nmpv_type_code
											WHEN 'BPC' THEN aipv.invpolv_tf ELSE 0
											end),
			invpolvtot_taxes_bpc		= sum(CASE npv.nmpv_type_code
											WHEN 'BPC' THEN aipv.invpolv_taxes ELSE 0
											end),
			invpolvtot_ti_bpc			= sum(CASE npv.nmpv_type_code
											WHEN 'BPC' THEN aipv.invpolv_ti ELSE 0
											end),
			invpolvtot_cion_tf_bpc		= sum(CASE npv.nmpv_type_code
											WHEN 'BPC' THEN aipv.invpolv_commission_tf ELSE 0
											end),
			invpolvtot_cion_taxes_bpc	= sum(CASE npv.nmpv_type_code
											WHEN 'BPC' THEN aipv.invpolv_commission_taxes ELSE 0
											end),
			invpolvtot_cion_ti_bpc		= sum(CASE npv.nmpv_type_code
											WHEN 'BPC' THEN aipv.invpolv_commission_ti ELSE 0
											end),
			--------------------------------------------
			-- somme
			invpolvtot_tf				= sum(aipv.invpolv_tf),
			invpolvtot_taxes			= sum(aipv.invpolv_taxes),
			invpolvtot_ti				= sum(aipv.invpolv_ti),
			invpolvtot_cion_tf			= sum(aipv.invpolv_commission_tf),
			invpolvtot_cion_taxes		= sum(aipv.invpolv_commission_taxes),
			invpolvtot_cion_ti			= sum(aipv.invpolv_commission_ti)

	FROM	dbo.acc_invoices_policies_variables aipv
			inner join pol_policies_situations ps on ps.pols_policy_situation_id = aipv.invpolv_policy_situation_id
			INNER JOIN dbo.pr_products_variables ppv ON ppv.prdv_variable_id = aipv.invpolv_variable_id
			INNER JOIN dbo.nm_products_variables npv ON npv.nmpv_variable_code = ppv.prdv_variable_code
			inner join acc_invoices ai on ai.inv_invoice_id = aipv.invpolv_invoice_id and ai.inv_operation_code__last <> 'AN'
	GROUP BY invpolv_invoice_id



GO
