CAC Zero Item Costs – Oracle EBS SQL Report

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

Overview

Report to show zero item costs in the Costing Method cost type, the creation date, the last transaction id, last transaction date and any onhand stock, based on the item master creation date.

Parameters: Creation Date From: starting item master creation date (required). Creation Date To: ending item master creation date (required). Category Set 1: any item category you wish, typically the Cost or Product Line category set (optional). Category Set 2: any item category you wish, typically the Inventory category set (optional).S Item Number: enter the specific item number(s) 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 2010 - 2023 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_zero_item_cost_rept.sql – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 11 Mar 2010 Douglas Volz Initial Coding – | 1.1 29 Mar 2010 Douglas Volz Added item status and item type to the – | report and removed 9xx inventory orgs – | 1.2 05 Oct 2010 Douglas Volz Added Ledger parameter, updated column headings, – | added UOM_Code column, added union all to – | select items with no costs at all – | 1.3 10 Feb 2017 Douglas Volz Removed client-specific org restrictions – | 1.4 22 May 2017 Douglas Volz Added product type, business code, product family, – | product line and package code item categories – | 1.5 17 Jul 2018 Douglas Volz Revised to report any two item categories. – | 1.6 27 Jan 2020 Douglas Volz Added Org_Code and Operating_Unit parameters. – | 1.7 27 Apr 2020 Douglas Volz Changed to multi-language views for the item – | master, inventory orgs and operating units – | 1.8 07 Nov 2020 Douglas Volz Changed to multi-language for status and UOM – | 1.9 09 Jul 2023 Douglas Volz Remove tabs and restrict to only orgs you have – | access to, using the org access view. – +=============================================================================+*/

Report Parameters

Creation Date From, Creation Date To, Category Set 1, Category Set 2, Category Set 3, Item Number, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

mtl_material_transactions, mtl_transaction_types, mtl_onhand_quantities_detail, cst_item_costs, mtl_system_items_vl, mtl_units_of_measure_vl, mtl_item_status_vl, mtl_parameters, mfg_lookups, fnd_lookups, fnd_common_lookups, gl_ledgers, hr_organization_information, hr_all_organization_units_vl, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual, org_access_view

Report Categories

Enginatics

CAC New Items, CAC Cost vs. Planning Item Controls, CAC Inventory Pending Cost Adjustment, CAC Onhand Lot Value (Real-Time), CAC ICP PII Inventory Pending Cost Adjustment, CAC ICP PII WIP Material Usage Variance, CAC Inventory Lot and Locator OPM Value (Period-End), CAC Inventory and Intransit Value (Period-End) - Discrete/OPM, CAC User-Defined and Rolled Up Costs

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 Zero Item Costs 10-Jul-2022 170519.xlsx
Blitz Report™ XML Import CAC_Zero_Item_Costs.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-zero-item-costs/

Executive Summary

The CAC Zero Item Costs report is an inventory master data audit tool designed to identify items that have a standard cost of zero. While some items (like expense items or new parts) legitimately have zero cost, a “make” or “buy” part with zero cost in a standard costing environment can lead to significant accounting errors, such as zero-value inventory transactions and incorrect margins. This report helps the Cost Accounting team proactively monitor and fix missing costs before they cause transaction issues.

Business Challenge

In an ERP system, if an item has a cost of zero:

It is critical to identify these items before they are transacted. However, with thousands of items created automatically or manually, it is easy for cost setup to be missed.

Solution

This report scans the Item Master and Cost tables to find items where the total cost in the specified Cost Type (usually “Frozen” or “Standard”) is zero. It provides context such as the item’s creation date, last transaction date, and on-hand quantity to help prioritize which items need immediate attention.

Key Features:

Architecture

The query joins MTL_SYSTEM_ITEMS (Item Master) with CST_ITEM_COSTS (Cost table). It typically uses an outer join to find items that might not even have a record in the cost table yet.

Key Tables:

Impact

© 2026 Enginatics