FND Design Data [Home] [Help]

View: PJI_DIS_MGRWT_V

Product: PJI - Project Intelligence
Description: Discoverer view of manager work type utilization data.
Implementation/DBA Data: ViewAPPS.PJI_DIS_MGRWT_V
View Text

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'))

Columns

Name
CAPACITY_HRS
RES_TOTAL_HRS
RES_CONF_HRS
RES_REDUCE_CAP_HRS_A
RES_REDUCE_CAP_HRS_S
TOTAL_HRS_A
BILL_HRS_A
CONF_HRS_S
PROV_HRS_S
CONF_OVERCOM_HRS_S
PROV_OVERCOM_HRS_S
PERIOD_NAME
QUARTER_NAME
YEAR
PERIOD_START_DATE
PERIOD_END_DATE
QUARTER_START_DATE
QUARTER_END_DATE
YEAR_START_DATE
YEAR_END_DATE
PERIOD_TYPE_ID
CALENDAR_TYPE
TIME_ID
JOB_ID
WORK_TYPE_ID
ORGANIZATION_ID
EXPENDITURE_ORG_ID
PERIOD_ID
PERIOD_TYPE
QUARTER_ID
YEAR_ID
CALENDAR_ID
MGR_ABSOLUTE_LEVEL
PERSON_ABSOLUTE_LEVEL
PERSON_RELATIVE_LEVEL
MANAGER_ID
PERSON_ID
SUP_ASSIGNMENT_ID
SUB_ASSIGNMENT_ID
LAST_ACT_EXTR_DATE
ORG_UTIL_CALC_METH
RES_UTIL_CALC_METH
PERSON_TYPE