[Home] [Help]
36: DECODE(c_multi_currency_flag,
37: 'Y', pji_tmp.txn_currency_code,
38: 'N', c_proj_currency_code,
39: 'A', c_projfunc_currency_code)
40: FROM pji_fm_xbs_accum_tmp1 pji_tmp;
41:
42: /* Bug No.3858184
43: Cursors(budget_line_cursor_pa, budget_line_cursor_gl, budget_line_cursor_np)
44: modified to filter the records based on the VERSION_TYPE.
41:
42: /* Bug No.3858184
43: Cursors(budget_line_cursor_pa, budget_line_cursor_gl, budget_line_cursor_np)
44: modified to filter the records based on the VERSION_TYPE.
45: For COST versions, the records in the PJI_FM_XBS_ACCUM_TMP1 table
46: will be processed only if raw cost or the burdened cost is not equal to zero.
47: For Revenue versions, the records in the PJI_FM_XBS_ACCUM_TMP1 table
48: will be processed only if the revenue amt is not equal to zero. */
49:
43: Cursors(budget_line_cursor_pa, budget_line_cursor_gl, budget_line_cursor_np)
44: modified to filter the records based on the VERSION_TYPE.
45: For COST versions, the records in the PJI_FM_XBS_ACCUM_TMP1 table
46: will be processed only if raw cost or the burdened cost is not equal to zero.
47: For Revenue versions, the records in the PJI_FM_XBS_ACCUM_TMP1 table
48: will be processed only if the revenue amt is not equal to zero. */
49:
50: CURSOR budget_line_cursor_pa(c_multi_currency_flag VARCHAR2,
51: c_res_asg_id NUMBER,
82: sum(DECODE(c_multi_currency_flag,
83: 'Y', pji_tmp.pou_revenue,
84: 'N', pji_tmp.prj_revenue,
85: 'A', pji_tmp.pou_revenue))
86: FROM pji_fm_xbs_accum_tmp1 pji_tmp,pa_periods_all pd
87: WHERE c_version_type = 'ALL'
88: AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
89: (NVL(pji_tmp.prj_raw_cost, 0) <> 0) OR --Bug 9666017
90: (NVL(pji_tmp.pou_raw_cost, 0) <> 0) OR --Bug 9666017
136: sum(DECODE(c_multi_currency_flag,
137: 'Y', pji_tmp.pou_revenue,
138: 'N', pji_tmp.prj_revenue,
139: 'A', pji_tmp.pou_revenue))
140: FROM pji_fm_xbs_accum_tmp1 pji_tmp,pa_periods_all pd
141: WHERE c_version_type = 'COST'
142: AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
143: (NVL(pji_tmp.prj_raw_cost, 0) <> 0) OR --Bug 9666017
144: (NVL(pji_tmp.pou_raw_cost, 0) <> 0) OR --Bug 9666017
187: sum(DECODE(c_multi_currency_flag,
188: 'Y', pji_tmp.pou_revenue,
189: 'N', pji_tmp.prj_revenue,
190: 'A', pji_tmp.pou_revenue))
191: FROM pji_fm_xbs_accum_tmp1 pji_tmp,pa_periods_all pd
192: WHERE c_version_type = 'REVENUE'
193: AND (
194: (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
195: (NVL(pji_tmp.prj_revenue, 0) <> 0) OR --Bug 9666017
241: sum(DECODE(c_multi_currency_flag,
242: 'Y', pji_tmp.pou_revenue,
243: 'N', pji_tmp.prj_revenue,
244: 'A', pji_tmp.pou_revenue))
245: FROM pji_fm_xbs_accum_tmp1 pji_tmp,gl_period_statuses gd
246: WHERE c_version_type = 'ALL'
247: AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
248: (NVL(pji_tmp.prj_raw_cost, 0) <> 0) OR --Bug 9666017
249: (NVL(pji_tmp.pou_raw_cost, 0) <> 0) OR --Bug 9666017
297: sum(DECODE(c_multi_currency_flag,
298: 'Y', pji_tmp.pou_revenue,
299: 'N', pji_tmp.prj_revenue,
300: 'A', pji_tmp.pou_revenue))
301: FROM pji_fm_xbs_accum_tmp1 pji_tmp,gl_period_statuses gd
302: WHERE c_version_type = 'COST'
303: AND (
304: (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
305: (NVL(pji_tmp.prj_raw_cost, 0) <> 0) OR --Bug 9666017
351: sum(DECODE(c_multi_currency_flag,
352: 'Y', pji_tmp.pou_revenue,
353: 'N', pji_tmp.prj_revenue,
354: 'A', pji_tmp.pou_revenue))
355: FROM pji_fm_xbs_accum_tmp1 pji_tmp,gl_period_statuses gd
356: WHERE c_version_type = 'REVENUE'
357: AND (
358: (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
359: (NVL(pji_tmp.prj_revenue, 0) <> 0) OR --Bug 9666017
407: sum(DECODE(c_multi_currency_flag,
408: 'Y', pji_tmp.pou_revenue,
409: 'N', pji_tmp.prj_revenue,
410: 'A', pji_tmp.pou_revenue))
411: FROM pji_fm_xbs_accum_tmp1 pji_tmp,
412: pa_resource_assignments ra
413: WHERE c_version_type = 'ALL'
414: AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
415: (NVL(pji_tmp.prj_raw_cost, 0) <> 0) OR --Bug 9666017
461: sum(DECODE(c_multi_currency_flag,
462: 'Y', pji_tmp.pou_revenue,
463: 'N', pji_tmp.prj_revenue,
464: 'A', pji_tmp.pou_revenue))
465: FROM pji_fm_xbs_accum_tmp1 pji_tmp,
466: pa_resource_assignments ra
467: WHERE c_version_type = 'COST'
468: AND (
469: (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
513: sum(DECODE(c_multi_currency_flag,
514: 'Y', pji_tmp.pou_revenue,
515: 'N', pji_tmp.prj_revenue,
516: 'A', pji_tmp.pou_revenue))
517: FROM pji_fm_xbs_accum_tmp1 pji_tmp,
518: pa_resource_assignments ra
519: WHERE c_version_type = 'REVENUE'
520: AND (
521: (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
664: END IF;
665: l_record_type := l_record_type||'N';
666: IF P_PA_DEBUG_MODE = 'Y' THEN
667: pa_fp_gen_amount_utils.fp_debug
668: (p_msg => 'Before calling pji_fm_xbs_accum_tmp1',
669: p_module_name => l_module_name,
670: p_log_level => 5);
671: END IF;
672: --dbms_output.put_line('Before calling pji api');
669: p_module_name => l_module_name,
670: p_log_level => 5);
671: END IF;
672: --dbms_output.put_line('Before calling pji api');
673: --Calling PJI API to get table pji_fm_xbs_accum_tmp1 populated
674: --hr_utility.trace_on(null,'mftest');
675: --hr_utility.trace('before entering get_sum');
676: --hr_utility.trace('l_project_id_tab:'||l_project_id_tab(1));
677: --hr_utility.trace('l_resource_list_id_tab:'||l_resource_list_id_tab(1));
692: p_currency_type => 6,
693: x_return_status => x_return_status,
694: x_msg_code => x_msg_data);
695: --dbms_output.put_line('After calling pji api: '||x_return_status);
696: select count(*) into l_count from pji_fm_xbs_accum_tmp1;
697:
698:
699: --hr_utility.trace('after entering get_sum:'||x_return_status);
700: --delete from get_sum_test;
697:
698:
699: --hr_utility.trace('after entering get_sum:'||x_return_status);
700: --delete from get_sum_test;
701: --insert into get_sum_test (select * from pji_fm_xbs_accum_tmp1);
702: IF P_PA_DEBUG_MODE = 'Y' THEN
703: pa_fp_gen_amount_utils.fp_debug
704: (p_msg => 'After calling pji_fm_xbs_accum_tmp1,return status is: '||x_return_status,
705: p_module_name => l_module_name,
700: --delete from get_sum_test;
701: --insert into get_sum_test (select * from pji_fm_xbs_accum_tmp1);
702: IF P_PA_DEBUG_MODE = 'Y' THEN
703: pa_fp_gen_amount_utils.fp_debug
704: (p_msg => 'After calling pji_fm_xbs_accum_tmp1,return status is: '||x_return_status,
705: p_module_name => l_module_name,
706: p_log_level => 5);
707: END IF;
708: --dbms_output.put_line('After calling pji api: '||x_return_status);
721: END IF;
722: RETURN;
723: END IF;
724:
725: select count(*) into l_count_no_rlm from pji_fm_xbs_accum_tmp1 WHERE
726: res_list_member_id IS NULL;
727:
728: IF l_count_no_rlm > 0 THEN
729: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
730: p_msg_name => 'PA_FP_NO_RLM_ID_FOR_ACTUAL');
731: RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
732: END IF;
733:
734: /* Update rlm_id for all rows in pji_fm_xbs_accum_tmp1 if the resource list
735: * (p_fp_cols_rec.X_RESOURCE_LIST_ID) is None - Uncategorized.
736: * This logic is not handled by the PJI generic resource mapping API. */
737:
738: SELECT NVL(uncategorized_flag,'N')
744: l_rlm_id := PA_FP_GEN_AMOUNT_UTILS.GET_RLM_ID (
745: p_project_id => p_project_id,
746: p_resource_list_id => p_fp_cols_rec.X_RESOURCE_LIST_ID,
747: p_resource_class_code => 'FINANCIAL_ELEMENTS' );
748: UPDATE pji_fm_xbs_accum_tmp1
749: SET res_list_member_id = l_rlm_id;
750: END IF;
751:
752: /* updating the project element id ( task id ) to NULL
752: /* updating the project element id ( task id ) to NULL
753: when the value is <= 0 for addressing the P1 bug 3841480.
754: Please note that we cannot resolve the issue by populating the NULL value
755: into the tmp table PA_FP_PLANNING_RES_TMP1. Because, the task id value is referred
756: in the pji_fm_xbs_accum_tmp1 table later in the code. */
757:
758: update pji_fm_xbs_accum_tmp1 set project_element_id = null
759: where NVL(project_element_id,0) <= 0;
760:
754: Please note that we cannot resolve the issue by populating the NULL value
755: into the tmp table PA_FP_PLANNING_RES_TMP1. Because, the task id value is referred
756: in the pji_fm_xbs_accum_tmp1 table later in the code. */
757:
758: update pji_fm_xbs_accum_tmp1 set project_element_id = null
759: where NVL(project_element_id,0) <= 0;
760:
761: /**Populating PA_FP_PLANNING_RES_TMP1, call COPY_ACUTALS_PUB.CREATE_RES_ASG to create
762: *missing resource assignment in pa_resource_assignment table. After that, resource_
772: ( SELECT DISTINCT PROJECT_ELEMENT_ID,
773: RES_LIST_MEMBER_ID,
774: NULL,
775: CBS_ELEMENT_ID --bug#16791711
776: FROM PJI_FM_XBS_ACCUM_TMP1);
777: -- select count(*) into l_count from pa_resource_assignments where
778: -- budget_version_id = p_budget_version_id;
779: --dbms_output.put_line('before calling cre res asg api: res_assign has: '||l_count);
780: IF P_PA_DEBUG_MODE = 'Y' THEN
801: raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
802: END IF;
803:
804: /**Calling update_res_asg to populate the newly created resource_assignment_id back to
805: *pa_fp_planning_res_tmp1. Then this value needs to populated back to pji_fm_xbs_accum_tmp1
806: **/
807: IF P_PA_DEBUG_MODE = 'Y' THEN
808: pa_fp_gen_amount_utils.fp_debug
809: (p_msg => 'Before calling update_res_asg',
827: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
828: raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
829: END IF;
830:
831: UPDATE PJI_FM_XBS_ACCUM_TMP1 tmp1
832: SET source_id =
833: (SELECT /*+ INDEX(ra,PA_FP_PLANNING_RES_TMP1_N2)*/ resource_assignment_id
834: FROM PA_FP_PLANNING_RES_TMP1 ra
835: WHERE nvl(ra.task_id,0) = nvl(tmp1.project_element_id,0)
835: WHERE nvl(ra.task_id,0) = nvl(tmp1.project_element_id,0)
836: AND ra.resource_list_member_id = tmp1.res_list_member_id
837: AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
838: );
839: --dbms_output.put_line('No.of rows updated in pji_fm_xbs_accum_tmp1 table: '||sql%rowcount);
840: --dbms_output.put_line('Opening distinct_ra_curr_cursor');
841: OPEN distinct_ra_curr_cursor(l_txn_currency_flag,
842: P_FP_COLS_REC.X_PROJECT_CURRENCY_CODE,
843: P_FP_COLS_REC.X_PROJFUNC_CURRENCY_CODE);
859:
860: SELECT DISTINCT source_id
861: BULK COLLECT
862: INTO l_res_asg_id_tmp_tab
863: FROM pji_fm_xbs_accum_tmp1;
864:
865: -- Bug 4170419: Start
866: -- FORALL k IN 1..l_res_asg_id_tmp_tab.count
867: -- UPDATE pa_resource_assignments ra
2071: is
2072: select tmp.source_id
2073: ,tmp.txn_currency_code
2074: ,tmp.period_name
2075: FROM pji_fm_xbs_accum_tmp1 tmp
2076: WHERE tmp.source_id = p_resource_assignment_id;
2077: get_period_info_rec get_period_info_csr%rowtype;
2078:
2079: -- Get distinct resource assignment id and txn currency code from temp table
2084: DECODE(c_multi_currency_flag,
2085: 'Y', pji_tmp.txn_currency_code,
2086: 'N', c_proj_currency_code,
2087: 'A', c_projfunc_currency_code)
2088: FROM pji_fm_xbs_accum_tmp1 pji_tmp
2089: WHERE pji_tmp.source_id is NOT NULL --bug#8485646
2090: order by 1,2;
2091:
2092: -- Cursor to check whether budget line already exists for the period
2161: sum(DECODE(c_multi_currency_flag,
2162: 'Y', pji_tmp.pou_revenue,
2163: 'N', pji_tmp.prj_revenue,
2164: 'A', pji_tmp.pou_revenue))
2165: FROM pji_fm_xbs_accum_tmp1 pji_tmp,pa_periods_all pd
2166: WHERE c_version_type = 'ALL'
2167: AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
2168: (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
2169: (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
2209: sum(DECODE(c_multi_currency_flag,
2210: 'Y', pji_tmp.pou_revenue,
2211: 'N', pji_tmp.prj_revenue,
2212: 'A', pji_tmp.pou_revenue))
2213: FROM pji_fm_xbs_accum_tmp1 pji_tmp,pa_periods_all pd
2214: WHERE c_version_type = 'COST'
2215: AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
2216: (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
2217: (NVL(pji_tmp.quantity,0) <> 0)
2270: sum(DECODE(c_multi_currency_flag,
2271: 'Y', pji_tmp.pou_revenue,
2272: 'N', pji_tmp.prj_revenue,
2273: 'A', pji_tmp.pou_revenue)) pou_revenue
2274: FROM pji_fm_xbs_accum_tmp1 pji_tmp,gl_period_statuses gd
2275: WHERE c_version_type = 'ALL'
2276: AND pji_tmp.source_id = c_source_id
2277: AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
2278: (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
2327: sum(DECODE(c_multi_currency_flag,
2328: 'Y', pji_tmp.pou_revenue,
2329: 'N', pji_tmp.prj_revenue,
2330: 'A', pji_tmp.pou_revenue)) pou_revenue
2331: FROM pji_fm_xbs_accum_tmp1 pji_tmp,gl_period_statuses gd
2332: WHERE c_version_type = 'COST'
2333: AND pji_tmp.source_id = c_source_id
2334: AND (
2335: (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
2494: end if;
2495: /* Calling utility api to get plan version details - End */
2496:
2497: /*---------------------------------------------------------------
2498: Populate actual amounts in PJI_FM_XBS_ACCUM_TMP1 table - Begin
2499: ---------------------------------------------------------------*/
2500: -- get actual thru date
2501: l_actuals_through_date := to_date(PA_FP_GEN_FCST_PG_PKG.GET_ACTUALS_THRU_PERIOD_DTLS(p_budget_version_id, 'END_DATE'),'rrrrmmdd');
2502: l_calendar_type := l_fp_cols_rec.X_TIME_PHASED_CODE;
2551: l_record_type := l_record_type||'N';
2552:
2553: IF P_PA_DEBUG_MODE = 'Y' THEN
2554: pa_fp_gen_amount_utils.fp_debug
2555: (p_msg => 'Before calling pji_fm_xbs_accum_tmp1',
2556: p_module_name => l_module_name,
2557: p_log_level => 5);
2558: END IF;
2559:
2567: p_currency_type => 6,
2568: x_return_status => x_return_status,
2569: x_msg_code => x_msg_data);
2570:
2571: select count(*) into l_count from pji_fm_xbs_accum_tmp1;
2572:
2573: IF P_PA_DEBUG_MODE = 'Y' THEN
2574: pa_fp_gen_amount_utils.fp_debug
2575: (p_msg => 'After calling pji_fm_xbs_accum_tmp1,return status is: '||x_return_status,
2571: select count(*) into l_count from pji_fm_xbs_accum_tmp1;
2572:
2573: IF P_PA_DEBUG_MODE = 'Y' THEN
2574: pa_fp_gen_amount_utils.fp_debug
2575: (p_msg => 'After calling pji_fm_xbs_accum_tmp1,return status is: '||x_return_status,
2576: p_module_name => l_module_name,
2577: p_log_level => 5);
2578: END IF;
2579:
2591: END IF;
2592: RETURN;
2593: END IF;
2594:
2595: select count(*) into l_count_no_rlm from pji_fm_xbs_accum_tmp1 WHERE
2596: res_list_member_id IS NULL;
2597:
2598: IF l_count_no_rlm > 0 THEN
2599: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2600: p_msg_name => 'PA_FP_NO_RLM_ID_FOR_ACTUAL');
2601: RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2602: END IF;
2603:
2604: /* Update rlm_id for all rows in pji_fm_xbs_accum_tmp1 if the resource list
2605: * (l_fp_cols_rec.X_RESOURCE_LIST_ID) is None - Uncategorized.
2606: * This logic is not handled by the PJI generic resource mapping API. */
2607:
2608: SELECT NVL(uncategorized_flag,'N')
2614: l_rlm_id := PA_FP_GEN_AMOUNT_UTILS.GET_RLM_ID (
2615: p_project_id => p_project_id,
2616: p_resource_list_id => l_fp_cols_rec.X_RESOURCE_LIST_ID,
2617: p_resource_class_code => 'FINANCIAL_ELEMENTS' );
2618: UPDATE pji_fm_xbs_accum_tmp1
2619: SET res_list_member_id = l_rlm_id;
2620: END IF;
2621:
2622: /* updating the project element id ( task id ) to NULL
2620: END IF;
2621:
2622: /* updating the project element id ( task id ) to NULL
2623: when the value is <= 0. */
2624: update pji_fm_xbs_accum_tmp1 set project_element_id = null
2625: where NVL(project_element_id,0) <= 0;
2626:
2627: /* updating the resource assignment id in pji_fm_xbs_accum_tmp1 */
2628: UPDATE PJI_FM_XBS_ACCUM_TMP1 tmp1
2623: when the value is <= 0. */
2624: update pji_fm_xbs_accum_tmp1 set project_element_id = null
2625: where NVL(project_element_id,0) <= 0;
2626:
2627: /* updating the resource assignment id in pji_fm_xbs_accum_tmp1 */
2628: UPDATE PJI_FM_XBS_ACCUM_TMP1 tmp1
2629: SET source_id =
2630: (SELECT resource_assignment_id
2631: FROM pa_resource_assignments ra
2624: update pji_fm_xbs_accum_tmp1 set project_element_id = null
2625: where NVL(project_element_id,0) <= 0;
2626:
2627: /* updating the resource assignment id in pji_fm_xbs_accum_tmp1 */
2628: UPDATE PJI_FM_XBS_ACCUM_TMP1 tmp1
2629: SET source_id =
2630: (SELECT resource_assignment_id
2631: FROM pa_resource_assignments ra
2632: WHERE nvl(ra.task_id,0) = nvl(tmp1.project_element_id,0)
2634: AND nvl(ra.CBS_ELEMENT_ID,-1) = nvl(tmp1.CBS_ELEMENT_ID,-1) --bug#16791711
2635: AND ra.budget_version_id = p_budget_version_id);
2636:
2637: /*---------------------------------------------------------------
2638: Populate actual amounts in PJI_FM_XBS_ACCUM_TMP1 table - End
2639: -----------------------------------------------------------------*/
2640:
2641: /*---------------------------------------------------------------
2642: Delete resource assignments which are not selected if resouce
2662: end if;
2663: end loop;
2664:
2665: forall i in 1..l_del_resource_assgn_id_tab.count
2666: delete from pji_fm_xbs_accum_tmp1
2667: where source_id = l_del_resource_assgn_id_tab(i);
2668: end if;
2669: -- populate the resource assignment id along with currency code
2670: -- that needs to be processed
2682:
2683: /*---------------------------------------------------------------
2684: Check whether budget lines are available in pa_budget_lines for
2685: all period for each resource assignment present in
2686: PJI_FM_XBS_ACCUM_TMP1 temp table. If budget line is not present,
2687: create budget line.
2688: -----------------------------------------------------------------*/
2689: if l_resource_assgn_id_tab.count > 0 then
2690: for i in 1..l_resource_assgn_id_tab.count loop
2689: if l_resource_assgn_id_tab.count > 0 then
2690: for i in 1..l_resource_assgn_id_tab.count loop
2691: for period_info_rec in get_period_info_csr(l_resource_assgn_id_tab(i))
2692: loop
2693: -- Check whether budget line exists in PJI_FM_XBS_ACCUM_TMP1
2694: -- table for that period
2695: open budget_line_info_csr(l_resource_assgn_id_tab(i),
2696: period_info_rec.period_name,
2697: l_txn_currency_code_tab(i));