INV Subinventories – 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
Profile subinventory report with subinventory name, description, status, default cost group, type, restriction attributes, and general ledger account linkages. For BR100.
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
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_organization_definitions, xxen_report_org_access
Report Categories
Related Reports
INV Aging, INV Items, INV Material Transactions, INV Material Account Distribution Detail, INL Duty and Tariff Analysis, INL Landed Unit Cost Trend, INL Landed Cost Estimated vs Actual
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 Subinventories 18-Jan-2018 222405.xlsx |
| Blitz Report™ XML Import | INV_Subinventories.xml |
| SQL Source | INV Subinventories.sql |
| Full Report 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
© Enginatics GmbH