Skip to the content.

INV Transaction Historical Summary – 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

Application: Inventory Description: Transaction Historical Summary Report

This report provides equivalent functionality to the Oracle standard Transaction historical summary (XML) report.

Templates Quantity/Value Template – for use with the Quantity and Value Selection Option Balance Template – for use with the Balance Selection Option

The templates provide a summary pivot based on the selected sort option parameter with drill down to the detail data.

Sort Option Category: Pivot by Category, Subinventory/Cost Group All Others: Pivot by Subinventory/Cost Group, Category

Source: Transaction historical summary (XML) Short Name: INVTRHAN_XML DB package: INV_INVTRHAN_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

Organization Code, Selection Option, Sort By, Cost Groups From, Cost Groups To, Include Consigned, Rollback to this Date, Category Set, Categories From, Categories To, Items From, Items To, Subinventories From, Subinventories To, Source Type for Column One, Source Type for Column Two, Source Type for Column Three, Source Type for Column Four

Oracle EBS Tables Used

mtl_secondary_inventories, mtl_system_items_vl, mtl_item_categories, mtl_categories_kfv, mtl_onhand_quantities_detail, mtl_material_transactions, mtl_txn_source_types, mtl_parameters, mtl_onhand_qty_cost_v, cst_item_costs_for_gl_view, cst_inv_qty_temp, cst_inv_cost_temp, cst_cost_groups

Report Categories

Enginatics, R12 only

INV Onhand Quantities, MRP Pegging, CAC Inventory Pending Cost Adjustment - No Currencies, CAC Inventory Pending Cost Adjustment, CAC ICP PII Inventory Pending Cost Adjustment, INV Items, INV Transaction Register, WIP Discrete Job Shortage, INV Lot Transaction Register

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 Example
Blitz Report™ XML Import INV_Transaction_Historical_Summary.xml
SQL Source INV Transaction Historical Summary.sql
Full Report on Enginatics www.enginatics.com/reports/inv-transaction-historical-summary/

INV Transaction Historical Summary - Case Study & Technical Analysis

Executive Summary

The INV Transaction Historical Summary report is a retrospective analysis tool. It allows users to “roll back” the clock and see what the inventory value or quantity was at a specific point in the past. This is essential for audit reconstruction and trend analysis.

Business Challenge

Standard on-hand reports only show the current balance. But businesses often ask:

Solution

The INV Transaction Historical Summary report reconstructs the past balance by taking the current balance and “reversing” all transactions that happened since the target date.

Key Features:

Technical Architecture

This report uses a complex calculation engine (often involving temporary tables) to perform the rollback.

Key Tables and Views

Core Logic

  1. Current Balance: Starts with the current on-hand quantity.
  2. Reverse Transactions: Subtracts (or adds) the quantities of all transactions that occurred after the “Rollback Date”.
    • Formula: Historical Qty = Current Qty - (Sum of Inflows since Date) + (Sum of Outflows since Date)
  3. Costing: Applies the cost that was active at that historical date (for Standard Costing) or the calculated layer cost (for Average/FIFO).

Business Impact


© Enginatics GmbH