Search Results wsh_cmstpw_orders_v




Overview
Explore in AI Chat

Overview

WSH_CMSTPW_ORDERS_V is a shipping execution view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It is one of the compatibility views required by the Oracle Shipping Execution module to support the integration between outbound delivery data and downstream trade compliance or order management processing. The view presents a de-duplicated list of order and delivery source identifiers, classified by a document type and a source type, together with the client identifier of the owning operating unit.

The name includes the "CMSTPW" token, which reflects its function as an interface view for conveying shipment and order reference data. Reporting users, integration developers, and support analysts query this view primarily to resolve the originating sales order or release number associated with a delivery detail. The column SOURCE_HEADER_NUMBER is the key identifier exposed for that purpose, and it is the column most frequently searched against this object.

Underlying Base Objects

The view is defined over four documented base objects: WSH_DELIVERY_DETAILS, WSH_DELIVERY_ASSIGNMENTS_V, WSH_NEW_DELIVERIES, and WSH_TRANSACTIONS_HISTORY. The first three appear as the core of the three UNION ALL branches that make up the view text, while WSH_DELIVERY_ASSIGNMENTS_V and WSH_TRANSACTIONS_HISTORY appear in the second branch, which handles deliveries whose source code is 'WSH'.

WSH_DELIVERY_DETAILS is the primary driving object in every branch. It supplies the source header number, source code, line direction, container flag, released status, and client identifier. WSH_DELIVERY_ASSIGNMENTS_V links each delivery detail to its parent delivery, and WSH_NEW_DELIVERIES supplies the delivery name and status code used to constrain the result set. WSH_TRANSACTIONS_HISTORY is joined by delivery name and entity number to restrict the WSH-sourced branch to deliveries that were received from an inbound shipment transaction.

All three branches filter on CONTAINER_FLAG = 'N' and LINE_DIRECTION IN ('O','IO'), which restricts the output to outbound order lines and inter-org lines rather than LPN containers or inbound lines.

Key Columns

  • DOCUMENT_TYPE — Distinguishes the origin of the record. A value of 'SR' denotes a source record drawn directly from WSH_DELIVERY_DETAILS where the source code is not 'WSH'; a value of 'SA' denotes a shipment or assignment record resolved through a delivery assignment.
  • SOURCE_HEADER_NUMBER — The originating document header identifier, typically the sales order number or release number behind the delivery line. This is the most commonly filtered and joined column.
  • SOURCE_TYPE — Qualifies the document type. 'OTH' is emitted for source code values other than 'WSH' and for the WSH-sourced delivery branch, while 'STND' is emitted for the standard order entry branch where the source code is 'OE' and the delivery is in-transit or closed.
  • CLIENT_ID — The operating unit or business entity identifier associated with the delivery detail, used for multi-org security filtering and reporting by legal entity.

Common Use Cases and Queries

A typical reporting requirement is to list every distinct order or release number that has been shipped, segmented by document type and source type:

  • SELECT DISTINCT document_type, source_header_number, source_type, client_id FROM apps.wsh_cmstpw_orders_v WHERE source_header_number = :order_number;
  • SELECT source_header_number, COUNT(*) FROM apps.wsh_cmstpw_orders_v GROUP BY source_header_number;
  • SELECT DISTINCT source_header_number FROM apps.wsh_cmstpw_orders_v WHERE document_type = 'SA' AND source_type = 'STND' AND client_id = :org_id;

Because SOURCE_HEADER_NUMBER is not guaranteed unique — the same header can appear across multiple delivery details and across document types — queries should apply DISTINCT or aggregate functions before use in downstream processing. The view is read-only and its column list is fixed, so it should be used as a lookup and reconciliation source rather than as a base for DML.