SQL to fetch Budget Numbers (very useful in saving time)
SELECT ppa.segment1 “Proj Num”
,ppa.name “Proj Name”
,ppa.description “Proj Desc”
,ppa.project_type “Proj Type”
,pt.task_number “Task Num”
,(
SELECT name
FROM pa_proj_elements
WHERE proj_element_id = task_id
AND object_type = ‘PA_TASKS’
) “Task Name”
,pt.description “Task Desc”
,pt.long_task_name
,(
SELECT sum(bl.burdened_cost)
FROM pa_budget_lines bl
,pa_resource_assignments ra
,pa_budget_versions bv
WHERE bl.budget_version_id = bv.budget_version_id
AND bv.CURRENT_ORIGINAL_FLAG = ‘Y’
AND bv.BUDGET_STATUS_CODE = ‘B’
AND bv.fin_plan_type_id <> 10
AND bl.resource_assignment_id = ra.resource_assignment_id
AND ra.task_id = pt.task_id
AND ra.project_id = bv.project_id
) “Cost Budget – As Sold”
,(
SELECT sum(bl.revenue)
FROM pa_budget_lines bl
,pa_resource_assignments ra
,pa_budget_versions bv
WHERE bl.budget_version_id = bv.budget_version_id
AND bv.CURRENT_ORIGINAL_FLAG = ‘Y’
AND bv.BUDGET_STATUS_CODE = ‘B’
AND bv.fin_plan_type_id <> 10
AND bl.resource_assignment_id = ra.resource_assignment_id
AND ra.task_id = pt.task_id
AND ra.project_id = bv.project_id
) “Revenue Budget – As Sold”
,(
SELECT sum(bl.burdened_cost)
FROM pa_budget_lines bl
,pa_resource_assignments ra
,pa_budget_versions bv
WHERE bl.budget_version_id = bv.budget_version_id
AND bv.CURRENT_FLAG = ‘Y’
AND bv.BUDGET_STATUS_CODE = ‘B’
AND bv.fin_plan_type_id <> 10
AND bl.resource_assignment_id = ra.resource_assignment_id
AND ra.task_id = pt.task_id
AND ra.project_id = bv.project_id
) “Cost Budget – Baselined”
,(
SELECT sum(bl.revenue)
FROM pa_budget_lines bl
,pa_resource_assignments ra
,pa_budget_versions bv
WHERE bl.budget_version_id = bv.budget_version_id
AND bv.CURRENT_FLAG = ‘Y’
AND bv.BUDGET_STATUS_CODE = ‘B’
AND bv.fin_plan_type_id <> 10
AND bl.resource_assignment_id = ra.resource_assignment_id
AND ra.task_id = pt.task_id
AND ra.project_id = bv.project_id
) “Revenue Budget – Baselined”
,(
SELECT sum(bl.burdened_cost)
FROM pa_budget_lines bl
,pa_resource_assignments ra
,pa_budget_versions bv
WHERE bl.budget_version_id = bv.budget_version_id
AND bv.current_working_flag = ‘Y’
AND bv.fin_plan_type_id <> 10
AND bl.resource_assignment_id = ra.resource_assignment_id
AND ra.task_id = pt.task_id
AND ra.project_id = bv.project_id
) “Cost Budget – To Date”
,(
SELECT sum(bl.revenue)
FROM pa_budget_lines bl
,pa_resource_assignments ra
,pa_budget_versions bv
WHERE bl.budget_version_id = bv.budget_version_id
AND bv.current_working_flag = ‘Y’
AND bv.fin_plan_type_id <> 10
AND bl.resource_assignment_id = ra.resource_assignment_id
AND ra.task_id = pt.task_id
AND ra.project_id = bv.project_id
) “Revenue Budget – To Date”
FROM pa_tasks pt
,pa_projects_all ppa
WHERE pt.project_id = ppa.project_id
AND template_flag = ‘N’
AND ppa.segment1 IN (‘<Project Numbers here>’)
ORDER BY ppa.segment1
,pt.task_number;
PS: Subquery is just my personal choice & comfort area. You can denormalize as you please.