Skip to the content.

CAC New Standard Item Costs – 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 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. – | 1.4 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

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

bom_structures_b, bom_operational_routings, mrp_sr_assignments, mrp_sr_receipt_org, mrp_sr_source_org, mtl_category_sets_vl, mtl_categories_v, mtl_parameters, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, cst_standard_costs, mtl_system_items_vl, mtl_item_status_vl, cst_item_costs, cst_cost_updates, cst_cost_types, gl_code_combinations_kfv, xxen_report_org_access

Report Categories

Enginatics

CAC Last Standard Item Cost, CAC Cost vs. Planning Item Controls, CAC New Items, CAC Item vs. Component Include in Rollup Controls, CAC Inventory Organization Summary, CAC User-Defined and Rolled Up Costs, CAC Intercompany SO Price List vs. Item Cost Comparison, CAC Calculate ICP PII Item Costs by Where Used, INV Items

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 Example
Blitz Report™ XML Import CAC_New_Standard_Item_Costs.xml
SQL Source CAC New Standard Item Costs.sql
Full Report 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.


© Enginatics GmbH