RWB Reports

select
x.*,
count(*) over (partition by x.view_ids, x.hash) dupl_count
from
(
select distinct
rr.report_name,
&folder
rr.report_short_name,
decode(rr.report_type,'USER','Adhoc','SEEDED','Seeded Adhoc','DASH_BOARD','BI Publisher','VAR','PL/SQL Custom','FLEX','Dashboard','SEEDED_VAR','Seeded PL/SQL Custom',rr.report_type) report_type,
&execution_count
&data_object
listagg(rv.view_id,'; ') within group (order by rv.view_id) over (partition by rr.report_id) view_ids,
rrp.parameter_count,
xxen_util.user_name(rr.owner_person_id) owner,
decode(rr.distinct_flag,'Y','Y') distinct_flag,
rrc.aggregator,
nvl2(rrc3.report_id,'Y',null) custom_aggregation,
nvl2(rr.group_clause,'Y',null) custom_group_clause,
nvl2(rr.having_clause,'Y',null) custom_having_clause,
nvl2(rr.cond_string,'Y',null) custom_where_clause,
nvl2(rr.order_clause,'Y',null) custom_order_clause,
(select 'Y' from rx_sorts rs where rr.report_id=rs.report_id and rownum=1) sort_exists,
rrc.pivot_field,
rrc.calculation_column,
rr.group_clause,
rr.having_clause,
rr.order_clause,
dbms_lob.substr(rr.cond_string,4000,1) where_clause,
count(distinct rfa.folder_id) over (partition by rr.report_id) folder_count,
xxen_util.user_name(rr.previous_owner_person_id) previous_owner,
rr.description,
xxen_util.user_name(rr.created_by) created_by,
xxen_util.client_time(rr.creation_date) creation_date,
xxen_util.user_name(rr.last_updated_by) last_updated_by,
xxen_util.client_time(rr.last_update_date) last_update_date,
rr.output_html_flag,
rr.output_xls_flag,
rr.output_csv_flag,
rr.output_xml_flag,
rr.output_pdf_flag,
rr.output_rtf_flag,
rr.template_name,
rr.multithread_enable,
rr.data_source,
rr.max_row_count,
rr.validate_flag,
rr.query_time_out,
rr.regenerate_flag,
rr.max_pdf_count,
rr.conc_prog_flag,
rr.runnow_max_row_count,
rr.email_enabled_flag,
rr.email_cf,
rr.moac_flag,
rr.runnow_enabled_flag,
dbms_lob.substr(rr.grid_output_config,4000,1) grid_output_config,
rr.del_opt_flag,
rr.cache_flag,
rr.report_id,
ora_hash(rrc2.condition_text||rrp.parameter_text||dbms_lob.substr(rr.cond_string)) hash
from
rx_reports rr,
rx_folder_association rfa,
rx_folders rf,
(select distinct rrc.report_id, rvc.view_id from rx_report_columns rrc, rx_view_columns rvc where rrc.view_column_id=rvc.view_column_id and rrc.enabled_flag='Y') rvc,
rx_views rv,
rx_folder_association rfa2,
rx_folders rf2,
(select distinct count(*) over (partition by rrs.report_id) count, count(distinct rrs.user_id) over (partition by rrs.report_id) user_count, rrs.report_id from rx_report_stats rrs where rrs.start_time>sysdate-:days and rrs.report_id>0) rrs,
(
select distinct
rrc.report_id,
max(rrc.aggregator) over (partition by rrc.report_id) aggregator,
nvl2(max(rrc.pivot_field) over (partition by rrc.report_id),'Y',null) pivot_field,
nvl2(max(rrc.calculation_column) over (partition by rrc.report_id),'Y',null) calculation_column
from
rx_report_columns rrc
where
rrc.enabled_flag='Y'
) rrc,
(
select distinct
rrc.report_id,
listagg(rrc.parameter1_id||rrc.parameter2_type||decode(rrc.parameter2_type,'P',rrp2.parameter_name||rrp2.lov_id,rrc.parameter2_id)) within group (order by rrc.cond_operator) over (partition by rrc.report_id) condition_text
from
rx_report_conditions rrc,
rx_report_parameters rrp2
where
rrc.parameter1_type='C' and
decode(rrc.parameter2_type,'P',rrc.parameter2_id)=rrp2.parameter_id(+) and
rrc.enabled_flag='Y'
) rrc2,
(select distinct rrp.report_id, count(*) over (partition by rrp.report_id) parameter_count, listagg(rrp.parameter_name||rrp.lov_id) within group (order by rrp.sequence_number) over (partition by rrp.report_id) parameter_text from rx_report_parameters rrp where rrp.enabled_flag='Y') rrp,
(
select distinct
x.report_id
from
(
select
rrc.report_id,
lower(regexp_substr(rrc.calculation_column,'((\w+\.)?\w+)\s*\(',1,rowgen.column_value,null,1)) function_name
from
rx_report_columns rrc,
table(xxen_util.rowgen(regexp_count(rrc.calculation_column,'(\w+\.)?\w+\s*\('))) rowgen
where
rrc.view_column_id=-1 and
rrc.enabled_flag='Y'
) x
where
x.function_name in ('count','sum','avg','min','max')
) rrc3
where
1=1 and
rr.report_id=rfa.report_id(+) and
rfa.folder_id=rf.folder_id(+) and
rr.report_id=rvc.report_id(+) and
rvc.view_id=rv.view_id(+) and
rvc.view_id=rfa2.view_id(+) and
rfa2.folder_id=rf2.folder_id(+) and
rr.report_id=rrs.report_id(+) and
rr.report_id=rrc.report_id(+) and
rr.report_id=rrc2.report_id(+) and
rr.report_id=rrp.report_id(+) and
rr.report_id=rrc3.report_id(+)
) x
order by
1,
x.report_name,
6

select x.*, count(*) over (partition by x.view_ids, x.hash) dupl_count from ( select distinct rr.report_name, &folder rr.report_short_name, decode(rr.report_type,'USER','Adhoc','SEEDED','Seeded Adhoc','DASH_BOARD','BI Publisher','VAR','PL/SQL Custom','FLEX','Dashboard','SEEDED_VAR','Seeded PL/SQL Custom',rr.report_type) report_type, &execution_count &data_object listagg(rv.view_id,'; ') within group (order by rv.view_id) over (partition by rr.report_id) view_ids, rrp.parameter_count, xxen_util.user_name(rr.owner_person_id) owner, decode(rr.distinct_flag,'Y','Y') distinct_flag, rrc.aggregator, nvl2(rrc3.report_id,'Y',null) custom_aggregation, nvl2(rr.group_clause,'Y',null) custom_group_clause, nvl2(rr.having_clause,'Y',null) custom_having_clause, nvl2(rr.cond_string,'Y',null) custom_where_clause, nvl2(rr.order_clause,'Y',null) custom_order_clause, (select 'Y' from rx_sorts rs where rr.report_id=rs.report_id and rownum=1) sort_exists, rrc.pivot_field, rrc.calculation_column, rr.group_clause, rr.having_clause, rr.order_clause, dbms_lob.substr(rr.cond_string,4000,1) where_clause, count(distinct rfa.folder_id) over (partition by rr.report_id) folder_count, xxen_util.user_name(rr.previous_owner_person_id) previous_owner, rr.description, xxen_util.user_name(rr.created_by) created_by, xxen_util.client_time(rr.creation_date) creation_date, xxen_util.user_name(rr.last_updated_by) last_updated_by, xxen_util.client_time(rr.last_update_date) last_update_date, rr.output_html_flag, rr.output_xls_flag, rr.output_csv_flag, rr.output_xml_flag, rr.output_pdf_flag, rr.output_rtf_flag, rr.template_name, rr.multithread_enable, rr.data_source, rr.max_row_count, rr.validate_flag, rr.query_time_out, rr.regenerate_flag, rr.max_pdf_count, rr.conc_prog_flag, rr.runnow_max_row_count, rr.email_enabled_flag, rr.email_cf, rr.moac_flag, rr.runnow_enabled_flag, dbms_lob.substr(rr.grid_output_config,4000,1) grid_output_config, rr.del_opt_flag, rr.cache_flag, rr.report_id, ora_hash(rrc2.condition_text||rrp.parameter_text||dbms_lob.substr(rr.cond_string)) hash from rx_reports rr, rx_folder_association rfa, rx_folders rf, (select distinct rrc.report_id, rvc.view_id from rx_report_columns rrc, rx_view_columns rvc where rrc.view_column_id=rvc.view_column_id and rrc.enabled_flag='Y') rvc, rx_views rv, rx_folder_association rfa2, rx_folders rf2, (select distinct count(*) over (partition by rrs.report_id) count, count(distinct rrs.user_id) over (partition by rrs.report_id) user_count, rrs.report_id from rx_report_stats rrs where rrs.start_time>sysdate-:days and rrs.report_id>0) rrs, ( select distinct rrc.report_id, max(rrc.aggregator) over (partition by rrc.report_id) aggregator, nvl2(max(rrc.pivot_field) over (partition by rrc.report_id),'Y',null) pivot_field, nvl2(max(rrc.calculation_column) over (partition by rrc.report_id),'Y',null) calculation_column from rx_report_columns rrc where rrc.enabled_flag='Y' ) rrc, ( select distinct rrc.report_id, listagg(rrc.parameter1_id||rrc.parameter2_type||decode(rrc.parameter2_type,'P',rrp2.parameter_name||rrp2.lov_id,rrc.parameter2_id)) within group (order by rrc.cond_operator) over (partition by rrc.report_id) condition_text from rx_report_conditions rrc, rx_report_parameters rrp2 where rrc.parameter1_type='C' and decode(rrc.parameter2_type,'P',rrc.parameter2_id)=rrp2.parameter_id(+) and rrc.enabled_flag='Y' ) rrc2, (select distinct rrp.report_id, count(*) over (partition by rrp.report_id) parameter_count, listagg(rrp.parameter_name||rrp.lov_id) within group (order by rrp.sequence_number) over (partition by rrp.report_id) parameter_text from rx_report_parameters rrp where rrp.enabled_flag='Y') rrp, ( select distinct x.report_id from ( select rrc.report_id, lower(regexp_substr(rrc.calculation_column,'((\w+\.)?\w+)\s*\(',1,rowgen.column_value,null,1)) function_name from rx_report_columns rrc, table(xxen_util.rowgen(regexp_count(rrc.calculation_column,'(\w+\.)?\w+\s*\('))) rowgen where rrc.view_column_id=-1 and rrc.enabled_flag='Y' ) x where x.function_name in ('count','sum','avg','min','max') ) rrc3 where 1=1 and rr.report_id=rfa.report_id(+) and rfa.folder_id=rf.folder_id(+) and rr.report_id=rvc.report_id(+) and rvc.view_id=rv.view_id(+) and rvc.view_id=rfa2.view_id(+) and rfa2.folder_id=rf2.folder_id(+) and rr.report_id=rrs.report_id(+) and rr.report_id=rrc.report_id(+) and rr.report_id=rrc2.report_id(+) and rr.report_id=rrp.report_id(+) and rr.report_id=rrc3.report_id(+) ) x order by 1, x.report_name, 6