Search Results inv_quantity_tree_pvt




Overview
Explore in AI Chat

Overview

INV_QUANTITY_TREE_PVT is a private PL/SQL package body in the APPS schema that implements the high-availability quantity tree engine used by Oracle Inventory in EBS 12.1.1 and 12.2.2. The quantity tree is an in-memory cache of on-hand, reserved, and available balances held for a given inventory item, organization, and set of locators or lots. It allows Oracle Inventory forms, transaction processors, and the Material Workbench to validate availability and reservations against a cached hierarchy rather than repeatedly querying MTL_ONHAND_QUANTITIES_DETAIL. The package is classified as PVT, meaning it is an internal implementation package not intended as a public API; it is referenced by 81 other packages, most notably INV_RESERVATION_PUB, INV_TRANSACTION_API, and the Inventory transaction and reservation forms.

Key Procedures and Functions

  • CREATE_TREE — Builds the in-memory quantity tree for a specified item/organization/locator/lot combination, populating it from on-hand and reservation sources.
  • QUERY_TREE — Returns balance information (onhand, available, reserved) from the cached tree without database re-reads.
  • UPDATE_QUANTITIES and UPDATE_QUANTITIES_FOR_FORM — Apply incremental postings to the tree after a transaction, keeping cached balances synchronized with MTL_ONHAND_QUANTITIES_DETAIL; the form variant tailors updates for UI display.
  • CLEAR_QUANTITY_CACHE — Flushes the cached tree and forces a rebuild, typically after an invalidation event.
  • FREE_TREE, FREE_ALL — Release the memory structures created by CREATE_TREE.
  • MARK_ALL_FOR_REFRESH — Flags all cached trees for rebuilding when underlying data changes.
  • FIND_ROOTINFO, BACKUP_TREE, RESTORE_TREE — Locate the tree root, snapshot state, and restore it after failure or rollback.
  • LOCK_TREE, RELEASE_LOCK — Provide concurrency control using DBMS_LOCK so concurrent sessions cannot corrupt cached balances.
  • DO_CHECK, DO_CHECK_RELEASE_LOCKS — Validate availability for a proposed transaction, releasing locks on completion.
  • PREPARE_RESERVATION_QUANTITIES — Assembles balances required by the reservation engine before committing reservations.
  • GET_TOTAL_QOH — Returns total quantity on hand from the tree for a node.
  • DEMAND_SOURCE_EQUALS — Compares demand sources when evaluating available supply.
  • PRINT_TREE — Diagnostic utility that dumps tree contents for debugging.

Tables Accessed

The package reads and writes the core Inventory tables through APPS synonyms. On-hand and reservation facts come from MTL_ONHAND_QUANTITIES_DETAIL, MTL_RESERVATIONS, and MTL_RSV_QUANTITIES_TEMP. Transactional staging is supported by MTL_MATERIAL_TRANSACTIONS_TEMP, MTL_TRANSACTION_LOTS_TEMP, MTL_DO_CHECK_TEMP, and MTL_TXN_REQUEST_HEADERS. Location and lot hierarchy data come from MTL_ITEM_LOCATIONS, MTL_LOT_NUMBERS, MTL_SECONDARY_INVENTORIES, and MTL_PARAMETERS. Material status validation reads MTL_MATERIAL_STATUSES and MTL_MATERIAL_STATUSES_B. Master data is resolved through MTL_SYSTEM_ITEMS and MTL_SYSTEM_ITEMS_B. Supporting concurrent-safe access uses DBMS_LOCK and DBMS_UTILITY.

Usage Notes

INV_QUANTITY_TREE_PVT is invoked indirectly by Inventory forms, the Material Workbench, the transaction manager, reservation processing, and the discrete/WIP-related packages that depend on quantity tree balances. Custom code should not call this package directly because it is a private implementation object; the supported entry points are the public Inventory APIs such as INV_RESERVATION_PUB, INV_TRANSACTION_API, and related PUBLIC packages. It caches per-session state, so long-running sessions must ensure FREE_TREE, LOCK_RELEASE, or CLEAR_QUANTITY_CACHE are invoked appropriately to prevent stale balances. Because it is referenced by 81 packages, any change carries broad regression risk across Inventory, WIP, and Order Management flows.