My Oracle Support Banner

ECM: TR_BTB_CALC Failed With Unique Constraint Error at Step TR_BTB_CALC.Populate.Table1 (Action SQL) (Doc ID 3060133.1)

Last updated on NOVEMBER 21, 2024

Applies to:

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

Symptoms

TR_BTB_CALC failed with unique constraint error when the expense payment is cancelled and paid back again.

Error:


ORA-00001: unique constraint (SYSADM.PS_BNK_BTB_GL_DTL) violated
Failed SQL stmt: INSERT INTO PS_BNK_BTB_GL_DTL (BNK_ID_NBR, BANK_ACCOUNT_NUM, FISCAL_YEAR, ACCOUNTING_PERIOD, SOURCE_BUS_UNIT, TRAN_REF_ID, TRAN_DT, SEQ_NBR, CASH_FLOW_LEG, CASH_FLOW_LINE, BTB_SOURCE, TR_SOURCE_CD, JOURNAL_ID, JOURNAL_DATE, BUSINESS_UNIT, LEDGER, ACCOUNT, ADJUSTMENT_SIDE, TYPE_ADJUST, SELECT_TRAN, MONETARY_AMOUNT, CURRENCY_CD, FOREIGN_AMOUNT, FOREIGN_CURRENCY, DESCR) SELECT BNK_ID_NBR, BANK_ACCOUNT_NUM, FISCAL_YEAR, ACCOUNTING_PERIOD, SOURCE_BUS_UNIT, TRAN_REF_ID, TRAN_DT, SEQ_NBR, CASH_FLOW_LEG, CASH_FLOW_LINE, BTB_SOURCE, TR_SOURCE_CD, JOURNAL_ID, JOURNAL_DATE, BUSINESS_UNIT, LEDGER, ACCOUNT, ADJUSTMENT_SIDE, TYPE_ADJUST, SELECT_TRAN, MONETARY_AMOUNT, CURRENCY_CD, FOREIGN_AMOUNT, FOREIGN_CURRENCY, DESCR FROM PS_BTB_TRANS_TAO4
ABENDED at Step TR_BTB_CALC.Populate.Table1 (SQL) -- RC = 805 (108,524)


STEPS:

  1. Sign in as EXA1
  2. Employee Self-Service Fluid Homepage
  3. Click on the Expenses tile and then click the Create Expense Report
  4. Make sure the expense report is for prior month Example- 01/01/2024
    Click on the Accounting link
    Save the report and click on Review and Submit and Click on Submit
  5. Logout and Login as VP1
  6. Go to the expense report to modify the accounting date to be same as the expense report date which for example 01/01/2024
    Click on the transaction id link and change the accounting date to 01/01/2024 and save the page
    Travel and Expenses > Approve Transactions > Modify Approved Transactions
  7. Run Expense processing for Stage Payments and Post Liabilities check boxes checked.
    Travel and Expenses > Process Expenses > Expense Processing
  8.  Create a new Paycycle
    Accounts Payable > Payments > Pay Cycle Processing > Payment Selection Criteria
    Make sure the Pay from date is 01/01/24
    Payment date is 01/31/2024
    Accounting date is 01/31/2024
    Source BU – EXPN
  9. Run Payment Selection to make sure expense report has been selected
  10. Run Payment Creation to make sure the expense payment created
  11. Check the payment table
    select * from ps_payment_tbl where pymnt_id_ref = 'XXXX'
  12. Print checks and make sure Paycycle is completed
  13. Run Expense Processing for Post Payments and Update Paid Statuses checkboxes checked
    Travel and Expenses > Process Expenses > Expense Processing
  14. Check the Expense Payment
    select * from ps_ex_sheet_pymnt where sheet_id = 'XXXX'
    Accounting entries for payment
    select * from ps_ex_acctg_line where ex_doc_id = 'XXXX'
  15. Run Journal Generator and make sure the from and to dates are as specified
    General Ledger > Journals > Subsystem Journals > Generate Jrnl from Subsystem
  16. Check data in the ex_acctg_line table to make sure the journal data is populated
    Now cancel the payment in current month for example on 13th Feb 2024 with reopen option
    Accounts Payable > Payments > Cancel/Void Payments > Payment Cancellation
  17. Run Expense processing for cancel payment checkbox checked
    Travel and Expenses > Process Expenses > Expense Processing
  18. Run Paycycle
    Accounts Payable > Payments > Pay Cycle Processing > Payment Selection Criteria
    Make sure the payment date is current month for example - 02/13/2024
    Accounting date is also for example - 02/13/2024
    Withholding date is also for example - 02/13/2024
    Run Paycycle Selection
    Expense report payment is selected
    Run Paycycle Creation
    Print check and make sure the paycycle is in completed status
  19.  Post the payments
    Accounts Payable > Batch Processes > Payment > Payment Posting
  20. Enter a Bank Statement - Statement Date, Creation date, Value date as in prior month for example 01/31/2024 and with the payment reference and amount from above steps
    Banking > Bank Statements > Enter Bank Statements
  21. Reconcile the statement by using Semi Manual Recon Page
    Banking > Reconcile Statements > Semi-Manual Reconciliation
  22. Select the book to bank statement and the click on select and calculate button
    Banking > Reconcile Statements > Select Book To Bank Statements
  23. Select the book to bank statement and run Book to Bank Reconciliation
    Banking > Reconcile Statements > Book to Bank Reconciliation
    TR_BTB_CALC – process went to no success with a unique constraint

NOTE: In the images/screenshots/examples mentioned and/or 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.