CAC Last Standard Item Cost – Oracle EBS SQL Report

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

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. – +=============================================================================+*/

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

mtl_category_sets_vl, mtl_categories_v, 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, bom_structures_b, bom_operational_routings, mrp_sr_receipt_org, mrp_sr_source_org, mrp_sourcing_rules, mrp_sr_assignments, mrp_assignment_sets, cst_standard_costs, cst_cost_updates, org_access_view

Report Categories

Enginatics

CAC New Standard Item Costs, CAC Inventory and Intransit Value (Period-End) - Discrete/OPM, 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), PO Headers and Lines

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_Last_Standard_Item_Cost.xml
Full SQL 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.

© 2026 Enginatics