VIEW: APPS.PA_REP_UTIL_ACT_RUCR_PA_V

Source

View Text - Preformatted

SELECT pautl.name AS UTILIZATION_CATEGORY_NAME , paobj.res_util_category_id AS UTILIZATION_CATEGORY_ID , paobj.person_id AS PERSON_ID /* * Field below is for WORKED_HOURS */ , sum( DECODE(summbal.amount_type_id , 1, NVL(summbal.period_balance,0), +0)) AS WORKED_HOURS /* * Field below is for UTILIZATION */ , ROUND(NVL( sum( DECODE(summbal.amount_type_id , 3, NVL(summbal.period_balance,0) , +0))*100/ PA_REP_UTIL_SCREEN.calculate_capacity( PA_REP_UTIL_GLOB.GetOrgId , PA_REP_UTIL_GLOB.GetBalTypeActuals , max(paobj.person_id) , max(summbal.version_id) , max(summbal.period_type) , max(summbal.period_set_name) , max(summbal.period_name) , max(summbal.global_exp_period_end_date) , 1 , 9 , 10 , PA_REP_UTIL_GLOB.GetUtilCalcMethod) , -9999) /* finished NVL */ , 0) AS UTILIZATION /* finished rounding and concatenation */ , pautl.reporting_order AS REPORTING_ORDER from PA_Summ_Balances summbal , PA_Objects paobj , PA_Util_Categories_V pautl WHERE pautl.util_category_id = paobj.res_util_category_id 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.period_name = PA_REP_UTIL_GLOB.GetU3PeriodName AND summbal.global_exp_period_end_date = PA_REP_UTIL_GLOB.GetDummyDate AND summbal.amount_type_id in ( 1 /* G_RES_HRS_C */ , 3 /* G_RES_WTDHRS_PEOPLE_C */ ) AND summbal.object_type_code = PA_REP_UTIL_GLOB.GetObjectTypeResUcr AND paobj.object_type_code = PA_REP_UTIL_GLOB.GetObjectTypeResUcr 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.balance_type_code = PA_REP_UTIL_GLOB.GetBalTypeActuals group by pautl.reporting_order , paobj.person_id , pautl.name , paobj.res_util_category_id

View Text - HTML Formatted

SELECT PAUTL.NAME AS UTILIZATION_CATEGORY_NAME
, PAOBJ.RES_UTIL_CATEGORY_ID AS UTILIZATION_CATEGORY_ID
, PAOBJ.PERSON_ID AS PERSON_ID /* * FIELD BELOW IS FOR WORKED_HOURS */
, SUM( DECODE(SUMMBAL.AMOUNT_TYPE_ID
, 1
, NVL(SUMMBAL.PERIOD_BALANCE
, 0)
, +0)) AS WORKED_HOURS /* * FIELD BELOW IS FOR UTILIZATION */
, ROUND(NVL( SUM( DECODE(SUMMBAL.AMOUNT_TYPE_ID
, 3
, NVL(SUMMBAL.PERIOD_BALANCE
, 0)
, +0))*100/ PA_REP_UTIL_SCREEN.CALCULATE_CAPACITY( PA_REP_UTIL_GLOB.GETORGID
, PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
, MAX(PAOBJ.PERSON_ID)
, MAX(SUMMBAL.VERSION_ID)
, MAX(SUMMBAL.PERIOD_TYPE)
, MAX(SUMMBAL.PERIOD_SET_NAME)
, MAX(SUMMBAL.PERIOD_NAME)
, MAX(SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE)
, 1
, 9
, 10
, PA_REP_UTIL_GLOB.GETUTILCALCMETHOD)
, -9999) /* FINISHED NVL */
, 0) AS UTILIZATION /* FINISHED ROUNDING
AND CONCATENATION */
, PAUTL.REPORTING_ORDER AS REPORTING_ORDER
FROM PA_SUMM_BALANCES SUMMBAL
, PA_OBJECTS PAOBJ
, PA_UTIL_CATEGORIES_V PAUTL
WHERE PAUTL.UTIL_CATEGORY_ID = PAOBJ.RES_UTIL_CATEGORY_ID
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.PERIOD_NAME = PA_REP_UTIL_GLOB.GETU3PERIODNAME
AND SUMMBAL.GLOBAL_EXP_PERIOD_END_DATE = PA_REP_UTIL_GLOB.GETDUMMYDATE
AND SUMMBAL.AMOUNT_TYPE_ID IN ( 1 /* G_RES_HRS_C */
, 3 /* G_RES_WTDHRS_PEOPLE_C */ )
AND SUMMBAL.OBJECT_TYPE_CODE = PA_REP_UTIL_GLOB.GETOBJECTTYPERESUCR
AND PAOBJ.OBJECT_TYPE_CODE = PA_REP_UTIL_GLOB.GETOBJECTTYPERESUCR
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.BALANCE_TYPE_CODE = PA_REP_UTIL_GLOB.GETBALTYPEACTUALS GROUP BY PAUTL.REPORTING_ORDER
, PAOBJ.PERSON_ID
, PAUTL.NAME
, PAOBJ.RES_UTIL_CATEGORY_ID