Explain Open Interface Tables and Import Programs in Oracle Applications

14 / 60

PL/SQLintermediate

Open Interface Tables and Import Programs

Understand the standard interface-table pattern EBS uses for high-volume data conversion and integration

Public APIs are correct and slow. For thirty thousand legacy invoices, EBS provides a different pattern: bulk-insert into a staging table, then run a standard import program.

Interface tables are like an airport customs pre-clearance area: travelers (data rows) queue up in a staging zone, get checked in batches, and anyone missing paperwork is pulled aside for correction while everyone else proceeds through — rather than the entire planeload being held up by one traveler's problem.

Key Concepts

    AP_INVOICES_INTERFACE          you populate these
    AP_INVOICE_LINES_INTERFACE
            |  Payables Open Interface Import (a concurrent program)
            |  validation
            v
    AP_INVOICES_ALL                the real invoices
    AP_INVOICE_DISTRIBUTIONS_ALL

The interface tables exist for every major module:

    RA_INTERFACE_LINES_ALL         receivables
    MTL_TRANSACTIONS_INTERFACE     inventory
    GL_INTERFACE                   general ledger
    PO_HEADERS_INTERFACE           purchasing

The pattern separates loading from validating, and that separation is what makes it usable at volume.

Rows go in fast, as a bulk insert. Validation happens afterwards, per row, and a bad row does not stop the good ones.

Partial failure is the normal outcome, not an error.

    30,000 rows loaded
    -> 29,400 created
    ->    600 rejected, still in the interface table with reasons

Fix the 600 and resubmit. The 29,400 are not reprocessed, because the import marks what it has taken.

Use a GROUP_ID or batch identifier. Nearly every interface table has one, and it is what lets you submit one batch at a time and report on it:

GROUP_ID

    populate with GROUP_ID = 20260930_BATCH1
    run the import with that GROUP_ID as a parameter
    query rejections for that GROUP_ID

Without it, a second load started while the first is being corrected mixes the two, and neither can be reconciled.

The sequence.

  1. Populate the interface tables, with a batch identifier
  2. Run the import program for that batch
  3. Read the exception report, or query the rejections table
  4. Correct the source data
  5. Resubmit only the failed rows

Step 3 is the one that gets skipped. Rows sitting in an interface table are invisible to the application. A conversion that "loaded successfully" and is short by six hundred invoices is usually a rejection report nobody opened.

API or interface table. Small volumes and immediate feedback: the API. Large conversions and scheduled feeds: the interface table. Both are supported; the interface tables are what Oracle's own programs use internally for the same reason.

What the interviewer is probing.1. "What is the standard interface pattern?" Probing: the model. Stalls: "Load the table." Moves up: insert into the interface table, run the import concurrent program, then review the error table and correct and resubmit.

2. "Why are interface tables still used when APIs exist?" Probing: the judgement. Stalls: "Legacy." Moves up: they handle high volume efficiently in a set-based way, where a row-by-row API call is far slower.

3. "What must your load process handle?" Probing: the engineering. Stalls: "Inserting." Moves up: reruns — a batch identifier and cleanup of prior rows, or a failed run leaves partially processed data behind.

4. "How do you reconcile what was loaded?" Probing: the control. Stalls: "Count rows." Moves up: compare source counts and totals against imported records plus rejected ones, so nothing is silently dropped.