Act as Proxy search not working when using Custom Criteria in Person Security Profile
We are using a custom criteria in person security profile for our HRBPs.
The SQL builds the hierarchy stored in the External Data page from the manager up to the highest HRBP and determines the access from there.
This is working in Act as Proxy when searching using person number but when using name and email address, the page errors out without providing any results. How can we adjust this SQL to address the issue and speed up the processing time?
EXISTS
(
select 1 from
(select person_id FROM CMP_EXTERNAL_WORKER_DATA where RECORD_TYPE_CD ='CMP_HRBP_ACCESS' start with VALUE2 =(SELECT PAPF.PERSON_NUMBER FROM PER_ALL_PEOPLE_F PAPF WHERE PAPF.PERSON_ID=(SELECT HRC_SESSION_UTIL.GET_USER_PERSONID FROM DUAL) AND (SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE) ) connect by nocycle prior VALUE1 = VALUE2 and prior RECORD_TYPE_CD = RECORD_TYPE_CD) EXTDATA