CAC ICP PII vs. Item Costs – Oracle EBS SQL Report

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

Overview

Report to compare the Frozen or Pending Costs against the PII item costs. If you enter a Period Name this report also shows the stored month-end from the period end snapshot (snapshot that is created when you close the inventory periods). If you leave the Period Name blank or null you will report the real-time onhand quantities. Also note that this report excludes inactive items.

Note: there is a hidden parameter, 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 into your PII Cost Type. Defaulted as positive (+1).

Parameters:

Cost Type: defaults to your Costing Method; if the cost type is missing component costs the report will find any missing item costs from your Costing Method cost type. PII Cost Type: the profit in inventory cost type you wish to report PII Sub-Element: the sub-element or resource for profit in inventory, such as PII or ICP (optional) Period Name (Closed): Accounting period you wish to report for the onhand quantities at month-end, If you leave this value blank or null you get the real-time onhand quantities. Category Set 1: any item category you wish, typically the Product or Product Line category set (optional). Category Set 2: any item category you wish, typically the Cost category set (optional). Organization Code: enter the specific inventory organization(s) you wish to report (optional). Operating Unit: enter the specific operating unit(s) you wish to report (optional). Ledger: enter the specific ledger(s) you wish to report (optional).

/* +=============================================================================+ – | 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. – +=============================================================================+ – | Version Modified on Modified by Description – | ======= =========== ===================================================== – | 1.0 29 Sep 2009 Douglas Volz Initial Coding – | 1.4 01 May 2019 Douglas Volz Period name is now optional, if left null – | the real-time quantities are reported. – | 1.5 27 Jan 2020 Douglas Volz Added Org Code and Operating Unit parameters. – | 1.6 26 Feb 2022 Douglas Volz Changed to multi-language views for items, – | item status and UOM. Added List Price, – | Market Price and Currency Code to report. – | Exclude items with a status of Inactive. – | 1.7 26 Sep 2022 Douglas Volz Performance improvements and removed group by. – +=============================================================================+*/

Report Parameters

Cost Type, PII Cost Type, PII Sub-Element, Period Name (Closed), Category Set 1, Category Set 2, Category Set 3, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

cst_item_costs, mtl_system_items_vl, mtl_item_status_vl, mtl_units_of_measure_vl, mfg_lookups, fnd_common_lookups, mtl_parameters, cst_cost_types, hr_organization_information, hr_all_organization_units, gl_ledgers, cst_item_cost_details, bom_resources, org_access_view, mtl_supply, mtl_interorg_parameters, mtl_onhand_quantities_detail, mtl_secondary_inventories, org_acct_periods, cst_period_close_summary, 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 Material Account Summary, CAC Inventory and Intransit Value (Period-End), CAC ICP PII Inventory and Intransit Value (Period-End), CAC Inventory and Intransit Value (Period-End) - Discrete/OPM, CAC New Standard Item Costs, CAC Missing Material Accounting Transactions, CAC User-Defined and Rolled Up Costs, CAC Cost vs. Planning Item Controls, CAC Item Cost Summary

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 vs. Item Costs 03-Sep-2022 232153.xlsx
Blitz Report™ XML Import CAC_ICP_PII_vs_Item_Costs.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-icp-pii-vs-item-costs/

Case Study & Technical Analysis: CAC ICP PII vs. Item Costs

Executive Summary

The CAC ICP PII vs. Item Costs report is a strategic validation tool used to audit the relationship between standard inventory costs and their embedded intercompany profit (PII) components. It serves two primary functions: validating that PII is correctly defined as a percentage of the total cost, and providing a valuation snapshot that compares “Gross Inventory Value” (with profit) vs. “Net Inventory Value” (without profit). This report is essential for ensuring that transfer pricing policies are correctly reflected in the system’s cost data.

Business Challenge

In complex supply chains, items may have PII components that are supposed to represent a specific margin (e.g., 10% of the total cost). However, due to manual errors, cost rollups, or currency fluctuations, the actual PII amount in the system might drift.

The Solution

This report provides a side-by-side comparison of the total item cost and its PII component.

Technical Architecture (High Level)

The query joins the standard cost definition table with a specialized PII calculation subquery.

Parameters & Filtering

Performance & Optimization

FAQ

Q: Why is the “Net Item Cost” higher than the “Item Cost”? A: This happens if your Numeric Sign for PII parameter is set incorrectly. If PII is stored as a negative number (contra-asset) but you tell the report it’s positive, the math will add it instead of subtracting it.

Q: Can I use this to check Pending Costs before a standard cost update? A: Yes. Set the Cost Type parameter to “Pending” (or whatever your simulation cost type is named) to validate the new costs and PII values before they are frozen.

Q: Why do some items show 0 Quantity? A: If you run in Real-Time mode, it shows items even if they have 0 on-hand, as long as they have a cost defined. This is useful for checking master data setup even for items currently out of stock.

© 2026 Enginatics