We run PeopleSoft 9.1 North American Payroll with bi-weekly payruns. At the end of the calendar year, we have a single off-cycle pay calendar entry for the remaining days in the year till 12/31 because bi-weekly calendars almost
never align with the year. This year after upgrading to 9.1, our final ON-CYCLE payroll confirmation errored out in PSPCNFRM with several messages like:
Application Program Failed
In Pgm Section : MAIN(PSPPYUPD)
With Return Code: 00001
Error Message : End of fetch
We found the problem to be in the following stored SQL statement:
SELECT A.PAY_END_DT FROM PS_PAY_CALENDAR A WHERE A.COMPANY=
':1 AND A.PAYGROUP=:2 AND A.PAY_OFF_CYCLE_CAL = 'N' AND A.PAY_END_DT = (SELECT MIN(A1.PAY_END_DT) FROM
PS_PAY_CALENDAR A1 WHERE A1.COMPANY = A.COMPANY AND A1.PAYGROUP = A.PAYGROUP AND A1.PAY_END_DT > :3)
Because this SQL is lacking the AND A1.PAY_OFF_CYCLE_CAL = 'N' in the inner Select, it returns null because it expects an on-cycle calendar to match the pay-end-date of the off-cycle calender. You need to either fix the stored
statement or temporarily remove the off-cycle calender until after the pay-confirmation runs.
