Search Results mtl_available_inventory_temp




Overview
Explore in AI Chat

Overview

MTL_AVAILABLE_INVENTORY_TEMP is an Oracle Inventory (INV) schema table used within Oracle EBS 12.1.1 and 12.2.2. It serves as a transient staging structure that supports the Available to Promise (ATP), picking, and material movement processes. Rows are inserted into this table to represent candidate inventory — lots, locators, revisions, and quantities — that the system evaluates before committing an actual inventory transaction. Because its contents are short-lived, it is best understood as a working area rather than a permanent transaction ledger.

Under a heuristic Data Vault classification, this object behaves as a link table: it does not own a persistent descriptive natural key and exists primarily to associate warehouse entities (items, organizations, locators, lots, serials) with transactional intents and reservations. It should be treated as a modeling suggestion only, since the table is a physical staging artifact rather than a deliberately modeled vault structure.

Key Information Stored

The documented 12.2.2 schema contains 29 columns. The most operationally significant are:

No surrogate primary key is documented in the supplied metadata; the meaningful uniqueness typically derives from the combination of the originating source line and the inventory addressing columns rather than a single business key.

Common Use Cases and Queries

This table is most often inspected when diagnosing ATP, picking, or move-order failures. A typical supporting query joins the staging rows to their source transaction:

  • ATP/picking diagnostics: list staged candidates for a given item and organization to see which lots, locators, or serials the system considered.
  • Quantity reconciliation: compare ONHAND_QTY with TRANSACTION_QTY to identify shortfalls or over-allocations.
  • Reservation tracing: join on RESERVATION_ID to confirm whether candidates are already reserved.
  • Lot/serial audit: filter on LOT_NUMBER, SERIAL_NUMBER, or LOT_EXPIRATION_DATE for traceability.

A representative pattern is SELECT organization_id, inventory_item_id, locator_id, lot_number, onhand_qty, transaction_qty FROM mtl_available_inventory_temp WHERE move_order_line_id = :line_id ORDER BY order_by;. Because the table is transient, queries should always be scoped by source or transaction identifiers and run promptly, as rows may be purged by subsequent processing.

Related Objects

The documented foreign key relationships connect this table to the following dependent objects:

  • MTL_TXN_SOURCE_TYPES — joined via TRANSACTION_SOURCE_TYPE_ID, defining the origin of the staged transaction.
  • CST_COST_GROUPS — joined via COST_GROUP_ID, providing costing context for the candidates.
  • MTL_RESERVATIONS — joined via RESERVATION_ID, linking staged rows to existing reservations.

In practice it also interacts with the core INV entities referenced by its other columns — MTL_SYSTEM_ITEMS_B (via INVENTORY_ITEM_ID), MTL_ITEM_LOCATIONS (via LOCATOR_ID), MTL_LOT_NUMBERS, and the move-order tables implied by MOVE_ORDER_LINE_ID — and it is consumed by the inventory transaction and ATP APIs during allocation processing.