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

> Source: https://netray.co/erp-help/data-conversion-in-oracle-ebs
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: ERP Help

## 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.

## Kind

how-to

## System

oracle-ebs

## Erp

Oracle E-Business Suite

## Module

Data Conversion / Open Interfaces

## Query

data conversion in oracle ebs

## Alternate Queries

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

## 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)

## Steps

### A repeatable EBS conversion approach

**Items:**

- Scope 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).
- Write a field-level mapping from legacy to EBS, including defaults, code translations (UOM, payment terms, tax codes) and who owns each rule.
- Extract to flat files or a staging schema, then clean duplicates and bad codes before anything touches EBS.
- Load the staging tables, then run a validation package that writes a status and error message per row (new, validated, error, loaded).
- Populate the open interface table for the object, for example MTL_SYSTEM_ITEMS_INTERFACE for items, and set the process flag the import program expects.
- Submit 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.
- Correct rejected rows in staging, reload, and repeat until the reject count is zero or accepted.
- Reconcile: row counts, control totals (open AP, open AR, GL trial balance, on-hand value) against the legacy source, and get sign-off.
- Rehearse the full sequence at least twice in a copy of production, timing it, before the cutover weekend.

## Sections

### Interface tables, APIs or loaders

**Body:**

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

**Body:**

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

**Body:**

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.

**Code:**

-- 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

**Body:**

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.

## 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.

## Ai Angle

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.

## Faq

### Can I load data directly into EBS base tables?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

data conversion in oracle ebs, oracle ebs data migration, oracle apps conversion, ebs open interface tables, MTL_SYSTEM_ITEMS_INTERFACE, AutoInvoice conversion, GL_INTERFACE journal import, oracle ebs data cleansing, ebs cutover reconciliation

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json