[Home] [Help]
4: P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5:
6: /* Assumption:
7: *1.Before getting into this procedure, we have all total plan amounts and commitment
8: amounts populated in temporary table PA_FP_CALC_AMT_TMP2 table with transaction
9: source codes of 'WORKPLAN'/'FINPLAN' or 'OPEN_COMMITMENTS'.
10: 2.Rate based flag for target resource assignment gets updated correctly before coming
11: into any of ETC methods.
12: 3.All considered scenarios:
206: AND data_type_code = DECODE(P_ETC_SOURCE_CODE,
207: 'WORKPLAN_RESOURCES', 'ETC_WP',
208: 'FINANCIAL_PLAN', 'ETC_FP')
209: MINUS
210: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
211: DISTINCT txn_currency_code
212: FROM PA_FP_CALC_AMT_TMP2
213: WHERE target_res_asg_id = p_tgt_res_asg_id
214: AND transaction_source_code = p_etc_source_code
208: 'FINANCIAL_PLAN', 'ETC_FP')
209: MINUS
210: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
211: DISTINCT txn_currency_code
212: FROM PA_FP_CALC_AMT_TMP2
213: WHERE target_res_asg_id = p_tgt_res_asg_id
214: AND transaction_source_code = p_etc_source_code
215: ) WHERE rownum = 1;
216:
226: l_pfc_currency_code := p_fp_cols_tgt_rec.x_projfunc_currency_code;
227: IF l_currency_flag = 'PC' OR l_currency_flag = 'PFC' THEN
228: /* No matter ETC source is 'FINANCIAL_PLAN' or 'WORKPLAN_RESOURCES', always get
229: total plan amounts in PC or PFC from financial data model.*/
230: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
231: NVL(SUM(NVL(total_plan_quantity,0)),0),
232: NVL(SUM(NVL(
233: DECODE(l_currency_flag, 'PC', total_pc_raw_cost,
234: 'PFC', total_pfc_raw_cost),0)),0),
241: INTO l_tot_quantity_pc_pfc,
242: l_tot_raw_cost_pc_pfc,
243: l_tot_brdn_cost_pc_pfc,
244: l_tot_revenue_pc_pfc
245: FROM PA_FP_CALC_AMT_TMP2
246: WHERE resource_assignment_id = p_src_res_asg_id
247: AND transaction_source_code = p_etc_source_code;
248:
249: IF l_rate_based_flag = 'N' THEN
334:
335: /*When not taking periodic rates, we need to calculate out the average
336: rates from the source resource assignments that are mapped to the current
337: target resource assignmentInsert the single PC record for total ETC.*/
338: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
339: NVL(SUM(NVL(total_plan_quantity,0)),0),
340: DECODE(l_currency_flag,
341: 'PC', NVL(SUM(NVL(total_pc_raw_cost,0)),0),
342: 'PFC', NVL(SUM(NVL(total_pfc_raw_cost,0)),0)),
349: INTO l_pc_pfc_rate_quantity,
350: l_pc_pfc_rate_raw_cost,
351: l_pc_pfc_rate_brdn_cost,
352: l_pc_pfc_rate_revenue
353: FROM pa_fp_calc_amt_tmp2
354: WHERE resource_assignment_id = p_src_res_asg_id
355: AND transaction_source_code in ('FINANCIAL_PLAN',
356: 'WORKPLAN_RESOURCES');
357:
378: ELSE
379: l_transaction_source_code := 'ETC';
380: END IF;
381:
382: INSERT INTO PA_FP_CALC_AMT_TMP2 (
383: RESOURCE_ASSIGNMENT_ID,
384: TARGET_RES_ASG_ID,
385: ETC_CURRENCY_CODE,
386: ETC_PLAN_QUANTITY,
427:
428: ELSIF l_currency_flag = 'TC' THEN
429: /* No matter ETC source is 'FINANCIAL_PLAN' or 'WORKPLAN_RESOURCES', always
430: get total plan amounts by txn currency from financial data model.*/
431: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
432: txn_currency_code,
433: SUM(NVL(total_plan_quantity,0)),
434: SUM(NVL(total_txn_raw_cost,0)),
435: SUM(NVL(total_txn_burdened_cost,0)),
439: l_tot_quantity_tab,
440: l_tot_raw_cost_tab,
441: l_tot_brdn_cost_tab,
442: l_tot_revenue_tab
443: FROM PA_FP_CALC_AMT_TMP2
444: WHERE resource_assignment_id = p_src_res_asg_id
445: AND transaction_source_code = p_etc_source_code
446: GROUP BY txn_currency_code;
447:
573: AND data_type_code = DECODE(P_ETC_SOURCE_CODE,
574: 'WORKPLAN_RESOURCES', 'ETC_WP',
575: 'FINANCIAL_PLAN', 'ETC_FP')
576: MINUS
577: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
578: DISTINCT txn_currency_code
579: FROM PA_FP_CALC_AMT_TMP2
580: WHERE resource_assignment_id = p_src_res_asg_id
581: AND transaction_source_code = p_etc_source_code
575: 'FINANCIAL_PLAN', 'ETC_FP')
576: MINUS
577: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
578: DISTINCT txn_currency_code
579: FROM PA_FP_CALC_AMT_TMP2
580: WHERE resource_assignment_id = p_src_res_asg_id
581: AND transaction_source_code = p_etc_source_code
582: ) WHERE rownum = 1;
583:
663: /*When not taking periodic rates, we need to calculate out the average
664: rates from the source resource assignments that are mapped to the current
665: target resource assignment.*/
666: FOR i IN 1..l_tot_currency_code_tab.count LOOP
667: SELECT /*+ INDEX(pa_fp_calc_amt_tmp2,PA_FP_CALC_AMT_TMP2_N2)*/
668: NVL(SUM(NVL(total_plan_quantity,0)),0),
669: NVL(SUM(NVL(total_txn_raw_cost,0)),0),
670: NVL(SUM(NVL(total_txn_burdened_cost,0)),0),
671: NVL(SUM(NVL(total_txn_revenue,0)),0),
678: l_txn_rate_revenue,
679: l_pc_rate_raw_cost,
680: l_pc_rate_brdn_cost,
681: l_pc_rate_revenue
682: FROM pa_fp_calc_amt_tmp2
683: WHERE resource_assignment_id = p_src_res_asg_id
684: AND txn_currency_code = l_tot_currency_code_tab(i)
685: AND transaction_source_code in ('FINANCIAL_PLAN',
686: 'WORKPLAN_RESOURCES');
721: ELSE
722: l_transaction_source_code := 'ETC';
723: END IF;
724: FORALL i IN 1..l_etc_quantity_tab.count
725: INSERT INTO PA_FP_CALC_AMT_TMP2 (
726: RESOURCE_ASSIGNMENT_ID,
727: TARGET_RES_ASG_ID,
728: ETC_CURRENCY_CODE,
729: ETC_PLAN_QUANTITY,
755: This only happens for non rate based resources*/
756:
757: /*No matter ETC source is 'FINANCIAL_PLAN' or 'WORKPLAN_RESOURCES',
758: always get total plan amounts in PC from financial data model*/
759: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
760: txn_currency_code,
761: SUM(NVL(total_plan_quantity,0)),
762: SUM(NVL(total_pc_raw_cost,0)),
763: SUM(NVL(total_pc_burdened_cost,0)),
767: l_tot_quantity_pc_tab,
768: l_tot_raw_cost_pc_tab,
769: l_tot_brdn_cost_pc_tab,
770: l_tot_revenue_pc_tab
771: FROM PA_FP_CALC_AMT_TMP2
772: WHERE resource_assignment_id = p_src_res_asg_id
773: AND transaction_source_code = p_etc_source_code
774: GROUP BY txn_currency_code;
775:
958: from the source resource assignments that are mapped to the current target
959: resource assignment.*/
960:
961: FOR i IN 1..l_tot_currency_code_tab.count LOOP
962: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
963: NVL(SUM(NVL(total_plan_quantity,0)),0),
964: NVL(SUM(NVL(total_txn_raw_cost,0)),0),
965: NVL(SUM(NVL(total_txn_burdened_cost,0)),0),
966: NVL(SUM(NVL(total_txn_revenue,0)),0),
973: l_txn_rate_revenue,
974: l_pc_rate_raw_cost,
975: l_pc_rate_brdn_cost,
976: l_pc_rate_revenue
977: FROM pa_fp_calc_amt_tmp2
978: WHERE resource_assignment_id = p_src_res_asg_id
979: AND txn_currency_code = l_tot_currency_code_tab(i)
980: AND transaction_source_code in ('FINANCIAL_PLAN' ,
981: 'WORKPLAN_RESOURCES');
1013: l_transaction_source_code := 'ETC';
1014: END IF;
1015:
1016: FORALL i IN 1..l_etc_quantity_tab.count
1017: INSERT INTO PA_FP_CALC_AMT_TMP2 (
1018: RESOURCE_ASSIGNMENT_ID,
1019: TARGET_RES_ASG_ID,
1020: ETC_CURRENCY_CODE,
1021: ETC_PLAN_QUANTITY,
1125: PA_DEBUG.RESET_CURR_FUNCTION;
1126: END IF;
1127:
1128: SELECT COUNT(*) INTO l_currency_count_for_flag FROM (
1129: SELECT /*+ INDEX(tot_tmp,PA_FP_CALC_AMT_TMP2_N1)*/
1130: DISTINCT txn_currency_code
1131: FROM PA_FP_CALC_AMT_TMP2
1132: WHERE target_res_asg_id = p_tgt_res_asg_id
1133: AND (transaction_source_code = 'FINANCIAL_PLAN'
1127:
1128: SELECT COUNT(*) INTO l_currency_count_for_flag FROM (
1129: SELECT /*+ INDEX(tot_tmp,PA_FP_CALC_AMT_TMP2_N1)*/
1130: DISTINCT txn_currency_code
1131: FROM PA_FP_CALC_AMT_TMP2
1132: WHERE target_res_asg_id = p_tgt_res_asg_id
1133: AND (transaction_source_code = 'FINANCIAL_PLAN'
1134: OR transaction_source_code = 'WORKPLAN_RESOURCES'
1135: OR transaction_source_code = 'COMMITMENT')
1133: AND (transaction_source_code = 'FINANCIAL_PLAN'
1134: OR transaction_source_code = 'WORKPLAN_RESOURCES'
1135: OR transaction_source_code = 'COMMITMENT')
1136: UNION
1137: SELECT /*+ INDEX(tot_tmp,PA_FP_CALC_AMT_TMP2_N1)*/
1138: DISTINCT act_tmp.txn_currency_code
1139: FROM PA_FP_FCST_GEN_TMP1 act_tmp,
1140: PA_FP_CALC_AMT_TMP2 tot_tmp
1141: WHERE act_tmp.source_id = tot_tmp.resource_assignment_id
1136: UNION
1137: SELECT /*+ INDEX(tot_tmp,PA_FP_CALC_AMT_TMP2_N1)*/
1138: DISTINCT act_tmp.txn_currency_code
1139: FROM PA_FP_FCST_GEN_TMP1 act_tmp,
1140: PA_FP_CALC_AMT_TMP2 tot_tmp
1141: WHERE act_tmp.source_id = tot_tmp.resource_assignment_id
1142: AND tot_tmp.target_res_asg_id = p_tgt_res_asg_id
1143: ) WHERE rownum <= 2;
1144:
1202:
1203:
1204: /* Assumption:
1205: 1.Before getting into this procedure, we have called all ETC methods to derive the total
1206: ETC quantity and populated them in the temporary table PA_FP_CALC_AMT_TMP2 with
1207: transaction source codes of 'TOTAL_ETC'.
1208: 2.Commitment can only be considered for cost/all version. For revenue forecast version,
1209: user can't select include commitment option from the UI.
1210: 3.No matter for cost, revenue or all forecast version, always pick up cost/revenue rate
1338:
1339: CURSOR src_tgt_cur_wp_fp_opt_same IS
1340: SELECT DISTINCT target_res_asg_id,
1341: resource_assignment_id
1342: FROM PA_FP_CALC_AMT_TMP2
1343: WHERE TRANSACTION_SOURCE_CODE = 'TOTAL_ETC';
1344:
1345: /* Bug 4369741: Added cursor src_tgt_cur_wp_fp_opt_diff to be used in
1346: * the following scenarios:
1354:
1355: CURSOR src_tgt_cur_wp_fp_opt_diff IS
1356: SELECT DISTINCT target_res_asg_id,
1357: NULL
1358: FROM PA_FP_CALC_AMT_TMP2
1359: WHERE TRANSACTION_SOURCE_CODE = 'TOTAL_ETC';
1360:
1361: /* Bug 4369741: Added cursor src_tgt_cur_wp_opt_same to be used in
1362: * the following scenarios:
1368: SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP1_N1)*/
1369: DISTINCT tmp.target_res_asg_id tgt_res_asg_id,
1370: tmp.resource_assignment_id src_res_asg_id
1371: FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
1372: PA_FP_CALC_AMT_TMP2 tmp
1373: WHERE tmp.TRANSACTION_SOURCE_CODE = 'TOTAL_ETC'
1374: AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id
1375: AND tmp_ra.transaction_source_code = 'WORKPLAN_RESOURCES'
1376: UNION ALL
1377: SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP1_N1)*/
1378: DISTINCT tmp.target_res_asg_id tgt_res_asg_id,
1379: NULL src_res_asg_id
1380: FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
1381: PA_FP_CALC_AMT_TMP2 tmp
1382: WHERE tmp.TRANSACTION_SOURCE_CODE = 'TOTAL_ETC'
1383: AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id
1384: AND tmp_ra.transaction_source_code = 'FINANCIAL_PLAN';
1385:
1393: SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP1_N1)*/
1394: DISTINCT tmp.target_res_asg_id tgt_res_asg_id,
1395: tmp.resource_assignment_id src_res_asg_id
1396: FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
1397: PA_FP_CALC_AMT_TMP2 tmp
1398: WHERE tmp.TRANSACTION_SOURCE_CODE = 'TOTAL_ETC'
1399: AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id
1400: AND tmp_ra.transaction_source_code = 'FINANCIAL_PLAN'
1401: UNION ALL
1402: SELECT /*+ INDEX(tmp,PA_FP_CALC_AMT_TMP1_N1)*/
1403: DISTINCT tmp.target_res_asg_id tgt_res_asg_id,
1404: NULL src_res_asg_id
1405: FROM PA_FP_CALC_AMT_TMP1 tmp_ra,
1406: PA_FP_CALC_AMT_TMP2 tmp
1407: WHERE tmp.TRANSACTION_SOURCE_CODE = 'TOTAL_ETC'
1408: AND tmp_ra.target_res_asg_id = tmp.target_res_asg_id
1409: AND tmp_ra.transaction_source_code = 'WORKPLAN_RESOURCES';
1410:
1530: -- pl/sql EXIT command to skip to the next iteration of the main loop to avoid
1531: -- further processing. This was done to replace RETURN with EXIT.
1532: FOR wrapper_loop_iterator IN 1..1 LOOP
1533:
1534: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/ COUNT(*)
1535: INTO l_cmt_count
1536: FROM PA_FP_CALC_AMT_TMP2
1537: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1538: AND transaction_source_code = 'OPEN_COMMITMENTS'
1532: FOR wrapper_loop_iterator IN 1..1 LOOP
1533:
1534: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/ COUNT(*)
1535: INTO l_cmt_count
1536: FROM PA_FP_CALC_AMT_TMP2
1537: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1538: AND transaction_source_code = 'OPEN_COMMITMENTS'
1539: AND rownum = 1;
1540:
1540:
1541: /* If no commitment available for the current target resource assignment,
1542: simply update the temp table from total_etc records to net etc records. */
1543: IF l_cmt_count = 0 THEN
1544: UPDATE /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/ PA_FP_CALC_AMT_TMP2
1545: SET transaction_source_code = 'ETC'
1546: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1547: AND transaction_source_code = 'TOTAL_ETC';
1548: ELSE
1601: l_currency_flag := 'PFC';
1602: ELSIF p_fp_cols_tgt_rec.X_PLAN_IN_MULTI_CURR_FLAG = 'N' THEN
1603: l_currency_flag := 'PC';
1604: ELSIF l_rate_based_flag = 'N' THEN
1605: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1606: COUNT(*) INTO l_currency_count_for_flag FROM (
1607: SELECT DISTINCT txn_currency_code
1608: FROM PA_FP_CALC_AMT_TMP2
1609: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1604: ELSIF l_rate_based_flag = 'N' THEN
1605: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1606: COUNT(*) INTO l_currency_count_for_flag FROM (
1607: SELECT DISTINCT txn_currency_code
1608: FROM PA_FP_CALC_AMT_TMP2
1609: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1610: AND transaction_source_code = 'OPEN_COMMITMENTS'
1611: MINUS
1612: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1608: FROM PA_FP_CALC_AMT_TMP2
1609: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1610: AND transaction_source_code = 'OPEN_COMMITMENTS'
1611: MINUS
1612: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1613: DISTINCT etc_currency_code
1614: FROM PA_FP_CALC_AMT_TMP2
1615: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1616: AND transaction_source_code = 'TOTAL_ETC'
1610: AND transaction_source_code = 'OPEN_COMMITMENTS'
1611: MINUS
1612: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1613: DISTINCT etc_currency_code
1614: FROM PA_FP_CALC_AMT_TMP2
1615: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1616: AND transaction_source_code = 'TOTAL_ETC'
1617: ) WHERE rownum = 1;
1618: IF l_currency_count_for_flag > 0 THEN
1623: /***********BY THIS TIME, WE DECIDED TO USE EITHER PC, TC or PC_PC*********/
1624:
1625: IF l_currency_flag = 'PC' or l_currency_flag = 'PFC' THEN
1626: /* Get total etc amounts in PC for each target resource assignment */
1627: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1628: NVL(SUM(ETC_PLAN_QUANTITY),0)
1629: INTO l_etc_quantity_pc_pfc
1630: FROM PA_FP_CALC_AMT_TMP2
1631: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1626: /* Get total etc amounts in PC for each target resource assignment */
1627: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1628: NVL(SUM(ETC_PLAN_QUANTITY),0)
1629: INTO l_etc_quantity_pc_pfc
1630: FROM PA_FP_CALC_AMT_TMP2
1631: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1632: AND TRANSACTION_SOURCE_CODE = 'TOTAL_ETC';
1633:
1634: /* Get commitment amounts in PC for currency target resource assignment */
1631: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1632: AND TRANSACTION_SOURCE_CODE = 'TOTAL_ETC';
1633:
1634: /* Get commitment amounts in PC for currency target resource assignment */
1635: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1636: NVL(SUM(NVL(total_plan_quantity,0)),0),
1637: DECODE(l_currency_flag,
1638: 'PC', NVL(SUM(NVL(total_pc_raw_cost,0)),0),
1639: 'PFC', NVL(SUM(NVL(total_pfc_raw_cost,0)),0)),
1642: 'PFC', NVL(SUM(NVL(total_pfc_burdened_cost,0)),0))
1643: INTO l_cmt_quantity_pc_pfc,
1644: l_cmt_raw_cost_pc_pfc,
1645: l_cmt_brdn_cost_pc_pfc
1646: FROM PA_FP_CALC_AMT_TMP2
1647: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1648: AND transaction_source_code = 'OPEN_COMMITMENTS';
1649:
1650:
1670:
1671: /*When not taking periodic rates, we need to calculate out the average
1672: rates from the source resource assignments that are mapped to the
1673: current target resource assignment.*/
1674: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1675: NVL(SUM(NVL(etc_plan_quantity,0)),0),
1676: NVL(SUM(DECODE(l_currency_flag,
1677: 'PC', NVL(etc_pc_raw_cost,0),
1678: 'PFC', NVL(etc_pfc_raw_cost,0))),0),
1685: INTO l_pc_pfc_rate_quantity,
1686: l_pc_pfc_rate_raw_cost,
1687: l_pc_pfc_rate_brdn_cost,
1688: l_pc_pfc_rate_revenue
1689: FROM pa_fp_calc_amt_tmp2
1690: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1691: AND transaction_source_code = 'TOTAL_ETC';
1692:
1693: -- IPM Change:
1727: l_pc_pfc_revenue_rate := NULL;
1728: END IF;
1729:
1730: -- Bug 5203622: Store OTHER rejection code in the
1731: -- txn_currency_code column of pa_fp_calc_amt_tmp2.
1732: /* Insert the single PC record for total ETC with source rates */
1733: INSERT INTO PA_FP_CALC_AMT_TMP2 (
1734: TARGET_RES_ASG_ID,
1735: ETC_CURRENCY_CODE,
1729:
1730: -- Bug 5203622: Store OTHER rejection code in the
1731: -- txn_currency_code column of pa_fp_calc_amt_tmp2.
1732: /* Insert the single PC record for total ETC with source rates */
1733: INSERT INTO PA_FP_CALC_AMT_TMP2 (
1734: TARGET_RES_ASG_ID,
1735: ETC_CURRENCY_CODE,
1736: ETC_PLAN_QUANTITY,
1737: ETC_TXN_RAW_COST,
1755: /**************BY THIS TIME, WE HAVE ALL ETC DATA FOR PC or PFC*********/
1756:
1757: ELSIF l_currency_flag = 'TC' THEN
1758: /* Get total etc amounts for multiple currencies */
1759: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1760: etc_currency_code,
1761: SUM(NVL(ETC_PLAN_QUANTITY,0))
1762: BULK COLLECT INTO
1763: l_etc_currency_code_tab,
1761: SUM(NVL(ETC_PLAN_QUANTITY,0))
1762: BULK COLLECT INTO
1763: l_etc_currency_code_tab,
1764: l_etc_quantity_tab
1765: FROM PA_FP_CALC_AMT_TMP2
1766: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1767: AND TRANSACTION_SOURCE_CODE = 'TOTAL_ETC'
1768: GROUP BY etc_currency_code;
1769:
1779: l_etc_quantity_sum := l_etc_quantity_sum + l_etc_quantity_tab(k);
1780: END LOOP;
1781:
1782: /* Get commitment amounts for multiple currencies */
1783: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1784: txn_currency_code,
1785: SUM(NVL(total_plan_quantity,0)),
1786: SUM(NVL(total_txn_raw_cost,0)),
1787: SUM(NVL(total_txn_burdened_cost,0))
1789: l_cmt_currency_code_tab,
1790: l_cmt_quantity_tab,
1791: l_cmt_raw_cost_tab,
1792: l_cmt_brdn_cost_tab
1793: FROM PA_FP_CALC_AMT_TMP2
1794: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1795: AND transaction_source_code = 'OPEN_COMMITMENTS'
1796: GROUP BY txn_currency_code;
1797:
1817: 'C' means take currency based calculation
1818: 'P' means take prorating based calculation */
1819: SELECT COUNT (*) INTO l_currency_count_cmt_min_tot
1820: FROM (
1821: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1822: DISTINCT txn_currency_code
1823: FROM PA_FP_CALC_AMT_TMP2
1824: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1825: AND transaction_source_code = 'OPEN_COMMITMENTS'
1819: SELECT COUNT (*) INTO l_currency_count_cmt_min_tot
1820: FROM (
1821: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1822: DISTINCT txn_currency_code
1823: FROM PA_FP_CALC_AMT_TMP2
1824: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1825: AND transaction_source_code = 'OPEN_COMMITMENTS'
1826: MINUS
1827: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1823: FROM PA_FP_CALC_AMT_TMP2
1824: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1825: AND transaction_source_code = 'OPEN_COMMITMENTS'
1826: MINUS
1827: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1828: DISTINCT etc_currency_code
1829: FROM PA_FP_CALC_AMT_TMP2
1830: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1831: AND transaction_source_code = 'TOTAL_ETC'
1825: AND transaction_source_code = 'OPEN_COMMITMENTS'
1826: MINUS
1827: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1828: DISTINCT etc_currency_code
1829: FROM PA_FP_CALC_AMT_TMP2
1830: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1831: AND transaction_source_code = 'TOTAL_ETC'
1832: );
1833:
1916: /*When not taking periodic rates, we need to calculate out the average rates
1917: from the source resource assignments that are mapped to the current target
1918: resource assignment.*/
1919: FOR k IN 1..l_etc_currency_code_tab.count LOOP
1920: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
1921: NVL(SUM(NVL(etc_plan_quantity,0)),0),
1922: NVL(SUM(NVL(etc_txn_raw_cost,0)),0),
1923: NVL(SUM(NVL(etc_txn_burdened_cost,0)),0),
1924: NVL(SUM(NVL(etc_txn_revenue,0)),0)
1925: INTO l_txn_rate_quantity,
1926: l_txn_rate_raw_cost,
1927: l_txn_rate_brdn_cost,
1928: l_txn_rate_revenue
1929: FROM pa_fp_calc_amt_tmp2
1930: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
1931: AND etc_currency_code = l_etc_currency_code_tab(k)
1932: AND transaction_source_code = 'TOTAL_ETC';
1933:
1973: END IF;
1974: END LOOP;
1975:
1976: -- Bug 5203622: Store OTHER rejection code in the
1977: -- txn_currency_code column of pa_fp_calc_amt_tmp2.
1978: /* Bulk insert for the ETC amounts for current target resource
1979: assignment with source rates */
1980: FORALL k IN 1..l_etc_currency_code_tab.count
1981: INSERT INTO PA_FP_CALC_AMT_TMP2 (
1977: -- txn_currency_code column of pa_fp_calc_amt_tmp2.
1978: /* Bulk insert for the ETC amounts for current target resource
1979: assignment with source rates */
1980: FORALL k IN 1..l_etc_currency_code_tab.count
1981: INSERT INTO PA_FP_CALC_AMT_TMP2 (
1982: TARGET_RES_ASG_ID,
1983: ETC_CURRENCY_CODE,
1984: ETC_PLAN_QUANTITY,
1985: ETC_TXN_RAW_COST,
2007: /*Take PC for calculation, then convert back to TC */
2008: /*No matter ETC source is 'FINANCIAL_PLAN' or 'WORKPLAN_RESOURCES',
2009: always get total plan amounts in PC from financial data model.*/
2010: l_pc_currency_code := p_fp_cols_tgt_rec.x_project_currency_code;
2011: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
2012: etc_currency_code,
2013: SUM(NVL(etc_plan_quantity,0)),
2014: SUM(NVL(etc_pc_raw_cost,0)),
2015: SUM(NVL(etc_pc_burdened_cost,0)),
2019: l_etc_quantity_pc_tab,
2020: l_etc_raw_cost_pc_tab,
2021: l_etc_brdn_cost_pc_tab,
2022: l_etc_revenue_pc_tab -- Added in IPM
2023: FROM PA_FP_CALC_AMT_TMP2
2024: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
2025: AND transaction_source_code = 'TOTAL_ETC'
2026: GROUP BY etc_currency_code;
2027:
2048: l_etc_quantity_pc_sum := l_etc_quantity_pc_sum + l_etc_quantity_pc_tab(k);
2049: END LOOP;
2050:
2051: /*Get the commitment amounts for the target planning resource in PC.*/
2052: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
2053: txn_currency_code,
2054: SUM(NVL(total_plan_quantity,0)),
2055: SUM(NVL(total_pc_raw_cost,0)),
2056: SUM(NVL(total_pc_burdened_cost,0))
2058: l_cmt_currency_code_tab,
2059: l_cmt_quantity_pc_tab,
2060: l_cmt_raw_cost_pc_tab,
2061: l_cmt_brdn_cost_pc_tab
2062: FROM PA_FP_CALC_AMT_TMP2
2063: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
2064: AND transaction_source_code = 'OPEN_COMMITMENTS'
2065: GROUP BY txn_currency_code;
2066:
2167: /*When not taking periodic rates, we need to calculate out the average
2168: rates from the source resource assignments that are mapped to the
2169: current target resource assignment.*/
2170: FOR k IN 1..l_etc_noncmt_quantity_tab.count LOOP
2171: SELECT /*+ INDEX(pa_fp_calc_amt_tmp2,PA_FP_CALC_AMT_TMP2_N1)*/
2172: NVL(SUM(NVL(etc_plan_quantity,0)),0),
2173: NVL(SUM(NVL(etc_txn_raw_cost,0)),0),
2174: NVL(SUM(NVL(etc_txn_burdened_cost,0)),0),
2175: NVL(SUM(NVL(etc_txn_revenue,0)),0)
2176: INTO l_txn_rate_quantity,
2177: l_txn_rate_raw_cost,
2178: l_txn_rate_brdn_cost,
2179: l_txn_rate_revenue
2180: FROM pa_fp_calc_amt_tmp2
2181: WHERE target_res_asg_id = l_tgt_res_asg_id_tab(i)
2182: AND etc_currency_code = l_etc_currency_code_tab(k)
2183: AND transaction_source_code = 'TOTAL_ETC';
2184:
2222: END IF;
2223: END LOOP;
2224:
2225: -- Bug 5203622: Store OTHER rejection code in the
2226: -- txn_currency_code column of pa_fp_calc_amt_tmp2.
2227: /* Bulk insert */
2228: FORALL k IN 1..l_etc_noncmt_quantity_tab.count
2229: INSERT INTO PA_FP_CALC_AMT_TMP2 (
2230: TARGET_RES_ASG_ID,
2225: -- Bug 5203622: Store OTHER rejection code in the
2226: -- txn_currency_code column of pa_fp_calc_amt_tmp2.
2227: /* Bulk insert */
2228: FORALL k IN 1..l_etc_noncmt_quantity_tab.count
2229: INSERT INTO PA_FP_CALC_AMT_TMP2 (
2230: TARGET_RES_ASG_ID,
2231: ETC_CURRENCY_CODE,
2232: ETC_PLAN_QUANTITY,
2233: ETC_TXN_RAW_COST,
2308:
2309:
2310: /* Assumption:
2311: *1.Before getting into this procedure, we have all total plan amounts and commitment
2312: amounts populated in temporary table PA_FP_CALC_AMT_TMP2 table with transaction
2313: source codes of 'WORKPLAN'/'FINPLAN' or 'OPEN_COMMITMENTS'.
2314: 2.Rate based flag for target resource assignment gets updated correctly before coming
2315: into any of ETC methods.
2316: 3.All considered scenarios:
2595: AND data_type_code = DECODE(L_ETC_SOURCE_CODE,
2596: 'WORKPLAN_RESOURCES', 'ETC_WP',
2597: 'FINANCIAL_PLAN', 'ETC_FP')
2598: MINUS
2599: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
2600: DISTINCT txn_currency_code
2601: FROM PA_FP_CALC_AMT_TMP2
2602: WHERE target_res_asg_id = l_tgt_res_asg_id
2603: AND transaction_source_code = l_etc_source_code
2597: 'FINANCIAL_PLAN', 'ETC_FP')
2598: MINUS
2599: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N1)*/
2600: DISTINCT txn_currency_code
2601: FROM PA_FP_CALC_AMT_TMP2
2602: WHERE target_res_asg_id = l_tgt_res_asg_id
2603: AND transaction_source_code = l_etc_source_code
2604: ) WHERE rownum = 1;
2605:
2622: l_pfc_currency_code := p_fp_cols_tgt_rec.x_projfunc_currency_code;
2623: IF l_currency_flag = 'PC' OR l_currency_flag = 'PFC' THEN
2624: /* No matter ETC source is 'FINANCIAL_PLAN' or 'WORKPLAN_RESOURCES', always get
2625: total plan amounts in PC or PFC from financial data model.*/
2626: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
2627: NVL(SUM(NVL(total_plan_quantity,0)),0),
2628: NVL(SUM(NVL(
2629: DECODE(l_currency_flag, 'PC', total_pc_raw_cost,
2630: 'PFC', total_pfc_raw_cost),0)),0),
2637: INTO l_tot_quantity_pc_pfc,
2638: l_tot_raw_cost_pc_pfc,
2639: l_tot_brdn_cost_pc_pfc,
2640: l_tot_revenue_pc_pfc
2641: FROM PA_FP_CALC_AMT_TMP2
2642: WHERE resource_assignment_id = l_src_res_asg_id
2643: AND transaction_source_code = l_etc_source_code;
2644:
2645: -- IPM Change:
2767:
2768: /*When not taking periodic rates, we need to calculate out the average
2769: rates from the source resource assignments that are mapped to the current
2770: target resource assignmentInsert the single PC record for total ETC.*/
2771: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
2772: NVL(SUM(NVL(total_plan_quantity,0)),0),
2773: DECODE(l_currency_flag,
2774: 'PC', NVL(SUM(NVL(total_pc_raw_cost,0)),0),
2775: 'PFC', NVL(SUM(NVL(total_pfc_raw_cost,0)),0)),
2782: INTO l_pc_pfc_rate_quantity,
2783: l_pc_pfc_rate_raw_cost,
2784: l_pc_pfc_rate_brdn_cost,
2785: l_pc_pfc_rate_revenue
2786: FROM pa_fp_calc_amt_tmp2
2787: WHERE resource_assignment_id = l_src_res_asg_id
2788: AND transaction_source_code in ('FINANCIAL_PLAN',
2789: 'WORKPLAN_RESOURCES');
2790:
2871:
2872: ELSIF l_currency_flag = 'TC' THEN
2873: /* No matter ETC source is 'FINANCIAL_PLAN' or 'WORKPLAN_RESOURCES', always
2874: get total plan amounts by txn currency from financial data model.*/
2875: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
2876: txn_currency_code,
2877: SUM(NVL(total_plan_quantity,0)),
2878: SUM(NVL(total_txn_raw_cost,0)),
2879: SUM(NVL(total_txn_burdened_cost,0)),
2883: l_tot_quantity_tab,
2884: l_tot_raw_cost_tab,
2885: l_tot_brdn_cost_tab,
2886: l_tot_revenue_tab
2887: FROM PA_FP_CALC_AMT_TMP2
2888: WHERE resource_assignment_id = l_src_res_asg_id
2889: AND transaction_source_code = l_etc_source_code
2890: GROUP BY txn_currency_code;
2891:
3047: AND data_type_code = DECODE(L_ETC_SOURCE_CODE,
3048: 'WORKPLAN_RESOURCES', 'ETC_WP',
3049: 'FINANCIAL_PLAN', 'ETC_FP')
3050: MINUS
3051: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
3052: DISTINCT txn_currency_code
3053: FROM PA_FP_CALC_AMT_TMP2
3054: WHERE resource_assignment_id = l_src_res_asg_id
3055: AND transaction_source_code = l_etc_source_code
3049: 'FINANCIAL_PLAN', 'ETC_FP')
3050: MINUS
3051: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
3052: DISTINCT txn_currency_code
3053: FROM PA_FP_CALC_AMT_TMP2
3054: WHERE resource_assignment_id = l_src_res_asg_id
3055: AND transaction_source_code = l_etc_source_code
3056: ) WHERE rownum = 1;
3057:
3075: ELSE
3076: l_etc_quantity_tab(1) := l_etc_quantity_sum;
3077:
3078: -- Bug 5203622: Store OTHER rejection code in the
3079: -- txn_currency_code column of pa_fp_calc_amt_tmp2.
3080: l_other_rej_code_tab(1) := null;
3081: IF l_rate_based_flag = 'N' AND
3082: l_source_version_type = 'ALL' AND
3083: l_target_version_type = 'ALL' AND
3166: /*When not taking periodic rates, we need to calculate out the average
3167: rates from the source resource assignments that are mapped to the current
3168: target resource assignment.*/
3169: FOR i IN 1..l_tot_currency_code_tab.count LOOP
3170: SELECT /*+ INDEX(pa_fp_calc_amt_tmp2,PA_FP_CALC_AMT_TMP2_N2)*/
3171: NVL(SUM(NVL(total_plan_quantity,0)),0),
3172: NVL(SUM(NVL(total_txn_raw_cost,0)),0),
3173: NVL(SUM(NVL(total_txn_burdened_cost,0)),0),
3174: NVL(SUM(NVL(total_txn_revenue,0)),0),
3181: l_txn_rate_revenue,
3182: l_pc_rate_raw_cost,
3183: l_pc_rate_brdn_cost,
3184: l_pc_rate_revenue
3185: FROM pa_fp_calc_amt_tmp2
3186: WHERE resource_assignment_id = l_src_res_asg_id
3187: AND txn_currency_code = l_tot_currency_code_tab(i)
3188: AND transaction_source_code in ('FINANCIAL_PLAN',
3189: 'WORKPLAN_RESOURCES');
3270: This only happens for non rate based resources*/
3271:
3272: /*No matter ETC source is 'FINANCIAL_PLAN' or 'WORKPLAN_RESOURCES',
3273: always get total plan amounts in PC from financial data model*/
3274: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
3275: txn_currency_code,
3276: SUM(NVL(total_plan_quantity,0)),
3277: SUM(NVL(total_pc_raw_cost,0)),
3278: SUM(NVL(total_pc_burdened_cost,0)),
3282: l_tot_quantity_pc_tab,
3283: l_tot_raw_cost_pc_tab,
3284: l_tot_brdn_cost_pc_tab,
3285: l_tot_revenue_pc_tab
3286: FROM PA_FP_CALC_AMT_TMP2
3287: WHERE resource_assignment_id = l_src_res_asg_id
3288: AND transaction_source_code = l_etc_source_code
3289: GROUP BY txn_currency_code;
3290:
3517: from the source resource assignments that are mapped to the current target
3518: resource assignment.*/
3519:
3520: FOR i IN 1..l_tot_currency_code_tab.count LOOP
3521: SELECT /*+ INDEX(PA_FP_CALC_AMT_TMP2,PA_FP_CALC_AMT_TMP2_N2)*/
3522: NVL(SUM(NVL(total_plan_quantity,0)),0),
3523: NVL(SUM(NVL(total_txn_raw_cost,0)),0),
3524: NVL(SUM(NVL(total_txn_burdened_cost,0)),0),
3525: NVL(SUM(NVL(total_txn_revenue,0)),0),
3532: l_txn_rate_revenue,
3533: l_pc_rate_raw_cost,
3534: l_pc_rate_brdn_cost,
3535: l_pc_rate_revenue
3536: FROM pa_fp_calc_amt_tmp2
3537: WHERE resource_assignment_id = l_src_res_asg_id
3538: AND txn_currency_code = l_tot_currency_code_tab(i)
3539: AND transaction_source_code in ('FINANCIAL_PLAN' ,
3540: 'WORKPLAN_RESOURCES');
3635: l_transaction_source_code := 'ETC';
3636: END IF;
3637:
3638: -- Bug 5203622: Store OTHER rejection code in the
3639: -- txn_currency_code column of pa_fp_calc_amt_tmp2.
3640: FORALL i IN 1..l_ins_etc_quantity_tab.count
3641: INSERT INTO PA_FP_CALC_AMT_TMP2
3642: ( RESOURCE_ASSIGNMENT_ID,
3643: TARGET_RES_ASG_ID,
3637:
3638: -- Bug 5203622: Store OTHER rejection code in the
3639: -- txn_currency_code column of pa_fp_calc_amt_tmp2.
3640: FORALL i IN 1..l_ins_etc_quantity_tab.count
3641: INSERT INTO PA_FP_CALC_AMT_TMP2
3642: ( RESOURCE_ASSIGNMENT_ID,
3643: TARGET_RES_ASG_ID,
3644: ETC_CURRENCY_CODE,
3645: ETC_PLAN_QUANTITY,