VIEW: APPS.PJI_DIS_MGRRES_V

Source

View Text - Preformatted

SELECT fact.CAPACITY_HRS, fact.TOTAL_HRS_A, fact.MISSING_HRS_A, fact.TOTAL_WTD_ORG_HRS_A, fact.TOTAL_WTD_RES_HRS_A, fact.BILL_HRS_A, fact.BILL_WTD_ORG_HRS_A, fact.BILL_WTD_RES_HRS_A, fact.TRAINING_HRS_A, fact.UNASSIGNED_HRS_A, fact.REDUCIBLE_CAPACITY_HRS_A, fact.REDUCE_CAPACITY_HRS_A, fact.CONF_HRS_S, fact.CONF_WTD_ORG_HRS_S, fact.CONF_WTD_RES_HRS_S, fact.CONF_BILL_HRS_S, fact.CONF_BILL_WTD_ORG_HRS_S, fact.CONF_BILL_WTD_RES_HRS_S, fact.PROV_HRS_S, fact.PROV_WTD_ORG_HRS_S, fact.PROV_WTD_RES_HRS_S, fact.PROV_BILL_HRS_S, fact.PROV_BILL_WTD_ORG_HRS_S, fact.PROV_BILL_WTD_RES_HRS_S, fact.TRAINING_HRS_S, fact.UNASSIGNED_HRS_S, fact.REDUCIBLE_CAPACITY_HRS_S, fact.REDUCE_CAPACITY_HRS_S, fact.CONF_OVERCOM_HRS_S, fact.PROV_OVERCOM_HRS_S, fact.AVAILABLE_HRS_BKT1_S, fact.AVAILABLE_HRS_BKT2_S, fact.AVAILABLE_HRS_BKT3_S, fact.AVAILABLE_HRS_BKT4_S, fact.AVAILABLE_HRS_BKT5_S, fact.AVAILABLE_RES_COUNT_BKT1_S, fact.AVAILABLE_RES_COUNT_BKT2_S, fact.AVAILABLE_RES_COUNT_BKT3_S, fact.AVAILABLE_RES_COUNT_BKT4_S, fact.AVAILABLE_RES_COUNT_BKT5_S, fact.TOTAL_RES_COUNT, 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.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 PJI_RM_RES_F 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_TYPE_USAGES_F typeusage, PER_PERSON_TYPES pertype 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 fact.PERIOD_TYPE_ID = 1 AND fact.CALENDAR_TYPE = 'C' 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_A
, FACT.MISSING_HRS_A
, FACT.TOTAL_WTD_ORG_HRS_A
, FACT.TOTAL_WTD_RES_HRS_A
, FACT.BILL_HRS_A
, FACT.BILL_WTD_ORG_HRS_A
, FACT.BILL_WTD_RES_HRS_A
, FACT.TRAINING_HRS_A
, FACT.UNASSIGNED_HRS_A
, FACT.REDUCIBLE_CAPACITY_HRS_A
, FACT.REDUCE_CAPACITY_HRS_A
, FACT.CONF_HRS_S
, FACT.CONF_WTD_ORG_HRS_S
, FACT.CONF_WTD_RES_HRS_S
, FACT.CONF_BILL_HRS_S
, FACT.CONF_BILL_WTD_ORG_HRS_S
, FACT.CONF_BILL_WTD_RES_HRS_S
, FACT.PROV_HRS_S
, FACT.PROV_WTD_ORG_HRS_S
, FACT.PROV_WTD_RES_HRS_S
, FACT.PROV_BILL_HRS_S
, FACT.PROV_BILL_WTD_ORG_HRS_S
, FACT.PROV_BILL_WTD_RES_HRS_S
, FACT.TRAINING_HRS_S
, FACT.UNASSIGNED_HRS_S
, FACT.REDUCIBLE_CAPACITY_HRS_S
, FACT.REDUCE_CAPACITY_HRS_S
, FACT.CONF_OVERCOM_HRS_S
, FACT.PROV_OVERCOM_HRS_S
, FACT.AVAILABLE_HRS_BKT1_S
, FACT.AVAILABLE_HRS_BKT2_S
, FACT.AVAILABLE_HRS_BKT3_S
, FACT.AVAILABLE_HRS_BKT4_S
, FACT.AVAILABLE_HRS_BKT5_S
, FACT.AVAILABLE_RES_COUNT_BKT1_S
, FACT.AVAILABLE_RES_COUNT_BKT2_S
, FACT.AVAILABLE_RES_COUNT_BKT3_S
, FACT.AVAILABLE_RES_COUNT_BKT4_S
, FACT.AVAILABLE_RES_COUNT_BKT5_S
, FACT.TOTAL_RES_COUNT
, 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.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 PJI_RM_RES_F 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_TYPE_USAGES_F TYPEUSAGE
, PER_PERSON_TYPES PERTYPE
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 FACT.PERIOD_TYPE_ID = 1
AND FACT.CALENDAR_TYPE = 'C'
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'))