Search Results wsh_delivery_details

Overview

WSH_DELIVERY_DETAILS is the core transactional table of Oracle Shipping Execution (WSH) in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the individual delivery lines that make up a shipment: each row represents a single shippable line, carrying the item, quantities, sourcing document references, shipping attributes, and container/LPN context required to plan, pick, pack, ship, and confirm a delivery. The table is owned by the WSH schema and, in the documented 12.2.2 physical schema, contains 210 columns with a single documented unique index, WSH_DELIVERY_DETAILS_U1 (DELIVERY_DETAIL_ID), alongside the primary key constraint WSH_DELIVERY_DETAILS_PK.

The table functions as the operational intersection between order capture (OE), inventory (INV), receiving (RCV), and shipping execution. Rows originate from order lines, returns, transfer orders, and internal requisitions, and are progressively updated as the delivery moves through pick release, ship confirm, and inventory interface. The metadata’s heuristic Data Vault classification is hub-leaning, meaning DELIVERY_DETAIL_ID behaves primarily as a durable business entity key; in a Data Vault model this would typically be represented as a hub with associated satellites for the mutable quantity, status, and container attributes. This classification is a modeling suggestion, not a physical constraint in EBS.

Key Information Stored

The surrogate primary key is DELIVERY_DETAIL_ID, which uniquely identifies each delivery line and is the column referenced by nearly every dependent WSH, WMS, and India localization (JAI) table. There is no separate documented business-key unique index beyond this identifier, so external document references act as natural business keys rather than enforced uniqueness.

• SOURCE_CODE, SOURCE_HEADER_ID, SOURCE_LINE_ID — identify the originating document (order, return, transfer) and its header and line. SOURCE_HEADER_NUMBER and SOURCE_LINE_NUMBER expose the user-visible document numbers.
• INVENTORY_ITEM_ID, ITEM_DESCRIPTION, REVISION, LOT_NUMBER, SUBLOT_NUMBER, SERIAL_NUMBER — the item and its lot/serial context for the delivery line.
• REQUESTED_QUANTITY, SHIPPED_QUANTITY, DELIVERED_QUANTITY, PICKED_QUANTITY, CANCELLED_QUANTITY (with their *_UOM and *_2 dual-unit counterparts) — the quantity lifecycle from request through ship confirm.
• SHIP_FROM_LOCATION_ID, ORGANIZATION_ID, SHIP_TO_LOCATION_ID, DELIVER_TO_LOCATION_ID — the ship-from and ship-to context, including intermediate ship-to.
• PICKABLE_FLAG and RELEASED_STATUS — control whether the line participates in pick release and where it stands in the shipping flow.
• DELIVERY_ID (via WSH_DELIVERY_ASSIGNMENTS), LPN_ID, LPN_CONTENT_ID, CONTAINER_FLAG — link the detail line to a delivery and to license plate number (LPN) or container structures.
• SPLIT_FROM_DELIVERY_DETAIL_ID — self-referencing column recording line splits.
• OE_INTERFACED_FLAG, INV_INTERFACED_FLAG, MVT_STAT_STATUS, MOVEMENT_ID — interface status flags and the movement statistics link.

Common Use Cases and Queries

Typical reporting and troubleshooting scenarios include tracing which order lines belong to a given delivery, checking pick/ship progress, and reconciling ship-confirm quantities against inventory.

To list all delivery lines for a shipment and join to the delivery assignment:

• SELECT d.delivery_detail_id, d.source_line_number, d.inventory_item_id, d.requested_quantity, d.shipped_quantity, d.released_status FROM wsh_delivery_details d WHERE d.source_header_id = :order_header_id;
• SELECT da.delivery_id, d.delivery_detail_id, d.inventory_item_id, d.delivered_quantity FROM wsh_delivery_details d, wsh_delivery_assignments da WHERE d.delivery_detail_id = da.delivery_detail_id AND da.delivery_id = :delivery_id;

Other practical uses: identifying unpicked lines via PICKABLE_FLAG = 'Y' and RELEASED_STATUS; finding split lines by querying SPLIT_FROM_DELIVERY_DETAIL_ID; and auditing interface failures through OE_INTERFACED_FLAG or INV_INTERFACED_FLAG. Reconciliation queries frequently join to MTL_MOVEMENT_STATISTICS through MOVEMENT_ID and to WMS_LPN_CONTENTS through LPN_CONTENT_ID.

Related Objects

The table participates in an extensive FK network. The most significant related objects include:

• WSH_DELIVERY_ASSIGNMENTS — joins on DELIVERY_DETAIL_ID and PARENT_DELIVERY_DETAIL_ID; links detail lines to the parent delivery.
• WSH_DELIVERY_DETAILS (self-reference) — via SPLIT_FROM_DELIVERY_DETAIL_ID for line splits.
• WSH_DEL_DETAILS_INTERFACE — staging interface joined on DELIVERY_DETAIL_ID.
• WSH_EXCEPTIONS — shipping exceptions keyed by DELIVERY_DETAIL_ID.
• WSH_FREIGHT_COSTS — freight cost records per delivery detail.
• WSH_PICKING_BATCHES — picking batch linkage on DELIVERY_DETAIL_ID.
• HZ_PARTIES — referenced via PARTY_ID for the customer/party.
• MTL_CUSTOMER_ITEMS — via CUSTOMER_ITEM_ID.
• WMS_LPN_CONTENTS — via LPN_CONTENT_ID for LPN logistics.
• JAI / localization tables (JAI_OM_LC_MATCHINGS, JAI_OM_OE_RMA_LINES, JAI_OM_OE_GEN_TAXINV_T) — references used by India localization.

Because of its hub role and central FK position, any extract or replication of shipping data should treat DELIVERY_DETAIL_ID as the stable entity key and expect its mutable attributes to change frequently across the pick-to-ship lifecycle.