SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE procedure [dbo].[p_tt_risk_situations_variables_all]
	@risk_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 risv_risk_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 riss_risk_situation_id
			*/
			rs.riss_risk_situation_id,
			risv.*, npv.*, npvt.*, 

			risv_acquired =		case 
								when risv.risv_variable_id is null or risv.risv_not_aquired = 1 then 0
								else 1
								end,


			risv_capital_formatted			= dbo.f_format_variable( 
												coalesce(risv_capital, prvb.prdvb_capital, prv.prdv_capital, 0), 
												coalesce(risv_fmt_code__capital, prvb.prdvb_fmt_code__capital, prv.prdv_fmt_code__capital),
												@culture_code,
												p.pol_currency_code),

			risv_franchise_formatted		= dbo.f_format_variable(
												coalesce(risv_franchise, prvb.prdvb_franchise, prv.prdv_franchise, 0), 
												coalesce(risv_fmt_code__franchise, prvb.prdvb_fmt_code__franchise, prv.prdv_fmt_code__franchise),
												@culture_code,
												p.pol_currency_code),
			
			risv_franchise_maxi_formatted	= dbo.f_format_variable(
												coalesce(risv_franchise_maxi, prvb.prdvb_franchise_maxi, prv.prdv_franchise_maxi, 0), 
												coalesce(risv_fmt_code__franchise_maxi, prvb.prdvb_fmt_code__franchise_maxi, prv.prdv_fmt_code__franchise_maxi),
												@culture_code,
												p.pol_currency_code),

			risv_franchise_mini_formatted	= dbo.f_format_variable(
												coalesce(risv_franchise_mini, prvb.prdvb_franchise_mini, prv.prdv_franchise_mini, 0), 
												coalesce(risv_fmt_code__franchise_mini, prvb.prdvb_fmt_code__franchise_mini, prv.prdv_fmt_code__franchise_mini),
												@culture_code,
												p.pol_currency_code),

			risv_premium_formatted			= dbo.f_format_variable(
												coalesce(risv_premium, prvb.prdvb_premium, prv.prdv_premium, 0), 
												coalesce(risv_fmt_code__premium, prvb.prdvb_fmt_code__premium, prv.prdv_fmt_code__premium),
												@culture_code,
												p.pol_currency_code)

	from	@risk_situations_table t
			inner join	ris_risks_situations rs				on	rs.riss_risk_situation_id = t.id 
			inner join	pol_policies p						on	p.pol_policy_id = rs.riss_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

			-- les variables du risque
			left join	ris_variables risv					on	risv.risv_risk_situation_id = rs.riss_risk_situation_id
															and	risv.risv_variable_id = prv.prdv_variable_id

	where	-- recupérer uniquement les variables de type risque ( != FEP)
			npvt.nmpvt_policy = 0 -- nmpv_type_code != 'FEP'
			-- exclure les variables ignorées sur l'entité juridique 
	and		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
