Skip to the content.

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

Enginatics

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

Solution

This report identifies the gaps.

Technical Architecture

Parameters

Performance

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.


© Enginatics GmbH