Search Results mtl_physical_adjustments




Overview
Explore in AI Chat

Overview

The MTL_PHYSICAL_ADJUSTMENTS table is a core transactional data store within the Oracle E-Business Suite Inventory (INV) module for versions 12.1.1 and 12.2.2. It serves as the definitive ledger for all approved adjustments resulting from the physical inventory counting process. When a discrepancy is identified between the system's perpetual on-hand quantity and the actual physical count, and subsequently approved, a record is created in this table. This record is the primary source for generating the corresponding material transaction that updates the inventory balances and, if applicable, the general ledger. Its role is critical for maintaining inventory accuracy, supporting financial reconciliation, and providing an audit trail for all count-related adjustments.

Key Information Stored

The table's primary key is ADJUSTMENT_ID, which uniquely identifies each approved adjustment transaction. Its structure captures the complete context of the adjustment through foreign key relationships to other master and transactional entities. Key columns include PHYSICAL_INVENTORY_ID and ORGANIZATION_ID, linking the adjustment to a specific physical inventory header and operating unit. It stores item identification (INVENTORY_ITEM_ID), location details (SUBINVENTORY_NAME, LOCATOR_ID), and lot/serial control information (LOT_NUMBER, SERIAL_NUMBER). For costed organizations, it records the COST_GROUP_ID and the GL_ADJUST_ACCOUNT, which determines the financial account impacted. Crucially, it holds the SYSTEM_QUANTITY (the book quantity) and the COUNT_QUANTITY (the physically counted quantity), from which the adjustment delta is derived.

Common Use Cases and Queries

This table is central to post-count analysis and audit reporting. Common operational queries involve listing all adjustments for a specific physical inventory to review the magnitude and value of discrepancies. Financial controllers may query adjustments posted to specific general ledger accounts within a date range. A typical reporting query would join to MTL_SYSTEM_ITEMS_B and MTL_PHYSICAL_INVENTORIES to provide item and count details.

  • Sample Query: Retrieving approved adjustments with item details for a specific physical inventory.
    SELECT mpa.adjustment_id, mpa.inventory_item_id, msi.segment1 item_code, mpa.subinventory_name, mpa.system_quantity, mpa.count_quantity, (mpa.count_quantity - mpa.system_quantity) adjustment_qty FROM inv.mtl_physical_adjustments mpa, inv.mtl_system_items_b msi WHERE mpa.physical_inventory_id = :p_phy_inv_id AND mpa.organization_id = msi.organization_id AND mpa.inventory_item_id = msi.inventory_item_id ORDER BY mpa.adjustment_id;
  • Integration Point: Each record is the direct source for creating a corresponding transaction in MTL_MATERIAL_TRANSACTIONS via the PHYSICAL_ADJUSTMENT_ID foreign key.

Related Objects

As indicated by the extensive foreign key constraints, MTL_PHYSICAL_ADJUSTMENTS is a central hub connected to numerous master and transactional tables. Key related objects include:

  • Parent Tables: MTL_PHYSICAL_INVENTORIES (header), MTL_SYSTEM_ITEMS_B (item master), MTL_SECONDARY_INVENTORIES (subinventory), MTL_LOT_NUMBERS, MTL_SERIAL_NUMBERS, CST_COST_GROUPS, GL_CODE_COMBINATIONS (account).
  • Child Tables: MTL_MATERIAL_TRANSACTIONS (via PHYSICAL_ADJUSTMENT_ID) is the most critical child, as it holds the resulting inventory transaction. MTL_PHYSICAL_INVENTORY_TAGS is linked via ADJUSTMENT_ID to mark which count tags were adjusted.
  • Key Interface: The Physical Inventory Adjustments form and related concurrent processes (e.g., Post Adjustments) are the primary application interfaces that populate and manage data in this table.