CAC Calculate Average 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
Using purchase order receipts, calculate average item costs over a specified transaction date range and compare against the Comparison Cost Type. In addition, with the Use Default Material Overheads parameter you can choose which material overheads to use on this report. Select Yes to use the Default Material Overhead setups. Set to No to use the material overheads from the specified Comparison Cost Type. And if planning to use roll up these item costs, to avoid doubling up rolled up material costs on assemblies, choose Yes for the parameter Exclude Rolled Up Items, to avoid having manually defined material costs and rolled up material costs.
– | Copyright 2006 - 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. – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 05 Jan 2019 Douglas Volz Initial Coding based on item_cost_history.sql – | 1.15 10 Jan 2023 Douglas Volz Fix for Average Cost calculations, UOM issue. – | 1.16 21 Aug 2023 Douglas Volz Remove tabs and restrict to only orgs you have access to, – | Add last payables invoice and purchase order price information. – | 1.17 25 Sep 2023 Douglas Volz Fix for the Last PO and Last A/P information – | 1.18 13 Oct 2023 Andy Haack Fix for G/L Daily Rates, outer joins – | 1.19 24 Nov 2023 Douglas Volz Fixed item number parameter for last PO information – | and fix Last A/P Invoice info for G/L Daily Rates – | 1.20 08 Dec 2023 Douglas Volz Added G/L and Operating Unit security restrictions. – | 1.21 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
Transaction Date From, Transaction Date To, Use Default Material Overheads, Only Active Rates, Exclude Rolled Up Items, Comparison Cost Type, Currency Conversion Date, Currency Conversion Type, Show Last AP Invoice, Category Set 1, Category Set 2, Category Set 3, Supplier Type to Exclude, Item Status to Exclude, Item Number, Organization Code, Operating Unit, Ledger
Oracle EBS Tables Used
mtl_parameters, mtl_material_transactions, rcv_transactions, ap_suppliers, mtl_system_items_vl, xxen_report_org_access, mtl_item_status_vl, gl_daily_rates, gl_daily_conversion_types, cst_item_costs, cst_cost_types, mtl_system_items_b, cst_item_overhead_defaults, bom_resources, mtl_item_categories, cst_item_cost_details, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, po_headers_all, po_lines_all, po_line_locations_all, po_distributions_all, mtl_uom_conversions_view
Report Categories
Related Reports
CAC Open Purchase Orders, CAC Purchase Price Variance, GL Account Analysis, GL Account Distribution Analysis, GL Account Analysis (Drilldown) (with inventory and WIP), CAC Receiving Activity Summary, CAC PO Price vs. Costing Method Comparison, INV Items, PO Headers and Lines
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 Calculate Average Item Costs 11-Dec-2022 114227.xlsx |
| Blitz Report™ XML Import | CAC_Calculate_Average_Item_Costs.xml |
| SQL Source | CAC Calculate Average Item Costs.sql |
| Full Report on Enginatics | www.enginatics.com/reports/cac-calculate-average-item-costs/ |
Case Study & Technical Analysis: CAC Calculate Average Item Costs
Executive Summary
The CAC Calculate Average Item Costs report is a sophisticated analytical tool for Cost Accountants and Procurement Managers. It calculates the “true” average cost of items based on actual Purchase Order (PO) receipts over a specified period and compares these calculated values against a standard cost type (e.g., Frozen or Pending). This analysis is crucial for validating standard costs during periodic updates, ensuring that the system’s cost definitions align with current market prices paid to suppliers.
Business Challenge
Setting standard costs is often a complex exercise involving estimation and historical analysis. Organizations face challenges such as:
- Cost Variance: Significant Purchase Price Variances (PPV) occurring because standard costs are outdated compared to actual procurement costs.
- Inflationary Pressure: Difficulty in quantifying the impact of rising supplier prices on inventory valuation.
- Overhead Allocation: Complexity in determining the correct material overheads to apply to new standard costs.
- Data Volume: The inability to manually calculate weighted average costs across thousands of items and receipts.
The Solution
The CAC Calculate Average Item Costs report automates the derivation of average costs. It enables users to:
- Validate Standards: Compare the calculated average purchase price against the current standard cost to identify items requiring revaluation.
- Simulate Overheads: Choose between using existing standard overheads or applying default overhead setups to simulate the total cost impact.
- Exclude Outliers: Filter out specific supplier types or inactive items to ensure the calculated average reflects “normal” business operations.
- Analyze Trends: View the last AP invoice and PO price alongside the average to spot recent pricing trends.
Technical Architecture (High Level)
The report performs a complex aggregation of purchasing and inventory data.
- Data Source: It queries
RCV_TRANSACTIONSandMTL_MATERIAL_TRANSACTIONSto identify receipts and their actual transaction values. - Calculation Engine: It calculates a weighted average cost: $\frac{\sum (Quantity \times Unit Price)}{\sum Quantity}$.
- Cost Comparison: It joins with
CST_ITEM_COSTSto retrieve the comparison cost (e.g., Frozen) and calculates the variance (absolute and percentage). - Overhead Logic: The query includes conditional logic (
DECODE) to apply material overhead rates either from the existing cost type or from the default category-based setups (CST_DEFAULT_COST_ELEMENT_RATES), depending on the user’s parameter selection.
Parameters & Filtering
The report offers extensive parameters for precise analysis:
- Transaction Date Range: Defines the historical period for the average cost calculation (e.g., last 6 months).
- Comparison Cost Type: The benchmark cost type (e.g., “Frozen” or “Pending”).
- Use Default Material Overheads: A toggle (‘Y’/’N’) to determine if overheads should be recalculated based on current default rules.
- Exclude Rolled Up Items: Prevents double-counting of costs for sub-assemblies if the intention is to roll up costs later.
- Show Last AP Invoice: Optionally retrieves the most recent invoice details for a “sanity check” against the average.
Performance & Optimization
The report handles large data volumes through:
- Pre-Aggregation: It aggregates receipt data by item and organization before joining to the master data, reducing the row count for subsequent joins.
- Indexed Lookups: Utilizes standard indexes on transaction dates and item IDs.
- Efficient Currency Conversion: Applies currency conversion rates at the transaction level (if needed) or uses the standard rate defined in the parameters.
FAQ
Q: How is the “Average Material Cost” calculated? A: It is the total value of PO receipts divided by the total quantity received during the specified date range.
Q: What happens if there are no receipts for an item? A: The item will not appear in the report unless it exists in the comparison cost type, depending on the join type. Typically, this report focuses on items with activity.
Q: Why can I exclude specific supplier types? A: You might want to exclude inter-company transfers or one-time spot buys from the average cost calculation to get a more representative market price.
Q: Does this update the system costs? A: No, this is a reporting tool only. It provides the data needed to make decisions about updating costs, but it does not perform the update itself.
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