CAC WIP 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 the potential standard cost changes for WIP discrete jobs, for the WIP completions, WIP component issues, WIP resource (labor) and WIP overhead transactions. However, WIP cost adjustments for Assembly Scrap is not included. The Cost Type (Old) defaults to your Costing Method Cost Type (Average, Standard, etc.); the Currency Conversion Dates default to the latest open or closed accounting period; and the To Currency Code and the Organization Code default from the organization code set for this session. And if you choose Yes for “Include All WIP Jobs” all WIP jobs will be reported even if there are no valuation changes.

/* +=============================================================================+ – | Copyright 2020 - 2025 Douglas Volz Consulting, Inc. – | Permission to use this code is granted provided the original author is – | acknowledged. No warranties, express or otherwise is included in this – | permission. All rights reserved. – +=============================================================================+

– | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 04 Dec 2020 Douglas Volz Created initial Report based on the Pending – | Cost Adjustment Report for Inventory and Intransit. – | 1.1 11 Dec 2020 Douglas Volz Corrected cost adjustments for assemblies – | 1.2 16 Dec 2020 Douglas Volz Change SIGN of completion quantities to match – | the Oracle WIP Standard Cost Adjustment Report. – | 1.3 10 Feb 2021 Douglas Volz Fixes for WIP completion quantities, needed to – | change the SIGN of completion quantities. – | 1.4 17 Feb 2021 Douglas Volz Add absolute difference column. – | 1.5 13 Dec 2021 Douglas Volz Add parameter to report all WIP jobs, even – | if there is no valuation change. – | 1.6 12 Feb 2024 Douglas Volz Remove tabs, simplify G/L conversion rates, – | added inventory org access security. – | 1.7 20 Jul 2026 Douglas Volz Added in overhead cost and valuation differences. – | 1.8 26 Sep 2026 Enginatics Currency rate and overhead corrections. – | 1.10 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

Cost Type (New), Cost Type (Old), 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, Include All WIP Jobs, Item Number, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

gl_daily_conversion_types, gl_daily_rates, cst_cost_types, cst_item_costs, cst_resource_costs, mtl_parameters, hr_organization_information, hr_all_organization_units, hr_all_organization_units_vl, gl_ledgers, wip_discrete_jobs, wip_entities, wip_accounting_classes, xxen_report_org_access, mtl_transaction_types, wip_requirement_operations, wip_operation_resources, bom_resources, wip_operations, cst_department_overheads, cst_resource_overheads, mtl_system_items_vl, mtl_item_status_vl

Report Categories

Enginatics

CAC Item List Price vs. Item Cost, CAC Calculate ICP PII Item Costs by Where Used, CAC Calculate Average Item Costs, CAC Inventory Pending Cost Adjustment, CAC Item Cost Comparison, CAC Intercompany SO Price List vs. Item Cost Comparison, CAC PO Price vs. Costing Method Comparison, CAC ICP PII WIP Pending Cost Adjustment, CAC ICP PII Inventory Pending Cost Adjustment

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 WIP Pending Cost Adjustment 10-Jul-2022 165903.xlsx
Blitz Report™ XML Import CAC_WIP_Pending_Cost_Adjustment.xml
SQL Source CAC WIP Pending Cost Adjustment.sql
Full Report on Enginatics www.enginatics.com/reports/cac-wip-pending-cost-adjustment/

Case Study & Technical Analysis: CAC WIP Pending Cost Adjustment

Executive Summary

The CAC WIP Pending Cost Adjustment report is a predictive financial tool used during the Standard Cost Update process. While the “Inventory Pending Cost Adjustment” report covers goods on the shelf, this report covers goods in production. It simulates the revaluation of Work in Process (WIP) balances that will occur when standard costs are updated. This allows Finance to:

  1. Forecast WIP Revaluation: Predict the gain or loss that will hit the P&L due to the revaluation of open jobs.
  2. Validate Resource Rates: Ensure that changes to labor and machine rates are correctly reflected in the WIP value.
  3. Audit Component Costs: Verify that the new standard costs for raw materials are correctly propagating to the jobs where they have been issued.

Business Challenge

Updating standard costs affects not just inventory, but also the value of every open work order.

The Solution

This report acts as a “What-If” engine for WIP.

Technical Architecture (High Level)

The query constructs a massive union of the three WIP value drivers.

Parameters & Filtering

Performance & Optimization

FAQ

Q: Why is the “New Material Overhead Cost” calculated differently for WIP Completions? A: The code snippet shows a CASE statement: round(nvl(cic1.material_overhead_cost,0) - nvl(cic1.tl_material_overhead,0),5). This logic subtracts “This Level” (TL) overheads. This is likely to avoid double-counting overheads that are applied at the assembly level versus those rolled up from components.

Q: Does this report update the costs? A: No, it is purely a reporting tool. The actual update happens when you run the “Update Standard Costs” concurrent program.

Q: Why don’t I see Overhead variances? A: As noted in the description, this specific version of the report excludes overheads. This is often done because overheads are calculated as a percentage of resource/material, so their variance is a derivative of the base cost variance.

© Enginatics GmbH