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:

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.