Clarity PPM: Data Discrepancies in Actuals, Estimates or Allocation

Document ID : KB000027253
Last Modified Date : 25/07/2018
Show Technical Document Details
Issue:

There are  discrepancies in actuals (ACT), estimates (ETC), or allocations (ALC) in Clarity or Jaspersoft reports.

One or more of the following actual hours/units discrepancy scenarios exist:
-Cost plan vs Posted Transactions Review portlet
-Timesheet versus time slices
-Project Team / Task vs Posted Transactions Review
-ACWP is 0 or not being reflected properly
 

Cause:

The issue can stem from a failure from a conflict with the Time Slicing and/or Load DWH job.



 

Resolution:

Step 1: Use the following queries to compare the actual hours(units) on the project side

a. Actual Hours

select slice, slice_date, r.full_name, t.prname task_name, p.name project
from prj_blb_slices s, prassignment a, prtask t, srm_resources r, srm_projects p
where s.prj_object_id = a.prid
and a.prtaskid = t.prid
and t.prprojectid = p.id
and a.prresourceid = r.id
and p.unique_name like '%project unique name here%' and r.unique_name like '%resource unique name%'
and slice_request_id = 2

b. Estimate Hours

select slice, slice_date, r.full_name, t.prname task_name, p.name project
from prj_blb_slices s, prassignment a, prtask t, srm_resources r, srm_projects p
where s.prj_object_id = a.prid
and a.prtaskid = t.prid
and t.prprojectid = p.id
and a.prresourceid = r.id
and p.unique_name like '%project unique name here%' and r.unique_name like '%resource unique name%'
and slice_request_id = 3

c. Task Assignment Hours

select r.full_name, t.prname task_name, p.name project, a.*
from prassignment a, prtask t, srm_resources r, srm_projects p
where a.prtaskid = t.prid
and t.prprojectid = p.id
and a.prresourceid = r.id
and p.unique_name like '%project unique name here%' and r.unique_name like '%resource unique name%'

d. Team Allocation Hours

If there is concern with verifying allocation data for time slices, the query below will pull the daily records for one project and resource combination.
This data can be compared with the resource's availability to a specific project in OWB or MSP.

select slice, slice_date, r.full_name, p.name project
from prj_blb_slices s, prteam t, srm_resources r, srm_projects p
where s.prj_object_id = t.prid
and t.prprojectid = p.id
and t.prresourceid = r.id
and p.unique_name like'%project unique name here%' and r.unique_name like '%resource unique name%'
and slice_request_id = 10

Step 2. Use the following queries to compare the actual hours(units) on the financial module side

select 
w.externaltransno, w.transno, w.applyto, 
w.sourcemodule, w.transtype, 
w.externalbatchid, w.entrydate, w.transdate, w.status, w.quantity, wv.billrate,   
wv.amount, wv.price, wv.actualcost ac_rate, wv.amountremaining,w.glposted, 
w.project_code, tsk.prname, w.resource_code, w.external_id, w.task_id, w.role_code, 
w.charge_code, wv.currency_code, w.user_lov1, wv.* 
from ppa_wip w, 
ppa_wip_values wv, 
prtask tsk 
where and w.transno = wv.transno 
and tsk.prid = w.task_id 
and wv.currency_type = 'home' 
--and w.transdate = 'DD-MON-YYYY'
--and w.resource_code in ('example')
--and w.project_code = upper('example')
--and w.task_id = 5xxxxxx
   order by w.transno

If there is a discrepancy, please contact the Support Team if the following actions do not resolve the issue.
-Ensure the Time Slicing job has ran and completed.
-Reslicing the data item in question via the Administration > Data Administration > Time Slices 
-Check if data correct utility CSTool is needed

Additional Information:

The Home > Resource Management > Resource Planning page is a collection of portlets that users can use to perform a quick check of Clarity calculations such as actual units.
One such portlet that can be helpful is the Resource Workloads portlet.