CAC ICP PII WIP Material Usage Variance – 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

For your open and closed WIP jobs use this report to calculate the amount of profit in inventory (PII) in WIP as well as the amount of PII in the WIP variances for the month.

Report your material usage variances for your open and closed WIP jobs. If the job is open the Report Type column displays “Valuation”, as this WIP job and potential material usage variance is still in your WIP inventory balances. If the job has been closed during the reporting period, the Report Type column displays “Variance”, as this WIP job was written off on a WIP Job Close Variance transaction during the reporting period. You can report prior periods and this report will automatically adjust the assembly completion quantities and component issue quantities to reflect what it was for the specified reported accounting period. And by specifying the profit in inventory (PII) cost type you can determine how much PII or ICP (intercompany profit) was either remaining on your balance sheet or how much was recorded as part of your WIP job close variances.

Closed, Pending Close, Cancelled, Complete and Complete No Charges WIP job statuses use the completion quantities. All other WIP jobs use the parameter “Use Completion Quantities”.

Rules: If component issue quantity = zero, and the WIP job is open, there is no PII as the components are in onhand inventory. But if the WIP job is closed, PII is calculated in order to correct the WIP variance.

/* +=============================================================================+ – | Copyright 2009 - 2022 Douglas Volz Consulting, Inc. – | All rights reserved. – | Version Modified on Modified by Description – | ======= =========== =============== ========================================= – | 1.26 20 Mar 2022 Douglas Volz Adjust PII, if the wip job is closed, even if there are no issued quantities, still calculate PII in order to correct the job close WIP variance. – | 1.27 24 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

Report Option, Period Name, Cost Type, PII Cost Type, PII Sub-Element, Include Scrap Quantities, Include Unreleased Jobs, Include Bulk Supply Items, Use Completion Quantities, Category Set 1, Category Set 2, Category Set 3, Organization Code, Class Code, Job Status, WIP Job, Component Number, Assembly Number, Operating Unit, Ledger

Oracle EBS Tables Used

mtl_material_transactions, mtl_parameters, hr_organization_information, hr_all_organization_units, hr_all_organization_units_vl, gl_ledgers, cst_cost_types, org_acct_periods, wip_discrete_jobs, wip_accounting_classes, xxen_report_org_access, wip_operations, cst_item_costs, cst_item_cost_details, bom_resources, wip_requirement_operations, pa_projects_all, wip_entities, mtl_system_items_vl, bom_departments, gl_code_combinations

Report Categories

Enginatics

CAC Material Account Summary, CAC Material Account Detail, CAC ICP PII Material Account Detail, CAC ICP PII Material Account Summary, CAC WIP Material Usage Variance, CAC WIP Resource Efficiency, CAC Manufacturing Variance, CAC WIP Account Summary, CAC WIP Period Balances to Accounting Activity Reconciliation

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 WIP Material Usage Variance 23-Jun-2022 155150.xlsx
Blitz Report™ XML Import CAC_ICP_PII_WIP_Material_Usage_Variance.xml
SQL Source CAC ICP PII WIP Material Usage Variance.sql
Full Report on Enginatics www.enginatics.com/reports/cac-icp-pii-wip-material-usage-variance/

Case Study & Technical Analysis: CAC ICP PII WIP Material Usage Variance

Executive Summary

The CAC ICP PII WIP Material Usage Variance report is a specialized forensic tool for manufacturing organizations that need to track Profit in Inventory (PII) within their Work in Process (WIP). It serves a dual purpose:

  1. Valuation: For open jobs, it calculates the PII embedded in the current WIP balance.
  2. Variance Analysis: For closed jobs, it calculates how much PII was written off to the P&L as part of the WIP Job Close Variance. This report is critical for ensuring that intercompany profit is not accidentally expensed or lost when manufacturing variances occur.

Business Challenge

Standard WIP variance reports focus on the total cost. However, for multinational companies, a portion of that cost is actually intercompany profit.

The Solution

This report reconstructs the WIP material history to isolate the PII component.

Technical Architecture (High Level)

The query is a complex assembly of Common Table Expressions (CTEs) that mimic the Oracle WIP variance calculation engine.

Parameters & Filtering

Performance & Optimization

FAQ

Q: Why does the report show “Valuation” for a job that is now closed? A: If you run the report for a prior period (e.g., January) and the job closed in February, the report correctly identifies that in January, the job was Open and therefore represents a Valuation balance, not a Variance write-off.

Q: How does it handle “PII Zero Component Quantity”? A: If a job is Open and no components have been issued yet, the report correctly shows 0 PII, because the profit is still sitting in Raw Materials inventory, not WIP.

Q: Does this report match the General Ledger? A: The “Variance” section should match the PII portion of the WIP Job Close Variance account in the GL. The “Valuation” section supports the WIP Inventory balance.

© Enginatics GmbH