Blitz Report Custom Reports Converter – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Lists Oracle EBS concurrent programs that are candidates for conversion to Blitz Report - RDF reports, BI Publisher reports, and other data-extract concurrent programs - with usage statistics. Select the programs to convert with the Convert Program column, then upload the file. The upload submits a host concurrent program that gathers each selected program’s source artifacts (.rdf, .ldt, data definition, template, related packages and custom dependencies) into a single zip file for analysis by Enginatics.

With Select All Programs set to Yes, every listed row is downloaded with Convert Program set to Yes and already flagged for upload, so after filtering the list you only need to set Convert Program to No on the programs you do not want and upload the file.

With Include Custom Views set to Yes, the list additionally shows custom database views whose name starts with the Custom Object Prefix. Selecting a view gathers its definition and its custom dependency closure into the same zip - use this to extract the views that Discoverer-migrated or other custom reports are based on.

With Include Noetix Views set to Yes on an instance running NoetixViews, the NOETIX_SYS query views are listed as program type Noetix View. Selecting one gathers the complete view definition closure - the view, its NOETIX_SYS helper views and packages, and the security wrapper views - into a noetix subfolder of the zip, as input for migrating Noetix based reporting to Blitz Report. Excel4apps Reports Wand programs show as program type Excel4apps and gather their report SQL.

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

Customer Abbreviation, Custom Object Prefix, Select All Programs, Application, Program Type, Execution Method, User Concurrent Program Name, User Concurrent Program Name (contains), Concurrent Program Short Name, Concurrent Program Short Name (contains), Custom Programs Only, Execution File Name (contains), Request Group, Responsibility, Minimum Run Count, Minimum Distinct Users, Last Run Date From, Last Run Date To, Include Custom Views, Custom View prefix, Include Noetix Views

Oracle EBS Tables Used

xdo_ds_definitions_b, fnd_application_vl, fnd_concurrent_programs_vl, fnd_executables_vl, fnd_concurrent_requests, dba_views, dba_objects

Report Categories

Enginatics, Upload

XDO Publisher Data Definitions, DBA Sessions, FND Concurrent Requests, Blitz Report RDF Import Validation, ECC Admin - Concurrent Programs, FND Concurrent Requests Summary

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 Blitz_Report_Custom_Reports_Converter.xml
SQL Source Blitz Report Custom Reports Converter.sql
Full Report on Enginatics www.enginatics.com/reports/blitz-report-custom-reports-converter/

Case Study & Technical Analysis: Blitz Report Custom Reports Converter

Executive Summary

The Blitz Report Custom Reports Converter is a discovery and collection report for organizations that want to move older Oracle EBS reporting into Blitz Report™. It lists concurrent programs that are candidates for conversion, including RDF reports, BI Publisher reports and data-extract programs, together with their run statistics. The user selects the programs to convert and uploads the file, which starts a host concurrent program that gathers each program’s source artifacts into one zip file for analysis by Enginatics. Migration leads and reporting teams use it to decide which custom reports are worth converting, based on how often they run and how many users run them.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. FND_APPLICATION_VL is joined to FND_CONCURRENT_PROGRAMS_VL on application ID.
  2. FND_CONCURRENT_PROGRAMS_VL is joined to FND_EXECUTABLES_VL on the executable application ID and executable ID.
  3. An inline view summarises FND_CONCURRENT_REQUESTS by program ID and program application ID, and is outer joined so programs that never ran still appear with zero counts.
  4. A CASE expression classifies each program as Excel4apps, BI Publisher, RDF or Data Extract.
  5. Two further branches, unioned to the program list, return custom views and Noetix views from DBA_VIEWS joined to DBA_OBJECTS on owner and name.

Parameters & Filtering

Performance & Optimization

FAQ

Q: How do I find custom RDF reports that ran at least ten times in the last year? A: Set Program Type to RDF, Custom Programs Only to Yes, Minimum Run Count to 10 and Last Run Date From to a date one year back.

Q: What does the converter zip contain? A: For each selected program it gathers the executable, data definition, template, related packages and custom dependencies, and for a selected view it gathers the view definition and its custom dependency closure.

Q: Which programs does Select All Programs include? A: Every row the filters return. Set Convert Program to No on any row you do not want collected before uploading.

© Enginatics GmbH