Results for “wsh_trips_interface”

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

Overview

WSH_TRIPS_INTERFACE is the open interface table for trip data within the Oracle Shipping Execution (WSH) module. It serves as the staging repository through which external systems, legacy applications, planning engines, and integration programs load trip records into Oracle E-Business Suite before validation and transfer into the base trip tables (WSH_TRIPS and its dependents). The table belongs to the WSH schema and is documented as VALID in both Oracle EBS 12.1.1 and 12.2.2. Its physical definition contains 81 columns, reflecting the breadth of attributes required to describe a trip, its carriers, routing, vehicle assignments, and transportation planning reference data.

From a Data Vault modeling perspective, the metadata's heuristic classification for this table is satellite-leaning. This reflects the fact that the primary key, TRIP_INTERFACE_ID, is a surrogate identifier sourced from a sequence rather than a natural business key, and that most non-key columns are descriptive context attributes surrounding a single trip entity. In practice, data warehouse designs may treat the interface record as a satellite attached to the trip hub or as a staging image of the eventual operational trip.

Key Information Stored

WSH_TRIPS_INTERFACE is anchored by the surrogate primary key TRIP_INTERFACE_ID, validated by the unique index WSH_TRIPS_INTERFACE_U1. This identifier is also the referenced column for downstream interface children such as WSH_TRIP_STOPS_INTERFACE. A secondary key, TRIP_ID, carries the identifier of an existing base trip when the interface row is intended to update an existing record rather than create a new one; TRIP_ID is the foreign key to WSH_TRIPS.

The most significant descriptive columns include the trip NAME, PLANNED_FLAG, and STATUS_CODE, which define identity and lifecycle position. Routing and carrier context is held in CARRIER_ID, SHIP_METHOD_CODE, ROUTE_ID, ROUTING_INSTRUCTIONS, MODE_OF_TRANSPORT, FREIGHT_TERMS_CODE, and SERVICE_LEVEL. Vehicle and load-tender detail appear in VEHICLE_ITEM_ID, VEHICLE_NUMBER, LOAD_TENDER_STATUS, LOAD_TENDER_NUMBER, and LOAD_TENDERED_TIME. Planning integration is supported by LANE_ID, ROUTE_LANE_ID, SCHEDULE_ID, TP_PLAN_NAME, and TP_TRIP_NUMBER. Interface control columns—INTERFACE_ACTION_CODE, PROCESS_FLAG, PROCESS_MODE, DELETE_FLAG, PROCESS_STATUS_FLAG, and LOCK_FLAG—govern which rows the import concurrent program selects and how each row is processed. The standard audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, REQUEST_ID, PROGRAM_ID) track provenance, while fifteen ATTRIBUTE columns provide descriptive flexibility.

Additional foreign keys link LANE_ID to FTE_LANES and RANK_ID to AS_SALES_LEAD_RANKS_B, tying interface trips to Oracle Transportation Management lane data and sales-lead ranking reference data respectively.

Common Use Cases and Queries

The dominant use case is bulk loading or migrating trip records via the Trip Interface concurrent program, which reads rows where PROCESS_FLAG is unset or flagged for execution. A typical diagnostic query retrieves pending rows and their intended action:

• SELECT trip_interface_id, name, carrier_id, status_code, interface_action_code FROM wsh_trips_interface WHERE process_flag = 'N' OR process_flag IS NULL;
• Reconciliation joins compare staged rows against base trips: SELECT i.trip_interface_id, i.trip_id, t.trip_name FROM wsh_trips_interface i, wsh_trips t WHERE i.trip_id = t.trip_id;
• Error review filters rows that failed validation by inspecting PROCESS_STATUS_FLAG and the attribute columns populated by the validation logic.
• Reporting frequently aggregates trips by carrier, lane, or mode of transport to support freight spend and utilization analysis once the interface data has been promoted to WSH_TRIPS.

Because the table is transient in nature, purging policies typically delete rows after successful processing, so cache or audit strategies should retain historical interface content separately.

Related Objects

• WSH_TRIPS — the base trip table; joined via WSH_TRIPS_INTERFACE.TRIP_ID = WSH_TRIPS.TRIP_ID.
• WSH_TRIP_STOPS_INTERFACE — child interface for trip stops; joined via TRIP_INTERFACE_ID.
• WSH_FREIGHT_COSTS_INTERFACE — freight cost interface rows referencing TRIP_INTERFACE_ID.
• FTE_TRIP_MOVES_INTERFACE — transportation planning trip move interface referencing TRIP_INTERFACE_ID.
• FTE_LANES — lane master referenced through LANE_ID.
• AS_SALES_LEAD_RANKS_B — sales lead rank reference referenced through RANK_ID.