[Home] [Help]
5:
6: --Bug 5845142. These variables are only for internal usage of check_valid_combo(for cvc) function
7: --and should not be used in other procedures/functions
8: l_cvc_project_id NUMBER;
9: l_cvc_app_cost_pt_rev_flag pa_proj_fp_options.approved_cost_plan_type_flag%TYPE;
10: l_cvc_app_cost_pt_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
11: --Bug 5845142
12:
13: PROCEDURE Get_Fin_Plan_Dtls(p_project_id IN Pa_Projects_All.Project_Id%TYPE,
6: --Bug 5845142. These variables are only for internal usage of check_valid_combo(for cvc) function
7: --and should not be used in other procedures/functions
8: l_cvc_project_id NUMBER;
9: l_cvc_app_cost_pt_rev_flag pa_proj_fp_options.approved_cost_plan_type_flag%TYPE;
10: l_cvc_app_cost_pt_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
11: --Bug 5845142
12:
13: PROCEDURE Get_Fin_Plan_Dtls(p_project_id IN Pa_Projects_All.Project_Id%TYPE,
14: p_ci_id IN NUMBER,
86: SELECT COUNT(*) INTO x_no_of_ci_plan_versions FROM pa_budget_Versions
87: WHERE project_id = p_project_id AND
88: nvl(ci_id,-1) = p_ci_id;
89:
90: SELECT COUNT(*) INTO l_no_of_app_plan_types FROM Pa_Proj_Fp_Options
91: WHERE
92: Project_Id = p_project_id AND
93: Fin_Plan_Option_Level_Code = 'PLAN_TYPE' AND
94: ( NVL(Approved_Cost_Plan_Type_Flag ,'N') = 'Y' OR
100: INTO
101: x_fp_pref_code,
102: x_report_labor_hours_code,
103: l_tmp_fin_plan_type_id
104: FROM Pa_Proj_Fp_Options WHERE
105: Project_Id = p_project_id AND
106: Fin_Plan_Option_Level_Code = 'PLAN_TYPE' AND
107: ( NVL(Approved_Cost_Plan_Type_Flag ,'N') = 'Y' OR
108: NVL(Approved_Rev_Plan_Type_Flag ,'N') = 'Y');
151: bv.request_id
152: INTO x_margin_derived_from_code,
153: x_ci_ver_plan_prc_code,
154: x_request_id
155: FROM pa_proj_fp_options fpo,
156: pa_budget_versions bv
157: WHERE bv.ci_id = p_ci_id
158: AND bv.version_type in ('COST','ALL')
159: AND fpo.fin_plan_version_id = bv.budget_version_id
182: bv.request_id
183: INTO x_margin_derived_from_code,
184: x_ci_ver_plan_prc_code,
185: x_request_id
186: FROM pa_proj_fp_options fpo,
187: pa_budget_versions bv
188: WHERE fpo.project_id = p_project_id
189: AND bv.current_working_flag = 'Y'
190: AND fpo.fin_plan_version_id = bv.budget_version_id
211: bv.request_id
212: INTO x_margin_derived_from_code,
213: x_ci_ver_plan_prc_code,
214: x_request_id
215: FROM pa_proj_fp_options fpo,
216: pa_budget_versions bv
217: WHERE bv.ci_id = p_ci_id
218: AND bv.version_type in ('COST','ALL')
219: AND fpo.fin_plan_version_id = bv.budget_version_id
243: bv.request_id
244: INTO x_margin_derived_from_code,
245: x_ci_ver_plan_prc_code,
246: x_request_id
247: FROM pa_proj_fp_options fpo,
248: pa_budget_versions bv
249: WHERE fpo.project_id = p_project_id
250: AND bv.current_working_flag = 'Y'
251: AND fpo.fin_plan_version_id = bv.budget_version_id
263: INTO
264: x_fp_pref_code,
265: x_report_labor_hours_code,
266: x_fin_plan_type_id_cost
267: FROM Pa_Proj_Fp_Options WHERE
268: Project_Id = p_project_id AND
269: Fin_Plan_Option_Level_Code = 'PLAN_TYPE' AND
270: NVL(Approved_Cost_Plan_Type_Flag ,'N') = 'Y';
271:
275: x_fp_pref_code := 'COST_AND_REV_SEP';
276:
277:
278: SELECT Fin_Plan_Type_Id INTO x_fin_plan_type_id_rev
279: FROM Pa_Proj_Fp_Options WHERE
280: Project_Id = p_project_id AND
281: Fin_Plan_Option_Level_Code = 'PLAN_TYPE' AND
282: NVL(Approved_Rev_Plan_Type_Flag ,'N') = 'Y';
283:
470: PROCEDURE FP_CI_GET_VERSION_DETAILS
471: (
472: p_project_id IN NUMBER,
473: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
474: x_fin_plan_pref_code OUT NOCOPY pa_proj_fp_options.fin_plan_preference_code%TYPE, --File.Sql.39 bug 4440895
475: x_multi_curr_flag OUT NOCOPY pa_proj_fp_options.plan_in_multi_curr_flag%TYPE, --File.Sql.39 bug 4440895
476: x_fin_plan_level_code OUT NOCOPY pa_proj_fp_options.all_fin_plan_level_code%TYPE, --File.Sql.39 bug 4440895
477: x_resource_list_id OUT NOCOPY pa_proj_fp_options.all_resource_list_id%TYPE, --File.Sql.39 bug 4440895
478: x_time_phased_code OUT NOCOPY pa_proj_fp_options.all_time_phased_code%TYPE, --File.Sql.39 bug 4440895
471: (
472: p_project_id IN NUMBER,
473: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
474: x_fin_plan_pref_code OUT NOCOPY pa_proj_fp_options.fin_plan_preference_code%TYPE, --File.Sql.39 bug 4440895
475: x_multi_curr_flag OUT NOCOPY pa_proj_fp_options.plan_in_multi_curr_flag%TYPE, --File.Sql.39 bug 4440895
476: x_fin_plan_level_code OUT NOCOPY pa_proj_fp_options.all_fin_plan_level_code%TYPE, --File.Sql.39 bug 4440895
477: x_resource_list_id OUT NOCOPY pa_proj_fp_options.all_resource_list_id%TYPE, --File.Sql.39 bug 4440895
478: x_time_phased_code OUT NOCOPY pa_proj_fp_options.all_time_phased_code%TYPE, --File.Sql.39 bug 4440895
479: x_uncategorized_flag OUT NOCOPY pa_resource_lists_all_bg.uncategorized_flag%TYPE, --File.Sql.39 bug 4440895
472: p_project_id IN NUMBER,
473: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
474: x_fin_plan_pref_code OUT NOCOPY pa_proj_fp_options.fin_plan_preference_code%TYPE, --File.Sql.39 bug 4440895
475: x_multi_curr_flag OUT NOCOPY pa_proj_fp_options.plan_in_multi_curr_flag%TYPE, --File.Sql.39 bug 4440895
476: x_fin_plan_level_code OUT NOCOPY pa_proj_fp_options.all_fin_plan_level_code%TYPE, --File.Sql.39 bug 4440895
477: x_resource_list_id OUT NOCOPY pa_proj_fp_options.all_resource_list_id%TYPE, --File.Sql.39 bug 4440895
478: x_time_phased_code OUT NOCOPY pa_proj_fp_options.all_time_phased_code%TYPE, --File.Sql.39 bug 4440895
479: x_uncategorized_flag OUT NOCOPY pa_resource_lists_all_bg.uncategorized_flag%TYPE, --File.Sql.39 bug 4440895
480: x_group_res_type_id OUT NOCOPY pa_resource_lists_all_bg.group_resource_type_id%TYPE, --File.Sql.39 bug 4440895
473: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
474: x_fin_plan_pref_code OUT NOCOPY pa_proj_fp_options.fin_plan_preference_code%TYPE, --File.Sql.39 bug 4440895
475: x_multi_curr_flag OUT NOCOPY pa_proj_fp_options.plan_in_multi_curr_flag%TYPE, --File.Sql.39 bug 4440895
476: x_fin_plan_level_code OUT NOCOPY pa_proj_fp_options.all_fin_plan_level_code%TYPE, --File.Sql.39 bug 4440895
477: x_resource_list_id OUT NOCOPY pa_proj_fp_options.all_resource_list_id%TYPE, --File.Sql.39 bug 4440895
478: x_time_phased_code OUT NOCOPY pa_proj_fp_options.all_time_phased_code%TYPE, --File.Sql.39 bug 4440895
479: x_uncategorized_flag OUT NOCOPY pa_resource_lists_all_bg.uncategorized_flag%TYPE, --File.Sql.39 bug 4440895
480: x_group_res_type_id OUT NOCOPY pa_resource_lists_all_bg.group_resource_type_id%TYPE, --File.Sql.39 bug 4440895
481: x_version_type OUT NOCOPY pa_budget_versions.version_type%TYPE, --File.Sql.39 bug 4440895
474: x_fin_plan_pref_code OUT NOCOPY pa_proj_fp_options.fin_plan_preference_code%TYPE, --File.Sql.39 bug 4440895
475: x_multi_curr_flag OUT NOCOPY pa_proj_fp_options.plan_in_multi_curr_flag%TYPE, --File.Sql.39 bug 4440895
476: x_fin_plan_level_code OUT NOCOPY pa_proj_fp_options.all_fin_plan_level_code%TYPE, --File.Sql.39 bug 4440895
477: x_resource_list_id OUT NOCOPY pa_proj_fp_options.all_resource_list_id%TYPE, --File.Sql.39 bug 4440895
478: x_time_phased_code OUT NOCOPY pa_proj_fp_options.all_time_phased_code%TYPE, --File.Sql.39 bug 4440895
479: x_uncategorized_flag OUT NOCOPY pa_resource_lists_all_bg.uncategorized_flag%TYPE, --File.Sql.39 bug 4440895
480: x_group_res_type_id OUT NOCOPY pa_resource_lists_all_bg.group_resource_type_id%TYPE, --File.Sql.39 bug 4440895
481: x_version_type OUT NOCOPY pa_budget_versions.version_type%TYPE, --File.Sql.39 bug 4440895
482: x_ci_id OUT NOCOPY pa_budget_versions.ci_id%TYPE, --File.Sql.39 bug 4440895
549: x_resource_list_id,
550: x_time_phased_code,
551: x_version_type,
552: x_ci_id
553: FROM pa_budget_versions bv, pa_proj_fp_options po
554: WHERE
555: bv.budget_version_id = p_budget_version_id
556: AND po.fin_plan_version_id = bv.budget_version_id
557: AND po.project_id = p_project_id;
622: l_budget_version_id pa_budget_versions.budget_version_id%TYPE;
623:
624: --Defining Local variables for source version
625: l_s_version_id pa_budget_versions.budget_version_id%TYPE;
626: l_s_fin_plan_pref_code pa_proj_fp_options. fin_plan_preference_code%TYPE;
627: l_s_multi_curr_flag pa_proj_fp_options. plan_in_multi_curr_flag%TYPE;
628: l_s_time_phased_code pa_proj_fp_options. all_time_phased_code%TYPE;
629: l_s_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
630: l_s_fin_plan_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
623:
624: --Defining Local variables for source version
625: l_s_version_id pa_budget_versions.budget_version_id%TYPE;
626: l_s_fin_plan_pref_code pa_proj_fp_options. fin_plan_preference_code%TYPE;
627: l_s_multi_curr_flag pa_proj_fp_options. plan_in_multi_curr_flag%TYPE;
628: l_s_time_phased_code pa_proj_fp_options. all_time_phased_code%TYPE;
629: l_s_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
630: l_s_fin_plan_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
631: l_s_uncategorized_flag pa_resource_lists_all_bg.uncategorized_flag %TYPE;
624: --Defining Local variables for source version
625: l_s_version_id pa_budget_versions.budget_version_id%TYPE;
626: l_s_fin_plan_pref_code pa_proj_fp_options. fin_plan_preference_code%TYPE;
627: l_s_multi_curr_flag pa_proj_fp_options. plan_in_multi_curr_flag%TYPE;
628: l_s_time_phased_code pa_proj_fp_options. all_time_phased_code%TYPE;
629: l_s_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
630: l_s_fin_plan_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
631: l_s_uncategorized_flag pa_resource_lists_all_bg.uncategorized_flag %TYPE;
632: l_s_group_res_type_id pa_resource_lists_all_bg.group_resource_type_id%TYPE;
625: l_s_version_id pa_budget_versions.budget_version_id%TYPE;
626: l_s_fin_plan_pref_code pa_proj_fp_options. fin_plan_preference_code%TYPE;
627: l_s_multi_curr_flag pa_proj_fp_options. plan_in_multi_curr_flag%TYPE;
628: l_s_time_phased_code pa_proj_fp_options. all_time_phased_code%TYPE;
629: l_s_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
630: l_s_fin_plan_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
631: l_s_uncategorized_flag pa_resource_lists_all_bg.uncategorized_flag %TYPE;
632: l_s_group_res_type_id pa_resource_lists_all_bg.group_resource_type_id%TYPE;
633: l_s_version_type pa_budget_versions.version_type%TYPE;
626: l_s_fin_plan_pref_code pa_proj_fp_options. fin_plan_preference_code%TYPE;
627: l_s_multi_curr_flag pa_proj_fp_options. plan_in_multi_curr_flag%TYPE;
628: l_s_time_phased_code pa_proj_fp_options. all_time_phased_code%TYPE;
629: l_s_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
630: l_s_fin_plan_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
631: l_s_uncategorized_flag pa_resource_lists_all_bg.uncategorized_flag %TYPE;
632: l_s_group_res_type_id pa_resource_lists_all_bg.group_resource_type_id%TYPE;
633: l_s_version_type pa_budget_versions.version_type%TYPE;
634: l_s_ci_id pa_budget_versions.ci_id%TYPE;
634: l_s_ci_id pa_budget_versions.ci_id%TYPE;
635:
636: --Defining Local variables for target version
637: l_t_version_id pa_budget_versions.budget_version_id%TYPE;
638: l_t_fin_plan_pref_code pa_proj_fp_options. fin_plan_preference_code%TYPE;
639: l_t_multi_curr_flag pa_proj_fp_options. plan_in_multi_curr_flag%TYPE;
640: l_t_time_phased_code pa_proj_fp_options. all_time_phased_code%TYPE;
641: l_t_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
642: l_t_fin_plan_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
635:
636: --Defining Local variables for target version
637: l_t_version_id pa_budget_versions.budget_version_id%TYPE;
638: l_t_fin_plan_pref_code pa_proj_fp_options. fin_plan_preference_code%TYPE;
639: l_t_multi_curr_flag pa_proj_fp_options. plan_in_multi_curr_flag%TYPE;
640: l_t_time_phased_code pa_proj_fp_options. all_time_phased_code%TYPE;
641: l_t_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
642: l_t_fin_plan_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
643: l_t_uncategorized_flag pa_resource_lists_all_bg.uncategorized_flag %TYPE;
636: --Defining Local variables for target version
637: l_t_version_id pa_budget_versions.budget_version_id%TYPE;
638: l_t_fin_plan_pref_code pa_proj_fp_options. fin_plan_preference_code%TYPE;
639: l_t_multi_curr_flag pa_proj_fp_options. plan_in_multi_curr_flag%TYPE;
640: l_t_time_phased_code pa_proj_fp_options. all_time_phased_code%TYPE;
641: l_t_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
642: l_t_fin_plan_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
643: l_t_uncategorized_flag pa_resource_lists_all_bg.uncategorized_flag %TYPE;
644: l_t_group_res_type_id pa_resource_lists_all_bg.group_resource_type_id%TYPE;
637: l_t_version_id pa_budget_versions.budget_version_id%TYPE;
638: l_t_fin_plan_pref_code pa_proj_fp_options. fin_plan_preference_code%TYPE;
639: l_t_multi_curr_flag pa_proj_fp_options. plan_in_multi_curr_flag%TYPE;
640: l_t_time_phased_code pa_proj_fp_options. all_time_phased_code%TYPE;
641: l_t_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
642: l_t_fin_plan_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
643: l_t_uncategorized_flag pa_resource_lists_all_bg.uncategorized_flag %TYPE;
644: l_t_group_res_type_id pa_resource_lists_all_bg.group_resource_type_id%TYPE;
645: l_t_version_type pa_budget_versions.version_type%TYPE;
638: l_t_fin_plan_pref_code pa_proj_fp_options. fin_plan_preference_code%TYPE;
639: l_t_multi_curr_flag pa_proj_fp_options. plan_in_multi_curr_flag%TYPE;
640: l_t_time_phased_code pa_proj_fp_options. all_time_phased_code%TYPE;
641: l_t_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
642: l_t_fin_plan_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
643: l_t_uncategorized_flag pa_resource_lists_all_bg.uncategorized_flag %TYPE;
644: l_t_group_res_type_id pa_resource_lists_all_bg.group_resource_type_id%TYPE;
645: l_t_version_type pa_budget_versions.version_type%TYPE;
646: l_t_ci_id pa_budget_versions.ci_id%TYPE;
1563: cursor check_ci_funding_level is
1564: select DECODE(bv.version_type,'REVENUE',revenue_fin_plan_level_code,
1565: 'ALL',all_fin_plan_level_code,null)
1566: from pa_budget_versions bv,
1567: pa_proj_fp_options po,
1568: pa_ci_impacts pci,
1569: pa_projects_all ppa
1570: where bv.project_id = p_project_id
1571: and bv.approved_rev_plan_type_flag = 'Y'
1810: ===============================================================================*/
1811:
1812: PROCEDURE Is_Create_CI_Version_Allowed
1813: ( p_project_id IN pa_budget_versions.project_id%TYPE
1814: ,p_fin_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE
1815: ,p_version_type IN pa_budget_versions.version_type%TYPE
1816: ,p_impacted_task_id IN pa_tasks.task_id%TYPE
1817: ,x_version_allowed_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1818: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1833: l_group_resource_type_id pa_resource_lists.group_resource_type_id%TYPE;
1834: l_resource_list_id pa_resource_lists.resource_list_id%TYPE;
1835: l_grouped_flag VARCHAR2(1); --indicates if resource_list is grouped
1836:
1837: l_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
1838:
1839: l_count NUMBER;
1840: l_impacted_task_level VARCHAR2(1);
1841: l_plan_type_planning_level pa_proj_fp_options.all_fin_plan_level_code%TYPE;
1837: l_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
1838:
1839: l_count NUMBER;
1840: l_impacted_task_level VARCHAR2(1);
1841: l_plan_type_planning_level pa_proj_fp_options.all_fin_plan_level_code%TYPE;
1842:
1843: -- jwhite: Added for Plannable Task Dev Effort ------------------
1844: -- 01-JUL-2003 Default ci from current working version
1845: l_ci_apprv_bv_option_id pa_proj_fp_options.proj_fp_options_id%TYPE :=NULL;
1841: l_plan_type_planning_level pa_proj_fp_options.all_fin_plan_level_code%TYPE;
1842:
1843: -- jwhite: Added for Plannable Task Dev Effort ------------------
1844: -- 01-JUL-2003 Default ci from current working version
1845: l_ci_apprv_bv_option_id pa_proj_fp_options.proj_fp_options_id%TYPE :=NULL;
1846: l_ci_apprv_cw_bv_id pa_budget_versions.budget_version_id%TYPE :=NULL;
1847:
1848: -- ---------------------------------------------------------------------------
1849:
1996: PA_FP_CONSTANTS_PKG.G_ELEMENT_TYPE_REVENUE, revenue_fin_plan_level_code) plan_type_planning_level
1997: INTO l_proj_fp_options_id,
1998: l_resource_list_id,
1999: l_plan_type_planning_level
2000: FROM pa_proj_fp_options
2001: WHERE project_id = p_project_id
2002: AND fin_plan_type_id = p_fin_plan_type_id
2003: AND fin_plan_version_id = l_ci_apprv_cw_bv_id;
2004:
2302: FUNCTION IsFpAutoBaselineEnabled(p_project_id IN NUMBER)
2303: RETURN VARCHAR2 IS
2304: l_baseline_funding_flag pa_projects_all.baseline_funding_flag%TYPE;
2305: l_no_of_app_plan_types NUMBER;
2306: l_fp_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
2307:
2308: BEGIN
2309: SELECT NVL(Baseline_Funding_Flag,'N') INTO
2310: l_baseline_Funding_flag
2314: IF l_baseline_funding_flag = 'N' THEN
2315: RETURN l_baseline_funding_flag;
2316: END IF;
2317:
2318: SELECT COUNT(*) INTO l_no_of_app_plan_types FROM Pa_Proj_Fp_Options
2319: WHERE
2320: Project_Id = p_project_id AND
2321: Fin_Plan_Option_Level_Code = 'PLAN_TYPE' AND
2322: ( NVL(Approved_Cost_Plan_Type_Flag ,'N') = 'Y' OR
2329:
2330: IF l_no_of_app_plan_types = 1 THEN
2331: SELECT fin_plan_preference_code INTO
2332: l_fp_pref_code
2333: FROM pa_proj_fp_options
2334: WHERE
2335: project_id = p_project_id AND
2336: Fin_Plan_Option_Level_Code = 'PLAN_TYPE' AND
2337: ( NVL(Approved_Cost_Plan_Type_Flag ,'N') = 'Y' OR
2487: --behavior are targeted for track1 post FP.M
2488:
2489: PROCEDURE CHK_APRV_CUR_WORKING_BV_EXISTS
2490: ( p_project_id IN pa_projects_all.project_id%TYPE
2491: ,p_fin_plan_type_id IN pa_proj_fp_options.fin_plan_type_id%TYPE
2492: ,p_version_type IN pa_budget_versions.version_type%TYPE
2493: ,x_cur_work_bv_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE --File.Sql.39 bug 4440895
2494: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2495: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2506: --Bug 5845142. Exclude COST impacts with cost and revenue together setup.
2507: cursor c1 is
2508: Select budget_version_id
2509: from pa_budget_versions bv,
2510: pa_proj_fp_options pfo
2511: where bv.project_id = p_project_id
2512: and bv.fin_plan_type_id = p_fin_plan_type_id
2513: and bv.version_type = p_version_type
2514: and bv.current_working_flag = 'Y'
2629: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2630: ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
2631:
2632: l_stage number ;
2633: l_source_time_phased_code pa_proj_fp_options.all_time_phased_code%TYPE;
2634: l_target_time_phased_code pa_proj_fp_options.all_time_phased_code%TYPE;
2635:
2636: cursor c1 is
2637: select DECODE(pos.fin_plan_preference_code,'COST_ONLY', pos.cost_time_phased_code,
2630: ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
2631:
2632: l_stage number ;
2633: l_source_time_phased_code pa_proj_fp_options.all_time_phased_code%TYPE;
2634: l_target_time_phased_code pa_proj_fp_options.all_time_phased_code%TYPE;
2635:
2636: cursor c1 is
2637: select DECODE(pos.fin_plan_preference_code,'COST_ONLY', pos.cost_time_phased_code,
2638: 'REVENUE_ONLY',pos.revenue_time_phased_code,
2639: pos.all_time_phased_code) source_time_phased_code,
2640: DECODE(pot.fin_plan_preference_code,'COST_ONLY', pot.cost_time_phased_code,
2641: 'REVENUE_ONLY',pot.revenue_time_phased_code,
2642: pot.all_time_phased_code) target_time_phased_code
2643: from pa_proj_fp_options pos
2644: ,pa_proj_fp_options pot
2645: where pos.fin_plan_version_id = p_source_bv_id
2646: and pot.fin_plan_version_id = p_target_bv_id;
2647: BEGIN
2640: DECODE(pot.fin_plan_preference_code,'COST_ONLY', pot.cost_time_phased_code,
2641: 'REVENUE_ONLY',pot.revenue_time_phased_code,
2642: pot.all_time_phased_code) target_time_phased_code
2643: from pa_proj_fp_options pos
2644: ,pa_proj_fp_options pot
2645: where pos.fin_plan_version_id = p_source_bv_id
2646: and pot.fin_plan_version_id = p_target_bv_id;
2647: BEGIN
2648: x_return_status := FND_API.G_RET_STS_SUCCESS;
2772: -- For procedure, FP_CI_CHECK_COPY_POSSIBLE
2773: -- default ci from the current working version.
2774:
2775: PROCEDURE FP_CI_CHECK_COPY_POSSIBLE
2776: ( p_source_plan_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE
2777: ,p_source_time_phased_code IN pa_proj_fp_options.all_time_phased_code%TYPE
2778: ,p_source_resource_list_id IN pa_proj_fp_options.all_resource_list_id%TYPE
2779: ,p_source_version_type IN pa_budget_versions.version_type%TYPE
2780: ,p_project_id IN pa_budget_versions.project_id%TYPE
2773: -- default ci from the current working version.
2774:
2775: PROCEDURE FP_CI_CHECK_COPY_POSSIBLE
2776: ( p_source_plan_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE
2777: ,p_source_time_phased_code IN pa_proj_fp_options.all_time_phased_code%TYPE
2778: ,p_source_resource_list_id IN pa_proj_fp_options.all_resource_list_id%TYPE
2779: ,p_source_version_type IN pa_budget_versions.version_type%TYPE
2780: ,p_project_id IN pa_budget_versions.project_id%TYPE
2781: ,p_s_ci_id IN pa_budget_versions.ci_id%TYPE
2774:
2775: PROCEDURE FP_CI_CHECK_COPY_POSSIBLE
2776: ( p_source_plan_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE
2777: ,p_source_time_phased_code IN pa_proj_fp_options.all_time_phased_code%TYPE
2778: ,p_source_resource_list_id IN pa_proj_fp_options.all_resource_list_id%TYPE
2779: ,p_source_version_type IN pa_budget_versions.version_type%TYPE
2780: ,p_project_id IN pa_budget_versions.project_id%TYPE
2781: ,p_s_ci_id IN pa_budget_versions.ci_id%TYPE
2782: ,p_multiple_plan_types_flag IN VARCHAR2
2786: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2787: IS
2788:
2789: l_debug_mode VARCHAR2(30);
2790: l_target_plan_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
2791: l_target_time_phased_code pa_proj_fp_options.all_time_phased_code%TYPE;
2792: l_target_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
2793:
2794: l_token_ci_id varchar2(150);
2787: IS
2788:
2789: l_debug_mode VARCHAR2(30);
2790: l_target_plan_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
2791: l_target_time_phased_code pa_proj_fp_options.all_time_phased_code%TYPE;
2792: l_target_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
2793:
2794: l_token_ci_id varchar2(150);
2795: l_token_v_type VARCHAR2(30);
2788:
2789: l_debug_mode VARCHAR2(30);
2790: l_target_plan_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
2791: l_target_time_phased_code pa_proj_fp_options.all_time_phased_code%TYPE;
2792: l_target_resource_list_id pa_proj_fp_options.all_resource_list_id%TYPE;
2793:
2794: l_token_ci_id varchar2(150);
2795: l_token_v_type VARCHAR2(30);
2796: l_time_phase_code_flag VARCHAR2(1);
2796: l_time_phase_code_flag VARCHAR2(1);
2797: l_baseline_funding_Flag pa_projects_All.baseline_funding_flag%type;
2798: l_s_rev_bv_id pa_budget_versions.budget_version_id%type;
2799: l_count number;
2800: l_s_bv_fp_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
2801: l_ci_number pa_control_items.ci_number%type;
2802: l_ci_type_name pa_ci_types_tl.short_name%type;
2803: l_s_bv_time_phased_code pa_proj_fp_options.revenue_time_phased_code%type;
2804: l_s_bv_resource_list_id pa_proj_fp_options.revenue_resource_list_id%type;
2799: l_count number;
2800: l_s_bv_fp_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
2801: l_ci_number pa_control_items.ci_number%type;
2802: l_ci_type_name pa_ci_types_tl.short_name%type;
2803: l_s_bv_time_phased_code pa_proj_fp_options.revenue_time_phased_code%type;
2804: l_s_bv_resource_list_id pa_proj_fp_options.revenue_resource_list_id%type;
2805:
2806: l_ci_aprv_bv_option_id pa_proj_fp_options.proj_fp_options_id%TYPE;
2807: l_ci_aprv_cw_bv_id pa_budget_versions.budget_version_id%TYPE;
2800: l_s_bv_fp_level_code pa_proj_fp_options.all_fin_plan_level_code%TYPE;
2801: l_ci_number pa_control_items.ci_number%type;
2802: l_ci_type_name pa_ci_types_tl.short_name%type;
2803: l_s_bv_time_phased_code pa_proj_fp_options.revenue_time_phased_code%type;
2804: l_s_bv_resource_list_id pa_proj_fp_options.revenue_resource_list_id%type;
2805:
2806: l_ci_aprv_bv_option_id pa_proj_fp_options.proj_fp_options_id%TYPE;
2807: l_ci_aprv_cw_bv_id pa_budget_versions.budget_version_id%TYPE;
2808: l_ci_aprv_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
2802: l_ci_type_name pa_ci_types_tl.short_name%type;
2803: l_s_bv_time_phased_code pa_proj_fp_options.revenue_time_phased_code%type;
2804: l_s_bv_resource_list_id pa_proj_fp_options.revenue_resource_list_id%type;
2805:
2806: l_ci_aprv_bv_option_id pa_proj_fp_options.proj_fp_options_id%TYPE;
2807: l_ci_aprv_cw_bv_id pa_budget_versions.budget_version_id%TYPE;
2808: l_ci_aprv_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
2809: l_return_status VARCHAR2(1) := NULL;
2810: l_msg_count NUMBER := NULL;
2804: l_s_bv_resource_list_id pa_proj_fp_options.revenue_resource_list_id%type;
2805:
2806: l_ci_aprv_bv_option_id pa_proj_fp_options.proj_fp_options_id%TYPE;
2807: l_ci_aprv_cw_bv_id pa_budget_versions.budget_version_id%TYPE;
2808: l_ci_aprv_plan_type_id pa_proj_fp_options.fin_plan_type_id%TYPE;
2809: l_return_status VARCHAR2(1) := NULL;
2810: l_msg_count NUMBER := NULL;
2811: l_msg_data VARCHAR2(2000) := NULL;
2812: l_data VARCHAR2(2000) := NULL;
2877: -- Get plan_type_id of approved budget plan type
2878:
2879: SELECT fin_plan_type_id
2880: INTO l_ci_aprv_plan_type_id
2881: FROM pa_proj_fp_options po
2882: WHERE
2883: po.project_id = p_project_id
2884: AND fin_plan_option_level_code = 'PLAN_TYPE'
2885: AND DECODE
2924: INTO
2925: l_target_plan_level_code,
2926: l_target_resource_list_id,
2927: l_target_time_phased_code
2928: FROM pa_proj_fp_options po
2929: WHERE
2930: po.project_id = p_project_id
2931: AND fin_plan_type_id = l_ci_aprv_plan_type_id
2932: AND fin_plan_version_id = l_ci_aprv_cw_bv_id;
2943: -- Get plan_type_id of approved budget plan type
2944:
2945: SELECT fin_plan_type_id
2946: INTO l_ci_aprv_plan_type_id
2947: FROM pa_proj_fp_options po
2948: WHERE
2949: po.project_id = p_project_id
2950: AND fin_plan_option_level_code = 'PLAN_TYPE'
2951: AND ( NVL(po.approved_rev_plan_type_flag,'N') = 'Y'
2992: INTO
2993: l_target_plan_level_code,
2994: l_target_resource_list_id,
2995: l_target_time_phased_code
2996: FROM pa_proj_fp_options po
2997: WHERE
2998: po.project_id = p_project_id
2999: AND fin_plan_type_id = l_ci_aprv_plan_type_id
3000: AND fin_plan_version_id = l_ci_aprv_cw_bv_id;
3026: po.revenue_resource_list_id into
3027: l_s_bv_fp_level_code,
3028: l_s_bv_time_phased_code,
3029: l_s_bv_resource_list_id
3030: FROM pa_proj_fp_options po,
3031: pa_budget_versions bv
3032: WHERE
3033: bv.project_id = p_project_id and
3034: bv.ci_id = p_s_ci_id and
3231:
3232: l_ci_version_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
3233: l_version_type_tbl PA_PLSQL_DATATYPES.Char30TabTyp;
3234: l_fin_plan_type_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
3235: l_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
3236: l_curr_work_version_id pa_proj_fp_options.fin_plan_version_id%TYPE;
3237: l_attr_same_flag VARCHAR2(1);
3238:
3239: --Added by Xin Liu. For enhancement.
3232: l_ci_version_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
3233: l_version_type_tbl PA_PLSQL_DATATYPES.Char30TabTyp;
3234: l_fin_plan_type_id_tbl PA_PLSQL_DATATYPES.IdTabTyp;
3235: l_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
3236: l_curr_work_version_id pa_proj_fp_options.fin_plan_version_id%TYPE;
3237: l_attr_same_flag VARCHAR2(1);
3238:
3239: --Added by Xin Liu. For enhancement.
3240: l_source_id_tbl SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
3609:
3610: procedure chk_res_resgrp_mismatch(
3611: p_project_id in number,
3612: p_s_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
3613: p_s_fin_plan_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE,
3614: p_t_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
3615: p_t_fin_plan_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE,
3616: p_calling_mode in varchar2,
3617: x_res_resgr_mismatch_flag OUT NOCOPY varchar2, --File.Sql.39 bug 4440895
3611: p_project_id in number,
3612: p_s_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
3613: p_s_fin_plan_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE,
3614: p_t_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
3615: p_t_fin_plan_level_code IN pa_proj_fp_options.all_fin_plan_level_code%TYPE,
3616: p_calling_mode in varchar2,
3617: x_res_resgr_mismatch_flag OUT NOCOPY varchar2, --File.Sql.39 bug 4440895
3618: x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3619: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3667: select o.proj_fp_options_id,
3668: bv.version_type into
3669: l_source_fp_opt_id,
3670: l_source_ver_type
3671: from pa_proj_fp_options o,
3672: pa_budget_versions bv
3673: where
3674: bv.budget_version_id = p_s_budget_version_id and
3675: bv.fin_plan_type_id = o.fin_plan_type_id and
3680: select o.proj_fp_options_id ,
3681: bv.version_type into
3682: l_target_fp_opt_id,
3683: l_target_ver_type
3684: from pa_proj_fp_options o,
3685: pa_budget_versions bv
3686: where
3687: bv.budget_version_id = p_t_budget_version_id and
3688: bv.fin_plan_type_id = o.fin_plan_type_id and
4079: select o.proj_fp_options_id,
4080: bv.version_type into
4081: l_source_fp_opt_id,
4082: l_source_ver_type
4083: from pa_proj_fp_options o,
4084: pa_budget_versions bv
4085: where
4086: bv.budget_version_id = p_s_budget_version_id and
4087: bv.fin_plan_type_id = o.fin_plan_type_id and
4098: select o.proj_fp_options_id ,
4099: bv.version_type into
4100: l_target_fp_opt_id,
4101: l_target_ver_type
4102: from pa_proj_fp_options o,
4103: pa_budget_versions bv
4104: where
4105: bv.budget_version_id = p_t_budget_version_id and
4106: bv.fin_plan_type_id = o.fin_plan_type_id and
4441:
4442: l_assigned_flag VARCHAR2(1) := 'N';
4443: l_continue_flag VARCHAR2(1) := 'Y';
4444: l_row_count NUMBER := 0;
4445: l_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE;
4446: l_module_name VARCHAR2(30) := 'ctrl_itm_utls.plan_summ_data';
4447: l_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4448: l_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4449: l_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4444: l_row_count NUMBER := 0;
4445: l_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE;
4446: l_module_name VARCHAR2(30) := 'ctrl_itm_utls.plan_summ_data';
4447: l_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4448: l_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4449: l_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4450: l_report_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4451: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4452: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4445: l_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE;
4446: l_module_name VARCHAR2(30) := 'ctrl_itm_utls.plan_summ_data';
4447: l_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4448: l_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4449: l_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4450: l_report_version_type PA_BUDGET_VERSIONS.version_type%TYPE;
4451: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
4452: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
4453: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
4474: l_set_ob_url_flag VARCHAR2(1) := 'Y';
4475: l_set_cb_url_flag VARCHAR2(1) := 'Y';
4476: l_set_cpb_url_flag VARCHAR2(1) := 'Y';
4477: l_set_ccw_url_flag VARCHAR2(1) := 'Y';
4478: l_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
4479: l_pa_cw_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4480: l_pa_cw_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4481: l_pa_ob_cost_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4482: l_pa_ob_revenue_bv_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4489: l_context VARCHAR2(30);
4490:
4491: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4492:
4493: l_report_labor_hrs_code pa_proj_fp_options.report_labor_hrs_from_code%TYPE; /* Bug 4038253 */
4494: l_pref_code_for_pt_of_ver pa_proj_fp_options.fin_plan_preference_code%TYPE; /* Bug 4038253 */
4495:
4496: CURSOR c_lookup_summary IS
4497: SELECT MEANING,to_number(LOOKUP_CODE)
4490:
4491: l_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE;
4492:
4493: l_report_labor_hrs_code pa_proj_fp_options.report_labor_hrs_from_code%TYPE; /* Bug 4038253 */
4494: l_pref_code_for_pt_of_ver pa_proj_fp_options.fin_plan_preference_code%TYPE; /* Bug 4038253 */
4495:
4496: CURSOR c_lookup_summary IS
4497: SELECT MEANING,to_number(LOOKUP_CODE)
4498: FROM PA_LOOKUPS
4511: fin_plan_preference_code,
4512: report_labor_hrs_from_code,
4513: approved_cost_plan_type_flag,
4514: approved_rev_plan_type_flag
4515: FROM pa_proj_fp_options
4516: WHERE project_id = c_project_id
4517: AND (approved_cost_plan_type_flag = 'Y' or approved_rev_plan_type_flag = 'Y')
4518: AND fin_plan_option_level_code = 'PLAN_TYPE';
4519:
4518: AND fin_plan_option_level_code = 'PLAN_TYPE';
4519:
4520: CURSOR c_original_baselined
4521: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4522: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4523: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4524: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4525: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS
4526: SELECT nvl(sum(decode(pbv.version_type,
4519:
4520: CURSOR c_original_baselined
4521: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4522: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4523: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4524: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4525: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS
4526: SELECT nvl(sum(decode(pbv.version_type,
4527: c_report_version_type,
4520: CURSOR c_original_baselined
4521: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4522: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4523: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4524: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4525: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS
4526: SELECT nvl(sum(decode(pbv.version_type,
4527: c_report_version_type,
4528: nvl(pbv.labor_quantity,0),0)),0),
4548: AND pbv.budget_status_code = 'B';
4549:
4550: CURSOR c_current_baselined
4551: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4552: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4553: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4554: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4555: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS
4556: SELECT nvl(sum(decode(pbv.version_type,
4549:
4550: CURSOR c_current_baselined
4551: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4552: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4553: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4554: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4555: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS
4556: SELECT nvl(sum(decode(pbv.version_type,
4557: c_report_version_type,
4550: CURSOR c_current_baselined
4551: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4552: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4553: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4554: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4555: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS
4556: SELECT nvl(sum(decode(pbv.version_type,
4557: c_report_version_type,
4558: nvl(pbv.labor_quantity,0),0)),0),
4579: AND pbv.budget_status_code = 'B';
4580:
4581: CURSOR c_current_working
4582: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4583: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4584: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4585: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4586: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS
4587: SELECT nvl(sum(decode(pbv.version_type,
4580:
4581: CURSOR c_current_working
4582: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4583: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4584: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4585: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4586: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS
4587: SELECT nvl(sum(decode(pbv.version_type,
4588: c_report_version_type,
4581: CURSOR c_current_working
4582: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4583: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4584: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4585: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4586: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS
4587: SELECT nvl(sum(decode(pbv.version_type,
4588: c_report_version_type,
4589: nvl(pbv.labor_quantity,0),0)),0),
4617: included in the current baseline version(as it was done earlier).
4618: */
4619: /* CURSOR c_change_documents_current
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,
4618: */
4619: /* CURSOR c_change_documents_current
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),
4619: /* CURSOR c_change_documents_current
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),
4667: -- Bug 5845142. Take the revenue amounts only from revenue impact.Note that cost impacts
4668: -- with ALL version type can have revenue amounts.
4669: CURSOR c_change_documents_current
4670: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4671: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4672: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4673: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4674: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS -- Raja review
4675: Select nvl(sum(decode(c_report_version_type,
4668: -- with ALL version type can have revenue amounts.
4669: CURSOR c_change_documents_current
4670: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4671: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4672: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4673: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4674: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS -- Raja review
4675: Select nvl(sum(decode(c_report_version_type,
4676: 'COST', decode(merge.version_type, 'COST',nvl(merge.impl_quantity,0),0),
4669: CURSOR c_change_documents_current
4670: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4671: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4672: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4673: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4674: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS -- Raja review
4675: Select nvl(sum(decode(c_report_version_type,
4676: 'COST', decode(merge.version_type, 'COST',nvl(merge.impl_quantity,0),0),
4677: 'REVENUE', decode(merge.version_type, 'REVENUE',nvl(merge.impl_quantity,0),0),
4709: -- Bug 5845142. Take the revenue amounts only from revenue impact.Note that cost impacts
4710: -- with ALL version type can have revenue amounts.
4711: CURSOR c_change_documents_prior
4712: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4713: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4714: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4715: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4716: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS -- Raja review
4717: Select nvl(sum(decode(c_report_version_type,
4710: -- with ALL version type can have revenue amounts.
4711: CURSOR c_change_documents_prior
4712: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4713: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4714: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4715: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4716: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS -- Raja review
4717: Select nvl(sum(decode(c_report_version_type,
4718: 'COST', decode(merge.version_type, 'COST',nvl(merge.impl_quantity,0),0),
4711: CURSOR c_change_documents_prior
4712: (c_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE,
4713: c_ac_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4714: c_ar_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE,
4715: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4716: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE) IS -- Raja review
4717: Select nvl(sum(decode(c_report_version_type,
4718: 'COST', decode(merge.version_type, 'COST',nvl(merge.impl_quantity,0),0),
4719: 'REVENUE', decode(merge.version_type, 'REVENUE',nvl(merge.impl_quantity,0),0),
4764: /* commented by Raja rewritten the cursor below
4765: CURSOR c_change_documents_status
4766: (c_project_id pa_projects_all.project_id%TYPE,
4767: c_system_status_code pa_ci_statuses_v.project_system_status_code%TYPE,
4768: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE) IS
4769: SELECT nvl(sum(pfca.people_effort),0),
4770: nvl(sum(pfca.equipment_effort),0),
4771: nvl(sum(decode(c_margin_derived_from_code,'B',
4772: nvl(pfca.burdened_cost,0),
4785: -- the bug for details
4786: CURSOR c_change_documents_status
4787: (c_project_id pa_projects_all.project_id%TYPE,
4788: c_system_status_code pa_ci_statuses_v.project_system_status_code%TYPE,
4789: c_margin_derived_from_code PA_PROJ_FP_OPTIONS.margin_derived_from_code%TYPE,
4790: c_report_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE,
4791: c_appr_rev_cw_version_id PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE) IS -- Raja review
4792: SELECT (cost_query.people_effort + revenue_query.people_effort),
4793: (cost_query.equipment_effort + revenue_query.equipment_effort),
5074:
5075: BEGIN
5076: SELECT nvl(MARGIN_DERIVED_FROM_CODE,'B')
5077: INTO l_margin_derived_from_code
5078: FROM PA_PROJ_FP_OPTIONS
5079: WHERE project_id = p_project_id
5080: AND fin_plan_option_level_code = 'PLAN_TYPE'
5081: AND approved_cost_plan_type_flag = 'Y';
5082:
5205: INTO
5206: l_fin_plan_type_id_tbl(1),
5207: l_fin_plan_preference_code_tbl(1),
5208: l_rep_lab_from_code_tbl(1)
5209: FROM pa_proj_fp_options
5210: WHERE project_id = p_project_id
5211: AND fin_plan_option_level_code = 'PLAN_TYPE'
5212: AND fin_plan_type_id in(select FIN_PLAN_TYPE_ID from
5213: PA_BUDGET_VERSIONS where
5968: SELECT fin_plan_preference_code,
5969: report_labor_hrs_from_code
5970: INTO l_pref_code_for_pt_of_ver,
5971: l_report_labor_hrs_code
5972: FROM pa_proj_fp_options
5973: WHERE project_id = p_project_id
5974: AND fin_plan_type_id = l_fin_plan_type_id
5975: AND fin_plan_option_level_code = 'PLAN_TYPE';
5976: EXCEPTION
6888: )
6889: RETURN NUMBER
6890: IS
6891: l_return_cost NUMBER :=0;
6892: l_margin_derived_from_code pa_proj_fp_options.margin_derived_from_code%TYPE;
6893: l_debug_mode VARCHAR2(30);
6894: l_module_name VARCHAR2(30) := 'ctrl_utils.get_pc_cost';
6895: l_budget_version_type pa_budget_versions.version_type%TYPE;
6896: l_version_type pa_budget_versions.version_type%TYPE;
6920: -----------------------------------------
6921:
6922: SELECT nvl(MARGIN_DERIVED_FROM_CODE,'B')
6923: INTO l_margin_derived_from_code
6924: FROM PA_PROJ_FP_OPTIONS a
6925: WHERE a.FIN_PLAN_VERSION_ID = p_ci_version_id
6926: AND a.fin_plan_option_level_code = 'PLAN_VERSION';
6927:
6928: ----------------------------------------------------------------
7067: --End of variables used for debugging
7068:
7069: l_row_count NUMBER := 0;
7070: l_version_type PA_BUDGET_VERSIONS.VERSION_TYPE%TYPE;
7071: l_fin_plan_type_id PA_PROJ_FP_OPTIONS.FIN_PLAN_TYPE_ID%TYPE;
7072: l_labor_quantity PA_BUDGET_VERSIONS.LABOR_QUANTITY%TYPE;
7073: l_equipment_quantity PA_BUDGET_VERSIONS.EQUIPMENT_QUANTITY%TYPE;
7074: l_cost PA_BUDGET_VERSIONS.RAW_COST%TYPE;
7075: l_revenue PA_BUDGET_VERSIONS.REVENUE%TYPE;
7930: ,NVL(pfo.approved_rev_plan_type_flag,'N') approved_rev_plan_type_flag
7931: ,fin.plan_class_code
7932: ,pfo.fin_plan_preference_code
7933: FROM pa_fin_plan_types_vl fin,
7934: pa_proj_fp_options pfo
7935: WHERE pfo.project_id = p_project_id
7936: AND pfo.fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
7937: AND pfo.fin_plan_type_id = fin.fin_plan_type_id
7938: AND nvl(fin.use_for_workplan_flag,'N') <> 'Y'
8695:
8696: l_status_allows_cost_impl VARCHAR2(1);
8697: l_status_allows_rev_impl VARCHAR2(1);
8698:
8699: l_app_rev_plan_type_flag pa_proj_fp_options.approved_rev_plan_type_flag%TYPE;
8700:
8701: l_agreement_amount NUMBER;
8702: l_agreement_currency_code pa_agreements_all.agreement_currency_code%TYPE;
8703:
8716:
8717: --Bug 5845142
8718: l_t_app_cost_flag pa_budget_versions.approved_cost_plan_type_flag%TYPE;
8719: l_t_app_rev_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
8720: l_t_pt_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
8721:
8722: BEGIN
8723:
8724: pa_debug.set_curr_function( p_function => 'GET_IMPL_DETAILS',
8837: fin_plan_preference_code
8838: INTO l_t_app_cost_flag,
8839: l_t_app_rev_flag,
8840: l_t_pt_pref_code
8841: FROM pa_proj_fp_options
8842: WHERE project_id=p_project_id
8843: AND fin_plan_type_id=p_fin_plan_type_id
8844: AND fin_plan_version_id IS NULL;
8845:
9033: IF p_app_rev_plan_type_flag IS NULL THEN
9034: BEGIN
9035: SELECT approved_rev_plan_type_flag
9036: INTO l_app_rev_plan_type_flag
9037: FROM pa_proj_fp_options
9038: WHERE project_id = p_project_id
9039: AND fin_plan_option_level_code = 'PLAN_TYPE'
9040: AND fin_plan_type_id = p_fin_plan_type_id;
9041:
9444: INTO x_report_cost_using,
9445: x_impact_in_mc_flag,
9446: l_ci_resource_list_id1,
9447: l_ci_resource_list_id2
9448: FROM pa_proj_fp_options pfoc,
9449: pa_proj_fp_options pfor
9450: WHERE pfoc.fin_plan_version_id = NVL(l_cost_ci_version_id,NVL(l_all_ci_version_id,l_rev_ci_version_id))
9451: AND pfor.fin_plan_version_id = NVL(l_rev_ci_version_id,NVL(l_all_ci_version_id,l_cost_ci_version_id));
9452:
9445: x_impact_in_mc_flag,
9446: l_ci_resource_list_id1,
9447: l_ci_resource_list_id2
9448: FROM pa_proj_fp_options pfoc,
9449: pa_proj_fp_options pfor
9450: WHERE pfoc.fin_plan_version_id = NVL(l_cost_ci_version_id,NVL(l_all_ci_version_id,l_rev_ci_version_id))
9451: AND pfor.fin_plan_version_id = NVL(l_rev_ci_version_id,NVL(l_all_ci_version_id,l_cost_ci_version_id));
9452:
9453: ELSE
9461: INTO x_report_cost_using,
9462: x_impact_in_mc_flag,
9463: l_ci_resource_list_id1,
9464: l_ci_resource_list_id2
9465: FROM pa_proj_fp_options pfo
9466: WHERE pfo.fin_plan_version_id=NVL(l_rev_ci_version_id,NVL(l_all_ci_version_id,l_cost_ci_version_id)) ;
9467:
9468: END IF;
9469:
10266: p_targ_app_cost_flag IN VARCHAR2,
10267: p_targ_app_rev_flag IN VARCHAR2)
10268: RETURN VARCHAR2 IS
10269: --Bug 5845142
10270: l_app_cost_pt_rev_flag pa_proj_fp_options.approved_cost_plan_type_flag%TYPE;
10271: l_app_cost_pt_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
10272: BEGIN
10273:
10274: IF l_cvc_project_id=p_project_id THEN
10267: p_targ_app_rev_flag IN VARCHAR2)
10268: RETURN VARCHAR2 IS
10269: --Bug 5845142
10270: l_app_cost_pt_rev_flag pa_proj_fp_options.approved_cost_plan_type_flag%TYPE;
10271: l_app_cost_pt_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
10272: BEGIN
10273:
10274: IF l_cvc_project_id=p_project_id THEN
10275:
10283: SELECT nvl(pfo.approved_rev_plan_type_flag,'N'),
10284: nvl(pfo.fin_plan_preference_code,'N')
10285: INTO l_app_cost_pt_rev_flag,
10286: l_app_cost_pt_pref_code
10287: FROM pa_proj_fp_options pfo
10288: WHERE pfo.project_id = p_project_id
10289: AND pfo.fin_plan_version_id IS NULL
10290: AND pfo.approved_cost_plan_type_flag ='Y';
10291: