GP UK Pension Auto Enrollment Process Fails Due To Duplicate Key Error (Doc ID 2128655.1)

Last updated on APRIL 25, 2016

Applies to:

PeopleSoft Enterprise HCM Global Payroll UK - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

When attempting to run a payrun for multiple calendars where an employee has transferred from one paygroup to another, the following error occurs:

ERROR
-----------------------
AE_GPGB_PEN_EN_569474.stdout. shows the following error being encountered when running the Auto Pension enrollment process
File: e:\pt85304c-retail\peopletools\src\psappeng\aedebug.hSQL error. Stmt #: 1623 Error Position: 0 Return: 805 - [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert duplicate key row in object 'dbo.PS_GPGB_PEN_TMP4' with unique index 'PS_GPGB_PEN_TMP4'. The duplicate key value is (569474, 00165132, 0, Dec 30 2015 12:00AM, PEGKSER).
Failed SQL stmt: INSERT INTO PS_GPGB_PEN_TMP4 (PROCESS_INSTANCE , EMPLID , EMPL_RCD ,GPGB_PRCRN_DATE ,PAY_ENTITY ,GP_PAYGROUP ,CAL_RUN_ID ,GPGB_QPEN_SCHEME ,GPGB_PEN_SCHEME ,GPGB_PEN_EVENT ,GPGB_PEN_A_STATUS ,START_DATE ,END_DATE ,GPGB_EE_PCTG_CONT ,GPGB_ER_PCTG_CONT ,GPGB_EE_PEN_AMT ,GPGB_ER_PEN_AMT ,GPGB_QEARNING ,BIRTHDATE ,GPGB_STATE_PEN_AGE ,GPGB_OPTIN_FLG ,GPGB_OPTOUT_FLG ,GPGB_OPTOUT_ST_DT ,GPGB_OPTOUT_END_DT ,GPGB_POSTPONE_DT ,GPGB_POSTPONE_RSN ,GPGB_AGGER_FLG ,GPGB_NOTPRC_FLG ,GPGB_OVRPEN_SCHEME ,GPGB_PEN_REFUND ,FREQUENCY_ID ,FREQ_ANNUAL_FACTOR ,PRD_BGN_DT ,PRD_END_DT ,GPGB_PEN_THRESHOLD ,GPGB_PEN_QELL ,GPGB_PEN_QEUL ,RUN_FINALIZED_IND ,GPGB_PEN_EVENT1 ,GPGB_PEN_A_STATUS1 ,GPGB_AGG_QEARNING ,GPGB_PRP_START_DT ,GPGB_PRP_END_DT ,SEX ,GPGB_EFF_PRC_DT ,GPGB_PRV_OPTOUT_DT ,GPGB_PEN_FACTOR ,SEQNO ,GPGB_PRCRN_DATE1 ,EFFDT ,GPGB_STG_DATE ,GPGB_QE_PIN_CODE ,GPGB_POSTPONE_JD ,GPGB_POSTPONE_WR ,GPGB_STG_DT_DFR ,GPGB_DEFERAL_DT ,GPGB_ELG_ASMT_DT ,JOB_ENTRY_DT ,GPGB_PRV_DEFERL_DT ,PYMT_DT ,GPGB_MAX_DEFER_DT ,BIRTH_DT) SELECT 569474 ,A.EMPLID ,A.EMPL_RCD ,'2015-12-30' ,S.PAY_ENTITY ,A.GP_PAYGROUP ,'2015F32MW4' ,' ' ,' ' ,' ' ,' ' ,NULL ,NULL ,0 ,0 ,0 ,0 ,0 ,C.BIRTHDATE ,0 ,COALESCE(L.GPGB_OPTIN_FLG , ' ') ,COALESCE(L.GPGB_OPTOUT_FLG , ' ') ,N.GPGB_OPTOUT_ST_DT ,N.GPGB_OPTOUT_END_DT , L.GPGB_POSTPONE_DT ,COALESCE(L.GPGB_POSTPONE_RSN , ' ') ,COALESCE(L.GPGB_AGGER_FLG , ' ') ,COALESCE(L.GPGB_NOTPRC_FLG , ' ') ,COALESCE(L.GPGB_OVRPEN_SCHEME, ' ') ,' ' ,E.FREQUENCY_ID ,F.FREQ_ANNUAL_FACTOR ,E.PRD_BGN_DT ,E.PRD_END_DT ,ROUND((H.DATA_VAL1_DEC),(3),1) ,ROUND((H.DATA_VAL2_DEC),(3),1) ,ROUND((H.DATA_VAL3_DEC ),(3),1) ,G.RUN_FINALIZED_IND ,COALESCE(J.GPGB_PEN_EVENT , ' ') ,COALESCE(J.GPGB_PEN_A_STATUS , ' ') ,0 ,NULL ,NULL ,C.SEX ,NULL ,N.END_DATE ,COALESCE(L.GPGB_PEN_FACTOR , 0) ,COALESCE(J.SEQNO , 0) ,J.GPGB_PRCRN_DATE ,L.EFFDT ,NULL ,' ' ,0 ,0 ,NULL ,NULL ,NULL ,NULL ,J.GPGB_DEFERAL_DT ,P.PYMT_DT ,NULL ,NULL FROM PS_JOB A LEFT OUTER JOIN PS_GPGB_PN_EVNT_VW J ON J.EMPLID = A.EMPLID AND J.EMPL_RCD = A.EMPL_RCD , PS_JOB K LEFT OUTER JOIN PS_GPGB_PN_PYE_VW L ON L.EMPLID = K.EMPLID AND L.EMPL_RCD = K.EMPL_RCD , PS_JOB M LEFT OUTER JOIN PS_GPGB_EE_PEN_VW N ON N.EMPLID = M.EMPLID AND N.EMPL_RCD = M.EMPL_RCD , PS_PERSONAL_DATA C , PS_GP_CAL_PRD E ,PS_FREQUENCY_TBL F ,PS_GP_CAL_RUN G , PS_GP_BRACKET_DTL H ,PS_GP_PIN I ,PS_GP_CALENDAR P ,PS_GP_CAL_RUN_DTL S ,PS_GP_RUN_TYPE T WHERE G.CAL_RUN_ID = '2015F32MW4' AND S.CAL_RUN_ID=G.CAL_RUN_ID AND P.GP_PAYGROUP=S.GP_PAYGROUP AND P.CAL_ID=S.CAL_ID AND T.RUN_TYPE=P.RUN_TYPE AND T.CALC_TYPE='P' AND E.CAL_PRD_ID=S.CAL_PRD_ID AND A.TERMINATION_DT IS NULL AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A1.EMPLID = A.EMPLID AND A1.EMPL_RCD = A.EMPL_RCD AND A1.EFFDT <=E.PRD_END_DT ) AND A.EFFSEQ = ( SELECT MAX(A2.EFFSEQ) FROM PS_JOB A2 WHERE A2.EMPLID = A.EMPLID AND A2.EMPL_RCD = A.EMPL_RCD AND A2.EFFDT = A.EFFDT ) AND A.GP_PAYGROUP=P.GP_PAYGROUP AND K.EMPLID = A.EMPLID AND K.EMPL_RCD = A.EMPL_RCD AND K.EFFDT = A.EFFDT AND K.EFFSEQ=A.EFFSEQ AND M.EMPLID = A.EMPLID AND M.EMPL_RCD = A.EMPL_RCD AND M.EFFDT = A.EFFDT AND M.EFFSEQ=A.EFFSEQ AND C.EMPLID = A.EMPLID AND E.FREQUENCY_ID = F.FREQUENCY_ID AND F.FREQUENCY_ID= ( SELECT F1.FREQUENCY_ID FROM PS_FREQUENCY_TBL F1 WHERE F1.FREQUENCY_ID = E.FREQUENCY_ID AND F1.EFFDT = ( SELECT MAX(F2.EFFDT) FROM PS_FREQUENCY_TBL F2 WHERE F2.EFF_STATUS = 'A' AND F2.FREQUENCY_ID = E.FREQUENCY_ID )) AND I.PIN_CODE = 'GBR BR PEN LIMITS GBR' AND H.PIN_NUM = I.PIN_NUM AND H.DATA_KEY1 = F.FREQUENCY_ID AND H.EFFDT = ( SELECT MAX(H1.EFFDT) FROM PS_GP_BRACKET_DTL H1 WHERE H1.EFFDT <= '2015-12-30' AND H1.PIN_NUM = H.PIN_NUM AND H1.DATA_KEY1 = H.DATA_KEY1) AND NOT EXISTS ( SELECT 'X' FROM PS_GPGB_PEN_TMP4 WHERE PS_GPGB_PEN_TMP4.EMPLID=A.EMPLID AND PS_GPGB_PEN_TMP4.EMPL_RCD=A.EMPL_RCD)

Process 569474 ABENDED at Step GPGB_PEN_EN.CRI.Step03 (SQL) -- RC = 805 (108,524)

Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s

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

1. Configure the Pension Setup.
2. Create a new period to follow on from the monthly calendar the employee will be transferred from .
3. Create a calendar in a new Pay Group, where the test employee will be transferred into.
4. Add the new calendar to a calendar group. 
5. Transfer an employee, effective dated at the start of the new calendar.
6. Process the payroll.
7. Run the Auto Enrollment process.


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