Scribd
Upload
24 views2 pages

Fusion User Roles SQL Query

The document provides SQL queries to extract user and role information from Oracle Fusion. It includes three main queries: the first retrieves user details along with their roles, the second fetches role descriptions, and the third combines user and role data with additional attributes. The queries utilize various tables such as per_users, per_user_roles, and per_roles_dn to gather comprehensive user-role information.

Uploaded by

satish1981
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
24 views2 pages

Fusion User Roles SQL Query

The document provides SQL queries to extract user and role information from Oracle Fusion. It includes three main queries: the first retrieves user details along with their roles, the second fetches role descriptions, and the third combines user and role data with additional attributes. The queries utilize various tables such as per_users, per_user_roles, and per_roles_dn to gather comprehensive user-role information.

Uploaded by

satish1981
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Fusion User Roles SQL Query & Tables

Detail SQL Query to Extract Oracle Fusion User & Roles


Query 1:-
select [Link],
a1.ACTIVE_FLAG,
a1.CREDENTIALS_EMAIL_SENT,
a2.START_DATE,USER_ROLE_ID,
a3.ROLE_ID,
a3.ROLE_GUID,
a3.ABSTRACT_ROLE,
a3.JOB_ROLE,
a3.DATA_ROLE,
a3.ROLE_COMMON_NAME
from per_users a1, per_user_roles a2,per_roles_dn a3
where a1.user_id=a2.USER_ID
and a2.ROLE_ID=a3.ROLE_ID
and a2.ROLE_GUID=a3.ROLE_GUID

Query 2:-Role Details

SELECT prdt.role_id, prdt.role_name,


[Link] RoleDescription,
prdt.source_lang
FROM per_roles_dn_tl prdt

Query 3:-

SELECT pu.user_id,
[Link],
ppnf.full_name,
prdt.role_id,
prdt.role_name,
prd.role_common_name,
[Link],
TO_CHAR (pur.start_date, 'DD-MON-YYYY') role_start_date,
TO_CHAR (pur.end_date, 'DD-MON-YYYY') role_end_date,
prd.abstract_role,
prd.job_role,
prd.data_role,
prd.duty_role,
prd.active_flag
FROM per_user_roles pur,
per_users pu,
per_roles_dn_tl prdt,
per_roles_dn prd,
per_person_names_f ppnf
Co WHERE 1 = 1
nfi AND pu.user_id = pur.user_id
de AND prdt.role_id = pur.role_id
nti AND [Link] = USERENV ('lang')
AND prdt.role_id = prd.role_id
al – AND NVL ([Link], 'N') = 'N'
Ora
cle
Int
ern
al
-- AND [Link] =:p_username
AND ppnf.person_id = pu.person_id
AND ppnf.name_type = 'GLOBAL'
AND pu.active_flag = 'Y'
AND NVL (pu.start_date, SYSDATE) <= SYSDATE
AND NVL (pu.end_date, SYSDATE) >= SYSDATE
ORDER BY [Link], prdt.role_name

Co
nfi
de
nti
al –
Ora
cle
Int
ern
al

You might also like