CAC Department Overhead Setup – Oracle EBS SQL Report

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

Overview

Report to show the departments and the overhead codes assigned to each department. In addition, this report displays the corresponding overhead and resource rates.

Note: if a resource does not have a cost, the Resource Rate column has a blank or empty value.

Parameters:

Cost Type: enter the cost type you wish to use to report. If left blank, depending on your Costing Method, it defaults to your AvgRates or Frozen Cost Type (optional). 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). 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 2016 - 2024 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_setup.sql – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 18 Oct 2016 Douglas Volz Initial Coding – | 1.1 07 Nov 2016 Douglas Volz Added Department / Resource relationships – | 1.2 17 Jul 2018 Douglas Volz Made Cost Type parameter optional – | 1.3 12 Jan 2020 Douglas Volz Added Org Code and Operating Unit parameters – | and change to gl.short_name. Added Last – | rate update column to report. – | 1.4 27 Apr 2020 Douglas Volz Changed to multi-language views for the – | inventory orgs and operating units. – | 1.5 22 Jan 2024 Douglas Volz Add select statement for cst_resource_costs, overheads – | were not reported if the resource cost was missing. – | Added resource “Allow Costs” column. Removed – | tabs and added inventory access controls. – +=============================================================================+*/

Report Parameters

Cost Type, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

cst_department_overheads, cst_resource_overheads, bom_departments, bom_department_resources, cst_resource_costs, mtl_parameters, cst_cost_types, bom_resources, mfg_lookups, gl_code_combinations, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, org_access_view

Report Categories

Enginatics

CAC Manufacturing Variance, CAC Resources Associated with Overheads Setup, CAC Department Overhead Rates, CAC Department Overhead Setup Errors, CAC ICP PII WIP Material Usage Variance, CAC WIP Resource Efficiency, CAC Last Standard Item Cost, CAC New Standard 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 Department Overhead Setup 23-Jun-2022 145432.xlsx
Blitz Report™ XML Import CAC_Department_Overhead_Setup.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-department-overhead-setup/

Case Study & Technical Analysis: CAC Department Overhead Setup

Executive Summary

The CAC Department Overhead Setup report provides a comprehensive view of the relationship between Manufacturing Departments, Overhead codes, and the Resources that drive them. Unlike the simpler “Department Overhead Rates” report, this analysis focuses on the linkage (association) between Overheads and Resources. It validates that overheads are correctly attached to the productive resources (like Labor or Machine) within a department, ensuring that when labor is charged, the appropriate overhead is automatically applied.

Business Challenge

In Oracle EBS, overheads can be applied based on “Resource Units” or “Resource Value”. This requires a two-step setup:

  1. Define the Overhead and its Rate (Department Overhead).
  2. Associate the Overhead with a specific Resource (Resource Overhead).

Common configuration errors include:

The Solution

This report bridges the gap between the Department definition and the Costing definition by:

Technical Architecture (High Level)

The query joins the Department Overhead table with the Resource Overhead association table.

Parameters & Filtering

Performance & Optimization

FAQ

Q: Why is the “Resource Rate” blank for some rows? A: If the Resource Rate is blank, it means there is no record in CST_RESOURCE_COSTS for that resource in the selected Cost Type. This is a setup error if the overhead is based on “Resource Value”.

Q: What happens if an overhead is defined in CST_DEPARTMENT_OVERHEADS but not in CST_RESOURCE_OVERHEADS? A: It will likely not appear in this report (depending on the join type) or will appear with missing resource details. This report focuses on the setup of the association. If the association is missing, the overhead will never be charged to WIP.

Q: Can I use this for “Item” basis overheads? A: No, this report is specific to Department/Resource overheads. Item overheads are not linked to departments in this manner.

© 2026 Enginatics