SQL Query to list All User Roles in ERP Cloud

SELECT *
FROM
  (SELECT user_info.user_login,
    user_info.user_id,
    user_roles.role_name,
    user_info.creation_date,
    user_info.first_name,
    user_info.last_name,
    user_info.location_code,
    user_info.location_name,
    user_info.town,
    user_info.country,
    user_info.department,
    user_info.username,
    user_info.active_flag
  FROM
    (SELECT DISTINCT pp.creation_date creation_date,
      ppf.first_name first_name,
      ppf.last_name last_name,
      hl.location_code location_code,
      hl.location_name location_name,
      hl.town_or_city town,
      hl.country country,
      pd.name department,
      pu.username username,
      pu.active_flag active_flag,
      au.user_id user_id,
      au.user_login user_login
    FROM per_persons pp,
      per_all_people_f papf,
      per_person_names_f_v ppf,
      hr_locations_all_f_vl hl,
      per_departments pd,
      per_all_assignments_m paaf,
      per_users pu,
      ase_user_vl au
    WHERE au.user_guid          = pu.user_guid(+)
    AND pu.person_id              = papf.person_id(+)
    AND papf.person_id           = pp.person_id(+)
    AND pp.person_id            = ppf.person_id(+)
    AND ppf.person_id       = paaf.person_id(+)
    AND paaf.location_id     = hl.location_id(+)
    AND paaf.organization_id = pd.organization_id(+)
    AND TRUNC(sysdate) BETWEEN NVL(ppf.effective_start_date,TRUNC(sysdate)) AND NVL(ppf.effective_end_date,TRUNC(sysdate))
    AND TRUNC(sysdate) BETWEEN NVL(papf.effective_start_date,TRUNC(sysdate)) AND NVL(papf.effective_end_date,TRUNC(sysdate))
    AND TRUNC(sysdate) BETWEEN NVL(paaf.effective_start_date,TRUNC(sysdate)) AND NVL(paaf.effective_end_date,TRUNC(sysdate))
    AND TRUNC(sysdate) BETWEEN NVL(hl.effective_start_date,TRUNC(sysdate)) AND NVL(hl.effective_end_date,TRUNC(sysdate))
    AND TRUNC(sysdate) BETWEEN NVL(pd.effective_start_date,TRUNC(sysdate)) AND NVL(pd.effective_end_date,TRUNC(sysdate))
    AND TRUNC(sysdate) BETWEEN NVL(au.effective_start_date,TRUNC(sysdate)) AND NVL(au.effective_end_date,TRUNC(sysdate))
    )user_info
  LEFT JOIN
    (SELECT u.user_login user_login,
      r.role_name role_name,
      r.description description ,
      aurm.user_id user_id
    FROM ase_user_vl u,
      ase_role_vl r,
      ase_user_role_mbr aurm
    WHERE r.role_id              = aurm.role_id
    AND aurm.user_id             =u.user_id
    AND r.effective_end_date    IS NULL
    AND aurm.effective_end_date IS NULL
    )user_roles
  ON user_info.user_login=user_roles.user_login
  ) qrslt
WHERE 1 = 1
  AND UPPER(QRSLT.username) = UPPER('shthallam@oracleappsdna.com')
ORDER BY user_login

For Further Reading

  1. Oracle HRMS SQL Query to find out Employee and their Supervisor Hierarchy
  2. Query to list all Documents and their information under a Payment Process Request
  3. SQL Query to List Oracle ERP Cloud Users and their Attached Roles
  4. SQL Query to List Reference Data Sets and their Assignments with Business Units in Oracle ERP Cloud
  5. Oracle Script to Copy Responsibilites of one user account to another user account
  6. SQL Query to list Active Responsibilities of a Active User
  7. How to customize seeded BIP reports in ERP Cloud
Tags:
OracleAppsDNA.com