EIS Reports

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

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