Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Workplan and tasks

Highlighted
Tommy Vu
Occasional Contributor

Workplan and tasks

I am pretty new to the Project module, I want to build a query to list out all the tasks in given project.

Any helps is greatly appreciated.
7 REPLIES
Celil
Esteemed Contributor

Re: Workplan and tasks

Hi Tommy,

This is what you want:

select
prj.PROJECT_ID,
prjwp.WORK_PLAN_ID,
prjt.TASK_ID,
prjt.TASK_ACTUALS_ID,
prjt.CALENDAR_CONSTRAINT_ID CLDR_CNSTRNT_ID,
prjt.TASK_COST_ID,
prjt.MILESTONE_ID,
prjt.OUTLINE_LEVEL,
prjt.PARENT_TASK_ID,
prjt.TASK_SCHEDULE_ID,
prjt.TASK_INFO_ID,
prjt.TASK_USERDATA_ID,
prja.ACT_DURATION,
prja.ACT_EFFORT,
prja.ACT_FINISH_DATE,
prja.ACT_START_DATE,
prja.EST_FINISH_DATE,
prja.EST_REM_EFFORT,
prja.TOT_SCHED_DURATION,
prja.PERC_COMPLETE,
prjs.SCHED_DURATION,
prjs.SCHED_EFFORT,
prjs.SCHED_FINISH_DATE,
prjs.SCHED_START_DATE,
prjti.ACTIVITY_ID,
prjti.PRIORITY,
prjti.TASK_TYPE_CODE,
prjti.ROLE_ID,
prjti.SCHEDULE_HEALTH,
prjti.STATUS,
prjti.SERVICE_ID,
prjr.RESOURCE_ID,
prjr.SCHED_EFFORT,
prjr.ACT_DURATION,
prjr.ACT_EFFORT,
prjr.ACT_START_DATE,
prjr.ACT_FINISH_DATE,
prjr.EST_EFFORT,
prjr.EST_FINISH_DATE,
prjr.PERC_COMPLETE
from
PM_PROJECTS prj,
PM_WORK_PLANS prjwp,
WP_TASKS prjt,
WP_TASK_ACTUALS prja,
WP_TASK_SCHEDULE prjs,
WP_TASK_INFO prjti,
WP_WORK_UNITS prjr
where
prj.PROJECT_ID = prjwp.PROJECT_ID
AND prjwp.WORK_PLAN_ID = prjt.WORK_PLAN_ID
AND prjwp.ENTITY_TYPE = 'WORK_PLAN'
AND prjt.TASK_ACTUALS_ID = prja.ACTUALS_ID
AND prjt.TASK_SCHEDULE_ID = prjs.TASK_SCHEDULE_ID
AND prjt.TASK_INFO_ID = prjti.TASK_INFO_ID
AND prjt.TASK_ID = prjr.TASK_ID
order by prj.PROJECT_ID
Celil

IT Governance Professional
& PPM Solution Architect
Tommy Vu
Occasional Contributor

Re: Workplan and tasks

One more thing, the status in the wp_task_info table is in numeric where can I join this to get a text info like "complete", "acancelled"?
Edtroleis
Regular Collector

Re: Workplan and tasks

Try associate with kcrt_statuses.
Tommy Vu
Occasional Contributor

Re: Workplan and tasks

that is not it. It is not correct.
Celil
Esteemed Contributor

Re: Workplan and tasks


All "kcrt" prefix tables are related with Request Types.

this is what you want:

SELECT state_id,
state_name,
FROM kdrv_states

Celil
Celil

IT Governance Professional
& PPM Solution Architect
Tommy Vu
Occasional Contributor

Re: Workplan and tasks

i actually have to use the validation PM - Task Statuses for my status. Thanks all for your helps.
M Santosh Kumar
Super Collector

Re: Workplan and tasks

Hi,

You can map it with Knta_Lookups table to get the status name of the task, as the values of 'PM - Task Statuses' validation will be store init.

Thanks,
Kumar
//Add this to "OnDomLoad" event