Available Resource Hours: Available Resource Hours by GL Period

Description
Categories: Discoverer
The Available Resource Hours workbook allows you to evaluate the number of capacity hours that are currently not scheduled within Project Resource Management.
Owner: SYSADMIN (System Administrator)
Identifier: PAAVLRES
SELECT PA_REP_RES_CAP_GL_V.GL_PERIOD_NAME, PA_REP_RES_CAP_GL_V.GL_PERIOD_NUMBER, PA_REP_RES_CAP_GL_V.QUARTER, PA_REP_RES_CAP_GL_V.JOB_NAME, PA_REP_ALL_REP_ORG_V.CHILD_ORG_NAME, PA_REP_RES_CAP_GL_V.MANAGER_NAME, PA_REP_RES_CAP_GL_V.RESOURCE_NAME, NVL((DECODE(SIGN(NVL((SUM(PA_REP_RES_CAP_GL_V.CAPACITY_HOURS) ),0)-NVL((SUM((NVL(PA_REP_RES_SCHHRS_GL_V.CONFIRMED_HOURS,0)+NVL(PA_REP_RES_SCHHRS_GL_V.PROVISIONAL_HOURS,0) )) ),0)),-1,0,NVL((SUM(PA_REP_RES_CAP_GL_V.CAPACITY_HOURS) ),0)-NVL((SUM((NVL(PA_REP_RES_SCHHRS_GL_V.CONFIRMED_HOURS,0)+NVL(PA_REP_RES_SCHHRS_GL_V.PROVISIONAL_HOURS,0) )) ),0)) ),0)/DECODE(NVL((SUM(PA_REP_RES_CAP_GL_V.CAPACITY_HOURS) ),0),0,1,NVL((SUM(PA_REP_RES_CAP_GL_V.CAPACITY_HOURS) ),0))*100, DECODE(SIGN(NVL((SUM(PA_REP_RES_CAP_GL_V.CAPACITY_HOURS) ),0)-NVL((SUM((NVL(PA_REP_RES_SCHHRS_GL_V.CONFIRMED_HOURS,0)+NVL(PA_REP_RES_SCHHRS_GL_V.PROVISIONAL_HOURS,0) )) ),0)),-1,0,NVL((SUM(PA_REP_RES_CAP_GL_V.CAPACITY_HOURS) ),0)-NVL((SUM((NVL(PA_REP_RES_SCHHRS_GL_V.CONFIRMED_HOURS,0)+NVL(PA_REP_RES_SCHHRS_GL_V.PROVISIONAL_HOURS,0) )) ),0)), NVL((DECODE(SIGN(NVL((SUM(PA_REP_RES_CAP_GL_V.CAPACITY_HOURS) ),0)-NVL((SUM(PA_REP_RES_SCHHRS_GL_V.CONFIRMED_HOURS) ),0)),-1,0,NVL((SUM(PA_REP_RES_CAP_GL_V.CAPACITY_HOURS) ),0)-NVL((SUM(PA_REP_RES_SCHHRS_GL_V.CONFIRMED_HOURS) ),0)) ),0)/DECODE(NVL((SUM(PA_REP_RES_CAP_GL_V.CAPACITY_HOURS) ),0),0,1,NVL((SUM(PA_REP_RES_CAP_GL_V.CAPACITY_HOURS) ),0))*100, DECODE(SIGN(NVL((SUM(PA_REP_RES_CAP_GL_V.CAPACITY_HOURS) ),0)-NVL((SUM(PA_REP_RES_SCHHRS_GL_V.CONFIRMED_HOURS) ),0)),-1,0,NVL((SUM(PA_REP_RES_CAP_GL_V.CAPACITY_HOURS) ),0)-NVL((SUM(PA_REP_RES_SCHHRS_GL_V.CONFIRMED_HOURS) ),0)), PA_REP_RES_CAP_GL_V.RESOURCE_JOB_LEVEL
FROM PA_REP_RES_CAP_GL_V PA_REP_RES_CAP_GL_V, PA_REP_RES_SCHHRS_GL_V PA_REP_RES_SCHHRS_GL_V, PA_REP_ALL_REP_ORG_V PA_REP_ALL_REP_ORG_V WHERE 1=1 AND ((PA_REP_RES_CAP_GL_V.RESOURCE_ID = PA_REP_RES_SCHHRS_GL_V.RESOURCE_ID(+) AND PA_REP_RES_CAP_GL_V.GL_PERIOD_NAME = PA_REP_RES_SCHHRS_GL_V.GL_PERIOD(+) AND PA_REP_RES_CAP_GL_V.GL_PERIOD_SET_NAME = PA_REP_RES_SCHHRS_GL_V.GL_PERIOD_SET_NAME(+) ) AND (PA_REP_ALL_REP_ORG_V.CHILD_ORGANIZATION_ID = PA_REP_RES_CAP_GL_V.ORGANIZATION_ID ) ) AND (1 = 1 )
GROUP BY PA_REP_RES_CAP_GL_V.GL_PERIOD_NAME, PA_REP_RES_CAP_GL_V.GL_PERIOD_NUMBER, PA_REP_RES_CAP_GL_V.QUARTER, PA_REP_RES_CAP_GL_V.JOB_NAME, PA_REP_ALL_REP_ORG_V.CHILD_ORG_NAME, PA_REP_RES_CAP_GL_V.RESOURCE_JOB_LEVEL, PA_REP_RES_CAP_GL_V.MANAGER_NAME, PA_REP_RES_CAP_GL_V.RESOURCE_NAME
Parameter Name SQL text Validation
Resource Manager
PA_REP_RES_CAP_GL_V.MANAGER_NAME = :resource_manager
LOV
Quarter
PA_REP_RES_CAP_GL_V.QUARTER = :quarter
LOV
Year
PA_REP_RES_CAP_GL_V.YEAR = :year
LOV
Starting Organization
PA_REP_ALL_REP_ORG_V.PARENT_ORG_NAME = :starting_organization
LOV