EAR9.2: ARUPDATE Abended At Step AR_REV_EST.MASSAGE.BUDGETD2 When Posting Payment (Doc ID 2054308.1)

Last updated on SEPTEMBER 25, 2023

Applies to:

Symptoms

On : 9.2 version, Revenue Est & Commit Cntrl

When running ARUPDATE, AR_UPDATE2 ends in "No Success" with the following error.

ERROR
-----------------------
Revenue Estimate Started (6832,1)
17 activities loaded for process (6832,3)

File: e:\pt85312b-retail\peopletools\src\psappeng\aedebug.hSQL error. Stmt #: 1623 Error Position: 0 Return: 8601 - [Microsoft][SQL Server Native Client 10.0][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
[Microsoft][SQL Server Native Client 10.0][SQL
Failed SQL stmt: UPDATE PS_RE_TRANS_TAO5 SET BUDGET_DT = ( SELECT X.BUDGET_DT FROM PS_RE_IN_DST_TAO5 X , PS_RE_TRANS_TAO5 WHERE PS_RE_TRANS_TAO5.BUSINESS_UNIT = X.BUSINESS_UNIT AND PS_RE_TRANS_TAO5.CUST_ID = X.CUST_ID AND PS_RE_TRANS_TAO5.ITEM = X.ITEM AND PS_RE_TRANS_TAO5.ITEM_LINE = X.ITEM_LINE AND PS_RE_TRANS_TAO5.PROCESS_INSTANCE = X.PROCESS_INSTANCE AND PS_RE_TRANS_TAO5.AR_SEQ_NUM = X.AR_SEQ_NUM AND PS_RE_TRANS_TAO5.REV_ITEM_SEQ_NUM = X.REV_ITEM_SEQ_NUM AND PS_RE_TRANS_TAO5.DST_SEQ_NUM = X.DST_SEQ_NUM) WHERE PROCESS_INSTANCE = 1379 AND SYNC_BUDGET_DT = 'Y' AND REV_SOURCE = 'B' AND 'N' = ( SELECT BUDGET_DT_OVER_FLG FROM PS_BUS_UNIT_OPT_AR B WHERE B.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = PS_RE_TRANS_TAO5.BUSINESS_UNIT AND RECNAME = 'BUS_UNIT_OPT_AR'))

Process 1379 ABENDED at Step AR_REV_EST.MASSAGE.BUDGETD2 (SQL) -- RC = 8601 (108,524)
Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s
PSAESRV completed service request at 19.51.38 2015-06-09


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
Setup:

1. Enable commitment control for Billing, General Ledger and Receivables.
2. In Billing - Integration Options make sure GL Level = BI Creates GL Acct Entries and AR Level = Bill Line is AR Open Item.
3. Fund Code is set to Always Inherit for Inheritance Group 'Receivables and Billing'.
4. IntraUnit Balancing Entries is enabled and Fund Code is balanced chartfield for Ledger Group.
5. In Budget Definition Control Option = Tracking w/Budget and Account, Department and Fund Code are all Required.
6. In Receivables Options make sure Synchronize Budget Date is enabled and Budget Date Override is disabled.

Replication Steps:

1. Enter and post budget journal.
2. Revenue Estimate is updated correctly.
3. Add a new bill and enter 3 bill lines.
4. For Acctg – Rev Distribution use the same Account , Fund Code and Department.
5. Change status to RDY and save bill.
6. Run Finalize and Print Invoices.
7. Run Load Invoices to AR (PSJob BIJOB05).
8. Run Create Accounting Entries.
9. Run Budget Check.
10. Recognized Revenue is updated correctly.
11. Run ARUPDATE to post item.
12. Create a Regular Deposit for business unit GBR01, bank UKBNK and account CHCK.
13. Build payment worksheet.
14. Select item for payment.
15. Change Posting Action to Batch Standard.
16. Run ARUPDATE to post payment.
17. AR_UPDATE2 is running to No Success, process ABENDED at Step AR_REV_EST.MASSAGE.BUDGETD2 (Action SQL) -- RC = 8601.

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot post any payments.

Cause

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