INV Lot Transaction Register – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Imported from BI Publisher Description: Lot transaction register Application: Inventory Source: Lot transaction register (XML) Short Name: INVTRLNT_XML DB package: INV_INVTRLNT_XMLP_PKG

Report Parameters

Unit of Measure, Transaction Dates From, Transaction Dates To, Serial Number Detail, Lot Numbers From, Lot Numbers To, Items From, Items To, Transaction Types From, Transaction Types To, Transaction Reasons From, Transaction Reasons To, Subinventories From, Subinventories To, Category Set, Categories From, Categories To, Source Type, Transaction Sources From, Transaction Sources To

Oracle EBS Tables Used

mtl_transaction_lot_numbers, mtl_system_items_vl, mtl_material_transactions, mtl_transaction_types, mtl_transaction_reasons, mtl_item_categories, mtl_item_locations_kfv, mtl_categories_kfv, fnd_user, po_headers_all, mtl_sales_orders_kfv, gl_code_combinations_kfv, wip_entities, mtl_generic_dispositions_kfv, cst_cost_updates, mtl_cycle_count_headers, mtl_physical_inventories, po_requisition_headers_all, mtl_unit_transactions, mtl_category_sets, q_body, q_serial

Report Categories

Enginatics, R12 only

INV Transaction Register, INV Material Transactions, GL Account Distribution Analysis, GL Account Analysis (Distributions), CAC Material Account Alias with Lot Numbers

Running This SQL Without Blitz Report

Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.

Download & Import Options

Resource Link
Excel Example Output INV Lot Transaction Register - Default 03-May-2024 032459.xlsx
Blitz Report™ XML Import INV_Lot_Transaction_Register.xml
Full SQL on Enginatics www.enginatics.com/reports/inv-lot-transaction-register/

INV Lot Transaction Register - Case Study & Technical Analysis

Executive Summary

The INV Lot Transaction Register is a specialized audit report designed for industries with strict traceability requirements (Pharmaceuticals, Food & Beverage, Aerospace). It provides a complete genealogy of lot-controlled items, detailing every transaction that has affected a specific lot number—from initial receipt or production, through inventory moves, to final shipment or consumption.

Business Challenge

Traceability is a major compliance requirement for many organizations. Challenges include:

Solution

The INV Lot Transaction Register consolidates all lot-related activities into a single chronological view. It links the lot number to the underlying transaction source (PO, Job, Sales Order), providing end-to-end visibility.

Key Features:

Technical Architecture

The report is built on the Oracle Inventory transaction model, specifically focusing on the lot extension tables.

Key Tables and Views

Core Logic

  1. Transaction Selection: The report selects transactions from MTL_MATERIAL_TRANSACTIONS based on the date range and item criteria.
  2. Lot Explosion: It joins to MTL_TRANSACTION_LOT_NUMBERS to retrieve the specific lots involved in each transaction.
  3. Source Resolution: Depending on the TRANSACTION_SOURCE_TYPE_ID, it joins to PO_HEADERS_ALL, WIP_ENTITIES, or OE_ORDER_HEADERS_ALL to get the document number (PO#, Job#, SO#).
  4. Serial Detail: If requested, it can further drill down to MTL_UNIT_TRANSACTIONS for serial-controlled lots.

Business Impact

© 2026 Enginatics