select /*+ no_push_pred(d) */
era.application_name application,
erv.view_name,
erv.user_view_name,
erv.description,
z.source_type,
z.view_owner,
z.view_status,
xxen_util.yes(erv.seeded_flag) seeded,
z.text_length,
xxen_util.yes(z.with_flag) with_clause,
xxen_util.yes(z.union_flag) "Union",
xxen_util.yes(z.group_by_flag) "Group By",
xxen_util.yes(z.distinct_flag) "Distinct",
xxen_util.yes(z.connect_by_flag) "Connect By",
xxen_util.yes(z.dff_flag) "DFF Columns",
xxen_util.yes(z.kff_flag) "KFF Columns",
xxen_util.yes(z.gl_flag) "GL Account Columns",
d.xxeis_objects "XXEIS Objects",
r.reports,
r.paste_sql_reports "Paste SQL Reports",
p.active_reports,
p.runs,
p.users,
xxen_util.client_time(p.last_run) last_run,
&view_sql
xxen_util.user_name(erv.created_by) created_by,
xxen_util.client_time(erv.creation_date) creation_date,
xxen_util.user_name(erv.last_updated_by) last_updated_by,
xxen_util.client_time(erv.last_update_date) last_update_date
from
xxeis.eis_rs_views erv,
xxeis.eis_rs_applications era,
(
select
err.view_id,
count(case when err.paste_view_sql is null then 1 end) reports,
count(case when err.paste_view_sql is not null then 1 end) paste_sql_reports
from
xxeis.eis_rs_reports err
group by
err.view_id
) r,
(
select
err.view_id,
count(distinct erp.report_id) active_reports,
count(*) runs,
count(distinct erp.created_by) users,
max(erp.start_time) last_run
from
xxeis.eis_rs_processes erp,
xxeis.eis_rs_reports err
where
2=2 and
erp.submission_source in ('eXpress','XL Connect') and
erp.report_id=err.report_id and
err.paste_view_sql is null
group by
err.view_id
) p,
(
select /*+ no_merge */
z.view_id,
z.view_name,
z.source_type,
z.view_owner,
z.view_status,
z.text,
z.text_length,
z.with_flag,
case when instr(z.top_level,'U')>0 then 'Y' end union_flag,
case when instr(z.top_level,'G')>0 then 'Y' end group_by_flag,
z.distinct_flag,
case when instr(z.skeleton,'C')>0 then 'Y' end connect_by_flag,
z.dff_flag,
z.kff_flag,
z.gl_flag
from
(
select
z.*,
--each pass removes the bracket groups nested up to three levels deep, leaving the top level; a SQL enclosed in brackets as a whole is evaluated inside them
regexp_replace(
regexp_replace(
regexp_replace(
regexp_replace(case when z.paren_flag='Y' then regexp_replace(z.skeleton,'^\((.*)\)$','\1') else z.skeleton end,'\([^()]*(\([^()]*(\([^()]*\)[^()]*)*\)[^()]*)*\)'),
'\([^()]*(\([^()]*(\([^()]*\)[^()]*)*\)[^()]*)*\)'),
'\([^()]*(\([^()]*(\([^()]*\)[^()]*)*\)[^()]*)*\)'),
'\([^()]*(\([^()]*(\([^()]*\)[^()]*)*\)[^()]*)*\)') top_level
from
(
select /*+ no_merge */
y.view_id,
y.view_name,
y.source_type,
y.view_owner,
y.view_status,
y.text,
y.text_length,
y.dff_flag,
y.kff_flag,
y.gl_flag,
case when regexp_like(dbms_lob.substr(y.code,1000,1),'^[[:space:](]*with[^a-z0-9_$#]') then 'Y' end with_flag,
case when regexp_like(dbms_lob.substr(y.code,1000,1),'^[[:space:](]*select[[:space:]]+(distinct|unique)[^a-z0-9_$#]') then 'Y' end distinct_flag,
case when regexp_like(dbms_lob.substr(y.code,1000,1),'^[[:space:]]*\(') then 'Y' end paren_flag,
--brackets and markers for set operators (U), group by (G) and connect by (C) only
regexp_replace(
replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(y.code,'(',' ( '),')',' ) '),' ',' '),' ',' '),' ',' '),
' union ',' U '),' minus ',' U '),' intersect ',' U '),' group by ',' G '),' connect by ',' C '),
'[^()UGC]+') skeleton
from
(
select /*+ no_merge */
x.view_id,
x.view_name,
x.source_type,
x.view_owner,
x.view_status,
x.text,
dbms_lob.getlength(x.text) text_length,
--the first start marker of each kind is followed by a generated column, not by its end marker
case when regexp_like(dbms_lob.substr(x.lower_text,200,nullif(instr(x.lower_text,'descr#flexfield#start'),0)+21),'^(\*/|[[:space:]]|(--|/\*)descr#flexfield#start)*[^[:space:]/-]') then 'Y' end dff_flag,
case when regexp_like(dbms_lob.substr(x.lower_text,200,nullif(instr(x.lower_text,'kff#start'),0)+9),'^(\*/|[[:space:]]|(--|/\*)kff#start)*[^[:space:]/-]') then 'Y' end kff_flag,
case when regexp_like(dbms_lob.substr(x.lower_text,200,nullif(instr(x.lower_text,'gl#accountff#start'),0)+18),'^(\*/|[[:space:]]|(--|/\*)gl#accountff#start)*[^[:space:]/-]') then 'Y' end gl_flag,
' '||replace(replace(replace(regexp_replace(x.lower_text,'''[^'']*''|/\*.*?\*/|--[^'||chr(10)||']*',' ',1,0,'n'),chr(9),' '),chr(10),' '),chr(13),' ')||' ' code
from
(
select /*+ no_merge */
x.*,
lower(x.text) lower_text
from
(
select
erv.view_id,
erv.view_name,
case
when erv.object_type='SQL' then 'SQL View'
when o.object_type='VIEW' then 'Database View'
when o.object_type='SYNONYM' then 'Synonym'
when o.object_type='TABLE' then 'Table'
else 'Missing'
end source_type,
o.owner view_owner,
o.status view_status,
case when v.obj# is not null then xxen_util.long_to_clob('SYS.VIEW$','TEXT',v.rowid) else erv.view_sql end text
from
xxeis.eis_rs_views erv,
(
select
erv.view_id,
max(ceo.owner) keep (dense_rank first order by decode(ceo.owner,erv.view_owner,1,'APPS',2,3)) owner,
max(ceo.object_type) keep (dense_rank first order by decode(ceo.owner,erv.view_owner,1,'APPS',2,3)) object_type,
max(ceo.status) keep (dense_rank first order by decode(ceo.owner,erv.view_owner,1,'APPS',2,3)) status,
max(ceo.obj#) keep (dense_rank first order by decode(ceo.owner,erv.view_owner,1,'APPS',2,3)) obj#
from
xxeis.eis_rs_views erv,
(
select
u.name owner,
ceo.name,
decode(ceo.type#,2,'TABLE',4,'VIEW',5,'SYNONYM') object_type,
decode(ceo.status,1,'VALID','INVALID') status,
ceo.obj#
from
sys.user$ u,
sys."_CURRENT_EDITION_OBJ" ceo
where
u.name in ('APPS','XXEIS') and
u.user#=ceo.owner# and
ceo.type# in (2,4,5) and
ceo.name in (select erv.view_name from xxeis.eis_rs_views erv)
) ceo
where
nvl(erv.object_type,'VIEW')<>'SQL' and
erv.view_name=ceo.name
group by
erv.view_id
) o,
sys.view$ v
where
erv.view_id in (select err.view_id from xxeis.eis_rs_reports err) and
erv.view_id=o.view_id(+) and
o.obj#=v.obj#(+)
) x
) x
) y
) z
) z
) z,
(
select
x.owner,
x.name,
listagg(x.object_name,', ') within group (order by x.object_name) xxeis_objects
from
(
select distinct
dd.owner,
dd.name,
nvl(ds.table_name,dd.referenced_name) object_name
from
dba_dependencies dd,
dba_synonyms ds
where
dd.owner in ('APPS','XXEIS') and
dd.type='VIEW' and
dd.name in (select erv.view_name from xxeis.eis_rs_views erv where erv.view_id in (select err.view_id from xxeis.eis_rs_reports err)) and
dd.referenced_owner='XXEIS' and
dd.referenced_type<>'NON-EXISTENT' and
dd.referenced_owner=ds.owner(+) and
dd.referenced_name=ds.synonym_name(+) and
(dd.referenced_type<>'SYNONYM' or ds.table_owner='XXEIS')
) x
group by
x.owner,
x.name
) d
where
1=1 and
erv.application_id=era.application_id(+) and
erv.view_id=r.view_id and
erv.view_id=p.view_id(+) and
erv.view_id=z.view_id and
z.view_owner=d.owner(+) and
z.view_name=d.name(+)
order by
p.runs desc nulls last,
era.application_name,
erv.view_name |
