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 2017-04-07 16:40:22,792 [http-nio-80-exec-6098] niku.calendar (clarity:SPA12938:69990468__852F3CC4-0955-4A3A-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

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).

Environment

Any Clarity release.

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. 

NOTE 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 ); 

 

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