VIEW: APPS.PA_ASSGN_COST_EFFORT_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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