Actuals reported in Tasks is different from what is logged in Timesheets

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

It is noticed that Actual Hours shown in Tasks do not match with Actuals Hours reported in Timesheets.

Environment

Version: 15.9.3

Cause

Incorrect comparison of total Actual Hours with weekly Actual Hours from Timesheets.

Resolution

Use the following queries to compare Actual Hours in Tasks, with Actual Hours from Timesheets. Please note that data is derived from Timesheet tables here, and slices are not being used.

Total Actual Hours reported against individual Tasks can be obtained using the following query:

SELECT
    SUM(pr.practsum / 3600) hrs,
    prta.prname
FROM
    prtimesheet prt
    JOIN prtimeentry pr ON pr.prtimesheetid = prt.prid
    JOIN prassignment pra ON pr.prassignmentid = pra.prid
    JOIN prtask prta ON prta.prid = pra.prtaskid
    JOIN inv_investments ii ON ii.id = prta.prprojectid
    JOIN srm_resources sr ON prt.prresourceid = sr.id
    JOIN prtimeperiod pri ON pri.prid = prt.prtimeperiodid
WHERE
        upper(sr.unique_name) = '<Replace with Resource ID in Upper Case>'
    AND
        upper(ii.code) = '<Replace with Investment Code in Upper Case>'
GROUP BY
    prta.prname
ORDER BY SUM(pr.practsum / 3600) desc;

Actual Hours reported against Timesheets, with additional information about the periods:

SELECT
    pri.prstart,
    pri.prfinish,
    ii.name,
    sr.full_name,
    prt.prresourceid,
    pr.practsum / 3600 hrs,
    prta.prname
FROM
    prtimesheet prt
    JOIN prtimeentry pr ON pr.prtimesheetid = prt.prid
    JOIN prassignment pra ON pr.prassignmentid = pra.prid
    JOIN prtask prta ON prta.prid = pra.prtaskid
    JOIN inv_investments ii ON ii.id = prta.prprojectid
    JOIN srm_resources sr ON prt.prresourceid = sr.id
    JOIN prtimeperiod pri ON pri.prid = prt.prtimeperiodid
WHERE
        upper(sr.unique_name) = '<Replace with Resource ID in Upper Case>'
    AND
        upper(ii.code) = '<Replace with Investment Code in Upper Case>'
ORDER BY pri.prstart ASC;