INV Stock Locators – 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
Summary report for Inventory locations, showing locator number, description, type, status, subinventory, picking order, dropping order and unit, volume, weight, dimension and co-ordinate information.
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
Locator Type, Status, Subinventory, Organization Code
Oracle EBS Tables Used
mtl_item_locations_kfv, hr_all_organization_units_tl, mtl_parameters, mtl_item_locations, mtl_material_statuses_vl, org_access_view
Report Categories
Related Reports
CAC Inventory Lot and Locator OPM Value (Period-End), INV Items, INV Item Upload 11i, INV Onhand Quantities, INV Item Upload, INV Serial Numbers, INV Stock Locator Upload
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 Stock Locators 26-Jul-2018 134603.xlsx |
| Blitz Report™ XML Import | INV_Stock_Locators.xml |
| SQL Source | INV Stock Locators.sql |
| Full Report on Enginatics | www.enginatics.com/reports/inv-stock-locators/ |
INV Stock Locators - Case Study & Technical Analysis
Executive Summary
The INV Stock Locators report is a warehouse layout document. It lists all the defined storage locations (Locators) within the subinventories. It includes critical attributes like dimensions, weight capacities, and picking order. This report is essential for warehouse mapping and capacity planning.
Business Challenge
A warehouse is a physical space that needs to be mapped digitally.
- Capacity Planning: “Do we have enough empty bins to receive the incoming shipment?”
- Route Optimization: “Are the bins numbered in a way that makes sense for a picker walking the aisle?”
- Status Control: “Which bins are damaged and should be blocked from use?”
Solution
The INV Stock Locators report provides a complete list of valid addresses in the warehouse. It exposes the configuration details of each bin.
Key Features:
- Address Breakdown: Shows the segments of the locator (e.g., Aisle-Row-Bin-Level).
- Physical Constraints: Lists the max weight, volume, and dimensions of the bin.
- Operational Flags: Shows the Picking Order (sequence) and Status (Active/Inactive).
Technical Architecture
The report queries the locator definition table.
Key Tables and Views
MTL_ITEM_LOCATIONS: The table storing the locator definitions.MTL_SECONDARY_INVENTORIES: The subinventory the locator belongs to.MTL_MATERIAL_STATUSES: The status of the locator (e.g., “Quarantine”).
Core Logic
- Retrieval: Selects from
MTL_ITEM_LOCATIONS(often joined withMTL_ITEM_LOCATIONS_KFVfor the concatenated segments). - Filtering: Can filter by Subinventory, Locator Type, or Status.
- Sorting: Usually sorted by Subinventory and then Picking Order.
Business Impact
- Warehouse Optimization: Used to analyze and optimize the picking path (by adjusting the Picking Order field).
- Space Management: Helps identify underutilized or over-utilized areas of the warehouse.
- Master Data Management: Ensures the digital map matches the physical labels on the racks.
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