INV Onhand Quantities – 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

Detail report inventory item quantities by org, sub inventory, location, unit of measure, quantity on hand, quantity reserved, quantity unpacked, lot number, lot expiration, planning information, serial control, availability type, date received, list price, min / max and safety stock.

Show Serial Numbers adds one row per serial number resident in the on-hand location; the quantity and value columns are those of the location and repeat on every serial row.

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, Subinventory, Item, Category Set 1, Category Set 2, Category Set 3, Nettable only, Show Movements Summary, Txn Date From, ABC Assignment Group, Show Serial Numbers

Oracle EBS Tables Used

mtl_item_status_vl, mtl_abc_classes, mtl_abc_assignments, mtl_abc_assignment_groups, mtl_item_categories, mtl_categories_b_kfv, mtl_category_sets_vl, mtl_categories_b, mtl_safety_stocks, org_organization_definitions, mtl_onhand_quantities_detail, mtl_secondary_inventories, mtl_item_locations_kfv, mtl_material_statuses_vl, wms_license_plate_numbers, mtl_system_items_vl, mtl_units_of_measure_vl, ap_supplier_sites_all, ap_suppliers, mtl_parameters, mtl_lot_numbers, cst_cost_groups, pa_projects_all, pjm_seiban_numbers, pa_tasks, mtl_reservations, mtl_material_transactions, mtl_transaction_lot_numbers, cst_cost_types, cst_item_costs, xxen_report_org_access

Report Categories

Enginatics

INV Item Upload, INV Items, INV Item Upload 11i, INV Cycle counts pending approval, CST Inventory Value - Multi-Organization (Item Costs), CST Subinventory Account Value - Multi-Org, CAC Last Standard Item Cost, WSH Commercial Invoice, MRP Sourcing Rules and Bills of Distribution

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 Onhand Quantities 26-Jul-2018 134821.xlsx
Blitz Report™ XML Import INV_Onhand_Quantities.xml
SQL Source INV Onhand Quantities.sql
Full Report on Enginatics www.enginatics.com/reports/inv-onhand-quantities/

INV Onhand Quantities - Case Study & Technical Analysis

Executive Summary

The INV Onhand Quantities report is the definitive “Stock Status” report for Oracle Inventory. It provides a detailed snapshot of exactly what is in the warehouse right now. Unlike simple stock lists, this report exposes the rich attributes of on-hand inventory, including Lot numbers, Serial numbers, Reservations, LPNs (License Plates), and Statuses.

Business Challenge

Knowing “we have 100 units” is rarely enough. Operations teams need to know:

Solution

The INV Onhand Quantities report provides a multi-dimensional view of stock. It joins the core quantity tables with all the attribute tables to provide a complete picture of inventory health.

Key Features:

Technical Architecture

The report queries the live on-hand balance tables, which are the most critical and heavily indexed tables in the Inventory schema.

Key Tables and Views

Core Logic

  1. Balance Retrieval: Selects from MTL_ONHAND_QUANTITIES_DETAIL.
  2. Reservation Netting: Subqueries MTL_RESERVATIONS to determine how much of that stock is hard-allocated.
  3. Status Check: Checks MTL_MATERIAL_STATUSES to see if the stock is transactable.
  4. Costing: Can join to CST_ITEM_COSTS to provide the value of the on-hand stock.

Business Impact

© Enginatics GmbH