E-AWE: Worklist Creation Issue with Approval Framework Transactions (Doc ID 1350054.1)

Last updated on AUGUST 02, 2016

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.49 to 8.50 [Release 8.4]
PeopleSoft Enterprise SCM eProcurement - Version 9 to 9.1 [Release 9]
PeopleSoft Enterprise PT PeopleTools - Version 8.50 to 8.51 [Release 8.4]
PeopleSoft Enterprise FIN Payables - Version 9 to 9.1 [Release 9]
PeopleSoft Enterprise HRMS Talent Acquisition Manager - Version 9 to 9.1 [Release 9]
Information in this document applies to any platform.
This document will benefit those running on DB2 and MicroSoft SQLServer Database Platforms the most.


Symptoms

With concurrent processing of  Approval Framework (AWE) Approvals for  those transactions configured to create Worklist are failing with  Deadlocks or Timeout on but not limited to the DB2, MicroSoft SQLServer, and Sybase database platforms.

ERROR
---------------
PSSUBHND_dflt.3555526 (5) [05/10/11 15:57:41 SubConProcess](3) File: /vob/peopletools/src/pspcm/pcmosql.cppSQL error. Stmt #: 758 Error Position: 0 Return: 8608 - [IBM][CLI Driver][DB2/AIX64] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "2". SQLSTATE=40001
(SQLSTATE 40001) -911
Failed SQL stmt:SELECT FILL.BUSPROCNAME,FILL.ACTIVITYNAME,FILL.EVENTNAME,FILL.WORKLISTNAME,FILL.INSTMAX FROM PSWLINSTMAX FILL WHERE BUSPROCNAME = :1 AND ACTIVITYNAME = :2 AND EVENTNAME = :3 AND WORKLISTNAME = :4
PSSUBHND_dflt.3555526 (5) [05/10/11 15:57:41 SubConProcess](1) GenMessageBox(200, 0, M): /vob/peopletools/src/pspcm/pcmosql.cpp: A SQL error occurred. Please consult your system log for details.
PSSUBHND_dflt.3555526 (5) [05/10/11 15:57:41 SubConProcess](0) SQL error. Function: Rowset.Fill
Error Position: 0
Return: 8608 - [IBM][CLI Driver][DB2/AIX64] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "2". SQLSTATE=40001
(SQLSTATE 40001) -911
Statement: SELECT FILL.BUSPROCNAME,FILL.ACTIVITYNAME,FILL.EVENTNAME,FILL.WORKLISTNAME,FILL.INSTMAX FROM PSWLINSTMAX FILL WHERE BUSPROCNAME = :1 AND ACTIVITYNAME = :2 AND EVENTNAME = :3 AND WORKLISTNAME = :4
Original Statement: %SelectAll(:5 FILL) WHERE BUSPROCNAME = :1 AND ACTIVITYNAME = :2 AND EVENTNAME = :3 AND WORKLISTNAME = :4

--Exception

Fatal SQL error occurred. (2,125) PT_WF_WORKLIST.WorklistEntry.OnExecute Name:GetNewWorkInstance PCPC:2627 Statement:41 Called from:PT_WF_WORKLIST.WorklistEntry.OnExecute Name:Create Statement:99 Called from:EOAW_CORE.WorklistManager.OnExecute Name:CreateEntryFromRecord Statement:32 Called from:EOAW_CORE.WorklistManager.OnExecute Name:CreateEntry Statement:11 Called from:EOAW_CORE.NotificationEventHandler.OnExecute Name:createWLEntry Statement:1619 Called from:EOAW_CORE.NotificationEventHandler.OnExecute Name:OnStepActivate


The issue can be reproduced with the following steps, for the Expense Transactions:

1. Setup of IB for Expense processing, including up to 20 sub handlers.Tracing on at App Server level to trace sub handler. (TraceSQL=31, TracePC=448)
2. Concurrent Processing for Submission of Expense Transactions.
3. Examine Trace file for exceptions.

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