EIS Reports

Description
List of EIS reports. This can be used, for example, when migrating from EIS to Blitz Report, to show EIS reports with the database views they are based on, and which ones were used by the business.
select
era.application_name,
errc.category_name,
err.report_name,
err.view_name,
&execution_count
err.description,
dv.text,
xxen_util.user_name(err.report_owner) report_owner,
decode(err.seeded_flag,'Y', 'Yes','No') seeded_flag,
decode(err.enable_report,'Y', 'Yes','No') enable_report,
erv.user_view_name,
erv.description view_description,
xxen_util.user_name(err.created_by) created_by,
err.creation_date,
xxen_util.user_name(err.last_updated_by) last_updated_by,
err.last_update_date,
err.first_flag,
err.last_flag,
erv.view_owner
from
(
select
case when min(err.creation_date) over (partition by err.view_id)=err.creation_date and min(err.report_id) over (partition by err.view_id,err.creation_date)=err.report_id then 'Y' end first_flag,
case when max(err.creation_date) over (partition by err.view_id)=err.creation_date and max(err.report_id) over (partition by err.view_id,err.creation_date)=err.report_id then 'Y' end last_flag,
err.*
from
xxeis.eis_rs_reports err
) err,
xxeis.eis_rs_report_categories errc,
xxeis.eis_rs_views erv,
xxeis.eis_rs_applications era,
(
select
u.name owner,
o.name view_name,
xxen_util.long_to_clob('SYS.VIEW$', 'TEXT', v.rowid) text
from
sys."_CURRENT_EDITION_OBJ" o,
sys.view$ v,
sys.user$ u
where
o.obj#=v.obj# and
o.owner#=u.user#
) dv
where
1=1 and
err.category_id=errc.category_id(+) and
err.application_id=era.application_id and
err.view_id=erv.view_id and
erv.view_owner=dv.owner(+) and
erv.view_name=dv.view_name(+)
order by
&execution_order_by
era.application_name,
errc.category_name,
err.report_name
Parameter Name SQL text Validation
Show oldest per View only
err.first_flag='Y'
LOV
Show latest per View only
err.last_flag='Y'
LOV
View SQL Contains
lower(dv.text) like '%'||lower(:search_text)||'%'
Execution Count within Days
(select count(*) from xxeis.eis_rs_processes erp where err.report_id=erp.report_id and erp.creation_date>=sysdate-:submitted_within) executions,
Number
Seeded Flag
nvl(err.seeded_flag,'N')=:seeded_flag
LOV Oracle
Execution Count within Days
executions desc,