My Oracle Support Banner

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.

Symptoms


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.

REPLICATION STEPS:

  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.

ERROR MESSAGE:

" -- 19:31:29.606 ...(EX_CANCL_PMT.CANCELPY.REISU_ER) (SQL)

  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

  ROLLBACK

  /

  -- 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.

NOTE: In the attached document, user details / company name / address / email / telephone number represent a fictitious sample (based upon made up data used in the Oracle Demo Vision instance).  Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

Changes

 

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
Changes
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.