Results for “mtl_physical_subinventories”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

MTL_PHYSICAL_SUBINVENTORIES is an Oracle Inventory (INV) transactional table that defines the set of subinventories included in a physical inventory count. A physical inventory is initiated at the organization level, but inventory quantities are physically verified within individual subinventories; this table is the association that scopes each physical inventory to the subinventories that must be counted. It is owned by the INV schema and is present in both Oracle EBS 12.1.1 and 12.2.2.

From a heuristic Data Vault modeling perspective, the ETRM metadata classifies this object as a link entity. This is consistent with its structure: it resolves a many-to-many relationship between physical inventory definitions and subinventories, carrying no descriptive attributes of its own beyond audit columns. It serves as a junction between MTL_PHYSICAL_INVENTORIES and MTL_SECONDARY_INVENTORIES.

Key Information Stored

The documented structure contains 12 columns. The most significant are the key components and the standard Oracle audit and concurrent-program columns.

• ORGANIZATION_ID — Identifies the inventory organization in which the physical inventory and subinventory reside. Part of the composite primary key and a foreign key to MTL_PARAMETERS.
• PHYSICAL_INVENTORY_ID — Identifies the specific physical inventory to which the subinventory is attached. Also part of the primary key.
• SUBINVENTORY — The subinventory code being included in the count. Part of the primary key and a foreign key value referencing MTL_SECONDARY_INVENTORIES.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard audit trail indicating when and by whom the row was last modified.
• CREATION_DATE, CREATED_BY — Standard creation audit columns.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent program context, identifying the request and program responsible for the last update.

The primary key is defined by the constraint MTL_PHYSICAL_SUBINVENTORIES_PK, composed of ORGANIZATION_ID, PHYSICAL_INVENTORY_ID, and SUBINVENTORY. A unique index, MTL_PHYSICAL_SUBINVENTORIES_U1, covers the same three columns, serving as a documented business-key candidate. Because the primary key is itself composite and business-meaningful, there is no separate surrogate identifier column in this table.

Common Use Cases and Queries

Typical uses include listing the subinventories in scope for a physical inventory, reporting on count coverage, and validating that count data has been captured for every required subinventory.

• Retrieve the subinventories assigned to a physical inventory:

  SELECT subinventory FROM mtl_physical_subinventories WHERE organization_id = :org AND physical_inventory_id = :pid;

• Join to MTL_PHYSICAL_INVENTORIES to report the physical inventory name and status alongside covered subinventories.
• Join to MTL_SECONDARY_INVENTORIES to obtain the subinventory description and attributes for a given count.
• Audit which concurrent program created or last touched the assignment using REQUEST_ID and PROGRAM_ID.

Related Objects

• MTL_PHYSICAL_INVENTORIES — joined on ORGANIZATION_ID and PHYSICAL_INVENTORY_ID; the parent physical inventory definition.
• MTL_SECONDARY_INVENTORIES — joined on ORGANIZATION_ID and SUBINVENTORY; provides subinventory details.
• MTL_PARAMETERS — joined on ORGANIZATION_ID; the organization-level inventory parameters.
• MTL_PHYSICAL_INVENTORY_TAGS — the tag records generated from these scoped subinventories during a count.
• MTL_PHYSICAL_ADJUSTMENTS — adjustments resulting from approved physical counts.