[Home] [Help]
139: p_module_name => l_module_name);
140: END IF;
141:
142: DELETE FROM PA_FP_CALC_AMT_TMP1;
143: DELETE FROM PA_FP_CALC_AMT_TMP2;
144: IF p_fp_cols_rec.x_gen_incl_open_comm_flag = 'Y' THEN
145: IF p_pa_debug_mode = 'Y' THEN
146: PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
147: P_CALLED_MODE => P_CALLED_MODE,
1363: * cursor picks up each task's generation source. */
1364:
1365: CURSOR etc_amts_cur_wp_fp_opt_same
1366: (c_gen_etc_source_code VARCHAR2 DEFAULT NULL) IS
1367: SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1368: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1369: tmp.RESOURCE_ASSIGNMENT_ID,
1370: tmp.TARGET_RES_ASG_ID,
1371: tmp.ETC_CURRENCY_CODE,
1382: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1383: nvl(c_gen_etc_source_code, /* Added for Bug 4369741 */
1384: nvl(tmp_ra.transaction_source_code, 'NONE'))
1385: FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
1386: PA_FP_CALC_AMT_TMP2 tmp,
1387: PA_RESOURCE_ASSIGNMENTS ra,
1388: pa_tasks ta /* Added for ER 4376722 */
1389: WHERE tmp.target_res_asg_id = ra.resource_assignment_id
1390: AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
1430: * cursor picks up each task's generation source. */
1431:
1432: CURSOR etc_amts_cur_wp_fp_opt_diff
1433: (c_gen_etc_source_code VARCHAR2 DEFAULT NULL) IS
1434: SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1435: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1436: sum(1*null),
1437: tmp.TARGET_RES_ASG_ID,
1438: tmp.ETC_CURRENCY_CODE,
1449: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1450: nvl(c_gen_etc_source_code, /* Added for Bug 4369741 */
1451: nvl(tmp_ra.transaction_source_code, 'NONE'))
1452: FROM PA_FP_CALC_AMT_TMP1 tmp_ra, /* Added for Bug 4369741 */
1453: PA_FP_CALC_AMT_TMP2 tmp,
1454: PA_RESOURCE_ASSIGNMENTS ra,
1455: pa_tasks ta /* Added for ER 4376722 */
1456: WHERE tmp.target_res_asg_id = ra.resource_assignment_id
1457: AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
1485: * ALL the etc_amts_cur_wp_fp_opt_diff cursor's SELECT statement for
1486: * resources with non-Workplan source. */
1487:
1488: CURSOR etc_amts_cur_wp_opt_same IS
1489: SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1490: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1491: tmp.RESOURCE_ASSIGNMENT_ID,
1492: tmp.TARGET_RES_ASG_ID,
1493: tmp.ETC_CURRENCY_CODE,
1503: SUM(tmp.ETC_TXN_REVENUE),
1504: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1505: tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1506: FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
1507: PA_FP_CALC_AMT_TMP2 tmp,
1508: PA_RESOURCE_ASSIGNMENTS ra,
1509: pa_tasks ta /* Added for ER 4376722 */
1510: WHERE tmp.target_res_asg_id = ra.resource_assignment_id
1511: AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
1527: tmp_ra.etc_method_code,
1528: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1529: tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1530: UNION ALL
1531: SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1532: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1533: sum(1*null),
1534: tmp.TARGET_RES_ASG_ID,
1535: tmp.ETC_CURRENCY_CODE,
1545: SUM(tmp.ETC_TXN_REVENUE),
1546: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1547: tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1548: FROM PA_FP_CALC_AMT_TMP1 tmp_ra, /* Added for Bug 4369741 */
1549: PA_FP_CALC_AMT_TMP2 tmp,
1550: PA_RESOURCE_ASSIGNMENTS ra,
1551: pa_tasks ta /* Added for ER 4376722 */
1552: WHERE tmp.target_res_asg_id = ra.resource_assignment_id
1553: AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
1581: * UNION ALL the etc_amts_cur_wp_fp_opt_diff cursor's SELECT statement for
1582: * resources with non Financial Plan source. */
1583:
1584: CURSOR etc_amts_cur_fp_opt_same IS
1585: SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1586: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1587: tmp.RESOURCE_ASSIGNMENT_ID,
1588: tmp.TARGET_RES_ASG_ID,
1589: tmp.ETC_CURRENCY_CODE,
1599: SUM(tmp.ETC_TXN_REVENUE),
1600: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1601: tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1602: FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
1603: PA_FP_CALC_AMT_TMP2 tmp,
1604: PA_RESOURCE_ASSIGNMENTS ra,
1605: pa_tasks ta /* Added for ER 4376722 */
1606: WHERE tmp.target_res_asg_id = ra.resource_assignment_id
1607: AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
1623: tmp_ra.etc_method_code,
1624: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1625: tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1626: UNION ALL
1627: SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP2_N1)
1628: INDEX(tmp_ra,PA_FP_CALC_AMT_TMP1_N1) */
1629: sum(1*null),
1630: tmp.TARGET_RES_ASG_ID,
1631: tmp.ETC_CURRENCY_CODE,
1641: SUM(tmp.ETC_TXN_REVENUE),
1642: NVL(ta.billable_flag,'Y'), /* Added for ER 4376722 */
1643: tmp_ra.transaction_source_code /* Added for Bug 4369741 */
1644: FROM PA_FP_CALC_AMT_TMP1 tmp_ra, /* Added for Bug 4369741 */
1645: PA_FP_CALC_AMT_TMP2 tmp,
1646: PA_RESOURCE_ASSIGNMENTS ra,
1647: pa_tasks ta /* Added for ER 4376722 */
1648: WHERE tmp.target_res_asg_id = ra.resource_assignment_id
1649: AND tmp.TRANSACTION_SOURCE_CODE = 'ETC'
2562: *baselined cost**/
2563:
2564: /* select count(*) into l_test from Pa_fp_CALC_AMT_TMP1;
2565: hr_utility.trace('fp calc amt tmp1 tab count '||l_test);
2566: select count(*) into l_test from Pa_fp_CALC_AMT_TMP2;
2567: hr_utility.trace('fp calc amt tmp2 tab count '||l_test); */
2568:
2569: DELETE FROM PA_FP_CALC_AMT_TMP3;
2570: /* the following code is commented. We are not going to use the
3158: *is not null, otherwise, we need to populate into pa_budget_lines by ourselves**/
3159:
3160: /* select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP1;
3161: hr_utility.trace('tmp1 count :'|| l_count_tmp);
3162: select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP2;
3163: hr_utility.trace('tmp2 count :'|| l_count_tmp); */
3164: --delete from calc_amt_tmp11;
3165: --insert into calc_amt_tmp11 select * from pa_fp_calc_amt_tmp1;
3166:
3165: --insert into calc_amt_tmp11 select * from pa_fp_calc_amt_tmp1;
3166:
3167: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP1;
3168: --hr_utility.trace('***PA_FP_CALC_AMT_TMP1.count'||l_count_tmp);
3169: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP2;
3170: --hr_utility.trace('**PA_FP_CALC_AMT_TMP2.count'||l_count_tmp);
3171: --hr_utility.trace('**P_FP_COLS_REC.X_TIME_PHASED_CODE:'||P_FP_COLS_REC.X_TIME_PHASED_CODE);
3172:
3173: --l_rev_gen_method := PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(p_project_id);
3166:
3167: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP1;
3168: --hr_utility.trace('***PA_FP_CALC_AMT_TMP1.count'||l_count_tmp);
3169: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP2;
3170: --hr_utility.trace('**PA_FP_CALC_AMT_TMP2.count'||l_count_tmp);
3171: --hr_utility.trace('**P_FP_COLS_REC.X_TIME_PHASED_CODE:'||P_FP_COLS_REC.X_TIME_PHASED_CODE);
3172:
3173: --l_rev_gen_method := PA_FP_GEN_FCST_PG_PKG.GET_REV_GEN_METHOD(p_project_id);
3174: 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
3175: IF l_fp_cols_rec_target.x_version_type = 'REVENUE' and l_rev_gen_method = 'C' THEN
3176: IF p_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'N' THEN
3177: SELECT DISTINCT target_res_asg_id
3178: BULK COLLECT INTO l_res_asg_uom_update_tab
3179: FROM PA_FP_CALC_AMT_TMP2
3180: WHERE transaction_source_code = 'ETC';
3181: ELSIF p_fp_cols_rec.X_GEN_RET_MANUAL_LINE_FLAG = 'Y' THEN
3182: IF p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
3183: l_etc_start_date :=
3198: WHERE bl.resource_assignment_id =
3199: ra.resource_assignment_id
3200: AND bl.start_date >= l_etc_start_date
3201: AND rownum = 1 )))
3202: AND EXISTS ( SELECT /*+ INDEX(tmp2,PA_FP_CALC_AMT_TMP2_N1)*/ 1
3203: FROM PA_FP_CALC_AMT_TMP2 tmp2
3204: WHERE tmp2.target_res_asg_id = tmp1.target_res_asg_id
3205: AND tmp2.transaction_source_code = 'ETC'
3206: AND rownum = 1 );
3199: ra.resource_assignment_id
3200: AND bl.start_date >= l_etc_start_date
3201: AND rownum = 1 )))
3202: AND EXISTS ( SELECT /*+ INDEX(tmp2,PA_FP_CALC_AMT_TMP2_N1)*/ 1
3203: FROM PA_FP_CALC_AMT_TMP2 tmp2
3204: WHERE tmp2.target_res_asg_id = tmp1.target_res_asg_id
3205: AND tmp2.transaction_source_code = 'ETC'
3206: AND rownum = 1 );
3207: ELSIF p_fp_cols_rec.x_time_phased_code = 'N' THEN
3221: ra.resource_assignment_id
3222: AND NVL(bl.quantity,0) <>
3223: NVL(bl.init_quantity,0)
3224: AND rownum = 1 )))
3225: AND EXISTS ( SELECT /*+ INDEX(tmp2,PA_FP_CALC_AMT_TMP2_N1)*/ 1
3226: FROM PA_FP_CALC_AMT_TMP2 tmp2
3227: WHERE tmp2.target_res_asg_id = tmp1.target_res_asg_id
3228: AND tmp2.transaction_source_code = 'ETC'
3229: AND rownum = 1 );
3222: AND NVL(bl.quantity,0) <>
3223: NVL(bl.init_quantity,0)
3224: AND rownum = 1 )))
3225: AND EXISTS ( SELECT /*+ INDEX(tmp2,PA_FP_CALC_AMT_TMP2_N1)*/ 1
3226: FROM PA_FP_CALC_AMT_TMP2 tmp2
3227: WHERE tmp2.target_res_asg_id = tmp1.target_res_asg_id
3228: AND tmp2.transaction_source_code = 'ETC'
3229: AND rownum = 1 );
3230: END IF; -- time phase check
3249: l_cal_cbs_id_tab.delete; --bug#16791711
3250: l_cal_task_id_tab.delete;
3251: l_cal_etc_method_code_tab.delete;
3252:
3253: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP2;
3254: --hr_utility.trace('?????PA_FP_CALC_AMT_TMP2.count'||l_count_tmp);
3255:
3256: -- select count(*) into l_test from PA_FP_CALC_AMT_TMP2 where
3257: -- TRANSACTION_SOURCE_CODE = 'ETC';
3250: l_cal_task_id_tab.delete;
3251: l_cal_etc_method_code_tab.delete;
3252:
3253: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP2;
3254: --hr_utility.trace('?????PA_FP_CALC_AMT_TMP2.count'||l_count_tmp);
3255:
3256: -- select count(*) into l_test from PA_FP_CALC_AMT_TMP2 where
3257: -- TRANSACTION_SOURCE_CODE = 'ETC';
3258: -- hr_utility.trace('calc amt tmp2 count with src code as ETC:'||l_test);
3252:
3253: --select count(*) into l_count_tmp from PA_FP_CALC_AMT_TMP2;
3254: --hr_utility.trace('?????PA_FP_CALC_AMT_TMP2.count'||l_count_tmp);
3255:
3256: -- select count(*) into l_test from PA_FP_CALC_AMT_TMP2 where
3257: -- TRANSACTION_SOURCE_CODE = 'ETC';
3258: -- hr_utility.trace('calc amt tmp2 count with src code as ETC:'||l_test);
3259:
3260: IF ( l_fp_planning_options_flag = 'Y' AND
5131: * errors. See bug 5203622.
5132: *
5133: * Pre-Conditions:
5134: * 1. At this point, other_rejection_code values should be stored
5135: * in the txn_currency_code column of the pa_fp_calc_amt_tmp2
5136: * table for planning txns with ETC revenue calculation errors.
5137: *
5138: * Note: The etc_currency_code column (not txn_currency_code)
5139: * to store the currency for ETC records in pa_fp_calc_amt_tmp2.
5135: * in the txn_currency_code column of the pa_fp_calc_amt_tmp2
5136: * table for planning txns with ETC revenue calculation errors.
5137: *
5138: * Note: The etc_currency_code column (not txn_currency_code)
5139: * to store the currency for ETC records in pa_fp_calc_amt_tmp2.
5140: *
5141: * Also worth noting is that this procedure is package-private.
5142: */
5143: PROCEDURE UPD_REV_CALCULATION_ERR
5211: END IF;
5212:
5213: -- Update pa_budget_lines with any other_rejection_codes stored
5214: -- in the txn_currency_code column of the task level selection
5215: -- forecast generation processing table pa_fp_calc_amt_tmp2.
5216:
5217: UPDATE pa_budget_lines bl
5218: SET bl.other_rejection_code =
5219: ( SELECT tmp2.txn_currency_code
5216:
5217: UPDATE pa_budget_lines bl
5218: SET bl.other_rejection_code =
5219: ( SELECT tmp2.txn_currency_code
5220: FROM pa_fp_calc_amt_tmp2 tmp2
5221: WHERE tmp2.transaction_source_code = 'ETC'
5222: AND tmp2.txn_currency_code is not null
5223: AND bl.resource_assignment_id = tmp2.target_res_asg_id
5224: AND bl.txn_currency_code = tmp2.etc_currency_code )
5225: WHERE bl.budget_version_id = p_budget_version_id
5226: AND nvl(bl.quantity,0) <> nvl(bl.init_quantity,0) -- ETC lines only
5227: AND EXISTS
5228: ( SELECT null
5229: FROM pa_fp_calc_amt_tmp2 tmp2
5230: WHERE tmp2.transaction_source_code = 'ETC'
5231: AND tmp2.txn_currency_code is not null
5232: AND bl.resource_assignment_id = tmp2.target_res_asg_id
5233: AND bl.txn_currency_code = tmp2.etc_currency_code );