Search Results pickup_stop




Overview
Explore in AI Chat

Overview

The APPS.WSH_DELIVERY_TRIPS_V view is a shipping execution reporting object that joins delivery legs to their associated trip definitions. Its primary purpose is to expose, for each delivery, the trip record that carries the pickup and dropoff stops belonging to that delivery. The view therefore provides a consolidated picture of trip-level and stop-level shipping data across the outbound delivery lifecycle.

The view is defined with a filter restricting results to outbound shipment directions. Specifically, the underlying WSH_NEW_DELIVERIES record must satisfy NVL(shipment_direction, 'O') IN ('O', 'IO'), so records with a null direction (treated as outbound) and explicitly outbound or inbound-outbound combinations are returned. Inbound-only deliveries are excluded.

A notable feature of the view is a derived status column. Rather than surfacing the raw stop status, the view computes a combined delivery-level status by decoding the pickup stop status code and, where the pickup status is not one of the recognized values, falling back to the dropoff stop status code. This makes the view useful for status-driven reporting where a single consolidated code is required per delivery leg.

The TO_NUMBER(NULL) expression in the second position of the select list is a placeholder column that carries no data; consumers should not rely on it for any functional purpose.

Underlying Base Objects

The view is owned by APPS and is defined over four documented objects:

  • WSH_DELIVERY_LEGS (synonym) — supplies the delivery-to-stop linkage through pick_up_stop_id and drop_off_stop_id, and the delivery_id.
  • WSH_NEW_DELIVERIES (synonym) — supplies the delivery master record and the shipment_direction used in the outbound filter.
  • WSH_TRIPS_V (view) — supplies all trip-level attributes, aliased as t.
  • WSH_TRIP_STOPS (synonym) — instantiated twice, as pickup_stop and dropoff_stop, to resolve the status of each end of the delivery leg.

The join path is: pickup_stop.stop_id = dl.pick_up_stop_id, dropoff_stop.stop_id = dl.drop_off_stop_id, pickup_stop.trip_id = t.trip_id, and wnd.delivery_id = dl.delivery_id. Because a delivery leg is matched to its pickup and dropoff stops and those stops are tied back to the same trip, the view returns one row per qualifying delivery leg and trip combination.

Key Columns

Common Use Cases and Queries

This view is typically used to report on deliveries in the context of their assigned trips, to trace carrier and routing attributes, and to obtain a consolidated leg status. A representative query follows:

  • Retrieve delivery and trip combinations for a delivery: SELECT DELIVERY_ID, TRIP_ID, NAME, STATUS_CODE FROM APPS.WSH_DELIVERY_TRIPS_V WHERE DELIVERY_ID = :p_delivery_id;
  • List deliveries by carrier and service level: SELECT DELIVERY_ID, TRIP_ID, CARRIER_ID, SERVICE_LEVEL FROM APPS.WSH_DELIVERY_TRIPS_V WHERE CARRIER_ID = :p_carrier_id;
  • Filter on the derived status expression by wrapping the view with an outer query that returns the status column positionally, or by reproducing the decode logic against WSH_TRIP_STOPS.

Because the view joins across stop, trip, and delivery structures, queries should generally constrain on DELIVERY_ID, TRIP_ID, or carrier attributes to limit the returned row set.