Results for “mtl_transaction_accounts”

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

Overview

MTL_TRANSACTION_ACCOUNTS is the inventory distribution table in Oracle E-Business Suite (INV module, INV schema). It records the accounting distributions generated for material transactions processed in Oracle Inventory, storing the debit and credit entries that will eventually be transferred to Oracle General Ledger. Every row links a single material transaction to the GL accounts, cost elements, activities, resources, and amounts that describe its accounting impact, making it the authoritative bridge between inventory subledger activity and the general ledger.

Under the supplied relationship metadata, this object is heuristically classified as a link in a Data Vault model. This reflects its position as an associative entity: it connects a material transaction (MTL_MATERIAL_TRANSACTIONS) to myriad dimension-like references such as GL_CODE_COMBINATIONS, CST_COST_ELEMENTS, CST_ACTIVITIES, BOM_RESOURCES, and FND_CURRENCIES. Modelers should treat it as a relationship table rather than a pure hub or satellite, since it carries both foreign-key pairings and descriptive accounting measures.

Key Information Stored

The table contains 36 documented columns. The most operationally significant are outlined below.

• TRANSACTION_ID – Foreign key to MTL_MATERIAL_TRANSACTIONS, identifying the originating material transaction (the primary business association; this is a foreign-key candidate rather than a unique surrogate).
• REFERENCE_ACCOUNT – Foreign key to GL_CODE_COMBINATIONS, holding the accounting flexfield combination for the distribution line.
• INVENTORY_ITEM_ID, ORGANIZATION_ID – Composite foreign key to MTL_SYSTEM_ITEMS_B, identifying the item and inventory organization.
• TRANSACTION_DATE, TRANSACTION_SOURCE_ID, TRANSACTION_SOURCE_TYPE_ID – Date and source classification of the transaction; TRANSACTION_SOURCE_TYPE_ID references MTL_TXN_SOURCE_TYPES.
• TRANSACTION_VALUE, BASE_TRANSACTION_VALUE – Entered and base-currency amounts for the distribution.
• PRIMARY_QUANTITY – Quantity associated with the accounting line.
• COST_ELEMENT_ID, ACTIVITY_ID, RESOURCE_ID – Foreign keys to CST_COST_ELEMENTS, CST_ACTIVITIES, and BOM_RESOURCES, defining the costing composition.
• CURRENCY_CODE and conversion columns – CURRENCY_CODE references FND_CURRENCIES; CURRENCY_CONVERSION_DATE, CURRENCY_CONVERSION_TYPE, and CURRENCY_CONVERSION_RATE support multi-currency distributions.
• GL_BATCH_ID – Links the distribution to the ORG_GL_BATCHES transfer batch.
• ACCOUNTING_LINE_TYPE, BASIS_TYPE, RATE_OR_AMOUNT, CONTRA_SET_ID – Describe the nature and pairing of the accounting entry.
• ENCUMBRANCE_TYPE_ID, REPETITIVE_SCHEDULE_ID – Reference GL_ENCUMBRANCE_TYPES and WIP_REPETITIVE_SCHEDULES respectively.
• GL_SL_LINK_ID, INV_SUB_LEDGER_ID, USSGL_TRANSACTION_CODE – Subledger linking and federal USSGL attribution.
• Audit columns – LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_ID.

Common Use Cases and Queries

Typical uses include reconciling inventory subledger balances to GL, analyzing cost element and activity breakdowns, and tracing transactions awaiting transfer.

Distributions for a given transaction:

SELECT transaction_id, reference_account, transaction_value, base_transaction_value, cost_element_id, activity_id FROM mtl_transaction_accounts WHERE transaction_id = :txn_id;

Unposted distributions not yet assigned to a GL batch:

SELECT organization_id, COUNT(*), SUM(transaction_value) FROM mtl_transaction_accounts WHERE gl_batch_id IS NULL GROUP BY organization_id;

Joining to the cost element and activity names for reporting purposes is common in period-end close and cost analysis workbooks.

Related Objects

• MTL_MATERIAL_TRANSACTIONS – joined on TRANSACTION_ID; the originating transaction.
• GL_CODE_COMBINATIONS – joined on REFERENCE_ACCOUNT; provides account descriptions.
• MTL_SYSTEM_ITEMS_B – joined on INVENTORY_ITEM_ID and ORGANIZATION_ID.
• CST_COST_ELEMENTS, CST_ACTIVITIES, BOM_RESOURCES – cost composition dimensions.
• ORG_GL_BATCHES – joined on ORGANIZATION_ID and GL_BATCH_ID.
• MTL_TXN_SOURCE_TYPES – joined on TRANSACTION_SOURCE_TYPE_ID.
• FND_CURRENCIES – joined on CURRENCY_CODE.
• GL_ENCUMBRANCE_TYPES, WIP_REPETITIVE_SCHEDULES – optional encumbrance and repetitive references.