[Home] [Help]
PACKAGE BODY: APPS.LNS_FIN_UTILS
Source
1 PACKAGE BODY LNS_FIN_UTILS AS
2 /* $Header: LNS_FIN_UTILS_B.pls 120.9.12010000.3 2008/11/21 12:23:52 mbolli ship $ */
3
4 --------------------------------------------
5 -- declaration of global variables and types
6 --------------------------------------------
7 G_DEBUG_COUNT NUMBER := 0;
8 G_DEBUG BOOLEAN := FALSE;
9 G_FILE_NAME CONSTANT VARCHAR2(30) := 'LNS_FIN_UTILS_B.pls';
10
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'LNS_FIN_UTILS';
12 G_DAYS_COUNT NUMBER;
13 G_DAYS_IN_YEAR NUMBER;
14
15 --------------------------------------------
16 -- internal package routines
17 --------------------------------------------
18
19 procedure logMessage(log_level in number
20 ,module in varchar2
21 ,message in varchar2)
22 is
23
24 begin
25 IF log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
26 FND_LOG.STRING(log_level, module, message);
27 END IF;
28
29 end;
30
31 -- internal usage only
32 function formatTerm(p_timeString IN varchar2) return varchar2
33 is
34
35 l_temp varchar2(30);
36 begin
37
38 -- this logic is to handle "MONTHLY" => "MONTHS" ETC...
39 if substr(p_timeString, length(p_timeString) - 1, 2) = 'LY' then
40 l_temp := substr(p_timeString, 1, length(p_timeString) - 2) || 'S';
41 else
42 l_temp := p_timeString;
43 end if;
44
45 return l_temp;
46
47 end;
48
49 /*=========================================================================
50 || PUBLIC function julian_date
51 ||
52 || DESCRIPTION
53 || Overview: function returns a number representing the julian date |
54 ||
55 || PARAMETERS
56 || Parameter: p_date => date
57 ||
58 || Return value: number
59 ||
60 || MODIFICATION HISTORY
61 || Date Author Description of Changes
62 || 12/08/2003 8:16PM raverma Created
63 ||
64 *=======================================================================*/
65 function julian_date(p_date in date) return number
66 is
67 --l_char VARCHAR(10);
68 l_num NUMBER(20);
69
70 begin
71
72 SELECT to_number(TO_CHAR(p_date, 'J'))
73 INTO l_num
74 FROM DUAL;
75
76 RETURN(l_num);
77
78 end julian_date;
79
80
81 /*=========================================================================
82 || PUBLIC FUNCTION getNextDate
83 ||
84 || DESCRIPTION
85 || this is used to add/subtract months to a particular date
86 ||
87 || PSEUDO CODE/LOGIC
88 ||
89 || PARAMETERS
90 || p_date => beginning date
91 || p_interval_type => MONTHLY, QUARTERLY, YEARLY
92 || (translates to the number of months to add/subtract)
93 || p_direction => 1 = add, -1 = subtract
94 || Return value:
95 ||
96 || Source Tables: NA
97 ||
98 || Target Tables: NA
99 ||
100 || MODIFICATION HISTORY
101 || Date Author Description of Changes
102 || 03/22/2004 9:13PM raverma Created
103 || 06/26/2006 5:27PM karamach Added additional values for p_interval_type and fixed the set_token call for the error message to fix bug5215501
104 *=======================================================================*/
105 function getNextDate(p_date in date
106 ,p_interval_type in varchar2
107 ,p_direction in number) return Date
108 is
109 l_next_date date;
110 l_multiplier number;
111 l_api_name varchar2(25);
112 l_add_months boolean;
113
114 begin
115 l_api_name := 'getNextDate';
116 l_add_months := true;
117
118 if p_interval_type in ('WEEKLY','WEEKS') then
119 l_multiplier := 7;
120 l_add_months := false;
121
122 elsif p_interval_type in ('BIWEEKLY','BIWEEKS') then
123 l_multiplier := 14;
124 l_add_months := false;
125
126 elsif p_interval_type in ('SEMI-MONTHLY','SEMI-MONTHS') then
127 l_multiplier := 15;
128 l_add_months := false;
129
130 elsif p_interval_type in ('MONTHLY','MONTHS') then
131 l_multiplier := 1;
132
133 elsif p_interval_type in ('BI-MONTHLY','BI-MONTHS') then
134 l_multiplier := 2;
135
136 elsif p_interval_type in ('QUARTERLY','QUARTERS') then
137 l_multiplier := 3;
138
139 elsif p_interval_type in ('SEMI-ANNUALLY','SEMI-ANNUALS') then
140 l_multiplier := 6;
141
142 elsif p_interval_type in ('YEARLY','YEARS') then
143 l_multiplier := 12;
144
145 else
146 FND_MESSAGE.Set_Name('LNS', 'LNS_INVALID_INTERVAL');
147 FND_MESSAGE.SET_TOKEN('INTERVAL',p_interval_type);
148 FND_MSG_PUB.Add;
149 RAISE FND_API.G_EXC_ERROR;
150 end if;
151
152 l_multiplier := l_multiplier * p_direction;
153
154 if l_add_months then
155 l_next_date := add_months(p_date, l_multiplier);
156 else
157 l_next_date := p_date + l_multiplier;
158 end if;
159
160 return trunc(l_next_date);
161
162 end getNextDate;
163
164 /*=========================================================================
165 || PUBLIC PROCEDURE getDayCount
166 ||
167 || DESCRIPTION
168 ||
169 || Overview: this function will return NUMERATOR FOR PERIODIC RATE
170 || period of time so the interest in a give month at
171 || 12% interest per year will return a 1% monthly rate
172 ||
173 || supports the following day count methods
174 || 1. ACTUAL_ACTUAL
175 || 2. 30/360
176 || 3. 30E/360
177 || 4. 30E+/360
178 || 5. ACTUAL_360
179 || 6. ACTUAL_365
180 || 7. ACTUAL_365L
181 ||
182 || Parameter: p_start_date start date of period
183 || p_end_date end date of period
184 || p_days_count_method = counting method
185 ||
186 || Source Tables: NA
187 ||
188 || Target Tables: NA
189 ||
190 || Return value: number of days between 2 dates
191 ||
192 || MODIFICATION HISTORY
193 || Date Author Description of Changes
194 || 12/09/2003 1:51PM raverma Created
195 || 2/26/2004 raverma added more robust day / year counting methodolgy
196 || 05/27/2008 scherkas Fixed bug 6498179: changed days count for february
197 ||
198 *=======================================================================*/
199 function getDayCount(p_start_date in date
200 ,p_end_date in date
201 ,p_day_count_method in varchar2) return number
202 is
203
204 l_api_name varchar2(25);
205 l_day_count number;
206 l_day1 number;
207 l_day2 number;
208 l_month1 number;
209 l_month2 number;
210 l_year1 number;
211 l_year2 number;
212
213 l_numerator number;
214 l_denominator number;
215 l_count_method varchar2(30);
216
217 begin
218
219 l_api_name := 'getDayCount';
220
221 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': p_start_date: ' || p_start_date);
222 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': p_end_date: ' || p_end_date);
223 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': p_day_count_method: ' || p_day_count_method);
224
225 if p_end_date < p_start_date then
226 FND_MESSAGE.Set_Name('LNS', 'LNS_PERIOD_INVALID');
227 FND_MSG_PUB.Add;
228 RAISE FND_API.G_EXC_ERROR;
229 end if;
230
231 if p_end_date is null then
232 FND_MESSAGE.Set_Name('LNS', 'LNS_NO_END_DATE');
233 FND_MSG_PUB.Add;
234 RAISE FND_API.G_EXC_ERROR;
235 end if;
236
237 if p_start_date is null then
238 FND_MESSAGE.Set_Name('LNS', 'LNS_NO_START_DATE');
239 FND_MSG_PUB.Add;
240 RAISE FND_API.G_EXC_ERROR;
241 end if;
242
243 if p_day_count_method is null then
244 FND_MESSAGE.Set_Name('LNS', 'LNS_NO_COUNT_METHOD');
245 FND_MSG_PUB.Add;
246 RAISE FND_API.G_EXC_ERROR;
247 end if;
248
249 l_day1 := to_number(to_char(p_start_date, 'DD'));
250 l_month1 := to_number(to_char(p_start_date, 'MM'));
251 l_year1 := to_number(to_char(p_start_date, 'YYYY'));
252
253 l_day2 := to_number(to_char(p_end_date, 'DD'));
254 l_month2 := to_number(to_char(p_end_date, 'MM'));
255 l_year2 := to_number(to_char(p_end_date, 'YYYY'));
256
257 if p_day_count_method = 'ACTUAL_ACTUAL' then
258 l_day_Count := to_number(TO_CHAR(p_end_date, 'J')) - to_number(TO_CHAR(p_start_date, 'J'));
259 --l_day_count := LNS_FIN_UTILS.Julian_date(p_end_date) - LNS_FIN_UTILS.Julian_date(p_start_date);
260
261 elsif p_day_count_method = '30/360' then
262
263 -- begin bug fix 6498179; scherkas; 10/12/2007;
264 if l_month1 = 2 then
265 if not LNS_FIN_UTILS.isLeapYear(l_year1) then
266 if l_day1 = 28 then
267 l_day1 := 30;
268 if l_day2 = 28 or l_day2 = 29 then
269 l_day2 := 30;
270 end if;
271 end if;
272 else
273 if l_day1 = 29 then
274 l_day1 := 30;
275 if l_day2 = 29 then
276 l_day2 := 30;
277 end if;
278 end if;
279 end if;
280 else
281 if l_day1 = 30 or l_day1 = 31 then
282 l_day1 := 30;
283 end if;
284 end if;
285
286 if l_month2 = 2 then
287 if not LNS_FIN_UTILS.isLeapYear(l_year2) then
288 if l_day2 = 28 then
289 l_day2 := 30;
290 if l_day1 = 28 or l_day1 = 29 then
291 l_day1 := 30;
292 end if;
293 end if;
294 else
295 if l_day2 = 29 then
296 l_day2 := 30;
297 if l_day1 = 29 then
298 l_day1 := 30;
299 end if;
300 end if;
301 end if;
302 else
303 if l_day2 = 30 or l_day2 = 31 then
304 l_day2 := 30;
305 end if;
306 end if;
307 -- end bug fix 6498179; scherkas; 10/12/2007;
308
309 l_day_count := ( ( l_day2 - l_day1 ) + 30 * ( l_month2 - l_month1 ) + 360 * ( l_year2 - l_year1 ) );
310
311 elsif p_day_count_method = '30E/360' then
312 if l_day1 = 31 then
313 l_day1 := 30;
314 end if;
315
316 if l_day2 = 31 then
317 l_day2 := 30;
318 end if;
319
320 l_day_count := ( ( l_day2 - l_day1 ) + 30 * ( l_month2 - l_month1 ) + 360 * ( l_year2 - l_year1 ) );
321
322 elsif p_day_count_method = '30E+/360' then
323 if l_day1 = 31 then
324 l_day1 := 30;
325 end if;
326
327 if l_day2 = 31 then
328 l_day2 := 1;
329 l_month2 := l_month2 + 1;
330 end if;
331
332 l_day_count := ( ( l_day2 - l_day1 ) + 30 * ( l_month2 - l_month1 ) + 360 * ( l_year2 - l_year1 ) );
333
334 elsif p_day_count_method = 'ACTUAL_360' then
335 l_day_Count := to_number(TO_CHAR(p_end_date, 'J')) - to_number(TO_CHAR(p_start_date, 'J'));
336 --l_day_count := LNS_FIN_UTILS.Julian_date(p_end_date) - LNS_FIN_UTILS.Julian_date(p_start_date);
337
338 elsif p_day_count_method = 'ACTUAL_365' then
339 l_day_Count := to_number(TO_CHAR(p_end_date, 'J')) - to_number(TO_CHAR(p_start_date, 'J'));
340 --l_day_count := LNS_FIN_UTILS.Julian_date(p_end_date) - LNS_FIN_UTILS.Julian_date(p_start_date);
341
342 elsif p_day_count_method = 'ACTUAL_365L' then
343 l_day_Count := to_number(TO_CHAR(p_end_date, 'J')) - to_number(TO_CHAR(p_start_date, 'J'));
344 --l_day_count := LNS_FIN_UTILS.Julian_date(p_end_date) - LNS_FIN_UTILS.Julian_date(p_start_date);
345
346 end if;
347
348 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': day count is: ' || l_day_count || ' by method ' || p_day_count_method);
349 return l_day_count;
350
351 end getDayCount;
352
353 -- returns DENOMINATOR FOR PERIODIC RATE
354 function daysInYear(p_year in number
355 ,p_year_count_method in varchar2) return number
356
357 is
358 l_days_in_year number;
359 l_api_name varchar2(25);
360
361 begin
362
363 l_api_name := 'daysInYear';
364
365 if p_year is not null then
366 -- default the year count to 360
367 if p_year_count_method is null then
368 l_days_in_year := 360;
369
370 -- if the year is actual number of days then we need to determine if
371 -- it's a leap year or not
372 elsif p_year_count_method = 'ACTUAL_ACTUAL' or p_year_count_method = 'ACTUAL_365L' then
373 if LNS_FIN_UTILS.isLeapYear(p_year) then
374 l_days_in_year := 366;
375 else
376 l_days_in_year := 365;
377 end if;
378
379 elsif p_year_count_method = 'ACTUAL_360' then
380 l_days_in_year := 360;
381
382 elsif p_year_count_method = 'ACTUAL_365' then
383 l_days_in_year := 365;
384
385 elsif p_year_count_method = '30/360' then
386 l_days_in_year := 360;
387
388 elsif p_year_count_method = '30E/360' then
389 l_days_in_year := 360;
390
391 elsif p_year_count_method = '30E+/360' then
392 l_days_in_year := 360;
393
394 --elsif p_year_count_method = '30/365' then
395 -- l_days_in_year := 365;
396
397 end if;
398
399 else
400 FND_MESSAGE.Set_Name('LNS', 'LNS_NO_YEAR');
401 FND_MSG_PUB.Add;
402 RAISE FND_API.G_EXC_ERROR;
403 end if;
404
405 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': days in year is: ' || l_days_in_year);
406 return l_days_in_year;
407
408 end daysInYear;
409
410 /*=========================================================================
411 || PUBLIC PROCEDURE isLeapYear
412 ||
413 || DESCRIPTION
414 || Overview: given any year return whether it is a leap year or not
415 ||
416 || PSEUDO CODE/LOGIC
417 ||
418 || PARAMETERS
419 || Parameter: p_year => year
420 ||
421 || Return value: boolean true = is a leap year; false = is not leap year
422 ||
423 || KNOWN ISSUES
424 || This is based on Gregorian Calendar. I will get ahead one day every 3,289 years
425 || One year = 365.2425 days
426 || NOTES
427 ||
428 || MODIFICATION HISTORY
429 || Date Author Description of Changes
430 || 12/08/2003 8:40PM raverma Created
431 ||
432 *=======================================================================*/
433 function isLeapYear(p_year in number) return boolean
434 is
435 l_return boolean;
436 begin
437
438 if p_year is not null then
439 if ((p_year mod 400) = 0) then
440 l_return := true;
441 elsif ((p_year mod 100) = 0) then
442 l_return := false;
443 elsif ((p_year mod 4) = 0) then
444 l_return := true;
445 else l_return := false;
446 end if;
447 else
448 FND_MESSAGE.Set_Name('LNS', 'LNS_NO_YEAR');
449 FND_MSG_PUB.Add;
450 RAISE FND_API.G_EXC_ERROR;
451 end if;
452 return l_return;
453
454 end isLeapYear;
455
456 /*=========================================================================
457 || PUBLIC PROCEDURE intervalsInPeriod
458 ||
459 || DESCRIPTION
460 || Overview: return the number of intervals in a given period type
461 || like how many WEEKS are in 4 YEARS
462 ||
463 || PSEUDO CODE/LOGIC
464 ||
465 || PARAMETERS
466 || Parameter: p_period_number number
467 || p_period_type1
468 || WEEKS 1
469 || MONTHS 2
470 || QUARTERS 3
471 || YEARS 4
472 || period_type2
473 || WEEKS 1
474 || MONTHS 2
475 || QUARTERS 3
476 || YEARS 4
477 ||
478 || period_type1 < period_type2
479 ||
480 || Return value: number of intervals
481 || SECONDS NO
482 || MINUTES NO
483 || HOURS NO
484 || DAYS NO
485 || WEEKS YES
486 || BI-WEEKS NO
487 || SEMI-MONTHS NO
488 || MONTHS YES
489 || BI-MONTHS NO
490 || QUARTERS YES
491 || SEMI-ANNUALS YES
492 || YEARS YES
493 ||
494 || MODIFICATION HISTORY
495 || Date Author Description of Changes
496 || 12/09/2003 4:19PM raverma Created
497 || 03/13/2004 raverma Karamach uses for UI rate schedule creation
498 || 29-Jan-2008 mbolli Bug#6634845 - Added the 'DAYS' to all period_types
499 *=======================================================================*/
500 function intervalsInPeriod(p_period_number in number
501 ,p_period_type1 in varchar2
502 ,p_period_type2 in varchar2) return number
503 is
504 l_ratio number;
505 l_period_type1 varchar2(30);
506 l_period_type2 varchar2(30);
507 l_num_intervals number;
508 l_api_name varchar2(30);
509
510 begin
511 l_num_intervals := 0;
512 l_api_name := 'intervalsInPeriod';
513 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
514
515 l_period_type1 := formatTerm(p_timeString => p_period_type1);
516 l_period_type2 := formatTerm(p_timeString => p_period_type2);
517 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' ' || p_period_number);
518 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' ' || l_period_type1);
519 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' ' || l_period_type2);
520
521 if l_period_type1 = l_period_type2 then
522 l_ratio := 1;
523
524 -- how many xxx in a month
525 elsif l_period_type1 = 'MONTHS' then
526
527 if l_period_type2 = 'DAYS' then
528 l_ratio := 30;
529 elsif l_period_type2 = 'WEEKS' then
530 l_ratio := 4;
531 elsif l_period_type2 = 'BIWEEKS' then
532 l_ratio := 2;
533 elsif l_period_type2 = 'SEMI-MONTHS' then
534 l_ratio := 2;
535 elsif l_period_type2 = 'BI-MONTHS' then
536 l_ratio := 1 / 2;
537 elsif l_period_type2 = 'QUARTERS' then
538 l_ratio := 1 / 3;
539 elsif l_period_type2 = 'SEMI-ANNUALS' then
540 l_ratio := 1 / 6;
541 elsif l_period_type2 = 'YEARS' then
542 l_ratio := 1 / 12;
543 end if;
544
545 -- how many xxx in a quarter
546 elsif l_period_type1 = 'QUARTERS' then
547
548 if l_period_type2 = 'DAYS' then
549 l_ratio := 91;
550 elsif l_period_type2 = 'WEEKS' then
551 l_ratio := 13;
552 elsif l_period_type2 = 'BIWEEKS' then
553 l_ratio := 6;
554 elsif l_period_type2 = 'SEMI-MONTHS' then
555 l_ratio := 6;
556 elsif l_period_type2 = 'MONTHS' then
557 l_ratio := 3;
558 elsif l_period_type2 = 'BI-MONTHS' then
559 l_ratio := 3 / 2;
560 elsif l_period_type2 = 'SEMI-ANNUALS' then
561 l_ratio := 1 / 2;
562 elsif l_period_type2 = 'YEARS' then
563 l_ratio := 1 / 4;
564 end if;
565
566 -- how many xxx in a year
567 elsif l_period_type1 = 'YEARS' then
568
569 if l_period_type2 = 'DAYS' then
570 l_ratio := 365;
571 elsif l_period_type2 = 'WEEKS' then
572 l_ratio := 52;
573 elsif l_period_type2 = 'BIWEEKS' then
574 l_ratio := 26;
575 elsif l_period_type2 = 'SEMI-MONTHS' then
576 l_ratio := 24;
577 elsif l_period_type2 = 'MONTHS' then
578 l_ratio := 12;
579 elsif l_period_type2 = 'BI-MONTHS' then
580 l_ratio := 6;
581 elsif l_period_type2 = 'QUARTERS' then
582 l_ratio := 4;
583 elsif l_period_type2 = 'SEMI-ANNUALS' then
584 l_ratio := 2;
585 elsif l_period_type2 = 'YEARS' then
586 l_ratio := 1;
587 end if;
588
589 -- how many xxx in a week
590 elsif l_period_type1 = 'WEEKS' then
591
592 if l_period_type2 = 'DAYS' then
593 l_ratio := 7;
594 elsif l_period_type2 = 'SEMI-MONTHS' then
595 l_ratio := 1 / 2;
596 elsif l_period_type2 = 'BIWEEKS' then
597 l_ratio := 1 / 2;
598 elsif l_period_type2 = 'MONTHS' then
599 l_ratio := 1 / 4;
600 elsif l_period_type2 = 'BI-MONTHS' then
601 l_ratio := 1 / 8;
602 elsif l_period_type2 = 'QUARTERS' then
603 l_ratio := 1 / 13;
604 elsif l_period_type2 = 'SEMI-ANNUALS' then
605 l_ratio := 1 / 26;
606 elsif l_period_type2 = 'YEARS' then
607 l_ratio := 1 / 52;
608 end if;
609
610 -- how many xxx in a semi-month --15 days
611 elsif l_period_type1 = 'SEMI-MONTHS' then
612
613 if l_period_type2 = 'DAYS' then
614 l_ratio := 15;
615 elsif l_period_type2 = 'WEEKS' then
616 l_ratio := 2;
617 elsif l_period_type2 = 'BIWEEKS' then
618 l_ratio := 2;
619 elsif l_period_type2 = 'MONTHS' then
620 l_ratio := 1 / 2;
621 elsif l_period_type2 = 'BI-MONTHS' then
622 l_ratio := 1 / 4;
623 elsif l_period_type2 = 'QUARTERS' then
624 l_ratio := 1 / 6;
625 elsif p_period_type2 = 'SEMI-ANNUALS' then
626 l_ratio := 1 / 12;
627 elsif l_period_type2 = 'YEARS' then
628 l_ratio := 1 / 24;
629 end if;
630
631 -- how many xxx in a bi-week --14 days
632 elsif l_period_type1 = 'BI-WEEKS' then
633
634 if l_period_type2 = 'DAYS' then
635 l_ratio := 14;
636 elsif l_period_type2 = 'WEEKS' then
637 l_ratio := 2;
638 elsif l_period_type2 = 'MONTHS' then
639 l_ratio := 1 / 2;
640 elsif l_period_type2 = 'QUARTERS' then
641 l_ratio := 1 / 6;
642 elsif l_period_type2 = 'YEARS' then
643 l_ratio := 1 / 26;
644 end if;
645
646 -- how many xxx in a semi-annual --
647 elsif l_period_type1 = 'SEMI-ANNUALS' then
648
649 if l_period_type2 = 'DAYS' then
650 l_ratio := 182;
651 elsif l_period_type2 = 'WEEKS' then
652 l_ratio := 26;
653 elsif l_period_type2 = 'BIWEEKS' then
654 l_ratio := 13;
655 elsif p_period_type2 = 'SEMI-MONTHS' then
656 l_ratio := 13;
657 elsif l_period_type2 = 'BI-MONTHS' then
658 l_ratio := 3;
659 elsif l_period_type2 = 'MONTHS' then
660 l_ratio := 6;
661 elsif l_period_type2 = 'QUARTERS' then
662 l_ratio := 2;
663 elsif l_period_type2 = 'YEARS' then
664 l_ratio := 1 / 2;
665 end if;
666
667 -- how many xxx in a bi-month
668 elsif l_period_type1 = 'BI-MONTHS' then -- 60 days
669
670
671 if l_period_type2 = 'DAYS' then
672 l_ratio := 60;
673 elsif l_period_type2 = 'WEEKS' then
674 l_ratio := 8;
675 elsif p_period_type2 = 'SEMI-MONTHS' then
676 l_ratio := 4;
677 elsif l_period_type2 = 'MONTHS' then
678 l_ratio := 2;
679 elsif l_period_type2 = 'QUARTERS' then
680 l_ratio := 2 / 3;
681 elsif l_period_type2 = 'YEARS' then
682 l_ratio := 1 / 6;
683 end if;
684
685 -- how many xxx for a day
686 elsif l_period_type1 = 'DAYS' then
687
688 if l_period_type2 = 'WEEKS' then
689 l_ratio := 1 / 7;
690 elsif l_period_type2 = 'SEMI-MONTHS' then
691 l_ratio := 1 / (365/24);
692 elsif l_period_type2 = 'BIWEEKS' then
693 l_ratio := 1 / 14;
694 elsif l_period_type2 = 'MONTHS' then
695 l_ratio := 1 / (365/12);
696 elsif l_period_type2 = 'BI-MONTHS' then
697 l_ratio := 1 / (365/6);
698 elsif l_period_type2 = 'QUARTERS' then
699 l_ratio := 1 / (365/4);
700 elsif l_period_type2 = 'SEMI-ANNUALS' then
701 l_ratio := 1 / (365/2);
702 elsif l_period_type2 = 'YEARS' then
703 l_ratio := 1 / 365;
704 end if;
705
706 end if;
707
708 l_num_intervals := p_period_number * l_ratio;
709 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': num intervals is: ' || l_num_intervals);
710 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
711
712 if (l_num_intervals > 0 and l_num_intervals < 1) then
713 l_num_intervals := 1;
714 end if;
715
716
717 IF l_period_type1 = 'DAYS' THEN
718 return ceil(l_num_intervals);
719 ELSE
720 return round(l_num_intervals);
721 END IF;
722
723 end intervalsInPeriod;
724
725 /*========================================================================
726 || PUBLIC FUNCTION convertPeriod
727 ||
728 || DESCRIPTION
729 || function that will return the number of X in a term period in
730 || terms of MONTHS
731 ||
732 || e.g. MONTHS in 4 YEARS = 48
733 || MONTHS in 7 QUARTERS = 21
734 ||
735 || PSEUDO CODE/LOGIC
736 ||
737 || PARAMETERS
738 || p_term
739 | p_term_period
740 | p_convert_to_term_period
741 || Return value:
742 ||
743 || Source Tables: NA
744 ||
745 || Target Tables: NA
746 ||
747 || KNOWN ISSUES
748 ||
749 || NOTES
750 ||
751 ||
752 || MODIFICATION HISTORY
753 || Date Author Description of Changes
754 || 12/12/05 6:42:PM raverma Created
755 || 29-Jan-2008 mbolli Bug#6634845 - added to work for 'DAYS' term also
756 =======================================================================*/
757 function convertPeriod(p_term in number
758 ,p_term_period in varchar2) return number
759
760 is
761
762 l_return number;
763 l_api_name varchar2(25);
764
765 begin
766
767 l_api_name := 'convertPeriod';
768
769 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_term ' || p_term);
770 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_term_period ' || p_term_period);
771 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - calling IntInPer ');
772
773 if p_term_period = 'DAYS' then
774 l_return := intervalsInPeriod(p_period_number => p_term
775 ,p_period_type1 => p_term_period
776 ,p_period_type2 => 'DAYS');
777 else
778 l_return := intervalsInPeriod(p_period_number => p_term
779 ,p_period_type1 => p_term_period
780 ,p_period_type2 => 'MONTHS');
781 end if;
782
783
784
785 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_return ' || l_return);
786
787 return l_return;
788
789 end convertPeriod;
790
791 /*========================================================================
792 | PUBLIC FUNCTION convertRate
793 |
794 | DESCRIPTION
795 | enter description
796 |
797 | PSEUDO CODE/LOGIC
798 |
799 | PARAMETERS
800 |
801 | Return value:
802 |
803 | Source Tables: NA
804 |
805 | Target Tables: NA
806 |
807 | KNOWN ISSUES
808 |
809 | NOTES
810 |
811 |
812 | MODIFICATION HISTORY
813 | Date Author Description of Changes
814 | 11/10/04 6:42:PM raverma Created
815 |
816 *=======================================================================*/
817 function convertRate(p_annualized_rate in number
818 ,p_amortization_frequency in varchar2) return number
819 is
820 l_converted_rate number;
821 l_amortization_frequency varchar2(30);
822 l_api_name varchar2(30);
823
824
825 begin
826 --l_converted_rate := 0;
827 l_api_name := 'convertRate';
828 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
829
830 l_amortization_frequency := formatTerm(p_timeString => p_amortization_frequency);
831 --l_period_type2 := formatTerm(p_timeString => p_period_type2);
832 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_amortization_frequency ' || l_amortization_frequency);
833 --logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
834
835 if l_amortization_frequency = 'MONTHS' then
836 l_converted_rate := p_annualized_rate / 1200;
837 elsif l_amortization_frequency = 'BIWEEKS' then
838 l_converted_rate := p_annualized_rate / 2600;
839 elsif l_amortization_frequency = 'SEMI-MONTHS' then
840 l_converted_rate := p_annualized_rate / 2400;
841 elsif l_amortization_frequency = 'BI-MONTHS' then
842 l_converted_rate := p_annualized_rate / 600;
843 elsif l_amortization_frequency = 'QUARTERS' then
844 l_converted_rate := p_annualized_rate / 400;
845 elsif l_amortization_frequency = 'SEMI-ANNUALS' then
846 l_converted_rate := p_annualized_rate / 200;
847 elsif l_amortization_frequency = 'YEARS' then
848 l_converted_rate := p_annualized_rate / 100;
849 elsif l_amortization_frequency = 'WEEKS' then
850 l_converted_rate := p_annualized_rate / 5200;
851 end if;
852
853 return l_converted_rate;
854
855 end convertRate;
856
857 /*=========================================================================
858 || PUBLIC PROCEDURE getMaturityDate
859 ||
860 || DESCRIPTION
861 ||
862 || Overview: returns last installment date for a loan
863 ||
864 || Parameter: p_amortized_term = amortized term (30)
865 || p_amortized_term_period = period ('YEARS')
866 || p_amortization_frequency ('MONTHlY')
867 || p_start_date = LOAN_START_DATE
868 || p_pay_in_arrears = NOT USED
869 ||
870 || Return value: maturity date of loan
871 ||
872 || Source Tables: NA
873 ||
874 || Target Tables: NA
875 ||
876 ||
877 | MODIFICATION HISTORY
878 | Date Author Description of Changes
879 | 03/1/2004 6:22PM raverma Created
880 || 03/22/2004 raverma add one interval to get accurate maturity date
881 || 03/25/2004 raverma we will need to change spec - TERM And TERM_PERIOD are appropriate
882 || 06/30/2004 raverma not changing spec...BUT PASS TERM/TERM PERIOD
883 || 08/02/2004 raverma this function is also used by getLoanDetails to get the
884 || theoretically amortization_maturity_date to get the num_amortization_intervals
885 || 08/19/2004 raverma we actually dont use/need payInArrears parameter -> karthik using this so dont remove
886 || 28-Jan-2008 mbolli Bug#6634845 - Added to work this module for term 'DAYS' also.
887 *=======================================================================*/
888 function getMaturityDate(p_term in number
889 ,p_term_period in varchar2
890 ,p_frequency in varchar2
891 ,p_start_date in date) return date
892 is
893 --l_pay_dates lns_fin_utils.DATE_TBL;
894 l_intervals NUMBER;
895 l_date date;
896 l_next_date date;
897 l_api_name varchar2(30);
898 i number;
899 l_term number;
900 l_term_period varchar2(30);
901 l_frequency varchar2(30);
902
903 begin
904
905 l_api_name := 'getMaturityDate';
906 i := 1;
907 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
908 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - term ' || p_term);
909 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - term period ' || p_term_period);
910 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - frequency ' || p_frequency);
911 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - start date ' || to_char(p_start_date,'DD-MON-YYYY HH:MI:SS'));
912
913 l_term := p_term;
914 l_term_period := p_term_period;
915 l_frequency := p_frequency;
916
917 if l_term is null or l_term_period is null or l_frequency is null
918 or p_start_date is null then
919 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - CANNOT COMPUTE MATURITY DATE');
920 FND_MESSAGE.Set_Name('LNS', 'LNS_MATURITY_DATE_INVALID');
921 FND_MSG_PUB.Add;
922 RAISE FND_API.G_EXC_ERROR;
923 end if;
924
925 -- Bug#6634845
926
927 IF (l_term_period = 'DAYS') THEN
928
929 l_date := p_start_date + l_term;
930 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' Days term calculation in Maturity Date ');
931
932 ELSE
933
934 -- first get number of intervals in loan
935 l_intervals := lns_fin_utils.intervalsInPeriod(l_term
936 ,l_term_period
937 ,'MONTHLY');
938
939 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - intervals ' || l_intervals);
940
941 -- l_date := p_start_date;
942 l_date := add_months(p_start_date, l_intervals);
943
944 END IF;
945
946 -- Bug#6634845 - Commented out this.
947 /*
948 WHILE i <= l_intervals LOOP
949 -- bug 5842639; scherkas 1/16/2007: changed calculation method for payment dates
950 --l_next_date := lns_fin_utils.getNextDate(l_date, p_frequency, 1);
951 l_next_date := lns_fin_utils.getNextDate(p_start_date, p_frequency, i); -- new way
952 l_date := l_next_date;
953 i := i + 1;
954 END LOOP;
955 */
956
957 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - date returns is ' || l_date);
958 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - maturity date ' || to_char(l_date,'DD-MON-YYYY HH:MI:SS'));
959 return trunc(l_date);
960
961 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
962
963 end getMaturityDate;
964
965 /*=========================================================================
966 || PUBLIC PROCEDURE getPaymentSchedule
967 ||
968 || DESCRIPTION
969 ||
970 || Overview: return a table of dates that payments are due for a loan
971 ||
972 ||
973 || Parameter: first_payment_date date
974 || number of intervals
975 || interval type (weeks, months, quarters, years)
976 ||
977 || Return value: table of dates
978 ||
979 || Source Tables: NA
980 ||
981 || Target Tables: NA
982 ||
983 || KNOWN ISSUES
984 ||
985 || NOTES
986 ||
987 || MODIFICATION HISTORY
988 || Date Author Description of Changes
989 || 12/10/2003 4:28PM raverma Created
990 || 7/28/2004 raverma change this to handle maturityDate
991 || 4/05/2005 raverma should result in unique pay dates
992 || 01/19/2007 scherkas Fixed bug 5842639: added p_loan_start_date parameter
993 *=======================================================================*/
994 function getPaymentSchedule(p_loan_start_date in date
995 ,p_first_pay_date in date
996 ,p_maturity_date in date
997 ,p_pay_in_arrears in boolean
998 ,p_num_intervals in number
999 ,p_interval_type in varchar2) return lns_fin_utils.DATE_TBL
1000 is
1001 l_payment_dates lns_fin_utils.DATE_TBL;
1002 i number;
1003 l_date date;
1004 l_next_date date;
1005 l_multiplier number;
1006 l_api_name varchar2(25);
1007 l_skip boolean;
1008 l_default_first_pay_date date;
1009 l_start_date date;
1010 l_intervals number;
1011
1012 begin
1013
1014 l_api_name := 'getPaymentSchedule';
1015 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1016 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_first_pay_date ' || p_first_pay_date);
1017 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_maturity_date ' || p_maturity_date);
1018 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_num_intervals ' || p_num_intervals);
1019 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_interval_type ' || p_interval_type);
1020 if p_pay_in_arrears then
1021 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_pay_in_arrears TRUE');
1022 else
1023 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_pay_in_arrears FALSE');
1024 end if;
1025
1026 l_skip := false;
1027 l_default_first_pay_date := lns_fin_utils.getNextDate(p_loan_start_date, p_interval_type, 1);
1028 if l_default_first_pay_date = p_first_pay_date then
1029
1030 i := 0;
1031 l_start_date := p_loan_start_date;
1032 l_intervals := p_num_intervals;
1033
1034 if p_first_pay_date = p_maturity_date then
1035 l_payment_dates(i+1) := p_first_pay_date;
1036 l_skip := true;
1037 end if;
1038
1039 else
1040
1041 i := 1;
1042 l_start_date := p_first_pay_date;
1043 l_intervals := p_num_intervals - 1;
1044 l_payment_dates(i) := p_first_pay_date;
1045
1046 if p_first_pay_date = p_maturity_date then
1047 l_skip := true;
1048 end if;
1049
1050 end if;
1051
1052 --l_next_date := p_first_pay_date;
1053
1054 if l_skip = false then
1055 -- if p_first_pay_date <> p_maturity_date then
1056 -- the first pay date is already established at this point
1057 -- this loop builds the table of subsequent dates that payments will be due
1058 -- since first pay date can be anywhere on the loan we build schedule
1059 -- until we pass the maturity date
1060 -- for paying in advance we will go thru the entire schedule
1061 for k in 1..l_intervals loop
1062
1063 -- bug 5842639; scherkas 1/16/2007: changed calculation method for payment dates
1064 --l_next_date := lns_fin_utils.getNextDate(l_payment_dates(i), p_interval_type, 1); -- old way
1065 l_next_date := lns_fin_utils.getNextDate(l_start_date, p_interval_type, k); -- new way
1066
1067 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' ' || i || ' - next_date ' || l_next_date);
1068 -- we should never get beyond the maturity date
1069
1070 -- scherkas; fixed bug 6111460: fixes problem with generating several installments on the last installment date if first payment date is later then default first payment date
1071 if (p_pay_in_arrears and trunc(l_next_date) > trunc(p_maturity_date)) or
1072 (trunc(l_next_date) = trunc(p_maturity_date)) then
1073 -- for pay in arrears make sure there is final payment on maturity date
1074 l_payment_dates(i+1) := p_maturity_date;
1075 exit;
1076 end if;
1077 l_payment_dates(i+1) := l_next_date;
1078 i := i + 1;
1079 end loop;
1080 end if;
1081
1082 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1083
1084 return l_payment_dates;
1085
1086 end getPaymentSchedule;
1087
1088 /*=========================================================================
1089 | PUBLIC FUNCTION getInstallmentDate
1090 |
1091 | DESCRIPTION
1092 | gets an installment date for a given loan
1093 |
1094 | PSEUDO CODE/LOGIC
1095 || based off of the loan_start_date and the payment_frequency
1096 || we will build dates
1097 |
1098 | PARAMETERS p_loan_id = loan ID
1099 | p_installment --> installment number to get
1100 ||
1101 | Return value: date installment is due
1102 |
1103 | Source Tables: NA
1104 |
1105 | Target Tables: NA
1106 |
1107 | KNOWN ISSUES
1108 || we are passing negative installments for memo fees staring before
1109 || loan start date
1110 |
1111 | NOTES
1112 |
1113 |
1114 | MODIFICATION HISTORY
1115 | Date Author Description of Changes
1116 | 03/31/2004 3:56PM raverma Created
1117 |
1118 *=======================================================================*/
1119 function getInstallmentDate(p_loan_id IN NUMBER
1120 ,p_installment_number IN NUMBER) return date
1121 is
1122 CURSOR c_payment_info(p_Loan_id NUMBER) IS
1123 SELECT
1124 t.loan_payment_frequency
1125 ,t.first_payment_date
1126 FROM lns_loan_headers_all h, lns_terms t
1127 WHERE h.loan_id = p_loan_id AND
1128 h.loan_id = t.loan_id;
1129
1130 l_payment_frequency varchar2(30);
1131 l_first_payment_date date;
1132 l_installment_date date;
1133
1134 begin
1135
1136 open c_payment_info(p_loan_id);
1137 fetch c_payment_info
1138 into l_payment_frequency
1139 ,l_first_payment_date;
1140 close c_payment_info;
1141
1142 l_installment_date := lns_fin_utils.getNextDate(p_date => l_first_payment_date
1143 ,p_interval_type => l_payment_frequency
1144 ,p_direction => p_installment_number);
1145 return l_installment_date;
1146
1147 end getInstallmentDate;
1148
1149 /*=========================================================================
1150 || PUBLIC PROCEDURE getNumberInstallments
1151 ||
1152 || DESCRIPTION
1153 ||
1154 || Overview: returns the number of installments for a loan
1155 ||
1156 || Parameter: p_loan_id => loan_id
1157 ||
1158 || Return value: last number of installments
1159 ||
1160 || Source Tables: LNS_LOAN_HEADER, LNS_TERMS
1161 ||
1162 || Target Tables: NA
1163 ||
1164 || MODIFICATION HISTORY
1165 || Date Author Description of Changes
1166 || 03/8/2004 6:22PM raverma Created
1167 || 03/26/2004 raverma make standalone call for performance
1168 *=======================================================================*/
1169 function getNumberInstallments(p_loan_id in number) return NUMBER
1170
1171 is
1172 l_api_name varchar2(25);
1173
1174 l_installments NUMBER;
1175 l_term number;
1176 l_term_period varchar2(30);
1177 l_amortized_term number;
1178 l_amortized_term_period varchar2(30);
1179 l_amortization_frequency varchar2(30);
1180 l_payment_frequency varchar2(30);
1181
1182 cursor c_details (p_loan_id NUMBER)
1183 is
1184 SELECT h.loan_term
1185 ,h.loan_term_period
1186 ,h.amortized_term
1187 ,h.amortized_term_period
1188 ,t.amortization_frequency
1189 ,t.loan_payment_frequency
1190 FROM lns_loan_headers_all h, lns_terms t
1191 WHERE h.loan_id = p_loan_id AND
1192 h.loan_id = t.loan_id;
1193
1194 begin
1195
1196 l_api_name := 'getNumberInstallments';
1197 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1198
1199 l_installments := 0;
1200
1201 OPEN c_details(p_loan_id);
1202 FETCH c_details INTO
1203 l_term
1204 ,l_term_period
1205 ,l_amortized_term
1206 ,l_amortized_term_period
1207 ,l_amortization_frequency
1208 ,l_payment_frequency;
1209 close c_details;
1210
1211 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_term: ' || l_term);
1212 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_term_period: ' || l_term_period);
1213 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_amortized_term: ' || l_amortized_term);
1214 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_amortized_term_period: ' || l_amortized_term_period);
1215 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_amortization_frequency: ' || l_amortization_frequency);
1216 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_payment_frequency: ' || l_payment_frequency);
1217
1218 l_installments := lns_fin_utils.intervalsInPeriod(l_term
1219 ,l_term_period
1220 ,l_payment_frequency);
1221
1222 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_installments: ' || l_installments);
1223 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1224
1225 return l_installments;
1226
1227 end getNumberInstallments;
1228
1229
1230 /*=========================================================================
1231 || PUBLIC PROCEDURE getNumberInstallments - R12
1232 ||
1233 || DESCRIPTION
1234 ||
1235 || Overview: returns the number of installments for a loan
1236 ||
1237 || Parameter: p_loan_id => loan_id
1238 || p_phase => phase of the loan
1239 ||
1240 || Return value: last number of installments
1241 ||
1242 || Source Tables: LNS_LOAN_HEADER, LNS_TERMS
1243 ||
1244 || Target Tables: NA
1245 ||
1246 || MODIFICATION HISTORY
1247 || Date Author Description of Changes
1248 || 07/17/2005 6:22PM raverma Created
1249 *=======================================================================*/
1250 function getNumberInstallments(p_loan_id in number
1251 ,p_phase in varchar2) return NUMBER
1252 is
1253 l_api_name varchar2(25);
1254
1255 l_installments NUMBER;
1256 l_term number;
1257 l_term_period varchar2(30);
1258 l_payment_frequency varchar2(30);
1259
1260 l_loan_start_date date;
1261 l_maturity_date date;
1262 l_first_payment_date date;
1263 l_intervals number;
1264 l_pay_in_arrears varchar2(1);
1265 l_pay_in_arrears_bool boolean;
1266 l_prin_first_pay_date date;
1267 l_prin_intervals number;
1268 l_prin_payment_frequency varchar2(30);
1269 l_prin_pay_in_arrears varchar2(1);
1270 l_prin_pay_in_arrears_bool boolean;
1271 l_pay_calc_method varchar2(30);
1272
1273 l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
1274 l_custom_schedule varchar2(1);
1275
1276 cursor c_details (p_loan_id NUMBER, p_phase varchar2)
1277 is
1278 SELECT decode(p_phase, 'OPEN', h.open_loan_term,
1279 'TERM', h.loan_term, h.loan_term)
1280 ,decode(p_phase, 'OPEN', h.open_loan_term_period,
1281 'TERM', h.loan_term_period, h.loan_term_period)
1282 ,decode(p_phase, 'OPEN', t.open_payment_frequency,
1283 'TERM', t.loan_payment_frequency, t.loan_payment_frequency)
1284 ,trunc(h.loan_start_date)
1285 ,trunc(t.first_payment_date)
1286 ,trunc(h.loan_maturity_date)
1287 ,decode(trunc(t.first_payment_date) - trunc(h.loan_start_date), 0, 'N', 'Y') -- calculate in advance or arrears
1288 ,nvl(t.PAYMENT_CALC_METHOD, 'EQUAL_PAYMENT')
1289 ,trunc(nvl(t.prin_first_pay_date, t.first_payment_date))
1290 ,nvl(t.prin_payment_frequency, t.loan_payment_frequency)
1291 ,decode(trunc(nvl(t.prin_first_pay_date, t.first_payment_date)) - trunc(h.loan_start_date), 0, 'N', 'Y')
1292 ,nvl(h.custom_payments_flag, 'N')
1293 FROM lns_loan_headers_all h, lns_terms t
1294 WHERE h.loan_id = p_loan_id AND
1295 h.loan_id = t.loan_id;
1296
1297 cursor c_num_cust_instal (p_loan_id NUMBER) is
1298 select max(PAYMENT_NUMBER)
1299 from LNS_CUSTOM_PAYMNT_SCHEDS
1300 where loan_id = p_loan_id;
1301
1302 begin
1303
1304 l_api_name := 'getNumberInstallments2';
1305 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1306 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_loan_id: ' || p_loan_id);
1307 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_phase: ' || p_phase);
1308
1309 l_installments := 0;
1310
1311 OPEN c_details(p_loan_id, p_phase);
1312 FETCH c_details INTO
1313 l_term
1314 ,l_term_period
1315 ,l_payment_frequency
1316 ,l_loan_start_date
1317 ,l_first_payment_date
1318 ,l_maturity_date
1319 ,l_pay_in_arrears
1320 ,l_pay_calc_method
1321 ,l_prin_first_pay_date
1322 ,l_prin_payment_frequency
1323 ,l_prin_pay_in_arrears
1324 ,l_custom_schedule;
1325 close c_details;
1326
1327 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_term: ' || l_term);
1328 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_term_period: ' || l_term_period);
1329 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_payment_frequency: ' || l_payment_frequency);
1330 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_start_date: ' || l_loan_start_date);
1331 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_first_payment_date: ' || l_first_payment_date);
1332 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_maturity_date: ' || l_maturity_date);
1333 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_pay_in_arrears: ' || l_pay_in_arrears);
1334 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_pay_calc_method: ' || l_pay_calc_method);
1335 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_prin_first_pay_date: ' || l_prin_first_pay_date);
1336 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_prin_payment_frequency: ' || l_prin_payment_frequency);
1337 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_prin_pay_in_arrears: ' || l_prin_pay_in_arrears);
1338 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_custom_schedule: ' || l_custom_schedule);
1339
1340 if p_phase = 'OPEN' then
1341 l_installments := lns_fin_utils.intervalsInPeriod(l_term
1342 ,l_term_period
1343 ,l_payment_frequency);
1344 else
1345
1346 if l_custom_schedule = 'N' then
1347
1348 if (l_pay_calc_method = 'SEPARATE_SCHEDULES') then
1349
1350 if l_pay_in_arrears = 'Y' then
1351 l_pay_in_arrears_bool := true;
1352 else
1353 l_pay_in_arrears_bool := false;
1354 end if;
1355
1356 if l_prin_pay_in_arrears = 'Y' then
1357 l_prin_pay_in_arrears_bool := true;
1358 else
1359 l_prin_pay_in_arrears_bool := false;
1360 end if;
1361
1362 l_intervals := lns_fin_utils.intervalsInPeriod(l_term
1363 ,l_term_period
1364 ,l_payment_frequency);
1365
1366 l_prin_intervals := lns_fin_utils.intervalsInPeriod(l_term
1367 ,l_term_period
1368 ,l_prin_payment_frequency);
1369
1370 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_intervals: ' || l_intervals);
1371 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_prin_intervals: ' || l_prin_intervals);
1372
1373 l_payment_tbl := LNS_FIN_UTILS.buildSIPPaymentSchedule(
1374 p_loan_start_date => l_loan_start_date
1375 ,p_loan_maturity_date => l_maturity_date
1376 ,p_int_first_pay_date => l_first_payment_date
1377 ,p_int_num_intervals => l_intervals
1378 ,p_int_interval_type => l_payment_frequency
1379 ,p_int_pay_in_arrears => l_pay_in_arrears_bool
1380 ,p_prin_first_pay_date => l_prin_first_pay_date
1381 ,p_prin_num_intervals => l_prin_intervals
1382 ,p_prin_interval_type => l_prin_payment_frequency
1383 ,p_prin_pay_in_arrears => l_prin_pay_in_arrears_bool);
1384
1385 l_installments := l_payment_tbl.count;
1386
1387 else
1388
1389 l_installments := lns_fin_utils.intervalsInPeriod(l_term
1390 ,l_term_period
1391 ,l_payment_frequency);
1392 end if;
1393
1394 else
1395
1396 -- Fixed bug 6133313
1397 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Getting number of installments from LNS_CUSTOM_PAYMNT_SCHEDS...');
1398 OPEN c_num_cust_instal(p_loan_id);
1399 FETCH c_num_cust_instal INTO l_installments;
1400 close c_num_cust_instal;
1401
1402 end if; --if l_custom_schedule = 'N'
1403
1404 end if; --if p_phase = 'OPEN'
1405
1406 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_installments: ' || l_installments);
1407 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1408
1409 return l_installments;
1410
1411 end getNumberInstallments;
1412
1413
1414 /*=========================================================================
1415 || PUBLIC PROCEDURE buildPaymentSchedule
1416 ||
1417 || DESCRIPTION
1418 ||
1419 || Overview: return a table of dates that represent installment
1420 || begin and end dates for a loan
1421 ||
1422 ||
1423 || Parameter: first_payment_date date
1424 || number of intervals
1425 || interval type (weeks, months, quarters, years)
1426 || pay_in_arrears TRUE if loan is paid in arrears, FALSE in advance
1427 ||
1428 || Return value: table of dates
1429 ||
1430 || Source Tables: LNS_LOAN_HEADER, LNS_TERMS
1431 ||
1432 || Target Tables: NA
1433 ||
1434 || MODIFICATION HISTORY
1435 || Date Author Description of Changes
1436 || 11/2/2004 4:28PM raverma Created
1437 *=======================================================================*/
1438 function buildPaymentSchedule(p_loan_start_date in date
1439 ,p_loan_maturity_date in date
1440 ,p_first_pay_date in date
1441 ,p_num_intervals in number
1442 ,p_interval_type in varchar2
1443 ,p_pay_in_arrears in boolean) return LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL
1444
1445 is
1446 l_pay_dates LNS_FIN_UTILS.DATE_TBL;
1447 l_payment_schedule LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
1448 l_num_installments number;
1449 l_multiplier number;
1450 l_api_name varchar2(25);
1451
1452 begin
1453
1454 l_api_name := 'buildPaymentSchedule';
1455 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1456 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - loan_start_date ' || p_loan_start_date);
1457 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - loan_maturity_date ' || p_loan_maturity_date);
1458 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - first_pay_date ' || p_first_pay_date);
1459 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - num_intervals ' || p_num_intervals);
1460 if p_pay_in_arrears then
1461 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_pay_in_arrears TRUE');
1462 end if;
1463
1464 -- fix for bug 5842639: added p_loan_start_date parameter to LNS_FIN_UTILS.getPaymentSchedule
1465 l_pay_dates := LNS_FIN_UTILS.getPaymentSchedule(p_loan_start_date => p_loan_start_date
1466 ,p_first_pay_date => p_first_pay_date
1467 ,p_maturity_Date => p_loan_maturity_date
1468 ,p_pay_in_arrears => p_pay_in_arrears
1469 ,p_num_intervals => p_num_intervals
1470 ,p_interval_type => p_interval_type);
1471
1472 -- we need to ensure maturity date is accurately calculated
1473 -- also begin / end period dates is very important to the calculation of interest due
1474 l_num_installments := l_pay_dates.count;
1475 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - number installments: ' || l_num_installments);
1476 for i in 1..l_num_installments
1477 loop
1478
1479 -- begin bug fix 6498340; scherkas; 10/12/2007;
1480 if i = 1 then
1481 l_payment_schedule(i).period_begin_date := p_loan_start_date;
1482 else
1483 l_payment_schedule(i).period_begin_date := l_pay_dates(i - 1);
1484 end if;
1485 l_payment_schedule(i).period_end_date := l_pay_dates(i);
1486 l_payment_schedule(i).period_due_date := l_payment_schedule(i).period_end_date;
1487 -- end bug fix 6498340; scherkas; 10/12/2007;
1488
1489 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || '- ' || i || ' period_start_date: ' || l_payment_schedule(i).period_begin_date);
1490 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || '- ' || i || ' period_end_date: ' || l_payment_schedule(i).period_end_date);
1491 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || '- ' || i || ' period_due_date: ' || l_payment_schedule(i).period_due_date);
1492
1493 end loop;
1494
1495 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1496
1497 return l_payment_schedule;
1498
1499 end buildPaymentSchedule;
1500
1501
1502 /*=========================================================================
1503 || PUBLIC PROCEDURE buildSIPPaymentSchedule
1504 ||
1505 || DESCRIPTION
1506 ||
1507 || Overview: return a table of dates that represent installment begin and
1508 || end dates for a seperate interest and principal (SIP) schedules loan
1509 ||
1510 ||
1511 || Parameters:
1512 ||
1513 || Return value: table of dates
1514 ||
1515 || Source Tables: LNS_LOAN_HEADER, LNS_TERMS
1516 ||
1517 || Target Tables: NA
1518 ||
1519 || KNOWN ISSUES
1520 ||
1521 || NOTES
1522 ||
1523 || MODIFICATION HISTORY
1524 || Date Author Description of Changes
1525 || 10/03/2007 scherkas Created: fix for bug 6498771
1526 *=======================================================================*/
1527 function buildSIPPaymentSchedule(p_loan_start_date in date
1528 ,p_loan_maturity_date in date
1529 ,p_int_first_pay_date in date
1530 ,p_int_num_intervals in number
1531 ,p_int_interval_type in varchar2
1532 ,p_int_pay_in_arrears in boolean
1533 ,p_prin_first_pay_date in date
1534 ,p_prin_num_intervals in number
1535 ,p_prin_interval_type in varchar2
1536 ,p_prin_pay_in_arrears in boolean) return LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL
1537 is
1538 l_api_name varchar2(25);
1539 l_merged_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
1540 l_int_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
1541 l_prin_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
1542 int_count number;
1543 prin_count number;
1544 merged_count number;
1545 l_int_due_date date;
1546 l_prin_due_date date;
1547 l_int_pay LNS_FIN_UTILS.PAYMENT_SCHEDULE;
1548 l_prin_pay LNS_FIN_UTILS.PAYMENT_SCHEDULE;
1549 l_size number;
1550 i number;
1551 j number;
1552
1553 TYPE number_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1554 l_delete_tbl number_tbl;
1555
1556 begin
1557
1558 l_api_name := 'buildSIPPaymentSchedule';
1559 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1560 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - loan_start_date ' || p_loan_start_date);
1561 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - loan_maturity_date ' || p_loan_maturity_date);
1562 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - INT first_pay_date ' || p_int_first_pay_date);
1563 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - INT num_intervals ' || p_int_num_intervals);
1564 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - INT interval_type ' || p_int_interval_type);
1565 if p_int_pay_in_arrears then
1566 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - INT pay_in_arrears TRUE');
1567 else
1568 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - INT pay_in_arrears FALSE');
1569 end if;
1570 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - PRIN first_pay_date ' || p_prin_first_pay_date);
1571 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - PRIN num_intervals ' || p_prin_num_intervals);
1572 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - PRIN interval_type ' || p_prin_interval_type);
1573 if p_prin_pay_in_arrears then
1574 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - PRIN pay_in_arrears TRUE');
1575 else
1576 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - PRIN pay_in_arrears FALSE');
1577 end if;
1578
1579 -- get interest payment schedule
1580 l_int_payment_tbl := LNS_FIN_UTILS.buildPaymentSchedule(
1581 p_loan_start_date => p_loan_start_date
1582 ,p_loan_maturity_date => p_loan_maturity_date
1583 ,p_first_pay_date => p_int_first_pay_date
1584 ,p_num_intervals => p_int_num_intervals
1585 ,p_interval_type => p_int_interval_type
1586 ,p_pay_in_arrears => p_int_pay_in_arrears);
1587
1588 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'INT payment schedule:');
1589 for j in 1..l_int_payment_tbl.count loop
1590 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, j || ': ' || l_int_payment_tbl(j).PERIOD_DUE_DATE);
1591 end loop;
1592
1593 -- get principal payment schedule
1594 l_prin_payment_tbl := LNS_FIN_UTILS.buildPaymentSchedule(
1595 p_loan_start_date => p_loan_start_date
1596 ,p_loan_maturity_date => p_loan_maturity_date
1597 ,p_first_pay_date => p_prin_first_pay_date
1598 ,p_num_intervals => p_prin_num_intervals
1599 ,p_interval_type => p_prin_interval_type
1600 ,p_pay_in_arrears => p_prin_pay_in_arrears);
1601
1602 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRIN payment schedule:');
1603 for j in 1..l_prin_payment_tbl.count loop
1604 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, j || ': ' || l_prin_payment_tbl(j).PERIOD_DUE_DATE);
1605 end loop;
1606
1607
1608 -- merging payment schedules
1609 int_count := 1;
1610 prin_count := 1;
1611 merged_count := 1;
1612 loop
1613 l_int_due_date := null;
1614 l_prin_due_date := null;
1615
1616 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, '-------');
1617 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'int_count = ' || int_count);
1618 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'prin_count = ' || prin_count);
1619
1620 if (int_count <= l_int_payment_tbl.count ) then
1621 l_int_pay := l_int_payment_tbl(int_count);
1622 l_int_pay.CONTENTS := 'INT';
1623 l_int_due_date := trunc(l_int_pay.PERIOD_DUE_DATE);
1624 end if;
1625 if (prin_count <= l_prin_payment_tbl.count ) then
1626 l_prin_pay := l_prin_payment_tbl(prin_count);
1627 l_prin_pay.CONTENTS := 'PRIN';
1628 l_prin_due_date := trunc(l_prin_pay.PERIOD_DUE_DATE);
1629 end if;
1630
1631 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_int_due_date = ' || l_int_due_date);
1632 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_prin_due_date = ' || l_prin_due_date);
1633
1634 if (l_int_due_date is not null and l_prin_due_date is not null) then
1635
1636 if (l_int_due_date < l_prin_due_date) then
1637 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'if 11');
1638 l_merged_payment_tbl(merged_count) := l_int_pay;
1639 int_count := int_count + 1;
1640 elsif (l_int_due_date > l_prin_due_date) then
1641 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'if 12');
1642 l_merged_payment_tbl(merged_count) := l_prin_pay;
1643 prin_count := prin_count + 1;
1644 elsif (l_int_due_date = l_prin_due_date) then
1645 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'if 12');
1646 l_merged_payment_tbl(merged_count) := l_prin_pay;
1647 l_merged_payment_tbl(merged_count).CONTENTS := 'PRIN_INT';
1648 int_count := int_count + 1;
1649 prin_count := prin_count + 1;
1650 end if;
1651
1652 elsif (l_int_due_date is null and l_prin_due_date is not null) then
1653
1654 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'if 2');
1655 l_merged_payment_tbl(merged_count) := l_prin_pay;
1656 prin_count := prin_count + 1;
1657
1658 elsif (l_int_due_date is not null and l_prin_due_date is null) then
1659
1660 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'if 3');
1661 l_merged_payment_tbl(merged_count) := l_int_pay;
1662 int_count := int_count + 1;
1663
1664 elsif (l_int_due_date is null and l_prin_due_date is null) then
1665
1666 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'if 4');
1667 exit;
1668
1669 end if;
1670
1671 if (merged_count > 1) then
1672 l_merged_payment_tbl(merged_count).PERIOD_BEGIN_DATE := l_merged_payment_tbl(merged_count-1).PERIOD_END_DATE;
1673 end if;
1674
1675 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'result due_date = ' || l_merged_payment_tbl(merged_count).PERIOD_DUE_DATE);
1676 merged_count := merged_count + 1;
1677
1678 end loop;
1679
1680 -- processing merged payment schedule
1681 l_size := l_merged_payment_tbl.count;
1682 if (l_merged_payment_tbl(l_size).CONTENTS = 'PRIN') then
1683 -- adjusting last installment if its PRIN:
1684 -- if last installment due date = maturity date then adding INT to this installment
1685 -- otherwise adding last INT installment on maturity date
1686
1687 if (l_merged_payment_tbl(l_size).PERIOD_END_DATE = p_loan_maturity_date) then
1688 l_merged_payment_tbl(l_size).CONTENTS := 'PRIN_INT';
1689 else
1690 l_merged_payment_tbl(l_size+1).PERIOD_BEGIN_DATE := l_merged_payment_tbl(l_size).PERIOD_END_DATE;
1691 l_merged_payment_tbl(l_size+1).PERIOD_DUE_DATE := p_loan_maturity_date;
1692 l_merged_payment_tbl(l_size+1).PERIOD_END_DATE := p_loan_maturity_date;
1693 l_merged_payment_tbl(l_size+1).CONTENTS := 'INT';
1694 end if;
1695 /*
1696 elsif (l_merged_payment_tbl(l_size).CONTENTS = 'INT') then
1697
1698 -- collecting all extra INT records and delete then from merged table
1699 j := 1;
1700 for i in reverse 1..(l_size-1) loop
1701 if (l_merged_payment_tbl(i).CONTENTS = 'INT') then
1702 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Deleting installment ' || (i+1) || ' - ' || l_merged_payment_tbl(i+1).CONTENTS);
1703 l_delete_tbl(j) := i+1;
1704 elsif (l_merged_payment_tbl(i).CONTENTS = 'PRIN') then
1705 exit;
1706 elsif (l_merged_payment_tbl(i).CONTENTS = 'PRIN_INT') then
1707 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Deleting installment ' || (i+1) || ' - ' || l_merged_payment_tbl(i+1).CONTENTS);
1708 l_delete_tbl(j) := i+1;
1709 exit;
1710 end if;
1711 j := j + 1;
1712 end loop;
1713
1714 for i in 1..l_delete_tbl.count loop
1715 l_merged_payment_tbl.delete(l_delete_tbl(i));
1716 end loop;
1717 */
1718 end if;
1719
1720 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, '-------');
1721 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Mixed payment schedule:');
1722 for j in 1..l_merged_payment_tbl.count loop
1723 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, j || ': ' ||l_merged_payment_tbl(j).CONTENTS || ' ' ||
1724 l_merged_payment_tbl(j).PERIOD_DUE_DATE || ' (from ' || l_merged_payment_tbl(j).PERIOD_BEGIN_DATE ||
1725 ' to ' || l_merged_payment_tbl(j).PERIOD_END_DATE || ')');
1726 end loop;
1727
1728 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1729
1730 return l_merged_payment_tbl;
1731
1732 end buildSIPPaymentSchedule;
1733
1734
1735 /*=========================================================================
1736 || PUBLIC PROCEDURE getActiveRate
1737 ||
1738 || DESCRIPTION
1739 ||
1740 || Overview: gets the current interest rate for the loan
1741 || we will look at the last installment billed (not reversed)
1742 || to get the rate on the loan
1743 || Parameter: loan_id
1744 ||
1745 || Return value: current annual rate for the loan
1746 ||
1747 || Source Tables: LNS_RATE_SCHEDULES, LNS_TERMS, LNS_LOAN_HEADERS_ALL
1748 ||
1749 || Target Tables: NA
1750 ||
1751 || MODIFICATION HISTORY
1752 || Date Author Description of Changes
1753 || 3/8/2004 4:28PM raverma Created
1754 ||
1755 *=======================================================================*/
1756 function getActiveRate(p_loan_id in number) return number
1757 is
1758 l_active_rate number;
1759 l_last_installment number;
1760
1761 cursor c_activeRate(p_loan_id number, p_last_installment number) is
1762 select current_interest_rate
1763 from lns_rate_schedules rs
1764 ,lns_terms t
1765 ,lns_loan_headers_All lnh
1766 where lnh.loan_id = p_loan_id
1767 and lnh.loan_id = t.loan_id
1768 and t.term_id = rs.term_id
1769 and rs.end_installment_number >= p_last_installment
1770 and rs.begin_installment_number <= p_last_installment
1771 and rs.end_date_active is null
1772 and rs.phase = lnh.current_phase;
1773
1774 begin
1775
1776 l_last_installment := 1;
1777 l_active_rate := -1;
1778
1779 l_last_installment := LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id);
1780
1781 if l_last_installment = 0
1782 then l_last_installment := 1;
1783 end if;
1784
1785 begin
1786 open c_activeRate(p_loan_id, l_last_installment);
1787 fetch c_activeRate into l_active_rate;
1788 close c_activeRate;
1789
1790 exception
1791 when others then
1792 FND_MESSAGE.Set_Name('LNS', 'LNS_CANNOT_DETERMINE_RATE');
1793 FND_MSG_PUB.Add;
1794 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'LNS_CANNOT_DETERMINE_RATE - END');
1795 --RAISE FND_API.G_EXC_ERROR;
1796 end;
1797 return l_active_rate;
1798
1799 end getActiveRate;
1800
1801 /*=========================================================================
1802 || PUBLIC PROCEDURE getRateForDate
1803 ||
1804 || DESCRIPTION
1805 ||
1806 || Overview: gets an interest rate for a given index and date
1807 || Parameter: p_index_rate_id => PK to lns_int_rate_headers
1808 || p_rate_date => date to capture rate
1809 ||
1810 || Return value: current annual rate for the index
1811 ||
1812 || Source Tables: lns_int_Rate_lines
1813 ||
1814 || Target Tables: NA
1815 ||
1816 || MODIFICATION HISTORY
1817 || Date Author Description of Changes
1818 || 11/8/2005 4:28PM raverma Created
1819 ||
1820 *=======================================================================*/
1821 function getRateForDate(p_index_rate_id in number
1822 ,p_rate_date in date) return number
1823
1824 is
1825 cursor c_rate_for_Date(p_index_rate_id number, p_rate_date date) is
1826 select interest_rate
1827 from lns_int_Rate_lines
1828 where interest_rate_id = p_index_rate_id
1829 and p_rate_date >= start_date_active
1830 and p_rate_date < end_date_active;
1831
1832 l_rate number;
1833
1834
1835 begin
1836
1837 open c_rate_for_Date(p_index_rate_id, p_rate_date);
1838 fetch c_rate_for_Date into l_rate;
1839 close c_rate_for_Date;
1840
1841 return l_rate;
1842
1843 --exception when no_data_found then
1844 -- FND_MESSAGE.SET_NAME('LNS', 'LNS_RATES_ERROR');
1845 -- FND_MSG_PUB.ADD;
1846 end;
1847
1848
1849 END LNS_FIN_UTILS;