Search Results wsh_trip_stops_v




Overview
Explore in AI Chat

Overview

WSH_TRIP_STOPS_V is a read-only reporting view owned by the APPS schema in Oracle E-Business Suite, belonging to the Shipping Execution (WSH) product family. It presents trip stop information — the planned sequence of physical stops that make up a trip or carrier manifest — in a denormalized, human-readable form suitable for reporting, inquiry, and integration. Rather than requiring report authors and integrators to join WSH_TRIP_STOPS to WSH_TRIPS and WSH_LOOKUPS manually, the view encapsulates that join logic and exposes trip name and status meaning alongside the raw stop attributes.

The view is defined with a filter restricting output to the 'O' and 'M' shipment type flags, implemented as NVL(S.SHIPMENTS_TYPE_FLAG,'O') IN ('O','M'). This means outbound and miscellaneous shipment stops are returned, while other shipment type classifications are excluded. The view also exposes several "placeholder" columns populated with TO_NUMBER(NULL) or TO_CHAR(NULL), retained for legacy compatibility with prior OTM/transportation forms that expected those column positions. Report authors should not rely on those columns for data.

Underlying Base Objects

The view is defined over three referenced objects documented in ETRM for 12.2.2:

  • WSH_TRIP_STOPS (SYNONYM) — the primary base table supplying the majority of columns, aliased S. It holds one row per stop associated with a trip, including stop location, sequence number, status code, planned and actual arrival/departure datetimes, weight and volume departure metrics, and the descriptive flexfield and tracking attributes.
  • WSH_TRIPS (SYNONYM) — aliased T, joined on T.TRIP_ID = S.TRIP_ID. This supplies the trip NAME column exposed as TRIP_NAME in the view.
  • WSH_LOOKUPS (VIEW) — aliased WL, used to translate the stop STATUS_CODE into a readable status meaning via LOOKUP_TYPE = 'TRIP_STOP_STATUS' with WL.LOOKUP_CODE = S.STATUS_CODE. Because WSH_LOOKUPS is itself a view over the common lookup infrastructure, the join is effectively a lookup-driven decode of the stop status.

All three objects are resolved through APPS synonyms, so the view may be queried directly against APPS without schema qualification concerns in most implementations.

Key Columns

Common Use Cases and Queries

Typical reporting scenarios include trip stop manifests, plan-versus-actual variance reporting, and stop duration analysis. A representative query lists the stops on a given trip in sequence:

  • SELECT trip_name, stop_sequence_number, stop_location_id, status_na, planned_arrival_date, actual_arrival_date FROM apps.wsh_trip_stops_v WHERE trip_id = :trip_id ORDER BY stop_sequence_number;
  • SELECT status_na, COUNT(*) FROM apps.wsh_trip_stops_v GROUP BY status_na; — used for status distribution and carrier performance dashboards.
  • SELECT trip_name, stop_sequence_number, (actual_arrival_date - planned_arrival_date) slip FROM apps.wsh_trip_stops_v WHERE actual_arrival_date IS NOT NULL; — used for schedule adherence analysis.

Because the view already resolves the lookup and trip name, it is the preferred access path for custom reports, OBIEE extracts, and interface programs that would otherwise need to re-implement the joins to WSH_TRIP_STOPS, WSH_TRIPS, and WSH_LOOKUPS.