How do you undo accounting in R12?


R12 does not have any standard functionality to undo accounting. If you find accounting that is incorrect due to a bug, a datafix bug will need to be logged to provide you a script to undo accounting. If development determines that the accounting is incorrect and needs to be reversed/undone, they will provide you the appropriate instructions and corresponding scripts/patches.

The ap_undo_acctg.sql is a centralized script for Undo Accounting to be used in AP. If the accounting is transferred, this script will create a manual entry with a NEW event id that reverses the original entry, change the original entry to a manual entry with a NEW event id, reset the status of the original event to unprocessed and also reset the posted flags for transactions assigned the original event id. The original event id, the reversed event id and the new event id are stored in the backup table created , namely AP_undo_event_log. If the accounting is NOT transferred, the script will delete the accounting and reset the original event to be accounted again, but it will not create a reversing entry.   Example of undo for transferred accounting:
Before Undo
1 Event:
Event_id = 123456, Event Type = Invoice Validated, Event Status = P

After Undo
3 Events:
Event_id = 123456, Event Type = Invoice Validated, Event Status = U  -- The original event id remains associated with the transaction data and the accounting status is reset to allow the new/corrected accounting to be created.
Event_id = 234567, Event Type = Manual, Event Status = P -- New event id created for the reversal of the original accounting header and lines.
Event_id = 234568, Event Type = Manual, Event Status = P -- New event id assigned to the original accounting header and lines.
Behavior of undo GL date for Single and Bulk Undo
1.Single Transaction Undo accounting API
AP_ACCTG_DATA_FIX_PKG.Undo_Accounting(l_source_table, l_source_id, l_event_id, p_calling_sequence, l_bug_Id, l_gl_date);
Priority 1 : Use transaction Accounting Date, if it is in open period.  Irrespective of p_gl_date parameter passed or not.  If transaction Accounting Date is in closed period then Priority 2.
Priority 2 :
Case A: p_gl_date, if this parameter is passed to API use p_gl_date, if p_gl_date is in open period.  If p_gl_date is not in open period undo API return with Error message.
Case B: p_gl_date, if this parameter is NOT passed to API, Undo api calculates Oldest open accounting date.       
If no open period available, use transaction accounting date even though it is in closed period, so that XLA API will throw error.
2.  Bulk Undo Accounting undo_acctg_entries()
      (p_bug_no            => l_bug_no,
       p_driver_table      => l_driver_table,
       p_calling_sequence  => l_calling_sequence
Case 1: proposed_undo_date value is present in driver table.  Undo API just checks as proposed_undo_date is in open period or not.  Use proposed_undo_date, if it is in open period.  If proposed_undo_date values falls under closed period, undo accounting script will fail, it will NOT take open period accounting date or sysdate or transaction accounting date automatically.
 Case 2: proposed_undo_date value is NOT present or NULL in driver table         
Priority 1 : Use transaction accounting date, if it in open period.          
Priotity 2 : Use SYSDATE, if transaction accounting date is in closed period and SYSDATE is in open period.  Any other case return error.

The script utilizes a new package to undo/reverse accounting. The package name is AP_ACCTG_DATA_FIX_PKG and it should be created/compiled before running the ap_undo_acctg.sql script. If development provides you the undo script for a data fix, it should include the files for the new package and instructions to compile the package before running the script e.g.

1. Please download and compile apgdfals.pls and apgdfalb.pls
For R12.0:
Please download and apply patch the latest consolidated checkin for undo - Patch 14082924  to get the latest Generic Datafix Package with the latest undo accounting code.
For R12.1:
Please download and apply patch the latest consolidated checkin for undo - Patch 14082924  to get the latest Generic Datafix Package with the latest undo accounting code.  And if AP_ACCTG_DATA_FIX_PKG Package Body Invalid, see Doc Id 1335220.1 for additional patches.

2. Run ap_undo_acctg.sql with the following parameters:
  • P_EVENT_ID -- This can be null if all of the events related to an invoice or payment are to be unaccounted.
  • P_SOURCE_ID -- The invoice id or check id to be unaccounted.
  • P_BUG_ID -- The bug number under which the fix was provided. This is logged in the AP_undo_event_log table so we can keep track of which bugs are responsible for the undo of which events.
  • GL_DATE -- If transaction is in closed period, then enter GL date to which it has to be swept. Format like 01-APR-2009.  If left blank the GL Date will default to the latest Open Period. 
  • L_USER_NAME*** -- Your user name (case insensitive)
  • L_RESP_NAME ***-- Responsibility Name(Case Sensitive)
***Make sure you are using a responsibility name and user name for a payables responsibility and associated user that have access to run accounting for the subject transaction.
3.  Run the Transfer Journal Entries to GL process to transfer the reversing entries. The reversing entries created by the undo are not automatically transferred. You MUST run the Transfer Journal Entries to GL program after you run any data fix that uses the undo fix.  And you must run with Process Category parameter = Blank or 'Manual' to pick up the reversing entries.
4.   If the undo included transferred accounting, please follow the instructions in Note 553484.1 to rebuild the Trial Balance.  This step will remove the old/undone accounting from the xla_trial_balances table and from the Trial Balance report.