Search Results mtl_item_status




Overview
Explore in AI Chat

Overview

The MTL_ITEM_STATUS table is a core reference table within the Oracle E-Business Suite Inventory (INV) module. It serves as the master repository for defining and storing all valid material status codes that can be assigned to items and transactions. A material status is a powerful control mechanism that governs the usability of an item for specific business functions, such as restricting an item from being issued to a job, sold on an order, or received into inventory. The table is essential for enforcing inventory control policies and managing item lifecycles across the supply chain.

Key Information Stored

The primary data stored in this table is the status code definition. The key column is INVENTORY_ITEM_STATUS_CODE, which is the unique identifier and primary key for each status (e.g., 'INACTIVE', 'PENDING', 'NONTRANSACTABLE'). While the provided ETRM excerpt does not list all columns, based on standard implementation, the table typically includes descriptive columns such as DESCRIPTION, DISABLE_DATE, and ENABLED_FLAG. Crucially, the table's relationship with MTL_STATUS_ATTRIBUTE_VALUES (as shown in the foreign key metadata) indicates that each status code is further defined by a set of attribute controls (e.g., Inventory, Purchasing, Order Management) that specify the exact transactional restrictions.

Common Use Cases and Queries

The primary use case is to query valid statuses for reporting, validation, and setup purposes. A common requirement is to list all active status codes along with their descriptions for a LOV in a custom form or report. Another critical use is to analyze which items are governed by a specific status, often joining to MTL_SYSTEM_ITEMS_B. For instance, to find all items currently assigned a status that prevents shipping, one might join MTL_ITEM_STATUS, MTL_STATUS_ATTRIBUTE_VALUES, and MTL_SYSTEM_ITEMS_B, filtering on the relevant shipping attribute. A basic query to retrieve all defined statuses is:

  • SELECT inventory_item_status_code, description, enabled_flag FROM mtl_item_status WHERE SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE);

Related Objects

As per the provided foreign key metadata, MTL_ITEM_STATUS is centrally referenced by several key inventory and manufacturing tables. The most significant relationship is with MTL_SYSTEM_ITEMS_B, where the INVENTORY_ITEM_STATUS_CODE column assigns the status to an item master record. MTL_STATUS_ATTRIBUTE_VALUES stores the detailed transactional restrictions (attributes) for each status code. MTL_PENDING_ITEM_STATUS references it for items in a pending approval state, and BOM_PARAMETERS references it to define a default status for component deletion in Bills of Material. These relationships underscore its role as a foundational control table.