CAC Intercompany SO Price List vs. Item Cost Comparison – 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 the internal SO price lists, source org item costs and compare against the “To Org” item costs and PII (profit in inventory) amounts. This report is used to ensure the profit in inventory (PII) cost model is working correctly.

Parameters:

Price Effective Date: the date the sales order list prices are effective (mandatory). Currency Conversion Date: the exchange rate conversion date to use to convert the sales price to the same currency as the item cost (mandatory). Currency Conversion Type: the desired currency conversion type to use to convert the sales price to the same currency as the item cost (mandatory). PII Cost Type: the profit in inventory cost type you wish to report (mandatory). PII Sub-Element: the sub-element or resource for profit in inventory, such as PII or ICP (mandatory). Assignment Set: the set of sourcing rules to use with calculating the PII item costs (mandatory). Cost Type: the cost type to use for the item costs, such as Frozen or Pending (mandatory). Category Set: any item category you wish, typically the Cost or Product Line category set (optional). Item Number: enter the specific item numbers(s) you wish to report (optional). Operating Unit: enter the specific operating unit(s) you wish to report (optional). To Org Code: enter the specific To Org you wish to report (optional). To Operating Unit: enter the specific operating unit(s) you wish to report (optional). To Org Ledger: enter the specific “To Org” ledger(s) you wish to report (optional).

/* +=============================================================================+ – | Copyright 2010 - 2024 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_so_price_cost_pii_rept.sql – | – | Version Modified on Modified by Description – | ======= =========== ========================================= – | 1.0 26 Nov 2010 Douglas Volz Created initial Report – | 1.18 13 Dec 2018 Douglas Volz Add Source Org OU to the InterCo_OUs inline table, to ensure – | uniqueness. Add Source Org joins for Src_Org item costs. – | Removed Release 11i edits. Replaced gl.name with gl.short_name. – | 1.19 17 Jun 2019 Douglas Volz Replace Oracle function apps.qp_price_list_pvt.get_product_uom_code – | with “and ucr.uom_code = qpa.product_uom_code”. Comment out Source – | and To Org Added Costs columns. – | 1.20 21 Aug 2019 Douglas Volz Removed client-specific SQL logic. – | 1.21 25 Oct 2019 Douglas Volz Correction to p_price_effective_date parameter – | 1.22 17 Jul 2022 Douglas Volz Changes for multi-language lookup values. Changed – | back to Oracle QP price packages, to get price list – | information based on both category or item-specific price lists. – | 1.23 28 Nov 2023 Andy Haack Added G/L security for the “To Org” G/L and removed tabs. – | 1.24 06 Feb 2024 Douglas Volz Fix for G/L Daily Rates and add “To Org” inventory security. – | 1.25 25 Jun 2024 Douglas Volz Reinstall missing parameters, Item Number and To Operating Unit. – | 1.26 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

Price Effective Date, Currency Conversion Date, Currency Conversion Type, PII Cost Type, PII Sub-Element, Assignment Set, Cost Type, Category Set, Item Number, To Org Code, To Operating Unit, To Org Ledger

Oracle EBS Tables Used

mrp_assignment_sets, mrp_sr_assignments, mrp_sourcing_rules, mrp_sr_receipt_org, mrp_sr_source_org, mtl_system_items_b, cst_item_cost_details, cst_cost_types, bom_resources, cst_item_costs, mtl_intercompany_parameters, hz_cust_site_uses_all, hz_cust_acct_sites_all, hz_cust_accounts, qp_list_headers_vl, qp_list_lines, mtl_uom_conversions_view, dual, mtl_item_status_vl, mtl_category_sets_vl, mtl_item_categories, mtl_categories_v, gl_daily_rates, gl_daily_conversion_types, mtl_parameters, mtl_system_items_vl, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, xxen_report_org_access

Report Categories

Enginatics

CAC Cost vs. Planning Item Controls, CAC Calculate ICP PII Item Costs by Where Used, CAC Calculate ICP PII Item Costs, CAC Last Standard Item Cost, CAC User-Defined and Rolled Up Costs, CAC New Standard Item Costs, MRP Pegging, INV Items, CAC Item vs. Component Include in Rollup 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 Intercompany SO Price List vs. Item Cost Comparison 11-Jul-2022 140147.xlsx
Blitz Report™ XML Import CAC_Intercompany_SO_Price_List_vs_Item_Cost_Comparison.xml
SQL Source CAC Intercompany SO Price List vs- Item Cost Comparison.sql
Full Report on Enginatics www.enginatics.com/reports/cac-intercompany-so-price-list-vs-item-cost-comparison/

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

Executive Summary

The CAC Intercompany SO Price List vs. Item Cost Comparison report is a high-value financial control tool used to validate the “Profit in Inventory” (PII) cost model. It performs a three-way comparison between the Internal Sales Order (ISO) price, the Source Organization’s item cost, and the Destination Organization’s item cost. This ensures that intercompany margins are calculated correctly and that the transfer price covers the cost of goods sold plus the intended markup.

Business Challenge

Global organizations often move goods between entities with a markup (Profit in Inventory).

Solution

This report automates the complex reconciliation of intercompany costs and prices.

Technical Architecture

The report executes a sophisticated analysis across multiple modules:

Parameters

Performance

FAQ

Q: What is PII? A: Profit in Inventory (PII) represents the intercompany profit included in the inventory value of the receiving organization. It must be eliminated for consolidated financial reporting.

Q: Why do I need an Assignment Set? A: The Assignment Set tells the report which Source Organization supplies the Destination Organization, which is essential for picking the correct source cost for comparison.

Q: Does it handle different currencies? A: Yes, the report includes mandatory parameters for Currency Conversion Date and Type to normalize all figures to a single currency.

© Enginatics GmbH