[Home] [Help]
1438: p_project_id IN NUMBER,
1439: p_wbs_version_id IN NUMBER,
1440: p_resource_list_member_id IN NUMBER ) IS
1441: Select a.alias, b.resource_assignment_id
1442: from pa_resource_list_members a, pa_resource_assignments b, pa_budget_versions bv
1443: where a.resource_list_member_id = b.resource_list_member_id
1444: and b.resource_list_member_id = p_resource_list_member_id
1445: and b.ta_display_flag = 'Y'
1446: and b.budget_version_id = bv.budget_version_id
1460: -- Bug 4087956
1461: -- Cursor C_Reference_Check(p_res_assignment_reference IN VARCHAR2) IS
1462: Cursor C_Reference_Check(p_res_assignment_reference IN VARCHAR2, c_budget_version_id IN NUMBER) IS
1463: select 'X'
1464: from pa_resource_assignments a --, pa_budget_versions b
1465: where a.pm_res_assignment_reference = p_res_assignment_reference
1466: and a.pm_product_code = p_pm_product_code
1467: and a.project_id = p_pa_project_id
1468: and a.ta_display_flag is not null
1477: p_project_id IN NUMBER,
1478: p_wbs_version_id IN NUMBER,
1479: p_resource_list_member_id IN NUMBER ) IS
1480: Select 'X'
1481: from pa_resource_assignments b, pa_budget_versions bv
1482: where b.resource_list_member_id = p_resource_list_member_id
1483: and b.ta_display_flag is not null
1484: and b.budget_version_id = bv.budget_version_id
1485: and b.project_id = bv.project_id
1492: p_project_id IN NUMBER,
1493: p_wbs_version_id IN NUMBER
1494: ) IS
1495: Select b.budget_version_id
1496: from pa_resource_assignments b, pa_budget_versions bv
1497: where b.ta_display_flag = 'N'
1498: and b.budget_version_id = bv.budget_version_id
1499: and b.project_id = bv.project_id
1500: and bv.project_structure_version_id = p_structure_version_id
1531: CURSOR C_Get_Default_Res_Asgmt(p_project_id IN NUMBER,
1532: p_budget_version_id IN NUMBER)
1533: IS
1534: SELECT resource_assignment_id, wbs_element_version_id
1535: FROM pa_resource_assignments ra, pa_copy_asgmts_temp cat
1536: WHERE ra.wbs_element_version_id = cat.src_elem_ver_id
1537: AND ra.project_id = p_project_id
1538: AND ra.budget_version_id = p_budget_version_id
1539: AND ra.ta_display_flag = 'N';
2369:
2370: IF c_people_res_check%FOUND THEN
2371:
2372:
2373: --dbms_output.put_line('Deleting records of task level effort from pa_resource_assignments. ');
2374:
2375: delete from pa_resource_assignments where
2376: resource_class_code = 'PEOPLE'
2377: and project_id = l_project_id
2371:
2372:
2373: --dbms_output.put_line('Deleting records of task level effort from pa_resource_assignments. ');
2374:
2375: delete from pa_resource_assignments where
2376: resource_class_code = 'PEOPLE'
2377: and project_id = l_project_id
2378: and budget_version_id = c_people_res_rec.budget_version_id
2379: and wbs_element_version_id = lk_task_elem_version_id_tbl(i)
2381: -- Bug 3840323 fix
2382: -- Should delete regardless of product code
2383: --and pm_product_code is not null;
2384:
2385: --dbms_output.put_line('After Deleting records of task level effort from pa_resource_assignments. ');
2386:
2387: END IF;
2388:
2389: close c_people_res_check;
2467:
2468: 1. We currently only delete if the resource being assigned to task is of
2469: 'PEOPLE' class. This IF should be removed, deletion should be done when
2470: resource of ANY class is assigned to task.
2471: 2. We should NOT be deleting directly from pa_resource_assignments. Instead,
2472: we should call pa_fp_planning_transaction_pub.delete_planning_transactions to
2473: to properly delete the assignment record.
2474: */
2475:
2477: /* Updates from the Bug
2478: The UPDATE does not perform very well in volume env. My suggestion to improve
2479: this is to replace the FORALL UPDATE by 2 operations:
2480: * 1. Insert the task version ids into an existing temp table, PA_COPY_ASGMTS_TEMP
2481: * 2. Select resource assignments from pa_resource_assignments joining to the temp table
2482: */
2483:
2484: -- dynamically computing the statistics for the Temporary table
2485: l_num_of_tasks := l_task_elem_version_id_tbl.COUNT;
2522: END IF;
2523:
2524: /*
2525: FORALL k IN l_task_elem_version_id_tbl.FIRST .. l_task_elem_version_id_tbl.LAST
2526: UPDATE pa_resource_assignments
2527: SET ta_display_flag = 'N'
2528: WHERE ta_display_flag = 'N'
2529: AND wbs_element_version_id = l_task_elem_version_id_tbl(k)
2530: AND project_id = l_project_id
3753: p_project_id IN NUMBER,
3754: p_wbs_version_id IN NUMBER,
3755: p_resource_list_member_id IN NUMBER ) IS
3756: Select a.alias, b.resource_assignment_id
3757: from pa_resource_list_members a, pa_resource_assignments b, pa_budget_versions bv
3758: where a.resource_list_member_id = b.resource_list_member_id
3759: and b.resource_list_member_id = p_resource_list_member_id
3760: and b.ta_display_flag = 'Y'
3761: and b.budget_version_id = bv.budget_version_id
3827: select task_id, wbs_element_version_id, resource_class_code, resource_assignment_id,
3828: project_role_id, organization_id,
3829: fc_res_type_code, named_role,res_type_code, planning_start_date, planning_end_date,
3830: use_task_schedule_flag, rate_based_flag, supplier_id
3831: from pa_resource_assignments
3832: where resource_assignment_id = p_resource_assignment_id;
3833: C_Res_Asgmt_Data_Rec C_Res_Asgmt_Data%ROWTYPE;
3834:
3835: ip_fc_res_type_code varchar2(200);
3875: -- Currently going ahead with commenting out the pm_product_code check.
3876:
3877: CURSOR c_ta_del(p_structure_version_id in number, p_project_id in number) is
3878: select a.resource_assignment_id, a.wbs_element_version_id
3879: from pa_resource_assignments a, pa_budget_versions b
3880: where a.budget_version_id = b.budget_version_id
3881: and a.project_id = b.project_id
3882: and a.ta_display_flag = 'Y'
3883: and b.project_id = p_project_id
3893: -- Bug 4087956
3894: -- Cursor C_Reference_Check(p_res_assignment_reference IN VARCHAR2) IS
3895: Cursor C_Reference_Check(p_res_assignment_reference IN VARCHAR2, c_budget_version_id IN NUMBER) IS
3896: select 'X'
3897: from pa_resource_assignments a --, pa_budget_versions b
3898: where a.pm_res_assignment_reference = p_res_assignment_reference
3899: and a.pm_product_code = p_pm_product_code
3900: and a.project_id = p_pa_project_id
3901: and a.ta_display_flag is not null
3910: p_project_id IN NUMBER,
3911: p_wbs_version_id IN NUMBER
3912: ) IS
3913: Select b.budget_version_id
3914: from pa_resource_assignments b, pa_budget_versions bv
3915: where b.ta_display_flag = 'N'
3916: and b.budget_version_id = bv.budget_version_id
3917: and b.project_id = bv.project_id
3918: and bv.project_structure_version_id = p_structure_version_id
3939: CURSOR C_Get_Default_Res_Asgmt(p_project_id IN NUMBER,
3940: p_budget_version_id IN NUMBER)
3941: IS
3942: SELECT resource_assignment_id, wbs_element_version_id
3943: FROM pa_resource_assignments ra, pa_copy_asgmts_temp cat
3944: WHERE ra.wbs_element_version_id = cat.src_elem_ver_id
3945: AND ra.project_id = p_project_id
3946: AND ra.budget_version_id = p_budget_version_id
3947: AND ra.ta_display_flag = 'N';
5455:
5456: 1. We currently only delete if the resource being assigned to task is of
5457: 'PEOPLE' class. This IF should be removed, deletion should be done when
5458: resource of ANY class is assigned to task.
5459: 2. We should NOT be deleting directly from pa_resource_assignments. Instead,
5460: we should call pa_fp_planning_transaction_pub.delete_planning_transactions to
5461: to properly delete the assignment record.
5462: */
5463:
5465: /* Updates from the Bug
5466: The UPDATE does not perform very well in volume env. My suggestion to improve
5467: this is to replace the FORALL UPDATE by 2 operations:
5468: * 1. Insert the task version ids into an existing temp table, PA_COPY_ASGMTS_TEMP
5469: * 2. Select resource assignments from pa_resource_assignments joining to the temp table
5470: */
5471:
5472: -- dynamically computing the statistics for the Temporary table
5473: l_num_of_tasks := l_task_elem_version_id_tbl.COUNT;
5510: END IF;
5511:
5512: /*
5513: FORALL k IN l_task_elem_version_id_tbl.FIRST .. l_task_elem_version_id_tbl.LAST
5514: UPDATE pa_resource_assignments
5515: SET ta_display_flag = 'N'
5516: WHERE ta_display_flag = 'N'
5517: AND wbs_element_version_id = l_task_elem_version_id_tbl(k)
5518: AND project_id = l_project_id
6325:
6326: CURSOR l_task_asgmt_id_csr(l_struct_ver_id NUMBER)
6327: IS
6328: SELECT 'X'
6329: FROM pa_resource_assignments a, pa_budget_versions b
6330: WHERE a.resource_assignment_id = p_pa_task_assignment_id
6331: AND a.project_id = p_pa_project_id
6332: AND b.budget_version_id = a.budget_version_id
6333: AND b.project_structure_version_id = l_struct_ver_id;
6334:
6335:
6336: CURSOR c_task_asgmt_csr(p_struct_version_id IN NUMBER) IS
6337: SELECT a.resource_assignment_id
6338: FROM pa_resource_assignments a, pa_budget_versions b
6339: WHERE a.project_id = p_pa_project_id
6340: AND b.budget_version_id = a.budget_version_id
6341: AND b.project_structure_version_id = p_struct_version_id
6342: AND a.pm_res_assignment_reference = p_pm_task_asgmt_reference
6343: AND a.pm_product_code = p_pm_product_code;
6344:
6345: CURSOR c_task_asgmt_from_id_csr(p_struct_version_id IN NUMBER) IS
6346: SELECT a.resource_assignment_id
6347: FROM pa_resource_assignments a, pa_budget_versions b
6348: WHERE a.project_id = p_pa_project_id
6349: AND b.budget_version_id = a.budget_version_id
6350: AND b.project_structure_version_id = p_struct_version_id
6351: AND a.wbs_element_version_id = p_pa_task_elem_ver_id
6373: --Added by rtarway for BUG 3872176
6374: CURSOR c_task_asgmt_csr_pub_ver ( l_structure_version_id NUMBER )
6375: IS
6376: SELECT published_version.resource_assignment_id
6377: FROM pa_resource_assignments published_version,
6378: (SELECT task_id, resource_list_member_id
6379: FROM pa_resource_assignments ra,
6380: pa_budget_versions bv,
6381: pa_proj_elem_ver_structure pevs
6375: IS
6376: SELECT published_version.resource_assignment_id
6377: FROM pa_resource_assignments published_version,
6378: (SELECT task_id, resource_list_member_id
6379: FROM pa_resource_assignments ra,
6380: pa_budget_versions bv,
6381: pa_proj_elem_ver_structure pevs
6382: WHERE ra.pm_product_code = p_pm_product_code
6383: AND ra.pm_res_assignment_reference = p_pm_task_asgmt_reference