SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[P_CLAIM_VARIABLES_SELECT]
	@risk_situation_id int,
	@variables_to_exclure type_table_id READONLY,
	@culture_code ais_code_short
AS

	/*
	DGE
	Afficher la liste des variables sinistrables d'une situation risque, sous format:

	|-------------------------------------------------------------------------------------------------|------------------|
	|  CODE VARIABLE  |  INTITULE  |  COMPAGNIE   |    CAPITAL  |  FRANCHISE       |  FRANCHISE MINI  |  FRANCHISE MAXI  |
	|-----------------+------------+--------------+-------------+------------------+------------------|------------------|
	|                 |            |              |             |                  |                  |                  |
	|-------------------------------------------------------------------------------------------------|------------------|

	On ajoute à cette liste les variables de la table nm_claims_variables (règlement mandataire, par exemple) qui ne sont pas déjà utilisées
	*/


	select	nv.nmpv_variable_code, 
			nv.nmpv_l1, nv.nmpv_l2, nv.nmpv_l3,
			pv.prdv_variable_id,
			prdv_company_id = isnull(pv.prdv_company_id, pp.pol_company_id),
			risv_capital_formatted = dbo.f_format_variable(risv.risv_capital, risv.risv_fmt_code__capital, @culture_code, pp.pol_currency_code),
			risv_franchise_formatted = dbo.f_format_variable(risv.risv_franchise, risv.risv_fmt_code__franchise, @culture_code, pp.pol_currency_code),
			risv_franchise_mini_formatted = dbo.f_format_variable(risv.risv_franchise_mini, risv.risv_fmt_code__franchise_mini, @culture_code, pp.pol_currency_code),
			risv_franchise_maxi_formatted = dbo.f_format_variable(risv.risv_franchise_maxi, risv.risv_fmt_code__franchise_maxi, @culture_code, pp.pol_currency_code)
	from	ris_variables risv
			inner join pr_products_variables pv on pv.prdv_variable_id = risv.risv_variable_id 
			inner join nm_products_variables nv on nv.nmpv_variable_code = pv.prdv_variable_code
			inner join ris_risks_situations rrs on rrs.riss_risk_situation_id = risv.risv_risk_situation_id
			inner join pol_policies_situations pps on pps.pols_policy_situation_id = rrs.riss_policy_situation_id
			inner join pol_policies pp on pp.pol_policy_id = pps.pols_policy_id
	where	-- filtre par id situation
			risv_risk_situation_id = @risk_situation_id		
			-- variables sinistrables uniquement
	and		nv.nmpv_claims != 0					
			-- exclure les variables déjà sélectionnées au niveau du sinistre
	and		risv_variable_id not in ( select id from @variables_to_exclure )

	union

	select	nmpv_variable_code				= nmcv_variable_code,
			nmpv_l1							= nmcv_l1,
			nmpv_l2							= nmcv_l2,
			nmpv_l3							= nmcv_l3,
			prdv_variable_id				= nmcv_variable_id,
			prdv_company_id					= null,
			risv_capital_formatted			= null,
			risv_franchise_formatted		= null,
			risv_franchise_mini_formatted	= null,
			risv_franchise_maxi_formatted	= null
	from	nm_claims_variables
	where	nmcv_variable_id not in ( select id from @variables_to_exclure )



	



GO
