Same Competence Requirements with Multiple Grades: Update Details Competency in Appraisals erroring with ORA-01427: single-row subquery returns more than one row

(Doc ID 2028658.1)

Last updated on MARCH 08, 2017

Applies to:

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

Symptoms

On : 12.1.3 version, Appraisals

R12.HR_PF.B.delta.7

Employee enters competencies ratings in a performance appraisal
then shares with Main Appraiser.
When the manager is updating the appraisal,
when clicking the Update Details icon available for a competency,
the below error occurs.

It's happening only for competencies set with multiple Grades at Competence
Requirement level. 


ERROR
-----------------------
The error coming:
Exception Details
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT pc.name
  ,pc.competence_alias alias
  ,pc.behavioural_indicator behav_ind
  ,pc.description
  ,hrl1.meaning structure_type
  ,hrl2.meaning global
  ,nvl2 (prlt1.name
  ,prlb1.step_value
  || '-'
  || prlt1.name
  ,prlb1.step_value) min_level
  ,nvl2 (prlt2.name
  ,prlb2.step_value
  || '-'
  || prlt2.name
  ,prlb2.step_value) max_level
  ,pce.competence_id
  ,pc.competence_cluster
  ,pc.unit_standard_id
  ,hr_general.decode_lookup ('PER_COMPETENCE_CLUSTER'
  ,nvl (pc.competence_cluster
  ,'NORMAL_COMPETENCE')) comp_cluster
  ,hr_general.decode_lookup ('PER_QUAL_FWK_FIELD'
  ,pc.field) field
  ,hr_general.decode_lookup ('PER_QUAL_FWK_SUB_FIELD'
  ,pc.sub_field) sub_field
  ,hr_general.decode_lookup ('PER_QUAL_FWK_LEVEL_TYPE'
  ,pc.level_type) level_type
  ,hr_general.decode_lookup ('PER_QUAL_FWK_LEVEL'
  ,pc.level_number) level_number
  ,hr_general.decode_lookup ('PER_QUAL_FWK_CREDIT_TYPE'
  ,pc.credit_type) credit_type
  ,pc.credits credits
FROM per_competence_elements pce
  ,per_competences_vl pc
  ,hr_lookups hrl1
  ,hr_lookups hrl2
  ,per_rating_levels prlb1
  ,per_rating_levels_tl prlt1
  ,per_rating_levels prlb2
  ,per_rating_levels_tl prlt2
WHERE competence_element_id =
  (
  SELECT decode (type
  ,'ASSESSMENT'
  ,nvl (
  (
  SELECT req.competence_element_id parent_competence_element_id
  FROM per_assessments pa
  ,per_assignments_f paaf
  ,per_competence_elements req
  WHERE pce.assessment_id = pa.assessment_id
  AND pa.person_id = paaf.person_id
  AND paaf.primary_flag = 'Y'
  AND trunc (sysdate) BETWEEN paaf.effective_start_date
  AND paaf.effective_end_date
  AND req.competence_id = pce.competence_id
  AND req.job_id = paaf.job_id
AND trunc(sysdate) between nvl(req.effective_date_from,trunc(sysdate)) and nvl(req.effective_date_to,trunc(sysdate))
  )
  ,nvl (parent_competence_element_id
  ,competence_element_id))
  ,competence_element_id) competence_element_id
  FROM per_competence_elements pce
  WHERE competence_element_id = :1
  )
AND pce.competence_id = pc.competence_id
AND hrl1.lookup_type = 'STRUCTURE_TYPE'
AND hrl1.lookup_code = decode (pce.type
  ,'ASSESSMENT'
  ,'ADD'
  ,'ASSESSMENT_COMPETENCE'
  ,'AST'
  ,'REQUIREMENT'
  ,nvl2 (enterprise_id
  ,'BUS'
  ,nvl2 (organization_id
  ,'ORG'
  ,nvl2 (job_id
  ,'JOB'
  ,nvl2 (position_id
  ,'POS'
  ,'ADD'))))
  ,'ADD')
AND hrl2.lookup_type = 'YES_NO'
AND hrl2.lookup_code = nvl2 (pc.business_group_id
  ,'N'
  ,'Y')
AND pce.proficiency_level_id = prlb1.rating_level_id (+)
AND pce.proficiency_level_id = prlt1.rating_level_id (+)
AND prlt1.language (+) = userenv ('LANG')
AND pce.high_proficiency_level_id = prlb2.rating_level_id (+)
AND pce.high_proficiency_level_id = prlt2.rating_level_id (+)
AND prlt2.language (+) = userenv ('LANG')
UNION ALL
SELECT pc.name
  ,pc.competence_alias alias
  ,pc.behavioural_indicator behav_ind
  ,pc.description
  ,hrl2.meaning structure_type
  ,hrl1.meaning global
  ,NULL min_level
  ,NULL max_level
  ,to_number (NULL) competence_id
  ,pc.competence_cluster
  ,pc.unit_standard_id
  ,hr_general.decode_lookup ('PER_COMPETENCE_CLUSTER'
  ,nvl (pc.competence_cluster
  ,'NORMAL_COMPETENCE')) comp_cluster
  ,hr_general.decode_lookup ('PER_QUAL_FWK_FIELD'
  ,pc.field) field
  ,hr_general.decode_lookup ('PER_QUAL_FWK_SUB_FIELD'
  ,pc.sub_field) sub_field
  ,hr_general.decode_lookup ('PER_QUAL_FWK_LEVEL_TYPE'
  ,pc.level_type) level_type
  ,hr_general.decode_lookup ('PER_QUAL_FWK_LEVEL'
  ,pc.level_number) level_number
  ,hr_general.decode_lookup ('PER_QUAL_FWK_CREDIT_TYPE'
  ,pc.credit_type) credit_type
  ,pc.credits credits
FROM per_competences_vl pc
  ,hr_lookups hrl1
  ,hr_lookups hrl2
WHERE pc.competence_id = :2
AND hrl1.lookup_type = 'YES_NO'
AND hrl1.lookup_code = nvl2 (pc.business_group_id
  ,'N'
  ,'Y')
AND hrl2.lookup_type = 'STRUCTURE_TYPE'
AND hrl2.lookup_code = 'ADD'
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)

....

## Detail 0 ##
java.sql.SQLException: ORA-01427: single-row subquery returns more than one row

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)


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

A. Prequisite:

1. Create an appraisal template using a competency template with Include Job
    Competencies checked
2. Set Competence Requirements:
    At job level, for one job, add the same competence several times with
    different Grades (Valid grades may have to be added at job level)
3. Assign a worker to this job and to one grade used at competence requirements level.
4. Create and publish a plan including the worker and with your appraisal template starting by employees.


B. Reproduction of the issue

1. Log in as the employee
2. Employee Self-Service
3. Performance Management
4. See your plan
5. Manage appraisal task > Go to Task
6. See one planned appraisal. Click Update
7. See the competencies automatically created
8. Enter ratings
8. Submit to Main appraiser
9. Log in as the Manager
10.Manager Self-Service
11.Appraisal in Progress.
    See the Ongoing with Main Appraiser appraisal of your worker.
12.Click Appraise
13. Update Appraisal button
14. Click update details for the competence set with multiple grades at competence requirements level
15. The error is coming


BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Managers are unable to update competencies in appraisals.

RECENT CHANGES
-----------------------
Patch 20390905:R12.PER.B.

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