Results for “container_revision”
26 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
MTL_PHYSICAL_ADJUSTMENTS_VIEW is an APPS-owned reporting view in the Oracle E-Business Suite Inventory (INV) module. It presents physical inventory adjustments generated by item count activities, joining the base adjustment table to item master, license plate (LPN), and cost group data. The view is defined in both 12.1.1 and 12.2.2 and carries a VALID status in the ETRM repository. Its purpose is to provide a denormalized, query-ready record of each physical count adjustment, including quantities, valuation, approval status, revision, lot, serial, subinventory, locator, and container (LPN) context. Because it already resolves the item description, primary and secondary UOM codes, and cost group names, it is well suited for reporting, reconciliation, and integration extracts without requiring multiple joins on the caller side.
The view is particularly relevant to warehouse operations where adjustments must be traced back to the parent or outermost LPN that contained the counted item. Columns such as CONTAINER_SERIAL_NUMBER, CONTAINER_LOT_NUMBER, CONTAINER_REVISION, and the container cost group fields expose the identity of the container item, which is critical when a count is performed against a nested LPN hierarchy in a WMS-enabled organization.
Underlying Base Objects
The view is defined over four referenced base objects, all accessed through APPS synonyms:
- MTL_PHYSICAL_ADJUSTMENTS — the driving table, aliased M, holding one row per adjustment with organization, physical inventory, item, quantities, approval status, and LPN references.
- MTL_SYSTEM_ITEMS — aliased MSI, supplying item description, primary UOM, and secondary UOM.
- WMS_LICENSE_PLATE_NUMBERS — aliased twice (LPN1 for the parent LPN, LPN2 for the outermost LPN), providing license plate number, container item, revision, lot, serial, and cost group.
- CST_COST_GROUPS — aliased twice (CG for the adjustment cost group, CG2 for the container cost group), supplying cost group names.
The join between MTL_PHYSICAL_ADJUSTMENTS and MTL_SYSTEM_ITEMS is an inner join on ORGANIZATION_ID and INVENTORY_ITEM_ID. All other joins are outer joins (note the (+) operator on the LPN and cost group predicates), so adjustments without an associated parent LPN, outermost LPN, or cost group are still returned. This design preserves the adjustment as the primary grain.
Key Columns
- ORGANIZATION_ID, PHYSICAL_INVENTORY_ID, ADJUSTMENT_ID — keys identifying the inventory organization, the physical inventory event, and the specific adjustment.
- APPROVAL_STATUS — NVL-applied to zero, indicating whether the adjustment has been approved.
- INVENTORY_ITEM_ID, ITEM_DESCRIPTION, UOM, REVISION, LOT_NUMBER, SERIAL_NUMBER — the counted item and its identifying attributes.
- SYSTEM_QUANTITY, COUNT_QUANTITY, ADJUSTMENT_QUANTITY — the system on-hand, the physically counted quantity, and the resulting difference.
- VALUE, ACTUAL_COST, PCT — the adjustment value (cost times quantity, rounded to five places) and the percentage variance versus the system quantity.
- PARENT_LPN_ID, PARENT_LPN, OUTERMOST_LPN_ID, OUTERMOST_LPN — the LPN hierarchy associated with the adjustment.
- CONTAINER_ITEM_ID, CONTAINER_REVISION, CONTAINER_LOT_NUMBER, CONTAINER_SERIAL_NUMBER — attributes of the parent LPN's container item. CONTAINER_SERIAL_NUMBER is the column implied by the user's search term and identifies the serial number of the container holding the counted item.
- COST_GROUP_ID, COST_GROUP_NAME, CONTAINER_COST_GROUP_ID, CONTAINER_COST_GROUP_NAME — cost grouping for both the adjustment and its container.
- SECONDARY_UOM_CODE, SECONDARY_SYSTEM_QTY, SECONDARY_COUNT_QTY, SECONDARY_ADJUSTMENT_QTY — dual UOM support for organizations using secondary units of measure.
- CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard audit columns.
Common Use Cases and Queries
Typical uses include reconciliation of count results against system on-hand, approval tracking, variance analysis by percentage, and extraction feeding external warehouse or finance systems. The following query lists physical adjustments for a given organization ordered by value:
- SELECT adjustment_id, physical_inventory_id, item_description, uom, system_quantity, count_quantity, adjustment_quantity, value, pct FROM mtl_physical_adjustments_view WHERE organization_id = :org_id ORDER BY value DESC;
To locate adjustments tied to a container by serial number, referencing the searched column:
- SELECT adjustment_id, item_description, container_item_id, container_lot_number, container_serial_number, parent_lpn, outermost_lpn FROM mtl_physical_adjustments_view WHERE organization_id = :org_id AND container_serial_number = :serial_number;
To report only approved adjustments with secondary quantities:
- SELECT adjustment_id, item_description, secondary_uom_code, secondary_system_qty, secondary_count_qty, secondary_adjustment_qty FROM mtl_physical_adjustments_view WHERE organization_id = :org_id AND approval_status = 1;
-
Physical inventory adjustments created by Item Counts.
APPS.MTL_PHYSICAL_ADJUSTMENTS_VIEW·↳ CST_COST_GROUPS·↳ MTL_PHYSICAL_ADJUSTMENTS·↳ MTL_SYSTEM_ITEMS·Explore INV module →
-
Physical inventory adjustments created by Item Counts.
APPS.MTL_PHYSICAL_ADJUSTMENTS_VIEW·↳ CST_COST_GROUPS·↳ MTL_PHYSICAL_ADJUSTMENTS·↳ MTL_SYSTEM_ITEMS·Explore INV module →
-
10SC ONLY
APPS.MTL_PHYSICAL_ADJUSTMENTS_V·↳ CST_COST_GROUPS·↳ MTL_PHYSICAL_ADJUSTMENTS·↳ MTL_SYSTEM_ITEMS·Explore INV module →
-
View based on table MTL_PHYSICAL_INVENTORY_TAGS. Has information for all the Physical Inventory tags.
APPS.MTL_PHYSICAL_INVENTORY_TAGS_V·↳ CST_COST_GROUPS·↳ MTL_PHYSICAL_INVENTORY_TAGS·↳ MTL_SYSTEM_ITEMS·Explore INV module →
-
View based on table MTL_PHYSICAL_INVENTORY_TAGS. Has information for all the Physical Inventory tags.
APPS.MTL_PHYSICAL_INVENTORY_TAGS_V·↳ CST_COST_GROUPS·↳ MTL_PHYSICAL_INVENTORY_TAGS·↳ MTL_SYSTEM_ITEMS·Explore INV module →
-
10SC ONLY
APPS.MTL_PHYSICAL_ADJUSTMENTS_V·↳ CST_COST_GROUPS·↳ MTL_PHYSICAL_ADJUSTMENTS·↳ MTL_SYSTEM_ITEMS·Explore INV module →
-
10SC ONLY
APPS.MTL_CYCLE_COUNT_ENTRIES_V·↳ CST_COST_GROUPS·↳ MFG_LOOKUPS·↳ MTL_ABC_CLASSES·Explore INV module →
-
10SC ONLY
APPS.MTL_CYCLE_COUNT_ENTRIES_V·↳ CST_COST_GROUPS·↳ MFG_LOOKUPS·↳ MTL_ABC_CLASSES·Explore INV module →
-
eTRM - INV Tables and Views 12.1.1
-
eTRM - INV Tables and Views 12.2.2