[Home] [Help]
226: p_module_name => l_module_name,
227: p_log_level => 5);
228: END IF;
229:
230: /* Records are deleted from pa_budget_lines and PA_RESOURCE_ASSIGNMENTS
231: tables when the flag is set to N */
232: IF l_fp_cols_rec.x_gen_ret_manual_line_flag = 'N' THEN
233: DELETE FROM PA_BUDGET_LINES
234: WHERE budget_version_id = p_budget_version_id ;
232: IF l_fp_cols_rec.x_gen_ret_manual_line_flag = 'N' THEN
233: DELETE FROM PA_BUDGET_LINES
234: WHERE budget_version_id = p_budget_version_id ;
235:
236: DELETE FROM PA_RESOURCE_ASSIGNMENTS
237: WHERE budget_version_id = p_budget_version_id ;
238:
239: -- IPM: New Entity ER ------------------------------------------
240: -- Call the maintenance api in DELETE mode
282:
283: SELECT resource_assignment_id
284: BULK COLLECT INTO
285: l_res_as_id
286: FROM PA_RESOURCE_ASSIGNMENTS
287: WHERE budget_version_id = p_budget_version_id AND
288: transaction_source_code IS NOT NULL;
289:
290: IF (l_res_as_id.count > 0) THEN
292: DELETE FROM PA_BUDGET_LINES
293: WHERE resource_assignment_id = l_res_as_id(i);
294:
295: FORALL j IN 1 .. l_res_as_id.count
296: UPDATE PA_RESOURCE_ASSIGNMENTS
297: SET transaction_source_code = null
298: WHERE resource_assignment_id = l_res_as_id(j);
299:
300: -- IPM: New Entity ER ------------------------------------------
1091: AND FI.EXPENDITURE_ORGANIZATION_ID = c_exp_organization_id
1092: GROUP BY FI.EXPENDITURE_ORG_ID,
1093: FI.EXPENDITURE_ORGANIZATION_ID;
1094:
1095: CURSOR BUDGET_LINES(c_budget_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE,
1096: c_project_id PA_RESOURCE_ASSIGNMENTS.PROJECT_ID%TYPE,
1097: c_resource_assignment_id PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE) IS
1098: SELECT BL.PERIOD_NAME,
1099: BL.START_DATE,
1092: GROUP BY FI.EXPENDITURE_ORG_ID,
1093: FI.EXPENDITURE_ORGANIZATION_ID;
1094:
1095: CURSOR BUDGET_LINES(c_budget_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE,
1096: c_project_id PA_RESOURCE_ASSIGNMENTS.PROJECT_ID%TYPE,
1097: c_resource_assignment_id PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE) IS
1098: SELECT BL.PERIOD_NAME,
1099: BL.START_DATE,
1100: BL.BURDENED_COST
1093: FI.EXPENDITURE_ORGANIZATION_ID;
1094:
1095: CURSOR BUDGET_LINES(c_budget_version_id PA_RESOURCE_ASSIGNMENTS.BUDGET_VERSION_ID%TYPE,
1096: c_project_id PA_RESOURCE_ASSIGNMENTS.PROJECT_ID%TYPE,
1097: c_resource_assignment_id PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE) IS
1098: SELECT BL.PERIOD_NAME,
1099: BL.START_DATE,
1100: BL.BURDENED_COST
1101: FROM PA_BUDGET_LINES BL,
1098: SELECT BL.PERIOD_NAME,
1099: BL.START_DATE,
1100: BL.BURDENED_COST
1101: FROM PA_BUDGET_LINES BL,
1102: PA_RESOURCE_ASSIGNMENTS RA
1103: WHERE BL.RESOURCE_ASSIGNMENT_ID = RA.RESOURCE_ASSIGNMENT_ID
1104: AND RA.BUDGET_VERSION_ID = c_budget_version_id
1105: AND RA.PROJECT_ID = c_project_id
1106: AND RA.RESOURCE_LIST_MEMBER_ID = 103
1141: FROM PA_FORECAST_ITEMS FI,
1142: PA_FORECAST_ITEM_DETAILS FID,
1143: PA_PERIODS_ALL P,
1144: PA_RES_LIST_MAP_TMP4 TMP4,
1145: PA_RESOURCE_ASSIGNMENTS RA,
1146: PA_RESOURCE_LIST_MEMBERS RLM
1147: WHERE FI.PROJECT_ORG_ID = NVL(P.ORG_ID,-99)
1148: AND P.ORG_ID = c_org_id /* Bug 5657334 */
1149: AND P.PERIOD_NAME = FI.RCVR_PA_PERIOD_NAME
1216: FROM PA_FORECAST_ITEMS FI,
1217: PA_FORECAST_ITEM_DETAILS FID,
1218: GL_PERIODS GLP,
1219: PA_RES_LIST_MAP_TMP4 TMP4,
1220: PA_RESOURCE_ASSIGNMENTS RA,
1221: PA_RESOURCE_LIST_MEMBERS RLM
1222: WHERE FI.FORECAST_ITEM_ID = FID.FORECAST_ITEM_ID
1223: AND FID.FORECAST_SUMMARIZED_CODE = 'N'
1224: AND FID.NET_ZERO_FLAG = 'N'
1290: RLM.ALIAS
1291: FROM PA_FORECAST_ITEMS FI,
1292: PA_FORECAST_ITEM_DETAILS FID,
1293: PA_RES_LIST_MAP_TMP4 TMP4,
1294: PA_RESOURCE_ASSIGNMENTS RA,
1295: PA_RESOURCE_LIST_MEMBERS RLM
1296: WHERE FI.FORECAST_ITEM_ID = FID.FORECAST_ITEM_ID
1297: AND FID.FORECAST_SUMMARIZED_CODE = 'N'
1298: AND FID.NET_ZERO_FLAG = 'N'
1634: l_proj_error_msg_code PA_PLSQL_DATATYPES.Char30TabTyp;
1635: l_proj_end_date PA_PLSQL_DATATYPES.DateTabTyp;
1636: l_proj_fc_res_type_code PA_PLSQL_DATATYPES.Char30TabTyp;
1637:
1638: l_role_error_code PA_RESOURCE_ASSIGNMENTS.PLAN_ERROR_CODE%TYPE;
1639:
1640: l_err_code VARCHAR2(30);
1641: l_err_stack VARCHAR2(2000);
1642: l_err_stage VARCHAR2(2000);
1661: l_other_reject_reason VARCHAR2(1000);
1662:
1663: l_resource_list_member_id PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_MEMBER_ID%TYPE;
1664: l_resource_id PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_MEMBER_ID%TYPE;
1665: l_resource_assignment_id PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE;
1666: l_track_as_labor_flag PA_RESOURCE_LIST_MEMBERS.TRACK_AS_LABOR_FLAG%TYPE;
1667: l_parent_member_id PA_RESOURCE_LIST_MEMBERS.PARENT_MEMBER_ID%TYPE;
1668: l_prj_res_assignment_id PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE;
1669:
1664: l_resource_id PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_MEMBER_ID%TYPE;
1665: l_resource_assignment_id PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE;
1666: l_track_as_labor_flag PA_RESOURCE_LIST_MEMBERS.TRACK_AS_LABOR_FLAG%TYPE;
1667: l_parent_member_id PA_RESOURCE_LIST_MEMBERS.PARENT_MEMBER_ID%TYPE;
1668: l_prj_res_assignment_id PA_RESOURCE_ASSIGNMENTS.RESOURCE_ASSIGNMENT_ID%TYPE;
1669:
1670: l_fcst_opt_jobcostrate_sch_id PA_FORECASTING_OPTIONS_ALL.JOB_COST_RATE_SCHEDULE_ID%TYPE;
1671:
1672: l_calling_mode VARCHAR2(50);
2018: l_t_line_start_date_tab SYSTEM.pa_date_tbl_type:=SYSTEM.pa_date_tbl_type();
2019: l_t_line_end_date_tab SYSTEM.pa_date_tbl_type:=SYSTEM.pa_date_tbl_type();
2020:
2021: l_bl_count NUMBER;
2022: l_transaction_source_code PA_RESOURCE_ASSIGNMENTS.TRANSACTION_SOURCE_CODE%TYPE;
2023:
2024: -- Bug 4548733: Added new pl/sql table to hold billability flag values for
2025: -- Calculate API. Corresponds to p_fp_task_billable_flag_tab IN parameter.
2026: l_calc_billable_flag_tab SYSTEM.pa_varchar2_1_tbl_type:=SYSTEM.pa_varchar2_1_tbl_type();
2149: DELETE FROM pa_budget_lines
2150: WHERE budget_version_id = p_budget_version_id
2151: AND budget_line_id IN
2152: (SELECT bl.budget_line_id
2153: FROM pa_budget_lines bl, pa_resource_assignments ra
2154: WHERE ra.budget_version_id = p_budget_version_id
2155: AND bl.budget_version_id = p_budget_version_id
2156: AND ra.transaction_source_code IS NOT NULL
2157: AND ra.resource_assignment_id = bl.resource_assignment_id);
2160: DELETE FROM pa_budget_lines
2161: WHERE budget_version_id = p_budget_version_id
2162: AND budget_line_id IN
2163: (SELECT bl.budget_line_id
2164: FROM pa_budget_lines bl, pa_resource_assignments ra
2165: WHERE ra.budget_version_id = p_budget_version_id
2166: AND bl.budget_version_id = p_budget_version_id
2167: AND ra.transaction_source_code IS NOT NULL
2168: AND ra.resource_assignment_id = bl.resource_assignment_id
2194: projfunc_rev_exchange_rate = DECODE(NVL(txn_init_revenue,0),0,NULL,init_revenue/txn_init_revenue)
2195: WHERE budget_version_id = p_budget_version_id
2196: AND budget_line_id IN
2197: (SELECT bl.budget_line_id
2198: FROM pa_budget_lines bl, pa_resource_assignments ra
2199: WHERE ra.budget_version_id = p_budget_version_id
2200: AND bl.budget_version_id = p_budget_version_id
2201: AND ra.transaction_source_code IS NOT NULL
2202: AND ra.resource_assignment_id = bl.resource_assignment_id);
4413: FROM PA_RES_LIST_MAP_TMP4 T
4414: WHERE NOT EXISTS
4415: (SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
4416: P.RESOURCE_LIST_MEMBER_ID
4417: FROM PA_RESOURCE_ASSIGNMENTS P
4418: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
4419: AND NVL(P.TASK_ID,0) = 0
4420: AND P.PROJECT_ASSIGNMENT_ID = -1
4421: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID)
4429: MAX(T.TXN_PLANNING_END_DATE)
4430: FROM PA_RES_LIST_MAP_TMP4 T
4431: WHERE NOT EXISTS
4432: (SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
4433: FROM PA_RESOURCE_ASSIGNMENTS P
4434: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
4435: AND NVL(P.TASK_ID,0) = NVL(T.TXN_TASK_ID,0)
4436: AND P.PROJECT_ASSIGNMENT_ID = -1
4437: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID)
4449: WHERE NVL(T.TXN_TASK_ID,0) > 0
4450: AND NVL(T.TXN_TASK_ID,0) = PAT.TASK_ID
4451: AND NOT EXISTS
4452: (SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
4453: FROM PA_RESOURCE_ASSIGNMENTS P,PA_TASKS TS
4454: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
4455: AND NVL(T.TXN_TASK_ID,0) > 0
4456: AND TS.TASK_ID = NVL(T.TXN_TASK_ID,0)
4457: AND NVL(TS.TOP_TASK_ID,0) = NVL(P.TASK_ID,0)
4467: FROM PA_RES_LIST_MAP_TMP4 T
4468: WHERE NVL(T.TXN_TASK_ID,0) = 0
4469: AND 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(T.TXN_TASK_ID,0) = 0
4474: AND NVL(P.TASK_ID,0) = 0
4475: AND P.RESOURCE_LIST_MEMBER_ID = T.RESOURCE_LIST_MEMBER_ID
4489: FROM PA_RES_LIST_MAP_TMP4 T,
4490: PA_MAP_WP_TO_FIN_TASKS_V V1
4491: WHERE NOT EXISTS
4492: (SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
4493: FROM PA_RESOURCE_ASSIGNMENTS P,PA_MAP_WP_TO_FIN_TASKS_V V
4494: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
4495: AND V.PARENT_STRUCTURE_VERSION_ID = P_WP_STRUCTURE_VER_ID
4496: AND nvl(T.TXN_TASK_ID,0) = NVL(V.PROJ_ELEMENT_ID,0)
4497: AND P.PROJECT_ASSIGNMENT_ID = -1
4513: FROM PA_RES_LIST_MAP_TMP4 T
4514: WHERE NVL(T.TXN_TASK_ID,0) = 0 AND
4515: NOT EXISTS
4516: (SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
4517: FROM PA_RESOURCE_ASSIGNMENTS P
4518: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
4519: AND P.PROJECT_ASSIGNMENT_ID = -1
4520: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
4521: AND NVL(P.TASK_ID,0) = 0 )
4537: PA_MAP_WP_TO_FIN_TASKS_V V1
4538: WHERE NVL(V1.MAPPED_FIN_TASK_ID,0) = PAT.TASK_ID (+)
4539: AND NOT EXISTS
4540: (SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
4541: FROM PA_RESOURCE_ASSIGNMENTS P,PA_TASKS TS,PA_MAP_WP_TO_FIN_TASKS_V V
4542: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
4543: AND V.PARENT_STRUCTURE_VERSION_ID = P_WP_STRUCTURE_VER_ID
4544: AND NVL(T.TXN_TASK_ID,0) = NVL(V.PROJ_ELEMENT_ID,0)
4545: AND TS.TASK_ID(+) = NVL(V.MAPPED_FIN_TASK_ID,0)
4562: FROM PA_RES_LIST_MAP_TMP4 T
4563: WHERE NVL(T.TXN_TASK_ID,0) = 0 AND
4564: NOT EXISTS
4565: (SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ 1
4566: FROM PA_RESOURCE_ASSIGNMENTS P
4567: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
4568: AND P.PROJECT_ASSIGNMENT_ID = -1
4569: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
4570: AND NVL(P.TASK_ID,0) = 0)
4625: l_da_rate_func_curr_code_tab SYSTEM.PA_VARCHAR2_30_TBL_TYPE;
4626: l_da_incur_by_res_type_tab SYSTEM.PA_VARCHAR2_30_TBL_TYPE;
4627:
4628: /* Performance-Variables to replace the literals in the Insert into
4629: pa_resource_assignments stmts. */
4630: l_task_id_01 NUMBER:=0;
4631: l_proj_asg_id_minus1 NUMBER:=-1;
4632: l_res_as_type_USER_ENTERED VARCHAR2(30):='USER_ENTERED';
4633: l_rec_ver_number_1 NUMBER:=1;
4682: FROM pa_budget_versions
4683: WHERE budget_version_id=P_BUDGET_VERSION_ID;
4684:
4685: FORALL i IN 1..l_res_list_member_id.count
4686: INSERT INTO PA_RESOURCE_ASSIGNMENTS(RESOURCE_ASSIGNMENT_ID,
4687: BUDGET_VERSION_ID,
4688: PROJECT_ID,
4689: TASK_ID,
4690: RESOURCE_LIST_MEMBER_ID,
4698: record_version_number,
4699: planning_start_date,
4700: planning_end_date,
4701: transaction_source_code)
4702: VALUES (PA_RESOURCE_ASSIGNMENTS_S.nextval,
4703: P_BUDGET_VERSION_ID,
4704: P_PROJECT_ID,
4705: l_task_id_01,
4706: l_res_list_member_id(i),
4739: RETURN;
4740: END IF;
4741:
4742: FORALL i IN 1..l_res_list_member_id.count
4743: INSERT INTO PA_RESOURCE_ASSIGNMENTS(RESOURCE_ASSIGNMENT_ID,
4744: BUDGET_VERSION_ID,
4745: PROJECT_ID,
4746: TASK_ID,
4747: RESOURCE_LIST_MEMBER_ID,
4755: record_version_number,
4756: planning_start_date,
4757: planning_end_date,
4758: transaction_source_code)
4759: VALUES (PA_RESOURCE_ASSIGNMENTS_S.nextval,
4760: P_BUDGET_VERSION_ID,
4761: P_PROJECT_ID,
4762: l_task_id(i),
4763: l_res_list_member_id(i),
4794: END IF;
4795: RETURN;
4796: END IF;
4797: FORALL i IN 1..l_res_list_member_id.count
4798: INSERT INTO PA_RESOURCE_ASSIGNMENTS(RESOURCE_ASSIGNMENT_ID,
4799: BUDGET_VERSION_ID,
4800: PROJECT_ID,
4801: TASK_ID,
4802: RESOURCE_LIST_MEMBER_ID,
4810: record_version_number,
4811: planning_start_date,
4812: planning_end_date,
4813: transaction_source_code)
4814: VALUES (PA_RESOURCE_ASSIGNMENTS_S.nextval,
4815: P_BUDGET_VERSION_ID,
4816: P_PROJECT_ID,
4817: l_task_id(i),
4818: l_res_list_member_id(i),
4834: --dbms_output.put('before res_asg4 cursor');
4835:
4836: --dbms_output.put('p_wp_structure_ver_id'||p_wp_structure_ver_id);
4837: --insert into ltmp4 select * from PA_RES_LIST_MAP_TMP4;
4838: --insert into lra select * from pa_resource_assignments where budget_version_id = p_budget_version_id;
4839: OPEN RES_ASG4;
4840: FETCH RES_ASG4
4841: BULK COLLECT
4842: INTO l_res_list_member_id,
4870: --dbms_output.put_line('l_task_id:'||l_task_id(i));
4871: --END LOOP;
4872: --commit;
4873: FORALL i IN 1..l_res_list_member_id.count
4874: INSERT INTO PA_RESOURCE_ASSIGNMENTS(RESOURCE_ASSIGNMENT_ID,
4875: BUDGET_VERSION_ID,
4876: PROJECT_ID,
4877: TASK_ID,
4878: RESOURCE_LIST_MEMBER_ID,
4887: planning_end_date,
4888: record_version_number,
4889: wbs_element_version_id,
4890: transaction_source_code)
4891: VALUES (PA_RESOURCE_ASSIGNMENTS_S.nextval,
4892: P_BUDGET_VERSION_ID,
4893: P_PROJECT_ID,
4894: l_task_id(i),
4895: l_res_list_member_id(i),
4926: END IF;
4927: RETURN;
4928: END IF;
4929: FORALL i IN 1..l_res_list_member_id.count
4930: INSERT INTO PA_RESOURCE_ASSIGNMENTS(RESOURCE_ASSIGNMENT_ID,
4931: BUDGET_VERSION_ID,
4932: PROJECT_ID,
4933: TASK_ID,
4934: RESOURCE_LIST_MEMBER_ID,
4943: wbs_element_version_id,
4944: planning_start_date,
4945: planning_end_date,
4946: transaction_source_code)
4947: VALUES (PA_RESOURCE_ASSIGNMENTS_S.nextval,
4948: P_BUDGET_VERSION_ID,
4949: P_PROJECT_ID,
4950: l_task_id(i),
4951: l_res_list_member_id(i),
5073:
5074: --Bug 4052036. Changed the check in the where clause to identify the resource assignments that got inserted
5075: --thru this API.
5076: FORALL i IN 1 .. l_da_resource_list_members_tab.count
5077: UPDATE PA_RESOURCE_ASSIGNMENTS
5078: SET RESOURCE_CLASS_FLAG = l_da_resource_class_flag_tab(i),
5079: RESOURCE_CLASS_CODE = l_da_resource_class_code_tab(i),
5080: RES_TYPE_CODE = l_da_res_type_code_tab(i),
5081: PERSON_ID = l_da_person_id_tab(i),
5226: P.RESOURCE_LIST_MEMBER_ID
5227: BULK COLLECT
5228: INTO l_res_assgn_id_tab,
5229: l_rlm_id_tab
5230: FROM PA_RESOURCE_ASSIGNMENTS P,
5231: PA_RES_LIST_MAP_TMP4 T
5232: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
5233: AND NVL(P.TASK_ID,0) = 0
5234: AND P.PROJECT_ASSIGNMENT_ID = -1
5257: BULK COLLECT
5258: INTO l_res_assgn_id_tab,
5259: l_rlm_id_tab,
5260: l_txn_task_id_tab
5261: FROM PA_RESOURCE_ASSIGNMENTS P,
5262: PA_RES_LIST_MAP_TMP4 T
5263: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
5264: AND NVL(P.TASK_ID,0) = NVL(T.TXN_TASK_ID,0)
5265: AND P.PROJECT_ASSIGNMENT_ID = -1
5289: INTO l_res_assgn_id_tab,
5290: l_rlm_id_tab,
5291: l_txn_top_task_id_tab,
5292: l_txn_sub_task_id_tab
5293: FROM PA_RESOURCE_ASSIGNMENTS P,
5294: PA_RES_LIST_MAP_TMP4 T,
5295: PA_TASKS TS
5296: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
5297: AND TS.TASK_ID(+) = NVL(T.TXN_TASK_ID,0)
5325: P.RESOURCE_ASSIGNMENT_ID resource_assignment_id,
5326: P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
5327: NVL(T.TXN_TASK_ID,0) txn_task_id ,
5328: NVL(V.MAPPED_FIN_TASK_ID,0) mapped_fin_task_id
5329: FROM PA_RESOURCE_ASSIGNMENTS P,
5330: PA_RES_LIST_MAP_TMP4 T,
5331: PA_MAP_WP_TO_FIN_TASKS_V V
5332: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
5333: AND V.PARENT_STRUCTURE_VERSION_ID = P_WP_STRUCTURE_VER_ID
5341: P.RESOURCE_ASSIGNMENT_ID resource_assignment_id,
5342: P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
5343: 0 txn_task_id,
5344: 0 mapped_fin_task_id
5345: FROM PA_RESOURCE_ASSIGNMENTS P,
5346: PA_RES_LIST_MAP_TMP4 T
5347: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
5348: AND P.PROJECT_ASSIGNMENT_ID = -1
5349: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
5417: P.RESOURCE_ASSIGNMENT_ID resource_assignment_id,
5418: P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
5419: NVL(T.TXN_TASK_ID,0) txn_task_id,
5420: NVL(V.MAPPED_FIN_TASK_ID,0) mapped_fin_task_id
5421: FROM PA_RESOURCE_ASSIGNMENTS P,
5422: PA_RES_LIST_MAP_TMP4 T,
5423: PA_MAP_WP_TO_FIN_TASKS_V V,
5424: PA_TASKS TS
5425: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
5435: DISTINCT P.RESOURCE_ASSIGNMENT_ID resource_assignment_id,
5436: P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
5437: 0 txn_task_id,
5438: 0 mapped_fin_task_id
5439: FROM PA_RESOURCE_ASSIGNMENTS P,
5440: PA_RES_LIST_MAP_TMP4 T
5441: WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
5442: AND P.PROJECT_ASSIGNMENT_ID = -1
5443: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
5468: IF p_fp_cols_rec.x_plan_class_code = 'BUDGET' THEN
5469: DELETE FROM pa_res_list_map_tmp4 tmp
5470: WHERE EXISTS
5471: ( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
5472: FROM pa_resource_assignments ra
5473: WHERE ra.budget_version_id = p_budget_version_id
5474: AND ra.resource_assignment_id = tmp.txn_resource_assignment_id
5475: AND ra.transaction_source_code IS NULL
5476: AND EXISTS
5483: ( p_budget_version_id );
5484: DELETE FROM pa_res_list_map_tmp4 tmp
5485: WHERE EXISTS
5486: ( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
5487: FROM pa_resource_assignments ra
5488: WHERE ra.budget_version_id = p_budget_version_id
5489: AND ra.resource_assignment_id = tmp.txn_resource_assignment_id
5490: AND ra.transaction_source_code IS NULL
5491: AND EXISTS
5510: --WHERE txn_budget_version_id = p_budget_version_id
5511: GROUP BY txn_resource_assignment_id;
5512:
5513: FORALL j IN 1..l_txn_res_asg_id_tab.count
5514: UPDATE PA_RESOURCE_ASSIGNMENTS
5515: SET PLANNING_START_DATE = l_txn_plan_start_date_tab(j),
5516: PLANNING_END_DATE = l_txn_plan_end_date_tab(j)
5517: WHERE RESOURCE_ASSIGNMENT_ID = l_txn_res_asg_id_tab(j);
5518:
5522: INTO l_spread_curve_id
5523: FROM pa_spread_curves_b
5524: WHERE spread_curve_code = 'FIXED_DATE';
5525:
5526: UPDATE PA_RESOURCE_ASSIGNMENTS
5527: SET SP_FIXED_DATE = PLANNING_START_DATE
5528: WHERE SP_FIXED_DATE IS NULL
5529: AND SPREAD_CURVE_ID = l_spread_curve_id
5530: AND budget_version_id = p_budget_version_id
5541: -- Lines logic is handled, so replaced previous update logic.
5542:
5543: --Bug 4198901 : Spread curve should be null when generate from source plan where actual exist
5544: -- added update for spread_curve_id /sp_fixed_date
5545: UPDATE PA_RESOURCE_ASSIGNMENTS
5546: SET transaction_source_code = p_gen_src_code,
5547: sp_fixed_date = decode (p_gen_src_code, 'RESOURCE_SCHEDULE', NULL, sp_fixed_date),
5548: spread_curve_id = decode (p_gen_src_code, 'RESOURCE_SCHEDULE', NULL, spread_curve_id)
5549: WHERE budget_version_id = p_budget_version_id
6427: l_rev_res_class_rt_sch_id_tab,
6428: l_cost_res_class_rt_sch_id_tab,
6429:
6430: l_res_format_id_tab
6431: FROM pa_fp_calc_amt_tmp2 tmp, pa_resource_assignments ra,
6432: pa_projects_all proj, pa_tasks t,
6433: pa_proj_fp_options fp,
6434: pa_resource_list_members rlm
6435: WHERE tmp.resource_assignment_id = ra.resource_assignment_id
6971: l_rev_res_class_rt_sch_id_tab,
6972: l_cost_res_class_rt_sch_id_tab,
6973:
6974: l_res_format_id_tab
6975: FROM pa_fp_calc_amt_tmp2 tmp, pa_resource_assignments ra,
6976: pa_projects_all proj, pa_tasks t,
6977: pa_proj_fp_options fp,
6978: pa_resource_list_members rlm
6979: WHERE tmp.resource_assignment_id = ra.resource_assignment_id