Search Results mtl_physical_inventories




Overview
Explore in AI Chat

Overview

The MTL_PHYSICAL_INVENTORIES table is a core master data table within the Oracle E-Business Suite Inventory (INV) module. It serves as the primary repository for the definition and control parameters of all physical inventory processes. A physical inventory is a critical business procedure for verifying the accuracy of on-hand quantities by performing a manual count of items in a warehouse or organization. This table stores the foundational header-level information for each distinct physical inventory, enabling organizations to schedule, execute, and reconcile these counts. Its role is central to the cycle counting and physical inventory functionalities, acting as the parent entity for associated count tags, subinventory inclusions, and subsequent adjustment transactions.

Key Information Stored

The table's structure is anchored by a composite primary key consisting of ORGANIZATION_ID and PHYSICAL_INVENTORY_ID, ensuring uniqueness within each inventory organization. Key columns define the inventory's operational parameters. The PHYSICAL_INVENTORY_ID is the system-generated unique identifier. ORGANIZATION_ID links the inventory to a specific operating unit within the MTL_PARAMETERS table. Other significant columns include the PHYSICAL_INVENTORY_NAME for user identification, and status flags that control the inventory's lifecycle (e.g., from 'Draft' to 'Approved' to 'Completed'). The table also holds control information such as the DEFAULT_GL_ADJUST_ACCOUNT, which references GL_CODE_COMBINATIONS to determine the general ledger account used for posting valuation adjustments resulting from count variances. Dates for the scheduled count and approval, along with descriptive comments, are also standard stored attributes.

Common Use Cases and Queries

This table is primarily accessed for setup, monitoring, and reporting on physical inventory exercises. Common operational queries include retrieving all active inventories for a specific organization to track progress, or listing inventories pending approval. A typical reporting query might join with related tag tables to summarize count status. For example:

  • Listing Inventories by Status: SELECT physical_inventory_name, description, approval_required FROM mtl_physical_inventories WHERE organization_id = :org_id AND approval_status = 'APPROVED';
  • Inventory Setup Verification: Queries often join with MTL_PHYSICAL_SUBINVENTORIES to verify which storage sub-locations are included in a given inventory definition.
  • Adjustment Analysis: For reconciliation, data from this table is joined to MTL_PHYSICAL_ADJUSTMENTS to analyze the financial impact of completed inventories by linking adjustments back to their source inventory header.

Related Objects

As documented in the ETRM metadata, MTL_PHYSICAL_INVENTORIES has defined foreign key relationships with several key transactional and setup tables, forming the backbone of the physical inventory process.

  • MTL_PARAMETERS: Linked via ORGANIZATION_ID. This establishes the inventory organization context for the physical inventory.
  • GL_CODE_COMBINATIONS: Linked via DEFAULT_GL_ADJUST_ACCOUNT. This defines the default general ledger account for posting adjustment values.
  • MTL_PHYSICAL_ADJUSTMENTS: Child table linked by ORGANIZATION_ID and PHYSICAL_INVENTORY_ID. Stores the actual quantity and value adjustments resulting from the inventory count.
  • MTL_PHYSICAL_INVENTORY_TAGS: Child table linked by ORGANIZATION_ID and PHYSICAL_INVENTORY_ID. Stores the individual count tags or sheets generated for the inventory.
  • MTL_PHYSICAL_SUBINVENTORIES: Child table linked by ORGANIZATION_ID and PHYSICAL_INVENTORY_ID. Defines which specific subinventories are included within the scope of the physical inventory.