[Home] [Help]
PACKAGE BODY: APPS.LNS_FIN_UTILS
Source
1 PACKAGE BODY LNS_FIN_UTILS AS
2 /* $Header: LNS_FIN_UTILS_B.pls 120.25.12020000.3 2013/02/11 14:40:03 scherkas 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 k number;
1004 l_date date;
1005 l_next_date date;
1006 l_multiplier number;
1007 l_api_name varchar2(25);
1008 l_skip boolean;
1009 l_default_first_pay_date date;
1010 l_start_date date;
1011 l_intervals number;
1012
1013 begin
1014
1015 l_api_name := 'getPaymentSchedule';
1016 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1017 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_first_pay_date ' || p_first_pay_date);
1018 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_maturity_date ' || p_maturity_date);
1019 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_interval_type ' || p_interval_type);
1020
1021 i := 1;
1022 l_payment_dates(i) := p_first_pay_date;
1023 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' ' || i || ' - ' || l_payment_dates(i));
1024
1025 if p_first_pay_date <> p_maturity_date then
1026
1027 k := 1;
1028 l_next_date := lns_fin_utils.getNextDate(p_first_pay_date, p_interval_type, k);
1029 while true loop
1030
1031 if trunc(l_next_date) > trunc(p_maturity_date) then
1032 exit;
1033 end if;
1034
1035 i := i + 1;
1036 l_payment_dates(i) := l_next_date;
1037 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' ' || i || ' - ' || l_payment_dates(i));
1038
1039 k := k + 1;
1040 l_next_date := lns_fin_utils.getNextDate(p_first_pay_date, p_interval_type, k);
1041
1042 end loop;
1043
1044 end if;
1045
1046 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1047
1048 return l_payment_dates;
1049
1050 end getPaymentSchedule;
1051
1052
1053 /*=========================================================================
1054 || PUBLIC PROCEDURE getPaymentSchedule
1055 ||
1056 || DESCRIPTION
1057 ||
1058 || Overview: return a table of dates that payments are due for a loan
1059 ||
1060 ||
1061 || Parameter: frequency schedule table
1062 || loan maturity date
1063 ||
1064 || Return value: table of dates
1065 ||
1066 || Source Tables: NA
1067 ||
1068 || Target Tables: NA
1069 ||
1070 || KNOWN ISSUES
1071 ||
1072 || NOTES
1073 ||
1074 || MODIFICATION HISTORY
1075 || Date Author Description of Changes
1076 || 09/05/2012 scherkas Created
1077 *=======================================================================*/
1078 function getPaymentSchedule(p_freq_schedule_tbl in LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL
1079 ,p_loan_maturity_date in date) return lns_fin_utils.DATE_TBL
1080 is
1081 l_api_name varchar2(25);
1082 l_payment_dates lns_fin_utils.DATE_TBL;
1083 i number;
1084 j number;
1085 k number;
1086 l_freq_count number;
1087 l_first_date date;
1088 l_current_freq varchar2(30);
1089 l_current_date date;
1090 l_next_freq varchar2(30);
1091 l_next_date date;
1092
1093 begin
1094
1095 l_api_name := 'getPaymentSchedule';
1096
1097 l_freq_count := p_freq_schedule_tbl.count;
1098 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - BEGIN');
1099 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_freq_schedule_tbl count = ' || l_freq_count);
1100 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_loan_maturity_date = ' || p_loan_maturity_date);
1101
1102 i := 1; -- frequency table counter
1103 l_current_freq := p_freq_schedule_tbl(i).FREQUENCY;
1104 l_first_date := p_freq_schedule_tbl(i).PERIOD_BEGIN_DATE;
1105 l_current_date := l_first_date;
1106 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Frequency schedule record ' || i);
1107 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'first date = ' || l_first_date);
1108 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'frequency = ' || l_current_freq);
1109
1110 j := 1; -- output table counter
1111 l_payment_dates(j) := l_first_date;
1112 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, j || ' - ' || l_payment_dates(j));
1113 k := 0; -- multiplyer for current frequency
1114
1115 if trunc(l_current_date) <> trunc(p_loan_maturity_date) then
1116
1117 while true loop
1118
1119 k := k + 1;
1120 l_current_date := lns_fin_utils.getNextDate(l_first_date, l_current_freq, k);
1121
1122 if i < l_freq_count then
1123 l_next_freq := p_freq_schedule_tbl(i+1).FREQUENCY;
1124 l_next_date := p_freq_schedule_tbl(i+1).PERIOD_BEGIN_DATE;
1125
1126 if trunc(l_current_date) >= trunc(l_next_date) then
1127 l_first_date := l_next_date;
1128 l_current_date := l_first_date;
1129 l_current_freq := l_next_freq;
1130 i := i + 1;
1131 k := 0; -- reset multiplyer for new frequency
1132
1133 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Frequency schedule record ' || i);
1134 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'first date = ' || l_first_date);
1135 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'frequency = ' || l_current_freq);
1136 end if;
1137 end if;
1138
1139 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_current_date = ' || l_current_date);
1140 if trunc(l_current_date) > trunc(p_loan_maturity_date) then
1141 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'current date > maturity date - exiting');
1142 exit;
1143 end if;
1144
1145 j := j + 1;
1146 l_payment_dates(j) := l_current_date;
1147 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, j || ' - ' || l_payment_dates(j));
1148
1149 end loop;
1150
1151 end if;
1152
1153 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1154
1155 return l_payment_dates;
1156
1157 end getPaymentSchedule;
1158
1159
1160 /*=========================================================================
1161 | PUBLIC FUNCTION getInstallmentDate
1162 |
1163 | DESCRIPTION
1164 | gets an installment date for a given loan
1165 |
1166 | PSEUDO CODE/LOGIC
1167 || based off of the loan_start_date and the payment_frequency
1168 || we will build dates
1169 |
1170 | PARAMETERS p_loan_id = loan ID
1171 | p_installment --> installment number to get
1172 ||
1173 | Return value: date installment is due
1174 |
1175 | Source Tables: NA
1176 |
1177 | Target Tables: NA
1178 |
1179 | KNOWN ISSUES
1180 || we are passing negative installments for memo fees staring before
1181 || loan start date
1182 |
1183 | NOTES
1184 |
1185 |
1186 | MODIFICATION HISTORY
1187 | Date Author Description of Changes
1188 | 03/31/2004 3:56PM raverma Created
1189 |
1190 *=======================================================================*/
1191 /*
1192 function getInstallmentDate(p_loan_id IN NUMBER
1193 ,p_installment_number IN NUMBER) return date
1194 is
1195 CURSOR c_payment_info(p_Loan_id NUMBER) IS
1196 SELECT
1197 t.loan_payment_frequency
1198 ,t.first_payment_date
1199 FROM lns_loan_headers_all h, lns_terms t
1200 WHERE h.loan_id = p_loan_id AND
1201 h.loan_id = t.loan_id;
1202
1203 l_payment_frequency varchar2(30);
1204 l_first_payment_date date;
1205 l_installment_date date;
1206
1207 begin
1208
1209 open c_payment_info(p_loan_id);
1210 fetch c_payment_info
1211 into l_payment_frequency
1212 ,l_first_payment_date;
1213 close c_payment_info;
1214
1215 l_installment_date := lns_fin_utils.getNextDate(p_date => l_first_payment_date
1216 ,p_interval_type => l_payment_frequency
1217 ,p_direction => p_installment_number);
1218 return l_installment_date;
1219
1220 end getInstallmentDate;
1221 */
1222
1223 /*=========================================================================
1224 || PUBLIC PROCEDURE getNumberInstallments
1225 ||
1226 || DESCRIPTION
1227 ||
1228 || Overview: returns the number of installments for a loan
1229 ||
1230 || Parameter: p_loan_id => loan_id
1231 ||
1232 || Return value: last number of installments
1233 ||
1234 || Source Tables: LNS_LOAN_HEADER, LNS_TERMS
1235 ||
1236 || Target Tables: NA
1237 ||
1238 || MODIFICATION HISTORY
1239 || Date Author Description of Changes
1240 || 03/8/2004 6:22PM raverma Created
1241 || 03/26/2004 raverma make standalone call for performance
1242 *=======================================================================*/
1243 /*
1244 function getNumberInstallments(p_loan_id in number) return NUMBER
1245
1246 is
1247 l_api_name varchar2(25);
1248
1249 l_installments NUMBER;
1250 l_term number;
1251 l_term_period varchar2(30);
1252 l_amortized_term number;
1253 l_amortized_term_period varchar2(30);
1254 l_amortization_frequency varchar2(30);
1255 l_payment_frequency varchar2(30);
1256
1257 cursor c_details (p_loan_id NUMBER)
1258 is
1259 SELECT h.loan_term
1260 ,h.loan_term_period
1261 ,h.amortized_term
1262 ,h.amortized_term_period
1263 ,t.amortization_frequency
1264 ,t.loan_payment_frequency
1265 FROM lns_loan_headers_all h, lns_terms t
1266 WHERE h.loan_id = p_loan_id AND
1267 h.loan_id = t.loan_id;
1268
1269 begin
1270
1271 l_api_name := 'getNumberInstallments';
1272 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1273
1274 l_installments := 0;
1275
1276 OPEN c_details(p_loan_id);
1277 FETCH c_details INTO
1278 l_term
1279 ,l_term_period
1280 ,l_amortized_term
1281 ,l_amortized_term_period
1282 ,l_amortization_frequency
1283 ,l_payment_frequency;
1284 close c_details;
1285
1286 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_term: ' || l_term);
1287 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_term_period: ' || l_term_period);
1288 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_amortized_term: ' || l_amortized_term);
1289 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_amortized_term_period: ' || l_amortized_term_period);
1290 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_amortization_frequency: ' || l_amortization_frequency);
1291 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_payment_frequency: ' || l_payment_frequency);
1292
1293 l_installments := lns_fin_utils.intervalsInPeriod(l_term
1294 ,l_term_period
1295 ,l_payment_frequency);
1296
1297 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_installments: ' || l_installments);
1298 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1299
1300 return l_installments;
1301
1302 end getNumberInstallments;
1303 */
1304 function getNumberInstallments(p_loan_id in number) return NUMBER
1305
1306 is
1307 l_api_name varchar2(25);
1308 l_installments NUMBER;
1309 l_phase varchar2(30);
1310
1311 cursor c_phase (p_loan_id NUMBER) is
1312 select nvl(CURRENT_PHASE, 'TERM')
1313 from lns_loan_headers_all
1314 where loan_id = p_loan_id;
1315
1316 begin
1317
1318 l_api_name := 'getNumberInstallments';
1319 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1320
1321 l_installments := 0;
1322
1323 OPEN c_phase(p_loan_id);
1324 FETCH c_phase INTO l_phase;
1325 close c_phase;
1326
1327 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_phase: ' || l_phase);
1328
1329 l_installments := lns_fin_utils.getNumberInstallments(p_loan_id, l_phase);
1330
1331 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_installments: ' || l_installments);
1332 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1333
1334 return l_installments;
1335
1336 end getNumberInstallments;
1337
1338 /*=========================================================================
1339 || PUBLIC PROCEDURE getNumberInstallments - R12
1340 ||
1341 || DESCRIPTION
1342 ||
1343 || Overview: returns the number of installments for a loan
1344 ||
1345 || Parameter: p_loan_id => loan_id
1346 || p_phase => phase of the loan
1347 ||
1348 || Return value: last number of installments
1349 ||
1350 || Source Tables: LNS_LOAN_HEADER, LNS_TERMS
1351 ||
1352 || Target Tables: NA
1353 ||
1354 || MODIFICATION HISTORY
1355 || Date Author Description of Changes
1356 || 07/17/2005 6:22PM raverma Created
1357 *=======================================================================*/
1358 /*
1359 function getNumberInstallments(p_loan_id in number
1360 ,p_phase in varchar2) return NUMBER
1361 is
1362 l_api_name varchar2(25);
1363
1364 l_installments NUMBER;
1365 l_term number;
1366 l_term_period varchar2(30);
1367 l_payment_frequency varchar2(30);
1368
1369 l_loan_start_date date;
1370 l_maturity_date date;
1371 l_first_payment_date date;
1372 l_intervals number;
1373 l_pay_in_arrears varchar2(1);
1374 l_pay_in_arrears_bool boolean;
1375 l_prin_first_pay_date date;
1376 l_prin_intervals number;
1377 l_prin_payment_frequency varchar2(30);
1378 l_prin_pay_in_arrears varchar2(1);
1379 l_prin_pay_in_arrears_bool boolean;
1380 l_pay_calc_method varchar2(30);
1381
1382 l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
1383 l_custom_schedule varchar2(1);
1384
1385 cursor c_details (p_loan_id NUMBER, p_phase varchar2)
1386 is
1387 SELECT decode(p_phase, 'OPEN', h.open_loan_term,
1388 'TERM', h.loan_term, h.loan_term)
1389 ,decode(p_phase, 'OPEN', h.open_loan_term_period,
1390 'TERM', h.loan_term_period, h.loan_term_period)
1391 ,decode(p_phase, 'OPEN', t.open_payment_frequency,
1392 'TERM', t.loan_payment_frequency, t.loan_payment_frequency)
1393 ,decode(p_phase, 'TERM', trunc(h.loan_start_date),
1394 'OPEN' , trunc(h.open_loan_start_date), trunc(h.loan_start_date))
1395 ,decode(p_phase, 'TERM', trunc(t.first_payment_date),
1396 'OPEN' , trunc(t.open_first_payment_date), trunc(t.first_payment_date))
1397 ,decode(p_phase, 'TERM', trunc(h.loan_maturity_date),
1398 'OPEN', trunc(h.open_maturity_date), trunc(h.loan_maturity_date))
1399 ,decode(p_phase, 'TERM', decode(trunc(t.first_payment_date) - trunc(h.loan_start_date), 0, 'N', 'Y')
1400 , 'OPEN', decode(trunc(t.open_first_payment_date) - trunc(h.open_loan_start_date), 0, 'N', 'Y')
1401 , decode(trunc(t.first_payment_date) - trunc(h.loan_start_date), 0, 'N', 'Y'))
1402 ,nvl(t.PAYMENT_CALC_METHOD, 'EQUAL_PAYMENT')
1403 ,trunc(nvl(t.prin_first_pay_date, t.first_payment_date))
1404 ,nvl(t.prin_payment_frequency, t.loan_payment_frequency)
1405 ,decode(trunc(nvl(t.prin_first_pay_date, t.first_payment_date)) - trunc(h.loan_start_date), 0, 'N', 'Y')
1406 ,nvl(h.custom_payments_flag, 'N')
1407 FROM lns_loan_headers_all h, lns_terms t
1408 WHERE h.loan_id = p_loan_id AND
1409 h.loan_id = t.loan_id;
1410
1411 cursor c_num_cust_instal (p_loan_id NUMBER) is
1412 select nvl(max(PAYMENT_NUMBER), 1)
1413 from LNS_CUSTOM_PAYMNT_SCHEDS
1414 where loan_id = p_loan_id;
1415
1416 begin
1417
1418 l_api_name := 'getNumberInstallments2';
1419 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1420 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_loan_id: ' || p_loan_id);
1421 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_phase: ' || p_phase);
1422
1423 l_installments := 0;
1424
1425 OPEN c_details(p_loan_id, p_phase);
1426 FETCH c_details INTO
1427 l_term
1428 ,l_term_period
1429 ,l_payment_frequency
1430 ,l_loan_start_date
1431 ,l_first_payment_date
1432 ,l_maturity_date
1433 ,l_pay_in_arrears
1434 ,l_pay_calc_method
1435 ,l_prin_first_pay_date
1436 ,l_prin_payment_frequency
1437 ,l_prin_pay_in_arrears
1438 ,l_custom_schedule;
1439 close c_details;
1440
1441 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_term: ' || l_term);
1442 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_term_period: ' || l_term_period);
1443 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_payment_frequency: ' || l_payment_frequency);
1444 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_start_date: ' || l_loan_start_date);
1445 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_first_payment_date: ' || l_first_payment_date);
1446 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_maturity_date: ' || l_maturity_date);
1447 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_pay_in_arrears: ' || l_pay_in_arrears);
1448 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_pay_calc_method: ' || l_pay_calc_method);
1449 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_prin_first_pay_date: ' || l_prin_first_pay_date);
1450 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_prin_payment_frequency: ' || l_prin_payment_frequency);
1451 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_prin_pay_in_arrears: ' || l_prin_pay_in_arrears);
1452 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_custom_schedule: ' || l_custom_schedule);
1453
1454 if p_phase = 'OPEN' then
1455 if l_pay_in_arrears = 'Y' then
1456 l_pay_in_arrears_bool := true;
1457 else
1458 l_pay_in_arrears_bool := false;
1459 end if;
1460
1461 l_payment_tbl := LNS_FIN_UTILS.buildPaymentSchedule(
1462 p_loan_start_date => l_loan_start_date
1463 ,p_loan_maturity_date => l_maturity_date
1464 ,p_first_pay_date => l_first_payment_date
1465 ,p_num_intervals => null
1466 ,p_interval_type => l_payment_frequency
1467 ,p_pay_in_arrears => l_pay_in_arrears_bool);
1468
1469 l_installments := l_payment_tbl.count;
1470
1471 else
1472
1473 if l_custom_schedule = 'N' then
1474
1475 if (l_pay_calc_method = 'SEPARATE_SCHEDULES') then
1476
1477 if l_pay_in_arrears = 'Y' then
1478 l_pay_in_arrears_bool := true;
1479 else
1480 l_pay_in_arrears_bool := false;
1481 end if;
1482
1483 if l_prin_pay_in_arrears = 'Y' then
1484 l_prin_pay_in_arrears_bool := true;
1485 else
1486 l_prin_pay_in_arrears_bool := false;
1487 end if;
1488
1489 l_payment_tbl := LNS_FIN_UTILS.buildSIPPaymentSchedule(
1490 p_loan_start_date => l_loan_start_date
1491 ,p_loan_maturity_date => l_maturity_date
1492 ,p_int_first_pay_date => l_first_payment_date
1493 ,p_int_num_intervals => l_intervals
1494 ,p_int_interval_type => l_payment_frequency
1495 ,p_int_pay_in_arrears => l_pay_in_arrears_bool
1496 ,p_prin_first_pay_date => l_prin_first_pay_date
1497 ,p_prin_num_intervals => l_prin_intervals
1498 ,p_prin_interval_type => l_prin_payment_frequency
1499 ,p_prin_pay_in_arrears => l_prin_pay_in_arrears_bool);
1500
1501 l_installments := l_payment_tbl.count;
1502
1503 else
1504
1505 if l_pay_in_arrears = 'Y' then
1506 l_pay_in_arrears_bool := true;
1507 else
1508 l_pay_in_arrears_bool := false;
1509 end if;
1510
1511 l_payment_tbl := LNS_FIN_UTILS.buildPaymentSchedule(
1512 p_loan_start_date => l_loan_start_date
1513 ,p_loan_maturity_date => l_maturity_date
1514 ,p_first_pay_date => l_first_payment_date
1515 ,p_num_intervals => null
1516 ,p_interval_type => l_payment_frequency
1517 ,p_pay_in_arrears => l_pay_in_arrears_bool);
1518
1519 l_installments := l_payment_tbl.count;
1520
1521 end if;
1522
1523 else
1524
1525 -- Fixed bug 6133313
1526 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Getting number of installments from LNS_CUSTOM_PAYMNT_SCHEDS...');
1527 OPEN c_num_cust_instal(p_loan_id);
1528 FETCH c_num_cust_instal INTO l_installments;
1529 close c_num_cust_instal;
1530
1531 end if; --if l_custom_schedule = 'N'
1532
1533 end if; --if p_phase = 'OPEN'
1534
1535 -- fix for bug 8309391: set number of installments = last billed istall number or 1 if no rows in amort sched
1536 if l_installments = 0 then
1537 l_installments := LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id);
1538 if l_installments = 0 then
1539 l_installments := 1;
1540 end if;
1541 end if;
1542
1543 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_installments: ' || l_installments);
1544 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1545
1546 return l_installments;
1547
1548 end getNumberInstallments;
1549 */
1550
1551 function getNumberInstallments(p_loan_id in number
1552 ,p_phase in varchar2) return NUMBER
1553 is
1554 l_api_name varchar2(25);
1555 l_installments NUMBER;
1556 l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
1557
1558 begin
1559
1560 l_api_name := 'getNumberInstallments2';
1561 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1562 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_loan_id: ' || p_loan_id);
1563 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_phase: ' || p_phase);
1564
1565 l_installments := 0;
1566 l_payment_tbl := LNS_FIN_UTILS.buildPaymentScheduleExt(p_loan_id, p_phase);
1567 l_installments := l_payment_tbl.count;
1568
1569 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_installments: ' || l_installments);
1570 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1571
1572 return l_installments;
1573
1574 end getNumberInstallments;
1575
1576
1577 /*=========================================================================
1578 || PUBLIC PROCEDURE buildPaymentSchedule
1579 ||
1580 || DESCRIPTION
1581 ||
1582 || Overview: return a table of dates that represent installment
1583 || begin and end dates for a loan
1584 ||
1585 ||
1586 || Parameter: first_payment_date date
1587 || number of intervals
1588 || interval type (weeks, months, quarters, years)
1589 || pay_in_arrears TRUE if loan is paid in arrears, FALSE in advance
1590 ||
1591 || Return value: table of dates
1592 ||
1593 || Source Tables: LNS_LOAN_HEADER, LNS_TERMS
1594 ||
1595 || Target Tables: NA
1596 ||
1597 || MODIFICATION HISTORY
1598 || Date Author Description of Changes
1599 || 11/2/2004 4:28PM raverma Created
1600 *=======================================================================*/
1601 function buildPaymentSchedule(p_loan_start_date in date
1602 ,p_loan_maturity_date in date
1603 ,p_first_pay_date in date
1604 ,p_num_intervals in number
1605 ,p_interval_type in varchar2
1606 ,p_pay_in_arrears in boolean) return LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL
1607
1608 is
1609 l_pay_dates LNS_FIN_UTILS.DATE_TBL;
1610 l_payment_schedule LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
1611 l_num_installments number;
1612 l_multiplier number;
1613 l_api_name varchar2(25);
1614
1615 begin
1616
1617 l_api_name := 'buildPaymentSchedule';
1618 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1619 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - loan_start_date ' || p_loan_start_date);
1620 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - loan_maturity_date ' || p_loan_maturity_date);
1621 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - first_pay_date ' || p_first_pay_date);
1622 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - num_intervals ' || p_num_intervals);
1623 if p_pay_in_arrears then
1624 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_pay_in_arrears TRUE');
1625 end if;
1626
1627 -- fix for bug 5842639: added p_loan_start_date parameter to LNS_FIN_UTILS.getPaymentSchedule
1628 l_pay_dates := LNS_FIN_UTILS.getPaymentSchedule(p_loan_start_date => p_loan_start_date
1629 ,p_first_pay_date => p_first_pay_date
1630 ,p_maturity_Date => p_loan_maturity_date
1631 ,p_pay_in_arrears => p_pay_in_arrears
1632 ,p_num_intervals => p_num_intervals
1633 ,p_interval_type => p_interval_type);
1634
1635 -- we need to ensure maturity date is accurately calculated
1636 -- also begin / end period dates is very important to the calculation of interest due
1637 l_num_installments := l_pay_dates.count;
1638 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - number installments: ' || l_num_installments);
1639 for i in 1..l_num_installments
1640 loop
1641
1642 -- begin bug fix 6498340; scherkas; 10/12/2007;
1643 if i = 1 then
1644 l_payment_schedule(i).period_begin_date := p_loan_start_date;
1645 else
1646 l_payment_schedule(i).period_begin_date := l_pay_dates(i - 1);
1647 end if;
1648 l_payment_schedule(i).period_end_date := l_pay_dates(i);
1649 l_payment_schedule(i).period_due_date := l_payment_schedule(i).period_end_date;
1650 -- end bug fix 6498340; scherkas; 10/12/2007;
1651
1652 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || '- ' || i || ': ' || l_payment_schedule(i).period_due_date || ' (from ' || l_payment_schedule(i).period_begin_date || ' to ' || l_payment_schedule(i).period_end_date || ')');
1653
1654 -- logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || '- ' || i || ' period_start_date: ' || l_payment_schedule(i).period_begin_date);
1655 -- logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || '- ' || i || ' period_end_date: ' || l_payment_schedule(i).period_end_date);
1656 -- logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || '- ' || i || ' period_due_date: ' || l_payment_schedule(i).period_due_date);
1657
1658 end loop;
1659
1660 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1661
1662 return l_payment_schedule;
1663
1664 end buildPaymentSchedule;
1665
1666
1667 function buildPaymentSchedule(p_loan_start_date in date
1668 ,p_loan_maturity_date in date
1669 ,p_freq_schedule_tbl in LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL) return LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL
1670
1671 is
1672 l_pay_dates LNS_FIN_UTILS.DATE_TBL;
1673 l_payment_schedule LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
1674 l_num_installments number;
1675 l_api_name varchar2(25);
1676
1677 begin
1678
1679 l_api_name := 'buildPaymentSchedule';
1680 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1681 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_loan_start_date = ' || p_loan_start_date);
1682 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_loan_maturity_date = ' || p_loan_maturity_date);
1683 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_freq_schedule_tbl count = ' || p_freq_schedule_tbl.count);
1684
1685 l_pay_dates := LNS_FIN_UTILS.getPaymentSchedule(p_freq_schedule_tbl => p_freq_schedule_tbl
1686 ,p_loan_maturity_date => p_loan_maturity_date);
1687
1688 l_num_installments := l_pay_dates.count;
1689 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - number installments: ' || l_num_installments);
1690 for i in 1..l_num_installments loop
1691
1692 if i = 1 then
1693 l_payment_schedule(i).period_begin_date := p_loan_start_date;
1694 else
1695 l_payment_schedule(i).period_begin_date := l_pay_dates(i - 1);
1696 end if;
1697 l_payment_schedule(i).period_end_date := l_pay_dates(i);
1698 l_payment_schedule(i).period_due_date := l_payment_schedule(i).period_end_date;
1699 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || '- ' || i || ': ' || l_payment_schedule(i).period_due_date || ' (from ' || l_payment_schedule(i).period_begin_date || ' to ' || l_payment_schedule(i).period_end_date || ')');
1700
1701 end loop;
1702
1703 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1704
1705 return l_payment_schedule;
1706
1707 end buildPaymentSchedule;
1708
1709
1710 /*=========================================================================
1711 || PUBLIC PROCEDURE buildSIPPaymentSchedule
1712 ||
1713 || DESCRIPTION
1714 ||
1715 || Overview: return a table of dates that represent installment begin and
1716 || end dates for a seperate interest and principal (SIP) schedules loan
1717 ||
1718 ||
1719 || Parameters:
1720 ||
1721 || Return value: table of dates
1722 ||
1723 || Source Tables: LNS_LOAN_HEADER, LNS_TERMS
1724 ||
1725 || Target Tables: NA
1726 ||
1727 || KNOWN ISSUES
1728 ||
1729 || NOTES
1730 ||
1731 || MODIFICATION HISTORY
1732 || Date Author Description of Changes
1733 || 10/03/2007 scherkas Created: fix for bug 6498771
1734 *=======================================================================*/
1735 function buildSIPPaymentSchedule(p_loan_start_date in date
1736 ,p_loan_maturity_date in date
1737 ,p_int_first_pay_date in date
1738 ,p_int_num_intervals in number
1739 ,p_int_interval_type in varchar2
1740 ,p_int_pay_in_arrears in boolean
1741 ,p_prin_first_pay_date in date
1742 ,p_prin_num_intervals in number
1743 ,p_prin_interval_type in varchar2
1744 ,p_prin_pay_in_arrears in boolean) return LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL
1745 is
1746 l_api_name varchar2(25);
1747 l_merged_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
1748 l_int_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
1749 l_prin_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
1750 int_count number;
1751 prin_count number;
1752 merged_count number;
1753 l_int_due_date date;
1754 l_prin_due_date date;
1755 l_int_pay LNS_FIN_UTILS.PAYMENT_SCHEDULE;
1756 l_prin_pay LNS_FIN_UTILS.PAYMENT_SCHEDULE;
1757 l_size number;
1758 i number;
1759 j number;
1760
1761 TYPE number_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1762 l_delete_tbl number_tbl;
1763
1764 begin
1765
1766 l_api_name := 'buildSIPPaymentSchedule';
1767 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1768 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - loan_start_date ' || p_loan_start_date);
1769 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - loan_maturity_date ' || p_loan_maturity_date);
1770 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - INT first_pay_date ' || p_int_first_pay_date);
1771 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - INT num_intervals ' || p_int_num_intervals);
1772 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - INT interval_type ' || p_int_interval_type);
1773 if p_int_pay_in_arrears then
1774 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - INT pay_in_arrears TRUE');
1775 else
1776 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - INT pay_in_arrears FALSE');
1777 end if;
1778 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - PRIN first_pay_date ' || p_prin_first_pay_date);
1779 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - PRIN num_intervals ' || p_prin_num_intervals);
1780 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - PRIN interval_type ' || p_prin_interval_type);
1781 if p_prin_pay_in_arrears then
1782 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - PRIN pay_in_arrears TRUE');
1783 else
1784 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - PRIN pay_in_arrears FALSE');
1785 end if;
1786
1787 -- get interest payment schedule
1788 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'INT payment schedule:');
1789 l_int_payment_tbl := LNS_FIN_UTILS.buildPaymentSchedule(
1790 p_loan_start_date => p_loan_start_date
1791 ,p_loan_maturity_date => p_loan_maturity_date
1792 ,p_first_pay_date => p_int_first_pay_date
1793 ,p_num_intervals => p_int_num_intervals
1794 ,p_interval_type => p_int_interval_type
1795 ,p_pay_in_arrears => p_int_pay_in_arrears);
1796 /*
1797 for j in 1..l_int_payment_tbl.count loop
1798 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, j || ': ' || l_int_payment_tbl(j).PERIOD_DUE_DATE);
1799 end loop;
1800 */
1801 -- get principal payment schedule
1802 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRIN payment schedule:');
1803 l_prin_payment_tbl := LNS_FIN_UTILS.buildPaymentSchedule(
1804 p_loan_start_date => p_loan_start_date
1805 ,p_loan_maturity_date => p_loan_maturity_date
1806 ,p_first_pay_date => p_prin_first_pay_date
1807 ,p_num_intervals => p_prin_num_intervals
1808 ,p_interval_type => p_prin_interval_type
1809 ,p_pay_in_arrears => p_prin_pay_in_arrears);
1810 /*
1811 for j in 1..l_prin_payment_tbl.count loop
1812 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, j || ': ' || l_prin_payment_tbl(j).PERIOD_DUE_DATE);
1813 end loop;
1814 */
1815
1816 -- merging payment schedules
1817 int_count := 1;
1818 prin_count := 1;
1819 merged_count := 1;
1820 loop
1821 l_int_due_date := null;
1822 l_prin_due_date := null;
1823
1824 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, '-------');
1825 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'int_count = ' || int_count);
1826 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'prin_count = ' || prin_count);
1827
1828 if (int_count <= l_int_payment_tbl.count ) then
1829 l_int_pay := l_int_payment_tbl(int_count);
1830 l_int_pay.CONTENTS := 'INT';
1831 l_int_due_date := trunc(l_int_pay.PERIOD_DUE_DATE);
1832 end if;
1833 if (prin_count <= l_prin_payment_tbl.count ) then
1834 l_prin_pay := l_prin_payment_tbl(prin_count);
1835 l_prin_pay.CONTENTS := 'PRIN';
1836 l_prin_due_date := trunc(l_prin_pay.PERIOD_DUE_DATE);
1837 end if;
1838
1839 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_int_due_date = ' || l_int_due_date);
1840 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_prin_due_date = ' || l_prin_due_date);
1841
1842 if (l_int_due_date is not null and l_prin_due_date is not null) then
1843
1844 if (l_int_due_date < l_prin_due_date) then
1845 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'if 11');
1846 l_merged_payment_tbl(merged_count) := l_int_pay;
1847 int_count := int_count + 1;
1848 elsif (l_int_due_date > l_prin_due_date) then
1849 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'if 12');
1850 l_merged_payment_tbl(merged_count) := l_prin_pay;
1851 prin_count := prin_count + 1;
1852 elsif (l_int_due_date = l_prin_due_date) then
1853 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'if 12');
1854 l_merged_payment_tbl(merged_count) := l_prin_pay;
1855 l_merged_payment_tbl(merged_count).CONTENTS := 'PRIN_INT';
1856 int_count := int_count + 1;
1857 prin_count := prin_count + 1;
1858 end if;
1859
1860 elsif (l_int_due_date is null and l_prin_due_date is not null) then
1861
1862 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'if 2');
1863 l_merged_payment_tbl(merged_count) := l_prin_pay;
1864 prin_count := prin_count + 1;
1865
1866 elsif (l_int_due_date is not null and l_prin_due_date is null) then
1867
1868 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'if 3');
1869 l_merged_payment_tbl(merged_count) := l_int_pay;
1870 int_count := int_count + 1;
1871
1872 elsif (l_int_due_date is null and l_prin_due_date is null) then
1873
1874 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'if 4');
1875 exit;
1876
1877 end if;
1878
1879 if (merged_count > 1) then
1880 l_merged_payment_tbl(merged_count).PERIOD_BEGIN_DATE := l_merged_payment_tbl(merged_count-1).PERIOD_END_DATE;
1881 end if;
1882
1883 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'result due_date = ' || l_merged_payment_tbl(merged_count).PERIOD_DUE_DATE);
1884 merged_count := merged_count + 1;
1885
1886 end loop;
1887
1888 -- processing merged payment schedule
1889 l_size := l_merged_payment_tbl.count;
1890 if (l_merged_payment_tbl(l_size).CONTENTS = 'PRIN') then
1891 -- adjusting last installment if its PRIN:
1892 -- if last installment due date = maturity date then adding INT to this installment
1893 -- otherwise adding last INT installment on maturity date
1894
1895 if (l_merged_payment_tbl(l_size).PERIOD_END_DATE = p_loan_maturity_date) then
1896 l_merged_payment_tbl(l_size).CONTENTS := 'PRIN_INT';
1897 else
1898 l_merged_payment_tbl(l_size+1).PERIOD_BEGIN_DATE := l_merged_payment_tbl(l_size).PERIOD_END_DATE;
1899 l_merged_payment_tbl(l_size+1).PERIOD_DUE_DATE := p_loan_maturity_date;
1900 l_merged_payment_tbl(l_size+1).PERIOD_END_DATE := p_loan_maturity_date;
1901 l_merged_payment_tbl(l_size+1).CONTENTS := 'INT';
1902 end if;
1903 /*
1904 elsif (l_merged_payment_tbl(l_size).CONTENTS = 'INT') then
1905
1906 -- collecting all extra INT records and delete then from merged table
1907 j := 1;
1908 for i in reverse 1..(l_size-1) loop
1909 if (l_merged_payment_tbl(i).CONTENTS = 'INT') then
1910 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Deleting installment ' || (i+1) || ' - ' || l_merged_payment_tbl(i+1).CONTENTS);
1911 l_delete_tbl(j) := i+1;
1912 elsif (l_merged_payment_tbl(i).CONTENTS = 'PRIN') then
1913 exit;
1914 elsif (l_merged_payment_tbl(i).CONTENTS = 'PRIN_INT') then
1915 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Deleting installment ' || (i+1) || ' - ' || l_merged_payment_tbl(i+1).CONTENTS);
1916 l_delete_tbl(j) := i+1;
1917 exit;
1918 end if;
1919 j := j + 1;
1920 end loop;
1921
1922 for i in 1..l_delete_tbl.count loop
1923 l_merged_payment_tbl.delete(l_delete_tbl(i));
1924 end loop;
1925 */
1926 end if;
1927
1928 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, '-------');
1929 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Mixed payment schedule:');
1930 for j in 1..l_merged_payment_tbl.count loop
1931 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, j || ': ' ||l_merged_payment_tbl(j).CONTENTS || ' ' ||
1932 l_merged_payment_tbl(j).PERIOD_DUE_DATE || ' (from ' || l_merged_payment_tbl(j).PERIOD_BEGIN_DATE ||
1933 ' to ' || l_merged_payment_tbl(j).PERIOD_END_DATE || ')');
1934 end loop;
1935
1936 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1937
1938 return l_merged_payment_tbl;
1939
1940 end buildSIPPaymentSchedule;
1941
1942
1943
1944 function buildSIPPaymentSchedule(p_loan_start_date in date
1945 ,p_loan_maturity_date in date
1946 ,p_prin_freq_schedule_tbl in LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL
1947 ,p_int_freq_schedule_tbl in LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL) return LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL
1948 is
1949 l_api_name varchar2(25);
1950 l_merged_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
1951 l_int_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
1952 l_prin_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
1953 int_count number;
1954 prin_count number;
1955 merged_count number;
1956 l_int_due_date date;
1957 l_prin_due_date date;
1958 l_int_pay LNS_FIN_UTILS.PAYMENT_SCHEDULE;
1959 l_prin_pay LNS_FIN_UTILS.PAYMENT_SCHEDULE;
1960 l_size number;
1961 i number;
1962 j number;
1963
1964 TYPE number_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1965 l_delete_tbl number_tbl;
1966
1967 begin
1968
1969 l_api_name := 'buildSIPPaymentSchedule';
1970 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1971 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loan_start_date ' || p_loan_start_date);
1972 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loan_maturity_date ' || p_loan_maturity_date);
1973 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_prin_freq_schedule_tbl.count = ' || p_prin_freq_schedule_tbl.count);
1974 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_int_freq_schedule_tbl.count = ' || p_int_freq_schedule_tbl.count);
1975
1976 -- get interest payment schedule
1977 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'INT payment schedule:');
1978 l_int_payment_tbl := LNS_FIN_UTILS.buildPaymentSchedule(
1979 p_loan_start_date => p_loan_start_date
1980 ,p_loan_maturity_date => p_loan_maturity_date
1981 ,p_freq_schedule_tbl => p_int_freq_schedule_tbl);
1982
1983 -- get principal payment schedule
1984 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRIN payment schedule:');
1985 l_prin_payment_tbl := LNS_FIN_UTILS.buildPaymentSchedule(
1986 p_loan_start_date => p_loan_start_date
1987 ,p_loan_maturity_date => p_loan_maturity_date
1988 ,p_freq_schedule_tbl => p_prin_freq_schedule_tbl);
1989
1990 -- merging payment schedules
1991 int_count := 1;
1992 prin_count := 1;
1993 merged_count := 1;
1994 loop
1995 l_int_due_date := null;
1996 l_prin_due_date := null;
1997
1998 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, '-------');
1999 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'int_count = ' || int_count);
2000 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'prin_count = ' || prin_count);
2001
2002 if (int_count <= l_int_payment_tbl.count ) then
2003 l_int_pay := l_int_payment_tbl(int_count);
2004 l_int_pay.CONTENTS := 'INT';
2005 l_int_due_date := trunc(l_int_pay.PERIOD_DUE_DATE);
2006 end if;
2007 if (prin_count <= l_prin_payment_tbl.count ) then
2008 l_prin_pay := l_prin_payment_tbl(prin_count);
2009 l_prin_pay.CONTENTS := 'PRIN';
2010 l_prin_due_date := trunc(l_prin_pay.PERIOD_DUE_DATE);
2011 end if;
2012
2013 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_int_due_date = ' || l_int_due_date);
2014 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_prin_due_date = ' || l_prin_due_date);
2015
2016 if (l_int_due_date is not null and l_prin_due_date is not null) then
2017
2018 if (l_int_due_date < l_prin_due_date) then
2019 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'if 11');
2020 l_merged_payment_tbl(merged_count) := l_int_pay;
2021 int_count := int_count + 1;
2022 elsif (l_int_due_date > l_prin_due_date) then
2023 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'if 12');
2024 l_merged_payment_tbl(merged_count) := l_prin_pay;
2025 prin_count := prin_count + 1;
2026 elsif (l_int_due_date = l_prin_due_date) then
2027 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'if 12');
2028 l_merged_payment_tbl(merged_count) := l_prin_pay;
2029 l_merged_payment_tbl(merged_count).CONTENTS := 'PRIN_INT';
2030 int_count := int_count + 1;
2031 prin_count := prin_count + 1;
2032 end if;
2033
2034 elsif (l_int_due_date is null and l_prin_due_date is not null) then
2035
2036 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'if 2');
2037 l_merged_payment_tbl(merged_count) := l_prin_pay;
2038 prin_count := prin_count + 1;
2039
2040 elsif (l_int_due_date is not null and l_prin_due_date is null) then
2041
2042 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'if 3');
2043 l_merged_payment_tbl(merged_count) := l_int_pay;
2044 int_count := int_count + 1;
2045
2046 elsif (l_int_due_date is null and l_prin_due_date is null) then
2047
2048 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'if 4');
2049 exit;
2050
2051 end if;
2052
2053 if (merged_count > 1) then
2054 l_merged_payment_tbl(merged_count).PERIOD_BEGIN_DATE := l_merged_payment_tbl(merged_count-1).PERIOD_END_DATE;
2055 end if;
2056
2057 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'result due_date = ' || l_merged_payment_tbl(merged_count).PERIOD_DUE_DATE);
2058 merged_count := merged_count + 1;
2059
2060 end loop;
2061
2062 -- processing merged payment schedule
2063 l_size := l_merged_payment_tbl.count;
2064 if (l_merged_payment_tbl(l_size).CONTENTS = 'PRIN') then
2065 -- adjusting last installment if its PRIN:
2066 -- if last installment due date = maturity date then adding INT to this installment
2067 -- otherwise adding last INT installment on maturity date
2068
2069 if (l_merged_payment_tbl(l_size).PERIOD_END_DATE = p_loan_maturity_date) then
2070 l_merged_payment_tbl(l_size).CONTENTS := 'PRIN_INT';
2071 else
2072 l_merged_payment_tbl(l_size+1).PERIOD_BEGIN_DATE := l_merged_payment_tbl(l_size).PERIOD_END_DATE;
2073 l_merged_payment_tbl(l_size+1).PERIOD_DUE_DATE := p_loan_maturity_date;
2074 l_merged_payment_tbl(l_size+1).PERIOD_END_DATE := p_loan_maturity_date;
2075 l_merged_payment_tbl(l_size+1).CONTENTS := 'INT';
2076 end if;
2077 end if;
2078
2079 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, '-------');
2080 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Mixed payment schedule:');
2081 for j in 1..l_merged_payment_tbl.count loop
2082 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, j || ': ' ||l_merged_payment_tbl(j).CONTENTS || ' ' ||
2083 l_merged_payment_tbl(j).PERIOD_DUE_DATE || ' (from ' || l_merged_payment_tbl(j).PERIOD_BEGIN_DATE ||
2084 ' to ' || l_merged_payment_tbl(j).PERIOD_END_DATE || ')');
2085 end loop;
2086
2087 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
2088
2089 return l_merged_payment_tbl;
2090
2091 end buildSIPPaymentSchedule;
2092
2093
2094 /*=========================================================================
2095 || PUBLIC PROCEDURE getActiveRate
2096 ||
2097 || DESCRIPTION
2098 ||
2099 || Overview: gets the current interest rate for the loan
2100 || we will look at the last installment billed (not reversed)
2101 || to get the rate on the loan
2102 || Parameter: loan_id
2103 ||
2104 || Return value: current annual rate for the loan
2105 ||
2106 || Source Tables: LNS_RATE_SCHEDULES, LNS_TERMS, LNS_LOAN_HEADERS_ALL
2107 ||
2108 || Target Tables: NA
2109 ||
2110 || MODIFICATION HISTORY
2111 || Date Author Description of Changes
2112 || 3/8/2004 4:28PM raverma Created
2113 ||
2114 *=======================================================================*/
2115 function getActiveRate(p_loan_id in number) return number
2116 is
2117 l_active_rate number;
2118 l_last_installment number;
2119
2120 cursor c_activeRate(p_loan_id number, p_last_installment number) is
2121 select current_interest_rate
2122 from lns_rate_schedules rs
2123 ,lns_terms t
2124 ,lns_loan_headers_All lnh
2125 where lnh.loan_id = p_loan_id
2126 and lnh.loan_id = t.loan_id
2127 and t.term_id = rs.term_id
2128 and rs.end_installment_number >= p_last_installment
2129 and rs.begin_installment_number <= p_last_installment
2130 and rs.end_date_active is null
2131 and rs.phase = lnh.current_phase;
2132
2133 begin
2134
2135 l_last_installment := 1;
2136 l_active_rate := -1;
2137
2138 l_last_installment := LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id);
2139
2140 if l_last_installment = 0
2141 then l_last_installment := 1;
2142 end if;
2143
2144 begin
2145 open c_activeRate(p_loan_id, l_last_installment);
2146 fetch c_activeRate into l_active_rate;
2147 close c_activeRate;
2148
2149 exception
2150 when others then
2151 FND_MESSAGE.Set_Name('LNS', 'LNS_CANNOT_DETERMINE_RATE');
2152 FND_MSG_PUB.Add;
2153 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'LNS_CANNOT_DETERMINE_RATE - END');
2154 --RAISE FND_API.G_EXC_ERROR;
2155 end;
2156 return l_active_rate;
2157
2158 end getActiveRate;
2159
2160 /*=========================================================================
2161 || PUBLIC PROCEDURE getRateForDate
2162 ||
2163 || DESCRIPTION
2164 ||
2165 || Overview: gets an interest rate for a given index and date
2166 || Parameter: p_index_rate_id => PK to lns_int_rate_headers
2167 || p_rate_date => date to capture rate
2168 ||
2169 || Return value: current annual rate for the index
2170 ||
2171 || Source Tables: lns_int_Rate_lines
2172 ||
2173 || Target Tables: NA
2174 ||
2175 || MODIFICATION HISTORY
2176 || Date Author Description of Changes
2177 || 11/8/2005 4:28PM raverma Created
2178 ||
2179 *=======================================================================*/
2180 function getRateForDate(p_index_rate_id in number
2181 ,p_rate_date in date) return number
2182
2183 is
2184 cursor c_rate_for_Date(p_index_rate_id number, p_rate_date date) is
2185 select interest_rate
2186 from lns_int_Rate_lines
2187 where interest_rate_id = p_index_rate_id
2188 and p_rate_date >= start_date_active
2189 and p_rate_date < end_date_active;
2190
2191 l_rate number;
2192
2193
2194 begin
2195
2196 open c_rate_for_Date(p_index_rate_id, p_rate_date);
2197 fetch c_rate_for_Date into l_rate;
2198 close c_rate_for_Date;
2199
2200 return l_rate;
2201
2202 --exception when no_data_found then
2203 -- FND_MESSAGE.SET_NAME('LNS', 'LNS_RATES_ERROR');
2204 -- FND_MSG_PUB.ADD;
2205 end;
2206
2207
2208 /*=========================================================================
2209 || PUBLIC PROCEDURE getPaymentScheduleExt
2210 ||
2211 || DESCRIPTION
2212 ||
2213 || Overview: returns lns_fin_utils.DATE_TBL for a loan
2214 ||
2215 || Parameter: p_loan_id => loan_id
2216 || p_phase => phase of the loan
2217 ||
2218 || Return value: lns_fin_utils.DATE_TBL
2219 ||
2220 || MODIFICATION HISTORY
2221 || Date Author Description of Changes
2222 || 02/24/2009 6:22PM scherkas Created
2223 *=======================================================================*/
2224 /*
2225 function buildPaymentScheduleExt(p_loan_id in number
2226 ,p_phase in varchar2) return LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL
2227 is
2228 l_api_name varchar2(25);
2229
2230 l_installments NUMBER;
2231 l_term number;
2232 l_term_period varchar2(30);
2233 l_payment_frequency varchar2(30);
2234
2235 l_loan_start_date date;
2236 l_maturity_date date;
2237 l_first_payment_date date;
2238 l_intervals number;
2239 l_pay_in_arrears varchar2(1);
2240 l_pay_in_arrears_bool boolean;
2241 l_prin_first_pay_date date;
2242 l_prin_intervals number;
2243 l_prin_payment_frequency varchar2(30);
2244 l_prin_pay_in_arrears varchar2(1);
2245 l_prin_pay_in_arrears_bool boolean;
2246 l_pay_calc_method varchar2(30);
2247
2248 l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
2249 l_custom_schedule varchar2(1);
2250
2251 cursor c_details (p_loan_id NUMBER, p_phase varchar2)
2252 is
2253 SELECT decode(p_phase, 'OPEN', h.open_loan_term,
2254 'TERM', h.loan_term, h.loan_term)
2255 ,decode(p_phase, 'OPEN', h.open_loan_term_period,
2256 'TERM', h.loan_term_period, h.loan_term_period)
2257 ,decode(p_phase, 'OPEN', t.open_payment_frequency,
2258 'TERM', t.loan_payment_frequency, t.loan_payment_frequency)
2259 ,decode(p_phase, 'TERM', trunc(h.loan_start_date),
2260 'OPEN' , trunc(h.open_loan_start_date), trunc(h.loan_start_date))
2261 ,decode(p_phase, 'TERM', trunc(t.first_payment_date),
2262 'OPEN' , trunc(t.open_first_payment_date), trunc(t.first_payment_date))
2263 ,decode(p_phase, 'TERM', trunc(h.loan_maturity_date),
2264 'OPEN', trunc(h.open_maturity_date), trunc(h.loan_maturity_date))
2265 ,decode(p_phase, 'TERM', decode(trunc(t.first_payment_date) - trunc(h.loan_start_date), 0, 'N', 'Y')
2266 , 'OPEN', decode(trunc(t.open_first_payment_date) - trunc(h.open_loan_start_date), 0, 'N', 'Y')
2267 , decode(trunc(t.first_payment_date) - trunc(h.loan_start_date), 0, 'N', 'Y'))
2268 ,nvl(t.PAYMENT_CALC_METHOD, 'EQUAL_PAYMENT')
2269 ,trunc(nvl(t.prin_first_pay_date, t.first_payment_date))
2270 ,nvl(t.prin_payment_frequency, t.loan_payment_frequency)
2271 ,decode(trunc(nvl(t.prin_first_pay_date, t.first_payment_date)) - trunc(h.loan_start_date), 0, 'N', 'Y')
2272 ,nvl(h.custom_payments_flag, 'N')
2273 FROM lns_loan_headers_all h, lns_terms t
2274 WHERE h.loan_id = p_loan_id AND
2275 h.loan_id = t.loan_id;
2276
2277 begin
2278
2279 l_api_name := 'buildPaymentScheduleExt';
2280 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2281 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_loan_id: ' || p_loan_id);
2282 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_phase: ' || p_phase);
2283
2284 l_installments := 0;
2285
2286 OPEN c_details(p_loan_id, p_phase);
2287 FETCH c_details INTO
2288 l_term
2289 ,l_term_period
2290 ,l_payment_frequency
2291 ,l_loan_start_date
2292 ,l_first_payment_date
2293 ,l_maturity_date
2294 ,l_pay_in_arrears
2295 ,l_pay_calc_method
2296 ,l_prin_first_pay_date
2297 ,l_prin_payment_frequency
2298 ,l_prin_pay_in_arrears
2299 ,l_custom_schedule;
2300 close c_details;
2301
2302 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_term: ' || l_term);
2303 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_term_period: ' || l_term_period);
2304 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_payment_frequency: ' || l_payment_frequency);
2305 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_start_date: ' || l_loan_start_date);
2306 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_first_payment_date: ' || l_first_payment_date);
2307 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_maturity_date: ' || l_maturity_date);
2308 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_pay_in_arrears: ' || l_pay_in_arrears);
2309 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_pay_calc_method: ' || l_pay_calc_method);
2310 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_prin_first_pay_date: ' || l_prin_first_pay_date);
2311 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_prin_payment_frequency: ' || l_prin_payment_frequency);
2312 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_prin_pay_in_arrears: ' || l_prin_pay_in_arrears);
2313 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_custom_schedule: ' || l_custom_schedule);
2314
2315 if p_phase = 'OPEN' then
2316
2317 if l_pay_in_arrears = 'Y' then
2318 l_pay_in_arrears_bool := true;
2319 else
2320 l_pay_in_arrears_bool := false;
2321 end if;
2322
2323 l_payment_tbl := LNS_FIN_UTILS.buildPaymentSchedule(
2324 p_loan_start_date => l_loan_start_date
2325 ,p_loan_maturity_date => l_maturity_date
2326 ,p_first_pay_date => l_first_payment_date
2327 ,p_num_intervals => null
2328 ,p_interval_type => l_payment_frequency
2329 ,p_pay_in_arrears => l_pay_in_arrears_bool);
2330
2331 else
2332
2333 if l_custom_schedule = 'N' then
2334
2335 if (l_pay_calc_method = 'SEPARATE_SCHEDULES') then
2336
2337 if l_pay_in_arrears = 'Y' then
2338 l_pay_in_arrears_bool := true;
2339 else
2340 l_pay_in_arrears_bool := false;
2341 end if;
2342
2343 if l_prin_pay_in_arrears = 'Y' then
2344 l_prin_pay_in_arrears_bool := true;
2345 else
2346 l_prin_pay_in_arrears_bool := false;
2347 end if;
2348
2349 l_payment_tbl := LNS_FIN_UTILS.buildSIPPaymentSchedule(
2350 p_loan_start_date => l_loan_start_date
2351 ,p_loan_maturity_date => l_maturity_date
2352 ,p_int_first_pay_date => l_first_payment_date
2353 ,p_int_num_intervals => l_intervals
2354 ,p_int_interval_type => l_payment_frequency
2355 ,p_int_pay_in_arrears => l_pay_in_arrears_bool
2356 ,p_prin_first_pay_date => l_prin_first_pay_date
2357 ,p_prin_num_intervals => l_prin_intervals
2358 ,p_prin_interval_type => l_prin_payment_frequency
2359 ,p_prin_pay_in_arrears => l_prin_pay_in_arrears_bool);
2360
2361 else
2362
2363 if l_pay_in_arrears = 'Y' then
2364 l_pay_in_arrears_bool := true;
2365 else
2366 l_pay_in_arrears_bool := false;
2367 end if;
2368
2369 l_payment_tbl := LNS_FIN_UTILS.buildPaymentSchedule(
2370 p_loan_start_date => l_loan_start_date
2371 ,p_loan_maturity_date => l_maturity_date
2372 ,p_first_pay_date => l_first_payment_date
2373 ,p_num_intervals => null
2374 ,p_interval_type => l_payment_frequency
2375 ,p_pay_in_arrears => l_pay_in_arrears_bool);
2376
2377 end if;
2378
2379 else
2380
2381 l_payment_tbl := LNS_CUSTOM_PUB.buildCustomPaySchedule(p_loan_id);
2382
2383 end if; --if l_custom_schedule = 'N'
2384
2385 end if; --if p_phase = 'OPEN'
2386
2387 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
2388
2389 return l_payment_tbl;
2390
2391 end buildPaymentScheduleExt;
2392 */
2393
2394 function buildPaymentScheduleExt(p_loan_id in number
2395 ,p_phase in varchar2) return LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL
2396 is
2397 l_api_name varchar2(25);
2398 l_loan_start_date date;
2399 l_maturity_date date;
2400 l_pay_calc_method varchar2(30);
2401 l_custom_schedule varchar2(1);
2402
2403 l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
2404 l_freq_schedule_tbl LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL;
2405 l_int_freq_schedule_tbl LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL;
2406 l_prin_freq_schedule_tbl LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL;
2407
2408 cursor c_details (p_loan_id NUMBER, p_phase varchar2)
2409 is
2410 SELECT decode(p_phase, 'TERM', trunc(h.loan_start_date),
2411 'OPEN' , trunc(h.open_loan_start_date), trunc(h.loan_start_date))
2412 ,decode(p_phase, 'TERM', trunc(h.loan_maturity_date),
2413 'OPEN', trunc(h.open_maturity_date), trunc(h.loan_maturity_date))
2414 ,nvl(t.PAYMENT_CALC_METHOD, 'EQUAL_PAYMENT')
2415 ,nvl(h.custom_payments_flag, 'N')
2416 FROM lns_loan_headers_all h, lns_terms t
2417 WHERE h.loan_id = p_loan_id AND
2418 h.loan_id = t.loan_id;
2419
2420 cursor c_num_cust_instal (p_loan_id NUMBER) is
2421 select nvl(max(PAYMENT_NUMBER), 1)
2422 from LNS_CUSTOM_PAYMNT_SCHEDS
2423 where loan_id = p_loan_id;
2424
2425 begin
2426
2427 l_api_name := 'buildPaymentScheduleExt';
2428 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2429 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_loan_id: ' || p_loan_id);
2430 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_phase: ' || p_phase);
2431
2432 OPEN c_details(p_loan_id, p_phase);
2433 FETCH c_details INTO
2434 l_loan_start_date
2435 ,l_maturity_date
2436 ,l_pay_calc_method
2437 ,l_custom_schedule;
2438 close c_details;
2439
2440 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_start_date: ' || l_loan_start_date);
2441 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_maturity_date: ' || l_maturity_date);
2442 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_pay_calc_method: ' || l_pay_calc_method);
2443 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_custom_schedule: ' || l_custom_schedule);
2444
2445 if p_phase = 'OPEN' then
2446
2447 l_freq_schedule_tbl := LNS_FIN_UTILS.fetchFreqSchedule(
2448 P_LOAN_ID => p_loan_id,
2449 P_PHASE => 'OPEN',
2450 P_COMPONENT => 'PRIN_INT');
2451
2452 l_payment_tbl := LNS_FIN_UTILS.buildPaymentSchedule(
2453 p_loan_start_date => l_loan_start_date
2454 ,p_loan_maturity_date => l_maturity_date
2455 ,p_freq_schedule_tbl => l_freq_schedule_tbl);
2456
2457 else
2458
2459 if l_custom_schedule = 'N' then
2460
2461 if (l_pay_calc_method = 'SEPARATE_SCHEDULES') then
2462
2463 l_int_freq_schedule_tbl := LNS_FIN_UTILS.fetchFreqSchedule(
2464 P_LOAN_ID => p_loan_id,
2465 P_PHASE => 'TERM',
2466 P_COMPONENT => 'INT');
2467
2468 l_prin_freq_schedule_tbl := LNS_FIN_UTILS.fetchFreqSchedule(
2469 P_LOAN_ID => p_loan_id,
2470 P_PHASE => 'TERM',
2471 P_COMPONENT => 'PRIN');
2472
2473 l_payment_tbl := LNS_FIN_UTILS.buildSIPPaymentSchedule(
2474 p_loan_start_date => l_loan_start_date,
2475 p_loan_maturity_date => l_maturity_date,
2476 p_prin_freq_schedule_tbl => l_prin_freq_schedule_tbl,
2477 p_int_freq_schedule_tbl => l_int_freq_schedule_tbl);
2478
2479 else
2480
2481 l_freq_schedule_tbl := LNS_FIN_UTILS.fetchFreqSchedule(
2482 P_LOAN_ID => p_loan_id,
2483 P_PHASE => 'TERM',
2484 P_COMPONENT => 'PRIN_INT');
2485
2486 l_payment_tbl := LNS_FIN_UTILS.buildPaymentSchedule(
2487 p_loan_start_date => l_loan_start_date
2488 ,p_loan_maturity_date => l_maturity_date
2489 ,p_freq_schedule_tbl => l_freq_schedule_tbl);
2490
2491 end if;
2492
2493 else
2494
2495 l_payment_tbl := LNS_CUSTOM_PUB.buildCustomPaySchedule(p_loan_id);
2496
2497 end if; --if l_custom_schedule = 'N'
2498
2499 end if; --if p_phase = 'OPEN'
2500
2501 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
2502 return l_payment_tbl;
2503
2504 end buildPaymentScheduleExt;
2505
2506
2507 /*=========================================================================
2508 || PUBLIC FUNCTION getNextInstallmentAfterDate - R12
2509 ||
2510 || DESCRIPTION
2511 ||
2512 || Overview: returns the installmentNumber for the provided date for a loan
2513 ||
2514 || Parameter: p_loan_id => loan_id
2515 || p_date => date for which the installment exists
2516 || p_phase => phase of the loan
2517 ||
2518 || Return value: installment number
2519 ||
2520 || Source Tables: LNS_LOAN_HEADERS_ALL, LNS_TERMS
2521 ||
2522 || Target Tables: NA
2523 ||
2524 || MODIFICATION HISTORY
2525 || Date Author Description of Changes
2526 || 13-Jan-2010 mbolli Bug#9255294 - Created
2527 *=======================================================================*/
2528 function getNextInstallmentAfterDate(p_loan_id in number
2529 ,p_date in date
2530 ,p_phase in varchar2) return NUMBER
2531 is
2532 l_api_name varchar2(30);
2533 l_installment_no NUMBER;
2534 l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
2535
2536 begin
2537
2538 l_api_name := 'getNextInstallmentAfterDate';
2539 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2540 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_loan_id: ' || p_loan_id);
2541 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_phase: ' || p_phase);
2542
2543 l_installment_no := null;
2544 l_payment_tbl := LNS_FIN_UTILS.buildPaymentScheduleExt(p_loan_id, p_phase);
2545 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' l_payment_tbl.count = ' || l_payment_tbl.count);
2546
2547 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_date: ' || p_date);
2548 FOR k in 1..l_payment_tbl.count LOOP
2549
2550 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' Period ' || k ||
2551 ': ' || l_payment_tbl(k).PERIOD_BEGIN_DATE || ' - ' || (l_payment_tbl(k).PERIOD_DUE_DATE-1));
2552
2553 if p_date >= l_payment_tbl(k).PERIOD_BEGIN_DATE and p_date < l_payment_tbl(k).PERIOD_DUE_DATE then
2554 l_installment_no := k;
2555 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' Found match, exiting loop');
2556 exit;
2557 end if;
2558
2559 END LOOP;
2560
2561 IF l_installment_no IS NULL THEN
2562 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' Did not find match, assigning next installment to be billed');
2563 l_installment_no := LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id) + 1;
2564 END IF;
2565
2566 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' l_installment_no = ' || l_installment_no);
2567 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
2568
2569 return l_installment_no;
2570
2571 end getNextInstallmentAfterDate;
2572
2573
2574 /*=========================================================================
2575 || PUBLIC PROCEDURE getNextInstForDisbursement - R12
2576 ||
2577 || DESCRIPTION
2578 ||
2579 || Overview: returns the installmentNumber for the provided disbursement
2580 ||
2581 || Parameter: p_loan_id => disb_header_id
2582 ||
2583 || Return value: installment number
2584 ||
2585 || Source Tables: LNS_DISB_HEADERS
2586 ||
2587 || Target Tables: NA
2588 ||
2589 || MODIFICATION HISTORY
2590 || Date Author Description of Changes
2591 || 05-Feb-2010 mbolli Bug#9255294 - Created
2592 *=======================================================================*/
2593 function getNextInstForDisbursement(p_disb_hdr_id in number) return NUMBER
2594 is
2595 l_api_name VARCHAR2(30);
2596 l_installment_no NUMBER;
2597 l_loan_id NUMBER;
2598 l_pay_req_date DATE;
2599 l_disbursement_date DATE;
2600 l_phase VARCHAR2(30);
2601 l_sourceDate DATE;
2602
2603 cursor c_disb_details (c_disb_hdr_id NUMBER)
2604 is
2605 SELECT dh.loan_id
2606 ,payment_request_date
2607 ,(select max(disbursement_date) from lns_disb_lines
2608 where disb_header_id = c_disb_hdr_id) disbursement_date
2609 ,dh.phase
2610 FROM lns_disb_headers dh, lns_loan_headers_all lhdr
2611 WHERE dh.disb_header_id = c_disb_hdr_id
2612 AND lhdr.loan_id = dh.loan_id;
2613
2614 begin
2615
2616 l_api_name := 'getNextInstForDisbursement';
2617 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2618 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_disb_hdr_id: ' || p_disb_hdr_id);
2619
2620 l_installment_no := 0;
2621
2622 OPEN c_disb_details(p_disb_hdr_id);
2623 FETCH c_disb_details INTO
2624 l_loan_id
2625 ,l_pay_req_date
2626 ,l_disbursement_date
2627 ,l_phase;
2628 close c_disb_details;
2629
2630 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_id: ' || l_loan_id);
2631 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_pay_req_date: ' || l_pay_req_date);
2632 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_disbursement_date: ' || l_disbursement_date);
2633 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_phase: ' || l_phase);
2634
2635 l_sourceDate := null;
2636 IF (l_disbursement_date IS NOT NULL) THEN
2637 l_sourceDate := l_disbursement_date;
2638 ELSE
2639 l_sourceDate := l_pay_req_date;
2640 END IF;
2641
2642 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' Calling getNextInstallmentAfterDate...');
2643 l_installment_no := getNextInstallmentAfterDate(l_loan_id, l_sourceDate, l_phase);
2644
2645 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' l_installment_no = ' || l_installment_no);
2646 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
2647
2648 return l_installment_no;
2649
2650 end getNextInstForDisbursement;
2651
2652
2653 -- This procedure returns CURRENT/ORIGINAL/CUSTOM amortization schedule in form of csv formatted clob
2654 procedure getAmortSchedCSV(
2655 P_API_VERSION IN NUMBER,
2656 P_INIT_MSG_LIST IN VARCHAR2,
2657 P_COMMIT IN VARCHAR2,
2658 P_VALIDATION_LEVEL IN NUMBER,
2659 P_LOAN_ID IN NUMBER,
2660 P_SCHEDULE_TYPE IN VARCHAR2, -- CURRENT/ORIGINAL/CUSTOM
2661 X_CSV_CLOB OUT NOCOPY CLOB,
2662 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2663 X_MSG_COUNT OUT NOCOPY NUMBER,
2664 X_MSG_DATA OUT NOCOPY VARCHAR2)
2665 IS
2666
2667 /*-----------------------------------------------------------------------+
2668 | Local Variable Declarations and initializations |
2669 +-----------------------------------------------------------------------*/
2670 l_api_name CONSTANT VARCHAR2(30) := 'getAmortSchedCSV';
2671 l_api_version CONSTANT NUMBER := 1.0;
2672 l_msg_count NUMBER;
2673 l_msg_data VARCHAR2(32767);
2674 l_return_status VARCHAR2(1);
2675 l_csv_str varchar2(512);
2676 l_date_format varchar2(64);
2677 l_amort_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
2678 l_custom_tbl LNS_CUSTOM_PUB.CUSTOM_TBL;
2679 l_CUSTOM_SET_REC LNS_CUSTOM_PUB.custom_settings_type;
2680 l_endline CONSTANT VARCHAR2(5) := '
2681 ';
2682
2683 /*-----------------------------------------------------------------------+
2684 | Cursor Declarations |
2685 +-----------------------------------------------------------------------*/
2686
2687 BEGIN
2688
2689 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
2690
2691 -- Standard start of API savepoint
2692 SAVEPOINT getAmortSchedCSV;
2693 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Savepoint is established');
2694
2695 -- Standard call to check for call compatibility
2696 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2697 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2698 END IF;
2699
2700 -- Initialize message list if p_init_msg_list is set to TRUE
2701 IF FND_API.To_Boolean(p_init_msg_list) THEN
2702 FND_MSG_PUB.initialize;
2703 END IF;
2704
2705 -- Initialize API return status to success
2706 l_return_status := FND_API.G_RET_STS_SUCCESS;
2707
2708 -- START OF BODY OF API
2709 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
2710 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID: ' || P_LOAN_ID);
2711 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_SCHEDULE_TYPE: ' || P_SCHEDULE_TYPE);
2712
2713 -- validation
2714 if P_LOAN_ID is null then
2715 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2716 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
2717 FND_MESSAGE.SET_TOKEN('VALUE', P_LOAN_ID);
2718 FND_MSG_PUB.ADD;
2719 RAISE FND_API.G_EXC_ERROR;
2720 end if;
2721 if P_SCHEDULE_TYPE is null then
2722 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2723 FND_MESSAGE.SET_TOKEN('PARAMETER', 'P_SCHEDULE_TYPE');
2724 FND_MESSAGE.SET_TOKEN('VALUE', P_SCHEDULE_TYPE);
2725 FND_MSG_PUB.ADD;
2726 RAISE FND_API.G_EXC_ERROR;
2727 end if;
2728
2729 /* init statement xml */
2730 DBMS_LOB.createtemporary(X_CSV_CLOB, FALSE, DBMS_LOB.CALL);
2731 DBMS_LOB.open(X_CSV_CLOB, DBMS_LOB.lob_readwrite);
2732
2733 l_csv_str := 'Payment Number,Due Date,Interest Rate,Beginning Balance,Payment,Principal,Freeze Principal Flag,Interest,Freeze Interest Flag,Fees,Ending Balance';
2734 dbms_lob.writeAppend(X_CSV_CLOB, length(l_csv_str), l_csv_str);
2735
2736 l_date_format := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
2737 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_date_format: ' || l_date_format);
2738
2739 if P_SCHEDULE_TYPE = 'CURRENT' or P_SCHEDULE_TYPE = 'ORIGINAL' then
2740
2741 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_FINANCIALS.runAmortization...');
2742 LNS_FINANCIALS.runAmortization(
2743 P_API_VERSION => 1.0,
2744 P_INIT_MSG_LIST => FND_API.G_TRUE,
2745 P_COMMIT => FND_API.G_FALSE,
2746 P_LOAN_ID => P_LOAN_ID,
2747 P_BASED_ON_TERMS => P_SCHEDULE_TYPE,
2748 x_amort_tbl => l_amort_tbl,
2749 x_return_status => l_return_status,
2750 x_msg_count => l_msg_count,
2751 x_msg_data => l_msg_data);
2752
2753 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status: ' || l_return_status);
2754 IF l_return_status <> 'S' THEN
2755 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'ERROR: ' || FND_MSG_PUB.Get(p_encoded => 'F'));
2756 RAISE FND_API.G_EXC_ERROR;
2757 END IF;
2758
2759 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Building CLOB...');
2760 FOR i IN 1 .. l_amort_tbl.count LOOP
2761 l_csv_str := l_endline ||
2762 l_amort_tbl(i).INSTALLMENT_NUMBER || ',' ||
2763 to_date(l_amort_tbl(i).DUE_DATE, l_date_format) || ',' ||
2764 l_amort_tbl(i).INTEREST_RATE || ',' ||
2765 l_amort_tbl(i).BEGIN_BALANCE || ',' ||
2766 l_amort_tbl(i).TOTAL || ',' ||
2767 l_amort_tbl(i).PRINCIPAL_AMOUNT || ',Y,' ||
2768 l_amort_tbl(i).INTEREST_AMOUNT || ',Y,' ||
2769 l_amort_tbl(i).FEE_AMOUNT || ',' ||
2770 l_amort_tbl(i).END_BALANCE;
2771 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_csv_str);
2772 dbms_lob.writeAppend(X_CSV_CLOB, length(l_csv_str), l_csv_str);
2773 END LOOP;
2774
2775 else
2776
2777 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_CUSTOM_PUB.getRetainedSchedule...');
2778 LNS_CUSTOM_PUB.getRetainedSchedule(
2779 P_API_VERSION => 1.0,
2780 P_INIT_MSG_LIST => FND_API.G_TRUE,
2781 P_COMMIT => FND_API.G_FALSE,
2782 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
2783 X_CUSTOM_SET_REC => l_CUSTOM_SET_REC,
2784 X_CUSTOM_TBL => l_custom_tbl,
2785 x_return_status => l_return_status,
2786 x_msg_count => l_msg_count,
2787 x_msg_data => l_msg_data);
2788
2789 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status: ' || l_return_status);
2790 IF l_return_status <> 'S' THEN
2791 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'ERROR: ' || FND_MSG_PUB.Get(p_encoded => 'F'));
2792 RAISE FND_API.G_EXC_ERROR;
2793 END IF;
2794
2795 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Building CLOB...');
2796 FOR i IN 1 .. l_custom_tbl.count LOOP
2797 l_csv_str := l_endline ||
2798 l_custom_tbl(i).PAYMENT_NUMBER || ',' ||
2799 to_date(l_custom_tbl(i).DUE_DATE, l_date_format) || ',' ||
2800 l_custom_tbl(i).INTEREST_RATE || ',' ||
2801 l_custom_tbl(i).INSTALLMENT_BEGIN_BALANCE || ',' ||
2802 l_custom_tbl(i).CURRENT_TERM_PAYMENT || ',' ||
2803 l_custom_tbl(i).PRINCIPAL_AMOUNT || ',Y,' ||
2804 l_custom_tbl(i).INTEREST_AMOUNT || ',Y,' ||
2805 l_custom_tbl(i).FEE_AMOUNT || ',' ||
2806 l_custom_tbl(i).INSTALLMENT_END_BALANCE;
2807 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_csv_str);
2808 dbms_lob.writeAppend(X_CSV_CLOB, length(l_csv_str), l_csv_str);
2809 END LOOP;
2810
2811 end if;
2812
2813 -- END OF BODY OF API
2814 if P_COMMIT = FND_API.G_TRUE then
2815 COMMIT WORK;
2816 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
2817 end if;
2818
2819 -- END OF BODY OF API
2820 x_return_status := FND_API.G_RET_STS_SUCCESS;
2821
2822 -- Standard call to get message count and if count is 1, get message info
2823 FND_MSG_PUB.Count_And_Get(
2824 p_encoded => FND_API.G_FALSE,
2825 p_count => x_msg_count,
2826 p_data => x_msg_data);
2827
2828 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
2829
2830 EXCEPTION
2831 WHEN FND_API.G_EXC_ERROR THEN
2832 ROLLBACK TO getAmortSchedCSV;
2833 x_return_status := FND_API.G_RET_STS_ERROR;
2834 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2835 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
2836 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2837 ROLLBACK TO getAmortSchedCSV;
2838 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2839 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2840 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
2841 WHEN OTHERS THEN
2842 ROLLBACK TO getAmortSchedCSV;
2843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2844 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2845 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
2846 END;
2847
2848
2849 function fetchFreqSchedule(
2850 P_LOAN_ID IN NUMBER,
2851 P_PHASE IN VARCHAR2, -- OPEN/TERM/null will be defaulted to TERM
2852 P_COMPONENT IN VARCHAR2 -- PRIN/INT/PRIN_INT/null will be defaulted to PRIN_INT
2853 ) return LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL
2854 IS
2855
2856 /*-----------------------------------------------------------------------+
2857 | Local Variable Declarations and initializations |
2858 +-----------------------------------------------------------------------*/
2859 l_api_name CONSTANT VARCHAR2(30) := 'fetchFreqSchedule';
2860 l_api_version CONSTANT NUMBER := 1.0;
2861 l_msg_count NUMBER;
2862 l_msg_data VARCHAR2(32767);
2863 l_return_status VARCHAR2(1);
2864 i number;
2865 l_FREQUENCY_BEGIN_DATE date;
2866 l_FREQUENCY VARCHAR2(30);
2867 l_FREQ_SCHEDULE_TBL LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL;
2868
2869 /*-----------------------------------------------------------------------+
2870 | Cursor Declarations |
2871 +-----------------------------------------------------------------------*/
2872
2873 cursor c_freq_schedule (p_loan_id NUMBER, p_phase VARCHAR2, p_component VARCHAR2) is
2874 select FREQUENCY_BEGIN_DATE, FREQUENCY
2875 from LNS_FREQ_SCHEDULES
2876 where loan_id = p_loan_id and
2877 nvl(phase, 'TERM') = nvl(p_phase, 'TERM') and
2878 nvl(component, 'PRIN_INT') = nvl(p_component, 'PRIN_INT')
2879 order by FREQUENCY_BEGIN_DATE;
2880
2881 BEGIN
2882
2883 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
2884
2885 -- START OF BODY OF API
2886 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
2887 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID: ' || P_LOAN_ID);
2888 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_PHASE: ' || P_PHASE);
2889 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_COMPONENT: ' || P_COMPONENT);
2890
2891 i := 0;
2892 OPEN c_freq_schedule(P_LOAN_ID, P_PHASE, P_COMPONENT);
2893 LOOP
2894 FETCH c_freq_schedule INTO l_FREQUENCY_BEGIN_DATE, l_FREQUENCY;
2895 exit when c_freq_schedule%NOTFOUND;
2896
2897 i := i + 1;
2898 l_FREQ_SCHEDULE_TBL(i).PERIOD_BEGIN_DATE := l_FREQUENCY_BEGIN_DATE;
2899 l_FREQ_SCHEDULE_TBL(i).FREQUENCY := l_FREQUENCY;
2900 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Record ' || i);
2901 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PERIOD_BEGIN_DATE = ' || l_FREQ_SCHEDULE_TBL(i).PERIOD_BEGIN_DATE);
2902 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'FREQUENCY = ' || l_FREQ_SCHEDULE_TBL(i).FREQUENCY);
2903 END LOOP;
2904 close c_freq_schedule;
2905
2906 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
2907 return l_FREQ_SCHEDULE_TBL;
2908
2909 END;
2910
2911
2912 procedure fetchPayFreqRecByDate(
2913 p_FREQUENCY_SCHEDULE IN LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL,
2914 p_DATE IN DATE,
2915 x_FREQUENCY_REC OUT NOCOPY LNS_FIN_UTILS.FREQUENCY_SCHEDULE)
2916 IS
2917
2918 l_api_name CONSTANT VARCHAR2(30) := 'fetchPayFreqRecByDate';
2919
2920 BEGIN
2921
2922 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
2923 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
2924 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_DATE = ' || p_DATE);
2925
2926 x_FREQUENCY_REC := p_FREQUENCY_SCHEDULE(1);
2927 for i in REVERSE 1..p_FREQUENCY_SCHEDULE.count loop
2928 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Record ' || i || ': Date = ' || p_FREQUENCY_SCHEDULE(i).PERIOD_BEGIN_DATE);
2929 if p_DATE >= p_FREQUENCY_SCHEDULE(i).PERIOD_BEGIN_DATE then
2930 x_FREQUENCY_REC := p_FREQUENCY_SCHEDULE(i);
2931 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Returning this record');
2932 exit;
2933 end if;
2934 end loop;
2935
2936 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
2937
2938 END;
2939
2940
2941 END LNS_FIN_UTILS;