CAC WIP Jobs With Complete Status Which Are Ready for Close – 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 WIP jobs which have a status of “Complete”, do not exceed variance tolerances, have completed or exceeded the WIP start quantity, with no open material requirements, no unearned OSP (outside processing) charges and no stuck transactions in interfaces. When you include scrap quantities, any scrapped assemblies are counted with the completed units. Note that for material requirements, expense items are ignored.

/* +=============================================================================+ – | Copyright 2017 - 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. – +=============================================================================+ – | – | Original Author: Douglas A. Volz – | – | Version Modified on Modified by Description – | ======= =========== ============= ========================================= – | 1.0 16 MAR 2017 Douglas Volz Initial Coding – | 1.1 19 MAR 2017 Douglas Volz Added interface conditions for eligibility – | and check for no applied OSP – | 1.3 27 MAR 2017 Douglas Volz Fix “return more than one row” error for – | correlated sub-query on OSP and add in check for purchase requisitions – | 1.4 27 APR 2017 Douglas Volz Fix for cross-joining results – | 1.6 25 Oct 2017 Douglas Volz Remove p_date_completed parameter, not needed – | 1.7 25 Jul 2018 Douglas Volz Removed all categories except Inventory – | 1.8 25 Jul 2018 Douglas Volz Removed all category values – | 1.9 11 Dec 2020 Douglas Volz Now for Standard, Lot Based Standard and Non- – | Standard Asset Jobs. Added another category. – | 1.10 26 Jan 2021 Douglas Volz Check for unissued materials and WIP scrap controls – | 1.11 11 Feb 2021 Douglas Volz Added parameter to include scrap for requirements – | 1.12 05 Mar 2021 Douglas Volz Added parameter to include bulk items for requirements. – | 1.13 12 Mar 2021 Douglas Volz Add logic to ignore Phantom WIP Supply Types as – | these requirements are never issued. – | 1.14 15 Apr 2021 Douglas Volz Added Date Released – | 1.15 10 Jul 2022 Douglas Volz Added WIP Variance Percentage parameter. – | 1.16 21 Jan 2024 Douglas Volz Bug fix for Pending Material and Pending Shop Floor – | Move. Remove tabs and add inventory access controls. – | 1.17 24 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

Variance Amount Threshold, Variance Percent Threshold, Include Scrap Quantities, Include Bulk Supply Items, Category Set 1, Category Set 2, Category Set 3, Organization Code, Class Code, WIP Job, Assembly Number, Operating Unit, Ledger

Oracle EBS Tables Used

wip_requirement_operations, mtl_system_items_b, wip_operation_resources, mtl_material_transactions_temp, mtl_material_transactions, wip_cost_txn_interface, rcv_transactions_interface, po_requisitions_interface, mtl_transactions_interface, wip_move_txn_interface, wsm_sm_starting_jobs, wsm_split_merge_transactions, wsm_sm_resulting_jobs, wsm_starting_jobs_interface, wsm_split_merge_txn_interface, wsm_resulting_jobs_interface, wsm_resulting_lots_interface, wsm_lot_split_merges_interface, wsm_lot_job_interface, wip_discrete_jobs, wip_entities, wip_accounting_classes, mtl_parameters, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, mtl_system_items_vl, wip_period_balances, xxen_report_org_access

Report Categories

Enginatics

CAC WIP Jobs With Complete Status But Not Ready for Close, GL Account Distribution Analysis, GL Account Analysis, GL Account Analysis (Drilldown) (with inventory and WIP), CAC Interface Error Summary, INV Period Close Pending Transactions, INV Material Account Distribution Detail, CAC Receiving Value (Period-End)

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 Jobs With Complete Status Which Are Ready for Close 10-Jul-2022 155448.xlsx
Blitz Report™ XML Import CAC_WIP_Jobs_With_Complete_Status_Which_Are_Ready_for_Close.xml
SQL Source CAC WIP Jobs With Complete Status Which Are Ready for Close.sql
Full Report on Enginatics www.enginatics.com/reports/cac-wip-jobs-with-complete-status-which-are-ready-for-close/

Executive Summary

This report identifies Work in Process (WIP) jobs that have a status of “Complete” and meet all the necessary criteria to be safely closed. It validates that the jobs are within specified variance tolerances, have no open material requirements, no unearned Outside Processing (OSP) charges, and no stuck transactions in the open interfaces. This tool is essential for the period-end close process, allowing the Cost Accounting team to confidently bulk-close jobs that have passed all validation checks, thereby recognizing variances and keeping the WIP valuation clean.

Business Challenge

Closing WIP jobs is a critical step in the manufacturing accounting cycle. When a job is closed, the system calculates the final variances (difference between costs incurred and costs relieved) and posts them to the General Ledger. However, closing a job prematurely—before all transactions are processed or if there are significant unexplained variances—can lead to:

Identifying which of the thousands of “Complete” jobs are actually ready to be closed requires checking multiple conditions across different tables (interfaces, material requirements, OSP status), which is manually impossible.

Solution

The CAC WIP Jobs With Complete Status Which Are Ready for Close report automates this validation logic. It acts as a “green light” report, listing only those jobs that have passed a battery of integrity checks and are within acceptable variance thresholds.

Key Validation Checks:

Architecture

The report queries the WIP_DISCRETE_JOBS table as the primary source, joining with WIP_PERIOD_BALANCES to calculate current costs incurred and relieved. It uses NOT EXISTS subqueries to ensure no pending transactions exist in the interface tables.

Key Tables:

Impact

© Enginatics GmbH