Shawn_Moore

CA Clarity Tuesday Tip: Delete Process Instances Job

Discussion created by Shawn_Moore Employee on Aug 30, 2011
Latest reply on Oct 12, 2012 by Chris_Hackett
Title: CA Clarity Tuesday Tip: Delete Process Instances Job

CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 8/30/2011

Before we start delving into financials, I have topic or two I'd like to cover. Today, I'd had a need to look at exactly what the delete process instance job does in terms of tables it deletes from.

This job allows you to delete process instance that have "finished" that are either in an Aborted or Done status. Filtering criteria allows you to select a finish date range, process instance obs, object type, initiated by and initiated OBS.

Here's how the deletion of the processes works:

1) The job itself will run the instance selection query and add the filter criteria. Here's the base query without the dynamic filtering components.

SELECT
INSTANCES.ID INSTANCE_ID
FROM
BPM_DEF_PROCESSES PROCESSES,
BPM_DEF_PROCESS_VERSIONS VERSIONS,
BPM_RUN_PROCESSES INSTANCES,
CMN_CAPTIONS_NLS CAPTIONS,
CMN_LOOKUPS_V LOOKUP,
SRM_RESOURCES RESOURCES
WHERE
INSTANCES.STATUS_CODE IN ('BPM_PIS_DONE', 'BPM_PIS_ABORTED') AND
VERSIONS.PROCESS_ID = PROCESSES.ID AND
CAPTIONS.PK_ID = PROCESSES.ID AND
INSTANCES.PROCESS_VERSION_ID=VERSIONS.ID AND
CAPTIONS.LANGUAGE_CODE = ? AND
CAPTIONS.TABLE_NAME = 'BPM_DEF_PROCESSES' AND
LOOKUP.LANGUAGE_CODE = ? AND
LOOKUP.LOOKUP_TYPE = 'BPM_PROCESS_INSTANCE_STATES' AND
INSTANCES.STATUS_CODE = LOOKUP.LOOKUP_CODE AND
RESOURCES.USER_ID = INSTANCES.INITIATED_BY

2) Next the job will iterate through the results gathered in step 1 and perform the following delete actions per process instance.

a) First, check if this process has any subprocesses; if so, delete subprocesses first. (this essentially will follow 2b-2d for the sub-process)
b) Delete escalation objects (action items, etc.) for the process and each step instance. Records from the following are deleted:

- ESC_RUN_USERS
- ESC_RUN_LEVELS
- ESC_RUN_UNREG_OBJECTS
- ESC_RUN_OBJECTS

c) Delete all associated steps/run records

- BPM_RUN_OBJECTS
- BPM_RUN_STEPS
- BPM_RUN_STEP_ACTION_RESULTS
- BPM_RUN_STEP_COND_OBJECTS
- BPM_RUN_STEP_COND_RESULTS
- BPM_RUN_STEP_TRANSITIONS
- BPM_RUN_ASSIGNEES
- BPM_RUN_REPLACE_ASSIGNEES
- BPM_RUN_ASSIGNEE_NOTES
- CAL_ACTION_ITEMS
- CAL_ACTION_ITEM_ASSIGNEES


d) Delete the process instance
- BPM_RUN_PROCESSES
- BPM_RUN_OBJECTS
- BPM_RUN_THREADS
- BPM_ERRORS
- BPM_ERROR_ARGUMENTS

** After every 100 delete process cycles (this is the default), Clarity will commit the transaction.

Outcomes