SQL Query to Retrieve Only Active Employees in Oracle HCM

EXISTS (
  SELECT 1
  FROM PER_ALL_ASSIGNMENTS_M ASG,
       PER_PERIODS_OF_SERVICE PS
  WHERE ASG.ASSIGNMENT_TYPE IN ('E', 'C', 'N', 'P')
    AND ASG.EFFECTIVE_LATEST_CHANGE = 'Y'
    AND SYSDATE BETWEEN LEAST(SYSDATE, ASG.EFFECTIVE_START_DATE) AND ASG.EFFECTIVE_END_DATE
    AND ASG.ASSIGNMENT_ID = &TABLE_ALIAS.ASSIGNMENT_ID
    AND PS.PERIOD_OF_SERVICE_ID = ASG.PERIOD_OF_SERVICE_ID
    AND (
         ASG.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE', 'SUSPENDED')
         OR (
              ASG.ASSIGNMENT_STATUS_TYPE = 'INACTIVE'
              AND NOT EXISTS (
                SELECT 1
                FROM PER_ALL_ASSIGNMENTS_M EXASG
                WHERE EXASG.ASSIGNMENT_TYPE IN ('E', 'C', 'N', 'P')
                  AND EXASG.EFFECTIVE_LATEST_CHANGE = 'Y'
                  AND EXASG.PERSON_ID = ASG.PERSON_ID
                  AND SYSDATE BETWEEN LEAST(SYSDATE, EXASG.EFFECTIVE_START_DATE) AND EXASG.EFFECTIVE_END_DATE
                  AND EXASG.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE', 'SUSPENDED')
              )
              AND PS.ACTUAL_TERMINATION_DATE = (
                SELECT MAX(ALLPS.ACTUAL_TERMINATION_DATE)
                FROM PER_PERIODS_OF_SERVICE ALLPS
                WHERE ALLPS.PERSON_ID = ASG.PERSON_ID
                  AND ALLPS.ACTUAL_TERMINATION_DATE IS NOT NULL
              )
         )
    )
)

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.