Results for “delivery_interface_id”

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

Overview

WSH_DEL_ASSGN_INTERFACE is the Delivery Assignment Interface Table in the Oracle E-Business Suite Shipping Execution (WSH) module. It functions as a staging and open-interface table used by the Delivery Interface (and related assignment) programs to create, update, or delete delivery assignments in bulk. Records in this table do not represent live shipping data; they represent pending assignment instructions that the interface concurrent programs validate and then push into the base delivery tables such as WSH_DELIVERY_ASSIGNMENTS and WSH_DELIVERY_DETAILS.

In Oracle EBS 12.1.1 and 12.2.2, the table resides in the WSH schema and is documented as VALID. Its physical schema contains 26 columns, and it carries the primary key WSH_DEL_ASSGN_INTERFACE_PK on DEL_ASSGN_INTERFACE_ID. Two further unique indexes, WSH_DEL_ASSGN_INTERFACE_U2 and WSH_DEL_ASSGN_INTERFACE_U3, additionally qualify DELIVERY_ASSIGNMENT_ID and DELIVERY_DETAIL_INTERFACE_ID, indicating that the interface is designed to process one assignment per source detail.

Based on the foreign-key topology mined from the ETRM metadata, the table behaves as a link (association) entity: it connects new delivery interface rows, delivery detail interface rows, and existing parent delivery/detail records, and therefore a Data Vault modeling suggestion would be to treat WSH_DEL_ASSGN_INTERFACE as a link table rather than a hub or satellite.

Key Information Stored

Although the table contains 26 columns, the following are the most significant for integration and troubleshooting:

• DEL_ASSGN_INTERFACE_ID — the surrogate primary key of the interface row (WSH_DEL_ASSGN_INTERFACE_PK); unique per assignment record.
• DELIVERY_ASSIGNMENT_ID — the identifier of the assignment being created or altered; also a documented unique index candidate (U2), linking back to WSH_DELIVERY_ASSIGNMENTS.
• DELIVERY_DETAIL_INTERFACE_ID — the source detail interface row that this assignment applies to; unique index candidate (U3) and foreign key to WSH_DEL_DETAILS_INTERFACE.
• DELIVERY_INTERFACE_ID and PARENT_DELIVERY_INTERFACE_ID — foreign keys to WSH_NEW_DEL_INTERFACE, identifying the new delivery (and its parent) being staged.
• PARENT_DETAIL_INTERFACE_ID — reference to the parent detail interface row.
• DELIVERY_ID and PARENT_DELIVERY_ID — existing delivery identifiers; PARENT_DELIVERY_ID is a foreign key to WSH_NEW_DELIVERIES.
• DELIVERY_DETAIL_ID and PARENT_DELIVERY_DETAIL_ID — existing detail identifiers; PARENT_DELIVERY_DETAIL_ID is a foreign key to WSH_DELIVERY_DETAILS.
• INTERFACE_ACTION_CODE — instructs the interface program whether to create, update, or delete the assignment.
• PROCESS_FLAG, PROCESS_MODE, and PROCESS_STATUS_FLAG — control which rows are eligible for the interface run and record processing outcome.
• LOCK_FLAG, ACTIVE_FLAG, and DELETE_FLAG — concurrency and lifecycle controls for the interface record.
• Standard who-columns including CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, plus PROGRAM_APPLICATION_ID, PROGRAM_ID, REQUEST_ID, and PROGRAM_UPDATE_DATE for concurrent program auditing.

Common Use Cases and Queries

Typical usage centers on bulk shipping integrations — for example, loading assignment data from an external order management or warehouse system — and on diagnosing why a delivery did not receive an expected assignment. Support and DBA queries commonly filter rows by PROCESS_FLAG or INTERFACE_ACTION_CODE to isolate unprocessed or failed records:

• SELECT del_assgn_interface_id, delivery_detail_interface_id, delivery_assignment_id, interface_action_code, process_flag FROM wsh.wsh_del_assgn_interface WHERE process_flag IS NULL OR process_flag = 'N';
• Joining to WSH_NEW_DEL_INTERFACE on delivery_interface_id to confirm which staged delivery a failed assignment belongs to.
• Correlating REQUEST_ID with the interface concurrent program run to trace a batch load.
• Auditing stale records using CREATION_DATE against LAST_UPDATE_DATE to identify entries never picked up by the interface.

For reporting, the table is generally transient: it should be queried for status and reconciliation during integration windows, not as a historical reporting source.

Related Objects

• WSH_DELIVERY_ASSIGNMENTS — destination table; joined via DELIVERY_ASSIGNMENT_ID.
• WSH_NEW_DEL_INTERFACE — staged deliveries; joined via DELIVERY_INTERFACE_ID and PARENT_DELIVERY_INTERFACE_ID.
• WSH_DEL_DETAILS_INTERFACE — staged details; joined via DELIVERY_DETAIL_INTERFACE_ID.
• WSH_NEW_DELIVERIES — live delivery headers; joined via PARENT_DELIVERY_ID.
• WSH_DELIVERY_DETAILS — live delivery details; joined via PARENT_DELIVERY_DETAIL_ID.
• WSH_DELIVERY_ASSIGNMENTS interface concurrent programs and the Delivery Interface/Assignment APIs that read and purge this staging table.