CAC Missing WIP Accounting Transactions – 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 to find work in process (WIP) accounting entries where the WIP transaction has been created but the WIP accounting entries do not exist. If you enter Yes for “Only Costed Resources” the report ignores WIP transactions where the resource code is defined as not allowing costs (not costed). If you enter No for “Only Costed Resources” the report includes WIP transactions where the resource code does not allow costs as well as costed resources. And to get all transactions which are missing the WIP accounting entries, even for transactions where the resources are not costed, set the “Only Costed Resources” to No and the Minimum Transaction Amount to zero (0).
/* +=============================================================================+ – | Copyright 2022 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. | – +=============================================================================+ – | – | Original Author: Douglas Volz (doug@volzconsulting.com) – | – | Program Name: missing_wip_accounting_transactions.sql – | – | Parameters: – | p_trx_date_from – Starting transaction date, mandatory – | p_trx_date_to – Ending transaction date, mandatory – | p_minimum_amount – The absolute smallest transaction amount to be reported – | p_only_costed_resources – Only include transactions where the resource code is costed. – | p_org_code – Specific inventory organization you wish to report (optional) – | p_operating_unit – Operating Unit you wish to report, leave blank for all – | operating units (optional) – | p_ledger – general ledger you wish to report, leave blank for all – | ledgers (optional) – | – | Description: – | Report to find WIP accounting entries where the WIP accounting entries do not – | exist. – | – | Version Modified on Modified by Description – | ======= =========== ============== ========================================= – | 1.0 21 Jul 2022 Douglas Volz Initial Coding based on missing_material_accounting_transactions.sql – | 1.1 23 Jul 2022 Douglas Volz Added Ledger and Operating Unit columns. – | 1.2 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
Transaction Date From, Transaction Date To, Minimum Transaction Amount, Only Costed Resources, Organization Code, Operating Unit, Ledger
Oracle EBS Tables Used
mtl_txn_source_types, cst_cost_types, cst_resource_costs, mtl_parameters, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, wip_transactions, org_acct_periods, wip_entities, bom_resources, cst_cost_elements, mtl_system_items_vl, xxen_report_org_access, wip_transaction_accounts
Report Categories
Related Reports
CAC Manufacturing Variance, CAC Missing Material Accounting Transactions, 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 ICP PII WIP Material Usage Variance
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 Missing WIP Accounting Transactions 23-Jul-2022 161739.xlsx |
| Blitz Report™ XML Import | CAC_Missing_WIP_Accounting_Transactions.xml |
| SQL Source | CAC Missing WIP Accounting Transactions.sql |
| Full Report on Enginatics | www.enginatics.com/reports/cac-missing-wip-accounting-transactions/ |
Case Study & Technical Analysis: CAC Missing WIP Accounting Transactions
Executive Summary
The CAC Missing WIP Accounting Transactions report is a diagnostic tool for the Work in Process (WIP) module. It identifies WIP transactions—such as Component Issues, Resource Transactions, and Assembly Completions—that have been processed but failed to generate the corresponding accounting entries in the WIP_TRANSACTION_ACCOUNTS table.
Business Challenge
- WIP Valuation: If a resource is charged to a job ($100 debit to WIP) but no accounting is generated, the WIP General Ledger balance will be understated compared to the operational reality.
- Period Close: Unaccounted transactions can prevent the accounting period from closing or lead to “Sweep” transactions that distort future periods.
- Cost Accuracy: Missing entries mean the job cost is incomplete, leading to incorrect variance calculations upon job close.
Solution
This report identifies the gaps.
- Logic: Compares
wip_transactionstowip_transaction_accounts. - Resource Filter: Can optionally ignore “Uncosted” resources (resources set up to not generate costs), reducing false positives.
- Scope: Covers all WIP transaction types.
Technical Architecture
- Tables:
wip_transactions(WT),wip_transaction_accounts(WTA). - Join:
WT.transaction_id = WTA.transaction_id (+). - Condition:
WTA.transaction_id IS NULL.
Parameters
- Transaction Date From/To: (Mandatory) Period.
- Only Costed Resources: (Mandatory) “Yes” is recommended to avoid seeing transactions for resources that are designed to be free.
- Minimum Amount: (Optional) To filter out zero-dollar transactions.
Performance
- Efficient: Uses standard anti-join logic.
- Volume: WIP transaction volume can be high in manufacturing environments, so date filtering is important.
FAQ
Q: How do I fix these? A: These often require a data fix from Oracle Support or a specialized script to re-trigger the “WIP Cost Manager”.
Q: Does this include “Move” transactions? A: Only if the Move transaction includes a “Resource” charge (Shop Floor Move). Pure moves often do not generate accounting unless they trigger a resource or overhead charge.
Useful Links
- 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
© Enginatics GmbH