Symptoms

• AP payment accounting events are stuck — associated XLA accounting entries exist but have not transferred to GL for the affected period.
• AP_INVOICE_PAYMENTS_ALL.ACCRUAL_POSTED_FLAG and AP_PAYMENT_HISTORY_ALL.POSTED_FLAG indicate the records are posted when the GL journal was never successfully created.
• The GL period cannot be closed because outstanding unposted AP items remain.
• The GL Sequential Numbering profile option (UNIQUE:SEQ_NUMBERS) is set to Always Used for the affected ledger's superuser responsibility, which prevented normal GL Journal Import from completing.

Root Cause

AP payment XLA accounting events were generated and accounted (producing XLA_AE_HEADERS and XLA_AE_LINES records), but GL transfer failed — likely because the Sequential Numbering profile option was set to Always Used for the GL responsibility, causing the journal import to fail when a sequence could not be allocated. The XLA accounting entries remained behind in an incomplete transfer state. The fix resets the XLA event status to allow re-accounting, then manually inserts GL_INTERFACE rows from the XLA lines for the affected AE headers, marks those headers as GL-transferred, and imports and posts the correcting batch, then closes the period.

Resolution

Run data_fix.txt. Backup tables are created for:

• AP_PAYMENT_HISTORY_ALL
• AP_INVOICE_PAYMENTS_ALL
• XLA_EVENTS
• XLA_AE_HEADERS
• XLA_AE_LINES (via ae_header_id subquery)
• XLA_DISTRIBUTION_LINKS (via ae_header_id subquery)
• XLA_ACCOUNTING_ERRORS

Update AP_INVOICE_PAYMENTS_ALL to set ACCRUAL_POSTED_FLAG = 'N' for the affected event IDs. Update AP_PAYMENT_HISTORY_ALL to set POSTED_FLAG = 'N'. Update XLA_EVENTS to set EVENT_STATUS_CODE = 'U' and PROCESS_STATUS_CODE = 'U' (unprocessed) for the affected event IDs.

Delete from XLA_AE_HEADERS, XLA_AE_LINES, XLA_DISTRIBUTION_LINKS, and XLA_ACCOUNTING_ERRORS for the affected event IDs. Commit.

Re-run the Create Accounting process for the affected period. This regenerates the XLA_AE_HEADERS and XLA_AE_LINES records for the reset events.

Temporarily change the Sequential Numbering profile option to Not Used for the GL SuperUser responsibility associated with the affected ledger. This allows GL Journal Import to proceed without requiring a sequence number.

Insert rows into GL_INTERFACE by selecting from XLA_AE_LINES joined to XLA_AE_HEADERS for the specific ae_header_id values produced by Create Accounting. Use STATUS = 'NEW', the correct ledger_id, period_name, and an appropriate group_id for the import batch. Set reference1 to identify the batch (e.g., case reference).

Back up the affected XLA_AE_HEADERS rows (second backup for the new headers), then update XLA_AE_HEADERS.GL_TRANSFER_STATUS_CODE = 'Y' for the same ae_header_id values. Commit.

Run GL Journal Import for the group_id used in Step 6 to create the correcting journal batch. Post the resulting journal to the affected period.

Restore the Sequential Numbering profile option to Always Used for the GL SuperUser responsibility.

Close the affected period in AP, then close it in GL.

Script Inventory

Key Tables Affected