SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_claim_variables_accounts_detail]
	@account_id int
as 
	/*
	DGE

	Obtenir les informations qui s'affichent dans l'écran ClaimAccountViewerForm
	*/

	set nocount on;


	select	ca.*,
			a.*,
			p.pol_client_id,
			p.pol_policy_id, 
			p.pol_product_code,
			ps.pols_policy_situation_id,
			c.cl_internal_reference,
			c.cl_policy_situation_id,
			rs.riss_risk_id,
			rs.riss_risk_situation_id,
			rs.riss_number,
			vir_variable_code = v.nmpv_variable_code
	from	claim_variables_accounts ca
			inner join acc_accounts a on a_account_id = ca.clacc_account_id
			inner join claim_claims c on c.cl_claim_id = ca.clacc_claim_id
			inner join pol_policies_situations ps on ps.pols_policy_situation_id = c.cl_policy_situation_id
			inner join pol_policies p on p.pol_policy_id = ps.pols_policy_id
			inner join ris_risks_situations rs on rs.riss_risk_situation_id = c.cl_risk_situation_id
			inner join vw_claim_variables v on	v.clv_claim_id = ca.clacc_claim_id
											and	v.clv_variable_id = ca.clacc_variable_id
	where	ca.clacc_account_id = @account_id


GO
