HR_TREEBLD Process Goes To No Success With A Unique Constraint Error

(Doc ID 2339545.1)

Last updated on DECEMBER 15, 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

On : 9.2 version, Other

HR_TREEBLD process goes to No Success with the following error:

Stmt #: 1723 Error Position: 0 Return: 805 - ORA-00001: unique constraint (SYSADM.PS_HR_JTR11_TAO) violated

Failed SQL stmt: INSERT INTO PS_HR_JTR11_TAO (PROCESS_INSTANCE, TREE_NODE, EMPLID, EMPL_RCD, SUPERVISOR_ID, SUPERVISOR_ERN, PARENT_NODE_NAME, TREE_LEVEL_NUM) SELECT A.PROCESS_INSTANCE, ' ', A.EMPLID, A.EMPL_RCD, A.SUPERVISOR_ID, B.EMPL_RCD, B.TREE_NODE, A.TREE_LEVEL_NUM FROM PS_HR_JTR7_TAO A, PS_HR_JTR7_TAO B WHERE A.PROCESS_INSTANCE = 4876411 AND B.PROCESS_INSTANCE = 4876411 AND A.SUPERVISOR_ID = B.EMPLID AND A.SUPERVISOR_ERN <> B.EMPL_RCD AND A.TREE_LEVEL_NUM-1 = B.TREE_LEVEL_NUM

The issue can be reproduced at will with the following steps:
1. Navigate to Set Up HRMS > Common Definitions > Org Chart Viewer > Tree Builder Run Control and select:
2. Go to Process Monitor, and notice the HR_TREEBLD runs to No Success
3. Open the corresponding Log File and note the above error


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