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 |
