SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_policy_situations_list]
	@policy_id int,
	@culture_code ais_code_short
as 
	set nocount on;

	select p.*, s.*, i.*,
			prd_family_code,
			prd_company_id, pf_class_code, pf_subclass_code, pf_bonus,
			pf_bonus, pf_type_pol_code, m.pmvt_type_code, 
			c.cie_treaty, 
			pols_policy_situation = RANK() over (order by pols_policy_situation_id),
			pf_delegation
	from	pol_policies p
			inner join pol_policies_situations s on	pols_policy_id = pol_policy_id
			inner join pr_products on prd_product_code = pol_product_code
			inner join pr_families on pf_family_code = prd_family_code
			inner join act_branches b on br_actor_id = pol_branch_id
			inner join pol_mvts m on m.pmvt_mvt_code = pols_mvt_code
			inner join act_cies c on c.cie_actor_id = pol_company_id
			left join pol_policies_situations_introducers i on i.polsi_policy_situation_id = s.pols_policy_situation_id
	where p.pol_policy_id = @policy_id
	order by pols_policy_situation_id desc

	-- variabile
	SELECT  v.polv_policy_situation_id, v.polv_variable_id, /*v.polv_variable_date_setup,*/
			prdv_variable_code, nmpv_l1, nmpv_l2, nmpv_l3, nmpv_variable_code,
			polv_premium,					polv_premium_formatted = dbo.f_format_variable(polv_premium, polv_fmt_code__premium, @culture_code, pp.pol_currency_code),
			polv_tf,						
			polv_taxes,						
			polv_ti,						
			polv_franchise_mini,			polv_franchise_mini_formatted = dbo.f_format_variable(polv_franchise_mini, polv_fmt_code__franchise_mini, @culture_code, pp.pol_currency_code),
			polv_franchise_maxi,			polv_franchise_maxi_formatted = dbo.f_format_variable(polv_franchise_maxi, polv_fmt_code__franchise_maxi, @culture_code, pp.pol_currency_code),
			polv_franchise,					polv_franchise_formatted = dbo.f_format_variable(polv_franchise, polv_fmt_code__franchise, @culture_code, pp.pol_currency_code),
			polv_capital,					polv_capital_formatted = dbo.f_format_variable(polv_capital, polv_fmt_code__capital, @culture_code, pp.pol_currency_code)
	FROM    pol_variables v
			inner join pr_products_variables pv on pv.prdv_variable_id = v.polv_variable_id 
			inner join nm_products_variables on nmpv_variable_code = pv.prdv_variable_code 
			inner join pol_policies_situations s on s.pols_policy_situation_id = v.polv_policy_situation_id 
			inner join pol_policies pp on pp.pol_policy_id = s.pols_policy_id
	where	s.pols_policy_id = @policy_id
	
	-- taxe pentru variabile
	SELECT  t.polvt_policy_situation_id, t.polvt_variable_id, /*t.polvt_variable_date_setup,*/ 
		n.nmtax_tax_code, n.nmtax_l1, n.nmtax_l2, n.nmtax_l3, t.polvt_tax_value,
		pv.prdv_variable_code
	FROM    pol_variables_taxes t
			inner join pr_products_variables pv on pv.prdv_variable_id = t.polvt_variable_id 
			inner join pol_policies_situations s	on	s.pols_policy_situation_id = t.polvt_policy_situation_id
			inner join acc_taxes at on at.acctax_tax_id = t.polvt_tax_id 
			inner join nm_taxes n	on	n.nmtax_tax_code = at.acctax_tax_code 


	-- exceptions des paramètres famille par EJ?

	declare @branch_id int,
			@family_code ais_code_short,
	
			@scen_code_policy ais_code_short,
			@scen_code_risk ais_code_short,
			
			@class_code ais_code_short,
			@zone_code ais_code_short,
			@usage_code ais_code_short,

			@index bit


	-- initialiser avec les valeurs de base
	select	@branch_id = p.pol_branch_id,
			@family_code = pf_family_code,
			
			@scen_code_policy = pf_policy_scen_code,
			@scen_code_risk = pf_risk_scen_code,

			@class_code = pf_group_code__class,
			@zone_code = pf_group_code__zone,
			@usage_code = pf_group_code__usage,

			@index = pf_index

	from	pol_policies p
			inner join pol_policies_situations s on s.pols_policy_situation_id = p.pol_last_policy_situation_id
			inner join pr_products on prd_product_code = pol_product_code
			inner join pr_families on pf_family_code = prd_family_code
	where	p.pol_policy_id = @policy_id

	-- initialiser les exceptions (si elles existent)
	select	@scen_code_policy = pfb_policy_scen_code,
			@scen_code_risk = pfb_risk_scen_code,

			@class_code = pfb_group_code__class,
			@zone_code = pfb_group_code__zone,
			@usage_code = pfb_group_code__usage,

			@index = pfb_index

	from	pr_families_branches
	where	pfb_branch_id = @branch_id
	and		pfb_family_code = @family_code


			
	
	select	-- codurile pentru class, zone, usage
			pf_family_code, 
			pf_group_code__class = @class_code, 
			pf_group_code__zone = @zone_code, 
			pf_group_code__usage = @usage_code, 
			pf_bonus,
			-- libellés riss_number et riss_nature
			pf_field_riss_number_l1, pf_field_riss_number_l2, pf_field_riss_number_l3,
			pf_field_riss_nature_l1, pf_field_riss_nature_l2, pf_field_riss_nature_l3,
			-- affichage des indices
			pf_index = @index
	from	pr_families 
	where	pf_family_code = @family_code

	



	-- scenariul contract
	select	scen.sc_scen_code, scen.sc_l1, scen.sc_l2, scen.sc_l3
	from	dyn_scenarios scen
	where	scen.sc_scen_code = @scen_code_policy
	
	
	-- scenariul risc
	select	scen.sc_scen_code, scen.sc_l1, scen.sc_l2, scen.sc_l3
	from	dyn_scenarios scen 
	where	scen.sc_scen_code = @scen_code_risk

	-- coassurance
	select	psr.*
	from	pol_policies_situations_repartitions psr
			inner join pol_policies_situations ps	on	ps.pols_policy_situation_id = psr.polsr_policy_situation_id
	where	ps.pols_policy_id = @policy_id




GO
