Not able to see Action Item on the organizer.
It showing the following error message "System error. Contact system administrator" and we can't see the actions.
Also in app-ca.log appears the following errors:
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
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).
All CA PPM Versions.
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
problem 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
problem 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 de ppm>
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.