
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_policies_last_situation_children]
    @policies_ids_table type_table_id readonly,
    @return_variables bit = 0,
    @return_risks bit = 0,
    @return_also_retired_risks bit = 0
as 
BEGIN
    set nocount on;

    select pps.*
    from pol_policies pp
	   inner join @policies_ids_table pit on pit.id = pp.pol_policy_id
	   inner join pol_policies_situations pps on pps.pols_policy_situation_id = pp.pol_last_policy_situation_id

    select ac.*
    from vw_act_clients ac
	   inner join pol_policies pp on pp.pol_client_id = ac.cli_actor_id
	   inner join @policies_ids_table pit on pit.id = pp.pol_policy_id
	   

    select pc.*
    from pol_policies pp
	   inner join @policies_ids_table pit on pit.id = pp.pol_policy_id
	   inner join pol_policies_situations pps on pps.pols_policy_situation_id = pp.pol_last_policy_situation_id
	   inner join pol_cpl pc on pc.pcpl_policy_situation_id = pps.pols_policy_situation_id

    select ppsi.*
    from pol_policies pp
	   inner join @policies_ids_table pit on pit.id = pp.pol_policy_id
	   inner join pol_policies_situations pps on pps.pols_policy_situation_id = pp.pol_last_policy_situation_id
	   inner join pol_policies_situations_introducers ppsi on ppsi.polsi_policy_situation_id = pps.pols_policy_situation_id

    select pp.*
    from pol_policies pp
	   inner join @policies_ids_table pit on pit.id = pp.pol_policy_id
	   
    IF @return_variables = 1
    BEGIN
	   select pv.*, npv.*
	   from pol_policies pp
		  inner join @policies_ids_table pit on pit.id = pp.pol_policy_id
		  inner join pol_policies_situations pps on pps.pols_policy_situation_id = pp.pol_last_policy_situation_id
		  inner join pol_variables pv ON pv.polv_policy_situation_id = pps.pols_policy_situation_id
		  INNER JOIN pr_products_variables ppv ON ppv.prdv_variable_id = pv.polv_variable_id
		  INNER JOIN nm_products_variables npv ON npv.nmpv_variable_code = ppv.prdv_variable_code


	   select pvt.*, nt.*
	   from pol_policies pp
		  inner join @policies_ids_table pit on pit.id = pp.pol_policy_id
		  inner join pol_policies_situations pps on pps.pols_policy_situation_id = pp.pol_last_policy_situation_id
		  inner join pol_variables pv ON pv.polv_policy_situation_id = pps.pols_policy_situation_id
		  INNER JOIN pol_variables_taxes pvt ON pvt.polvt_policy_situation_id = pv.polv_policy_situation_id AND pvt.polvt_variable_id = pv.polv_variable_id
		  INNER JOIN acc_taxes at ON at.acctax_tax_id = pvt.polvt_tax_id
		  INNER JOIN nm_taxes nt ON nt.nmtax_tax_code = at.acctax_tax_code

    end

    IF @return_risks = 1
    BEGIN
	   
	   declare @risks_situations table(risk_situation_id int primary key, risk_id int)

	   -- risks selection
	   insert into @risks_situations(	risk_situation_id,			risk_id)
	   select						riss_risk_situation_id,		riss_risk_id
	   from ris_risks_situations rrs
		   INNER JOIN ris_risks rr on		   rrs.riss_risk_id = rr.ris_risk_id 
								    and rrs.riss_risk_situation_id = rr.ris_last_risk_situation_id
		   INNER JOIN @policies_ids_table pit ON pit.id = rr.ris_policy_id  
	   where	 (@return_also_retired_risks = 1 or rrs.riss_retire_date is null)

	   SELECT rr.*
	   FROM ris_risks rr
		  INNER JOIN @risks_situations rs ON rs.risk_id = rr.ris_risk_id

	   SELECT rrs.*
	   FROM ris_risks_situations rrs
		  INNER JOIN @risks_situations rs ON rs.risk_situation_id = rrs.riss_risk_situation_id

	   SELECT rv.*, npv.*
	   FROM ris_variables rv
		  INNER JOIN @risks_situations rs ON rs.risk_situation_id = rv.risv_risk_situation_id
		  INNER JOIN pr_products_variables ppv ON ppv.prdv_variable_id = rv.risv_variable_id
		  INNER JOIN nm_products_variables npv ON npv.nmpv_variable_code = ppv.prdv_variable_code

	   SELECT rvt.*, nt.*
	   FROM ris_variables_taxes rvt
		  INNER JOIN @risks_situations rs ON rs.risk_situation_id = rvt.risvt_risk_situation_id
		  INNER JOIN acc_taxes at ON at.acctax_tax_id = rvt.risvt_tax_id
		  INNER JOIN nm_taxes nt ON nt.nmtax_tax_code = at.acctax_tax_code

	   SELECT rc.* 
	   FROM ris_cpl rc
		  INNER JOIN @risks_situations rs ON rs.risk_situation_id = rc.rcpl_risk_situation_id

    END 

END 

		
    




GO
