Search Results wsh_delivery_assignments_v




Overview
Explore in AI Chat

Overview

WSH_DELIVERY_ASSIGNMENTS_V is an APPS-owned database view within the Oracle E-Business Suite Shipping Execution (WSH) module. It exposes a filtered and lightly transformed projection of the delivery assignment records that link delivery details to deliveries and, where applicable, to parent deliveries. Unlike many reporting views in the WSH schema, this view does not join additional tables; its purpose is to restrict the underlying assignment data to a defined set of assignment types and to normalize the TYPE column value for downstream consumers.

Because the view belongs to the APPS schema and carries a VALID status in both 12.1.1 and 12.2.2, it is treated as a stable interface for reporting, concurrent programs, and integrations that need to reason about which delivery details are assigned to which deliveries. It is commonly consumed by shipping reports, delivery confirmation logic, and custom extensions that must distinguish standard shipment assignments from other assignment categories.

Underlying Base Objects

The view is defined directly over a single base object, referenced in the metadata as WSH_DELIVERY_ASSIGNMENTS (SYNONYM). No joins, aggregations, or subqueries are present in the view text. The defining query selects the assignment identifier, the delivery and parent delivery identifiers, the delivery detail and parent delivery detail identifiers, the standard WHO audit columns, the program and request context columns, the ACTIVE_FLAG, and a derived TYPE value.

The TYPE transformation is the principal behavioral characteristic of the view. The projection computes NVL(WDA.TYPE, 'S'), meaning any assignment whose TYPE is null is surfaced as 'S'. The WHERE clause then filters rows so that only assignments with TYPE in ('S','O') or with a null TYPE are returned. In effect, the view presents standard and 'O'-type assignments plus untagged rows normalized to 'S', while excluding any other type codes that may exist in the base table.

Key Columns

Common Use Cases and Queries

Typical scenarios include identifying all active delivery details assigned to a given delivery, correlating details with parent deliveries, and building shipping reports that exclude non-standard assignment types. Because the view hides underlying type codes other than 'S' and 'O', it is often preferred over the base table when the reporting requirement aligns with standard shipment semantics.

Example query listing active assignments for a delivery:

  • SELECT delivery_assignment_id, delivery_id, delivery_detail_id, parent_delivery_id, type FROM wsh_delivery_assignments_v WHERE delivery_id = :p_delivery_id AND active_flag = 'Y';
  • SELECT delivery_id, delivery_detail_id FROM wsh_delivery_assignments_v WHERE active_flag = 'Y' AND type = 'S';
  • SELECT a.delivery_id, a.delivery_detail_id, a.parent_delivery_id FROM wsh_delivery_assignments_v a WHERE a.delivery_detail_id = :p_detail_id AND a.active_flag = 'Y';

For performance, queries should filter on DELIVERY_ID, DELIVERY_DETAIL_ID, or ACTIVE_FLAG, since these columns drive the most selective access paths against the underlying synonym. Where transformations beyond the built-in TYPE normalization are required, the base table WSH_DELIVERY_ASSIGNMENTS should be queried directly.