Issue with reversal documents

Contents 1 Brief summary of the issue 2 Case I 2.1 Scenario 2.2 Module Script 2.3 To take into account 3 Case II 3.1 Scenario 3.2 Module Script 3.3 To take into account 4 Case III 4.1 Scenario 4.2 Module Script 4.3 To take into account 5 Case IV 5.1 Scenario 5.2 Module Script 5.3 To take into account 6 Useful SQL Queries

Brief summary of the issue

Due to this changeset introduced in OB3 MP5, and 2.50 MP36, when voiding a processed purchase or sales invoice (or credit memo), system was creating its reversal in status Completed, instead of Voided. This may cause some reports to show invoices that shouldn't be shown, or adding amounts of invoices that shouldn't be taken into account. This bad behavior was reported in this issue.

A fix for this issue is published in 2.50 MP37.1, 2.50 MP38.1, OB 3 MP6.2 and OB 3 MP7.2, and higher versions. The fix will make your system behave correctly for new invoices that are voided. Regarding the invoices that could be in Completed status, while it should be in Voided status, a module script will perform an update IF USER HASN'T REACTIVATED THEM. Let's see with a simple example

Case I

Scenario

User creates invoice A, process it, and voids it:

• Document type: AP/AR Invoice
• Documentno: A
• Amount: $1,000
• Status: Void

System will create another invoice:

• Document Type: Purchase/Sales Reversal Invoice
• Documentno: A'
• Amount: -$1,000
• Status: Completed

Module Script

If user updates now to an MP with the fix, and APR module is installed, a module script will change the document status for reversal invoice A' to Voided.

To take into account

Invoices are correctly fixed through the module script in case APR is installed. In other case, those must been fixed through database, updating the document status to Voided ('VO')

Case II

Scenario

If user re-activates the reversal invoice A' (as it is in completed status, user is able to re-activate it), it will change to Draft status.

Module Script

If user updates now to an MP with the fix, the module script will NOT change the document status for reversal invoice A'.

To take into account

Invoices can be fixed by updating the document status to Voided ('VO'). Please notice that these invoices must not be completed, nor voided through UI. The update must be done from Draft status to Voided status.

Case III

Scenario

If, once the invoice has been re-activated, user completes it, realize that a payment plan will be created for it (or a payment if not in an APR environment). The amounts of this payment plan will present debts with the business partner that are not real, as belongs to operations that have been voided.

Module Script

If user updates now to an MP with the fix, the module script will NOT change the document status for reversal invoice A', even if it is in Completed status.

To take into account

Invoices have different solutions in case APR is installed or not:

• If APR module is installed, invoice must be reactivated again, and then updated from document status Draft to Voided ('VO', trough database). This way, no payment plan will be created.
• If the instance is running with the classic receivables & payables flow, then the invoice must be processed, then updated ('VO', through database) to Voided status, and then the debt-payments of the invoice A, and the ones of invoice A' must be canceled each other in a settlement.

Case IV

Scenario

If the invoice A' is voided, then system will automatically create one more invoice A'', in completed status. This A'' invoice can, as well, be voided, etc.

Module Script

The A'' invoice, if it has not been reactivated, will be turned by the module script to Voided status. In fact, it should be removed from the system.

To take into account

In order to have everything ok, only A and A' invoices must exist in the system. The only fix is to reactivate all the A'', A''', etc invoices (if in voided status, turn to completed status through DB), then re-activate and delete them.

Useful SQL Queries

In order to detect invoices in the system that could be in any of these scenarios, please run the following SQL query:

select documentno, (select name from c_doctype dt where dt.c_doctype_id=i.c_doctype_id), docstatus
from c_invoice i
where docstatus <> 'VO'
  and c_doctype_id in (select coalesce(c_doctype_reversed_id, c_doctype_id) from c_doctype where docbasetype in ('API','ARI','APC','ARC'))
  and exists (select 1 from c_invoice_reverse r where r.c_invoice_id=i.c_invoice_id);

Please notice that this query may show invoices that are correct, and don't need any update. User must have a look into it to determine with 100% security it must be updated.

In order to update to Voided status a set of invoices, please use the query

update c_invoice
set docstatus='VO'
where ...