[Home] [Help]
28: x_no_of_ci_plan_versions OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
29: x_ci_est_qty OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
30: x_ci_planned_qty OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
31: x_baselined_planned_qty OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
32: x_ci_ver_plan_prc_code OUT NOCOPY pa_budget_versions.plan_processing_code%TYPE, --File.Sql.39 bug 4440895
33: x_request_id OUT NOCOPY pa_budget_versions.request_id%TYPE) IS --File.Sql.39 bug 4440895
34:
35: l_no_of_app_plan_types NUMBER;
36: l_tmp_fin_plan_type_id NUMBER;
29: x_ci_est_qty OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
30: x_ci_planned_qty OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
31: x_baselined_planned_qty OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
32: x_ci_ver_plan_prc_code OUT NOCOPY pa_budget_versions.plan_processing_code%TYPE, --File.Sql.39 bug 4440895
33: x_request_id OUT NOCOPY pa_budget_versions.request_id%TYPE) IS --File.Sql.39 bug 4440895
34:
35: l_no_of_app_plan_types NUMBER;
36: l_tmp_fin_plan_type_id NUMBER;
37: l_rev_budget_flag Pa_Project_Types_All.Allow_Rev_Budget_Entry_Flag%TYPE;
82: END;
83:
84: x_ci_type_class_code := Pa_Control_Items_Utils.GetCiTypeClassCode(p_ci_id);
85:
86: SELECT COUNT(*) INTO x_no_of_ci_plan_versions FROM pa_budget_Versions
87: WHERE project_id = p_project_id AND
88: nvl(ci_id,-1) = p_ci_id;
89:
90: SELECT COUNT(*) INTO l_no_of_app_plan_types FROM Pa_Proj_Fp_Options
152: INTO x_margin_derived_from_code,
153: x_ci_ver_plan_prc_code,
154: x_request_id
155: FROM pa_proj_fp_options fpo,
156: pa_budget_versions bv
157: WHERE bv.ci_id = p_ci_id
158: AND bv.version_type in ('COST','ALL')
159: AND fpo.fin_plan_version_id = bv.budget_version_id
160: AND bv.project_id = p_project_id;
170: SELECT bv.plan_processing_code,
171: bv.request_id
172: INTO x_ci_ver_plan_prc_code,
173: x_request_id
174: FROM pa_budget_versions bv
175: WHERE bv.ci_id = p_ci_id
176: AND bv.version_type = 'REVENUE'
177: AND bv.project_id = p_project_id;
178: ELSE
183: INTO x_margin_derived_from_code,
184: x_ci_ver_plan_prc_code,
185: x_request_id
186: FROM pa_proj_fp_options fpo,
187: pa_budget_versions bv
188: WHERE fpo.project_id = p_project_id
189: AND bv.current_working_flag = 'Y'
190: AND fpo.fin_plan_version_id = bv.budget_version_id
191: AND bv.approved_cost_plan_type_flag = 'Y';
212: INTO x_margin_derived_from_code,
213: x_ci_ver_plan_prc_code,
214: x_request_id
215: FROM pa_proj_fp_options fpo,
216: pa_budget_versions bv
217: WHERE bv.ci_id = p_ci_id
218: AND bv.version_type in ('COST','ALL')
219: AND fpo.fin_plan_version_id = bv.budget_version_id
220: AND bv.project_id = p_project_id;
230: SELECT bv.plan_processing_code,
231: bv.request_id
232: INTO x_ci_ver_plan_prc_code,
233: x_request_id
234: FROM pa_budget_versions bv
235: WHERE bv.ci_id = p_ci_id
236: AND bv.version_type = 'REVENUE'
237: AND bv.project_id = p_project_id;
238:
244: INTO x_margin_derived_from_code,
245: x_ci_ver_plan_prc_code,
246: x_request_id
247: FROM pa_proj_fp_options fpo,
248: pa_budget_versions bv
249: WHERE fpo.project_id = p_project_id
250: AND bv.current_working_flag = 'Y'
251: AND fpo.fin_plan_version_id = bv.budget_version_id
252: AND bv.approved_cost_plan_type_flag = 'Y';
419: pg.agreement_currency_code
420: INTO x_agreement_num,
421: x_agreement_amount,
422: x_agreement_currency_code
423: FROM pa_agreements_all pg, pa_budget_versions bv
424: WHERE
425: bv.project_id = p_project_id
426: and bv.ci_id = p_ci_id
427: and bv.agreement_id = pg.agreement_id
469:
470: PROCEDURE FP_CI_GET_VERSION_DETAILS
471: (
472: p_project_id IN NUMBER,
473: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
474: x_fin_plan_pref_code OUT NOCOPY pa_proj_fp_options.fin_plan_preference_code%TYPE, --File.Sql.39 bug 4440895
475: x_multi_curr_flag OUT NOCOPY pa_proj_fp_options.plan_in_multi_curr_flag%TYPE, --File.Sql.39 bug 4440895
476: x_fin_plan_level_code OUT NOCOPY pa_proj_fp_options.all_fin_plan_level_code%TYPE, --File.Sql.39 bug 4440895
477: x_resource_list_id OUT NOCOPY pa_proj_fp_options.all_resource_list_id%TYPE, --File.Sql.39 bug 4440895
477: x_resource_list_id OUT NOCOPY pa_proj_fp_options.all_resource_list_id%TYPE, --File.Sql.39 bug 4440895
478: x_time_phased_code OUT NOCOPY pa_proj_fp_options.all_time_phased_code%TYPE, --File.Sql.39 bug 4440895
479: x_uncategorized_flag OUT NOCOPY pa_resource_lists_all_bg.uncategorized_flag%TYPE, --File.Sql.39 bug 4440895
480: x_group_res_type_id OUT NOCOPY pa_resource_lists_all_bg.group_resource_type_id%TYPE, --File.Sql.39 bug 4440895
481: x_version_type OUT NOCOPY pa_budget_versions.version_type%TYPE, --File.Sql.39 bug 4440895
482: x_ci_id OUT NOCOPY pa_budget_versions.ci_id%TYPE, --File.Sql.39 bug 4440895
483: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
484: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
485: x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
478: x_time_phased_code OUT NOCOPY pa_proj_fp_options.all_time_phased_code%TYPE, --File.Sql.39 bug 4440895
479: x_uncategorized_flag OUT NOCOPY pa_resource_lists_all_bg.uncategorized_flag%TYPE, --File.Sql.39 bug 4440895
480: x_group_res_type_id OUT NOCOPY pa_resource_lists_all_bg.group_resource_type_id%TYPE, --File.Sql.39 bug 4440895
481: x_version_type OUT NOCOPY pa_budget_versions.version_type%TYPE, --File.Sql.39 bug 4440895
482: x_ci_id OUT NOCOPY pa_budget_versions.ci_id%TYPE, --File.Sql.39 bug 4440895
483: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
484: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
485: x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
486: )
549: x_resource_list_id,
550: x_time_phased_code,
551: x_version_type,
552: x_ci_id
553: FROM pa_budget_versions bv, pa_proj_fp_options po
554: WHERE
555: bv.budget_version_id = p_budget_version_id
556: AND po.fin_plan_version_id = bv.budget_version_id
557: AND po.project_id = p_project_id;
618: x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
619: )
620: IS
621: -- Local Variable Declaration
622: l_budget_version_id pa_budget_versions.budget_version_id%TYPE;
623:
624: --Defining Local variables for source version
625: l_s_version_id pa_budget_versions.budget_version_id%TYPE;
626: l_s_fin_plan_pref_code pa_proj_fp_options. fin_plan_preference_code%TYPE;
621: -- Local Variable Declaration
622: l_budget_version_id pa_budget_versions.budget_version_id%TYPE;
623:
624: --Defining Local variables for source version
625: l_s_version_id pa_budget_versions.budget_version_id%TYPE;
626: l_s_fin_plan_pref_code pa_proj_fp_options. fin_plan_preference_code%TYPE;
627: l_s_multi_curr_flag pa_proj_fp_options. plan_in_multi_curr_flag%TYPE;
628: l_s_time_phased_code pa_proj_fp_options. all_time_phased_code%TYPE;
629: l_s_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
629: l_s_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
630: l_s_fin_plan_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
631: l_s_uncategorized_flag pa_resource_lists_all_bg.uncategorized_flag %TYPE;
632: l_s_group_res_type_id pa_resource_lists_all_bg.group_resource_type_id%TYPE;
633: l_s_version_type pa_budget_versions.version_type%TYPE;
634: l_s_ci_id pa_budget_versions.ci_id%TYPE;
635:
636: --Defining Local variables for target version
637: l_t_version_id pa_budget_versions.budget_version_id%TYPE;
630: l_s_fin_plan_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
631: l_s_uncategorized_flag pa_resource_lists_all_bg.uncategorized_flag %TYPE;
632: l_s_group_res_type_id pa_resource_lists_all_bg.group_resource_type_id%TYPE;
633: l_s_version_type pa_budget_versions.version_type%TYPE;
634: l_s_ci_id pa_budget_versions.ci_id%TYPE;
635:
636: --Defining Local variables for target version
637: l_t_version_id pa_budget_versions.budget_version_id%TYPE;
638: l_t_fin_plan_pref_code pa_proj_fp_options. fin_plan_preference_code%TYPE;
633: l_s_version_type pa_budget_versions.version_type%TYPE;
634: l_s_ci_id pa_budget_versions.ci_id%TYPE;
635:
636: --Defining Local variables for target version
637: l_t_version_id pa_budget_versions.budget_version_id%TYPE;
638: l_t_fin_plan_pref_code pa_proj_fp_options. fin_plan_preference_code%TYPE;
639: l_t_multi_curr_flag pa_proj_fp_options. plan_in_multi_curr_flag%TYPE;
640: l_t_time_phased_code pa_proj_fp_options. all_time_phased_code%TYPE;
641: l_t_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
641: l_t_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
642: l_t_fin_plan_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
643: l_t_uncategorized_flag pa_resource_lists_all_bg.uncategorized_flag %TYPE;
644: l_t_group_res_type_id pa_resource_lists_all_bg.group_resource_type_id%TYPE;
645: l_t_version_type pa_budget_versions.version_type%TYPE;
646: l_t_ci_id pa_budget_versions.ci_id%TYPE;
647:
648: --Defining Local PL/SQL variables for source version
649: l_s_version_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
642: l_t_fin_plan_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
643: l_t_uncategorized_flag pa_resource_lists_all_bg.uncategorized_flag %TYPE;
644: l_t_group_res_type_id pa_resource_lists_all_bg.group_resource_type_id%TYPE;
645: l_t_version_type pa_budget_versions.version_type%TYPE;
646: l_t_ci_id pa_budget_versions.ci_id%TYPE;
647:
648: --Defining Local PL/SQL variables for source version
649: l_s_version_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
650: l_s_fin_plan_pref_code_tbl PA_PLSQL_DATATYPES.Char30TabTyp;
667: l_t_uncategorized_flag_tbl PA_PLSQL_DATATYPES.Char1TabTyp;
668: l_t_version_type_tbl PA_PLSQL_DATATYPES.Char30TabTyp;
669: l_t_group_res_type_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
670: l_t_ci_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
671: l_s_agreement_id pa_budget_versions.agreement_id%TYPE;
672: l_t_agreement_id pa_budget_versions.agreement_id%TYPE;
673: --Other Local Variables
674: l_merge_possible_code VARCHAR2(1);
675: l_raise_error_flag VARCHAR2(1);
668: l_t_version_type_tbl PA_PLSQL_DATATYPES.Char30TabTyp;
669: l_t_group_res_type_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
670: l_t_ci_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
671: l_s_agreement_id pa_budget_versions.agreement_id%TYPE;
672: l_t_agreement_id pa_budget_versions.agreement_id%TYPE;
673: --Other Local Variables
674: l_merge_possible_code VARCHAR2(1);
675: l_raise_error_flag VARCHAR2(1);
676: l_debug_mode VARCHAR2(30);
690: l_src_ci_number pa_control_items.ci_number%TYPE;
691: l_module_name varchar2(30) := 'check is possible';
692:
693: -- Bug 5845142
694: l_s_app_rev_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
695: l_t_app_rev_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
696:
697: BEGIN
698: IF P_PA_DEBUG_MODE = 'Y' THEN
691: l_module_name varchar2(30) := 'check is possible';
692:
693: -- Bug 5845142
694: l_s_app_rev_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
695: l_t_app_rev_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
696:
697: BEGIN
698: IF P_PA_DEBUG_MODE = 'Y' THEN
699: pa_debug.init_err_stack('PAFPCIUB.FP_CI_CHECK_MERGE_POSSIBLE');
750:
751: SELECT name
752: INTO l_targ_pt_name
753: FROM pa_fin_plan_types_vl fin,
754: pa_budget_versions pbv
755: WHERE fin.fin_plan_type_id = pbv.fin_plan_type_id
756: AND pbv.budget_version_id= l_t_version_id;
757:
758: END IF;
1023: IF (l_s_version_type IN ('REVENUE','ALL') AND l_t_version_type IN ('REVENUE','ALL')) THEN
1024: --Bug 5845142
1025: SELECT NVL(agreement_id,-99), NVL(approved_rev_plan_type_flag,'N')
1026: INTO l_s_agreement_id,l_s_app_rev_flag
1027: FROM pa_budget_versions
1028: where budget_version_id = l_s_version_id;
1029:
1030: --Bug 5845142
1031: SELECT NVL(agreement_id,-100), NVL(approved_rev_plan_type_flag,'N')
1029:
1030: --Bug 5845142
1031: SELECT NVL(agreement_id,-100), NVL(approved_rev_plan_type_flag,'N')
1032: INTO l_t_agreement_id,l_t_app_rev_flag
1033: FROM pa_budget_versions
1034: where budget_version_id = l_t_version_id;
1035:
1036: IF l_debug_mode = 'Y' THEN
1037: pa_debug.g_err_stage:='Source Agr: ' || l_s_agreement_id;
1092: x_merge_possible_code_tbl (i) := l_merge_possible_code;
1093: x_return_status := FND_API.G_RET_STS_ERROR;
1094: ----DBMS_OUTPUT.PUT_LINE('FP_CI_CHECK_MERGE_POSSIBLE - 36****');
1095: select ci_id into l_token_ci_id from
1096: pa_budget_versions where
1097: budget_version_id = l_s_version_id;
1098:
1099: l_chg_doc_token := null;
1100: begin
1562:
1563: cursor check_ci_funding_level is
1564: select DECODE(bv.version_type,'REVENUE',revenue_fin_plan_level_code,
1565: 'ALL',all_fin_plan_level_code,null)
1566: from pa_budget_versions bv,
1567: pa_proj_fp_options po,
1568: pa_ci_impacts pci,
1569: pa_projects_all ppa
1570: where bv.project_id = p_project_id
1677: select 'Y' from dual
1678: where exists (
1679: select 'x'
1680: from pa_summary_project_fundings fu,
1681: pa_budget_versions bv
1682: where fu.agreement_id = p_agreement_id
1683: and bv.project_id = fu.project_id
1684: and bv.agreement_id = p_agreement_id);
1685: BEGIN
1749: select 'Y' from dual
1750: where exists (
1751: select 'x'
1752: from pa_summary_project_fundings fu,
1753: pa_budget_versions bv
1754: where fu.agreement_id = p_agreement_id
1755: and bv.project_id = fu.project_id
1756: and bv.agreement_id = p_agreement_id);
1757: BEGIN
1809: -- default ci from the current working version.
1810: ===============================================================================*/
1811:
1812: PROCEDURE Is_Create_CI_Version_Allowed
1813: ( p_project_id IN pa_budget_versions.project_id%TYPE
1814: ,p_fin_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE
1815: ,p_version_type IN pa_budget_versions.version_type%TYPE
1816: ,p_impacted_task_id IN pa_tasks.task_id%TYPE
1817: ,x_version_allowed_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1811:
1812: PROCEDURE Is_Create_CI_Version_Allowed
1813: ( p_project_id IN pa_budget_versions.project_id%TYPE
1814: ,p_fin_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE
1815: ,p_version_type IN pa_budget_versions.version_type%TYPE
1816: ,p_impacted_task_id IN pa_tasks.task_id%TYPE
1817: ,x_version_allowed_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1818: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1819: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1842:
1843: -- jwhite: Added for Plannable Task Dev Effort ------------------
1844: -- 01-JUL-2003 Default ci from current working version
1845: l_ci_apprv_bv_option_id pa_proj_fp_options.proj_fp_options_id%TYPE :=NULL;
1846: l_ci_apprv_cw_bv_id pa_budget_versions.budget_version_id%TYPE :=NULL;
1847:
1848: -- ---------------------------------------------------------------------------
1849:
1850:
2351: END IsFpAutoBaselineEnabled;
2352:
2353: /*
2354: This API returns budget version information
2355: from the pa budget versions table and
2356: also returns information regarding project
2357: currency to show on the OA Page
2358: */
2359: PROCEDURE GET_BUDGET_VERSION_INFO
2408: x_approved_rev_flag,
2409: x_fin_plan_type_id,
2410: x_plan_type_name,
2411: x_plan_class_code
2412: FROM pa_budget_versions bv, pa_fin_plan_types_tl patl,pa_fin_plan_types_b pftb
2413: WHERE bv.budget_version_id = p_budget_version_id
2414: and bv.project_id = p_project_id
2415: and bv.fin_plan_type_id = patl.fin_plan_type_id
2416: and patl.fin_plan_type_id = pftb.fin_plan_type_id
2488:
2489: PROCEDURE CHK_APRV_CUR_WORKING_BV_EXISTS
2490: ( p_project_id IN pa_projects_all.project_id%TYPE
2491: ,p_fin_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE
2492: ,p_version_type IN pa_budget_versions.version_type%TYPE
2493: ,x_cur_work_bv_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE --File.Sql.39 bug 4440895
2494: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2495: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2496: ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
2489: PROCEDURE CHK_APRV_CUR_WORKING_BV_EXISTS
2490: ( p_project_id IN pa_projects_all.project_id%TYPE
2491: ,p_fin_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE
2492: ,p_version_type IN pa_budget_versions.version_type%TYPE
2493: ,x_cur_work_bv_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE --File.Sql.39 bug 4440895
2494: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2495: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2496: ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
2497:
2505: l_msg_name fnd_new_messages.message_name%TYPE;
2506: --Bug 5845142. Exclude COST impacts with cost and revenue together setup.
2507: cursor c1 is
2508: Select budget_version_id
2509: from pa_budget_versions bv,
2510: pa_proj_fp_options pfo
2511: where bv.project_id = p_project_id
2512: and bv.fin_plan_type_id = p_fin_plan_type_id
2513: and bv.version_type = p_version_type
2621:
2622: --In FP M merge is not possible only when the source time phasing is PA and target time phasing is GL or vice-versa
2623: --IN other cases merge will be possible. Modified the API for this change
2624: PROCEDURE COMPARE_SOURCE_TARGET_VER_ATTR
2625: ( p_source_bv_id IN pa_budget_versions.budget_version_id%TYPE
2626: ,p_target_bv_id IN pa_budget_versions.budget_version_id%TYPE
2627: ,x_attributes_same_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2628: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2629: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2622: --In FP M merge is not possible only when the source time phasing is PA and target time phasing is GL or vice-versa
2623: --IN other cases merge will be possible. Modified the API for this change
2624: PROCEDURE COMPARE_SOURCE_TARGET_VER_ATTR
2625: ( p_source_bv_id IN pa_budget_versions.budget_version_id%TYPE
2626: ,p_target_bv_id IN pa_budget_versions.budget_version_id%TYPE
2627: ,x_attributes_same_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2628: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2629: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2630: ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
2726: SELECT
2727: count(*)
2728: INTO
2729: x_no_of_bv_versions
2730: FROM pa_budget_versions bv
2731: WHERE bv.version_name = p_budget_version_name
2732: and bv.project_id = p_project_id
2733: and bv.fin_plan_type_id = p_fin_plan_type_id
2734: and bv.version_type = p_version_type;
2737: SELECT
2738: bv.budget_version_id
2739: INTO
2740: x_budget_version_id
2741: FROM pa_budget_versions bv
2742: WHERE bv.version_name = p_budget_version_name
2743: and bv.project_id = p_project_id
2744: and bv.fin_plan_type_id = p_fin_plan_type_id
2745: and bv.version_type = p_version_type;
2775: PROCEDURE FP_CI_CHECK_COPY_POSSIBLE
2776: ( p_source_plan_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE
2777: ,p_source_time_phased_code IN pa_proj_fp_options.all_time_phased_code%TYPE
2778: ,p_source_resource_list_id IN pa_proj_fp_options.all_resource_list_id%TYPE
2779: ,p_source_version_type IN pa_budget_versions.version_type%TYPE
2780: ,p_project_id IN pa_budget_versions.project_id%TYPE
2781: ,p_s_ci_id IN pa_budget_versions.ci_id%TYPE
2782: ,p_multiple_plan_types_flag IN VARCHAR2
2783: ,x_copy_possible_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2776: ( p_source_plan_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE
2777: ,p_source_time_phased_code IN pa_proj_fp_options.all_time_phased_code%TYPE
2778: ,p_source_resource_list_id IN pa_proj_fp_options.all_resource_list_id%TYPE
2779: ,p_source_version_type IN pa_budget_versions.version_type%TYPE
2780: ,p_project_id IN pa_budget_versions.project_id%TYPE
2781: ,p_s_ci_id IN pa_budget_versions.ci_id%TYPE
2782: ,p_multiple_plan_types_flag IN VARCHAR2
2783: ,x_copy_possible_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2784: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2777: ,p_source_time_phased_code IN pa_proj_fp_options.all_time_phased_code%TYPE
2778: ,p_source_resource_list_id IN pa_proj_fp_options.all_resource_list_id%TYPE
2779: ,p_source_version_type IN pa_budget_versions.version_type%TYPE
2780: ,p_project_id IN pa_budget_versions.project_id%TYPE
2781: ,p_s_ci_id IN pa_budget_versions.ci_id%TYPE
2782: ,p_multiple_plan_types_flag IN VARCHAR2
2783: ,x_copy_possible_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2784: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2785: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2794: l_token_ci_id varchar2(150);
2795: l_token_v_type VARCHAR2(30);
2796: l_time_phase_code_flag VARCHAR2(1);
2797: l_baseline_funding_Flag pa_projects_All.baseline_funding_flag%type;
2798: l_s_rev_bv_id pa_budget_versions.budget_version_id%type;
2799: l_count number;
2800: l_s_bv_fp_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
2801: l_ci_number pa_control_items.ci_number%type;
2802: l_ci_type_name pa_ci_types_tl.short_name%type;
2803: l_s_bv_time_phased_code pa_proj_fp_options.revenue_time_phased_code%type;
2804: l_s_bv_resource_list_id pa_proj_fp_options.revenue_resource_list_id%type;
2805:
2806: l_ci_aprv_bv_option_id pa_proj_fp_options.proj_fp_options_id%TYPE;
2807: l_ci_aprv_cw_bv_id pa_budget_versions.budget_version_id%TYPE;
2808: l_ci_aprv_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
2809: l_return_status VARCHAR2(1) := NULL;
2810: l_msg_count NUMBER := NULL;
2811: l_msg_data VARCHAR2(2000) := NULL;
3027: l_s_bv_fp_level_code,
3028: l_s_bv_time_phased_code,
3029: l_s_bv_resource_list_id
3030: FROM pa_proj_fp_options po,
3031: pa_budget_versions bv
3032: WHERE
3033: bv.project_id = p_project_id and
3034: bv.ci_id = p_s_ci_id and
3035: bv.version_type = 'REVENUE' and
3178: SELECT
3179: count(*)
3180: INTO
3181: l_no_of_bv_versions
3182: FROM pa_budget_versions bv
3183: WHERE bv.project_id = p_project_id
3184: and bv.ci_id = p_ci_id;
3185:
3186: IF (l_no_of_bv_versions = 0) THEN
3213: Plan version has the same Plan Settings as the Current Working Approved Budget.
3214: If the Plan settings are different, then appropriate error messages are raised. */
3215:
3216: PROCEDURE Fp_Ci_Impact_Submit_Chk
3217: ( p_project_id IN pa_budget_versions.project_id%TYPE
3218: ,p_ci_id IN pa_budget_versions.ci_id%TYPE
3219: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3220: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3221: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3214: If the Plan settings are different, then appropriate error messages are raised. */
3215:
3216: PROCEDURE Fp_Ci_Impact_Submit_Chk
3217: ( p_project_id IN pa_budget_versions.project_id%TYPE
3218: ,p_ci_id IN pa_budget_versions.ci_id%TYPE
3219: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3220: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3221: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3222: IS
3224: CURSOR cur_ci_version_dtl IS
3225: SELECT budget_version_id
3226: ,version_type
3227: ,fin_plan_type_id
3228: FROM pa_budget_versions
3229: WHERE project_id = p_project_id AND
3230: nvl(ci_id,-1) = p_ci_id;
3231:
3232: l_ci_version_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
3238:
3239: --Added by Xin Liu. For enhancement.
3240: l_source_id_tbl SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
3241:
3242: l_target_version_id pa_budget_versions.budget_version_id%TYPE;
3243: l_source_version_id pa_budget_versions.budget_version_id%TYPE;
3244: --Done by Xin Liu for enhancement
3245:
3246: l_msg_count NUMBER := 0;
3239: --Added by Xin Liu. For enhancement.
3240: l_source_id_tbl SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
3241:
3242: l_target_version_id pa_budget_versions.budget_version_id%TYPE;
3243: l_source_version_id pa_budget_versions.budget_version_id%TYPE;
3244: --Done by Xin Liu for enhancement
3245:
3246: l_msg_count NUMBER := 0;
3247: l_data VARCHAR2(2000);
3415: ==================================================================*/
3416: --Bug 3550073. Included x_upd_cost_impact_allowed and x_upd_rev_impact_allowed
3417: PROCEDURE FP_CI_VALIDATE_UPDATE_IMPACT
3418: (
3419: p_project_id IN pa_budget_versions.project_id%TYPE,
3420: p_ci_id IN pa_control_items.ci_id%TYPE,
3421: p_source_version_id IN pa_budget_versions.budget_version_id%TYPE,
3422: p_target_version_id IN pa_budget_versions.budget_version_id%TYPE,
3423: x_upd_cost_impact_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3417: PROCEDURE FP_CI_VALIDATE_UPDATE_IMPACT
3418: (
3419: p_project_id IN pa_budget_versions.project_id%TYPE,
3420: p_ci_id IN pa_control_items.ci_id%TYPE,
3421: p_source_version_id IN pa_budget_versions.budget_version_id%TYPE,
3422: p_target_version_id IN pa_budget_versions.budget_version_id%TYPE,
3423: x_upd_cost_impact_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3424: x_upd_rev_impact_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3425: x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3418: (
3419: p_project_id IN pa_budget_versions.project_id%TYPE,
3420: p_ci_id IN pa_control_items.ci_id%TYPE,
3421: p_source_version_id IN pa_budget_versions.budget_version_id%TYPE,
3422: p_target_version_id IN pa_budget_versions.budget_version_id%TYPE,
3423: x_upd_cost_impact_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3424: x_upd_rev_impact_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3425: x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3426: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3433: l_msg_data VARCHAR2(2000);
3434: l_msg_index_out NUMBER;
3435: l_debug_mode VARCHAR2(1);
3436:
3437: l_approved_cost_flag pa_budget_versions.approved_cost_plan_type_flag%TYPE;
3438: l_approved_rev_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
3439: l_ci_id pa_budget_versions.ci_id%TYPE;
3440: l_second_bv_id pa_budget_versions.budget_version_id%TYPE;
3441:
3434: l_msg_index_out NUMBER;
3435: l_debug_mode VARCHAR2(1);
3436:
3437: l_approved_cost_flag pa_budget_versions.approved_cost_plan_type_flag%TYPE;
3438: l_approved_rev_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
3439: l_ci_id pa_budget_versions.ci_id%TYPE;
3440: l_second_bv_id pa_budget_versions.budget_version_id%TYPE;
3441:
3442: l_count NUMBER := 0;
3435: l_debug_mode VARCHAR2(1);
3436:
3437: l_approved_cost_flag pa_budget_versions.approved_cost_plan_type_flag%TYPE;
3438: l_approved_rev_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
3439: l_ci_id pa_budget_versions.ci_id%TYPE;
3440: l_second_bv_id pa_budget_versions.budget_version_id%TYPE;
3441:
3442: l_count NUMBER := 0;
3443: l_merged_count NUMBER := 0;
3436:
3437: l_approved_cost_flag pa_budget_versions.approved_cost_plan_type_flag%TYPE;
3438: l_approved_rev_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
3439: l_ci_id pa_budget_versions.ci_id%TYPE;
3440: l_second_bv_id pa_budget_versions.budget_version_id%TYPE;
3441:
3442: l_count NUMBER := 0;
3443: l_merged_count NUMBER := 0;
3444: l_module_name VARCHAR2(100) := 'pa.plsql.Pa_Fp_Control_Items_Utils';
3500: NVL(approved_rev_plan_type_flag,'N')
3501: INTO
3502: l_approved_cost_flag,
3503: l_approved_rev_flag
3504: FROM pa_budget_versions
3505: WHERE budget_version_id = p_target_version_id
3506: AND project_id = p_project_id;
3507:
3508: x_upd_cost_impact_allowed:='N';
3511: IF(l_approved_cost_flag = 'Y' OR l_approved_rev_flag = 'Y') THEN
3512: IF p_ci_id IS NULL THEN
3513: SELECT ci_id
3514: INTO l_ci_id
3515: FROM pa_budget_versions
3516: WHERE budget_version_id = p_source_version_id
3517: AND project_id = p_project_id;
3518: ELSE
3519: l_ci_id:=p_ci_id;
3608: END FP_CI_VALIDATE_UPDATE_IMPACT;
3609:
3610: procedure chk_res_resgrp_mismatch(
3611: p_project_id in number,
3612: p_s_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
3613: p_s_fin_plan_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE,
3614: p_t_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
3615: p_t_fin_plan_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE,
3616: p_calling_mode in varchar2,
3610: procedure chk_res_resgrp_mismatch(
3611: p_project_id in number,
3612: p_s_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
3613: p_s_fin_plan_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE,
3614: p_t_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
3615: p_t_fin_plan_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE,
3616: p_calling_mode in varchar2,
3617: x_res_resgr_mismatch_flag OUT NOCOPY varchar2, --File.Sql.39 bug 4440895
3618: x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3653: l_prj_rlm_id_target NUMBER;
3654: l_target_prj_plan_level varchar2(1);
3655: l_source_fp_opt_id NUMBER;
3656: l_target_fp_opt_id NUMBER;
3657: l_source_ver_type pa_budget_versions.version_type%type;
3658: l_target_ver_type pa_budget_versions.version_type%type;
3659: l_top_task_plan_level pa_fp_elements.top_task_planning_level%type;
3660: BEGIN
3661: l_count := 0;
3654: l_target_prj_plan_level varchar2(1);
3655: l_source_fp_opt_id NUMBER;
3656: l_target_fp_opt_id NUMBER;
3657: l_source_ver_type pa_budget_versions.version_type%type;
3658: l_target_ver_type pa_budget_versions.version_type%type;
3659: l_top_task_plan_level pa_fp_elements.top_task_planning_level%type;
3660: BEGIN
3661: l_count := 0;
3662: x_msg_count := 0;
3668: bv.version_type into
3669: l_source_fp_opt_id,
3670: l_source_ver_type
3671: from pa_proj_fp_options o,
3672: pa_budget_versions bv
3673: where
3674: bv.budget_version_id = p_s_budget_version_id and
3675: bv.fin_plan_type_id = o.fin_plan_type_id and
3676: o.project_id = p_project_id and
3681: bv.version_type into
3682: l_target_fp_opt_id,
3683: l_target_ver_type
3684: from pa_proj_fp_options o,
3685: pa_budget_versions bv
3686: where
3687: bv.budget_version_id = p_t_budget_version_id and
3688: bv.fin_plan_type_id = o.fin_plan_type_id and
3689: o.project_id = p_project_id and
4001: END chk_res_resgrp_mismatch;
4002:
4003: procedure chk_tsk_plan_level_mismatch(
4004: p_project_id in number,
4005: p_s_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
4006: p_t_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
4007: p_calling_mode in varchar2,
4008: x_tsk_plan_level_mismatch OUT NOCOPY varchar2, --File.Sql.39 bug 4440895
4009: x_s_task_id_tbl OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
4002:
4003: procedure chk_tsk_plan_level_mismatch(
4004: p_project_id in number,
4005: p_s_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
4006: p_t_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
4007: p_calling_mode in varchar2,
4008: x_tsk_plan_level_mismatch OUT NOCOPY varchar2, --File.Sql.39 bug 4440895
4009: x_s_task_id_tbl OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
4010: x_t_task_id_tbl OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
4049: l_prj_rlm_id_target NUMBER;
4050: l_target_prj_plan_level varchar2(1);
4051: l_source_fp_opt_id NUMBER;
4052: l_target_fp_opt_id NUMBER;
4053: l_source_ver_type pa_budget_versions.version_type%type;
4054: l_target_ver_type pa_budget_versions.version_type%type;
4055: l_top_task_plan_level pa_fp_elements.top_task_planning_level%type;
4056: l_source_task_id number;
4057: l_source_plan_level varchar2(30);
4050: l_target_prj_plan_level varchar2(1);
4051: l_source_fp_opt_id NUMBER;
4052: l_target_fp_opt_id NUMBER;
4053: l_source_ver_type pa_budget_versions.version_type%type;
4054: l_target_ver_type pa_budget_versions.version_type%type;
4055: l_top_task_plan_level pa_fp_elements.top_task_planning_level%type;
4056: l_source_task_id number;
4057: l_source_plan_level varchar2(30);
4058: l_target_plan_level varchar2(30);
4080: bv.version_type into
4081: l_source_fp_opt_id,
4082: l_source_ver_type
4083: from pa_proj_fp_options o,
4084: pa_budget_versions bv
4085: where
4086: bv.budget_version_id = p_s_budget_version_id and
4087: bv.fin_plan_type_id = o.fin_plan_type_id and
4088: o.project_id = p_project_id and
4099: bv.version_type into
4100: l_target_fp_opt_id,
4101: l_target_ver_type
4102: from pa_proj_fp_options o,
4103: pa_budget_versions bv
4104: where
4105: bv.budget_version_id = p_t_budget_version_id and
4106: bv.fin_plan_type_id = o.fin_plan_type_id and
4107: o.project_id = p_project_id and
4406:
4407:
4408: PROCEDURE get_summary_data
4409: ( p_project_id IN NUMBER
4410: ,p_cost_version_id IN pa_budget_versions.budget_version_id%TYPE
4411: ,p_revenue_version_id IN pa_budget_versions.budget_version_id%TYPE
4412: ,p_page_context IN VARCHAR2
4413: ,p_calling_mode IN VARCHAR2 DEFAULT 'APPROVED' --Bug 5278200 kchaitan
4414: ,x_context OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4407:
4408: PROCEDURE get_summary_data
4409: ( p_project_id IN NUMBER
4410: ,p_cost_version_id IN pa_budget_versions.budget_version_id%TYPE
4411: ,p_revenue_version_id IN pa_budget_versions.budget_version_id%TYPE
4412: ,p_page_context IN VARCHAR2
4413: ,p_calling_mode IN VARCHAR2 DEFAULT 'APPROVED' --Bug 5278200 kchaitan
4414: ,x_context OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4415: ,x_summary_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_150_TBL_TYPE --File.Sql.39 bug 4440895
4443: l_continue_flag VARCHAR2(1) := 'Y';
4444: l_row_count NUMBER := 0;
4445: l_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE;
4446: l_module_name VARCHAR2(30) := 'ctrl_itm_utls.plan_summ_data';
4447: l_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4448: l_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4449: l_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4450: l_report_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4451: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4446: l_module_name VARCHAR2(30) := 'ctrl_itm_utls.plan_summ_data';
4447: l_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4448: l_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4449: l_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4450: l_report_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4451: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4452: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4453: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4454: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4447: l_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4448: l_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4449: l_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4450: l_report_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4451: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4452: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4453: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4454: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4455:
4448: l_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4449: l_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4450: l_report_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4451: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4452: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4453: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4454: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4455:
4456: l_cb_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4449: l_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4450: l_report_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4451: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4452: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4453: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4454: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4455:
4456: l_cb_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4457: l_cb_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4450: l_report_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4451: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4452: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4453: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4454: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4455:
4456: l_cb_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4457: l_cb_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4458: l_cb_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4452: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4453: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4454: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4455:
4456: l_cb_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4457: l_cb_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4458: l_cb_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4459: l_cb_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4460:
4453: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4454: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4455:
4456: l_cb_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4457: l_cb_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4458: l_cb_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4459: l_cb_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4460:
4461: l_cw_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4454: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4455:
4456: l_cb_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4457: l_cb_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4458: l_cb_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4459: l_cb_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4460:
4461: l_cw_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4462: l_cw_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4455:
4456: l_cb_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4457: l_cb_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4458: l_cb_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4459: l_cb_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4460:
4461: l_cw_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4462: l_cw_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4463: l_cw_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4457: l_cb_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4458: l_cb_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4459: l_cb_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4460:
4461: l_cw_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4462: l_cw_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4463: l_cw_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4464: l_cw_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4465:
4458: l_cb_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4459: l_cb_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4460:
4461: l_cw_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4462: l_cw_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4463: l_cw_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4464: l_cw_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4465:
4466: l_lookup_code_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
4459: l_cb_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4460:
4461: l_cw_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4462: l_cw_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4463: l_cw_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4464: l_cw_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4465:
4466: l_lookup_code_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
4467: l_fin_plan_type_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
4460:
4461: l_cw_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4462: l_cw_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4463: l_cw_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4464: l_cw_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4465:
4466: l_lookup_code_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
4467: l_fin_plan_type_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
4468: l_fin_plan_preference_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
4475: l_set_cb_url_flag VARCHAR2(1) := 'Y';
4476: l_set_cpb_url_flag VARCHAR2(1) := 'Y';
4477: l_set_ccw_url_flag VARCHAR2(1) := 'Y';
4478: l_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4479: l_pa_cw_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4480: l_pa_cw_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4481: l_pa_ob_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4482: l_pa_ob_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4483: l_pa_cb_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4476: l_set_cpb_url_flag VARCHAR2(1) := 'Y';
4477: l_set_ccw_url_flag VARCHAR2(1) := 'Y';
4478: l_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4479: l_pa_cw_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4480: l_pa_cw_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4481: l_pa_ob_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4482: l_pa_ob_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4483: l_pa_cb_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4484: l_pa_cb_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4477: l_set_ccw_url_flag VARCHAR2(1) := 'Y';
4478: l_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4479: l_pa_cw_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4480: l_pa_cw_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4481: l_pa_ob_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4482: l_pa_ob_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4483: l_pa_cb_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4484: l_pa_cb_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4485: l_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4478: l_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4479: l_pa_cw_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4480: l_pa_cw_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4481: l_pa_ob_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4482: l_pa_ob_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4483: l_pa_cb_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4484: l_pa_cb_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4485: l_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4486: l_version_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
4479: l_pa_cw_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4480: l_pa_cw_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4481: l_pa_ob_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4482: l_pa_ob_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4483: l_pa_cb_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4484: l_pa_cb_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4485: l_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4486: l_version_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
4487: l_budget_version_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
4480: l_pa_cw_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4481: l_pa_ob_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4482: l_pa_ob_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4483: l_pa_cb_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4484: l_pa_cb_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4485: l_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4486: l_version_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
4487: l_budget_version_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
4488: l_next VARCHAR2(30);
4481: l_pa_ob_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4482: l_pa_ob_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4483: l_pa_cb_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4484: l_pa_cb_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4485: l_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4486: l_version_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
4487: l_budget_version_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
4488: l_next VARCHAR2(30);
4489: l_context VARCHAR2(30);
4487: l_budget_version_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
4488: l_next VARCHAR2(30);
4489: l_context VARCHAR2(30);
4490:
4491: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4492:
4493: l_report_labor_hrs_code pa_proj_fp_options.report_labor_hrs_from_code%TYPE; /* Bug 4038253 */
4494: l_pref_code_for_pt_of_ver pa_proj_fp_options.fin_plan_preference_code%TYPE; /* Bug 4038253 */
4495:
4521: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4522: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4523: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4524: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4525: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS
4526: SELECT nvl(sum(decode(pbv.version_type,
4527: c_report_version_type,
4528: nvl(pbv.labor_quantity,0),0)),0),
4529: nvl(sum(decode(pbv.version_type,
4536: nvl(pbv.total_project_raw_cost,0))
4537: ,0)),0) as cost,
4538: nvl(sum(decode(pbv.fin_plan_type_id,
4539: c_ar_fin_plan_type_id,nvl(pbv.total_project_revenue,0),0)),0)
4540: FROM pa_budget_versions pbv
4541: WHERE pbv.project_id = c_project_id
4542: AND pbv.ci_id is null
4543: AND nvl(pbv.current_original_flag,'N') = 'Y'
4544: --Below 2 lines commented for bug 5278200
4551: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4552: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4553: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4554: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4555: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS
4556: SELECT nvl(sum(decode(pbv.version_type,
4557: c_report_version_type,
4558: nvl(pbv.labor_quantity,0),0)),0),
4559: nvl(sum(decode(pbv.version_type,
4567: ,0)),0) as cost,
4568: nvl(sum(decode(pbv.fin_plan_type_id,
4569: c_ar_fin_plan_type_id,
4570: nvl(pbv.total_project_revenue,0),0)),0)
4571: FROM pa_budget_versions pbv
4572: WHERE pbv.project_id = c_project_id
4573: AND pbv.ci_id is null
4574: AND nvl(pbv.current_flag,'N') = 'Y'
4575: --Below 2 lines commented for bug 5278200
4582: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4583: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4584: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4585: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4586: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS
4587: SELECT nvl(sum(decode(pbv.version_type,
4588: c_report_version_type,
4589: nvl(pbv.labor_quantity,0),0)),0),
4590: nvl(sum(decode(pbv.version_type,
4597: nvl(pbv.total_project_raw_cost,0)),0)),0) as cost,
4598: nvl(sum(decode(pbv.fin_plan_type_id,
4599: c_ar_fin_plan_type_id,
4600: nvl(pbv.total_project_revenue,0),0)),0)
4601: FROM pa_budget_versions pbv
4602: WHERE pbv.project_id = c_project_id
4603: AND pbv.ci_id is null
4604: AND nvl(pbv.current_working_flag,'N') = 'Y'
4605: --Below 2 lines commented for bug 5278200
4620: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4621: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4622: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4623: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4624: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS -- Raja review
4625: Select nvl(sum(decode(c_report_version_type,
4626: 'COST', decode(merge.version_type, 'COST',nvl(merge.impl_quantity,0),0),
4627: 'REVENUE', decode(merge.version_type, 'REVENUE',nvl(merge.impl_quantity,0),0),
4628: 'ALL', decode(merge.version_type, 'COST',nvl(merge.impl_quantity,0),0)
4641: 'B',nvl(merge.impl_proj_burdened_cost,0),
4642: nvl(merge.impl_proj_raw_cost,0))),0) as cost,
4643: nvl(sum(nvl(merge.impl_proj_revenue,0)),0)
4644: from pa_fp_merged_ctrl_items merge,
4645: pa_budget_versions pbv
4646: where pbv.project_id = c_project_id
4647: and pbv.fin_plan_type_id in (c_ac_fin_plan_type_id, c_ar_fin_plan_type_id)
4648: and pbv.current_working_flag = 'Y'
4649: and merge.project_id = c_project_id
4651: and pbv.ci_id is null
4652: -- and pbv.budget_status_code in ('S','W') -- Bug#3815378
4653: -- Added by Raja, filter all the ci versions included/copied in current baseline version
4654: and not exists(select 1
4655: from pa_fp_merged_ctrl_items merge1, pa_budget_versions pbv1
4656: where pbv1.project_id = c_project_id
4657: and pbv1.fin_plan_type_id in (c_ac_fin_plan_type_id,
4658: c_ar_fin_plan_type_id)
4659: and pbv1.budget_status_code = 'B'
4670: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4671: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4672: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4673: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4674: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS -- Raja review
4675: Select nvl(sum(decode(c_report_version_type,
4676: 'COST', decode(merge.version_type, 'COST',nvl(merge.impl_quantity,0),0),
4677: 'REVENUE', decode(merge.version_type, 'REVENUE',nvl(merge.impl_quantity,0),0),
4678: 'ALL', decode(merge.version_type, 'COST',nvl(merge.impl_quantity,0),0)
4688: nvl(sum(decode(pbv.fin_plan_type_id,
4689: c_ar_fin_plan_type_id,nvl(merge.impl_proj_revenue,0),
4690: 0)),0)
4691: from pa_fp_merged_ctrl_items merge,
4692: pa_budget_versions pbv
4693: where pbv.project_id = c_project_id
4694: and pbv.fin_plan_type_id in (c_ac_fin_plan_type_id, c_ar_fin_plan_type_id)
4695: and pbv.current_working_flag = 'Y'
4696: and merge.project_id = c_project_id
4712: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4713: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4714: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4715: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4716: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS -- Raja review
4717: Select nvl(sum(decode(c_report_version_type,
4718: 'COST', decode(merge.version_type, 'COST',nvl(merge.impl_quantity,0),0),
4719: 'REVENUE', decode(merge.version_type, 'REVENUE',nvl(merge.impl_quantity,0),0),
4720: 'ALL', decode(merge.version_type, 'COST',nvl(merge.impl_quantity,0),0)
4734: nvl(sum(decode(pbv.fin_plan_type_id,
4735: c_ar_fin_plan_type_id,nvl(merge.impl_proj_revenue,0),
4736: 0)),0)
4737: from pa_fp_merged_ctrl_items merge,
4738: pa_budget_versions pbv
4739: where merge.plan_version_id = pbv.budget_version_id
4740: and pbv.fin_plan_type_id in (c_ac_fin_plan_type_id, c_ar_fin_plan_type_id)
4741: and pbv.current_flag = 'Y'
4742: and pbv.project_id = c_project_id
4745: and pbv.budget_status_code = 'B'
4746: and merge.inclusion_method_code = 'COPIED' -- Bug 3882985
4747: /* Raja filter all the change orders that have been included/copied in the original baseline version */
4748: and not exists(select 1
4749: from pa_fp_merged_ctrl_items merge1, pa_budget_versions pbv1
4750: where pbv1.project_id = c_project_id
4751: and pbv1.fin_plan_type_id in (c_ac_fin_plan_type_id,
4752: c_ar_fin_plan_type_id)
4753: and pbv1.current_original_flag = 'Y'
4786: CURSOR c_change_documents_status
4787: (c_project_id pa_projects_all.project_id%TYPE,
4788: c_system_status_code pa_ci_statuses_v.project_system_status_code%TYPE,
4789: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4790: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE,
4791: c_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE) IS -- Raja review
4792: SELECT (cost_query.people_effort + revenue_query.people_effort),
4793: (cost_query.equipment_effort + revenue_query.equipment_effort),
4794: (cost_query.cost + revenue_query.cost),
4787: (c_project_id pa_projects_all.project_id%TYPE,
4788: c_system_status_code pa_ci_statuses_v.project_system_status_code%TYPE,
4789: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4790: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE,
4791: c_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE) IS -- Raja review
4792: SELECT (cost_query.people_effort + revenue_query.people_effort),
4793: (cost_query.equipment_effort + revenue_query.equipment_effort),
4794: (cost_query.cost + revenue_query.cost),
4795: (cost_query.revenue + revenue_query.revenue)
4813: ,PBV.LABOR_QUANTITY AS PEOPLE_EFFORT
4814: ,PBV.EQUIPMENT_QUANTITY AS EQUIPMENT_EFFORT
4815: ,PBV.TOTAL_PROJECT_RAW_COST AS RAW_COST
4816: ,PBV.TOTAL_PROJECT_BURDENED_COST AS BURDENED_COST
4817: FROM pa_budget_versions pbv
4818: ,pa_control_items pci
4819: ,pa_project_statuses pcs
4820: ,pa_ci_types_vl pct
4821: WHERE PBV.CI_ID = PCI.CI_ID
4826: where pfca.project_id = c_project_id
4827: and pfca.project_system_status_code = c_system_status_code
4828: and pfca.ci_version_type in ('COST', 'ALL')
4829: and not exists(select 1
4830: from pa_fp_merged_ctrl_items merge1, pa_budget_versions pbv1
4831: where merge1.project_id = c_project_id
4832: and merge1.ci_id = pfca.ci_id
4833: and merge1.ci_plan_version_id = pfca.ci_version_id
4834: and merge1.version_type = 'COST'
4877: ,PBV.LABOR_QUANTITY AS PEOPLE_EFFORT
4878: ,PBV.EQUIPMENT_QUANTITY AS EQUIPMENT_EFFORT
4879: ,PBV.TOTAL_PROJECT_REVENUE AS REVENUE
4880: ,nvl(PBV.REV_PARTIALLY_IMPL_FLAG,'N') AS REV_PARTIALLY_IMPL_FLAG
4881: FROM pa_budget_versions pbv
4882: ,pa_control_items pci
4883: ,pa_project_statuses pcs
4884: ,pa_ci_types_vl pct
4885: WHERE PBV.CI_ID = PCI.CI_ID
4891: and pfca.project_system_status_code = c_system_status_code
4892: and pfca.ci_version_type in ('REVENUE', 'ALL')
4893: and (pfca.REV_PARTIALLY_IMPL_FLAG = 'Y' OR
4894: not exists(select 1
4895: from pa_fp_merged_ctrl_items merge1, pa_budget_versions pbv1
4896: where merge1.project_id = c_project_id
4897: and merge1.ci_id = pfca.ci_id
4898: and merge1.ci_plan_version_id = pfca.ci_version_id
4899: and merge1.version_type = 'REVENUE'
4904: and pbv1.approved_rev_plan_type_flag = 'Y'))) revenue_query;
4905:
4906: --Bug 5278200Added extra parameter
4907: cursor c_url_original_baseline(c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4908: c_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE,
4909: c_version_type pa_budget_versions.version_type%TYPE) IS
4910: SELECT BUDGET_VERSION_ID,VERSION_TYPE
4911: FROM PA_BUDGET_VERSIONS
4912: WHERE BUDGET_STATUS_CODE = 'B'
4905:
4906: --Bug 5278200Added extra parameter
4907: cursor c_url_original_baseline(c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4908: c_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE,
4909: c_version_type pa_budget_versions.version_type%TYPE) IS
4910: SELECT BUDGET_VERSION_ID,VERSION_TYPE
4911: FROM PA_BUDGET_VERSIONS
4912: WHERE BUDGET_STATUS_CODE = 'B'
4913: AND PROJECT_ID = c_project_id
4907: cursor c_url_original_baseline(c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4908: c_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE,
4909: c_version_type pa_budget_versions.version_type%TYPE) IS
4910: SELECT BUDGET_VERSION_ID,VERSION_TYPE
4911: FROM PA_BUDGET_VERSIONS
4912: WHERE BUDGET_STATUS_CODE = 'B'
4913: AND PROJECT_ID = c_project_id
4914: AND CI_ID IS NULL
4915: AND NVL(CURRENT_ORIGINAL_FLAG,'N') = 'Y'
4919: --AND (approved_cost_plan_type_flag='Y' or approved_rev_plan_type_flag='Y');
4920:
4921: --Bug 5278200Added extra parameter
4922: cursor c_url_current_baseline(c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4923: c_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE,
4924: c_version_type pa_budget_versions.version_type%TYPE) IS
4925: SELECT BUDGET_VERSION_ID,VERSION_TYPE
4926: FROM PA_BUDGET_VERSIONS
4927: WHERE BUDGET_STATUS_CODE = 'B'
4920:
4921: --Bug 5278200Added extra parameter
4922: cursor c_url_current_baseline(c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4923: c_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE,
4924: c_version_type pa_budget_versions.version_type%TYPE) IS
4925: SELECT BUDGET_VERSION_ID,VERSION_TYPE
4926: FROM PA_BUDGET_VERSIONS
4927: WHERE BUDGET_STATUS_CODE = 'B'
4928: AND PROJECT_ID = c_project_id
4922: cursor c_url_current_baseline(c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4923: c_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE,
4924: c_version_type pa_budget_versions.version_type%TYPE) IS
4925: SELECT BUDGET_VERSION_ID,VERSION_TYPE
4926: FROM PA_BUDGET_VERSIONS
4927: WHERE BUDGET_STATUS_CODE = 'B'
4928: AND PROJECT_ID = c_project_id
4929: AND CI_ID IS NULL
4930: AND NVL(CURRENT_FLAG,'N') = 'Y'
4934: --AND (approved_cost_plan_type_flag='Y' or approved_rev_plan_type_flag='Y');
4935:
4936: --Bug 5278200Added extra parameter
4937: cursor c_url_current_working(c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4938: c_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE,
4939: c_version_type pa_budget_versions.version_type%TYPE) IS
4940: SELECT BUDGET_VERSION_ID,VERSION_TYPE
4941: FROM PA_BUDGET_VERSIONS
4942: WHERE PROJECT_ID = c_project_id
4935:
4936: --Bug 5278200Added extra parameter
4937: cursor c_url_current_working(c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4938: c_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE,
4939: c_version_type pa_budget_versions.version_type%TYPE) IS
4940: SELECT BUDGET_VERSION_ID,VERSION_TYPE
4941: FROM PA_BUDGET_VERSIONS
4942: WHERE PROJECT_ID = c_project_id
4943: AND CI_ID IS NULL
4937: cursor c_url_current_working(c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4938: c_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE,
4939: c_version_type pa_budget_versions.version_type%TYPE) IS
4940: SELECT BUDGET_VERSION_ID,VERSION_TYPE
4941: FROM PA_BUDGET_VERSIONS
4942: WHERE PROJECT_ID = c_project_id
4943: AND CI_ID IS NULL
4944: AND NVL(CURRENT_WORKING_FLAG,'N') = 'Y'
4945: -- AND BUDGET_STATUS_CODE in ('S','W') -- Bug#3815378
5124: END IF;
5125: BEGIN
5126: SELECT A.BUDGET_VERSION_ID
5127: INTO l_appr_rev_cw_version_id
5128: FROM PA_BUDGET_VERSIONS A
5129: WHERE A.PROJECT_ID = p_project_id
5130: AND A.VERSION_TYPE IN('ALL', 'REVENUE')
5131: AND A.APPROVED_REV_PLAN_TYPE_FLAG = 'Y'
5132: AND A.CURRENT_WORKING_FLAG = 'Y'
5169:
5170: BEGIN
5171: SELECT A.BUDGET_VERSION_ID
5172: INTO l_appr_rev_cw_version_id
5173: FROM PA_BUDGET_VERSIONS A
5174: WHERE A.PROJECT_ID = p_project_id
5175: AND A.VERSION_TYPE IN('ALL', 'REVENUE')
5176: --AND A.APPROVED_REV_PLAN_TYPE_FLAG = 'Y'
5177: AND A.FIN_PLAN_TYPE_ID IN(select FIN_PLAN_TYPE_ID from
5174: WHERE A.PROJECT_ID = p_project_id
5175: AND A.VERSION_TYPE IN('ALL', 'REVENUE')
5176: --AND A.APPROVED_REV_PLAN_TYPE_FLAG = 'Y'
5177: AND A.FIN_PLAN_TYPE_ID IN(select FIN_PLAN_TYPE_ID from
5178: PA_BUDGET_VERSIONS where
5179: budget_version_id in
5180: (p_cost_version_id,p_revenue_version_id))
5181: AND A.CURRENT_WORKING_FLAG = 'Y'
5182: AND A.CI_ID IS NULL;
5209: FROM pa_proj_fp_options
5210: WHERE project_id = p_project_id
5211: AND fin_plan_option_level_code = 'PLAN_TYPE'
5212: AND fin_plan_type_id in(select FIN_PLAN_TYPE_ID from
5213: PA_BUDGET_VERSIONS where
5214: budget_version_id in (p_cost_version_id,p_revenue_version_id));
5215: l_appr_cost_plan_type_flag_tbl(1):='Y';
5216: l_appr_rev_plan_type_flag_tbl(1):='Y';
5217:
6049: -- Note: In some cases, p_pt_ct_version_type is passed as null in this case
6050: -- values are returned without bothering about double count.
6051: --------------------------------------------------------------------------------
6052: FUNCTION get_labor_qty_partial(
6053: p_version_type IN pa_budget_versions.version_type%TYPE, -- This is the CI version type
6054: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6055: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6056: p_labor_qty IN pa_budget_versions.labor_quantity%TYPE DEFAULT NULL, -- CI qty
6057: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6050: -- values are returned without bothering about double count.
6051: --------------------------------------------------------------------------------
6052: FUNCTION get_labor_qty_partial(
6053: p_version_type IN pa_budget_versions.version_type%TYPE, -- This is the CI version type
6054: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6055: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6056: p_labor_qty IN pa_budget_versions.labor_quantity%TYPE DEFAULT NULL, -- CI qty
6057: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6058: )
6051: --------------------------------------------------------------------------------
6052: FUNCTION get_labor_qty_partial(
6053: p_version_type IN pa_budget_versions.version_type%TYPE, -- This is the CI version type
6054: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6055: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6056: p_labor_qty IN pa_budget_versions.labor_quantity%TYPE DEFAULT NULL, -- CI qty
6057: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6058: )
6059: RETURN NUMBER
6052: FUNCTION get_labor_qty_partial(
6053: p_version_type IN pa_budget_versions.version_type%TYPE, -- This is the CI version type
6054: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6055: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6056: p_labor_qty IN pa_budget_versions.labor_quantity%TYPE DEFAULT NULL, -- CI qty
6057: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6058: )
6059: RETURN NUMBER
6060: IS
6059: RETURN NUMBER
6060: IS
6061: l_debug_mode VARCHAR2(30);
6062: l_module_name VARCHAR2(30) := 'ctrl_utils.lab_qty_prtial';
6063: l_source_version_type pa_budget_versions.version_type%TYPE;
6064: l_target_version_type pa_budget_versions.version_type%TYPE;
6065: l_impl_qty_exists VARCHAR2(1) := 'N';
6066: l_revenue_partial_flag varchar2(1) := 'N';
6067: l_return_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6060: IS
6061: l_debug_mode VARCHAR2(30);
6062: l_module_name VARCHAR2(30) := 'ctrl_utils.lab_qty_prtial';
6063: l_source_version_type pa_budget_versions.version_type%TYPE;
6064: l_target_version_type pa_budget_versions.version_type%TYPE;
6065: l_impl_qty_exists VARCHAR2(1) := 'N';
6066: l_revenue_partial_flag varchar2(1) := 'N';
6067: l_return_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6068: l_partial_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6063: l_source_version_type pa_budget_versions.version_type%TYPE;
6064: l_target_version_type pa_budget_versions.version_type%TYPE;
6065: l_impl_qty_exists VARCHAR2(1) := 'N';
6066: l_revenue_partial_flag varchar2(1) := 'N';
6067: l_return_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6068: l_partial_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6069: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6070: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6071: BEGIN
6064: l_target_version_type pa_budget_versions.version_type%TYPE;
6065: l_impl_qty_exists VARCHAR2(1) := 'N';
6066: l_revenue_partial_flag varchar2(1) := 'N';
6067: l_return_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6068: l_partial_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6069: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6070: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6071: BEGIN
6072: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
6065: l_impl_qty_exists VARCHAR2(1) := 'N';
6066: l_revenue_partial_flag varchar2(1) := 'N';
6067: l_return_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6068: l_partial_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6069: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6070: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6071: BEGIN
6072: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
6073: IF l_debug_mode = 'Y' THEN
6066: l_revenue_partial_flag varchar2(1) := 'N';
6067: l_return_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6068: l_partial_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6069: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6070: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6071: BEGIN
6072: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
6073: IF l_debug_mode = 'Y' THEN
6074: pa_debug.g_err_stage:='In get_labor_qty_partial - pa_fp_control_items_utils ';
6085: END IF;
6086: BEGIN
6087: SELECT VERSION_TYPE
6088: INTO l_source_version_type
6089: FROM PA_BUDGET_VERSIONS
6090: WHERE BUDGET_VERSION_ID = p_ci_version_id;
6091: EXCEPTION
6092: WHEN NO_DATA_FOUND THEN
6093: IF l_debug_mode = 'Y' THEN
6109: END IF;
6110: BEGIN
6111: SELECT VERSION_TYPE
6112: INTO l_target_version_type
6113: FROM PA_BUDGET_VERSIONS
6114: WHERE BUDGET_VERSION_ID = p_budget_version_id;
6115: EXCEPTION
6116: WHEN NO_DATA_FOUND THEN
6117: IF l_debug_mode = 'Y' THEN
6131: END IF;
6132: BEGIN
6133: SELECT labor_quantity
6134: INTO l_labor_quantity
6135: FROM PA_BUDGET_VERSIONS
6136: WHERE BUDGET_VERSION_ID = p_ci_version_id;
6137: EXCEPTION
6138: WHEN NO_DATA_FOUND THEN
6139: IF l_debug_mode = 'Y' THEN
6205: IF l_impl_qty_exists = 'Y' THEN
6206: BEGIN
6207: SELECT NVL(B.REV_PARTIALLY_IMPL_FLAG,'N') , A.IMPL_QUANTITY
6208: INTO l_revenue_partial_flag, l_partial_quantity
6209: FROM PA_FP_MERGED_CTRL_ITEMS A , PA_BUDGET_VERSIONS B
6210: WHERE A.CI_PLAN_VERSION_ID = p_ci_version_id
6211: AND A.PLAN_VERSION_ID = p_budget_version_id
6212: AND A.VERSION_TYPE = 'REVENUE'
6213: AND B.BUDGET_VERSION_ID = A.CI_PLAN_VERSION_ID;
6232: END IF;
6233: BEGIN
6234: SELECT A.BUDGET_VERSION_ID
6235: INTO l_appr_rev_cw_version_id
6236: FROM PA_BUDGET_VERSIONS A
6237: WHERE A.PROJECT_ID = (SELECT B.PROJECT_ID FROM PA_BUDGET_VERSIONS B
6238: WHERE B.BUDGET_VERSION_ID = p_budget_version_id)
6239: AND A.VERSION_TYPE IN ('REVENUE', 'ALL')
6240: -- Raja review A.VERSION_TYPE = 'REVENUE'
6233: BEGIN
6234: SELECT A.BUDGET_VERSION_ID
6235: INTO l_appr_rev_cw_version_id
6236: FROM PA_BUDGET_VERSIONS A
6237: WHERE A.PROJECT_ID = (SELECT B.PROJECT_ID FROM PA_BUDGET_VERSIONS B
6238: WHERE B.BUDGET_VERSION_ID = p_budget_version_id)
6239: AND A.VERSION_TYPE IN ('REVENUE', 'ALL')
6240: -- Raja review A.VERSION_TYPE = 'REVENUE'
6241: AND A.APPROVED_REV_PLAN_TYPE_FLAG = 'Y'
6317: IF l_impl_qty_exists = 'Y' THEN
6318: BEGIN
6319: SELECT NVL(B.REV_PARTIALLY_IMPL_FLAG,'N') , A.IMPL_QUANTITY
6320: INTO l_revenue_partial_flag, l_partial_quantity
6321: FROM PA_FP_MERGED_CTRL_ITEMS A , PA_BUDGET_VERSIONS B
6322: WHERE A.CI_PLAN_VERSION_ID = p_ci_version_id
6323: AND A.PLAN_VERSION_ID = p_budget_version_id
6324: AND A.VERSION_TYPE = 'REVENUE'
6325: AND B.BUDGET_VERSION_ID = A.CI_PLAN_VERSION_ID;
6359:
6360: --------------------------------------------------------------------------------
6361:
6362: FUNCTION get_equip_qty_partial(
6363: p_version_type IN pa_budget_versions.version_type%TYPE, -- This is the CI version type
6364: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6365: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6366: p_equip_qty IN pa_budget_versions.equipment_quantity%TYPE DEFAULT NULL, -- CI qty
6367: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6360: --------------------------------------------------------------------------------
6361:
6362: FUNCTION get_equip_qty_partial(
6363: p_version_type IN pa_budget_versions.version_type%TYPE, -- This is the CI version type
6364: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6365: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6366: p_equip_qty IN pa_budget_versions.equipment_quantity%TYPE DEFAULT NULL, -- CI qty
6367: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6368: )
6361:
6362: FUNCTION get_equip_qty_partial(
6363: p_version_type IN pa_budget_versions.version_type%TYPE, -- This is the CI version type
6364: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6365: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6366: p_equip_qty IN pa_budget_versions.equipment_quantity%TYPE DEFAULT NULL, -- CI qty
6367: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6368: )
6369: RETURN NUMBER
6362: FUNCTION get_equip_qty_partial(
6363: p_version_type IN pa_budget_versions.version_type%TYPE, -- This is the CI version type
6364: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6365: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6366: p_equip_qty IN pa_budget_versions.equipment_quantity%TYPE DEFAULT NULL, -- CI qty
6367: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6368: )
6369: RETURN NUMBER
6370: IS
6369: RETURN NUMBER
6370: IS
6371: l_debug_mode VARCHAR2(30);
6372: l_module_name VARCHAR2(30) := 'ctrl_utils.equip_qty_prtial';
6373: l_source_version_type pa_budget_versions.version_type%TYPE;
6374: l_target_version_type pa_budget_versions.version_type%TYPE;
6375: l_impl_qty_exists VARCHAR2(1) := 'N';
6376: l_revenue_partial_flag varchar2(1) := 'N';
6377: l_return_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6370: IS
6371: l_debug_mode VARCHAR2(30);
6372: l_module_name VARCHAR2(30) := 'ctrl_utils.equip_qty_prtial';
6373: l_source_version_type pa_budget_versions.version_type%TYPE;
6374: l_target_version_type pa_budget_versions.version_type%TYPE;
6375: l_impl_qty_exists VARCHAR2(1) := 'N';
6376: l_revenue_partial_flag varchar2(1) := 'N';
6377: l_return_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6378: l_partial_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6373: l_source_version_type pa_budget_versions.version_type%TYPE;
6374: l_target_version_type pa_budget_versions.version_type%TYPE;
6375: l_impl_qty_exists VARCHAR2(1) := 'N';
6376: l_revenue_partial_flag varchar2(1) := 'N';
6377: l_return_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6378: l_partial_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6379: l_equip_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6380: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6381: BEGIN
6374: l_target_version_type pa_budget_versions.version_type%TYPE;
6375: l_impl_qty_exists VARCHAR2(1) := 'N';
6376: l_revenue_partial_flag varchar2(1) := 'N';
6377: l_return_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6378: l_partial_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6379: l_equip_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6380: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6381: BEGIN
6382: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
6375: l_impl_qty_exists VARCHAR2(1) := 'N';
6376: l_revenue_partial_flag varchar2(1) := 'N';
6377: l_return_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6378: l_partial_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6379: l_equip_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6380: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6381: BEGIN
6382: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
6383: IF l_debug_mode = 'Y' THEN
6376: l_revenue_partial_flag varchar2(1) := 'N';
6377: l_return_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6378: l_partial_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6379: l_equip_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6380: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6381: BEGIN
6382: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
6383: IF l_debug_mode = 'Y' THEN
6384: pa_debug.g_err_stage:='In get_equip_qty_partial - pa_fp_control_items_utils ';
6395: END IF;
6396: BEGIN
6397: SELECT VERSION_TYPE
6398: INTO l_source_version_type
6399: FROM PA_BUDGET_VERSIONS
6400: WHERE BUDGET_VERSION_ID = p_ci_version_id;
6401: EXCEPTION
6402: WHEN NO_DATA_FOUND THEN
6403: IF l_debug_mode = 'Y' THEN
6419: END IF;
6420: BEGIN
6421: SELECT VERSION_TYPE
6422: INTO l_target_version_type
6423: FROM PA_BUDGET_VERSIONS
6424: WHERE BUDGET_VERSION_ID = p_budget_version_id;
6425: EXCEPTION
6426: WHEN NO_DATA_FOUND THEN
6427: IF l_debug_mode = 'Y' THEN
6441: END IF;
6442: BEGIN
6443: SELECT EQUIPMENT_QUANTITY
6444: INTO l_equip_quantity
6445: FROM PA_BUDGET_VERSIONS
6446: WHERE BUDGET_VERSION_ID = p_ci_version_id;
6447: EXCEPTION
6448: WHEN NO_DATA_FOUND THEN
6449: IF l_debug_mode = 'Y' THEN
6514: IF l_impl_qty_exists = 'Y' THEN
6515: BEGIN
6516: SELECT NVL(B.REV_PARTIALLY_IMPL_FLAG,'N') , A.IMPL_EQUIPMENT_QUANTITY
6517: INTO l_revenue_partial_flag, l_partial_quantity
6518: FROM PA_FP_MERGED_CTRL_ITEMS A , PA_BUDGET_VERSIONS B
6519: WHERE A.CI_PLAN_VERSION_ID = p_ci_version_id
6520: AND A.PLAN_VERSION_ID = p_budget_version_id
6521: AND A.VERSION_TYPE = 'REVENUE'
6522: AND B.BUDGET_VERSION_ID = A.CI_PLAN_VERSION_ID;
6542:
6543: BEGIN
6544: SELECT A.BUDGET_VERSION_ID
6545: INTO l_appr_rev_cw_version_id
6546: FROM PA_BUDGET_VERSIONS A
6547: WHERE A.PROJECT_ID = (SELECT B.PROJECT_ID FROM PA_BUDGET_VERSIONS B
6548: WHERE B.BUDGET_VERSION_ID = p_budget_version_id)
6549: AND A.VERSION_TYPE = 'REVENUE'
6550: AND A.APPROVED_REV_PLAN_TYPE_FLAG = 'Y'
6543: BEGIN
6544: SELECT A.BUDGET_VERSION_ID
6545: INTO l_appr_rev_cw_version_id
6546: FROM PA_BUDGET_VERSIONS A
6547: WHERE A.PROJECT_ID = (SELECT B.PROJECT_ID FROM PA_BUDGET_VERSIONS B
6548: WHERE B.BUDGET_VERSION_ID = p_budget_version_id)
6549: AND A.VERSION_TYPE = 'REVENUE'
6550: AND A.APPROVED_REV_PLAN_TYPE_FLAG = 'Y'
6551: AND CURRENT_WORKING_FLAG = 'Y'
6626: IF l_impl_qty_exists = 'Y' THEN
6627: BEGIN
6628: SELECT NVL(B.REV_PARTIALLY_IMPL_FLAG,'N') , A.IMPL_EQUIPMENT_QUANTITY
6629: INTO l_revenue_partial_flag, l_partial_quantity
6630: FROM PA_FP_MERGED_CTRL_ITEMS A , PA_BUDGET_VERSIONS B
6631: WHERE A.CI_PLAN_VERSION_ID = p_ci_version_id
6632: AND A.PLAN_VERSION_ID = p_budget_version_id
6633: AND A.VERSION_TYPE = 'REVENUE'
6634: AND B.BUDGET_VERSION_ID = A.CI_PLAN_VERSION_ID;
6666: -- values are returned without bothering about double count.
6667: -----------------------------------------------------------------------------------
6668:
6669: FUNCTION get_pc_revenue_partial (
6670: p_version_type IN pa_budget_versions.version_type%TYPE, -- version type of CI
6671: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6672: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6673: p_revenue IN pa_budget_versions.total_project_revenue%TYPE DEFAULT NULL,
6674: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6667: -----------------------------------------------------------------------------------
6668:
6669: FUNCTION get_pc_revenue_partial (
6670: p_version_type IN pa_budget_versions.version_type%TYPE, -- version type of CI
6671: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6672: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6673: p_revenue IN pa_budget_versions.total_project_revenue%TYPE DEFAULT NULL,
6674: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6675: )
6668:
6669: FUNCTION get_pc_revenue_partial (
6670: p_version_type IN pa_budget_versions.version_type%TYPE, -- version type of CI
6671: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6672: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6673: p_revenue IN pa_budget_versions.total_project_revenue%TYPE DEFAULT NULL,
6674: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6675: )
6676: RETURN NUMBER
6669: FUNCTION get_pc_revenue_partial (
6670: p_version_type IN pa_budget_versions.version_type%TYPE, -- version type of CI
6671: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6672: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6673: p_revenue IN pa_budget_versions.total_project_revenue%TYPE DEFAULT NULL,
6674: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6675: )
6676: RETURN NUMBER
6677: IS
6681: l_revenue_partial_flag VARCHAR2(1);
6682: l_debug_mode VARCHAR2(30);
6683: l_module_name VARCHAR2(30) := 'ctrl_utils.rev_prtial';
6684: l_exists VARCHAR2(1) := 'N';
6685: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6686: l_budget_version_type pa_budget_versions.version_type%TYPE;
6687: l_version_type pa_budget_versions.version_type%TYPE;
6688: -- Bug 5845142
6689: l_ci_app_rev_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
6682: l_debug_mode VARCHAR2(30);
6683: l_module_name VARCHAR2(30) := 'ctrl_utils.rev_prtial';
6684: l_exists VARCHAR2(1) := 'N';
6685: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6686: l_budget_version_type pa_budget_versions.version_type%TYPE;
6687: l_version_type pa_budget_versions.version_type%TYPE;
6688: -- Bug 5845142
6689: l_ci_app_rev_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
6690: BEGIN
6683: l_module_name VARCHAR2(30) := 'ctrl_utils.rev_prtial';
6684: l_exists VARCHAR2(1) := 'N';
6685: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6686: l_budget_version_type pa_budget_versions.version_type%TYPE;
6687: l_version_type pa_budget_versions.version_type%TYPE;
6688: -- Bug 5845142
6689: l_ci_app_rev_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
6690: BEGIN
6691:
6685: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6686: l_budget_version_type pa_budget_versions.version_type%TYPE;
6687: l_version_type pa_budget_versions.version_type%TYPE;
6688: -- Bug 5845142
6689: l_ci_app_rev_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
6690: BEGIN
6691:
6692: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
6693:
6705:
6706: --Bug 5845142
6707: SELECT NVL(approved_rev_plan_type_flag,'N')
6708: INTO l_ci_app_rev_flag
6709: FROM pa_budget_versions
6710: WHERE BUDGET_VERSION_ID = p_ci_version_id;
6711:
6712: --Bug 5845142
6713: IF l_ci_app_rev_flag = 'N' THEN
6717: IF p_revenue IS NULL THEN
6718: BEGIN
6719: SELECT TOTAL_PROJECT_REVENUE
6720: INTO l_revenue
6721: FROM PA_BUDGET_VERSIONS
6722: WHERE BUDGET_VERSION_ID = p_ci_version_id;
6723: EXCEPTION
6724: WHEN NO_DATA_FOUND THEN
6725: IF l_debug_mode = 'Y' THEN
6756: If p_version_type is NULL THEN
6757: BEGIN
6758: SELECT VERSION_TYPE
6759: INTO l_version_type
6760: FROM PA_BUDGET_VERSIONS
6761: WHERE BUDGET_VERSION_ID = p_ci_version_id;
6762: EXCEPTION
6763: WHEN NO_DATA_FOUND THEN
6764: IF l_debug_mode = 'Y' THEN
6776: ----------------------------------------------------------------
6777: BEGIN
6778: SELECT VERSION_TYPE
6779: INTO l_budget_version_type
6780: FROM PA_BUDGET_VERSIONS
6781: WHERE BUDGET_VERSION_ID = p_budget_version_id;
6782: EXCEPTION
6783: WHEN NO_DATA_FOUND THEN
6784: IF l_debug_mode = 'Y' THEN
6800: IF l_exists = 'Y' THEN
6801: BEGIN
6802: SELECT NVL(B.REV_PARTIALLY_IMPL_FLAG,'N') , A.IMPL_PROJ_REVENUE
6803: INTO l_revenue_partial_flag, l_partial_revenue
6804: FROM PA_FP_MERGED_CTRL_ITEMS A , PA_BUDGET_VERSIONS B
6805: WHERE A.CI_PLAN_VERSION_ID = p_ci_version_id
6806: AND A.PLAN_VERSION_ID = p_budget_version_id
6807: AND A.VERSION_TYPE = 'REVENUE'
6808: AND B.BUDGET_VERSION_ID = A.CI_PLAN_VERSION_ID;
6831:
6832: BEGIN
6833: SELECT A.BUDGET_VERSION_ID
6834: INTO l_appr_rev_cw_version_id
6835: FROM PA_BUDGET_VERSIONS A
6836: WHERE A.PROJECT_ID = (SELECT B.PROJECT_ID FROM PA_BUDGET_VERSIONS B
6837: WHERE B.BUDGET_VERSION_ID = p_budget_version_id)
6838: AND A.VERSION_TYPE IN('ALL', 'REVENUE')
6839: -- Raja review AND A.VERSION_TYPE IN('REVENUE')
6832: BEGIN
6833: SELECT A.BUDGET_VERSION_ID
6834: INTO l_appr_rev_cw_version_id
6835: FROM PA_BUDGET_VERSIONS A
6836: WHERE A.PROJECT_ID = (SELECT B.PROJECT_ID FROM PA_BUDGET_VERSIONS B
6837: WHERE B.BUDGET_VERSION_ID = p_budget_version_id)
6838: AND A.VERSION_TYPE IN('ALL', 'REVENUE')
6839: -- Raja review AND A.VERSION_TYPE IN('REVENUE')
6840: AND A.APPROVED_REV_PLAN_TYPE_FLAG = 'Y'
6878: -- Note: In some cases, p_pt_ct_version_type is passed as null in this case
6879: -- values are returned without bothering about double count.
6880: --------------------------------------------------------------------------------
6881: FUNCTION get_pc_cost (
6882: p_version_type IN pa_budget_versions.version_type%TYPE, -- this is the ci version type
6883: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6884: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6885: p_raw_cost IN pa_budget_versions.total_project_raw_cost%TYPE DEFAULT NULL,
6886: p_burdened_cost IN pa_budget_versions.total_project_burdened_cost%TYPE DEFAULT NULL,
6879: -- values are returned without bothering about double count.
6880: --------------------------------------------------------------------------------
6881: FUNCTION get_pc_cost (
6882: p_version_type IN pa_budget_versions.version_type%TYPE, -- this is the ci version type
6883: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6884: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6885: p_raw_cost IN pa_budget_versions.total_project_raw_cost%TYPE DEFAULT NULL,
6886: p_burdened_cost IN pa_budget_versions.total_project_burdened_cost%TYPE DEFAULT NULL,
6887: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6880: --------------------------------------------------------------------------------
6881: FUNCTION get_pc_cost (
6882: p_version_type IN pa_budget_versions.version_type%TYPE, -- this is the ci version type
6883: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6884: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6885: p_raw_cost IN pa_budget_versions.total_project_raw_cost%TYPE DEFAULT NULL,
6886: p_burdened_cost IN pa_budget_versions.total_project_burdened_cost%TYPE DEFAULT NULL,
6887: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6888: )
6881: FUNCTION get_pc_cost (
6882: p_version_type IN pa_budget_versions.version_type%TYPE, -- this is the ci version type
6883: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6884: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6885: p_raw_cost IN pa_budget_versions.total_project_raw_cost%TYPE DEFAULT NULL,
6886: p_burdened_cost IN pa_budget_versions.total_project_burdened_cost%TYPE DEFAULT NULL,
6887: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6888: )
6889: RETURN NUMBER
6882: p_version_type IN pa_budget_versions.version_type%TYPE, -- this is the ci version type
6883: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6884: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6885: p_raw_cost IN pa_budget_versions.total_project_raw_cost%TYPE DEFAULT NULL,
6886: p_burdened_cost IN pa_budget_versions.total_project_burdened_cost%TYPE DEFAULT NULL,
6887: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6888: )
6889: RETURN NUMBER
6890: IS
6891: l_return_cost NUMBER :=0;
6892: l_margin_derived_from_code pa_proj_fp_options.margin_derived_from_code%TYPE;
6893: l_debug_mode VARCHAR2(30);
6894: l_module_name VARCHAR2(30) := 'ctrl_utils.get_pc_cost';
6895: l_budget_version_type pa_budget_versions.version_type%TYPE;
6896: l_version_type pa_budget_versions.version_type%TYPE;
6897: l_cost pa_budget_versions.total_project_raw_cost%TYPE;
6898:
6899: BEGIN
6892: l_margin_derived_from_code pa_proj_fp_options.margin_derived_from_code%TYPE;
6893: l_debug_mode VARCHAR2(30);
6894: l_module_name VARCHAR2(30) := 'ctrl_utils.get_pc_cost';
6895: l_budget_version_type pa_budget_versions.version_type%TYPE;
6896: l_version_type pa_budget_versions.version_type%TYPE;
6897: l_cost pa_budget_versions.total_project_raw_cost%TYPE;
6898:
6899: BEGIN
6900:
6893: l_debug_mode VARCHAR2(30);
6894: l_module_name VARCHAR2(30) := 'ctrl_utils.get_pc_cost';
6895: l_budget_version_type pa_budget_versions.version_type%TYPE;
6896: l_version_type pa_budget_versions.version_type%TYPE;
6897: l_cost pa_budget_versions.total_project_raw_cost%TYPE;
6898:
6899: BEGIN
6900:
6901: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
6931: If p_version_type is NULL THEN
6932: BEGIN
6933: SELECT VERSION_TYPE
6934: INTO l_version_type
6935: FROM PA_BUDGET_VERSIONS
6936: WHERE BUDGET_VERSION_ID = p_ci_version_id;
6937: EXCEPTION
6938: WHEN NO_DATA_FOUND THEN
6939: IF l_debug_mode = 'Y' THEN
6951: ----------------------------------------------------------------
6952: BEGIN
6953: SELECT VERSION_TYPE
6954: INTO l_budget_version_type
6955: FROM PA_BUDGET_VERSIONS
6956: WHERE BUDGET_VERSION_ID = p_budget_version_id;
6957: EXCEPTION
6958: WHEN NO_DATA_FOUND THEN
6959: IF l_debug_mode = 'Y' THEN
6991: Select decode(l_margin_derived_from_code,
6992: 'R',total_project_raw_cost
6993: ,total_project_burdened_cost)
6994: INTO l_cost
6995: FROM PA_BUDGET_VERSIONS
6996: WHERE BUDGET_VERSION_ID = p_budget_version_id;
6997:
6998: EXCEPTION
6999: WHEN NO_DATA_FOUND THEN
7040: END get_pc_cost;
7041:
7042: PROCEDURE get_not_included
7043: ( p_project_id IN NUMBER
7044: ,p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
7045: ,p_fin_plan_type_id IN pa_budget_versions.fin_plan_type_id%TYPE
7046: ,p_version_type IN pa_budget_versions.version_type%TYPE
7047: ,x_summary_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_150_TBL_TYPE --File.Sql.39 bug 4440895
7048: ,x_equipment_hours_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
7041:
7042: PROCEDURE get_not_included
7043: ( p_project_id IN NUMBER
7044: ,p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
7045: ,p_fin_plan_type_id IN pa_budget_versions.fin_plan_type_id%TYPE
7046: ,p_version_type IN pa_budget_versions.version_type%TYPE
7047: ,x_summary_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_150_TBL_TYPE --File.Sql.39 bug 4440895
7048: ,x_equipment_hours_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
7049: ,x_labor_hours_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
7042: PROCEDURE get_not_included
7043: ( p_project_id IN NUMBER
7044: ,p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
7045: ,p_fin_plan_type_id IN pa_budget_versions.fin_plan_type_id%TYPE
7046: ,p_version_type IN pa_budget_versions.version_type%TYPE
7047: ,x_summary_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_150_TBL_TYPE --File.Sql.39 bug 4440895
7048: ,x_equipment_hours_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
7049: ,x_labor_hours_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
7050: ,x_cost_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
7066: l_debug_mode VARCHAR2(30);
7067: --End of variables used for debugging
7068:
7069: l_row_count NUMBER := 0;
7070: l_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE;
7071: l_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
7072: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
7073: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
7074: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
7068:
7069: l_row_count NUMBER := 0;
7070: l_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE;
7071: l_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
7072: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
7073: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
7074: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
7075: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
7076: l_lookup_code_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
7069: l_row_count NUMBER := 0;
7070: l_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE;
7071: l_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
7072: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
7073: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
7074: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
7075: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
7076: l_lookup_code_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
7077: l_assigned_flag VARCHAR2(1) := 'N';
7070: l_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE;
7071: l_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
7072: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
7073: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
7074: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
7075: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
7076: l_lookup_code_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
7077: l_assigned_flag VARCHAR2(1) := 'N';
7078: l_module_name VARCHAR2(30) := 'ctrl_itm_utls.get_not_incl';
7071: l_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
7072: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
7073: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
7074: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
7075: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
7076: l_lookup_code_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
7077: l_assigned_flag VARCHAR2(1) := 'N';
7078: l_module_name VARCHAR2(30) := 'ctrl_itm_utls.get_not_incl';
7079:
7092: --Changed this cursor for bug 3902176
7093:
7094: CURSOR c_change_documents_status(
7095: c_system_status_code PA_CI_STATUSES_V.PROJECT_SYSTEM_STATUS_CODE%TYPE,
7096: c_budget_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
7097: c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
7098: c_fin_plan_type_id PA_BUDGET_VERSIONS.FIN_PLAN_TYPE_ID%TYPE,
7099: c_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS
7100: select nvl(sum(nvl(PA_FP_CONTROL_ITEMS_UTILS.get_labor_qty_partial(
7094: CURSOR c_change_documents_status(
7095: c_system_status_code PA_CI_STATUSES_V.PROJECT_SYSTEM_STATUS_CODE%TYPE,
7096: c_budget_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
7097: c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
7098: c_fin_plan_type_id PA_BUDGET_VERSIONS.FIN_PLAN_TYPE_ID%TYPE,
7099: c_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS
7100: select nvl(sum(nvl(PA_FP_CONTROL_ITEMS_UTILS.get_labor_qty_partial(
7101: pfca.CI_VERSION_TYPE,
7102: c_budget_version_id,
7095: c_system_status_code PA_CI_STATUSES_V.PROJECT_SYSTEM_STATUS_CODE%TYPE,
7096: c_budget_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
7097: c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
7098: c_fin_plan_type_id PA_BUDGET_VERSIONS.FIN_PLAN_TYPE_ID%TYPE,
7099: c_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS
7100: select nvl(sum(nvl(PA_FP_CONTROL_ITEMS_UTILS.get_labor_qty_partial(
7101: pfca.CI_VERSION_TYPE,
7102: c_budget_version_id,
7103: pfca.CI_VERSION_ID,
7209:
7210: BEGIN
7211: Select version_type
7212: into l_version_type
7213: from pa_budget_versions
7214: where budget_version_id = p_budget_version_id;
7215:
7216: IF l_debug_mode = 'Y' THEN
7217: pa_debug.g_err_stage:='l_version_type; '||l_version_type;
7242:
7243: BEGIN
7244: Select fin_plan_type_id
7245: into l_fin_plan_type_id
7246: from pa_budget_versions
7247: where budget_version_id = p_budget_version_id;
7248:
7249: IF l_debug_mode = 'Y' THEN
7250: pa_debug.g_err_stage:='l_fin_plan_type_id; '||l_fin_plan_type_id;
7730: /* Returns the Ids of the versions created for this Change Document(p_ci_id).
7731: The ID will be NULL if the version can never be there */
7732:
7733: --Bug 5845142
7734: PROCEDURE GET_CI_VERSIONS(P_ci_id IN Pa_budget_versions.ci_id%TYPE -- Controm item id of the change document
7735: ,X_cost_budget_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE -- ID of the cost version associated with the CI --File.Sql.39 bug 4440895
7736: ,X_rev_budget_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE -- ID of the revenue version associated with the CI --File.Sql.39 bug 4440895
7737: ,X_all_budget_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE -- ID of the all version associated with the CI --File.Sql.39 bug 4440895
7738: ,x_return_status OUT NOCOPY VARCHAR2 -- Indicates the exit status of the API --File.Sql.39 bug 4440895
7731: The ID will be NULL if the version can never be there */
7732:
7733: --Bug 5845142
7734: PROCEDURE GET_CI_VERSIONS(P_ci_id IN Pa_budget_versions.ci_id%TYPE -- Controm item id of the change document
7735: ,X_cost_budget_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE -- ID of the cost version associated with the CI --File.Sql.39 bug 4440895
7736: ,X_rev_budget_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE -- ID of the revenue version associated with the CI --File.Sql.39 bug 4440895
7737: ,X_all_budget_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE -- ID of the all version associated with the CI --File.Sql.39 bug 4440895
7738: ,x_return_status OUT NOCOPY VARCHAR2 -- Indicates the exit status of the API --File.Sql.39 bug 4440895
7739: ,x_msg_data OUT NOCOPY VARCHAR2 -- Indicates the error occurred --File.Sql.39 bug 4440895
7732:
7733: --Bug 5845142
7734: PROCEDURE GET_CI_VERSIONS(P_ci_id IN Pa_budget_versions.ci_id%TYPE -- Controm item id of the change document
7735: ,X_cost_budget_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE -- ID of the cost version associated with the CI --File.Sql.39 bug 4440895
7736: ,X_rev_budget_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE -- ID of the revenue version associated with the CI --File.Sql.39 bug 4440895
7737: ,X_all_budget_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE -- ID of the all version associated with the CI --File.Sql.39 bug 4440895
7738: ,x_return_status OUT NOCOPY VARCHAR2 -- Indicates the exit status of the API --File.Sql.39 bug 4440895
7739: ,x_msg_data OUT NOCOPY VARCHAR2 -- Indicates the error occurred --File.Sql.39 bug 4440895
7740: ,X_msg_count OUT NOCOPY NUMBER) -- Indicates the number of error messages --File.Sql.39 bug 4440895
7733: --Bug 5845142
7734: PROCEDURE GET_CI_VERSIONS(P_ci_id IN Pa_budget_versions.ci_id%TYPE -- Controm item id of the change document
7735: ,X_cost_budget_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE -- ID of the cost version associated with the CI --File.Sql.39 bug 4440895
7736: ,X_rev_budget_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE -- ID of the revenue version associated with the CI --File.Sql.39 bug 4440895
7737: ,X_all_budget_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE -- ID of the all version associated with the CI --File.Sql.39 bug 4440895
7738: ,x_return_status OUT NOCOPY VARCHAR2 -- Indicates the exit status of the API --File.Sql.39 bug 4440895
7739: ,x_msg_data OUT NOCOPY VARCHAR2 -- Indicates the error occurred --File.Sql.39 bug 4440895
7740: ,X_msg_count OUT NOCOPY NUMBER) -- Indicates the number of error messages --File.Sql.39 bug 4440895
7741: IS
7743: SELECT budget_version_id,
7744: Version_type,
7745: approved_cost_plan_type_flag,
7746: approved_rev_plan_type_flag
7747: FROM pa_budget_versions
7748: WHERE ci_id =p_ci_id;
7749:
7750: -- Start of variables used for debugging purpose
7751:
7884: and other information including whether a change order can be implemented into it */
7885: -- Added New Params for Quantity in GET_PLAN_TYPES_FOR_IMPL - Bug 3902176
7886: PROCEDURE GET_PLAN_TYPES_FOR_IMPL
7887: (P_ci_id IN Pa_fin_plan_types_b.fin_plan_type_id%TYPE, -- Id of the Change Document
7888: P_project_id IN Pa_budget_versions.project_id%TYPE, -- Id of the Project
7889: X_pt_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, -- Plsql table for fin plan type ids --File.Sql.39 bug 4440895
7890: X_pt_name_tbl OUT NOCOPY SYSTEM.pa_varchar2_150_tbl_type, -- Plsql table for fin plan type names --File.Sql.39 bug 4440895
7891: x_cost_impact_impl_tbl OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type, --File.Sql.39 bug 4440895
7892: x_rev_impact_impl_tbl OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type, --File.Sql.39 bug 4440895
7908: X_all_bv_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, -- Plsql table for all bv id --File.Sql.39 bug 4440895
7909: X_select_flag_tbl OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type, -- The flag which indicates whether the select flag can be checked by default or not --File.Sql.39 bug 4440895
7910: X_agreement_num OUT NOCOPY Pa_agreements_all.agreement_num%TYPE, -- Agreement number of the agreement --File.Sql.39 bug 4440895
7911: X_partially_impl_flag OUT NOCOPY VARCHAR2, -- A flag that indicates whether a partially implemented CO exists for the plan type or not. Possible values are Y/N --File.Sql.39 bug 4440895
7912: X_cost_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci cost Budget version id --File.Sql.39 bug 4440895
7913: X_rev_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci rev Budget version id --File.Sql.39 bug 4440895
7914: X_all_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci all Budget version id --File.Sql.39 bug 4440895
7915: x_rem_proj_revenue OUT NOCOPY Pa_budget_versions.total_project_revenue%TYPE, -- Remaining revenue amount to be implemented --File.Sql.39 bug 4440895
7916: x_rem_labor_qty OUT NOCOPY Pa_budget_versions.labor_quantity%TYPE, --File.Sql.39 bug 4440895
7909: X_select_flag_tbl OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type, -- The flag which indicates whether the select flag can be checked by default or not --File.Sql.39 bug 4440895
7910: X_agreement_num OUT NOCOPY Pa_agreements_all.agreement_num%TYPE, -- Agreement number of the agreement --File.Sql.39 bug 4440895
7911: X_partially_impl_flag OUT NOCOPY VARCHAR2, -- A flag that indicates whether a partially implemented CO exists for the plan type or not. Possible values are Y/N --File.Sql.39 bug 4440895
7912: X_cost_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci cost Budget version id --File.Sql.39 bug 4440895
7913: X_rev_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci rev Budget version id --File.Sql.39 bug 4440895
7914: X_all_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci all Budget version id --File.Sql.39 bug 4440895
7915: x_rem_proj_revenue OUT NOCOPY Pa_budget_versions.total_project_revenue%TYPE, -- Remaining revenue amount to be implemented --File.Sql.39 bug 4440895
7916: x_rem_labor_qty OUT NOCOPY Pa_budget_versions.labor_quantity%TYPE, --File.Sql.39 bug 4440895
7917: x_rem_equip_qty OUT NOCOPY pa_budget_versions.equipment_quantity%TYPE, --File.Sql.39 bug 4440895
7910: X_agreement_num OUT NOCOPY Pa_agreements_all.agreement_num%TYPE, -- Agreement number of the agreement --File.Sql.39 bug 4440895
7911: X_partially_impl_flag OUT NOCOPY VARCHAR2, -- A flag that indicates whether a partially implemented CO exists for the plan type or not. Possible values are Y/N --File.Sql.39 bug 4440895
7912: X_cost_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci cost Budget version id --File.Sql.39 bug 4440895
7913: X_rev_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci rev Budget version id --File.Sql.39 bug 4440895
7914: X_all_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci all Budget version id --File.Sql.39 bug 4440895
7915: x_rem_proj_revenue OUT NOCOPY Pa_budget_versions.total_project_revenue%TYPE, -- Remaining revenue amount to be implemented --File.Sql.39 bug 4440895
7916: x_rem_labor_qty OUT NOCOPY Pa_budget_versions.labor_quantity%TYPE, --File.Sql.39 bug 4440895
7917: x_rem_equip_qty OUT NOCOPY pa_budget_versions.equipment_quantity%TYPE, --File.Sql.39 bug 4440895
7918: X_autobaseline_project OUT NOCOPY VARCHAR2, -- This flag will be set to Y if the project is enabled for autobaseline --File.Sql.39 bug 4440895
7911: X_partially_impl_flag OUT NOCOPY VARCHAR2, -- A flag that indicates whether a partially implemented CO exists for the plan type or not. Possible values are Y/N --File.Sql.39 bug 4440895
7912: X_cost_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci cost Budget version id --File.Sql.39 bug 4440895
7913: X_rev_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci rev Budget version id --File.Sql.39 bug 4440895
7914: X_all_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci all Budget version id --File.Sql.39 bug 4440895
7915: x_rem_proj_revenue OUT NOCOPY Pa_budget_versions.total_project_revenue%TYPE, -- Remaining revenue amount to be implemented --File.Sql.39 bug 4440895
7916: x_rem_labor_qty OUT NOCOPY Pa_budget_versions.labor_quantity%TYPE, --File.Sql.39 bug 4440895
7917: x_rem_equip_qty OUT NOCOPY pa_budget_versions.equipment_quantity%TYPE, --File.Sql.39 bug 4440895
7918: X_autobaseline_project OUT NOCOPY VARCHAR2, -- This flag will be set to Y if the project is enabled for autobaseline --File.Sql.39 bug 4440895
7919: x_disable_baseline_flag_tbl OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type, -- Plsql table for Disable Baseline Checkbox Flag -- 3735309 --File.Sql.39 bug 4440895
7912: X_cost_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci cost Budget version id --File.Sql.39 bug 4440895
7913: X_rev_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci rev Budget version id --File.Sql.39 bug 4440895
7914: X_all_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci all Budget version id --File.Sql.39 bug 4440895
7915: x_rem_proj_revenue OUT NOCOPY Pa_budget_versions.total_project_revenue%TYPE, -- Remaining revenue amount to be implemented --File.Sql.39 bug 4440895
7916: x_rem_labor_qty OUT NOCOPY Pa_budget_versions.labor_quantity%TYPE, --File.Sql.39 bug 4440895
7917: x_rem_equip_qty OUT NOCOPY pa_budget_versions.equipment_quantity%TYPE, --File.Sql.39 bug 4440895
7918: X_autobaseline_project OUT NOCOPY VARCHAR2, -- This flag will be set to Y if the project is enabled for autobaseline --File.Sql.39 bug 4440895
7919: x_disable_baseline_flag_tbl OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type, -- Plsql table for Disable Baseline Checkbox Flag -- 3735309 --File.Sql.39 bug 4440895
7920: x_return_status OUT NOCOPY VARCHAR2, -- Indicates the exit status of the API --File.Sql.39 bug 4440895
7913: X_rev_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci rev Budget version id --File.Sql.39 bug 4440895
7914: X_all_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci all Budget version id --File.Sql.39 bug 4440895
7915: x_rem_proj_revenue OUT NOCOPY Pa_budget_versions.total_project_revenue%TYPE, -- Remaining revenue amount to be implemented --File.Sql.39 bug 4440895
7916: x_rem_labor_qty OUT NOCOPY Pa_budget_versions.labor_quantity%TYPE, --File.Sql.39 bug 4440895
7917: x_rem_equip_qty OUT NOCOPY pa_budget_versions.equipment_quantity%TYPE, --File.Sql.39 bug 4440895
7918: X_autobaseline_project OUT NOCOPY VARCHAR2, -- This flag will be set to Y if the project is enabled for autobaseline --File.Sql.39 bug 4440895
7919: x_disable_baseline_flag_tbl OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type, -- Plsql table for Disable Baseline Checkbox Flag -- 3735309 --File.Sql.39 bug 4440895
7920: x_return_status OUT NOCOPY VARCHAR2, -- Indicates the exit status of the API --File.Sql.39 bug 4440895
7921: x_msg_data OUT NOCOPY VARCHAR2, -- Indicates the error occurred --File.Sql.39 bug 4440895
7967:
7968: l_status_code pa_control_items.status_code%TYPE;
7969: l_ci_type_id pa_control_items.ci_type_id%TYPE;
7970:
7971: l_ci_version_type pa_budget_versions.version_type%TYPE;
7972:
7973: l_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
7974:
7975: l_approved_fin_pt_id Pa_fin_plan_types_b.fin_plan_type_id%TYPE;
7969: l_ci_type_id pa_control_items.ci_type_id%TYPE;
7970:
7971: l_ci_version_type pa_budget_versions.version_type%TYPE;
7972:
7973: l_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
7974:
7975: l_approved_fin_pt_id Pa_fin_plan_types_b.fin_plan_type_id%TYPE;
7976:
7977: -- Version Type ref is not longer required as both Cost and Revenue Quantity Figures are Retrieved and used now -- 3902176
7974:
7975: l_approved_fin_pt_id Pa_fin_plan_types_b.fin_plan_type_id%TYPE;
7976:
7977: -- Version Type ref is not longer required as both Cost and Revenue Quantity Figures are Retrieved and used now -- 3902176
7978: -- l_version_type pa_budget_versions.version_type%TYPE;--Bug 3662077
7979:
7980: -- Added for Bug 3735309 - Function Security Check
7981: l_submit_cost VARCHAR2(1);
7982: l_submit_revenue VARCHAR2(1);
8622:
8623: See comments for other OUT parameters to know what they indicate */
8624:
8625: PROCEDURE GET_IMPL_DETAILS(P_fin_plan_type_id IN Pa_fin_plan_types_b.fin_plan_type_id%TYPE -- Id of the plan type
8626: ,P_project_id IN Pa_budget_versions.project_id%TYPE -- Id of the Project
8627: ,P_app_rev_plan_type_flag IN pa_budget_versions.approved_rev_plan_type_flag%TYPE DEFAULT NULL -- Indicates whether the plan type passed is approved rev_plan_type or not. If the value is NULL the value will be derived
8628: ,P_ci_id IN Pa_budget_versions.ci_id%TYPE -- Id of the Change Order
8629: ,p_ci_type_id IN pa_control_items.ci_type_id%TYPE DEFAULT NULL
8630: ,P_ci_status IN Pa_control_items.status_code%TYPE DEFAULT NULL -- Status of the Change Order
8623: See comments for other OUT parameters to know what they indicate */
8624:
8625: PROCEDURE GET_IMPL_DETAILS(P_fin_plan_type_id IN Pa_fin_plan_types_b.fin_plan_type_id%TYPE -- Id of the plan type
8626: ,P_project_id IN Pa_budget_versions.project_id%TYPE -- Id of the Project
8627: ,P_app_rev_plan_type_flag IN pa_budget_versions.approved_rev_plan_type_flag%TYPE DEFAULT NULL -- Indicates whether the plan type passed is approved rev_plan_type or not. If the value is NULL the value will be derived
8628: ,P_ci_id IN Pa_budget_versions.ci_id%TYPE -- Id of the Change Order
8629: ,p_ci_type_id IN pa_control_items.ci_type_id%TYPE DEFAULT NULL
8630: ,P_ci_status IN Pa_control_items.status_code%TYPE DEFAULT NULL -- Status of the Change Order
8631: ,P_ci_cost_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Cost ci version
8624:
8625: PROCEDURE GET_IMPL_DETAILS(P_fin_plan_type_id IN Pa_fin_plan_types_b.fin_plan_type_id%TYPE -- Id of the plan type
8626: ,P_project_id IN Pa_budget_versions.project_id%TYPE -- Id of the Project
8627: ,P_app_rev_plan_type_flag IN pa_budget_versions.approved_rev_plan_type_flag%TYPE DEFAULT NULL -- Indicates whether the plan type passed is approved rev_plan_type or not. If the value is NULL the value will be derived
8628: ,P_ci_id IN Pa_budget_versions.ci_id%TYPE -- Id of the Change Order
8629: ,p_ci_type_id IN pa_control_items.ci_type_id%TYPE DEFAULT NULL
8630: ,P_ci_status IN Pa_control_items.status_code%TYPE DEFAULT NULL -- Status of the Change Order
8631: ,P_ci_cost_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Cost ci version
8632: ,P_ci_rev_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Revenue ci version
8627: ,P_app_rev_plan_type_flag IN pa_budget_versions.approved_rev_plan_type_flag%TYPE DEFAULT NULL -- Indicates whether the plan type passed is approved rev_plan_type or not. If the value is NULL the value will be derived
8628: ,P_ci_id IN Pa_budget_versions.ci_id%TYPE -- Id of the Change Order
8629: ,p_ci_type_id IN pa_control_items.ci_type_id%TYPE DEFAULT NULL
8630: ,P_ci_status IN Pa_control_items.status_code%TYPE DEFAULT NULL -- Status of the Change Order
8631: ,P_ci_cost_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Cost ci version
8632: ,P_ci_rev_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Revenue ci version
8633: ,P_ci_all_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the All ci version
8634: ,p_targ_bv_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the target budget version. Bug 3745163
8635: ,x_cost_impl_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
8628: ,P_ci_id IN Pa_budget_versions.ci_id%TYPE -- Id of the Change Order
8629: ,p_ci_type_id IN pa_control_items.ci_type_id%TYPE DEFAULT NULL
8630: ,P_ci_status IN Pa_control_items.status_code%TYPE DEFAULT NULL -- Status of the Change Order
8631: ,P_ci_cost_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Cost ci version
8632: ,P_ci_rev_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Revenue ci version
8633: ,P_ci_all_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the All ci version
8634: ,p_targ_bv_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the target budget version. Bug 3745163
8635: ,x_cost_impl_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
8636: ,x_rev_impl_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
8629: ,p_ci_type_id IN pa_control_items.ci_type_id%TYPE DEFAULT NULL
8630: ,P_ci_status IN Pa_control_items.status_code%TYPE DEFAULT NULL -- Status of the Change Order
8631: ,P_ci_cost_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Cost ci version
8632: ,P_ci_rev_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Revenue ci version
8633: ,P_ci_all_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the All ci version
8634: ,p_targ_bv_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the target budget version. Bug 3745163
8635: ,x_cost_impl_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
8636: ,x_rev_impl_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
8637: ,X_cost_impact_impl_flag OUT NOCOPY VARCHAR2 -- Contains 'Y' if the cost impact is completely implemented --File.Sql.39 bug 4440895
8630: ,P_ci_status IN Pa_control_items.status_code%TYPE DEFAULT NULL -- Status of the Change Order
8631: ,P_ci_cost_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Cost ci version
8632: ,P_ci_rev_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Revenue ci version
8633: ,P_ci_all_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the All ci version
8634: ,p_targ_bv_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the target budget version. Bug 3745163
8635: ,x_cost_impl_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
8636: ,x_rev_impl_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
8637: ,X_cost_impact_impl_flag OUT NOCOPY VARCHAR2 -- Contains 'Y' if the cost impact is completely implemented --File.Sql.39 bug 4440895
8638: ,x_rev_impact_impl_flag OUT NOCOPY VARCHAR2 -- Contains 'Y' if the revenue impact is completely implemented --File.Sql.39 bug 4440895
8683:
8684: -- End of variables used for debugging purpose
8685:
8686: l_is_impl VARCHAR2(1);
8687: l_ci_version_id Pa_budget_versions.budget_version_id%TYPE;
8688: l_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8689:
8690: l_cost_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8691: l_rev_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8684: -- End of variables used for debugging purpose
8685:
8686: l_is_impl VARCHAR2(1);
8687: l_ci_version_id Pa_budget_versions.budget_version_id%TYPE;
8688: l_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8689:
8690: l_cost_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8691: l_rev_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8692: l_all_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8686: l_is_impl VARCHAR2(1);
8687: l_ci_version_id Pa_budget_versions.budget_version_id%TYPE;
8688: l_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8689:
8690: l_cost_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8691: l_rev_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8692: l_all_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8693:
8694: l_ci_type_id pa_control_items.ci_type_id%TYPE;
8687: l_ci_version_id Pa_budget_versions.budget_version_id%TYPE;
8688: l_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8689:
8690: l_cost_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8691: l_rev_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8692: l_all_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8693:
8694: l_ci_type_id pa_control_items.ci_type_id%TYPE;
8695:
8688: l_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8689:
8690: l_cost_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8691: l_rev_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8692: l_all_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8693:
8694: l_ci_type_id pa_control_items.ci_type_id%TYPE;
8695:
8696: l_status_allows_cost_impl VARCHAR2(1);
8700:
8701: l_agreement_amount NUMBER;
8702: l_agreement_currency_code pa_agreements_all.agreement_currency_code%TYPE;
8703:
8704: l_ci_cost_version_id Pa_budget_versions.budget_version_id%TYPE;
8705: l_ci_rev_version_id Pa_budget_versions.budget_version_id%TYPE;
8706: l_ci_all_version_id Pa_budget_versions.budget_version_id%TYPE;
8707:
8708: l_status_code pa_control_items.status_code%TYPE;
8701: l_agreement_amount NUMBER;
8702: l_agreement_currency_code pa_agreements_all.agreement_currency_code%TYPE;
8703:
8704: l_ci_cost_version_id Pa_budget_versions.budget_version_id%TYPE;
8705: l_ci_rev_version_id Pa_budget_versions.budget_version_id%TYPE;
8706: l_ci_all_version_id Pa_budget_versions.budget_version_id%TYPE;
8707:
8708: l_status_code pa_control_items.status_code%TYPE;
8709: l_target_version_id pa_budget_versions.budget_version_id%TYPE;
8702: l_agreement_currency_code pa_agreements_all.agreement_currency_code%TYPE;
8703:
8704: l_ci_cost_version_id Pa_budget_versions.budget_version_id%TYPE;
8705: l_ci_rev_version_id Pa_budget_versions.budget_version_id%TYPE;
8706: l_ci_all_version_id Pa_budget_versions.budget_version_id%TYPE;
8707:
8708: l_status_code pa_control_items.status_code%TYPE;
8709: l_target_version_id pa_budget_versions.budget_version_id%TYPE;
8710:
8705: l_ci_rev_version_id Pa_budget_versions.budget_version_id%TYPE;
8706: l_ci_all_version_id Pa_budget_versions.budget_version_id%TYPE;
8707:
8708: l_status_code pa_control_items.status_code%TYPE;
8709: l_target_version_id pa_budget_versions.budget_version_id%TYPE;
8710:
8711: l_rev_impl_full VARCHAR2(1); -- Will indicate if revenue has been implemented in full or not
8712:
8713: l_version_type Pa_budget_versions.version_type%TYPE; -- Bug 3745163
8709: l_target_version_id pa_budget_versions.budget_version_id%TYPE;
8710:
8711: l_rev_impl_full VARCHAR2(1); -- Will indicate if revenue has been implemented in full or not
8712:
8713: l_version_type Pa_budget_versions.version_type%TYPE; -- Bug 3745163
8714:
8715: l_current_working_flag Pa_budget_versions.current_working_flag%TYPE; -- Bug 3732446
8716:
8717: --Bug 5845142
8711: l_rev_impl_full VARCHAR2(1); -- Will indicate if revenue has been implemented in full or not
8712:
8713: l_version_type Pa_budget_versions.version_type%TYPE; -- Bug 3745163
8714:
8715: l_current_working_flag Pa_budget_versions.current_working_flag%TYPE; -- Bug 3732446
8716:
8717: --Bug 5845142
8718: l_t_app_cost_flag pa_budget_versions.approved_cost_plan_type_flag%TYPE;
8719: l_t_app_rev_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
8714:
8715: l_current_working_flag Pa_budget_versions.current_working_flag%TYPE; -- Bug 3732446
8716:
8717: --Bug 5845142
8718: l_t_app_cost_flag pa_budget_versions.approved_cost_plan_type_flag%TYPE;
8719: l_t_app_rev_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
8720: l_t_pt_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
8721:
8722: BEGIN
8715: l_current_working_flag Pa_budget_versions.current_working_flag%TYPE; -- Bug 3732446
8716:
8717: --Bug 5845142
8718: l_t_app_cost_flag pa_budget_versions.approved_cost_plan_type_flag%TYPE;
8719: l_t_app_rev_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
8720: l_t_pt_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
8721:
8722: BEGIN
8723:
8866:
8867: IF p_targ_bv_id IS NOT NULL THEN
8868: SELECT version_type,current_working_flag
8869: INTO l_version_type,l_current_working_flag
8870: FROM pa_budget_versions
8871: WHERE budget_version_id=p_targ_bv_id;
8872:
8873: IF l_version_type=PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST THEN
8874: l_ci_rev_version_id := NULL;
9077: AND ((l_ci_rev_version_id IS NOT NULL) OR (l_ci_all_version_id IS NOT NULL)) THEN
9078:
9079: /* Scheme Used to Derive Partial Impl Flag Below(this might change)
9080: ---------------------------------------------
9081: Fetch partial impl flag from pa_budget_versions
9082: for the ci_version
9083: If Flag is Y then
9084: Return Y
9085: If Flag is not Y then
9095: BEGIN
9096: -- Changed Select below for Bug 3668169
9097: SELECT nvl(rev_partially_impl_flag,'N')
9098: INTO x_partially_impl_flag
9099: FROM pa_budget_versions pbv
9100: WHERE pbv.budget_Version_id = nvl(l_ci_rev_version_id,l_ci_all_version_id);
9101:
9102: /* SELECT nvl(rev_partially_impl_flag,'N')
9103: INTO x_partially_impl_flag
9100: WHERE pbv.budget_Version_id = nvl(l_ci_rev_version_id,l_ci_all_version_id);
9101:
9102: /* SELECT nvl(rev_partially_impl_flag,'N')
9103: INTO x_partially_impl_flag
9104: FROM pa_budget_versions pbv
9105: WHERE pbv.budget_Version_id = decode( nvl(l_rev_budget_version_id,-1),
9106: -1,l_all_budget_version_id,
9107: l_rev_budget_version_id); */
9108:
9284: --is available its not required to fetch the approved cost/rev current working ids.
9285: PROCEDURE get_dtls_for_view_fin_imp_pg
9286: (p_project_id IN pa_projects_all.project_id%TYPE,
9287: p_ci_id IN pa_control_items.ci_id%TYPE,
9288: p_ci_cost_version_id IN pa_budget_versions.budget_version_id%TYPE,
9289: p_ci_rev_version_id IN pa_budget_versions.budget_version_id%TYPE,
9290: p_ci_all_version_id IN pa_budget_versions.budget_version_id%TYPE,
9291: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
9292: x_app_cost_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9285: PROCEDURE get_dtls_for_view_fin_imp_pg
9286: (p_project_id IN pa_projects_all.project_id%TYPE,
9287: p_ci_id IN pa_control_items.ci_id%TYPE,
9288: p_ci_cost_version_id IN pa_budget_versions.budget_version_id%TYPE,
9289: p_ci_rev_version_id IN pa_budget_versions.budget_version_id%TYPE,
9290: p_ci_all_version_id IN pa_budget_versions.budget_version_id%TYPE,
9291: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
9292: x_app_cost_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9293: x_app_rev_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9286: (p_project_id IN pa_projects_all.project_id%TYPE,
9287: p_ci_id IN pa_control_items.ci_id%TYPE,
9288: p_ci_cost_version_id IN pa_budget_versions.budget_version_id%TYPE,
9289: p_ci_rev_version_id IN pa_budget_versions.budget_version_id%TYPE,
9290: p_ci_all_version_id IN pa_budget_versions.budget_version_id%TYPE,
9291: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
9292: x_app_cost_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9293: x_app_rev_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9294: x_ci_status_code OUT NOCOPY pa_control_items.status_code%TYPE, --File.Sql.39 bug 4440895
9287: p_ci_id IN pa_control_items.ci_id%TYPE,
9288: p_ci_cost_version_id IN pa_budget_versions.budget_version_id%TYPE,
9289: p_ci_rev_version_id IN pa_budget_versions.budget_version_id%TYPE,
9290: p_ci_all_version_id IN pa_budget_versions.budget_version_id%TYPE,
9291: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
9292: x_app_cost_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9293: x_app_rev_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9294: x_ci_status_code OUT NOCOPY pa_control_items.status_code%TYPE, --File.Sql.39 bug 4440895
9295: x_project_currency_code OUT NOCOPY pa_projects_all.project_currency_code%TYPE, --File.Sql.39 bug 4440895
9288: p_ci_cost_version_id IN pa_budget_versions.budget_version_id%TYPE,
9289: p_ci_rev_version_id IN pa_budget_versions.budget_version_id%TYPE,
9290: p_ci_all_version_id IN pa_budget_versions.budget_version_id%TYPE,
9291: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
9292: x_app_cost_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9293: x_app_rev_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9294: x_ci_status_code OUT NOCOPY pa_control_items.status_code%TYPE, --File.Sql.39 bug 4440895
9295: x_project_currency_code OUT NOCOPY pa_projects_all.project_currency_code%TYPE, --File.Sql.39 bug 4440895
9296: x_impact_in_mc_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9289: p_ci_rev_version_id IN pa_budget_versions.budget_version_id%TYPE,
9290: p_ci_all_version_id IN pa_budget_versions.budget_version_id%TYPE,
9291: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
9292: x_app_cost_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9293: x_app_rev_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9294: x_ci_status_code OUT NOCOPY pa_control_items.status_code%TYPE, --File.Sql.39 bug 4440895
9295: x_project_currency_code OUT NOCOPY pa_projects_all.project_currency_code%TYPE, --File.Sql.39 bug 4440895
9296: x_impact_in_mc_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9297: x_targ_version_type OUT NOCOPY pa_budget_Versions.version_type%TYPE, --File.Sql.39 bug 4440895
9293: x_app_rev_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9294: x_ci_status_code OUT NOCOPY pa_control_items.status_code%TYPE, --File.Sql.39 bug 4440895
9295: x_project_currency_code OUT NOCOPY pa_projects_all.project_currency_code%TYPE, --File.Sql.39 bug 4440895
9296: x_impact_in_mc_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9297: x_targ_version_type OUT NOCOPY pa_budget_Versions.version_type%TYPE, --File.Sql.39 bug 4440895
9298: x_show_resources_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9299: x_plan_class_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9300: x_report_cost_using OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9301: x_cost_impl_into_app_cw_ver OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9315: l_return_status VARCHAR2(2000);
9316: l_debug_mode VARCHAR2(30);
9317: l_module_name VARCHAR2(100) := 'PAFPCIUB.get_dtls_for_view_fin_imp_pg' ;
9318: --End of variables used for debugging
9319: l_cost_ci_version_id pa_budget_versions.budget_version_id%TYPE;
9320: l_rev_ci_version_id pa_budget_versions.budget_version_id%TYPE;
9321: l_all_ci_version_id pa_budget_versions.budget_version_id%TYPE;
9322: l_ci_resource_list_id1 pa_resource_lists_all_bg.resource_list_id%TYPE;
9323: l_ci_resource_list_id2 pa_resource_lists_all_bg.resource_list_id%TYPE;
9316: l_debug_mode VARCHAR2(30);
9317: l_module_name VARCHAR2(100) := 'PAFPCIUB.get_dtls_for_view_fin_imp_pg' ;
9318: --End of variables used for debugging
9319: l_cost_ci_version_id pa_budget_versions.budget_version_id%TYPE;
9320: l_rev_ci_version_id pa_budget_versions.budget_version_id%TYPE;
9321: l_all_ci_version_id pa_budget_versions.budget_version_id%TYPE;
9322: l_ci_resource_list_id1 pa_resource_lists_all_bg.resource_list_id%TYPE;
9323: l_ci_resource_list_id2 pa_resource_lists_all_bg.resource_list_id%TYPE;
9324: l_targ_resource_list_id1 pa_resource_lists_all_bg.resource_list_id%TYPE;
9317: l_module_name VARCHAR2(100) := 'PAFPCIUB.get_dtls_for_view_fin_imp_pg' ;
9318: --End of variables used for debugging
9319: l_cost_ci_version_id pa_budget_versions.budget_version_id%TYPE;
9320: l_rev_ci_version_id pa_budget_versions.budget_version_id%TYPE;
9321: l_all_ci_version_id pa_budget_versions.budget_version_id%TYPE;
9322: l_ci_resource_list_id1 pa_resource_lists_all_bg.resource_list_id%TYPE;
9323: l_ci_resource_list_id2 pa_resource_lists_all_bg.resource_list_id%TYPE;
9324: l_targ_resource_list_id1 pa_resource_lists_all_bg.resource_list_id%TYPE;
9325: l_targ_resource_list_id2 pa_resource_lists_all_bg.resource_list_id%TYPE;
9324: l_targ_resource_list_id1 pa_resource_lists_all_bg.resource_list_id%TYPE;
9325: l_targ_resource_list_id2 pa_resource_lists_all_bg.resource_list_id%TYPE;
9326: l_dummy VARCHAR2(1);
9327:
9328: CURSOR c_ci_merge_csr(c_version_type pa_budget_Versions.version_type%TYPE,
9329: c_ci_version_id pa_budget_Versions.budget_Version_id%TYPE,
9330: c_app_cw_ver_id pa_budget_Versions.budget_Version_id%TYPE)
9331: IS
9332: SELECT 'x'
9325: l_targ_resource_list_id2 pa_resource_lists_all_bg.resource_list_id%TYPE;
9326: l_dummy VARCHAR2(1);
9327:
9328: CURSOR c_ci_merge_csr(c_version_type pa_budget_Versions.version_type%TYPE,
9329: c_ci_version_id pa_budget_Versions.budget_Version_id%TYPE,
9330: c_app_cw_ver_id pa_budget_Versions.budget_Version_id%TYPE)
9331: IS
9332: SELECT 'x'
9333: FROM DUAL
9326: l_dummy VARCHAR2(1);
9327:
9328: CURSOR c_ci_merge_csr(c_version_type pa_budget_Versions.version_type%TYPE,
9329: c_ci_version_id pa_budget_Versions.budget_Version_id%TYPE,
9330: c_app_cw_ver_id pa_budget_Versions.budget_Version_id%TYPE)
9331: IS
9332: SELECT 'x'
9333: FROM DUAL
9334: WHERE EXISTS (SELECT 'x'
9504: SELECT pbvc.resource_list_id,
9505: pbvr.resource_list_id
9506: INTO l_targ_resource_list_id1 ,
9507: l_targ_resource_list_id2
9508: FROM pa_budget_versions pbvc,
9509: pa_budget_versions pbvr
9510: WHERE pbvc.budget_version_id=nvl(x_app_cost_cw_ver_id,x_app_rev_cw_ver_id)
9511: AND pbvr.budget_version_id=nvl(x_app_rev_cw_ver_id,x_app_cost_cw_ver_id) ;
9512:
9505: pbvr.resource_list_id
9506: INTO l_targ_resource_list_id1 ,
9507: l_targ_resource_list_id2
9508: FROM pa_budget_versions pbvc,
9509: pa_budget_versions pbvr
9510: WHERE pbvc.budget_version_id=nvl(x_app_cost_cw_ver_id,x_app_rev_cw_ver_id)
9511: AND pbvr.budget_version_id=nvl(x_app_rev_cw_ver_id,x_app_cost_cw_ver_id) ;
9512:
9513: --The approved cost/rev current working versions will always be from plan type of plan class code BUDGET
9528: INTO x_targ_version_type,
9529: l_targ_resource_list_id1 ,
9530: l_targ_resource_list_id2,
9531: x_plan_class_code
9532: FROM pa_budget_versions pbv,
9533: pa_fin_plan_types_b fin
9534: WHERE budget_version_id=p_budget_version_id
9535: AND fin.fin_plan_type_id=pbv.fin_plan_type_id;
9536:
9700: --populated in both x_app_cost_cw_ver_id and x_app_rev_cw_ver_id
9701: --If the current working versions do not exist then null will be returned
9702: PROCEDURE get_app_cw_ver_ids_for_proj
9703: (p_project_id IN pa_projects_all.project_id%TYPE,
9704: x_app_cost_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9705: x_app_rev_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9706: x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9707: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
9708: x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
9701: --If the current working versions do not exist then null will be returned
9702: PROCEDURE get_app_cw_ver_ids_for_proj
9703: (p_project_id IN pa_projects_all.project_id%TYPE,
9704: x_app_cost_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9705: x_app_rev_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9706: x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9707: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
9708: x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
9709: IS
9722: IS
9723: SELECT budget_version_id,
9724: approved_cost_plan_type_flag,
9725: approved_rev_plan_type_flag
9726: FROM pa_budget_versions pbv
9727: WHERE pbv.project_id=p_project_id
9728: AND pbv.ci_id IS NULL
9729: AND pbv.fin_plan_type_id IS NOT NULL
9730: AND nvl(pbv.wp_version_flag,'N')='N'
9886: BEGIN
9887: -- Selecting budget_version_id for the Rev CI version
9888: SELECT budget_version_id
9889: INTO l_ci_rev_version_id
9890: FROM pa_budget_versions
9891: WHERE project_id = p_project_id
9892: AND ci_id = p_ci_id
9893: AND Nvl(approved_rev_plan_type_flag, 'N')= 'Y'
9894: AND version_type IN ('REVENUE','ALL');
9895:
9896: -- Selecting the budget_version_id of the target current working version
9897: SELECT budget_version_id
9898: INTO l_cw_bv_id
9899: FROM pa_budget_versions
9900: WHERE project_id = p_project_id
9901: AND Nvl(approved_rev_plan_type_flag, 'N')= 'Y'
9902: AND Nvl(current_working_flag, 'N') = 'Y';
9903:
10039: All the lines of a revenue change order version will be in a single currency
10040: */
10041: PROCEDURE get_txn_curr_code_of_ci_ver(
10042: p_project_id IN pa_projects_all.project_id%TYPE
10043: ,p_ci_id IN pa_budget_versions.ci_id%TYPE
10044: ,x_txn_currency_code OUT NOCOPY pa_budget_lines.txn_currency_code%TYPE --File.Sql.39 bug 4440895
10045: ,x_budget_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE --File.Sql.39 bug 4440895
10046: ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
10047: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
10041: PROCEDURE get_txn_curr_code_of_ci_ver(
10042: p_project_id IN pa_projects_all.project_id%TYPE
10043: ,p_ci_id IN pa_budget_versions.ci_id%TYPE
10044: ,x_txn_currency_code OUT NOCOPY pa_budget_lines.txn_currency_code%TYPE --File.Sql.39 bug 4440895
10045: ,x_budget_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE --File.Sql.39 bug 4440895
10046: ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
10047: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
10048: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
10049: )
10099: rac.budget_version_id
10100: INTO x_txn_currency_code,
10101: x_budget_version_id
10102: FROM pa_resource_asgn_curr rac,
10103: pa_budget_versions bv
10104: WHERE bv.project_id = p_project_id
10105: AND bv.ci_id = p_ci_id
10106: AND bv.version_type IN ('REVENUE','ALL')
10107: AND rac.budget_version_id = bv.budget_version_id