CAC Inactive Items Set to Roll Up – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Report to show items which are set to roll up even though these items have an inactive status. The Cost Rollup uses the Based on Rollup Flag to control if an item is rolled up, as opposed to the item status.

Parameters:

Cost Type: enter the cost type to report (mandatory). Defaults to your Costing Method cost type. Inactive Item Status: enter the item statuses which should not be rolled up (mandatory). Defaults to ‘Inactive’. Category Set 1: any item category you wish, typically the Cost or Product Line category set (optional). Category Set 2: any item category you wish, typically the Inventory category set (optional). Item Number: enter the specific item number(s) you wish to report (optional). 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 2023 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. – +=============================================================================+ – | – | Original Author: Douglas Volz (doug@volzconsulting.com) – | – | Program Name: xxx_inactive_items_set_to_rollup_rept.sql – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 15 Nov 2023 Douglas Volz Initial version – | 1.1 05 Dec 2023 Douglas Volz Added G/L and Operating Unit security restrictions. – +=============================================================================+*/

Report Parameters

Cost Type, Inactive Item Status, Category Set 1, Category Set 2, Category Set 3, Item Number, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

cst_item_costs, cst_cost_types, mtl_parameters, mtl_system_items_vl, mtl_item_status_vl, mtl_units_of_measure_vl, mfg_lookups, fnd_lookups, fnd_common_lookups, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, org_access_view, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual

Report Categories

Enginatics

CAC Last Standard Item Cost, CAC Material Account Summary, 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, CAC New Standard Item Costs, CAC Missing Material Accounting Transactions, CAC User-Defined and Rolled Up Costs, CAC Cost vs. Planning Item Controls, CAC Item Cost Summary

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
Blitz Report™ XML Import CAC_Inactive_Items_Set_to_Roll_Up.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-inactive-items-set-to-roll-up/

Case Study & Technical Analysis: CAC Inactive Items Set to Roll Up

Executive Summary

The CAC Inactive Items Set to Roll Up report is a specialized inventory and costing diagnostic tool designed to ensure the accuracy of standard cost rollups. It identifies a specific data quality issue where items marked as “Inactive” are still flagged to be included in cost rollups. By detecting these inconsistencies, the report helps organizations prevent invalid or obsolete items from distorting product costs and inventory valuations.

Business Challenge

In Oracle EBS Cost Management, the “Based on Rollup” flag determines if an item’s cost is calculated during a standard cost rollup. A common configuration error occurs when an item is retired (set to Inactive status) but the “Based on Rollup” flag remains set to “Yes”.

Solution

This report provides a targeted exception list to proactively manage item master data quality.

Technical Architecture

The report is built on a robust SQL query that joins key Inventory and Costing tables:

Parameters

Performance

The report is optimized for large item masters:

FAQ

Q: Why does an inactive item affect my rollup? A: If an inactive item is a component in an active Bill of Material (BOM) and “Based on Rollup” is Yes, the rollup process will attempt to cost it, potentially using outdated purchase prices or routing data.

Q: How do I fix the items identified? A: You should update the Item Master for these items, unchecking the “Based on Rollup” flag in the Costing tab.

Q: Can I use this for statuses other than ‘Inactive’? A: Yes, the “Inactive Item Status” parameter allows you to check for any status code (e.g., ‘Obsolete’, ‘Phase-Out’).

© 2026 Enginatics