Worker API compatible custom SQL based person security profile
Summary:
I am using the below custom SQL to get all the employees satisfying the criteria. The data role is created using this person security profile. I am using the data role to restrict API user access. The API seems to error out when a blank search is done using /hcmRestApi/resources/11.13.18.05/workers/. But the search with person number like this returns data /hcmRestApi/resources/11.13.18.05/workers?q=PersonNumber>=600000
EXISTS
(SELECT 1
FROM PER_ALL_ASSIGNMENTS_M ASG,PER_ALL_PEOPLE_F PTV
WHERE ASG.ASSIGNMENT_ID=&TABLE_ALIAS.ASSIGNMENT_ID
AND ASG.PERSON_ID =PTV.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN LEAST(TRUNC(SYSDATE),ASG.EFFECTIVE_START_DATE) AND ASG.EFFECTIVE_END_DATE