Project and Portfolio Management Practitioners Forum
cancel

timesheet actuals vs workplan actuals

Highlighted
riznad
Super Contributor.

timesheet actuals vs workplan actuals

Hi;

In our ppm system timesheet actuals (actuals entered by resources on weekly timesheets) are diferent than total actual value on workplan.

We are sing PPM 8.4.

I requested offical suppor. They send me the script above. But the script only modifiy open workplans. It does not work for closed projects. Is there any way to modify this script to correct actuals on closed workplans?

 

DECLARE

   l_tm_temp_table_exists   NUMBER := 0;

   l_currpt_number      NUMBER :=0;

   

   TYPE rtm_type IS REF CURSOR;

   rtm rtm_type;

  

   TYPE rec_type IS RECORD (

       task_id            tm_time_sheet_lines.work_item_id%TYPE,

       resource_id        tm_time_sheets.resource_id%TYPE,

       actual_on_sum      NUMBER,

       min_date           tm_work_units.act_start_date%TYPE,

       max_date           tm_work_units.act_finish_date%TYPE

    );

   

    rec rec_type;

  

   o_message_type NUMBER;

   o_message_name VARCHAR2(19);

   o_message VARCHAR2(19);

BEGIN

 

 

   BEGIN

      SELECT 1

        INTO l_tm_temp_table_exists

        FROM DUAL

       WHERE EXISTS (SELECT 1

                       FROM user_tables

                      WHERE table_name = 'TM_TEMP_TABLE');

   EXCEPTION

      WHEN NO_DATA_FOUND

      THEN

         l_tm_temp_table_exists := 0;

   END;

 

   IF (l_tm_temp_table_exists = 1)

   THEN

      BEGIN

         EXECUTE IMMEDIATE '

            DROP TABLE tm_temp_table

         ';

      EXCEPTION

         WHEN NO_DATA_FOUND

         THEN

            l_tm_temp_table_exists := 0;

      END;

   END IF;

 

/* EXECUTE IMMEDIATE '

update tm_work_units tmwu set

tmwu.needs_sync = ''Y'',

tmwu.act_start_date = (

select min(comp.start_time)

from

tm_time_sheets t

, tm_time_sheet_lines l

, tm_actuals a

, tm_actuals_effort e

, itg_calendar cal

, itg_components comp

where

l.tm_work_unit_id = tmwu.tm_work_unit_id

and l.time_sheet_line_id = a.time_sheet_line_id

and a.actuals_id = e.actuals_id

and l.time_sheet_id = t.time_sheet_id

and t.calendar_id = cal.calendar_id

and comp.calendar_id = cal.calendar_id

and a.totals_flag = ''Y''

and e.actual_effort > 0

and l.state not in (4,5)

)

where tmwu.act_start_date is null

and tmwu.act_effort > 0;';  

 

COMMIT;

*/

 

EXECUTE IMMEDIATE '

CREATE TABLE tm_temp_table AS

SELECT distinct tl.work_item_id task_id,

        tl.work_item_type workitem_type,

       tskif.name,

       tl.resource_id,

       usr.username,

       tl.act_effort actual_on_sum,

       tl.start_date min_date,

       tl.finish_date max_date,

       tm.tm_work_unit_id,

       tm.perc_complete  tmu_pc,

       tm.act_effort tmu_ae,

       tm.act_start_date tmu_startdate,

       tm.act_finish_date tmu_finishdate,

       tm.needs_sync,

       wpu.sched_effort scheduled_effort,

       wpu.perc_complete  pm_percent_complete,

       wpu.act_effort pm_actual_effort,

       wpu.act_start_date pm_start_date,

       wpu.act_finish_date pm_finish_date

  FROM (SELECT   l.work_item_id work_item_id, l.work_item_type work_item_type,

                 l.work_item_set_id, t.resource_id resource_id,

                 SUM (ef.actual_effort) act_effort,

                 MIN (c.start_time) start_date,

                 MAX (c.finish_time) finish_date

            FROM tm_actuals act,

                 tm_actuals_effort ef,

                 itg_components c,

                 tm_time_sheet_lines l,

                 tm_time_sheets t

           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.time_sheet_id = t.time_sheet_id

             AND c.calendar_id = t.calendar_id

             AND c.component_seq = ef.effort_order

             AND l.state <> 4

             AND l.state <> 5

             AND l.work_item_type IN (''TASK'', ''PROJECT'')

             AND ef.actual_effort <> 0

        GROUP BY work_item_id,

                 work_item_type,

                 resource_id,

                 work_item_set_id) tl,

       tm_work_units tm,

       wp_work_units wpu,

       wp_tasks tsk,

       wp_task_info tskif,

       pm_work_plans pln,

       pm_projects prj,

       rsc_resources rsc,

       knta_users usr

WHERE tl.work_item_type = tm.work_item_type

   AND tl.work_item_id = tm.work_item_id

   AND tl.resource_id = tm.resource_id

   AND tm.work_item_id = wpu.task_id

   AND tm.resource_id = wpu.resource_id  

   AND rsc.resource_id = tm.resource_id

   AND usr.user_id = rsc.user_id

  -- AND usr.username =''

   AND wpu.task_id = tsk.task_id

   AND tskif.task_info_id= tsk.task_info_id

   AND pln.work_plan_id=tsk.work_plan_id

   AND prj.project_id=pln.project_id

   AND (  tl.act_effort - tm.act_effort  > 0.001

        OR  tl.act_effort - tm.act_effort  < -0.001

        OR tm.act_effort - wpu.act_effort > 0.001

        OR tm.act_effort - wpu.act_effort< - 0.001

        OR trunc(tl.start_date) <> trunc(tm.act_start_date)

        OR trunc(tl.finish_date) <> trunc(tm.act_finish_date)

       )

';

 

 

   EXECUTE IMMEDIATE 'SELECT count(*)  FROM tm_temp_table' into l_currpt_number;

  

   IF (l_currpt_number = 0)

   THEN

     DBMS_OUTPUT.put_line('Nothing to adjust...');

     RETURN;

   END IF;

  

   

   EXECUTE IMMEDIATE 'SELECT count(*)  FROM tm_temp_table WHERE actual_on_sum - tmu_ae > 0.001 or actual_on_sum - tmu_ae < -0.001  OR min_date <> tmu_startdate OR max_date <> tmu_finishdate  OR tmu_ae - pm_actual_effort > 0.001 OR tmu_ae - pm_actual_effort < - 0.001' into  l_currpt_number;

  

   IF (l_currpt_number > 0)

   THEN

     DBMS_OUTPUT.put_line('------------------------------------------------');

     DBMS_OUTPUT.put_line('To adjust unequal actuals in tm_work_units ...');

           EXECUTE IMMEDIATE '

        MERGE INTO tm_work_units wu

           USING tm_temp_table k

           ON (wu.tm_work_unit_id = k.tm_work_unit_id)

           WHEN MATCHED THEN

              UPDATE

                 SET wu.act_effort = k.actual_on_sum,

                     wu.act_start_date = k.min_date,

                     wu.act_finish_date = decode(wu.perc_complete, 100, k.max_date, null),

                     wu.needs_sync = ''Y''

        ';

 

    

     DBMS_OUTPUT.put_line('------------------------------------------------');

     DBMS_OUTPUT.put_line('Fix null percent complete ...');

           EXECUTE IMMEDIATE '

update tm_work_units tmwu

set tmwu.perc_complete =

(select wpwu.perc_complete from wp_work_units wpwu

where tmwu.resource_id = wpwu.resource_id

and tmwu.work_item_id = wpwu.task_id

and tmwu.work_item_type in (''PROJECT'', ''TASK'')

and wpwu.perc_complete is not null)

where tmwu.perc_complete is null';

 

           EXECUTE IMMEDIATE '

update tm_work_units tmwu

set tmwu.est_effort =

(select wpwu.est_effort from wp_work_units wpwu

where tmwu.resource_id = wpwu.resource_id

and tmwu.work_item_id = wpwu.task_id

and tmwu.work_item_type in (''PROJECT'', ''TASK'')

and wpwu.est_effort is not null)

where tmwu.est_effort is null';

 

 

      DBMS_OUTPUT.put_line('corrected '||to_char(l_currpt_number)||' unequal actuals');

   END IF;

  

   COMMIT;

 

END;

/

EXIT;

 

1 REPLY
dirkf
Acclaimed Contributor.

Re: timesheet actuals vs workplan actuals

Hi Riznad,

 

closed workplans as well as closed timesheets don't roll up to any financial summary anymore, since the service won't touch or trigger these plans / sheets anymore.

While I understand that you might want correct values on all corners, this will not happen on closed projects / workplans.

 

I'm not aware of any script that would update those values.

 

Regards,

Dirk