SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[vw_acc_invoices_policies_variables_taxes]
AS
	WITH cte_union
	AS	(
			SELECT	t.invpolvt_invoice_id				AS invoice_id,
					t.invpolvt_policy_situation_id		AS policy_situation_id,
					t.invpolvt_variable_id				AS variable_id,
					t.invpolvt_tax_id					AS tax_id,
					t.invpolvt_tax_value				AS tax_value
			FROM	acc_invoices_policies_variables_taxes t

			UNION ALL

			SELECT	ct.invpolvct_invoice_id, 
					ct.invpolvct_policy_situation_id,
					ct.invpolvct_variable_id, 
					ct.invpolvct_tax_id, 
					ct.invpolvct_tax_value
			FROM	acc_invoices_policies_variables_commission_taxes ct
		),
	cte_group
	AS	(
		SELECT	u.invoice_id, u.policy_situation_id, u.variable_id, u.tax_id,
				SUM(u.tax_value) AS tax_value
		FROM	cte_union u
		GROUP BY u.invoice_id, u.policy_situation_id, u.variable_id, u.tax_id
		)
	SELECT	g.invoice_id, g.policy_situation_id, g.variable_id, g.tax_id, g.tax_value
	FROM	cte_group g



GO
