CST Inventory Value - Multi-Organization (Item Costs) – Oracle EBS SQL Report
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Cost Management · all Cost Management reports
Overview
Report: CST Inventory Value - Multi-Organization (Item Costs)
Description: The Inventory Value Report can be used to report Inventory Value by
- Elemental Item Cost level
- Quantity Type (Onhand, Intransit, Receiving)
The report can be used to analyze inventory value by Ledger, Operating Unit, Organization, Subinventory, Item Category, Cost Group
The report corresponds to the following standard Oracle Reports
- Elemental Inventory Value Report
- All Inventories Value Report
The report can be run across multiple Inventory Organizations
Templates are provided that match the existing standard Oracle Reports of the same name:
- All Inventories Value
- All Inventories Value by Cost Group
- Elemental Inventory Value
- Elemental Inventory Value by Subinventory
- Elemental Inventory Value by Cost Group
- Intransit Value Report
New parameter added: Show Shipment Details If this parameter is set to Y, columns related to shipment details will be included, such as Shipment Number, Ship Date, FOB Point, etc.
DB package: XXEN_INV_VALUE
Notes: To run the report including non-costed items the As of Date parameter must be left blank (current date). The report cannot be run historically when including non-costed items due to a bug in the Oracle API uses to populate the interminm costing tables used by the report. The API will error with the following error, however the report will complete but return no data: ORA-01403: no data found in Package CST_Inventory_PVT Procedure Calculate_InventoryCost
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
Quantity Type, Ledger, Operating Unit, Organization Code, Cost Type, As of Date, Costing Enabled Items Only, Item From, Item To, Category Set, Category From, Category To, Subinventory From, Subinventory To, Currency, Exchange Rate, Quantities By Revision, Negative Quantities only, Display Zero Costs only, Include Expense Items, Include Expense Subinventories, Include Zero Quantities, Include Unvalued Transactions, Show Shipment Details
Oracle EBS Tables Used
cst_inv_qty_temp, cst_inv_cost_temp, mtl_parameters, mtl_secondary_inventories, mtl_system_items_vl, mtl_categories_b_kfv, org_access_view, org_organization_definitions, hr_all_organization_units, gl_sets_of_books, cst_cost_groups
Report Categories
Related Reports
CST Subinventory Account Value - Multi-Org, INV Items, INV Material Transactions, INV Onhand Quantities, INV Transaction Historical Summary, INV Transaction Register, CAC Last Standard Item Cost, CAC New Standard Item Costs, GL Account Distribution Analysis
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 | CST Inventory Value - Multi-Organization (Item Costs) - Pivot Organization Subinventory Item 15-Mar-2024 020251.xlsx |
| Blitz Report™ XML Import | CST_Inventory_Value_Multi_Organization_Item_Costs.xml |
| SQL Source | CST Inventory Value - Multi-Organization (Item Costs).sql |
| Full Report on Enginatics | www.enginatics.com/reports/cst-inventory-value-multi-organization-item-costs/ |
Executive Summary
The CST Inventory Value - Multi-Organization (Item Costs) report is a comprehensive valuation tool that aggregates inventory data across multiple inventory organizations. Unlike the “Element Costs” version which focuses on the breakdown of cost components (Material, Labor, etc.), this report focuses on the total item cost and quantity types (Onhand, Intransit, Receiving). It serves as a consolidated “All Inventories Value Report” for the entire enterprise.
Business Challenge
Large enterprises with multiple warehouses (Organizations) often struggle to get a single, consolidated view of their total inventory asset.
- Fragmented Reporting: Standard Oracle reports must be run organization by organization, requiring manual aggregation in Excel.
- Intransit Visibility: Goods moving between warehouses (Intransit) are often missed in standard on-hand reports, leading to an understatement of assets.
- Period-End Reconciliation: Finance needs a snapshot of inventory value at a specific “As Of” date to support the month-end balance sheet.
Solution
This report provides a multi-org view of inventory value, capable of rolling back to a historical date.
Key Features:
- Quantity Types: Reports not just On-hand, but also Intransit and Receiving inspection quantities.
- Flexible Grouping: Can analyze value by Ledger, Operating Unit, Organization, Subinventory, or Cost Group.
- Historical Reporting: The “As of Date” parameter allows for retrospective valuation (though note the limitation on non-costed items).
Architecture
The report relies on the XXEN_INV_VALUE package and standard Oracle temporary tables CST_INV_QTY_TEMP and CST_INV_COST_TEMP to calculate quantities and costs dynamically.
Key Tables:
CST_INV_QTY_TEMP: Stores calculated quantities based on the “As Of” date.CST_INV_COST_TEMP: Stores calculated costs.MTL_SYSTEM_ITEMS_VL: Item master data.ORG_ORGANIZATION_DEFINITIONS: Organization hierarchy.
Impact
- Consolidated Financials: Provides the “big picture” number for total inventory assets across the company.
- Supply Chain Visibility: Highlights how much capital is tied up in transit between locations.
- Efficiency: Eliminates the need to run dozens of separate reports for each warehouse.
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