ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 5C6941B9A60979A1E0630100007F92F4Y 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 New report: database objects referenced by the EIS report views with synonyms resolved, object category, flexfield columns only flag and report usage per view. US EIS View Dependencies Database objects referenced by the EIS report views, one row per view and referenced object (direct dependencies from dba_dependencies), with synonyms resolved to the object they point to. It shows which XXEIS objects the EIS reports still need before the XXEIS schema can be dropped. Only views that a report runs from are listed. Reports running from pasted SQL or from a SQL-type view have no database view and are not covered, although their SQL can reference XXEIS objects as well. Reports counts the reports running from the view, Active Reports those executed within Executed within Days and Runs their executions from eXpress and XL Connect (email distributions are not counted). EIS purges its run history, so a view without runs may still have been used before the earliest retained run. Flexfield Columns Only marks objects that the view references only in the flexfield columns EIS generated into it between its descr#flexfield, kff and gl#accountff markers, typically EIS_RS_DFF and EIS_RS_FIN_UTILITY. The import into Blitz Report drops these columns and shows descriptive flexfields through its own DFF columns, so the migrated reports do not need these objects. The import also replaces the EIS org access, GL security and lookup functions with standard code; EIS Reports with Show Migration Analysis lists the XXEIS objects a migrated report still uses. APPS objects count as APPS Custom only when named XX or EIS, so customer objects in APPS with other prefixes show as Standard. The Objects template totals the counts per referenced object. EIS 1=1 2=2 1 10 1=1 x.application=: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 x.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. 3 30 1=1 x.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 nvl(erv.object_type,'VIEW')<>'SQL' and erv.view_id in (select err.view_id from xxeis.eis_rs_reports err where err.paste_view_sql is null) order by erv.view_name US View Name 4 40 1=1 x.object_category=:object_category LOV custom Y select 'EIS Code' value, 'EIS package, function or type in schema XXEIS' description from dual union all select 'Custom Code in XXEIS', 'Code in schema XXEIS named XX, but not XXEIS' from dual union all select 'XXEIS Table', 'Table in schema XXEIS' from dual union all select 'XXEIS View', 'View in schema XXEIS' from dual union all select 'APPS Custom', 'APPS object named XX or EIS' from dual union all select 'Custom Schema', 'Object outside APPS, XXEIS and the Oracle EBS product schemas' from dual union all select 'Standard', 'Object in an Oracle EBS product schema or SYS, or any other APPS object' from dual US Object Category 5 50 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. 6 60 1=1 x.active_reports>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. 7 1=1 x.active_reports=0 N US Executed