My Oracle Support Banner

ECM: Book To Bank Reconciliation (TR_BTB_CALC) Fails Due To Unique Constraint Error IN PS_BNK_BTB_DTL (Doc ID 2562691.1)

Last updated on FEBRUARY 01, 2021

Applies to:

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

Symptoms


Book to Bank reconciliation (TR_BTB_CALC) Fails due to unique constraint error. The issue seems to be due to AR deposit post and then unpost in a future period.
Along with un-post, if using the same deposit id to create a direct journal payment in same period and post the same to GL, Book to bank recon process picks both the unpost payment and AR direct journal in the process with same deposit id and then abends with unique constraint error.

Steps:

1. Create  payment in AR (Oct 2018).
2. Apply the payment.
3. Enter a bankstatement (Oct 2018).
4. Semi-Manual Reconciliation to reconciled bank statement to AR payment.
5. Unpost the AR payment. (Dec 2018).
6. Journal Generate for the next fiscal for templates AR_PAYMENT and AR_MAINT (Payment Unpost)so that both the AR Payment and Payment Unpost are  posted to GL.
7. Select and Calculate Book to Bank Reconciliation.
8. Go to semi-manual reconciliation page and Unreconcile the transactions.
9. Use the same deposit id to create a direct journal payment in same period (Dec 2018) and post the same to GL.
10. Run Generate Journals.
11. Reconcile the transactions again.
12. Select Book to Bank Statements - Select and Calculate:

Book to Bank reconciliation (TR_BTB_CALC) Fails due to unique constraint error:
"
File: e:\pt857p02b_1810030530-retail\peopletools\src\psappeng\aedebug.hSQL error. Stmt #: 1725  Erro r Position: 0  Return: 805 - ORA-00001: unique constraint (EMDBO.PS_BNK_BTB_DTL) violated Failed SQL stmt: INSERT INTO PS_BNK_BTB_DTL (BNK_ID_NBR, BANK_ACCOUNT_NUM, FISCAL_YEAR, ACCOUNTING_P
ERIOD, STATEMENT_ID, RECORD_SEQ_NUMBER, SOURCE_BUS_UNIT, TRAN_REF_ID, TRAN_DT, SEQ_NBR, CASH_FLOW_LI NE, CASH_FLOW_LEG, TR_SOURCE_CD, PYMNT_REF_ID, BUSINESS_UNIT, LEDGER, ADJUSTMENT_SIDE, TYPE_ADJUST, SELECT_TRAN, DESCR, BANK_CURRENCY, BASE_CURRENCY, BANK_STMT_CODE, RECON_TRANS_CODE, MONETARY_AMOUNT, MONETARY_AMT_BSE) SELECT BNK_ID_NBR, BANK_ACCOUNT_NUM, FISCAL_YEAR, ACCOUNTING_PERIOD, STATEMENT_ID , RECORD_SEQ_NUMBER, SOURCE_BUS_UNIT, TRAN_REF_ID, TRAN_DT, SEQ_NBR, CASH_FLOW_LINE, CASH_FLOW_LEG, TR_SOURCE_CD, PYMNT_REF_ID, BUSINESS_UNIT, LEDGER, ADJUSTMENT_SIDE, TYPE_ADJUST, SELECT_TRAN, DESCR,

Process 177211 ABENDED at Step TR_BTB_CALC.Populate.Table2 (SQL) -- RC = 805
"


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.