Results for “mtl_physical_adjustments_view”

20 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

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;