[Home] [Help]
342: AND object_type = 'PA_DELIVERABLES'
343: AND trunc(date_computed)<= trunc(p_as_of_date)
344: AND structure_type = 'WORKPLAN'
345: AND published_flag = 'Y'
346: AND PA_PROGRESS_UTILS.get_system_task_status( status_code, 'PA_DELIVERABLES') = 'DLVR_IN_PROGRESS'
347: );
348:
349:
350: CURSOR c_get_object_status (l_project_id NUMBER, l_proj_element_id NUMBER)
448: , ppr.PROJ_COST_RATE_DATE
449: , ppr.TXN_CURRENCY_CODE
450: , ppr.PROG_PA_PERIOD_NAME
451: , ppr.PROG_GL_PERIOD_NAME
452: , pa_progress_utils.Get_BAC_Value(ppev.project_id, decode(p_structure_type,'WORKPLAN',p_wp_rollup_method,'FINANCIAL',p_fin_rollup_method), ppev.proj_element_id, ppev.element_version_id, p_structure_type,p_working_wp_prog_flag) BAC_value
453: , pa_progress_utils.Get_BAC_Value(ppev.project_id, decode(p_structure_type,'WORKPLAN',p_wp_rollup_method,'FINANCIAL',p_fin_rollup_method), ppev.proj_element_id, ppev.element_version_id, p_structure_type,p_working_wp_prog_flag,'N')
454: BAC_value_self -- bug 4493105
455: , null earned_value
456: , to_char(null) task_derivation_method
449: , ppr.TXN_CURRENCY_CODE
450: , ppr.PROG_PA_PERIOD_NAME
451: , ppr.PROG_GL_PERIOD_NAME
452: , pa_progress_utils.Get_BAC_Value(ppev.project_id, decode(p_structure_type,'WORKPLAN',p_wp_rollup_method,'FINANCIAL',p_fin_rollup_method), ppev.proj_element_id, ppev.element_version_id, p_structure_type,p_working_wp_prog_flag) BAC_value
453: , pa_progress_utils.Get_BAC_Value(ppev.project_id, decode(p_structure_type,'WORKPLAN',p_wp_rollup_method,'FINANCIAL',p_fin_rollup_method), ppev.proj_element_id, ppev.element_version_id, p_structure_type,p_working_wp_prog_flag,'N')
454: BAC_value_self -- bug 4493105
455: , null earned_value
456: , to_char(null) task_derivation_method
457: , ppr.progress_rollup_id
483: AND ppev.object_type = 'PA_STRUCTURES'
484: AND ppr.project_id = ppev.project_id
485: AND ppr.object_id = ppev.proj_element_id
486: AND ppr.object_type = 'PA_STRUCTURES'
487: AND ppr.as_of_date = pa_progress_utils.get_max_rollup_asofdate(ppev.project_id,
488: ppev.proj_element_id, ppev.object_type,p_as_of_date, ppev.element_version_id, p_structure_type, l_structure_version_id, ppev.proj_element_id/* Bug 3764224 */) -- FPM Dev CR 3
489: AND ppr.current_flag <> 'W'
490: AND ppr.EFF_ROLLUP_PROG_STAT_CODE = pps1.project_status_code(+)
491: AND ppr.progress_status_code = pps2.project_status_code(+)
565: , ppr.PROJ_COST_RATE_DATE
566: , ppr.TXN_CURRENCY_CODE
567: , ppr.PROG_PA_PERIOD_NAME
568: , ppr.PROG_GL_PERIOD_NAME
569: , pa_progress_utils.Get_BAC_Value(ppev2.project_id, decode(p_structure_type,'WORKPLAN',p_wp_rollup_method,
570: 'FINANCIAL',p_fin_rollup_method), ppev2.proj_element_id, ppev2.parent_structure_version_id,
571: p_structure_type,p_working_wp_prog_flag) BAC_value
572: , pa_progress_utils.Get_BAC_Value(ppev2.project_id, decode(p_structure_type,'WORKPLAN',p_wp_rollup_method,
573: 'FINANCIAL',p_fin_rollup_method), ppev2.proj_element_id, ppev2.parent_structure_version_id,
568: , ppr.PROG_GL_PERIOD_NAME
569: , pa_progress_utils.Get_BAC_Value(ppev2.project_id, decode(p_structure_type,'WORKPLAN',p_wp_rollup_method,
570: 'FINANCIAL',p_fin_rollup_method), ppev2.proj_element_id, ppev2.parent_structure_version_id,
571: p_structure_type,p_working_wp_prog_flag) BAC_value
572: , pa_progress_utils.Get_BAC_Value(ppev2.project_id, decode(p_structure_type,'WORKPLAN',p_wp_rollup_method,
573: 'FINANCIAL',p_fin_rollup_method), ppev2.proj_element_id, ppev2.parent_structure_version_id,
574: p_structure_type,p_working_wp_prog_flag,'N') BAC_value_self -- bug 4493105
575: , null earned_value
576: , decode(ppe.base_percent_comp_deriv_code, null, ttype.base_percent_comp_deriv_code,'^',ttype.base_percent_comp_deriv_code,ppe.base_percent_comp_deriv_code) task_derivation_method
609: AND pobj.object_id_from1 = ppev1.element_version_id
610: AND pobj.object_id_to1 = ppev2.element_version_id
611: AND pobj.relationship_type = 'S'
612: AND ppr.project_id = ppev2.project_id
613: AND ppr.as_of_date = pa_progress_utils.get_max_rollup_asofdate(ppev2.project_id,
614: ppev2.proj_element_id, ppev2.object_type,p_as_of_date, ppev2.element_version_id, p_structure_type, l_structure_version_id, ppev2.proj_element_id/*Bug 3764224 */) -- FPM Dev CR 3
615: AND ppr.current_flag <> 'W'
616: AND ppr.object_id = ppev2.proj_element_id
617: AND ppr.project_id = ppev2.project_id
704: , to_date(null) PROJ_COST_RATE_DATE
705: , to_char(null) TXN_CURRENCY_CODE
706: , to_char(null) PROG_PA_PERIOD_NAME
707: , to_char(null) PROG_GL_PERIOD_NAME
708: , pa_progress_utils.Get_BAC_Value(ppev.project_id, decode(p_structure_type,'WORKPLAN',p_wp_rollup_method,'FINANCIAL',p_fin_rollup_method), ppev.proj_element_id, ppev.element_version_id, p_structure_type,p_working_wp_prog_flag) BAC_value
709: , pa_progress_utils.Get_BAC_Value(ppev.project_id, decode(p_structure_type,'WORKPLAN',p_wp_rollup_method,'FINANCIAL',p_fin_rollup_method), ppev.proj_element_id, ppev.element_version_id, p_structure_type,p_working_wp_prog_flag,'N')
710: BAC_value_self -- bug 4493105
711: , null earned_value
712: , to_char(null) task_derivation_method
705: , to_char(null) TXN_CURRENCY_CODE
706: , to_char(null) PROG_PA_PERIOD_NAME
707: , to_char(null) PROG_GL_PERIOD_NAME
708: , pa_progress_utils.Get_BAC_Value(ppev.project_id, decode(p_structure_type,'WORKPLAN',p_wp_rollup_method,'FINANCIAL',p_fin_rollup_method), ppev.proj_element_id, ppev.element_version_id, p_structure_type,p_working_wp_prog_flag) BAC_value
709: , pa_progress_utils.Get_BAC_Value(ppev.project_id, decode(p_structure_type,'WORKPLAN',p_wp_rollup_method,'FINANCIAL',p_fin_rollup_method), ppev.proj_element_id, ppev.element_version_id, p_structure_type,p_working_wp_prog_flag,'N')
710: BAC_value_self -- bug 4493105
711: , null earned_value
712: , to_char(null) task_derivation_method
713: , to_number(null) progress_rollup_id
730: --IN (SELECT object_id from pa_proj_rollup_temp where process_number = l_process_number_temp)
731: element_version_id = temp.object_id
732: AND temp.process_number = l_process_number_temp
733: AND ppev.object_type = 'PA_STRUCTURES'
734: AND pa_progress_utils.get_max_rollup_asofdate(ppev.project_id,
735: ppev.proj_element_id, ppev.object_type,p_as_of_date, ppev.element_version_id, p_structure_type, l_structure_version_id, ppev.proj_element_id/*Bug 3764224 */)
736: IS NULL
737: UNION
738: -- select tasks
807: , to_date(null) PROJ_COST_RATE_DATE
808: , to_char(null) TXN_CURRENCY_CODE
809: , to_char(null) PROG_PA_PERIOD_NAME
810: , to_char(null) PROG_GL_PERIOD_NAME
811: , pa_progress_utils.Get_BAC_Value(ppev2.project_id, decode(p_structure_type,'WORKPLAN',p_wp_rollup_method,'FINANCIAL',
812: p_fin_rollup_method), ppev2.proj_element_id, ppev2.parent_structure_version_id,
813: p_structure_type,p_working_wp_prog_flag) BAC_value
814: , pa_progress_utils.Get_BAC_Value(ppev2.project_id, decode(p_structure_type,'WORKPLAN',p_wp_rollup_method,'FINANCIAL',
815: p_fin_rollup_method), ppev2.proj_element_id, ppev2.parent_structure_version_id,
810: , to_char(null) PROG_GL_PERIOD_NAME
811: , pa_progress_utils.Get_BAC_Value(ppev2.project_id, decode(p_structure_type,'WORKPLAN',p_wp_rollup_method,'FINANCIAL',
812: p_fin_rollup_method), ppev2.proj_element_id, ppev2.parent_structure_version_id,
813: p_structure_type,p_working_wp_prog_flag) BAC_value
814: , pa_progress_utils.Get_BAC_Value(ppev2.project_id, decode(p_structure_type,'WORKPLAN',p_wp_rollup_method,'FINANCIAL',
815: p_fin_rollup_method), ppev2.proj_element_id, ppev2.parent_structure_version_id,
816: p_structure_type,p_working_wp_prog_flag,'N') BAC_value_self -- bug 4493105
817: , null earned_value
818: , decode(ppe.base_percent_comp_deriv_code, null, ttype.base_percent_comp_deriv_code,'^',ttype.base_percent_comp_deriv_code,ppe.base_percent_comp_deriv_code) task_derivation_method
855: AND ppe.link_task_flag <> 'Y' -- 4392189
856: AND pobj.object_type_to = 'PA_TASKS'
857: AND pobj.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
858: AND ppe.type_id = ttype.task_type_id
859: AND pa_progress_utils.get_max_rollup_asofdate(ppev2.project_id,
860: ppev2.proj_element_id, ppev2.object_type,p_as_of_date, ppev2.element_version_id, p_structure_type, l_structure_version_id, ppev2.proj_element_id/*Bug 3764224 */)
861: IS NULL
862: AND ( (ppev2.financial_task_flag = 'Y' AND p_structure_type = 'FINANCIAL') OR p_structure_type = 'WORKPLAN') -- Bug 4346483
863: ;
1054: AND asgn.RESOURCE_LIST_MEMBER_ID = ppr.object_id -- Bug 3764224
1055: AND asgn.task_id = ppr.proj_element_id -- Bug 3764224
1056: AND ppr.object_type = 'PA_ASSIGNMENTS'
1057: -- AND asgn.ta_display_flag = 'Y' --Bug 4323537.
1058: AND ppr.as_of_date = pa_progress_utils.get_max_rollup_asofdate(asgn.project_id,
1059: asgn.RESOURCE_LIST_MEMBER_ID, 'PA_ASSIGNMENTS',p_as_of_date,asgn.task_version_id, p_structure_type, l_structure_version_id, asgn.task_id) ---Bug 3764224 -- FPM Dev CR 3
1060: AND ppr.current_flag <> 'W'
1061: AND ppr.structure_type = p_structure_type
1062: AND ((l_published_structure = 'Y' AND ppr.structure_version_id is null) OR (l_published_structure = 'N' AND ppr.structure_version_id = p_structure_version_id))
1155: ,asgn.planned_bur_cost_proj_cur) bac_value_in_task_deriv --3801780
1156: FROM
1157: pa_task_asgmts_v asgn
1158: WHERE asgn.task_version_id = c_task_ver_id
1159: AND pa_progress_utils.get_max_rollup_asofdate(asgn.project_id,
1160: asgn.RESOURCE_LIST_MEMBER_ID, 'PA_ASSIGNMENTS',p_as_of_date,asgn.task_version_id, p_structure_type, l_structure_version_id, asgn.task_id ) --- Bug 3764224
1161: IS NULL
1162: --bug 3958686, now hidden assignments should not to be selected
1163: -- AND asgn.ta_display_flag = 'Y' -- Bug 4323537
1201: -- AND obj.object_id_to1 = ppr.object_version_id(+)
1202: AND ppr.object_type(+) = 'PA_DELIVERABLES'
1203: AND ppr.project_id(+) = c_project_id
1204: AND ppr.object_id(+) = obj.object_id_to2
1205: AND ppr.as_of_date(+) = pa_progress_utils.get_max_rollup_asofdate(c_project_id,
1206: obj.object_id_to2, 'PA_DELIVERABLES',p_as_of_date,obj.object_id_to1, p_structure_type, l_structure_version_id, obj.object_id_from2 /* Bug 3764224 */) -- FPM Dev CR 3
1207: AND ppr.structure_type(+) = p_structure_type
1208: AND ppr.structure_version_id is null -- deliverable progress for working version is not allowed
1209: AND ppr.base_progress_status_code = pps3.project_status_code(+)
1307: SELECT
1308: ppv2.project_id sub_project_id
1309: ,ppv2.element_version_id sub_structure_ver_id
1310: ,ppv2.proj_element_id sub_proj_element_id
1311: , pa_progress_utils.Get_BAC_Value(ppv2.project_id, c_task_per_comp_deriv_method, ppv2.proj_element_id, ppv2.parent_structure_version_id,
1312: 'WORKPLAN','N') sub_project_bac_value
1313: FROM
1314: pa_proj_element_versions ppv2
1315: ,pa_proj_elem_ver_structure ppevs2
2068: END IF;
2069:
2070: -- Bug 3957792 : Added check for Cancelled tasks
2071: IF p_structure_type = 'WORKPLAN' AND l_published_structure = 'Y' AND l_tsk_deriv_method_tab(k) = 'DELIVERABLE'
2072: AND (PA_PROGRESS_UTILS.get_system_task_status(PA_PROGRESS_UTILS.get_task_status( p_project_id, l_tsk_proj_element_id_tab(k))) <> 'CANCELLED')
2073: THEN
2074:
2075: FOR cur_del_rec in cur_deliverables(l_tsk_proj_element_id_tab(k), l_tsk_object_id_to1_tab(k), p_project_id) LOOP -- FPM Change
2076: IF g1_debug_mode = 'Y' THEN
2273: -- 4586449 Begin : For link tasks, pass BAC_VALUE in terms of derivation method of the task
2274: -- in earned_value1 set
2275: IF p_structure_type = 'WORKPLAN' AND l_tsk_object_id_to1_tab(k) = nvl(l_subproj_task_version_id, -789) THEN
2276:
2277: l_rollup_table1(l_index).EARNED_VALUE1 := pa_progress_utils.Get_BAC_Value(p_project_id
2278: , l_tsk_deriv_method_tab(k), l_tsk_proj_element_id_tab(k), p_structure_version_id,
2279: 'WORKPLAN','N','Y');
2280: -- Bug 4636100 Issue 1 : We should always pass self plan for link task as 1
2281: --l_rollup_table1(l_index).EARNED_VALUE2 := NVL( l_tsk_bac_self_value_tab(k), 0 );
2352: END LOOP; -- End Tasks Loop
2353:
2354: --begin bug 3951982
2355: IF p_lowest_level_task = 'N' AND p_rollup_entire_wbs = 'N'
2356: AND pa_progress_utils.check_assignment_exists(p_project_id,p_task_version_id, 'PA_TASKS') = 'Y'
2357: AND l_actual_lowest_task = 'N' -- Bug 4392189
2358: THEN
2359: INSERT INTO pa_proj_rollup_temp(process_number, object_id, object_type, wbs_level)
2360: SELECT l_process_number_temp, p_task_version_id, 'PA_TASKS', 1
2695:
2696:
2697: BEGIN
2698: -- 4746476 : Added org_id in functions call below
2699: l_prog_pa_period_name := nvl(PA_PROGRESS_UTILS.Prog_Get_Pa_Period_Name(p_as_of_date,l_org_id),null);
2700: l_prog_gl_period_name := nvl(PA_PROGRESS_UTILS.Prog_Get_GL_Period_Name(p_as_of_date,l_org_id),null);
2701: EXCEPTION
2702: WHEN OTHERS THEN
2703: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2696:
2697: BEGIN
2698: -- 4746476 : Added org_id in functions call below
2699: l_prog_pa_period_name := nvl(PA_PROGRESS_UTILS.Prog_Get_Pa_Period_Name(p_as_of_date,l_org_id),null);
2700: l_prog_gl_period_name := nvl(PA_PROGRESS_UTILS.Prog_Get_GL_Period_Name(p_as_of_date,l_org_id),null);
2701: EXCEPTION
2702: WHEN OTHERS THEN
2703: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2704: p_msg_name => 'PA_FP_INVALID_DATE_RANGE');
3244: FETCH c_get_object_status INTO l_existing_object_status;
3245: CLOSE c_get_object_status;
3246:
3247: l_status_code := l_existing_object_status;
3248: l_system_status_code := PA_PROGRESS_UTILS.get_system_task_status( l_status_code ); --Bug#5374114
3249: l_status_code_temp := l_status_code; --Bug#5374114
3250: l_system_status_code_temp := l_system_status_code; --Bug#5374114
3251:
3252: IF ( nvl(l_rollup_table1(i).task_status1,0) <> 0 )
3257: pa_debug.write(x_Module=>'PA_PROGRESS_PVT.ROLLUP_PROGRESS_PVT', x_Msg => 'l_status_code ='||l_status_code, x_Log_Level=> 3);
3258: END IF;
3259: -- Now Defaulting of Status will happen even if the status is returned by Scheuling API, but it is wrong
3260:
3261: l_system_status_code := PA_PROGRESS_UTILS.get_system_task_status( l_status_code ); -- Bug 3956299
3262: IF g1_debug_mode = 'Y' THEN
3263: pa_debug.write(x_Module=>'PA_PROGRESS_PVT.ROLLUP_PROGRESS_PVT', x_Msg => 'l_status_code='||l_status_code, x_Log_Level=> 3);
3264: pa_debug.write(x_Module=>'PA_PROGRESS_PVT.ROLLUP_PROGRESS_PVT', x_Msg => 'l_existing_object_status='||l_existing_object_status, x_Log_Level=> 3);
3265: pa_debug.write(x_Module=>'PA_PROGRESS_PVT.ROLLUP_PROGRESS_PVT', x_Msg => 'l_system_status_code='||l_system_status_code, x_Log_Level=> 3);
3340: , actual_finish_date = l_actual_finish_date -- Bug 3956299
3341: WHERE object_id = cur_reverse_tree_rec.proj_element_id
3342: AND object_Type = cur_reverse_tree_rec.object_Type ---4743866
3343: AND project_id = p_project_id
3344: --AND PA_PROGRESS_UTILS.get_system_task_status( status_code ) NOT IN ( 'CANCELLED', 'COMPLETED' )
3345: AND PA_PROGRESS_UTILS.get_system_task_status( status_code ) NOT IN ( 'CANCELLED' ) -- 02/06/04 Satish
3346: AND structure_type = p_structure_type -- FPM Dev CR 3
3347: AND current_flag = 'N' and published_flag = 'N'
3348: ;
3341: WHERE object_id = cur_reverse_tree_rec.proj_element_id
3342: AND object_Type = cur_reverse_tree_rec.object_Type ---4743866
3343: AND project_id = p_project_id
3344: --AND PA_PROGRESS_UTILS.get_system_task_status( status_code ) NOT IN ( 'CANCELLED', 'COMPLETED' )
3345: AND PA_PROGRESS_UTILS.get_system_task_status( status_code ) NOT IN ( 'CANCELLED' ) -- 02/06/04 Satish
3346: AND structure_type = p_structure_type -- FPM Dev CR 3
3347: AND current_flag = 'N' and published_flag = 'N'
3348: ;
3349:
3354: , actual_finish_date = l_actual_finish_date -- Bug 3956299
3355: WHERE object_id = cur_reverse_tree_rec.proj_element_id
3356: AND object_Type = cur_reverse_tree_rec.object_Type ---4743866
3357: AND project_id = p_project_id
3358: AND PA_PROGRESS_UTILS.get_system_task_status( status_code ) NOT IN ( 'CANCELLED' ) -- 02/06/04 Satish
3359: AND structure_type = p_structure_type -- FPM Dev CR 3
3360: AND published_flag = 'Y' ---4743866
3361: AND current_flag = 'Y'
3362: AND trunc(date_computed) = trunc(p_as_of_date)
3554: AND date_computed <= p_as_of_date);
3555: EXCEPTION WHEN OTHERS THEN
3556: l_percent_complete_id := null;
3557: END;
3558: l_max_rollup_as_of_date2 := PA_PROGRESS_UTILS.get_max_rollup_asofdate2
3559: (p_project_id => p_project_id,
3560: p_object_id => cur_reverse_tree_rec.proj_element_id,
3561: p_object_type => l_rollup_table1(i).object_type,
3562: p_structure_type => p_structure_type,
4614: CLOSE c_get_task_weightage_method;
4615:
4616: ---4701759, 4701727
4617: if (l_child_prog_cycle_id <> l_par_prog_cycle_id) then
4618: l_parent_as_of_date := pa_progress_utils.get_next_progress_cycle(
4619: p_project_id => l_c1rec.parent_project_id
4620: ,p_task_id => l_c1rec.parent_task_id
4621: ,p_start_date => trunc(p_as_of_date)-1);
4622: else
4676: CLOSE c_get_par_task_rollup;
4677:
4678: --bug 4033869
4679: -- Get sub project plan in terms of derivation method of parent task
4680: l_pln_sub_project := pa_progress_utils.Get_BAC_Value(p_project_id => p_project_id
4681: ,p_task_weight_method => l_base_prcnt_comp_drv_code
4682: ,p_proj_element_id => l_sub_project_rec.object_id
4683: ,p_structure_version_id => p_structure_ver_id
4684: ,p_structure_type => 'WORKPLAN');
4741: pa_debug.write(x_Module=>'PA_PROGRESS_PVT.PROGRAM_ROLLUP_PVT', x_Msg => 'l_bcwp='||l_bcwp, x_Log_Level=> 3);
4742: END IF;
4743:
4744:
4745: IF PA_PROGRESS_UTILS.get_system_task_status(l_parent_task_status) NOT IN ( 'CANCELLED', 'ON_HOLD', 'COMPLETED' )
4746: --bug 4033869 If parent is ON-HOLD, CANCELLED or COMPLETED then do not rollup any progress attributes.
4747: THEN
4748: --l_parent_progress_status := nvl(l_parent_task_rec.PROGRESS_STATUS_CODE, l_parent_task_rec.EFF_ROLLUP_PROG_STAT_CODE);
4749: --l_child_progress_status := nvl(l_sub_project_rec.PROGRESS_STATUS_CODE, l_sub_project_rec.EFF_ROLLUP_PROG_STAT_CODE);
4850: END IF;
4851:
4852: -- For Task Status Rollup, we can use PA_SCHEDULE_OBJECTS_PVT.GET_PROGRESS_STATUS
4853: -- First we need to confirm that whether we are inserting/updating in PPC table
4854: END IF; --PA_PROGRESS_UTILS.get_system_task_status bug 4033869
4855: ELSE
4856: IF g1_debug_mode = 'Y' THEN
4857: pa_debug.write(x_Module=>'PA_PROGRESS_PVT.PROGRAM_ROLLUP_PVT', x_Msg => 'Mode is Insert I', x_Log_Level=> 3);
4858: END IF;
4896: END IF;
4897: */
4898: /*
4899:
4900: l_pln_parent_task := pa_progress_utils.Get_BAC_Value(p_project_id => l_c1rec.parent_project_id
4901: ,p_task_weight_method => l_base_prcnt_comp_drv_code
4902: ,p_proj_element_id => l_c1rec.parent_task_id
4903: ,p_structure_version_id => l_c1rec.parent_structure_ver_id
4904: ,p_structure_type => 'WORKPLAN');
5204: AND current_flag = 'Y';
5205: END;
5206: --bug 4033869
5207: --derive as of date for parent task relative to p_as_of_date
5208: l_parent_as_of_date := pa_progress_utils.get_next_progress_cycle(
5209: p_project_id => l_c1rec.parent_project_id
5210: ,p_task_id => l_c1rec.parent_task_id
5211: ,p_start_date => p_as_of_date);
5212: --End bug 4033869
5641: , ppr.PROJ_COST_RATE_DATE
5642: , ppr.TXN_CURRENCY_CODE
5643: , ppr.PROG_PA_PERIOD_NAME
5644: , ppr.PROG_GL_PERIOD_NAME
5645: , pa_progress_utils.Get_BAC_Value(ppev.project_id, p_wp_rollup_method, ppev.proj_element_id, ppev.parent_structure_version_id, 'WORKPLAN') BAC_value
5646: , pa_progress_utils.Get_BAC_Value(ppev.project_id, p_wp_rollup_method, ppev.proj_element_id, ppev.parent_structure_version_id, 'WORKPLAN','N','N') BAC_value_self -- bug 4493105
5647: , null earned_value
5648: , ppr.Oth_quantity_to_date
5649: , ppr.Oth_etc_quantity
5642: , ppr.TXN_CURRENCY_CODE
5643: , ppr.PROG_PA_PERIOD_NAME
5644: , ppr.PROG_GL_PERIOD_NAME
5645: , pa_progress_utils.Get_BAC_Value(ppev.project_id, p_wp_rollup_method, ppev.proj_element_id, ppev.parent_structure_version_id, 'WORKPLAN') BAC_value
5646: , pa_progress_utils.Get_BAC_Value(ppev.project_id, p_wp_rollup_method, ppev.proj_element_id, ppev.parent_structure_version_id, 'WORKPLAN','N','N') BAC_value_self -- bug 4493105
5647: , null earned_value
5648: , ppr.Oth_quantity_to_date
5649: , ppr.Oth_etc_quantity
5650: -- 4533112 : Added base_progress_status_code
5689: and ppc1.structure_type = 'WORKPLAN'
5690: and ppc1.as_of_date <= p_as_of_date
5691: and ppc1.structure_version_id is null
5692: and ppc1.current_flag = 'W')))
5693: -- AND ppr.as_of_date = pa_progress_utils.get_max_rollup_asofdate2(ppev.project_id,
5694: -- ppev.proj_element_id, ppev.object_type,'WORKPLAN',null, ppev.proj_element_id/* Bug 3764224 */)
5695: AND ppr.EFF_ROLLUP_PROG_STAT_CODE = pps1.project_status_code(+)
5696: AND ppr.progress_status_code = pps2.project_status_code(+)
5697: AND ppr.base_progress_status_code = pps3.project_status_code(+)
5809: , to_date(null) PROJ_COST_RATE_DATE
5810: , to_char(null) TXN_CURRENCY_CODE
5811: , to_char(null) PROG_PA_PERIOD_NAME
5812: , to_char(null) PROG_GL_PERIOD_NAME
5813: , pa_progress_utils.Get_BAC_Value(ppev.project_id, p_wp_rollup_method, ppev.proj_element_id, ppev.parent_structure_version_id, 'WORKPLAN') BAC_value
5814: , pa_progress_utils.Get_BAC_Value(ppev.project_id, p_wp_rollup_method, ppev.proj_element_id, ppev.parent_structure_version_id, 'WORKPLAN','N','N') BAC_value_self -- bug 4493105
5815: , null earned_value
5816: , to_number(null) Oth_quantity_to_date
5817: , to_number(null) Oth_etc_quantity
5810: , to_char(null) TXN_CURRENCY_CODE
5811: , to_char(null) PROG_PA_PERIOD_NAME
5812: , to_char(null) PROG_GL_PERIOD_NAME
5813: , pa_progress_utils.Get_BAC_Value(ppev.project_id, p_wp_rollup_method, ppev.proj_element_id, ppev.parent_structure_version_id, 'WORKPLAN') BAC_value
5814: , pa_progress_utils.Get_BAC_Value(ppev.project_id, p_wp_rollup_method, ppev.proj_element_id, ppev.parent_structure_version_id, 'WORKPLAN','N','N') BAC_value_self -- bug 4493105
5815: , null earned_value
5816: , to_number(null) Oth_quantity_to_date
5817: , to_number(null) Oth_etc_quantity
5818: -- 4533112 : Added base_progress_status_code
5830: AND structure_type = 'WORKPLAN'
5831: AND structure_version_id is null
5832: AND as_of_date <= p_as_of_date
5833: )
5834: -- AND pa_progress_utils.get_max_rollup_asofdate2(ppev.project_id,
5835: -- ppev.proj_element_id, ppev.object_type,'WORKPLAN',null, ppev.proj_element_id/* Bug 3764224 */) IS NULL
5836: -- Bug 3879461 End
5837: -- Begin fix for Bug # 4032987.
5838: -- This query returns the progress of immediate sub-tasks of the input task.
5945: , ppr.PROJ_COST_RATE_DATE
5946: , ppr.TXN_CURRENCY_CODE
5947: , ppr.PROG_PA_PERIOD_NAME
5948: , ppr.PROG_GL_PERIOD_NAME
5949: , pa_progress_utils.Get_BAC_Value(ppev2.project_id, p_wp_rollup_method, ppev2.proj_element_id, ppev2.parent_structure_version_id, 'WORKPLAN') BAC_value
5950: , pa_progress_utils.Get_BAC_Value(ppev2.project_id, p_wp_rollup_method, ppev2.proj_element_id, ppev2.parent_structure_version_id, 'WORKPLAN','N','N') BAC_value_self -- bug 4493105
5951: , null earned_value
5952: , ppr.Oth_quantity_to_date
5953: , ppr.Oth_etc_quantity
5946: , ppr.TXN_CURRENCY_CODE
5947: , ppr.PROG_PA_PERIOD_NAME
5948: , ppr.PROG_GL_PERIOD_NAME
5949: , pa_progress_utils.Get_BAC_Value(ppev2.project_id, p_wp_rollup_method, ppev2.proj_element_id, ppev2.parent_structure_version_id, 'WORKPLAN') BAC_value
5950: , pa_progress_utils.Get_BAC_Value(ppev2.project_id, p_wp_rollup_method, ppev2.proj_element_id, ppev2.parent_structure_version_id, 'WORKPLAN','N','N') BAC_value_self -- bug 4493105
5951: , null earned_value
5952: , ppr.Oth_quantity_to_date
5953: , ppr.Oth_etc_quantity
5954: -- 4533112 : Added base_progress_status_code
6112: , to_date(null) PROJ_COST_RATE_DATE
6113: , to_char(null) TXN_CURRENCY_CODE
6114: , to_char(null) PROG_PA_PERIOD_NAME
6115: , to_char(null) PROG_GL_PERIOD_NAME
6116: , pa_progress_utils.Get_BAC_Value(ppev2.project_id, p_wp_rollup_method, ppev2.proj_element_id, ppev2.parent_structure_version_id, 'WORKPLAN') BAC_value
6117: , pa_progress_utils.Get_BAC_Value(ppev2.project_id, p_wp_rollup_method, ppev2.proj_element_id, ppev2.parent_structure_version_id, 'WORKPLAN','N','N') BAC_value_self -- bug 4493105
6118: , null earned_value
6119: , to_number(null) Oth_quantity_to_date
6120: , to_number(null) Oth_etc_quantity
6113: , to_char(null) TXN_CURRENCY_CODE
6114: , to_char(null) PROG_PA_PERIOD_NAME
6115: , to_char(null) PROG_GL_PERIOD_NAME
6116: , pa_progress_utils.Get_BAC_Value(ppev2.project_id, p_wp_rollup_method, ppev2.proj_element_id, ppev2.parent_structure_version_id, 'WORKPLAN') BAC_value
6117: , pa_progress_utils.Get_BAC_Value(ppev2.project_id, p_wp_rollup_method, ppev2.proj_element_id, ppev2.parent_structure_version_id, 'WORKPLAN','N','N') BAC_value_self -- bug 4493105
6118: , null earned_value
6119: , to_number(null) Oth_quantity_to_date
6120: , to_number(null) Oth_etc_quantity
6121: -- 4533112 : Added base_progress_status_code
6383: -- It is to select only the rollup record till passed as of date
6384: AND ppr.structure_type(+) = 'WORKPLAN'
6385: AND ppr.structure_version_id(+) is null
6386: AND ppr.as_of_date(+) <= p_as_of_date
6387: AND nvl(ppr.progress_rollup_id,-99) = pa_progress_utils.get_w_pub_prupid_asofdate(p_project_id,asgn.RESOURCE_LIST_MEMBER_ID,'PA_ASSIGNMENTS',asgn.task_id,p_as_of_date);
6388:
6389: /* (SELECT nvl(max(progress_rollup_id),-99)
6390: FROM pa_progress_rollup
6391: WHERE project_id = p_project_id
6407: and ppc1.structure_type = 'WORKPLAN'
6408: and ppc1.as_of_date <= p_as_of_date
6409: and ppc1.structure_version_id is null
6410: and ppc1.current_flag = 'W'))))
6411: -- AND ppr.as_of_date = pa_progress_utils.get_max_rollup_asofdate2(asgn.project_id,
6412: -- asgn.RESOURCE_LIST_MEMBER_ID, 'PA_ASSIGNMENTS','WORKPLAN', null, asgn.task_id) ---Bug 3764224
6413: --bug 3958686, hidden assignments should not to be selected
6414: -- AND asgn.ta_display_flag = 'Y' -- Bug 4323537
6415: ; */
6454: AND ppr.object_type(+) = 'PA_DELIVERABLES'
6455: AND ppr.as_of_date(+) <= p_as_of_date
6456: -- Bug 3879461 : After discussion with Sameer Realeraskar, Majid
6457: -- It is to select only the rollup record till passed as of date
6458: AND nvl(ppr.progress_rollup_id,-99) = pa_progress_utils.get_w_pub_prupid_asofdate(c_project_id,obj.object_id_to2,'PA_DELIVERABLES',obj.object_id_from2,p_as_of_date)
6459: AND ppr.base_progress_status_code = pps3.project_status_code(+)
6460: AND ppr.progress_status_code = pps2.project_status_code(+);
6461:
6462: /* (SELECT nvl(max(progress_rollup_id),-99)
6482: and ppc1.structure_type = 'WORKPLAN'
6483: and ppc1.as_of_date <= p_as_of_date
6484: and ppc1.structure_version_id is null
6485: and ppc1.current_flag = 'W'))))
6486: -- AND ppr.as_of_date(+) = pa_progress_utils.get_max_rollup_asofdate2(c_project_id,
6487: -- obj.object_id_to2, 'PA_DELIVERABLES','WORKPLAN', null, obj.object_id_from2 ) -- bug 3764224 Bug 3808044 : Changed to2 to from2
6488: AND ppr.structure_type(+) = 'WORKPLAN'
6489: AND ppr.structure_version_id(+) is null
6490: -- AND ppr.current_flag(+) = 'W' --bug 3879461
6675: AND task_id = p_task_id
6676: AND object_type = 'PA_DELIVERABLES'
6677: AND trunc(date_computed)<= trunc(p_as_of_date)
6678: AND structure_type = 'WORKPLAN'
6679: AND PA_PROGRESS_UTILS.get_system_task_status( status_code, 'PA_DELIVERABLES') = 'DLVR_IN_PROGRESS'
6680: );
6681:
6682: l_actual_exists VARCHAR2(1):='N';
6683: -- Bug 3922325 End
6687: SELECT
6688: ppv2.project_id sub_project_id
6689: ,ppv2.element_version_id sub_structure_ver_id
6690: ,ppv2.proj_element_id sub_proj_element_id
6691: , pa_progress_utils.Get_BAC_Value(ppv2.project_id, c_task_per_comp_deriv_method, ppv2.proj_element_id, ppv2.parent_structure_version_id,
6692: 'WORKPLAN','N') sub_project_bac_value
6693: FROM
6694: pa_proj_element_versions ppv2
6695: ,pa_proj_elem_ver_structure ppevs2
7434: IF cur_tasks_rec.object_id_to1 = nvl(l_subproj_task_version_id, -789)
7435: AND cur_tasks_rec.object_id_to1 = p_task_version_id
7436: THEN
7437:
7438: l_rollup_table1(l_index).EARNED_VALUE1 := pa_progress_utils.Get_BAC_Value(p_project_id
7439: , l_task_derivation_code, p_task_id, p_structure_version_id,
7440: 'WORKPLAN','N','Y');
7441: -- Bug 4636100 Issue 1 : We should always pass self plan for link task as 1
7442: --l_rollup_table1(l_index).EARNED_VALUE2 := NVL( cur_tasks_rec.BAC_VALUE_SELF, 0 );
7554: /*
7555: --bug 3951982
7556: --populate scheduling pl/sql table with sub-tasks of a summary tasks if it has assignments.
7557: IF PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(p_task_version_id => p_task_version_id ) = 'N'
7558: AND pa_progress_utils.check_assignment_exists(p_project_id,p_task_version_id, 'PA_TASKS') = 'Y'
7559: THEN
7560: -- Loop thru all tasks of a passed task
7561: FOR cur_tasks_rec in cur_tasks( p_task_version_id ) LOOP
7562: IF cur_tasks_rec.object_type = 'PA_TASKS'
7938: END IF;
7939:
7940: pa_debug.write(x_Module=>'PA_PROGRESS_PVT.UPDATE_ASGN_DLV_TO_TASK_ROLLUP_PVT', x_Msg => 'Getting Periods', x_Log_Level=> 3);
7941: BEGIN
7942: l_prog_pa_period_name := nvl(PA_PROGRESS_UTILS.Prog_Get_Pa_Period_Name(p_as_of_date),null);
7943: l_prog_gl_period_name := nvl(PA_PROGRESS_UTILS.Prog_Get_GL_Period_Name(p_as_of_date),null);
7944: EXCEPTION
7945: WHEN OTHERS THEN
7946: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
7939:
7940: pa_debug.write(x_Module=>'PA_PROGRESS_PVT.UPDATE_ASGN_DLV_TO_TASK_ROLLUP_PVT', x_Msg => 'Getting Periods', x_Log_Level=> 3);
7941: BEGIN
7942: l_prog_pa_period_name := nvl(PA_PROGRESS_UTILS.Prog_Get_Pa_Period_Name(p_as_of_date),null);
7943: l_prog_gl_period_name := nvl(PA_PROGRESS_UTILS.Prog_Get_GL_Period_Name(p_as_of_date),null);
7944: EXCEPTION
7945: WHEN OTHERS THEN
7946: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
7947: p_msg_name => 'PA_FP_INVALID_DATE_RANGE');
8067: l_rolled_up_base_prog_stat := l_cur_pa_rollup1_rec.base_progress_status_code;
8068:
8069: --Check whether there exists any rollup record for the task.
8070: --if exists then update otherwise create.
8071: l_PROGRESS_ROLLUP_ID := PA_PROGRESS_UTILS.get_prog_rollup_id(
8072: p_project_id => p_project_id
8073: ,p_object_id => p_task_id
8074: ,p_object_type => 'PA_TASKS'
8075: ,p_object_version_id => p_task_version_id
8205: UPDATE pa_percent_completes
8206: SET status_code = l_status_code
8207: WHERE object_id = p_task_id
8208: AND project_id = p_project_id
8209: AND PA_PROGRESS_UTILS.get_system_task_status( status_code ) NOT IN ( 'CANCELLED' )
8210: AND structure_type = 'WORKPLAN'
8211: AND current_flag = 'N' and published_flag = 'N';
8212:
8213: END IF;*/
8252: pa_debug.write(x_Module=>'PA_PROGRESS_PVT.UPDATE_ROLLUP_PROGRESS_PVT', x_Msg => 'l_status_code ='||l_status_code, x_Log_Level=> 3);
8253: END IF; -- Bug 3956299 : Reduced scope of IF.
8254: -- Now Defaulting of Status will happen even if the status is returned by Scheuling API, but it is wrong
8255: --ELSE
8256: --l_task_system_status_code := PA_PROGRESS_UTILS.get_system_task_status( l_existing_object_status ); Bug 3956299
8257: l_task_system_status_code := PA_PROGRESS_UTILS.get_system_task_status( l_status_code ); -- Bug 3956299
8258:
8259: IF g1_debug_mode = 'Y' THEN
8260: pa_debug.write(x_Module=>'PA_PROGRESS_PVT.UPDATE_ASGN_DLV_TO_TASK_ROLLUP_PVT', x_Msg => 'l_status_code='||l_status_code, x_Log_Level=> 3);
8253: END IF; -- Bug 3956299 : Reduced scope of IF.
8254: -- Now Defaulting of Status will happen even if the status is returned by Scheuling API, but it is wrong
8255: --ELSE
8256: --l_task_system_status_code := PA_PROGRESS_UTILS.get_system_task_status( l_existing_object_status ); Bug 3956299
8257: l_task_system_status_code := PA_PROGRESS_UTILS.get_system_task_status( l_status_code ); -- Bug 3956299
8258:
8259: IF g1_debug_mode = 'Y' THEN
8260: pa_debug.write(x_Module=>'PA_PROGRESS_PVT.UPDATE_ASGN_DLV_TO_TASK_ROLLUP_PVT', x_Msg => 'l_status_code='||l_status_code, x_Log_Level=> 3);
8261: pa_debug.write(x_Module=>'PA_PROGRESS_PVT.UPDATE_ASGN_DLV_TO_TASK_ROLLUP_PVT', x_Msg => 'l_existing_object_status='||l_existing_object_status, x_Log_Level=> 3);
8334: , actual_start_date = l_actual_start_date -- Bug 3956299
8335: , actual_finish_date = l_actual_finish_date -- Bug 3956299
8336: WHERE object_id = p_task_id
8337: AND project_id = p_project_id
8338: AND PA_PROGRESS_UTILS.get_system_task_status( status_code ) NOT IN ( 'CANCELLED' )
8339: AND structure_type = 'WORKPLAN'
8340: AND current_flag = 'N' and published_flag = 'N';
8341: END IF;
8342: -- Bug 3879461 End
8461: THEN
8462: /* Start of changes for bug 5675437*/
8463: -- For hidden assignment case, we need to get the actuals from submitted record rather than current working one
8464:
8465: IF l_rollup_table1(i).SUMMARY_OBJECT_FLAG = 'N' AND PA_PROGRESS_UTILS.check_assignment_exists(p_project_id, p_task_version_id, 'PA_TASKS') = 'N' THEN
8466:
8467: l_PROGRESS_ROLLUP_ID3 := PA_PROGRESS_UTILS.get_prog_rollup_id(
8468: p_project_id => p_project_id
8469: ,p_object_id => p_task_id
8463: -- For hidden assignment case, we need to get the actuals from submitted record rather than current working one
8464:
8465: IF l_rollup_table1(i).SUMMARY_OBJECT_FLAG = 'N' AND PA_PROGRESS_UTILS.check_assignment_exists(p_project_id, p_task_version_id, 'PA_TASKS') = 'N' THEN
8466:
8467: l_PROGRESS_ROLLUP_ID3 := PA_PROGRESS_UTILS.get_prog_rollup_id(
8468: p_project_id => p_project_id
8469: ,p_object_id => p_task_id
8470: ,p_object_type => 'PA_TASKS'
8471: ,p_object_version_id => p_task_version_id
8652:
8653: --update progress_outdated_flag for summary tasks if there exists any ppc record
8654: --for the summary task
8655:
8656: l_progress_exists_on_aod := PA_PROGRESS_UTILS.check_prog_exists_on_aod(
8657: p_task_id => p_task_id
8658: ,p_object_id => p_task_id -- Bug 3764224
8659: ,p_as_of_date => p_as_of_date
8660: ,p_project_id => p_project_id
8695: END;
8696:
8697: -- Bug 3879461 Begin : Code Not Required now
8698: /*
8699: l_max_rollup_as_of_date2 := PA_PROGRESS_UTILS.get_max_rollup_asofdate2
8700: (p_project_id => p_project_id,
8701: p_object_id => p_task_id,
8702: p_object_type => l_rollup_table1(i).object_type,
8703: p_structure_type => 'WORKPLAN',
8862:
8863: --update progress_outdated_flag for summary tasks if there exists any ppc record
8864: --for the summary task
8865:
8866: l_progress_exists_on_aod := PA_PROGRESS_UTILS.check_prog_exists_on_aod(
8867: p_task_id => p_task_id
8868: ,p_object_id => p_task_id -- Bug 3764224
8869: ,p_as_of_date => p_as_of_date
8870: ,p_project_id => p_project_id
10007: AND target.resource_list_member_id =
10008: decode(l_res_list_mismatch_flag, 'N', source.resource_list_member_id,
10009: l_target_people_rlm_id)
10010: --bug 3956258
10011: AND 'Y' = PA_PROGRESS_UTILS.check_object_has_prog(
10012: source.project_id --p_project_id
10013: ,source.task_id --p_proj_element_id
10014: -- ,decode( source.ta_display_flag, 'N', source.task_id,source.resource_list_member_id) --p_object_id -- Fix for Bug # 4112283.
10015: ,source.resource_list_member_id -- Fix for Bug # 4112283.