SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_find_claim]
	@branch_id		int,
	@introducer_id	int,
	@reference		ais_code_long = '%',
	@reference_int	ais_code_long = '%',
	@reference_ext	ais_code_long = '%',
	@date_occur		datetime = null,
	@reg_no			ais_code_long = '%',
	@tiers_name		ais_name, 
	@tiers_id		ais_code_long,
	@event_code		ais_code_medium
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 ris on ris.ris_risk_id = rs.riss_risk_id
			inner join pol_policies pol on pol_policy_id = ris.ris_policy_id
			inner join pol_policies_situations pols on pols.pols_policy_situation_id = pol.pol_last_policy_situation_id
			inner join act_clients cli on cli_actor_id = pol.pol_client_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	cl.cl_branch_id = @branch_id
	AND		(@reference = '%' or cl_internal_reference like @reference or cl_external_reference like @reference)
	AND		(@reference_int = '%' or cl_internal_reference like @reference_int)
	AND		(@reference_ext = '%' or cl_external_reference like @reference_ext)
	AND		(@date_occur is null or cl_occurence_date = @date_occur)
	AND		(@reg_no = '%' or riss_number like @reg_no)
	AND		(@introducer_id = 0 or ISNULL(cli.cli_introducer_id, 0) = @introducer_id)

	-- nom du tiers
	AND		1 = case 
				when @tiers_name = '%' then 1
				when exists (	select	1
								from	claim_actors ca
										inner join act_actors a		on	a.act_actor_id = ca.clact_actor_id
										inner join adr_addresses aa	on	aa.adr_address_id = a.act_address_id
								where	ca.clact_claim_id = cl.cl_claim_id
								and		aa.adr_complete_name like @tiers_name ) then 1
				else 0
				end
	-- id du tiers
	AND		1 =	case
				when @tiers_id = '%' then 1
				when exists (	select	1
								from	claim_actors ca
								where	ca.clact_claim_id = cl.cl_claim_id
								and		ca.clact_identification like @tiers_id) then 1
				else 0
				end

	-- code événement
	and		isnull(cl.cl_event_code, '') like @event_code

	


GO
