CAC Inventory Organization Summary – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Cost Accounting · all Cost Accounting reports

Overview

Report to show inventory org names, summary org controls, org hierarchy, operating unit and Ledger, and whether or not the Org should be rolled up for costing, based on the existence of BOMs, routings or org-level sourcing rules. Note: this report automatically looks for hierarchies which might be used with the Open Period Control and the Close Period Control Oracle programs. Looking for the translated values of “Close”, “Open” and “Period” in the Hierarchy Name.

Parameters:

Assignment Set: choose the Assignment Set to report for sourcing rules. You may leave this value null and the report still works (optional). Hierarchy Name: select the organization hierarchy used to open and close your inventory organizations (optional). If you leave this field blank the report automatically looks for hierarchies which might be used with the Open Period Control and the Close Period Control Oracle programs. Looking for the translated values of “Close”, “Open” and “Period” in the Hierarchy Name. Organization Code: enter the specific inventory organization(s) you wish to report (optional). Operating Unit: enter the specific operating unit(s) you wish to report (optional). Ledger: enter the specific ledger(s) you wish to report (optional).

/* +=============================================================================+ – | Copyright 2010-2025 Douglas Volz Consulting, Inc. – | All rights reserved. – | Permission to use this code is granted provided the original author is – | acknowledged. No warranties, express or otherwise is included in this permission. – | 1.31 24 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. – +=============================================================================+ – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 14 Apr 2010 Douglas Volz Initial Coding – | 1.19 09 Jul 2019 Douglas Volz Changed Org Hierarchy logic to look only for Hierarchy – | Names with “Open” or “Close” or “Period” in it. – | For the 2nd union all, added an Outer Join to OU: – | and haou2.organization_id (+) = to_number(hoi.org_information3) – | … found an inventory org in Vision with no OU – | 1.20 16 Jan 2020 Douglas Volz Added Ledger, Operating Unit and Org Code parameters. – | 1.21 02 Feb 2020 Douglas Volz Added max material and WIP transaction dates and removed – | flv.source_lang joins, not needed. – | 1.22 08 Mar 2020 Douglas Volz Checking for a routing for the parent org – | 1.23 07 Apr 2020 Douglas Volz Consolidated two (union all) statements into one. – | 1.24 27 Apr 2020 Douglas Volz Changed to multi-language views for the – | inventory orgs and operating units. – | 1.25 29 Jun 2022 Douglas Volz Fixed indicator for category accounts. – | 1.26 09 Sep 2022 Douglas Volz Added indicator for PAC Enabled. – | 1.27 13 Jul 2023 Douglas Volz Added condition to avoid SQL error, single-row subquery – | returns more than one row. – | 1.28 01 Nov 2024 Douglas Volz Added BOM Parameters, Use Phantom Routing column. – | 1.29 01 Jan 2025 Douglas Volz Added WIP Parameters, Record Scrap column. – | 1.30 15 Feb 2025 Douglas Volz Added Oracle Release Number. +=============================================================================+*/

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

Assignment Set, Hierarchy Name, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

fnd_product_groups, org_access, fnd_responsibility, oe_system_parameters_all, cst_cost_group_assignments, cst_cost_groups, bom_resources, gmd_recipes_b, bom_structures_b, bom_operational_routings, bom_parameters, mrp_assignment_sets, mrp_sr_assignments, mrp_sr_receipt_org, mrp_sr_source_org, gl_item_cst, cm_mthd_mst, gmf_fiscal_policies, cst_item_costs, mtl_onhand_quantities_detail, mtl_material_transactions, wip_transaction_accounts, wip_parameters, mtl_category_accounts, pjm_org_parameters, wsm_parameters, cst_ap_po_reconciliation, cst_margin_summary, mtl_parameters, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, po_system_parameters_all, hrfv_organization_hierarchies

Report Categories

Enginatics

CAC Last Standard Item Cost, INV Items, CAC New Standard Item Costs, CAC User-Defined and Rolled Up Costs, CAC Manufacturing Variance, CAC Cost vs. Planning Item Controls, INV Item Upload 11i, INV Item Upload, CAC New Items

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 CAC Inventory Organization Summary 19-Oct-2022 172501.xlsx
Blitz Report™ XML Import CAC_Inventory_Organization_Summary.xml
SQL Source CAC Inventory Organization Summary.sql
Full Report on Enginatics www.enginatics.com/reports/cac-inventory-organization-summary/

Case Study & Technical Analysis: CAC Inventory Organization Summary

Executive Summary

The CAC Inventory Organization Summary is a strategic infrastructure report that provides a bird’s-eye view of the Oracle Inventory landscape. It documents the configuration of every inventory organization, including its relationship to Operating Units and Ledgers, its costing method, and its place within the period-close hierarchy. This report is essential for System Administrators and Global Process Owners managing complex, multi-org environments.

Business Challenge

In large Oracle EBS implementations, the number of inventory organizations can grow into the hundreds.

Solution

This report automates the documentation of the inventory topology.

Technical Architecture

The report queries the fundamental definition tables of Oracle Inventory:

Parameters

Performance

FAQ

Q: What is the “Rollup” column? A: It’s a derived flag that suggests whether this organization should be part of a standard cost rollup, usually based on whether it has manufacturing data (BOMs/Routings).

Q: Why is the Hierarchy Name blank? A: If the organization is not assigned to the hierarchy specified (or found), the column will be blank.

Q: Can I use this to find inactive orgs? A: Yes, the report typically includes the “Date To” or active status of the organization.

© Enginatics GmbH