SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE view [dbo].[vw_acc_invoices_totals]
as
			
	select	invt_invoice_id = invoice_id,
			invt_variable_type_code = variable_type_code,
			invt_tf = sum(tf),
			invt_taxes = sum(taxes),
			invt_client_taxes = sum(ti) - sum(tf),
			invt_ti = sum(ti),
			invt_commission_ti = SUM(comm_ti),
			invt_commission_tf = SUM(comm_tf),
			invt_commission_taxes = SUM(comm_taxes),
			invt_commission_client_taxes = SUM(comm_ti) - SUM(comm_tf)
	from	(
			select	invoice_id = invpolt_invoice_id,
					variable_type_code = invpolt_variable_type_code,
					tf = SUM(invpolt_tf),
					taxes = SUM(invpolt_taxes),
					ti = SUM(invpolt_ti),
					comm_ti = SUM(invpolt_commission_ti),
					comm_tf = SUM(invpolt_commission_tf),
					comm_taxes = SUM(invpolt_commission_taxes)
			from	vw_acc_invoices_policies_situations_totals
			group by invpolt_invoice_id, invpolt_variable_type_code
			
			union
			
			select	invoice_id = invrist_invoice_id,
					variable_type_code = invrist_variable_type_code,
					tf = SUM(invrist_tf),
					taxes = SUM(invrist_taxes),
					ti = SUM(invrist_ti),
					comm_ti = SUM(invrist_commission_ti),
					comm_tf = SUM(invrist_commission_tf),
					comm_taxes = SUM(invrist_commission_taxes)
			from	vw_acc_invoices_risks_situations_totals
			group by invrist_invoice_id, invrist_variable_type_code
			) as t
	group by invoice_id, variable_type_code

	--select * from vw_acc_invoices_risks_situations_totals

GO
