SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_find_act]
	@branch_id int,
	@introducer_id int,
	@type_code ais_code_short,
	@internal_reference ais_code_medium = '%',
	@external_reference ais_code_long = '%',
	@name nvarchar(128) = '%'
AS
	/*
	ATENTIE, daca se adauga campuri select-uri, trebuie adaugate peste tot
	*/

	set nocount on;
	
	declare @actors TABLE (actor_id int)
	
	insert into @actors (	actor_id )
	select					act_actor_id
	FROM    vw_act_actors_adresses 
	WHERE	act_type_code = @type_code 
	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)
			)
	
	set @type_code = isnull(@type_code,'')
	
	if @type_code = 'CLI'
	
		select  item.*,
				act_actor_id,
				act_type_code,
				act_internal_reference,
				act_external_reference,
				act_sequence_code,
				act_date_setup,
				adr_name,
				adr_complete_name,
				adr_address1,
				adr_address2,
				adr_address3
		from @actors
			inner join act_clients item on actor_id = item.cli_actor_id
			inner join act_actors on actor_id = act_actor_id
			inner join adr_addresses on act_address_id = adr_address_id
			--inner join usr_users on usr_user_code = cli_user_code
		where	cli_branch_id = @branch_id
		and		(
					@introducer_id = 0									-- accès à tous les clients
				--or	isnull(usr_introducer_id, 0) = @introducer_id	-- accès restreint
				or	isnull(cli_introducer_id, 0) = @introducer_id
				)

			
	else if @type_code = 'INT' 
	
		select  item.*,
				act_actor_id,
				act_type_code,
				act_internal_reference,
				act_external_reference,
				act_sequence_code,
				act_date_setup,
				adr_name,
				adr_complete_name,
				adr_address1,
				adr_address2,
				adr_address3
		from @actors
			inner join act_introducers item on actor_id = item.int_actor_id
			inner join act_actors on actor_id = act_actor_id
			inner join adr_addresses on act_address_id = adr_address_id
		
	else if @type_code = 'BRA' 
	
		select  item.*,
				act_actor_id,
				act_type_code,
				act_internal_reference,
				act_external_reference,
				act_sequence_code,
				act_date_setup,
				adr_name,
				adr_complete_name,
				adr_address1,
				adr_address2,
				adr_address3
		from @actors
			inner join act_branches item on actor_id = item.br_actor_id
			inner join act_actors on actor_id = act_actor_id
			inner join adr_addresses on act_address_id = adr_address_id
			
	else if @type_code = 'CIE' 
	
		select  item.*,
				act_actor_id,
				act_type_code,
				act_internal_reference,
				act_external_reference,
				act_sequence_code,
				act_date_setup,
				adr_name,
				adr_complete_name,
				adr_address1,
				adr_address2,
				adr_address3
		from @actors
			inner join act_cies item on actor_id = item.cie_actor_id
			inner join act_actors on actor_id = act_actor_id
			inner join adr_addresses on act_address_id = adr_address_id
		
	else if @type_code = 'BNK' 
	
		select  item.*,
				act_actor_id,
				act_type_code,
				act_internal_reference,
				act_external_reference,
				act_sequence_code,
				act_date_setup,
				adr_name,
				adr_complete_name,
				adr_address1,
				adr_address2,
				adr_address3
		from @actors
			inner join act_banks item on actor_id = item.bk_actor_id
			inner join act_actors on actor_id = act_actor_id
			inner join adr_addresses on act_address_id = adr_address_id
			
	else if @type_code = 'TAX' 
	
		select  item.*,
				act_actor_id,
				act_type_code,
				act_internal_reference,
				act_external_reference,
				act_sequence_code,
				act_date_setup,
				adr_name,
				adr_complete_name,
				adr_address1,
				adr_address2,
				adr_address3
		from @actors
			inner join act_tax_entities item on actor_id = item.tent_actor_id
			inner join act_actors on actor_id = act_actor_id
			inner join adr_addresses on act_address_id = adr_address_id
			
	else if @type_code = 'TRT' 
	
		select  item.*,
				act_actor_id,
				act_type_code,
				act_internal_reference,
				act_external_reference,
				act_sequence_code,
				act_date_setup,
				adr_name,
				adr_complete_name,
				adr_address1,
				adr_address2,
				adr_address3
		from @actors
			inner join act_treaties item on actor_id = item.trt_actor_id
			inner join act_actors on actor_id = act_actor_id
			inner join adr_addresses on act_address_id = adr_address_id
		
	else
	
		select  act_actor_id,
				act_type_code,
				act_internal_reference,
				act_external_reference,
				act_sequence_code,
				act_date_setup,
				adr_name,
				adr_complete_name,
				adr_address1,
				adr_address2,
				adr_address3
		from @actors
			inner join act_actors on actor_id = act_actor_id
			inner join adr_addresses on act_address_id = adr_address_id


GO
