Results for “on_hand”
50+ results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
MTL_LOT_ONHAND_SUM_V is an APPS-owned database view in the Oracle E-Business Suite Inventory (INV) module. It is documented as a "Quantity view for Lot uom conversion use," meaning its primary design intent is to expose lot-level on-hand quantities aggregated in a form that supports unit-of-measure (UOM) conversion for lot-controlled items. The view consolidates on-hand balances held in MTL_ONHAND_QUANTITIES_DETAIL and enriches them with descriptive and status attributes drawn from organization, subinventory, locator, lot, item, and license plate sources. Because the view returns denormalized, human-readable values such as organization code, concatenated item and locator flexfield segments, item description, and status identifiers, it is well suited for reporting and integration scenarios where callers need summarized on-hand quantities by lot without navigating the full normalized data model.
In the 12.1.1 and 12.2.2 releases the object is registered in ETRM as VALID under the APPS schema. Its status is maintained as VALID, confirming it is a supported dictionary object available to custom reports, concurrent programs, and interfaces.
Underlying Base Objects
The view is defined over the following documented base objects, joined together in a single SELECT with an aggregate GROUP BY:
- MTL_PARAMETERS (synonym) — supplies organization-level context and ORGANIZATION_CODE.
- MTL_ONHAND_QUANTITIES_DETAIL (synonym) — the fact source; provides primary and secondary transaction quantities, revision, lot number, subinventory, locator, LPN, and item identifiers.
- MTL_ITEM_LOCATIONS_KFV (view) — key flexfield view supplying concatenated locator segments and the locator STATUS_ID.
- MTL_SECONDARY_INVENTORIES (synonym) — provides the subinventory-level STATUS_ID.
- MTL_LOT_NUMBERS (synonym) — joined by organization, item, and lot number to supply lot GRADE_CODE and lot STATUS_ID.
- MTL_SYSTEM_ITEMS_VL (view) — supplies item description, primary UOM code, and concatenated item segments.
- WMS_LICENSE_PLATE_NUMBERS (synonym) — supplies the license plate number for LPN-tracked inventory.
Most joins to locator, lot, and license plate tables are outer joins (+), so on-hand rows are preserved even when the corresponding descriptive record does not exist. The join to MTL_SECONDARY_INVENTORIES, by contrast, is an inner join on organization and subinventory name.
Key Columns
The SELECT list exposes the following important columns:
- ORGANIZATION_ID / ORGANIZATION_CODE — the inventory organization and its code.
- SUBI — abbreviated reference to SUBINVENTORY_CODE, the subinventory holding the stock.
- LOCATOR_ID / LOCATOR — the locator identifier and its concatenated flexfield segments.
- INVENTORY_ITEM_ID / ITEM / ITEM_DESCRIPTION — item identity keyed by ID, with concatenated segments and description.
- REVISION — the item revision for revision-controlled items.
- UOM — the item's primary UOM code (PRIMARY_UOM_CODE).
- ON_HAND — the summed PRIMARY_TRANSACTION_QUANTITY.
- LPN_ID / LPN — license plate identifier and number for LPN-tracked stock.
- LOT_NUMBER — the lot for which quantities are summarized.
- SUBINVENTORY_STATUS_ID, LOCATOR_STATUS_ID, LOT_STATUS_ID — status identifiers at subinventory, locator, and lot levels respectively. In particular, LOCATOR_STATUS_ID reflects the status carried by the locator record from MTL_ITEM_LOCATIONS_KFV.
- SECONDARY_UOM_CODE / SECONDARY_ONHAND — the secondary UOM and the summed secondary transaction quantity.
- GRADE_CODE — the grade associated with the lot.
Common Use Cases and Queries
Typical uses include lot-level on-hand reporting, UOM conversion validation, and integration extracts keyed by organization, item, and lot. A representative query filtering on locator status follows:
SELECT organization_code, subinventory_code, locator, item, lot_number, on_hand, uom FROM apps.mtl_lot_onhand_sum_v WHERE locator_status_id = :p_locator_status_id AND organization_id = :p_org_id;SELECT item, lot_number, SUM(on_hand) FROM apps.mtl_lot_onhand_sum_v WHERE organization_id = :p_org_id GROUP BY item, lot_number;
Because the view already aggregates quantities and resolves status IDs at three levels, it is a convenient single source when reporting or interfacing lot-level inventory without writing the underlying multi-table joins.
-
View: MTL_LOT_ONHAND_SUM_V 12.1.1
The view is used to get total onhand of a lot number.
APPS.MTL_LOT_ONHAND_SUM_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_ONHAND_QUANTITIES_DETAIL·Explore INV module →
-
View: MTL_RCV_MWB_ONHAND_V 12.2.2
MTL_RCV_MWB_ONHAND_V is used in the material workbench to query the on-hand quantities of materials that are in receiving.
APPS.MTL_RCV_MWB_ONHAND_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_PARAMETERS·↳ MTL_SYSTEM_ITEMS_VL·Explore INV module →
-
View: MTL_RCV_MWB_NEW_OH_V 12.2.2
APPS.MTL_RCV_MWB_NEW_OH_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_PARAMETERS·Explore INV module →
-
View: MTL_ONHAND_NEW_LPN_V 12.1.1
LPN content view
APPS.MTL_ONHAND_NEW_LPN_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_ONHAND_QUANTITIES_DETAIL·Explore INV module →
-
LPN content view for Material Workbench
APPS.MTL_ONHAND_NEW_LPN_MWB_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_ONHAND_QUANTITIES_DETAIL·Explore INV module →
-
View: MTL_RCV_MWB_ONHAND_V 12.1.1
MTL_RCV_MWB_ONHAND_V is used in the material workbench to query the on-hand quantities of materials that are in receiving.
APPS.MTL_RCV_MWB_ONHAND_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_PARAMETERS·↳ MTL_SYSTEM_ITEMS_VL·Explore INV module →
-
View: MTL_ONHAND_TOTAL_MWB_V 12.2.2
Onhand Total View for the Material Work Bench
APPS.MTL_ONHAND_TOTAL_MWB_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_ONHAND_QUANTITIES_DETAIL·Explore INV module →
-
View: MTL_ONHAND_SERIAL_V 12.2.2
APPS.MTL_ONHAND_SERIAL_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_PARAMETERS·Explore INV module →
-
View: MTL_ONHAND_TOTAL_V 12.2.2
APPS.MTL_ONHAND_TOTAL_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_ONHAND_QUANTITIES_DETAIL·Explore INV module →
-
View: MTL_LOT_ONHAND_SUM_V 12.2.2
Quantity view for Lot uom conversion use
APPS.MTL_LOT_ONHAND_SUM_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_ONHAND_QUANTITIES_DETAIL·Explore INV module →
-
LPN content view for Material Workbench
APPS.MTL_ONHAND_NEW_LPN_MWB_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_ONHAND_QUANTITIES_DETAIL·Explore INV module →
-
View: MTL_ONHAND_NEW_LPN_V 12.2.2
LPN content view
APPS.MTL_ONHAND_NEW_LPN_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_ONHAND_QUANTITIES_DETAIL·Explore INV module →
-
Onhand Serial View for the Material Work Bench
APPS.MTL_ONHAND_SERIAL_MWB_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_PARAMETERS·Explore INV module →
-
View: MTL_ONHAND_TOTAL_MWB_V 12.1.1
Onhand Total View for the Material Work Bench
APPS.MTL_ONHAND_TOTAL_MWB_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_ONHAND_QUANTITIES_DETAIL·Explore INV module →
-
View: MTL_ONHAND_SERIAL_V 12.1.1
APPS.MTL_ONHAND_SERIAL_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_PARAMETERS·Explore INV module →
-
View: MTL_ONHAND_DUMMY_V 12.1.1
The view is used to show the data on material workbench form.
-
View: MTL_ONHAND_DUMMY_V 12.2.2
-
View: MTL_ONHAND_LPN_MWB_V 12.1.1
LPN content view for Material Workbench
APPS.MTL_ONHAND_LPN_MWB_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_PARAMETERS·Explore INV module →
-
View: MTL_ONHAND_LPN_MWB_V 12.2.2
LPN content view for Material Workbench
APPS.MTL_ONHAND_LPN_MWB_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_PARAMETERS·Explore INV module →
-
View: MTL_ONHAND_LPN_V 12.1.1
This view contains information about packed material that resides in LPNs.
APPS.MTL_ONHAND_LPN_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_PARAMETERS·Explore INV module →
-
MTL_RCV_SERIAL_MWB_OH_V is a new view which needs to be used for querying data when the user chooses subinventory type of Receiving and user has entered serials and grade code in the Find window, or has entered grade in the Find window and
APPS.MTL_RCV_SERIAL_MWB_OH_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_PARAMETERS·Explore INV module →
-
MTL_RCV_SERIAL_MWB_OH_V is a new view which needs to be used for querying data when the user chooses subinventory type of Receiving and user has entered serials and grade code in the Find window, or has entered grade in the Find window and
APPS.MTL_RCV_SERIAL_MWB_OH_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_PARAMETERS·Explore INV module →
-
View: MTL_ONHAND_LPN_V 12.2.2
This view contains information about packed material that resides in LPNs.
APPS.MTL_ONHAND_LPN_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_PARAMETERS·Explore INV module →
-
View: MTL_RCV_MWB_NEW_OH_V 12.1.1
MTL_RCV_MWB_NEW_OH_V is used in the material workbench to query the on-hand quantities of materials that are in receiving.
APPS.MTL_RCV_MWB_NEW_OH_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_PARAMETERS·Explore INV module →
-
Onhand Serial View for the Material Work Bench
APPS.MTL_ONHAND_SERIAL_MWB_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_PARAMETERS·Explore INV module →
-
View: MTL_RCV_SERIAL_OH_V 12.2.2
MTL_RCV_SERIAL_OH_V is used in the material workbench to query the on-hand quantities of materials that are in receiving and are serial controlled.
APPS.MTL_RCV_SERIAL_OH_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_PARAMETERS·Explore INV module →
-
View: MTL_RCV_SERIAL_OH_V 12.1.1
MTL_RCV_SERIAL_OH_V is used in the material workbench to query the on-hand quantities of materials that are in receiving and are serial controlled.
APPS.MTL_RCV_SERIAL_OH_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_PARAMETERS·Explore INV module →
-
View: MTL_ONHAND_TOTAL_V 12.1.1
APPS.MTL_ONHAND_TOTAL_V·↳ MTL_ITEM_LOCATIONS_KFV·↳ MTL_LOT_NUMBERS·↳ MTL_ONHAND_QUANTITIES_DETAIL·Explore INV module →
-
VIEW: APPS.MTL_ONHAND_LPN_V 12.1.1
-
VIEW: APPS.MTL_ONHAND_LPN_V 12.2.2
-
VIEW: APPS.MTL_ONHAND_LPN_V 12.1.1