[Home] [Help]
904: /* Insert the distinct target task_id and rlm_id values from tmp3 into tmp4.
905: * These are the only resources that have planned amounts in the baselined
906: * approved cost budget and are therefore the only resources that can possibly
907: * have unspent amounts. */
908: DELETE PA_RES_LIST_MAP_TMP4;
909: INSERT INTO PA_RES_LIST_MAP_TMP4
910: ( txn_task_id,
911: txn_resource_list_member_id )
912: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP3,PA_FP_CALC_AMT_TMP3_N1)*/
905: * These are the only resources that have planned amounts in the baselined
906: * approved cost budget and are therefore the only resources that can possibly
907: * have unspent amounts. */
908: DELETE PA_RES_LIST_MAP_TMP4;
909: INSERT INTO PA_RES_LIST_MAP_TMP4
910: ( txn_task_id,
911: txn_resource_list_member_id )
912: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP3,PA_FP_CALC_AMT_TMP3_N1)*/
913: DISTINCT
915: res_list_member_id
916: FROM PA_FP_CALC_AMT_TMP3
917: WHERE plan_version_id = p_app_cost_bdgt_ver_id;
918:
919: select count(*) into l_count from pa_res_list_map_tmp4 where rownum=1;
920: --dbms_output.put_line('Number of records inserted into tmp4 from tmp3 = ' || l_count);
921:
922: IF l_count = 0 THEN
923: IF p_pa_debug_mode = 'Y' THEN
930: PA_FP_GEN_AMOUNT_UTILS.GET_ETC_START_DATE(p_budget_version_id);
931:
932: /* Get target resource assignment ids. */
933: IF p_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'N' THEN
934: SELECT /*+ INDEX(tmp4,PA_RES_LIST_MAP_TMP4_N1)*/
935: ra.resource_assignment_id,
936: ra.task_id,
937: ra.resource_list_member_id,
938: ra.rate_based_flag,
945: l_rate_based_flag_tab,
946: l_planning_start_date_tab,
947: l_planning_end_date_tab
948: FROM pa_resource_assignments ra,
949: pa_res_list_map_tmp4 tmp4
950: WHERE ra.budget_version_id = p_budget_version_id
951: AND ra.task_id = tmp4.txn_task_id
952: AND ra.resource_list_member_id = tmp4.txn_resource_list_member_id
953: ORDER BY ra.resource_assignment_id ASC;
951: AND ra.task_id = tmp4.txn_task_id
952: AND ra.resource_list_member_id = tmp4.txn_resource_list_member_id
953: ORDER BY ra.resource_assignment_id ASC;
954: ELSIF p_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'Y' THEN
955: SELECT /*+ INDEX(tmp4,PA_RES_LIST_MAP_TMP4_N1)*/
956: ra.resource_assignment_id,
957: ra.task_id,
958: ra.resource_list_member_id,
959: ra.rate_based_flag,
966: l_rate_based_flag_tab,
967: l_planning_start_date_tab,
968: l_planning_end_date_tab
969: FROM pa_resource_assignments ra,
970: pa_res_list_map_tmp4 tmp4
971: WHERE ra.budget_version_id = p_budget_version_id
972: AND ra.task_id = tmp4.txn_task_id
973: AND ra.resource_list_member_id = tmp4.txn_resource_list_member_id
974: AND ( ra.transaction_source_code IS NOT NULL
984:
985: /* Add target task_id, rlm_id, and ra_id values from pl/sql tables into tmp4.
986: * We delete tmp4 and insert new lines instead of updating the existing ones
987: * to simplify the manually added plan lines logic. */
988: DELETE PA_RES_LIST_MAP_TMP4;
989: FORALL i IN 1..l_res_asg_id_tab.count
990: INSERT INTO PA_RES_LIST_MAP_TMP4
991: ( txn_task_id,
992: txn_resource_list_member_id,
986: * We delete tmp4 and insert new lines instead of updating the existing ones
987: * to simplify the manually added plan lines logic. */
988: DELETE PA_RES_LIST_MAP_TMP4;
989: FORALL i IN 1..l_res_asg_id_tab.count
990: INSERT INTO PA_RES_LIST_MAP_TMP4
991: ( txn_task_id,
992: txn_resource_list_member_id,
993: txn_resource_assignment_id )
994: VALUES
995: ( l_task_id_tab(i),
996: l_res_list_mem_id_tab(i),
997: l_res_asg_id_tab(i) );
998:
999: select count(*) into l_count from pa_res_list_map_tmp4 where rownum=1;
1000: --dbms_output.put_line('Number of target resources in tmp4 to be processed = ' || l_count);
1001:
1002: IF l_count = 0 THEN
1003: IF p_pa_debug_mode = 'Y' THEN
1006: RETURN;
1007: END IF;
1008:
1009: /* Bulk collect plan amounts, ordered by ascending ra_id. */
1010: SELECT /*+ INDEX(tmp4,PA_RES_LIST_MAP_TMP4_N1) INDEX(bl,PA_FP_CALC_AMT_TMP3_N1)*/
1011: tmp4.txn_resource_assignment_id,
1012: bl.txn_currency_code,
1013: nvl(sum(nvl(bl.quantity,0)),0),
1014: nvl(sum(nvl(bl.pc_raw_cost,0)),0),
1023: l_plan_txn_raw_cost_tab,
1024: l_plan_pc_burd_cost_tab,
1025: l_plan_txn_burd_cost_tab
1026: FROM pa_fp_calc_amt_tmp3 bl,
1027: pa_res_list_map_tmp4 tmp4
1028: WHERE bl.plan_version_id = p_app_cost_bdgt_ver_id
1029: AND bl.task_id = tmp4.txn_task_id
1030: AND bl.res_list_member_id = tmp4.txn_resource_list_member_id
1031: GROUP BY tmp4.txn_resource_assignment_id,
1052: l_init_txn_raw_cost_tab,
1053: l_init_pc_burd_cost_tab,
1054: l_init_txn_burd_cost_tab
1055: FROM pa_budget_lines bl,
1056: pa_res_list_map_tmp4 tmp4
1057: WHERE bl.resource_assignment_id = tmp4.txn_resource_assignment_id
1058: AND bl.start_date < l_etc_start_date
1059: GROUP BY tmp4.txn_resource_assignment_id,
1060: bl.txn_currency_code
1336:
1337: /* If the resource uses Fixed Date spread and the fixed date is not in the
1338: * unspent amounts period, then NULL out the spread curve and fixed date. */
1339:
1340: DELETE PA_RES_LIST_MAP_TMP4;
1341: FORALL i IN 1..l_ins_ra_id_tab.count
1342: INSERT INTO PA_RES_LIST_MAP_TMP4
1343: ( txn_resource_assignment_id )
1344: VALUES ( l_ins_ra_id_tab(i) );
1338: * unspent amounts period, then NULL out the spread curve and fixed date. */
1339:
1340: DELETE PA_RES_LIST_MAP_TMP4;
1341: FORALL i IN 1..l_ins_ra_id_tab.count
1342: INSERT INTO PA_RES_LIST_MAP_TMP4
1343: ( txn_resource_assignment_id )
1344: VALUES ( l_ins_ra_id_tab(i) );
1345:
1346: SELECT spread_curve_id
1355: ra.resource_assignment_id
1356: BULK COLLECT
1357: INTO l_fixed_date_ra_id_tab
1358: FROM pa_resource_assignments ra,
1359: pa_res_list_map_tmp4 tmp4
1360: WHERE ra.resource_assignment_id = tmp4.txn_resource_assignment_id
1361: AND ra.spread_curve_id = l_fixed_date_curve_id
1362: AND NOT ( ra.sp_fixed_date BETWEEN l_start_date AND l_end_date );
1363: