Search for:
BIP – Query to get Active Users for terminated employees

Once a worker is terminated in Oracle HCM Cloud, one would expect that associated User account to be inactive as well. For this to happen automatically, autoprovisioning rules should be setup which will remove the associated roles to the terminated worker’s user. In case, there is even a single role attached to the user, the user will show as active in Security Console.

From the backend, Suspended attribute in PER_USERS table is mapped to active checkbox in UI. If the user is active, the suspended flag will hold N value. For inactive users, the value of this attribute will be Y.

You can use below query to get the list of terminated workers for whom the user is still active:

SELECT papf.person_number,pu.username
  FROM per_all_people_f papf
      ,per_all_assignments_m paam
	  ,per_users pu
 WHERE papf.person_id = paam.person_id
   AND paam.effective_latest_change = 'Y'
   AND paam.effective_sequence = 1
   AND paam.assignment_status_type like 'INACTIVE%' 
   AND paam.assignment_type NOT LIKE '%T'
   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date   
   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date   
   AND papf.person_id = pu.person_id
   AND pu.suspended = 'N'
Reports (BIP) – User Role Details
SELECT distinct pu.username
FROM per_users pu
,per_roles_dn prd
,per_user_roles pur
,per_roles_dn_tl prdt
WHERE pu.user_id = pur.user_id
and pur.role_id = prdt.role_id
and pur.role_id = prd.role_id
and prdt.language = 'US'