[Home] [Help]
1140: RLM.ALIAS
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 */
1215: RLM.ALIAS
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'
1289: RLM.RESOURCE_LIST_ID,
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'
2358: END IF;
2359: END LOOP;
2360:
2361: DELETE FROM PA_RES_LIST_MAP_TMP1;
2362: DELETE FROM PA_RES_LIST_MAP_TMP4;
2363:
2364: l_TXN_SOURCE_ID_tab := l_proj_assignment_id;
2365: l_PERSON_ID_tab := l_proj_person_id;
2366: l_JOB_ID_tab := l_proj_fcst_job_id;
2459: IF x_return_status <> 'S' THEN
2460: RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2461: END IF;
2462:
2463: SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ count(*) INTO l_count
2464: FROM PA_RES_LIST_MAP_TMP4
2465: WHERE RESOURCE_LIST_MEMBER_ID IS NULL and rownum=1;
2466: IF l_count > 0 THEN
2467: PA_UTILS.ADD_MESSAGE
2460: RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2461: END IF;
2462:
2463: SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/ count(*) INTO l_count
2464: FROM PA_RES_LIST_MAP_TMP4
2465: WHERE RESOURCE_LIST_MEMBER_ID IS NULL and rownum=1;
2466: IF l_count > 0 THEN
2467: PA_UTILS.ADD_MESSAGE
2468: (p_app_short_name => 'PA',
2470: RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2471: END IF;
2472:
2473: SELECT count(*) INTO l_count
2474: FROM PA_RES_LIST_MAP_TMP4;
2475: IF p_pa_debug_mode = 'Y' THEN
2476: pa_fp_gen_amount_utils.fp_debug
2477: (p_called_mode => p_called_mode,
2478: p_msg => 'After calling pa_resource_mapping.map_resource_list,'||
2475: IF p_pa_debug_mode = 'Y' THEN
2476: pa_fp_gen_amount_utils.fp_debug
2477: (p_called_mode => p_called_mode,
2478: p_msg => 'After calling pa_resource_mapping.map_resource_list,'||
2479: 'pa_res_list_map_tmp4.count has '||l_count||' rows',
2480: p_module_name => l_module_name,
2481: p_log_level => 5);
2482: END IF;
2483:
2567: l_proj_res_assgn_id_tab,
2568: l_proj_bill_rate_override_tab, -- M-Closeout ER: Bill Rate Override ER
2569: l_proj_bill_rate_cur_ovrd_tab, -- M-Closeout ER: Bill Rate Override ER
2570: l_proj_billable_flag -- M-Closeout ER: Honor billability flag ER
2571: FROM PA_RES_LIST_MAP_TMP4 tmp4,
2572: PA_PROJECT_ASSIGNMENTS PA,
2573: PA_WORK_TYPES_B WB
2574: WHERE tmp4.txn_source_id = pa.assignment_id
2575: AND WB.WORK_TYPE_ID = PA.WORK_TYPE_ID(+);
4409: CURSOR RES_ASG1 IS
4410: SELECT T.RESOURCE_LIST_MEMBER_ID,
4411: MIN(T.TXN_PLANNING_START_DATE),
4412: MAX(T.TXN_PLANNING_END_DATE)
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
4411: MIN(T.TXN_PLANNING_START_DATE),
4412: MAX(T.TXN_PLANNING_END_DATE)
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
4426: SELECT T.RESOURCE_LIST_MEMBER_ID,
4427: NVL(T.TXN_TASK_ID,0),
4428: MIN(T.TXN_PLANNING_START_DATE),
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
4428: MIN(T.TXN_PLANNING_START_DATE),
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
4443: SELECT T.RESOURCE_LIST_MEMBER_ID,
4444: NVL(PAT.TOP_TASK_ID,0),
4445: MIN(T.TXN_PLANNING_START_DATE),
4446: MAX(T.TXN_PLANNING_END_DATE)
4447: FROM PA_RES_LIST_MAP_TMP4 T,
4448: PA_TASKS PAT
4449: WHERE NVL(T.TXN_TASK_ID,0) > 0
4450: AND NVL(T.TXN_TASK_ID,0) = PAT.TASK_ID
4451: AND NOT EXISTS
4448: PA_TASKS PAT
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)
4463: SELECT T.RESOURCE_LIST_MEMBER_ID,
4464: 0,
4465: MIN(T.TXN_PLANNING_START_DATE),
4466: MAX(T.TXN_PLANNING_END_DATE)
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
4466: MAX(T.TXN_PLANNING_END_DATE)
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
4485: NVL(V1.MAPPED_FIN_TASK_ID,0) mapped_fin_task_id,
4486: NVL(V1.MAPPED_FIN_TASK_VERSION_ID,0) mapped_fin_task_version_id,
4487: MIN(T.TXN_PLANNING_START_DATE) txn_planning_start_date,
4488: MAX(T.TXN_PLANNING_END_DATE) txn_planning_end_date
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
4488: MAX(T.TXN_PLANNING_END_DATE) txn_planning_end_date
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)
4509: 0 mapped_fin_task_id,
4510: 0 mapped_fin_task_version_id,
4511: MIN(T.TXN_PLANNING_START_DATE) txn_planning_start_date,
4512: MAX(T.TXN_PLANNING_END_DATE) txn_planning_end_date
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
4512: MAX(T.TXN_PLANNING_END_DATE) txn_planning_end_date
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
4531: NVL(pa_proj_elements_utils.get_task_version_id(
4532: v1.MAPPED_FIN_STR_VERSION_ID,pat.top_task_id),0),
4533: MIN(T.TXN_PLANNING_START_DATE),
4534: MAX(T.TXN_PLANNING_END_DATE)
4535: FROM PA_RES_LIST_MAP_TMP4 T,
4536: PA_TASKS PAT,
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
4536: PA_TASKS PAT,
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)
4558: 0,
4559: 0,
4560: MIN(T.TXN_PLANNING_START_DATE) txn_planning_start_date,
4561: MAX(T.TXN_PLANNING_END_DATE) txn_planning_end_date
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
4561: MAX(T.TXN_PLANNING_END_DATE) txn_planning_end_date
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
4833: AND l_stru_sharing_code IS NOT NULL THEN
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
5220: IF P_FP_COLS_REC.X_FIN_PLAN_LEVEL_CODE = 'P' or
5221: P_GEN_SRC_CODE = 'RESOURCE_SCHEDULE' THEN
5222:
5223: /* Updating the TMP4 table with resource_assignment_id */
5224: SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
5225: distinct P.RESOURCE_ASSIGNMENT_ID,
5226: P.RESOURCE_LIST_MEMBER_ID
5227: BULK COLLECT
5228: INTO l_res_assgn_id_tab,
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
5235: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID;
5234: AND P.PROJECT_ASSIGNMENT_ID = -1
5235: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID;
5236:
5237: FORALL i IN 1..l_res_assgn_id_tab.count
5238: UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
5239: PA_RES_LIST_MAP_TMP4
5240: SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
5241: WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i);
5242: /* AND NVL(TXN_TASK_ID,0) = l_txn_task_id_tab(i);
5235: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID;
5236:
5237: FORALL i IN 1..l_res_assgn_id_tab.count
5238: UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
5239: PA_RES_LIST_MAP_TMP4
5240: SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
5241: WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i);
5242: /* AND NVL(TXN_TASK_ID,0) = l_txn_task_id_tab(i);
5243: task id check is not required. commented for bug 3475017 */
5249: l_stru_sharing_code = 'SHARE_FULL' OR
5250: P_GEN_SRC_CODE IN ( 'FINANCIAL_PLAN',
5251: 'OPEN_COMMITMENTS','BILLING_EVENTS' )) THEN
5252:
5253: SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
5254: P.RESOURCE_ASSIGNMENT_ID,
5255: P.RESOURCE_LIST_MEMBER_ID,
5256: NVL(T.TXN_TASK_ID,0)
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
5266: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID;
5265: AND P.PROJECT_ASSIGNMENT_ID = -1
5266: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID;
5267:
5268: FORALL i IN 1..l_res_assgn_id_tab.count
5269: UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
5270: PA_RES_LIST_MAP_TMP4
5271: SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
5272: WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i)
5273: AND NVL(TXN_TASK_ID,0) = l_txn_task_id_tab(i);
5266: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID;
5267:
5268: FORALL i IN 1..l_res_assgn_id_tab.count
5269: UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
5270: PA_RES_LIST_MAP_TMP4
5271: SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
5272: WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i)
5273: AND NVL(TXN_TASK_ID,0) = l_txn_task_id_tab(i);
5274:
5279: l_stru_sharing_code = 'SHARE_FULL' OR
5280: P_GEN_SRC_CODE IN ( 'FINANCIAL_PLAN',
5281: 'OPEN_COMMITMENTS','BILLING_EVENTS' )) THEN
5282:
5283: SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
5284: P.RESOURCE_ASSIGNMENT_ID,
5285: P.RESOURCE_LIST_MEMBER_ID,
5286: NVL(P.TASK_ID,0),
5287: NVL(T.TXN_TASK_ID,0)
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)
5298: AND NVL(P.TASK_ID,0) = NVL(TS.TOP_TASK_ID,0)
5299: AND P.PROJECT_ASSIGNMENT_ID = -1
5300: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID;
5301:
5302: FORALL i IN 1..l_res_assgn_id_tab.count
5303: UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
5304: PA_RES_LIST_MAP_TMP4 tmp4
5305: SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
5306: WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i)
5307: AND NVL(TXN_TASK_ID,0) = l_txn_sub_task_id_tab(i);
5300: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID;
5301:
5302: FORALL i IN 1..l_res_assgn_id_tab.count
5303: UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
5304: PA_RES_LIST_MAP_TMP4 tmp4
5305: SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
5306: WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i)
5307: AND NVL(TXN_TASK_ID,0) = l_txn_sub_task_id_tab(i);
5308:
5320: l_txn_task_id_tab,
5321: l_mapped_task_id_tab
5322: FROM
5323: (
5324: SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
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
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
5334: AND NVL(T.TXN_TASK_ID,0) = NVL(V.PROJ_ELEMENT_ID,0)
5336: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
5337: AND NVL(P.TASK_ID,0) = NVL(V.MAPPED_FIN_TASK_ID,0)
5338: AND NVL(T.TXN_TASK_ID,0) > 0
5339: union
5340: SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
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
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
5350: AND NVL(P.TASK_ID,0) = 0 );
5376: --dbms_output.put_line('@@l_txn_task_id_tab(1):'||l_txn_task_id_tab(1));
5377: --dbms_output.put_line('@@l_txn_task_id_tab(2):'||l_txn_task_id_tab(2));
5378: --dbms_output.put_line('@@l_txn_task_id_tab(3):'||l_txn_task_id_tab(3));
5379: --dbms_output.put_line('@@l_txn_task_id_tab(4):'||l_txn_task_id_tab(4));
5380: --select count(*) into tmp_count from PA_RES_LIST_MAP_TMP4;
5381: --dbms_output.put_line('@@l_count of tmp4:'||tmp_count);
5382: --select txn_resource_assignment_id,resource_list_member_id, txn_task_id
5383: --bulk collect into tmp_ra_id_tab, tmp_rlm_id_tab, tmp_task_id_tab
5384: --from PA_RES_LIST_MAP_TMP4;
5380: --select count(*) into tmp_count from PA_RES_LIST_MAP_TMP4;
5381: --dbms_output.put_line('@@l_count of tmp4:'||tmp_count);
5382: --select txn_resource_assignment_id,resource_list_member_id, txn_task_id
5383: --bulk collect into tmp_ra_id_tab, tmp_rlm_id_tab, tmp_task_id_tab
5384: --from PA_RES_LIST_MAP_TMP4;
5385: --dbms_output.put_line('@@tmp_ra_id_tab.count'||tmp_ra_id_tab.count);
5386: --dbms_output.put_line('@@tmp_ra_id_tab(1):'||tmp_ra_id_tab(1));
5387: --dbms_output.put_line('@@tmp_ra_id_tab(2):'||tmp_ra_id_tab(2));
5388: --dbms_output.put_line('@@tmp_ra_id_tab(3):'||tmp_ra_id_tab(3));
5393: --dbms_output.put_line('@@tmp_task_id_tab(2):'||tmp_task_id_tab(2));
5394: --dbms_output.put_line('@@tmp_task_id_tab(3):'||tmp_task_id_tab(3));
5395:
5396: FORALL i IN 1..l_res_assgn_id_tab.count
5397: UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
5398: PA_RES_LIST_MAP_TMP4
5399: SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
5400: WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i)
5401: AND NVL(TXN_TASK_ID,0) = l_txn_task_id_tab(i);
5394: --dbms_output.put_line('@@tmp_task_id_tab(3):'||tmp_task_id_tab(3));
5395:
5396: FORALL i IN 1..l_res_assgn_id_tab.count
5397: UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
5398: PA_RES_LIST_MAP_TMP4
5399: SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
5400: WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i)
5401: AND NVL(TXN_TASK_ID,0) = l_txn_task_id_tab(i);
5402:
5412: l_txn_task_id_tab,
5413: l_mapped_task_id_tab
5414: FROM
5415: (
5416: SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
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
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
5426: AND V.PARENT_STRUCTURE_VERSION_ID = P_WP_STRUCTURE_VER_ID
5430: AND P.PROJECT_ASSIGNMENT_ID = -1
5431: AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
5432: AND NVL(T.TXN_TASK_ID,0) > 0
5433: union
5434: SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
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
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
5444: AND NVL(P.TASK_ID,0) = 0
5444: AND NVL(P.TASK_ID,0) = 0
5445: AND NVL(T.TXN_TASK_ID,0) = NVL(P.TASK_ID,0) );
5446:
5447: FORALL i IN 1..l_res_assgn_id_tab.count
5448: UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
5449: PA_RES_LIST_MAP_TMP4
5450: SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
5451: WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i)
5452: AND NVL(TXN_TASK_ID,0) = l_txn_task_id_tab(i);
5445: AND NVL(T.TXN_TASK_ID,0) = NVL(P.TASK_ID,0) );
5446:
5447: FORALL i IN 1..l_res_assgn_id_tab.count
5448: UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
5449: PA_RES_LIST_MAP_TMP4
5450: SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
5451: WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i)
5452: AND NVL(TXN_TASK_ID,0) = l_txn_task_id_tab(i);
5453:
5460: -- (e.g. when Target is planned at a higher level than the source or when
5461: -- the structure is Partially Shared).
5462:
5463: /* If the Retain Manually Added Plan Lines option is enabled, we remove
5464: * all rows in the PA_RES_LIST_MAP_TMP4 table with target resources that
5465: * have manually added plan lines. Thus, after this point, we can use the
5466: * mapping table without checking for the manually added lines condition. */
5467: IF p_fp_cols_rec.x_gen_ret_manual_line_flag = 'Y' THEN
5468: IF p_fp_cols_rec.x_plan_class_code = 'BUDGET' THEN
5465: * have manually added plan lines. Thus, after this point, we can use the
5466: * mapping table without checking for the manually added lines condition. */
5467: IF p_fp_cols_rec.x_gen_ret_manual_line_flag = 'Y' THEN
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
5467: IF p_fp_cols_rec.x_gen_ret_manual_line_flag = 'Y' THEN
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
5480: AND rownum = 1 ));
5481: ELSIF p_fp_cols_rec.x_plan_class_code = 'FORECAST' THEN
5482: l_etc_start_date := PA_FP_GEN_AMOUNT_UTILS.GET_ETC_START_DATE
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
5482: l_etc_start_date := PA_FP_GEN_AMOUNT_UTILS.GET_ETC_START_DATE
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
5505: BULK COLLECT
5506: INTO l_txn_res_asg_id_tab,
5507: l_txn_plan_start_date_tab,
5508: l_txn_plan_end_date_tab
5509: FROM PA_RES_LIST_MAP_TMP4
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
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
5531: AND EXISTS ( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
5532: FROM pa_res_list_map_tmp4 tmp
5533: WHERE tmp.txn_resource_assignment_id = resource_assignment_id
5534: AND rownum = 1 );
5535:
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
5531: AND EXISTS ( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
5532: FROM pa_res_list_map_tmp4 tmp
5533: WHERE tmp.txn_resource_assignment_id = resource_assignment_id
5534: AND rownum = 1 );
5535:
5536: -- Bug 3973015: Added the NOT EXISTS condition to the WHERE clause
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
5550: AND EXISTS ( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
5551: FROM pa_res_list_map_tmp4 tmp
5552: WHERE tmp.txn_resource_assignment_id = resource_assignment_id
5553: AND rownum = 1 );
5554:
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
5550: AND EXISTS ( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
5551: FROM pa_res_list_map_tmp4 tmp
5552: WHERE tmp.txn_resource_assignment_id = resource_assignment_id
5553: AND rownum = 1 );
5554:
5555: IF P_PA_DEBUG_MODE = 'Y' THEN
5826: IF P_CHK_DUPLICATE_FLAG = 'N' THEN
5827: SELECT DISTINCT TXN_RESOURCE_ASSIGNMENT_ID
5828: BULK COLLECT
5829: INTO PX_GEN_RES_ASG_ID_TAB
5830: FROM PA_RES_LIST_MAP_TMP4;
5831: END IF;
5832:
5833: /*Code checking for duplicate res_asg_id */
5834: FOR i IN 1..PX_GEN_RES_ASG_ID_TAB.count LOOP
5837:
5838: SELECT DISTINCT TXN_RESOURCE_ASSIGNMENT_ID
5839: BULK COLLECT
5840: INTO l_cmt_res_id_tab
5841: FROM PA_RES_LIST_MAP_TMP4;
5842:
5843: IF l_cmt_res_id_tab.count > 0 THEN
5844: FOR k IN 1..l_cmt_res_id_tab.count LOOP
5845: l_exist_flag := 'N';