HR_TREEBLD Process Ends in No Success with Error: "ORA-01427: Single-row Subquery Returns More than One Row" (Doc ID 2001462.1)

Last updated on FEBRUARY 14, 2017

Applies to:

PeopleSoft Enterprise HCM Human Resources - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms



Application Engine process HR_TREEBLD fails with the following error:
  
ERROR
 
File: /vob/peopletools/src/psappeng/aedebug.hSQL error. Stmt #: 1623 Error Position: 44 Return: 1427 - ORA-01427: single-row subquery returns more than one row
Failed SQL stmt: UPDATE PS_HR_JTR8_TAO SET SUPERVISOR_ID = ( SELECT DISTINCT A.EMPLID FROM PS_HR_JTR8_TAO A , PS_HR_DIRECT_REP_2 DR WHERE A.PROCESS_INSTANCE = 712108 AND A.EMPLID = DR.SUPERVISOR_ID AND DR.DRILL_DOWN_FLAG = 'Y' AND DR.HR_DR_LEVEL = 1 AND DR.EMPLID = PS_HR_JTR8_TAO.EMPLID AND DR.EMPL_RCD = PS_HR_JTR8_TAO.EMPL_RCD AND A.TREE_LEVEL_NUM = PS_HR_JTR8_TAO.TREE_LEVEL_NUM-1), SUPERVISOR_ERN = ( SELECT MIN(A.EMPL_RCD) FROM PS_HR_JTR8_TAO A , PS_HR_DIRECT_REP_2 DR WHERE A.PROCESS_INSTANCE = 712108 AND A.EMPLID = DR.SUPERVISOR_ID AND DR.DRILL_DOWN_FLAG = 'Y' AND DR.HR_DR_LEVEL = 1 AND DR.EMPLID = PS_HR_JTR8_TAO.EMPLID AND DR.EMPL_RCD = PS_HR_JTR8_TAO.EMPL_RCD AND A.TREE_LEVEL_NUM = PS_HR_JTR8_TAO.TREE_LEVEL_NUM-1), PARENT_NODE_NAME = ' ' WHERE PROCESS_INSTANCE = 712108 AND TREE_LEVEL_NUM > 1 AND EXISTS ( SELECT 'X' FROM PS_HR_JTR8_TAO A , PS_HR_DIRECT_REP_2 DR WHERE A.PROCESS_INSTANCE = 712108 AND A.EMPLID = DR.SUPERVISOR_ID AND DR.DRILL_DOWN_FLAG = 'Y' AND DR.HR_DR_LEVEL = 1 AND DR.EMPLID = PS_HR_JTR8_TAO.EMPLID AND DR.EMPL_RCD = PS_HR_JTR8_TAO.EMPL_RCD AND A.TREE_LEVEL_NUM = PS_HR_JTR8_TAO.TREE_LEVEL_NUM-1)


REPLICATION STEPS:

1. Set Up HCM > Common Definitions > Org Chart Viewer > Chart and Profile Settings (Choose and save settings.)
2. Set Up HCM > Common Definitions > Org Chart Viewer > Chart and Profile Field Map (Choose and save field mapping.)
3. Set Up HCM > Common Definitions > Direct Reports for Managers > Direct Reports Table Build.  (Run HR_DRPT_BLD in incremental mode)
4. Set Up HCM > Common Definitions > Org Chart Viewer > Tree Builder Run Control. (Run HR_TREEBLD  using Build Action "Create/Replace Tree")
5. PeopleTools > Process Scheduler > Process Monitor.  Select the HR_TREEBLD process and review message log to find the above error.

Due to this issue, users cannot view organization charts.

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