EAR 9.2: AR Update Program Abends at Step AR_POSTING.PSTPYMTS.UPD_COM3 Due to Incorrect Syntax Around Record PS_DEPOSIT_CONTROL F When Executing the Process in MSSQL Database Platform
(Doc ID 3037259.1)
Last updated on JULY 25, 2024
Applies to:
PeopleSoft Enterprise FIN Receivables - Version 9.2 and laterInformation in this document applies to any platform.
Symptoms
The AR Update program is abending at Step AR_POSTING.PSTPYMTS.UPD_COM3 stating that the SQL Update Statement against Record PS_DEPOSIT_CONTROL could not be completed due to incorrect syntax. This is taking place on Microsoft SQL Database Platform environments.
REPLICATION STEPS:
1.- Log into the FSCM Online Application as a Receivables User
2.- Navigate to: Accounts Receivable > Receivables Update > Request Receivables Update
3.- Create a new Run Control ID, defining the needed options
4.- Click on Run button, and select ARUPDATE PSJob to be launched
5.- PROBLEM: The AR Update process has abended in Error Message
ERROR MESSAGE:
-- .......(AR_POSTING.PSTPYMTS.UPD_COM3) (SQL)
UPDATE PS_DEPOSIT_CONTROL F SET F.POSTED_TOTAL = ( SELECT DISTINCT ABS(SUM(IT.PAYMENT_AMT)) FROM PS_ITEM_ACTIVITY IT , PS_ITEM I , PS_DEPOSIT_CONTROL D WHERE IT.DEPOSIT_BU = 'XXXXX' AND IT.DEPOSIT_ID = '11111' AND IT.DEPOSIT_BU = D.DEPOSIT_BU AND IT.DEPOSIT_ID = D.DEPOSIT_ID AND IT.BUSINESS_UNIT = I.BUSINESS_UNIT AND IT.CUST_ID = I.CUST_ID AND IT.ITEM = I.ITEM AND IT.ITEM_LINE = I.ITEM_LINE AND I.ITEM_STATUS IN ('C','O') AND IT.ENTRY_USE_ID = 'WS-01' AND D.CONTROL_CNT <> D.POSTED_COUNT + D.MISC_DST_COUNT AND D.CONTROL_AMT <> D.POSTED_TOTAL + D.MISC_DST_TOTAL AND D.DEP_POST_STATUS = 'C' AND D.DEPOSIT_STATUS = 'C') , F.POSTED_COUNT = ( SELECT DISTINCT D.ENTERED_CNT FROM PS_DEPOSIT_CONTROL D WHERE D.DEPOSIT_BU = 'XXXXX' AND D.DEPOSIT_ID = '11111' AND D.CONTROL_CNT <> D.POSTED_COUNT + D.MISC_DST_COUNT AND D.CONTROL_AMT <> D.POSTED_TOTAL + D.MISC_DST_TOTAL AND D.DEP_POST_STATUS = 'C' AND D.DEPOSIT_STATUS = 'C') WHERE EXISTS ( SELECT 'X' FROM PS_RP_DEPCTL_TAO1 E WHERE E.PROCESS_INSTANCE = 123456789 AND E.DEPOSIT_BU = 'XXXXX' AND E.DEPOSIT_ID = '11111' AND E.DEPOSIT_BU = F.DEPOSIT_BU AND E.DEPOSIT_ID = F.DEPOSIT_ID AND E.GROUP_TYPE = 'P' AND E.DEP_POST_STATUS = 'P') AND F.DEPOSIT_BU = 'XXXXX' AND F.DEPOSIT_ID = '11111' AND F.DEP_POST_STATUS = 'C' AND F.DEPOSIT_STATUS = 'C' AND F.CONTROL_CNT <> F.POSTED_COUNT + F.MISC_DST_COUNT AND F.CONTROL_AMT <> F.POSTED_TOTAL + F.MISC_DST_TOTAL
/
-- Row(s) affected: 0
-- Process 1672638 ABENDED at Step AR_POSTING.PSTPYMTS.UPD_COM3 (Action SQL) -- RC = 8601
ROLLBACK
/
-- SQL Error: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'F'.
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ','.
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'WHE? "
With the abend of the AR Update program, the handled transactions have become stuck, and until the wrong codeline is not addressed, the affected groups will not be able to be processed successfully, generating the needed Accounting Entries.
Regardless of the Database Platform being used, (Oracle, DB2, MSSQL, etc...), the AR Update Step of AR_POSTING.PSTPYMTS.UPD_COM3 (Action SQL) should be able to complete successfully on all scenarios handled.
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 |