CAC ICP PII Inventory Pending Cost Adjustment – 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 showing potential standard cost changes for onhand and intransit inventory value which you own, for gross, profit in inventory and net inventory values. If you enter a period name this report uses quantities from the month-end snapshot; if you leave the period name blank it uses the real-time quantities. The Cost Type (Old) defaults to your Costing Method Cost Type (Average, Standard, etc.), the Currency Conversion Dates default to the current accounting period, and the To Currency Code and the Organization Code default from the organization code set for this session. And to use the quantities from the month-end snapshot, you can only choose closed accounting periods as the month-end snapshot is created when you close the inventory accounting period.

Note: If using this report for reporting after the standard cost update this report requires both the before and after cost types available after the standard cost update is run. Please save your frozen costs to another Cost Type before running the standard cost update, using the item cost copy.

Hidden Parameters: Sign PII: hidden parameter to set the sign of the profit in inventory amounts. This parameter determines if PII is normally entered as a positive or negative amount. Default value for this report assumes PII costs are entered as positive amounts.

– Copyright 2008-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 Desc – | ======= =========== ============== ========================================= – | 1.0 21 Nov 2010 Douglas Volz Created initial Report for prior client based on BBCI_INV_VALUE_STD_ADJ_FX_REPT1.7.sql – | 1.14 07 Feb 2024 Douglas Volz Add item master and costing lot sizes, use default controls, – | based on rollup and shrinkage rate columns. Added in GL and OU security restrictions. – | 1.15 25 Jun 2024 Douglas Volz Reinstalled missing parameter, To Currency Code. Commented out GL and OU security restrictions. – | 1.17 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. – +=============================================================================

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

Period Name (Closed), Cost Type (New), Cost Type (Old), PII Cost Type (New), PII Cost Type (Old), PII Sub-Element, Currency Conversion Date (New), Currency Conversion Type (New), Currency Conversion Date (Old), Currency Conversion Type (Old), To Currency Code, Category Set 1, Category Set 2, Category Set 3, Only Items in New Cost Type, Include Items With No Quantities, Item Number, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

cst_cost_types, cst_item_cost_details, bom_resources, mtl_interorg_parameters, mtl_item_categories, mtl_category_accounts, cst_cost_group_accounts, cst_item_costs, mtl_parameters, hr_organization_information, hr_all_organization_units, hr_all_organization_units_vl, gl_ledgers, mtl_system_items_vl, xxen_report_org_access, org_acct_periods, cst_period_close_summary, mtl_onhand_quantities_detail, mtl_secondary_inventories, mtl_supply, gl_daily_conversion_types, gl_daily_rates, mtl_item_status_vl, gl_code_combinations

Report Categories

Enginatics

CAC ICP PII Inventory and Intransit Value (Period-End), CAC Intercompany SO Price List vs. Item Cost Comparison, CAC ICP PII vs. Item Costs, CAC Inventory and Intransit Value (Period-End) - Discrete/OPM, CAC Inventory Pending Cost Adjustment - No Currencies, CAC Calculate Average Item Costs, CAC Inventory Pending Cost Adjustment, CAC Inventory and Intransit Value (Period-End), CAC Last Standard Item Cost

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 ICP PII Inventory Pending Cost Adjustment 23-Jun-2022 152034.xlsx
Blitz Report™ XML Import CAC_ICP_PII_Inventory_Pending_Cost_Adjustment.xml
SQL Source CAC ICP PII Inventory Pending Cost Adjustment.sql
Full Report on Enginatics www.enginatics.com/reports/cac-icp-pii-inventory-pending-cost-adjustment/

Case Study & Technical Analysis: CAC ICP PII Inventory Pending Cost Adjustment

Executive Summary

The CAC ICP PII Inventory Pending Cost Adjustment report is a critical pre-update analysis tool for multinational organizations that manage Intercompany Profit (ICP) or Profit in Inventory (PII). Before committing a Standard Cost Update, finance teams must understand the financial impact of changing costs. This report specifically isolates the impact on the “Profit” portion of inventory value, allowing users to forecast the revaluation of PII separately from the revaluation of the base inventory cost.

Business Challenge

When standard costs are updated, the value of existing inventory changes, resulting in a revaluation gain or loss. For companies that track intercompany profit (the markup added when goods move between subsidiaries), this revaluation has two distinct components:

  1. Base Cost Revaluation: The change in the true manufacturing cost.
  2. Profit Revaluation: The change in the embedded profit margin.

Mixing these two creates financial reporting risks. If the profit portion increases, it shouldn’t be recognized as income but rather deferred. Finance teams need to know exactly how much the “Profit” bucket will change before running the update to book the correct elimination entries.

The Solution

This report provides a “What-If” analysis by comparing two cost scenarios (Old vs. New) against the current (or historical) inventory quantities.

Technical Architecture (High Level)

The report is built on a complex query structure that unions On-hand and Intransit inventory.

Parameters & Filtering

Performance & Optimization

FAQ

Q: Why do I need to specify both “Cost Type” and “PII Cost Type”? A: The “Cost Type” represents the full value of the item (Base + Profit). The “PII Cost Type” is often a shadow cost type used to track only the profit component, or the report uses it to isolate the specific sub-element value.

Q: Can I use this report for the actual month-end close? A: Yes. By selecting a closed Period Name, the report uses the official frozen quantities, making it perfect for calculating the final month-end PII elimination entry.

Q: What happens if I leave the Period Name blank? A: The report will use current real-time on-hand quantities. This is useful for mid-month forecasting to see what the impact would be if you updated costs today.

© Enginatics GmbH