PA_REP_UTIL_ACT_RSUM_GE_V is a view to provide Actual Capacity and Total Actual Hours values for Person Resources across Global Week. This information is used by OA Screens.
SELECT LKUP.MEANING AS TITLE_CODE , PAOBJ.PERSON_ID AS PERSON_ID , DECODE( SIGN( SUM(CASE WHEN TRIPLE.DUMNUM = 1 AND SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE , 'MM/DD/YYYY') 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.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE , 'MM/DD/YYYY') 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.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE , 'MM/DD/YYYY') 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.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE , 'MM/DD/YYYY') 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 , SUM(CASE WHEN TRIPLE.DUMNUM = 1 AND SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE , 'MM/DD/YYYY') 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 , ROUND(NVL( SUM(CASE WHEN TRIPLE.DUMNUM = 1 AND SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE , 'MM/DD/YYYY') 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.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE , 'MM/DD/YYYY') 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.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE , 'MM/DD/YYYY') 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.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE , 'MM/DD/YYYY') 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.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE , 'MM/DD/YYYY') 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.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE , 'MM/DD/YYYY') 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.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE , 'MM/DD/YYYY') 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) , 0) AS UTILIZATION 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 , 'MONTH_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.GETPERIODTYPEGE AND SUMMBAL.PERIOD_SET_NAME = PA_REP_UTIL_GLOB.GETDUMMY AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM AND SUMMBAL.PERIOD_NAME = PA_REP_UTIL_GLOB.GETDUMMY AND SUMMBAL.AMOUNT_TYPE_ID IN ( 1 , 3 , 9 , 10 ) AND SUMMBAL.OBJECT_TYPE_CODE = PA_REP_UTIL_GLOB.GETOBJECTTYPERES AND SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE <= PA_REP_UTIL_GLOB.GETU3GEENDDATE 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