How to Cancel a Queued Process in PeopleCode

At one of my clients, I have a bolt-on that will automatically update security based on certain business events. For example, when an employee is terminated we need to de-activate the person’s profile. This is easily done via a component interface call to lock the account and remove all the roles. We use component interface because we want an integration broker message to trigger to syncronize any other databases.

An often overlooked item is to delete all the queued processes that the user may have on the process scheduler. These might be processes running under a recurrence like scheduled queries and jobs. These could also be single run reports that the user scheduled for a future date.

This is actually really easy to do using the “Process Request” PeopleCode API. It is easy to miss in the documentation because most of the API is about creating new requests not updating existing ones.

Here is a code snippet that will find all processes for a user in certain run statuses and delete them.

   Local SQL &sqlQueued;
   Local String &TerminatedOprid = "CMALEK";

   /* delete all process requests that are in runstatus:
             error (3), hold (4), queued (5), processing (7) */

   &sqlQueued = CreateSQL("SELECT  A.PRCSINSTANCE,  A.JOBINSTANCE,A.PRCSNAME FROM PSPRCSRQST A, PSOPRDEFN B WHERE A.OPRID = B.OPRID   AND A.OPRID = :1 AND A.RUNSTATUS IN (7, 5, 4, 3) ", &TerminatedOprid);

   Local number &instance, &jobInstance;
   Local string &prcsname;
   Local ProcessRequest &rqst;

   While &sqlQueued.Fetch(&instance, &jobInstance, &prcsname);

      &rqst = CreateProcessRequest();
      &rqst.ProcessInstance = &instance;
      &rqst.JobInstance = &jobInstance;
      &rqst.RunStatus = "2"; /* delete */
      &rqst.UpdateRunStatus();

   End-While;

I have seen issues if the user’s security is deleted, then a queued process tries to run without the proper security. It will corrupt the whole process scheduler and users will start getting errors. I had documented this in the Unique Constraint Error on PS_MESSAGE_LOG KB article.