ORA-28113: Policy Predicate Error While Searching For A Person Record (Doc ID 2221375.1)

Last updated on FEBRUARY 26, 2017

Applies to:

Oracle Fusion Global Human Resources Cloud Service - Version 11.1.11.1.0 and later
Information in this document applies to any platform.

Symptoms

On : 11.1.11.1.0 version, Global Human Resources

When attempting to search for a person in person management UI,
the following error occurs.

ERROR
-----------------------
ORA - 28113 : Policy Predicate Error

Following custom sql is being used -

EXISTS
(SELECT 1 FROM PER_ALL_ASSIGNMENTS_M ASG,PER_PERIODS_OF_SERVICE PS,PER_ASG_RESPONSIBILITIES RES
WHERE ASG.ASSIGNMENT_TYPE IN('E','C','N','P')
AND ASG.EFFECTIVE_LATEST_CHANGE='Y'
AND SYSDATE < ASG.EFFECTIVE_END_DATE
AND ASG.PERIOD_OF_SERVICE_ID=PS.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
  WHERE EXASG.ASSIGNMENT_TYPE IN('E','C','N','P')
  AND EXASG.EFFECTIVE_LATEST_CHANGE = 'Y'
  AND EXASG.PERSON_ID = ASG.PERSON_ID
  AND SYSDATE < 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)
  ))
AND SYSDATE BETWEEN RES.START_DATE AND NVL(RES.END_DATE,SYSDATE)
AND ASG.PERSON_ID=&TABLE_ALIAS.PERSON_ID
AND RES.PERSON_ID=(SELECT HRC_SESSION_UTIL.GET_USER_PERSONID FROM DUAL)
AND RES.RESPONSIBILITY_TYPE='CWT_HR_SERVICES_APAC'
AND (RES.BUSINESS_UNIT_ID IS NULL or ASG.BUSINESS_UNIT_ID=RES.BUSINESS_UNIT_ID)
AND (RES.COUNTRY IS NULL or ASG.LEGISLATION_CODE = RES.COUNTRY)
AND (RES.ORGANIZATION_ID IS NULL or ASG.ORGANIZATION_ID=RES.ORGANIZATION_ID)
AND (RES.GRADE_ID IS NULL or ASG.GRADE_ID=RES.GRADE_ID)
AND (RES.JOB_ID IS NULL or ASG.JOB_ID=RES.JOB_ID)
AND (RES.LEGAL_ENTITY_ID IS NULL or ASG.LEGAL_ENTITY_ID=RES.LEGAL_ENTITY_ID)
AND (RES.LOCATION_ID IS NULL or ASG.LOCATION_ID=RES.LOCATION_ID)
AND (RES.POSITION_ID IS NULL or ASG.POSITION_ID=RES.POSITION_ID)
AND
(
ASG.ORGANIZATION_ID NOT IN ('100000016385163','100000016411248','100000016411249','100000016385164','100000008472635')
  AND
  ASG.LEGISLATION_CODE NOT IN ('DE','AT','AR','CL','PE','CO','CR','MX','BR','US','CA','BE','DK','FI','FR','IE','IT','LU','NL','PL','ES','SE','CH','GB')
)
)


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Navigator > Person Management
2. Search using keywords and error occurs


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms