View: PA_REP_UTIL_RWTVW_GL_V

Product:	PA - Projects
Description:	PA_REP_UTIL_RWTVW_GL_V is a view to provide Total Hours and Total Weighted Hours values for resources across GL Periods. This information is used by Disco Workbooks.
Implementation/DBA Data:	APPS.PA_REP_UTIL_RWTVW_GL_V

View Text

SELECT PAOBJ.EXPENDITURE_ORGANIZATION_ID AS EXPENDITURE_ORGANIZATION_ID
, PAOBJ.ORG_UTIL_CATEGORY_ID AS ORG_UTIL_CATEGORY_ID
, UCATGO.NAME AS ORG_UTIL_CATEGORY_NAME
, UCATGO.REPORTING_ORDER AS ORG_REPORTING_ORDER
, PAOBJ.RES_UTIL_CATEGORY_ID AS RES_UTIL_CATEGORY_ID
, UCATGR.NAME AS RES_UTIL_CATEGORY_NAME
, UCATGR.REPORTING_ORDER AS RES_REPORTING_ORDER
, PAOBJ.WORK_TYPE_ID AS WORK_TYPE_ID
, UWKTY.NAME AS WORK_TYPE_NAME
, PAOBJ.PERSON_ID AS PERSON_ID
, RESDNORM.RESOURCE_NAME AS PERSON_NAME
, RESDNORM.MANAGER_ID AS MANAGER_ID
, RESDNORM.MANAGER_NAME AS MANAGER_NAME
, SUMMBAL.PERIOD_NAME AS PERIOD_NAME
, SUMMBAL.PERIOD_NUM AS PERIOD_NUM
, GLPRD.START_DATE AS PERIOD_START_DATE
, GLPRD.END_DATE AS PERIOD_END_DATE
, SUMMBAL.QUARTER_OR_MONTH_NUMBER AS PERIOD_QUARTER
, SUMMBAL.PERIOD_YEAR AS PERIOD_YEAR
, PAOBJ.BALANCE_TYPE_CODE AS BALANCE_TYPE_CODE
, MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 1 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) AS TOTAL_HOURS
, MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 4 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) AS TOTAL_PROV_HOURS
, MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 1 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) - MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 4 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) AS TOTAL_CONF_HOURS
, MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 3 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) AS TOTAL_WEIGHTED_HOURS_PEOPLE
, MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 2 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) AS TOTAL_WEIGHTED_HOURS_ORG
, MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 6 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) AS PROV_WEIGHTED_HOURS_PEOPLE
, MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 5 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) AS PROV_WEIGHTED_HOURS_ORG
, MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 3 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) - MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 6 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) AS CONF_WEIGHTED_HOURS_PEOPLE
, MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 2 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) - MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 5 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) AS CONF_WEIGHTED_HOURS_ORG
FROM PA_SUMM_BALANCES SUMMBAL
, GL_PERIODS GLPRD
, PA_UTIL_CATEGORIES_V UCATGO
, PA_UTIL_CATEGORIES_V UCATGR
, PA_WORK_TYPES_TL UWKTY
, PA_RESOURCES_DENORM RESDNORM
, PA_OBJECTS PAOBJ
WHERE UWKTY.LANGUAGE = USERENV('LANG')
AND GLPRD.PERIOD_SET_NAME = SUMMBAL.PERIOD_SET_NAME
AND GLPRD.PERIOD_NAME = SUMMBAL.PERIOD_NAME
AND GLPRD.START_DATE BETWEEN RESDNORM.RESOURCE_EFFECTIVE_START_DATE
AND NVL(RESDNORM.RESOURCE_EFFECTIVE_END_DATE
, GLPRD.START_DATE)
AND SUMMBAL.PERIOD_TYPE = PA_REP_UTIL_GLOB.GETPERIODTYPEGL
AND SUMMBAL.OBJECT_TYPE_CODE = PA_REP_UTIL_GLOB.GETOBJECTTYPERESWT
AND SUMMBAL.AMOUNT_TYPE_ID IN ( 1 /* G_RES_HRS_C */
, 2 /* G_RES_WTDHRS_ORG_C */
, 3 /* G_RES_WTDHRS_PEOPLE_C */
, 4 /* G_RES_PRVHRS_C */
, 5 /* G_RES_PRVWTDHRS_ORG_C */
, 6 /* G_RES_PRVWTDHRS_PEOPLE_C */ )
AND SUMMBAL.PERIOD_SET_NAME = PA_REP_UTIL_GLOB.GETPERIODSETNAME
AND SUMMBAL.VERSION_ID = -1
AND PAOBJ.OBJECT_ID = SUMMBAL.OBJECT_ID
AND PAOBJ.OBJECT_TYPE_CODE = SUMMBAL.OBJECT_TYPE_CODE
AND PAOBJ.BALANCE_TYPE_CODE IN (PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
, PA_REP_UTIL_GLOB.GETBALTYPEFORECAST)
AND PAOBJ.PROJECT_ORG_ID = -1
AND PAOBJ.PROJECT_ORGANIZATION_ID = -1
AND PAOBJ.PROJECT_ID = -1
AND PAOBJ.TASK_ID = -1
AND NVL(PAOBJ.EXPENDITURE_ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99) ) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)
AND PAOBJ.PERSON_ID = RESDNORM.PERSON_ID
AND PAOBJ.WORK_TYPE_ID = UWKTY.WORK_TYPE_ID
AND PAOBJ.ORG_UTIL_CATEGORY_ID = UCATGO.UTIL_CATEGORY_ID
AND PAOBJ.RES_UTIL_CATEGORY_ID = UCATGR.UTIL_CATEGORY_ID GROUP BY PAOBJ.EXPENDITURE_ORGANIZATION_ID
, PAOBJ.ORG_UTIL_CATEGORY_ID
, UCATGO.NAME
, UCATGO.REPORTING_ORDER
, PAOBJ.RES_UTIL_CATEGORY_ID
, UCATGR.NAME
, UCATGR.REPORTING_ORDER
, PAOBJ.WORK_TYPE_ID
, UWKTY.NAME
, PAOBJ.PERSON_ID
, RESDNORM.RESOURCE_NAME
, RESDNORM.MANAGER_ID
, RESDNORM.MANAGER_NAME
, SUMMBAL.PERIOD_NAME
, SUMMBAL.PERIOD_NUM
, GLPRD.START_DATE
, GLPRD.END_DATE
, SUMMBAL.QUARTER_OR_MONTH_NUMBER
, SUMMBAL.PERIOD_YEAR
, PAOBJ.BALANCE_TYPE_CODE

Columns