View: PA_ASSIGNMENT_PROG_HIST_V

Product:	PA - Projects
Description:
Implementation/DBA Data:	APPS.PA_ASSIGNMENT_PROG_HIST_V

View Text

SELECT DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, PPR.PPL_ACT_COST_TO_DATE_TC
, 'EQUIPMENT'
, PPR.EQPMT_ACT_COST_TO_DATE_TC
, PPR.OTH_ACT_COST_TO_DATE_TC) ACTUAL_COST
, DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, PPR.PPL_ACT_EFFORT_TO_DATE
, 'EQUIPMENT'
, PPR.EQPMT_ACT_EFFORT_TO_DATE
, PPR.OTH_QUANTITY_TO_DATE) ACTUAL_EFFORT
, PPR.ACTUAL_FINISH_DATE
, PPR.ACTUAL_START_DATE
, PPR.AS_OF_DATE AS_OF_DATE
, PTAV.BASELINED_ASGMT_END_DATE
, PTAV.BASELINED_ASGMT_START_DATE
, NVL(PPR.OTH_ACT_COST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_TC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_TC
, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_BUR_COST_TXN_CUR
, PPR.PPL_ETC_COST_TC
, PPR.EQPMT_ETC_COST_TC
, PPR.OTH_ETC_COST_TC
, NULL
, NULL
, NULL
, NULL
, (NVL(PPR.OTH_ACT_COST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_TC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_TC
, 0))) EST_AT_COMPLETION_COST
, NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(OTH_QUANTITY_TO_DATE
, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_QUANTITY
, PPR.ESTIMATED_REMAINING_EFFORT
, PPR.EQPMT_ETC_EFFORT
, NULL
, NULL
, NULL
, NULL
, PPR.OTH_ETC_QUANTITY
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.OTH_QUANTITY_TO_DATE
, 0))) EST_AT_COMPLETION_EFFORT
, PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_QUANTITY
, PPR.ESTIMATED_REMAINING_EFFORT
, PPR.EQPMT_ETC_EFFORT
, NULL
, NULL
, NULL
, NULL
, PPR.OTH_ETC_QUANTITY
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.OTH_QUANTITY_TO_DATE
, 0))) ESTIMATED_REMAINING_EFFORT
, PPR.ESTIMATED_FINISH_DATE
, PPR.ESTIMATED_START_DATE
, PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_BUR_COST_TXN_CUR
, PPR.PPL_ETC_COST_TC
, PPR.EQPMT_ETC_COST_TC
, PPR.OTH_ETC_COST_TC
, NULL
, NULL
, NULL
, NULL
, (NVL(PPR.OTH_ACT_COST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_COST_TO_DATE_TC
, 0)+NVL(PPR.EQPMT_ACT_COST_TO_DATE_TC
, 0))) ETC_COST_TC
, PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_QUANTITY
, PPR.ESTIMATED_REMAINING_EFFORT
, PPR.EQPMT_ETC_EFFORT
, NULL
, NULL
, NULL
, NULL
, PPR.OTH_ETC_QUANTITY
, (NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.OTH_QUANTITY_TO_DATE
, 0))) ETC_EFFORT
, PA_PROGRESS_UTILS.GET_NEXT_PROGRESS_CYCLE(PPE.PROJECT_ID
, PPE.PROJ_ELEMENT_ID
, PTAV.RESOURCE_ASSIGNMENT_ID
, 'PA_ASSIGNMENTS')
, PA_PROGRESS_UTILS.GET_PROG_DT_CLOSEST_TO_SYS_DT(PPE.PROJECT_ID
, PPE.PROJ_ELEMENT_ID
, PTAV.RESOURCE_ASSIGNMENT_ID
, 'PA_ASSIGNMENTS')
, PA_PROGRESS_UTILS.PERCENT_COMPLETE_VALUE( ( NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0) +NVL(PPR.OTH_QUANTITY_TO_DATE
, 0) )
, ( NVL(PPR.OTH_ETC_QUANTITY
, 0)+NVL(PPR.ESTIMATED_REMAINING_EFFORT
, 0)+NVL(PPR.EQPMT_ETC_EFFORT
, 0) ) ) PERCENT_COMPLETE_EFFORT
, TO_NUMBER(NULL) PERCENT_COMPLETE_ID
, PA_PROGRESS_UTILS.PERCENT_SPENT_VALUE( ( NVL(PPR.PPL_ACT_EFFORT_TO_DATE
, 0)+NVL(PPR.EQPMT_ACT_EFFORT_TO_DATE
, 0) +NVL(PPR.OTH_QUANTITY_TO_DATE
, 0) )
, ( NVL(PTAV.PLANNED_QUANTITY
, 0) ) ) PERCENT_SPENT_EFFORT
, NVL(PTAV.PLANNED_BUR_COST_TXN_CUR
, 0) PLANNED_COST
, PTAV.PLANNED_QUANTITY PLANNED_EFFORT
, PTAV.ASSIGNMENT_END_DATE
, PTAV.ASSIGNMENT_START_DATE
, PPE.PROJ_ELEMENT_ID
, PPE.NAME
, PPE.ELEMENT_NUMBER
, TO_CHAR(NULL) UNIT_OF_MEASURE
, PPEV.WBS_NUMBER
, PTAV.RESOURCE_ASSIGNMENT_ID RESOURCE_ASSIGNMENT_ID
, PPR.CURRENT_FLAG
, PPEV.ELEMENT_VERSION_ID
, PPEV.OBJECT_TYPE
, PPE.PROJ_ELEMENT_ID
, PPA.PROJECT_ID
, PPA.NAME
, PPA.SEGMENT1
, NULL PUBLISHED_FLAG
, PPR.RECORD_VERSION_NUMBER
, PTAV.UNIT_OF_MEASURE_CODE
, PTAV.UNIT_OF_MEASURE
, PTAV.TXN_CURRENCY_CODE
, DECODE(PPE.OBJECT_TYPE
, 'PA_STRUCTURES'
, PPPA.REMAIN_EFFORT_ENABLE_FLAG
, DECODE(PTT.REMAIN_EFFORT_ENABLE_FLAG
, 'Y'
, DECODE(PPPA.REMAIN_EFFORT_ENABLE_FLAG
, 'Y'
, 'Y'
, 'N')
, 'N'))
, DECODE(PPE.OBJECT_TYPE
, 'PA_TASKS'
, PTT.PROG_ENTRY_ENABLE_FLAG
, 'Y')
, PTAV.RESOURCE_ALIAS
, PTAV.RESOURCE_LIST_MEMBER_ID
, PTAV.RESOURCE_CLASS_CODE
, PTAV.RBS_ELEMENT_ID
, PTAV.RATE_BASED_FLAG RATE_BASED_FLAG
, NULL ACTUAL_EFFORT_THIS_PERIOD
, NULL ACTUAL_COST_THIS_PERIOD
, PA_PROGRESS_UTILS.GET_LATEST_PROGRESS_ENTRY_DATE(PPA.PROJECT_ID
, PPR.OBJECT_ID
, PPR.OBJECT_TYPE
, PPR.STRUCTURE_TYPE
, PPE.PROJ_ELEMENT_ID) LATEST_PROGRESS_DATE
, PPEV.PARENT_STRUCTURE_VERSION_ID STRUCTURE_VERSION_ID
, DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, PPR.PPL_ACT_RAWCOST_TO_DATE_TC
, 'EQUIPMENT'
, PPR.EQPMT_ACT_RAWCOST_TO_DATE_TC
, PPR.OTH_ACT_RAWCOST_TO_DATE_TC) ACTUAL_RAWCOST
, NVL(PTAV.PLANNED_RAW_COST_TXN_CUR
, 0) PLANNED_RAWCOST
, PTAV.BL_PLANNED_RAW_COST_TXN_CUR BASELINED_RAWCOST
, NVL(PPR.OTH_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.EQPMT_ACT_RAWCOST_TO_DATE_TC
, 0) +PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_RAW_COST_TXN_CUR
, PPR.PPL_ETC_RAWCOST_TC
, PPR.EQPMT_ETC_RAWCOST_TC
, PPR.OTH_ETC_RAWCOST_TC
, NULL
, NULL
, NULL
, NULL
, (NVL(PPR.OTH_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.EQPMT_ACT_RAWCOST_TO_DATE_TC
, 0))) EST_AT_COMPLETION_RAWCOST
, PA_PROGRESS_UTILS.SUM_ETC_VALUES(PTAV.PLANNED_RAW_COST_TXN_CUR
, PPR.PPL_ETC_RAWCOST_TC
, PPR.EQPMT_ETC_RAWCOST_TC
, PPR.OTH_ETC_RAWCOST_TC
, NULL
, NULL
, NULL
, NULL
, (NVL(PPR.OTH_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.EQPMT_ACT_RAWCOST_TO_DATE_TC
, 0))) ETC_RAWCOST_TC
, PA_PROGRESS_UTILS.PERCENT_COMPLETE_VALUE( ( NVL(PPR.OTH_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_RAWCOST_TO_DATE_TC
, 0) +NVL(PPR.EQPMT_ACT_RAWCOST_TO_DATE_TC
, 0) )
, ( NVL(PPR.OTH_ETC_RAWCOST_TC
, 0)+NVL(PPR.PPL_ETC_RAWCOST_TC
, 0)+NVL(PPR.EQPMT_ETC_RAWCOST_TC
, 0) ) ) PERCENT_COMPLETE_RAWCOST
, PA_PROGRESS_UTILS.PERCENT_SPENT_VALUE( ( NVL(PPR.OTH_ACT_RAWCOST_TO_DATE_TC
, 0)+NVL(PPR.PPL_ACT_RAWCOST_TO_DATE_TC
, 0) +NVL(PPR.EQPMT_ACT_RAWCOST_TO_DATE_TC
, 0) )
, ( NVL(PTAV.PLANNED_RAW_COST_TXN_CUR
, 0) ) ) PERCENT_SPENT_RAWCOST
, NULL ACTUAL_RAWCOST_THIS_PERIOD
, PTAV.UNPLANNED_FLAG UNPLANNED_ASSIGNMENTS_FLAG
FROM PA_PROJECTS_ALL PPA
, PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PPEV
, PA_PROJ_ELEM_VER_STRUCTURE PPVS
, PA_PROGRESS_ROLLUP PPR
, PA_PROJ_PROGRESS_ATTR PPPA
, PA_TASK_TYPES PTT
, PA_TASK_ASGMTS_V PTAV
WHERE PPE.PROJECT_ID = PPVS.PROJECT_ID
AND PPE.LINK_TASK_FLAG = 'N'
AND PPE.OBJECT_TYPE = 'PA_TASKS'
AND PPE.PROJ_ELEMENT_ID = PPEV.PROJ_ELEMENT_ID
AND PPEV.PARENT_STRUCTURE_VERSION_ID = PPVS.ELEMENT_VERSION_ID
AND PPA.PROJECT_ID = PPVS.PROJECT_ID
AND PPE.TYPE_ID = PTT.TASK_TYPE_ID(+)
AND PTT.OBJECT_TYPE = 'PA_TASKS'
AND PPEV.PROJECT_ID = PTAV.PROJECT_ID
AND PPEV.PROJ_ELEMENT_ID = PTAV.TASK_ID
AND PTAV.PROJECT_ID = PPR.PROJECT_ID
AND PTAV.TASK_ID = PPR.PROJ_ELEMENT_ID
AND PTAV.TASK_VERSION_ID = PPEV.ELEMENT_VERSION_ID
AND PPR.OBJECT_TYPE = 'PA_ASSIGNMENTS'
AND PTAV.RESOURCE_LIST_MEMBER_ID = PPR.OBJECT_ID
AND PPR.STRUCTURE_VERSION_ID IS NULL
AND PPR.CURRENT_FLAG IN ('Y'
, 'N')
AND PPPA.STRUCTURE_TYPE = 'WORKPLAN'
AND PPVS.PROJECT_ID = PPPA.PROJECT_ID(+)
AND PPVS.PROJ_ELEMENT_ID = PPPA.OBJECT_ID(+)
AND PTAV.TA_DISPLAY_FLAG = 'Y' UNION ALL SELECT TO_NUMBER(NULL) ACTUAL_COST
, TO_NUMBER(NULL) ACTUAL_EFFORT
, TO_DATE(NULL) ACTUAL_FINISH_DATE
, TO_DATE(NULL) ACTUAL_START_DATE
, TO_DATE(NULL) AS_OF_DATE
, PTAV.BASELINED_ASGMT_END_DATE
, PTAV.BASELINED_ASGMT_START_DATE
, NVL(PTAV.PLANNED_BUR_COST_TXN_CUR
, 0) EST_AT_COMPLETION_COST
, PTAV.PLANNED_QUANTITY EST_AT_COMPLETION_EFFORT
, TO_NUMBER(NULL) ESTIMATED_REMAINING_EFFORT
, TO_DATE(NULL) ESTIMATED_FINISH_DATE
, TO_DATE(NULL) ESTIMATED_START_DATE
, DECODE(PROJSTAT.PROJECT_SYSTEM_STATUS_CODE
, 'COMPLETED'
, 0
, NVL(PTAV.PLANNED_BUR_COST_TXN_CUR
, 0)) ETC_COST_TC
, DECODE(PROJSTAT.PROJECT_SYSTEM_STATUS_CODE
, 'COMPLETED'
, 0
, PTAV.PLANNED_QUANTITY) ETC_EFFORT
, PA_PROGRESS_UTILS.GET_NEXT_PROGRESS_CYCLE(PPE.PROJECT_ID
, PPE.PROJ_ELEMENT_ID
, PTAV.RESOURCE_ASSIGNMENT_ID
, 'PA_ASSIGNMENTS')
, PA_PROGRESS_UTILS.GET_PROG_DT_CLOSEST_TO_SYS_DT(PPE.PROJECT_ID
, PPE.PROJ_ELEMENT_ID
, PTAV.RESOURCE_ASSIGNMENT_ID
, 'PA_ASSIGNMENTS')
, TO_NUMBER(NULL) PERCENT_COMPLETE_EFFORT
, TO_NUMBER(NULL) PERCENT_COMPLETE_ID
, TO_NUMBER(NULL) PERCENT_SPENT_EFFORT
, NVL(PTAV.PLANNED_BUR_COST_TXN_CUR
, 0) PLANNED_COST
, PTAV.PLANNED_QUANTITY PLANNED_EFFORT
, PTAV.ASSIGNMENT_END_DATE
, PTAV.ASSIGNMENT_START_DATE
, PPE.PROJ_ELEMENT_ID
, PPE.NAME
, PPE.ELEMENT_NUMBER
, TO_CHAR(NULL) UNIT_OF_MEASURE
, PPEV.WBS_NUMBER
, PTAV.RESOURCE_ASSIGNMENT_ID RESOURCE_ASSIGNMENT_ID
, TO_CHAR(NULL) CURRENT_FLAG
, PPEV.ELEMENT_VERSION_ID
, PPEV.OBJECT_TYPE
, PPE.PROJ_ELEMENT_ID
, PPA.PROJECT_ID
, PPA.NAME
, PPA.SEGMENT1
, 'D' PUBLISHED_FLAG
, TO_NUMBER(NULL) RECORD_VERSION_NUMBER
, PTAV.UNIT_OF_MEASURE_CODE
, PTAV.UNIT_OF_MEASURE
, PTAV.TXN_CURRENCY_CODE
, DECODE(PPE.OBJECT_TYPE
, 'PA_STRUCTURES'
, PPPA.REMAIN_EFFORT_ENABLE_FLAG
, DECODE(PTT.REMAIN_EFFORT_ENABLE_FLAG
, 'Y'
, DECODE(PPPA.REMAIN_EFFORT_ENABLE_FLAG
, 'Y'
, 'Y'
, 'N')
, 'N'))
, DECODE(PPE.OBJECT_TYPE
, 'PA_TASKS'
, PTT.PROG_ENTRY_ENABLE_FLAG
, 'Y')
, PTAV.RESOURCE_ALIAS
, PTAV.RESOURCE_LIST_MEMBER_ID
, PTAV.RESOURCE_CLASS_CODE
, PTAV.RBS_ELEMENT_ID
, PTAV.RATE_BASED_FLAG RATE_BASED_FLAG
, TO_NUMBER(NULL) ACTUAL_EFFORT_THIS_PERIOD
, TO_NUMBER(NULL) ACTUAL_COST_THIS_PERIOD
, TO_DATE(NULL) LATEST_PROGRESS_DATE
, PPEV.PARENT_STRUCTURE_VERSION_ID STRUCTURE_VERSION_ID
, TO_NUMBER(NULL) ACTUAL_RAWCOST
, NVL(PTAV.PLANNED_RAW_COST_TXN_CUR
, 0) PLANNED_RAWCOST
, PTAV.BL_PLANNED_RAW_COST_TXN_CUR BASELINED_RAWCOST
, NVL(PTAV.PLANNED_RAW_COST_TXN_CUR
, 0) EST_AT_COMPLETION_RAWCOST
, DECODE(PROJSTAT.PROJECT_SYSTEM_STATUS_CODE
, 'COMPLETED'
, 0
, NVL(PTAV.PLANNED_RAW_COST_TXN_CUR
, 0)) ETC_RAWCOST_TC
, TO_NUMBER(NULL) PERCENT_COMPLETE_RAWCOST
, TO_NUMBER(NULL) PERCENT_SPENT_RAWCOST
, TO_NUMBER(NULL) ACTUAL_RAWCOST_THIS_PERIOD
, PTAV.UNPLANNED_FLAG UNPLANNED_ASSIGNMENTS_FLAG
FROM PA_PROJECTS_ALL PPA
, PA_PROJ_ELEMENTS PPE
, PA_PROJ_ELEMENT_VERSIONS PPEV
, PA_PROJ_ELEM_VER_STRUCTURE PPVS
, PA_PROJ_PROGRESS_ATTR PPPA
, PA_TASK_TYPES PTT
, PA_TASK_ASGMTS_V PTAV
, PA_PROJECT_STATUSES PROJSTAT
WHERE PPE.PROJECT_ID = PPVS.PROJECT_ID
AND PPE.LINK_TASK_FLAG = 'N'
AND PPE.OBJECT_TYPE = 'PA_TASKS'
AND PPE.PROJ_ELEMENT_ID = PPEV.PROJ_ELEMENT_ID
AND PPEV.PARENT_STRUCTURE_VERSION_ID = PPVS.ELEMENT_VERSION_ID
AND PPA.PROJECT_ID = PPVS.PROJECT_ID
AND PPE.TYPE_ID = PTT.TASK_TYPE_ID(+)
AND PTT.OBJECT_TYPE = 'PA_TASKS'
AND PPEV.PROJECT_ID = PTAV.PROJECT_ID
AND PPEV.PROJ_ELEMENT_ID = PTAV.TASK_ID
AND PTAV.TASK_VERSION_ID = PPEV.ELEMENT_VERSION_ID
AND PPPA.STRUCTURE_TYPE = 'WORKPLAN'
AND PPVS.PROJECT_ID = PPPA.PROJECT_ID(+)
AND PPVS.PROJ_ELEMENT_ID = PPPA.OBJECT_ID(+)
AND PTAV.TA_DISPLAY_FLAG = 'Y'
AND PROJSTAT.PROJECT_STATUS_CODE(+) = PPE.STATUS_CODE

Columns