Project Status Inquiry (PSI) - R12

In this section i would like to expand the view pa_status_task_generic_v which is used to display the Project Status Inquiry task wise.

Querying pa_status_task_generic_v in sql doesn't give the data because it needs inputs which are passed from oracle form.

pa_status_task_generic_v uses four views

pa_status_proj_bgt_rev_v => R -- Revenue

pa_status_proj_bgt_cost_v => C -- Actual Costs , Also can be used to get Forecast Cost by modifying budget_type_code = AC/FC

pa_project_accum_actuals => A -- Actual Expenditure

pa_project_accum_commitments => M -- Commitment

which in turn uses some more views.

So i have decoded the view to make easy for newbie's. This can be directly run from SQL by passing the parameter

SELECT t.project_id, t.task_id, t.parent_task_id, t.wbs_level,
c.budget_type_code, r.budget_type_code,
DECODE (pa_task_utils.check_child_exists (t.task_id), 1, '+', 0, ' '),
t.task_number, t.task_name,
DECODE ((SIGN ( (NVL (c.baseline_burdened_cost_itd, 0) * 1.1)
- ( NVL (a.burdened_cost_itd, 0)
+ NVL (m.cmt_burdened_cost_ptd, 0)
)
)
),
-1, '*',
NULL
),
ROUND (NVL (r.baseline_revenue_itd, 0)),
ROUND (NVL (a.revenue_itd, 0)),
ROUND (NVL (c.baseline_burdened_cost_itd, 0)),
ROUND (NVL (a.burdened_cost_itd, 0)),
ROUND (NVL (m.cmt_burdened_cost_ptd, 0)) cmt_burdened_cost_ptd,
ROUND (NVL (c.baseline_burdened_cost_ptd, 0)),
ROUND (NVL (a.burdened_cost_ptd, 0)),
ROUND (NVL (c.baseline_labor_hours_itd, 0)),
ROUND (NVL (a.billable_labor_hours_ytd, 0)),
ROUND (NVL (c.baseline_labor_hours_ptd, 0)),
ROUND (NVL (a.billable_labor_hours_ytd, 0)),
ROUND (NVL (r.baseline_revenue_tot, 0)),
ROUND (NVL (c.baseline_burdened_cost_tot, 0)),
ROUND (NVL (c.baseline_labor_hours_tot, 0)),
ROUND (NVL (r.original_revenue_tot, 0)),
ROUND (NVL (c.original_burdened_cost_tot, 0)),
ROUND (NVL (c.original_labor_hours_tot, 0)),
ROUND (NVL (r.original_revenue_itd, 0)),
ROUND (DECODE (c.baseline_burdened_cost_tot,
0, 0,
(a.burdened_cost_itd / c.baseline_burdened_cost_tot
)
* 100
)
),
ROUND (DECODE (c.baseline_labor_hours_tot,
0, 0,
(a.BILLABLE_LABOR_HOURS_YTD
/ c.baseline_labor_hours_tot
)
* 100
)
),
ROUND ( NVL (c.baseline_burdened_cost_tot, 0)
- NVL (m.cmt_burdened_cost_ptd, 0)
- NVL (a.burdened_cost_itd, 0)
),
ROUND (NVL (a.burdened_cost_itd, 0) + NVL (m.cmt_burdened_cost_ptd, 0)),
ROUND ( NVL (r.baseline_revenue_tot, 0)
- NVL (c.baseline_burdened_cost_tot, 0)
),
ROUND (NVL (a.revenue_itd, 0) - NVL (a.burdened_cost_itd, 0)), 0, 0, 0,
0, 0, 0
FROM pa_tasks t,
(SELECT pah.project_id,
pah.task_id,
pab.budget_type_code,
NVL (pab.base_raw_cost_itd, 0),
NVL (pab.base_raw_cost_ytd, 0),
NVL (pab.base_raw_cost_pp, 0),
NVL (pab.base_raw_cost_ptd, 0),
NVL (pab.base_burdened_cost_itd, 0) baseline_burdened_cost_itd,
NVL (pab.base_burdened_cost_ytd, 0),
NVL (pab.base_burdened_cost_pp, 0),
NVL (pab.base_burdened_cost_ptd, 0) baseline_burdened_cost_ptd,
NVL (pab.orig_raw_cost_itd, 0),
NVL (pab.orig_raw_cost_ytd, 0),
NVL (pab.orig_raw_cost_pp, 0),
NVL (pab.orig_raw_cost_ptd, 0),
NVL (pab.orig_burdened_cost_itd, 0),
NVL (pab.orig_burdened_cost_ytd, 0),
NVL (pab.orig_burdened_cost_pp, 0),
NVL (pab.orig_burdened_cost_ptd, 0),
NVL (pab.orig_labor_hours_itd, 0),
NVL (pab.orig_labor_hours_ytd, 0),
NVL (pab.orig_labor_hours_pp, 0),
NVL (pab.orig_labor_hours_ptd, 0),
NVL (pab.base_labor_hours_itd, 0) baseline_labor_hours_itd,
NVL (pab.base_labor_hours_ytd, 0),
NVL (pab.base_labor_hours_pp, 0),
NVL (pab.base_labor_hours_ptd, 0) baseline_labor_hours_ptd,
NVL (pab.base_raw_cost_tot, 0),
NVL (pab.base_burdened_cost_tot, 0) baseline_burdened_cost_tot,
NVL (pab.orig_raw_cost_tot, 0),
NVL (pab.orig_burdened_cost_tot, 0) original_burdened_cost_tot,
NVL (pab.orig_labor_hours_tot, 0) original_labor_hours_tot,
NVL (pab.base_labor_hours_tot, 0) baseline_labor_hours_tot
FROM pa_project_accum_headers pah, pa_project_accum_budgets pab
WHERE pah.task_id > 0
AND pah.project_id = :P_PROJECT_ID
AND pah.resource_list_id = 0
AND pah.project_accum_id = pab.project_accum_id
AND pab.budget_type_code = 'AC'
--UNION
--SELECT t.project_id, t.task_id, bt.budget_type_code, 0, 0, 0, 0, 0, 0, 0, 0,
-- 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
-- FROM pa_tasks t, pa_budget_types bt
-- WHERE bt.budget_type_code = 'AC'
-- AND t.project_id = :P_PROJECT_ID
-- GROUP BY t.project_id, t.task_id,bt.budget_type_code
) c,
(SELECT pah.project_id,
pah.task_id,
pab.budget_type_code,
NVL (pab.base_revenue_itd, 0) baseline_revenue_itd,
NVL (pab.base_revenue_ytd, 0),
NVL (pab.base_revenue_pp, 0),
NVL (pab.base_revenue_ptd, 0),
NVL (pab.orig_revenue_itd, 0) original_revenue_itd,
NVL (pab.orig_revenue_ytd, 0),
NVL (pab.orig_revenue_pp, 0),
NVL (pab.orig_revenue_ptd, 0),
NVL (pab.orig_labor_hours_itd, 0),
NVL (pab.orig_labor_hours_ytd, 0),
NVL (pab.orig_labor_hours_pp, 0),
NVL (pab.orig_labor_hours_ptd, 0),
NVL (pab.base_labor_hours_itd, 0),
NVL (pab.base_labor_hours_ytd, 0),
NVL (pab.base_labor_hours_pp, 0),
NVL (pab.base_labor_hours_ptd, 0),
NVL (pab.base_revenue_tot, 0) baseline_revenue_tot,
NVL (pab.orig_revenue_tot, 0) original_revenue_tot,
NVL (pab.orig_labor_hours_tot, 0),
NVL (pab.base_labor_hours_tot, 0)
FROM pa_project_accum_headers pah, pa_project_accum_budgets pab
WHERE pah.task_id > 0
AND pah.project_id = :P_PROJECT_ID
AND pah.resource_list_id = 0
AND pah.project_accum_id = pab.project_accum_id
AND pab.budget_type_code = 'AC'
--UNION
-- SELECT t.project_id, t.task_id, bt.budget_type_code, 0, 0, 0, 0, 0, 0, 0, 0,
-- 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
-- FROM pa_tasks t, pa_budget_types bt
-- WHERE bt.budget_type_code = 'AC'
-- AND t.project_id = :P_PROJECT_ID
) r,
(SELECT pah.project_id, pah.task_id, NVL (paa.raw_cost_itd, 0),
NVL (paa.raw_cost_ytd, 0), NVL (paa.raw_cost_pp, 0),
NVL (paa.raw_cost_ptd, 0), NVL (paa.billable_raw_cost_itd, 0),
NVL (paa.billable_raw_cost_ytd, 0), NVL (paa.billable_raw_cost_pp, 0),
NVL (paa.billable_raw_cost_ptd, 0), NVL (paa.burdened_cost_itd, 0) burdened_cost_itd,
NVL (paa.burdened_cost_ytd, 0), NVL (paa.burdened_cost_pp, 0),
NVL (paa.burdened_cost_ptd, 0) burdened_cost_ptd ,
NVL (paa.billable_burdened_cost_itd, 0),
NVL (paa.billable_burdened_cost_ytd, 0),
NVL (paa.billable_burdened_cost_pp, 0),
NVL (paa.billable_burdened_cost_ptd, 0), NVL (paa.labor_hours_itd, 0),
NVL (paa.labor_hours_ytd, 0), NVL (paa.labor_hours_pp, 0),
NVL (paa.labor_hours_ptd, 0), NVL (paa.billable_labor_hours_itd, 0),
NVL (paa.billable_labor_hours_ytd, 0),
NVL (paa.billable_labor_hours_pp, 0),
NVL (paa.billable_labor_hours_ptd, 0), NVL (paa.revenue_itd, 0) revenue_itd,
NVL (paa.revenue_ytd, 0), NVL (paa.revenue_pp, 0),
NVL (paa.revenue_ptd, 0),
1,
paa.BILLABLE_LABOR_HOURS_YTD
FROM pa_project_accum_headers pah, pa_project_accum_actuals paa
WHERE pah.task_id > 0
AND pah.project_id = :P_PROJECT_ID
AND pah.resource_list_id = 0
AND pah.project_accum_id = paa.project_accum_id) a,
(SELECT pah.project_id, pah.task_id, NVL (pac.cmt_raw_cost_itd, 0),
NVL (pac.cmt_raw_cost_ytd, 0), NVL (pac.cmt_raw_cost_pp, 0),
NVL (pac.cmt_raw_cost_ptd, 0), NVL (pac.cmt_burdened_cost_itd, 0),
NVL (pac.cmt_burdened_cost_ytd, 0), NVL (pac.cmt_burdened_cost_pp, 0),
NVL (pac.cmt_burdened_cost_ptd, 0) cmt_burdened_cost_ptd, NVL (pac.cmt_quantity_itd, 0),
NVL (pac.cmt_quantity_ytd, 0), NVL (pac.cmt_quantity_pp, 0),
NVL (pac.cmt_quantity_ptd, 0)
FROM pa_project_accum_headers pah, pa_project_accum_commitments pac
WHERE pah.task_id > 0
AND pah.project_id = :P_PROJECT_ID
AND pah.resource_list_id = 0
AND pah.project_accum_id = pac.project_accum_id) m
WHERE t.project_id = :P_PROJECT_ID
AND t.task_id = c.task_id(+)
AND t.task_id = r.task_id(+)
AND t.task_id = a.task_id(+)
AND t.task_id = m.task_id(+)