[Home] [Help]
18:
19: --These variables are internally used by the API get_fin_struct_id. They should not be used by other
20: --APIs in this package. These are created for bug 3546208
21: l_edit_plan_project_id pa_projects_all.project_id%TYPE;
22: l_edit_plan_struct_id pa_budget_versions.project_structure_version_id%TYPE;
23: l_edit_plan_bv_id pa_budget_versions.budget_version_id%TYPE;
24:
25: PROCEDURE get_workplan_bvids
26: (p_project_id IN pa_budget_versions.project_id%TYPE,
19: --These variables are internally used by the API get_fin_struct_id. They should not be used by other
20: --APIs in this package. These are created for bug 3546208
21: l_edit_plan_project_id pa_projects_all.project_id%TYPE;
22: l_edit_plan_struct_id pa_budget_versions.project_structure_version_id%TYPE;
23: l_edit_plan_bv_id pa_budget_versions.budget_version_id%TYPE;
24:
25: PROCEDURE get_workplan_bvids
26: (p_project_id IN pa_budget_versions.project_id%TYPE,
27: p_element_version_id IN pa_proj_element_versions.element_version_id%TYPE,
22: l_edit_plan_struct_id pa_budget_versions.project_structure_version_id%TYPE;
23: l_edit_plan_bv_id pa_budget_versions.budget_version_id%TYPE;
24:
25: PROCEDURE get_workplan_bvids
26: (p_project_id IN pa_budget_versions.project_id%TYPE,
27: p_element_version_id IN pa_proj_element_versions.element_version_id%TYPE,
28: x_current_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
29: x_baselined_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
30: x_published_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
24:
25: PROCEDURE get_workplan_bvids
26: (p_project_id IN pa_budget_versions.project_id%TYPE,
27: p_element_version_id IN pa_proj_element_versions.element_version_id%TYPE,
28: x_current_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
29: x_baselined_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
30: x_published_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
31: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
32: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
25: PROCEDURE get_workplan_bvids
26: (p_project_id IN pa_budget_versions.project_id%TYPE,
27: p_element_version_id IN pa_proj_element_versions.element_version_id%TYPE,
28: x_current_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
29: x_baselined_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
30: x_published_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
31: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
32: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
33: x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
26: (p_project_id IN pa_budget_versions.project_id%TYPE,
27: p_element_version_id IN pa_proj_element_versions.element_version_id%TYPE,
28: x_current_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
29: x_baselined_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
30: x_published_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
31: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
32: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
33: x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
34: l_wp_structure_version_id pa_budget_versions.project_structure_version_id%TYPE;
30: x_published_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
31: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
32: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
33: x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
34: l_wp_structure_version_id pa_budget_versions.project_structure_version_id%TYPE;
35: BEGIN
36: x_return_status := FND_API.G_RET_STS_SUCCESS;
37: begin
38: -- RETRIEVE THE WORKPLAN STRUCTURE VERSION ID which is used for
35: BEGIN
36: x_return_status := FND_API.G_RET_STS_SUCCESS;
37: begin
38: -- RETRIEVE THE WORKPLAN STRUCTURE VERSION ID which is used for
39: -- join with pa_budget_versions
40: select evs.element_version_id -- changed by shyugen
41: into l_wp_structure_version_id
42: from pa_proj_element_versions ev,
43: pa_proj_elem_ver_structure evs
59: begin
60: -- RETRIEVE CURRENT VERSION ID
61: select budget_version_id
62: into x_current_version_id
63: from pa_budget_versions
64: where project_id = p_project_id and
65: wp_version_flag = 'Y' and
66: project_structure_version_id = l_wp_structure_version_id;
67: exception
74: into x_baselined_version_id
75: from pa_proj_elem_ver_structure str,
76: pa_proj_element_versions ppev1,
77: pa_proj_element_versions ppev2,
78: pa_budget_versions bv
79: where ppev1.element_version_id = p_element_version_id
80: and ppev1.project_id = ppev2.project_id
81: and ppev1.proj_element_id = ppev2.proj_element_id -- all the other task versions
82: and ppev2.parent_structure_version_id = str.element_version_id --the structure version of each task version
94: into x_published_version_id
95: from pa_proj_elem_ver_structure str,
96: pa_proj_element_versions ppev1,
97: pa_proj_element_versions ppev2,
98: pa_budget_versions bv
99: where ppev1.element_version_id = p_element_version_id
100: and ppev1.project_id = ppev2.project_id
101: and ppev1.proj_element_id = ppev2.proj_element_id -- all the other task versions
102: and ppev2.parent_structure_version_id = str.element_version_id --the structure version of each task version
133: * 07/14/2005 dlai if p_budget_version is a FORECAST version, x_original_version_id will
134: * be passed if it exists (regardless of the value of p_view_plan_flag)
135: */
136: PROCEDURE get_finplan_bvids
137: (p_project_id IN pa_budget_versions.project_id%TYPE,
138: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
139: p_view_plan_flag IN VARCHAR2 default 'N',
140: x_current_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
141: x_original_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
134: * be passed if it exists (regardless of the value of p_view_plan_flag)
135: */
136: PROCEDURE get_finplan_bvids
137: (p_project_id IN pa_budget_versions.project_id%TYPE,
138: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
139: p_view_plan_flag IN VARCHAR2 default 'N',
140: x_current_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
141: x_original_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
142: x_prior_fcst_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
136: PROCEDURE get_finplan_bvids
137: (p_project_id IN pa_budget_versions.project_id%TYPE,
138: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
139: p_view_plan_flag IN VARCHAR2 default 'N',
140: x_current_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
141: x_original_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
142: x_prior_fcst_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
143: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
144: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
137: (p_project_id IN pa_budget_versions.project_id%TYPE,
138: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
139: p_view_plan_flag IN VARCHAR2 default 'N',
140: x_current_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
141: x_original_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
142: x_prior_fcst_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
143: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
144: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
145: x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
138: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
139: p_view_plan_flag IN VARCHAR2 default 'N',
140: x_current_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
141: x_original_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
142: x_prior_fcst_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
143: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
144: x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
145: x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
146: l_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
150: select pt.plan_class_code,
151: po.fin_plan_preference_code
152: into l_plan_class_code,
153: l_fin_plan_pref_code
154: from pa_budget_versions bv,
155: pa_fin_plan_types_b pt,
156: pa_proj_fp_options po
157: where bv.budget_version_id = p_budget_version_id and
158: bv.fin_plan_type_id = pt.fin_plan_type_id and
163: -- RETRIEVE CURRENT BASELINED VERSION (IF IT EXISTS)
164: select bv.budget_version_id
165: into x_current_version_id
166: from pa_proj_fp_options po,
167: pa_budget_versions bv,
168: pa_proj_fp_options po2
169: where po.project_id = p_project_id and
170: po.fin_plan_version_id = p_budget_version_id and
171: po.fin_plan_option_level_code = 'PLAN_VERSION' and
183: begin
184: select bv.budget_version_id
185: into x_original_version_id
186: from pa_proj_fp_options po,
187: pa_budget_versions bv,
188: pa_proj_fp_options po2
189: where po.project_id = p_project_id and
190: po.fin_plan_version_id = p_budget_version_id and
191: po.fin_plan_option_level_code = 'PLAN_VERSION' and
205: begin
206: select bv.budget_version_id
207: into x_prior_fcst_version_id
208: from pa_proj_fp_options po,
209: pa_budget_versions bv
210: where po.project_id = p_project_id and
211: po.fin_plan_option_level_code = 'PLAN_VERSION' and
212: bv.primary_cost_forecast_flag = 'Y' and
213: po.fin_plan_version_id = bv.budget_version_id and
221: begin
222: select bv.budget_version_id
223: into x_prior_fcst_version_id
224: from pa_proj_fp_options po,
225: pa_budget_versions bv
226: where po.project_id = p_project_id and
227: po.fin_plan_option_level_code = 'PLAN_VERSION' and
228: bv.primary_rev_forecast_flag = 'Y' and
229: po.fin_plan_version_id = bv.budget_version_id and
239: begin
240: select bv.budget_version_id
241: into x_prior_fcst_version_id
242: from pa_proj_fp_options po,
243: pa_budget_versions bv,
244: pa_proj_fp_options po2
245: where po.project_id = p_project_id and
246: po.fin_plan_version_id = p_budget_version_id and
247: po.fin_plan_option_level_code = 'PLAN_VERSION' and
263: begin
264: select bv.budget_version_id
265: into x_original_version_id
266: from pa_proj_fp_options po,
267: pa_budget_versions bv
268: where po.project_id = p_project_id and
269: po.fin_plan_option_level_code = 'PLAN_VERSION' and
270: bv.approved_cost_plan_type_flag = 'Y' and
271: po.fin_plan_version_id = bv.budget_version_id and
279: begin
280: select bv.budget_version_id
281: into x_original_version_id
282: from pa_proj_fp_options po,
283: pa_budget_versions bv
284: where po.project_id = p_project_id and
285: po.fin_plan_option_level_code = 'PLAN_VERSION' and
286: bv.approved_rev_plan_type_flag = 'Y' and
287: po.fin_plan_version_id = bv.budget_version_id and
295: begin
296: select bv.budget_version_id
297: into x_original_version_id
298: from pa_proj_fp_options po,
299: pa_budget_versions bv
300: where po.project_id = p_project_id and
301: po.fin_plan_option_level_code = 'PLAN_VERSION' and
302: bv.approved_cost_plan_type_flag = 'Y' and
303: bv.approved_rev_plan_type_flag = 'Y' and
315: begin
316: select bv.budget_version_id
317: into x_current_version_id
318: from pa_proj_fp_options po,
319: pa_budget_versions bv
320: where po.project_id = p_project_id and
321: po.fin_plan_option_level_code = 'PLAN_VERSION' and
322: bv.approved_cost_plan_type_flag = 'Y' and
323: po.fin_plan_version_id = bv.budget_version_id and
331: begin
332: select bv.budget_version_id
333: into x_current_version_id
334: from pa_proj_fp_options po,
335: pa_budget_versions bv
336: where po.project_id = p_project_id and
337: po.fin_plan_option_level_code = 'PLAN_VERSION' and
338: bv.approved_rev_plan_type_flag = 'Y' and
339: po.fin_plan_version_id = bv.budget_version_id and
675: * 05/10/2005 - dlai - margin parameters are returned as null if
676: * p_budget_version_id refers to COST_ONLY or REVENUE_ONLY version
677: */
678: PROCEDURE get_common_budget_version_info
679: (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
680: p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE,
681: p_project_currency_code IN pa_projects_all.project_currency_code%TYPE,
682: p_projfunc_currency_code IN pa_projects_all.projfunc_currency_code%TYPE,
683: p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE,
682: p_projfunc_currency_code IN pa_projects_all.projfunc_currency_code%TYPE,
683: p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE,
684: p_line_start_date IN pa_budget_lines.start_date%TYPE := to_date(NULL),
685: p_line_end_date IN pa_budget_lines.end_date%TYPE := to_date(NULL),
686: x_budget_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
687: x_planning_start_date OUT NOCOPY pa_resource_assignments.planning_start_date%TYPE, --File.Sql.39 bug 4440895
688: x_planning_end_date OUT NOCOPY pa_resource_assignments.planning_end_date%TYPE, --File.Sql.39 bug 4440895
689: x_schedule_start_date OUT NOCOPY pa_resource_assignments.schedule_start_date%TYPE, --File.Sql.39 bug 4440895
690: x_schedule_end_date OUT NOCOPY pa_resource_assignments.schedule_start_date%TYPE, --File.Sql.39 bug 4440895
842: null, 0,
843: (SUM(bl.txn_burdened_cost) - SUM(nvl(bl.txn_init_burdened_cost,0)))/(SUM(bl.quantity) - SUM(nvl(init_quantity,0))))
844: from pa_resource_assignments ra,
845: pa_budget_lines bl,
846: pa_budget_versions bv,
847: pa_proj_fp_options po
848: where ra.resource_assignment_id = l_resource_assignment_id and
849: ra.resource_assignment_id = bl.resource_assignment_id and
850: bl.txn_currency_code = p_txn_currency_code and
970: -- DECODE(ra.rate_based_flag, 'Y', rac.txn_etc_burden_cost_rate, TO_NUMBER(NULL))
971: -- FROM pa_resource_assignments ra,
972: -- -- pa_budget_lines bl,
973: -- pa_resource_asgn_curr rac,
974: -- pa_budget_versions bv,
975: -- pa_proj_fp_options po
976: -- WHERE ra.resource_assignment_id = l_resource_assignment_id AND
977: -- -- ra.resource_assignment_id = bl.resource_assignment_id AND
978: -- -- bl.txn_currency_code = p_txn_currency_code AND
1166: x_margin_proj_cur,
1167: x_margin_proj_func_cur,
1168: x_margin_pct
1169: from pa_resource_assignments ra,
1170: pa_budget_versions bv,
1171: pa_proj_fp_options po
1172: where ra.resource_assignment_id = l_resource_assignment_id and
1173: ra.budget_version_id = bv.budget_version_id and
1174: bv.budget_version_id = po.fin_plan_version_id and
1397: x_etc_avg_burd_cost_rate
1398: FROM pa_resource_assignments ra,
1399: -- pa_budget_lines bl,
1400: pa_resource_asgn_curr rac,
1401: pa_budget_versions bv,
1402: pa_proj_fp_options po
1403: WHERE ra.resource_assignment_id = l_resource_assignment_id AND
1404: -- ra.resource_assignment_id = bl.resource_assignment_id AND
1405: -- bl.txn_currency_code = p_txn_currency_code AND
1509: * 06/02/2005 - dlai - queries for etc amounts should use nvl(..,0) so that they
1510: * are calculate correctly in case the act or fcst amounts are null
1511: */
1512: PROCEDURE get_common_bv_info_fcst
1513: (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
1514: p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE,
1515: p_project_currency_code IN pa_projects_all.project_currency_code%TYPE,
1516: p_projfunc_currency_code IN pa_projects_all.projfunc_currency_code%TYPE,
1517: p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE,
1516: p_projfunc_currency_code IN pa_projects_all.projfunc_currency_code%TYPE,
1517: p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE,
1518: p_line_start_date IN pa_budget_lines.start_date%TYPE := to_date(NULL),
1519: p_line_end_date IN pa_budget_lines.end_date%TYPE := to_date(NULL),
1520: x_budget_version_id OUT NOCOPY pa_budget_versions.budget_version_id%TYPE, --File.Sql.39 bug 4440895
1521: x_planning_start_date OUT NOCOPY pa_resource_assignments.planning_start_date%TYPE, --File.Sql.39 bug 4440895
1522: x_planning_end_date OUT NOCOPY pa_resource_assignments.planning_end_date%TYPE, --File.Sql.39 bug 4440895
1523: x_schedule_start_date OUT NOCOPY pa_resource_assignments.schedule_start_date%TYPE, --File.Sql.39 bug 4440895
1524: x_schedule_end_date OUT NOCOPY pa_resource_assignments.schedule_start_date%TYPE, --File.Sql.39 bug 4440895
1755: po.margin_derived_from_code
1756: FROM pa_resource_assignments ra,
1757: pa_budget_lines bl,
1758: -- pa_resource_asgn_curr rac,
1759: pa_budget_versions bv,
1760: pa_proj_fp_options po
1761: WHERE ra.resource_assignment_id = l_resource_assignment_id AND
1762: ra.resource_assignment_id = bl.resource_assignment_id AND
1763: bl.txn_currency_code = p_txn_currency_code AND
2009: x_etc_margin_pct,
2010: x_fcst_margin_pct,
2011: l_margin_derived_from_code
2012: from pa_resource_assignments ra,
2013: pa_budget_versions bv,
2014: pa_proj_fp_options po
2015: where ra.resource_assignment_id = l_resource_assignment_id and
2016: ra.budget_version_id = bv.budget_version_id and
2017: bv.budget_version_id = po.fin_plan_version_id and
2369: l_margin_derived_from_code
2370: FROM pa_resource_assignments ra,
2371: -- pa_budget_lines bl,
2372: pa_resource_asgn_curr rac,
2373: pa_budget_versions bv,
2374: pa_proj_fp_options po
2375: where ra.resource_assignment_id = l_resource_assignment_id and
2376: -- ra.resource_assignment_id = bl.resource_assignment_id and
2377: -- bl.txn_currency_code = p_txn_currency_code and
2677: * version 115.29 of PAFPPEUB.pls
2678: */
2679: PROCEDURE add_new_resource_assignments
2680: (p_context IN VARCHAR2,
2681: p_project_id IN pa_budget_versions.project_id%TYPE,
2682: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
2683: p_task_elem_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
2684: p_resource_list_member_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
2685: p_cbs_element_id_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),--bug#16200605
2678: */
2679: PROCEDURE add_new_resource_assignments
2680: (p_context IN VARCHAR2,
2681: p_project_id IN pa_budget_versions.project_id%TYPE,
2682: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
2683: p_task_elem_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
2684: p_resource_list_member_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
2685: p_cbs_element_id_tbl IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),--bug#16200605
2686: p_quantity_tbl IN SYSTEM.PA_NUM_TBL_TYPE DEFAULT SYSTEM.PA_NUM_TBL_TYPE(),
3144: /* REVISION HISTORY
3145: * Created: 07/20/2004 by DLAI for bug 3747582
3146: */
3147: FUNCTION get_bv_name_from_id
3148: (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE) return VARCHAR2 is
3149: l_return_value pa_budget_versions.version_name%TYPE;
3150: BEGIN
3151: select version_name
3152: into l_return_value
3145: * Created: 07/20/2004 by DLAI for bug 3747582
3146: */
3147: FUNCTION get_bv_name_from_id
3148: (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE) return VARCHAR2 is
3149: l_return_value pa_budget_versions.version_name%TYPE;
3150: BEGIN
3151: select version_name
3152: into l_return_value
3153: from pa_budget_versions
3149: l_return_value pa_budget_versions.version_name%TYPE;
3150: BEGIN
3151: select version_name
3152: into l_return_value
3153: from pa_budget_versions
3154: where budget_version_id = p_budget_version_id;
3155: return l_return_value;
3156: EXCEPTION
3157: when NO_DATA_FOUND then
3162:
3163: --Created for bug 3546208. This function will return the financial structure version id for the project
3164: --id passed.
3165: FUNCTION get_fin_struct_id(p_project_id pa_projects_all.project_id%TYPE,
3166: p_budget_version_id pa_budget_versions.budget_Version_id%TYPE)
3167: RETURN NUMBER
3168: IS
3169: BEGIN
3170: IF (l_edit_plan_project_id IS NULL OR
3176: SELECT DECODE(wp_version_flag,
3177: 'Y',project_structure_version_id,
3178: PA_PROJECT_STRUCTURE_UTILS.GET_FIN_STRUC_VER_ID(project_id))
3179: INTO l_edit_plan_struct_id
3180: FROM pa_budget_versions
3181: WHERE budget_Version_id=p_budget_version_id;
3182:
3183: l_edit_plan_project_id:= p_project_id;
3184: l_edit_plan_bv_id := p_budget_version_id;
3274:
3275:
3276: FUNCTION get_task_percent_complete
3277: (p_project_id IN pa_projects_all.project_id%TYPE,
3278: p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE,
3279: p_proj_element_id IN pa_proj_element_versions.proj_element_id%TYPE,
3280: p_calling_context IN VARCHAR2) return NUMBER
3281: is
3282: l_return_value NUMBER;
3355: x_prior_fcst_version_id = Version previous to the current baselined version
3356: of same plan type
3357: */
3358: FUNCTION get_prior_forecast_version_id
3359: (p_plan_version_id IN pa_budget_versions.budget_version_id%TYPE,
3360: p_project_id IN pa_projects_all.project_id%TYPE
3361: ) return NUMBER
3362: is
3363: l_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
3361: ) return NUMBER
3362: is
3363: l_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
3364: l_fin_plan_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
3365: l_curr_fcst_ver_id pa_budget_versions.budget_version_id%TYPE := NULL;
3366: l_curr_fcst_ver_num pa_budget_versions.version_number%TYPE := NULL;
3367: l_fp_type_id pa_budget_versions.fin_plan_type_id%TYPE := NULL;
3368: l_version_type pa_budget_versions.version_type%TYPE := NULL;
3369: x_prior_fcst_version_id pa_budget_versions.budget_version_id%TYPE := NULL;
3362: is
3363: l_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
3364: l_fin_plan_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
3365: l_curr_fcst_ver_id pa_budget_versions.budget_version_id%TYPE := NULL;
3366: l_curr_fcst_ver_num pa_budget_versions.version_number%TYPE := NULL;
3367: l_fp_type_id pa_budget_versions.fin_plan_type_id%TYPE := NULL;
3368: l_version_type pa_budget_versions.version_type%TYPE := NULL;
3369: x_prior_fcst_version_id pa_budget_versions.budget_version_id%TYPE := NULL;
3370:
3363: l_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
3364: l_fin_plan_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
3365: l_curr_fcst_ver_id pa_budget_versions.budget_version_id%TYPE := NULL;
3366: l_curr_fcst_ver_num pa_budget_versions.version_number%TYPE := NULL;
3367: l_fp_type_id pa_budget_versions.fin_plan_type_id%TYPE := NULL;
3368: l_version_type pa_budget_versions.version_type%TYPE := NULL;
3369: x_prior_fcst_version_id pa_budget_versions.budget_version_id%TYPE := NULL;
3370:
3371:
3364: l_fin_plan_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
3365: l_curr_fcst_ver_id pa_budget_versions.budget_version_id%TYPE := NULL;
3366: l_curr_fcst_ver_num pa_budget_versions.version_number%TYPE := NULL;
3367: l_fp_type_id pa_budget_versions.fin_plan_type_id%TYPE := NULL;
3368: l_version_type pa_budget_versions.version_type%TYPE := NULL;
3369: x_prior_fcst_version_id pa_budget_versions.budget_version_id%TYPE := NULL;
3370:
3371:
3372: BEGIN
3365: l_curr_fcst_ver_id pa_budget_versions.budget_version_id%TYPE := NULL;
3366: l_curr_fcst_ver_num pa_budget_versions.version_number%TYPE := NULL;
3367: l_fp_type_id pa_budget_versions.fin_plan_type_id%TYPE := NULL;
3368: l_version_type pa_budget_versions.version_type%TYPE := NULL;
3369: x_prior_fcst_version_id pa_budget_versions.budget_version_id%TYPE := NULL;
3370:
3371:
3372: BEGIN
3373: select pt.plan_class_code,
3374: decode(bv.version_type,'COST','COST_ONLY','REVENUE','REVENUE_ONLY',
3375: 'ALL','COST_AND_REV_SAME')
3376: into l_plan_class_code,
3377: l_fin_plan_pref_code
3378: from pa_budget_versions bv,
3379: pa_fin_plan_types_b pt
3380: where bv.budget_version_id = p_plan_version_id and
3381: bv.fin_plan_type_id = pt.fin_plan_type_id;
3382:
3389: select bv.budget_version_id,
3390: bv.version_number
3391: into l_curr_fcst_ver_id,
3392: l_curr_fcst_ver_num
3393: from pa_budget_versions bv
3394: where bv.project_id = p_project_id and
3395: bv.primary_cost_forecast_flag = 'Y' and
3396: bv.current_flag = 'Y';
3397:
3396: bv.current_flag = 'Y';
3397:
3398: select bv1.budget_version_id
3399: into x_prior_fcst_version_id
3400: from pa_budget_versions bv1
3401: where bv1.project_id = p_project_id
3402: and bv1.primary_cost_forecast_flag = 'Y'
3403: and bv1.budget_status_code = 'B'
3404: /*and bv1.version_number = l_curr_fcst_ver_num - 1;
3403: and bv1.budget_status_code = 'B'
3404: /*and bv1.version_number = l_curr_fcst_ver_num - 1;
3405: commented and added below for bug 6870324 */
3406: and bv1.version_number = (select max(bv2.version_number)
3407: from pa_budget_versions bv2
3408: where bv2.project_id = p_project_id
3409: and bv2.primary_cost_forecast_flag = 'Y'
3410: and bv2.budget_status_code = 'B'
3411: and bv2.version_number < l_curr_fcst_ver_num
3422: select bv.budget_version_id,
3423: bv.version_number
3424: into l_curr_fcst_ver_id,
3425: l_curr_fcst_ver_num
3426: from pa_budget_versions bv
3427: where bv.project_id = p_project_id and
3428: bv.primary_rev_forecast_flag = 'Y' and
3429: bv.current_flag = 'Y';
3430:
3429: bv.current_flag = 'Y';
3430:
3431: select bv1.budget_version_id
3432: into x_prior_fcst_version_id
3433: from pa_budget_versions bv1
3434: where bv1.project_id = p_project_id
3435: and bv1.primary_rev_forecast_flag = 'Y'
3436: and bv1.budget_status_code = 'B'
3437: /*and bv1.version_number = l_curr_fcst_ver_num - 1;
3436: and bv1.budget_status_code = 'B'
3437: /*and bv1.version_number = l_curr_fcst_ver_num - 1;
3438: commented and added below for bug 6870324 */
3439: and bv1.version_number = (select max(bv2.version_number)
3440: from pa_budget_versions bv2
3441: where bv2.project_id = p_project_id
3442: and bv2.primary_rev_forecast_flag = 'Y'
3443: and bv2.budget_status_code = 'B'
3444: and bv2.version_number < l_curr_fcst_ver_num
3454: select bv.budget_version_id,
3455: bv.version_number
3456: into l_curr_fcst_ver_id,
3457: l_curr_fcst_ver_num
3458: from pa_budget_versions bv
3459: where bv.project_id = p_project_id and
3460: bv.primary_rev_forecast_flag = 'Y' and
3461: bv.primary_cost_forecast_flag = 'Y' and
3462: bv.current_flag = 'Y';
3461: bv.primary_cost_forecast_flag = 'Y' and
3462: bv.current_flag = 'Y';
3463: select bv1.budget_version_id
3464: into x_prior_fcst_version_id
3465: from pa_budget_versions bv1
3466: where bv1.project_id = p_project_id
3467: and bv1.primary_rev_forecast_flag = 'Y'
3468: and bv1.primary_cost_forecast_flag = 'Y'
3469: and bv1.budget_status_code = 'B'
3469: and bv1.budget_status_code = 'B'
3470: /*and bv1.version_number = l_curr_fcst_ver_num - 1;
3471: commented and added below for bug 6870324 */
3472: and bv1.version_number = (select max(bv2.version_number)
3473: from pa_budget_versions bv2
3474: where bv2.project_id = p_project_id
3475: and bv2.primary_rev_forecast_flag = 'Y'
3476: and bv2.primary_cost_forecast_flag = 'Y'
3477: and bv2.budget_status_code = 'B'
3485: select bv.budget_version_id,
3486: bv.version_number
3487: into l_curr_fcst_ver_id,
3488: l_curr_fcst_ver_num
3489: from pa_budget_versions bv
3490: where bv.project_id = p_project_id and
3491: bv.primary_cost_forecast_flag = 'Y' and
3492: bv.current_flag = 'Y';
3493:
3492: bv.current_flag = 'Y';
3493:
3494: select bv1.budget_version_id
3495: into x_prior_fcst_version_id
3496: from pa_budget_versions bv1
3497: where bv1.project_id = p_project_id
3498: and bv1.primary_cost_forecast_flag = 'Y'
3499: and bv1.budget_status_code = 'B'
3500: /*and bv1.version_number = l_curr_fcst_ver_num - 1;
3499: and bv1.budget_status_code = 'B'
3500: /*and bv1.version_number = l_curr_fcst_ver_num - 1;
3501: commented and added below for bug 6870324 */
3502: and bv1.version_number = (select max(bv2.version_number)
3503: from pa_budget_versions bv2
3504: where bv2.project_id = p_project_id
3505: and bv2.primary_cost_forecast_flag = 'Y'
3506: and bv2.budget_status_code = 'B'
3507: and bv2.version_number < l_curr_fcst_ver_num
3514: select bv.budget_version_id,
3515: bv.version_number
3516: into l_curr_fcst_ver_id,
3517: l_curr_fcst_ver_num
3518: from pa_budget_versions bv
3519: where bv.project_id = p_project_id and
3520: bv.primary_rev_forecast_flag = 'Y' and
3521: bv.current_flag = 'Y';
3522:
3521: bv.current_flag = 'Y';
3522:
3523: select bv1.budget_version_id
3524: into x_prior_fcst_version_id
3525: from pa_budget_versions bv1
3526: where bv1.project_id = p_project_id
3527: and bv1.primary_rev_forecast_flag = 'Y'
3528: and bv1.budget_status_code = 'B'
3529: /*and bv1.version_number = l_curr_fcst_ver_num - 1;
3528: and bv1.budget_status_code = 'B'
3529: /*and bv1.version_number = l_curr_fcst_ver_num - 1;
3530: commented and added below for bug 6870324 */
3531: and bv1.version_number = (select max(bv2.version_number)
3532: from pa_budget_versions bv2
3533: where bv2.project_id = p_project_id
3534: and bv2.primary_rev_forecast_flag = 'Y'
3535: and bv2.budget_status_code = 'B'
3536: and bv2.version_number < l_curr_fcst_ver_num
3555: into l_curr_fcst_ver_id,
3556: l_curr_fcst_ver_num,
3557: l_fp_type_id,
3558: l_version_type
3559: from pa_budget_versions bv1,
3560: pa_budget_versions bv2
3561: where bv1.project_id = p_project_id and
3562: bv1.budget_version_id = p_plan_version_id and
3563: bv1.project_id = bv2.project_id and
3556: l_curr_fcst_ver_num,
3557: l_fp_type_id,
3558: l_version_type
3559: from pa_budget_versions bv1,
3560: pa_budget_versions bv2
3561: where bv1.project_id = p_project_id and
3562: bv1.budget_version_id = p_plan_version_id and
3563: bv1.project_id = bv2.project_id and
3564: bv1.fin_plan_type_id = bv2.fin_plan_type_id and
3566: bv2.current_flag = 'Y';
3567:
3568: select budget_version_id
3569: into x_prior_fcst_version_id
3570: from pa_budget_versions
3571: where project_id = p_project_id and
3572: fin_plan_type_id = l_fp_type_id and
3573: version_type = l_version_type and
3574: budget_status_code = 'B' and
3574: budget_status_code = 'B' and
3575: /* version_number = l_curr_fcst_ver_num - 1;
3576: commented and added below for bug 6870324 */
3577: version_number = (select max(bv1.version_number)
3578: from pa_budget_versions bv1
3579: where bv1.project_id = p_project_id
3580: and bv1.fin_plan_type_id = l_fp_type_id
3581: and bv1.version_type = l_version_type
3582: and bv1.budget_status_code = 'B'