SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_tt_invoices_risk_variables_sum_by_risk_situation_id_invoice_id]
	@invoices_table type_table_id readonly
AS

	set nocount on;

	SELECT	--ai.invris_invoice_id,             col retournée par t.*
			--ai.invris_risk_situation_id,      col retournée par t.*
			invris_start_date = isnull(ai.invris_start_date, ai2.inv_start_date),
			invris_end_date = isnull(ai.invris_end_date, ai2.inv_end_date),
			t.*
	FROM	acc_invoices_risks_situations ai 
			INNER JOIN dbo.acc_invoices ai2 ON ai2.inv_invoice_id = ai.invris_invoice_id
			INNER JOIN @invoices_table p ON p.id = ai.invris_invoice_id
			INNER JOIN (
							select	airs.invris_risk_situation_id,
									airs.invris_invoice_id,
									invris_tf_per =	sum(CASE nv.nmpv_type_code
												WHEN 'PER' THEN irv.invrisv_tf
												ELSE 0
												end),
									invris_taxes_per =	sum(CASE nv.nmpv_type_code
												WHEN 'PER' THEN irv.invrisv_taxes
												ELSE 0
												end),
									invris_ti_per =	sum(CASE nv.nmpv_type_code
												WHEN 'PER' THEN irv.invrisv_ti
												ELSE 0
												end),
									invris_cion_tf_per = sum(CASE nv.nmpv_type_code
												WHEN 'PER' THEN irv.invrisv_commission_tf
												ELSE 0
												end),
									invris_cion_taxes_per =	sum(CASE nv.nmpv_type_code
												WHEN 'PER' THEN irv.invrisv_commission_taxes
												ELSE 0
												end),
									invris_cion_ti_per =	sum(CASE nv.nmpv_type_code
												WHEN 'PER' THEN irv.invrisv_commission_ti
												ELSE 0
												end),
									------------------------
									invris_tf_brc =	sum(CASE nv.nmpv_type_code
												WHEN 'BRC' THEN irv.invrisv_tf
												ELSE 0
												end),
									invris_taxes_brc =	sum(CASE nv.nmpv_type_code
												WHEN 'BRC' THEN irv.invrisv_taxes
												ELSE 0
												end),
									invris_ti_brc =	sum(CASE nv.nmpv_type_code
												WHEN 'BRC' THEN irv.invrisv_ti
												ELSE 0
												end),
									invris_cion_tf_brc =	sum(CASE nv.nmpv_type_code
												WHEN 'BRC' THEN irv.invrisv_commission_tf
												ELSE 0
												end),
									invris_cion_taxes_brc =	sum(CASE nv.nmpv_type_code
												WHEN 'BRC' THEN irv.invrisv_commission_taxes
												ELSE 0
												end),
									invris_cion_ti_brc =	sum(CASE nv.nmpv_type_code
												WHEN 'BRC' THEN irv.invrisv_commission_ti
												ELSE 0
												end),

									--------------------------
									invris_ti	=	sum(CASE nv.nmpv_type_code
												WHEN 'PER' THEN irv.invrisv_ti
												ELSE 0
												end) 
												+	sum(CASE nv.nmpv_type_code
												WHEN 'BRC' THEN irv.invrisv_ti
												ELSE 0
												end)

							from	dbo.acc_invoices_risks_situations airs
									INNER JOIN dbo.acc_invoices ai ON ai.inv_invoice_id = airs.invris_invoice_id
									inner join acc_invoices_risks_variables irv		ON	irv.invrisv_invoice_id = airs.invris_invoice_id 
																					AND irv.invrisv_risk_situation_id = airs.invris_risk_situation_id

									inner join pr_products_variables pv on pv.prdv_variable_id = irv.invrisv_variable_id 
									inner join nm_products_variables nv on nv.nmpv_variable_code = pv.prdv_variable_code 
									INNER JOIN @invoices_table t ON t.id = airs.invris_invoice_id
							GROUP BY	airs.invris_risk_situation_id,
										airs.invris_invoice_id
							) AS t	ON	t.invris_invoice_id = ai.invris_invoice_id
									AND	t.invris_risk_situation_id = ai.invris_risk_situation_id
			



GO
