[Home] [Help]
PACKAGE BODY: APPS.LNS_INDEX_RATES_PUB
Source
1 PACKAGE BODY LNS_INDEX_RATES_PUB as
2 /* $Header: LNS_FLOATRATE_B.pls 120.0.12010000.5 2009/08/14 16:15:37 scherkas noship $ */
3
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7 G_PKG_NAME CONSTANT VARCHAR2(30):= 'LNS_INDEX_RATES_PUB';
8
9 TYPE LOAN_REC is record(LOAN_ID NUMBER,
10 TERM_ID NUMBER,
11 LOAN_NUMBER VARCHAR2(60),
12 LAST_BILLED_INSTALLMENT NUMBER,
13 LOAN_STATUS VARCHAR2(30),
14 CURRENT_PHASE VARCHAR2(30),
15 percent_increase NUMBER,
16 percent_increase_life NUMBER,
17 floor_rate NUMBER,
18 ceiling_rate NUMBER
19 );
20
21 TYPE RATE_LINE_REC is record(INTEREST_RATE_LINE_ID NUMBER,
22 INTEREST_RATE_ID NUMBER,
23 INTEREST_RATE NUMBER,
24 START_DATE_ACTIVE DATE,
25 END_DATE_ACTIVE DATE
26 );
27 TYPE RATE_LINES_TBL is table of RATE_LINE_REC index by binary_integer;
28
29 TYPE RATE_SCHED_REC is record(RATE_ID NUMBER,
30 TERM_ID NUMBER,
31 BEGIN_INSTALLMENT_NUMBER NUMBER,
32 END_INSTALLMENT_NUMBER NUMBER,
33 INDEX_RATE NUMBER,
34 SPREAD NUMBER,
35 CURRENT_INTEREST_RATE NUMBER,
36 INTEREST_ONLY_FLAG VARCHAR2(1),
37 ACTION VARCHAR2(20),
38 BEGIN_DATE DATE,
39 END_DATE DATE
40 );
41 TYPE RATE_SCHEDS_TBL is table of RATE_SCHED_REC index by binary_integer;
42
43 TYPE ADJ_RATE_REC is record(FROM_INSTALLMENT NUMBER,
44 TO_INSTALLMENT NUMBER,
45 INTEREST_RATE NUMBER,
46 START_DATE DATE,
47 END_DATE DATE
48 );
49 TYPE ADJ_RATES_TBL is table of ADJ_RATE_REC index by binary_integer;
50
51 TYPE RATE_SCHED_LINE_REC is record(BEGIN_DATE DATE,
52 END_DATE DATE,
53 INDEX_RATE NUMBER,
54 SPREAD NUMBER,
55 CURRENT_INTEREST_RATE NUMBER
56 );
57 TYPE RATE_SCHED_LINES_TBL is table of RATE_SCHED_LINE_REC index by binary_integer;
58
59 /*========================================================================
60 | PRIVATE PROCEDURE LogMessage
61 |
62 | DESCRIPTION
63 | This procedure logs debug messages to db and to CM log
64 |
65 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
66 | UPDATE_FLOATING_RATE_LOANS
67 |
68 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
69 | None
70 |
71 | PARAMETERS
72 | p_msg_level IN Debug msg level
73 | p_msg IN Debug msg itself
74 |
75 | KNOWN ISSUES
76 | None
77 |
78 | NOTES
79 | Any interesting aspect of the code in the package body which needs
80 | to be stated.
81 |
82 | MODIFICATION HISTORY
83 | Date Author Description of Changes
84 | 01-01-2004 scherkas Created
85 |
86 *=======================================================================*/
87 Procedure LogMessage(p_msg_level IN NUMBER, p_msg in varchar2)
88 IS
89 BEGIN
90 if (p_msg_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
91
92 FND_LOG.STRING(p_msg_level, G_PKG_NAME, p_msg);
93
94 end if;
95
96 if (FND_GLOBAL.Conc_Request_Id is not null) then
97 fnd_file.put_line(FND_FILE.LOG, p_msg);
98 end if;
99
100 EXCEPTION
101 WHEN OTHERS THEN
102 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
103 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'ERROR in LogMessage while logging '|| p_msg || ' : ' || sqlerrm);
104 end if;
105 END;
106
107
108
109 function dateToPayNum(P_PAYMENT_SCHEDULE in LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL
110 ,p_date in date) return number
111 is
112 l_num_installments number;
113 l_pay_num number;
114 begin
115
116 l_num_installments := P_PAYMENT_SCHEDULE.count;
117
118 if trunc(p_date) < trunc(P_PAYMENT_SCHEDULE(1).PERIOD_BEGIN_DATE) then
119 l_pay_num := 1;
120 elsif trunc(p_date) > trunc(P_PAYMENT_SCHEDULE(l_num_installments).PERIOD_END_DATE) then
121 l_pay_num := l_num_installments+1;
122 else
123 for i in 1..l_num_installments loop
124 if trunc(p_date) > trunc(P_PAYMENT_SCHEDULE(i).PERIOD_BEGIN_DATE) and
125 trunc(p_date) <= trunc(P_PAYMENT_SCHEDULE(i).PERIOD_END_DATE)
126 then
127 l_pay_num := i+1;
128 exit;
129 elsif trunc(p_date) = trunc(P_PAYMENT_SCHEDULE(i).PERIOD_BEGIN_DATE) then
130 l_pay_num := i;
131 exit;
132 end if;
133 end loop;
134 end if;
135
136 logMessage(FND_LOG.LEVEL_STATEMENT, 'Date ' || p_date || ' = payment ' || l_pay_num);
137 return l_pay_num;
138
139 end;
140
141
142
143 function payNumToDate(P_PAYMENT_SCHEDULE in LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL
144 ,p_installment in number
145 ,p_target in varchar2) return date
146 is
147 l_num_installments number;
148 l_return_date date;
149 begin
150
151 l_num_installments := P_PAYMENT_SCHEDULE.count;
152
153 for i in 1..l_num_installments loop
154 if p_installment = i then
155 if p_target = 'BEGIN' then
156 l_return_date := P_PAYMENT_SCHEDULE(i).PERIOD_BEGIN_DATE;
157 elsif p_target = 'END' then
158 l_return_date := P_PAYMENT_SCHEDULE(i).PERIOD_END_DATE;
159 end if;
160 exit;
161 end if;
162 end loop;
163
164 -- logMessage(FND_LOG.LEVEL_STATEMENT, p_target || ' of installment ' || p_installment || ' = ' || l_return_date);
165 return l_return_date;
166
167 end;
168
169
170
171 -- This procedure adjust interest rate based on provided rules
172 procedure adjustInterestRate(p_initial_rate in number
173 ,p_last_period_rate in number
174 ,p_max_period_adjustment in number
175 ,p_max_lifetime_adjustment in number
176 ,p_ceiling_rate in number
177 ,p_floor_rate in number
178 ,x_interest_rate in out nocopy number
179 ,x_adjustment_reason out nocopy varchar2)
180
181 is
182 l_api_name CONSTANT VARCHAR2(30) := 'adjustInterestRate';
183 l_new_rate number;
184 l_rate_diff number;
185 l_life_rate_diff number;
186 l_sign1 number;
187 l_sign2 number;
188 l_adjustment_reason varchar2(256);
189 l_new_rate1 number;
190 l_new_line CONSTANT VARCHAR2(1) := '
191 ';
192
193 begin
194
195 logMessage(FND_LOG.LEVEL_PROCEDURE, l_api_name || ' +');
196
197 logMessage(FND_LOG.LEVEL_STATEMENT, 'Input parameters:');
198 logMessage(FND_LOG.LEVEL_STATEMENT, 'p_initial_rate = ' || p_initial_rate);
199 logMessage(FND_LOG.LEVEL_STATEMENT, 'p_last_period_rate = ' || p_last_period_rate);
200 logMessage(FND_LOG.LEVEL_STATEMENT, 'p_max_period_adjustment = ' || p_max_period_adjustment);
201 logMessage(FND_LOG.LEVEL_STATEMENT, 'p_max_lifetime_adjustment = ' || p_max_lifetime_adjustment);
202 logMessage(FND_LOG.LEVEL_STATEMENT, 'p_ceiling_rate = ' || p_ceiling_rate);
203 logMessage(FND_LOG.LEVEL_STATEMENT, 'p_floor_rate = ' || p_floor_rate);
204 logMessage(FND_LOG.LEVEL_STATEMENT, 'x_interest_rate = ' || x_interest_rate);
205
206 -- need to check for NULLs
207 l_sign1 := 1;
208 l_sign2 := 1;
209
210 l_new_rate := x_interest_rate;
211
212 l_rate_diff := ABS(l_new_rate - p_last_period_rate);
213 logMessage(FND_LOG.LEVEL_STATEMENT, 'l_rate_diff = ' || l_rate_diff);
214
215 -- rate differentials go both ways
216 if l_new_rate < p_last_period_rate then
217 l_sign1 := -1;
218 end if;
219
220 if p_max_period_adjustment is not null and l_rate_diff > p_max_period_adjustment then
221 l_new_rate1 := l_new_rate;
222 l_new_rate := p_last_period_rate + (p_max_period_adjustment * l_sign1);
223 logMessage(FND_LOG.LEVEL_STATEMENT, 'l_new_rate = ' || l_new_rate);
224 l_adjustment_reason :=
225 'Difference between previous period rate ' || p_last_period_rate ||
226 '% and new rate ' || l_new_rate1 || '% is greater than max period adjustment differential of ' || p_max_period_adjustment ||
227 '%. Adjusting new rate to ' || l_new_rate || '%.';
228 logMessage(FND_LOG.LEVEL_STATEMENT, l_adjustment_reason);
229 end if;
230
231 l_life_rate_diff := ABS(l_new_rate - p_initial_rate);
232 logMessage(FND_LOG.LEVEL_STATEMENT, 'l_life_rate_diff = ' || l_life_rate_diff);
233
234 -- rate differentials go both ways
235 if l_new_rate < p_initial_rate then
236 l_sign2 := -1;
237 end if;
238
239 if p_max_lifetime_adjustment is not null and l_life_rate_diff > p_max_lifetime_adjustment then
240 l_new_rate1 := l_new_rate;
241 l_new_rate := p_initial_rate + (p_max_lifetime_adjustment * l_sign2);
242 logMessage(FND_LOG.LEVEL_STATEMENT, 'l_new_rate = ' || l_new_rate);
243
244 if l_adjustment_reason is not null then
245 l_adjustment_reason := l_adjustment_reason || l_new_line;
246 end if;
247 l_adjustment_reason := l_adjustment_reason ||
248 'Difference between initial rate ' || p_initial_rate ||
249 '% and new rate ' || l_new_rate1 || '% is greater than life adjustment differential of ' || p_max_lifetime_adjustment ||
250 '. Adjusting new rate to ' || l_new_rate || '%.';
251 logMessage(FND_LOG.LEVEL_STATEMENT, l_adjustment_reason);
252 end if;
253
254 if p_floor_rate is not null and l_new_rate < p_floor_rate then
255 l_new_rate1 := l_new_rate;
256 l_new_rate := p_floor_rate;
257 logMessage(FND_LOG.LEVEL_STATEMENT, 'l_new_rate = ' || l_new_rate);
258
259 if l_adjustment_reason is not null then
260 l_adjustment_reason := l_adjustment_reason || l_new_line;
261 end if;
262 l_adjustment_reason := l_adjustment_reason ||
263 'New rate ' || l_new_rate1 || '% is below floor of ' || p_floor_rate ||
264 '. Adjusting new rate to ' || l_new_rate || '%.';
265 logMessage(FND_LOG.LEVEL_STATEMENT, l_adjustment_reason);
266 end if;
267
268 if p_ceiling_rate is not null and l_new_rate > p_ceiling_rate then
269 l_new_rate1 := l_new_rate;
270 l_new_rate := p_ceiling_rate;
271 logMessage(FND_LOG.LEVEL_STATEMENT, 'l_new_rate = ' || l_new_rate);
272
273 if l_adjustment_reason is not null then
274 l_adjustment_reason := l_adjustment_reason || l_new_line;
275 end if;
276 l_adjustment_reason := l_adjustment_reason ||
277 'New rate ' || l_new_rate1 || '% is above ceiling of ' || p_ceiling_rate ||
278 '. Adjusting new rate to ' || l_new_rate || '%.';
279 logMessage(FND_LOG.LEVEL_STATEMENT, l_adjustment_reason);
280 end if;
281
282 logMessage(FND_LOG.LEVEL_PROCEDURE, 'l_new_rate = ' || l_new_rate);
283 logMessage(FND_LOG.LEVEL_PROCEDURE, l_api_name || ' -');
284
285 x_interest_rate := l_new_rate;
286 x_adjustment_reason := l_adjustment_reason;
287
288 end;
289
290
291
292
293 PROCEDURE PROCESS_SINGLE_LOAN(
294 P_API_VERSION IN NUMBER,
295 P_INIT_MSG_LIST IN VARCHAR2,
296 P_COMMIT IN VARCHAR2,
297 P_VALIDATION_LEVEL IN NUMBER,
298 P_LOAN_REC IN LOAN_REC,
299 P_RATE_LINES_TBL IN RATE_LINES_TBL,
300 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
301 X_MSG_COUNT OUT NOCOPY NUMBER,
302 X_MSG_DATA OUT NOCOPY VARCHAR2)
303 IS
304
305 /*-----------------------------------------------------------------------+
306 | Local Variable Declarations and initializations |
307 +-----------------------------------------------------------------------*/
308
309 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_SINGLE_LOAN';
310 l_api_version CONSTANT NUMBER := 1.0;
311 i number;
312 j number;
313 y number;
314 l_rates_count number;
315 l_temp_pay_num number;
316 l_rate_sched_from number;
317 l_rate_sched_to number;
318 l_index_from number;
319 l_index_to number;
320 l_rate_sched_rate number;
321 l_prev_spead number;
322 l_prev_io varchar2(1);
323 rate_sched_count number;
324 index_rate_count number;
325 merged_count number;
326 l_index_rate number;
327 l_last_period_rate NUMBER;
328 l_initial_int_rate NUMBER;
329 l_start_from_installment NUMBER;
330 l_update1 boolean;
331 l_adjustment_reason varchar2(256);
332 l_start number;
333 l_do_insert boolean;
334
335 l_RATE_LINES_TBL RATE_LINES_TBL;
336 l_RATE_LINE_REC RATE_LINE_REC;
337 l_RATE_SCHED_REC RATE_SCHED_REC;
338 l_RATE_SCHEDS_TBL RATE_SCHEDS_TBL;
339 l_pay_schedule LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
340 l_ADJ_RATES_TBL ADJ_RATES_TBL;
341 l_TEMP_ADJ_RATES_TBL ADJ_RATES_TBL;
342 l_merged_rates_tbl RATE_SCHEDS_TBL;
343 l_merged_rate_lines_tbl RATE_SCHED_LINES_TBL;
344
345 /*-----------------------------------------------------------------------+
346 | Cursor Declarations |
347 +-----------------------------------------------------------------------*/
348
349 CURSOR c_get_rate_sch_info(termId NUMBER, p_phase VARCHAR2) IS
350 SELECT rate_id,
351 begin_installment_number,
352 end_installment_number,
353 index_rate,
354 spread,
355 CURRENT_INTEREST_RATE,
356 INTEREST_ONLY_FLAG
357 FROM lns_rate_schedules
358 WHERE end_date_active IS NULL
359 AND term_id = termId
360 AND PHASE = p_phase
361 order by begin_installment_number;
362
363 BEGIN
364 LogMessage(FND_LOG.level_unexpected, ' ');
365 LogMessage(FND_LOG.LEVEL_PROCEDURE, l_api_name || ' +');
366
367 -- Standard start of API savepoint
368 SAVEPOINT PROCESS_SINGLE_LOAN;
369 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Established savepoint');
370
371 -- Standard call to check for call compatibility
372 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
373 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
374 END IF;
375
376 -- Initialize message list if p_init_msg_list is set to TRUE
377 IF FND_API.To_Boolean(p_init_msg_list) THEN
378 FND_MSG_PUB.initialize;
379 END IF;
380
381 -- Initialize API return status to success
382 x_return_status := FND_API.G_RET_STS_SUCCESS;
383
384 -- START OF BODY OF API
385
386 -- init
387 l_RATE_LINES_TBL := P_RATE_LINES_TBL;
388 l_rates_count := l_RATE_LINES_TBL.count;
389
390 LogMessage(FND_LOG.level_unexpected, 'Processing loan ' || P_LOAN_REC.LOAN_NUMBER);
391 LogMessage(FND_LOG.level_unexpected, 'loan_id = ' || P_LOAN_REC.LOAN_ID);
392 LogMessage(FND_LOG.level_unexpected, 'term_id = ' || P_LOAN_REC.TERM_ID);
393 LogMessage(FND_LOG.level_unexpected, 'last_billed_installment = ' || P_LOAN_REC.LAST_BILLED_INSTALLMENT);
394 LogMessage(FND_LOG.level_unexpected, 'loan_status = ' || P_LOAN_REC.LOAN_STATUS);
395 LogMessage(FND_LOG.level_unexpected, 'CURRENT_PHASE = ' || P_LOAN_REC.CURRENT_PHASE);
396 LogMessage(FND_LOG.level_unexpected, 'percent_increase = ' || P_LOAN_REC.percent_increase);
397 LogMessage(FND_LOG.level_unexpected, 'percent_increase_life = ' || P_LOAN_REC.percent_increase_life);
398 LogMessage(FND_LOG.level_unexpected, 'floor_rate = ' || P_LOAN_REC.floor_rate);
399 LogMessage(FND_LOG.level_unexpected, 'ceiling_rate = ' || P_LOAN_REC.ceiling_rate);
400
401 l_start_from_installment := P_LOAN_REC.LAST_BILLED_INSTALLMENT + 1;
402 LogMessage(FND_LOG.level_unexpected, 'l_start_from_installment = ' || l_start_from_installment);
403
404 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling LNS_FIN_UTILS.buildPaymentScheduleExt...');
405 l_pay_schedule := LNS_FIN_UTILS.buildPaymentScheduleExt(P_LOAN_REC.LOAN_ID, P_LOAN_REC.CURRENT_PHASE);
406
407 i := 0;
408 l_RATE_SCHEDS_TBL.delete;
409 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Current loan rate schedule:');
410 LogMessage(FND_LOG.LEVEL_STATEMENT, 'From_date To_Date From_Inst To_Inst Rate+Spread=Current_Rate IO Rate_sched_id');
411 LogMessage(FND_LOG.LEVEL_STATEMENT, '--------- ------- --------- ------- ------------------------ -- -------------');
412 open c_get_rate_sch_info(P_LOAN_REC.TERM_ID, P_LOAN_REC.CURRENT_PHASE);
413 LOOP
414
415 fetch c_get_rate_sch_info into
416 l_RATE_SCHED_REC.RATE_ID,
417 l_RATE_SCHED_REC.BEGIN_INSTALLMENT_NUMBER,
418 l_RATE_SCHED_REC.END_INSTALLMENT_NUMBER,
419 l_RATE_SCHED_REC.INDEX_RATE,
420 l_RATE_SCHED_REC.SPREAD,
421 l_RATE_SCHED_REC.CURRENT_INTEREST_RATE,
422 l_RATE_SCHED_REC.INTEREST_ONLY_FLAG;
423 exit when c_get_rate_sch_info%NOTFOUND;
424
425 i := i + 1;
426 l_RATE_SCHEDS_TBL(i) := l_RATE_SCHED_REC;
427
428 l_RATE_SCHEDS_TBL(i).BEGIN_DATE := payNumToDate(l_pay_schedule,
429 l_RATE_SCHEDS_TBL(i).BEGIN_INSTALLMENT_NUMBER,
430 'BEGIN');
431 l_RATE_SCHEDS_TBL(i).END_DATE := payNumToDate(l_pay_schedule,
432 l_RATE_SCHEDS_TBL(i).END_INSTALLMENT_NUMBER,
433 'END');
434
435 LogMessage(FND_LOG.LEVEL_STATEMENT,
436 l_RATE_SCHEDS_TBL(i).BEGIN_DATE || ' ' ||
437 l_RATE_SCHEDS_TBL(i).END_DATE || ' ' ||
438 l_RATE_SCHEDS_TBL(i).BEGIN_INSTALLMENT_NUMBER || ' ' ||
439 l_RATE_SCHEDS_TBL(i).END_INSTALLMENT_NUMBER || ' ' ||
440 l_RATE_SCHEDS_TBL(i).INDEX_RATE || ' + ' || l_RATE_SCHEDS_TBL(i).SPREAD || ' = ' || l_RATE_SCHEDS_TBL(i).CURRENT_INTEREST_RATE || ' ' ||
441 l_RATE_SCHEDS_TBL(i).INTEREST_ONLY_FLAG || ' ' ||
442 l_RATE_SCHEDS_TBL(i).RATE_ID);
443
444 if l_RATE_SCHED_REC.BEGIN_INSTALLMENT_NUMBER = 1 then
445 l_initial_int_rate := l_RATE_SCHED_REC.CURRENT_INTEREST_RATE;
446 end if;
447
448 END LOOP;
449 close c_get_rate_sch_info;
450
451 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Adjusting index rate lines for this loan...');
452 l_TEMP_ADJ_RATES_TBL.delete;
453 y := 0;
454 for k in 1..l_rates_count loop
455 l_temp_pay_num := dateToPayNum(l_pay_schedule, l_RATE_LINES_TBL(k).START_DATE_ACTIVE);
456 if l_temp_pay_num <= l_pay_schedule.count then
457 if y = 0 or l_TEMP_ADJ_RATES_TBL(y).FROM_INSTALLMENT <> l_temp_pay_num then
458 y := y + 1;
459 end if;
460 l_TEMP_ADJ_RATES_TBL(y).FROM_INSTALLMENT := l_temp_pay_num;
461 l_TEMP_ADJ_RATES_TBL(y).INTEREST_RATE := l_RATE_LINES_TBL(k).INTEREST_RATE;
462 l_TEMP_ADJ_RATES_TBL(y).START_DATE := l_RATE_LINES_TBL(k).START_DATE_ACTIVE;
463 l_TEMP_ADJ_RATES_TBL(y).END_DATE := l_RATE_LINES_TBL(k).END_DATE_ACTIVE;
464 end if;
465 if y > 1 then
466 l_TEMP_ADJ_RATES_TBL(y-1).TO_INSTALLMENT := l_TEMP_ADJ_RATES_TBL(y).FROM_INSTALLMENT - 1;
467 end if;
468 end loop;
469 if y > 0 then
470 l_TEMP_ADJ_RATES_TBL(y).TO_INSTALLMENT := l_RATE_SCHEDS_TBL(i).END_INSTALLMENT_NUMBER;
471 end if;
472
473 l_ADJ_RATES_TBL.delete;
474 i := 0;
475 for k in 1..l_TEMP_ADJ_RATES_TBL.count loop
476 if l_start_from_installment between l_TEMP_ADJ_RATES_TBL(k).FROM_INSTALLMENT and l_TEMP_ADJ_RATES_TBL(k).TO_INSTALLMENT then
477 i := i + 1;
478 l_ADJ_RATES_TBL(i) := l_TEMP_ADJ_RATES_TBL(k);
479 l_ADJ_RATES_TBL(i).FROM_INSTALLMENT := l_start_from_installment;
480 elsif l_TEMP_ADJ_RATES_TBL(k).FROM_INSTALLMENT > l_start_from_installment then
481 i := i + 1;
482 l_ADJ_RATES_TBL(i) := l_TEMP_ADJ_RATES_TBL(k);
483 end if;
484 end loop;
485
486 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Adjusted index rate lines:');
487 LogMessage(FND_LOG.LEVEL_STATEMENT, 'From To Rate');
488 LogMessage(FND_LOG.LEVEL_STATEMENT, '---- --- ----');
489 for k in 1..l_ADJ_RATES_TBL.count loop
490 LogMessage(FND_LOG.LEVEL_STATEMENT, l_ADJ_RATES_TBL(k).FROM_INSTALLMENT || ' ' || l_ADJ_RATES_TBL(k).TO_INSTALLMENT || ' ' || l_ADJ_RATES_TBL(k).INTEREST_RATE);
491 end loop;
492
493 if l_ADJ_RATES_TBL.count = 0 then
494 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Adjusted index rate table is empty. Nothing to Merge. Exiting.');
495 return;
496 end if;
497
498 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Merging rate schedule with adjusted index rate lines...');
499
500 rate_sched_count := 1;
501 index_rate_count := 1;
502 merged_count := 1;
503 l_update1 := true;
504
505 loop
506
507 l_rate_sched_from := null;
508 l_rate_sched_to := null;
509 l_index_from := null;
510 l_index_to := null;
511
512 if (rate_sched_count <= l_RATE_SCHEDS_TBL.count) then
513 l_rate_sched_from := l_RATE_SCHEDS_TBL(rate_sched_count).BEGIN_INSTALLMENT_NUMBER;
514 l_rate_sched_to := l_RATE_SCHEDS_TBL(rate_sched_count).END_INSTALLMENT_NUMBER;
515 l_rate_sched_rate := l_RATE_SCHEDS_TBL(rate_sched_count).INDEX_RATE;
516 else
517 l_rate_sched_rate := 0;
518 end if;
519
520 if (l_ADJ_RATES_TBL.count = 0) then
521 l_index_rate := 0;
522 elsif (index_rate_count <= l_ADJ_RATES_TBL.count) then
523 l_index_from := l_ADJ_RATES_TBL(index_rate_count).FROM_INSTALLMENT;
524 l_index_to := l_ADJ_RATES_TBL(index_rate_count).TO_INSTALLMENT;
525 l_index_rate := l_ADJ_RATES_TBL(index_rate_count).INTEREST_RATE;
526 else
527 l_index_rate := l_ADJ_RATES_TBL(l_ADJ_RATES_TBL.count).INTEREST_RATE;
528 end if;
529
530 logMessage(FND_LOG.LEVEL_STATEMENT, '---------------');
531 logMessage(FND_LOG.LEVEL_STATEMENT, 'Loop ' || merged_count);
532 logMessage(FND_LOG.LEVEL_STATEMENT, 'rate_sched_count = ' || rate_sched_count);
533 logMessage(FND_LOG.LEVEL_STATEMENT, 'index_rate_count = ' || index_rate_count);
534 logMessage(FND_LOG.LEVEL_STATEMENT, 'rate_sched: ' || l_rate_sched_from || ' - ' || l_rate_sched_to);
535 logMessage(FND_LOG.LEVEL_STATEMENT, 'index: ' || l_index_from || ' - ' || l_index_to);
536
537 if (l_rate_sched_from is not null and l_rate_sched_to is not null and
538 l_index_from is not null and l_index_from is not null)
539 then
540
541 if (l_rate_sched_from between l_index_from and l_index_to) then
542
543 if l_rate_sched_to > l_index_to then
544
545 logMessage(FND_LOG.LEVEL_STATEMENT, 'if 1 - updating');
546 l_merged_rates_tbl(merged_count) := l_RATE_SCHEDS_TBL(rate_sched_count);
547 l_merged_rates_tbl(merged_count).END_INSTALLMENT_NUMBER := l_index_to;
548 l_merged_rates_tbl(merged_count).INDEX_RATE := l_index_rate;
549 l_merged_rates_tbl(merged_count).CURRENT_INTEREST_RATE := l_index_rate + nvl(l_merged_rates_tbl(merged_count).SPREAD, 0);
550 l_merged_rates_tbl(merged_count).ACTION := 'UPDATE';
551 l_update1 := false;
552 index_rate_count := index_rate_count + 1;
553
554 elsif l_rate_sched_to < l_index_to then
555
556 l_merged_rates_tbl(merged_count) := l_RATE_SCHEDS_TBL(rate_sched_count);
557
558 if l_RATE_SCHEDS_TBL(rate_sched_count).INDEX_RATE = l_index_rate then
559
560 logMessage(FND_LOG.LEVEL_STATEMENT, 'if 21 - skipping');
561 l_merged_rates_tbl(merged_count).ACTION := 'SKIP';
562
563 else
564
565 logMessage(FND_LOG.LEVEL_STATEMENT, 'if 22 - updating');
566 l_merged_rates_tbl(merged_count).INDEX_RATE := l_index_rate;
567 l_merged_rates_tbl(merged_count).CURRENT_INTEREST_RATE := l_index_rate + nvl(l_merged_rates_tbl(merged_count).SPREAD, 0);
568 l_merged_rates_tbl(merged_count).ACTION := 'UPDATE';
569
570 end if;
571 rate_sched_count := rate_sched_count + 1;
572
573 else -- l_rate_sched_to = l_index_to
574
575 l_merged_rates_tbl(merged_count) := l_RATE_SCHEDS_TBL(rate_sched_count);
576
577 if l_RATE_SCHEDS_TBL(rate_sched_count).INDEX_RATE = l_index_rate then
578
579 logMessage(FND_LOG.LEVEL_STATEMENT, 'if 31 - skipping');
580 l_merged_rates_tbl(merged_count).ACTION := 'SKIP';
581
582 else
583
584 logMessage(FND_LOG.LEVEL_STATEMENT, 'if 32 - updating');
585 l_merged_rates_tbl(merged_count).INDEX_RATE := l_index_rate;
586 l_merged_rates_tbl(merged_count).CURRENT_INTEREST_RATE := l_index_rate + nvl(l_merged_rates_tbl(merged_count).SPREAD, 0);
587 l_merged_rates_tbl(merged_count).ACTION := 'UPDATE';
588
589 end if;
590 rate_sched_count := rate_sched_count + 1;
591 index_rate_count := index_rate_count + 1;
592
593 end if;
594
595 l_prev_spead := l_merged_rates_tbl(merged_count).SPREAD;
596 l_prev_io := l_merged_rates_tbl(merged_count).INTEREST_ONLY_FLAG;
597
598 if merged_count > 1 then
599 l_last_period_rate := l_merged_rates_tbl(merged_count-1).CURRENT_INTEREST_RATE;
600 else
601 if l_last_period_rate is null then
602 l_last_period_rate := l_merged_rates_tbl(merged_count).CURRENT_INTEREST_RATE;
603 end if;
604 end if;
605
606 if l_merged_rates_tbl(merged_count).BEGIN_INSTALLMENT_NUMBER = 1 then
607 l_initial_int_rate := l_merged_rates_tbl(merged_count).CURRENT_INTEREST_RATE;
608 end if;
609
610 if l_merged_rates_tbl(merged_count).ACTION <> 'SKIP' then
611 -- adjust rate based on loan rules
612 adjustInterestRate(p_initial_rate => l_initial_int_rate
613 ,p_last_period_rate => l_last_period_rate
614 ,p_max_period_adjustment => P_LOAN_REC.percent_increase
615 ,p_max_lifetime_adjustment => P_LOAN_REC.percent_increase_life
616 ,p_ceiling_rate => P_LOAN_REC.ceiling_rate
617 ,p_floor_rate => P_LOAN_REC.floor_rate
618 ,x_interest_rate => l_merged_rates_tbl(merged_count).CURRENT_INTEREST_RATE
619 ,x_adjustment_reason => l_adjustment_reason);
620 end if;
621
622 merged_count := merged_count + 1;
623
624 elsif (l_rate_sched_from < l_index_from) then
625
626 if l_rate_sched_to < l_index_from then
627 l_merged_rates_tbl(merged_count) := l_RATE_SCHEDS_TBL(rate_sched_count);
628 logMessage(FND_LOG.LEVEL_STATEMENT, 'if 41 - skipping');
629 l_merged_rates_tbl(merged_count).ACTION := 'SKIP';
630 rate_sched_count := rate_sched_count + 1;
631
632 elsif l_rate_sched_to >= l_index_from then
633
634 if l_RATE_SCHEDS_TBL(rate_sched_count).INDEX_RATE = l_index_rate and
635 l_rate_sched_to = l_index_to and
636 rate_sched_count < l_RATE_SCHEDS_TBL.count and
637 index_rate_count < l_ADJ_RATES_TBL.count
638 then
639
640 l_merged_rates_tbl(merged_count) := l_RATE_SCHEDS_TBL(rate_sched_count);
641 logMessage(FND_LOG.LEVEL_STATEMENT, 'if 42 - skipping');
642 l_merged_rates_tbl(merged_count).ACTION := 'SKIP';
643 rate_sched_count := rate_sched_count + 1;
644 index_rate_count := index_rate_count + 1;
645
646 else
647
648 l_do_insert := true;
649
650 if l_start_from_installment > 1 and l_update1 then
651
652 l_merged_rates_tbl(merged_count) := l_RATE_SCHEDS_TBL(rate_sched_count);
653 l_merged_rates_tbl(merged_count).ACTION := 'UPDATE';
654 l_prev_spead := l_merged_rates_tbl(merged_count).SPREAD;
655 l_prev_io := l_merged_rates_tbl(merged_count).INTEREST_ONLY_FLAG;
656 l_update1 := false;
657
658 if l_RATE_SCHEDS_TBL(rate_sched_count).INDEX_RATE = l_index_rate then
659 logMessage(FND_LOG.LEVEL_STATEMENT, 'if 43 - updating');
660 l_merged_rates_tbl(merged_count).END_INSTALLMENT_NUMBER := l_index_to;
661 l_do_insert := false;
662 index_rate_count := index_rate_count + 1;
663 else
664 logMessage(FND_LOG.LEVEL_STATEMENT, 'if 44 - updating');
665 l_merged_rates_tbl(merged_count).END_INSTALLMENT_NUMBER := l_index_from-1;
666 l_do_insert := true;
667 merged_count := merged_count + 1;
668 end if;
669
670 end if;
671
672 if l_do_insert then
673
674 l_merged_rates_tbl(merged_count).RATE_ID := null;
675 l_merged_rates_tbl(merged_count).TERM_ID := P_LOAN_REC.TERM_ID;
676 l_merged_rates_tbl(merged_count).BEGIN_INSTALLMENT_NUMBER := l_index_from;
677 l_merged_rates_tbl(merged_count).INDEX_RATE := l_index_rate;
678 l_merged_rates_tbl(merged_count).SPREAD := l_prev_spead;
679 l_merged_rates_tbl(merged_count).CURRENT_INTEREST_RATE := l_index_rate + nvl(l_merged_rates_tbl(merged_count).SPREAD, 0);
680 l_merged_rates_tbl(merged_count).INTEREST_ONLY_FLAG := l_prev_io;
681 l_merged_rates_tbl(merged_count).ACTION := 'INSERT';
682
683 if l_rate_sched_to > l_index_to then
684
685 logMessage(FND_LOG.LEVEL_STATEMENT, 'if 45 - inserting');
686 l_merged_rates_tbl(merged_count).END_INSTALLMENT_NUMBER := l_index_to;
687 index_rate_count := index_rate_count + 1;
688
689 elsif l_rate_sched_to < l_index_to then
690
691 logMessage(FND_LOG.LEVEL_STATEMENT, 'if 46 - inserting');
692 l_merged_rates_tbl(merged_count).END_INSTALLMENT_NUMBER := l_rate_sched_to;
693 rate_sched_count := rate_sched_count + 1;
694
695 else -- l_rate_sched_to = l_index_to
696
697 logMessage(FND_LOG.LEVEL_STATEMENT, 'if 47 - inserting');
698 l_merged_rates_tbl(merged_count).END_INSTALLMENT_NUMBER := l_rate_sched_to;
699 rate_sched_count := rate_sched_count + 1;
700 index_rate_count := index_rate_count + 1;
701
702 end if;
703
704 if merged_count > 1 then
705 l_last_period_rate := l_merged_rates_tbl(merged_count-1).CURRENT_INTEREST_RATE;
706 else
707 if l_last_period_rate is null then
708 l_last_period_rate := l_merged_rates_tbl(merged_count).CURRENT_INTEREST_RATE;
709 end if;
710 end if;
711
712 if l_merged_rates_tbl(merged_count).BEGIN_INSTALLMENT_NUMBER = 1 then
713 l_initial_int_rate := l_merged_rates_tbl(merged_count).CURRENT_INTEREST_RATE;
714 end if;
715
716 -- adjust rate based on loan rules
717 adjustInterestRate(p_initial_rate => l_initial_int_rate
718 ,p_last_period_rate => l_last_period_rate
719 ,p_max_period_adjustment => P_LOAN_REC.percent_increase
720 ,p_max_lifetime_adjustment => P_LOAN_REC.percent_increase_life
721 ,p_ceiling_rate => P_LOAN_REC.ceiling_rate
722 ,p_floor_rate => P_LOAN_REC.floor_rate
723 ,x_interest_rate => l_merged_rates_tbl(merged_count).CURRENT_INTEREST_RATE
724 ,x_adjustment_reason => l_adjustment_reason);
725
726 end if;
727
728 end if;
729
730 end if;
731
732 merged_count := merged_count + 1;
733
734 elsif (l_rate_sched_from > l_index_to) then
735
736 logMessage(FND_LOG.LEVEL_STATEMENT, 'if 5 - going to next index rate record');
737 index_rate_count := index_rate_count + 1;
738
739 end if;
740
741 elsif (l_rate_sched_from is null or l_rate_sched_to is null or
742 l_index_from is null or l_index_from is null)
743 then
744
745 logMessage(FND_LOG.LEVEL_STATEMENT, 'if 6 - exiting loop');
746 exit;
747
748 end if;
749
750 end loop;
751
752 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'New rate schedule:');
753 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'From_date To_Date From_Inst To_Inst Rate+Spread=Current_Rate IO Rate_sched_id');
754 LogMessage(FND_LOG.LEVEL_UNEXPECTED, '--------- ------- --------- ------- ------------------------ -- -------------');
755 for k in 1..l_merged_rates_tbl.count loop
756
757 if l_merged_rates_tbl(k).ACTION = 'UPDATE' then
758
759 update lns_rate_schedules
760 set index_rate = l_merged_rates_tbl(k).INDEX_RATE
761 ,current_interest_rate = l_merged_rates_tbl(k).CURRENT_INTEREST_RATE
762 ,end_installment_number = l_merged_rates_tbl(k).END_INSTALLMENT_NUMBER
763 ,last_update_date = sysdate
764 ,last_updated_by = LNS_UTILITY_PUB.last_updated_by
765 ,last_update_login = LNS_UTILITY_PUB.last_update_login
766 ,object_version_number = object_version_number + 1
767 where rate_id = l_merged_rates_tbl(k).RATE_ID;
768
769 elsif l_merged_rates_tbl(k).ACTION = 'INSERT' then
770
771 select LNS_RATE_SCHEDULES_S.NEXTVAL into l_merged_rates_tbl(k).RATE_ID from dual;
772
773 insert into lns_rate_schedules(
774 RATE_ID
775 ,TERM_ID
776 ,INDEX_RATE
777 ,SPREAD
778 ,CURRENT_INTEREST_RATE
779 ,START_DATE_ACTIVE
780 ,END_DATE_ACTIVE
781 ,CREATED_BY
782 ,CREATION_DATE
783 ,LAST_UPDATED_BY
784 ,LAST_UPDATE_DATE
785 ,LAST_UPDATE_LOGIN
786 ,OBJECT_VERSION_NUMBER
787 ,BEGIN_INSTALLMENT_NUMBER
788 ,END_INSTALLMENT_NUMBER
789 ,INTEREST_ONLY_FLAG
790 ,PHASE
791 )
792 VALUES
793 (l_merged_rates_tbl(k).RATE_ID
794 ,l_merged_rates_tbl(k).TERM_ID
795 ,l_merged_rates_tbl(k).INDEX_RATE
796 ,l_merged_rates_tbl(k).SPREAD
797 ,l_merged_rates_tbl(k).CURRENT_INTEREST_RATE
798 ,sysdate
799 ,null
800 ,LNS_UTILITY_PUB.created_by
801 ,sysdate
802 ,LNS_UTILITY_PUB.last_updated_by
803 ,sysdate
804 ,LNS_UTILITY_PUB.last_update_login
805 ,1
806 ,l_merged_rates_tbl(k).BEGIN_INSTALLMENT_NUMBER
807 ,l_merged_rates_tbl(k).END_INSTALLMENT_NUMBER
808 ,l_merged_rates_tbl(k).INTEREST_ONLY_FLAG
809 ,P_LOAN_REC.CURRENT_PHASE
810 );
811
812 end if;
813
814 LogMessage(FND_LOG.LEVEL_UNEXPECTED,
815 l_merged_rates_tbl(k).BEGIN_DATE || ' ' ||
816 l_merged_rates_tbl(k).END_DATE || ' ' ||
817 l_merged_rates_tbl(k).BEGIN_INSTALLMENT_NUMBER || ' ' ||
818 l_merged_rates_tbl(k).END_INSTALLMENT_NUMBER || ' ' ||
819 l_merged_rates_tbl(k).INDEX_RATE || ' + ' || l_merged_rates_tbl(k).SPREAD || ' = ' || l_merged_rates_tbl(k).CURRENT_INTEREST_RATE || ' ' ||
820 l_merged_rates_tbl(k).INTEREST_ONLY_FLAG || ' ' ||
821 l_merged_rates_tbl(k).RATE_ID || ' ' ||
822 l_merged_rates_tbl(k).ACTION );
823
824 end loop;
825
826 if P_COMMIT = FND_API.G_TRUE then
827 COMMIT WORK;
828 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Commited');
829 end if;
830
831 -- END OF BODY OF API
832 x_return_status := FND_API.G_RET_STS_SUCCESS;
833
834 -- Standard call to get message count and if count is 1, get message info
835 FND_MSG_PUB.Count_And_Get(
836 p_encoded => FND_API.G_FALSE,
837 p_count => x_msg_count,
838 p_data => x_msg_data);
839
840 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Successfully processed loan ' || P_LOAN_REC.LOAN_NUMBER);
841 LogMessage(FND_LOG.LEVEL_PROCEDURE, l_api_name || ' -');
842
843 EXCEPTION
844 WHEN FND_API.G_EXC_ERROR THEN
845 ROLLBACK TO PROCESS_SINGLE_LOAN;
846 x_return_status := FND_API.G_RET_STS_ERROR;
847 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
848 LogMessage(FND_LOG.LEVEL_ERROR, 'Rollbacked');
849 LogMessage(FND_LOG.LEVEL_ERROR, 'Failed to process loan ' || P_LOAN_REC.LOAN_NUMBER);
850
851 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
852 ROLLBACK TO PROCESS_SINGLE_LOAN;
853 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
854 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
855 LogMessage(FND_LOG.LEVEL_ERROR, 'Rollbacked');
856 LogMessage(FND_LOG.LEVEL_ERROR, 'Failed to process loan ' || P_LOAN_REC.LOAN_NUMBER);
857
858 WHEN OTHERS THEN
859 ROLLBACK TO PROCESS_SINGLE_LOAN;
860 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
861 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
862 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
863 END IF;
864 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
865 LogMessage(FND_LOG.LEVEL_ERROR, 'Rollbacked');
866 LogMessage(FND_LOG.LEVEL_ERROR, 'Failed to process loan ' || P_LOAN_REC.LOAN_NUMBER);
867
868 END;
869
870
871
872
873 /*========================================================================
874 | PUBLIC PROCEDURE UPDATE_FLOATING_RATE_LOANS
875 |
876 | DESCRIPTION
877 | This procedure gets called from CM to mass update index rate for floating loans.
878 | Concurrent Program Name: "LNS: Mass Update Floating Rate Loans"
879 |
880 | PSEUDO CODE/LOGIC
881 |
882 | PARAMETERS
883 | ERRBUF OUT Returns errors to CM
884 | RETCODE OUT Returns error code to CM
885 | INDEX_RATE_ID IN Inputs index rate type
886 | INTEREST_RATE_LINE_ID IN Inputs index rate
887 |
888 | KNOWN ISSUES
889 | None
890 |
891 | NOTES
892 |
893 | MODIFICATION HISTORY
894 | Date Author Description of Changes
895 | 07-SEP-2006 karamach Created
896 | 12-Mar-2008 scherkas Fix for bug 6849817: changed program logic to support multiple rate schedule rows
897 |
898 *=======================================================================*/
899 PROCEDURE UPDATE_FLOATING_RATE_LOANS(
900 ERRBUF OUT NOCOPY VARCHAR2,
901 RETCODE OUT NOCOPY VARCHAR2,
902 P_INDEX_RATE_ID IN NUMBER)
903 IS
904
905 /*-----------------------------------------------------------------------+
906 | Local Variable Declarations and initializations |
907 +-----------------------------------------------------------------------*/
908 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_FLOATING_RATE_LOANS';
909 l_msg_data VARCHAR2(32767);
910 l_msg_count number;
911 l_return boolean;
912 l_return_status varchar2(10);
913 l_Count number;
914 l_success_count number;
915 l_failure_count number;
916 l_setup_int_rate number;
917 l_setup_rate_name varchar2(50);
918 l_setup_rate_desc varchar2(250);
919 j number;
920
921 l_RATE_LINES_TBL RATE_LINES_TBL;
922 l_RATE_LINE_REC RATE_LINE_REC;
923 l_LOAN_REC LOAN_REC;
924
925 /*-----------------------------------------------------------------------+
926 | Cursor Declarations |
927 +-----------------------------------------------------------------------*/
928 CURSOR c_get_loan_info(indexRateId NUMBER) IS
929 SELECT loan.loan_id,
930 term.term_id,
931 loan.loan_number,
932 lns_billing_util_pub.last_payment_number(term.loan_id) last_billed_installment,
933 loan.loan_status,
934 loan.CURRENT_PHASE,
935 decode(loan.CURRENT_PHASE, 'TERM', term.percent_increase, 'OPEN', term.open_percent_increase),
936 decode(loan.CURRENT_PHASE, 'TERM', term.percent_increase_life, 'OPEN', term.open_percent_increase_life),
937 decode(loan.CURRENT_PHASE, 'TERM', term.floor_rate, 'OPEN', term.open_floor_rate),
938 decode(loan.CURRENT_PHASE, 'TERM', term.ceiling_rate, 'OPEN', term.open_ceiling_rate)
939 FROM lns_loan_headers loan,
940 lns_terms term
941 WHERE loan.loan_id = term.loan_id
942 AND term.rate_type = 'FLOATING'
943 AND loan.loan_status NOT IN ('PAIDOFF','REJECTED','DELETED')
944 AND nvl(indexRateId, term.index_rate_id) = term.index_rate_id;
945
946 CURSOR c_get_int_rates(P_INDEX_RATE_ID VARCHAR2) IS
947 SELECT hdr.interest_rate_id,
948 hdr.interest_rate_name,
949 hdr.interest_rate_description
950 FROM lns_int_rate_headers_vl hdr
951 WHERE (EXISTS
952 (SELECT null
953 FROM lns_loan_headers loan,
954 lns_terms term
955 WHERE loan.loan_id = term.loan_id
956 AND term.rate_type = 'FLOATING'
957 AND loan.loan_status NOT IN ('PAIDOFF','REJECTED','DELETED')
958 AND term.index_rate_id = hdr.interest_rate_id)
959 AND nvl(P_INDEX_RATE_ID, hdr.interest_rate_id) = hdr.interest_rate_id)
960 order by hdr.interest_rate_name;
961
962 CURSOR c_get_int_lines(indexRateId NUMBER) IS
963 SELECT interest_rate_line_id,
964 interest_rate_id,
965 interest_rate,
966 start_date_active,
967 end_date_active
968 FROM lns_int_rate_lines
969 WHERE interest_rate_id = indexRateId
970 order by start_date_active;
971
972 BEGIN
973
974 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
975
976 -- Standard start of API savepoint
977 SAVEPOINT UPDATE_FLOATING_RATE_LOANS_PVT;
978 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Established savepoint');
979
980 /* init variables */
981 l_Count := 0;
982 l_success_count := 0;
983 l_failure_count := 0;
984
985 LogMessage(FND_LOG.level_unexpected, 'Input Parameters:');
986 LogMessage(FND_LOG.level_unexpected, 'Index: ' || P_INDEX_RATE_ID);
987
988 --Obtain rate setup info based on user input parameters
989 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Searching for index rates...');
990 open c_get_int_rates(P_INDEX_RATE_ID);
991 LOOP
992
993 fetch c_get_int_rates into
994 l_setup_int_rate,
995 l_setup_rate_name,
996 l_setup_rate_desc;
997 exit when c_get_int_rates%NOTFOUND;
998
999 LogMessage(FND_LOG.level_unexpected, ' ');
1000 LogMessage(FND_LOG.level_unexpected, 'Index rate - ' || l_setup_rate_name || ' (' || l_setup_int_rate || ')');
1001
1002 j := 0;
1003 l_RATE_LINES_TBL.delete;
1004 open c_get_int_lines(l_setup_int_rate);
1005 LOOP
1006
1007 fetch c_get_int_lines into
1008 l_RATE_LINE_REC.INTEREST_RATE_LINE_ID,
1009 l_RATE_LINE_REC.INTEREST_RATE_ID,
1010 l_RATE_LINE_REC.INTEREST_RATE,
1011 l_RATE_LINE_REC.START_DATE_ACTIVE,
1012 l_RATE_LINE_REC.END_DATE_ACTIVE;
1013 exit when c_get_int_lines%NOTFOUND;
1014
1015 j := j + 1;
1016 l_RATE_LINES_TBL(j) := l_RATE_LINE_REC;
1017 LogMessage(FND_LOG.level_unexpected, l_RATE_LINES_TBL(j).START_DATE_ACTIVE || ' - ' || l_RATE_LINES_TBL(j).END_DATE_ACTIVE || ': ' || l_RATE_LINES_TBL(j).INTEREST_RATE || ' (id=' || l_RATE_LINES_TBL(j).INTEREST_RATE_LINE_ID || ')');
1018
1019 END LOOP;
1020 close c_get_int_lines;
1021
1022 open c_get_loan_info(l_setup_int_rate);
1023 LOOP
1024
1025 fetch c_get_loan_info into
1026 l_LOAN_REC.loan_id,
1027 l_LOAN_REC.term_id,
1028 l_LOAN_REC.loan_number,
1029 l_LOAN_REC.last_billed_installment,
1030 l_LOAN_REC.loan_status,
1031 l_LOAN_REC.CURRENT_PHASE,
1032 l_LOAN_REC.percent_increase,
1033 l_LOAN_REC.percent_increase_life,
1034 l_LOAN_REC.floor_rate,
1035 l_LOAN_REC.ceiling_rate;
1036 exit when c_get_loan_info%NOTFOUND;
1037
1038 l_Count := l_Count + 1;
1039
1040 PROCESS_SINGLE_LOAN(
1041 P_API_VERSION => 1.0,
1042 P_INIT_MSG_LIST => FND_API.G_FALSE,
1043 P_COMMIT => FND_API.G_TRUE,
1044 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1045 P_LOAN_REC => l_LOAN_REC,
1046 P_RATE_LINES_TBL => l_RATE_LINES_TBL,
1047 x_return_status => l_return_status,
1048 x_msg_count => l_msg_count,
1049 x_msg_data => l_msg_data);
1050
1051 if l_return_status = 'S' then
1052 l_success_count := l_success_count + 1;
1053 else
1054 l_failure_count := l_failure_count + 1;
1055 end if;
1056
1057 END LOOP;
1058 close c_get_loan_info;
1059
1060 END LOOP;
1061 close c_get_int_rates;
1062
1063 LogMessage(FND_LOG.LEVEL_STATEMENT, ' ');
1064 LogMessage(FND_LOG.level_unexpected, '----------------------------------');
1065 LogMessage(FND_LOG.level_unexpected, 'Total Processed: ' || l_Count || ' loan(s)');
1066 LogMessage(FND_LOG.level_unexpected, 'Failed: ' || l_failure_count || ' loan(s)');
1067 LogMessage(FND_LOG.level_unexpected, 'Succeeded: ' || l_success_count || ' loan(s)');
1068
1069 RETCODE := FND_API.G_RET_STS_SUCCESS;
1070 if l_Count = 0 then
1071 ERRBUF := 'No floating rate loans were found.';
1072 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
1073 status => 'WARNING',
1074 message => ERRBUF);
1075 LogMessage(FND_LOG.level_unexpected, ERRBUF);
1076 elsif l_failure_count > 0 then
1077 ERRBUF := 'Not all floating rate loans were updated successfully. Please review log file.';
1078 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
1079 status => 'WARNING',
1080 message => ERRBUF);
1081 LogMessage(FND_LOG.level_unexpected, ERRBUF);
1082 end if;
1083
1084 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1085
1086 EXCEPTION
1087 WHEN OTHERS THEN
1088 ERRBUF := 'Update of floating rate loans has failed. Please review log file.';
1089 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
1090 status => 'ERROR',
1091 message => ERRBUF);
1092 RETCODE := FND_API.G_RET_STS_ERROR;
1093 LogMessage(FND_LOG.level_unexpected, ERRBUF);
1094
1095 END UPDATE_FLOATING_RATE_LOANS;
1096
1097
1098
1099 -- This api updates floating rates for single loan
1100 PROCEDURE UPDATE_LOAN_FLOATING_RATE(
1101 P_API_VERSION IN NUMBER,
1102 P_INIT_MSG_LIST IN VARCHAR2,
1103 P_COMMIT IN VARCHAR2,
1104 P_VALIDATION_LEVEL IN NUMBER,
1105 P_LOAN_ID IN NUMBER,
1106 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1107 X_MSG_COUNT OUT NOCOPY NUMBER,
1108 X_MSG_DATA OUT NOCOPY VARCHAR2)
1109 IS
1110
1111 /*-----------------------------------------------------------------------+
1112 | Local Variable Declarations and initializations |
1113 +-----------------------------------------------------------------------*/
1114
1115 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_LOAN_FLOATING_RATE';
1116 l_api_version CONSTANT NUMBER := 1.0;
1117 l_msg_data VARCHAR2(32767);
1118 l_msg_count number;
1119 l_return_status varchar2(10);
1120 l_index_rate_id NUMBER;
1121 l_interest_rate_name VARCHAR2(30);
1122 j NUMBER;
1123
1124 l_RATE_LINES_TBL RATE_LINES_TBL;
1125 l_RATE_LINE_REC RATE_LINE_REC;
1126 l_LOAN_REC LOAN_REC;
1127
1128 /*-----------------------------------------------------------------------+
1129 | Cursor Declarations |
1130 +-----------------------------------------------------------------------*/
1131 CURSOR c_get_loan_info(p_loan_id NUMBER) IS
1132 SELECT loan.loan_id,
1133 term.term_id,
1134 loan.loan_number,
1135 lns_billing_util_pub.last_payment_number(term.loan_id) last_billed_installment,
1136 loan.loan_status,
1137 loan.CURRENT_PHASE,
1138 term.index_rate_id,
1139 hdr.interest_rate_name,
1140 decode(loan.CURRENT_PHASE, 'TERM', term.percent_increase, 'OPEN', term.open_percent_increase),
1141 decode(loan.CURRENT_PHASE, 'TERM', term.percent_increase_life, 'OPEN', term.open_percent_increase_life),
1142 decode(loan.CURRENT_PHASE, 'TERM', term.floor_rate, 'OPEN', term.open_floor_rate),
1143 decode(loan.CURRENT_PHASE, 'TERM', term.ceiling_rate, 'OPEN', term.open_ceiling_rate)
1144 FROM lns_loan_headers loan,
1145 lns_terms term,
1146 lns_int_rate_headers_vl hdr
1147 WHERE loan.loan_id = p_loan_id
1148 AND loan.loan_id = term.loan_id
1149 AND term.rate_type = 'FLOATING'
1150 AND loan.loan_status NOT IN ('PAIDOFF','REJECTED','DELETED')
1151 AND term.index_rate_id = hdr.interest_rate_id;
1152
1153 CURSOR c_get_int_lines(indexRateId NUMBER) IS
1154 SELECT interest_rate_line_id,
1155 interest_rate_id,
1156 interest_rate,
1157 start_date_active,
1158 end_date_active+1
1159 FROM lns_int_rate_lines
1160 WHERE interest_rate_id = indexRateId
1161 order by start_date_active;
1162
1163 BEGIN
1164 LogMessage(FND_LOG.level_unexpected, ' ');
1165 LogMessage(FND_LOG.LEVEL_PROCEDURE, l_api_name || ' +');
1166
1167 -- Standard start of API savepoint
1168 SAVEPOINT UPDATE_LOAN_FLOATING_RATE;
1169 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Established savepoint');
1170
1171 -- Standard call to check for call compatibility
1172 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1173 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1174 END IF;
1175
1176 -- Initialize message list if p_init_msg_list is set to TRUE
1177 IF FND_API.To_Boolean(p_init_msg_list) THEN
1178 FND_MSG_PUB.initialize;
1179 END IF;
1180
1181 -- Initialize API return status to success
1182 x_return_status := FND_API.G_RET_STS_SUCCESS;
1183
1184 -- START OF BODY OF API
1185
1186 LogMessage(FND_LOG.level_unexpected, 'Input Parameters:');
1187 LogMessage(FND_LOG.level_unexpected, 'P_LOAN_ID: ' || P_LOAN_ID);
1188
1189 open c_get_loan_info(P_LOAN_ID);
1190 fetch c_get_loan_info into
1191 l_LOAN_REC.loan_id,
1192 l_LOAN_REC.term_id,
1193 l_LOAN_REC.loan_number,
1194 l_LOAN_REC.last_billed_installment,
1195 l_LOAN_REC.loan_status,
1196 l_LOAN_REC.CURRENT_PHASE,
1197 l_index_rate_id,
1198 l_interest_rate_name,
1199 l_LOAN_REC.percent_increase,
1200 l_LOAN_REC.percent_increase_life,
1201 l_LOAN_REC.floor_rate,
1202 l_LOAN_REC.ceiling_rate;
1203 close c_get_loan_info;
1204
1205 j := 0;
1206 l_RATE_LINES_TBL.delete;
1207 LogMessage(FND_LOG.level_unexpected, 'Index rate ' || l_interest_rate_name || ':');
1208 open c_get_int_lines(l_index_rate_id);
1209 LOOP
1210
1211 fetch c_get_int_lines into
1212 l_RATE_LINE_REC.INTEREST_RATE_LINE_ID,
1213 l_RATE_LINE_REC.INTEREST_RATE_ID,
1214 l_RATE_LINE_REC.INTEREST_RATE,
1215 l_RATE_LINE_REC.START_DATE_ACTIVE,
1216 l_RATE_LINE_REC.END_DATE_ACTIVE;
1217 exit when c_get_int_lines%NOTFOUND;
1218
1219 j := j + 1;
1220 l_RATE_LINES_TBL(j) := l_RATE_LINE_REC;
1221 LogMessage(FND_LOG.level_unexpected, l_RATE_LINES_TBL(j).START_DATE_ACTIVE || ' - ' || l_RATE_LINES_TBL(j).END_DATE_ACTIVE || ': ' || l_RATE_LINES_TBL(j).INTEREST_RATE || ' (id=' || l_RATE_LINES_TBL(j).INTEREST_RATE_LINE_ID || ')');
1222
1223 END LOOP;
1224 close c_get_int_lines;
1225
1226 PROCESS_SINGLE_LOAN(
1227 P_API_VERSION => 1.0,
1228 P_INIT_MSG_LIST => FND_API.G_FALSE,
1229 P_COMMIT => FND_API.G_FALSE,
1230 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1231 P_LOAN_REC => l_LOAN_REC,
1232 P_RATE_LINES_TBL => l_RATE_LINES_TBL,
1233 x_return_status => l_return_status,
1234 x_msg_count => l_msg_count,
1235 x_msg_data => l_msg_data);
1236
1237 if l_return_status <> 'S' then
1238 RAISE FND_API.G_EXC_ERROR;
1239 end if;
1240
1241 if P_COMMIT = FND_API.G_TRUE then
1242 COMMIT WORK;
1243 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Commited');
1244 end if;
1245
1246 -- END OF BODY OF API
1247 x_return_status := FND_API.G_RET_STS_SUCCESS;
1248
1249 -- Standard call to get message count and if count is 1, get message info
1250 FND_MSG_PUB.Count_And_Get(
1251 p_encoded => FND_API.G_FALSE,
1252 p_count => x_msg_count,
1253 p_data => x_msg_data);
1254
1255 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Successfully processed loan ' || l_LOAN_REC.LOAN_NUMBER);
1256 LogMessage(FND_LOG.LEVEL_PROCEDURE, l_api_name || ' -');
1257
1258 EXCEPTION
1259 WHEN FND_API.G_EXC_ERROR THEN
1260 ROLLBACK TO UPDATE_LOAN_FLOATING_RATE;
1261 x_return_status := FND_API.G_RET_STS_ERROR;
1262 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1263 LogMessage(FND_LOG.LEVEL_ERROR, 'Rollbacked');
1264 LogMessage(FND_LOG.LEVEL_ERROR, 'Failed to process loan ' || l_LOAN_REC.LOAN_NUMBER);
1265
1266 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1267 ROLLBACK TO UPDATE_LOAN_FLOATING_RATE;
1268 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1269 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1270 LogMessage(FND_LOG.LEVEL_ERROR, 'Rollbacked');
1271 LogMessage(FND_LOG.LEVEL_ERROR, 'Failed to process loan ' || l_LOAN_REC.LOAN_NUMBER);
1272
1273 WHEN OTHERS THEN
1274 ROLLBACK TO UPDATE_LOAN_FLOATING_RATE;
1275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1276 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1277 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1278 END IF;
1279 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1280 LogMessage(FND_LOG.LEVEL_ERROR, 'Rollbacked');
1281 LogMessage(FND_LOG.LEVEL_ERROR, 'Failed to process loan ' || l_LOAN_REC.LOAN_NUMBER);
1282
1283 END;
1284
1285
1286 END LNS_INDEX_RATES_PUB;