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_FCT_RSUM_GE_V

Source

View Text - Preformatted


SELECT lkup.meaning AS TITLE_CODE , paobj.person_id AS PERSON_ID /* * Field below is for CAPACITY */ , DECODE( sign( sum(CASE when triple.dumnum = 1 and summbal.global_exp_period_end_date = to_date(PA_REP_UTIL_GLOB.GetU3GeEndDate) and summbal.amount_type_id = 9 then NVL(summbal.period_balance,0) when triple.dumnum = 2 and summbal.quarter_or_month_number = PA_REP_UTIL_GLOB.GetU3QtrOrMonNum and summbal.amount_type_id = 9 then NVL(summbal.period_balance,0) when triple.dumnum = 3 and summbal.period_year = PA_REP_UTIL_GLOB.GetU3YearNum and summbal.amount_type_id = 9 then NVL(summbal.period_balance,0) else +0 end) -sum(CASE when triple.dumnum = 1 and summbal.global_exp_period_end_date = to_date(PA_REP_UTIL_GLOB.GetU3GeEndDate) and summbal.amount_type_id = 10 then NVL(summbal.period_balance,0) when triple.dumnum = 2 and summbal.quarter_or_month_number = PA_REP_UTIL_GLOB.GetU3QtrOrMonNum and summbal.amount_type_id = 10 then NVL(summbal.period_balance,0) when triple.dumnum = 3 and summbal.period_year = PA_REP_UTIL_GLOB.GetU3YearNum and summbal.amount_type_id = 10 then NVL(summbal.period_balance,0) else +0 end)) , 1, sum(CASE when triple.dumnum = 1 and summbal.global_exp_period_end_date = to_date(PA_REP_UTIL_GLOB.GetU3GeEndDate) and summbal.amount_type_id = 9 then NVL(summbal.period_balance,0) when triple.dumnum = 2 and summbal.quarter_or_month_number = PA_REP_UTIL_GLOB.GetU3QtrOrMonNum and summbal.amount_type_id = 9 then NVL(summbal.period_balance,0) when triple.dumnum = 3 and summbal.period_year = PA_REP_UTIL_GLOB.GetU3YearNum and summbal.amount_type_id = 9 then NVL(summbal.period_balance,0) else +0 end) -sum(CASE when triple.dumnum = 1 and summbal.global_exp_period_end_date = to_date(PA_REP_UTIL_GLOB.GetU3GeEndDate) and summbal.amount_type_id = 10 then NVL(summbal.period_balance,0) when triple.dumnum = 2 and summbal.quarter_or_month_number = PA_REP_UTIL_GLOB.GetU3QtrOrMonNum and summbal.amount_type_id = 10 then NVL(summbal.period_balance,0) when triple.dumnum = 3 and summbal.period_year = PA_REP_UTIL_GLOB.GetU3YearNum and summbal.amount_type_id = 10 then NVL(summbal.period_balance,0) else +0 end) , +0) AS CAPACITY /* * Field below is for PROVISIONAL_HOURS */ , sum(CASE when triple.dumnum = 1 and summbal.global_exp_period_end_date = to_date(PA_REP_UTIL_GLOB.GetU3GeEndDate) and summbal.amount_type_id = 4 then NVL(summbal.period_balance,0) when triple.dumnum = 2 and summbal.quarter_or_month_number = PA_REP_UTIL_GLOB.GetU3QtrOrMonNum and summbal.amount_type_id = 4 then NVL(summbal.period_balance,0) when triple.dumnum = 3 and summbal.period_year = PA_REP_UTIL_GLOB.GetU3YearNum and summbal.amount_type_id = 4 then NVL(summbal.period_balance,0) else +0 end) AS PROVISIONAL_HOURS /* * Field below is for CONFIRMED_HOURS */ , sum(CASE when triple.dumnum = 1 and summbal.global_exp_period_end_date = to_date(PA_REP_UTIL_GLOB.GetU3GeEndDate) and summbal.amount_type_id = 1 then NVL(summbal.period_balance,0) when triple.dumnum = 2 and summbal.quarter_or_month_number = PA_REP_UTIL_GLOB.GetU3QtrOrMonNum and summbal.amount_type_id = 1 then NVL(summbal.period_balance,0) when triple.dumnum = 3 and summbal.period_year = PA_REP_UTIL_GLOB.GetU3YearNum and summbal.amount_type_id = 1 then NVL(summbal.period_balance,0) else +0 end) -sum(CASE when triple.dumnum = 1 and summbal.global_exp_period_end_date = to_date(PA_REP_UTIL_GLOB.GetU3GeEndDate) and summbal.amount_type_id = 4 then NVL(summbal.period_balance,0) when triple.dumnum = 2 and summbal.quarter_or_month_number = PA_REP_UTIL_GLOB.GetU3QtrOrMonNum and summbal.amount_type_id = 4 then NVL(summbal.period_balance,0) when triple.dumnum = 3 and summbal.period_year = PA_REP_UTIL_GLOB.GetU3YearNum and summbal.amount_type_id = 4 then NVL(summbal.period_balance,0) else +0 end) AS CONFIRMED_HOURS /* * Field below is for UTILIZATION */ , ROUND(NVL( (sum(CASE when triple.dumnum = 1 and summbal.global_exp_period_end_date = to_date(PA_REP_UTIL_GLOB.GetU3GeEndDate) and summbal.amount_type_id = 3 then NVL(summbal.period_balance,0) when triple.dumnum = 2 and summbal.quarter_or_month_number = PA_REP_UTIL_GLOB.GetU3QtrOrMonNum and summbal.amount_type_id = 3 then NVL(summbal.period_balance,0) when triple.dumnum = 3 and summbal.period_year = PA_REP_UTIL_GLOB.GetU3YearNum and summbal.amount_type_id = 3 then NVL(summbal.period_balance,0) else +0 end) -sum(CASE when triple.dumnum = 1 and summbal.global_exp_period_end_date = to_date(PA_REP_UTIL_GLOB.GetU3GeEndDate) and summbal.amount_type_id = 6 then NVL(summbal.period_balance,0) when triple.dumnum = 2 and summbal.quarter_or_month_number = PA_REP_UTIL_GLOB.GetU3QtrOrMonNum and summbal.amount_type_id = 6 then NVL(summbal.period_balance,0) when triple.dumnum = 3 and summbal.period_year = PA_REP_UTIL_GLOB.GetU3YearNum and summbal.amount_type_id = 6 then NVL(summbal.period_balance,0) else +0 end))*100/ DECODE(PA_REP_UTIL_GLOB.GetUtilCalcMethod, 'CAPACITY', DecodE( sign( sum(CASE when triple.dumnum = 1 and summbal.global_exp_period_end_date = to_date(PA_REP_UTIL_GLOB.GetU3GeEndDate) and summbal.amount_type_id = 9 then NVL(summbal.period_balance,0) when triple.dumnum = 2 and summbal.quarter_or_month_number = PA_REP_UTIL_GLOB.GetU3QtrOrMonNum and summbal.amount_type_id = 9 then NVL(summbal.period_balance,0) when triple.dumnum = 3 and summbal.period_year = PA_REP_UTIL_GLOB.GetU3YearNum and summbal.amount_type_id = 9 then NVL(summbal.period_balance,0) else +0 end) -sum(CASE when triple.dumnum = 1 and summbal.global_exp_period_end_date = to_date(PA_REP_UTIL_GLOB.GetU3GeEndDate) and summbal.amount_type_id = 10 then NVL(summbal.period_balance,0) when triple.dumnum = 2 and summbal.quarter_or_month_number = PA_REP_UTIL_GLOB.GetU3QtrOrMonNum and summbal.amount_type_id = 10 then NVL(summbal.period_balance,0) when triple.dumnum = 3 and summbal.period_year = PA_REP_UTIL_GLOB.GetU3YearNum and summbal.amount_type_id = 10 then NVL(summbal.period_balance,0) else +0 end)) , 1, sum(CASE when triple.dumnum = 1 and summbal.global_exp_period_end_date = to_date(PA_REP_UTIL_GLOB.GetU3GeEndDate) and summbal.amount_type_id = 9 then NVL(summbal.period_balance,0) when triple.dumnum = 2 and summbal.quarter_or_month_number = PA_REP_UTIL_GLOB.GetU3QtrOrMonNum and summbal.amount_type_id = 9 then NVL(summbal.period_balance,0) when triple.dumnum = 3 and summbal.period_year = PA_REP_UTIL_GLOB.GetU3YearNum and summbal.amount_type_id = 9 then NVL(summbal.period_balance,0) else +0 end) -sum(CASE when triple.dumnum = 1 and summbal.global_exp_period_end_date = to_date(PA_REP_UTIL_GLOB.GetU3GeEndDate) and summbal.amount_type_id = 10 then NVL(summbal.period_balance,0) when triple.dumnum = 2 and summbal.quarter_or_month_number = PA_REP_UTIL_GLOB.GetU3QtrOrMonNum and summbal.amount_type_id = 10 then NVL(summbal.period_balance,0) when triple.dumnum = 3 and summbal.period_year = PA_REP_UTIL_GLOB.GetU3YearNum and summbal.amount_type_id = 10 then NVL(summbal.period_balance,0) else +0 end) , 1) , 'TOTAL_WORKED_HOURS' , DecodE( sign( sum(CASE when triple.dumnum = 1 and summbal.global_exp_period_end_date = to_date(PA_REP_UTIL_GLOB.GetU3GeEndDate) and summbal.amount_type_id = 1 then NVL(summbal.period_balance,0) when triple.dumnum = 2 and summbal.quarter_or_month_number = PA_REP_UTIL_GLOB.GetU3QtrOrMonNum and summbal.amount_type_id = 1 then NVL(summbal.period_balance,0) when triple.dumnum = 3 and summbal.period_year = PA_REP_UTIL_GLOB.GetU3YearNum and summbal.amount_type_id = 1 then NVL(summbal.period_balance,0) else +0 end)) , 1, sum(CASE when triple.dumnum = 1 and summbal.global_exp_period_end_date = to_date(PA_REP_UTIL_GLOB.GetU3GeEndDate) and summbal.amount_type_id = 1 then NVL(summbal.period_balance,0) when triple.dumnum = 2 and summbal.quarter_or_month_number = PA_REP_UTIL_GLOB.GetU3QtrOrMonNum and summbal.amount_type_id = 1 then NVL(summbal.period_balance,0) when triple.dumnum = 3 and summbal.period_year = PA_REP_UTIL_GLOB.GetU3YearNum and summbal.amount_type_id = 1 then NVL(summbal.period_balance,0) else +0 end) , 1) ) , -9999) /* finished NVL */ , 0) AS UTILIZATION /* finished rounding and concatenation */ from PA_Summ_Balances summbal , PA_Objects paobj , pa_lookups lkup , (select to_number(1) AS DUMNUM from dual union all select to_number(2) AS DUMNUM from dual union all select to_number(3) AS DUMNUM from dual ) triple WHERE lkup.lookup_type = 'PA_RES_UTIL_PERIOD_PROMPT' AND lkup.lookup_code = DECODE(triple.dumnum , 1, 'SELECTED_PERIOD' , 2, 'SELECTED_MONTH' , 3, 'SELECTED_YEAR' ) AND summbal.object_id = paobj.object_id AND summbal.version_id = -1 AND summbal.period_type = PA_REP_UTIL_GLOB.GetPeriodTypeGe AND summbal.period_set_name = PA_REP_UTIL_GLOB.GetDummy AND summbal.period_year = PA_REP_UTIL_GLOB.GetU3YearNum AND summbal.period_name = PA_REP_UTIL_GLOB.GetDummy AND summbal.amount_type_id in ( 1 /* G_RES_HRS_C */ , 3 /* G_RES_WTDHRS_PEOPLE_C */ , 4 /* G_RES_PRVHRS_C */ , 6 /* G_RES_PRVWTDHRS_PEOPLE_C */ , 9 /* G_RES_CAP_C */ , 10 /* G_RES_REDUCEDCAP_C */ ) AND summbal.object_type_code = PA_REP_UTIL_GLOB.GetObjectTypeRes AND summbal.global_exp_period_end_date <= PA_REP_UTIL_GLOB.GetU3GeEndDate AND paobj.object_type_code = PA_REP_UTIL_GLOB.GetObjectTypeRes AND paobj.expenditure_org_id = PA_REP_UTIL_GLOB.GetOrgId AND paobj.project_org_id = -1 AND paobj.project_organization_id = -1 AND paobj.project_id = -1 AND paobj.task_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 paobj.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeForecast group by paobj.person_id , triple.dumnum , lkup.meaning

View Text - HTML Formatted

SELECT LKUP.MEANING AS TITLE_CODE
, PAOBJ.PERSON_ID AS PERSON_ID /* * FIELD BELOW IS FOR CAPACITY */
, DECODE( SIGN( SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE)
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) -SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE)
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END))
, 1
, SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE)
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) -SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE)
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END)
, +0) AS CAPACITY /* * FIELD BELOW IS FOR PROVISIONAL_HOURS */
, SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE)
AND SUMMBAL.AMOUNT_TYPE_ID = 4 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 4 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 4 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) AS PROVISIONAL_HOURS /* * FIELD BELOW IS FOR CONFIRMED_HOURS */
, SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE)
AND SUMMBAL.AMOUNT_TYPE_ID = 1 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 1 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 1 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) -SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE)
AND SUMMBAL.AMOUNT_TYPE_ID = 4 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 4 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 4 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) AS CONFIRMED_HOURS /* * FIELD BELOW IS FOR UTILIZATION */
, ROUND(NVL( (SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE)
AND SUMMBAL.AMOUNT_TYPE_ID = 3 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 3 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 3 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) -SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE)
AND SUMMBAL.AMOUNT_TYPE_ID = 6 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 6 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 6 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END))*100/ DECODE(PA_REP_UTIL_GLOB.GETUTILCALCMETHOD
, 'CAPACITY'
, DECODE( SIGN( SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE)
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) -SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE)
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END))
, 1
, SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE)
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END) -SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE)
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END)
, 1)
, 'TOTAL_WORKED_HOURS'
, DECODE( SIGN( SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE)
AND SUMMBAL.AMOUNT_TYPE_ID = 1 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 1 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 1 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END))
, 1
, SUM(CASE WHEN TRIPLE.DUMNUM = 1
AND SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE = TO_DATE(PA_REP_UTIL_GLOB.GETU3GEENDDATE)
AND SUMMBAL.AMOUNT_TYPE_ID = 1 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 2
AND SUMMBAL.QUARTER_OR_MONTH_NUMBER = PA_REP_UTIL_GLOB.GETU3QTRORMONNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 1 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) WHEN TRIPLE.DUMNUM = 3
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.AMOUNT_TYPE_ID = 1 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE +0 END)
, 1) )
, -9999) /* FINISHED NVL */
, 0) AS UTILIZATION /* FINISHED ROUNDING
AND CONCATENATION */
FROM PA_SUMM_BALANCES SUMMBAL
, PA_OBJECTS PAOBJ
, PA_LOOKUPS LKUP
, (SELECT TO_NUMBER(1) AS DUMNUM
FROM DUAL UNION ALL SELECT TO_NUMBER(2) AS DUMNUM
FROM DUAL UNION ALL SELECT TO_NUMBER(3) AS DUMNUM
FROM DUAL ) TRIPLE
WHERE LKUP.LOOKUP_TYPE = 'PA_RES_UTIL_PERIOD_PROMPT'
AND LKUP.LOOKUP_CODE = DECODE(TRIPLE.DUMNUM
, 1
, 'SELECTED_PERIOD'
, 2
, 'SELECTED_MONTH'
, 3
, 'SELECTED_YEAR' )
AND SUMMBAL.OBJECT_ID = PAOBJ.OBJECT_ID
AND SUMMBAL.VERSION_ID = -1
AND SUMMBAL.PERIOD_TYPE = PA_REP_UTIL_GLOB.GETPERIODTYPEGE
AND SUMMBAL.PERIOD_SET_NAME = PA_REP_UTIL_GLOB.GETDUMMY
AND SUMMBAL.PERIOD_YEAR = PA_REP_UTIL_GLOB.GETU3YEARNUM
AND SUMMBAL.PERIOD_NAME = PA_REP_UTIL_GLOB.GETDUMMY
AND SUMMBAL.AMOUNT_TYPE_ID IN ( 1 /* G_RES_HRS_C */
, 3 /* G_RES_WTDHRS_PEOPLE_C */
, 4 /* G_RES_PRVHRS_C */
, 6 /* G_RES_PRVWTDHRS_PEOPLE_C */
, 9 /* G_RES_CAP_C */
, 10 /* G_RES_REDUCEDCAP_C */ )
AND SUMMBAL.OBJECT_TYPE_CODE = PA_REP_UTIL_GLOB.GETOBJECTTYPERES
AND SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE <= PA_REP_UTIL_GLOB.GETU3GEENDDATE
AND PAOBJ.OBJECT_TYPE_CODE = PA_REP_UTIL_GLOB.GETOBJECTTYPERES
AND PAOBJ.EXPENDITURE_ORG_ID = PA_REP_UTIL_GLOB.GETORGID
AND PAOBJ.PROJECT_ORG_ID = -1
AND PAOBJ.PROJECT_ORGANIZATION_ID = -1
AND PAOBJ.PROJECT_ID = -1
AND PAOBJ.TASK_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 PAOBJ.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEFORECAST GROUP BY PAOBJ.PERSON_ID
, TRIPLE.DUMNUM
, LKUP.MEANING