| layout | default |
|---|---|
| title | CAC Inventory Organization Summary | Oracle EBS SQL Report |
| description | 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… |
| keywords | Oracle EBS, Oracle E-Business Suite, SQL report, Blitz Report, Enginatics, R12 only, CAC, Inventory, Organization, Summary, fnd_product_groups, hrfv_organization_hierarchies, mtl_parameters |
| permalink | /CAC%20Inventory%20Organization%20Summary/ |
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.
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.
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.
| -- +=============================================================================+ |
|---|
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| +=============================================================================+*/ |
Assignment Set, Hierarchy Name, Organization Code, Operating Unit, Ledger
fnd_product_groups, hrfv_organization_hierarchies, mtl_parameters, org_access, fnd_responsibility, oe_system_parameters_all, cst_cost_group_assignments, cst_cost_groups, bom_structures_b, gmd_recipes_b, bom_operational_routings, bom_parameters, mrp_sr_receipt_org, mrp_sr_source_org, mrp_sourcing_rules, mrp_sr_assignments, mrp_assignment_sets, cst_item_costs, gl_item_cst, gmf_fiscal_policies, cm_mthd_mst, mtl_onhand_quantities_detail, mtl_material_transactions, wip_transaction_accounts, wip_parameters, mtl_category_accounts, pjm_org_parameters, wsm_parameters, fnd_lookup_values_vl, po_system_parameters_all, cst_ap_po_reconciliation, cst_margin_summary, bom_resources, hr_organization_information, hr_all_organization_units_vl, mfg_lookups, fnd_user, gl_ledgers, org_access_view, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual
CAC Inventory and Intransit Value (Period-End) - Discrete/OPM, CAC Intercompany SO Price List vs. Item Cost Comparison, CAC OPM Costed Formula, CAC OPM Batch Material Summary, CAC Last Standard Item Cost, INV Item Upload, CAC ICP PII Inventory and Intransit Value (Period-End), CAC User-Defined and Rolled Up Costs, CAC Cost vs. Planning Item Controls
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.
| Resource | Link |
|---|---|
| Excel Example Output | CAC Inventory Organization Summary 19-Oct-2022 172501.xlsx |
| Blitz Report™ XML Import | CAC_Inventory_Organization_Summary.xml |
| Full SQL on Enginatics | www.enginatics.com/reports/cac-inventory-organization-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.
In large Oracle EBS implementations, the number of inventory organizations can grow into the hundreds.
- Configuration Drift: It becomes difficult to ensure that all "European Distribution Centers" are set up with identical parameters.
- Period Close Management: Identifying which organizations belong to which "Period Control" hierarchy is critical for ensuring a smooth month-end close.
- Costing Consistency: Verifying that all manufacturing plants are using the correct Costing Method (e.g., Standard vs. Average) requires tedious manual checking.
This report automates the documentation of the inventory topology.
- Hierarchy Visualization: Identifies the "Hierarchy Name" used for opening/closing periods, grouping organizations logically.
- Control Parameters: Displays key settings like Costing Method, General Ledger link, and Operating Unit assignment.
- Rollup Logic: Indicates if the org should be included in cost rollups based on the presence of BOMs or Routings.
The report queries the fundamental definition tables of Oracle Inventory:
- Org Definitions:
hr_all_organization_unitsandmtl_parameters. - Hierarchy:
per_organization_structuresandper_org_structure_versions(implied) to resolve the hierarchy relationships. - Business Logic: Contains logic to "guess" the correct hierarchy by looking for keywords like "Close" or "Period" if the user doesn't specify one.
- Hierarchy Name: (Optional) The specific hierarchy to analyze.
- Assignment Set: (Optional) To check for sourcing rule existence.
- Org Code: (Optional) Filter for specific orgs.
- Fast: This is a metadata report. It runs extremely fast as it queries setup tables rather than transaction tables.
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.
- 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