View: PA_ASSGN_COST_EFFORT_V

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

View Text

SELECT PTAV.PROJECT_ID PROJECT_ID
, PTAV.TASK_ID TASK_ID
, PTAV.TASK_VERSION_ID TASK_VERSION_ID
, PPVS.ELEMENT_VERSION_ID STRUCTURE_VERSION_ID
, PTAV.RESOURCE_LIST_MEMBER_ID RESOURCE_LIST_MEMBER_ID
, PTAV.RESOURCE_ASSIGNMENT_ID RESOURCE_ASSIGNMENT_ID
, PPR.ACTUAL_START_DATE ACTUAL_START_DATE
, PPR.ACTUAL_FINISH_DATE ACTUAL_FINISH_DATE
, PPR.ESTIMATED_START_DATE ESTIMATED_START_DATE
, PPR.ESTIMATED_FINISH_DATE ESTIMATED_FINISH_DATE
, NVL(PTAV.PLANNED_BUR_COST_TXN_CUR
, 0) PLANNED_COST
, NVL(PTAV.PLANNED_RAW_COST_TXN_CUR
, 0) PLANNED_RAWCOST
, PTAV.PLANNED_QUANTITY PLANNED_EFFORT
, 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_TC
, 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_TC
, 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))
, DECODE(PPWA.WP_ENABLE_VERSION_FLAG
, 'Y'
, 'PUBLISH'
, 'WORKING')) ETC_RAWCOST_TC
, 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))
, DECODE(PPWA.WP_ENABLE_VERSION_FLAG
, 'Y'
, 'PUBLISH'
, 'WORKING')) ETC_COST_TC
, 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
, DECODE(PPWA.WP_ENABLE_VERSION_FLAG
, 'N'
, PA_FP_FCST_GEN_AMT_UTILS.GET_ETC_FROM_PLAN_ACT(NVL (PTAV.PLANNED_QUANTITY
, 0)
, DECODE(PTAV.RESOURCE_CLASS_CODE
, 'PEOPLE'
, PPR.PPL_ACT_EFFORT_TO_DATE
, 'EQUIPMENT'
, PPR.EQPMT_ACT_EFFORT_TO_DATE
, PPR.OTH_QUANTITY_TO_DATE))
, 'Y'
, 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))
, 'PUBLISH' ) )ETC_EFFORT
, PTAV.BUDGET_VERSION_ID BUDGET_VERSION_ID
, PPR.PROGRESS_ROLLUP_ID
FROM PA_PROGRESS_ROLLUP PPR
, PA_PROJ_ELEM_VER_STRUCTURE PPVS
, PA_TASK_ASGMTS_V PTAV
, PA_PROJ_WORKPLAN_ATTR PPWA
WHERE PPVS.PROJ_ELEMENT_ID = PPWA.PROJ_ELEMENT_ID
AND PPVS.PROJECT_ID = PPWA.PROJECT_ID
AND PPVS.PROJECT_ID = PTAV.PROJECT_ID
AND PPVS.ELEMENT_VERSION_ID = PTAV.STRUCTURE_VERSION_ID
AND PPVS.STATUS_CODE = 'STRUCTURE_PUBLISHED'
AND PPR.OBJECT_ID (+) = PTAV.RESOURCE_LIST_MEMBER_ID
AND PPR.PROJECT_ID (+) = PTAV.PROJECT_ID
AND PPR.PROJ_ELEMENT_ID (+) = PTAV.TASK_ID
AND PPR.OBJECT_TYPE (+) = 'PA_ASSIGNMENTS'
AND PPR.STRUCTURE_TYPE (+) = 'WORKPLAN'
AND PPR.STRUCTURE_VERSION_ID (+) IS NULL
AND PPR.OBJECT_VERSION_ID (+) <= PTAV.TASK_VERSION_ID
AND PPR.CURRENT_FLAG (+) <> 'W'
AND NVL(PPR.AS_OF_DATE
, TRUNC(SYSDATE)) = (SELECT NVL(MAX(PPR2.AS_OF_DATE)
, TRUNC(SYSDATE))
FROM PA_PROGRESS_ROLLUP PPR2
, PA_PROJ_ELEMENT_VERSIONS PPEV2
, PA_PROJ_ELEM_VER_STRUCTURE PPVS2
WHERE PPR2.OBJECT_ID = PTAV.RESOURCE_LIST_MEMBER_ID
AND PPR2.PROJ_ELEMENT_ID = PTAV.TASK_ID
AND PPR2.PROJECT_ID = PTAV.PROJECT_ID
AND PPR2.OBJECT_TYPE = 'PA_ASSIGNMENTS'
AND PPR2.STRUCTURE_TYPE = 'WORKPLAN'
AND PPR2.STRUCTURE_VERSION_ID IS NULL
AND PPR2.CURRENT_FLAG <> 'W'
AND PPR2.PROJECT_ID = PPEV2.PROJECT_ID
AND PPR2.PROJ_ELEMENT_ID = PPEV2.PROJ_ELEMENT_ID
AND PPR2.OBJECT_VERSION_ID = PPEV2.ELEMENT_VERSION_ID
AND PPEV2.PARENT_STRUCTURE_VERSION_ID = PPVS2.ELEMENT_VERSION_ID
AND PPVS2.PROJECT_ID = PPR2.PROJECT_ID
AND PPVS2.STATUS_CODE = 'STRUCTURE_PUBLISHED'
AND PPVS2.PUBLISHED_DATE <= PPVS.PUBLISHED_DATE) UNION SELECT PTAV.PROJECT_ID PROJECT_ID
, PTAV.TASK_ID TASK_ID
, PTAV.TASK_VERSION_ID TASK_VERSION_ID
, PPVS.ELEMENT_VERSION_ID STRUCTURE_VERSION_ID
, PTAV.RESOURCE_LIST_MEMBER_ID RESOURCE_LIST_MEMBER_ID
, PTAV.RESOURCE_ASSIGNMENT_ID RESOURCE_ASSIGNMENT_ID
, PPR.ACTUAL_START_DATE ACTUAL_START_DATE
, PPR.ACTUAL_FINISH_DATE ACTUAL_FINISH_DATE
, PPR.ESTIMATED_START_DATE ESTIMATED_START_DATE
, PPR.ESTIMATED_FINISH_DATE ESTIMATED_FINISH_DATE
, NVL(PTAV.PLANNED_BUR_COST_TXN_CUR
, 0) PLANNED_COST
, NVL(PTAV.PLANNED_RAW_COST_TXN_CUR
, 0) PLANNED_RAWCOST
, PTAV.PLANNED_QUANTITY PLANNED_EFFORT
, 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_TC
, 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_TC
, 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))
, 'WORKING') ETC_RAWCOST_TC
, 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))
, 'WORKING') ETC_COST_TC
, 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
, 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))
, 'WORKING') ETC_EFFORT
, PTAV.BUDGET_VERSION_ID BUDGET_VERSION_ID
, PPR.PROGRESS_ROLLUP_ID
FROM PA_PROGRESS_ROLLUP PPR
, PA_TASK_ASGMTS_V PTAV
, PA_PROJ_ELEM_VER_STRUCTURE PPVS
WHERE PPR.PROJECT_ID (+) = PTAV.PROJECT_ID
AND PPR.PROJ_ELEMENT_ID (+) = PTAV.TASK_ID
AND PPR.OBJECT_VERSION_ID (+) = PTAV.TASK_VERSION_ID
AND PPR.OBJECT_ID (+) = PTAV.RESOURCE_LIST_MEMBER_ID
AND PPR.OBJECT_TYPE (+) = 'PA_ASSIGNMENTS'
AND PPR.STRUCTURE_VERSION_ID (+) IS NOT NULL
AND PPR.CURRENT_FLAG (+) = 'Y'
AND PPVS.STATUS_CODE <> 'STRUCTURE_PUBLISHED'
AND PPVS.PROJECT_ID = PTAV.PROJECT_ID
AND PPVS.ELEMENT_VERSION_ID = PTAV.STRUCTURE_VERSION_ID

Columns