[Home] [Help]
282: -- Bug 3764224 : No need to have proj element id join in these cursors as it is used only in tasks context
283: CURSOR cur_pa_rollup1
284: IS
285: SELECT *
286: FROM pa_progress_rollup
287: WHERE project_id = p_project_id
288: AND object_id = p_object_id
289: AND structure_type = p_structure_type -- FPM Dev CR 3
290: AND structure_version_id IS NULL -- Bug 3764224
288: AND object_id = p_object_id
289: AND structure_type = p_structure_type -- FPM Dev CR 3
290: AND structure_version_id IS NULL -- Bug 3764224
291: AND as_of_date = ( SELECT max( as_of_date )
292: FROM pa_progress_rollup
293: WHERE project_id = p_project_id
294: AND object_id = p_object_id
295: AND object_type = p_object_type
296: AND structure_type = p_structure_type -- FPM Dev CR 3
300:
301: CURSOR cur_pa_rollup2
302: IS
303: SELECT *
304: FROM pa_progress_rollup
305: WHERE project_id = p_project_id
306: AND object_id = p_object_id
307: AND object_type = p_object_type
308: AND as_of_date = p_as_of_date
406: --bug 3879461 --maansari
407: CURSOR check_prog_after_aod
408: IS
409: SELECT 'Y'
410: FROM pa_progress_rollup
411: WHERE current_flag = 'Y'
412: AND project_id = p_project_id
413: AND structure_type = p_structure_type
414: AND structure_version_id is null
463: ppc.progress_comment,
464: ppc.description progress_overview
465: from pa_task_asgmts_v asgn
466: , pa_percent_completes ppc
467: , pa_progress_rollup ppr
468: where
469: ppc.PROJECT_ID = l_project_id
470: AND ppr.project_id = ppc.project_id
471: AND ppc.project_id = asgn.project_id
506: ppr.actual_finish_date,
507: ppc.record_version_number percomp_record_version_number,
508: ppc.status_code del_status_code
509: FROM pa_percent_completes ppc
510: , pa_progress_rollup ppr
511: WHERE
512: ppc.project_id=l_project_id
513: AND ppc.project_id = ppr.project_id
514: AND ppc.object_type = 'PA_DELIVERABLES'
568: , estimated_start_date
569: , estimated_finish_date
570: , actual_start_date
571: , actual_finish_date
572: FROM pa_progress_rollup
573: WHERE project_id = p_project_id
574: AND object_id = p_object_id
575: AND object_type = 'PA_TASKS'
576: AND structure_type = 'WORKPLAN'
596: -- Added cursor c_get_last_ppl_effort_fr_assgn
597: CURSOR c_get_last_ppl_effort_fr_assgn(c_res_list_mem_id NUMBER)
598: IS
599: SELECT NVL( PPL_ACT_EFFORT_TO_DATE, 0 )
600: FROM pa_progress_rollup
601: WHERE project_id = p_project_id
602: AND object_id = c_res_list_mem_id
603: AND as_of_date =
604: (SELECT max(as_of_date)
601: WHERE project_id = p_project_id
602: AND object_id = c_res_list_mem_id
603: AND as_of_date =
604: (SELECT max(as_of_date)
605: FROM pa_progress_rollup ppr2
606: WHERE ppr2.as_of_date <= p_as_of_date
607: AND ppr2.project_id = p_project_id
608: AND ppr2.object_id = c_res_list_mem_id
609: AND ppr2.object_type = 'PA_ASSIGNMENTS'
868: --AND date_computed <> p_as_of_date --bug 4185364, delete all the working records
869: AND structure_type = p_structure_type
870: ;
871:
872: DELETE FROM pa_progress_rollup
873: WHERE project_id= p_project_id
874: AND object_id = p_object_id
875: AND current_flag = 'W'
876: --AND as_of_date <> p_as_of_date --bug 4185364, delete all the working records
889: AND date_computed <= p_as_of_date --- 4245168
890: AND structure_type = p_structure_type
891: ;
892:
893: DELETE FROM pa_progress_rollup
894: WHERE project_id= p_project_id
895: AND object_id = p_object_id
896: AND current_flag = 'W'
897: AND as_of_date < p_as_of_date
2366: AND structure_type = p_structure_type; --Satish
2367:
2368: -- Bug 3879461 Begin
2369: IF NVL(l_prog_exist_after_aod, 'N') = 'N' THEN
2370: UPDATE pa_progress_rollup
2371: SET current_flag = 'N'
2372: WHERE project_id = p_project_id
2373: AND object_id = p_object_id
2374: AND proj_element_id = p_object_id
2392: --bug 4247839, removed the l_db_Action check as it should get deleted for CREATE also
2393: --IF l_db_action = 'UPDATE' AND p_progress_mode <> 'BACKDATED' THEN
2394: IF p_progress_mode <> 'BACKDATED' THEN
2395:
2396: Delete from pa_progress_rollup
2397: where project_id = p_project_id
2398: and object_id = p_object_id
2399: and proj_element_id = p_object_id
2400: and object_type = p_object_type
2609: END IF;
2610: -- Bug 4506461 End
2611:
2612: IF g1_debug_mode = 'Y' THEN
2613: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_PROGRESS', x_Msg => 'Inserting in PA_PROGRESS_ROLLUP ', x_Log_Level=> 3);
2614: END IF;
2615:
2616:
2617: PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
2613: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_PROGRESS', x_Msg => 'Inserting in PA_PROGRESS_ROLLUP ', x_Log_Level=> 3);
2614: END IF;
2615:
2616:
2617: PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
2618: x_progress_rollup_id => l_progress_rollup_id
2619: ,x_project_id => p_project_id
2620: ,x_object_id => p_object_id
2621: ,x_object_type => p_object_type
2744:
2745: -- Progress Management Changes. FP-M Bug # 3420093. Begin:Set CURRENT_FLAG of previous rollup records to 'N'.
2746:
2747: IF g1_debug_mode = 'Y' THEN
2748: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_PROGRESS', x_Msg => 'Inserted in PA_PROGRESS_ROLLUP ', x_Log_Level=> 3);
2749: END IF;
2750:
2751: ELSE --1
2752: --update progress rollup
2811: END IF;
2812: -- Bug 4506461 End
2813:
2814:
2815: PA_PROGRESS_ROLLUP_PKG.UPDATE_ROW(
2816: x_progress_rollup_id => l_progress_rollup_id
2817: ,x_project_id => p_project_id
2818: ,x_object_id => p_object_id
2819: ,x_object_type => p_object_type
2942: IF Fnd_Msg_Pub.count_msg > 0 THEN
2943: RAISE FND_API.G_EXC_ERROR;
2944: END IF;
2945:
2946: -----update percent_complete_id in pa_progress_rollup
2947: UPDATE pa_progress_rollup
2948: SET percent_complete_id = l_percent_complete_id
2949: WHERE object_id = p_object_id
2950: AND project_id = p_project_id
2943: RAISE FND_API.G_EXC_ERROR;
2944: END IF;
2945:
2946: -----update percent_complete_id in pa_progress_rollup
2947: UPDATE pa_progress_rollup
2948: SET percent_complete_id = l_percent_complete_id
2949: WHERE object_id = p_object_id
2950: AND project_id = p_project_id
2951: AND as_of_date > p_as_of_date
3135: END IF;
3136: -- Bug 4506461 End
3137:
3138: IF g1_debug_mode = 'Y' THEN
3139: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_PROGRESS', x_Msg => 'Update Action: Updating PA_PROGRESS_ROLLUP ', x_Log_Level=> 3);
3140: END IF;
3141:
3142: pa_progress_rollup_pkg.update_row(
3143: x_progress_rollup_id => l_progress_rollup_id
3138: IF g1_debug_mode = 'Y' THEN
3139: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_PROGRESS', x_Msg => 'Update Action: Updating PA_PROGRESS_ROLLUP ', x_Log_Level=> 3);
3140: END IF;
3141:
3142: pa_progress_rollup_pkg.update_row(
3143: x_progress_rollup_id => l_progress_rollup_id
3144: ,x_project_id => p_project_id
3145: ,x_object_id => p_object_id
3146: ,x_object_type => p_object_type
3266: ,x_subprj_eqpmt_etc_rawcost_pc => l_rollup_rec2.subprj_eqpmt_etc_rawcost_pc
3267: );
3268:
3269: IF g1_debug_mode = 'Y' THEN
3270: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_PROGRESS', x_Msg => 'Update Action: Updated PA_PROGRESS_ROLLUP ', x_Log_Level=> 3);
3271: END IF;
3272:
3273: -- FPM Dev CR 6
3274: IF Fnd_Msg_Pub.count_msg > 0 THEN
3364: -- Bug 4506461 End
3365:
3366:
3367: -- changed this for effort to cost conversion
3368: PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
3369: x_progress_rollup_id => l_progress_rollup_id
3370: ,x_project_id => p_project_id
3371: ,x_object_id => p_object_id
3372: ,x_object_type => p_object_type
3616: IF p_object_type = 'PA_TASKS'
3617: AND l_element_struc_type = 'WORKPLAN' AND p_progress_mode <> 'BACKDATED'
3618: THEN
3619: --- update % complete and prog stat for future rollup recs
3620: update pa_progress_rollup
3621: set completed_percentage = l_percent_complete,
3622: progress_status_code = p_progress_status_code
3623: where project_id = p_project_id
3624: and object_id = p_object_id
4992:
4993: CURSOR cur_pa_ppr_str( c_project_id NUMBER, c_proj_element_id NUMBER )
4994: IS
4995: SELECT rowid
4996: FROM pa_progress_rollup
4997: WHERE object_version_id = p_structure_version_id
4998: AND object_id = c_proj_element_id
4999: AND project_id = c_project_id
5000: AND object_type = 'PA_STRUCTURES';
5010:
5011: CURSOR cur_pa_ppr_tsk ( c_project_id NUMBER, c_proj_element_id NUMBER )
5012: IS
5013: SELECT rowid
5014: FROM pa_progress_rollup
5015: WHERE object_version_id = p_task_version_id
5016: AND object_id = c_proj_element_id
5017: AND project_id = c_project_id
5018: AND object_type = 'PA_TASKS';
5082: CLOSE cur_pa_ppr_tsk;
5083: END IF;
5084:
5085: PA_PERCENT_COMPLETES_PKG.DELETE_ROW( l_ppc_row_id );
5086: PA_PROGRESS_ROLLUP_PKG.DELETE_ROW( l_rollup_row_id );
5087:
5088: EXCEPTION
5089: when FND_API.G_EXC_ERROR then
5090: if p_commit = FND_API.G_TRUE then
5204: , ppr.OTH_ACT_RAWCOST_TO_DATE_TC, ppr.OTH_ACT_RAWCOST_TO_DATE_PC, ppr.OTH_ACT_RAWCOST_TO_DATE_FC
5205: , ppr.as_of_date
5206: , ptav.assignment_start_date
5207: FROM pa_task_asgmts_V ptav
5208: , pa_progress_rollup ppr
5209: WHERE ptav.task_version_id = c_task_version_id
5210: -- AND ptav.structure_version_id = l_structure_version_id
5211: AND ptav.task_id = c_task_id
5212: AND ptav.project_id = p_project_id
5271: , ppr.OTH_ACT_RAWCOST_TO_DATE_TC, ppr.OTH_ACT_RAWCOST_TO_DATE_PC, ppr.OTH_ACT_RAWCOST_TO_DATE_FC
5272: , ppr.as_of_date
5273: , ptav.assignment_start_date
5274: FROM pa_task_asgmts_V ptav
5275: , pa_progress_rollup ppr
5276: WHERE ptav.task_version_id = c_task_version_id
5277: -- AND ptav.structure_version_id = l_structure_version_id
5278: AND ptav.task_id = c_task_id
5279: AND ptav.project_id = p_project_id
5289: AND ppr.structure_version_id(+) is null
5290: ;
5291:
5292: L_PROGRESS_ROLLUP_ID NUMBER;
5293: L_PROG_PA_PERIOD_NAME pa_progress_rollup.prog_pa_period_name%TYPE;
5294: L_PROG_GL_PERIOD_NAME pa_progress_rollup.prog_gl_period_name%TYPE;
5295: l_user_id NUMBER:= fnd_global.user_id;
5296: l_login_id NUMBER:= fnd_global.login_id;
5297: -- Bug 3878024 End
5290: ;
5291:
5292: L_PROGRESS_ROLLUP_ID NUMBER;
5293: L_PROG_PA_PERIOD_NAME pa_progress_rollup.prog_pa_period_name%TYPE;
5294: L_PROG_GL_PERIOD_NAME pa_progress_rollup.prog_gl_period_name%TYPE;
5295: l_user_id NUMBER:= fnd_global.user_id;
5296: l_login_id NUMBER:= fnd_global.login_id;
5297: -- Bug 3878024 End
5298:
5394: AND date_computed = p_as_of_date
5395: AND structure_type = p_structure_type
5396: ;
5397:
5398: --Update pa_progress_rollup
5399: UPDATE pa_progress_rollup
5400: SET completed_percentage = 100,
5401: Actual_Finish_date = p_actual_finish_date,
5402: EQPMT_ETC_EFFORT = 0,
5395: AND structure_type = p_structure_type
5396: ;
5397:
5398: --Update pa_progress_rollup
5399: UPDATE pa_progress_rollup
5400: SET completed_percentage = 100,
5401: Actual_Finish_date = p_actual_finish_date,
5402: EQPMT_ETC_EFFORT = 0,
5403: OTH_ETC_COST_TC = 0,
5583: l_PROGRESS_ROLLUP_ID := null;
5584: l_prog_pa_period_name := PA_PROGRESS_UTILS.Prog_Get_Pa_Period_Name(l_push_as_of_date);
5585: l_prog_gl_period_name := PA_PROGRESS_UTILS.Prog_Get_GL_Period_Name(l_push_as_of_date);
5586:
5587: PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
5588: X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
5589: ,X_PROJECT_ID => p_project_id
5590: ,X_OBJECT_ID => cur_task_assignments_rec.resource_list_member_id
5591: ,X_OBJECT_TYPE => 'PA_ASSIGNMENTS'
5785: AND ((current_flag = 'Y' and published_flag = 'Y') or
5786: (current_flag = 'N' and published_flag = 'N'))
5787: ;
5788:
5789: --Update pa_progress_rollup
5790: UPDATE pa_progress_rollup
5791: SET actual_start_date = decode(actual_start_date, null, cur_task_assignments_rec.assignment_start_date, actual_start_date),
5792: Actual_Finish_date = p_actual_finish_date,
5793: estimated_remaining_effort = 0,
5786: (current_flag = 'N' and published_flag = 'N'))
5787: ;
5788:
5789: --Update pa_progress_rollup
5790: UPDATE pa_progress_rollup
5791: SET actual_start_date = decode(actual_start_date, null, cur_task_assignments_rec.assignment_start_date, actual_start_date),
5792: Actual_Finish_date = p_actual_finish_date,
5793: estimated_remaining_effort = 0,
5794: EQPMT_ETC_EFFORT = 0,
5904: l_PROGRESS_ROLLUP_ID := null;
5905: l_prog_pa_period_name := PA_PROGRESS_UTILS.Prog_Get_Pa_Period_Name(l_push_as_of_date);
5906: l_prog_gl_period_name := PA_PROGRESS_UTILS.Prog_Get_GL_Period_Name(l_push_as_of_date);
5907:
5908: PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
5909: X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
5910: ,X_PROJECT_ID => p_project_id
5911: ,X_OBJECT_ID => cur_task_assignments_rec.proj_element_id
5912: ,X_OBJECT_TYPE => 'PA_TASKS'
6038: -- Bug 3922325 : Hidden Assignment record also shd get created
6039:
6040: l_PROGRESS_ROLLUP_ID := null;
6041:
6042: PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
6043: X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
6044: ,X_PROJECT_ID => p_project_id
6045: ,X_OBJECT_ID => cur_task_assignments_rec.resource_list_member_id
6046: ,X_OBJECT_TYPE => 'PA_ASSIGNMENTS'
6229: );
6230: --END IF;-- PA_PROGRESS_UTILS.get_system_task_status(l_old_status_code) <> 'COMPLETED' THEN
6231: END IF; -- IF cur_task_assignments_rec.current_flag = 'Y' OR cur_task_assignments_rec.current_flag = 'X' THEN
6232: -- Bug 3922325 : Hidden Assignment records also shd be updated
6233: UPDATE pa_progress_rollup
6234: SET actual_start_date = decode(actual_start_date, null, cur_task_assignments_rec.assignment_start_date, actual_start_date),
6235: Actual_Finish_date = p_actual_finish_date,
6236: estimated_remaining_effort = 0,
6237: EQPMT_ETC_EFFORT = 0,
6292: AND PA_PROGRESS_UTILS.get_system_task_status( status_code )
6293: NOT IN ( 'CANCELLED', 'COMPLETED' )
6294: ;
6295:
6296: --Update pa_progress_rollup
6297: UPDATE pa_progress_rollup
6298: SET Actual_Finish_date = p_actual_finish_date,
6299: actual_start_date = decode(actual_start_date, null, cur_tasks_rec.scheduled_start_date, actual_start_date),
6300: completed_percentage = 100,
6293: NOT IN ( 'CANCELLED', 'COMPLETED' )
6294: ;
6295:
6296: --Update pa_progress_rollup
6297: UPDATE pa_progress_rollup
6298: SET Actual_Finish_date = p_actual_finish_date,
6299: actual_start_date = decode(actual_start_date, null, cur_tasks_rec.scheduled_start_date, actual_start_date),
6300: completed_percentage = 100,
6301: estimated_remaining_effort = 0,
6329:
6330: -- 3922325 : Added code to create task record if they do not exists
6331: IF sql%notfound THEN
6332: IF g1_debug_mode = 'Y' THEN
6333: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.PUSH_DOWN_TASK_STATUS', x_Msg => 'Inserting Tasks Record in PA_PROGRESS_ROLLUP ', x_Log_Level=> 3);
6334: END IF;
6335:
6336: l_percent_complete := 100;
6337: l_PROGRESS_ROLLUP_ID := null;
6338: l_push_as_of_date := p_as_of_date;
6339: l_prog_pa_period_name := PA_PROGRESS_UTILS.Prog_Get_Pa_Period_Name(l_push_as_of_date);
6340: l_prog_gl_period_name := PA_PROGRESS_UTILS.Prog_Get_GL_Period_Name(l_push_as_of_date);
6341:
6342: PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
6343: X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
6344: ,X_PROJECT_ID =>p_project_id
6345: ,X_OBJECT_ID => cur_tasks_rec.proj_element_id
6346: ,X_OBJECT_TYPE => 'PA_TASKS'
6469: RAISE FND_API.G_EXC_ERROR;
6470: END IF;
6471:
6472: IF g1_debug_mode = 'Y' THEN
6473: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_PROGRESS', x_Msg => 'Inserted Tasks Record in PA_PROGRESS_ROLLUP ', x_Log_Level=> 3);
6474: END IF;
6475: END IF;
6476:
6477: END LOOP;
6851:
6852: CURSOR C1(p_project_id NUMBER, p_working_str_version_id NUMBER) is
6853: -- Bug # 4581937.
6854: select ppr.*
6855: from pa_progress_rollup ppr, pa_proj_element_versions ppev --4871809
6856: where ppr.project_id = p_project_id
6857: and ppr.structure_version_id is null
6858: and ppr.object_type in ('PA_STRUCTURES','PA_ASSIGNMENTS','PA_TASKS')
6859: --and pa_proj_elements_utils.is_lowest_task(ppr.object_version_id) = 'Y' --bug 4050532
6865:
6866:
6867: CURSOR C2(p_project_id NUMBER) is
6868: select max(as_of_date)
6869: from pa_progress_rollup ppr
6870: where ppr.project_id = p_project_id
6871: and ppr.structure_version_id is null
6872: and ppr.structure_type = 'WORKPLAN'
6873: and ppr.current_flag <> 'W' -- Bug 3879461
6923: where ptav.project_id= p_project_id
6924: and ptav.structure_version_id = p_working_str_version_id
6925: and ptav.planning_start_date <= l_etc_start_date
6926: -- Bug 3958686 : Commenting the ta_display_flag portion
6927: and NOT EXISTS ( select 'x' from pa_progress_rollup
6928: where project_id=p_project_id
6929: and object_id = ptav.resource_list_member_id
6930: and proj_element_id = ptav.task_id
6931: and object_type = 'PA_ASSIGNMENTS'
7722: -- Assign the pl/sql tables bug 4050532
7723:
7724: l_index2 := l_index2 + 1;
7725:
7726: select pa_progress_rollup_s.nextval into progress_rollup_id(l_index2) from dual;
7727: percent_complete_id(l_index2) := to_number(null);
7728: PROJECT_ID(l_index2) := p_project_Id;
7729: OBJECT_ID(l_index2) := l_new_object_id;
7730: OBJECT_TYPE(l_index2) := l_c1rec.object_type;
7886: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.APPLY_LP_PROG_ON_CWV', x_Msg => 'l_EFF_ROLLUP_PERCENT_COMP='||l_EFF_ROLLUP_PERCENT_COMP, x_Log_Level=> 3);
7887: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.APPLY_LP_PROG_ON_CWV', x_Msg => 'l_c1rec.progress_rollup_id='||l_c1rec.progress_rollup_id, x_Log_Level=> 3);
7888: END IF;
7889:
7890: UPDATE pa_progress_rollup
7891: SET EFF_ROLLUP_PERCENT_COMP = l_EFF_ROLLUP_PERCENT_COMP
7892: ,last_update_date = SYSDATE
7893: WHERE progress_rollup_id = l_c1rec.progress_rollup_id;
7894: IF g1_debug_mode = 'Y' THEN
7913: IF g1_debug_mode = 'Y' THEN
7914: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.APPLY_LP_PROG_ON_CWV', x_Msg => 'Before BULK insert l_EFF_ROLLUP_PERCENT_COMP='||l_EFF_ROLLUP_PERCENT_COMP, x_Log_Level=> 3);
7915: END IF;
7916:
7917: DELETE FROM pa_progress_rollup WHERE project_id = p_project_id and structure_version_id = p_working_str_version_id;
7918:
7919: forall l_index1 in 1..l_index2
7920: INSERT INTO pa_progress_rollup(
7921: PROGRESS_ROLLUP_ID
7916:
7917: DELETE FROM pa_progress_rollup WHERE project_id = p_project_id and structure_version_id = p_working_str_version_id;
7918:
7919: forall l_index1 in 1..l_index2
7920: INSERT INTO pa_progress_rollup(
7921: PROGRESS_ROLLUP_ID
7922: ,PERCENT_COMPLETE_ID
7923: ,PROJECT_ID
7924: ,OBJECT_ID
8045: ,EQPMT_ACT_RAWCOST_TO_DATE_FC(l_index1)
8046: ,EQPMT_ACT_RAWCOST_TO_DATE_PC(l_index1)
8047: );
8048: IF g1_debug_mode = 'Y' THEN
8049: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.APPLY_LP_PROG_ON_CWV', x_Msg => 'Done with BULK insert into pa_progress_rollup', x_Log_Level=> 3);
8050: END IF;
8051: END IF;
8052:
8053:
8467: x_return_status := FND_API.G_RET_STS_SUCCESS;
8468:
8469: if (p_calling_context = 'STRUCTURE_VERSION') then
8470:
8471: delete from pa_progress_rollup
8472: where project_id = p_project_id
8473: -- and object_version_id = p_structure_version_id -- Bug 4190086
8474: and structure_version_id = p_structure_version_id -- Bug 4190086
8475: ;
8475: ;
8476:
8477: elsif (p_calling_context = 'PROJECT_STRUCTURE') then
8478:
8479: delete from pa_progress_rollup
8480: where project_id = p_project_id;
8481:
8482: delete from pa_proj_progress_attr
8483: where project_id = p_project_id;
8487:
8488: elsif (p_calling_context = 'TASK_VERSION') then
8489:
8490: forall i in p_task_version_id.first..p_task_version_id.last
8491: delete from pa_progress_rollup
8492: where project_id = p_project_id
8493: and object_version_id = p_task_version_id(i);
8494:
8495: forall i in p_task_version_id.first..p_task_version_id.last
8579: AND pst.structure_type = 'WORKPLAN';
8580: -- Bug 4600547 Begin
8581: CURSOR c_check_prog_exists IS
8582: SELECT 'Y'
8583: FROM pa_progress_rollup
8584: where project_id = P_Project_ID
8585: AND structure_type = 'WORKPLAN'
8586: and structure_version_id is null;
8587:
8591: -- Bug 5961235 Begin
8592: CURSOR c_check_prog_exists_prg IS
8593: SELECT 'Y' from dual
8594: where exists ( select DISTINCT ppev.PROJECT_ID
8595: FROM pa_progress_rollup ppr,
8596: pa_proj_element_versions ppev
8597: where ppr.project_id=ppev.project_id
8598: and ppev.OBJECT_TYPE = 'PA_STRUCTURES'
8599: and ppr.structure_type = 'WORKPLAN'
8899: cursor cur_percent_complete(p_project_id NUMBER, p_object_id NUMBER
8900: , p_object_version_id NUMBER, p_object_type VARCHAR2
8901: , p_as_of_date DATE) is
8902: select 'Y'
8903: from pa_progress_rollup
8904: where project_id = p_project_id
8905: and object_id = p_object_id
8906: and object_type = p_object_type
8907: and as_of_date = p_as_of_date
10040:
10041: CURSOR cur_rollup(c_period_type VARCHAR2)
10042: IS
10043: SELECT decode(c_period_type, 'P', PROG_PA_PERIOD_NAME, 'G', PROG_GL_PERIOD_NAME, null )
10044: FROM pa_progress_rollup
10045: WHERE project_id = p_project_id
10046: AND object_id = p_object_id
10047: AND proj_element_id = p_proj_element_id
10048: AND as_of_date = p_as_of_date
10701:
10702:
10703: CURSOR cur_fin_low_tasks(c_structure_type VARCHAR, c_structure_version_id number ) -- Bug 3957457 : Added c_structure_version_id
10704: IS
10705: -- Bug 3957457 : get percent complete from pa_progress_rollup instead of pa_percent_complete
10706: -- 4392189 : Program Reporting Changes - Phase 2 : Get base % complete rather rollup % complete
10707: -- SELECT ppv.proj_element_id, ppv.element_version_id, ppv.record_version_number, nvl(ppr.completed_percentage, ppr.eff_rollup_percent_comp) completed_percentage
10708: SELECT ppv.proj_element_id, ppv.element_version_id, ppv.record_version_number, nvl(ppr.completed_percentage, ppr.base_percent_complete) completed_percentage
10709: FROM pa_proj_element_versions ppv, pa_progress_rollup ppr
10705: -- Bug 3957457 : get percent complete from pa_progress_rollup instead of pa_percent_complete
10706: -- 4392189 : Program Reporting Changes - Phase 2 : Get base % complete rather rollup % complete
10707: -- SELECT ppv.proj_element_id, ppv.element_version_id, ppv.record_version_number, nvl(ppr.completed_percentage, ppr.eff_rollup_percent_comp) completed_percentage
10708: SELECT ppv.proj_element_id, ppv.element_version_id, ppv.record_version_number, nvl(ppr.completed_percentage, ppr.base_percent_complete) completed_percentage
10709: FROM pa_proj_element_versions ppv, pa_progress_rollup ppr
10710: WHERE ppv.project_id = p_project_id
10711: AND ppv.financial_task_flag = 'Y'
10712: AND ppv.parent_structure_version_id = c_structure_version_id -- Bug 3957457
10713: -- AND PA_PROJ_ELEMENTS_UTILS.check_child_element_exist(ppv.element_version_id) = 'N' bug 4086613
10728:
10729: -- 4392189 : Program Reporting Changes - Phase 2 : Get base % complete rather rollup % complete
10730: CURSOR cur_wp_task( c_object_version_id NUMBER, c_structure_version_id number ) -- Bug 3952006 : Added c_structure_version_id
10731: IS
10732: -- Bug 3952006 : get percent complete from pa_progress_rollup instead of pa_percent_complete
10733: --SELECT ppv.proj_element_id, ppv.element_version_id, ppv.record_version_number, nvl(ppr.completed_percentage, ppr.eff_rollup_percent_comp) completed_percentage
10734: SELECT ppv.proj_element_id, ppv.element_version_id, ppv.record_version_number, nvl(ppr.completed_percentage, ppr.base_percent_complete) completed_percentage
10735: FROM pa_proj_element_versions ppv, pa_progress_rollup ppr, pa_object_relationships por
10736: WHERE ppv.project_id = p_project_id
10731: IS
10732: -- Bug 3952006 : get percent complete from pa_progress_rollup instead of pa_percent_complete
10733: --SELECT ppv.proj_element_id, ppv.element_version_id, ppv.record_version_number, nvl(ppr.completed_percentage, ppr.eff_rollup_percent_comp) completed_percentage
10734: SELECT ppv.proj_element_id, ppv.element_version_id, ppv.record_version_number, nvl(ppr.completed_percentage, ppr.base_percent_complete) completed_percentage
10735: FROM pa_proj_element_versions ppv, pa_progress_rollup ppr, pa_object_relationships por
10736: WHERE ppv.project_id = p_project_id
10737: AND ppv.parent_structure_version_id = c_structure_version_id -- Bug 3952006
10738: AND ppv.project_id = ppr.project_id(+)
10739: AND por.object_id_to1 = c_object_version_id
10767: CURSOR c_max_date IS
10768: -- 4487630 : Now Date will be derived as max of existing financial as of date, workplan as of date, sysdate
10769: -- select max(decode(structure_type, 'WORKPLAN', as_of_date, as_of_date+1))
10770: select max(as_of_date)
10771: from pa_progress_rollup
10772: where project_id = p_project_id
10773: and structure_version_id is null
10774: and structure_type IN('WORKPLAN', 'FINANCIAL')
10775: and current_flag = 'Y';
11135: p_baseline_flag VARCHAR2(1):='N';
11136:
11137: CURSOR C2(p_project_id NUMBER) is
11138: select max(trunc(as_of_date)) -- 5294838 truncate
11139: from pa_progress_rollup ppr
11140: where ppr.project_id = p_project_id
11141: and ppr.structure_version_id is null
11142: and ppr.structure_type = 'WORKPLAN'
11143: and current_flag = 'Y';
11151:
11152:
11153: CURSOR c_get_working_wp_progress is
11154: select ppr.*
11155: from pa_progress_rollup ppr
11156: where ppr.project_id = p_project_id
11157: -- and ppr.structure_version_id is not null -- Bug 4190086
11158: and ppr.structure_version_id = p_working_str_ver_id -- Bug 4190086
11159: and ppr.structure_type = 'WORKPLAN';
11192: EQPMT_ACT_COST_TO_DATE_TC,
11193: EQPMT_ACT_COST_TO_DATE_FC,
11194: EQPMT_ACT_COST_TO_DATE_PC,
11195: EARNED_VALUE
11196: from pa_progress_rollup ppr
11197: where ppr.project_id = p_project_id
11198: and ppr.structure_version_id = p_working_str_ver_id -- Bug 4190086
11199: and ppr.structure_type = 'WORKPLAN'
11200: and ppr.proj_element_id <> 0 -- 4398411
11231: EQPMT_ACT_COST_TO_DATE_TC,
11232: EQPMT_ACT_COST_TO_DATE_FC,
11233: EQPMT_ACT_COST_TO_DATE_PC,
11234: EARNED_VALUE
11235: from pa_progress_rollup ppr1
11236: where ppr1.project_id = p_project_id
11237: and ppr1.structure_version_id IS NULL
11238: and ppr1.structure_type = 'WORKPLAN'
11239: and ppr1.current_flag = 'Y'
11239: and ppr1.current_flag = 'Y'
11240: and ppr1.proj_element_id <> 0 -- 4398411
11241: and ppr1.proj_element_id is not null -- 4398411
11242: and not exists
11243: (select 'xyz' from pa_progress_rollup ppr2
11244: where ppr2.project_id = ppr1.project_id
11245: and ppr2.object_id = ppr1.object_id
11246: and nvl(ppr2.proj_element_id,0) = nvl(ppr1.proj_element_id,0)
11247: and ppr2.structure_version_id = p_working_str_ver_id
11290: and type_id = task_type_id;
11291:
11292: CURSOR c_get_ppr_rec(c_project_id NUMBER, c_object_id NUMBER, c_proj_element_id NUMBER, c_object_type VARCHAR2, c_as_of_date DATE) is
11293: select rowid, ppr.*
11294: from pa_progress_rollup ppr
11295: where ppr.project_id = c_project_id
11296: and ppr.structure_type = 'WORKPLAN'
11297: and ppr.structure_version_id is null
11298: --and trunc(ppr.as_of_date) = trunc(c_as_of_date) -- Bug 4227280
11302: and ppr.object_type = c_object_type;
11303:
11304: CURSOR c_get_last_ppr_rec(c_project_id NUMBER, c_object_id NUMBER, c_proj_element_id NUMBER, c_object_type VARCHAR2, c_as_of_date DATE) is
11305: select ppr.*
11306: from pa_progress_rollup ppr
11307: where ppr.project_id = c_project_id
11308: and ppr.structure_type = 'WORKPLAN'
11309: and ppr.structure_version_id is null
11310: ------ and trunc(ppr.as_of_date) < trunc(c_as_of_date) redundant cond. 4743866
11365: L_LATEST_PUB_WP_VERSION_ID NUMBER;
11366: L_PRE_PUB_STR_VER_ID NUMBER;
11367: L_TASK_WEIGHT_BASIS_CODE pa_proj_progress_attr.task_weight_basis_code%TYPE;
11368: L_OBJECT_VERSION_ID NUMBER;
11369: L_PROGRESS_STATUS_CODE pa_progress_rollup.progress_status_code%TYPE;
11370: L_ACTUAL_START_DATE DATE;
11371: L_ACTUAL_FINISH_DATE DATE;
11372: L_ESTIMATED_START_DATE DATE;
11373: L_ESTIMATED_FINISH_DATE DATE;
11376: L_SYSTEM_STATUS_CODE pa_project_statuses.project_system_status_code%TYPE;
11377: L_PROGRESS_ROLLUP_ID NUMBER;
11378: L_BAC_VALUE NUMBER;
11379: L_EARNED_VALUE NUMBER;
11380: L_PROG_PA_PERIOD_NAME pa_progress_rollup.PROG_PA_PERIOD_NAME%TYPE;
11381: L_PROG_GL_PERIOD_NAME pa_progress_rollup.PROG_GL_PERIOD_NAME%TYPE;
11382: l_scheduled_start_date DATE;
11383: l_scheduled_finish_date DATE;
11384: l_baselined_str_ver_id NUMBER;
11377: L_PROGRESS_ROLLUP_ID NUMBER;
11378: L_BAC_VALUE NUMBER;
11379: L_EARNED_VALUE NUMBER;
11380: L_PROG_PA_PERIOD_NAME pa_progress_rollup.PROG_PA_PERIOD_NAME%TYPE;
11381: L_PROG_GL_PERIOD_NAME pa_progress_rollup.PROG_GL_PERIOD_NAME%TYPE;
11382: l_scheduled_start_date DATE;
11383: l_scheduled_finish_date DATE;
11384: l_baselined_str_ver_id NUMBER;
11385: l_oth_etc_quantity NUMBER;
11411: l_upd_new_elem_ver_id_flag VARCHAR2(1) := 'Y';
11412:
11413: CURSOR cur_lp_progress is
11414: select *
11415: from pa_progress_rollup ppr
11416: where ppr.project_id = p_project_id
11417: and ppr.structure_version_id is null
11418: and ppr.object_type = 'PA_TASKS'
11419: and structure_type = 'WORKPLAN'
11421: and pa_progress_utils.check_assignment_exists(p_project_id,ppr.object_version_id,ppr.object_type) = 'N' -- Bug 3627315 issue 8 Added this condition
11422: and current_flag = 'Y'
11423: Union
11424: select *
11425: from pa_progress_rollup ppr
11426: where ppr.project_id = p_project_id
11427: and ppr.structure_version_id is null
11428: and ppr.object_type = 'PA_ASSIGNMENTS'
11429: and structure_type = 'WORKPLAN'
11710: where project_id = p_project_id
11711: and structure_type = 'WORKPLAN'
11712: and published_flag = 'N';
11713:
11714: DELETE from pa_progress_rollup
11715: where project_id = p_project_id
11716: and structure_type = 'WORKPLAN'
11717: and structure_version_id is null
11718: and current_flag = 'W';
11785: -- delete it
11786: -- IF g1_debug_mode = 'Y' THEN
11787: -- pa_debug.write(x_Module=>'PA_PROGRESS_PUB.PUBLISH_PROGRESS', x_Msg => 'Working PPR Record Found, Deleting it', x_Log_Level=> 3);
11788: -- END IF;
11789: -- PA_PROGRESS_ROLLUP_PKG.DELETE_ROW(l_ppr_rec.rowid);
11790: --END IF;
11791:
11792: l_last_ppr_rec := null;
11793: OPEN c_get_last_ppr_rec(l_rec.project_id, l_rec.object_id, l_rec.proj_element_id, l_rec.object_type, l_as_of_date);
12506:
12507: ---5212999
12508: if (l_upd_rollup_prog = 'N') then
12509: FORALL i IN 1..l_index
12510: update pa_progress_rollup
12511: set current_flag = 'N'
12512: where project_id = l_project_id_tbl(i)
12513: and current_flag = 'Y'
12514: and structure_version_id is null
12518: and proj_element_id = l_task_id_tbl(i);
12519:
12520: else
12521: FORALL i IN 1..l_index
12522: delete from pa_progress_rollup
12523: where project_id = l_project_id_tbl(i)
12524: and current_flag = 'Y'
12525: and structure_version_id is null
12526: and structure_type = 'WORKPLAN'
12558:
12559:
12560:
12561: FORALL i IN 1..l_index
12562: insert into pa_progress_rollup(
12563: PROGRESS_ROLLUP_ID
12564: ,PROJECT_ID
12565: ,OBJECT_ID
12566: ,OBJECT_TYPE
12642: ,EQPMT_ETC_RAWCOST_FC
12643: ,EQPMT_ETC_RAWCOST_PC
12644: ,BASE_PERCENT_COMPLETE -- 4604661
12645: ) VALUES (
12646: PA_PROGRESS_ROLLUP_S.nextval
12647: ,l_project_id_tbl(i)
12648: ,l_object_id_tbl(i)
12649: ,l_object_type_tbl(i)
12650: ,l_as_of_date
12951: and object_type = 'PA_ASSIGNMENTS'
12952: and structure_type = 'WORKPLAN';
12953:
12954: cursor max_as_of_date_rollup is
12955: select max(as_of_date) from pa_progress_rollup
12956: where project_id = l_project_id
12957: and structure_type = 'WORKPLAN'
12958: and structure_version_id is null
12959: and current_flag = 'Y';
12958: and structure_version_id is null
12959: and current_flag = 'Y';
12960:
12961: cursor max_as_of_date_rollup_tsk is
12962: select max(as_of_date) from pa_progress_rollup
12963: where project_id = l_project_id
12964: and object_type = 'PA_TASKS'
12965: and structure_type = 'WORKPLAN'
12966: and structure_version_id is null
13330: PROG_PA_PERIOD_NAME,
13331: PROG_GL_PERIOD_NAME,
13332: OTH_QUANTITY_TO_DATE,
13333: OTH_ETC_QUANTITY
13334: from pa_progress_rollup
13335: where project_id = l_project_id
13336: and object_id = l_object_id
13337: and object_Type = l_object_type
13338: and structure_type = 'WORKPLAN'
13339: and structure_version_id is null
13340: and proj_element_id = l_task_id
13341: AND current_flag <> 'W' -- Bug 3879461
13342: and trunc(as_of_date) = (select max(trunc(as_of_Date))
13343: from pa_progress_rollup
13344: where project_id = l_project_id
13345: and object_id = l_object_id
13346: and object_Type = l_object_type
13347: and structure_type = 'WORKPLAN'
13477: PROG_PA_PERIOD_NAME,
13478: PROG_GL_PERIOD_NAME,
13479: OTH_QUANTITY_TO_DATE,
13480: OTH_ETC_QUANTITY
13481: from pa_progress_rollup
13482: where project_id = l_project_id
13483: and object_id = l_object_id
13484: and object_Type = l_object_type
13485: and structure_type = 'WORKPLAN'
13614: PROG_PA_PERIOD_NAME,
13615: PROG_GL_PERIOD_NAME,
13616: OTH_QUANTITY_TO_DATE,
13617: OTH_ETC_QUANTITY
13618: from pa_progress_rollup
13619: where project_id = l_project_id
13620: and object_id = l_object_id
13621: and object_type = l_object_type
13622: and structure_type = 'WORKPLAN'
13628:
13629: -- cursor to get all assignment records for that as of date
13630: cursor all_assignments_asofdate is
13631: select *
13632: from pa_progress_rollup
13633: where project_id = l_project_id
13634: and object_type = 'PA_ASSIGNMENTS'
13635: and structure_version_id is null
13636: and structure_Type = 'WORKPLAN'
13640:
13641: -- cursor to get all assignment records for the previous as of date
13642: cursor all_assign_prev_asofdate is
13643: select *
13644: from pa_progress_rollup ppr
13645: where ppr.project_id = l_project_id
13646: and ppr.object_type = 'PA_ASSIGNMENTS'
13647: and ppr.structure_version_id is null
13648: and ppr.structure_Type = 'WORKPLAN'
13647: and ppr.structure_version_id is null
13648: and ppr.structure_Type = 'WORKPLAN'
13649: AND current_flag <> 'W' -- Bug 3879461
13650: and ppr.as_of_date = (select max(as_of_date)
13651: from pa_progress_rollup ppr1
13652: where ppr1.project_id = l_project_id
13653: and ppr1.object_type = 'PA_ASSIGNMENTS'
13654: and ppr1.object_id = ppr.object_id
13655: and ppr1.proj_element_id = ppr.proj_element_id
13657: and ppr1.structure_Type = 'WORKPLAN'
13658: AND current_flag <> 'W' -- Bug 3879461
13659: and ppr1.as_of_date < l_temp_as_of_Date
13660: and not exists (select 1 ---4871809 performance bug
13661: from pa_progress_rollup ppr2
13662: where ppr2.project_id = l_project_id
13663: and ppr2.object_type = 'PA_ASSIGNMENTS'
13664: and ppr2.object_id = ppr.object_id
13665: and ppr2.proj_element_id = ppr.proj_element_id
13793: , ppr.actual_finish_date
13794: , ppr.proj_element_id
13795: , ppr.object_type
13796: , ppr.project_id
13797: FROM pa_progress_rollup ppr
13798: , pa_proj_elements ppe
13799: WHERE ppr.progress_rollup_id = c_progress_rollup_id
13800: AND ppr.proj_element_id = ppe.proj_element_id
13801: ;
13820: , ppr.estimated_finish_date
13821: , ppr.actual_start_date
13822: , ppr.actual_finish_date
13823: , ppe.status_code
13824: FROM pa_progress_rollup ppr
13825: , pa_proj_elements ppe
13826: WHERE ppr.project_id = c_project_id
13827: AND ppr.object_id = c_object_id
13828: AND ppr.object_type = c_object_type
13846: , ppr.object_type
13847: , ppr.as_of_date
13848: , ppr.project_id
13849: , ppr.current_flag
13850: FROM pa_progress_rollup ppr
13851: , pa_proj_elements ppe
13852: WHERE ppr.progress_rollup_id = c_progress_rollup_id
13853: AND ppr.proj_element_id = ppe.proj_element_id
13854: ;
13880:
13881: -- 4654495
13882: cursor some_progress_exists is
13883: select 'Y'
13884: from pa_progress_rollup
13885: where project_id = l_project_id
13886: and proj_element_id = l_task_id
13887: and object_type in ('PA_TASKS','PA_ASSIGNMENTS');
13888:
14306:
14307: -- 4654495
14308: if (p_plan_res_level = 'N' and
14309: (nvl(all_objects_in_temp_rec.act_labor_hrs,0)+nvl(all_objects_in_temp_rec.act_equip_hrs,0)+nvl(all_objects_in_temp_rec.act_prj_raw_cost,0) = 0)) then
14310: --- check in pa_progress_rollup table if data already exists for this task
14311: open some_progress_exists;
14312: fetch some_progress_exists into l_some_progress_exists;
14313: if (some_progress_exists%notfound) then
14314: l_ignore_rec := 'Y';
15279: --- u_ tables else populate the insert tables, for bulk processing
15280: --- call an api to check if a record exists on that date
15281: if (l_db_case = '1') then
15282: --- insert with defaults
15283: select pa_progress_rollup_s.nextval into progress_rollup_id(l_index2) from dual;
15284: percent_complete_id(l_index2) := to_number(null);
15285: project_id(l_index2) := l_project_id;
15286: object_id(l_index2) := l_object_id;
15287: OBJECT_TYPE(l_index2) := l_object_Type;
16210:
16211: elsif (l_db_case = '3') then
16212: --- get progress attrs for the task/assignment
16213:
16214: select pa_progress_rollup_s.nextval into progress_rollup_id(l_index2) from dual;
16215: percent_complete_id(l_index2) := to_number(null);
16216: project_id(l_index2) := l_project_id;
16217: object_id(l_index2) := l_object_id;
16218: OBJECT_TYPE(l_index2) := l_rollup_rec_asofdate.object_Type;
17062: if (l_match_found = 'N') then
17063: if (nvl(all_assign_prev_asofdate_rec.ppl_act_effort_to_date,0) + nvl(all_assign_prev_asofdate_rec.eqpmt_act_effort_to_date,0) + nvl(all_assign_prev_asofdate_rec.oth_quantity_to_date,0)
17064: + nvl(all_assign_prev_asofdate_rec.ppl_act_cost_to_date_tc,0) + nvl(all_assign_prev_asofdate_rec.eqpmt_act_cost_to_date_tc,0) + nvl(all_assign_prev_asofdate_rec.oth_act_cost_to_date_tc,0) <> 0) then ---4461052
17065: l_index2 := l_index2 + 1;
17066: select pa_progress_rollup_s.nextval into progress_rollup_id(l_index2) from dual;
17067: percent_complete_id(l_index2) := to_number(null);
17068: project_id(l_index2) := l_project_id;
17069: object_id(l_index2) := all_assign_prev_asofdate_rec.object_id;
17070: OBJECT_TYPE(l_index2) := all_assign_prev_asofdate_rec.object_Type;
17565:
17566:
17567: --- do bulk insert
17568: forall l_index1 in 1..l_index2
17569: insert into pa_progress_rollup(
17570: PROGRESS_ROLLUP_ID
17571: ,PROJECT_ID
17572: ,OBJECT_ID
17573: ,OBJECT_TYPE
17819: ,oth_etc_qty(l_index1)
17820: );
17821: IF g1_debug_mode = 'Y' THEN
17822: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.get_summarized_actuals',
17823: x_Msg => 'Done with bulk insert into pa_progress_rollup',
17824: x_Log_Level=> 3);
17825: END IF;
17826:
17827: -- Bug 6917961
17862: -- Bug 6917961
17863:
17864: --- do bulk update
17865: forall l_index1 in 1..l_index3
17866: update pa_progress_rollup set
17867: OBJECT_VERSION_ID = u_object_version_id(l_index1)
17868: ,LAST_UPDATE_DATE = sysdate
17869: ,LAST_UPDATED_BY = fnd_global.user_id
17870: ,LAST_UPDATE_LOGIN = fnd_global.login_id
17927: where progress_rollup_id = u_progress_rollup_id(l_index1);
17928:
17929: IF g1_debug_mode = 'Y' THEN
17930: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.get_summarized_actuals',
17931: x_Msg => 'Done with bulk update into pa_progress_rollup',
17932: x_Log_Level=> 3);
17933: END IF;
17934:
17935: forall l_index1 in 1..l_index4
17932: x_Log_Level=> 3);
17933: END IF;
17934:
17935: forall l_index1 in 1..l_index4
17936: update pa_progress_rollup set
17937: CURRENT_FLAG = 'N'
17938: where progress_rollup_id = u1_progress_rollup_id(l_index1);
17939:
17940: IF g1_debug_mode = 'Y' THEN
17938: where progress_rollup_id = u1_progress_rollup_id(l_index1);
17939:
17940: IF g1_debug_mode = 'Y' THEN
17941: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.get_summarized_actuals',
17942: x_Msg => 'Done with bulk update of current flag in pa_progress_rollup',
17943: x_Log_Level=> 3);
17944: END IF;
17945:
17946: pa_debug.log_message('GET_SUMM_ACT: Done insert/update of pa_progress_rollup.', 1);
17942: x_Msg => 'Done with bulk update of current flag in pa_progress_rollup',
17943: x_Log_Level=> 3);
17944: END IF;
17945:
17946: pa_debug.log_message('GET_SUMM_ACT: Done insert/update of pa_progress_rollup.', 1);
17947:
17948: if ((l_index2 > 0 or l_index3 > 0) and p_plan_res_level = 'N') then
17949: --- now we are done with updating all actuals
17950: --- we need to rollup data for the l_temp_as_of_date
18741: AND current_flag = 'Y'
18742: AND object_type = l_object_type
18743: AND structure_type = p_structure_type;
18744:
18745: UPDATE pa_progress_rollup
18746: SET current_flag = 'N'
18747: WHERE project_id = p_project_id
18748: AND object_id = p_object_id
18749: AND object_type = p_object_type
18850: );
18851:
18852: IF l_PROGRESS_ROLLUP_ID IS NULL
18853: THEN
18854: PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
18855: X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
18856: ,X_PROJECT_ID => p_project_id
18857: ,X_OBJECT_ID => l_object_id
18858: ,X_OBJECT_TYPE => l_object_type
18975: ,X_SUBPRJ_EQPMT_ETC_RAWCOST_TC => null
18976: ,X_SUBPRJ_EQPMT_ETC_RAWCOST_FC => null
18977: ,X_SUBPRJ_EQPMT_ETC_RAWCOST_PC => null);
18978: ELSE
18979: PA_PROGRESS_ROLLUP_PKG.UPDATE_ROW(
18980: X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
18981: ,X_PROJECT_ID => p_project_id
18982: ,X_OBJECT_ID => l_object_id
18983: ,X_OBJECT_TYPE => l_object_type
19212: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_FINANCIAL_TASK_PROGRESS', x_Msg => 'l_PROGRESS_ROLLUP_ID: '||l_PROGRESS_ROLLUP_ID, x_Log_Level=> 3);
19213: END IF;
19214:
19215:
19216: PA_PROGRESS_ROLLUP_PKG.UPDATE_ROW(
19217: X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
19218: ,X_PROJECT_ID => p_project_id
19219: ,X_OBJECT_ID => l_object_id
19220: ,X_OBJECT_TYPE => l_object_type
19437: -- Bug 3693757 : This cursor is wrong as it may return wrong task_wt_basis_code of some other projects' task
19438:
19439: cursor l_ve_cur(p_project_id NUMBER, p_structure_version_id NUMBER) is
19440: select as_of_date, task_wt_basis_code
19441: from pa_progress_rollup
19442: where project_id = p_project_id -- Bug 3693757
19443: and structure_version_id = p_structure_version_id -- Bug 3693757
19444: and object_type = 'PA_STRUCTURES' -- Bug 3693757
19445: ;
19444: and object_type = 'PA_STRUCTURES' -- Bug 3693757
19445: ;
19446: /* Bug 3693757
19447: and as_of_date = (select max(as_of_date)
19448: from pa_progress_rollup
19449: where project_id = p_project_id
19450: and structure_version_id = p_structure_version_id
19451: and current_flag = 'Y');
19452: */
19465: --bug # 3792713 Satish end
19466:
19467: cursor l_vd_cur(p_project_id NUMBER, p_structure_version_id NUMBER) is
19468: select as_of_date, task_wt_basis_code
19469: from pa_progress_rollup
19470: where project_id = p_project_id
19471: and structure_version_id = p_structure_version_id;
19472:
19473: --added by maansari for
19974: -- null to not null. In this case only ETC needs to be pushed as PJi will derive actuals as planned-etc.
19975:
19976: CURSOR cur_working_wp_progress is
19977: select *
19978: from pa_progress_rollup ppr
19979: where ppr.project_id = p_project_id
19980: and ppr.structure_version_id = p_src_str_ver_id
19981: and ppr.object_type = 'PA_ASSIGNMENTS'
19982: and structure_type = 'WORKPLAN'
19984:
19985:
19986: CURSOR cur_lp_progress is
19987: select *
19988: from pa_progress_rollup ppr
19989: where ppr.project_id = p_project_id
19990: and ppr.structure_version_id is null
19991: and ppr.object_type = 'PA_ASSIGNMENTS'
19992: and structure_type = 'WORKPLAN'
19994: -- Using current_flag = 'Y' may push data of working progress too. Is it fine?
19995:
19996: l_working_prog VARCHAR2(1) := 'N';
19997:
19998: l_ppr_rec pa_progress_rollup%rowtype;
19999:
20000: -- Bug 3764224 : Changed the cusrsor def'n
20001: cursor cur_ptav (p_object_id NUMBER, p_task_id NUMBER) is
20002: select ptav2.resource_list_member_id, ptav2.txn_currency_code
20042: l_prog_mode varchar2(1);
20043:
20044: cursor cur_task_assgn(p_project_id NUMBER, p_object_version_id NUMBER) is
20045: select count(*)
20046: from pa_progress_rollup ppr
20047: where ppr.project_id = p_project_id
20048: and ppr.object_version_id = p_object_version_id
20049: and ppr.object_type = 'PA_ASSIGNMENTS'
20050: and ppr.structure_version_id is null
20057: --bug 3968791
20058: CURSOR cur_select_all_assgns
20059: IS
20060: SELECT ptav.*
20061: FROM pa_progress_rollup ppr2
20062: --,pa_task_assignments_v ptav --bug 3967374
20063: ,pa_task_asgmts_v ptav
20064: WHERE ppr2.project_id=p_project_id
20065: AND ptav.project_id=p_project_id
20065: AND ptav.project_id=p_project_id
20066: AND ppr2.object_id = ptav.task_id
20067: AND (ptav.resource_list_member_id ) not in (
20068: SELECT object_id
20069: FROM pa_progress_rollup ppr
20070: where ppr.project_id=p_project_id
20071: and ppr.structure_version_id is null
20072: and ppr.current_flag = 'Y'
20073: and ppr.object_type = 'PA_ASSIGNMENTS'
20243: )
20244: )
20245: THEN
20246: IF l_prog_mode = 'P' THEN
20247: DELETE from pa_progress_rollup
20248: WHERE project_id = p_project_id
20249: AND structure_version_id is null
20250: AND structure_type = 'WORKPLAN'
20251: AND object_type = 'PA_ASSIGNMENTS'
20252: AND object_id = l_ppr_rec.object_id
20253: AND proj_element_id = l_ppr_rec.proj_element_id;
20254: END IF;
20255: IF l_prog_mode = 'W' THEN
20256: DELETE from pa_progress_rollup
20257: WHERE project_id = p_project_id
20258: AND structure_version_id = p_src_str_ver_id
20259: AND structure_type = 'WORKPLAN'
20260: AND object_type = 'PA_ASSIGNMENTS'
20847: END IF;
20848: /***********
20849: -- here we need to make ETC as null on assignment records
20850: if (l_calling_context = 'PUBLISH') then
20851: update pa_progress_rollup
20852: set oth_etc_quantity = to_number(null)
20853: ,oth_etc_rawcost_tc = to_number(null)
20854: ,oth_etc_rawcost_pc = to_number(null)
20855: ,oth_etc_rawcost_fc = to_number(null)
20945: pa_proj_element_versions ppv1 --4871809
20946: ,pa_proj_element_versions ppv2
20947: ,pa_object_relationships por1
20948: ,pa_object_relationships por2
20949: ,pa_progress_rollup ppr
20950: WHERE
20951: ppv2.element_version_id = por1.object_id_to1
20952: AND ppv2.object_type = 'PA_STRUCTURES'
20953: AND por1.relationship_type in ( 'LW', 'LF' )