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:

Symptoms

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

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.