CAC ICP PII Item Cost Summary – Oracle EBS SQL Report

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

Overview

Report to show item costs in any cost type, including the profit in inventory costs (also known as ICP or PII). For one or more inventory organizations.

/* +=============================================================================+ – | Copyright 2009-2022 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_item_cost_rept.sql – | – | Parameters: – | p_cost_type – The cost type you wish to report – | p_pii_cost_type – The new PII Cost Type you wish to report – | p_pii_sub_element – The sub-element or resource for profit in inventory, – | such as PII or ICP (mandatory) – | p_ledger – general ledger you wish to report, works with – | null or valid ledger names – | 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_include_uncosted_items – Yes/No flag to include or not include non-costed resources – | 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 06 Oct 2009 Douglas Volz Initial Coding – | 1.1 16 Mar 2010 Douglas Volz Updated with Make/Buy flags – | 1.2 08 Nov 2010 Douglas Volz Updated with additional columns and parameters – | 1.3 07 Feb 2011 Douglas Volz Added COGS and Revenue default accounts – | 1.4 15 Nov 2016 Douglas Volz Added category information – | 1.5 27 Jan 2020 Douglas Volz Added Org Code and Operating Unit parameters – | 1.6 27 Apr 2020 Douglas Volz Changed to multi-language views for the item – | master, inventory orgs and operating units. – | 1.7 21 Jun 2020 Douglas Volz Changed to multi-language views for item – | status and UOM. – | 1.8 24 Sep 2020 Douglas Volz Added List Price to report – | 1.9 29 Jan 2021 Douglas Volz Added item master dates and Inactive Items parameter +=============================================================================+*/

Report Parameters

Cost Type, PII Cost Type, PII Sub-Element, Include Inactive Items, Include Uncosted Items, Category Set 1, Category Set 2, Category Set 3, Item Number, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

cst_item_cost_details, cst_cost_types, bom_resources, cst_item_costs, mtl_system_items_vl, mtl_item_status_vl, mtl_units_of_measure_vl, mtl_parameters, mfg_lookups, fnd_lookups, fnd_common_lookups, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, gl_code_combinations, org_access_view, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual

Report Categories

Enginatics

CAC Last Standard Item Cost, CAC ICP PII Inventory Pending Cost Adjustment, CAC ICP PII WIP Material Usage Variance, CAC Manufacturing Variance, CAC Material Account Summary, CAC ICP PII Inventory and Intransit Value (Period-End), CAC New Standard Item Costs, CAC User-Defined and Rolled Up Costs, CAC Cost vs. Planning Item Controls

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 ICP PII Item Cost Summary 07-Jul-2022 143147.xlsx
Blitz Report™ XML Import CAC_ICP_PII_Item_Cost_Summary.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-icp-pii-item-cost-summary/

Case Study & Technical Analysis: CAC ICP PII Item Cost Summary

Executive Summary

The CAC ICP PII Item Cost Summary report is a specialized costing tool designed to provide transparency into the composition of item costs, specifically isolating the Intercompany Profit (ICP) or Profit in Inventory (PII) component. While standard cost reports show the total value, this report breaks down the cost into “Gross Cost,” “Profit Portion,” and “Net Cost” (True Manufacturing Cost). This is essential for transfer pricing analysis and validating that profit margins are correctly embedded in the standard costs.

Business Challenge

In multi-entity supply chains, items are often transferred between subsidiaries at a markup. The receiving organization sees this markup as part of their standard material cost.

The Solution

This report solves the visibility gap by performing a dual-lookup for every item:

  1. Standard Cost Retrieval: It pulls the standard cost components (Material, Overhead, Resource, etc.) from the primary Cost Type.
  2. Profit Isolation: It simultaneously queries a specific “PII Cost Type” or looks for a specific “PII Sub-Element” to quantify the profit portion.
  3. Net Cost Calculation: It mathematically derives the Net Cost (Gross Cost - Profit) to show the underlying value of the inventory.

Technical Architecture (High Level)

The query is a direct join against the Item Master and Costing tables, with a specific sub-query logic for PII.

Parameters & Filtering

Performance & Optimization

FAQ

Q: What if my profit is not stored in a specific sub-element? A: This report is designed for the common configuration where profit is tracked as a distinct cost element (usually Material Overhead). If profit is just “baked in” to the material cost without a separate tag, this report cannot isolate it without a reference “True Cost” cost type to compare against.

Q: Can I use this for “What-If” analysis? A: Yes. By pointing the “Cost Type” parameter to a simulation cost type (e.g., “Pending”), you can see what the PII values will be after a cost update.

Q: Why do I see “Net Item Cost”? A: Net Item Cost = Total Standard Cost - PII Amount. This represents the cost to the consolidated entity (the group), stripping out the internal artificial profit.

© 2026 Enginatics