Duplicate Timesheets Are Displayed in Clarity

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

• A duplicate timesheet is defined as 2 records displaying the same resource for the same time period.
• If a duplicate timesheet condition exists, one or more of the issues are reported:
  
  1. It can introduce an issue in the modern UX Timesheet interface where if a user tries to edit/populate timesheets, one of the errors appear:
• "The selected resource’s time tracking settings are not correct. Check that the resource’s settings have Open for Time Entry turned on and the Track Mode is set"
  or
• "java.lang.Exception: Error encountered populating timesheet from populateCreatedTimesheetWithOption"
  
  

        2. The corresponding timestamp from when the duplicate timesheet gets created appears as in the APP log as:

WARN 2019-01-29 11:19:30,242 [http-nio-14001-exec-8] niku.union (clarity:sampleuser:10499070__5A31FD04-C8D0-4573-A131-0836E706D0B6:PPM_REST_API) java.lang.Exception: Error encountered populating timesheet from populateCreatedTimesheetWithOptionOverrides:
<data> 
<header> 
<messages> 
<message id="timeadmin.TIMESHEET_LOCK_ERROR" location="" type="error" value=""/> 
</messages> 
</header> 
</data> 

2. Stock reports such as Resource Time Summary and Detail show as 'Open', which makes a timesheet submission appear as late.

Environment

All Clarity versions

Cause

The timesheet action sessions could have been duplicated if there was communication issue between the server nodes. 

 
   

Resolution

FOR CLARITY VERSIONS 15.7.1.2 AND HIGHER:

• To permanently address the problem, we have created an OOTB job called Delete Duplicate Timesheets
• The job is in 15.7.1.2, 15.8 and higher.
• Check for more information here: Delete Duplicate Timesheets Job and Delete Duplicate Timesheets Job - how to schedule it? Best practices on using Delete Duplicate Timesheets job
• Note: We recommend running this job before the Post Timesheets job, to identify and delete any duplicate timesheets.

FOR CLARITY VERSIONS 15.7.1.1 and BELOW:

• ON PREMISE ONLY

 

         1) Run the following query to see for which resources a duplicate timesheet exist

select srm_resources.id as res_id, srm_resources.full_name, prtimeperiod.prstart,
prtimesheet.prtimeperiodid, count(*)
from prtimesheet, prtimeperiod, srm_resources
where prtimesheet.prresourceid = srm_resources.ID
and prtimesheet.prtimeperiodid = prtimeperiod.prid
and prtimeperiod.prstart > to_date('01/01/2019','mm/dd/yyyy')
and prtimesheet.prstatus < 4
group by srm_resources.id,srm_resources.full_name,
prtimeperiod.prstart, prtimesheet.prtimeperiodid
having count(*) > 1

• The above returns a RES_ID column containing the resource ID of the resource that has a duplicate timesheet, and a PRTIMEPERIODID that contains the time period ID for which that duplicate timesheet exists.

RES_ID = XXXXXXX
PRTIMEPERIODID = YYYYYYY

NOTE: We will not delete any timesheets that are not returned from the query.

2) Run the following query to confirm the results returned are actually duplicate timesheets

select * from prtimesheet
where prtimeperiodid = YYYYYYY
and prresourceid = XXXXXXX

The above query will return a PRID column containing the two duplicate timesheets that exist in the same time period for the same resource.
 

PRID = ZZZZZZZ
 

        3) Run the below query to see if there are any time entry records associated with the duplicate timesheets
 

select * from prtimeentry
where prtimesheetid = ZZZZZZZ

• The above query should be run for each of the PRID's given in Step 2.
• Pick the one you would like removed, then you can remove that one.
• Otherwise, remove the duplicate that has no time entries, so take note of which ID returned 0 results for the above query. If both return 0 results, then you can select either of the PRID's.
• If one timesheet is in a 'Posted' status and another is in 'Open', check if the timesheet in the Open status can be deleted via the UI, as it would be an 'Open' adjustment to the 'Posted' timesheet.

• If one is in the 'Posted' status and another is in 'Open' status with no reference to the Posted timesheet (PRTIMESHEET.pradjustedid is null), then this is a duplicate and would need to be reviewed by the Support for an approval to delete the timesheet.

  4) Backup tables PRTIMEENTRY and PRTIMESHEET

  5) Run the commands to have the duplicate timesheet deleted:
 

delete from PRTIMEENTRY where PRTIMESHEETID in (ZZZZZZZ);
delete from PRTIMESHEET where PRID in (ZZZZZZZ);
commit;

 This should resolve the issue and remove the duplicate timesheet.

Additional Information

• There are also reports of duplication appearing in custom reports, however this is not the case.
  • As mentioned above, there could be one timesheet that was originally 'Posted' but if the timesheet gets adjusted, a new adjustment timesheet is created which references the adjusted/posted timesheet.
  • This is not a duplicate, but a Posted/Adjustment pair.
  • Reports such as the Timesheet Detail have the ability to display the timesheet statuses for a given time period, therefore the custom report will need to be developed further in order to mimic the stock reports.
  • For more information on how to view the SQL behind the stock reports, please refer to the following article https://ca-broadcom.wolkenservicedesk.com/external/article?articleId=130566