eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PA_REP_UTIL_ORG_PA_V

Source

View Text - Preformatted


SELECT DECODE(paobj.expenditure_organization_id , orgdnorm.parent_organization_id, lkup.meaning, NULL) AS TITLE_NAME , DECODE(paobj.expenditure_organization_id , orgdnorm.parent_organization_id, lkup.lookup_code, NULL) AS TITLE_CODE , orgdnorm.parent_organization_id AS EXP_ORGANIZATION_ID , paobj.expenditure_organization_id AS EXP_SUB_ORGANIZATION_ID , max(hrorgtl.name) AS EXP_SUB_ORGANIZATION_NAME , max(CASE when paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' then 0 when paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' then 0 when paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' then 0 else 0 end ) AS EMP_HEAD_COUNT , max(CASE when paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' then 0 when paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' then 0 when paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' then 0 else 0 end ) AS OTHERS_HEAD_COUNT , DECODE( sign( max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 17 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 27 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 37 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 37 then NVL(summbal.period_balance,0) else +0 end) -max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 18 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 28 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 38 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 38 then NVL(summbal.period_balance,0) else +0 end)) , 1, max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 17 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 27 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 37 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 37 then NVL(summbal.period_balance,0) else +0 end) -max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 18 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 28 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 38 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 38 then NVL(summbal.period_balance,0) else +0 end) , +0) AS ACTUALS_CAPACITY , max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 11 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 21 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 31 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 31 then NVL(summbal.period_balance,0) else +0 end) AS ACTUALS_HOURS , max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 12 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 22 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 32 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 32 then NVL(summbal.period_balance,0) else +0 end) AS ACTUALS_WEIGHTED_HOURS ,ROUND(NVL( max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 12 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 22 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 32 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 32 then NVL(summbal.period_balance,0) else +0 end)*100/ DECODE( PA_REP_UTIL_GLOB.GetU1ShowPrctgBy, 'CAPACITY', DecodE( sign( max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 17 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 27 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 37 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 37 then NVL(summbal.period_balance,0) else +0 end) -max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 18 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 28 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 38 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 38 then NVL(summbal.period_balance,0) else +0 end)) , 1, max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 17 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 27 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 37 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 37 then NVL(summbal.period_balance,0) else +0 end) -max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 18 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 28 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 38 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 38 then NVL(summbal.period_balance,0) else +0 end) , 1) , 'TOTAL_WORKED_HOURS' , max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 11 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 21 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 31 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 31 then NVL(summbal.period_balance,0) else +0 end) ) , -9999) , 0) AS ACTUALS_UTILIZATION , DECODE( sign( max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 17 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 27 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 37 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 37 then NVL(summbal.period_balance,0) else +0 end) -max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 18 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 28 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 38 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 38 then NVL(summbal.period_balance,0) else +0 end)) , 1, max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 17 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 27 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 37 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 37 then NVL(summbal.period_balance,0) else +0 end) -max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 18 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 28 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 38 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 38 then NVL(summbal.period_balance,0) else +0 end) , +0) AS FORECAST_CAPACITY , max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 11 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 21 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 31 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 31 then NVL(summbal.period_balance,0) else +0 end) AS FORECAST_HOURS , max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 12 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 22 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 32 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 32 then NVL(summbal.period_balance,0) else +0 end) AS FORECAST_WEIGHTED_HOURS ,ROUND(NVL( max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 12 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 22 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 32 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 32 then NVL(summbal.period_balance,0) else +0 end)*100/ DECODE( PA_REP_UTIL_GLOB.GetU1ShowPrctgBy, 'CAPACITY', DecodE( sign( max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 17 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 27 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 37 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 37 then NVL(summbal.period_balance,0) else +0 end) -max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 18 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 28 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 38 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 38 then NVL(summbal.period_balance,0) else +0 end)) , 1, max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 17 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 27 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 37 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 37 then NVL(summbal.period_balance,0) else +0 end) -max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 18 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 28 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 38 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 38 then NVL(summbal.period_balance,0) else +0 end) , 1) , 'TOTAL_WORKED_HOURS' , DecodE( sign( max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 11 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 21 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 31 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 31 then NVL(summbal.period_balance,0) else +0 end)) , 1, max(CASE when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'SUB_ORGANIZATIONS' and summbal.amount_type_id = 11 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'DIRECT_PEOPLE' and summbal.amount_type_id = 21 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id = orgdnorm.parent_organization_id and lkup.lookup_code = 'TOTALS' and summbal.amount_type_id = 31 then NVL(summbal.period_balance,0) when paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast and paobj.expenditure_organization_id <> orgdnorm.parent_organization_id and summbal.amount_type_id = 31 then NVL(summbal.period_balance,0) else +0 end) , 1) ) , -9999) , 0) AS FORECAST_UTILIZATION , max(summbal.period_set_name) AS PERIOD_SET_NAME , max(summbal.period_year) AS PERIOD_YEAR , max(summbal.quarter_or_month_number) AS PERIOD_QUARTER , summbal.period_name AS PERIOD_NAME , max(summbal.period_num) AS PERIOD_NUM from PA_Summ_Balances summbal , PA_Objects paobj , pa_lookups lkup , PA_Org_Hierarchy_Denorm orgdnorm , hr_all_organization_units_tl hrorgtl , hr_org_units_no_join hrorgnj WHERE lkup.lookup_type = 'PA_OASCREEN_CHARS' AND hrorgnj.organization_id(+) = paobj.expenditure_organization_id AND hrorgnj.organization_id = hrorgtl.organization_id(+) AND hrorgtl.LANGUAGE = USERENV('LANG') AND orgdnorm.child_organization_id = paobj.expenditure_organization_id AND orgdnorm.org_hierarchy_version_id = PA_REP_UTIL_GLOB.GetOrgStructureVersionId AND orgdnorm.pa_org_use_type = 'REPORTING' AND NVL(orgdnorm.org_id,-99) = PA_REP_UTIL_GLOB.GetOrgID AND orgdnorm.parent_level - orgdnorm.child_level <= 1 AND summbal.object_id = paobj.object_id AND summbal.version_id = -1 AND summbal.period_type = PA_REP_UTIL_GLOB.GetPeriodTypePa AND summbal.period_set_name = PA_REP_UTIL_GLOB.GetPeriodSetName AND summbal.amount_type_id in ( 11 , 12 , 17 , 18 , 21 , 22 , 27 , 28 , 31 , 32 , 37 , 38 ) AND summbal.object_type_code = PA_REP_UTIL_GLOB.GetObjectTypeOrg AND paobj.object_type_code = PA_REP_UTIL_GLOB.GetObjectTypeOrg AND paobj.balance_type_code in (PA_REP_UTIL_GLOB.GetBalTypeActuals,PA_REP_UTIL_GLOB.GetBalTypeForecast) AND paobj.expenditure_org_id = PA_REP_UTIL_GLOB.GetOrgId AND paobj.project_org_id = -1 AND paobj.expenditure_organization_id = DECODE(lkup.lookup_code , 'SUB_ORGANIZATIONS', orgdnorm.parent_organization_id , 'DIRECT_PEOPLE', orgdnorm.parent_organization_id , 'TOTALS', orgdnorm.child_organization_id , orgdnorm.child_organization_id ) AND paobj.project_organization_id = -1 AND paobj.project_id = -1 AND paobj.task_id = -1 AND paobj.person_id = -1 AND paobj.assignment_id = -1 AND paobj.work_type_id = -1 AND paobj.org_util_category_id = -1 AND paobj.res_util_category_id = -1 AND orgdnorm.parent_organization_id = PA_REP_UTIL_GLOB.GetU1OrganizationID AND summbal.period_name = PA_REP_UTIL_GLOB.GetU1PeriodName group by orgdnorm.parent_organization_id , paobj.expenditure_organization_id , DECODE(paobj.expenditure_organization_id , orgdnorm.parent_organization_id, lkup.meaning, NULL) , DECODE(paobj.expenditure_organization_id , orgdnorm.parent_organization_id, lkup.lookup_code, NULL) , summbal.period_name

View Text - HTML Formatted

SELECT DECODE(PAOBJ.EXPENDITURE_ORGANIZATION_ID
, ORGDNORM.PARENT_ORGANIZATION_ID
, LKUP.MEANING
, NULL) AS TITLE_NAME
, DECODE(PAOBJ.EXPENDITURE_ORGANIZATION_ID
, ORGDNORM.PARENT_ORGANIZATION_ID
, LKUP.LOOKUP_CODE
, NULL) AS TITLE_CODE
, ORGDNORM.PARENT_ORGANIZATION_ID AS EXP_ORGANIZATION_ID
, PAOBJ.EXPENDITURE_ORGANIZATION_ID AS EXP_SUB_ORGANIZATION_ID
, MAX(HRORGTL.NAME) AS EXP_SUB_ORGANIZATION_NAME
, MAX(CASE WHEN PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS' THEN 0 WHEN PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE' THEN 0 WHEN PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS' THEN 0 ELSE 0 END ) AS EMP_HEAD_COUNT
, MAX(CASE WHEN PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS' THEN 0 WHEN PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE' THEN 0 WHEN PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS' THEN 0 ELSE 0 END ) AS OTHERS_HEAD_COUNT
, DECODE( SIGN( MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 17 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 27 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 37 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 37 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) -MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 18 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 28 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 38 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 38 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END))
, 1
, MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 17 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 27 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 37 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 37 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) -MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 18 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 28 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 38 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 38 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END)
, +0) AS ACTUALS_CAPACITY
, MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 11 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 21 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 31 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 31 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) AS ACTUALS_HOURS
, MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 12 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 22 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 32 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 32 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) AS ACTUALS_WEIGHTED_HOURS
, ROUND(NVL( MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 12 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 22 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 32 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 32 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END)*100/ DECODE( PA_REP_UTIL_GLOB.GETU1SHOWPRCTGBY
, 'CAPACITY'
, DECODE( SIGN( MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 17 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 27 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 37 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 37 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) -MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 18 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 28 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 38 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 38 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END))
, 1
, MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 17 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 27 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 37 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 37 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) -MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 18 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 28 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 38 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 38 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END)
, 1)
, 'TOTAL_WORKED_HOURS'
, MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 11 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 21 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 31 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 31 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) )
, -9999)
, 0) AS ACTUALS_UTILIZATION
, DECODE( SIGN( MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 17 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 27 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 37 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 37 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) -MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 18 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 28 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 38 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 38 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END))
, 1
, MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 17 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 27 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 37 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 37 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) -MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 18 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 28 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 38 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 38 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END)
, +0) AS FORECAST_CAPACITY
, MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 11 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 21 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 31 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 31 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) AS FORECAST_HOURS
, MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 12 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 22 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 32 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 32 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) AS FORECAST_WEIGHTED_HOURS
, ROUND(NVL( MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 12 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 22 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 32 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 32 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END)*100/ DECODE( PA_REP_UTIL_GLOB.GETU1SHOWPRCTGBY
, 'CAPACITY'
, DECODE( SIGN( MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 17 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 27 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 37 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 37 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) -MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 18 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 28 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 38 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 38 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END))
, 1
, MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 17 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 27 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 37 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 37 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) -MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 18 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 28 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 38 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 38 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END)
, 1)
, 'TOTAL_WORKED_HOURS'
, DECODE( SIGN( MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 11 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 21 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 31 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 31 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END))
, 1
, MAX(CASE WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'SUB_ORGANIZATIONS'
AND SUMMBAL.AMOUNT_TYPE_ID = 11 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'DIRECT_PEOPLE'
AND SUMMBAL.AMOUNT_TYPE_ID = 21 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = ORGDNORM.PARENT_ORGANIZATION_ID
AND LKUP.LOOKUP_CODE = 'TOTALS'
AND SUMMBAL.AMOUNT_TYPE_ID = 31 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID <> ORGDNORM.PARENT_ORGANIZATION_ID
AND SUMMBAL.AMOUNT_TYPE_ID = 31 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END)
, 1) )
, -9999)
, 0) AS FORECAST_UTILIZATION
, MAX(SUMMBAL.PERIOD_SET_NAME) AS PERIOD_SET_NAME
, MAX(SUMMBAL.PERIOD_YEAR) AS PERIOD_YEAR
, MAX(SUMMBAL.QUARTER_OR_MONTH_NUMBER) AS PERIOD_QUARTER
, SUMMBAL.PERIOD_NAME AS PERIOD_NAME
, MAX(SUMMBAL.PERIOD_NUM) AS PERIOD_NUM
FROM PA_SUMM_BALANCES SUMMBAL
, PA_OBJECTS PAOBJ
, PA_LOOKUPS LKUP
, PA_ORG_HIERARCHY_DENORM ORGDNORM
, HR_ALL_ORGANIZATION_UNITS_TL HRORGTL
, HR_ORG_UNITS_NO_JOIN HRORGNJ
WHERE LKUP.LOOKUP_TYPE = 'PA_OASCREEN_CHARS'
AND HRORGNJ.ORGANIZATION_ID(+) = PAOBJ.EXPENDITURE_ORGANIZATION_ID
AND HRORGNJ.ORGANIZATION_ID = HRORGTL.ORGANIZATION_ID(+)
AND HRORGTL.LANGUAGE = USERENV('LANG')
AND ORGDNORM.CHILD_ORGANIZATION_ID = PAOBJ.EXPENDITURE_ORGANIZATION_ID
AND ORGDNORM.ORG_HIERARCHY_VERSION_ID = PA_REP_UTIL_GLOB.GETORGSTRUCTUREVERSIONID
AND ORGDNORM.PA_ORG_USE_TYPE = 'REPORTING'
AND NVL(ORGDNORM.ORG_ID
, -99) = PA_REP_UTIL_GLOB.GETORGID
AND ORGDNORM.PARENT_LEVEL - ORGDNORM.CHILD_LEVEL <= 1
AND SUMMBAL.OBJECT_ID = PAOBJ.OBJECT_ID
AND SUMMBAL.VERSION_ID = -1
AND SUMMBAL.PERIOD_TYPE = PA_REP_UTIL_GLOB.GETPERIODTYPEPA
AND SUMMBAL.PERIOD_SET_NAME = PA_REP_UTIL_GLOB.GETPERIODSETNAME
AND SUMMBAL.AMOUNT_TYPE_ID IN ( 11
, 12
, 17
, 18
, 21
, 22
, 27
, 28
, 31
, 32
, 37
, 38 )
AND SUMMBAL.OBJECT_TYPE_CODE = PA_REP_UTIL_GLOB.GETOBJECTTYPEORG
AND PAOBJ.OBJECT_TYPE_CODE = PA_REP_UTIL_GLOB.GETOBJECTTYPEORG
AND PAOBJ.BALANCE_TYPE_CODE IN (PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
, PA_REP_UTIL_GLOB.GETBALTYPEFORECAST)
AND PAOBJ.EXPENDITURE_ORG_ID = PA_REP_UTIL_GLOB.GETORGID
AND PAOBJ.PROJECT_ORG_ID = -1
AND PAOBJ.EXPENDITURE_ORGANIZATION_ID = DECODE(LKUP.LOOKUP_CODE
, 'SUB_ORGANIZATIONS'
, ORGDNORM.PARENT_ORGANIZATION_ID
, 'DIRECT_PEOPLE'
, ORGDNORM.PARENT_ORGANIZATION_ID
, 'TOTALS'
, ORGDNORM.CHILD_ORGANIZATION_ID
, ORGDNORM.CHILD_ORGANIZATION_ID )
AND PAOBJ.PROJECT_ORGANIZATION_ID = -1
AND PAOBJ.PROJECT_ID = -1
AND PAOBJ.TASK_ID = -1
AND PAOBJ.PERSON_ID = -1
AND PAOBJ.ASSIGNMENT_ID = -1
AND PAOBJ.WORK_TYPE_ID = -1
AND PAOBJ.ORG_UTIL_CATEGORY_ID = -1
AND PAOBJ.RES_UTIL_CATEGORY_ID = -1
AND ORGDNORM.PARENT_ORGANIZATION_ID = PA_REP_UTIL_GLOB.GETU1ORGANIZATIONID
AND SUMMBAL.PERIOD_NAME = PA_REP_UTIL_GLOB.GETU1PERIODNAME GROUP BY ORGDNORM.PARENT_ORGANIZATION_ID
, PAOBJ.EXPENDITURE_ORGANIZATION_ID
, DECODE(PAOBJ.EXPENDITURE_ORGANIZATION_ID
, ORGDNORM.PARENT_ORGANIZATION_ID
, LKUP.MEANING
, NULL)
, DECODE(PAOBJ.EXPENDITURE_ORGANIZATION_ID
, ORGDNORM.PARENT_ORGANIZATION_ID
, LKUP.LOOKUP_CODE
, NULL)
, SUMMBAL.PERIOD_NAME