ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN F449645C95B4203DE0530100007FFFFFY select y.owner, y.workbook, y.description, y.workbook_exists, y.eex_uploaded, y.selected_for_conversion, y.converted, y.sheet, y.object_use_key, xrv.report_name, xrtv.template_name, xrtv.description template_description, eqs.access_count, eqs.last_accessed, eqs.last_accessed_by, y.last_updated_by, y.last_update_date, y.doc_id, xdw.count flattened_count from ( select x.*, xds.sheet_name sheet, xds.object_use_key from ( select nvl(xxen_util.dis_user_name(ed.doc_eu_id,:eul),xxen_util.dis_user_name(xmlcast(xmlquery('/EndUserLayerExport/Document/ElementRef[@Type="EulUser"]/UniqueIdent/@Username' passing xdwx.xml returning content) as varchar2(64)),null,:eul)) owner, nvl(ed.doc_name,xmlcast(xmlquery('/EndUserLayerExport/Document/@Name' passing xdwx.xml returning content) as varchar2(100))) workbook, nvl(ed.doc_description,xmlcast(xmlquery('/EndUserLayerExport/Document/@Description' passing xdwx.xml returning content) as varchar2(4000))) description, xxen_util.meaning(nvl2(ed.doc_id,'Y',null),'YES_NO',0) workbook_exists, xxen_util.meaning(nvl2(xdwx.doc_id,'Y',null),'YES_NO',0) eex_uploaded, xxen_util.yes(decode(xdwx.convert_flag,'Y','Y')) selected_for_conversion, xxen_util.yes(decode(xdwx.convert_flag,'C','Y')) converted, xxen_util.dis_user_name(ed.doc_updated_by) last_updated_by, ed.doc_updated_date last_update_date, nvl(ed.doc_id,xdwx.doc_id) doc_id, ed.doc_eu_id from &eul.eul5_documents ed full join (select xdwx.* from xxen_discoverer_workbook_xmls xdwx where upper(xdwx.eul)=upper(:eul)) xdwx on ed.doc_id=xdwx.doc_id ) x, ( select /*+ no_merge */ xds.doc_id, xds.sheet_name, xxen_xdo.sheet_object_use_key(:eul,xds.doc_id,xds.sheet_name) object_use_key from (select /*+ no_merge */ distinct xds.doc_id, xds.sheet_name from xxen_discoverer_sheets xds where upper(xds.eul)=upper(:eul)) xds ) xds where x.doc_id=xds.doc_id(+) ) y, ( select upper(regexp_replace(eqs.qs_doc_name,'\.dis$',null,1,1,'i')) doc_name_, count(*) access_count, max(eqs.qs_created_date) last_accessed, xxen_util.dis_user_name(max(eqs.qs_created_by) keep (dense_rank first order by eqs.qs_created_date desc)) last_accessed_by from &eul.eul5_qpp_stats eqs where 2=2 group by upper(regexp_replace(eqs.qs_doc_name,'\.dis$',null,1,1,'i')) ) eqs, (select xdw.doc_id, count(*) count from xxen_discoverer_workbooks xdw where upper(xdw.eul)=upper(:eul) group by xdw.doc_id) xdw, ( select regexp_substr(xrv.description,chr(10)||'Object IDs: (.+)'||chr(10),1,1,null,1) object_use_key, xrv.report_name from xxen_reports_v xrv where xrv.description like '%'||chr(10)||'Object IDs: %'||chr(10)||'EUL: '||:eul ) xrv, (select regexp_substr(xrtv.description,chr(10)||'Sheet: (.+)',1,1,null,1) sheet, regexp_substr(xrtv.description,chr(10)||'Doc Id: (\d+)',1,1,null,1) doc_id, xrtv.* from xxen_report_templates_v xrtv where regexp_substr(xrtv.report_description,chr(10)||'EUL: (\w+)',1,1,null,1)=:eul) xrtv where 1=1 and upper(regexp_replace(y.workbook,'\.dis$',null,1,1,'i'))=eqs.doc_name_(+) and y.doc_id=xdw.doc_id(+) and y.object_use_key=xrv.object_use_key(+) and y.doc_id=xrtv.doc_id(+) and y.sheet=xrtv.sheet(+) order by y.owner, y.workbook, y.sheet no_merge fences on the folder set derivation so it evaluates once per distinct worksheet - view merging re-multiplied it per sheet item row, which made the report spend minutes on instances with many staged workbooks US DIS Workbook Import Validation Discoverer workbook migration validation report showing every workbook and worksheet next to its conversion state: whether the workbook xml is uploaded, selected for conversion or converted, the object use key derived from the workbook xml, and the migrated Blitz report and template. Workbooks known only from the end user layer and workbooks whose xml was uploaded after deletion from Discoverer are both listed, so gaps in either direction are visible. The Not yet imported parameter restricts to worksheets without a migrated template. The Accessed after parameter restricts the output to workbooks with recorded usage since that date, the same scope the DIS Workbook Export Script lists for export; left blank, every workbook is shown including those never accessed. The usage statistics columns show access count, last accessed date and user within the same window. Enginatics &eul 1=1 2=2 :eul 1 10 1=1 y.workbook=:workbook LOV DIS Workbook 8E2FF36EDF2079D2E0530100007F1FF2 Y select ed.doc_name value, ed.doc_description||' ('||ed.doc_developer_key||')' description from eul_us.eul5_documents ed order by ed.doc_name F Cahier d'exercices US Workbook 2 20 2=2 eqs.qs_created_date>=:accessed_after Date US Accessed after 3 1=1 eqs.doc_name_ is not null US Accessed after 4 25 1=1 xxen_util.dis_user_name(y.doc_eu_id,:eul,'N')=:doc_owner LOV FND User Name 8E2FF36EDE8479D2E0530100007F1FF2 Y Y select fu.user_name value, trim(coalesce( trim(papf.first_name||' '||papf.last_name), fu.description, fu.email_address, papf.email_address )||fu.inactive) description from (select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf, ( select distinct furg.user_id, count(*) over (partition by furg.user_id) resp_count, max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key from fnd_responsibility fr, fnd_user_resp_groups_direct furg where fr.responsibility_id=furg.responsibility_id and fr.application_id=furg.responsibility_application_id ) furg where fu.employee_id=papf.person_id(+) and fu.user_id=furg.user_id(+) and not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE') order by fu.inactive desc, fu.user_name US Workbook Owner 5 30 1=1 xrtv.template_name is null LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Not yet imported 6 50 &eul <parameter_value> LOV DIS End User Layer 8E2FF36EDF3879D2E0530100007F1FF2 Y select lower(do.owner) value, do.created description from dba_objects do where do.object_type='TABLE' and do.object_name='EUL5_VERSIONS' order by do.created desc xxen_util.dis_default_eul Y F Couche de l'utilisateur final US End User Layer 7 :eul F Couche de l'utilisateur final US End User Layer