EAP: Payment Creation abends at Step AP_APY2015.M_CLOS1B.Step03 (SQL) with Unique Constraint Error (Doc ID 1634303.1)

Last updated on MARCH 18, 2016

Applies to:

PeopleSoft Enterprise FIN Payables - Version 9.1 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

System allows to apply Prepayment to a Paid Voucher.  Apply button in "Apply Vouchers to Prepayment" does not verify the current Payment Status of the Voucher.  As a result, Payment Creation abends at Step AP_APY2015.M_CLOS1B.Step03 (SQL) with an error.

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

Failed SQL stmt: INSERT INTO PS_VCHR_DOCSEQ_HDR (BUSINESS_UNIT , VOUCHER_ID , AP_TRANS_TYPE , UNPOST_SEQ , APPL_JRNL_ID , PYMNT_CNT , BUSINESS_UNIT_GL , ACCOUNTING_DT , DOC_TYPE , DOC_SEQ_NBR , DOC_SEQ_DATE , DOC_SEQ_STATUS ,
PROCESS_INSTANCE) SELECT A.BUSINESS_UNIT , SUBSTR(A.PAY_DOC_ID ,1,8) , 'P' , 0 , D.APPL_JRNL_ID_CASH , A.PAY_DOC_SEQ , C.BUSINESS_UNIT_GL , B.ACCOUNTING_DT , B.DOC_TYPE , B.DOC_SEQ_NBR , B.DOC_SEQ_DATE ,
B.DOC_SEQ_STATUS , B.PROCESS_INSTANCE FROM PS_PYCYCL_01_TMP4 A ,PS_PAYMENT_TBL B , PS_BANK_ACCT_DEFN C , PS_BUS_UNIT_INTFC D WHERE B.PAY_CYCLE = 'PR' AND B.PAY_CYCLE_SEQ_NUM = 5 AND B.DOC_TYPE <> ' ' AND
A.SOURCE_TXN = 'VCHR' AND A.BANK_SETID = B.BANK_SETID AND A.BANK_CD = B.BANK_CD AND A.BANK_ACCT_KEY = B.BANK_ACCT_KEY AND A.PYMNT_ID = B.PYMNT_ID AND B.BANK_SETID = C.SETID AND B.BANK_CD = C.BANK_CD AND B.BANK_ACCT_KEY =
C.BANK_ACCT_KEY AND C.BUSINESS_UNIT_GL = D.BUSINESS_UNIT AND B.DOC_SEQ_NBR <> ' '

Process 12564 ABENDED at Step AP_APY2015.M_CLOS1B.Step03 (SQL) -- RC = 805 (108,524)

STEPS
1) Ensure Document Sequencing is enabled
2) Create and pay a Prepaid Voucher of 1000 USD
3) Create a Regular Voucher of 127000 USD
4) Go to "Apply Vouchers to Prepayments" and open the Prepaid Voucher
5) Select Regular Voucher to Apply. Do Not click on Apply but open a New Window
6) Run Pay Cycle to pay the Regular Voucher
7) Now go back to the previous window of Prepayment and click on the Apply button
8) Again run the Pay Cycle, such that the Regular Voucher meets the Selection Criteria
9) Run Payment Creation
10) Above error occurs

EXPECTED RESULTS
System should not allow to apply Paid Voucher to Prepayment.

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