
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_risk_detail]
	@risk_situation_id int,
	@return_complement bit = 1,
	@return_variables bit = 1,
	@culture_code ais_code_short = 'en-US'		-- pour afficher les primes/franchises/capitaux dans le bon format
as 
	set nocount on;
	
	if @return_complement = 1
	begin											
		select *
		from ris_risks_situations
			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)
		where @risk_situation_id > 0 and riss_risk_situation_id = @risk_situation_id
	end
	else
	begin
		select *
		from ris_risks_situations
			inner join ris_risks on ris_risk_id = riss_risk_id
		where @risk_situation_id > 0 and riss_risk_situation_id = @risk_situation_id
	end 

	if @return_variables = 1
	begin

		select v.*, n.*, p.prdv_product_code, p.prdv_variable_code, p.prdv_variable_date_setup,
			risv_capital_formatted = dbo.f_format_variable(risv_capital, risv_fmt_code__capital, @culture_code, pp.pol_currency_code),
			risv_franchise_formatted = dbo.f_format_variable(risv_franchise, risv_fmt_code__franchise, @culture_code, pp.pol_currency_code),
			risv_franchise_maxi_formatted = dbo.f_format_variable(risv_franchise_maxi, risv_fmt_code__franchise_maxi, @culture_code, pp.pol_currency_code),
			risv_franchise_mini_formatted = dbo.f_format_variable(risv_franchise_mini, risv_fmt_code__franchise_mini, @culture_code, pp.pol_currency_code),
			risv_premium_formatted = dbo.f_format_variable(risv_premium, risv_fmt_code__premium, @culture_code, pp.pol_currency_code)

		from ris_variables v
			inner join pr_products_variables p on risv_variable_id = prdv_variable_id
			inner join nm_products_variables n on prdv_variable_code = nmpv_variable_code
			inner join ris_risks_situations r on r.riss_risk_situation_id = v.risv_risk_situation_id
			inner join pol_policies pp on pp.pol_policy_id = r.riss_policy_id
		where @risk_situation_id > 0 and risv_risk_situation_id = @risk_situation_id
		
		select	v.*, a.*, n.*, p.prdv_product_code, p.prdv_variable_code, p.prdv_variable_date_setup
		from ris_variables_taxes v
			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
		where @risk_situation_id > 0 and risvt_risk_situation_id = @risk_situation_id
		
	end











GO
