View: PA_REP_UTIL_RESVW_PA_V

Product:	PA - Projects
Description:	PA_REP_UTIL_RESVW_PA_V is a view to provide Capacity and Total Hours values for resources across Global Week. This information is used by Disco Workbooks.
Implementation/DBA Data:	APPS.PA_REP_UTIL_RESVW_PA_V

View Text

SELECT PAOBJ.EXPENDITURE_ORGANIZATION_ID AS EXPENDITURE_ORGANIZATION_ID
, HRORGTL.NAME AS EXPENDITURE_ORGANIZATION_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
, LKUPB.MEANING AS BALANCE_TYPE_NAME
, 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
, DECODE( SIGN( MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) - MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END))
, 1
, MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) - MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END)
, +0) AS CAPACITY
FROM PA_SUMM_BALANCES SUMMBAL
, PA_LOOKUPS LKUPB
, GL_PERIODS GLPRD
, PA_RESOURCES_DENORM RESDNORM
, PA_OBJECTS PAOBJ
, HR_ALL_ORGANIZATION_UNITS_TL HRORGTL
, HR_ORG_UNITS_NO_JOIN HRORGNJ
WHERE LKUPB.LOOKUP_TYPE = 'PA_BALANCE_TYPES'
AND LKUPB.LOOKUP_CODE IN (PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
, PA_REP_UTIL_GLOB.GETBALTYPEFORECAST)
AND HRORGNJ.ORGANIZATION_ID(+) = PAOBJ.EXPENDITURE_ORGANIZATION_ID
AND HRORGNJ.ORGANIZATION_ID = HRORGTL.ORGANIZATION_ID(+)
AND DECODE(HRORGTL.ORGANIZATION_ID
, NULL
, '1'
, HRORGTL.LANGUAGE) = DECODE(HRORGTL.ORGANIZATION_ID
, NULL
, '1'
, 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.GETPERIODTYPEPA
AND SUMMBAL.OBJECT_TYPE_CODE = PA_REP_UTIL_GLOB.GETOBJECTTYPERES
AND SUMMBAL.AMOUNT_TYPE_ID IN ( 1 /* G_RES_HRS_C */
, 9 /* G_RES_CAP_C */
, 10 /* G_RES_REDUCEDCAP_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 = LKUPB.LOOKUP_CODE
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 = -1
AND PAOBJ.ORG_UTIL_CATEGORY_ID = -1
AND PAOBJ.RES_UTIL_CATEGORY_ID = -1 GROUP BY PAOBJ.EXPENDITURE_ORGANIZATION_ID
, HRORGTL.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
, LKUPB.MEANING
, PAOBJ.BALANCE_TYPE_CODE

Columns