Results for “mtl_material_statuses_b”

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

Overview

MTL_MATERIAL_STATUSES_B is the base definition table for inventory material statuses in Oracle E-Business Suite. Owned by the INV schema and validated against both release 12.1.1 and 12.2.2, it stores the master list of status codes that govern how on-hand material can be transacted, reserved, and moved within an organization. Each row defines a reusable status such as "Available," "Quarantine," "Inspection Required," or "Restricted," together with the enabling rules that determine how material bearing that status may be handled.

The ETRM documentation notes that this table is designated as the base table for inventory status definitions and is "Used by WMS only," meaning it is primarily populated and consumed through the Warehouse Management System flows rather than general Inventory transaction entry. Status records are created and maintained in the Inventory setup windows, then referenced across the logistics and planning modules.

Under the heuristic Data Vault classification mined from the foreign-key structure, this table is modelled as a hub: it holds the durable business key (STATUS_ID) that numerous dependent entities point to as their reference. This is a modelling suggestion rather than a physical Oracle construct — the table itself remains a standard EBS transactional setup table (not a Data Vault artifact).

Key Information Stored

The table carries 37 documented columns. The most significant are:

• STATUS_ID — the surrogate primary key, and the single column forming MTL_MATERIAL_STATUSES_B_PK. In 12.2.2 the documented unique index is MTL_MATERIAL_STATUSES_B_PK (STATUS_ID, ZD_EDITION_NAME), which reflects the Editioning View / online patching framework. Status IDs are generated internally and should not be reused or renumbered.
• ENABLED_FLAG — indicates whether the status is active and selectable in setup and transaction flows.
• ZONE_CONTROL, LOCATOR_CONTROL, LOT_CONTROL, SERIAL_CONTROL, LPN_CONTROL — the control flags that determine whether a status permits assignment to zones, locators, lots, serial numbers, and license plates.
• ONHAND_CONTROL — the on-hand control setting governing how quantities under this status are reported or netted.
• AVAILABILITY_TYPE and RESERVABLE_TYPE — determine whether material is treated as available for planning/demand and whether it can be reserved. These drive availability and reservation behavior in order and shipping flows.
• INVENTORY_ATP_CODE — controls whether the status contributes to Available-to-Promise calculations.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, plus the ATTRIBUTE_CATEGORY and ATTRIBUTE1–ATTRIBUTE15 descriptive flexfield columns.

Translated, user-facing status names are not stored here; they reside in the language companion table MTL_MATERIAL_STATUSES_TL, which joins to this table on STATUS_ID.

Common Use Cases and Queries

Typical uses include listing enabled statuses for setup review, auditing which statuses are reservable or ATP-eligible, and joining to transaction history to see the status carried at each movement.

Listing enabled, reservable statuses with their translated names:

• SELECT b.status_id, t.description, b.enabled_flag, b.reservable_type, b.availability_type
• FROM mtl_material_statuses_b b, mtl_material_statuses_tl t
• WHERE b.status_id = t.status_id
• AND t.language = USERENV('LANG') AND b.enabled_flag = 'Y';

Finding which lots, serial numbers, or subinventories currently carry a given status:

• SELECT msi.secondary_inventory_name, b.status_id
• FROM mtl_secondary_inventories msi, mtl_material_statuses_b b
• WHERE msi.status_id = b.status_id;

Because the key is shared with the _TL table, any status report in a multilingual instance should join MTL_MATERIAL_STATUSES_TL rather than display the numeric ID.

Related Objects

The following objects reference this table through documented foreign keys on STATUS_ID:

• MTL_SECONDARY_INVENTORIES — subinventory-level default status (STATUS_ID).
• MTL_ITEM_LOCATIONS — status applied at the locator level.
• MTL_LOT_NUMBERS — status carried by a lot.
• MTL_SERIAL_NUMBERS — status carried by a serialized unit.
• MTL_MATERIAL_STATUS_HISTORY — status change audit trail.
• MTL_STATUS_TRANSACTION_CONTROL — rules controlling which transactions are permitted from/to a status.
• MTL_MATERIAL_STATUSES_TL — language-specific descriptions.
• CSI_ITEM_INSTANCES — instance condition reference (INSTANCE_CONDITION_ID).
• AHL_RA_SETUPS — status reference in Oracle Enterprise Asset Management / Asset Health setup.

For programmatic setup, statuses are typically created through the Inventory setup forms and the WMS APIs rather than direct inserts, since the STATUS_ID must remain consistent with the _TL and history tables.