EEX 9.2: Cancel Payments Abends at Step EX_CANCL_PMT.CANCELPY.REISU_ER Due to Duplicate Insert into PS_EX_SHEET_PYMNT when Two Payments Cancelled, Each for a Different EMPL_RCD Value
(Doc ID 2576940.1)
Last updated on APRIL 21, 2020
Applies to:PeopleSoft Enterprise FIN Expenses - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.
After having cancelled a couple of Expenses related Payments, for an Employee who has multiple Job Records (EMPL_RCD values of 0 and 1 for instance), and running the Cancel Payments process (EX_CANCL_PMT AE Program) at the Expense Processing AE Program (EX_TRAN_PRCS), this ends up with an abend due to a unique constraint violation on Record PS_EX_SHEET_PYMNT table.
This takes place if both Payments being cancelled are processed at the same time by the Cancel payments process, and if the Cancel Action was to Re-issue the liability, meaning the affected Expense Report transactions need to be staged for payment once again. If each Expense Report belongs to a different EMPL_RCD value, the system will fail at Step EX_CANCL_PMT.CANCELPY.REISU_ER.
1.- Have an Employee with multiple active Job Records (EMPL_RCD 0 and 1), and set as Default Profile EMPL_RCD = '0'
2.- Create a first Expense Report for this Employee, when EMPL_RCD is 0, and submit it for approval
3.- Approve the Expense Report
4.- Launch the Expense Processing program with the options of Post Liabilities, and Stage Payments
5.- Run Pay Cycle to completion to generate the needed Payment of this first Expense Report
6.- Launch the Expense Processing program with the options of Update Paid Statuses, and Post Payments
7.- Go to the Expenses Profile of the involved Employee, and set as Default Profile EMPL_RCD = '1'
8.- Create a second Expense Report for this Employee, when EMPL_RCD is 0, and submit it for approval
9.- Approve the Expense Report
10.- Launch the Expense Processing program with the options of Post Liabilities, and Stage Payments
11.- Run Pay Cycle to completion to generate the needed Payment of this second Expense Report
12.- Launch the Expense Processing program with the options of Update Paid Statuses, and Post Payments
13.- Proceed to cancel the first Payment with the options of: Void, and Re-Issue
14.- Proceed to cancel the second Payment with the options of: Void, and Re-Issue
15.- Launch the Expense Processing program with the options of Cancel Payments
16.- The process abends in Error Message
To gather more information concerning this scenario and its related problem, refer to the available Replication Steps PDF Document here linked containing the complete configuration and the replication steps necessary to reproduce the issue.
INSERT INTO PS_EX_SHEET_PYMNT (SHEET_ID, LINE_NBR, SEQ_NBR, EX_PYMNT_TYPE, PROCESS_INSTANCE, PROCESS_FLAG, PAY_CYCLE, PYMNT_SELCT_STATUS, PYMNT_ID, PYMNT_ID_REF, PYMNT_HOLD, PYMNT_NOTIFY, POST_STATUS_AP, PYMNT_STATUS, CANCEL_ACTION, CANCEL_DT, EMPLID, EMPL_RCD, APPROVAL_DT, ACCOUNTING_DT, DOC_TYPE, DOC_SEQ_NBR, DOC_SEQ_DATE, DUE_DT, SCHEDULED_PAY_DT, STTLMNT_DT_EST, PYMNT_DT, TRANS_DT, PREFERRED_LANGUAGE, BUSINESS_UNIT_GL, BUSINESS_UNIT_BANK, SOURCE_TXN, BANK_SETID, BANK_CD, BANK_ACCT_KEY, REMIT_BANK_SETID, REMIT_BANK_CD, REMIT_BNK_ACCT_KEY, EFT_PYMNT_FMT_CD, EFT_RTNG_FLG, EFT_RTNG_STATUS, EFT_TRANS_HANDLING, EFT_DOM_COSTS_CD, EFT_CORR_COSTS_CD, EFT_CROSSED_CHECK, EFT_CHECK_FWRD_CD, EFT_CHECK_DRAW_CD, EFT_PAY_INST_CD1, EFT_PAY_INST_CD2, EFT_PAY_INST_CD3, EFT_PAY_INST_CD4, EFT_PAY_INST_REF1, EFT_PAY_INST_REF2, NAME1, NAME2, NAME1_AC, NAME2_AC, COUNTRY, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, NUM1, NUM2, HOUSE_TYPE, ADDR_FIELD1, ADDR_FIELD2, ADDR_FIELD3, COUNTY, STATE, POSTAL, GEO_CODE, IN_CITY_LIMIT, PYMNT_METHOD, PYMNT_HANDLING_CD, INVOICE_ID, VENDOR_ID, VENDOR_SETID, VNDR_LOC, VENDOR_NAME_SHORT, REMIT_SETID, REMIT_VENDOR, ADDRESS_SEQ_NUM, PYMNT_GROSS_AMT, GROSS_AMT_BSE, BASE_CURRENCY, TXN_CURRENCY_CD, CURRENCY_CD, CURRENCY_PYMNT, BASE_CURRENCY_BNK, RT_TYPE, RATE_MULT, RATE_DIV, PYMNT_RATE_MULT, PYMNT_RATE_DIV, BNK_RATE_MULT, BNK_RATE_DIV, CUR_RT_TYPE_PYMNT, EXCHNG_RT_BNK, RT_TYPE_BNK, CUR_EXCHNG_RT_PYMT, PAID_AMT, PAID_AMT_BSE, PAID_AMT_BNK_BSE, GROSSUP, ACCOUNT, EX_PYRL_STATUS, EX_PYMNT_DEST, REJECTED_REASON, DATE_ACKNOWLEDGE, PAYCHECK_NBR, FORM_ID, RECEIPT_ID, REFERENCE_ID, PYMNT_MESSAGE, LASTUPDDTTM) SELECT DISTINCT B.SHEET_ID, B.LINE_NBR, (B.SEQ_NBR + 1) , B.EX_PYMNT_TYPE, A.PROCESS_INSTANCE, B.PROCESS_FLAG, ' ' , 'N' , ' ' , ' ' , B.PYMNT_HOLD, 'N', 'U' , 'N' , 'N' , B.CANCEL_DT, A.EMPLID, A.EMPL_RCD, B.APPROVAL_DT, NULL, B.DOC_TYPE, B.DOC_SEQ_NBR, B.DOC_SEQ_DATE, B.DUE_DT, B.SCHEDULED_PAY_DT, B.STTLMNT_DT_EST, B.PYMNT_DT, B.TRANS_DT, B.PREFERRED_LANGUAGE, A.BUSINESS_UNIT_GL, B.BUSINESS_UNIT_BANK, B.SOURCE_TXN, A.BANK_SETID, A.BANK_CD, A.BANK_ACCT_KEY, A.REMIT_BANK_SETID, A.REMIT_BANK_CD, A.REMIT_BNK_ACCT_KEY, A.EFT_PYMNT_FMT_CD, A.EFT_RTNG_FLG, A.EFT_RTNG_STATUS, A.EFT_TRANS_HANDLING, A.EFT_DOM_COSTS_CD, A.EFT_CORR_COSTS_CD, A.EFT_CROSSED_CHECK, A.EFT_CHECK_FWRD_CD, A.EFT_CHECK_DRAW_CD, A.EFT_PAY_INST_CD1, A.EFT_PAY_INST_CD2, A.EFT_PAY_INST_CD3, A.EFT_PAY_INST_CD4, A.EFT_PAY_INST_REF1, A.EFT_PAY_INST_REF2, A.NAME1, A.NAME2, A.NAME1_AC, A.NAME2_AC, A.COUNTRY, A.ADDRESS1, A.ADDRESS2, A.ADDRESS3, A.ADDRESS4, A.CITY, A.NUM1, A.NUM2, A.HOUSE_TYPE, A.ADDR_FIELD1, A.ADDR_FIELD2, A.ADDR_FIELD3, A.COUNTY, A.STATE, A.POSTAL, A.GEO_CODE, A.IN_CITY_LIMIT, A.PYMNT_METHOD, A.PYMNT_HANDLING_CD, B.INVOICE_ID, B.VENDOR_ID, B.VENDOR_SETID, B.VNDR_LOC, B.VENDOR_NAME_SHORT, B.REMIT_SETID, B.REMIT_VENDOR, B.ADDRESS_SEQ_NUM, B.PYMNT_GROSS_AMT, B.GROSS_AMT_BSE, B.BASE_CURRENCY, B.TXN_CURRENCY_CD, B.CURRENCY_CD, ' ', ' ', B.RT_TYPE, B.RATE_MULT, B.RATE_DIV, B.PYMNT_RATE_MULT, B.PYMNT_RATE_DIV, B.BNK_RATE_MULT, B.BNK_RATE_DIV, B.CUR_RT_TYPE_PYMNT, B.EXCHNG_RT_BNK, B.RT_TYPE_BNK, B.CUR_EXCHNG_RT_PYMT, 0 , 0 , 0, B.GROSSUP, ' ', 'N', B.EX_PYMNT_DEST, '0', NULL, 0, ' ', B.RECEIPT_ID, B.REFERENCE_ID, B.PYMNT_MESSAGE, CAST(SYSTIMESTAMP AS TIMESTAMP) FROM PS_EXEMPPMT_TAO4 A, PS_EXCANPMT_TAO4 B WHERE A.EMPLID=B.EMPLID AND A.BUSINESS_UNIT_GL = B.BUSINESS_UNIT_GL AND B.CANCEL_ACTION IN ('H','R') AND B.EX_PYMNT_TYPE IN ('E', 'A') AND B.PROCESS_INSTANCE = 191811
-- Row(s) affected: 0
-- 19:31:29.695 Process 191811 ABENDED at Step EX_CANCL_PMT.CANCELPY.REISU_ER (Action SQL) -- RC = 805
-- 19:31:29.721 SQL Error: ORA-00001: unique constraint (EMDBO.PS_EX_SHEET_PYMNT) violated "
While the Cancel Payments process (EX_CANCL_PMT AE Program) abends in such a manner, the Cancel Payment Accounting Entries are not being generated, and the affected Expense Reports are being left as corrupted, and can no longer be Staged for Payment. This delays the new reimbursement needed for those Expenses, and prevents from impacting the Ledger with the latest tasks performed.
If multiple Payments are being cancelled, and their respective Expense Report transactions belong to different EMPL_RCD values, the Cancel Payments process (EX_CANCL_PMT AE Program) should be able to process the request, generate the needed Payment Cancellation Accounting Entries, re-open the previously paid Expense Report transactions, and place them automatically in Staged for Payment status ready for a new payment to be issued.
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