Timesheet locked by a Process that may be stuck resulting in error TMA-0118 or TMA-0122

Document ID : KB000027007
Last Modified Date : 23/05/2018
Show Technical Document Details
Issue:

There are two possible error messages that may occur if a BPM process gets stuck trying to process a timesheet.  If we look at the BPM process, we can see that there is something wrong and it is stuck or not moving forward or completing.  What can be done to unlock the Timesheet?

TMA-0122: One or more timesheets' status could not be changed because it is locked by a process when trying to run a user-defined timesheet approval process. 
TMA-0118: This timesheet has been locked by the process engine. One or more timesheets cannot be edited, submitted, approved, or posted.

Cause:
It is by design that records are locked by the process. Typically the BPM process completes as intended and the lock is released as expected. In can happen that a lock gets placed but never releases (unlock not removed) due to a BPM process stoppage.  
The BPM process is not able to resume and there is no way to release or unlock the records through the application; thereby causing the Timesheet to get stuck.
This type of timesheet lock cannot be removed through the UI using ‘security.locks’ because it is a BPM process lock.  
Also, in the current design of the application, although a BPM process for Timesheets can be defined for ‘on-demand’ start condition, it can never be initiated on the timesheet ; this prevents the possibility to create a ‘unlock’ process definition that can be used through the application to ‘unlock’ this stuck timesheet.
Once the locks are removed, the Timesheet can be approved by a user with appropriate timesheet approval access rights; instead of using the BPM process flow that was aborted
Resolution:

Run the following query to find out if any timesheets have been locked by a process.  Check the state of the BPM Process Engine and make sure that all processes have been processed.  If there are any results from this query and you have checked to ensure the BPM process is not moving forward or 'stuck' you may need assistance from CA Technical Support to release the lock on these Timesheets.

  1. Capture Screenshots of the Process error that has caused the Timesheet to remain locked 
  2. Capture Screenshots of the Process Engines page showing that the Process Engine is running fine  
  3. On Premise Customers: Provide a copy of the full bg-ca.log file  
  4. On Premise Customers: Provide the output of the following query into a Microsoft Excel spreadsheet with header rows  
select odf_locked_attributes.id, first_name, last_name, prstart, prstatus, odf_locked_attributes.odf_pk, 
   prtimesheet.prid, status_code, object_type_code
from odf_locked_attributes, prtimesheet, prtimeperiod, srm_resources, bpm_run_objects, bpm_run_processes
where object_code = 'timesheet'
and attribute_code = 'prstatus'
and odf_locked_attributes.odf_pk = prtimesheet.prid
and prtimesheet.prtimeperiodid = prtimeperiod.prid
and srm_resources.id = prtimesheet.prresourceid   
and odf_locked_attributes.odf_pk = bpm_run_objects.object_id
and bpm_run_objects.table_name = 'BPM_RUN_PROCESSES'
and bpm_run_objects.object_id = odf_locked_attributes.odf_pk 
and odf_locked_attributes.object_code = bpm_run_objects.object_type_code
and bpm_run_objects.pk_id = bpm_run_processes.id 
and status_code in ('BPM_PIS_ABORTING', 'BPM_PIS_ABORTED', 'BPM_PIS_ERROR')