ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN BCE3B875D7F13357E053B86B63581E03 RWB Data Object Y select rv.view_alias value, rv.db_view_name||nvl2(rv.description,' ('||rv.description||')',null) description from xxfo.rx_views rv order by rv.view_alias US Polaris Reporting Workbench data object BCE3B875D7F03357E053B86B63581E03 RWB Folder (Data Object) Y select distinct rf.folder_name value, rf.description||' ('||count(*)||')' description from rx_folders rf, rx_folder_association rfa where rf.folder_id=rfa.folder_id and rfa.view_id is not null group by rf.folder_name, rf.description order by value US Polaris Reporting Workbench folders containing data objects BCE3B2C60D383359E053B86B635829C8 RWB Folder (Report) Y select distinct rf.folder_name value, rf.description||' ('||count(*)||')' description from rx_folders rf, rx_folder_association rfa where rf.folder_id=rfa.folder_id and rfa.report_id is not null group by rf.folder_name, rf.description order by value US Polaris Reporting Workbench folders containing reports B2998B7D582438F7E0530100007F1BBB RWB Report Name Y select rr.report_name value, rr.description from xxfo.rx_reports rr order by rr.report_name US B29A78367A174C9BE0530100007FD3E8 RWB View Name Y select rv.db_view_name value, rv.view_name||nvl2(rv.description,' ('||rv.description||')',null) description from xxfo.rx_views rv order by rv.db_view_name US Polaris Reporting Workbench database view name 8E2FF36EDEA679D2E0530100007F1FF2 Yes Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US B29A78367A164C9BE0530100007FD3E8Y 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 US RWB Reports Polaris Reporting Workbench reports, folders, data objects, database views and the data object folders that these views reside in RWB &data_object &execution_count &folder 1=1 :days 1 10 1=1 rr.report_name=:report_name LOV RWB Report Name B2998B7D582438F7E0530100007F1BBB Y select rr.report_name value, rr.description from xxfo.rx_reports rr order by rr.report_name F Nom du rapport US Report Name 2 15 1=1 rv.view_alias=:data_object LOV RWB Data Object BCE3B875D7F13357E053B86B63581E03 Y select rv.view_alias value, rv.db_view_name||nvl2(rv.description,' ('||rv.description||')',null) description from xxfo.rx_views rv order by rv.view_alias F Objet de données US Data Object 3 20 1=1 rv.db_view_name=:db_view_name LOV RWB View Name B29A78367A174C9BE0530100007FD3E8 Y select rv.db_view_name value, rv.view_name||nvl2(rv.description,' ('||rv.description||')',null) description from xxfo.rx_views rv order by rv.db_view_name F Nom de vue US View Name 4 22 1=1 rr.report_id=:report_id Number F Rapport Id US Report Id 5 25 1=1 rv.view_id=:view_id Number F Voir Id US View Id 6 30 1=1 rf.folder_name=:folder LOV RWB Folder (Report) BCE3B2C60D383359E053B86B635829C8 Y select distinct rf.folder_name value, rf.description||' ('||count(*)||')' description from rx_folders rf, rx_folder_association rfa where rf.folder_id=rfa.folder_id and rfa.report_id is not null group by rf.folder_name, rf.description order by value F Dossier des rapports US Report Folder 7 35 1=1 rf2.folder_name=:data_object_folder LOV RWB Folder (Data Object) BCE3B875D7F03357E053B86B63581E03 Y select distinct rf.folder_name value, rf.description||' ('||count(*)||')' description from rx_folders rf, rx_folder_association rfa where rf.folder_id=rfa.folder_id and rfa.view_id is not null group by rf.folder_name, rf.description order by value F Dossier d'objets de données US Data Object Folder 8 37 :days Number 365 F Nombre de jours d'exécution US Execution Count within Days 9 &execution_count rrs.count executions_<parameter_value>_days, rrs.user_count users_<parameter_value>_days, F Nombre de jours d'exécution US Execution Count within Days 10 38 1=1 rr.report_id=rrs.report_id LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y F Actif uniquement US Active only 11 40 &folder rf.folder_name report_folder, LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y N Y F Développer les dossiers US Expand Folders 12 &folder listagg(rf.folder_name,'; ') within group (order by rf.folder_name) over (partition by rr.report_id) report_folders, N F Développer les dossiers US Expand Folders 13 50 &data_object 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 Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y N Y F Développer les objets de données US Expand Data Objects 14 &data_object listagg(rv.view_alias,'; ') within group (order by rv.view_alias) over (partition by rr.report_id) data_objects, listagg(rv.db_view_name,'; ') within group (order by rv.db_view_name) over (partition by rr.report_id) db_views, listagg(rf2.folder_name,'; ') within group (order by rf2.folder_name) over (partition by rr.report_id) data_object_folders, N F Développer les objets de données US Expand Data Objects