Users with Recurrences

It can be quite useful to know which users have recurrences configured in the process monitor. These are processes that users have scheduled to run on a regular basis at a scheduled time.

The following SQL will return this information from the process request table (PSPRCSRQST):

select
    p.PRCSINSTANCE,
    p.PRCSTYPE,
    p.PRCSNAME,
    p.RUNDTTM,
    p.RECURNAME,
    p.OPRID,
    o.OPRDEFNDESC,
    o.EMPLID,
    o.EMAILID,
    p.RUNCNTLID,
    p.LASTUPDDTTM
from 
    PSPRCSRQST p left outer join PSOPRDEFN o
    on p.OPRID = o.OPRID 
where 
    p.RECURNAME != ' ' 
    and p.RUNSTATUS = '5' 
;

You might be a bit curious as to why I have a left outer join to the operator definition table. Every user that has a reoccurrence in the system should exist right?

Well, turns out if the operator has been deleted, and the recurrence is left behind it can cause a bit of a problem.

For instance if you are seeing the following in your message log:

1:45:40PM  You are not authorized to run process type SQR Report and process name PRCS1.   
1:45:40PM  You are not authorized to run process type SQR Report and process name PRCS2.

And also seeing unique constraint errors on the PS_MESSAGE_LOG table like this (this is for an SQR running on Oracle):

PRCSAPI.SQC,Update-Process-Status,Update,PSPrcsRqst
SQL Status =     1, SQL Error  = ORA-00001: unique constraint (SYSADM.PS_MESSAGE_LOG) violated

Error on line 56:
   (SQR 3301) Program stopped by user request.

SQR for PeopleSoft: Program Aborting.

Then go and check your process scheduler logs for further information (SCHDLR_MMYY.LOG)

If you find something like the following, it will tell you the user that was deleted. Go back and check if this user has an recurrences configured in the system using the SQL provided at the start of this post.

=================================Error===============================
Database error encountered
 Info:     
 Section:  
 Info:     
 SQL Stmt: SELECT A.ACCESSID       ,A.ACCESSPSWD      ,A.ENCRYPTED 
           FROM PSOPRDEFN O    ,PSACCESSPRFL A 
           WHERE O.OPRID = :1  AND A.SYMBOLICID = O.SYMBOLICID 
=====================================================================
=================================Error===============================
 Msg Set:  65
 Msg #:    67
 Message:  Unable to retrieve the access profile for this user ID <OPRID> (65,67)
=====================================================================

To fix, I recommend recreating the operator ID and then cancelling the recurrences from the process monitor.

You could also try manually changing the RUNSTATUS field in the process request table from a value of 5 (scheduled) to say 8 (cancelled). However, proceed with caution if you go down this road.