Results for “ap_invoices_interface”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AP_INVOICES_INTERFACE is the Payables Open Interface staging table owned by the AP schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It serves as the primary inbound conduit for supplier invoice data entering Payables from external sources: legacy system conversions, feeder programs, EDI transmissions, spreadsheet uploads via SQL*Loader and external tables, and third-party applications. Records inserted here are read by the Payables Open Interface Import program, which validates each row and creates corresponding rows in AP_INVOICES_ALL, AP_INVOICE_LINES_ALL, and downstream distribution tables. Rows that fail validation remain in the table with a STATUS of 'REJECTED' and a REQUEST_ID referencing the import run, enabling correction and re-submission.

The table is wide by design. The documented 12.2.2 schema carries 155 columns, reflecting the breadth of attributes required to describe a complete invoice header without referencing the production invoice tables during import. In Data Vault terms, the mined foreign-key structure suggests a hub-leaning classification: the table is anchored on a single surrogate key and holds descriptive attributes, but the absence of extensive outward foreign-key relationships on most descriptive columns means it is best modeled as a hub or staging entity rather than a pure satellite.

Key Information Stored

The primary key is INVOICE_ID, backed by the unique index AP_INVOICES_INTERFACE_U1 (INVOICE_ID). The unique index also serves as a business-key candidate, though in practice INVOICE_ID here is typically populated by sequence before import and is not the business invoice number. The substantive identifier is INVOICE_NUM, paired with the supplier attributes.

• INVOICE_ID – surrogate primary key; unique per staged row.
• INVOICE_NUM and INVOICE_TYPE_LOOKUP_CODE – supplier invoice number and invoice type (Standard, Credit, Debit, Prepayment, etc.).
• VENDOR_ID, VENDOR_NUM, VENDOR_NAME, VENDOR_SITE_ID, VENDOR_SITE_CODE – supplier identification; one of ID or name/code pair is required depending on validation setup.
• INVOICE_DATE and INVOICE_AMOUNT – core financial attributes of the invoice header.
• INVOICE_CURRENCY_CODE, EXCHANGE_RATE, EXCHANGE_RATE_TYPE, EXCHANGE_DATE – currency and conversion controls.
• TERMS_ID and TERMS_NAME – payment terms selection.
• PO_NUMBER – purchase order reference for matching.
• STATUS – staging lifecycle flag: 'NEW' or null on entry, 'REJECTED' or 'PROCESSED' after import.
• SOURCE – source identifier used to segregate inbound feeds.
• GROUP_ID – logical grouping of a batch for import selection.
• REQUEST_ID – concurrent request that processed or rejected the row.
• ORG_ID and LEGAL_ENTITY_ID – operating unit and legal entity; LEGAL_ENTITY_ID carries a documented foreign key to FV_LEGAL_ENTITIES.
• DESCRIPTION – free-form invoice narrative.
• ACCTS_PAY_CODE_COMBINATION_ID and GL_DATE – accounting and distribution date controls.

Common Use Cases and Queries

The dominant use case is populating the table from a feeder script or external file and then submitting Payables Open Interface Import with a Source and Group parameter that selects the intended batch. Reporting queries typically monitor import health. For example, counting staged rows by source and status:

• SELECT source, status, COUNT(*) FROM ap.ap_invoices_interface GROUP BY source, status;
• SELECT invoice_id, invoice_num, vendor_name, invoice_amount FROM ap.ap_invoices_interface WHERE status = 'REJECTED' AND request_id = :req_id;
• SELECT a.invoice_num, b.column_name, b.error_message FROM ap_invoices_interface a, ap_interface_rejections b WHERE a.invoice_id = b.parent_id AND b.parent_table = 'AP_INVOICES_INTERFACE';

Other scenarios include pre-import data quality checks (missing vendor, zero amount, invalid currency), reconciliation of staged counts against created invoices via REQUEST_ID, and purging successfully processed rows. Because the table is a staging area, scripts should always scope deletes and updates by STATUS, GROUP_ID, or SOURCE to avoid disturbing batches pending import.

Related Objects

• AP_EXPENSE_FEED_DISTS_ALL – references AP_INVOICES_INTERFACE via AP_EXPENSE_FEED_DISTS_ALL.INVOICE_ID; supplies invoice distribution lines for import. This is the documented child of the foreign-key relationship.
• FV_LEGAL_ENTITIES – referenced by AP_INVOICES_INTERFACE.LEGAL_ENTITY_ID; resolves the legal entity for the invoice.
• AP_INVOICES_ALL – target header table populated by Payables Open Interface Import.
• AP_INVOICE_LINES_ALL – target line table created alongside the header during import.
• AP_INVOICE_LINES_INTERFACE – companion staging table for invoice lines, keyed by the same invoice identifier.
• AP_INTERFACE_REJECTIONS – captures validation errors keyed to the interface row and parent table.
• Payables Open Interface Import – the concurrent program that reads this table and writes to AP_INVOICES_ALL.
• AP_INVOICES_INTERFACE_S – sequence used to generate INVOICE_ID values prior to insert.