HC_COMP_COMPENSATION_HISTORY SES Index Throwing Unique Constraint Error

(Doc ID 2206158.1)

Last updated on MARCH 10, 2017

Applies to:

PeopleSoft Enterprise HCM Human Resources - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

On PeopleSoft HCM 9.2 : SES / Indexing

PTSF_GENFEED goes to No Success when building the HC_COMP_COMPENSATION_HISTORY Index with the following error:

File: /vob/peopletools/src/psappeng/aedebug.hSQL error. Stmt #: 1703 Error Position: 0 Return: 805 - ORA-00001: unique constraint (SYSADM.PS_HR_SRCH_DR_5) violated Failed SQL stmt: INSERT INTO PS_HR_SRCH_DR_5 (SUPERVISOR_ID ,SUPERVIS_EMPL_RCD ,EMPLID ,EMPL_RCD ,EM PL_STATUS ,JOB_EFFDT ,POSITION_NBR ,REPORTS_TO ,HR_DR_LEVEL ,SUPERVISOR_FLAG , DRILL_DOWN_FLAG ,HR_S RCH_CTRL_ID ,HR_SRCH_ASSOC_CD , LASTUPDDTTM) SELECT DISTINCT SUPER.SUPERVISOR_ID , SUPER.SUPERVIS_EM PL_RCD , INDIR.EMPLID , INDIR.EMPL_RCD , INDIR.EMPL_STATUS , INDIR.JOB_EFFDT , INDIR.POSITION_NBR , INDIR.REPORTS_TO , INDIR.HR_DR_LEVEL + SUPER.HR_DR_LEVEL , INDIR.SUPERVISOR_FLAG , INDIR.DRILL_DOWN_ FLAG ,1 ,CASE WHEN (INDIR.HR_DR_LEVEL + SUPER.HR_DR_LEVEL )=1 THEN 'DIR' ELSE 'IND' END ,TO_TIMESTAM P(TO_TIMESTAMP('2016-11-17-11.50.40.000000','YYYY-MM-DD-HH24.MI.SS.FF'),'YYYY-MM-DD-HH24.MI.SS.FF')


The issue can be reproduced at will with the following steps:
1. Navigate to Main Menu > PeopleTools > Search Framework > Administration > Schedule Search Index
2. Build the HC_COMP_COMPENSATION_HISTORY Search Index
3. Go to Process Monitor and notice PTSF_GENFEED goes to No Success with 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