ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 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 发现者终端用户层 B3324709C867CCD6E0530100007F9982 select x.owner, decode(x.current_ads_table_owner,x.new_ads_table_owner,xxen_util.meaning('Y','YES_NO',0)) ads_table_owner, decode(x.current_lov_eul,x.new_lov_eul,xxen_util.meaning('Y','YES_NO',0)) lov_schema, x.created, y.ads_sqls, y.folders, y.xdfu_count, y.workbooks "Workbooks (ed)", y.active_workbooks "Active Workbooks (eqs)", y.active_existing_workbooks "Active existing Workbooks", xdwx.count "Uploaded Workbooks (xdwx)", xdw.count "Flattened Workbooks (xdw)", xds.count "Flattened Sheet (xds)", xrtv.workbook_count "Imported Workbooks (xrt)", xrtv.count "Imported Templates (xrt)", xrv.count "Imported Reports (xr)", 'begin'||chr(10)|| 'execute immediate ''create or replace synonym apps.ams_discoverer_sql for '||lower(x.new_ads_table_owner)||'.ams_discoverer_sql'';'||chr(10)|| 'update xxen_report_parameter_lovs xrpl set xrpl.lov_query=replace(xrpl.lov_query,'''||x.current_lov_eul||'.'','''||x.new_lov_eul||'.''),xrpl.last_updated_by=xxen_util.user_id(''SYSADMIN''),xrpl.last_update_date=sysdate where xrpl.lov_query like ''%'||x.current_lov_eul||'.%'' and xrpl.lov_name like ''DIS %'';'||chr(10)|| 'update xxen_report_parameters xrp set xrp.lov_query=replace(xrp.lov_query,'''||x.current_lov_eul||'.'','''||x.new_lov_eul||'.''),xrp.last_updated_by=xxen_util.user_id(''SYSADMIN''),xrp.last_update_date=sysdate where xrp.lov_query like ''%'||x.current_lov_eul||'.%'' and xrp.parameter_id in (select xrpv.parameter_id from xxen_report_parameters_v xrpv where xrpv.report_name like ''DIS %'');'||chr(10)|| 'update xxen_report_parameters xrp set xrp.sql_text=replace(xrp.sql_text,'''||x.current_lov_eul||'.'','''||x.new_lov_eul||'.''),xrp.last_updated_by=xxen_util.user_id(''SYSADMIN''),xrp.last_update_date=sysdate where xrp.sql_text like ''%'||x.current_lov_eul||'.%'' and xrp.parameter_id in (select xrpv.parameter_id from xxen_report_parameters_v xrpv where xrpv.report_name like ''DIS %'');'||chr(10)|| 'update fnd_profile_option_values fpov set fpov.profile_option_value='''||x.new_lov_eul||''', fpov.last_updated_by=xxen_util.user_id(''SYSADMIN''), fpov.last_update_date=sysdate where fpov.profile_option_id=(select fpo.profile_option_id from fnd_profile_options fpo where fpo.profile_option_name=''XXEN_REPORT_DISCOVERER_DEFAULT_EUL'');'||chr(10)|| 'commit;'||chr(10)|| 'end;' change_command from ( select lower(do.owner) owner, (select regexp_substr(dbms_lob.substr(xrpl.lov_query),'(\w+)\.eul5_bas eb',1,1,null,1) current_eul from xxen_report_parameter_lovs xrpl where xrpl.guid='8E2FF36EDF1179D2E0530100007F1FF2') current_lov_eul, lower(do.owner) new_lov_eul, (select ds.table_owner from dba_synonyms ds where ds.owner='APPS' and ds.table_name='AMS_DISCOVERER_SQL') current_ads_table_owner, nvl((select do.owner from dba_tables dt where do.owner=dt.owner and dt.table_name='AMS_DISCOVERER_SQL'),'AMS') new_ads_table_owner, do.created from dba_objects do where 1=1 and do.object_type='TABLE' and do.object_name='EUL5_VERSIONS' ) x, ( &eul_object_counts ) y, (select count(*) count, xdwx.eul from xxen_discoverer_workbook_xmls xdwx group by xdwx.eul) xdwx, (select count(distinct xdw.doc_name||'.'||xdw.doc_owner) count, xdw.eul from xxen_discoverer_workbooks xdw group by xdw.eul) xdw, (select count(distinct xds.doc_name||'.'||xds.doc_owner||'.'||xds.sheet_name) count, xds.eul from xxen_discoverer_sheets xds group by xds.eul) xds, (select count(*) count, count(distinct xxen_util.instring(xrtv.template_name,':',1)) workbook_count, regexp_substr(xrtv.report_description,chr(10)||'EUL: (\w+)',1,1,null,1) eul from xxen_report_templates_v xrtv where xrtv.report_description like 'Imported Discoverer folders:%Object IDs: %EUL: %' group by regexp_substr(xrtv.report_description,chr(10)||'EUL: (\w+)',1,1,null,1)) xrtv, (select count(*) count, regexp_substr(xrv.description,chr(10)||'EUL: (\w+)',1,1,null,1) eul from xxen_reports_v xrv where xrv.description like 'Imported Discoverer folders:%Object IDs: %EUL: %' group by regexp_substr(xrv.description,chr(10)||'EUL: (\w+)',1,1,null,1)) xrv where x.owner=y.owner(+) and x.owner=xdwx.eul(+) and x.owner=xdw.eul(+) and x.owner=xds.eul(+) and x.owner=xrtv.eul(+) and x.owner=xrv.eul(+) order by x.created desc Y AR DIS End User Layers طبقات المستخدم النهائي للمكتشف D DIS End User Layers Entdecker-Endbenutzerschichten E DIS End User Layers Capas de usuario final de Discoverer F DIS End User Layers Découvreur de couches d'utilisateurs finaux I DIS End User Layers Livelli utente finale Discoverer JA DIS End User Layers 発見者エンドユーザー層 KO DIS End User Layers Discoverer 최종 사용자 계층 PTB DIS End User Layers Descobridor de camadas do usuário final RU DIS End User Layers Первоначальные слои конечного пользователя S DIS End User Layers Discoverer-slutanvändarlager TR DIS End User Layers Discoverer son kullanıcı katmanları US DIS End User Layers Discoverer end user layers ZHS DIS End User Layers 发现者终端用户层 Enginatics &eul_object_counts 1=1 1 -10 &eul_object_counts select 'select '''||x.owner||''' owner, '||x.ads_text||' ads_sqls, (select count(*) from xxen_discoverer_fnd_user xdfu where xdfu.eul='''||x.owner||''') xdfu_count, (select count(*) from '||x.owner||'.eul5_objs) folders, (select count(*) from '||x.owner||'.eul5_documents) workbooks, (select count(distinct eqs.qs_doc_name||''.''||upper(eqs.qs_doc_owner)) from '||x.owner||'.eul5_qpp_stats eqs where eqs.qs_created_date>sysdate-:history_days) active_workbooks, (select count(distinct eqs.qs_doc_name||''.''||upper(eqs.qs_doc_owner)) from '||x.owner||'.eul5_qpp_stats eqs where eqs.qs_created_date>sysdate-:history_days and (eqs.qs_doc_name,upper(eqs.qs_doc_owner)) in (select ed.doc_name, xxen_util.dis_user_name(ed.doc_eu_id,'''||x.owner||''',''N'') doc_owner from '||x.owner||'.eul5_documents ed)) active_existing_workbooks from dual'|| decode(x.owner,max(x.owner) over (),null,' union all') text from ( select lower(do.owner) owner, nvl((select '(select count(distinct ads.workbook_owner_name||ads.workbook_name||ads.worksheet_name) from '||dt.owner||'.ams_discoverer_sql ads)' from dba_tables dt where dt.table_name='AMS_DISCOVERER_SQL' and do.owner=dt.owner),'null') ads_text from dba_objects do where do.object_type='TABLE' and do.object_name='EUL5_VERSIONS' ) x order by x.owner Char x US Dummy for dynamic SQL execution 2 10 1=1 do.owner=upper(:end_user_layer) 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 US End User Layer 3 20 1=1 :history_days=:history_days Number fnd_profile.value('XXEN_REPORT_DISCOVERER_IMPORT_LOV_ACCESS_HISTORY_DAYS') Y US History days