SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE procedure [dbo].[p_tt_policies_risks_situations_variables]
	@policies_situations_table type_table_id readonly,
	@culture_code ais_code_short
as 
	set nocount on;
		
	select	polv.*, npv.*, npvt.*,
			polv_capital_formatted = dbo.f_format_variable(polv_capital, prdv_fmt_code__capital, @culture_code, pp.pol_currency_code),
			polv_franchise_formatted = dbo.f_format_variable(polv_franchise, prdv_fmt_code__franchise, @culture_code, pp.pol_currency_code),
			polv_franchise_maxi_formatted = dbo.f_format_variable(polv_franchise_maxi, prdv_fmt_code__franchise_maxi, @culture_code, pp.pol_currency_code),
			polv_franchise_mini_formatted = dbo.f_format_variable(polv_franchise_mini, prdv_fmt_code__franchise_mini, @culture_code, pp.pol_currency_code),
			polv_premium_formatted = dbo.f_format_variable(polv_premium, prdv_fmt_code__premium, @culture_code, pp.pol_currency_code)
	from 
		(
			select	polv_policy_situation_id,							polv_variable_id,		
					polv_premium,										polv_tf,										polv_taxes,
					polv_ti,											polv_franchise_mini,							polv_franchise_maxi,
					polv_franchise,										polv_capital
			from pol_variables polv
				inner join @policies_situations_table t on polv.polv_policy_situation_id = t.id
				
			union all
				
			select	polv_policy_situation_id = riss_policy_situation_id,polv_variable_id = risv_variable_id,		
					polv_premium = sum(risv_premium),					polv_tf = sum(risv_tf),							polv_taxes = sum(risv_taxes),
					polv_ti = sum(risv_ti),								polv_franchise_mini = sum(risv_franchise_mini),	polv_franchise_maxi = sum(risv_franchise_maxi),
					polv_franchise = sum(risv_franchise),				polv_capital = sum(risv_capital)
			from ris_variables risv 
				inner join ris_risks_situations riss on riss_risk_situation_id = risv_risk_situation_id 
				inner join @policies_situations_table on riss_policy_situation_id = id 
			where risv.risv_not_aquired = 0
			group by riss_policy_situation_id, risv_variable_id
		) as polv
		inner join pol_policies_situations pps on pps.pols_policy_situation_id = polv.polv_policy_situation_id
		inner join pol_policies pp on pps.pols_policy_id = pp.pol_policy_id
		inner join pr_products_variables prv on polv.polv_variable_id = prv.prdv_variable_id
		inner join nm_products_variables npv on npv.nmpv_variable_code = prv.prdv_variable_code
		inner join nm_products_variables_types npvt on npvt.nmpvt_type_code = npv.nmpv_type_code
	
		

		


GO
