Results for “oe_headers_iface_all”

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

Overview

OE_HEADERS_IFACE_ALL is the open interface (staging) table for sales order headers in Oracle Order Management (ONT). Owned by the ONT schema and implemented as a multi-org table, it serves as the inbound landing zone through which external systems, legacy applications, EDI feeds, XML messages, and custom programs load order header data before the Order Management concurrent programs validate and import those records into the base order tables. Rows persist here until the import process either promotes them into OE_ORDER_HEADERS_ALL or flags them with an error status for correction and reprocessing.

In Data Vault modeling terms, the table is heuristic-classified as a link. This classification is consistent with its structure: it is dominated by foreign-key references to parties, customer accounts, organizations, currencies, rules, and terms, making it a transactional association point rather than a pure hub or descriptive satellite. It is important to note that this is a modeling suggestion derived from the FK structure, not a declared design property of the table.

Key Information Stored

The table carries 333 documented columns in release 12.2.2. The most significant include:

• HEADER_ID — Surrogate identifier for the header record; it also functions as a foreign key to OE_ORDER_HEADERS_ALL once the record is imported.
• ORDER_SOURCE_ID / ORDER_SOURCE — Identifies the originating order source, joining to OE_ORDER_SOURCES and governing defaulting behavior during import.
• ORIG_SYS_DOCUMENT_REF, CHANGE_SEQUENCE, CHANGE_REQUEST_CODE, OPERATION_CODE — Source-system document reference and change-control fields that drive insert versus update processing.
• ORDER_TYPE_ID / ORDER_TYPE, ORDER_NUMBER, ORDERED_DATE — Business keys for the order header.
• SOLD_TO_ORG_ID, SHIP_TO_ORG_ID, INVOICE_TO_ORG_ID, DELIVER_TO_ORG_ID — Operating unit and organization context for the various party roles.
• PAYMENT_TERM_ID / PAYMENT_TERM and CUSTOMER_PAYMENT_TERM_ID / CUSTOMER_PAYMENT_TERM — Payment terms. Both reference RA_TERMS_B, the receipts terms base table, and are the join path behind the RA_TERMS_B search that surfaces this object.
• SALESREP_ID, INVOICING_RULE_ID, ACCOUNTING_RULE_ID, AGREEMENT_ID — References to RA_SALESREPS_ALL, RA_RULES, and OE_AGREEMENTS_B respectively.
• TRANSACTIONAL_CURR_CODE, PRICE_LIST_ID, CONVERSION_RATE — Currency and pricing context.
• SOLD_FROM_ORG_ID — Reference to HR_ALL_ORGANIZATION_UNITS, establishing the fulfillment organization.
• INTERFACE_STATUS, ERROR_FLAG, READY_FLAG, STATUS_FLAG, REJECTED_FLAG, FORCE_APPLY_FLAG — Interface control columns determining whether a row is eligible for import, applied, or rejected.

Business-key candidates are represented by ORDER_NUMBER combined with ORDER_SOURCE and ORG_ID rather than by any single surrogate column. The table also carries extensive descriptive and legacy-address columns (SHIP_TO_ADDRESS1–4, INVOICE_ADDRESS1–4, DELIVER_TO_ADDRESS1–4, END_CUSTOMER_*), 20 ATTRIBUTE columns, 20 GLOBAL_ATTRIBUTE columns, and the standard WHO/audit and concurrent-program columns (CREATED_BY, LAST_UPDATE_DATE, REQUEST_ID, PROGRAM_ID).

Common Use Cases and Queries

Typical applications include validating staged feed data prior to submission, diagnosing rejected orders, and reconciling payment terms on imported orders.

• Detecting rows not yet imported: SELECT HEADER_ID, ORDER_NUMBER, ERROR_FLAG, INTERFACE_STATUS FROM OE_HEADERS_IFACE_ALL WHERE ERROR_FLAG = 'Y';
• Reviewing rows awaiting processing: SELECT ORDER_NUMBER, ORG_ID, PAYMENT_TERM_ID, READY_FLAG FROM OE_HEADERS_IFACE_ALL WHERE READY_FLAG = 'Y' AND INTERFACE_STATUS IS NULL;
• Resolving payment terms for a staged order, which is the relevant pattern when investigating RA_TERMS_B references: SELECT h.ORDER_NUMBER, t.NAME, t.START_DATE_ACTIVE, t.END_DATE_ACTIVE FROM OE_HEADERS_IFACE_ALL h, RA_TERMS_B t WHERE h.PAYMENT_TERM_ID = t.TERM_ID;
• Confirming that a party or site reference is valid before import by joining to HZ_CUST_ACCOUNTS, HZ_PARTIES, or HZ_CUST_SITE_USES_ALL on the corresponding *_ID columns.
• Reporting on staging volume by order source or operating unit for interface throughput monitoring.

Because RA_TERMS_B carries the term ID, NAME, and effective-date range, joins between it and OE_HEADERS_IFACE_ALL are frequently used to verify that the staged term is currently active for the order date.

Related Objects

The documented foreign keys connect this table to the following principal objects:

• OE_ORDER_HEADERS_ALL — the target base table for imported records, joined on HEADER_ID.
• RA_TERMS_B — referenced twice, via PAYMENT_TERM_ID and CUSTOMER_PAYMENT_TERM_ID.
• RA_RULES — referenced via INVOICING_RULE_ID and ACCOUNTING_RULE_ID.
• RA_SALESREPS_ALL — referenced via SALESREP_ID.
• HZ_PARTIES — referenced through the SOLD_TO_PARTY_ID, SHIP_TO_PARTY_ID, INVOICE_TO_PARTY_ID, DELIVER_TO_PARTY_ID, and END_CUSTOMER_PARTY_ID columns.
• HZ_CUST_ACCOUNTS — referenced via CUSTOMER_ID, DELIVER_TO_CUSTOMER_ID, and END_CUSTOMER_ID.
• HZ_CUST_ACCT_SITES_ALL — referenced via SHIP_TO_CUSTOMER_ID.
• HZ_CUST_SITE_USES_ALL — referenced via SOLD_TO_SITE_USE_ID and END_CUSTOMER_SITE_USE_ID.
• HZ_PARTY_SITES / HZ_PARTY_SITE_USES — referenced via SHIP_TO_PARTY_SITE_ID and END_CUSTOMER_PARTY_SITE_USE_ID.
• FND_CURRENCIES, HR_ALL_ORGANIZATION_UNITS, OE_ORDER_SOURCES, OE_AGREEMENTS_B, SO_ORDER_TYPES_115_ALL — supporting lookups for currency, organization, source, agreement, and order type.

Records migrate from this interface table into OE_ORDER_HEADERS_ALL (and its lines into OE_LINES_IFACE_ALL) through the Order Import program, which is the primary consumer of the staged data.