Search Results oe_order_headers_v




Overview
Explore in AI Chat

Overview

OE_ORDER_HEADERS_V is an APPS-owned database view within the ONT (Order Management) product family of Oracle E-Business Suite, valid in both 12.1.1 and 12.2.2. The ETRM documentation describes it succinctly as the "View for Order header used in sales order form." In practice, this view presents a denormalized, display-oriented projection of sales order header information, joining the base transaction table OE_ORDER_HEADERS with a range of reference and master data objects so that the Sales Orders form and downstream reporting can resolve identifiers into meaningful values without repeatedly coding the joins. Because it is a view rather than a table, it carries no independent storage, no DML of its own, and no object-level security beyond that inherited from its underlying objects and the row-level operating unit model governed by ORG_ID.

The view is widely used as a reporting and integration surface. Concurrent programs, custom concurrent extracts, BI Publisher reports, OAF extensions, and interface programs query it to retrieve order header context — customer, currency, pricing, and shipping attributes — in a single SELECT. It is important to note that the Sales Orders form itself does not rely solely on this view for maintenance; transactional DML against order headers is performed through the Order Management APIs and the base table. The view is therefore best regarded as a read interface.

Underlying Base Objects

According to the ETRM 12.2.2 metadata, OE_ORDER_HEADERS_V is defined over the following objects: AR_LOOKUPS, FND_CURRENCIES, HZ_CUST_ACCOUNTS, HZ_CUST_ACCOUNT_ROLES, HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL, HZ_LOCATIONS, HZ_PARTIES, HZ_PARTY_SITES, HZ_RELATIONSHIPS, MTL_PARAMETERS, OE_ORDER_HEADERS, OE_TRANSACTION_TYPES_TL, QP_LIST_HEADERS_TL, RA_RULES, and RA_TERMS_TL.

The single driving table is OE_ORDER_HEADERS, exposed as a synonym and supplying the ROWID (aliased ROW_ID) together with the full set of header columns. OE_TRANSACTION_TYPES_TL supplies the translated order type name; QP_LIST_HEADERS_TL supplies the price list name; RA_TERMS_TL and RA_RULES provide payment terms and accounting rule descriptions; and MTL_PARAMETERS contributes inventory organization context. The substantial set of HZ tables — HZ_CUST_ACCOUNTS, HZ_CUST_ACCOUNT_ROLES, HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL, HZ_LOCATIONS, HZ_PARTIES, HZ_PARTY_SITES, and HZ_RELATIONSHIPS — resolve the SOLD_TO, SHIP_TO, INVOICE_TO, and DELIVER_TO identifiers into customer names, site details, addresses, and contact information. AR_LOOKUPS and FND_CURRENCIES decode lookup codes and currency codes respectively. The joins are outer joins keyed on the party, account, and site identifiers, so header rows are preserved even where reference data is incomplete.

Key Columns

The view exposes the complete column set of OE_ORDER_HEADERS. Identification and control columns include ROW_ID, HEADER_ID, ORG_ID, ORDER_TYPE_ID, ORDER_NUMBER, VERSION_NUMBER, ORDER_SOURCE_ID, ORIG_SYS_DOCUMENT_REF, and SOURCE_DOCUMENT_ID. Date columns include ORDERED_DATE, REQUEST_DATE, and PRICING_DATE. Commercial and pricing attributes include PRICE_LIST_ID, AGREEMENT_ID, CONVERSION_RATE, CONVERSION_TYPE_CODE, TRANSACTIONAL_CURR_CODE, TAX_EXEMPT_FLAG, TAX_EXEMPT_NUMBER, TAX_EXEMPT_REASON_CODE, and TAX_POINT_CODE.

Fulfillment and financial terms are represented by SHIPPING_METHOD_CODE, FOB_POINT_CODE, FREIGHT_TERMS_CODE, SHIP_TOLERANCE_ABOVE and SHIP_TOLERANCE_BELOW, PARTIAL_SHIPMENTS_ALLOWED, INVOICING_RULE_ID, ACCOUNTING_RULE_ID, ACCOUNTING_RULE_DURATION, and PAYMENT_TERM_ID. Party and site columns include SOLD_TO_ORG_ID, SHIP_TO_ORG_ID, INVOICE_TO_ORG_ID, DELIVER_TO_ORG_ID, and the corresponding contact identifiers. Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_ID — support lineage and change tracking. The DFF columns ATTRIBUTE1 through ATTRIBUTE20, GLOBAL_ATTRIBUTE_CATEGORY, and GLOBAL_ATTRIBUTE1 through GLOBAL_ATTRIBUTE16 carry descriptive flexfield data.

Common Use Cases and Queries

Typical scenarios include order status reporting, open order ageing, customer-level order history, and source data extraction for downstream warehouses or integration hubs.

  • Retrieve headers for a specific operating unit and date range.
  • Join to OE_ORDER_LINES_ALL on HEADER_ID for line-level detail.
  • Extract orders awaiting fulfillment by filtering on flow status through the lines table.
  • Resolve customer and site names using the party and account identifiers already projected by the view.

Sample query:

SELECT header_id, order_number, ordered_date, transactional_curr_code, cust_po_number, sold_to_org_id, ship_to_org_id FROM oe_order_headers_v WHERE org_id = :p_org_id AND ordered_date BETWEEN :p_from AND :p_to AND NVL(expiration_date, SYSDATE + 1) > SYSDATE ORDER BY ordered_date DESC;

Because the view performs numerous outer joins, queries against it should always filter on ORG_ID and, where possible, on ORDER_NUMBER or HEADER_ID to limit the join cost. For high-volume extracts, indexing predicates on OE_ORDER_HEADERS should be reviewed, and the view should be preferred over ad hoc replication of its join logic in order to remain consistent with the Sales Orders form.