Results for “wsh_trip_stops_interface_u1”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

WSH_TRIP_STOPS_INTERFACE is a Shipping Execution (WSH) interface table that serves as the inbound staging area for trip stop information. It holds operational data for the scheduled and actual stops along a trip — arrival and departure dates, location details, weight and volume readings, carrier estimates, and the associated shipping transaction references — before that data is validated and transferred into the permanent trip stop tables. In Oracle EBS 12.1.1 and 12.2.2, records in this table are typically populated by external transportation management, integration, or carrier-facing loaders that push planned trip and stop information into Shipping Execution for processing.

From a modeling perspective, the ETRM metadata classifies this table as "hub-leaning" based on its foreign key structure. A Data Vault design would suggest treating STOP_INTERFACE_ID as the hub business key, with the referenced trip, stop, and location identifiers acting as link relationships. The table is anchored by the primary key WSH_TRIP_STOPS_INTERFACE_PK on STOP_INTERFACE_ID, and it is also cross-referenced by unique index WSH_TRIP_STOPS_INTERFACE_U1 on the same column.

Key Information Stored

The table is documented as containing 87 columns. The most consequential include:

• STOP_INTERFACE_ID — surrogate primary key; the unique identifier for each interface stop record.
• TRIP_INTERFACE_ID — foreign key to WSH_TRIPS_INTERFACE, linking the stop to its parent trip interface record.
• TRIP_ID, STOP_ID — references to the permanent WSH_TRIPS and WSH_TRIP_STOPS records once processed.
• STOP_LOCATION_ID, WSH_LOCATION_ID, PHYSICAL_LOCATION_ID — identifiers for the physical and logical locations of the stop.
• STOP_SEQUENCE_NUMBER — the ordering of the stop within the trip.
• PLANNED_ARRIVAL_DATE / PLANNED_DEPARTURE_DATE and ACTUAL_ARRIVAL_DATE / ACTUAL_DEPARTURE_DATE — scheduled versus executed timestamps.
• STATUS_CODE, PROCESS_FLAG, PROCESS_MODE, INTERFACE_ACTION_CODE, DELETE_FLAG — lifecycle and processing control flags that drive the interface program.
• DEPARTURE_GROSS_WEIGHT, DEPARTURE_NET_WEIGHT, WEIGHT_UOM_CODE, DEPARTURE_VOLUME, VOLUME_UOM_CODE — load measurement data at departure.
• CARRIER_EST_DEPARTURE_DATE, CARRIER_EST_ARRIVAL_DATE — carrier-supplied time estimates.
• LOADING_START_DATETIME, LOADING_END_DATETIME, UNLOADING_START_DATETIME, UNLOADING_END_DATETIME — dock activity windows.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY — standard audit columns.

Common Use Cases and Queries

Typical use cases include monitoring pending interface records before the trip stop processor runs, reconciling successful versus failed loads, and diagnosing processing errors via the process flags.

SELECT stop_interface_id, trip_interface_id, stop_location_id,
       status_code, process_flag, process_mode
FROM   wsh_trip_stops_interface
WHERE  process_flag IS NULL
   OR  process_flag = 'N';

A second common pattern joins the interface table to WSH_TRIPS_INTERFACE to audit trips awaiting stop processing:

SELECT ti.trip_interface_id, ts.stop_interface_id, ts.stop_sequence_number
FROM   wsh_trips_interface ti, wsh_trip_stops_interface ts
WHERE  ti.trip_interface_id = ts.trip_interface_id
AND    ts.interface_action_code = 'CREATE';

Reporting over planned versus actual arrival dates supports on-time performance analysis, while weight and volume columns feed capacity and load utilization reports. Interface action codes distinguish insert, update, and delete operations for reconciliation.

Related Objects

• WSH_TRIPS_INTERFACE — parent trip interface; joined on TRIP_INTERFACE_ID.
• WSH_TRIP_STOPS — target permanent stop table; referenced via STOP_ID.
• WSH_TRIPS — target trip table; referenced via TRIP_ID.
• WSH_LOCATIONS — location master; joined on WSH_LOCATION_ID.
• WSH_DEL_LEGS_INTERFACE — references this table via PICK_UP_STOP_INTERFACE_ID and DROP_OFF_STOP_INTERFACE_ID.
• WSH_FREIGHT_COSTS_INTERFACE — references this table via STOP_INTERFACE_ID.