CAC ICP PII Material Account Summary – Oracle EBS SQL Report

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

Overview

Use this report to eliminate your internal profit in inventory (PII) at month-end. This report to sums up the material accounting entries for each item, organization, subinventory with the original amount, profit in inventory and net amounts. With the Show SLA Accounting parameter you can choose to use the Release 12 Subledger Accounting (Create Accounting) account setups by selecting Yes. And if you have not modified your SLA accounting rules, select No to allow this report to run a bit faster. Use Show Subinventories to display or not display the subinventory information. Use Show Projects to display or not display the project number and name and use Show WIP Job to display or not display the WIP job information (WIP class, class type, WIP job, description, assembly number and assembly description). For Discrete, Flow and Workorderless WIP (but not Repetitive Schedules). Both Flow and Workorderless show up as the WIP Type “Flow schedule”. And for WIP Completions and WIP Completion Returns, this report also has a column “PII Zero Component Quantity” indicating if the underlying components, which have PII, were not issued to the job. If not, the PII amount for WIP completions, for WIP jobs that were open, may be overstated as the PII is actually still sitting in the onhand inventory.

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).

Note: The SQL logic and code for this report is identical to the CAC Material Account Summary report.

Parameters:

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). Transaction Date From: enter the starting transaction date (mandatory). Transaction Date To: enter the ending transaction date (mandatory). Show SLA Accounting: enter Yes to use the Subledger Accounting rules for your accounting information (mandatory). If you choose No the report uses the pre-Create Accounting entries. Show Projects: display the project number and name. Enter Yes or No, use to limit the report size (mandatory). Show WIP: display the WIP job or flow schedule information (WIP class, class type, WIP job, description, assembly number and assembly description). Enter Yes or No, use to limit the report size (mandatory). 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). Item Number: enter the specific item number(s) you wish to report (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 06 Nov 2009 Douglas Volz Initial Coding – | 1.27 07 Oct 2022 Douglas Volz Correction for period name joins for interorg transactions. – | 1.28 16 Oct 2022 Douglas Volz Correction for quantity calculations and PII logic. – +=============================================================================+*/

Report Parameters

PII Cost Type, PII Sub-Element, Transaction Date From, Transaction Date To, Show SLA Accounting, Show Subinventory, Show Projects, Show WIP Job, Category Set 1, Category Set 2, Category Set 3, Item Number, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

mfg_lookups, mtl_system_items_vl, org_acct_periods, mtl_transaction_types, mtl_units_of_measure_vl, mtl_item_status_vl, mtl_txn_source_types, pii, gl_code_combinations, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, fnd_common_lookups, &subinventory_table, gl_access_set_norm_assign, gl_ledger_set_norm_assign_v, mo_glob_org_access_tmp, dual

Report Categories

Enginatics, R12 only

CAC Material Account Summary, CAC Material Account Detail, CAC ICP PII Material Account Detail, CAC ICP PII WIP Material Usage Variance, CAC Material Account Alias with Lot Numbers, CAC Recost Cost of Goods Sold, CAC Deferred COGS Out-of-Balance, CAC WIP Material Usage Variance, CAC Manufacturing Variance

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 Material Account Summary - Pivot by Org 16-Oct-2022 163618.xlsx
Blitz Report™ XML Import CAC_ICP_PII_Material_Account_Summary.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-icp-pii-material-account-summary/

Case Study & Technical Analysis: CAC ICP PII Material Account Summary

Executive Summary

The CAC ICP PII Material Account Summary report is a high-level financial analysis tool designed to streamline the month-end elimination of Profit in Inventory (PII). Unlike detailed transaction reports, this summary aggregates material accounting entries by General Ledger account, Organization, and Item. It provides a clear “Before and After” view: the original transaction amount, the calculated PII amount, and the resulting Net amount. This is essential for preparing manual journal entries or validating automated elimination processes.

Business Challenge

Multinational corporations often transfer inventory between subsidiaries at a markup (transfer price). For consolidated financial reporting, this internal profit must be eliminated until the goods are sold to an external customer.

The Solution

This report acts as a “PII Subledger,” summarizing the financial impact of profit movements.

Technical Architecture (High Level)

The report leverages a similar architecture to the “Material Account Detail” report but adds a heavy aggregation layer.

Parameters & Filtering

Performance & Optimization

FAQ

Q: How does the “PII Zero Component Quantity” logic work? A: For WIP Completions, the report checks if the components issued to the job actually had PII. If a job is completed (receiving PII) but no PII-bearing components were issued, the report flags this. This prevents “phantom profit” from being recognized in WIP when it’s actually still sitting in Raw Materials.

Q: Why do I see a “Net Amount” of zero for some lines? A: This usually happens for purely statistical transactions or if the PII amount equals the total transaction amount (which would be an error in cost setup).

Q: Can I use this for “Flash” reporting? A: Yes. By running with Show SLA Accounting = No, you can get a near real-time view of PII movements before the Create Accounting process has run for the period.

© 2026 Enginatics