select
err.report_id "EIS Report ID",
era.application_name application,
errc.category_name category,
err.report_name,
err.description,
xxen_util.user_name(err.report_owner) owner,
xxen_util.yes(err.seeded_flag) seeded,
xxen_util.yes(err.enable_report) enabled,
(select err_c.report_name from xxeis.eis_rs_reports err_c where err.copied_from_report_id=err_c.report_id) copied_from,
err.view_name,
y.view_owner,
y.source_type,
y.view_status,
(select count(*) from xxeis.eis_rs_report_columns errco where err.report_id=errco.report_id and nvl(errco.enabled_flag,'Y')='Y') columns,
(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.derived_flag='Y') derived_columns,
(select count(*) from xxeis.eis_rs_report_views errv where err.report_id=errv.report_id and errv.view_component_id is not null) components,
(select count(*) from xxeis.eis_rs_report_parameters errp where err.report_id=errp.report_id and errp.enabled_flag='Y') parameters,
(select count(*) from xxeis.eis_rs_report_parameters errp where err.report_id=errp.report_id and errp.enabled_flag='Y' and errp.required_flag='Y') required_parameters,
(select count(*) from xxeis.eis_rs_report_cond_headers errch where err.report_id=errch.report_id and errch.enabled='Y') conditions,
(select count(*) from xxeis.eis_rs_report_cond_headers errch where err.report_id=errch.report_id and errch.enabled='Y' and errch.condition_type='FREE_TEXT') free_text_conditions,
(select count(*) from xxeis.eis_rs_sorts erso where err.report_id=erso.report_id) sorts,
(select count(*) from xxeis.eis_rs_report_pivots errpi where err.report_id=errpi.report_id) pivots,
(select count(*) from xxeis.eis_rs_report_triggers errt where err.report_id=errt.report_id) triggers,
xxen_util.yes(err.distinct_flag) "Distinct",
xxen_util.yes(err.group_by_flag) "Group By",
x.runs,
x.scheduled_runs,
x.runs-x.scheduled_runs ad_hoc_runs,
x.xl_connect_runs "XL Connect Runs",
x.users,
xxen_util.client_time(x.last_run) last_run,
xxen_util.user_name(x.last_user_id) last_user,
x.errors,
x.average_rows,
x.average_seconds,
x.deliveries,
x.email_deliveries,
x.ftp_deliveries "FTP Deliveries",
fcr.active_schedules,
errs.responsibility_grants,
errs.request_group_grants,
errs.user_grants,
(select count(*) from xxeis.eis_rs_report_favorites errf where err.report_id=errf.report_id) favorites,
coalesce(xrt_id.report_name,xrt_name.report_name) blitz_report,
&migration_columns
&source_sql
xxen_util.user_name(err.created_by) created_by,
xxen_util.client_time(err.creation_date) creation_date,
xxen_util.user_name(err.last_updated_by) last_updated_by,
xxen_util.client_time(err.last_update_date) last_update_date
from
xxeis.eis_rs_reports err,
xxeis.eis_rs_applications era,
xxeis.eis_rs_report_categories errc,
(
select
x.report_id,
case
when x.paste_view_sql is not null then 'Paste SQL'
when x.view_type='SQL' then 'SQL View'
when x.object_type='VIEW' then 'Database View'
when x.object_type='SYNONYM' then 'Synonym'
when x.object_type='TABLE' then 'Table'
else 'Missing'
end source_type,
x.view_owner,
x.view_status,
x.view_sql
from
(
select
err.report_id,
err.paste_view_sql,
erv.object_type view_type,
erv.view_sql,
(select max(ao.owner) keep (dense_rank first order by decode(ao.owner,erv.view_owner,1,'APPS',2,3)) from all_objects ao where erv.view_name=ao.object_name and ao.owner in ('APPS','XXEIS') and ao.object_type in ('VIEW','SYNONYM','TABLE')) view_owner,
(select max(ao.object_type) keep (dense_rank first order by decode(ao.owner,erv.view_owner,1,'APPS',2,3)) from all_objects ao where erv.view_name=ao.object_name and ao.owner in ('APPS','XXEIS') and ao.object_type in ('VIEW','SYNONYM','TABLE')) object_type,
(select max(ao.status) keep (dense_rank first order by decode(ao.owner,erv.view_owner,1,'APPS',2,3)) from all_objects ao where erv.view_name=ao.object_name and ao.owner in ('APPS','XXEIS') and ao.object_type in ('VIEW','SYNONYM','TABLE')) view_status
from
xxeis.eis_rs_reports err,
xxeis.eis_rs_views erv
where
err.view_id=erv.view_id(+)
) x
) y,
(
select
x.report_id,
count(x.run_flag) runs,
count(x.scheduled_flag) scheduled_runs,
count(case when x.submission_source='XL Connect' then 1 end) xl_connect_runs,
count(distinct case when x.run_flag='Y' then x.created_by end) users,
max(case when x.run_flag='Y' then x.start_time end) last_run,
max(case when x.run_flag='Y' then x.created_by end) keep (dense_rank last order by case when x.run_flag='Y' then x.start_time end nulls first) last_user_id,
count(case when x.run_flag='Y' and x.status in ('E','T') then 1 end) errors,
round(avg(case when x.run_flag='Y' then x.rows_retrieved end)) average_rows,
round(avg(case when x.run_flag='Y' then x.seconds end)) average_seconds,
count(case when x.run_flag is null then 1 end) deliveries,
count(x.email_flag) email_deliveries,
count(x.ftp_flag) ftp_deliveries
from
(
select
case when erp.submission_source like 'Email Distribution~%' then to_number(substr(erp.submission_source,20)) else erp.report_id end report_id,
case when erp.submission_source in ('eXpress','XL Connect') then 'Y' end run_flag,
case when erp.submission_source in ('eXpress','XL Connect') and erp.start_time-ers.logon_time>1 then 'Y' end scheduled_flag,
erp.submission_source,
erp.status,
erp.start_time,
erp.created_by,
erp.rows_retrieved,
(erp.end_time-erp.start_time)*86400 seconds,
erprd.email_flag,
erprd.ftp_flag
from
xxeis.eis_rs_processes erp,
xxeis.eis_rs_sessions ers,
(
select
erprd.process_id,
max(case when erprd.select_flag='Y' and (erprd.email_address is not null or erprd.user_group_id is not null) then 'Y' end) email_flag,
max(case when erprd.ftp_site_id is not null then 'Y' end) ftp_flag
from
xxeis.eis_rs_process_rpt_distribute erprd
group by
erprd.process_id
) erprd
where
2=2 and
(erp.submission_source in ('eXpress','XL Connect') or erp.submission_source like 'Email Distribution~%') and
erp.session_id=ers.session_id(+) and
erp.process_id=erprd.process_id(+)
) x
group by
x.report_id
) x,
(
select
fcr.argument1,
count(*) active_schedules
from
fnd_concurrent_requests fcr
where
(fcr.phase_code='P' or fcr.phase_code='R' and fcr.release_class_id is not null) and
(fcr.program_application_id,fcr.concurrent_program_id) in
(
select
fcp.application_id,
fcp.concurrent_program_id
from
fnd_application fa,
fnd_executables fe,
fnd_concurrent_programs fcp
where
fa.application_short_name='XXEIS' and
fa.application_id=fe.application_id and
upper(fe.execution_file_name)='XXEIS.EIS_RSC_PROCESS_REPORTS.SUBMIT_CONC_REPORT' and
fe.application_id=fcp.executable_application_id and
fe.executable_id=fcp.executable_id
)
group by
fcr.argument1
) fcr,
(
select
coalesce(errs.report_id,errsu.report_id) report_id,
count(distinct errs.responsibility_id) responsibility_grants,
count(distinct errs.request_group_id) request_group_grants,
count(distinct errs.user_id) user_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
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_tl xrt_id,
xxen_reports_tl xrt_name
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
to_char(err.report_id)=fcr.argument1(+) and
err.report_id=errs.report_id(+) and
err.report_id=xrt_eis.report_id(+) and
xrt_eis.blitz_report_id=xrt_id.report_id(+) and
xrt_id.language(+)=userenv('lang') and
err.report_name=xrt_name.report_name(+) and
xrt_name.language(+)=userenv('lang')
order by
x.runs desc nulls last,
era.application_name,
errc.category_name,
err.report_name |
