SYS-04: Interface & Open Tables Audit

Interface tables in Oracle EBS are the integration layer between modules and between external systems and Oracle. Every subledger posts to GL through GL_INTERFACE. Every AutoInvoice submission loads through RA_INTERFACE_LINES_ALL. Every inventory cost update posts through MTL_TRANSACTION_INTERFACE. When records accumulate in these interface tables without being processed — because an import program errored, a period is closed, or a scheduled job has not run — the tables grow silently, performance degrades, and the downstream module never receives the data it expects.

The interface table audit is the single most revealing diagnostic available for an Oracle EBS environment because it shows the current state of every cross-module data flow in one pass. A GL_INTERFACE with 50,000 unprocessed rows means journal imports have not been running. An RA_INTERFACE_ERRORS_ALL with 2,000 error rows means AutoInvoice has been rejecting records, probably for weeks. An MTL_TRANSACTION_INTERFACE with aged records means inventory costing is broken. None of these conditions are visible in any individual module screen — they only appear when you look at the raw table counts.

Open tables — interface records that have been imported but not yet fully processed — are a second category of accumulation risk. AP_INVOICES_INTERFACE records that have been through AutoInvoice but have not been validated. PO_REQUISITIONS_INTERFACE_ALL records that have been imported but not converted to purchase orders. These records sit in a processed-but-incomplete state and can block period close or reconciliation because they appear in the module as open transactions.

SYS-04 provides a system-wide interface table audit — a single-pass count and aging check across all 15 major EBS interface tables — giving the consultant an immediate picture of where data is accumulating, how old it is, and which interface programs need to be investigated. It is designed to run at the start of any support engagement or period close review as the first diagnostic step.

Financial Interfaces

GL_INTERFACE (journal import backlog), AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE (payables FBDI), RA_INTERFACE_LINES_ALL and RA_INTERFACE_ERRORS_ALL (AutoInvoice). The three most operationally critical interface pipelines in any Oracle EBS environment.

Supply Chain Interfaces

PO_HEADERS_INTERFACE and PO_LINES_INTERFACE (requisition and PO import), RCV_HEADERS_INTERFACE and RCV_TRANSACTIONS_INTERFACE (receiving). Row count and aging for each — identifies supply chain integration failures that may not surface in module screens.

Inventory & Manufacturing

MTL_TRANSACTIONS_INTERFACE (inventory transactions pending processing), CST_COMP_SNAP_INTERFACE (WIP completion costing), and FA_MASS_ADDITIONS (asset additions from AP). Aging inventory interface records cause costing errors and period close blocks.

Aging & Priority Classification

Each table's oldest record age determines its priority classification: same-day (normal), 1-3 days (review), 3-7 days (high), 7+ days (critical). SYS-04 outputs the priority classification for each table to guide where to investigate first.

════════════════════════════════════════════════════════════
  ORACLE EBS R12 — INTERFACE & OPEN TABLES AUDIT
════════════════════════════════════════════════════════════
  Environment        : PROD
  Audit Date         : 25-FEB-2026 11:30:00
  Case Number        : SYS-514840
════════════════════════════════════════════════════════════

[ GL INTERFACE ]
────────────────────────────────────────────────────────────
  GL_INTERFACE        : 4,841 rows — oldest 18 days ✗
  STATUS              : STATUS=NULL (unprocessed)
  ✗ ACTION: Journal import not running — investigate GL_INTERFACE

[ AP INTERFACES ]
────────────────────────────────────────────────────────────
  AP_INVOICES_INTERFACE   : 0 rows ✓
  AP_INVOICE_LINES_INTERFACE: 0 rows ✓

[ AR INTERFACES ]
────────────────────────────────────────────────────────────
  RA_INTERFACE_LINES_ALL  : 284 rows — oldest 3 days ⚠
  RA_INTERFACE_ERRORS_ALL : 1,841 rows — oldest 22 days ✗
  ✗ ACTION: AutoInvoice errors accumulating — 22-day-old errors need review

[ PO / RECEIVING INTERFACES ]
────────────────────────────────────────────────────────────
  PO_HEADERS_INTERFACE    : 0 rows ✓
  RCV_HEADERS_INTERFACE   : 12 rows — today ✓
  RCV_TRANSACTIONS_INTERFACE: 12 rows — today ✓

[ INVENTORY / WIP INTERFACES ]
────────────────────────────────────────────────────────────
  MTL_TRANSACTIONS_INTERFACE: 88 rows — oldest 2 days ⚠
  CST_COMP_SNAP_INTERFACE : 0 rows ✓

[ PAYROLL INTERFACES ]
────────────────────────────────────────────────────────────
  PAY_BATCH_HEADERS       : 0 rows ✓

════════════════════════════════════════════════════════════
  AUDIT SUMMARY
════════════════════════════════════════════════════════════
  GL_INTERFACE: 4,841 rows / 18 days — CRITICAL
  RA_INTERFACE_ERRORS_ALL: 1,841 errors / 22 days — HIGH
  RA_INTERFACE_LINES_ALL: 284 rows / 3 days — REVIEW
  MTL_TRANSACTIONS_INTERFACE: 88 rows / 2 days — MONITOR
════════════════════════════════════════════════════════════
  Snapshot saved  : CONS_BACKUP.SYS_INTF_AUDIT_514840 ✓
  Registry ID     : 7138
════════════════════════════════════════════════════════════

Diagnostic Engine

Single-pass audit of 15 major EBS interface tables — row count, oldest record age, and status distribution for each. Covers GL, AP, AR, PO, Receiving, Inventory, WIP, Payroll, and FA interface tables. Provides an immediate picture of where data is accumulating across the entire Oracle environment.

Backup Created

SYS-04 is read-only for the audit pass. A point-in-time count snapshot is saved to CONS_BACKUP.SYS_INTF_AUDIT_<case#>. Any subsequent cleanup actions (purging error records, resubmitting import programs) are made against the individual interface tables with their own backup per standard procedure.

Guided Data Fix

For aged GL_INTERFACE records: submit Journal Import for the affected source and category. For RA_INTERFACE_ERRORS_ALL accumulation: correct the source data and resubmit AutoInvoice. For MTL_TRANSACTIONS_INTERFACE: submit the Transaction Manager concurrent program. SYS-04 identifies the right program and parameters for each table.

KB Article Generated

Complete KB article generated — audit date, all table counts and ages, critical flags, action items by priority. Upload directly to your knowledge base. Designed to be the opening diagnostic document for any support engagement.

KB-SYS-514840-001 · Script: SYS-04

Interface Table Audit — GL_INTERFACE 4,841 Rows / 18 Days, AR Errors 1,841 Rows / 22 Days

Module: System / Technical
Platform: EBS R12.2.10
Status: RESOLVED

Symptom

System-wide interface table audit finds GL_INTERFACE with 4,841 unprocessed rows (oldest 18 days) — journal import not running. RA_INTERFACE_ERRORS_ALL with 1,841 error rows (oldest 22 days) — AutoInvoice errors accumulating. MTL_TRANSACTIONS_INTERFACE with 88 rows (2 days old).

Root Cause

GL Journal Import concurrent program schedule was inadvertently removed during a Concurrent Manager reconfiguration on 08-FEB-2026. 18 days of journal submissions from AP, AR, and PO subledgers accumulated without posting. AR AutoInvoice error accumulation is a separate ongoing issue — CCID changes in JAN-2026 were never corrected in the source billing system.

Tables Affected

GL_INTERFACE: 4,841 rows, STATUS=NULL, oldest CREATION_DATE = 07-FEB-2026
RA_INTERFACE_ERRORS_ALL: 1,841 rows, oldest CREATION_DATE = 03-FEB-2026, error: Invalid CCID

Fix Applied

GL Journal Import concurrent program rescheduled — runs every 15 minutes for all sources. Backlog of 4,841 rows cleared over 3 Journal Import runs. AR AutoInvoice errors analyzed — CCID correction in billing system submitted to IT. MTL interface (88 rows) cleared by submitting Transaction Manager.

Prevention

Interface table row count monitoring added to daily operations dashboard — any table > 500 rows or any record > 3 days old triggers an alert. GL Journal Import schedule change requires change management approval.

GL_INTERFACE Aged Records — Journal Import Not Running

Unprocessed GL_INTERFACE records older than 3 days indicate the Journal Import program is not running on schedule. Subledger transactions are not posting to GL. Period-end GL balances will not reconcile to subledger reports. Identified immediately by SYS-04 before any individual module diagnostic.

RA_INTERFACE_ERRORS_ALL Accumulation

AutoInvoice error rows that have not been investigated or corrected. Aged error rows indicate a systemic data condition in the billing source — invalid accounts, wrong customer references, or closed GL periods. Often accumulates silently for weeks.

Inventory

MTL_TRANSACTIONS_INTERFACE Aged Records

Inventory transactions pending processing by the Transaction Manager. Aged records cause inventory quantity discrepancies and costing errors. Period close for Inventory cannot complete while unprocessed interface records exist.

FA_MASS_ADDITIONS Pending Records

Asset additions from AP invoices sitting in the mass additions queue unprocessed. Depreciation will not run on these assets, and the asset register balance will not match the AP capitalization amounts until Post Mass Additions is run.

Table	Module	Purpose
GL_INTERFACE	GL	Journal import staging — all subledger-to-GL postings
AP_INVOICES_INTERFACE	AP	Invoice FBDI import staging
RA_INTERFACE_LINES_ALL	AR	AutoInvoice transaction staging
RA_INTERFACE_ERRORS_ALL	AR	AutoInvoice rejection errors
PO_HEADERS_INTERFACE	PO	PO and requisition import staging
RCV_TRANSACTIONS_INTERFACE	PO/INV	Receiving transaction staging
MTL_TRANSACTIONS_INTERFACE	INV	Inventory transaction staging
FA_MASS_ADDITIONS	FA	Asset addition staging from AP capitalization
PAY_BATCH_HEADERS	PAY	Payroll batch import staging

Decision Framework

How Every Fix Decision Is Made

Before any data is modified in a production Oracle database, AP-01 walks through a four-stage decision process. Every condition identified by the diagnostic maps to exactly one resolution path.

First Option — Always

Can the front end fix this?

Oracle's own forms and concurrent programs are always the first option. If the condition can be corrected through Oracle's standard UI — a form, a concurrent program, a setup screen — that path is taken first. No consultant SQL required, no database risk, and the fix is fully supported by Oracle. The diagnostic identifies these conditions explicitly and states the exact front-end navigation path.

✓ Functional Fix

When Front End Is Not Available

Is a direct data fix safe to apply?

When the front-end path is unavailable or would require an unacceptable volume of manual steps, a direct fix is evaluated against explicit criteria. All of the following must be true before proceeding:

→The fix mirrors exactly what Oracle's own code would have done if the underlying condition were corrected

→All dependent tables have been identified and will be updated in the same transaction

→The fix is fully reversible — a single INSERT from the backup table restores the original state

→The environment patch level has been confirmed against the fix logic version

⚡ Direct Fix

Hard Stops — No Exceptions

Does this require Oracle Support?

Certain table areas are never touched directly, regardless of how well the underlying structure is understood. The diagnostic flags these conditions and generates the Service Request documentation:

✗XLA subledger tables — XLA_EVENTS, XLA_AE_HEADERS — incorrect changes corrupt the subledger audit trail in ways undetectable until period close fails or an auditor requests a reconciliation

✗Workflow engine tables — WF_ITEMS, WF_ITEM_ACTIVITY_STATUSES — ad-hoc DML can corrupt the workflow engine state instance-wide

✗Recurring conditions after a fix — indicates a code defect, not a data error. Documented and submitted as a Service Request with a reproducible test case

⚠ Oracle Support

For All Approved Direct Fixes

Backup → Execute → Verify → Document

A timestamped backup table is created and row-count verified before the first UPDATE fires. Explicit parameter confirmation is required — the script will not self-execute. After execution, a verification query confirms the expected state. A complete change record — rows affected, before and after values, database username, timestamp — is written to the FIX_BACKUP_REGISTRY and becomes the primary artifact in the knowledge base entry for this incident.

📋 Documented

Condition Identified	Resolution Path	Notes
GL_INTERFACE aged records — journal import not running	Functional First	Schedule or resubmit the Journal Import concurrent program for the affected source and category. SYS-04 identifies the source, category, and period of the aged records to use as import parameters.
RA_INTERFACE_ERRORS_ALL accumulation — AutoInvoice errors	Functional First	Correct the source data causing the rejection (invalid CCID, invalid customer, wrong GL date) and resubmit AutoInvoice. SYS-04 identifies the most common error type from the error table row count and age.
MTL_TRANSACTIONS_INTERFACE aged records	Functional First	Submit the Inventory Transaction Manager concurrent program. SYS-04 identifies aged records by organization and item to confirm the correct processing parameters.
AP_INVOICES_INTERFACE unprocessed — FBDI import incomplete	Functional First	Submit the Import Payables Invoices concurrent program. SYS-04 identifies whether the records have a SOURCE and GROUP_ID that can be used to filter the import submission.
RCV_TRANSACTIONS_INTERFACE aged records — receiving stuck	Functional First	Submit the Receiving Transaction Processor concurrent program. SYS-04 identifies the transaction type distribution in the aged records to confirm the processor parameters.
RA_INTERFACE_LINES_ALL large volume — AutoInvoice not scheduled	Functional First	Schedule AutoInvoice to run on the correct frequency for the business volume. SYS-04 identifies whether the accumulation is due to a missing schedule or a concurrent program error.
FA_MASS_ADDITIONS aged records — asset additions not processed	Functional First	Submit the Post Mass Additions concurrent program for the affected asset book. SYS-04 identifies the book and the age of the oldest unprocessed mass addition.
Bulk interface cleanup needed — error records too old to correct	Direct Fix	For error records confirmed as uncorrectable (source system no longer exists, transaction cancelled at source), SYS-04 deletes the interface error rows with full backup after confirming with the business owner that the records should be purged.

Safeguards

Nothing Executes Without a Safety Net

Before any data fix runs, the script verifies pre-flight conditions and creates a complete verified backup. If any check fails, the script aborts. There is no partial execution path.

Pre-Flight Checks — All Must Pass

✓

POSTED_FLAG = 'N'

Will not modify a posted transaction under any circumstances

✓

ACCOUNTING_EVENT_ID IS NULL

Will not modify rows with an active or pending XLA accounting event

✓

No active database lock

Checks V$LOCK — will not proceed if another session holds a lock on target rows

✓

CONS_BACKUP schema writable

Backup schema must be accessible before any DML is permitted

✓

Explicit confirmation required

Script outputs a confirmation prompt — DML does not execute until consultant enters the case number as the execution parameter

Backup Methodology

Before the first UPDATE fires, the script creates a complete copy of every row to be modified. Tables are named CONS_BACKUP.<TABLE>_<CASE#> and persist permanently after execution.

GL_INTERFACE snapshot

RA_INTERFACE_ERRORS_ALL snapshot

MTL_TRANSACTIONS_INTERFACE snapshot

Row count verified after backup creation. If backup fails for any reason the script aborts without executing any DML. Rollback is a single INSERT from the backup table. No reconstruction required.

SYS-04 — Pre-Flight & Backup Verification

════════════════════════════════════════════════════════════
  PRE-FLIGHT — READ-ONLY AUDIT
════════════════════════════════════════════════════════════
  Mode               : SELECT ONLY — row counts and max dates ✓
  Schema Access      : All 15 interface tables accessible ✓
  CONS_BACKUP Schema : Accessible — snapshot table only ✓
────────────────────────────────────────────────────────────
  ALL PRE-FLIGHT CHECKS PASSED
════════════════════════════════════════════════════════════
  Creating : CONS_BACKUP.SYS_INTF_AUDIT_514840
  Rows     : 15-table count snapshot saved ✓
  Registry : FIX_BACKUP_REGISTRY — ID 7138 created ✓
────────────────────────────────────────────────────────────
  AUDIT SNAPSHOT COMPLETE
════════════════════════════════════════════════════════════

Knowledge Base

Every Execution Produces a Record

The knowledge base article is generated automatically from the script's execution output. No manual documentation required. It becomes the institutional record — for the team, for auditors, and for every future engagement in the same environment.

Zero Manual Effort

Every field — environment, tables, before/after values, backup reference, root cause, prevention — is generated from actual execution output. Nothing is written by hand.

Patterns Surface Over Time

The first engagement produces findings. The third produces patterns. Recurring conditions that are invisible as individual incidents become obvious as knowledge base trends.

Survives Staff Turnover

The knowledge base is an institutional record of the Oracle environment. A new manager, a new DBA, or an external auditor can read exactly what happened, what was done, and what prevents recurrence.

KB-SYS-514840-001

Interface Table Audit — GL_INTERFACE 4,841 Rows / 18 Days, AR Errors 1,841 Rows / 22 Days

EBS R12.2.10 · System / Technical

● RESOLVED

Symptom

Root Cause

Tables

GL_INTERFACE: 4,841 rows, STATUS=NULL, oldest CREATION_DATE = 07-FEB-2026
RA_INTERFACE_ERRORS_ALL: 1,841 rows, oldest CREATION_DATE = 03-FEB-2026, error: Invalid CCID

Fix Applied

Prevention

R12 Guide (PDF)	Title & Chapter	Detail
120apug.pdf	Oracle Payables User's Guide — Ch. 3: Payables Open Interface Purge Program	pp. 3-148 to 3-151: Purging processed and rejected interface records from AP tables
120arig.pdf	Oracle Receivables Implementation Guide — AutoInvoice	RA_INTERFACE_LINES_ALL and RA_INTERFACE_ERRORS_ALL purge procedures
120poug.pdf	Oracle Purchasing User's Guide — Ch. 9: Receipt Accruals	Accrual interface table cleanup and AP_ACCRUAL_RECONCILE_TEMP purge

Interface & Open Tables Audit

Why This Fails — and What It Costs

What This Script Diagnoses

What the Report Looks Like

The Four-Layer Architecture in SYS-04

Backup & Rollback for SYS-04

Tables Backed Up — SYS-04

Pre-Flight Safety Guards

FIX_BACKUP_REGISTRY Entry

Auto-Generated Knowledge Base Article

What This Script Finds

GL_INTERFACE Aged Records — Journal Import Not Running

RA_INTERFACE_ERRORS_ALL Accumulation

MTL_TRANSACTIONS_INTERFACE Aged Records

FA_MASS_ADDITIONS Pending Records

Tables Examined

How Every Fix Decision Is Made

Nothing Executes Without a Safety Net

Every Execution Produces a Record

Oracle Documentation References

Ready to Resolve This in Your Environment?

Why This Fails — and What It Costs

What This Script Diagnoses

What the Report Looks Like

The Four-Layer Architecture in SYS-04

Backup & Rollback for SYS-04

Tables Backed Up — SYS-04

Pre-Flight Safety Guards

FIX_BACKUP_REGISTRY Entry

Auto-Generated Knowledge Base Article

What This Script Finds

GL_INTERFACE Aged Records — Journal Import Not Running

RA_INTERFACE_ERRORS_ALL Accumulation

MTL_TRANSACTIONS_INTERFACE Aged Records

FA_MASS_ADDITIONS Pending Records

Tables Examined

How Every Fix Decision Is Made

Nothing Executes Without a Safety Net

Every Execution Produces a Record

Oracle Documentation References

Related Diagnostic Scripts

Concurrent Manager Health

Journal Import Diagnostic

AutoInvoice Interface Diagnostic

AP Period Close Readiness

Ready to Resolve This in Your Environment?