CAC Last Standard Item Cost – 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 the last standard item cost as of a specified cost update date; expecially useful if you have not saved off your Frozen costs at year-end, before updating to your new standard costs for the following year. Including various item controls, last cost update submission, last standard item cost and current (Frozen) item cost. And whether or not BOMs, routings or (inventory organization) sourcing rules exist for the item as of the specified cost update date. And 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.

This report complements the CAC New Standard Item Cost report. The CAC New Standard Item Cost report shows you the cost update history over a range of Standard Cost Revision Dates, useful to track standard cost changes on a weekly basis. The CAC Last Standard Item Cost report shows you how each item got its last Standard Item Cost as of a given Standard Cost Revision Date. Useful to assess when the current standard costs were created and by whom.

Parameters:

Cost Update Date To: ending cost update revision date, based on standard cost submission history (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). Item Status to Exclude: enter the item status to exclude from this report, defaults to Inactive (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 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) – | – | Program Name: xxx_last_standard_item_cost_rept.sql – | – | Description: – | Report to show the last standard cost for all items in an inventory organization, – | up to the entered Cost Update Date parameter. – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 15 Jan 2024 Douglas Volz Initial coding based on the New Standard Item Cost report. – | 1.1 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 To, Category Set 1, Category Set 2, Category Set 3, Item Status to Exclude, Assignment Set, Item Number, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

bom_structures_b, bom_operational_routings, mrp_sr_assignments, mrp_assignment_sets, 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_cost_types, cst_item_costs, mtl_system_items_vl, mtl_item_status_vl, cst_standard_costs, cst_cost_updates, gl_code_combinations_kfv, xxen_report_org_access

Report Categories

Enginatics

CAC New Standard Item Costs, 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 Item Upload

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_Last_Standard_Item_Cost.xml
SQL Source CAC Last Standard Item Cost.sql
Full Report on Enginatics www.enginatics.com/reports/cac-last-standard-item-cost/

Case Study & Technical Analysis: CAC Last Standard Item Cost

Executive Summary

The CAC Last Standard Item Cost report is a forensic costing tool. It reconstructs the history of the “Frozen” (Standard) cost by identifying the last time a Standard Cost Update was successfully run for each item. This is crucial for year-end audits and for understanding the provenance of the current standard cost.

Business Challenge

In a Standard Costing environment, the “Frozen” cost is static until explicitly updated.

Solution

This report links the current cost back to the update event.

Technical Architecture

The report joins the item cost to the standard cost history tables:

Parameters

Performance

FAQ

Q: Why is the “Last Update” date blank? A: If the item was created and costed via a direct interface or data load that didn’t use the standard “Cost Update” concurrent program, there may be no record in cst_cost_updates.

Q: Does this show the previous cost? A: It shows the cost as of the update. To see the previous cost, you would need to look at the history prior to that update.

Q: Is this useful for Average Costing? A: No, this is specific to Standard Costing (cst_standard_costs). Average costs change with every transaction.

© Enginatics GmbH