EAR 9.2: Incorrect Syntax Error With Update SQL Statement On PS_FS_MAP_SRC_FLD When Creating Map Definition In An MSSQL Database
Last updated on APRIL 20, 2018
Applies to:PeopleSoft Enterprise FIN Receivables - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.
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.
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.
" 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 "
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.
The Map Definition component should be able to handle the configuration steps regardless of the Database Platform being used.
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