CAC Resources by Department Setup – Oracle EBS SQL Report

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

Overview

Report to show which resources are assigned to which departments. With the respective resource rates as well.

/* +=============================================================================+ – | Copyright 2016 - 2022 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_resources_by_dept.sql – | – | Parameters: – | p_org_code – Specific inventory organization you wish to report (optional) – | p_operating_unit – Operating Unit you wish to report, leave blank for all – | operating units (optional) – | p_ledger – general ledger you wish to report, leave blank for all – | ledgers (optional) – | p_cost_type – Enter Pending or Frozen or AvgRates for the Cost Type.
– | Optional, defaults to your AvgRates or Frozen Cost Type, – | depending on your Costing Method. – | Description: – | Report to show which resources are assigned to which departments. – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 21 Jan 2017 Douglas Volz Initial Coding – | 1.1 20 Jan 2020 Douglas Volz Add operating unit and org code parameters. – | 1.2 20 Apr 2020 Douglas Volz Make Cost Type default work for all cost methods – +=============================================================================+*/

Report Parameters

Resource/Overhead Cost Type, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

cst_activities, bom_departments, bom_resources, bom_department_resources, cst_resource_costs, cst_cost_types, cst_cost_elements, mtl_parameters, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, mfg_lookups, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual, org_access_view

Report Categories

Enginatics

CAC Item Cost & Routing, CAC Manufacturing Variance, CAC Resources Associated with Overheads Setup, CAC WIP Account Detail, CAC ICP PII WIP Material Usage Variance, CAC Department Overhead Setup, CAC Department Overhead Setup Errors, CAC Resource Costs, CAC WIP Resource Efficiency

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 Resources by Department Setup 08-Jul-2022 195451.xlsx
Blitz Report™ XML Import CAC_Resources_by_Department_Setup.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-resources-by-department-setup/

Case Study & Technical Analysis: CAC Resources by Department Setup

Executive Summary

The CAC Resources by Department Setup report validates the manufacturing topology. It shows which Resources are available in which Departments. This setup controls both Scheduling (Capacity Planning) and Costing (Departmental Rates).

Business Challenge

Solution

This report lists the assignments.

Technical Architecture

Parameters

Performance

FAQ

Q: Can a resource be in multiple departments? A: Yes, “General Labor” might be assigned to every department. “Specialized CNC” might be in only one.

© 2026 Enginatics