How-toOracle E-Business SuiteData Conversion / Open Interfaces

Data Conversion in Oracle EBS: Interface Tables, Open Interfaces and Reconciliation

Question
data conversion in oracle ebs

Also searched as

  • oracle ebs data migration approach
  • oracle ebs open interface tables conversion
  • oracle apps conversion interface validation reconciliation

Short answer

Data conversion in Oracle EBS means moving legacy data into EBS through supported paths only: open interface tables with their import concurrent programs, or public PL/SQL APIs. Never insert into base tables directly. The standard pattern is extract, stage, validate, load into the interface, run the import, fix rejects, and reconcile against the source.

Applies to: Oracle E-Business Suite 11i, 12.1 and 12.2 (interface names are stable across releases; verify columns in the Oracle Electronic Technical Reference Manual or the eTRM for your release)

A repeatable EBS conversion approach

  1. 1Scope each object (items, customers, suppliers, open POs, open invoices, on-hand, GL balances) and decide whether it is a conversion (history or open balances) or a setup load (configuration).
  2. 2Write a field-level mapping from legacy to EBS, including defaults, code translations (UOM, payment terms, tax codes) and who owns each rule.
  3. 3Extract to flat files or a staging schema, then clean duplicates and bad codes before anything touches EBS.
  4. 4Load the staging tables, then run a validation package that writes a status and error message per row (new, validated, error, loaded).
  5. 5Populate the open interface table for the object, for example MTL_SYSTEM_ITEMS_INTERFACE for items, and set the process flag the import program expects.
  6. 6Submit the import concurrent program (for example Item Import, Customer Interface, Payables Open Interface Import, AutoInvoice, Journal Import) and read its output and the interface error tables.
  7. 7Correct rejected rows in staging, reload, and repeat until the reject count is zero or accepted.
  8. 8Reconcile: row counts, control totals (open AP, open AR, GL trial balance, on-hand value) against the legacy source, and get sign-off.
  9. 9Rehearse the full sequence at least twice in a copy of production, timing it, before the cutover weekend.

Interface tables, APIs or loaders

Oracle supports three entry points. Open interface tables are staging tables owned by the module, processed by a concurrent program that validates and writes the base tables. Examples: MTL_SYSTEM_ITEMS_INTERFACE (items), RA_INTERFACE_LINES_ALL (AutoInvoice), GL_INTERFACE (journals), AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE (payables invoices), PO_HEADERS_INTERFACE and PO_LINES_INTERFACE (purchasing documents), MTL_TRANSACTIONS_INTERFACE (inventory transactions, on-hand via miscellaneous receipt).

Public APIs are PL/SQL packages for objects with no interface table or where you need row-level control, for example the TCA APIs such as HZ_PARTY_V2PUB and HZ_CUST_ACCOUNT_V2PUB for customers. Web ADI and data loaders are acceptable for small volumes and setup data, but they are not a substitute for a tested conversion on large volumes. The common failure is a direct insert into base tables: it works once, breaks on patching, and skips the validation that EBS reports later depend on.

Load order and dependencies

Sequence matters because each object validates against earlier ones. A typical order is: GL structure and calendars, organizations and inventory setup, items and categories, suppliers and sites, customers and sites, open POs, on-hand inventory, open AP invoices, open AR invoices, then GL opening balances last. Open POs need items and suppliers first; AP invoices need suppliers and often PO references; AutoInvoice needs customers, transaction types and revenue accounts.

Plan for the periods. Open items go in as transactions dated before cutover so they age correctly, and the GL load must land in a period that is open. Set the accounting period status deliberately and close the legacy period only after reconciliation.

Validation and error handling

Do your own validation before the interface so the import program only sees clean rows. Check mandatory fields, code values against the EBS lookup or setup table, referential integrity, and arithmetic (line totals equal header totals). Write errors into a staging error column you can query and send back to the business.

After the import, errors appear in module-specific places: the interface table error column, PO_INTERFACE_ERRORS, AP_INTERFACE_REJECTIONS, MTL_INTERFACE_ERRORS, or the concurrent request output. Build one view that unions staging status and these tables so reject triage is one query, not five screens.

-- Staging status roll-up per object
SELECT object_name, status, COUNT(*) AS row_count
FROM xx_conv_staging
GROUP BY object_name, status
ORDER BY object_name, status;

Reconciliation

Reconcile with control totals the finance team already trusts: open AP by supplier, open AR by customer, inventory value by organization and subinventory, open PO value, and the GL trial balance. Count rows in, rows rejected and rows loaded must tie back to the extract count.

Keep the reconciliation reports as deliverables. Auditors and the next upgrade team will ask how opening balances were proven, and a stored query with dated output answers that without a meeting.

Try it here, free

Oracle EBS BOM Interface Load Worksheet

Open full tool

Stage BOM component rows with the columns used by the Oracle BOM open interface, then export to Excel for loading.

Common pitfalls

  • !Inserting straight into base tables, which bypasses validation and risks support and patching problems.
  • !Converting too much history. Most projects convert open items and balances, and archive the rest for reporting.
  • !Loading in the wrong order so dependent objects reject in bulk.
  • !Skipping a full rehearsal on production-sized volume, so cutover timing is a guess.
  • !No owner for data cleansing, so bad legacy codes surface for the first time in the import errors.
  • !Forgetting that import programs depend on profile options and an open accounting period.

How an ERP-grounded AI assistant handles this

Conversion mapping and reject triage are repetitive reading work. A private AI layer running on your own servers can read the legacy extracts, the interface error tables and the mapping spec, then cluster rejects by cause and draft the correction rules, while every load still goes through your validated process. Netray builds this kind of assistant on-prem so legacy data and EBS schemas never leave your network.

See ERPray

Frequently asked questions

Can I load data directly into EBS base tables?

It is technically possible but unsupported for almost every object. Direct inserts skip validation, can leave child records or accounting out of sync, and make patching and upgrades risky. Use the open interface tables, public APIs or supported loaders, even when they are slower.

How many mock conversions should we run?

Plan at least two full rehearsals on production-sized data, plus smaller loops per object during build. The rehearsals prove timing, load order and reconciliation, and they are where most defects are found. A single dress rehearsal is usually not enough for finance and inventory objects.

Should we convert transaction history?

Usually only open items, balances and a limited history window. Full history inflates the effort and the risk. Many teams archive older history in a reporting database and convert only what is needed to operate and to age open balances correctly.

What tools do consultants use for EBS conversions?

Typically SQL*Loader or external tables for staging, PL/SQL packages for validation and loading interface tables, concurrent programs registered for the load, and Web ADI for small setup sets. Third-party migration tools exist, but they still load through the same interfaces and APIs.

Related

CIO briefing

Oracle EBS 12.2: stay on-prem or move to Fusion in 2026

Oracle has committed to Premier Support for E-Business Suite 12.2 into the early 2030s, which is longer than many CIOs assume, so there is no forced 2027 cliff for EBS specifically (that date is commonly confused with SAP ECC's 2027 deadline). The real decision drivers for EBS customers are: how much the org depends on customizations that do not translate to Fusion's SaaS model, whether continuous quarterly Fusion updates fit the org's change-management capacity, and whether Fusion's standardized processes are an acceptable tradeoff for the customizations currently in EBS.

Error fix

Fix FRM-40735: WHEN-VALIDATE-RECORD Trigger Raised Unhandled Exception ORA-06508

FRM-40735 with ORA-06508 almost always means a PL/SQL package used by the form was recompiled while your session still held the old cached state, or the trigger's own exception handling does not trap a real data error. Exit the form completely and re-enter it; if the error repeats for all users, recompile invalid objects on the database and check for a bad custom trigger.

Error fix

Oracle EBS Concurrent Manager Will Not Start: How to Fix It

A concurrent manager that will not start in Oracle EBS is almost always one of three things: the node registered in FND_NODES no longer matches the actual hostname (common after cloning), stale OS processes are blocking a fresh start, or the database and listener the manager connects to are unreachable. Check the internal manager log, verify the node name, clear stale processes, and restart with adcmctl.sh.

Error fix

Fix Oracle EBS Workflow Notification Mailer Not Sending Emails

When EBS notifications stop reaching inboxes, check the mailer status in Oracle Applications Manager (OAM) Workflow Manager first - a Suspended or Error status almost always points at an SMTP or IMAP connectivity or credential problem introduced by a mail server change, not at Workflow itself. Fix the mail server configuration, restart the mailer component, and requeue the backlog.

Error fix

Fix ORA-01555: Snapshot Too Old in Oracle EBS Concurrent Requests

ORA-01555 in a concurrent request means the program tried to read data from undo that has already been overwritten, usually because the report or custom PL/SQL ran long enough, or committed often enough mid-cursor, that the undo tablespace could not retain the needed read-consistent image. Increase UNDO_RETENTION and undo tablespace size first, then review the program for commits inside open cursor loops.

Error fix

Fix adop phase=prepare Failures in EBS 12.2 Online Patching

An adop prepare phase failure in EBS 12.2 almost always means the patch edition (patch file system) and the run edition are out of sync, or a previous cutover or cleanup did not complete cleanly. Read adop.log for the specific worker error, resync the file systems with adop phase=fs_clone if needed, and never start a new patch cycle on top of an unfinished one.

AI for ERP

AI for Oracle E-Business Suite, Without Leaving On-Prem

Hosting Oracle EBS 12.2 on-prem or in a private cloud? Add a private LLM over concurrent programs, interface tables and AP/PO data without moving to Fusion.

AI for ERP

AI for Oracle EBS in Industrial Manufacturing: WIP, MRP, and BOM Questions Answered Directly

AI for Oracle E-Business Suite in industrial manufacturing: WIP jobs, BOMs, MRP, inventory and purchasing answered in plain English, private and read-only.

Stuck on Oracle E-Business Suite?

Talk to engineers who work inside Oracle E-Business Suite every week, and who build private AI that answers these questions from your own ERP data.

Talk to an ERP engineerBrowse ERP Help
All ERP Help articles

Keep exploring

Free tools

ERP fixes and how-tos

Runs with your ERP

AI on your ERP

Integrations

Software

TermsOracle EBS Concurrent Program