SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_find_ris_by_client_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
	WHERE	pol_client_id = @id
	and		(
			pol.pol_last_policy_situation_id = last_sit.riss_policy_situation_id
			or 
			riss_obsolete = 0
			)
GO
