Search Results delivery_wf_process

Overview

WSH_TRIP_DELIVERIES_V is a Shipping Execution (WSH) reporting and integration view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It presents a consolidated, denormalized picture of the relationship between trips and the deliveries assigned to them, joining delivery header information from the deliveries entity with trip and trip-stop context. Rather than forcing developers and report authors to navigate the normalized structures of trips, trip stops, delivery legs, and delivery headers separately, the view exposes a single rowset in which each delivery appears together with its parent trip identifier and a derived trip-level status.

The view is frequently referenced in transportation and outbound logistics reporting, carrier and manifest integrations, and workflow-driven status derivations. Although the object is a view and therefore read-only, it is commonly used as a query source for concurrent programs, BI Publisher data templates, and custom PL/SQL that must determine the current status of a delivery within a trip. The search term "delivery_wf_process" suggests the view is also used in contexts where downstream workflow logic derives delivery or trip status for notification and acknowledgment processing.

Underlying Base Objects

The documented ETRM 12.2.2 metadata lists four referenced base objects: WSH_DELIVERY_LEGS (SYNONYM), WSH_NEW_DELIVERIES_V (VIEW), WSH_TRIPS (SYNONYM), and WSH_TRIP_STOPS (SYNONYM). The view text confirms these relationships. WSH_TRIPS supplies the TRIP_ID at the head of the SELECT list. WSH_NEW_DELIVERIES_V (aliased D) supplies the majority of columns, since it is the delivery-level view that already exposes delivery name, status, addresses, weights, volumetric measures, and the extensive DFF attribute columns (D.ATTRIBUTE1–15, D.TP_ATTRIBUTE1–15, and D.GLOBAL_ATTRIBUTE1–15). WSH_TRIP_STOPS is referenced twice, aliased as PICKUP_STOP and DROPOFF_STOP, to resolve pickup and dropoff stop status codes. WSH_DELIVERY_LEGS provides the link between deliveries and stops that permits the stop-status DECODE to be evaluated.

Because the view depends on WSH_NEW_DELIVERIES_V, it inherits that view's own dependencies, so any performance tuning or invalidation analysis must trace through both layers. The two TO_NUMBER(NULL) expressions in the SELECT list are intentional placeholders preserving column position and datatype compatibility with earlier definitions or with sibling trip-delivery structures.

Key Columns

• TRIP_ID — identifier of the parent trip from WSH_TRIPS; the primary grouping key for trip-level aggregation.
• ROW_ID / DELIVERY_ID / NAME — surrogate key, internal delivery identifier, and user-facing delivery name from the delivery header.
• STATUS_CODE / STATUS_NAME — the delivery's own status, distinct from the derived trip-stop status.
• Derived status expression — a nested DECODE over PICKUP_STOP.STATUS_CODE and DROPOFF_STOP.STATUS_CODE returning values such as RE, LO, CL, OB, UL, and CO, representing combined pickup/dropoff progress for the delivery within the trip.
• PLANNED_FLAG, ACCEPTANCE_FLAG, ACCEPTED_BY, ACCEPTED_DATE, CONFIRMED_BY, CONFIRM_DATE, ASN_DATE_SENT, ASN_STATUS_CODE, ASN_SEQ_NUMBER — shipment lifecycle and advance ship notice state indicators.
• INITIAL_PICKUP_DATE, INITIAL_PICKUP_LOCATION_ID, ULTIMATE_DROPOFF_DATE, ULTIMATE_DROPOFF_LOCATION_ID, INTMED_SHIP_TO_LOCATION_ID, POOLED_SHIP_TO_LOCATION_ID — route and location context.
• CUSTOMER_ID, FREIGHT_TERMS_CODE, FOB_CODE, FOB_LOCATION_ID, WAYBILL, SHIP_METHOD_CODE, CARRIER_ID, DOCK_CODE, DELIVERY_TYPE — commercial, carrier, and dock attributes.
• GROSS_WEIGHT, TARE_WEIGHT, NET_WEIGHT, WEIGHT_UOM_CODE, VOLUME, VOLUME_UOM_CODE — weight and volume measures used in load planning and manifest generation.
• ORGANIZATION_ID, LOADING_SEQUENCE, LOADING_ORDER_FLAG, CONSOLIDATION_FLAG, WV_FROZEN_FLAG, ADDITIONAL_SHIPMENT_INFO — warehouse, sequencing, and consolidation controls.
• ATTRIBUTE1–15, TP_ATTRIBUTE1–15, GLOBAL_ATTRIBUTE1–15 and their categories — descriptive flexfield segments for delivery and transportation extensions.

Common Use Cases and Queries

The most common pattern is a trip-centric listing of deliveries with their derived stop status, suitable for dispatch reports and carrier tendering:

• Deliveries by trip: SELECT TRIP_ID, DELIVERY_ID, NAME, STATUS_CODE, ORGANIZATION_ID FROM APPS.WSH_TRIP_DELIVERIES_V WHERE TRIP_ID = :p_trip_id ORDER BY LOADING_SEQUENCE;
• Unconfirmed or unacknowledged deliveries: filter on ACCEPTANCE_FLAG, ACKNOWLEDGED_BY, or CONFIRM_DATE being null to drive follow-up workflow and notification processes.
• ASN monitoring: select ASN_STATUS_CODE and ASN_DATE_SENT to identify deliveries whose advance ship notices have not been transmitted.
• Weight and volume rollups: aggregate GROSS_WEIGHT, NET_WEIGHT, and VOLUME by TRIP_ID for load plan verification.
• Status derivation joins: because the view already computes a combined pickup/dropoff status, it is convenient for feeding workflow status transitions referenced by processes such as delivery_wf_process, avoiding reimplementation of the DECODE logic.

Queries should filter on indexed keys such as TRIP_ID or DELIVERY_ID where possible, since the view composition over multiple synonyms and a nested view can otherwise produce costly full scans on large delivery volumes.