SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[vw_polris_variables_inv]
AS
	/*
	sélectionner les totaux des variables invoice contrat et risque, par situation contrat
	*/
	select	policy_situation_id, variable_id, tf = sum(tf), taxes = sum(taxes), ti = sum(ti)
	from	(
			select	policy_situation_id = invpolv_policy_situation_id, variable_id = invpolv_variable_id, tf = invpolv_tf, taxes = invpolv_taxes, ti = invpolv_ti
			FROM pol_variables pv
				LEFT JOIN pol_policies_situations_main_invoices ppsmi ON ppsmi.polmi_policy_situation_id = pv.polv_policy_situation_id
				LEFT JOIN acc_invoices_policies_variables aipv ON aipv.invpolv_invoice_id = ppsmi.polmi_invoice_id AND aipv.invpolv_policy_situation_id = ppsmi.polmi_policy_situation_id AND aipv.invpolv_variable_id = pv.polv_variable_id

			union 

			select	policy_situation_id = rrs.riss_policy_situation_id, variable_id = invrisv_variable_id, tf = invrisv_tf, taxes = invrisv_taxes, ti = invrisv_ti
			FROM ris_variables rv
				INNER JOIN ris_risks_situations rrs ON rrs.riss_risk_situation_id = rv.risv_risk_situation_id
				LEFT JOIN ris_risks_situations_main_invoices rrsmi ON rrsmi.rismi_risk_situation_id = rv.risv_risk_situation_id
				LEFT JOIN acc_invoices_risks_variables airv ON airv.invrisv_risk_situation_id = rv.risv_risk_situation_id AND airv.invrisv_variable_id = rv.risv_variable_id AND airv.invrisv_invoice_id = rrsmi.rismi_invoice_id
			
			) as a
	group by policy_situation_id, variable_id


GO
