Results for “mtl_transaction_lots_interface”

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

Overview

MTL_TRANSACTION_LOTS_INTERFACE is an INV-schema staging table that provides temporary lot storage for the Oracle EBS transaction processor. During inventory transactions that involve lot-controlled items, rows are written to this interface before the transaction manager validates and posts the data to the permanent lot tables (MTL_LOT_NUMBERS, MTL_TRANSACTION_LOTS_TEMP, and related entities). The table therefore occupies the inbound boundary of the inventory transaction pipeline, acting as the buffer through which lot attributes, quantities, and descriptive flexfield context flow from feeder processes such as receiving, work in process, order management, and external data loads.

Under the heuristic Data Vault classification mined from its foreign-key structure (satellite-leaning), this table is best modeled as a satellite or staging entity. It carries descriptive lot attributes keyed by a surrogate transaction interface identifier, with a small number of outbound references to transaction and serial-number tables; it does not function as a central hub or as a many-to-many link.

Key Information Stored

The table is defined with 163 columns in the documented 12.2.2 schema. The primary key is MTL_TRANSACTION_LOTS_INT_PK, defined on TRANSACTION_INTERFACE_ID, which is a surrogate key generated by the transaction interface rather than a business identifier. SOURCE_CODE and SOURCE_LINE_ID identify the originating feeder system and its document line, and together with LOT_NUMBER they form the most useful business-key candidates for reconciliation.

• TRANSACTION_INTERFACE_ID — surrogate primary key linking the row to its parent transaction interface record.
• SOURCE_CODE, SOURCE_LINE_ID — originating application and source document line.
• LOT_NUMBER, SUBLOT_NUM — the lot and sublots being transacted.
• TRANSACTION_QUANTITY, PRIMARY_QUANTITY, SECONDARY_TRANSACTION_QUANTITY — quantity moved in transaction, primary, and secondary UOMs.
• LOT_EXPIRATION_DATE, RETEST_DATE, ORIGINATION_DATE — shelf-life and quality dates.
• STATUS_ID, REASON_ID, REASON_CODE, EXPIRATION_ACTION_CODE — lot status and disposition controls.
• PROCESS_FLAG, ERROR_CODE — transaction processor control and validation outcome.
• SERIAL_TRANSACTION_TEMP_ID — foreign key to MTL_SERIAL_NUMBERS_INTERFACE.
• PRODUCT_TRANSACTION_ID — foreign key to CSD_PRODUCT_TRANSACTIONS.
• VENDOR_ID, VENDOR_NAME, SUPPLIER_LOT_NUMBER, COUNTRY_OF_ORIGIN — supplier and country-of-origin traceability.
• PARENT_LOT_NUMBER, PARENT_OBJECT_TYPE, PARENT_OBJECT_ID — genealogy and parent-object relationships.
• ATTRIBUTE_CATEGORY, ATTRIBUTE1–15 and C/D/N_ATTRIBUTE1–30 — descriptive flexfield and lot attribute storage.

Common Use Cases and Queries

The dominant use case is diagnostics: when a lot transaction fails or remains pending, developers and functional analysts query this table to inspect PROCESS_FLAG and ERROR_CODE before reprocessing.

• Identify failed lot lines: SELECT TRANSACTION_INTERFACE_ID, LOT_NUMBER, ERROR_CODE, PROCESS_FLAG FROM MTL_TRANSACTION_LOTS_INTERFACE WHERE PROCESS_FLAG = 'E' OR ERROR_CODE IS NOT NULL;
• Trace a supplier lot back to its receipt source through SOURCE_CODE, SOURCE_LINE_ID, and SUPPLIER_LOT_NUMBER.
• Report lots nearing expiry using LOT_EXPIRATION_DATE and RETEST_DATE for shelf-life monitoring.
• Reconcile inbound interfaces against posted MTL_MATERIAL_TRANSACTIONS by joining on TRANSACTION_INTERFACE_ID and SOURCE_LINE_ID.

Related Objects

The most significant related objects are those referenced by foreign key and those that consume the rows during processing.

• MTL_SERIAL_NUMBERS_INTERFACE — joined via MTL_TRANSACTION_LOTS_INTERFACE.SERIAL_TRANSACTION_TEMP_ID.
• CSD_PRODUCT_TRANSACTIONS — joined via PRODUCT_TRANSACTION_ID for product/customer traceability.
• MTL_TRANSACTION_LOTS_TEMP — the working table populated by the transaction processor from this interface.
• MTL_MATERIAL_TRANSACTIONS — the final inventory transaction ledger receiving posted rows.
• MTL_LOT_NUMBERS — the master lot definition updated once the interface row is processed.
• MTL_TRANSACTIONS_INTERFACE — the parent transaction-interface header associated via TRANSACTION_INTERFACE_ID.

Together these objects define the standard lot-transaction flow: staging in MTL_TRANSACTION_LOTS_INTERFACE, validation and processing in the temp tables, and permanent posting to the material transaction and lot master tables.