[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.txn_brdn_cost, 0) <> 0) OR
90: (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
130: sum(DECODE(c_multi_currency_flag,
131: 'Y', pji_tmp.pou_revenue,
132: 'N', pji_tmp.prj_revenue,
133: 'A', pji_tmp.pou_revenue))
134: FROM pji_fm_xbs_accum_tmp1 pji_tmp,pa_periods_all pd
135: WHERE c_version_type = 'COST'
136: AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
137: (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
138: (NVL(pji_tmp.quantity,0) <> 0)
177: sum(DECODE(c_multi_currency_flag,
178: 'Y', pji_tmp.pou_revenue,
179: 'N', pji_tmp.prj_revenue,
180: 'A', pji_tmp.pou_revenue))
181: FROM pji_fm_xbs_accum_tmp1 pji_tmp,pa_periods_all pd
182: WHERE c_version_type = 'REVENUE'
183: AND (
184: (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
185: (NVL(pji_tmp.quantity,0) <> 0)
229: sum(DECODE(c_multi_currency_flag,
230: 'Y', pji_tmp.pou_revenue,
231: 'N', pji_tmp.prj_revenue,
232: 'A', pji_tmp.pou_revenue))
233: FROM pji_fm_xbs_accum_tmp1 pji_tmp,gl_period_statuses gd
234: WHERE c_version_type = 'ALL'
235: AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
236: (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
237: (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
279: sum(DECODE(c_multi_currency_flag,
280: 'Y', pji_tmp.pou_revenue,
281: 'N', pji_tmp.prj_revenue,
282: 'A', pji_tmp.pou_revenue))
283: FROM pji_fm_xbs_accum_tmp1 pji_tmp,gl_period_statuses gd
284: WHERE c_version_type = 'COST'
285: AND (
286: (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
287: (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
329: sum(DECODE(c_multi_currency_flag,
330: 'Y', pji_tmp.pou_revenue,
331: 'N', pji_tmp.prj_revenue,
332: 'A', pji_tmp.pou_revenue))
333: FROM pji_fm_xbs_accum_tmp1 pji_tmp,gl_period_statuses gd
334: WHERE c_version_type = 'REVENUE'
335: AND (
336: (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
337: (NVL(pji_tmp.quantity,0) <> 0)
383: sum(DECODE(c_multi_currency_flag,
384: 'Y', pji_tmp.pou_revenue,
385: 'N', pji_tmp.prj_revenue,
386: 'A', pji_tmp.pou_revenue))
387: FROM pji_fm_xbs_accum_tmp1 pji_tmp,
388: pa_resource_assignments ra
389: WHERE c_version_type = 'ALL'
390: AND ( (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
391: (NVL(pji_tmp.txn_brdn_cost, 0) <> 0) OR
431: sum(DECODE(c_multi_currency_flag,
432: 'Y', pji_tmp.pou_revenue,
433: 'N', pji_tmp.prj_revenue,
434: 'A', pji_tmp.pou_revenue))
435: FROM pji_fm_xbs_accum_tmp1 pji_tmp,
436: pa_resource_assignments ra
437: WHERE c_version_type = 'COST'
438: AND (
439: (NVL(pji_tmp.txn_raw_cost, 0) <> 0) OR
479: sum(DECODE(c_multi_currency_flag,
480: 'Y', pji_tmp.pou_revenue,
481: 'N', pji_tmp.prj_revenue,
482: 'A', pji_tmp.pou_revenue))
483: FROM pji_fm_xbs_accum_tmp1 pji_tmp,
484: pa_resource_assignments ra
485: WHERE c_version_type = 'REVENUE'
486: AND (
487: (NVL(pji_tmp.txn_revenue, 0) <> 0) OR
628: END IF;
629: l_record_type := l_record_type||'N';
630: IF P_PA_DEBUG_MODE = 'Y' THEN
631: pa_fp_gen_amount_utils.fp_debug
632: (p_msg => 'Before calling pji_fm_xbs_accum_tmp1',
633: p_module_name => l_module_name,
634: p_log_level => 5);
635: END IF;
636: --dbms_output.put_line('Before calling pji api');
633: p_module_name => l_module_name,
634: p_log_level => 5);
635: END IF;
636: --dbms_output.put_line('Before calling pji api');
637: --Calling PJI API to get table pji_fm_xbs_accum_tmp1 populated
638: --hr_utility.trace_on(null,'mftest');
639: --hr_utility.trace('before entering get_sum');
640: --hr_utility.trace('l_project_id_tab:'||l_project_id_tab(1));
641: --hr_utility.trace('l_resource_list_id_tab:'||l_resource_list_id_tab(1));
656: p_currency_type => 6,
657: x_return_status => x_return_status,
658: x_msg_code => x_msg_data);
659: --dbms_output.put_line('After calling pji api: '||x_return_status);
660: select count(*) into l_count from pji_fm_xbs_accum_tmp1;
661:
662:
663: --hr_utility.trace('after entering get_sum:'||x_return_status);
664: --delete from get_sum_test;
661:
662:
663: --hr_utility.trace('after entering get_sum:'||x_return_status);
664: --delete from get_sum_test;
665: --insert into get_sum_test (select * from pji_fm_xbs_accum_tmp1);
666: IF P_PA_DEBUG_MODE = 'Y' THEN
667: pa_fp_gen_amount_utils.fp_debug
668: (p_msg => 'After calling pji_fm_xbs_accum_tmp1,return status is: '||x_return_status,
669: p_module_name => l_module_name,
664: --delete from get_sum_test;
665: --insert into get_sum_test (select * from pji_fm_xbs_accum_tmp1);
666: IF P_PA_DEBUG_MODE = 'Y' THEN
667: pa_fp_gen_amount_utils.fp_debug
668: (p_msg => 'After calling pji_fm_xbs_accum_tmp1,return status is: '||x_return_status,
669: p_module_name => l_module_name,
670: p_log_level => 5);
671: END IF;
672: --dbms_output.put_line('After calling pji api: '||x_return_status);
685: END IF;
686: RETURN;
687: END IF;
688:
689: select count(*) into l_count_no_rlm from pji_fm_xbs_accum_tmp1 WHERE
690: res_list_member_id IS NULL;
691:
692: IF l_count_no_rlm > 0 THEN
693: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
694: p_msg_name => 'PA_FP_NO_RLM_ID_FOR_ACTUAL');
695: RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
696: END IF;
697:
698: /* Update rlm_id for all rows in pji_fm_xbs_accum_tmp1 if the resource list
699: * (p_fp_cols_rec.X_RESOURCE_LIST_ID) is None - Uncategorized.
700: * This logic is not handled by the PJI generic resource mapping API. */
701:
702: SELECT NVL(uncategorized_flag,'N')
708: l_rlm_id := PA_FP_GEN_AMOUNT_UTILS.GET_RLM_ID (
709: p_project_id => p_project_id,
710: p_resource_list_id => p_fp_cols_rec.X_RESOURCE_LIST_ID,
711: p_resource_class_code => 'FINANCIAL_ELEMENTS' );
712: UPDATE pji_fm_xbs_accum_tmp1
713: SET res_list_member_id = l_rlm_id;
714: END IF;
715:
716: /* updating the project element id ( task id ) to NULL
716: /* updating the project element id ( task id ) to NULL
717: when the value is <= 0 for addressing the P1 bug 3841480.
718: Please note that we cannot resolve the issue by populating the NULL value
719: into the tmp table PA_FP_PLANNING_RES_TMP1. Because, the task id value is referred
720: in the pji_fm_xbs_accum_tmp1 table later in the code. */
721:
722: update pji_fm_xbs_accum_tmp1 set project_element_id = null
723: where NVL(project_element_id,0) <= 0;
724:
718: Please note that we cannot resolve the issue by populating the NULL value
719: into the tmp table PA_FP_PLANNING_RES_TMP1. Because, the task id value is referred
720: in the pji_fm_xbs_accum_tmp1 table later in the code. */
721:
722: update pji_fm_xbs_accum_tmp1 set project_element_id = null
723: where NVL(project_element_id,0) <= 0;
724:
725: /**Populating PA_FP_PLANNING_RES_TMP1, call COPY_ACUTALS_PUB.CREATE_RES_ASG to create
726: *missing resource assignment in pa_resource_assignment table. After that, resource_
733: RESOURCE_ASSIGNMENT_ID )
734: ( SELECT DISTINCT PROJECT_ELEMENT_ID,
735: RES_LIST_MEMBER_ID,
736: NULL
737: FROM PJI_FM_XBS_ACCUM_TMP1);
738: -- select count(*) into l_count from pa_resource_assignments where
739: -- budget_version_id = p_budget_version_id;
740: --dbms_output.put_line('before calling cre res asg api: res_assign has: '||l_count);
741: IF P_PA_DEBUG_MODE = 'Y' THEN
762: raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
763: END IF;
764:
765: /**Calling update_res_asg to populate the newly created resource_assignment_id back to
766: *pa_fp_planning_res_tmp1. Then this value needs to populated back to pji_fm_xbs_accum_tmp1
767: **/
768: IF P_PA_DEBUG_MODE = 'Y' THEN
769: pa_fp_gen_amount_utils.fp_debug
770: (p_msg => 'Before calling update_res_asg',
788: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
789: raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
790: END IF;
791:
792: UPDATE PJI_FM_XBS_ACCUM_TMP1 tmp1
793: SET source_id =
794: (SELECT /*+ INDEX(ra,PA_FP_PLANNING_RES_TMP1_N2)*/ resource_assignment_id
795: FROM PA_FP_PLANNING_RES_TMP1 ra
796: WHERE nvl(ra.task_id,0) = nvl(tmp1.project_element_id,0)
794: (SELECT /*+ INDEX(ra,PA_FP_PLANNING_RES_TMP1_N2)*/ resource_assignment_id
795: FROM PA_FP_PLANNING_RES_TMP1 ra
796: WHERE nvl(ra.task_id,0) = nvl(tmp1.project_element_id,0)
797: AND ra.resource_list_member_id = tmp1.res_list_member_id );
798: --dbms_output.put_line('No.of rows updated in pji_fm_xbs_accum_tmp1 table: '||sql%rowcount);
799: --dbms_output.put_line('Opening distinct_ra_curr_cursor');
800: OPEN distinct_ra_curr_cursor(l_txn_currency_flag,
801: P_FP_COLS_REC.X_PROJECT_CURRENCY_CODE,
802: P_FP_COLS_REC.X_PROJFUNC_CURRENCY_CODE);
818:
819: SELECT DISTINCT source_id
820: BULK COLLECT
821: INTO l_res_asg_id_tmp_tab
822: FROM pji_fm_xbs_accum_tmp1;
823:
824: -- Bug 4170419: Start
825: -- FORALL k IN 1..l_res_asg_id_tmp_tab.count
826: -- UPDATE pa_resource_assignments ra