AR-02: AutoInvoice Interface Diagnostic

AutoInvoice is Oracle EBS Accounts Receivable's mechanism for importing invoices from upstream systems — order management, project billing, contracts, and custom applications. It works through an interface table staging layer: the source system populates RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL, and optionally RA_INTERFACE_SALESCREDITS_ALL, and then the AutoInvoice concurrent program validates each record, transforms it into AR transaction tables, and reports errors to RA_INTERFACE_ERRORS_ALL. When the import fails, the error records tell you what went wrong — but the error messages are coded, and the same error code can have multiple root causes depending on the transaction type, the batch source, and the interface record configuration.

The most common AutoInvoice failure — INVALID_TRANS_TYPE — occurs when the CUST_TRX_TYPE_ID in the interface line references a transaction type that either does not exist in RA_CUST_TRX_TYPES_ALL, is inactive, or does not match the transaction class expected for the batch source. In multi-OU environments where the same batch source is used across multiple operating units, the transaction type ID from one OU is frequently used in interface records intended for a different OU, where that type ID maps to a different or inactive type. The source system has no way to know the type ID is invalid until AutoInvoice rejects it.

The GL date validation failure — GL_DATE_CLOSED_PERIOD — is the second most common error and is the most time-sensitive. When the upstream system populates the GL date field in RA_INTERFACE_LINES_ALL with the transaction date from the source system, and that date falls in a closed AR period, every interface record in that batch fails with the same error. The fix is to update the GL_DATE in the interface table to a date in an open period — but this requires knowing which period is open, confirming that the accounting date change is authorized by the Controller, and applying the update before the next AutoInvoice run to prevent a second failure.

AR-02 runs a complete analysis of the AutoInvoice interface tables — error summary from RA_INTERFACE_ERRORS_ALL grouped by error type and frequency, transaction type validity check against RA_CUST_TRX_TYPES_ALL per OU, GL date validation against open periods, unit of measure existence in FND_UNIT_OF_MEASURE, customer account and site existence in HZ tables, duplicate ORIG_SYSTEM_REFERENCE detection, and orphaned line identification where header records exist without corresponding line records. It generates the corrected SQL for each fixable interface record error.

Interface Error Summary

RA_INTERFACE_ERRORS_ALL — error count by type, batch source, and GL date. Top 10 error codes. Oldest pending interface records. Lines in VALID vs. INVALID status.

Transaction Type Validity

Interface lines referencing invalid or inactive transaction types in RA_CUST_TRX_TYPES_ALL. Missing transaction type assignment. Class mismatch — INVOICE vs. CREDIT_MEMO vs. DEBIT_MEMO.

GL Date and Period

Interface lines with GL dates in closed or invalid periods. Future-period dates beyond the latest open future period. GL date gap between OM ship date and AR import date.

UOM and Item

Unit of measure not in FND_UNIT_OF_MEASURE. Item number not in MTL_SYSTEM_ITEMS_B for the inventory OU. Item inactive or not transactable.

Duplicate Transaction Numbers

Same transaction number already exists in RA_CUSTOMER_TRX_ALL for the same batch source and customer. Cross-check with ORIG_SYSTEM_BILL_CUSTOMER_ID.

════════════════════════════════════════════════════════════
  ORACLE EBS R12 — AUTOINVOICE INTERFACE DIAGNOSTIC
════════════════════════════════════════════════════════════
  Batch Source       : OMS-INVOICES
  Request ID         : 4491820
  Interface Records  : 284 total
  Case Number        : AR-301482
  Report Date        : 21-FEB-2026 09:08:44
════════════════════════════════════════════════════════════

[ SECTION 1 — ERROR SUMMARY ]
────────────────────────────────────────────────────────────
  Total Errors       : 47 records in RA_INTERFACE_ERRORS_ALL
  GL_DATE_CLOSED_PERIOD : 38 records — GL date in JAN-2026 (closed)
  INVALID_TRANS_TYPE : 7 records — TRX_TYPE_ID 8812 invalid in US-WEST OU
  DUP_ORIG_SYSTEM_REF: 2 records — ORIG_SYSTEM_REF already in RA_CUSTOMER_TRX_ALL

[ SECTION 2 — GL DATE ANALYSIS ]             STATUS: ✗ FAIL
────────────────────────────────────────────────────────────
  Interface GL Date  : 31-JAN-2026
  JAN-2026 Status    : CLOSED ✗
  Next Open Period   : FEB-2026 — OPEN ✓
  Fix                : UPDATE RA_INTERFACE_LINES_ALL SET GL_DATE = '28-FEB-2026' WHERE REQUEST_ID = 4491820 AND ERROR_FLAG = 'Y'

[ SECTION 3 — TRANSACTION TYPE ]             STATUS: ✗ FAIL
────────────────────────────────────────────────────────────
  TRX_TYPE_ID        : 8812
  Valid in US-EAST OU: YES — Standard Invoice ✓
  Valid in US-WEST OU: NO — type does not exist in this OU ✗
  Fix                : Correct TRX_TYPE_ID to 9104 (Standard Invoice — US West OU)

[ SECTION 4 — DUPLICATE DETECTION ]          STATUS: ✗ FAIL
────────────────────────────────────────────────────────────
  ✗ ORIG_SYSTEM_REF OMS-2026-00441 already imported on 15-JAN-2026
  ✗ ORIG_SYSTEM_REF OMS-2026-00442 already imported on 15-JAN-2026

════════════════════════════════════════════════════════════
  DIAGNOSTIC SUMMARY
════════════════════════════════════════════════════════════
  3 distinct error types — 47 interface records blocked
  1. Update GL dates for 38 records to FEB-2026 (Controller auth required)
  2. Correct TRX_TYPE_ID for 7 US-West records
  3. Delete 2 duplicate interface records — already imported
════════════════════════════════════════════════════════════

KB-AR-301482-001 · Script: AR-02

AutoInvoice Interface Errors — GL Date in Closed Period and Invalid Transaction Type in US-West OU

Module: Accounts Receivable
Platform: EBS R12.2.10
Status: RESOLVED

Symptom

47 of 284 OMS-INVOICES interface records rejected by AutoInvoice. 38 GL_DATE_CLOSED_PERIOD errors and 7 INVALID_TRANS_TYPE errors. 2 duplicate ORIG_SYSTEM_REFERENCE records detected. AutoInvoice batch partially imported — 237 invoices created, 47 blocked.

Root Cause

GL date errors: OMS system populated GL_DATE with transaction date (31-JAN-2026) — JAN-2026 period was closed on 03-FEB-2026. OMS export runs do not check AR period status before populating the interface. Transaction type errors: OMS uses a single TRX_TYPE_ID 8812 for all OUs — type 8812 only exists in US-East OU. US-West OU requires type 9104.

Tables Affected

RA_INTERFACE_LINES_ALL — GL_DATE, CUST_TRX_TYPE_ID (47 error records, Request ID: 4491820)
RA_INTERFACE_ERRORS_ALL — MESSAGE_TEXT, INTERFACE_LINE_ID (47 error records)

Fix Applied

GL dates updated to 28-FEB-2026 for 38 records (Controller authorized). TRX_TYPE_ID corrected to 9104 for 7 US-West records. 2 duplicate records deleted. AutoInvoice resubmitted — all 47 previously rejected records imported successfully.

Prevention

OMS export script modified to: (1) query AR open periods before populating GL_DATE, (2) use OU-specific TRX_TYPE_ID lookup table. AR-02 interface validation should run as a pre-check before each AutoInvoice submission.

Table / View	Schema	Purpose in Diagnostic
RA_INTERFACE_LINES_ALL	AR	Interface lines pending import
RA_INTERFACE_ERRORS_ALL	AR	Interface rejection detail by error code
RA_INTERFACE_DISTRIBUTIONS_ALL	AR	Interface distribution records
RA_CUST_TRX_TYPES_ALL	AR	Transaction type validity
HZ_CUST_ACCOUNTS	HZ	Customer account validation
FND_UNIT_OF_MEASURE	FND	UOM validity
GL_PERIOD_STATUSES	GL	Period open/closed check for GL dates

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_DATE_CLOSED_PERIOD — interface GL date in closed period	Direct Fix	AR-02 identifies the next open period, generates the UPDATE for GL_DATE in RA_INTERFACE_LINES_ALL with full backup. Controller authorization documented before execution. AutoInvoice resubmitted after correction.
INVALID_TRANS_TYPE — transaction type ID invalid for OU	Direct Fix	AR-02 identifies the correct TRX_TYPE_ID for the target OU and applies the correction to RA_INTERFACE_LINES_ALL with full backup. Common in multi-OU environments where source systems use a single type ID.
DUP_ORIG_SYSTEM_REF — record already imported	Direct Fix	AR-02 identifies the original transaction in RA_CUSTOMER_TRX_ALL and deletes the duplicate interface record with backup. If the original import was incorrect, documents the correction path before deletion.
INVALID_SALESREP — sales rep ID does not exist	Functional First	Correct the SALESREP_ID in the interface record or add the missing sales rep via Receivables > Setup > Salespersons. AR-02 identifies the invalid ID and the active sales rep options.
Orphaned interface lines — lines without header records	Direct Fix	AR-02 identifies interface line records where no corresponding header record exists and deletes the orphaned lines with backup. Common when header records fail a validation that line records passed.
INVALID_CURRENCY_CODE — currency not enabled	Functional First	Enable the currency via General Ledger > Setup > Currencies. AR-02 identifies the currency code in the interface record and the current enabled status in FND_CURRENCIES.
UOM not found in FND_UNIT_OF_MEASURE	Direct Fix	AR-02 identifies the UOM value in the interface record and the closest matching active UOM. Corrects the UOM_CODE in RA_INTERFACE_LINES_ALL with full backup.
INVALID_CUSTOMER — customer account not found in HZ	Functional First	Verify the customer account number or ID against HZ_CUST_ACCOUNTS. If the customer exists but is inactive, reactivate via Customers workbench. If the customer does not exist, create it before resubmitting AutoInvoice.

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.

RA_INTERFACE_LINES_ALL

RA_INTERFACE_ERRORS_ALL

RA_INTERFACE_DISTRIBUTIONS_ALL

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.

AR-02 — Pre-Flight & Backup Verification

════════════════════════════════════════════════════════════
  PRE-FLIGHT SAFETY CHECK
════════════════════════════════════════════════════════════
  Batch Source       : OMS-INVOICES
  Request ID         : 4491820
  Records in Scope   : 47 error records
  CONS_BACKUP Schema : Accessible ✓
  Session Lock Check : No locks on interface records ✓
────────────────────────────────────────────────────────────
  ALL PRE-FLIGHT CHECKS PASSED — Ready to create backup
════════════════════════════════════════════════════════════
  Creating : CONS_BACKUP.RA_INTERFACE_LINES_ALL_301482
  Rows     : 47 rows backed up ✓ Verified
  Creating : CONS_BACKUP.RA_INTERFACE_ERRORS_ALL_301482
  Rows     : 47 rows backed up ✓ Verified
  Registry : FIX_BACKUP_REGISTRY — ID 3058 created ✓
────────────────────────────────────────────────────────────
  BACKUP COMPLETE — Rollback available via single INSERT
════════════════════════════════════════════════════════════
  Enter case number to confirm : 301482
  Confirmed. Applying GL date and type corrections...
════════════════════════════════════════════════════════════

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-AR-301482-001

AutoInvoice Interface Errors — GL Date in Closed Period and Invalid Transaction Type in US-West OU

EBS R12.2.10 · Accounts Receivable

● RESOLVED

Symptom

Root Cause

Tables

RA_INTERFACE_LINES_ALL — GL_DATE, CUST_TRX_TYPE_ID (47 error records, Request ID: 4491820)
RA_INTERFACE_ERRORS_ALL — MESSAGE_TEXT, INTERFACE_LINE_ID (47 error records)

Fix Applied

Prevention

R12 Guide (PDF)	Title & Chapter	Detail
120arig.pdf	Oracle Receivables Implementation Guide — AutoInvoice	AutoInvoice grouping rules, line ordering rules, and interface error resolution
120arig.pdf	Oracle Receivables Implementation Guide — Ch. 1: Setup Steps	Setup steps for transaction sources, transaction types, and AutoInvoice configuration
120funmo.pdf	Oracle Applications Multiple Organizations Implementation Guide	Operating unit isolation of RA_INTERFACE_LINES_ALL data for multi-org environments

AutoInvoice Interface Diagnostic

Why This Fails — and What It Costs

What This Script Diagnoses

What the Report Looks Like

The Four-Layer Architecture in AR-02

Backup & Rollback for AR-02

Tables Backed Up — AR-02

Pre-Flight Safety Guards

FIX_BACKUP_REGISTRY Entry

Auto-Generated Knowledge Base Article

Most Common Issues Detected by AR-02

INVALID_TRANS_TYPE

GL_DATE_CLOSED_PERIOD

DUP_ORIG_SYSTEM_REF

Orphaned Interface Lines

Tables & Views 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 AR-02

Backup & Rollback for AR-02

Tables Backed Up — AR-02

Pre-Flight Safety Guards

FIX_BACKUP_REGISTRY Entry

Auto-Generated Knowledge Base Article

Most Common Issues Detected by AR-02

INVALID_TRANS_TYPE

GL_DATE_CLOSED_PERIOD

DUP_ORIG_SYSTEM_REF

Orphaned Interface Lines

Tables & Views Examined

How Every Fix Decision Is Made

Nothing Executes Without a Safety Net

Every Execution Produces a Record

Oracle Documentation References

Related Diagnostic Scripts

AR Transaction Validation

AR Period Close Readiness

OM to AR Interface Diagnostic

Ready to Resolve This in Your Environment?