Search Results mtl_physical_subinventories




Overview
Explore in AI Chat

Overview

The MTL_PHYSICAL_SUBINVENTORIES table is a core transactional table within the Oracle E-Business Suite Inventory (INV) module, specifically for versions 12.1.1 and 12.2.2. It serves as a junction table that defines the relationship between a specific physical inventory and the subinventories selected for inclusion in that count. Its primary role is to store the list of subinventories that are part of a given physical inventory definition, enabling organizations to plan and execute cycle counts or full physical inventories on a targeted subset of their stock locations rather than the entire organization.

Key Information Stored

The table's structure is defined by a composite primary key and several foreign key relationships that enforce data integrity. The key columns are:

Together, these three columns form the primary key (MTL_PHYSICAL_SUBINVENTORIES_PK), ensuring a unique combination of inventory, count, and subinventory. Each record represents the assignment of one subinventory to one physical inventory event.

Common Use Cases and Queries

This table is central to reporting and validation during physical inventory setup and analysis. A common use case is generating a list of all subinventories scheduled for a particular physical inventory, which is crucial for count tag preparation and auditor review. Another scenario involves identifying all active physical inventories that include a specific subinventory, perhaps to avoid conflicting counts. A typical query would join to the physical inventory header table to filter on the count name or status.

Sample SQL: To list all subinventories for a specific physical inventory ID, one would query: SELECT SUBINVENTORY FROM INV.MTL_PHYSICAL_SUBINVENTORIES WHERE ORGANIZATION_ID = :org_id AND PHYSICAL_INVENTORY_ID = :phy_id; For a more detailed report, a join to MTL_SECONDARY_INVENTORIES and MTL_PHYSICAL_INVENTORIES would provide subinventory descriptions and count details.

Related Objects

As indicated by its foreign keys, MTL_PHYSICAL_SUBINVENTORIES has direct dependencies on several key Inventory tables:

  • MTL_PHYSICAL_INVENTORIES: The parent header table defining the physical inventory name, dates, and parameters.
  • MTL_SECONDARY_INVENTORIES: Provides descriptive and control information for the SUBINVENTORY code.
  • MTL_PARAMETERS: Defines the inventory ORGANIZATION_ID.

This table is primarily populated through the Oracle Forms interface when defining a physical inventory or via the corresponding public APIs. It is referenced by programs that generate count tags and reconcile inventory balances.