SQL Query to list All User Roles in ERP Cloud

Shailender Thallam
5 years ago
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
