Project and Portfolio Management Practitioners Forum
cancel

how to get dates from a baseline

SOLVED
Go to solution
Highlighted
patrick-sa
Super Contributor.

how to get dates from a baseline

Hi ,

How can i modify this query so that it returns dates from the baseline as opposed to the workplan.
i've included sections required to make it return baseline data but still returns workplan data.

AND LOOKUP_TYPE = 'PM - Task Statuses'
and pwp3.ENTITY_TYPE = 'BASE_LINE'

full query below

select prj.project_name ,
(SELECT ' ST :' || l3.MEANING || '
SF :' || ts3.SCHED_FINISH_DATE||'
AF :' || ta3.ACT_FINISH_DATE || '
' ||
DECODE(pti3.EXCEPTION_TASK_COUNT, 0, 'GREEN','RED') || '
'
FROM
KNTA_LOOKUPS l3,PM_WORK_PLANS pwp3,WP_TASKS pt3,WP_TASK_INFO pti3,wp_task_schedule ts3,wp_task_actuals ta3,wp_milestones m3
WHERE pwp3.PROJECT_ID = prj.PROJECT_ID
AND pwp3.MASTER_WORK_PLAN_ID = pt3.WORK_PLAN_ID
AND pt3.TASK_INFO_ID = pti3.TASK_INFO_ID
AND m3.MILESTONE_ID = pt3.MILESTONE_ID
AND l3.LOOKUP_CODE = pti3.STATUS
AND ts3.TASK_SCHEDULE_ID(+) = pt3.TASK_SCHEDULE_ID
AND ta3.ACTUALS_ID(+) = pt3.TASK_ACTUALS_ID
AND m3.MAJOR='Y'
AND LOOKUP_TYPE = 'PM - Task Statuses'
and pwp3.ENTITY_TYPE = 'BASE_LINE'
and pwp3.IS_ACTIVE_FLAG ='Y'
AND ROWNUM = 1)
from pm_projects prj
4 REPLIES
Semenov Alexand
Acclaimed Contributor.

Re: how to get dates from a baseline

Hi.
Try thisquery:

select
to_char(wpa.perc_complete) perc_complete
,blts.sched_start_date bl_start_date
,blts.sched_finish_date bl_finish_date
,nvl(wpti.name, '###absent in WP###') wp_name
,nvl(blti.name, '###absent in BLa###') bl_name
,nvl(wpts.sched_start_date, blts.sched_start_date) wp_start_date
,nvl(wpts.sched_finish_date, blts.sched_finish_date) wp_finish_date
,nvl(wpt.outline_level, blt.outline_level)-1 outline_level
,wpa.act_start_date act_start_date
,wpa.act_finish_date act_finish_date
from
itguser.wp_tasks wpt full join itguser.wp_tasks blt on wpt.task_id=blt.business_uid
left join itguser.wp_task_actuals wpa on wpa.actuals_id=wpt.task_actuals_id
left join itguser.pm_work_plans wp on wp.work_plan_id=wpt.work_plan_id
left join itguser.pm_work_plans bl on bl.work_plan_id=blt.work_plan_id
left join itguser.wp_task_info wpti on wpti.task_info_id=wpt.task_info_id
left join itguser.wp_task_info blti on blti.task_info_id=blt.task_info_id
left join itguser.wp_task_schedule wpts on wpts.task_schedule_id=wpt.task_schedule_id
left join itguser.wp_task_schedule blts on blts.task_schedule_id=blt.task_schedule_id
where
bl.is_active_flag='Y' and bl.entity_type='BASE_LINE' and bl.project_id=31777 -- this is my ID
patrick-sa
Super Contributor.

Re: how to get dates from a baseline

Hi Semenov ,

Thanks.i'm getting a table does not exist error.i'm on 7.5sp3.i want to try and fix this query because it has all that i need except that its returning dates from the schedule.

thanks
Erik Cole
Acclaimed Contributor.
Solution

Re: how to get dates from a baseline

Hi,

I think you're joining to the real work plan and not the baseline...

AND pwp3.MASTER_WORK_PLAN_ID = pt3.WORK_PLAN_ID

should be

AND pwp3.WORK_PLAN_ID = pt3.WORK_PLAN_ID
patrick-sa
Super Contributor.

Re: how to get dates from a baseline

Hi Eric,

Thanks for pointing out that out.it works like a charm.