DIS End User Layers

Description
Categories: Enginatics
Repository: Github Columns: Owner, Ads Table Owner, Lov Schema, Created, Folder Count, Workbook Count, Ads Sql Count, Change Command ...
Discoverer end user layers
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.folder_count,
y.workbook_count,
y.ads_sql_count,
'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
do.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
where
x.owner=y.owner(+)
order by
x.created desc
Parameter NameSQL textValidation
End User Layer
do.owner=upper(:end_user_layer)
LOV
Dummy for dynamic SQL execution
select
'select '''||x.owner||''' owner, (select count(*) from '||x.owner||'.eul5_objs) folder_count, (select count(*) from '||x.owner||'.eul5_documents) workbook_count, (select count(distinct ads.workbook_owner_name||ads.workbook_name||ads.worksheet_name) from '||x.ads_owner||'.ams_discoverer_sql ads) ads_sql_count from dual'||
decode(x.owner,max(x.owner) over (),null,' union all') text
from
(
select
do.owner,
nvl((select dt.owner from dba_tables dt where dt.table_name='AMS_DISCOVERER_SQL' and do.owner=dt.owner),'AMS') ads_owner
from
dba_objects do
where
do.object_type='TABLE' and
do.object_name='EUL5_VERSIONS'
) x
order by
x.owner