[Home] [Help]
PACKAGE BODY: APPS.IGW_GENERATE_PERIODS
Source
1 PACKAGE BODY IGW_GENERATE_PERIODS as
2 -- $Header: igwbugpb.pls 115.29 2002/11/19 23:47:56 vmedikon ship $
3 PROCEDURE create_budget_detail(
4 l_proposal_id IGW_budget_periods.proposal_id%TYPE
5 ,l_version_id IGW_budget_periods.version_id%TYPE
6 ,l_budget_period_id IGW_budget_periods.budget_period_id%TYPE
7 ,l_line_item_id IGW_budget_details.line_item_id%TYPE
8 ,l_expenditure_type IGW_budget_details.expenditure_type%TYPE
9 ,l_budget_category_code IGW_budget_details.budget_category_code%TYPE
10 ,l_expenditure_category_flag IGW_budget_details.expenditure_category_flag%TYPE
11 ,l_line_item_description IGW_budget_details.line_item_description%TYPE
12 ,l_based_on_line_item IGW_budget_details.based_on_line_item%TYPE
13 ,l_line_item_cost NUMBER
14 ,l_cost_sharing_amount NUMBER
15 ,l_underrecovery_amount NUMBER
16 ,l_apply_inflation_flag VARCHAR2
17 ,l_budget_justification IGW_budget_details.budget_justification%TYPE
18 ,l_location_code VARCHAR2) is
19 BEGIN
20 insert into IGW_budget_details(
21 proposal_id
22 ,version_id
23 ,budget_period_id
24 ,line_item_id
25 ,expenditure_type
26 ,budget_category_code
27 ,expenditure_category_flag
28 ,line_item_description
29 ,based_on_line_item
30 ,line_item_cost
31 ,cost_sharing_amount
32 ,underrecovery_amount
33 ,apply_inflation_flag
34 ,budget_justification
35 ,location_code
36 ,record_version_number
37 ,last_update_date
38 ,last_updated_by
39 ,creation_date
40 ,created_by
41 ,last_update_login )
42 values (
43 l_proposal_id
44 ,l_version_id
45 ,l_budget_period_id
46 ,igw_budget_details_s.nextval
47 ,l_expenditure_type
48 ,l_budget_category_code
49 ,l_expenditure_category_flag
50 ,l_line_item_description
51 ,l_based_on_line_item
52 ,l_line_item_cost
53 ,l_cost_sharing_amount
54 ,l_underrecovery_amount
55 ,l_apply_inflation_flag
56 ,l_budget_justification
57 ,l_location_code
58 ,1
59 ,sysdate
60 ,fnd_global.user_id
61 ,sysdate
62 ,fnd_global.user_id
63 ,fnd_global.login_id);
64
65 END create_budget_detail;
66
67 PROCEDURE create_budget_personnel_amts (
68 l_budget_personnel_detail_id NUMBER
69 ,l_rate_class_id NUMBER
70 ,l_rate_type_id NUMBER
71 ,l_apply_rate_flag VARCHAR2
72 ,l_calculated_cost NUMBER
73 ,l_calculated_cost_sharing NUMBER) IS
74 BEGIN
75 insert into igw_budget_personnel_cal_amts (
76 budget_personnel_detail_id
77 ,rate_class_id
78 ,rate_type_id
79 ,apply_rate_flag
80 ,calculated_cost
81 ,calculated_cost_sharing
82 ,record_version_number
83 ,last_update_date
84 ,last_updated_by
85 ,creation_date
86 ,created_by
87 ,last_update_login)
88 values(
89 l_budget_personnel_detail_id
90 ,l_rate_class_id
91 ,l_rate_type_id
92 ,l_apply_rate_flag
93 ,l_calculated_cost
94 ,l_calculated_cost_sharing
95 ,1
96 ,sysdate
97 ,fnd_global.user_id
98 ,sysdate
99 ,fnd_global.user_id
100 ,fnd_global.login_id);
101 END create_budget_personnel_amts;
102
103 PROCEDURE create_budget_detail_amts (p_proposal_id NUMBER
104 ,p_version_id NUMBER
105 ,p_budget_period_id NUMBER
106 ,p_line_item_id NUMBER
107 ,p_rate_class_id NUMBER
108 ,p_rate_type_id NUMBER
109 ,p_apply_rate_flag VARCHAR2
110 ,p_calculated_cost NUMBER
111 ,p_calculated_cost_sharing NUMBER) is
112 BEGIN
113 insert into igw_budget_details_cal_amts (
114 proposal_id
115 ,version_id
116 ,budget_period_id
117 ,line_item_id
118 ,rate_class_id
119 ,rate_type_id
120 ,apply_rate_flag
121 ,calculated_cost
122 ,calculated_cost_sharing
123 ,record_version_number
124 ,last_update_date
125 ,last_updated_by
126 ,creation_date
127 ,created_by
128 ,last_update_login)
129 values (
130 p_proposal_id
131 ,p_version_id
132 ,p_budget_period_id
133 ,p_line_item_id
134 ,p_rate_class_id
135 ,p_rate_type_id
136 ,p_apply_rate_flag
137 ,p_calculated_cost
138 ,p_calculated_cost_sharing
139 ,1
140 ,sysdate
141 ,fnd_global.user_id
142 ,sysdate
143 ,fnd_global.user_id
144 ,fnd_global.login_id);
145 END;
146
147 --------------------------------------------------------------------------------------------
148 /* Lines are not generated for expenditure types/categories with
149 personnel attached flag if the periods are not exactly 12 month
150 periods. The reason being that if a personnel in the first budget line
151 is for 12 months, and if the last period is only 10 months(say), how do
152 we allocate that person?
153 */
154
155 PROCEDURE generate_lines ( p_proposal_id NUMBER
156 ,p_version_id NUMBER
157 ,p_budget_period_id NUMBER
158 ,p_activity_type_code VARCHAR2
159 ,p_oh_rate_class_id NUMBER
160 ,x_return_status OUT NOCOPY VARCHAR2
161 ,x_msg_data OUT NOCOPY VARCHAR2
162 ,x_msg_count OUT NOCOPY NUMBER) is
163
164 cursor c_budget_version is
165 select apply_inflation_setup_rates
166 , apply_eb_setup_rates
167 , apply_oh_setup_rates
168 , enter_budget_at_period_level
169 from igw_budgets
170 where proposal_id = p_proposal_id
171 and version_id = p_version_id;
172
173
174 cursor c_budget_periods is
175 select count(*)
176 from igw_budget_periods
177 where proposal_id = p_proposal_id
178 and version_id = p_version_id;
179
180
181 cursor c_budget_details is
182 select pbd.proposal_id
183 , pbd.version_id
184 , pbd.budget_period_id
185 , pbd.line_item_id
186 , pbd.expenditure_type
187 , pbd.budget_category_code
188 , pbd.expenditure_category_flag
189 , pbd.line_item_description
190 , pbd.line_item_cost
191 , pbd.cost_sharing_amount
192 , pbd.underrecovery_amount
193 , pbd.apply_inflation_flag
194 , pbd.budget_justification
195 , pbd.location_code
196 , et.personnel_attached_flag
197 from igw_budget_details pbd
198 , igw_budget_expenditures_v et
199 where pbd.expenditure_type = et.budget_expenditure
200 and pbd.expenditure_category_flag = et.expenditure_category_flag
201 and pbd.proposal_id = p_proposal_id
202 and pbd.budget_period_id = p_budget_period_id
203 and pbd.version_id = p_version_id;
204
205 l_line_item_id NUMBER(15);
206
207 cursor c_budget_personnel is
208 select budget_personnel_detail_id
209 , line_item_id
210 , person_id
211 , party_id
212 , start_date
213 , end_date
214 , period_type_code
215 , appointment_type_code
216 , salary_requested
217 , percent_charged
218 , percent_effort
219 , cost_sharing_percent
220 , underrecovery_amount
221 from igw_budget_personnel_details
222 where line_item_id = l_line_item_id;
223
224 l_dummy_personnel_id NUMBER;
225 l_salary_requested NUMBER;
226 l_cost_sharing_amount NUMBER;
227 l_no_of_periods NUMBER(10);
228 l_base_amount NUMBER;
229 l_input_amount NUMBER;
230 l_calculated_cost_share NUMBER;
231 l_calculated_cost_share_ov NUMBER;
232 l_calculated_percent NUMBER;
233 l_budget_period_id NUMBER(10);
234 l_rate_class_id_oh NUMBER(15);
235 l_rate_type_id_oh NUMBER(15);
236 l_rate_class_id_eb NUMBER(15);
237 l_rate_type_id_eb NUMBER(15);
238 l_rate_class_id_oh_d NUMBER(15);
239 l_rate_type_id_oh_d NUMBER(15);
240 l_rate_class_id_eb_d NUMBER(15);
241 l_rate_type_id_eb_d NUMBER(15);
242 l_rate_class_id_inf NUMBER(15);
243 l_rate_type_id_inf NUMBER(15);
244 l_budget_end_date DATE;
245 l_budget_start_date DATE;
246 l_max_budget_end_date DATE;
247 l_max_budget_start_date DATE;
248 l_personnel_start_date DATE;
249 l_personnel_end_date DATE;
250 l_dummy_value VARCHAR2(1);
251 l_oh_value NUMBER;
252 l_eb_value NUMBER;
253 l_oh_value_ov NUMBER;
254 l_eb_value_ov NUMBER;
255 l_calculation_base NUMBER;
256 l_effective_date DATE;
257 l_sum_cost NUMBER;
258 l_sum_cost_share NUMBER;
259 l_inflated_salary NUMBER;
260 l_inflated_salary_ov NUMBER;
261 l_line_item_seq NUMBER;
262 l_direct_cost1 NUMBER;
263 l_direct_cost2 NUMBER;
264 l_cost_share1 NUMBER;
265 l_cost_share2 NUMBER;
266 l_cost_share3 NUMBER;
267 l_underrecovery NUMBER;
268 l_indirect_cost NUMBER;
269 l_total_cost NUMBER;
270 l_total_direct_cost NUMBER;
271 l_total_indirect_cost NUMBER;
272 l_cost_sharing_amt NUMBER;
273 l_underrecovery_amount NUMBER;
274 l_total_cost_limit NUMBER;
275 l_appointment_type_code VARCHAR2(30);
276 l_apply_rate_flag_oh VARCHAR2(1);
277 l_apply_rate_flag_eb VARCHAR2(1);
278 l_value VARCHAR2(10);
279 l_apply_inflation_setup_rates VARCHAR2(1);
280 l_apply_eb_setup_rates VARCHAR2(1);
281 l_apply_oh_setup_rates VARCHAR2(1);
282 l_enter_budget_at_period_level VARCHAR2(1);
283 l_return_status VARCHAR2(1);
284 l_msg_data VARCHAR2(200);
285 l_msg_count NUMBER(10);
286
287 BEGIN
288 fnd_msg_pub.initialize;
289 begin
290 select '1'
291 into l_value
292 from igw_budget_details pbd
293 where pbd.proposal_id = p_proposal_id
294 and pbd.version_id = p_version_id
295 and pbd.budget_period_id <> 1
296 and rownum < 2;
297
298 if l_value is not null then
299 fnd_message.set_name('IGW', 'IGW_CANNOT_GENERATE_PERIODS');
300 fnd_msg_pub.add;
301 l_return_status := 'E';
302 RAISE FND_API.G_EXC_ERROR;
303 end if;
304 exception
305 when no_data_found then null;
306 end;
307
308 open c_budget_version;
309 fetch c_budget_version into l_apply_inflation_setup_rates
310 ,l_apply_eb_setup_rates
311 ,l_apply_oh_setup_rates
312 ,l_enter_budget_at_period_level;
313 close c_budget_version;
314
315 open c_budget_periods;
316 fetch c_budget_periods into l_no_of_periods;
317 close c_budget_periods;
318 for rec_budget_details in c_budget_details
319 LOOP
320 l_rate_class_id_oh := p_oh_rate_class_id;
321
322 IGW_OVERHEAD_CAL.get_rate_id(rec_budget_details.expenditure_type
323 ,rec_budget_details.expenditure_category_flag
324 , 'O'
325 ,l_rate_class_id_oh
326 ,l_rate_type_id_oh
327 ,l_return_status
328 ,l_msg_data);
329
330 if l_return_status <> 'S' then
331 raise FND_API.G_EXC_ERROR;
332 end if;
333
334 if rec_budget_details.personnel_attached_flag = 'N' then
335 BEGIN
336 select apply_rate_flag
337 into l_apply_rate_flag_oh
338 from igw_budget_details_cal_amts
339 where line_item_id = rec_budget_details.line_item_id;
340 EXCEPTION
341 when no_data_found then null;
342 End;
343 if rec_budget_details.apply_inflation_flag = 'Y' then
344 IGW_OVERHEAD_CAL.get_rate_id(rec_budget_details.expenditure_type
345 ,rec_budget_details.expenditure_category_flag
346 ,'I'
347 , l_rate_class_id_inf
348 ,l_rate_type_id_inf
349 ,l_return_status
350 ,l_msg_data);
351 if l_return_status <> 'S' then
352 raise FND_API.G_EXC_ERROR;
353 end if;
354 end if;
355 l_input_amount := rec_budget_details.line_item_cost;
356 l_budget_period_id := rec_budget_details.budget_period_id;
357
358 for i in 1 .. (l_no_of_periods-1)
359 LOOP
360
361 l_budget_period_id := l_budget_period_id + 1;
362
363 begin
364 select start_date, end_date
365 into l_budget_start_date
366 , l_budget_end_date
367 from igw_budget_periods
368 where proposal_id = p_proposal_id
369 and version_id = p_version_id
370 and budget_period_id = l_budget_period_id;
371 exception
372 when no_data_found then
373 l_msg_data := 'IGW_PERIOD_NOT_CONSECUTIVE';
374 l_return_status := 'E';
375 fnd_message.set_name('IGW', 'IGW_PERIOD_NOT_CONSECUTIVE');
376 fnd_msg_pub.add;
377 RAISE FND_API.G_EXC_ERROR;
378 end;
379
380
381 if i = (l_no_of_periods - 1) then
382 begin
383 select max(end_date)
384 into l_budget_end_date
385 from igw_budget_periods
386 where proposal_id = p_proposal_id
387 and version_id = p_version_id;
388 exception
389 when no_data_found then
390 l_msg_data := 'IGW_PERIOD_NOT_CONSECUTIVE';
391 l_return_status := 'E';
392 fnd_message.set_name('IGW', 'IGW_PERIOD_NOT_CONSECUTIVE');
393 fnd_msg_pub.add;
394 RAISE FND_API.G_EXC_ERROR;
395 end;
396 end if;
397
398
399 if rec_budget_details.apply_inflation_flag = 'Y' then
400
401 IGW_OVERHEAD_CAL.calc_inflation(p_proposal_id
402 ,p_version_id
403 ,l_input_amount
404 ,l_budget_start_date
405 ,l_budget_end_date
406 ,l_base_amount
407 ,p_activity_type_code
408 ,rec_budget_details.location_code
409 ,l_rate_class_id_inf
410 ,l_rate_type_id_inf
411 ,l_return_status
412 ,l_msg_data
413 ,l_msg_count);
414
415 if l_return_status <> 'S' then
416 raise FND_API.G_EXC_ERROR;
417 end if;
418 else
419 l_base_amount := rec_budget_details.line_item_cost;
420 end if;
421
422 /* NON_PERSONNEL: the reason why we are calculating cal_oh twice is because, once to calculate cost share amt
423 for line and second time to calculate cost share for Overhead line. */
424
425 /* if oh_setup_rates is not applied then don't calculate oh */
426 if l_apply_oh_setup_rates = 'N' then
427 l_oh_value := 0;
428 l_oh_value_ov := 0;
429 else
430 IGW_OVERHEAD_CAL.calc_oh(p_proposal_id
431 ,p_version_id
432 ,l_base_amount
433 ,l_budget_start_date
434 ,l_budget_end_date
435 ,l_oh_value
436 ,l_oh_value_ov
437 ,p_activity_type_code
438 ,rec_budget_details.location_code
439 ,l_rate_class_id_oh
440 ,l_rate_type_id_oh
441 ,l_return_status
442 ,l_msg_data
443 ,l_msg_count);
444
445 if l_return_status <> 'S' then
446 raise FND_API.G_EXC_ERROR;
447 end if;
448 end if;
449
450 /* if oh_setup_rates is not applied then don't calculate oh */
451 if l_apply_oh_setup_rates = 'N' then
452 l_calculated_cost_share := 0;
453 l_calculated_cost_share_ov := 0;
454 else
455 if l_apply_rate_flag_oh = 'N' then
456 l_oh_value_ov := 0;
457 elsif l_apply_rate_flag_oh = 'Y' then
458 IGW_OVERHEAD_CAL.calc_oh(p_proposal_id
459 ,p_version_id
460 ,rec_budget_details.cost_sharing_amount
461 ,l_budget_start_date
462 ,l_budget_end_date
463 ,l_calculated_cost_share
464 ,l_calculated_cost_share_ov
465 ,p_activity_type_code
466 ,rec_budget_details.location_code
467 ,l_rate_class_id_oh
468 ,l_rate_type_id_oh
469 ,l_return_status
470 ,l_msg_data
471 ,l_msg_count);
472 if l_return_status <> 'S' then
473 raise FND_API.G_EXC_ERROR;
474 end if;
475 end if;
476 end if;
477
478 create_budget_detail (
479 rec_budget_details.proposal_id
480 ,rec_budget_details.version_id
481 ,l_budget_period_id
482 ,l_line_item_id
483 ,rec_budget_details.expenditure_type
484 ,rec_budget_details.budget_category_code
485 ,rec_budget_details.expenditure_category_flag
486 ,rec_budget_details.line_item_description
487 ,rec_budget_details.line_item_id
488 ,l_base_amount
489 ,rec_budget_details.cost_sharing_amount
490 ,(l_oh_value - l_oh_value_ov)
491 ,rec_budget_details.apply_inflation_flag
492 ,rec_budget_details.budget_justification
493 ,rec_budget_details.location_code);
494
495 if l_rate_class_id_oh is not null and l_rate_type_id_oh is not null then
496 insert into igw_budget_details_cal_amts (
497 proposal_id
498 ,version_id
499 ,budget_period_id
500 ,line_item_id
501 ,rate_class_id
502 ,rate_type_id
503 ,apply_rate_flag
504 ,calculated_cost
505 ,calculated_cost_sharing
506 ,last_update_date
507 ,last_updated_by
508 ,creation_date
509 ,created_by
510 ,last_update_login)
511 values (
512 rec_budget_details.proposal_id
513 ,rec_budget_details.version_id
514 ,l_budget_period_id
515 ,igw_budget_details_s.currval
516 ,l_rate_class_id_oh
517 ,l_rate_type_id_oh
518 ,l_apply_rate_flag_oh
519 ,nvl(l_oh_value_ov,0)
520 ,nvl(l_calculated_cost_share_ov,0)
521 ,sysdate
522 ,fnd_global.user_id
523 ,sysdate
524 ,fnd_global.user_id
525 ,fnd_global.login_id);
526 end if;
527 l_input_amount := l_base_amount;
528 END LOOP;
529
530 elsif rec_budget_details.personnel_attached_flag = 'Y' then
531 IGW_OVERHEAD_CAL.get_rate_id(rec_budget_details.expenditure_type
532 ,rec_budget_details.expenditure_category_flag
533 ,'E'
534 ,l_rate_class_id_eb
535 ,l_rate_type_id_eb
536 ,l_return_status
537 , l_msg_data);
538
539 if l_return_status <> 'S' then
540 raise FND_API.G_EXC_ERROR;
541 end if;
542
543 select max(end_date), max(start_date)
544 into l_max_budget_end_date, l_max_budget_start_date
545 from igw_budget_periods
546 where proposal_id = p_proposal_id
547 and version_id = p_version_id;
548
549 l_line_item_id := rec_budget_details.line_item_id;
550 l_budget_period_id := p_budget_period_id;
551
552 BEGIN
553 select '1'
554 into l_dummy_value
555 from igw_budget_periods
556 where ((end_date-start_date) < 364 or (end_date-start_date) > 365)
557 --and start_date <> l_max_budget_start_date
558 and proposal_id = p_proposal_id
559 and version_id = p_version_id
560 and rownum <2;
561 EXCEPTION
562 when no_data_found then
563 null;
564 END;
565
566 for i in 1 .. (l_no_of_periods-1)
567 LOOP
568 l_budget_period_id := l_budget_period_id + 1;
569 begin
570 select start_date, end_date
571 into l_budget_start_date
572 , l_budget_end_date
573 from igw_budget_periods
574 where proposal_id = p_proposal_id
575 and version_id = p_version_id
576 and budget_period_id = l_budget_period_id;
577 exception
578 when no_data_found then
579 l_msg_data := 'IGW_PERIOD_NOT_CONSECUTIVE';
580 l_return_status := 'E';
581 fnd_message.set_name('IGW', 'IGW_PERIOD_NOT_CONSECUTIVE');
582 fnd_msg_pub.add;
583 RAISE FND_API.G_EXC_ERROR;
584 end;
585
586 if i = (l_no_of_periods - 1) then
587 l_budget_end_date := l_max_budget_end_date;
588 end if;
589
590
591 create_budget_detail (
592 rec_budget_details.proposal_id
593 ,rec_budget_details.version_id
594 ,l_budget_period_id
595 ,l_line_item_id
596 ,rec_budget_details.expenditure_type
597 ,rec_budget_details.budget_category_code
598 ,rec_budget_details.expenditure_category_flag
599 ,rec_budget_details.line_item_description
600 ,rec_budget_details.line_item_id
601 ,0
602 ,0
603 ,rec_budget_details.underrecovery_amount
604 ,rec_budget_details.apply_inflation_flag
605 ,rec_budget_details.budget_justification
606 ,rec_budget_details.location_code);
607
608 if l_rate_class_id_oh is not null and l_rate_type_id_oh is not null then
609 insert into igw_budget_details_cal_amts (
610 proposal_id
611 ,version_id
612 ,budget_period_id
613 ,line_item_id
614 ,rate_class_id
615 ,rate_type_id
616 ,apply_rate_flag
617 ,calculated_cost
618 ,calculated_cost_sharing
619 ,last_update_date
620 ,last_updated_by
621 ,creation_date
622 ,created_by
623 ,last_update_login)
624 values (
625 rec_budget_details.proposal_id
626 ,rec_budget_details.version_id
627 ,l_budget_period_id
628 ,igw_budget_details_s.currval
629 ,l_rate_class_id_oh
630 ,l_rate_type_id_oh
631 ,l_apply_rate_flag_oh
632 ,0
633 ,0
634 ,sysdate
635 ,fnd_global.user_id
636 ,sysdate
637 ,fnd_global.user_id
638 ,fnd_global.login_id);
639 end if;
640
641 if l_rate_class_id_eb is not null and l_rate_type_id_eb is not null then
642 insert into igw_budget_details_cal_amts (
643 proposal_id
644 ,version_id
645 ,budget_period_id
646 ,line_item_id
647 ,rate_class_id
648 ,rate_type_id
649 ,apply_rate_flag
650 ,calculated_cost
651 ,calculated_cost_sharing
652 ,last_update_date
653 ,last_updated_by
654 ,creation_date
655 ,created_by
656 ,last_update_login)
657 values (
658 rec_budget_details.proposal_id
659 ,rec_budget_details.version_id
660 ,l_budget_period_id
661 ,igw_budget_details_s.currval
662 ,l_rate_class_id_eb
663 ,l_rate_type_id_eb
664 ,l_apply_rate_flag_eb
665 ,0
666 ,0
667 ,sysdate
668 ,fnd_global.user_id
669 ,sysdate
670 ,fnd_global.user_id
671 ,fnd_global.login_id);
672 end if;
673
674 if l_dummy_value is null then
675 for rec_budget_personnel in c_budget_personnel
676 LOOP
677 BEGIN
678 select pca.apply_rate_flag
679 into l_apply_rate_flag_oh
680 from igw_budget_personnel_cal_amts pca
681 , igw_budget_personnel_details pbd
682 where pbd.budget_personnel_detail_id = rec_budget_personnel.budget_personnel_detail_id
683 and pca.budget_personnel_detail_id = pbd.budget_personnel_detail_id
684 and pca.rate_class_id = (select pr.rate_class_id
685 from igw_rate_classes pr
686 where pca.rate_class_id = pr.rate_class_id
687 and pr.rate_class_type = 'O');
688 EXCEPTION
689 when no_data_found then null;
690 End;
691
692 BEGIN
693 select pca.apply_rate_flag
694 into l_apply_rate_flag_eb
695 from igw_budget_personnel_cal_amts pca
696 , igw_budget_personnel_details pbd
697 where pbd.budget_personnel_detail_id = rec_budget_personnel.budget_personnel_detail_id
698 and pca.budget_personnel_detail_id = pbd.budget_personnel_detail_id
699 and pca.rate_class_id = (select pr.rate_class_id
700 from igw_rate_classes pr
701 where pca.rate_class_id = pr.rate_class_id
702 and pr.rate_class_type = 'E');
703 EXCEPTION
704 when no_data_found then null;
705 End;
706 l_personnel_start_date:= add_months(rec_budget_personnel.start_date, 12*i);
707
708 if i = (l_no_of_periods - 1) then
709 if add_months(rec_budget_personnel.end_date, 12*i) > l_budget_end_date then
710 l_personnel_end_date := l_budget_end_date;
711 else
712 l_personnel_end_date := add_months(rec_budget_personnel.end_date, 12*i);
713 end if;
714 else
715 l_personnel_end_date := add_months(rec_budget_personnel.end_date, 12*i);
716 end if;
717
718
719 ---get base amount
720 select calculation_base
721 , effective_date
722 , appointment_type_code
723 into l_calculation_base
724 , l_effective_date
725 , l_appointment_type_code
726 from igw_budget_persons
727 where proposal_id = p_proposal_id
728 and version_id = p_version_id
729 --and person_id = rec_budget_personnel.person_id
730 and party_id = rec_budget_personnel.party_id
731 and appointment_type_code = rec_budget_personnel.appointment_type_code;
732
733
734
735 IGW_OVERHEAD_CAL.calc_salary(p_proposal_id
736 ,p_version_id
737 ,l_calculation_base
738 ,l_effective_date
739 ,l_appointment_type_code
740 ,l_personnel_start_date
741 ,l_personnel_end_date
742 ,l_inflated_salary
743 ,l_inflated_salary_ov
744 ,rec_budget_details.expenditure_type
745 ,rec_budget_details.expenditure_category_flag
746 ,p_activity_type_code
747 ,rec_budget_details.location_code
748 ,l_return_status
749 ,l_msg_data
750 ,l_msg_count);
751 if l_return_status <> 'S' then
752 raise FND_API.G_EXC_ERROR;
753 end if;
754 l_inflated_salary := rec_budget_personnel.percent_charged/100 * l_inflated_salary;
755 l_inflated_salary_ov := rec_budget_personnel.percent_charged/100 * l_inflated_salary_ov;
756
757 if l_apply_rate_flag_oh = 'N' then
758 l_rate_class_id_oh_d := null;
759 l_rate_type_id_oh_d := null;
760 else
761 l_rate_class_id_oh_d := l_rate_class_id_oh;
762 l_rate_type_id_oh_d := l_rate_type_id_oh;
763 end if;
764 if l_apply_rate_flag_eb = 'N' then
765 l_rate_class_id_eb_d := null;
766 l_rate_type_id_eb_d := null;
767 else
768 l_rate_class_id_eb_d := l_rate_class_id_eb;
769 l_rate_type_id_eb_d := l_rate_type_id_eb;
770 end if;
771
772 /* if oh_setup_rates is not applied then don't calculate oh */
773 if l_apply_oh_setup_rates = 'N' then
774 l_oh_value := 0;
775 l_oh_value_ov := 0;
776 end if;
777
778 /* if eb_setup_rates is not applied then don't calculate eb */
779 if l_apply_eb_setup_rates = 'N' then
780 l_eb_value := 0;
781 l_eb_value_ov := 0;
782 end if;
783
784 /* No need to calculate eb and oh if setup rates flags are equal to N */
785 if l_apply_oh_setup_rates <> 'N' and l_apply_eb_setup_rates <> 'N' then
786 IGW_OVERHEAD_CAL.calc_oh_eb (p_proposal_id
787 ,p_version_id
788 ,l_inflated_salary
789 ,l_personnel_start_date
790 ,l_personnel_end_date
791 ,l_oh_value
792 ,l_oh_value_ov
793 ,l_eb_value
794 ,l_eb_value_ov
795 ,p_activity_type_code
796 ,rec_budget_details.location_code
797 ,l_rate_class_id_oh_d
798 ,l_rate_type_id_oh_d
799 ,l_rate_class_id_eb_d
800 ,l_rate_type_id_eb_d
801 ,l_return_status
802 ,l_msg_data
803 ,l_msg_count);
804 if l_return_status <> 'S' then
805 raise FND_API.G_EXC_ERROR;
806 end if;
807 end if;
808
809 /* No need to calculate eb and oh if setup rates flags are equal to N */
810 /* Recalculate budget is being called at the end. Hence all the amounts will be sunk after this operation */
811
812 if l_apply_oh_setup_rates <> 'N' and l_apply_eb_setup_rates <> 'N' then
813 IGW_OVERHEAD_CAL.calc_oh_eb (p_proposal_id
814 ,p_version_id
815 ,l_inflated_salary
816 ,l_personnel_start_date
817 ,l_personnel_end_date
818 ,l_oh_value
819 ,l_oh_value_ov
820 ,l_eb_value
821 ,l_eb_value_ov
822 ,p_activity_type_code
823 ,rec_budget_details.location_code
824 ,l_rate_class_id_oh
825 ,l_rate_type_id_oh
826 ,l_rate_class_id_eb
827 ,l_rate_type_id_eb
828 ,l_return_status
829 ,l_msg_data
830 ,l_msg_count);
831 if l_return_status <> 'S' then
832 raise FND_API.G_EXC_ERROR;
833 end if;
834 end if;
835
836 if l_apply_rate_flag_oh = 'N' then
837 l_oh_value_ov := 0;
838 end if;
839 if l_apply_rate_flag_eb = 'N' then
840 l_eb_value_ov := 0;
841 end if;
842
843 insert into igw_budget_personnel_details (
844 budget_personnel_detail_id
845 ,proposal_id
846 ,version_id
847 ,budget_period_id
848 ,line_item_id
849 ,person_id
850 ,party_id
851 ,start_date
852 ,end_date
853 ,period_type_code
854 ,appointment_type_code
855 ,salary_requested
856 ,percent_charged
857 ,percent_effort
858 ,cost_sharing_percent
859 ,cost_sharing_amount
860 ,underrecovery_amount
861 ,last_update_date
862 ,last_updated_by
863 ,creation_date
864 ,created_by
865 ,last_update_login)
866 values (
867 igw_budget_personnel_s.nextval
868 ,rec_budget_details.proposal_id
869 ,rec_budget_details.version_id
870 ,l_budget_period_id
871 ,igw_budget_details_s.currval
872 ,rec_budget_personnel.person_id
873 ,rec_budget_personnel.party_id
874 ,l_personnel_start_date
875 ,l_personnel_end_date
876 ,rec_budget_personnel.period_type_code
877 ,rec_budget_personnel.appointment_type_code
878 ,l_inflated_salary_ov
879 ,rec_budget_personnel.percent_charged
880 ,rec_budget_personnel.percent_effort
881 ,rec_budget_personnel.cost_sharing_percent
882 ,rec_budget_personnel.cost_sharing_percent/100 *l_inflated_salary_ov
883 ,l_oh_value_ov - l_oh_value
884 ,sysdate
885 ,fnd_global.user_id
886 ,sysdate
887 ,fnd_global.user_id
888 ,fnd_global.login_id);
889
890 select igw_budget_personnel_s.currval into l_dummy_personnel_id from dual;
891 if l_rate_class_id_oh is not null and l_rate_type_id_oh is not null then
892 create_budget_personnel_amts (
893 l_dummy_personnel_id
894 ,l_rate_class_id_oh
895 ,l_rate_type_id_oh
896 ,l_apply_rate_flag_oh
897 ,l_oh_value_ov
898 ,rec_budget_personnel.cost_sharing_percent/100 * l_oh_value_ov);
899 end if;
900
901 if l_rate_class_id_eb is not null and l_rate_type_id_eb is not null then
902 create_budget_personnel_amts (
903 l_dummy_personnel_id
904 ,l_rate_class_id_eb
905 ,l_rate_type_id_eb
906 ,l_apply_rate_flag_eb
907 ,l_eb_value_ov
908 ,rec_budget_personnel.cost_sharing_percent/100 * l_eb_value_ov);
909 end if;
910 END LOOP; --for rec_personnel_budget
911 end if;
912 select igw_budget_details_s.currval
913 into l_line_item_seq
914 from dual;
915
916
917 update igw_budget_details_cal_amts pdc
918 set pdc.calculated_cost =
919 (select nvl(sum(ppc.calculated_cost),0)
920 from igw_budget_personnel_cal_amts ppc
921 , igw_budget_personnel_details ppd
922 , igw_budget_details pd
923 where pd.line_item_id = ppd.line_item_id
924 and ppd.budget_personnel_detail_id =ppc.budget_personnel_detail_id
925 and pd.proposal_id = p_proposal_id
926 and pd.version_id = p_version_id
927 and pd.budget_period_id = l_budget_period_id
928 and pd.line_item_id = l_line_item_seq
929 and ppc.rate_class_id = pdc.rate_class_id
930 and ppc.rate_type_id = pdc.rate_type_id)
931 , pdc.calculated_cost_sharing =
932 (select nvl(sum(ppc.calculated_cost_sharing),0)
933 from igw_budget_personnel_cal_amts ppc
934 , igw_budget_personnel_details ppd
935 , igw_budget_details pd
936 where pd.line_item_id = ppd.line_item_id
937 and ppd.budget_personnel_detail_id = ppc.budget_personnel_detail_id
938 and pd.proposal_id = p_proposal_id
939 and pd.version_id = p_version_id
940 and pd.budget_period_id = l_budget_period_id
941 and pd.line_item_id = l_line_item_seq
942 and ppc.rate_class_id = pdc.rate_class_id
943 and ppc.rate_type_id = pdc.rate_type_id)
944 where pdc.line_item_id = l_line_item_seq;
945
946 update igw_budget_details pdb
947 set line_item_cost =
948 (select nvl(sum(salary_requested),0)
949 from igw_budget_personnel_details ppd
950 where ppd.line_item_id = l_line_item_seq)
951 , cost_sharing_amount =
952 (select nvl(sum(cost_sharing_amount),0)
953 from igw_budget_personnel_details ppd
954 where ppd.line_item_id = l_line_item_seq)
955 where pdb.line_item_id = l_line_item_seq;
956
957
958 END LOOP; --for periods
959 end if;
960
961 END LOOP; --for rec_budget_detail
962
963 for i in 1 .. (l_no_of_periods-1)
964 LOOP
965 select nvl(sum(line_item_cost),0)
966 , nvl(sum(cost_sharing_amount),0)
967 , nvl(sum(underrecovery_amount),0)
968 into l_direct_cost1
969 , l_cost_share1
970 , l_underrecovery
971 from igw_budget_details
972 where proposal_id = p_proposal_id
973 and version_id = p_version_id
974 and budget_period_id = i+1;
975
976 select nvl(sum(calculated_cost_sharing),0)
977 , nvl(sum(calculated_cost),0)
978 into l_cost_share2
979 , l_indirect_cost
980 from igw_budget_details_cal_amts pc
981 where proposal_id = p_proposal_id
982 and version_id = p_version_id
983 and budget_period_id = i+1
984 and pc.rate_class_id = ( select pr.rate_class_id
985 from igw_rate_classes pr
986 where pc.rate_class_id = pr.rate_class_id
987 and pr.rate_class_type = 'O');
988 select nvl(sum(calculated_cost_sharing),0)
989 , nvl(sum(calculated_cost),0)
990 into l_cost_share3
991 , l_direct_cost2
992 from igw_budget_details_cal_amts pc
993 where proposal_id = p_proposal_id
994 and version_id = p_version_id
995 and budget_period_id = i+1
996 and pc.rate_class_id = ( select pr.rate_class_id
997 from igw_rate_classes pr
998 where pc.rate_class_id = pr.rate_class_id
999 and pr.rate_class_type = 'E');
1000
1001 update igw_budget_periods
1002 set total_cost = (l_direct_cost1+l_direct_cost2+l_indirect_cost)
1003 , total_direct_cost = (l_direct_cost1+l_direct_cost2)
1004 , total_indirect_cost = l_indirect_cost
1005 , cost_sharing_amount = (l_cost_share1+l_cost_share2+l_cost_share3)
1006 , underrecovery_amount = l_underrecovery
1007 where proposal_id = p_proposal_id
1008 and version_id = p_version_id
1009 and budget_period_id = i+1;
1010
1011 END LOOP;
1012
1013 select nvl(sum(total_cost),0)
1014 , nvl(sum(total_direct_cost),0)
1015 , nvl(sum(total_indirect_cost),0)
1016 , nvl(sum(cost_sharing_amount),0)
1017 , nvl(sum(underrecovery_amount),0)
1018 , nvl(sum(total_cost_limit),0)
1019 into l_total_cost
1020 , l_total_direct_cost
1021 , l_total_indirect_cost
1022 , l_cost_sharing_amt
1023 , l_underrecovery_amount
1024 , l_total_cost_limit
1025 from igw_budget_periods
1026 where proposal_id = p_proposal_id
1027 and version_id = p_version_id;
1028
1029
1030 update igw_budgets
1031 set total_cost = l_total_cost
1032 , total_direct_cost = l_total_direct_cost
1033 , total_indirect_cost = l_total_indirect_cost
1034 , cost_sharing_amount = l_cost_sharing_amt
1035 , underrecovery_amount = l_underrecovery_amount
1036 , total_cost_limit = l_total_cost_limit
1037 where proposal_id = p_proposal_id
1038 and version_id = p_version_id;
1039
1040 IGW_BUDGET_OPERATIONS.recalculate_budget (
1041 p_proposal_id => p_proposal_id
1042 ,p_version_id => p_version_id
1043 ,p_activity_type_code => p_activity_type_code
1044 ,p_oh_rate_class_id => p_oh_rate_class_id
1045 ,x_return_status => x_return_status
1046 ,x_msg_data => x_msg_data
1047 ,x_msg_count => x_msg_count);
1048
1049 x_return_status := 'S';
1050
1051 /* Persons are not projected if periods are of not equal length. Reason
1052 being, if a person works for 3 months in a period, and if the next period
1053 length is of only 2 months, how do we project that person? Same logic
1054 applies to apply to later periods for expenditure type/category
1055 involving persons */
1056
1057 if l_dummy_value is not null then
1058 l_msg_data := 'IGW_BUDGET_PERIOD_NOT_EQUAL';
1059 l_return_status := 'S';
1060 fnd_message.set_name('IGW', 'IGW_BUDGET_PERIOD_NOT_EQUAL');
1061 fnd_msg_pub.add;
1062 --raised the error volutarily to get the message count
1063 RAISE FND_API.G_EXC_ERROR;
1064 end if;
1065
1066 EXCEPTION
1067 when FND_API.G_EXC_ERROR then
1068 x_return_status := l_return_status;
1069 x_msg_data := l_msg_data;
1070 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1071 p_data => x_msg_data);
1072 when others then
1073 x_return_status := 'U';
1074 x_msg_data := SQLCODE||' '||SQLERRM;
1075 fnd_msg_pub.add_exc_msg(G_PKG_NAME, 'GENERATE_LINES');
1076 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1077 p_data => x_msg_data);
1078 END generate_lines;
1079 ------------------------------------------------------------------------------------------------
1080
1081 PROCEDURE apply_future_periods(p_proposal_id NUMBER
1082 ,p_version_id NUMBER
1083 ,p_budget_period_id NUMBER
1084 ,p_line_item_id NUMBER
1085 ,p_activity_type_code VARCHAR2
1086 ,p_oh_rate_class_id NUMBER
1087 ,x_return_status OUT NOCOPY VARCHAR2
1088 ,x_msg_data OUT NOCOPY VARCHAR2
1089 ,x_msg_count OUT NOCOPY NUMBER) is
1090
1091 cursor c_budget_periods is
1092 select count(*)
1093 from igw_budget_periods
1094 where proposal_id = p_proposal_id
1095 and version_id = p_version_id
1096 and budget_period_id > p_budget_period_id;
1097
1098 cursor c_budget_details is
1099 select pbd.proposal_id
1100 , pbd.version_id
1101 , pbd.expenditure_type
1102 , pbd.budget_category_code
1103 , pbd.expenditure_category_flag
1104 , pbd.line_item_id
1105 , pbd.line_item_description
1106 , pbd.line_item_cost
1107 , pbd.cost_sharing_amount
1108 , pbd.underrecovery_amount
1109 , pbd.apply_inflation_flag
1110 , pbd.budget_justification
1111 , pbd.location_code
1112 , et.personnel_attached_flag
1113 from igw_budget_details pbd
1114 , igw_budget_expenditures_v et
1115 where pbd.expenditure_type = et.budget_expenditure
1116 and pbd.expenditure_category_flag = et.expenditure_category_flag
1117 and pbd.line_item_id = p_line_item_id;
1118
1119
1120 l_line_item_id NUMBER(15);
1121
1122 cursor c_budget_personnel is
1123 select budget_personnel_detail_id
1124 , line_item_id
1125 , person_id
1126 , party_id
1127 , start_date
1128 , end_date
1129 , period_type_code
1130 , appointment_type_code
1131 , salary_requested
1132 , percent_charged
1133 , percent_effort
1134 , cost_sharing_percent
1135 , underrecovery_amount
1136 from igw_budget_personnel_details
1137 where line_item_id = l_line_item_id;
1138
1139 l_dummy_personnel_id NUMBER;
1140 l_no_of_periods NUMBER(10);
1141 l_input_amount NUMBER;
1142 l_base_amount NUMBER;
1143 l_oh_value NUMBER;
1144 l_eb_value NUMBER;
1145 l_oh_value_ov NUMBER;
1146 l_eb_value_ov NUMBER;
1147 l_budget_start_date DATE;
1148 l_budget_end_date DATE;
1149 l_max_budget_end_date DATE;
1150 l_max_budget_start_date DATE;
1151 l_personnel_start_date DATE;
1152 l_personnel_end_date DATE;
1153 l_effective_date DATE;
1154 l_dummy_value VARCHAR2(1);
1155 l_budget_period_id NUMBER(10);
1156 l_rate_class_id_oh NUMBER(15);
1157 l_rate_type_id_oh NUMBER(15);
1158 l_rate_class_id_eb NUMBER(15);
1159 l_rate_type_id_eb NUMBER(15);
1160 l_rate_class_id_oh_d NUMBER(15);
1161 l_rate_type_id_oh_d NUMBER(15);
1162 l_rate_class_id_eb_d NUMBER(15);
1163 l_rate_type_id_eb_d NUMBER(15);
1164 l_rate_class_id_inf NUMBER(15);
1165 l_calculated_percent NUMBER;
1166 l_rate_type_id_inf NUMBER(15);
1167 l_dummy_based_on VARCHAR2(10);
1168 l_dummy_line_item_cost NUMBER;
1169 l_apply_rate_flag_oh VARCHAR2(1);
1170 l_apply_rate_flag_eb VARCHAR2(1);
1171 l_inflated_salary NUMBER;
1172 l_inflated_salary_ov NUMBER;
1173 l_line_item_seq NUMBER;
1174 l_direct_cost1 NUMBER;
1175 l_direct_cost2 NUMBER;
1176 l_cost_share1 NUMBER;
1177 l_cost_share2 NUMBER;
1178 l_cost_share3 NUMBER;
1179 l_underrecovery NUMBER;
1180 l_indirect_cost NUMBER;
1181 l_total_cost NUMBER;
1182 l_total_direct_cost NUMBER;
1183 l_total_indirect_cost NUMBER;
1184 l_cost_sharing_amt NUMBER;
1185 l_underrecovery_amount NUMBER;
1186 l_calculation_base NUMBER;
1187 l_total_cost_limit NUMBER;
1188 l_calculated_cost_share NUMBER;
1189 l_calculated_cost_share_ov NUMBER;
1190 l_appointment_type_code VARCHAR2(30);
1191 l_return_status VARCHAR2(1);
1192 l_msg_data VARCHAR2(200);
1193 l_msg_count NUMBER(10);
1194 BEGIN
1195 fnd_msg_pub.initialize;
1196 open c_budget_periods;
1197 fetch c_budget_periods into l_no_of_periods;
1198 close c_budget_periods;
1199
1200 for rec_budget_details in c_budget_details
1201 LOOP
1202 l_rate_class_id_oh := p_oh_rate_class_id;
1203 IGW_OVERHEAD_CAL.get_rate_id(rec_budget_details.expenditure_type
1204 ,rec_budget_details.expenditure_category_flag
1205 , 'O'
1206 ,l_rate_class_id_oh
1207 ,l_rate_type_id_oh
1208 ,l_return_status
1209 ,l_msg_data);
1210
1211 if l_return_status <> 'S' then
1212 raise FND_API.G_EXC_ERROR;
1213 end if;
1214
1215
1216 if rec_budget_details.personnel_attached_flag = 'N' then
1217
1218 if rec_budget_details.apply_inflation_flag = 'Y' then
1219 IGW_OVERHEAD_CAL.get_rate_id(rec_budget_details.expenditure_type
1220 ,rec_budget_details.expenditure_category_flag
1221 ,'I'
1222 , l_rate_class_id_inf
1223 ,l_rate_type_id_inf
1224 ,l_return_status
1225 ,l_msg_data);
1226 if l_return_status <> 'S' then
1227 raise FND_API.G_EXC_ERROR;
1228 end if;
1229 end if;
1230 BEGIN
1231 select apply_rate_flag
1232 into l_apply_rate_flag_oh
1233 from igw_budget_details_cal_amts
1234 where line_item_id = rec_budget_details.line_item_id;
1235 EXCEPTION
1236 when no_data_found then null;
1237 End;
1238
1239 BEGIN
1240 select distinct based_on_line_item
1241 into l_dummy_based_on
1242 from igw_budget_details
1243 where based_on_line_item = rec_budget_details.line_item_id;
1244 EXCEPTION
1245 when no_data_found then null;
1246 End;
1247
1248
1249 if l_dummy_based_on is not null then
1250 delete from igw_budget_details_cal_amts
1251 where line_item_id IN (select line_item_id
1252 from igw_budget_details pd
1253 where pd.based_on_line_item = l_dummy_based_on);
1254
1255 delete from igw_budget_details
1256 where based_on_line_item = l_dummy_based_on;
1257 end if;
1258
1259 l_input_amount := rec_budget_details.line_item_cost;
1260 for i in (p_budget_period_id+1) .. (p_budget_period_id+l_no_of_periods)
1261 LOOP
1262 begin
1263 select start_date, end_date
1264 into l_budget_start_date
1265 , l_budget_end_date
1266 from igw_budget_periods
1267 where proposal_id = p_proposal_id
1268 and version_id = p_version_id
1269 and budget_period_id = i;
1270 exception
1271 when no_data_found then
1272 l_msg_data := 'IGW_PERIOD_NOT_CONSECUTIVE';
1273 l_return_status := 'E';
1274 fnd_message.set_name('IGW', 'IGW_PERIOD_NOT_CONSECUTIVE');
1275 fnd_msg_pub.add;
1276 RAISE FND_API.G_EXC_ERROR;
1277 end;
1278
1279
1280
1281 if rec_budget_details.apply_inflation_flag = 'Y' then
1282
1283 IGW_OVERHEAD_CAL.calc_inflation(p_proposal_id
1284 ,p_version_id
1285 ,l_input_amount
1286 ,l_budget_start_date
1287 ,l_budget_end_date
1288 ,l_base_amount
1289 ,p_activity_type_code
1290 ,rec_budget_details.location_code
1291 ,l_rate_class_id_inf
1292 ,l_rate_type_id_inf
1293 ,l_return_status
1294 ,l_msg_data
1295 ,l_msg_count);
1296
1297 if l_return_status <> 'S' then
1298 raise FND_API.G_EXC_ERROR;
1299 end if;
1300 else
1301 l_base_amount := rec_budget_details.line_item_cost;
1302 end if;
1303
1304 l_input_amount := l_base_amount;
1305
1306 IGW_OVERHEAD_CAL.calc_oh(p_proposal_id
1307 ,p_version_id
1308 ,l_base_amount
1309 ,l_budget_start_date
1310 ,l_budget_end_date
1311 ,l_oh_value
1312 ,l_oh_value_ov
1313 ,p_activity_type_code
1314 ,rec_budget_details.location_code
1315 ,l_rate_class_id_oh
1316 ,l_rate_type_id_oh
1317 ,l_return_status
1318 ,l_msg_data
1319 ,l_msg_count);
1320
1321 if l_return_status <> 'S' then
1322 raise FND_API.G_EXC_ERROR;
1323 end if;
1324
1325 if l_apply_rate_flag_oh = 'N' then
1326 l_oh_value_ov := 0;
1327
1328 elsif l_apply_rate_flag_oh = 'Y' then
1329
1330 IGW_OVERHEAD_CAL.calc_oh(p_proposal_id
1331 ,p_version_id
1332 ,rec_budget_details.cost_sharing_amount
1333 ,l_budget_start_date
1334 ,l_budget_end_date
1335 ,l_calculated_cost_share
1336 ,l_calculated_cost_share_ov
1337 ,p_activity_type_code
1338 ,rec_budget_details.location_code
1339 ,l_rate_class_id_oh
1340 ,l_rate_type_id_oh
1341 ,l_return_status
1342 ,l_msg_data
1343 ,l_msg_count);
1344 if l_return_status <> 'S' then
1345 raise FND_API.G_EXC_ERROR;
1346 end if;
1347 end if;
1348
1349 create_budget_detail (
1350 rec_budget_details.proposal_id
1351 ,rec_budget_details.version_id
1352 ,i
1353 ,l_line_item_id
1354 ,rec_budget_details.expenditure_type
1355 ,rec_budget_details.budget_category_code
1356 ,rec_budget_details.expenditure_category_flag
1357 ,rec_budget_details.line_item_description
1358 ,rec_budget_details.line_item_id
1359 ,l_base_amount
1360 ,rec_budget_details.cost_sharing_amount
1361 ,(l_oh_value - l_oh_value_ov)
1362 ,rec_budget_details.apply_inflation_flag
1363 ,rec_budget_details.budget_justification
1364 ,rec_budget_details.location_code);
1365
1366
1367
1368
1369 if l_rate_class_id_oh is not null and l_rate_type_id_oh is not null then
1370 insert into igw_budget_details_cal_amts (
1371 proposal_id
1372 ,version_id
1373 ,budget_period_id
1374 ,line_item_id
1375 ,rate_class_id
1376 ,rate_type_id
1377 ,apply_rate_flag
1378 ,calculated_cost
1379 ,calculated_cost_sharing
1380 ,last_update_date
1381 ,last_updated_by
1382 ,creation_date
1383 ,created_by
1384 ,last_update_login)
1385 values (
1386 rec_budget_details.proposal_id
1387 ,rec_budget_details.version_id
1388 ,i
1389 ,igw_budget_details_s.currval
1390 ,l_rate_class_id_oh
1391 ,l_rate_type_id_oh
1392 ,l_apply_rate_flag_oh
1393 ,l_oh_value
1394 ,l_calculated_cost_share
1395 ,sysdate
1396 ,fnd_global.user_id
1397 ,sysdate
1398 ,fnd_global.user_id
1399 ,fnd_global.login_id);
1400
1401 end if;
1402 END LOOP;
1403 elsif rec_budget_details.personnel_attached_flag = 'Y' then
1404 IGW_OVERHEAD_CAL.get_rate_id(rec_budget_details.expenditure_type
1405 ,rec_budget_details.expenditure_category_flag
1406 ,'E'
1407 ,l_rate_class_id_eb,l_rate_type_id_eb,l_return_status, l_msg_data);
1408
1409 if l_return_status <> 'S' then
1410 raise FND_API.G_EXC_ERROR;
1411 end if;
1412 l_input_amount := rec_budget_details.line_item_cost;
1413
1414 select max(end_date), max(start_date)
1415 into l_max_budget_end_date, l_max_budget_start_date
1416 from igw_budget_periods
1417 where proposal_id = p_proposal_id
1418 and version_id = p_version_id;
1419
1420 l_line_item_id := rec_budget_details.line_item_id;
1421 l_budget_period_id := p_budget_period_id;
1422
1423 BEGIN
1424 select '1'
1425 into l_dummy_value
1426 from igw_budget_periods
1427 where ((end_date-start_date) < 364 or (end_date-start_date) > 365)
1428 --and start_date <> l_max_budget_start_date
1429 and proposal_id = p_proposal_id
1430 and version_id = p_version_id
1431 and rownum <2;
1432 EXCEPTION
1433 when no_data_found then
1434 null;
1435 END;
1436 BEGIN
1437 select distinct based_on_line_item
1438 into l_dummy_based_on
1439 from igw_budget_details
1440 where based_on_line_item = rec_budget_details.line_item_id;
1441
1442 EXCEPTION
1443 when no_data_found then null;
1444 End;
1445
1446 if l_dummy_based_on is not null then
1447
1448 delete from igw_budget_personnel_cal_amts
1449 where budget_personnel_detail_id IN (select budget_personnel_detail_id
1450 from igw_budget_details pd
1451 , igw_budget_personnel_details ppd
1452 where pd.line_item_id = ppd.line_item_id
1453 and pd.based_on_line_item = l_dummy_based_on);
1454
1455 delete from igw_budget_personnel_details
1456 where line_item_id IN (select line_item_id
1457 from igw_budget_details pd
1458 where pd.based_on_line_item = l_dummy_based_on);
1459
1460 delete from igw_budget_details_cal_amts
1461 where line_item_id IN (select line_item_id
1462 from igw_budget_details pd
1463 where pd.based_on_line_item = l_dummy_based_on);
1464
1465 delete from igw_budget_details
1466 where based_on_line_item = l_dummy_based_on;
1467 end if;
1468
1469 for i in (p_budget_period_id+1) .. (p_budget_period_id+l_no_of_periods)
1470 LOOP
1471 begin
1472 select start_date, end_date
1473 into l_budget_start_date
1474 , l_budget_end_date
1475 from igw_budget_periods
1476 where proposal_id = p_proposal_id
1477 and version_id = p_version_id
1478 and budget_period_id = i;
1479 exception
1480 when no_data_found then
1481 l_msg_data := 'IGW_PERIOD_NOT_CONSECUTIVE';
1482 l_return_status := 'E';
1483 fnd_message.set_name('IGW', 'IGW_PERIOD_NOT_CONSECUTIVE');
1484 fnd_msg_pub.add;
1485 RAISE FND_API.G_EXC_ERROR;
1486 end;
1487
1488
1489 create_budget_detail (
1490 rec_budget_details.proposal_id
1491 ,rec_budget_details.version_id
1492 ,i
1493 ,l_line_item_id
1494 ,rec_budget_details.expenditure_type
1495 ,rec_budget_details.budget_category_code
1496 ,rec_budget_details.expenditure_category_flag
1497 ,rec_budget_details.line_item_description
1498 ,rec_budget_details.line_item_id
1499 ,0
1500 ,0
1501 ,rec_budget_details.underrecovery_amount
1502 ,rec_budget_details.apply_inflation_flag
1503 ,rec_budget_details.budget_justification
1504 ,rec_budget_details.location_code);
1505
1506 if l_rate_class_id_oh is not null and l_rate_type_id_oh is not null then
1507 insert into igw_budget_details_cal_amts (
1508 proposal_id
1509 ,version_id
1510 ,budget_period_id
1511 ,line_item_id
1512 ,rate_class_id
1513 ,rate_type_id
1514 ,apply_rate_flag
1515 ,calculated_cost
1516 ,calculated_cost_sharing
1517 ,last_update_date
1518 ,last_updated_by
1519 ,creation_date
1520 ,created_by
1521 ,last_update_login)
1522 values (
1523 rec_budget_details.proposal_id
1524 ,rec_budget_details.version_id
1525 ,i
1526 ,igw_budget_details_s.currval
1527 ,l_rate_class_id_oh
1528 ,l_rate_type_id_oh
1529 ,'Y'
1530 ,0
1531 ,0
1532 ,sysdate
1533 ,fnd_global.user_id
1534 ,sysdate
1535 ,fnd_global.user_id
1536 ,fnd_global.login_id);
1537 end if;
1538
1539 if l_rate_class_id_eb is not null and l_rate_type_id_eb is not null then
1540 insert into igw_budget_details_cal_amts (
1541 proposal_id
1542 ,version_id
1543 ,budget_period_id
1544 ,line_item_id
1545 ,rate_class_id
1546 ,rate_type_id
1547 ,apply_rate_flag
1548 ,calculated_cost
1549 ,calculated_cost_sharing
1550 ,last_update_date
1551 ,last_updated_by
1552 ,creation_date
1553 ,created_by
1554 ,last_update_login)
1555 values (
1556 rec_budget_details.proposal_id
1557 ,rec_budget_details.version_id
1558 ,i
1559 ,igw_budget_details_s.currval
1560 ,l_rate_class_id_eb
1561 ,l_rate_type_id_eb
1562 ,'Y'
1563 ,0
1564 ,0
1565 ,sysdate
1566 ,fnd_global.user_id
1567 ,sysdate
1568 ,fnd_global.user_id
1569 ,fnd_global.login_id);
1570 end if;
1571
1572
1573 if l_dummy_value is null then
1574 for rec_budget_personnel in c_budget_personnel
1575 LOOP
1576
1577 BEGIN
1578 select pca.apply_rate_flag
1579 into l_apply_rate_flag_oh
1580 from igw_budget_personnel_cal_amts pca
1581 , igw_budget_personnel_details pbd
1582 where pbd.budget_personnel_detail_id = rec_budget_personnel.budget_personnel_detail_id
1583 and pca.budget_personnel_detail_id = pbd.budget_personnel_detail_id
1584 and pca.rate_class_id = ( select pr.rate_class_id
1585 from igw_rate_classes pr
1586 where pca.rate_class_id = pr.rate_class_id
1587 and pr.rate_class_type = 'O');
1588 EXCEPTION
1589 when no_data_found then null;
1590 End;
1591
1592 BEGIN
1593 select pca.apply_rate_flag
1594 into l_apply_rate_flag_eb
1595 from igw_budget_personnel_cal_amts pca
1596 , igw_budget_personnel_details pbd
1597 where pbd.budget_personnel_detail_id = rec_budget_personnel.budget_personnel_detail_id
1598 and pca.budget_personnel_detail_id = pbd.budget_personnel_detail_id
1599 and pca.rate_class_id = ( select pr.rate_class_id
1600 from igw_rate_classes pr
1601 where pca.rate_class_id = pr.rate_class_id
1602 and pr.rate_class_type = 'E');
1603 EXCEPTION
1604 when no_data_found then null;
1605 End;
1606 l_personnel_start_date:= add_months(rec_budget_personnel.start_date,
1607 12*(i-p_budget_period_id));
1608
1609 if i = (p_budget_period_id+l_no_of_periods) then
1610 if add_months(rec_budget_personnel.end_date, 12*(i-p_budget_period_id)) > l_budget_end_date then
1611 l_personnel_end_date := l_budget_end_date;
1612 else
1613 l_personnel_end_date := add_months(rec_budget_personnel.end_date, 12*(i-p_budget_period_id));
1614 end if;
1615 else
1616 l_personnel_end_date := add_months(rec_budget_personnel.end_date,
1617 12*(i-p_budget_period_id));
1618 end if;
1619
1620 ---get base amount
1621 select calculation_base
1622 , effective_date
1623 , appointment_type_code
1624 into l_calculation_base
1625 , l_effective_date
1626 , l_appointment_type_code
1627 from igw_budget_persons
1628 where proposal_id = p_proposal_id
1629 and version_id = p_version_id
1630 --and person_id = rec_budget_personnel.person_id
1631 and party_id = rec_budget_personnel.party_id
1632 and appointment_type_code = rec_budget_personnel.appointment_type_code;
1633
1634
1635 IGW_OVERHEAD_CAL.calc_salary(p_proposal_id
1636 ,p_version_id
1637 ,l_calculation_base
1638 ,l_effective_date
1639 ,l_appointment_type_code
1640 ,l_personnel_start_date
1641 ,l_personnel_end_date
1642 ,l_inflated_salary
1643 ,l_inflated_salary_ov
1644 ,rec_budget_details.expenditure_type
1645 ,rec_budget_details.expenditure_category_flag
1646 ,p_activity_type_code
1647 ,rec_budget_details.location_code
1648 ,l_return_status
1649 ,l_msg_data
1650 ,l_msg_count);
1651 if l_return_status <> 'S' then
1652 raise FND_API.G_EXC_ERROR;
1653 end if;
1654 l_inflated_salary := rec_budget_personnel.percent_charged/100 * l_inflated_salary;
1655
1656 if l_apply_rate_flag_oh = 'N' then
1657 l_rate_class_id_oh_d := null;
1658 l_rate_type_id_oh_d := null;
1659 else
1660 l_rate_class_id_oh_d := l_rate_class_id_oh;
1661 l_rate_type_id_oh_d := l_rate_type_id_oh;
1662 end if;
1663 if l_apply_rate_flag_eb = 'N' then
1664 l_rate_class_id_eb_d := null;
1665 l_rate_type_id_eb_d := null;
1666 else
1667 l_rate_class_id_eb_d := l_rate_class_id_eb;
1668 l_rate_type_id_eb_d := l_rate_type_id_eb;
1669 end if;
1670
1671 IGW_OVERHEAD_CAL.calc_oh_eb (p_proposal_id
1672 ,p_version_id
1673 ,l_inflated_salary
1674 ,l_personnel_start_date
1675 ,l_personnel_end_date
1676 ,l_oh_value
1677 ,l_oh_value_ov
1678 ,l_eb_value
1679 ,l_eb_value_ov
1680 ,p_activity_type_code
1681 ,rec_budget_details.location_code
1682 ,l_rate_class_id_oh_d
1683 ,l_rate_type_id_oh_d
1684 ,l_rate_class_id_eb_d
1685 ,l_rate_type_id_eb_d
1686 ,l_return_status
1687 ,l_msg_data
1688 ,l_msg_count);
1689 if l_return_status <> 'S' then
1690 raise FND_API.G_EXC_ERROR;
1691 end if;
1692
1693 insert into igw_budget_personnel_details (
1694 budget_personnel_detail_id
1695 ,proposal_id
1696 ,version_id
1697 ,budget_period_id
1698 ,line_item_id
1699 ,person_id
1700 ,party_id
1701 ,start_date
1702 ,end_date
1703 ,period_type_code
1704 ,appointment_type_code
1705 ,salary_requested
1706 ,percent_charged
1707 ,percent_effort
1708 ,cost_sharing_percent
1709 ,cost_sharing_amount
1710 ,underrecovery_amount
1711 ,last_update_date
1712 ,last_updated_by
1713 ,creation_date
1714 ,created_by
1715 ,last_update_login)
1716 values (
1717 igw_budget_personnel_s.nextval
1718 ,rec_budget_details.proposal_id
1719 ,rec_budget_details.version_id
1720 ,i
1721 ,igw_budget_details_s.currval
1722 ,rec_budget_personnel.person_id
1723 ,rec_budget_personnel.party_id
1724 ,l_personnel_start_date
1725 ,l_personnel_end_date
1726 ,rec_budget_personnel.period_type_code
1727 ,rec_budget_personnel.appointment_type_code
1728 ,l_inflated_salary
1729 ,rec_budget_personnel.percent_charged
1730 ,rec_budget_personnel.percent_effort
1731 ,rec_budget_personnel.cost_sharing_percent
1732 ,rec_budget_personnel.cost_sharing_percent/100 * l_inflated_salary
1733 ,rec_budget_personnel.underrecovery_amount
1734 ,sysdate
1735 ,fnd_global.user_id
1736 ,sysdate
1737 ,fnd_global.user_id
1738 ,fnd_global.login_id);
1739
1740 select igw_budget_personnel_s.currval into l_dummy_personnel_id from dual;
1741 if l_rate_class_id_oh is not null and l_rate_type_id_oh is not null then
1742 create_budget_personnel_amts (
1743 l_dummy_personnel_id
1744 ,l_rate_class_id_oh
1745 ,l_rate_type_id_oh
1746 ,l_apply_rate_flag_oh
1747 ,l_oh_value
1748 ,rec_budget_personnel.cost_sharing_percent/100 * l_oh_value);
1749 end if;
1750
1751 if l_rate_class_id_eb is not null and l_rate_type_id_eb is not null then
1752 create_budget_personnel_amts (
1753 l_dummy_personnel_id
1754 ,l_rate_class_id_eb
1755 ,l_rate_type_id_eb
1756 ,l_apply_rate_flag_eb
1757 ,l_eb_value
1758 ,rec_budget_personnel.cost_sharing_percent/100 * l_eb_value);
1759 end if;
1760
1761 END LOOP; --for rec_personnel_budget
1762 end if;
1763
1764 select igw_budget_details_s.currval
1765 into l_line_item_seq
1766 from dual;
1767
1768 update igw_budget_details_cal_amts pdc
1769 set pdc.calculated_cost =
1770 (select nvl(sum(ppc.calculated_cost),0)
1771 from igw_budget_personnel_cal_amts ppc
1772 , igw_budget_personnel_details ppd
1773 , igw_budget_details pd
1774 where pd.line_item_id = ppd.line_item_id
1775 and ppd.budget_personnel_detail_id =ppc.budget_personnel_detail_id
1776 and pd.proposal_id = p_proposal_id
1777 and pd.version_id = p_version_id
1778 and pd.budget_period_id = i
1779 and pd.line_item_id = l_line_item_seq
1780 and ppc.rate_class_id = pdc.rate_class_id
1781 and ppc.rate_type_id = pdc.rate_type_id)
1782 , pdc.calculated_cost_sharing =
1783 (select nvl(sum(ppc.calculated_cost_sharing),0)
1784 from igw_budget_personnel_cal_amts ppc
1785 , igw_budget_personnel_details ppd
1786 , igw_budget_details pd
1787 where pd.line_item_id = ppd.line_item_id
1788 and ppd.budget_personnel_detail_id = ppc.budget_personnel_detail_id
1789 and pd.proposal_id = p_proposal_id
1790 and pd.version_id = p_version_id
1791 and pd.budget_period_id = i
1792 and pd.line_item_id = l_line_item_seq
1793 and ppc.rate_class_id = pdc.rate_class_id
1794 and ppc.rate_type_id = pdc.rate_type_id)
1795 where pdc.line_item_id = l_line_item_seq;
1796
1797
1798 update igw_budget_details pdb
1799 set line_item_cost =
1800 (select nvl(sum(salary_requested),0)
1801 from igw_budget_personnel_details ppd
1802 where ppd.line_item_id = l_line_item_seq)
1803 , cost_sharing_amount =
1804 (select nvl(sum(cost_sharing_amount),0)
1805 from igw_budget_personnel_details ppd
1806 where ppd.line_item_id = l_line_item_seq)
1807 where pdb.line_item_id = l_line_item_seq;
1808
1809
1810 END LOOP; --for periods
1811 end if;
1812
1813 END LOOP; --for rec_budget_detail
1814
1815 for i in (p_budget_period_id+1) .. (p_budget_period_id+l_no_of_periods)
1816 LOOP
1817 select nvl(sum(line_item_cost),0)
1818 , nvl(sum(cost_sharing_amount),0)
1819 , nvl(sum(underrecovery_amount),0)
1820 into l_direct_cost1
1821 , l_cost_share1
1822 , l_underrecovery
1823 from igw_budget_details
1824 where proposal_id = p_proposal_id
1825 and version_id = p_version_id
1826 and budget_period_id = i;
1827
1828 select nvl(sum(calculated_cost_sharing),0)
1829 , nvl(sum(calculated_cost),0)
1830 into l_cost_share2
1831 , l_indirect_cost
1832 from igw_budget_details_cal_amts pc
1833 where proposal_id = p_proposal_id
1834 and version_id = p_version_id
1835 and budget_period_id = i
1836 and pc.rate_class_id = ( select pr.rate_class_id
1837 from igw_rate_classes pr
1838 where pc.rate_class_id = pr.rate_class_id
1839 and pr.rate_class_type = 'O');
1840 select nvl(sum(calculated_cost_sharing),0)
1841 , nvl(sum(calculated_cost),0)
1842 into l_cost_share3
1843 , l_direct_cost2
1844 from igw_budget_details_cal_amts pc
1845 where proposal_id = p_proposal_id
1846 and version_id = p_version_id
1847 and budget_period_id = i
1848 and pc.rate_class_id = ( select pr.rate_class_id
1849 from igw_rate_classes pr
1850 where pc.rate_class_id = pr.rate_class_id
1851 and pr.rate_class_type = 'E');
1852
1853 update igw_budget_periods
1854 set total_cost = (l_direct_cost1+l_direct_cost2+l_indirect_cost)
1855 , total_direct_cost = (l_direct_cost1+l_direct_cost2)
1856 , total_indirect_cost = l_indirect_cost
1857 , cost_sharing_amount = (l_cost_share1+l_cost_share2+l_cost_share3)
1858 , underrecovery_amount = l_underrecovery
1859 where proposal_id = p_proposal_id
1860 and version_id = p_version_id
1861 and budget_period_id = i;
1862
1863 END LOOP;
1864
1865 select nvl(sum(total_cost),0)
1866 , nvl(sum(total_direct_cost),0)
1867 , nvl(sum(total_indirect_cost),0)
1868 , nvl(sum(cost_sharing_amount),0)
1869 , nvl(sum(underrecovery_amount),0)
1870 , nvl(sum(total_cost_limit),0)
1871 into l_total_cost
1872 , l_total_direct_cost
1873 , l_total_indirect_cost
1874 , l_cost_sharing_amt
1875 , l_underrecovery_amount
1876 , l_total_cost_limit
1877 from igw_budget_periods
1878 where proposal_id = p_proposal_id
1879 and version_id = p_version_id;
1880
1881
1882 update igw_budgets
1883 set total_cost = l_total_cost
1884 , total_direct_cost = l_total_direct_cost
1885 , total_indirect_cost = l_total_indirect_cost
1886 , cost_sharing_amount = l_cost_sharing_amt
1887 , underrecovery_amount = l_underrecovery_amount
1888 , total_cost_limit = l_total_cost_limit
1889 where proposal_id = p_proposal_id
1890 and version_id = p_version_id;
1891
1892
1893 x_return_status := 'S';
1894
1895 IGW_BUDGET_OPERATIONS.recalculate_budget (
1896 p_proposal_id => p_proposal_id
1897 ,p_version_id => p_version_id
1898 ,p_activity_type_code => p_activity_type_code
1899 ,p_oh_rate_class_id => p_oh_rate_class_id
1900 ,x_return_status => x_return_status
1901 ,x_msg_data => x_msg_data
1902 ,x_msg_count => x_msg_count);
1903
1904 /* Persons are not projected if periods are of not equal length. Reason
1905 being, if a person works for 3 months in a period, and if the next period
1906 length is of only 2 months, how do we project that person? Same logic
1907 applies to apply to later periods for expenditure type/category
1908 involving persons */
1909
1910 if l_dummy_value is not null then
1911 l_msg_data := 'IGW_BUDGET_PERIOD_NOT_EQUAL';
1912 l_return_status := 'S';
1913 fnd_message.set_name('IGW', 'IGW_BUDGET_PERIOD_NOT_EQUAL');
1914 fnd_msg_pub.add;
1915 --raised the error volutarily to get the message count
1916 RAISE FND_API.G_EXC_ERROR;
1917 end if;
1918
1919 EXCEPTION
1920 when FND_API.G_EXC_ERROR then
1921 x_return_status := l_return_status;
1922 x_msg_data := l_msg_data;
1923 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1924 p_data => x_msg_data);
1925 when others then
1926 x_return_status := 'U';
1927 x_msg_data := SQLCODE||' '||SQLERRM;
1928 fnd_msg_pub.add_exc_msg(G_PKG_NAME, 'APPLY_FUTURE_PERIODS');
1929 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1930 p_data => x_msg_data);
1931 END apply_future_periods;
1932 ----------------------------------------------------------------------------------------------
1933
1934 PROCEDURE sync_to_cost_limit( p_proposal_id NUMBER
1935 ,p_version_id NUMBER
1936 ,p_budget_period_id NUMBER
1937 ,p_line_item_id NUMBER
1938 ,p_activity_type_code VARCHAR2
1939 ,p_line_item_cost NUMBER
1940 ,p_total_cost_limit NUMBER
1941 ,x_line_item_cost OUT NOCOPY NUMBER
1942 ,x_calculated_cost OUT NOCOPY NUMBER
1943 ,x_return_status OUT NOCOPY VARCHAR2
1944 ,x_msg_data OUT NOCOPY VARCHAR2
1945 ,x_msg_count OUT NOCOPY NUMBER) is
1946
1947 cursor c_budget_periods is
1948 select total_cost
1949 from igw_budget_periods
1950 where proposal_id = p_proposal_id
1951 and version_id = p_version_id
1952 and budget_period_id = p_budget_period_id;
1953
1954 l_line_item_cost NUMBER;
1955 l_total_cost NUMBER;
1956 l_calculated_cost NUMBER;
1957 l_diff_amount NUMBER;
1958 l_oh_percent NUMBER;
1959 l_return_status VARCHAR2(1);
1960 l_msg_data VARCHAR2(200);
1961 l_msg_count NUMBER(10);
1962
1963 insufficient_amount EXCEPTION;
1964
1965 BEGIN
1966 fnd_msg_pub.initialize;
1967 open c_budget_periods;
1968 fetch c_budget_periods into l_total_cost;
1969 close c_budget_periods;
1970
1971 begin
1972 select calculated_cost
1973 into l_calculated_cost
1974 from igw_budget_details_cal_amts
1975 where line_item_id = p_line_item_id;
1976 exception
1977 when no_data_found then null;
1978 end;
1979
1980 l_diff_amount := nvl(p_total_cost_limit,0) + nvl(p_line_item_cost,0) +
1981 nvl(l_calculated_cost,0) - nvl(l_total_cost,0);
1982
1983 --dbms_output.put_line('l_diff_amount'||l_diff_amount);
1984 --dbms_output.put_line('(p_total_cost_limit'||p_total_cost_limit);
1985 --dbms_output.put_line('p_line_item_cost'||p_line_item_cost);
1986 --dbms_output.put_line('l_calculated_cost'||l_calculated_cost);
1987 --dbms_output.put_line('l_total_cost'||l_total_cost);
1988
1989 if l_diff_amount < 0 then
1990 raise insufficient_amount;
1991 null;
1992 elsif l_diff_amount >= 0 then
1993 if p_line_item_cost = 0 then
1994 l_oh_percent := 0;
1995 else
1996 l_oh_percent := nvl(l_calculated_cost,0)/p_line_item_cost;
1997 end if;
1998 --dbms_output.put_line('l_oh_percent'||l_oh_percent);
1999 x_line_item_cost := l_diff_amount/(1+l_oh_percent);
2000 x_calculated_cost:= l_diff_amount - x_line_item_cost;
2001 end if;
2002 x_return_status := 'S';
2003
2004 EXCEPTION
2005 when FND_API.G_EXC_ERROR then
2006 x_return_status := l_return_status;
2007 x_msg_data := l_msg_data;
2008 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2009 p_data => x_msg_data);
2010
2011 when insufficient_amount then
2012 x_return_status := 'E';
2013 x_msg_data := 'IGW_INSUFFICIENT_AMOUNT';
2014 fnd_message.set_name('IGW', 'IGW_INSUFFICIENT_AMOUNT');
2015 --dbms_output.put_line('FIRING THE FOLLOWING'||'IGW_INSUFFICIENT_AMOUNT');
2016 fnd_msg_pub.add;
2017 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2018 p_data => x_msg_data);
2019 when others then
2020 x_return_status := 'U';
2021 x_msg_data := SQLCODE||' '||SQLERRM;
2022 fnd_msg_pub.add_exc_msg(G_PKG_NAME, 'SYNC_TO_COST_LIMIT');
2023 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2024
2025 END sync_to_cost_limit;
2026 ------------------------------------------------------------------------------------------
2027
2028 PROCEDURE sync_to_cost_limit_wrap(
2029 p_line_item_id NUMBER
2030 ,p_line_item_cost NUMBER
2031 ,x_return_status OUT NOCOPY VARCHAR2
2032 ,x_msg_data OUT NOCOPY VARCHAR2
2033 ,x_msg_count OUT NOCOPY NUMBER) is
2034
2035 l_proposal_id NUMBER;
2036 l_version_id NUMBER;
2037 l_budget_period_id NUMBER;
2038
2039 l_activity_type_code VARCHAR2(30);
2040 l_expenditure_type VARCHAR2(30);
2041 l_period_cost_limit NUMBER;
2042 l_new_line_item_cost NUMBER;
2043 l_new_calculated_cost NUMBER;
2044
2045 l_expenditure_category_flag VARCHAR2(1);
2046 l_personnel_attached_flag VARCHAR2(1);
2047
2048
2049 l_return_status VARCHAR2(1);
2050 l_msg_data VARCHAR2(200);
2051 l_msg_count NUMBER(10);
2052 l_msg_index_out NUMBER;
2053
2054 cursor c_budget is
2055 select proposal_id
2056 , version_id
2057 , budget_period_id
2058 , expenditure_category_flag
2059 , expenditure_type
2060 from igw_budget_details
2061 where line_item_id = p_line_item_id;
2062 Begin
2063 x_return_status := 'S';
2064
2065 --fnd_msg_pub.initialize;
2066
2067 open c_budget;
2068 fetch c_budget into l_proposal_id, l_version_id
2069 , l_budget_period_id, l_expenditure_category_flag, l_expenditure_type;
2070 close c_budget;
2071
2072
2073 if l_expenditure_category_flag = 'Y' then
2074 begin
2075 select personnel_attached_flag
2076 into l_personnel_attached_flag
2077 from igw_expenditure_categories_v
2078 where expenditure_category = l_expenditure_type;
2079 exception
2080 when no_data_found then
2081 null;
2082 end;
2083 elsif l_expenditure_category_flag = 'N' then
2084 begin
2085 select personnel_attached_flag
2086 into l_personnel_attached_flag
2087 from igw_expenditure_categories_v
2088 where expenditure_category = (select expenditure_category from igw_expenditure_types_v
2089 where expenditure_type = l_expenditure_type);
2090 exception
2091 when no_data_found then
2092 null;
2093 end;
2094 end if;
2095
2096 if l_personnel_attached_flag = 'Y' then
2097 fnd_message.set_name('IGW', 'IGW_NO_PERSONNEL_SYNC');
2098 fnd_msg_pub.add;
2099 Raise FND_API.G_EXC_ERROR;
2100 end if;
2101
2102 begin
2103 select activity_type_code
2104 into l_activity_type_code
2105 from igw_proposals_all
2106 where proposal_id = l_proposal_id;
2107 exception
2108 when others then
2109 RAISE;
2110 end;
2111
2112 begin
2113 select total_cost_limit
2114 into l_period_cost_limit
2115 from igw_budget_periods
2116 where proposal_id = l_proposal_id
2117 and version_id = l_version_id
2118 and budget_period_id = l_budget_period_id;
2119 exception
2120 when others then
2121 RAISE;
2122 end;
2123
2124
2125 --dbms_output.put_line('proposal_id'||l_proposal_id);
2126 --dbms_output.put_line('l_version_id'||l_version_id);
2127 --dbms_output.put_line('l_budget_period_id'||l_budget_period_id);
2128 --dbms_output.put_line('p_line_item_id'||p_line_item_id);
2129 --dbms_output.put_line('l_activity_type_code'||l_activity_type_code);
2130 --dbms_output.put_line('p_line_item_cost'||p_line_item_cost);
2131 --dbms_output.put_line('l_period_cost_limit'||l_period_cost_limit);
2132
2133
2134
2135 igw_generate_periods.sync_to_cost_limit(
2136 p_proposal_id => l_proposal_id
2137 ,p_version_id => l_version_id
2138 ,p_budget_period_id => l_budget_period_id
2139 ,p_line_item_id => p_line_item_id
2140 ,p_activity_type_code => l_activity_type_code
2141 ,p_line_item_cost => p_line_item_cost
2142 ,p_total_cost_limit => l_period_cost_limit
2143 ,x_line_item_cost => l_new_line_item_cost
2144 ,x_calculated_cost => l_new_calculated_cost
2145 ,x_return_status => l_return_status
2146 ,x_msg_data => l_msg_data
2147 ,x_msg_count => x_msg_count);
2148
2149 --dbms_output.put_line('l_new_line_item_cost'||l_new_line_item_cost);
2150 --dbms_output.put_line('l_new_calculated_cost'||l_new_calculated_cost);
2151 --dbms_output.put_line('l_msg_data'||l_msg_data);
2152 --dbms_output.put_line('l_return_status'||l_return_status);
2153 --dbms_output.put_line('x_msg_count'||x_msg_count);
2154
2155 x_return_status := l_return_status;
2156
2157 If x_msg_count > 0 THEN
2158 If x_msg_count = 1 THEN
2159 fnd_msg_pub.get
2160 (p_encoded => FND_API.G_TRUE ,
2161 p_msg_index => 1,
2162 p_data => x_msg_data,
2163 p_msg_index_out => l_msg_index_out );
2164
2165 End if;
2166 RAISE FND_API.G_EXC_ERROR;
2167 End if;
2168
2169
2170 update igw_budget_details
2171 set line_item_cost = nvl(l_new_line_item_cost, line_item_cost)
2172 where line_item_id = p_line_item_id;
2173
2174 update igw_budget_details_cal_amts
2175 set calculated_cost = nvl(l_new_calculated_cost, calculated_cost)
2176 where line_item_id = p_line_item_id;
2177
2178
2179 IGW_BUDGET_OPERATIONS.recalculate_budget (
2180 p_proposal_id => l_proposal_id
2181 ,p_version_id => l_version_id
2182 ,p_budget_period_id => l_budget_period_id
2183 ,x_return_status => x_return_status
2184 ,x_msg_data => x_msg_data
2185 ,x_msg_count => x_msg_count);
2186
2187 --x_return_status := l_return_status;
2188
2189
2190
2191 Exception
2192 when FND_API.G_EXC_ERROR then
2193 --x_return_status := l_return_status;
2194 --x_msg_data := l_msg_data;
2195 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2196 p_data => x_msg_data);
2197 when others then
2198 x_return_status := 'U';
2199 x_msg_data := SQLCODE||' '||SQLERRM;
2200 fnd_msg_pub.add_exc_msg(G_PKG_NAME, 'SYNC_TO_COST_LIMIT_WRAP');
2201 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2202 End;
2203 ------------------------------------------------------------------------------------------
2204 PROCEDURE sync_to_cost_limit_wrap_2(
2205 p_line_item_id NUMBER
2206 ,p_line_item_cost NUMBER
2207 ,x_return_status OUT NOCOPY VARCHAR2
2208 ,x_msg_data OUT NOCOPY VARCHAR2
2209 ,x_msg_count OUT NOCOPY NUMBER) is
2210
2211
2212 l_line_item_cost NUMBER;
2213 l_return_status VARCHAR2(1);
2214 l_msg_data VARCHAR2(200);
2215 l_msg_count NUMBER(10);
2216 l_msg_index_out NUMBER;
2217
2218 cursor c_line_item_cost is
2219 select line_item_cost
2220 from igw_budget_details
2221 where line_item_id = p_line_item_id;
2222
2223 Begin
2224 x_return_status := 'S';
2225 fnd_msg_pub.initialize;
2226
2227
2228
2229 sync_to_cost_limit_wrap(p_line_item_id
2230 ,p_line_item_cost
2231 ,x_return_status
2232 ,x_msg_data
2233 ,x_msg_count );
2234
2235 /* There is a reason why to execute sync_to_cost_limit when p_line_item_cost is equal to 0. It is because
2236 when p_line_item_cost is 0, we don't know the indirect cost rate. Executing once exceeds the total cost. */
2237 if p_line_item_cost = 0 then
2238 open c_line_item_cost;
2239 fetch c_line_item_cost into l_line_item_cost;
2240 close c_line_item_cost;
2241 sync_to_cost_limit_wrap(p_line_item_id
2242 ,l_line_item_cost
2243 ,x_return_status
2244 ,x_msg_data
2245 ,x_msg_count );
2246 end if;
2247 Exception
2248 when FND_API.G_EXC_ERROR then
2249 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2250 p_data => x_msg_data);
2251 when others then
2252 x_return_status := 'U';
2253 x_msg_data := SQLCODE||' '||SQLERRM;
2254 fnd_msg_pub.add_exc_msg(G_PKG_NAME, 'SYNC_TO_COST_LIMIT_WRAP_2');
2255 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2256 End;
2257
2258
2259 END IGW_GENERATE_PERIODS;