SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_acc_transaction_delete_ec_aec_rmb]
	@transaction_id int
as 

	set nocount on;

	/*

	DGE

	Suppression d'une transaction de type EC(encaissement), AEC (encaissement avec acomptes), RMB (remboursement)

	On a N quittances et M acomptes qui sont encaissés/remboursés.

	Si le montant de la transaction est supérieur a la somme des lignes, on crée un acompte de différence (delta).


	Vérifications:
	- on vérifie que la transaction est de type aec;
	- il faut que l'acompte généré (delta) soit non repris, s'il existe

	Actions:
	- supprimer la ligne de transaction
	- remettre la/les quittance(s) en statut DB
	- supprimer l'acompte delta
	- passer tous les acomptes en non repris et remettre leur lettrage transaction 
	

	*/



	declare @type ais_code_short 
	
	select	@type = isnull(atrn_type_code, '')
	from	acc_transactions
	where	atrn_transaction_id = @transaction_id

	-- type != EC/AEC/RMB

	if @type not in('EC', 'AEC', 'RMB' )
	begin
		
		raiserror('Transaction type != EC/AEC/RMB.', 16,1)
		return

	end


	declare	@actor_id			int,
			@lettering			int,
			@delta_advance_id	int

	select	@actor_id = a_actor_id,
			@lettering = a_lettering
	from	acc_accounts
			inner join acc_transactions on atrn_transaction_id = a_account_id
	where	a_account_id = @transaction_id

	-- déterminer l'id de l'acompte DIFF (delta)
	select @delta_advance_id = -1

	if @type = 'AEC' 
	begin

		select	@delta_advance_id = a.a_account_id
		from	acc_accounts a
				inner join acc_advances adv on adv.adv_advance_id = a.a_account_id
		where	a.a_actor_id = @actor_id
		and		a.a_lettering = @lettering
		and		adv.adv_taken = 0


		if isnull(@delta_advance_id, 0) <= 0
		begin
			raiserror('The delta advance is taken.', 16,1)
			return
		end

	end


	-- suppression de la transaction d'encaissement
	delete	acc_transactions where atrn_transaction_id = @transaction_id
	delete	acc_accounts where a_account_id = @transaction_id

	-- passage des acomptes en "non repris"
	update	adv
	set		adv.adv_taken = 0
	from	acc_accounts a
			inner join acc_advances adv on adv.adv_advance_id = a.a_account_id
	where	a.a_actor_id = @actor_id
	and		a.a_lettering = @lettering
	and		a.a_account_id != @delta_advance_id

	-- MAJ des lettrages des acomptes
	update	a
	set		a.a_lettering = adv.adv_lettering
	from	acc_accounts a
			inner join acc_advances adv on adv.adv_advance_id = a.a_account_id
	where	a.a_actor_id = @actor_id
	and		a.a_lettering = @lettering
	and		a.a_account_id != @delta_advance_id


	-- remise des quittances en DB 
	update	i
	set		i.inv_ec_date = null, 
			i.inv_operation_code__last = 'DB'
	from	acc_accounts a
			inner join acc_invoices i on i.inv_invoice_id = a.a_account_id
	where	a.a_actor_id = @actor_id
	and		a.a_lettering = @lettering


	delete	o
	from	acc_accounts a
			inner join acc_invoices i on i.inv_invoice_id = a.a_account_id
			inner join acc_invoices_operations o on o.invop_invoice_id = i.inv_invoice_id and o.invop_operation_code in ( 'ec', 'rmb' )
	where	a.a_actor_id = @actor_id
	and		a.a_lettering = @lettering

	-- suppression de l'acompte delta
	delete acc_advances where adv_advance_id = @delta_advance_id
	delete acc_accounts where a_account_id = @delta_advance_id			


	-- suppression du lettrage transaction
	update	acc_accounts
	set		a_lettering = null
	where	a_actor_id = @actor_id
	and		a_lettering = @lettering
	and		a_type_code = 'I'


GO
