Oracle-EBS-SQL/FND User Roles 11i/FND User Roles 11i.sql at master · Enginatics/Oracle-EBS-SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
/*************************************************************************/
/*                                                                       */
/*                       (c) 2010-2024 Enginatics GmbH                   */
/*                              www.enginatics.com                       */
/*                                                                       */
/*************************************************************************/
-- Report Name: FND User Roles 11i
-- Description: Report for User Management (UMX) roles and their assigned users to manage role-based access control (RBAC)
-- Excel Examle Output: https://www.enginatics.com/example/fnd-user-roles-11i/
-- Library Link: https://www.enginatics.com/reports/fnd-user-roles-11i/
-- Run Report: https://demo.enginatics.com/

select
x.*
from
(
select
xxen_util.user_name(wlur.user_name) user_name,
decode(wlur.role_orig_system,'FND_RESP',frv.responsibility_name,nvl(wlrt.display_name,wlr.display_name)) role_name,
wlr.name role_code,
decode(wlur.role_orig_system,'FND_RESP',fnd_message.get_string('FND','W3H_RESPONSIBILITY_ROLETYPE'),fnd_message.get_string('FND','W3H_ROLE_ROLETYPE')) role_type,
decode(wlur.assignment_type,'D',fnd_message.get_string('FND','W3H_DIRECT_ASSIGNMENTTYPE'),'I',fnd_message.get_string('FND', 'W3H_INDIRECT_ASSIGNMENTTYPE'),'B',fnd_message.get_string('FND','W3H_BOTH_ASSIGNMENTTYPE')) assignment_type,
(
select
decode(wlr.orig_system,'FND_RESP',frv.responsibility_name,nvl(wlrt.display_name,wlr.display_name)) assigning_role_name
from
wf_local_roles wlr0,
wf_local_roles_tl wlrt0,
fnd_responsibility_vl frv0
where
nullif(wura.assigning_role,wlr.name)=wlr0.name and
wlr0.orig_system in ('UMX','FND_RESP') and
wlr0.name=wlrt0.name(+) and
wlr0.orig_system=wlrt0.orig_system(+) and
wlr0.orig_system_id=wlrt0.orig_system_id(+) and
wlrt0.language(+)=userenv('lang') and
wlr0.partition_id=wlrt0.partition_id(+) and
wlr0.orig_system_id=frv0.responsibility_id(+)
) assigning_role_name,
nullif(wura.assigning_role,wlr.name) assigning_role_code,
wura.effective_start_date,
wura.effective_end_date,
wura.role_start_date,
wura.role_end_date,
xxen_util.user_name(wura.created_by) assigned_by,
wura.assignment_reason
from
wf_local_user_roles wlur,
wf_user_role_assignments wura,
fnd_responsibility_vl frv,
wf_local_roles wlr,
wf_local_roles_tl wlrt
where
1=1 and
wlur.role_orig_system in ('UMX','FND_RESP') and
wlur.role_orig_system_id=frv.responsibility_id(+) and
wlur.role_name=wlr.name and
wlr.orig_system in ('UMX','FND_RESP') and
wlur.user_name=wura.user_name and
wlur.role_name=wura.role_name 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(+)
) x
where
2=2
order by
x.user_name,
x.role_name