ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E811F942597A2075E0530100007FF4F2 Blitz Upload Mode Y select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual US 8E2FF36EDF3879D2E0530100007F1FF2 DIS End User Layer 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 AR طبقة المستخدم النهائي للمكتشف D Entdecker-Endbenutzerschicht E Capa de usuario final de Discoverer F Couche utilisateur final Discoverer I Livello utente finale Discoverer JA 発見者エンドユーザー層 KO Discoverer 최종 사용자 계층 PTB Camada do usuário final do Discoverer RU Уровень конечного пользователя S Discoverer-slutanvändarlager TR Discoverer son kullanıcı katmanı US Discoverer end user layer ZHS 发现者终端用户层 8E2FF36EDEA679D2E0530100007F1FF2 Yes Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US 4D461D94ABFB48DDA702F87DD22340F0Y select null action_, null status_, null message_, xxen_util.yes(x.convert_flag) convert_workbook, null modified_columns_, x.workbook, x.owner, x.workbook_description, x.sheet_count, xxen_util.client_time(eqs.last_run_date) last_run_date, eqs.run_count, eqs.distinct_users, xxen_util.yes(x.in_eul) exists_in_eul, xxen_util.yes(decode(x.convert_flag,'C','Y')) converted, conv.blitz_report, xxen_util.client_time(conv.blitz_report_created) blitz_report_created, xxen_util.client_time(conv.blitz_report_updated) blitz_report_updated, xxen_util.yes(conv.folder_set_converted) folder_set_converted, xxen_util.client_time(x.doc_updated_date) workbook_last_updated, x.doc_updated_by workbook_updated_by, xxen_util.client_time(x.doc_created_date) workbook_created, x.doc_created_by workbook_created_by, xxen_util.client_time(x.eex_upload_date) eex_upload_date, xxen_util.client_time(x.last_update_date) last_updated, x.doc_id workbook_id, x.eul, :submit_import submit_import, :report_name_prefix report_name_prefix, rownum upload_row from ( select xdwx.convert_flag, nvl(ed.doc_name,xmlcast(xmlquery('/EndUserLayerExport/Document/@Name' passing xdwx.xml returning content) as varchar2(100))) workbook, 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_description,xmlcast(xmlquery('/EndUserLayerExport/Document/@Description' passing xdwx.xml returning content) as varchar2(4000))) workbook_description, xmlcast(xmlquery('count(/EndUserLayerExport/Document/Workbook/Sheet)' passing xdwx.xml returning content) as number) sheet_count, nvl2(ed.doc_id,'Y',null) in_eul, ed.doc_updated_date, ed.doc_updated_by, ed.doc_created_date, ed.doc_created_by, xdwx.creation_date eex_upload_date, xdwx.last_update_date, xdwx.doc_id, xdwx.eul from xxen_discoverer_workbook_xmls xdwx, &eul.eul5_documents ed where upper(xdwx.eul)=upper(:eul) and xdwx.doc_id=ed.doc_id(+) ) x, ( select upper(regexp_replace(eqs.qs_doc_name,'\.dis$',null,1,1,'i')) doc_name_, max(eqs.qs_created_date) last_run_date, count(*) run_count, count(distinct eqs.qs_created_by) distinct_users from &eul.eul5_qpp_stats eqs group by upper(regexp_replace(eqs.qs_doc_name,'\.dis$',null,1,1,'i')) ) eqs, ( select y.doc_id, listagg(y.report_name,chr(10)) within group (order by y.report_name) blitz_report, min(y.creation_date) blitz_report_created, max(y.last_update_date) blitz_report_updated, max(y.converted) folder_set_converted from ( select distinct z.doc_id, xrv.report_name, xrv.creation_date, xrv.last_update_date, nvl2(xrv.report_name,'Y',null) converted from ( select xds.doc_id, xxen_xdo.sheet_object_use_key(:eul,xds.doc_id,xds.sheet_name) object_use_key from ( select distinct xds.doc_id, xds.sheet_name from xxen_discoverer_sheets xds, xxen_discoverer_workbook_xmls xdwx where upper(xds.eul)=upper(:eul) and xds.eul=xdwx.eul and xds.doc_id=xdwx.doc_id and not exists ( select null from xxen_discoverer_workbooks xdw where xdw.eul=xds.eul and xdw.doc_id=xds.doc_id and xdw.class='Condition' and dbms_lob.instr(xdw.expression_string,'[9,')>0 and dbms_lob.instr(xdw.expression_string,'[9,'||xmlcast(xmlquery('/EndUserLayerExport/Document/Workbook/Sheet[@Name=$n]/@DeveloperKey' passing xdwx.xml, xds.sheet_name as "n" returning content) as varchar2(240))||']')>0 ) ) xds ) z, ( select regexp_substr(xrv.description,chr(10)||'Object IDs: (.+)'||chr(10),1,1,null,1) object_use_key, xrv.report_name, xrv.creation_date, xrv.last_update_date from xxen_reports_v xrv where xrv.description like '%'||chr(10)||'Object IDs: %'||chr(10)||'EUL: '||:eul ) xrv where z.object_use_key is not null and z.object_use_key=xrv.object_use_key(+) ) y group by y.doc_id ) conv where 1=1 and upper(regexp_replace(x.workbook,'\.dis$',null,1,1,'i'))=eqs.doc_name_(+) and x.doc_id=conv.doc_id(+) New Submit Import parameter: the upload submits the Blitz Report Discoverer Import for the end user layer, waits for it and reports per workbook how many Blitz reports and templates exist after the conversion; the result also refreshes the Blitz Report columns from the post-import state U A xxen_dis_upload.update_workbook_selection xxen_dis_upload.submit_discoverer_import US DIS Workbook Conversion Selection Lists the Discoverer workbook xmls uploaded to Blitz Report (Setup>Tools>Import>XML Upload), enriched with information supporting the decision which workbooks to convert: latest run date, run count and distinct users from the end user layer's usage statistics, last update and creation date from the workbook definition, owner, description and sheet count. Set Convert Workbook to Yes for each workbook that should be converted to Blitz Report and upload the file. The selection is stored on the staged workbook xml, and the 'Blitz Report Discoverer Import' concurrent program converts the selected workbooks. With the Submit Import parameter set to Yes, the upload submits the 'Blitz Report Discoverer Import' itself after storing the selection, waits for it to complete, and reports per workbook how many Blitz reports and templates exist for its worksheets. A workbook whose worksheet folder sets could not all be converted is reported as an error row. The usage statistics are informational only: workbooks without any usage history are listed as well, with an empty run count and last run date. The migration process is described in the following link: https://www.enginatics.com/blog/discoverer-replacement-with-blitz-report/ Enginatics Upload &eul 1=1 :eul :report_name_prefix :submit_import 1 10 1=1 :p_upload_mode like '%' || xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 Y select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C,U Y US Upload Mode 2 20 &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 US End User Layer 3 :eul US End User Layer 4 30 1=1 x.workbook=:workbook LOV custom Y select xmlcast(xmlquery('/EndUserLayerExport/Document/@Name' passing xdwx.xml returning content) as varchar2(100)) value, null description from xxen_discoverer_workbook_xmls xdwx where upper(xdwx.eul)=upper(:$flex$.End_User_Layer) order by 1 US Workbook 5 40 1=1 x.doc_id=:workbook_id Number US Workbook Id 6 50 :submit_import LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Submit Import 7 60 :report_name_prefix Char fnd_profile.value('XXEN_REPORT_DISCOVERER_IMPORT_REPORT_NAME_PREFIX') US Report Name Prefix 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 CONVERT_WORKBOOK LOV varchar2 10 Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Set to Yes for each workbook you want to convert to Blitz Report. Clear the cell to remove a previous selection. 5 MODIFIED_COLUMNS_ Char varchar2 Y 6 WORKBOOK Char varchar2 100 Y 7 OWNER Char varchar2 342 Y 8 WORKBOOK_DESCRIPTION Char varchar2 4000 Y 9 SHEET_COUNT Number number Y 10 LAST_RUN_DATE Date date Latest run date from the end user layer usage statistics, matched by workbook name. Empty for workbooks without any recorded usage. Y 11 RUN_COUNT Number number Y 12 DISTINCT_USERS Number number Y 13 EXISTS_IN_EUL Char varchar2 10 Yes when the workbook definition still exists in the end user layer. Empty when the workbook was deleted from Discoverer after its xml was exported and uploaded. Y 14 CONVERTED Char varchar2 10 Yes when the Blitz Report Discoverer Import converted every worksheet folder set of the workbook. Enter Yes in Convert Workbook again to re-select a converted workbook. Y 15 BLITZ_REPORT Char varchar2 4000 The migrated Blitz Report(s) carrying this workbook's worksheet folder sets. Populated once the workbook has been processed by the import. Y 16 BLITZ_REPORT_CREATED Date date Y 17 BLITZ_REPORT_UPDATED Date date Y 18 FOLDER_SET_CONVERTED Char varchar2 10 Yes when a Blitz Report already exists for one of this workbook's worksheet folder sets, for example converted from another workbook sharing the same folders. Such a folder set is only rebuilt through the import's Object Use Key parameter. Populated once the workbook has been processed by the import. Y 19 WORKBOOK_LAST_UPDATED Date date Y 20 WORKBOOK_UPDATED_BY Char varchar2 64 Y 21 WORKBOOK_CREATED Date date Y 22 WORKBOOK_CREATED_BY Char varchar2 64 Y 23 EEX_UPLOAD_DATE Date date Date the workbook xml was uploaded to Blitz Report. Y 24 LAST_UPDATED Date date Date the staged workbook was last touched: selected or deselected through this upload, or stamped converted by the import. Y 25 WORKBOOK_ID Number number Y 26 EUL Char varchar2 30 :$flex$.End_User_Layer Y Y 27 SUBMIT_IMPORT Char varchar2 10 :$flex$.Submit_Import Y Y 28 REPORT_NAME_PREFIX Char varchar2 10 :$flex$.Report_Name_Prefix Y Y 29 UPLOAD_ROW Number number Y U 1 status_ out Char STATUS_CODE_ U 2 message_ out Char MESSAGE_ U 3 p_convert in Char CONVERT_WORKBOOK U 4 p_workbook in Char WORKBOOK U 5 p_eul in Char EUL U 6 p_doc_id in Number WORKBOOK_ID U 10 p_doc_id_out out Number WORKBOOK_ID P 1 p_eul in Char EUL P 2 p_submit_import in Char SUBMIT_IMPORT P 3 p_report_name_prefix in Char REPORT_NAME_PREFIX P 4 p_doc_id in Number WORKBOOK_ID P 5 status_ out Char STATUS_CODE_ P 6 message_ out Char MESSAGE_ Error SQL Order By upload_row Success SQL select xu.action_, xu.status_, xu.message_, xxen_util.yes(xdwx.convert_flag) convert_workbook, xu.modified_columns_, xu.workbook, xu.owner, xu.workbook_description, xu.sheet_count, xu.last_run_date, xu.run_count, xu.distinct_users, xu.exists_in_eul, xxen_util.yes(decode(xdwx.convert_flag,'C','Y')) converted, conv.blitz_report, xxen_util.client_time(conv.blitz_report_created) blitz_report_created, xxen_util.client_time(conv.blitz_report_updated) blitz_report_updated, xxen_util.yes(conv.folder_set_converted) folder_set_converted, xu.workbook_last_updated, xu.workbook_updated_by, xu.workbook_created, xu.workbook_created_by, xu.eex_upload_date, xxen_util.client_time(xdwx.last_update_date) last_updated, xu.workbook_id, xu.eul, xu.submit_import, xu.report_name_prefix, xu.upload_row from xxen_dis_workbook_conve_9608_u xu, xxen_discoverer_workbook_xmls xdwx, ( select y.eul, y.doc_id, listagg(y.report_name,chr(10)) within group (order by y.report_name) blitz_report, min(y.creation_date) blitz_report_created, max(y.last_update_date) blitz_report_updated, max(y.converted) folder_set_converted from ( select distinct z.eul, z.doc_id, xrv.report_name, xrv.creation_date, xrv.last_update_date, nvl2(xrv.report_name,'Y',null) converted from ( select xds.eul, xds.doc_id, xxen_xdo.sheet_object_use_key(xds.eul,xds.doc_id,xds.sheet_name) object_use_key from ( select distinct xds.eul, xds.doc_id, xds.sheet_name from xxen_discoverer_sheets xds, xxen_discoverer_workbook_xmls xdwx where xds.eul=xdwx.eul and xds.doc_id=xdwx.doc_id and not exists ( select null from xxen_discoverer_workbooks xdw where xdw.eul=xds.eul and xdw.doc_id=xds.doc_id and xdw.class='Condition' and dbms_lob.instr(xdw.expression_string,'[9,')>0 and dbms_lob.instr(xdw.expression_string,'[9,'||xmlcast(xmlquery('/EndUserLayerExport/Document/Workbook/Sheet[@Name=$n]/@DeveloperKey' passing xdwx.xml, xds.sheet_name as "n" returning content) as varchar2(240))||']')>0 ) ) xds ) z, ( select regexp_substr(xrv.description,chr(10)||'Object IDs: (.+)'||chr(10),1,1,null,1) object_use_key, regexp_substr(xrv.description,chr(10)||'EUL: (\w+)',1,1,null,1) eul, xrv.report_name, xrv.creation_date, xrv.last_update_date from xxen_reports_v xrv where xrv.description like '%'||chr(10)||'Object IDs: %'||chr(10)||'EUL: %' ) xrv where z.object_use_key is not null and z.object_use_key=xrv.object_use_key(+) and upper(z.eul)=upper(xrv.eul(+)) ) y group by y.eul, y.doc_id ) conv where xu.status_code_=xxen_upload.status_success and upper(xu.eul)=upper(xdwx.eul(+)) and xu.workbook_id=xdwx.doc_id(+) and upper(xu.eul)=upper(conv.eul(+)) and xu.workbook_id=conv.doc_id(+)