SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[p_find_pol_by_risk_situation_id]
	@id int
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
			inner join ris_risks on ris_policy_id = pol_policy_id	
			inner join ris_risks_situations on riss_risk_id = ris_risk_id 	
			
			-- voir si c'est un contrat en coassurance
			inner join act_cies c on c.cie_actor_id = p.pol_company_id
				
	WHERE	riss_risk_situation_id = @id 











GO
