CAC User-Defined and Rolled Up Costs – Oracle EBS SQL Report

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

Overview

Use this report to find items with both user-defined (manually entered) and rolled up costs, for material and other cost elements. Useful to find rolled up assemblies where the item costs have been accidentally doubled-up.

Parameters:

Cost Type: enter the cost type to report (mandatory). 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). Assignment Set: enter the sourcing rule assignment set, used when transferring items between inventory organizations on internal requisitions. 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 2010-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_user_defined_rolled_up_cost_rept.sql – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 29 Dec 2010 Douglas Volz Create new report to find items with – | both manually entered and rolled up material costs – | 1.1 24 May 2011 Douglas Volz Bug fix for the resource code column – | 1.2 19 Oct 2019 Douglas Volz Add columns for non-material costs – | 1.3 27 Jan 2020 Douglas Volz Added Operating_Unit parameter and outer – | join for Item_Type. – | 1.4 05 May 2021 Douglas Volz Modify for multi-language tables. – | 1.5 12 Jan 2023 Douglas Volz Correction for definition of manually entered – | material costs. – | 1.6 20 Nov 2023 Douglas Volz Fix for Manual Other Costs and Rolled Up Other Costs columns. – | 1.7 05 Dec 2023 Douglas Volz Added G/L and Operating Unit security restrictions. – | 1.8 07 Jan 2024 Douglas Volz Add onhand quantities, to help find valuation issues. Remove tabs. – +=============================================================================+*/

Report Parameters

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

Oracle EBS Tables Used

bom_resources, cst_item_cost_details, mfg_lookups, fnd_lookups, bom_structures_b, bom_operational_routings, mrp_sr_receipt_org, mrp_sr_source_org, mrp_sourcing_rules, mrp_sr_assignments, mrp_assignment_sets, mtl_parameters, mtl_system_items_vl, mtl_units_of_measure_vl, mtl_item_status_vl, cst_item_costs, cst_cost_types, mtl_onhand_quantities_detail, fnd_common_lookups, hr_organization_information, hr_all_organization_units, 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 New Standard Item Costs, CAC Cost vs. Planning Item Controls, CAC Inventory Organization Summary, CAC Item vs. Component Include in Rollup Controls, CAC New Items, CAC Calculate ICP PII Item Costs by Where Used, CAC Intercompany SO Price List vs. Item Cost Comparison, CAC Calculate ICP PII Item Costs

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 CAC User-Defined and Rolled Up Costs 12-Jan-2023 132132.xlsx
Blitz Report™ XML Import CAC_User_Defined_and_Rolled_Up_Costs.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-user-defined-and-rolled-up-costs/

Case Study & Technical Analysis: CAC User-Defined and Rolled Up Costs

Executive Summary

The CAC User-Defined and Rolled Up Costs report is a data integrity tool for Standard Costing. It identifies items that have conflicting cost definitions: a manually entered (“User Defined”) cost and a system-calculated (“Rolled Up”) cost. This often indicates a setup error that leads to incorrect costing.

Business Challenge

Solution

This report finds the intersection.

Technical Architecture

Parameters

Performance

FAQ

Q: Is it ever valid to have both? A: Yes, for “Value Add”. You might roll up the Material cost from components but manually enter a “Material Overhead” or “Labor” cost at the assembly level.

© 2026 Enginatics