SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_claim_claims_lst]
    @claim_id int
AS 
	set nocount on

	-- sinistre
	select	c.*,
			act_actor_id = p.pol_client_id,
			p.pol_policy_id,
			s.pols_policy_situation_id,
			r.ris_risk_id,			
			rs.riss_risk_situation_id,
			rs.riss_number,
			f.pf_claim_scen_code,
			pf_family_code, p.pol_company_id, 
			e.evt_name
	from	claim_claims c
			inner join ris_risks_situations rs on rs.riss_risk_situation_id = c.cl_risk_situation_id
			inner join ris_risks r on r.ris_risk_id = rs.riss_risk_id
			inner join pol_policies p on p.pol_policy_id = r.ris_policy_id
			inner join pol_policies_situations s on s.pols_policy_situation_id = p.pol_last_policy_situation_id
			inner join pr_products on p.pol_product_code = prd_product_code
			inner join pr_families f on prd_family_code = pf_family_code

			left join claim_events e on e.evt_event_code = c.cl_event_code
	where	@claim_id = 0 or c.cl_claim_id = @claim_id

	declare @family_code ais_code_short,
			@branch_id int,

			@claim_scen_code ais_code_short

	select	@family_code = pf_family_code,
			@branch_id = p.pol_branch_id,

			@claim_scen_code = pf_claim_scen_code
	from	claim_claims c
			inner join ris_risks_situations rs on rs.riss_risk_situation_id = c.cl_risk_situation_id
			inner join ris_risks r on r.ris_risk_id = rs.riss_risk_id
			inner join pol_policies p on p.pol_policy_id = r.ris_policy_id
			inner join pol_policies_situations s on s.pols_policy_situation_id = p.pol_last_policy_situation_id
			inner join pr_products on prd_product_code = p.pol_product_code
			inner join pr_families on pf_family_code = prd_family_code
			inner join dyn_scenarios scen on sc_scen_code = pf_claim_scen_code
	where	c.cl_claim_id = @claim_id


	-- traitement des exceptions
	select	@claim_scen_code = pfb_claim_scen_code
	from	pr_families_branches
	where	pfb_family_code = @family_code
	and		pfb_branch_id = @branch_id


	
	-- scenariul sinistru
	select	scen.sc_scen_code, scen.sc_l1, scen.sc_l2, scen.sc_l3
	from	dyn_scenarios scen 
	where	sc_scen_code = @claim_scen_code

GO
