Organizer error when trying to see an Action Item

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

It is not possible to see Action Item on the organizer.

• It shows the following error message

System error. Contact system administrator

• The app-ca.log shows a similar error to this:

ERROR 1776-07-04 16:40:22,792 [http-nio-80-exec-6098] niku.calendar (clarity:someUser:69968__852F3CC4--89FA-12879A81EB89:npt.overview) calendar.getAllActionItemsList_set:PMD application error com.niku.bpm.BpmException: getProcessVersionId - Cannot get process version id. Did not get exactly one rowset with one row back No of rowset: 1 No of rows: 0

Environment

Any Clarity release.

Cause

This problem is caused by orphaned action items (action items that are still in the database after the process instance that sent the action item has been deleted).

Resolution

The best way to fix this issue is to change the status of these action items via the database so that they are no longer in a status that should be shown in the action items portlet.

Here are the queries and steps to investigate the issue. 

Please, make sure you have a good and working backup of your dataset before running the updates on the DB. 

1. Run the following query to get a list of users ids that have this issue and the orphaned action item IDs that are causing the error:

SELECT assignee_id
    ,status_code
    ,cal_action_item_id from  cal_action_item_assignees
WHERE cal_action_item_id in (  SELECT id  FROM CAL_ACTION_ITEMS_OWNED_V  WHERE LANGUAGE_CODE = 'en'  
        AND (
            (
                STATUS_CODE != 'CAL_CLOSED'
                AND STATUS_CODE != 'CAL_DEFERRED' AND  STATUS_CODE != 'CAL_DONE'
                )  
            OR STATUS_CODE IS NULL
            )  
        AND NOT EXISTS (
            SELECT  o.object_type_code  FROM  BPM_DEF_OBJECTS o
            ,  BPM_RUN_PROCESSES p
            ,  BPM_RUN_STEPS s  WHERE  o.pk_id = p.process_version_id and  o.table_name = 'BPM_DEF_PROCESS_VERSIONS' and (o.is_system IS NULL
                OR o.is_system = 0)
            AND o.src_pk_id IS null  and  s.process_instance_id = p.id and  s.id = PROCESS_HANDLER_ID
            )  )

Note:

• Keep the results of this query so that you can ensure that the issue is fixed for these users after running the query in step 2. 
• Assignee_id = user_id from Clarity 

2. Run the following update statement: 

UPDATE CAL_ACTION_ITEMS
SET PROCESS_HANDLER_ID = NULL
WHERE id IN (
        SELECT id
        FROM CAL_ALL_ACTION_ITEMS_V
        WHERE LANGUAGE_CODE = 'en'  
            -- AND USER_ID = <USER_ID from Clarity> 
            AND (
                (
                    STATUS_CODE != 'CAL_DEFERRED'
                    AND STATUS_CODE != 'CAL_DONE'
                    )
                OR STATUS_CODE IS NULL
                )
            AND NOT EXISTS (
                SELECT rp.id process_instance_id
                    ,rp.process_version_id
                    ,rs.step_id
                FROM BPM_RUN_STEP_ACTION_RESULTS sar
                    ,BPM_RUN_STEPS rs
                    ,BPM_RUN_PROCESSES rp
                WHERE sar.id = PROCESS_HANDLER_ID
                    AND sar.step_instance_id = rs.id
                    AND rp.id = rs.process_instance_id
                )
            AND PROCESS_HANDLER_ID IS NOT NULL
        )

Note:

Uncomment the USER_ID = <user_ID> to affect the selected/affected user 

3. Log in as the users you found in the query from step 1 and make sure that the Action items view in the organizer works.

Additional Information

Action Item Portlet Error