CAC Item Cost Summary – Oracle EBS SQL Report

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

Overview

Report to show item costs in any cost type. For one or more inventory organizations.

Parameters:

Cost Type: enter the cost type(s) you wish to report (mandatory). Include Uncosted Items: enter Yes to include non-costed items, enter No to exclude them (mandatory). 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). 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 2009-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) – | – | Description: – | Report to show item costs in any cost type – | – | 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 07 Feb 2011 Douglas Volz Added COGS and Revenue default accounts – | 1.4 15 Nov 2016 Douglas Volz Added category information – | 1.5 27 Jan 2020 Douglas Volz Added Org Code and Operating Unit parameters – | 1.6 27 Apr 2020 Douglas Volz Changed to multi-language views for the item – | master, inventory orgs and operating units – | 1.7 21 Jun 2020 Douglas Volz Changed to multi-language views for item – | status and UOM – | 1.8 24 Sep 2020 Douglas Volz Added List Price and Market Price to report – | 1.9 29 Jan 2021 Douglas Volz Added item master dates and Inactive Items parameter – | 1.10 22 Nov 2023 Douglas Volz Add item master std lot size, costing lot size, – | remove tabs and add org access controls – | 1.11 05 Dec 2023 Douglas Volz Added G/L and Operating Unit security restrictions. – | 1.12 30 Jun 2024 Douglas Volz Reinstalled missing parameter for Item Status to Exclude – | and commented out G/L and Operating Unit security restrictions. – +=============================================================================+*/

Report Parameters

Cost Type, Include Uncosted Items, 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, R12 only

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

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 Summary 07-Jul-2022 150443.xlsx
Blitz Report™ XML Import CAC_Item_Cost_Summary.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-item-cost-summary/

Case Study & Technical Analysis: CAC Item Cost Summary

Executive Summary

The CAC Item Cost Summary report is the workhorse of cost reporting. It provides a straightforward, comprehensive list of item costs for a specified Cost Type and Organization. It is the “go-to” report for answering basic questions about inventory value, standard costs, and item attributes.

Business Challenge

Users need a simple, reliable way to extract cost data.

Solution

This report provides a flat, filterable list of item costs.

Technical Architecture

The report queries the primary costing view:

Parameters

Performance

FAQ

Q: Does this show the breakdown of resources? A: No, it shows the total Resource cost. For a breakdown by specific resource (e.g., Labor vs. Machine), use the “Item Cost & Routing” report.

Q: Why is the cost zero? A: Either the item is new and hasn’t been costed, or it is an Expense item (Inventory Asset Flag = No) which is not tracked with a value in inventory.

Q: Can I use this for Average Costing? A: Yes, simply select the “Average” cost type.

© 2026 Enginatics