SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_policy_delete_last_situation_by_id]
	@policy_situation_id int
as


	-- DGE 23/03/2017
	-- suppression de la dernière situation d'un contrat


	set nocount on;

	if exists(	select	1
				from	pol_policies_situations ps
						inner join pol_policies p	on p.pol_policy_id = ps.pols_policy_id
				where	ps.pols_policy_situation_id = @policy_situation_id
				and		p.pol_last_policy_situation_id != @policy_situation_id)
	begin
		
		raiserror('MSG_ERR_LAST_SITUATION_DELETE_WRONG_SITUATION', 16, 1)
		return

	end


	-- sinistres
	if exists ( select	1
				from	claim_claims
				where	cl_policy_situation_id = @policy_situation_id )
	begin
		raiserror('MSG_ERR_LAST_SITUATION_DELETE_CLAIMS', 16, 1)
		return
	end

	-- invoices != CR / DB / AWR / 'INF'
	if exists ( select	1
				from	acc_invoices
				where	inv_policy_situation_id = @policy_situation_id
				and		inv_operation_code__last not in ('CR', 'DB', 'AWR', 'INF') )
	begin
		raiserror('MSG_ERR_LAST_SITUATION_DELETE_INVOICES', 16, 1)
		return
	end

	-- bordereaux
	if exists ( select	1
				from	acc_invoices i
						inner join acc_bordereaux_details abd on abd.bdrd_invoice_id = i.inv_invoice_id
				where	inv_policy_situation_id = @policy_situation_id)
	begin
		raiserror('MSG_ERR_LAST_SITUATION_DELETE_INVOICES_BDR', 16, 1)
		return
	end

	-- bordereaux de prélèvement
	if exists ( select	1
				from	acc_invoices i
						inner join acc_mpay_bordereaux_details abd on abd.mpaybd_invoice_id = i.inv_invoice_id
				where	inv_policy_situation_id = @policy_situation_id)
	begin
		raiserror('MSG_ERR_LAST_SITUATION_DELETE_INVOICES_BDR_MPAY', 16, 1)
		return
	end

	-- documents
	if exists (	select	1
				from	doc_collections_docs
				where	dcd_policy_situation_id = @policy_situation_id)
	begin
		raiserror('MSG_ERR_LAST_SITUATION_DELETE_DOCS', 16, 1)
		return
	end

	exec p_policy_situation_delete @policy_situation_id




GO
