Person Security Profile with Custom Crtieria not working

Summary:

Below is the custom criteria of a person sec profile included in the data role for 'Human Resource Specialist'

&TABLE_ALIAS.PERSON_ID IN (SELECT A.PERSON_ID FROM PER_ASSIGNMENTS_F A, HR_ALL_POSITIONS B
WHERE A.POSITION_ID = B.POSITION_ID
AND B.NAME in (
'a specific position name added here'
))

No other conditions added.

When run in SQL interface, the above SQL is resulting with the required rows; also, the person sec profile preview allows to search only the people in this position

The data role added with above person sec profile, added to a person with no other role than EMployee role is allowing to access all people through Person Managmenet (Classic Page) and through RUI Page.