Key points to consider before taking any action with this stored procedure (SP):
1. The SP will run against multiple table in the workpoint DB. You can view the list of tables from its nested stored procedure spWP_DELETE_JOBS_BYLEVEL, which also comes with the installation. The WP_PROCI table specifically contains the main handle for a job/worklist object used at runtime. It is where IM pulls the worklist items from. These items are connected to records in the tasksession12_5 table (i.e. parent and approval tasks). Therefore we should be mindful of what we decide to remove.
2. In the WP_PROCI table there's a column called PROC_STATE_ID. This is mapped to the table WP_PROC_STATE which lists the states:
3. In the WP_PROCI table there's also column called LU_ID. In order for spWP_DELETE_JOBS to clear jobs, the LU_ID value of that record should be set to Delete Job. Please make sure the PROC_STATE_ID of the records that you want deleted are in an end state and not active.
4. BEFORE setting the LU_ID to Delete Job on the unwanted records in the WP_PROCI table and running the SP, you will need to shutdown all application server nodes. If you don't, the application server will continue to load the transactions associated with these worklist items from the JMS queue and flood the log file with retries indefinitely. The retries will always fail because the associated worklist item no longer exists. A restart thereafter will not resolve the problem, only a clearing of the JMS queue will resolve this. So please have all nodes shutdown before running it.