CA PPM: Duplicate timesheets are displayed or exists

Document ID : KB000004723
Last Modified Date : 19/07/2018
Show Technical Document Details
Issue:

Records in the PRTIMESHEET table sometimes can be created as duplicate due to a sequence of actions the user does when creating or updating a timesheet.  A duplicate timesheet can be created when a user is attempting to create a timesheet and the user gets impatient, hits the browser ‘Back’ button and starts over.  The original Timesheet record is created as well as a second record with duplicate period details.

If a duplicate does occur, it can introduce an issue in the New UI Timesheet interface where if a user tries to edit/populate timesheets, the error message appears:
"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"

However, the error does not reflect the root cause.

 

 

Cause:

Since the timesheet is not an exposed object for auditing, the duplication/false error can occur.

Steps to create a duplicate timesheet are not known at this time, however we do know that the issue does appear to be triggered when a duplicate timesheet is created. When action is taken to remove the duplicates, an incorrect record might be getting removed.

The problem occurs when the
PRTIMESHEET.prApprovedBy is null for an approved timesheet or
PRTIMESHEET.prSubmittedBy has a null value for a submitted timesheet.

 

Resolution:

Run the following queries:

--a (To detect the existence of potential duplicate timesheets)
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('xx/xx/xxxx','mm/dd/yyyy')
and prtimesheet.prstatus < 4
group by srm_resources.id,srm_resources.full_name,
prtimeperiod.prstart, prtimesheet.prtimeperiodid
having count(*) > 1
 

--b (To detect the existence of duplicate timesheets, one that is in Posted status and one in Open status)

select srm_resources.id as res_id, srm_resources.full_name, prtimeperiod.prstart,
prtimesheet.prtimeperiodid, prtimesheet.prstatus, prtimesheet.prid, prtimesheet.pradjustedid
from prtimesheet, prtimeperiod, srm_resources
where prtimesheet.prresourceid = srm_resources.ID
and prtimesheet.prtimeperiodid = prtimeperiod.prid
and srm_resources.id = xxxxxx --provide the resource id from the above query
order by prtimesheet.prtimeperiodid desc

Review the results and determine if there are two timesheets in the same time period that are truly duplicates with query:

select * from prtimesheet where prresourceid = xxxxxx
and prtimeperiodid = yyyyy

If one is in a 'Posted' status and another is in 'Adjusted' status, then this is not a duplicate.
If one is in a 'Posted' status and another is in 'Open' status, then this is a duplicate. The one in 'Open' status would need to be deleted.

--c (To identify submitted timesheets that have a null value for prSubmittedBy column)

select prstatus, count(*) as total, count(prsubmittedby) as good, count(*) - count(prsubmittedby) as bad
from prtimesheet
where prstatus >= 1
group by prstatus
having count(*) <> count(prsubmittedby)
order by 1  

 --d (To identify approved timesheets that have a null value for the prApprovedBy column) 

select prstatus, count(*) as total, count(prapprovedby) as good, count(*) - count(prapprovedby) as bad
from prtimesheet
where prstatus >= 3
group by prstatus
having count(*) <> count(prapprovedby)
order by 1 

If there are records returned from any of the above queries, please contact CA Support for the resolution.


 

Additional Information:
If the duplicate timesheets issue occurs very frequently in your environment, please instruct the affected end users to use the Windows Steps Recorder tool to capture the steps to reproduce the problem and then open a ticket with CA Support and share the STR video with us so that we can analyze and pin down the STR.