VIEW: APPS.PA_STATUS_PROJ_LIST_V

Source

View Text - Preformatted

SELECT
        p.project_id,
        p.projfunc_currency_code,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)),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.LABOR_HOURS_ITD,0)),ROUND(NVL(C.BASELINE_LABOR_HOURS_PTD,0)),ROUND(NVL(A.LABOR_HOURS_PTD,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.LABOR_HOURS_ITD/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_projects_all p,pa_project_accum_headers pah,pa_project_accum_actuals a,pa_project_accum_commitments m,PA_PROJECT_ACCUM_BUDGETS_V c,PA_PROJECT_ACCUM_BUDGETS_V r WHERE  p.project_id = pah.project_id AND pah.task_id = 0 AND pah.resource_list_member_id = 0 AND pah.project_accum_id = a.project_accum_id AND pah.project_accum_id = m.project_accum_id AND pah.project_accum_id = c.project_accum_id AND C.BUDGET_TYPE_CODE = 'AC' AND pah.project_accum_id = r.project_accum_id AND R.BUDGET_TYPE_CODE = 'AR'

View Text - HTML Formatted

SELECT P.PROJECT_ID
, P.PROJFUNC_CURRENCY_CODE
, 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))
, 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.LABOR_HOURS_ITD
, 0))
, ROUND(NVL(C.BASELINE_LABOR_HOURS_PTD
, 0))
, ROUND(NVL(A.LABOR_HOURS_PTD
, 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.LABOR_HOURS_ITD/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_PROJECTS_ALL P
, PA_PROJECT_ACCUM_HEADERS PAH
, PA_PROJECT_ACCUM_ACTUALS A
, PA_PROJECT_ACCUM_COMMITMENTS M
, PA_PROJECT_ACCUM_BUDGETS_V C
, PA_PROJECT_ACCUM_BUDGETS_V R
WHERE P.PROJECT_ID = PAH.PROJECT_ID
AND PAH.TASK_ID = 0
AND PAH.RESOURCE_LIST_MEMBER_ID = 0
AND PAH.PROJECT_ACCUM_ID = A.PROJECT_ACCUM_ID
AND PAH.PROJECT_ACCUM_ID = M.PROJECT_ACCUM_ID
AND PAH.PROJECT_ACCUM_ID = C.PROJECT_ACCUM_ID
AND C.BUDGET_TYPE_CODE = 'AC'
AND PAH.PROJECT_ACCUM_ID = R.PROJECT_ACCUM_ID
AND R.BUDGET_TYPE_CODE = 'AR'