[Home] [Help]
PACKAGE BODY: APPS.PA_FP_UPGRADE_PKG
Source
1 PACKAGE BODY pa_fp_upgrade_pkg AS
2 /* $Header: PAFPUPGB.pls 120.7.12010000.4 2009/02/09 12:09:03 spasala ship $*/
3
4 l_module_name VARCHAR2(100):= 'pa.plsql.pa_fp_upgrade_pkg';
5 p_pa_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
6 l_migration_code varchar2(1) := null;
7
8 TYPE res_list_tbl IS TABLE OF
9 pa_resource_lists_all_bg.resource_list_id%TYPE INDEX BY BINARY_INTEGER;
10
11 TYPE bud_typ_code_tbl IS TABLE OF
12 pa_budget_versions.budget_type_code%TYPE INDEX BY BINARY_INTEGER;
13
14 TYPE ra_id_tbl_type IS TABLE OF
15 pa_resource_assignments.resource_assignment_id%TYPE INDEX BY BINARY_INTEGER;
16
17 TYPE rtx_ra_id_tbl_type IS TABLE OF
18 pa_resource_asgn_curr.resource_assignment_id%TYPE INDEX BY BINARY_INTEGER;
19
20 l_budget_ver_tbl SYSTEM.PA_NUM_TBL_TYPE;
21 l_res_list_tbl res_list_tbl;
22 l_bud_typ_code_tbl bud_typ_code_tbl;
23 l_ra_id_tbl ra_id_tbl_type;
24 l_rtx_ra_id_tbl rtx_ra_id_tbl_type;
25
26 -- The following cursor is required both in upgrade_budgets api and
27 -- also validate_budgets api so, the cursoe has been declared here.
28
29
30 CURSOR projects_for_upgrade_cur1 (
31 c_from_project_number IN VARCHAR2
32 ,c_to_project_number IN VARCHAR2
33 ,c_project_type IN pa_projects.project_type%TYPE
34 ,c_project_statuses IN VARCHAR2) IS
35 SELECT project_id
36 FROM pa_projects
37 WHERE segment1 BETWEEN c_from_project_number AND c_to_project_number
38 AND NVL(c_project_type,project_type) = project_type
39 AND DECODE(c_project_statuses,'ALL','ACTIVE',project_status_code) <> 'CLOSED'; --Bug 5194368
40
41
42 CURSOR projects_for_upgrade_cur2 (
43 c_from_project_number IN VARCHAR2
44 ,c_to_project_number IN VARCHAR2
45 ,c_project_type IN pa_projects.project_type%TYPE
46 ,c_project_statuses IN VARCHAR2) IS
47 SELECT project_id
48 FROM pa_projects
49 WHERE segment1 BETWEEN NVL(c_from_project_number,segment1) AND NVL(c_to_project_number,segment1)
50 AND c_project_type = project_type
51 AND DECODE(c_project_statuses,'ALL','ACTIVE',project_status_code) <> 'CLOSED'; --Bug 5194368
52
53
54 CURSOR projects_for_upgrade_cur3 (
55 c_from_project_number IN VARCHAR2
56 ,c_to_project_number IN VARCHAR2
57 ,c_project_type IN pa_projects.project_type%TYPE
58 ,c_project_statuses IN VARCHAR2) IS
59 SELECT project_id
60 FROM pa_projects
61 WHERE segment1 BETWEEN NVL(c_from_project_number,segment1) AND NVL(c_to_project_number,segment1)
62 AND NVL(c_project_type,project_type) = project_type
63 AND project_status_code <> 'CLOSED'; --Bug 5194368
64
65
66
67 CURSOR projects_for_upgrade_cur (
68 c_from_project_number IN VARCHAR2
69 ,c_to_project_number IN VARCHAR2
70 ,c_project_type IN pa_projects.project_type%TYPE
71 ,c_project_statuses IN VARCHAR2) IS
72 SELECT project_id
73 FROM pa_projects
74 WHERE segment1 BETWEEN NVL(c_from_project_number,segment1) AND NVL(c_to_project_number,segment1)
75 AND NVL(c_project_type,project_type) = project_type
76 AND DECODE(c_project_statuses,'ALL','ACTIVE',project_status_code) <> 'CLOSED'; --Bug 5194368
77
78 project_info_rec projects_for_upgrade_cur%ROWTYPE;
79
80 CURSOR project_type_info_cur (
81 c_project_id IN pa_projects.project_id%TYPE)IS
82 SELECT allow_cost_budget_entry_flag
83 ,allow_rev_budget_entry_flag
84 ,name
85 ,segment1
86 ,org_project_flag -- bug 2788983
87 FROM pa_project_types ppt
88 ,pa_projects pp
89 WHERE pp.project_id = c_project_id
90 AND ppt.project_type = pp.project_type;
91
92 project_type_info_rec project_type_info_cur%ROWTYPE;
93
94 CURSOR attached_plan_types_cur(
95 c_project_id IN pa_projects.project_id%TYPE
96 ,c_budget_types IN VARCHAR2 ) IS
97 SELECT pt.fin_plan_type_id fin_plan_type_id
98 ,bt.budget_Type_code budget_Type_code
99 FROM pa_fin_plan_types_b pt /* Bug# 2661650 - Replaced _vl by _b for performance reasons */
100 ,pa_budget_types bt
101 WHERE DECODE(c_budget_types,'ALL','Y', bt.upgrade_budget_type_flag) = 'Y'
102 AND bt.budget_type_code = pt.migrated_frm_bdgt_typ_code
103 AND NVL(bt.plan_type,'BUDGET') = 'BUDGET'
104 AND not exists
105 (SELECT 1
106 FROM pa_proj_fp_options ppfo
107 WHERE ppfo.project_id = c_project_id
108 AND ppfo.fin_plan_type_id = pt.fin_plan_type_id
109 AND ppfo.fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE)
110 AND exists
111 (SELECT 1
112 FROM pa_budget_versions pbv
113 WHERE pbv.project_id = c_project_id
114 AND pbv.budget_type_code = bt.budget_type_code);
115
116 attached_plan_types_rec attached_plan_types_cur%ROWTYPE;
117
118 -- The follwing cursor would be used in validate_budget_version and also
119 -- upgrade_budget_versions apis. So, the cursor has been declared here.
120
121 -- bug 3673111, 07-JUN-4, jwhite -------------------------------
122 -- Add resource_list_id as a new column in the select statement.
123
124 CURSOR budgets_for_upgrade_cur (
125 c_project_id IN pa_projects.project_id%TYPE
126 ,c_budget_types IN VARCHAR2
127 ,c_budget_statuses IN VARCHAR2
128 ,c_mode IN VARCHAR2 ) IS
129 SELECT budget_version_id
130 , bt.budget_type_code
131 , bv.resource_list_id /* bug 3673111, 07-JUN-4, jwhite: New Column */
132 , bv.budget_status_code -- Bug# 7187487
133 FROM pa_budget_versions bv,
134 pa_budget_types bt
135 WHERE bv.project_id = c_project_id
136 AND bt.budget_type_code = bv.budget_type_code
137 AND bv.budget_type_code IS NOT NULL
138 AND DECODE(c_budget_types,'ALL','Y',bt.upgrade_budget_type_flag) = 'Y'
139 AND NVL(bt.plan_type,'BUDGET') = 'BUDGET' /* Bug 2758786 */
140 AND EXISTS (
141 SELECT 1 FROM DUAL
142 WHERE c_budget_statuses = 'ALL'
143 UNION ALL
144 SELECT 1 FROM DUAL
145 WHERE (current_original_flag = 'Y' OR
146 original_flag = 'Y' OR
147 current_flag = 'Y' OR
148 budget_status_code IN (PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_WORKING) )
149 AND c_budget_statuses = 'CWB')
150 AND (c_mode = 'PRE_UPGRADE' OR EXISTS (
151 SELECT 1
152 FROM pa_proj_fp_options pfo,
153 pa_fin_plan_types_b pt /* Bug# 2661650 - Replaced _vl by _b for performance reasons */
154 WHERE pfo.project_id = c_project_id
155 AND pfo.fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
156 AND pt.fin_plan_type_id = pfo.fin_plan_type_id
157 AND pt.migrated_frm_bdgt_typ_code = bv.budget_type_code));
158
159 budgets_for_upgrade_rec budgets_for_upgrade_cur%ROWTYPE;
160
161 -- END bug 3673111, 07-JUN-4, jwhite -------------------------------
162
163 /*==============================================================================
164 This method will be called from all the apis in this package whenever the current
165 option changes. The api populates the local variables using the inputs and
166 the business rules for upgrade process.
167 ==============================================================================*/
168 Procedure Populate_Local_Variables(
169 p_project_id IN pa_proj_fp_options.project_id%TYPE
170 ,p_budget_type_code IN pa_budget_versions.budget_type_code%TYPE
171 ,p_fin_plan_version_id IN pa_proj_fp_options.fin_plan_version_id%TYPE
172 ,p_fin_plan_option_level IN pa_proj_fp_options.fin_plan_option_level_code%TYPE
173 ,x_upgrade_elements_rec OUT NOCOPY pa_fp_upgrade_pkg.upgrade_elements_rec_type
174 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
175 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
176 ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
177
178 l_return_status VARCHAR2(2000);
179 l_msg_count NUMBER :=0;
180 l_msg_data VARCHAR2(2000);
181 l_data VARCHAR2(2000);
182 l_msg_index_out NUMBER;
183 l_debug_mode VARCHAR2(30);
184 l_err_code NUMBER;
185 l_err_stage VARCHAR2(2000);
186 l_err_stack VARCHAR2(2000);
187
188 l_cost_amount_set_id pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE;
189 l_revenue_amount_set_id pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE;
190 l_all_amount_set_id pa_fin_plan_amount_sets.fin_plan_amount_set_id%TYPE;
191
192 l_track_as_labor_flag pa_resource_list_members.track_as_labor_flag%TYPE;
193 l_resource_id pa_resource_list_members.resource_id%TYPE;
194
195 CURSOR budget_version_info_cur(
196 c_budget_version_id IN pa_budget_versions.budget_version_id%TYPE) IS
197 SELECT pbv.budget_entry_method_code budget_entry_method_code
198 ,resource_list_id
199 ,entry_level_code
200 ,time_phased_type_code
201 ,cost_quantity_flag
202 ,raw_cost_flag
203 ,burdened_cost_flag
204 ,rev_quantity_flag
205 ,revenue_flag
206 FROM pa_budget_versions pbv,
207 pa_budget_entry_methods pbem
208 WHERE pbv.budget_version_id = c_budget_version_id
209 AND pbem.budget_entry_method_code = pbv.budget_entry_method_code;
210
211 budget_version_info_rec budget_version_info_cur%ROWTYPE;
212
213 CURSOR project_type_level_info_cur (
214 c_project_id IN pa_projects.project_id%TYPE) IS
215 SELECT cost_budget_entry_method_code
216 ,cost_budget_resource_list_id
217 ,rev_budget_entry_method_code
218 ,rev_budget_resource_list_id
219 ,allow_cost_budget_entry_flag
220 ,allow_rev_budget_entry_flag
221 FROM pa_projects a,
222 pa_project_types b
223 WHERE a.project_id = c_project_id
224 AND b.project_type = a.project_type;
225
226 project_type_level_info_rec project_type_level_info_cur%ROWTYPE;
227
228 CURSOR budget_entry_method_info_cur(
229 c_budget_entry_method_code IN pa_budget_entry_methods.budget_entry_method_code%TYPE) IS
230 SELECT entry_level_code
231 ,time_phased_type_code
232 ,cost_quantity_flag
233 ,raw_cost_flag
234 ,burdened_cost_flag
235 ,rev_quantity_flag
236 ,revenue_flag
237 FROM pa_budget_entry_methods
238 WHERE budget_entry_method_code = c_budget_entry_method_code;
239
240 budget_entry_method_info_rec budget_entry_method_info_cur%ROWTYPE;
241
242 BEGIN
243
244 x_msg_count := 0;
245 x_return_status := FND_API.G_RET_STS_SUCCESS;
246
247 pa_debug.set_err_stack('PA_FP_UPGRADE_PKG.Populate_Local_Variables');
248 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
249 l_debug_mode := NVL(l_debug_mode, 'Y');
250 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
251
252 IF p_pa_debug_mode = 'Y' THEN
253 pa_debug.g_err_stage := 'Inside Populate_Local_Variables';
254 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
255 END IF;
256 -- Check for not null parameters
257 IF p_pa_debug_mode = 'Y' THEN
258 pa_debug.g_err_stage := 'Checking for valid parameters:';
259 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
260 END IF;
261
262 IF p_fin_plan_option_level = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT THEN
263
264 IF (p_project_id IS NULL) THEN
265 IF p_pa_debug_mode = 'Y' THEN
266 pa_debug.g_err_stage := 'P_fin_plan_option_level='||p_fin_plan_option_level;
267 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
268 pa_debug.g_err_stage := 'p_project_id='||p_project_id;
269 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
270 END IF;
271 PA_UTILS.ADD_MESSAGE(p_app_short_name=> 'PA',
272 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
273 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
274 END IF;
275
276 ELSIF p_fin_plan_option_level = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE THEN
277
278 IF (p_project_id IS NULL) OR
279 (p_budget_type_code IS NULL) THEN
280 IF p_pa_debug_mode = 'Y' THEN
281 pa_debug.g_err_stage := 'P_fin_plan_option_level='||p_fin_plan_option_level;
282 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
283 pa_debug.g_err_stage := 'p_project_id='||p_project_id;
284 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
285 pa_debug.g_err_stage := 'p_budget_type_code='||p_budget_type_code;
286 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
287 END IF;
288 PA_UTILS.ADD_MESSAGE(p_app_short_name=> 'PA',
289 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
290 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
291 END IF;
292
293 ELSIF p_fin_plan_option_level = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION THEN
294
295 IF (p_project_id IS NULL) OR
296 (p_budget_type_code IS NULL) OR
297 (p_fin_plan_version_id IS NULL) THEN
298 IF p_pa_debug_mode = 'Y' THEN
299 pa_debug.g_err_stage := 'P_fin_plan_option_level='||p_fin_plan_option_level;
300 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
301 pa_debug.g_err_stage := 'p_project_id='||p_project_id;
302 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
303 pa_debug.g_err_stage := 'p_budget_type_code='||p_budget_type_code;
304 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
305 pa_debug.g_err_stage := 'p_fin_plan_version_id='||p_fin_plan_version_id;
306 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
307 END IF;
308 PA_UTILS.ADD_MESSAGE(p_app_short_name=> 'PA',
309 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
310 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
311 END IF;
312 ELSE
313 IF p_pa_debug_mode = 'Y' THEN
314 pa_debug.g_err_stage := 'P_fin_plan_option_level='||p_fin_plan_option_level;
315 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
316 END IF;
317 PA_UTILS.ADD_MESSAGE(p_app_short_name=> 'PA',
318 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
319 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
320 END IF;
321 IF p_pa_debug_mode = 'Y' THEN
322 pa_debug.g_err_stage := 'Parameter validation complete';
323 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
324
325 pa_debug.g_err_stage := 'P_fin_plan_option_level='||p_fin_plan_option_level;
326 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
327 pa_debug.g_err_stage := 'p_project_id='||p_project_id;
328 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
329 pa_debug.g_err_stage := 'p_budget_type_code='||p_budget_type_code;
330 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
331 pa_debug.g_err_stage := 'p_fin_plan_version_id='||p_fin_plan_version_id;
332 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
333 END IF;
334 --Null Out all the global parameters.
335
336 x_upgrade_elements_rec := NULL;
337
338 x_upgrade_elements_rec.curr_option_budget_type_code := p_budget_type_code;
339
340 IF p_fin_plan_option_level = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT THEN
341
342 --Set the fp option related variables
343
344 x_upgrade_elements_rec.curr_option_project_id := p_project_id;
345 x_upgrade_elements_rec.curr_option_plan_type_id := NULL;
346 x_upgrade_elements_rec.curr_option_plan_version_id := NULL;
347 x_upgrade_elements_rec.curr_option_level_code := PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT;
348
349 x_upgrade_elements_rec.curr_option_preference_code := PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SEP;
350
351 --Fetch lastest Approved Cost baselined version id as basis_cost_version_id
352
353 IF p_pa_debug_mode = 'Y' THEN
354 pa_debug.g_err_stage:='Calling pa_budget_utils.get_baselined_version_id for AC';
355 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
356 END IF;
357
358 pa_budget_utils.get_baselined_version_id (
359 x_project_id => p_project_id
360 ,x_budget_type_code => PA_FP_CONSTANTS_PKG.G_BUDGET_TYPE_CODE_AC
361 ,x_budget_version_id => x_upgrade_elements_rec.basis_cost_version_id
362 ,x_err_code => l_err_code
363 ,x_err_stage => l_err_stage
364 ,x_err_stack => l_err_stack);
365
366 /* Bug# 2643043 -- Error code 10 is no_data_found */
367 IF l_err_code NOT IN (10,0) THEN
368 -- the api has returned an error
369 IF p_pa_debug_mode = 'Y' THEN
370 pa_debug.g_err_stage:='Error returned by pa_budget_utils.get_baselined_version_id';
371 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
372 END IF;
373 RAISE pa_fp_constants_pkg.invalid_arg_exc;
374 END IF;
375
376 IF l_err_code = 10 or x_upgrade_elements_rec.basis_cost_version_id IS NULL THEN /* Bug# 2643043 */
377
378 --Fetch Approved Cost working version as basis_cost_version_id
379
380 IF p_pa_debug_mode = 'Y' THEN
381 pa_debug.g_err_stage:='Calling pa_budget_utils.get_draft_version_id as no baselined version for no AC';
382 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
383 END IF;
384
385 pa_budget_utils.get_draft_version_id (
386 x_project_id => p_project_id
387 ,x_budget_type_code => PA_FP_CONSTANTS_PKG.G_BUDGET_TYPE_CODE_AC
388 ,x_budget_version_id => x_upgrade_elements_rec.basis_cost_version_id
389 ,x_err_code => l_err_code
390 ,x_err_stage => l_err_stage
391 ,x_err_stack => l_err_stack);
392
393
394 /* Bug# 2643043 -- Error code 10 is no_data_found */
395 IF l_err_code NOT IN (10,0) THEN
396 -- the api has returned an error
397 IF p_pa_debug_mode = 'Y' THEN
398 pa_debug.g_err_stage:='Error returned by pa_budget_utils.get_draft_version_id';
399 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
400 END IF;
401 RAISE pa_fp_constants_pkg.invalid_arg_exc;
402 END IF;
403
404 /*** Bug# 2643043
405 **** IF l_err_code <> 0 THEN
406 **** x_upgrade_elements_rec.basis_cost_version_id := NULL;
407 **** END IF;
408 **** Bug# 2643043 */
409
410 END IF;
411
412 IF p_pa_debug_mode = 'Y' THEN
413 pa_debug.g_err_stage:='cost_version_id ='||x_upgrade_elements_rec.basis_cost_version_id;
414 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
415 END IF;
416
417 --Fetch lastest Approved Revenue baselined version id as basis_rev_version_id
418
419 IF p_pa_debug_mode = 'Y' THEN
420 pa_debug.g_err_stage:='Calling pa_budget_utils.get_baselined_version_id for AR';
421 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
422 END IF;
423
424 pa_budget_utils.get_baselined_version_id (
425 x_project_id => p_project_id
426 ,x_budget_type_code => PA_FP_CONSTANTS_PKG.G_BUDGET_TYPE_CODE_AR
427 ,x_budget_version_id => x_upgrade_elements_rec.basis_rev_version_id
428 ,x_err_code => l_err_code
429 ,x_err_stage => l_err_stage
430 ,x_err_stack => l_err_stack);
431
432
433 /* Bug# 2643043 -- Error code 10 is no_data_found */
434 IF l_err_code NOT IN (10,0) THEN
435 -- the api has returned an error
436 IF p_pa_debug_mode = 'Y' THEN
437 pa_debug.g_err_stage:='Error returned by pa_budget_utils.get_baselined_version_id';
438 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
439 END IF;
440 RAISE pa_fp_constants_pkg.invalid_arg_exc;
441 END IF;
442
443 IF l_err_code = 10 or x_upgrade_elements_rec.basis_rev_version_id IS NULL THEN /* Bug# 2643043 */
444
445 -- baselined version doesn't exist
446
447 IF p_pa_debug_mode = 'Y' THEN
448 pa_debug.g_err_stage:='Calling pa_budget_utils.get_draft_version_id as no baselined version for no AR';
449 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
450 END IF;
451
452 --Fetch Approved Revenue working version
453
454 pa_budget_utils.get_draft_version_id (
455 x_project_id => p_project_id
456 ,x_budget_type_code => PA_FP_CONSTANTS_PKG.G_BUDGET_TYPE_CODE_AR
457 ,x_budget_version_id => x_upgrade_elements_rec.basis_rev_version_id
458 ,x_err_code => l_err_code
459 ,x_err_stage => l_err_stage
460 ,x_err_stack => l_err_stack);
461
462 /*** Bug# 2643043
463 **** IF l_err_code <> 0 THEN
464 **** x_upgrade_elements_rec.basis_cost_version_id := NULL;
465 **** END IF;
466 **** Bug# 2643043 */
467
468 /* Bug# 2643043 -- Error code 10 is no_data_found */
469 IF l_err_code NOT IN (10,0) THEN
470 -- the api has returned an error
471 IF p_pa_debug_mode = 'Y' THEN
472 pa_debug.g_err_stage:='Error returned by pa_budget_utils.get_draft_version_id';
473 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
474 END IF;
475 RAISE pa_fp_constants_pkg.invalid_arg_exc;
476 END IF;
477
478 END IF;
479
480 IF p_pa_debug_mode = 'Y' THEN
481 pa_debug.g_err_stage:='basis_rev_version_id ='||x_upgrade_elements_rec.basis_rev_version_id;
482 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
483 END IF;
484
485 ELSIF p_fin_plan_option_level = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE THEN
486
487 --Set the fp option related variables
488
489 x_upgrade_elements_rec.curr_option_project_id := p_project_id;
490 x_upgrade_elements_rec.curr_option_plan_version_id := NULL;
491 x_upgrade_elements_rec.curr_option_level_code := PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE;
492
493 BEGIN
494 --Fetch plan type id using budget_type_code
495
496 SELECT fin_plan_type_id
497 INTO x_upgrade_elements_rec.curr_option_plan_type_id
498 FROM pa_fin_plan_types_b /* Bug# 2661650 - Replaced _vl by _b for performance reasons */
499 WHERE migrated_frm_bdgt_typ_code = p_budget_type_code;
500
501 --Fetch preference code using budget amount code
502
503 SELECT DECODE(budget_amount_code,PA_FP_CONSTANTS_PKG.G_BUDGET_AMOUNT_CODE_C,PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY
504 ,PA_FP_CONSTANTS_PKG.G_BUDGET_AMOUNT_CODE_R,PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY)
505 INTO x_upgrade_elements_rec.curr_option_preference_code
506 FROM pa_budget_types
507 WHERE budget_type_code = p_budget_type_code;
508
509 EXCEPTION
510 WHEN OTHERS THEN
511 IF p_pa_debug_mode = 'Y' THEN
512 pa_debug.g_err_stage:='failed while fetching plan type id for plan_type option '||SQLERRM;
513 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
514 END IF;
515 RAISE;
516 END;
517
518 IF x_upgrade_elements_rec.curr_option_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY THEN
519
520 --Fetch lastest baselined version id as basis_cost_version_id
521
522 pa_budget_utils.get_baselined_version_id (
523 x_project_id => p_project_id
524 ,x_budget_type_code => p_budget_type_code
525 ,x_budget_version_id => x_upgrade_elements_rec.basis_cost_version_id
526 ,x_err_code => l_err_code
527 ,x_err_stage => l_err_stage
528 ,x_err_stack => l_err_stack);
529
530
531 /* Bug# 2643043 -- Error code 10 is no_data_found */
532 IF l_err_code not in (10,0) THEN
533 -- the api has returned an error
534 IF p_pa_debug_mode = 'Y' THEN
535 pa_debug.g_err_stage:='Error returned by pa_budget_utils.get_baselined_version_id';
536 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
537 END IF;
538 RAISE PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
539 END IF;
540
541 IF l_err_code = 10 or x_upgrade_elements_rec.basis_cost_version_id IS NULL THEN /* Bug# 2643043 */
542
543 --Fetch working version as basis_cost_version_id
544
545 pa_budget_utils.get_draft_version_id (
546 x_project_id => p_project_id
547 ,x_budget_type_code => p_budget_type_code
548 ,x_budget_version_id => x_upgrade_elements_rec.basis_cost_version_id
549 ,x_err_code => l_err_code
550 ,x_err_stage => l_err_stage
551 ,x_err_stack => l_err_stack);
552
553 /*** Bug# 2643043
554 **** IF l_err_code <> 0 THEN
555 **** x_upgrade_elements_rec.basis_cost_version_id := NULL;
556 **** END IF;
557 **** Bug# 2643043 */
558
559 /* Bug# 2643043 -- Error code 10 is no_data_found */
560 IF l_err_code not in (10,0) THEN
561 -- the api has returned an error
562 IF p_pa_debug_mode = 'Y' THEN
563 pa_debug.g_err_stage:='Error returned by pa_budget_utils.get_draft_version_id';
564 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
565 END IF;
566 RAISE PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
567 END IF;
568
569 END IF;
570 ELSIF x_upgrade_elements_rec.curr_option_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY THEN
571
572 --Fetch lastest baselined version id as basis_rev_version_id
573
574 pa_budget_utils.get_baselined_version_id (
575 x_project_id => p_project_id
576 ,x_budget_type_code => p_budget_type_code
577 ,x_budget_version_id => x_upgrade_elements_rec.basis_rev_version_id
578 ,x_err_code => l_err_code
579 ,x_err_stage => l_err_stage
580 ,x_err_stack => l_err_stack);
581
582
583 /* Bug# 2643043 -- Error code 10 is no_data_found */
584 IF l_err_code not in (10,0) THEN
585 -- the api has returned an error
586 IF p_pa_debug_mode = 'Y' THEN
587 pa_debug.g_err_stage:='Error returned by pa_budget_utils.get_baselined_version_id';
588 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
589 END IF;
590 RAISE PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
591 END IF;
592
593 IF l_err_code = 10 or x_upgrade_elements_rec.basis_cost_version_id IS NULL THEN /* Bug# 2643043 */
594
595 --Fetch working version as basis_rev_version_id
596
597 pa_budget_utils.get_draft_version_id (
598 x_project_id => p_project_id
599 ,x_budget_type_code => p_budget_type_code
600 ,x_budget_version_id => x_upgrade_elements_rec.basis_rev_version_id
601 ,x_err_code => l_err_code
602 ,x_err_stage => l_err_stage
603 ,x_err_stack => l_err_stack);
604
605 /*** Bug# 2643043
606 **** IF l_err_code <> 0 THEN
607 **** x_upgrade_elements_rec.basis_rev_version_id := NULL;
608 **** END IF;
609 **** Bug# 2643043 */
610
611 /* Bug# 2643043 -- Error code 10 is no_data_found */
612 IF l_err_code not in (10,0) THEN
613 -- the api has returned an error
614 IF p_pa_debug_mode = 'Y' THEN
615 pa_debug.g_err_stage:='Error returned by pa_budget_utils.get_draft_version_id';
616 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
617 END IF;
618 RAISE PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
619 END IF;
620
621 END IF;
622 END IF;
623
624 ELSIF p_fin_plan_option_level = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION THEN
625
626 --Set the fp option related variables
627
628 x_upgrade_elements_rec.curr_option_project_id := p_project_id;
629 x_upgrade_elements_rec.curr_option_plan_version_id := p_fin_plan_version_id;
630 x_upgrade_elements_rec.curr_option_level_code := PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION;
631
632 BEGIN
633
634 --Fetch plan type id using budget_type_code
635
636 SELECT fin_plan_type_id
637 INTO x_upgrade_elements_rec.curr_option_plan_type_id
638 FROM pa_fin_plan_types_b /* Bug# 2661650 - Replaced _vl by _b for performance reasons */
639 WHERE migrated_frm_bdgt_typ_code = p_budget_type_code;
640
641 --Fetch preference code using budget amount code
642
643 SELECT DECODE(budget_amount_code,PA_FP_CONSTANTS_PKG.G_BUDGET_AMOUNT_CODE_C,PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY
644 ,PA_FP_CONSTANTS_PKG.G_BUDGET_AMOUNT_CODE_R,PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY)
645 INTO x_upgrade_elements_rec.curr_option_preference_code
646 FROM pa_budget_types
647 WHERE budget_type_code = p_budget_type_code;
648
649 EXCEPTION
650 WHEN OTHERS THEN
651 IF p_pa_debug_mode = 'Y' THEN
652 pa_debug.g_err_stage:='Failed while fetching plan type id for version'||SQLERRM;
653 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
654 END IF;
655 RAISE;
656 END;
657
658 --Using preference code set basis_cost_version_id /g_basis_revenue_version_id appropriately.
659
660 IF x_upgrade_elements_rec.curr_option_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY THEN
661
662 x_upgrade_elements_rec.basis_cost_version_id := p_fin_plan_version_id;
663
664 ELSIF x_upgrade_elements_rec.curr_option_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY THEN
665
666 x_upgrade_elements_rec.basis_rev_version_id := p_fin_plan_version_id;
667
668 END IF;
669
670 END IF;
671
672 --If preference code is cost_only or cost_and_rev_sep then set cost variables.
673
674 IF x_upgrade_elements_rec.curr_option_preference_code IN(PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY,
675 PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SEP ) THEN
676
677 IF x_upgrade_elements_rec.basis_cost_version_id IS NOT NULL THEN
678
679 --Using the basis_cost_version_id set all the cost variables.
680
681 OPEN budget_version_info_cur(x_upgrade_elements_rec.basis_cost_version_id);
682 FETCH budget_version_info_cur INTO budget_version_info_rec;
683 CLOSE budget_version_info_cur;
684
685
686 x_upgrade_elements_rec.basis_cost_bem := budget_version_info_rec.budget_entry_method_code;
687 x_upgrade_elements_rec.basis_cost_res_list_id := budget_version_info_rec.resource_list_id;
688 x_upgrade_elements_rec.basis_cost_planning_level := budget_version_info_rec.entry_level_code;
689 x_upgrade_elements_rec.basis_cost_time_phased_code := budget_version_info_rec.time_phased_type_code;
690
691 --The following api either returns amount set ids based on preference code
692 --and the flag i/p parameters combination if it already exists OR else
693 --creates a new amount set id.
694
695 PA_FIN_PLAN_UTILS.GET_OR_CREATE_AMOUNT_SET_ID(
696 p_raw_cost_flag => budget_version_info_rec.raw_cost_flag
697 ,p_burdened_cost_flag => budget_version_info_rec.burdened_cost_flag
698 ,p_revenue_flag => budget_version_info_rec.revenue_flag
699 ,p_cost_qty_flag => budget_version_info_rec.cost_quantity_flag
700 ,p_revenue_qty_flag => budget_version_info_rec.rev_quantity_flag
701 ,p_all_qty_flag => 'N'
702 ,p_plan_pref_code => x_upgrade_elements_rec.curr_option_preference_code
703 /* Changes for FP.M, Tracking Bug No - 3354518, Adding three new IN parameters p_bill_rate_flag,
704 p_cost_rate_flag, p_burden_multiplier below for new columns in pa_fin_plan_amount_sets
705 defaulting these parameters as 'Y'*/
706 ,p_bill_rate_flag => 'Y'
707 ,p_cost_rate_flag => 'Y'
708 ,p_burden_rate_flag => 'Y'
709 ,x_cost_amount_set_id => x_upgrade_elements_rec.basis_cost_amount_set_id
710 ,x_revenue_amount_set_id => l_revenue_amount_set_id
711 ,x_all_amount_set_id => l_all_amount_set_id
712 ,x_message_count => l_msg_count
713 ,x_return_status => l_return_status
714 ,x_message_data => l_msg_data );
715
716 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
717 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
718 END IF;
719 ELSE
720
721 --populate the variables using project type level cost options
722
723 --Fetch project type level options
724
725 OPEN project_type_level_info_cur(p_project_id);
726 FETCH project_type_level_info_cur INTO project_type_level_info_rec;
727 CLOSE project_type_level_info_cur;
728
729 IF project_type_level_info_rec.allow_cost_budget_entry_flag = 'Y' THEN
730
731 OPEN budget_entry_method_info_cur(project_type_level_info_rec.cost_budget_entry_method_code);
732 FETCH budget_entry_method_info_cur INTO budget_entry_method_info_rec;
733 CLOSE budget_entry_method_info_cur;
734
735 x_upgrade_elements_rec.basis_cost_bem := project_type_level_info_rec.cost_budget_entry_method_code;
736 x_upgrade_elements_rec.basis_cost_res_list_id := project_type_level_info_rec.cost_budget_resource_list_id;
737 x_upgrade_elements_rec.basis_cost_planning_level := budget_entry_method_info_rec.entry_level_code;
738 x_upgrade_elements_rec.basis_cost_time_phased_code := budget_entry_method_info_rec.time_phased_type_code;
739
740 --The following api either returns amount set ids based on preference code
741 --and the flag i/p parameters combination if it already exists OR else
742 --creates a new amount set id.
743
744 PA_FIN_PLAN_UTILS.GET_OR_CREATE_AMOUNT_SET_ID(
745 p_raw_cost_flag => budget_entry_method_info_rec.raw_cost_flag
746 ,p_burdened_cost_flag => budget_entry_method_info_rec.burdened_cost_flag
747 ,p_revenue_flag => budget_entry_method_info_rec.revenue_flag
748 ,p_cost_qty_flag => budget_entry_method_info_rec.cost_quantity_flag
749 ,p_revenue_qty_flag => budget_entry_method_info_rec.rev_quantity_flag
750 ,p_all_qty_flag => 'N'
751 ,p_plan_pref_code => x_upgrade_elements_rec.curr_option_preference_code
752 /* Changes for FP.M, Tracking Bug No - 3354518, Adding three new IN parameters p_bill_rate_flag,
753 p_cost_rate_flag, p_burden_multiplier below for new columns in pa_fin_plan_amount_sets
754 defaulting these parameters as 'Y'*/
755 ,p_bill_rate_flag => 'Y'
756 ,p_cost_rate_flag => 'Y'
757 ,p_burden_rate_flag => 'Y'
758 ,x_cost_amount_set_id => x_upgrade_elements_rec.basis_cost_amount_set_id
759 ,x_revenue_amount_set_id => l_revenue_amount_set_id
760 ,x_all_amount_set_id => l_all_amount_set_id
761 ,x_message_count => l_msg_count
762 ,x_return_status => l_return_status
763 ,x_message_data => l_msg_data );
764
765 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
766 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
767 END IF;
768 END IF;
769 END IF;
770 END IF;
771
772 --If preference code is revenue_only or cost_and_rev_sep then set revenue variables.
773
774 IF x_upgrade_elements_rec.curr_option_preference_code IN(PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY,
775 PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SEP ) THEN
776
777 IF x_upgrade_elements_rec.basis_rev_version_id IS NOT NULL THEN
778
779 --Using the basis_rev_version_id set all the cost variables.
780
781 OPEN budget_version_info_cur(x_upgrade_elements_rec.basis_rev_version_id);
782 FETCH budget_version_info_cur INTO budget_version_info_rec;
783 CLOSE budget_version_info_cur;
784
785 x_upgrade_elements_rec.basis_rev_bem := budget_version_info_rec.budget_entry_method_code;
786 x_upgrade_elements_rec.basis_rev_res_list_id := budget_version_info_rec.resource_list_id;
787 x_upgrade_elements_rec.basis_rev_planning_level := budget_version_info_rec.entry_level_code;
788 x_upgrade_elements_rec.basis_rev_time_phased_code := budget_version_info_rec.time_phased_type_code;
789
790 --The following api either returns amount set ids based on preference code
791 --and the flag i/p parameters combination if it already exists OR else
792 --creates a new amount set id.
793
794 PA_FIN_PLAN_UTILS.GET_OR_CREATE_AMOUNT_SET_ID(
795 p_raw_cost_flag => budget_version_info_rec.raw_cost_flag
796 ,p_burdened_cost_flag => budget_version_info_rec.burdened_cost_flag
797 ,p_revenue_flag => budget_version_info_rec.revenue_flag
798 ,p_cost_qty_flag => budget_version_info_rec.cost_quantity_flag
799 ,p_revenue_qty_flag => budget_version_info_rec.rev_quantity_flag
800 ,p_all_qty_flag => 'N'
801 ,p_plan_pref_code => x_upgrade_elements_rec.curr_option_preference_code
802 /* Changes for FP.M, Tracking Bug No - 3354518, Adding three new IN parameters p_bill_rate_flag,
803 p_cost_rate_flag, p_burden_multiplier below for new columns in pa_fin_plan_amount_sets
804 defaulting these parameters as 'Y'*/
805 ,p_bill_rate_flag => 'Y'
806 ,p_cost_rate_flag => 'Y'
807 ,p_burden_rate_flag => 'Y'
808 ,x_cost_amount_set_id => l_cost_amount_set_id
809 ,x_revenue_amount_set_id => x_upgrade_elements_rec.basis_rev_amount_set_id
810 ,x_all_amount_set_id => l_all_amount_set_id
811 ,x_message_count => l_msg_count
812 ,x_return_status => l_return_status
813 ,x_message_data => l_msg_data );
814 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
815 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
816 END IF;
817 ELSE
818 --Set revenue varaibles using project type revenue properties
819
820 --Fetch project type level options
821
822 OPEN project_type_level_info_cur(p_project_id);
823 FETCH project_type_level_info_cur INTO project_type_level_info_rec;
824 CLOSE project_type_level_info_cur;
825
826 --Using budget entry method code at project type level fetch cost and revenue flags
827
828 IF project_type_level_info_rec.allow_rev_budget_entry_flag = 'Y' THEN
829
830 OPEN budget_entry_method_info_cur(project_type_level_info_rec.rev_budget_entry_method_code);
831 FETCH budget_entry_method_info_cur INTO budget_entry_method_info_rec;
832 CLOSE budget_entry_method_info_cur;
833
834 x_upgrade_elements_rec.basis_rev_bem := project_type_level_info_rec.rev_budget_entry_method_code;
835 x_upgrade_elements_rec.basis_rev_res_list_id := project_type_level_info_rec.rev_budget_resource_list_id;
836 x_upgrade_elements_rec.basis_rev_planning_level := budget_entry_method_info_rec.entry_level_code;
837 x_upgrade_elements_rec.basis_rev_time_phased_code := budget_entry_method_info_rec.time_phased_type_code;
838
839 --The following api either returns amount set ids based on preference code
840 --and the flag i/p parameters combination if it already exists OR else
841 --creates a new amount set id.
842
843 PA_FIN_PLAN_UTILS.GET_OR_CREATE_AMOUNT_SET_ID(
844 p_raw_cost_flag => budget_entry_method_info_rec.raw_cost_flag
845 ,p_burdened_cost_flag => budget_entry_method_info_rec.burdened_cost_flag
846 ,p_revenue_flag => budget_entry_method_info_rec.revenue_flag
847 ,p_cost_qty_flag => budget_entry_method_info_rec.cost_quantity_flag
848 ,p_revenue_qty_flag => budget_entry_method_info_rec.rev_quantity_flag
849 ,p_all_qty_flag => 'N'
850 ,p_plan_pref_code => x_upgrade_elements_rec.curr_option_preference_code
851 /* Changes for FP.M, Tracking Bug No - 3354518, Adding three new IN parameters p_bill_rate_flag,
852 p_cost_rate_flag, p_burden_multiplier below for new columns in pa_fin_plan_amount_sets
853 defaulting these parameters as 'Y'*/
854 ,p_bill_rate_flag => 'Y'
855 ,p_cost_rate_flag => 'Y'
856 ,p_burden_rate_flag => 'Y'
857 ,x_cost_amount_set_id => l_cost_amount_set_id
858 ,x_revenue_amount_set_id => x_upgrade_elements_rec.basis_rev_amount_set_id
859 ,x_all_amount_set_id => l_all_amount_set_id
860 ,x_message_count => l_msg_count
861 ,x_return_status => l_return_status
862 ,x_message_data => l_msg_data );
863
864 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
865 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
866 END IF;
867
868 END IF;
869 END IF; --g_basis_rev_version_id
870
871 END IF; --g_curr_option_preference_code is revenue_only or cost_and_rev_sep
872 IF p_pa_debug_mode = 'Y' THEN
873 pa_debug.g_err_stage := 'Exiting Populate_Local_Variables';
874 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
875 END IF;
876 pa_debug.reset_err_stack;
877 EXCEPTION
878
879 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
880
881 l_msg_count := FND_MSG_PUB.count_msg;
882 IF l_msg_count = 1 THEN
883 PA_INTERFACE_UTILS_PUB.get_messages
884 (p_encoded => FND_API.G_TRUE
885 ,p_msg_index => 1
886 ,p_msg_count => l_msg_count
887 ,p_msg_data => l_msg_data
888 ,p_data => l_data
889 ,p_msg_index_out => l_msg_index_out);
890 x_msg_data := l_data;
891 x_msg_count := l_msg_count;
892 ELSE
893 x_msg_count := l_msg_count;
894 x_msg_data := l_msg_data;
895 END IF;
896 IF p_pa_debug_mode = 'Y' THEN
897 pa_debug.g_err_stage:='Invalid Arguments Passed';
898 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
899 pa_debug.write_file('Populate_Local_Variables ' || x_msg_data,5);
900 END IF;
901 x_return_status:= FND_API.G_RET_STS_ERROR;
902 pa_debug.reset_err_stack;
903 RAISE;
904
905 WHEN Others THEN
906
907 IF budget_version_info_cur%ISOPEN THEN
908 CLOSE budget_version_info_cur;
909 END IF;
910 IF project_type_level_info_cur%ISOPEN THEN
911 CLOSE project_type_level_info_cur;
912 END IF;
913 IF budget_entry_method_info_cur%ISOPEN THEN
914 CLOSE budget_entry_method_info_cur;
915 END IF;
916 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
917 x_msg_count := 1;
918 x_msg_data := SQLERRM;
919
920 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_FP_UPGRADE_PKG'
921 ,p_procedure_name => 'Populate_Local_Variables');
922 IF p_pa_debug_mode = 'Y' THEN
923 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
924 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
925 pa_debug.write_file('Populate_Local_Variables ' || pa_debug.G_Err_Stack,5);
926 END IF;
927 pa_debug.reset_err_stack;
928 RAISE;
929
930 END Populate_Local_Variables;
931
932 /*=============================================================================
933 This is the main api which will do all that is necessary to upgrade all budget
934 versions which are eligible for upgrade as per the input parameters to the
935 concurrent request.
936 =============================================================================*/
937 PROCEDURE Upgrade_Budgets(
938 p_from_project_number IN VARCHAR2
939 ,p_to_project_number IN VARCHAR2
940 ,p_budget_types IN VARCHAR2
941 ,p_budget_statuses IN VARCHAR2
942 ,p_project_type IN VARCHAR2
943 ,p_project_statuses IN VARCHAR2
944 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
945 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
946 ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
947
948 l_return_status VARCHAR2(2000);
949 l_msg_count NUMBER :=0;
950 l_msg_data VARCHAR2(2000);
951 l_data VARCHAR2(2000);
952 l_msg_index_out NUMBER;
953 l_debug_mode VARCHAR2(30);
954
955 l_project_id pa_projects.project_id%TYPE;
956 l_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
957 l_upgrade_elements_rec upgrade_elements_rec_type;
958
959 l_proj_validation_status VARCHAR2(30);
960 l_retcode number;
961 l_errbuf varchar2(512);
962
963 /* Bug #2727377 */
964 l_fp_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
965 l_multi_curr_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
966
967 BEGIN
968 x_msg_count := 0;
969 x_return_status := FND_API.G_RET_STS_SUCCESS;
970 savepoint pa_fp_upgrade_pkg;
971
972 pa_debug.init_err_stack('PA_FP_UPGRADE_PKG.Upgrade_Budget_Types');
973 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
974 l_debug_mode := NVL(l_debug_mode, 'Y');
975 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
976 IF p_pa_debug_mode = 'Y' THEN
977 pa_debug.g_err_stage := 'Entered Upgrade_Budget_Types';
978 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
979
980 pa_debug.g_err_stage := 'Checking for valid parameters';
981 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
982 END IF;
983
984 IF (p_budget_types IS NULL) OR
985 (p_budget_statuses IS NULL) OR
986 (p_project_statuses IS NULL)
987 THEN
988 IF p_pa_debug_mode = 'Y' THEN
989 pa_debug.g_err_stage := 'p_budget_types='||p_budget_types;
990 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
991 pa_debug.g_err_stage := 'p_budget_statuses='||p_budget_statuses;
992 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
993 pa_debug.g_err_stage := 'p_project_statuses='||p_project_statuses;
994 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
995 END IF;
996 PA_UTILS.ADD_MESSAGE(p_app_short_name=> 'PA',
997 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
998 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
999
1000 END IF;
1001 IF p_pa_debug_mode = 'Y' THEN
1002 pa_debug.g_err_stage := 'Parameter validation complete';
1003 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1004
1005 --Upgrade all the budget types selected for upgrade
1006
1007 pa_debug.g_err_stage := 'Calling Upgrade_Budget_Types';
1008 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1009 END IF;
1010 pa_fp_upgrade_pkg.Upgrade_Budget_Types(
1011 p_budget_types => p_budget_types
1012 ,x_return_status => l_return_status
1013 ,x_msg_count => l_msg_count
1014 ,x_msg_data => l_msg_data);
1015 if (l_return_status <> 'S') then
1016 raise pa_fp_constants_pkg.Invalid_Arg_Exc;
1017 end if;
1018 --Fetch the projects chosen for upgrade
1019 IF p_pa_debug_mode = 'Y' THEN
1020 pa_debug.g_err_stage := 'opening projects_for_upgrade_cur';
1021 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1022 pa_debug.g_err_stage := 'p_from_project_number = '||p_from_project_number;
1023 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1024 pa_debug.g_err_stage := 'p_to_project_number = '|| p_to_project_number;
1025 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1026 END IF;
1027
1028 IF ( p_from_project_number IS NOT NULL) AND ( p_to_project_number IS NOT NULL ) THEN
1029
1030 OPEN projects_for_upgrade_cur1(p_from_project_number,p_to_project_number,p_project_type,p_project_statuses);
1031
1032 ELSIF ( p_project_type IS NOT NULL) THEN
1033
1034 OPEN projects_for_upgrade_cur2(p_from_project_number,p_to_project_number,p_project_type,p_project_statuses);
1035
1036 ELSIF (p_project_statuses <> 'ALL') THEN
1037
1038 OPEN projects_for_upgrade_cur3(p_from_project_number,p_to_project_number,p_project_type,p_project_statuses);
1039 ELSE
1040
1041 OPEN projects_for_upgrade_cur(p_from_project_number,p_to_project_number,p_project_type,p_project_statuses);
1042
1043 END IF;
1044
1045 LOOP
1046
1047 IF ( p_from_project_number IS NOT NULL) AND ( p_to_project_number IS NOT NULL ) THEN
1048
1049 FETCH projects_for_upgrade_cur1 INTO l_project_id;
1050 EXIT WHEN projects_for_upgrade_cur1%NOTFOUND;
1051
1052 ELSIF ( p_project_type IS NOT NULL) THEN
1053
1054 FETCH projects_for_upgrade_cur2 INTO l_project_id;
1055 EXIT WHEN projects_for_upgrade_cur2%NOTFOUND;
1056
1057 ELSIF (p_project_statuses <> 'ALL') THEN
1058
1059 FETCH projects_for_upgrade_cur3 INTO l_project_id;
1060 EXIT WHEN projects_for_upgrade_cur3%NOTFOUND;
1061
1062 ELSE
1063 FETCH projects_for_upgrade_cur INTO l_project_id;
1064 EXIT WHEN projects_for_upgrade_cur%NOTFOUND;
1065
1066 END IF;
1067
1068 IF p_pa_debug_mode = 'Y' THEN
1069 pa_debug.g_err_stage := 'Project_id ='||l_project_id;
1070 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1071
1072 --Check if any types of budgets are allowed for the project using project_type_info_cur.
1073
1074 pa_debug.g_err_stage := 'Opening project_type_info_cur'||l_project_id;
1075 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1076 END IF;
1077 OPEN project_type_info_cur(l_project_id);
1078 FETCH project_type_info_cur INTO project_type_info_rec;
1079 CLOSE project_type_info_cur;
1080 IF p_pa_debug_mode = 'Y' THEN
1081 pa_debug.g_err_stage := 'Closed project_type_info_cur';
1082 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1083 END IF;
1084 IF (( project_type_info_rec.allow_cost_budget_entry_flag ='Y' )OR
1085 ( project_type_info_rec.allow_rev_budget_entry_flag = 'Y' )) AND
1086 (NVL(project_type_info_rec.org_project_flag,'N') = 'N') -- bug:- 2788983, org_forecast project shouldn't be upgraded
1087 THEN
1088 --Check if project level fp option for the current project_id is
1089 --already available. Ifn't available create fp option.
1090
1091 BEGIN
1092 SELECT proj_fp_options_id
1093 INTO l_proj_fp_options_id
1094 FROM pa_proj_fp_options
1095 WHERE project_id = l_project_id
1096 AND fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT;
1097
1098 -- The follwing variable indicates if there are any project level exceptions
1099 -- As the project is available in new model, there would be project level no
1100 -- exceptions and so setting it to 'Y'
1101 l_proj_validation_status := 'Y';
1102
1103 EXCEPTION
1104 WHEN no_data_found THEN
1105
1106 -- Check for project level exceptions and
1107 -- set l_proj_validation_status accordingly
1108 pa_fp_upgrade_pkg.Validate_Project (
1109 p_project_id => l_project_id
1110 ,x_validation_status => l_proj_validation_status
1111 ,x_return_status => l_return_status
1112 ,x_msg_count => l_msg_count
1113 ,x_msg_data => l_msg_data);
1114
1115 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1116 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1117 END IF;
1118 -- create fp options and elements for project only if there are
1119 -- no project level exceptions
1120
1121 IF l_proj_validation_status = 'Y' THEN
1122
1123 l_proj_fp_options_id := NULL;
1124 IF p_pa_debug_mode = 'Y' THEN
1125 pa_debug.g_err_stage := 'Calling Create_fp_options for project';
1126 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1127 END IF;
1128 pa_fp_upgrade_pkg.Create_fp_options(
1129 p_project_id => l_project_id
1130 ,p_budget_type_code => NULL
1131 ,p_fin_plan_version_id => NULL
1132 ,p_fin_plan_option_level => PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT
1133 ,x_proj_fp_options_id => l_proj_fp_options_id
1134 ,x_upgrade_elements_rec => l_upgrade_elements_rec
1135 ,x_return_status => l_return_status
1136 ,x_msg_count => l_msg_count
1137 ,x_msg_data => l_msg_data);
1138 if (l_return_status <> 'S') then
1139 raise pa_fp_constants_pkg.Invalid_Arg_Exc;
1140 end if;
1141
1142 /* 2727377: Added call to copy_fp_txn_currencies API to populate the currencies
1143 in pa_fp_txn_currencies. The source fp option ID is being passed as NULL as
1144 the source is determined in copy_fp_txn_currencies API in case the source is
1145 not passed. Plan in multi currency flag and the fp_preference_code is
1146 retrieved from pa_proj_fp_options_table. */
1147
1148 SELECT fin_plan_preference_code, plan_in_multi_curr_flag
1149 INTO l_fp_preference_code, l_multi_curr_flag
1150 FROM pa_proj_fp_options
1151 WHERE proj_fp_options_id = l_proj_fp_options_id;
1152
1153 IF p_pa_debug_mode = 'Y' THEN
1154 pa_debug.g_err_stage := 'Calling copy_fp_txn_currencies for project';
1155 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1156 END IF;
1157 pa_fp_txn_currencies_pub.Copy_Fp_Txn_Currencies (
1158 p_source_fp_option_id => NULL
1159 ,p_target_fp_option_id => l_proj_fp_options_id
1160 ,p_target_fp_preference_code => l_fp_preference_code
1161 ,p_plan_in_multi_curr_flag => l_multi_curr_flag
1162 ,x_return_status => l_return_status
1163 ,x_msg_count => l_msg_count
1164 ,x_msg_data => l_msg_data);
1165 if (l_return_status <> 'S') then
1166 raise pa_fp_constants_pkg.Invalid_Arg_Exc;
1167 end if;
1168
1169 -- Insert into audit table
1170 pa_fp_upgrade_pkg.Insert_Audit_Record(
1171 p_project_id => l_project_id
1172 ,p_budget_type_code => NULL
1173 ,p_proj_fp_options_id => l_proj_fp_options_id
1174 ,p_fin_plan_option_level_code => PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT
1175 ,p_basis_cost_version_id => l_upgrade_elements_rec.basis_cost_version_id
1176 ,p_basis_rev_version_id => l_upgrade_elements_rec.basis_rev_version_id
1177 ,p_basis_cost_bem => l_upgrade_elements_rec.basis_cost_bem
1178 ,p_basis_rev_bem => l_upgrade_elements_rec.basis_rev_bem
1179 ,p_upgraded_flag => 'Y'
1180 ,p_failure_reason_code => NULL);
1181 END IF;
1182 WHEN OTHERS THEN
1183 IF p_pa_debug_mode = 'Y' THEN
1184 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
1185 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1186 END IF;
1187 RAISE;
1188 END;
1189
1190 -- Proceed only if there are no project level exceptions
1191
1192 IF l_proj_validation_status = 'Y' THEN
1193
1194 -- Add the plan types to the project which have been used for this project
1195 -- and those selected for upgrade.This api validates (?) each of the budget type
1196 -- for upgrade. If there are no exceptions it creates both fp options and
1197 -- fp elements for all the above plan type .
1198
1199 IF p_pa_debug_mode ='Y' THEN
1200 pa_debug.g_err_stage := 'Calling Add_Plan_Types';
1201 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1202 END IF;
1203
1204 pa_fp_upgrade_pkg.Add_Plan_Types(
1205 p_project_id => l_project_id
1206 ,p_budget_types => p_budget_types
1207 ,x_return_status => l_return_status
1208 ,x_msg_count => l_msg_count
1209 ,x_msg_data => l_msg_data);
1210 if (l_return_status <> 'S') then
1211 raise pa_fp_constants_pkg.Invalid_Arg_Exc;
1212 end if;
1213
1214 --Upgrade the budget versions of the project for all the budget_types eligible for upgrade
1215 IF p_pa_debug_mode = 'Y' THEN
1216 pa_debug.g_err_stage := 'Calling Upgrade_Budget_Versions';
1217 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1218 END IF;
1219 pa_fp_upgrade_pkg.Upgrade_Budget_Versions(
1220 p_project_id => l_project_id
1221 ,p_budget_types => p_budget_types
1222 ,p_budget_statuses => p_budget_statuses
1223 ,x_return_status => l_return_status
1224 ,x_msg_count => l_msg_count
1225 ,x_msg_data => l_msg_data);
1226 if (l_return_status <> 'S') then
1227 raise pa_fp_constants_pkg.Invalid_Arg_Exc;
1228 end if;
1229 END IF; -- l_proj_validation_status
1230 END IF; -- if any types of budget are allowed for the project
1231
1232 COMMIT; -- this commits data for each project processed
1233
1234 END LOOP;
1235
1236 IF projects_for_upgrade_cur1%ISOPEN THEN
1237 CLOSE projects_for_upgrade_cur1;
1238 ELSIF projects_for_upgrade_cur2%ISOPEN THEN
1239 CLOSE projects_for_upgrade_cur2;
1240 ELSIF projects_for_upgrade_cur3%ISOPEN THEN
1241 CLOSE projects_for_upgrade_cur3;
1242 ELSE
1243 CLOSE projects_for_upgrade_cur;
1244 END IF;
1245
1246 IF p_pa_debug_mode = 'Y' THEN
1247 pa_debug.g_err_stage := 'Closed projects_for_upgrade_cur';
1248 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1249 pa_debug.g_err_stage := 'Exiting Upgrade_Budgets';
1250 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1251 END IF;
1252 pa_debug.reset_err_stack;
1253 EXCEPTION
1254
1255 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1256
1257 IF projects_for_upgrade_cur1%ISOPEN THEN
1258 CLOSE projects_for_upgrade_cur1;
1259 ELSIF projects_for_upgrade_cur2%ISOPEN THEN
1260 CLOSE projects_for_upgrade_cur2;
1261 ELSIF projects_for_upgrade_cur3%ISOPEN THEN
1262 CLOSE projects_for_upgrade_cur3;
1263 ELSIF projects_for_upgrade_cur%ISOPEN THEN
1264 CLOSE projects_for_upgrade_cur;
1265 END IF;
1266
1267 IF project_type_info_cur%ISOPEN THEN
1268 CLOSE project_type_info_cur;
1269 END IF;
1270 l_msg_count := FND_MSG_PUB.count_msg;
1271 IF l_msg_count = 1 THEN
1272 PA_INTERFACE_UTILS_PUB.get_messages
1273 (p_encoded => FND_API.G_TRUE
1274 ,p_msg_index => 1
1275 ,p_msg_count => l_msg_count
1276 ,p_msg_data => l_msg_data
1277 ,p_data => l_data
1278 ,p_msg_index_out => l_msg_index_out);
1279 x_msg_data := l_data;
1280 x_msg_count := l_msg_count;
1281 ELSE
1282 x_msg_count := l_msg_count;
1283 x_msg_data := l_msg_data;
1284 END IF;
1285
1286 IF p_pa_debug_mode = 'Y' THEN
1287 pa_debug.g_err_stage:='Invalid Arguments Passed';
1288 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1289 END IF;
1290 x_return_status:= FND_API.G_RET_STS_ERROR;
1291 pa_debug.write_file('Upgrade_Budgets : Upgrade has failed for the project: '||project_type_info_rec.segment1||'(project number)',5);
1292 pa_debug.write_file('Upgrade_Budgets : Failure Reason:'||x_msg_data,5);
1293 pa_debug.reset_err_stack;
1294 ROLLBACK TO pa_fp_upgrade_pkg;
1295 RAISE;
1296 WHEN Others THEN
1297
1298 IF projects_for_upgrade_cur1%ISOPEN THEN
1299 CLOSE projects_for_upgrade_cur1;
1300 ELSIF projects_for_upgrade_cur2%ISOPEN THEN
1301 CLOSE projects_for_upgrade_cur2;
1302 ELSIF projects_for_upgrade_cur3%ISOPEN THEN
1303 CLOSE projects_for_upgrade_cur3;
1304 ELSIF projects_for_upgrade_cur%ISOPEN THEN
1305 CLOSE projects_for_upgrade_cur;
1306 END IF;
1307 IF project_type_info_cur%ISOPEN THEN
1308 CLOSE project_type_info_cur;
1309 END IF;
1310 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1311 x_msg_count := 1;
1312 x_msg_data := SQLERRM;
1313
1314 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_FP_UPGRADE_PKG'
1315 ,p_procedure_name => 'Upgrade_Budgets');
1316 IF p_pa_debug_mode = 'Y' THEN
1317 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
1318 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1319 END IF;
1320 pa_debug.write_file('Upgrade_Budgets : Upgrade has failed for the project'||project_type_info_rec.segment1||'(project number)',5);
1321 pa_debug.write_file('Upgrade_Budgets : Failure Reason:'||pa_debug.G_Err_Stack,5);
1322 pa_debug.reset_err_stack;
1323 ROLLBACK TO pa_fp_upgrade_pkg;
1324 RAISE;
1325 END Upgrade_Budgets;
1326
1327 /*=============================================================================
1328 This api will create plan types at implementation level for each budget type
1329 selected for upgrade. IF plan type for a budget type already exists then this
1330 api will skip such budget types. Users can submit the upgrade process either
1331 for all budget types or only those which are selected on budget type from
1332 =============================================================================*/
1333 PROCEDURE Upgrade_Budget_Types(
1334 p_budget_types IN VARCHAR2
1335 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1336 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1337 ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1338
1339 l_return_status VARCHAR2(2000);
1340 l_msg_count NUMBER :=0;
1341 l_msg_data VARCHAR2(2000);
1342 l_data VARCHAR2(2000);
1343 l_msg_index_out NUMBER;
1344 l_debug_mode VARCHAR2(30);
1345 /* Bug# 2661650 - Replaced _vl by _b for performance reasons */
1346 l_plan_class_code pa_fin_plan_types_b.plan_class_code%TYPE;
1347 l_approved_cost_plan_type_flag pa_fin_plan_types_b.approved_cost_plan_type_flag%TYPE;
1348 l_approved_rev_plan_type_flag pa_fin_plan_types_b.approved_rev_plan_type_flag%TYPE;
1349
1350 l_rowid ROWID := NULL;
1351
1352 CURSOR budget_types_for_upgrade_cur (
1353 c_budget_types IN VARCHAR2) IS
1354 SELECT budget_type_code
1355 ,budget_type
1356 ,description
1357 ,enable_wf_flag
1358 ,start_date_active
1359 ,end_date_active
1360 ,predefined_flag
1361 ,attribute_category
1362 ,attribute1
1363 ,attribute2
1364 ,attribute3
1365 ,attribute4
1366 ,attribute5
1367 ,attribute6
1368 ,attribute7
1369 ,attribute8
1370 ,attribute9
1371 ,attribute10
1372 ,attribute11
1373 ,attribute12
1374 ,attribute13
1375 ,attribute14
1376 ,attribute15
1377 FROM pa_budget_types bt
1378 WHERE DECODE(c_budget_types, 'ALL' ,'Y', upgrade_budget_type_flag) = 'Y'
1379 AND not exists
1380 (SELECT 1
1381 FROM pa_fin_plan_types_b pt /* Bug# 2661650 - Replaced _vl by _b for performance reasons */
1382 WHERE pt.migrated_frm_bdgt_typ_code = bt.budget_type_code);
1383
1384 budget_types_for_upgrade_rec budget_types_for_upgrade_cur%ROWTYPE;
1385
1386 BEGIN
1387 x_msg_count := 0;
1388 x_return_status := FND_API.G_RET_STS_SUCCESS;
1389
1390 pa_debug.set_err_stack('PA_FP_UPGRADE_PKG.Upgrade_Budget_Types');
1391 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1392 l_debug_mode := NVL(l_debug_mode, 'Y');
1393 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
1394 IF p_pa_debug_mode = 'Y' THEN
1395 pa_debug.g_err_stage := 'Entered Upgrade_Budget_Types';
1396 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1397
1398 pa_debug.g_err_stage := 'Checking for valid parameters';
1399 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1400 END IF;
1401 IF (p_budget_types IS NULL) THEN
1402 IF p_pa_debug_mode = 'Y' THEN
1403 pa_debug.g_err_stage := 'p_budget_types ='||p_budget_types;
1404 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1405 END IF;
1406 PA_UTILS.ADD_MESSAGE(p_app_short_name=> 'PA',
1407 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
1408 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1409
1410 END IF;
1411 IF p_pa_debug_mode = 'Y' THEN
1412 pa_debug.g_err_stage := 'parameter validation complete';
1413 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1414 END IF;
1415
1416 OPEN budget_types_for_upgrade_cur(p_budget_types);
1417 LOOP
1418 FETCH budget_types_for_upgrade_cur INTO budget_types_for_upgrade_rec;
1419 EXIT WHEN budget_types_for_upgrade_cur%NOTFOUND;
1420
1421 --Set l_plan_class_code, l_approved_cost_plan_type_flag, l_approved_rev_plan_type_flag
1422 --using budget_type_code
1423
1424 IF budget_types_for_upgrade_rec.budget_type_code IN (PA_FP_CONSTANTS_PKG.G_BUDGET_TYPE_CODE_FC,
1425 PA_FP_CONSTANTS_PKG.G_BUDGET_TYPE_CODE_FR)
1426 THEN
1427 l_plan_class_code := PA_FP_CONSTANTS_PKG.G_PLAN_CLASS_FORECAST;
1428 ELSE
1429 l_plan_class_code := PA_FP_CONSTANTS_PKG.G_PLAN_CLASS_BUDGET;
1430 END IF;
1431
1432 IF budget_types_for_upgrade_rec.budget_type_code = PA_FP_CONSTANTS_PKG.G_BUDGET_TYPE_CODE_AC THEN
1433 l_approved_cost_plan_type_flag := 'Y';
1434 ELSE
1435 l_approved_cost_plan_type_flag := 'N';
1436 END IF;
1437
1438 IF budget_types_for_upgrade_rec.budget_type_code = PA_FP_CONSTANTS_PKG.G_BUDGET_TYPE_CODE_AR THEN
1439 l_approved_rev_plan_type_flag := 'Y';
1440 ELSE
1441 l_approved_rev_plan_type_flag := 'N';
1442 END IF;
1443
1444 PA_FIN_PLAN_TYPES_PKG.Insert_Row (
1445 x_rowid => l_rowid
1446 ,x_fin_plan_type_id => NULL
1447 ,x_fin_plan_type_code => NULL
1448 ,x_pre_defined_flag => budget_types_for_upgrade_rec.predefined_flag
1449 ,x_generated_flag => 'N'
1450 ,x_edit_generated_amt_flag => 'N'
1451 ,x_used_in_billing_flag => 'N'
1452 ,x_enable_wf_flag => NVL(budget_types_for_upgrade_rec.enable_wf_flag,'N')
1453 ,x_start_date_active => budget_types_for_upgrade_rec.start_date_active
1454 ,x_end_date_active => budget_types_for_upgrade_rec.end_date_active
1455 ,x_record_version_number => 1
1456 ,x_name => budget_types_for_upgrade_rec.budget_type
1457 ,x_description => budget_types_for_upgrade_rec.description
1458 ,x_plan_class_code => l_plan_class_code
1459 ,x_approved_cost_plan_type_flag => l_approved_cost_plan_type_flag
1460 ,x_approved_rev_plan_type_flag => l_approved_rev_plan_type_flag
1461 ,x_projfunc_cost_rate_type => NULL
1462 ,x_projfunc_cost_rate_date_type => NULL
1463 ,x_projfunc_cost_rate_date => NULL
1464 ,x_projfunc_rev_rate_type => NULL
1465 ,x_projfunc_rev_rate_date_type => NULL
1466 ,x_projfunc_rev_rate_date => NULL
1467 ,x_project_cost_rate_type => NULL
1468 ,x_project_cost_rate_date_type => NULL
1469 ,x_project_cost_rate_date => NULL
1470 ,x_project_rev_rate_type => NULL
1471 ,x_project_rev_rate_date_type => NULL
1472 ,x_project_rev_rate_date => NULL
1473 ,x_attribute_category => budget_types_for_upgrade_rec.attribute_category
1474 ,x_attribute1 => budget_types_for_upgrade_rec.attribute1
1475 ,x_attribute2 => budget_types_for_upgrade_rec.attribute2
1476 ,x_attribute3 => budget_types_for_upgrade_rec.attribute3
1477 ,x_attribute4 => budget_types_for_upgrade_rec.attribute4
1478 ,x_attribute5 => budget_types_for_upgrade_rec.attribute5
1479 ,x_attribute6 => budget_types_for_upgrade_rec.attribute6
1480 ,x_attribute7 => budget_types_for_upgrade_rec.attribute7
1481 ,x_attribute8 => budget_types_for_upgrade_rec.attribute8
1482 ,x_attribute9 => budget_types_for_upgrade_rec.attribute9
1483 ,x_attribute10 => budget_types_for_upgrade_rec.attribute10
1484 ,x_attribute11 => budget_types_for_upgrade_rec.attribute11
1485 ,x_attribute12 => budget_types_for_upgrade_rec.attribute12
1486 ,x_attribute13 => budget_types_for_upgrade_rec.attribute13
1487 ,x_attribute14 => budget_types_for_upgrade_rec.attribute14
1488 ,x_attribute15 => budget_types_for_upgrade_rec.attribute15
1489 ,x_creation_date => sysdate
1490 ,x_created_by => fnd_global.user_id
1491 ,x_last_update_date => sysdate
1492 ,x_last_updated_by => fnd_global.user_id
1493 ,x_last_update_login => fnd_global.login_id
1494 ,x_migrated_frm_bdgt_typ_code => budget_types_for_upgrade_rec.budget_type_code
1495 ,X_ENABLE_PARTIAL_IMPL_FLAG => 'N'
1496 ,X_PRIMARY_COST_FORECAST_FLAG => 'N'
1497 ,X_PRIMARY_REV_FORECAST_FLAG => 'N'
1498 ,X_EDIT_AFTER_BASELINE_FLAG => 'Y'
1499 ,X_USE_FOR_WORKPLAN_FLAG => 'N');
1500
1501 END LOOP;
1502 CLOSE budget_types_for_upgrade_cur;
1503
1504 IF p_pa_debug_mode = 'Y' THEN
1505 pa_debug.g_err_stage := 'Exiting Upgrade_Budget_Types';
1506 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1507 END IF;
1508 pa_debug.reset_err_stack;
1509 EXCEPTION
1510
1511 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1512
1513 IF budget_types_for_upgrade_cur%ISOPEN THEN
1514 CLOSE budget_types_for_upgrade_cur;
1515 END IF;
1516 l_msg_count := FND_MSG_PUB.count_msg;
1517 IF l_msg_count = 1 THEN
1518 PA_INTERFACE_UTILS_PUB.get_messages
1519 (p_encoded => FND_API.G_TRUE
1520 ,p_msg_index => 1
1521 ,p_msg_count => l_msg_count
1522 ,p_msg_data => l_msg_data
1523 ,p_data => l_data
1524 ,p_msg_index_out => l_msg_index_out);
1525 x_msg_data := l_data;
1526 x_msg_count := l_msg_count;
1527 ELSE
1528 x_msg_count := l_msg_count;
1529 x_msg_data := l_msg_data;
1530 END IF;
1531
1532 IF p_pa_debug_mode = 'Y' THEN
1533 pa_debug.g_err_stage:='Invalid Arguments Passed';
1534 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1535 pa_debug.write_file('Upgrade_Budget_Types ' || x_msg_data,5);
1536 END IF;
1537 x_return_status:= FND_API.G_RET_STS_ERROR;
1538 pa_debug.reset_err_stack;
1539 RAISE;
1540
1541 WHEN Others THEN
1542
1543 IF budget_types_for_upgrade_cur%ISOPEN THEN
1544 CLOSE budget_types_for_upgrade_cur;
1545 END IF;
1546 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1547 x_msg_count := 1;
1548 x_msg_data := SQLERRM;
1549
1550 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_FP_UPGRADE_PKG'
1551 ,p_procedure_name => 'Upgrade_Budget_Types');
1552 IF p_pa_debug_mode = 'Y' THEN
1553 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
1554 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1555 pa_debug.write_file('Upgrade_Budget_Types ' || pa_debug.G_Err_Stack,5);
1556 END IF;
1557 pa_debug.reset_err_stack;
1558 RAISE;
1559
1560 END Upgrade_Budget_Types;
1561
1562 /*==============================================================================
1563 This process will create record in pa_proj_fp_options table for the project,
1564 plan type and plan version levels. It will create fp option only at the level
1565 for which this api is called.
1566 Bug#2731534: The api has been modified to default MC conversion attributes from
1567 project level(pa_projects_all) for the plan type and plan version fp options if
1568 Project currency <> Projfunc Currency for the project.
1569 ===============================================================================*/
1570 PROCEDURE Create_fp_options(
1571 p_project_id IN pa_proj_fp_options.project_id%TYPE
1572 ,p_budget_type_code IN pa_budget_versions.budget_type_code%TYPE
1573 ,p_fin_plan_version_id IN pa_proj_fp_options.fin_plan_version_id%TYPE
1574 ,p_fin_plan_option_level IN pa_proj_fp_options.fin_plan_option_level_code%TYPE
1575 ,x_proj_fp_options_id OUT NOCOPY pa_proj_fp_options.proj_fp_options_id%TYPE --File.Sql.39 bug 4440895
1576 ,x_upgrade_elements_rec OUT NOCOPY pa_fp_upgrade_pkg.upgrade_elements_rec_type
1577 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1578 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1579 ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1580
1581 l_return_status VARCHAR2(2000);
1582 l_msg_count NUMBER :=0;
1583 l_msg_data VARCHAR2(2000);
1584 l_data VARCHAR2(2000);
1585 l_msg_index_out NUMBER;
1586 l_debug_mode VARCHAR2(30);
1587
1588 l_target_proj_fp_option_id pa_proj_fp_options.proj_fp_options_id%TYPE;
1589 l_approved_cost_plan_type_flag pa_proj_fp_options.approved_cost_plan_type_flag%TYPE;
1590 l_approved_rev_plan_type_flag pa_proj_fp_options.approved_rev_plan_type_flag%TYPE;
1591
1592 l_upgrade_elements_rec upgrade_elements_rec_type;
1593
1594 l_multi_currency_billing_flag pa_projects_all.multi_currency_billing_flag%TYPE;
1595 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
1596 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
1597 l_project_bil_rate_type pa_projects_all.project_bil_rate_type%TYPE;
1598 l_projfunc_bil_rate_type pa_projects_all.projfunc_bil_rate_type%TYPE;
1599 l_project_cost_rate_type pa_projects_all.project_rate_type%TYPE;
1600 l_projfunc_cost_rate_type pa_projects_all.projfunc_cost_rate_type%TYPE;
1601
1602
1603 BEGIN
1604
1605 x_msg_count := 0;
1606 x_return_status := FND_API.G_RET_STS_SUCCESS;
1607
1608 pa_debug.set_err_stack('PA_FP_UPGRADE_PKG.Create_fp_options');
1609 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1610 l_debug_mode := NVL(l_debug_mode, 'Y');
1611 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
1612
1613 IF p_pa_debug_mode = 'Y' THEN
1614 pa_debug.g_err_stage := 'Entered Create_fp_options';
1615 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1616
1617 pa_debug.g_err_stage := 'Calling Populate_Local_Variables';
1618 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1619 END IF;
1620
1621 pa_fp_upgrade_pkg.Populate_Local_Variables(
1622 p_project_id => p_project_id
1623 ,p_budget_type_code => p_budget_type_code
1624 ,p_fin_plan_version_id => p_fin_plan_version_id
1625 ,p_fin_plan_option_level => p_fin_plan_option_level
1626 ,x_upgrade_elements_rec => l_upgrade_elements_rec
1627 ,x_return_status => l_return_status
1628 ,x_msg_count => l_msg_count
1629 ,x_msg_data => l_msg_data);
1630 if (l_return_status <> 'S') then
1631 raise pa_fp_constants_pkg.Invalid_Arg_Exc;
1632 end if;
1633
1634 --Calling create_fp_option api to create an option
1635 IF p_pa_debug_mode = 'Y' THEN
1636 pa_debug.g_err_stage := 'Calling Create_FP_Option of PA_PROJ_FP_OPTIONS_PUB';
1637 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1638 END IF;
1639 PA_PROJ_FP_OPTIONS_PUB.Create_FP_Option (
1640 px_target_proj_fp_option_id => l_target_proj_fp_option_id
1641 ,p_source_proj_fp_option_id => NULL
1642 ,p_target_fp_option_level_code => l_upgrade_elements_rec.curr_option_level_code
1643 ,p_target_fp_preference_code => l_upgrade_elements_rec.curr_option_preference_code
1644 ,p_target_fin_plan_version_id => l_upgrade_elements_rec.curr_option_plan_version_id
1645 ,p_target_project_id => l_upgrade_elements_rec.curr_option_project_id
1646 ,p_target_plan_type_id => l_upgrade_elements_rec.curr_option_plan_type_id
1647 ,x_return_status => l_return_status
1648 ,x_msg_count => l_msg_count
1649 ,x_msg_data => l_msg_data);
1650 if (l_return_status <> 'S') then
1651 raise pa_fp_constants_pkg.Invalid_Arg_Exc;
1652 end if;
1653
1654 --Bug 4336691. The below block which, based on the approved cost/rev budgets plan settings,
1655 --could possibly update the Project Option's settings to have Date Range Time Phase or Top/Lowest
1656 --Planning Level is not executed. Note that for Project level options, PADTRNGB.DATE_RANGE_UPGRD and
1657 --PABDGATB.BUDGET_ATTR_UPGRD will not be called in the flow.
1658 IF l_upgrade_elements_rec.curr_option_level_code <> PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT THEN
1659
1660 --Update option with planning level,time phasing,resourcelist, amount set id,
1661 --approved_rev_plan_type_flag,approved_cost_plan_type_flag values
1662 --based upon the curr_option_preference_code and g_curr_option_budget_type_code
1663
1664 IF l_upgrade_elements_rec.curr_option_level_code IN(PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE,
1665 PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION) THEN
1666 IF l_upgrade_elements_rec.curr_option_budget_type_code = PA_FP_CONSTANTS_PKG.G_BUDGET_TYPE_CODE_AC THEN
1667 l_approved_cost_plan_type_flag := 'Y';
1668 l_approved_rev_plan_type_flag := 'N';
1669 ELSIF l_upgrade_elements_rec.curr_option_budget_type_code = PA_FP_CONSTANTS_PKG.G_BUDGET_TYPE_CODE_AR THEN
1670 l_approved_cost_plan_type_flag := 'N';
1671 l_approved_rev_plan_type_flag := 'Y';
1672 ELSE
1673 l_approved_cost_plan_type_flag := 'N';
1674 l_approved_rev_plan_type_flag := 'N';
1675 END IF;
1676 ELSE
1677 l_approved_cost_plan_type_flag := 'N';
1678 l_approved_rev_plan_type_flag := 'N';
1679
1680 END IF;
1681
1682 /* FP M related columns upgrade is done by pa_budget_attr_upgr_pkg.budget_attr_upgrd later in the api.
1683 pa_budget_attr_upgr_pkg.budget_attr_upgrd also takes care of upgrade FP M attribs for
1684 project and plan type level records */
1685
1686 IF l_upgrade_elements_rec.curr_option_preference_code IN ( PA_FP_CONSTANTS_PKG.G_PREF_COST_ONLY,
1687 PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SEP)
1688 AND (l_upgrade_elements_rec.basis_cost_bem IS NOT NULL)
1689 THEN
1690
1691 UPDATE PA_PROJ_FP_OPTIONS
1692 SET cost_fin_plan_level_code = l_upgrade_elements_rec.basis_cost_planning_level
1693 ,cost_time_phased_code = l_upgrade_elements_rec.basis_cost_time_phased_code
1694 ,cost_resource_list_id = l_upgrade_elements_rec.basis_cost_res_list_id
1695 ,cost_amount_set_id = l_upgrade_elements_rec.basis_cost_amount_set_id
1696 ,approved_cost_plan_type_flag = l_approved_cost_plan_type_flag
1697 ,approved_rev_plan_type_flag = l_approved_rev_plan_type_flag
1698 --Bug 4174907
1699 ,primary_cost_forecast_flag = 'N'
1700 ,primary_rev_forecast_flag = 'N'
1701 WHERE proj_fp_options_id = l_target_proj_fp_option_id;
1702
1703 END IF;
1704
1705 IF l_upgrade_elements_rec.curr_option_preference_code IN ( PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY,
1706 PA_FP_CONSTANTS_PKG.G_PREF_COST_AND_REV_SEP)
1707 AND (l_upgrade_elements_rec.basis_rev_bem IS NOT NULL)
1708 THEN
1709
1710 UPDATE PA_PROJ_FP_OPTIONS
1711 SET revenue_fin_plan_level_code = l_upgrade_elements_rec.basis_rev_planning_level
1712 ,revenue_time_phased_code = l_upgrade_elements_rec.basis_rev_time_phased_code
1713 ,revenue_resource_list_id = l_upgrade_elements_rec.basis_rev_res_list_id
1714 ,revenue_amount_set_id = l_upgrade_elements_rec.basis_rev_amount_Set_id
1715 ,approved_cost_plan_type_flag = l_approved_cost_plan_type_flag
1716 ,approved_rev_plan_type_flag = l_approved_rev_plan_type_flag
1717 --Bug 4174907
1718 ,primary_cost_forecast_flag = 'N'
1719 ,primary_rev_forecast_flag = 'N'
1720 WHERE proj_fp_options_id = l_target_proj_fp_option_id;
1721
1722 END IF;
1723
1724 END IF;--Bug 4336691.
1725
1726 IF l_upgrade_elements_rec.curr_option_level_code IN(PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE,
1727 PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_VERSION) THEN
1728
1729 -- Get project currency info and the cost,revenue conversion attributes
1730
1731 PA_FIN_PLAN_UTILS.Get_Project_Curr_Attributes
1732 ( p_project_id => p_project_id
1733 ,x_multi_currency_billing_flag => l_multi_currency_billing_flag
1734 ,x_project_currency_code => l_project_currency_code
1735 ,x_projfunc_currency_code => l_projfunc_currency_code
1736 ,x_project_cost_rate_type => l_project_cost_rate_type
1737 ,x_projfunc_cost_rate_type => l_projfunc_cost_rate_type
1738 ,x_project_bil_rate_type => l_project_bil_rate_type
1739 ,x_projfunc_bil_rate_type => l_projfunc_bil_rate_type
1740 ,x_return_status => l_return_status
1741 ,x_msg_count => l_msg_count
1742 ,x_msg_data => l_msg_data );
1743
1744 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1745 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1746 END IF;
1747
1748 -- Populate the MC conversion attributes for the fp option if MC enabled
1749
1750 IF l_project_currency_code <> l_projfunc_currency_code THEN
1751
1752 UPDATE PA_PROJ_FP_OPTIONS
1753 SET projfunc_cost_rate_type = l_projfunc_cost_rate_type
1754 ,projfunc_cost_rate_date_type = PA_FP_CONSTANTS_PKG.G_RATE_DATE_TYPE_START_DATE
1755 ,projfunc_rev_rate_type = l_projfunc_bil_rate_type
1756 ,projfunc_rev_rate_date_type = PA_FP_CONSTANTS_PKG.G_RATE_DATE_TYPE_START_DATE
1757 ,project_cost_rate_type = l_project_cost_rate_type
1758 ,project_cost_rate_date_type = PA_FP_CONSTANTS_PKG.G_RATE_DATE_TYPE_START_DATE
1759 ,project_rev_rate_type = l_project_bil_rate_type
1760 ,project_rev_rate_date_type = PA_FP_CONSTANTS_PKG.G_RATE_DATE_TYPE_START_DATE
1761 WHERE proj_fp_options_id = l_target_proj_fp_option_id;
1762
1763 END IF;
1764
1765 END IF;
1766 x_proj_fp_options_id := l_target_proj_fp_option_id;
1767 x_upgrade_elements_rec := l_upgrade_elements_rec;
1768
1769 IF p_pa_debug_mode = 'Y' THEN
1770 pa_debug.g_err_stage := 'Exiting Create_fp_options';
1771 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1772 END IF;
1773 pa_debug.reset_err_stack;
1774 EXCEPTION
1775
1776 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1777
1778 l_msg_count := FND_MSG_PUB.count_msg;
1779 IF l_msg_count = 1 THEN
1780 PA_INTERFACE_UTILS_PUB.get_messages
1781 (p_encoded => FND_API.G_TRUE
1782 ,p_msg_index => 1
1783 ,p_msg_count => l_msg_count
1784 ,p_msg_data => l_msg_data
1785 ,p_data => l_data
1786 ,p_msg_index_out => l_msg_index_out);
1787 x_msg_data := l_data;
1788 x_msg_count := l_msg_count;
1789 ELSE
1790 x_msg_count := l_msg_count;
1791 x_msg_data := l_msg_data;
1792 END IF;
1793
1794 IF p_pa_debug_mode = 'Y' THEN
1795 pa_debug.g_err_stage:='Invalid Arguments Passed';
1796 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1797 pa_debug.write_file('Create_fp_options ' || x_msg_data,5);
1798 END IF;
1799 x_return_status:= FND_API.G_RET_STS_ERROR;
1800 pa_debug.reset_err_stack;
1801 RAISE;
1802
1803 WHEN Others THEN
1804
1805 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1806 x_msg_count := 1;
1807 x_msg_data := SQLERRM;
1808
1809 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_FP_UPGRADE_PKG'
1810 ,p_procedure_name => 'Create_fp_options');
1811 IF p_pa_debug_mode = 'Y' THEN
1812 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
1813 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1814 pa_debug.write_file('Create_fp_options ' || pa_debug.G_Err_Stack,5);
1815 END IF;
1816 pa_debug.reset_err_stack;
1817 RAISE;
1818 END Create_fp_options;
1819
1820 /*=============================================================================
1821 This process will identify the budget types for which budget versions exist for
1822 the project and add those plan types to the project.
1823 =============================================================================*/
1824 Procedure Add_Plan_Types(
1825 p_project_id IN pa_projects.project_id%TYPE
1826 ,p_budget_types IN VARCHAR2
1827 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1828 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1829 ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1830
1831 l_return_status VARCHAR2(2000);
1832 l_msg_count NUMBER :=0;
1833 l_msg_data VARCHAR2(2000);
1834 l_data VARCHAR2(2000);
1835 l_msg_index_out NUMBER;
1836 l_debug_mode VARCHAR2(30);
1837
1838 l_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
1839
1840 l_upgrade_elements_rec upgrade_elements_rec_type;
1841
1842 l_validation_status VARCHAR2(30);
1843
1844 /* Bug #2727377 */
1845 l_fp_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
1846 l_multi_curr_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
1847
1848 BEGIN
1849
1850 x_msg_count := 0;
1851 x_return_status := FND_API.G_RET_STS_SUCCESS;
1852
1853 pa_debug.set_err_stack('PA_FP_UPGRADE_PKG.Add_Plan_Types');
1854 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1855 l_debug_mode := NVL(l_debug_mode, 'Y');
1856 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
1857 IF p_pa_debug_mode = 'Y' THEN
1858 pa_debug.g_err_stage := 'Entered Add_Plan_Types';
1859 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1860
1861 -- Check for not null parameters
1862
1863 pa_debug.g_err_stage := 'Checking for valid parameters:';
1864 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1865 END IF;
1866 IF (p_project_id IS NULL) OR (p_budget_types IS NULL)
1867 THEN
1868 IF p_pa_debug_mode = 'Y' THEN
1869 pa_debug.g_err_stage := 'p_project_id = '||p_project_id;
1870 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1871 pa_debug.g_err_stage := 'p_budget_types = '||p_budget_types;
1872 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1873 END IF;
1874
1875 PA_UTILS.ADD_MESSAGE(p_app_short_name=> 'PA',
1876 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
1877
1878 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1879
1880 END IF;
1881 IF p_pa_debug_mode = 'Y' THEN
1882 pa_debug.g_err_stage := 'Parameter validation complete';
1883 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1884 END IF;
1885 -- Fetch each budget type used for the project
1886
1887 OPEN attached_plan_types_cur(p_project_id,p_budget_types);
1888 LOOP
1889 FETCH attached_plan_types_cur INTO attached_plan_types_rec;
1890 EXIT WHEN attached_plan_types_cur%NOTFOUND;
1891
1892 -- For each budget type fetched check if any plan type level exceptions exist
1893 pa_fp_upgrade_pkg.Validate_Project_Plan_Type (
1894 p_project_id => p_project_id
1895 ,p_budget_type_code => attached_plan_types_rec.budget_type_code
1896 ,x_validation_status => l_validation_status
1897 ,x_return_status => l_return_status
1898 ,x_msg_count => l_msg_count
1899 ,x_msg_data => l_msg_data);
1900
1901 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1902 RAISE PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
1903 END IF;
1904
1905 IF l_validation_status = 'Y' THEN
1906
1907 -- For each budget type fetched create fp options
1908
1909 l_proj_fp_options_id := NULL;
1910
1911 IF p_pa_debug_mode = 'Y' THEN
1912 pa_debug.g_err_stage := 'Calling Create_fp_options for plan type';
1913 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1914 END IF;
1915 pa_fp_upgrade_pkg.Create_fp_options(
1916 p_project_id => p_project_id
1917 ,p_budget_type_code => attached_plan_types_rec.budget_type_code
1918 ,p_fin_plan_version_id => NULL
1919 ,p_fin_plan_option_level => PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
1920 ,x_proj_fp_options_id => l_proj_fp_options_id
1921 ,x_upgrade_elements_rec => l_upgrade_elements_rec
1922 ,x_return_status => l_return_status
1923 ,x_msg_count => l_msg_count
1924 ,x_msg_data => l_msg_data);
1925
1926 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1927 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1928 END IF;
1929 /* 2727377: Added call to copy_fp_txn_currencies API to populate the currencies
1930 in pa_fp_txn_currencies. The source fp option ID is being passed as NULL as
1931 the source is determined in copy_fp_txn_currencies API in case the source is
1932 not passed. Plan in multi currency flag and the fp_preference_code is
1933 retrieved from pa_proj_fp_options_table. */
1934
1935 SELECT fin_plan_preference_code, plan_in_multi_curr_flag
1936 INTO l_fp_preference_code, l_multi_curr_flag
1937 FROM pa_proj_fp_options
1938 WHERE proj_fp_options_id = l_proj_fp_options_id;
1939
1940 IF p_pa_debug_mode = 'Y' THEN
1941 pa_debug.g_err_stage := 'Calling copy_fp_txn_currencies for plan type';
1942 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1943 END IF;
1944 pa_fp_txn_currencies_pub.Copy_Fp_Txn_Currencies (
1945 p_source_fp_option_id => NULL
1946 ,p_target_fp_option_id => l_proj_fp_options_id
1947 ,p_target_fp_preference_code => l_fp_preference_code
1948 ,p_plan_in_multi_curr_flag => l_multi_curr_flag
1949 ,x_return_status => l_return_status
1950 ,x_msg_count => l_msg_count
1951 ,x_msg_data => l_msg_data);
1952
1953 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1954 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
1955 END IF;
1956
1957 -- Insert into audit table
1958 pa_fp_upgrade_pkg.Insert_Audit_Record(
1959 p_project_id => p_project_id
1960 ,p_budget_type_code => attached_plan_types_rec.budget_type_code
1961 ,p_proj_fp_options_id => l_proj_fp_options_id
1962 ,p_fin_plan_option_level_code => PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
1963 ,p_basis_cost_version_id => l_upgrade_elements_rec.basis_cost_version_id
1964 ,p_basis_rev_version_id => l_upgrade_elements_rec.basis_rev_version_id
1965 ,p_basis_cost_bem => l_upgrade_elements_rec.basis_cost_bem
1966 ,p_basis_rev_bem => l_upgrade_elements_rec.basis_rev_bem
1967 ,p_upgraded_flag => 'Y'
1968 ,p_failure_reason_code => NULL);
1969
1970 END IF;
1971 END LOOP;
1972 CLOSE attached_plan_types_cur;
1973 IF p_pa_debug_mode = 'Y' THEN
1974 pa_debug.g_err_stage := 'Exiting Add_Plan_Types';
1975 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
1976 END IF;
1977 pa_debug.reset_err_stack;
1978
1979 EXCEPTION
1980
1981 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
1982
1983 IF attached_plan_types_cur%ISOPEN THEN
1984 CLOSE attached_plan_types_cur;
1985 END IF;
1986 l_msg_count := FND_MSG_PUB.count_msg;
1987 IF l_msg_count = 1 THEN
1988 PA_INTERFACE_UTILS_PUB.get_messages
1989 (p_encoded => FND_API.G_TRUE
1990 ,p_msg_index => 1
1991 ,p_msg_count => l_msg_count
1992 ,p_msg_data => l_msg_data
1993 ,p_data => l_data
1994 ,p_msg_index_out => l_msg_index_out);
1995 x_msg_data := l_data;
1996 x_msg_count := l_msg_count;
1997 ELSE
1998 x_msg_count := l_msg_count;
1999 x_msg_data := l_msg_data;
2000 END IF;
2001
2002 IF p_pa_debug_mode = 'Y' THEN
2003 pa_debug.g_err_stage:='Invalid Arguments Passed';
2004 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2005 pa_debug.write_file('Add_Plan_Types ' || x_msg_data,5);
2006 END IF;
2007 x_return_status:= FND_API.G_RET_STS_ERROR;
2008 pa_debug.reset_err_stack;
2009 RAISE;
2010
2011 WHEN Others THEN
2012
2013 IF attached_plan_types_cur%ISOPEN THEN
2014 CLOSE attached_plan_types_cur;
2015 END IF;
2016 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2017 x_msg_count := 1;
2018 x_msg_data := SQLERRM;
2019 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_FP_UPGRADE_PKG'
2020 ,p_procedure_name => 'Add_Plan_Types');
2021 IF p_pa_debug_mode = 'Y' THEN
2022 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
2023 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2024 pa_debug.write_file('Add_Plan_Types ' || pa_debug.G_Err_Stack,5);
2025 END IF;
2026 pa_debug.reset_err_stack;
2027 RAISE;
2028
2029 END Add_Plan_Types;
2030 /*=============================================================================
2031 This api will upgrade all the budget versions eligible for upgrade based upon the
2032 input.This api will do the following:-
2033 1.Create record in PA_PROJ_FP_OPTIONS for the budget version.
2034 2.Create fp elements
2035 3.Update Budget Version in PA_BUDGET_VERSIONS
2036 4.Create resource assignments
2037 5.Roll up resource assignments
2038 6.Create period denorm records for the budget.
2039
2040
2041 -- 07-JUN-04 jwhite Bug 3673111
2042 -- When I closely reviewed this package for
2043 -- FP.M resource list and RBS modifications,
2044 -- I found so many issues that I decided to do
2045 -- following:
2046 -- 1) Move most of the calls to this
2047 -- private Upgrade_Budget_Versions api.
2048 -- 2) Change the FP.M Uprade api calls to process one
2049 -- budget_version_id at a time per the budget_version
2050 -- cursor in this procedure.
2051 -- 12-Dec-06 nkumbi Bug 5676682 :Same local variables cannot be passed as both
2052 -- IN and OUT variables to an api. Fixed the issue
2053 -- in upgrade_budget_versions api while calling
2054 -- apply_calculate_fpm_rules.
2055 =============================================================================*/
2056 PROCEDURE Upgrade_Budget_Versions (
2057 p_project_id IN pa_projects.project_id%TYPE
2058 ,p_budget_types IN VARCHAR2
2059 ,p_budget_statuses IN VARCHAR2
2060 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2061 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2062 ,x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
2063
2064 l_return_status VARCHAR2(2000);
2065 l_msg_count NUMBER :=0;
2066 l_msg_data VARCHAR2(2000);
2067 l_data VARCHAR2(2000);
2068 l_msg_index_out NUMBER;
2069 l_debug_mode VARCHAR2(30);
2070
2071 l_budget_version_id pa_budget_versions.budget_version_id%TYPE;
2072 l_version_type pa_budget_versions.version_type%TYPE;
2073 l_approved_cost_plan_type_flag pa_budget_versions.approved_cost_plan_type_flag%TYPE;
2074 l_approved_rev_plan_type_flag pa_budget_versions.approved_rev_plan_type_flag%TYPE;
2075 l_prev_budget_type_code pa_budget_versions.budget_type_code%TYPE;
2076
2077 l_proj_fp_options_id pa_proj_fp_options.proj_fp_options_id%TYPE;
2078
2079 l_upgrade_elements_rec upgrade_elements_rec_type;
2080
2081 /* Bug #2727377 */
2082 l_fp_preference_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
2083 l_multi_curr_flag pa_proj_fp_options.plan_in_multi_curr_flag%TYPE;
2084
2085 -- Bug 3673111, 07-JUN-04, jwhite -----------------------------------------------
2086
2087
2088 l_migration_code varchar2(1) := null;
2089 l_budget_ver_tbl SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
2090
2091 -- End Bug 3673111 --------------------------------------------------------------
2092 -- Added for Bug# 7187487
2093 l_budget_status_code_tbl SYSTEM.pa_varchar2_1_tbl_type:=SYSTEM.pa_varchar2_1_tbl_type();
2094
2095
2096 --Bug 4300363
2097 l_upg_bl_id_tbl SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
2098 l_upg_ra_id_tbl SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
2099 l_upg_quantity_tbl SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
2100 l_upg_quantity_tbl_in SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
2101 l_upg_txn_raw_cost_tbl SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
2102 l_upg_txn_raw_cost_tbl_in SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
2103 l_upg_txn_burdened_cost_tbl SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
2104 l_upg_txn_burdened_cost_tbl_in SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
2105 l_upg_txn_revenue_tbl SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
2106 l_upg_txn_revenue_tbl_in SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type(); --Bug 5676682
2107 l_upg_rate_based_flag_tbl SYSTEM.pa_varchar2_1_tbl_type:=SYSTEM.pa_varchar2_1_tbl_type();
2108 l_upg_raw_cost_rate_tbl SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
2109 l_upg_burd_cost_rate_tbl SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
2110 l_upg_bill_rate_tbl SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
2111 l_upg_non_rb_ra_id_tbl SYSTEM.pa_num_tbl_type:=SYSTEM.pa_num_tbl_type();
2112 l_pref_code pa_proj_fp_options.fin_plan_preference_code%TYPE;
2113
2114 -- bug 4865563: added the followings
2115 l_fp_cols_rec_var PA_FP_GEN_AMOUNT_UTILS.FP_COLS;
2116
2117 BEGIN
2118 x_msg_count := 0;
2119 x_msg_data := NULL;
2120 x_return_status := FND_API.G_RET_STS_SUCCESS;
2121
2122 pa_debug.set_err_stack('PA_FP_UPGRADE_PKG.Upgrade_Budget_Versions');
2123 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2124 l_debug_mode := NVL(l_debug_mode, 'Y');
2125 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
2126 IF p_pa_debug_mode = 'Y' THEN
2127 pa_debug.g_err_stage := 'Entered Upgrade_Budget_Versions';
2128 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2129
2130 -- Check for not null parameters
2131
2132 pa_debug.g_err_stage := 'Checking for valid parameters:';
2133 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2134 END IF;
2135
2136 IF (p_project_id IS NULL) OR
2137 (p_budget_types IS NULL) OR
2138 (p_budget_statuses IS NULL)
2139 THEN
2140 IF p_pa_debug_mode = 'Y' THEN
2141 pa_debug.g_err_stage := 'p_project_id = '||p_project_id;
2142 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2143 pa_debug.g_err_stage := 'p_budget_types = '||p_budget_types;
2144 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2145 pa_debug.g_err_stage := 'p_budget_statuses= '||p_budget_statuses;
2146 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2147 END IF;
2148
2149 PA_UTILS.ADD_MESSAGE(p_app_short_name=> 'PA',
2150 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
2151
2152 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2153
2154 END IF;
2155 IF p_pa_debug_mode = 'Y' THEN
2156 pa_debug.g_err_stage := 'Parameter validation complete';
2157 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2158
2159 END IF;
2160
2161 --Fetch the budget versions that are eligible for upgrade
2162 IF p_pa_debug_mode = 'Y' THEN
2163 pa_debug.g_err_stage := 'Opening budgets_for_upgrade_cur ';
2164 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2165 END IF;
2166
2167
2168 -- This Processes ONE Qualifying Budget_Version_Id At a Time --------------------------
2169
2170 OPEN budgets_for_upgrade_cur(p_project_id,p_budget_types,p_budget_statuses,'UPGRADE');
2171 LOOP
2172 --Bug 4171254. Corrected the order of the pl/sql tbls to match the order of the columns selected
2173 FETCH budgets_for_upgrade_cur BULK COLLECT INTO l_budget_ver_tbl,l_bud_typ_code_tbl,l_res_list_tbl,l_budget_status_code_tbl -- Added l_budget_status_code_tbl for Bug# 7187487
2174
2175 LIMIT 200;
2176
2177 --Bug 4171254.
2178 IF l_budget_ver_tbl.COUNT>0 THEN
2179
2180 FOR j in l_budget_ver_tbl.first .. l_budget_ver_tbl.last loop
2181
2182 l_proj_fp_options_id := NULL;
2183
2184 --Create fp option for the budget version
2185 IF p_pa_debug_mode = 'Y' THEN
2186 pa_debug.g_err_stage := 'Calling Create_fp_options for '|| l_budget_ver_tbl(j);
2187 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2188 END IF;
2189
2190 pa_fp_upgrade_pkg.Create_fp_options(
2191 p_project_id => p_project_id
2192 ,p_budget_type_code => l_bud_typ_code_tbl(j)
2193 ,p_fin_plan_version_id => l_budget_ver_tbl(j)
2194 ,p_fin_plan_option_level => pa_fp_constants_pkg.g_option_level_plan_version
2195 ,x_proj_fp_options_id => l_proj_fp_options_id
2196 ,x_upgrade_elements_rec => l_upgrade_elements_rec
2197 ,x_return_status => l_return_status
2198 ,x_msg_count => l_msg_count
2199 ,x_msg_data => l_msg_data);
2200
2201 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2202 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2203 END IF;
2204
2205 /* 2727377: Added call to copy_fp_txn_currencies API to populate the currencies
2206 in pa_fp_txn_currencies. The source fp option ID is being passed as NULL as
2207 the source is determined in copy_fp_txn_currencies API in case the source is
2208 not passed. Plan in multi currency flag and the fp_preference_code is
2209 retrieved from pa_proj_fp_options_table. */
2210
2211 SELECT fin_plan_preference_code, plan_in_multi_curr_flag
2212 INTO l_fp_preference_code, l_multi_curr_flag
2213 FROM pa_proj_fp_options
2214 WHERE proj_fp_options_id = l_proj_fp_options_id;
2215
2216 IF p_pa_debug_mode = 'Y' THEN
2217 pa_debug.g_err_stage := 'Calling copy_fp_txn_currencies for version';
2218 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2219 END IF;
2220 pa_fp_txn_currencies_pub.Copy_Fp_Txn_Currencies (
2221 p_source_fp_option_id => NULL
2222 ,p_target_fp_option_id => l_proj_fp_options_id
2223 ,p_target_fp_preference_code => l_fp_preference_code
2224 ,p_plan_in_multi_curr_flag => l_multi_curr_flag
2225 ,x_return_status => l_return_status
2226 ,x_msg_count => l_msg_count
2227 ,x_msg_data => l_msg_data);
2228
2229 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2230 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2231 END IF;
2232
2233 --Setting the variables for upgrading budget version to fin_plan version
2234
2235 --Set l_period_profile_id,l_version_type
2236
2237 IF l_upgrade_elements_rec.curr_option_preference_code = pa_fp_constants_pkg.G_PREF_COST_ONLY THEN
2238
2239 l_version_type := PA_FP_CONSTANTS_PKG.G_VERSION_TYPE_COST;
2240
2241 ELSIF l_upgrade_elements_rec.curr_option_preference_code = PA_FP_CONSTANTS_PKG.G_PREF_REVENUE_ONLY THEN
2242
2243 l_version_type := PA_FP_CONSTANTS_PKG.G_VERSION_TYPE_REVENUE;
2244
2245 END IF;
2246
2247 --Set l_approved_cost_plan_type_flag,l_approved_rev_plan_type_flag variables
2248 --using g_curr_option_budget_type_code
2249
2250 IF l_upgrade_elements_rec.curr_option_budget_type_code = PA_FP_CONSTANTS_PKG.G_BUDGET_TYPE_CODE_AC THEN
2251 l_approved_cost_plan_type_flag := 'Y';
2252 l_approved_rev_plan_type_flag := 'N';
2253 ELSIF l_upgrade_elements_rec.curr_option_budget_type_code = PA_FP_CONSTANTS_PKG.G_BUDGET_TYPE_CODE_AR THEN
2254 l_approved_cost_plan_type_flag := 'N';
2255 l_approved_rev_plan_type_flag := 'Y';
2256 ELSE
2257 l_approved_cost_plan_type_flag := 'N';
2258 l_approved_rev_plan_type_flag := 'N';
2259 END IF;
2260
2261 IF p_pa_debug_mode = 'Y' THEN
2262 pa_debug.g_err_stage := 'Updating budget version '||l_budget_ver_tbl(j);
2263 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2264 END IF;
2265
2266 /* FP M related columns upgrade is done by pa_budget_attr_upgr_pkg.budget_attr_upgrd later in the api. */
2267
2268 UPDATE PA_BUDGET_VERSIONS
2269 SET budget_type_code = NULL,
2270 version_name = nvl(version_name,to_char(version_number)),-- Added for Bug 6722317
2271 fin_plan_type_id = l_upgrade_elements_rec.curr_option_plan_type_id,
2272 version_type = l_version_type,
2273 approved_cost_plan_type_flag = l_approved_cost_plan_type_flag,
2274 approved_rev_plan_type_flag = l_approved_rev_plan_type_flag,
2275 record_version_number = NVl(record_version_number,0) + 1, -- null handling ,bug 2788983
2276 first_budget_period = NULL,
2277 request_id = FND_GLOBAL.conc_request_id,
2278 last_update_date = sysdate,
2279 last_updated_by = fnd_global.user_id,
2280 creation_date = sysdate,
2281 created_by = fnd_global.user_id,
2282 last_update_login = fnd_global.login_id,
2283 current_working_flag = DECODE(budget_status_code,
2284 PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_WORKING,'Y',NULL)
2285 --Bug 4174907
2286 ,primary_cost_forecast_flag = 'N'
2287 ,primary_rev_forecast_flag = 'N'
2288 WHERE budget_version_id = l_budget_ver_tbl(j);
2289
2290 --update the resource assignments table
2291 IF p_pa_debug_mode = 'Y' THEN
2292 pa_debug.g_err_stage := 'Updating resource assignments '||l_budget_ver_tbl(j);
2293 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2294 END IF;
2295
2296 UPDATE PA_RESOURCE_ASSIGNMENTS
2297 SET resource_assignment_type = PA_FP_CONSTANTS_PKG.G_USER_ENTERED
2298 WHERE budget_version_id = l_budget_ver_tbl(j);
2299
2300 --Populate txn currency buckets from the project functional currency buckets of budget lines
2301 IF p_pa_debug_mode = 'Y' THEN
2302 pa_debug.g_err_stage := 'Updating budget lines for '|| l_budget_ver_tbl(j);
2303 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2304 END IF;
2305
2306 UPDATE PA_BUDGET_LINES
2307 SET txn_raw_cost = raw_cost,
2308 txn_burdened_cost = burdened_cost,
2309 txn_revenue = revenue
2310 WHERE budget_version_id = l_budget_ver_tbl(j);
2311
2312 --Call convert_txn_currencies api to populate the project and project functional amounts
2313 --from txn_currency amounts.
2314
2315 IF p_pa_debug_mode = 'Y' THEN
2316 pa_debug.g_err_stage := 'Calling convert_txn_currency ';
2317 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2318 END IF;
2319
2320 PA_FP_MULTI_CURRENCY_PKG.convert_txn_currency (
2321 p_budget_version_id => l_budget_ver_tbl(j)
2322 ,p_entire_version => 'Y'
2323 ,x_return_status => l_return_status
2324 ,x_msg_count => l_msg_count
2325 ,x_msg_data => l_msg_data );
2326
2327 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN /* Bug# 2644641 */
2328 /*For bug 2755740*/
2329 IF p_pa_debug_mode = 'Y' THEN
2330 pa_debug.write_file('Upgrade failed due to error in PA_FP_MULTI_CURRENCY_PKG.convert_txn_currency',5);
2331 END IF;
2332 raise PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
2333 END IF;
2334
2335 -- Convert the Resource List to a Planning Resource List, if Not Already Done So.
2336
2337 IF p_pa_debug_mode = 'Y' THEN
2338 pa_debug.g_err_stage := 'Is Version Resource List Already a Planning Resource List? ';
2339 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2340 END IF;
2341
2342 l_migration_code := null;
2343 SELECT migration_code
2344 INTO l_migration_code
2345 FROM pa_resource_lists_all_bg
2346 WHERE resource_list_id = l_res_list_tbl(j);
2347
2348 IF (l_migration_code is null )
2349 then
2350
2351 IF p_pa_debug_mode ='Y' THEN
2352 pa_debug.g_err_stage := 'Calling Resource List Upgrade to Planning Resource List';
2353 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2354 END IF;
2355
2356
2357
2358 -- Bug 3802762, 30-JUN-2004, jwhite ------------------------------------
2359 -- Added the following IN-parmeters as FND_API.G_FALSE
2360 -- 1) p_commit
2361 -- 2) p_init_msg_list
2362
2363
2364 PA_RES_LIST_UPGRADE_PKG.RES_LIST_TO_PLAN_RES_LIST(
2365 p_resource_list_id => l_res_list_tbl(j)
2366 , p_commit => FND_API.G_FALSE
2367 , p_init_msg_list => FND_API.G_FALSE
2368 , x_return_status => l_return_status
2369 , x_msg_count => l_msg_count
2370 , x_msg_data => l_msg_data);
2371
2372
2373
2374 -- End Bug 3802762, 30-JUN-2004, jwhite ------------------------------------
2375
2376 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2377 THEN
2378 IF p_pa_debug_mode = 'Y' THEN
2379 pa_debug.write_file('Upgrade failed due to error in PA_RES_LIST_UPGRADE_PKG.RES_LIST_TO_PLAN_RES_LIST ',5);
2380 END IF;
2381 raise PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
2382 END IF;
2383
2384
2385 END IF; -- l_migration_code is null
2386
2387
2388 END LOOP;
2389
2390 -- Perform Budget Version Data Entity Migration to FP.M Data Model
2391
2392 IF p_pa_debug_mode = 'Y' THEN
2393 pa_debug.g_err_stage := 'Calling Budget Attribute Upgrade';
2394 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2395 END IF;
2396
2397 FOR k in l_budget_ver_tbl.first .. l_budget_ver_tbl.last LOOP
2398
2399 pa_budget_attr_upgr_pkg.budget_attr_upgrd(
2400 p_project_id => p_project_id
2401 ,p_budget_version_id => l_budget_ver_tbl(k)
2402 ,x_return_status => l_return_status
2403 ,x_msg_count => l_msg_count
2404 ,x_msg_data => l_msg_data);
2405
2406 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2407 THEN
2408 IF p_pa_debug_mode = 'Y' THEN
2409 pa_debug.write_file('Upgrade failed due to error in pa_budget_attr_upgr_pkg.budget_attr_upgrd ',5);
2410 END IF;
2411 raise PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
2412 END IF;
2413
2414 END LOOP;
2415
2416 IF p_pa_debug_mode = 'Y' THEN
2417 pa_debug.g_err_stage := 'Calling Rate Attributes Upgrade';
2418 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2419 END IF;
2420
2421 pa_rate_attr_pkg.rate_attr_upgrd(
2422 p_budget_ver_tbl => l_budget_ver_tbl
2423 ,x_return_status => l_return_status
2424 ,x_msg_count => l_msg_count
2425 ,x_msg_data => l_msg_data);
2426
2427 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2428 THEN
2429 IF p_pa_debug_mode = 'Y' THEN
2430 pa_debug.write_file('Upgrade failed due to error in pa_rate_attr_pkg.rate_attr_upgrd ',5);
2431 END IF;
2432 raise PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
2433 END IF;
2434
2435
2436 IF p_pa_debug_mode = 'Y' THEN
2437 pa_debug.g_err_stage := 'Calling Date Range Upgrade Attributes';
2438 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2439 END IF;
2440 pa_date_range_pkg.date_range_upgrd(
2441 p_budget_versions => l_budget_ver_tbl
2442 ,x_return_status => l_return_status
2443 ,x_msg_count => l_msg_count
2444 ,x_msg_data => l_msg_data);
2445
2446 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2447 THEN
2448 IF p_pa_debug_mode = 'Y' THEN
2449 pa_debug.write_file('Upgrade failed due to error in pa_date_range_pkg.date_range_upgrd ',5);
2450 END IF;
2451 raise PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
2452 END IF;
2453
2454 IF p_pa_debug_mode = 'Y' THEN
2455 pa_debug.g_err_stage := 'Calling rollup_budget_version ';
2456 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2457 END IF;
2458
2459 FOR m in l_budget_ver_tbl.first .. l_budget_ver_tbl.last LOOP
2460
2461
2462 --Bug 4300363. Budget lines should not exist with NULL amounts. Below code does
2463 --the required processing for correcting such budget lines
2464
2465 DELETE
2466 FROM PA_BUDGET_LINES BL
2467 WHERE bl.budget_version_id = l_budget_ver_tbl(m)
2468 AND NVL(bl.quantity,0) = 0
2469 AND NVL(bl.txn_raw_cost,0) = 0
2470 AND NVL(bl.txn_burdened_cost,0) = 0
2471 AND NVL(bl.txn_revenue,0) = 0 ;
2472
2473 SELECT bl.budget_line_id
2474 ,bl.resource_assignment_id
2475 ,nvl(bl.quantity,0)
2476 ,nvl(bl.txn_raw_cost,0)
2477 ,nvl(bl.txn_burdened_cost,0)
2478 ,nvl(bl.txn_revenue,0)
2479 ,nvl(ra.rate_based_flag,'N') rate_based_flag
2480 BULK COLLECT INTO
2481 l_upg_bl_id_tbl
2482 ,l_upg_ra_id_tbl
2483 ,l_upg_quantity_tbl
2484 ,l_upg_txn_raw_cost_tbl
2485 ,l_upg_txn_burdened_cost_tbl
2486 ,l_upg_txn_revenue_tbl
2487 ,l_upg_rate_based_flag_tbl
2488 FROM pa_budget_lines bl
2489 ,pa_resource_assignments ra
2490 WHERE bl.resource_assignment_id=ra.resource_assignment_id
2491 AND bl.budget_version_id=l_budget_ver_tbl(m)
2492 ORDER BY bl.resource_assignment_id ,bl.quantity NULLS FIRST;
2493
2494 IF l_upg_bl_id_tbl.COUNT>0 THEN
2495
2496 SELECT fin_plan_preference_code
2497 INTO l_pref_code
2498 FROM pa_proj_fp_options
2499 WHERE fin_plan_version_id=l_budget_ver_tbl(m);
2500
2501
2502 --Call the API to correct the budget line amounts/rates
2503 l_upg_quantity_tbl_in := l_upg_quantity_tbl;
2504 l_upg_txn_raw_cost_tbl_in := l_upg_txn_raw_cost_tbl;
2505 l_upg_txn_burdened_cost_tbl_in := l_upg_txn_burdened_cost_tbl;
2506 l_upg_txn_revenue_tbl_in := l_upg_txn_revenue_tbl; --Bug 5676682
2507 pa_fp_upgrade_pkg.Apply_Calculate_FPM_Rules(
2508 p_preference_code => l_pref_code
2509 ,p_resource_assignment_id_tbl => l_upg_ra_id_tbl
2510 ,p_rate_based_flag_tbl => l_upg_rate_based_flag_tbl
2511 ,p_quantity_tbl => l_upg_quantity_tbl_in
2512 ,p_txn_raw_cost_tbl => l_upg_txn_raw_cost_tbl_in
2513 ,p_txn_burdened_cost_tbl => l_upg_txn_burdened_cost_tbl_in
2514 ,p_txn_revenue_tbl => l_upg_txn_revenue_tbl_in --Bug 5676682
2515 ,p_calling_module => 'UPGRADE' -- bug 5007734
2516 ,x_quantity_tbl => l_upg_quantity_tbl
2517 ,x_txn_raw_cost_tbl => l_upg_txn_raw_cost_tbl
2518 ,x_txn_burdened_cost_tbl => l_upg_txn_burdened_cost_tbl
2519 ,x_txn_revenue_tbl => l_upg_txn_revenue_tbl
2520 ,x_raw_cost_override_rate_tbl => l_upg_raw_cost_rate_tbl
2521 ,x_burd_cost_override_rate_tbl => l_upg_burd_cost_rate_tbl
2522 ,x_bill_override_rate_tbl => l_upg_bill_rate_tbl
2523 ,x_non_rb_ra_id_tbl => l_upg_non_rb_ra_id_tbl
2524 ,x_return_status => l_return_status
2525 ,x_msg_count => l_msg_count
2526 ,x_msg_data => l_msg_data );
2527
2528
2529 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2530 THEN
2531 IF p_pa_debug_mode = 'Y' THEN
2532 pa_debug.write_file('Upgrade failed due to error in pa_fp_upgrade_pkg.Apply_Calculate_FPM_Rules ',5);
2533 END IF;
2534 raise PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
2535 END IF;
2536
2537 FORALL j IN 1..l_upg_non_rb_ra_id_tbl.COUNT
2538
2539 UPDATE PA_RESOURCE_ASSIGNMENTS ra
2540 SET ra.rate_based_flag = 'N'
2541 ,ra.unit_of_measure = 'DOLLARS'
2542 WHERE ra.resource_assignment_id = l_upg_non_rb_ra_id_tbl(j);
2543
2544 FORALL j IN l_upg_bl_id_tbl.first .. l_upg_bl_id_tbl.last
2545
2546 UPDATE PA_BUDGET_LINES bl
2547 SET bl.quantity = l_upg_quantity_tbl(j)
2548 ,bl.txn_raw_cost = l_upg_txn_raw_cost_tbl(j)
2549 ,bl.txn_cost_rate_override = l_upg_raw_cost_rate_tbl(j)
2550 ,bl.txn_standard_cost_rate = l_upg_raw_cost_rate_tbl(j)
2551 ,bl.txn_burdened_cost = l_upg_txn_burdened_cost_tbl(j)
2552 ,bl.burden_cost_rate_override = l_upg_burd_cost_rate_tbl(j)
2553 ,bl.burden_cost_rate = l_upg_burd_cost_rate_tbl(j)
2554 ,bl.txn_revenue = l_upg_txn_revenue_tbl(j)
2555 ,bl.txn_bill_rate_override = l_upg_bill_rate_tbl(j)
2556 ,bl.txn_standard_bill_rate = l_upg_bill_rate_tbl(j)
2557 WHERE bl.budget_line_id = l_upg_bl_id_tbl(j);
2558
2559 END IF;
2560 --Call MC API
2561 PA_FP_MULTI_CURRENCY_PKG.convert_txn_currency
2562 ( p_budget_version_id => l_budget_ver_tbl(m)
2563 ,p_entire_version => 'Y'
2564 ,x_return_status =>l_return_status
2565 ,x_msg_count =>l_msg_count
2566 ,x_msg_data =>l_msg_data) ;
2567
2568 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2569 THEN
2570 IF p_pa_debug_mode = 'Y' THEN
2571 pa_debug.write_file('Upgrade failed due to error in PA_FP_MULTI_CURRENCY_PKG.convert_txn_currency ',5);
2572 END IF;
2573 raise PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
2574 END IF;
2575 -- Bug Fix: 4569365. Removed MRC code.
2576 /*
2577 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS IS NULL THEN
2578 PA_MRC_FINPLAN.CHECK_MRC_INSTALL
2579 (x_return_status => l_return_status,
2580 x_msg_count => l_msg_count,
2581 x_msg_data => l_msg_data);
2582 END IF;
2583
2584 IF PA_MRC_FINPLAN.G_MRC_ENABLED_FOR_BUDGETS AND
2585 PA_MRC_FINPLAN.G_FINPLAN_MRC_OPTION_CODE = 'A' THEN
2586
2587 PA_MRC_FINPLAN.MAINTAIN_ALL_MC_BUDGET_LINES
2588 (p_fin_plan_version_id => l_budget_ver_tbl(m),
2589 p_entire_version => 'Y',
2590 x_return_status => l_return_status,
2591 x_msg_count => l_msg_count,
2592 x_msg_data => l_msg_data);
2593
2594 END IF;
2595
2596 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2597 THEN
2598 IF p_pa_debug_mode = 'Y' THEN
2599 pa_debug.write_file('Upgrade failed due to error in PA_MRC_FINPLAN.MAINTAIN_ALL_MC_BUDGET_LINES ',5);
2600 END IF;
2601 raise PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
2602 END IF;
2603 */
2604 /* bug 4865563: IPM Changes. Calling APIs which take care of
2605 * i. update the display_quantity new column in pa_budget_lines
2606 * ii. insert planning transaction records in the new entity pa_resource_asgn_curr
2607 * with the appropriate records.
2608 */
2609 PA_BUDGET_LINES_UTILS.populate_display_qty
2610 (p_budget_version_id => l_budget_ver_tbl(m),
2611 p_context => 'FINANCIAL',
2612 x_return_status => l_return_status);
2613
2614 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2615 THEN
2616 IF p_pa_debug_mode = 'Y' THEN
2617 pa_debug.write_file('Upgrade failed due to error in PA_BUDGET_LINES_UTILS.populate_display_quantity',5);
2618 END IF;
2619 raise PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
2620 END IF;
2621
2622 /* populating fp_cols_rec to call the new entity maintenace API */
2623 PA_FP_GEN_AMOUNT_UTILS.get_plan_version_dtls
2624 (p_budget_version_id => l_budget_ver_tbl(m),
2625 x_fp_cols_rec => l_fp_cols_rec_var,
2626 x_return_status => l_return_status,
2627 x_msg_count => l_msg_count,
2628 x_msg_data => l_msg_data);
2629
2630 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2631 THEN
2632 IF p_pa_debug_mode = 'Y' THEN
2633 pa_debug.write_file('Upgrade failed due to error in PA_FP_GEN_AMOUNT_UTILS.get_plan_version_dtls',5);
2634 END IF;
2635 raise PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
2636 END IF;
2637
2638 /* calling the maintenance api to insert data into the new planning transaction level table */
2639 PA_RES_ASG_CURRENCY_PUB.maintain_data
2640 (p_fp_cols_rec => l_fp_cols_rec_var,
2641 p_calling_module => 'UPGRADE',
2642 p_rollup_flag => 'Y',
2643 p_version_level_flag => 'Y',
2644 x_return_status => l_return_status,
2645 x_msg_count => l_msg_count,
2646 x_msg_data => l_msg_data);
2647
2648 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2649 THEN
2650 IF p_pa_debug_mode = 'Y' THEN
2651 pa_debug.write_file('Upgrade failed due to error in PA_RES_ASG_CURRENCY_PUB.maintain_data',5);
2652 END IF;
2653 raise PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
2654 END IF;
2655
2656 /* If there is no budget lines for some resource assignments of the current budget versions
2657 * then, the maintenance api would not create data in the new entity. In that scenario, we have
2658 * to insert those resource assignment with default applicable currency
2659 */
2660 PA_FIN_PLAN_PUB.create_default_plan_txn_rec
2661 (p_budget_version_id => l_budget_ver_tbl(m),
2662 p_calling_module => 'UPGRADE',
2663 x_return_status => l_return_status,
2664 x_msg_count => l_msg_count,
2665 x_msg_data => l_msg_data);
2666
2667 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2668 THEN
2669 IF p_pa_debug_mode = 'Y' THEN
2670 pa_debug.write_file('Upgrade failed due to error in PA_FIN_PLAN_PUB.create_default_plan_txn_rec',5);
2671 END IF;
2672 raise PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
2673 END IF;
2674 /* bug 4865563: ends */
2675
2676 PA_FP_ROLLUP_PKG.rollup_budget_version (
2677 p_budget_version_id => l_budget_ver_tbl(m)
2678 ,p_entire_version => 'Y'
2679 ,x_return_status => l_return_status
2680 ,x_msg_count => l_msg_count
2681 ,x_msg_data => l_msg_data );
2682
2683 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2684 THEN
2685 IF p_pa_debug_mode = 'Y' THEN
2686 pa_debug.write_file('Upgrade failed due to error in PA_FP_ROLLUP_PKG.rollup_budget_version',5);
2687 END IF;
2688 raise PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
2689 END IF;
2690
2691 END LOOP;
2692
2693 --Below code is added for bug 7187487
2694 --This is to create performance reporting data for the bv's.
2695 DECLARE
2696 l_budget_ver_tbl2 SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
2697 l_budget_ver_tbl3 SYSTEM.PA_NUM_TBL_TYPE := system.pa_num_tbl_type();
2698 i number;
2699 j number;
2700 k number;
2701 BEGIN
2702 j:=1;
2703 k:=1;
2704 for i in l_budget_ver_tbl.first..l_budget_ver_tbl.last loop
2705 if l_budget_status_code_tbl(i) = 'B' then
2706 l_budget_ver_tbl2.extend(1);
2707 l_budget_ver_tbl2(j) := l_budget_ver_tbl(i);
2708 j := j + 1;
2709 else
2710 l_budget_ver_tbl3.extend(1);
2711 l_budget_ver_tbl3(k) := l_budget_ver_tbl(i);
2712 k := k + 1;
2713 end if;
2714 end loop;
2715 -- End of Bug# 7187487
2716
2717
2718 IF p_pa_debug_mode = 'Y' THEN
2719 pa_debug.g_err_stage := 'Calling PJI Plan (Version) Create ';
2720 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2721 END IF;
2722
2723 PJI_FM_XBS_ACCUM_MAINT.PLAN_CREATE (
2724 p_fp_version_ids => l_budget_ver_tbl3, -- Modified to l_budget_ver_tbl3 for Bug# 7187487
2725 x_return_status => l_return_status,
2726 x_msg_code => l_msg_data);
2727
2728 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2729 IF p_pa_debug_mode = 'Y' THEN
2730 pa_debug.write_file('Upgrade failed due to error in PJI_FM_XBS_ACCUM_MAINT.PLAN_CREATE',5);
2731 END IF;
2732 raise PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
2733 END IF;
2734
2735 IF(l_budget_ver_tbl2.COUNT >0) THEN --Bug 8233686
2736 -- Added below for Bug# 7187487
2737 for i in l_budget_ver_tbl2.first..l_budget_ver_tbl2.last loop
2738 IF p_pa_debug_mode = 'Y' THEN
2739 pa_debug.g_err_stage := 'Calling PJI Plan (Version) Baseline ';
2740 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2741 END IF;
2742
2743 PJI_FM_XBS_ACCUM_MAINT.PLAN_BASELINE (
2744 p_baseline_version_id => l_budget_ver_tbl2(i),
2745 p_new_version_id => l_budget_ver_tbl2(i),
2746 x_return_status => l_return_status,
2747 x_msg_code => l_msg_data);
2748
2749 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2750 IF p_pa_debug_mode = 'Y' THEN
2751 pa_debug.write_file('Upgrade failed due to error in PJI_FM_XBS_ACCUM_MAINT.PLAN_baseline',5);
2752 END IF;
2753 raise PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
2754 END IF;
2755 end loop;
2756 END IF; --Bug 8233686
2757 END; -- end of creation of proj perf data bug 7187487
2758
2759
2760 END IF;--IF l_budget_ver_tbl.COUNT>0 THEN
2761
2762 --Bug 4171254. Corrected the criteria for exiting the loop. The loop should be exited whenever
2763 --l_budget_ver_tbl contains records less than the limit size.
2764 EXIT WHEN l_budget_ver_tbl.count < 200;
2765
2766 END LOOP;
2767 CLOSE budgets_for_upgrade_cur;
2768
2769
2770 IF p_pa_debug_mode = 'Y' THEN
2771 pa_debug.g_err_stage := 'Closed budgets_for_upgrade_cur ';
2772 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2773
2774 pa_debug.g_err_stage := 'Exiting Upgrade_Budget_Versions';
2775 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2776 END IF;
2777 pa_debug.reset_err_stack;
2778
2779 EXCEPTION
2780
2781 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2782
2783 IF budgets_for_upgrade_cur%ISOPEN THEN
2784 CLOSE budgets_for_upgrade_cur;
2785 END IF;
2786 l_msg_count := FND_MSG_PUB.count_msg;
2787 IF l_msg_count = 1 THEN
2788 PA_INTERFACE_UTILS_PUB.get_messages
2789 ( p_encoded => FND_API.G_TRUE
2790 ,p_msg_index => 1
2791 ,p_msg_count => l_msg_count
2792 ,p_msg_data => l_msg_data
2793 ,p_data => l_data
2794 ,p_msg_index_out => l_msg_index_out);
2795 x_msg_data := l_data;
2796 x_msg_count := l_msg_count;
2797 ELSE
2798 x_msg_count := l_msg_count;
2799 x_msg_data := l_msg_data;
2800 END IF;
2801 IF p_pa_debug_mode = 'Y' THEN
2802 pa_debug.g_err_stage:='Invalid Arguments Passed';
2803 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2804 pa_debug.write_file('Upgrade_Budget_Versions ' || x_msg_data,5);
2805 END IF;
2806
2807 x_return_status:= FND_API.G_RET_STS_ERROR;
2808 pa_debug.reset_err_stack;
2809 RAISE;
2810
2811 WHEN Others THEN
2812
2813 IF budgets_for_upgrade_cur%ISOPEN THEN
2814 CLOSE budgets_for_upgrade_cur;
2815 END IF;
2816 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2817 x_msg_count := 1;
2818 x_msg_data := SQLERRM;
2819 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_UPGRADE_PKG'
2820 ,p_procedure_name => 'Upgrade_Budget_Versions');
2821 IF p_pa_debug_mode = 'Y' THEN
2822 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
2823 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2824 pa_debug.write_file('Upgrade_Budget_Versions ' || pa_debug.G_Err_Stack,5);
2825 END IF;
2826 pa_debug.reset_err_stack;
2827 RAISE;
2828
2829 END Upgrade_Budget_Versions;
2830
2831 /*===============================================================================
2832 The follwing api is a table handler for the pa_fp_upgrade_audit table.
2833 ===============================================================================*/
2834 PROCEDURE Insert_Audit_Record(
2835 p_project_id IN PA_FP_UPGRADE_AUDIT.PROJECT_ID%TYPE
2836 ,p_budget_type_code IN PA_FP_UPGRADE_AUDIT.BUDGET_TYPE_CODE%TYPE
2837 ,p_proj_fp_options_id IN PA_FP_UPGRADE_AUDIT.PROJ_FP_OPTIONS_ID%TYPE
2838 ,p_fin_plan_option_level_code IN PA_FP_UPGRADE_AUDIT.FIN_PLAN_OPTION_LEVEL_CODE%TYPE
2839 ,p_basis_cost_version_id IN PA_FP_UPGRADE_AUDIT.BASIS_COST_VERSION_ID%TYPE
2840 ,p_basis_rev_version_id IN PA_FP_UPGRADE_AUDIT.BASIS_REV_VERSION_ID%TYPE
2841 ,p_basis_cost_bem IN PA_FP_UPGRADE_AUDIT.BASIS_COST_BEM%TYPE
2842 ,p_basis_rev_bem IN PA_FP_UPGRADE_AUDIT.BASIS_REV_BEM%TYPE
2843 ,p_upgraded_flag IN PA_FP_UPGRADE_AUDIT.UPGRADED_FLAG%TYPE
2844 ,p_failure_reason_code IN PA_FP_UPGRADE_AUDIT.FAILURE_REASON_CODE%TYPE
2845 ,p_proj_fp_options_id_rup IN PA_FP_UPGRADE_AUDIT.PROJ_FP_OPTIONS_ID%TYPE DEFAULT NULL) IS
2846
2847 BEGIN
2848
2849 INSERT INTO PA_FP_UPGRADE_AUDIT (
2850 PROJECT_ID
2851 ,BUDGET_TYPE_CODE
2852 ,PROJ_FP_OPTIONS_ID
2853 ,FIN_PLAN_OPTION_LEVEL_CODE
2854 ,BASIS_COST_VERSION_ID
2855 ,BASIS_REV_VERSION_ID
2856 ,BASIS_COST_BEM
2857 ,BASIS_REV_BEM
2858 ,REQUEST_ID
2859 ,UPGRADED_FLAG
2860 ,FAILURE_REASON_CODE
2861 ,LAST_UPDATE_DATE
2862 ,LAST_UPDATED_BY
2863 ,CREATION_DATE
2864 ,CREATED_BY
2865 ,LAST_UPDATE_LOGIN
2866 ,proj_fp_options_id_rup )
2867 VALUES(
2868 p_project_id
2869 ,p_budget_type_code
2870 ,p_proj_fp_options_id
2871 ,p_fin_plan_option_level_code
2872 ,p_basis_cost_version_id
2873 ,p_basis_rev_version_id
2874 ,p_basis_cost_bem
2875 ,p_basis_rev_bem
2876 ,fnd_global.conc_request_id
2877 ,p_upgraded_flag
2878 ,p_failure_reason_code
2879 ,sysdate
2880 ,fnd_global.user_id
2881 ,sysdate
2882 ,fnd_global.user_id
2883 ,fnd_global.login_id
2884 ,p_proj_fp_options_id_rup);
2885
2886 END Insert_Audit_Record;
2887
2888
2889 /*==================================================================
2890 This api would be called by the pre_upgrade process from the
2891 upgrade budgets report. The api would insert the exception records
2892 into pa_fp_upgrade_audit table and pa_fp_upgrade_exceptions_tmp as
2893 necessary.
2894 ==================================================================*/
2895
2896 PROCEDURE VALIDATE_BUDGETS (
2897 p_from_project_number IN VARCHAR2
2898 ,p_to_project_number IN VARCHAR2
2899 ,p_budget_types IN VARCHAR2
2900 ,p_budget_statuses IN VARCHAR2
2901 ,p_project_type IN VARCHAR2
2902 ,p_project_statuses IN VARCHAR2
2903 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2904 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2905 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2906 AS
2907
2908 l_msg_count NUMBER := 0;
2909 l_data VARCHAR2(2000);
2910 l_msg_data VARCHAR2(2000);
2911 l_msg_index_out NUMBER;
2912 l_return_status VARCHAR2(2000);
2913 l_debug_mode VARCHAR2(30);
2914
2915 l_validation_status VARCHAR2(30);
2916 l_project_id pa_projects.project_id%TYPE;
2917
2918 -- cursor written for bug 2853511
2919
2920 CURSOR attached_budget_types_cur(
2921 c_project_id IN pa_projects.project_id%TYPE
2922 ,c_budget_types IN VARCHAR2 ) IS
2923 SELECT bt.budget_type_code budget_type_code
2924 FROM pa_budget_types bt
2925 WHERE DECODE(c_budget_types,'ALL','Y', bt.upgrade_budget_type_flag) = 'Y'
2926 AND NVL(bt.plan_type,'BUDGET') = 'BUDGET'
2927 AND NOT EXISTS
2928 (SELECT 1
2929 FROM pa_proj_fp_options ppfo
2930 ,pa_fin_plan_types_b pt
2931 WHERE pt.migrated_frm_bdgt_typ_code = bt.budget_type_code
2932 AND ppfo.project_id = c_project_id
2933 AND ppfo.fin_plan_type_id = pt.fin_plan_type_id
2934 AND ppfo.fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE)
2935 AND EXISTS
2936 (SELECT 1
2937 FROM pa_budget_versions pbv
2938 WHERE pbv.project_id = c_project_id
2939 AND pbv.budget_type_code = bt.budget_type_code);
2940
2941 attached_budget_types_rec attached_budget_types_cur%ROWTYPE;
2942
2943 BEGIN
2944
2945 x_msg_count := 0;
2946 x_return_status := FND_API.G_RET_STS_SUCCESS;
2947 pa_debug.init_err_stack('PA_FP_UPGRADE_PKG.validate_budgets');
2948 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2949 l_debug_mode := NVL(l_debug_mode, 'Y');
2950 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
2951
2952 -- Check for business rules violations
2953
2954 IF p_pa_debug_mode = 'Y' THEN
2955 pa_debug.g_err_stage:= 'Validating input parameters';
2956 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2957 END IF;
2958
2959 IF (p_budget_types IS NULL) OR
2960 (p_budget_statuses IS NULL) OR
2961 (p_project_statuses IS NULL)
2962 THEN
2963 IF p_pa_debug_mode = 'Y' THEN
2964 pa_debug.g_err_stage := 'p_budget_types='||p_budget_types;
2965 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2966 pa_debug.g_err_stage := 'p_budget_statuses='||p_budget_statuses;
2967 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2968 pa_debug.g_err_stage := 'p_project_statuses='||p_project_statuses;
2969 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2970 END IF;
2971
2972 PA_UTILS.ADD_MESSAGE(p_app_short_name=> 'PA',
2973 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
2974 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2975
2976 END IF;
2977
2978 -- Fetch all the projects that are eligible for upgrade
2979
2980 IF ( p_from_project_number IS NOT NULL) AND ( p_to_project_number IS NOT NULL ) THEN
2981
2982 OPEN projects_for_upgrade_cur1(p_from_project_number,p_to_project_number,p_project_type,p_project_statuses);
2983
2984 ELSIF ( p_project_type IS NOT NULL) THEN
2985
2986 OPEN projects_for_upgrade_cur2(p_from_project_number,p_to_project_number,p_project_type,p_project_statuses);
2987
2988 ELSIF (p_project_statuses <> 'ALL') THEN
2989
2990 OPEN projects_for_upgrade_cur3(p_from_project_number,p_to_project_number,p_project_type,p_project_statuses);
2991 ELSE
2992 OPEN projects_for_upgrade_cur(p_from_project_number,p_to_project_number,p_project_type,p_project_statuses);
2993 END IF;
2994
2995
2996 LOOP
2997
2998 IF ( p_from_project_number IS NOT NULL) AND ( p_to_project_number IS NOT NULL ) THEN
2999
3000 FETCH projects_for_upgrade_cur1 INTO l_project_id;
3001 EXIT WHEN projects_for_upgrade_cur1%NOTFOUND;
3002
3003 ELSIF ( p_project_type IS NOT NULL) THEN
3004
3005 FETCH projects_for_upgrade_cur2 INTO l_project_id;
3006 EXIT WHEN projects_for_upgrade_cur2%NOTFOUND;
3007
3008 ELSIF (p_project_statuses <> 'ALL') THEN
3009
3010 FETCH projects_for_upgrade_cur3 INTO l_project_id;
3011 EXIT WHEN projects_for_upgrade_cur3%NOTFOUND;
3012
3013 ELSE
3014 FETCH projects_for_upgrade_cur INTO l_project_id;
3015 EXIT WHEN projects_for_upgrade_cur%NOTFOUND;
3016 END IF;
3017
3018
3019 --Check if any types of budgets are allowed for the project using project_type_info_cur.
3020
3021 IF p_pa_debug_mode = 'Y' THEN
3022 pa_debug.g_err_stage := 'l_project_id='||l_project_id;
3023 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3024 END IF;
3025
3026 OPEN project_type_info_cur(l_project_id);
3027 FETCH project_type_info_cur INTO project_type_info_rec;
3028 CLOSE project_type_info_cur;
3029
3030 IF (( project_type_info_rec.allow_cost_budget_entry_flag ='Y' )OR
3031 ( project_type_info_rec.allow_rev_budget_entry_flag = 'Y' )) AND
3032 ( NVL(project_type_info_rec.org_project_flag,'N') = 'N' ) -- bug :- 2788983 org_forecast project shouldn't be upgraded
3033 THEN
3034
3035 -- Perform project level validations necessary for UPGRADE
3036
3037 pa_fp_upgrade_pkg.Validate_Project(
3038 p_project_id => l_project_id
3039 ,x_validation_status => l_validation_status
3040 ,x_return_status => l_return_status
3041 ,x_msg_count => l_msg_count
3042 ,x_msg_data => l_msg_data);
3043
3044 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3045 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3046 END IF;
3047
3048 -- Fetch all the budget/plan types that have to be attached to the project
3049 -- during upgrade.
3050
3051 -- Changes for bug 2853511
3052 -- In PRE_UPGRADE mode, there needn't exist a corresponding plan_type for each
3053 -- budget type that has been chosen to be upgraded. So, we should use a different
3054 -- cursor to return all the budget_type_codes for a given project.
3055
3056 /*
3057 OPEN attached_plan_types_cur(l_project_id, p_budget_types);
3058 LOOP
3059 FETCH attached_plan_types_cur INTO attached_plan_types_rec;
3060 EXIT WHEN attached_plan_types_cur%NOTFOUND;
3061
3062 IF p_pa_debug_mode = 'Y' THEN
3063 pa_debug.g_err_stage := 'budget_type_code='||attached_plan_types_rec.budget_type_code;
3064 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3065 END IF;
3066
3067 -- For each budget type fetched check for all the exceptions
3068
3069 pa_fp_upgrade_pkg.Validate_Project_Plan_Type(
3070 p_project_id => l_project_id
3071 ,p_budget_type_code => attached_plan_types_rec.budget_type_code
3072 ,x_validation_status => l_validation_status
3073 ,x_return_status => l_return_status
3074 ,x_msg_count => l_msg_count
3075 ,x_msg_data => l_msg_data);
3076
3077 END LOOP;
3078 CLOSE attached_plan_types_cur;
3079 */
3080
3081 OPEN attached_budget_types_cur(l_project_id, p_budget_types);
3082 LOOP
3083 FETCH attached_budget_types_cur INTO attached_budget_types_rec;
3084 EXIT WHEN attached_budget_types_cur%NOTFOUND;
3085
3086 IF p_pa_debug_mode = 'Y' THEN
3087 pa_debug.g_err_stage := 'budget_type_code='||attached_budget_types_rec.budget_type_code;
3088 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3089 END IF;
3090
3091 -- For each budget type fetched check for all the exceptions
3092
3093 pa_fp_upgrade_pkg.Validate_Project_Plan_Type(
3094 p_project_id => l_project_id
3095 ,p_budget_type_code => attached_budget_types_rec.budget_type_code
3096 ,x_validation_status => l_validation_status
3097 ,x_return_status => l_return_status
3098 ,x_msg_count => l_msg_count
3099 ,x_msg_data => l_msg_data);
3100
3101 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3102 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3103 END IF;
3104
3105 END LOOP;
3106 CLOSE attached_budget_types_cur;
3107
3108
3109 -- Fetch all the budget versions of the current project that are eligible for upgrade
3110 /* For FP M: The pre upgrade report is not going to do anything. Retaining the code as it is
3111 (its just dummy processing) and would remove it later. */
3112 OPEN budgets_for_upgrade_cur(l_project_id,p_budget_types,p_budget_statuses,'PRE_UPGRADE');
3113 LOOP
3114 FETCH budgets_for_upgrade_cur INTO budgets_for_upgrade_rec;
3115 EXIT WHEN budgets_for_upgrade_cur%NOTFOUND;
3116
3117 IF p_pa_debug_mode = 'Y' THEN
3118 pa_debug.g_err_stage := 'budget_version_id='||budgets_for_upgrade_rec.budget_version_id;
3119 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3120 END IF;
3121
3122 -- Perform budget_version level validations necessary for UPGRADE
3123 -- The only validation that was done in this api was for mixed resource planning level.
3124 -- This is not applicable for FP M and hence for FP M this api doesnt do any validation.
3125
3126 pa_fp_upgrade_pkg.Validate_Budget_Version
3127 ( p_budget_version_id => budgets_for_upgrade_rec.budget_version_id
3128 ,x_return_status => l_return_status
3129 ,x_msg_count => l_msg_count
3130 ,x_msg_data => l_msg_data);
3131
3132 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3133 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3134 END IF;
3135 END LOOP;
3136 CLOSE budgets_for_upgrade_cur;
3137
3138 END IF; --if any types of budget are allowed for the project
3139
3140 END LOOP;
3141
3142 IF projects_for_upgrade_cur1%ISOPEN THEN
3143 CLOSE projects_for_upgrade_cur1;
3144 ELSIF projects_for_upgrade_cur2%ISOPEN THEN
3145 CLOSE projects_for_upgrade_cur2;
3146 ELSIF projects_for_upgrade_cur3%ISOPEN THEN
3147 CLOSE projects_for_upgrade_cur3;
3148 ELSE
3149 CLOSE projects_for_upgrade_cur;
3150 END IF;
3151
3152 IF p_pa_debug_mode = 'Y' THEN
3153 pa_debug.g_err_stage:= 'Exiting validate_budgets';
3154 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3155 END IF;
3156 pa_debug.reset_err_stack;
3157
3158 EXCEPTION
3159
3160 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3161
3162 x_return_status := FND_API.G_RET_STS_ERROR;
3163 l_msg_count := FND_MSG_PUB.count_msg;
3164 IF l_msg_count = 1 THEN
3165 PA_INTERFACE_UTILS_PUB.get_messages
3166 (p_encoded => FND_API.G_TRUE
3167 ,p_msg_index => 1
3168 ,p_msg_count => l_msg_count
3169 ,p_msg_data => l_msg_data
3170 ,p_data => l_data
3171 ,p_msg_index_out => l_msg_index_out);
3172 x_msg_data := l_data;
3173 x_msg_count := l_msg_count;
3174 ELSE
3175 x_msg_count := l_msg_count;
3176 END IF;
3177 x_msg_data := l_msg_data;
3178 IF p_pa_debug_mode = 'Y' THEN
3179 pa_debug.write_file('VALIDATE_BUDGETS ' || x_msg_data,5);
3180 END IF;
3181 pa_debug.reset_err_stack;
3182 RAISE;
3183
3184 WHEN others THEN
3185
3186 IF budgets_for_upgrade_cur%ISOPEN THEN
3187 CLOSE budgets_for_upgrade_cur;
3188 END IF;
3189 IF projects_for_upgrade_cur1%ISOPEN THEN
3190 CLOSE projects_for_upgrade_cur1;
3191 ELSIF projects_for_upgrade_cur2%ISOPEN THEN
3192 CLOSE projects_for_upgrade_cur2;
3193 ELSIF projects_for_upgrade_cur3%ISOPEN THEN
3194 CLOSE projects_for_upgrade_cur3;
3195 ELSIF projects_for_upgrade_cur%ISOPEN THEN
3196 CLOSE projects_for_upgrade_cur;
3197 END IF;
3198 -- start of changes for bug 2853511
3199 IF attached_budget_types_cur%ISOPEN THEN
3200 CLOSE attached_budget_types_cur;
3201 END IF;
3202 -- end of changes for bug 2853511
3203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3204 x_msg_count := 1;
3205 x_msg_data := SQLERRM;
3206 FND_MSG_PUB.add_exc_msg
3207 ( p_pkg_name => 'PA_FP_UPGRADE_PKG'
3208 ,p_procedure_name => 'validate_budgets'
3209 ,p_error_text => sqlerrm);
3210 IF p_pa_debug_mode = 'Y' THEN
3211 pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
3212 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3213 pa_debug.write_file('VALIDATE_BUDGETS ' || pa_debug.G_Err_Stack,5);
3214 END IF;
3215 pa_debug.reset_err_stack;
3216 RAISE;
3217
3218 END VALIDATE_BUDGETS;
3219
3220 /*==================================================================
3221 This api is used to do validations required at project level for
3222 upgrade. This api is called both in PRE_UPGRADE and UPGRADE modes.
3223
3224 Bug#2731534. Checking billing flag doesn't suffice the availablity
3225 of the conversion attributes for the project. It takes care of the
3226 revenue conversion attributes only. For cost attributes, if we do
3227 not find cost conversion attributes in pa_projects_all, then get
3228 them from the implementations table for the project's OU and if
3229 we do not find them there then raise exception for the project.
3230 ==================================================================*/
3231
3232 PROCEDURE VALIDATE_PROJECT (
3233 p_project_id IN pa_budget_versions.project_id%TYPE
3234 ,x_validation_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3235 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3236 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3237 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3238 AS
3239
3240 l_msg_count NUMBER := 0;
3241 l_data VARCHAR2(2000);
3242 l_msg_data VARCHAR2(2000);
3243 l_msg_index_out NUMBER;
3244 l_return_status VARCHAR2(2000);
3245 l_debug_mode VARCHAR2(30);
3246
3247
3248 l_multi_currency_billing_flag pa_projects_all.multi_currency_billing_flag%TYPE;
3249 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
3250 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
3251 l_project_bil_rate_type pa_projects_all.project_bil_rate_type%TYPE;
3252 l_projfunc_bil_rate_type pa_projects_all.projfunc_bil_rate_type%TYPE;
3253 l_project_cost_rate_type pa_projects_all.project_rate_type%TYPE;
3254 l_projfunc_cost_rate_type pa_projects_all.projfunc_cost_rate_type%TYPE;
3255
3256 BEGIN
3257
3258 x_msg_count := 0;
3259 x_return_status := FND_API.G_RET_STS_SUCCESS;
3260 pa_debug.set_err_stack('PA_FP_UPGRADE_PKG.VALIDATE_PROJECT');
3261 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3262 l_debug_mode := NVL(l_debug_mode, 'Y');
3263 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
3264
3265 -- Check for business rules violations
3266 IF p_pa_debug_mode = 'Y' THEN
3267 pa_debug.g_err_stage:= 'Validating input parameters';
3268 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3269 END IF;
3270
3271 --Check if plan version id is null
3272
3273 IF (p_project_id IS NULL)
3274 THEN
3275
3276 IF p_pa_debug_mode = 'Y' THEN
3277 pa_debug.g_err_stage:= 'p_project_id = '|| p_project_id;
3278 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3279 pa_debug.g_err_stage:= 'Invalid Arguments Passed';
3280 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3281 END IF;
3282
3283 PA_UTILS.ADD_MESSAGE
3284 (p_app_short_name => 'PA',
3285 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
3286 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3287 END IF;
3288
3289 -- Set x_validation_status to 'Y' initailly as and when we hit upona exception
3290 -- we update it to 'N'
3291
3292 x_validation_status := 'Y';
3293
3294 -- Fetch the project currencies, MCB flag and cost rate types
3295
3296 /* This api fetches the cost rate types from pa_projects_all table,
3297 if they aren't defined for project level then they are fetched from
3298 pa_implementations table */
3299
3300 PA_FIN_PLAN_UTILS.Get_Project_Curr_Attributes
3301 ( p_project_id => p_project_id
3302 ,x_multi_currency_billing_flag => l_multi_currency_billing_flag
3303 ,x_project_currency_code => l_project_currency_code
3304 ,x_projfunc_currency_code => l_projfunc_currency_code
3305 ,x_project_cost_rate_type => l_project_cost_rate_type
3306 ,x_projfunc_cost_rate_type => l_projfunc_cost_rate_type
3307 ,x_project_bil_rate_type => l_project_bil_rate_type
3308 ,x_projfunc_bil_rate_type => l_projfunc_bil_rate_type
3309 ,x_return_status => l_return_status
3310 ,x_msg_count => l_msg_count
3311 ,x_msg_data => l_msg_data );
3312
3313 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3314 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3315 END IF;
3316
3317 -- check if project currency and projfunc currency are not equal
3318 -- then we require conversion attributes at project level for upgrade
3319
3320 IF l_projfunc_currency_code <> l_project_currency_code AND
3321 (l_multi_currency_billing_flag <> 'Y' OR
3322 l_project_cost_rate_type IS NULL OR -- bug 2731534
3323 l_projfunc_cost_rate_type IS NULL) -- bug 2731534
3324 THEN
3325
3326 -- set x_validation_status to 'N'
3327
3328 x_validation_status := 'N';
3329
3330 -- Insert into audit table
3331
3332 pa_fp_upgrade_pkg.Insert_Audit_Record(
3333 p_project_id => p_project_id
3334 ,p_budget_type_code => NULL
3335 ,p_proj_fp_options_id => NULL
3336 ,p_fin_plan_option_level_code => PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT
3337 ,p_basis_cost_version_id => NULL
3338 ,p_basis_rev_version_id => NULL
3339 ,p_basis_cost_bem => NULL
3340 ,p_basis_rev_bem => NULL
3341 ,p_upgraded_flag => 'N'
3342 ,p_failure_reason_code => 'NO_CONV_ATTR_FOR_PROJ');
3343
3344 END IF;
3345 IF p_pa_debug_mode = 'Y' THEN
3346 pa_debug.g_err_stage:= 'Exiting validate_project';
3347 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3348 END IF;
3349 pa_debug.reset_err_stack;
3350
3351 EXCEPTION
3352
3353 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3354
3355 x_validation_status := 'N';
3356 x_return_status := FND_API.G_RET_STS_ERROR;
3357 l_msg_count := FND_MSG_PUB.count_msg;
3358 IF l_msg_count = 1 THEN
3359 PA_INTERFACE_UTILS_PUB.get_messages
3360 (p_encoded => FND_API.G_TRUE
3361 ,p_msg_index => 1
3362 ,p_msg_count => l_msg_count
3363 ,p_msg_data => l_msg_data
3364 ,p_data => l_data
3365 ,p_msg_index_out => l_msg_index_out);
3366 x_msg_data := l_data;
3367 x_msg_count := l_msg_count;
3368 ELSE
3369 x_msg_count := l_msg_count;
3370 x_msg_data := l_msg_data;
3371 END IF;
3372
3373 IF p_pa_debug_mode = 'Y' THEN
3374 pa_debug.write_file('VALIDATE_PROJECT ' || x_msg_data,5);
3375 END IF;
3376 pa_debug.reset_err_stack;
3377 RAISE;
3378
3379 WHEN others THEN
3380
3381 x_validation_status := 'N';
3382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3383 x_msg_count := 1;
3384 x_msg_data := SQLERRM;
3385 FND_MSG_PUB.add_exc_msg
3386 ( p_pkg_name => 'PA_FP_UPGRADE_PKG'
3387 ,p_procedure_name => 'VALIDATE_PROJECT'
3388 ,p_error_text => sqlerrm);
3389 IF p_pa_debug_mode = 'Y' THEN
3390 pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
3391 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3392 pa_debug.write_file('VALIDATE_PROJECT ' || pa_debug.G_Err_Stack,5);
3393 END IF;
3394 pa_debug.reset_err_stack;
3395 RAISE;
3396
3397 END VALIDATE_PROJECT;
3398
3399 /*==================================================================
3400 This api would be called both in 'PRE_UPGRADE' mode and 'UPGRADE'
3401 mode and does all the necesary business validations that are to be
3402 done at the budget type level.
3403 ==================================================================*/
3404
3405 PROCEDURE VALIDATE_PROJECT_PLAN_TYPE (
3406 p_project_id IN pa_budget_versions.project_id%TYPE
3407 ,p_budget_type_code IN pa_budget_versions.budget_type_code%TYPE
3408 ,x_validation_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3409 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3410 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3411 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3412 AS
3413
3414 l_msg_count NUMBER := 0;
3415 l_data VARCHAR2(2000);
3416 l_msg_data VARCHAR2(2000);
3417 l_msg_index_out NUMBER;
3418 l_return_status VARCHAR2(2000);
3419 l_debug_mode VARCHAR2(30);
3420 l_err_code NUMBER;
3421 l_err_stage VARCHAR2(2000);
3422 l_err_stack VARCHAR2(2000);
3423
3424 l_draft_version_id pa_budget_versions.budget_version_id%TYPE;
3425 l_budget_status_code pa_budget_versions.budget_status_code%TYPE;
3426
3427 ---------- Variables Used for get_budget_ctrl_options api --------------
3428 l_fck_req_flag VARCHAR2(1);
3429 l_bdgt_intg_flag VARCHAR2(1);
3430 l_bdgt_ver_id pa_budget_versions.budget_version_id%TYPE;
3431 l_encum_type_id pa_budgetary_control_options.encumbrance_type_id%TYPE;
3432 l_balance_type pa_budgetary_control_options.balance_type%TYPE ;
3433 ---------- Variables Used for get_budget_ctrl_options api --------------
3434
3435 BEGIN
3436
3437 x_msg_count := 0;
3438 x_return_status := FND_API.G_RET_STS_SUCCESS;
3439 pa_debug.set_err_stack('PA_FP_UPGRADE_PKG.VALIDATE_PROJECT_PLAN_TYPE');
3440 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3441 l_debug_mode := NVL(l_debug_mode, 'Y');
3442 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
3443
3444 -- Check for business rules violations
3445
3446 IF (p_project_id IS NULL) OR (p_budget_type_code IS NULL)
3447 THEN
3448
3449 IF p_pa_debug_mode = 'Y' THEN
3450 pa_debug.g_err_stage:= 'p_project_id = '|| p_project_id;
3451 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3452 pa_debug.g_err_stage:= 'p_budget_type_code = '|| p_budget_type_code;
3453 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3454 pa_debug.g_err_stage:= 'Invalid Arguments Passed';
3455 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3456 END IF;
3457
3458 PA_UTILS.ADD_MESSAGE
3459 (p_app_short_name => 'PA',
3460 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
3461
3462
3463 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3464 END IF;
3465
3466 -- Initially setting x_validation_status to yes.
3467 -- as and when we hit upon an error we set the x_validation_status to 'N'
3468 -- but we still proceed to report all the exceptions
3469
3470 x_validation_status := 'Y';
3471
3472 --Check if budetary controls exist for the budget type and project combination.
3473
3474 IF p_pa_debug_mode = 'Y' THEN
3475 pa_debug.g_err_stage := 'Calling get_budget_ctrl_options';
3476 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3477 END IF;
3478
3479 PA_BUDGET_FUND_PKG.get_budget_ctrl_options (
3480 p_project_id => p_project_id,
3481 p_budget_type_code => p_budget_type_code,
3482 p_calling_mode => 'BUDGET',
3483 x_fck_req_flag => l_fck_req_flag,
3484 x_bdgt_intg_flag => l_bdgt_intg_flag,
3485 x_bdgt_ver_id => l_bdgt_ver_id,
3486 x_encum_type_id => l_encum_type_id,
3487 x_balance_type => l_balance_type,
3488 x_return_status => l_return_status,
3489 x_msg_count => l_msg_count,
3490 x_msg_data => l_msg_data);
3491
3492 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3493 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3494 END IF;
3495
3496
3497 --Check if any budget version exists in submitted status having this budget type
3498
3499 IF NVL(l_bdgt_intg_flag,'Y') <> 'N' OR NVL(l_fck_req_flag,'Y')<> 'N' THEN -- Bug:- 2686836
3500
3501 -- Set x_validation_status to 'N' as the this budget type and
3502 -- all the budget versions can't be upgraded.
3503
3504 x_validation_status := 'N';
3505
3506 -- Insert the exception into audit table
3507
3508 pa_fp_upgrade_pkg.Insert_Audit_Record(
3509 p_project_id => p_project_id
3510 ,p_budget_type_code => p_budget_type_code
3511 ,p_proj_fp_options_id => NULL
3512 ,p_fin_plan_option_level_code => PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
3513 ,p_basis_cost_version_id => NULL
3514 ,p_basis_rev_version_id => NULL
3515 ,p_basis_cost_bem => NULL
3516 ,p_basis_rev_bem => NULL
3517 ,p_upgraded_flag => 'N'
3518 ,p_failure_reason_code => 'BUDGET_INTEGRATION_EXISTS');
3519 END IF;
3520
3521 -- Check if for the budget type if any of the budget versions to be upgraded is in
3522 -- submitted status. if so don't upgrade.
3523 IF p_pa_debug_mode = 'Y' THEN
3524 pa_debug.g_err_stage := 'Calling get_draft_version_id';
3525 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3526 END IF;
3527
3528 l_draft_version_id := Null;
3529
3530 pa_budget_utils.get_draft_version_id (
3531 x_project_id => p_project_id
3532 ,x_budget_type_code => p_budget_type_code
3533 ,x_budget_version_id => l_draft_version_id
3534 ,x_err_code => l_err_code
3535 ,x_err_stage => l_err_stage
3536 ,x_err_stack => l_err_stack);
3537
3538 -- bug 2853511 draft version id could be deleted after baselining the draft version
3539 -- and thus draft version needn't exist
3540
3541 IF l_draft_version_id IS NOT NULL THEN
3542 IF p_pa_debug_mode = 'Y' THEN
3543 pa_debug.g_err_stage := 'draft_version_id = '|| l_draft_version_id;
3544 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3545 END IF;
3546
3547 -- The draft version id fetched may be either working or submitted version
3548 -- If the draft version is in submitted status this budget type can't be upgraded
3549
3550 BEGIN
3551 SELECT budget_status_code
3552 INTO l_budget_status_code
3553 FROM pa_budget_versions
3554 WHERE budget_version_id = l_draft_version_id;
3555 EXCEPTION
3556 WHEN OTHERS THEN
3557 IF attached_plan_types_cur%ISOPEN THEN
3558 CLOSE attached_plan_types_cur;
3559 END IF;
3560 IF p_pa_debug_mode = 'Y' THEN
3561 pa_debug.g_err_stage:='draft_version_id is null or invalid'||SQLERRM;
3562 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3563 END IF;
3564 RAISE;
3565 END;
3566
3567 IF l_budget_status_code = PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_SUBMITTED THEN
3568
3569 -- Set x_validation_status to 'N' as the this budget type and
3570 -- all the budget versions can't be upgraded.
3571
3572 x_validation_status := 'N';
3573
3574 -- Insert into audit table
3575 pa_fp_upgrade_pkg.Insert_Audit_Record(
3576 p_project_id => p_project_id
3577 ,p_budget_type_code => p_budget_type_code
3578 ,p_proj_fp_options_id => NULL
3579 ,p_fin_plan_option_level_code => PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
3580 ,p_basis_cost_version_id => NULL
3581 ,p_basis_rev_version_id => NULL
3582 ,p_basis_cost_bem => NULL
3583 ,p_basis_rev_bem => NULL
3584 ,p_upgraded_flag => 'N'
3585 ,p_failure_reason_code => 'SUBMIT_STATUS_VERSION_EXISTS');
3586 END IF;
3587 END IF;
3588
3589 IF p_pa_debug_mode = 'Y' THEN
3590 pa_debug.g_err_stage:= 'Exiting VALIDATE_PROJECT_PLAN_TYPE';
3591 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3592 END IF;
3593 pa_debug.reset_err_stack;
3594
3595 EXCEPTION
3596
3597 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3598
3599 x_validation_status := 'N';
3600 x_return_status := FND_API.G_RET_STS_ERROR;
3601 l_msg_count := FND_MSG_PUB.count_msg;
3602 IF l_msg_count = 1 THEN
3603 PA_INTERFACE_UTILS_PUB.get_messages
3604 (p_encoded => FND_API.G_TRUE
3605 ,p_msg_index => 1
3606 ,p_msg_count => l_msg_count
3607 ,p_msg_data => l_msg_data
3608 ,p_data => l_data
3609 ,p_msg_index_out => l_msg_index_out);
3610 x_msg_data := l_data;
3611 x_msg_count := l_msg_count;
3612 ELSE
3613 x_msg_count := l_msg_count;
3614 x_msg_data := l_msg_data;
3615 END IF;
3616
3617 IF p_pa_debug_mode = 'Y' THEN
3618 pa_debug.write_file('VALIDATE_PROJECT_PLAN_TYPE ' || x_msg_data,5);
3619 END IF;
3620 pa_debug.reset_err_stack;
3621 RAISE;
3622
3623 WHEN others THEN
3624
3625 x_validation_status := 'N';
3626 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3627 x_msg_count := 1;
3628 x_msg_data := SQLERRM;
3629 FND_MSG_PUB.add_exc_msg
3630 ( p_pkg_name => 'PA_FP_UPGRADE_PKG'
3631 ,p_procedure_name => 'VALIDATE_PROJECT_PLAN_TYPE'
3632 ,p_error_text => sqlerrm);
3633 IF p_pa_debug_mode = 'Y' THEN
3634 pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
3635 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3636 pa_debug.write_file('VALIDATE_PROJECT_PLAN_TYPE ' || pa_debug.G_Err_Stack,5);
3637 END IF;
3638 pa_debug.reset_err_stack;
3639 RAISE;
3640
3641 END VALIDATE_PROJECT_PLAN_TYPE;
3642
3643 /*==================================================================
3644 This api is used to validate a budget version in pre_upgrade mode.
3645 The api reports all the tasks along with different resource
3646 groups ,if the task id is planned both at resource level and
3647 resource group level(referred to as 'mixed planning level').
3648
3649 1.0)In the api, for each task we cache all the resource groups
3650 planned for along with the planning level in pl/sql tables
3651 i)if for the task mixed planning level exists then they are
3652 written to PA_FP_UPG_EXCEPTIONS_TMP table for reporting
3653 puposes.
3654 ii)else we flush the plsql tables and move to next task.
3655 ====================================================================*/
3656
3657 PROCEDURE VALIDATE_BUDGET_VERSION
3658 ( p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
3659 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3660 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3661 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3662 AS
3663
3664 l_msg_count NUMBER := 0;
3665 l_data VARCHAR2(2000);
3666 l_msg_data VARCHAR2(2000);
3667 l_msg_index_out NUMBER;
3668 l_return_status VARCHAR2(2000);
3669 l_debug_mode VARCHAR2(30);
3670
3671 l_task_id pa_tasks.task_id%TYPE;
3672
3673 CURSOR budget_version_info_cur
3674 (c_budget_version_id pa_budget_versions.budget_version_id%TYPE) IS
3675 SELECT project_id,
3676 budget_type_code,
3677 resource_list_id
3678 FROM pa_budget_versions
3679 WHERE budget_Version_id = c_budget_version_id;
3680
3681 budget_version_info_rec budget_version_info_cur%ROWTYPE;
3682
3683 BEGIN
3684
3685 x_msg_count := 0;
3686 x_return_status := FND_API.G_RET_STS_SUCCESS;
3687 pa_debug.set_err_stack('PA_FP_UPGRADE_PKG.validate_budget_version');
3688 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3689 l_debug_mode := NVL(l_debug_mode, 'Y');
3690 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
3691
3692 -- Check for business rules violations
3693 IF p_pa_debug_mode = 'Y' THEN
3694 pa_debug.g_err_stage:= 'Validating input parameters';
3695 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3696 END IF;
3697
3698 --Check if plan version id is null
3699
3700 IF (p_budget_version_id IS NULL)
3701 THEN
3702 IF p_pa_debug_mode = 'Y' THEN
3703 pa_debug.g_err_stage:= 'p_budget_version_id = '|| p_budget_version_id;
3704 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3705 pa_debug.g_err_stage:= 'Invalid Arguments Passed';
3706 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3707 END IF;
3708
3709 PA_UTILS.ADD_MESSAGE
3710 (p_app_short_name => 'PA',
3711 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
3712 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3713 END IF;
3714
3715 OPEN budget_version_info_cur(p_budget_version_id);
3716 FETCH budget_version_info_cur INTO budget_version_info_rec;
3717 CLOSE budget_version_info_cur;
3718
3719 IF p_pa_debug_mode = 'Y' THEN
3720 pa_debug.g_err_stage:= 'Exiting validate_budget_version';
3721 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3722 END IF;
3723 pa_debug.reset_err_stack;
3724
3725 EXCEPTION
3726
3727 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3728 x_return_status := FND_API.G_RET_STS_ERROR;
3729 l_msg_count := FND_MSG_PUB.count_msg;
3730 IF l_msg_count = 1 THEN
3731 PA_INTERFACE_UTILS_PUB.get_messages
3732 (p_encoded => FND_API.G_TRUE
3733 ,p_msg_index => 1
3734 ,p_msg_count => l_msg_count
3735 ,p_msg_data => l_msg_data
3736 ,p_data => l_data
3737 ,p_msg_index_out => l_msg_index_out);
3738 x_msg_data := l_data;
3739 x_msg_count := l_msg_count;
3740 ELSE
3741 x_msg_count := l_msg_count;
3742 x_msg_data := l_msg_data;
3743 END IF;
3744
3745 IF p_pa_debug_mode = 'Y' THEN
3746 pa_debug.write_file('VALIDATE_BUDGET_VERSION ' || x_msg_data,5);
3747 END IF;
3748 pa_debug.reset_err_stack;
3749 RAISE;
3750
3751 WHEN others THEN
3752
3753 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3754 x_msg_count := 1;
3755 x_msg_data := SQLERRM;
3756 FND_MSG_PUB.add_exc_msg
3757 ( p_pkg_name => 'PA_FP_UPGRADE_PKG'
3758 ,p_procedure_name => 'VALIDATE_BUDGET_VERSION'
3759 ,p_error_text => SQLERRM);
3760 IF p_pa_debug_mode = 'Y' THEN
3761 pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
3762 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3763 pa_debug.write_file('VALIDATE_BUDGET_VERSION ' || pa_debug.G_Err_Stack,5);
3764 END IF;
3765 pa_debug.reset_err_stack;
3766 RAISE;
3767
3768 END VALIDATE_BUDGET_VERSION;
3769
3770 --This procedure will upgrade the budget lines of a budget version so that all the amount/quantity columns
3771 --are populated. Please refer to the bug to see more discussion on this matter
3772
3773 --ASSUMPTIONS
3774 --1.Input is ordered by resource assignment id ,quantities with NULLS coming first
3775 --2.0(Zero)s are passed as input for amounts instead of NULL.
3776 PROCEDURE Apply_Calculate_FPM_Rules
3777 ( p_preference_code IN pa_proj_fp_options.fin_plan_preference_code%TYPE
3778 ,p_resource_assignment_id_tbl IN SYSTEM.pa_num_tbl_type
3779 ,p_rate_based_flag_tbl IN SYSTEM.pa_varchar2_1_tbl_type
3780 ,p_quantity_tbl IN SYSTEM.pa_num_tbl_type
3781 ,p_txn_raw_cost_tbl IN SYSTEM.pa_num_tbl_type
3782 ,p_txn_burdened_cost_tbl IN SYSTEM.pa_num_tbl_type
3783 ,p_txn_revenue_tbl IN SYSTEM.pa_num_tbl_type
3784 ,p_calling_module IN VARCHAR2 -- bug 5007734
3785 ,x_quantity_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type --File.Sql.39 bug 4440895
3786 ,x_txn_raw_cost_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type --File.Sql.39 bug 4440895
3787 ,x_txn_burdened_cost_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type --File.Sql.39 bug 4440895
3788 ,x_txn_revenue_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type --File.Sql.39 bug 4440895
3789 ,x_raw_cost_override_rate_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type --File.Sql.39 bug 4440895
3790 ,x_burd_cost_override_rate_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type --File.Sql.39 bug 4440895
3791 ,x_bill_override_rate_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type --File.Sql.39 bug 4440895
3792 ,x_non_rb_ra_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type --File.Sql.39 bug 4440895
3793 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3794 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3795 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3796 IS
3797
3798 --Start of variables used for debugging
3799 l_return_status VARCHAR2(1);
3800 l_msg_count NUMBER := 0;
3801 l_msg_data VARCHAR2(2000);
3802 l_data VARCHAR2(2000);
3803 l_msg_index_out NUMBER;
3804 l_debug_mode VARCHAR2(30);
3805 l_module_name VARCHAR2(200) := 'PAFPUPGB.Apply_Calculate_FPM_Rules';
3806
3807 --Stores previous non rate based resource assignment id
3808 l_prev_non_rb_ra_id pa_resource_assignments.resource_assignment_id%TYPE;
3809
3810 --Processing will be done in the following local variables
3811 l_quantity_tab SYSTEM.pa_num_tbl_type;
3812 l_txn_raw_cost_tab SYSTEM.pa_num_tbl_type;
3813 l_txn_burdened_cost_tab SYSTEM.pa_num_tbl_type;
3814 l_txn_revenue_tab SYSTEM.pa_num_tbl_type;
3815 l_cost_rate_override_tab SYSTEM.pa_num_tbl_type;
3816 l_burden_rate_override_tab SYSTEM.pa_num_tbl_type;
3817 l_bill_rate_override_tab SYSTEM.pa_num_tbl_type;
3818
3819 l_stage VARCHAR2(100);
3820
3821 BEGIN
3822
3823 x_msg_count := 0;
3824 x_return_status := FND_API.G_RET_STS_SUCCESS;
3825 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3826 l_debug_mode := NVL(l_debug_mode, 'Y');
3827
3828 -- Set curr function
3829 pa_debug.set_curr_function(
3830 p_function =>'PAFPUPGB.Apply_Calculate_FPM_Rules'
3831 ,p_debug_mode => l_debug_mode );
3832
3833 IF l_debug_mode = 'Y' THEN
3834 pa_debug.g_err_stage:='Validating input parameters';
3835 pa_debug.write( l_module_name,pa_debug.g_err_stage,3);
3836 END IF;
3837
3838 IF NVL(p_preference_code,'-99') NOT IN ('COST_ONLY','REVENUE_ONLY','COST_AND_REV_SAME') OR
3839 p_quantity_tbl.COUNT <> p_resource_assignment_id_tbl.COUNT OR
3840 p_quantity_tbl.COUNT <> p_rate_based_flag_tbl.COUNT OR
3841 p_quantity_tbl.COUNT <> p_txn_raw_cost_tbl.COUNT OR
3842 p_quantity_tbl.COUNT <> p_txn_burdened_cost_tbl.COUNT OR
3843 p_quantity_tbl.COUNT <> p_txn_revenue_tbl.COUNT THEN
3844
3845 IF l_debug_mode = 'Y' THEN
3846 pa_debug.g_err_stage:='p_preference_code is '||p_preference_code;
3847 pa_debug.write( l_module_name,pa_debug.g_err_stage,5);
3848
3849 pa_debug.g_err_stage:='p_quantity_tbl.COUNT is '||p_quantity_tbl.COUNT;
3850 pa_debug.write( l_module_name,pa_debug.g_err_stage,5);
3851
3852 pa_debug.g_err_stage:='p_txn_raw_cost_tbl.COUNT is '||p_txn_raw_cost_tbl.COUNT;
3853 pa_debug.write( l_module_name,pa_debug.g_err_stage,5);
3854
3855 pa_debug.g_err_stage:='p_txn_burdened_cost_tbl.COUNT is '||p_txn_burdened_cost_tbl.COUNT;
3856 pa_debug.write( l_module_name,pa_debug.g_err_stage,5);
3857
3858 pa_debug.g_err_stage:='p_txn_revenue_tbl.COUNT is '||p_txn_revenue_tbl.COUNT;
3859 pa_debug.write( l_module_name,pa_debug.g_err_stage,5);
3860
3861 END IF;
3862
3863 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3864 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
3865
3866 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3867
3868 END IF;
3869
3870 IF p_quantity_tbl.COUNT=0 THEN
3871
3872 IF l_debug_mode = 'Y' THEN
3873 pa_debug.g_err_stage:='Quantity Table is empty -> returning';
3874 pa_debug.write( l_module_name,pa_debug.g_err_stage,3);
3875 END IF;
3876 pa_debug.reset_curr_function;
3877 RETURN;
3878
3879 END IF;
3880
3881 --Prepare amount tbls for processing
3882 l_quantity_tab := p_quantity_tbl;
3883 l_txn_raw_cost_tab := p_txn_raw_cost_tbl;
3884 l_txn_burdened_cost_tab := p_txn_burdened_cost_tbl;
3885 l_txn_revenue_tab := p_txn_revenue_tbl;
3886 l_cost_rate_override_tab := SYSTEM.pa_num_tbl_type();
3887 l_burden_rate_override_tab := SYSTEM.pa_num_tbl_type();
3888 l_bill_rate_override_tab := SYSTEM.pa_num_tbl_type();
3889 l_cost_rate_override_tab.extend(p_quantity_tbl.COUNT);
3890 l_burden_rate_override_tab.extend(p_quantity_tbl.COUNT);
3891 l_bill_rate_override_tab.extend(p_quantity_tbl.COUNT);
3892
3893 --Prepare the tbl that holds the RAs for which rate based flag should be changed.
3894 x_non_rb_ra_id_tbl := SYSTEM.pa_num_tbl_type();
3895
3896
3897 FOR i IN l_quantity_tab.first .. l_quantity_tab.last LOOP
3898
3899 /* check if planning resource is rate based and quantity does not exists
3900 * then mark the planning resource as non-rate based and change the
3901 * UOM as Currency
3902 */
3903 IF (p_rate_based_flag_tbl(i) = 'Y' AND l_quantity_tab(i) = 0) THEN
3904
3905 IF l_prev_non_rb_ra_id IS NULL OR (l_prev_non_rb_ra_id <> p_resource_assignment_id_tbl(i)) THEN
3906
3907 l_stage := 'This is rate based resource quantity doesnot exists';
3908 x_non_rb_ra_id_tbl.extend;
3909 x_non_rb_ra_id_tbl(x_non_rb_ra_id_tbl.COUNT) := p_resource_assignment_id_tbl(i);
3910 l_prev_non_rb_ra_id := p_resource_assignment_id_tbl(i);
3911
3912 END IF;
3913
3914 END IF;
3915
3916 IF p_preference_code = 'COST_ONLY' THEN
3917
3918 l_txn_revenue_tab(i) := NULL;
3919 l_bill_rate_override_tab(i) := NULL;
3920
3921
3922 --this portion will check quantity is zero and amounts are null/zero
3923 IF l_quantity_tab(i) = 0 THEN
3924
3925 If (Nvl(l_txn_burdened_cost_tab(i),0) <> 0 and
3926 nvl(l_txn_raw_cost_tab(i),0) <> 0 ) Then
3927
3928 l_stage := 'PRC:1';
3929 l_quantity_tab(i) := l_txn_raw_cost_tab(i);
3930 l_cost_rate_override_tab(i) := l_txn_raw_cost_tab(i)/l_quantity_tab(i);
3931 l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
3932
3933 Elsif (Nvl(l_txn_burdened_cost_tab(i),0) <> 0 and
3934 nvl(l_txn_raw_cost_tab(i),0) = 0 ) Then
3935
3936 l_stage := 'PRC:2';
3937 l_quantity_tab(i) := l_txn_burdened_cost_tab(i);
3938 l_txn_raw_cost_tab(i) := l_txn_burdened_cost_tab(i);
3939 l_cost_rate_override_tab(i) := l_txn_raw_cost_tab(i)/l_quantity_tab(i);
3940 l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
3941
3942 Elsif (Nvl(l_txn_burdened_cost_tab(i),0) = 0
3943 and nvl(l_txn_raw_cost_tab(i),0) <> 0 ) Then
3944
3945 l_stage := 'PRC:3';
3946 l_quantity_tab(i) := l_txn_raw_cost_tab(i);
3947 --l_txn_burdened_cost_tab(i) := l_txn_raw_cost_tab(i);
3948 l_cost_rate_override_tab(i) := l_txn_raw_cost_tab(i)/l_quantity_tab(i);
3949 --l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
3950
3951 End If;
3952 -- this portion of code checks quantity not zero and amounts are zero
3953 Else
3954 If p_rate_based_flag_tbl(i) = 'N' OR
3955 l_prev_non_rb_ra_id = p_resource_assignment_id_tbl(i) Then -- added for bug 5007734:
3956
3957 -- bug 5007734: Making quantity = amounts only for upgrade flow, for the planning transactions
3958 -- which are non rate based or going to be made non rate based.
3959 IF p_calling_module = 'UPGRADE' THEN
3960
3961 If (l_txn_raw_cost_tab(i) <> 0
3962 and l_txn_raw_cost_tab(i) <> l_quantity_tab(i)) Then
3963
3964 l_stage := 'PRC:4';
3965 l_quantity_tab(i) := l_txn_raw_cost_tab(i);
3966 l_cost_rate_override_tab(i) := 1;
3967
3968 Else /* if (l_txn_raw_cost_tab(i) = 0 and l_txn_raw_cost_tab(i) <> l_quantity_tab(i))
3969 or
3970 (txn_raw_cost = quantity and txn_raw_cost <> 0) Then */
3971
3972 l_stage := 'PRC:5'; --Bug 5076350
3973 If (l_txn_raw_cost_tab(i) = l_quantity_tab(i) and nvl(l_txn_raw_cost_tab(i),0) <> 0) Then
3974 l_cost_rate_override_tab(i) := 1;
3975 Elsif nvl(l_txn_burdened_cost_tab(i),0) <> 0 Then
3976 l_txn_raw_cost_tab(i) := l_quantity_tab(i);
3977 l_cost_rate_override_tab(i) := 1;
3978 End if;
3979 End If;
3980
3981 If nvl(l_txn_burdened_cost_tab(i),0) <> 0 Then
3982
3983 l_stage := 'PRC:6';
3984 l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
3985
3986 Else
3987
3988 l_stage := 'PRC:7';
3989 --l_txn_burdened_cost_tab(i) := l_txn_raw_cost_tab(i);
3990 --l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
3991
3992 End If;
3993 END IF; -- end of bug 5007734
3994 Else --Rate Based Flag ='Y'
3995
3996 If l_txn_raw_cost_tab(i) <> 0 Then
3997
3998 l_stage := 'PRC:8';
3999 l_cost_rate_override_tab(i) := l_txn_raw_cost_tab(i)/l_quantity_tab(i);
4000
4001 Else
4002
4003 l_stage := 'PRC:9';
4004 --l_txn_raw_cost_tab(i) := l_quantity_tab(i);
4005 --l_cost_rate_override_tab(i) := l_txn_raw_cost_tab(i)/l_quantity_tab(i);
4006
4007 End If;
4008
4009 If nvl(l_txn_burdened_cost_tab(i),0) <> 0 Then
4010
4011 l_stage := 'PRC:10';
4012 l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
4013
4014 Else
4015
4016 l_stage := 'PRC:11';
4017 --l_txn_burdened_cost_tab(i) := l_txn_raw_cost_tab(i);
4018 --l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
4019
4020 End If;
4021
4022 End If; --If p_rate_based_flag_tbl(i) = 'N' Then
4023
4024 End If; --IF l_quantity_tab(i) = 0 THEN
4025
4026 Elsif p_preference_code = 'REVENUE_ONLY' Then
4027
4028 l_txn_raw_cost_tab(i) := NULL;
4029 l_txn_burdened_cost_tab(i) := NULL;
4030 l_cost_rate_override_tab(i) := NULL;
4031 l_burden_rate_override_tab(i) := NULL;
4032
4033 If l_quantity_tab(i) = 0 then
4034
4035 l_stage := 'PRC:12';
4036 If (nvl(l_txn_revenue_tab(i),0) <> 0 ) Then
4037 l_stage := 'PRC:13';
4038 l_quantity_tab(i) := l_txn_revenue_tab(i);
4039 l_bill_rate_override_tab(i) := 1;
4040 End If;
4041
4042 Else
4043
4044 If p_rate_based_flag_tbl(i) = 'N' OR
4045 l_prev_non_rb_ra_id = p_resource_assignment_id_tbl(i) Then -- added for bug 5007734:
4046 -- bug 5007734: Making quantity = amounts only for upgrade flow, for the planning transactions
4047 -- which are non rate based or going to be made non rate based.
4048 IF p_calling_module = 'UPGRADE' THEN
4049
4050 If (nvl(l_txn_revenue_tab(i),0) <> 0
4051 and l_txn_revenue_tab(i) <> l_quantity_tab(i)) Then
4052
4053 l_stage := 'PRC:14';
4054 l_quantity_tab(i) := l_txn_revenue_tab(i);
4055 l_bill_rate_override_tab(i) := 1;
4056
4057 Else /* if nvl(l_txn_revenue_tab(i),0) = 0 or quantity = revenue Then */
4058
4059 l_stage := 'PRC:15';
4060 l_txn_revenue_tab(i) := l_quantity_tab(i);
4061 l_bill_rate_override_tab(i) := 1;
4062
4063 End If;
4064 END IF; -- end of bug 5007734
4065
4066 Else-- Rate Based RA
4067
4068 If nvl(l_txn_revenue_tab(i),0) <> 0 Then
4069
4070 l_stage := 'PRC:16';
4071 l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4072
4073 Else
4074
4075 l_stage := 'PRC:17';
4076 --l_txn_revenue_tab(i) := l_quantity_tab(i);
4077 --l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4078
4079 End If;
4080
4081 End If;--If p_rate_based_flag_tbl(i) = 'N' Then
4082
4083 End If; --If l_quantity_tab(i) = 0 then
4084
4085 Elsif p_preference_code = 'COST_AND_REV_SAME' then
4086
4087 If l_quantity_tab(i) = 0 then
4088
4089 l_stage := 'PRC:18';
4090 If (Nvl(l_txn_burdened_cost_tab(i),0) <> 0
4091 and nvl(l_txn_raw_cost_tab(i),0) <> 0 ) Then --{
4092
4093 l_stage := 'PRC:19';
4094 l_quantity_tab(i) := l_txn_raw_cost_tab(i);
4095 l_cost_rate_override_tab(i) := l_txn_raw_cost_tab(i)/l_quantity_tab(i);
4096 l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
4097
4098 If nvl(l_txn_revenue_tab(i),0) <> 0 Then
4099
4100 l_stage := 'PRC:21';
4101 l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4102
4103 Else
4104
4105 l_stage := 'PRC:22';
4106 --l_txn_revenue_tab(i) := l_txn_burdened_cost_tab(i);
4107 --l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4108
4109 End If;
4110
4111 Elsif (Nvl(l_txn_burdened_cost_tab(i),0) <> 0
4112 and nvl(l_txn_raw_cost_tab(i),0) = 0 ) Then
4113
4114 l_stage := 'PRC:20';
4115 l_quantity_tab(i) := l_txn_burdened_cost_tab(i);
4116 --l_txn_raw_cost_tab(i) := l_txn_burdened_cost_tab(i);
4117 --l_cost_rate_override_tab(i) := l_txn_raw_cost_tab(i)/l_quantity_tab(i);
4118 l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
4119
4120 If nvl(l_txn_revenue_tab(i),0) <> 0 Then
4121
4122 l_stage := 'PRC:21';
4123 l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4124
4125 Else
4126
4127 l_stage := 'PRC:22';
4128 --l_txn_revenue_tab(i) := l_txn_burdened_cost_tab(i);
4129 --l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4130
4131 End If;
4132
4133 Elsif (Nvl(l_txn_burdened_cost_tab(i),0) = 0
4134 and nvl(l_txn_raw_cost_tab(i),0) <> 0 ) Then
4135
4136 l_stage := 'PRC:23';
4137 l_quantity_tab(i) := l_txn_raw_cost_tab(i);
4138 --l_txn_burdened_cost_tab(i) := l_txn_raw_cost_tab(i);
4139 l_cost_rate_override_tab(i) := l_txn_raw_cost_tab(i)/l_quantity_tab(i);
4140 --l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
4141
4142 If nvl(l_txn_revenue_tab(i),0) <> 0 Then
4143
4144 l_stage := 'PRC:24';
4145 l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4146
4147 Else
4148
4149 l_stage := 'PRC:25';
4150 --l_txn_revenue_tab(i) := l_txn_raw_cost_tab(i);
4151 --l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4152
4153 End If;
4154
4155 Elsif (Nvl(l_txn_burdened_cost_tab(i),0) = 0
4156 and nvl(l_txn_raw_cost_tab(i),0) = 0
4157 and nvl(l_txn_revenue_tab(i),0) <> 0 ) Then
4158
4159 l_stage := 'PRC:26';
4160 /* Bug 4865563: IPM Business Rule, if only revenue is present, don't copy it to anything. */
4161 l_quantity_tab(i) := l_txn_revenue_tab(i);
4162 --l_txn_raw_cost_tab(i) := l_txn_revenue_tab(i);
4163 --l_txn_burdened_cost_tab(i) := l_txn_raw_cost_tab(i);
4164 --l_cost_rate_override_tab(i) := l_txn_raw_cost_tab(i)/l_quantity_tab(i);
4165 --l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
4166 l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i); /* bug 5006029 */
4167
4168 End If; --}
4169
4170 Else -- quantity not equal to zero
4171
4172 If p_rate_based_flag_tbl(i) = 'N' OR
4173 l_prev_non_rb_ra_id = p_resource_assignment_id_tbl(i) Then -- added for bug 5007734:
4174 -- bug 5007734: Making quantity = amounts only for upgrade flow, for the planning transactions
4175 -- which are non rate based or going to be made non rate based.
4176 IF p_calling_module = 'UPGRADE' THEN
4177
4178 If (nvl(l_txn_raw_cost_tab(i),0) <> 0
4179 and l_quantity_tab(i) <> l_txn_raw_cost_tab(i)) Then
4180
4181 l_stage := 'PRC:27';
4182 l_quantity_tab(i) := l_txn_raw_cost_tab(i);
4183 l_cost_rate_override_tab(i) := 1;
4184
4185 Else /* if nvl(l_txn_raw_cost_tab(i),0) = 0 or l_quantity_tab(i) = l_txn_raw_cost_tab(i) */
4186
4187 l_stage := 'PRC:28';--Bug 5076350
4188 If (l_txn_raw_cost_tab(i) = l_quantity_tab(i) and nvl(l_txn_raw_cost_tab(i),0) <> 0) Then
4189 l_cost_rate_override_tab(i) := 1;
4190 Elsif nvl(l_txn_burdened_cost_tab(i),0) <> 0 Then
4191 l_txn_raw_cost_tab(i) := l_quantity_tab(i);
4192 l_cost_rate_override_tab(i) := 1;
4193 End if;
4194 End If;
4195
4196 If ( nvl(l_txn_burdened_cost_tab(i),0) <> 0) Then
4197
4198 l_stage := 'PRC:29';
4199 l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
4200
4201 Else
4202
4203 l_stage := 'PRC:30';
4204 --l_txn_burdened_cost_tab(i) := l_txn_raw_cost_tab(i);
4205 --l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
4206
4207 End If;
4208
4209 If (nvl(l_txn_revenue_tab(i),0) <> 0) Then
4210
4211 l_stage := 'PRC:31';
4212 l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4213
4214 Else
4215
4216 l_stage := 'PRC:32';
4217 --l_txn_revenue_tab(i) := l_txn_burdened_cost_tab(i);
4218 --l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4219
4220 End If;
4221 END IF; -- end of bug 5007734
4222
4223 Else -- this for rate based resource
4224
4225 If nvl(l_txn_raw_cost_tab(i),0) <> 0 Then
4226
4227 l_stage := 'PRC:33';
4228 l_cost_rate_override_tab(i) := l_txn_raw_cost_tab(i)/l_quantity_tab(i);
4229
4230 Elsif nvl(l_txn_raw_cost_tab(i),0) = 0 Then
4231
4232 l_stage := 'PRC:34';
4233 --l_txn_raw_cost_tab(i) := l_quantity_tab(i);
4234 --l_cost_rate_override_tab(i) := 1;
4235
4236 End If;
4237
4238 If ( nvl(l_txn_burdened_cost_tab(i),0) <> 0) Then
4239
4240 l_stage := 'PRC:35';
4241 l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
4242
4243 Else
4244
4245 l_stage := 'PRC:36';
4246 --l_txn_burdened_cost_tab(i) := l_txn_raw_cost_tab(i);
4247 --l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
4248
4249 End If;
4250
4251 If (nvl(l_txn_revenue_tab(i),0) <> 0) Then
4252
4253 l_stage := 'PRC:37';
4254 l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4255
4256 Else
4257
4258 l_stage := 'PRC:38';
4259 --l_txn_revenue_tab(i) := l_txn_burdened_cost_tab(i);
4260 --l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4261
4262 End If;
4263
4264 End If; --If p_rate_based_flag_tbl(i) = 'N' Then
4265
4266 End If; --If l_quantity_tab(i) = 0 then
4267
4268 End IF; --IF p_preference_code = 'COST_ONLY' THEN
4269
4270 END LOOP;
4271
4272 x_quantity_tbl := l_quantity_tab ;
4273 x_txn_raw_cost_tbl := l_txn_raw_cost_tab ;
4274 x_txn_burdened_cost_tbl := l_txn_burdened_cost_tab ;
4275 x_txn_revenue_tbl := l_txn_revenue_tab ;
4276 x_raw_cost_override_rate_tbl := l_cost_rate_override_tab ;
4277 x_burd_cost_override_rate_tbl:= l_burden_rate_override_tab ;
4278 x_bill_override_rate_tbl := l_bill_rate_override_tab ;
4279
4280 IF l_debug_mode = 'Y' THEN
4281 pa_debug.g_err_stage:='Exiting Apply_Calculate_FPM_Rules';
4282 pa_debug.write( l_module_name,pa_debug.g_err_stage,3);
4283 END IF;
4284 -- reset curr function
4285 pa_debug.reset_curr_function;
4286
4287 EXCEPTION
4288
4289 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
4290 l_msg_count := FND_MSG_PUB.count_msg;
4291 IF l_msg_count = 1 THEN
4292 PA_INTERFACE_UTILS_PUB.get_messages
4293 (p_encoded => FND_API.G_TRUE
4294 ,p_msg_index => 1
4295 ,p_msg_count => l_msg_count
4296 ,p_msg_data => l_msg_data
4297 ,p_data => l_data
4298 ,p_msg_index_out => l_msg_index_out);
4299
4300 x_msg_data := l_data;
4301 x_msg_count := l_msg_count;
4302 ELSE
4303 x_msg_count := l_msg_count;
4304 END IF;
4305
4306 x_return_status := FND_API.G_RET_STS_ERROR;
4307
4308 IF l_debug_mode = 'Y' THEN
4309 pa_debug.g_err_stage:='Invalid Arguments Passed Or called api raised an error';
4310 pa_debug.write( l_module_name,pa_debug.g_err_stage,5);
4311
4312 END IF;
4313 -- reset curr function
4314 pa_debug.reset_curr_function();
4315 RETURN;
4316 WHEN OTHERS THEN
4317 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4318 x_msg_count := 1;
4319 x_msg_data := SQLERRM;
4320
4321 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_fp_upgrade_pkg'
4322 ,p_procedure_name => 'Apply_Calculate_FPM_Rules l_stage'||l_stage);
4323
4324 IF l_debug_mode = 'Y' THEN
4325 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
4326 pa_debug.write( l_module_name,pa_debug.g_err_stage,5);
4327 END IF;
4328 -- reset curr function
4329 pa_debug.Reset_Curr_Function();
4330 RAISE;
4331
4332 END Apply_Calculate_FPM_Rules;
4333
4334 PROCEDURE rollup_rejected_bl_amounts(
4335 p_from_project_number IN VARCHAR2 DEFAULT NULL
4336 ,p_to_project_number IN VARCHAR2 DEFAULT NULL
4337 ,p_fin_plan_type_id IN NUMBER DEFAULT NULL
4338 ,p_project_statuses IN VARCHAR2
4339 ,x_return_status OUT NOCOPY VARCHAR2
4340 ,x_msg_count OUT NOCOPY NUMBER
4341 ,x_msg_data OUT NOCOPY VARCHAR2) IS
4342 l_return_status VARCHAR2(2000);
4343 l_msg_count NUMBER :=0;
4344 l_msg_data VARCHAR2(2000);
4345 l_data VARCHAR2(2000);
4346 l_msg_index_out NUMBER;
4347 l_debug_mode VARCHAR2(30);
4348 l_error_msg_code VARCHAR2(2000);
4349
4350 l_project_id pa_projects.project_id%TYPE;
4351 l_bv_id pa_budget_versions.budget_version_id%TYPE;
4352 l_budg_ver_id pa_budget_versions.budget_version_id%TYPE;
4353 l_ci_id pa_control_items.ci_id%TYPE;
4354 l_op_id pa_proj_fp_options.proj_fp_options_id%TYPE;
4355
4356 /* For bug 5084161 */
4357 l_ci_status_code pa_control_items.status_code%TYPE;
4358 l_process_flag varchar2(1);
4359 /* For bug 5084161 */
4360
4361 l_retcode number;
4362 l_errbuf varchar2(512);
4363
4364 l_date date := sysdate;
4365 l_login_id NUMBER := fnd_global.login_id;
4366 l_user_id NUMBER := fnd_global.user_id;
4367
4368 l_budget_ver_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
4369
4370 l_fp_cols_rec_var PA_FP_GEN_AMOUNT_UTILS.FP_COLS; --/* Bug 5098818 */
4371
4372 record_locked EXCEPTION;
4373 PRAGMA EXCEPTION_INIT (record_locked, -54);
4374
4375 /* 5084161 - Removed the ci status checking logic from this cursor */
4376
4377 CURSOR get_proj_bv_ids_for_rup(
4378 c_from_project_number IN VARCHAR2
4379 ,c_to_project_number IN VARCHAR2
4380 ,c_project_statuses IN VARCHAR2
4381 ,c_fin_plan_type_id IN NUMBER) IS
4382 SELECT prj.project_id,
4383 bv.budget_version_id,
4384 bv.ci_id,
4385 op.proj_fp_options_id,
4386 ci.status_code
4387 FROM pa_projects prj,
4388 pa_budget_versions bv,
4389 pa_fin_plan_types_b fp,
4390 pa_control_items ci,
4391 pa_proj_fp_options op
4392 WHERE segment1 BETWEEN NVL(c_from_project_number,segment1) AND NVL(c_to_project_number,segment1)
4393 AND DECODE(c_project_statuses,'ALL','ACTIVE',prj.project_status_code) = 'ACTIVE'
4394 AND bv.project_id = prj.project_id
4395 and bv.fin_plan_type_id = fp.fin_plan_type_id
4396 and bv.budget_version_id = op.fin_plan_version_id
4397 and op.project_id = bv.project_id
4398 and nvl(c_fin_plan_type_id,fp.fin_plan_type_id) = fp.fin_plan_type_id
4399 and nvl(fp.FIN_PLAN_TYPE_CODE,'x') <> 'ORG_FORECAST'
4400 and bv.budget_status_code = 'W'
4401 and bv.ci_id = ci.ci_id(+)
4402 and NVL(pa_project_structure_utils.check_struc_ver_published(bv.project_id,bv.project_structure_version_id),'N') = 'N'
4403 and NOT EXISTS (SELECT 1 FROM pa_fp_upgrade_audit aud
4404 WHERE aud.project_id = op.project_id
4405 AND aud.proj_fp_options_id_rup = op.PROJ_FP_OPTIONS_ID
4406 AND aud.upgraded_flag = 'Y')
4407 and EXISTS (SELECT 1 FROM pa_budget_lines bl
4408 WHERE bl.budget_version_id = bv.budget_version_id
4409 AND ( bl.cost_rejection_code IS NOT NULL
4410 OR bl.revenue_rejection_code IS NOT NULL
4411 OR bl.burden_rejection_code IS NOT NULL
4412 OR bl.pfc_cur_conv_rejection_code IS NOT NULL
4413 OR bl.pc_cur_conv_rejection_code IS NOT NULL
4414 )
4415 )
4416 and bv.prc_generated_flag = 'M'; --IPM Optional Upgrade Process
4417 /* PRC_GENERATED_FLAG is marked with 'M' during the patchset upgrade process
4418 of paupg109.sql while upgrading from FPM to IPM level. This cursor picks up
4419 only those budget_versions which were marked during patchset upgrade. Note that
4420 PRC_GENERATED_FLAG is being reused here; it was introduced earlier for a different
4421 purpose but was never used
4422 */
4423
4424 BEGIN
4425 x_msg_count := 0;
4426 x_return_status := FND_API.G_RET_STS_SUCCESS;
4427 savepoint rollup_rejected_bl_amounts;
4428
4429 pa_debug.init_err_stack('PA_FP_UPGRADE_PKG.rollup_rejected_bl_amounts');
4430
4431 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
4432 l_debug_mode := NVL(l_debug_mode, 'Y');
4433 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
4434
4435 IF p_pa_debug_mode = 'Y' THEN
4436 pa_debug.g_err_stage := 'Entered rollup_rejected_bl_amounts';
4437 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4438 pa_debug.g_err_stage := 'Checking for valid parameters';
4439 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4440 END IF;
4441
4442 IF p_project_statuses IS NULL THEN
4443 IF p_pa_debug_mode = 'Y' THEN
4444 pa_debug.g_err_stage := 'p_project_statuses='||p_project_statuses;
4445 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
4446 END IF;
4447
4448 PA_UTILS.ADD_MESSAGE(p_app_short_name=> 'PA',
4449 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
4450 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4451 END IF;
4452
4453 IF p_pa_debug_mode = 'Y' THEN
4454 pa_debug.g_err_stage := 'Parameter validation complete';
4455 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4456 END IF;
4457
4458 -- Fetch all the projects whose budget's lines amounts need to be rolled up.
4459 IF p_pa_debug_mode = 'Y' THEN
4460 pa_debug.g_err_stage := 'opening get_proj_bv_ids_for_rup';
4461 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4462 pa_debug.g_err_stage := 'p_from_project_number = '||p_from_project_number;
4463 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4464 pa_debug.g_err_stage := 'p_to_project_number = '|| p_to_project_number;
4465 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4466 pa_debug.g_err_stage := 'p_project_statuses='||p_project_statuses;
4467 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
4468 END IF;
4469
4470 OPEN get_proj_bv_ids_for_rup(p_from_project_number,p_to_project_number,p_project_statuses,p_fin_plan_type_id);
4471 LOOP
4472 FETCH get_proj_bv_ids_for_rup INTO l_project_id,l_bv_id,l_ci_id,l_op_id,l_ci_status_code;
4473 EXIT WHEN get_proj_bv_ids_for_rup%NOTFOUND;
4474
4475 IF p_pa_debug_mode = 'Y' THEN
4476 pa_debug.g_err_stage := 'Project_id ='||l_project_id;
4477 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4478
4479 pa_debug.g_err_stage := 'Opening get_fin_plan_versions'||l_project_id;
4480 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4481 END IF;
4482
4483 savepoint rollup_bl_amounts_to_bv;
4484
4485 BEGIN
4486
4487 /* Start of fix for bug 5084161 */
4488
4489 l_process_flag := 'Y';
4490
4491 /* Check if ci is in updateable status - following code got from ci team */
4492
4493 IF l_ci_id IS NOT NULL THEN
4494
4495 begin
4496 select 'Y'
4497 into l_process_flag
4498 from pa_project_statuses ps ,
4499 pa_project_status_controls psc
4500 where ps.project_Status_code = l_ci_status_code
4501 and ps.project_system_status_code = nvl(psc.project_system_status_code,psc.project_Status_code)
4502 and psc.status_type = 'CONTROL_ITEM'
4503 and psc.action_code = 'CONTROL_ITEM_ALLOW_UPDATE'
4504 and psc.enabled_flag = 'N'
4505 and rownum < 2;
4506
4507 exception
4508 when no_data_found then
4509 l_process_flag := 'N';
4510 end;
4511
4512 END IF;
4513
4514 /* End of fix for bug 5084161 */
4515
4516 IF p_pa_debug_mode = 'Y' THEN
4517 pa_debug.g_err_stage := 'Budget Version Id ='||l_bv_id;
4518 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4519
4520 pa_debug.g_err_stage := 'Change Order/Req ID ='||l_ci_id;
4521 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4522
4523 pa_debug.g_err_stage := 'l_ci_status_code / l_process_flag = ' || l_ci_status_code || '/' || l_process_flag;
4524 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4525 END IF;
4526
4527 IF l_process_flag = 'Y' THEN /* 5084161 */
4528
4529 -- Now we have a budget version id and we will try to lock these
4530 -- We try to lock the records in pa_resource_asgn_curr,pa_resource_assignments
4531 -- as well to avoid the partial processing.
4532 -- Imagine the case where the ra records are processed and now we fail
4533 -- to obtain a lock on budget versions record. In order to avoid this
4534 -- we obtain locks on all the records in all the table and then we will
4535 -- proceed. If we fail to obtain a lock on a particular budget version then
4536 -- We will update the audit table with that id and the reason then we will
4537 -- proceed to process the other budget versions in the project/range.
4538
4539
4540 IF p_pa_debug_mode = 'Y' THEN
4541 pa_debug.g_err_stage := 'Deleting the pl/sql tables before locking the records.';
4542 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4543 END IF;
4544
4545
4546 l_rtx_ra_id_tbl.delete;
4547 l_ra_id_tbl.delete ;
4548
4549 IF p_pa_debug_mode = 'Y' THEN
4550 pa_debug.g_err_stage := 'Successfully Deleted the pl/sql tables.';
4551 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4552 END IF;
4553
4554 SELECT bv.budget_version_id INTO l_budg_ver_id
4555 FROM pa_budget_versions bv
4556 WHERE bv.budget_version_id = l_bv_id
4557 FOR UPDATE OF bv.budget_version_id NOWAIT;
4558
4559 IF p_pa_debug_mode = 'Y' THEN
4560 pa_debug.g_err_stage := 'Successfully locked the budget version records';
4561 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4562 END IF;
4563
4564 SELECT rtx.resource_assignment_id BULK COLLECT INTO l_rtx_ra_id_tbl
4565 FROM pa_resource_asgn_curr rtx
4566 WHERE rtx.budget_version_id = l_bv_id
4567 FOR UPDATE OF rtx.resource_assignment_id NOWAIT;
4568
4569 IF p_pa_debug_mode = 'Y' THEN
4570 pa_debug.g_err_stage := 'Successfully locked the resource assign curr records';
4571 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4572 END IF;
4573
4574
4575 SELECT ra.resource_assignment_id BULK COLLECT INTO l_ra_id_tbl
4576 FROM pa_resource_assignments ra
4577 WHERE ra.budget_version_id = l_bv_id
4578 FOR UPDATE OF ra.resource_assignment_id NOWAIT;
4579
4580 IF p_pa_debug_mode = 'Y' THEN
4581 pa_debug.g_err_stage := 'Successfully locked the resource assignment records';
4582 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4583 END IF;
4584
4585
4586 -- Process each Budget version here
4587 -- by rolling up the totals from rejected Budget Line's total
4588 -- onto the new entity and RA and BV.
4589 /* Bug 5098818 - Start - Replaced exclusive update stmt with a call to maintain_data api */
4590 /* populating fp_cols_rec to call the new entity maintenace API */
4591 PA_FP_GEN_AMOUNT_UTILS.get_plan_version_dtls
4592 (p_budget_version_id => l_bv_id,
4593 x_fp_cols_rec => l_fp_cols_rec_var,
4594 x_return_status => l_return_status,
4595 x_msg_count => l_msg_count,
4596 x_msg_data => l_msg_data);
4597
4598 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
4599 THEN
4600 IF p_pa_debug_mode = 'Y' THEN
4601 pa_debug.write_file('Upgrade failed due to error in PA_FP_GEN_AMOUNT_UTILS.get_plan_version_dtls',5);
4602 END IF;
4603 raise PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
4604 END IF;
4605
4606 /* calling the maintenance api to insert data into the new planning transaction level table */
4607 PA_RES_ASG_CURRENCY_PUB.maintain_data
4608 (p_fp_cols_rec => l_fp_cols_rec_var,
4609 p_calling_module => 'UPGRADE',
4610 p_rollup_flag => 'Y',
4611 p_version_level_flag => 'Y',
4612 x_return_status => l_return_status,
4613 x_msg_count => l_msg_count,
4614 x_msg_data => l_msg_data);
4615
4616 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
4617 THEN
4618 IF p_pa_debug_mode = 'Y' THEN
4619 pa_debug.write_file('Upgrade failed due to error in PA_RES_ASG_CURRENCY_PUB.maintain_data',5);
4620 END IF;
4621 raise PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
4622 END IF;
4623 /* Bug 5098818 - End - Replaced exclusive update stmt with a call to maintain_data api */
4624
4625 IF p_pa_debug_mode = 'Y' THEN
4626 pa_debug.g_err_stage := 'Updated the resource assign curr amts from budget lines';
4627 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4628 END IF;
4629
4630
4631 -- Rollup the totals onto the RA
4632 /* Bug 5098818 - Start - Replaced exclusive update stmt with a call to already existing rollup_budget api */
4633
4634 PA_FP_ROLLUP_PKG.ROLLUP_BUDGET_VERSION(
4635 p_budget_version_id => l_bv_id
4636 ,p_entire_version => 'Y'
4637 ,p_context => NULL
4638 ,x_return_status => l_return_status
4639 ,x_msg_count => l_msg_count
4640 ,x_msg_data => l_msg_data);
4641
4642 /* Bug 5098818 - End - Replaced exclusive update stmt with a call to already existing rollup_budget api */
4643 IF p_pa_debug_mode = 'Y' THEN
4644 pa_debug.g_err_stage := 'Updated the resource assignment amts from resource assign curr';
4645 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4646 END IF;
4647
4648 -- Auditing the RA ID records.
4649 FORALL i IN l_ra_id_tbl.FIRST..l_ra_id_tbl.LAST
4650 -- SAVE EXCEPTIONS
4651 INSERT INTO pa_budget_lines_m_upg_dtrange(
4652 LAST_UPDATE_DATE
4653 ,LAST_UPDATED_BY
4654 ,CREATION_DATE
4655 ,CREATED_BY
4656 ,LAST_UPDATE_LOGIN
4657 ,BUDGET_VERSION_ID_RUP
4658 ,RESOURCE_ASSIGNMENT_ID_RUP)
4659 VALUES ( sysdate
4660 ,fnd_global.user_id
4661 ,sysdate
4662 ,fnd_global.user_id
4663 ,fnd_global.login_id
4664 ,l_bv_id
4665 ,l_ra_id_tbl(i));
4666
4667 IF p_pa_debug_mode = 'Y' THEN
4668 pa_debug.g_err_stage := 'Audited the resource assignment IDs';
4669 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4670 END IF;
4671
4672 -- Sync'ing up the amounts in the PJI model by calling the PJI APIs.
4673 -- The business rule is that we should not call the PJI APIs for CO/CR.
4674
4675 IF p_pa_debug_mode = 'Y' THEN
4676 pa_debug.g_err_stage := 'Before calling PJI APIs';
4677 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4678 END IF;
4679
4680
4681 IF l_ci_id IS NULL THEN
4682
4683 l_budget_ver_tbl.extend;
4684 l_budget_ver_tbl(1) := l_bv_id;
4685
4686 IF p_pa_debug_mode = 'Y' THEN
4687 pa_debug.g_err_stage := 'Before calling PJI API PLAN_DELETE for budget ver '||l_bv_id;
4688 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4689 END IF;
4690
4691 PJI_FM_XBS_ACCUM_MAINT.PLAN_DELETE (
4692 p_fp_version_ids => l_budget_ver_tbl,
4693 x_return_status => x_return_status,
4694 x_msg_code => l_error_msg_code);
4695
4696 IF p_pa_debug_mode = 'Y' THEN
4697 pa_debug.g_err_stage := 'The rtn sts of PJI API PLAN_DELETE is '||x_return_status;
4698 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4699
4700 pa_debug.g_err_stage := 'The msg code of PJI API PLAN_DELETE is '||l_error_msg_code;
4701 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4702 END IF;
4703
4704 IF (l_return_status <> 'S') THEN
4705 RAISE pa_fp_constants_pkg.Invalid_Arg_Exc;
4706 END IF;
4707
4708 PJI_FM_XBS_ACCUM_MAINT.PLAN_CREATE (
4709 p_fp_version_ids => l_budget_ver_tbl,
4710 x_return_status => x_return_status,
4711 x_msg_code => l_error_msg_code);
4712
4713 IF p_pa_debug_mode = 'Y' THEN
4714 pa_debug.g_err_stage := 'The rtn sts of PJI API PLAN_CREATE is '||x_return_status;
4715 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4716
4717 pa_debug.g_err_stage := 'The msg code of PJI API PLAN_CREATE is '||l_error_msg_code;
4718 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4719 END IF;
4720
4721 IF (l_return_status <> 'S') THEN
4722 RAISE pa_fp_constants_pkg.Invalid_Arg_Exc;
4723 END IF;
4724
4725 END IF;
4726
4727 -- Now Audit the Budget version level record change
4728
4729 IF p_pa_debug_mode = 'Y' THEN
4730 pa_debug.g_err_stage := 'Auditing the budget version ';
4731 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4732
4733 pa_debug.g_err_stage := 'The budget version proj fp id is '||l_op_id;
4734 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4735 END IF;
4736
4737
4738 pa_fp_upgrade_pkg.Insert_Audit_Record(
4739 p_project_id => l_project_id
4740 ,p_budget_type_code => NULL
4741 ,p_proj_fp_options_id => NULL
4742 ,p_fin_plan_option_level_code => NULL
4743 ,p_basis_cost_version_id => NULL
4744 ,p_basis_rev_version_id => NULL
4745 ,p_basis_cost_bem => NULL
4746 ,p_basis_rev_bem => NULL
4747 ,p_upgraded_flag => 'Y'
4748 ,p_failure_reason_code => NULL
4749 ,p_proj_fp_options_id_rup => l_op_id);
4750 END IF; /* l_process_flag = 'Y' */
4751 EXCEPTION
4752 WHEN record_locked THEN
4753 /* Record was already locked, so audit this version and
4754 just keep on going */
4755
4756 IF p_pa_debug_mode = 'Y' THEN
4757 pa_debug.g_err_stage := 'inside Bdgts loop:Record is locked ';
4758 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4759
4760 pa_debug.g_err_stage := 'The proj id is '||l_project_id;
4761 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4762
4763 pa_debug.g_err_stage := 'The proj fp id is '||l_op_id;
4764 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4765 END IF;
4766
4767 rollback to rollup_bl_amounts_to_bv;
4768 pa_fp_upgrade_pkg.Insert_Audit_Record(
4769 p_project_id => l_project_id
4770 ,p_budget_type_code => NULL
4771 ,p_proj_fp_options_id => NULL
4772 ,p_fin_plan_option_level_code => NULL
4773 ,p_basis_cost_version_id => NULL
4774 ,p_basis_rev_version_id => NULL
4775 ,p_basis_cost_bem => NULL
4776 ,p_basis_rev_bem => NULL
4777 ,p_upgraded_flag => 'N'
4778 ,p_failure_reason_code => 'Record Locked'
4779 ,p_proj_fp_options_id_rup => l_op_id);
4780
4781 WHEN OTHERS THEN
4782
4783 IF p_pa_debug_mode = 'Y' THEN
4784 pa_debug.g_err_stage := 'inside Bdgts loop:When others and sqlcode is '||sqlcode;
4785 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4786
4787 pa_debug.g_err_stage := 'The proj id is '||l_project_id;
4788 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4789
4790 pa_debug.g_err_stage := 'The proj fp id is '||l_op_id;
4791 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4792 END IF;
4793
4794 rollback to rollup_bl_amounts_to_bv;
4795 pa_fp_upgrade_pkg.Insert_Audit_Record(
4796 p_project_id => l_project_id
4797 ,p_budget_type_code => NULL
4798 ,p_proj_fp_options_id => NULL
4799 ,p_fin_plan_option_level_code => NULL
4800 ,p_basis_cost_version_id => NULL
4801 ,p_basis_rev_version_id => NULL
4802 ,p_basis_cost_bem => NULL
4803 ,p_basis_rev_bem => NULL
4804 ,p_upgraded_flag => 'N'
4805 ,p_failure_reason_code => sqlcode
4806 ,p_proj_fp_options_id_rup => l_op_id);
4807
4808
4809 END;
4810 l_rtx_ra_id_tbl.delete;
4811 l_ra_id_tbl.delete ;
4812 l_budget_ver_tbl.DELETE;
4813 COMMIT; -- this commits data for each Plan processed
4814
4815 -- END LOOP;
4816 -- CLOSE get_fin_plan_versions;
4817 END LOOP;
4818 CLOSE get_proj_bv_ids_for_rup;
4819 IF p_pa_debug_mode = 'Y' THEN
4820 pa_debug.g_err_stage := 'Closed get_proj_bv_ids_for_rup';
4821 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4822 pa_debug.g_err_stage := 'Exiting rollup_rejected_bl_amounts';
4823 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4824 END IF;
4825 pa_debug.reset_err_stack;
4826 EXCEPTION
4827
4828 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
4829
4830 IF get_proj_bv_ids_for_rup%ISOPEN THEN
4831 CLOSE get_proj_bv_ids_for_rup;
4832 END IF;
4833 l_msg_count := FND_MSG_PUB.count_msg;
4834 IF l_msg_count = 1 THEN
4835 PA_INTERFACE_UTILS_PUB.get_messages
4836 (p_encoded => FND_API.G_TRUE
4837 ,p_msg_index => 1
4838 ,p_msg_count => l_msg_count
4839 ,p_msg_data => l_msg_data
4840 ,p_data => l_data
4841 ,p_msg_index_out => l_msg_index_out);
4842 x_msg_data := l_data;
4843 x_msg_count := l_msg_count;
4844 ELSE
4845 x_msg_count := l_msg_count;
4846 x_msg_data := l_msg_data;
4847 END IF;
4848
4849 IF p_pa_debug_mode = 'Y' THEN
4850 pa_debug.g_err_stage:='Invalid Arguments Passed';
4851 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
4852 END IF;
4853 x_return_status:= FND_API.G_RET_STS_ERROR;
4854 pa_debug.write_file('Rollup_rejected_bl_ampunts : Upgrade has failed for the project: '||l_project_id,5);
4855 pa_debug.write_file('Rollup_rejected_bl_ampunts : Failure Reason:'||x_msg_data,5);
4856 pa_debug.reset_err_stack;
4857 ROLLBACK TO rollup_rejected_bl_amounts;
4858 RAISE;
4859 WHEN Others THEN
4860
4861 IF get_proj_bv_ids_for_rup%ISOPEN THEN
4862 CLOSE get_proj_bv_ids_for_rup;
4863 END IF;
4864 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4865 x_msg_count := 1;
4866 x_msg_data := SQLERRM;
4867
4868 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_FP_UPGRADE_PKG'
4869 ,p_procedure_name => 'Rollup_rejected_bl_ampunts');
4870 IF p_pa_debug_mode = 'Y' THEN
4871 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
4872 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
4873 END IF;
4874 pa_debug.write_file('Rollup_rejected_bl_ampunts : Upgrade has failed for the project'||l_project_id,5);
4875 pa_debug.write_file('Upgrade_Budgets : Failure Reason:'||pa_debug.G_Err_Stack,5);
4876 pa_debug.reset_err_stack;
4877 ROLLBACK TO rollup_rejected_bl_amounts;
4878 RAISE;
4879 END rollup_rejected_bl_amounts;
4880
4881 END pa_fp_upgrade_pkg;