A user has Duplicate (2) timesheets for the same date and neither is editable

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

There are Duplicate (2) timesheets for the same resource for the same time period. When I try to edit one of the timesheets, I get the following timesheet error message:

Error message in Classic PPM timesheets:

TMA-0111: Error updating timesheet. Couldn't obtain lock. Click browser's back button and try again.

Or

Error message in New User Experience (UX) Timesheet:

java.lang.Exception: Error encountered populating timesheet from populateCreatedTimesheetWithOptionOverrride

Notes: 

• You might only see that there are duplicate timesheets and not notice the error message.
• You might also only see the TMA-0111 error message before realizing there is a duplicate timesheet, hence the reason for the error message.

Environment

Release:
Component:

Cause

A duplicate timesheet can be created when a user is attempting to create a timesheet and the user gets impatient, hits the back button, and starts over. The original is created as well as the retry.

Resolution

A new job has been introduced to delete the duplicate timesheet(s)

Reference

Delete Duplicate Timesheets Job

 

In limited customer cases, Clarity erroneously creates duplicate timesheets for a given time period. Customers requested the capability to help identify duplicate timesheets associated with a single time period/resource and remove them from the system in order to improve their internal timesheet audit processes. The new job helps the administrator identify the duplicate timesheets associated with a single time period and a resourceID and removes them from the system. Like all Clarity jobs, it can be executed manually or via a pre-determined schedule.

 

If you have duplicate posted timesheets the job will not find those and they may need to be deleted manually. 

Contact support for that as we want to make sure that the correct timesheet gets deleted.

If you do not have the job available, you can run these statements to identify and delete the duplicate:

 

You can see all the duplicate timesheets and select the ones you want to delete.

STEP 1: Identify the timesheet ID:

Run the following queries to find the duplicate timesheet prids.

Find duplicate timesheets based on dates/resource

 

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('06/30/2016','mm/dd/yyyy')

and prtimesheet.prstatus < 4

group by srm_resources.id,srm_resources.full_name,

prtimeperiod.prstart, prtimesheet.prtimeperiodid

having count(*) > 1
 

For this example:

Where 6/30/2016 is the prstart date of the time period of timesheet minus 1 day. Change the date in query above to the prstart date of the time period of timesheet minus 1 day for the duplicate timesheets.

Find the timesheet for the resource for the time period and copy the res_id for the resource for the queries listed below.

Get timesheet prid use the results from query 1

select srm_resources.id as res_id,srm_resources.full_name, prtimesheet.PRID as prtimesheetid,

prtimeperiod.prstart,prtimesheet.prtimeperiodid, prtimeentry.*

from prtimesheet, prtimeperiod, prtimeentry, srm_resources

where prtimesheet.prresourceid = srm_resources.ID

and prtimesheet.prtimeperiodid = prtimeperiod.prid

and prtimesheet.PRID = prtimeentry.PRTIMESHEETID (+) -- get timeentry (hours/assignment)

and prtimeperiod.prstart = to_date('07/01/2016','mm/dd/yyyy')

and prtimesheet.PRRESOURCEID = AAAAAAA

Note: AAAAAAA is the res_id (seven digit resource id number) for the resource returned from query 1

Copy the prtimesheetid for both timesheets returned for the resource for the queries listed below.

Take a good look at both timesheets in Clarity. Clarity -> Timesheets -> Find and select one of the duplicate timesheets for the resource for the time period by clicking on the timesheet link. Copy the 7-digit number after the editTimesheet&id=1111111 in the URL web address bar. Compare these numbers to the numbers returned from the query above.
 

STEP 2: Delete the duplicate timesheets:

Note: Be sure to back up database and fully test on a DEV/TEST environment before implementing in a PRODUCTION environment.
 

delete from prtimeentry where prtimesheetid in (BBBBBBB);

delete from prtimesheet where prid in (BBBBBBB);

 

Note: BBBBBBB is the timesheet id returned from the query above. If there are multiple duplicate timesheets, separate the numbers within the parenthesis with commas (BBBBBBB, CCCCCCC).