Project and Portfolio Management Practitioners Forum
cancel

SQL for pull Total Budget,Budget to Date and Acutla To Date

Highlighted
Shravan Kathuri
Honored Contributor.

SQL for pull Total Budget,Budget to Date and Acutla To Date

Hi,

Can anyone provide me the SQL to pull the Total budget,Budget To Date and Actuals to Date
that are displayed in Project Summary tab?
Quick response would be appriciated.


Thanks
Shravan Kumar.K
3 REPLIES
Shravan Kathuri
Honored Contributor.

Re: SQL for pull Total Budget,Budget to Date and Acutla To Date

Any body has SQL to find Budget To Date and Actual To Date values?

And also Can anyone have SQL to pull the health indicater values ( green,red or yellow) for any projects in PPM 7.5.

Thanks
Shravan Kumar.K
Vibhor Dwivedi
Super Contributor.

Re: SQL for pull Total Budget,Budget to Date and Acutla To Date

Something like attached query will help you for getting actuals.

SELECT distinct
pp.project_name
,req.description
,per1.period_full_name start_period
,per2.period_full_name finish_period
,ste.state_name
,pp.project_id
,pt.project_type_name
,pfm.project_health_code,
to_char(( ROUND (tc.act_cap_labor_bse / 10000, 2)
+ ROUND (tc.act_cap_non_labor_bse / 10000, 2)
+ ROUND (tc.act_op_labor_bse / 10000, 2)
+ ROUND (tc.act_op_non_labor_bse / 10000, 2)
),'$99999999999999.99') actual_to_date
FROM pm_projects pp
JOIN pm_work_plans wp ON wp.project_id = pp.project_id
JOIN wp_tasks t ON t.work_plan_id = wp.work_plan_id
JOIN wp_task_info i ON i.task_info_id = t.task_info_id
JOIN wp_task_actuals a ON a.actuals_id = t.task_actuals_id
JOIN wp_task_costs tc ON tc.task_cost_id = t.task_cost_id
JOIN pm_project_types pt ON pt.project_type_id = pp.project_type_id
JOIN kcrt_requests req ON req.request_id = pp.pfm_request_id
JOIN kcrt_request_details rd ON rd.parameter1 = TO_CHAR(req.request_id) AND rd.batch_number=1
JOIN kcrt_fg_pfm_project pfm ON pfm.request_id = pp.pfm_request_id
LEFT OUTER JOIN pm_project_rollup ppr ON ppr.rollup_id = pfm.prj_project_id
JOIN knta_periods per1 ON per1.period_id = pp.start_date_period
JOIN knta_periods per2 ON per2.period_id = pp.finish_date_period
LEFT OUTER JOIN kdrv_states ste ON ste.state_id = pp.status
-- WHERE pp.pfm_request_id =
Shravan Kathuri
Honored Contributor.

Re: SQL for pull Total Budget,Budget to Date and Acutla To Date

Hi Vibhor Dwivedi,

Thanks for yuor response. But looks like this is for workplan "Acvtual to Date". I need to pull Budget "Actual To Date". Can you please help me If you have any SQL.

Thanks
Shravan Kumar.K