Search Results date_ordered




Overview
Explore in AI Chat

Overview

WSH_ORDER_VALUESET_V is a value-set view exposed within the Oracle E-Business Suite Order Entry (OE) product family. Its name aligns it with the WSH (Shipping Execution) schema, and it serves as a lightweight, denormalized projection of order header data intended for use as a value-set source, typically bound to a descriptive flexfield, a report parameter, or a lookup-style list of values that must present order numbers in a business-friendly format alongside customer and order-date context.

The view returns one row per order header and exposes four columns: HEADER_ID, ORDER_NUMBER, CUSTOMER_NAME, and DATE_ORDERED. This makes it suitable for any consumer that must resolve a displayed order number or customer name back to the numeric SO_HEADERS_ALL.HEADER_ID primary key. In Oracle EBS 12.1.1 and 12.2.2, such views are commonly referenced by concurrent programs, OAF-based pages, and business intelligence queries that cannot or should not join directly against transactional base tables.

The ETRM metadata records the view with the description "Not implemented in this database," which indicates that in the documented environment the view definition exists in the data dictionary metadata but the object itself may not have been deployed. Consumers should therefore verify existence with ALL_VIEWS before relying on it at runtime.

Underlying Base Objects

The view text documents a three-table join. The driving table is SO_HEADERS_ALL (aliased SH), the Order Management order header table that stores ORDER_NUMBER, CUSTOMER_ID, and ORDER_TYPE_ID. It is joined to RA_CUSTOMERS on CUSTOMER_ID = SH.CUSTOMER_ID to retrieve the customer name, and to SO_ORDER_TYPES on ORDER_TYPE_ID = SH.ORDER_TYPE_ID to obtain the order type name.

Although no base objects are separately documented in the ETRM object metadata, the embedded SQL plainly establishes these dependencies. Notably, the SELECT list references CUSTOMER_NAME and DATE_ORDERED without explicit aliases, implying they resolve unambiguously from the joined RA_CUSTOMERS and SO_HEADERS_ALL sources respectively. No WHERE clause filters the result set, so the view is effectively a full projection across all order headers that have matching customer and order-type rows.

Key Columns

  • HEADER_ID — The surrogate primary key from SO_HEADERS_ALL. This is the join key used whenever the view is consumed as a value set, because displayed text (order number) must map back to a numeric identifier.
  • ORDER_NUMBER — The concatenation of the order number right-padded to twenty characters with the order type name, produced by RPAD(TO_CHAR(SH.ORDER_NUMBER), 20) || SO_ORDER_TYPES.NAME. The padding creates a fixed-width prefix, after which the order type name is appended, yielding a readable label such as the order number followed by its type.
  • CUSTOMER_NAME — The customer name sourced from RA_CUSTOMERS, providing immediate business context without a separate lookup.
  • DATE_ORDERED — The order date from SO_HEADERS_ALL. This column is the one most frequently targeted by ad hoc searches, including the query term "date_ordered," since it enables date-based filtering and ordering directly on the view.

Common Use Cases and Queries

Typical uses include populating order-number list of values, supplying parameters to shipping and order reports, and supporting quick date-bounded extracts. A basic retrieval follows:

  • SELECT HEADER_ID, ORDER_NUMBER, CUSTOMER_NAME, DATE_ORDERED FROM WSH_ORDER_VALUESET_V WHERE DATE_ORDERED >= :P_FROM_DATE ORDER BY DATE_ORDERED DESC;
  • SELECT HEADER_ID, ORDER_NUMBER FROM WSH_ORDER_VALUESET_V WHERE CUSTOMER_NAME = :P_CUSTOMER;
  • SELECT DATE_ORDERED, COUNT(*) FROM WSH_ORDER_VALUESET_V GROUP BY DATE_ORDERED ORDER BY DATE_ORDERED;

Because the view carries no status or organization filter, it should not be used for transactional validation where only open or shipped orders are relevant; instead, join back to SO_HEADERS_ALL for additional qualifiers. Performance is generally acceptable for value-set and reporting volumes, but large extracts benefit from an index on SO_HEADERS_ALL.ORDER_NUMBER and DATE_ORDERED.