SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_test_policy_delete_by_id]
	@policy_id int
as


	-- DGE 07/10/2016
	-- suppression d'un contrat par id


	set nocount on;


	-- suppression des sinistres - à tester
	declare @claim_id int

	declare c_claims insensitive cursor 
	for	select	cl_claim_id
		from	claim_claims 
				inner join pol_policies_situations on pols_policy_situation_id = cl_policy_situation_id
		where	pols_policy_id = @policy_id

	open c_claims

	fetch next from c_claims into @claim_id

	while @@FETCH_STATUS = 0
	begin

		exec p_test_claim_delete_by_id @claim_id

		fetch next from c_claims into @claim_id
	end

	close c_claims
	deallocate c_claims



	-- suppression de la compta

	delete	d
	from	acc_invoices_operations d
			inner join acc_invoices i on i.inv_invoice_id = d.invop_invoice_id
			inner join pol_policies_situations ps on ps.pols_policy_situation_id = i.inv_policy_situation_id
	where	ps.pols_policy_id = @policy_id

	delete	d
	from	acc_invoices_policies_variables_commission_taxes d
			inner join acc_invoices i on i.inv_invoice_id = d.invpolvct_invoice_id
			inner join pol_policies_situations ps on ps.pols_policy_situation_id = i.inv_policy_situation_id
	where	ps.pols_policy_id = @policy_id			

	delete	d
	from	acc_invoices_policies_variables_taxes d
			inner join acc_invoices i on i.inv_invoice_id = d.invpolvt_invoice_id
			inner join pol_policies_situations ps on ps.pols_policy_situation_id = i.inv_policy_situation_id
	where	ps.pols_policy_id = @policy_id	

	delete	d
	from	acc_invoices_policies_variables d
			inner join acc_invoices i on i.inv_invoice_id = d.invpolv_invoice_id
			inner join pol_policies_situations ps on ps.pols_policy_situation_id = i.inv_policy_situation_id
	where	ps.pols_policy_id = @policy_id	

	delete	d
	from	acc_invoices_policies_variables_introducers d
			inner join acc_invoices i on i.inv_invoice_id = d.invpolvi_invoice_id
			inner join pol_policies_situations ps on ps.pols_policy_situation_id = i.inv_policy_situation_id
	where	ps.pols_policy_id = @policy_id	

	delete	d
	from	acc_invoices_risks_variables_commission_taxes d
			inner join acc_invoices i on i.inv_invoice_id = d.invrisvct_invoice_id
			inner join pol_policies_situations ps on ps.pols_policy_situation_id = i.inv_policy_situation_id
	where	ps.pols_policy_id = @policy_id	

	delete	d
	from	acc_invoices_risks_variables_taxes d
			inner join acc_invoices i on i.inv_invoice_id = d.invrisvt_invoice_id
			inner join pol_policies_situations ps on ps.pols_policy_situation_id = i.inv_policy_situation_id
	where	ps.pols_policy_id = @policy_id	

	delete	d
	from	acc_invoices_risks_variables d
			inner join acc_invoices i on i.inv_invoice_id = d.invrisv_invoice_id
			inner join pol_policies_situations ps on ps.pols_policy_situation_id = i.inv_policy_situation_id
	where	ps.pols_policy_id = @policy_id	

	delete	d
	from	acc_invoices_risks_variables_introducers d
			inner join acc_invoices i on i.inv_invoice_id = d.invrisvi_invoice_id
			inner join pol_policies_situations ps on ps.pols_policy_situation_id = i.inv_policy_situation_id
	where	ps.pols_policy_id = @policy_id	

	delete	d
	from	ris_risks_situations_main_invoices d
			inner join acc_invoices i on i.inv_invoice_id = d.rismi_invoice_id
			inner join pol_policies_situations ps on ps.pols_policy_situation_id = i.inv_policy_situation_id
	where	ps.pols_policy_id = @policy_id	

	delete	d
	from	acc_invoices_risks_situations d
			inner join acc_invoices i on i.inv_invoice_id = d.invris_invoice_id
			inner join pol_policies_situations ps on ps.pols_policy_situation_id = i.inv_policy_situation_id
	where	ps.pols_policy_id = @policy_id	


	delete	d
	from	pol_policies_situations_main_invoices d
			inner join pol_policies_situations ps on ps.pols_policy_situation_id = d.polmi_policy_situation_id
	where	ps.pols_policy_id = @policy_id	


	declare	@invoices type_table_id

	insert into @invoices
	select distinct d.inv_invoice_id
	from	acc_invoices d
			inner join pol_policies_situations ps on ps.pols_policy_situation_id = d.inv_policy_situation_id
	where	ps.pols_policy_id = @policy_id	

	-- acc_invoices
	delete	dst
	from	acc_invoices dst
			inner join @invoices ai on dst.inv_invoice_id = ai.id

	-- acc_accounts
	delete	dst
	from	acc_accounts dst
			inner join @invoices ai on dst.a_account_id = ai.id
	

	-- suppression des risques
	update	ris_risks
	set		ris_last_risk_situation_id = null,
			ris_situations_count = 0
	where	ris_policy_id = @policy_id


	delete	c
	from	ris_cpl c
			inner join ris_risks_situations rs on rs.riss_risk_situation_id = c.rcpl_risk_situation_id
	where	riss_policy_id = @policy_id

	delete	v
	from	ris_variables_taxes v 
			inner join ris_risks_situations rs on rs.riss_risk_situation_id = v.risvt_risk_situation_id
	where	riss_policy_id = @policy_id

	delete	v
	from	ris_variables v
			inner join ris_risks_situations rs on rs.riss_risk_situation_id = v.risv_risk_situation_id
	where	riss_policy_id = @policy_id

	delete	s
	from	ris_risks_situations s
	where	riss_policy_id = @policy_id

	delete	r
	from	ris_risks r
	where	ris_policy_id = @policy_id



	-- suppression du contrat

	update	pol_policies 
	set		pol_last_policy_situation_id = null,
			pol_situations_count = 0
	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 d 
	where	pols_policy_id = @policy_id

	delete d
	from	pol_policies d 
	where pol_policy_id = @policy_id

GO
