Results for “wsh_del_legs_interface”

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

Overview

WSH_DEL_LEGS_INTERFACE is the delivery-legs interface table within the Oracle Shipping Execution (WSH) module. It serves as the inbound staging table through which external systems, legacy applications, and batch programs supply delivery leg records for validation and eventual transfer into the operational delivery legs table, WSH_DELIVERY_LEGS. The table holds 57 documented columns in the ETRM 12.2.2 physical schema and is owned by the WSH schema. Each row represents a single leg of a delivery—typically a pickup-to-drop-off movement performed by a carrier—and is subject to the standard Oracle interface-table control columns such as PROCESS_FLAG, PROCESS_MODE, INTERFACE_ACTION_CODE, and DELETE_FLAG that drive concurrent-program processing.

From a heuristic Data Vault modeling perspective, the foreign-key structure mined from the ETRM metadata suggests a link classification. The table resolves associations among deliveries, trip stops, and existing delivery legs, and its composite reference structure is characteristic of a relationship (link) entity rather than a standalone hub or descriptive satellite.

Key Information Stored

The surrogate primary key is DELIVERY_LEG_INTERFACE_ID, enforced by the WSH_DEL_LEGS_INTERFACE_PK constraint and mirrored by the unique index WSH_DEL_LEGS_INTERFACE_U1; this column is also the business-key candidate for the interface row. The most significant columns include:

• DELIVERY_LEG_ID — the identifier of the corresponding operational record in WSH_DELIVERY_LEGS, forming the bridge from interface to production data.
• DELIVERY_ID and DELIVERY_INTERFACE_ID — the delivery and delivery-interface keys that anchor the leg to its parent shipment.
• PICK_UP_STOP_ID / PICK_UP_STOP_INTERFACE_ID and DROP_OFF_STOP_ID / DROP_OFF_STOP_INTERFACE_ID — the origin and destination stops, linking to WSH_TRIP_STOPS_INTERFACE for the interface side.
• SEQUENCE_NUMBER — ordering of the leg within the delivery.
• GROSS_WEIGHT, NET_WEIGHT, WEIGHT_UOM_CODE, VOLUME, VOLUME_UOM_CODE — weight and volume measures with unit-of-measure qualifiers.
• INTERFACE_ACTION_CODE, PROCESS_FLAG, PROCESS_MODE, PROCESS_STATUS_FLAG, LOCK_FLAG, DELETE_FLAG — control columns governing how the interface row is validated and transferred.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE — concurrent request provenance columns.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — the standard WHO audit columns.
• Carrier and documentation attributes such as BOOKING_NUMBER, BOOKING_OFFICE, AETC_NUMBER, SERVICE_CONTRACT, CARRIER_EXPORT_REF, and POD_DATE — commercial and proof-of-delivery details carried through the interface.

Common Use Cases and Queries

Typical usage centers on loading and monitoring interface rows before the delivery-legs import concurrent program processes them. A common pattern is to inspect unprocessed rows by process flag or status:

• SELECT delivery_leg_interface_id, delivery_interface_id, pick_up_stop_interface_id, drop_off_stop_interface_id, process_flag, process_status_flag FROM wsh_del_legs_interface WHERE process_flag = 'N';
• Joining to WSH_NEW_DEL_INTERFACE on DELIVERY_INTERFACE_ID to trace an interface leg back to its delivery-interface header.
• Joining to WSH_TRIP_STOPS_INTERFACE on PICK_UP_STOP_INTERFACE_ID and DROP_OFF_STOP_INTERFACE_ID to reconcile stop sequences and locations.
• Reconciling interface rows against WSH_DELIVERY_LEGS on DELIVERY_LEG_ID to confirm successful transfer and to detect orphaned or rejected legs.
• Reporting weight and volume totals by delivery for freight-cost estimation, aggregating GROSS_WEIGHT and VOLUME with their UOM codes.

Interface troubleshooting generally keys on INTERFACE_ACTION_CODE and PROCESS_STATUS_FLAG to isolate rows requiring correction and resubmission.

Related Objects

The documented foreign keys and inbound references establish the following significant relationships:

• WSH_DELIVERY_LEGS — referenced via WSH_DEL_LEGS_INTERFACE.DELIVERY_LEG_ID; the operational target table.
• WSH_NEW_DEL_INTERFACE — referenced via DELIVERY_INTERFACE_ID; the delivery-interface header.
• WSH_TRIP_STOPS_INTERFACE — referenced via PICK_UP_STOP_INTERFACE_ID and DROP_OFF_STOP_INTERFACE_ID; supplies stop-level interface data.
• WSH_FREIGHT_COSTS_INTERFACE — references WSH_DEL_LEGS_INTERFACE.DELIVERY_LEG_INTERFACE_ID, carrying freight cost interface rows tied to each leg.

These objects collectively form the interface-and-import chain that moves delivery leg data from external sources into Oracle Shipping Execution.