PA_REP_UTIL_FCT_RSUM_GE_V is a view to provide Forecast Capacity and Total Forecast 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 = 4 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 = 4 THEN NVL(SUMMBAL.PERIOD_BALANCE , 0) WHEN TRIPLE.DUMNUM = 3 AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM AND SUMMBAL.AMOUNT_TYPE_ID = 4 THEN NVL(SUMMBAL.PERIOD_BALANCE , 0) ELSE +0 END) AS PROVISIONAL_HOURS , 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) -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 = 4 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 = 4 THEN NVL(SUMMBAL.PERIOD_BALANCE , 0) WHEN TRIPLE.DUMNUM = 3 AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM AND SUMMBAL.AMOUNT_TYPE_ID = 4 THEN NVL(SUMMBAL.PERIOD_BALANCE , 0) ELSE +0 END) AS CONFIRMED_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) -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 = 6 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 = 6 THEN NVL(SUMMBAL.PERIOD_BALANCE , 0) WHEN TRIPLE.DUMNUM = 3 AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM AND SUMMBAL.AMOUNT_TYPE_ID = 6 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 , 'SELECTED_MONTH' , 3 , 'SELECTED_YEAR' ) 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 , 4 , 6 , 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.GETBALTYPEFORECAST GROUP BY PAOBJ.PERSON_ID , TRIPLE.DUMNUM , LKUP.MEANING
APPS.PA_REP_UTIL_FCT_RSUM_GE_V
[Home] [Help]