[Home] [Help]
917: /* Insert the distinct target task_id and rlm_id values from tmp3 into tmp4.
918: * These are the only resources that have planned amounts in the baselined
919: * approved cost budget and are therefore the only resources that can possibly
920: * have unspent amounts. */
921: DELETE PA_RES_LIST_MAP_TMP4;
922: INSERT INTO PA_RES_LIST_MAP_TMP4
923: ( txn_task_id,
924: txn_resource_list_member_id )
925: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP3,PA_FP_CALC_AMT_TMP3_N1)*/
918: * These are the only resources that have planned amounts in the baselined
919: * approved cost budget and are therefore the only resources that can possibly
920: * have unspent amounts. */
921: DELETE PA_RES_LIST_MAP_TMP4;
922: INSERT INTO PA_RES_LIST_MAP_TMP4
923: ( txn_task_id,
924: txn_resource_list_member_id )
925: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP3,PA_FP_CALC_AMT_TMP3_N1)*/
926: DISTINCT
928: res_list_member_id
929: FROM PA_FP_CALC_AMT_TMP3
930: WHERE plan_version_id = p_app_cost_bdgt_ver_id;
931:
932: select count(*) into l_count from pa_res_list_map_tmp4 where rownum=1;
933: --dbms_output.put_line('Number of records inserted into tmp4 from tmp3 = ' || l_count);
934:
935: IF l_count = 0 THEN
936: IF p_pa_debug_mode = 'Y' THEN
943: PA_FP_GEN_AMOUNT_UTILS.GET_ETC_START_DATE(p_budget_version_id);
944:
945: /* Get target resource assignment ids. */
946: IF p_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'N' THEN
947: SELECT /*+ INDEX(tmp4,PA_RES_LIST_MAP_TMP4_N1)*/
948: ra.resource_assignment_id,
949: ra.task_id,
950: ra.resource_list_member_id,
951: ra.rate_based_flag,
958: l_rate_based_flag_tab,
959: l_planning_start_date_tab,
960: l_planning_end_date_tab
961: FROM pa_resource_assignments ra,
962: pa_res_list_map_tmp4 tmp4
963: WHERE ra.budget_version_id = p_budget_version_id
964: AND ra.task_id = tmp4.txn_task_id
965: AND ra.resource_list_member_id = tmp4.txn_resource_list_member_id
966: ORDER BY ra.resource_assignment_id ASC;
964: AND ra.task_id = tmp4.txn_task_id
965: AND ra.resource_list_member_id = tmp4.txn_resource_list_member_id
966: ORDER BY ra.resource_assignment_id ASC;
967: ELSIF p_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'Y' THEN
968: SELECT /*+ INDEX(tmp4,PA_RES_LIST_MAP_TMP4_N1)*/
969: ra.resource_assignment_id,
970: ra.task_id,
971: ra.resource_list_member_id,
972: ra.rate_based_flag,
979: l_rate_based_flag_tab,
980: l_planning_start_date_tab,
981: l_planning_end_date_tab
982: FROM pa_resource_assignments ra,
983: pa_res_list_map_tmp4 tmp4
984: WHERE ra.budget_version_id = p_budget_version_id
985: AND ra.task_id = tmp4.txn_task_id
986: AND ra.resource_list_member_id = tmp4.txn_resource_list_member_id
987: AND ( ra.transaction_source_code IS NOT NULL
997:
998: /* Add target task_id, rlm_id, and ra_id values from pl/sql tables into tmp4.
999: * We delete tmp4 and insert new lines instead of updating the existing ones
1000: * to simplify the manually added plan lines logic. */
1001: DELETE PA_RES_LIST_MAP_TMP4;
1002: FORALL i IN 1..l_res_asg_id_tab.count
1003: INSERT INTO PA_RES_LIST_MAP_TMP4
1004: ( txn_task_id,
1005: txn_resource_list_member_id,
999: * We delete tmp4 and insert new lines instead of updating the existing ones
1000: * to simplify the manually added plan lines logic. */
1001: DELETE PA_RES_LIST_MAP_TMP4;
1002: FORALL i IN 1..l_res_asg_id_tab.count
1003: INSERT INTO PA_RES_LIST_MAP_TMP4
1004: ( txn_task_id,
1005: txn_resource_list_member_id,
1006: txn_resource_assignment_id )
1007: VALUES
1008: ( l_task_id_tab(i),
1009: l_res_list_mem_id_tab(i),
1010: l_res_asg_id_tab(i) );
1011:
1012: select count(*) into l_count from pa_res_list_map_tmp4 where rownum=1;
1013: --dbms_output.put_line('Number of target resources in tmp4 to be processed = ' || l_count);
1014:
1015: IF l_count = 0 THEN
1016: IF p_pa_debug_mode = 'Y' THEN
1019: RETURN;
1020: END IF;
1021:
1022: /* Bulk collect plan amounts, ordered by ascending ra_id. */
1023: SELECT /*+ INDEX(tmp4,PA_RES_LIST_MAP_TMP4_N1) INDEX(bl,PA_FP_CALC_AMT_TMP3_N1)*/
1024: tmp4.txn_resource_assignment_id,
1025: bl.txn_currency_code,
1026: nvl(sum(nvl(bl.quantity,0)),0),
1027: nvl(sum(nvl(bl.pc_raw_cost,0)),0),
1036: l_plan_txn_raw_cost_tab,
1037: l_plan_pc_burd_cost_tab,
1038: l_plan_txn_burd_cost_tab
1039: FROM pa_fp_calc_amt_tmp3 bl,
1040: pa_res_list_map_tmp4 tmp4
1041: WHERE bl.plan_version_id = p_app_cost_bdgt_ver_id
1042: AND bl.task_id = tmp4.txn_task_id
1043: AND bl.res_list_member_id = tmp4.txn_resource_list_member_id
1044: GROUP BY tmp4.txn_resource_assignment_id,
1065: l_init_txn_raw_cost_tab,
1066: l_init_pc_burd_cost_tab,
1067: l_init_txn_burd_cost_tab
1068: FROM pa_budget_lines bl,
1069: pa_res_list_map_tmp4 tmp4
1070: WHERE bl.resource_assignment_id = tmp4.txn_resource_assignment_id
1071: AND bl.start_date < l_etc_start_date
1072: GROUP BY tmp4.txn_resource_assignment_id,
1073: bl.txn_currency_code
1349:
1350: /* If the resource uses Fixed Date spread and the fixed date is not in the
1351: * unspent amounts period, then NULL out the spread curve and fixed date. */
1352:
1353: DELETE PA_RES_LIST_MAP_TMP4;
1354: FORALL i IN 1..l_ins_ra_id_tab.count
1355: INSERT INTO PA_RES_LIST_MAP_TMP4
1356: ( txn_resource_assignment_id )
1357: VALUES ( l_ins_ra_id_tab(i) );
1351: * unspent amounts period, then NULL out the spread curve and fixed date. */
1352:
1353: DELETE PA_RES_LIST_MAP_TMP4;
1354: FORALL i IN 1..l_ins_ra_id_tab.count
1355: INSERT INTO PA_RES_LIST_MAP_TMP4
1356: ( txn_resource_assignment_id )
1357: VALUES ( l_ins_ra_id_tab(i) );
1358:
1359: SELECT spread_curve_id
1368: ra.resource_assignment_id
1369: BULK COLLECT
1370: INTO l_fixed_date_ra_id_tab
1371: FROM pa_resource_assignments ra,
1372: pa_res_list_map_tmp4 tmp4
1373: WHERE ra.resource_assignment_id = tmp4.txn_resource_assignment_id
1374: AND ra.spread_curve_id = l_fixed_date_curve_id
1375: AND NOT ( ra.sp_fixed_date BETWEEN l_start_date AND l_end_date );
1376: