Search Results mtl_material_statuses




Overview
Explore in AI Chat

Overview

MTL_MATERIAL_STATUSES is an Oracle Inventory (INV) reference table that stores the definition of material statuses used to control the handling, movement, and disposition of inventory items within a warehouse. In Oracle E-Business Suite 12.1.1 and 12.2.2, the table serves as the master repository for status codes that may be assigned to on-hand quantities, lots, and serial numbers. According to the ETRM metadata, the object is documented as "Inventory status definition – Used by WMS only," indicating that its principal runtime consumption is within the Warehouse Management System (WMS) context rather than in core INV transaction processing alone.

The metadata further states that the table is "Not implemented in this database." This notation typically reflects the state of a specific reference environment rather than a universal condition; in an operational EBS instance with WMS enabled, the table is populated with the status records that drive status-based material control. The heuristic Data Vault classification supplied in the metadata is standalone, meaning the table has no mined foreign-key relationships to other tables. In Data Vault modeling terms, this suggests MTL_MATERIAL_STATUSES behaves as a hub-like reference entity, holding a durable business concept (the material status) whose relationships to transactional tables are maintained implicitly rather than through enforced foreign keys.

Key Information Stored

The documented structure is deliberately compact. The primary key is defined by the constraint MTL_MATERIAL_STATUSES_PK, built on the single column STATUS_ID. This surrogate key uniquely identifies each material status record and is the value referenced by downstream inventory, lot, and serial tables when a status is applied.

  • STATUS_ID – The surrogate primary key and the sole documented column. It is the numeric identifier assigned to each material status definition and is the value propagated to dependent objects that assign a status to material.

Beyond the documented column, a complete material status definition in EBS conventionally carries a business-key attribute, typically the status code, along with a descriptive name, a status type or class, and flags governing whether the status permits transactions, reservations, shipping, or billing. These attributes constitute the business-key candidates that complement the surrogate STATUS_ID. Because the ETRM metadata documents only STATUS_ID, implementers should treat the surrogate key as authoritative and confirm any additional descriptive or business-key columns directly against the deployed table definition in the target environment.

Common Use Cases and Queries

The primary use case is resolving a STATUS_ID encountered on an inventory or WMS record into a human-readable status definition for reporting and validation.

  • Listing all defined material statuses for reference or configuration review: SELECT status_id FROM mtl_material_statuses;
  • Resolving a status applied to on-hand material back to its definition by joining on STATUS_ID.
  • Driving status-based reporting in WMS, such as segregating quarantined, rejected, or restricted stock from available inventory.
  • Validating inbound interface data to ensure that a supplied STATUS_ID exists before loading inventory transactions.

Because the metadata describes the table as used by WMS only, query efforts in non-WMS implementations may return no rows or reveal that the object is unused, consistent with the "Not implemented in this database" note. Reporting designs should therefore verify population before depending on the table.

Related Objects

The mined relationship data classifies MTL_MATERIAL_STATUSES as standalone, with no enforced foreign keys originating from it. Its practical dependencies are implied rather than declarative, and the most significant objects that reference or rely on its STATUS_ID value include:

  • MTL_MATERIAL_STATUS_HISTORY – Records status transitions, joining on STATUS_ID.
  • MTL_ONHAND_QUANTITIES – Carries the current status of on-hand stock by STATUS_ID.
  • MTL_LOT_NUMBERS – Associates a material status with lot-controlled inventory.
  • MTL_SERIAL_NUMBERS – Associates a material status with serialized units.
  • WMS-related transaction and task tables – Consume the status definition during warehouse task execution and movement.

Collectively, these objects establish MTL_MATERIAL_STATUSES as the reference anchor for status-driven inventory control across the warehouse.