CAC Calculate ICP PII Item Costs by Where Used – Oracle EBS SQL Report

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

Overview

Report to identify the intercompany “To Org” profit in inventory (also known as PII or ICP) for each inventory organization and item. Gets the PII item costs by joining the sourcing rule information from the first “hop” from the sourcing rule information to the second “hop”. In addition, if an item has a source org 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. And while calculating the profit in inventory item costs, this report also shows where these components are being used on the To Org bills of material.

Parameters: Assignment Set: the set of sourcing rules to use with calculating the PII item costs (mandatory). From (Source) Organization: the source organization where the goods come from (optional). To Organization: the organization where the goods are being shipped to (optional). Cost Type: the cost type to use for the item costs, such as Frozen or Pending (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). Currency Conversion Date: the exchange rate conversion date that was used to set the standard costs (mandatory). Currency Conversion Type: the exchange rate conversion type that was used to set the standard costs (mandatory). Period Name: the accounting period you wish to report for; this value does not change any PII or item costs, it is merely a reference value for reporting purposes (mandatory). Include Transfers to Same OU: allows you to include or exclude transfers within the same Operating Unit (OU). Defaulted to not include these internal transfers. Include Expense Items: enter Yes or No to indicate if you want to include expense items on the bills of material (mandatory). Defaulted to No. Include Uncosted Items: enter Yes or No to indicate if you want to include uncosted items on the bills of material (mandatory). Defaulted to No. Category Set 1: any item category you wish, typically the Cost or Product Line category set (optional). Category Set 2: any item category you wish, typically the Inventory category set (optional). Component Number: enter the specific component(s) you wish to report (optional). Assembly Number: enter the specific assembly or assemblies you wish to report (optional). Include Unimplemented ECOs: enter Yes or No to indicate if you want to include engineering changes which have not been implemented (mandatory). Operating Unit: enter the specific operating unit(s) you wish to report (optional). Ledger: enter the specific ledger(s) you wish to report (optional).

Hidden Parameters: Numeric Sign for PII: to set the sign of the profit in inventory amounts. This parameter determines if PII is normally entered as a positive or negative amount (mandatory).

– Copyright 2018 - 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.
– Version Modified on Modified by Description
– ======= =========== ============== =========================================
– 1.6 29 Jan 2024 Doug Volz Remove tabs, add org access controls, fix for G/L Daily Rates,
– Make three parameters non-hidden: Include Transfers to Same OU, Include Expense Items and Include Uncosted Items.

Report Parameters

Assignment Set, From (Source) Organization, To Organization, Cost Type, PII Cost Type, PII Sub-Element, Currency Conversion Date, Currency Conversion Type, Period Name, Include Transfers to Same OU, Include Uncosted Items, Include Expense Items, Category Set 1, Category Set 2, Category Set 3, Component Number, Assembly Number, Include Unimplemented ECOs, Operating Unit, Ledger

Oracle EBS Tables Used

mtl_onhand_quantities_detail, mtl_units_of_measure_vl, mtl_item_status_vl, mfg_lookups, fnd_common_lookups, mtl_parameters, mtl_system_items_vl, bom_structures_b, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, bom_components_b, org_access_view, mtl_item_revisions_b, mrp_sr_source_org, mrp_sr_receipt_org, mrp_sourcing_rules, mrp_sr_assignments, mrp_assignment_sets

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 Calculate ICP PII Item Costs by Where Used 20-May-2023 Vision 021803.xlsx
Blitz Report™ XML Import CAC_Calculate_ICP_PII_Item_Costs_by_Where_Used.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-calculate-icp-pii-item-costs-by-where-used/

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

Executive Summary

The CAC Calculate ICP PII Item Costs by Where Used report extends the functionality of the standard PII calculation by integrating it with the Bill of Materials (BOM) structure. It not only identifies the Intercompany Profit (ICP/PII) for specific items but also traces these items upwards to see which parent assemblies consume them. This is critical for understanding how intercompany profits roll up into finished goods and for validating the cost structure of complex manufactured products that rely on intercompany transfers.

Business Challenge

While knowing the PII for a specific component is useful, organizations often need to know:

The Solution

This report provides a multi-dimensional view of PII:

Technical Architecture (High Level)

The report combines supply chain network logic with manufacturing data.

Parameters & Filtering

The report offers granular control for detailed analysis:

Performance & Optimization

FAQ

Q: How is this different from the “CAC Calculate ICP PII Item Costs” report? A: The base report lists PII for items in a flat list. This report adds the “Where Used” dimension, showing you which assemblies consume those items. It’s a “bottom-up” view of your product structure with PII data attached.

Q: Why do I see “Quantity per Assembly”? A: This field allows you to calculate the total PII impact on one unit of the parent assembly (PII per unit * Quantity per Assembly).

Q: Can I use this to find items with missing sourcing rules? A: Yes, if an item exists in a BOM but has no sourcing rule defined (and thus no calculated PII where expected), it can highlight gaps in your supply chain setup.

Q: Does it handle phantom assemblies? A: The report logic respects the BOM structure. If phantom assemblies are part of the structure, their components would typically be blown through, but this report focuses on the direct parent-child link defined in the BOM tables.

© 2026 Enginatics