EAP: Recreating Pay Cycle With 'Recreate With New Reference' Triggers PeopleCode SQL (Doc ID 2135076.1)

Last updated on JUNE 09, 2016

Applies to:

PeopleSoft Enterprise FIN Payables - Version 9.1 and later
Information in this document applies to any platform.

Symptoms

Recreating Pay cycle with 'Recreate with new reference' triggers PoepleCode SQL error.

This happens when RSTRT# Pay cycle system is trying to assign have process instance in PYCYCL_NAME_TMP table thatis same as the Process Instacne on PYCYCL_STAT table for the most current pay cycle seq number of RSTRT#.In addtion the RSTRT# pay cycle was last reprinted 7 days or before.

Following error is displayed on the screen :

A fatal PeopleCode SQL error occurred. Please consult your system log for details.

Sometimes following Error message is also seen:

Check Restart Pay Cycle could not be assigned. All Restart Pay Cycles already in use. (7250,101)

An unused restart Pay Cycle could not be found.

Trace file shows:

PSAPPSRV.4744 (2894) 1-1455 15.01.31 0.003000 SQL error. Function:SQLExec Error Position: 0

Return: 805 - ORA-00001: unique constraint (EMDBO.PS_PYCYCL_NAME_TMP) violated

Statement: INSERT INTO PS_PYCYCL_NAME_TMP (PAY_CYCLE, DATE_ADDED,PROCESS_INSTANCE) VALUES (:1, TO_DATE(:2,'YYYY-MM-DD'), :3)

Caught Exception: SQL error in Exec. (2,280)
PYCYCL_PNL_WRK5.PAY_CYCLE_RSTRT.SaveEdit Name:chk_pay_cycle_name PCPC:1335 Statement:13

Called from:PYCYCL_PNL_WRK5.PAY_CYCLE_RSTRT.SaveEdit Name:set_pay_cycle_name Statement:51

Called from:PYCYCL_PNL_WRK5.PAY_CYCLE_RSTRT.SaveEdit Name:get_restart_cycle Statement:64

Called from:PYCYCL_PNL_WRK5.PB_PROCESS.FieldChange Statement:24

 

NOTE: As a workaround ,deleting data for Pay Cycles RSTRT# from PS_PYCYCL_NAME_TMP resolves above errors.

Scenario:
1. Created 9 vouchers for 9 different Business Units - US001, US002, US003,US004, US005, US006, US007, US021, US022

2. To keep the test simple, the Bank was same on these vouchers :
SHARE/USBNK/CHCK and also made sure 'Confirm Numbers' was checked on the External Bank Account

3. Created 9 different Pay Cycles for each Business Unit

4. Ran Pay cycle for US001 and confirmed the Payment Reference.

5. After confirming the Payment Reference , opened the Pay Cycle in Payment Cycle Manager.

While opening the Pay Cycle following Message was displayed :

Pay Cycle status has been changed. (7015,416)

One or more process status in this pay cycle has been changed during the time, please click Refresh button to get the latest pay cycle status.

Also noticed: Pay Cycle Status displayed was 'Completed' and Schedule Payments Selected displayed number greater than zero (In this case it was 1)

6. Clicked on the 'Refresh' Button on the Pay Cycle Manager. The Page got saved and the Schedule Payments Selected now should zero

7. Followed Step # 4 through Step # 6 for rest of the 8 Pay Cycles

8. Restarted each pay cycle with option 'Recreate with new reference' and confirmed the new Payment Reference

But DID NOT reopen the Restart Pay Cycles in Pay Cycle Manager again after reconfirming Payment Reference. (This was to ensure PYCYCL_NAME_TMP has rows for all 9 RSTRT# pay cycles)

9. Query PS_PYCYCL_NAME_TMP
There were 9 rows in PS_PYCYCL_NAME_TMP table for each RSTRT# Pay Cycle.

10. Opened Pay Cycle RSTRT1 in Pay Cycle Manager and clicked refresh to update the Pay cycle to 'Completed'

11. In PYCYCL_NAME_TMP , For Pay Cycle RSTR1 Process Instance = 12778 and in PYCYCL_STAT table for the max Pay cycle Seq Number for Pay Cycle RSTRT1 ,Process Instance = 13003

12. Recreated Pay Cycle RSTR1 and this time did 'Re-Print using same reference'

13. Completed RSTRT1.

14. In PYCYCL_NAME_TMP , For Pay Cycle RSTR1 Process Instance = 13003 and in PYCYCL_STAT table for the max Pay cycle Seq Number for Pay Cycle RSTRT1 ,Process Instance = 13003

15. After 7 days or later,create Voucher for US001 and pay it using Pay cycle US001.

16. After completing the Pay cycle , do a recreate with option 'Recreate with new reference'.

17. When Process is clicked receives following error message :

A fatal PeopleCode SQL error occurred. Please consult your system log for details.

Sometimes users also see following message:

Check Restart Pay Cycle could not be assigned. All Restart Pay Cycles already in use. (7250,101)

An unused restart Pay Cycle could not be found.



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