HC_HR_SUCC_PLAN, HC_HR_CAREER_PLAN, and HC_COMP_COMPENSATION_HISTORY Indexes Failing Due To Unique Constraint (SYSADM.PS_HR_SRCH_CHG_2) Error (Doc ID 2463386.1)

Last updated on DECEMBER 01, 2019

Applies to:

Symptoms

On : 9.2 version, Integration Broker/Messaging

PTSF_GENFEED failing for indexes HC_HR_SUCC_PLAN, HC_HR_CAREER_PLAN, and HC_COMP_COMPENSATION_HISTORY.
 

ERROR
-----------------------
-- 10:57:05.542 .......(HR_SRCH_DR.2ChgIns.Step01) (SQL)
INSERT INTO PS_HR_SRCH_CHG_2 (EMPLID ,EMPL_RCD ,SUPERVISOR_ID ,OLD_SUPER_ID )
SELECT JOB1.EMPLID , JOB1.EMPL_RCD , JOB1.SUPERVISOR_ID , DR.SUPERVISOR_ID
FROM PS_JOB JOB1 , PS_HR_SRCH_DR_2 DR WHERE JOB1.LASTUPDDTTM
>TO_TIMESTAMP(TO_TIMESTAMP('2018-10-11-11.19.00.000000',
'YYYY-MM-DD-HH24.MI.SS.FF'),'YYYY-MM-DD-HH24.MI.SS.FF') AND JOB1.EFFDT=(
SELECT MAX(JOB2.EFFDT) FROM PS_JOB JOB2 WHERE JOB1.EMPLID=JOB2.EMPLID AND
JOB1.EMPL_RCD=JOB2.EMPL_RCD AND JOB2.EFFDT <=
TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')) AND JOB1.EFFSEQ=( SELECT
MAX(JOB3.EFFSEQ) FROM PS_JOB JOB3 WHERE JOB1.EMPLID=JOB3.EMPLID AND
JOB1.EMPL_RCD=JOB3.EMPL_RCD AND JOB1.EFFDT=JOB3.EFFDT) AND JOB1.EMPLID =
DR.EMPLID AND JOB1.EMPL_RCD = DR.EMPL_RCD AND (JOB1.SUPERVISOR_ID <>
DR.SUPERVISOR_ID OR JOB1.EMPL_STATUS <> DR.EMPL_STATUS OR JOB1.EFFDT <>
DR.JOB_EFFDT) AND DR.HR_DR_LEVEL = 1
/
-- Row(s) affected: 0
-- 10:57:05.830 Process 475277 ABENDED at Step HR_SRCH_DR.2ChgIns.Step01 (Action SQL) -- RC = 805
ROLLBACK
/
-- 10:57:05.874 SQL Error: ORA-00001: unique constraint (SYSADM.PS_HR_SRCH_CHG_2) violated



STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Schedule any one of these 3 indexes to run in Full or Incremental mode.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.