Search Results wsh_trips_v




Overview
Explore in AI Chat

Overview

WSH_TRIPS_V is a reporting and integration view owned by the APPS schema in Oracle E-Business Suite, defined within the Shipping Execution (WSH) product family. It exposes trip header information from the WSH_TRIPS entity together with the decoded trip status description. The view is available in both Oracle EBS 12.1.1 and 12.2.2 and is marked VALID in the ETRM metadata for 12.2.2. Its principal role is to present trip data in a denormalized, user-friendly form: rather than forcing downstream consumers to join to the lookup table for the status meaning, the view performs that join internally and returns the translated status name as a column. This makes it suitable for operational reports, outbound interfaces, and custom concurrent programs that require trip-level detail without additional lookup resolution logic. The first two columns, DELIVERY_ID and DELIVERY_DETAIL_ID, are defined as TO_NUMBER(NULL), and ACTIVITY_CODE is defined as TO_CHAR(NULL). These placeholder (null-typed) columns preserve a stable, wide column signature that supports backward compatibility with earlier definitions of the view; they will always return NULL and should not be used as real data sources. Because the view contains no trip-line or stop-level detail, it is scoped to the trip (header) level only.

Underlying Base Objects

The documented base objects referenced by the view are WSH_TRIPS (exposed through a synonym) and WSH_LOOKUPS (a view). The primary source is WSH_TRIPS, aliased as T, which supplies the trip header attributes. WSH_LOOKUPS, aliased as WL, is joined to supply the status meaning. The join is an equijoin on two conditions: WL.LOOKUP_TYPE = 'TRIP_STATUS' and WL.LOOKUP_CODE = T.STATUS_CODE. The TRIP_STATUS lookup type is the seeded lookup that enumerates valid trip statuses, so the view effectively restricts and decodes trip status from that controlled list. The WHERE clause applies NVL(T.SHIPMENTS_TYPE_FLAG, 'O') IN ('O', 'M'), which filters the result set to outbound and miscellaneous shipment trips, defaulting the flag to 'O' when it is null. In other words, the view deliberately excludes trip records whose shipment type flags fall outside this set. Systems that query the view should be aware that it is not a complete list of all rows in WSH_TRIPS; the filter is part of the view definition and cannot be overridden by a consumer query.

Key Columns

Common Use Cases and Queries

Typical uses include trip status dashboards, load tender tracking extracts, carrier and route reporting, and integration feeds that require decoded trip statuses. A basic listing of active, planned trips would be expressed as:

  • SELECT trip_id, name, status_name, carrier_id, vehicle_number, planned_flag FROM wsh_trips_v WHERE status_code = 'OPEN';

Because the status is already decoded, reports can group and aggregate without joining WSH_LOOKUPS again:

  • SELECT status_name, COUNT(*) trip_count FROM wsh_trips_v GROUP BY status_name ORDER BY status_name;

To identify trips awaiting a carrier response during tender, a query could filter on the tender columns directly:

  • SELECT trip_id, name, load_tender_number, load_tender_status, load_tendered_time FROM wsh_trips_v WHERE load_tender_status = 'TENDERED' AND carrier_response IS NULL;

For integration extracts, selecting the trip key together with workflow and Transportation Planning identifiers supports downstream correlation:

  • SELECT trip_id, tp_trip_number, tp_plan_name, wf_item_key, last_update_date FROM wsh_trips_v WHERE tp_trip_number IS NOT NULL;

Consumers should remember the implicit SHIPMENTS_TYPE_FLAG filter, avoid the null-typed placeholder columns (DELIVERY_ID, DELIVERY_DETAIL_ID, ACTIVITY_CODE), and use TRIP_ID as the anchor when joining to trip stop or delivery detail tables for line-level reporting.