SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE procedure [dbo].[p_acc_transaction_delete_pec]
	@transaction_id int
as 

	set nocount on;

	/*

	DGE

	Suppression d'une transaction de type PEC(encaissement partiel)

	On a une quittance encaissée partiellement.

	Exemple:
		----------------------------
		t0
				DB	100 EUR
		----------------------------
		t1		S	100 EUR				(1)
				S  -100 EUR				(2)
				EC   80 EUR				(3)
				T    80 EUR				(4)
				DB   20 EUR				(5)


	Vérifications:
	- on vérifie que la transaction (4) est de type pec;
	- on vérifie que la quittance delta (5) est en DB;

	Actions:
	- supprimer la ligne de transaction (4)
	- remettre les quittances de 80 (3) et 20 EUR (5) en statut DB

	*/



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

	-- type != PEC

	if @type != 'PEC'
	begin
		
		raiserror('Transaction type != PEC.', 16,1)
		return

	end


	declare	@actor_id			int,
			@lettering			int,
			@ec_invoice_id		int,		-- quittance (3)
			@db_invoice_id		int			-- quittance (5)

	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 la quittance EC (3)

	select @ec_invoice_id = -1

	select	@ec_invoice_id = a.a_account_id
	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
	and		i.inv_operation_code__last = 'EC'

	if isnull(@ec_invoice_id, 0) <= 0
	begin
		raiserror('The EC invoice is not found.', 16,1)
		return
	end



	-- déterminer la quittance (5)
	select @db_invoice_id = @ec_invoice_id + 1			-- temporairement

	
	if not exists (		select	1 
						from	acc_invoices
						where	inv_invoice_id = @db_invoice_id
						and		inv_operation_code__last = 'DB' )
	begin
		raiserror('The delta invoice is not DB.', 16,1)
		return
	end


	-- suppression de la transaction (4)
	delete	acc_transactions where atrn_transaction_id = @transaction_id
	delete	acc_accounts where a_account_id = @transaction_id

	-- remise de la quittance EC 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_account_id = @ec_invoice_id

	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_account_id = @ec_invoice_id
GO
