Search Results hz_party_sites




Overview
Explore in AI Chat

Overview

ONT.OE_LINES_IFACE_ALL is the multi-organization open interface table for sales order lines in Oracle Order Management. It is the staging and inbound processing mechanism through which external systems, legacy applications, EDI translators, and custom programs load sales order line and shipment-level data into Oracle E-Business Suite. Rows inserted into this table are picked up by the Order Management concurrent program (typically "Process Order API" / Order Import) and validated, then written into the base order tables OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL. Because it is an "_ALL" table, it is partitioned by ORG_ID and must always be accessed with the appropriate operating unit context in 12.1.1 and 12.2.2.

The table is exceptionally wide, with 478 columns documented in ETRM 12.2.2, reflecting the full breadth of sales order line attributes: ordering, pricing, shipping, tax, service, project, and customer (TCA) references. It also carries interface control columns (INTERFACE_STATUS, ERROR_FLAG, STATUS_FLAG, OPERATION_CODE, REQUEST_ID) that drive import processing and error reporting. The heuristic Data Vault classification mined from the foreign key structure is link, which is a reasonable modeling suggestion: the table predominantly joins business entities (customers, sites, contacts, items, organizations, rules, salesreps) rather than holding a single stable descriptive entity.

Key Information Stored

Although 478 columns exist, the following are the most operationally significant:

No surrogate primary key column is documented for this interface table; it is populated and consumed as a staging area rather than as a durable keyed entity. Business-key candidates are the source combination of ORDER_SOURCE_ID, ORIG_SYS_DOCUMENT_REF, ORIG_SYS_LINE_REF, and ORIG_SYS_SHIPMENT_REF, which uniquely identify an external order line shipment across repeated imports. The foreign keys listed in ETRM (for example SHIP_TO_PARTY_SITE_ID to HZ_PARTY_SITES and SHIP_TO_PARTY_SITE_USE_ID to HZ_PARTY_SITE_USES) act as the referential integrity anchors rather than a single-row uniqueness constraint.

Common Use Cases and Queries

The dominant use case is bulk order import. A typical pattern is to stage line rows, then submit Order Import with a filter on REQUEST_ID or INTERFACE_STATUS, and finally query the table to diagnose rejected records:

  • Locate unprocessed or errored lines: SELECT LINE_NUMBER, ORIG_SYS_LINE_REF, ERROR_FLAG, INTERFACE_STATUS FROM OE_LINES_IFACE_ALL WHERE ORG_ID = :p_org AND INTERFACE_STATUS IN ('1','2') ORDER BY LINE_NUMBER;
  • Resolve a party site reference to its TCA definition: SELECT l.ORIG_SYS_LINE_REF, ps.PARTY_SITE_NUMBER, ps.ADDRESS1, ps.CITY FROM OE_LINES_IFACE_ALL l, HZ_PARTY_SITES ps WHERE l.SHIP_TO_PARTY_SITE_ID = ps.PARTY_SITE_ID AND l.ORG_ID = :p_org;
  • Reconciliation reporting — count staged versus successfully imported lines by source system: SELECT ORDER_SOURCE_ID, INTERFACE_STATUS, COUNT(*) FROM OE_LINES_IFACE_ALL WHERE ORG_ID = :p_org GROUP BY ORDER_SOURCE_ID, INTERFACE_STATUS;

Reporting use cases include pre-import validation reports (pricing, tax, party site completeness), source-system reconciliation, and troubleshooting of party site or customer account mismatches flagged by Order Import.

Related Objects

The interface table is joined to the following objects through documented foreign keys:

Downstream, the imported content is written by Order Import into OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL; those tables, not OE_LINES_IFACE_ALL, are the permanent order repository.