INV Item Supply/Demand – 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
Inventory Item Supply/Demand data as per the standard Inventory Item Supply/Demand form
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, Item Number Contains, Item From, Item To, Category Set, Category, Planner, Cutoff Date, Include Onhand Source
Oracle EBS Tables Used
po_headers_all, mtl_sales_orders_kfv, gl_code_combinations_kfv, wip_entities, mtl_generic_dispositions_kfv, rcv_shipment_headers, po_requisition_headers_all, mrp_schedule_dates, mtl_txn_request_headers, mtl_supply_demand_temp, mtl_system_items_vl, org_organization_definitions
Report Categories
Related Reports
INV Material Transactions, GL Account Analysis, GL Account Analysis (Drilldown) (with inventory and WIP), GL Account Distribution Analysis, INV Material Account Distribution Detail, INV Item Upload 11i, INV Items, INV Item Upload, INV 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 | INV Item Supply Demand 11-May-2021 190750.xlsx |
| Blitz Report™ XML Import | INV_Item_Supply_Demand.xml |
| SQL Source | INV Item Supply-Demand.sql |
| Full Report on Enginatics | www.enginatics.com/reports/inv-item-supply-demand/ |
INV Item Supply-Demand - Case Study & Technical Analysis
Executive Summary
The INV Item Supply-Demand report is the most comprehensive view of an item’s availability. It replicates the logic of the “Supply/Demand” form in Oracle Inventory. It lists all incoming supply (On-hand, Approved POs, WIP Jobs, In-transit Shipments) and all outgoing demand (Sales Orders, WIP Requirements, Reservations) to calculate the net “Available to Promise” (ATP) quantity over time.
Business Use Cases
- Shortage Analysis: The first place a planner looks when an item is short. It answers “When is the next PO arriving?” and “Who is consuming the stock?”.
- ATP Verification: Validates the system’s promise date to customers.
- Excess Inventory: Identifies items where Supply far exceeds Demand.
Technical Analysis
Core Tables
MTL_SUPPLY_DEMAND_TEMP: This is a special temporary table. The report logic (or the form) populates this table on-the-fly by queryingPO_HEADERS_ALL,OE_ORDER_LINES_ALL,WIP_REQUIREMENT_OPERATIONS,MTL_ONHAND_QUANTITIES, etc.MTL_SYSTEM_ITEMS_VL: Item master.
Key Joins & Logic
- Union of Everything: The underlying logic is a massive
UNION ALLquery that brings together every possible source of supply and demand. - Netting: It sorts all transactions by date and calculates a running total (Cumulative Quantity).
- Cutoff: Transactions past the cutoff date are ignored.
Key Parameters
- Item: The specific item to analyze.
- Cutoff Date: How far into the future to look.
- Include Onhand Source: Whether to include current stock as the starting balance.
Useful Links
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© Enginatics GmbH