SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_acc_cl_bordereaux_delete_by_table_id]
	@table_id type_table_id READONLY
as
begin

	set nocount on;

	/*
	DGE 06/02/2018
	Suppression des bordereaux de crédit sinistre (uniquement de type IS - issued). 
	*/

	if exists ( select	1 
				from	acc_accounts a
						inner join @table_id t	on	t.id = a.a_account_id
												and	a.a_lettering is not null)
	begin
		raiserror('Lettering not null, cannot delete bordereau !', 16,1)
		return		
	END



	if exists ( select	1 
				from	acc_cl_bordereaux_accounts b
						inner join @table_id t	on	t.id = b.clba_account_id
												and	b.clba_operation_code != 'IS' )
	begin
		raiserror('Cannot delete a bordereau with an operation code != IS !', 16,1)
		return		
	END



	-- liste de bordereaux affectés par la transaction
	declare @bdx type_table_id


	insert into @bdx(id)
	select	distinct a.clba_bordereau_id
	from	acc_cl_bordereaux_accounts a
			inner join @table_id t on t.id = a.clba_account_id



	delete	a
	from	acc_cl_bordereaux_accounts_details a
			inner join @table_id t on t.id = a.clbad_account_id

	delete	a
	from	acc_cl_bordereaux_accounts a
			inner join @table_id t on t.id = a.clba_account_id

	delete	a
	from	acc_accounts a
			inner join @table_id t on t.id = a.a_account_id


		
	declare @t type_table_id


	insert into @t(id)
	select	distinct b.id
	from	@bdx b
	where	not exists	(
						select	1
						from	acc_cl_bordereaux_accounts a
						where	a.clba_bordereau_id = b.id
						)

	-- suppression totale des bordereaux qui n'ont plus de détails
	delete	a
	from	acc_cl_bordereaux_variables a
			inner join @t t on t.id = a.clbvar_bordereau_id

	delete	a
	from	acc_cl_bordereaux_companies a
			inner join @t t on t.id = a.clbcie_bordereau_id

	delete	a
	from	acc_cl_bordereaux a
			inner join @t t on t.id = a.clb_bordereau_id 

			


end
GO
