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 Accounting Period Status

Description:

Report to show the accounting period status for General Ledger, Inventory, Lease Management, Payables, Projects, Purchasing and Receivables. You can choose All Statuses (open or closed or never opened), Closed, Open or Never Opened periods. And for process manufacturing (OPM), if your cost calendar has the same period end dates as your inventory accounting periods, this report will also display the OPM cost calendar status.

Note: this report automatically looks for hierarchies which might be used with the Open Period Control and the Close Period Control Oracle programs.

Parameters

Period Name - the desired inventory accounting period you wish to report (mandatory). Report Period Option - the parameter used to combine the Period Open and Period Close reports. The list of value choices are: Closed, Open, Never Opened or All Statuses (mandatory). Functional Area - the Oracle module(s) or functional area you wish to report. To see all functional areas leave this parameter empty (optional). Hierarchy Name - select the organization hierarchy used to open and close your inventory organizations. If you leave this parameter blank the report will try to find any organization hierarchy having “Period”, “Open”, or “Close” in the hierarchy name (optional) Organization Code: any inventory organization, defaults to your session’s inventory organization (optional). Operating Unit: Operating Unit you wish to report, leave blank for all operating units (optional). Ledger: general ledger you wish to report, leave blank for all ledgers (optional).

/* +=============================================================================+ – | SQL Code Copyright 2011-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 Volz (doug@volzconsulting.com) – | – | Version Modified on Modified by Description – | ======= =========== =============== ========================================= – | 1.0 19 Jan 2015 Douglas Volz Combined the xxx_period_open_status_rept.sql xxx_period_close_status_rept.sql into one report. Originally written in 2006 and 2011. – | 1.1 19 Jan 2015 Douglas Volz Added OPM Cost Calendar status and Projects. – | 1.2 19 Dec 2016 Douglas Volz Fixed list of value choices, was preventing purchasing operating units from being reported. – | 1.3 18 May 2016 Douglas Volz Minor fix for reporting the Organization Hierarchy – | 1.4 03 Dec 2018 Douglas Volz Open Periods option now checks for Summarized_Flag. – | 1.5 27 Oct 2019 Douglas Volz Condense A/R, A/P, Projects, Purchasing SQL logic – | 1.6 16 Jan 2020 Douglas Volz Add operating unit parameter – | 1.7 10 Apr 2020 Douglas Volz Made the following multi-language changes: Changed fnd_application to fnd_application_vl and changed hr_all_organization_units to hr_all_organization_units_vl – | 1.8 7 May 2020 Douglas Volz Added fnd_product_installations to only report installed applications. – | 1.9 26 May 2020 Douglas Volz Added lookup values and parameters for the organization_hierarchy_name subquery. – | 1.10 28 May 2020 Douglas Volz For language translation, replaced custom Report Options LOV with compound Oracle lookup values. – | 1.11 21 Jun 2020 Douglas Volz Added Organization Hierarchy as a separate parameter – | 1.12 07 Feb 2022 Eric Clegg Added who columns using xxen_util functions. – | 1.13 13 Mar 2022 Douglas Volz Modified for financial apps, to show blank who column values if status = Never Opened. – | 1.14 11 Jun 2024 Douglas Volz Replaced tabs with spaces. Reinstalled missing parameters. – +=============================================================================+*/

Parameters

Period Name, Report Option, Functional Area, Hierarchy Name, Operating Unit, Ledger

Used tables

hrfv_organization_hierarchies, org_acct_periods, mtl_parameters, hr_organization_information, hr_all_organization_units_vl, gl_ledgers, mfg_lookups, fnd_application_vl, fnd_product_installations, fnd_lookups, fnd_lookup_values_vl, gmf_fiscal_policies, gmf_period_statuses, hr_all_organization_units, gl_periods, gl_period_statuses

Categories

Enginatics

CAC Inventory Periods Not Closed or Summarized

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 Accounting Period Status 23-Jun-2022 144348.xlsx

Report SQL

www.enginatics.com/reports/cac-accounting-period-status/

Blitz Report™ import options

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