Results for “mtl_status_transaction_control”

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

Overview

MTL_STATUS_TRANSACTION_CONTROL is an Inventory (INV) module table that defines the relationship between inventory material statuses and inventory transaction types. In Oracle EBS 12.1.1 and 12.2.2, it functions as a control matrix that governs whether a specific transaction type is permitted against material held under a given status. The table is documented in ETRM as being used by Warehouse Management System (WMS) functionality only, meaning its rows drive validation logic when WMS-managed transactions are executed against status-controlled inventory.

From a Data Vault modeling perspective, the heuristic classification mined from the foreign key structure designates this table as a link. This is appropriate because the table resolves a many-to-many relationship between two independent entities: material statuses and transaction types. It does not carry descriptive attributes about either entity; instead, it records the association and an allowance flag, which is characteristic of a link table rather than a hub or satellite.

Key Information Stored

The table contains 10 documented columns. The most significant are:

• STATUS_ID – Identifies the material status from MTL_MATERIAL_STATUSES_B. Part of the composite primary key and unique index.
• TRANSACTION_TYPE_ID – Identifies the transaction type from MTL_TRANSACTION_TYPES. Also part of the composite primary key and unique index.
• IS_ALLOWED – A flag indicating whether the combination of status and transaction type is permitted. This is the operative control attribute referenced during transaction validation.
• CREATION_DATE, CREATED_BY – Standard audit columns recording row creation.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN – Standard audit columns recording the most recent modification.
• PROGRAM_APPLICATION_ID, PROGRAM_ID – Concurrent program context that created or last updated the row.

The surrogate primary key is MTL_STATUS_TRANSACTION_CON_PK, defined across (STATUS_ID, TRANSACTION_TYPE_ID). A unique index, MTL_STATUS_TRANSACT_CONTROL_U1, covers the same column pair, making it the business-key candidate. Because the PK and unique index are identical in composition, the table is effectively a pure intersection entity with no separate surrogate identifier column.

Common Use Cases and Queries

Typical usage centers on determining whether a transaction is valid for a given status, and on reporting the configured control matrix.

• Validating a transaction type against a material status before processing in WMS flows.
• Reporting which statuses block or permit specific transaction types across a warehouse.
• Auditing configuration changes to status-transaction controls.

A representative query joins the control table to its parents:

SELECT ms.status_code, tt.transaction_type_name, ctl.is_allowed FROM mtl_status_transaction_control ctl, mtl_material_statuses_b ms, mtl_transaction_types tt WHERE ctl.status_id = ms.status_id AND ctl.transaction_type_id = tt.transaction_type_id AND ctl.is_allowed = 'Y';

Filtering on IS_ALLOWED supports quick extraction of permitted or prohibited combinations for a given status or transaction type.

Related Objects

The following objects are most significant to MTL_STATUS_TRANSACTION_CONTROL, based on the documented foreign key relationships:

• MTL_MATERIAL_STATUSES_B – Referenced via STATUS_ID; the base table of material status definitions.
• MTL_TRANSACTION_TYPES – Referenced via TRANSACTION_TYPE_ID; the base table of transaction type definitions.
• MTL_MATERIAL_STATUSES_TL – Provides translated status names for reporting joins on STATUS_ID.
• MTL_MATERIAL_TRANSACTIONS – Consumes status and transaction type context during inventory movement validation.
• MTL_TRANSACTION_TYPES_TL – Supplies translated transaction type names when building control reports.

Because the table is WMS-specific, its rows are consumed primarily by WMS validation logic rather than by general inventory transaction processing, and joins to the two parent tables above are the standard path for both validation and reporting.