Symptoms

• The Payables Accounting Process (Create Accounting / XLAACCUP) reports events in error for one or more invoices.
• Affected invoices cannot be posted to the General Ledger.
• The Accounting Program log shows: "Unit Processor completed normally with some events in error."
• Invoices may carry DIST VARIANCE or LINE VARIANCE holds that cannot be resolved through normal validation.

Root Cause

A data inconsistency exists between the AP invoice distribution tables and the eBusiness Tax (ZX) tables. Records in AP_INVOICE_DISTRIBUTIONS_ALL or AP_SELF_ASSESSED_TAX_DIST_ALL have SUMMARY_TAX_LINE_ID and DETAIL_TAX_DIST_ID populated — meaning they reference tax distribution records in ZX_REC_NREC_DIST — but those corresponding records do not exist in ZX_REC_NREC_DIST. The ASSOCIATED_CHILD_FROZEN_FLAG on the associated ZX_LINES records is set to 'Y', confirming the distributions are in a frozen state.

When the Subledger Accounting (XLA) engine processes these invoices it cannot resolve the tax distribution pointers, causing accounting to fail.

Known trigger scenario (Oracle Bug 9774707): Tax distributions for Prepayment Application/Unapplication lines were generated after the parent distribution had already been stamped with an accounting event. When AP Validation was rerun, it could not stamp the new tax distribution with an accounting event because the architecture requires all prepayment application taxes to share the same event as their parent. The result is tax distributions permanently stuck in an unaccounted state.

Identification Query — finds all affected invoices across the instance

SELECT DISTINCT org_id, invoice_id, invoice_date
  FROM ap_invoices_all ai
 WHERE ai.invoice_id IN (
   -- Tax distributions in AP with no matching record in ZX
   SELECT dist.invoice_id
     FROM ap_invoice_distributions_all dist
    WHERE NVL(dist.historical_flag,'N') <> 'Y'
      AND dist.summary_tax_line_id IS NOT NULL
      AND dist.detail_tax_dist_id IS NOT NULL
      AND NOT EXISTS (
            SELECT 1 FROM zx_rec_nrec_dist zd
             WHERE zd.rec_nrec_tax_dist_id = dist.detail_tax_dist_id
               AND zd.summary_tax_line_id = dist.summary_tax_line_id)
   UNION
   -- Same check for self-assessed tax distributions
   SELECT sa_dist.invoice_id
     FROM ap_self_assessed_tax_dist_all sa_dist
    WHERE sa_dist.summary_tax_line_id IS NOT NULL
      AND sa_dist.detail_tax_dist_id IS NOT NULL
      AND NOT EXISTS (
            SELECT 1 FROM zx_rec_nrec_dist zd
             WHERE zd.rec_nrec_tax_dist_id = sa_dist.detail_tax_dist_id
               AND zd.summary_tax_line_id = sa_dist.summary_tax_line_id))
   AND ai.cancelled_date IS NULL;

Resolution

The fix is a structured, multi-step process executed from SQL*Plus against the APPS schema. A driver/logging framework is created in Step 1 so all activity is fully auditable and each invoice is processed within a SAVEPOINT, allowing per-invoice rollback on error.

Run: 01_setup_tables.sql

Creates the following objects in the APPS schema:

Run: 02_identify_invoices.sql

Populates ZX_MISSING_DIST_INVOICES using the identification query above and sets three processing flags per invoice:

• POSTED_ENCUMBERED_FLAG = 'Y' — if the invoice has any posted or encumbered distributions
• PAYMENT_POSTED_FLAG = 'Y' — if the invoice has a non-voided payment whose XLA accounting event is fully processed
• PROCESS_FLAG = 'Y' — set for all identified invoices

SELECT COUNT(invoice_id)
  FROM zx_missing_dist_invoices
 WHERE posted_encumbered_flag = 'Y'
    OR payment_posted_flag = 'Y';

If result > 0, proceed to Steps 4–5. Otherwise skip to Step 6.

Run: 03_populate_undo_tables.sql

Populates AP_UNDO_INVOICES with accounting events for all invoices where POSTED_ENCUMBERED_FLAG = 'Y', and AP_UNDO_CHECKS with payment events where PAYMENT_POSTED_FLAG = 'Y'.

Requires PROPOSED_UNDO_DATE as input — must be a date in an open GL period to receive the reversal entries.

Before running Step 5, enable FND debug logging for the executing user:

FND: Debug Log Enabled    → Yes
FND: Debug Log Level      → Statement
FND: Debug Log Module     → %

Record MAX(LOG_SEQUENCE) from FND_LOG_MESSAGES before and after execution to extract the debug log afterward:

SELECT MAX(log_sequence) FROM fnd_log_messages;  -- capture as LOG_SEQ1 before run
                                                 -- capture as LOG_SEQ2 after run

-- Retrieve debug log:
SELECT module, message_text
  FROM fnd_log_messages
 WHERE log_sequence BETWEEN &LOG_SEQ1 AND &LOG_SEQ2
 ORDER BY log_sequence;

Run: 04_execute_undo_accounting.sql

Accepts two input parameters:

• USER_NAME — valid Oracle Applications username (uppercase)
• RESPONSIBILITY_NAME — Payables responsibility name (case-sensitive)

Calls AP_ACCTG_DATA_FIX_PKG.Undo_Accounting, which creates reversal journal entries in XLA/SLA for each invoice and payment event identified in Step 4. On success the script resets POSTED_ENCUMBERED_FLAG and PAYMENT_POSTED_FLAG to 'N' in the driver table for all successfully processed invoices.

Run: 05_apply_data_fix.sql

Processes every invoice in the driver table where all flags are 'N' and PROCESS_FLAG = 'Y'. Each invoice is wrapped in a SAVEPOINT so a failure on one invoice rolls back only that invoice without affecting others.

For each invoice the script performs the following in sequence:

6a. Backup

Inserts pre-fix rows into the three backup tables:

• AP_INV_DIST_FD_BKUP — tax distributions from AP_INVOICE_DISTRIBUTIONS_ALL (types REC_TAX, NONREC_TAX, TRV, TIPV, TERV)
• AP_ASSESSED_DIST_FD_BKUP — from AP_SELF_ASSESSED_TAX_DIST_ALL
• ZX_DIST_FD_BKUP — from ZX_REC_NREC_DIST (application_id=200, entity AP_INVOICES, event classes STANDARD INVOICES, PREPAYMENT INVOICES, EXPENSE REPORTS)

6b. Delete Orphaned AP Tax Distributions

Deletes from AP_INVOICE_DISTRIBUTIONS_ALL and AP_SELF_ASSESSED_TAX_DIST_ALL all tax-type lines: REC_TAX, NONREC_TAX, TRV, TIPV, TERV.

6c. Delete Orphaned ZX Distributions

Deletes matching records from ZX_REC_NREC_DIST.

6d. Reset ITEM Distribution Tax Flag

Clears TAX_ALREADY_DISTRIBUTED_FLAG on non-reversed ITEM distributions in AP_INVOICE_DISTRIBUTIONS_ALL so tax will be redistributed on revalidation.

6e. Reset ZX_LINES Flags

On matching records in ZX_LINES:

• Sets ASSOCIATED_CHILD_FROZEN_FLAG = 'N'
• Sets PROCESS_FOR_RECOVERY_FLAG = 'Y' (or 'N' for cancelled lines)

6f. Release Holds

Deletes any DIST VARIANCE or LINE VARIANCE holds from AP_HOLDS_ALL.

6g. Mark Complete

Sets PROCESS_FLAG = 'D' in the driver table for the successfully fixed invoice.

Re-run AP Validation for all affected invoices. This triggers eBusiness Tax to recalculate and fully repopulate all ZX tables with correct distributions. Then re-run the Payables Accounting Process. Invoices should account successfully and transfer to GL.

-- All PROCESS_FLAG values should = 'D'
SELECT * FROM zx_missing_dist_invoices;
SELECT * FROM ap_undo_invoices;
SELECT * FROM ap_undo_checks;
SELECT * FROM zx_fd_dfix_log_messages ORDER BY log_sequence;

Re-run the identification query from the Root Cause section — it should return zero rows.

Script Inventory

Related Oracle Patches