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:
- MTL_ONHAND_QUANTITIES (synonym) — the core quantity source, aliased A; supplies TRANSACTION_QUANTITY, REVISION, SUBINVENTORY_CODE, LOCATOR_ID, and LOT_NUMBER.
- MTL_SYSTEM_ITEMS_KFV (synonym) — item master key flexfield view, aliased B; supplies CONCATENATED_SEGMENTS (ITEM), DESCRIPTION, PRIMARY_UOM_CODE, and ATTRIBUTE14.
- MTL_ITEM_LOCATIONS_KFV (view) — locator key flexfield view, aliased F; supplies CONCATENATED_SEGMENTS (LOCATION).
- MTL_LOT_NUMBERS (synonym) — lot master, aliased D; supplies EXPIRATION_DATE.
- MTL_PARAMETERS (synonym) — organization parameters, aliased C; supplies ORGANIZATION_CODE.
- HR_ORGANIZATION_UNITS (view), with HR_GENERAL (package) and HR_SECURITY (package) — aliased E; supplies the organization NAME.
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_qohFROM apps.pos_mtl_onhand_lot_kfvWHERE 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.
-
View: POS_MTL_ONHAND_LOT_KFV 12.2.2
APPS.POS_MTL_ONHAND_LOT_KFV·↳ HR_GENERAL·↳ HR_ORGANIZATION_UNITS·↳ HR_SECURITY·Explore PO module →
-
APPS.POS_MTL_ONHAND_LOCATOR_KFV·↳ HR_GENERAL·↳ HR_ORGANIZATION_UNITS·↳ HR_SECURITY·Explore PO module →
-
APPS.POS_MTL_ONHAND_LOCATOR_KFV·↳ HR_GENERAL·↳ HR_ORGANIZATION_UNITS·↳ HR_SECURITY·Explore PO module →
-
View: POS_MTL_ONHAND_SUB_KFV 12.2.2
APPS.POS_MTL_ONHAND_SUB_KFV·↳ HR_GENERAL·↳ HR_ORGANIZATION_UNITS·↳ HR_SECURITY·Explore PO module →
-
View: POS_MTL_ONHAND_LOT_KFV 12.1.1
APPS.POS_MTL_ONHAND_LOT_KFV·↳ HR_GENERAL·↳ HR_ORGANIZATION_UNITS·↳ HR_SECURITY·Explore PO module →
-
View: POS_MTL_ONHAND_SUB_KFV 12.1.1
APPS.POS_MTL_ONHAND_SUB_KFV·↳ HR_GENERAL·↳ HR_ORGANIZATION_UNITS·↳ HR_SECURITY·Explore PO module →