[Home] [Help]
PACKAGE BODY: APPS.GMS_BUDGET_CORE
Source
1 PACKAGE BODY gms_budget_core AS
2 /* $Header: gmsbubcb.pls 120.2.12010000.2 2008/12/23 05:31:34 prabsing ship $ */
3
4 -- To check on, whether to print debug messages in log file or not
5 L_DEBUG varchar2(1) := NVL(FND_PROFILE.value('GMS_ENABLE_DEBUG_MODE'), 'N');
6
7 /***************** commenting this whole procedure for perf fix 4007119.
8 procedure shift_periods(x_start_period_date in date,
9 x_periods in number,
10 x_period_name in out NOCOPY varchar2,
11 x_period_type in varchar2,
12 x_start_date in out NOCOPY date,
13 x_end_date in out NOCOPY date,
14 x_err_code in out NOCOPY number,
15 x_err_stage in out NOCOPY varchar2,
16 x_err_stack in out NOCOPY varchar2)
17 is
18 cursor c is
19 select period_name, period_start_date , period_end_date
20 from pa_budget_periods_v
21 where period_type_code= x_period_type
22 and period_start_date > x_start_period_date
23 order by period_start_date ;
24
25 cursor c1 is
26 select period_name, period_start_date , period_end_date
27 from PA_budget_periods_v
28 where period_type_code= x_period_type
29 and period_start_date < x_start_period_date
30 order by period_start_date desc;
31
32 old_stack varchar2(630);
33 number_period number(10);
34
35 begin
36 x_err_code := 0;
37 old_stack := x_err_stack;
38 x_err_stack := x_err_stack || '->shift_periods';
39
40 if x_periods > 0 then
41
42 select count(*)
43 into number_period
44 from pa_budget_periods_v
45 where period_type_code= x_period_type
46 and period_start_date > x_start_period_date;
47
48 if number_period < abs(x_periods) then
49 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_INVALID_NEW_PERIOD',
50 x_err_code => x_err_code,
51 x_err_buff => x_err_stage);
52
53 APP_EXCEPTION.RAISE_EXCEPTION;
54 end if;
55
56 open c;
57 for i in 1..abs(x_periods)
58 loop
59 fetch c into x_period_name, x_start_date, x_end_date;
60 exit when c%notfound;
61 end loop;
62 close c;
63 elsif x_periods < 0 then
64
65 select count(*)
66 into number_period
67 from pa_budget_periods_v
68 where period_type_code= x_period_type
69 and period_start_date < x_start_period_date;
70
71 if number_period < abs(x_periods) then
72 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_INVALID_NEW_PERIOD',
73 x_err_code => x_err_code,
74 x_err_buff => x_err_stage);
75
76 APP_EXCEPTION.RAISE_EXCEPTION;
77 end if;
78
79 open c1;
80 for i in 1..abs(x_periods)
81 loop
82 fetch c1 into x_period_name, x_start_date, x_end_date;
83 exit when c1%notfound;
84 end loop;
85 close c1;
86 end if;
87
88 ***********************/
89 -- Bug 4007119 .. shift_periods re-written to use pl/sql tables.
90
91 procedure shift_periods(x_start_period_date in date,
92 x_periods in number,
93 x_period_name in out NOCOPY varchar2,
94 x_period_type in varchar2,
95 x_start_date in out NOCOPY date,
96 x_end_date in out NOCOPY date,
97 x_err_code in out NOCOPY number,
98 x_err_stage in out NOCOPY varchar2,
99 x_err_stack in out NOCOPY varchar2)
100 is
101
102 TYPE tt_date is table of date;
103 TYPE tt_period_name is table of varchar2(30);
104
105 t_start_date tt_date;
106 t_end_date tt_date;
107 t_period_name tt_period_name;
108
109 old_stack varchar2(630);
110 number_period number(10);
111 current_period_index number;
112 shift_by_index number;
113
114 begin
115
116 x_err_code := 0;
117 old_stack := x_err_stack;
118 x_err_stack := x_err_stack || '->shift_periods';
119
120 if x_period_type = 'P' then
121
122 select p.period_name, p.start_date, p.end_date
123 bulk collect into t_period_name, t_start_date, t_end_date
124 from pa_periods p
125 order by p.start_date;
126
127 elsif x_period_type = 'G' then
128
129 select p.period_name, p.start_date, p.end_date
130 bulk collect into t_period_name, t_start_date, t_end_date
131 from gl_period_statuses p, pa_implementations i
132 where i.set_of_books_id = p.set_of_books_id
133 and p.application_id = pa_period_process_pkg.application_id
134 and p.adjustment_period_flag = 'N'
135 order by p.start_date;
136
137 end if;
138
139 number_period := 0;
140
141 if x_periods > 0 then
142
143 for i in t_start_date.first..t_start_date.last loop
144 if t_start_date(i) = x_start_period_date then
145 current_period_index := i;
146 end if;
147 if t_start_date(i) > x_start_period_date then
148 number_period := number_period + 1;
149 end if;
150 end loop;
151
152 if number_period < x_periods then
153 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_INVALID_NEW_PERIOD',
154 x_err_code => x_err_code,
155 x_err_buff => x_err_stage);
156
157 APP_EXCEPTION.RAISE_EXCEPTION;
158 end if;
159
160 -- the new budget starts later than the source budget.
161 -- identify the period by advancing the index to the appropriate period.
162 -- pl/sql table is order by start_date asc.
163
164 shift_by_index := current_period_index + x_periods;
165
166 x_period_name := t_period_name(shift_by_index);
167 x_start_date := t_start_date(shift_by_index);
168 x_end_date := t_end_date(shift_by_index);
169
170 elsif x_periods < 0 then
171
172 for i in t_start_date.first..t_start_date.last loop
173 if t_start_date(i) = x_start_period_date then
174 current_period_index := i;
175 end if;
176 if t_start_date(i) < x_start_period_date then
177 number_period := number_period + 1;
178 end if;
179 end loop;
180
181 if number_period < abs(x_periods) then
182 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_INVALID_NEW_PERIOD',
183 x_err_code => x_err_code,
184 x_err_buff => x_err_stage);
185
186 APP_EXCEPTION.RAISE_EXCEPTION;
187 end if;
188
189 -- the new budget starts earlier than the source budget.
190 -- identify the period by moving back the index to the appropriate period.
191 -- pl/sql table is order by start_date asc.
192
193 shift_by_index := current_period_index - abs(x_periods);
194
195 x_period_name := t_period_name(shift_by_index);
196 x_start_date := t_start_date(shift_by_index);
197 x_end_date := t_end_date(shift_by_index);
198
199 end if;
200
201 t_period_name.delete;
202 t_start_date.delete;
203 t_end_date.delete;
204
205 x_err_stack := old_stack;
206
207 exception
208 when NO_DATA_FOUND
209 then
210 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_INVALID_NEW_PERIOD',
211 x_err_code => x_err_code,
212 x_err_buff => x_err_stage);
213
214
215 when OTHERS then
216 gms_error_pkg.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
217 x_token_name1 => 'SQLCODE',
218 x_token_val1 => sqlcode,
219 x_token_name2 => 'SQLERRM',
220 x_token_val2 => sqlerrm,
221 x_err_code => x_err_code,
222 x_err_buff => x_err_stage);
223
224 -- x_err_code := SQLCODE;
225 -- return;
226 end;
227 -------------------------------------------------------------------------------
228 -- procedure get_periods identifies the number of periods between the start of
229 -- source budget and dest budget. this is used by shift_periods procedure to
230 -- identify the details of the new period.
231
232 procedure get_periods(x_start_date1 in date,
233 x_start_date2 in date,
234 x_period_type in varchar2,
235 x_periods in out NOCOPY number,
236 x_err_code in out NOCOPY number,
237 x_err_stage in out NOCOPY varchar2,
238 x_err_stack in out NOCOPY varchar2)
239 is
240 x_period_start_date1 date;
241 x_period_start_date2 date;
242
243 /********** commented out for bug 4007119
244 cursor c is
245 select count(1) -1
246 from pa_budget_periods_v
247 where period_type_code= x_period_type
248 and period_start_date between least(x_period_start_date1,x_period_start_date2) and greatest(x_period_start_date1,x_period_start_date2);
249 ********** commented out for bug 4007119 ****/
250
251 old_stack varchar2(630);
252 begin
253 x_err_code := 0;
254 old_stack := x_err_stack;
255 x_err_stack := x_err_stack || '->get_periods';
256
257 -- Bug 4007119..changed the code to use base tables instead of pa_budget_periods_v view.
258
259 if x_period_type = 'P' then
260
261 select start_date
262 into x_period_start_date1
263 from pa_periods
264 where x_start_date1 between start_date and end_date;
265
266 select start_date
267 into x_period_start_date2
268 from pa_periods
269 where x_start_date2 between start_date and end_date;
270
271 select count(1) - 1
272 into x_periods
273 from pa_periods
274 where start_date between least(x_period_start_date1, x_period_start_date2)
275 and greatest(x_period_start_date1, x_period_start_date2);
276
277 elsif x_period_type = 'G' then
278
279 select p.start_date
280 into x_period_start_date1
281 from gl_period_statuses p, pa_implementations i
282 where i.set_of_books_id = p.set_of_books_id
283 and p.application_id = pa_period_process_pkg.application_id
284 and p.adjustment_period_flag = 'N'
285 and x_start_date1 between p.start_date and p.end_date;
286
287 select p.start_date
288 into x_period_start_date2
289 from gl_period_statuses p, pa_implementations i
290 where i.set_of_books_id = p.set_of_books_id
291 and p.application_id = pa_period_process_pkg.application_id
292 and p.adjustment_period_flag = 'N'
293 and x_start_date2 between p.start_date and p.end_date;
294
295 select count(1) - 1
296 into x_periods
297 from gl_period_statuses p, pa_implementations i
298 where i.set_of_books_id = p.set_of_books_id
299 and p.application_id = pa_period_process_pkg.application_id
300 and p.adjustment_period_flag = 'N'
301 and p.start_date between least(x_period_start_date1,x_period_start_date2)
302 and greatest(x_period_start_date1,x_period_start_date2);
303
304 end if;
305
306 /**** commented out for bug 4007119
307 open c;
308 fetch c into x_periods;
309 close c;
310 *********/
311
312 if x_start_date1 > x_start_date2 then
313 x_periods := -1* x_periods;
314 end if;
315
316 x_err_stack := old_stack;
317
318 exception
319 when NO_DATA_FOUND
320 then
321 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_INVALID_NEW_PERIOD',
322 x_err_code => x_err_code,
323 x_err_buff => x_err_stage);
324
325 when OTHERS
326 then
327 gms_error_pkg.gms_message( x_err_name => 'GMS_UNEXPECTED_ERROR',
328 x_token_name1 => 'SQLCODE',
329 x_token_val1 => sqlcode,
330 x_token_name2 => 'SQLERRM',
331 x_token_val2 => sqlerrm,
332 x_err_code => x_err_code,
333 x_err_buff => x_err_stage);
334
335 -- x_err_code := SQLCODE;
336 -- return;
337 end;
338
339 ------------------------------------------------------------------------------
340
341 procedure baseline (x_draft_version_id in number,
342 x_mark_as_original in varchar2,
343 x_verify_budget_rules in varchar2 default 'Y',
344 x_err_code in out NOCOPY number,
345 x_err_stage in out NOCOPY varchar2,
346 x_err_stack in out NOCOPY varchar2)
347
348 IS
349 -- Standard who
350 x_created_by number(15);
351 x_last_update_login number(15);
352
353 x_project_id number(15);
354 x_award_id number(15);
355 x_budget_type_code varchar2(30);
356 max_version number(15);
357 x_dest_version_id number(15);
358 x_entry_level_code varchar2(30);
359 x_project_type_class_code varchar2(30);
360 dummy number;
361 budget_total number default 0;
362 old_stack varchar2(630);
363 x_resource_list_assgmt_id number;
364 x_resource_list_id number;
365 x_baselined_version_id number;
366 x_funding_level varchar2(2) default NULL;
367 x_time_phased_type_code varchar2(30);
368
369 l_warnings_only_flag VARCHAR2(1) := 'Y';
370 l_err_msg_count NUMBER := 0;
371 v_project_start_date date;
372 v_project_completion_date date;
373 v_emp_id number;
374
375 x_budget_start_date date;
376 x_budget_end_date date;
377 x_period_name varchar2(20);
378
379
380 BEGIN
381
382 IF L_DEBUG = 'Y' THEN
383 gms_error_pkg.gms_debug('*** Start of GMS_BUDGET_CORE.BASELINE ***','C');
384 END IF;
385
386 savepoint before_baseline;
387
388 x_err_code := 0;
389 old_stack := x_err_stack;
390 x_err_stack := x_err_stack || '->baseline';
391
392 x_created_by := FND_GLOBAL.USER_ID;
393 x_last_update_login := FND_GLOBAL.LOGIN_ID;
394
395 -- This call is repeated in GMS_BUDGET_UTILS.Verify_Budget_Rules
396 -- as the APIs call that procedure. Using v_emp_id eliminates join
397 -- to fnd_user while inserting record in gms_budget_versions
398
399 v_emp_id := PA_UTILS.GetEmpIdFromUser(x_created_by );
400
401 if v_emp_id IS NULL then
402 x_err_stage := 'GMS_BUDGET_CORE.BASELINE - Error occurred while validating employee information';
403 gms_error_pkg.gms_message( x_err_name => 'GMS_ALL_WARN_NO_EMPL_REC',
404 x_err_code => x_err_code,
405 x_err_buff => x_err_stage);
406
407 fnd_msg_pub.add; --Bug 2587078
408
409 APP_EXCEPTION.RAISE_EXCEPTION;
410 end if;
411
412 x_err_stage := 'get draft budget info <' || to_char(x_draft_version_id)
413 || '>';
414
415 select v.project_id, v.award_id, v.budget_type_code, v.resource_list_id,
416 t.project_type_class_code,time_phased_type_code,
417 entry_level_code
418 into x_project_id, x_award_id, x_budget_type_code, x_resource_list_id,
419 x_project_type_class_code,x_time_phased_type_code,
420 x_entry_level_code
421 from pa_project_types t,
422 pa_projects p,
423 gms_budget_versions v,
424 pa_budget_entry_methods b
425 where v.budget_version_id = x_draft_version_id
426 and v.project_id = p.project_id
427 and b.budget_entry_method_code = v.budget_entry_method_code
428 and p.project_type = t.project_type;
429
430 ---------------------------------------------------------------------------------------
431
432 -- Need to check if call is for verification purpose only
433
434 IF ( x_verify_budget_rules = 'Y' )
435 THEN
436
437 IF L_DEBUG = 'Y' THEN
438 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Calling GMS_BUDGET_UTILS.Verify_Budget_Rules - Baseline','C');
439 END IF;
440
441 GMS_BUDGET_UTILS.Verify_Budget_Rules
442 (p_draft_version_id => x_draft_version_id
443 , p_mark_as_original => x_mark_as_original
444 , p_event => 'BASELINE'
445 , p_project_id => x_project_id
446 , p_award_id => x_award_id
447 , p_budget_type_code => x_budget_type_code
448 , p_resource_list_id => x_resource_list_id
449 , p_project_type_class_code => x_project_type_class_code
450 , p_created_by => x_created_by
451 , p_calling_module => 'GMSBUBCB'
452 , p_warnings_only_flag => l_warnings_only_flag
453 , p_err_msg_count => l_err_msg_count
454 , p_err_code => x_err_code
455 , p_err_stage => x_err_stage
456 , p_err_stack => x_err_stack
457 );
458
459 -- Bug 2587078 : Replacing check from l_err_msg_count > 0 to x_err_code <> 0
460 -- as the l_err_msg_count is not set in all the error cases .
461
462 --IF (l_err_msg_count > 0)
463 IF (x_err_code <> 0)
464 THEN
465 IF (l_warnings_only_flag = 'N') THEN
466 x_err_stage := 'GMS_BUDGET_CORE.BASELINE - Error occurred while validating Budget';
467 gms_error_pkg.gms_message(x_err_name => 'GMS_VERIFY_BUDGET_FAIL_B',
468 x_err_code => x_err_code,
469 x_err_buff => x_err_stage);
470 fnd_msg_pub.add;
471 RETURN;
472 END IF;
473 END IF;
474 END IF; -- x_verify_budget_rules = 'Y'
475
476 -- End R11 rewrite
477 -- ----------------------------------------------------------------------------------
478
479 IF L_DEBUG = 'Y' THEN
480 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Calling GMS_BUDGET_UTILS.get_baselined_version_id','C');
481 END IF;
482
483 GMS_BUDGET_UTILS.get_baselined_version_id(x_project_id,
484 x_award_id,
485 x_budget_type_code,
486 x_baselined_version_id,
487 x_err_code,
488 x_err_stage,
489 x_err_stack);
490
491 if (x_err_code < 0) then
492 IF L_DEBUG = 'Y' THEN
493 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Call to GMS_BUDGET_UTILS.get_baselined_version_id returned x_err_code : '||x_err_code ,'C');
494 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Call to GMS_BUDGET_UTILS.get_baselined_version_id returned x_err_stage : '||x_err_stage ,'C');
495 END IF;
496
497 rollback to before_baseline;
498 return;
499
500 elsif (x_err_code > 0) then
501
502 -- baseline budget does not exist
503
504 IF L_DEBUG = 'Y' THEN
505 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- First time baselining','C');
506 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Calling pa_res_list_assignments.create_rl_assgmt','C');
507 END IF;
508
509 x_err_stage := 'create resource list assignment <'
510 || to_char(x_project_id) || '><'
511 || to_char(x_resource_list_id) || '>';
512
513 -- create resource list assignment if necessary
514 pa_res_list_assignments.create_rl_assgmt(x_project_id,
515 x_resource_list_id,
516 x_resource_list_assgmt_id,
517 x_err_code,
518 x_err_stage,
519 x_err_stack);
520
521 -- if oracle or application error, return
522 if (x_err_code <> 0) then
523 IF L_DEBUG = 'Y' THEN
524 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Call to pa_res_list_assignments.create_rl_assgmt returned x_err_code : '||x_err_code ,'C');
525 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Call to pa_res_list_assignments.create_rl_assgmt returned x_err_stage : '||x_err_stage ,'C');
526 END IF;
527 rollback to before_baseline;
528 return;
529 end if;
530
531 x_err_stage := 'create resource list usage <'
532 || to_char(x_project_id) || '><'
533 || to_char(x_resource_list_assgmt_id) || '><'
534 || x_budget_type_code || '>';
535
536 -- create resource list usage if necessary
537
538 IF L_DEBUG = 'Y' THEN
539 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Calling pa_res_list_assignments.create_rl_uses','C');
540 END IF;
541
542 pa_res_list_assignments.create_rl_uses(x_project_id,
543 x_resource_list_assgmt_id,
544 x_budget_type_code,
545 x_err_code,
546 x_err_stage,
547 x_err_stack);
548
549 -- if oracle or application error, return.
550
551 if (x_err_code <> 0) then
552 IF L_DEBUG = 'Y' THEN
553 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Call to pa_res_list_assignments.create_rl_uses returned x_err_code :'||x_err_code ,'C');
554 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Call to pa_res_list_assignments.create_rl_uses returned x_err_stage :'||x_err_stage ,'C');
555 END IF;
556 rollback to before_baseline;
557 return;
558 end if;
559
560 end if;
561
562 x_err_stage := 'update current version <' || to_char(x_project_id) || '><'
563 || x_budget_type_code || '>';
564
565
566 IF L_DEBUG = 'Y' THEN
567 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- x_mark_as_original : '||x_mark_as_original ,'C');
568 END IF;
569
570 if (x_mark_as_original = 'Y') then
571
572 -- reset current budget version to non-current
573 update gms_budget_versions
574 set original_flag = 'Y',
575 current_original_flag = 'N',
576 last_update_date = SYSDATE,
577 last_updated_by = x_created_by,
578 last_update_login = x_last_update_login
579 where project_id = x_project_id
580 and award_id = x_award_id
581 and budget_type_code = x_budget_type_code
582 and current_original_flag = 'Y';
583
584 end if;
585
586 -------------------------------------------------------------------------------------------
587 -- 04-June-2000
588 -- Setting the current budget version's current_flag to an intermediate stage 'R',
589 -- which will be set to either 'N' (if Funds check passes) or 'Y' (if Funds check fails)
590 -- at the end of Baseline process.
591 -------------------------------------------------------------------------------------------
592
593 IF L_DEBUG = 'Y' THEN
594 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Updating current_flag to an intermediate status R','C');
595 END IF;
596
597 update gms_budget_versions
598 set current_flag = 'R',
599 last_update_date = SYSDATE,
600 last_updated_by = x_created_by,
601 last_update_login = x_last_update_login
602 where project_id = x_project_id
603 and award_id = x_award_id
604 and budget_type_code = x_budget_type_code
605 and current_flag = 'Y';
606
607 -- get the maximun number of existing versions
608 x_err_stage := 'get maximum baseline number <' || to_char(x_project_id)
609 || '><' || x_budget_type_code || '>';
610
611 select nvl(max(version_number), 0)
612 into max_version
613 from gms_budget_versions
614 where project_id = x_project_id
615 and award_id = x_award_id
616 and budget_type_code = x_budget_type_code
617 and budget_status_code = 'B';
618
619 -- get the dest version id
620 select gms_budget_versions_s.nextval
621 into x_dest_version_id
622 from sys.dual;
623
624 -- populate gms_budget_versions
625 x_err_stage := 'create baselined version <' || to_char(x_dest_version_id)
626 || '><' || to_char(max_version)
627 || '><' || to_char(x_created_by) || '>';
628
629 IF L_DEBUG = 'Y' THEN
630 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Inserting records into gms_budget_versions','C');
631 END IF;
632
633 insert into gms_budget_versions(
634 budget_version_id,
635 project_id,
636 award_id,
637 budget_type_code,
638 version_number,
639 budget_status_code,
640 last_update_date,
641 last_updated_by,
642 creation_date,
643 created_by,
644 last_update_login,
645 current_flag,
646 original_flag,
647 current_original_flag,
648 resource_accumulated_flag,
649 resource_list_id,
650 version_name,
651 budget_entry_method_code,
652 baselined_by_person_id,
653 baselined_date,
654 change_reason_code,
655 labor_quantity,
656 labor_unit_of_measure,
657 raw_cost,
658 burdened_cost,
659 revenue,
660 description,
661 attribute_category,
662 attribute1,
663 attribute2,
664 attribute3,
665 attribute4,
666 attribute5,
667 attribute6,
668 attribute7,
669 attribute8,
670 attribute9,
671 attribute10,
672 attribute11,
673 attribute12,
674 attribute13,
675 attribute14,
676 attribute15,
677 first_budget_period,
678 pm_product_code,
679 pm_budget_reference,
680 wf_status_code )
681 select
682 x_dest_version_id,
683 v.project_id,
684 v.award_id,
685 v.budget_type_code,
686 max_version + 1,
687 'B',
688 SYSDATE,
689 x_created_by,
690 SYSDATE,
691 x_created_by,
692 x_last_update_login,
693 -- 'Y',
694 'N', -- 29-May-2000
695 'N',
696 x_mark_as_original,
697 'N',
698 v.resource_list_id,
699 v.version_name,
700 v.budget_entry_method_code,
701 v_emp_id,
702 SYSDATE,
703 v.change_reason_code,
704 (v.labor_quantity),
705 v.labor_unit_of_measure,
706 v.raw_cost,
707 v.burdened_cost,
708 v.revenue,
709 v.description,
710 v.attribute_category,
711 v.attribute1,
712 v.attribute2,
713 v.attribute3,
714 v.attribute4,
715 v.attribute5,
716 v.attribute6,
717 v.attribute7,
718 v.attribute8,
719 v.attribute9,
720 v.attribute10,
721 v.attribute11,
722 v.attribute12,
723 v.attribute13,
724 v.attribute14,
725 v.attribute15,
726 first_budget_period,
727 pm_product_code,
728 pm_budget_reference,
729 NULL
730 from gms_budget_versions v
731 where budget_version_id = x_draft_version_id;
732
733 x_err_stage := 'create budget lines <' || to_char(x_draft_version_id)
734 || '><' || to_char(x_dest_version_id)
735 || '>';
736
737 IF L_DEBUG = 'Y' THEN
738 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Calling gms_budget_core.copy_draft_lines','C');
739 END IF;
740
741 gms_budget_core.copy_draft_lines(x_src_version_id => x_draft_version_id,
742 x_time_phased_type_code => x_time_phased_type_code,
743 x_entry_level_code => x_entry_level_code,
744 x_dest_version_id => x_dest_version_id,
745 x_err_code => x_err_code,
746 x_err_stage => x_err_stage,
747 x_err_stack => x_err_stack,
748 x_pm_flag => 'Y');
749
750
751 if (x_err_code <> 0) then
752 IF L_DEBUG = 'Y' THEN
753 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Call to gms_budget_core.copy_draft_lines returned x_err_code : '||x_err_code ,'C');
754 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Call to gms_budget_core.copy_draft_lines returned x_err_stage : '||x_err_stage ,'C');
755 END IF;
756 rollback to before_baseline;
757 return;
758 end if;
759
760 -- If the effective dates on Project/Tasks
761 -- has changed for Non Time phased budgets, then update the
762 -- start and end dates on the budget lines.
763
764 -- Begin Bug 2404567
765 -- gp_msg('TIME:'||x_time_phased_type_code||':ENTRY:'||x_entry_level_code);
766
767 IF L_DEBUG = 'Y' THEN
768 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- x_time_phased_type_code : '||x_time_phased_type_code,'C');
769 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- x_entry_level_code : '||x_entry_level_code,'C');
770 END IF;
771
772 if (x_time_phased_type_code = 'N')
773 and (x_entry_level_code = 'P') then -- Project Level
774
775 -- Added call to GMS_BUDGET_UTILS.get_valid_period_dates() for Bug:2592747
776
777 IF L_DEBUG = 'Y' THEN
778 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Calling gms_budget_utils.get_valid_period_dates','C');
779 END IF;
780
781 gms_budget_utils.get_valid_period_dates(
782 x_err_code => x_err_code,
783 x_err_stage => x_err_stage,
784 p_project_id => x_project_id,
785 p_task_id => NULL,
786 p_award_id => x_award_id,
787 p_time_phased_type_code => x_time_phased_type_code,
788 p_entry_level_code => x_entry_level_code,
789 p_period_name_in => null,
790 p_budget_start_date_in => null,
791 p_budget_end_date_in => null,
792 p_period_name_out => x_period_name,
793 p_budget_start_date_out => v_project_start_date,
794 p_budget_end_date_out => v_project_completion_date);
795
796
797 IF L_DEBUG = 'Y' THEN
798 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Updating dates on gms_budget_lines','C');
799 END IF;
800
801 -- update for baselined version
802 update gms_budget_lines
803 set start_date= v_project_start_date,
804 end_date = v_project_completion_date
805 where resource_assignment_id in
806 (select resource_assignment_id
807 from gms_resource_assignments
808 where budget_version_id = x_dest_version_id)
809 and ((start_date <> v_project_start_date) OR (end_date <> v_project_completion_date));
810
811 -- update for draft version
812 update gms_budget_lines
813 set start_date= v_project_start_date,
814 end_date = v_project_completion_date
815 where resource_assignment_id in
816 (select resource_assignment_id
817 from gms_resource_assignments
818 where budget_version_id = x_draft_version_id)
819 and ((start_date <> v_project_start_date) OR (end_date <> v_project_completion_date));
820
821 -- Added check that rows should be updated only if the project start or end
822 -- dates are different from the budget start and end dates
823
824 elsif (x_time_phased_type_code = 'N') then -- Task Level
825
826 select start_date,completion_date
827 into v_project_start_date,
828 v_project_completion_date
829 from pa_projects_all
830 where project_id = x_project_id;
831
832 IF L_DEBUG = 'Y' THEN
833 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Calling gms_budget_utils.get_valid_period_dates','C');
834 END IF;
835
836 for b1_rec in (select t.task_id, resource_assignment_id
837 from pa_tasks t , gms_resource_assignments r
838 where t.task_id = r.task_id
839 and r.budget_version_id = x_dest_version_id) loop
840
841 -- Added the call to gms_budget_utils.get_valid_period_dates() for Bug: 2592747
842
843 gms_budget_utils.get_valid_period_dates(
844 x_err_code => x_err_code,
845 x_err_stage => x_err_stage,
846 p_project_id => x_project_id,
847 p_task_id => b1_rec.task_id,
848 p_award_id => x_award_id,
849 p_time_phased_type_code => x_time_phased_type_code,
850 p_entry_level_code => x_entry_level_code,
851 p_period_name_in => null,
852 p_budget_start_date_in => null,
853 p_budget_end_date_in => null,
854 p_period_name_out => x_period_name,
855 p_budget_start_date_out => x_budget_start_date,
856 p_budget_end_date_out => x_budget_end_date);
857
858 if x_err_code <> 0 then
859
860 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_NO_TASK_PROJ_DATE',
861 x_err_code => x_err_code,
862 x_err_buff => x_err_stage);
863 fnd_msg_pub.add; --Bug 2587078
864 APP_EXCEPTION.RAISE_EXCEPTION;
865
866 end if;
867
868 update gms_budget_lines
869 set start_date = x_budget_start_date,
870 end_date = x_budget_end_date
871 where resource_assignment_id = b1_rec.resource_assignment_id
872 and ((start_date <> x_budget_start_date) or (end_date <> x_budget_end_date));
873
874 end loop;
875
876 IF L_DEBUG = 'Y' THEN
877 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Calling gms_budget_utils.get_valid_period_dates for draft version','C');
878 END IF;
879
880 --following loop is for draft version
881 for b2_rec in (select t.task_id, resource_assignment_id
882 from pa_tasks t , gms_resource_assignments r
883 where t.task_id = r.task_id
884 and r.budget_version_id = x_draft_version_id) loop
885
886 -- Added the call to gms_budget_utils.get_valid_period_dates() for Bug: 2592747
887
888 gms_budget_utils.get_valid_period_dates(
889 x_err_code => x_err_code,
890 x_err_stage => x_err_stage,
891 p_project_id => x_project_id,
892 p_task_id => b2_rec.task_id,
893 p_award_id => x_award_id,
894 p_time_phased_type_code => x_time_phased_type_code,
895 p_entry_level_code => x_entry_level_code,
896 p_period_name_in => null,
897 p_budget_start_date_in => null,
898 p_budget_end_date_in => null,
899 p_period_name_out => x_period_name,
900 p_budget_start_date_out => x_budget_start_date,
901 p_budget_end_date_out => x_budget_end_date);
902
903 if x_err_code <> 0 then
904
905 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_NO_TASK_PROJ_DATE',
906 x_err_code => x_err_code,
907 x_err_buff => x_err_stage);
908 fnd_msg_pub.add; --Bug 2587078
909 APP_EXCEPTION.RAISE_EXCEPTION;
910
911 end if;
912
913 update gms_budget_lines
914 set start_date = x_budget_start_date,
915 end_date = x_budget_end_date
916 where resource_assignment_id = b2_rec.resource_assignment_id
917 and ((start_date <> x_budget_start_date) or (end_date <> x_budget_end_date));
918
919
920 end loop;
921
922 -- x_err_stage :='GMS_BU_NO_TASK_PROJ_DATE';
923
924 if x_err_code <> 0 then
925 return;
926 end if;
927
928 --
929 end if;
930
931 -- End Bug 2404567
932
933 IF L_DEBUG = 'Y' THEN
934 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Calling fnd_attached_documents2_pkg.copy_attachments','C');
935 END IF;
936
937 -- Copy attachments for every draft budget copied
938
939 fnd_attached_documents2_pkg.copy_attachments('GMS_BUDGET_VERSIONS',
940 x_draft_version_id,
941 null,null,null,null,
942 'GMS_BUDGET_VERSIONS',
943 x_dest_version_id,
944 null,null,null,null,
945 FND_GLOBAL.USER_ID,
946 FND_GLOBAL.LOGIN_ID,
947 275, null, null) ;
948 -- End copying attachments
949
950 IF L_DEBUG = 'Y' THEN
951 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Calling gms_budget_pub.summerize_project_totals','C');
952 END IF;
953
954 gms_budget_pub.summerize_project_totals(x_dest_version_id,
955 x_err_code,
956 x_err_stage,
957 x_err_stack);
958 if (x_err_code <> 0) then
959 IF L_DEBUG = 'Y' THEN
960 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Call to gms_budget_pub.summerize_project_totals returned failed status','C');
961 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Value of x_err_code '||x_err_code,'C');
962 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- Value of x_err_stage '||x_err_stage,'C');
963 END IF;
964 rollback to before_baseline;
965 return;
966 end if;
967
968 x_err_stack := old_stack;
969
970 IF L_DEBUG = 'Y' THEN
971 gms_error_pkg.gms_debug('*** End of GMS_BUDGET_CORE.BASELINE ***','C');
972 END IF;
973
974 exception
975 when OTHERS
976 then
977 IF L_DEBUG = 'Y' THEN
978 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.BASELINE- In when others exception','C');
979 END IF;
980 rollback to before_baseline;
981
982 -- x_err_code := SQLCODE;
983 -- rollback to before_baseline;
984 -- return;
985
986 end baseline;
987
988 -----------------------------------------------------------------------------
989 -- This procedure is called from the Award Budgets form.
990
991 procedure copy (x_src_version_id in number,
992 x_amount_change_pct in number,
993 x_rounding_precision in number,
994 x_shift_days in number,
995 x_dest_project_id in number,
996 x_dest_award_id in number,
997 x_dest_budget_type_code in varchar2,
998 x_err_code in out NOCOPY number,
999 x_err_stage in out NOCOPY varchar2,
1000 x_err_stack in out NOCOPY varchar2)
1001 is
1002 old_stack varchar2(630);
1003 x_dest_version_id number;
1004 x_created_by number;
1005 x_last_update_login number;
1006 x_baselined_version_id number;
1007 x_baselined_resource_list_id number;
1008 x_src_resource_list_id number;
1009 x_resource_list_assgmt_id number;
1010 x_baselined_exists boolean;
1011 x_first_budget_period varchar2(30);
1012 x_time_phased_type_code varchar2(30);
1013 x_entry_level_code varchar2(30);
1014 x_fbp_start_date date;
1015 x_periods number;
1016 x_start_date date;
1017 x_end_date date;
1018
1019 begin
1020
1021 x_err_code := 0;
1022 old_stack := x_err_stack;
1023 x_err_stack := x_err_stack || '->copy';
1024
1025 x_created_by := FND_GLOBAL.USER_ID;
1026 x_last_update_login := FND_GLOBAL.LOGIN_ID;
1027
1028 x_err_stage := 'get project start date <'
1029 || to_char(x_src_version_id) || '>';
1030
1031 select start_date
1032 into g_project_start_date
1033 from pa_projects a, gms_budget_versions b
1034 where b.budget_version_id = x_src_version_id
1035 and a.project_id = b.project_id;
1036
1037 savepoint before_copy;
1038
1039 x_err_stage := 'get source resource list id <'
1040 || to_char(x_src_version_id) || '>';
1041
1042 select resource_list_id,first_budget_period
1043 into x_src_resource_list_id, x_first_budget_period
1044 from gms_budget_versions
1045 where budget_version_id = x_src_version_id;
1046
1047 x_err_stage := 'get baselined budget <' || to_char(x_dest_project_id)
1048 || '><' || x_dest_budget_type_code || '>' ;
1049
1050 -- check if baseline budget already exist
1051 GMS_BUDGET_UTILS.get_baselined_version_id(x_dest_project_id,
1052 x_dest_award_id,
1053 x_dest_budget_type_code,
1054 x_baselined_version_id,
1055 x_err_code,
1056 x_err_stage,
1057 x_err_stack);
1058
1059 if (x_err_code > 0) then
1060 x_baselined_exists := FALSE;
1061
1062 elsif (x_err_code = 0) then
1063 -- baseliend budget exists, verify if resource lists are the same
1064 -- resource list used in accumulation
1065
1066 select resource_list_id
1067 into x_baselined_resource_list_id
1068 from gms_budget_versions
1069 where budget_version_id = x_baselined_version_id;
1070
1071 if (x_src_resource_list_id <> x_baselined_resource_list_id) then
1072
1073 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_BASE_RES_LIST_EXISTS',
1074 x_err_code => x_err_code,
1075 x_err_buff => x_err_stage);
1076
1077 APP_EXCEPTION.RAISE_EXCEPTION;
1078
1079 end if;
1080
1081 x_baselined_exists := TRUE;
1082
1083 else
1084 -- x_err_code < 0
1085 rollback to before_copy;
1086 return;
1087 end if;
1088
1089 x_err_stage := 'delete old draft budget <' || to_char(x_dest_project_id)
1090 || '><' || x_dest_budget_type_code || '>' ;
1091
1092 -- check if destination draft budget exists
1093 GMS_BUDGET_UTILS.get_draft_version_id(x_dest_project_id,
1094 x_dest_award_id,
1095 x_dest_budget_type_code,
1096 x_dest_version_id,
1097 x_err_code,
1098 x_err_stage,
1099 x_err_stack);
1100
1101 if (x_err_code = 0) then
1102 -- draft budget exists, delete it
1103
1104
1105 -- GMS_BUDGET_UTILS.delete_draft(x_dest_version_id,
1106
1107 GMS_BUDGET_PUB.delete_draft_budget(
1108 p_api_version_number => 1.0,
1109 x_err_code => x_err_code,
1110 x_err_stage => x_err_stage,
1111 x_err_stack => x_err_stack,
1112 p_pm_product_code => 'GMS',
1113 p_project_id => x_dest_project_id,
1114 p_award_id => x_dest_award_id,
1115 p_budget_type_code => x_dest_budget_type_code);
1116
1117 end if;
1118
1119 if (x_err_code < 0) then
1120 gms_error_pkg.gms_message( x_err_name => 'GMS_DELETE_DRAFT_FAILED',
1121 x_err_code => x_err_code,
1122 x_err_buff => x_err_stage);
1123
1124 APP_EXCEPTION.RAISE_EXCEPTION;
1125 end if;
1126
1127 /* Only check at baseline
1128 if (x_baselined_exists = FALSE) then
1129
1130 -- create resource list assignment if necessary
1131 x_err_stage := 'create resource list assignment <'
1132 || to_char(x_dest_project_id) || '><'
1133 || to_char(x_src_resource_list_id) || '>';
1134
1135 pa_res_list_assignments.create_rl_assgmt(x_dest_project_id,
1136 x_src_resource_list_id,
1137 x_resource_list_assgmt_id,
1138 x_err_code,
1139 x_err_stage,
1140 x_err_stack);
1141
1142 -- if oracle or application error, return
1143 if (x_err_code <> 0) then
1144 rollback to before_copy;
1145 return;
1146 end if;
1147
1148 x_err_stage := 'create resource list usage <'
1149 || to_char(x_dest_project_id) || '><'
1150 || to_char(x_resource_list_assgmt_id) || '><'
1151 || x_dest_budget_type_code || '>';
1152
1153 -- create resource list usage if necessary
1154 pa_res_list_assignments.create_rl_uses(x_dest_project_id,
1155 x_resource_list_assgmt_id,
1156 x_dest_budget_type_code,
1157 x_err_code,
1158 x_err_stage,
1159 x_err_stack);
1160
1161 -- if oracle or application error, return.
1162
1163 if (x_err_code <> 0) then
1164 rollback to before_copy;
1165 return;
1166 end if;
1167
1168 end if;
1169 */
1170
1171
1172
1173 x_err_stage := 'Getting Budget Entry Method Parameters <'|| to_char(x_src_version_id);
1174 select m.time_phased_type_code,
1175 m.entry_level_code
1176 into x_time_phased_type_code,
1177 x_entry_level_code
1178 from pa_budget_entry_methods m,
1179 gms_budget_versions v
1180 where v.budget_version_id = x_src_version_id
1181 and v.budget_entry_method_code = m.budget_entry_method_code;
1182
1183 -- Shifting the First Budget Period
1184 if ( (nvl(x_shift_days,0) <> 0) and (x_first_budget_period is not null) and (
1185 x_time_phased_type_code not in ('R','N') ) ) then
1186
1187 x_err_stage := 'Getting First Budget Period Start Date <'|| to_char(x_src_version_id);
1188
1189 /****** commented for bug 4007119..
1190 select period_start_date
1191 into x_fbp_start_date
1192 from pa_budget_periods_v
1193 where period_type_code= x_time_phased_type_code
1194 and period_name = x_first_budget_period;
1195 **********/
1196
1197 -- Bug 4007119..changed code to use base tables instead of pa_budget_periods_v view.
1198
1199 if x_time_phased_type_code = 'P' then
1200
1201 select start_date
1202 into x_fbp_start_date
1203 from pa_periods
1204 where period_name = x_first_budget_period;
1205
1206 elsif x_time_phased_type_code = 'G' then
1207
1208 select start_date
1209 into x_fbp_start_date
1210 from gl_period_statuses p, pa_implementations i
1211 where i.set_of_books_id = p.set_of_books_id
1212 and p.application_id = pa_period_process_pkg.application_id
1213 and p.adjustment_period_flag = 'N'
1214 and p.period_name = x_first_budget_period;
1215
1216 end if;
1217
1218 x_err_stage := 'Getting no of periods by which first budget period needs to be shifted<'|| to_char(x_src_version_id);
1219 get_periods(nvl(g_project_start_date,x_fbp_start_date),
1220 nvl(g_project_start_date, x_fbp_start_date)+ x_shift_days,
1221 x_time_phased_type_code ,
1222 x_periods ,
1223 x_err_code ,
1224 x_err_stage,
1225 x_err_stack );
1226
1227 if (x_err_code <> 0) then
1228 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_GET_PERIOD_FAIL',
1229 x_err_code => x_err_code,
1230 x_err_buff => x_err_stage);
1231
1232 APP_EXCEPTION.RAISE_EXCEPTION;
1233 end if;
1234
1235
1236
1237 x_err_stage := 'Shifting first budget period <'|| to_char(x_src_version_id);
1238 shift_periods(x_fbp_start_date,
1239 x_periods ,
1240 x_first_budget_period ,
1241 x_time_phased_type_code,
1242 x_start_date ,
1243 x_end_date,
1244 x_err_code,
1245 x_err_stage ,
1246 x_err_stack );
1247
1248 if (x_err_code <> 0) then
1249 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_SHIFT_PERIOD_FAIL',
1250 x_err_code => x_err_code,
1251 x_err_buff => x_err_stage);
1252
1253 APP_EXCEPTION.RAISE_EXCEPTION;
1254 end if;
1255
1256 end if;
1257
1258
1259 x_err_stage := 'create budget version <' || to_char(x_dest_project_id)
1260 || '><' || x_dest_budget_type_code || '>' ;
1261
1262 select gms_budget_versions_s.nextval
1263 into x_dest_version_id
1264 from sys.dual;
1265
1266 insert into gms_budget_versions(
1267 budget_version_id,
1268 project_id,
1269 award_id,
1270 budget_type_code,
1271 version_number,
1272 budget_status_code,
1273 last_update_date,
1274 last_updated_by,
1275 creation_date,
1276 created_by,
1277 last_update_login,
1278 current_flag,
1279 original_flag,
1280 current_original_flag,
1281 resource_accumulated_flag,
1282 resource_list_id,
1283 version_name,
1284 budget_entry_method_code,
1285 baselined_by_person_id,
1286 baselined_date,
1287 change_reason_code,
1288 labor_quantity,
1289 labor_unit_of_measure,
1290 raw_cost,
1291 burdened_cost,
1292 revenue,
1293 description,
1294 attribute_category,
1295 attribute1,
1296 attribute2,
1297 attribute3,
1298 attribute4,
1299 attribute5,
1300 attribute6,
1301 attribute7,
1302 attribute8,
1303 attribute9,
1304 attribute10,
1305 attribute11,
1306 attribute12,
1307 attribute13,
1308 attribute14,
1309 attribute15,
1310 first_budget_period,
1311 wf_status_code
1312 )
1313 select
1314 x_dest_version_id,
1315 x_dest_project_id,
1316 x_dest_award_id,
1317 x_dest_budget_type_code,
1318 1,
1319 'W',
1320 SYSDATE,
1321 x_created_by,
1322 SYSDATE,
1323 x_created_by,
1324 x_last_update_login,
1325 'N',
1326 'N',
1327 'N',
1328 'N',
1329 v.resource_list_id,
1330 v.version_name,
1331 v.budget_entry_method_code,
1332 NULL,
1333 NULL,
1334 v.change_reason_code,
1335 NULL,
1336 NULL,
1337 NULL,
1338 NULL,
1339 NULL,
1340 v.description,
1341 v.attribute_category,
1342 v.attribute1,
1343 v.attribute2,
1344 v.attribute3,
1345 v.attribute4,
1346 v.attribute5,
1347 v.attribute6,
1348 v.attribute7,
1349 v.attribute8,
1350 v.attribute9,
1351 v.attribute10,
1352 v.attribute11,
1353 v.attribute12,
1354 v.attribute13,
1355 v.attribute14,
1356 v.attribute15,
1357 x_first_budget_period,
1358 NULL
1359 from gms_budget_versions v
1360 where v.budget_version_id = x_src_version_id;
1361
1362 gms_budget_core.copy_lines(x_src_version_id,
1363 x_amount_change_pct,
1364 x_rounding_precision,
1365 x_shift_days,
1366 x_dest_version_id,
1367 x_err_code,
1368 x_err_stage,
1369 x_err_stack);
1370
1371 if (x_err_code <> 0) then
1372 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_COPY_LINES_FAIL',
1373 x_err_code => x_err_code,
1374 x_err_buff => x_err_stage);
1375
1376 APP_EXCEPTION.RAISE_EXCEPTION;
1377 end if;
1378
1379 -- Copy attachments for every draft budget copied
1380
1381 fnd_attached_documents2_pkg.copy_attachments('GMS_BUDGET_VERSIONS',
1382 x_src_version_id,
1383 null,null,null,null,
1384 'GMS_BUDGET_VERSIONS',
1385 x_dest_version_id,
1386 null,null,null,null,
1387 FND_GLOBAL.USER_ID,
1388 FND_GLOBAL.LOGIN_ID,
1389 275, null, null) ;
1390
1391 -- End copying attachments
1392
1393 gms_budget_pub.summerize_project_totals(x_dest_version_id,
1394 x_err_code,
1395 x_err_stage,
1396 x_err_stack);
1397
1398 if (x_err_code <> 0) then
1399 gms_error_pkg.gms_message( x_err_name => 'GMS_SUMMERIZE_TOTALS_FAILED',
1400 x_err_code => x_err_code,
1401 x_err_buff => x_err_stage);
1402
1403 APP_EXCEPTION.RAISE_EXCEPTION;
1404
1405 end if;
1406
1407 x_err_stack := old_stack;
1408
1409 exception
1410 when OTHERS
1411 then
1412 rollback to before_copy;
1413
1414 end copy;
1415
1416 -----------------------------------------------------------------------------
1417
1418 procedure verify (x_budget_version_id in number,
1419 x_err_code in out NOCOPY number,
1420 x_err_stage in out NOCOPY varchar2,
1421 x_err_stack in out NOCOPY varchar2)
1422 is
1423 begin
1424 null;
1425 exception
1426 when others then
1427 x_err_code := SQLCODE;
1428 end verify;
1429
1430
1431 -----------------------------------------------------------------------------
1432
1433 procedure copy_lines (x_src_version_id in number,
1434 x_amount_change_pct in number,
1435 x_rounding_precision in number,
1436 x_shift_days in number,
1437 x_dest_version_id in number,
1438 x_err_code in out NOCOPY number,
1439 x_err_stage in out NOCOPY varchar2,
1440 x_err_stack in out NOCOPY varchar2,
1441 x_pm_flag in varchar2 default 'N')
1442 is
1443 -- Standard who
1444 x_created_by NUMBER(15);
1445 x_last_update_login NUMBER(15);
1446
1447 old_stack varchar2(630);
1448 x_start_date date;
1449 x_end_date date;
1450 x_period_name varchar2(30);
1451 amount_change_pct number;
1452 rounding_precision number;
1453 x_time_phased_type_code varchar2(30);
1454 x_entry_level_code varchar2(30);
1455 x_task_start_date date;
1456 x_periods number;
1457
1458 begin
1459
1460 x_err_code := 0;
1461 old_stack := x_err_stack;
1462 x_err_stack := x_err_stack || '->copy_lines';
1463
1464 x_created_by := FND_GLOBAL.USER_ID;
1465 x_last_update_login := FND_GLOBAL.LOGIN_ID;
1466
1467 if (x_amount_change_pct is not null) then
1468 amount_change_pct := x_amount_change_pct;
1469 else
1470 amount_change_pct := 1;
1471 end if;
1472
1473 if (x_rounding_precision is not null) then
1474 rounding_precision := x_rounding_precision;
1475 else
1476 rounding_precision := 5;
1477 end if;
1478
1479 x_err_stage := 'get time phased type <' || to_char(x_src_version_id)
1480 || '>' ;
1481 select m.time_phased_type_code,
1482 m.entry_level_code
1483 into x_time_phased_type_code,
1484 x_entry_level_code
1485 from pa_budget_entry_methods m,
1486 gms_budget_versions v
1487 where v.budget_version_id = x_src_version_id
1488 and v.budget_entry_method_code = m.budget_entry_method_code;
1489
1490 x_err_stage := 'copy resource assignment <' || to_char(x_src_version_id)
1491 || '>' ;
1492
1493 if (x_entry_level_code <> 'P') then
1494
1495 insert into gms_resource_assignments
1496 (resource_assignment_id,
1497 budget_version_id,
1498 project_id,
1499 task_id,
1500 resource_list_member_id,
1501 last_update_date,
1502 last_updated_by,
1503 creation_date,
1504 created_by,
1505 last_update_login,
1506 unit_of_measure,
1507 track_as_labor_flag)
1508 select gms_resource_assignments_s.nextval,
1509 x_dest_version_id,
1510 dt.project_id,
1511 dt.task_id,
1512 sa.resource_list_member_id,
1513 SYSDATE,
1514 x_created_by,
1515 SYSDATE,
1516 x_created_by,
1517 x_last_update_login,
1518 sa.unit_of_measure,
1519 sa.track_as_labor_flag
1520 from
1521 gms_resource_assignments sa,
1522 pa_tasks st,
1523 pa_tasks dt,
1524 gms_budget_versions dv
1525 where sa.budget_version_id = x_src_version_id
1526 and sa.project_id = st.project_id
1527 and sa.task_id = st.task_id
1528 and st.task_number = dt.task_number
1529 and dt.project_id = dv.project_id
1530 and dv.budget_version_id = x_dest_version_id;
1531
1532 else
1533
1534 insert into gms_resource_assignments
1535 (resource_assignment_id,
1536 budget_version_id,
1537 project_id,
1538 task_id,
1539 resource_list_member_id,
1540 last_update_date,
1541 last_updated_by,
1542 creation_date,
1543 created_by,
1544 last_update_login,
1545 unit_of_measure,
1546 track_as_labor_flag)
1547 select gms_resource_assignments_s.nextval,
1548 x_dest_version_id,
1549 dv.project_id,
1550 0,
1551 sa.resource_list_member_id,
1552 SYSDATE,
1553 x_created_by,
1554 SYSDATE,
1555 x_created_by,
1556 x_last_update_login,
1557 sa.unit_of_measure,
1558 sa.track_as_labor_flag
1559 from
1560 gms_resource_assignments sa,
1561 gms_budget_versions dv
1562 where sa.budget_version_id = x_src_version_id
1563 and sa.task_id = 0
1564 and dv.budget_version_id = x_dest_version_id;
1565
1566 end if;
1567
1568 x_err_stage := 'copy budget lines <' ||to_char(x_src_version_id)
1569 || '>' ;
1570
1571 for budget_line_row in
1572 (select l.resource_assignment_id, l.start_date, l.end_date,a.task_id
1573 from gms_budget_lines l,
1574 gms_resource_assignments a
1575 where a.budget_version_id = x_src_version_id
1576 and a.resource_assignment_id = l.resource_assignment_id
1577 ) loop
1578
1579 x_period_name := NULL;
1580 x_start_date := NULL;
1581 x_end_date := NULL;
1582
1583 -- Shifting Periods for Budget Lines
1584
1585 if (nvl(x_shift_days,0) <> 0) then
1586 if ( (x_time_phased_type_code = 'R')
1587 or (x_time_phased_type_code = 'N')) then
1588 -- time-phased by date range or non-time-phased
1589 x_start_date := budget_line_row.start_date + x_shift_days;
1590 x_end_date := budget_line_row.end_date + x_shift_days;
1591 else
1592 if (x_entry_level_code <> 'P') then
1593 x_err_stage := 'Getting Task Start Date <'|| to_char(x_src_version_id);
1594 select start_date
1595 into x_task_start_date
1596 from pa_tasks
1597 where task_id = budget_line_row.task_id;
1598 end if;
1599
1600 x_err_stage := 'Getting no of periods by which line budget period needs to be shifted<'||
1601 to_char(x_src_version_id);
1602 get_periods(nvl(x_task_start_date, nvl(g_project_start_date, budget_line_row.start_date) ),
1603 nvl(x_task_start_date, nvl(g_project_start_date, budget_line_row.start_date) )
1604 + x_shift_days,
1605 x_time_phased_type_code ,
1606 x_periods ,
1607 x_err_code ,
1608 x_err_stage,
1609 x_err_stack );
1610
1611 if (x_err_code <> 0) then
1612 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_GET_PERIOD_FAIL',
1613 x_err_code => x_err_code,
1614 x_err_buff => x_err_stage);
1615
1616 APP_EXCEPTION.RAISE_EXCEPTION;
1617 end if;
1618
1619 x_err_stage := 'Shifting line budget period <'|| to_char(x_src_version_id);
1620 shift_periods(budget_line_row.start_date,
1621 x_periods ,
1622 x_period_name ,
1623 x_time_phased_type_code,
1624 x_start_date ,
1625 x_end_date,
1626 x_err_code,
1627 x_err_stage,
1628 x_err_stack );
1629
1630 if (x_err_code <> 0) then
1631 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_SHIFT_PERIOD_FAIL',
1632 x_err_code => x_err_code,
1633 x_err_buff => x_err_stage);
1634
1635 APP_EXCEPTION.RAISE_EXCEPTION;
1636 end if;
1637
1638 end if;
1639
1640 end if;
1641
1642
1643 if (x_entry_level_code <> 'P') then
1644
1645 insert into gms_budget_lines
1646 (resource_assignment_id,
1647 start_date,
1648 last_update_date,
1649 last_updated_by,
1650 creation_date,
1651 created_by,
1652 last_update_login,
1653 end_date,
1654 period_name,
1655 quantity,
1656 raw_cost,
1657 burdened_cost,
1658 revenue,
1659 change_reason_code,
1660 description,
1661 attribute_category,
1662 attribute1,
1663 attribute2,
1664 attribute3,
1665 attribute4,
1666 attribute5,
1667 attribute6,
1668 attribute7,
1669 attribute8,
1670 attribute9,
1671 attribute10,
1672 attribute11,
1673 attribute12,
1674 attribute13,
1675 attribute14,
1676 attribute15,
1677 pm_product_code,
1678 pm_budget_line_reference,
1679 raw_cost_source,
1680 burdened_cost_source,
1681 quantity_source,
1682 revenue_source
1683 )
1684 select
1685 da.resource_assignment_id,
1686 decode(x_start_date, NULL, l.start_date, x_start_date),
1687 SYSDATE,
1688 x_created_by,
1689 SYSDATE,
1690 x_created_by,
1691 x_last_update_login,
1692 decode(x_end_date, NULL, l.end_date, x_end_date),
1693 decode(x_period_name, NULL, l.period_name, x_period_name),
1694 l.quantity,
1695 round(l.raw_cost * amount_change_pct, rounding_precision),
1696 round(l.burdened_cost * amount_change_pct, rounding_precision),
1697 round(l.revenue * amount_change_pct, rounding_precision),
1698 l.change_reason_code,
1699 l.description,
1700 l.attribute_category,
1701 l.attribute1,
1702 l.attribute2,
1703 l.attribute3,
1704 l.attribute4,
1705 l.attribute5,
1706 l.attribute6,
1707 l.attribute7,
1708 l.attribute8,
1709 l.attribute9,
1710 l.attribute10,
1711 l.attribute11,
1712 l.attribute12,
1713 l.attribute13,
1714 l.attribute14,
1715 l.attribute15,
1716 decode(x_pm_flag,'Y',l.pm_product_code,NULL),
1717 decode(x_pm_flag,'Y',l.pm_budget_line_reference,NULL),
1718 'B',
1719 'B',
1720 'B',
1721 'B'
1722 from gms_budget_lines l,
1723 gms_resource_assignments sa,
1724 pa_tasks st,
1725 pa_tasks dt,
1726 gms_resource_assignments da
1727 where l.resource_assignment_id =
1728 budget_line_row.resource_assignment_id
1729 and l.start_date = budget_line_row.start_date
1730 and l.resource_assignment_id = sa.resource_assignment_id
1731 and sa.budget_version_id = x_src_version_id
1732 and sa.task_id = st.task_id
1733 and sa.project_id = st.project_id
1734 and sa.resource_list_member_id = da.resource_list_member_id
1735 and st.task_number = dt.task_number
1736 and dt.task_id = da.task_id
1737 and dt.project_id = da.project_id
1738 and da.budget_version_id = x_dest_version_id;
1739
1740 else
1741
1742 insert into gms_budget_lines
1743 (resource_assignment_id,
1744 start_date,
1745 last_update_date,
1746 last_updated_by,
1747 creation_date,
1748 created_by,
1749 last_update_login,
1750 end_date,
1751 period_name,
1752 quantity,
1753 raw_cost,
1754 burdened_cost,
1755 revenue,
1756 change_reason_code,
1757 description,
1758 attribute_category,
1759 attribute1,
1760 attribute2,
1761 attribute3,
1762 attribute4,
1763 attribute5,
1764 attribute6,
1765 attribute7,
1766 attribute8,
1767 attribute9,
1768 attribute10,
1769 attribute11,
1770 attribute12,
1771 attribute13,
1772 attribute14,
1773 attribute15,
1774 pm_product_code,
1775 pm_budget_line_reference,
1776 raw_cost_source,
1777 burdened_cost_source,
1778 quantity_source,
1779 revenue_source
1780 )
1781 select
1782 da.resource_assignment_id,
1783 decode(x_start_date, NULL, l.start_date, x_start_date),
1784 SYSDATE,
1785 x_created_by,
1786 SYSDATE,
1787 x_created_by,
1788 x_last_update_login,
1789 decode(x_end_date, NULL, l.end_date, x_end_date),
1790 decode(x_period_name, NULL, l.period_name, x_period_name),
1791 l.quantity,
1792 round(l.raw_cost * amount_change_pct, rounding_precision),
1793 round(l.burdened_cost * amount_change_pct, rounding_precision),
1794 round(l.revenue * amount_change_pct, rounding_precision),
1795 l.change_reason_code,
1796 l.description,
1797 l.attribute_category,
1798 l.attribute1,
1799 l.attribute2,
1800 l.attribute3,
1801 l.attribute4,
1802 l.attribute5,
1803 l.attribute6,
1804 l.attribute7,
1805 l.attribute8,
1806 l.attribute9,
1807 l.attribute10,
1808 l.attribute11,
1809 l.attribute12,
1810 l.attribute13,
1811 l.attribute14,
1812 l.attribute15,
1813 decode(x_pm_flag,'Y',l.pm_product_code,NULL),
1814 decode(x_pm_flag,'Y',l.pm_budget_line_reference,NULL),
1815 'B',
1816 'B',
1817 'B',
1818 'B'
1819 from gms_budget_lines l,
1820 gms_resource_assignments sa,
1821 gms_resource_assignments da
1822 where l.resource_assignment_id =
1823 budget_line_row.resource_assignment_id
1824 and l.start_date = budget_line_row.start_date
1825 and l.resource_assignment_id = sa.resource_assignment_id
1826 and sa.budget_version_id = x_src_version_id
1827 and sa.task_id = 0
1828 and sa.resource_list_member_id = da.resource_list_member_id
1829 and da.task_id = 0
1830 and da.budget_version_id = x_dest_version_id;
1831
1832 end if;
1833
1834 end loop;
1835
1836 exception
1837 when others then
1838 x_err_code := SQLCODE;
1839 return;
1840 end copy_lines;
1841 -------------------------------------------------------------------------------------
1842 -- Added for bug 1831151
1843 -- This function checks for budget entry levels among Draft budget and baselined budget
1844 -- the function will return false in following conditions
1845 -- 1. If budget Entry Method or Resource lists are different in baselined budget
1846 -- and draft budget
1847 -- 2. If the Draft Budget is at Top Task or Resource Group level and the copy actual
1848 -- start period and end period are not covering all the budgeted periods of draft Budget.
1849
1850 FUNCTION p_validate (
1851 x_project_id NUMBER,
1852 draft_bvid NUMBER,
1853 bal_bvid NUMBER,
1854 p_start_period_date DATE,
1855 p_end_period_date DATE
1856 )
1857 RETURN BOOLEAN IS
1858
1859
1860 CURSOR c_draft_details IS
1861 SELECT task_id,resource_list_member_id
1862 FROM gms_resource_assignments gra, gms_budget_lines gbl
1863 WHERE gra.resource_assignment_id = gbl.resource_assignment_id
1864 AND gra.budget_version_id = draft_bvid;
1865
1866
1867 CURSOR c_budget_details (p_budget_version NUMBER) IS
1868 SELECT gbv.budget_entry_method_code, resource_list_id,
1869 entry_level_code
1870 FROM gms_budget_versions gbv, pa_budget_entry_methods pbem
1871 WHERE gbv.budget_version_id = p_budget_version
1872 AND gbv.budget_entry_method_code = pbem.budget_entry_method_code;
1873
1874 CURSOR c_period_dates_draft IS
1875 SELECT MIN (start_date), MAX (end_date)
1876 FROM gms_resource_assignments gra, gms_budget_lines gbl
1877 WHERE gra.resource_assignment_id = gbl.resource_assignment_id
1878 AND gra.budget_version_id = draft_bvid;
1879
1880 CURSOR c_period_dates_baselined IS
1881 SELECT MIN (start_date), MAX (end_date)
1882 FROM gms_balances
1883 WHERE budget_version_id = bal_bvid;
1884
1885
1886 x_task_id NUMBER;
1887 x_rlmid NUMBER;
1888 x_c_resource_list_id NUMBER;
1889 x_d_resource_list_id NUMBER;
1890 dummy NUMBER := 0;
1891 x_c_budget_entry_method VARCHAR2 (30);
1892 x_c_entry_level_code VARCHAR2 (10);
1893 x_d_budget_entry_method VARCHAR2 (30);
1894 budget_method_changed BOOLEAN := FALSE;
1895 x_draft_start_date DATE;
1896 x_draft_end_date DATE;
1897 x_baselined_start_date DATE;
1898 x_baselined_end_date DATE;
1899 result_code VARCHAR(2);
1900 BEGIN
1901 OPEN c_budget_details (bal_bvid);
1902 FETCH c_budget_details INTO x_c_budget_entry_method,
1903 x_c_resource_list_id,
1904 x_c_entry_level_code;
1905 CLOSE c_budget_details;
1906
1907
1908 OPEN c_budget_details (draft_bvid);
1909 FETCH c_budget_details INTO x_d_budget_entry_method,
1910 x_d_resource_list_id,
1911 x_c_entry_level_code;
1912 CLOSE c_budget_details;
1913
1914 IF ( x_c_budget_entry_method <> x_d_budget_entry_method
1915 OR x_c_resource_list_id <> x_d_resource_list_id
1916 ) THEN
1917 budget_method_changed := TRUE;
1918 fnd_message.set_name ('GMS', 'GMS_BUDG_ENTRY_CHANGED');
1919 RETURN FALSE;
1920 END IF;
1921 IF NOT budget_method_changed THEN
1922
1923 OPEN c_period_dates_draft;
1924 FETCH c_period_dates_draft INTO x_draft_start_date, x_draft_end_date;
1925 CLOSE c_period_dates_draft;
1926
1927 IF (x_draft_start_date NOT BETWEEN p_start_period_date
1928 AND p_end_period_date
1929 )
1930 OR (x_draft_end_date NOT BETWEEN p_start_period_date
1931 AND p_end_period_date
1932 ) THEN
1933
1934 result_code := 'P';
1935 OPEN c_draft_details;
1936 LOOP
1937 FETCH c_draft_details INTO x_task_id,x_rlmid ;
1938 EXIT WHEN c_draft_details%NOTFOUND
1939 OR result_code in ('FT','FR');
1940 dummy := 0;
1941 -- Bug 4908109 : Performance Fix
1942 begin
1943 SELECT 1
1944 INTO dummy
1945 FROM DUAL
1946 WHERE EXISTS (
1947 SELECT 1
1948 FROM pa_tasks
1949 WHERE project_id = x_project_id
1950 AND task_id = x_task_id
1951 AND task_id = top_task_id
1952 AND EXISTS (select 1
1953 FROM pa_tasks
1954 where nvl(parent_task_id,0) = x_task_id
1955 and project_id = x_project_id));
1956 exception
1957 when NO_DATA_FOUND then
1958 dummy := 0;
1959 end;
1960 IF dummy <> 0 THEN
1961 result_code := 'FT';
1962 ELSE
1963 -- Bug 4908109 : Performance Fix
1964 begin
1965 SELECT 1
1966 INTO dummy
1967 FROM DUAL
1968 WHERE EXISTS (
1969 SELECT 1
1970 FROM pa_resource_list_members prl
1971 WHERE prl.resource_list_member_id = x_rlmid
1972 AND prl.parent_member_id IS NULL );
1973 exception
1974 when NO_DATA_FOUND then
1975 dummy := 0;
1976 end;
1977 IF dummy <> 0 THEN
1978 result_code := 'FR';
1979 END IF;
1980
1981 END IF;
1982 END LOOP;
1983 CLOSE c_draft_details;
1984
1985 IF result_code = 'FT' THEN
1986 fnd_message.set_name ('GMS', 'GMS_DRAFT_BUD_AT_TOP_TASK');
1987 RETURN FALSE;
1988 ELSIF result_code = 'FR' THEN
1989 fnd_message.set_name ('GMS', 'GMS_DRAFT_BUD_AT_RES_GRP');
1990 RETURN FALSE;
1991 END IF;
1992
1993
1994
1995 END IF; -- x_draft_start_date
1996 END IF; -- NOT budget_method_changed
1997 RETURN TRUE;
1998 END;
1999 -----------------------------------------------------------------------------------
2000
2001 -- Added for bug 1831151
2002 -- This Procedure Returns Min Start Period and Max end Period to the Award Budget form ,
2003 -- these periods will be used to populate default values of for copy actual periods
2004 -- when copy actual button is pressed.
2005
2006
2007 PROCEDURE start_end_period (
2008 x_project_id IN NUMBER,
2009 x_award_id IN NUMBER,
2010 x_version_id IN NUMBER,
2011 x_current_budget_version_id IN NUMBER,
2012 x_budget_entry_method_code IN VARCHAR2,
2013 x_time_phase_type_code IN VARCHAR2,
2014 x_resource_list_id IN NUMBER,
2015 x_resource_list_name IN VARCHAR2,
2016 x_start_period_name OUT NOCOPY VARCHAR2,
2017 x_start_date IN OUT NOCOPY DATE,
2018 x_end_period_name OUT NOCOPY VARCHAR2,
2019 x_end_date IN OUT NOCOPY DATE,
2020 x_err_code IN OUT NOCOPY NUMBER,
2021 x_err_stage IN OUT NOCOPY VARCHAR2,
2022 x_err_stack IN OUT NOCOPY VARCHAR2
2023 ) IS
2024
2025 x_gbal_start_date DATE;
2026 x_pdb_start_date DATE;
2027 x_gbal_end_date DATE;
2028 x_pdb_end_date DATE;
2029
2030 /********* commented for bug 4007119
2031 CURSOR c_period_name (p_period_date DATE, p_time_phase_type_code VARCHAR2) IS
2032 SELECT period_name
2033 FROM pa_budget_periods_v
2034 WHERE period_type_code = p_time_phase_type_code
2035 AND p_period_date BETWEEN period_start_date AND period_end_date;
2036 ********/
2037
2038 BEGIN
2039 --Added the following select statements to fetch period name based
2040 --on the basis of time_phased_type_code
2041 BEGIN
2042
2043 SELECT MIN (start_date), MAX (end_date)
2044 INTO x_gbal_start_date, x_gbal_end_date
2045 FROM gms_balances gb, gms_budget_versions gbv
2046 WHERE gb.budget_version_id = gbv.budget_version_id
2047 AND gb.project_id = x_project_id
2048 AND gb.award_id = x_award_id
2049 AND gbv.award_id = gb.award_id
2050 AND gbv.project_id = gb.project_id
2051 AND gbv.current_flag = 'Y'
2052 AND gb.actual_period_to_date IS NOT NULL
2053 AND gb.encumb_period_to_date IS NOT NULL
2054 GROUP BY gb.budget_version_id;
2055
2056 EXCEPTION
2057 WHEN NO_DATA_FOUND THEN
2058 fnd_message.set_name ('GMS', 'GMS_BU_ACCUMS_NOT_EXIST');
2059 fnd_message.set_token('RES_LIST',x_resource_list_name);
2060 app_exception.raise_exception;
2061 END;
2062
2063 BEGIN
2064
2065 SELECT MIN (start_date), MAX (end_date)
2066 INTO x_pdb_start_date, x_pdb_end_date
2067 FROM gms_resource_assignments gra,
2068 gms_budget_versions gbv,
2069 gms_budget_lines gbl
2070 WHERE gbv.budget_version_id = gra.budget_version_id
2071 AND gbv.budget_version_id = x_version_id
2072 AND gra.resource_assignment_id = gbl.resource_assignment_id
2073 GROUP BY gbv.budget_version_id;
2074 EXCEPTION
2075 WHEN NO_DATA_FOUND THEN
2076 NULL;
2077 END;
2078
2079 x_start_date := LEAST (x_gbal_start_date, nvl(x_pdb_start_date,x_gbal_start_date));
2080 x_end_date := GREATEST (x_gbal_end_date, nvl(x_pdb_end_date,x_gbal_end_date));
2081
2082 /******* commented for bug 4007119
2083 IF x_time_phase_type_code = 'P' THEN
2084 OPEN c_period_name (x_start_date, 'P');
2085 ELSIF x_time_phase_type_code = 'G' THEN
2086 OPEN c_period_name (x_start_date, 'G');
2087 END IF;
2088
2089 IF c_period_name%ISOPEN THEN
2090 FETCH c_period_name INTO x_start_period_name;
2091 CLOSE c_period_name;
2092 END IF;
2093
2094 IF x_time_phase_type_code = 'P' THEN
2095 OPEN c_period_name (x_end_date, 'P');
2096 ELSIF x_time_phase_type_code = 'G' THEN
2097 OPEN c_period_name (x_end_date, 'G');
2098 END IF;
2099
2100 IF c_period_name%ISOPEN THEN
2101 FETCH c_period_name INTO x_end_period_name;
2102 CLOSE c_period_name;
2103 END IF;
2104 ***************/
2105
2106 -- Bug 4007119..changed code to use base tables instead of pa_budget_periods_v view.
2107
2108 IF x_time_phase_type_code = 'P' THEN
2109
2110 select period_name
2111 into x_start_period_name
2112 from pa_periods
2113 where x_start_date between start_date and end_date;
2114
2115 select period_name
2116 into x_end_period_name
2117 from pa_periods
2118 where x_end_date between start_date and end_date;
2119
2120 ELSIF x_time_phase_type_code = 'G' THEN
2121
2122 select p.period_name
2123 into x_start_period_name
2124 from gl_period_statuses p, pa_implementations i
2125 where i.set_of_books_id = p.set_of_books_id
2126 and p.application_id = pa_period_process_pkg.application_id
2127 and p.adjustment_period_flag = 'N'
2128 and x_start_date between p.start_date and p.end_date;
2129
2130 select p.period_name
2131 into x_end_period_name
2132 from gl_period_statuses p, pa_implementations i
2133 where i.set_of_books_id = p.set_of_books_id
2134 and p.application_id = pa_period_process_pkg.application_id
2135 and p.adjustment_period_flag = 'N'
2136 and x_end_date between p.start_date and p.end_date;
2137
2138 END IF;
2139
2140 END;
2141 ------------------------------------------------------------------------------------
2142 --Modified the code to make the copy actual pick data from Gms_balances
2143
2144
2145 procedure copy_actual (x_project_id in number,
2146 x_award_id in number,
2147 x_version_id in number,
2148 x_budget_entry_method_code in varchar2,
2149 x_resource_list_id in number,
2150 x_start_period in varchar2,
2151 x_end_period in varchar2,
2152 x_err_code in out NOCOPY number,
2153 x_err_stage in out NOCOPY varchar2,
2154 x_err_stack in out NOCOPY varchar2,
2155 x_funding_status out NOCOPY number) -- Added for bug 1831151
2156 is
2157 -- Standard who
2158 x_created_by number(15);
2159 x_last_update_login number(15);
2160
2161 x_entry_level_code varchar2(30);
2162 x_categorization_code varchar2(30);
2163 x_time_phased_type_code varchar2(30);
2164 x_start_period_start_date date;
2165 x_end_period_end_date date;
2166 x_task_id number;
2167 x_uncat_res_list_member_id number;
2168 x_uncat_unit_of_measure varchar2(30);
2169 x_uncat_track_as_labor_flag varchar2(2);
2170 x_raw_cost number;
2171 x_burdened_cost number;
2172 x_revenue number;
2173 x_quantity number;
2174 x_labor_hours number;
2175 x_unit_of_measure varchar2(30);
2176 x_resource_assignment_id number;
2177 x_raw_cost_total number;
2178 x_burdened_cost_total number;
2179 x_revenue_total number;
2180 x_quantity_total number;
2181 x_labor_hours_total number;
2182 x_dummy1 number;
2183 x_dummy2 number;
2184 x_dummy3 number;
2185 x_dummy4 number;
2186 x_dummy5 number;
2187 x_dummy6 number;
2188 x_rowid rowid;
2189 old_stack varchar2(630);
2190 x_budget_amount_code varchar2(1);
2191
2192 x_current_budget_version_id NUMBER;
2193
2194 -- record definition
2195 type period_type is
2196 record (period_name varchar2(30),
2197 start_date date,
2198 end_date date);
2199
2200 period_rec period_type;
2201
2202 -- cursor definition
2203
2204 cursor pa_cursor is
2205 select period_name,
2206 start_date,
2207 end_date
2208 from pa_periods
2209 where start_date between x_start_period_start_date
2210 and x_end_period_end_date;
2211
2212 cursor gl_cursor is
2213 select p.period_name,
2214 p.start_date,
2215 p.end_date
2216 from gl_period_statuses p,
2217 pa_implementations i
2218 where p.application_id = 101
2219 and p.set_of_books_id = i.set_of_books_id
2220 and p.start_date between x_start_period_start_date
2221 and x_end_period_end_date
2222 and p.adjustment_period_flag = 'N'; -- 7653209 ;
2223
2224 -- Added for bug 1831151
2225
2226 CURSOR get_current_budget_version_id IS
2227 SELECT budget_version_id
2228 FROM gms_budget_versions
2229 WHERE award_id = x_award_id
2230 AND project_id = x_project_id
2231 AND current_flag = 'Y';
2232
2233 -- end of modifications for bug 1831151
2234
2235 cursor get_budget_amount_code is
2236 select budget_amount_code
2237 from gms_budget_versions b, pa_budget_types t
2238 where b.budget_version_id = x_version_id
2239 and b.budget_type_code = t.budget_type_code;
2240
2241 -- required to fetch the budget line details reqd. to delete a budget line.
2242
2243 cursor budget_lines_csr (p_budget_version_id NUMBER
2244 ,p_start_date DATE
2245 ,p_end_date DATE) is
2246 select gra.task_id,
2247 gra.resource_list_member_id,
2248 gbl.period_name,
2249 gbl.start_date
2250 from gms_resource_assignments gra,
2251 gms_budget_lines gbl
2252 where gbl.resource_assignment_id = gra.resource_assignment_id
2253 and gra.budget_version_id = p_budget_version_id
2254 and gbl.start_date between p_start_date and p_end_date;
2255
2256 begin
2257
2258 open get_budget_amount_code;
2259 fetch get_budget_amount_code into x_budget_amount_code;
2260 close get_budget_amount_code;
2261
2262 -- For bug 1831151
2263
2264 open get_current_budget_version_id;
2265 fetch get_current_budget_version_id into x_current_budget_version_id;
2266 close get_current_budget_version_id;
2267
2268 -- end of the code added for bug 1831151
2269
2270
2271 x_err_code := 0;
2272 x_funding_status := -1; -- bug 1831151
2273 old_stack := x_err_stack;
2274 x_err_stack := x_err_stack || '->copy_actual';
2275
2276 x_created_by := FND_GLOBAL.USER_ID;
2277 x_last_update_login := FND_GLOBAL.LOGIN_ID;
2278
2279 savepoint before_copy_actual;
2280
2281 x_err_stage := 'get budget entry method <' || x_budget_entry_method_code
2282 || '>';
2283
2284 select entry_level_code, categorization_code,
2285 time_phased_type_code
2286 into x_entry_level_code, x_categorization_code,
2287 x_time_phased_type_code
2288 from pa_budget_entry_methods
2289 where budget_entry_method_code = x_budget_entry_method_code;
2290
2291 if ( (x_time_phased_type_code = 'N')
2292 or (x_time_phased_type_code = 'R')) then
2293 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_INVALID_TIME_PHASED', -- cannot copy-actual for a non-time phased/date range budgets
2294 x_err_code => x_err_code,
2295 x_err_buff => x_err_stage);
2296
2297 APP_EXCEPTION.RAISE_EXCEPTION;
2298 end if;
2299
2300 x_err_stage := 'get uncategorized resource list member id';
2301
2302 select m.resource_list_member_id,
2303 m.track_as_labor_flag,
2304 r.unit_of_measure
2305 into x_uncat_res_list_member_id,
2306 x_uncat_track_as_labor_flag,
2307 x_uncat_unit_of_measure
2308 from pa_resources r,
2309 pa_resource_list_members m,
2310 --gms_implementations gia, -- Commented out NOCOPY for Bug:2113499
2311 pa_implementations pi,
2312 pa_resource_lists l
2313 where l.uncategorized_flag = 'Y'
2314 and l.resource_list_id = m.resource_list_id
2315 --and gia.org_id = l.business_group_id -- Commented out NOCOPY for Bug:2113499
2316 and pi.business_group_id = l.business_group_id
2317 and m.resource_id = r.resource_id
2318 AND NVL(m.migration_code,'M') ='M' -- Bug 3626671
2319 AND NVL(l.migration_code,'M') ='M'; -- Bug 3626671
2320
2321
2322 x_err_stage := 'get start date of periods <' || x_start_period
2323 || '><' || x_end_period
2324 || '>';
2325 if (x_time_phased_type_code = 'P') then
2326
2327 select start_date
2328 into x_start_period_start_date
2329 from pa_periods
2330 where period_name = x_start_period;
2331
2332 select end_date
2333 into x_end_period_end_date
2334 from pa_periods
2335 where period_name = x_end_period;
2336
2337 else
2338 select start_date
2339 into x_start_period_start_date
2340 from gl_period_statuses p,
2341 pa_implementations i
2342 where p.period_name = x_start_period
2343 and p.application_id = 101
2344 and p.set_of_books_id = i.set_of_books_id;
2345
2346 select end_date
2347 into x_end_period_end_date
2348 from gl_period_statuses p,
2349 pa_implementations i
2350 where p.period_name = x_end_period
2351 and p.application_id = 101
2352 and p.set_of_books_id = i.set_of_books_id;
2353
2354 end if;
2355 -- Added for bug 1831151
2356 IF NOT p_validate (
2357 x_project_id,
2358 x_version_id,
2359 x_current_budget_version_id,
2360 x_start_period_start_date,
2361 x_end_period_end_date
2362 ) THEN
2363 app_exception.raise_exception;
2364 -- end of code added for bug 1831151
2365
2366 END IF;
2367 x_err_stage := 'delete budget lines <' || to_char(x_version_id)
2368 || '><' || x_start_period
2369 || '><' || x_end_period
2370 || '>';
2371
2372 /* for bl_rec in (
2373 select rowid
2374 from gms_budget_lines l
2375 where l.resource_assignment_id in
2376 (select a.resource_assignment_id
2377 from gms_resource_assignments a
2378 where a.budget_version_id = x_version_id)
2379 and l.start_date between x_start_period_start_date and
2380 x_end_period_end_date) loop
2381 */
2382
2383 for budget_lines_rec in budget_lines_csr(x_version_id
2384 ,x_start_period_start_date
2385 ,x_end_period_end_date)
2386 loop
2387 gms_budget_pub.delete_budget_line(
2388 p_api_version_number => 1.0,
2389 x_err_code => x_err_code,
2390 x_err_stage => x_err_stage,
2391 x_err_stack=> x_err_stack,
2392 p_pm_product_code => 'GMS',
2393 p_project_id => x_project_id,
2394 p_award_id => x_award_id,
2395 p_budget_type_code => 'AC', -- changed from C to AC
2396 p_task_id => budget_lines_rec.task_id,
2397 p_resource_list_member_id => budget_lines_rec.resource_list_member_id,
2398 p_start_date => budget_lines_rec.start_date,
2399 p_period_name => budget_lines_rec.period_name
2400 );
2401
2402 IF x_err_code <> 0
2403 THEN
2404 gms_error_pkg.gms_message( x_err_name => 'GMS_DELETE_BUDGET_LINE_FAIL',
2405 x_err_code => x_err_code,
2406 x_err_buff => x_err_stage);
2407
2408 APP_EXCEPTION.RAISE_EXCEPTION;
2409 END IF;
2410
2411 end loop;
2412 -- process every period between the starting period and ending period
2413
2414 if (x_time_phased_type_code = 'P') then
2415 open pa_cursor;
2416 else
2417 open gl_cursor;
2418 end if;
2419
2420 loop -- period
2421
2422 if (x_time_phased_type_code = 'P') then
2423 fetch pa_cursor into period_rec ;
2424 exit when pa_cursor%NOTFOUND;
2425 else
2426 fetch gl_cursor into period_rec;
2427 exit when gl_cursor%NOTFOUND;
2428 end if;
2429
2430 x_err_stage := 'process period <' || period_rec.period_name
2431 || '><' || x_time_phased_type_code
2432 || '>';
2433
2434 if (x_entry_level_code = 'P') then
2435 if (x_categorization_code = 'N') then
2436 -- project level, uncategorized
2437
2438 x_burdened_cost := 0;
2439 x_unit_of_measure := NULL;
2440
2441 get_proj_accum_actuals(x_project_id,
2442 NULL,
2443 x_current_budget_version_id,
2444 NULL,
2445 x_time_phased_type_code,
2446 period_rec.period_name,
2447 period_rec.start_date,
2448 period_rec.end_date,
2449 x_burdened_cost,
2450 x_dummy1,
2451 x_unit_of_measure,
2452 x_err_stage,
2453 x_err_code
2454 );
2455
2456 if (x_err_code <> 0) then
2457 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_GET_ACCUM_ACTUALS_FAIL',
2458 x_err_code => x_err_code,
2459 x_err_buff => x_err_stage);
2460
2461 APP_EXCEPTION.RAISE_EXCEPTION;
2462 end if;
2463
2464
2465 if (nvl(x_burdened_cost,0) <> 0) then
2466
2467 gms_budget_pub.add_budget_line(
2468 p_api_version_number => 1.0
2469 ,x_err_code => x_err_code
2470 ,x_err_stage => x_err_stage
2471 ,x_err_stack => x_err_stack
2472 ,p_pm_product_code => 'GMS'
2473 ,p_project_id => x_project_id
2474 ,p_award_id => x_award_id
2475 ,p_budget_type_code => 'AC' -- Approved Cost Budget
2476 ,p_task_id => 0
2477 ,p_resource_list_member_id => x_uncat_res_list_member_id
2478 ,p_budget_start_date => period_rec.start_date
2479 ,p_budget_end_date => period_rec.end_date
2480 ,p_period_name => period_rec.period_name
2481 ,p_description => NULL
2482 ,p_raw_cost => x_raw_cost
2483 ,p_burdened_cost => x_burdened_cost
2484 ,p_quantity => x_quantity
2485 ,p_pm_budget_line_reference => NULL ); -- jjj - identifies external system
2486
2487
2488 if (x_err_code <> 0) then
2489 gms_error_pkg.gms_message( x_err_name => 'GMS_ADD_BUDGET_LINE_FAIL',
2490 x_err_code => x_err_code,
2491 x_err_buff => x_err_stage);
2492 x_funding_status := 0;
2493 APP_EXCEPTION.RAISE_EXCEPTION;
2494 end if;
2495 end if;
2496
2497 else
2498 -- project level, categorized
2499 for res_rec in (select m.resource_list_member_id,
2500 m.resource_id,
2501 m.track_as_labor_flag
2502 from pa_resource_list_members m
2503 where m.resource_list_id = x_resource_list_id
2504 and not exists
2505 (select 1
2506 from pa_resource_list_members m1
2507 where m1.parent_member_id =
2508 m.resource_list_member_id
2509 AND NVL(m1.migration_code,'M') ='M') -- Bug 3626671
2510 and exists (select 1 -- Bug 1831151
2511 from gms_balances gb
2512 where budget_version_id = x_current_budget_version_id
2513 and gb.resource_list_member_id=m.resource_list_member_id)
2514 AND NVL(m.migration_code,'M') ='M') -- Bug 3626671
2515 loop
2516 x_err_stage := 'process period and resource <'
2517 || period_rec.period_name
2518 || '><' || to_char(res_rec.resource_list_member_id)
2519 || '>';
2520
2521 x_burdened_cost := 0;
2522 x_unit_of_measure := NULL;
2523
2524 -- Diverted the call to local procedure for Bug 1831151 instead of PA
2525
2526 get_proj_accum_actuals(x_project_id,
2527 NULL,
2528 x_current_budget_version_id,
2529 res_rec.resource_list_member_id,
2530 x_time_phased_type_code,
2531 period_rec.period_name,
2532 period_rec.start_date,
2533 period_rec.end_date,
2534 x_burdened_cost,
2535 x_dummy1,
2536 x_unit_of_measure,
2537 x_err_stage,
2538 x_err_code
2539 );
2540
2541 if (x_err_code <> 0) then
2542 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_GET_ACCUM_ACTUALS_FAIL',
2543 x_err_code => x_err_code,
2544 x_err_buff => x_err_stage);
2545
2546 APP_EXCEPTION.RAISE_EXCEPTION;
2547 end if;
2548
2549 if( nvl(x_burdened_cost,0) <> 0) then
2550
2551 gms_budget_pub.add_budget_line(
2552 p_api_version_number => 1.0
2553 ,x_err_code => x_err_code
2554 ,x_err_stage => x_err_stage
2555 ,x_err_stack => x_err_stack
2556 ,p_pm_product_code => 'GMS'
2557 ,p_project_id => x_project_id
2558 ,p_award_id => x_award_id
2559 ,p_budget_type_code => 'AC'
2560 ,p_task_id => 0
2561 ,p_resource_list_member_id => res_rec.resource_list_member_id
2562 ,p_budget_start_date => period_rec.start_date
2563 ,p_budget_end_date => period_rec.end_date
2564 ,p_period_name => period_rec.period_name
2565 ,p_description => NULL
2566 ,p_raw_cost => x_raw_cost
2567 ,p_burdened_cost => x_burdened_cost
2568 ,p_quantity => x_quantity
2569 ,p_pm_budget_line_reference => NULL ); -- jjj - identifies external system
2570
2571
2572 if (x_err_code <> 0) then
2573 gms_error_pkg.gms_message( x_err_name => 'GMS_ADD_BUDGET_LINE_FAIL',
2574 x_err_code => x_err_code,
2575 x_err_buff => x_err_stage);
2576 x_funding_status := 0;
2577 APP_EXCEPTION.RAISE_EXCEPTION;
2578 end if;
2579
2580 end if;
2581
2582 end loop; -- resource
2583
2584 end if;
2585
2586 elsif (x_entry_level_code = 'T') then
2587
2588 -- go through every top level task
2589 for top_task_rec in (select t.task_id
2590 from pa_tasks t
2591 where t.project_id = x_project_id
2592 and t.task_id = t.top_task_id
2593 and EXISTS ( SELECT 1 -- added for bug 1831151
2594 FROM gms_balances gb
2595 WHERE gb.budget_version_id =
2596 x_current_budget_version_id
2597 AND gb.top_task_id = t.task_id )) loop
2598 x_burdened_cost:= 0;
2599
2600 if (x_categorization_code = 'N') then
2601 x_burdened_cost := 0;
2602 x_unit_of_measure := NULL;
2603
2604 -- Diverted the call to local procedure for Bug 1831151 instead of PA
2605 get_proj_accum_actuals(x_project_id,
2606 top_task_rec.task_id,
2607 x_current_budget_version_id,
2608 NULL,
2609 x_time_phased_type_code,
2610 period_rec.period_name,
2611 period_rec.start_date,
2612 period_rec.end_date,
2613 x_burdened_cost,
2614 x_dummy1,
2615 x_unit_of_measure,
2616 x_err_stage,
2617 x_err_code
2618 );
2619
2620 if (x_err_code <> 0) then
2621 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_GET_ACCUM_ACTUALS_FAIL',
2622 x_err_code => x_err_code,
2623 x_err_buff => x_err_stage);
2624
2625 APP_EXCEPTION.RAISE_EXCEPTION;
2626 end if;
2627 -- commented for bug 1831151
2628 /* if x_budget_amount_code = 'C' then
2629 x_revenue := null;
2630 else
2631 x_raw_cost := null;
2632 x_burdened_cost := null;
2633 end if;
2634
2635 if ( (nvl(x_quantity,0) <> 0)
2636 or (nvl(x_raw_cost,0) <> 0)
2637 or (nvl(x_revenue,0) <> 0)) then */
2638
2639 if (nvl(x_burdened_cost,0) <> 0) then
2640
2641 gms_budget_pub.add_budget_line(
2642 p_api_version_number => 1.0
2643 ,x_err_code => x_err_code
2644 ,x_err_stage => x_err_stage
2645 ,x_err_stack => x_err_stack
2646 ,p_pm_product_code => 'GMS'
2647 ,p_project_id => x_project_id
2648 ,p_award_id => x_award_id
2649 ,p_budget_type_code => 'AC'
2650 ,p_task_id => top_task_rec.task_id
2651 ,p_resource_list_member_id => x_uncat_res_list_member_id
2652 ,p_budget_start_date => period_rec.start_date
2653 ,p_budget_end_date => period_rec.end_date
2654 ,p_period_name => period_rec.period_name
2655 ,p_description => NULL
2656 ,p_raw_cost => x_raw_cost
2657 ,p_burdened_cost => x_burdened_cost
2658 ,p_quantity => x_quantity
2659 ,p_pm_budget_line_reference => NULL ); -- jjj - identifies external system
2660
2661
2662 if (x_err_code <> 0) then
2663 gms_error_pkg.gms_message( x_err_name => 'GMS_ADD_BUDGET_LINE_FAIL',
2664 x_err_code => x_err_code,
2665 x_err_buff => x_err_stage);
2666 x_funding_status := 0;
2667 APP_EXCEPTION.RAISE_EXCEPTION;
2668 end if;
2669 end if;
2670
2671 else
2672 -- top level task, categorized
2673 for res_rec in (select m.resource_list_member_id,
2674 m.resource_id,
2675 m.track_as_labor_flag
2676 from pa_resource_list_members m
2677 where m.resource_list_id =
2678 x_resource_list_id
2679 and not exists
2680 (select 1
2681 from pa_resource_list_members m1
2682 where m1.parent_member_id =
2683 m.resource_list_member_id
2684 AND NVL(m1.migration_code,'M') ='M') -- Bug 3626671
2685 AND EXISTS ( SELECT 1-- added for bug 1831151
2686 FROM gms_balances gb
2687 WHERE budget_version_id =
2688 x_current_budget_version_id
2689 AND gb.resource_list_member_id =
2690 m.resource_list_member_id)
2691 AND NVL(m.migration_code,'M') ='M') loop -- Bug 3626671
2692 x_burdened_cost:= 0;
2693 x_unit_of_measure := NULL;
2694
2695 x_err_stage := 'process period/task/resource <'
2696 || period_rec.period_name
2697 || '><' || to_char(top_task_rec.task_id)
2698 || '><'
2699 || to_char(res_rec.resource_list_member_id)
2700 || '>';
2701 -- Diverted the call to local procedure for Bug 1831151 instead of PA
2702 get_proj_accum_actuals(x_project_id,
2703 top_task_rec.task_id,
2704 x_current_budget_version_id,
2705 res_rec.resource_list_member_id,
2706 x_time_phased_type_code,
2707 period_rec.period_name,
2708 period_rec.start_date,
2709 period_rec.end_date,
2710 x_burdened_cost,
2711 x_dummy1,
2712 x_unit_of_measure,
2713 x_err_stage,
2714 x_err_code
2715 );
2716
2717 if (x_err_code <> 0) then
2718 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_GET_ACCUM_ACTUALS_FAIL',
2719 x_err_code => x_err_code,
2720 x_err_buff => x_err_stage);
2721
2722 APP_EXCEPTION.RAISE_EXCEPTION;
2723 end if;
2724 if (nvl(x_burdened_cost,0) <> 0) then
2725 gms_budget_pub.add_budget_line(
2726 p_api_version_number => 1.0
2727 ,x_err_code => x_err_code
2728 ,x_err_stage => x_err_stage
2729 ,x_err_stack => x_err_stack
2730 ,p_pm_product_code => 'GMS'
2731 ,p_project_id => x_project_id
2732 ,p_award_id => x_award_id
2733 ,p_budget_type_code => 'AC'
2734 ,p_task_id => top_task_rec.task_id
2735 ,p_resource_list_member_id => res_rec.resource_list_member_id
2736 ,p_budget_start_date => period_rec.start_date
2737 ,p_budget_end_date => period_rec.end_date
2738 ,p_period_name => period_rec.period_name
2739 ,p_description => NULL
2740 ,p_raw_cost => x_raw_cost
2741 ,p_burdened_cost => x_burdened_cost
2742 ,p_quantity => x_quantity
2743 ,p_pm_budget_line_reference => NULL ); -- jjj - identifies external system
2744
2745
2746 if (x_err_code <> 0) then
2747 gms_error_pkg.gms_message( x_err_name => 'GMS_ADD_BUDGET_LINE_FAIL',
2748 x_err_code => x_err_code,
2749 x_err_buff => x_err_stage);
2750 x_funding_status := 0;
2751 APP_EXCEPTION.RAISE_EXCEPTION;
2752 end if;
2753
2754 end if;
2755
2756 end loop; -- resource
2757
2758 end if; -- categorized
2759
2760 end loop; -- top task
2761
2762 else -- 'L' or 'M'
2763 -- go through every lowest level task
2764 for task_rec in (select t.task_id
2765 from pa_tasks t
2766 where t.project_id = x_project_id
2767 and not exists
2768 (select 1
2769 from pa_tasks t1
2770 where t1.parent_task_id = t.task_id)
2771 AND EXISTS ( SELECT 1 --bug 1831151
2772 FROM gms_balances
2773 WHERE budget_version_id =
2774 x_current_budget_version_id
2775 AND task_id = t.task_id)
2776
2777 ) loop
2778
2779 if (x_categorization_code = 'N') then
2780 x_burdened_cost := 0;
2781 x_unit_of_measure := NULL;
2782 -- Diverted the call to local procedure for Bug 1831151 instead of PA
2783 get_proj_accum_actuals(x_project_id,
2784 task_rec.task_id,
2785 x_current_budget_version_id,
2786 NULL,
2787 x_time_phased_type_code,
2788 period_rec.period_name,
2789 period_rec.start_date,
2790 period_rec.end_date,
2791 x_burdened_cost,
2792 x_dummy1,
2793 x_unit_of_measure,
2794 x_err_stage,
2795 x_err_code
2796 );
2797
2798 if (x_err_code <> 0) then
2799 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_GET_ACCUM_ACTUALS_FAIL',
2800 x_err_code => x_err_code,
2801 x_err_buff => x_err_stage);
2802
2803 APP_EXCEPTION.RAISE_EXCEPTION;
2804 end if;
2805 if (nvl(x_burdened_cost,0) <> 0) then
2806 gms_budget_pub.add_budget_line(
2807 p_api_version_number => 1.0
2808 ,x_err_code => x_err_code
2809 ,x_err_stage => x_err_stage
2810 ,x_err_stack => x_err_stack
2811 ,p_pm_product_code => 'GMS'
2812 ,p_project_id => x_project_id
2813 ,p_award_id => x_award_id
2814 ,p_budget_type_code => 'AC'
2815 ,p_task_id => task_rec.task_id
2816 ,p_resource_list_member_id => x_uncat_res_list_member_id
2817 ,p_budget_start_date => period_rec.start_date
2818 ,p_budget_end_date => period_rec.end_date
2819 ,p_period_name => period_rec.period_name
2820 ,p_description => NULL
2821 ,p_raw_cost => x_raw_cost
2822 ,p_burdened_cost => x_burdened_cost
2823 ,p_quantity => x_quantity
2824 ,p_pm_budget_line_reference => NULL ); -- jjj - identifies external system
2825
2826 if (x_err_code <> 0) then
2827 gms_error_pkg.gms_message( x_err_name => 'GMS_ADD_BUDGET_LINE_FAIL',
2828 x_err_code => x_err_code,
2829 x_err_buff => x_err_stage);
2830 x_funding_status := 0;
2831 APP_EXCEPTION.RAISE_EXCEPTION;
2832 end if;
2833
2834 end if;
2835
2836 else
2837 -- lowest level task, categorized
2838 for res_rec in (select m.resource_list_member_id,
2839 m.resource_id,
2840 m.track_as_labor_flag
2841 from pa_resource_list_members m
2842 where m.resource_list_id =
2843 x_resource_list_id
2844 and not exists
2845 (select 1
2846 from pa_resource_list_members m1
2847 where m1.parent_member_id =
2848 m.resource_list_member_id
2849 AND NVL(m1.migration_code,'M') ='M') -- Bug 3626671
2850 AND EXISTS ( SELECT 1 -- Bug 1831151
2851 FROM gms_balances gb
2852 WHERE budget_version_id =
2853 x_current_budget_version_id
2854 AND gb.resource_list_member_id =
2855 m.resource_list_member_id )
2856 AND NVL(m.migration_code,'M') ='M') loop -- Bug 3626671
2857
2858 x_err_stage := 'process period/task/resource <'
2859 || period_rec.period_name
2860 || '><' || to_char(task_rec.task_id)
2861 || '><' || to_char(res_rec.resource_list_member_id)
2862 || '>';
2863 x_burdened_cost := 0;
2864 x_unit_of_measure := NULL;
2865 -- Diverted the call to local procedure for Bug 1831151 instead of PA
2866 get_proj_accum_actuals(x_project_id,
2867 task_rec.task_id,
2868 x_current_budget_version_id,
2869 res_rec.resource_list_member_id,
2870 x_time_phased_type_code,
2871 period_rec.period_name,
2872 period_rec.start_date,
2873 period_rec.end_date,
2874 x_burdened_cost,
2875 x_dummy1,
2876 x_unit_of_measure,
2877 x_err_stage,
2878 x_err_code
2879 );
2880
2881 if (x_err_code <> 0) then
2882 gms_error_pkg.gms_message( x_err_name => 'GMS_BU_GET_ACCUM_ACTUALS_FAIL',
2883 x_err_code => x_err_code,
2884 x_err_buff => x_err_stage);
2885
2886 APP_EXCEPTION.RAISE_EXCEPTION;
2887 end if;
2888 if (nvl(x_burdened_cost,0) <> 0) then
2889
2890 gms_budget_pub.add_budget_line(
2891 p_api_version_number => 1.0
2892 ,x_err_code => x_err_code
2893 ,x_err_stage => x_err_stage
2894 ,x_err_stack => x_err_stack
2895 ,p_pm_product_code => 'GMS'
2896 ,p_project_id => x_project_id
2897 ,p_award_id => x_award_id
2898 ,p_budget_type_code => 'AC'
2899 ,p_task_id => task_rec.task_id
2900 ,p_resource_list_member_id => res_rec.resource_list_member_id
2901 ,p_budget_start_date => period_rec.start_date
2902 ,p_budget_end_date => period_rec.end_date
2903 ,p_period_name => period_rec.period_name
2904 ,p_description => NULL
2905 ,p_raw_cost => x_raw_cost
2906 ,p_burdened_cost => x_burdened_cost
2907 ,p_quantity => x_quantity
2908 ,p_pm_budget_line_reference => NULL ); -- jjj - identifies external system
2909
2910 if (x_err_code <> 0) then
2911 gms_error_pkg.gms_message( x_err_name => 'GMS_ADD_BUDGET_LINE_FAIL',
2912 x_err_code => x_err_code,
2913 x_err_buff => x_err_stage);
2914 x_funding_status := 0;
2915 APP_EXCEPTION.RAISE_EXCEPTION;
2916 end if;
2917
2918 end if;
2919
2920 end loop; -- resource
2921
2922 end if;
2923
2924 end loop; -- task
2925
2926 end if;
2927
2928 end loop; -- period
2929
2930
2931 if (x_time_phased_type_code = 'P') then
2932 close pa_cursor;
2933 else
2934 close gl_cursor;
2935 end if;
2936
2937
2938 x_err_stack := old_stack;
2939
2940
2941 end copy_actual;
2942 ------------------------------------------------------------------------------------
2943 -- added for bug 1831151
2944 -- Actuals accumulation API
2945
2946 -- Following procedure are used to fetch actual amounts from gms_balances
2947 -- based on the parameters passed.
2948
2949 PROCEDURE get_proj_txn_accum (
2950 x_project_id IN NUMBER,
2951 x_task_id IN NUMBER DEFAULT NULL,
2952 x_current_budget_version_id IN NUMBER,
2953 x_period_type IN VARCHAR2 DEFAULT 'P',
2954 x_from_period_name IN VARCHAR2 DEFAULT NULL,
2955 x_prd_start_date IN DATE DEFAULT NULL,
2956 x_prd_end_date IN DATE DEFAULT NULL,
2957 x_burdened_cost IN OUT NOCOPY NUMBER,
2958 x_billable_burdened_cost IN OUT NOCOPY NUMBER,
2959 x_unit_of_measure IN OUT NOCOPY VARCHAR2,
2960 x_err_stage IN OUT NOCOPY VARCHAR2,
2961 x_err_code IN OUT NOCOPY NUMBER
2962 ) IS
2963 CURSOR seltxnaccums_p (x_prd_start_date IN DATE, x_prd_end_date IN DATE) IS
2964 SELECT gmsb.actual_period_to_date tot_burdened_cost,
2965 gmsb.actual_period_to_date tot_billable_burdened_cost
2966 FROM gms_balances gmsb, pa_periods pp
2967 WHERE gmsb.project_id = x_project_id
2968 AND gmsb.budget_version_id = x_current_budget_version_id
2969 AND gmsb.task_id = DECODE (
2970 x_task_id,
2971 NULL, gmsb.task_id,
2972 0, gmsb.task_id,
2973 gmsb.top_task_id,gmsb.task_id,
2974 x_task_id
2975 )
2976 AND x_period_type = 'P'
2977 AND pp.start_date >= gmsb.start_date
2978 AND pp.end_date <= gmsb.end_date
2979 AND gmsb.balance_type = 'EXP'
2980 AND pp.start_date BETWEEN NVL (x_prd_start_date, pp.start_date)
2981 AND NVL (x_prd_end_date, pp.end_date);
2982
2983 CURSOR seltxnaccums_g (x_prd_start_date IN DATE, x_prd_end_date IN DATE) IS
2984 SELECT gmsb.actual_period_to_date tot_burdened_cost,
2985 gmsb.actual_period_to_date tot_billable_burdened_cost
2986 FROM pa_implementations imp,
2987 gl_period_statuses glp,
2988 gms_balances gmsb
2989 WHERE gmsb.project_id = x_project_id
2990 AND gmsb.budget_version_id = x_current_budget_version_id
2991 AND gmsb.task_id = DECODE (
2992 x_task_id,
2993 NULL, gmsb.task_id,
2994 0, gmsb.task_id,
2995 gmsb.top_task_id,gmsb.task_id ,
2996 x_task_id
2997 )
2998 AND x_period_type = 'G'
2999 AND gmsb.balance_type = 'EXP'
3000 AND glp.set_of_books_id = imp.set_of_books_id
3001 AND glp.application_id = 101
3002 AND glp.start_date >= gmsb.start_date
3003 AND glp.end_date <= gmsb.end_date
3004 AND glp.adjustment_period_flag = 'N'
3005 AND glp.start_date BETWEEN NVL (x_prd_start_date, glp.start_date)
3006 AND NVL (x_prd_end_date, glp.end_date);
3007
3008 txnaccumrec_p seltxnaccums_p%ROWTYPE;
3009 txnaccumrec_g seltxnaccums_g%ROWTYPE;
3010 BEGIN
3011 x_err_code := 0;
3012 x_err_stage := 'Getting the Project Txn Accumlation';
3013
3014 -- all of the accumlation numbers are initialized in the calling
3015 -- procedure
3016
3017
3018 IF x_period_type = 'G' THEN
3019 FOR txnaccumrec_g IN seltxnaccums_g (
3020 x_prd_start_date,
3021 x_prd_end_date
3022 )
3023 LOOP
3024 x_burdened_cost :=
3025 x_burdened_cost
3026 + NVL (txnaccumrec_g.tot_burdened_cost, 0);
3027 x_billable_burdened_cost :=
3028 x_billable_burdened_cost
3029 + NVL (txnaccumrec_g.tot_billable_burdened_cost, 0);
3030 END LOOP;
3031
3032 x_unit_of_measure := NULL;
3033 END IF; /* End of x_period_type = 'G' */
3034
3035 IF x_period_type = 'P' THEN
3036 FOR txnaccumrec_p IN seltxnaccums_p (
3037 x_prd_start_date,
3038 x_prd_end_date
3039 )
3040 LOOP
3041 x_burdened_cost :=
3042 x_burdened_cost
3043 + NVL (txnaccumrec_p.tot_burdened_cost, 0);
3044 x_billable_burdened_cost :=
3045 x_billable_burdened_cost
3046 + NVL (txnaccumrec_p.tot_billable_burdened_cost, 0);
3047 END LOOP;
3048
3049 x_unit_of_measure := NULL;
3050 END IF; /* End of x_period_type = 'P' */
3051 END get_proj_txn_accum;
3052 -----------------------------------------------------------------------------------------
3053 -- Added for bug 1831151
3054 -- Actuals accumulation API
3055
3056 -- Following procedure are used to fetch actual amounts from gms_balances
3057 -- based on the parameters passed.
3058
3059
3060 PROCEDURE get_proj_res_accum (
3061 x_project_id IN NUMBER,
3062 x_task_id IN NUMBER DEFAULT NULL,
3063 x_current_budget_version_id IN NUMBER,
3064 x_resource_list_member_id IN NUMBER DEFAULT NULL,
3065 x_period_type IN VARCHAR2 DEFAULT 'P',
3066 x_from_period_name IN VARCHAR2 DEFAULT NULL,
3067 x_prd_start_date IN DATE DEFAULT NULL,
3068 x_prd_end_date IN DATE DEFAULT NULL,
3069 x_burdened_cost IN OUT NOCOPY NUMBER,
3070 x_billable_burdened_cost IN OUT NOCOPY NUMBER,
3071 x_unit_of_measure IN OUT NOCOPY VARCHAR2,
3072 x_err_stage IN OUT NOCOPY VARCHAR2,
3073 x_err_code IN OUT NOCOPY NUMBER
3074 ) IS
3075 CURSOR selresaccums_p (x_prd_start_date IN DATE, x_prd_end_date IN DATE) IS
3076 SELECT gmsb.actual_period_to_date tot_burdened_cost,
3077 gmsb.actual_period_to_date tot_billable_burdened_cost
3078 FROM gms_balances gmsb, pa_periods pp
3079 WHERE gmsb.project_id = x_project_id
3080 AND gmsb.budget_version_id = x_current_budget_version_id
3081 AND gmsb.task_id = DECODE (
3082 x_task_id,
3083 NULL, gmsb.task_id,
3084 0, gmsb.task_id,
3085 gmsb.top_task_id,gmsb.task_id ,
3086 x_task_id
3087 )
3088 AND gmsb.resource_list_member_id = x_resource_list_member_id
3089 AND x_period_type = 'P'
3090 AND gmsb.balance_type = 'EXP'
3091 AND pp.start_date >= gmsb.start_date
3092 AND pp.end_date <= gmsb.end_date
3093 AND pp.start_date BETWEEN NVL (x_prd_start_date, pp.start_date)
3094 AND NVL (x_prd_end_date, pp.end_date)
3095 AND NVL (gmsb.actual_period_to_date, 0) <> 0;
3096
3097 CURSOR selresaccums_g (x_prd_start_date IN DATE, x_prd_end_date IN DATE) IS
3098 SELECT gmsb.actual_period_to_date tot_burdened_cost,
3099 gmsb.actual_period_to_date tot_billable_burdened_cost
3100 FROM pa_implementations imp,
3101 gl_period_statuses glp,
3102 gms_balances gmsb
3103 WHERE gmsb.project_id = x_project_id
3104 AND gmsb.budget_version_id = x_current_budget_version_id
3105 AND gmsb.task_id = DECODE (
3106 x_task_id,
3107 NULL, gmsb.task_id,
3108 0, gmsb.task_id,
3109 gmsb.top_task_id,gmsb.task_id ,
3110 x_task_id
3111 )
3112 AND gmsb.resource_list_member_id = x_resource_list_member_id
3113 AND x_period_type = 'G'
3114 AND gmsb.balance_type = 'EXP'
3115 AND glp.set_of_books_id = imp.set_of_books_id
3116 AND glp.application_id = 101
3117 AND glp.start_date >= gmsb.start_date
3118 AND glp.end_date <= gmsb.end_date
3119 AND glp.adjustment_period_flag = 'N'
3120 AND glp.start_date BETWEEN NVL (x_prd_start_date, glp.start_date)
3121 AND NVL (x_prd_end_date, glp.end_date)
3122 AND NVL (gmsb.actual_period_to_date, 0) <> 0;
3123
3124
3125 resaccumrec_p selresaccums_p%ROWTYPE;
3126 resaccumrec_g selresaccums_g%ROWTYPE;
3127 BEGIN
3128 x_err_code := 0;
3129 x_err_stage := 'Getting the Project Res Accumlation';
3130
3131 -- all of the accumlation numbers are initialized in the calling
3132 -- procedure
3133
3134 IF x_period_type = 'G' THEN
3135 FOR resaccumrec_g IN selresaccums_g (
3136 x_prd_start_date,
3137 x_prd_end_date
3138 )
3139 LOOP
3140 x_burdened_cost :=
3141 x_burdened_cost
3142 + NVL (resaccumrec_g.tot_burdened_cost, 0);
3143 x_billable_burdened_cost :=
3144 x_billable_burdened_cost
3145 + NVL (resaccumrec_g.tot_billable_burdened_cost, 0);
3146 x_unit_of_measure := NULL;
3147 END LOOP;
3148 END IF; /* End of x_period_type = 'G' */
3149
3150 IF x_period_type = 'P' THEN
3151 FOR resaccumrec_p IN selresaccums_p (
3152 x_prd_start_date,
3153 x_prd_end_date
3154 )
3155 LOOP
3156
3157 x_burdened_cost :=
3158 x_burdened_cost
3159 + NVL (resaccumrec_p.tot_burdened_cost, 0);
3160 x_billable_burdened_cost :=
3161 x_billable_burdened_cost
3162 + NVL (resaccumrec_p.tot_billable_burdened_cost, 0);
3163 x_unit_of_measure := NULL;
3164 END LOOP;
3165 END IF; /* End of x_period_type = 'P' */
3166 END get_proj_res_accum;
3167 --------------------------------------------------------------------------------------
3168 -- added for bug 1831151
3169 PROCEDURE get_proj_accum_actuals (
3170 x_project_id IN NUMBER,
3171 x_task_id IN NUMBER DEFAULT NULL,
3172 x_current_budget_version_id IN NUMBER,
3173 x_resource_list_member_id IN NUMBER DEFAULT NULL,
3174 x_period_type IN VARCHAR2 DEFAULT 'P',
3175 x_from_period_name IN VARCHAR2 DEFAULT NULL,
3176 x_prd_start_date IN DATE DEFAULT NULL,
3177 x_prd_end_date IN DATE DEFAULT NULL,
3178 x_burdened_cost IN OUT NOCOPY NUMBER,
3179 x_billable_burdened_cost IN OUT NOCOPY NUMBER,
3180 x_unit_of_measure IN OUT NOCOPY VARCHAR2,
3181 x_err_stage IN OUT NOCOPY VARCHAR2,
3182 x_err_code IN OUT NOCOPY NUMBER
3183 ) IS
3184 BEGIN
3185 x_err_code := 0;
3186 x_err_stage := 'Getting the Project Accumlation';
3187 x_burdened_cost := 0;
3188 x_billable_burdened_cost := 0;
3189 x_unit_of_measure := NULL;
3190
3191 IF (x_resource_list_member_id IS NULL) THEN
3192 -- Call the txn accum
3193 get_proj_txn_accum (
3194 x_project_id,
3195 x_task_id,
3196 x_current_budget_version_id,
3197 x_period_type,
3198 x_from_period_name,
3199 x_prd_start_date,
3200 x_prd_end_date,
3201 x_burdened_cost,
3202 x_billable_burdened_cost,
3203 x_unit_of_measure,
3204 x_err_stage,
3205 x_err_code
3206 );
3207 ELSE
3208 -- Call the resource accum
3209 get_proj_res_accum (
3210 x_project_id,
3211 x_task_id,
3212 x_current_budget_version_id,
3213 x_resource_list_member_id,
3214 x_period_type,
3215 x_from_period_name,
3216 x_prd_start_date,
3217 x_prd_end_date,
3218 x_burdened_cost,
3219 x_billable_burdened_cost,
3220 x_unit_of_measure,
3221 x_err_stage,
3222 x_err_code
3223 );
3224 END IF;
3225 END get_proj_accum_actuals;
3226
3227 -------------------------------------------------------------------------------------
3228 -- This procedure is used by the baseline procedure to copy budget lines and
3229 -- resource assignments from a source (draft) budget version to the destination
3230 -- (baselined) budget version for a single project
3231 --
3232
3233 procedure copy_draft_lines (x_src_version_id in number,
3234 x_time_phased_type_code in varchar2,
3235 x_entry_level_code in varchar2,
3236 x_dest_version_id in number,
3237 x_err_code in out NOCOPY number,
3238 x_err_stage in out NOCOPY varchar2,
3239 x_err_stack in out NOCOPY varchar2,
3240 x_pm_flag in varchar2 )
3241 is
3242 -- Standard who
3243 x_created_by NUMBER(15);
3244 x_last_update_login NUMBER(15);
3245
3246 old_stack varchar2(630);
3247
3248 begin
3249
3250 IF L_DEBUG = 'Y' THEN
3251 gms_error_pkg.gms_debug('*** Start of GMS_BUDGET_CORE.COPY_DRAFT_LINES ***','C');
3252 END IF;
3253
3254 x_err_code := 0;
3255 old_stack := x_err_stack;
3256 x_err_stack := x_err_stack || '->copy_draft_lines';
3257
3258 x_created_by := FND_GLOBAL.USER_ID;
3259 x_last_update_login := FND_GLOBAL.LOGIN_ID;
3260
3261 savepoint before_copy_draft_lines;
3262
3263 x_err_stage := 'copy resource assignment <' || to_char(x_src_version_id)
3264 || '>' ;
3265
3266 IF L_DEBUG = 'Y' THEN
3267 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.COPY_DRAFT_LINES- Inserting records into gms_resource_assignments','C');
3268 END IF;
3269
3270
3271 insert into gms_resource_assignments
3272 (resource_assignment_id,
3273 budget_version_id,
3274 project_id,
3275 task_id,
3276 resource_list_member_id,
3277 last_update_date,
3278 last_updated_by,
3279 creation_date,
3280 created_by,
3281 last_update_login,
3282 unit_of_measure,
3283 track_as_labor_flag)
3284 select gms_resource_assignments_s.nextval,
3285 x_dest_version_id,
3286 s.project_id,
3287 s.task_id,
3288 s.resource_list_member_id,
3289 SYSDATE,
3290 x_created_by,
3291 SYSDATE,
3292 x_created_by,
3293 x_last_update_login,
3294 s.unit_of_measure,
3295 s.track_as_labor_flag
3296 from
3297 gms_resource_assignments s
3298 where s.budget_version_id = x_src_version_id;
3299
3300
3301 x_err_stage := 'copy budget lines <' ||to_char(x_src_version_id)
3302 || '>' ;
3303
3304 IF L_DEBUG = 'Y' THEN
3305 gms_error_pkg.gms_debug('GMS_BUDGET_CORE.COPY_DRAFT_LINES- Inserting records into gms_budget_lines','C');
3306 END IF;
3307
3308 insert into gms_budget_lines
3309 (resource_assignment_id,
3310 start_date,
3311 last_update_date,
3312 last_updated_by,
3313 creation_date,
3314 created_by,
3315 last_update_login,
3316 end_date,
3317 period_name,
3318 quantity,
3319 raw_cost,
3320 burdened_cost,
3321 revenue,
3322 change_reason_code,
3323 description,
3324 attribute_category,
3325 attribute1,
3326 attribute2,
3327 attribute3,
3328 attribute4,
3329 attribute5,
3330 attribute6,
3331 attribute7,
3332 attribute8,
3333 attribute9,
3334 attribute10,
3335 attribute11,
3336 attribute12,
3337 attribute13,
3338 attribute14,
3339 attribute15,
3340 pm_product_code,
3341 pm_budget_line_reference,
3342 raw_cost_source,
3343 burdened_cost_source,
3344 quantity_source,
3345 revenue_source
3346 )
3347 select
3348 da.resource_assignment_id,
3349 l.start_date,
3350 SYSDATE,
3351 x_created_by,
3352 SYSDATE,
3353 x_created_by,
3354 x_last_update_login,
3355 l.end_date,
3356 l.period_name,
3357 l.quantity,
3358 l.raw_cost,
3359 l.burdened_cost,
3360 l.revenue,
3361 l.change_reason_code,
3362 l.description,
3363 l.attribute_category,
3364 l.attribute1,
3365 l.attribute2,
3366 l.attribute3,
3367 l.attribute4,
3368 l.attribute5,
3369 l.attribute6,
3370 l.attribute7,
3371 l.attribute8,
3372 l.attribute9,
3373 l.attribute10,
3374 l.attribute11,
3375 l.attribute12,
3376 l.attribute13,
3377 l.attribute14,
3378 l.attribute15,
3379 decode(x_pm_flag,'Y',l.pm_product_code,NULL),
3380 decode(x_pm_flag,'Y',l.pm_budget_line_reference,NULL),
3381 'B',
3382 'B',
3383 'B',
3384 'B'
3385 from gms_budget_lines l,
3386 gms_resource_assignments sa,
3387 gms_resource_assignments da
3388 where l.resource_assignment_id = sa.resource_assignment_id
3389 and sa.budget_version_id = x_src_version_id
3390 and sa.task_id = da.task_id
3391 and sa.project_id = da.project_id
3392 and sa.resource_list_member_id = da.resource_list_member_id
3393 and da.budget_version_id = x_dest_version_id;
3394
3395 if(x_err_code <> 0) then
3396 rollback to before_copy_draft_lines;
3397 return;
3398 end if;
3399
3400 x_err_stack := old_stack;
3401
3402 IF L_DEBUG = 'Y' THEN
3403 gms_error_pkg.gms_debug('*** End of GMS_BUDGET_CORE.COPY_DRAFT_LINES ***','C');
3404 END IF;
3405
3406 exception
3407 when others then
3408 -- Bug 2587078 : Modified the below code to set proper error message
3409 -- x_err_code := SQLCODE;
3410 x_err_stage := 'GMS_BUDGET_CORE.COPY_DRAFT_LINES - In others exception';
3411 gms_error_pkg.gms_message(x_err_name => 'GMS_BU_COPY_BUDG_LINES_FAIL',
3412 x_err_code => x_err_code,
3413 x_err_buff => x_err_stage);
3414 fnd_msg_pub.add;
3415 rollback to before_copy_draft_lines;
3416 return;
3417
3418 end copy_draft_lines;
3419
3420 END gms_budget_core;