SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_find_ris]
	@branch_id int,
	@introducer_id int,
	@number ais_code_long
AS

	/*
	DGE - recherche des risques par numéro d'immat.

	La procédure renvoie toujours la dernière situation des risques.
	*/

	SELECT  act.act_actor_id,
			pol.pol_policy_id,
			pols.pols_policy_situation_id,
			ris.ris_risk_id,
			last_sit.*
	FROM    
			(
			select	distinct last_risk_situation_id = ris.ris_last_risk_situation_id 
			FROM    ris_risks ris
					inner join ris_risks_situations sit on sit.riss_risk_situation_id = ris.ris_last_risk_situation_id  
					inner join pol_policies pol on pol_policy_id = ris.ris_policy_id 
			WHERE	(
					pol.pol_last_policy_situation_id = sit.riss_policy_situation_id
					or 
					riss_obsolete = 0
					)
			and		(@number = '%' or sit.riss_number like @number)
			) as t
			inner join ris_risks_situations last_sit on last_sit.riss_risk_situation_id = t.last_risk_situation_id 
			inner join ris_risks ris on ris.ris_risk_id = last_sit.riss_risk_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 act_clients cli on cli_actor_id = act_actor_id
			--inner join usr_users u on u.usr_user_code = cli_user_code
	where	pol.pol_branch_id = @branch_id
	and		(	
				@introducer_id = 0
			or	isnull(cli.cli_introducer_id, 0) = @introducer_id
			)



GO
