CAC Material 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 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. – +=============================================================================+*/

Report Parameters

Active Only, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

gl_code_combinations, fnd_lookup_values, mfg_lookups, mtl_parameters, gl_ledgers, hr_organization_information, hr_all_organization_units_vl, bom_resources, cst_item_overhead_defaults, mtl_categories_b_kfv, mtl_category_sets_tl, org_access_view, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual

Report Categories

Enginatics

CAC Calculate Average Item Costs, CAC ICP PII Inventory Pending Cost Adjustment, CAC Intercompany SO Price List vs. Item Cost Comparison, CAC Last Standard Item Cost, CAC ICP PII Inventory and Intransit Value (Period-End), CAC New Standard Item Costs, CAC Calculate ICP PII Item Costs, CAC Material Account Summary, CAC WIP Account Summary

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 Material Overhead Setup 24-Jun-2022 080352.xlsx
Blitz Report™ XML Import CAC_Material_Overhead_Setup.xml
Full SQL 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.

© 2026 Enginatics