ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 51E3BF0C73BA1799E0630100007FF20FY select case when :select_all_programs='Y' then xxen_upload.action_meaning(xxen_upload.action_update) end action_, case when :select_all_programs='Y' then xxen_upload.status_meaning(xxen_upload.status_new) end status_, case when :select_all_programs='Y' then xxen_util.description('U_EXCEL_MSG_VALIDATION_PENDING','XXEN_REPORT_TRANSLATIONS',0) end message_, cast(null as varchar2(240)) gather_status, null modified_columns_, xxen_util.yes(:select_all_programs) convert_program, x.application, x.program, x.short_name, x.program_type, x.executable, x.method, x.execution_file_name, x.output_format, x.run_count, x.distinct_users, x.last_run_date, x.first_run_date, x.created_by, x.creation_date, x.concurrent_program_id, x.application_id, upper(:customer_abbreviation) customer_abbreviation, upper(:custom_prefix) custom_prefix, cast(null as number) request_id_, rownum upload_row from ( select fav.application_name application, fcpv.user_concurrent_program_name program, fcpv.concurrent_program_name short_name, case when fev.executable_name like 'XXE4A%' then 'Excel4apps' when exists (select null from xdo_ds_definitions_b xddb where xddb.data_source_code=fcpv.concurrent_program_name and xddb.application_short_name=fav.application_short_name) then 'BI Publisher' when fev.execution_method_code='P' then 'RDF' else 'Data Extract' end program_type, fev.executable_name executable, xxen_util.meaning(fev.execution_method_code,'CP_EXECUTION_METHOD_CODE',0) method, fev.execution_file_name, xxen_util.meaning(fcpv.output_file_type,'CP_OUTPUT_FILE_TYPE',0) output_format, nvl(stats.run_count,0) run_count, nvl(stats.distinct_users,0) distinct_users, xxen_util.client_time(stats.last_run_date) last_run_date, xxen_util.client_time(stats.first_run_date) first_run_date, xxen_util.user_name(fcpv.created_by) created_by, xxen_util.client_time(fcpv.creation_date) creation_date, fcpv.concurrent_program_id, fcpv.application_id from fnd_application_vl fav, fnd_concurrent_programs_vl fcpv, fnd_executables_vl fev, ( select fcr.concurrent_program_id, fcr.program_application_id, count(*) run_count, count(distinct fcr.requested_by) distinct_users, max(fcr.actual_start_date) last_run_date, min(fcr.actual_start_date) first_run_date from fnd_concurrent_requests fcr group by fcr.concurrent_program_id, fcr.program_application_id ) stats where 3=3 and fav.application_id=fcpv.application_id and fcpv.executable_application_id=fev.application_id and fcpv.executable_id=fev.executable_id and fcpv.concurrent_program_id=stats.concurrent_program_id(+) and fcpv.application_id=stats.program_application_id(+) and nvl(fcpv.enabled_flag,'Y')='Y' union all select dv.owner application, dv.view_name program, dv.view_name short_name, 'Custom View' program_type, null executable, 'View' method, null execution_file_name, null output_format, to_number(null) run_count, to_number(null) distinct_users, to_date(null) last_run_date, to_date(null) first_run_date, null created_by, xxen_util.client_time(do.created) creation_date, to_number(null) concurrent_program_id, to_number(null) application_id from dba_views dv, dba_objects do where 2=2 and :include_custom_views='Y' and dv.owner=do.owner and dv.view_name=do.object_name and do.object_type='VIEW' union all select 'Noetix' application, dv.view_name program, dv.view_name short_name, 'Noetix View' program_type, null executable, 'View' method, null execution_file_name, null output_format, to_number(null) run_count, to_number(null) distinct_users, to_date(null) last_run_date, to_date(null) first_run_date, null created_by, xxen_util.client_time(do.created) creation_date, to_number(null) concurrent_program_id, to_number(null) application_id from dba_views dv, dba_objects do where :include_noetix_views='Y' and dv.owner='NOETIX_SYS' and dv.view_name not like 'XXK%' and dv.view_name not like '%#' and dv.view_name not like '%\_BASE' escape '\' and dv.view_name not like 'N\_%' escape '\' and dv.owner=do.owner and dv.view_name=do.object_name and do.object_type='VIEW' ) x where 1=1 Upload Mode parameter removed: the upload only ever lists existing programs, so Create mode returned nothing. New parameter Select All Programs downloads every listed row with Convert Program set to Yes and the row flagged for upload, so a filtered list can be uploaded after setting the exceptions to No; Convert Program now uses the Yes_No value set. New parameter Include Noetix Views gates the NOETIX_SYS branch, which previously listed the Noetix views regardless of the program filters entered. U A xxen_report_converter.process_row xxen_report_converter.submit_converter US Blitz Report Custom Reports Converter 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. Enginatics Upload 1=1 2=2 3=3 :custom_prefix :customer_abbreviation :include_custom_views :include_noetix_views :select_all_programs 1 10 :customer_abbreviation Char Y US Customer Abbreviation 1–5 alphanumeric characters (e.g. ACME), agreed with Enginatics. 2 20 :custom_prefix Char XX Y US Custom Object Prefix Override only if your custom objects don’t follow Oracle’s XX convention. If you identify your custom objects with more than one prefix, enter them separated by commas, for example XX,APL. Every prefix is used when collecting the custom dependencies of the programs you select. 3 30 :select_all_programs LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Select All Programs Downloads every listed row with Convert Program set to Yes and already flagged for upload. Set Convert Program to No on the rows you do not want, then upload the file. 4 40 1=1 x.application=:application LOV custom Y select fav.application_name value, fav.application_short_name description from fnd_application_vl fav order by fav.application_name US Application 5 50 1=1 x.program_type=:program_type LOV custom Y select 'RDF' value, 'Oracle Reports (.rdf) report' description from dual union all select 'BI Publisher' value, 'XML Publisher / BI Publisher report' description from dual union all select 'Data Extract' value, 'Other concurrent program producing a data file' description from dual union all select 'Excel4apps' value, 'Excel4apps Reports Wand report' description from dual union all select 'Custom View' value, 'Custom database view (definition gather only)' description from dual union all select 'Noetix View' value, 'NoetixViews query view (view closure gather only)' description from dual US Program Type 6 60 3=3 fev.execution_method_code=xxen_util.lookup_code(:execution_method,'CP_EXECUTION_METHOD_CODE',0) LOV FND Concurrent Execution Method 8E2FF36EDED979D2E0530100007F1FF2 Y select flvv.meaning value, flvv.lookup_code description from fnd_lookup_values_vl flvv where flvv.lookup_type='CP_EXECUTION_METHOD_CODE' and flvv.view_application_id=0 and flvv.security_group_id=0 order by flvv.meaning US Execution Method The execution method of the program's executable, for example Oracle Reports, PL/SQL Stored Procedure, Host or SQL*Loader. Finer grained than Program Type. Does not restrict custom view rows. 7 70 3=3 fcpv.user_concurrent_program_name=:program_name LOV FND Concurrent Program Name 8E2FF36EDE9D79D2E0530100007F1FF2 Y select fcpt.user_concurrent_program_name value, fav.application_name||nvl2(fcpt.description,': ',null)||fcpt.description description from fnd_application_vl fav, fnd_concurrent_programs_tl fcpt where fcpt.language=userenv('lang') and fcpt.application_id=fav.application_id order by fcpt.user_concurrent_program_name US User Concurrent Program Name Exact match on the user visible concurrent program name. Several programs can be selected at once with the multiple values option. 8 80 3=3 upper(fcpv.user_concurrent_program_name) like '%'||upper(:program_name_contains)||'%' LOV FND Concurrent Program Name (wildcard) 8E2FF36EDE9179D2E0530100007F1FF2 select fcpt.user_concurrent_program_name value, fav.application_name||nvl2(fcpt.description,': ',null)||fcpt.description description from fnd_application_vl fav, fnd_concurrent_programs_tl fcpt where fcpt.language=userenv('lang') and fcpt.application_id=fav.application_id order by fcpt.user_concurrent_program_name US User Concurrent Program Name (contains) Lists every program whose user visible name contains the entered text. The list of values only suggests values, any text can be typed. Single value only. 9 90 3=3 fcpv.concurrent_program_name=:short_name LOV FND Concurrent Program Short Name 8E2FF36EDEA179D2E0530100007F1FF2 Y select fcpv.concurrent_program_name value, fav.application_name||': '||fcpv.user_concurrent_program_name description from fnd_application_vl fav, fnd_concurrent_programs_vl fcpv where fav.application_id=fcpv.application_id order by fcpv.concurrent_program_name US Concurrent Program Short Name Exact match on the internal concurrent program short name, which is also the name used for the gathered source files. Several programs can be selected at once with the multiple values option. 10 100 3=3 upper(fcpv.concurrent_program_name) like '%'||upper(:short_name_contains)||'%' LOV custom select fcpv.concurrent_program_name value, fav.application_name||': '||fcpv.user_concurrent_program_name description from fnd_application_vl fav, fnd_concurrent_programs_vl fcpv where fav.application_id=fcpv.application_id order by fcpv.concurrent_program_name US Concurrent Program Short Name (contains) Lists every program whose short name contains the entered text, for example a module abbreviation. The list of values only suggests values, any text can be typed. Single value only. 11 110 3=3 regexp_like(fcpv.concurrent_program_name,'^('||regexp_replace(upper(:custom_prefix),'[[:space:]]*[,;][[:space:]]*','|')||')') LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y US Custom Programs Only Restricts the list to programs whose short name starts with any of the prefixes entered in Custom Object Prefix, which is usually the quickest way to isolate a customer's own developments. 12 120 3=3 upper(fev.execution_file_name) like '%'||upper(:execution_file_name)||'%' LOV custom select distinct fev.execution_file_name value, null description from fnd_executables_vl fev where fev.execution_file_name is not null order by fev.execution_file_name US Execution File Name (contains) Lists every program whose executable file name contains the entered text. Useful to find all programs sharing the same report file, which only needs to be converted once. 13 130 3=3 exists ( select null from fnd_request_groups frg, fnd_request_group_units frgu where frg.request_group_name=:request_group and frg.application_id=frgu.application_id and frg.request_group_id=frgu.request_group_id and frgu.unit_application_id=fcpv.application_id and (frgu.request_unit_type='A' or frgu.request_unit_type='P' and frgu.request_unit_id=fcpv.concurrent_program_id) ) LOV FND Request Group 8E2FF36EDE8F79D2E0530100007F1FF2 Y select frg.request_group_name value, frg.description from fnd_request_groups frg where (:$flex$.fnd_application_name is null or frg.application_id in (select fav.application_id from fnd_application_vl fav where fav.application_name=:$flex$.fnd_application_name)) order by frg.request_group_name US Request Group Lists the programs a request group can run, either assigned individually or through one of its applications. Programs reachable only through a request set in the group are not listed. Request group names are not unique across applications, so a name used by several applications matches the programs of all of them. 14 140 3=3 exists ( select null from fnd_responsibility_vl frv, fnd_request_group_units frgu where frv.responsibility_name=:responsibility and frv.group_application_id=frgu.application_id and frv.request_group_id=frgu.request_group_id and frgu.unit_application_id=fcpv.application_id and (frgu.request_unit_type='A' or frgu.request_unit_type='P' and frgu.request_unit_id=fcpv.concurrent_program_id) ) LOV FND Responsibility Name 8E2FF36EDE9E79D2E0530100007F1FF2 Y select frv.responsibility_name value, frv.description from fnd_responsibility_vl frv order by frv.responsibility_name US Responsibility Lists the programs a responsibility can run through its request group. Programs reachable only through a request set are not listed. Responsibility names are not unique across applications, so a name used by several applications matches the programs of all of them. 15 150 1=1 (x.run_count>=:minimum_run_count or x.program_type in ('Custom View','Noetix View')) Number US Minimum Run Count 16 160 1=1 (x.distinct_users>=:minimum_distinct_users or x.program_type in ('Custom View','Noetix View')) Number US Minimum Distinct Users Only lists programs run by at least this many different users, which helps to separate widely used reports from one off developments. 17 170 1=1 (x.last_run_date>=:last_run_date_from or x.program_type in ('Custom View','Noetix View')) Date US Last Run Date From 18 180 1=1 (x.last_run_date<:last_run_date_to+1 or x.program_type in ('Custom View','Noetix View')) Date US Last Run Date To Only lists programs last run on or before this date. Left empty by default so that entering Last Run Date From on its own keeps its usual meaning of everything still in use since that date. 19 190 :include_custom_views LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Include Custom Views Additionally list custom database views whose name starts with the Custom Object Prefix, so their definitions can be gathered for analysis (e.g. views that Discoverer-migrated reports are based on). 20 200 2=2 regexp_like(dv.view_name,'^('||regexp_replace(upper(:custom_view_prefix),'[[:space:]]*[,;][[:space:]]*','|')||')') LOV custom Y select distinct dv.view_name id, dv.view_name value, null description from dba_views dv where :$flex$.include_custom_views='Y' order by 1 US Custom View prefix 21 210 :include_noetix_views LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Include Noetix Views Additionally list the NOETIX_SYS query views of a NoetixViews installation as program type Noetix View, so their view definition closure can be gathered for migration. 80B933A2A9234FE5B97A66E577E9458E Default All columns. ENGINATICS 3 APPLICATION 2 CONVERT_PROGRAM 15 CREATED_BY 16 CREATION_DATE 12 DISTINCT_USERS 7 EXECUTABLE 9 EXECUTION_FILE_NAME 14 FIRST_RUN_DATE 1 GATHER_STATUS 13 LAST_RUN_DATE 8 METHOD 10 OUTPUT_FORMAT 4 PROGRAM 6 PROGRAM_TYPE 11 RUN_COUNT 5 SHORT_NAME Y S Site 80B933A2A9234FE5B97A66E577E9458E 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 GATHER_STATUS Char varchar2 240 Y 5 MODIFIED_COLUMNS_ Char varchar2 Y 6 CONVERT_PROGRAM LOV Oracle varchar2 10 Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Set to Yes for each program you want to convert to Blitz Report. No or blank excludes the program. 7 APPLICATION Char varchar2 240 Y 8 PROGRAM Char varchar2 240 Y 9 SHORT_NAME Char varchar2 128 Y 10 PROGRAM_TYPE Char varchar2 12 Y 11 EXECUTABLE Char varchar2 30 Y 12 METHOD Char varchar2 Y 13 EXECUTION_FILE_NAME Char varchar2 61 Y 14 OUTPUT_FORMAT Char varchar2 Y 15 RUN_COUNT Number number Y 16 DISTINCT_USERS Number number Y 17 LAST_RUN_DATE Date date Y 18 FIRST_RUN_DATE Date date Y 19 CREATED_BY Char varchar2 Y 20 CREATION_DATE Date date Y 21 CONCURRENT_PROGRAM_ID Number number Y Y 22 APPLICATION_ID Number number Y Y 23 CUSTOMER_ABBREVIATION Char varchar2 2000 Y Y 24 CUSTOM_PREFIX Char varchar2 2000 Y Y 25 REQUEST_ID_ Number number Y Y 26 UPLOAD_ROW Number number Y U 1 status_ out Char STATUS_CODE_ U 2 message_ out Char MESSAGE_ U 3 p_convert in Char CONVERT_PROGRAM U 4 p_concurrent_program_id in Number CONCURRENT_PROGRAM_ID U 5 p_application_id in Number APPLICATION_ID U 6 p_program_type in Char PROGRAM_TYPE U 7 p_program in Char PROGRAM P 1 p_convert in Char CONVERT_PROGRAM P 2 request_id_ out Number REQUEST_ID_ P 3 status_ out Char STATUS_CODE_ P 4 message_ out Char MESSAGE_ Error SQL Order By upload_row Success SQL select xu.action_, xu.status_, xu.message_, xu.gather_status, xu.modified_columns_, xu.convert_program, xu.application, xu.program, xu.short_name, xu.program_type, xu.executable, xu.method, xu.execution_file_name, xu.output_format, xu.run_count, xu.distinct_users, xu.last_run_date, xu.first_run_date, xu.created_by, xu.creation_date, xu.concurrent_program_id, xu.application_id, xu.customer_abbreviation, xu.custom_prefix, xu.request_id_, xu.upload_row from xxen_blitz_report_custo_8552_u xu where xu.status_code_=xxen_upload.status_success