| layout | default |
|---|---|
| title | CAC WIP Account Value | Oracle EBS SQL Report |
| description | Report to show WIP values and all accounts for discrete manufacturing, in summary by inventory, organization, with WIP class, job status, name and other… |
| keywords | Oracle EBS, Oracle E-Business Suite, SQL report, Blitz Report, Cost Accounting - Inventory Value, Enginatics, CAC, WIP, Account, Value, wip_period_balances, wip_discrete_jobs, wip_entities |
| permalink | /CAC%20WIP%20Account%20Value/ |
Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.
Report to show WIP values and all accounts for discrete manufacturing, in summary by inventory, organization, with WIP class, job status, name and other details. This report uses the valuation accounts from each discrete job and reports both jobs which were open during the accounting period as well as jobs closed during the accounting period. You can also run this report for earlier accounting periods and still get the correct amounts and the jobs that were open at that time.
Period Name: the accounting period you wish to report (mandatory). Include Expense WIP: enter Yes to include Expense WIP jobs. Defaults to No. Job Status: enter a specific job status (optional). Category Sets 1 - 3: any item category you wish (optional). Item Number: specific item you wish to report (optional) Organization Code: specific inventory organization to report (optional) Operating Unit: specific operating unit (optional) Ledger: specific ledger (optional)
/* +=============================================================================+ -- | Copyright 2009 - 2025 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.
| -- +=============================================================================+ |
|---|
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| -- |
| +=============================================================================+*/ |
Period Name, Include Expense WIP, Job Status, Category Set 1, Category Set 2, Category Set 3, Item Number, Organization Code, Operating Unit, Ledger
wip_period_balances, wip_discrete_jobs, wip_entities, wip_accounting_classes, mtl_secondary_inventories, org_acct_periods, inv_organizations, mfg_lookups, gl_code_combinations_kfv, mtl_system_items_vl, mtl_units_of_measure_vl, pa_projects_all, wip_value
Cost Accounting - Inventory Value, Enginatics
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.
| Resource | Link |
|---|---|
| Excel Example Output | CAC WIP Account Value 10-Jul-2022 131938.xlsx |
| Blitz Report™ XML Import | CAC_WIP_Account_Value.xml |
| Full SQL on Enginatics | www.enginatics.com/reports/cac-wip-account-value/ |
The CAC WIP Account Value report is the standard "WIP Inventory Value" report. It calculates the value of Work in Process as of a specific period end. This value represents the "Asset" on the balance sheet—materials issued, labor charged, and overheads absorbed, minus any completions or scrap.
- Balance Sheet Validation: The Controller needs a detailed list of jobs that make up the $10M "WIP Inventory" asset.
- Aging: "Why is this job from 2019 still open and carrying value?" (Stale WIP).
- Costing Method: Works for both Standard (Variance based) and Average (Actual Cost based) costing.
This report calculates the balance.
- Formula:
Costs In (Material + Resource + Overhead) - Costs Out (Completion + Scrap) = Ending Balance. - Status: Shows if the job is Open, Complete, or Closed (if run for a prior period).
- Breakdown: Columns for Material, Labor, Overhead, OSP, etc.
- Tables:
wip_period_balances,wip_discrete_jobs. - Logic: Uses the snapshot table
wip_period_balanceswhich stores the value at the end of each period.
- Period Name: (Mandatory) The target period.
- Include Expense WIP: (Optional) Toggle to show non-asset jobs (e.g., Maintenance).
- Fast: Queries summary tables rather than summing individual transactions.
Q: Why is my WIP value negative? A: Usually timing. You completed the assembly (Credit) before you issued the components (Debit). This is a process error called "Negative WIP".
- Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
- Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
- Oracle EBS Reporting Toolkits by Blitz Report™
- Blitz Report™ FAQ & Community Q&A
- Supply Chain Hub by Blitz Report™
- Blitz Report™ Customer Case Studies
- Oracle EBS Reporting Blog
- Oracle EBS Reporting Resource Centre
© 2026 Enginatics