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

	SELECT  act_actor_id = pol.pol_client_id,	
			pol_policy_id = pol.pol_policy_id,
			pols_policy_situation_id,
			ris_risk_id,
			riss_risk_situation_id, riss_number,
			cl.*,

			ca.clact_identification, tadr.adr_complete_name,
			ce.evt_event_code, ce.evt_name
	FROM    claim_claims cl
			inner join ris_risks_situations rs on rs.riss_risk_situation_id = cl.cl_risk_situation_id
			inner join ris_risks r on r.ris_risk_id = rs.riss_risk_id
			inner join pol_policies pol on pol_policy_id = r.ris_policy_id
			inner join pol_policies_situations pols on pols.pols_policy_situation_id = pol.pol_last_policy_situation_id

			-- tiers principal
			left join vw_claim_main_actors ma	on	ma.clact_claim_id = cl.cl_claim_id
			left join claim_actors ca			on	ca.clact_claim_id = ma.clact_claim_id
												and	ca.clact_actor_id = ma.clact_actor_id
			left join act_actors ta				on	ta.act_actor_id = ca.clact_actor_id
			left join adr_addresses tadr		on	tadr.adr_address_id = ta.act_address_id

			left join claim_events ce			on	ce.evt_event_code = cl.cl_event_code

	WHERE	riss_risk_situation_id = @id
GO
