Results for “wsh_deliveries_interface”

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

Overview

The WSH_DELIVERIES_INTERFACE table is a delivery interface data object owned by the OE (Order Entry) schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It functions as an inbound staging area through which external systems, legacy applications, or third-party logistics feeds supply delivery header information for processing by Oracle Shipping Execution. Records inserted into this table are consumed by the delivery import process, which validates each row and creates or updates the corresponding delivery in the WSH_DELIVERIES base table. The presence of PROCESS_FLAG, ERROR_EXPLANATION, and ERROR_CODE columns confirms its role as a transactional interface rather than an operational table.

From a Data Vault modeling perspective, the table's heuristic classification is hub-leaning. The unique index WSH_DELIVERIES_INT_U1 on TRANSACTION_ID and the primary key constraint WSH_DELIVERIES_INT_PK both anchor identity on that single column, which typically indicates a hub-style business key. In practice, however, the wide column set—109 columns spanning departure, weight, volume, customer, and vehicle attributes—means the table behaves more like a combined hub and satellite in a conventional interface staging pattern.

Key Information Stored

The surrogate primary key is TRANSACTION_ID, which is also the business-key candidate exposed through the unique index WSH_DELIVERIES_INT_U1. This column links delivery header records to their subordinate detail rows in the freight charge and packed container interface tables. Other significant columns include:

• DELIVERY_ID / DELIVERY_NAME — identifies the target delivery when an existing delivery is being updated rather than created.
• DEPARTURE_ID / DEPARTURE_NAME — associates the interface row with a departure; DEPARTURE_ID carries a foreign key to WSH_DEPARTURES.
• ORGANIZATION_ID / ORGANIZATION_CODE — the shipping warehouse or inventory organization that owns the delivery.
• CUSTOMER_ID / CUSTOMER_NUMBER — the ship-to customer receiving the goods.
• LOADING_ORDER_FLAG / LOADING_ORDER_DESC — controls whether the delivery is sequenced onto the loading order.
• GROSS_WEIGHT / WEIGHT_UOM_CODE / VOLUME / VOLUME_UOM_CODE — physical shipment attributes used in carrier rating and documentation.
• WAYBILL_NUM / BILL_OF_LADING / SEAL_CODE — transport documentation references.
• VEHICLE_ITEM_ID, VEHICLE_NUMBER, VEHICLE_SEGMENT1 through VEHICLE_SEGMENT20 — vehicle and trailer descriptive flexfield data.
• PROCESS_FLAG, ERROR_CODE, ERROR_EXPLANATION, ACTION_CODE — control and diagnostic columns governing import processing outcomes.

Common Use Cases and Queries

The primary use case is bulk loading of delivery headers through the Shipping Execution delivery import concurrent program. Typical supporting queries include identifying unprocessed rows, diagnosing failures, and reconciling staged data against base tables:

• Select rows where PROCESS_FLAG is null or 'N' to determine pending import workload.
• Filter ERROR_CODE IS NOT NULL to retrieve rejected records and review ERROR_EXPLANATION for corrective action.
• Join TRANSACTION_ID to WSH_FREIGHT_CHARGES_INTERFACE and WSH_PACKED_CONTAINER_INTERFACE to assemble a complete delivery payload before submission.
• Join DEPARTURE_ID to WSH_DEPARTURES to confirm the departure exists prior to import, avoiding referential failures.
• Aggregate GROSS_WEIGHT and VOLUME by ORGANIZATION_ID for pre-import capacity and rating analysis.

Related Objects

The table participates in a small but well-defined relationship set:

• WSH_DEPARTURES — referenced by WSH_DELIVERIES_INTERFACE.DEPARTURE_ID; the parent departure record.
• WSH_FREIGHT_CHARGES_INTERFACE — child table keyed by TRANSACTION_ID, holding freight charge lines for the delivery.
• WSH_PACKED_CONTAINER_INTERFACE — child table keyed by TRANSACTION_ID, holding packed container detail.
• WSH_DELIVERIES — the base delivery table populated by the import process from validated interface rows.

Together these objects form the inbound delivery staging model used by Oracle Shipping Execution.