1 package body PA_BUDGET_ATTR_UPGR_PKG AS
2 /* $Header: PABDGATB.pls 120.0 2005/05/30 11:28:12 appldev noship $ */
3
4 procedure BUDGET_ATTR_UPGRD(
5 P_PROJECT_ID IN pa_projects_all.project_id%type
6 , p_budget_version_id IN pa_budget_versions.budget_version_id%type
7 , X_RETURN_STATUS OUT NOCOPY VARCHAR2
8 , X_MSG_COUNT OUT NOCOPY NUMBER
9 , X_MSG_DATA OUT NOCOPY VARCHAR2) IS
10
11 --Bug 4185180.Given a budget version id as parameter the cursor should bring the PLAN_VERSION, PLAN_TYPE level records for that
12 -- project and budget version.
13 cursor get_fin_plan_ver_csr(c_project_id pa_projects_all.project_id%type
14 , c_budget_version_id pa_budget_versions.budget_version_id%type) is
15 select proj_fp_options_id,fin_plan_version_id,pfo.project_id,
16 fin_plan_option_level_code,cost_time_phased_code,fin_plan_preference_code preference_code,
17 revenue_time_phased_code,all_time_phased_code,cost_period_mask_id,
18 rev_period_mask_id,all_period_mask_id,decode(revenue_time_phased_code,'G',1,'P',2,
19 decode(cost_time_phased_code,'G',1,'P',2,
20 decode(all_time_phased_code,'G',1,'P',2,null))) time_phased_code,
21 decode(all_resource_list_id,null,decode(cost_resource_list_id,null,revenue_resource_list_id,cost_resource_list_id),all_resource_list_id) resource_list_id
22 from pa_proj_fp_options pfo
23 ,pa_budget_versions pbv
24 where pfo.project_id = c_project_id
25 and pbv.budget_version_id=c_budget_version_id
26 and (fin_plan_version_id = c_budget_version_id OR
27 (pfo.project_id = p_project_id AND
28 fin_plan_option_level_code <> 'PLAN_VERSION' AND
29 nvl(pfo.fin_plan_type_id,-99)=nvl(pbv.fin_plan_type_id,-99))); /* So that the fetch/update is not done if project/plan type level record is already upgraded */
30
31 cursor get_rbs_ver_csr(c_resource_list_id pa_resource_lists_all_bg.resource_list_id%TYPE) is
32 select migrated_rbs_version_id,uncategorized_flag from
33 pa_resource_lists_all_bg
34 where resource_list_id = c_resource_list_id;
35
36
37 cursor get_per_mask_id_csr is
38 select period_mask_id,decode(time_phase_code,'G',1,'P',2)time_phase_code
39 from pa_period_masks_b where pre_defined_flag='Y';
40
41
42 -- Bug 3800485, 28-JUL-04, jwhite -----------------------------------------------
43
44 cursor get_rbs_header_csr (c_rbs_version_id pa_resource_lists_all_bg.migrated_rbs_version_id%type)
45 is
46 SELECT RBS_HEADER_ID
47 FROM pa_rbs_versions_b
48 WHERE RBS_VERSION_ID = c_rbs_version_id;
49
50 -- End Bug 3800485 ---------------------------------------------------------------
51
52
53 TYPE get_per_mask_tbl is table of number
54 index by binary_integer;
55 l_get_per_mask_tbl get_per_mask_tbl;
56
57 TYPE get_rbs_ver_tbl is table of pa_resource_lists_all_bg.migrated_rbs_version_id%type
58 index by binary_integer;
59 l_get_rbs_ver_tbl get_rbs_ver_tbl;
60
61 l_period_mask_id pa_period_masks_b.period_mask_id%type;
62 l_curr_plan_period pa_budget_versions.current_planning_period%type;
63 l_curr_plan_period_b pa_budget_versions.current_planning_period%type;
64 l_rbs_version_id pa_resource_lists_all_bg.migrated_rbs_version_id%type;
65 l_cost_current_planning_period pa_proj_fp_options.cost_current_planning_period%type;
66 l_cost_period_mask_id pa_proj_fp_options.cost_period_mask_id%type;
67 l_rev_current_planning_period pa_proj_fp_options.rev_current_planning_period%type;
68 l_rev_period_mask_id pa_proj_fp_options.rev_period_mask_id%type;
69 l_all_current_planning_period pa_proj_fp_options.all_current_planning_period%type;
70 l_all_period_mask_id pa_proj_fp_options.all_period_mask_id%type;
71 l_stage VARCHAR2(240) :='';
72 l_debug_mode varchar2(30);
73 l_module_name VARCHAR2(100):= 'pa.plsql.pa_budget_attr_upgr_pkg';
74 l_msg_count NUMBER :=0;
75 l_msg_data VARCHAR2(2000);
76 l_msg_index_out NUMBER;
77 l_data VARCHAR2(2000);
78
79
80 -- Bug 3800485, 28-JUL-04, jwhite -----------------------------------------------
81
82 l_rbs_header_id pa_rbs_versions_b.RBS_HEADER_ID%TYPE := NULL;
83
84 l_return_status VARCHAR2(1) := NULL;
85
86 -- End Bug 3800485 --------------------------------------------------------------
87
88
89 -- Bug 3804286, 12-AUG-04, jwhite -----------------------------------------------
90
91 l_project_start_date pa_projects_all.start_date%TYPE := NULL;
92 l_org_id pa_projects_all.org_id%TYPE := NULL;
93 l_PA_period_type pa_implementations_all.pa_period_type%TYPE := NULL;
94 l_GL_period_type gl_sets_of_books.accounted_period_type%TYPE := NULL;
95
96 -- End Bug 3804286, 12-AUG-04, jwhite --------------------------------------------
97
98 l_uncategorized_flag pa_resource_lists_all_bg.uncategorized_flag%TYPE; -- Bug 3935863
99
100 --Bug 3977417.These variables will be used for cost and rev separate options. They will have the
101 --Current Planning Period(cpp) for GL/PA time phasing.
102 l_cpp_for_gl_time_phase pa_proj_fp_options.cost_time_phased_code%TYPE;
103 l_cpp_for_pa_time_phase pa_proj_fp_options.cost_time_phased_code%TYPE;
104 begin
105 -- FND_MSG_PUB.initialize; /* Bug 3800485 */
106 x_msg_count := 0;
107 x_return_status := FND_API.G_RET_STS_SUCCESS;
108 pa_debug.init_err_stack('PA_BUDGET_ATTR_UPGR_PKG.Budget_Attr_Upgrd');
109 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
110 l_debug_mode := NVL(l_debug_mode, 'Y');
111 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
112 IF l_debug_mode = 'Y' THEN
113 pa_debug.g_err_stage := 'Entered Budget Attribute Upgrade';
114 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
115
116 pa_debug.g_err_stage := 'Checking for valid parameters';
117 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
118 END IF;
119
120
121 if (nvl(p_project_id,0) = 0) then
122 IF l_debug_mode = 'Y' THEN
123 pa_debug.g_err_stage := 'p_project_id='||to_char(p_project_id);
124 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
125 END IF;
126 PA_UTILS.ADD_MESSAGE(p_app_short_name=> 'PA',
127 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
128 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
129 end if;
130
131 -- bug 3673111, 07-JUN-04, jwhite ------------------------------------
132
133 if (nvl(p_budget_version_id,0) = 0) then
134 IF l_debug_mode = 'Y' THEN
135 pa_debug.g_err_stage := 'p_budget_version_id='||to_char(p_budget_version_id);
136 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
137 END IF;
138 PA_UTILS.ADD_MESSAGE(p_app_short_name=> 'PA',
139 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
140 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
141 end if;
142
143
144 -- End Bug 3673111 ----------------------------------------------------
145
146 -- Loop for storing period mask id for each time phase code
147 for l_get_per_mask_id_csr in get_per_mask_id_csr
148 loop
149 l_get_per_mask_tbl(l_get_per_mask_id_csr.time_phase_code) := l_get_per_mask_id_csr.period_mask_id;
150 end loop;
151
152
153 -- bug 3673111, 07-JUN-04, jwhite ---------------------------------------
154 -- 1) added p_budget_verion_id to loop cursor parameter list.
155
156 -- Loop for each fin plan version
157
158 for l_get_fin_plan_ver_csr in get_fin_plan_ver_csr(p_project_id, p_budget_version_id)
159 loop
160 l_period_mask_id := NULL;
161 l_curr_plan_period := NULL;
162 l_curr_plan_period_b := NULL;
163 l_rbs_version_id := NULL;
164 l_cost_current_planning_period := NULL;
165 l_cost_period_mask_id := NULL;
166 l_rev_current_planning_period := NULL;
167 l_rev_period_mask_id := NULL;
168 l_all_current_planning_period := NULL;
169 l_all_period_mask_id := NULL;
170 l_rbs_header_id := NULL;
171 l_uncategorized_flag := NULL;
172 -- Bug 3804286, 12-AUG-04, jwhite -----------------------------------------------
173
174 l_project_start_date := NULL;
175 l_org_id := NULL;
176 l_PA_period_type := NULL;
177 l_GL_period_type := NULL;
178
179 -- End Bug 3804286, 12-AUG-04, jwhite -------------------------------------------
180
181
182 -- To obtain period mask id for the time phase code.
183 --Bug 3977417.For Cost and Rev Sep Options, cost/rev period mask ids should be separately derived based on
184 --cost/rev time phasings
185 if (l_get_fin_plan_ver_csr.preference_code='COST_AND_REV_SEP') then
186
187 if l_get_fin_plan_ver_csr.cost_time_phased_code='G' then
188 l_cost_period_mask_id := l_get_per_mask_tbl(1);
189 elsif l_get_fin_plan_ver_csr.cost_time_phased_code='P' then
190 l_cost_period_mask_id := l_get_per_mask_tbl(2);
191 else
192 l_cost_period_mask_id := NULL;
193 end if;
194
195 if l_get_fin_plan_ver_csr.revenue_time_phased_code='G' then
196 l_rev_period_mask_id := l_get_per_mask_tbl(1);
197 elsif l_get_fin_plan_ver_csr.revenue_time_phased_code='P' then
198 l_rev_period_mask_id := l_get_per_mask_tbl(2);
199 else
200 l_rev_period_mask_id := NULL;
201 end if;
202
203 elsif (l_get_per_mask_tbl.exists(l_get_fin_plan_ver_csr.time_phased_code)) then
204 l_period_mask_id := l_get_per_mask_tbl(l_get_fin_plan_ver_csr.time_phased_code);
205 end if;
206
207 -- To obtain RBS version for the given resource list id.
208 OPEN get_rbs_ver_csr(l_get_fin_plan_ver_csr.resource_list_id);
209 FETCH get_rbs_ver_csr
210 INTO l_rbs_version_id,l_uncategorized_flag;
211 CLOSE get_rbs_ver_csr;
212
213
214
215 -- Bug 3804286, 12-AUG-04, jwhite ---------------------------------------------
216
217 -- For Periodic Budget Versions, Get the Start Date for
218 -- Subseqeunt Derivation of the Current Planning Period Name.
219
220 --Bug 3977417
221 l_cpp_for_pa_time_phase := NULL;
222 l_cpp_for_gl_time_phase := NULL;
223
224 IF ( l_get_fin_plan_ver_csr.time_phased_code IN (1,2) OR
225 (l_get_fin_plan_ver_csr.preference_code='COST_AND_REV_SEP' AND--In this case too, l_get_fin_plan_ver_csr.time_phased_code
226 (l_get_fin_plan_ver_csr.cost_time_phased_code IN ('P','G') OR--would be either P or G. But Added condition since
227 l_get_fin_plan_ver_csr.revenue_time_phased_code IN ('P','G')-- l_get_fin_plan_ver_csr.time_phased_code should
228 ) --not be used for cost_and_rev_sep plan types.Bug 3977417
229 )
230 ) THEN
231 -- GL or PA Periodic Data
232
233 begin
234
235 -- Find Project Start Date and Org_id.
236 -- The Project Record MUST Exist. RAISE error if not found.
237 SELECT start_date, nvl(org_id,-99)
238 INTO l_project_start_date, l_org_id
239 FROM pa_projects_all
240 WHERE project_id = l_get_fin_plan_ver_csr.project_id;
241
242 exception
243 WHEN NO_DATA_FOUND THEN
244 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
245
246 end;
247
248
249 -- If Project Start is Still NUll,
250 -- then Find Minimum Budget Line Start Date, If Any.
251 IF ( l_project_start_date IS NULL)
252 THEN
253
254 begin
255
256 IF l_get_fin_plan_ver_csr.fin_plan_version_id IS NOT NULL THEN
257
258 SELECT min(start_date)
259 INTO l_project_start_date
260 FROM pa_budget_lines
261 WHERE budget_version_id = l_get_fin_plan_ver_csr.fin_plan_version_id;
262
263 END IF;
264
265 /* Following "if" takes care of case when there no budget lines for the budget verison
266 or
267 the record that is processed is a project/plan type level record. */
268
269 IF l_project_start_date IS NULL THEN
270 select trunc(sysdate) into l_project_start_date from dual;
271 END IF;
272 end;
273
274 END IF; -- l_project_start_date IS NULL)
275
276
277 -- IF Start Date FOUND,
278 -- THEN Derive GL/PA Period Name to Populate the Current Planning Period
279
280 IF ( l_project_start_date IS NULL)
281 THEN
282
283 l_curr_plan_period := NULL;
284
285 ELSE
286
287 IF ( l_get_fin_plan_ver_csr.time_phased_code = 1 OR
288 (l_get_fin_plan_ver_csr.preference_code='COST_AND_REV_SEP' AND
289 (l_get_fin_plan_ver_csr.cost_time_phased_code = 'G' OR
290 l_get_fin_plan_ver_csr.revenue_time_phased_code = 'G'
291 )
292 )
293 ) THEN
294 -- Get GL Period Name
295
296 begin
297
298 SELECT sob.accounted_period_type
299 INTO l_GL_period_type
300 FROM pa_implementations_all I
301 , gl_sets_of_books sob
302 WHERE nvl(i.org_id,-99) = l_org_id
303 AND sob.set_of_books_id = i.set_of_books_id;
304
305 SELECT gl.PERIOD_NAME
306 INTO l_curr_plan_period
307 FROM gl_periods gl
308 , pa_implementations_all i
309 , gl_sets_of_books sob
310 WHERE nvl(i.org_id,-99) = l_org_id
311 AND gl.period_type = l_GL_period_type
312 and l_project_start_date between gl.START_DATE and gl.END_DATE
313 AND sob.set_of_books_id = i.set_of_books_id
314 AND gl.period_set_name = sob.period_set_name
315 AND gl.ADJUSTMENT_PERIOD_FLAG = 'N';
316
317 exception
318 WHEN NO_DATA_FOUND THEN
319 l_curr_plan_period := NULL;
320
321 end;
322 l_cpp_for_gl_time_phase:=l_curr_plan_period;
323
324 End IF; -- GL Period Type
325
326 IF ( l_get_fin_plan_ver_csr.time_phased_code = 2 OR
327 (l_get_fin_plan_ver_csr.preference_code='COST_AND_REV_SEP' AND
328 (l_get_fin_plan_ver_csr.cost_time_phased_code = 'P' OR
329 l_get_fin_plan_ver_csr.revenue_time_phased_code = 'P'
330 )
331 )
332 ) THEN
333 -- Get PA Period Name
334
335 begin
336
337 SELECT i.pa_period_type
338 INTO l_PA_period_type
339 FROM pa_implementations_all i
340 WHERE nvl(i.org_id,-99) = l_org_id;
341
342 exception
343 WHEN OTHERS THEN
344 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc; -- pa_implementations must exist for org_id.
345
346 end;
347
348 begin
349
350 SELECT gl.PERIOD_NAME
351 INTO l_curr_plan_period
352 FROM gl_periods gl
353 , pa_implementations_all i
354 , gl_sets_of_books sob
355 WHERE nvl(i.org_id,-99) = l_org_id
356 AND gl.period_type = l_PA_period_type
357 and l_project_start_date between gl.START_DATE and gl.END_DATE
358 AND sob.set_of_books_id = i.set_of_books_id
359 AND gl.period_set_name = sob.period_set_name
360 AND gl.ADJUSTMENT_PERIOD_FLAG = 'N';
361
362 exception
363 WHEN NO_DATA_FOUND THEN
364 l_curr_plan_period := NULL;
365
366 end;
367 l_cpp_for_pa_time_phase:=l_curr_plan_period;
368 End IF; -- PA Period Type
369
370 End IF; -- Start Date Processing
371
372 END IF; -- GL or PA periodic Data Processing
373
374 if (l_get_fin_plan_ver_csr.preference_code = 'COST_ONLY') then
375 l_cost_current_planning_period := l_curr_plan_period;
376 l_cost_period_mask_id := l_period_mask_id;
377 elsif (l_get_fin_plan_ver_csr.preference_code = 'REVENUE_ONLY') then
378 l_rev_current_planning_period := l_curr_plan_period;
379 l_rev_period_mask_id := l_period_mask_id;
380 elsif (l_get_fin_plan_ver_csr.preference_code = 'COST_AND_REV_SEP') then
381 --Bug 3977417
382 if l_get_fin_plan_ver_csr.cost_time_phased_code='G' then
383 l_cost_current_planning_period := l_cpp_for_gl_time_phase;
384 elsif l_get_fin_plan_ver_csr.cost_time_phased_code='P' then
385 l_cost_current_planning_period := l_cpp_for_pa_time_phase;
386 else
387 l_cost_current_planning_period := NULL;
388 end if;
389
390 if l_get_fin_plan_ver_csr.revenue_time_phased_code='G' then
391 l_rev_current_planning_period := l_cpp_for_gl_time_phase;
392 elsif l_get_fin_plan_ver_csr.revenue_time_phased_code='P' then
393 l_rev_current_planning_period := l_cpp_for_pa_time_phase;
394 else
395 l_rev_current_planning_period := NULL;
396 end if;
397
398 else
399 l_all_current_planning_period := l_curr_plan_period;
400 l_all_period_mask_id := l_period_mask_id;
401 end if;
402
403 IF l_debug_mode = 'Y' THEN
404 pa_debug.g_err_stage := 'Update Pa_proj_fp_options Table.';
405 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
406 end if;
407
408 update pa_proj_fp_options
409 SET cost_current_planning_period = l_cost_current_planning_period,
410 cost_period_mask_id = l_cost_period_mask_id,
411 rev_current_planning_period = l_rev_current_planning_period,
412 rev_period_mask_id = l_rev_period_mask_id,
413 all_current_planning_period = l_all_current_planning_period ,
414 all_period_mask_id = l_all_period_mask_id,
415 rbs_version_id = l_rbs_version_id, /* Rbs upgrade happens based on cost resource list id in case of cost and rev sep pref code */
416 all_FIN_PLAN_LEVEL_CODE = decode(all_FIN_PLAN_LEVEL_CODE, 'M', 'L', all_FIN_PLAN_LEVEL_CODE), /* bug 3820552 */
417 cost_FIN_PLAN_LEVEL_CODE = decode(cost_FIN_PLAN_LEVEL_CODE, 'M', 'L', cost_FIN_PLAN_LEVEL_CODE), /* bug 3820552 */
418 revenue_FIN_PLAN_LEVEL_CODE = decode(revenue_FIN_PLAN_LEVEL_CODE, 'M', 'L', revenue_FIN_PLAN_LEVEL_CODE), /* bug 3820552 */
419 use_planning_rates_flag = 'N'
420 where proj_fp_options_id = l_get_fin_plan_ver_csr.proj_fp_options_id;
421
422
423 IF l_get_fin_plan_ver_csr.fin_plan_option_level_code = 'PLAN_VERSION' THEN
424
425 -- Update budget version table with the above values
426 IF l_debug_mode = 'Y' THEN
427 pa_debug.g_err_stage := 'Update Pa_Budget_versions Table.';
428 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
429 end if;
430
431 update pa_budget_versions
432 set project_structure_version_id = NULL,
433 object_type_code = 'PROJECT',
434 object_id = l_get_fin_plan_ver_csr.project_id,
435 pji_summarized_flag = 'N',
436 current_planning_period = l_curr_plan_period,
437 period_mask_id = l_period_mask_id,
438 wp_version_flag = 'N' /* Bug 3799921: added this column */
439 where budget_version_id = l_get_fin_plan_ver_csr.fin_plan_version_id;
440
441 END IF;
442
443 /* Bug 3935863: RBS migration does not happen for uncategorized resource lists.
444 So, PA_RBS_ASGMT_PUB.Create_RBS_Assignment should not be called for such resource lists. */
445 IF (l_uncategorized_flag <> 'Y') THEN
446 -- Bug 3800485, 28-JUL-04, jwhite -----------------------------------------------
447
448 Open get_rbs_header_csr(l_rbs_version_id);
449 Fetch get_rbs_header_csr INTO l_rbs_header_id;
450 Close get_rbs_header_csr;
451
452
453
454 PA_RBS_ASGMT_PUB.Create_RBS_Assignment
455 ( p_commit => FND_API.G_FALSE
456 , p_init_msg_list => FND_API.G_FALSE
457 , p_rbs_header_id => l_rbs_header_id
458 , p_rbs_version_id => l_rbs_version_id
459 , p_project_id => p_project_id
460 , p_fp_usage_flag => 'Y'
461 , x_return_status => l_return_status
462 , x_msg_count => l_msg_count
463 , x_error_msg_data => l_msg_data
464 );
465
466
467 IF ( l_return_status <> 'S')
468 THEN
469 IF l_debug_mode = 'Y' THEN
470 pa_debug.g_err_stage := 'p_budget_version_id='||to_char(p_budget_version_id);
471 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
472 END IF;
473 PA_UTILS.ADD_MESSAGE(p_app_short_name=> 'PA',
474 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
475 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
476 END IF;
477
478 -- End bug 3800485 --------------------------------------------------------------
479 END IF; --l_uncategorized_flag <> 'Y'
480 end loop;
481 -- Loop for each fin plan version ends here.
482
483 EXCEPTION
484 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc then
485 l_msg_count := FND_MSG_PUB.count_msg;
486 IF l_msg_count = 1 THEN
487 PA_INTERFACE_UTILS_PUB.get_messages
488 (p_encoded => FND_API.G_TRUE
489 ,p_msg_index => 1
490 ,p_msg_count => l_msg_count
491 ,p_msg_data => l_msg_data
492 ,p_data => l_data
493 ,p_msg_index_out => l_msg_index_out);
494 x_msg_data := l_data;
495 x_msg_count := l_msg_count;
496 ELSE
497 x_msg_count := l_msg_count;
498 x_msg_data := l_msg_data;
499 END IF;
500
501 IF l_debug_mode = 'Y' THEN
502 pa_debug.g_err_stage:='Invalid Arguments Passed';
503 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
504 END IF;
505 x_return_status:= FND_API.G_RET_STS_ERROR;
506 pa_debug.write_file('BUDGET_ATTR_UPGRD_ATTR : Upgrade has failed for the project: '||p_project_id,5);
507 pa_debug.write_file('BUDGET_ATTR_UPGRD : Failure Reason:'||x_msg_data,5);
508 pa_debug.reset_err_stack;
509 --ROLLBACK; /* Should Not have Rollback in this package. Only main API rollback should be active */
510 RAISE;
511 WHEN OTHERS THEN
512
513 if get_fin_plan_ver_csr%ISOPEN then
514 close get_fin_plan_ver_csr;
515 end if;
516 if get_rbs_ver_csr%ISOPEN then
517 close get_rbs_ver_csr;
518 end if;
519 if get_per_mask_id_csr%ISOPEN then
520 close get_per_mask_id_csr;
521 end if;
522 if get_rbs_header_csr%ISOPEN then
523 close get_rbs_header_csr;
524 end if;
525 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
526 x_msg_count := 1;
527 x_msg_data := SQLERRM;
528
529 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_BUDGET_ATTR_UPGR_PKG',p_procedure_name => 'BUDGET_ATTR_UPGRD');
530 IF l_debug_mode = 'Y' THEN
531 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
532 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
533 END IF;
534
535 pa_debug.write_file('BUDGET_ATTR_UPGRD : Upgrade has failed for the project'||p_project_id,5);
536 pa_debug.write_file('BUDGET_ATTR_UPGRD : Failure Reason:'||pa_debug.G_Err_Stack,5);
537 pa_debug.reset_err_stack;
538 --ROLLBACK; /* Should Not have Rollback in this package. Only main API rollback should be active */
539 RAISE;
540 end BUDGET_ATTR_UPGRD;
541 end PA_BUDGET_ATTR_UPGR_PKG;