Search Results get_lpn_list

Overview

WMS_CONTAINER2_PUB is a public PL/SQL API package in the Oracle Warehouse Management (WMS) module, owned by the APPS schema and declared with AUTHID CURRENT_USER. It provides the second-tier container and License Plate Number (LPN) management interface within Oracle E-Business Suite Release 12.1.1 and 12.2.2. Where the companion package WMS_CONTAINER_PUB establishes the base container abstractions, WMS_CONTAINER2_PUB exposes a broader set of LPN lifecycle operations: purging obsolete LPNs, exploding container hierarchies, transferring contents between LPNs, computing container quantities, resolving outermost parent LPNs, and validating pick-and-drop LPN operations. The package is classified as a PUB (public) API, indicating it is intended for direct invocation by external callers, including Oracle Forms, concurrent programs, and custom integration code. Its header comment ($Header: WMSCNT2S.pls 115.8 2003/02/05) shows a long-stable signature lineage carried forward into the 12.2.2 codebase.

Key Procedures and Functions

• PURGE_LPN — Removes a specified LPN and, based on caller-supplied purge and history-retention parameters, optionally deletes associated history records.
• EXPLODE_LPN — Recursively expands an LPN into its constituent contents to a caller-specified explosion level, returning the resolved contents through an output table of container records.
• TRANSFER_LPN_CONTENTS — Moves the entire contents of a source LPN into a destination LPN.
• CONTAINER_REQUIRED_QTY — Calculates the quantity of a source item required to fill a destination container item, returning the destination container identifier and the computed required quantity.
• GET_OUTERMOST_LPN — Traverses the container hierarchy upward from a given LPN to identify its topmost (outermost) parent LPN.
• GET_LPN_LIST — Returns a list of LPNs matching caller-supplied selection criteria.
• VALIDATE_PICK_DROP_LPN — Validates that a pick-and-drop operation against an LPN is legal before the transaction is executed, enforcing container integrity rules.
• DEFAULT_PICK_DROP_LPN — Determines or defaults the LPN to be used during a pick-and-drop transaction.

Tables Accessed

• WMS_LICENSE_PLATE_NUMBERS — The master LPN definition; read and updated for existence, status, and hierarchy relationships.
• WMS_LPN_CONTENTS — The child contents of each LPN; central to explosion, transfer, and purge logic.
• WMS_LPN_HISTORIES — Historical LPN records, consulted or purged according to the p_purge_history and p_del_history_days_old parameters.
• WSH_CONTAINER_ITEMS and WSH_DELIVERY_DETAILS / WSH_DELIVERY_ASSIGNMENTS — Shipping-side container and delivery associations used to validate that LPN operations do not violate outbound delivery commitments.
• MTL_SYSTEM_ITEMS, MTL_PARAMETERS, MTL_SERIAL_NUMBERS, and MTL_MATERIAL_TRANSACTIONS_TEMP — Inventory item, organization, and serialization context, plus the transaction interface used when container operations generate material movements.
• DBMS_SQL, DUAL, and PLITBLM — Dynamic SQL execution, single-row selection, and PL/SQL index-by table utility support.

Usage Notes

WMS_CONTAINER2_PUB is invoked primarily when warehouse operators perform LPN-centric actions in the WMS mobile and desktop flows, and when integration code must manipulate container hierarchies outside the standard UI. The pick-and-drop validation path is the most frequently exercised entry point, since it gates every LPN-driven pick confirmation; the VALIDATE_PICK_DROP_LPN and DEFAULT_PICK_DROP_LPN procedures are reached from the WMS pick-and-drop forms and mobile pages before the transaction commits. Because the package is a PUB API, callers should follow the standard EBS API convention: initialize the message list, supply a valid p_api_version, and inspect x_return_status, x_msg_count, and x_msg_data after every call. ETRM records show the package is referenced by zero other database packages, confirming it is an externally consumed API rather than an internal dependency. Customizations should invoke it through the APPS synonym, never against the underlying WMS base tables directly, to preserve hierarchy and history integrity.