[Home] [Help]
PACKAGE BODY: APPS.IGW_OVERHEAD_CAL
Source
1 PACKAGE BODY IGW_OVERHEAD_CAL as
2 -- $Header: igwbuovb.pls 115.31 2002/11/14 18:48:26 vmedikon ship $
3
4 PROCEDURE get_date_details( p_input_date DATE
5 ,x_fiscal_year OUT NOCOPY NUMBER
6 ,x_fiscal_start_date OUT NOCOPY DATE
7 ,x_fiscal_end_date OUT NOCOPY DATE
8 ,x_return_status OUT NOCOPY VARCHAR2
9 ,x_msg_data OUT NOCOPY VARCHAR2) is
10
11 l_profile_date VARCHAR2(4);
12 l_profile_date_number NUMBER;
13 l_date_number NUMBER;
14 l_fiscal_year NUMBER;
15 l_dummy_start VARCHAR2(8);
16 l_dummy_end VARCHAR2(8);
17 BEGIN
18
19 l_profile_date := fnd_profile.value('IGW_FISCAL_YEAR_START_MMDD');
20 l_profile_date_number := l_profile_date;
21 --dbms_output.put_line('the l_profile_date_number is '||l_profile_date_number);
22
23 l_date_number:= to_number(to_char(p_input_date, 'MM'||'DD'));
24
25 if l_date_number < l_profile_date_number and
26 l_date_number >= 101 then
27 l_fiscal_year:= to_number(to_char(p_input_date, 'YYYY'));
28 --dbms_output.put_line('the l_fiscal_year is '||l_fiscal_year);
29 else
30 l_fiscal_year:= to_number(to_char(p_input_date, 'YYYY')) + 1;
31 --dbms_output.put_line('the l_fiscal_year is ELSE '||l_fiscal_year);
32 end if;
33
34 l_dummy_start := l_profile_date||l_fiscal_year;
35 l_dummy_end := l_profile_date||(l_fiscal_year+1);
36 x_fiscal_start_date := add_months(to_date(l_dummy_start, 'MMDDYYYY'),-12);
37 x_fiscal_end_date := add_months(to_date(l_dummy_end, 'MMDDYYYY') -1, -12);
38 -- don't use the fiscal date obtained above. Use the one below. This is fix for BUG 2317219
39 -- The fiscal start date and end date above, however, seem to be correct under all conditions
40 x_fiscal_year := greatest(to_number(to_char(x_fiscal_start_date, 'YYYY')),to_number(to_char(x_fiscal_end_date, 'YYYY')));
41
42 END get_date_details;
43
44 PROCEDURE get_rate_id (p_expenditure_type VARCHAR2
45 ,p_expenditure_category_flag VARCHAR2
46 ,p_rate_class_type VARCHAR2
47 ,x_rate_class_id IN OUT NOCOPY NUMBER
48 ,x_rate_type_id OUT NOCOPY NUMBER
49 ,x_return_status OUT NOCOPY VARCHAR2
50 ,x_msg_data OUT NOCOPY VARCHAR2) is
51 l_parent_category VARCHAR2(30);
52 cursor c_rate_id is
53 select rc.rate_class_id
54 , rt.rate_type_id
55 from igw_rate_classes rc
56 , igw_rate_types rt
57 , igw_exp_type_rate_types rct
58 where rc.rate_class_id = rt.rate_class_id
59 and rt.rate_type_id = rct.rate_type_id
60 and rc.rate_class_id = rct.rate_class_id
61 and rct.expenditure_category = l_parent_category
62 and rc.rate_class_type = p_rate_class_type
63 and rc.rate_class_id = nvl(x_rate_class_id,rc.rate_class_id);
64 BEGIN
65 if p_expenditure_category_flag = 'N' then
66 select parent_category
67 into l_parent_category
68 from igw_budget_expenditures_v
69 where budget_expenditure = p_expenditure_type
70 and expenditure_category_flag = p_expenditure_category_flag
71 and parent_category is not null;
72 elsif p_expenditure_category_flag = 'Y' then
73 l_parent_category := p_expenditure_type;
74 end if;
75 open c_rate_id;
76 fetch c_rate_id into x_rate_class_id, x_rate_type_id;
77 close c_rate_id;
78 EXCEPTION
79 when others then
80 x_return_status := 'U';
81 x_msg_data := SQLCODE||' '||SQLERRM;
82 fnd_msg_pub.add_exc_msg(G_PKG_NAME, 'GET_FISCAL_YEAR');
83 END;
84
85
86 PROCEDURE get_rate ( p_proposal_id NUMBER
87 ,p_version_id NUMBER
88 ,p_fiscal_year NUMBER
89 ,p_activity_type_code VARCHAR2
90 ,p_location_code VARCHAR2
91 ,p_rate_class_id NUMBER
92 ,p_rate_type_id NUMBER
93 ,x_rate OUT NOCOPY NUMBER
94 ,x_rate_ov OUT NOCOPY NUMBER
95 ,x_start_date OUT NOCOPY DATE
96 ,x_return_status OUT NOCOPY VARCHAR2
97 ,x_msg_data OUT NOCOPY VARCHAR2) is
98
99 l_activity_type VARCHAR2(80);
100 l_location VARCHAR2(80);
101 l_rate_class VARCHAR2(250);
102 l_rate_type VARCHAR2(250);
103
104 BEGIN
105 if p_rate_class_id is null and p_rate_type_id is null then
106 x_rate := 0;
107 x_rate_ov := 0;
108 else
109 begin
110 --selecting values for tokens
111 --commented for GMSINSTALL
112 --independent of post-award
113
114 select meaning
115 into l_activity_type
116 from fnd_lookups
117 where lookup_type = 'IGW_ACTIVITY_TYPES'
118 and lookup_code = p_activity_type_code;
119 -- end if;
120
121 --selecting values for tokens
122 select meaning
123 into l_location
124 from fnd_lookups
125 where lookup_type = 'IGW_LOCATION'
126 and lookup_code = p_location_code;
127
128 --selecting values for tokens
129 select rc.description
130 , rt.description
131 into l_rate_class
132 , l_rate_type
133 from igw_rate_classes rc
134 , igw_rate_types rt
135 where rc.rate_class_id = rt.rate_class_id
136 and rc.rate_class_id = p_rate_class_id
137 and rt.rate_type_id = p_rate_type_id;
138
139 begin
140 select rate,start_date
141 into x_rate, x_start_date
142 from igw_institute_rates ir
143 where ir.rate_class_id = p_rate_class_id
144 and ir.rate_type_id = p_rate_type_id
145 and ir.activity_type_code = p_activity_type_code
146 and ir.location_code = p_location_code
147 and ir.fiscal_year = p_fiscal_year;
148 exception
149 when no_data_found then null;
150 end;
151
152 begin
153 select applicable_rate, start_date
154 into x_rate_ov, x_start_date
155 from igw_prop_rates
156 where rate_class_id = p_rate_class_id
157 and rate_type_id = p_rate_type_id
158 and activity_type_code = p_activity_type_code
159 and location_code = p_location_code
160 and fiscal_year = p_fiscal_year
161 and proposal_id = p_proposal_id
162 and version_id = p_version_id;
163 exception
164 when no_data_found then
165 x_rate_ov := x_rate;
166 x_start_date := x_start_date;
167 end;
168 end;
169 end if;
170 x_return_status := 'S';
171 EXCEPTION
172 when no_data_found then
173 null;
174 /* commenting out NOCOPY because we don't want to show these message on recalculations */
175 /*
176 x_return_status := 'I';
177 x_msg_data := 'IGW_FISCAL_YEAR_UNDEFINED';
178 fnd_message.set_name('IGW', 'IGW_FISCAL_RATE_UNDEFINED');
179 fnd_message.set_token('ACTIVITY_TYPE', l_activity_type);
180 fnd_message.set_token('LOCATION_CODE', l_location);
181 fnd_message.set_token('FISCAL_YEAR', p_fiscal_year);
182 fnd_message.set_token('RATE_CLASS', l_rate_class);
183 fnd_message.set_token('RATE_TYPE', l_rate_type);
184 fnd_msg_pub.add;
185 */
186
187 when others then
188 x_return_status := 'U';
189 x_msg_data := SQLCODE||' '||SQLERRM;
190 fnd_msg_pub.add_exc_msg(G_PKG_NAME, 'GET_RATE');
191 END get_rate;
192
193 ---------------------------------------------------------------------
194 FUNCTION get_applicable_rate (
195 p_proposal_id number
196 ,p_version_id number
197 ,p_rate_class_id number
198 ,p_rate_type_id number
199 ,p_activity_type_code varchar2
200 ,p_location_code varchar2
201 ,p_fiscal_year number) RETURN NUMBER IS
202
203 l_applicable_rate number(15);
204 begin
205 select applicable_rate
206 into l_applicable_rate
207 from igw_prop_rates
208 where proposal_id = p_proposal_id
209 and version_id = p_version_id
210 and rate_class_id = p_rate_class_id
211 and rate_type_id = p_rate_type_id
212 and location_code = p_location_code
213 and activity_type_code = p_activity_type_code
214 and fiscal_year = p_fiscal_year;
215
216 return l_applicable_rate;
217 exception
218 when others then
219 return null;
220 end;
221
222 -----------------------------------------------------------------------------
223
224 PROCEDURE calc_oh ( p_proposal_id NUMBER
225 ,p_version_id NUMBER
226 ,p_base_amount NUMBER
227 ,p_budget_start_date DATE
228 ,p_budget_end_date DATE
229 ,x_oh_value OUT NOCOPY NUMBER
230 ,x_oh_value_ov OUT NOCOPY NUMBER
231 ,p_activity_type_code VARCHAR2
232 ,p_location_code VARCHAR2
233 ,p_rate_class_id NUMBER
234 ,p_rate_type_id NUMBER
235 ,x_return_status OUT NOCOPY VARCHAR2
236 ,x_msg_data OUT NOCOPY VARCHAR2
237 ,x_msg_count OUT NOCOPY NUMBER) is
238
239 l_no_of_days NUMBER;
240 l_start_fiscal_year NUMBER(4);
241 l_end_fiscal_year NUMBER(4);
242 l_new_fiscal_year NUMBER(4);
243 l_fiscal_diff NUMBER(4);
244 l_oh NUMBER;
245 l_oh_ov NUMBER;
246 l_rate_start_date DATE;
247 l_start_date DATE;
248 l_end_date DATE;
249 l_start_f_start_date DATE;
250 l_start_f_end_date DATE;
251 l_end_f_start_date DATE;
252 l_end_f_end_date DATE;
253 l_oh_rate NUMBER(5,2);
254 l_oh_rate_ov NUMBER(5,2);
255 l_return_status VARCHAR2(1);
256 l_msg_data VARCHAR2(200);
257
258 BEGIN
259 fnd_msg_pub.initialize;
260 l_no_of_days := (p_budget_end_date - p_budget_start_date) +1 ;
261 get_date_details(p_budget_start_date
262 ,l_start_fiscal_year
263 ,l_start_f_start_date
264 ,l_start_f_end_date
265 ,l_return_status
266 ,l_msg_data);
267 if l_return_status <> 'S' then
268 raise FND_API.G_EXC_ERROR;
269 end if;
270
271 get_date_details(p_budget_end_date
272 ,l_end_fiscal_year
273 ,l_end_f_start_date
274 ,l_end_f_end_date
275 ,l_return_status
276 ,l_msg_data);
277
278 if l_return_status <> 'S' then
279 raise FND_API.G_EXC_ERROR;
280 end if;
281 l_fiscal_diff := (l_end_fiscal_year - l_start_fiscal_year) +1;
282 l_start_date := p_budget_start_date;
283 l_new_fiscal_year := l_start_fiscal_year;
284 l_end_date := l_start_f_end_date;
285 for i IN 1 .. l_fiscal_diff
286 LOOP
287
288 if l_fiscal_diff = 1 and i = 1 then
289 l_end_date := p_budget_end_date;
290 end if;
291
292 get_rate(p_proposal_id
293 ,p_version_id
294 ,l_new_fiscal_year
295 ,p_activity_type_code
296 ,p_location_code
297 ,p_rate_class_id
298 ,p_rate_type_id
299 ,l_oh_rate
300 ,l_oh_rate_ov
301 ,l_rate_start_date
302 ,l_return_status
303 ,l_msg_data);
304
305 if l_return_status NOT IN ('S','I') then
306 raise FND_API.G_EXC_ERROR;
307 end if;
308
309 --dbms_output.put_line('the oh_rate is '||l_oh_rate);
310 --dbms_output.put_line('the l_no_of_days '||l_no_of_days);
311 --dbms_output.put_line('the l_start_Date '||l_start_date);
312 --dbms_output.put_line('the l_end_Date '||l_end_date);
313 --dbms_output.put_line('the the base amount '||p_base_amount);
314
315 l_oh := p_base_amount * ((l_end_date - l_start_date)+ 1)/l_no_of_days
316 * l_oh_rate/100;
317
318 l_oh_ov := p_base_amount * ((l_end_date - l_start_date)+ 1)/l_no_of_days
319 * l_oh_rate_ov/100;
320
321 --dbms_output.put_line('the l_oh is '||l_oh);
322
323 l_new_fiscal_year := l_new_fiscal_year + 1;
324 l_start_date := l_end_date +1;
325 l_end_date := add_months(l_end_date,12);
326 if p_budget_end_date< l_end_date then
327 l_end_date := p_budget_end_date;
328 end if;
329
330 if i = (l_fiscal_diff) then
331 l_end_date := p_budget_end_date;
332 end if;
333 x_oh_value := nvl(l_oh,0) + nvl(x_oh_value,0);
334 x_oh_value_ov := nvl(l_oh_ov,0) + nvl(x_oh_value_ov,0);
335
336 END LOOP;
337 x_return_status := 'S';
338 EXCEPTION
339 when FND_API.G_EXC_ERROR then
340 x_return_status := l_return_status;
341 x_msg_data := l_msg_data;
342 fnd_msg_pub.count_and_get(p_count => x_msg_count,
343 p_data => x_msg_data);
344 when others then
345 x_return_status := 'U';
346 x_msg_data := SQLCODE||' '||SQLERRM;
347 fnd_msg_pub.add_exc_msg(G_PKG_NAME, 'CALC_OH');
348 fnd_msg_pub.count_and_get(p_count => x_msg_count,
349 p_data => x_msg_data);
350 END calc_oh;
351
352
353 PROCEDURE calc_oh_eb( p_proposal_id NUMBER
354 ,p_version_id NUMBER
355 ,p_base_amount NUMBER
356 ,p_budget_start_date DATE
357 ,p_budget_end_date DATE
358 ,x_oh_value OUT NOCOPY NUMBER
359 ,x_oh_value_ov OUT NOCOPY NUMBER
360 ,x_eb_value OUT NOCOPY NUMBER
361 ,x_eb_value_ov OUT NOCOPY NUMBER
362 ,p_activity_type_code VARCHAR2
363 ,p_location_code VARCHAR2
364 ,p_rate_class_id_oh NUMBER
365 ,p_rate_type_id_oh NUMBER
366 ,p_rate_class_id_eb NUMBER
367 ,p_rate_type_id_eb NUMBER
368 ,x_return_status OUT NOCOPY VARCHAR2
369 ,x_msg_data OUT NOCOPY VARCHAR2
370 ,x_msg_count OUT NOCOPY NUMBER) is
371
372 l_no_of_days NUMBER;
373 l_start_fiscal_year NUMBER(4);
374 l_end_fiscal_year NUMBER(4);
375 l_new_fiscal_year NUMBER(4);
376 l_fiscal_diff NUMBER(4);
377 l_oh NUMBER;
378 l_oh_ov NUMBER;
379 l_rate_start_date DATE;
380 l_start_date DATE;
381 l_end_date DATE;
382 l_start_f_start_date DATE;
383 l_start_f_end_date DATE;
384 l_end_f_start_date DATE;
385 l_end_f_end_date DATE;
386 l_rate NUMBER(5,2);
387 l_rate_ov NUMBER(5,2);
388 l_eb NUMBER;
389 l_eb_ov NUMBER;
390 l_return_status VARCHAR2(1);
391 l_msg_data VARCHAR2(200);
392
393 BEGIN
394 fnd_msg_pub.initialize;
395 l_no_of_days := (p_budget_end_date - p_budget_start_date) +1 ;
396 get_date_details(p_budget_start_date
397 ,l_start_fiscal_year
398 ,l_start_f_start_date
399 ,l_start_f_end_date
400 ,l_return_status
401 ,l_msg_data);
402
403 if l_return_status <> 'S' then
404 raise FND_API.G_EXC_ERROR;
405 end if;
406
407 get_date_details(p_budget_end_date
408 ,l_end_fiscal_year
409 ,l_end_f_start_date
413
410 ,l_end_f_end_date
411 ,l_return_status
412 ,l_msg_data);
414 if l_return_status <> 'S' then
415 raise FND_API.G_EXC_ERROR;
416 end if;
417 l_fiscal_diff := (l_end_fiscal_year - l_start_fiscal_year) +1;
418 --dbms_output.put_line('the fiscal diff is '||l_fiscal_diff);
419 l_start_date := p_budget_start_date;
420 l_new_fiscal_year := l_start_fiscal_year;
421 l_end_date := l_start_f_end_date;
422
423 if l_return_status <> 'S' then
424 raise FND_API.G_EXC_ERROR;
425 end if;
426
427 for i IN 1 .. l_fiscal_diff
428 LOOP
429
430 if l_fiscal_diff = 1 and i = 1 then
431 l_end_date := p_budget_end_date;
432 end if;
433
434 get_rate(p_proposal_id
435 ,p_version_id
436 ,l_new_fiscal_year
437 ,p_activity_type_code
438 ,p_location_code
439 ,p_rate_class_id_eb
440 ,p_rate_type_id_eb
441 ,l_rate
442 ,l_rate_ov
443 ,l_rate_start_date
444 ,l_return_status
445 ,l_msg_data);
446 --dbms_output.put_line('the rate for eb is '||l_rate);
447 --dbms_output.put_line('the rate for eb_ov is '||l_rate_ov);
448 if l_return_status NOT IN ('S','I') then
449 raise FND_API.G_EXC_ERROR;
450 end if;
451
452 l_eb := nvl(p_base_amount * ((l_end_date - l_start_date)+ 1)/l_no_of_days
453 * nvl(l_rate,0)/100,0);
454
455 l_eb_ov := nvl(p_base_amount * ((l_end_date - l_start_date)+ 1)/l_no_of_days
456 * nvl(l_rate_ov,0)/100,0);
457 --dbms_output.put_line('the l_eb is '||l_eb);
458 --dbms_output.put_line('the l_end_date_eb is '||l_end_date);
459 --dbms_output.put_line('the l_start_date_eb is '||l_start_date);
460 --dbms_output.put_line('the l_no_of_days_eb is '||l_no_of_days);
461
462 get_rate(p_proposal_id
463 ,p_version_id
464 ,l_new_fiscal_year
465 ,p_activity_type_code
466 ,p_location_code
467 ,p_rate_class_id_oh
468 ,p_rate_type_id_oh
469 ,l_rate
470 ,l_rate_ov
471 ,l_rate_start_date
472 ,l_return_status
473 ,l_msg_data);
474 --dbms_output.put_line('the rate for oh is '||l_rate);
475 if l_return_status NOT IN ('S','I') then
476 raise FND_API.G_EXC_ERROR;
477 end if;
478
479
480 l_oh := ((p_base_amount + ((nvl(l_eb,0) * l_no_of_days/((l_end_date - l_start_date)+ 1)))) *
481 ((l_end_date - l_start_date)+ 1))/l_no_of_days * l_rate/100;
482
483
484 l_oh_ov := ((p_base_amount + ((nvl(l_eb_ov,0) * l_no_of_days/((l_end_date - l_start_date)+ 1)))) *
485 ((l_end_date - l_start_date)+ 1))/l_no_of_days * l_rate_ov/100;
486
487 --dbms_output.put_line('the l_oh is '||l_oh);
488 --dbms_output.put_line('the l_end_date_oh is '||l_end_date);
489 --dbms_output.put_line('the l_start_date_oh is '||l_start_date);
490 --dbms_output.put_line('the l_no_of_days_oh is '||l_no_of_days);
491
492 l_new_fiscal_year := l_new_fiscal_year + 1;
493 l_start_date := l_end_date +1;
494 l_end_date := add_months(l_end_date,12);
495 if p_budget_end_date< l_end_date then
496 l_end_date := p_budget_end_date;
497 end if;
498
499 if i = (l_fiscal_diff) then
500 l_end_date := p_budget_end_date;
501 end if;
502 x_eb_value := l_eb + nvl(x_eb_value,0);
503 x_eb_value_ov := l_eb_ov + nvl(x_eb_value_ov,0);
504 x_oh_value := l_oh + nvl(x_oh_value,0);
505 x_oh_value_ov := l_oh_ov + nvl(x_oh_value_ov,0);
506
507 END LOOP;
508 x_return_status := 'S';
509
510 EXCEPTION
511 when FND_API.G_EXC_ERROR then
512 x_return_status := l_return_status;
513 x_msg_data := l_msg_data;
514 fnd_msg_pub.count_and_get(p_count => x_msg_count,
515 p_data => x_msg_data);
516 when others then
517 x_return_status := 'U';
518 x_msg_data := SQLCODE||' '||SQLERRM;
519 fnd_msg_pub.add_exc_msg(G_PKG_NAME, 'CALC_OH_EB');
520 fnd_msg_pub.count_and_get(p_count => x_msg_count,
521 p_data => x_msg_data);
522 END calc_oh_eb;
523
524 /* Inflation is calculated based on each fiscal year. If
525 a period falls in two(or more) fiscal years, the inflation is calculated
526 based on the two(or more) rates available for the two(or more) fiscal years.
527 For each part, number of days of the period falling in each fiscal year
528 and the corresponding rate for the fiscal year is taken to calculate
529 the inflation
530 */
531
532 PROCEDURE calc_inflation(p_proposal_id NUMBER
533 ,p_version_id NUMBER
534 ,p_base_amount NUMBER
535 ,p_budget_start_date DATE
536 ,p_budget_end_date DATE
537 ,x_inflated_amt OUT NOCOPY NUMBER
538 ,p_activity_type_code VARCHAR2
539 ,p_location_code VARCHAR2
540 ,p_rate_class_id_inf NUMBER
541 ,p_rate_type_id_inf NUMBER
542 ,x_return_status OUT NOCOPY VARCHAR2
543 ,x_msg_data OUT NOCOPY VARCHAR2
544 ,x_msg_count OUT NOCOPY NUMBER) is
545
549 l_new_fiscal_year NUMBER(4);
546 l_no_of_days NUMBER;
547 l_start_fiscal_year NUMBER(4);
548 l_end_fiscal_year NUMBER(4);
550 l_fiscal_diff NUMBER(4);
551 l_inflated_amount NUMBER;
552 l_base_amount NUMBER;
553 l_rate_start_date DATE;
554 l_start_date DATE;
555 l_end_date DATE;
556 l_start_f_start_date DATE;
557 l_start_f_end_date DATE;
558 l_end_f_start_date DATE;
559 l_end_f_end_date DATE;
560 l_inflated_rate NUMBER(5,2);
561 l_inflated_rate_ov NUMBER(5,2);
562 l_return_status VARCHAR2(1);
563 l_msg_data VARCHAR2(200);
564 BEGIN
565 fnd_msg_pub.initialize;
566 l_base_amount := p_base_amount;
567 l_no_of_days := (p_budget_end_date - p_budget_start_date) +1 ;
568 get_date_details(p_budget_start_date
569 ,l_start_fiscal_year
570 ,l_start_f_start_date
571 ,l_start_f_end_date
572 ,l_return_status
573 ,l_msg_data);
574 if l_return_status <> 'S' then
575 raise FND_API.G_EXC_ERROR;
576 end if;
577
578 get_date_details(p_budget_end_date
579 ,l_end_fiscal_year
580 ,l_end_f_start_date
581 ,l_end_f_end_date
582 ,l_return_status
583 ,l_msg_data);
584
585 if l_return_status <> 'S' then
586 raise FND_API.G_EXC_ERROR;
587 end if;
588
589 /*fiscal diff is calculated to seed how many fiscal years
590 is a period spans into */
591
592 l_fiscal_diff := (l_end_fiscal_year - l_start_fiscal_year) +1;
593 l_start_date := p_budget_start_date;
594 l_new_fiscal_year := l_start_fiscal_year;
595 l_end_date := l_start_f_end_date;
596 --dbms_output.put_line('l_fiscal_diff is'||l_fiscal_diff);
597
598 for i IN 1 .. l_fiscal_diff
599 LOOP
600
601 if l_fiscal_diff = 1 and i = 1 then
602 l_end_date := p_budget_end_date;
603 end if;
604 get_rate(p_proposal_id
605 ,p_version_id
606 ,l_new_fiscal_year
607 ,p_activity_type_code
608 ,p_location_code
609 ,p_rate_class_id_inf
610 ,p_rate_type_id_inf
611 ,l_inflated_rate
612 ,l_inflated_rate_ov
613 ,l_rate_start_date
614 ,l_return_status
615 ,l_msg_data);
616 if l_return_status NOT IN ('S','I') then
617 raise FND_API.G_EXC_ERROR;
618 end if;
619 /*
620 --dbms_output.put_line('the inflated_rate is '||l_inflated_rate);
621 --dbms_output.put_line('the l_no_of_days '||l_no_of_days);
622 --dbms_output.put_line('the l_start_Date '||l_start_date);
623 --dbms_output.put_line('the l_end_Date '||l_end_date);
624 --dbms_output.put_line('the the base amount '||l_base_amount);
625 */
626 l_inflated_amount := l_base_amount * ((l_end_date - l_start_date)+ 1)/l_no_of_days
627 * l_inflated_rate_ov/100;
628 --dbms_output.put_line('the inflated amount is '||l_inflated_amount);
629 l_new_fiscal_year := l_new_fiscal_year + 1;
630 l_start_date := l_end_date+1;
631
632 if p_budget_end_date > add_months(l_end_date,12) then
633 l_end_date := add_months(l_end_date,12);
634 else
635 l_end_date := p_budget_end_date;
636 end if;
637
638 if i = (l_fiscal_diff) then
639 l_end_date := p_budget_end_date;
640 end if;
641 --l_base_amount := l_base_amount + nvl(l_inflated_amount,0);
642 --x_inflated_amt := l_base_amount;
643 x_inflated_amt := nvl(x_inflated_amt,0) + nvl(l_inflated_amount,0);
644 END LOOP;
645 x_inflated_amt := l_base_amount + x_inflated_amt;
646 x_return_status := 'S';
647 EXCEPTION
648 when FND_API.G_EXC_ERROR then
649 x_return_status := l_return_status;
650 x_msg_data := l_msg_data;
651 fnd_msg_pub.count_and_get(p_count => x_msg_count,
652 p_data => x_msg_data);
653 when others then
654 x_return_status := 'U';
655 x_msg_data := SQLCODE||' '||SQLERRM;
656 fnd_msg_pub.add_exc_msg(G_PKG_NAME, 'CALC_INFLATION');
657 fnd_msg_pub.count_and_get(p_count => x_msg_count,
658 p_data => x_msg_data);
659 END calc_inflation;
660
661
662
663
664 PROCEDURE calc_sal_between_months(p_end_date DATE
665 ,p_start_date DATE
666 ,p_base_amount NUMBER
667 ,x_final_sal OUT NOCOPY NUMBER
668 ,x_return_status OUT NOCOPY VARCHAR2
669 ,x_msg_data OUT NOCOPY VARCHAR2) IS
670 l_start_date1 DATE;
671 l_end_date1 DATE;
672 l_end_date2 DATE;
673 l_base_amount1 NUMBER;
674 l_base_amount2 NUMBER;
675 l_base_amount3 NUMBER;
676 l_months_diff NUMBER(10);
677 BEGIN
678 l_end_date1 := add_months(last_day(p_end_date), -1);
679 l_start_date1:= last_day(p_start_date);
680 --dbms_output.put_line('l_end_date1 and l_start_date1 are'||l_end_date1||'and'||l_start_date1);
681 l_months_diff:= months_between((l_end_date1+1 ), (l_start_date1 +1));
682 --dbms_output.put_line('l_months_diff is >>> '||l_months_diff);
683
684 l_base_amount1 := (p_end_date - (l_end_date1))/(last_day(p_end_date)-l_end_date1)*p_base_amount/12;
685 --dbms_output.put_line('l_base_amount1 is >>>> '||l_base_amount1);
689
686
687 l_base_amount2 := ((l_start_date1 - p_start_date) + 1)/(l_start_date1-add_months(l_start_date1, -1))
688 * p_base_amount/12;
690 --dbms_output.put_line('l_base_amount2 is>>>> '||l_base_amount2);
691
692 l_base_amount3 := l_months_diff * p_base_amount/12;
693 --dbms_output.put_line('l_base_amount3 is>>>>>'||l_base_amount3);
694
695 x_final_sal := l_base_amount1 + l_base_amount2 + l_base_amount3;
696 EXCEPTION
697 when others then
698 x_return_status := 'U';
699 x_msg_data := SQLCODE||' '||SQLERRM;
700 fnd_msg_pub.add_exc_msg(G_PKG_NAME, 'CALC_SAL_BETWEEN_MONTHS');
701 END calc_sal_between_months;
702
703
704 PROCEDURE calc_salary(p_proposal_id NUMBER
705 ,p_version_id NUMBER
706 ,p_base_amount NUMBER
707 ,p_effective_date DATE
708 ,p_appointment_type VARCHAR2
709 ,p_line_start_date DATE
710 ,p_line_end_date DATE
711 ,x_inflated_salary OUT NOCOPY NUMBER
712 ,x_inflated_salary_ov OUT NOCOPY NUMBER
713 ,p_expenditure_type VARCHAR2
714 ,p_expenditure_category_flag VARCHAR2
715 ,p_activity_type_code VARCHAR2
716 ,p_location_code VARCHAR2
717 ,x_return_status OUT NOCOPY VARCHAR2
718 ,x_msg_data OUT NOCOPY VARCHAR2
719 ,x_msg_count OUT NOCOPY NUMBER) is
720
721 l_loop_counter NUMBER(10);
722 l_effect_fiscal_year NUMBER(4);
723 l_effect_f_start_date DATE;
724 l_effect_f_end_date DATE;
725 l_start_f_start_date DATE;
726 l_start_f_end_date DATE;
727 l_end_f_start_date DATE;
728 l_end_f_end_date DATE;
729 l_start_date DATE;
730 l_end_date DATE;
731 l_rate_start_date DATE;
732 l_final_amount1 NUMBER;
733 l_final_amount1_ov NUMBER;
734 l_final_amount2 NUMBER;
735 l_final_amount2_ov NUMBER;
736 l_base_amount NUMBER;
737 l_base_amount_ov NUMBER;
738 l_final_amount NUMBER;
739 l_final_amount_ov NUMBER;
740 l_inflated_amount NUMBER;
741 l_rate_class_id_inf NUMBER(15);
742 l_rate_type_id_inf NUMBER(15);
743 l_inflation_rate NUMBER(5,2);
744 l_inflation_rate_ov NUMBER(5,2);
745 l_start_fiscal_year NUMBER(4);
746 l_end_fiscal_year NUMBER(4);
747 l_new_fiscal_year NUMBER(4);
748 l_fiscal_diff NUMBER(4);
749 l_return_status VARCHAR2(1);
750 l_msg_data VARCHAR2(200);
751
752 cursor c_inf_rates is
753 select rate,start_date, fiscal_year
754 from igw_institute_rates ir
755 where ir.rate_class_id = l_rate_class_id_inf
756 and ir.rate_type_id = l_rate_type_id_inf
757 and ir.activity_type_code = p_activity_type_code
758 and ir.location_code = p_location_code
759 and (ir.start_date > p_line_start_date and ir.start_date <= p_line_end_date);
760
761 BEGIN
762 fnd_msg_pub.initialize;
763 if p_appointment_type = '12' then
764 l_base_amount := p_base_amount;
765 elsif p_appointment_type = '11' then
766 l_base_amount := p_base_amount * 12/11;
767 elsif p_appointment_type = '10' then
768 l_base_amount := p_base_amount * 12/10;
769 elsif p_appointment_type = '9' then
770 l_base_amount := p_base_amount * 12/9;
771 elsif p_appointment_type = '8' then
772 l_base_amount := p_base_amount * 12/8;
773 elsif p_appointment_type = '7' then
774 l_base_amount := p_base_amount * 12/7;
775 elsif p_appointment_type = '6' then
776 l_base_amount := p_base_amount * 12/6;
777 elsif p_appointment_type = '5' then
778 l_base_amount := p_base_amount * 12/5;
779 elsif p_appointment_type = '4' then
780 l_base_amount := p_base_amount * 12/4;
781 elsif p_appointment_type = '3' then
782 l_base_amount := p_base_amount * 12/03;
783 elsif p_appointment_type = '2' then
784 l_base_amount := p_base_amount * 12/2;
785 elsif p_appointment_type = '1' then
786 l_base_amount := p_base_amount * 12/1;
787 end if;
788 --dbms_output.put_line('the base amount is'||l_base_amount);
789 l_base_amount_ov := l_base_amount;
790 get_date_details(p_effective_date
791 ,l_effect_fiscal_year
792 ,l_effect_f_start_date
793 ,l_effect_f_end_date
794 ,l_return_status
795 ,l_msg_data);
796 if l_return_status <> 'S' then
797 raise FND_API.G_EXC_ERROR;
798 end if;
799 get_date_details(p_line_start_date
800 ,l_start_fiscal_year
801 ,l_start_f_start_date
802 ,l_start_f_end_date --USED LATER DOWN
803 ,l_return_status
804 ,l_msg_data);
805 --dbms_output.put_line('the l_start_fiscal_year is '||l_start_fiscal_year);
806 --dbms_output.put_line('the l_start_f_end_date is '||l_start_f_end_date);
807 if l_return_status <> 'S' then
808 raise FND_API.G_EXC_ERROR;
809 end if;
810 get_date_details(p_line_end_date
811 ,l_end_fiscal_year
812 ,l_end_f_start_date
813 ,l_end_f_end_date
814 ,l_return_status
815 ,l_msg_data);
816 --dbms_output.put_line('the l_end_fiscal_year is '||l_end_fiscal_year);
817
818 if l_return_status <> 'S' then
819 raise FND_API.G_EXC_ERROR;
820 end if;
824 get_rate_id (p_expenditure_type
821 l_loop_counter := (l_start_fiscal_year - l_effect_fiscal_year) + 1;
822 --dbms_output.put_line('the loop counter l_loop_counter is '||l_loop_counter);
823 l_new_fiscal_year := l_effect_fiscal_year;
825 ,p_expenditure_category_flag
826 ,'I'
827 ,l_rate_class_id_inf
828 ,l_rate_type_id_inf
829 ,l_return_status
830 ,l_msg_data);
831 --dbms_output.put_line('the l_rate_class_id_inf is '||l_rate_class_id_inf);
832 --dbms_output.put_line('the l_rate_type_id_inf is '||l_rate_type_id_inf);
833
834 if l_return_status <> 'S' then
835 raise FND_API.G_EXC_ERROR;
836 end if;
837
838 /*the following code inflates the salary(if any) till the period line
839 start date including the first day of the period start date. */
840
841 for i in 1 .. l_loop_counter
842 LOOP --infation calculation loop
843 --dbms_output.put_line('the l_new_fiscal_year is '||l_new_fiscal_year);
844
845 get_rate(p_proposal_id
846 ,p_version_id
847 ,l_new_fiscal_year
848 ,p_activity_type_code
849 ,p_location_code
850 ,l_rate_class_id_inf
851 ,l_rate_type_id_inf
852 ,l_inflation_rate
853 ,l_inflation_rate_ov
854 ,l_rate_start_date
855 ,l_return_status
856 ,l_msg_data);
857 --dbms_output.put_line('the l_rate_start_date for inflation is '||l_rate_start_date);
858 --dbms_output.put_line('the l_inflation_rate is '|| l_inflation_rate);
859 --dbms_output.put_line('the l_inflation_rate_ov is '|| l_inflation_rate_ov);
860 if l_return_status NOT IN ('S','I') then
861 raise FND_API.G_EXC_ERROR;
862 end if;
863 if l_inflation_rate is not null then
864 if i = 1 and l_rate_start_date <= p_effective_date then
865 l_base_amount := l_base_amount;
866 l_base_amount_ov := l_base_amount_ov;
867 elsif i = l_loop_counter and l_rate_start_date > p_line_start_date then
868 l_base_amount := l_base_amount;
869 l_base_amount_ov := l_base_amount_ov;
870 else
871 l_base_amount_ov := l_base_amount_ov * (1 + l_inflation_rate_ov/100);
872 l_base_amount := l_base_amount * (1 + l_inflation_rate/100);
873 end if;
874 else
875 l_base_amount := l_base_amount;
876 l_base_amount_ov := l_base_amount_ov;
877 end if;
878 --dbms_output.put_line('the l_base_amount is '||l_base_amount);
879 --dbms_output.put_line('the l_base_amount_ov is '||l_base_amount_ov);
880 l_new_fiscal_year := l_new_fiscal_year + 1;
881 END LOOP; --inflation calculation loop
882
883 --INITIALIZING VARIABLES FOR REUSAGE
884 l_fiscal_diff:= (l_end_fiscal_year - l_start_fiscal_year) + 1;
885 --dbms_output.put_line('the l_fiscal_diff is '||l_fiscal_diff);
886 l_new_fiscal_year := l_start_fiscal_year;
887 l_start_date := p_line_start_date;
888 l_rate_start_date:= null;
889 l_inflation_rate_ov := null;
890
891 /* the following section is if a particular expenditure type is not assigned to
892 inflation rate class */
893 if l_rate_class_id_inf is null and l_rate_type_id_inf is null then
894 l_end_date := p_line_end_date;
895 --dbms_output.put_line('1st stage');
896 calc_sal_between_months(l_end_date
897 ,l_start_date
898 ,l_base_amount
899 ,l_final_amount
900 ,l_return_status
901 ,l_msg_data);
902
903 if l_return_status <> 'S' then
904 raise FND_API.G_EXC_ERROR;
905 end if;
906
907 calc_sal_between_months(l_end_date
908 ,l_start_date
909 ,l_base_amount_ov
910 ,l_final_amount_ov
911 ,l_return_status
912 ,l_msg_data);
913
914 if l_return_status <> 'S' then
915 raise FND_API.G_EXC_ERROR;
916 end if;
917
918 x_inflated_salary := l_final_amount;
919 x_inflated_salary_ov := l_final_amount_ov;
920 else
921
922 /* following is the code till go through the loop
923 for all the inflation rates found in the cursor */
924
925 for rec_inf_rates in c_inf_rates
926 LOOP
927 l_end_date := rec_inf_rates.start_date-1;
928 --dbms_output.put_line('l_start_date in the loop'||l_start_date);
929 --dbms_output.put_line('l_end_date in the loop'||l_end_date);
930 --dbms_output.put_line('l_base_amount 1st'||l_base_amount);
931
932 calc_sal_between_months(l_end_date
933 ,l_start_date
934 ,l_base_amount
935 ,l_final_amount1
936 ,l_return_status
937 ,l_msg_data);
938
939 if l_return_status <> 'S' then
940 raise FND_API.G_EXC_ERROR;
941 end if;
942
943 calc_sal_between_months(l_end_date
944 ,l_start_date
945 ,l_base_amount_ov
946 ,l_final_amount1_ov
947 ,l_return_status
948 ,l_msg_data);
949 if l_return_status <> 'S' then
950 raise FND_API.G_EXC_ERROR;
951 end if;
952
953 l_start_date := rec_inf_rates.start_date;
954
955
956 l_final_amount := nvl(l_final_amount,0)+ nvl(l_final_amount1,0);
957
958
959 l_final_amount_ov := nvl(l_final_amount_ov,0) + nvl(l_final_amount1_ov,0);
960
961 l_new_fiscal_year := l_new_fiscal_year + 1;
962
963 x_inflated_salary := l_final_amount;
964 x_inflated_salary_ov := l_final_amount_ov;
965
966 --initializing local variables to zero
967 l_final_amount1 := null;
968
969 begin
970 select applicable_rate
971 into l_inflation_rate_ov
972 from igw_prop_rates
973 where rate_class_id = l_rate_class_id_inf
974 and rate_type_id = l_rate_type_id_inf
975 and activity_type_code = p_activity_type_code
976 and location_code = p_location_code
977 and fiscal_year = rec_inf_rates.fiscal_year
978 and proposal_id = p_proposal_id
979 and version_id = p_version_id;
980 exception
981 when no_data_found then
982 l_inflation_rate_ov := rec_inf_rates.rate;
983 end;
984
985 l_base_amount := l_base_amount * (1 +rec_inf_rates.rate/100);
986 l_base_amount_ov := l_base_amount_ov * (1 +l_inflation_rate_ov/100);
987
988 END LOOP;
989 --dbms_output.put_line('l_final_amount is outside the loop'||l_final_amount);
990
991 l_start_date := nvl(l_end_date+1,p_line_start_date);
992 l_end_date := p_line_end_date;
993 --dbms_output.put_line('l_start_date outside'||l_start_date);
994 --dbms_output.put_line('l_end_date outside'||l_end_date);
995 --dbms_output.put_line('l_base_amount 1st'||l_base_amount);
996
997 /* following amount is calculated for the last part for the dates
998 between last inflation record found and the p_line_end_date */
999
1000
1001 calc_sal_between_months(l_end_date
1002 ,l_start_date
1003 ,l_base_amount
1004 ,l_final_amount2
1005 ,l_return_status
1006 ,l_msg_data);
1007
1008 if l_return_status <> 'S' then
1009 raise FND_API.G_EXC_ERROR;
1010 end if;
1011 --dbms_output.put_line('l_final_amount2 is '||l_final_amount2);
1012
1013 calc_sal_between_months(l_end_date
1014 ,l_start_date
1015 ,l_base_amount_ov
1016 ,l_final_amount2_ov
1017 ,l_return_status
1018 ,l_msg_data);
1019 if l_return_status <> 'S' then
1020 raise FND_API.G_EXC_ERROR;
1021 end if;
1022
1023 l_final_amount := nvl(l_final_amount,0)+ nvl(l_final_amount2,0);
1024
1025
1026 l_final_amount_ov := nvl(l_final_amount_ov,0) + nvl(l_final_amount2_ov,0);
1027
1028 x_inflated_salary := l_final_amount;
1029 x_inflated_salary_ov := l_final_amount_ov;
1030 end if;
1031 x_return_status := 'S';
1032 EXCEPTION
1033 when FND_API.G_EXC_ERROR then
1034 x_return_status := l_return_status;
1035 x_msg_data := l_msg_data;
1036 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1037 p_data => x_msg_data);
1038 when others then
1039 x_return_status := 'U';
1040 x_msg_data := SQLCODE||' '||SQLERRM;
1041 fnd_msg_pub.add_exc_msg(G_PKG_NAME, 'CALC_INFLATION');
1042 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1043 p_data => x_msg_data);
1044 END calc_salary;
1045
1046
1047 END IGW_OVERHEAD_CAL;