Product: | PJI - Project Intelligence(Obsolete) |
---|---|
Description: | Discoverer view of organization job utilization data. |
Implementation/DBA Data: | Not implemented in this database |
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.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.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.QUARTER_NAME
, TIME.YEAR
, TIME.PERIOD_START_DATE
, TIME.PERIOD_END_DATE
, TIME.QUARTER_START_DATE
, TIME.QUARTER_END_DATE
, TIME.YEAR_START_DATE
, TIME.YEAR_END_DATE
, FACT.PERIOD_TYPE_ID
, FACT.CALENDAR_TYPE
, FACT.TIME_ID
, FACT.JOB_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.QUARTER_ID
, TIME.YEAR_ID
, TIME.CALENDAR_ID
, TO_NUMBER(TO_CHAR(FUNC1.LAST_EXTR_DATE_A
, 'J'))
, FUNC2.ORG_UTIL_CALC_METHOD
, FUNC3.RES_UTIL_CALC_METHOD
FROM PJI_RM_JOB_F_MV 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_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
FROM DUAL) FUNC1
, (SELECT FND_PROFILE.VALUE('PA_ORG_UTIL_DEF_CALC_METHOD') ORG_UTIL_CALC_METHOD
FROM DUAL) FUNC2
, (SELECT FND_PROFILE.VALUE('PA_RES_UTIL_DEF_CALC_METHOD') RES_UTIL_CALC_METHOD
FROM DUAL) FUNC3
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.PERIOD_ID
AND FACT.PERIOD_TYPE_ID <> 1