CAC Manufacturing Variance – 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 your summary or detail manufacturing variances for open and closed WIP jobs. If the job is open the Report Type column displays “Valuation”, as this WIP job and potential variances are still in your WIP inventory balances. If the job has been closed during the reporting period, the Report Type column displays “Variance”, as this WIP job was written off on a WIP Job Close Variance transaction. You can report prior periods and the report will automatically adjust the assembly completion, assembly scrap, component issue and resource quantities to reflect the reported accounting period, as well as report only jobs which were open or closed during that prior period.

Closed, Pending Close, Cancelled, Complete and Complete No Charges WIP job statuses use the completion quantities. All other WIP jobs use the parameter “Use Completion Quantities”. And if you use Standard Costing, for standard discrete jobs this report also shows your configuration and method variances; the difference between your WIP BOM/routing and your standard BOM/routing. Non-standard jobs usually do not have configuration variances, as they are “non-standard” without standard BOM or routing requirements.

– Copyright 2011-25 Douglas Volz Consulting, Inc.
– Version Modified on Modified by Description
– ======= =========== =============== =========================================
– 1.36 21 Dec 2024 Douglas Volz Fixes for Configuration and Method Variances.
– 1.37 02 Jan 2025 Douglas Volz Add Scrap Variance column, to avoid double-counting assy scrap.
– 1.38 04 Feb 2025 Douglas Volz Consolidate Summary and Detail reports into one report.
– 1.39 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

Report Mode, Report Option, Period Name, Cost Type, Include Scrap Quantities, Include Unreleased Jobs, Include Bulk Supply Items, Use Completion Quantities, Config/Lot Variances for Non-Std, Include Unimplemented ECOs, Alternate BOM Designator, Category Set 1, Category Set 2, Category Set 3, Organization Code, Class Code, Job Status, WIP Job, Component Number, Assembly Number, Outside Processing Item, Resource Code, Operating Unit, Ledger

Oracle EBS Tables Used

bom_alternate_designators_vl, mtl_material_transactions, mtl_parameters, wip_parameters, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, cst_cost_types, org_acct_periods, wip_discrete_jobs, wip_accounting_classes, mtl_system_items_vl, xxen_report_org_access, cst_item_costs, cst_item_cost_details, wip_requirement_operations, bom_structures_b, bom_components_b, mtl_system_items_b, cst_standard_costs, cst_resource_costs, cst_department_overheads, cst_resource_overheads, wip_operations, wip_operation_resources, wip_transactions, bom_resources, bom_operational_routings, bom_operation_sequences, bom_operation_resources, bom_parameters, po_distributions_all, wip_period_balances, dual, pa_projects_all, wip_entities, bom_departments

Report Categories

Enginatics

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, CAC WIP Account Summary, CAC Missing Material Accounting Transactions

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 Example
Blitz Report™ XML Import CAC_Manufacturing_Variance.xml
SQL Source CAC Manufacturing Variance.sql
Full Report on Enginatics www.enginatics.com/reports/cac-manufacturing-variance/

Case Study & Technical Analysis: CAC Manufacturing Variance

Executive Summary

The CAC Manufacturing Variance report is the definitive tool for analyzing production performance in a Standard Costing environment. It calculates and categorizes the difference between the Standard cost of a job and the Actual input costs. It distinguishes between “Valuation” (potential variance in Open jobs) and “Variance” (realized P&L impact in Closed jobs).

Business Challenge

Manufacturing variances are the primary indicator of shop floor efficiency.

Solution

This report provides a granular breakdown of the variance.

Technical Architecture

The report is a complex synthesis of WIP and Cost data:

Parameters

Performance

FAQ

Q: Why do I have a variance on an Open job? A: You don’t technically have a “variance” yet; you have a “potential variance”. If you issued all materials but haven’t completed the assembly, the WIP value is high. The report estimates the variance assuming the current state is final.

Q: What is a “Configuration Variance”? A: It occurs when the BOM used for the Job differs from the Standard BOM used for the Cost Rollup. E.g., substituting Part A for Part B.

Q: Does this match the GL? A: For Closed jobs, the total variance should match the WIP Variance account postings in the GL. For Open jobs, it represents the WIP Inventory balance.

© Enginatics GmbH