SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc [dbo].[p_test_policy_delete_test_data]
	@client_id int = 0,
	@policy_id int = 0,
	@policy_situation_id int = 0,
	@risk_id int = 0,
	@risk_situation_id int = 0
	
as

-- exec p_sys_policy_delete_test_data @client_id = 0, @policy_id = 0, @policy_situation_id = 0, @risk_id = 0, @risk_situation_id = 0
	
if @risk_situation_id > 0
begin

	update ris_risks
	set ris_last_risk_situation_id = null,
		ris_situations_count = 0

	delete from ris_cpl where rcpl_risk_situation_id > @risk_situation_id
	
	delete from ris_variables_taxes where risvt_risk_situation_id > @risk_situation_id
	
	delete from ris_variables where risv_risk_situation_id > @risk_situation_id
	
	delete from ris_risks_situations where riss_risk_situation_id > @risk_situation_id
	
	--exec p_risk_set_last_situation 0 
end

if @risk_id > 0
begin

	update ris_risks
	set ris_last_risk_situation_id = null,
		ris_situations_count = 0

	delete d
	from ris_cpl d 
		inner join ris_risks_situations on rcpl_risk_situation_id = riss_risk_situation_id
	where riss_risk_id > @risk_id
	
	delete d
	from ris_variables_taxes d 
		inner join ris_variables on risvt_risk_situation_id = risv_risk_situation_id 
		inner join ris_risks_situations on risv_risk_situation_id = riss_risk_situation_id
	where riss_risk_id > @risk_id
	
	delete d
	from ris_variables d 
		inner join ris_risks_situations on risv_risk_situation_id = riss_risk_situation_id
	where riss_risk_id > @risk_id
	
	delete d
	from ris_risks_situations d 
	where riss_risk_id > @risk_id
	
	delete d
	from ris_risks d 
	where ris_risk_id > @risk_id
	
	--exec p_risk_set_last_situation 0 
	
end

if @policy_situation_id > 0
begin

	update ris_risks
	set ris_last_risk_situation_id = null,
		ris_situations_count = 0
		
	update pol_policies 
	set pol_last_policy_situation_id = null,
		pol_situations_count = 0
		
	delete d
	from ris_variables_taxes d 
		inner join ris_variables on risvt_risk_situation_id = risv_risk_situation_id  
		inner join ris_risks_situations on risv_risk_situation_id = riss_risk_situation_id
	where riss_policy_situation_id > @policy_situation_id

	delete d
	from ris_variables d 
		inner join ris_risks_situations on risv_risk_situation_id = riss_risk_situation_id
	where riss_policy_situation_id > @policy_situation_id

	delete d
	from ris_cpl d 
		inner join ris_risks_situations on rcpl_risk_situation_id = riss_risk_situation_id
	where riss_policy_situation_id > @policy_situation_id
	
	delete d
	from ris_risks_situations d 
	where riss_policy_situation_id > @policy_situation_id

	delete d
	from pol_variables_taxes d 
		inner join pol_variables on polvt_policy_situation_id = polv_policy_situation_id
		inner join pol_policies_situations on polv_policy_situation_id = pols_policy_situation_id
	where pols_policy_situation_id > @policy_situation_id
	
	delete d
	from pol_variables d 
		inner join pol_policies_situations on polv_policy_situation_id = pols_policy_situation_id
	where pols_policy_situation_id > @policy_situation_id
	
	
	delete d
	from pol_cpl d 
		inner join pol_policies_situations on pcpl_policy_situation_id = pols_policy_situation_id
	where pols_policy_situation_id > @policy_situation_id
	
	
	delete d
	from pol_policies_situations_introducers d 
		inner join pol_policies_situations on polsi_policy_situation_id = pols_policy_situation_id
	where pols_policy_situation_id > @policy_situation_id

	delete d
	from pol_policies_situations_repartitions d 
		inner join pol_policies_situations on polsr_policy_situation_id = pols_policy_situation_id
	where pols_policy_situation_id > @policy_situation_id

	
	delete d
	from pol_policies_situations d 
	where pols_policy_situation_id > @policy_situation_id
	
	--exec p_risk_set_last_situation 0 
	
	--exec p_policy_set_last_situation 0
	
end


if @policy_id > 0
begin

	update ris_risks
	set ris_last_risk_situation_id = null,
		ris_situations_count = 0
		
	update pol_policies 
	set pol_last_policy_situation_id = null,
		pol_situations_count = 0

	delete d
	from ris_cpl d 
		inner join ris_risks_situations on rcpl_risk_situation_id = riss_risk_situation_id
		inner join pol_policies on riss_policy_id = pol_policy_id 
	where pol_policy_id > @policy_id
	
	delete d
	from ris_variables_taxes d  
		inner join ris_variables on risvt_risk_situation_id = risv_risk_situation_id 
		inner join ris_risks_situations on risv_risk_situation_id = riss_risk_situation_id
		inner join pol_policies on riss_policy_id = pol_policy_id 
	where pol_policy_id > @policy_id
	
	delete d
	from ris_variables d 
		inner join ris_risks_situations on risv_risk_situation_id = riss_risk_situation_id
		inner join pol_policies on riss_policy_id = pol_policy_id 
	where pol_policy_id > @policy_id
	
	
	delete d
	from ris_risks_situations d 
		inner join pol_policies on riss_policy_id = pol_policy_id 
	where pol_policy_id > @policy_id
	
	delete d
	from ris_risks d 
		inner join pol_policies on ris_policy_id = pol_policy_id 
	where pol_policy_id > @policy_id
	
	delete d
	from pol_variables_taxes d
		inner join pol_variables on polvt_policy_situation_id = polv_policy_situation_id
		inner join pol_policies_situations on polv_policy_situation_id = pols_policy_situation_id
	where pols_policy_id > @policy_id
	
	
	delete d
	from pol_variables d 
		inner join pol_policies_situations on polv_policy_situation_id = pols_policy_situation_id
	where pols_policy_id > @policy_id
	
	delete d
	from pol_cpl d 
		inner join pol_policies_situations on pcpl_policy_situation_id = pols_policy_situation_id
	where pols_policy_id > @policy_id
	
	delete d
	from pol_policies_situations_introducers d 
		inner join pol_policies_situations on polsi_policy_situation_id = pols_policy_situation_id
	where pols_policy_id > @policy_id

	delete d
	from pol_policies_situations_repartitions d 
		inner join pol_policies_situations on polsr_policy_situation_id = pols_policy_situation_id
	where pols_policy_id > @policy_id

	
	delete d
	from pol_policies_situations d 
		inner join pol_policies on pols_policy_id = pol_policy_id 
	where pol_policy_id > @policy_id
	
	delete d
	from pol_policies d 
	where pol_policy_id > @policy_id
	
	--exec p_risk_set_last_situation 0 
	
	--exec p_policy_set_last_situation 0
	
end


if @client_id > 0
begin

	update ris_risks
	set ris_last_risk_situation_id = null,
		ris_situations_count = 0
		
	update pol_policies 
	set pol_last_policy_situation_id = null,
		pol_situations_count = 0

	delete d
	from ris_cpl d 
		inner join ris_risks_situations on rcpl_risk_situation_id = riss_risk_situation_id
		inner join pol_policies on riss_policy_id = pol_policy_id 
	where pol_client_id > @client_id
	
	delete d
	from ris_variables_taxes d 
		inner join ris_variables on risvt_risk_situation_id = risv_risk_situation_id 
		inner join ris_risks_situations on risv_risk_situation_id = riss_risk_situation_id
		inner join pol_policies on riss_policy_id = pol_policy_id 
	where pol_client_id > @client_id
	
	delete d
	from ris_variables d 
		inner join ris_risks_situations on risv_risk_situation_id = riss_risk_situation_id
		inner join pol_policies on riss_policy_id = pol_policy_id 
	where pol_client_id > @client_id
	
	
	delete d
	from ris_risks_situations d 
		inner join pol_policies on riss_policy_id = pol_policy_id 
	where pol_client_id > @client_id
	
	delete d
	from ris_risks d 
		inner join pol_policies on ris_policy_id = pol_policy_id 
	where pol_client_id > @client_id
	
	delete d
	from pol_variables_taxes d 
		inner join pol_variables on polvt_policy_situation_id = polv_policy_situation_id 
		inner join pol_policies_situations on polv_policy_situation_id = pols_policy_situation_id
		inner join pol_policies on pols_policy_id = pol_policy_id 
	where pol_client_id > @client_id
	
	delete d
	from pol_variables d 
		inner join pol_policies_situations on polv_policy_situation_id = pols_policy_situation_id
		inner join pol_policies on pols_policy_id = pol_policy_id 
	where pol_client_id > @client_id
	
	delete d
	from pol_cpl d 
		inner join pol_policies_situations on pcpl_policy_situation_id = pols_policy_situation_id
		inner join pol_policies on pols_policy_id = pol_policy_id 
	where pol_client_id > @client_id
	
	delete d
	from pol_policies_situations_introducers d 
		inner join pol_policies_situations on polsi_policy_situation_id = pols_policy_situation_id
		inner join pol_policies on pols_policy_id = pol_policy_id 
	where pol_client_id > @client_id

	delete d
	from pol_policies_situations_repartitions d 
		inner join pol_policies_situations on polsr_policy_situation_id = pols_policy_situation_id
		inner join pol_policies on pols_policy_id = pol_policy_id 
	where pol_client_id > @client_id
	
	delete d
	from pol_policies_situations d 
		inner join pol_policies on pols_policy_id = pol_policy_id 
	where pol_client_id > @client_id
	
	delete d
	from pol_policies d 
	where pol_client_id > @client_id
	
	delete d
	from act_cpl d
	where acpl_actor_id > @client_id
	
	delete d
	from act_clients d
	where cli_actor_id > @client_id

	delete d 
	from	act_accounts d
			inner join act_actors a on a.act_actor_id = d.acc_actor_id
	where act_actor_id > @client_id and act_type_code = 'CLI'

	
	delete d 
	from act_actors d
	where act_actor_id > @client_id and act_type_code = 'CLI'
	
	
	--exec p_risk_set_last_situation 0 
	
	--exec p_policy_set_last_situation 0
	
end









GO
