
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_policy_risks_details]
	@policy_id int,
	@policy_situation_id int,
	@risk_situation_id int,
	@return_risks_retired bit = 1,
	@return_complement bit = 1,
	@return_variables bit = 1,
	@return_invoice_variables bit = 0,
	@return_obsolete bit = 0
as 
	set nocount on;
	
	declare @risks_situations TABLE(situation_id int)
	
	if @risk_situation_id > 0
	begin
		insert into @risks_situations	(	situation_id		)
		values							(	@risk_situation_id	)
	end
	else
	begin

		 if @policy_situation_id > 0 
		  begin

			 insert into @risks_situations (	situation_id	)
			 select							riss_risk_situation_id
			 from ris_risks_situations
				inner join ris_risks on		ris_risk_id = riss_risk_id 
										  and ris_last_risk_situation_id = riss_risk_situation_id
			 where		riss_policy_situation_id = @policy_situation_id
				    and ( @return_risks_retired = 1 or riss_retire_date is null)
				    and ( @return_obsolete = 1 or riss_obsolete = @return_obsolete)
		  end
		  else
		  begin
			 insert into @risks_situations (	situation_id	)
			 select							riss_risk_situation_id
			 from ris_risks_situations
				inner join ris_risks on		ris_risk_id = riss_risk_id 
										  and ris_last_risk_situation_id = riss_risk_situation_id
			 where		riss_policy_id = @policy_id
				    and ( @return_risks_retired = 1 or riss_retire_date is null)
				    and ( @return_obsolete = 1 or riss_obsolete = @return_obsolete)
		  end

	end
	
	if @return_complement = 1
	begin											
		select *
		from ris_risks_situations
			inner join @risks_situations on situation_id = riss_risk_situation_id
			inner join ris_risks on ris_risk_id = riss_risk_id
			left join ris_cpl on riss_risk_situation_id = rcpl_risk_situation_id -- returnam complementul cuplat cu situatia pentru ca asa avem nevoie de el pe client (si situatia si complementul se afla pe acelasi formular)
	end
	else
	begin
		select *
		from ris_risks_situations
			inner join @risks_situations on situation_id = riss_risk_situation_id
			inner join ris_risks on ris_risk_id = riss_risk_id
	end 

	if @return_variables = 1
	begin
		select v.*, n.*, p.prdv_product_code, p.prdv_variable_code, p.prdv_variable_date_setup, rrs.riss_formula_code
				--, pfv.pfv_variable_code__parent, npvt.*
		from ris_variables v
			inner join @risks_situations on situation_id = risv_risk_situation_id
			inner join ris_risks_situations rrs on rrs.riss_risk_situation_id = situation_id
			inner join pr_products_variables p on risv_variable_id = prdv_variable_id
			--inner join pr_products pp on pp.prd_product_code = p.prdv_product_code
			inner join nm_products_variables n on prdv_variable_code = nmpv_variable_code
			--inner join pr_families_variables pfv on pfv.pfv_family_code = p.prdv_family_code 
			--inner join nm_products_variables_types npvt on npvt.nmpvt_type_code = n.nmpv_type_code
		
		select	v.*, a.*, n.*, p.prdv_product_code, p.prdv_variable_code, p.prdv_variable_date_setup, rrs.riss_formula_code
		from ris_variables_taxes v
			inner join @risks_situations on situation_id = risvt_risk_situation_id
			inner join ris_risks_situations rrs on rrs.riss_risk_situation_id = situation_id
			inner join pr_products_variables p on risvt_variable_id = prdv_variable_id
			inner join acc_taxes a on	acctax_tax_id = risvt_tax_id	
			inner join nm_taxes n on acctax_tax_code = nmtax_tax_code

	end

	if @return_invoice_variables = 1
	BEGIN

		select airs.*
		from acc_invoices_risks_situations airs
			inner join @risks_situations on situation_id = airs.invris_risk_situation_id

		select	airv.*,
				rrs.riss_formula_code,
				npv.* --,
				--pfv.pfv_variable_code__parent,
				--npvt.*
		from acc_invoices_risks_variables airv
			inner join @risks_situations on situation_id = airv.invrisv_risk_situation_id
			inner join ris_risks_situations rrs on rrs.riss_risk_situation_id = situation_id
			inner join pr_products_variables ppv on ppv.prdv_variable_id = airv.invrisv_variable_id 
			inner join nm_products_variables npv on ppv.prdv_variable_code = npv.nmpv_variable_code
			--inner join pr_families_variables pfv on pfv.pfv_family_code = ppv.prdv_family_code
			--inner join nm_products_variables_types npvt on npvt.nmpvt_type_code = npv.nmpv_type_code

		select	airvct.*,
				rrs.riss_formula_code
		from acc_invoices_risks_variables_commission_taxes airvct
			inner join @risks_situations on situation_id = airvct.invrisvct_risk_situation_id
			inner join ris_risks_situations rrs on rrs.riss_risk_situation_id = situation_id

		select	airvt.*,
				rrs.riss_formula_code
		from acc_invoices_risks_variables_taxes airvt
			inner join @risks_situations on situation_id = airvt.invrisvt_risk_situation_id
			inner join ris_risks_situations rrs on rrs.riss_risk_situation_id = situation_id

	end










GO
