Responsibilities User Access in Oracle Apps

User Access List, linked to HR tables

SELECT   papf.full_name        , papf.employee_number empno        , fu.user_name un        , fu.description        , haout.NAME hr_org             , frt.responsibility_name        , papf.email_address        , hlat.description user_location        , hlat.location_code user_location_code        , fat.application_name application        , furg.start_date resp_start     FROM applsys.fnd_user fu        , applsys.fnd_user fu2        , applsys.fnd_user fu3        , hr.per_all_people_f papf        , hr.per_all_assignments_f paaf        , hr.hr_all_organization_units_tl haout        , hr.hr_locations_all_tl hlat        , hr.per_person_type_usages_f pptu        , apps.fnd_user_resp_groups_direct furg        , apps.fnd_responsibility fr        , apps.fnd_responsibility_tl frt        , applsys.fnd_application fa        , applsys.fnd_application_tl fat    WHERE papf.person_id = fu.employee_id(+)      AND paaf.location_id = hlat.location_id(+)      AND fa.application_id = fat.application_id      AND haout.organization_id = paaf.organization_id      AND frt.application_id = fat.application_id      AND fr.responsibility_id = frt.responsibility_id      AND paaf.person_id = papf.person_id      AND furg.user_id = fu.user_id      AND frt.responsibility_id = furg.responsibility_id      AND furg.created_by = fu3.user_id      AND furg.last_updated_by = fu2.user_id      AND papf.person_id = pptu.person_id(+)      AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date      AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date      AND SYSDATE BETWEEN pptu.effective_start_date AND pptu.effective_end_date      AND NVL(furg.end_date, SYSDATE + 1) > SYSDATE      AND NVL(fu.end_date, SYSDATE + 1) > SYSDATE      AND papf.current_employee_flag = 'Y'      AND paaf.assignment_type = 'E'      AND paaf.primary_flag = 'Y'      AND fu.user_name = 'SYSADMIN' --     AND FA.APPLICATION_SHORT_NAME = 'INV' --     AND frt.responsibility_name = 'System Administrator' ORDER BY papf.full_name        , frt.responsibility_name;

Simple access list, with no link to HR tables

Sometimes it can be handy to look at access without joining to HR tables, as HR tables are date tracked and can sometimes return strange data depending on how today's date interacts with the effective dates on the HR tables.

SELECT DISTINCT fu.user_name               , fu.description               , fu.email_address               , fu.last_logon_date               , furg.creation_date resp_added               , furg.start_date resp_start_date               , frt.responsibility_name               , fmt.user_menu_name            FROM applsys.fnd_user fu               , apps.fnd_user_resp_groups_direct furg               , apps.fnd_responsibility_tl frt               , apps.fnd_responsibility fr               , apps.fnd_application fa               , applsys.fnd_menus_tl fmt           WHERE fr.responsibility_id = frt.responsibility_id             AND frt.responsibility_id = furg.responsibility_id             AND furg.user_id = fu.user_id             AND fr.application_id = fa.application_id             AND fr.menu_id = fmt.menu_id             AND NVL(furg.end_date, SYSDATE + 1) > SYSDATE             AND NVL(fu.end_date, SYSDATE + 1) > SYSDATE             AND frt.responsibility_name = 'System Administrator' --            AND fu.user_name = :un --            AND furg.CREATION_DATE >= '01-JAN-2007' --            AND fa.application_short_name = 'PO' --            AND fu.user_name = 'SYSADMIN'               AND 1 = 1;

User Count Per Application

SELECT DISTINCT fa.application_short_name, count(*) ct            FROM applsys.fnd_user fu               , apps.fnd_user_resp_groups_direct furg               , apps.fnd_responsibility_tl frt               , apps.fnd_responsibility fr               , apps.fnd_application fa           WHERE fr.responsibility_id = frt.responsibility_id             AND frt.responsibility_id = furg.responsibility_id             AND furg.user_id = fu.user_id             AND fr.application_id = fa.application_id             AND NVL(furg.end_date, SYSDATE + 1) > SYSDATE             AND NVL(fu.end_date, SYSDATE + 1) > SYSDATE --            AND fu.user_name = :un --            AND furg.CREATION_DATE >= '01-JAN-2007' --            AND frt.responsibility_name = 'System Administrator' --            AND fa.application_short_name = 'INV'        GROUP BY fa.application_short_name        ORDER BY 1;

User Count Per Responsibility

SELECT fat.application_name module      , frt.responsibility_name      , fr.responsibility_key      , (SELECT DISTINCT COUNT(*)                    FROM applsys.fnd_user fu                       , apps.fnd_user_resp_groups_direct furg                   WHERE furg.user_id = fu.user_id                     AND frt.responsibility_id = furg.responsibility_id                     AND NVL(furg.end_date, SYSDATE + 1) > SYSDATE) user_ct   FROM applsys.fnd_responsibility fr      , applsys.fnd_responsibility_tl frt      , applsys.fnd_application_tl fat      , applsys.fnd_request_groups frg  WHERE fr.responsibility_id = frt.responsibility_id    AND frt.application_id = fat.application_id    AND fr.request_group_id = frg.request_group_id    AND frt.responsibility_name LIKE '%GL%'    AND fr.responsibility_key NOT LIKE '%SCH%'    AND fat.application_name = 'General Ledger'    AND (SELECT COUNT(*)           FROM applsys.fnd_user fu              , apps.fnd_user_resp_groups_direct furg          WHERE furg.user_id = fu.user_id            AND frt.responsibility_id = furg.responsibility_id            AND NVL(furg.end_date, SYSDATE + 1) > SYSDATE) > 1    AND NVL(fr.end_date, SYSDATE + 1) > SYSDATE;

User Management SQL

-- ONCE ON RELEASE 12, USER MANAGEMENT ALLOWS ACCESS TO BE GRANTED VIA 'ROLES' -- THIS SQL CAN BE HANDY TO FIND OUT ABOUT ROLES ASSIGNED TO USERS   SELECT   fu.user_name        , fu.description        , urav.display_name     FROM apps.umx_role_assignments_v urav        , applsys.fnd_user fu    WHERE urav.user_id = fu.user_id      AND urav.user_name IN ('SYSADMIN')      AND role_name LIKE '%UMX%' --     AND status_code = 'APPROVED' --     AND urav.display_name = 'Chief Cook and Bottle Washer' ORDER BY fu.description        , urav.display_name;