Skip to the content.

CAC Material Overhead Setup – 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

Report to show the material overhead sub-element definition and the default material overheads, if any.

/* +=============================================================================+ – | Copyright 2011 - 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_mtl_ovhd_setup_rept.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_only_active – include only active material overhead codes. Enter – | Yes (Yes) to return only active (non-disabled) material – | overhead codes. Enter No (No) to get all material – | overhead codes. – | – | Description: – | Report to show the material overhead sub-element definition and the default – | material overheads, if any. – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 05 Apr 2011 Douglas Volz Initial Coding – | 1.1 21 Feb 2016 Douglas Volz Modified Chart of Accounts to match client’s COA – | 1.2 17 Jul 2018 Douglas Volz Modified Chart of Accounts to match client’s COA – | 1.3 16 Jan 2020 Douglas Volz Add inventory org and operating unit parameters. – | 1.4 8 Apr 2020 Douglas Volz Fix for p_only_active parameter conditions and – | changed from fnd_lookup_values to mfg_lookups – | sys_yes_no for the Functional Currency column. – | Was duplicating rows. – | 1.5 28 Apr 2020 Douglas Volz Changed to multi-language views for the – | inventory orgs and operating units. – | 1.6 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

Active Only, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

mtl_parameters, hr_organization_information, hr_all_organization_units, hr_all_organization_units_vl, gl_ledgers, bom_resources, gl_code_combinations, cst_item_overhead_defaults, mtl_category_sets_vl, mtl_categories_b_kfv

Report Categories

Enginatics

CAC Last Standard Item Cost, CAC New Standard Item Costs, CAC Calculate Average Item Costs, CAC ICP PII Inventory Pending Cost Adjustment, CAC Calculate ICP PII Item Costs by Where Used, CAC Standard Cost Update Submissions, CAC New Items, CAC Intercompany SO Price List vs. Item Cost Comparison, GL Account Analysis

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 Material Overhead Setup 24-Jun-2022 080352.xlsx
Blitz Report™ XML Import CAC_Material_Overhead_Setup.xml
SQL Source CAC Material Overhead Setup.sql
Full Report on Enginatics www.enginatics.com/reports/cac-material-overhead-setup/

Case Study & Technical Analysis: CAC Material Overhead Setup

Executive Summary

The CAC Material Overhead Setup report is a configuration audit tool for the Cost Management module. It provides a detailed view of how Material Overheads (MOH) are defined and defaulted within the system. Material Overheads are indirect costs (like freight, handling, or purchasing administrative costs) applied to items as they are received into inventory. This report ensures that:

  1. Cost Recovery: Overhead rates are correctly set to recover indirect expenses.
  2. Consistency: Default rules (e.g., “All items in the ‘Electronics’ category get a 5% surcharge”) are applied consistently across organizations.
  3. Account Accuracy: The absorption accounts defined for these overheads map to the correct General Ledger accounts.

Business Challenge

Managing Material Overheads can be complex, especially in large organizations with multiple inventory sites.

The Solution

This report flattens the complex relationship between Resources, Overheads, and Defaulting Rules into a single, readable view.

Technical Architecture (High Level)

The query is built around the BOM_RESOURCES table, which is where Material Overheads are defined as “Resources” with a type of “Material Overhead”.

Parameters & Filtering

Performance & Optimization

FAQ

Q: What is the difference between “Item” basis and “Total Value” basis? A: “Item” basis means a fixed dollar amount is added to every unit received (e.g., $10 handling fee per unit). “Total Value” basis means a percentage is applied to the cost of the item (e.g., 5% freight charge on the PO price).

Q: Why do I see multiple lines for the same Overhead Code? A: You might have different default rules for different categories. For example, “Freight” might be 5% for “Hardware” but 10% for “Chemicals”. Each rule appears as a row.

Q: If I change the rate here, does it update existing inventory? A: No. This report shows the Setup and Defaults. Changing a default only affects future item definitions or cost updates. To change the value of existing inventory, you must run a Standard Cost Update.


© Enginatics GmbH