Duplicate timesheets are displayed

Document ID : KB000004723
Last Modified Date : 06/04/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 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, count(*)
from prtimesheet, prtimeperiod, srm_resources
where prtimesheet.prresourceid = srm_resources.ID
and prtimesheet.prtimeperiodid = prtimeperiod.prid
and srm_resources.id = xxxxxxx
group by srm_resources.id,srm_resources.full_name,
prtimeperiod.prstart, prtimesheet.prtimeperiodid
having count(*) > 1

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