EIS View Dependencies

select
x.application,
x.view_owner,
x.view_name,
x.reports,
x.active_reports,
x.runs,
x.via_synonym,
x.object_owner,
x.object_name,
x.object_type,
x.object_category,
xxen_util.yes(x.flexfield_only) flexfield_columns_only
from
(
select
y.*,
case
when y.object_owner='XXEIS' then
  case
  when y.object_type='TABLE' then 'XXEIS Table'
  when y.object_type='VIEW' then 'XXEIS View'
  when y.object_name like 'XX%' and y.object_name not like 'XXEIS%' then 'Custom Code in XXEIS'
  else 'EIS Code'
  end
when y.object_owner='APPS' then case when y.object_name like 'XX%' or y.object_name like 'EIS%' then 'APPS Custom' else 'Standard' end
when y.object_owner in ('SYS','SYSTEM','PUBLIC') or y.object_owner in (select fou.oracle_username from fnd_oracle_userid fou, fnd_product_installations fpi where fou.oracle_id=fpi.oracle_id and fpi.application_id<20000) then 'Standard'
else 'Custom Schema'
end object_category
from
(
select
era.application_name application,
z.view_id,
z.view_owner,
z.view_name,
z.reports,
z.active_reports,
z.runs,
min(z.via_synonym) via_synonym,
z.object_owner,
z.object_name,
max(nvl(do.object_type,z.referenced_type)) object_type,
case when count(z.flexfield_only)=count(*) then 'Y' end flexfield_only
from
(
select
w.view_id,
w.application_id,
w.view_owner,
w.view_name,
w.reports,
w.active_reports,
w.runs,
decode(dd.referenced_type,'SYNONYM',dd.referenced_owner||'.'||dd.referenced_name) via_synonym,
dd.referenced_type,
coalesce(ds3.table_owner,ds2.table_owner,ds1.table_owner,dd.referenced_owner) object_owner,
coalesce(ds3.table_name,ds2.table_name,ds1.table_name,dd.referenced_name) object_name,
case when dbms_lob.instr(w.code,lower(dd.referenced_name))>0 and regexp_instr(w.code_outside_flex,'[^a-z0-9_$#]'||replace(lower(dd.referenced_name),'$','\$')||'[^a-z0-9_$#]')=0 then 'Y' end flexfield_only
from
(
select /*+ no_merge */
w.*,
--removes the column blocks EIS generated between a start and an end flexfield marker of the same kind, then literals and comments
case when dbms_lob.instr(w.code,'#start')>0 then
regexp_replace(
regexp_replace(
regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(w.code,
'descr#flexfield#(groupby)?start',chr(1)),'descr#flexfield#(groupby)?end',chr(2)),
'kff#(groupby)?start',chr(3)),'kff#(groupby)?end',chr(4)),
'(gl|bal)#accountff#(groupby)?start',chr(5)),'(gl|bal)#accountff#(groupby)?end',chr(6)),
chr(1)||'[^'||chr(1)||'-'||chr(6)||']*'||chr(2)||'|'||chr(3)||'[^'||chr(1)||'-'||chr(6)||']*'||chr(4)||'|'||chr(5)||'[^'||chr(1)||'-'||chr(6)||']*'||chr(6),' '),
'''[^'']*''|--[^'||chr(10)||']*|/\*.*?\*/',' ',1,0,'n')
end code_outside_flex
from
(
select /*+ no_merge */
w.*,
case when v.obj# is not null then ' '||lower(xxen_util.long_to_clob('SYS.VIEW$','TEXT',v.rowid))||' ' end code
from
(
select
erv.view_id,
erv.application_id,
do.owner view_owner,
do.object_name view_name,
do.object_type view_type,
do.object_id,
v.reports,
v.active_reports,
v.runs,
row_number() over (partition by erv.view_id order by decode(do.owner,erv.view_owner,1,'APPS',2,3)) rnk
from
(
select
err.view_id,
count(*) reports,
count(erp.report_id) active_reports,
sum(erp.runs) runs
from
xxeis.eis_rs_reports err,
(
select
erp.report_id,
count(*) runs
from
xxeis.eis_rs_processes erp
where
2=2 and
erp.submission_source in ('eXpress','XL Connect')
group by
erp.report_id
) erp
where
err.paste_view_sql is null and
err.report_id=erp.report_id(+)
group by
err.view_id
) v,
xxeis.eis_rs_views erv,
dba_objects do
where
v.view_id=erv.view_id and
nvl(erv.object_type,'VIEW')<>'SQL' and
erv.view_name=do.object_name and
do.owner in (erv.view_owner,'APPS','XXEIS') and
do.object_type in ('VIEW','SYNONYM')
) w,
sys.view$ v
where
w.rnk=1 and
w.object_id=v.obj#(+)
) w
) w,
dba_dependencies dd,
dba_synonyms ds1,
dba_synonyms ds2,
dba_synonyms ds3
where
w.view_owner=dd.owner and
w.view_name=dd.name and
w.view_type=dd.type and
dd.referenced_type<>'NON-EXISTENT' and
dd.referenced_owner=ds1.owner(+) and
dd.referenced_name=ds1.synonym_name(+) and
ds1.table_owner=ds2.owner(+) and
ds1.table_name=ds2.synonym_name(+) and
ds2.table_owner=ds3.owner(+) and
ds2.table_name=ds3.synonym_name(+)
) z,
xxeis.eis_rs_applications era,
dba_objects do
where
z.application_id=era.application_id(+) and
z.object_owner=do.owner(+) and
z.object_name=do.object_name(+) and
do.namespace(+)=1 and
do.subobject_name(+) is null
group by
era.application_name,
z.view_id,
z.view_owner,
z.view_name,
z.reports,
z.active_reports,
z.runs,
z.object_owner,
z.object_name
) y
) x
where
1=1
order by
x.application,
x.view_name,
x.object_owner,
x.object_name