My Oracle Support Banner

EBI 9.2: “ Incorrect Syntax Near the Keyword ‘ORDER’ “ Error Message Triggered When Clicking the Look-Up Icon on the From/to Invoice Fields From the Apply VAT Defaults Run Control ID Page (RUN_BI_VATDFLTS) (Doc ID 2992086.1)

Last updated on DECEMBER 10, 2023

Applies to:

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

Symptoms

A Fatal SQL Error Message is being triggered by the system when clicking on the Look-Up icons for the From Invoice and To Invoice fields at the Apply VAT Defaults Run Control ID page (Panel RUN_BI_VATDFLTS).

The delivered codeline seems to be working just fine in Oracle Database Platform, but is being built in a format that is incompatible with MSSQL Database Platforms.

REPLICATION STEPS:

    1.- Select a Microsoft SQL Server Database for the test
    2.- Log into the FIN Online Application as a Billing User
    3.- Navigate to: Billing > Interface Transactions > Apply VAT Defaults
    4.- Add a new Run Control ID with the below values:
           a) Run Control ID = OSS
           b) Language = English
           c) Range Selection = Invoice ID
           d) Business Unit = FRAE1
    5.- Click on the Look-Up icon available on the 'From Invoice' or 'To Invoice' fields, either one would be fine
    6.- PROBLEM: The system throws a Fatal SQL Error Message

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

ERROR MESSAGE:

    " A Fatal SQL Error occurred. Please consult your system log for details.

      COM Stmt=SELECT INVOICE FROM (SELECT DISTINCT HDR.INVOICE FROM PS_BI_HDR HDR, PS_BI_HDR_VAT VAT, PS_BI_LINE LN WHERE HDR.BUSINESS_UNIT = VAT.BUSINESS_UNIT AND HDR.INVOICE = VAT.INVOICE AND HDR.BUSINESS_UNIT = LN.BUSINESS_UNIT AND HDR.INVOICE = LN.INVOICE AND HDR.BUSINESS_UNIT = 'FRAE1' AND HDR.BILL_STATUS NOT IN ('FNL', 'INV') AND HDR.PROCESS_INSTANCE = 0 AND ((VAT.DFLTS_APPLIED <> 'Y' AND VAT.VAT_HDR_IND <> 'Y') OR LN.VAT_DFLT_DONE_FLG <> 'Y')) ORDER BY 1

      RC=8601 Dur=0.002000 EXE

      RC=0 Dur=0.000000 RCD rtncd=8601

      RC=0 Dur=0.000000 EPO error pos=0

      RC=0 Dur=0.000000 XER rtncd=245 msg=[Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'ORDER'.

      [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement(s) could not be prepared.

      [Microsoft][SQL Server Native Client 11.0][SQL Server]Executing SQL directly; no cursor. (SQLSTATE 01000) 16954

      RC=0 Dur=0.000000 Rollback

     RC=0 Dur=0.000000 Disconnect

      RC=0 Dur=0.003000 Destroy Connection Handle=0000020B755CE018 "


The Users are unable to launch the Process VAT Defaults (BIPVAT00 AE Program) on selected Invoice transactions, which delays the handling of them on further tasks/actions, potentially affecting the revenue stream.

The From Invoice and To Invoice Look-Up features should work regardless of what Database Platform the environment is built upon, (Oracle, Microsoft SQL Server, DB2, etc...).

NOTE: In the images/screenshots/examples mentioned and/or 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.