Search Results tl_trip_stop_input_rec_type

Overview

FTE_TL_CACHE is a PL/SQL caching package in the Oracle E-Business Suite Transportation and Logistics (FTE) module. Its principal role is to build and manage an in-memory cache of trip, trip leg, stop, and shipment data so that downstream transportation planning, rating, and comparison routines can operate without repeatedly querying the base FTE and Oracle Shipping (WSH) tables. The package is declared with AUTHID CURRENT_USER, meaning its SQL executes under the privileges of the calling schema rather than the package owner. It maintains a set of placeholder ("FAKE") identifier constants for dummy delivery legs, trips, stops, and locations, which are used to satisfy referential integrity during intermediate cache-building steps when real identifiers are not yet available.

The cache structures themselves are defined as PL/SQL record and table types within the package specification. TL_trip_data_input_rec_type carries the full attribute set of a planned trip, including lane, schedule, service type, carrier, mode of transport, vehicle type, price list, loaded and unloaded distance, pallet and container counts, time, stop counts, total and direct distance, distance method, weight and volume, continuous move and deadhead flags, and planned departure and arrival dates. TL_TRIP_STOP_INPUT_REC_TYPE carries per-stop detail such as layovers, distance and time to the next stop, pickup and dropoff weight, volume, pallets, containers, loading protocol, region, zone, stop type, and physical stop and location identifiers used for dummy-stop resolution. Collectively these structures allow the transportation engine to assemble an entire trip model in memory before evaluation.

Key Procedures and Functions

• TL_BUILD_CACHE_FOR_MOVE — Populates the cache for a single trip move.
• TL_BUILD_CACHE_FOR_TRIP — Builds the full cache for a trip, including its stops and legs.
• TL_BUILD_CACHE_FOR_DELIVERY — Builds cache data from a delivery, bridging WSH delivery details into the transportation cache.
• TL_BUILD_CACHE_FOR_OM — Builds cache content originating from Order Management data.
• TL_BUILD_CACHE_FOR_TRP_COMPARE — Builds the cache used for transportation plan comparison.
• TL_BUILD_CACHE_FOR_LCS — Builds the cache supporting least-cost / lane cost sourcing evaluation.
• TL_BUILD_CACHE_FOR_ESTIMATE — Builds cache data for rate and cost estimation.
• TL_BUILD_CACHE_FOR_DLV_COMPARE — Builds the cache used to compare delivery alternatives.
• DELETE_CACHE — Clears the in-memory cache structures.
• DISPLAY_CACHE — Emits the current cache contents for debugging and diagnostics.
• GET_APPROXIMATE_DISTANCE_TIME — Returns an estimated distance and time between locations when exact routing data is unavailable.
• GET_VEHICLE_TYPE — Retrieves the vehicle type associated with the current transportation context.
• FPA_GET_TRIP_INFO — Returns trip information for Freight Planning and Analysis use.
• CALCULATE_DIMENSIONAL_WEIGHT — Computes dimensional (volumetric) weight for rating purposes.

Tables Accessed

The package reads reference and transactional transportation data through APPS synonyms. Lane and rate data come from FTE_LANES, FTE_LANE_RATE_CHARTS, and FTE_SCHEDULES. Trip structure is sourced from FTE_TRIP_MOVES. Shipping data is drawn from WSH_DELIVERY_DETAILS, WSH_DELIVERY_ASSIGNMENTS, WSH_DELIVERY_LEGS, WSH_CARRIERS, and WSH_CARRIER_SERVICES. Customer and location context is resolved through HZ_CUST_ACCT_SITES_ALL, HZ_CUST_SITE_USES_ALL, HZ_PARTY_SITES, and PO_LOCATION_ASSOCIATIONS_ALL. Item attributes are read from MTL_SYSTEM_ITEMS_B, and DBMS_UTILITY is referenced for utility operations such as formatted error stack output. These sources supply the attributes that populate the trip, stop, and leg cache records.

Usage Notes

FTE_TL_CACHE is an internal support package rather than an end-user API; it has no concurrent program or form of its own and is invoked programmatically. It is referenced by four other packages, which call the appropriate TL_BUILD_CACHE_FOR_* routine before performing planning, rating, or comparison logic, then call DELETE_CACHE to release the cached data. Customizations that extend transportation planning should follow the same pattern, and must be aware that the FAKE identifier constants and dummy stop and location resolution logic exist to satisfy structural requirements during cache assembly. Because the package uses AUTHID CURRENT_USER, calling sessions require direct grants on the underlying tables rather than relying on the APPS owner's privileges. The DISPLAY_CACHE and DBMS_UTILITY references make the package useful for diagnostics when investigating discrepancies between planned and actual trip data.