GP CHE- Payment File Preparation Fails In Case Of Multiple Garnishment Payments (GPCH_PMT)

(Doc ID 2417310.1)

Last updated on JUNE 29, 2018

Applies to:

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

Symptoms

Customer statement
=================
Payroll can handle multiple garnishment deduction with beneficiairy. But the CHE payment preparations fails due to multiple rows inside an sql- UPDATE statement on table GPCH_BK_XFR_EE
GPCH_PMT, step GPCH_BK_TRN


Replication steps
=============
 Calculate payroll
 Run CHE payment preparation (GPCH_PMT), step GPCH_BK_TRN fails (see error on page-1)


Error
=======
File: /vob/peopletools/src/psappeng/aedebug.hSQL error. Stmt #: 1723 Error Position: 1031 Return: 1427 - ORA-01427: single-row subquery returns more than one row

Failed SQL stmt:
UPDATE PS_GPCH_BK_XFER_EE SET
GPCH_BK_PUR_TRNFER= ( SELECT DISTINCT C.DESCR FROM PS_GP_PIN_CATEGORY C , PS_GP_PIN P WHERE P.PIN_CATEGORY = C.PIN_CATEGORY AND P.PIN_NUM = PS_GPCH_BK_XFER_EE .GPCH_BK_SEQ_NBR AND PS_GPCH_BK_XFER_EE.GPCH_AL_PAYMT_TYPE='8')
,GPCH_BK_MULTI_FLD1= ( SELECT DISTINCT C.DESCR FROM PS_DEPT_TBL C , PS_JOB A WHERE C.DEPTID = A.DEPTID AND C.SETID = A.SETID_DEPT AND A.EMPLID = PS_GPCH_BK_XFER_EE.EMPLID AND A.EMPL_RCD = PS_GPCH_BK_XFER_EE.EMPL_RCD AND A.COMPANY = PS_GPCH_BK_XFER_EE.PAY_ENTITY AND A.EFFDT=( SELECT MAX(B.EFFDT) FROM PS_JOB B WHERE A.EMPLID=B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.COMPANY = B.COMPANY AND B.EFFDT <= PS_GPCH_BK_XFER_EE.SEG_END_DT) AND A.EFFSEQ = ( SELECT MAX(B1.EFFSEQ) FROM PS_JOB B1 WHERE A.EMPLID = B1.EMPLID AND A.EMPL_RCD = B1.EMPL_RCD AND A.EFFDT = B1.EFFDT AND A.COMPANY = B1.COMPANY) AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_DEPT_TBL C1 WHERE C.DEPTID = C1.DEPTID AND C.SETID = C1.SETID AND C1.EFFDT <=PS_GPCH_BK_XFER_EE.SEG_END_DT))
,GPCH_BK_MULTI_FLD2=SUBSTR(( SELECT DISTINCT RTRIM(F.SOVR_VAL_CHAR,' ') ||'/'|| RTRIM(K.SOVR_VAL_CHAR,' ') FROM PS_GP_PYE_OVR_SOVR F ,PS_GP_PYE_OVR_SOVR K ,PS_GP_PIN G ,PS_GP_PIN L WHERE F.PIN_SOVR_NUM=G.PIN_NUM AND K.PIN_SOVR_NUM=L.PIN_NUM AND (G.PIN_NM='CH_GR_COURT' AND F.PIN_NUM=PS_GPCH_BK_XFER_EE.GPCH_BK_SEQ_NBR AND F.EMPLID=PS_GPCH_BK_XFER_EE.EMPLID AND F.EMPL_RCD=PS_GPCH_BK_XFER_EE.EMPL_RCD AND F.BGN_DT=( SELECT MAX(E.BGN_DT) FROM PS_GPCH_BK_XFER_EE D ,PS_GP_PYE_OVR_SOVR E WHERE E.BGN_DT < D.SEG_END_DT AND E.EMPLID=D.EMPLID AND E.EMPL_RCD = D.EMPL_RCD AND D.CAL_RUN_ID=PS_GPCH_BK_XFER_EE.CAL_RUN_ID AND E.EMPLID=F.EMPLID AND E.EMPL_RCD = F.EMPL_RCD AND E.PIN_NUM = PS_GPCH_BK_XFER_EE.GPCH_BK_SEQ_NBR)) AND (L.PIN_NM='CH_GR_REF_NR' AND K.EMPLID=PS_GPCH_BK_XFER_EE.EMPLID AND K.EMPL_RCD =PS_GPCH_BK_XFER_EE.EMPL_RCD AND K.BGN_DT=( SELECT MAX(N.BGN_DT) FROM PS_GPCH_BK_XFER_EE M ,PS_GP_PYE_OVR_SOVR N WHERE N.BGN_DT < M.SEG_END_DT AND N.EMPLID=M.EMPLID AND N.EMPL_RCD = M.EMPL_RCD AND M.CAL_RUN_ID=PS_GPCH_BK_XFER_EE.CAL_RUN_ID AND N.EMPLID=K.EMPLID AND N.EMPL_RCD = K.EMPL_RCD AND N.PIN_NUM = PS_GPCH_BK_XFER_EE.GPCH_BK_SEQ_NBR))),1,35)
WHERE PS_GPCH_BK_XFER_EE.CAL_RUN_ID = '00800_2016_M01'
AND PS_GPCH_BK_XFER_EE.PAY_ENTITY = '100'
AND PS_GPCH_BK_XFER_EE.EMPLID BETWEEN '0000874' AND '0700184'
AND PS_GPCH_BK_XFER_EE.GPCH_AL_PAYMT_TYPE='8'

Traitement 95332 ABANDONNE à l'étape

Changes

 

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