Search Results secondary_transaction_qty

Overview

APPS.MTL_AVAILABLE_INVENTORY_V is a reporting and integration view in Oracle E-Business Suite 12.1.1 and 12.2.2 that exposes the aggregated contents of the temporary availability table used by the inventory transaction and reservation engines. Rather than presenting physical on-hand balances, the view summarizes pending material transactions staged in MTL_AVAILABLE_INVENTORY_TEMP, each of which represents an availability adjustment that the Inventory module uses to calculate which material is reserved, allotted, or available at a given locator, lot, subinventory, revision, and LPN.

The view joins the temporary staging records to lot master data, location key flexfield information, and transaction reason codes, producing a denormalized, human-readable result set. It is documented in ETRM with owner APPS and is defined over a mixture of synonyms, views, and a package function, reflecting the modular structure of the Inventory schema.

Because the underlying temporary table is volatile, the view is primarily intended for diagnostics, custom reporting, and integration scripts rather than as a transactional source of truth. It is especially relevant to implementers working with reservations, move orders, and WMS-directed picks.

Underlying Base Objects

The documented referenced base objects for this view are:

• MTL_AVAILABLE_INVENTORY_TEMP (SYNONYM) — the driving table, aliased PKLT, holding the individual availability adjustment rows.
• MTL_LOT_NUMBERS (SYNONYM) — aliased LOT, supplying lot number, creation date, expiration date, and grade.
• MTL_ITEM_LOCATIONS_KFV (VIEW) — aliased LOCT, the key flexfield view that renders the concatenated inventory location (locator) description.
• MTL_TRANSACTION_REASONS (SYNONYM) — aliased RSN, providing the transaction reason name.
• INV_PROJECT (PACKAGE) — invoked via INV_PROJECT.GET_LOCATOR to return the formatted locator string in the select list.

All joins are outer joins ((+)), which means availability rows are preserved even when the related lot, locator, or reason record is absent. This is important because pending availability rows may reference lots that have not yet been fully defined or reasons that are optional.

Key Columns

• LOCATOR_ID, SUBINVENTORY_CODE, LPN_ID — the physical location context of the availability row, including the license plate number when WMS is used.
• LOT_NUMBER, CREATION_DATE, EXPIRATION_DATE, GRADE_CODE — lot-level attributes for lot-controlled items.
• TRANSACTION_TEMP_ID — the key discriminator. A value of 0 causes the aggregation columns to return 0 instead of a summed value, per the DECODE in the view definition.
• TRANS_QTY — DECODE(PKLT.TRANSACTION_TEMP_ID, 0, 0, SUM(TRANSACTION_QTY)), the summed primary quantity available for the grouped key.
• TRANS_QTY2 — DECODE(PKLT.TRANSACTION_TEMP_ID, 0, 0, SUM(SECONDARY_TRANSACTION_QTY)), the summed secondary_transaction_qty, i.e., the quantity expressed in the secondary unit of measure.
• TRANSACTION_UOM, SECONDARY_UOM — the primary and secondary units of measure for the row.
• REVISION — the item revision, supporting revision-controlled items.
• REASON_ID, REASON_NAME — the transaction reason recorded against the availability row.
• ORDER_BY, WMS_RULE_ID — sequencing and WMS rule identifiers used to order and drive the availability allocation.

The user searching for secondary_transaction_qty should note that the source column exists only in MTL_AVAILABLE_INVENTORY_TEMP; the view exposes its summed form exclusively through the alias TRANS_QTY2.

Common Use Cases and Queries

The view is used to inspect what the inventory engine currently considers available, to reconcile reservation discrepancies, and to debug WMS-directed allocation. A typical query retrieves total primary and secondary quantities per subinventory and locator:

• SELECT subinventory_code, locator_id, lot_number, transaction_uom, secondary_uom, trans_qty, trans_qty2 FROM apps.mtl_available_inventory_v WHERE subinventory_code = 'STORES' ORDER BY order_by;
• SELECT locator_id, lpn_id, revision, SUM(trans_qty2) secondary_total FROM apps.mtl_available_inventory_v GROUP BY locator_id, lpn_id, revision;
• SELECT reason_name, COUNT(*) FROM apps.mtl_available_inventory_v GROUP BY reason_name;

Because TRANS_QTY2 returns 0 whenever TRANSACTION_TEMP_ID = 0, reporting queries that rely on the secondary quantity should filter or interpret these rows carefully. All queries must be executed within the APPS schema context or with appropriate synonyms and grants.