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.PJI_DIS_MGRWT_V

Source

View Text - Preformatted


SELECT fact.CAPACITY_HRS, fact.TOTAL_HRS, fact.CONF_HRS, fact.RED_CAP_HRS_A, fact.RED_CAP_HRS_S, fact.TOTAL_HRS_A, fact.BILL_HRS_A, fact.CONF_HRS_S, fact.PROV_HRS_S, fact.CONF_OVERCOM_HRS_S, fact.PROV_OVERCOM_HRS_S, TIME.PERIOD_NAME, TIME.QTR_NAME, TIME.YEAR_NAME, TIME.PERIOD_START_DATE, TIME.PERIOD_END_DATE, TIME.QTR_START_DATE, TIME.QTR_END_DATE, TIME.YEAR_START_DATE, TIME.YEAR_END_DATE, fact.PERIOD_TYPE_ID, fact.CALENDAR_TYPE, fact.TIME_ID, fact.JOB_ID, fact.WORK_TYPE_ID, fact.EXPENDITURE_ORGANIZATION_ID, fact.EXPENDITURE_ORG_ID, TIME.PERIOD_ID, TIME.PERIOD_TYPE, TIME.QTR_ID, TIME.YEAR_ID, TIME.CALENDAR_ID, mgr.SUP_ABSOLUTE_LEVEL MGR_ABSOLUTE_LEVEL, mgr.SUB_ABSOLUTE_LEVEL PERSON_ABSOLUTE_LEVEL, mgr.SUB_RELATIVE_LEVEL PERSON_RELATIVE_LEVEL, mgr.SUP_PERSON_ID MANAGER_ID, mgr.SUB_PERSON_ID PERSON_ID, mgr.SUP_ASSIGNMENT_ID, mgr.SUB_ASSIGNMENT_ID, TO_NUMBER(TO_CHAR(func.last_extr_date_a,'J')), func.org_util_calc_method, func.res_util_calc_method, pertype.system_person_type person_type FROM ( SELECT SUM(CAPACITY_HRS) CAPACITY_HRS, SUM(TOTAL_HRS) TOTAL_HRS, SUM(CONF_HRS) CONF_HRS, SUM(RED_CAP_HRS_A) RED_CAP_HRS_A, SUM(RED_CAP_HRS_S) RED_CAP_HRS_S, SUM(TOTAL_HRS_A) TOTAL_HRS_A, SUM(BILL_HRS_A) BILL_HRS_A, SUM(CONF_HRS_S) CONF_HRS_S, SUM(PROV_HRS_S) PROV_HRS_S, SUM(CONF_OVERCOM_HRS_S) CONF_OVERCOM_HRS_S, SUM(PROV_OVERCOM_HRS_S) PROV_OVERCOM_HRS_S, PERIOD_TYPE_ID, CALENDAR_TYPE, TIME_ID, PERSON_ID, JOB_ID, WORK_TYPE_ID, EXPENDITURE_ORG_ID, EXPENDITURE_ORGANIZATION_ID FROM ( SELECT 0 CAPACITY_HRS, 0 TOTAL_HRS, 0 CONF_HRS, 0 RED_CAP_HRS_A, 0 RED_CAP_HRS_S, SUM(TOTAL_HRS_A) TOTAL_HRS_A, SUM(BILL_HRS_A) BILL_HRS_A, SUM(CONF_HRS_S) CONF_HRS_S, SUM(PROV_HRS_S) PROV_HRS_S, SUM(CONF_OVERCOM_HRS_S) CONF_OVERCOM_HRS_S, SUM(PROV_OVERCOM_HRS_S) PROV_OVERCOM_HRS_S, PERIOD_TYPE_ID, CALENDAR_TYPE, TIME_ID, PERSON_ID, JOB_ID, WORK_TYPE_ID, EXPENDITURE_ORG_ID, EXPENDITURE_ORGANIZATION_ID FROM PJI_RM_RES_WT_F WHERE PERIOD_TYPE_ID = 1 AND CALENDAR_TYPE = 'C' GROUP BY PERIOD_TYPE_ID, CALENDAR_TYPE, TIME_ID, PERSON_ID, JOB_ID, WORK_TYPE_ID, EXPENDITURE_ORG_ID, EXPENDITURE_ORGANIZATION_ID UNION ALL SELECT CAPACITY_HRS, TOTAL_HRS_A TOTAL_HRS, CONF_HRS_S CONF_HRS, REDUCE_CAPACITY_HRS_A RED_CAP_HRS_A, REDUCE_CAPACITY_HRS_S RED_CAP_HRS_S, 0 TOTAL_HRS_A, 0 BILL_HRS_A, 0 CONF_HRS_S, 0 PROV_HRS_S, 0 CONF_OVERCOM_HRS_S, 0 PROV_OVERCOM_HRS_S, PERIOD_TYPE_ID, CALENDAR_TYPE, TIME_ID, PERSON_ID, JOB_ID, WORK_TYPE_ID, EXPENDITURE_ORG_ID, EXPENDITURE_ORGANIZATION_ID FROM PJI_RM_RES_F, PA_WORK_TYPES_B WHERE PERIOD_TYPE_ID = 1 AND CALENDAR_TYPE = 'C' ) GROUP BY PERIOD_TYPE_ID, CALENDAR_TYPE, TIME_ID, PERSON_ID, JOB_ID, WORK_TYPE_ID, EXPENDITURE_ORG_ID, EXPENDITURE_ORGANIZATION_ID ) fact, HRI_CS_SUPH_V mgrsec, HRI_CS_SUPH_V mgr, PJI_TIME_DAY_MV TIME, PJI_ORG_EXTR_INFO info, (SELECT TO_DATE(pji_utils.get_parameter('LAST_FM_EXTR_DATE'),'YYYY/MM/DD') last_extr_date_a , fnd_profile.value('PA_ORG_UTIL_DEF_CALC_METHOD') org_util_calc_method , fnd_profile.value('PA_RES_UTIL_DEF_CALC_METHOD') res_util_calc_method FROM dual) func, PER_PERSON_TYPES pertype, PER_PERSON_TYPE_USAGES_F typeusage WHERE info.ORG_ID = fact.EXPENDITURE_ORG_ID AND DECODE(TIME.PERIOD_TYPE, 'FII_TIME_CAL_PERIOD', info.GL_CALENDAR_ID, 'FII_TIME_PA_PERIOD', info.PA_CALENDAR_ID, -1) = TIME.calendar_id AND fact.PERSON_ID = mgr.SUB_PERSON_ID AND fact.TIME_ID BETWEEN TO_NUMBER(TO_CHAR(mgr.EFFECTIVE_START_DATE, 'J')) AND TO_NUMBER(TO_CHAR(mgr.EFFECTIVE_END_DATE, 'J')) AND fact.TIME_ID = TIME.REPORT_DATE_JULIAN AND mgr.PRIMARY_HIERARCHY_FLAG_CODE = 'Y' AND mgr.SUP_INVALID_FLAG_CODE = 'N' AND mgr.SUB_INVALID_FLAG_CODE = 'N' AND fact.PERSON_ID = mgrsec.SUB_PERSON_ID AND fact.TIME_ID BETWEEN TO_NUMBER(TO_CHAR(mgrsec.EFFECTIVE_START_DATE, 'J')) AND TO_NUMBER(TO_CHAR(mgrsec.EFFECTIVE_END_DATE, 'J')) AND mgrsec.PRIMARY_HIERARCHY_FLAG_CODE = 'Y' AND mgrsec.SUP_INVALID_FLAG_CODE = 'N' AND mgrsec.SUB_INVALID_FLAG_CODE = 'N' AND mgrsec.SUP_PERSON_ID = fnd_global.EMPLOYEE_ID AND pertype.PERSON_TYPE_ID = typeusage.PERSON_TYPE_ID AND typeusage.PERSON_ID = mgr.SUB_PERSON_ID AND fact.TIME_ID BETWEEN TO_NUMBER(TO_CHAR(typeusage.EFFECTIVE_START_DATE, 'J')) AND TO_NUMBER(TO_CHAR(typeusage.EFFECTIVE_END_DATE, 'J'))

View Text - HTML Formatted

SELECT FACT.CAPACITY_HRS
, FACT.TOTAL_HRS
, FACT.CONF_HRS
, FACT.RED_CAP_HRS_A
, FACT.RED_CAP_HRS_S
, FACT.TOTAL_HRS_A
, FACT.BILL_HRS_A
, FACT.CONF_HRS_S
, FACT.PROV_HRS_S
, FACT.CONF_OVERCOM_HRS_S
, FACT.PROV_OVERCOM_HRS_S
, TIME.PERIOD_NAME
, TIME.QTR_NAME
, TIME.YEAR_NAME
, TIME.PERIOD_START_DATE
, TIME.PERIOD_END_DATE
, TIME.QTR_START_DATE
, TIME.QTR_END_DATE
, TIME.YEAR_START_DATE
, TIME.YEAR_END_DATE
, FACT.PERIOD_TYPE_ID
, FACT.CALENDAR_TYPE
, FACT.TIME_ID
, FACT.JOB_ID
, FACT.WORK_TYPE_ID
, FACT.EXPENDITURE_ORGANIZATION_ID
, FACT.EXPENDITURE_ORG_ID
, TIME.PERIOD_ID
, TIME.PERIOD_TYPE
, TIME.QTR_ID
, TIME.YEAR_ID
, TIME.CALENDAR_ID
, MGR.SUP_ABSOLUTE_LEVEL MGR_ABSOLUTE_LEVEL
, MGR.SUB_ABSOLUTE_LEVEL PERSON_ABSOLUTE_LEVEL
, MGR.SUB_RELATIVE_LEVEL PERSON_RELATIVE_LEVEL
, MGR.SUP_PERSON_ID MANAGER_ID
, MGR.SUB_PERSON_ID PERSON_ID
, MGR.SUP_ASSIGNMENT_ID
, MGR.SUB_ASSIGNMENT_ID
, TO_NUMBER(TO_CHAR(FUNC.LAST_EXTR_DATE_A
, 'J'))
, FUNC.ORG_UTIL_CALC_METHOD
, FUNC.RES_UTIL_CALC_METHOD
, PERTYPE.SYSTEM_PERSON_TYPE PERSON_TYPE
FROM ( SELECT SUM(CAPACITY_HRS) CAPACITY_HRS
, SUM(TOTAL_HRS) TOTAL_HRS
, SUM(CONF_HRS) CONF_HRS
, SUM(RED_CAP_HRS_A) RED_CAP_HRS_A
, SUM(RED_CAP_HRS_S) RED_CAP_HRS_S
, SUM(TOTAL_HRS_A) TOTAL_HRS_A
, SUM(BILL_HRS_A) BILL_HRS_A
, SUM(CONF_HRS_S) CONF_HRS_S
, SUM(PROV_HRS_S) PROV_HRS_S
, SUM(CONF_OVERCOM_HRS_S) CONF_OVERCOM_HRS_S
, SUM(PROV_OVERCOM_HRS_S) PROV_OVERCOM_HRS_S
, PERIOD_TYPE_ID
, CALENDAR_TYPE
, TIME_ID
, PERSON_ID
, JOB_ID
, WORK_TYPE_ID
, EXPENDITURE_ORG_ID
, EXPENDITURE_ORGANIZATION_ID
FROM ( SELECT 0 CAPACITY_HRS
, 0 TOTAL_HRS
, 0 CONF_HRS
, 0 RED_CAP_HRS_A
, 0 RED_CAP_HRS_S
, SUM(TOTAL_HRS_A) TOTAL_HRS_A
, SUM(BILL_HRS_A) BILL_HRS_A
, SUM(CONF_HRS_S) CONF_HRS_S
, SUM(PROV_HRS_S) PROV_HRS_S
, SUM(CONF_OVERCOM_HRS_S) CONF_OVERCOM_HRS_S
, SUM(PROV_OVERCOM_HRS_S) PROV_OVERCOM_HRS_S
, PERIOD_TYPE_ID
, CALENDAR_TYPE
, TIME_ID
, PERSON_ID
, JOB_ID
, WORK_TYPE_ID
, EXPENDITURE_ORG_ID
, EXPENDITURE_ORGANIZATION_ID
FROM PJI_RM_RES_WT_F
WHERE PERIOD_TYPE_ID = 1
AND CALENDAR_TYPE = 'C' GROUP BY PERIOD_TYPE_ID
, CALENDAR_TYPE
, TIME_ID
, PERSON_ID
, JOB_ID
, WORK_TYPE_ID
, EXPENDITURE_ORG_ID
, EXPENDITURE_ORGANIZATION_ID UNION ALL SELECT CAPACITY_HRS
, TOTAL_HRS_A TOTAL_HRS
, CONF_HRS_S CONF_HRS
, REDUCE_CAPACITY_HRS_A RED_CAP_HRS_A
, REDUCE_CAPACITY_HRS_S RED_CAP_HRS_S
, 0 TOTAL_HRS_A
, 0 BILL_HRS_A
, 0 CONF_HRS_S
, 0 PROV_HRS_S
, 0 CONF_OVERCOM_HRS_S
, 0 PROV_OVERCOM_HRS_S
, PERIOD_TYPE_ID
, CALENDAR_TYPE
, TIME_ID
, PERSON_ID
, JOB_ID
, WORK_TYPE_ID
, EXPENDITURE_ORG_ID
, EXPENDITURE_ORGANIZATION_ID
FROM PJI_RM_RES_F
, PA_WORK_TYPES_B
WHERE PERIOD_TYPE_ID = 1
AND CALENDAR_TYPE = 'C' ) GROUP BY PERIOD_TYPE_ID
, CALENDAR_TYPE
, TIME_ID
, PERSON_ID
, JOB_ID
, WORK_TYPE_ID
, EXPENDITURE_ORG_ID
, EXPENDITURE_ORGANIZATION_ID ) FACT
, HRI_CS_SUPH_V MGRSEC
, HRI_CS_SUPH_V MGR
, PJI_TIME_DAY_MV TIME
, PJI_ORG_EXTR_INFO INFO
, (SELECT TO_DATE(PJI_UTILS.GET_PARAMETER('LAST_FM_EXTR_DATE')
, 'YYYY/MM/DD') LAST_EXTR_DATE_A
, FND_PROFILE.VALUE('PA_ORG_UTIL_DEF_CALC_METHOD') ORG_UTIL_CALC_METHOD
, FND_PROFILE.VALUE('PA_RES_UTIL_DEF_CALC_METHOD') RES_UTIL_CALC_METHOD
FROM DUAL) FUNC
, PER_PERSON_TYPES PERTYPE
, PER_PERSON_TYPE_USAGES_F TYPEUSAGE
WHERE INFO.ORG_ID = FACT.EXPENDITURE_ORG_ID
AND DECODE(TIME.PERIOD_TYPE
, 'FII_TIME_CAL_PERIOD'
, INFO.GL_CALENDAR_ID
, 'FII_TIME_PA_PERIOD'
, INFO.PA_CALENDAR_ID
, -1) = TIME.CALENDAR_ID
AND FACT.PERSON_ID = MGR.SUB_PERSON_ID
AND FACT.TIME_ID BETWEEN TO_NUMBER(TO_CHAR(MGR.EFFECTIVE_START_DATE
, 'J'))
AND TO_NUMBER(TO_CHAR(MGR.EFFECTIVE_END_DATE
, 'J'))
AND FACT.TIME_ID = TIME.REPORT_DATE_JULIAN
AND MGR.PRIMARY_HIERARCHY_FLAG_CODE = 'Y'
AND MGR.SUP_INVALID_FLAG_CODE = 'N'
AND MGR.SUB_INVALID_FLAG_CODE = 'N'
AND FACT.PERSON_ID = MGRSEC.SUB_PERSON_ID
AND FACT.TIME_ID BETWEEN TO_NUMBER(TO_CHAR(MGRSEC.EFFECTIVE_START_DATE
, 'J'))
AND TO_NUMBER(TO_CHAR(MGRSEC.EFFECTIVE_END_DATE
, 'J'))
AND MGRSEC.PRIMARY_HIERARCHY_FLAG_CODE = 'Y'
AND MGRSEC.SUP_INVALID_FLAG_CODE = 'N'
AND MGRSEC.SUB_INVALID_FLAG_CODE = 'N'
AND MGRSEC.SUP_PERSON_ID = FND_GLOBAL.EMPLOYEE_ID
AND PERTYPE.PERSON_TYPE_ID = TYPEUSAGE.PERSON_TYPE_ID
AND TYPEUSAGE.PERSON_ID = MGR.SUB_PERSON_ID
AND FACT.TIME_ID BETWEEN TO_NUMBER(TO_CHAR(TYPEUSAGE.EFFECTIVE_START_DATE
, 'J'))
AND TO_NUMBER(TO_CHAR(TYPEUSAGE.EFFECTIVE_END_DATE
, 'J'))