My Oracle Support Banner

Online Journal Edit Errors Out With Error - "[SQL Server]'TO_DATE' is not a recognized built-in function name." (Doc ID 2506371.1)

Last updated on FEBRUARY 25, 2019

Applies to:

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

Symptoms

On :  FSCM 9.2 Image 28, Journal Edit

Using SQL server database:

Online journal edit errors out when Accounting Date is not current - had not tested this scenario before going live.

ERROR
-----------------------
Message Log: 
File: e:\XXXX\aedebug.hSQL 
error. Stmt #: 1725  Erro r Position: 0  Return: 8617 - [Microsoft][ODBC 
Driver 13 for SQL Server][SQL Server]'TO_DATE' is not a recognized built-in 
function name. [Microsoft][ODBC Driver 13 for SQL Server][SQL 
Server]Statement(s) could not be prepared. [Microsoft][ODBC Driver 13 for SQL 
Server][SQL Server]Ex Failed SQL stmt: SELECT A.ACCOUNT_TYPE , A.BOOK_CODE 
FROM PS_GL_ACCOUNT_TBL A WHERE A.SETID = 'SHARE ' AND A.ACCOUNT = 'XXXXXX' 
AND A.EFFDT=( SELECT MAX(EFFDT) FROM PS_GL_ACCOUNT_TBL B WHERE B.SETID=A. 
SETID AND B.ACCOUNT=A.ACCOUNT AND 
B.EFFDT<=TO_DATE('2018-10-31','YYYY-MM-DD')) AND 'G' = 'G' 


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
Set up and configuration: 
 - Installation Options:  Project Costing enabled.
 - Ledger Group Definition 
       o    Ledger Group = RECORDING 
       o    Ledger = LOCAL
 - Ledger Group Balancing:  IntraUnit Balancing Entries selected 
 - SpeedType 1:   
      SpeedType Key = ST_001 
        Fund Code 
        Department 
        Class Field
 - SpeedType 2:   
      SpeedType Key = ST_002 
        Fund Code 
        Department 
        Class Field 
        PC Business Unit 
        Project 
        Activity
 - Chartfield Combo Definition 1: 
     Combo Definition = ACCT_DP_PJ:  (Account+Department+Project)
     Combination Rule 1:  Combination Rule = ACCT_DP_PJ 
          Non-Anchor CF: 
          Department - Value Required 
          Project - Value Required
     CF Combination Editing Group 1: 
          Process Group = ACCT_DP_PJ 
          Combination Defn = ACCT_DP_PJ 
          Anchor Values Not In Rules = Mark Valid 
          Combination Group Defines = Valid Combinations 
          Combination Rule = ACCT_DP_PJ 
 - Chartfield Combo Definition 2: 
     Combo Definition = ACCT_FUND:  (Account+Fund Code)
     Combination Rule 2:  Combination Rule = ACCT_FUND 
          Non-Anchor CF: 
          Fund Code - Value Required 
     CF Combination Editing Group 2: 
          Process Group = ACCT_FUND 
          Combination Defn = ACCT_FUND 
          Anchor Values Not In Rules = Mark Valid 
          Combination Group Defines = Valid Combinations 
          Combination Rule = ACCT_FUND 
 - Ledgers for a Unit - Journal Edit Options: 
          Business Unit = US001 
          Ledger Group = RECORDING 
          CF Combo Edit Process Groups: 
              ACCT_DP_PJ 
              ACCT_FUND
 - Build Combination Date Request 
          Business Unit = US001 
          Ledger Group = RECORDING 
 - FS_CEBD Process Name
   Process Instance = 173442
  
 - Grants related setup:  Facilities Admin Options
  
  
Test: 

1.  Using JRNL1_WS, populate defaults.
2.  Populate journal header details.
3.  Populate journal lines details using the SpeedTypes.
4.  Import Now.   Journal ID 0000000621
5.  Validate using Edit Journal Request (GL_JEDIT) 
     Process Instance = 173450
     Run status = No Success


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.