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 |
