Skip to the content.

CAC Item Cost Break-Out by Activity – 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 item costs by cost element, by activity. Using up to five entered activities. In order for this report to show your activity costs you must first define your activities and then associate your sub-elements by activity.

/* +=============================================================================+ – | Copyright 2009-2022 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_item_cost_break_out_by_activity_rept.sql – | – | Parameters:

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 Type, Category Set 1, Category Set 2, Category Set 3, Activity1, Activity2, Activity3, Activity4, Activity5, Organization Code, Item Number, Operating Unit, Ledger

Oracle EBS Tables Used

mtl_parameters, hr_organization_information, hr_all_organization_units, hr_all_organization_units_vl, gl_ledgers, cst_item_costs, cst_cost_types, mtl_system_items_vl, cst_item_cost_details, bom_resources, cst_activities, xxen_report_org_access

Report Categories

Enginatics

CAC Last Standard Item Cost, CAC ICP PII WIP Pending Cost Adjustment, CAC ICP PII Inventory Pending Cost Adjustment, CAC ICP PII Item Cost Summary, CAC ICP PII WIP Material Usage Variance, CAC Manufacturing Variance, CAC ICP PII Inventory and Intransit Value (Period-End), CAC New Standard Item Costs, CAC User-Defined and Rolled Up Costs, CAC Cost vs. Planning Item Controls

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 CAC Item Cost Break-Out by Activity 23-Jun-2022 201517.xlsx
Blitz Report™ XML Import CAC_Item_Cost_Break_Out_by_Activity.xml
SQL Source CAC Item Cost Break-Out by Activity.sql
Full Report on Enginatics www.enginatics.com/reports/cac-item-cost-break-out-by-activity/

Case Study & Technical Analysis: CAC Item Cost Break-Out by Activity

Executive Summary

The CAC Item Cost Break-Out by Activity report is an Activity-Based Costing (ABC) analysis tool. It moves beyond the traditional “Material/Labor/Overhead” view and breaks down costs by the specific business activities defined in the system (e.g., “Setup”, “Quality Control”, “Machining”, “Plating”). This perspective is vital for organizations trying to understand the true process costs of their products.

Business Challenge

Traditional cost elements often hide the true drivers of expense.

Solution

This report leverages the “Activity” field in Oracle Costing.

Technical Architecture

The report relies on the activity_id stored in cst_item_cost_details:

Parameters

Performance

FAQ

Q: What if I don’t use Activities? A: Then this report will likely show zeros in the activity columns. It is only useful if you have implemented Activity-Based Costing features in Oracle.

Q: Can I see more than 5 activities? A: The standard template supports 5. To see more, you would need to modify the SQL or run the report multiple times with different parameters.

Q: Does this work for OPM? A: This specific report is designed for Discrete Costing (Standard/Average). OPM has its own Activity Costing structure.


© Enginatics GmbH