HR Professional V4.0 --> Appraisals Function, Error: Java Exception "JBO-27122: SQL error during statement preparation" (Doc ID 2115134.1)

Last updated on MARCH 06, 2017

Applies to:

Oracle Performance Management - Version 12.1 HRMS RUP5 and later
Information in this document applies to any platform.

Symptoms

On 12.1 HRMS RUP 5 version of Application, In the Appraisals Page.

When attempting to act on the Appraisal, the following error occurs.

'You have encountered an unexpected error. Please contact the System Administrator for assistance.
Click here for exception details.'

Error Stack:

Exception Details.
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT pa.APPRAISAL_ID,
decode(nvl(pa.appraisal_system_status,'OLDAPPR'),'COMPLETED','DisableUpdate','OLDAPPR','DisableUpdate','DELETED','DisableUpdate','EnableUpdate') AS UPD_STATUS,
  pa.BUSINESS_GROUP_ID,
  pa.APPRAISAL_DATE,
  pa.TYPE,
  pa.STATUS,
  ppf.EFFECTIVE_START_DATE,
  ppf.EFFECTIVE_END_DATE,
  decode(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'G',ppf.global_name,ppf.local_name) FULL_NAME,
  ppf.PERSON_ID,
  pa.SYSTEM_TYPE,
  pa.GROUP_INITIATOR_ID,
  pa.APPRAISEE_PERSON_ID,
  pa.APPRAISER_PERSON_ID,
  'EnableDelete' AS DEL_STATUS,
  h2.MEANING,
  h2.LOOKUP_TYPE,
  h2.LOOKUP_CODE,
  pa.APPRAISAL_PERIOD_START_DATE,
  pa.APPRAISAL_PERIOD_END_DATE,
  decode(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'G',ppf1.global_name,ppf1.local_name) AS MAIN_AP_NAME,
  ppf1.PERSON_ID AS PERSON_ID1,
  ppf1.EFFECTIVE_START_DATE AS EFFECTIVE_START_DATE1,
  ppf1.EFFECTIVE_END_DATE AS EFFECTIVE_END_DATE1,
  nvl(APPRAISAL_SYSTEM_STATUS, decode(OPEN,'N','COMPLETED','OLDAPPR')) AS APPRAISAL_SYSTEM_STATUS,
  decode(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'G',ppf2.global_name,ppf2.local_name) AS APPRAISER_NAME,
  ppf2.PERSON_ID AS PERSON_ID2,
  ppf2.EFFECTIVE_START_DATE AS EFFECTIVE_START_DATE2,
  ppf2.EFFECTIVE_END_DATE AS EFFECTIVE_END_DATE2,
  h1.MEANING AS APPR_STATUS_MEANING,
  pa.PLAN_ID
FROM PER_APPRAISALS pa, PER_ALL_PEOPLE_F ppf, HR_LOOKUPS h2, PER_ALL_PEOPLE_F ppf1, PER_ALL_PEOPLE_F ppf2, HR_LOOKUPS h1
WHERE ppf.person_id = pa.appraisee_person_id AND (sysdate between ppf.effective_start_date and ppf.effective_end_date) AND pa.appraisee_person_id = :1 AND h2.lookup_code = nvl(pa.appraisal_system_status,decode(pa.open,'N','COMPLETED','ONGOING')) and h2.lookup_type='APPRAISAL_SYSTEM_STATUS' AND PA.MAIN_APPRAISER_ID = PPF1.PERSON_ID(+) AND TRUNC(SYSDATE) BETWEEN PPF1.EFFECTIVE_START_DATE(+) AND PPF1.EFFECTIVE_END_DATE(+) AND PA.APPRAISER_PERSON_ID = PPF2.PERSON_ID AND TRUNC(SYSDATE) BETWEEN PPF2.EFFECTIVE_START_DATE AND PPF2.EFFECTIVE_END_DATE
AND nvl(pa.system_type,'OLD') <> 'SELF' AND 'APPRAISAL_TYPE' = h1.lookup_type(+) and pa.type = h1.lookup_code(+)) QRSLT WHERE (APPRAISAL_SYSTEM_STATUS = 'COMPLETED')
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:886)
at oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:1009)

...................................

## Detail 0 ##
java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5168)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8098)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8034)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8767)
at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8748)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:230)
at oracle.jbo.server.BaseSQLBuilderImpl.bindParamValue(BaseSQLBuilderImpl.java:1847)
at oracle.jbo.server.OracleSQLBuilderImpl.bindParamValue(OracleSQLBuilderImpl.java:3893)
at oracle.jbo.server.BaseSQLBuilderImpl.bindParametersForStmt(BaseSQLBuilderImpl.java:3335)
at oracle.jbo.server.ViewObjectImpl.bindParametersForCollection(ViewObjectImpl.java:13827)


STEPS

The issue can be reproduced at will with the following steps:

1. Logged into 'HR Professional V4.0' Responsibility.

2. Navigated to Function 'Appraisals'.

3. In the launched page clicked on the 'Action' Icon against the employee and got the below error message.

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