Search Results order_version

Overview

The ASO_I_SEARCH_ORDERS_V view is a seeded database object owned by the APPS schema within the ASO (Order Capture) product family. Its status is documented as VALID in ETRM for both Oracle EBS 12.1.1 and 12.2.2. The view functions as a denormalized, presentation-oriented query surface that exposes order header information suitable for search, lookup, and integration scenarios within the Order Capture module. Rather than requiring callers to join order headers, customer records, and contacts independently, the view pre-assembles the most frequently referenced attributes of a sales order into a single, flat result set. This makes it particularly useful for building search pages, LOVs (lists of values), and lightweight integration payloads where a complete OE_ORDER_HEADERS_ALL record is not required. The "I" in the object name typically denotes an interface or internal search-oriented view, indicating its intended role as a supporting query layer rather than a transactional base table.

Underlying Base Objects

The view's View Text definition selects from OE_ORDER_HEADERS_ALL OH as its primary driver, outer-joining to the customer and contact tables. The documented referenced base objects in the 12.2.2 metadata include ASO_ORDER_INT (PACKAGE), along with synonyms HZ_CUST_ACCOUNTS, HZ_ORG_CONTACTS, HZ_PARTIES, HZ_RELATIONSHIPS, and OE_ORDER_HEADERS. In practice, the SELECT text joins OE_ORDER_HEADERS_ALL to RA_CUSTOMERS and RA_CONTACTS via the SOLD_TO_ORG_ID and SOLD_TO_CONTACT_ID columns using outer joins (denoted by the (+) syntax on the lookup side). The ASO_ORDER_INT package supplies two scalar function calls embedded in the projection: GET_HEADER_STATUS and TOTAL_ORDER_PRICE. This means the view is not a pure table join; invoking it triggers package logic, which can carry a performance cost when queried over large volumes.

Key Columns

• HEADER_ID — Unique identifier of the order header; the primary join key to OE_ORDER_HEADERS_ALL.
• ORDER_NUMBER — User-facing order number.
• ORDER_VERSION / VERSION_NUMBER — The version number of the order header. This is the column most closely associated with the user's "order_version" search and reflects header versioning used for revision tracking.
• ORDERED_DATE, CREATION_DATE, LAST_UPDATE_DATE — Standard dates for filtering and sorting search results.
• CUST_ACCT_ID / SOLD_TO_ORG_ID — Sold-to organization identifier.
• CUSTOMER_NAME, CUSTOMER_NUMBER, ACCOUNT_NUMBER — Denormalized customer attributes for display.
• CURRENCY_CODE (TRANSACTIONAL_CURR_CODE) — Order currency.
• CUST_PO_NUMBER — Customer purchase order reference.
• SOLD_TO_CONTACT_ID, SOLD_TO_CONTACT_FIRST_NAME, SOLD_TO_CONTACT_LAST_NAME — Contact details resolved from the contact tables.
• STATUS — Derived from ASO_ORDER_INT.GET_HEADER_STATUS.
• TOTAL_ORDER_PRICE — Derived from ASO_ORDER_INT.TOTAL_ORDER_PRICE.
• PARTY_ID — Trading party identifier linking to HZ_PARTIES.

Common Use Cases and Queries

Typical uses include order search pages, status dashboards, and integration extracts. Because STATUS and TOTAL_ORDER_PRICE invoke package functions, filters should be applied predominantly on indexed header columns such as ORDER_NUMBER or ORDERED_DATE.

• Retrieve order version details: SELECT ORDER_NUMBER, ORDER_VERSION FROM APPS.ASO_I_SEARCH_ORDERS_V WHERE ORDER_NUMBER = :p_order_number;
• List recent orders for a customer: SELECT ORDER_NUMBER, CUSTOMER_NAME, STATUS FROM APPS.ASO_I_SEARCH_ORDERS_V WHERE CUST_ACCT_ID = :p_cust_id ORDER BY ORDERED_DATE DESC;
• Lookup orders by customer PO: SELECT ORDER_NUMBER, TOTAL_ORDER_PRICE FROM APPS.ASO_I_SEARCH_ORDERS_V WHERE CUST_PO_NUMBER = :p_po;