
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_acc_cts_bordereaux_delete_by_table_id]
	@table_id type_table_id READONLY
AS

	/*
	DGE
	Autoriser uniquement la suppression des bordereaux de type PN et PR ( cts_acc_types.ctstyp_mark_item = false ).
	La suppression des autres bordereaux est interdite, car des modifications sont faites au niveau des clients/contrats/quittances.
	Voir le contenu de la table cts_acc_types.
	*/

	if exists ( select	1 
				from	acc_cts_bordereaux b
						inner join acc_cts_types act on act.ctstyp_type_code = b.ctsb_type_code and act.ctstyp_mark_item != 0
						inner join @table_id t on t.id = b.ctsb_bordereau_id )
	begin
		raiserror('Cannot delete a bordereau with a type <<mark item>> !', 16,1)
		return		
	END


	-- suppression de la collection de documents
	DECLARE @table_collections type_table_id


	INSERT INTO @table_collections
	SELECT	DISTINCT dc.dc_collection_id
	FROM	dbo.doc_collections dc
			INNER JOIN @table_id t ON t.id = dc.dc_id


	EXEC p_doc_collections_delete_by_table_id @table_collections


	delete	a
	from	acc_cts_bordereaux_accounts a
			inner join @table_id t on t.id = a.ctsa_bordereau_id
			
	delete	a
	from	acc_cts_bordereaux_introducers a
			inner join @table_id t on t.id = a.ctsbi_bordereau_id

	delete	a
	from	acc_cts_bordereaux_policies a
			inner join @table_id t on t.id = a.ctsp_bordereau_id

	delete	a
	from	acc_cts_bordereaux a
			inner join @table_id t on t.id = a.ctsb_bordereau_id


	

GO
