Results for “subinventory_code”

6 results




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

Overview

APPS.POS_MTL_ONHAND_LOT_KFV is a Purchasing (PO) module view in Oracle E-Business Suite 12.1.1 and 12.2.2 that presents on-hand inventory quantities at the lot level, combining quantity data with descriptive and key-flexfield (KFF) information. The view materializes the "lot" dimension of stock balances so that purchasing, receiving, and supply-chain users can report quantities by item, revision, subinventory, locator, and lot number without manually joining the transactional on-hand table to the many descriptive entities involved.

Because the view joins MTL_ONHAND_QUANTITIES to item, location, lot, and organization entities and returns concatenated key-flexfield segments, it is commonly embedded in custom reports, Discoverer workbooks, and integration extracts that require human-readable item, location, and organization identifiers rather than surrogate IDs. It is the lot-level companion to item/locator-level on-hand views and is frequently reached when a user searches for "mtl_onhand_quantities," since that base view contains no descriptive columns and no lot aggregation.

Underlying Base Objects

Per the documented ETRM 12.2.2 metadata, POS_MTL_ONHAND_LOT_KFV references the following objects:

The view is defined as a UNION of two branches. The first branch joins to MTL_ITEM_LOCATIONS_KFV and includes rows where LOCATOR_ID is not null; the second branch returns locator rows where a location flexfield is not applicable, exposing LOCATION as NULL and substituting a LOCATOR_ID of -1 via NVL. Both branches aggregate TRANSACTION_QUANTITY with SUM and use GROUP BY across the descriptive attributes.

Key Columns

  • ORGANIZATION_ID / ORGANIZATION_CODE / ORGANIZATION_NAME — the inventory organization and its descriptive identifiers.
  • INVENTORY_ITEM_ID / ITEM — surrogate item ID and the concatenated item flexfield segments.
  • ITEM_DESCRIPTION / PRIMARY_UOM_CODE — item description and unit of measure.
  • REVISION — item revision under which the on-hand balance is held.
  • TOTAL_QOH — summed transaction quantity, the aggregate on-hand for the grouping keys.
  • SUBINVENTORY_CODE — subinventory holding the stock.
  • LOCATOR_ID / LOCATION — locator surrogate and concatenated locator flexfield; LOCATOR_ID is forced to -1 where no locator applies.
  • LOT / EXPIRATION_DATE — lot number and, from MTL_LOT_NUMBERS, its expiration date.
  • ITEM_URL — sourced from item ATTRIBUTE14.

Common Use Cases and Queries

Typical uses include lot-level on-hand reporting, expiration-date monitoring, subinventory/locator reconciliation, and extracts feeding warehouse or planning systems. A representative query filters by organization and item:

  • SELECT organization_code, item, revision, subinventory_code, location, lot, expiration_date, total_qoh
  • FROM apps.pos_mtl_onhand_lot_kfv
  • WHERE organization_id = :org_id AND inventory_item_id = :item_id;

To identify expiring lots, callers order by or filter on EXPIRATION_DATE, for example WHERE expiration_date <= SYSDATE + 30. To summarize balances by subinventory, aggregate TOTAL_QOH while grouping on SUBINVENTORY_CODE. Because the view aggregates MTL_ONHAND_QUANTITIES, results reflect current on-hand balances; consumers requiring unfiltered detail or non-lot rows should query the base MTL_ONHAND_QUANTITIES view directly.