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™.

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.

Parameters:

Variance Amount Threshold: maximum absolute WIP variance or current job balance that is allowed for jobs you wish to close (required). Variance Percent Threshold: maximum absolute WIP variance percentage that is allowed for jobs you wish to close. Based on WIP Job Balance / WIP Costs In. (required). Include Scrap Quantities: include scrapped assemblies in completion and component material requirements (required). Include Bulk Supply Items: include bulk WIP supply types in the component requirements (required). 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). Organization Code: enter the specific inventory organization(s) you wish to report (optional). Class Code: enter the WIP class code to report (optional). WIP Job: enter the WIP Job to report (optional). Assembly Number: enter the specific assembly number(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 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. – +=============================================================================+*/

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

fnd_lookups, mfg_lookups, wip_requirement_operations, mtl_system_items_b, wip_parameters, 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_split_merge_transactions, wsm_sm_starting_jobs, wsm_sm_resulting_jobs, wsm_split_merge_txn_interface, wsm_starting_jobs_interface, wsm_resulting_jobs_interface, wsm_resulting_lots_interface, wsm_lot_split_merges_interface

Report Categories

Enginatics, R12 only

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 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
Full SQL 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

© 2026 Enginatics