CAC Calculate ICP PII 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

Report to identify the intercompany “To Org” profit in inventory (also known as PII or ICP) for each inventory organization and item. Report gets the PII item costs across organizations, by joining the sourcing rule information from the first “hop” to the sourcing rule information to the second “hop”. In addition, if an item has a source organization in the item master, but the sourcing rule does not exist, this item relationship will still be reported. This report also assumes that the first hop may have profit in inventory from another source organization and will not include any profit in inventory from the source org for the “To Org” profit in inventory calculations. Likewise for the “To Org”, any this level material overheads, resources, outside processing or overhead costs are ignored for the profit in inventory calculations. In addition, inactive items and disabled organizations are ignored.

Note: there is one hidden parameter: 1) Numeric Sign for PII which allows you to set the sign of the profit in inventory amounts. You can specify positive or negative values based on how you enter PII amounts. Defaulted as positive (+1).

/* +=============================================================================+ – | Copyright 2009-23 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. – | 1.38 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. – +=============================================================================+ – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 26 Sep 2009 Douglas Volz Initial Coding – | 1.34 05 May 2021 Douglas Volz Add Make Buy Code. – | 1.35 26 Feb 2022 Douglas Volz Add category sets and To Org and From Org – | parameters. Add two hidden parameters, – | Include Same OU Transfers and set Sign – | for PII Amounts (p_sign_pii), to determine – | if PII is entered as a positive or negative. – | 1.36 28 Nov 2023 Andy Haack Remove tabs, add org access controls, fix for G/L Daily Rates, outer joins – | 1.37 28 Jan 2024 Douglas Volz Make Include Transfers to Same OU a displayed parameter. +=============================================================================+*/

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

Assignment Set, Cost Type, PII Cost Type, PII Sub-Element, Currency Conversion Date, Currency Conversion Type, Period Name, Include Transfers to Same OU, From Organization, To Organization, Category Set 1, Category Set 2, Category Set 3, Item Number

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_parameters, gl_daily_rates, gl_daily_conversion_types, mtl_system_items_vl, mtl_item_status_vl, hr_organization_information, 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 Intercompany SO Price List vs. Item Cost Comparison, 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 Calculate ICP PII Item Costs 26-Jun-2023 171332.xlsx
Blitz Report™ XML Import CAC_Calculate_ICP_PII_Item_Costs.xml
SQL Source CAC Calculate ICP PII Item Costs.sql
Full Report on Enginatics www.enginatics.com/reports/cac-calculate-icp-pii-item-costs/

Case Study & Technical Analysis: CAC Calculate ICP PII Item Costs

Executive Summary

The CAC Calculate ICP PII Item Costs report is a specialized financial tool designed for multinational corporations with complex intercompany supply chains. It calculates the Profit in Inventory (PII), also known as Intercompany Profit (ICP), embedded in the standard costs of items as they move between inventory organizations. By analyzing sourcing rules and cost structures across “hops” (transfer points), this report enables finance teams to accurately eliminate intercompany profits for consolidated financial reporting and ensure transfer pricing compliance.

Business Challenge

In multi-entity organizations, goods are often transferred between subsidiaries at a markup (transfer price). While this markup is recognized as revenue for the selling entity, it represents an unrealized profit for the consolidated group until the goods are sold to an external customer. Challenges include:

The Solution

The CAC Calculate ICP PII Item Costs report provides a granular view of intercompany profit at the item level. It allows users to:

Technical Architecture (High Level)

The report logic is built around the concept of “hops” in the supply chain.

Parameters & Filtering

The report requires specific configuration to yield accurate results:

Performance & Optimization

FAQ

Q: What is “PII” or “ICP”? A: PII stands for Profit in Inventory, and ICP stands for Intercompany Profit. They refer to the profit margin added by a selling entity within the same corporate group, which must be eliminated for consolidated reporting.

Q: How does the report handle currency conversion? A: It uses the Currency Conversion Date and Type parameters to look up rates in the General Ledger. This is crucial because standard costs are often set using a fixed exchange rate for the year.

Q: Does this report update the costs? A: No, it is a calculation and reporting tool. The results are typically used to create a mass update (e.g., via an interface or API) to adjust the PII cost elements in the system.

Q: Why is there a “Sign for PII” parameter? A: Different implementations track PII differently—some as a positive cost element (additive) and others as a contra-asset (negative). This parameter ensures the math works correctly for your specific setup.

© Enginatics GmbH