Search Results wsh_new_deliveries_u1

Overview

WSH.WSH_NEW_DELIVERIES is the core delivery header table within the Oracle E-Business Suite Shipping Execution module (WSH schema). It stores one record per delivery — the logical grouping of order lines, move orders, or shipping transactions that travel together from an initial pickup location to an ultimate drop-off location. Every downstream shipping activity in Oracle EBS, including pick release, packing, manifesting, freight costing, and advanced shipment notice (ASN) generation, anchors to a row in this table. The object is documented as VALID in both Release 12.1.1 and 12.2.2 ETRM repositories, carrying a physical column count of 157 and residing in the APPS_TS_TX_DATA tablespace with a PCT Free of 10.

From a dimensional modeling perspective (heuristic Data Vault classification mined from the foreign key structure), WSH_NEW_DELIVERIES behaves as a hub: its primary key, WSH_DELIVERIES_PK on DELIVERY_ID, is referenced by at least fourteen dependent tables spanning shipping, transportation, receiving, and India localization (JAI) modules. This confirms its role as the central business key entity around which delivery-related links and satellites are constructed. FND Design Data registers the object as WSH.WSH_NEW_DELIVERIES.

Key Information Stored

The document excerpt documents the following significant attributes:

• DELIVERY_ID — NUMBER, the mandatory surrogate primary key that uniquely identifies a delivery and is enforced by the unique index WSH_NEW_DELIVERIES_U1.
• NAME — VARCHAR2(30), the user-visible unique delivery name, enforced by unique index WSH_NEW_DELIVERIES_U2 (the index referenced in the user's search).
• STATUS_CODE — shipping status such as OP for an open delivery; supported by non-unique index WSH_NEW_DELIVERIES_N1.
• PLANNED_FLAG — indicates whether the delivery was created through planning (Y) or is unplanned (N).
• INITIAL_PICKUP_DATE / INITIAL_PICKUP_LOCATION_ID — first pickup timing and originating location; indexed via WSH_NEW_DELIVERIES_N7.
• ULTIMATE_DROPOFF_DATE / ULTIMATE_DROPOFF_LOCATION_ID — final drop-off timing and destination.
• CUSTOMER_ID — ship-to customer, indexed via WSH_NEW_DELIVERIES_N6 with a HASH_VALUE subcolumn.
• ORGANIZATION_ID — the shipping warehouse or inventory organization, indexed via WSH_NEW_DELIVERIES_N4.
• SHIP_METHOD_CODE — carrier service method, indexed via WSH_NEW_DELIVERIES_N5.
• PARTY_ID — foreign key referencing HZ_PARTIES, linking the delivery to the trading partner.
• ROUTING_RESPONSE_ID — association with Oracle Transportation Management routing results, indexed via WSH_NEW_DELIVERIES_N3.
• BATCH_ID / AP_BATCH_ID — picking batch and payables batch associations, indexed via WSH_NEW_DELIVERIES_N8 and N9.
• ASN_SHIPMENT_HEADER_ID / RCV_SHIPMENT_HEADER_ID — links to outbound ASN and inbound receiving shipments, indexed via N10 and N11.

The distinction between the surrogate key (DELIVERY_ID) and the business-key candidates (DELIVERY_ID and NAME, both enforced unique) is essential when writing integration logic: NAME is user-facing and may be regenerated, whereas DELIVERY_ID is immutable and preferred for all foreign key joins.

Common Use Cases and Queries

Typical scenarios include shipment tracking, on-time delivery reporting, freight cost analysis, and ASN reconciliation. A representative query retrieves open deliveries with their carrier and destination:

• SELECT d.delivery_id, d.name, d.status_code, d.ship_method_code FROM wsh_new_deliveries d WHERE d.organization_id = :p_org AND d.status_code = 'OP';
• Joining to WSH_DELIVERY_ASSIGNMENTS and WSH_DELIVERY_LEGS to report the contents and stops of each delivery.
• Resolving a user-supplied delivery name to its surrogate ID via the U2 index: SELECT delivery_id FROM wsh_new_deliveries WHERE name = :p_name;
• Identifying deliveries awaiting confirmation (CONFIRM_DATE IS NULL) for manifesting dashboards.
• Grouping by ORGANIZATION_ID and INITIAL_PICKUP_DATE for warehouse throughput reporting.

Because NAME is unique, lookup by name is index-efficient; by contrast, filtering on STATUS_CODE alone is non-unique and should always be combined with ORGANIZATION_ID or date ranges to limit scan cost.

Related Objects

• WSH_DELIVERY_ASSIGNMENTS — maps delivery lines to deliveries via DELIVERY_ID and PARENT_DELIVERY_ID.
• WSH_DELIVERY_LEGS — stores leg-level routing for a delivery (DELIVERY_ID).
• WSH_EXCEPTIONS — captures shipping exceptions raised against a delivery (DELIVERY_ID).
• WSH_FREIGHT_COSTS — records freight charges per delivery (DELIVERY_ID).
• WSH_PICKING_BATCHES — associates picking batches with deliveries (DELIVERY_ID).
• WSH_NEW_DEL_INTERFACE — open interface for loading deliveries (DELIVERY_ID).
• WMS_SHIPPING_TRANSACTION_TEMP — Warehouse Management staging table referencing DELIVERY_ID.
• OE_INVOICE_NUMBERS — links invoiced orders back to deliveries (DELIVERY_ID).
• HZ_PARTIES — master party table referenced via WSH_NEW_DELIVERIES.PARTY_ID.
• WSH_DEL_ASSGN_INTERFACE — interface table carrying PARENT_DELIVERY_ID references.