[Home] [Help]
95: /*after calling create_res_asg and update_res_asg
96: *we will create the new res_asg_id for the mapped
97: *rlm_id and task id for target budget_version; and
98: *also, the newly created res_asg_id is written back
99: *to PA_FP_PLANNING_RES_TMP1. We need to update this
100: *value to calc_amt_tmp1 and calc_amt_tmp2 to facilitae
101: *our future operation. */
102: CURSOR update_res_asg IS
103: SELECT task_id,
103: SELECT task_id,
104: resource_list_member_id,
105: cbs_element_id, --bug#16791711
106: resource_assignment_id
107: FROM PA_FP_PLANNING_RES_TMP1;
108:
109: l_upd_task_id_tab PA_PLSQL_DATATYPES.NumTabTyp;
110: l_upd_rlm_id_tab PA_PLSQL_DATATYPES.NumTabTyp;
111: l_upd_cbs_id_tab PA_PLSQL_DATATYPES.NumTabTyp; --bug#16791711
421: --dbms_output.put_line('@@after maintain_res_asg,count of pa_resource_asgs:' ||l_count_tmp);
422:
423: -- Bug 3982592: Between forecast generation processes users may have changed
424: -- planning resource start and end dates. Thus, we must update the resource
425: -- assignments table with the source dates in pa_fp_planning_res_tmp1.
426: -- Bug 4114589: Moved logic for bug 3982592 from UPDATE_RES_ASG to here
427: -- so that we only update the planning dates once. Added manual lines logic.
428:
429: -- Bug 4301959: Modified the Retain Manually Added Lines logic to
437: BULK COLLECT
438: INTO l_res_asg_id_tab,
439: l_start_date_tab,
440: l_end_date_tab
441: FROM pa_fp_planning_res_tmp1
442: GROUP BY resource_assignment_id;
443: ELSIF p_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'Y' THEN
444: IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
445: l_etc_start_date :=
444: IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
445: l_etc_start_date :=
446: PA_FP_GEN_AMOUNT_UTILS.GET_ETC_START_DATE(p_budget_version_id);
447:
448: SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N1)*/
449: tmp1.resource_assignment_id,
450: MIN(tmp1.planning_start_date),
451: MAX(tmp1.planning_end_date)
452: BULK COLLECT
452: BULK COLLECT
453: INTO l_res_asg_id_tab,
454: l_start_date_tab,
455: l_end_date_tab
456: FROM pa_fp_planning_res_tmp1 tmp1,
457: pa_resource_assignments ra
458: WHERE ra.budget_version_id = p_budget_version_id
459: AND ra.task_id = tmp1.task_id
460: AND ra.resource_list_member_id = tmp1.resource_list_member_id
469: AND bl.start_date >= l_etc_start_date
470: AND rownum = 1 )))
471: GROUP BY tmp1.resource_assignment_id;
472: ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
473: SELECT /*+ INDEX(tmp1,PA_FP_PLANNING_RES_TMP1_N1)*/
474: tmp1.resource_assignment_id,
475: MIN(tmp1.planning_start_date),
476: MAX(tmp1.planning_end_date)
477: BULK COLLECT
477: BULK COLLECT
478: INTO l_res_asg_id_tab,
479: l_start_date_tab,
480: l_end_date_tab
481: FROM pa_fp_planning_res_tmp1 tmp1,
482: pa_resource_assignments ra
483: WHERE ra.budget_version_id = p_budget_version_id
484: AND ra.task_id = tmp1.task_id
485: AND ra.resource_list_member_id = tmp1.resource_list_member_id