Search Results insert_row_bulk

Overview

WSH_TRXSN_HANDLER is an Oracle EBS PL/SQL package owned by APPS that provides the client-side interface routines for maintaining records in the MTL_SERIAL_NUMBERS_INTERFACE table. This table is the open interface through which serial number information is staged before Oracle Warehouse Management and Oracle Inventory processes validate and import the data into the production serial number tables. The package is classified as an OTHER API in the ETRM metadata and is declared with AUTHID CURRENT_USER, meaning its database objects are resolved using the privileges of the invoking user rather than the package owner.

The package header carries the revision marker $Header: WSHIISNS.pls 115.2 2003/07/08 01:54:41 heali ship $, identifying it as a Shipping (WSH) module object. Its central purpose is to give forms, concurrent programs, and custom code a controlled mechanism for inserting, updating, deleting, and locking serial number interface records without directly issuing DML against the interface table.

Key Procedures and Functions

• INSERT_ROW — Inserts a single row into MTL_SERIAL_NUMBERS_INTERFACE. It accepts a broad set of serial attributes, including source code and source line identifier, from/to serial numbers, parent and vendor serial numbers, vendor lot number, standard WHO columns, request/program audit columns, an error code, and a process flag defaulting to 1.
• INSERT_ROW_BULK — The bulk variant of INSERT_ROW, used to stage multiple serial number interface rows in a single call. This is the procedure associated with the user's search term and is intended for high-volume loading scenarios where row-by-row invocation would be inefficient.
• UPDATE_ROW — Updates an existing row in MTL_SERIAL_NUMBERS_INTERFACE, keyed by transaction interface identifier, applying revised serial and source attributes along with last-updated audit information.
• DELETE_ROW — Removes a row from the serial number interface table, typically used to purge incorrectly staged or rejected records.
• LOCK_ROW — Acquires a row-level lock on a specified interface record, providing concurrency control when multiple sessions may attempt to modify the same staged serial number entry.

The package declares one public exception, WSH_FM_SERIALNO_NULL, raised when the from serial number is null — the primary validation enforced at this layer.

Tables Accessed

• MTL_SERIAL_NUMBERS_INTERFACE — The primary target table. All five procedures operate against this interface table, which is the staging area for serial number import into Oracle Inventory and Warehouse Management.
• MTL_MATERIAL_TRANSACTIONS_S — The material transaction sequence, referenced to obtain transaction identifiers that link staged serial numbers to their originating material transaction.
• PLITBLM — The PL/SQL table used by Oracle Forms and the package internals for bulk operations, consistent with the presence of INSERT_ROW_BULK.

All three objects are referenced through APPS synonyms.

Usage Notes

WSH_TRXSN_HANDLER is invoked by client code — most commonly Oracle Forms and concurrent programs in the Shipping and Inventory modules — rather than being run directly by end users. The presence of INSERT_ROW_BULK makes it suitable for batch and high-volume serial staging, while the single-row procedures support interactive form-based maintenance. Because the package uses AUTHID CURRENT_USER, executing sessions must hold appropriate DML privileges on the underlying interface tables. The ETRM metadata records that the package is referenced by one other APPS package, indicating a degree of internal reuse within the Shipping module. One other package references it, so dependency changes should be reviewed before modification.