SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_find_ris_by_claim_id]
	@id int
AS

	SELECT  act.act_actor_id,
			pol.pol_policy_id,
			pols.pols_policy_situation_id,
			ris.ris_risk_id,
			last_sit.*
	FROM    ris_risks ris
			inner join ris_risks_situations last_sit on last_sit.riss_risk_situation_id = ris.ris_last_risk_situation_id  
			inner join pol_policies pol on pol_policy_id = ris.ris_policy_id 
			inner join pol_policies_situations pols on pols_policy_situation_id = last_sit.riss_policy_situation_id 
			inner join act_actors act on act.act_actor_id = pol.pol_client_id
			inner join claim_claims on cl_risk_situation_id = riss_risk_situation_id
	WHERE	(
			pol.pol_last_policy_situation_id = last_sit.riss_policy_situation_id
			or 
			riss_obsolete = 0
			)
	and		cl_claim_id = @id
GO
