CAC New Standard Item Costs – Oracle EBS SQL Report

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

Overview

Report to show items which have recent item cost changes, including various item controls, last cost update submission, new standard item cost, prior item cost and current (Frozen) item cost, by cost update date. And whether or not BOMs, routings or (inventory organization) sourcing rules exist for the item. As allowed with no onhand or intransit quantities, if an item cost has been directly entered into the Frozen Cost Type, the Cost Update Request Id, Cost Update Description, Cost Update Status and related cost update submission columns will be blank or empty.

Parameters: Cost Update Date From: starting cost update date, based on standard cost submission history (required). Cost Update Date To: ending cost update date, based on standard cost submission history (required). From Cost Type: enter the cost type implemented by the Standard Cost Update into the Frozen costs (optional). 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). 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 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_new_standard_item_cost_rept.sql – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 29 Sep 2023 Douglas Volz Initial coding based on the Cost Update – | Submissions report. – | 1.1 30 Oct 2023 Douglas Volz Added Item Created By, Last Updated By – | and BOM/Routing/Sourcing Rules exist columns. – | 1.2 22 Nov 2023 Douglas Volz Add item master and costing lot sizes, and – | use default controls columns. – | 1.3 05 Dec 2023 Douglas Volz Added G/L and Operating Unit security restrictions. – +=============================================================================+*/

Report Parameters

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

Oracle EBS Tables Used

mtl_category_sets_vl, mtl_categories_v, cst_standard_costs, cst_item_costs, cst_cost_types, mtl_parameters, mtl_system_items_vl, mtl_item_status_vl, mtl_units_of_measure_vl, gl_code_combinations_kfv, fnd_common_lookups, mfg_lookups, fnd_lookups, fnd_user, gl_ledgers, hr_organization_information, hr_all_organization_units_vl, cst_cost_updates, bom_structures_b, bom_operational_routings, mrp_sr_receipt_org, mrp_sr_source_org, mrp_sourcing_rules, mrp_sr_assignments, mrp_assignment_sets, 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, PO Headers and Lines, CAC Inventory Pending Cost Adjustment - No Currencies, CAC ICP PII Inventory Pending Cost Adjustment, 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

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_New_Standard_Item_Costs.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-new-standard-item-costs/

Case Study & Technical Analysis: CAC New Standard Item Costs

Executive Summary

The CAC New Standard Item Costs report is a change management tool for Standard Costing. It focuses specifically on the “Standard Cost Update” event—the moment when a “Pending” cost becomes “Frozen”. It allows the finance team to review exactly what changed during the last cost roll.

Business Challenge

Solution

This report links the Item to the Cost Update history.

Technical Architecture

Parameters

Performance

FAQ

Q: Does this show manual cost updates? A: If the manual update was done via the “Interface” or “Cost Update” screen, yes. If it was done via direct SQL (not recommended), it might not appear here.

Q: Why is the “Prior Cost” zero? A: If it’s a new item receiving its first standard cost, the prior cost is zero.

© 2026 Enginatics