CAC Item List Price vs. 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

Compare item master list price against any cost type (Cost Type 1) and also list the Standard Price (Market Price), Last PO Price, Converted Last PO Price and a secondary cost type (Cost Type 2). All item costs and prices are in the primary UOM, using the To Currency Code.

– | Program Name: xxx_item_target_cost_review_rept.sql – | – | Parameters: – | p_cost_type1 – The first comparison cost type you wish to report – | p_cost_type2 – The second comparison cost type you wish to report – | p_item_number – Enter the specific item number you wish to report – | p_org_code – specific organization code, works with – | null or valid organization codes – | p_operating_unit – Operating Unit you wish to report, leave blank for all – | operating units (optional) – | p_ledger – general ledger you wish to report, leave blank for all – | ledgers (optional) – | p_include_uncosted_items – Yes/No flag to include or not include non-costed items – | p_category_set1 – The first item category set to report, typically the – | Cost or Product Line Category Set – | p_category_set2 – The second item category set to report, typically the – | Inventory Category Set – | – | Description: – | Report to show item costs in any cost type – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 29 Sep 2020 Douglas Volz Initial Coding based on Item Cost Summary Report – | 1.1 22 Oct 2020 Douglas Volz Added the Master Target Price – | 1.2 26 Oct 2020 Douglas Volz Changed as List Price is always in a common, global – | currency, translate to this “to_currency” currency – | 1.3 28 Oct 2020 Douglas Volz Fixes for the last PO, need to exclude Cancelled – | PO Lines.
– | 1.4 30 Oct 2020 Douglas Volz Add To Currency Code as a visible parameter and – | show all item costs in that currency – | 1.5 04 Dec 2020 Douglas Volz Outer join fix for PO joins to item master and – | currency exchange rates. – | 1.6 06 Dec 2020 Douglas Volz Added Target Price Percent Difference column. +=============================================================================+*/ – | 1.7 24 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards, price per primary unit of measure divided by the conversion rate.

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

Comparison Cost Type, Additional Cost Type, To Currency Code, Currency Conversion Type, Currency Conversion Date, Include Uncosted Items, Category Set 1, Category Set 2, Category Set 3, Item Number, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

gl_daily_rates, gl_daily_conversion_types, mtl_parameters, hr_organization_information, hr_all_organization_units, hr_all_organization_units_vl, gl_ledgers, mtl_system_items_vl, mtl_system_items_b, cst_item_costs, cst_cost_types, po_headers_all, po_lines_all, po_line_locations_all, po_distributions_all, mtl_uom_conversions_view, xxen_report_org_access

Report Categories

Enginatics

CAC Calculate ICP PII Item Costs by Where Used, CAC Calculate Average Item Costs, CAC Intercompany SO Price List vs. Item Cost Comparison, CAC PO Price vs. Costing Method Comparison, CAC ICP PII Inventory Pending Cost Adjustment, CAC Inventory Pending Cost Adjustment, CAC Item Cost Comparison, CAC WIP Pending Cost Adjustment, CAC ICP PII WIP Pending Cost Adjustment

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 List Price vs. Item Cost 24-Jun-2022 024533.xlsx
Blitz Report™ XML Import CAC_Item_List_Price_vs_Item_Cost.xml
SQL Source CAC Item List Price vs- Item Cost.sql
Full Report on Enginatics www.enginatics.com/reports/cac-item-list-price-vs-item-cost/

Case Study & Technical Analysis: CAC Item List Price vs. Item Cost

Executive Summary

The CAC Item List Price vs. Item Cost report is a margin analysis tool that compares the official List Price (from the Item Master) against the Item Cost (Standard or Average). It also brings in the “Last PO Price” to provide a market-based reference point. This report is essential for ensuring that pricing strategies are aligned with current cost realities.

Business Challenge

Maintaining healthy margins requires constant vigilance.

Solution

This report provides a multi-dimensional view of value.

Technical Architecture

The report integrates data from three distinct modules:

Parameters

Performance

FAQ

Q: Which List Price does it use? A: It uses the list_price_per_unit field from the Master Item table (mtl_system_items). It does not look at Advanced Pricing modifiers.

Q: Why is the Last PO Price blank? A: If the item has never been purchased (e.g., it’s a Make item or a new item), there will be no PO history.

Q: Does it include tax? A: Generally, Oracle PO prices and Item Costs exclude recoverable taxes (VAT/GST).

© Enginatics GmbH