[Home] [Help]
PACKAGE BODY: APPS.IGW_BUDGETS_PVT
Source
1 PACKAGE BODY IGW_BUDGETS_PVT AS
2 --$Header: igwvbvsb.pls 115.35 2004/04/14 22:28:47 vmedikon ship $
3
4 procedure manage_budget_deletion
5 (p_delete_level VARCHAR2
6 ,p_proposal_id NUMBER
7 ,p_version_id NUMBER
8 ,p_budget_period_id NUMBER := null
9 ,p_line_item_id NUMBER := null
10 ,p_budget_personnel_detail_id NUMBER := null
11 ,x_return_status OUT NOCOPY VARCHAR2) IS
12 /* possible values of p_delete_level are 'BUDGET_VERSION', 'BUDGET_PERIOD', 'BUDGET_LINE', 'BUDGET_PERSONNEL' */
13 l_api_name varchar2(30) := 'MANAGE_BUDGET_DELETION';
14 begin
15 if p_delete_level = 'BUDGET_VERSION' then
16
17 delete from igw_budget_persons
18 where proposal_id = p_proposal_id
19 and version_id = p_version_id;
20
21 delete from igw_budget_personnel_cal_amts pbp
22 where pbp.budget_personnel_detail_id IN (select pb.budget_personnel_detail_id
23 from igw_budget_personnel_details pb
24 where pb.proposal_id = p_proposal_id
25 and pb.version_id = p_version_id);
26
27
28 delete from igw_budget_personnel_details
29 where proposal_id = p_proposal_id
30 and version_id = p_version_id;
31
32 delete from igw_budget_details_cal_amts
33 where proposal_id = p_proposal_id
34 and version_id = p_version_id;
35
36 delete from igw_budget_details
37 where proposal_id = p_proposal_id
38 and version_id = p_version_id;
39
40 delete from igw_budget_periods
41 where proposal_id = p_proposal_id
42 and version_id = p_version_id;
43
44 delete from igw_prop_rates
45 where proposal_id = p_proposal_id
46 and version_id = p_version_id;
47
48 elsif p_delete_level = 'BUDGET_PERIOD' then
49
50 delete from igw_budget_personnel_cal_amts pbp
51 where pbp.budget_personnel_detail_id IN (select pb.budget_personnel_detail_id
52 from igw_budget_personnel_details pb
53 where pb.proposal_id = p_proposal_id
54 and pb.version_id = p_version_id
55 and pb.budget_period_id = p_budget_period_id);
56
57 delete from igw_budget_personnel_details
58 where proposal_id = p_proposal_id
59 and version_id = p_version_id
60 and budget_period_id = p_budget_period_id;
61
62 delete from igw_budget_details_cal_amts
63 where proposal_id = p_proposal_id
64 and version_id = p_version_id
65 and budget_period_id = p_budget_period_id;
66
67 delete from igw_budget_details
68 where proposal_id = p_proposal_id
69 and version_id = p_version_id
70 and budget_period_id = p_budget_period_id;
71
72 elsif p_delete_level = 'BUDGET_LINE' then
73
74 delete from igw_budget_personnel_cal_amts pbp
75 where pbp.budget_personnel_detail_id IN (select pb.budget_personnel_detail_id
76 from igw_budget_personnel_details pb
77 where pb.line_item_id = p_line_item_id);
78
79 delete from igw_budget_personnel_details
80 where line_item_id = p_line_item_id;
81
82 delete from igw_budget_details_cal_amts
83 where line_item_id = p_line_item_id;
84
85 elsif p_delete_level = 'BUDGET_PERSONNEL' then
86
87 delete from igw_budget_personnel_cal_amts pbp
88 where pbp.budget_personnel_detail_id = p_budget_personnel_detail_id;
89
90 end if;
91 exception
92 when others then
93 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
94 Fnd_Msg_Pub.Add_Exc_Msg(
95 p_pkg_name => G_package_name,
96 p_procedure_name => l_api_name);
97 RAISE Fnd_Api.G_Exc_Unexpected_Error;
98 end;
99 ---------------------------------------------------------------------------------------
100
101 PROCEDURE copy_budget(p_proposal_id IN NUMBER
102 ,p_proposal_installment_id IN NUMBER
103 ,x_return_status OUT NOCOPY VARCHAR2
104 ,x_msg_data OUT NOCOPY VARCHAR2
105 ,x_msg_count OUT NOCOPY NUMBER) is
106
107 cursor c_budget_details is
108 SELECT ibc.proposal_id
109 , ibc.budget_period_id
110 , ibc.expenditure_type
111 , ibc.expenditure_category_flag
112 , nvl(ibc.line_item_cost,0)+nvl(igw_budget_integration.get_eb_cost_ss(ibc.line_item_id),0) direct_cost
113 , ibc.line_item_cost
114 , igw_budget_integration.get_oh_cost_ss(ibc.line_item_id) indirect_cost
115 , ibp.start_date
116 , ibp.end_date
117 FROM igw_budgets ib
118 , igw_budget_periods ibp
119 , igw_budget_details ibc
120 WHERE ib.proposal_id = ibp.proposal_id
121 AND ib.version_id = ibp.version_id
122 AND ib.final_version_flag = 'Y'
123 AND ibp.proposal_id = ibc.proposal_id
124 AND ibp.version_id = ibc.version_id
125 AND ibp.budget_period_id = ibc.budget_period_id
126 AND ib.proposal_id = ibc.proposal_id
127 AND ib.version_id = ibc.version_id
128 AND ib.proposal_id = p_proposal_id;
129
130 /* commented out for cursor c_budget_Details as rounded amounts were coming up due to
131 igw_budget_complete_v */
132 /*
133 SELECT ibc.proposal_id
134 , ibc.budget_period_id
135 , ibc.expenditure_type
136 , ibc.expenditure_category_flag
137 , nvl(ibc.line_item_cost,0)+nvl(ibc.eb_cost,0) direct_cost
138 , ibc.oh_cost indirect_cost
139 , ibp.start_date
140 , ibp.end_date
141 FROM igw_budgets ib
142 , igw_budget_periods ibp
143 , igw_budget_complete_v ibc
144 WHERE ib.proposal_id = ibp.proposal_id
145 AND ib.version_id = ibp.version_id
146 AND ib.final_version_flag = 'Y'
147 AND ibp.proposal_id = ibc.proposal_id
148 AND ibp.version_id = ibc.version_id
149 AND ibp.budget_period_id = ibc.budget_period_id
150 AND ib.proposal_id = ibc.proposal_id
151 AND ib.version_id = ibc.version_id
152 AND ib.proposal_id = p_proposal_id;
153 */
154
155
156 cursor c_budget_indirect_cost is
157 SELECT ibc.budget_period_id
158 , sum(igw_budget_integration.get_oh_cost_ss(ibc.line_item_id)) indirect_cost
159 FROM igw_budgets ib
160 , igw_budget_periods ibp
161 , igw_budget_details ibc
162 WHERE ib.proposal_id = ibp.proposal_id
163 AND ib.version_id = ibp.version_id
164 AND ib.final_version_flag = 'Y'
165 AND ibp.proposal_id = ibc.proposal_id
166 AND ibp.version_id = ibc.version_id
167 AND ibp.budget_period_id = ibc.budget_period_id
168 AND ib.proposal_id = ibc.proposal_id
169 AND ib.version_id = ibc.version_id
170 AND ib.proposal_id = p_proposal_id
171 GROUP BY ibc.budget_period_id;
172
173 /* commented out for cursor c_budget_Details as rounded amounts were coming up due to
174 igw_budget_complete_v */
175 /*
176 SELECT ibc.budget_period_id
177 , sum(ibc.oh_cost) indirect_cost
178 FROM igw_budgets ib
179 , igw_budget_periods ibp
180 , igw_budget_complete_v ibc
181 WHERE ib.proposal_id = ibp.proposal_id
182 AND ib.version_id = ibp.version_id
183 AND ib.final_version_flag = 'Y'
184 AND ibp.proposal_id = ibc.proposal_id
185 AND ibp.version_id = ibc.version_id
186 AND ibp.budget_period_id = ibc.budget_period_id
187 AND ib.proposal_id = ibc.proposal_id
188 AND ib.version_id = ibc.version_id
189 AND ib.proposal_id = p_proposal_id
190 GROUP BY ibc.budget_period_id;
191 */
192
193 l_award_budget_id NUMBER(15);
194 l_award_id NUMBER(15);
195 l_project_id NUMBER(15);
196 l_task_id NUMBER(15);
197 l_version_id NUMBER(15);
198 l_period_name VARCHAR2(30);
199 l_start_date DATE;
200 l_end_date DATE;
201 l_time_phased_type_code VARCHAR2(30);
202 l_awd_start_date DATE;
203 l_awd_end_date DATE;
204 l_proj_start_date DATE;
205 l_proj_end_date DATE;
206 l_budget_start_date DATE;
207 l_budget_end_date DATE;
208 l_return_status VARCHAR2(1);
209 l_msg_data VARCHAR2(200);
210 l_msg_count NUMBER(10);
211 x_rowid ROWID;
212 l_entry_level_code varchar2(30);
213
214 BEGIN
215 --fnd_msg_pub.initialize;
216 x_return_status := 'S';
217
218 -- dbms_output.put_line('--------till here 1----------');
219
220 SELECT version_id
221 INTO l_version_id
222 FROM igw_budgets
223 WHERE proposal_id = p_proposal_id
224 AND final_version_flag = 'Y';
225
226 -- dbms_output.put_line('--------till here 2----------');
227
228 select project_id, task_id
229 into l_project_id, l_task_id
230 from igw_project_fundings
231 where proposal_installment_id = p_proposal_installment_id
232 AND ROWNUM < 2;
233
234 -- dbms_output.put_line('--------till here 3----------');
235
236 select ia.award_id
237 into l_award_id
238 from igw_awards ia,
239 igw_installments ii
240 where ii.proposal_installment_id = p_proposal_installment_id
241 and ii.proposal_award_id = ia.proposal_award_id;
242
243 -- dbms_output.put_line('--------till here 4----------');
244
245 select pbem.time_phased_type_code
246 , pbem.entry_level_code
247 into l_time_phased_type_code
248 , l_entry_level_code
249 from pa_projects_all pp
250 , pa_project_types_all ppt
251 , pa_budget_entry_methods pbem
252 where pp.project_id = l_project_id
253 and pp.project_type = ppt.project_type
254 and ppt.cost_budget_entry_method_code = pbem.budget_entry_method_code;
255
256
257 -- use l_budget_start_date and l_budget_end_date obtained below if time_phased_type_code = 'N'
258 select nvl(preaward_date, start_date_active), end_date_active
259 into l_awd_start_date, l_awd_end_date
260 from gms_awards_all
261 where award_id = l_award_id;
262
263 -- dbms_output.put_line('--------till here 6----------');
264
265 select nvl(start_date,l_awd_start_date), nvl(completion_date,l_awd_end_date)
266 into l_proj_start_date, l_proj_end_date
267 from pa_projects_all
268 where project_id = l_project_id;
269
270 -- dbms_output.put_line('--------till here 7----------');
271
272 l_msg_data := 'after project date selection';
273
274 l_budget_start_date := greatest(l_awd_start_date,l_proj_start_date);
275 l_budget_end_date := least(l_awd_end_date,l_proj_end_date);
276
277 /* -- commented by Debashis and rewritten below
278 Bug 2702671 (ENTER AWARD BUDGET SCREEN: THE DEFAULT TASK NUMBER IS NOT CORRECT)
279 --populate the task id if project requires task
280 if l_entry_level_code in ('T','M') then
281 select task_id
282 into l_task_id
283 from pa_tasks_top_v
284 where project_id = l_project_id
285 and wbs_sort_order = (select min(wbs_sort_order) from pa_tasks_top_v where project_id = l_project_id);
286 elsif l_entry_level_code = 'L' then
287 select task_id
288 into l_task_id
289 from pa_tasks_lowest_v
290 where project_id = l_project_id
291 and wbs_sort_order = (select min(wbs_sort_order) from pa_tasks_lowest_v where project_id = l_project_id);
292 end if;
293 */
294 if l_entry_level_code = 'T' then
295 -- l_task_id is already known since funding is always at top task level
296 null;
297 elsif l_entry_level_code = 'L' then
298 --bug 3527151 no data found raising exception
299 begin
300 select task_id
301 into l_task_id
302 from pa_tasks_lowest_v
303 where project_id = l_project_id
304 and top_task_id = l_task_id
305 and wbs_sort_order = (select min(wbs_sort_order)
306 from pa_tasks_lowest_v
307 where project_id = l_project_id
308 and top_task_id = l_task_id);
309 exception
310 when no_data_found then null;
311 end;
312 elsif l_entry_level_code = 'M' then
313 --bug 3527151 no data found raising exception
314 begin
315 select task_id
316 into l_task_id
317 from pa_tasks
318 where project_id = l_project_id
319 and (PA_TASK_UTILS.CHECK_CHILD_EXISTS(TASK_ID) = 0 or TOP_TASK_ID = task_id)
320 and top_task_id = l_task_id
321 and rownum < 2;
322 exception
323 when no_data_found then null;
324 end;
325 end if;
326
327
328 for rec_budget_details in c_budget_details
329 LOOP
330 select igw_award_budget_s.nextval into l_award_budget_id from dual;
331
332 if (l_time_phased_type_code IN ('G', 'P')) then
333 Begin
334 --dbms_output.put_line('--------till here 8----------');
335 --dbms_output.put_line('start date'||greatest(rec_budget_details.start_date, l_budget_start_date));
336 --dbms_output.put_line('end date'||least(rec_budget_details.end_date ,l_budget_end_date) );
337 --dbms_output.put_line('l_time_phased_type_code'||l_time_phased_type_code);
338 select period_name
339 into l_period_name
340 from pa_budget_periods_v pv
341 where period_type_code = l_time_phased_type_code
342 and period_start_date >= l_budget_start_date
343 and period_end_date <= l_budget_end_date
344 and rownum < 2;
345 -- dbms_output.put_line('--------till here 9----------');
346 Exception
347 when no_data_found then
348 Fnd_Message.SET_NAME('IGW','IGW_SS_XFER_NO_PERIOD_FOUND');
349 Fnd_Message.set_token('EXPENDITURE', rec_budget_details.expenditure_type);
350 Fnd_Message.set_token('START_DATE', rec_budget_details.start_date);
351 Fnd_Message.set_token('END_DATE', rec_budget_details.end_date);
352 Fnd_Msg_Pub.ADD;
353 x_return_status := 'E';
354 End;
355
356 igw_award_budgets_tbh.insert_row(
357 p_award_budget_id => l_award_budget_id
358 ,p_proposal_installment_id => p_proposal_installment_id
359 ,p_budget_period_id => rec_budget_details.budget_period_id
360 ,p_expenditure_type_cat => rec_budget_details.expenditure_type
361 ,p_expenditure_category_flag => rec_budget_details.expenditure_category_flag
362 ,p_budget_amount => rec_budget_details.direct_cost
363 ,p_indirect_flag => 'N'
364 ,p_project_id => l_project_id
365 ,p_task_id => l_task_id
366 ,p_period_name => l_period_name
367 ,p_start_date => rec_budget_details.start_date
368 ,p_end_date => rec_budget_details.end_date
369 ,p_transferred_flag => 'N'
370 ,x_rowid => x_rowid
371 ,x_return_status => l_return_status);
372
373 elsif (l_time_phased_type_code = 'R') then
374 -- Bug 2702677 (ENTER AWARD BUDGET SCREEN: THE START, END DATE SHOULD DEFAULT FROM INSTALLMENT)
375 -- This should only happen for time phase = R
376 select start_date, end_date
377 into l_start_date, l_end_date
378 from igw_installments
379 where proposal_installment_id = p_proposal_installment_id;
380
381 -- dbms_output.put_line('--------till here 10----------');
382 igw_award_budgets_tbh.insert_row(
383 p_award_budget_id => l_award_budget_id
384 ,p_proposal_installment_id => p_proposal_installment_id
385 ,p_budget_period_id => rec_budget_details.budget_period_id
386 ,p_expenditure_type_cat => rec_budget_details.expenditure_type
387 ,p_expenditure_category_flag => rec_budget_details.expenditure_category_flag
388 ,p_budget_amount => rec_budget_details.direct_cost
389 ,p_indirect_flag => 'N'
390 ,p_project_id => l_project_id
391 ,p_task_id => l_task_id
392 ,p_period_name => null
393 ,p_start_date => l_start_date -- rec_budget_details.start_date
394 ,p_end_date => l_end_date -- rec_budget_details.end_date
395 ,p_transferred_flag => 'N'
396 ,x_rowid => x_rowid
397 ,x_return_status => l_return_status);
398 elsif (l_time_phased_type_code = 'N') then
399 -- dbms_output.put_line('--------till here 11----------');
400 igw_award_budgets_tbh.insert_row(
401 p_award_budget_id => l_award_budget_id
402 ,p_proposal_installment_id => p_proposal_installment_id
403 ,p_budget_period_id => rec_budget_details.budget_period_id
404 ,p_expenditure_type_cat => rec_budget_details.expenditure_type
405 ,p_expenditure_category_flag => rec_budget_details.expenditure_category_flag
406 ,p_budget_amount => rec_budget_details.direct_cost
407 ,p_indirect_flag => 'N'
408 ,p_project_id => l_project_id
409 ,p_task_id => l_task_id
410 ,p_period_name => null
411 ,p_start_date => l_budget_start_date
412 ,p_end_date => l_budget_end_date
413 ,p_transferred_flag => 'N'
414 ,x_rowid => x_rowid
415 ,x_return_status => l_return_status);
416 end if;
417
418
419 END LOOP;
420
421 for rec_budget_indirect_cost in c_budget_indirect_cost
422 LOOP
423 if (rec_budget_indirect_cost.indirect_cost > 0) then
424 select igw_award_budget_s.nextval into l_award_budget_id from dual;
425
426 select start_date, end_date
427 into l_start_date, l_end_date
428 from igw_budget_periods
429 where proposal_id = p_proposal_id
430 and version_id = l_version_id
431 and budget_period_id = rec_budget_indirect_cost.budget_period_id;
432
433 if (l_time_phased_type_code IN ('G', 'P')) then
434 Begin
435 select period_name
436 into l_period_name
437 from pa_budget_periods_v
438 where period_type_code = l_time_phased_type_code
439 and period_start_date >= l_budget_start_date
440 and period_end_date <= l_budget_end_date
441 and rownum < 2;
442 Exception
443 when no_data_found then
444 Fnd_Message.SET_NAME('IGW','IGW_SS_XFER_INVALID_PERIODNAME'); --change this message
445 Fnd_Msg_Pub.ADD;
446 x_return_status := 'E';
447 End;
448
449 igw_award_budgets_tbh.insert_row(
450 p_award_budget_id => l_award_budget_id
451 ,p_proposal_installment_id => p_proposal_installment_id
452 ,p_budget_period_id => rec_budget_indirect_cost.budget_period_id
453 ,p_expenditure_type_cat => null
454 ,p_expenditure_category_flag => null
455 ,p_budget_amount => rec_budget_indirect_cost.indirect_cost
456 ,p_indirect_flag => 'Y'
457 ,p_project_id => l_project_id
458 ,p_task_id => l_task_id
459 ,p_period_name => l_period_name
460 ,p_start_date => l_start_date
461 ,p_end_date => l_end_date
462 ,p_transferred_flag => 'N'
463 ,x_rowid => x_rowid
464 ,x_return_status => l_return_status);
465 elsif (l_time_phased_type_code = 'R') then
466
467 -- Bug 2702677 (ENTER AWARD BUDGET SCREEN: THE START, END DATE SHOULD DEFAULT FROM INSTALLMENT)
468 -- This should only happen for time phase = R
469 select start_date, end_date
470 into l_start_date, l_end_date
471 from igw_installments
472 where proposal_installment_id = p_proposal_installment_id;
473
474 igw_award_budgets_tbh.insert_row(
475 p_award_budget_id => l_award_budget_id
476 ,p_proposal_installment_id => p_proposal_installment_id
477 ,p_budget_period_id => rec_budget_indirect_cost.budget_period_id
478 ,p_expenditure_type_cat => null
479 ,p_expenditure_category_flag => null
480 ,p_budget_amount => rec_budget_indirect_cost.indirect_cost
481 ,p_indirect_flag => 'Y'
482 ,p_project_id => l_project_id
483 ,p_task_id => l_task_id
484 ,p_period_name => null
485 ,p_start_date => l_start_date
486 ,p_end_date => l_end_date
487 ,p_transferred_flag => 'N'
488 ,x_rowid => x_rowid
489 ,x_return_status => l_return_status);
490 elsif (l_time_phased_type_code = 'N') then
491 igw_award_budgets_tbh.insert_row(
492 p_award_budget_id => l_award_budget_id
493 ,p_proposal_installment_id => p_proposal_installment_id
494 ,p_budget_period_id => rec_budget_indirect_cost.budget_period_id
495 ,p_expenditure_type_cat => null
496 ,p_expenditure_category_flag => null
497 ,p_budget_amount => rec_budget_indirect_cost.indirect_cost
498 ,p_indirect_flag => 'Y'
499 ,p_project_id => l_project_id
500 ,p_task_id => l_task_id
501 ,p_period_name => null
502 ,p_start_date => l_budget_start_date
503 ,p_end_date => l_budget_end_date
504 ,p_transferred_flag => 'N'
505 ,x_rowid => x_rowid
506 ,x_return_status => l_return_status);
507 end if;
508 end if;
509 END LOOP;
510
511 EXCEPTION
512 when FND_API.G_EXC_ERROR then
513 x_return_status := 'E';
514 x_msg_data := l_msg_data;
515 fnd_msg_pub.count_and_get(p_count => x_msg_count,
516 p_data => x_msg_data);
517 raise;
518 when others then
519 x_return_status := 'U';
520 x_msg_data := SQLCODE||' '||SQLERRM;
521 --dbms_output.put_line(x_msg_data);
522 fnd_msg_pub.add_exc_msg(G_package_name, 'COPY_BUDGET');
523 fnd_msg_pub.count_and_get(p_count => x_msg_count,
524 p_data => x_msg_data);
525 raise;
526 END copy_budget;
527
528 ---------------------------------------------------------------------------------------
529 PROCEDURE copy_final_to_award_budget(
530 p_proposal_id IN NUMBER
531 ,p_proposal_installment_id IN NUMBER
532 ,x_return_status OUT NOCOPY VARCHAR2
533 ,x_msg_data OUT NOCOPY VARCHAR2
534 ,x_msg_count OUT NOCOPY NUMBER) is
535
536
537 l_api_name varchar2(30) := 'COPY_FINAL_TO_AWARD_BUDGET';
538 l_final_version number(4);
539 l_award_budget_count NUMBER(4);
540 l_return_status VARCHAR2(1);
541 l_msg_count NUMBER;
542 l_msg_data VARCHAR2(250);
543
544 BEGIN
545 fnd_msg_pub.initialize;
546 x_return_status := 'S';
547
548 begin
549 -- dbms_output.put_line('copy finaL 1');
550 select version_id
551 into l_final_version
552 from igw_budgets
553 where proposal_id = p_proposal_id
554 and final_version_flag = 'Y';
555 exception
556 when no_data_found then
557 x_return_status := Fnd_Api.G_Ret_Sts_Error;
558 Fnd_Message.Set_Name('IGW','IGW_SS_BUD_NO_FINAL_VERSION');
559 Fnd_Msg_Pub.Add;
560 RAISE FND_API.G_EXC_ERROR;
561 end;
562
563
564 begin
565 select count(*)
566 into l_award_budget_count
567 from igw_award_budgets
568 where proposal_installment_id = p_proposal_installment_id
569 and transferred_flag = 'N';
570 end ;
571 -- dbms_output.put_line('copy finaL 1, count' || l_award_budget_count);
572
573 if l_award_budget_count = 0 then
574
575 if l_final_version is not null then
576
577 copy_budget(p_proposal_id => p_proposal_id
578 ,p_proposal_installment_id => p_proposal_installment_id
579 ,x_return_status => l_return_status
580 ,x_msg_data => l_msg_data
581 ,x_msg_count => l_msg_count);
582
583 if l_return_status <> 'S' then
584 x_return_status := 'E';
585 RAISE FND_API.G_EXC_ERROR;
586 end if;
587 end if;
588 end if;
589
590 --following commit needed as it is called before rendering a screen
591 COMMIT;
592
593 EXCEPTION
594 WHEN FND_API.G_EXC_ERROR THEN
595 x_return_status := 'E';
596 fnd_msg_pub.count_and_get(p_count => x_msg_count
597 ,p_data => x_msg_data);
598 rollback;
599 when others then
600 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
601 Fnd_Msg_Pub.Add_Exc_Msg(
602 p_pkg_name => G_package_name,
603 p_procedure_name => l_api_name);
604 fnd_msg_pub.count_and_get(p_count => x_msg_count
605 ,p_data => x_msg_data);
606 rollback;
607 RAISE Fnd_Api.G_Exc_Unexpected_Error;
608 END;
609 -----------------------------------------------------------------------------------------
610 procedure get_rate_class_id(p_rate_class_name IN VARCHAR2
611 , x_rate_class_id OUT NOCOPY NUMBER
612 , x_return_status OUT NOCOPY VARCHAR2) is
613
614 l_api_name varchar2(30) := 'GET_RATE_CLASS_ID';
615
616 begin
617 select rate_class_id
618 into x_rate_class_id
619 from igw_rate_classes
620 where description = p_rate_class_name;
621
622 exception
623 when no_data_found OR too_many_rows then
624 x_return_status := Fnd_Api.G_Ret_Sts_Error;
625 Fnd_Message.Set_Name('IGW','IGW_SS_BUD_RATE_CLASS_INV');
626 Fnd_Msg_Pub.Add;
627 when others then
628 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
629 Fnd_Msg_Pub.Add_Exc_Msg(
630 p_pkg_name => G_package_name,
631 p_procedure_name => l_api_name);
632 RAISE Fnd_Api.G_Exc_Unexpected_Error;
633 end; --get_rate_class_id
634 ------------------------------------------------------------------------------------------------
635
636 procedure check_final_version(p_proposal_id IN NUMBER
637 ,p_version_id IN NUMBER
638 , x_return_status OUT NOCOPY VARCHAR2) is
639
640 l_api_name varchar2(30) := 'CHECK_FINAL_VERSION';
641 l_final_version_flag varchar2(1);
642
643 begin
644 x_return_status := 'S';
645
646 select final_version_flag
647 into l_final_version_flag
648 from igw_budgets
649 where proposal_id = p_proposal_id
650 and version_id <> nvl(p_version_id,0)
651 and final_version_flag = 'Y';
652
653 if l_final_version_flag = 'Y' then
654 x_return_status := Fnd_Api.G_Ret_Sts_Error;
655 Fnd_Message.Set_Name('IGW','IGW_SS_BUD_DUP_FINAL_VERSION');
656 Fnd_Msg_Pub.Add;
657 end if;
658
659 exception
660 when no_data_found then
661 null;
662 when others then
663 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
664 Fnd_Msg_Pub.Add_Exc_Msg(
665 p_pkg_name => G_package_name,
666 p_procedure_name => l_api_name);
667 RAISE Fnd_Api.G_Exc_Unexpected_Error;
668 end; --get_rate_class_id
669 ---------------------------------------------------------------------------------------
670 procedure validate_budget_entry(p_proposal_id IN NUMBER
671 ,p_version_id IN NUMBER
672 ,x_return_status OUT NOCOPY VARCHAR2) is
673
674 l_api_name varchar2(30) := 'VALIDATE_BUDGET_ENTRY';
675 l_line_item_id number(15);
676
677 cursor c_budget_lines is
678 select line_item_id
679 from igw_budget_details
680 where proposal_id = p_proposal_id
681 and version_id = p_version_id;
682 begin
683 x_return_status := 'S';
684
685 open c_budget_lines;
686 fetch c_budget_lines into l_line_item_id;
687 close c_budget_lines;
688 if l_line_item_id is not null then
689 x_return_status := Fnd_Api.G_Ret_Sts_Error;
690 Fnd_Message.Set_Name('IGW','IGW_SS_BUD_LINES_EXIST');
691 Fnd_Msg_Pub.Add;
692 end if;
693
694 exception
695 when others then
696 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
697 Fnd_Msg_Pub.Add_Exc_Msg(
698 p_pkg_name => G_package_name,
699 p_procedure_name => l_api_name);
700 RAISE Fnd_Api.G_Exc_Unexpected_Error;
701 end; --validate_budget_entry
702 --------------------------------------------------------------------------------
703
704 procedure validate_sponsor_hierarchy(p_proposal_form_number IN VARCHAR2
705 ,x_proposal_form_number OUT NOCOPY VARCHAR2
706 ,x_return_status OUT NOCOPY VARCHAR2) is
707
708 l_api_name varchar2(30) := 'VALIDATE_SPONSOR_HIERARCHY';
709 begin
710 select distinct proposal_form_number
711 into x_proposal_form_number
712 from igw_report_seed_header_v
713 where proposal_form_number = p_proposal_form_number;
714 exception
715 when no_data_found OR too_many_rows then
716 x_return_status := Fnd_Api.G_Ret_Sts_Error;
717 Fnd_Message.Set_Name('IGW','IGW_SS_BUD_SPONSOR_HIERAR_INV');
718 Fnd_Msg_Pub.Add;
719 when others then
720 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
721 Fnd_Msg_Pub.Add_Exc_Msg(
722 p_pkg_name => G_package_name,
723 p_procedure_name => l_api_name);
724 RAISE Fnd_Api.G_Exc_Unexpected_Error;
725 end; --get_rate_class_id
726
727 ----------------------------------------------------------------------------------
728 procedure create_budget_version
729 (p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
730 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
731 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
732 ,p_proposal_id NUMBER
733 ,p_version_id NUMBER
734 ,p_start_date DATE := null
735 ,p_end_date DATE := null
736 ,p_total_cost NUMBER := 0
737 ,p_total_direct_cost NUMBER := 0
738 ,p_total_indirect_cost NUMBER := 0
739 ,p_cost_sharing_amount NUMBER := 0
740 ,p_underrecovery_amount NUMBER := 0
741 ,p_residual_funds NUMBER := 0
742 ,p_total_cost_limit NUMBER
743 ,p_oh_rate_class_id NUMBER
744 ,p_oh_rate_class_name VARCHAR2
745 ,p_proposal_form_number VARCHAR2
746 ,p_comments VARCHAR2
747 ,p_final_version_flag VARCHAR2 := 'N'
748 ,p_budget_type_code VARCHAR2 := 'PROPOSAL_BUDGET'
749 ,p_enter_budget_at_period_level VARCHAR2
750 ,p_apply_inflation_setup_rates VARCHAR2
751 ,p_apply_eb_setup_rates VARCHAR2
752 ,p_apply_oh_setup_rates VARCHAR2
753 ,p_attribute_category VARCHAR2 := null
754 ,p_attribute1 VARCHAR2 := null
755 ,p_attribute2 VARCHAR2 := null
756 ,p_attribute3 VARCHAR2 := null
757 ,p_attribute4 VARCHAR2 := null
758 ,p_attribute5 VARCHAR2 := null
759 ,p_attribute6 VARCHAR2 := null
760 ,p_attribute7 VARCHAR2 := null
761 ,p_attribute8 VARCHAR2 := null
762 ,p_attribute9 VARCHAR2 := null
763 ,p_attribute10 VARCHAR2 := null
764 ,p_attribute11 VARCHAR2 := null
765 ,p_attribute12 VARCHAR2 := null
766 ,p_attribute13 VARCHAR2 := null
767 ,p_attribute14 VARCHAR2 := null
768 ,p_attribute15 VARCHAR2 := null
769 ,x_rowid OUT NOCOPY ROWID
770 ,x_return_status OUT NOCOPY VARCHAR2
771 ,x_msg_count OUT NOCOPY NUMBER
772 ,x_msg_data OUT NOCOPY VARCHAR2) IS
773
774 l_api_name VARCHAR2(30) := 'CREATE_BUDGET_VERSION';
775 l_rate_class_id NUMBER(15) := p_oh_rate_class_id;
776 l_start_date DATE := p_start_date;
777 l_end_date DATE := p_end_date;
778 l_period_start_date DATE;
779 l_period_end_date DATE;
780 l_budget_period NUMBER;
781 l_version_id NUMBER := p_version_id;
782 l_proposal_form_number Varchar2(30) := p_proposal_form_number;
783 l_return_status VARCHAR2(1);
784 l_msg_count NUMBER;
785 l_data VARCHAR2(250);
786 l_msg_index_out NUMBER;
787
788 BEGIN
789 IF p_commit = FND_API.G_TRUE THEN
790 SAVEPOINT create_budget_version;
791 END IF;
792
793 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
794 fnd_msg_pub.initialize;
795 end if;
796
797 --checking for duplicate final version
798 if p_final_version_flag = 'Y' then
799 check_final_version(p_proposal_id, l_version_id, l_return_status);
800 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
801 x_return_status := 'E';
802 END IF;
803 end if;
804
805 --Rate class
806 --rate class is a poplist hence take the value as it is
807 l_rate_class_id := p_oh_rate_class_id;
808 /*
809 IF p_oh_rate_class_name is null THEN
810 l_rate_class_id := null;
811 ELSE
812 --ELSIF p_oh_rate_class_id is null THEN
813 get_rate_class_id(p_rate_class_name => p_oh_rate_class_name
814 ,x_rate_class_id => l_rate_class_id
815 ,x_return_status => l_return_status);
816
817 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
818 x_return_status := 'E';
819 END IF;
820 END IF;
821 */
822
823 --Sponsor Hierarchy
824 validate_sponsor_hierarchy(p_proposal_form_number
825 ,l_proposal_form_number
826 ,x_return_status );
827
828 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
829 x_return_status := 'E';
830 END IF;
831
832 l_msg_count := FND_MSG_PUB.count_msg;
833 If l_msg_count > 0 THEN
834 x_msg_count := l_msg_count;
835 If l_msg_count = 1 THEN
836 fnd_msg_pub.get
837 (p_encoded => FND_API.G_TRUE ,
838 p_msg_index => 1,
839 p_data => l_data,
840 p_msg_index_out => l_msg_index_out );
841
842 x_msg_data := l_data;
843 End if;
844 RAISE FND_API.G_EXC_ERROR;
845 End if;
846
847 x_return_status := 'S';
848
849 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
850
851 begin
852 select proposal_start_date, proposal_end_date
853 into l_start_date, l_end_date
854 from igw_proposals_all
855 where proposal_id = p_proposal_id;
856 exception
857 when others then
858 raise;
859 end;
860
861 begin
862 select nvl(max(version_id),0)+1
863 into l_version_id
864 from igw_budgets
865 where proposal_id = p_proposal_id;
866 exception
867 when no_data_found then
868 l_version_id := nvl(p_version_id,0) + 1;
869 when others then
870 raise;
871 end;
872
873 igw_budgets_tbh.insert_row(
874 p_proposal_id => p_proposal_id
875 ,p_version_id => l_version_id
876 ,p_start_date => l_start_date
877 ,p_end_date => l_end_date
878 ,p_total_cost => p_total_cost
879 ,p_total_direct_cost => p_total_direct_cost
880 ,p_total_indirect_cost => p_total_indirect_cost
881 ,p_cost_sharing_amount => p_cost_sharing_amount
882 ,p_underrecovery_amount => p_underrecovery_amount
883 ,p_residual_funds => p_residual_funds
884 ,p_total_cost_limit => p_total_cost_limit
885 ,p_oh_rate_class_id => l_rate_class_id
886 ,p_proposal_form_number => p_proposal_form_number
887 ,p_comments => p_comments
888 ,p_final_version_flag => p_final_version_flag
889 ,p_budget_type_code => p_budget_type_code
890 ,p_enter_budget_at_period_level => p_enter_budget_at_period_level
891 ,p_apply_inflation_setup_rates => p_apply_inflation_setup_rates
892 ,p_apply_eb_setup_rates => p_apply_eb_setup_rates
893 ,p_apply_oh_setup_rates => p_apply_oh_setup_rates
894 ,p_attribute_category => p_attribute_category
895 ,p_attribute1 => p_attribute1
896 ,p_attribute2 => p_attribute2
897 ,p_attribute3 => p_attribute3
898 ,p_attribute4 => p_attribute4
899 ,p_attribute5 => p_attribute5
900 ,p_attribute6 => p_attribute6
901 ,p_attribute7 => p_attribute7
902 ,p_attribute8 => p_attribute8
903 ,p_attribute9 => p_attribute9
904 ,p_attribute10 => p_attribute10
905 ,p_attribute11 => p_attribute11
906 ,p_attribute12 => p_attribute12
907 ,p_attribute13 => p_attribute13
908 ,p_attribute14 => p_attribute14
909 ,p_attribute15 => p_attribute15
910 ,x_rowid => x_rowid
911 ,x_return_status => l_return_status);
912
913 x_return_status := l_return_status;
914
915
916 l_period_start_date := l_start_date;
917 l_period_end_date := add_months(l_period_start_date,12)-1;
918 l_budget_period := 0;
919 <<PERIOD_LOOP>>
920 LOOP
921 l_budget_period := nvl(l_budget_period,0) +1;
922
923 if l_period_end_date < l_end_date then
924 igw_budget_periods_tbh.insert_row(
925 p_proposal_id => p_proposal_id
926 ,p_version_id => l_version_id
927 ,p_budget_period_id => l_budget_period
928 ,p_start_date => l_period_start_date
929 ,p_end_date => l_period_end_date
930 ,p_total_cost => 0
931 ,p_total_direct_cost => 0
932 ,p_total_indirect_cost => 0
933 ,p_cost_sharing_amount => 0
934 ,p_underrecovery_amount => 0
935 ,p_total_cost_limit => 0
936 ,p_program_income => 0
937 ,p_program_income_source => null
938 ,x_rowid => x_rowid
939 ,x_return_status => l_return_status);
940
941 x_return_status := l_return_status;
942
943 l_period_start_date := l_period_end_date +1;
944 l_period_end_date := add_months(l_period_start_date,12)-1;
945 GOTO PERIOD_LOOP;
946 else
947 l_period_end_date := l_end_date;
948 igw_budget_periods_tbh.insert_row(
949 p_proposal_id => p_proposal_id
950 ,p_version_id => l_version_id
951 ,p_budget_period_id => l_budget_period
952 ,p_start_date => l_period_start_date
953 ,p_end_date => l_period_end_date
954 ,p_total_cost => 0
955 ,p_total_direct_cost => 0
956 ,p_total_indirect_cost => 0
957 ,p_cost_sharing_amount => 0
958 ,p_underrecovery_amount => 0
959 ,p_total_cost_limit => 0
960 ,p_program_income => 0
961 ,p_program_income_source => null
962 ,x_rowid => x_rowid
963 ,x_return_status => l_return_status);
964
965 x_return_status := l_return_status;
966 EXIT;
967 end if;
968 END LOOP PERIOD_LOOP;
969
970 end if; -- p_validate_only = 'Y'
971
972 l_msg_count := FND_MSG_PUB.count_msg;
973 If l_msg_count > 0 THEN
974 x_msg_count := l_msg_count;
975 If l_msg_count = 1 THEN
976 fnd_msg_pub.get
977 (p_encoded => FND_API.G_TRUE ,
978 p_msg_index => 1,
979 p_data => l_data,
980 p_msg_index_out => l_msg_index_out );
981
982 x_msg_data := l_data;
983 End if;
984 RAISE FND_API.G_EXC_ERROR;
985 End if;
986
987 x_return_status := FND_API.G_RET_STS_SUCCESS;
988
989 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
990 IF p_commit = FND_API.G_TRUE THEN
991 ROLLBACK TO create_budget_version;
992 END IF;
993 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
994 fnd_msg_pub.add_exc_msg(p_pkg_name => G_package_name,
995 p_procedure_name => l_api_name,
996 p_error_text => SUBSTRB(SQLERRM,1,240));
997 fnd_msg_pub.count_and_get(p_count => x_msg_count
998 ,p_data => x_msg_data);
999 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1000
1001 WHEN FND_API.G_EXC_ERROR THEN
1002 IF p_commit = FND_API.G_TRUE THEN
1003 ROLLBACK TO create_budget_version;
1004 END IF;
1005 x_return_status := 'E';
1006
1007 WHEN OTHERS THEN
1008 IF p_commit = FND_API.G_TRUE THEN
1009 ROLLBACK TO create_budget_version;
1010 END IF;
1011 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1012 fnd_msg_pub.add_exc_msg(p_pkg_name => G_package_name,
1013 p_procedure_name => l_api_name,
1014 p_error_text => SUBSTRB(SQLERRM,1,240));
1015 fnd_msg_pub.count_and_get(p_count => x_msg_count
1016 ,p_data => x_msg_data);
1017 RAISE;
1018
1019
1020 END; --CREATE BUDGET VERSION
1021
1022
1023 ------------------------------------------------------------------------------------------
1024 procedure update_budget_version
1025 (p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1026 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1027 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1028 ,p_proposal_id NUMBER
1029 ,p_version_id NUMBER
1030 ,p_start_date DATE
1031 ,p_end_date DATE
1032 ,p_total_cost NUMBER
1033 ,p_total_direct_cost NUMBER
1034 ,p_total_indirect_cost NUMBER
1035 ,p_cost_sharing_amount NUMBER
1036 ,p_underrecovery_amount NUMBER
1037 ,p_residual_funds NUMBER
1038 ,p_total_cost_limit NUMBER
1039 ,p_oh_rate_class_id NUMBER
1040 ,p_oh_rate_class_name VARCHAR2
1041 ,p_proposal_form_number VARCHAR2
1042 ,p_comments VARCHAR2
1043 ,p_final_version_flag VARCHAR2
1044 ,p_budget_type_code VARCHAR2 := 'PROPOSAL_BUDGET'
1045 ,p_enter_budget_at_period_level VARCHAR2
1046 ,p_apply_inflation_setup_rates VARCHAR2
1047 ,p_apply_eb_setup_rates VARCHAR2
1048 ,p_apply_oh_setup_rates VARCHAR2
1049 ,p_attribute_category VARCHAR2 := null
1050 ,p_attribute1 VARCHAR2 := null
1051 ,p_attribute2 VARCHAR2 := null
1052 ,p_attribute3 VARCHAR2 := null
1053 ,p_attribute4 VARCHAR2 := null
1054 ,p_attribute5 VARCHAR2 := null
1055 ,p_attribute6 VARCHAR2 := null
1056 ,p_attribute7 VARCHAR2 := null
1057 ,p_attribute8 VARCHAR2 := null
1058 ,p_attribute9 VARCHAR2 := null
1059 ,p_attribute10 VARCHAR2 := null
1060 ,p_attribute11 VARCHAR2 := null
1061 ,p_attribute12 VARCHAR2 := null
1062 ,p_attribute13 VARCHAR2 := null
1063 ,p_attribute14 VARCHAR2 := null
1064 ,p_attribute15 VARCHAR2 := null
1065 ,p_record_version_number IN NUMBER
1066 ,p_rowid IN ROWID
1067 ,x_return_status OUT NOCOPY VARCHAR2
1068 ,x_msg_count OUT NOCOPY NUMBER
1069 ,x_msg_data OUT NOCOPY VARCHAR2) IS
1070
1071 cursor c_rate_class is
1072 select oh_rate_class_id
1073 from igw_budgets
1074 where rowid = p_rowid;
1075
1076 l_api_name VARCHAR2(30) := 'UPDATE_BUDGET_VERSION';
1077 l_rate_class_id NUMBER(15) := p_oh_rate_class_id;
1078 l_orig_rate_class_id NUMBER(15);
1079 l_proposal_form_number Varchar2(30) := p_proposal_form_number;
1080 l_return_status VARCHAR2(1);
1081 l_msg_count NUMBER;
1082 l_data VARCHAR2(250);
1083 l_msg_index_out NUMBER;
1084 l_dummy VARCHAR2(1);
1085
1086
1087 BEGIN
1088 IF p_commit = FND_API.G_TRUE THEN
1089 SAVEPOINT update_budget_version;
1090 END IF;
1091
1092 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
1093 fnd_msg_pub.initialize;
1094 end if;
1095
1096 x_return_status := 'S';
1097
1098
1099 --checking for duplicate final version
1100 if p_final_version_flag = 'Y' then
1101 check_final_version(p_proposal_id, p_version_id, l_return_status);
1102 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1103 x_return_status := 'E';
1104 END IF;
1105 end if;
1106
1107 --Rate class
1108 --rate class is a poplist hence take the value as it is
1109 l_rate_class_id := p_oh_rate_class_id;
1110 /*
1111 IF p_oh_rate_class_name is null THEN
1112 l_rate_class_id := null;
1113 ELSE
1114 --ELSIF p_oh_rate_class_id is null THEN
1115 get_rate_class_id(p_rate_class_name => p_oh_rate_class_name
1116 ,x_rate_class_id => l_rate_class_id
1117 ,x_return_status => l_return_status);
1118
1119 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1120 x_return_status := 'E';
1121 END IF;
1122 END IF;
1123 */
1124
1125 /* commented it out NOCOPY because we need to recalculate for almost all the cases even when
1126 check boxes for apply rates are changed */
1127 /*
1128 if p_rowid is not null then
1129 open c_rate_class;
1130 fetch c_rate_class into l_orig_rate_class_id;
1131 close c_rate_class;
1132 end if;
1133 */
1134
1135 IGW_UTILS.Check_Date_Validity(
1136 p_context_field => 'BUDGET_VERSION_DATE'
1137 ,p_start_date => nvl(p_start_date, sysdate-1)
1138 ,p_end_date => nvl(p_end_date, sysdate+1)
1139 ,x_return_status => l_return_status);
1140
1141 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1142 x_return_status := 'E';
1143 END IF;
1144
1145 validate_sponsor_hierarchy(p_proposal_form_number
1146 ,l_proposal_form_number
1147 ,x_return_status );
1148
1149 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1150 x_return_status := 'E';
1151 END IF;
1152
1153 --validate budget entry
1154 if p_enter_budget_at_period_level = 'Y' then
1155 validate_budget_entry(p_proposal_id, p_version_id, l_return_status);
1156 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1157 x_return_status := 'E';
1158 END IF;
1159 end if;
1160
1161 l_msg_count := FND_MSG_PUB.count_msg;
1162 If l_msg_count > 0 THEN
1163 x_msg_count := l_msg_count;
1164 If l_msg_count = 1 THEN
1165 fnd_msg_pub.get
1166 (p_encoded => FND_API.G_TRUE ,
1167 p_msg_index => 1,
1168 p_data => l_data,
1169 p_msg_index_out => l_msg_index_out );
1170
1171 x_msg_data := l_data;
1172 End if;
1173 RAISE FND_API.G_EXC_ERROR;
1174 End if;
1175
1176 BEGIN
1177 SELECT 'x' INTO l_dummy
1178 FROM igw_budgets
1179 WHERE ((proposal_id = p_proposal_id AND version_id = p_version_id)
1180 OR rowid = p_rowid)
1181 AND record_version_number = p_record_version_number;
1182 EXCEPTION
1183 WHEN NO_DATA_FOUND THEN
1184 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
1185 FND_MSG_PUB.Add;
1186 x_msg_data := 'IGW_SS_RECORD_CHANGED';
1187 x_return_status := 'E' ;
1188 END;
1189
1190 l_msg_count := FND_MSG_PUB.count_msg;
1191
1192 IF l_msg_count > 0 THEN
1193 x_msg_count := l_msg_count;
1194 x_return_status := 'E';
1195 If l_msg_count = 1 THEN
1196 fnd_msg_pub.get
1197 (p_encoded => FND_API.G_TRUE ,
1198 p_msg_index => 1,
1199 p_data => l_data,
1200 p_msg_index_out => l_msg_index_out );
1201
1202 x_msg_data := l_data;
1203 End if;
1204 RAISE FND_API.G_EXC_ERROR;
1205 END IF;
1206
1207 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
1208
1209 igw_budgets_tbh.update_row(
1210 p_rowid => p_rowid
1211 ,p_proposal_id => p_proposal_id
1212 ,p_version_id => p_version_id
1213 ,p_start_date => p_start_date
1214 ,p_end_date => p_end_date
1215 ,p_total_cost => p_total_cost
1216 ,p_total_direct_cost => p_total_direct_cost
1217 ,p_total_indirect_cost => p_total_indirect_cost
1218 ,p_cost_sharing_amount => p_cost_sharing_amount
1219 ,p_underrecovery_amount => p_underrecovery_amount
1220 ,p_residual_funds => p_residual_funds
1221 ,p_total_cost_limit => p_total_cost_limit
1222 ,p_oh_rate_class_id => l_rate_class_id
1223 ,p_proposal_form_number => p_proposal_form_number
1224 ,p_comments => p_comments
1225 ,p_final_version_flag => p_final_version_flag
1226 ,p_budget_type_code => p_budget_type_code
1227 ,p_enter_budget_at_period_level => p_enter_budget_at_period_level
1228 ,p_apply_inflation_setup_rates => p_apply_inflation_setup_rates
1229 ,p_apply_eb_setup_rates => p_apply_eb_setup_rates
1230 ,p_apply_oh_setup_rates => p_apply_oh_setup_rates
1231 ,p_attribute_category => p_attribute_category
1232 ,p_attribute1 => p_attribute1
1233 ,p_attribute2 => p_attribute2
1234 ,p_attribute3 => p_attribute3
1235 ,p_attribute4 => p_attribute4
1236 ,p_attribute5 => p_attribute5
1237 ,p_attribute6 => p_attribute6
1238 ,p_attribute7 => p_attribute7
1239 ,p_attribute8 => p_attribute8
1240 ,p_attribute9 => p_attribute9
1241 ,p_attribute10 => p_attribute10
1242 ,p_attribute11 => p_attribute11
1243 ,p_attribute12 => p_attribute12
1244 ,p_attribute13 => p_attribute13
1245 ,p_attribute14 => p_attribute14
1246 ,p_attribute15 => p_attribute15
1247 ,p_record_version_number => p_record_version_number
1248 ,x_return_status => l_return_status);
1249
1250 x_return_status := l_return_status;
1251
1252 --updating budget lines for inflation flag with new value
1253 update igw_budget_details
1254 set apply_inflation_flag = p_apply_inflation_setup_rates
1255 where proposal_id = p_proposal_id
1256 and version_id = p_version_id;
1257
1258 --Recalculate only if rate class is different.
1259 --if l_rate_class_id <> l_orig_rate_class_id then
1260 /* Need to recalculate for almost all the cases even when
1261 check boxes for apply rates are changed */
1262
1263 IGW_BUDGET_OPERATIONS.recalculate_budget (
1264 p_proposal_id => p_proposal_id
1265 ,p_version_id => p_version_id
1266 ,x_return_status => l_return_status
1267 ,x_msg_data => x_msg_data
1268 ,x_msg_count => x_msg_count);
1269
1270 x_return_status := l_return_status;
1271 --end if;
1272
1273 end if; -- p_validate_only = 'Y'
1274
1275 l_msg_count := FND_MSG_PUB.count_msg;
1276 If l_msg_count > 0 THEN
1277 x_msg_count := l_msg_count;
1278 If l_msg_count = 1 THEN
1279 fnd_msg_pub.get
1280 (p_encoded => FND_API.G_TRUE ,
1281 p_msg_index => 1,
1282 p_data => l_data,
1283 p_msg_index_out => l_msg_index_out );
1284
1285 x_msg_data := l_data;
1286 End if;
1287 RAISE FND_API.G_EXC_ERROR;
1288 End if;
1289
1290 x_return_status := FND_API.G_RET_STS_SUCCESS;
1291
1292 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1293 IF p_commit = FND_API.G_TRUE THEN
1294 ROLLBACK TO update_budget_version;
1295 END IF;
1296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1297 fnd_msg_pub.add_exc_msg(p_pkg_name => G_package_name,
1298 p_procedure_name => l_api_name,
1299 p_error_text => SUBSTRB(SQLERRM,1,240));
1300 fnd_msg_pub.count_and_get(p_count => x_msg_count
1301 ,p_data => x_msg_data);
1302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1303
1304 WHEN FND_API.G_EXC_ERROR THEN
1305 IF p_commit = FND_API.G_TRUE THEN
1306 ROLLBACK TO update_budget_version;
1307 END IF;
1308 x_return_status := 'E';
1309
1310 WHEN OTHERS THEN
1311 IF p_commit = FND_API.G_TRUE THEN
1312 ROLLBACK TO update_budget_version;
1313 END IF;
1314 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1315 fnd_msg_pub.add_exc_msg(p_pkg_name => G_package_name,
1316 p_procedure_name => l_api_name,
1317 p_error_text => SUBSTRB(SQLERRM,1,240));
1318 fnd_msg_pub.count_and_get(p_count => x_msg_count
1319 ,p_data => x_msg_data);
1320 RAISE;
1321
1322 END; --UPDATE BUDGET VERSIONS
1323
1324 -------------------------------------------------------------------------------------------
1325
1326 procedure delete_budget_version
1327 (p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1328 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1329 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1330 ,p_proposal_id IN NUMBER
1331 ,p_version_id IN NUMBER
1332 ,p_record_version_number IN NUMBER
1333 ,p_rowid IN ROWID
1334 ,x_return_status OUT NOCOPY VARCHAR2
1335 ,x_msg_count OUT NOCOPY NUMBER
1336 ,x_msg_data OUT NOCOPY VARCHAR2)is
1337
1338 l_api_name VARCHAR2(30) := 'DELETE_BUDGET_VERSION';
1339 l_return_status VARCHAR2(1);
1340 l_msg_count NUMBER;
1341 l_data VARCHAR2(250);
1342 l_msg_index_out NUMBER;
1343 l_dummy VARCHAR2(1);
1344
1345
1346
1347 BEGIN
1348 IF p_commit = FND_API.G_TRUE THEN
1349 SAVEPOINT delete_budget_version;
1350 END IF;
1351
1352 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
1353 fnd_msg_pub.initialize;
1354 end if;
1355
1356 x_return_status := 'S';
1357
1358 BEGIN
1359 SELECT 'x' INTO l_dummy
1360 FROM igw_budgets
1361 WHERE ((proposal_id = p_proposal_id AND version_id = p_version_id)
1362 OR rowid = p_rowid)
1363 AND record_version_number = p_record_version_number;
1364 EXCEPTION
1365 WHEN NO_DATA_FOUND THEN
1366 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
1367 FND_MSG_PUB.Add;
1368 x_msg_data := 'IGW_SS_RECORD_CHANGED';
1369 x_return_status := 'E' ;
1370 END;
1371
1372 l_msg_count := FND_MSG_PUB.count_msg;
1373
1374 IF l_msg_count > 0 THEN
1375 x_msg_count := l_msg_count;
1376 x_return_status := 'E';
1377 If l_msg_count = 1 THEN
1378 fnd_msg_pub.get
1379 (p_encoded => FND_API.G_TRUE ,
1380 p_msg_index => 1,
1381 p_data => l_data,
1382 p_msg_index_out => l_msg_index_out );
1383
1384 x_msg_data := l_data;
1385 End if;
1386 RAISE FND_API.G_EXC_ERROR;
1387 END IF;
1388
1389 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
1390
1391 igw_budgets_tbh.delete_row (
1392 p_rowid => p_rowid,
1393 p_proposal_id => p_proposal_id,
1394 p_version_id => p_version_id,
1395 p_record_version_number => p_record_version_number,
1396 x_return_status => l_return_status);
1397
1398 igw_budgets_pvt.manage_budget_deletion(
1399 p_delete_level => 'BUDGET_VERSION'
1400 ,p_proposal_id => p_proposal_id
1401 ,p_version_id => p_version_id
1402 ,x_return_status => l_return_status);
1403
1404 x_return_status := l_return_status;
1405 end if; -- p_validate_only = 'Y'
1406
1407
1408 l_msg_count := FND_MSG_PUB.count_msg;
1409 If l_msg_count > 0 THEN
1410 x_msg_count := l_msg_count;
1411 If l_msg_count = 1 THEN
1412 fnd_msg_pub.get
1413 (p_encoded => FND_API.G_TRUE ,
1414 p_msg_index => 1,
1415 p_data => l_data,
1416 p_msg_index_out => l_msg_index_out );
1417
1418 x_msg_data := l_data;
1419 End if;
1420 RAISE FND_API.G_EXC_ERROR;
1421 End if;
1422
1423 x_return_status := FND_API.G_RET_STS_SUCCESS;
1424
1425 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1426 IF p_commit = FND_API.G_TRUE THEN
1427 ROLLBACK TO delete_budget_version;
1428 END IF;
1429 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1430 fnd_msg_pub.add_exc_msg(p_pkg_name => G_package_name,
1431 p_procedure_name => l_api_name,
1432 p_error_text => SUBSTRB(SQLERRM,1,240));
1433 fnd_msg_pub.count_and_get(p_count => x_msg_count
1434 ,p_data => x_msg_data);
1435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1436
1437 WHEN FND_API.G_EXC_ERROR THEN
1438 IF p_commit = FND_API.G_TRUE THEN
1439 ROLLBACK TO delete_budget_version;
1440 END IF;
1441 x_return_status := 'E';
1442
1443 WHEN OTHERS THEN
1444 IF p_commit = FND_API.G_TRUE THEN
1445 ROLLBACK TO delete_budget_version;
1446 END IF;
1447 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1448 fnd_msg_pub.add_exc_msg(p_pkg_name => G_package_name,
1449 p_procedure_name => l_api_name,
1450 p_error_text => SUBSTRB(SQLERRM,1,240));
1451 fnd_msg_pub.count_and_get(p_count => x_msg_count
1452 ,p_data => x_msg_data);
1453 RAISE;
1454
1455
1456 END; --DELETE BUDGET VERSION
1457
1458 END IGW_BUDGETS_PVT;