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