Results for “drop_off_stop_id”

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

Overview

WSH_DELIVERY_LEGS is a core table in the Oracle E-Business Suite Shipping Execution (WSH) module, residing in the WSH schema. It represents the individual legs of a delivery — the discrete movement segments between an origin and a destination stop that together form the complete transportation plan for a shipment. Where WSH_NEW_DELIVERIES acts as the parent header for a delivery, WSH_DELIVERY_LEGS breaks that delivery into one or more sequential transport legs, each tied to a specific pick-up stop and drop-off stop on a trip. This structure supports multi-stop, multi-leg, and consolidated freight scenarios common in outbound logistics, freight tendering, and carrier settlement.

The object is documented as VALID in ETRM for both 12.1.1 and 12.2.2, with a physical schema of 68 columns owned by the WSH schema. Its primary key is WSH_DELIVERY_LEGS_PK, defined on DELIVERY_LEG_ID, and a unique index WSH_DELIVERY_LEGS_U1 also covers DELIVERY_LEG_ID, confirming it as the business-key candidate. The heuristic Data Vault classification places this table as satellite-leaning, meaning it captures descriptive, transactional attributes that depend on the delivery/trip hubs rather than acting as an independent hub or a pure junction link. This is consistent with its heavy attribute payload (weights, volumes, references, signatures, tracking status).

Key Information Stored

The table stores both surrogate identity and a rich set of operational attributes. The most significant columns include:

• DELIVERY_LEG_ID — the surrogate primary key uniquely identifying each leg.
• DELIVERY_ID — foreign key to WSH_NEW_DELIVERIES, linking the leg to its parent delivery.
• SEQUENCE_NUMBER — ordering of legs within a delivery.
• PICK_UP_STOP_ID and DROP_OFF_STOP_ID — foreign keys to WSH_TRIP_STOPS defining the origin and destination of the leg.
• ORIGIN_STOP_ID and DESTINATION_STOP_ID — additional stop references used in routing.
• GROSS_WEIGHT, NET_WEIGHT, WEIGHT_UOM_CODE, VOLUME, VOLUME_UOM_CODE — physical freight measurements.
• FTE_TRIP_ID — foreign key to FTE_TRIPS, tying the leg to a transportation trip.
• STATUS_CODE and LOAD_TENDER_STATUS — lifecycle and tender state of the leg.
• CARRIER_EST_DEPARTURE_DATE, CARRIER_EST_ARRIVAL_DATE, ACTUAL_ARRIVAL_DATE, ACTUAL_DEPARTURE_DATE — scheduling and actual milestone dates.
• POD_FLAG, POD_BY, POD_DATE, EXPECTED_POD_DATE — proof-of-delivery tracking.
• BOOKING_NUMBER, SERVICE_CONTRACT, PORT_OF_LOADING, PORT_OF_DISCHARGE — freight booking and ocean/air routing references.
• DELIVERED_QUANTITY, LOADED_QUANTITY, RECEIVED_QUANTITY — quantity tracking across the leg lifecycle.
• PARENT_DELIVERY_LEG_ID — self-referencing hierarchy for nested legs.

Standard EBS audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, PROGRAM_ID, REQUEST_ID) are also present.

Common Use Cases and Queries

Typical reporting and integration scenarios include tracking leg milestones, reconciling freight costs, and exporting leg data to carriers. A representative query retrieving legs for a delivery, joined to the shipment stops, is:

• SELECT l.delivery_leg_id, l.delivery_id, l.sequence_number, l.status_code, ps.location_id pickup, ds.location_id dropoff FROM wsh_delivery_legs l JOIN wsh_trip_stops ps ON l.pick_up_stop_id = ps.stop_id JOIN wsh_trip_stops ds ON l.drop_off_stop_id = ds.stop_id WHERE l.delivery_id = :p_delivery_id ORDER BY l.sequence_number; — a typical enquiry listing each leg with its pickup and drop-off stops.
• Freight cost reconciliation joining WSH_FREIGHT_COSTS on DELIVERY_LEG_ID.
• Proof-of-delivery dashboards filtering by POD_FLAG and POD_DATE.
• Interface processing against WSH_DEL_LEGS_INTERFACE for inbound leg imports.
• Trip-level analysis joining FTE_TRIPS on FTE_TRIP_ID.

Related Objects

The table participates in a well-defined foreign-key network. It references WSH_NEW_DELIVERIES via DELIVERY_ID, WSH_TRIP_STOPS twice via PICK_UP_STOP_ID and DROP_OFF_STOP_ID, and FTE_TRIPS via FTE_TRIP_ID. Several child tables reference WSH_DELIVERY_LEGS through DELIVERY_LEG_ID, including WSH_DEL_LEGS_INTERFACE, WSH_FREIGHT_COSTS, WSH_FREIGHT_COSTS_INTERFACE, WSH_DELIVERY_LEG_ACTIVITIES, FTE_SHIPMENT_STATUS_HEADERS, FTE_SHIPMENT_STATUS_DETAILS, and FTE_FREIGHT_COSTS_TEMP. These relationships make WSH_DELIVERY_LEGS the central operational anchor for leg-level freight, tracking, and cost data within Shipping Execution.