You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

Extracting Role Hierarchy data from Oracle Cloud via SQL or Seeded Report

Received Response
edited Feb 18, 2020 12:05PM in Applications Security 2 comments



We are using 19D (

I have a requirement to create copies of some existing Roles. Some of the Roles are seeded Oracle Roles, others are custom roles.

I am familiar with being able to run SQL to find roles assigned to users:

select prdt.role_name
, prd.role_common_name
, fu.username
, to_char(pur.creation_date, 'YYYY-MM-DD HH24:MI:SS') date_assigned_to_user
, pur.created_by
from per_users fu
join per_user_roles pur on fu.user_id = pur.user_id
join per_roles_dn_tl prdt on pur.role_id = prdt.role_id
join per_roles_dn prd on prd.role_id = prdt.role_id and prdt.language = 'US'
where 1 = 1
and prdt.role_name in ('Collections Manager')
order by to_char(pur.creation_date, 'YYYY-MM-DD HH24:MI:SS') desc

Howdy, Stranger!

Log In

To view full details, sign in.


Don't have an account? Click here to get started!