Search Results fte_load_history

Overview

The FTE_LOAD_HISTORY table is a core data object within the Oracle E-Business Suite Transportation Execution (FTE) module, specifically designed to provide an audit trail for shipment lifecycle management. Its primary role is to chronologically store a record of all significant actions or events performed on a shipment, also referred to as a load. This historical log is critical for operational visibility, troubleshooting, compliance, and analyzing the progression of shipments from initial planning through to final delivery. By maintaining this history, the table enables users to reconstruct the sequence of events for any given load, understanding who performed which action and when.

Key Information Stored

While the provided ETRM metadata does not list all columns, the documented primary and foreign keys define its essential structure. The composite primary key, comprised of LOAD_ID, ACTION_DATE, and ACTION, ensures a unique record for each discrete action taken on a shipment at a specific point in time. The LOAD_ID column is the fundamental link to the shipment itself, stored in the FTE_LOAD_HEADERS table. The ACTION column captures the type of event logged, such as "Planned," "Tendered," "Dispatched," "Delivered," or "Cancelled." The ACTION_DATE records the timestamp of the event. A notable foreign key column is LOAD_SCHEDULE_ID, which links the historical action to a specific schedule within the FTE_LOAD_SCHEDULES table, allowing history to be tracked against both the master load and its individual scheduled trips or legs.

Common Use Cases and Queries

The primary use case is auditing and reporting on shipment activity. Logistics managers frequently query this table to generate reports on shipment status transitions, identify bottlenecks by analyzing time gaps between actions, and verify process compliance. A common query pattern involves joining to FTE_LOAD_HEADERS to retrieve shipment details alongside its history. For example, to find the complete audit trail for a specific load, one might use: SELECT lh.load_number, lh.status, lh.* FROM fte_load_history lh JOIN fte_load_headers h ON lh.load_id = h.load_id WHERE h.load_number = '<LOAD_NUMBER>' ORDER BY lh.action_date;. Another typical report identifies all loads that were tendered to a carrier on a specific date but not yet dispatched, aiding in carrier performance monitoring.

Related Objects

The FTE_LOAD_HISTORY table is centrally connected to two primary transactional tables in the FTE schema, as defined by its documented foreign key relationships:

• FTE_LOAD_HEADERS: This is the master shipment table. The relationship is defined as FTE_LOAD_HISTORY.LOAD_ID = FTE_LOAD_HEADERS.LOAD_ID. Every historical record must pertain to a valid load in this table.
• FTE_LOAD_SCHEDULES: This table manages scheduled trips or legs for a shipment. The relationship is defined as FTE_LOAD_HISTORY.LOAD_SCHEDULE_ID = FTE_LOAD_SCHEDULES.LOAD_SCHEDULE_ID. This allows actions to be recorded against specific schedule lines within a multi-leg shipment, providing granular historical tracking.

These relationships are foundational for any query that combines shipment history with its core transactional data or schedule details.