Results for “mtl_available_inventory_v”
20 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
APPS.MTL_AVAILABLE_INVENTORY_V is a reporting view in the Oracle E-Business Suite Inventory (INV) module that exposes a curated snapshot of select available inventory. It surfaces locator-, lot-, and subinventory-level availability information together with transaction quantities that are relevant to the Inventory transaction processing workflow. The view is defined in the APPS schema and is documented as VALID in ETRM 12.2.2 (and available in the 12.1.1 code line).
Its primary role is to present the working contents of the available inventory temporary data — the staged set of rows produced while Inventory determines what is available for a given item, subinventory, and locator — in a readable, denormalized form. Because it joins temporary locator data to lot master attributes, locator key flexfield segments, and transaction reasons, it is commonly used in custom reports, concurrent programs, and integration extracts that need to display available quantity alongside lot, revision, and reason context. It is not a general ledger or costing view; it is a transactional availability view tied to the Inventory transaction engine.
Underlying Base Objects
Per the documented ETRM metadata, the view is defined over the following referenced base objects:
MTL_AVAILABLE_INVENTORY_TEMP(SYNONYM) — the driving temporary table (PKLT) holding the working availability rows.MTL_LOT_NUMBERS(SYNONYM) — joined with an outer join on lot number, item, and organization to supply lot creation and expiration attributes.MTL_ITEM_LOCATIONS_KFV(VIEW) — the key flexfield view providing the concatenated locator segments via an outer join onLOCATOR_ID.MTL_TRANSACTION_REASONS(SYNONYM) — outer-joined onREASON_IDto resolve the reason name.INV_PROJECT.GET_LOCATOR(PACKAGE function) — called in the SELECT list and GROUP BY to resolve the locator description/identifier.
All joins from the driving temporary table to the lot, locator, and reason objects are outer joins, ensuring that availability rows are not lost when a matching lot, locator descriptor, or reason is absent. The query aggregates quantities with SUM grouped by locator, lot, subinventory, grade, reason, UOM, revision, ordering, and WMS rule attributes, and returns rows ordered by ORDER_BY.
Key Columns
LOCATOR_ID,CONCATENATED_SEGMENTS— locator identifier and its concatenated key flexfield description.LOT_NUMBER,CREATION_DATE,EXPIRATION_DATE— lot identity and its lifecycle dates.SUBINVENTORY_CODE— the subinventory holding the stock.GRADE_CODE,REVISION— grade and revision of the on-hand material.TRANSACTION_TEMP_ID— internal temporary transaction identifier used to control aggregation.REASON_ID,REASON_NAME— transaction reason code and resolved name.TRANS_QTY— sum ofTRANSACTION_QTY, the primary quantity. It is forced to 0 whenTRANSACTION_TEMP_IDis 0.TRANS_QTY2— the column referenced in the user's search; it isDECODE(PKLT.TRANSACTION_TEMP_ID, 0, 0, SUM(SECONDARY_TRANSACTION_QTY)), i.e. the summed secondary quantity, also suppressed to 0 when the temporary transaction id is 0.TRANSACTION_UOM,SECONDARY_UOM— primary and secondary units of measure for the quantities.ORDER_BY,WMS_RULE_ID— ordering sequence and the warehouse management rule identifier.
Common Use Cases and Queries
Typical uses include displaying available stock during a transaction, feeding lot-controlled picking or put-away logic, and producing custom availability reports where dual-UOM quantities are required.
SELECT subinventory_code,
locator_id,
lot_number,
trans_qty,
trans_qty2,
transaction_uom,
secondary_uom
FROM apps.mtl_available_inventory_v
WHERE order_by IS NOT NULL;
Because TRANS_QTY2 reflects secondary quantities for dual-UOM items, reporting filters frequently isolate rows where TRANS_QTY2 > 0. Note that the view reads from a temporary table, so meaningful results depend on the inventory process having populated MTL_AVAILABLE_INVENTORY_TEMP; querying it outside that context may return only rows generated by the current session- or process-level transaction.
-
View of select available inventory
APPS.MTL_AVAILABLE_INVENTORY_V·↳ INV_PROJECT·↳ MTL_AVAILABLE_INVENTORY_TEMP·↳ MTL_ITEM_LOCATIONS_KFV·Explore INV module →
-
View of select available inventory
APPS.MTL_AVAILABLE_INVENTORY_V·↳ INV_PROJECT·↳ MTL_AVAILABLE_INVENTORY_TEMP·↳ MTL_ITEM_LOCATIONS_KFV·Explore INV module →
-
12.1.1 FND Design Data 12.1.1
-
12.2.2 FND Design Data 12.2.2
-
eTRM - INV Tables and Views 12.2.2
-
eTRM - INV Tables and Views 12.1.1
-
12.2.2 DBA Data 12.2.2
-
12.1.1 DBA Data 12.1.1
-
eTRM - INV Tables and Views 12.1.1
-
eTRM - INV Tables and Views 12.2.2