Search Results lock_dlvy_no_compare

Overview

WSH_NEW_DELIVERIES_PVT is the private implementation package for the Oracle Shipping Execution delivery entity. It encapsulates the core insert, update, delete, and locking logic that governs rows in the WSH_NEW_DELIVERIES base table, which stores the header-level attributes of a delivery — name, status, delivery type, carrier, ship method, freight terms, FOB terms, dock, waybill, and the planned and acceptance flags. In Oracle EBS 12.1.1 and 12.2.2, this package acts as the write layer beneath the public WSH_DELIVERIES_PUB interfaces and the Shipping Transactions form, ensuring that delivery header changes are made consistently and that dependent child records such as delivery legs, freight costs, and the DFF (_S) extension tables remain synchronized.

A second and equally important responsibility is transportation management integration. The package maintains the tms_interface_flag on the delivery, driving the state machine that communicates delivery data to Oracle Transportation Management (OTM) under the OTM R12 / glog project. That flag progresses through values such as NS (not to be sent), CR (creation required), UR (update required), UP (update in process), DR (delete required), AW (awaiting answer), AR (answer received), and CMP (completed) — constants declared directly at the top of the package specification. Because the package is classified PVT, it is not a supported public API; it is intended for internal consumption by other Shipping Execution and Order Management modules.

Key Procedures and Functions

• CREATE_DELIVERY — Inserts a new delivery header record, populating the delivery record type from the supplied attributes and writing to WSH_NEW_DELIVERIES and its _S (DFF) counterpart.
• UPDATE_DELIVERY — Modifies an existing delivery header. This is the procedure users reach when searching for "update_delivery"; it refreshes the record type, applies changed columns, and updates the TMS interface flag when the change warrants propagation to OTM.
• DELETE_DELIVERY — Removes a delivery header and coordinates removal of associated child rows (legs, freight costs).
• LOCK_DELIVERY — Acquires a lock on a delivery, typically by delivery ID, to serialize concurrent modification attempts.
• LOCK_DLVY_NO_COMPARE — A variant locking routine used when the caller must lock a delivery number without performing a full record comparison, useful in high-contention or bulk flows.
• POPULATE_RECORD — Loads a Delivery_Rec_Type structure from the database for a given delivery, providing the current persisted values to callers.
• TABLE_TO_RECORD — Converts a WSH_NEW_DELIVERIES row (or %ROWTYPE cursor) into the package's record type, used by POPULATE_RECORD and internal validation.
• GET_NAME — Returns or derives the delivery name, honoring naming rules and the sequence maintained in WSH_NEW_DELIVERIES_S.
• CLONE — Copies an existing delivery into a new record, supporting split, copy, or re-creation scenarios.
• UPDATE_TMS_INTERFACE_FLAG — Sets the tms_interface_flag to the appropriate state (for example UR, UP, or CMP) for OTM synchronization.

Tables Accessed

• WSH_NEW_DELIVERIES — Primary header table; all create, update, delete, and lock operations target this table.
• WSH_NEW_DELIVERIES_S — The DFF/extension table storing descriptive flexfield attribute columns keyed by delivery ID; read and written alongside the header.
• WSH_DELIVERY_LEGS / WSH_DELIVERY_LEGS_S — Child leg records (pickup and dropoff stops) that must be inserted, updated, or deleted in step with the header.
• WSH_FREIGHT_COSTS — Freight charge rows associated with the delivery; managed during delete and clone operations.
• DUAL — Used for sequence, sysdate, and constant evaluations in PL/SQL.
• PLITBLM — The standard EBS PL/SQL table/bulk collection helper, used for bulk DML and name derivation.

Usage Notes

WSH_NEW_DELIVERIES_PVT is not invoked directly by end users or supported customizations; it is called internally by WSH_DELIVERIES_PUB, the Shipping Transactions and Delivery Workbench forms, delivery assignment and consolidation logic, and shipping concurrent programs such as Ship Confirm and Interface Trip Stop processing. The ETRM metadata records that the package is referenced by 58 other packages, confirming its central role in the shipping write path. Because the package is classified PVT and its signature is not guaranteed across patches or releases, custom code should call WSH_DELIVERIES_PUB or the documented Shipping Execution APIs rather than this private package. The "update_delivery" search term maps to the UPDATE_DELIVERY procedure, but direct calls bypass public validation, so they are appropriate only for Oracle-internal or tightly controlled extension code.