Results for “revision_qty_control_code”

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

Overview

The MTL_QOH_REV_ALL_V view is an Inventory (INV) module database object owned by the APPS schema in Oracle E-Business Suite. It is documented in ETRM (E-Business Suite Technical Reference Manual) for releases 12.1.1 and 12.2.2 and its status is recorded as VALID. The ETRM metadata carries the terse description "10SC ONLY," indicating the view is scoped to a restricted functional or deployment context rather than being a general-purpose public interface used across all inventory flows. Regardless of that restricted labeling, the view exposes a useful cross-section of inventory data: for each organization and inventory item combination it returns the current revision, the item description, the revision quantity control code, and the primary unit of measure.

The view derives its name from its evident purpose: quantity-on-hand (QOH) enriched with revision data. It consolidates information found in the on-hand quantities tables with descriptive and control attributes defined at the master item level. This makes the view useful wherever a report, interface, or concurrent program needs to reconcile physical on-hand quantities against the revision and revision-control configuration of the item. Because it exposes the REVISION_QTY_CONTROL_CODE column explicitly, it is particularly relevant to revision-level quantity control behaviors, including lot and serial control logic applied at the revision level.

Underlying Base Objects

Per the documented metadata, MTL_QOH_REV_ALL_V is defined over three referenced base objects, each resolved through a SYNONYM in the APPS schema:

• MTL_ONHAND_QUANTITIES_DETAIL — holds on-hand quantity records by organization, inventory item, and revision.
• MTL_MATERIAL_TRANSACTIONS_TEMP — the temporary interface table for pending material transactions.
• MTL_SYSTEM_ITEMS — the master item definition table holding descriptive and control attributes such as item description, revision quantity control code, and primary UOM.

The view text performs a UNION of two SELECT statements. The first joins MTL_ONHAND_QUANTITIES_DETAIL to MTL_SYSTEM_ITEMS on INVENTORY_ITEM_ID and ORGANIZATION_ID. The second joins MTL_MATERIAL_TRANSACTIONS_TEMP to MTL_SYSTEM_ITEMS on the same keys. This structure means the view returns revision-level rows for both confirmed on-hand records and pending (unposted) transactions that reference items with revisions, provided the item join resolves.

Key Columns

• ORGANIZATION_ID — the inventory organization identifier; the primary partitioning key for multi-org inventory data.
• INVENTORY_ITEM_ID — the unique inventory item identifier, joined across the base tables.
• REVISION — the item revision from the on-hand or transaction source. This is the pivot column for revision-level analysis.
• DESCRIPTION — the item description sourced from MTL_SYSTEM_ITEMS (aliased B or D in the view text).
• REVISION_QTY_CONTROL_CODE — the control code defining how quantities are managed with respect to revision, exposed directly from MTL_SYSTEM_ITEMS. This is the column referenced by users searching on revision_qty_control_code.
• PRIMARY_UOM_CODE — the primary unit of measure of the item, also sourced from MTL_SYSTEM_ITEMS.

Common Use Cases and Queries

Typical scenarios include auditing revision-controlled items, validating revision quantities against pending transactions, and reporting on-hand by revision with descriptive attributes joined in a single query. A representative query is:

SELECT organization_id, inventory_item_id, revision, description, revision_qty_control_code, primary_uom_code FROM apps.mtl_qoh_rev_all_v WHERE organization_id = :org_id AND revision_qty_control_code IS NOT NULL;

Because the view mixes confirmed on-hand data with temporary transaction staging records, care should be taken to distinguish the two sources when the result is used for reconciliation; a separate join or additional filtering may be required to isolate only posted on-hand records.