ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDEFA79D2E0530100007F1FF2 Blitz Report Category Y select xrcv.category value, (select count(*) from xxen_report_category_assigns xrca where xrcv.category_id=xrca.category_id) description from xxen_report_categories_v xrcv order by xrcv.category US 028C2F5E98A1A51DE0630100007F0DD5 Blitz Report Type Y select xrv.type_dsp value, count(*) description from xxen_reports_v xrv group by xrv.type_dsp order by count(*) desc US 8E2FF36EDEA679D2E0530100007F1FF2 Yes Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US 0F3A7C2E9B1D4A6F8E0C5B7D9A2F4E16Y select xrv.report_name, xxen_util.application_name(substr(xrv.report_name,1,instr(xrv.report_name,' ')-1)) application, xrv.type_dsp type, xrv.category, xxen_util.yes(xrv.disabled) disabled, xxen_util.zero_to_null(xra.total_assignments) total_assignments, xxen_util.zero_to_null(xra.site_assignments) site_assignments, xxen_util.zero_to_null(xra.application_assignments) application_assignments, xxen_util.zero_to_null(xra.request_group_assignments) request_group_assignments, xxen_util.zero_to_null(xra.responsibility_assignments) responsibility_assignments, xxen_util.zero_to_null(xra.user_assignments) user_assignments, xxen_util.zero_to_null(xra.form_assignments) form_assignments, xxen_util.zero_to_null(xra.function_assignments) function_assignments, xxen_util.zero_to_null(xra.report_assignments) report_assignments, xxen_util.zero_to_null(xra.excluded_assignments) excluded_assignments, xxen_util.user_name(xrv.created_by) created_by, xxen_util.client_time(xrv.creation_date) creation_date, xxen_util.user_name(xrv.last_updated_by) last_updated_by, xxen_util.client_time(xrv.last_update_date) last_update_date, xrv.report_id from xxen_reports_v xrv, ( select xra.report_id, count(distinct case when xra.include_exclude='I' then xra.assignment_level||':'||xra.id1||','||xra.id2 end) total_assignments, count(distinct case when xra.include_exclude='I' and xra.assignment_level='Site' then xra.id1||','||xra.id2 end) site_assignments, count(distinct case when xra.include_exclude='I' and xra.assignment_level='Application' then xra.id1||','||xra.id2 end) application_assignments, count(distinct case when xra.include_exclude='I' and xra.assignment_level='Request Group' then xra.id1||','||xra.id2 end) request_group_assignments, count(distinct case when xra.include_exclude='I' and xra.assignment_level='Responsibility' then xra.id1||','||xra.id2 end) responsibility_assignments, count(distinct case when xra.include_exclude='I' and xra.assignment_level='User' then xra.id1||','||xra.id2 end) user_assignments, count(distinct case when xra.include_exclude='I' and xra.assignment_level='Form' then xra.id1||','||xra.id2 end) form_assignments, count(distinct case when xra.include_exclude='I' and xra.assignment_level='Function' then xra.id1||','||xra.id2 end) function_assignments, count(distinct case when xra.include_exclude='I' and xra.assignment_level='Report' then xra.id1||','||xra.id2 end) report_assignments, count(distinct case when xra.include_exclude='E' then xra.assignment_level||':'||xra.id1||','||xra.id2 end) excluded_assignments from xxen_report_assignments xra group by xra.report_id ) xra where 1=1 and 2=2 and 3=3 and xrv.report_id=xra.report_id(+) order by xra.total_assignments nulls first, xrv.report_name Initial version. Lists all reports of all types with the count of assignments at each level (Site, Application, Request Group, Responsibility, User, Form, Function, Report) plus excluded assignments, using xxen_util.zero_to_null so unassigned levels show as blank cells and fully unassigned reports stand out. US Blitz Report Assignment Validation Lists every Blitz Report with the number of assignments it has at each assignment level, so you can validate that all reports are assigned. Each level column counts the distinct included assignment targets at that level (Site, Application, Request Group, Responsibility, User, Form, Function, Report). The Excluded column counts exclusion assignments. The zero_to_null function leaves a blank cell where there is no assignment, so reports missing an assignment stand out at a glance - a row that is completely blank across the assignment columns is not assigned to anyone. Use the Category, Not in Category and Type parameters to limit the scope. Set 'Not Assigned Only' to Yes to list only reports that have no include assignment at any level, or pick a level in 'Not Assigned at Level' to find reports missing an assignment at one specific level. Enginatics 1=1 2=2 3=3 1 10 1=1 xrv.report_id in ( select xrca.report_id from xxen_report_category_assigns xrca, xxen_report_categories_v xrcv where xrcv.category=:category and xrcv.category_id=xrca.category_id ) LOV Blitz Report Category 8E2FF36EDEFA79D2E0530100007F1FF2 Y select xrcv.category value, (select count(*) from xxen_report_category_assigns xrca where xrcv.category_id=xrca.category_id) description from xxen_report_categories_v xrcv order by xrcv.category F Catégorie US Category 2 20 1=1 xrv.report_id not in ( select xrca.report_id from xxen_report_category_assigns xrca, xxen_report_categories_v xrcv where xrcv.category=:not_in_category and xrcv.category_id=xrca.category_id ) LOV Blitz Report Category 8E2FF36EDEFA79D2E0530100007F1FF2 Y select xrcv.category value, (select count(*) from xxen_report_category_assigns xrca where xrcv.category_id=xrca.category_id) description from xxen_report_categories_v xrcv order by xrcv.category US Not in Category 3 30 1=1 xrv.type_dsp=:type LOV Blitz Report Type 028C2F5E98A1A51DE0630100007F0DD5 Y select xrv.type_dsp value, count(*) description from xxen_reports_v xrv group by xrv.type_dsp order by count(*) desc US Type 4 40 1=1 xrv.disabled='Y' 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 US Disabled 5 1=1 xrv.disabled is null N US Disabled 6 50 2=2 xrv.report_id not in ( select xra.report_id from xxen_report_assignments xra where xra.include_exclude='I' ) LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Not Assigned Only 7 60 3=3 xrv.report_id not in ( select xra.report_id from xxen_report_assignments xra where xra.include_exclude='I' and xra.assignment_level=:missing_level ) LOV custom Y select x.value, (select count(distinct xra.report_id) from xxen_report_assignments xra where xra.include_exclude='I' and xra.assignment_level=x.value) description from ( select 'Site' value from dual union all select 'Application' from dual union all select 'Request Group' from dual union all select 'Responsibility' from dual union all select 'User' from dual union all select 'Form' from dual union all select 'Function' from dual union all select 'Report' from dual ) x order by x.value US Not Assigned at Level