ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 5C7299AF0BEE58F9E0630100007F2779Y select z.report_id "EIS Report ID", z.application, z.category, z.report_name, xxen_util.yes(z.seeded_flag) seeded, xxen_util.yes(z.enable_report) enabled, z.source_type, z.runs, z.users, xxen_util.client_time(z.last_run) last_run, z.status, z.blitz_report, z.blitz_report_id "Blitz Report ID", xxen_util.client_time(z.import_date) import_date, xxen_util.client_time(z.blitz_last_update) blitz_last_update, xxen_util.yes(z.blitz_enabled) blitz_enabled, z.imported_tier, &migration_columns case when z.blitz_report_id is not null then nvl2(z.parse_error,'Error','Parsed') end parse_status, z.parse_error, z.eis_columns "EIS Columns", case when z.blitz_report_id is not null and z.parse_error is null then coalesce(z.template_columns,(select max(x.column_id) from table(xxen_util.sql_columns(regexp_replace(z.sql_text,chr(38)||'\w+'),'Y')) x)) end blitz_columns, z.eis_parameters "EIS Parameters", z.blitz_parameters, z.eis_pivots "EIS Pivots", z.blitz_templates, z.eis_grants "EIS Grants", z.blitz_assignments, z.db_package "DB Package", z.db_package_status "DB Package Status" from ( select z.*, case when z.blitz_report_id is null then 'Not Imported' when z.parse_error is not null or z.imported_tier in ('XXEIS Code','Trigger') then 'Imported with Issues' else 'Imported' end status, rownum row_num --keeps the import status and migration tier filters on the final rows, as xxen_eis.migration_tier generates the sql of each report it is called for from ( select err.report_id, era.application_name application, errc.category_name category, err.report_name, err.seeded_flag, err.enable_report, y.source_type, x.runs, x.users, x.last_run, xr.report_name blitz_report, xr.report_id blitz_report_id, xr.creation_date import_date, xr.last_update_date blitz_last_update, xr.enabled blitz_enabled, xr.tier imported_tier, case when xr.report_id is not null then (select regexp_substr(max(x.data_type),'.+') from table(xxen_util.sql_columns(regexp_replace(xr.sql_text,chr(38)||'\w+'),'Y')) x where x.column_name='---- error ----') end parse_error, xr.sql_text, (select count(*) from xxeis.eis_rs_report_columns errco where err.report_id=errco.report_id and nvl(errco.enabled_flag,'Y')='Y' and errco.display_flag='Y') eis_columns, xr.template_columns, (select count(*) from xxeis.eis_rs_report_parameters errp where err.report_id=errp.report_id and errp.enabled_flag='Y') eis_parameters, xr.parameters blitz_parameters, (select count(*) from xxeis.eis_rs_report_pivots errpi where err.report_id=errpi.report_id) eis_pivots, xr.templates blitz_templates, errs.grants eis_grants, xr.assignments blitz_assignments, xr.db_package, xr.db_package_status from xxeis.eis_rs_reports err, xxeis.eis_rs_applications era, xxeis.eis_rs_report_categories errc, ( select err.report_id, case when err.paste_view_sql is not null then 'Paste SQL' when erv.object_type='SQL' then 'SQL View' when ao.object_type='VIEW' then 'Database View' when ao.object_type='SYNONYM' then 'Synonym' when ao.object_type='TABLE' then 'Table' else 'Missing' end source_type from xxeis.eis_rs_reports err, xxeis.eis_rs_views erv, ( select erv.view_id, max(ao.object_type) keep (dense_rank first order by decode(ao.owner,erv.view_owner,1,'APPS',2,3)) object_type from xxeis.eis_rs_views erv, all_objects ao where erv.view_id in (select err.view_id from xxeis.eis_rs_reports err) and erv.view_name=ao.object_name and ao.owner in ('APPS','XXEIS') and ao.object_type in ('VIEW','SYNONYM','TABLE') group by erv.view_id ) ao where err.view_id=erv.view_id(+) and erv.view_id=ao.view_id(+) ) y, ( select erp.report_id, count(*) runs, count(distinct erp.created_by) users, max(erp.start_time) last_run from xxeis.eis_rs_processes erp where 2=2 and erp.submission_source in ('eXpress','XL Connect') group by erp.report_id ) x, ( select coalesce(errs.report_id,errsu.report_id) report_id, count(distinct errs.responsibility_id)+count(distinct errs.request_group_id)+count(distinct errs.user_id) grants from xxeis.eis_rs_report_security errs, xxeis.eis_rs_report_set_units errsu where errs.report_set_id=errsu.report_set_id(+) and sysdate between errs.start_date and nvl(errs.end_date,sysdate) group by coalesce(errs.report_id,errsu.report_id) ) errs, ( select xrt_eis.report_id eis_report_id, xr.report_id, xrt.report_name, regexp_substr(xrt.description,'migration tier ([^.]+)\.',1,1,null,1) tier, xr.creation_date, xr.last_update_date, case when xr.disabled is null then 'Y' end enabled, xr.sql_text, (select count(xrtc.display_sequence) from xxen_report_default_templates xrdt, xxen_report_template_columns xrtc where xr.report_id=xrdt.report_id and xrdt.user_id is null and xrdt.template_id=xrtc.template_id group by xrdt.template_id) template_columns, (select count(*) from xxen_report_parameters xrp where xr.report_id=xrp.report_id and xrp.display_sequence>0) parameters, (select count(*) from xxen_report_templates xrte where xr.report_id=xrte.report_id) templates, (select count(*) from xxen_report_assignments xra where xr.report_id=xra.report_id and xra.include_exclude='I' and xra.disabled is null) assignments, xr.db_package, (select uo.status from user_objects uo where upper(xr.db_package)=uo.object_name and uo.object_type='PACKAGE BODY') db_package_status from ( select err.report_id, min(xrt.report_id) keep (dense_rank first order by decode(xrt.report_name,err.report_name,1)) blitz_report_id from xxen_reports_tl xrt, xxeis.eis_rs_reports err where xrt.language=xxen_util.base_language and xrt.description like '%EIS Report ID: %' and to_number(regexp_substr(xrt.description,'^EIS Report ID: ([0-9]+)',1,1,'m',1))=err.report_id group by err.report_id ) xrt_eis, xxen_reports xr, xxen_reports_tl xrt where xrt_eis.blitz_report_id=xr.report_id and xr.report_id=xrt.report_id and xrt.language=xxen_util.base_language ) xr where 1=1 and err.application_id=era.application_id(+) and err.category_id=errc.category_id(+) and err.report_id=y.report_id and err.report_id=x.report_id(+) and err.report_id=errs.report_id(+) and err.report_id=xr.eis_report_id(+) ) z ) z where 3=3 order by z.runs desc nulls last, z.application, z.category, z.report_name New report: EIS eXpress reports matched to the Blitz Reports of the EIS import by the EIS Report ID line of the description, with import status, parse check, imported tier, EIS against Blitz Report counts of columns, parameters, pivots and grants, the trigger DB package status and optional migration analysis. US EIS Import Validation One row per EIS eXpress report with the Blitz Report the EIS import created from it, to check the import. A Blitz Report belongs to an EIS report when its description carries the line EIS Report ID: <report id>, which the import writes. Of several such Blitz Reports, a copy for example, the one named like the EIS report is shown, else the oldest. Status Imported with Issues: the Blitz SQL does not parse, or the import recorded migration tier XXEIS Code (the SQL still calls XXEIS objects) or Trigger (the report runs EIS trigger code in its DB package). Imported Tier is the tier the import wrote into the Blitz Report description, Migration Tier the tier of a generation from the current EIS definition. Parse Status parses the Blitz SQL on this database without its lexical parameters, up to a second per imported report, so restrict the selection for a quick check. EIS Columns counts the enabled and displayed columns, Blitz Columns the columns the global default template shows, else the columns of the SQL. EIS Parameters counts the enabled parameters, hidden ones included, Blitz Parameters those on the run screen; the import hides the hidden EIS parameters and leaves out those used nowhere. EIS Grants counts active grants with report sets expanded, Blitz Assignments the active include assignments. Runs, Users and Last Run count eXpress and XL Connect executions that started within Executed within Days. EIS &migration_columns 1=1 2=2 3=3 1 10 1=1 era.application_name=:application LOV custom Y select era.application_name value, era.description from xxeis.eis_rs_applications era order by era.application_name US Application 2 20 1=1 errc.category_name=:category LOV custom Y select distinct errc.category_name value, null description from xxeis.eis_rs_report_categories errc, xxeis.eis_rs_applications era where errc.application_id=era.application_id and (:$flex$.application is null or xxen_util.contains(:$flex$.application,era.application_name)='Y') order by errc.category_name US Category 3 30 1=1 err.report_name=:report_name LOV custom Y select err.report_name value, err.description from xxeis.eis_rs_reports err order by err.report_name US Report Name 4 40 1=1 err.seeded_flag='Y' LOV Oracle 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 Y US Seeded 5 1=1 err.seeded_flag is null N US Seeded 6 50 1=1 err.enable_report=:enabled LOV Oracle 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 US Enabled 7 60 1=1 y.source_type=:source_type LOV custom Y select 'Paste SQL' value, 'SQL pasted into the EIS report' description from dual union all select 'SQL View', 'SQL stored with an EIS view of type SQL' from dual union all select 'Database View', 'Database view referenced by name' from dual union all select 'Synonym', 'Synonym referenced by name' from dual union all select 'Table', 'Table referenced by name' from dual union all select 'Missing', 'No source found, the report cannot run in EIS' from dual US Source Type 8 70 2=2 erp.start_time>=sysdate-:executed_within_days Number 365 US Executed within Days Usage window in days before today, by run start. Blank counts the whole retained history. 9 80 1=1 x.runs>0 LOV Oracle 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 Y US Executed Yes lists the reports with a run within the usage window, No the others. 10 1=1 x.runs is null N US Executed 11 90 3=3 z.status=:import_status LOV custom Y select 'Not Imported' value, 'No Blitz Report carries the EIS report id' description from dual union all select 'Imported', 'SQL parses, migration tier Clean or Rewritten' from dual union all select 'Imported with Issues', 'SQL does not parse, or migration tier XXEIS Code or Trigger' from dual US Import Status 12 1=1 xr.report_id is null Not Imported US Import Status 13 100 &migration_columns xxen_eis.sql_shape(z.report_id) "SQL Shape", xxen_eis.migration_tier(z.report_id) migration_tier, xxen_eis.xxeis_objects(z.report_id) "XXEIS Objects", xxen_eis.migration_notes(z.report_id) migration_notes, LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show Migration Analysis Adds the SQL shape, migration tier, XXEIS objects and migration notes that the EIS import (package XXEN_EIS) generates from the current EIS definition. Generates the SQL of every listed report, about a second each, so restrict the selection. 14 110 3=3 xxen_eis.migration_tier(z.report_id)=:migration_tier LOV custom Y select x.value id, x.value, x.description from ( select 'Clean' value, 'No XXEIS code in the report' description from dual union all select 'Rewritten', 'XXEIS calls replaced by standard equivalents' from dual union all select 'XXEIS Code', 'Still calls XXEIS objects' from dual union all select 'Trigger', 'Runs EIS trigger code in a DB package' from dual union all select 'Not Supported', 'No Blitz Report can be generated' from dual ) x where :$flex$.show_migration_analysis='Y' US Migration Tier Tier of a generation from the current EIS definition. Available with Show Migration Analysis.