CAC WIP Account Summary – Oracle EBS SQL Report

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

Overview

Report to get the WIP accounting distributions, in summary, by WIP job, resource, overhead and WIP cost update. 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. With parameters to limit the report size, Show Project to display or not display the project number and name, Show WIP Job to display or not display the WIP job (WIP job, description and resource codes) and Show WIP Outside Processing to display or not display the outside processing information (WIP OSP item number, supplier, purchase order, purchase order line and release). For Discrete, Flow and Workorderless WIP (but not Repetitive Schedules). Note that both Flow and Workorderless show up as the WIP Type “Flow schedule”.

Parameters:

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 Jobs: display the WIP job, description, department and resource. Enter Yes or No, use to limit the report size (mandatory). Show WIP Outside Processing: display the WIP OSP item number, supplier, purchase order, purchase order line and release. 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). Assembly Number: enter the specific assembly 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- 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.0 06 Nov 2009 Douglas Volz Initial Coding – | 1.13 11 Mar 2021 Douglas Volz Added Flow Schedules and Workorderless WIP – | and removed redundant joins and tables to – | improve performance. – | 1.14 22 Mar 2021 Douglas Volz Add WIP Job parameter. – | 1.15 20 Dec 2021 Douglas Volz Add WIP Department. – | 1.16 12 Aug 2022 Douglas Volz Combine with WIP Account Summary No SLA report – | and add Show WIP Job and Show WIP OSP parameters. – | 1.17 14 Aug 2022 Douglas Volz Screen out zero job close variances for Flow Schedules. – | 1.18 22 Aug 2022 Douglas Volz Improve performance with outer joins and streamline dynamic SQL. – | 1.19 26 Feb 2023 Douglas Volz Fix to show job close variances. – | 1.20 20 Jun 2024 Douglas Volz Remove tabs, reinstall missing parameters and org access controls. – +=============================================================================+*/

Report Parameters

Transaction Date From, Transaction Date To, Show SLA Accounting, Show Projects, Show WIP Job, Show WIP Outside Processing, Category Set 1, Category Set 2, Category Set 3, Assembly Number, Organization Code, Operating Unit, Ledger

Oracle EBS Tables Used

wip_entities, wip_accounting_classes, mtl_system_items_vl, bom_departments, bom_resources, org_acct_periods, gl_code_combinations, mfg_lookups, fnd_common_lookups, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, &project_tables

Report Categories

Enginatics, R12 only

CAC WIP Account Detail, CAC WIP Resource Efficiency, CAC ICP PII WIP Material Usage Variance, CAC Manufacturing Variance, 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

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 WIP Account Summary 17-Aug-2022 204307.xlsx
Blitz Report™ XML Import CAC_WIP_Account_Summary.xml
Full SQL on Enginatics www.enginatics.com/reports/cac-wip-account-summary/

Case Study & Technical Analysis: CAC WIP Account Summary

Executive Summary

The CAC WIP Account Summary report is the primary tool for reconciling Work in Process (WIP) value. It provides a summarized view of all financial activity within the factory floor, bridging the gap between operational manufacturing transactions and the General Ledger. This report is essential for:

  1. WIP Reconciliation: Validating that the WIP Valuation account balance in the GL matches the net activity of the shop floor.
  2. Variance Analysis: Identifying the sources of manufacturing variances (Material Usage, Resource Efficiency, etc.) at a high level before drilling down.
  3. Cost Element Analysis: Breaking down WIP costs into their components (Material, Labor, Overhead, OSP) to understand cost drivers.

Business Challenge

WIP is often the most complex area of inventory accounting.

The Solution

This report simplifies WIP accounting by aggregating transactions into meaningful buckets.

Technical Architecture (High Level)

The query uses a dynamic FROM clause (likely hidden in the &subledger_tab variable) to switch between data sources.

Parameters & Filtering

Performance & Optimization

FAQ

Q: Why do I see “Cost Update” transactions? A: If you change the standard cost of an item that is currently sitting in a WIP job, the system revalues the WIP balance. This “Cost Update” transaction ensures the job’s value reflects the new standard.

Q: How do I find the “Job Close Variance”? A: Look for the “WIP Variance” transaction type. When a job is closed, any remaining balance (difference between inputs and outputs) is written off to a variance account. This report summarizes those write-offs.

Q: Does this report show “Repetitive Schedules”? A: The description notes that it covers Discrete, Flow, and Workorderless, but not Repetitive Schedules. Repetitive manufacturing uses a different accounting model in Oracle EBS.

© 2026 Enginatics