SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[vw_acc_invoices_policies_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_policies_variables
	- pour les quittances coass						=>	acc_invoices_policies_variables_cies
	*/

		SELECT	i.invpolv_invoice_id							AS invoice_id,
				i.invpolv_policy_situation_id					AS policy_situation_id,
				i.invpolv_variable_id							AS variable_id,
				isnull(pv.prdv_company_id, p.pol_company_id)	AS company_id,
				i.invpolv_tf									AS tf,
				i.invpolv_taxes									AS taxes,
				i.invpolv_ti									AS ti,
				i.invpolv_commission_tf							AS commission_tf,
				i.invpolv_commission_taxes						AS commission_taxes,
				i.invpolv_commission_ti							AS commission_ti
		FROM	acc_invoices_policies_variables i
				INNER JOIN pol_policies_situations ps		ON	ps.pols_policy_situation_id = i.invpolv_policy_situation_id
				INNER JOIN pol_policies p					ON	p.pol_policy_id = ps.pols_policy_id
				INNER JOIN pr_products_variables pv			ON pv.prdv_variable_id = i.invpolv_variable_id 

		WHERE	NOT EXISTS	(
							SELECT	1
							FROM	acc_invoices_policies_variables_cies ir
							WHERE	ir.invpolvc_invoice_id = i.invpolv_invoice_id
							AND		ir.invpolvc_policy_situation_id = i.invpolv_policy_situation_id
							AND		ir.invpolvc_variable_id = i.invpolv_variable_id
							)

		UNION ALL

		SELECT	ir.invpolvc_invoice_id, 
				ir.invpolvc_policy_situation_id,
				ir.invpolvc_variable_id,
				ir.invpolvc_company_id,
				ir.invpolvc_tf,
				ir.invpolvc_taxes,
				ir.invpolvc_ti,
				ir.invpolvc_commission_tf,
				ir.invpolvc_commission_taxes,
				ir.invpolvc_commission_ti
		FROM	acc_invoices_policies_variables_cies ir
GO
