ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 07A810777481C4ADE0630100007F9923Y with xroac as ( select distinct x.user_id, x.responsibility_id, x.application_id, x.request_group_id, x.group_application_id from ( select coalesce( (select fpov.profile_option_value from fnd_profile_option_values fpov where furg.user_id=fpov.level_value and fpov.level_id=10004 and (fpov.application_id,fpov.profile_option_id) in (select fpo.application_id, fpo.profile_option_id from fnd_profile_options fpo where fpo.profile_option_name='XXEN_REPORT_ACCESS')), (select fpov.profile_option_value from fnd_profile_option_values fpov where furg.responsibility_id=fpov.level_value and furg.responsibility_application_id=fpov.level_value_application_id and fpov.level_id=10003 and (fpov.application_id,fpov.profile_option_id) in (select fpo.application_id, fpo.profile_option_id from fnd_profile_options fpo where fpo.profile_option_name='XXEN_REPORT_ACCESS')), (select fpov.profile_option_value from fnd_profile_option_values fpov where fpov.level_value=0 and fpov.level_id=10001 and (fpov.application_id,fpov.profile_option_id) in (select fpo.application_id, fpo.profile_option_id from fnd_profile_options fpo where fpo.profile_option_name='XXEN_REPORT_ACCESS')) ) blitz_report_access, decode(:show_users,'Y',furg.user_id,-1) user_id, furg.responsibility_id, furg.responsibility_application_id application_id, fr.request_group_id, fr.group_application_id from fnd_user_resp_groups furg, fnd_user fu, fnd_responsibility fr where furg.user_id=fu.user_id and trunc(sysdate) between fu.start_date and nvl(fu.end_date,sysdate) and furg.responsibility_id=fr.responsibility_id and furg.responsibility_application_id=fr.application_id and trunc(sysdate) between fr.start_date and nvl(fr.end_date,sysdate) and fr.menu_id in (select fcmf.menu_id from fnd_form_functions fff, fnd_compiled_menu_functions fcmf where fff.function_name like 'XXEN_REPORTS%' and fff.function_id=fcmf.function_id and fcmf.grant_flag='Y') ) x where x.blitz_report_access is not null ) --------- SQL starts here --------- select &user_name frv.responsibility_name responsibility, fav.application_name, xrv.report_name, xrv.type_dsp type, xrv.category from ( select distinct x.report_id, x.user_id, x.responsibility_id, x.application_id from ( select xroac.*, xra.report_id from xroac, xxen_report_assignments xra where xra.disabled is null and xra.include_exclude='I' and xra.assignment_level='Site' and xra.id1=0 and (not exists (select null from xxen_report_assignment_users xrau where xrau.assignment_id=xra.assignment_id) or exists (select null from xxen_report_assignment_users xrau, fnd_user_resp_groups furg2 where xrau.assignment_id=xra.assignment_id and furg2.user_id=xrau.user_id and furg2.responsibility_id=xroac.responsibility_id and furg2.responsibility_application_id=xroac.application_id and (:show_users is null or xrau.user_id=xroac.user_id))) union select xroac.*, xra.report_id from xroac, xxen_report_assignments xra where xra.disabled is null and xra.include_exclude='I' and xra.assignment_level='Application' and xroac.application_id=xra.id1 and (not exists (select null from xxen_report_assignment_users xrau where xrau.assignment_id=xra.assignment_id) or exists (select null from xxen_report_assignment_users xrau, fnd_user_resp_groups furg2 where xrau.assignment_id=xra.assignment_id and furg2.user_id=xrau.user_id and furg2.responsibility_id=xroac.responsibility_id and furg2.responsibility_application_id=xroac.application_id and (:show_users is null or xrau.user_id=xroac.user_id))) union select xroac.*, xra.report_id from xroac, xxen_report_assignments xra where xra.disabled is null and xra.include_exclude='I' and xra.assignment_level='Request Group' and xroac.request_group_id=xra.id1 and xroac.group_application_id=xra.id2 and (not exists (select null from xxen_report_assignment_users xrau where xrau.assignment_id=xra.assignment_id) or exists (select null from xxen_report_assignment_users xrau, fnd_user_resp_groups furg2 where xrau.assignment_id=xra.assignment_id and furg2.user_id=xrau.user_id and furg2.responsibility_id=xroac.responsibility_id and furg2.responsibility_application_id=xroac.application_id and (:show_users is null or xrau.user_id=xroac.user_id))) union select xroac.*, xra.report_id from xroac, xxen_report_assignments xra where xra.disabled is null and xra.include_exclude='I' and xra.assignment_level='Responsibility' and xroac.responsibility_id=xra.id1 and xroac.application_id=xra.id2 and (not exists (select null from xxen_report_assignment_users xrau where xrau.assignment_id=xra.assignment_id) or exists (select null from xxen_report_assignment_users xrau, fnd_user_resp_groups furg2 where xrau.assignment_id=xra.assignment_id and furg2.user_id=xrau.user_id and furg2.responsibility_id=xroac.responsibility_id and furg2.responsibility_application_id=xroac.application_id and (:show_users is null or xrau.user_id=xroac.user_id))) union select xroac.*, xra.report_id from xroac, xxen_report_assignments xra where xra.disabled is null and xra.include_exclude='I' and xra.assignment_level='User' and xroac.user_id=xra.id1 and :show_users='Y' minus ( select xroac.*, xra.report_id from xroac, xxen_report_assignments xra where xra.disabled is null and xra.include_exclude='E' and xra.assignment_level='Site' and xra.id1=0 union select xroac.*, xra.report_id from xroac, xxen_report_assignments xra where xra.disabled is null and xra.include_exclude='E' and xra.assignment_level='Application' and xroac.application_id=xra.id1 union select xroac.*, xra.report_id from xroac, xxen_report_assignments xra where xra.disabled is null and xra.include_exclude='E' and xra.assignment_level='Request Group' and xroac.request_group_id=xra.id1 and xroac.group_application_id=xra.id2 union select xroac.*, xra.report_id from xroac, xxen_report_assignments xra where xra.disabled is null and xra.include_exclude='E' and xra.assignment_level='Responsibility' and xroac.responsibility_id=xra.id1 and xroac.application_id=xra.id2 union select xroac.*, xra.report_id from xroac, xxen_report_assignments xra where xra.disabled is null and xra.include_exclude='E' and xra.assignment_level='User' and xroac.user_id=xra.id1 and :show_users='Y' ) ) x ) y, xxen_reports_v xrv, fnd_responsibility_vl frv, fnd_application_vl fav where 1=1 and y.report_id=xrv.report_id and xrv.disabled is null and y.responsibility_id=frv.responsibility_id and y.application_id=frv.application_id and y.application_id=fav.application_id order by 1,2,3,4 Aligned the access logic with the runtime (xxen_reports_run_vset_v / xxen_report_ebs.initialize_report_access_cache). Disabled assignments are now ignored: every Include and Exclude branch filters xra.disabled is null, so a disabled assignment no longer grants (or revokes) access in this report. Responsibilities are matched on function name like XXEN_REPORTS%, so responsibilities holding only the Blitz Report (Web) function are no longer omitted. Removed the obsolete Organization assignment level, which was dropped from the product and can no longer grant access at runtime, together with the per_security_profiles / hr_operating_units / per_organization_list join and the MO/HR profile lookups that existed only to derive its organization_id. Site branches now match on id1=0 like the runtime does. US Blitz Report Assignments and Responsibilities Lists all responsibilities, users, and the bitz reports that they can access, presumed they have their Blitz Report Access profile option set to 'User' Enginatics &user_name 1=1 :show_users 1 10 1=1 frv.responsibility_name=:responsibility LOV FND Responsibility Name 8E2FF36EDE9E79D2E0530100007F1FF2 Y select frv.responsibility_name value, frv.description from fnd_responsibility_vl frv order by frv.responsibility_name US Responsibility Name 2 20 1=1 xrv.report_name=:report_name LOV Blitz Report Name 8E2FF36EDEA879D2E0530100007F1FF2 Y select xrv.report_name value, xrv.type_dsp||nvl2(xrv.type,' ',null)||nvl2(xrv.category,'('||xrv.category||') ',null)||substrb(xrv.description,1,4000-lengthb(xrv.type_dsp||nvl2(xrv.type,' ',null)||nvl2(xrv.category,'('||xrv.category||') ',null))) description from xxen_reports_v xrv where nvl(xrv.type,'x')<>'S' and (fnd_profile.value('XXEN_REPORT_ACCESS')='S' or nvl(xrv.type,'x')<>'U') order by xrv.report_name US Report Name 3 30 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 US Category 4 40 1=1 y.user_id in (select fu.user_id from fnd_user fu where fu.user_name=:user_name) LOV FND User Name 8E2FF36EDE8479D2E0530100007F1FF2 Y Y select fu.user_name value, trim(coalesce( trim(papf.first_name||' '||papf.last_name), fu.description, fu.email_address, papf.email_address )||fu.inactive) description from (select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf, ( select distinct furg.user_id, count(*) over (partition by furg.user_id) resp_count, max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key from fnd_responsibility fr, fnd_user_resp_groups_direct furg where fr.responsibility_id=furg.responsibility_id and fr.application_id=furg.responsibility_application_id ) furg where fu.employee_id=papf.person_id(+) and fu.user_id=furg.user_id(+) and not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE') order by fu.inactive desc, fu.user_name US User Name 5 50 :show_users LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show Users 6 &user_name xxen_util.user_name(y.user_id) user_name, Y US Show Users