View: PA_REP_UTIL_OWTVW_PA_V

Product:	PA - Projects
Description:	PA_REP_UTIL_OWTVW_PA_V is a view to provide Total Hours and Total Weighted Hours values for organization/WorkType combinations across PA Period. This information is used by Disco Workbooks.
Implementation/DBA Data:	APPS.PA_REP_UTIL_OWTVW_PA_V

View Text

SELECT PAOBJ.EXPENDITURE_ORGANIZATION_ID AS EXPENDITURE_ORGANIZATION_ID
, PAOBJ.ORG_UTIL_CATEGORY_ID AS ORG_UTIL_CATEGORY_ID
, UCATG.NAME AS ORG_UTIL_CATEGORY_NAME
, UCATG.REPORTING_ORDER AS ORG_REPORTING_ORDER
, PAOBJ.WORK_TYPE_ID AS WORK_TYPE_ID
, UWKTY.NAME AS WORK_TYPE_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 = 31 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) AS TOTAL_HOURS
, MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 32 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) AS TOTAL_WEIGHTED_HOURS
FROM PA_SUMM_BALANCES SUMMBAL
, GL_PERIODS GLPRD
, PA_UTIL_CATEGORIES_V UCATG
, PA_WORK_TYPES_TL UWKTY
, 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 SUMMBAL.PERIOD_TYPE = PA_REP_UTIL_GLOB.GETPERIODTYPEPA
AND SUMMBAL.OBJECT_TYPE_CODE = PA_REP_UTIL_GLOB.GETOBJECTTYPEORGWT
AND SUMMBAL.AMOUNT_TYPE_ID IN ( 31 /* G_ORG_TOT_HRS_C */
, 32 /* G_ORG_TOT_WTDHRS_ORG_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 = -1
AND PAOBJ.WORK_TYPE_ID = UWKTY.WORK_TYPE_ID
AND PAOBJ.ORG_UTIL_CATEGORY_ID = UCATG.UTIL_CATEGORY_ID
AND PAOBJ.RES_UTIL_CATEGORY_ID = -1 GROUP BY PAOBJ.EXPENDITURE_ORGANIZATION_ID
, PAOBJ.ORG_UTIL_CATEGORY_ID
, UCATG.NAME
, UCATG.REPORTING_ORDER
, PAOBJ.WORK_TYPE_ID
, UWKTY.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