CST Detailed Item Cost – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Cost Management · all Cost Management reports

Overview

Detail report that lists each item and the associated costs to be recognized as part of total unit cost of producing the item.

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

Ledger, Organization Code, Cost Type, Category Set 1, Category Set 2, Category From, Category To, Item, Item From, Item To, Exclude Items with no Cost Details

Oracle EBS Tables Used

cst_activities, gl_ledgers, fnd_currencies, org_organization_definitions, mtl_parameters, cst_item_costs, cst_cost_types, cst_item_cost_details, mtl_system_items_vl, mtl_units_of_measure_vl, mtl_item_status_vl, bom_resources, org_access_view

Report Categories

Enginatics

Related Reports

CST Supply Chain Indented Bills of Material Cost, CAC Item Cost & Routing, CST Item Cost Reports, INV Items, CAC WIP Material Usage Variance, PO Internal Requisitions and Sales Orders, CAC Resource Costs, CAC Open Purchase Orders, CAC Item Cost Break-Out by Activity

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	CST Detailed Item Cost 25-Jun-2019 140038.xlsx
Blitz Report™ XML Import	CST_Detailed_Item_Cost.xml
SQL Source	CST Detailed Item Cost.sql
Full Report on Enginatics	www.enginatics.com/reports/cst-detailed-item-cost/

CST Detailed Item Cost - Case Study

Executive Summary

The CST Detailed Item Cost report is a vital analytical tool for manufacturing and supply chain finance professionals. It provides a granular breakdown of the cost structure for inventory items, detailing the specific components—such as material, labor, resources, and overhead—that comprise the total unit cost. This level of detail is essential for accurate product pricing, margin analysis, and cost variance investigation.

Business Challenge

In complex manufacturing environments, understanding the “true” cost of a product is often challenging. Organizations struggle with:

• Margin Visibility: Without a clear understanding of cost components, it is difficult to set prices that ensure profitability or to identify products that are eroding margins.
• Cost Driver Analysis: When unit costs increase, finance teams need to pinpoint whether the rise is due to raw material price hikes, increased labor hours, or higher overhead allocations.
• Standard Cost Maintenance: Validating that standard costs are correctly set up before a new period begins requires a detailed audit of all cost elements.
• Inventory Valuation: Ensuring that the inventory value on the balance sheet accurately reflects the cost of goods requires rigorous validation of item costs.

Solution

The CST Detailed Item Cost report solves these problems by offering deep visibility into item costing:

• Component-Level Breakdown: Lists each item and breaks down its cost into specific elements (e.g., Material, Material Overhead, Resource, Outside Processing, Overhead), providing a complete DNA of the product cost.
• Cost Type Flexibility: Allows users to analyze costs for different Cost Types (e.g., “Frozen” for current standard costs, “Pending” for future costs, or “Average” for actual costing), facilitating “what-if” analysis and period-end updates.
• Category Analysis: Supports filtering by Item Categories, enabling category managers to review cost structures for entire product lines at once.
• Efficiency: The “Exclude Items with no Cost Details” parameter helps keep the report focused on active, costed items, reducing noise in the data.

Technical Architecture

The report extracts data from the Oracle Cost Management module, linking item definitions with their cost details.

• Core Tables:
  • CST_ITEM_COSTS: The header table for item costs, storing the total unit cost for an item in a specific organization and cost type.
  • CST_ITEM_COST_DETAILS: The detail table that holds the individual cost elements (e.g., the specific cost of steel vs. plastic for a component).
  • CST_COST_TYPES: Defines the set of costs being viewed (e.g., Frozen, Average, Pending).
  • MTL_SYSTEM_ITEMS_VL: Provides item descriptions and attributes.
  • ORG_ORGANIZATION_DEFINITIONS: Identifies the inventory organization.
• Key Logic:
  • The report joins item master data with cost tables based on Inventory_Item_Id and Organization_Id.
  • It filters by the selected Cost_Type_Id to ensure the user sees the specific version of the cost they requested.
  • It aggregates detailed costs to match the total unit cost found in the header table.

Frequently Asked Questions

Q: Can I use this report to compare my current standard costs with next year’s proposed costs? A: Yes, you can run the report twice—once for the “Frozen” cost type (current) and once for your “Pending” cost type (proposed)—and compare the outputs to analyze the impact of cost changes.

Q: Does this report show costs for all organizations? A: The report is typically run for a specific “Organization Code” to provide a focused view, as costs for the same item can vary significantly between different manufacturing plants or warehouses.

Q: Why do some items appear with zero cost? A: This could happen if the item is new and hasn’t been costed yet, or if it’s an expense item. You can use the “Exclude Items with no Cost Details” parameter to hide these from the report.

Q: Does it include overheads? A: Yes, the report details all cost elements, including material overheads and resource overheads, provided they are defined in the cost structure.

---

Useful Links

• Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
• Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
• Oracle EBS Reporting Toolkits by Blitz Report™
• Blitz Report™ FAQ & Community Q&A
• Supply Chain Hub by Blitz Report™
• Blitz Report™ Customer Case Studies
• Oracle EBS Reporting Blog
• Oracle EBS Reporting Resource Centre

© Enginatics GmbH