DIS End User Layers

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Discoverer end user layers
Run DIS End User Layers and other Oracle EBS reports with Blitz Report™ on our demo environment
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.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
Parameter Name SQL text Validation
End User Layer 
do.owner=upper(:end_user_layer)
 LOV
History days 
:history_days=:history_days
 Number
Download
Related Blitz Reports
DIS Workbook Import Validation COPY OF: DIS Workbook Import Validation DIS Workbooks, Folders, Items and LOVs DIS Worksheet SQLs DBA Blocking Sessions DBA AWR Blocking Session Summary DBA AWR Active Session History DBA SGA Active Session History Blitz Report VPD Policy Setup
Blitz Report™

Blitz Report™ provides multiple benefits: