[Home] [Help]
471: ,'N' create_required
472: ,'N' update_required
473: , ppr.base_percent_complete -- 4392189 : Program Reporting Changes - Phase 2
474: FROM pa_proj_element_versions ppev,
475: pa_progress_rollup ppr,
476: pa_project_statuses pps1,
477: pa_project_statuses pps2,
478: pa_project_statuses pps3 ,
479: pa_proj_rollup_temp temp
595: FROM
596: pa_object_relationships pobj,
597: pa_proj_element_versions ppev1,
598: pa_proj_element_versions ppev2,
599: pa_progress_rollup ppr,
600: pa_proj_elements ppe,
601: pa_project_statuses pps1,
602: pa_project_statuses pps2,
603: pa_project_statuses pps3,
893: ;
894:
895: CURSOR cur_get_asgn_progress(c_object_id NUMBER, c_proj_element_id NUMBER) IS
896: SELECT ppr.*
897: FROM pa_progress_rollup ppr
898: WHERE ppr.project_id = p_project_id
899: AND ppr.object_id = c_object_id
900: AND ppr.proj_element_id = c_proj_element_id
901: AND ppr.object_type = 'PA_ASSIGNMENTS'
1052: ))
1053: ) bac_value_in_task_deriv
1054: FROM
1055: pa_task_asgmts_v asgn,
1056: pa_progress_rollup ppr
1057: WHERE asgn.task_version_id = c_task_ver_id
1058: AND asgn.project_id = ppr.project_id
1059: AND asgn.RESOURCE_LIST_MEMBER_ID = ppr.object_id -- Bug 3764224
1060: AND asgn.task_id = ppr.proj_element_id -- Bug 3764224
1191: , pps2.project_status_weight override_weight ---override progress status code
1192: , pps3.project_status_weight base_weight --base prog status
1193: FROM pa_proj_elements elem
1194: , pa_object_relationships obj
1195: , pa_progress_rollup ppr
1196: , pa_project_statuses pps2
1197: , pa_project_statuses pps3
1198: WHERE obj.object_id_from2= c_task_proj_elem_id
1199: AND obj.object_type_from = 'PA_TASKS'
1282: IS
1283: select /*+ leading (ROLLUP) */ ppr.as_of_date, ver2.proj_element_id child_task_id, ver2.element_version_id child_task_ver_id
1284: from pa_object_relationships obj
1285: , pa_proj_element_versions ver
1286: , pa_progress_rollup ppr
1287: , pa_proj_rollup_temp rollup
1288: , pa_proj_element_versions ver2
1289: where rollup.object_id = obj.object_id_to1
1290: AND rollup.process_number = l_process_number_temp
1355: ,decode(c_task_per_comp_deriv_method,'EFFORT', nvl(ppr.PPL_ACT_EFFORT_TO_DATE,0) + nvl(ppr.estimated_remaining_effort,0) + nvl(ppr.EQPMT_ACT_EFFORT_TO_DATE,0) + nvl(ppr.EQPMT_ETC_EFFORT,0)
1356: , nvl(ppr.OTH_ACT_COST_TO_DATE_PC,0) + nvl(ppr.OTH_ETC_COST_PC,0) + nvl(ppr.PPL_ACT_COST_TO_DATE_PC,0) + nvl(ppr.PPL_ETC_COST_PC,0) + nvl(ppr.EQPMT_ACT_COST_TO_DATE_PC,0) + nvl(ppr.EQPMT_ETC_COST_PC,0)) bac_value
1357: , nvl(ppr.completed_percentage, ppr.eff_rollup_percent_comp) completed_percentage --Bug 4506009
1358: FROM
1359: pa_progress_rollup ppr
1360: ,pa_project_statuses pps1
1361: ,pa_project_statuses pps2
1362: ,pa_project_statuses pps3
1363: ,pa_project_statuses pps4
2085: AND l_asgn_progress_rec.actual_finish_date IS NOT NULL) THEN
2086: -- Update to happen only version is disabled or on a working version when version enabled
2087: IF (l_wp_version_enabled_flag = 'N'
2088: OR (l_wp_version_enabled_flag = 'Y'AND l_asgn_progress_rec.structure_version_id IS NOT NULL) ) THEN
2089: UPDATE PA_PROGRESS_ROLLUP
2090: SET ACTUAL_FINISH_DATE = NULL
2091: WHERE progress_rollup_id = l_asgn_progress_rec.progress_rollup_id;
2092: END IF;
2093: END IF; -- end check for
3443: -- One solution for this issue could be to pass
3444: -- p_upd_new_elem_ver_id_flag as 'N' from rollup API call in
3445: -- program_rollup_pvt, rollup_future_progress_pvt, and recursive call of Rollup API
3446: -- within Rollup API. This solution works for Case 1 and Case2, but not Case 3
3447: -- Best way is to always retain the existing object_version_id in pa_progress_rollup
3448: -- table in case of update, in case of insert, use the new object version id
3449:
3450: -- Commented below code and added new condition
3451:
3617: l_tsk_prog_gl_period_name_tab(task_index) := l_prog_gl_period_name;
3618:
3619:
3620: IF l_max_rollup_as_of_date2 < p_as_of_date THEN
3621: UPDATE pa_progress_rollup
3622: SET current_flag = 'N'
3623: WHERE project_id = p_project_id
3624: AND object_id = cur_reverse_tree_rec.proj_element_id
3625: AND object_type = l_rollup_table1(i).object_type
3636: END LOOP;
3637: END LOOP;
3638:
3639: FORALL i in 1..l_tsk_object_version_id_tab.count
3640: UPDATE pa_progress_rollup
3641: SET
3642: object_version_id = l_tsk_object_version_id_tab(i)
3643: , last_update_date = sysdate
3644: , last_updated_by = l_user_id
3692:
3693:
3694: IF p_structure_type = 'WORKPLAN' AND l_structure_version_id IS NULL AND p_progress_mode <> 'BACKDATED' THEN
3695: FORALL i in 1..l_tsk_object_version_id_tab.count
3696: UPDATE pa_progress_rollup
3697: SET
3698: object_version_id = l_tsk_object_version_id_tab(i)
3699: , last_update_date = sysdate
3700: , last_updated_by = l_user_id
3756: -- We should not create new records if the rollup is called from Future Rollup API
3757: -- It should just update.
3758: IF p_calling_mode <> 'FUTURE_ROLLUP' OR p_calling_mode IS NULL THEN
3759: FORALL i in 1..l_tsk_object_version_id_tab.count
3760: INSERT INTO pa_progress_rollup
3761: (
3762: progress_rollup_id
3763: ,project_id
3764: ,object_id
3829: ,prog_gl_period_name
3830: ,base_percent_complete
3831: )
3832: SELECT
3833: PA_PROGRESS_ROLLUP_S.nextval
3834: , p_project_id
3835: , l_tsk_object_id_tab(i)
3836: , l_tsk_object_type_tab(i)
3837: , p_as_of_date
4200: -- Bug 3764224 : This cursor deals with tasks only, so no need to add join of proj_element_id
4201: CURSOR cur_tree_rollup_dates
4202: IS
4203: select as_of_date, published_flag, ppc.current_flag
4204: from pa_progress_rollup ppr, pa_percent_completes ppc
4205: where ppr.project_id = p_project_id
4206: and ppr.object_id = l_object_id
4207: and ppr.as_of_Date > p_as_of_date
4208: and ppr.project_id = ppc.project_id(+)
4480: AND structure_type = 'WORKPLAN';
4481:
4482: CURSOR c_get_sub_proj_rollup IS
4483: SELECT *
4484: FROM pa_progress_rollup
4485: WHERE project_id = p_project_id
4486: AND object_type = 'PA_STRUCTURES'
4487: AND structure_type = 'WORKPLAN'
4488: AND structure_version_id is NULL
4489: AND as_of_date = p_as_of_date;
4490:
4491: CURSOR c_get_par_task_rollup(c_object_id NUMBER, c_project_id NUMBER) IS
4492: SELECT *
4493: FROM pa_progress_rollup
4494: WHERE object_id = c_object_id
4495: AND project_id = c_project_id
4496: AND object_type = 'PA_TASKS'
4497: AND structure_type = 'WORKPLAN'
4563: l_POU_OTH_RAW_COST NUMBER := null;
4564: l_PRJ_OTH_RAW_COST NUMBER := null;
4565: l_current_flag VARCHAR2(1);
4566: l_dummy VARCHAR2(1);
4567: l_parent_progress_status pa_progress_rollup.progress_status_code%TYPE;
4568: l_child_progress_status pa_progress_rollup.progress_status_code%TYPE;
4569: l_par_progress_status_weight pa_project_statuses.project_status_weight%TYPE;
4570: l_child_progress_status_weight pa_project_statuses.project_status_weight%TYPE;
4571: l_progress_status_weight pa_project_statuses.project_status_weight%TYPE;
4564: l_PRJ_OTH_RAW_COST NUMBER := null;
4565: l_current_flag VARCHAR2(1);
4566: l_dummy VARCHAR2(1);
4567: l_parent_progress_status pa_progress_rollup.progress_status_code%TYPE;
4568: l_child_progress_status pa_progress_rollup.progress_status_code%TYPE;
4569: l_par_progress_status_weight pa_project_statuses.project_status_weight%TYPE;
4570: l_child_progress_status_weight pa_project_statuses.project_status_weight%TYPE;
4571: l_progress_status_weight pa_project_statuses.project_status_weight%TYPE;
4572: l_eff_rollup_progress_status pa_progress_rollup.progress_status_code%TYPE;
4568: l_child_progress_status pa_progress_rollup.progress_status_code%TYPE;
4569: l_par_progress_status_weight pa_project_statuses.project_status_weight%TYPE;
4570: l_child_progress_status_weight pa_project_statuses.project_status_weight%TYPE;
4571: l_progress_status_weight pa_project_statuses.project_status_weight%TYPE;
4572: l_eff_rollup_progress_status pa_progress_rollup.progress_status_code%TYPE;
4573: l_actual_start_date DATE;
4574: l_actual_finish_date DATE;
4575: l_estimated_start_date DATE;
4576: l_estimated_finish_date DATE;
4576: l_estimated_finish_date DATE;
4577: --Added for bug 16230177
4578: cursor get_parent_max_asofdate(l_proj number, l_obj number) is
4579: select as_of_date
4580: from pa_progress_rollup
4581: where project_id = l_proj
4582: and object_id = l_obj
4583: and object_type = 'PA_TASKS'
4584: and structure_version_id is null
5112: IF l_mode = 'U' THEN
5113:
5114: l_parent_as_of_date := l_parent_task_rec.AS_OF_DATE; --bug 4033869
5115:
5116: PA_PROGRESS_ROLLUP_PKG.UPDATE_ROW(
5117: X_PROGRESS_ROLLUP_ID => l_parent_task_rec.PROGRESS_ROLLUP_ID
5118: ,X_PROJECT_ID => l_parent_task_rec.PROJECT_ID
5119: ,X_OBJECT_ID => l_parent_task_rec.OBJECT_ID
5120: ,X_OBJECT_TYPE => l_parent_task_rec.OBJECT_TYPE
5245: ELSE -- IF l_mode = 'U' THEN
5246: BEGIN
5247: SELECT 'X'
5248: INTO l_dummy
5249: FROM pa_progress_rollup
5250: WHERE project_id = l_c1rec.parent_project_id
5251: AND object_id = l_c1rec.parent_task_id
5252: AND object_type = 'PA_TASKS'
5253: AND structure_type = 'WORKPLAN'
5259: EXCEPTION
5260: WHEN NO_DATA_FOUND THEN
5261: l_current_flag := 'Y';
5262:
5263: UPDATE pa_progress_rollup
5264: SET current_flag = 'N'
5265: WHERE project_id = l_c1rec.parent_project_id
5266: AND object_id = l_c1rec.parent_task_id
5267: AND object_type = 'PA_TASKS'
5275: p_project_id => l_c1rec.parent_project_id
5276: ,p_task_id => l_c1rec.parent_task_id
5277: ,p_start_date => p_as_of_date);
5278: --End bug 4033869
5279: PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
5280: X_PROGRESS_ROLLUP_ID => l_progress_rollup_id
5281: ,X_PROJECT_ID => l_c1rec.parent_project_id
5282: ,X_OBJECT_ID => l_c1rec.parent_task_id
5283: ,X_OBJECT_TYPE => 'PA_TASKS'
5716: -- 4533112 : Added base_progress_status_code
5717: , decode(ppr.base_progress_status_code, 'Y' , 'Y', 'N') base_progress_status_code
5718: FROM pa_proj_element_versions ppev,
5719: pa_object_relationships obj,
5720: pa_progress_rollup ppr,
5721: pa_project_statuses pps1,
5722: pa_project_statuses pps2,
5723: pa_project_statuses pps3,
5724: pa_proj_elements ppe -- 4392189
5734: -- AND ppr.as_of_date = trunc(p_as_of_date)
5735: -- Bug 3879461 : After discussion with Sameer Realeraskar, Majid
5736: -- It is to select only the rollup record till passed as of date
5737: AND ppr.as_of_date = (SELECT max(as_of_date)
5738: FROM pa_progress_rollup
5739: WHERE project_id = p_project_id
5740: AND object_id = ppev.proj_element_id
5741: AND proj_element_id = ppev.proj_element_id
5742: AND object_type = 'PA_TASKS'
5746: )
5747: AND (ppr.current_flag = 'W'
5748: OR (ppr.current_flag IN( 'Y', 'N') -- Bug 4336720 : Added Y and N Both
5749: AND NOT EXISTS (select 1
5750: from pa_progress_rollup ppc1
5751: where ppc1.project_id = p_project_id
5752: and ppc1.object_id = ppev.proj_element_id
5753: and ppc1.proj_element_id = ppev.proj_element_id
5754: and ppc1.object_Type = 'PA_TASKS'
5887: WHERE ppev.element_version_id = c_task_ver_id
5888: AND ppev.object_type = 'PA_TASKS'
5889: -- Bug 3879461 Begin
5890: AND NOT EXISTS (SELECT 1
5891: FROM pa_progress_rollup
5892: WHERE project_id = p_project_id
5893: AND object_id = ppev.proj_element_id
5894: AND proj_element_id = ppev.proj_element_id
5895: AND object_type = 'PA_TASKS'
6021: , decode(ppr.base_progress_status_code,'Y','Y','N') base_progress_status_code
6022: FROM pa_proj_element_versions ppev1,
6023: pa_proj_element_versions ppev2,
6024: pa_object_relationships obj,
6025: pa_progress_rollup ppr,
6026: pa_project_statuses pps1,
6027: pa_project_statuses pps2,
6028: pa_project_statuses pps3,
6029: pa_proj_elements ppe -- 4392189
6038: AND ppr.project_id = ppev2.project_id
6039: AND ppr.object_id = ppev2.proj_element_id
6040: AND ppr.proj_element_id = ppev2.proj_element_id
6041: AND ppr.as_of_date = (SELECT max(as_of_date)
6042: FROM pa_progress_rollup
6043: WHERE project_id = p_project_id
6044: AND object_id = ppev2.proj_element_id
6045: AND proj_element_id = ppev2.proj_element_id
6046: AND object_type = 'PA_TASKS'
6050: )
6051: AND (ppr.current_flag = 'W'
6052: OR (ppr.current_flag IN( 'Y', 'N') -- Bug 4336720 : Added Y and N Both
6053: AND NOT EXISTS (select 1
6054: from pa_progress_rollup ppc1
6055: where ppc1.project_id = p_project_id
6056: and ppc1.object_id = ppev2.proj_element_id
6057: and ppc1.proj_element_id = ppev2.proj_element_id
6058: and ppc1.object_Type = 'PA_TASKS'
6205: AND pfxat.calendar_type(+) = 'A'
6206: AND pfxat.plan_version_id(+) > 0
6207: AND pfxat.txn_currency_code(+) is null
6208: AND NOT EXISTS (SELECT 1
6209: FROM pa_progress_rollup
6210: WHERE project_id = p_project_id
6211: AND object_id = ppev2.proj_element_id
6212: AND proj_element_id = ppev2.proj_element_id
6213: AND object_type = 'PA_TASKS'
6436: , ppr.Oth_quantity_to_date
6437: , ppr.Oth_etc_quantity
6438: FROM
6439: pa_task_asgmts_v asgn,
6440: pa_progress_rollup ppr
6441: WHERE asgn.task_version_id = c_task_ver_id
6442: AND asgn.task_id = c_task_id
6443: AND asgn.project_id = p_project_id
6444: AND asgn.project_id = ppr.project_id(+)
6452: AND ppr.as_of_date(+) <= p_as_of_date
6453: 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);
6454:
6455: /* (SELECT nvl(max(progress_rollup_id),-99)
6456: FROM pa_progress_rollup
6457: WHERE project_id = p_project_id
6458: AND object_id = asgn.RESOURCE_LIST_MEMBER_ID
6459: AND proj_element_id = asgn.task_id
6460: AND object_type = 'PA_ASSIGNMENTS'
6464: AND (ppr.current_flag = 'W'
6465: --OR (ppr.current_flag ='Y' --bug 4183176
6466: OR (ppr.current_flag IN ('Y', 'N')
6467: AND NOT EXISTS (select 1
6468: from pa_progress_rollup ppc1
6469: where ppc1.project_id = p_project_id
6470: and ppc1.object_id = asgn.RESOURCE_LIST_MEMBER_ID
6471: and ppc1.proj_element_id = asgn.task_id
6472: and ppc1.object_Type = 'PA_ASSIGNMENTS'
6502: , pps2.project_status_weight override_weight ---override progress status code
6503: , pps3.project_status_weight base_weight --base prog status
6504: FROM pa_proj_elements elem
6505: , pa_object_relationships obj
6506: , pa_progress_rollup ppr
6507: , pa_project_statuses pps2
6508: , pa_project_statuses pps3
6509: WHERE obj.object_id_from2= c_task_proj_elem_id
6510: ---AND obj.object_id_from1 = c_task_ver_id ---to get delv for specific task ver , object_id_from1 is not populated in this case
6525: AND ppr.base_progress_status_code = pps3.project_status_code(+)
6526: AND ppr.progress_status_code = pps2.project_status_code(+);
6527:
6528: /* (SELECT nvl(max(progress_rollup_id),-99)
6529: FROM pa_progress_rollup
6530: WHERE project_id = p_project_id
6531: AND object_id = obj.object_id_to2
6532: --bug 4250623, do not check for proj_element_id as deliverable may not be associated with task
6533: --AND proj_element_id = obj.object_id_from2
6538: --bug 4182870, added for selecting only one record
6539: AND (ppr.current_flag = 'W'
6540: OR (ppr.current_flag IN ('Y', 'N')
6541: AND NOT EXISTS (select 1
6542: from pa_progress_rollup ppc1
6543: where ppc1.project_id = p_project_id
6544: and ppc1.object_id = obj.object_id_to2
6545: --bug 4250623, do not check for proj_element_id as deliverable may not be associated with task
6546: --and ppc1.proj_element_id = obj.object_id_from2
6588: and predefined_flag = 'Y';
6589:
6590: CURSOR cur_rollup( c_progress_rollup_id NUMBER )
6591: IS
6592: SELECT * from pa_progress_rollup
6593: WHERE progress_rollup_id = c_progress_rollup_id;
6594:
6595: CURSOR cur_pa_rollup1( c_proj_element_id NUMBER )
6596: IS
6594:
6595: CURSOR cur_pa_rollup1( c_proj_element_id NUMBER )
6596: IS
6597: SELECT *
6598: FROM pa_progress_rollup
6599: WHERE project_id = p_project_id
6600: AND object_id = c_proj_element_id
6601: AND structure_type = 'WORKPLAN'
6602: AND structure_version_id is null
6600: AND object_id = c_proj_element_id
6601: AND structure_type = 'WORKPLAN'
6602: AND structure_version_id is null
6603: AND as_of_date = ( SELECT max( as_of_date )
6604: FROM pa_progress_rollup
6605: WHERE project_id = p_project_id
6606: AND object_id = c_proj_element_id
6607: AND structure_type = 'WORKPLAN'
6608: AND structure_version_id is null
6841: , decode(nvl(ppr.OTH_ACT_COST_TO_DATE_PC,0) + nvl(ppr.OTH_ETC_COST_PC,0) + nvl(ppr.PPL_ACT_COST_TO_DATE_PC,0) + nvl(ppr.PPL_ETC_COST_PC,0) + nvl(ppr.EQPMT_ACT_COST_TO_DATE_PC,0) + nvl(ppr.EQPMT_ETC_COST_PC,0),0,
6842: nvl(pfxat.ETC_PRJ_BRDN_COST,0), nvl(ppr.OTH_ACT_COST_TO_DATE_PC,0) + nvl(ppr.OTH_ETC_COST_PC,0) + nvl(ppr.PPL_ACT_COST_TO_DATE_PC,0) + nvl(ppr.PPL_ETC_COST_PC,0) + nvl(ppr.EQPMT_ACT_COST_TO_DATE_PC,0) + nvl(ppr.EQPMT_ETC_COST_PC,0))) bac_value
6843: , nvl(ppr.completed_percentage, ppr.eff_rollup_percent_comp) completed_percentage -- Bug 4506009
6844: FROM
6845: pa_progress_rollup ppr
6846: ,pa_project_statuses pps1
6847: ,pa_project_statuses pps2
6848: ,pa_project_statuses pps3
6849: ,pa_project_statuses pps4
8585:
8586:
8587: /* if etc values are null, set them as planned - actual. */
8588:
8589: PA_PROGRESS_ROLLUP_PKG.UPDATE_ROW(
8590: X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
8591: ,X_PROJECT_ID => p_project_id
8592: ,X_OBJECT_ID => p_task_id
8593: ,X_OBJECT_TYPE => l_rollup_table1(i).object_type
8782: -- Bug 3879461 End
8783:
8784: -- This code is not required if we do not do partail rollup from assignmnet progress details page
8785:
8786: PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
8787: X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
8788: ,X_PROJECT_ID => p_project_id
8789: ,X_OBJECT_ID => p_task_id
8790: ,X_OBJECT_TYPE => l_rollup_table1(i).object_type
8913: /*
8914: --update all previous record current flag to 'N'
8915: IF l_max_rollup_as_of_date2 < p_as_of_date --creating a new progress record.
8916: THEN
8917: UPDATE pa_progress_rollup
8918: SET current_flag = 'N'
8919: WHERE progress_rollup_id <> l_progress_rollup_id
8920: AND project_id = p_project_id
8921: AND object_id = p_task_id
9089: /*
9090: CURSOR cur_chk_asgn
9091: IS
9092: SELECT 'x'
9093: FROM pa_progress_rollup
9094: WHERE object_id = p_resource_list_mem_id
9095: and structure_version_id = p_structure_version_id
9096: and proj_element_id = p_task_id
9097: and structure_type = 'WORKPLAN'
9235: and published_flag = 'N'
9236: and structure_type = 'WORKPLAN'
9237: ;
9238:
9239: delete from pa_progress_rollup
9240: where project_id= p_project_id
9241: and proj_element_id = p_task_id
9242: and object_id = p_resource_list_mem_id
9243: and current_flag = 'W'
9268: and object_type = 'PA_ASSIGNMENTS'
9269: and published_flag = 'Y';
9270:
9271:
9272: delete from pa_progress_rollup
9273: where project_id= p_project_id
9274: and proj_element_id = p_task_id
9275: and object_id = p_resource_list_mem_id
9276: and object_type = 'PA_ASSIGNMENTS'
9279: and structure_type = 'WORKPLAN'
9280: and structure_version_id is null
9281: ;
9282:
9283: delete from pa_progress_rollup
9284: WHERE project_id = p_project_id
9285: and object_id = p_resource_list_mem_id
9286: and structure_version_id is null
9287: and proj_element_id = p_task_id
9308: AND task_id = p_task_id
9309: AND current_flag = 'Y'
9310: AND object_type = 'PA_ASSIGNMENTS';
9311:
9312: UPDATE pa_progress_rollup
9313: SET current_flag = 'N'
9314: WHERE project_id = p_project_id
9315: AND object_id = p_resource_list_mem_id
9316: AND proj_element_id = p_task_id
9379: select PA_PERCENT_COMPLETES_S.nextval
9380: into x_percent_complete_id
9381: from dual;
9382:
9383: select PA_PROGRESS_ROLLUP_S.nextval
9384: into X_PROGRESS_ROLLUP_ID
9385: from dual;
9386: IF g1_debug_mode = 'Y' THEN
9387: pa_debug.write(x_Module=>'PA_PROGRESS_PVT.convert_task_prog_to_assgn', x_Msg => 'Before Inserting into pa_percent_completes', x_Log_Level=> 3);
9493: pa_debug.write(x_Module=>'PA_PROGRESS_PVT.convert_task_prog_to_assgn', x_Msg => 'Done Inserting into pa_percent_completes', x_Log_Level=> 3);
9494: END IF;
9495: -- Bug 4490532 : Subtracting the subproject amounts if available.
9496: -- Bug 4661350 : Using newly introduced local variable to store subprojects sum value
9497: INSERT INTO pa_progress_rollup(
9498: PROGRESS_ROLLUP_ID
9499: ,PERCENT_COMPLETE_ID
9500: ,PROJECT_ID
9501: ,OBJECT_ID
9662: ,EQPMT_ACT_RAWCOST_TO_DATE_PC- nvl(sp_EQPMT_ACT_RAWCOST_TODATE_PC,0)
9663: ,EQPMT_ETC_RAWCOST_TC
9664: ,EQPMT_ETC_RAWCOST_FC- nvl(sp_EQPMT_ETC_RAWCOST_FC,0)
9665: ,EQPMT_ETC_RAWCOST_PC- nvl(sp_EQPMT_ETC_RAWCOST_PC,0)
9666: FROM pa_progress_rollup
9667: WHERE project_id = p_project_id
9668: and object_id = p_task_id
9669: and structure_version_id is NULL
9670: and proj_element_id = p_task_id
9675: or (p_action = 'PUBLISH' and current_flag in ('Y', 'N')));
9676:
9677:
9678: IF g1_debug_mode = 'Y' THEN
9679: pa_debug.write(x_Module=>'PA_PROGRESS_PVT.convert_task_prog_to_assgn', x_Msg => 'Done Inserting into pa_progress_rollup', x_Log_Level=> 3);
9680: END IF;
9681:
9682: -- END IF;
9683:
9821: /* Begin code to fix Bug # 4141850. */
9822:
9823: cursor cur_as_of_date(p_project_id NUMBER) is
9824: select max(as_of_date)
9825: from pa_progress_rollup ppr
9826: where ppr.project_id = p_project_id
9827: and ppr.structure_version_id is null
9828: and ppr.structure_type = 'WORKPLAN'
9829: and ppr.current_flag <> 'W';
10261: FROM pa_proj_element_versions a,
10262: pa_proj_elem_ver_structure b,
10263: pa_proj_structure_types ppst,
10264: pa_proj_element_versions c,
10265: pa_progress_rollup ppr
10266: WHERE
10267: c.project_id = p_project_id
10268: AND c.element_version_id = p_structure_version_id ---5095632
10269: AND c.prg_group = a.prg_group
10293: FROM pa_proj_element_versions a,
10294: pa_proj_elem_ver_structure b,
10295: pa_proj_structure_types ppst,
10296: pa_proj_element_versions c,
10297: pa_progress_rollup ppr
10298: WHERE
10299: c.project_id = p_project_id
10300: AND c.element_version_id = p_structure_version_id -- Backported performance fix from R12 bug 5095632
10301: AND c.prg_group = a.prg_group
10518: and task_id = l_proj_element_id_tab(i)
10519: and structure_type = 'WORKPLAN'
10520: and object_version_id = l_old_element_version_id_tab(i);
10521:
10522: --- update pa_progress_rollup table with new object_version_ids
10523: Forall i in l_new_element_version_id_tab.first..l_new_element_version_id_tab.last
10524: update pa_progress_rollup ppr
10525: set object_version_id = l_new_element_version_id_tab(i)
10526: where project_id = p_project_id
10520: and object_version_id = l_old_element_version_id_tab(i);
10521:
10522: --- update pa_progress_rollup table with new object_version_ids
10523: Forall i in l_new_element_version_id_tab.first..l_new_element_version_id_tab.last
10524: update pa_progress_rollup ppr
10525: set object_version_id = l_new_element_version_id_tab(i)
10526: where project_id = p_project_id
10527: and proj_element_id = l_proj_element_id_tab(i)
10528: and structure_type = 'WORKPLAN'
10982: ,'N' create_required
10983: ,'N' update_required
10984: , ppr.base_percent_complete -- 4392189 : Program Reporting Changes - Phase 2
10985: FROM pa_proj_element_versions ppev,
10986: pa_progress_rollup ppr,
10987: pa_project_statuses pps1,
10988: pa_project_statuses pps2,
10989: pa_project_statuses pps3 ,
10990: pa_proj_rollup_temp temp
11106: FROM
11107: pa_object_relationships pobj,
11108: pa_proj_element_versions ppev1,
11109: pa_proj_element_versions ppev2,
11110: pa_progress_rollup ppr,
11111: pa_proj_elements ppe,
11112: pa_project_statuses pps1,
11113: pa_project_statuses pps2,
11114: pa_project_statuses pps3,
11404: ;
11405:
11406: CURSOR cur_get_asgn_progress(c_object_id NUMBER, c_proj_element_id NUMBER) IS
11407: SELECT ppr.*
11408: FROM pa_progress_rollup ppr
11409: WHERE ppr.project_id = p_project_id
11410: AND ppr.object_id = c_object_id
11411: AND ppr.proj_element_id = c_proj_element_id
11412: AND ppr.object_type = 'PA_ASSIGNMENTS'
11460: , pps2.project_status_weight override_weight ---override progress status code
11461: , pps3.project_status_weight base_weight --base prog status
11462: FROM pa_proj_elements elem
11463: , pa_object_relationships obj
11464: , pa_progress_rollup ppr
11465: , pa_project_statuses pps2
11466: , pa_project_statuses pps3
11467: WHERE obj.object_id_from2= c_task_proj_elem_id
11468: AND obj.object_type_from = 'PA_TASKS'
11551: IS
11552: select /*+ leading (ROLLUP) */ ppr.as_of_date, ver2.proj_element_id child_task_id, ver2.element_version_id child_task_ver_id
11553: from pa_object_relationships obj
11554: , pa_proj_element_versions ver
11555: , pa_progress_rollup ppr
11556: , pa_proj_rollup_temp rollup
11557: , pa_proj_element_versions ver2
11558: where rollup.object_id = obj.object_id_to1
11559: AND rollup.process_number = l_process_number_temp
11624: ,decode(c_task_per_comp_deriv_method,'EFFORT', nvl(ppr.PPL_ACT_EFFORT_TO_DATE,0) + nvl(ppr.estimated_remaining_effort,0) + nvl(ppr.EQPMT_ACT_EFFORT_TO_DATE,0) + nvl(ppr.EQPMT_ETC_EFFORT,0)
11625: , nvl(ppr.OTH_ACT_COST_TO_DATE_PC,0) + nvl(ppr.OTH_ETC_COST_PC,0) + nvl(ppr.PPL_ACT_COST_TO_DATE_PC,0) + nvl(ppr.PPL_ETC_COST_PC,0) + nvl(ppr.EQPMT_ACT_COST_TO_DATE_PC,0) + nvl(ppr.EQPMT_ETC_COST_PC,0)) bac_value
11626: , nvl(ppr.completed_percentage, ppr.eff_rollup_percent_comp) completed_percentage --Bug 4506009
11627: FROM
11628: pa_progress_rollup ppr
11629: ,pa_project_statuses pps1
11630: ,pa_project_statuses pps2
11631: ,pa_project_statuses pps3
11632: ,pa_project_statuses pps4
12352: AND l_asgn_progress_rec.actual_finish_date IS NOT NULL) THEN
12353: -- Update to happen only version is disabled or on a working version when version enabled
12354: IF (l_wp_version_enabled_flag = 'N'
12355: OR (l_wp_version_enabled_flag = 'Y'AND l_asgn_progress_rec.structure_version_id IS NOT NULL) ) THEN
12356: UPDATE PA_PROGRESS_ROLLUP
12357: SET ACTUAL_FINISH_DATE = NULL
12358: WHERE progress_rollup_id = l_asgn_progress_rec.progress_rollup_id;
12359: END IF;
12360: END IF; -- end check for
13523: -- One solution for this issue could be to pass
13524: -- p_upd_new_elem_ver_id_flag as 'N' from rollup API call in
13525: -- program_rollup_pvt, rollup_future_progress_pvt, and recursive call of Rollup API
13526: -- within Rollup API. This solution works for Case 1 and Case2, but not Case 3
13527: -- Best way is to always retain the existing object_version_id in pa_progress_rollup
13528: -- table in case of update, in case of insert, use the new object version id
13529:
13530: -- Commented below code and added new condition
13531:
13694: l_tsk_prog_pa_period_name_tab(task_index) := l_prog_pa_period_name;
13695: l_tsk_prog_gl_period_name_tab(task_index) := l_prog_gl_period_name;
13696:
13697: IF l_max_rollup_as_of_date2 < p_as_of_date THEN
13698: UPDATE pa_progress_rollup
13699: SET current_flag = 'N'
13700: WHERE project_id = p_project_id
13701: AND object_id = cur_reverse_tree_rec.proj_element_id
13702: AND object_type = l_rollup_table1(i).object_type
13712: END LOOP;
13713: END LOOP;
13714:
13715: FORALL i in 1..l_tsk_object_version_id_tab.count
13716: UPDATE pa_progress_rollup
13717: SET
13718: object_version_id = l_tsk_object_version_id_tab(i)
13719: , last_update_date = sysdate
13720: , last_updated_by = l_user_id
13767:
13768:
13769: IF p_structure_type = 'WORKPLAN' AND l_structure_version_id IS NULL AND p_progress_mode <> 'BACKDATED' THEN
13770: FORALL i in 1..l_tsk_object_version_id_tab.count
13771: UPDATE pa_progress_rollup
13772: SET
13773: object_version_id = l_tsk_object_version_id_tab(i)
13774: , last_update_date = sysdate
13775: , last_updated_by = l_user_id
13841: end if;
13842: end loop;
13843:
13844: FORALL i in 1..l_tsk_object_version_id_tab.count
13845: INSERT INTO pa_progress_rollup
13846: (
13847: progress_rollup_id
13848: ,project_id
13849: ,object_id
13914: ,prog_gl_period_name
13915: ,base_percent_complete
13916: )
13917: SELECT
13918: PA_PROGRESS_ROLLUP_S.nextval
13919: , p_project_id
13920: , l_tsk_object_id_tab(i)
13921: , l_tsk_object_type_tab(i)
13922: , p_as_of_date
14542: ,'N' create_required
14543: ,'N' update_required
14544: , ppr.base_percent_complete -- 4392189 : Program Reporting Changes - Phase 2
14545: FROM pa_proj_element_versions ppev,
14546: pa_progress_rollup ppr,
14547: pa_project_statuses pps1,
14548: pa_project_statuses pps2,
14549: pa_project_statuses pps3 ,
14550: pa_proj_rollup_temp temp
14666: FROM
14667: pa_object_relationships pobj,
14668: pa_proj_element_versions ppev1,
14669: pa_proj_element_versions ppev2,
14670: pa_progress_rollup ppr,
14671: pa_proj_elements ppe,
14672: pa_project_statuses pps1,
14673: pa_project_statuses pps2,
14674: pa_project_statuses pps3,
14965: ;
14966:
14967: CURSOR cur_get_asgn_progress(c_object_id NUMBER, c_proj_element_id NUMBER) IS
14968: SELECT ppr.*
14969: FROM pa_progress_rollup ppr
14970: WHERE ppr.project_id = p_project_id
14971: AND ppr.object_id = c_object_id
14972: AND ppr.proj_element_id = c_proj_element_id
14973: AND ppr.object_type = 'PA_ASSIGNMENTS'
15021: , pps2.project_status_weight override_weight ---override progress status code
15022: , pps3.project_status_weight base_weight --base prog status
15023: FROM pa_proj_elements elem
15024: , pa_object_relationships obj
15025: , pa_progress_rollup ppr
15026: , pa_project_statuses pps2
15027: , pa_project_statuses pps3
15028: WHERE obj.object_id_from2= c_task_proj_elem_id
15029: AND obj.object_type_from = 'PA_TASKS'
15112: IS
15113: select /*+ leading (ROLLUP) */ ppr.as_of_date, ver2.proj_element_id child_task_id, ver2.element_version_id child_task_ver_id
15114: from pa_object_relationships obj
15115: , pa_proj_element_versions ver
15116: , pa_progress_rollup ppr
15117: , pa_proj_rollup_temp rollup
15118: , pa_proj_element_versions ver2
15119: where rollup.object_id = obj.object_id_to1
15120: AND rollup.process_number = l_process_number_temp
15185: ,decode(c_task_per_comp_deriv_method,'EFFORT', nvl(ppr.PPL_ACT_EFFORT_TO_DATE,0) + nvl(ppr.estimated_remaining_effort,0) + nvl(ppr.EQPMT_ACT_EFFORT_TO_DATE,0) + nvl(ppr.EQPMT_ETC_EFFORT,0)
15186: , nvl(ppr.OTH_ACT_COST_TO_DATE_PC,0) + nvl(ppr.OTH_ETC_COST_PC,0) + nvl(ppr.PPL_ACT_COST_TO_DATE_PC,0) + nvl(ppr.PPL_ETC_COST_PC,0) + nvl(ppr.EQPMT_ACT_COST_TO_DATE_PC,0) + nvl(ppr.EQPMT_ETC_COST_PC,0)) bac_value
15187: , nvl(ppr.completed_percentage, ppr.eff_rollup_percent_comp) completed_percentage --Bug 4506009
15188: FROM
15189: pa_progress_rollup ppr
15190: ,pa_project_statuses pps1
15191: ,pa_project_statuses pps2
15192: ,pa_project_statuses pps3
15193: ,pa_project_statuses pps4
15938: AND l_asgn_progress_rec.actual_finish_date IS NOT NULL) THEN
15939: -- Update to happen only version is disabled or on a working version when version enabled
15940: IF (l_wp_version_enabled_flag = 'N'
15941: OR (l_wp_version_enabled_flag = 'Y'AND l_asgn_progress_rec.structure_version_id IS NOT NULL) ) THEN
15942: UPDATE PA_PROGRESS_ROLLUP
15943: SET ACTUAL_FINISH_DATE = NULL
15944: WHERE progress_rollup_id = l_asgn_progress_rec.progress_rollup_id;
15945: END IF;
15946: END IF; -- end check for
17144: -- One solution for this issue could be to pass
17145: -- p_upd_new_elem_ver_id_flag as 'N' from rollup API call in
17146: -- program_rollup_pvt, rollup_future_progress_pvt, and recursive call of Rollup API
17147: -- within Rollup API. This solution works for Case 1 and Case2, but not Case 3
17148: -- Best way is to always retain the existing object_version_id in pa_progress_rollup
17149: -- table in case of update, in case of insert, use the new object version id
17150:
17151: -- Commented below code and added new condition
17152:
17315: l_tsk_prog_pa_period_name_tab(task_index) := l_prog_pa_period_name;
17316: l_tsk_prog_gl_period_name_tab(task_index) := l_prog_gl_period_name;
17317:
17318: IF l_max_rollup_as_of_date2 < p_as_of_date THEN
17319: UPDATE pa_progress_rollup
17320: SET current_flag = 'N'
17321: WHERE project_id = p_project_id
17322: /* -- sridhar_rollup
17323: AND object_id = cur_reverse_tree_rec.proj_element_id
17341:
17342: */
17343:
17344: FORALL i in 1..l_tsk_object_version_id_tab.count
17345: UPDATE pa_progress_rollup
17346: SET
17347: object_version_id = l_tsk_object_version_id_tab(i)
17348: , last_update_date = sysdate
17349: , last_updated_by = l_user_id
17396:
17397:
17398: IF p_structure_type = 'WORKPLAN' AND l_structure_version_id IS NULL AND p_progress_mode <> 'BACKDATED' THEN
17399: FORALL i in 1..l_tsk_object_version_id_tab.count
17400: UPDATE pa_progress_rollup
17401: SET
17402: object_version_id = l_tsk_object_version_id_tab(i)
17403: , last_update_date = sysdate
17404: , last_updated_by = l_user_id
17473: end if; -- sridhar_fix
17474: end loop;
17475:
17476: FORALL i in 1..l_tsk_object_version_id_tab.count
17477: INSERT INTO pa_progress_rollup
17478: (
17479: progress_rollup_id
17480: ,project_id
17481: ,object_id
17546: ,prog_gl_period_name
17547: ,base_percent_complete
17548: )
17549: SELECT
17550: PA_PROGRESS_ROLLUP_S.nextval
17551: , p_project_id
17552: , l_tsk_object_id_tab(i)
17553: , l_tsk_object_type_tab(i)
17554: , p_as_of_date