SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[vw_act_branches_used_cies]
AS

	-- les compagnies utilisées dans les contrats
	SELECT	DISTINCT
			pol_branch_id = pp.pol_branch_id, 
			pol_company_id = pp.pol_company_id
	FROM pol_policies pp

	UNION

	-- les compagnies utilisées dans la coassurance
	SELECT  DISTINCT
			pol_branch_id = pp.pol_branch_id, 
			pol_company_id = ppsr.polsr_company_id
	FROM pol_policies_situations_repartitions ppsr
		INNER JOIN pol_policies_situations pps ON ppsr.polsr_policy_situation_id = pps.pols_policy_situation_id
		INNER JOIN pol_policies pp ON pps.pols_policy_id = pp.pol_policy_id

	UNION

	-- les compagnies utilisées dans les sinistres
	SELECT	DISTINCT
			pol_branch_id = pp.pol_branch_id, 
			pol_company_id = ca.clact_company_id
	FROM claim_actors ca
		INNER JOIN claim_claims cc ON cc.cl_claim_id = ca.clact_claim_id
		INNER JOIN pol_policies_situations pps ON pps.pols_policy_situation_id = cc.cl_policy_situation_id
		INNER JOIN pol_policies pp ON pps.pols_policy_id = pp.pol_policy_id
	WHERE ca.clact_company_id IS NOT null

	UNION

	-- les compagnies annexes des variables contrat
	SELECT DISTINCT
			pol_branch_id = pp.pol_branch_id, 
			pol_company_id = ppv.prdv_company_id
	FROM pol_variables pv
		INNER JOIN pol_policies_situations pps ON pv.polv_policy_situation_id = pps.pols_policy_situation_id
		INNER JOIN pol_policies pp ON pps.pols_policy_id = pp.pol_policy_id
		INNER JOIN pr_products_variables ppv ON		pv.polv_variable_id = ppv.prdv_variable_id 
												
	WHERE		ppv.prdv_company_id IS NOT NULL
			AND pp.pol_company_id != ppv.prdv_company_id


	UNION 

	-- les compagnies annexes des variables risque
	SELECT DISTINCT
			pol_branch_id = pp.pol_branch_id, 
			pol_company_id = ppv.prdv_company_id
	FROM ris_variables rv
		INNER JOIN ris_risks_situations rrs ON rv.risv_risk_situation_id = rrs.riss_risk_situation_id
		INNER JOIN pol_policies_situations pps ON rrs.riss_policy_situation_id = pps.pols_policy_situation_id AND rrs.riss_policy_id = pps.pols_policy_id
		INNER JOIN pol_policies pp ON pps.pols_policy_id = pp.pol_policy_id
		INNER JOIN pr_products_variables ppv ON rv.risv_variable_id = ppv.prdv_variable_id
	WHERE		ppv.prdv_company_id IS NOT NULL
			AND pp.pol_company_id != ppv.prdv_company_id
GO
