SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_find_pol_by_client_id]
	@id			int,
	@type_code	ais_code_short			-- movement type
AS

	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,
			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

			-- voir si c'est un contrat en coassurance
			inner join act_cies c on c.cie_actor_id = p.pol_company_id
			
	WHERE	pol_client_id = @id 
	and		(@type_code = 'ALL' or m.pmvt_type_code = @type_code OR ( @type_code = 'A_S' AND pmvt_type_code IN ('ACT', 'SUS')))
GO
