Orphan records are created in tables such as bpm_run_steps, bpm_run_objects

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Steps to reproduce this behaviour are as follows:

1. Create a process. Create a Gel Script within it using the following:
   <gel:script
 xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary">
    <gel:log>Logging...</gel:log>
</gel:script>
2. The process can contain just the start and finish steps. Validate and Activate the process
3. Execute the process
4. Delete the executed instance
5. Use the query below to check if any orphan records are present in the bpm_run_steps table
   SELECT brp.id
    ,brs.process_instance_id
FROM bpm_run_steps brs
LEFT JOIN bpm_run_processes brp ON brp.id = brs.process_instance_id

Expected Results: No orphan records are left in the bpm_run_steps, bpm_run_objects and other related tables.

Actual Results: Orphan records are left behind.

Environment

Version: 16.1.1

Cause

DE70006

Resolution

DE70006 is fixed in version 16.1.3.

Additional Information

Defect fix in 16.1.3

Deletion of action items and related records are omitted from deletion as part of the delete process instance job and will be deleted when action items are being deleted.
The above is required in order to retain the action items and render them correctly.

However, due to the bug process artifacts such as run_objecs and run_steps without an action item being orphaned. 
The same is being fixed now in the delete process instance flow as well as the cleanup upgrade script. This is available in 16.1.3

Even after this fix, there will be certain records that will remain in the database until the action items are purged (through the purge action items job), the same should not be considered as orphan records.

Script to remove orphan records while in 16.1.2:

--delete orphan bpm_run_object step records
            delete from bpm_run_objects
            where pk_id in (select id from bpm_run_steps brs
            where 0 = (select count(1) from bpm_run_processes brp where brp.id = brs.process_instance_id )
            and 0 = (select count(1) from bpm_run_step_action_results brsar where brsar.step_instance_id = brs.id and brsar.ai_id is not null ) )
            and table_name = 'BPM_RUN_STEPS';

--delete orphan bpm_run_object process records
            delete from bpm_run_objects
            where 0 = (select count(1) from bpm_run_processes brp where brp.id = pk_id  )
            and table_name = 'BPM_RUN_PROCESSES'
            and 0 = (select count(1) from cal_action_items cai2 where cai2.object_id = pk_id );

--delete orphan bpm_run_assignees
            delete from bpm_run_assignees
            where pk_id in ( select id from bpm_run_steps brs where 0 = (select count(1) from bpm_run_processes brp where brp.id = brs.process_instance_id ) )
            and table_name = 'BPM_RUN_STEPS';

--delete orphan bpm_step_action_results
            delete from bpm_run_step_action_results where step_instance_id in (select id from bpm_run_steps brs
            where 0 = (select count(1) from bpm_run_processes brp where brp.id = brs.process_instance_id )
            and 0 = (select count(1) from bpm_run_step_action_results brsar where brsar.step_instance_id = brs.id and brsar.ai_id is not null) );

--delete orphan bpm_run_steps
            delete from bpm_run_steps
            where 0 = (select count(1) from bpm_run_processes brp where brp.id = process_instance_id )
            and 0 = (select count(1) from bpm_run_step_action_results brsar where brsar.step_instance_id = id and brsar.ai_id is not null );