INV Material Movements – 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

Detailed report of On Hand Quantity with stock movements by Item , Org Code . Material Movements involve cumulative buckets for stock/in/mvmt and month wise non cumulative buckets for stock out

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, Item, Category Set 1, Category Set 2, Category Set 3, Show Movements Summary, Txn Date From

Oracle EBS Tables Used

org_organization_definitions, mtl_onhand_quantities_detail, mtl_item_status_vl, mtl_system_items_vl, mtl_units_of_measure_vl, mtl_material_transactions, mtl_parameters, cst_cost_types, cst_item_costs, org_access_view

Report Categories

Enginatics

CAC New Items, INV Onhand Quantities, CAC Zero Item Costs, CAC Onhand Lot Value (Real-Time), CAC Calculate ICP PII Item Costs by Where Used, CAC Cost vs. Planning Item Controls, CAC Inventory and Intransit Value (Period-End) - Discrete/OPM, INV Slow Moving Inventory, CAC User-Defined and Rolled Up Costs

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 Material Movements 04-Apr-2026 123137.xlsx
Blitz Report™ XML Import INV_Material_Movements.xml
SQL Source INV Material Movements.sql
Full Report on Enginatics www.enginatics.com/reports/inv-material-movements/

INV Material Movements - Case Study & Technical Analysis

Executive Summary

The INV Material Movements report provides a flow-based view of inventory. Unlike a static “On-Hand” report (snapshot in time) or a “Transaction Register” (list of events), this report combines both to show the flux of inventory: Opening Balance + In - Out = Closing Balance. It is essential for analyzing inventory turnover and understanding the velocity of material through the warehouse.

Business Challenge

Understanding why inventory levels changed is often harder than knowing what the level is. Managers struggle with:

Solution

The INV Material Movements report calculates the material flow for a specific period. It categorizes movements into “In” (Receipts, WIP Completions) and “Out” (Shipments, WIP Issues) to provide a clear picture of activity.

Key Features:

Technical Architecture

This report is computationally intensive as it often has to reconstruct historical balances from the transaction log.

Key Tables and Views

Core Logic

  1. Current State: Determines the current on-hand quantity from MTL_ONHAND_QUANTITIES_DETAIL.
  2. Rollback/Rollforward: To find the balance at a past date, the report sums all transactions after that date and subtracts them from the current balance (or adds them, depending on the direction).
  3. Aggregation: Sums the PRIMARY_QUANTITY of transactions within the period, grouped by Transaction Type (In vs. Out).

Business Impact

© Enginatics GmbH