CAC Items Without This Level Material Overhead – Oracle EBS SQL Report

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

Overview

Report to show item costs which do not have this level material overhead, for any cost type. For one or more inventory organizations.

Parameters:

Cost Type: enter the cost type(s) you wish to report (mandatory). Make or Buy: enter Buy, Make or leave blank to get all items (optional). Based on Rollup: enter Yes to get the rolled up items, No to get the non-rolled-up items (optional). Basis Type: enter the basis type you wish to report, such as Item, Lot, Res Units, Res Value, or Ttl Value (optional). Item Status to Exclude: enter the item status you wish to exclude, defaulted to Inactive (optional). Category Set 1: the first item category set to report, typically the Cost or Product Line Category Set (optional). Category Set 2: the second item category set to report, typically the Inventory Category Set (optional). Category Set 3: the third item category set to report (optional). Item Number: enter the item numbers you wish to report (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 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) – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 15 Nov 2024 Douglas Volz Initial Coding based on the Item Cost Summary Report. – | 1.1 08 Dec 2024 Douglas Volz Added Ledger and Operating Unit security profiles. – +=============================================================================+*/

Report Parameters

Cost Type, Make or Buy, Based on Rollup, Item Status to Exclude, Category Set 1, Category Set 2, Category Set 3, Item Number, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

cst_item_costs, cst_cost_types, mtl_system_items_vl, mtl_item_status_vl, mtl_units_of_measure_vl, mtl_parameters, mfg_lookups, fnd_lookups, fnd_common_lookups, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, gl_code_combinations, org_access_view, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual

Report Categories

Enginatics

CAC Last Standard Item Cost, CAC Material Account Summary, CAC Inventory and Intransit Value (Period-End), CAC ICP PII Inventory and Intransit Value (Period-End), CAC Inventory and Intransit Value (Period-End) - Discrete/OPM, CAC New Standard Item Costs, CAC Missing Material Accounting Transactions, CAC User-Defined and Rolled Up Costs, CAC Cost vs. Planning Item Controls, CAC Item Cost 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 None
Blitz Report™ XML Import CAC_Items_Without_This_Level_Material_Overhead.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-items-without-this-level-material-overhead/

Case Study & Technical Analysis: CAC Items Without This Level Material Overhead

Executive Summary

The CAC Items Without This Level Material Overhead report is a revenue leakage prevention tool. It identifies items that are missing “Material Overhead” (MOH). For many manufacturing and distribution companies, MOH is the mechanism used to recover freight, handling, and procurement costs. If an item lacks this cost element, the company is effectively subsidizing these expenses.

Business Challenge

Solution

This report acts as a “completeness check”.

Technical Architecture

The report queries the detailed cost table:

Parameters

Performance

FAQ

Q: Is Material Overhead mandatory? A: No, it depends on your company’s costing policy. Some companies expense freight and handling directly to the P&L rather than capitalizing it into inventory.

Q: Can I use this for OPM? A: This report is designed for Discrete Costing. OPM handles overheads through a different mechanism (Cost Factors).

Q: What if I use Total Value basis? A: The report checks for the existence of the cost element. If you use Total Value basis, the element should still exist, even if the calculated amount varies.

© 2026 Enginatics