Oracle-EBS-SQL

Library of Oracle EBS SQLs for operational Excel and ECC reporting, Discoverer replacement, Oracle EBS performance tuning and rapid report development.

View the Project on GitHub Enginatics/Oracle-EBS-SQL

CAC Manufacturing Variance Detail

Description:

Report your detailed manufacturing variances for your 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 this report will automatically adjust the assembly completion quantities and component issue quantities to reflect the quantities for the specified 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 primary or standard BOM/routing. Non-standard jobs usually do not have configuration variances, as they are “non-standard” without standard BOM or routing requirements.

Parameters:

Report Option: Open jobs, Closed jobs or All jobs. Use this to limit the size of the report. (mandatory) Period Name: the accounting period you wish to report. (mandatory) Cost Type: defaults to your Costing Method; if the cost type is missing component costs the report will find any missing item costs from your Costing Method cost type. Include Scrap Quantities: for calculating your completion quantities and component quantity requirements, include or exclude any scrapped assembly quantities. (mandatory) Include Unreleased Jobs: include jobs which have not been released and are not started. (mandatory) Include Bulk Supply Items: include Bulk items to match the results from the Oracle Discrete Job Value Report; exclude knowing that Bulk items are usually not issued to the WIP job. (mandatory) Use Completion Qtys: for jobs in a released status, use the completion quantities for the material usage and configuration variance calculations. Useful if you backflush your materials based on your completion quantities. Complete, Complete - No Charges, Cancelled, Closed, Pending Close or Failed Close alway use the completion quantities in the variance calculations. (mandatory) Config/Lot Variances for Non-Std: calculate configuration and lot variances for non-standard jobs. Include Unimplemented ECOs: include future BOMs changes. Alternate BOM Designator: if you save your BOMs during your Cost Rollups (based on your Cost Type step ups), use this parameter to get the correct BOMs for the configuration variance calculations. If you leave this field blank the report uses the latest BOM component effectivity date up to the period close date. (optional) Category Set 1: any item category you wish (optional). Category Set 2: any item category you wish (optional). Class Code: specific type of WIP class to report (optional). Job Status: specific WIP job status (optional). WIP Job: specific WIP job (optional). Assembly Number: specific assembly number you wish to report (optional) Component Number: specific component item you wish to report (optional) Outside Processing Item: Specific outside processing component to report (optional). Resource Code: Specific resource code to report (optional). Organization Code: any inventory organization, defaults to your session’s inventory organization (optional).

Copyright 2011-22 Douglas Volz Consulting, Inc.
Version Modified on Modified by Description
======= =========== =============== =========================================
1.33 13 Oct 2022 Douglas Volz Fix divide by zero error with the start quantity and
fix single-row subquery returns more than one row error..

Parameters

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, Organization Code, Class Code, Job Status, WIP Job, Component Number, Assembly Number, Outside Processing Item, Resource Code, Operating Unit, Ledger

Used tables

wip_discrete_jobs, org_acct_periods, mtl_parameters, wip_accounting_classes, mtl_system_items_vl, org_access_view, wdj0, mtl_material_transactions, wdj, cst_item_costs, cst_item_cost_details, cst_cost_types, wdj_assys, dual

Categories

Enginatics

Dependencies

If you would like to try one of these Oracle EBS SQLs without having Blitz Report installed, note that some of the reports require functions from utility package xxen_util.

Example Report

CAC Manufacturing Variance Detail 11-Oct-2022 225802.xlsx

Report SQL

www.enginatics.com/reports/cac-manufacturing-variance-detail/

Blitz Report™ import options

CAC_Manufacturing_Variance_Detail.xml

Oracle E-Business Suite Reporting Library

We provide an open source Oracle EBS SQLs as a part of operational and project implementation support toolkits for rapid Excel reports generation.

Blitz Report™ is based on Oracle EBS forms technology, and hence requires minimal training. There are no data or performance limitations since the output files are created directly from the database without going through intermediate file formats such as XML.

Blitz Report can be used as BI Publisher and Oracle Discoverer replacement tool. Standard Oracle BI Publisher and Discoverer reports can also be imported into Blitz Report for immediate output to Excel. Typically, reports can be created and version tracked within hours instead of days. The concurrent request output automatically opens upon completion without the need for re-formatting.

The Filters, Columns, Rows and Values fields are used to create and deliver the data in pivot table format with full drill down to details.

Blitz Report Pivots

The Excel template upload functionality in Blitz Report allows users to create their own layouts by uploading an Excel template with additional sheets and charts, automatically refreshed when the report runs again. This allows to create custom dashboards and more advanced visualizations of report data.

Blitz Report Dashboard

You can download and use Blitz Report free of charge for your first 30 reports.

The installation and implementation process usually takes less than 1 hour; you can refer to our installation and user guides for specific details.

Blitz Report™ YouTube Tutorials

Title Description
Oracle Enterprise Command Center (ECC) installation and integration with Oracle EBS 12.2 This video describes Oracle Enterprise Command Center Framework (ECC) installation and integration with Oracle EBS 12.2.
How to quickly create or modify Oracle E-Business Suite BI Publisher based reports BI publisher is used extensively in Oracle E-Business Suite. The standard Oracle design for the report layout often prevents the report from being exported to Excel in a usable format. Making changes to the displayed columns or parameters usually requires the submission of time consuming change requests that often sit in long queues waiting for development resource availability.
Blitz Report™ - Simple, effective Excel reporting for Oracle EBS R12 and 11i Blitz Report™ is the easiest and quickest way to export Oracle Applications R12 or 11i data into MS Excel.
ECC report generation in Oracle EBS tutorial This video provides instructions for rapid report development using standardized Oracle ECC SQL queries that generate Excel reports, in real time from EBS. These can be attached to any EBS form using the ‘Zoom’ toolbar option to replace the slow and inconvenient CSV export function. Benefits This method enables rapid report development using the same queries used by the ECC dashboards. Since these have been tested and approved by Oracle the output is assured.
Oracle E-Business Suite - Concurrent Request Monitoring and Alert Reporting Concurrent Request Management Critical concurrent requests should be monitored at all times to prevent problems in downstream processes. The standard method is to use Oracle Alert and email the output to the support or database teams. Other techniques involve running SQL with time based schedules handled by Cron. The alert method is somewhat out-dated, emails are in text format and Excel exception files cannot be sent.
How to develop proven EBS reports using Oracle Enterprise Command Center SQL’s in under 5 minutes Introduction Oracle Enterprise Command Center Framework provides information discovery, visualization, and exploration capabilities embedded within the Oracle E-Business Suite Dashboards are provided for functional areas such as General Ledger, Receivables, Payables, Project Accounting, Service Contracts, and Supply Chain modules. This article provides instructions for rapid report development using standardized Oracle ECC SQL queries within EBS.
Oracle Discoverer replacement. How to Import Oracle Discoverer Reports to EBS - Blitz Report™. Task: Oracle Discoverer replacement Audience: DBA’s, IT consultants and business operations Background: Oracle de-supported Discoverer in 2017. Recognizing that this is a significant and challenging step for EBS customers to undertake, the Enginatics team has developed an Oracle Discoverer replacement procedure with automated functionality to import Discoverer worksheets together with their security assignments and parameters.
Introduction to Blitz Report for Oracle EBS with the ERPStuff community Showing Blitz Report for SQL management and Excel reporting directly out of EBS
Blitz Report in 15 minutes Quick introduction to the world’s fastest reporting tool for Oracle E-Business Suite
Asset tracking integration with Install Base and Inventory Audience: EBS architects, consultants, and business analysts for asset planning and tracking Article Summary: Understanding the asset integrations, including the Enterprise Command Center and troubleshooting transactions with Blitz Report.
Data management and Enterprise Command Center enhancements (ECC) Support of ECC dashboards processes can be challenging at the best of times. In this tutorial, we cover the Blitz Report data governance reporting and managing the Enterprise Command Centers We also support EBS Excel dashboard output with unlimited record size from the EBS instance. This video is based on the session run by Webinar at the Oracle User Group for Scotland, the full version of that is available on the UKOUG site per the 11th June 2020.
Blitz Report - E-Business Suite SQL to Excel Reporting for Non Developers This recorded interactive session shows how easy it is for non-developers to get great results from Oracle EBS with Blitz Report.
How to create a DIFOT Sales Order Shipping Metric in Oracle E-Business Suite Why measure your supply chain performance? The delivered in full on time, or DIFOT, metric quantifies the number of successfully fulfilled sales orders. It is important to monitor this metric to quantify the performance of your supply chain. There are several factors that causes an adverse DIFOT rating in your supply chain.
The optimal way to analyse Automatic Workload Repository - AWR for Oracle EBS The Oracle’s Automatic Workload Repository (AWR) collects and maintains performance statistics for problem detection and self-tuning purposes.The report generated by AWR is typically large and requires significant years of experience to fully understand all aspects. The process is made harder by the fact that short module codes are used and it is not clear which package and line of code needs attention.
Blitz Report™ Tutorial - Running Blitz Report Blitz Report™ is the easiest and quickest way to export Oracle Applications R12 or 11i data into MS Excel.
Oracle E-Business Suite - How to Audit Master Data Changes with Blitz Report™ Tracking changes to Oracle EBS master data can be challenging using the standard Oracle reports and hence it is never implemented. Here we demonstrate how this can be easily done using the Blitz Report™ toolkit. The example used in this video is for supplier name / bank changes, but can be applied to any data element available in the Oracle.
Find the SQL behind an Oracle EBS standard form How to find the SQL queries behind an Oracle EBS standard form without running a trace
Blitz Report™ Tutorial - Building a Blitz Report Blitz Report™ is the easiest and quickest way to export Oracle Applications R12 or 11i data into MS Excel.
Find the SQL behind an OA Framework page in Oracle EBS How to find the SQL query behind and OAF page in Oracle EBS
Blitz Report and Upload in 20 minutes Introduction to Blitz Report, the fastest and easiest reporting and data upload for Oracle EBS.
Supply Chain Hub - Detailed Overview This is a complete focal hub for supply chain planners, everything a planner needs from Oracle EBS is provided in our central SCM hub with rapid output of any data to fully formatted Excel, but without the hour-glass’ waits per std form exports. There is no need to navigate out of the hub form for analysis or reporting as there is a full suite of PO, OM, Item, Material and MRP reports available within the hub. Even change org is available within the form.
Oracle E-Business Suite Inventory Period-End Optimization / Close The inventory period close process can often be challenging with the volume of stuck transactions typically faced. Oracle provide a pending transactions view with mandatory recommended resolutions. Inventory period close is towards the closure process which means it can often hold up the General Ledger accounting process and or other related modules.
Blitz Report™ Tutorial - Sending Blitz Report Output to Email and Scheduling a Report Blitz Report™ is the easiest and quickest way to export Oracle Applications R12 or 11i data into MS Excel.
Blitz Report™ Tutorial - Report parameters and output formats Blitz Report™ is the easiest and quickest way to export Oracle Applications R12 or 11i data into MS Excel.
Blitz Report™ – World’s fastest reporting for Oracle E-Business Suite Real-time data from all EBS operational areas, including reconciliation, alert reporting and performance tuning
Helping ASCP planners to achieve better planning results Many customers of the Oracle module – Advanced Supply Chain Planning (ASCP) are disappointed or just cannot get the planners to use the Planner Workbench (PWB). But what if there was an easier way? Finally, there is, we provide a full suite of planning reports completely free to use that run directly within the workbench. Delivering plan data to Excel in a much faster method than any other EBS reporting tool. There are no hourglass waits, or CSV files to reformat and no data limitations.
Oracle E-Business Suite - Sales Order Management - Order Organizer Optimization In this short video, we demonstrate how to optimize your Oracle Order Management processes and allow your customer services teams to get on with the real order entry work. The concept can be applied to any Oracle form. Having worked with customer services teams for many years, a common problem is quickly responding to order queries and or exporting large volumes of order line and header information from the order organizer.
Supply Chain Hub in 4 minutes Introduction to Supply Chain Hub, a very useful tool for anyone working with the Oracle EBS Supply Chain and Manufacturing modules.
Blitz Report™ Tutorial - Creating Report Parameters Blitz Report™ is the easiest and quickest way to export Oracle Applications R12 or 11i data into MS Excel.
Oracle ASCP - Plan by exception with Blitz Report™ Reviewing the exceptions generated by a plan allows planners to quickly pinpoint issues in the supply chain, this is usually done in a proactive way. The key challenge with this is that the standard Oracle Planner Workbench tools are not efficient and tend to be ad-hoc rather than automated. For each exception type displayed in the Planner Workbench, it is possible to drill into the detailed information about the nature of the exception.

If you would like to optimize your Oracle EBS implementation and or operational reporting you can visit www.enginatics.com to review great ideas and example usage in blog. Or why not try for yourself in our demo environment.

AlertCRM Foundation
Contracts CoreLease and Finance Management
PaymentsProcess Manufacturing
Process Manufacturing InventoryPublic Sector Financials
Shipping ExecutionXML Publisher
CapacityLegal Entity Configurator
ProjectsService Contracts
Advanced PricingApplications DBA
Enterprise Asset ManagementInstalled Base
PayrollSubledger Accounting
WorkflowE-Business Tax
Enterprise Command CenterBills of Material
Property ManagerAdvanced Supply Chain Planning
Work in ProcessHuman Resources
Master Scheduling/MRPOrder Management
Cash ManagementCost Management
PurchasingDiscoverer
AssetsPayables
General LedgerReceivables
InventoryBlitz Report
Database AdministrationApplication Object Library

Useful Links

Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Oracle Discoverer replacement – importing worksheets into Blitz Report™

Blitz Report™ Toolkits

Supply Chain Hub by Blitz Report™

Blitz Report™ customers

Oracle EBS Reporting Blog

Oracle EBS Reporting

© 2024 Enginatics