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™. Oracle EBS module: Cost Accounting · all Cost Accounting reports

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. – | 1.10 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. – +=============================================================================+*/

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

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

Oracle EBS Tables Used

cst_item_cost_details, bom_resources, bom_structures_b, bom_operational_routings, mrp_sr_assignments, mrp_assignment_sets, mrp_sr_receipt_org, mrp_sr_source_org, mtl_onhand_quantities_detail, mtl_parameters, hr_organization_information, hr_all_organization_units, hr_all_organization_units_vl, gl_ledgers, cst_item_costs, cst_cost_types, mtl_system_items_vl, xxen_report_org_access

Report Categories

Enginatics

CAC Cost vs. Planning Item Controls, CAC Inventory Organization Summary, CAC New Items, CAC Calculate ICP PII Item Costs by Where Used, CAC Last Standard Item Cost, CAC New Standard Item Costs, CAC Item vs. Component Include in Rollup Controls, MRP Pegging, CAC Intercompany SO Price List vs. Item Cost Comparison

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 User-Defined and Rolled Up Costs 12-Jan-2023 132132.xlsx
Blitz Report™ XML Import CAC_User_Defined_and_Rolled_Up_Costs.xml
SQL Source CAC User-Defined and Rolled Up Costs.sql
Full Report 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.

© Enginatics GmbH