SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[vw_acc_invoices_risks_variables_cies]
AS

	/*
	DGE 08/08/2019
	Détails des quittances avec la répartition de coassurance:
	- pour les quittances qui n'ont pas las coass	=>	acc_invoices_risks_variables
	- pour les quittances coass						=>	acc_invoices_risks_variables_cies
	*/

		SELECT	i.invrisv_invoice_id							AS invoice_id,
				i.invrisv_risk_situation_id						AS risk_situation_id,
				i.invrisv_variable_id							AS variable_id,
				isnull(pv.prdv_company_id, p.pol_company_id)	AS company_id,
				i.invrisv_tf									AS tf,
				i.invrisv_taxes									AS taxes,
				i.invrisv_ti									AS ti,
				i.invrisv_commission_tf							AS commission_tf,
				i.invrisv_commission_taxes						AS commission_taxes,
				i.invrisv_commission_ti							AS commission_ti
		FROM	acc_invoices_risks_variables i
				INNER JOIN ris_risks_situations rs			ON	rs.riss_risk_situation_id = i.invrisv_risk_situation_id
				INNER JOIN pol_policies p					ON	p.pol_policy_id = rs.riss_policy_id
				INNER JOIN pr_products_variables pv			ON	pv.prdv_variable_id = i.invrisv_variable_id 

		WHERE	NOT EXISTS	(
							SELECT	1
							FROM	acc_invoices_risks_variables_cies ir
							WHERE	ir.invrisvc_invoice_id = i.invrisv_invoice_id
							AND		ir.invrisvc_risk_situation_id = i.invrisv_risk_situation_id
							AND		ir.invrisvc_variable_id = i.invrisv_variable_id
							)

		UNION ALL

		SELECT	ir.invrisvc_invoice_id, 
				ir.invrisvc_risk_situation_id,
				ir.invrisvc_variable_id,
				ir.invrisvc_company_id,
				ir.invrisvc_tf,
				ir.invrisvc_taxes,
				ir.invrisvc_ti,
				ir.invrisvc_commission_tf,
				ir.invrisvc_commission_taxes,
				ir.invrisvc_commission_ti
		FROM	acc_invoices_risks_variables_cies ir
GO
