Results for “mtl_available_inventory_temp”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

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:

• SOURCE_LINE_ID and MOVE_ORDER_LINE_ID — surrogate-style identifiers that tie a staged row back to its originating document line (for example, a move order line).
• ORGANIZATION_ID, INVENTORY_ITEM_ID, LOCATOR_ID, SUBINVENTORY_CODE — the core warehouse addressing tuple identifying where the candidate stock resides.
• TRANSACTION_TEMP_ID and TRANSACTION_ACTION_ID — correlate the staged row with the pending transaction and the action being attempted.
• LOT_NUMBER, LOT_CREATED, LOT_EXPIRATION_DATE, GRADE_CODE, REVISION, SERIAL_NUMBER — lot, grade, revision, and serial attributes used for lot/serial control and FIFO/FEFO selection.
• ONHAND_QTY, SECONDARY_ONHAND_QTY, TRANSACTION_QTY, SECONDARY_TRANSACTION_QTY, TRANSACTION_UOM, SECONDARY_UOM — quantity and UOM pairs that drive allocation and dual-UOM validation.
• COST_GROUP_ID, RESERVATION_ID, TRANSACTION_SOURCE_TYPE_ID, LPN_ID, WMS_RULE_ID — costing, reservation, source-type, license plate, and warehouse-rule references.
• ORDER_BY, ORDER_BY_STRING, CONSIST_STRING — sort/consistency metadata the inventory engine uses to rank candidates and enforce consistency during selection.

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.