SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_tt_risks_situations]
	@risk_situations_table type_table_id readonly,
	@pol_policies_situations_table type_table_id readonly
as
	set NOCOUNT ON;
	
	-- luam in calcul toate situatiile risk din @risk_situations_table si cele care au policy situation in @pol_policies_situations_table
	declare @table type_table_id
	
	insert INTO @table(id)
	select id 
	from @risk_situations_table
	union -- face si distinct
	select riss_risk_situation_id
	from ris_risks_situations 
		inner join @pol_policies_situations_table p on riss_policy_situation_id = p.id
		
	
	
	select	ris.ris_entrance_date, ris.ris_risk_id,
			riss.*,
			riss_ann_tf = isnull(vrrst.riss_ann_tf,0), 
			riss_ann_taxes = isnull(vrrst.riss_ann_taxes,0), 
			riss_ann_ti = isnull(vrrst.riss_ann_ti,0), 
			nrm.rmvt_l1, nrm.rmvt_l2, nrm.rmvt_l3,
			pff.pff_l1,pff.pff_l2,pff.pff_l3,
			pfn.pfn_l1, pfn.pfn_l2, pfn.pfn_l3,
			usg.*,

			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 clscie_class_id is null then clsc_default_group else clscie_default_group end,
			clsc_default_class = case when clscie_class_id is null then clsc_default_class else clscie_default_class end,
			clsc_default_theft = case when clscie_class_id is null then clsc_default_theft else clscie_default_theft end,
			clsc_sra_group = case when clscie_class_id is null then clsc_sra_group else clscie_sra_group end,
			clsc_sra_class = case when clscie_class_id is null then clsc_sra_class else clscie_sra_class end,
			clsc_sra_repair = case when clscie_class_id is null then clsc_sra_repair else clscie_sra_repair end,
			gg.*,
			nb.*,

			rcpl.*

	from ris_risks_situations riss
		inner join @table on riss_risk_situation_id = id
		inner JOIN vw_ris_risks_situations_totals vrrst ON riss.riss_risk_situation_id = vrrst.riss_risk_situation_id
		inner join ris_risks ris on ris_risk_id = riss_risk_id
		inner join pol_policies_situations on riss.riss_policy_situation_id = pols_policy_situation_id
		inner join pol_policies on pol_policy_id = pols_policy_id
		inner JOIN pr_products on pol_product_code = prd_product_code
		inner join nm_risks_movements nrm on riss.riss_movement_code = nrm.rmvt_movement_code
		left JOIN pr_families_formulas pff ON pff.pff_family_code = prd_family_code and pff.pff_formula_code = riss.riss_formula_code
		left join ris_cpl rcpl on riss.riss_risk_situation_id = rcpl_risk_situation_id
		left join pr_families_natures pfn on pfn.pfn_family_code = prd_family_code and pfn.pfn_nature_code = riss.riss_nature_code
		left join grpusg_usages usg on usg.usgu_usage_id = rcpl.rcpl_usage_id
		
		left join grpcls_classes gc on gc.clsc_class_id = rcpl.rcpl_class_id
		left join grpcls_classes_cies gcc on		gcc.clscie_class_id = rcpl.rcpl_class_id
												and gcc.clscie_company_id = pol_company_id
		left join grpcls_groups gg on gg.clsg_group_code = gc.clsc_group_code
		left join grpcls_brands gb on		gb.clsb_group_code = gc.clsc_group_code
										and gb.clsb_brand_code = gc.clsc_brand_code
		left join nm_brands nb on nb.brd_brand_code = gb.clsb_brand_code
		


GO
