CAC Item Cost Comparison – Oracle EBS SQL Report

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

Overview

Report to compare the item costs from two cost types in any two inventory orgs, converting the Org Code 1 (Source Org) into the currency of Org Code 2 (To Org). Put the smallest cost type into Cost Type 1, as the values for cost type 1 are always reported even if not in cost type 2. Note that the Source Org and the Compared To Org default from the inventory organization as set for your session.

/* +=============================================================================+ – | Copyright 2006 - 2020 Douglas Volz Consulting, Inc. | – | All rights reserved. | – +=============================================================================+ – | – | Original Author: Douglas Volz (doug@volzconsulting.com) – | – | Parameters: – | p_org_code1 – The inventory organization that is the source – | p_org_code2 – The inventory organization that is the target – | p_cost_type1 – The source comparison cost type – | p_cost_type2 – The target comparison cost type – | p_curr_conv_type – Conversion conversion type, to convert the Source – | org, org_code1, into the currency of the To_Org, – | org_code2 – | p_curr_conv_date – The currency conversion date, typically a month-end – | date – | p_min_cost_diff – Minimum material cost diff. to show on the report – | 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 compare the item costs from two cost types in any two inventory orgs, – | converting the Org_Code 1 (Source_Org) into the currency of Org_Code 2 (To_Org). – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 02 APR 2006 Douglas Volz Initial Coding – | 1.1 14 Apr 2006 Douglas Volz Final Coding for client – | 1.4 13 JUN 2006 Douglas Volz Added nvl to all material costs columns – | 1.5 02 NOV 2009 Douglas Volz Changed to compare the item costs for two orgs – | across different currencies – | 1.6 21 Nov 2016 Douglas Volz Take out currencies, allow comparison within – | the same inventory org but using different – | cost types – | 1.7 18 Dec 2018 Douglas Volz Add currencies back in – | 1.8 30 Aug 2019 Douglas Volz Add cost and inventory item categories, – | item type and item status. – | 1.9 01 Sep 2020 Douglas Volz Added costs by cost element. – | 1.10 11 Sep 2020 Douglas Volz Added ability to report cost type1 even when – | the item is not in cost type1. Made cic2 a – | table select to avoid sql outer join errors. – | 1.11 24 Oct 2020 Douglas Volz Fix bug to convert into any currency, not just USD. – | 1.12 02 Nov 2020 Douglas Volz Again, fix to report cost type 1 even when the – | item is not in cost type 2. – | 1.13 05 Jan 2022 Douglas Volz Add lot size, shrinkage rate, based on rollup – | flags. Change calc. for percent difference. +=============================================================================+*/

Report Parameters

Cost Type 1, Compared to Cost Type 2, Source Org (Org Code 1), Compared to Org (Org Code 2), Minimum Cost Difference, Currency Conversion Type, Currency Conversion Date, Category Set 1, Category Set 2, Category Set 3, Item Number

Oracle EBS Tables Used

gl_daily_rates, gl_daily_conversion_types, cst_item_costs, org_organization_definitions, gl_ledgers, cst_cost_types, mtl_system_items_vl, mtl_item_status_vl, mtl_units_of_measure_vl, mtl_parameters, fnd_common_lookups, mfg_lookups, hr_organization_information, hr_all_organization_units_vl

Report Categories

Enginatics

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 CAC Item Cost Comparison 23-Jun-2022 212444.xlsx
Blitz Report™ XML Import CAC_Item_Cost_Comparison.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-item-cost-comparison/

Case Study & Technical Analysis: CAC Item Cost Comparison

Executive Summary

The CAC Item Cost Comparison report is a strategic sourcing and transfer pricing tool. It enables the comparison of item costs between two different inventory organizations, even if they use different currencies. This is critical for “Make vs. Buy” decisions (e.g., “Is it cheaper to make this in Plant A or Plant B?”) and for validating intercompany transfer prices.

Business Challenge

Global supply chains involve moving goods between entities with different cost structures and currencies.

Solution

This report automates the cross-org comparison.

Technical Architecture

The report performs a complex join across organizations:

Parameters

Performance

FAQ

Q: Can I compare the same org to itself? A: Yes, you can use this to compare two different Cost Types (e.g., Frozen vs. Pending) within the same organization, effectively acting as a “Cost Impact” report.

Q: What if the item doesn’t exist in the second org? A: The report typically uses an outer join, so it will show the item in Org 1 with a null value for Org 2 (or vice versa).

Q: Does it handle different Units of Measure? A: The report assumes the Primary UOM is the same. If Org 1 uses “Each” and Org 2 uses “Dozen”, the comparison will be skewed unless a conversion is applied (which this report typically does not do automatically).

© 2026 Enginatics