EAR 9.2: Invalid Column Name 'SYSDATE' Error Message When Running AR30009 SQR Report In MSSQL Server Database

(Doc ID 2215277.1)

Last updated on DECEMBER 20, 2016

Applies to:

PeopleSoft Enterprise FIN Receivables - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

ISSUE:

The delivered AR30009 SQR Report (AR Aging by Department) seems to have an incompatibility issue when using MicroSoft SQL Server 2012 Database Platform. The process, when launched, goes into Error, stating at the Message Log level that the " Process Request shows status of 'INITIATED' or 'PROCESSING' but no longer running ". However, at the system output log file, it clearly states that the codeline Statement could not be prepared. This seems to be a database-related issue, specific to MSSQL.

REPLICATION STEPS:

     1.- Have a PeopleSoft Enterprise FSCM 9.2 Image #20 environment using MicroSoft SQL Server 2012 Database Platform
     2.- Log into the FSCM Online Application as User ID VP1
     3.- Navigate to: Accounts Receivable > Receivables Analysis > Aging > Aging by Department Report
     4.- Create a brand new Run Control ID with the below settings:
          - Run Control ID = GCS
          - Language = English
          - As of Date = Today's Date
          - Use System Date = N
          - Set ID = SHARE
          - Aging ID = STD
          - Amount Type = Base Curr
          - Rate Type = Blank
          - Department = 1
          - Customer ID = Blank
          - Contract = Blank
          - Display Option = Include All
          - Business Unit = US001
     5.- Save the new Run Control ID
     6.- Click on the RUN button
     7.- Select the AR30009 SQR Report rows
     8.- Click on the OK button
     9.- Navigate to: PeopleTools > Process Scheduler > Process Monitor
     10.- Find the Process Instance to the AR30009 process launched, and confirmed that it is in Error status

To gather more information concerning this scenario and its related problem, refer to the available Replication Steps Word Document here linked containing the complete configuration and the replication steps necessary to reproduce the issue.

ERROR MESSAGE:

     " Process Request shows status of 'INITIATED' or 'PROCESSING' but no longer running "

     " (SQR 5528) ODBC SQL dbexec: SQLExecute error 207 in cursor 20:
       [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'SYSDATE'.
       (SQR 5528) ODBC SQL dbexec: SQLExecute error 145 in cursor 20:
       [Microsoft][SQL Server Native Client 11.0][SQL Server]ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
       (SQR 5528) ODBC SQL dbexec: SQLExecute error 8180 in cursor 20:
       [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared.
       Error on line 333:
       (SQR 3723) Problem executing cursor.
       SQR for PeopleSoft: Program Aborting. "

ACTUAL RESULT:

The AR Aging by Department delivered Report (AR30009 SQR Report) cannot be used, as the delivered codeline is having issues with MicroSoft SQL Server 2012 platform. Therefore, functional Users are unable to get the analysis of Customer's aging items by departments.

EXPECTED BEHAVIOR:

The AR30009 SQR Report (AR Aging by Department) should be able to work with any Database Platform that PeopleSoft Enterprise FSCM 9.2 Application Release has been certified on.

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms