[Home] [Help]
PACKAGE BODY: APPS.IGW_BUDGET_OPERATIONS
Source
1 PACKAGE BODY IGW_BUDGET_OPERATIONS as
2 --$Header: igwbuopb.pls 115.37 2003/08/08 23:31:37 ashkumar ship $
3
4 FUNCTION check_exp_assignment (p_expenditure_type VARCHAR2
5 ,p_expenditure_category_flag VARCHAR2
6 ,p_rate_class_id NUMBER
7 ,p_rate_type_id NUMBER) RETURN BOOLEAN is
8 l_dummy VARCHAR2(10);
9 l_expenditure_category VARCHAR2(30);
10 BEGIN
11 if p_expenditure_category_flag = 'Y' then
12 l_expenditure_category := p_expenditure_type;
13 elsif p_expenditure_category_flag = 'N' then
14 select expenditure_category
15 into l_expenditure_category
16 from igw_expenditure_types_v
17 where expenditure_type = p_expenditure_type;
18 end if;
19
20 select '1'
21 into l_dummy
22 from igw_exp_type_rate_types
23 where expenditure_category = l_expenditure_category /* p_expenditure_type */
24 and rate_class_id = p_rate_class_id
25 and rate_type_id = p_rate_type_id
26 and rownum < 2;
27
28 RETURN TRUE;
29 EXCEPTION
30 when no_data_found then
31 RETURN FALSE;
32 END check_exp_assignment;
33
34 ------------------------------------------------------------------------------------
35 /* this procedure is obsoleted, duplicated procedure below to recalculate individually */
36
37 /*
38 PROCEDURE recalculate_budget (p_proposal_id NUMBER
39 ,p_version_id NUMBER
40 ,p_activity_type_code VARCHAR2
41 ,p_oh_rate_class_id NUMBER
42 ,x_return_status OUT NOCOPY VARCHAR2
43 ,x_msg_data OUT NOCOPY VARCHAR2
44 ,x_msg_count OUT NOCOPY NUMBER) is
45
46 cursor c_budget_periods is
47 select budget_period_id
48 , start_date
49 , end_date
50 from igw_budget_periods
51 where proposal_id = p_proposal_id
52 and version_id = p_version_id;
53
54 l_budget_period_id NUMBER(15);
55
56 cursor c_budget_details is
57 select pbd.line_item_id
58 , pbd.expenditure_type
59 , pbd.expenditure_category_flag
60 , pbd.apply_inflation_flag
61 , pbd.line_item_cost
62 , pbd.cost_sharing_amount
63 , pbd.location_code
64 , et.personnel_attached_flag
65 , pbd.budget_period_id
66 , pbd.proposal_id
67 , pbd.version_id
68 from igw_budget_details pbd
69 , igw_budget_expenditures_v et
70 where pbd.expenditure_type = et.budget_expenditure
71 and pbd.expenditure_category_flag = et.expenditure_category_flag
72 and pbd.proposal_id = p_proposal_id
73 and pbd.budget_period_id = l_budget_period_id
74 and pbd.version_id = p_version_id;
75
76
77 l_line_item_id NUMBER(15);
78
79 cursor c_budget_personnel is
80 select budget_personnel_detail_id
81 , start_date
82 , end_date
83 , percent_charged
84 , cost_sharing_percent
85 , person_id
86 , appointment_type_code
87 from igw_budget_personnel_details
88 where line_item_id = l_line_item_id;
89
90 l_base_amount NUMBER;
91 l_rate_class_id_oh NUMBER(15);
92 l_rate_type_id_oh NUMBER(15);
93 l_rate_class_id_eb NUMBER(15);
94 l_rate_type_id_eb NUMBER(15);
95 l_rate_class_id_inf NUMBER(15);
96 l_rate_type_id_inf NUMBER(15);
97 l_rate_class_id_oh_d NUMBER(15);
98 l_rate_type_id_oh_d NUMBER(15);
99 l_rate_class_id_eb_d NUMBER(15);
100 l_rate_type_id_eb_d NUMBER(15);
101 l_calculated_cost_share NUMBER;
102 l_calculated_cost_share_ov NUMBER;
103 l_oh_value NUMBER;
104 l_oh_value_ov NUMBER;
105 l_eb_value NUMBER;
106 l_eb_value_ov NUMBER;
107 l_oh_value_d NUMBER;
108 l_oh_value_ov_d NUMBER;
109 l_eb_value_d NUMBER;
110 l_eb_value_ov_d NUMBER;
111 l_inflated_salary NUMBER;
112 l_inflated_salary_ov NUMBER;
113 l_effective_date DATE;
114 l_appointment_type_code VARCHAR2(30);
115 l_apply_rate_flag_oh VARCHAR2(1);
116 l_apply_rate_flag_eb VARCHAR2(1);
117 l_apply_rate_flag VARCHAR2(1);
118 l_calculation_base NUMBER;
119 l_direct_cost1 NUMBER;
120 l_cost_share1 NUMBER;
121 l_underrecovery NUMBER;
122 l_cost_share2 NUMBER;
123 l_indirect_cost NUMBER;
124 l_cost_share3 NUMBER;
125 l_direct_cost2 NUMBER;
126 l_total_cost NUMBER;
127 l_total_direct_cost NUMBER;
128 l_total_indirect_cost NUMBER;
129 l_cost_sharing_amt NUMBER;
130 l_underrecovery_amount NUMBER;
131 l_total_cost_limit NUMBER;
132 l_calculated_cost_oh NUMBER;
133 l_cost_sharing_oh NUMBER;
134 l_calculated_cost_eb NUMBER;
135 l_cost_sharing_eb NUMBER;
136 l_salary_requested_upd NUMBER;
137 l_cost_sharing_amount_upd NUMBER;
138 l_underrecovery_amount_upd NUMBER;
139 l_return_status VARCHAR2(1);
140 l_msg_data VARCHAR2(200);
141 l_msg_count NUMBER(10);
142
143 BEGIN
144 fnd_msg_pub.initialize;
145 for rec_budget_periods in c_budget_periods
146 LOOP
147 l_budget_period_id := rec_budget_periods.budget_period_id;
148 for rec_budget_details in c_budget_details
149 LOOP
150 l_line_item_id := rec_budget_details.line_item_id;
151 l_rate_class_id_oh := p_oh_rate_class_id;
152 IGW_OVERHEAD_CAL.get_rate_id(rec_budget_details.expenditure_type
153 ,rec_budget_details.expenditure_category_flag
154 , 'O'
155 ,l_rate_class_id_oh
156 ,l_rate_type_id_oh
157 ,l_return_status
158 ,l_msg_data);
159
160 if l_return_status <> 'S' then
161 raise FND_API.G_EXC_ERROR;
162 end if;
163
164
165 if rec_budget_details.personnel_attached_flag = 'N' then
166
167 if rec_budget_details.apply_inflation_flag = 'Y' then
168 IGW_OVERHEAD_CAL.get_rate_id(rec_budget_details.expenditure_type
169 ,rec_budget_details.expenditure_category_flag
170 ,'I'
171 , l_rate_class_id_inf
172 ,l_rate_type_id_inf
173 ,l_return_status
174 ,l_msg_data);
175 if l_return_status <> 'S' then
176 raise FND_API.G_EXC_ERROR;
177 end if;
178 end if;
179 BEGIN
180 select apply_rate_flag
181 into l_apply_rate_flag_oh
182 from igw_budget_details_cal_amts
183 where line_item_id = rec_budget_details.line_item_id;
184 EXCEPTION
185 when no_data_found then
186 l_apply_rate_flag_oh := 'Y';
187 End;
188
189 l_base_amount := rec_budget_details.line_item_cost;
190
191 IGW_OVERHEAD_CAL.calc_oh(p_proposal_id
192 ,p_version_id
193 ,l_base_amount
194 ,rec_budget_periods.start_date
195 ,rec_budget_periods.end_date
196 ,l_oh_value
197 ,l_oh_value_ov
198 ,p_activity_type_code
199 ,rec_budget_details.location_code
200 ,l_rate_class_id_oh
201 ,l_rate_type_id_oh
202 ,l_return_status
203 ,l_msg_data
204 ,l_msg_count);
205
206 if l_return_status <> 'S' then
207 raise FND_API.G_EXC_ERROR;
208 end if;
209
210 if l_apply_rate_flag_oh = 'N' then
211 l_oh_value_ov := 0;
212 l_calculated_cost_share_ov := 0;
213 elsif l_apply_rate_flag_oh = 'Y' then
214 IGW_OVERHEAD_CAL.calc_oh(p_proposal_id
215 ,p_version_id
216 ,rec_budget_details.cost_sharing_amount
217 ,rec_budget_periods.start_date
218 ,rec_budget_periods.end_date
219 ,l_calculated_cost_share
220 ,l_calculated_cost_share_ov
221 ,p_activity_type_code
222 ,rec_budget_details.location_code
223 ,l_rate_class_id_oh
224 ,l_rate_type_id_oh
225 ,l_return_status
226 ,l_msg_data
227 ,l_msg_count);
228 if l_return_status <> 'S' then
229 raise FND_API.G_EXC_ERROR;
230 end if;
231 end if;
232
233
234 update igw_budget_details
235 set line_item_cost = l_base_amount
236 , underrecovery_amount = nvl(l_oh_value - l_oh_value_ov,0)
237 where line_item_id = rec_budget_details.line_item_id;
238
239 delete from igw_budget_details_cal_amts
240 where line_item_id = rec_budget_details.line_item_id;
241
242 if check_exp_assignment(rec_budget_details.expenditure_type
243 ,rec_budget_details.expenditure_category_flag
244 ,l_rate_class_id_oh
245 ,l_rate_type_id_oh) then
246
247
248 IGW_GENERATE_PERIODS.create_budget_detail_amts(p_proposal_id
249 ,p_version_id
250 ,rec_budget_details.budget_period_id
251 ,rec_budget_details.line_item_id
252 ,l_rate_class_id_oh
253 ,l_rate_type_id_oh
254 ,l_apply_rate_flag_oh
255 ,nvl(l_oh_value_ov,0)
256 ,nvl(l_calculated_cost_share_ov,0));
257 end if;
258
259 elsif rec_budget_details.personnel_attached_flag = 'Y' then
260 IGW_OVERHEAD_CAL.get_rate_id(rec_budget_details.expenditure_type
261 ,rec_budget_details.expenditure_category_flag
262 ,'E'
263 ,l_rate_class_id_eb,l_rate_type_id_eb,l_return_status, l_msg_data);
264
265 if l_return_status <> 'S' then
266 raise FND_API.G_EXC_ERROR;
267 end if;
268 for rec_budget_personnel in c_budget_personnel
269 LOOP
270 BEGIN
271 select pca.apply_rate_flag
272 into l_apply_rate_flag_oh
273 from igw_budget_personnel_cal_amts pca
274 , igw_budget_personnel_details pbd
275 where pbd.budget_personnel_detail_id = rec_budget_personnel.budget_personnel_detail_id
276 and pca.budget_personnel_detail_id = pbd.budget_personnel_detail_id
277 and pca.rate_class_id = ( select pr.rate_class_id
278 from igw_rate_classes pr
279 where pca.rate_class_id = pr.rate_class_id
280 and pr.rate_class_type = 'O');
281 EXCEPTION
282 when no_data_found then
283 l_apply_rate_flag_oh := 'Y';
284 End;
285
286 BEGIN
287 select pca.apply_rate_flag
288 into l_apply_rate_flag_eb
289 from igw_budget_personnel_cal_amts pca
290 , igw_budget_personnel_details pbd
291 where pbd.budget_personnel_detail_id = rec_budget_personnel.budget_personnel_detail_id
292 and pca.budget_personnel_detail_id = pbd.budget_personnel_detail_id
293 and pca.rate_class_id = ( select pr.rate_class_id
294 from igw_rate_classes pr
295 where pca.rate_class_id = pr.rate_class_id
296 and pr.rate_class_type = 'E');
297 EXCEPTION
298 when no_data_found then
299 l_apply_rate_flag_eb := 'Y';
300 End;
301
302 ---get base amount
303 select calculation_base
304 , effective_date
305 , appointment_type_code
306 into l_calculation_base
307 , l_effective_date
308 , l_appointment_type_code
309 from igw_budget_persons
310 where proposal_id = p_proposal_id
311 and version_id = p_version_id
312 and person_id = rec_budget_personnel.person_id
313 and appointment_type_code = rec_budget_personnel.appointment_type_code;
314
315 IGW_OVERHEAD_CAL.calc_salary(p_proposal_id
316 ,p_version_id
317 ,l_calculation_base
318 ,l_effective_date
319 ,l_appointment_type_code
320 ,rec_budget_personnel.start_date
321 ,rec_budget_personnel.end_date
322 ,l_inflated_salary
323 ,l_inflated_salary_ov
324 ,rec_budget_details.expenditure_type
325 ,rec_budget_details.expenditure_category_flag
326 ,p_activity_type_code
327 ,rec_budget_details.location_code
328 ,l_return_status
329 ,l_msg_data
330 ,l_msg_count);
331 if l_return_status <> 'S' then
332 raise FND_API.G_EXC_ERROR;
333 end if;
334
335 l_inflated_salary := rec_budget_personnel.percent_charged/100 * l_inflated_salary;
336 l_inflated_salary_ov := rec_budget_personnel.percent_charged/100 * l_inflated_salary_ov;
337 if l_apply_rate_flag_oh = 'N' then
338 l_rate_class_id_oh_d := null;
339 l_rate_type_id_oh_d := null;
340 else
341 l_rate_class_id_oh_d := l_rate_class_id_oh;
342 l_rate_type_id_oh_d := l_rate_type_id_oh;
343 end if;
344 if l_apply_rate_flag_eb = 'N' then
345 l_rate_class_id_eb_d := null;
346 l_rate_type_id_eb_d := null;
347 else
348 l_rate_class_id_eb_d := l_rate_class_id_eb;
349 l_rate_type_id_eb_d := l_rate_type_id_eb;
350 end if;
351
352
353 IGW_OVERHEAD_CAL.calc_oh_eb (p_proposal_id
354 ,p_version_id
355 ,l_inflated_salary_ov
356 ,rec_budget_personnel.start_date
357 ,rec_budget_personnel.end_date
358 ,l_oh_value
359 ,l_oh_value_ov
360 ,l_eb_value
361 ,l_eb_value_ov
362 ,p_activity_type_code
363 ,rec_budget_details.location_code
364 ,l_rate_class_id_oh_d
365 ,l_rate_type_id_oh_d
366 ,l_rate_class_id_eb_d
367 ,l_rate_type_id_eb_d
368 ,l_return_status
369 ,l_msg_data
370 ,l_msg_count);
371 if l_return_status <> 'S' then
372 raise FND_API.G_EXC_ERROR;
373 end if;
374
375 IGW_OVERHEAD_CAL.calc_oh_eb (p_proposal_id
376 ,p_version_id
377 ,l_inflated_salary_ov
378 ,rec_budget_personnel.start_date
379 ,rec_budget_personnel.end_date
380 ,l_oh_value_d
381 ,l_oh_value_ov_d
382 ,l_eb_value_d
383 ,l_eb_value_ov_d
384 ,p_activity_type_code
385 ,rec_budget_details.location_code
386 ,l_rate_class_id_oh
387 ,l_rate_type_id_oh
388 ,l_rate_class_id_eb
389 ,l_rate_type_id_eb
390 ,l_return_status
391 ,l_msg_data
392 ,l_msg_count);
393 if l_return_status <> 'S' then
394 raise FND_API.G_EXC_ERROR;
395 end if;
396
397
398 if l_apply_rate_flag_oh = 'N' then
399 l_oh_value_ov := 0;
400 end if;
401 if l_apply_rate_flag_eb = 'N' then
402 l_eb_value_ov := 0;
403 end if;
404
405 update igw_budget_personnel_details
406 set salary_requested = l_inflated_salary_ov
407 , cost_sharing_amount = rec_budget_personnel.cost_sharing_percent/100 *
408 l_inflated_salary_ov
409 , underrecovery_amount = nvl(l_oh_value_d - l_oh_value_ov,0)
410 where budget_personnel_detail_id = rec_budget_personnel.budget_personnel_detail_id;
411
412
413 delete from igw_budget_personnel_cal_amts
414 where budget_personnel_detail_id = rec_budget_personnel.budget_personnel_detail_id;
415
416 if check_exp_assignment(rec_budget_details.expenditure_type
417 ,rec_budget_details.expenditure_category_flag
418 ,l_rate_class_id_oh
419 ,l_rate_type_id_oh) then
420 IGW_GENERATE_PERIODS.create_budget_personnel_amts (
421 rec_budget_personnel.budget_personnel_detail_id
422 ,l_rate_class_id_oh
423 ,l_rate_type_id_oh
424 ,l_apply_rate_flag_oh
425 ,nvl(l_oh_value_ov,0)
426 ,rec_budget_personnel.cost_sharing_percent/100 * l_oh_value_ov);
427 end if;
431 ,l_rate_type_id_eb) then
428 if check_exp_assignment(rec_budget_details.expenditure_type
429 ,rec_budget_details.expenditure_category_flag
430 ,l_rate_class_id_eb
432 IGW_GENERATE_PERIODS.create_budget_personnel_amts (
433 rec_budget_personnel.budget_personnel_detail_id
434 ,l_rate_class_id_eb
435 ,l_rate_type_id_eb
436 ,l_apply_rate_flag_eb
437 ,nvl(l_eb_value_ov,0)
438 ,rec_budget_personnel.cost_sharing_percent/100 * l_eb_value);
439 end if;
440
441 END LOOP; --rec_budget_personnel
442
443
444 delete from igw_budget_details_cal_amts
445 where line_item_id = rec_budget_details.line_item_id;
446
447 select sum(nvl(ppc.calculated_cost,0))
448 , sum(nvl(ppc.calculated_cost_sharing,0))
449 into l_calculated_cost_oh
450 , l_cost_sharing_oh
451 from igw_budget_personnel_cal_amts ppc
452 , igw_budget_personnel_details ppd
453 where ppd.line_item_id = rec_budget_details.line_item_id
454 and ppd.budget_personnel_detail_id = ppc.budget_personnel_detail_id
455 and ppc.rate_class_id = l_rate_class_id_oh
456 and ppc.rate_type_id = l_rate_type_id_oh;
457
458 select sum(nvl(ppc.calculated_cost,0))
459 , sum(nvl(ppc.calculated_cost_sharing,0))
460 into l_calculated_cost_eb
461 , l_cost_sharing_eb
462 from igw_budget_personnel_cal_amts ppc
463 , igw_budget_personnel_details ppd
464 where ppd.line_item_id = rec_budget_details.line_item_id
465 and ppd.budget_personnel_detail_id = ppc.budget_personnel_detail_id
466 and ppc.rate_class_id = l_rate_class_id_eb
467 and ppc.rate_type_id = l_rate_type_id_eb;
468
469 if check_exp_assignment(rec_budget_details.expenditure_type
470 ,rec_budget_details.expenditure_category_flag
471 ,l_rate_class_id_oh
472 ,l_rate_type_id_oh) then
473 IGW_GENERATE_PERIODS.create_budget_detail_amts(rec_budget_details.proposal_id
474 ,rec_budget_details.version_id
475 ,rec_budget_details.budget_period_id
476 ,rec_budget_details.line_item_id
477 ,l_rate_class_id_oh
478 ,l_rate_type_id_oh
479 ,'Y'
480 ,nvl(l_calculated_cost_oh,0)
481 ,nvl(l_cost_sharing_oh,0));
482 end if;
483
484 if check_exp_assignment(rec_budget_details.expenditure_type
485 ,rec_budget_details.expenditure_category_flag
486 ,l_rate_class_id_eb
487 ,l_rate_type_id_eb) then
488 IGW_GENERATE_PERIODS.create_budget_detail_amts(rec_budget_details.proposal_id
489 ,rec_budget_details.version_id
490 ,rec_budget_details.budget_period_id
491 ,rec_budget_details.line_item_id
492 ,l_rate_class_id_eb
493 ,l_rate_type_id_eb
494 ,'Y'
495 ,nvl(l_calculated_cost_eb,0)
496 ,nvl(l_cost_sharing_eb,0));
497 end if;
498
499 --changed as recalculate should insert and delete overhead and eb amounts
500
501 select sum(nvl(ppd.salary_requested,0))
502 , sum(nvl(ppd.cost_sharing_amount,0))
503 , sum(nvl(ppd.underrecovery_amount,0))
504 into l_salary_requested_upd
505 , l_cost_sharing_amount_upd
506 , l_underrecovery_amount_upd
507 from igw_budget_personnel_details ppd
508 where ppd.line_item_id = rec_budget_details.line_item_id;
509
510 update igw_budget_details pdb
511 set line_item_cost = nvl(l_salary_requested_upd,0)
512 , cost_sharing_amount = nvl(l_cost_sharing_amount_upd,0)
513 , underrecovery_amount = nvl(l_underrecovery_amount_upd,0)
514 where pdb.line_item_id = rec_budget_details.line_item_id;
515
516 end if;
517 end LOOP; --rec_budget_details
518
519
520 select nvl(sum(line_item_cost),0)
521 , nvl(sum(cost_sharing_amount),0)
522 , nvl(sum(underrecovery_amount),0)
523 into l_direct_cost1
524 , l_cost_share1
525 , l_underrecovery
526 from igw_budget_details
527 where proposal_id = p_proposal_id
528 and version_id = p_version_id
529 and budget_period_id = rec_budget_periods.budget_period_id;
530
531 select nvl(sum(calculated_cost_sharing),0)
532 , nvl(sum(calculated_cost),0)
533 into l_cost_share2
534 , l_indirect_cost
535 from igw_budget_details_cal_amts pc
536 where proposal_id = p_proposal_id
537 and version_id = p_version_id
538 and budget_period_id = rec_budget_periods.budget_period_id
539 and pc.rate_class_id = ( select pr.rate_class_id
540 from igw_rate_classes pr
541 where pc.rate_class_id = pr.rate_class_id
542 and pr.rate_class_type = 'O');
543 select nvl(sum(calculated_cost_sharing),0)
544 , nvl(sum(calculated_cost),0)
545 into l_cost_share3
546 , l_direct_cost2
547 from igw_budget_details_cal_amts pc
548 where proposal_id = p_proposal_id
549 and version_id = p_version_id
550 and budget_period_id = rec_budget_periods.budget_period_id
551 and pc.rate_class_id = ( select pr.rate_class_id
552 from igw_rate_classes pr
553 where pc.rate_class_id = pr.rate_class_id
554 and pr.rate_class_type = 'E');
555
559 , total_indirect_cost = l_indirect_cost
556 update igw_budget_periods
557 set total_cost = (l_direct_cost1+l_direct_cost2+l_indirect_cost)
558 , total_direct_cost = (l_direct_cost1+l_direct_cost2)
560 , cost_sharing_amount = (l_cost_share1+l_cost_share2+l_cost_share3)
561 , underrecovery_amount = l_underrecovery
562 where proposal_id = p_proposal_id
563 and version_id = p_version_id
564 and budget_period_id = rec_budget_periods.budget_period_id;
565
566 end LOOP; --rec_budget_periods
567
568
569 select nvl(sum(total_cost),0)
570 , nvl(sum(total_direct_cost),0)
571 , nvl(sum(total_indirect_cost),0)
572 , nvl(sum(cost_sharing_amount),0)
573 , nvl(sum(underrecovery_amount),0)
574 , nvl(sum(total_cost_limit),0)
575 into l_total_cost
576 , l_total_direct_cost
577 , l_total_indirect_cost
578 , l_cost_sharing_amt
579 , l_underrecovery_amount
580 , l_total_cost_limit
581 from igw_budget_periods
582 where proposal_id = p_proposal_id
583 and version_id = p_version_id;
584
585
586 update igw_budgets
587 set total_cost = l_total_cost
588 , total_direct_cost = l_total_direct_cost
589 , total_indirect_cost = l_total_indirect_cost
590 , cost_sharing_amount = l_cost_sharing_amt
591 , underrecovery_amount = l_underrecovery_amount
592 , total_cost_limit = l_total_cost_limit
593 where proposal_id = p_proposal_id
594 and version_id = p_version_id;
595
596 x_return_status := 'S';
597 EXCEPTION
598 when FND_API.G_EXC_ERROR then
599 x_return_status := l_return_status;
600 x_msg_data := l_msg_data;
601 fnd_msg_pub.count_and_get(p_count => x_msg_count,
602 p_data => x_msg_data);
603
604 when others then
605 x_return_status := 'U';
606 x_msg_data := SQLCODE||' '||SQLERRM;
607 fnd_msg_pub.add_exc_msg(G_PKG_NAME, 'RECALCULATE_BUDGET');
608 fnd_msg_pub.count_and_get(p_count => x_msg_count,
609 p_data => x_msg_data);
610 end recalculate_budget;
611 */
612 -----------------------------------------------------------------------------------------------
613 PROCEDURE copy_budget(p_proposal_id NUMBER
614 ,p_target_proposal_id NUMBER
615 ,p_version_id NUMBER
616 ,p_copy_first_period VARCHAR2
617 ,p_copy_type VARCHAR2
618 ,p_budget_type_code VARCHAR2
619 ,x_return_status OUT NOCOPY VARCHAR2
620 ,x_msg_data OUT NOCOPY VARCHAR2
621 ,x_msg_count OUT NOCOPY NUMBER) is
622
623 /* The values for p_copy_type can be 'B'
624 for budget and 'P' for proposal.
625
626 The values for p_copy_first_period is 'S'
627 for a single period
628
629 The values for p_budget_type_code are 'PROPOSAL_BUDGET'
630 for any normal budget version in PRE-AWARD system and
631 'AWARD_BUDGET' for the version that will used to
632 export to POST-AWARD system */
633
634
635 cursor c_budgets is
636 select *
637 from igw_budgets
638 where proposal_id = p_proposal_id
639 and version_id = nvl(p_version_id, version_id);
640
641 l_version_id NUMBER(4);
642
643 cursor c_budget_periods is
644 select *
645 from igw_budget_periods
646 where proposal_id = p_proposal_id
647 and version_id = l_version_id;
648
649 l_budget_period_id NUMBER(15);
650
651 cursor c_budget_details is
652 select pbd.line_item_id
653 , pbd.expenditure_type
654 , pbd.budget_category_code
655 , pbd.expenditure_category_flag
656 , pbd.line_item_description
657 , pbd.based_on_line_item
658 , pbd.line_item_cost
659 , pbd.cost_sharing_amount
660 , pbd.underrecovery_amount
661 , pbd.apply_inflation_flag
662 , pbd.budget_justification
663 , pbd.location_code
664 , et.personnel_attached_flag
665 , pbd.budget_period_id
666 from igw_budget_details pbd
667 , igw_budget_expenditures_v et
668 where pbd.expenditure_type = et.budget_expenditure
669 and pbd.expenditure_category_flag = et.expenditure_category_flag
670 and pbd.proposal_id = p_proposal_id
671 and pbd.budget_period_id = l_budget_period_id
672 and pbd.version_id = l_version_id;
673
674 l_line_item_id NUMBER(15);
675
676
677 cursor c_budget_detail_amts is
678 select *
679 from igw_budget_details_cal_amts
680 where line_item_id = l_line_item_id;
681
682
683 cursor c_budget_personnel is
684 select *
685 from igw_budget_personnel_details
686 where line_item_id = l_line_item_id;
687
688 l_budget_personnel_detail_id NUMBER(15);
689
690 cursor c_budget_personnel_amts is
691 select *
692 from igw_budget_personnel_cal_amts
693 where budget_personnel_detail_id = l_budget_personnel_detail_id;
694
695 cursor c_budget_persons is
696 select *
697 from igw_budget_persons
698 where proposal_id = p_proposal_id
699 and version_id = l_version_id;
700
701 cursor c_prop_rates is
702 select *
703 from igw_prop_rates
704 where proposal_id = p_proposal_id
705 and version_id = l_version_id;
706
710 l_based_on_line_item NUMBER(15);
707 l_target_version_id NUMBER(4);
708 l_dummy_line_item_id NUMBER(15);
709 l_dummy_personnel_id NUMBER(15);
711 l_counter NUMBER(15) :=1;
712 l_loop_counter NUMBER(15) :=1;
713 l_direct_cost1 NUMBER;
714 l_cost_share1 NUMBER;
715 l_underrecovery NUMBER;
716 l_cost_share2 NUMBER;
717 l_indirect_cost NUMBER;
718 l_cost_share3 NUMBER;
719 l_direct_cost2 NUMBER;
720 l_total_cost NUMBER;
721 l_total_direct_cost NUMBER;
722 l_total_indirect_cost NUMBER;
723 l_cost_sharing_amt NUMBER;
724 l_underrecovery_amount NUMBER;
725 l_total_cost_limit NUMBER;
726 l_return_status VARCHAR2(1);
727 l_msg_data VARCHAR2(200);
728 l_msg_count NUMBER(10);
729
730
731
732 BEGIN
733 fnd_msg_pub.initialize;
734
735 if p_copy_type = 'B' then
736 select max(version_id)+1
737 into l_target_version_id
738 from igw_budgets
739 where proposal_id = p_proposal_id;
740
741 --dbms_output.put_line('the max version id available is '||l_target_version_id);
742 end if;
743 l_version_id := p_version_id;
744
745 for rec_budgets in c_budgets
746 LOOP
747 if p_copy_type = 'P' then
748 if p_version_id is not null then
749 l_target_version_id := 1;
750 else
751 l_target_version_id := rec_budgets.version_id;
752 --dbms_output.put_line('the l_target_version_id '||l_target_version_id);
753 end if;
754 end if;
755 l_version_id := rec_budgets.version_id;
756
757 insert into igw_budgets( proposal_id
758 ,version_id
759 ,start_date
760 ,end_date
761 ,total_cost
762 ,total_direct_cost
763 ,total_indirect_cost
764 ,cost_sharing_amount
765 ,underrecovery_amount
766 ,residual_funds
767 ,total_cost_limit
768 ,oh_rate_class_id
769 ,proposal_form_number
770 ,comments
771 ,final_version_flag
772 ,budget_type_code
773 ,record_version_number
774 ,apply_inflation_setup_rates
775 ,apply_eb_setup_rates
776 ,apply_oh_setup_rates
777 ,enter_budget_at_period_level
778 ,last_update_date
779 ,last_updated_by
780 ,creation_date
781 ,created_by
782 ,last_update_login
783 ,attribute_category
784 ,attribute1
785 ,attribute2
786 ,attribute3
787 ,attribute4
788 ,attribute5
789 ,attribute6
790 ,attribute7
791 ,attribute8
792 ,attribute9
793 ,attribute10
794 ,attribute11
795 ,attribute12
796 ,attribute13
797 ,attribute14
798 ,attribute15)
799 values( p_target_proposal_id
800 ,l_target_version_id
801 ,rec_budgets.start_date
802 ,rec_budgets.end_date
803 ,rec_budgets.total_cost
804 ,rec_budgets.total_direct_cost
805 ,rec_budgets.total_indirect_cost
806 ,rec_budgets.cost_sharing_amount
807 ,rec_budgets.underrecovery_amount
808 ,rec_budgets.residual_funds
809 ,rec_budgets.total_cost_limit
810 ,rec_budgets.oh_rate_class_id
811 ,rec_budgets.proposal_form_number
812 ,rec_budgets.comments
813 ,'N'
814 ,p_budget_type_code
815 ,1
816 ,rec_budgets.apply_inflation_setup_rates
817 ,rec_budgets.apply_eb_setup_rates
818 ,rec_budgets.apply_oh_setup_rates
819 ,rec_budgets.enter_budget_at_period_level
820 ,sysdate
821 ,fnd_global.user_id
822 ,sysdate
823 ,fnd_global.user_id
824 ,fnd_global.login_id
825 ,rec_budgets.attribute_category
826 ,rec_budgets.attribute1
827 ,rec_budgets.attribute2
828 ,rec_budgets.attribute3
829 ,rec_budgets.attribute4
830 ,rec_budgets.attribute5
831 ,rec_budgets.attribute6
832 ,rec_budgets.attribute7
833 ,rec_budgets.attribute8
834 ,rec_budgets.attribute9
835 ,rec_budgets.attribute10
836 ,rec_budgets.attribute11
837 ,rec_budgets.attribute12
838 ,rec_budgets.attribute13
839 ,rec_budgets.attribute14
840 ,rec_budgets.attribute15);
841
842 for rec_budget_periods in c_budget_periods
843 LOOP
844 if p_copy_first_period = 'S' then
845 EXIT when l_loop_counter = 2;
846 end if;
847
848 insert into igw_budget_periods( proposal_id
849 ,version_id
850 ,budget_period_id
851 ,start_date
852 ,end_date
853 ,total_cost
854 ,total_direct_cost
855 ,total_indirect_cost
856 ,cost_sharing_amount
857 ,underrecovery_amount
858 ,total_cost_limit
859 ,program_income
860 ,program_income_source
861 ,record_version_number
862 ,last_update_date
863 ,last_updated_by
864 ,creation_date
865 ,created_by
866 ,last_update_login)
870 ,rec_budget_periods.start_date
867 values( p_target_proposal_id
868 ,l_target_version_id
869 ,rec_budget_periods.budget_period_id
871 ,rec_budget_periods.end_date
872 ,rec_budget_periods.total_cost
873 ,rec_budget_periods.total_direct_cost
874 ,rec_budget_periods.total_indirect_cost
875 ,rec_budget_periods.cost_sharing_amount
876 ,rec_budget_periods.underrecovery_amount
877 ,rec_budget_periods.total_cost_limit
878 ,rec_budget_periods.program_income
879 ,rec_budget_periods.program_income_source
880 ,1
881 ,sysdate
882 ,fnd_global.user_id
883 ,sysdate
884 ,fnd_global.user_id
885 ,fnd_global.login_id);
886
887 l_budget_period_id := rec_budget_periods.budget_period_id;
888
889 for rec_budget_details in c_budget_details
890 LOOP
891 if l_counter = 1 then
892 l_based_on_line_item := null;
893 elsif l_counter = 2 then
894 select igw_budget_details_s.currval into l_based_on_line_item from dual;
895 end if;
896
897 IGW_GENERATE_PERIODS.create_budget_detail (
898 p_target_proposal_id
899 ,l_target_version_id
900 ,rec_budget_details.budget_period_id
901 ,l_dummy_line_item_id
902 ,rec_budget_details.expenditure_type
903 ,rec_budget_details.budget_category_code
904 ,rec_budget_details.expenditure_category_flag
905 ,rec_budget_details.line_item_description
906 ,l_based_on_line_item
907 ,rec_budget_details.line_item_cost
908 ,rec_budget_details.cost_sharing_amount
909 ,rec_budget_details.underrecovery_amount
910 ,rec_budget_details.apply_inflation_flag
911 ,rec_budget_details.budget_justification
912 ,rec_budget_details.location_code);
913
914
915 if rec_budget_details.personnel_attached_flag = 'Y' then
916 l_line_item_id := rec_budget_details.line_item_id;
917 for rec_budget_personnel in c_budget_personnel
918 LOOP
919
920 insert into igw_budget_personnel_details (
921 budget_personnel_detail_id
922 ,proposal_id
923 ,version_id
924 ,budget_period_id
925 ,line_item_id
926 ,person_id
927 ,party_id
928 ,start_date
929 ,end_date
930 ,period_type_code
931 ,appointment_type_code
932 ,salary_requested
933 ,percent_charged
934 ,percent_effort
935 ,cost_sharing_percent
936 ,cost_sharing_amount
937 ,underrecovery_amount
938 ,record_version_number
939 ,last_update_date
940 ,last_updated_by
941 ,creation_date
942 ,created_by
943 ,last_update_login)
944 values (igw_budget_personnel_s.nextval
945 ,p_target_proposal_id
946 ,l_target_version_id
947 ,rec_budget_details.budget_period_id
948 ,igw_budget_details_s.currval
949 ,rec_budget_personnel.person_id
950 ,rec_budget_personnel.party_id
951 ,rec_budget_personnel.start_date
952 ,rec_budget_personnel.end_date
953 ,rec_budget_personnel.period_type_code
954 ,rec_budget_personnel.appointment_type_code
955 ,rec_budget_personnel.salary_requested
956 ,rec_budget_personnel.percent_charged
957 ,rec_budget_personnel.percent_effort
958 ,rec_budget_personnel.cost_sharing_percent
959 ,rec_budget_personnel.cost_sharing_amount
960 ,rec_budget_personnel.underrecovery_amount
961 ,1
962 ,sysdate
963 ,fnd_global.user_id
964 ,sysdate
965 ,fnd_global.user_id
966 ,fnd_global.login_id);
967
968 l_budget_personnel_detail_id := rec_budget_personnel.budget_personnel_detail_id;
969 for rec_budget_personnel_amts in c_budget_personnel_amts
970 LOOP
971 select igw_budget_personnel_s.currval into l_dummy_personnel_id from dual;
972 IGW_GENERATE_PERIODS.create_budget_personnel_amts (
973 l_dummy_personnel_id
974 ,rec_budget_personnel_amts.rate_class_id
975 ,rec_budget_personnel_amts.rate_type_id
976 ,rec_budget_personnel_amts.apply_rate_flag
977 ,rec_budget_personnel_amts.calculated_cost
978 ,rec_budget_personnel_amts.calculated_cost_sharing);
979 END LOOP; --personnel amts
980 END LOOP; --personnel
981 end if;
982 l_line_item_id := rec_budget_details.line_item_id;
983 for rec_budget_detail_amts in c_budget_detail_amts
984 LOOP
985
986 select igw_budget_details_s.currval into l_dummy_line_item_id from dual;
987
988 IGW_GENERATE_PERIODS.create_budget_detail_amts(p_target_proposal_id
989 ,l_target_version_id
990 ,rec_budget_details.budget_period_id
991 ,l_dummy_line_item_id
992 ,rec_budget_detail_amts.rate_class_id
993 ,rec_budget_detail_amts.rate_type_id
994 ,rec_budget_detail_amts.apply_rate_flag
995 ,rec_budget_detail_amts.calculated_cost
996 ,rec_budget_detail_amts.calculated_cost_sharing);
997
998 END LOOP; --rec_budget_detail_amts
999 l_counter := l_counter + 1;
1000 END LOOP; --budget details
1001 l_loop_counter := l_loop_counter + 1;
1005 insert into igw_budget_persons( proposal_id
1002 END LOOP; -- budget periods
1003 for rec_budget_persons in c_budget_persons
1004 LOOP
1006 ,version_id
1007 ,person_id
1008 ,party_id
1009 ,appointment_type_code
1010 ,effective_date
1011 ,calculation_base
1012 ,record_version_number
1013 ,last_update_date
1014 ,last_updated_by
1015 ,creation_date
1016 ,created_by
1017 ,last_update_login)
1018 values( p_target_proposal_id
1019 ,l_target_version_id
1020 ,rec_budget_persons.person_id
1021 ,rec_budget_persons.party_id
1022 ,rec_budget_persons.appointment_type_code
1023 ,rec_budget_persons.effective_date
1024 ,rec_budget_persons.calculation_base
1025 ,1
1026 ,sysdate
1027 ,fnd_global.user_id
1028 ,sysdate
1029 ,fnd_global.user_id
1030 ,fnd_global.login_id);
1031 END LOOP; --budget_persons
1032
1033 for rec_prop_rates in c_prop_rates
1034 LOOP
1035
1036 insert into igw_prop_rates( proposal_id
1037 ,version_id
1038 ,rate_class_id
1039 ,rate_type_id
1040 ,fiscal_year
1041 ,location_code
1042 ,activity_type_code
1043 ,start_date
1044 ,applicable_rate
1045 ,institute_rate
1046 ,record_version_number
1047 ,last_update_date
1048 ,last_updated_by
1049 ,creation_date
1050 ,created_by
1051 ,last_update_login)
1052 values( p_target_proposal_id
1053 ,l_target_version_id
1054 ,rec_prop_rates.rate_class_id
1055 ,rec_prop_rates.rate_type_id
1056 ,rec_prop_rates.fiscal_year
1057 ,rec_prop_rates.location_code
1058 ,rec_prop_rates.activity_type_code
1059 ,rec_prop_rates.start_date
1060 ,rec_prop_rates.applicable_rate
1061 ,rec_prop_rates.institute_rate
1062 ,1
1063 ,sysdate
1064 ,fnd_global.user_id
1065 ,sysdate
1066 ,fnd_global.user_id
1067 ,fnd_global.login_id);
1068 END LOOP;--rec_prop_rates
1069 END LOOP; -- budgets
1070 if p_copy_first_period = 'S' then
1071 select nvl(sum(line_item_cost),0)
1072 , nvl(sum(cost_sharing_amount),0)
1073 , nvl(sum(underrecovery_amount),0)
1074 into l_direct_cost1
1075 , l_cost_share1
1076 , l_underrecovery
1077 from igw_budget_details
1078 where proposal_id = p_target_proposal_id
1079 and version_id = l_target_version_id
1080 and budget_period_id = 1;
1081
1082 select nvl(sum(calculated_cost_sharing),0)
1083 , nvl(sum(calculated_cost),0)
1084 into l_cost_share2
1085 , l_indirect_cost
1086 from igw_budget_details_cal_amts pc
1087 where proposal_id = p_target_proposal_id
1088 and version_id = l_target_version_id
1089 and budget_period_id = 1
1090 and pc.rate_class_id = ( select pr.rate_class_id
1091 from igw_rate_classes pr
1092 where pc.rate_class_id = pr.rate_class_id
1093 and pr.rate_class_type = 'O');
1094 select nvl(sum(calculated_cost_sharing),0)
1095 , nvl(sum(calculated_cost),0)
1096 into l_cost_share3
1097 , l_direct_cost2
1098 from igw_budget_details_cal_amts pc
1099 where proposal_id = p_target_proposal_id
1100 and version_id = l_target_version_id
1101 and budget_period_id = 1
1102 and pc.rate_class_id = ( select pr.rate_class_id
1103 from igw_rate_classes pr
1104 where pc.rate_class_id = pr.rate_class_id
1105 and pr.rate_class_type = 'E');
1106
1107 update igw_budget_periods
1108 set total_cost = (l_direct_cost1+l_direct_cost2+l_indirect_cost)
1109 , total_direct_cost = (l_direct_cost1+l_direct_cost2)
1110 , total_indirect_cost = l_indirect_cost
1111 , cost_sharing_amount = (l_cost_share1+l_cost_share2+l_cost_share3)
1112 , underrecovery_amount = l_underrecovery
1113 where proposal_id = p_target_proposal_id
1114 and version_id = l_target_version_id
1115 and budget_period_id = 1;
1116
1117
1118
1119 select nvl(sum(total_cost),0)
1120 , nvl(sum(total_direct_cost),0)
1121 , nvl(sum(total_indirect_cost),0)
1122 , nvl(sum(cost_sharing_amount),0)
1123 , nvl(sum(underrecovery_amount),0)
1124 , nvl(sum(total_cost_limit),0)
1125 into l_total_cost
1126 , l_total_direct_cost
1127 , l_total_indirect_cost
1128 , l_cost_sharing_amt
1129 , l_underrecovery_amount
1130 , l_total_cost_limit
1131 from igw_budget_periods
1132 where proposal_id = p_target_proposal_id
1133 and version_id = l_target_version_id;
1134
1135
1136 update igw_budgets
1137 set total_cost = l_total_cost
1138 , total_direct_cost = l_total_direct_cost
1139 , total_indirect_cost = l_total_indirect_cost
1140 , cost_sharing_amount = l_cost_sharing_amt
1141 , underrecovery_amount = l_underrecovery_amount
1142 , total_cost_limit = l_total_cost_limit
1143 where proposal_id = p_target_proposal_id
1144 and version_id = l_target_version_id;
1145
1146 end if;
1147 x_return_status := 'S';
1148 EXCEPTION
1149 when FND_API.G_EXC_ERROR then
1150 x_return_status := l_return_status;
1154
1151 x_msg_data := l_msg_data;
1152 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1153 p_data => x_msg_data);
1155 when others then
1156 x_return_status := 'U';
1157 x_msg_data := SQLCODE||' '||SQLERRM;
1158 --dbms_output.put_line(x_msg_data);
1159 fnd_msg_pub.add_exc_msg(G_PKG_NAME, 'COPY_BUDGET');
1160 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1161 p_data => x_msg_data);
1162 END copy_budget;
1163 -----------------------------------------------------------------------------------------
1164 Function get_proposal_id RETURN NUMBER is
1165 Begin
1166 RETURN IGW_BUDGET_OPERATIONS.G_PROPOSAL_ID;
1167 End;
1168 -------------------------------------------------------------------------------------------
1169 Function get_version_id RETURN NUMBER is
1170 Begin
1171 RETURN IGW_BUDGET_OPERATIONS.G_VERSION_ID;
1172 End;
1173
1174 ----------------------------------------------------------------------------------------
1175 Function get_period_id RETURN NUMBER is
1176 Begin
1177 RETURN IGW_BUDGET_OPERATIONS.G_START_PERIOD;
1178 End;
1179
1180 ------------------------------------------------------------------------------------------------
1181 Procedure set_global_variables(p_start_period NUMBER
1182 ,p_proposal_id NUMBER
1183 ,p_version_id NUMBER) is
1184
1185 Begin
1186 IGW_BUDGET_OPERATIONS.G_START_PERIOD :=p_start_period;
1187 IGW_BUDGET_OPERATIONS.G_PROPOSAL_ID :=p_proposal_id;
1188 IGW_BUDGET_OPERATIONS.G_VERSION_ID :=p_version_id;
1189 End;
1190 --------------------------------------------------------------------------------------------
1191 PROCEDURE recalculate_budget(p_proposal_id NUMBER
1192 ,p_version_id NUMBER
1193 ,p_budget_period_id NUMBER :=NULL
1194 ,p_line_item_id NUMBER :=NULL
1195 ,p_budget_personnel_detail_id NUMBER :=NULL
1196 ,p_activity_type_code VARCHAR2 :=NULL
1197 ,p_oh_rate_class_id NUMBER :=NULL
1198 ,x_return_status OUT NOCOPY VARCHAR2
1199 ,x_msg_data OUT NOCOPY VARCHAR2
1200 ,x_msg_count OUT NOCOPY NUMBER) is
1201
1202
1203
1204 cursor c_budget_version is
1205 select enter_budget_at_period_level
1206 , apply_inflation_setup_rates
1207 , apply_eb_setup_rates
1208 , apply_oh_setup_rates
1209 from igw_budgets
1210 where proposal_id = p_proposal_id
1211 and version_id = p_version_id;
1212
1213
1214 cursor c_budget_periods is
1215 select budget_period_id
1216 , start_date
1217 , end_date
1218 , total_direct_cost
1219 , total_indirect_cost
1220 , total_cost
1221 from igw_budget_periods
1222 where proposal_id = p_proposal_id
1223 and version_id = p_version_id
1224 and budget_period_id = nvl(p_budget_period_id, budget_period_id);
1225
1226 l_budget_period_id NUMBER(15);
1227
1228 cursor c_budget_details is
1229 select pbd.line_item_id
1230 , pbd.expenditure_type
1231 , pbd.expenditure_category_flag
1232 , pbd.apply_inflation_flag
1233 , pbd.line_item_cost
1234 , pbd.cost_sharing_amount
1235 , pbd.location_code
1236 , et.personnel_attached_flag
1237 , pbd.budget_period_id
1238 , pbd.proposal_id
1239 , pbd.version_id
1240 from igw_budget_details pbd
1241 , igw_budget_expenditures_v et
1242 where pbd.expenditure_type = et.budget_expenditure
1243 and pbd.expenditure_category_flag = et.expenditure_category_flag
1244 and pbd.proposal_id = p_proposal_id
1245 and pbd.budget_period_id = l_budget_period_id
1246 and pbd.version_id = p_version_id
1247 and pbd.line_item_id = nvl(p_line_item_id, line_item_id);
1248
1249
1250 l_line_item_id NUMBER(15);
1251
1252 cursor c_budget_personnel is
1253 select budget_personnel_detail_id
1254 , start_date
1255 , end_date
1256 , percent_charged
1257 , cost_sharing_percent
1258 , person_id
1259 , party_id
1260 , appointment_type_code
1261 from igw_budget_personnel_details
1262 where line_item_id = l_line_item_id
1263 and budget_personnel_detail_id = nvl(p_budget_personnel_detail_id, budget_personnel_detail_id);
1264
1265 l_pers_cost_sharing_amt NUMBER; -- Bug 2702314
1266 l_base_amount NUMBER;
1267 l_rate_class_id_oh NUMBER(15);
1268 l_rate_type_id_oh NUMBER(15);
1269 l_rate_class_id_eb NUMBER(15);
1270 l_rate_type_id_eb NUMBER(15);
1271 l_rate_class_id_inf NUMBER(15);
1272 l_rate_type_id_inf NUMBER(15);
1273 l_rate_class_id_oh_d NUMBER(15);
1274 l_rate_type_id_oh_d NUMBER(15);
1275 l_rate_class_id_eb_d NUMBER(15);
1276 l_rate_type_id_eb_d NUMBER(15);
1277 l_calculated_cost_share NUMBER;
1278 l_calculated_cost_share_ov NUMBER;
1279 l_oh_value NUMBER;
1280 l_oh_value_ov NUMBER;
1281 l_eb_value NUMBER;
1282 l_eb_value_ov NUMBER;
1283 l_oh_value_d NUMBER;
1284 l_oh_value_ov_d NUMBER;
1285 l_eb_value_d NUMBER;
1286 l_eb_value_ov_d NUMBER;
1287 l_inflated_salary NUMBER;
1291 l_apply_rate_flag_oh VARCHAR2(1);
1288 l_inflated_salary_ov NUMBER;
1289 l_effective_date DATE;
1290 l_appointment_type_code VARCHAR2(30);
1292 l_apply_rate_flag_eb VARCHAR2(1);
1293 l_apply_rate_flag VARCHAR2(1);
1294 l_calculation_base NUMBER;
1295 l_direct_cost1 NUMBER;
1296 l_cost_share1 NUMBER;
1297 l_underrecovery NUMBER;
1298 l_cost_share2 NUMBER;
1299 l_indirect_cost NUMBER;
1300 l_cost_share3 NUMBER;
1301 l_direct_cost2 NUMBER;
1302 l_total_cost NUMBER;
1303 l_total_direct_cost NUMBER;
1304 l_total_indirect_cost NUMBER;
1305 l_cost_sharing_amt NUMBER;
1306 l_underrecovery_amount NUMBER;
1307 l_total_cost_limit NUMBER;
1308 l_calculated_cost_oh NUMBER;
1309 l_cost_sharing_oh NUMBER;
1310 l_calculated_cost_eb NUMBER;
1311 l_cost_sharing_eb NUMBER;
1312 l_salary_requested_upd NUMBER;
1313 l_cost_sharing_amount_upd NUMBER;
1314 l_underrecovery_amount_upd NUMBER;
1315 l_activity_type_code NUMBER := p_activity_type_code;
1316 l_oh_rate_class_id NUMBER := p_oh_rate_class_id;
1317 l_enter_budget_at_period_level VARCHAR2(1);
1318 l_apply_inflation_setup_rates VARCHAR2(1);
1319 l_apply_eb_setup_rates VARCHAR2(1);
1320 l_apply_oh_setup_rates VARCHAR2(1);
1321 l_calculated_cost_share_ov_usr NUMBER;
1322 l_oh_value_ov_usr NUMBER;
1323 l_eb_value_ov_usr NUMBER;
1324 l_calculated_cost_share_eb_usr NUMBER;
1325 l_return_status VARCHAR2(1);
1326 l_msg_data VARCHAR2(200);
1327 l_msg_count NUMBER(10);
1328
1329 BEGIN
1330 fnd_msg_pub.initialize;
1331 x_return_status := 'S';
1332
1333 /* getting needed values if not provided */
1334 if (p_version_id is not null and p_proposal_id is not null) then
1335 if p_activity_type_code is null then
1336 select activity_type_code
1337 into l_activity_type_code
1338 from igw_proposals_all
1339 where proposal_id = p_proposal_id;
1340 end if;
1341
1342 if p_oh_rate_class_id is null then
1343 select oh_rate_class_id
1344 into l_oh_rate_class_id
1345 from igw_budgets
1346 where proposal_id = p_proposal_id
1347 and version_id = p_version_id;
1348 end if;
1349 end if;
1350
1351 open c_budget_version;
1352 fetch c_budget_version into l_enter_budget_at_period_level
1353 , l_apply_inflation_setup_rates
1354 , l_apply_eb_setup_rates
1355 , l_apply_oh_setup_rates;
1356 close c_budget_version;
1357
1358 if l_enter_budget_at_period_level = 'N' then
1359 for rec_budget_periods in c_budget_periods
1360 LOOP
1361 l_budget_period_id := rec_budget_periods.budget_period_id;
1362 for rec_budget_details in c_budget_details
1363 LOOP
1364 l_line_item_id := rec_budget_details.line_item_id;
1365 l_rate_class_id_oh := l_oh_rate_class_id;
1366 IGW_OVERHEAD_CAL.get_rate_id(rec_budget_details.expenditure_type
1367 ,rec_budget_details.expenditure_category_flag
1368 , 'O'
1369 ,l_rate_class_id_oh
1370 ,l_rate_type_id_oh
1371 ,l_return_status
1372 ,l_msg_data);
1373
1374 if l_return_status <> 'S' then
1375 raise FND_API.G_EXC_ERROR;
1376 end if;
1377
1378
1379 if rec_budget_details.personnel_attached_flag = 'N' then
1380
1381 if rec_budget_details.apply_inflation_flag = 'Y' then
1382 IGW_OVERHEAD_CAL.get_rate_id(rec_budget_details.expenditure_type
1383 ,rec_budget_details.expenditure_category_flag
1384 ,'I'
1385 , l_rate_class_id_inf
1386 ,l_rate_type_id_inf
1387 ,l_return_status
1388 ,l_msg_data);
1389 if l_return_status <> 'S' then
1390 raise FND_API.G_EXC_ERROR;
1391 end if;
1392 end if;
1393 BEGIN
1394 select apply_rate_flag, calculated_cost, calculated_cost_sharing
1395 into l_apply_rate_flag_oh, l_oh_value_ov_usr, l_calculated_cost_share_ov_usr
1396 from igw_budget_details_cal_amts
1397 where line_item_id = rec_budget_details.line_item_id;
1398 EXCEPTION
1399 when no_data_found then
1400 l_apply_rate_flag_oh := 'Y';
1401 End;
1402
1403 l_base_amount := rec_budget_details.line_item_cost;
1404
1405 /* NON_PERSONNEL: the reason why we are calculating cal_oh twice is because, once to calculate cost share amt
1406 for line and second time to calculate cost share for Overhead line. */
1407 IGW_OVERHEAD_CAL.calc_oh(p_proposal_id
1408 ,p_version_id
1409 ,l_base_amount
1410 ,rec_budget_periods.start_date
1411 ,rec_budget_periods.end_date
1412 ,l_oh_value
1413 ,l_oh_value_ov
1414 ,l_activity_type_code
1415 ,rec_budget_details.location_code
1416 ,l_rate_class_id_oh
1417 ,l_rate_type_id_oh
1418 ,l_return_status
1419 ,l_msg_data
1420 ,l_msg_count);
1421
1422 if l_return_status <> 'S' then
1423 raise FND_API.G_EXC_ERROR;
1424 end if;
1425
1426 if l_apply_rate_flag_oh = 'N' then
1427 l_oh_value_ov := 0;
1431 /*this proc is for calculationg Overhead cost share amt from line cost share amt */
1428 l_calculated_cost_share_ov := 0;
1429 elsif l_apply_rate_flag_oh = 'Y' then
1430 if l_apply_oh_setup_rates = 'Y' then
1432 IGW_OVERHEAD_CAL.calc_oh(p_proposal_id
1433 ,p_version_id
1434 ,rec_budget_details.cost_sharing_amount
1435 ,rec_budget_periods.start_date
1436 ,rec_budget_periods.end_date
1437 ,l_calculated_cost_share
1438 ,l_calculated_cost_share_ov
1439 ,l_activity_type_code
1440 ,rec_budget_details.location_code
1441 ,l_rate_class_id_oh
1442 ,l_rate_type_id_oh
1443 ,l_return_status
1444 ,l_msg_data
1445 ,l_msg_count);
1446 if l_return_status <> 'S' then
1447 raise FND_API.G_EXC_ERROR;
1448 end if;
1449 end if;
1450 end if;
1451
1452 if l_apply_oh_setup_rates = 'Y' then
1453 /* underrecovery is Oh value using setup rates minus oh value using setup/overwritten rates */
1454 update igw_budget_details
1455 set line_item_cost = l_base_amount
1456 , underrecovery_amount = nvl(l_oh_value - l_oh_value_ov,0)
1457 where line_item_id = rec_budget_details.line_item_id;
1458 else
1459 update igw_budget_details
1460 set line_item_cost = l_base_amount
1461 , underrecovery_amount = nvl(l_oh_value - l_oh_value_ov_usr,0)
1462 where line_item_id = rec_budget_details.line_item_id;
1463 end if;
1464
1465 delete from igw_budget_details_cal_amts
1466 where line_item_id = rec_budget_details.line_item_id;
1467
1468 if check_exp_assignment(rec_budget_details.expenditure_type
1469 ,rec_budget_details.expenditure_category_flag
1470 ,l_rate_class_id_oh
1471 ,l_rate_type_id_oh) then
1472
1473 if l_apply_oh_setup_rates = 'Y' then
1474 IGW_GENERATE_PERIODS.create_budget_detail_amts(p_proposal_id
1475 ,p_version_id
1476 ,rec_budget_details.budget_period_id
1477 ,rec_budget_details.line_item_id
1478 ,l_rate_class_id_oh
1479 ,l_rate_type_id_oh
1480 ,l_apply_rate_flag_oh
1481 ,nvl(l_oh_value_ov,0)
1482 ,nvl(l_calculated_cost_share_ov,0));
1483 else
1484 IGW_GENERATE_PERIODS.create_budget_detail_amts(p_proposal_id
1485 ,p_version_id
1486 ,rec_budget_details.budget_period_id
1487 ,rec_budget_details.line_item_id
1488 ,l_rate_class_id_oh
1489 ,l_rate_type_id_oh
1490 ,l_apply_rate_flag_oh
1491 ,nvl(l_oh_value_ov_usr,0)
1492 ,nvl(l_calculated_cost_share_ov_usr,0));
1493 end if;
1494 end if;
1495
1496 elsif rec_budget_details.personnel_attached_flag = 'Y' then
1497 IGW_OVERHEAD_CAL.get_rate_id(rec_budget_details.expenditure_type
1498 ,rec_budget_details.expenditure_category_flag
1499 ,'E'
1500 ,l_rate_class_id_eb,l_rate_type_id_eb,l_return_status, l_msg_data);
1501
1502 if l_return_status <> 'S' then
1503 raise FND_API.G_EXC_ERROR;
1504 end if;
1505 for rec_budget_personnel in c_budget_personnel
1506 LOOP
1507 BEGIN
1508 select pca.apply_rate_flag , calculated_cost, calculated_cost_sharing
1509 into l_apply_rate_flag_oh, l_oh_value_ov_usr, l_calculated_cost_share_ov_usr
1510 from igw_budget_personnel_cal_amts pca
1511 , igw_budget_personnel_details pbd
1512 where pbd.budget_personnel_detail_id = rec_budget_personnel.budget_personnel_detail_id
1513 and pca.budget_personnel_detail_id = pbd.budget_personnel_detail_id
1514 and pca.rate_class_id = ( select pr.rate_class_id
1515 from igw_rate_classes pr
1516 where pca.rate_class_id = pr.rate_class_id
1517 and pr.rate_class_type = 'O');
1518 EXCEPTION
1519 when no_data_found then
1520 l_apply_rate_flag_oh := 'Y';
1521 End;
1522
1523 BEGIN
1524 select pca.apply_rate_flag , calculated_cost, calculated_cost_sharing
1525 into l_apply_rate_flag_eb, l_eb_value_ov_usr, l_calculated_cost_share_eb_usr
1526 from igw_budget_personnel_cal_amts pca
1527 , igw_budget_personnel_details pbd
1528 where pbd.budget_personnel_detail_id = rec_budget_personnel.budget_personnel_detail_id
1529 and pca.budget_personnel_detail_id = pbd.budget_personnel_detail_id
1530 and pca.rate_class_id = ( select pr.rate_class_id
1531 from igw_rate_classes pr
1532 where pca.rate_class_id = pr.rate_class_id
1533 and pr.rate_class_type = 'E');
1534 EXCEPTION
1535 when no_data_found then
1536 l_apply_rate_flag_eb := 'Y';
1537 End;
1538
1539 ---get base amount
1540 select calculation_base
1541 , effective_date
1542 , appointment_type_code
1543 into l_calculation_base
1544 , l_effective_date
1545 , l_appointment_type_code
1546 from igw_budget_persons
1547 where proposal_id = p_proposal_id
1548 and version_id = p_version_id
1549 --and person_id = rec_budget_personnel.person_id
1550 and party_id = rec_budget_personnel.party_id
1554 ,p_version_id
1551 and appointment_type_code = rec_budget_personnel.appointment_type_code;
1552
1553 IGW_OVERHEAD_CAL.calc_salary(p_proposal_id
1555 ,l_calculation_base
1556 ,l_effective_date
1557 ,l_appointment_type_code
1558 ,rec_budget_personnel.start_date
1559 ,rec_budget_personnel.end_date
1560 ,l_inflated_salary
1561 ,l_inflated_salary_ov
1562 ,rec_budget_details.expenditure_type
1563 ,rec_budget_details.expenditure_category_flag
1564 ,l_activity_type_code
1565 ,rec_budget_details.location_code
1566 ,l_return_status
1567 ,l_msg_data
1568 ,l_msg_count);
1569 if l_return_status <> 'S' then
1570 raise FND_API.G_EXC_ERROR;
1571 end if;
1572
1573 l_inflated_salary := rec_budget_personnel.percent_charged/100 * l_inflated_salary;
1574
1575 l_pers_cost_sharing_amt := rec_budget_personnel.cost_sharing_percent/100 * l_inflated_salary_ov; -- Bug 2702314
1576
1577 l_inflated_salary_ov := rec_budget_personnel.percent_charged/100 * l_inflated_salary_ov;
1578 if l_apply_rate_flag_oh = 'N' then
1579 l_rate_class_id_oh_d := null;
1580 l_rate_type_id_oh_d := null;
1581 else
1582 l_rate_class_id_oh_d := l_rate_class_id_oh;
1583 l_rate_type_id_oh_d := l_rate_type_id_oh;
1584 end if;
1585 if l_apply_rate_flag_eb = 'N' then
1586 l_rate_class_id_eb_d := null;
1587 l_rate_type_id_eb_d := null;
1588 else
1589 l_rate_class_id_eb_d := l_rate_class_id_eb;
1590 l_rate_type_id_eb_d := l_rate_type_id_eb;
1591 end if;
1592
1593 /* PERSONNEL: the reason why we are calculating cal_oh_eb twice is because of multiple combinations of apply rate
1594 flag for eb and oh. we always need to calculate with setup rates(1st time). Second time we need based on
1595 apply rate flag. Cost share amt is calculed internally based on the cost share percentage unlike for NON-PERSONNEL*/
1596
1597 IGW_OVERHEAD_CAL.calc_oh_eb (p_proposal_id
1598 ,p_version_id
1599 ,l_inflated_salary_ov
1600 ,rec_budget_personnel.start_date
1601 ,rec_budget_personnel.end_date
1602 ,l_oh_value
1603 ,l_oh_value_ov
1604 ,l_eb_value
1605 ,l_eb_value_ov
1606 ,l_activity_type_code
1607 ,rec_budget_details.location_code
1608 ,l_rate_class_id_oh_d
1609 ,l_rate_type_id_oh_d
1610 ,l_rate_class_id_eb_d
1611 ,l_rate_type_id_eb_d
1612 ,l_return_status
1613 ,l_msg_data
1614 ,l_msg_count);
1615 if l_return_status <> 'S' then
1616 raise FND_API.G_EXC_ERROR;
1617 end if;
1618
1619 IGW_OVERHEAD_CAL.calc_oh_eb (p_proposal_id
1620 ,p_version_id
1621 ,l_inflated_salary_ov
1622 ,rec_budget_personnel.start_date
1623 ,rec_budget_personnel.end_date
1624 ,l_oh_value_d
1625 ,l_oh_value_ov_d
1626 ,l_eb_value_d
1627 ,l_eb_value_ov_d
1628 ,l_activity_type_code
1629 ,rec_budget_details.location_code
1630 ,l_rate_class_id_oh
1631 ,l_rate_type_id_oh
1632 ,l_rate_class_id_eb
1633 ,l_rate_type_id_eb
1634 ,l_return_status
1635 ,l_msg_data
1636 ,l_msg_count);
1637 if l_return_status <> 'S' then
1638 raise FND_API.G_EXC_ERROR;
1639 end if;
1640
1641
1642 if l_apply_rate_flag_oh = 'N' then
1643 l_oh_value_ov := 0;
1644 end if;
1645 if l_apply_rate_flag_eb = 'N' then
1646 l_eb_value_ov := 0;
1647 end if;
1648
1649 if l_apply_oh_setup_rates = 'Y' then
1650 /* underrecovery is setup calculation amt minus actual calculation */
1651 update igw_budget_personnel_details
1652 set salary_requested = l_inflated_salary_ov
1653 ,cost_sharing_amount = l_pers_cost_sharing_amt -- Bug 2702314
1654 --, cost_sharing_amount = rec_budget_personnel.cost_sharing_percent/100 *
1655 -- l_inflated_salary_ov
1656 , underrecovery_amount = nvl(l_oh_value_d - l_oh_value_ov,0)
1657 where budget_personnel_detail_id = rec_budget_personnel.budget_personnel_detail_id;
1658 else
1659 update igw_budget_personnel_details
1660 set salary_requested = l_inflated_salary_ov
1661 ,cost_sharing_amount = l_pers_cost_sharing_amt -- Bug 2702314
1662 --, cost_sharing_amount = rec_budget_personnel.cost_sharing_percent/100 *
1663 -- l_inflated_salary_ov
1664 , underrecovery_amount = nvl(l_oh_value_d - l_oh_value_ov_usr,0)
1665 where budget_personnel_detail_id = rec_budget_personnel.budget_personnel_detail_id;
1666 end if;
1667
1668
1669 delete from igw_budget_personnel_cal_amts
1670 where budget_personnel_detail_id = rec_budget_personnel.budget_personnel_detail_id;
1671
1672
1673 if check_exp_assignment(rec_budget_details.expenditure_type
1674 ,rec_budget_details.expenditure_category_flag
1675 ,l_rate_class_id_oh
1676 ,l_rate_type_id_oh) then
1677 if l_apply_oh_setup_rates = 'Y' then
1678 IGW_GENERATE_PERIODS.create_budget_personnel_amts (
1679 rec_budget_personnel.budget_personnel_detail_id
1680 ,l_rate_class_id_oh
1681 ,l_rate_type_id_oh
1682 ,l_apply_rate_flag_oh
1683 ,nvl(l_oh_value_ov,0)
1684 ,rec_budget_personnel.cost_sharing_percent/100 * nvl(l_oh_value_ov,0));
1685 else
1686 IGW_GENERATE_PERIODS.create_budget_personnel_amts (
1687 rec_budget_personnel.budget_personnel_detail_id
1688 ,l_rate_class_id_oh
1692 ,rec_budget_personnel.cost_sharing_percent/100 * nvl(l_oh_value_ov_usr,0));
1689 ,l_rate_type_id_oh
1690 ,l_apply_rate_flag_oh
1691 ,nvl(l_oh_value_ov_usr,0)
1693 end if;
1694 end if;
1695
1696 if check_exp_assignment(rec_budget_details.expenditure_type
1697 ,rec_budget_details.expenditure_category_flag
1698 ,l_rate_class_id_eb
1699 ,l_rate_type_id_eb) then
1700 if l_apply_eb_setup_rates = 'Y' then
1701 IGW_GENERATE_PERIODS.create_budget_personnel_amts (
1702 rec_budget_personnel.budget_personnel_detail_id
1703 ,l_rate_class_id_eb
1704 ,l_rate_type_id_eb
1705 ,l_apply_rate_flag_eb
1706 ,nvl(l_eb_value_ov,0)
1707 ,rec_budget_personnel.cost_sharing_percent/100 * nvl(l_eb_value,0));
1708 else
1709 IGW_GENERATE_PERIODS.create_budget_personnel_amts (
1710 rec_budget_personnel.budget_personnel_detail_id
1711 ,l_rate_class_id_eb
1712 ,l_rate_type_id_eb
1713 ,l_apply_rate_flag_eb
1714 ,nvl(l_eb_value_ov_usr,0)
1715 ,rec_budget_personnel.cost_sharing_percent/100 * nvl(l_eb_value_ov_usr,0));
1716 end if;
1717 end if;
1718
1719 END LOOP; --rec_budget_personnel
1720
1721
1722 delete from igw_budget_details_cal_amts
1723 where line_item_id = rec_budget_details.line_item_id;
1724
1725 select sum(nvl(ppc.calculated_cost,0))
1726 , sum(nvl(ppc.calculated_cost_sharing,0))
1727 into l_calculated_cost_oh
1728 , l_cost_sharing_oh
1729 from igw_budget_personnel_cal_amts ppc
1730 , igw_budget_personnel_details ppd
1731 where ppd.line_item_id = rec_budget_details.line_item_id
1732 and ppd.budget_personnel_detail_id = ppc.budget_personnel_detail_id
1733 and ppc.rate_class_id = l_rate_class_id_oh
1734 and ppc.rate_type_id = l_rate_type_id_oh;
1735
1736 select sum(nvl(ppc.calculated_cost,0))
1737 , sum(nvl(ppc.calculated_cost_sharing,0))
1738 into l_calculated_cost_eb
1739 , l_cost_sharing_eb
1740 from igw_budget_personnel_cal_amts ppc
1741 , igw_budget_personnel_details ppd
1742 where ppd.line_item_id = rec_budget_details.line_item_id
1743 and ppd.budget_personnel_detail_id = ppc.budget_personnel_detail_id
1744 and ppc.rate_class_id = l_rate_class_id_eb
1745 and ppc.rate_type_id = l_rate_type_id_eb;
1746
1747 if check_exp_assignment(rec_budget_details.expenditure_type
1748 ,rec_budget_details.expenditure_category_flag
1749 ,l_rate_class_id_oh
1750 ,l_rate_type_id_oh) then
1751 IGW_GENERATE_PERIODS.create_budget_detail_amts(rec_budget_details.proposal_id
1752 ,rec_budget_details.version_id
1753 ,rec_budget_details.budget_period_id
1754 ,rec_budget_details.line_item_id
1755 ,l_rate_class_id_oh
1756 ,l_rate_type_id_oh
1757 ,'Y'
1758 ,nvl(l_calculated_cost_oh,0)
1759 ,nvl(l_cost_sharing_oh,0));
1760 end if;
1761
1762 if check_exp_assignment(rec_budget_details.expenditure_type
1763 ,rec_budget_details.expenditure_category_flag
1764 ,l_rate_class_id_eb
1765 ,l_rate_type_id_eb) then
1766 IGW_GENERATE_PERIODS.create_budget_detail_amts(rec_budget_details.proposal_id
1767 ,rec_budget_details.version_id
1768 ,rec_budget_details.budget_period_id
1769 ,rec_budget_details.line_item_id
1770 ,l_rate_class_id_eb
1771 ,l_rate_type_id_eb
1772 ,'Y'
1773 ,nvl(l_calculated_cost_eb,0)
1774 ,nvl(l_cost_sharing_eb,0));
1775 end if;
1776
1777 --changed as recalculate should insert and delete overhead and eb amounts
1778
1779 select sum(nvl(ppd.salary_requested,0))
1780 , sum(nvl(ppd.cost_sharing_amount,0))
1781 , sum(nvl(ppd.underrecovery_amount,0))
1782 into l_salary_requested_upd
1783 , l_cost_sharing_amount_upd
1784 , l_underrecovery_amount_upd
1785 from igw_budget_personnel_details ppd
1786 where ppd.line_item_id = rec_budget_details.line_item_id;
1787
1788 update igw_budget_details pdb
1789 set line_item_cost = nvl(l_salary_requested_upd,0)
1790 , cost_sharing_amount = nvl(l_cost_sharing_amount_upd,0)
1791 , underrecovery_amount = nvl(l_underrecovery_amount_upd,0)
1792 where pdb.line_item_id = rec_budget_details.line_item_id;
1793
1794 end if; --rec_budget_details.personnel_attached_flag
1795 end LOOP; --rec_budget_details
1796
1797
1798 select nvl(sum(line_item_cost),0)
1799 , nvl(sum(cost_sharing_amount),0)
1800 , nvl(sum(underrecovery_amount),0)
1801 into l_direct_cost1
1802 , l_cost_share1
1803 , l_underrecovery
1804 from igw_budget_details
1805 where proposal_id = p_proposal_id
1806 and version_id = p_version_id
1807 and budget_period_id = rec_budget_periods.budget_period_id;
1808
1809 select nvl(sum(calculated_cost_sharing),0)
1810 , nvl(sum(calculated_cost),0)
1811 into l_cost_share2
1812 , l_indirect_cost
1813 from igw_budget_details_cal_amts pc
1814 where proposal_id = p_proposal_id
1815 and version_id = p_version_id
1816 and budget_period_id = rec_budget_periods.budget_period_id
1817 and pc.rate_class_id = ( select pr.rate_class_id
1818 from igw_rate_classes pr
1819 where pc.rate_class_id = pr.rate_class_id
1820 and pr.rate_class_type = 'O');
1821 select nvl(sum(calculated_cost_sharing),0)
1822 , nvl(sum(calculated_cost),0)
1823 into l_cost_share3
1824 , l_direct_cost2
1825 from igw_budget_details_cal_amts pc
1826 where proposal_id = p_proposal_id
1827 and version_id = p_version_id
1828 and budget_period_id = rec_budget_periods.budget_period_id
1829 and pc.rate_class_id = ( select pr.rate_class_id
1830 from igw_rate_classes pr
1831 where pc.rate_class_id = pr.rate_class_id
1832 and pr.rate_class_type = 'E');
1833
1834 update igw_budget_periods
1835 set total_cost = (l_direct_cost1+l_direct_cost2+l_indirect_cost)
1836 , total_direct_cost = (l_direct_cost1+l_direct_cost2)
1837 , total_indirect_cost = l_indirect_cost
1838 , cost_sharing_amount = (l_cost_share1+l_cost_share2+l_cost_share3)
1839 , underrecovery_amount = l_underrecovery
1840 where proposal_id = p_proposal_id
1841 and version_id = p_version_id
1842 and budget_period_id = rec_budget_periods.budget_period_id;
1843
1844 end LOOP; --rec_budget_periods
1845 elsif l_enter_budget_at_period_level = 'Y' then
1846 for rec_budget_periods in c_budget_periods
1847 LOOP
1848 update igw_budget_periods
1849 set total_cost = nvl(rec_budget_periods.total_direct_cost,0) + nvl(rec_budget_periods.total_indirect_Cost,0)
1850 where proposal_id = p_proposal_id
1851 and version_id = p_version_id
1852 and budget_period_id = rec_budget_periods.budget_period_id;
1853 END LOOP; --rec_budget_period and l_enter_budget_at_period_level = 'Y'
1854
1855 end if; --l_enter_budget_at_period_level = 'N'
1856
1857
1858 select nvl(sum(total_cost),0)
1859 , nvl(sum(total_direct_cost),0)
1860 , nvl(sum(total_indirect_cost),0)
1861 , nvl(sum(cost_sharing_amount),0)
1862 , nvl(sum(underrecovery_amount),0)
1863 --, nvl(sum(total_cost_limit),0)
1864 into l_total_cost
1865 , l_total_direct_cost
1866 , l_total_indirect_cost
1867 , l_cost_sharing_amt
1868 , l_underrecovery_amount
1869 --, l_total_cost_limit
1870 from igw_budget_periods
1871 where proposal_id = p_proposal_id
1872 and version_id = p_version_id;
1873
1874
1875 update igw_budgets
1876 set total_cost = l_total_cost
1877 , total_direct_cost = l_total_direct_cost
1878 , total_indirect_cost = l_total_indirect_cost
1879 , cost_sharing_amount = l_cost_sharing_amt
1880 , underrecovery_amount = l_underrecovery_amount
1881 --, total_cost_limit = l_total_cost_limit
1882 where proposal_id = p_proposal_id
1883 and version_id = p_version_id;
1884
1885 x_return_status := 'S';
1886 EXCEPTION
1887 when FND_API.G_EXC_ERROR then
1888 x_return_status := l_return_status;
1889 x_msg_data := l_msg_data;
1890 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1891 p_data => x_msg_data);
1892
1893 when others then
1894 x_return_status := 'U';
1895 x_msg_data := SQLCODE||' '||SQLERRM;
1896 fnd_msg_pub.add_exc_msg(G_PKG_NAME, 'RECALCULATE_BUDGET');
1897 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1898 p_data => x_msg_data);
1899 end recalculate_budget;
1900
1901
1902 END IGW_BUDGET_OPERATIONS;