BIP – Query to find active user accounts post termination

Once a worker record is terminated, it is expected that the associated user account will get inactive. However, it depends upon the configuration on Enterprise Level and auto provisioning rules setup. If there is even on role assigned to user name, the user account will not get terminated in HCM.

To help the business to get a list of active user accounts even if the associated worker record is terminated, below query is created:

SELECT DISTINCT papf.person_number
      ,ppos.actual_termination_date 
  FROM PER_ALL_PEOPLE_F papf
      ,PER_PERIODS_OF_SERVICE ppos
	  ,PER_USERS pu
	  ,PER_USER_ROLES pur
 WHERE papf.person_id = ppos.person_id
   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
   AND papf.person_id = pu.person_id
   AND pu.user_id = pur.user_id
   AND ppos.actual_termination_date IS NOT NULL
   AND ppos.actual_termination_date < TRUNC(SYSDATE) /*Check only for past dated terminations*/
   AND NOT EXISTS (SELECT 1 FROM PER_PERIODS_OF_SERVICE ppos2
                    WHERE ppos2.person_id = ppos.person_id
					  AND ppos2.actual_termination_date IS NULL) 
 ORDER BY 1,2