SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE procedure [dbo].[p_tt_policies_risks_situations_variables_all]
	@policies_situations_table type_table_id readonly,
	@culture_code ais_code_short
as 
	set nocount on;
		
	/*
	Liste de toutes les variables de type != FEP (variables risque) du produit, acquises ou non acquises
	*/

	select	
			
			/*
			ATTENTION !
			Le champ polv_policy_situation_id, qui devrait normalement servir de lien avec le parent, est NULL dans le cas d'une variable non acquise.
			On se sert donc de pols_policy_situation_id
			*/
			
			ps.pols_policy_situation_id,
			polv.*, npv.*, npvt.*,

			polv_acquired =		case 
								when polv.polv_variable_id is null then 0
								else 1
								end,
			
			polv_capital_formatted			= dbo.f_format_variable( 
												coalesce(polv_capital, prvb.prdvb_capital, prv.prdv_capital, 0), 
												coalesce(polv_fmt_code__capital, prvb.prdvb_fmt_code__capital, prv.prdv_fmt_code__capital),
												@culture_code,
												p.pol_currency_code),

			polv_franchise_formatted		= dbo.f_format_variable(
												coalesce(polv_franchise, prvb.prdvb_franchise, prv.prdv_franchise, 0), 
												coalesce(polv_fmt_code__franchise, prvb.prdvb_fmt_code__franchise, prv.prdv_fmt_code__franchise),
												@culture_code,
												p.pol_currency_code),
			
			polv_franchise_maxi_formatted	= dbo.f_format_variable(
												coalesce(polv_franchise_maxi, prvb.prdvb_franchise_maxi, prv.prdv_franchise_maxi, 0), 
												coalesce(polv_fmt_code__franchise_maxi, prvb.prdvb_fmt_code__franchise_maxi, prv.prdv_fmt_code__franchise_maxi),
												@culture_code,
												p.pol_currency_code),

			polv_franchise_mini_formatted	= dbo.f_format_variable(
												coalesce(polv_franchise_mini, prvb.prdvb_franchise_mini, prv.prdv_franchise_mini, 0), 
												coalesce(polv_fmt_code__franchise_mini, prvb.prdvb_fmt_code__franchise_mini, prv.prdv_fmt_code__franchise_mini),
												@culture_code,
												p.pol_currency_code),

			polv_premium_formatted			= dbo.f_format_variable(
												coalesce(polv_premium, prvb.prdvb_premium, prv.prdv_premium, 0), 
												coalesce(polv_fmt_code__premium, prvb.prdvb_fmt_code__premium, prv.prdv_fmt_code__premium),
												@culture_code,
												p.pol_currency_code)
			

	from	@policies_situations_table t
			inner join	pol_policies_situations ps			on	ps.pols_policy_situation_id = t.id
			inner join	pol_policies p						on	p.pol_policy_id = ps.pols_policy_id
			inner join	pr_products_variables prv			on	prv.prdv_product_code = p.pol_product_code
			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

			-- exceptions des variables par EJ
			left join	pr_products_variables_branches prvb	on	prvb.prdvb_variable_id = prv.prdv_variable_id
															and	prvb.prdvb_branch_id = p.pol_branch_id

			left join	(
							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,
									polv_fmt_code__premium,								polv_fmt_code__capital,								
									polv_fmt_code__franchise,							polv_fmt_code__franchise_mini,					polv_fmt_code__franchise_maxi

							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),
									polv_fmt_code__premium = max(risv_fmt_code__premium), 
									polv_fmt_code__capital = max(risv_fmt_code__capital),
									polv_fmt_code__franchise = max(risv_fmt_code__franchise),							
									polv_fmt_code__franchise_mini = max(risv_fmt_code__franchise_mini),	
									polv_fmt_code__franchise_maxi = max(risv_fmt_code__franchise_maxi)
							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							
															on	polv.polv_policy_situation_id = ps.pols_policy_situation_id
															and	polv.polv_variable_id = prv.prdv_variable_id

	where	-- exclure les variables ignorées sur l'entité juridique 
			not exists	(
							select	1
							from	pr_products_variables_branches a
							where	a.prdvb_variable_id = prv.prdv_variable_id
							and		a.prdvb_branch_id = p.pol_branch_id
							and		a.prdvb_ignored != 0
						)


			





GO
