SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_risks_list]
(
	@return_complement bit = 0,
	@policy_id int = 0,
	@policy_situation_id int = 0,
	@risk_number ais_code_long = '%',
	@risk_id int = null,
	@risk_situation_id int = null,
	@movement_date date = null,
	@nature_code ais_code_short = '%',
	@return_obsolete bit = 0
) 
as 
	set nocount on 
	
	if isnull(@return_complement,0) = 1
	begin
		select s.*, c.*, ris_last_risk_situation_id = f.risk_last_situation_id, vrs.riss_ann_ti
		from ris_risks_situations s
			inner join dbo.f_risks_list(@policy_id, @risk_number, @risk_id, @risk_situation_id, @movement_date, @nature_code, @return_obsolete) f on s.riss_risk_situation_id = f.risk_situation_id
			left join ris_cpl c on riss_risk_situation_id = rcpl_risk_situation_id
			left join vw_ris_risks_situations_totals vrs on vrs.riss_risk_situation_id = s.riss_risk_situation_id
		  where	    (@return_obsolete = 1 or riss_obsolete = 0)
				and (@policy_situation_id = 0 or s.riss_policy_situation_id = @policy_situation_id)
	end
	else
	begin
		select s.*, ris_last_risk_situation_id = f.risk_last_situation_id, vrs.riss_ann_ti
		from ris_risks_situations s
			inner join dbo.f_risks_list(@policy_id, @risk_number, @risk_id, @risk_situation_id, @movement_date, @nature_code, @return_obsolete) f on s.riss_risk_situation_id = f.risk_situation_id
			left join vw_ris_risks_situations_totals vrs on vrs.riss_risk_situation_id = s.riss_risk_situation_id
		  where	    (@return_obsolete = 1 or riss_obsolete = 0)
				and (@policy_situation_id = 0 or s.riss_policy_situation_id = @policy_situation_id)
	end
		
	




GO
