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

--ATENTIE AICI ce tabele selectez

--AICI AM PUS ca tabela contractele, nu situatiile, s-ar putea sa avem nevoie de tabela de situatii in alta ipostaza
SELECT pol_policy_id = pps.pols_policy_id, pps.pols_policy_id, pps.pols_incomplete, pps.pols_policy_situation_id
FROM pol_policies_situations pps
	inner JOIN @policies_situations ps ON ps.id = pps.pols_policy_situation_id

--AICI AM PUS ca tabela riscurile, nu situatiile lor, tabela de situatii este folosita mai jos pentru formulele selectate si poate pentru alte date pe viitor
SELECT ris_risk_id = rrs.riss_risk_id, rrs.riss_risk_situation_id, rrs.riss_risk_id, rrs.riss_policy_id, rrs.riss_policy_situation_id
FROM ris_risks_situations rrs
	INNER JOIN @risks_situations rs ON rs.id = rrs.riss_risk_situation_id
	
--selectie variabile contracte, pentru calcul avem nevoie doar de coduri
select	v.*, 
		npv.nmpv_variable_code, 
		p.prdv_variable_date_setup
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


-- taxe contracte
select  pvt.*
from pol_variables v
	inner join @policies_situations on polv_policy_situation_id = id
	inner join pol_variables_taxes pvt on pvt.polvt_policy_situation_id = v.polv_policy_situation_id and pvt.polvt_variable_id = v.polv_variable_id
	
--selectie variabile riscuri, pentru calcul avem nevoie doar de coduri
select	rv.*,
		npv.nmpv_variable_code, 
		p.prdv_variable_date_setup
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.*
from ris_variables rv
	inner join @risks_situations on id = risv_risk_situation_id
	inner join ris_variables_taxes rvt on rvt.risvt_risk_situation_id = rv.risv_risk_situation_id and rvt.risvt_variable_id = rv.risv_variable_id


--selectie formule riscuri, avem nevoie sa stim care e cea curenta
select rrs.riss_risk_situation_id, rrs.riss_formula_code
from ris_risks_situations rrs
	inner join @risks_situations on id = riss_risk_situation_id





GO
