Results for “changed_status_date”

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

Overview

FTE_SHIPMENT_STATUS_DETAILS is a Transportation Execution (FTE) module table within the Oracle E-Business Suite 12.1.1 and 12.2.2 environments. It stores the granular, line-level detail records that constitute the "Detail" element of the ShipStatus message exchanged between Oracle Transportation Management (OTM) and Oracle EBS. In practical terms, the table captures status updates for individual shipment units or delivery legs, providing a drill-down layer beneath the header-level shipment status message. Applications and interfaces that consume shipment status events—such as inbound integration processing, delivery tracking, and carrier status reconciliation—rely on this table to resolve the specific units affected by a status change.

From a data modeling perspective, the metadata’s heuristic Data Vault classification identifies this object as hub-leaning. This classification is a modeling suggestion: the table’s central surrogate identifier and its role as the target of foreign keys from dependent tables suggest it behaves conceptually like a hub, anchoring descriptive and exception data that attach to individual shipment status details.

Key Information Stored

The table contains 16 documented columns. The most significant are summarized below.

• SHIPMENT_STATUS_DETAIL_ID — The surrogate primary key, enforced by the FTE_SHIPMENT_STATUS_DETAILS_PK constraint. It uniquely identifies each detail record within a ShipStatus message.
• DELIVERY_LEG_ID — A foreign key to WSH_DELIVERY_LEGS, linking the status detail to the specific delivery leg within Shipping Execution.
• REPORT_DATE — The effective date on which the shipment status was reported by the source system.
• SHIPMENT_STATUS — The status value assigned to the shipment unit or leg at the time of the message.
• CHANGED_STATUS_DATE — The timestamp when the shipment status transitioned to its current value.
• TRANSACTION_ID and SHIP_UNIT_SEQ — Identify the transaction context and the sequence of the ship unit within the message.
• SHIP_UNIT_TOTAL — The total number of ship units associated with the detail record.
• TRACKING_ID and TRACKING_ID_TYPE — Store the carrier tracking reference and its classification (for example, PRO number or container number).
• DESCRIPTION — Free-form text describing the status detail.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN support standard EBS who-did-what tracking.

The unique index FTE_SHIPMENT_STATUS_DETAILS_U1 on SHIPMENT_STATUS_DETAIL_ID is a business-key candidate, though it mirrors the primary key in this schema.

Common Use Cases and Queries

A frequent requirement is to report shipment status history for a delivery leg. Because DELIVERY_LEG_ID joins to WSH_DELIVERY_LEGS, status detail rows can be resolved to tangible shipping entities:

SELECT d.shipment_status_detail_id, d.shipment_status, d.changed_status_date, d.tracking_id
FROM fte.fte_shipment_status_details d
WHERE d.delivery_leg_id = :leg_id
ORDER BY d.changed_status_date DESC;

Integration monitoring queries aggregate counts by status and report date to detect stalled or failed transmissions. Exception handling is another common pattern: joining to FTE_SHIPMENT_STATUS_EXCEPTIONS on DETAIL_ID isolates details that generated exceptions during processing.

Related Objects

The following objects are documented as directly related through foreign key relationships:

• WSH_DELIVERY_LEGS — Referenced by DELIVERY_LEG_ID; provides the delivery leg context for each status detail.
• FTE_MESSAGE_LOCATION — References this table via LOCATION_TO_ID, capturing location information associated with the status detail.
• FTE_SHIPMENT_STATUS_EXCEPTIONS — References this table via DETAIL_ID, holding exception events raised against individual status details.

Together, these objects form the ShipStatus message chain that connects OTM status communications to EBS shipping execution data.