Search Results mtl_material_statuses_b




Overview
Explore in AI Chat

Overview

The MTL_MATERIAL_STATUSES_B table is the base table for defining inventory statuses within Oracle E-Business Suite Inventory (INV) and Warehouse Management (WMS) modules. As a core setup entity, it stores the master list of status codes that can be applied to inventory items to control their availability and transaction eligibility. The ETRM documentation explicitly notes its primary use is for WMS, indicating its critical role in warehouse operations where precise control over material condition—such as Hold, Quarantine, or Available—is required. It serves as the parent table for a multilingual descriptive table (MTL_MATERIAL_STATUSES_TL) and is referenced by numerous transactional tables to track the current condition of inventory across lots, serials, locations, and subinventories.

Key Information Stored

The table's primary key is the STATUS_ID, a unique internal identifier for each material status. While the provided ETRM excerpt does not list all columns, the foreign key relationships imply the table stores the core attributes of a status definition. Typical columns in such a base table would include the status code (e.g., 'HOLD', 'QUARANTINE'), an enabled flag, default controls for transactions (like allowing issues, receipts, or transfers), and creation/update metadata. The STATUS_ID is the central column, propagated to all related transactional tables to tag inventory with a specific condition.

Common Use Cases and Queries

This table is central to queries reporting on inventory health and transaction blocks. Common use cases include identifying all items on a specific status hold, analyzing status usage across the warehouse, and validating setup before transaction processing. A frequent reporting query joins the base table with its descriptive translation table to fetch user-friendly names. For example, to list all defined statuses, one might use:

  • SELECT msb.STATUS_ID, mst.STATUS_CODE, mst.DESCRIPTION FROM INV.MTL_MATERIAL_STATUSES_B msb, INV.MTL_MATERIAL_STATUSES_TL mst WHERE msb.STATUS_ID = mst.STATUS_ID AND mst.LANGUAGE = USERENV('LANG');

Another critical pattern is joining to transactional tables like MTL_LOT_NUMBERS to find all lots assigned a particular status, which is essential for release-from-hold workflows in WMS.

Related Objects

As per the ETRM metadata, MTL_MATERIAL_STATUSES_B has a wide array of foreign key dependencies, highlighting its foundational nature. Key related objects include:

These relationships enforce data integrity and ensure status definitions centrally govern material flow throughout the inventory system.