SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_claim_variables_list]
	@claim_id int,
	@culture_code ais_code_short
AS

	/*
	DGE
	Afficher la liste des variables d'un sinistre

	|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
	|  CODE VARIABLE  |  INTITULE  |  CAPITAL  |  FRANCHISE  |  FRANCHISE MINI  |  FRANCHISE MAXI  |  PROVISION  |  PROVISION REPR |PREV. RECOURS  |  PAIEMENT  |  RECOURS ENCAISSES  |
	|-----------------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------|
	|                 |            |           |             |                  |                  |             |                 |               |            |                     |
	|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
	*/

	select	
			cv.*,
			risv.risv_capital, 
			risv_capital_formatted = dbo.f_format_variable(risv.risv_capital, risv.risv_fmt_code__capital, @culture_code, pp.pol_currency_code),
			risv.risv_franchise, 
			risv_franchise_formatted = dbo.f_format_variable(risv.risv_franchise, risv.risv_fmt_code__franchise, @culture_code, pp.pol_currency_code),
			risv.risv_franchise_mini, 
			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.risv_franchise_maxi, 
			risv_franchise_maxi_formatted = dbo.f_format_variable(risv.risv_franchise_maxi, risv.risv_fmt_code__franchise_maxi, @culture_code, pp.pol_currency_code),
			nv.nmpv_claims_injury
	from	vw_claim_variables cv
			inner join claim_claims c on c.cl_claim_id = cv.clv_claim_id
			inner join pol_policies_situations pps on c.cl_policy_situation_id = pps.pols_policy_situation_id
			inner join pol_policies pp on pp.pol_policy_id = pps.pols_policy_id
			left join pr_products_variables pv on pv.prdv_variable_id = cv.clv_variable_id
			left join nm_products_variables nv on nv.nmpv_variable_code = pv.prdv_variable_code
			left join ris_variables risv on risv.risv_risk_situation_id = c.cl_risk_situation_id and risv.risv_variable_id = cv.clv_variable_id
	where	cv.clv_claim_id = @claim_id


	-- liste des provisions
	select	cvp.clvp_claim_id,
			cvp.clvp_variable_id,
			cvp.clvp_date,
			cvp.clvp_amount, 
			cvp.clvp_repr,
			cvp.clvp_injury,
			u.usr_name
	from	claim_variables_provisions cvp
			inner join usr_users u on u.usr_user_code = cvp.clvp_user_code
	where	cvp.clvp_claim_id = @claim_id

	-- liste des prévisions de recours
	select	cvr.clvr_claim_id,
			cvr.clvr_variable_id,
			cvr.clvr_date,
			cvr.clvr_amount,
			u.usr_name
	from	claim_variables_recoveries cvr
			inner join usr_users u on u.usr_user_code = cvr.clvr_user_code
	where	cvr.clvr_claim_id = @claim_id








GO
