Results for “secondary_uom_code”

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

Overview

The APPS.MTL_ONHAND_LOT_V view presents lot-level on-hand inventory balances within Oracle E-Business Suite Release 12.1.1 and 12.2.2. As documented in the Oracle ETRM repository, the object carries the product classification INV (Inventory) and a description of "10SC ONLY", indicating that the view is delivered for a specific supply chain configuration rather than as a general-purpose inventory reporting object. From a technical standpoint, the view aggregates on-hand quantity records by organization, item, revision, subinventory, locator, and lot number, joining the transactional on-hand detail to item master, lot master, organization, and parameter data. Its principal value lies in exposing lot-attribute data — most notably grade code, expiration date, and secondary quantity — that is not available directly from the standard on-hand quantities table. Users who search for grade_code in the context of inventory reporting are typically routed to this view because GRADE_CODE appears here as a projected column obtained from MTL_LOT_NUMBERS.

Underlying Base Objects

The documented base objects referenced by this view are MTL_ONHAND_QUANTITIES_DETAIL, MTL_SYSTEM_ITEMS_KFV, MTL_PARAMETERS, MTL_LOT_NUMBERS, and HR_ORGANIZATION_UNITS, with supporting dependencies on the HR_GENERAL and HR_SECURITY packages. The view is defined over these as synonyms, joined on ORGANIZATION_ID and INVENTORY_ITEM_ID. MTL_ONHAND_QUANTITIES_DETAIL supplies the quantity facts (PRIMARY_TRANSACTION_QUANTITY and SECONDARY_TRANSACTION_QUANTITY), which are summed to produce TOTAL_QOH and SECONDARY_TOTAL. MTL_SYSTEM_ITEMS_KFV provides the padded concatenated item segments, description, primary and secondary UOM codes, and the lot, locator, and serial control flags. MTL_LOT_NUMBERS contributes lot-level attributes such as EXPIRATION_DATE and GRADE_CODE. MTL_PARAMETERS supplies ORGANIZATION_CODE, while HR_ORGANIZATION_UNITS provides ORGANIZATION_NAME. Because HR_ORGANIZATION_UNITS is protected by HR security, access to the view is implicitly constrained by the organization security profile of the connecting user. Notably, the GRADE_CODE and SECONDARY_UOM_CODE/SECONDARY_TOTAL columns are wrapped in INVCONV customization markers (RNRAO), indicating they were added through an inventory conversion or localization enhancement.

Key Columns

• ORGANIZATION_ID / ORGANIZATION_CODE / ORGANIZATION_NAME — inventory organization identifiers and descriptive labels.
• INVENTORY_ITEM_ID / PADDED_CONCATENATED_SEGMENTS / ITEM_DESCRIPTION — item identifiers and reporting-friendly item keys.
• REVISION, SUBINVENTORY_CODE, LOCATOR_ID, LOT — the disaggregation dimensions of the aggregated balance.
• TOTAL_QOH — summed primary transaction quantity across the grouped dimensions.
• EXPIRATION_DATE — lot expiration date sourced from MTL_LOT_NUMBERS.
• GRADE_CODE — lot grade attribute, the column most commonly sought when querying this view.
• SECONDARY_UOM_CODE / SECONDARY_TOTAL — secondary-unit UOM and summed secondary quantity for dual-UOM inventory.
• ITEM_LOT_CONTROL, ITEM_LOCATOR_CONTROL, ITEM_SERIAL_CONTROL — item-level control flags indicating lot, locator, and serial tracking behavior.
• PRIMARY_UOM_CODE — the item's primary unit of measure.

Common Use Cases and Queries

Typical uses include lot balance reporting, expiration and grade analysis, and feeding lot-attribute data into conversions or integrations that the base on-hand table cannot satisfy directly. A representative query retrieving graded lot balances is shown below.

• Lot balance by grade:

  SELECT organization_code, padded_concatenated_segments item,
         lot, grade_code, expiration_date, total_qoh
  FROM   apps.mtl_onhand_lot_v
  WHERE  organization_id = :org_id
  AND    grade_code IS NOT NULL;

• Expiring lots:

  SELECT organization_code, lot, expiration_date, total_qoh
  FROM   apps.mtl_onhand_lot_v
  WHERE  expiration_date < SYSDATE + 30
  AND    total_qoh > 0;

• Secondary quantity reconciliation:

  SELECT lot, primary_uom_code, total_qoh,
         secondary_uom_code, secondary_total
  FROM   apps.mtl_onhand_lot_v
  WHERE  secondary_uom_code IS NOT NULL;

Because the view performs a GROUP BY over MTL_ONHAND_QUANTITIES_DETAIL, it is best suited to balance-level reporting rather than transaction-level reconciliation; users requiring individual quantity rows should query the base detail table directly.