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_RESVW_PA_V

Source

View Text - Preformatted


SELECT paobj.expenditure_organization_id AS expenditure_organization_id , hrorgtl.name AS expenditure_organization_name , paobj.person_id AS person_id , resdnorm.resource_name AS person_name , resdnorm.manager_id AS manager_id , resdnorm.manager_name AS manager_name , summbal.period_name AS period_name , summbal.period_num AS period_num , glprd.start_date AS period_start_date , glprd.end_date AS period_end_date , summbal.quarter_or_month_number AS period_quarter , summbal.period_year AS period_year , lkupB.meaning AS balance_type_name , paobj.balance_type_code AS balance_type_code , max(case when summbal.amount_type_id = 1 then NVL(summbal.period_balance,0) else 0 end) AS total_hours , DECODE( sign( max(case when summbal.amount_type_id = 9 then NVL(summbal.period_balance,0) else 0 end) - max(case when summbal.amount_type_id = 10 then NVL(summbal.period_balance,0) else 0 end)) , 1, max(case when summbal.amount_type_id = 9 then NVL(summbal.period_balance,0) else 0 end) - max(case when summbal.amount_type_id = 10 then NVL(summbal.period_balance,0) else 0 end) , +0) AS capacity from PA_SUMM_BALANCES summbal , pa_lookups lkupB , gl_periods glprd , pa_resources_denorm resdnorm , PA_Objects paobj , hr_all_organization_units_tl hrorgtl , hr_org_units_no_join hrorgnj WHERE lkupB.lookup_type = 'PA_BALANCE_TYPES' AND lkupB.lookup_code in (PA_REP_UTIL_GLOB.GetBalTypeActuals,PA_REP_UTIL_GLOB.GetBalTypeForecast) AND hrorgnj.organization_id(+) = paobj.expenditure_organization_id AND hrorgnj.organization_id = hrorgtl.organization_id(+) AND decode(hrorgtl.organization_id,null,'1',hrorgtl.language) = decode(hrorgtl.organization_id,null,'1',userenv('LANG')) AND glprd.period_set_name = summbal.period_set_name AND glprd.period_name = summbal.period_name AND glprd.start_date between resdnorm.resource_effective_start_date and NVL(resdnorm.resource_effective_end_date,glprd.start_date) AND summbal.period_type = PA_REP_UTIL_GLOB.GetPeriodTypePa AND summbal.object_type_code = PA_REP_UTIL_GLOB.GetObjectTypeRes AND summbal.amount_type_id in ( 1 /* G_RES_HRS_C */ , 9 /* G_RES_CAP_C */ , 10 /* G_RES_REDUCEDCAP_C */ ) AND summbal.period_set_name = PA_REP_UTIL_GLOB.GetPeriodSetName AND summbal.version_id = -1 AND paobj.object_id = summbal.object_id AND paobj.object_type_code = summbal.object_type_code AND paobj.balance_type_code = lkupB.lookup_code AND paobj.project_org_id = -1 AND paobj.project_organization_id = -1 AND paobj.project_id = -1 AND paobj.task_id = -1 AND NVL(paobj.expenditure_org_id , NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1) ,' ',NULL ,SUBSTRB(USERENV('CLIENT_INFO'),1,10))), -99) ) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1) ,' ',NULL ,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99) AND paobj.person_id = resdnorm.person_id AND paobj.work_type_id = -1 AND paobj.org_util_category_id = -1 AND paobj.res_util_category_id = -1 group by paobj.expenditure_organization_id , hrorgtl.name , paobj.person_id , resdnorm.resource_name , resdnorm.manager_id , resdnorm.manager_name , summbal.period_name , summbal.period_num , glprd.start_date , glprd.end_date , summbal.quarter_or_month_number , summbal.period_year , lkupB.meaning , paobj.balance_type_code

View Text - HTML Formatted

SELECT PAOBJ.EXPENDITURE_ORGANIZATION_ID AS EXPENDITURE_ORGANIZATION_ID
, HRORGTL.NAME AS EXPENDITURE_ORGANIZATION_NAME
, PAOBJ.PERSON_ID AS PERSON_ID
, RESDNORM.RESOURCE_NAME AS PERSON_NAME
, RESDNORM.MANAGER_ID AS MANAGER_ID
, RESDNORM.MANAGER_NAME AS MANAGER_NAME
, SUMMBAL.PERIOD_NAME AS PERIOD_NAME
, SUMMBAL.PERIOD_NUM AS PERIOD_NUM
, GLPRD.START_DATE AS PERIOD_START_DATE
, GLPRD.END_DATE AS PERIOD_END_DATE
, SUMMBAL.QUARTER_OR_MONTH_NUMBER AS PERIOD_QUARTER
, SUMMBAL.PERIOD_YEAR AS PERIOD_YEAR
, LKUPB.MEANING AS BALANCE_TYPE_NAME
, PAOBJ.BALANCE_TYPE_CODE AS BALANCE_TYPE_CODE
, MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 1 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) AS TOTAL_HOURS
, DECODE( SIGN( MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) - MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END))
, 1
, MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 9 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) - MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 10 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END)
, +0) AS CAPACITY
FROM PA_SUMM_BALANCES SUMMBAL
, PA_LOOKUPS LKUPB
, GL_PERIODS GLPRD
, PA_RESOURCES_DENORM RESDNORM
, PA_OBJECTS PAOBJ
, HR_ALL_ORGANIZATION_UNITS_TL HRORGTL
, HR_ORG_UNITS_NO_JOIN HRORGNJ
WHERE LKUPB.LOOKUP_TYPE = 'PA_BALANCE_TYPES'
AND LKUPB.LOOKUP_CODE IN (PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
, PA_REP_UTIL_GLOB.GETBALTYPEFORECAST)
AND HRORGNJ.ORGANIZATION_ID(+) = PAOBJ.EXPENDITURE_ORGANIZATION_ID
AND HRORGNJ.ORGANIZATION_ID = HRORGTL.ORGANIZATION_ID(+)
AND DECODE(HRORGTL.ORGANIZATION_ID
, NULL
, '1'
, HRORGTL.LANGUAGE) = DECODE(HRORGTL.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND GLPRD.PERIOD_SET_NAME = SUMMBAL.PERIOD_SET_NAME
AND GLPRD.PERIOD_NAME = SUMMBAL.PERIOD_NAME
AND GLPRD.START_DATE BETWEEN RESDNORM.RESOURCE_EFFECTIVE_START_DATE
AND NVL(RESDNORM.RESOURCE_EFFECTIVE_END_DATE
, GLPRD.START_DATE)
AND SUMMBAL.PERIOD_TYPE = PA_REP_UTIL_GLOB.GETPERIODTYPEPA
AND SUMMBAL.OBJECT_TYPE_CODE = PA_REP_UTIL_GLOB.GETOBJECTTYPERES
AND SUMMBAL.AMOUNT_TYPE_ID IN ( 1 /* G_RES_HRS_C */
, 9 /* G_RES_CAP_C */
, 10 /* G_RES_REDUCEDCAP_C */ )
AND SUMMBAL.PERIOD_SET_NAME = PA_REP_UTIL_GLOB.GETPERIODSETNAME
AND SUMMBAL.VERSION_ID = -1
AND PAOBJ.OBJECT_ID = SUMMBAL.OBJECT_ID
AND PAOBJ.OBJECT_TYPE_CODE = SUMMBAL.OBJECT_TYPE_CODE
AND PAOBJ.BALANCE_TYPE_CODE = LKUPB.LOOKUP_CODE
AND PAOBJ.PROJECT_ORG_ID = -1
AND PAOBJ.PROJECT_ORGANIZATION_ID = -1
AND PAOBJ.PROJECT_ID = -1
AND PAOBJ.TASK_ID = -1
AND NVL(PAOBJ.EXPENDITURE_ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99) ) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)
AND PAOBJ.PERSON_ID = RESDNORM.PERSON_ID
AND PAOBJ.WORK_TYPE_ID = -1
AND PAOBJ.ORG_UTIL_CATEGORY_ID = -1
AND PAOBJ.RES_UTIL_CATEGORY_ID = -1 GROUP BY PAOBJ.EXPENDITURE_ORGANIZATION_ID
, HRORGTL.NAME
, PAOBJ.PERSON_ID
, RESDNORM.RESOURCE_NAME
, RESDNORM.MANAGER_ID
, RESDNORM.MANAGER_NAME
, SUMMBAL.PERIOD_NAME
, SUMMBAL.PERIOD_NUM
, GLPRD.START_DATE
, GLPRD.END_DATE
, SUMMBAL.QUARTER_OR_MONTH_NUMBER
, SUMMBAL.PERIOD_YEAR
, LKUPB.MEANING
, PAOBJ.BALANCE_TYPE_CODE