oracle.apps.fnd.framework.OAException: java.sql.SQLException Error While validating HR_OBJECTIVES_API.UPDATE_OBJECTIVE

(Doc ID 1609324.1)

Last updated on JULY 27, 2017

Applies to:

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

Symptoms

On : 12.1 HRMS RUP4 version, Appraisals in Dev instance.

When attempting to validate validations written for HR_OBJECTIVES_API.UPDATE_OBJECTIVE by updating objectives in appraisals by employees, application throws up OA Exception if two validations are tested back to back in the same session.

Exception Details.
oracle.apps.fnd.framework.OAException: java.sql.SQLException: Invalid column index
at oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:996)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:211)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:133)
at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:797)
  ...
  ...
## Detail 0 ##
java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.OracleCallableStatement.getInt(OracleCallableStatement.java:1476)
at oracle.jdbc.driver.OracleCallableStatementWrapper.getInt(OracleCallableStatementWrapper.java:783)
at oracle.apps.per.schema.server.AssessmentEOImpl.deleteRow(AssessmentEOImpl.java:544)
at oracle.apps.per.common.server.PerOAPlsqlEntityImpl.deleteRow(PerOAPlsqlEntityImpl.java:431)
at oracle.apps.fnd.framework.server.OAPlsqlEntityImpl.doDML(OAPlsqlEntityImpl.java:258)
at oracle.jbo.server.EntityImpl.postChanges(EntityImpl.java:4509)



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

1. Create and compile the custom package including 2 validations to be hooked to Before Process User Hook for API HR_OBJECTIVES_API.UPDATE_OBJECTIVE.

2. Register the procedure that is written with user hook.

3. Run the pre-processor program which builds the logic to execute the custom validations.

4. Create and publish a plan without 'Objective Setting' phase so that appraisals are created (by Mass Appraisal Creation program).

5. Go into 'Manager Self-Service' responsibility.

6. Navigated to function 'Performance Management'.

7. Select the plan and click on 'Go to Task' for 'Appraisals' process.

8. Next page is 'Appraisals As Main Appraiser'.
    Here select one of the employees and click on 'Appraise'.

9. Next page is 'Update Standard Appraisal: Overview'.
    This shows the personalized page showing the objectives, target value, actual value and comments fields.

10. Here below test cases are to be conducted:
     Validation 1: The Actual Value should not be greater than Target Value.
     Validation 2: The Actual Value cannot be a decimal value.

    Test Case (1):
    In this page, enter 'Actual Value' to be greater than 'Target Value' and click on 'Continue' button so that user hook code fires.
    Now, as expected user hook code fires and page shows the expected validation message.

    Now, on the same page, enter a decimal number to 'Actual Value' field and click on 'Continue' button. Now, instead of API User Hook exception,
    the above OA page exception is shown.

    Test Case (2):
    Log out and log in again.
    Navigate back to 'Update Standard Appraisal: Overview' page.
    Now do the validations in reverse order.
  
    Enter a decimal value in the 'Actual Value' field and click on 'Continue' field. The User Hook code fires as expected.
    Now, on the same page, enter 'Actual Value' to be greater than 'Target Value'. Now, you see that the page shows OA exception.

    Test Case (3):
    Log in again.

    Navigate back to 'Update Standard Appraisal: Overview' page.
    Check any of the validations and see that it works.

    Next log out and log in back.
    Now, test the other validation and application DOES NOT throw any OA page exception and User Hook code throws.



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