Shipment Management Data Structure

A shipment in OTM consists of one or more Equipment records (containers or trailers), Shipment Ship Units loaded into each equipment, and Shipment Ship Unit Lines that link back to the original Order Release lines. Understanding this structure is essential for troubleshooting planning results, writing integration queries, and working with carrier tracking events.

Shipment Structure:

When OTM plans an Order Release to a shipment, it creates Shipment Ship Units and assigns them to equipment. If a Bulk Plan splits an order across two containers — say 60 units in one and 40 in another — that split is recorded at the Shipment Ship Unit Line level. This line is the critical link between the shipment and the originating order: it ties back to a specific item, Order Release Line, Order Release, and Order Base.

Shipment tables:

Shipment Ship Unit tables:

Shipment Stop tables:

Equipment:

Equipment represents the physical container used to move freight — a 53 FT trailer, a 40 FT ocean container, etc. The same container can move across multiple legs and transport modes (intermodal). For example, a sealed container may travel from a supplier in China to a Chinese port by truck, then cross to a US port by vessel, then move to a warehouse by truck. Note that trucks have two parts: the Power Unit (engine) and the Trailer (that holds the container).

Itinerary:

An Itinerary defines the scope of shipment planning — which origin/destination regions are covered, what equipment types are valid for each region pair, what transport modes apply, and how many legs exist between source and destination.

For example, an import itinerary from China suppliers to a US warehouse might have three legs:

Identifying the itinerary is the first step OTM Bulk Plan performs. Rate Offerings and Rate Records must also be defined for each leg’s source/destination region, transport mode, and equipment combination.

Itinerary tables:

Shipment Tracking Events:

Once goods are picked up and the shipment is in transit, the carrier can send tracking events to OTM specifying the event location and status code. These are stored in:

Sample query — tracking events for a shipment:

SELECT ies.i_transaction_no,
       shp.shipment_gid,
       ssh.event_location_gid,
       ssh.shipment_stop_num,
       TO_CHAR(ies.eventdate, 'MM/DD/YYYY HH24:MI:SS') AS event_date,
       ies.status_code_gid,
       bsc.description
FROM   shipment shp,
       ss_status_history ssh,
       ie_shipmentstatus ies,
       bs_status_code bsc
WHERE  shp.shipment_gid = 'DOMAIN.554680'
AND    shp.shipment_gid = ssh.shipment_gid
AND    ies.i_transaction_no = ssh.i_transaction_no
AND    ies.status_code_gid = bsc.bs_status_code_gid
ORDER BY ies.eventdate

Shipment Tender:

Sample queries:

-- Order Release and its ship units
SELECT * FROM order_release WHERE order_release_gid = 'DOMAIN.TST3PL1029001-001';
SELECT * FROM order_release_line WHERE order_release_gid = 'DOMAIN.TST3PL1029001-001';
SELECT * FROM ship_unit WHERE order_release_gid = 'DOMAIN.TST3PL1029001-001';
SELECT * FROM ship_unit_line
WHERE  order_release_line_gid IN (
         SELECT order_release_line_gid FROM order_release_line
         WHERE  order_release_gid = 'DOMAIN.TST3PL1029001-001');

-- Shipment and its equipment, ship units, and stops
SELECT * FROM shipment WHERE shipment_gid = 'DOMAIN.267199';
SELECT * FROM shipment_s_equipment_join WHERE shipment_gid = 'DOMAIN.267199';
SELECT * FROM s_equipment_s_ship_unit_join
WHERE  s_equipment_gid IN (
         SELECT s_equipment_gid FROM shipment_s_equipment_join
         WHERE  shipment_gid = 'DOMAIN.267199');
SELECT * FROM shipment_stop_d WHERE shipment_gid = 'DOMAIN.267199';
SELECT * FROM s_ship_unit
WHERE  s_ship_unit_gid IN (
         SELECT s_ship_unit_gid FROM shipment_stop_d
         WHERE  shipment_gid = 'DOMAIN.267199');
SELECT * FROM s_ship_unit_line
WHERE  s_ship_unit_gid IN (
         SELECT s_ship_unit_gid FROM shipment_stop_d
         WHERE  shipment_gid = 'DOMAIN.267199');

What’s Next:

The next topic covers the Contract and Rate Management Data Structure — how OTM stores carrier contracts, rate records, and the tables used to look up freight costs during planning.

Next Topic: Contract & Rate Management Data Structure