Overcommitted Resource Hours: Overcommitted Resource Hours by Global Week

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  i257519 as E257519,i257524 as E257524,i257672 as E257672,i257860 as E257860,i397673 as E397673,i397674 as E397674,i397683 as E397683,i397684 as E397684,i397686 as E397686,in125 as in125,SUM(i397672) as E397672_SUM,SUM(i397670) as E397670_SUM,( DECODE(SIGN(NVL(i397672,0)-NVL(i397670,0)),-1,0,NVL(i397672,0)-NVL(i397670,0)) )/DECODE(NVL(i397670,1),0,1,NVL(i397670,1))*100 as E_44,DECODE(SIGN(NVL(i397672,0)-NVL(i397670,0)),-1,0,NVL(i397672,0)-NVL(i397670,0)) as E_41,i397682 as E397682
 FROM ( SELECT MONTH_NAME AS i257519, MONTH_NUM AS i257523 FROM PA_REP_MONTH_V ) o257393,
      ( SELECT PERIOD_YEAR AS i257524 FROM PA_REP_YEAR_CAL_V ) o257395,
      ( SELECT MANAGER_ID AS i257614, MANAGER_NAME AS i257860 FROM PA_REP_ALL_MGR_V ) o257419,
      ( 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,
      ( SELECT CAPACITY_HOURS AS i397670, CONFIRMED_HOURS AS i397672, GLOBAL_WEEK_END_DATE AS i397673, JOB_NAME AS i397674, MANAGER_ID AS i397675, MANAGER_NAME AS i397676, MONTH AS i397677, ORGANIZATION_ID AS i397678, PROVISIONAL_HOURS AS i397680, RESOURCE_ID AS i397681, RESOURCE_JOB_LEVEL AS i397682, RESOURCE_NAME AS i397683, WEEK_DAY AS i397684, YEAR AS i397685, TO_CHAR(TRUNC(WEEK_DAY),'DY') AS i397686, CONFIRMED_HOURS+PROVISIONAL_HOURS AS i397688 FROM PA_REP_RES_CAP_WKD_V ) o397668,
      ( SELECT i257519 AS in123, MAX(i257523) AS in125 FROM ( SELECT MONTH_NAME AS i257519, MONTH_NUM AS i257523 FROM PA_REP_MONTH_V ) IO257393  GROUP BY i257519 ) on121
 WHERE 1=1 AND ( i257523 = i397677
   and i257524 = i397685
   and i257614 = i397675
   and i257538 = i397678
   and (i257519 = in123(+)))
   AND ( RTRIMo257393.i257519 = RTRIM(:calendar_month)  AND 1 = 1 )
 GROUP BY in125, ( DECODE(SIGN(NVL(i397672,0)-NVL(i397670,0)),-1,0,NVL(i397672,0)-NVL(i397670,0)) )/DECODE(NVL(i397670,1),0,1,NVL(i397670,1))*100, DECODE(SIGN(NVL(i397672,0)-NVL(i397670,0)),-1,0,NVL(i397672,0)-NVL(i397670,0)), i257519, i257524, i257672, i257860, i397673, i397674, i397682, i397683, i397684, i397686
Parameter Name SQL text Validation
Starting Organization
o257434.i257874 = :starting_organization
Year
o257395.i257524 = :year
Calendar Month
 
Resource Manager
o257419.i257860 = :resource_manager