EAR 9.2: Incorrect Syntax Error With Update SQL Statement On PS_FS_MAP_SRC_FLD When Creating Map Definition In An MSSQL Database (Doc ID 2389333.1)

Last updated on AUGUST 03, 2022

Applies to:

Symptoms

ISSUE:

Users are getting an Error Message when trying to add a new Map Definition in the system, using Delimited File functionality, in an MSSQL Database Platform.

This seems to be Platform specific, as the Error Message is not happening on an Oracle Database.

REPLICATION STEPS:

     1.- Log into a MicroSoft SQL Server FSCM 9.2 Environment as User ID VP1
     2.- Navigate to: Set Up Financials/Supply Chain > Common Definitions > Data Export Import Utility > Map Definition
     3.- Define a new Map ID value, and click on the ADD button
     4.- In the Step 1 (Map), define the below values:
          - Map ID = MAPDEFN_1
          - Description = OSS Map Definition 1
          - Active = Y
          - Version = 1
          - Owner = FAR
          - Role Name = System Administration
     5.- Click on the NEXT button
     6.- In the Step 2 (Source), define the below values:
          - Delimited File = Y
          - Layout ID = EDI_820_DELIMITED_FF
          - Character Set = UTF-8
     7.- Click on the NEXT button
     8.- The system throws an Error Message

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:

     " Return: 8601 - [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'A'.
       [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'WHERE'.
       [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not

       Statement: UPDATE PS_FS_MAP_SRC_FLD A SET A.FS_MAP_SEQ=(SELECT B.FS_MAP_SEQ FROM PS_FS_MAP_FLD_TMP B WHERE A.FS_MAP_ID=B.FS_MAP_ID AND B.FS_MAP_DATA_TYPE=:1 AND A.FS_MAP_FLD_DISP=B.FS_MAP_FLD_DISP ) WHERE A.FS_MAP_ID=:2 AND EXISTS (SELECT 'X' FROM PS_FS_MAP_FLD_TMP C WHERE A.FS_MAP_ID=C.FS_MAP_ID AND C.FS_MAP_DATA_TYPE=:3 AND A.FS_MAP_FLD_DISP=C.FS_MAP_FLD_DISP AND A.FS_MAP_SEQ<>C.FS_MAP_SEQ)

       Original Statement: update PS_FS_MAP_SRC_FLD a set a.fs_map_seq=(select b.fs_map_seq from PS_FS_MAP_FLD_TMP b where a.fs_map_id=b.fs_map_id and b.fs_map_data_type=:1 and a.fs_map_fld_disp=b.fs_map_fld_disp ) where a.fs_map_id=:2 and exists (select 'x' from PS_FS_MAP_FLD_TMP c where a.fs_map_id=c.fs_map_id and c.fs_map_data_type=:1 and a.fs_map_fld_disp=c.fs_map_fld_disp and a.fs_map_seq<>c.fs_map_seq)

       ErrorReturn-> 280 - SQL error in Exec. (2,280) FS_MAP_WIZ_WRK.FS_MAP_OK_PB.FieldChange Name:refreshFieldList PCPC:18015 Statement:231
       Called from:FS_MAP_WIZ_WRK.FS_MAP_OK_PB.FieldChange Name:bufferDataPrepare Statement:342
       Called from:FS_MAP_WIZ_WRK.FS_MAP_OK_PB.FieldChange Name:doComponentSave Statement:399
       Called from:FS_MAPPER_ENGINE.MapperWizard.ProcessEventHandler.OnExecute Name:Execute Statement:79
       Called from:EOTL_WIZARD.Controller.OnExecute Name:DispatchEvent Statement:385
       Called from:EOTL_WIZARD.ProcessDefn.OnExecute Name:GotoS "

ACTUAL RESULTS:

The required Map Definition cannot be defined correctly, and as such, is preventing EDI 820 Payment files to be uploaded into the PeopleSoft systems, delaying the processing of payments and their invoices.

EXPECTED BEHAVIOR:

The Map Definition component should be able to handle the configuration steps regardless of the Database Platform being used.

Changes

Cause

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