[Home] [Help]
238: p_module_name => l_module_name,
239: p_log_level => 5);
240: END IF;
241:
242: /* Records are deleted from pa_budget_lines and PA_RESOURCE_ASSIGNMENTS
243: tables when the flag is set to N */
244: IF l_fp_cols_rec.x_gen_ret_manual_line_flag = 'N' THEN
245: DELETE FROM PA_BUDGET_LINES
246: WHERE budget_version_id = p_budget_version_id ;
244: IF l_fp_cols_rec.x_gen_ret_manual_line_flag = 'N' THEN
245: DELETE FROM PA_BUDGET_LINES
246: WHERE budget_version_id = p_budget_version_id ;
247:
248: DELETE FROM PA_RESOURCE_ASSIGNMENTS
249: WHERE budget_version_id = p_budget_version_id ;
250:
251: -- IPM: New Entity ER ------------------------------------------
252: -- Call the maintenance api in DELETE mode
294:
295: SELECT resource_assignment_id
296: BULK COLLECT INTO
297: l_res_as_id
298: FROM PA_RESOURCE_ASSIGNMENTS
299: WHERE budget_version_id = p_budget_version_id AND
300: transaction_source_code IS NOT NULL;
301:
302: IF (l_res_as_id.count > 0) THEN
304: DELETE FROM PA_BUDGET_LINES
305: WHERE resource_assignment_id = l_res_as_id(i);
306:
307: FORALL j IN 1 .. l_res_as_id.count
308: UPDATE PA_RESOURCE_ASSIGNMENTS
309: SET transaction_source_code = null
310: WHERE resource_assignment_id = l_res_as_id(j);
311:
312: -- IPM: New Entity ER ------------------------------------------
1103: AND FI.EXPENDITURE_ORGANIZATION_ID = c_exp_organization_id
1104: GROUP BY FI.EXPENDITURE_ORG_ID,
1105: FI.EXPENDITURE_ORGANIZATION_ID;
1106:
1107: CURSOR BUDGET_LINES(c_budget_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE,
1108: c_project_id PA_RESOURCE_ASSIGNMENTS.PROJECT_ID%TYPE,
1109: c_resource_assignment_id PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE) IS
1110: SELECT BL.PERIOD_NAME,
1111: BL.START_DATE,
1104: GROUP BY FI.EXPENDITURE_ORG_ID,
1105: FI.EXPENDITURE_ORGANIZATION_ID;
1106:
1107: CURSOR BUDGET_LINES(c_budget_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE,
1108: c_project_id PA_RESOURCE_ASSIGNMENTS.PROJECT_ID%TYPE,
1109: c_resource_assignment_id PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE) IS
1110: SELECT BL.PERIOD_NAME,
1111: BL.START_DATE,
1112: BL.BURDENED_COST
1105: FI.EXPENDITURE_ORGANIZATION_ID;
1106:
1107: CURSOR BUDGET_LINES(c_budget_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE,
1108: c_project_id PA_RESOURCE_ASSIGNMENTS.PROJECT_ID%TYPE,
1109: c_resource_assignment_id PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE) IS
1110: SELECT BL.PERIOD_NAME,
1111: BL.START_DATE,
1112: BL.BURDENED_COST
1113: FROM PA_BUDGET_LINES BL,
1110: SELECT BL.PERIOD_NAME,
1111: BL.START_DATE,
1112: BL.BURDENED_COST
1113: FROM PA_BUDGET_LINES BL,
1114: PA_RESOURCE_ASSIGNMENTS RA
1115: WHERE BL.RESOURCE_ASSIGNMENT_ID = RA.RESOURCE_ASSIGNMENT_ID
1116: AND RA.BUDGET_VERSION_ID = c_budget_version_id
1117: AND RA.PROJECT_ID = c_project_id
1118: AND RA.RESOURCE_LIST_MEMBER_ID = 103
1153: FROM PA_FORECAST_ITEMS FI,
1154: PA_FORECAST_ITEM_DETAILS FID,
1155: PA_PERIODS_ALL P,
1156: PA_RES_LIST_MAP_TMP4 TMP4,
1157: PA_RESOURCE_ASSIGNMENTS RA,
1158: PA_RESOURCE_LIST_MEMBERS RLM
1159: WHERE FI.PROJECT_ORG_ID = NVL(P.ORG_ID,-99)
1160: AND P.ORG_ID = c_org_id /* Bug 5657334 */
1161: AND P.PERIOD_NAME = FI.RCVR_PA_PERIOD_NAME
1228: FROM PA_FORECAST_ITEMS FI,
1229: PA_FORECAST_ITEM_DETAILS FID,
1230: GL_PERIODS GLP,
1231: PA_RES_LIST_MAP_TMP4 TMP4,
1232: PA_RESOURCE_ASSIGNMENTS RA,
1233: PA_RESOURCE_LIST_MEMBERS RLM
1234: WHERE FI.FORECAST_ITEM_ID = FID.FORECAST_ITEM_ID
1235: AND FID.FORECAST_SUMMARIZED_CODE = 'N'
1236: AND FID.NET_ZERO_FLAG = 'N'
1302: RLM.ALIAS
1303: FROM PA_FORECAST_ITEMS FI,
1304: PA_FORECAST_ITEM_DETAILS FID,
1305: PA_RES_LIST_MAP_TMP4 TMP4,
1306: PA_RESOURCE_ASSIGNMENTS RA,
1307: PA_RESOURCE_LIST_MEMBERS RLM
1308: WHERE FI.FORECAST_ITEM_ID = FID.FORECAST_ITEM_ID
1309: AND FID.FORECAST_SUMMARIZED_CODE = 'N'
1310: AND FID.NET_ZERO_FLAG = 'N'
1646: l_proj_error_msg_code PA_PLSQL_DATATYPES.Char30TabTyp;
1647: l_proj_end_date PA_PLSQL_DATATYPES.DateTabTyp;
1648: l_proj_fc_res_type_code PA_PLSQL_DATATYPES.Char30TabTyp;
1649:
1650: l_role_error_code PA_RESOURCE_ASSIGNMENTS.PLAN_ERROR_CODE%TYPE;
1651:
1652: l_err_code VARCHAR2(30);
1653: l_err_stack VARCHAR2(2000);
1654: l_err_stage VARCHAR2(2000);
1673: l_other_reject_reason VARCHAR2(1000);
1674:
1675: l_resource_list_member_id PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_MEMBER_ID%TYPE;
1676: l_resource_id PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_MEMBER_ID%TYPE;
1677: l_resource_assignment_id PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE;
1678: l_track_as_labor_flag PA_RESOURCE_LIST_MEMBERS.TRACK_AS_LABOR_FLAG%TYPE;
1679: l_parent_member_id PA_RESOURCE_LIST_MEMBERS.PARENT_MEMBER_ID%TYPE;
1680: l_prj_res_assignment_id PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE;
1681:
1676: l_resource_id PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_MEMBER_ID%TYPE;
1677: l_resource_assignment_id PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE;
1678: l_track_as_labor_flag PA_RESOURCE_LIST_MEMBERS.TRACK_AS_LABOR_FLAG%TYPE;
1679: l_parent_member_id PA_RESOURCE_LIST_MEMBERS.PARENT_MEMBER_ID%TYPE;
1680: l_prj_res_assignment_id PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE;
1681:
1682: l_fcst_opt_jobcostrate_sch_id PA_FORECASTING_OPTIONS_ALL.JOB_COST_RATE_SCHEDULE_ID%TYPE;
1683:
1684: l_calling_mode VARCHAR2(50);
2030: l_t_line_start_date_tab SYSTEM.pa_date_tbl_type:=SYSTEM.pa_date_tbl_type();
2031: l_t_line_end_date_tab SYSTEM.pa_date_tbl_type:=SYSTEM.pa_date_tbl_type();
2032:
2033: l_bl_count NUMBER;
2034: l_transaction_source_code PA_RESOURCE_ASSIGNMENTS.TRANSACTION_SOURCE_CODE%TYPE;
2035:
2036: -- Bug 4548733: Added new pl/sql table to hold billability flag values for
2037: -- Calculate API. Corresponds to p_fp_task_billable_flag_tab IN parameter.
2038: l_calc_billable_flag_tab SYSTEM.pa_varchar2_1_tbl_type:=SYSTEM.pa_varchar2_1_tbl_type();
2161: DELETE FROM pa_budget_lines
2162: WHERE budget_version_id = p_budget_version_id
2163: AND budget_line_id IN
2164: (SELECT bl.budget_line_id
2165: FROM pa_budget_lines bl, pa_resource_assignments ra
2166: WHERE ra.budget_version_id = p_budget_version_id
2167: AND bl.budget_version_id = p_budget_version_id
2168: AND ra.transaction_source_code IS NOT NULL
2169: AND ra.resource_assignment_id = bl.resource_assignment_id);
2172: DELETE FROM pa_budget_lines
2173: WHERE budget_version_id = p_budget_version_id
2174: AND budget_line_id IN
2175: (SELECT bl.budget_line_id
2176: FROM pa_budget_lines bl, pa_resource_assignments ra
2177: WHERE ra.budget_version_id = p_budget_version_id
2178: AND bl.budget_version_id = p_budget_version_id
2179: AND ra.transaction_source_code IS NOT NULL
2180: AND ra.resource_assignment_id = bl.resource_assignment_id
2206: projfunc_rev_exchange_rate = DECODE(NVL(txn_init_revenue,0),0,NULL,init_revenue/txn_init_revenue)
2207: WHERE budget_version_id = p_budget_version_id
2208: AND budget_line_id IN
2209: (SELECT bl.budget_line_id
2210: FROM pa_budget_lines bl, pa_resource_assignments ra
2211: WHERE ra.budget_version_id = p_budget_version_id
2212: AND bl.budget_version_id = p_budget_version_id
2213: AND ra.transaction_source_code IS NOT NULL
2214: AND ra.resource_assignment_id = bl.resource_assignment_id);
4426: FROM PA_RES_LIST_MAP_TMP4 T
4427: WHERE NOT EXISTS
4428: (SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
4429: P.RESOURCE_LIST_MEMBER_ID
4430: FROM PA_RESOURCE_ASSIGNMENTS P
4431: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
4432: AND NVL(P.TASK_ID,0) = 0
4433: AND P.PROJECT_ASSIGNMENT_ID = -1
4434: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
4444: MAX(T.TXN_PLANNING_END_DATE)
4445: FROM PA_RES_LIST_MAP_TMP4 T
4446: WHERE NOT EXISTS
4447: (SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
4448: FROM PA_RESOURCE_ASSIGNMENTS P
4449: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
4450: AND NVL(P.TASK_ID,0) = NVL(T.TXN_TASK_ID,0)
4451: AND P.PROJECT_ASSIGNMENT_ID = -1
4452: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
4462: MIN(T.TXN_PLANNING_START_DATE),
4463: MAX(T.TXN_PLANNING_END_DATE),
4464: PRA.CBS_ELEMENT_ID
4465: FROM PA_RES_LIST_MAP_TMP4 T,
4466: PA_RESOURCE_ASSIGNMENTS PRA
4467: WHERE
4468: PRA.RESOURCE_ASSIGNMENT_ID = T.TXN_SOURCE_ID AND
4469: NOT EXISTS
4470: (SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
4467: WHERE
4468: PRA.RESOURCE_ASSIGNMENT_ID = T.TXN_SOURCE_ID AND
4469: NOT EXISTS
4470: (SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
4471: FROM PA_RESOURCE_ASSIGNMENTS P
4472: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
4473: AND NVL(P.TASK_ID,0) = NVL(T.TXN_TASK_ID,0)
4474: AND P.PROJECT_ASSIGNMENT_ID = -1
4475: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID)
4491: WHERE NVL(T.TXN_TASK_ID,0) > 0
4492: AND NVL(T.TXN_TASK_ID,0) = PAT.TASK_ID
4493: AND NOT EXISTS
4494: (SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
4495: FROM PA_RESOURCE_ASSIGNMENTS P,PA_TASKS TS
4496: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
4497: AND NVL(T.TXN_TASK_ID,0) > 0
4498: AND TS.TASK_ID = NVL(T.TXN_TASK_ID,0)
4499: AND NVL(TS.TOP_TASK_ID,0) = NVL(P.TASK_ID,0)
4512: FROM PA_RES_LIST_MAP_TMP4 T
4513: WHERE NVL(T.TXN_TASK_ID,0) = 0
4514: AND NOT EXISTS
4515: (SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
4516: FROM PA_RESOURCE_ASSIGNMENTS P
4517: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
4518: AND NVL(T.TXN_TASK_ID,0) = 0
4519: AND NVL(P.TASK_ID,0) = 0
4520: AND P.RESOURCE_LIST_MEMBER_ID = T.RESOURCE_LIST_MEMBER_ID
4532: MAX(T.TXN_PLANNING_END_DATE),
4533: PRA.CBS_ELEMENT_ID
4534: FROM PA_RES_LIST_MAP_TMP4 T,
4535: PA_TASKS PAT,
4536: PA_RESOURCE_ASSIGNMENTS PRA,
4537: PA_ALTERNATE_TASKS PALT
4538: WHERE NVL(T.TXN_TASK_ID,0) > 0
4539: AND NVL(T.TXN_TASK_ID,0) = PAT.TASK_ID
4540: AND PALT.CBS_ELEMENT_ID = PRA.CBS_ELEMENT_ID
4541: AND PALT.PROJ_ELEMENT_ID = NVL(PAT.TOP_TASK_ID,0)
4542: AND PRA.RESOURCE_ASSIGNMENT_ID = T.TXN_SOURCE_ID
4543: AND NOT EXISTS
4544: (SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
4545: FROM PA_RESOURCE_ASSIGNMENTS P,PA_TASKS TS
4546: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
4547: AND NVL(T.TXN_TASK_ID,0) > 0
4548: AND TS.TASK_ID = NVL(T.TXN_TASK_ID,0)
4549: AND NVL(TS.TOP_TASK_ID,0) = NVL(P.TASK_ID,0)
4561: FROM PA_RES_LIST_MAP_TMP4 T
4562: WHERE NVL(T.TXN_TASK_ID,0) = 0
4563: AND NOT EXISTS
4564: (SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
4565: FROM PA_RESOURCE_ASSIGNMENTS P
4566: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
4567: AND NVL(T.TXN_TASK_ID,0) = 0
4568: AND NVL(P.TASK_ID,0) = 0
4569: AND P.RESOURCE_LIST_MEMBER_ID = T.RESOURCE_LIST_MEMBER_ID
4585: FROM PA_RES_LIST_MAP_TMP4 T,
4586: PA_MAP_WP_TO_FIN_TASKS_V V1
4587: WHERE NOT EXISTS
4588: (SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
4589: FROM PA_RESOURCE_ASSIGNMENTS P,PA_MAP_WP_TO_FIN_TASKS_V V
4590: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
4591: AND V.PARENT_STRUCTURE_VERSION_ID = P_WP_STRUCTURE_VER_ID
4592: AND nvl(T.TXN_TASK_ID,0) = NVL(V.PROJ_ELEMENT_ID,0)
4593: AND P.PROJECT_ASSIGNMENT_ID = -1
4612: FROM PA_RES_LIST_MAP_TMP4 T
4613: WHERE NVL(T.TXN_TASK_ID,0) = 0 AND
4614: NOT EXISTS
4615: (SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
4616: FROM PA_RESOURCE_ASSIGNMENTS P
4617: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
4618: AND P.PROJECT_ASSIGNMENT_ID = -1
4619: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
4620: AND NVL(T.CBS_ELEMENT_ID,-1) = NVL(P.CBS_ELEMENT_ID,-1) --bug#16827157
4636: MAX(T.TXN_PLANNING_END_DATE) txn_planning_end_date,
4637: PRA.CBS_ELEMENT_ID cbs_element_id
4638: FROM PA_RES_LIST_MAP_TMP4 T,
4639: PA_MAP_WP_TO_FIN_TASKS_V V1,
4640: PA_RESOURCE_ASSIGNMENTS PRA,
4641: PA_ALTERNATE_TASKS PAT
4642: WHERE NOT EXISTS
4643: (SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
4644: FROM PA_RESOURCE_ASSIGNMENTS P,PA_MAP_WP_TO_FIN_TASKS_V V
4640: PA_RESOURCE_ASSIGNMENTS PRA,
4641: PA_ALTERNATE_TASKS PAT
4642: WHERE NOT EXISTS
4643: (SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
4644: FROM PA_RESOURCE_ASSIGNMENTS P,PA_MAP_WP_TO_FIN_TASKS_V V
4645: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
4646: AND V.PARENT_STRUCTURE_VERSION_ID = P_WP_STRUCTURE_VER_ID
4647: AND nvl(T.TXN_TASK_ID,0) = NVL(V.PROJ_ELEMENT_ID,0)
4648: AND P.PROJECT_ASSIGNMENT_ID = -1
4669: FROM PA_RES_LIST_MAP_TMP4 T
4670: WHERE NVL(T.TXN_TASK_ID,0) = 0 AND
4671: NOT EXISTS
4672: (SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
4673: FROM PA_RESOURCE_ASSIGNMENTS P
4674: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
4675: AND P.PROJECT_ASSIGNMENT_ID = -1
4676: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
4677: AND NVL(P.TASK_ID,0) = 0 )
4695: PA_MAP_WP_TO_FIN_TASKS_V V1
4696: WHERE NVL(V1.MAPPED_FIN_TASK_ID,0) = PAT.TASK_ID (+)
4697: AND NOT EXISTS
4698: (SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
4699: FROM PA_RESOURCE_ASSIGNMENTS P,PA_TASKS TS,PA_MAP_WP_TO_FIN_TASKS_V V
4700: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
4701: AND V.PARENT_STRUCTURE_VERSION_ID = P_WP_STRUCTURE_VER_ID
4702: AND NVL(T.TXN_TASK_ID,0) = NVL(V.PROJ_ELEMENT_ID,0)
4703: AND TS.TASK_ID(+) = NVL(V.MAPPED_FIN_TASK_ID,0)
4723: FROM PA_RES_LIST_MAP_TMP4 T
4724: WHERE NVL(T.TXN_TASK_ID,0) = 0 AND
4725: NOT EXISTS
4726: (SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
4727: FROM PA_RESOURCE_ASSIGNMENTS P
4728: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
4729: AND P.PROJECT_ASSIGNMENT_ID = -1
4730: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
4731: AND NVL(T.CBS_ELEMENT_ID,-1) = NVL(P.CBS_ELEMENT_ID,-1) --bug#16827157
4737:
4738: --Cursor to get the cbs_element_id when CBS is enabled -- bug#16200605
4739: CURSOR GET_CBS_ELEMENT_ID(c_res_asgn_id NUMBER) IS
4740: SELECT CBS_ELEMENT_ID
4741: FROM PA_RESOURCE_ASSIGNMENTS T
4742: WHERE T.RESOURCE_ASSIGNMENT_ID = c_res_asgn_id;
4743:
4744: l_stru_sharing_code PA_PROJECTS_ALL.STRUCTURE_SHARING_CODE%TYPE;
4745: l_unique_rlm_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
4794: l_da_rate_func_curr_code_tab SYSTEM.PA_VARCHAR2_30_TBL_TYPE;
4795: l_da_incur_by_res_type_tab SYSTEM.PA_VARCHAR2_30_TBL_TYPE;
4796:
4797: /* Performance-Variables to replace the literals in the Insert into
4798: pa_resource_assignments stmts. */
4799: l_task_id_01 NUMBER:=0;
4800: l_proj_asg_id_minus1 NUMBER:=-1;
4801: l_res_as_type_USER_ENTERED VARCHAR2(30):='USER_ENTERED';
4802: l_rec_ver_number_1 NUMBER:=1;
4868: FROM pa_budget_versions
4869: WHERE budget_version_id=P_BUDGET_VERSION_ID;
4870:
4871: FORALL i IN 1..l_res_list_member_id.count
4872: INSERT INTO PA_RESOURCE_ASSIGNMENTS(RESOURCE_ASSIGNMENT_ID,
4873: BUDGET_VERSION_ID,
4874: PROJECT_ID,
4875: TASK_ID,
4876: RESOURCE_LIST_MEMBER_ID,
4885: record_version_number,
4886: planning_start_date,
4887: planning_end_date,
4888: transaction_source_code)
4889: VALUES (PA_RESOURCE_ASSIGNMENTS_S.nextval,
4890: P_BUDGET_VERSION_ID,
4891: P_PROJECT_ID,
4892: l_task_id_01,
4893: l_res_list_member_id(i),
4928: RETURN;
4929: END IF;
4930:
4931: FORALL i IN 1..l_res_list_member_id.count
4932: INSERT INTO PA_RESOURCE_ASSIGNMENTS(RESOURCE_ASSIGNMENT_ID,
4933: BUDGET_VERSION_ID,
4934: PROJECT_ID,
4935: TASK_ID,
4936: RESOURCE_LIST_MEMBER_ID,
4945: record_version_number,
4946: planning_start_date,
4947: planning_end_date,
4948: transaction_source_code)
4949: VALUES (PA_RESOURCE_ASSIGNMENTS_S.nextval,
4950: P_BUDGET_VERSION_ID,
4951: P_PROJECT_ID,
4952: l_task_id(i),
4953: l_res_list_member_id(i),
4986: END IF;
4987: RETURN;
4988: END IF;
4989: FORALL i IN 1..l_res_list_member_id.count
4990: INSERT INTO PA_RESOURCE_ASSIGNMENTS(RESOURCE_ASSIGNMENT_ID,
4991: BUDGET_VERSION_ID,
4992: PROJECT_ID,
4993: TASK_ID,
4994: RESOURCE_LIST_MEMBER_ID,
5003: record_version_number,
5004: planning_start_date,
5005: planning_end_date,
5006: transaction_source_code)
5007: VALUES (PA_RESOURCE_ASSIGNMENTS_S.nextval,
5008: P_BUDGET_VERSION_ID,
5009: P_PROJECT_ID,
5010: l_task_id(i),
5011: l_res_list_member_id(i),
5028: --dbms_output.put('before res_asg4 cursor');
5029:
5030: --dbms_output.put('p_wp_structure_ver_id'||p_wp_structure_ver_id);
5031: --insert into ltmp4 select * from PA_RES_LIST_MAP_TMP4;
5032: --insert into lra select * from pa_resource_assignments where budget_version_id = p_budget_version_id;
5033: OPEN RES_ASG4;
5034: FETCH RES_ASG4
5035: BULK COLLECT
5036: INTO l_res_list_member_id,
5065: --dbms_output.put_line('l_task_id:'||l_task_id(i));
5066: --END LOOP;
5067: --commit;
5068: FORALL i IN 1..l_res_list_member_id.count
5069: INSERT INTO PA_RESOURCE_ASSIGNMENTS(RESOURCE_ASSIGNMENT_ID,
5070: BUDGET_VERSION_ID,
5071: PROJECT_ID,
5072: TASK_ID,
5073: RESOURCE_LIST_MEMBER_ID,
5083: planning_end_date,
5084: record_version_number,
5085: wbs_element_version_id,
5086: transaction_source_code)
5087: VALUES (PA_RESOURCE_ASSIGNMENTS_S.nextval,
5088: P_BUDGET_VERSION_ID,
5089: P_PROJECT_ID,
5090: l_task_id(i),
5091: l_res_list_member_id(i),
5124: END IF;
5125: RETURN;
5126: END IF;
5127: FORALL i IN 1..l_res_list_member_id.count
5128: INSERT INTO PA_RESOURCE_ASSIGNMENTS(RESOURCE_ASSIGNMENT_ID,
5129: BUDGET_VERSION_ID,
5130: PROJECT_ID,
5131: TASK_ID,
5132: RESOURCE_LIST_MEMBER_ID,
5142: wbs_element_version_id,
5143: planning_start_date,
5144: planning_end_date,
5145: transaction_source_code)
5146: VALUES (PA_RESOURCE_ASSIGNMENTS_S.nextval,
5147: P_BUDGET_VERSION_ID,
5148: P_PROJECT_ID,
5149: l_task_id(i),
5150: l_res_list_member_id(i),
5273:
5274: --Bug 4052036. Changed the check in the where clause to identify the resource assignments that got inserted
5275: --thru this API.
5276: FORALL i IN 1 .. l_da_resource_list_members_tab.count
5277: UPDATE PA_RESOURCE_ASSIGNMENTS
5278: SET RESOURCE_CLASS_FLAG = l_da_resource_class_flag_tab(i),
5279: RESOURCE_CLASS_CODE = l_da_resource_class_code_tab(i),
5280: RES_TYPE_CODE = l_da_res_type_code_tab(i),
5281: PERSON_ID = l_da_person_id_tab(i),
5353:
5354:
5355: FORALL i IN 1..l_res_list_member_id.count
5356:
5357: INSERT INTO PA_RESOURCE_ASSIGNMENTS(RESOURCE_ASSIGNMENT_ID,
5358: BUDGET_VERSION_ID,
5359: PROJECT_ID,
5360: TASK_ID,
5361: RESOURCE_LIST_MEMBER_ID,
5370: record_version_number,
5371: planning_start_date,
5372: planning_end_date,
5373: transaction_source_code)
5374: VALUES (PA_RESOURCE_ASSIGNMENTS_S.nextval,
5375: P_BUDGET_VERSION_ID,
5376: P_PROJECT_ID,
5377: l_task_id(i),
5378: l_res_list_member_id(i),
5413: END IF;
5414:
5415:
5416: FORALL i IN 1..l_res_list_member_id.count
5417: INSERT INTO PA_RESOURCE_ASSIGNMENTS(RESOURCE_ASSIGNMENT_ID,
5418: BUDGET_VERSION_ID,
5419: PROJECT_ID,
5420: TASK_ID,
5421: RESOURCE_LIST_MEMBER_ID,
5430: record_version_number,
5431: planning_start_date,
5432: planning_end_date,
5433: transaction_source_code)
5434: VALUES (PA_RESOURCE_ASSIGNMENTS_S.nextval,
5435: P_BUDGET_VERSION_ID,
5436: P_PROJECT_ID,
5437: l_task_id(i),
5438: l_res_list_member_id(i),
5455: --dbms_output.put('before res_asg4 cursor');
5456:
5457: --dbms_output.put('p_wp_structure_ver_id'||p_wp_structure_ver_id);
5458: --insert into ltmp4 select * from PA_RES_LIST_MAP_TMP4;
5459: --insert into lra select * from pa_resource_assignments where budget_version_id = p_budget_version_id;
5460: --insert_test_dump2(p_budget_version_id);
5461: OPEN RES_ASG4_CBS;
5462: FETCH RES_ASG4_CBS
5463: BULK COLLECT
5494: --END LOOP;
5495: --commit;
5496:
5497: FORALL i IN 1..l_res_list_member_id.count
5498: INSERT INTO PA_RESOURCE_ASSIGNMENTS(RESOURCE_ASSIGNMENT_ID,
5499: BUDGET_VERSION_ID,
5500: PROJECT_ID,
5501: TASK_ID,
5502: RESOURCE_LIST_MEMBER_ID,
5512: planning_end_date,
5513: record_version_number,
5514: wbs_element_version_id,
5515: transaction_source_code)
5516: VALUES (PA_RESOURCE_ASSIGNMENTS_S.nextval,
5517: P_BUDGET_VERSION_ID,
5518: P_PROJECT_ID,
5519: l_task_id(i),
5520: l_res_list_member_id(i),
5555: END IF;
5556:
5557:
5558: FORALL i IN 1..l_res_list_member_id.count
5559: INSERT INTO PA_RESOURCE_ASSIGNMENTS(RESOURCE_ASSIGNMENT_ID,
5560: BUDGET_VERSION_ID,
5561: PROJECT_ID,
5562: TASK_ID,
5563: RESOURCE_LIST_MEMBER_ID,
5572: wbs_element_version_id,
5573: planning_start_date,
5574: planning_end_date,
5575: transaction_source_code)
5576: VALUES (PA_RESOURCE_ASSIGNMENTS_S.nextval,
5577: P_BUDGET_VERSION_ID,
5578: P_PROJECT_ID,
5579: l_task_id(i),
5580: l_res_list_member_id(i),
5702:
5703: --Bug 4052036. Changed the check in the where clause to identify the resource assignments that got inserted
5704: --thru this API.
5705: FORALL i IN 1 .. l_da_resource_list_members_tab.count
5706: UPDATE PA_RESOURCE_ASSIGNMENTS
5707: SET RESOURCE_CLASS_FLAG = l_da_resource_class_flag_tab(i),
5708: RESOURCE_CLASS_CODE = l_da_resource_class_code_tab(i),
5709: RES_TYPE_CODE = l_da_res_type_code_tab(i),
5710: PERSON_ID = l_da_person_id_tab(i),
5859: P.RESOURCE_LIST_MEMBER_ID
5860: BULK COLLECT
5861: INTO l_res_assgn_id_tab,
5862: l_rlm_id_tab
5863: FROM PA_RESOURCE_ASSIGNMENTS P,
5864: PA_RES_LIST_MAP_TMP4 T
5865: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
5866: AND NVL(P.TASK_ID,0) = 0
5867: AND P.PROJECT_ASSIGNMENT_ID = -1
5893: INTO l_res_assgn_id_tab,
5894: l_rlm_id_tab,
5895: l_cbs_element_id_tab, --bug#16827157
5896: l_txn_task_id_tab
5897: FROM PA_RESOURCE_ASSIGNMENTS P,
5898: PA_RES_LIST_MAP_TMP4 T
5899: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
5900: AND NVL(P.TASK_ID,0) = NVL(T.TXN_TASK_ID,0)
5901: AND P.PROJECT_ASSIGNMENT_ID = -1
5930: l_rlm_id_tab,
5931: l_cbs_element_id_tab, --bug#16827157
5932: l_txn_top_task_id_tab,
5933: l_txn_sub_task_id_tab
5934: FROM PA_RESOURCE_ASSIGNMENTS P,
5935: PA_RES_LIST_MAP_TMP4 T,
5936: PA_TASKS TS
5937: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
5938: AND TS.TASK_ID(+) = NVL(T.TXN_TASK_ID,0)
5971: P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
5972: P.CBS_ELEMENT_ID cbs_element_id, --bug#16827157
5973: NVL(T.TXN_TASK_ID,0) txn_task_id ,
5974: NVL(V.MAPPED_FIN_TASK_ID,0) mapped_fin_task_id
5975: FROM PA_RESOURCE_ASSIGNMENTS P,
5976: PA_RES_LIST_MAP_TMP4 T,
5977: PA_MAP_WP_TO_FIN_TASKS_V V
5978: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
5979: AND V.PARENT_STRUCTURE_VERSION_ID = P_WP_STRUCTURE_VER_ID
5989: P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
5990: P.CBS_ELEMENT_ID cbs_element_id, --bug#16827157
5991: 0 txn_task_id,
5992: 0 mapped_fin_task_id
5993: FROM PA_RESOURCE_ASSIGNMENTS P,
5994: PA_RES_LIST_MAP_TMP4 T
5995: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
5996: AND P.PROJECT_ASSIGNMENT_ID = -1
5997: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
6070: P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
6071: P.CBS_ELEMENT_ID cbs_element_id, --bug#16827157
6072: NVL(T.TXN_TASK_ID,0) txn_task_id,
6073: NVL(V.MAPPED_FIN_TASK_ID,0) mapped_fin_task_id
6074: FROM PA_RESOURCE_ASSIGNMENTS P,
6075: PA_RES_LIST_MAP_TMP4 T,
6076: PA_MAP_WP_TO_FIN_TASKS_V V,
6077: PA_TASKS TS
6078: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
6090: P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
6091: P.CBS_ELEMENT_ID cbs_element_id, --bug#16827157
6092: 0 txn_task_id,
6093: 0 mapped_fin_task_id
6094: FROM PA_RESOURCE_ASSIGNMENTS P,
6095: PA_RES_LIST_MAP_TMP4 T
6096: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
6097: AND P.PROJECT_ASSIGNMENT_ID = -1
6098: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
6125: IF p_fp_cols_rec.x_plan_class_code = 'BUDGET' THEN
6126: DELETE FROM pa_res_list_map_tmp4 tmp
6127: WHERE EXISTS
6128: ( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
6129: FROM pa_resource_assignments ra
6130: WHERE ra.budget_version_id = p_budget_version_id
6131: AND ra.resource_assignment_id = tmp.txn_resource_assignment_id
6132: AND ra.transaction_source_code IS NULL
6133: AND EXISTS
6140: ( p_budget_version_id );
6141: DELETE FROM pa_res_list_map_tmp4 tmp
6142: WHERE EXISTS
6143: ( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
6144: FROM pa_resource_assignments ra
6145: WHERE ra.budget_version_id = p_budget_version_id
6146: AND ra.resource_assignment_id = tmp.txn_resource_assignment_id
6147: AND ra.transaction_source_code IS NULL
6148: AND EXISTS
6167: --WHERE txn_budget_version_id = p_budget_version_id
6168: GROUP BY txn_resource_assignment_id;
6169:
6170: FORALL j IN 1..l_txn_res_asg_id_tab.count
6171: UPDATE PA_RESOURCE_ASSIGNMENTS
6172: SET PLANNING_START_DATE = l_txn_plan_start_date_tab(j),
6173: PLANNING_END_DATE = l_txn_plan_end_date_tab(j)
6174: WHERE RESOURCE_ASSIGNMENT_ID = l_txn_res_asg_id_tab(j);
6175:
6179: INTO l_spread_curve_id
6180: FROM pa_spread_curves_b
6181: WHERE spread_curve_code = 'FIXED_DATE';
6182:
6183: UPDATE PA_RESOURCE_ASSIGNMENTS
6184: SET SP_FIXED_DATE = PLANNING_START_DATE
6185: WHERE SP_FIXED_DATE IS NULL
6186: AND SPREAD_CURVE_ID = l_spread_curve_id
6187: AND budget_version_id = p_budget_version_id
6198: -- Lines logic is handled, so replaced previous update logic.
6199:
6200: --Bug 4198901 : Spread curve should be null when generate from source plan where actual exist
6201: -- added update for spread_curve_id /sp_fixed_date
6202: UPDATE PA_RESOURCE_ASSIGNMENTS
6203: SET transaction_source_code = p_gen_src_code,
6204: sp_fixed_date = decode (p_gen_src_code, 'RESOURCE_SCHEDULE', NULL, sp_fixed_date),
6205: spread_curve_id = decode (p_gen_src_code, 'RESOURCE_SCHEDULE', NULL, spread_curve_id)
6206: WHERE budget_version_id = p_budget_version_id
7084: l_rev_res_class_rt_sch_id_tab,
7085: l_cost_res_class_rt_sch_id_tab,
7086:
7087: l_res_format_id_tab
7088: FROM pa_fp_calc_amt_tmp2 tmp, pa_resource_assignments ra,
7089: pa_projects_all proj, pa_tasks t,
7090: pa_proj_fp_options fp,
7091: pa_resource_list_members rlm
7092: WHERE tmp.resource_assignment_id = ra.resource_assignment_id
7628: l_rev_res_class_rt_sch_id_tab,
7629: l_cost_res_class_rt_sch_id_tab,
7630:
7631: l_res_format_id_tab
7632: FROM pa_fp_calc_amt_tmp2 tmp, pa_resource_assignments ra,
7633: pa_projects_all proj, pa_tasks t,
7634: pa_proj_fp_options fp,
7635: pa_resource_list_members rlm
7636: WHERE tmp.resource_assignment_id = ra.resource_assignment_id