Timesheet List View Total Hours is higher than Timesheet Details Entries Total Hours

Document ID : KB000024808
Last Modified Date : 14/02/2018
Show Technical Document Details

Problem:

If a Timesheet Detail Entry view is showing the individual time correctly but the total time on the Timesheet List View is higher than it should be, this might be due to orphaned time entries.  This can happen if an investment is deleted or an assignment is deleted when the Timesheet is in different states.  

Cause:

Caused by CLRT-73802

Resolution:

Our sustaining team reviewed CLRT-73802 and closed it as they are not planning to make any changes to this area of the product.

Use the identified workaround to resolve this issue.

Workaround:

The problem is that if a Timesheet is returned or in an open status (unsubmitted), then the system will still allow the project to be deleted using the 'Delete Investments' job.

On Demand Customers: Contact CA Technical Support for confirmation of this issue for resolution.  

On Premise Customers:

Please execute the following queries to see if there are some Timesheet Entry Records that do not have an associated Resource Assignment Record. 

If there are orphaned time entry records, missing the resource assignment, this article has a pre-approved statement to allow you to remove the orphaned Time Entry record.

Open the Timesheet Entry detail page with the list of time entries.  The resource id and timeperiod id should appear in the URL.

In this scenario, the user is reporting that the total hours is 20 hours more than it should be when looking at the Timesheet List View for 'Total' hours on each timesheet in the listing.

  select ts.prid, ts.prstatus, ts.pradjustedid, te.prid, te.practsum/3600, 
  te.prassignmentid 
  from prtimeentry te, prtimesheet ts 
  where te.prtimesheetid = ts.prid 
  and ts.prresourceid = xxxxxxx 
  and ts.prtimeperiodid = yyyyyyy 

Example Query:

  select ts.prid, ts.prstatus, ts.pradjustedid, te.prid, te.practsum/3600, 
  te.prassignmentid 
  from prtimeentry te, prtimesheet ts 
  where te.prtimesheetid = ts.prid 
  and ts.prresourceid = 5002335 
  and ts.prtimeperiodid = 5000033

Example Query Results:

  prid      prstatus    pradjustedid       prid      (No column name)   prassignmentid 
  5007039     3              NULL         5071791        15                5017102 
  5007039     3              NULL         5071792         6                5017297 
  5007039     3              NULL         5071801        16                5017496 
  5007039     3              NULL         5071789         1                5013871 
  5007039     3              NULL         5071790         2                5014425 
  5007039     3              NULL         5066975        20                5017739 

Since the reported problem is that it is showing 20 hours more than it should have, this points to assignment id 5017739 as a possible orphaned time entry.

The next query will tell us if the assignment id is valid:

  select prid, prtaskid, prresourceid 
  from prassignment 
  where prid = xxxxxxx  (plug in value of suspect prassignmentid)
Example Query: 
  select prid, prtaskid, prresourceid 
from prassignment
where prid = 5017739

 

If the queries do not return any records, it indicates an orphaned time entry without an assignment.  As an On Premise customer you can use the following steps to remove the 'orphaned' time entry record.

1. Be sure to take a back up copy of Production Database and fully test in a non-production system before taking action in Production

2. The following queries can be used by replacing the values for the internal record ID values as noted 

delete from prnote where 
  prtablename = 'PRTimeEntry' and 
  prrecordid = xxxxxx
  xxxxxx represents the internal record id for the note (PRNote.prrecordid) 
delete from prtimeEntry where prid = xxxxxx and prassignmentid = yyyyyyy
  xxxxxx represents the time entry id (PRTimeEntry.prid) 
  yyyyyy represents the assignment id (PPTimeEntry.prassignmentid) 
Example Query: 
  delete from prnote where 
prtablename = 'PRTimeEntry' and prrecordid = 5066975 ;
delete from prtimeEntry where 
prid = 5066975 and prassignmentid = 5017739 ;
  commit ;  

This should only delete 1 record from each delete statement as per this example. A commit should be run afterwards to ensure the delete is done.

3. Go into the application and check the Timesheet List View Totals

 

Additional Information:

For more details please review the Customer Reported Defect Resolution Policy for CA PPM   

Please report this to CA Technical Support for tracking more occurrences of this issue in the event that this issue is reviewed at a later time for reconsideration.