[Home] [Help]
1456: p_project_id IN NUMBER,
1457: p_wbs_version_id IN NUMBER,
1458: p_resource_list_member_id IN NUMBER ) IS
1459: Select a.alias, b.resource_assignment_id
1460: from pa_resource_list_members a, pa_resource_assignments b, pa_budget_versions bv
1461: where a.resource_list_member_id = b.resource_list_member_id
1462: and b.resource_list_member_id = p_resource_list_member_id
1463: and b.ta_display_flag = 'Y'
1464: and b.budget_version_id = bv.budget_version_id
1473: p_wbs_version_id IN NUMBER,
1474: p_resource_list_member_id IN NUMBER,
1475: p_cbs_element_id IN NUMBER ) IS
1476: Select a.alias, b.resource_assignment_id
1477: from pa_resource_list_members a, pa_resource_assignments b, pa_budget_versions bv
1478: where a.resource_list_member_id = b.resource_list_member_id
1479: and b.resource_list_member_id = p_resource_list_member_id
1480: and b.ta_display_flag = 'Y'
1481: and b.budget_version_id = bv.budget_version_id
1496: -- Bug 4087956
1497: -- Cursor C_Reference_Check(p_res_assignment_reference IN VARCHAR2) IS
1498: Cursor C_Reference_Check(p_res_assignment_reference IN VARCHAR2, c_budget_version_id IN NUMBER) IS
1499: select 'X'
1500: from pa_resource_assignments a --, pa_budget_versions b
1501: where a.pm_res_assignment_reference = p_res_assignment_reference
1502: and a.pm_product_code = p_pm_product_code
1503: and a.project_id = p_pa_project_id
1504: and a.ta_display_flag is not null
1513: p_project_id IN NUMBER,
1514: p_wbs_version_id IN NUMBER,
1515: p_resource_list_member_id IN NUMBER ) IS
1516: Select 'X'
1517: from pa_resource_assignments b, pa_budget_versions bv
1518: where b.resource_list_member_id = p_resource_list_member_id
1519: and b.ta_display_flag is not null
1520: and b.budget_version_id = bv.budget_version_id
1521: and b.project_id = bv.project_id
1530: p_wbs_version_id IN NUMBER,
1531: p_resource_list_member_id IN NUMBER ,
1532: p_cbs_element_id IN NUMBER) IS
1533: Select 'X'
1534: from pa_resource_assignments b, pa_budget_versions bv
1535: where b.resource_list_member_id = p_resource_list_member_id
1536: and b.ta_display_flag is not null
1537: and b.budget_version_id = bv.budget_version_id
1538: and b.project_id = bv.project_id
1546: p_project_id IN NUMBER,
1547: p_wbs_version_id IN NUMBER
1548: ) IS
1549: Select b.budget_version_id
1550: from pa_resource_assignments b, pa_budget_versions bv
1551: where b.ta_display_flag = 'N'
1552: and b.budget_version_id = bv.budget_version_id
1553: and b.project_id = bv.project_id
1554: and bv.project_structure_version_id = p_structure_version_id
1585: CURSOR C_Get_Default_Res_Asgmt(p_project_id IN NUMBER,
1586: p_budget_version_id IN NUMBER)
1587: IS
1588: SELECT resource_assignment_id, wbs_element_version_id
1589: FROM pa_resource_assignments ra, pa_copy_asgmts_temp cat
1590: WHERE ra.wbs_element_version_id = cat.src_elem_ver_id
1591: AND ra.project_id = p_project_id
1592: AND ra.budget_version_id = p_budget_version_id
1593: AND ra.ta_display_flag = 'N';
2572:
2573: IF c_people_res_check%FOUND THEN
2574:
2575:
2576: --dbms_output.put_line('Deleting records of task level effort from pa_resource_assignments. ');
2577:
2578: delete from pa_resource_assignments where
2579: resource_class_code = 'PEOPLE'
2580: and project_id = l_project_id
2574:
2575:
2576: --dbms_output.put_line('Deleting records of task level effort from pa_resource_assignments. ');
2577:
2578: delete from pa_resource_assignments where
2579: resource_class_code = 'PEOPLE'
2580: and project_id = l_project_id
2581: and budget_version_id = c_people_res_rec.budget_version_id
2582: and wbs_element_version_id = lk_task_elem_version_id_tbl(i)
2584: -- Bug 3840323 fix
2585: -- Should delete regardless of product code
2586: --and pm_product_code is not null;
2587:
2588: --dbms_output.put_line('After Deleting records of task level effort from pa_resource_assignments. ');
2589:
2590: END IF;
2591:
2592: close c_people_res_check;
2670:
2671: 1. We currently only delete if the resource being assigned to task is of
2672: 'PEOPLE' class. This IF should be removed, deletion should be done when
2673: resource of ANY class is assigned to task.
2674: 2. We should NOT be deleting directly from pa_resource_assignments. Instead,
2675: we should call pa_fp_planning_transaction_pub.delete_planning_transactions to
2676: to properly delete the assignment record.
2677: */
2678:
2680: /* Updates from the Bug
2681: The UPDATE does not perform very well in volume env. My suggestion to improve
2682: this is to replace the FORALL UPDATE by 2 operations:
2683: * 1. Insert the task version ids into an existing temp table, PA_COPY_ASGMTS_TEMP
2684: * 2. Select resource assignments from pa_resource_assignments joining to the temp table
2685: */
2686:
2687: -- dynamically computing the statistics for the Temporary table
2688: l_num_of_tasks := l_task_elem_version_id_tbl.COUNT;
2726: END IF;
2727:
2728: /*
2729: FORALL k IN l_task_elem_version_id_tbl.FIRST .. l_task_elem_version_id_tbl.LAST
2730: UPDATE pa_resource_assignments
2731: SET ta_display_flag = 'N'
2732: WHERE ta_display_flag = 'N'
2733: AND wbs_element_version_id = l_task_elem_version_id_tbl(k)
2734: AND project_id = l_project_id
3976:
3977: --Bug#9374037
3978: CURSOR C_Proj_Ass_Id(p_resource_assignment_id IN NUMBER) IS
3979: select project_assignment_id
3980: from pa_resource_assignments
3981: where resource_assignment_id = p_resource_assignment_id;
3982:
3983: CURSOR C_Check_Res_List_None (c_resource_list_id IN NUMBER) IS
3984: SELECT uncategorized_flag
4013: p_project_id IN NUMBER,
4014: p_wbs_version_id IN NUMBER,
4015: p_resource_list_member_id IN NUMBER ) IS
4016: Select a.alias, b.resource_assignment_id
4017: from pa_resource_list_members a, pa_resource_assignments b, pa_budget_versions bv
4018: where a.resource_list_member_id = b.resource_list_member_id
4019: and b.resource_list_member_id = p_resource_list_member_id
4020: and b.ta_display_flag = 'Y'
4021: and b.budget_version_id = bv.budget_version_id
4030: p_wbs_version_id IN NUMBER,
4031: p_resource_list_member_id IN NUMBER,
4032: p_cbs_element_id IN NUMBER ) IS
4033: Select a.alias, b.resource_assignment_id
4034: from pa_resource_list_members a, pa_resource_assignments b, pa_budget_versions bv
4035: where a.resource_list_member_id = b.resource_list_member_id
4036: and b.resource_list_member_id = p_resource_list_member_id
4037: and b.ta_display_flag = 'Y'
4038: and b.budget_version_id = bv.budget_version_id
4105: select task_id, wbs_element_version_id, resource_class_code, resource_assignment_id,
4106: project_role_id, organization_id,
4107: fc_res_type_code, named_role,res_type_code, planning_start_date, planning_end_date,
4108: use_task_schedule_flag, rate_based_flag, supplier_id
4109: from pa_resource_assignments
4110: where resource_assignment_id = p_resource_assignment_id;
4111: C_Res_Asgmt_Data_Rec C_Res_Asgmt_Data%ROWTYPE;
4112:
4113: ip_fc_res_type_code varchar2(200);
4153: -- Currently going ahead with commenting out the pm_product_code check.
4154:
4155: CURSOR c_ta_del(p_structure_version_id in number, p_project_id in number) is
4156: select a.resource_assignment_id, a.wbs_element_version_id
4157: from pa_resource_assignments a, pa_budget_versions b
4158: where a.budget_version_id = b.budget_version_id
4159: and a.project_id = b.project_id
4160: and a.ta_display_flag = 'Y'
4161: and b.project_id = p_project_id
4171: -- Bug 4087956
4172: -- Cursor C_Reference_Check(p_res_assignment_reference IN VARCHAR2) IS
4173: Cursor C_Reference_Check(p_res_assignment_reference IN VARCHAR2, c_budget_version_id IN NUMBER) IS
4174: select 'X'
4175: from pa_resource_assignments a --, pa_budget_versions b
4176: where a.pm_res_assignment_reference = p_res_assignment_reference
4177: and a.pm_product_code = p_pm_product_code
4178: and a.project_id = p_pa_project_id
4179: and a.ta_display_flag is not null
4188: p_project_id IN NUMBER,
4189: p_wbs_version_id IN NUMBER
4190: ) IS
4191: Select b.budget_version_id
4192: from pa_resource_assignments b, pa_budget_versions bv
4193: where b.ta_display_flag = 'N'
4194: and b.budget_version_id = bv.budget_version_id
4195: and b.project_id = bv.project_id
4196: and bv.project_structure_version_id = p_structure_version_id
4217: CURSOR C_Get_Default_Res_Asgmt(p_project_id IN NUMBER,
4218: p_budget_version_id IN NUMBER)
4219: IS
4220: SELECT resource_assignment_id, wbs_element_version_id
4221: FROM pa_resource_assignments ra, pa_copy_asgmts_temp cat
4222: WHERE ra.wbs_element_version_id = cat.src_elem_ver_id
4223: AND ra.project_id = p_project_id
4224: AND ra.budget_version_id = p_budget_version_id
4225: AND ra.ta_display_flag = 'N';
4261: c_time_phased_code_rec C_Time_Phased_Code%ROWTYPE;
4262:
4263: Cursor C_Spread_Curve_Id(p_task_assignment_id IN NUMBER) IS
4264: select spread_curve_id
4265: from pa_resource_assignments
4266: where resource_assignment_id = p_task_assignment_id ;
4267: c_spread_curve_id_rec C_Spread_Curve_Id%ROWTYPE;
4268:
4269: Cursor C_Res_Asgmt_Id(p_project_id IN NUMBER,p_task_asgmt_reference IN
4269: Cursor C_Res_Asgmt_Id(p_project_id IN NUMBER,p_task_asgmt_reference IN
4270: VARCHAR2,p_task_element_version_id IN NUMBER, p_resource_list_member_id IN
4271: NUMBER) IS
4272: select resource_assignment_id
4273: from pa_resource_assignments
4274: where project_id = p_project_id AND
4275: pm_res_assignment_reference = p_task_asgmt_reference AND
4276: wbs_element_version_id = p_task_element_version_id AND
4277: resource_list_member_id = p_resource_list_member_id;
6103:
6104: 1. We currently only delete if the resource being assigned to task is of
6105: 'PEOPLE' class. This IF should be removed, deletion should be done when
6106: resource of ANY class is assigned to task.
6107: 2. We should NOT be deleting directly from pa_resource_assignments. Instead,
6108: we should call pa_fp_planning_transaction_pub.delete_planning_transactions to
6109: to properly delete the assignment record.
6110: */
6111:
6113: /* Updates from the Bug
6114: The UPDATE does not perform very well in volume env. My suggestion to improve
6115: this is to replace the FORALL UPDATE by 2 operations:
6116: * 1. Insert the task version ids into an existing temp table, PA_COPY_ASGMTS_TEMP
6117: * 2. Select resource assignments from pa_resource_assignments joining to the temp table
6118: */
6119:
6120: -- dynamically computing the statistics for the Temporary table
6121: l_num_of_tasks := l_task_elem_version_id_tbl.COUNT;
6159: END IF;
6160:
6161: /*
6162: FORALL k IN l_task_elem_version_id_tbl.FIRST .. l_task_elem_version_id_tbl.LAST
6163: UPDATE pa_resource_assignments
6164: SET ta_display_flag = 'N'
6165: WHERE ta_display_flag = 'N'
6166: AND wbs_element_version_id = l_task_elem_version_id_tbl(k)
6167: AND project_id = l_project_id
7030:
7031: CURSOR l_task_asgmt_id_csr(l_struct_ver_id NUMBER)
7032: IS
7033: SELECT 'X'
7034: FROM pa_resource_assignments a, pa_budget_versions b
7035: WHERE a.resource_assignment_id = p_pa_task_assignment_id
7036: AND a.project_id = p_pa_project_id
7037: AND b.budget_version_id = a.budget_version_id
7038: AND b.project_structure_version_id = l_struct_ver_id;
7039:
7040:
7041: CURSOR c_task_asgmt_csr(p_struct_version_id IN NUMBER) IS
7042: SELECT a.resource_assignment_id
7043: FROM pa_resource_assignments a, pa_budget_versions b
7044: WHERE a.project_id = p_pa_project_id
7045: AND b.budget_version_id = a.budget_version_id
7046: AND b.project_structure_version_id = p_struct_version_id
7047: AND a.pm_res_assignment_reference = p_pm_task_asgmt_reference
7048: AND a.pm_product_code = p_pm_product_code;
7049:
7050: CURSOR c_task_asgmt_from_id_csr(p_struct_version_id IN NUMBER) IS
7051: SELECT a.resource_assignment_id
7052: FROM pa_resource_assignments a, pa_budget_versions b
7053: WHERE a.project_id = p_pa_project_id
7054: AND b.budget_version_id = a.budget_version_id
7055: AND b.project_structure_version_id = p_struct_version_id
7056: AND a.wbs_element_version_id = p_pa_task_elem_ver_id
7079: --Added by rtarway for BUG 3872176
7080: CURSOR c_task_asgmt_csr_pub_ver ( l_structure_version_id NUMBER )
7081: IS
7082: SELECT published_version.resource_assignment_id
7083: FROM pa_resource_assignments published_version,
7084: (SELECT task_id, resource_list_member_id
7085: FROM pa_resource_assignments ra,
7086: pa_budget_versions bv,
7087: pa_proj_elem_ver_structure pevs
7081: IS
7082: SELECT published_version.resource_assignment_id
7083: FROM pa_resource_assignments published_version,
7084: (SELECT task_id, resource_list_member_id
7085: FROM pa_resource_assignments ra,
7086: pa_budget_versions bv,
7087: pa_proj_elem_ver_structure pevs
7088: WHERE ra.pm_product_code = p_pm_product_code
7089: AND ra.pm_res_assignment_reference = p_pm_task_asgmt_reference