
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_tt_policies_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 (frais police) 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), 
												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), 
												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), 
												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), 
												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), 
												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

			-- les variables du contrat
			left join	pol_variables polv					on	polv.polv_policy_situation_id = ps.pols_policy_situation_id
															and	polv.polv_variable_id = prv.prdv_variable_id

	where	npvt.nmpvt_policy = 1 -- 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
