SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_find_cli]
	@branch_id int,
	@introducer_id int,
	@type_code ais_code_short,
	@statut_code ais_code_short,
	@internal_reference ais_code_medium = '%',
	@external_reference ais_code_long = '%',
	@name nvarchar(128) = '%',
	@registry_number varchar(50) = '%'
AS

	set @statut_code = isnull(nullif(@statut_code, 'ALL'),'%')

	SELECT  act_actor_id,
			act_type_code,
			act_internal_reference,
			act_external_reference,
			adr_name,
			adr_complete_name,
			adr_address1,
			adr_address2,
			adr_address3
	FROM    vw_act_actors_adresses
			INNER JOIN act_clients on cli_actor_id = act_actor_id AND act_type_code = @type_code AND @statut_code in ('%', cli_statut_code  )
			--inner join usr_users on usr_user_code = cli_user_code
	WHERE	cli_branch_id = @branch_id
	and		(	@introducer_id <= 0 or isnull(cli_introducer_id, 0) = @introducer_id )

	and		(		(@internal_reference = '%' or act_internal_reference like @internal_reference)
				or	(@external_reference = '%' or act_external_reference like @external_reference)
				or	(@name = '%' or	adr_complete_name like @name)
				or	(@registry_number = '%' or cli_registry_number like @registry_number)
			)



GO
