Search Results oe_order_headers_pk

Overview

ONT.OE_ORDER_HEADERS_ALL is the master header table in Oracle Order Management (ONT). It stores one row per order header, capturing the order-level attributes required to price, book, fulfill, and invoice a sales order. It is the parent of the order lines (OE_ORDER_LINES_ALL) and the anchoring entity for holds, sales credits, price adjustments, acknowledgements, and downstream fulfillment and financial flows. The table is owned by the ONT schema and is valid in both EBS 12.1.1 and 12.2.2; the documented physical schema lists 178 columns.

From a Data Vault modeling perspective, the metadata classifies this object as a hub. This is a heuristic classification derived from the foreign-key structure: the table carries a single-column surrogate primary key (HEADER_ID) and is heavily referenced by many child and dependent tables. A hub classification suggests HEADER_ID would function as the business key for a header hub, with descriptive attributes (customer, currency, terms, dates) modeled as satellites and relationships to customers, organizations, and lookups modeled as links.

Key Information Stored

The table exposes a primary key and a small number of business-key candidates, plus a large set of descriptive and control columns.

• HEADER_ID — the surrogate primary key (OE_ORDER_HEADERS_PK), used by virtually every child table as the foreign key.
• ORDER_NUMBER, ORDER_TYPE_ID, VERSION_NUMBER — the composite business-key candidate defined by unique index OE_ORDER_HEADERS_U1/OE_ORDER_HEADERS_U2. VERSION_NUMBER supports order versioning and change history.
• ORG_ID — operating unit, the multi-org partitioning column that governs which business unit owns the order.
• ORDER_TYPE_ID — foreign key to OE_TRANSACTION_TYPES_ALL, defining the transaction type (order, return, quote) and its workflow.
• ORDERED_DATE, REQUEST_DATE, PRICING_DATE, BOOKED_DATE — key order lifecycle dates used for aging and cycle-time reporting.
• SOLD_TO_ORG_ID, SHIP_TO_ORG_ID, INVOICE_TO_ORG_ID, SHIP_FROM_ORG_ID — the party/site roles (customer and organization) associated with the order; SOLD_FROM_ORG_ID and SHIP_FROM_ORG_ID reference HR_ALL_ORGANIZATION_UNITS.
• TRANSACTIONAL_CURR_CODE, PRICE_LIST_ID, PAYMENT_TERM_ID, INVOICING_RULE_ID, ACCOUNTING_RULE_ID — pricing, currency, and receivables/accounting defaults.
• FLOW_STATUS_CODE, OPEN_FLAG, BOOKED_FLAG, CANCELLED_FLAG — status and control flags that drive the Order Management workflow.
• ATTRIBUTE1–20, GLOBAL_ATTRIBUTE1–20, TP_ATTRIBUTE1–15 — DFF, global, and trade-partner flexfields for extensibility.
• END_CUSTOMER_ID, END_CUSTOMER_CONTACT_ID, END_CUSTOMER_SITE_USE_ID — end-customer references added for drop-ship and sales-order scenarios.

Common Use Cases and Queries

Typical uses involve joining headers to lines, customers, and lookup tables for order status, backlog, and fulfillment reporting. A common pattern retrieves an order by its business key:

SELECT h.header_id, h.order_number, h.ordered_date,
       h.transactional_curr_code, h.flow_status_code
FROM   oe_order_headers_all h
WHERE  h.order_number = :order_number
AND    h.org_id = :org_id;

Backlog and booked-order reporting filters on the control flags:

SELECT h.order_number, h.ordered_date, l.line_number, l.ordered_quantity
FROM   oe_order_headers_all h,
       oe_order_lines_all   l
WHERE  h.header_id = l.header_id
AND    h.booked_flag = 'Y'
AND    h.open_flag   = 'Y'
AND    h.cancelled_flag = 'N';

Join to HZ_CUST_ACCOUNTS via SOLD_TO_ORG_ID for customer details, and to OE_ORDER_LINES_ALL for line-level reporting. Note that EBS reports should generally query via the Order Management views (e.g., OE_ORDER_HEADERS_V) where available to respect multi-org security and joins.

Related Objects

• OE_ORDER_LINES_ALL — child lines; join on OE_ORDER_LINES_ALL.HEADER_ID = OE_ORDER_HEADERS_ALL.HEADER_ID.
• OE_ORDER_HOLDS_ALL — order-level holds; join on HEADER_ID.
• OE_PRICE_ADJUSTMENTS and OE_SALES_CREDITS — pricing and sales-credit details keyed by HEADER_ID.
• OE_HEADER_ACKS — inbound/outbound order acknowledgements tied to the header.
• OE_TRANSACTION_TYPES_ALL (via ORDER_TYPE_ID), HZ_CUST_ACCOUNTS (via SOLD_TO_ORG_ID), and RA_TERMS_B, RA_RULES, QP_LIST_HEADERS_B for terms, rules, and price lists.
• India-localization table JAI_OM_OE_BOND_TRXS — bond transaction records for excise/export scenarios; join on JAI_OM_OE_BOND_TRXS.ORDER_HEADER_ID = OE_ORDER_HEADERS_ALL.HEADER_ID. This is the object referenced by the search term "jai_om_oe_bond_trxs".

Other localized dependents include JAI_OM_LC_MATCHINGS, JAI_OM_OE_GEN_TAXINV_T, JAI_OM_OE_RMA_LINES, and JAI_OM_OE_SO_LINES, all of which carry a HEADER_ID/ORDER_HEADER_ID foreign key to this table.