Available Resource Hours: Available Resource Hours by PA Period

Description
Categories: Discoverer
Columns: Job Name, Child Org Name, Pa Period Name, Quarter, Period Start Date, Manager Name, Resource Name, (Decode(Sign(Nvl((Sum(Pa Rep Res Cap Pa V.Capacity Hours)),0)-Nvl((Sum(Pa Rep Res Schhrs Pa V.Confirmed Hours)),0)),-1,0,Nvl((Sum(Pa Rep Res Cap Pa V.Capacity Hours)),0)-Nvl((Sum(Pa Rep Res Schhrs Pa V.Confirmed Hours)),0)))/Decode(Nvl((Sum(Pa Rep Res, Decode(Sign(Nvl((Sum(Pa Rep Res Cap Pa V.Capacity Hours)),0)-Nvl((Sum(Pa Rep Res Schhrs Pa V.Confirmed Hours)),0)),-1,0,Nvl((Sum(Pa Rep Res Cap Pa V.Capacity Hours)),0)-Nvl((Sum(Pa Rep Res Schhrs Pa V.Confirmed Hours)),0)), (Decode(Sign(Nvl((Sum(Pa Rep Res Cap Pa V.Capacity Hours)),0)-Nvl((Sum((Pa Rep Res Schhrs Pa V.Confirmed Hours+Pa Rep Res Schhrs Pa V.Provisional Hours))),0)),-1,0,Nvl((Sum(Pa Rep Res Cap Pa V.Capacity Hours)),0)-Nvl((Sum((Pa Rep Res Schhrs Pa V.Confi ...
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_PA_V.JOB_NAME, PA_REP_ALL_REP_ORG_V.CHILD_ORG_NAME, PA_REP_RES_CAP_PA_V.PA_PERIOD_NAME, PA_REP_RES_CAP_PA_V.QUARTER, PA_REP_RES_CAP_PA_V.PERIOD_START_DATE, PA_REP_RES_CAP_PA_V.MANAGER_NAME, PA_REP_RES_CAP_PA_V.RESOURCE_NAME, (DECODE(SIGN(NVL((SUM(PA_REP_RES_CAP_PA_V.CAPACITY_HOURS) ),0)-NVL((SUM(PA_REP_RES_SCHHRS_PA_V.CONFIRMED_HOURS) ),0)),-1,0,NVL((SUM(PA_REP_RES_CAP_PA_V.CAPACITY_HOURS) ),0)-NVL((SUM(PA_REP_RES_SCHHRS_PA_V.CONFIRMED_HOURS) ),0)) )/DECODE(NVL((SUM(PA_REP_RES_CAP_PA_V.CAPACITY_HOURS) ),0),0,1,NVL((SUM(PA_REP_RES_CAP_PA_V.CAPACITY_HOURS) ),0))*100, DECODE(SIGN(NVL((SUM(PA_REP_RES_CAP_PA_V.CAPACITY_HOURS) ),0)-NVL((SUM(PA_REP_RES_SCHHRS_PA_V.CONFIRMED_HOURS) ),0)),-1,0,NVL((SUM(PA_REP_RES_CAP_PA_V.CAPACITY_HOURS) ),0)-NVL((SUM(PA_REP_RES_SCHHRS_PA_V.CONFIRMED_HOURS) ),0)), (DECODE(SIGN(NVL((SUM(PA_REP_RES_CAP_PA_V.CAPACITY_HOURS) ),0)-NVL((SUM((PA_REP_RES_SCHHRS_PA_V.CONFIRMED_HOURS+PA_REP_RES_SCHHRS_PA_V.PROVISIONAL_HOURS )) ),0)),-1,0,NVL((SUM(PA_REP_RES_CAP_PA_V.CAPACITY_HOURS) ),0)-NVL((SUM((PA_REP_RES_SCHHRS_PA_V.CONFIRMED_HOURS+PA_REP_RES_SCHHRS_PA_V.PROVISIONAL_HOURS )) ),0)) )/DECODE(NVL((SUM(PA_REP_RES_CAP_PA_V.CAPACITY_HOURS) ),0),0,1,NVL((SUM(PA_REP_RES_CAP_PA_V.CAPACITY_HOURS) ),0))*100, DECODE(SIGN(NVL((SUM(PA_REP_RES_CAP_PA_V.CAPACITY_HOURS) ),0)-NVL((SUM((PA_REP_RES_SCHHRS_PA_V.CONFIRMED_HOURS+PA_REP_RES_SCHHRS_PA_V.PROVISIONAL_HOURS )) ),0)),-1,0,NVL((SUM(PA_REP_RES_CAP_PA_V.CAPACITY_HOURS) ),0)-NVL((SUM((PA_REP_RES_SCHHRS_PA_V.CONFIRMED_HOURS+PA_REP_RES_SCHHRS_PA_V.PROVISIONAL_HOURS )) ),0)), PA_REP_RES_CAP_PA_V.RESOURCE_JOB_LEVEL
FROM PA_REP_RES_CAP_PA_V PA_REP_RES_CAP_PA_V, PA_REP_RES_SCHHRS_PA_V PA_REP_RES_SCHHRS_PA_V, PA_REP_ALL_REP_ORG_V PA_REP_ALL_REP_ORG_V WHERE 1=1 AND ((PA_REP_RES_CAP_PA_V.RESOURCE_ID = PA_REP_RES_SCHHRS_PA_V.RESOURCE_ID(+) AND PA_REP_RES_CAP_PA_V.PA_PERIOD_NAME = PA_REP_RES_SCHHRS_PA_V.PA_PERIOD(+) ) AND (PA_REP_ALL_REP_ORG_V.CHILD_ORGANIZATION_ID = PA_REP_RES_CAP_PA_V.ORGANIZATION_ID ) ) AND (1 = 1 )
GROUP BY PA_REP_RES_CAP_PA_V.JOB_NAME, PA_REP_ALL_REP_ORG_V.CHILD_ORG_NAME, PA_REP_RES_CAP_PA_V.PA_PERIOD_NAME, PA_REP_RES_CAP_PA_V.QUARTER, PA_REP_RES_CAP_PA_V.PERIOD_START_DATE, PA_REP_RES_CAP_PA_V.RESOURCE_JOB_LEVEL, PA_REP_RES_CAP_PA_V.MANAGER_NAME, PA_REP_RES_CAP_PA_V.RESOURCE_NAME
Parameter Name SQL text Validation
Resource Manager
PA_REP_RES_CAP_PA_V.MANAGER_NAME = :resource_manager
LOV
Quarter
PA_REP_RES_CAP_PA_V.QUARTER = :quarter
LOV
Year
PA_REP_RES_CAP_PA_V.YEAR = :year
LOV
Starting Organization
PA_REP_ALL_REP_ORG_V.PARENT_ORG_NAME = :starting_organization
LOV