VIEW: APPS.PJI_DIS_RESWT_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.PERSON_ID, fact.JOB_ID, fact.WORK_TYPE_ID, org.ORGANIZATION_ID, org.SUB_ORGANIZATION_ID, org.ORGANIZATION_LEVEL, org.SUB_ORGANIZATION_LEVEL, hou1.name ORGANIZATION_NAME, hou2.name SUB_ORGANIZATION_NAME, fact.EXPENDITURE_ORG_ID, TIME.PERIOD_ID, TIME.PERIOD_TYPE, TIME.QTR_ID, TIME.YEAR_ID, TIME.CALENDAR_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, PJI_PMV_ORG_DIM_TMP ou, PJI_PMV_ORGZ_DIM_TMP orgz, PJI_ORG_DENORM org, HR_ALL_ORGANIZATION_UNITS_TL hou1, HR_ALL_ORGANIZATION_UNITS_TL hou2, 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 ou.id = fact.EXPENDITURE_ORG_ID AND orgz.id = fact.EXPENDITURE_ORGANIZATION_ID AND 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.EXPENDITURE_ORGANIZATION_ID = org.SUB_ORGANIZATION_ID AND org.ORGANIZATION_ID = hou1.ORGANIZATION_ID AND org.sub_organization_id = hou2.ORGANIZATION_ID AND hou1.LANGUAGE = USERENV('LANG') AND hou2.LANGUAGE = USERENV('LANG') AND fact.TIME_ID = TIME.REPORT_DATE_JULIAN AND fact.PERIOD_TYPE_ID = 1 AND fact.CALENDAR_TYPE = 'C' AND pertype.PERSON_TYPE_ID = typeusage.PERSON_TYPE_ID AND typeusage.PERSON_ID = fact.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.PERSON_ID
, FACT.JOB_ID
, FACT.WORK_TYPE_ID
, ORG.ORGANIZATION_ID
, ORG.SUB_ORGANIZATION_ID
, ORG.ORGANIZATION_LEVEL
, ORG.SUB_ORGANIZATION_LEVEL
, HOU1.NAME ORGANIZATION_NAME
, HOU2.NAME SUB_ORGANIZATION_NAME
, FACT.EXPENDITURE_ORG_ID
, TIME.PERIOD_ID
, TIME.PERIOD_TYPE
, TIME.QTR_ID
, TIME.YEAR_ID
, TIME.CALENDAR_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
, PJI_PMV_ORG_DIM_TMP OU
, PJI_PMV_ORGZ_DIM_TMP ORGZ
, PJI_ORG_DENORM ORG
, HR_ALL_ORGANIZATION_UNITS_TL HOU1
, HR_ALL_ORGANIZATION_UNITS_TL HOU2
, 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 OU.ID = FACT.EXPENDITURE_ORG_ID
AND ORGZ.ID = FACT.EXPENDITURE_ORGANIZATION_ID
AND 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.EXPENDITURE_ORGANIZATION_ID = ORG.SUB_ORGANIZATION_ID
AND ORG.ORGANIZATION_ID = HOU1.ORGANIZATION_ID
AND ORG.SUB_ORGANIZATION_ID = HOU2.ORGANIZATION_ID
AND HOU1.LANGUAGE = USERENV('LANG')
AND HOU2.LANGUAGE = USERENV('LANG')
AND FACT.TIME_ID = TIME.REPORT_DATE_JULIAN
AND FACT.PERIOD_TYPE_ID = 1
AND FACT.CALENDAR_TYPE = 'C'
AND PERTYPE.PERSON_TYPE_ID = TYPEUSAGE.PERSON_TYPE_ID
AND TYPEUSAGE.PERSON_ID = FACT.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'))