Search Results mtl_material_status_history




Overview
Explore in AI Chat

Overview

The MTL_MATERIAL_STATUS_HISTORY table is a transactional history table within the Oracle E-Business Suite Inventory (INV) module. Its primary role is to serve as an audit trail, specifically for the Oracle Warehouse Management System (WMS), by logging every instance where the material status of an inventory item is changed. Material statuses control the usability of items (e.g., available, hold, quarantine) within a warehouse. This table provides a historical record of these status transitions, capturing the who, what, when, and why for each update, which is critical for compliance, traceability, and troubleshooting within complex warehouse operations.

Key Information Stored

The table's structure is designed to capture the complete context of a status change. The primary key, STATUS_UPDATE_ID, uniquely identifies each historical entry. Core data points include the ID of the new status (STATUS_ID), identifiers for the specific inventory entity affected (INVENTORY_ITEM_ID, ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID), and identifiers for lot and serial-controlled items (LOT_NUMBER, SERIAL_NUMBER). Crucially, it records the transaction details: the user who performed the update (LAST_UPDATED_BY), the timestamp (LAST_UPDATE_DATE), and the reason for the change via a link to MTL_TRANSACTION_REASONS (UPDATE_REASON_ID). The ZONE_CODE column indicates the warehouse zone associated with the update.

Common Use Cases and Queries

This table is primarily used for audit reporting and historical analysis within WMS-enabled environments. A common use case is tracing the status history of a specific serialized asset or lot to verify its chain of custody or to investigate quality holds. Another scenario involves generating reports for internal audits to demonstrate control over inventory status changes. Sample queries often join to related master tables to translate IDs into meaningful names. For example, to see the full history for a specific item in a location:

  • SELECT ms.STATUS_CODE, msh.* FROM MTL_MATERIAL_STATUS_HISTORY msh, MTL_MATERIAL_STATUSES_B ms WHERE msh.STATUS_ID = ms.STATUS_ID AND msh.INVENTORY_ITEM_ID = :item_id AND msh.ORGANIZATION_ID = :org_id AND msh.SUBINVENTORY_CODE = :subinv ORDER BY msh.LAST_UPDATE_DATE DESC;

Direct data manipulation is typically performed through the standard WMS or Inventory APIs, not via direct DML on this history table.

Related Objects

The MTL_MATERIAL_STATUS_HISTORY table maintains foreign key relationships with several core Inventory master tables, ensuring referential integrity for its logged data. As documented in the ETRM, the key relationships are:

  • MTL_MATERIAL_STATUSES_B: Links via STATUS_ID to validate and describe the new material status applied.
  • MTL_SECONDARY_INVENTORIES: Links via ORGANIZATION_ID and SUBINVENTORY_CODE (implied as ZONE_CODE in some contexts) to identify the subinventory.
  • MTL_ITEM_LOCATIONS: Links via ORGANIZATION_ID and LOCATOR_ID to identify the precise storage location.
  • MTL_LOT_NUMBERS: Links via ORGANIZATION_ID, INVENTORY_ITEM_ID, and LOT_NUMBER for lot-specific status history.
  • MTL_SERIAL_NUMBERS: Links via ORGANIZATION_ID, INVENTORY_ITEM_ID, and SERIAL_NUMBER for serial-specific status history.
  • MTL_TRANSACTION_REASONS: Links via UPDATE_REASON_ID to capture the documented reason for the status change.