[Home] [Help]
21: x_no_of_ci_plan_versions OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
22: x_ci_est_qty OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
23: x_ci_planned_qty OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
24: x_baselined_planned_qty OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
25: x_ci_ver_plan_prc_code OUT NOCOPY pa_budget_versions.plan_processing_code%TYPE, --File.Sql.39 bug 4440895
26: x_request_id OUT NOCOPY pa_budget_versions.request_id%TYPE) IS --File.Sql.39 bug 4440895
27:
28: l_no_of_app_plan_types NUMBER;
29: l_tmp_fin_plan_type_id NUMBER;
22: x_ci_est_qty OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
23: x_ci_planned_qty OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
24: x_baselined_planned_qty OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
25: x_ci_ver_plan_prc_code OUT NOCOPY pa_budget_versions.plan_processing_code%TYPE, --File.Sql.39 bug 4440895
26: x_request_id OUT NOCOPY pa_budget_versions.request_id%TYPE) IS --File.Sql.39 bug 4440895
27:
28: l_no_of_app_plan_types NUMBER;
29: l_tmp_fin_plan_type_id NUMBER;
30: l_rev_budget_flag Pa_Project_Types_All.Allow_Rev_Budget_Entry_Flag%TYPE;
75: END;
76:
77: x_ci_type_class_code := Pa_Control_Items_Utils.GetCiTypeClassCode(p_ci_id);
78:
79: SELECT COUNT(*) INTO x_no_of_ci_plan_versions FROM pa_budget_Versions
80: WHERE project_id = p_project_id AND
81: nvl(ci_id,-1) = p_ci_id;
82:
83: SELECT COUNT(*) INTO l_no_of_app_plan_types FROM Pa_Proj_Fp_Options
128: INTO x_margin_derived_from_code,
129: x_ci_ver_plan_prc_code,
130: x_request_id
131: FROM pa_proj_fp_options fpo,
132: pa_budget_versions bv
133: WHERE bv.ci_id = p_ci_id
134: AND bv.version_type in ('COST','ALL')
135: AND fpo.fin_plan_version_id = bv.budget_version_id
136: AND bv.project_id = p_project_id;
146: SELECT bv.plan_processing_code,
147: bv.request_id
148: INTO x_ci_ver_plan_prc_code,
149: x_request_id
150: FROM pa_budget_versions bv
151: WHERE bv.ci_id = p_ci_id
152: AND bv.version_type = 'REVENUE'
153: AND bv.project_id = p_project_id;
154: ELSE
159: INTO x_margin_derived_from_code,
160: x_ci_ver_plan_prc_code,
161: x_request_id
162: FROM pa_proj_fp_options fpo,
163: pa_budget_versions bv
164: WHERE fpo.project_id = p_project_id
165: AND bv.current_working_flag = 'Y'
166: AND fpo.fin_plan_version_id = bv.budget_version_id
167: AND bv.approved_cost_plan_type_flag = 'Y';
188: INTO x_margin_derived_from_code,
189: x_ci_ver_plan_prc_code,
190: x_request_id
191: FROM pa_proj_fp_options fpo,
192: pa_budget_versions bv
193: WHERE bv.ci_id = p_ci_id
194: AND bv.version_type in ('COST','ALL')
195: AND fpo.fin_plan_version_id = bv.budget_version_id
196: AND bv.project_id = p_project_id;
206: SELECT bv.plan_processing_code,
207: bv.request_id
208: INTO x_ci_ver_plan_prc_code,
209: x_request_id
210: FROM pa_budget_versions bv
211: WHERE bv.ci_id = p_ci_id
212: AND bv.version_type = 'REVENUE'
213: AND bv.project_id = p_project_id;
214:
220: INTO x_margin_derived_from_code,
221: x_ci_ver_plan_prc_code,
222: x_request_id
223: FROM pa_proj_fp_options fpo,
224: pa_budget_versions bv
225: WHERE fpo.project_id = p_project_id
226: AND bv.current_working_flag = 'Y'
227: AND fpo.fin_plan_version_id = bv.budget_version_id
228: AND bv.approved_cost_plan_type_flag = 'Y';
395: pg.agreement_currency_code
396: INTO x_agreement_num,
397: x_agreement_amount,
398: x_agreement_currency_code
399: FROM pa_agreements_all pg, pa_budget_versions bv
400: WHERE
401: bv.project_id = p_project_id
402: and bv.ci_id = p_ci_id
403: and bv.agreement_id = pg.agreement_id
445:
446: PROCEDURE FP_CI_GET_VERSION_DETAILS
447: (
448: p_project_id IN NUMBER,
449: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
450: x_fin_plan_pref_code OUT NOCOPY pa_proj_fp_options.fin_plan_preference_code%TYPE, --File.Sql.39 bug 4440895
451: x_multi_curr_flag OUT NOCOPY pa_proj_fp_options.plan_in_multi_curr_flag%TYPE, --File.Sql.39 bug 4440895
452: x_fin_plan_level_code OUT NOCOPY pa_proj_fp_options.all_fin_plan_level_code%TYPE, --File.Sql.39 bug 4440895
453: x_resource_list_id OUT NOCOPY pa_proj_fp_options.all_resource_list_id%TYPE, --File.Sql.39 bug 4440895
453: x_resource_list_id OUT NOCOPY pa_proj_fp_options.all_resource_list_id%TYPE, --File.Sql.39 bug 4440895
454: x_time_phased_code OUT NOCOPY pa_proj_fp_options.all_time_phased_code%TYPE, --File.Sql.39 bug 4440895
455: x_uncategorized_flag OUT NOCOPY pa_resource_lists_all_bg.uncategorized_flag%TYPE, --File.Sql.39 bug 4440895
456: x_group_res_type_id OUT NOCOPY pa_resource_lists_all_bg.group_resource_type_id%TYPE, --File.Sql.39 bug 4440895
457: x_version_type OUT NOCOPY pa_budget_versions.version_type%TYPE, --File.Sql.39 bug 4440895
458: x_ci_id OUT NOCOPY pa_budget_versions.ci_id%TYPE, --File.Sql.39 bug 4440895
459: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
460: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
461: x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
454: x_time_phased_code OUT NOCOPY pa_proj_fp_options.all_time_phased_code%TYPE, --File.Sql.39 bug 4440895
455: x_uncategorized_flag OUT NOCOPY pa_resource_lists_all_bg.uncategorized_flag%TYPE, --File.Sql.39 bug 4440895
456: x_group_res_type_id OUT NOCOPY pa_resource_lists_all_bg.group_resource_type_id%TYPE, --File.Sql.39 bug 4440895
457: x_version_type OUT NOCOPY pa_budget_versions.version_type%TYPE, --File.Sql.39 bug 4440895
458: x_ci_id OUT NOCOPY pa_budget_versions.ci_id%TYPE, --File.Sql.39 bug 4440895
459: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
460: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
461: x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
462: )
525: x_resource_list_id,
526: x_time_phased_code,
527: x_version_type,
528: x_ci_id
529: FROM pa_budget_versions bv, pa_proj_fp_options po
530: WHERE
531: bv.budget_version_id = p_budget_version_id
532: AND po.fin_plan_version_id = bv.budget_version_id
533: AND po.project_id = p_project_id;
594: x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
595: )
596: IS
597: -- Local Variable Declaration
598: l_budget_version_id pa_budget_versions.budget_version_id%TYPE;
599:
600: --Defining Local variables for source version
601: l_s_version_id pa_budget_versions.budget_version_id%TYPE;
602: l_s_fin_plan_pref_code pa_proj_fp_options. fin_plan_preference_code%TYPE;
597: -- Local Variable Declaration
598: l_budget_version_id pa_budget_versions.budget_version_id%TYPE;
599:
600: --Defining Local variables for source version
601: l_s_version_id pa_budget_versions.budget_version_id%TYPE;
602: l_s_fin_plan_pref_code pa_proj_fp_options. fin_plan_preference_code%TYPE;
603: l_s_multi_curr_flag pa_proj_fp_options. plan_in_multi_curr_flag%TYPE;
604: l_s_time_phased_code pa_proj_fp_options. all_time_phased_code%TYPE;
605: l_s_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
605: l_s_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
606: l_s_fin_plan_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
607: l_s_uncategorized_flag pa_resource_lists_all_bg.uncategorized_flag %TYPE;
608: l_s_group_res_type_id pa_resource_lists_all_bg.group_resource_type_id%TYPE;
609: l_s_version_type pa_budget_versions.version_type%TYPE;
610: l_s_ci_id pa_budget_versions.ci_id%TYPE;
611:
612: --Defining Local variables for target version
613: l_t_version_id pa_budget_versions.budget_version_id%TYPE;
606: l_s_fin_plan_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
607: l_s_uncategorized_flag pa_resource_lists_all_bg.uncategorized_flag %TYPE;
608: l_s_group_res_type_id pa_resource_lists_all_bg.group_resource_type_id%TYPE;
609: l_s_version_type pa_budget_versions.version_type%TYPE;
610: l_s_ci_id pa_budget_versions.ci_id%TYPE;
611:
612: --Defining Local variables for target version
613: l_t_version_id pa_budget_versions.budget_version_id%TYPE;
614: l_t_fin_plan_pref_code pa_proj_fp_options. fin_plan_preference_code%TYPE;
609: l_s_version_type pa_budget_versions.version_type%TYPE;
610: l_s_ci_id pa_budget_versions.ci_id%TYPE;
611:
612: --Defining Local variables for target version
613: l_t_version_id pa_budget_versions.budget_version_id%TYPE;
614: l_t_fin_plan_pref_code pa_proj_fp_options. fin_plan_preference_code%TYPE;
615: l_t_multi_curr_flag pa_proj_fp_options. plan_in_multi_curr_flag%TYPE;
616: l_t_time_phased_code pa_proj_fp_options. all_time_phased_code%TYPE;
617: l_t_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
617: l_t_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
618: l_t_fin_plan_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
619: l_t_uncategorized_flag pa_resource_lists_all_bg.uncategorized_flag %TYPE;
620: l_t_group_res_type_id pa_resource_lists_all_bg.group_resource_type_id%TYPE;
621: l_t_version_type pa_budget_versions.version_type%TYPE;
622: l_t_ci_id pa_budget_versions.ci_id%TYPE;
623:
624: --Defining Local PL/SQL variables for source version
625: l_s_version_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
618: l_t_fin_plan_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
619: l_t_uncategorized_flag pa_resource_lists_all_bg.uncategorized_flag %TYPE;
620: l_t_group_res_type_id pa_resource_lists_all_bg.group_resource_type_id%TYPE;
621: l_t_version_type pa_budget_versions.version_type%TYPE;
622: l_t_ci_id pa_budget_versions.ci_id%TYPE;
623:
624: --Defining Local PL/SQL variables for source version
625: l_s_version_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
626: l_s_fin_plan_pref_code_tbl PA_PLSQL_DATATYPES.Char30TabTyp;
643: l_t_uncategorized_flag_tbl PA_PLSQL_DATATYPES.Char1TabTyp;
644: l_t_version_type_tbl PA_PLSQL_DATATYPES.Char30TabTyp;
645: l_t_group_res_type_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
646: l_t_ci_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
647: l_s_agreement_id pa_budget_versions.agreement_id%TYPE;
648: l_t_agreement_id pa_budget_versions.agreement_id%TYPE;
649: --Other Local Variables
650: l_merge_possible_code VARCHAR2(1);
651: l_raise_error_flag VARCHAR2(1);
644: l_t_version_type_tbl PA_PLSQL_DATATYPES.Char30TabTyp;
645: l_t_group_res_type_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
646: l_t_ci_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
647: l_s_agreement_id pa_budget_versions.agreement_id%TYPE;
648: l_t_agreement_id pa_budget_versions.agreement_id%TYPE;
649: --Other Local Variables
650: l_merge_possible_code VARCHAR2(1);
651: l_raise_error_flag VARCHAR2(1);
652: l_debug_mode VARCHAR2(30);
721:
722: SELECT name
723: INTO l_targ_pt_name
724: FROM pa_fin_plan_types_vl fin,
725: pa_budget_versions pbv
726: WHERE fin.fin_plan_type_id = pbv.fin_plan_type_id
727: AND pbv.budget_version_id= l_t_version_id;
728:
729: END IF;
993: --Added agreement check for P2 bug 2724156
994: IF (l_s_version_type IN ('REVENUE','ALL') AND l_t_version_type IN ('REVENUE','ALL')) THEN
995: SELECT NVL(agreement_id,-99)
996: INTO l_s_agreement_id
997: FROM pa_budget_versions
998: where budget_version_id = l_s_version_id;
999:
1000: SELECT NVL(agreement_id,-100)
1001: INTO l_t_agreement_id
998: where budget_version_id = l_s_version_id;
999:
1000: SELECT NVL(agreement_id,-100)
1001: INTO l_t_agreement_id
1002: FROM pa_budget_versions
1003: where budget_version_id = l_t_version_id;
1004:
1005: IF l_debug_mode = 'Y' THEN
1006: pa_debug.g_err_stage:='Source Agr: ' || l_s_agreement_id;
1058: x_merge_possible_code_tbl (i) := l_merge_possible_code;
1059: x_return_status := FND_API.G_RET_STS_ERROR;
1060: ----DBMS_OUTPUT.PUT_LINE('FP_CI_CHECK_MERGE_POSSIBLE - 36****');
1061: select ci_id into l_token_ci_id from
1062: pa_budget_versions where
1063: budget_version_id = l_s_version_id;
1064:
1065: l_chg_doc_token := null;
1066: begin
1528:
1529: cursor check_ci_funding_level is
1530: select DECODE(bv.version_type,'REVENUE',revenue_fin_plan_level_code,
1531: 'ALL',all_fin_plan_level_code,null)
1532: from pa_budget_versions bv,
1533: pa_proj_fp_options po,
1534: pa_ci_impacts pci,
1535: pa_projects_all ppa
1536: where bv.project_id = p_project_id
1643: select 'Y' from dual
1644: where exists (
1645: select 'x'
1646: from pa_summary_project_fundings fu,
1647: pa_budget_versions bv
1648: where fu.agreement_id = p_agreement_id
1649: and bv.project_id = fu.project_id
1650: and bv.agreement_id = p_agreement_id);
1651: BEGIN
1715: select 'Y' from dual
1716: where exists (
1717: select 'x'
1718: from pa_summary_project_fundings fu,
1719: pa_budget_versions bv
1720: where fu.agreement_id = p_agreement_id
1721: and bv.project_id = fu.project_id
1722: and bv.agreement_id = p_agreement_id);
1723: BEGIN
1775: -- default ci from the current working version.
1776: ===============================================================================*/
1777:
1778: PROCEDURE Is_Create_CI_Version_Allowed
1779: ( p_project_id IN pa_budget_versions.project_id%TYPE
1780: ,p_fin_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE
1781: ,p_version_type IN pa_budget_versions.version_type%TYPE
1782: ,p_impacted_task_id IN pa_tasks.task_id%TYPE
1783: ,x_version_allowed_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1777:
1778: PROCEDURE Is_Create_CI_Version_Allowed
1779: ( p_project_id IN pa_budget_versions.project_id%TYPE
1780: ,p_fin_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE
1781: ,p_version_type IN pa_budget_versions.version_type%TYPE
1782: ,p_impacted_task_id IN pa_tasks.task_id%TYPE
1783: ,x_version_allowed_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1784: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1785: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1808:
1809: -- jwhite: Added for Plannable Task Dev Effort ------------------
1810: -- 01-JUL-2003 Default ci from current working version
1811: l_ci_apprv_bv_option_id pa_proj_fp_options.proj_fp_options_id%TYPE :=NULL;
1812: l_ci_apprv_cw_bv_id pa_budget_versions.budget_version_id%TYPE :=NULL;
1813:
1814: -- ---------------------------------------------------------------------------
1815:
1816:
2317: END IsFpAutoBaselineEnabled;
2318:
2319: /*
2320: This API returns budget version information
2321: from the pa budget versions table and
2322: also returns information regarding project
2323: currency to show on the OA Page
2324: */
2325: PROCEDURE GET_BUDGET_VERSION_INFO
2374: x_approved_rev_flag,
2375: x_fin_plan_type_id,
2376: x_plan_type_name,
2377: x_plan_class_code
2378: FROM pa_budget_versions bv, pa_fin_plan_types_tl patl,pa_fin_plan_types_b pftb
2379: WHERE bv.budget_version_id = p_budget_version_id
2380: and bv.project_id = p_project_id
2381: and bv.fin_plan_type_id = patl.fin_plan_type_id
2382: and patl.fin_plan_type_id = pftb.fin_plan_type_id
2454:
2455: PROCEDURE CHK_APRV_CUR_WORKING_BV_EXISTS
2456: ( p_project_id IN pa_projects_all.project_id%TYPE
2457: ,p_fin_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE
2458: ,p_version_type IN pa_budget_versions.version_type%TYPE
2459: ,x_cur_work_bv_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE --File.Sql.39 bug 4440895
2460: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2461: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2462: ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
2455: PROCEDURE CHK_APRV_CUR_WORKING_BV_EXISTS
2456: ( p_project_id IN pa_projects_all.project_id%TYPE
2457: ,p_fin_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE
2458: ,p_version_type IN pa_budget_versions.version_type%TYPE
2459: ,x_cur_work_bv_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE --File.Sql.39 bug 4440895
2460: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2461: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2462: ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
2463:
2470: l_baseline_funding_flag pa_projects_all.baseline_funding_flag%TYPE;
2471: l_msg_name fnd_new_messages.message_name%TYPE;
2472: cursor c1 is
2473: Select budget_version_id
2474: from pa_budget_versions bv
2475: where bv.project_id = p_project_id
2476: and bv.fin_plan_type_id = p_fin_plan_type_id
2477: and bv.version_type = p_version_type
2478: and bv.current_working_flag = 'Y'
2572:
2573: --In FP M merge is not possible only when the source time phasing is PA and target time phasing is GL or vice-versa
2574: --IN other cases merge will be possible. Modified the API for this change
2575: PROCEDURE COMPARE_SOURCE_TARGET_VER_ATTR
2576: ( p_source_bv_id IN pa_budget_versions.budget_version_id%TYPE
2577: ,p_target_bv_id IN pa_budget_versions.budget_version_id%TYPE
2578: ,x_attributes_same_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2579: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2580: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2573: --In FP M merge is not possible only when the source time phasing is PA and target time phasing is GL or vice-versa
2574: --IN other cases merge will be possible. Modified the API for this change
2575: PROCEDURE COMPARE_SOURCE_TARGET_VER_ATTR
2576: ( p_source_bv_id IN pa_budget_versions.budget_version_id%TYPE
2577: ,p_target_bv_id IN pa_budget_versions.budget_version_id%TYPE
2578: ,x_attributes_same_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2579: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2580: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2581: ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
2677: SELECT
2678: count(*)
2679: INTO
2680: x_no_of_bv_versions
2681: FROM pa_budget_versions bv
2682: WHERE bv.version_name = p_budget_version_name
2683: and bv.project_id = p_project_id
2684: and bv.fin_plan_type_id = p_fin_plan_type_id
2685: and bv.version_type = p_version_type;
2688: SELECT
2689: bv.budget_version_id
2690: INTO
2691: x_budget_version_id
2692: FROM pa_budget_versions bv
2693: WHERE bv.version_name = p_budget_version_name
2694: and bv.project_id = p_project_id
2695: and bv.fin_plan_type_id = p_fin_plan_type_id
2696: and bv.version_type = p_version_type;
2726: PROCEDURE FP_CI_CHECK_COPY_POSSIBLE
2727: ( p_source_plan_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE
2728: ,p_source_time_phased_code IN pa_proj_fp_options.all_time_phased_code%TYPE
2729: ,p_source_resource_list_id IN pa_proj_fp_options.all_resource_list_id%TYPE
2730: ,p_source_version_type IN pa_budget_versions.version_type%TYPE
2731: ,p_project_id IN pa_budget_versions.project_id%TYPE
2732: ,p_s_ci_id IN pa_budget_versions.ci_id%TYPE
2733: ,p_multiple_plan_types_flag IN VARCHAR2
2734: ,x_copy_possible_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2727: ( p_source_plan_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE
2728: ,p_source_time_phased_code IN pa_proj_fp_options.all_time_phased_code%TYPE
2729: ,p_source_resource_list_id IN pa_proj_fp_options.all_resource_list_id%TYPE
2730: ,p_source_version_type IN pa_budget_versions.version_type%TYPE
2731: ,p_project_id IN pa_budget_versions.project_id%TYPE
2732: ,p_s_ci_id IN pa_budget_versions.ci_id%TYPE
2733: ,p_multiple_plan_types_flag IN VARCHAR2
2734: ,x_copy_possible_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2735: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2728: ,p_source_time_phased_code IN pa_proj_fp_options.all_time_phased_code%TYPE
2729: ,p_source_resource_list_id IN pa_proj_fp_options.all_resource_list_id%TYPE
2730: ,p_source_version_type IN pa_budget_versions.version_type%TYPE
2731: ,p_project_id IN pa_budget_versions.project_id%TYPE
2732: ,p_s_ci_id IN pa_budget_versions.ci_id%TYPE
2733: ,p_multiple_plan_types_flag IN VARCHAR2
2734: ,x_copy_possible_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2735: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2736: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2745: l_token_ci_id varchar2(150);
2746: l_token_v_type VARCHAR2(30);
2747: l_time_phase_code_flag VARCHAR2(1);
2748: l_baseline_funding_Flag pa_projects_All.baseline_funding_flag%type;
2749: l_s_rev_bv_id pa_budget_versions.budget_version_id%type;
2750: l_count number;
2751: l_s_bv_fp_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
2752: l_ci_number pa_control_items.ci_number%type;
2753: l_ci_type_name pa_ci_types_tl.short_name%type;
2754: l_s_bv_time_phased_code pa_proj_fp_options.revenue_time_phased_code%type;
2755: l_s_bv_resource_list_id pa_proj_fp_options.revenue_resource_list_id%type;
2756:
2757: l_ci_aprv_bv_option_id pa_proj_fp_options.proj_fp_options_id%TYPE;
2758: l_ci_aprv_cw_bv_id pa_budget_versions.budget_version_id%TYPE;
2759: l_ci_aprv_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
2760: l_return_status VARCHAR2(1) := NULL;
2761: l_msg_count NUMBER := NULL;
2762: l_msg_data VARCHAR2(2000) := NULL;
2978: l_s_bv_fp_level_code,
2979: l_s_bv_time_phased_code,
2980: l_s_bv_resource_list_id
2981: FROM pa_proj_fp_options po,
2982: pa_budget_versions bv
2983: WHERE
2984: bv.project_id = p_project_id and
2985: bv.ci_id = p_s_ci_id and
2986: bv.version_type = 'REVENUE' and
3129: SELECT
3130: count(*)
3131: INTO
3132: l_no_of_bv_versions
3133: FROM pa_budget_versions bv
3134: WHERE bv.project_id = p_project_id
3135: and bv.ci_id = p_ci_id;
3136:
3137: IF (l_no_of_bv_versions = 0) THEN
3164: Plan version has the same Plan Settings as the Current Working Approved Budget.
3165: If the Plan settings are different, then appropriate error messages are raised. */
3166:
3167: PROCEDURE Fp_Ci_Impact_Submit_Chk
3168: ( p_project_id IN pa_budget_versions.project_id%TYPE
3169: ,p_ci_id IN pa_budget_versions.ci_id%TYPE
3170: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3171: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3172: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3165: If the Plan settings are different, then appropriate error messages are raised. */
3166:
3167: PROCEDURE Fp_Ci_Impact_Submit_Chk
3168: ( p_project_id IN pa_budget_versions.project_id%TYPE
3169: ,p_ci_id IN pa_budget_versions.ci_id%TYPE
3170: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3171: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3172: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3173: IS
3175: CURSOR cur_ci_version_dtl IS
3176: SELECT budget_version_id
3177: ,version_type
3178: ,fin_plan_type_id
3179: FROM pa_budget_versions
3180: WHERE project_id = p_project_id AND
3181: nvl(ci_id,-1) = p_ci_id;
3182:
3183: l_ci_version_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
3189:
3190: --Added by Xin Liu. For enhancement.
3191: l_source_id_tbl SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
3192:
3193: l_target_version_id pa_budget_versions.budget_version_id%TYPE;
3194: l_source_version_id pa_budget_versions.budget_version_id%TYPE;
3195: --Done by Xin Liu for enhancement
3196:
3197: l_msg_count NUMBER := 0;
3190: --Added by Xin Liu. For enhancement.
3191: l_source_id_tbl SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
3192:
3193: l_target_version_id pa_budget_versions.budget_version_id%TYPE;
3194: l_source_version_id pa_budget_versions.budget_version_id%TYPE;
3195: --Done by Xin Liu for enhancement
3196:
3197: l_msg_count NUMBER := 0;
3198: l_data VARCHAR2(2000);
3366: ==================================================================*/
3367: --Bug 3550073. Included x_upd_cost_impact_allowed and x_upd_rev_impact_allowed
3368: PROCEDURE FP_CI_VALIDATE_UPDATE_IMPACT
3369: (
3370: p_project_id IN pa_budget_versions.project_id%TYPE,
3371: p_ci_id IN pa_control_items.ci_id%TYPE,
3372: p_source_version_id IN pa_budget_versions.budget_version_id%TYPE,
3373: p_target_version_id IN pa_budget_versions.budget_version_id%TYPE,
3374: x_upd_cost_impact_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3368: PROCEDURE FP_CI_VALIDATE_UPDATE_IMPACT
3369: (
3370: p_project_id IN pa_budget_versions.project_id%TYPE,
3371: p_ci_id IN pa_control_items.ci_id%TYPE,
3372: p_source_version_id IN pa_budget_versions.budget_version_id%TYPE,
3373: p_target_version_id IN pa_budget_versions.budget_version_id%TYPE,
3374: x_upd_cost_impact_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3375: x_upd_rev_impact_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3376: x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3369: (
3370: p_project_id IN pa_budget_versions.project_id%TYPE,
3371: p_ci_id IN pa_control_items.ci_id%TYPE,
3372: p_source_version_id IN pa_budget_versions.budget_version_id%TYPE,
3373: p_target_version_id IN pa_budget_versions.budget_version_id%TYPE,
3374: x_upd_cost_impact_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3375: x_upd_rev_impact_allowed OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3376: x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3377: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3384: l_msg_data VARCHAR2(2000);
3385: l_msg_index_out NUMBER;
3386: l_debug_mode VARCHAR2(1);
3387:
3388: l_approved_cost_flag pa_budget_versions.approved_cost_plan_type_flag%TYPE;
3389: l_approved_rev_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
3390: l_ci_id pa_budget_versions.ci_id%TYPE;
3391: l_second_bv_id pa_budget_versions.budget_version_id%TYPE;
3392:
3385: l_msg_index_out NUMBER;
3386: l_debug_mode VARCHAR2(1);
3387:
3388: l_approved_cost_flag pa_budget_versions.approved_cost_plan_type_flag%TYPE;
3389: l_approved_rev_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
3390: l_ci_id pa_budget_versions.ci_id%TYPE;
3391: l_second_bv_id pa_budget_versions.budget_version_id%TYPE;
3392:
3393: l_count NUMBER := 0;
3386: l_debug_mode VARCHAR2(1);
3387:
3388: l_approved_cost_flag pa_budget_versions.approved_cost_plan_type_flag%TYPE;
3389: l_approved_rev_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
3390: l_ci_id pa_budget_versions.ci_id%TYPE;
3391: l_second_bv_id pa_budget_versions.budget_version_id%TYPE;
3392:
3393: l_count NUMBER := 0;
3394: l_merged_count NUMBER := 0;
3387:
3388: l_approved_cost_flag pa_budget_versions.approved_cost_plan_type_flag%TYPE;
3389: l_approved_rev_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
3390: l_ci_id pa_budget_versions.ci_id%TYPE;
3391: l_second_bv_id pa_budget_versions.budget_version_id%TYPE;
3392:
3393: l_count NUMBER := 0;
3394: l_merged_count NUMBER := 0;
3395: l_module_name VARCHAR2(100) := 'pa.plsql.Pa_Fp_Control_Items_Utils';
3451: NVL(approved_rev_plan_type_flag,'N')
3452: INTO
3453: l_approved_cost_flag,
3454: l_approved_rev_flag
3455: FROM pa_budget_versions
3456: WHERE budget_version_id = p_target_version_id
3457: AND project_id = p_project_id;
3458:
3459: x_upd_cost_impact_allowed:='N';
3462: IF(l_approved_cost_flag = 'Y' OR l_approved_rev_flag = 'Y') THEN
3463: IF p_ci_id IS NULL THEN
3464: SELECT ci_id
3465: INTO l_ci_id
3466: FROM pa_budget_versions
3467: WHERE budget_version_id = p_source_version_id
3468: AND project_id = p_project_id;
3469: ELSE
3470: l_ci_id:=p_ci_id;
3559: END FP_CI_VALIDATE_UPDATE_IMPACT;
3560:
3561: procedure chk_res_resgrp_mismatch(
3562: p_project_id in number,
3563: p_s_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
3564: p_s_fin_plan_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE,
3565: p_t_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
3566: p_t_fin_plan_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE,
3567: p_calling_mode in varchar2,
3561: procedure chk_res_resgrp_mismatch(
3562: p_project_id in number,
3563: p_s_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
3564: p_s_fin_plan_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE,
3565: p_t_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
3566: p_t_fin_plan_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE,
3567: p_calling_mode in varchar2,
3568: x_res_resgr_mismatch_flag OUT NOCOPY varchar2, --File.Sql.39 bug 4440895
3569: x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3604: l_prj_rlm_id_target NUMBER;
3605: l_target_prj_plan_level varchar2(1);
3606: l_source_fp_opt_id NUMBER;
3607: l_target_fp_opt_id NUMBER;
3608: l_source_ver_type pa_budget_versions.version_type%type;
3609: l_target_ver_type pa_budget_versions.version_type%type;
3610: l_top_task_plan_level pa_fp_elements.top_task_planning_level%type;
3611: BEGIN
3612: l_count := 0;
3605: l_target_prj_plan_level varchar2(1);
3606: l_source_fp_opt_id NUMBER;
3607: l_target_fp_opt_id NUMBER;
3608: l_source_ver_type pa_budget_versions.version_type%type;
3609: l_target_ver_type pa_budget_versions.version_type%type;
3610: l_top_task_plan_level pa_fp_elements.top_task_planning_level%type;
3611: BEGIN
3612: l_count := 0;
3613: x_msg_count := 0;
3619: bv.version_type into
3620: l_source_fp_opt_id,
3621: l_source_ver_type
3622: from pa_proj_fp_options o,
3623: pa_budget_versions bv
3624: where
3625: bv.budget_version_id = p_s_budget_version_id and
3626: bv.fin_plan_type_id = o.fin_plan_type_id and
3627: o.project_id = p_project_id and
3632: bv.version_type into
3633: l_target_fp_opt_id,
3634: l_target_ver_type
3635: from pa_proj_fp_options o,
3636: pa_budget_versions bv
3637: where
3638: bv.budget_version_id = p_t_budget_version_id and
3639: bv.fin_plan_type_id = o.fin_plan_type_id and
3640: o.project_id = p_project_id and
3952: END chk_res_resgrp_mismatch;
3953:
3954: procedure chk_tsk_plan_level_mismatch(
3955: p_project_id in number,
3956: p_s_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
3957: p_t_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
3958: p_calling_mode in varchar2,
3959: x_tsk_plan_level_mismatch OUT NOCOPY varchar2, --File.Sql.39 bug 4440895
3960: x_s_task_id_tbl OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
3953:
3954: procedure chk_tsk_plan_level_mismatch(
3955: p_project_id in number,
3956: p_s_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
3957: p_t_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
3958: p_calling_mode in varchar2,
3959: x_tsk_plan_level_mismatch OUT NOCOPY varchar2, --File.Sql.39 bug 4440895
3960: x_s_task_id_tbl OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
3961: x_t_task_id_tbl OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
4000: l_prj_rlm_id_target NUMBER;
4001: l_target_prj_plan_level varchar2(1);
4002: l_source_fp_opt_id NUMBER;
4003: l_target_fp_opt_id NUMBER;
4004: l_source_ver_type pa_budget_versions.version_type%type;
4005: l_target_ver_type pa_budget_versions.version_type%type;
4006: l_top_task_plan_level pa_fp_elements.top_task_planning_level%type;
4007: l_source_task_id number;
4008: l_source_plan_level varchar2(30);
4001: l_target_prj_plan_level varchar2(1);
4002: l_source_fp_opt_id NUMBER;
4003: l_target_fp_opt_id NUMBER;
4004: l_source_ver_type pa_budget_versions.version_type%type;
4005: l_target_ver_type pa_budget_versions.version_type%type;
4006: l_top_task_plan_level pa_fp_elements.top_task_planning_level%type;
4007: l_source_task_id number;
4008: l_source_plan_level varchar2(30);
4009: l_target_plan_level varchar2(30);
4031: bv.version_type into
4032: l_source_fp_opt_id,
4033: l_source_ver_type
4034: from pa_proj_fp_options o,
4035: pa_budget_versions bv
4036: where
4037: bv.budget_version_id = p_s_budget_version_id and
4038: bv.fin_plan_type_id = o.fin_plan_type_id and
4039: o.project_id = p_project_id and
4050: bv.version_type into
4051: l_target_fp_opt_id,
4052: l_target_ver_type
4053: from pa_proj_fp_options o,
4054: pa_budget_versions bv
4055: where
4056: bv.budget_version_id = p_t_budget_version_id and
4057: bv.fin_plan_type_id = o.fin_plan_type_id and
4058: o.project_id = p_project_id and
4357:
4358:
4359: PROCEDURE get_summary_data
4360: ( p_project_id IN NUMBER
4361: ,p_cost_version_id IN pa_budget_versions.budget_version_id%TYPE
4362: ,p_revenue_version_id IN pa_budget_versions.budget_version_id%TYPE
4363: ,p_page_context IN VARCHAR2
4364: ,p_calling_mode IN VARCHAR2 DEFAULT 'APPROVED' --Bug 5278200 kchaitan
4365: ,x_context OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4358:
4359: PROCEDURE get_summary_data
4360: ( p_project_id IN NUMBER
4361: ,p_cost_version_id IN pa_budget_versions.budget_version_id%TYPE
4362: ,p_revenue_version_id IN pa_budget_versions.budget_version_id%TYPE
4363: ,p_page_context IN VARCHAR2
4364: ,p_calling_mode IN VARCHAR2 DEFAULT 'APPROVED' --Bug 5278200 kchaitan
4365: ,x_context OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4366: ,x_summary_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_150_TBL_TYPE --File.Sql.39 bug 4440895
4394: l_continue_flag VARCHAR2(1) := 'Y';
4395: l_row_count NUMBER := 0;
4396: l_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE;
4397: l_module_name VARCHAR2(30) := 'ctrl_itm_utls.plan_summ_data';
4398: l_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4399: l_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4400: l_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4401: l_report_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4402: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4397: l_module_name VARCHAR2(30) := 'ctrl_itm_utls.plan_summ_data';
4398: l_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4399: l_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4400: l_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4401: l_report_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4402: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4403: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4404: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4405: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4398: l_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4399: l_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4400: l_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4401: l_report_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4402: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4403: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4404: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4405: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4406:
4399: l_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4400: l_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4401: l_report_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4402: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4403: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4404: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4405: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4406:
4407: l_cb_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4400: l_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4401: l_report_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4402: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4403: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4404: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4405: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4406:
4407: l_cb_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4408: l_cb_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4401: l_report_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4402: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4403: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4404: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4405: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4406:
4407: l_cb_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4408: l_cb_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4409: l_cb_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4403: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4404: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4405: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4406:
4407: l_cb_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4408: l_cb_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4409: l_cb_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4410: l_cb_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4411:
4404: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4405: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4406:
4407: l_cb_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4408: l_cb_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4409: l_cb_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4410: l_cb_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4411:
4412: l_cw_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4405: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4406:
4407: l_cb_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4408: l_cb_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4409: l_cb_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4410: l_cb_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4411:
4412: l_cw_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4413: l_cw_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4406:
4407: l_cb_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4408: l_cb_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4409: l_cb_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4410: l_cb_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4411:
4412: l_cw_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4413: l_cw_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4414: l_cw_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4408: l_cb_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4409: l_cb_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4410: l_cb_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4411:
4412: l_cw_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4413: l_cw_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4414: l_cw_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4415: l_cw_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4416:
4409: l_cb_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4410: l_cb_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4411:
4412: l_cw_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4413: l_cw_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4414: l_cw_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4415: l_cw_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4416:
4417: l_lookup_code_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
4410: l_cb_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4411:
4412: l_cw_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4413: l_cw_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4414: l_cw_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4415: l_cw_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4416:
4417: l_lookup_code_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
4418: l_fin_plan_type_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
4411:
4412: l_cw_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4413: l_cw_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4414: l_cw_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4415: l_cw_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
4416:
4417: l_lookup_code_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
4418: l_fin_plan_type_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
4419: l_fin_plan_preference_code_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
4426: l_set_cb_url_flag VARCHAR2(1) := 'Y';
4427: l_set_cpb_url_flag VARCHAR2(1) := 'Y';
4428: l_set_ccw_url_flag VARCHAR2(1) := 'Y';
4429: l_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4430: l_pa_cw_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4431: l_pa_cw_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4432: l_pa_ob_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4433: l_pa_ob_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4434: l_pa_cb_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4427: l_set_cpb_url_flag VARCHAR2(1) := 'Y';
4428: l_set_ccw_url_flag VARCHAR2(1) := 'Y';
4429: l_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4430: l_pa_cw_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4431: l_pa_cw_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4432: l_pa_ob_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4433: l_pa_ob_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4434: l_pa_cb_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4435: l_pa_cb_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4428: l_set_ccw_url_flag VARCHAR2(1) := 'Y';
4429: l_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4430: l_pa_cw_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4431: l_pa_cw_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4432: l_pa_ob_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4433: l_pa_ob_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4434: l_pa_cb_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4435: l_pa_cb_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4436: l_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4429: l_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4430: l_pa_cw_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4431: l_pa_cw_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4432: l_pa_ob_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4433: l_pa_ob_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4434: l_pa_cb_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4435: l_pa_cb_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4436: l_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4437: l_version_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
4430: l_pa_cw_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4431: l_pa_cw_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4432: l_pa_ob_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4433: l_pa_ob_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4434: l_pa_cb_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4435: l_pa_cb_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4436: l_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4437: l_version_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
4438: l_budget_version_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
4431: l_pa_cw_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4432: l_pa_ob_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4433: l_pa_ob_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4434: l_pa_cb_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4435: l_pa_cb_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4436: l_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4437: l_version_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
4438: l_budget_version_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
4439: l_next VARCHAR2(30);
4432: l_pa_ob_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4433: l_pa_ob_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4434: l_pa_cb_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4435: l_pa_cb_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4436: l_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4437: l_version_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
4438: l_budget_version_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
4439: l_next VARCHAR2(30);
4440: l_context VARCHAR2(30);
4438: l_budget_version_id_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
4439: l_next VARCHAR2(30);
4440: l_context VARCHAR2(30);
4441:
4442: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4443:
4444: l_report_labor_hrs_code pa_proj_fp_options.report_labor_hrs_from_code%TYPE; /* Bug 4038253 */
4445: l_pref_code_for_pt_of_ver pa_proj_fp_options.fin_plan_preference_code%TYPE; /* Bug 4038253 */
4446:
4472: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4473: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4474: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4475: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4476: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS
4477: SELECT nvl(sum(decode(pbv.version_type,
4478: c_report_version_type,
4479: nvl(pbv.labor_quantity,0),0)),0),
4480: nvl(sum(decode(pbv.version_type,
4487: nvl(pbv.total_project_raw_cost,0))
4488: ,0)),0) as cost,
4489: nvl(sum(decode(pbv.fin_plan_type_id,
4490: c_ar_fin_plan_type_id,nvl(pbv.total_project_revenue,0),0)),0)
4491: FROM pa_budget_versions pbv
4492: WHERE pbv.project_id = c_project_id
4493: AND pbv.ci_id is null
4494: AND nvl(pbv.current_original_flag,'N') = 'Y'
4495: --Below 2 lines commented for bug 5278200
4502: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4503: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4504: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4505: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4506: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS
4507: SELECT nvl(sum(decode(pbv.version_type,
4508: c_report_version_type,
4509: nvl(pbv.labor_quantity,0),0)),0),
4510: nvl(sum(decode(pbv.version_type,
4518: ,0)),0) as cost,
4519: nvl(sum(decode(pbv.fin_plan_type_id,
4520: c_ar_fin_plan_type_id,
4521: nvl(pbv.total_project_revenue,0),0)),0)
4522: FROM pa_budget_versions pbv
4523: WHERE pbv.project_id = c_project_id
4524: AND pbv.ci_id is null
4525: AND nvl(pbv.current_flag,'N') = 'Y'
4526: --Below 2 lines commented for bug 5278200
4533: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4534: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4535: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4536: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4537: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS
4538: SELECT nvl(sum(decode(pbv.version_type,
4539: c_report_version_type,
4540: nvl(pbv.labor_quantity,0),0)),0),
4541: nvl(sum(decode(pbv.version_type,
4548: nvl(pbv.total_project_raw_cost,0)),0)),0) as cost,
4549: nvl(sum(decode(pbv.fin_plan_type_id,
4550: c_ar_fin_plan_type_id,
4551: nvl(pbv.total_project_revenue,0),0)),0)
4552: FROM pa_budget_versions pbv
4553: WHERE pbv.project_id = c_project_id
4554: AND pbv.ci_id is null
4555: AND nvl(pbv.current_working_flag,'N') = 'Y'
4556: --Below 2 lines commented for bug 5278200
4571: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4572: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4573: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4574: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4575: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS -- Raja review
4576: Select nvl(sum(decode(c_report_version_type,
4577: 'COST', decode(merge.version_type, 'COST',nvl(merge.impl_quantity,0),0),
4578: 'REVENUE', decode(merge.version_type, 'REVENUE',nvl(merge.impl_quantity,0),0),
4579: 'ALL', decode(merge.version_type, 'COST',nvl(merge.impl_quantity,0),0)
4592: 'B',nvl(merge.impl_proj_burdened_cost,0),
4593: nvl(merge.impl_proj_raw_cost,0))),0) as cost,
4594: nvl(sum(nvl(merge.impl_proj_revenue,0)),0)
4595: from pa_fp_merged_ctrl_items merge,
4596: pa_budget_versions pbv
4597: where pbv.project_id = c_project_id
4598: and pbv.fin_plan_type_id in (c_ac_fin_plan_type_id, c_ar_fin_plan_type_id)
4599: and pbv.current_working_flag = 'Y'
4600: and merge.project_id = c_project_id
4602: and pbv.ci_id is null
4603: -- and pbv.budget_status_code in ('S','W') -- Bug#3815378
4604: -- Added by Raja, filter all the ci versions included/copied in current baseline version
4605: and not exists(select 1
4606: from pa_fp_merged_ctrl_items merge1, pa_budget_versions pbv1
4607: where pbv1.project_id = c_project_id
4608: and pbv1.fin_plan_type_id in (c_ac_fin_plan_type_id,
4609: c_ar_fin_plan_type_id)
4610: and pbv1.budget_status_code = 'B'
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)
4636: 'B',nvl(merge.impl_proj_burdened_cost,0),
4637: nvl(merge.impl_proj_raw_cost,0))),0) as cost,
4638: nvl(sum(nvl(merge.impl_proj_revenue,0)),0)
4639: from pa_fp_merged_ctrl_items merge,
4640: pa_budget_versions pbv
4641: where pbv.project_id = c_project_id
4642: and pbv.fin_plan_type_id in (c_ac_fin_plan_type_id, c_ar_fin_plan_type_id)
4643: and pbv.current_working_flag = 'Y'
4644: and merge.project_id = c_project_id
4658: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4659: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4660: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4661: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4662: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS -- Raja review
4663: Select nvl(sum(decode(c_report_version_type,
4664: 'COST', decode(merge.version_type, 'COST',nvl(merge.impl_quantity,0),0),
4665: 'REVENUE', decode(merge.version_type, 'REVENUE',nvl(merge.impl_quantity,0),0),
4666: 'ALL', decode(merge.version_type, 'COST',nvl(merge.impl_quantity,0),0)
4678: ,nvl(merge.impl_proj_burdened_cost,0)
4679: ,nvl(merge.impl_proj_raw_cost,0))),0) as cost,
4680: nvl(sum(nvl(merge.impl_proj_revenue,0)),0)
4681: from pa_fp_merged_ctrl_items merge,
4682: pa_budget_versions pbv
4683: where merge.plan_version_id = pbv.budget_version_id
4684: and pbv.fin_plan_type_id in (c_ac_fin_plan_type_id, c_ar_fin_plan_type_id)
4685: and pbv.current_flag = 'Y'
4686: and pbv.project_id = c_project_id
4689: and pbv.budget_status_code = 'B'
4690: and merge.inclusion_method_code = 'COPIED' -- Bug 3882985
4691: /* Raja filter all the change orders that have been included/copied in the original baseline version */
4692: and not exists(select 1
4693: from pa_fp_merged_ctrl_items merge1, pa_budget_versions pbv1
4694: where pbv1.project_id = c_project_id
4695: and pbv1.fin_plan_type_id in (c_ac_fin_plan_type_id,
4696: c_ar_fin_plan_type_id)
4697: and pbv1.current_original_flag = 'Y'
4730: CURSOR c_change_documents_status
4731: (c_project_id pa_projects_all.project_id%TYPE,
4732: c_system_status_code pa_ci_statuses_v.project_system_status_code%TYPE,
4733: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4734: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE,
4735: c_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE) IS -- Raja review
4736: SELECT (cost_query.people_effort + revenue_query.people_effort),
4737: (cost_query.equipment_effort + revenue_query.equipment_effort),
4738: (cost_query.cost + revenue_query.cost),
4731: (c_project_id pa_projects_all.project_id%TYPE,
4732: c_system_status_code pa_ci_statuses_v.project_system_status_code%TYPE,
4733: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4734: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE,
4735: c_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE) IS -- Raja review
4736: SELECT (cost_query.people_effort + revenue_query.people_effort),
4737: (cost_query.equipment_effort + revenue_query.equipment_effort),
4738: (cost_query.cost + revenue_query.cost),
4739: (cost_query.revenue + revenue_query.revenue)
4757: ,PBV.LABOR_QUANTITY AS PEOPLE_EFFORT
4758: ,PBV.EQUIPMENT_QUANTITY AS EQUIPMENT_EFFORT
4759: ,PBV.TOTAL_PROJECT_RAW_COST AS RAW_COST
4760: ,PBV.TOTAL_PROJECT_BURDENED_COST AS BURDENED_COST
4761: FROM pa_budget_versions pbv
4762: ,pa_control_items pci
4763: ,pa_project_statuses pcs
4764: ,pa_ci_types_vl pct
4765: WHERE PBV.CI_ID = PCI.CI_ID
4770: where pfca.project_id = c_project_id
4771: and pfca.project_system_status_code = c_system_status_code
4772: and pfca.ci_version_type in ('COST', 'ALL')
4773: and not exists(select 1
4774: from pa_fp_merged_ctrl_items merge1, pa_budget_versions pbv1
4775: where merge1.project_id = c_project_id
4776: and merge1.ci_id = pfca.ci_id
4777: and merge1.ci_plan_version_id = pfca.ci_version_id
4778: and merge1.version_type = 'COST'
4821: ,PBV.LABOR_QUANTITY AS PEOPLE_EFFORT
4822: ,PBV.EQUIPMENT_QUANTITY AS EQUIPMENT_EFFORT
4823: ,PBV.TOTAL_PROJECT_REVENUE AS REVENUE
4824: ,nvl(PBV.REV_PARTIALLY_IMPL_FLAG,'N') AS REV_PARTIALLY_IMPL_FLAG
4825: FROM pa_budget_versions pbv
4826: ,pa_control_items pci
4827: ,pa_project_statuses pcs
4828: ,pa_ci_types_vl pct
4829: WHERE PBV.CI_ID = PCI.CI_ID
4835: and pfca.project_system_status_code = c_system_status_code
4836: and pfca.ci_version_type in ('REVENUE', 'ALL')
4837: and (pfca.REV_PARTIALLY_IMPL_FLAG = 'Y' OR
4838: not exists(select 1
4839: from pa_fp_merged_ctrl_items merge1, pa_budget_versions pbv1
4840: where merge1.project_id = c_project_id
4841: and merge1.ci_id = pfca.ci_id
4842: and merge1.ci_plan_version_id = pfca.ci_version_id
4843: and merge1.version_type = 'REVENUE'
4848: and pbv1.approved_rev_plan_type_flag = 'Y'))) revenue_query;
4849:
4850: --Bug 5278200Added extra parameter
4851: cursor c_url_original_baseline(c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4852: c_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE,
4853: c_version_type pa_budget_versions.version_type%TYPE) IS
4854: SELECT BUDGET_VERSION_ID,VERSION_TYPE
4855: FROM PA_BUDGET_VERSIONS
4856: WHERE BUDGET_STATUS_CODE = 'B'
4849:
4850: --Bug 5278200Added extra parameter
4851: cursor c_url_original_baseline(c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4852: c_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE,
4853: c_version_type pa_budget_versions.version_type%TYPE) IS
4854: SELECT BUDGET_VERSION_ID,VERSION_TYPE
4855: FROM PA_BUDGET_VERSIONS
4856: WHERE BUDGET_STATUS_CODE = 'B'
4857: AND PROJECT_ID = c_project_id
4851: cursor c_url_original_baseline(c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4852: c_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE,
4853: c_version_type pa_budget_versions.version_type%TYPE) IS
4854: SELECT BUDGET_VERSION_ID,VERSION_TYPE
4855: FROM PA_BUDGET_VERSIONS
4856: WHERE BUDGET_STATUS_CODE = 'B'
4857: AND PROJECT_ID = c_project_id
4858: AND CI_ID IS NULL
4859: AND NVL(CURRENT_ORIGINAL_FLAG,'N') = 'Y'
4863: --AND (approved_cost_plan_type_flag='Y' or approved_rev_plan_type_flag='Y');
4864:
4865: --Bug 5278200Added extra parameter
4866: cursor c_url_current_baseline(c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4867: c_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE,
4868: c_version_type pa_budget_versions.version_type%TYPE) IS
4869: SELECT BUDGET_VERSION_ID,VERSION_TYPE
4870: FROM PA_BUDGET_VERSIONS
4871: WHERE BUDGET_STATUS_CODE = 'B'
4864:
4865: --Bug 5278200Added extra parameter
4866: cursor c_url_current_baseline(c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4867: c_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE,
4868: c_version_type pa_budget_versions.version_type%TYPE) IS
4869: SELECT BUDGET_VERSION_ID,VERSION_TYPE
4870: FROM PA_BUDGET_VERSIONS
4871: WHERE BUDGET_STATUS_CODE = 'B'
4872: AND PROJECT_ID = c_project_id
4866: cursor c_url_current_baseline(c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4867: c_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE,
4868: c_version_type pa_budget_versions.version_type%TYPE) IS
4869: SELECT BUDGET_VERSION_ID,VERSION_TYPE
4870: FROM PA_BUDGET_VERSIONS
4871: WHERE BUDGET_STATUS_CODE = 'B'
4872: AND PROJECT_ID = c_project_id
4873: AND CI_ID IS NULL
4874: AND NVL(CURRENT_FLAG,'N') = 'Y'
4878: --AND (approved_cost_plan_type_flag='Y' or approved_rev_plan_type_flag='Y');
4879:
4880: --Bug 5278200Added extra parameter
4881: cursor c_url_current_working(c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4882: c_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE,
4883: c_version_type pa_budget_versions.version_type%TYPE) IS
4884: SELECT BUDGET_VERSION_ID,VERSION_TYPE
4885: FROM PA_BUDGET_VERSIONS
4886: WHERE PROJECT_ID = c_project_id
4879:
4880: --Bug 5278200Added extra parameter
4881: cursor c_url_current_working(c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4882: c_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE,
4883: c_version_type pa_budget_versions.version_type%TYPE) IS
4884: SELECT BUDGET_VERSION_ID,VERSION_TYPE
4885: FROM PA_BUDGET_VERSIONS
4886: WHERE PROJECT_ID = c_project_id
4887: AND CI_ID IS NULL
4881: cursor c_url_current_working(c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4882: c_fin_plan_type_id pa_budget_versions.fin_plan_type_id%TYPE,
4883: c_version_type pa_budget_versions.version_type%TYPE) IS
4884: SELECT BUDGET_VERSION_ID,VERSION_TYPE
4885: FROM PA_BUDGET_VERSIONS
4886: WHERE PROJECT_ID = c_project_id
4887: AND CI_ID IS NULL
4888: AND NVL(CURRENT_WORKING_FLAG,'N') = 'Y'
4889: -- AND BUDGET_STATUS_CODE in ('S','W') -- Bug#3815378
5068: END IF;
5069: BEGIN
5070: SELECT A.BUDGET_VERSION_ID
5071: INTO l_appr_rev_cw_version_id
5072: FROM PA_BUDGET_VERSIONS A
5073: WHERE A.PROJECT_ID = p_project_id
5074: AND A.VERSION_TYPE IN('ALL', 'REVENUE')
5075: AND A.APPROVED_REV_PLAN_TYPE_FLAG = 'Y'
5076: AND A.CURRENT_WORKING_FLAG = 'Y'
5113:
5114: BEGIN
5115: SELECT A.BUDGET_VERSION_ID
5116: INTO l_appr_rev_cw_version_id
5117: FROM PA_BUDGET_VERSIONS A
5118: WHERE A.PROJECT_ID = p_project_id
5119: AND A.VERSION_TYPE IN('ALL', 'REVENUE')
5120: --AND A.APPROVED_REV_PLAN_TYPE_FLAG = 'Y'
5121: AND A.FIN_PLAN_TYPE_ID IN(select FIN_PLAN_TYPE_ID from
5118: WHERE A.PROJECT_ID = p_project_id
5119: AND A.VERSION_TYPE IN('ALL', 'REVENUE')
5120: --AND A.APPROVED_REV_PLAN_TYPE_FLAG = 'Y'
5121: AND A.FIN_PLAN_TYPE_ID IN(select FIN_PLAN_TYPE_ID from
5122: PA_BUDGET_VERSIONS where
5123: budget_version_id in
5124: (p_cost_version_id,p_revenue_version_id))
5125: AND A.CURRENT_WORKING_FLAG = 'Y'
5126: AND A.CI_ID IS NULL;
5153: FROM pa_proj_fp_options
5154: WHERE project_id = p_project_id
5155: AND fin_plan_option_level_code = 'PLAN_TYPE'
5156: AND fin_plan_type_id in(select FIN_PLAN_TYPE_ID from
5157: PA_BUDGET_VERSIONS where
5158: budget_version_id in (p_cost_version_id,p_revenue_version_id));
5159: l_appr_cost_plan_type_flag_tbl(1):='Y';
5160: l_appr_rev_plan_type_flag_tbl(1):='Y';
5161:
5993: -- Note: In some cases, p_pt_ct_version_type is passed as null in this case
5994: -- values are returned without bothering about double count.
5995: --------------------------------------------------------------------------------
5996: FUNCTION get_labor_qty_partial(
5997: p_version_type IN pa_budget_versions.version_type%TYPE, -- This is the CI version type
5998: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
5999: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6000: p_labor_qty IN pa_budget_versions.labor_quantity%TYPE DEFAULT NULL, -- CI qty
6001: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
5994: -- values are returned without bothering about double count.
5995: --------------------------------------------------------------------------------
5996: FUNCTION get_labor_qty_partial(
5997: p_version_type IN pa_budget_versions.version_type%TYPE, -- This is the CI version type
5998: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
5999: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6000: p_labor_qty IN pa_budget_versions.labor_quantity%TYPE DEFAULT NULL, -- CI qty
6001: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6002: )
5995: --------------------------------------------------------------------------------
5996: FUNCTION get_labor_qty_partial(
5997: p_version_type IN pa_budget_versions.version_type%TYPE, -- This is the CI version type
5998: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
5999: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6000: p_labor_qty IN pa_budget_versions.labor_quantity%TYPE DEFAULT NULL, -- CI qty
6001: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6002: )
6003: RETURN NUMBER
5996: FUNCTION get_labor_qty_partial(
5997: p_version_type IN pa_budget_versions.version_type%TYPE, -- This is the CI version type
5998: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
5999: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6000: p_labor_qty IN pa_budget_versions.labor_quantity%TYPE DEFAULT NULL, -- CI qty
6001: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6002: )
6003: RETURN NUMBER
6004: IS
6003: RETURN NUMBER
6004: IS
6005: l_debug_mode VARCHAR2(30);
6006: l_module_name VARCHAR2(30) := 'ctrl_utils.lab_qty_prtial';
6007: l_source_version_type pa_budget_versions.version_type%TYPE;
6008: l_target_version_type pa_budget_versions.version_type%TYPE;
6009: l_impl_qty_exists VARCHAR2(1) := 'N';
6010: l_revenue_partial_flag varchar2(1) := 'N';
6011: l_return_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6004: IS
6005: l_debug_mode VARCHAR2(30);
6006: l_module_name VARCHAR2(30) := 'ctrl_utils.lab_qty_prtial';
6007: l_source_version_type pa_budget_versions.version_type%TYPE;
6008: l_target_version_type pa_budget_versions.version_type%TYPE;
6009: l_impl_qty_exists VARCHAR2(1) := 'N';
6010: l_revenue_partial_flag varchar2(1) := 'N';
6011: l_return_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6012: l_partial_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6007: l_source_version_type pa_budget_versions.version_type%TYPE;
6008: l_target_version_type pa_budget_versions.version_type%TYPE;
6009: l_impl_qty_exists VARCHAR2(1) := 'N';
6010: l_revenue_partial_flag varchar2(1) := 'N';
6011: l_return_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6012: l_partial_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6013: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6014: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6015: BEGIN
6008: l_target_version_type pa_budget_versions.version_type%TYPE;
6009: l_impl_qty_exists VARCHAR2(1) := 'N';
6010: l_revenue_partial_flag varchar2(1) := 'N';
6011: l_return_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6012: l_partial_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6013: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6014: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6015: BEGIN
6016: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
6009: l_impl_qty_exists VARCHAR2(1) := 'N';
6010: l_revenue_partial_flag varchar2(1) := 'N';
6011: l_return_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6012: l_partial_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6013: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6014: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6015: BEGIN
6016: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
6017: IF l_debug_mode = 'Y' THEN
6010: l_revenue_partial_flag varchar2(1) := 'N';
6011: l_return_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6012: l_partial_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6013: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
6014: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6015: BEGIN
6016: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
6017: IF l_debug_mode = 'Y' THEN
6018: pa_debug.g_err_stage:='In get_labor_qty_partial - pa_fp_control_items_utils ';
6029: END IF;
6030: BEGIN
6031: SELECT VERSION_TYPE
6032: INTO l_source_version_type
6033: FROM PA_BUDGET_VERSIONS
6034: WHERE BUDGET_VERSION_ID = p_ci_version_id;
6035: EXCEPTION
6036: WHEN NO_DATA_FOUND THEN
6037: IF l_debug_mode = 'Y' THEN
6053: END IF;
6054: BEGIN
6055: SELECT VERSION_TYPE
6056: INTO l_target_version_type
6057: FROM PA_BUDGET_VERSIONS
6058: WHERE BUDGET_VERSION_ID = p_budget_version_id;
6059: EXCEPTION
6060: WHEN NO_DATA_FOUND THEN
6061: IF l_debug_mode = 'Y' THEN
6075: END IF;
6076: BEGIN
6077: SELECT labor_quantity
6078: INTO l_labor_quantity
6079: FROM PA_BUDGET_VERSIONS
6080: WHERE BUDGET_VERSION_ID = p_ci_version_id;
6081: EXCEPTION
6082: WHEN NO_DATA_FOUND THEN
6083: IF l_debug_mode = 'Y' THEN
6149: IF l_impl_qty_exists = 'Y' THEN
6150: BEGIN
6151: SELECT NVL(B.REV_PARTIALLY_IMPL_FLAG,'N') , A.IMPL_QUANTITY
6152: INTO l_revenue_partial_flag, l_partial_quantity
6153: FROM PA_FP_MERGED_CTRL_ITEMS A , PA_BUDGET_VERSIONS B
6154: WHERE A.CI_PLAN_VERSION_ID = p_ci_version_id
6155: AND A.PLAN_VERSION_ID = p_budget_version_id
6156: AND A.VERSION_TYPE = 'REVENUE'
6157: AND B.BUDGET_VERSION_ID = A.CI_PLAN_VERSION_ID;
6176: END IF;
6177: BEGIN
6178: SELECT A.BUDGET_VERSION_ID
6179: INTO l_appr_rev_cw_version_id
6180: FROM PA_BUDGET_VERSIONS A
6181: WHERE A.PROJECT_ID = (SELECT B.PROJECT_ID FROM PA_BUDGET_VERSIONS B
6182: WHERE B.BUDGET_VERSION_ID = p_budget_version_id)
6183: AND A.VERSION_TYPE IN ('REVENUE', 'ALL')
6184: -- Raja review A.VERSION_TYPE = 'REVENUE'
6177: BEGIN
6178: SELECT A.BUDGET_VERSION_ID
6179: INTO l_appr_rev_cw_version_id
6180: FROM PA_BUDGET_VERSIONS A
6181: WHERE A.PROJECT_ID = (SELECT B.PROJECT_ID FROM PA_BUDGET_VERSIONS B
6182: WHERE B.BUDGET_VERSION_ID = p_budget_version_id)
6183: AND A.VERSION_TYPE IN ('REVENUE', 'ALL')
6184: -- Raja review A.VERSION_TYPE = 'REVENUE'
6185: AND A.APPROVED_REV_PLAN_TYPE_FLAG = 'Y'
6261: IF l_impl_qty_exists = 'Y' THEN
6262: BEGIN
6263: SELECT NVL(B.REV_PARTIALLY_IMPL_FLAG,'N') , A.IMPL_QUANTITY
6264: INTO l_revenue_partial_flag, l_partial_quantity
6265: FROM PA_FP_MERGED_CTRL_ITEMS A , PA_BUDGET_VERSIONS B
6266: WHERE A.CI_PLAN_VERSION_ID = p_ci_version_id
6267: AND A.PLAN_VERSION_ID = p_budget_version_id
6268: AND A.VERSION_TYPE = 'REVENUE'
6269: AND B.BUDGET_VERSION_ID = A.CI_PLAN_VERSION_ID;
6303:
6304: --------------------------------------------------------------------------------
6305:
6306: FUNCTION get_equip_qty_partial(
6307: p_version_type IN pa_budget_versions.version_type%TYPE, -- This is the CI version type
6308: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6309: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6310: p_equip_qty IN pa_budget_versions.equipment_quantity%TYPE DEFAULT NULL, -- CI qty
6311: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6304: --------------------------------------------------------------------------------
6305:
6306: FUNCTION get_equip_qty_partial(
6307: p_version_type IN pa_budget_versions.version_type%TYPE, -- This is the CI version type
6308: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6309: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6310: p_equip_qty IN pa_budget_versions.equipment_quantity%TYPE DEFAULT NULL, -- CI qty
6311: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6312: )
6305:
6306: FUNCTION get_equip_qty_partial(
6307: p_version_type IN pa_budget_versions.version_type%TYPE, -- This is the CI version type
6308: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6309: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6310: p_equip_qty IN pa_budget_versions.equipment_quantity%TYPE DEFAULT NULL, -- CI qty
6311: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6312: )
6313: RETURN NUMBER
6306: FUNCTION get_equip_qty_partial(
6307: p_version_type IN pa_budget_versions.version_type%TYPE, -- This is the CI version type
6308: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6309: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6310: p_equip_qty IN pa_budget_versions.equipment_quantity%TYPE DEFAULT NULL, -- CI qty
6311: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6312: )
6313: RETURN NUMBER
6314: IS
6313: RETURN NUMBER
6314: IS
6315: l_debug_mode VARCHAR2(30);
6316: l_module_name VARCHAR2(30) := 'ctrl_utils.equip_qty_prtial';
6317: l_source_version_type pa_budget_versions.version_type%TYPE;
6318: l_target_version_type pa_budget_versions.version_type%TYPE;
6319: l_impl_qty_exists VARCHAR2(1) := 'N';
6320: l_revenue_partial_flag varchar2(1) := 'N';
6321: l_return_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6314: IS
6315: l_debug_mode VARCHAR2(30);
6316: l_module_name VARCHAR2(30) := 'ctrl_utils.equip_qty_prtial';
6317: l_source_version_type pa_budget_versions.version_type%TYPE;
6318: l_target_version_type pa_budget_versions.version_type%TYPE;
6319: l_impl_qty_exists VARCHAR2(1) := 'N';
6320: l_revenue_partial_flag varchar2(1) := 'N';
6321: l_return_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6322: l_partial_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6317: l_source_version_type pa_budget_versions.version_type%TYPE;
6318: l_target_version_type pa_budget_versions.version_type%TYPE;
6319: l_impl_qty_exists VARCHAR2(1) := 'N';
6320: l_revenue_partial_flag varchar2(1) := 'N';
6321: l_return_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6322: l_partial_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6323: l_equip_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6324: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6325: BEGIN
6318: l_target_version_type pa_budget_versions.version_type%TYPE;
6319: l_impl_qty_exists VARCHAR2(1) := 'N';
6320: l_revenue_partial_flag varchar2(1) := 'N';
6321: l_return_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6322: l_partial_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6323: l_equip_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6324: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6325: BEGIN
6326: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
6319: l_impl_qty_exists VARCHAR2(1) := 'N';
6320: l_revenue_partial_flag varchar2(1) := 'N';
6321: l_return_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6322: l_partial_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6323: l_equip_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6324: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6325: BEGIN
6326: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
6327: IF l_debug_mode = 'Y' THEN
6320: l_revenue_partial_flag varchar2(1) := 'N';
6321: l_return_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6322: l_partial_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6323: l_equip_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
6324: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6325: BEGIN
6326: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
6327: IF l_debug_mode = 'Y' THEN
6328: pa_debug.g_err_stage:='In get_equip_qty_partial - pa_fp_control_items_utils ';
6339: END IF;
6340: BEGIN
6341: SELECT VERSION_TYPE
6342: INTO l_source_version_type
6343: FROM PA_BUDGET_VERSIONS
6344: WHERE BUDGET_VERSION_ID = p_ci_version_id;
6345: EXCEPTION
6346: WHEN NO_DATA_FOUND THEN
6347: IF l_debug_mode = 'Y' THEN
6363: END IF;
6364: BEGIN
6365: SELECT VERSION_TYPE
6366: INTO l_target_version_type
6367: FROM PA_BUDGET_VERSIONS
6368: WHERE BUDGET_VERSION_ID = p_budget_version_id;
6369: EXCEPTION
6370: WHEN NO_DATA_FOUND THEN
6371: IF l_debug_mode = 'Y' THEN
6385: END IF;
6386: BEGIN
6387: SELECT EQUIPMENT_QUANTITY
6388: INTO l_equip_quantity
6389: FROM PA_BUDGET_VERSIONS
6390: WHERE BUDGET_VERSION_ID = p_ci_version_id;
6391: EXCEPTION
6392: WHEN NO_DATA_FOUND THEN
6393: IF l_debug_mode = 'Y' THEN
6458: IF l_impl_qty_exists = 'Y' THEN
6459: BEGIN
6460: SELECT NVL(B.REV_PARTIALLY_IMPL_FLAG,'N') , A.IMPL_EQUIPMENT_QUANTITY
6461: INTO l_revenue_partial_flag, l_partial_quantity
6462: FROM PA_FP_MERGED_CTRL_ITEMS A , PA_BUDGET_VERSIONS B
6463: WHERE A.CI_PLAN_VERSION_ID = p_ci_version_id
6464: AND A.PLAN_VERSION_ID = p_budget_version_id
6465: AND A.VERSION_TYPE = 'REVENUE'
6466: AND B.BUDGET_VERSION_ID = A.CI_PLAN_VERSION_ID;
6486:
6487: BEGIN
6488: SELECT A.BUDGET_VERSION_ID
6489: INTO l_appr_rev_cw_version_id
6490: FROM PA_BUDGET_VERSIONS A
6491: WHERE A.PROJECT_ID = (SELECT B.PROJECT_ID FROM PA_BUDGET_VERSIONS B
6492: WHERE B.BUDGET_VERSION_ID = p_budget_version_id)
6493: AND A.VERSION_TYPE = 'REVENUE'
6494: AND A.APPROVED_REV_PLAN_TYPE_FLAG = 'Y'
6487: BEGIN
6488: SELECT A.BUDGET_VERSION_ID
6489: INTO l_appr_rev_cw_version_id
6490: FROM PA_BUDGET_VERSIONS A
6491: WHERE A.PROJECT_ID = (SELECT B.PROJECT_ID FROM PA_BUDGET_VERSIONS B
6492: WHERE B.BUDGET_VERSION_ID = p_budget_version_id)
6493: AND A.VERSION_TYPE = 'REVENUE'
6494: AND A.APPROVED_REV_PLAN_TYPE_FLAG = 'Y'
6495: AND CURRENT_WORKING_FLAG = 'Y'
6570: IF l_impl_qty_exists = 'Y' THEN
6571: BEGIN
6572: SELECT NVL(B.REV_PARTIALLY_IMPL_FLAG,'N') , A.IMPL_EQUIPMENT_QUANTITY
6573: INTO l_revenue_partial_flag, l_partial_quantity
6574: FROM PA_FP_MERGED_CTRL_ITEMS A , PA_BUDGET_VERSIONS B
6575: WHERE A.CI_PLAN_VERSION_ID = p_ci_version_id
6576: AND A.PLAN_VERSION_ID = p_budget_version_id
6577: AND A.VERSION_TYPE = 'REVENUE'
6578: AND B.BUDGET_VERSION_ID = A.CI_PLAN_VERSION_ID;
6610: -- values are returned without bothering about double count.
6611: -----------------------------------------------------------------------------------
6612:
6613: FUNCTION get_pc_revenue_partial (
6614: p_version_type IN pa_budget_versions.version_type%TYPE, -- version type of CI
6615: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6616: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6617: p_revenue IN pa_budget_versions.total_project_revenue%TYPE DEFAULT NULL,
6618: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6611: -----------------------------------------------------------------------------------
6612:
6613: FUNCTION get_pc_revenue_partial (
6614: p_version_type IN pa_budget_versions.version_type%TYPE, -- version type of CI
6615: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6616: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6617: p_revenue IN pa_budget_versions.total_project_revenue%TYPE DEFAULT NULL,
6618: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6619: )
6612:
6613: FUNCTION get_pc_revenue_partial (
6614: p_version_type IN pa_budget_versions.version_type%TYPE, -- version type of CI
6615: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6616: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6617: p_revenue IN pa_budget_versions.total_project_revenue%TYPE DEFAULT NULL,
6618: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6619: )
6620: RETURN NUMBER
6613: FUNCTION get_pc_revenue_partial (
6614: p_version_type IN pa_budget_versions.version_type%TYPE, -- version type of CI
6615: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6616: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6617: p_revenue IN pa_budget_versions.total_project_revenue%TYPE DEFAULT NULL,
6618: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6619: )
6620: RETURN NUMBER
6621: IS
6625: l_revenue_partial_flag VARCHAR2(1);
6626: l_debug_mode VARCHAR2(30);
6627: l_module_name VARCHAR2(30) := 'ctrl_utils.rev_prtial';
6628: l_exists VARCHAR2(1) := 'N';
6629: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6630: l_budget_version_type pa_budget_versions.version_type%TYPE;
6631: l_version_type pa_budget_versions.version_type%TYPE;
6632: BEGIN
6633:
6626: l_debug_mode VARCHAR2(30);
6627: l_module_name VARCHAR2(30) := 'ctrl_utils.rev_prtial';
6628: l_exists VARCHAR2(1) := 'N';
6629: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6630: l_budget_version_type pa_budget_versions.version_type%TYPE;
6631: l_version_type pa_budget_versions.version_type%TYPE;
6632: BEGIN
6633:
6634: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
6627: l_module_name VARCHAR2(30) := 'ctrl_utils.rev_prtial';
6628: l_exists VARCHAR2(1) := 'N';
6629: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
6630: l_budget_version_type pa_budget_versions.version_type%TYPE;
6631: l_version_type pa_budget_versions.version_type%TYPE;
6632: BEGIN
6633:
6634: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
6635:
6648: IF p_revenue IS NULL THEN
6649: BEGIN
6650: SELECT TOTAL_PROJECT_REVENUE
6651: INTO l_revenue
6652: FROM PA_BUDGET_VERSIONS
6653: WHERE BUDGET_VERSION_ID = p_ci_version_id;
6654: EXCEPTION
6655: WHEN NO_DATA_FOUND THEN
6656: IF l_debug_mode = 'Y' THEN
6687: If p_version_type is NULL THEN
6688: BEGIN
6689: SELECT VERSION_TYPE
6690: INTO l_version_type
6691: FROM PA_BUDGET_VERSIONS
6692: WHERE BUDGET_VERSION_ID = p_ci_version_id;
6693: EXCEPTION
6694: WHEN NO_DATA_FOUND THEN
6695: IF l_debug_mode = 'Y' THEN
6707: ----------------------------------------------------------------
6708: BEGIN
6709: SELECT VERSION_TYPE
6710: INTO l_budget_version_type
6711: FROM PA_BUDGET_VERSIONS
6712: WHERE BUDGET_VERSION_ID = p_budget_version_id;
6713: EXCEPTION
6714: WHEN NO_DATA_FOUND THEN
6715: IF l_debug_mode = 'Y' THEN
6731: IF l_exists = 'Y' THEN
6732: BEGIN
6733: SELECT NVL(B.REV_PARTIALLY_IMPL_FLAG,'N') , A.IMPL_PROJ_REVENUE
6734: INTO l_revenue_partial_flag, l_partial_revenue
6735: FROM PA_FP_MERGED_CTRL_ITEMS A , PA_BUDGET_VERSIONS B
6736: WHERE A.CI_PLAN_VERSION_ID = p_ci_version_id
6737: AND A.PLAN_VERSION_ID = p_budget_version_id
6738: AND A.VERSION_TYPE = 'REVENUE'
6739: AND B.BUDGET_VERSION_ID = A.CI_PLAN_VERSION_ID;
6762:
6763: BEGIN
6764: SELECT A.BUDGET_VERSION_ID
6765: INTO l_appr_rev_cw_version_id
6766: FROM PA_BUDGET_VERSIONS A
6767: WHERE A.PROJECT_ID = (SELECT B.PROJECT_ID FROM PA_BUDGET_VERSIONS B
6768: WHERE B.BUDGET_VERSION_ID = p_budget_version_id)
6769: AND A.VERSION_TYPE IN('ALL', 'REVENUE')
6770: -- Raja review AND A.VERSION_TYPE IN('REVENUE')
6763: BEGIN
6764: SELECT A.BUDGET_VERSION_ID
6765: INTO l_appr_rev_cw_version_id
6766: FROM PA_BUDGET_VERSIONS A
6767: WHERE A.PROJECT_ID = (SELECT B.PROJECT_ID FROM PA_BUDGET_VERSIONS B
6768: WHERE B.BUDGET_VERSION_ID = p_budget_version_id)
6769: AND A.VERSION_TYPE IN('ALL', 'REVENUE')
6770: -- Raja review AND A.VERSION_TYPE IN('REVENUE')
6771: AND A.APPROVED_REV_PLAN_TYPE_FLAG = 'Y'
6809: -- Note: In some cases, p_pt_ct_version_type is passed as null in this case
6810: -- values are returned without bothering about double count.
6811: --------------------------------------------------------------------------------
6812: FUNCTION get_pc_cost (
6813: p_version_type IN pa_budget_versions.version_type%TYPE, -- this is the ci version type
6814: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6815: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6816: p_raw_cost IN pa_budget_versions.total_project_raw_cost%TYPE DEFAULT NULL,
6817: p_burdened_cost IN pa_budget_versions.total_project_burdened_cost%TYPE DEFAULT NULL,
6810: -- values are returned without bothering about double count.
6811: --------------------------------------------------------------------------------
6812: FUNCTION get_pc_cost (
6813: p_version_type IN pa_budget_versions.version_type%TYPE, -- this is the ci version type
6814: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6815: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6816: p_raw_cost IN pa_budget_versions.total_project_raw_cost%TYPE DEFAULT NULL,
6817: p_burdened_cost IN pa_budget_versions.total_project_burdened_cost%TYPE DEFAULT NULL,
6818: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6811: --------------------------------------------------------------------------------
6812: FUNCTION get_pc_cost (
6813: p_version_type IN pa_budget_versions.version_type%TYPE, -- this is the ci version type
6814: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6815: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6816: p_raw_cost IN pa_budget_versions.total_project_raw_cost%TYPE DEFAULT NULL,
6817: p_burdened_cost IN pa_budget_versions.total_project_burdened_cost%TYPE DEFAULT NULL,
6818: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6819: )
6812: FUNCTION get_pc_cost (
6813: p_version_type IN pa_budget_versions.version_type%TYPE, -- this is the ci version type
6814: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6815: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6816: p_raw_cost IN pa_budget_versions.total_project_raw_cost%TYPE DEFAULT NULL,
6817: p_burdened_cost IN pa_budget_versions.total_project_burdened_cost%TYPE DEFAULT NULL,
6818: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6819: )
6820: RETURN NUMBER
6813: p_version_type IN pa_budget_versions.version_type%TYPE, -- this is the ci version type
6814: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
6815: p_ci_version_id IN pa_budget_versions.budget_version_id%TYPE,
6816: p_raw_cost IN pa_budget_versions.total_project_raw_cost%TYPE DEFAULT NULL,
6817: p_burdened_cost IN pa_budget_versions.total_project_burdened_cost%TYPE DEFAULT NULL,
6818: p_pt_ct_version_type IN pa_pt_co_impl_statuses.version_type%TYPE DEFAULT NULL
6819: )
6820: RETURN NUMBER
6821: IS
6822: l_return_cost NUMBER :=0;
6823: l_margin_derived_from_code pa_proj_fp_options.margin_derived_from_code%TYPE;
6824: l_debug_mode VARCHAR2(30);
6825: l_module_name VARCHAR2(30) := 'ctrl_utils.get_pc_cost';
6826: l_budget_version_type pa_budget_versions.version_type%TYPE;
6827: l_version_type pa_budget_versions.version_type%TYPE;
6828: l_cost pa_budget_versions.total_project_raw_cost%TYPE;
6829:
6830: BEGIN
6823: l_margin_derived_from_code pa_proj_fp_options.margin_derived_from_code%TYPE;
6824: l_debug_mode VARCHAR2(30);
6825: l_module_name VARCHAR2(30) := 'ctrl_utils.get_pc_cost';
6826: l_budget_version_type pa_budget_versions.version_type%TYPE;
6827: l_version_type pa_budget_versions.version_type%TYPE;
6828: l_cost pa_budget_versions.total_project_raw_cost%TYPE;
6829:
6830: BEGIN
6831:
6824: l_debug_mode VARCHAR2(30);
6825: l_module_name VARCHAR2(30) := 'ctrl_utils.get_pc_cost';
6826: l_budget_version_type pa_budget_versions.version_type%TYPE;
6827: l_version_type pa_budget_versions.version_type%TYPE;
6828: l_cost pa_budget_versions.total_project_raw_cost%TYPE;
6829:
6830: BEGIN
6831:
6832: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
6862: If p_version_type is NULL THEN
6863: BEGIN
6864: SELECT VERSION_TYPE
6865: INTO l_version_type
6866: FROM PA_BUDGET_VERSIONS
6867: WHERE BUDGET_VERSION_ID = p_ci_version_id;
6868: EXCEPTION
6869: WHEN NO_DATA_FOUND THEN
6870: IF l_debug_mode = 'Y' THEN
6882: ----------------------------------------------------------------
6883: BEGIN
6884: SELECT VERSION_TYPE
6885: INTO l_budget_version_type
6886: FROM PA_BUDGET_VERSIONS
6887: WHERE BUDGET_VERSION_ID = p_budget_version_id;
6888: EXCEPTION
6889: WHEN NO_DATA_FOUND THEN
6890: IF l_debug_mode = 'Y' THEN
6920: Select decode(l_margin_derived_from_code,
6921: 'R',total_project_raw_cost
6922: ,total_project_burdened_cost)
6923: INTO l_cost
6924: FROM PA_BUDGET_VERSIONS
6925: WHERE BUDGET_VERSION_ID = p_budget_version_id;
6926:
6927: EXCEPTION
6928: WHEN NO_DATA_FOUND THEN
6968: END get_pc_cost;
6969:
6970: PROCEDURE get_not_included
6971: ( p_project_id IN NUMBER
6972: ,p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
6973: ,p_fin_plan_type_id IN pa_budget_versions.fin_plan_type_id%TYPE
6974: ,p_version_type IN pa_budget_versions.version_type%TYPE
6975: ,x_summary_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_150_TBL_TYPE --File.Sql.39 bug 4440895
6976: ,x_equipment_hours_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
6969:
6970: PROCEDURE get_not_included
6971: ( p_project_id IN NUMBER
6972: ,p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
6973: ,p_fin_plan_type_id IN pa_budget_versions.fin_plan_type_id%TYPE
6974: ,p_version_type IN pa_budget_versions.version_type%TYPE
6975: ,x_summary_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_150_TBL_TYPE --File.Sql.39 bug 4440895
6976: ,x_equipment_hours_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
6977: ,x_labor_hours_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
6970: PROCEDURE get_not_included
6971: ( p_project_id IN NUMBER
6972: ,p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
6973: ,p_fin_plan_type_id IN pa_budget_versions.fin_plan_type_id%TYPE
6974: ,p_version_type IN pa_budget_versions.version_type%TYPE
6975: ,x_summary_tbl OUT NOCOPY SYSTEM.PA_VARCHAR2_150_TBL_TYPE --File.Sql.39 bug 4440895
6976: ,x_equipment_hours_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
6977: ,x_labor_hours_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
6978: ,x_cost_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
6994: l_debug_mode VARCHAR2(30);
6995: --End of variables used for debugging
6996:
6997: l_row_count NUMBER := 0;
6998: l_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE;
6999: l_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
7000: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
7001: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
7002: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
6996:
6997: l_row_count NUMBER := 0;
6998: l_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE;
6999: l_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
7000: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
7001: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
7002: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
7003: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
7004: l_lookup_code_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
6997: l_row_count NUMBER := 0;
6998: l_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE;
6999: l_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
7000: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
7001: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
7002: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
7003: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
7004: l_lookup_code_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
7005: l_assigned_flag VARCHAR2(1) := 'N';
6998: l_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE;
6999: l_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
7000: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
7001: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
7002: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
7003: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
7004: l_lookup_code_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
7005: l_assigned_flag VARCHAR2(1) := 'N';
7006: l_module_name VARCHAR2(30) := 'ctrl_itm_utls.get_not_incl';
6999: l_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
7000: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
7001: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
7002: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
7003: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
7004: l_lookup_code_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
7005: l_assigned_flag VARCHAR2(1) := 'N';
7006: l_module_name VARCHAR2(30) := 'ctrl_itm_utls.get_not_incl';
7007:
7020: --Changed this cursor for bug 3902176
7021:
7022: CURSOR c_change_documents_status(
7023: c_system_status_code PA_CI_STATUSES_V.PROJECT_SYSTEM_STATUS_CODE%TYPE,
7024: c_budget_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
7025: c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
7026: c_fin_plan_type_id PA_BUDGET_VERSIONS.FIN_PLAN_TYPE_ID%TYPE,
7027: c_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS
7028: select nvl(sum(nvl(PA_FP_CONTROL_ITEMS_UTILS.get_labor_qty_partial(
7022: CURSOR c_change_documents_status(
7023: c_system_status_code PA_CI_STATUSES_V.PROJECT_SYSTEM_STATUS_CODE%TYPE,
7024: c_budget_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
7025: c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
7026: c_fin_plan_type_id PA_BUDGET_VERSIONS.FIN_PLAN_TYPE_ID%TYPE,
7027: c_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS
7028: select nvl(sum(nvl(PA_FP_CONTROL_ITEMS_UTILS.get_labor_qty_partial(
7029: pfca.CI_VERSION_TYPE,
7030: c_budget_version_id,
7023: c_system_status_code PA_CI_STATUSES_V.PROJECT_SYSTEM_STATUS_CODE%TYPE,
7024: c_budget_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
7025: c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
7026: c_fin_plan_type_id PA_BUDGET_VERSIONS.FIN_PLAN_TYPE_ID%TYPE,
7027: c_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS
7028: select nvl(sum(nvl(PA_FP_CONTROL_ITEMS_UTILS.get_labor_qty_partial(
7029: pfca.CI_VERSION_TYPE,
7030: c_budget_version_id,
7031: pfca.CI_VERSION_ID,
7137:
7138: BEGIN
7139: Select version_type
7140: into l_version_type
7141: from pa_budget_versions
7142: where budget_version_id = p_budget_version_id;
7143:
7144: IF l_debug_mode = 'Y' THEN
7145: pa_debug.g_err_stage:='l_version_type; '||l_version_type;
7170:
7171: BEGIN
7172: Select fin_plan_type_id
7173: into l_fin_plan_type_id
7174: from pa_budget_versions
7175: where budget_version_id = p_budget_version_id;
7176:
7177: IF l_debug_mode = 'Y' THEN
7178: pa_debug.g_err_stage:='l_fin_plan_type_id; '||l_fin_plan_type_id;
7657:
7658: /* Returns the Ids of the versions created for this Change Document(p_ci_id).
7659: The ID will be NULL if the version can never be there */
7660:
7661: PROCEDURE GET_CI_VERSIONS(P_ci_id IN Pa_budget_versions.ci_id%TYPE -- Controm item id of the change document
7662: ,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
7663: ,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
7664: ,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
7665: ,x_return_status OUT NOCOPY VARCHAR2 -- Indicates the exit status of the API --File.Sql.39 bug 4440895
7658: /* Returns the Ids of the versions created for this Change Document(p_ci_id).
7659: The ID will be NULL if the version can never be there */
7660:
7661: PROCEDURE GET_CI_VERSIONS(P_ci_id IN Pa_budget_versions.ci_id%TYPE -- Controm item id of the change document
7662: ,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
7663: ,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
7664: ,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
7665: ,x_return_status OUT NOCOPY VARCHAR2 -- Indicates the exit status of the API --File.Sql.39 bug 4440895
7666: ,x_msg_data OUT NOCOPY VARCHAR2 -- Indicates the error occurred --File.Sql.39 bug 4440895
7659: The ID will be NULL if the version can never be there */
7660:
7661: PROCEDURE GET_CI_VERSIONS(P_ci_id IN Pa_budget_versions.ci_id%TYPE -- Controm item id of the change document
7662: ,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
7663: ,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
7664: ,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
7665: ,x_return_status OUT NOCOPY VARCHAR2 -- Indicates the exit status of the API --File.Sql.39 bug 4440895
7666: ,x_msg_data OUT NOCOPY VARCHAR2 -- Indicates the error occurred --File.Sql.39 bug 4440895
7667: ,X_msg_count OUT NOCOPY NUMBER) -- Indicates the number of error messages --File.Sql.39 bug 4440895
7660:
7661: PROCEDURE GET_CI_VERSIONS(P_ci_id IN Pa_budget_versions.ci_id%TYPE -- Controm item id of the change document
7662: ,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
7663: ,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
7664: ,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
7665: ,x_return_status OUT NOCOPY VARCHAR2 -- Indicates the exit status of the API --File.Sql.39 bug 4440895
7666: ,x_msg_data OUT NOCOPY VARCHAR2 -- Indicates the error occurred --File.Sql.39 bug 4440895
7667: ,X_msg_count OUT NOCOPY NUMBER) -- Indicates the number of error messages --File.Sql.39 bug 4440895
7668: IS
7668: IS
7669: CURSOR c_vers_for_ci IS
7670: SELECT budget_version_id,
7671: Version_type
7672: FROM pa_budget_versions
7673: WHERE ci_id =p_ci_id;
7674:
7675: -- Start of variables used for debugging purpose
7676:
7794: and other information including whether a change order can be implemented into it */
7795: -- Added New Params for Quantity in GET_PLAN_TYPES_FOR_IMPL - Bug 3902176
7796: PROCEDURE GET_PLAN_TYPES_FOR_IMPL
7797: (P_ci_id IN Pa_fin_plan_types_b.fin_plan_type_id%TYPE, -- Id of the Change Document
7798: P_project_id IN Pa_budget_versions.project_id%TYPE, -- Id of the Project
7799: X_pt_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, -- Plsql table for fin plan type ids --File.Sql.39 bug 4440895
7800: X_pt_name_tbl OUT NOCOPY SYSTEM.pa_varchar2_150_tbl_type, -- Plsql table for fin plan type names --File.Sql.39 bug 4440895
7801: x_cost_impact_impl_tbl OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type, --File.Sql.39 bug 4440895
7802: x_rev_impact_impl_tbl OUT NOCOPY SYSTEM.pa_varchar2_1_tbl_type, --File.Sql.39 bug 4440895
7818: X_all_bv_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type, -- Plsql table for all bv id --File.Sql.39 bug 4440895
7819: 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
7820: X_agreement_num OUT NOCOPY Pa_agreements_all.agreement_num%TYPE, -- Agreement number of the agreement --File.Sql.39 bug 4440895
7821: 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
7822: X_cost_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci cost Budget version id --File.Sql.39 bug 4440895
7823: X_rev_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci rev Budget version id --File.Sql.39 bug 4440895
7824: X_all_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci all Budget version id --File.Sql.39 bug 4440895
7825: x_rem_proj_revenue OUT NOCOPY Pa_budget_versions.total_project_revenue%TYPE, -- Remaining revenue amount to be implemented --File.Sql.39 bug 4440895
7826: x_rem_labor_qty OUT NOCOPY Pa_budget_versions.labor_quantity%TYPE, --File.Sql.39 bug 4440895
7819: 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
7820: X_agreement_num OUT NOCOPY Pa_agreements_all.agreement_num%TYPE, -- Agreement number of the agreement --File.Sql.39 bug 4440895
7821: 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
7822: X_cost_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci cost Budget version id --File.Sql.39 bug 4440895
7823: X_rev_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci rev Budget version id --File.Sql.39 bug 4440895
7824: X_all_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci all Budget version id --File.Sql.39 bug 4440895
7825: x_rem_proj_revenue OUT NOCOPY Pa_budget_versions.total_project_revenue%TYPE, -- Remaining revenue amount to be implemented --File.Sql.39 bug 4440895
7826: x_rem_labor_qty OUT NOCOPY Pa_budget_versions.labor_quantity%TYPE, --File.Sql.39 bug 4440895
7827: x_rem_equip_qty OUT NOCOPY pa_budget_versions.equipment_quantity%TYPE, --File.Sql.39 bug 4440895
7820: X_agreement_num OUT NOCOPY Pa_agreements_all.agreement_num%TYPE, -- Agreement number of the agreement --File.Sql.39 bug 4440895
7821: 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
7822: X_cost_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci cost Budget version id --File.Sql.39 bug 4440895
7823: X_rev_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci rev Budget version id --File.Sql.39 bug 4440895
7824: X_all_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci all Budget version id --File.Sql.39 bug 4440895
7825: x_rem_proj_revenue OUT NOCOPY Pa_budget_versions.total_project_revenue%TYPE, -- Remaining revenue amount to be implemented --File.Sql.39 bug 4440895
7826: x_rem_labor_qty OUT NOCOPY Pa_budget_versions.labor_quantity%TYPE, --File.Sql.39 bug 4440895
7827: x_rem_equip_qty OUT NOCOPY pa_budget_versions.equipment_quantity%TYPE, --File.Sql.39 bug 4440895
7828: 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
7821: 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
7822: X_cost_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci cost Budget version id --File.Sql.39 bug 4440895
7823: X_rev_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci rev Budget version id --File.Sql.39 bug 4440895
7824: X_all_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci all Budget version id --File.Sql.39 bug 4440895
7825: x_rem_proj_revenue OUT NOCOPY Pa_budget_versions.total_project_revenue%TYPE, -- Remaining revenue amount to be implemented --File.Sql.39 bug 4440895
7826: x_rem_labor_qty OUT NOCOPY Pa_budget_versions.labor_quantity%TYPE, --File.Sql.39 bug 4440895
7827: x_rem_equip_qty OUT NOCOPY pa_budget_versions.equipment_quantity%TYPE, --File.Sql.39 bug 4440895
7828: 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
7829: 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
7822: X_cost_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci cost Budget version id --File.Sql.39 bug 4440895
7823: X_rev_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci rev Budget version id --File.Sql.39 bug 4440895
7824: X_all_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci all Budget version id --File.Sql.39 bug 4440895
7825: x_rem_proj_revenue OUT NOCOPY Pa_budget_versions.total_project_revenue%TYPE, -- Remaining revenue amount to be implemented --File.Sql.39 bug 4440895
7826: x_rem_labor_qty OUT NOCOPY Pa_budget_versions.labor_quantity%TYPE, --File.Sql.39 bug 4440895
7827: x_rem_equip_qty OUT NOCOPY pa_budget_versions.equipment_quantity%TYPE, --File.Sql.39 bug 4440895
7828: 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
7829: 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
7830: x_return_status OUT NOCOPY VARCHAR2, -- Indicates the exit status of the API --File.Sql.39 bug 4440895
7823: X_rev_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci rev Budget version id --File.Sql.39 bug 4440895
7824: X_all_ci_version_id OUT NOCOPY Pa_budget_versions.budget_version_id%TYPE, -- Ci all Budget version id --File.Sql.39 bug 4440895
7825: x_rem_proj_revenue OUT NOCOPY Pa_budget_versions.total_project_revenue%TYPE, -- Remaining revenue amount to be implemented --File.Sql.39 bug 4440895
7826: x_rem_labor_qty OUT NOCOPY Pa_budget_versions.labor_quantity%TYPE, --File.Sql.39 bug 4440895
7827: x_rem_equip_qty OUT NOCOPY pa_budget_versions.equipment_quantity%TYPE, --File.Sql.39 bug 4440895
7828: 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
7829: 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
7830: x_return_status OUT NOCOPY VARCHAR2, -- Indicates the exit status of the API --File.Sql.39 bug 4440895
7831: x_msg_data OUT NOCOPY VARCHAR2, -- Indicates the error occurred --File.Sql.39 bug 4440895
7876:
7877: l_status_code pa_control_items.status_code%TYPE;
7878: l_ci_type_id pa_control_items.ci_type_id%TYPE;
7879:
7880: l_ci_version_type pa_budget_versions.version_type%TYPE;
7881:
7882: l_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
7883:
7884: l_approved_fin_pt_id Pa_fin_plan_types_b.fin_plan_type_id%TYPE;
7878: l_ci_type_id pa_control_items.ci_type_id%TYPE;
7879:
7880: l_ci_version_type pa_budget_versions.version_type%TYPE;
7881:
7882: l_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
7883:
7884: l_approved_fin_pt_id Pa_fin_plan_types_b.fin_plan_type_id%TYPE;
7885:
7886: -- Version Type ref is not longer required as both Cost and Revenue Quantity Figures are Retrieved and used now -- 3902176
7883:
7884: l_approved_fin_pt_id Pa_fin_plan_types_b.fin_plan_type_id%TYPE;
7885:
7886: -- Version Type ref is not longer required as both Cost and Revenue Quantity Figures are Retrieved and used now -- 3902176
7887: -- l_version_type pa_budget_versions.version_type%TYPE;--Bug 3662077
7888:
7889: -- Added for Bug 3735309 - Function Security Check
7890: l_submit_cost VARCHAR2(1);
7891: l_submit_revenue VARCHAR2(1);
8490:
8491: See comments for other OUT parameters to know what they indicate */
8492:
8493: 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
8494: ,P_project_id IN Pa_budget_versions.project_id%TYPE -- Id of the Project
8495: ,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
8496: ,P_ci_id IN Pa_budget_versions.ci_id%TYPE -- Id of the Change Order
8497: ,p_ci_type_id IN pa_control_items.ci_type_id%TYPE DEFAULT NULL
8498: ,P_ci_status IN Pa_control_items.status_code%TYPE DEFAULT NULL -- Status of the Change Order
8491: See comments for other OUT parameters to know what they indicate */
8492:
8493: 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
8494: ,P_project_id IN Pa_budget_versions.project_id%TYPE -- Id of the Project
8495: ,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
8496: ,P_ci_id IN Pa_budget_versions.ci_id%TYPE -- Id of the Change Order
8497: ,p_ci_type_id IN pa_control_items.ci_type_id%TYPE DEFAULT NULL
8498: ,P_ci_status IN Pa_control_items.status_code%TYPE DEFAULT NULL -- Status of the Change Order
8499: ,P_ci_cost_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Cost ci version
8492:
8493: 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
8494: ,P_project_id IN Pa_budget_versions.project_id%TYPE -- Id of the Project
8495: ,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
8496: ,P_ci_id IN Pa_budget_versions.ci_id%TYPE -- Id of the Change Order
8497: ,p_ci_type_id IN pa_control_items.ci_type_id%TYPE DEFAULT NULL
8498: ,P_ci_status IN Pa_control_items.status_code%TYPE DEFAULT NULL -- Status of the Change Order
8499: ,P_ci_cost_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Cost ci version
8500: ,P_ci_rev_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Revenue ci version
8495: ,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
8496: ,P_ci_id IN Pa_budget_versions.ci_id%TYPE -- Id of the Change Order
8497: ,p_ci_type_id IN pa_control_items.ci_type_id%TYPE DEFAULT NULL
8498: ,P_ci_status IN Pa_control_items.status_code%TYPE DEFAULT NULL -- Status of the Change Order
8499: ,P_ci_cost_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Cost ci version
8500: ,P_ci_rev_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Revenue ci version
8501: ,P_ci_all_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the All ci version
8502: ,p_targ_bv_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the target budget version. Bug 3745163
8503: ,x_cost_impl_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
8496: ,P_ci_id IN Pa_budget_versions.ci_id%TYPE -- Id of the Change Order
8497: ,p_ci_type_id IN pa_control_items.ci_type_id%TYPE DEFAULT NULL
8498: ,P_ci_status IN Pa_control_items.status_code%TYPE DEFAULT NULL -- Status of the Change Order
8499: ,P_ci_cost_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Cost ci version
8500: ,P_ci_rev_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Revenue ci version
8501: ,P_ci_all_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the All ci version
8502: ,p_targ_bv_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the target budget version. Bug 3745163
8503: ,x_cost_impl_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
8504: ,x_rev_impl_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
8497: ,p_ci_type_id IN pa_control_items.ci_type_id%TYPE DEFAULT NULL
8498: ,P_ci_status IN Pa_control_items.status_code%TYPE DEFAULT NULL -- Status of the Change Order
8499: ,P_ci_cost_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Cost ci version
8500: ,P_ci_rev_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Revenue ci version
8501: ,P_ci_all_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the All ci version
8502: ,p_targ_bv_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the target budget version. Bug 3745163
8503: ,x_cost_impl_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
8504: ,x_rev_impl_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
8505: ,X_cost_impact_impl_flag OUT NOCOPY VARCHAR2 -- Contains 'Y' if the cost impact is completely implemented --File.Sql.39 bug 4440895
8498: ,P_ci_status IN Pa_control_items.status_code%TYPE DEFAULT NULL -- Status of the Change Order
8499: ,P_ci_cost_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Cost ci version
8500: ,P_ci_rev_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the Revenue ci version
8501: ,P_ci_all_version_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the All ci version
8502: ,p_targ_bv_id IN Pa_budget_versions.budget_version_id%TYPE DEFAULT NULL -- Id of the target budget version. Bug 3745163
8503: ,x_cost_impl_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
8504: ,x_rev_impl_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
8505: ,X_cost_impact_impl_flag OUT NOCOPY VARCHAR2 -- Contains 'Y' if the cost impact is completely implemented --File.Sql.39 bug 4440895
8506: ,x_rev_impact_impl_flag OUT NOCOPY VARCHAR2 -- Contains 'Y' if the revenue impact is completely implemented --File.Sql.39 bug 4440895
8551:
8552: -- End of variables used for debugging purpose
8553:
8554: l_is_impl VARCHAR2(1);
8555: l_ci_version_id Pa_budget_versions.budget_version_id%TYPE;
8556: l_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8557:
8558: l_cost_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8559: l_rev_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8552: -- End of variables used for debugging purpose
8553:
8554: l_is_impl VARCHAR2(1);
8555: l_ci_version_id Pa_budget_versions.budget_version_id%TYPE;
8556: l_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8557:
8558: l_cost_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8559: l_rev_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8560: l_all_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8554: l_is_impl VARCHAR2(1);
8555: l_ci_version_id Pa_budget_versions.budget_version_id%TYPE;
8556: l_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8557:
8558: l_cost_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8559: l_rev_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8560: l_all_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8561:
8562: l_ci_type_id pa_control_items.ci_type_id%TYPE;
8555: l_ci_version_id Pa_budget_versions.budget_version_id%TYPE;
8556: l_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8557:
8558: l_cost_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8559: l_rev_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8560: l_all_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8561:
8562: l_ci_type_id pa_control_items.ci_type_id%TYPE;
8563:
8556: l_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8557:
8558: l_cost_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8559: l_rev_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8560: l_all_budget_version_id Pa_budget_versions.budget_version_id%TYPE;
8561:
8562: l_ci_type_id pa_control_items.ci_type_id%TYPE;
8563:
8564: l_status_allows_cost_impl VARCHAR2(1);
8568:
8569: l_agreement_amount NUMBER;
8570: l_agreement_currency_code pa_agreements_all.agreement_currency_code%TYPE;
8571:
8572: l_ci_cost_version_id Pa_budget_versions.budget_version_id%TYPE;
8573: l_ci_rev_version_id Pa_budget_versions.budget_version_id%TYPE;
8574: l_ci_all_version_id Pa_budget_versions.budget_version_id%TYPE;
8575:
8576: l_status_code pa_control_items.status_code%TYPE;
8569: l_agreement_amount NUMBER;
8570: l_agreement_currency_code pa_agreements_all.agreement_currency_code%TYPE;
8571:
8572: l_ci_cost_version_id Pa_budget_versions.budget_version_id%TYPE;
8573: l_ci_rev_version_id Pa_budget_versions.budget_version_id%TYPE;
8574: l_ci_all_version_id Pa_budget_versions.budget_version_id%TYPE;
8575:
8576: l_status_code pa_control_items.status_code%TYPE;
8577: l_target_version_id pa_budget_versions.budget_version_id%TYPE;
8570: l_agreement_currency_code pa_agreements_all.agreement_currency_code%TYPE;
8571:
8572: l_ci_cost_version_id Pa_budget_versions.budget_version_id%TYPE;
8573: l_ci_rev_version_id Pa_budget_versions.budget_version_id%TYPE;
8574: l_ci_all_version_id Pa_budget_versions.budget_version_id%TYPE;
8575:
8576: l_status_code pa_control_items.status_code%TYPE;
8577: l_target_version_id pa_budget_versions.budget_version_id%TYPE;
8578:
8573: l_ci_rev_version_id Pa_budget_versions.budget_version_id%TYPE;
8574: l_ci_all_version_id Pa_budget_versions.budget_version_id%TYPE;
8575:
8576: l_status_code pa_control_items.status_code%TYPE;
8577: l_target_version_id pa_budget_versions.budget_version_id%TYPE;
8578:
8579: l_rev_impl_full VARCHAR2(1); -- Will indicate if revenue has been implemented in full or not
8580:
8581: l_version_type Pa_budget_versions.version_type%TYPE; -- Bug 3745163
8577: l_target_version_id pa_budget_versions.budget_version_id%TYPE;
8578:
8579: l_rev_impl_full VARCHAR2(1); -- Will indicate if revenue has been implemented in full or not
8580:
8581: l_version_type Pa_budget_versions.version_type%TYPE; -- Bug 3745163
8582:
8583: l_current_working_flag Pa_budget_versions.current_working_flag%TYPE; -- Bug 3732446
8584:
8585: BEGIN
8579: l_rev_impl_full VARCHAR2(1); -- Will indicate if revenue has been implemented in full or not
8580:
8581: l_version_type Pa_budget_versions.version_type%TYPE; -- Bug 3745163
8582:
8583: l_current_working_flag Pa_budget_versions.current_working_flag%TYPE; -- Bug 3732446
8584:
8585: BEGIN
8586:
8587: pa_debug.set_curr_function( p_function => 'GET_IMPL_DETAILS',
8708:
8709: IF p_targ_bv_id IS NOT NULL THEN
8710: SELECT version_type,current_working_flag
8711: INTO l_version_type,l_current_working_flag
8712: FROM pa_budget_versions
8713: WHERE budget_version_id=p_targ_bv_id;
8714:
8715: IF l_version_type=PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_COST THEN
8716: l_ci_rev_version_id := NULL;
8919: AND ((l_ci_rev_version_id IS NOT NULL) OR (l_ci_all_version_id IS NOT NULL)) THEN
8920:
8921: /* Scheme Used to Derive Partial Impl Flag Below(this might change)
8922: ---------------------------------------------
8923: Fetch partial impl flag from pa_budget_versions
8924: for the ci_version
8925: If Flag is Y then
8926: Return Y
8927: If Flag is not Y then
8937: BEGIN
8938: -- Changed Select below for Bug 3668169
8939: SELECT nvl(rev_partially_impl_flag,'N')
8940: INTO x_partially_impl_flag
8941: FROM pa_budget_versions pbv
8942: WHERE pbv.budget_Version_id = nvl(l_ci_rev_version_id,l_ci_all_version_id);
8943:
8944: /* SELECT nvl(rev_partially_impl_flag,'N')
8945: INTO x_partially_impl_flag
8942: WHERE pbv.budget_Version_id = nvl(l_ci_rev_version_id,l_ci_all_version_id);
8943:
8944: /* SELECT nvl(rev_partially_impl_flag,'N')
8945: INTO x_partially_impl_flag
8946: FROM pa_budget_versions pbv
8947: WHERE pbv.budget_Version_id = decode( nvl(l_rev_budget_version_id,-1),
8948: -1,l_all_budget_version_id,
8949: l_rev_budget_version_id); */
8950:
9126: --is available its not required to fetch the approved cost/rev current working ids.
9127: PROCEDURE get_dtls_for_view_fin_imp_pg
9128: (p_project_id IN pa_projects_all.project_id%TYPE,
9129: p_ci_id IN pa_control_items.ci_id%TYPE,
9130: p_ci_cost_version_id IN pa_budget_versions.budget_version_id%TYPE,
9131: p_ci_rev_version_id IN pa_budget_versions.budget_version_id%TYPE,
9132: p_ci_all_version_id IN pa_budget_versions.budget_version_id%TYPE,
9133: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
9134: x_app_cost_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9127: PROCEDURE get_dtls_for_view_fin_imp_pg
9128: (p_project_id IN pa_projects_all.project_id%TYPE,
9129: p_ci_id IN pa_control_items.ci_id%TYPE,
9130: p_ci_cost_version_id IN pa_budget_versions.budget_version_id%TYPE,
9131: p_ci_rev_version_id IN pa_budget_versions.budget_version_id%TYPE,
9132: p_ci_all_version_id IN pa_budget_versions.budget_version_id%TYPE,
9133: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
9134: x_app_cost_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9135: x_app_rev_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9128: (p_project_id IN pa_projects_all.project_id%TYPE,
9129: p_ci_id IN pa_control_items.ci_id%TYPE,
9130: p_ci_cost_version_id IN pa_budget_versions.budget_version_id%TYPE,
9131: p_ci_rev_version_id IN pa_budget_versions.budget_version_id%TYPE,
9132: p_ci_all_version_id IN pa_budget_versions.budget_version_id%TYPE,
9133: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
9134: x_app_cost_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9135: x_app_rev_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9136: x_ci_status_code OUT NOCOPY pa_control_items.status_code%TYPE, --File.Sql.39 bug 4440895
9129: p_ci_id IN pa_control_items.ci_id%TYPE,
9130: p_ci_cost_version_id IN pa_budget_versions.budget_version_id%TYPE,
9131: p_ci_rev_version_id IN pa_budget_versions.budget_version_id%TYPE,
9132: p_ci_all_version_id IN pa_budget_versions.budget_version_id%TYPE,
9133: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
9134: x_app_cost_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9135: x_app_rev_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9136: x_ci_status_code OUT NOCOPY pa_control_items.status_code%TYPE, --File.Sql.39 bug 4440895
9137: x_project_currency_code OUT NOCOPY pa_projects_all.project_currency_code%TYPE, --File.Sql.39 bug 4440895
9130: p_ci_cost_version_id IN pa_budget_versions.budget_version_id%TYPE,
9131: p_ci_rev_version_id IN pa_budget_versions.budget_version_id%TYPE,
9132: p_ci_all_version_id IN pa_budget_versions.budget_version_id%TYPE,
9133: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
9134: x_app_cost_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9135: x_app_rev_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9136: x_ci_status_code OUT NOCOPY pa_control_items.status_code%TYPE, --File.Sql.39 bug 4440895
9137: x_project_currency_code OUT NOCOPY pa_projects_all.project_currency_code%TYPE, --File.Sql.39 bug 4440895
9138: x_impact_in_mc_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9131: p_ci_rev_version_id IN pa_budget_versions.budget_version_id%TYPE,
9132: p_ci_all_version_id IN pa_budget_versions.budget_version_id%TYPE,
9133: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
9134: x_app_cost_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9135: x_app_rev_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9136: x_ci_status_code OUT NOCOPY pa_control_items.status_code%TYPE, --File.Sql.39 bug 4440895
9137: x_project_currency_code OUT NOCOPY pa_projects_all.project_currency_code%TYPE, --File.Sql.39 bug 4440895
9138: x_impact_in_mc_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9139: x_targ_version_type OUT NOCOPY pa_budget_Versions.version_type%TYPE, --File.Sql.39 bug 4440895
9135: x_app_rev_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9136: x_ci_status_code OUT NOCOPY pa_control_items.status_code%TYPE, --File.Sql.39 bug 4440895
9137: x_project_currency_code OUT NOCOPY pa_projects_all.project_currency_code%TYPE, --File.Sql.39 bug 4440895
9138: x_impact_in_mc_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9139: x_targ_version_type OUT NOCOPY pa_budget_Versions.version_type%TYPE, --File.Sql.39 bug 4440895
9140: x_show_resources_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9141: x_plan_class_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9142: x_report_cost_using OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9143: x_cost_impl_into_app_cw_ver OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9157: l_return_status VARCHAR2(2000);
9158: l_debug_mode VARCHAR2(30);
9159: l_module_name VARCHAR2(100) := 'PAFPCIUB.get_dtls_for_view_fin_imp_pg' ;
9160: --End of variables used for debugging
9161: l_cost_ci_version_id pa_budget_versions.budget_version_id%TYPE;
9162: l_rev_ci_version_id pa_budget_versions.budget_version_id%TYPE;
9163: l_all_ci_version_id pa_budget_versions.budget_version_id%TYPE;
9164: l_ci_resource_list_id1 pa_resource_lists_all_bg.resource_list_id%TYPE;
9165: l_ci_resource_list_id2 pa_resource_lists_all_bg.resource_list_id%TYPE;
9158: l_debug_mode VARCHAR2(30);
9159: l_module_name VARCHAR2(100) := 'PAFPCIUB.get_dtls_for_view_fin_imp_pg' ;
9160: --End of variables used for debugging
9161: l_cost_ci_version_id pa_budget_versions.budget_version_id%TYPE;
9162: l_rev_ci_version_id pa_budget_versions.budget_version_id%TYPE;
9163: l_all_ci_version_id pa_budget_versions.budget_version_id%TYPE;
9164: l_ci_resource_list_id1 pa_resource_lists_all_bg.resource_list_id%TYPE;
9165: l_ci_resource_list_id2 pa_resource_lists_all_bg.resource_list_id%TYPE;
9166: l_targ_resource_list_id1 pa_resource_lists_all_bg.resource_list_id%TYPE;
9159: l_module_name VARCHAR2(100) := 'PAFPCIUB.get_dtls_for_view_fin_imp_pg' ;
9160: --End of variables used for debugging
9161: l_cost_ci_version_id pa_budget_versions.budget_version_id%TYPE;
9162: l_rev_ci_version_id pa_budget_versions.budget_version_id%TYPE;
9163: l_all_ci_version_id pa_budget_versions.budget_version_id%TYPE;
9164: l_ci_resource_list_id1 pa_resource_lists_all_bg.resource_list_id%TYPE;
9165: l_ci_resource_list_id2 pa_resource_lists_all_bg.resource_list_id%TYPE;
9166: l_targ_resource_list_id1 pa_resource_lists_all_bg.resource_list_id%TYPE;
9167: l_targ_resource_list_id2 pa_resource_lists_all_bg.resource_list_id%TYPE;
9166: l_targ_resource_list_id1 pa_resource_lists_all_bg.resource_list_id%TYPE;
9167: l_targ_resource_list_id2 pa_resource_lists_all_bg.resource_list_id%TYPE;
9168: l_dummy VARCHAR2(1);
9169:
9170: CURSOR c_ci_merge_csr(c_version_type pa_budget_Versions.version_type%TYPE,
9171: c_ci_version_id pa_budget_Versions.budget_Version_id%TYPE,
9172: c_app_cw_ver_id pa_budget_Versions.budget_Version_id%TYPE)
9173: IS
9174: SELECT 'x'
9167: l_targ_resource_list_id2 pa_resource_lists_all_bg.resource_list_id%TYPE;
9168: l_dummy VARCHAR2(1);
9169:
9170: CURSOR c_ci_merge_csr(c_version_type pa_budget_Versions.version_type%TYPE,
9171: c_ci_version_id pa_budget_Versions.budget_Version_id%TYPE,
9172: c_app_cw_ver_id pa_budget_Versions.budget_Version_id%TYPE)
9173: IS
9174: SELECT 'x'
9175: FROM DUAL
9168: l_dummy VARCHAR2(1);
9169:
9170: CURSOR c_ci_merge_csr(c_version_type pa_budget_Versions.version_type%TYPE,
9171: c_ci_version_id pa_budget_Versions.budget_Version_id%TYPE,
9172: c_app_cw_ver_id pa_budget_Versions.budget_Version_id%TYPE)
9173: IS
9174: SELECT 'x'
9175: FROM DUAL
9176: WHERE EXISTS (SELECT 'x'
9346: SELECT pbvc.resource_list_id,
9347: pbvr.resource_list_id
9348: INTO l_targ_resource_list_id1 ,
9349: l_targ_resource_list_id2
9350: FROM pa_budget_versions pbvc,
9351: pa_budget_versions pbvr
9352: WHERE pbvc.budget_version_id=nvl(x_app_cost_cw_ver_id,x_app_rev_cw_ver_id)
9353: AND pbvr.budget_version_id=nvl(x_app_rev_cw_ver_id,x_app_cost_cw_ver_id) ;
9354:
9347: pbvr.resource_list_id
9348: INTO l_targ_resource_list_id1 ,
9349: l_targ_resource_list_id2
9350: FROM pa_budget_versions pbvc,
9351: pa_budget_versions pbvr
9352: WHERE pbvc.budget_version_id=nvl(x_app_cost_cw_ver_id,x_app_rev_cw_ver_id)
9353: AND pbvr.budget_version_id=nvl(x_app_rev_cw_ver_id,x_app_cost_cw_ver_id) ;
9354:
9355: --The approved cost/rev current working versions will always be from plan type of plan class code BUDGET
9370: INTO x_targ_version_type,
9371: l_targ_resource_list_id1 ,
9372: l_targ_resource_list_id2,
9373: x_plan_class_code
9374: FROM pa_budget_versions pbv,
9375: pa_fin_plan_types_b fin
9376: WHERE budget_version_id=p_budget_version_id
9377: AND fin.fin_plan_type_id=pbv.fin_plan_type_id;
9378:
9542: --populated in both x_app_cost_cw_ver_id and x_app_rev_cw_ver_id
9543: --If the current working versions do not exist then null will be returned
9544: PROCEDURE get_app_cw_ver_ids_for_proj
9545: (p_project_id IN pa_projects_all.project_id%TYPE,
9546: x_app_cost_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9547: x_app_rev_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9548: x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9549: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
9550: x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
9543: --If the current working versions do not exist then null will be returned
9544: PROCEDURE get_app_cw_ver_ids_for_proj
9545: (p_project_id IN pa_projects_all.project_id%TYPE,
9546: x_app_cost_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9547: x_app_rev_cw_ver_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
9548: x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
9549: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
9550: x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
9551: IS
9564: IS
9565: SELECT budget_version_id,
9566: approved_cost_plan_type_flag,
9567: approved_rev_plan_type_flag
9568: FROM pa_budget_versions pbv
9569: WHERE pbv.project_id=p_project_id
9570: AND pbv.ci_id IS NULL
9571: AND pbv.fin_plan_type_id IS NOT NULL
9572: AND nvl(pbv.wp_version_flag,'N')='N'
9728: BEGIN
9729: -- Selecting budget_version_id for the Rev CI version
9730: SELECT budget_version_id
9731: INTO l_ci_rev_version_id
9732: FROM pa_budget_versions
9733: WHERE project_id = p_project_id
9734: AND ci_id = p_ci_id
9735: AND Nvl(approved_rev_plan_type_flag, 'N')= 'Y'
9736: AND version_type IN ('REVENUE','ALL');
9737:
9738: -- Selecting the budget_version_id of the target current working version
9739: SELECT budget_version_id
9740: INTO l_cw_bv_id
9741: FROM pa_budget_versions
9742: WHERE project_id = p_project_id
9743: AND Nvl(approved_rev_plan_type_flag, 'N')= 'Y'
9744: AND Nvl(current_working_flag, 'N') = 'Y';
9745:
9855: All the lines of a revenue change order version will be in a single currency
9856: */
9857: PROCEDURE get_txn_curr_code_of_ci_ver(
9858: p_project_id IN pa_projects_all.project_id%TYPE
9859: ,p_ci_id IN pa_budget_versions.ci_id%TYPE
9860: ,x_txn_currency_code OUT NOCOPY pa_budget_lines.txn_currency_code%TYPE --File.Sql.39 bug 4440895
9861: ,x_budget_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE --File.Sql.39 bug 4440895
9862: ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
9863: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
9857: PROCEDURE get_txn_curr_code_of_ci_ver(
9858: p_project_id IN pa_projects_all.project_id%TYPE
9859: ,p_ci_id IN pa_budget_versions.ci_id%TYPE
9860: ,x_txn_currency_code OUT NOCOPY pa_budget_lines.txn_currency_code%TYPE --File.Sql.39 bug 4440895
9861: ,x_budget_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE --File.Sql.39 bug 4440895
9862: ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
9863: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
9864: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
9865: )
9915: rac.budget_version_id
9916: INTO x_txn_currency_code,
9917: x_budget_version_id
9918: FROM pa_resource_asgn_curr rac,
9919: pa_budget_versions bv
9920: WHERE bv.project_id = p_project_id
9921: AND bv.ci_id = p_ci_id
9922: AND bv.version_type IN ('REVENUE','ALL')
9923: AND rac.budget_version_id = bv.budget_version_id