Search Results c_tms_actual_request




Overview
Explore in AI Chat

Overview

WSH_TRIP_STOPS_PVT is a private PL/SQL package in the Oracle E-Business Suite transportation execution module. It encapsulates the business logic that governs trip stops — the intermediate and terminal locations at which a trip (a planned movement of goods by a carrier) loads, unloads, or transits. In EBS 12.1.1 and 12.2.2, this package provides the programmatic foundation upon which trip planning, carrier assignment, and Oracle Transportation Management (OTM) integration are built.

The header comment in the source identifies the package as part of the "OTM R12 glog project," which introduced declarative constants describing the lifecycle of a trip stop as it relates to OTM. These constants — C_TMS_NOT_TO_BE_SENT ('NS'), C_TMS_COMPLETED ('CMP'), C_TMS_ACTUAL_IN_PROCESS ('ASP'), and C_TMS_ACTUAL_REQUEST ('ASR') — define the possible values of the tms_interface_flag at the stop level, indicating whether a stop should be transmitted to OTM, is being transmitted, has been requested, or has finished transmission.

The package is classified as PVT (private), meaning it is not intended to be called directly by external customizations. Instead, it backs the public WSH API layer and is invoked internally by other Oracle applications code. The metadata records that it is referenced by 33 other packages, confirming its role as a shared internal utility.

Key Procedures and Functions

The package exposes ten documented procedures and functions:

  • CREATE_TRIP_STOP — Creates a new trip stop record, populating the base table with the supplied stop attributes.
  • UPDATE_TRIP_STOP — Modifies an existing trip stop record with revised attribute values.
  • DELETE_TRIP_STOP — Removes a trip stop record from the repository.
  • LOCK_TRIP_STOP — Acquires a lock on a trip stop row, validating the current attribute values before allowing changes; this protects against concurrent modification.
  • LOCK_TRIP_STOP_NO_COMPARE — A lock variant that obtains the row lock without performing the attribute comparison, useful when the caller already holds current values.
  • POPULATE_RECORD — Fills the trip_stop_rec_type record structure from either the database or an incoming source, standardizing data between table handlers and calling packages.
  • GET_NAME — Returns a descriptive name associated with a trip stop, typically for display or logging.
  • GET_DISABLED_LIST — Returns the set of values or actions that are disabled for a given trip stop, supporting validation and UI behavior.
  • UPDATE_TMS_INTERFACE_FLAG — The procedure most directly associated with the user's search. It updates the tms_interface_flag on a trip stop, moving the stop through the OTM transmission states defined by the package constants (NS, ASP, ASR, CMP). This is the mechanism by which stops are marked for, or confirmed as, transmission to Oracle Transportation Management.

Tables Accessed

The package operates against the following tables, referenced through APPS synonyms:

  • WSH_TRIP_STOPS — The primary base table holding stop-level data (locations, sequence, planned and actual dates, weights, volumes, seals, and descriptive flexfield attributes).
  • WSH_TRIP_STOPS_S — The corresponding sequence table used to generate unique stop identifiers.
  • WSH_TRIPS — The parent trip header, consulted for trip-level context and validation.
  • WSH_LOCATIONS — Supplies the location details for each stop.
  • WSH_DELIVERY_LEGS — Links deliveries to the legs of a trip, supporting stop-to-delivery association.
  • WSH_FREIGHT_COSTS — Holds freight cost information associated with trip movements.
  • PLITBLM — A generic PL/SQL table used for in-memory list handling within the package.

Usage Notes

WSH_TRIP_STOPS_PVT is a private package and should not be invoked directly from custom code. It is called internally by Oracle Transportation Management integration routines, trip planning forms, and concurrent programs that create, amend, or transmit trip stops. The tms_interface_flag updates performed by UPDATE_TMS_INTERFACE_FLAG are driven by the OTM integration flows and by UI actions that change a stop's transmission status.

Because the package is referenced by 33 other packages, changes to its behavior can ripple widely. Customizations requiring trip stop manipulation should use the supported public WSH APIs rather than this private layer. Developers reviewing OTM interface issues — particularly where stops remain in 'NS' or are stuck in 'ASP'/'ASR' — commonly trace through UPDATE_TMS_INTERFACE_FLAG and the associated constants to diagnose why a stop was or was not sent to Oracle Transportation Management.