INV Subinventories – Oracle EBS SQL Report
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.
Overview
Profile subinventory report with subinventory name, description, status, default cost group, type, restriction attributes, and general ledger account linkages. For BR100.
Report Parameters
Organization Code
Oracle EBS Tables Used
mtl_parameters, hr_all_organization_units_vl, mtl_secondary_inventories_fk_v, hr_locations_all, fnd_territories_vl, mtl_units_of_measure_vl, org_access_view
Report Categories
Related Reports
CAC Onhand Lot Value (Real-Time), CAC Missing Material Accounting Transactions, CAC Inventory Pending Cost Adjustment - No Currencies, CAC ICP PII Material Account Detail, CAC ICP PII Inventory Pending Cost Adjustment, CAC Inventory Lot and Locator OPM Value (Period-End), CAC Inventory and Intransit Value (Period-End), CAC ICP PII Inventory and Intransit Value (Period-End), CAC Inventory and Intransit Value (Period-End) - Discrete/OPM
Running This SQL Without Blitz Report
Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.
Download & Import Options
| Resource | Link |
|---|---|
| Excel Example Output | INV Subinventories 18-Jan-2018 222405.xlsx |
| Blitz Report™ XML Import | INV_Subinventories.xml |
| Full SQL on Enginatics | www.enginatics.com/reports/inv-subinventories/ |
INV Subinventories - Case Study & Technical Analysis
Executive Summary
The INV Subinventories report is a configuration audit document. It lists all the subinventories (storage areas) defined within an organization, along with their control parameters. This report is crucial for validating the physical layout of the warehouse against the system configuration.
Business Challenge
Subinventories are the primary “zones” of a warehouse (e.g., “Raw Materials”, “Finished Goods”, “Returns”). Incorrect setup leads to:
- Accounting Errors: If the “Expense” subinventory is linked to an Asset account, the balance sheet will be wrong.
- Process Failures: If a subinventory is not “Quantity Tracked”, stock will disappear from the books as soon as it is received.
- Planning Issues: If the “Nettable” flag is off, MRP will ignore the stock in that area, leading to unnecessary purchasing.
Solution
The INV Subinventories report provides a detailed view of each subinventory’s configuration. It serves as the “BR100” (Setup Document) for the warehouse structure.
Key Features:
- Control Flags: Shows Quantity Tracked, Asset Inventory, Nettable, and Include in ATP flags.
- Account Mapping: Displays the Material and Expense accounts linked to the subinventory.
- Locators: Indicates if the subinventory requires Locator control (Prespecified, Dynamic, or None).
Technical Architecture
The report queries the secondary inventory definition table.
Key Tables and Views
MTL_SECONDARY_INVENTORIES: The master table for subinventory definitions.MTL_PARAMETERS: Organization context.GL_CODE_COMBINATIONS: Account details.
Core Logic
- Retrieval: Selects all records from
MTL_SECONDARY_INVENTORIESfor the organization. - Decoding: Translates flags (1/2) into Yes/No.
- Context: Joins to the GL to show the account segments (e.g., 01-000-1210-0000).
Business Impact
- Setup Validation: Ensures that the system configuration matches the business design.
- Financial Integrity: Verifies that inventory value is flowing to the correct GL accounts.
- Operational Control: Confirms that restricted areas (like “Quarantine”) are properly flagged to prevent accidental usage.
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
© 2026 Enginatics