FA:Query - List of Assigned Roles For a Given User

SELECT ase_user_vl.user_id user_id
     , ase_user_vl.user_login user_login
     , ase_user_vl.user_display_name user_display_name
     , ase_role_vl.code code
     , ase_role_vl.role_name role_name
     , ase_role_vl.description description
     , ase_user_role_mbr.role_id role_id
  FROM fusion.ase_user_vl
     , fusion.ase_role_vl
     , fusion.ase_user_role_mbr
 WHERE ase_user_vl.user_id = ase_user_role_mbr.user_id
   AND ase_user_role_mbr.role_id = ase_role_vl.role_id
   AND ase_user_vl.effective_end_date IS NULL
   AND ase_role_vl.effective_end_date IS NULL
   AND ase_user_role_mbr.effective_end_date IS NULL
   AND upper(user_login) = upper('&user_name')
 ORDER BY role_name

FA:SQL:Manage Data Access for Users

 SELECT username
     , role
     , security_context
     , security_context_value
  FROM ( SELECT pu.username
              , prd.role_name role
              , 'Data Access Set' security_context
              , gl.name security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.gl_access_sets gl
            , fusion.per_users pu
        WHERE gl.access_set_id = role.access_set_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Business Unit' security_context
            , bu.bu_name security_context_value
         FROM fusion.fun_all_business_units_v bu
            , fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.per_users pu
        WHERE role.org_id = bu.bu_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Ledgers' security_context
            , led.name security_context_value
         FROM fusion.gl_ledgers led
            , fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.per_users pu
        WHERE role.ledger_id = led.ledger_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Asset Book' security_context
            , book.book_type_name security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.fa_book_controls book
            , fusion.per_users pu
        WHERE book.book_control_id = role.book_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Intercompany Organization' security_context
            , interco.interco_org_name security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.fun_interco_organizations interco
            , fusion.per_users pu
        WHERE interco.interco_org_id = role.interco_org_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Cost Organization' sercurity_context
            , cost.cost_org_name security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.cst_cost_orgs_v cost
            , fusion.per_users pu
        WHERE cost.cost_org_id = role.cst_organization_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Manufacturing Plant' security_context
            , iop.organization_code security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.rcs_mfg_parameters mfg
            , inv_org_parameters iop
            , fusion.per_users pu
        WHERE mfg.organization_id = role.mfg_organization_id
          AND mfg.organization_id = iop.organization_id        
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Control Budget' security_context
            , budget.name security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.xcc_control_budgets budget
            , fusion.per_users pu
        WHERE budget.control_budget_id = role.control_budget_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Reference data Set' security_context
            , st.set_name security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.fnd_setid_sets_vl st
            , fusion.per_users pu
        WHERE st.set_id = role.set_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Inventory Organization' security_context
            , inv.organization_code security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.inv_org_parameters inv
            , fusion.per_users pu
        WHERE inv.organization_id = role.inv_organization_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid
       UNION
       SELECT pu.username
            , prd.role_name role
            , 'Project Organization Classification' security_context
            , hr.classification_code security_context_value
         FROM fusion.fun_user_role_data_asgnmnts role
            , per_roles_dn_vl prd
            , fusion.hr_org_unit_classifications_f hr
            , fusion.per_users pu
        WHERE hr.org_unit_classification_id = role.org_id
          AND prd.role_common_name = role.role_name
          AND pu.user_guid = role.user_guid)
 WHERE username = :p_user_name
 ORDER BY 3
        --, 4