My Oracle Support Banner

GP AUS STP - GPAU_STP_PRC Abended STGLOAD.Step02 For LUMPA - SQL Server - Multiple Columns (Doc ID 2634546.1)

Last updated on FEBRUARY 28, 2020

Applies to:

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

Symptoms



STP - GPAU_STP_PRC Abended STGLOAD.Step02 For LUMPA - SQL Server - Multiple columns agreggage

When STP Preparation process (App Engine) GPAU_STP_PRC is run for a calendar with an employee who has an amount to be reported in Lump A (eg. Termination)

ERROR
-----------------------
Process 1429852 ABENDED at Step GPAU_STP_PRC.STGLOAD.Step02 (SQL) -- RC = 8601 (108,524)

[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only colu
Failed SQL stmt: UPDATE PS_GPAU_STP_PAYSTG SET GPAU_LUMPA_IND = (COALESCE((SELECT MIN(CASE WHEN PS_GPAU_STP_PAYSTG.GPAU_LUMPA_IND <> ' ' THEN PS_GPAU_STP_PAYSTG.GPAU_LUMPA_IND WHEN ((JOB.ACTION = 'TER' AND JOB.ACTION_REASON IN ('PTD','ERT','ELI'))) OR PS_GPAU_STP_PAYSTG.GPAU_STP_LUMPD <> 0 THEN 'R' WHEN ((JOB.ACTION = 'RET' AND JOB.ACTION_REASON IN ('RES','RMT'))) THEN 'T' END) FROM PS_JOB JOB , PS_JOB_JR JR , PS_GPAU_STP_TMPB4 A WHERE JOB.EMPLID = PS_GPAU_STP_PAYSTG.EMPLID AND PS_GPAU_STP_PAYSTG.GPAU_STP_LUMPA <> 0 AND JOB.EFFDT = (SELECT MAX(JOB2.EFFDT) FROM PS_JOB JOB2 WHERE JOB2.EMPLID = JOB.EMPLID AND JOB2.HR_STATUS = 'I' AND ((JOB2.ACTION = 'TER' AND JOB2.ACTION_REASON IN ('PTD','ERT','ELI')) OR (JOB2.ACTION = 'RET' AND JOB2.ACTION_REASON IN ('RES','RMT'))) AND JOB2.EFFDT <= DATEADD(DAY, 1, PS_GPAU_STP_PAYSTG.PRD_END_DT) AND JOB2.GP_PAYGROUP IN ( SELECT PYG.GP_PAYGROUP FROM PS_GP_PYGRP PYG WHERE PYG.PAY_ENTITY = PS_GPAU_STP_PAYSTG.PAY_ENTITY )) AND JOB.EFFSEQ = ( SELECT MAX(JOB3.EFFSEQ) FROM PS_JOB JOB3 WHERE JOB3.EMPLID = JOB.EMPLID AND JOB3.EMPL_RCD = JOB.EMPL_RCD AND JOB3.HR_STATUS = 'I' AND JOB3.EFFDT = JOB.EFFDT) AND JR.EMPLID = JOB.EMPLID AND JR.EMPL_RCD = JOB.EMPL_RCD AND JR.EFFDT = JOB.EFFDT AND JR.EFFSEQ = JOB.EFFSEQ AND JR.BALANCE_GRP_NUM = PS_GPAU_STP_PAYSTG.USER_KEY2 AND A.EMPLID = PS_GPAU_STP_PAYSTG.EMPLID AND A.CAL_RUN_ID = PS_GPAU_STP_PAYSTG.CAL_RUN_ID AND A.CAL_ID = PS_GPAU_STP_PAYSTG.CAL_ID AND A.ORIG_CAL_

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run GPAU_STP_PRC for calendar with payee who has LUMP A


Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


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