SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_ris_risks_situations_groups]
	@table_risks_groups type_risks_groups READONLY,
	@branch_id int
as

-- le classier
select	clsc_class_id,clsc_group_code,clsc_brand_code,clsc_class_code,clsc_vin,clsc_vin_order,clsc_model,clsc_version,clsc_fiscal_power,
		clsc_type,clsc_intake,clsc_value,clsc_energy,clsc_cc,clsc_gender,
		clsc_default_group = case WHEN gcc.clscie_class_id IS NULL THEN clsc_default_group ELSE clscie_default_group end,
		clsc_default_class = case WHEN gcc.clscie_class_id IS NULL THEN clsc_default_class ELSE clscie_default_class end,
		clsc_default_theft = case WHEN gcc.clscie_class_id IS NULL THEN clsc_default_theft ELSE clscie_default_theft end,
		clsc_sra_group = case WHEN gcc.clscie_class_id IS NULL THEN clsc_sra_group ELSE clscie_sra_group end,
		clsc_sra_class = case WHEN gcc.clscie_class_id IS NULL THEN clsc_sra_class ELSE clscie_sra_class end,
		clsc_sra_repair = case WHEN gcc.clscie_class_id IS NULL THEN clsc_sra_repair ELSE clscie_sra_repair end,
		rcpl_risk_situation_id
from @table_risks_groups t
	inner JOIN pr_products p on p.prd_product_code = t.prd_product_code
	inner join grpcls_classes gc on rcpl_class_id = gc.clsc_class_id


	-- exception des produits par entité
	left join pr_products_branches pb	on	pb.prdb_product_code = p.prd_product_code 
										and pb.prdb_branch_id = @branch_id

	left join grpcls_classes_cies gcc on gcc.clscie_class_id = gc.clsc_class_id and gcc.clscie_company_id = isnull(pb.prdb_company_id, p.prd_company_id )


	
-- le zonier
select	gz.*,
		zonz_zone = zonzc_zone,
		rcpl_risk_situation_id
from @table_risks_groups t
	inner JOIN pr_products p on p.prd_product_code = t.prd_product_code
	inner join grpzon_zones gz on rcpl_zone_id = zonz_zone_id

	-- exception des produits par entité
	left join pr_products_branches pb	on	pb.prdb_product_code = p.prd_product_code 
										and pb.prdb_branch_id = @branch_id

	left join grpzon_zones_cies gzc on rcpl_zone_id = gzc.zonzc_zone_id and gzc.zonzc_company_id = isnull(pb.prdb_branch_id, p.prd_company_id)

--l'usage
select	gu.*,
		rcpl_risk_situation_id
from @table_risks_groups
	inner join grpusg_usages gu on rcpl_usage_id = usgu_usage_id
	




GO
