CAC Department Overhead Rates – Oracle EBS SQL Report

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

Overview

Report to show departmental overheads and rates

/* +=============================================================================+ – | Copyright 2016 - 2020 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_dept_ovhd_rates.sql – | – | Parameters: – | p_ledger – Ledger you wish to report, enter a null or blank for all – | ledgers. – | p_cost_type – Cost Type you wish to report, enter a null or blank for all – | cost types. – | – | Description: – | Report to show departmental overheads and rates. – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 17 Sep 2013 Douglas Volz Initial Coding – | 1.1 18 Oct 2016 Douglas Volz Added organization information – | 1.2 6 Sep 2019 Douglas Volz Added last update date – | 1.3 27 Jan 2020 Douglas Volz Added Operating Unit parameter – | 1.4 26 Apr 2020 Douglas Volz Changed to multi-language views for – | operating units.+=============================================================================+*/

Report Parameters

Cost Type, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

bom_departments, cst_department_overheads, bom_resources, cst_cost_types, mtl_parameters, gl_code_combinations, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, mfg_lookups, org_access_view, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual

Report Categories

Enginatics

CAC Manufacturing Variance, CAC Department Overhead Setup, CAC Department Overhead Setup Errors, CAC Resources Associated with Overheads Setup, CAC ICP PII WIP Material Usage Variance, CAC WIP Resource Efficiency, CAC WIP Account Summary, CAC WIP Account Detail, CAC Resources by Department Setup

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 Department Overhead Rates 23-Jun-2022 145254.xlsx
Blitz Report™ XML Import CAC_Department_Overhead_Rates.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-department-overhead-rates/

Case Study & Technical Analysis: CAC Department Overhead Rates

Executive Summary

The CAC Department Overhead Rates report is a configuration audit tool that displays the overhead rates assigned to manufacturing departments. In Oracle EBS Cost Management, overheads can be applied at the department level (based on resource usage) rather than just at the item level. This report provides a clear view of these rates, their basis (e.g., Resource Units, Resource Value), and the associated General Ledger absorption accounts, ensuring that indirect costs are being allocated correctly to Work in Process (WIP).

Business Challenge

Setting up departmental overheads involves linking multiple entities: Departments, Resources (Overheads), Cost Types, and Rates. Common challenges include:

The Solution

The report solves these challenges by:

Technical Architecture (High Level)

The query is a straightforward join of the Costing and Manufacturing definition tables.

Parameters & Filtering

Performance & Optimization

FAQ

Q: What is the difference between “Resource Units” and “Resource Value” basis? A: “Resource Units” means the overhead is applied as a fixed amount per hour (or unit) of the resource used (e.g., $50 overhead per 1 hour of Labor). “Resource Value” means it is applied as a percentage of the resource’s cost (e.g., 150% of the Labor rate).

Q: Why don’t I see Item-based overheads here? A: This report focuses on Departmental overheads, which are driven by routing operations. Item-based overheads (Material Overhead) are typically defined on the Item Master or Category and are reported separately (e.g., “CAC Material Overhead Setup”).

Q: Can I see the history of rate changes? A: The report shows the Last_Update_Date, which indicates when the rate was last modified. However, it does not show a full audit trail of prior values; it shows the current state for the selected Cost Type.

© 2026 Enginatics