[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.3 2008/09/25 23:01:32 jngeorge 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 -- Added below for Bug# 7187487
2736 for i in l_budget_ver_tbl2.first..l_budget_ver_tbl2.last loop
2737 IF p_pa_debug_mode = 'Y' THEN
2738 pa_debug.g_err_stage := 'Calling PJI Plan (Version) Baseline ';
2739 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2740 END IF;
2741
2742 PJI_FM_XBS_ACCUM_MAINT.PLAN_BASELINE (
2743 p_baseline_version_id => l_budget_ver_tbl2(i),
2744 p_new_version_id => l_budget_ver_tbl2(i),
2745 x_return_status => l_return_status,
2746 x_msg_code => l_msg_data);
2747
2748 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2749 IF p_pa_debug_mode = 'Y' THEN
2750 pa_debug.write_file('Upgrade failed due to error in PJI_FM_XBS_ACCUM_MAINT.PLAN_baseline',5);
2751 END IF;
2752 raise PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
2753 END IF;
2754 end loop;
2755
2756 END; -- end of creation of proj perf data bug 7187487
2757
2758
2759 END IF;--IF l_budget_ver_tbl.COUNT>0 THEN
2760
2761 --Bug 4171254. Corrected the criteria for exiting the loop. The loop should be exited whenever
2762 --l_budget_ver_tbl contains records less than the limit size.
2763 EXIT WHEN l_budget_ver_tbl.count < 200;
2764
2765 END LOOP;
2766 CLOSE budgets_for_upgrade_cur;
2767
2768
2769 IF p_pa_debug_mode = 'Y' THEN
2770 pa_debug.g_err_stage := 'Closed budgets_for_upgrade_cur ';
2771 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2772
2773 pa_debug.g_err_stage := 'Exiting Upgrade_Budget_Versions';
2774 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2775 END IF;
2776 pa_debug.reset_err_stack;
2777
2778 EXCEPTION
2779
2780 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2781
2782 IF budgets_for_upgrade_cur%ISOPEN THEN
2783 CLOSE budgets_for_upgrade_cur;
2784 END IF;
2785 l_msg_count := FND_MSG_PUB.count_msg;
2786 IF l_msg_count = 1 THEN
2787 PA_INTERFACE_UTILS_PUB.get_messages
2788 ( p_encoded => FND_API.G_TRUE
2789 ,p_msg_index => 1
2790 ,p_msg_count => l_msg_count
2791 ,p_msg_data => l_msg_data
2792 ,p_data => l_data
2793 ,p_msg_index_out => l_msg_index_out);
2794 x_msg_data := l_data;
2795 x_msg_count := l_msg_count;
2796 ELSE
2797 x_msg_count := l_msg_count;
2798 x_msg_data := l_msg_data;
2799 END IF;
2800 IF p_pa_debug_mode = 'Y' THEN
2801 pa_debug.g_err_stage:='Invalid Arguments Passed';
2802 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2803 pa_debug.write_file('Upgrade_Budget_Versions ' || x_msg_data,5);
2804 END IF;
2805
2806 x_return_status:= FND_API.G_RET_STS_ERROR;
2807 pa_debug.reset_err_stack;
2808 RAISE;
2809
2810 WHEN Others THEN
2811
2812 IF budgets_for_upgrade_cur%ISOPEN THEN
2813 CLOSE budgets_for_upgrade_cur;
2814 END IF;
2815 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2816 x_msg_count := 1;
2817 x_msg_data := SQLERRM;
2818 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_UPGRADE_PKG'
2819 ,p_procedure_name => 'Upgrade_Budget_Versions');
2820 IF p_pa_debug_mode = 'Y' THEN
2821 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
2822 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2823 pa_debug.write_file('Upgrade_Budget_Versions ' || pa_debug.G_Err_Stack,5);
2824 END IF;
2825 pa_debug.reset_err_stack;
2826 RAISE;
2827
2828 END Upgrade_Budget_Versions;
2829
2830 /*===============================================================================
2831 The follwing api is a table handler for the pa_fp_upgrade_audit table.
2832 ===============================================================================*/
2833 PROCEDURE Insert_Audit_Record(
2834 p_project_id IN PA_FP_UPGRADE_AUDIT.PROJECT_ID%TYPE
2835 ,p_budget_type_code IN PA_FP_UPGRADE_AUDIT.BUDGET_TYPE_CODE%TYPE
2836 ,p_proj_fp_options_id IN PA_FP_UPGRADE_AUDIT.PROJ_FP_OPTIONS_ID%TYPE
2837 ,p_fin_plan_option_level_code IN PA_FP_UPGRADE_AUDIT.FIN_PLAN_OPTION_LEVEL_CODE%TYPE
2838 ,p_basis_cost_version_id IN PA_FP_UPGRADE_AUDIT.BASIS_COST_VERSION_ID%TYPE
2839 ,p_basis_rev_version_id IN PA_FP_UPGRADE_AUDIT.BASIS_REV_VERSION_ID%TYPE
2840 ,p_basis_cost_bem IN PA_FP_UPGRADE_AUDIT.BASIS_COST_BEM%TYPE
2841 ,p_basis_rev_bem IN PA_FP_UPGRADE_AUDIT.BASIS_REV_BEM%TYPE
2842 ,p_upgraded_flag IN PA_FP_UPGRADE_AUDIT.UPGRADED_FLAG%TYPE
2843 ,p_failure_reason_code IN PA_FP_UPGRADE_AUDIT.FAILURE_REASON_CODE%TYPE
2844 ,p_proj_fp_options_id_rup IN PA_FP_UPGRADE_AUDIT.PROJ_FP_OPTIONS_ID%TYPE DEFAULT NULL) IS
2845
2846 BEGIN
2847
2848 INSERT INTO PA_FP_UPGRADE_AUDIT (
2849 PROJECT_ID
2850 ,BUDGET_TYPE_CODE
2851 ,PROJ_FP_OPTIONS_ID
2852 ,FIN_PLAN_OPTION_LEVEL_CODE
2853 ,BASIS_COST_VERSION_ID
2854 ,BASIS_REV_VERSION_ID
2855 ,BASIS_COST_BEM
2856 ,BASIS_REV_BEM
2857 ,REQUEST_ID
2858 ,UPGRADED_FLAG
2859 ,FAILURE_REASON_CODE
2860 ,LAST_UPDATE_DATE
2861 ,LAST_UPDATED_BY
2862 ,CREATION_DATE
2863 ,CREATED_BY
2864 ,LAST_UPDATE_LOGIN
2865 ,proj_fp_options_id_rup )
2866 VALUES(
2867 p_project_id
2868 ,p_budget_type_code
2869 ,p_proj_fp_options_id
2870 ,p_fin_plan_option_level_code
2871 ,p_basis_cost_version_id
2872 ,p_basis_rev_version_id
2873 ,p_basis_cost_bem
2874 ,p_basis_rev_bem
2875 ,fnd_global.conc_request_id
2876 ,p_upgraded_flag
2877 ,p_failure_reason_code
2878 ,sysdate
2879 ,fnd_global.user_id
2880 ,sysdate
2881 ,fnd_global.user_id
2882 ,fnd_global.login_id
2883 ,p_proj_fp_options_id_rup);
2884
2885 END Insert_Audit_Record;
2886
2887
2888 /*==================================================================
2889 This api would be called by the pre_upgrade process from the
2890 upgrade budgets report. The api would insert the exception records
2891 into pa_fp_upgrade_audit table and pa_fp_upgrade_exceptions_tmp as
2892 necessary.
2893 ==================================================================*/
2894
2895 PROCEDURE VALIDATE_BUDGETS (
2896 p_from_project_number IN VARCHAR2
2897 ,p_to_project_number IN VARCHAR2
2898 ,p_budget_types IN VARCHAR2
2899 ,p_budget_statuses IN VARCHAR2
2900 ,p_project_type IN VARCHAR2
2901 ,p_project_statuses IN VARCHAR2
2902 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2903 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2904 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2905 AS
2906
2907 l_msg_count NUMBER := 0;
2908 l_data VARCHAR2(2000);
2909 l_msg_data VARCHAR2(2000);
2910 l_msg_index_out NUMBER;
2911 l_return_status VARCHAR2(2000);
2912 l_debug_mode VARCHAR2(30);
2913
2914 l_validation_status VARCHAR2(30);
2915 l_project_id pa_projects.project_id%TYPE;
2916
2917 -- cursor written for bug 2853511
2918
2919 CURSOR attached_budget_types_cur(
2920 c_project_id IN pa_projects.project_id%TYPE
2921 ,c_budget_types IN VARCHAR2 ) IS
2922 SELECT bt.budget_type_code budget_type_code
2923 FROM pa_budget_types bt
2924 WHERE DECODE(c_budget_types,'ALL','Y', bt.upgrade_budget_type_flag) = 'Y'
2925 AND NVL(bt.plan_type,'BUDGET') = 'BUDGET'
2926 AND NOT EXISTS
2927 (SELECT 1
2928 FROM pa_proj_fp_options ppfo
2929 ,pa_fin_plan_types_b pt
2930 WHERE pt.migrated_frm_bdgt_typ_code = bt.budget_type_code
2931 AND ppfo.project_id = c_project_id
2932 AND ppfo.fin_plan_type_id = pt.fin_plan_type_id
2933 AND ppfo.fin_plan_option_level_code = PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE)
2934 AND EXISTS
2935 (SELECT 1
2936 FROM pa_budget_versions pbv
2937 WHERE pbv.project_id = c_project_id
2938 AND pbv.budget_type_code = bt.budget_type_code);
2939
2940 attached_budget_types_rec attached_budget_types_cur%ROWTYPE;
2941
2942 BEGIN
2943
2944 x_msg_count := 0;
2945 x_return_status := FND_API.G_RET_STS_SUCCESS;
2946 pa_debug.init_err_stack('PA_FP_UPGRADE_PKG.validate_budgets');
2947 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2948 l_debug_mode := NVL(l_debug_mode, 'Y');
2949 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
2950
2951 -- Check for business rules violations
2952
2953 IF p_pa_debug_mode = 'Y' THEN
2954 pa_debug.g_err_stage:= 'Validating input parameters';
2955 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
2956 END IF;
2957
2958 IF (p_budget_types IS NULL) OR
2959 (p_budget_statuses IS NULL) OR
2960 (p_project_statuses IS NULL)
2961 THEN
2962 IF p_pa_debug_mode = 'Y' THEN
2963 pa_debug.g_err_stage := 'p_budget_types='||p_budget_types;
2964 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2965 pa_debug.g_err_stage := 'p_budget_statuses='||p_budget_statuses;
2966 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2967 pa_debug.g_err_stage := 'p_project_statuses='||p_project_statuses;
2968 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
2969 END IF;
2970
2971 PA_UTILS.ADD_MESSAGE(p_app_short_name=> 'PA',
2972 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
2973 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2974
2975 END IF;
2976
2977 -- Fetch all the projects that are eligible for upgrade
2978
2979 IF ( p_from_project_number IS NOT NULL) AND ( p_to_project_number IS NOT NULL ) THEN
2980
2981 OPEN projects_for_upgrade_cur1(p_from_project_number,p_to_project_number,p_project_type,p_project_statuses);
2982
2983 ELSIF ( p_project_type IS NOT NULL) THEN
2984
2985 OPEN projects_for_upgrade_cur2(p_from_project_number,p_to_project_number,p_project_type,p_project_statuses);
2986
2987 ELSIF (p_project_statuses <> 'ALL') THEN
2988
2989 OPEN projects_for_upgrade_cur3(p_from_project_number,p_to_project_number,p_project_type,p_project_statuses);
2990 ELSE
2991 OPEN projects_for_upgrade_cur(p_from_project_number,p_to_project_number,p_project_type,p_project_statuses);
2992 END IF;
2993
2994
2995 LOOP
2996
2997 IF ( p_from_project_number IS NOT NULL) AND ( p_to_project_number IS NOT NULL ) THEN
2998
2999 FETCH projects_for_upgrade_cur1 INTO l_project_id;
3000 EXIT WHEN projects_for_upgrade_cur1%NOTFOUND;
3001
3002 ELSIF ( p_project_type IS NOT NULL) THEN
3003
3004 FETCH projects_for_upgrade_cur2 INTO l_project_id;
3005 EXIT WHEN projects_for_upgrade_cur2%NOTFOUND;
3006
3007 ELSIF (p_project_statuses <> 'ALL') THEN
3008
3009 FETCH projects_for_upgrade_cur3 INTO l_project_id;
3010 EXIT WHEN projects_for_upgrade_cur3%NOTFOUND;
3011
3012 ELSE
3013 FETCH projects_for_upgrade_cur INTO l_project_id;
3014 EXIT WHEN projects_for_upgrade_cur%NOTFOUND;
3015 END IF;
3016
3017
3018 --Check if any types of budgets are allowed for the project using project_type_info_cur.
3019
3020 IF p_pa_debug_mode = 'Y' THEN
3021 pa_debug.g_err_stage := 'l_project_id='||l_project_id;
3022 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3023 END IF;
3024
3025 OPEN project_type_info_cur(l_project_id);
3026 FETCH project_type_info_cur INTO project_type_info_rec;
3027 CLOSE project_type_info_cur;
3028
3029 IF (( project_type_info_rec.allow_cost_budget_entry_flag ='Y' )OR
3030 ( project_type_info_rec.allow_rev_budget_entry_flag = 'Y' )) AND
3031 ( NVL(project_type_info_rec.org_project_flag,'N') = 'N' ) -- bug :- 2788983 org_forecast project shouldn't be upgraded
3032 THEN
3033
3034 -- Perform project level validations necessary for UPGRADE
3035
3036 pa_fp_upgrade_pkg.Validate_Project(
3037 p_project_id => l_project_id
3038 ,x_validation_status => l_validation_status
3039 ,x_return_status => l_return_status
3040 ,x_msg_count => l_msg_count
3041 ,x_msg_data => l_msg_data);
3042
3043 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3044 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3045 END IF;
3046
3047 -- Fetch all the budget/plan types that have to be attached to the project
3048 -- during upgrade.
3049
3050 -- Changes for bug 2853511
3051 -- In PRE_UPGRADE mode, there needn't exist a corresponding plan_type for each
3052 -- budget type that has been chosen to be upgraded. So, we should use a different
3053 -- cursor to return all the budget_type_codes for a given project.
3054
3055 /*
3056 OPEN attached_plan_types_cur(l_project_id, p_budget_types);
3057 LOOP
3058 FETCH attached_plan_types_cur INTO attached_plan_types_rec;
3059 EXIT WHEN attached_plan_types_cur%NOTFOUND;
3060
3061 IF p_pa_debug_mode = 'Y' THEN
3062 pa_debug.g_err_stage := 'budget_type_code='||attached_plan_types_rec.budget_type_code;
3063 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3064 END IF;
3065
3066 -- For each budget type fetched check for all the exceptions
3067
3068 pa_fp_upgrade_pkg.Validate_Project_Plan_Type(
3069 p_project_id => l_project_id
3070 ,p_budget_type_code => attached_plan_types_rec.budget_type_code
3071 ,x_validation_status => l_validation_status
3072 ,x_return_status => l_return_status
3073 ,x_msg_count => l_msg_count
3074 ,x_msg_data => l_msg_data);
3075
3076 END LOOP;
3077 CLOSE attached_plan_types_cur;
3078 */
3079
3080 OPEN attached_budget_types_cur(l_project_id, p_budget_types);
3081 LOOP
3082 FETCH attached_budget_types_cur INTO attached_budget_types_rec;
3083 EXIT WHEN attached_budget_types_cur%NOTFOUND;
3084
3085 IF p_pa_debug_mode = 'Y' THEN
3086 pa_debug.g_err_stage := 'budget_type_code='||attached_budget_types_rec.budget_type_code;
3087 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3088 END IF;
3089
3090 -- For each budget type fetched check for all the exceptions
3091
3092 pa_fp_upgrade_pkg.Validate_Project_Plan_Type(
3093 p_project_id => l_project_id
3094 ,p_budget_type_code => attached_budget_types_rec.budget_type_code
3095 ,x_validation_status => l_validation_status
3096 ,x_return_status => l_return_status
3097 ,x_msg_count => l_msg_count
3098 ,x_msg_data => l_msg_data);
3099
3100 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3101 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3102 END IF;
3103
3104 END LOOP;
3105 CLOSE attached_budget_types_cur;
3106
3107
3108 -- Fetch all the budget versions of the current project that are eligible for upgrade
3109 /* For FP M: The pre upgrade report is not going to do anything. Retaining the code as it is
3110 (its just dummy processing) and would remove it later. */
3111 OPEN budgets_for_upgrade_cur(l_project_id,p_budget_types,p_budget_statuses,'PRE_UPGRADE');
3112 LOOP
3113 FETCH budgets_for_upgrade_cur INTO budgets_for_upgrade_rec;
3114 EXIT WHEN budgets_for_upgrade_cur%NOTFOUND;
3115
3116 IF p_pa_debug_mode = 'Y' THEN
3117 pa_debug.g_err_stage := 'budget_version_id='||budgets_for_upgrade_rec.budget_version_id;
3118 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3119 END IF;
3120
3121 -- Perform budget_version level validations necessary for UPGRADE
3122 -- The only validation that was done in this api was for mixed resource planning level.
3123 -- This is not applicable for FP M and hence for FP M this api doesnt do any validation.
3124
3125 pa_fp_upgrade_pkg.Validate_Budget_Version
3126 ( p_budget_version_id => budgets_for_upgrade_rec.budget_version_id
3127 ,x_return_status => l_return_status
3128 ,x_msg_count => l_msg_count
3129 ,x_msg_data => l_msg_data);
3130
3131 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3132 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3133 END IF;
3134 END LOOP;
3135 CLOSE budgets_for_upgrade_cur;
3136
3137 END IF; --if any types of budget are allowed for the project
3138
3139 END LOOP;
3140
3141 IF projects_for_upgrade_cur1%ISOPEN THEN
3142 CLOSE projects_for_upgrade_cur1;
3143 ELSIF projects_for_upgrade_cur2%ISOPEN THEN
3144 CLOSE projects_for_upgrade_cur2;
3145 ELSIF projects_for_upgrade_cur3%ISOPEN THEN
3146 CLOSE projects_for_upgrade_cur3;
3147 ELSE
3148 CLOSE projects_for_upgrade_cur;
3149 END IF;
3150
3151 IF p_pa_debug_mode = 'Y' THEN
3152 pa_debug.g_err_stage:= 'Exiting validate_budgets';
3153 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3154 END IF;
3155 pa_debug.reset_err_stack;
3156
3157 EXCEPTION
3158
3159 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3160
3161 x_return_status := FND_API.G_RET_STS_ERROR;
3162 l_msg_count := FND_MSG_PUB.count_msg;
3163 IF l_msg_count = 1 THEN
3164 PA_INTERFACE_UTILS_PUB.get_messages
3165 (p_encoded => FND_API.G_TRUE
3166 ,p_msg_index => 1
3167 ,p_msg_count => l_msg_count
3168 ,p_msg_data => l_msg_data
3169 ,p_data => l_data
3170 ,p_msg_index_out => l_msg_index_out);
3171 x_msg_data := l_data;
3172 x_msg_count := l_msg_count;
3173 ELSE
3174 x_msg_count := l_msg_count;
3175 END IF;
3176 x_msg_data := l_msg_data;
3177 IF p_pa_debug_mode = 'Y' THEN
3178 pa_debug.write_file('VALIDATE_BUDGETS ' || x_msg_data,5);
3179 END IF;
3180 pa_debug.reset_err_stack;
3181 RAISE;
3182
3183 WHEN others THEN
3184
3185 IF budgets_for_upgrade_cur%ISOPEN THEN
3186 CLOSE budgets_for_upgrade_cur;
3187 END IF;
3188 IF projects_for_upgrade_cur1%ISOPEN THEN
3189 CLOSE projects_for_upgrade_cur1;
3190 ELSIF projects_for_upgrade_cur2%ISOPEN THEN
3191 CLOSE projects_for_upgrade_cur2;
3192 ELSIF projects_for_upgrade_cur3%ISOPEN THEN
3193 CLOSE projects_for_upgrade_cur3;
3194 ELSIF projects_for_upgrade_cur%ISOPEN THEN
3195 CLOSE projects_for_upgrade_cur;
3196 END IF;
3197 -- start of changes for bug 2853511
3198 IF attached_budget_types_cur%ISOPEN THEN
3199 CLOSE attached_budget_types_cur;
3200 END IF;
3201 -- end of changes for bug 2853511
3202 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3203 x_msg_count := 1;
3204 x_msg_data := SQLERRM;
3205 FND_MSG_PUB.add_exc_msg
3206 ( p_pkg_name => 'PA_FP_UPGRADE_PKG'
3207 ,p_procedure_name => 'validate_budgets'
3208 ,p_error_text => sqlerrm);
3209 IF p_pa_debug_mode = 'Y' THEN
3210 pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
3211 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3212 pa_debug.write_file('VALIDATE_BUDGETS ' || pa_debug.G_Err_Stack,5);
3213 END IF;
3214 pa_debug.reset_err_stack;
3215 RAISE;
3216
3217 END VALIDATE_BUDGETS;
3218
3219 /*==================================================================
3220 This api is used to do validations required at project level for
3221 upgrade. This api is called both in PRE_UPGRADE and UPGRADE modes.
3222
3223 Bug#2731534. Checking billing flag doesn't suffice the availablity
3224 of the conversion attributes for the project. It takes care of the
3225 revenue conversion attributes only. For cost attributes, if we do
3226 not find cost conversion attributes in pa_projects_all, then get
3227 them from the implementations table for the project's OU and if
3228 we do not find them there then raise exception for the project.
3229 ==================================================================*/
3230
3231 PROCEDURE VALIDATE_PROJECT (
3232 p_project_id IN pa_budget_versions.project_id%TYPE
3233 ,x_validation_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3234 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3235 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3236 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3237 AS
3238
3239 l_msg_count NUMBER := 0;
3240 l_data VARCHAR2(2000);
3241 l_msg_data VARCHAR2(2000);
3242 l_msg_index_out NUMBER;
3243 l_return_status VARCHAR2(2000);
3244 l_debug_mode VARCHAR2(30);
3245
3246
3247 l_multi_currency_billing_flag pa_projects_all.multi_currency_billing_flag%TYPE;
3248 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
3249 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
3250 l_project_bil_rate_type pa_projects_all.project_bil_rate_type%TYPE;
3251 l_projfunc_bil_rate_type pa_projects_all.projfunc_bil_rate_type%TYPE;
3252 l_project_cost_rate_type pa_projects_all.project_rate_type%TYPE;
3253 l_projfunc_cost_rate_type pa_projects_all.projfunc_cost_rate_type%TYPE;
3254
3255 BEGIN
3256
3257 x_msg_count := 0;
3258 x_return_status := FND_API.G_RET_STS_SUCCESS;
3259 pa_debug.set_err_stack('PA_FP_UPGRADE_PKG.VALIDATE_PROJECT');
3260 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3261 l_debug_mode := NVL(l_debug_mode, 'Y');
3262 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
3263
3264 -- Check for business rules violations
3265 IF p_pa_debug_mode = 'Y' THEN
3266 pa_debug.g_err_stage:= 'Validating input parameters';
3267 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3268 END IF;
3269
3270 --Check if plan version id is null
3271
3272 IF (p_project_id IS NULL)
3273 THEN
3274
3275 IF p_pa_debug_mode = 'Y' THEN
3276 pa_debug.g_err_stage:= 'p_project_id = '|| p_project_id;
3277 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3278 pa_debug.g_err_stage:= 'Invalid Arguments Passed';
3279 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3280 END IF;
3281
3282 PA_UTILS.ADD_MESSAGE
3283 (p_app_short_name => 'PA',
3284 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
3285 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3286 END IF;
3287
3288 -- Set x_validation_status to 'Y' initailly as and when we hit upona exception
3289 -- we update it to 'N'
3290
3291 x_validation_status := 'Y';
3292
3293 -- Fetch the project currencies, MCB flag and cost rate types
3294
3295 /* This api fetches the cost rate types from pa_projects_all table,
3296 if they aren't defined for project level then they are fetched from
3297 pa_implementations table */
3298
3299 PA_FIN_PLAN_UTILS.Get_Project_Curr_Attributes
3300 ( p_project_id => p_project_id
3301 ,x_multi_currency_billing_flag => l_multi_currency_billing_flag
3302 ,x_project_currency_code => l_project_currency_code
3303 ,x_projfunc_currency_code => l_projfunc_currency_code
3304 ,x_project_cost_rate_type => l_project_cost_rate_type
3305 ,x_projfunc_cost_rate_type => l_projfunc_cost_rate_type
3306 ,x_project_bil_rate_type => l_project_bil_rate_type
3307 ,x_projfunc_bil_rate_type => l_projfunc_bil_rate_type
3308 ,x_return_status => l_return_status
3309 ,x_msg_count => l_msg_count
3310 ,x_msg_data => l_msg_data );
3311
3312 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3313 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3314 END IF;
3315
3316 -- check if project currency and projfunc currency are not equal
3317 -- then we require conversion attributes at project level for upgrade
3318
3319 IF l_projfunc_currency_code <> l_project_currency_code AND
3320 (l_multi_currency_billing_flag <> 'Y' OR
3321 l_project_cost_rate_type IS NULL OR -- bug 2731534
3322 l_projfunc_cost_rate_type IS NULL) -- bug 2731534
3323 THEN
3324
3325 -- set x_validation_status to 'N'
3326
3327 x_validation_status := 'N';
3328
3329 -- Insert into audit table
3330
3331 pa_fp_upgrade_pkg.Insert_Audit_Record(
3332 p_project_id => p_project_id
3333 ,p_budget_type_code => NULL
3334 ,p_proj_fp_options_id => NULL
3335 ,p_fin_plan_option_level_code => PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PROJECT
3336 ,p_basis_cost_version_id => NULL
3337 ,p_basis_rev_version_id => NULL
3338 ,p_basis_cost_bem => NULL
3339 ,p_basis_rev_bem => NULL
3340 ,p_upgraded_flag => 'N'
3341 ,p_failure_reason_code => 'NO_CONV_ATTR_FOR_PROJ');
3342
3343 END IF;
3344 IF p_pa_debug_mode = 'Y' THEN
3345 pa_debug.g_err_stage:= 'Exiting validate_project';
3346 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3347 END IF;
3348 pa_debug.reset_err_stack;
3349
3350 EXCEPTION
3351
3352 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3353
3354 x_validation_status := 'N';
3355 x_return_status := FND_API.G_RET_STS_ERROR;
3356 l_msg_count := FND_MSG_PUB.count_msg;
3357 IF l_msg_count = 1 THEN
3358 PA_INTERFACE_UTILS_PUB.get_messages
3359 (p_encoded => FND_API.G_TRUE
3360 ,p_msg_index => 1
3361 ,p_msg_count => l_msg_count
3362 ,p_msg_data => l_msg_data
3363 ,p_data => l_data
3364 ,p_msg_index_out => l_msg_index_out);
3365 x_msg_data := l_data;
3366 x_msg_count := l_msg_count;
3367 ELSE
3368 x_msg_count := l_msg_count;
3369 x_msg_data := l_msg_data;
3370 END IF;
3371
3372 IF p_pa_debug_mode = 'Y' THEN
3373 pa_debug.write_file('VALIDATE_PROJECT ' || x_msg_data,5);
3374 END IF;
3375 pa_debug.reset_err_stack;
3376 RAISE;
3377
3378 WHEN others THEN
3379
3380 x_validation_status := 'N';
3381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3382 x_msg_count := 1;
3383 x_msg_data := SQLERRM;
3384 FND_MSG_PUB.add_exc_msg
3385 ( p_pkg_name => 'PA_FP_UPGRADE_PKG'
3386 ,p_procedure_name => 'VALIDATE_PROJECT'
3387 ,p_error_text => sqlerrm);
3388 IF p_pa_debug_mode = 'Y' THEN
3389 pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
3390 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3391 pa_debug.write_file('VALIDATE_PROJECT ' || pa_debug.G_Err_Stack,5);
3392 END IF;
3393 pa_debug.reset_err_stack;
3394 RAISE;
3395
3396 END VALIDATE_PROJECT;
3397
3398 /*==================================================================
3399 This api would be called both in 'PRE_UPGRADE' mode and 'UPGRADE'
3400 mode and does all the necesary business validations that are to be
3401 done at the budget type level.
3402 ==================================================================*/
3403
3404 PROCEDURE VALIDATE_PROJECT_PLAN_TYPE (
3405 p_project_id IN pa_budget_versions.project_id%TYPE
3406 ,p_budget_type_code IN pa_budget_versions.budget_type_code%TYPE
3407 ,x_validation_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3408 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3409 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3410 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3411 AS
3412
3413 l_msg_count NUMBER := 0;
3414 l_data VARCHAR2(2000);
3415 l_msg_data VARCHAR2(2000);
3416 l_msg_index_out NUMBER;
3417 l_return_status VARCHAR2(2000);
3418 l_debug_mode VARCHAR2(30);
3419 l_err_code NUMBER;
3420 l_err_stage VARCHAR2(2000);
3421 l_err_stack VARCHAR2(2000);
3422
3423 l_draft_version_id pa_budget_versions.budget_version_id%TYPE;
3424 l_budget_status_code pa_budget_versions.budget_status_code%TYPE;
3425
3426 ---------- Variables Used for get_budget_ctrl_options api --------------
3427 l_fck_req_flag VARCHAR2(1);
3428 l_bdgt_intg_flag VARCHAR2(1);
3429 l_bdgt_ver_id pa_budget_versions.budget_version_id%TYPE;
3430 l_encum_type_id pa_budgetary_control_options.encumbrance_type_id%TYPE;
3431 l_balance_type pa_budgetary_control_options.balance_type%TYPE ;
3432 ---------- Variables Used for get_budget_ctrl_options api --------------
3433
3434 BEGIN
3435
3436 x_msg_count := 0;
3437 x_return_status := FND_API.G_RET_STS_SUCCESS;
3438 pa_debug.set_err_stack('PA_FP_UPGRADE_PKG.VALIDATE_PROJECT_PLAN_TYPE');
3439 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3440 l_debug_mode := NVL(l_debug_mode, 'Y');
3441 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
3442
3443 -- Check for business rules violations
3444
3445 IF (p_project_id IS NULL) OR (p_budget_type_code IS NULL)
3446 THEN
3447
3448 IF p_pa_debug_mode = 'Y' THEN
3449 pa_debug.g_err_stage:= 'p_project_id = '|| p_project_id;
3450 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3451 pa_debug.g_err_stage:= 'p_budget_type_code = '|| p_budget_type_code;
3452 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3453 pa_debug.g_err_stage:= 'Invalid Arguments Passed';
3454 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3455 END IF;
3456
3457 PA_UTILS.ADD_MESSAGE
3458 (p_app_short_name => 'PA',
3459 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
3460
3461
3462 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3463 END IF;
3464
3465 -- Initially setting x_validation_status to yes.
3466 -- as and when we hit upon an error we set the x_validation_status to 'N'
3467 -- but we still proceed to report all the exceptions
3468
3469 x_validation_status := 'Y';
3470
3471 --Check if budetary controls exist for the budget type and project combination.
3472
3473 IF p_pa_debug_mode = 'Y' THEN
3474 pa_debug.g_err_stage := 'Calling get_budget_ctrl_options';
3475 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3476 END IF;
3477
3478 PA_BUDGET_FUND_PKG.get_budget_ctrl_options (
3479 p_project_id => p_project_id,
3480 p_budget_type_code => p_budget_type_code,
3481 p_calling_mode => 'BUDGET',
3482 x_fck_req_flag => l_fck_req_flag,
3483 x_bdgt_intg_flag => l_bdgt_intg_flag,
3484 x_bdgt_ver_id => l_bdgt_ver_id,
3485 x_encum_type_id => l_encum_type_id,
3486 x_balance_type => l_balance_type,
3487 x_return_status => l_return_status,
3488 x_msg_count => l_msg_count,
3489 x_msg_data => l_msg_data);
3490
3491 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3492 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3493 END IF;
3494
3495
3496 --Check if any budget version exists in submitted status having this budget type
3497
3498 IF NVL(l_bdgt_intg_flag,'Y') <> 'N' OR NVL(l_fck_req_flag,'Y')<> 'N' THEN -- Bug:- 2686836
3499
3500 -- Set x_validation_status to 'N' as the this budget type and
3501 -- all the budget versions can't be upgraded.
3502
3503 x_validation_status := 'N';
3504
3505 -- Insert the exception into audit table
3506
3507 pa_fp_upgrade_pkg.Insert_Audit_Record(
3508 p_project_id => p_project_id
3509 ,p_budget_type_code => p_budget_type_code
3510 ,p_proj_fp_options_id => NULL
3511 ,p_fin_plan_option_level_code => PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
3512 ,p_basis_cost_version_id => NULL
3513 ,p_basis_rev_version_id => NULL
3514 ,p_basis_cost_bem => NULL
3515 ,p_basis_rev_bem => NULL
3516 ,p_upgraded_flag => 'N'
3517 ,p_failure_reason_code => 'BUDGET_INTEGRATION_EXISTS');
3518 END IF;
3519
3520 -- Check if for the budget type if any of the budget versions to be upgraded is in
3521 -- submitted status. if so don't upgrade.
3522 IF p_pa_debug_mode = 'Y' THEN
3523 pa_debug.g_err_stage := 'Calling get_draft_version_id';
3524 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3525 END IF;
3526
3527 l_draft_version_id := Null;
3528
3529 pa_budget_utils.get_draft_version_id (
3530 x_project_id => p_project_id
3531 ,x_budget_type_code => p_budget_type_code
3532 ,x_budget_version_id => l_draft_version_id
3533 ,x_err_code => l_err_code
3534 ,x_err_stage => l_err_stage
3535 ,x_err_stack => l_err_stack);
3536
3537 -- bug 2853511 draft version id could be deleted after baselining the draft version
3538 -- and thus draft version needn't exist
3539
3540 IF l_draft_version_id IS NOT NULL THEN
3541 IF p_pa_debug_mode = 'Y' THEN
3542 pa_debug.g_err_stage := 'draft_version_id = '|| l_draft_version_id;
3543 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3544 END IF;
3545
3546 -- The draft version id fetched may be either working or submitted version
3547 -- If the draft version is in submitted status this budget type can't be upgraded
3548
3549 BEGIN
3550 SELECT budget_status_code
3551 INTO l_budget_status_code
3552 FROM pa_budget_versions
3553 WHERE budget_version_id = l_draft_version_id;
3554 EXCEPTION
3555 WHEN OTHERS THEN
3556 IF attached_plan_types_cur%ISOPEN THEN
3557 CLOSE attached_plan_types_cur;
3558 END IF;
3559 IF p_pa_debug_mode = 'Y' THEN
3560 pa_debug.g_err_stage:='draft_version_id is null or invalid'||SQLERRM;
3561 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3562 END IF;
3563 RAISE;
3564 END;
3565
3566 IF l_budget_status_code = PA_FP_CONSTANTS_PKG.G_BUDGET_STATUS_SUBMITTED THEN
3567
3568 -- Set x_validation_status to 'N' as the this budget type and
3569 -- all the budget versions can't be upgraded.
3570
3571 x_validation_status := 'N';
3572
3573 -- Insert into audit table
3574 pa_fp_upgrade_pkg.Insert_Audit_Record(
3575 p_project_id => p_project_id
3576 ,p_budget_type_code => p_budget_type_code
3577 ,p_proj_fp_options_id => NULL
3578 ,p_fin_plan_option_level_code => PA_FP_CONSTANTS_PKG.G_OPTION_LEVEL_PLAN_TYPE
3579 ,p_basis_cost_version_id => NULL
3580 ,p_basis_rev_version_id => NULL
3581 ,p_basis_cost_bem => NULL
3582 ,p_basis_rev_bem => NULL
3583 ,p_upgraded_flag => 'N'
3584 ,p_failure_reason_code => 'SUBMIT_STATUS_VERSION_EXISTS');
3585 END IF;
3586 END IF;
3587
3588 IF p_pa_debug_mode = 'Y' THEN
3589 pa_debug.g_err_stage:= 'Exiting VALIDATE_PROJECT_PLAN_TYPE';
3590 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3591 END IF;
3592 pa_debug.reset_err_stack;
3593
3594 EXCEPTION
3595
3596 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3597
3598 x_validation_status := 'N';
3599 x_return_status := FND_API.G_RET_STS_ERROR;
3600 l_msg_count := FND_MSG_PUB.count_msg;
3601 IF l_msg_count = 1 THEN
3602 PA_INTERFACE_UTILS_PUB.get_messages
3603 (p_encoded => FND_API.G_TRUE
3604 ,p_msg_index => 1
3605 ,p_msg_count => l_msg_count
3606 ,p_msg_data => l_msg_data
3607 ,p_data => l_data
3608 ,p_msg_index_out => l_msg_index_out);
3609 x_msg_data := l_data;
3610 x_msg_count := l_msg_count;
3611 ELSE
3612 x_msg_count := l_msg_count;
3613 x_msg_data := l_msg_data;
3614 END IF;
3615
3616 IF p_pa_debug_mode = 'Y' THEN
3617 pa_debug.write_file('VALIDATE_PROJECT_PLAN_TYPE ' || x_msg_data,5);
3618 END IF;
3619 pa_debug.reset_err_stack;
3620 RAISE;
3621
3622 WHEN others THEN
3623
3624 x_validation_status := 'N';
3625 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3626 x_msg_count := 1;
3627 x_msg_data := SQLERRM;
3628 FND_MSG_PUB.add_exc_msg
3629 ( p_pkg_name => 'PA_FP_UPGRADE_PKG'
3630 ,p_procedure_name => 'VALIDATE_PROJECT_PLAN_TYPE'
3631 ,p_error_text => sqlerrm);
3632 IF p_pa_debug_mode = 'Y' THEN
3633 pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
3634 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3635 pa_debug.write_file('VALIDATE_PROJECT_PLAN_TYPE ' || pa_debug.G_Err_Stack,5);
3636 END IF;
3637 pa_debug.reset_err_stack;
3638 RAISE;
3639
3640 END VALIDATE_PROJECT_PLAN_TYPE;
3641
3642 /*==================================================================
3643 This api is used to validate a budget version in pre_upgrade mode.
3644 The api reports all the tasks along with different resource
3645 groups ,if the task id is planned both at resource level and
3646 resource group level(referred to as 'mixed planning level').
3647
3648 1.0)In the api, for each task we cache all the resource groups
3649 planned for along with the planning level in pl/sql tables
3650 i)if for the task mixed planning level exists then they are
3651 written to PA_FP_UPG_EXCEPTIONS_TMP table for reporting
3652 puposes.
3653 ii)else we flush the plsql tables and move to next task.
3654 ====================================================================*/
3655
3656 PROCEDURE VALIDATE_BUDGET_VERSION
3657 ( p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
3658 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3659 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3660 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3661 AS
3662
3663 l_msg_count NUMBER := 0;
3664 l_data VARCHAR2(2000);
3665 l_msg_data VARCHAR2(2000);
3666 l_msg_index_out NUMBER;
3667 l_return_status VARCHAR2(2000);
3668 l_debug_mode VARCHAR2(30);
3669
3670 l_task_id pa_tasks.task_id%TYPE;
3671
3672 CURSOR budget_version_info_cur
3673 (c_budget_version_id pa_budget_versions.budget_version_id%TYPE) IS
3674 SELECT project_id,
3675 budget_type_code,
3676 resource_list_id
3677 FROM pa_budget_versions
3678 WHERE budget_Version_id = c_budget_version_id;
3679
3680 budget_version_info_rec budget_version_info_cur%ROWTYPE;
3681
3682 BEGIN
3683
3684 x_msg_count := 0;
3685 x_return_status := FND_API.G_RET_STS_SUCCESS;
3686 pa_debug.set_err_stack('PA_FP_UPGRADE_PKG.validate_budget_version');
3687 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3688 l_debug_mode := NVL(l_debug_mode, 'Y');
3689 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
3690
3691 -- Check for business rules violations
3692 IF p_pa_debug_mode = 'Y' THEN
3693 pa_debug.g_err_stage:= 'Validating input parameters';
3694 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3695 END IF;
3696
3697 --Check if plan version id is null
3698
3699 IF (p_budget_version_id IS NULL)
3700 THEN
3701 IF p_pa_debug_mode = 'Y' THEN
3702 pa_debug.g_err_stage:= 'p_budget_version_id = '|| p_budget_version_id;
3703 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3704 pa_debug.g_err_stage:= 'Invalid Arguments Passed';
3705 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3706 END IF;
3707
3708 PA_UTILS.ADD_MESSAGE
3709 (p_app_short_name => 'PA',
3710 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
3711 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3712 END IF;
3713
3714 OPEN budget_version_info_cur(p_budget_version_id);
3715 FETCH budget_version_info_cur INTO budget_version_info_rec;
3716 CLOSE budget_version_info_cur;
3717
3718 IF p_pa_debug_mode = 'Y' THEN
3719 pa_debug.g_err_stage:= 'Exiting validate_budget_version';
3720 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
3721 END IF;
3722 pa_debug.reset_err_stack;
3723
3724 EXCEPTION
3725
3726 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
3727 x_return_status := FND_API.G_RET_STS_ERROR;
3728 l_msg_count := FND_MSG_PUB.count_msg;
3729 IF l_msg_count = 1 THEN
3730 PA_INTERFACE_UTILS_PUB.get_messages
3731 (p_encoded => FND_API.G_TRUE
3732 ,p_msg_index => 1
3733 ,p_msg_count => l_msg_count
3734 ,p_msg_data => l_msg_data
3735 ,p_data => l_data
3736 ,p_msg_index_out => l_msg_index_out);
3737 x_msg_data := l_data;
3738 x_msg_count := l_msg_count;
3739 ELSE
3740 x_msg_count := l_msg_count;
3741 x_msg_data := l_msg_data;
3742 END IF;
3743
3744 IF p_pa_debug_mode = 'Y' THEN
3745 pa_debug.write_file('VALIDATE_BUDGET_VERSION ' || x_msg_data,5);
3746 END IF;
3747 pa_debug.reset_err_stack;
3748 RAISE;
3749
3750 WHEN others THEN
3751
3752 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3753 x_msg_count := 1;
3754 x_msg_data := SQLERRM;
3755 FND_MSG_PUB.add_exc_msg
3756 ( p_pkg_name => 'PA_FP_UPGRADE_PKG'
3757 ,p_procedure_name => 'VALIDATE_BUDGET_VERSION'
3758 ,p_error_text => SQLERRM);
3759 IF p_pa_debug_mode = 'Y' THEN
3760 pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
3761 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
3762 pa_debug.write_file('VALIDATE_BUDGET_VERSION ' || pa_debug.G_Err_Stack,5);
3763 END IF;
3764 pa_debug.reset_err_stack;
3765 RAISE;
3766
3767 END VALIDATE_BUDGET_VERSION;
3768
3769 --This procedure will upgrade the budget lines of a budget version so that all the amount/quantity columns
3770 --are populated. Please refer to the bug to see more discussion on this matter
3771
3772 --ASSUMPTIONS
3773 --1.Input is ordered by resource assignment id ,quantities with NULLS coming first
3774 --2.0(Zero)s are passed as input for amounts instead of NULL.
3775 PROCEDURE Apply_Calculate_FPM_Rules
3776 ( p_preference_code IN pa_proj_fp_options.fin_plan_preference_code%TYPE
3777 ,p_resource_assignment_id_tbl IN SYSTEM.pa_num_tbl_type
3778 ,p_rate_based_flag_tbl IN SYSTEM.pa_varchar2_1_tbl_type
3779 ,p_quantity_tbl IN SYSTEM.pa_num_tbl_type
3780 ,p_txn_raw_cost_tbl IN SYSTEM.pa_num_tbl_type
3781 ,p_txn_burdened_cost_tbl IN SYSTEM.pa_num_tbl_type
3782 ,p_txn_revenue_tbl IN SYSTEM.pa_num_tbl_type
3783 ,p_calling_module IN VARCHAR2 -- bug 5007734
3784 ,x_quantity_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type --File.Sql.39 bug 4440895
3785 ,x_txn_raw_cost_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type --File.Sql.39 bug 4440895
3786 ,x_txn_burdened_cost_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type --File.Sql.39 bug 4440895
3787 ,x_txn_revenue_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type --File.Sql.39 bug 4440895
3788 ,x_raw_cost_override_rate_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type --File.Sql.39 bug 4440895
3789 ,x_burd_cost_override_rate_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type --File.Sql.39 bug 4440895
3790 ,x_bill_override_rate_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type --File.Sql.39 bug 4440895
3791 ,x_non_rb_ra_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type --File.Sql.39 bug 4440895
3792 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3793 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3794 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3795 IS
3796
3797 --Start of variables used for debugging
3798 l_return_status VARCHAR2(1);
3799 l_msg_count NUMBER := 0;
3800 l_msg_data VARCHAR2(2000);
3801 l_data VARCHAR2(2000);
3802 l_msg_index_out NUMBER;
3803 l_debug_mode VARCHAR2(30);
3804 l_module_name VARCHAR2(200) := 'PAFPUPGB.Apply_Calculate_FPM_Rules';
3805
3806 --Stores previous non rate based resource assignment id
3807 l_prev_non_rb_ra_id pa_resource_assignments.resource_assignment_id%TYPE;
3808
3809 --Processing will be done in the following local variables
3810 l_quantity_tab SYSTEM.pa_num_tbl_type;
3811 l_txn_raw_cost_tab SYSTEM.pa_num_tbl_type;
3812 l_txn_burdened_cost_tab SYSTEM.pa_num_tbl_type;
3813 l_txn_revenue_tab SYSTEM.pa_num_tbl_type;
3814 l_cost_rate_override_tab SYSTEM.pa_num_tbl_type;
3815 l_burden_rate_override_tab SYSTEM.pa_num_tbl_type;
3816 l_bill_rate_override_tab SYSTEM.pa_num_tbl_type;
3817
3818 l_stage VARCHAR2(100);
3819
3820 BEGIN
3821
3822 x_msg_count := 0;
3823 x_return_status := FND_API.G_RET_STS_SUCCESS;
3824 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3825 l_debug_mode := NVL(l_debug_mode, 'Y');
3826
3827 -- Set curr function
3828 pa_debug.set_curr_function(
3829 p_function =>'PAFPUPGB.Apply_Calculate_FPM_Rules'
3830 ,p_debug_mode => l_debug_mode );
3831
3832 IF l_debug_mode = 'Y' THEN
3833 pa_debug.g_err_stage:='Validating input parameters';
3834 pa_debug.write( l_module_name,pa_debug.g_err_stage,3);
3835 END IF;
3836
3837 IF NVL(p_preference_code,'-99') NOT IN ('COST_ONLY','REVENUE_ONLY','COST_AND_REV_SAME') OR
3838 p_quantity_tbl.COUNT <> p_resource_assignment_id_tbl.COUNT OR
3839 p_quantity_tbl.COUNT <> p_rate_based_flag_tbl.COUNT OR
3840 p_quantity_tbl.COUNT <> p_txn_raw_cost_tbl.COUNT OR
3841 p_quantity_tbl.COUNT <> p_txn_burdened_cost_tbl.COUNT OR
3842 p_quantity_tbl.COUNT <> p_txn_revenue_tbl.COUNT THEN
3843
3844 IF l_debug_mode = 'Y' THEN
3845 pa_debug.g_err_stage:='p_preference_code is '||p_preference_code;
3846 pa_debug.write( l_module_name,pa_debug.g_err_stage,5);
3847
3848 pa_debug.g_err_stage:='p_quantity_tbl.COUNT is '||p_quantity_tbl.COUNT;
3849 pa_debug.write( l_module_name,pa_debug.g_err_stage,5);
3850
3851 pa_debug.g_err_stage:='p_txn_raw_cost_tbl.COUNT is '||p_txn_raw_cost_tbl.COUNT;
3852 pa_debug.write( l_module_name,pa_debug.g_err_stage,5);
3853
3854 pa_debug.g_err_stage:='p_txn_burdened_cost_tbl.COUNT is '||p_txn_burdened_cost_tbl.COUNT;
3855 pa_debug.write( l_module_name,pa_debug.g_err_stage,5);
3856
3857 pa_debug.g_err_stage:='p_txn_revenue_tbl.COUNT is '||p_txn_revenue_tbl.COUNT;
3858 pa_debug.write( l_module_name,pa_debug.g_err_stage,5);
3859
3860 END IF;
3861
3862 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3863 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
3864
3865 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
3866
3867 END IF;
3868
3869 IF p_quantity_tbl.COUNT=0 THEN
3870
3871 IF l_debug_mode = 'Y' THEN
3872 pa_debug.g_err_stage:='Quantity Table is empty -> returning';
3873 pa_debug.write( l_module_name,pa_debug.g_err_stage,3);
3874 END IF;
3875 pa_debug.reset_curr_function;
3876 RETURN;
3877
3878 END IF;
3879
3880 --Prepare amount tbls for processing
3881 l_quantity_tab := p_quantity_tbl;
3882 l_txn_raw_cost_tab := p_txn_raw_cost_tbl;
3883 l_txn_burdened_cost_tab := p_txn_burdened_cost_tbl;
3884 l_txn_revenue_tab := p_txn_revenue_tbl;
3885 l_cost_rate_override_tab := SYSTEM.pa_num_tbl_type();
3886 l_burden_rate_override_tab := SYSTEM.pa_num_tbl_type();
3887 l_bill_rate_override_tab := SYSTEM.pa_num_tbl_type();
3888 l_cost_rate_override_tab.extend(p_quantity_tbl.COUNT);
3889 l_burden_rate_override_tab.extend(p_quantity_tbl.COUNT);
3890 l_bill_rate_override_tab.extend(p_quantity_tbl.COUNT);
3891
3892 --Prepare the tbl that holds the RAs for which rate based flag should be changed.
3893 x_non_rb_ra_id_tbl := SYSTEM.pa_num_tbl_type();
3894
3895
3896 FOR i IN l_quantity_tab.first .. l_quantity_tab.last LOOP
3897
3898 /* check if planning resource is rate based and quantity does not exists
3899 * then mark the planning resource as non-rate based and change the
3900 * UOM as Currency
3901 */
3902 IF (p_rate_based_flag_tbl(i) = 'Y' AND l_quantity_tab(i) = 0) THEN
3903
3904 IF l_prev_non_rb_ra_id IS NULL OR (l_prev_non_rb_ra_id <> p_resource_assignment_id_tbl(i)) THEN
3905
3906 l_stage := 'This is rate based resource quantity doesnot exists';
3907 x_non_rb_ra_id_tbl.extend;
3908 x_non_rb_ra_id_tbl(x_non_rb_ra_id_tbl.COUNT) := p_resource_assignment_id_tbl(i);
3909 l_prev_non_rb_ra_id := p_resource_assignment_id_tbl(i);
3910
3911 END IF;
3912
3913 END IF;
3914
3915 IF p_preference_code = 'COST_ONLY' THEN
3916
3917 l_txn_revenue_tab(i) := NULL;
3918 l_bill_rate_override_tab(i) := NULL;
3919
3920
3921 --this portion will check quantity is zero and amounts are null/zero
3922 IF l_quantity_tab(i) = 0 THEN
3923
3924 If (Nvl(l_txn_burdened_cost_tab(i),0) <> 0 and
3925 nvl(l_txn_raw_cost_tab(i),0) <> 0 ) Then
3926
3927 l_stage := 'PRC:1';
3928 l_quantity_tab(i) := l_txn_raw_cost_tab(i);
3929 l_cost_rate_override_tab(i) := l_txn_raw_cost_tab(i)/l_quantity_tab(i);
3930 l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
3931
3932 Elsif (Nvl(l_txn_burdened_cost_tab(i),0) <> 0 and
3933 nvl(l_txn_raw_cost_tab(i),0) = 0 ) Then
3934
3935 l_stage := 'PRC:2';
3936 l_quantity_tab(i) := l_txn_burdened_cost_tab(i);
3937 l_txn_raw_cost_tab(i) := l_txn_burdened_cost_tab(i);
3938 l_cost_rate_override_tab(i) := l_txn_raw_cost_tab(i)/l_quantity_tab(i);
3939 l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
3940
3941 Elsif (Nvl(l_txn_burdened_cost_tab(i),0) = 0
3942 and nvl(l_txn_raw_cost_tab(i),0) <> 0 ) Then
3943
3944 l_stage := 'PRC:3';
3945 l_quantity_tab(i) := l_txn_raw_cost_tab(i);
3946 --l_txn_burdened_cost_tab(i) := l_txn_raw_cost_tab(i);
3947 l_cost_rate_override_tab(i) := l_txn_raw_cost_tab(i)/l_quantity_tab(i);
3948 --l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
3949
3950 End If;
3951 -- this portion of code checks quantity not zero and amounts are zero
3952 Else
3953 If p_rate_based_flag_tbl(i) = 'N' OR
3954 l_prev_non_rb_ra_id = p_resource_assignment_id_tbl(i) Then -- added for bug 5007734:
3955
3956 -- bug 5007734: Making quantity = amounts only for upgrade flow, for the planning transactions
3957 -- which are non rate based or going to be made non rate based.
3958 IF p_calling_module = 'UPGRADE' THEN
3959
3960 If (l_txn_raw_cost_tab(i) <> 0
3961 and l_txn_raw_cost_tab(i) <> l_quantity_tab(i)) Then
3962
3963 l_stage := 'PRC:4';
3964 l_quantity_tab(i) := l_txn_raw_cost_tab(i);
3965 l_cost_rate_override_tab(i) := 1;
3966
3967 Else /* if (l_txn_raw_cost_tab(i) = 0 and l_txn_raw_cost_tab(i) <> l_quantity_tab(i))
3968 or
3969 (txn_raw_cost = quantity and txn_raw_cost <> 0) Then */
3970
3971 l_stage := 'PRC:5'; --Bug 5076350
3972 If (l_txn_raw_cost_tab(i) = l_quantity_tab(i) and nvl(l_txn_raw_cost_tab(i),0) <> 0) Then
3973 l_cost_rate_override_tab(i) := 1;
3974 Elsif nvl(l_txn_burdened_cost_tab(i),0) <> 0 Then
3975 l_txn_raw_cost_tab(i) := l_quantity_tab(i);
3976 l_cost_rate_override_tab(i) := 1;
3977 End if;
3978 End If;
3979
3980 If nvl(l_txn_burdened_cost_tab(i),0) <> 0 Then
3981
3982 l_stage := 'PRC:6';
3983 l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
3984
3985 Else
3986
3987 l_stage := 'PRC:7';
3988 --l_txn_burdened_cost_tab(i) := l_txn_raw_cost_tab(i);
3989 --l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
3990
3991 End If;
3992 END IF; -- end of bug 5007734
3993 Else --Rate Based Flag ='Y'
3994
3995 If l_txn_raw_cost_tab(i) <> 0 Then
3996
3997 l_stage := 'PRC:8';
3998 l_cost_rate_override_tab(i) := l_txn_raw_cost_tab(i)/l_quantity_tab(i);
3999
4000 Else
4001
4002 l_stage := 'PRC:9';
4003 --l_txn_raw_cost_tab(i) := l_quantity_tab(i);
4004 --l_cost_rate_override_tab(i) := l_txn_raw_cost_tab(i)/l_quantity_tab(i);
4005
4006 End If;
4007
4008 If nvl(l_txn_burdened_cost_tab(i),0) <> 0 Then
4009
4010 l_stage := 'PRC:10';
4011 l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
4012
4013 Else
4014
4015 l_stage := 'PRC:11';
4016 --l_txn_burdened_cost_tab(i) := l_txn_raw_cost_tab(i);
4017 --l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
4018
4019 End If;
4020
4021 End If; --If p_rate_based_flag_tbl(i) = 'N' Then
4022
4023 End If; --IF l_quantity_tab(i) = 0 THEN
4024
4025 Elsif p_preference_code = 'REVENUE_ONLY' Then
4026
4027 l_txn_raw_cost_tab(i) := NULL;
4028 l_txn_burdened_cost_tab(i) := NULL;
4029 l_cost_rate_override_tab(i) := NULL;
4030 l_burden_rate_override_tab(i) := NULL;
4031
4032 If l_quantity_tab(i) = 0 then
4033
4034 l_stage := 'PRC:12';
4035 If (nvl(l_txn_revenue_tab(i),0) <> 0 ) Then
4036 l_stage := 'PRC:13';
4037 l_quantity_tab(i) := l_txn_revenue_tab(i);
4038 l_bill_rate_override_tab(i) := 1;
4039 End If;
4040
4041 Else
4042
4043 If p_rate_based_flag_tbl(i) = 'N' OR
4044 l_prev_non_rb_ra_id = p_resource_assignment_id_tbl(i) Then -- added for bug 5007734:
4045 -- bug 5007734: Making quantity = amounts only for upgrade flow, for the planning transactions
4046 -- which are non rate based or going to be made non rate based.
4047 IF p_calling_module = 'UPGRADE' THEN
4048
4049 If (nvl(l_txn_revenue_tab(i),0) <> 0
4050 and l_txn_revenue_tab(i) <> l_quantity_tab(i)) Then
4051
4052 l_stage := 'PRC:14';
4053 l_quantity_tab(i) := l_txn_revenue_tab(i);
4054 l_bill_rate_override_tab(i) := 1;
4055
4056 Else /* if nvl(l_txn_revenue_tab(i),0) = 0 or quantity = revenue Then */
4057
4058 l_stage := 'PRC:15';
4059 l_txn_revenue_tab(i) := l_quantity_tab(i);
4060 l_bill_rate_override_tab(i) := 1;
4061
4062 End If;
4063 END IF; -- end of bug 5007734
4064
4065 Else-- Rate Based RA
4066
4067 If nvl(l_txn_revenue_tab(i),0) <> 0 Then
4068
4069 l_stage := 'PRC:16';
4070 l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4071
4072 Else
4073
4074 l_stage := 'PRC:17';
4075 --l_txn_revenue_tab(i) := l_quantity_tab(i);
4076 --l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4077
4078 End If;
4079
4080 End If;--If p_rate_based_flag_tbl(i) = 'N' Then
4081
4082 End If; --If l_quantity_tab(i) = 0 then
4083
4084 Elsif p_preference_code = 'COST_AND_REV_SAME' then
4085
4086 If l_quantity_tab(i) = 0 then
4087
4088 l_stage := 'PRC:18';
4089 If (Nvl(l_txn_burdened_cost_tab(i),0) <> 0
4090 and nvl(l_txn_raw_cost_tab(i),0) <> 0 ) Then --{
4091
4092 l_stage := 'PRC:19';
4093 l_quantity_tab(i) := l_txn_raw_cost_tab(i);
4094 l_cost_rate_override_tab(i) := l_txn_raw_cost_tab(i)/l_quantity_tab(i);
4095 l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
4096
4097 If nvl(l_txn_revenue_tab(i),0) <> 0 Then
4098
4099 l_stage := 'PRC:21';
4100 l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4101
4102 Else
4103
4104 l_stage := 'PRC:22';
4105 --l_txn_revenue_tab(i) := l_txn_burdened_cost_tab(i);
4106 --l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4107
4108 End If;
4109
4110 Elsif (Nvl(l_txn_burdened_cost_tab(i),0) <> 0
4111 and nvl(l_txn_raw_cost_tab(i),0) = 0 ) Then
4112
4113 l_stage := 'PRC:20';
4114 l_quantity_tab(i) := l_txn_burdened_cost_tab(i);
4115 --l_txn_raw_cost_tab(i) := l_txn_burdened_cost_tab(i);
4116 --l_cost_rate_override_tab(i) := l_txn_raw_cost_tab(i)/l_quantity_tab(i);
4117 l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
4118
4119 If nvl(l_txn_revenue_tab(i),0) <> 0 Then
4120
4121 l_stage := 'PRC:21';
4122 l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4123
4124 Else
4125
4126 l_stage := 'PRC:22';
4127 --l_txn_revenue_tab(i) := l_txn_burdened_cost_tab(i);
4128 --l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4129
4130 End If;
4131
4132 Elsif (Nvl(l_txn_burdened_cost_tab(i),0) = 0
4133 and nvl(l_txn_raw_cost_tab(i),0) <> 0 ) Then
4134
4135 l_stage := 'PRC:23';
4136 l_quantity_tab(i) := l_txn_raw_cost_tab(i);
4137 --l_txn_burdened_cost_tab(i) := l_txn_raw_cost_tab(i);
4138 l_cost_rate_override_tab(i) := l_txn_raw_cost_tab(i)/l_quantity_tab(i);
4139 --l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
4140
4141 If nvl(l_txn_revenue_tab(i),0) <> 0 Then
4142
4143 l_stage := 'PRC:24';
4144 l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4145
4146 Else
4147
4148 l_stage := 'PRC:25';
4149 --l_txn_revenue_tab(i) := l_txn_raw_cost_tab(i);
4150 --l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4151
4152 End If;
4153
4154 Elsif (Nvl(l_txn_burdened_cost_tab(i),0) = 0
4155 and nvl(l_txn_raw_cost_tab(i),0) = 0
4156 and nvl(l_txn_revenue_tab(i),0) <> 0 ) Then
4157
4158 l_stage := 'PRC:26';
4159 /* Bug 4865563: IPM Business Rule, if only revenue is present, don't copy it to anything. */
4160 l_quantity_tab(i) := l_txn_revenue_tab(i);
4161 --l_txn_raw_cost_tab(i) := l_txn_revenue_tab(i);
4162 --l_txn_burdened_cost_tab(i) := l_txn_raw_cost_tab(i);
4163 --l_cost_rate_override_tab(i) := l_txn_raw_cost_tab(i)/l_quantity_tab(i);
4164 --l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
4165 l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i); /* bug 5006029 */
4166
4167 End If; --}
4168
4169 Else -- quantity not equal to zero
4170
4171 If p_rate_based_flag_tbl(i) = 'N' OR
4172 l_prev_non_rb_ra_id = p_resource_assignment_id_tbl(i) Then -- added for bug 5007734:
4173 -- bug 5007734: Making quantity = amounts only for upgrade flow, for the planning transactions
4174 -- which are non rate based or going to be made non rate based.
4175 IF p_calling_module = 'UPGRADE' THEN
4176
4177 If (nvl(l_txn_raw_cost_tab(i),0) <> 0
4178 and l_quantity_tab(i) <> l_txn_raw_cost_tab(i)) Then
4179
4180 l_stage := 'PRC:27';
4181 l_quantity_tab(i) := l_txn_raw_cost_tab(i);
4182 l_cost_rate_override_tab(i) := 1;
4183
4184 Else /* if nvl(l_txn_raw_cost_tab(i),0) = 0 or l_quantity_tab(i) = l_txn_raw_cost_tab(i) */
4185
4186 l_stage := 'PRC:28';--Bug 5076350
4187 If (l_txn_raw_cost_tab(i) = l_quantity_tab(i) and nvl(l_txn_raw_cost_tab(i),0) <> 0) Then
4188 l_cost_rate_override_tab(i) := 1;
4189 Elsif nvl(l_txn_burdened_cost_tab(i),0) <> 0 Then
4190 l_txn_raw_cost_tab(i) := l_quantity_tab(i);
4191 l_cost_rate_override_tab(i) := 1;
4192 End if;
4193 End If;
4194
4195 If ( nvl(l_txn_burdened_cost_tab(i),0) <> 0) Then
4196
4197 l_stage := 'PRC:29';
4198 l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
4199
4200 Else
4201
4202 l_stage := 'PRC:30';
4203 --l_txn_burdened_cost_tab(i) := l_txn_raw_cost_tab(i);
4204 --l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
4205
4206 End If;
4207
4208 If (nvl(l_txn_revenue_tab(i),0) <> 0) Then
4209
4210 l_stage := 'PRC:31';
4211 l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4212
4213 Else
4214
4215 l_stage := 'PRC:32';
4216 --l_txn_revenue_tab(i) := l_txn_burdened_cost_tab(i);
4217 --l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4218
4219 End If;
4220 END IF; -- end of bug 5007734
4221
4222 Else -- this for rate based resource
4223
4224 If nvl(l_txn_raw_cost_tab(i),0) <> 0 Then
4225
4226 l_stage := 'PRC:33';
4227 l_cost_rate_override_tab(i) := l_txn_raw_cost_tab(i)/l_quantity_tab(i);
4228
4229 Elsif nvl(l_txn_raw_cost_tab(i),0) = 0 Then
4230
4231 l_stage := 'PRC:34';
4232 --l_txn_raw_cost_tab(i) := l_quantity_tab(i);
4233 --l_cost_rate_override_tab(i) := 1;
4234
4235 End If;
4236
4237 If ( nvl(l_txn_burdened_cost_tab(i),0) <> 0) Then
4238
4239 l_stage := 'PRC:35';
4240 l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
4241
4242 Else
4243
4244 l_stage := 'PRC:36';
4245 --l_txn_burdened_cost_tab(i) := l_txn_raw_cost_tab(i);
4246 --l_burden_rate_override_tab(i) := l_txn_burdened_cost_tab(i)/l_quantity_tab(i);
4247
4248 End If;
4249
4250 If (nvl(l_txn_revenue_tab(i),0) <> 0) Then
4251
4252 l_stage := 'PRC:37';
4253 l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4254
4255 Else
4256
4257 l_stage := 'PRC:38';
4258 --l_txn_revenue_tab(i) := l_txn_burdened_cost_tab(i);
4259 --l_bill_rate_override_tab(i) := l_txn_revenue_tab(i)/l_quantity_tab(i);
4260
4261 End If;
4262
4263 End If; --If p_rate_based_flag_tbl(i) = 'N' Then
4264
4265 End If; --If l_quantity_tab(i) = 0 then
4266
4267 End IF; --IF p_preference_code = 'COST_ONLY' THEN
4268
4269 END LOOP;
4270
4271 x_quantity_tbl := l_quantity_tab ;
4272 x_txn_raw_cost_tbl := l_txn_raw_cost_tab ;
4273 x_txn_burdened_cost_tbl := l_txn_burdened_cost_tab ;
4274 x_txn_revenue_tbl := l_txn_revenue_tab ;
4275 x_raw_cost_override_rate_tbl := l_cost_rate_override_tab ;
4276 x_burd_cost_override_rate_tbl:= l_burden_rate_override_tab ;
4277 x_bill_override_rate_tbl := l_bill_rate_override_tab ;
4278
4279 IF l_debug_mode = 'Y' THEN
4280 pa_debug.g_err_stage:='Exiting Apply_Calculate_FPM_Rules';
4281 pa_debug.write( l_module_name,pa_debug.g_err_stage,3);
4282 END IF;
4283 -- reset curr function
4284 pa_debug.reset_curr_function;
4285
4286 EXCEPTION
4287
4288 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
4289 l_msg_count := FND_MSG_PUB.count_msg;
4290 IF l_msg_count = 1 THEN
4291 PA_INTERFACE_UTILS_PUB.get_messages
4292 (p_encoded => FND_API.G_TRUE
4293 ,p_msg_index => 1
4294 ,p_msg_count => l_msg_count
4295 ,p_msg_data => l_msg_data
4296 ,p_data => l_data
4297 ,p_msg_index_out => l_msg_index_out);
4298
4299 x_msg_data := l_data;
4300 x_msg_count := l_msg_count;
4301 ELSE
4302 x_msg_count := l_msg_count;
4303 END IF;
4304
4305 x_return_status := FND_API.G_RET_STS_ERROR;
4306
4307 IF l_debug_mode = 'Y' THEN
4308 pa_debug.g_err_stage:='Invalid Arguments Passed Or called api raised an error';
4309 pa_debug.write( l_module_name,pa_debug.g_err_stage,5);
4310
4311 END IF;
4312 -- reset curr function
4313 pa_debug.reset_curr_function();
4314 RETURN;
4315 WHEN OTHERS THEN
4316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4317 x_msg_count := 1;
4318 x_msg_data := SQLERRM;
4319
4320 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'pa_fp_upgrade_pkg'
4321 ,p_procedure_name => 'Apply_Calculate_FPM_Rules l_stage'||l_stage);
4322
4323 IF l_debug_mode = 'Y' THEN
4324 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
4325 pa_debug.write( l_module_name,pa_debug.g_err_stage,5);
4326 END IF;
4327 -- reset curr function
4328 pa_debug.Reset_Curr_Function();
4329 RAISE;
4330
4331 END Apply_Calculate_FPM_Rules;
4332
4333 PROCEDURE rollup_rejected_bl_amounts(
4334 p_from_project_number IN VARCHAR2 DEFAULT NULL
4335 ,p_to_project_number IN VARCHAR2 DEFAULT NULL
4336 ,p_fin_plan_type_id IN NUMBER DEFAULT NULL
4337 ,p_project_statuses IN VARCHAR2
4338 ,x_return_status OUT NOCOPY VARCHAR2
4339 ,x_msg_count OUT NOCOPY NUMBER
4340 ,x_msg_data OUT NOCOPY VARCHAR2) IS
4341 l_return_status VARCHAR2(2000);
4342 l_msg_count NUMBER :=0;
4343 l_msg_data VARCHAR2(2000);
4344 l_data VARCHAR2(2000);
4345 l_msg_index_out NUMBER;
4346 l_debug_mode VARCHAR2(30);
4347 l_error_msg_code VARCHAR2(2000);
4348
4349 l_project_id pa_projects.project_id%TYPE;
4350 l_bv_id pa_budget_versions.budget_version_id%TYPE;
4351 l_budg_ver_id pa_budget_versions.budget_version_id%TYPE;
4352 l_ci_id pa_control_items.ci_id%TYPE;
4353 l_op_id pa_proj_fp_options.proj_fp_options_id%TYPE;
4354
4355 /* For bug 5084161 */
4356 l_ci_status_code pa_control_items.status_code%TYPE;
4357 l_process_flag varchar2(1);
4358 /* For bug 5084161 */
4359
4360 l_retcode number;
4361 l_errbuf varchar2(512);
4362
4363 l_date date := sysdate;
4364 l_login_id NUMBER := fnd_global.login_id;
4365 l_user_id NUMBER := fnd_global.user_id;
4366
4367 l_budget_ver_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
4368
4369 l_fp_cols_rec_var PA_FP_GEN_AMOUNT_UTILS.FP_COLS; --/* Bug 5098818 */
4370
4371 record_locked EXCEPTION;
4372 PRAGMA EXCEPTION_INIT (record_locked, -54);
4373
4374 /* 5084161 - Removed the ci status checking logic from this cursor */
4375
4376 CURSOR get_proj_bv_ids_for_rup(
4377 c_from_project_number IN VARCHAR2
4378 ,c_to_project_number IN VARCHAR2
4379 ,c_project_statuses IN VARCHAR2
4380 ,c_fin_plan_type_id IN NUMBER) IS
4381 SELECT prj.project_id,
4382 bv.budget_version_id,
4383 bv.ci_id,
4384 op.proj_fp_options_id,
4385 ci.status_code
4386 FROM pa_projects prj,
4387 pa_budget_versions bv,
4388 pa_fin_plan_types_b fp,
4389 pa_control_items ci,
4390 pa_proj_fp_options op
4391 WHERE segment1 BETWEEN NVL(c_from_project_number,segment1) AND NVL(c_to_project_number,segment1)
4392 AND DECODE(c_project_statuses,'ALL','ACTIVE',prj.project_status_code) = 'ACTIVE'
4393 AND bv.project_id = prj.project_id
4394 and bv.fin_plan_type_id = fp.fin_plan_type_id
4395 and bv.budget_version_id = op.fin_plan_version_id
4396 and op.project_id = bv.project_id
4397 and nvl(c_fin_plan_type_id,fp.fin_plan_type_id) = fp.fin_plan_type_id
4398 and nvl(fp.FIN_PLAN_TYPE_CODE,'x') <> 'ORG_FORECAST'
4399 and bv.budget_status_code = 'W'
4400 and bv.ci_id = ci.ci_id(+)
4401 and NVL(pa_project_structure_utils.check_struc_ver_published(bv.project_id,bv.project_structure_version_id),'N') = 'N'
4402 and NOT EXISTS (SELECT 1 FROM pa_fp_upgrade_audit aud
4403 WHERE aud.project_id = op.project_id
4404 AND aud.proj_fp_options_id_rup = op.PROJ_FP_OPTIONS_ID
4405 AND aud.upgraded_flag = 'Y')
4406 and EXISTS (SELECT 1 FROM pa_budget_lines bl
4407 WHERE bl.budget_version_id = bv.budget_version_id
4408 AND ( bl.cost_rejection_code IS NOT NULL
4409 OR bl.revenue_rejection_code IS NOT NULL
4410 OR bl.burden_rejection_code IS NOT NULL
4411 OR bl.pfc_cur_conv_rejection_code IS NOT NULL
4412 OR bl.pc_cur_conv_rejection_code IS NOT NULL
4413 )
4414 )
4415 and bv.prc_generated_flag = 'M'; --IPM Optional Upgrade Process
4416 /* PRC_GENERATED_FLAG is marked with 'M' during the patchset upgrade process
4417 of paupg109.sql while upgrading from FPM to IPM level. This cursor picks up
4418 only those budget_versions which were marked during patchset upgrade. Note that
4419 PRC_GENERATED_FLAG is being reused here; it was introduced earlier for a different
4420 purpose but was never used
4421 */
4422
4423 BEGIN
4424 x_msg_count := 0;
4425 x_return_status := FND_API.G_RET_STS_SUCCESS;
4426 savepoint rollup_rejected_bl_amounts;
4427
4428 pa_debug.init_err_stack('PA_FP_UPGRADE_PKG.rollup_rejected_bl_amounts');
4429
4430 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
4431 l_debug_mode := NVL(l_debug_mode, 'Y');
4432 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
4433
4434 IF p_pa_debug_mode = 'Y' THEN
4435 pa_debug.g_err_stage := 'Entered rollup_rejected_bl_amounts';
4436 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4437 pa_debug.g_err_stage := 'Checking for valid parameters';
4438 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4439 END IF;
4440
4441 IF p_project_statuses IS NULL THEN
4442 IF p_pa_debug_mode = 'Y' THEN
4443 pa_debug.g_err_stage := 'p_project_statuses='||p_project_statuses;
4444 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
4445 END IF;
4446
4447 PA_UTILS.ADD_MESSAGE(p_app_short_name=> 'PA',
4448 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
4449 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
4450 END IF;
4451
4452 IF p_pa_debug_mode = 'Y' THEN
4453 pa_debug.g_err_stage := 'Parameter validation complete';
4454 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4455 END IF;
4456
4457 -- Fetch all the projects whose budget's lines amounts need to be rolled up.
4458 IF p_pa_debug_mode = 'Y' THEN
4459 pa_debug.g_err_stage := 'opening get_proj_bv_ids_for_rup';
4460 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4461 pa_debug.g_err_stage := 'p_from_project_number = '||p_from_project_number;
4462 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4463 pa_debug.g_err_stage := 'p_to_project_number = '|| p_to_project_number;
4464 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4465 pa_debug.g_err_stage := 'p_project_statuses='||p_project_statuses;
4466 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
4467 END IF;
4468
4469 OPEN get_proj_bv_ids_for_rup(p_from_project_number,p_to_project_number,p_project_statuses,p_fin_plan_type_id);
4470 LOOP
4471 FETCH get_proj_bv_ids_for_rup INTO l_project_id,l_bv_id,l_ci_id,l_op_id,l_ci_status_code;
4472 EXIT WHEN get_proj_bv_ids_for_rup%NOTFOUND;
4473
4474 IF p_pa_debug_mode = 'Y' THEN
4475 pa_debug.g_err_stage := 'Project_id ='||l_project_id;
4476 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4477
4478 pa_debug.g_err_stage := 'Opening get_fin_plan_versions'||l_project_id;
4479 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4480 END IF;
4481
4482 savepoint rollup_bl_amounts_to_bv;
4483
4484 BEGIN
4485
4486 /* Start of fix for bug 5084161 */
4487
4488 l_process_flag := 'Y';
4489
4490 /* Check if ci is in updateable status - following code got from ci team */
4491
4492 IF l_ci_id IS NOT NULL THEN
4493
4494 begin
4495 select 'Y'
4496 into l_process_flag
4497 from pa_project_statuses ps ,
4498 pa_project_status_controls psc
4499 where ps.project_Status_code = l_ci_status_code
4500 and ps.project_system_status_code = nvl(psc.project_system_status_code,psc.project_Status_code)
4501 and psc.status_type = 'CONTROL_ITEM'
4502 and psc.action_code = 'CONTROL_ITEM_ALLOW_UPDATE'
4503 and psc.enabled_flag = 'N'
4504 and rownum < 2;
4505
4506 exception
4507 when no_data_found then
4508 l_process_flag := 'N';
4509 end;
4510
4511 END IF;
4512
4513 /* End of fix for bug 5084161 */
4514
4515 IF p_pa_debug_mode = 'Y' THEN
4516 pa_debug.g_err_stage := 'Budget Version Id ='||l_bv_id;
4517 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4518
4519 pa_debug.g_err_stage := 'Change Order/Req ID ='||l_ci_id;
4520 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4521
4522 pa_debug.g_err_stage := 'l_ci_status_code / l_process_flag = ' || l_ci_status_code || '/' || l_process_flag;
4523 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4524 END IF;
4525
4526 IF l_process_flag = 'Y' THEN /* 5084161 */
4527
4528 -- Now we have a budget version id and we will try to lock these
4529 -- We try to lock the records in pa_resource_asgn_curr,pa_resource_assignments
4530 -- as well to avoid the partial processing.
4531 -- Imagine the case where the ra records are processed and now we fail
4532 -- to obtain a lock on budget versions record. In order to avoid this
4533 -- we obtain locks on all the records in all the table and then we will
4534 -- proceed. If we fail to obtain a lock on a particular budget version then
4535 -- We will update the audit table with that id and the reason then we will
4536 -- proceed to process the other budget versions in the project/range.
4537
4538
4539 IF p_pa_debug_mode = 'Y' THEN
4540 pa_debug.g_err_stage := 'Deleting the pl/sql tables before locking the records.';
4541 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4542 END IF;
4543
4544
4545 l_rtx_ra_id_tbl.delete;
4546 l_ra_id_tbl.delete ;
4547
4548 IF p_pa_debug_mode = 'Y' THEN
4549 pa_debug.g_err_stage := 'Successfully Deleted the pl/sql tables.';
4550 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4551 END IF;
4552
4553 SELECT bv.budget_version_id INTO l_budg_ver_id
4554 FROM pa_budget_versions bv
4555 WHERE bv.budget_version_id = l_bv_id
4556 FOR UPDATE OF bv.budget_version_id NOWAIT;
4557
4558 IF p_pa_debug_mode = 'Y' THEN
4559 pa_debug.g_err_stage := 'Successfully locked the budget version records';
4560 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4561 END IF;
4562
4563 SELECT rtx.resource_assignment_id BULK COLLECT INTO l_rtx_ra_id_tbl
4564 FROM pa_resource_asgn_curr rtx
4565 WHERE rtx.budget_version_id = l_bv_id
4566 FOR UPDATE OF rtx.resource_assignment_id NOWAIT;
4567
4568 IF p_pa_debug_mode = 'Y' THEN
4569 pa_debug.g_err_stage := 'Successfully locked the resource assign curr records';
4570 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4571 END IF;
4572
4573
4574 SELECT ra.resource_assignment_id BULK COLLECT INTO l_ra_id_tbl
4575 FROM pa_resource_assignments ra
4576 WHERE ra.budget_version_id = l_bv_id
4577 FOR UPDATE OF ra.resource_assignment_id NOWAIT;
4578
4579 IF p_pa_debug_mode = 'Y' THEN
4580 pa_debug.g_err_stage := 'Successfully locked the resource assignment records';
4581 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4582 END IF;
4583
4584
4585 -- Process each Budget version here
4586 -- by rolling up the totals from rejected Budget Line's total
4587 -- onto the new entity and RA and BV.
4588 /* Bug 5098818 - Start - Replaced exclusive update stmt with a call to maintain_data api */
4589 /* populating fp_cols_rec to call the new entity maintenace API */
4590 PA_FP_GEN_AMOUNT_UTILS.get_plan_version_dtls
4591 (p_budget_version_id => l_bv_id,
4592 x_fp_cols_rec => l_fp_cols_rec_var,
4593 x_return_status => l_return_status,
4594 x_msg_count => l_msg_count,
4595 x_msg_data => l_msg_data);
4596
4597 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
4598 THEN
4599 IF p_pa_debug_mode = 'Y' THEN
4600 pa_debug.write_file('Upgrade failed due to error in PA_FP_GEN_AMOUNT_UTILS.get_plan_version_dtls',5);
4601 END IF;
4602 raise PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
4603 END IF;
4604
4605 /* calling the maintenance api to insert data into the new planning transaction level table */
4606 PA_RES_ASG_CURRENCY_PUB.maintain_data
4607 (p_fp_cols_rec => l_fp_cols_rec_var,
4608 p_calling_module => 'UPGRADE',
4609 p_rollup_flag => 'Y',
4610 p_version_level_flag => 'Y',
4611 x_return_status => l_return_status,
4612 x_msg_count => l_msg_count,
4613 x_msg_data => l_msg_data);
4614
4615 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
4616 THEN
4617 IF p_pa_debug_mode = 'Y' THEN
4618 pa_debug.write_file('Upgrade failed due to error in PA_RES_ASG_CURRENCY_PUB.maintain_data',5);
4619 END IF;
4620 raise PA_FP_CONSTANTS_PKG.INVALID_ARG_EXC;
4621 END IF;
4622 /* Bug 5098818 - End - Replaced exclusive update stmt with a call to maintain_data api */
4623
4624 IF p_pa_debug_mode = 'Y' THEN
4625 pa_debug.g_err_stage := 'Updated the resource assign curr amts from budget lines';
4626 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4627 END IF;
4628
4629
4630 -- Rollup the totals onto the RA
4631 /* Bug 5098818 - Start - Replaced exclusive update stmt with a call to already existing rollup_budget api */
4632
4633 PA_FP_ROLLUP_PKG.ROLLUP_BUDGET_VERSION(
4634 p_budget_version_id => l_bv_id
4635 ,p_entire_version => 'Y'
4636 ,p_context => NULL
4637 ,x_return_status => l_return_status
4638 ,x_msg_count => l_msg_count
4639 ,x_msg_data => l_msg_data);
4640
4641 /* Bug 5098818 - End - Replaced exclusive update stmt with a call to already existing rollup_budget api */
4642 IF p_pa_debug_mode = 'Y' THEN
4643 pa_debug.g_err_stage := 'Updated the resource assignment amts from resource assign curr';
4644 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4645 END IF;
4646
4647 -- Auditing the RA ID records.
4648 FORALL i IN l_ra_id_tbl.FIRST..l_ra_id_tbl.LAST
4649 -- SAVE EXCEPTIONS
4650 INSERT INTO pa_budget_lines_m_upg_dtrange(
4651 LAST_UPDATE_DATE
4652 ,LAST_UPDATED_BY
4653 ,CREATION_DATE
4654 ,CREATED_BY
4655 ,LAST_UPDATE_LOGIN
4656 ,BUDGET_VERSION_ID_RUP
4657 ,RESOURCE_ASSIGNMENT_ID_RUP)
4658 VALUES ( sysdate
4659 ,fnd_global.user_id
4660 ,sysdate
4661 ,fnd_global.user_id
4662 ,fnd_global.login_id
4663 ,l_bv_id
4664 ,l_ra_id_tbl(i));
4665
4666 IF p_pa_debug_mode = 'Y' THEN
4667 pa_debug.g_err_stage := 'Audited the resource assignment IDs';
4668 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4669 END IF;
4670
4671 -- Sync'ing up the amounts in the PJI model by calling the PJI APIs.
4672 -- The business rule is that we should not call the PJI APIs for CO/CR.
4673
4674 IF p_pa_debug_mode = 'Y' THEN
4675 pa_debug.g_err_stage := 'Before calling PJI APIs';
4676 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4677 END IF;
4678
4679
4680 IF l_ci_id IS NULL THEN
4681
4682 l_budget_ver_tbl.extend;
4683 l_budget_ver_tbl(1) := l_bv_id;
4684
4685 IF p_pa_debug_mode = 'Y' THEN
4686 pa_debug.g_err_stage := 'Before calling PJI API PLAN_DELETE for budget ver '||l_bv_id;
4687 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4688 END IF;
4689
4690 PJI_FM_XBS_ACCUM_MAINT.PLAN_DELETE (
4691 p_fp_version_ids => l_budget_ver_tbl,
4692 x_return_status => x_return_status,
4693 x_msg_code => l_error_msg_code);
4694
4695 IF p_pa_debug_mode = 'Y' THEN
4696 pa_debug.g_err_stage := 'The rtn sts of PJI API PLAN_DELETE is '||x_return_status;
4697 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4698
4699 pa_debug.g_err_stage := 'The msg code of PJI API PLAN_DELETE is '||l_error_msg_code;
4700 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4701 END IF;
4702
4703 IF (l_return_status <> 'S') THEN
4704 RAISE pa_fp_constants_pkg.Invalid_Arg_Exc;
4705 END IF;
4706
4707 PJI_FM_XBS_ACCUM_MAINT.PLAN_CREATE (
4708 p_fp_version_ids => l_budget_ver_tbl,
4709 x_return_status => x_return_status,
4710 x_msg_code => l_error_msg_code);
4711
4712 IF p_pa_debug_mode = 'Y' THEN
4713 pa_debug.g_err_stage := 'The rtn sts of PJI API PLAN_CREATE is '||x_return_status;
4714 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4715
4716 pa_debug.g_err_stage := 'The msg code of PJI API PLAN_CREATE is '||l_error_msg_code;
4717 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4718 END IF;
4719
4720 IF (l_return_status <> 'S') THEN
4721 RAISE pa_fp_constants_pkg.Invalid_Arg_Exc;
4722 END IF;
4723
4724 END IF;
4725
4726 -- Now Audit the Budget version level record change
4727
4728 IF p_pa_debug_mode = 'Y' THEN
4729 pa_debug.g_err_stage := 'Auditing the budget version ';
4730 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4731
4732 pa_debug.g_err_stage := 'The budget version proj fp id is '||l_op_id;
4733 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4734 END IF;
4735
4736
4737 pa_fp_upgrade_pkg.Insert_Audit_Record(
4738 p_project_id => l_project_id
4739 ,p_budget_type_code => NULL
4740 ,p_proj_fp_options_id => NULL
4741 ,p_fin_plan_option_level_code => NULL
4742 ,p_basis_cost_version_id => NULL
4743 ,p_basis_rev_version_id => NULL
4744 ,p_basis_cost_bem => NULL
4745 ,p_basis_rev_bem => NULL
4746 ,p_upgraded_flag => 'Y'
4747 ,p_failure_reason_code => NULL
4748 ,p_proj_fp_options_id_rup => l_op_id);
4749 END IF; /* l_process_flag = 'Y' */
4750 EXCEPTION
4751 WHEN record_locked THEN
4752 /* Record was already locked, so audit this version and
4753 just keep on going */
4754
4755 IF p_pa_debug_mode = 'Y' THEN
4756 pa_debug.g_err_stage := 'inside Bdgts loop:Record is locked ';
4757 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4758
4759 pa_debug.g_err_stage := 'The proj id is '||l_project_id;
4760 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4761
4762 pa_debug.g_err_stage := 'The proj fp id is '||l_op_id;
4763 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4764 END IF;
4765
4766 rollback to rollup_bl_amounts_to_bv;
4767 pa_fp_upgrade_pkg.Insert_Audit_Record(
4768 p_project_id => l_project_id
4769 ,p_budget_type_code => NULL
4770 ,p_proj_fp_options_id => NULL
4771 ,p_fin_plan_option_level_code => NULL
4772 ,p_basis_cost_version_id => NULL
4773 ,p_basis_rev_version_id => NULL
4774 ,p_basis_cost_bem => NULL
4775 ,p_basis_rev_bem => NULL
4776 ,p_upgraded_flag => 'N'
4777 ,p_failure_reason_code => 'Record Locked'
4778 ,p_proj_fp_options_id_rup => l_op_id);
4779
4780 WHEN OTHERS THEN
4781
4782 IF p_pa_debug_mode = 'Y' THEN
4783 pa_debug.g_err_stage := 'inside Bdgts loop:When others and sqlcode is '||sqlcode;
4784 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4785
4786 pa_debug.g_err_stage := 'The proj id is '||l_project_id;
4787 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4788
4789 pa_debug.g_err_stage := 'The proj fp id is '||l_op_id;
4790 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4791 END IF;
4792
4793 rollback to rollup_bl_amounts_to_bv;
4794 pa_fp_upgrade_pkg.Insert_Audit_Record(
4795 p_project_id => l_project_id
4796 ,p_budget_type_code => NULL
4797 ,p_proj_fp_options_id => NULL
4798 ,p_fin_plan_option_level_code => NULL
4799 ,p_basis_cost_version_id => NULL
4800 ,p_basis_rev_version_id => NULL
4801 ,p_basis_cost_bem => NULL
4802 ,p_basis_rev_bem => NULL
4803 ,p_upgraded_flag => 'N'
4804 ,p_failure_reason_code => sqlcode
4805 ,p_proj_fp_options_id_rup => l_op_id);
4806
4807
4808 END;
4809 l_rtx_ra_id_tbl.delete;
4810 l_ra_id_tbl.delete ;
4811 l_budget_ver_tbl.DELETE;
4812 COMMIT; -- this commits data for each Plan processed
4813
4814 -- END LOOP;
4815 -- CLOSE get_fin_plan_versions;
4816 END LOOP;
4817 CLOSE get_proj_bv_ids_for_rup;
4818 IF p_pa_debug_mode = 'Y' THEN
4819 pa_debug.g_err_stage := 'Closed get_proj_bv_ids_for_rup';
4820 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4821 pa_debug.g_err_stage := 'Exiting rollup_rejected_bl_amounts';
4822 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
4823 END IF;
4824 pa_debug.reset_err_stack;
4825 EXCEPTION
4826
4827 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
4828
4829 IF get_proj_bv_ids_for_rup%ISOPEN THEN
4830 CLOSE get_proj_bv_ids_for_rup;
4831 END IF;
4832 l_msg_count := FND_MSG_PUB.count_msg;
4833 IF l_msg_count = 1 THEN
4834 PA_INTERFACE_UTILS_PUB.get_messages
4835 (p_encoded => FND_API.G_TRUE
4836 ,p_msg_index => 1
4837 ,p_msg_count => l_msg_count
4838 ,p_msg_data => l_msg_data
4839 ,p_data => l_data
4840 ,p_msg_index_out => l_msg_index_out);
4841 x_msg_data := l_data;
4842 x_msg_count := l_msg_count;
4843 ELSE
4844 x_msg_count := l_msg_count;
4845 x_msg_data := l_msg_data;
4846 END IF;
4847
4848 IF p_pa_debug_mode = 'Y' THEN
4849 pa_debug.g_err_stage:='Invalid Arguments Passed';
4850 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
4851 END IF;
4852 x_return_status:= FND_API.G_RET_STS_ERROR;
4853 pa_debug.write_file('Rollup_rejected_bl_ampunts : Upgrade has failed for the project: '||l_project_id,5);
4854 pa_debug.write_file('Rollup_rejected_bl_ampunts : Failure Reason:'||x_msg_data,5);
4855 pa_debug.reset_err_stack;
4856 ROLLBACK TO rollup_rejected_bl_amounts;
4857 RAISE;
4858 WHEN Others THEN
4859
4860 IF get_proj_bv_ids_for_rup%ISOPEN THEN
4861 CLOSE get_proj_bv_ids_for_rup;
4862 END IF;
4863 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4864 x_msg_count := 1;
4865 x_msg_data := SQLERRM;
4866
4867 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_FP_UPGRADE_PKG'
4868 ,p_procedure_name => 'Rollup_rejected_bl_ampunts');
4869 IF p_pa_debug_mode = 'Y' THEN
4870 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
4871 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
4872 END IF;
4873 pa_debug.write_file('Rollup_rejected_bl_ampunts : Upgrade has failed for the project'||l_project_id,5);
4874 pa_debug.write_file('Upgrade_Budgets : Failure Reason:'||pa_debug.G_Err_Stack,5);
4875 pa_debug.reset_err_stack;
4876 ROLLBACK TO rollup_rejected_bl_amounts;
4877 RAISE;
4878 END rollup_rejected_bl_amounts;
4879
4880 END pa_fp_upgrade_pkg;