FND Role Hierarchy

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
User Management (UMX) role hierarchy to manage role-based access control (RBAC).
When run for a specified role, the report shows all hierarchies that contain or lead to inheriting that role.
select
xxen_util.meaning(connect_by_root x.category_lookup_code,'UMX_CATEGORY_LOOKUP',0) category,
lpad(' ',2*(level-1))||level level_,
lpad(' ',2*(level-1))||wr.display_name role_name,
lpad(' ',2*(level-1))||x.role role_code,
fav.application_name application,
xxen_util.meaning(connect_by_root x.category_lookup_code,'UMX_CATEGORY_LOOKUP',0)||sys_connect_by_path(x.role,' > ') role_path
from
(
select urcv.category_lookup_code, null parent_role, urcv.wf_role_name role from umx_role_categories_v urcv union all
select null category_lookup_code, wrh.sub_name parent_role, wrh.super_name role from wf_role_hierarchies wrh where wrh.enabled_flag='Y'
) x,
wf_roles wr,
fnd_application_vl fav
where
x.role=wr.name and
wr.owner_tag=fav.application_short_name(+)
connect by
prior x.role=x.parent_role
start with
1=1 and
x.parent_role is null
order by
role_path
Parameter NameSQL textValidation
Role Name
x.role in (
select
y.role
from
(
select urcv.category_lookup_code, null parent_role, urcv.wf_role_name role from umx_role_categories_v urcv union all
select null category_lookup_code, wrh.sub_name parent_role, wrh.super_name role from wf_role_hierarchies wrh where wrh.enabled_flag='Y'
) y
where
y.parent_role is null
connect by
prior y.parent_role=y.role
start with
y.role in (
select
wlr.name
from
wf_local_roles wlr,
wf_local_roles_tl wlrt,
fnd_responsibility_vl frv
where
decode(wlr.orig_system,'FND_RESP',frv.responsibility_name,nvl(wlrt.display_name,wlr.display_name))=:role_name and
wlr.orig_system in ('UMX','FND_RESP') and
wlr.name=wlrt.name(+) and
wlr.orig_system=wlrt.orig_system(+) and
wlr.orig_system_id=wlrt.orig_system_id(+) and
wlrt.language(+)=userenv('lang') and
wlr.partition_id=wlrt.partition_id(+) and
wlr.orig_system_id=frv.responsibility_id(+)
)
)
LOV
Role Code
x.role in (
select
y.role
from
(
select urcv.category_lookup_code, null parent_role, urcv.wf_role_name role from umx_role_categories_v urcv union all
select null category_lookup_code, wrh.sub_name parent_role, wrh.super_name role from wf_role_hierarchies wrh where wrh.enabled_flag='Y'
) y
where
y.parent_role is null
connect by
prior y.parent_role=y.role
start with
y.role=:role_code
)
LOV
Download
Related Blitz Reports
FND W3H_Role_Objects FND User Roles 11i FND User Roles FND Roles GCS Consolidation Entries Writeback FND W3H_Role_Users FND W3H_User_Roles FND W3H_Role_Functions AR Customer Upload
Blitz Report™