AM: Absence calculation(GPPDPRUN) is failing in GP_PAYAFTER with ORA-01427 (Doc ID 2216997.1)

Last updated on DECEMBER 22, 2016

Applies to:

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

Symptoms

On : 9.2 version

Absence calculation(GPPDPRUN) is failing in GP_PAYAFTER with the following:


ERROR
-----------------------
1427 - ORA-01427: single-row subquery returns more than one row

Failed SQL stmt: UPDATE PS_HGA_RSLTACM_TMP SET PS_HGA_RSLTACM_TMP.ABS_TAKE_CAL_VAL = (PS_HGA_RSLTACM_TMP.CALC_RSLT_VAL - NVL(( SELECT B.CALC_RSLT_VAL FROM PS_HGA_RSLTACM_TMP B WHERE B.PROCESS_INSTANCE = 1610147 AND PS_HGA_RSLTACM_TMP.EMPLID = B.EMPLID AND PS_HGA_RSLTACM_TMP.EMPL_RCD = B.EMPL_RCD AND PS_HGA_RSLTACM_TMP.PIN_NUM = B.PIN_NUM AND PS_HGA_RSLTACM_TMP.ACM_FROM_DT = B.ACM_FROM_DT AND PS_HGA_RSLTACM_TMP.ACM_THRU_DT = B.ACM_THRU_DT AND PS_HGA_RSLTACM_TMP.USER_KEY1 = B.USER_KEY1 AND PS_HGA_RSLTACM_TMP.USER_KEY2 = B.USER_KEY2 AND PS_HGA_RSLTACM_TMP.USER_KEY3 = B.USER_KEY3 AND PS_HGA_RSLTACM_TMP.USER_KEY4 = B.USER_KEY4 AND PS_HGA_RSLTACM_TMP.USER_KEY5 = B.USER_KEY5 AND PS_HGA_RSLTACM_TMP.USER_KEY6 = B.USER_KEY6 AND B.SLICE_BGN_DT = ( SELECT MAX(C.SLICE_BGN_DT) FROM PS_HGA_RSLTACM_TMP C WHERE C.EMPLID = B.EMPLID AND C.EMPL_RCD = B.EMPL_RCD AND C.PIN_NUM = B.PIN_NUM AND C.ACM_FROM_DT = B.ACM_FROM_DT AND C.ACM_THRU_DT = B.ACM_THRU_DT AND C.USER_KEY1 = B.USER_KEY1 AND C.USER_KEY2 = B.USER_KEY2 AND C.USER_KEY3 = B.USER_KEY3 AND C.USER_KEY4 = B.USER_KEY4 AND C.USER_KEY5 = B.USER_KEY5 AND C.USER_KEY6 = B.USER_KEY6 AND C.SLICE_BGN_DT < PS_HGA_RSLTACM_TMP.SLICE_BGN_DT )), 0)) WHERE PS_HGA_RSLTACM_TMP.PROCESS_INSTANCE = 1610147 AND PS_HGA_RSLTACM_TMP.ABS_AC_TYPE = 'T'

Process 1610147 ABENDED at Step HGA_PG_DATA.RPTDATA.Step03 (SQL) -- RC = 1427 (108,524)

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

PSAESRV completed service request at 21.05.57 2016-12-19

 


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Navigate to : Global Payroll and Absence Management>Absence and Payroll Processing>Calculate Absence and Payroll
2. Enter information for a run and the process abends


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