EEX 9.2: Expense Report Stuck In 'Submission In Process' Status (SFA) Due To EX_APPROVAL Error On MicroSoft SQL Server Database Platform (Doc ID 1960551.1)

Last updated on AUGUST 23, 2017

Applies to:

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

Symptoms

ISSUE:

Whenever an Expenses User creates and submits for approval a brand new Expense Report, the transaction gets stuck in 'Submission in Process' status, and it never completes. Checking the Subscription Contract instance for Service Operations EX_APPROVAL, it has abended in Error.

   " Return:  8608 - [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near 'C'.
     [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared. (SQLSTATE 37000) 8180 "

Such common Error Message usually denotes that the underlying codeline being executed by the system is incompatible with a MicroSoft SQL Server database.

Checked the codeline from Application Designer object Application Package PeopleCode EX_APPR_WF.TxnProcessing.Messaging.ApprovalSubscriber.OnExecute , and it contains the below SQL Statement, whom is responsible for the customer's Error:

 SQLExec("DELETE FROM PS_EOAW_USERINST C WHERE EXISTS (SELECT 'X' FROM " | &tbl_aw | " A, PS_EOAW_STEPINST B WHERE A." | &txnField | " = :1 AND A.EOAWPARENT_THREAD=:2 AND A.EOAWPRCS_ID =:3 AND A.EOAWPRCS_ID = B.EOAWPRCS_ID  AND A.EOAWDEFN_ID = B.EOAWDEFN_ID AND A.EOAWTHREAD_ID = B.EOAWTHREAD_ID  AND C.EOAWSTEP_INSTANCE = B.EOAWSTEP_INSTANCE)", &txnID, &eoawparent_thread, &eoawprcs_id);

REPLICATION STEPS:

     - Have an FSCM 9.2 MicroSoft SQL Server Database Platform
     - Log in as User ID EXA1
     - Create, and Submit For Approval a new Expense Report transaction
     - After a while, confirm that the transaction is stuck in 'Submission in Process' status
     - The ER_APPROVAL Service Operations Message Subscription Contract is abended
     - At the Process Scheduler Log file, there is 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:  8608 - [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near 'C'.
    [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared. (SQLSTATE 37000) 8180
    Statement:  DELETE FROM PS_EOAW_USERINST C WHERE EXISTS (SELECT 'X' FROM PS_EX_SHEET_AW A, PS_EOAW_STEPINST B WHERE A.SHEET_ID = :1 AND A.EOAWPARENT_THREAD=:2 AND A.EOAWPRCS_ID =:3 AND A.EOAWPRCS_ID = B.EOAWPRCS_ID  AND A.EOAWDEFN_ID = B.EOAWDEFN_ID AND A.EOAWTHREAD_ID = B.EOAWTHREAD_ID  AND C.EOAWSTEP_INSTANCE = B.EOAWSTEP_INSTANCE)
    Original Statement:  DELETE FROM PS_EOAW_USERINST C WHERE EXISTS (SELECT 'X' FROM PS_EX_SHEET_AW A, PS_EOAW_STEPINST B WHERE A.SHEET_ID = :1 AND A.EOAWPARENT_THREAD=:2 AND A.EOAWPRCS_ID =:3 AND A.EOAWPRCS_ID = B.EOAWPRCS_ID  AND A.EOAWDEFN_ID = B.EOAWDEFN_ID AND A.EOAWTHREAD_ID = B.EOAWTHREAD_ID  AND C.EOAWSTEP_INSTANCE = B.EOAWSTEP_INSTANCE)
    PSSUBHND_dflt.42228 (200) [2014-12-01T14:46:47.226 SubConProcess](1) GenMessageBox(0, 0, M): PeopleCode Manager (Update): A fatal PeopleCode SQL error occurred. Please consult your system log for details. "

BUSINESS IMPACT:

No Expense transaction submissions, or approvals, get routed properly.

EXPECTED BEHAVIOR:

The Expenses Approval Workflow should properly route the submitted Expense Reports to the appropriate Approver Users.

Cause

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