ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 729B2857091D7748E053AD00140A40CDY 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 Rebuilt for the EIS migration: one row per EIS view with reports, with the EIS runtime source type and owner, top level SQL structure and flexfield flags, referenced XXEIS objects, report counts and usage, and the optional view SQL. US EIS Views One row per EIS view that has reports, with the structure of its SQL, the XXEIS objects it references and the usage of its reports. Source Type follows the EIS runtime: a view of type SQL runs from the SQL stored with it, any other from the database object of that name, looked up in the registered owner, then APPS, then XXEIS. View Owner and View Status describe that object. For a Missing view, Text Length and the SQL flags come from the SQL stored with the EIS view, where one exists. Union (also minus and intersect), Group By and With Clause are evaluated at the top level of the SQL, outside inline views and subqueries, Distinct on its first select and Connect By anywhere; comments and literals are ignored. DFF Columns, KFF Columns and GL Account Columns show that EIS generated flexfield columns into the view between its descr#flexfield, kff and gl#accountff markers. XXEIS Objects lists the objects of the XXEIS schema that the database view references, with synonyms resolved, so it is blank for SQL views. Reports, Active Reports, Runs and Users count the reports that run from the view. Reports with pasted SQL run from their own SQL and are counted only in Paste SQL Reports. Runs are eXpress and XL Connect executions that started within Executed within Days. EIS purges its run history, so a view without runs may still have been used before the earliest retained run. EIS &view_sql 1=1 2=2 1 10 1=1 era.application_name=:application LOV custom Y select era.application_name value, era.description from xxeis.eis_rs_applications era order by era.application_name US Application EIS application the view is registered in. 2 20 1=1 erv.view_id in (select err.view_id from xxeis.eis_rs_reports err, xxeis.eis_rs_report_categories errc where err.category_id=errc.category_id and errc.category_name=:category) LOV custom Y select distinct errc.category_name value, null description from xxeis.eis_rs_report_categories errc, xxeis.eis_rs_applications era where errc.application_id=era.application_id and (:$flex$.application is null or xxen_util.contains(:$flex$.application,era.application_name)='Y') order by errc.category_name US Category Lists the views of the reports in the category. 3 30 1=1 erv.view_id in (select err.view_id from xxeis.eis_rs_reports err where err.report_name=:report_name) LOV custom Y select err.report_name value, err.description from xxeis.eis_rs_reports err order by err.report_name US Report Name Lists the view the report is defined on. 4 40 1=1 erv.view_name=:view_name LOV custom Y select distinct erv.view_name value, erv.user_view_name description from xxeis.eis_rs_views erv where erv.view_id in (select err.view_id from xxeis.eis_rs_reports err) order by erv.view_name US View Name 5 50 1=1 z.source_type=:source_type LOV custom Y select 'SQL View' value, 'SQL stored with an EIS view of type SQL' description from dual union all select 'Database View', 'Database view referenced by name' from dual union all select 'Synonym', 'Synonym referenced by name' from dual union all select 'Table', 'Table referenced by name' from dual union all select 'Missing', 'No database object found' from dual US Source Type 6 60 1=1 erv.seeded_flag='Y' LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y US Seeded 7 1=1 erv.seeded_flag is null N US Seeded 8 70 2=2 erp.start_time>=sysdate-:executed_within_days Number 365 US Executed within Days Usage window in days before today, by run start. Blank counts the whole retained history. 9 80 1=1 p.runs>0 LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y US Executed Yes lists the views with a report run within the usage window, No the others. 10 1=1 p.runs is null N US Executed 11 90 &view_sql z.text "View SQL", LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show SQL Adds the SQL of the view: the database view text, or the SQL stored with an EIS view of type SQL.