CAC Item Cost Out-of-Balance – Oracle EBS SQL Report

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

Overview

Report to compare summary and detail item cost information and show any out-of-balances. Any difference may cause an inventory reconciliation issue between the G/L and the inventory perpetual balances.

/* +=============================================================================+ – | Copyright 2009-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_item_cost_diff_rept.sql – | – | Parameters: None – | – | Description: – | Report to compare summary and detail item cost information and show any out-of-balances. – | Any difference may cause an inventory reconciliation issue between the G/L – | and the inventory perpetual balances. – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 06 Oct 2009 Douglas Volz Initial Coding – | 1.1 16 Mar 2010 Douglas Volz Updated with Make/Buy flags – | 1.2 08 Nov 2010 Douglas Volz Updated with additional columns and parameters – | 1.3 10 Dec 2012 Douglas Volz Compare summary and detail item cost information. – | 1.4 29 Apr 2020 Douglas Volz Changed to multi-language views for the item – | master, operating unit and lookup values. Add – | Ledger and Operating Unit columns and parameters. – +=============================================================================+*/

Report Parameters

Item Number, Category Set 1, Category Set 2, Category Set 3, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

cst_item_costs, cst_item_cost_details, 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, 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 ICP PII WIP Material Usage Variance, CAC Manufacturing Variance, CAC Inventory Lot and Locator OPM Value (Period-End), CAC Material Account Summary, CAC ICP PII Inventory and Intransit Value (Period-End), CAC Inventory and Intransit Value (Period-End) - Discrete/OPM, CAC New Standard Item Costs, CAC User-Defined and Rolled Up Costs, CAC Cost vs. Planning Item Controls

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 Item Cost Out-of-Balance 23-Jun-2022 212720.xlsx
Blitz Report™ XML Import CAC_Item_Cost_Out_of_Balance.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-item-cost-out-of-balance/

Case Study & Technical Analysis: CAC Item Cost Out-of-Balance

Executive Summary

The CAC Item Cost Out-of-Balance report is a technical integrity check for the Costing module. It verifies that the summary unit cost stored in the header table (cst_item_costs) matches the sum of the detailed cost elements stored in the detail table (cst_item_cost_details). Any discrepancy here indicates data corruption that can lead to serious accounting errors.

Business Challenge

Oracle Costing stores costs in two places: a Header (Total) and Details (Breakdown).

Solution

This report hunts for these “impossible” errors.

Technical Architecture

The report is a direct database integrity query:

Parameters

Performance

FAQ

Q: How does this happen? A: It is rare. It usually happens after a custom data migration, a patch application that failed mid-stream, or a manual update to the database tables by a DBA.

Q: How do I fix it? A: You usually need to run a “Cost Update” or “Cost Rollup” for the affected items to force the system to recalculate and resave the data correctly. In severe cases, an Oracle Data Fix is required.

Q: Is a small difference okay? A: No. Even a $0.00001 difference is a sign of corruption. The system is designed to be exact.

© 2026 Enginatics