My Oracle Support Banner

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 APRIL 10, 2019

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.

 

NOTE: In 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.