SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_find_pol]
	@branch_id int,
	@introducer_id int,
	@number ais_code_long,
	@type_code ais_code_short,			-- movement type
	@policy_situation_id int = -1,		/*
										On a parfois besoin de renvoyer vers le client de cette ligne pour une police dont on connait l'ID.
										Exemple: on est dans ScreenPolViewer, sur une ligne précise, et on veut faire Aller Vers et "avenant".
										*/
	@family_code ais_code_short = '%' -- filtrer sur la famille du produit

AS

	set nocount on;

	SELECT  a.act_actor_id,
			a.act_internal_reference,
			p.*,
			ps.*, 
			prd.prd_family_code,
			fam.pf_class_code, fam.pf_subclass_code, fam.pf_delegation, fam.pf_type_pol_code, fam.pf_l1, fam.pf_l2, fam.pf_l3,
			c.cie_treaty
	FROM    pol_policies p
			inner join pol_policies_situations ps on pol_policy_id = pols_policy_id and pol_last_policy_situation_id = pols_policy_situation_id
			inner join pol_mvts m on m.pmvt_mvt_code = pols_mvt_code
			inner join act_actors a on a.act_actor_id = p.pol_client_id
			inner join pr_products prd on prd_product_code = pol_product_code
			inner join pr_families fam on pf_family_code = prd_family_code
			inner join act_clients cli on cli.cli_actor_id = p.pol_client_id
			inner join usr_users u on u.usr_user_code = cli.cli_user_code

			-- voir si c'est un contrat en coassurance
			inner join act_cies c on c.cie_actor_id = p.pol_company_id

	WHERE	p.pol_branch_id = @branch_id
	and		1 =		case
					when @policy_situation_id > 0 and ps.pols_policy_situation_id = @policy_situation_id THEN 1
					when @policy_situation_id <= 0 
						AND	(@number = '%' or pols_number like @number)
						and	(@type_code = 'ALL' or m.pmvt_type_code = @type_code OR ( @type_code = 'A_S' AND pmvt_type_code IN ('ACT', 'SUS')))
						and	(@introducer_id = 0 or isnull(cli.cli_introducer_id, 0) = @introducer_id) THEN 1
					else 0
					end
	and		( isnull(@family_code,'%') = '%' or pf_family_code = @family_code)


GO
