Results for “wsh_del_details_interface”

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

Overview

WSH_DEL_DETAILS_INTERFACE is the Delivery Detail interface table in the Oracle Shipping Execution (WSH) module, owned by the WSH schema. It serves as the inbound staging area through which delivery detail records — the line-level shipment entities that describe what is being shipped, in what quantity, from which organization and location, and to which customer — are loaded into Oracle E-Business Suite before validation and transfer into the base WSH_DELIVERY_DETAILS table. It is a core component of the Shipping Open Interface (SOI), used both for external data loads from legacy or third-party systems and for internal Oracle flows such as Order Management ship-confirm processing and Advanced Supply Chain Planning releases.

The documented physical schema in ETRM 12.2.2 records 214 columns, reflecting the broad set of attributes required to fully describe a shippable line. The table is transactional and high-volume by nature, populated and purged through concurrent interface processing. From a Data Vault modeling perspective, the mined foreign-key structure suggests a satellite-leaning classification: the table carries numerous descriptive and contextual attributes around a small set of driving business keys, and includes audit and interface-control columns typical of a staging satellite attached to a delivery-detail hub.

Key Information Stored

The surrogate primary key is DELIVERY_DETAIL_INTERFACE_ID, enforced by WSH_DEL_DETAILS_INTERFACE_PK and mirrored by unique index WSH_DEL_DETAILS_INTERFACE_U1. There is no separately documented business-key unique index, so this identifier is the sole documented unique key and is used as the correlation handle when child interface rows are loaded.

The most significant columns include:

• DELIVERY_DETAIL_INTERFACE_ID — surrogate primary key for the staging row.
• DELIVERY_DETAIL_ID — foreign key to WSH_DELIVERY_DETAILS; populated after successful transfer to the base table.
• SOURCE_CODE, SOURCE_HEADER_ID, SOURCE_LINE_ID — identify the originating system and source document lines.
• INVENTORY_ITEM_ID, REVISION, LOT_NUMBER, SERIAL_NUMBER — the item and its control attributes.
• ORGANIZATION_ID, SUBINVENTORY, LOCATOR_ID, SHIP_FROM_LOCATION_ID — the shipping inventory and location context.
• CUSTOMER_ID, SHIP_TO_LOCATION_ID, DELIVER_TO_LOCATION_ID, PARTY_ID — the receiving party and destination, with PARTY_ID tied to HZ_PARTIES.
• REQUESTED_QUANTITY, SHIPPED_QUANTITY, DELIVERED_QUANTITY, CANCELLED_QUANTITY and their _UOM columns — the quantity model.
• INTERFACE_ACTION_CODE, PROCESS_FLAG, PROCESS_MODE, PROCESS_STATUS_FLAG, LOCK_FLAG, DELETE_FLAG — interface lifecycle control.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE — audit and concurrent program context.
• CUSTOMER_ITEM_ID, HAZARD_CLASS_ID, LPN_CONTENT_ID, MOVEMENT_ID — reference FKs to customer items, hazard classes, LPN contents, and movement statistics.

Common Use Cases and Queries

Typical uses include loading shipment data from external systems, monitoring the status of staged interface rows, and reconciling interface records against their transferred delivery details. A common status query inspects unprocessed or errored rows:

• SELECT DELIVERY_DETAIL_INTERFACE_ID, SOURCE_CODE, INVENTORY_ITEM_ID, REQUESTED_QUANTITY, PROCESS_STATUS_FLAG FROM WSH_DEL_DETAILS_INTERFACE WHERE PROCESS_FLAG = 'N' OR PROCESS_STATUS_FLAG = 'E';
• Join to WSH_DELIVERY_DETAILS on DELIVERY_DETAIL_ID to confirm which staged rows have been transferred and to compare REQUESTED_QUANTITY against the delivered quantity.
• Join to WSH_DEL_ASSGN_INTERFACE on DELIVERY_DETAIL_INTERFACE_ID to validate that each detail row has a corresponding assignment before processing.
• Aggregate by ORGANIZATION_ID and SOURCE_CODE for load-volume and throughput reporting.

Related Objects

The most significant related objects are:

• WSH_DELIVERY_DETAILS — referenced through DELIVERY_DETAIL_ID; the post-validation target table.
• WSH_DEL_ASSGN_INTERFACE — references this table via DELIVERY_DETAIL_INTERFACE_ID, carrying the assignment (task) rows for each detail.
• WSH_FREIGHT_COSTS_INTERFACE — also references DELIVERY_DETAIL_INTERFACE_ID, holding freight cost staging lines.
• HZ_PARTIES — referenced through PARTY_ID for the trading partner.
• MTL_CUSTOMER_ITEMS — referenced through CUSTOMER_ITEM_ID.
• PO_HAZARD_CLASSES_B — referenced through HAZARD_CLASS_ID.
• WMS_LPN_CONTENTS — referenced through LPN_CONTENT_ID.
• MTL_MOVEMENT_STATISTICS — referenced through MOVEMENT_ID.
• Shipping Open Interface concurrent programs and the Shipping Execution delivery-detail APIs, which read, validate, and transfer these rows into WSH_DELIVERY_DETAILS.