Introduction:
This blog has the SQL query that can be used to pull the data for the oracle cloud users having what roles & for which BU it is assigned if it is not an employee.
Cause of the issue:
Business wants this report for Audit tracking purpose.
How do we solve:
Create a BI report in fusion using below SQL query to extract the details.
–User is not as employee
SELECT DISTINCT
pu.username username,
prdt.role_name rname,
prd.role_common_name rcode,
regexp_substr(fuv.bu_name, ‘[^ ]+’, 1, 2),
pur.start_date AS user_resp_start_date,
fnd.last_connect1 user_last_logon
FROM
per_user_roles pur,
per_users pu,
per_roles_dn_vl prdt,
per_roles_dn prd,
fun_user_role_data_asgnmnts role,
fun_all_business_units_v fuv,
(
SELECT
user_guid,
MAX(last_connect) last_connect1
FROM
fnd_sessions
WHERE
1 = 1
GROUP BY
user_guid
) fnd
WHERE
pu.user_id = pur.user_id
AND nvl(pu.start_date, sysdate) <= sysdate
AND nvl(pu.end_date, sysdate) >= sysdate
AND prdt.role_id = pur.role_id
AND prdt.role_id = prd.role_id
AND pu.active_flag (+) = ‘Y’
AND pu.username = nvl(:username1, pu.username)
AND pu.user_guid = fnd.user_guid (+)
AND pu.person_id IS NULL
AND pur.active_flag = ‘Y’
AND pu.suspended = ‘N’
AND pu.user_guid = role.user_guid
AND prdt.role_common_name = role.role_name
AND role.org_id = fuv.bu_id
