Search Results lock_record




Overview
Explore in AI Chat

Overview

APPS.WSH_INTERFACE_MESSAGE_PKG is a PL/SQL package that belongs to the Oracle Shipping Execution (WSH) module of Oracle E-Business Suite. Its specific business purpose is to support the Interface Message Correction (IMC) functionality — a corrective maintenance framework used by warehouse and shipping personnel to resolve data errors that arise when delivery and delivery detail records fail to import from the open interface tables into the transactional shipping tables.

The package is declared with AUTHID CURRENT_USER, meaning its SQL statements execute with the privileges of the invoking schema rather than the package owner. The header, last modified in the 120.1 version stream (2009), is shipped with Oracle's standard proprietary notice.

In the shipping flow, records are staged in the WSH delivery interface tables, validated by the shipping concurrent programs, and then transferred to the base tables. When validation or transfer fails, the offending row remains in the interface table and an error message is generated. The Interface Message Correction Form allows a user to inspect such a row, correct the erroneous data, and resubmit it. To prevent two users from correcting the same row simultaneously, the form must first lock the row in the interface tables — this is the single responsibility of WSH_INTERFACE_MESSAGE_PKG.

Key Procedures and Functions

  • lock_record — The only documented procedure in the package. Its stated purpose is "Submit From Interface Message Correction Form." It is invoked by the Interface Message Correction Form to lock the relevant record in the WSH interface tables so that it can be corrected without concurrent interference. The procedure accepts identifiers for either a delivery interface row or a delivery detail interface row and returns a status indicator to the caller.

No other procedures or functions are documented for this package in the ETRM metadata. Because the package specification contains only this one entry point, the package functions as a narrowly scoped locking utility rather than a general-purpose shipping API.

Tables Accessed

  • WSH_DEL_DETAILS_INTERFACE — The delivery detail interface table. The lock_record procedure locks the row identified by the delivery detail interface identifier so that the associated detail-level error can be corrected through the IMC form.
  • WSH_NEW_DEL_INTERFACE — The delivery (header) interface table. The procedure locks the row identified by the delivery interface identifier, protecting the delivery-level record while it is being corrected.

Both tables are referenced through APPS synonyms, consistent with the package's ownership in the APPS schema. Locking these rows with SELECT ... FOR UPDATE semantics ensures that concurrent IMC sessions cannot modify the same interface record until the first session commits or rolls back.

Usage Notes

  • Invocation context: The procedure is designed to be called from the Oracle Forms-based Interface Message Correction Form, the standard UI for diagnosing and repairing shipping interface errors in Releases 12.1.1 and 12.2.2.
  • Calling convention: Callers must supply at least one of the two interface identifiers (delivery or delivery detail) and must handle the returned status value to determine whether the lock succeeded.
  • Custom code: Although the package is not referenced by any other documented package, it can be invoked from custom PL/SQL where a programmatic lock on a shipping interface row is required prior to correction. Because it is an AUTHID CURRENT_USER package, custom callers must ensure the invoking schema has the necessary object privileges on the WSH interface tables.
  • Versioning: The behavior is consistent across 12.1.1 and 12.2.2; the underlying interface tables and the IMC framework remain substantially unchanged between these releases.
  • Scope limitation: The package performs no validation and no data correction; it only acquires the lock. All corrective logic, resubmission, and error-message maintenance are handled elsewhere in the Interface Message Correction framework.