RWB Reports

Description
Categories: RWB
Polaris Reporting Workbench reports, folders, data objects, database views and the data object folders that these views reside in
Run RWB Reports and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Report Name
rr.report_name=:report_name
LOV
Data Object
rv.view_alias=:data_object
LOV
View Name
rv.db_view_name=:db_view_name
LOV
Report Id
rr.report_id=:report_id
Number
View Id
rv.view_id=:view_id
Number
Report Folder
rf.folder_name=:folder
LOV
Data Object Folder
rf2.folder_name=:data_object_folder
LOV
Execution Count within Days
 
Number
Active only
rr.report_id=rrs.report_id
LOV
Expand Folders
rf.folder_name report_folder,
LOV Oracle
Expand Data Objects
rv.view_alias data_object,
rv.db_view_name db_view,
rv.view_id,
listagg(rf2.folder_name,'; ') within group (order by rf2.folder_name) over (partition by rr.report_id, rv.view_id) data_object_folders,
LOV Oracle