INV Lot Transaction Register – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Inventory · all Inventory reports

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

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

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

Report Categories

Enginatics, R12 only

INV Transaction Register, INV Material Transactions, INV Serial Numbers, GL Account Distribution Analysis, INV Items, INV Item Upload, INV Item Upload 11i, INV Move Orders

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

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
SQL Source INV Lot Transaction Register.sql
Full Report 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

© Enginatics GmbH