[Home] [Help]
120: p_module_name => l_module_name);
121: END IF;
122:
123: DELETE FROM PA_FP_CALC_AMT_TMP1;
124: DELETE FROM PA_FP_CALC_AMT_TMP2;
125: IF p_fp_cols_rec.x_gen_incl_open_comm_flag = 'Y' THEN
126: IF p_pa_debug_mode = 'Y' THEN
127: PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
128: P_CALLED_MODE => P_CALLED_MODE,
1206: * cursor picks up each task's generation source. */
1207:
1208: CURSOR etc_amts_cur_wp_fp_opt_same
1209: (c_gen_etc_source_code VARCHAR2 DEFAULT NULL) IS
1210: SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1211: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1212: tmp.RESOURCE_ASSIGNMENT_ID,
1213: tmp.TARGET_RES_ASG_ID,
1214: tmp.ETC_CURRENCY_CODE,
1224: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1225: nvl(c_gen_etc_source_code, /* Added for Bug 4369741 */
1226: nvl(tmp_ra.transaction_source_code, 'NONE'))
1227: FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
1228: PA_FP_CALC_AMT_TMP2 tmp,
1229: PA_RESOURCE_ASSIGNMENTS ra,
1230: pa_tasks ta /* Added for ER 4376722 */
1231: WHERE tmp.target_res_asg_id = ra.resource_assignment_id
1232: AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
1271: * cursor picks up each task's generation source. */
1272:
1273: CURSOR etc_amts_cur_wp_fp_opt_diff
1274: (c_gen_etc_source_code VARCHAR2 DEFAULT NULL) IS
1275: SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1276: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1277: sum(1*null),
1278: tmp.TARGET_RES_ASG_ID,
1279: tmp.ETC_CURRENCY_CODE,
1289: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1290: nvl(c_gen_etc_source_code, /* Added for Bug 4369741 */
1291: nvl(tmp_ra.transaction_source_code, 'NONE'))
1292: FROM PA_FP_CALC_AMT_TMP1 tmp_ra, /* Added for Bug 4369741 */
1293: PA_FP_CALC_AMT_TMP2 tmp,
1294: PA_RESOURCE_ASSIGNMENTS ra,
1295: pa_tasks ta /* Added for ER 4376722 */
1296: WHERE tmp.target_res_asg_id = ra.resource_assignment_id
1297: AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
1324: * ALL the etc_amts_cur_wp_fp_opt_diff cursor's SELECT statement for
1325: * resources with non-Workplan source. */
1326:
1327: CURSOR etc_amts_cur_wp_opt_same IS
1328: SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1329: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1330: tmp.RESOURCE_ASSIGNMENT_ID,
1331: tmp.TARGET_RES_ASG_ID,
1332: tmp.ETC_CURRENCY_CODE,
1341: SUM(tmp.ETC_TXN_REVENUE),
1342: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1343: tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1344: FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
1345: PA_FP_CALC_AMT_TMP2 tmp,
1346: PA_RESOURCE_ASSIGNMENTS ra,
1347: pa_tasks ta /* Added for ER 4376722 */
1348: WHERE tmp.target_res_asg_id = ra.resource_assignment_id
1349: AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
1364: tmp_ra.etc_method_code,
1365: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1366: tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1367: UNION ALL
1368: SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1369: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1370: sum(1*null),
1371: tmp.TARGET_RES_ASG_ID,
1372: tmp.ETC_CURRENCY_CODE,
1381: SUM(tmp.ETC_TXN_REVENUE),
1382: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1383: tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1384: FROM PA_FP_CALC_AMT_TMP1 tmp_ra, /* Added for Bug 4369741 */
1385: PA_FP_CALC_AMT_TMP2 tmp,
1386: PA_RESOURCE_ASSIGNMENTS ra,
1387: pa_tasks ta /* Added for ER 4376722 */
1388: WHERE tmp.target_res_asg_id = ra.resource_assignment_id
1389: AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
1416: * UNION ALL the etc_amts_cur_wp_fp_opt_diff cursor's SELECT statement for
1417: * resources with non Financial Plan source. */
1418:
1419: CURSOR etc_amts_cur_fp_opt_same IS
1420: SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1421: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1422: tmp.RESOURCE_ASSIGNMENT_ID,
1423: tmp.TARGET_RES_ASG_ID,
1424: tmp.ETC_CURRENCY_CODE,
1433: SUM(tmp.ETC_TXN_REVENUE),
1434: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1435: tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1436: FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
1437: PA_FP_CALC_AMT_TMP2 tmp,
1438: PA_RESOURCE_ASSIGNMENTS ra,
1439: pa_tasks ta /* Added for ER 4376722 */
1440: WHERE tmp.target_res_asg_id = ra.resource_assignment_id
1441: AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
1456: tmp_ra.etc_method_code,
1457: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1458: tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1459: UNION ALL
1460: SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1461: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1462: sum(1*null),
1463: tmp.TARGET_RES_ASG_ID,
1464: tmp.ETC_CURRENCY_CODE,
1473: SUM(tmp.ETC_TXN_REVENUE),
1474: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1475: tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1476: FROM PA_FP_CALC_AMT_TMP1 tmp_ra, /* Added for Bug 4369741 */
1477: PA_FP_CALC_AMT_TMP2 tmp,
1478: PA_RESOURCE_ASSIGNMENTS ra,
1479: pa_tasks ta /* Added for ER 4376722 */
1480: WHERE tmp.target_res_asg_id = ra.resource_assignment_id
1481: AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
2380: *baselined cost**/
2381:
2382: /* select count(*) into l_test from Pa_fp_CALC_AMT_TMP1;
2383: hr_utility.trace('fp calc amt tmp1 tab count '||l_test);
2384: select count(*) into l_test from Pa_fp_CALC_AMT_TMP2;
2385: hr_utility.trace('fp calc amt tmp2 tab count '||l_test); */
2386:
2387: DELETE FROM PA_FP_CALC_AMT_TMP3;
2388: /* the following code is commented. We are not going to use the
2931: *is not null, otherwise, we need to populate into pa_budget_lines by ourselves**/
2932:
2933: /* select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP1;
2934: hr_utility.trace('tmp1 count :'|| l_count_tmp);
2935: select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP2;
2936: hr_utility.trace('tmp2 count :'|| l_count_tmp); */
2937: --delete from calc_amt_tmp11;
2938: --insert into calc_amt_tmp11 select * from pa_fp_calc_amt_tmp1;
2939:
2938: --insert into calc_amt_tmp11 select * from pa_fp_calc_amt_tmp1;
2939:
2940: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP1;
2941: --hr_utility.trace('***PA_FP_CALC_AMT_TMP1.count'||l_count_tmp);
2942: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP2;
2943: --hr_utility.trace('**PA_FP_CALC_AMT_TMP2.count'||l_count_tmp);
2944: --hr_utility.trace('**P_FP_COLS_REC.X_TIME_PHASED_CODE:'||P_FP_COLS_REC.X_TIME_PHASED_CODE);
2945:
2946: --l_rev_gen_method := PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(p_project_id);
2939:
2940: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP1;
2941: --hr_utility.trace('***PA_FP_CALC_AMT_TMP1.count'||l_count_tmp);
2942: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP2;
2943: --hr_utility.trace('**PA_FP_CALC_AMT_TMP2.count'||l_count_tmp);
2944: --hr_utility.trace('**P_FP_COLS_REC.X_TIME_PHASED_CODE:'||P_FP_COLS_REC.X_TIME_PHASED_CODE);
2945:
2946: --l_rev_gen_method := PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(p_project_id);
2947: l_rev_gen_method := nvl(P_FP_COLS_REC.X_REVENUE_DERIVATION_METHOD,PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(p_project_id)); --Bug 5462471
2948: IF l_fp_cols_rec_target.x_version_type = 'REVENUE' and l_rev_gen_method = 'C' THEN
2949: IF p_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'N' THEN
2950: SELECT DISTINCT target_res_asg_id
2951: BULK COLLECT INTO l_res_asg_uom_update_tab
2952: FROM PA_FP_CALC_AMT_TMP2
2953: WHERE transaction_source_code = 'ETC';
2954: ELSIF p_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'Y' THEN
2955: IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
2956: l_etc_start_date :=
2971: WHERE bl.resource_assignment_id =
2972: ra.resource_assignment_id
2973: AND bl.start_date >= l_etc_start_date
2974: AND rownum = 1 )))
2975: AND EXISTS ( SELECT /*+ INDEX(tmp2,PA_FP_CALC_AMT_TMP2_N1)*/ 1
2976: FROM PA_FP_CALC_AMT_TMP2 tmp2
2977: WHERE tmp2.target_res_asg_id = tmp1.target_res_asg_id
2978: AND tmp2.transaction_source_code = 'ETC'
2979: AND rownum = 1 );
2972: ra.resource_assignment_id
2973: AND bl.start_date >= l_etc_start_date
2974: AND rownum = 1 )))
2975: AND EXISTS ( SELECT /*+ INDEX(tmp2,PA_FP_CALC_AMT_TMP2_N1)*/ 1
2976: FROM PA_FP_CALC_AMT_TMP2 tmp2
2977: WHERE tmp2.target_res_asg_id = tmp1.target_res_asg_id
2978: AND tmp2.transaction_source_code = 'ETC'
2979: AND rownum = 1 );
2980: ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
2994: ra.resource_assignment_id
2995: AND NVL(bl.quantity,0) <>
2996: NVL(bl.init_quantity,0)
2997: AND rownum = 1 )))
2998: AND EXISTS ( SELECT /*+ INDEX(tmp2,PA_FP_CALC_AMT_TMP2_N1)*/ 1
2999: FROM PA_FP_CALC_AMT_TMP2 tmp2
3000: WHERE tmp2.target_res_asg_id = tmp1.target_res_asg_id
3001: AND tmp2.transaction_source_code = 'ETC'
3002: AND rownum = 1 );
2995: AND NVL(bl.quantity,0) <>
2996: NVL(bl.init_quantity,0)
2997: AND rownum = 1 )))
2998: AND EXISTS ( SELECT /*+ INDEX(tmp2,PA_FP_CALC_AMT_TMP2_N1)*/ 1
2999: FROM PA_FP_CALC_AMT_TMP2 tmp2
3000: WHERE tmp2.target_res_asg_id = tmp1.target_res_asg_id
3001: AND tmp2.transaction_source_code = 'ETC'
3002: AND rownum = 1 );
3003: END IF; -- time phase check
3021: l_cal_rlm_id_tab.delete;
3022: l_cal_task_id_tab.delete;
3023: l_cal_etc_method_code_tab.delete;
3024:
3025: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP2;
3026: --hr_utility.trace('?????PA_FP_CALC_AMT_TMP2.count'||l_count_tmp);
3027:
3028: -- select count(*) into l_test from PA_FP_CALC_AMT_TMP2 where
3029: -- TRANSACTION_SOURCE_CODE = 'ETC';
3022: l_cal_task_id_tab.delete;
3023: l_cal_etc_method_code_tab.delete;
3024:
3025: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP2;
3026: --hr_utility.trace('?????PA_FP_CALC_AMT_TMP2.count'||l_count_tmp);
3027:
3028: -- select count(*) into l_test from PA_FP_CALC_AMT_TMP2 where
3029: -- TRANSACTION_SOURCE_CODE = 'ETC';
3030: -- hr_utility.trace('calc amt tmp2 count with src code as ETC:'||l_test);
3024:
3025: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP2;
3026: --hr_utility.trace('?????PA_FP_CALC_AMT_TMP2.count'||l_count_tmp);
3027:
3028: -- select count(*) into l_test from PA_FP_CALC_AMT_TMP2 where
3029: -- TRANSACTION_SOURCE_CODE = 'ETC';
3030: -- hr_utility.trace('calc amt tmp2 count with src code as ETC:'||l_test);
3031:
3032: IF ( l_fp_planning_options_flag = 'Y' AND
4869: * errors. See bug 5203622.
4870: *
4871: * Pre-Conditions:
4872: * 1. At this point, other_rejection_code values should be stored
4873: * in the txn_currency_code column of the pa_fp_calc_amt_tmp2
4874: * table for planning txns with ETC revenue calculation errors.
4875: *
4876: * Note: The etc_currency_code column (not txn_currency_code)
4877: * to store the currency for ETC records in pa_fp_calc_amt_tmp2.
4873: * in the txn_currency_code column of the pa_fp_calc_amt_tmp2
4874: * table for planning txns with ETC revenue calculation errors.
4875: *
4876: * Note: The etc_currency_code column (not txn_currency_code)
4877: * to store the currency for ETC records in pa_fp_calc_amt_tmp2.
4878: *
4879: * Also worth noting is that this procedure is package-private.
4880: */
4881: PROCEDURE UPD_REV_CALCULATION_ERR
4949: END IF;
4950:
4951: -- Update pa_budget_lines with any other_rejection_codes stored
4952: -- in the txn_currency_code column of the task level selection
4953: -- forecast generation processing table pa_fp_calc_amt_tmp2.
4954:
4955: UPDATE pa_budget_lines bl
4956: SET bl.other_rejection_code =
4957: ( SELECT tmp2.txn_currency_code
4954:
4955: UPDATE pa_budget_lines bl
4956: SET bl.other_rejection_code =
4957: ( SELECT tmp2.txn_currency_code
4958: FROM pa_fp_calc_amt_tmp2 tmp2
4959: WHERE tmp2.transaction_source_code = 'ETC'
4960: AND tmp2.txn_currency_code is not null
4961: AND bl.resource_assignment_id = tmp2.target_res_asg_id
4962: AND bl.txn_currency_code = tmp2.etc_currency_code )
4963: WHERE bl.budget_version_id = p_budget_version_id
4964: AND nvl(bl.quantity,0) <> nvl(bl.init_quantity,0) -- ETC lines only
4965: AND EXISTS
4966: ( SELECT null
4967: FROM pa_fp_calc_amt_tmp2 tmp2
4968: WHERE tmp2.transaction_source_code = 'ETC'
4969: AND tmp2.txn_currency_code is not null
4970: AND bl.resource_assignment_id = tmp2.target_res_asg_id
4971: AND bl.txn_currency_code = tmp2.etc_currency_code );