Search Results query_quantities

Overview

INV_QUANTITY_TREE_PUB is the public PL/SQL package in the Oracle EBS Inventory (INV) application that manages the in-memory quantity tree data structure. The quantity tree is a hierarchical representation of inventory quantities across item, revision, lot, subinventory, locator, and LPN levels. It maintains the interrelationship between quantity on hand (qoh), reservable quantity on hand (rqoh), quantity reserved (qr), quantity suggested (qs), available to transact (att), and available to reserve (atr) at each node of the tree.

The package serves as the supported public interface (API classification: PUB) to the underlying private package INV_QUANTITY_TREE_PVT. It exposes mode constants that determine how a tree is instantiated: reservation mode (g_reservation_mode), transaction mode (g_transaction_mode), loose-only mode (g_loose_only_mode), and no-LPN-reservations mode (g_no_lpn_rsvs_mode). Quantity type constants (g_qoh, g_qr_same_demand, g_qs_rsv, g_qs_txn) identify which balance a caller intends to manipulate when invoking quantity update operations. Together these constants allow callers to allocate, reserve, suggest, and transact against inventory quantities while keeping the in-memory tree consistent with the corresponding database tables.

Key Procedures and Functions

Six documented procedures and functions are exposed by this package:

• CLEAR_QUANTITY_CACHE — Deletes all quantity trees held in memory. Per the documented source, this procedure should be called when a session issues a rollback. Failure to do so leaves the cached trees out of sync with the underlying database tables, which can produce incorrect availability, reservation, or transaction results in subsequent operations.
• QUERY_QUANTITIES — Retrieves quantity values from a quantity tree for the requested level. It is the read path used by callers to inspect on-hand, reservable, reserved, suggested, available-to-transact, and available-to-reserve balances.
• UPDATE_QUANTITIES — Changes quantities at a given tree level. Callers specify the quantity type constant (quantity on hand, quantity reserved for the same demand source used to create the tree, or suggested quantity for reservation/transaction) to indicate which balance is being adjusted.
• DO_CHECK — Performs consistency validation, ensuring that the in-memory tree state reconciles correctly before or after quantity manipulation.

The package also inherits creation-side behavior from INV_QUANTITY_TREE_PVT, including the create_tree() entry point referenced in the documented source header, which accepts the mode constants defined here.

Tables Accessed

The documented table reference for this package is MTL_PARAMETERS, accessed via its APPS synonym. MTL_PARAMETERS holds inventory organization-level setup and control flags—such as negative inventory tolerances and reservation controls—that govern how quantity trees must be built and validated. Reading these parameters ensures that quantity computations honor organization-specific inventory policies. Broader underlying quantity data handled by the private package is reflected through the tree structure rather than direct public-table access from this PUB layer.

Usage Notes

INV_QUANTITY_TREE_PUB is a core internal API referenced by 71 other packages in the EBS Inventory suite. It is typically invoked indirectly—through reservation, pick, ship, and material transaction flows—rather than called directly by end users. Oracle Forms and concurrent programs that manipulate inventory quantities ultimately drive these procedures.

The most important integration rule concerns CLEAR_QUANTITY_CACHE. Any session that creates a quantity tree and subsequently executes a rollback must call this procedure before reusing the tree; otherwise stale, uncommitted quantities remain cached in memory and diverge from database truth. This is especially relevant in multi-row processing where some rows commit and others roll back within a single session. Custom code that calls the quantity tree API should therefore wrap quantity operations so that CLEAR_QUANTITY_CACHE is issued on every rollback path. Because the package maintains session-level memory state, concurrent sessions do not share trees, and no explicit synchronization between sessions is required.