CAC Item Cost Out-of-Balance – 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 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. – | 1.5 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

Item Number, Category Set 1, Category Set 2, Category Set 3, 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, mtl_system_items_vl, cst_item_costs, cst_cost_types, cst_item_cost_details, xxen_report_org_access

Report Categories

Enginatics

CAC Last Standard Item Cost, CAC WIP Resource Efficiency, CAC ICP PII WIP Pending Cost Adjustment, CAC ICP PII Inventory Pending Cost Adjustment, CAC ICP PII Item Cost Summary, CAC ICP PII WIP Material Usage Variance, CAC Manufacturing Variance, CAC ICP PII Inventory and Intransit Value (Period-End), CAC New Standard Item Costs, CAC User-Defined and Rolled Up Costs

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 Item Cost Out-of-Balance 23-Jun-2022 212720.xlsx
Blitz Report™ XML Import CAC_Item_Cost_Out_of_Balance.xml
SQL Source CAC Item Cost Out-of-Balance.sql
Full Report 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.

© Enginatics GmbH