EAR 9.2: AR Update Abends at AR_PGG_SERV.MNTINTR1.Step01 Due to Incorrect Syntax for MSSQL Database When Handling Maintenance Worksheets Using Inter/IntraUnit ChartField Inheritance
(Doc ID 3000377.1)
Last updated on JULY 05, 2024
Applies to:
PeopleSoft Enterprise FIN Receivables - Version 9.2 to 9.2 [Release 9]Information in this document applies to any platform.
Symptoms
The AR Update process (AR_UPDATE AE Program) is abending at AR_PGG_SERV.MNTINTR1.Step01 due to incorrect syntax being used on MicroSoft SQL Server Database Platform. This problem is taking place when the below conditions are met:
- Database Platform must be MSSQL Server
- ChartField Inheritance must be used for Accounting purposes
- The transaction must be a Maintenance Worksheet
- It also must be an InterUnit transaction, forcing InterUnit Accounting Entries to be generated (Or IntraUnit ones if IntraUnit Balancing Entries are enabled within a multiple ledger group)
- The environment must also be patched at PeopleSoft Enterprise FIN 9.2 PUM Image #47
REPLICATION STEPS:
1.- Log into the FIN Online Application as a System Administrator
2.- Navigate to: Set Up Financials/Supply Chain > Common Definitions > Design ChartFields > ChartField Inheritance > Define CF Inheritance Options
3.- Open ChartField Inheritance Group AR/BI under Set ID USBCF and set the Operating Unit to 'Always Inherit'
4.- Log into the FIN Online Application as a Receivables User
5.- Navigate to: Accounts Receivable > Pending Items > Online Items > Online Item Group Entry
6.- Create a new Pending Item Group for Business Unit USBC1, Customer ID 1008, Entry Type IN, and Amount 1000 USD.
7.- Balance the Group, go to the Accounting Entries tab, and click on the lightning bolt icon.
8.- Once the Accounting Entries are generated, in the User row, manually populate the value of Operating Unit FLORIDA, and save the changes. Notice that the same value will automatically populate into the AR row due to ChartField Inheritance.
9.- Go to the Actions tab, and set the Post Action to Batch Standard
10.- Navigate to: Accounts Receivables > Receivables Update > Request Receivables Update
11.- Create a new Run Control ID, and have the Business Unit as USBC1, and the Pending Items option selected, and launch AR Update process
12.- Navigate to: Accounts Receivable > Pending Items > Online Items > Online Item Group Entry
13.- Create a new Pending Item Group for Business Unit USBC2, Customer ID 1008, Entry Type CR, and Amount -1000 USD.
14.- Balance the Group, go to the Accounting Entries tab, and click on the lightning bolt icon.
15.- Once the Accounting Entries are generated, in the User row, manually populate the value of Operating Unit ILLINOIS, and save the changes. Notice that the same value will automatically populate into the AR row due to ChartField Inheritance.
16.- Go to the Actions tab, and set the Post Action to Batch Standard
17.- Navigate to: Accounts Receivables > Receivables Update > Request Receivables Update
18.- Create a new Run Control ID, and have the Business Unit as USBC2, and the Pending Items option selected, and launch AR Update process
19.- Navigate to: Accounts Receivable > Receivables Maintenance > Maintenance Worksheet > Create Maintenance Worksheet
20.- Proceed to build a new Maintenance Worksheet under Business Unit USBC1, for Customer ID 1008, and the 2 Items previously created
21.- At the Worksheet Application page, match the 2 transactions for the full amounts (1000 USD vs -1000 USD)
22.- Go to the Worksheet Action page, and set the Post Action into Batch Standard
23.- Navigate to: Accounts Receivables > Receivables Update > Request Receivables Update
24.- Create a new Run Control ID, and have the Business Unit as USBC1, have the Maintenance Transactions option selected, and launch AR Update process
25.- PROBLEM: The AR Update PSJob will end up in No Success with an Error Message
To gather more information concerning this scenario and its related problem, refer to the available Replication Steps PDF Document here linked containing the complete configuration and the replication steps necessary to reproduce the problem.
ERROR MESSAGE:
/*UPDATE PS_PG_PENDST_TAO4 P SET P.BUDGET_REF = '2023' WHERE P.PROCESS_INSTANCE = 1735707 AND P.SYSTEM_DEFINED = 'Y' AND EXISTS ( SELECT 'X' FROM PS_PG_PNDTMP_TAO4 I WHERE I.PROCESS_INSTANCE = 1735707 AND I.PROCESS_INSTANCE = P.PROCESS_INSTANCE AND I.GROUP_BU = P.GROUP_BU AND I.GROUP_ID = P.GROUP_ID AND I.CUST_ID = P.CUST_ID AND I.ITEM = P.ITEM AND I.ITEM_LINE = P.ITEM_LINE AND I.GROUP_SEQ_NUM = P.GROUP_SEQ_NUM AND I.ANCHOR_BU <> P.BUSINESS_UNIT) */ UPDATE PS_PG_PENDST_TAO4 SET PS_PG_PENDST_TAO4BUDGET_REF = '2023' WHERE PROCESS_INSTANCE = 1735707 AND SYSTEM_DEFINED = 'Y' AND EXISTS ( SELECT 'X' FROM PS_PG_PNDTMP_TAO4 I
WHERE I.PROCESS_INSTANCE = 1735707 AND I.PROCESS_INSTANCE = PS_PG_PENDST_TAO4.PROCESS_INSTANCE AND I.GROUP_BU = PS_PG_PENDST_TAO4.GROUP_BU AND I.GROUP_ID =PS_PG_PENDST_TAO4.GROUP_ID AND I.CUST_ID = PS_PG_PENDST_TAO4.CUST_ID AND I.ITEM = PS_PG_PENDST_TAO4.ITEM AND I.ITEM_LINE = PS_PG_PENDST_TAO4.ITEM_LINE AND I.GROUP_SEQ_NUM = PS_PG_PENDST_TAO4.GROUP_SEQ_NUM AND I.ANCHOR_BU <> PS_PG_PENDST_TAO4.BUSINESS_UNIT)
/
-- 2023-11-01 11:33:05.677 Process 1735707 ABENDED at Step AR_PGG_SERV.MNTINTR1.Step01 (Action SQL) -- RC = 8601
ROLLBACK
/
-- 2023-11-01 11:33:06.403 SQL Error: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'PS_PG_PENDST_TAO4BUDGET_REF'.
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (SQLSTATE 42000) 8180 "
The abend of the AR Update program prevents the Maintenance Worksheet transaction to be fully processed, including the affected Items/Invoices which cannot be closed/updated, and not generating the needed Accounting Entries to be later interfaced into FIN General Ledger module.
The codeline contained within Step AR_PGG_SERV.MNTINTR1.Step01 should be compatible with all Database Platforms supported, not only Oracle.
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 |