View: PA_REP_UTIL_ACT_RSUM_PA_V

Product:	PA - Projects
Description:	PA_REP_UTIL_ACT_RSUM_PA_V is a view to provide Actual Capacity and Total Actual Hours values for Person Resources across PA Periods. This information is used by OA Screens.
Implementation/DBA Data:	APPS.PA_REP_UTIL_ACT_RSUM_PA_V

View Text

SELECT LKUP.MEANING AS TITLE_CODE
, PAOBJ.PERSON_ID AS PERSON_ID /* * FIELD BELOW IS FOR CAPACITY */
, DECODE( SIGN( SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.PERIOD_NAME = PA_REP_UTIL_GLOB.GETU3PERIODNAME
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) -SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.PERIOD_NAME = PA_REP_UTIL_GLOB.GETU3PERIODNAME
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END))
, 1
, SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.PERIOD_NAME = PA_REP_UTIL_GLOB.GETU3PERIODNAME
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) -SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.PERIOD_NAME = PA_REP_UTIL_GLOB.GETU3PERIODNAME
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END)
, +0) AS CAPACITY /* * FIELD BELOW IS FOR WORKED_HOURS */
, SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.PERIOD_NAME = PA_REP_UTIL_GLOB.GETU3PERIODNAME
AND SUMMBAL.AMOUNT_TYPE_ID = 1 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 1 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 1 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) AS WORKED_HOURS /* * FIELD BELOW IS FOR UTILIZATION */
, ROUND(NVL( SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.PERIOD_NAME = PA_REP_UTIL_GLOB.GETU3PERIODNAME
AND SUMMBAL.AMOUNT_TYPE_ID = 3 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 3 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 3 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END)*100/ DECODE(PA_REP_UTIL_GLOB.GETUTILCALCMETHOD
, 'CAPACITY'
, DECODE( SIGN( SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.PERIOD_NAME = PA_REP_UTIL_GLOB.GETU3PERIODNAME
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) -SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.PERIOD_NAME = PA_REP_UTIL_GLOB.GETU3PERIODNAME
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END))
, 1
, SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.PERIOD_NAME = PA_REP_UTIL_GLOB.GETU3PERIODNAME
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) -SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.PERIOD_NAME = PA_REP_UTIL_GLOB.GETU3PERIODNAME
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END)
, 1)
, 'TOTAL_WORKED_HOURS'
, DECODE( SIGN( SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.PERIOD_NAME = PA_REP_UTIL_GLOB.GETU3PERIODNAME
AND SUMMBAL.AMOUNT_TYPE_ID = 1 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 1 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 1 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END))
, 1
, SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.PERIOD_NAME = PA_REP_UTIL_GLOB.GETU3PERIODNAME
AND SUMMBAL.AMOUNT_TYPE_ID = 1 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 1 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 1 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END)
, 1) )
, -9999) /* FINISHED NVL */
, 0) AS UTILIZATION /* FINISHED ROUNDING*/
FROM PA_SUMM_BALANCES SUMMBAL
, PA_OBJECTS PAOBJ
, PA_LOOKUPS LKUP
, (SELECT TO_NUMBER(1) AS DUMNUM
FROM DUAL UNION ALL SELECT TO_NUMBER(2) AS DUMNUM
FROM DUAL UNION ALL SELECT TO_NUMBER(3) AS DUMNUM
FROM DUAL ) TRIPLE
WHERE LKUP.LOOKUP_TYPE = 'PA_RES_UTIL_PERIOD_PROMPT'
AND LKUP.LOOKUP_CODE = DECODE(TRIPLE.DUMNUM
, 1
, 'SELECTED_PERIOD'
, 2
, 'QUARTER_TO_DATE'
, 3
, 'YEAR_TO_DATE' )
AND SUMMBAL.OBJECT_ID = PAOBJ.OBJECT_ID
AND SUMMBAL.VERSION_ID = -1
AND SUMMBAL.PERIOD_TYPE = PA_REP_UTIL_GLOB.GETPERIODTYPEPA
AND SUMMBAL.PERIOD_SET_NAME = PA_REP_UTIL_GLOB.GETPERIODSETNAME
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID IN ( 1 /* G_RES_HRS_C */
, 3 /* G_RES_WTDHRS_PEOPLE_C */
, 9 /* G_RES_CAP_C */
, 10 /* G_RES_REDUCEDCAP_C */ )
AND SUMMBAL.OBJECT_TYPE_CODE = PA_REP_UTIL_GLOB.GETOBJECTTYPERES
AND SUMMBAL.PERIOD_NUM <= PA_REP_UTIL_GLOB.GETU3EFFPERIODNUM
AND PAOBJ.OBJECT_TYPE_CODE = PA_REP_UTIL_GLOB.GETOBJECTTYPERES
AND PAOBJ.EXPENDITURE_ORG_ID = PA_REP_UTIL_GLOB.GETORGID
AND PAOBJ.PROJECT_ORG_ID = -1
AND PAOBJ.PROJECT_ORGANIZATION_ID = -1
AND PAOBJ.PROJECT_ID = -1
AND PAOBJ.TASK_ID = -1
AND PAOBJ.ASSIGNMENT_ID = -1
AND PAOBJ.WORK_TYPE_ID = -1
AND PAOBJ.ORG_UTIL_CATEGORY_ID = -1
AND PAOBJ.RES_UTIL_CATEGORY_ID = -1
AND PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS GROUP BY PAOBJ.PERSON_ID
, TRIPLE.DUMNUM
, LKUP.MEANING

Columns