Results for “mtl_item_status_pk”

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

Overview

MTL_ITEM_STATUS is a foundational Inventory (INV) reference table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores material status definitions. Each row defines a named status code that can be assigned to inventory items, lots, subinventories, and locators to control which transactions, activities, and operations are permitted against the associated material. Statuses are the primary mechanism by which Oracle Inventory enforces quality holds, quarantine restrictions, and staged lifecycle controls, from receipt through consumption and shipment.

Because MTL_ITEM_STATUS is referenced by multiple downstream tables and is itself largely independent of transactional data, the metadata classifies it heuristically as a hub-leaning Data Vault construct. In Data Vault modeling terms, it behaves as a hub: a stable, low-volume business-key registry (the status code) from which satellites and links are built by dependent tables such as MTL_SYSTEM_ITEMS_B and MTL_STATUS_ATTRIBUTE_VALUES.

Key Information Stored

The table is compact. Its primary key is MTL_ITEM_STATUS_PK, defined on INVENTORY_ITEM_STATUS_CODE, which is also the sole documented business-key candidate and the natural business identifier used throughout the application. This single column drives every foreign-key relationship into the table.

• INVENTORY_ITEM_STATUS_CODE — the unique, user-defined status identifier (for example, Active, Quarantine, Obsolete, Rework). Functions as both the surrogate primary key and business key.
• Description and status attributes — descriptive text and transaction-control flags (such as receipt, issue, and reservation indicators) that determine which material movements are permitted under the status.
• Enabled/disable indicators — govern whether the status remains selectable for new assignments while preserving history for records already using it.

Attribute-level detail for controlling individual transactions is stored not here but in the dependent table MTL_STATUS_ATTRIBUTE_VALUES, which maps each status to specific activity permissions. MTL_ITEM_STATUS therefore acts as the header registry, while MTL_STATUS_ATTRIBUTE_VALUES carries the granular rules.

Common Use Cases and Queries

Typical reporting and administrative scenarios include listing all defined statuses, identifying items currently held under a restrictive status, and joining status descriptions onto item queries for readability.

• Listing valid status lookups for a value set or LOV: SELECT inventory_item_status_code FROM mtl_item_status ORDER BY 1;
• Joining to items to surface status text: SELECT msib.segment1, msib.inventory_item_status_code, mis.description FROM mtl_system_items_b msib, mtl_item_status mis WHERE msib.inventory_item_status_code = mis.inventory_item_status_code;
• Auditing attribute permissions per status via MTL_STATUS_ATTRIBUTE_VALUES for quality or compliance reviews.
• Reviewing pending status transitions through MTL_PENDING_ITEM_STATUS to track items awaiting a status change.

Related Objects

The documented foreign-key relationships establish MTL_ITEM_STATUS as a referenced parent:

• MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_STATUS_CODE — assigns a status to each item definition.
• MTL_STATUS_ATTRIBUTE_VALUES.INVENTORY_ITEM_STATUS_CODE — defines the transaction-control rules for each status.
• MTL_PENDING_ITEM_STATUS.STATUS_CODE — tracks items awaiting a status update.
• BOM_PARAMETERS.BOM_DELETE_STATUS_CODE — references a status used during bill-of-material delete processing.