My Oracle Support Banner

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 JANUARY 30, 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:

    " 11:33:04.116 ................(AR_PGG_SERV.MNTINTR1.Step01) (SQL)

      /*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.

 

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


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