
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_policies_risks_variables_taxes]
	@policies_situations type_table_id READONLY,
	@risks_situations type_table_id READONLY
as

-- selectie contracte
select p.*
from pol_policies_situations pps
	inner join @policies_situations on pols_policy_situation_id = id
	inner join pol_policies p on p.pol_policy_id = pps.pols_policy_id


--selectie situatii contracte
select pps.*
from pol_policies_situations pps
	inner join @policies_situations on pols_policy_situation_id = id
	
--selectie variabile contracte
select	v.*, 
		p.prdv_product_code, p.prdv_variable_date_setup, p.prdv_coef_monthly, p.prdv_coef_quarterly, p.prdv_coef_semi_annual, p.prdv_reductible, p.prdv_refoundable, p.prdv_refoundable_terminated, p.prdv_prorated_amd,
		npv.nmpv_variable_code
from pol_variables v
	inner join @policies_situations on polv_policy_situation_id = id
	inner join pr_products_variables p on v.polv_variable_id = p.prdv_variable_id
	inner join nm_products_variables npv on p.prdv_variable_code = npv.nmpv_variable_code

--selectie taxe variabile contracte
select	t.*, 
		a.acctax_tax_code, a.acctax_tax_date_setup, 
		npv.nmpv_variable_code,
		nt.*
from pol_variables_taxes t 
	inner join @policies_situations on polvt_policy_situation_id = id
	inner join pol_policies_situations pps on pps.pols_policy_situation_id = id
	inner join pol_policies pp on pp.pol_policy_id = pps.pols_policy_id
	inner join pr_products_variables p on t.polvt_variable_id = p.prdv_variable_id
	inner join nm_products_variables npv on p.prdv_variable_code = npv.nmpv_variable_code
	inner join acc_taxes a on t.polvt_tax_id = a.acctax_tax_id
	inner join nm_taxes nt on a.acctax_tax_code = nt.nmtax_tax_code
	
--selectie situatii riscuri
select rrs.*
from ris_risks_situations rrs
	inner join @risks_situations on rrs.riss_risk_situation_id = id

--selectie variabile riscuri
select	rv.*, 
		p.prdv_product_code, p.prdv_variable_date_setup, p.prdv_coef_monthly, p.prdv_coef_quarterly, p.prdv_coef_semi_annual, p.prdv_reductible, p.prdv_refoundable, p.prdv_refoundable_terminated, p.prdv_prorated_amd,
		npv.nmpv_variable_code
from ris_variables rv
	inner join @risks_situations on id = risv_risk_situation_id
	inner join pr_products_variables p on rv.risv_variable_id = p.prdv_variable_id
	inner join nm_products_variables npv on p.prdv_variable_code = npv.nmpv_variable_code

--selectie taxe variabile riscuri
select	rvt.*,
		a.acctax_tax_code, a.acctax_tax_date_setup, 
		npv.nmpv_variable_code,
		nt.*
from ris_variables_taxes rvt
	inner join @risks_situations on id = risvt_risk_situation_id
	inner join pr_products_variables p on rvt.risvt_variable_id = p.prdv_variable_id
	inner join nm_products_variables npv on p.prdv_variable_code = npv.nmpv_variable_code
	inner join acc_taxes a on rvt.risvt_tax_id = a.acctax_tax_id
	inner join nm_taxes nt on a.acctax_tax_code = nt.nmtax_tax_code















GO
