[Home] [Help]
283: -- Bug 3764224 : No need to have proj element id join in these cursors as it is used only in tasks context
284: CURSOR cur_pa_rollup1
285: IS
286: SELECT *
287: FROM pa_progress_rollup
288: WHERE project_id = p_project_id
289: AND object_id = p_object_id
290: AND structure_type = p_structure_type -- FPM Dev CR 3
291: AND structure_version_id IS NULL -- Bug 3764224
289: AND object_id = p_object_id
290: AND structure_type = p_structure_type -- FPM Dev CR 3
291: AND structure_version_id IS NULL -- Bug 3764224
292: AND as_of_date = ( SELECT max( as_of_date )
293: FROM pa_progress_rollup
294: WHERE project_id = p_project_id
295: AND object_id = p_object_id
296: AND object_type = p_object_type
297: AND structure_type = p_structure_type -- FPM Dev CR 3
301:
302: CURSOR cur_pa_rollup2
303: IS
304: SELECT *
305: FROM pa_progress_rollup
306: WHERE project_id = p_project_id
307: AND object_id = p_object_id
308: AND object_type = p_object_type
309: AND as_of_date = p_as_of_date
407: --bug 3879461 --maansari
408: CURSOR check_prog_after_aod
409: IS
410: SELECT 'Y'
411: FROM pa_progress_rollup
412: WHERE current_flag = 'Y'
413: AND project_id = p_project_id
414: AND structure_type = p_structure_type
415: AND structure_version_id is null
464: ppc.progress_comment,
465: ppc.description progress_overview
466: from pa_task_asgmts_v asgn
467: , pa_percent_completes ppc
468: , pa_progress_rollup ppr
469: where
470: ppc.PROJECT_ID = l_project_id
471: AND ppr.project_id = ppc.project_id
472: AND ppc.project_id = asgn.project_id
507: ppr.actual_finish_date,
508: ppc.record_version_number percomp_record_version_number,
509: ppc.status_code del_status_code
510: FROM pa_percent_completes ppc
511: , pa_progress_rollup ppr
512: WHERE
513: ppc.project_id=l_project_id
514: AND ppc.project_id = ppr.project_id
515: AND ppc.object_type = 'PA_DELIVERABLES'
569: , estimated_start_date
570: , estimated_finish_date
571: , actual_start_date
572: , actual_finish_date
573: FROM pa_progress_rollup
574: WHERE project_id = p_project_id
575: AND object_id = p_object_id
576: AND object_type = 'PA_TASKS'
577: AND structure_type = 'WORKPLAN'
597: -- Added cursor c_get_last_ppl_effort_fr_assgn
598: CURSOR c_get_last_ppl_effort_fr_assgn(c_res_list_mem_id NUMBER)
599: IS
600: SELECT NVL( PPL_ACT_EFFORT_TO_DATE, 0 )
601: FROM pa_progress_rollup
602: WHERE project_id = p_project_id
603: AND object_id = c_res_list_mem_id
604: AND as_of_date =
605: (SELECT max(as_of_date)
602: WHERE project_id = p_project_id
603: AND object_id = c_res_list_mem_id
604: AND as_of_date =
605: (SELECT max(as_of_date)
606: FROM pa_progress_rollup ppr2
607: WHERE ppr2.as_of_date <= p_as_of_date
608: AND ppr2.project_id = p_project_id
609: AND ppr2.object_id = c_res_list_mem_id
610: AND ppr2.object_type = 'PA_ASSIGNMENTS'
888: --AND date_computed <> p_as_of_date --bug 4185364, delete all the working records
889: AND structure_type = p_structure_type
890: ;
891:
892: DELETE FROM pa_progress_rollup
893: WHERE project_id= p_project_id
894: AND object_id = p_object_id
895: AND current_flag = 'W'
896: --AND as_of_date <> p_as_of_date --bug 4185364, delete all the working records
909: AND date_computed <= p_as_of_date --- 4245168
910: AND structure_type = p_structure_type
911: ;
912:
913: DELETE FROM pa_progress_rollup
914: WHERE project_id= p_project_id
915: AND object_id = p_object_id
916: AND current_flag = 'W'
917: AND as_of_date < p_as_of_date
2673: AND structure_type = p_structure_type; --Satish
2674:
2675: -- Bug 3879461 Begin
2676: IF NVL(l_prog_exist_after_aod, 'N') = 'N' THEN
2677: UPDATE pa_progress_rollup
2678: SET current_flag = 'N'
2679: WHERE project_id = p_project_id
2680: AND object_id = p_object_id
2681: AND proj_element_id = p_object_id
2699: --bug 4247839, removed the l_db_Action check as it should get deleted for CREATE also
2700: --IF l_db_action = 'UPDATE' AND p_progress_mode <> 'BACKDATED' THEN
2701: IF p_progress_mode <> 'BACKDATED' THEN
2702:
2703: Delete from pa_progress_rollup
2704: where project_id = p_project_id
2705: and object_id = p_object_id
2706: and proj_element_id = p_object_id
2707: and object_type = p_object_type
2916: END IF;
2917: -- Bug 4506461 End
2918:
2919: IF g1_debug_mode = 'Y' THEN
2920: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_PROGRESS', x_Msg => 'Inserting in PA_PROGRESS_ROLLUP ', x_Log_Level=> 3);
2921: END IF;
2922:
2923:
2924: PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
2920: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_PROGRESS', x_Msg => 'Inserting in PA_PROGRESS_ROLLUP ', x_Log_Level=> 3);
2921: END IF;
2922:
2923:
2924: PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
2925: x_progress_rollup_id => l_progress_rollup_id
2926: ,x_project_id => p_project_id
2927: ,x_object_id => p_object_id
2928: ,x_object_type => p_object_type
3051:
3052: -- Progress Management Changes. FP-M Bug # 3420093. Begin:Set CURRENT_FLAG of previous rollup records to 'N'.
3053:
3054: IF g1_debug_mode = 'Y' THEN
3055: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_PROGRESS', x_Msg => 'Inserted in PA_PROGRESS_ROLLUP ', x_Log_Level=> 3);
3056: END IF;
3057:
3058: ELSE --1
3059: --update progress rollup
3118: END IF;
3119: -- Bug 4506461 End
3120:
3121:
3122: PA_PROGRESS_ROLLUP_PKG.UPDATE_ROW(
3123: x_progress_rollup_id => l_progress_rollup_id
3124: ,x_project_id => p_project_id
3125: ,x_object_id => p_object_id
3126: ,x_object_type => p_object_type
3249: IF Fnd_Msg_Pub.count_msg > 0 THEN
3250: RAISE FND_API.G_EXC_ERROR;
3251: END IF;
3252:
3253: -----update percent_complete_id in pa_progress_rollup
3254: UPDATE pa_progress_rollup
3255: SET percent_complete_id = l_percent_complete_id
3256: WHERE object_id = p_object_id
3257: AND project_id = p_project_id
3250: RAISE FND_API.G_EXC_ERROR;
3251: END IF;
3252:
3253: -----update percent_complete_id in pa_progress_rollup
3254: UPDATE pa_progress_rollup
3255: SET percent_complete_id = l_percent_complete_id
3256: WHERE object_id = p_object_id
3257: AND project_id = p_project_id
3258: AND as_of_date > p_as_of_date
3442: END IF;
3443: -- Bug 4506461 End
3444:
3445: IF g1_debug_mode = 'Y' THEN
3446: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_PROGRESS', x_Msg => 'Update Action: Updating PA_PROGRESS_ROLLUP ', x_Log_Level=> 3);
3447: END IF;
3448:
3449: pa_progress_rollup_pkg.update_row(
3450: x_progress_rollup_id => l_progress_rollup_id
3445: IF g1_debug_mode = 'Y' THEN
3446: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_PROGRESS', x_Msg => 'Update Action: Updating PA_PROGRESS_ROLLUP ', x_Log_Level=> 3);
3447: END IF;
3448:
3449: pa_progress_rollup_pkg.update_row(
3450: x_progress_rollup_id => l_progress_rollup_id
3451: ,x_project_id => p_project_id
3452: ,x_object_id => p_object_id
3453: ,x_object_type => p_object_type
3573: ,x_subprj_eqpmt_etc_rawcost_pc => l_rollup_rec2.subprj_eqpmt_etc_rawcost_pc
3574: );
3575:
3576: IF g1_debug_mode = 'Y' THEN
3577: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_PROGRESS', x_Msg => 'Update Action: Updated PA_PROGRESS_ROLLUP ', x_Log_Level=> 3);
3578: END IF;
3579:
3580: -- FPM Dev CR 6
3581: IF Fnd_Msg_Pub.count_msg > 0 THEN
3671: -- Bug 4506461 End
3672:
3673:
3674: -- changed this for effort to cost conversion
3675: PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
3676: x_progress_rollup_id => l_progress_rollup_id
3677: ,x_project_id => p_project_id
3678: ,x_object_id => p_object_id
3679: ,x_object_type => p_object_type
3931: IF p_object_type = 'PA_TASKS'
3932: AND l_element_struc_type = 'WORKPLAN' AND p_progress_mode <> 'BACKDATED'
3933: THEN
3934: --- update % complete and prog stat for future rollup recs
3935: update pa_progress_rollup
3936: set completed_percentage = l_percent_complete,
3937: progress_status_code = p_progress_status_code
3938: where project_id = p_project_id
3939: and object_id = p_object_id
4034: and task_status1 = '127' then
4035:
4036: If l_task_status = 124 then
4037:
4038: UPDATE pa_progress_rollup
4039: SET actual_finish_date = '',
4040: actual_start_date = '',
4041: completed_percentage = ''
4042: WHERE object_id = p_object_id
4048: AND structure_type = p_structure_type;
4049:
4050: Elsif l_task_status = 125 then
4051:
4052: UPDATE pa_progress_rollup
4053: SET actual_finish_date = '',
4054: completed_percentage = ''
4055: WHERE object_id = p_object_id
4056: AND current_flag IN ('Y')
5396:
5397: CURSOR cur_pa_ppr_str( c_project_id NUMBER, c_proj_element_id NUMBER )
5398: IS
5399: SELECT rowid
5400: FROM pa_progress_rollup
5401: WHERE object_version_id = p_structure_version_id
5402: AND object_id = c_proj_element_id
5403: AND project_id = c_project_id
5404: AND object_type = 'PA_STRUCTURES';
5414:
5415: CURSOR cur_pa_ppr_tsk ( c_project_id NUMBER, c_proj_element_id NUMBER )
5416: IS
5417: SELECT rowid
5418: FROM pa_progress_rollup
5419: WHERE object_version_id = p_task_version_id
5420: AND object_id = c_proj_element_id
5421: AND project_id = c_project_id
5422: AND object_type = 'PA_TASKS';
5486: CLOSE cur_pa_ppr_tsk;
5487: END IF;
5488:
5489: PA_PERCENT_COMPLETES_PKG.DELETE_ROW( l_ppc_row_id );
5490: PA_PROGRESS_ROLLUP_PKG.DELETE_ROW( l_rollup_row_id );
5491:
5492: EXCEPTION
5493: when FND_API.G_EXC_ERROR then
5494: if p_commit = FND_API.G_TRUE then
5608: , ppr.OTH_ACT_RAWCOST_TO_DATE_TC, ppr.OTH_ACT_RAWCOST_TO_DATE_PC, ppr.OTH_ACT_RAWCOST_TO_DATE_FC
5609: , ppr.as_of_date
5610: , ptav.assignment_start_date
5611: FROM pa_task_asgmts_V ptav
5612: , pa_progress_rollup ppr
5613: WHERE ptav.task_version_id = c_task_version_id
5614: -- AND ptav.structure_version_id = l_structure_version_id
5615: AND ptav.task_id = c_task_id
5616: AND ptav.project_id = p_project_id
5675: , ppr.OTH_ACT_RAWCOST_TO_DATE_TC, ppr.OTH_ACT_RAWCOST_TO_DATE_PC, ppr.OTH_ACT_RAWCOST_TO_DATE_FC
5676: , ppr.as_of_date
5677: , ptav.assignment_start_date
5678: FROM pa_task_asgmts_V ptav
5679: , pa_progress_rollup ppr
5680: WHERE ptav.task_version_id = c_task_version_id
5681: -- AND ptav.structure_version_id = l_structure_version_id
5682: AND ptav.task_id = c_task_id
5683: AND ptav.project_id = p_project_id
5693: AND ppr.structure_version_id(+) is null
5694: ;
5695:
5696: L_PROGRESS_ROLLUP_ID NUMBER;
5697: L_PROG_PA_PERIOD_NAME pa_progress_rollup.prog_pa_period_name%TYPE;
5698: L_PROG_GL_PERIOD_NAME pa_progress_rollup.prog_gl_period_name%TYPE;
5699: l_user_id NUMBER:= fnd_global.user_id;
5700: l_login_id NUMBER:= fnd_global.login_id;
5701: -- Bug 3878024 End
5694: ;
5695:
5696: L_PROGRESS_ROLLUP_ID NUMBER;
5697: L_PROG_PA_PERIOD_NAME pa_progress_rollup.prog_pa_period_name%TYPE;
5698: L_PROG_GL_PERIOD_NAME pa_progress_rollup.prog_gl_period_name%TYPE;
5699: l_user_id NUMBER:= fnd_global.user_id;
5700: l_login_id NUMBER:= fnd_global.login_id;
5701: -- Bug 3878024 End
5702:
5798: AND date_computed = p_as_of_date
5799: AND structure_type = p_structure_type
5800: ;
5801:
5802: --Update pa_progress_rollup
5803: UPDATE pa_progress_rollup
5804: SET completed_percentage = 100,
5805: Actual_Finish_date = p_actual_finish_date,
5806: EQPMT_ETC_EFFORT = 0,
5799: AND structure_type = p_structure_type
5800: ;
5801:
5802: --Update pa_progress_rollup
5803: UPDATE pa_progress_rollup
5804: SET completed_percentage = 100,
5805: Actual_Finish_date = p_actual_finish_date,
5806: EQPMT_ETC_EFFORT = 0,
5807: OTH_ETC_COST_TC = 0,
5987: l_PROGRESS_ROLLUP_ID := null;
5988: l_prog_pa_period_name := PA_PROGRESS_UTILS.Prog_Get_Pa_Period_Name(l_push_as_of_date);
5989: l_prog_gl_period_name := PA_PROGRESS_UTILS.Prog_Get_GL_Period_Name(l_push_as_of_date);
5990:
5991: PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
5992: X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
5993: ,X_PROJECT_ID => p_project_id
5994: ,X_OBJECT_ID => cur_task_assignments_rec.resource_list_member_id
5995: ,X_OBJECT_TYPE => 'PA_ASSIGNMENTS'
6189: AND ((current_flag = 'Y' and published_flag = 'Y') or
6190: (current_flag = 'N' and published_flag = 'N'))
6191: ;
6192:
6193: --Update pa_progress_rollup
6194: UPDATE pa_progress_rollup
6195: SET actual_start_date = decode(actual_start_date, null, cur_task_assignments_rec.assignment_start_date, actual_start_date),
6196: Actual_Finish_date = p_actual_finish_date,
6197: estimated_remaining_effort = 0,
6190: (current_flag = 'N' and published_flag = 'N'))
6191: ;
6192:
6193: --Update pa_progress_rollup
6194: UPDATE pa_progress_rollup
6195: SET actual_start_date = decode(actual_start_date, null, cur_task_assignments_rec.assignment_start_date, actual_start_date),
6196: Actual_Finish_date = p_actual_finish_date,
6197: estimated_remaining_effort = 0,
6198: EQPMT_ETC_EFFORT = 0,
6308: l_PROGRESS_ROLLUP_ID := null;
6309: l_prog_pa_period_name := PA_PROGRESS_UTILS.Prog_Get_Pa_Period_Name(l_push_as_of_date);
6310: l_prog_gl_period_name := PA_PROGRESS_UTILS.Prog_Get_GL_Period_Name(l_push_as_of_date);
6311:
6312: PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
6313: X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
6314: ,X_PROJECT_ID => p_project_id
6315: ,X_OBJECT_ID => cur_task_assignments_rec.proj_element_id
6316: ,X_OBJECT_TYPE => 'PA_TASKS'
6442: -- Bug 3922325 : Hidden Assignment record also shd get created
6443:
6444: l_PROGRESS_ROLLUP_ID := null;
6445:
6446: PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
6447: X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
6448: ,X_PROJECT_ID => p_project_id
6449: ,X_OBJECT_ID => cur_task_assignments_rec.resource_list_member_id
6450: ,X_OBJECT_TYPE => 'PA_ASSIGNMENTS'
6633: );
6634: --END IF;-- PA_PROGRESS_UTILS.get_system_task_status(l_old_status_code) <> 'COMPLETED' THEN
6635: END IF; -- IF cur_task_assignments_rec.current_flag = 'Y' OR cur_task_assignments_rec.current_flag = 'X' THEN
6636: -- Bug 3922325 : Hidden Assignment records also shd be updated
6637: UPDATE pa_progress_rollup
6638: SET actual_start_date = decode(actual_start_date, null, cur_task_assignments_rec.assignment_start_date, actual_start_date),
6639: Actual_Finish_date = p_actual_finish_date,
6640: estimated_remaining_effort = 0,
6641: EQPMT_ETC_EFFORT = 0,
6696: AND PA_PROGRESS_UTILS.get_system_task_status( status_code )
6697: NOT IN ( 'CANCELLED', 'COMPLETED' )
6698: ;
6699:
6700: --Update pa_progress_rollup
6701: UPDATE pa_progress_rollup
6702: SET Actual_Finish_date = p_actual_finish_date,
6703: actual_start_date = decode(actual_start_date, null, cur_tasks_rec.scheduled_start_date, actual_start_date),
6704: completed_percentage = 100,
6697: NOT IN ( 'CANCELLED', 'COMPLETED' )
6698: ;
6699:
6700: --Update pa_progress_rollup
6701: UPDATE pa_progress_rollup
6702: SET Actual_Finish_date = p_actual_finish_date,
6703: actual_start_date = decode(actual_start_date, null, cur_tasks_rec.scheduled_start_date, actual_start_date),
6704: completed_percentage = 100,
6705: estimated_remaining_effort = 0,
6733:
6734: -- 3922325 : Added code to create task record if they do not exists
6735: IF sql%notfound THEN
6736: IF g1_debug_mode = 'Y' THEN
6737: 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);
6738: END IF;
6739:
6740: l_percent_complete := 100;
6741: l_PROGRESS_ROLLUP_ID := null;
6742: l_push_as_of_date := p_as_of_date;
6743: l_prog_pa_period_name := PA_PROGRESS_UTILS.Prog_Get_Pa_Period_Name(l_push_as_of_date);
6744: l_prog_gl_period_name := PA_PROGRESS_UTILS.Prog_Get_GL_Period_Name(l_push_as_of_date);
6745:
6746: PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
6747: X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
6748: ,X_PROJECT_ID =>p_project_id
6749: ,X_OBJECT_ID => cur_tasks_rec.proj_element_id
6750: ,X_OBJECT_TYPE => 'PA_TASKS'
6873: RAISE FND_API.G_EXC_ERROR;
6874: END IF;
6875:
6876: IF g1_debug_mode = 'Y' THEN
6877: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.UPDATE_TASK_PROGRESS', x_Msg => 'Inserted Tasks Record in PA_PROGRESS_ROLLUP ', x_Log_Level=> 3);
6878: END IF;
6879: END IF;
6880:
6881: END LOOP;
7255:
7256: CURSOR C1(p_project_id NUMBER, p_working_str_version_id NUMBER) is
7257: -- Bug # 4581937.
7258: select ppr.*
7259: from pa_progress_rollup ppr, pa_proj_element_versions ppev --4871809
7260: where ppr.project_id = p_project_id
7261: and ppr.structure_version_id is null
7262: and ppr.object_type in ('PA_STRUCTURES','PA_ASSIGNMENTS','PA_TASKS')
7263: --and pa_proj_elements_utils.is_lowest_task(ppr.object_version_id) = 'Y' --bug 4050532
7269:
7270:
7271: CURSOR C2(p_project_id NUMBER) is
7272: select max(as_of_date)
7273: from pa_progress_rollup ppr
7274: where ppr.project_id = p_project_id
7275: and ppr.structure_version_id is null
7276: and ppr.structure_type = 'WORKPLAN'
7277: and ppr.current_flag <> 'W' -- Bug 3879461
7329: where ptav.project_id= p_project_id
7330: and ptav.structure_version_id = p_working_str_version_id
7331: and ptav.planning_start_date <= l_etc_start_date
7332: -- Bug 3958686 : Commenting the ta_display_flag portion
7333: and NOT EXISTS ( select 'x' from pa_progress_rollup
7334: where project_id=p_project_id
7335: and object_id = ptav.resource_list_member_id
7336: and NVL(cbs_element_id,-1) = NVL(ptav.cbs_element_id,-1)-- added for 16895485
7337: and proj_element_id = ptav.task_id
8137: -- Assign the pl/sql tables bug 4050532
8138:
8139: l_index2 := l_index2 + 1;
8140:
8141: select pa_progress_rollup_s.nextval into progress_rollup_id(l_index2) from dual;
8142: percent_complete_id(l_index2) := to_number(null);
8143: PROJECT_ID(l_index2) := p_project_Id;
8144: OBJECT_ID(l_index2) := l_new_object_id;
8145: l_cbs_element_id(l_index2) := l_c1rec.cbs_element_id;-- added for 16895485
8302: 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);
8303: 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);
8304: END IF;
8305:
8306: UPDATE pa_progress_rollup
8307: SET EFF_ROLLUP_PERCENT_COMP = l_EFF_ROLLUP_PERCENT_COMP
8308: ,last_update_date = SYSDATE
8309: WHERE progress_rollup_id = l_c1rec.progress_rollup_id;
8310: IF g1_debug_mode = 'Y' THEN
8329: IF g1_debug_mode = 'Y' THEN
8330: 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);
8331: END IF;
8332:
8333: DELETE FROM pa_progress_rollup WHERE project_id = p_project_id and structure_version_id = p_working_str_version_id;
8334:
8335: forall l_index1 in 1..l_index2
8336: INSERT INTO pa_progress_rollup(
8337: PROGRESS_ROLLUP_ID
8332:
8333: DELETE FROM pa_progress_rollup WHERE project_id = p_project_id and structure_version_id = p_working_str_version_id;
8334:
8335: forall l_index1 in 1..l_index2
8336: INSERT INTO pa_progress_rollup(
8337: PROGRESS_ROLLUP_ID
8338: ,PERCENT_COMPLETE_ID
8339: ,PROJECT_ID
8340: ,OBJECT_ID
8463: ,EQPMT_ACT_RAWCOST_TO_DATE_PC(l_index1)
8464: ,L_CBS_ELEMENT_ID(l_index1)-- added for 16895485
8465: );
8466: IF g1_debug_mode = 'Y' THEN
8467: 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);
8468: END IF;
8469: END IF;
8470:
8471:
8885: x_return_status := FND_API.G_RET_STS_SUCCESS;
8886:
8887: if (p_calling_context = 'STRUCTURE_VERSION') then
8888:
8889: delete from pa_progress_rollup
8890: where project_id = p_project_id
8891: -- and object_version_id = p_structure_version_id -- Bug 4190086
8892: and structure_version_id = p_structure_version_id -- Bug 4190086
8893: ;
8893: ;
8894:
8895: elsif (p_calling_context = 'PROJECT_STRUCTURE') then
8896:
8897: delete from pa_progress_rollup
8898: where project_id = p_project_id;
8899:
8900: delete from pa_proj_progress_attr
8901: where project_id = p_project_id;
8905:
8906: elsif (p_calling_context = 'TASK_VERSION') then
8907:
8908: forall i in p_task_version_id.first..p_task_version_id.last
8909: delete /*+ INDEX(PA_PROGRESS_ROLLUP PA_PROGRESS_ROLLUP_N4)*/ from pa_progress_rollup --Added hint for 15876400
8910: where project_id = p_project_id
8911: and object_version_id = p_task_version_id(i);
8912:
8913: forall i in p_task_version_id.first..p_task_version_id.last
8997: AND pst.structure_type = 'WORKPLAN';
8998: -- Bug 4600547 Begin
8999: CURSOR c_check_prog_exists IS
9000: SELECT 'Y'
9001: FROM pa_progress_rollup
9002: where project_id = P_Project_ID
9003: AND structure_type = 'WORKPLAN'
9004: and structure_version_id is null;
9005:
9009: -- Bug 5961235 Begin
9010: CURSOR c_check_prog_exists_prg IS
9011: SELECT 'Y' from dual
9012: where exists ( select DISTINCT ppev.PROJECT_ID
9013: FROM pa_progress_rollup ppr,
9014: pa_proj_element_versions ppev
9015: where ppr.project_id=ppev.project_id
9016: and ppev.OBJECT_TYPE = 'PA_STRUCTURES'
9017: and ppr.structure_type = 'WORKPLAN'
9317: cursor cur_percent_complete(p_project_id NUMBER, p_object_id NUMBER
9318: , p_object_version_id NUMBER, p_object_type VARCHAR2
9319: , p_as_of_date DATE) is
9320: select 'Y'
9321: from pa_progress_rollup
9322: where project_id = p_project_id
9323: and object_id = p_object_id
9324: and object_type = p_object_type
9325: and as_of_date = p_as_of_date
10474:
10475: CURSOR cur_rollup(c_period_type VARCHAR2)
10476: IS
10477: SELECT decode(c_period_type, 'P', PROG_PA_PERIOD_NAME, 'G', PROG_GL_PERIOD_NAME, null )
10478: FROM pa_progress_rollup
10479: WHERE project_id = p_project_id
10480: AND object_id = p_object_id
10481: AND proj_element_id = p_proj_element_id
10482: AND as_of_date = p_as_of_date
11143:
11144:
11145: CURSOR cur_fin_low_tasks(c_structure_type VARCHAR, c_structure_version_id number ) -- Bug 3957457 : Added c_structure_version_id
11146: IS
11147: -- Bug 3957457 : get percent complete from pa_progress_rollup instead of pa_percent_complete
11148: -- 4392189 : Program Reporting Changes - Phase 2 : Get base % complete rather rollup % complete
11149: -- SELECT ppv.proj_element_id, ppv.element_version_id, ppv.record_version_number, nvl(ppr.completed_percentage, ppr.eff_rollup_percent_comp) completed_percentage
11150: SELECT ppv.proj_element_id, ppv.element_version_id, ppv.record_version_number, nvl(ppr.completed_percentage, ppr.base_percent_complete) completed_percentage
11151: FROM pa_proj_element_versions ppv, pa_progress_rollup ppr
11147: -- Bug 3957457 : get percent complete from pa_progress_rollup instead of pa_percent_complete
11148: -- 4392189 : Program Reporting Changes - Phase 2 : Get base % complete rather rollup % complete
11149: -- SELECT ppv.proj_element_id, ppv.element_version_id, ppv.record_version_number, nvl(ppr.completed_percentage, ppr.eff_rollup_percent_comp) completed_percentage
11150: SELECT ppv.proj_element_id, ppv.element_version_id, ppv.record_version_number, nvl(ppr.completed_percentage, ppr.base_percent_complete) completed_percentage
11151: FROM pa_proj_element_versions ppv, pa_progress_rollup ppr
11152: WHERE ppv.project_id = p_project_id
11153: AND ppv.financial_task_flag = 'Y'
11154: AND ppv.parent_structure_version_id = c_structure_version_id -- Bug 3957457
11155: -- AND PA_PROJ_ELEMENTS_UTILS.check_child_element_exist(ppv.element_version_id) = 'N' bug 4086613
11170:
11171: -- 4392189 : Program Reporting Changes - Phase 2 : Get base % complete rather rollup % complete
11172: CURSOR cur_wp_task( c_object_version_id NUMBER, c_structure_version_id number ) -- Bug 3952006 : Added c_structure_version_id
11173: IS
11174: -- Bug 3952006 : get percent complete from pa_progress_rollup instead of pa_percent_complete
11175: --SELECT ppv.proj_element_id, ppv.element_version_id, ppv.record_version_number, nvl(ppr.completed_percentage, ppr.eff_rollup_percent_comp) completed_percentage
11176: SELECT ppv.proj_element_id, ppv.element_version_id, ppv.record_version_number, nvl(ppr.completed_percentage, ppr.base_percent_complete) completed_percentage
11177: FROM pa_proj_element_versions ppv, pa_progress_rollup ppr, pa_object_relationships por
11178: WHERE ppv.project_id = p_project_id
11173: IS
11174: -- Bug 3952006 : get percent complete from pa_progress_rollup instead of pa_percent_complete
11175: --SELECT ppv.proj_element_id, ppv.element_version_id, ppv.record_version_number, nvl(ppr.completed_percentage, ppr.eff_rollup_percent_comp) completed_percentage
11176: SELECT ppv.proj_element_id, ppv.element_version_id, ppv.record_version_number, nvl(ppr.completed_percentage, ppr.base_percent_complete) completed_percentage
11177: FROM pa_proj_element_versions ppv, pa_progress_rollup ppr, pa_object_relationships por
11178: WHERE ppv.project_id = p_project_id
11179: AND ppv.parent_structure_version_id = c_structure_version_id -- Bug 3952006
11180: AND ppv.project_id = ppr.project_id(+)
11181: AND por.object_id_to1 = c_object_version_id
11209: CURSOR c_max_date IS
11210: -- 4487630 : Now Date will be derived as max of existing financial as of date, workplan as of date, sysdate
11211: -- select max(decode(structure_type, 'WORKPLAN', as_of_date, as_of_date+1))
11212: select max(as_of_date)
11213: from pa_progress_rollup
11214: where project_id = p_project_id
11215: and structure_version_id is null
11216: and structure_type IN('WORKPLAN', 'FINANCIAL')
11217: and current_flag = 'Y';
11577: p_baseline_flag VARCHAR2(1):='N';
11578:
11579: CURSOR C2(p_project_id NUMBER) is
11580: select max(trunc(as_of_date)) -- 5294838 truncate
11581: from pa_progress_rollup ppr
11582: where ppr.project_id = p_project_id
11583: and ppr.structure_version_id is null
11584: and ppr.structure_type = 'WORKPLAN'
11585: and current_flag = 'Y';
11593:
11594:
11595: CURSOR c_get_working_wp_progress is
11596: select ppr.*
11597: from pa_progress_rollup ppr
11598: where ppr.project_id = p_project_id
11599: -- and ppr.structure_version_id is not null -- Bug 4190086
11600: and ppr.structure_version_id = p_working_str_ver_id -- Bug 4190086
11601: and ppr.structure_type = 'WORKPLAN';
11635: EQPMT_ACT_COST_TO_DATE_FC,
11636: EQPMT_ACT_COST_TO_DATE_PC,
11637: EARNED_VALUE ,
11638: CBS_ELEMENT_ID-- added for 16895485
11639: from pa_progress_rollup ppr
11640: where ppr.project_id = p_project_id
11641: and ppr.structure_version_id = p_working_str_ver_id -- Bug 4190086
11642: and ppr.structure_type = 'WORKPLAN'
11643: and ppr.proj_element_id <> 0 -- 4398411
11675: EQPMT_ACT_COST_TO_DATE_FC,
11676: EQPMT_ACT_COST_TO_DATE_PC,
11677: EARNED_VALUE,
11678: CBS_ELEMENT_ID-- added for 16895485
11679: from pa_progress_rollup ppr1
11680: where ppr1.project_id = p_project_id
11681: and ppr1.structure_version_id IS NULL
11682: and ppr1.structure_type = 'WORKPLAN'
11683: and ppr1.current_flag = 'Y'
11683: and ppr1.current_flag = 'Y'
11684: and ppr1.proj_element_id <> 0 -- 4398411
11685: and ppr1.proj_element_id is not null -- 4398411
11686: and not exists
11687: (select 'xyz' from pa_progress_rollup ppr2
11688: where ppr2.project_id = ppr1.project_id
11689: and ppr2.object_id = ppr1.object_id
11690: and nvl(ppr2.proj_element_id,0) = nvl(ppr1.proj_element_id,0)
11691: and ppr2.structure_version_id = p_working_str_ver_id
11736: and type_id = task_type_id;
11737:
11738: 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,c_cbs_element_id Number) is
11739: select rowid, ppr.*
11740: from pa_progress_rollup ppr
11741: where ppr.project_id = c_project_id
11742: and ppr.structure_type = 'WORKPLAN'
11743: and ppr.structure_version_id is null
11744: --and trunc(ppr.as_of_date) = trunc(c_as_of_date) -- Bug 4227280
11749: and NVL(ppr.cbs_element_id,-1)=NVL(c_cbs_element_id,-1);
11750:
11751: 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,c_cbs_element_id Number) is
11752: select ppr.*
11753: from pa_progress_rollup ppr
11754: where ppr.project_id = c_project_id
11755: and ppr.structure_type = 'WORKPLAN'
11756: and ppr.structure_version_id is null
11757: ------ and trunc(ppr.as_of_date) < trunc(c_as_of_date) redundant cond. 4743866
11815: L_LATEST_PUB_WP_VERSION_ID NUMBER;
11816: L_PRE_PUB_STR_VER_ID NUMBER;
11817: L_TASK_WEIGHT_BASIS_CODE pa_proj_progress_attr.task_weight_basis_code%TYPE;
11818: L_OBJECT_VERSION_ID NUMBER;
11819: L_PROGRESS_STATUS_CODE pa_progress_rollup.progress_status_code%TYPE;
11820: L_ACTUAL_START_DATE DATE;
11821: L_ACTUAL_FINISH_DATE DATE;
11822: L_ESTIMATED_START_DATE DATE;
11823: L_ESTIMATED_FINISH_DATE DATE;
11826: L_SYSTEM_STATUS_CODE pa_project_statuses.project_system_status_code%TYPE;
11827: L_PROGRESS_ROLLUP_ID NUMBER;
11828: L_BAC_VALUE NUMBER;
11829: L_EARNED_VALUE NUMBER;
11830: L_PROG_PA_PERIOD_NAME pa_progress_rollup.PROG_PA_PERIOD_NAME%TYPE;
11831: L_PROG_GL_PERIOD_NAME pa_progress_rollup.PROG_GL_PERIOD_NAME%TYPE;
11832: l_scheduled_start_date DATE;
11833: l_scheduled_finish_date DATE;
11834: l_baselined_str_ver_id NUMBER;
11827: L_PROGRESS_ROLLUP_ID NUMBER;
11828: L_BAC_VALUE NUMBER;
11829: L_EARNED_VALUE NUMBER;
11830: L_PROG_PA_PERIOD_NAME pa_progress_rollup.PROG_PA_PERIOD_NAME%TYPE;
11831: L_PROG_GL_PERIOD_NAME pa_progress_rollup.PROG_GL_PERIOD_NAME%TYPE;
11832: l_scheduled_start_date DATE;
11833: l_scheduled_finish_date DATE;
11834: l_baselined_str_ver_id NUMBER;
11835: l_oth_etc_quantity NUMBER;
11861: l_upd_new_elem_ver_id_flag VARCHAR2(1) := 'Y';
11862:
11863: CURSOR cur_lp_progress is
11864: select *
11865: from pa_progress_rollup ppr
11866: where ppr.project_id = p_project_id
11867: and ppr.structure_version_id is null
11868: and ppr.object_type = 'PA_TASKS'
11869: and structure_type = 'WORKPLAN'
11871: 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
11872: and current_flag = 'Y'
11873: Union
11874: select *
11875: from pa_progress_rollup ppr
11876: where ppr.project_id = p_project_id
11877: and ppr.structure_version_id is null
11878: and ppr.object_type = 'PA_ASSIGNMENTS'
11879: and structure_type = 'WORKPLAN'
12163: where project_id = p_project_id
12164: and structure_type = 'WORKPLAN'
12165: and published_flag = 'N';
12166:
12167: DELETE from pa_progress_rollup
12168: where project_id = p_project_id
12169: and structure_type = 'WORKPLAN'
12170: and structure_version_id is null
12171: and current_flag = 'W';
12238: -- delete it
12239: -- IF g1_debug_mode = 'Y' THEN
12240: -- pa_debug.write(x_Module=>'PA_PROGRESS_PUB.PUBLISH_PROGRESS', x_Msg => 'Working PPR Record Found, Deleting it', x_Log_Level=> 3);
12241: -- END IF;
12242: -- PA_PROGRESS_ROLLUP_PKG.DELETE_ROW(l_ppr_rec.rowid);
12243: --END IF;
12244:
12245: l_last_ppr_rec := null;
12246: 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,l_rec.cbs_element_id);
12965:
12966: ---5212999
12967: if (l_upd_rollup_prog = 'N') then
12968: FORALL i IN 1..l_index
12969: update pa_progress_rollup
12970: set current_flag = 'N'
12971: where project_id = l_project_id_tbl(i)
12972: and current_flag = 'Y'
12973: and structure_version_id is null
12978: and proj_element_id = l_task_id_tbl(i);
12979:
12980: else
12981: FORALL i IN 1..l_index
12982: delete from pa_progress_rollup
12983: where project_id = l_project_id_tbl(i)
12984: and current_flag = 'Y'
12985: and structure_version_id is null
12986: and structure_type = 'WORKPLAN'
13019:
13020:
13021:
13022: FORALL i IN 1..l_index
13023: insert into pa_progress_rollup(
13024: PROGRESS_ROLLUP_ID
13025: ,PROJECT_ID
13026: ,OBJECT_ID
13027: ,OBJECT_TYPE
13104: ,EQPMT_ETC_RAWCOST_PC
13105: ,BASE_PERCENT_COMPLETE -- 4604661
13106: ,CBS_ELEMENT_ID
13107: ) VALUES (
13108: PA_PROGRESS_ROLLUP_S.nextval
13109: ,l_project_id_tbl(i)
13110: ,l_object_id_tbl(i)
13111: ,l_object_type_tbl(i)
13112: ,l_as_of_date
13422: and object_type = 'PA_ASSIGNMENTS'
13423: and structure_type = 'WORKPLAN';
13424:
13425: cursor max_as_of_date_rollup is
13426: select max(as_of_date) from pa_progress_rollup
13427: where project_id = l_project_id
13428: and structure_type = 'WORKPLAN'
13429: and structure_version_id is null
13430: and current_flag = 'Y';
13429: and structure_version_id is null
13430: and current_flag = 'Y';
13431:
13432: cursor max_as_of_date_rollup_tsk is
13433: select max(as_of_date) from pa_progress_rollup
13434: where project_id = l_project_id
13435: and object_type = 'PA_TASKS'
13436: and structure_type = 'WORKPLAN'
13437: and structure_version_id is null
13809: OTH_QUANTITY_TO_DATE,
13810: OTH_ETC_QUANTITY,
13811: -- Add cbs_element_id
13812: CBS_ELEMENT_ID
13813: from pa_progress_rollup
13814: where project_id = l_project_id
13815: and object_id = l_object_id
13816: and object_Type = l_object_type
13817: -- Add cbs_element_id
13820: and structure_version_id is null
13821: and proj_element_id = l_task_id
13822: AND current_flag <> 'W' -- Bug 3879461
13823: and trunc(as_of_date) = (select max(trunc(as_of_Date))
13824: from pa_progress_rollup
13825: where project_id = l_project_id
13826: and object_id = l_object_id
13827: and object_Type = l_object_type
13828: -- Add cbs_element_id
13960: PROG_PA_PERIOD_NAME,
13961: PROG_GL_PERIOD_NAME,
13962: OTH_QUANTITY_TO_DATE,
13963: OTH_ETC_QUANTITY
13964: from pa_progress_rollup
13965: where project_id = l_project_id
13966: and object_id = l_object_id
13967: and object_Type = l_object_type
13968: -- Add cbs_element_id
14099: PROG_PA_PERIOD_NAME,
14100: PROG_GL_PERIOD_NAME,
14101: OTH_QUANTITY_TO_DATE,
14102: OTH_ETC_QUANTITY
14103: from pa_progress_rollup
14104: where project_id = l_project_id
14105: and object_id = l_object_id
14106: and object_type = l_object_type
14107: -- Add cbs_element_id
14115:
14116: -- cursor to get all assignment records for that as of date
14117: cursor all_assignments_asofdate is
14118: select *
14119: from pa_progress_rollup
14120: where project_id = l_project_id
14121: and object_type = 'PA_ASSIGNMENTS'
14122: and structure_version_id is null
14123: and structure_Type = 'WORKPLAN'
14127:
14128: -- cursor to get all assignment records for the previous as of date
14129: cursor all_assign_prev_asofdate is
14130: select *
14131: from pa_progress_rollup ppr
14132: where ppr.project_id = l_project_id
14133: and ppr.object_type = 'PA_ASSIGNMENTS'
14134: and ppr.structure_version_id is null
14135: and ppr.structure_Type = 'WORKPLAN'
14134: and ppr.structure_version_id is null
14135: and ppr.structure_Type = 'WORKPLAN'
14136: AND current_flag <> 'W' -- Bug 3879461
14137: and ppr.as_of_date = (select /*+ no_merge leading(PPr1) */ max(as_of_date) --Added hint for 11694228
14138: from pa_progress_rollup ppr1
14139: where ppr1.project_id = l_project_id
14140: and ppr1.object_type = 'PA_ASSIGNMENTS'
14141: and ppr1.object_id = ppr.object_id
14142: and ppr1.proj_element_id = ppr.proj_element_id
14144: and ppr1.structure_Type = 'WORKPLAN'
14145: AND current_flag <> 'W' -- Bug 3879461
14146: and ppr1.as_of_date < l_temp_as_of_Date
14147: and not exists (select 1 ---4871809 performance bug
14148: from pa_progress_rollup ppr2
14149: where ppr2.project_id = l_project_id
14150: and ppr2.object_type = 'PA_ASSIGNMENTS'
14151: and ppr2.object_id = ppr.object_id
14152: and ppr2.proj_element_id = ppr.proj_element_id
14286: , ppr.actual_finish_date
14287: , ppr.proj_element_id
14288: , ppr.object_type
14289: , ppr.project_id
14290: FROM pa_progress_rollup ppr
14291: , pa_proj_elements ppe
14292: WHERE ppr.progress_rollup_id = c_progress_rollup_id
14293: AND ppr.proj_element_id = ppe.proj_element_id
14294: ;
14314: , ppr.estimated_finish_date
14315: , ppr.actual_start_date
14316: , ppr.actual_finish_date
14317: , ppe.status_code
14318: FROM pa_progress_rollup ppr
14319: , pa_proj_elements ppe
14320: WHERE ppr.project_id = c_project_id
14321: AND ppr.object_id = c_object_id
14322: AND ppr.object_type = c_object_type
14341: , ppr.object_type
14342: , ppr.as_of_date
14343: , ppr.project_id
14344: , ppr.current_flag
14345: FROM pa_progress_rollup ppr
14346: , pa_proj_elements ppe
14347: WHERE ppr.progress_rollup_id = c_progress_rollup_id
14348: AND ppr.proj_element_id = ppe.proj_element_id
14349: ;
14375:
14376: -- 4654495
14377: cursor some_progress_exists is
14378: select 'Y'
14379: from pa_progress_rollup
14380: where project_id = l_project_id
14381: and proj_element_id = l_task_id
14382: and object_type in ('PA_TASKS','PA_ASSIGNMENTS');
14383:
14841:
14842: -- 4654495
14843: if (p_plan_res_level = 'N' and
14844: (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
14845: --- check in pa_progress_rollup table if data already exists for this task
14846: open some_progress_exists;
14847: fetch some_progress_exists into l_some_progress_exists;
14848: if (some_progress_exists%notfound) then
14849: l_ignore_rec := 'Y';
15977: --- call an api to check if a record exists on that date
15978:
15979: if (l_db_case = '1') then
15980: --- insert with defaults
15981: select pa_progress_rollup_s.nextval into progress_rollup_id(l_index2) from dual;
15982: percent_complete_id(l_index2) := to_number(null);
15983: project_id(l_index2) := l_project_id;
15984: object_id(l_index2) := l_object_id;
15985: pa_debug.log_message('Inside loop object_id '|| object_id(l_index2), 1);
17187:
17188: elsif (l_db_case = '3') then
17189: --- get progress attrs for the task/assignment
17190:
17191: select pa_progress_rollup_s.nextval into progress_rollup_id(l_index2) from dual;
17192: percent_complete_id(l_index2) := to_number(null);
17193: project_id(l_index2) := l_project_id;
17194: object_id(l_index2) := l_object_id;
17195: OBJECT_TYPE(l_index2) := l_rollup_rec_asofdate.object_Type;
18230: if (l_match_found = 'N') then
18231: 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)
18232: + 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
18233: l_index2 := l_index2 + 1;
18234: select pa_progress_rollup_s.nextval into progress_rollup_id(l_index2) from dual;
18235: percent_complete_id(l_index2) := to_number(null);
18236: project_id(l_index2) := l_project_id;
18237: object_id(l_index2) := all_assign_prev_asofdate_rec.object_id;
18238: OBJECT_TYPE(l_index2) := all_assign_prev_asofdate_rec.object_Type;
18740:
18741: if p_plan_res_level = 'Y' then
18742: --- do bulk insert
18743: forall l_index1 in 1..l_index2
18744: insert into pa_progress_rollup(
18745: PROGRESS_ROLLUP_ID
18746: ,PROJECT_ID
18747: ,OBJECT_ID
18748: ,OBJECT_TYPE
18999: );
19000: else
19001:
19002: forall l_index1 in 1..l_index2
19003: insert into pa_progress_rollup(
19004: PROGRESS_ROLLUP_ID
19005: ,PROJECT_ID
19006: ,OBJECT_ID
19007: ,OBJECT_TYPE
19255: end if;
19256:
19257: IF g1_debug_mode = 'Y' THEN
19258: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.get_summarized_actuals',
19259: x_Msg => 'Done with bulk insert into pa_progress_rollup',
19260: x_Log_Level=> 3);
19261: END IF;
19262:
19263: -- Bug 6917961
19298: -- Bug 6917961
19299:
19300: --- do bulk update
19301: forall l_index1 in 1..l_index3
19302: update pa_progress_rollup set
19303: OBJECT_VERSION_ID = u_object_version_id(l_index1)
19304: ,LAST_UPDATE_DATE = sysdate
19305: ,LAST_UPDATED_BY = fnd_global.user_id
19306: ,LAST_UPDATE_LOGIN = fnd_global.login_id
19363: where progress_rollup_id = u_progress_rollup_id(l_index1);
19364:
19365: IF g1_debug_mode = 'Y' THEN
19366: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.get_summarized_actuals',
19367: x_Msg => 'Done with bulk update into pa_progress_rollup',
19368: x_Log_Level=> 3);
19369: END IF;
19370:
19371: forall l_index1 in 1..l_index4
19368: x_Log_Level=> 3);
19369: END IF;
19370:
19371: forall l_index1 in 1..l_index4
19372: update pa_progress_rollup set
19373: CURRENT_FLAG = 'N'
19374: where progress_rollup_id = u1_progress_rollup_id(l_index1);
19375:
19376: IF g1_debug_mode = 'Y' THEN
19374: where progress_rollup_id = u1_progress_rollup_id(l_index1);
19375:
19376: IF g1_debug_mode = 'Y' THEN
19377: pa_debug.write(x_Module=>'PA_PROGRESS_PUB.get_summarized_actuals',
19378: x_Msg => 'Done with bulk update of current flag in pa_progress_rollup',
19379: x_Log_Level=> 3);
19380: END IF;
19381:
19382: pa_debug.log_message('GET_SUMM_ACT: Done insert/update of pa_progress_rollup.', 1);
19378: x_Msg => 'Done with bulk update of current flag in pa_progress_rollup',
19379: x_Log_Level=> 3);
19380: END IF;
19381:
19382: pa_debug.log_message('GET_SUMM_ACT: Done insert/update of pa_progress_rollup.', 1);
19383:
19384: if ((l_index2 > 0 or l_index3 > 0) and p_plan_res_level = 'N') then
19385: --- now we are done with updating all actuals
19386: --- we need to rollup data for the l_temp_as_of_date
20204: AND current_flag = 'Y'
20205: AND object_type = l_object_type
20206: AND structure_type = p_structure_type;
20207:
20208: UPDATE pa_progress_rollup
20209: SET current_flag = 'N'
20210: WHERE project_id = p_project_id
20211: AND object_id = p_object_id
20212: AND object_type = p_object_type
20313: );
20314:
20315: IF l_PROGRESS_ROLLUP_ID IS NULL
20316: THEN
20317: PA_PROGRESS_ROLLUP_PKG.INSERT_ROW(
20318: X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
20319: ,X_PROJECT_ID => p_project_id
20320: ,X_OBJECT_ID => l_object_id
20321: ,X_OBJECT_TYPE => l_object_type
20438: ,X_SUBPRJ_EQPMT_ETC_RAWCOST_TC => null
20439: ,X_SUBPRJ_EQPMT_ETC_RAWCOST_FC => null
20440: ,X_SUBPRJ_EQPMT_ETC_RAWCOST_PC => null);
20441: ELSE
20442: PA_PROGRESS_ROLLUP_PKG.UPDATE_ROW(
20443: X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
20444: ,X_PROJECT_ID => p_project_id
20445: ,X_OBJECT_ID => l_object_id
20446: ,X_OBJECT_TYPE => l_object_type
20675: 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);
20676: END IF;
20677:
20678:
20679: PA_PROGRESS_ROLLUP_PKG.UPDATE_ROW(
20680: X_PROGRESS_ROLLUP_ID => l_PROGRESS_ROLLUP_ID
20681: ,X_PROJECT_ID => p_project_id
20682: ,X_OBJECT_ID => l_object_id
20683: ,X_OBJECT_TYPE => l_object_type
20900: -- Bug 3693757 : This cursor is wrong as it may return wrong task_wt_basis_code of some other projects' task
20901:
20902: cursor l_ve_cur(p_project_id NUMBER, p_structure_version_id NUMBER) is
20903: select as_of_date, task_wt_basis_code
20904: from pa_progress_rollup
20905: where project_id = p_project_id -- Bug 3693757
20906: and structure_version_id = p_structure_version_id -- Bug 3693757
20907: and object_type = 'PA_STRUCTURES' -- Bug 3693757
20908: ;
20907: and object_type = 'PA_STRUCTURES' -- Bug 3693757
20908: ;
20909: /* Bug 3693757
20910: and as_of_date = (select max(as_of_date)
20911: from pa_progress_rollup
20912: where project_id = p_project_id
20913: and structure_version_id = p_structure_version_id
20914: and current_flag = 'Y');
20915: */
20928: --bug # 3792713 Satish end
20929:
20930: cursor l_vd_cur(p_project_id NUMBER, p_structure_version_id NUMBER) is
20931: select as_of_date, task_wt_basis_code
20932: from pa_progress_rollup
20933: where project_id = p_project_id
20934: and structure_version_id = p_structure_version_id;
20935:
20936: --added by maansari for
21437: -- null to not null. In this case only ETC needs to be pushed as PJi will derive actuals as planned-etc.
21438:
21439: CURSOR cur_working_wp_progress is
21440: select *
21441: from pa_progress_rollup ppr
21442: where ppr.project_id = p_project_id
21443: and ppr.structure_version_id = p_src_str_ver_id
21444: and ppr.object_type = 'PA_ASSIGNMENTS'
21445: and structure_type = 'WORKPLAN'
21447:
21448:
21449: CURSOR cur_lp_progress is
21450: select *
21451: from pa_progress_rollup ppr
21452: where ppr.project_id = p_project_id
21453: and ppr.structure_version_id is null
21454: and ppr.object_type = 'PA_ASSIGNMENTS'
21455: and structure_type = 'WORKPLAN'
21457: -- Using current_flag = 'Y' may push data of working progress too. Is it fine?
21458:
21459: l_working_prog VARCHAR2(1) := 'N';
21460:
21461: l_ppr_rec pa_progress_rollup%rowtype;
21462:
21463: -- Bug 3764224 : Changed the cusrsor def'n
21464: cursor cur_ptav (p_object_id NUMBER, p_task_id NUMBER) is
21465: select ptav2.resource_list_member_id, ptav2.txn_currency_code
21505: l_prog_mode varchar2(1);
21506:
21507: cursor cur_task_assgn(p_project_id NUMBER, p_object_version_id NUMBER) is
21508: select count(*)
21509: from pa_progress_rollup ppr
21510: where ppr.project_id = p_project_id
21511: and ppr.object_version_id = p_object_version_id
21512: and ppr.object_type = 'PA_ASSIGNMENTS'
21513: and ppr.structure_version_id is null
21520: --bug 3968791
21521: CURSOR cur_select_all_assgns
21522: IS
21523: SELECT ptav.*
21524: FROM pa_progress_rollup ppr2
21525: --,pa_task_assignments_v ptav --bug 3967374
21526: ,pa_task_asgmts_v ptav
21527: WHERE ppr2.project_id=p_project_id
21528: AND ptav.project_id=p_project_id
21528: AND ptav.project_id=p_project_id
21529: AND ppr2.object_id = ptav.task_id
21530: AND (ptav.resource_list_member_id ) not in (
21531: SELECT object_id
21532: FROM pa_progress_rollup ppr
21533: where ppr.project_id=p_project_id
21534: and ppr.structure_version_id is null
21535: and ppr.current_flag = 'Y'
21536: and ppr.object_type = 'PA_ASSIGNMENTS'
21706: )
21707: )
21708: THEN
21709: IF l_prog_mode = 'P' THEN
21710: DELETE from pa_progress_rollup
21711: WHERE project_id = p_project_id
21712: AND structure_version_id is null
21713: AND structure_type = 'WORKPLAN'
21714: AND object_type = 'PA_ASSIGNMENTS'
21715: AND object_id = l_ppr_rec.object_id
21716: AND proj_element_id = l_ppr_rec.proj_element_id;
21717: END IF;
21718: IF l_prog_mode = 'W' THEN
21719: DELETE from pa_progress_rollup
21720: WHERE project_id = p_project_id
21721: AND structure_version_id = p_src_str_ver_id
21722: AND structure_type = 'WORKPLAN'
21723: AND object_type = 'PA_ASSIGNMENTS'
22310: END IF;
22311: /***********
22312: -- here we need to make ETC as null on assignment records
22313: if (l_calling_context = 'PUBLISH') then
22314: update pa_progress_rollup
22315: set oth_etc_quantity = to_number(null)
22316: ,oth_etc_rawcost_tc = to_number(null)
22317: ,oth_etc_rawcost_pc = to_number(null)
22318: ,oth_etc_rawcost_fc = to_number(null)
22408: pa_proj_element_versions ppv1 --4871809
22409: ,pa_proj_element_versions ppv2
22410: ,pa_object_relationships por1
22411: ,pa_object_relationships por2
22412: ,pa_progress_rollup ppr
22413: WHERE
22414: ppv2.element_version_id = por1.object_id_to1
22415: AND ppv2.object_type = 'PA_STRUCTURES'
22416: AND por1.relationship_type in ( 'LW', 'LF' )