Product: | PA - Projects |
---|---|
Description: | This view is being retrofitted into CASE |
Implementation/DBA Data: | APPS.PA_STATUS_PROJ_GENERIC_V |
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 (+)