Results for “mtl_transaction_lot_numbers”

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

Overview

MTL_TRANSACTION_LOT_NUMBERS is an Inventory (INV) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores lot-level detail for inventory transactions. Every time a transaction is processed against a lot-controlled item, the corresponding lot attributes, quantities, and descriptive information are persisted in this table. It functions as the transactional companion to the master lot definition table, capturing the state of a lot at the moment a specific material transaction occurs. The table resides in the INV schema, is documented as VALID, and contains 158 columns in the 12.2.2 physical schema.

From a dimensional modeling perspective, the mined foreign-key structure suggests a link classification under Data Vault heuristics. The table resolves the many-to-many relationship between material transactions (MTL_MATERIAL_TRANSACTIONS) and lot master records (MTL_LOT_NUMBERS), carrying its own descriptive and attribute payload, which is characteristic of a link table that also holds satellite-style descriptive columns such as quality, expiry, and vendor data.

Key Information Stored

The documented primary key, MTL_TRANSACTION_LOT_NUMBERS_PK, is a composite surrogate key consisting of TRANSACTION_ID and LOT_NUMBER. TRANSACTION_ID references the parent material transaction, while LOT_NUMBER identifies the lot within that transaction context. The business-key candidates visible in the schema include the combination of INVENTORY_ITEM_ID, ORGANIZATION_ID, and LOT_NUMBER, which ties the transactional lot record back to the master lot definition.

• TRANSACTION_ID – Foreign key to MTL_MATERIAL_TRANSACTIONS; identifies the parent inventory transaction.
• LOT_NUMBER – The lot being transacted; part of the composite primary key.
• INVENTORY_ITEM_ID – The item associated with the lot; joins to MTL_LOT_NUMBERS along with ORGANIZATION_ID and LOT_NUMBER.
• ORGANIZATION_ID – The inventory organization in which the transaction was executed.
• TRANSACTION_DATE – Date the transaction occurred, used for chronological reporting.
• TRANSACTION_QUANTITY / PRIMARY_QUANTITY – Quantity transacted in the transaction and primary UOM respectively.
• TRANSACTION_SOURCE_TYPE_ID / TRANSACTION_SOURCE_NAME – Source classification, with the type ID referencing MTL_TXN_SOURCE_TYPES.
• SUPPLIER_LOT_NUMBER / VENDOR_NAME / VENDOR_ID – Supplier-origin lot traceability data.
• EXPIRATION_ACTION_CODE / EXPIRATION_ACTION_DATE – Shelf-life and expiry handling information.
• STATUS_ID – Current lot status relevant at transaction time.
• PARENT_LOT_NUMBER – Supports lot genealogy and parent/child lot relationships.
• PRODUCT_TRANSACTION_ID – Foreign key to CSD_PRODUCT_TRANSACTIONS for process manufacturing integration.
• ATTRIBUTE_CATEGORY and C_/D_/N_ATTRIBUTE1–30 – Descriptive flexfield segments for extensible lot attributes.

Common Use Cases and Queries

Typical usage centers on lot traceability, recall reporting, expiry monitoring, and genealogy analysis. A frequent query joins the table to MTL_MATERIAL_TRANSACTIONS to reconstruct transaction history for a given lot:

• Trace all transactions for a specific lot: SELECT t.transaction_id, l.lot_number, l.transaction_quantity, l.transaction_date FROM mtl_transaction_lot_numbers l JOIN mtl_material_transactions t ON t.transaction_id = l.transaction_id WHERE l.lot_number = :lot;
• Identify lots approaching expiry by querying EXPIRATION_ACTION_DATE.
• Report supplier-origin lots using SUPPLIER_LOT_NUMBER and VENDOR_NAME for vendor quality analysis.
• Perform parent/child lot genealogy using PARENT_LOT_NUMBER and PARENT_OBJECT_TYPE.
• Extract DFF attribute values for custom lot reporting via ATTRIBUTE_CATEGORY and ATTRIBUTE1–15.

Related Objects

The most significant related objects, derived from the documented foreign keys, are:

• MTL_MATERIAL_TRANSACTIONS – Parent transaction table; joined on TRANSACTION_ID.
• MTL_LOT_NUMBERS – Master lot definition; joined on INVENTORY_ITEM_ID, ORGANIZATION_ID, and LOT_NUMBER.
• MTL_TXN_SOURCE_TYPES – Source type lookup; joined on TRANSACTION_SOURCE_TYPE_ID.
• CSD_PRODUCT_TRANSACTIONS – Process manufacturing transaction reference; joined on PRODUCT_TRANSACTION_ID.
• MTL_SYSTEM_ITEMS_B – Item master, commonly joined via INVENTORY_ITEM_ID for item descriptions.
• MTL_PARAMETERS – Organization definition, joined via ORGANIZATION_ID.