Search Results mtl_status_transaction_control




Overview
Explore in AI Chat

Overview

The MTL_STATUS_TRANSACTION_CONTROL table is a core data object within the Oracle E-Business Suite Inventory (INV) module. It functions as a control matrix that defines permissible relationships between inventory material statuses and specific transaction types. As explicitly noted in the ETRM documentation, this table is "Used by WMS only," indicating its primary role is to enforce business rules within the Warehouse Management System (WMS) functionality. It acts as a gatekeeper, ensuring that items with a particular status can only be processed through transaction types explicitly allowed by this configuration, thereby preventing invalid or unintended material movements.

Key Information Stored

The table stores a simple but critical set of relationships. Its structure is defined by a composite primary key consisting of two columns: STATUS_ID and TRANSACTION_TYPE_ID. Each record represents a single, authorized link between a material status and a transaction type. The STATUS_ID is a foreign key referencing a specific status defined in MTL_MATERIAL_STATUSES_B. The TRANSACTION_TYPE_ID is a foreign key referencing a transaction type defined in MTL_TRANSACTION_TYPES. The existence of a record in this table explicitly permits the associated transaction type to be performed on items assigned the associated status. The absence of a record for a given status-transaction pair implies the transaction is prohibited for that status.

Common Use Cases and Queries

The primary use case is the validation of warehouse transactions. When a user attempts to perform an operation (e.g., a pick, move, or issue) in WMS, the system queries this table to verify if the item's current status allows the requested transaction type. Common queries involve listing all permitted transaction types for a given status or identifying which statuses allow a specific transaction. For reporting and administration, a join to the related descriptive tables is essential.

  • Sample Query: To find all allowed transaction types for a status named 'HOLD', one would use:
    SELECT mtt.transaction_type_name
    FROM mtl_status_transaction_control mstc,
    mtl_material_statuses_tl mst,
    mtl_transaction_types mtt
    WHERE mstc.status_id = mst.status_id
    AND mst.status_code = 'HOLD'
    AND mstc.transaction_type_id = mtt.transaction_type_id
    AND mst.language = USERENV('LANG');

Related Objects

MTL_STATUS_TRANSACTION_CONTROL is centrally linked to two master tables via foreign key constraints, as documented in the ETRM metadata.

  • MTL_MATERIAL_STATUSES_B: This table stores the definition of all inventory material statuses. The foreign key from MTL_STATUS_TRANSACTION_CONTROL.STATUS_ID to MTL_MATERIAL_STATUSES_B.STATUS_ID ensures that every control record references a valid, predefined status.
  • MTL_TRANSACTION_TYPES: This table defines all available inventory transaction types within Oracle EBS. The foreign key from MTL_STATUS_TRANSACTION_CONTROL.TRANSACTION_TYPE_ID to MTL_TRANSACTION_TYPES.TRANSACTION_TYPE_ID ensures that every control record references a valid, predefined transaction type.

These relationships are fundamental, as the control table's purpose is to intersect these two key domains. Any configuration or analysis of status-transaction rules requires joining to these tables to obtain descriptive codes and names.