VIEW: APPS.PA_STATUS_PROJ_GENERIC_V

Source

View Text - Preformatted

SELECT p.project_id , SUBSTR(pa_security.view_labor_costs(p.project_id),1,1) , c.budget_type_code , r.budget_type_code , P.SEGMENT1 , P.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)) , 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 p, pa_project_accum_headers pah, pa_status_proj_bgt_rev_v r, pa_status_proj_bgt_cost_v c, pa_project_accum_actuals a, pa_project_accum_commitments m WHERE p.project_id = pah.project_id AND 'Y' in (SELECT pa_security.allow_query(p.project_id) from sys.dual) AND pah.task_id = 0 AND pah.resource_list_id = 0 AND pah.project_id = r.project_id AND pah.project_id = c.project_id AND pah.project_accum_id = a.project_accum_id (+) AND pah.project_accum_id = m.project_accum_id (+)

View Text - HTML Formatted

SELECT P.PROJECT_ID
, SUBSTR(PA_SECURITY.VIEW_LABOR_COSTS(P.PROJECT_ID)
, 1
, 1)
, C.BUDGET_TYPE_CODE
, R.BUDGET_TYPE_CODE
, P.SEGMENT1
, P.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))
, 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 P
, PA_PROJECT_ACCUM_HEADERS PAH
, PA_STATUS_PROJ_BGT_REV_V R
, PA_STATUS_PROJ_BGT_COST_V C
, PA_PROJECT_ACCUM_ACTUALS A
, PA_PROJECT_ACCUM_COMMITMENTS M
WHERE P.PROJECT_ID = PAH.PROJECT_ID
AND 'Y' IN (SELECT PA_SECURITY.ALLOW_QUERY(P.PROJECT_ID)
FROM SYS.DUAL)
AND PAH.TASK_ID = 0
AND PAH.RESOURCE_LIST_ID = 0
AND PAH.PROJECT_ID = R.PROJECT_ID
AND PAH.PROJECT_ID = C.PROJECT_ID
AND PAH.PROJECT_ACCUM_ID = A.PROJECT_ACCUM_ID (+)
AND PAH.PROJECT_ACCUM_ID = M.PROJECT_ACCUM_ID (+)