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:
- Sign in as EXA1
- Employee Self-Service Fluid Homepage
- Click on the Expenses tile and then click the Create Expense Report
- 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 - Logout and Login as VP1
- 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 - Run Expense processing for Stage Payments and Post Liabilities check boxes checked.
Travel and Expenses > Process Expenses > Expense Processing - 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 - Run Payment Selection to make sure expense report has been selected
- Run Payment Creation to make sure the expense payment created
- Check the payment table
select * from ps_payment_tbl where pymnt_id_ref = 'XXXX' - Print checks and make sure Paycycle is completed
- Run Expense Processing for Post Payments and Update Paid Statuses checkboxes checked
Travel and Expenses > Process Expenses > Expense Processing - 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' - Run Journal Generator and make sure the from and to dates are as specified
General Ledger > Journals > Subsystem Journals > Generate Jrnl from Subsystem - 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 - Run Expense processing for cancel payment checkbox checked
Travel and Expenses > Process Expenses > Expense Processing - 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 - Post the payments
Accounts Payable > Batch Processes > Payment > Payment Posting - 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 - Reconcile the statement by using Semi Manual Recon Page
Banking > Reconcile Statements > Semi-Manual Reconciliation - Select the book to bank statement and the click on select and calculate button
Banking > Reconcile Statements > Select Book To Bank Statements - 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 |