INV Slow Moving Inventory – 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

Slow moving, excess and obsolete inventory. Lists items still holding stock as of a chosen date that have had no material movement over a chosen period, with their value, usage rate and months of supply, for obsolescence provisioning.

On Hand is the quantity as of the As of Date, not the current quantity. It is rolled back like Oracle’s Transaction Historical Summary report, by reversing every material transaction posted after that date. Values use the item cost of the organization’s primary cost method.

The no activity test is made per item, so an item is dropped as a whole as soon as any of its subinventories moved. Ignore Adjustment Transactions excludes cycle count adjustments, physical inventory adjustments and cost updates from that test and from Last Transaction Date. Logical transactions never count as activity.

Usage Quantity is the quantity issued out of stock over the Usage Months preceding the As of Date. Months of Supply stays empty where there was no usage at all, which identifies the items that have not moved once in the whole window.

Rows are per subinventory; Item On Hand and Item On Hand Value are the item totals across subinventories.

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, As of Date, No Activity Since, Usage Months, Ignore Adjustment Transactions, Subinventory, Item, Category Set 1, Category Set 2, Category Set 3

Oracle EBS Tables Used

mtl_onhand_quantities_detail, mtl_parameters, org_access_view, mtl_material_transactions, hr_all_organization_units_vl, mtl_secondary_inventories, mtl_system_items_vl, cst_item_costs, cst_cost_types, mtl_transaction_types

Report Categories

Enginatics

INV Onhand Quantities, CAC Inventory and Intransit Value (Period-End) - Discrete/OPM, CAC Inventory Pending Cost Adjustment - No Currencies, CAC Inventory Pending Cost Adjustment, CAC Onhand Lot Value (Real-Time), CAC ICP PII Inventory Pending Cost Adjustment, CAC Inventory Lot and Locator OPM Value (Period-End), CAC ICP PII vs. Item Costs, CAC Zero Item 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 Slow Moving Inventory 16-Aug-2026 153453.xlsx
Blitz Report™ XML Import INV_Slow_Moving_Inventory.xml
SQL Source INV Slow Moving Inventory.sql
Full Report on Enginatics www.enginatics.com/reports/inv-slow-moving-inventory/

Case Study & Technical Analysis: INV Slow Moving Inventory

Executive Summary

The INV Slow Moving Inventory report identifies slow moving, excess and obsolete stock in Oracle Inventory. It lists items that still hold on-hand quantity at a chosen as-of date but have had no material movement over a chosen period, and shows their value, usage rate and months of supply. Inventory controllers and finance teams use it to size obsolescence provisions and to decide which stock should be reviewed, written down or redeployed. The on-hand position is rebuilt as at the as-of date, so the figures are historical rather than current. The report runs in Excel through Blitz Report.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. The innermost query unions current on-hand rows with material transactions of the accessible organizations, excluding logical transactions.
  2. Each transaction contributes its reversal to on-hand when dated after the as-of date, usage when in the usage window and of an issue action, and a last transaction date when it falls before the as-of date.
  3. Rows are aggregated by organization, item and subinventory, then by organization and item with analytic sums.
  4. The item-level result joins to the organization, subinventory, item master, item cost and transaction type.
  5. Only rows with on-hand not equal to zero and no activity in the window are returned, ordered by organization, item value descending, item and subinventory.

Parameters & Filtering

Performance & Optimization

FAQ

Q: Why are the on-hand quantities different from the current balances? A: On-hand is rebuilt as at the As of Date by reversing every material transaction posted after that date, so it matches the historical position.

Q: What does an empty Months of Supply value mean? A: It means the item had no usage in the usage window, so months of supply cannot be calculated.

Q: How do adjustments affect whether an item is reported as slow moving? A: When Ignore Adjustment Transactions is Y, cycle count, physical inventory and cost update transactions do not count as activity, so an item with only adjustments can still be reported.

© Enginatics GmbH