We have a user that has 2 timesheets for the same date and neither is editable. How did this even happen and how can I get rid of it

Document ID : KB000027031
Last Modified Date : 04/02/2019
Show Technical Document Details
Issue:

Issue :

There are 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:

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

Or

Error message in New UX Timesheet:

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

 

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:

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

;

 

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

Additional Information:
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.