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 : 14/02/2018
Show Technical Document Details

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.

 

Environment:

CA PPM 14.2, 14.3, 14.4, 15.1

 

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/Workaround:

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.