Results for “mtl_unit_transactions”

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

Overview

MTL_UNIT_TRANSACTIONS is an Inventory (INV) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores serial number transaction history. Each row captures a discrete movement, receipt, issue, or status change associated with a specific serialized unit, and is tied back to the driving inventory transaction through TRANSACTION_ID. The table is the definitive audit trail for serialized item activity across an organization, subinventory, and locator, and it carries the attribute flexfields and usage metrics (time/cycles since new, overhaul, repair, visit, and mark) used in asset tracking and depot repair scenarios.

The metadata classifies this object heuristically as a link table in Data Vault terms: it resolves the many-to-many association between material transactions and individual serial numbers, with additional descriptive context. The primary key MTL_UNIT_TRANSACTIONS_PK is composite on (TRANSACTION_ID, SERIAL_NUMBER), reflecting that role.

Key Information Stored

The documented schema exposes 149 columns in 12.2.2, including 30 coded, 30 numeric, and 15 descriptive attribute flexfield columns. The most significant columns are:

• TRANSACTION_ID — surrogate identifier and part of the composite primary key; foreign key to MTL_MATERIAL_TRANSACTIONS. It is the strongest business-key candidate because it names the parent inventory transaction.
• SERIAL_NUMBER — second component of the primary key; foreign key to MTL_SERIAL_NUMBERS together with INVENTORY_ITEM_ID and ORGANIZATION_ID.
• INVENTORY_ITEM_ID and ORGANIZATION_ID — identify the serialized item and the inventory organization, and complete the FK to MTL_SERIAL_NUMBERS.
• TRANSACTION_DATE — effective date of the serialized movement.
• SUBINVENTORY_CODE and LOCATOR_ID — the physical location at the time of the transaction; FKs to MTL_SECONDARY_INVENTORIES and MTL_ITEM_LOCATIONS respectively (both keyed with ORGANIZATION_ID).
• TRANSACTION_SOURCE_TYPE_ID and TRANSACTION_SOURCE_NAME — the origin of the transaction; TRANSACTION_SOURCE_TYPE_ID is an FK to MTL_TXN_SOURCE_TYPES.
• RECEIPT_ISSUE_TYPE — classifies the movement as a receipt or issue.
• CUSTOMER_ID and SHIP_ID — downstream/upstream party references for shipping and receiving flows.
• STATUS_ID — current serialized unit status at that transaction (e.g., defined, in stock, shipped, repaired).
• PRODUCT_TRANSACTION_ID — FK to CSD_PRODUCT_TRANSACTIONS, linking depot/field service activity to the inventory event.
• Usage metrics: TIME_SINCE_NEW, CYCLES_SINCE_NEW, TIME_SINCE_OVERHAUL, CYCLES_SINCE_OVERHAUL, TIME_SINCE_REPAIR, CYCLES_SINCE_REPAIR, NUMBER_OF_REPAIRS — supporting maintenance and reliability reporting for serialized assets.
• SERIAL_ATTRIBUTE_CATEGORY and the ATTRIBUTE1–15 flexfield columns — user-defined descriptive attributes attached to the serialized unit at the transaction.

Surrogate primary key: MTL_UNIT_TRANSACTIONS_PK on (TRANSACTION_ID, SERIAL_NUMBER). Business-key candidates include the combination of SERIAL_NUMBER, INVENTORY_ITEM_ID, and ORGANIZATION_ID when tracing a specific unit, and TRANSACTION_ID when grouping a single transaction's serialized lines.

Common Use Cases and Queries

Typical scenarios include serial number traceability (where did a given serial go, and when?), receipt/issue reconciliation for serialized items, warranty and repair analysis, and integration extracts to WMS, MES, or field service systems.

• Tracing one serial's history:

  SELECT transaction_id, transaction_date, subinventory_code,
         locator_id, receipt_issue_type, status_id
  FROM   mtl_unit_transactions
  WHERE  serial_number = :p_serial
  AND    inventory_item_id = :p_item
  AND    organization_id  = :p_org
  ORDER BY transaction_date;

• Enriching a material transaction with its serialized units:

  SELECT mmt.transaction_id, mut.serial_number, mut.status_id
  FROM   mtl_material_transactions mmt,
         mtl_unit_transactions     mut
  WHERE  mmt.transaction_id = mut.transaction_id
  AND    mmt.transaction_date BETWEEN :p_from AND :p_to;

• Locating serials currently in a subinventory: filter the latest row per serial by TRANSACTION_DATE and constrain SUBINVENTORY_CODE and ORGANIZATION_ID.
• Repair/overhaul reporting: aggregate NUMBER_OF_REPAIRS and CYCLES_SINCE_OVERHAUL by item to identify units due for service.
• Depot integration: join to CSD_PRODUCT_TRANSACTIONS on PRODUCT_TRANSACTION_ID to correlate field service events with inventory movements.

Related Objects

• MTL_MATERIAL_TRANSACTIONS — parent transaction; join on TRANSACTION_ID.
• MTL_SERIAL_NUMBERS — master serial registry; join on SERIAL_NUMBER, INVENTORY_ITEM_ID, ORGANIZATION_ID.
• MTL_ITEM_LOCATIONS — locator master; join on LOCATOR_ID (with ORGANIZATION_ID).
• MTL_SECONDARY_INVENTORIES — subinventory master; join on SUBINVENTORY_CODE (with ORGANIZATION_ID).
• MTL_TXN_SOURCE_TYPES — transaction source lookup; join on TRANSACTION_SOURCE_TYPE_ID.
• CSD_PRODUCT_TRANSACTIONS — depot/field service transactions; join on PRODUCT_TRANSACTION_ID.
• MTL_SYSTEM_ITEMS_B and MTL_PARAMETERS — item and organization context, joined on INVENTORY_ITEM_ID and ORGANIZATION_ID.

Inventory APIs such as INV_MATERIAL_TXN_PUB and the serial number processing APIs (e.g., INV_SERIAL_NUMBER_PUB) ultimately drive inserts into this table, so any custom integration touching serialized inventory should treat MTL_UNIT_TRANSACTIONS as the authoritative historical record.