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_RWTVW_GL_V

Source

View Text - Preformatted


SELECT paobj.expenditure_organization_id AS expenditure_organization_id , paobj.org_util_category_id AS org_util_category_id , ucatgO.name AS org_util_category_name , ucatgO.reporting_order AS org_reporting_order , paobj.res_util_category_id AS res_util_category_id , ucatgR.name AS res_util_category_name , ucatgR.reporting_order AS res_reporting_order , paobj.work_type_id AS work_type_id , uwkty.name AS work_type_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 , 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 , max(case when summbal.amount_type_id = 4 then NVL(summbal.period_balance,0) else 0 end) AS total_prov_hours , max(case when summbal.amount_type_id = 1 then NVL(summbal.period_balance,0) else 0 end) - max(case when summbal.amount_type_id = 4 then NVL(summbal.period_balance,0) else 0 end) AS total_conf_hours , max(case when summbal.amount_type_id = 3 then NVL(summbal.period_balance,0) else 0 end) AS total_weighted_hours_people , max(case when summbal.amount_type_id = 2 then NVL(summbal.period_balance,0) else 0 end) AS total_weighted_hours_org , max(case when summbal.amount_type_id = 6 then NVL(summbal.period_balance,0) else 0 end) AS prov_weighted_hours_people , max(case when summbal.amount_type_id = 5 then NVL(summbal.period_balance,0) else 0 end) AS prov_weighted_hours_org , max(case when summbal.amount_type_id = 3 then NVL(summbal.period_balance,0) else 0 end) - max(case when summbal.amount_type_id = 6 then NVL(summbal.period_balance,0) else 0 end) AS conf_weighted_hours_people , max(case when summbal.amount_type_id = 2 then NVL(summbal.period_balance,0) else 0 end) - max(case when summbal.amount_type_id = 5 then NVL(summbal.period_balance,0) else 0 end) AS conf_weighted_hours_org from PA_SUMM_BALANCES summbal , gl_periods glprd , PA_Util_Categories_V ucatgO , PA_Util_Categories_V ucatgR , PA_Work_Types_TL uwkty , pa_resources_denorm resdnorm , PA_Objects paobj WHERE uwkty.language = 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.GetPeriodTypeGl AND summbal.object_type_code = PA_REP_UTIL_GLOB.GetObjectTypeResWt AND summbal.amount_type_id in ( 1 /* G_RES_HRS_C */ , 2 /* G_RES_WTDHRS_ORG_C */ , 3 /* G_RES_WTDHRS_PEOPLE_C */ , 4 /* G_RES_PRVHRS_C */ , 5 /* G_RES_PRVWTDHRS_ORG_C */ , 6 /* G_RES_PRVWTDHRS_PEOPLE_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 in (PA_REP_UTIL_GLOB.GetBalTypeActuals,PA_REP_UTIL_GLOB.GetBalTypeForecast) 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 = uwkty.work_type_id AND paobj.org_util_category_id = ucatgO.util_category_id AND paobj.res_util_category_id = ucatgR.util_category_id group by paobj.expenditure_organization_id , paobj.org_util_category_id , ucatgO.name , ucatgO.reporting_order , paobj.res_util_category_id , ucatgR.name , ucatgR.reporting_order , paobj.work_type_id , uwkty.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 , paobj.balance_type_code

View Text - HTML Formatted

SELECT PAOBJ.EXPENDITURE_ORGANIZATION_ID AS EXPENDITURE_ORGANIZATION_ID
, PAOBJ.ORG_UTIL_CATEGORY_ID AS ORG_UTIL_CATEGORY_ID
, UCATGO.NAME AS ORG_UTIL_CATEGORY_NAME
, UCATGO.REPORTING_ORDER AS ORG_REPORTING_ORDER
, PAOBJ.RES_UTIL_CATEGORY_ID AS RES_UTIL_CATEGORY_ID
, UCATGR.NAME AS RES_UTIL_CATEGORY_NAME
, UCATGR.REPORTING_ORDER AS RES_REPORTING_ORDER
, PAOBJ.WORK_TYPE_ID AS WORK_TYPE_ID
, UWKTY.NAME AS WORK_TYPE_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
, 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
, MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 4 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) AS TOTAL_PROV_HOURS
, MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 1 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) - MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 4 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) AS TOTAL_CONF_HOURS
, MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 3 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) AS TOTAL_WEIGHTED_HOURS_PEOPLE
, MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 2 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) AS TOTAL_WEIGHTED_HOURS_ORG
, MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 6 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) AS PROV_WEIGHTED_HOURS_PEOPLE
, MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 5 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) AS PROV_WEIGHTED_HOURS_ORG
, MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 3 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) - MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 6 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) AS CONF_WEIGHTED_HOURS_PEOPLE
, MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 2 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) - MAX(CASE WHEN SUMMBAL.AMOUNT_TYPE_ID = 5 THEN NVL(SUMMBAL.PERIOD_BALANCE
, 0) ELSE 0 END) AS CONF_WEIGHTED_HOURS_ORG
FROM PA_SUMM_BALANCES SUMMBAL
, GL_PERIODS GLPRD
, PA_UTIL_CATEGORIES_V UCATGO
, PA_UTIL_CATEGORIES_V UCATGR
, PA_WORK_TYPES_TL UWKTY
, PA_RESOURCES_DENORM RESDNORM
, PA_OBJECTS PAOBJ
WHERE UWKTY.LANGUAGE = 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.GETPERIODTYPEGL
AND SUMMBAL.OBJECT_TYPE_CODE = PA_REP_UTIL_GLOB.GETOBJECTTYPERESWT
AND SUMMBAL.AMOUNT_TYPE_ID IN ( 1 /* G_RES_HRS_C */
, 2 /* G_RES_WTDHRS_ORG_C */
, 3 /* G_RES_WTDHRS_PEOPLE_C */
, 4 /* G_RES_PRVHRS_C */
, 5 /* G_RES_PRVWTDHRS_ORG_C */
, 6 /* G_RES_PRVWTDHRS_PEOPLE_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 IN (PA_REP_UTIL_GLOB.GETBALTYPEACTUALS
, PA_REP_UTIL_GLOB.GETBALTYPEFORECAST)
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 = UWKTY.WORK_TYPE_ID
AND PAOBJ.ORG_UTIL_CATEGORY_ID = UCATGO.UTIL_CATEGORY_ID
AND PAOBJ.RES_UTIL_CATEGORY_ID = UCATGR.UTIL_CATEGORY_ID GROUP BY PAOBJ.EXPENDITURE_ORGANIZATION_ID
, PAOBJ.ORG_UTIL_CATEGORY_ID
, UCATGO.NAME
, UCATGO.REPORTING_ORDER
, PAOBJ.RES_UTIL_CATEGORY_ID
, UCATGR.NAME
, UCATGR.REPORTING_ORDER
, PAOBJ.WORK_TYPE_ID
, UWKTY.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
, PAOBJ.BALANCE_TYPE_CODE