Project and Portfolio Management Practitioners Forum
cancel

PM TM Actuals not in Sync

Highlighted
Peter Zehrer
Super Contributor.

PM TM Actuals not in Sync

Hi,

We are currently on PPM 8.04 version on windows.

 

For few projects the actuals coming from the Time Management is not in sync with the Projct Management actuals.

 

I am attaching the screenshot aswell as the query i am using, please review and let me know if i am using the wrong query to pull the data. I also tried running th kTMPMSync.sh but it showed a message that TMPMSync is not required.

 

Did anyone had same issue.?

 

Thanks,
Vamsee.

8 REPLIES
randull
Acclaimed Contributor.

Re: PM TM Actuals not in Sync

Hi Vamsee,

Is this happening with just one project?
What are the settings that you have on Cost and Effort for this project? Could you please attach some screenshots?

Thanks,
Randall
Best regards,
Randall

-- Remember to give Kudos to answers! (click the KUDOS star)
"If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
Oscar_Pereira
Honored Contributor.

Re: PM TM Actuals not in Sync

Hello Vamsee,

 

Here is a query that will hepl you to identify the problematic projects:

 

SELECT 'pm projects out of sync', p.project_name, p.project_id, wpwu.task_id, wpwu.resource_id, wpwu.act_effort, SUM (ef.actual_effort)
FROM tm_actuals act,
tm_actuals_effort ef,
tm_time_sheet_lines l,
tm_time_sheets t,
wp_work_units wpwu ,
wp_tasks tasks,
pm_work_plans wp,
pm_projects p
WHERE
ef.actuals_id = act.actuals_id
AND act.totals_flag = 'Y'
AND act.time_sheet_line_id = l.time_sheet_line_id
AND l.work_item_type in ('TASK', 'PROJECT')
AND wpwu.resource_id = t.resource_id
AND wpwu.task_id = l.work_item_id
AND l.time_sheet_id = t.time_sheet_id
and wpwu.task_id = tasks.task_id
and tasks.work_plan_id = wp.work_plan_id
and wp.project_id = p.project_id
AND l.time_sheet_id IS NOT NULL
AND l.effort_on_work_item_entity = 'Y'
HAVING (wpwu.act_effort < SUM (ef.actual_effort) -1
OR wpwu.act_effort > SUM (ef.actual_effort) + 1)
GROUP BY p.project_name, p.project_id, wpwu.task_id, wpwu.resource_id, wpwu.act_effort
order by p.project_name, p.project_id;


if we do see that the problem is tm-pm actuals out of sync, then i suggest we run the sync tool on these projects.
we first mark these projects as needing sync, with this query:

update pm_work_plans pwp
set source = 'NEEDS_TM_SYNC'
where pwp.work_plan_id IN (
SELECT DISTINCT wkp.work_plan_id
FROM (SELECT work_item_id, work_item_type, t.resource_id,
wp.act_effort wp_Act_Effort,
SUM (ef.actual_effort) tm_act_effort
FROM tm_actuals act,
tm_actuals_effort ef,
tm_time_sheet_lines l,
tm_time_sheets t,
wp_work_units wp
WHERE wp.resource_id IS NOT NULL
AND ef.actuals_id = act.actuals_id
AND act.totals_flag = 'Y'
AND act.time_sheet_line_id = l.time_sheet_line_id
AND wp.task_id = l.work_item_id
AND wp.resource_id = t.resource_id
AND l.time_sheet_id = t.time_sheet_id
AND l.time_sheet_id IS NOT NULL
AND wp.tm_origin = 'Y'
AND l.effort_on_work_item_entity = 'Y'
AND l.work_item_type IN ('TASK', 'PROJECT')
AND wp.act_effort IS NOT NULL
HAVING (wp.act_effort < SUM (ef.actual_effort) -1
OR wp.act_effort > SUM (ef.actual_effort) + 1)
GROUP BY work_item_id,
work_item_type,
t.resource_id,
wp.act_effort
) a, wp_tasks tsk, pm_work_plans wkp
WHERE a.work_item_id = tsk.task_id
AND tsk.work_plan_id = wkp.work_plan_id);

and then run the sync tool with bin\kPMTMSync.sh
the sync tool should recalculate the costing too.

Thanks

Oscar Pereira

Oscar_Pereira
Honored Contributor.

Re: PM TM Actuals not in Sync

On the other hand,

 

just to verify - make sure the cost rollup service is running properly without errors ?

 

Look at the serverLog.txt for any errors when the TM PM service and cost roll up service run....

 

-- Kudos are always welcome

 

Regards

Oscar Pereira

Peter Zehrer
Super Contributor.

Re: PM TM Actuals not in Sync

Hi Randall,

 

So far i have identified only two projects of this kind. Please find the attached screenshot of Cost and Effort section. Two projects have the same settings.

 

Thanks,

Vamsee.

Peter Zehrer
Super Contributor.

Re: PM TM Actuals not in Sync

Hi Oscar,

 

How do i veify whether the Cost Rollup service is running properly or not.

 

Thanks,

Vamsee.

Oscar_Pereira
Honored Contributor.

Re: PM TM Actuals not in Sync

Hello Vamsee,

 

You can do the following:

 

1-Login to PPM, go to Open < Administration < Services Audit Page and look for the “Cost Roll up” service.  This will show you the last run date or if is running…

 

2-Then run these queries to monitor the cost roll up:

 

select count(*) from itg_pending_rollups;

 

And

 

Select * from itg_pending_rollups;

 

This table stores the records pending to be rollup by the service, if after the service runs, you start seeing old records getting stuck on this table. It means that the service is having issues to process these rollups.

 

3-You can safely run this query to remove duplicate records from this table:

 

DELETE FROM itg_pending_rollups

 

      WHERE pending_rollup_id NOT IN (

 

               SELECT   MAX (pending_rollup_id)

 

                   FROM itg_pending_rollups

 

               GROUP BY rollup_type_code,

 

                        source_entity_id,

 

                        source_entity_primary_key,

 

                        target_entity_id,

 

                        target_entity_primary_key);

 

 

4-The other method is monitoring the serverLog.txt during the timeframe when the cost roll up service is running and search for any Cost related exception or error.

 

 

Let me know if this helps

 

-Kudos always welcome

 

Thanks

Oscar Pereira

Peter Zehrer
Super Contributor.

Re: PM TM Actuals not in Sync

HI Oscar,

 

I found three pending cost rollup entries out of which two are ACTULA and one is COST. Please let me know if i can delete these entries as mentioned in the STEP 3.

 

Just wanted to understand if there are any dependencies to be taken care before deleting those entries. Also do we need to force start the Cost Roll up Service or will that be taken care automaticall.

 

Thanks,

Vamsee.

Peter Zehrer
Super Contributor.

Re: PM TM Actuals not in Sync

Hi Oscar,

 

Please let me know if you have any updates on this.

 

Thanks,

Vamsee.