CAC WIP Jobs With Complete Status But Not 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” but exceed variance tolerances, have uncompleted assemblies, open material requirements, unearned OSP (outside processing) charges or 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. – | 1.10 26 Jan 2021 Douglas Volz Check for unissued materials and WIP scrap controls. And now – | use multi-language tables or views. – | 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 Which Are Ready for Close, GL Account Distribution Analysis, GL Account Analysis (Drilldown) (with inventory and WIP), GL Account Analysis, 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 But Not Ready for Close 10-Jul-2022 163050.xlsx
Blitz Report™ XML Import CAC_WIP_Jobs_With_Complete_Status_But_Not_Ready_for_Close.xml
SQL Source CAC WIP Jobs With Complete Status But Not Ready for Close.sql
Full Report on Enginatics www.enginatics.com/reports/cac-wip-jobs-with-complete-status-but-not-ready-for-close/

Case Study & Technical Analysis: CAC WIP Jobs With Complete Status But Not Ready for Close

Executive Summary

The CAC WIP Jobs With Complete Status But Not Ready for Close report is a “Pre-Close” validation tool. In Oracle WIP, changing a job status to “Closed” is irreversible and triggers final variance accounting. This report prevents premature closing by flagging jobs that have unresolved issues.

Business Challenge

Solution

This report acts as a gatekeeper.

Technical Architecture

Parameters

Performance

FAQ

Q: What should I do with the jobs on this report? A: Fix the issue (Issue the material, Receive the PO, Fix the interface error) then close the job.

© Enginatics GmbH