Overcommitted Resource Hours: Overcommitted Resource Hours by PA Period

Description
Categories: Discoverer
The overcommitted resource hours workbook allows you to evaluate the number of hours people are currently scheduled over their capacity.
Owner: SYSADMIN (System Administrator)
Identifier: PAOVCRES
SELECT  i257619 as E257619,i257672 as E257672,i257698 as E257698,i257704 as E257704,i257751 as E257751,i257859 as E257859,i257863 as E257863,(DECODE(SIGN(NVL((SUM(in409) ),0)-NVL((SUM(i257536) ),0)),-1,0,NVL((SUM(in409) ),0)-NVL((SUM(i257536) ),0)) )/DECODE(NVL((SUM(i257536) ),1),0,1,NVL((SUM(i257536) ),1))*100 as E_423,DECODE(SIGN(NVL((SUM(in409) ),0)-NVL((SUM(i257536) ),0)),-1,0,NVL((SUM(in409) ),0)-NVL((SUM(i257536) ),0)) as E_429,SUM(i257536) as E257536_SUM,SUM(in409) as "E-409_SUM",i257855 as E257855
 FROM (SELECT SUM(i257549) AS in409, i257851 AS in415, i257699 AS in417 FROM (SELECT CONFIRMED_HOURS AS i257549, PA_PERIOD AS i257699, PERIOD_SET_NAME AS i257745, PROVISIONAL_HOURS AS i257825, RESOURCE_ID AS i257851, CONFIRMED_HOURS+PROVISIONAL_HOURS AS i257959 FROM PA_REP_RES_SCHHRS_PA_V ) IO257424  GROUP BY i257699, i257851 ) on407,
      (SELECT CAPACITY_HOURS AS i257536, JOB_NAME AS i257619, MANAGER_ID AS i257628, ORGANIZATION_ID AS i257663, PA_PERIOD_NAME AS i257698, QUARTER AS i257704, YEAR AS i257707, PERIOD_START_DATE AS i257751, RESOURCE_ID AS i257846, RESOURCE_JOB_LEVEL AS i257855, MANAGER_NAME AS i257859, RESOURCE_NAME AS i257863 FROM PA_REP_RES_CAP_PA_V ) o257418,
      (SELECT CHILD_ORGANIZATION_ID AS i257538, CHILD_ORG_NAME AS i257672, PARENT_ORGANIZATION_ID AS i257693, PARENT_ORG_NAME AS i257874 FROM PA_REP_ALL_REP_ORG_V ) o257434
 WHERE 1=1 AND ((i257846 = in415(+) AND i257698 = in417(+))
   and i257538 = i257663)
   AND (1 = 1 )
 GROUP BY i257619, i257672, i257698, i257704, i257751, i257855, i257859, i257863
Parameter NameSQL textValidation
Starting Organization
o257434.i257874 = :starting_organization
Year
o257418.i257707 = :year
Quarter
o257418.i257704 = :quarter
Resource Manager
o257418.i257859 = :resource_manager