Custom Criteria for HR Role Visibility (Work Location + Department Tree) Not Working

Hello Community,

We have created an HR role with a visibility scope based on both the Work Location and the Department Tree, using custom criteria. Below is the query we used:

(
EXISTS (
SELECT 1
FROM PER_ALL_ASSIGNMENTS_M ASG,
PER_PERIODS_OF_SERVICE PS
WHERE ASG.ASSIGNMENT_TYPE IN ('E', 'C', 'P', 'N')
AND ASG.EFFECTIVE_LATEST_CHANGE = 'Y'
AND TRUNC(SYSDATE) < ASG.EFFECTIVE_END_DATE
AND PS.PERIOD_OF_SERVICE_ID = ASG.PERIOD_OF_SERVICE_ID
AND (
ASG.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE', 'SUSPENDED')
OR (
ASG.ASSIGNMENT_STATUS_TYPE IN ('INACTIVE')
AND NOT EXISTS (
SELECT 1
FROM PER_ALL_ASSIGNMENTS_M EXASG