[Home] [Help]
PACKAGE BODY: APPS.LNS_EXT_LOAN_PUB
Source
1 PACKAGE BODY LNS_EXT_LOAN_PUB as
2 /* $Header: LNS_EXT_LOAN_B.pls 120.0.12010000.1 2008/11/25 14:16:35 scherkas noship $ */
3
4
5 /*=======================================================================+
6 | Package Global Constants
7 +=======================================================================*/
8 G_PKG_NAME CONSTANT VARCHAR2(30):= 'LNS_EXT_LOAN_PUB';
9 G_LOG_ENABLED varchar2(5);
10 G_MSG_LEVEL NUMBER;
11
12
13 /*========================================================================
14 | PRIVATE PROCEDURE LogMessage
15 |
16 | DESCRIPTION
17 | This procedure logs debug messages to db and to CM log
18 |
19 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
20 | SAVE_LOAN_EXTENSION
21 | APPROVE_LOAN_EXTENSION
22 | REJECT_LOAN_EXTENSION
23 | CALC_NEW_TERMS
24 |
25 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
26 | None
27 |
28 | PARAMETERS
29 | p_msg_level IN Debug msg level
30 | p_msg IN Debug msg itself
31 |
32 | KNOWN ISSUES
33 | None
34 |
35 | NOTES
36 | Any interesting aspect of the code in the package body which needs
37 | to be stated.
38 |
39 | MODIFICATION HISTORY
40 | Date Author Description of Changes
41 | 09-25-2007 scherkas Created
42 |
43 *=======================================================================*/
44 Procedure LogMessage(p_msg_level IN NUMBER, p_msg in varchar2)
45 IS
46 BEGIN
47 if (p_msg_level >= G_MSG_LEVEL) then
48
49 FND_LOG.STRING(p_msg_level, G_PKG_NAME, p_msg);
50
51 end if;
52
53 EXCEPTION
54 WHEN OTHERS THEN
55 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
56 END;
57
58
59
60 /*========================================================================
61 | PRIVATE PROCEDURE VALIDATE_EXTN
62 |
63 | DESCRIPTION
64 | This procedure validates extension for different actions
65 |
66 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
67 | SAVE_LOAN_EXTENSION
68 | APPROVE_LOAN_EXTENSION
69 | REJECT_LOAN_EXTENSION
70 | CALC_NEW_TERMS
71 |
72 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
73 | None
74 |
75 | PARAMETERS
76 | P_LOAN_EXT_REC IN Extension record
77 | ACTION IN Action
78 |
79 | KNOWN ISSUES
80 | None
81 |
82 | NOTES
83 | Any interesting aspect of the code in the package body which needs
84 | to be stated.
85 |
86 | MODIFICATION HISTORY
87 | Date Author Description of Changes
88 | 09-27-2007 scherkas Created
89 |
90 *=======================================================================*/
91 Procedure VALIDATE_EXTN(P_LOAN_EXT_REC IN LNS_EXT_LOAN_PUB.LOAN_EXT_REC
92 ,P_ACTION IN VARCHAR2)
93 IS
94
95 /*-----------------------------------------------------------------------+
96 | Local Variable Declarations and initializations |
97 +-----------------------------------------------------------------------*/
98
99 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_EXTN';
100 l_extn_count NUMBER;
101 l_loan_status VARCHAR2(30);
102 l_extn_status VARCHAR2(30);
103 l_customized VARCHAR2(1);
104 l_phase VARCHAR2(30);
105
106 /*-----------------------------------------------------------------------+
107 | Cursor Declarations |
108 +-----------------------------------------------------------------------*/
109
110 CURSOR extn_count_cur(P_LOAN_ID number) IS
111 select count(1)
112 from lns_loan_extensions
113 where loan_id = P_LOAN_ID and
114 STATUS = 'PENDING';
115
116 CURSOR extn_details_cur(P_LOAN_EXT_ID number) IS
117 select STATUS
118 from lns_loan_extensions
119 where LOAN_EXT_ID = P_LOAN_EXT_ID;
120
121 CURSOR loan_details_cur(P_LOAN_ID number) IS
122 select LOAN_STATUS, nvl(custom_payments_flag, 'N'), nvl(CURRENT_PHASE, 'TERM')
123 from lns_loan_headers_all
124 where loan_id = P_LOAN_ID;
125
126 BEGIN
127
128 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
129
130 if (P_LOAN_EXT_REC.LOAN_ID is null or
131 P_LOAN_EXT_REC.EXT_TERM is null or
132 P_LOAN_EXT_REC.EXT_TERM_PERIOD is null or
133 P_LOAN_EXT_REC.EXT_BALLOON_TYPE is null or
134 (P_LOAN_EXT_REC.EXT_BALLOON_TYPE = 'TERM' and P_LOAN_EXT_REC.EXT_AMORT_TERM is null) or
135 (P_LOAN_EXT_REC.EXT_BALLOON_TYPE = 'AMOUNT' and P_LOAN_EXT_REC.EXT_BALLOON_AMOUNT is null)
136 )
137 then
138
139 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Not all required data is set.');
140 FND_MESSAGE.SET_NAME('LNS', 'LNS_EXTN_NO_REQ_PAR');
141 FND_MSG_PUB.Add;
142 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
143 RAISE FND_API.G_EXC_ERROR;
144
145 end if;
146
147 open loan_details_cur(P_LOAN_EXT_REC.LOAN_ID);
148 fetch loan_details_cur into l_loan_status, l_customized, l_phase;
149 close loan_details_cur;
150
151 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_loan_status: ' || l_loan_status);
152 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_customized: ' || l_customized);
153 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_phase: ' || l_phase);
154
155 if l_customized = 'N' and P_LOAN_EXT_REC.EXT_TERM > 0 then
156
157 if (P_LOAN_EXT_REC.EXT_RATE is null or
158 P_LOAN_EXT_REC.EXT_SPREAD is null or
159 P_LOAN_EXT_REC.EXT_IO_FLAG is null) then
160
161 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Not all required data is set.');
162 FND_MESSAGE.SET_NAME('LNS', 'LNS_EXTN_NO_REQ_PAR');
163 FND_MSG_PUB.Add;
164 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
165 RAISE FND_API.G_EXC_ERROR;
166
167 end if;
168
169 end if;
170
171 if (P_ACTION = 'INSERT') then
172
173 if (l_loan_status <> 'ACTIVE' and
174 l_loan_status <> 'APPROVED' and
175 l_loan_status <> 'DEFAULT' and
176 l_loan_status <> 'DELINQUENT' and
177 l_loan_status <> 'FUNDING_ERROR' and
178 l_loan_status <> 'IN_FUNDING') then
179
180 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Invalid loan status.');
181 FND_MESSAGE.SET_NAME('LNS', 'LNS_EXTN_INVAL_LN_STATUS');
182 FND_MSG_PUB.Add;
183 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
184 RAISE FND_API.G_EXC_ERROR;
185
186 end if;
187
188 open extn_count_cur(P_LOAN_EXT_REC.LOAN_ID);
189 fetch extn_count_cur into l_extn_count;
190 close extn_count_cur;
191
192 if (l_extn_count > 0) then
193
194 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: A pending extension for this loan already exists.');
195 FND_MESSAGE.SET_NAME('LNS', 'LNS_EXTN_ALREADY_EXTN');
196 FND_MSG_PUB.Add;
197 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
198 RAISE FND_API.G_EXC_ERROR;
199
200 end if;
201
202 elsif (P_ACTION = 'UPDATE') then
203
204 if (l_loan_status <> 'ACTIVE' and
205 l_loan_status <> 'APPROVED' and
206 l_loan_status <> 'DEFAULT' and
207 l_loan_status <> 'DELINQUENT' and
208 l_loan_status <> 'FUNDING_ERROR' and
209 l_loan_status <> 'IN_FUNDING') then
210
211 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Invalid loan status.');
212 FND_MESSAGE.SET_NAME('LNS', 'LNS_EXTN_INVAL_LN_STATUS');
213 FND_MSG_PUB.Add;
214 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
215 RAISE FND_API.G_EXC_ERROR;
216
217 end if;
218
219 open extn_details_cur(P_LOAN_EXT_REC.LOAN_EXT_ID);
220 fetch extn_details_cur into l_extn_status;
221 close extn_details_cur;
222
223 if (l_extn_status <> 'PENDING') then
224
225 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Invalid loan extension.');
226 FND_MESSAGE.SET_NAME('LNS', 'LNS_EXTN_INVAL_EXTN');
227 FND_MSG_PUB.Add;
228 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
229 RAISE FND_API.G_EXC_ERROR;
230
231 end if;
232
233 end if;
234
235 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
236
237 END;
238
239
240
241
242 /*========================================================================
243 | PRIVATE PROCEDURE VALIDATE_EXTN
244 |
245 | DESCRIPTION
246 | This procedure validates extension for different actions
247 |
248 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
249 | APPROVE_LOAN_EXTENSION
250 | REJECT_LOAN_EXTENSION
251 | CALC_NEW_TERMS
252 |
253 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
254 | None
255 |
256 | PARAMETERS
257 | P_LOAN_EXT_ID IN Extension ID
258 | ACTION IN Action
259 |
260 | KNOWN ISSUES
261 | None
262 |
263 | NOTES
264 | Any interesting aspect of the code in the package body which needs
265 | to be stated.
266 |
267 | MODIFICATION HISTORY
268 | Date Author Description of Changes
269 | 09-27-2007 scherkas Created
270 | 07-08-2008 scherkas Fix for bug 7238772
271 |
272 *=======================================================================*/
273 Procedure VALIDATE_EXTN(P_LOAN_EXT_ID IN NUMBER
274 ,P_ACTION IN VARCHAR2)
275 IS
276
277 /*-----------------------------------------------------------------------+
278 | Local Variable Declarations and initializations |
279 +-----------------------------------------------------------------------*/
280
281 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_EXTN';
282
283 l_LOAN_ID NUMBER;
284 l_OLD_INSTALLMENTS NUMBER;
285 l_NEW_TERM NUMBER;
286 l_NEW_TERM_PERIOD VARCHAR2(30);
287 l_NEW_AMORT_TERM NUMBER;
288 l_NEW_MATURITY_DATE DATE;
289 l_NEW_INSTALLMENTS NUMBER;
290 l_EXT_RATE NUMBER;
291 l_EXT_SPREAD NUMBER;
292 l_EXT_IO_FLAG VARCHAR2(1);
293 l_STATUS VARCHAR2(30);
294 l_loan_status VARCHAR2(30);
295 l_customized VARCHAR2(1);
296 l_NEW_BALLOON_TYPE VARCHAR2(30);
297 l_NEW_BALLOON_AMOUNT NUMBER;
298 l_phase VARCHAR2(30);
299
300 /*-----------------------------------------------------------------------+
301 | Cursor Declarations |
302 +-----------------------------------------------------------------------*/
303
304 CURSOR loan_ext_cur(P_LOAN_EXT_ID number) IS
305 select
306 ext.LOAN_ID,
307 ext.OLD_INSTALLMENTS,
308 ext.NEW_TERM,
309 ext.NEW_TERM_PERIOD,
310 ext.NEW_BALLOON_TYPE,
311 ext.NEW_BALLOON_AMOUNT,
312 ext.NEW_AMORT_TERM,
313 ext.NEW_MATURITY_DATE,
314 ext.NEW_INSTALLMENTS,
315 ext.EXT_RATE,
316 ext.EXT_SPREAD,
317 ext.EXT_IO_FLAG,
318 ext.STATUS
319 from lns_loan_extensions ext
320 where ext.LOAN_EXT_ID = P_LOAN_EXT_ID;
321
322 CURSOR loan_details_cur(P_LOAN_EXT_ID number) IS
323 select loan.LOAN_STATUS, nvl(loan.custom_payments_flag, 'N'), nvl(loan.CURRENT_PHASE, 'TERM')
324 from lns_loan_headers_all loan,
325 lns_loan_extensions ext
326 where ext.LOAN_EXT_ID = P_LOAN_EXT_ID and
327 ext.loan_id = loan.loan_id;
328
329 BEGIN
330
331 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
332
333 open loan_details_cur(P_LOAN_EXT_ID);
334 fetch loan_details_cur into l_loan_status, l_customized, l_phase;
335 close loan_details_cur;
336
337 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_loan_status: ' || l_loan_status);
338 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_customized: ' || l_customized);
339 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_phase: ' || l_phase);
340
341 open loan_ext_cur(P_LOAN_EXT_ID);
342 fetch loan_ext_cur
343 into l_LOAN_ID,
344 l_OLD_INSTALLMENTS,
345 l_NEW_TERM,
346 l_NEW_TERM_PERIOD,
347 l_NEW_BALLOON_TYPE,
348 l_NEW_BALLOON_AMOUNT,
349 l_NEW_AMORT_TERM,
350 l_NEW_MATURITY_DATE,
351 l_NEW_INSTALLMENTS,
352 l_EXT_RATE,
353 l_EXT_SPREAD,
354 l_EXT_IO_FLAG,
355 l_STATUS;
356 close loan_ext_cur;
357
358 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_LOAN_ID: ' || l_LOAN_ID);
359 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_OLD_INSTALLMENTS: ' || l_OLD_INSTALLMENTS);
360 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_NEW_TERM: ' || l_NEW_TERM);
361 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_NEW_TERM_PERIOD: ' || l_NEW_TERM_PERIOD);
362 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_NEW_BALLOON_TYPE: ' || l_NEW_BALLOON_TYPE);
363 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_NEW_BALLOON_AMOUNT: ' || l_NEW_BALLOON_AMOUNT);
364 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_NEW_AMORT_TERM: ' || l_NEW_AMORT_TERM);
365 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_NEW_MATURITY_DATE: ' || l_NEW_MATURITY_DATE);
366 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_NEW_INSTALLMENTS: ' || l_NEW_INSTALLMENTS);
367 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_EXT_RATE: ' || l_EXT_RATE);
368 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_EXT_SPREAD: ' || l_EXT_SPREAD);
369 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_EXT_IO_FLAG: ' || l_EXT_IO_FLAG);
370 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_STATUS: ' || l_STATUS);
371
372 if (P_ACTION = 'APPROVE') then
373
374 if (l_LOAN_ID is null or
375 l_OLD_INSTALLMENTS is null or
376 l_NEW_TERM is null or
377 l_NEW_TERM_PERIOD is null or
378 l_NEW_MATURITY_DATE is null or
379 l_NEW_INSTALLMENTS is null or
380 l_STATUS is null or
381 l_NEW_BALLOON_TYPE is null or
382 (l_NEW_BALLOON_TYPE = 'TERM' and l_NEW_AMORT_TERM is null) or
383 (l_NEW_BALLOON_TYPE = 'AMOUNT' and l_NEW_BALLOON_AMOUNT is null))
384 then
385
386 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Not all required data is set.');
387 FND_MESSAGE.SET_NAME('LNS', 'LNS_EXTN_NO_REQ_PAR');
388 FND_MSG_PUB.Add;
389 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
390 RAISE FND_API.G_EXC_ERROR;
391
392 end if;
393
394 if l_customized = 'N' and l_NEW_INSTALLMENTS > l_OLD_INSTALLMENTS then
395
396 if (l_EXT_RATE is null or
397 l_EXT_IO_FLAG is null or
398 l_EXT_SPREAD is null)
399 then
400
401 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Not all required data is set.');
402 FND_MESSAGE.SET_NAME('LNS', 'LNS_EXTN_NO_REQ_PAR');
403 FND_MSG_PUB.Add;
404 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
405 RAISE FND_API.G_EXC_ERROR;
406
407 end if;
408
409 end if;
410
411 if (l_loan_status <> 'ACTIVE' and
412 l_loan_status <> 'APPROVED' and
413 l_loan_status <> 'DEFAULT' and
414 l_loan_status <> 'DELINQUENT' and
415 l_loan_status <> 'FUNDING_ERROR' and
416 l_loan_status <> 'IN_FUNDING') then
417
418 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Invalid loan status.');
419 FND_MESSAGE.SET_NAME('LNS', 'LNS_EXTN_INVAL_LN_STATUS');
420 FND_MSG_PUB.Add;
421 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
422 RAISE FND_API.G_EXC_ERROR;
423
424 end if;
425
426 if (l_STATUS <> 'PENDING') then
427
428 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Invalid loan extension.');
429 FND_MESSAGE.SET_NAME('LNS', 'LNS_EXTN_INVAL_EXTN');
430 FND_MSG_PUB.Add;
431 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
432 RAISE FND_API.G_EXC_ERROR;
433
434 end if;
435
436 elsif (P_ACTION = 'REJECT') then
437
438 if (l_loan_status <> 'ACTIVE' and
439 l_loan_status <> 'APPROVED' and
440 l_loan_status <> 'DEFAULT' and
441 l_loan_status <> 'DELINQUENT' and
442 l_loan_status <> 'FUNDING_ERROR' and
443 l_loan_status <> 'IN_FUNDING') then
444
445 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Invalid loan status.');
446 FND_MESSAGE.SET_NAME('LNS', 'LNS_EXTN_INVAL_LN_STATUS');
447 FND_MSG_PUB.Add;
448 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
449 RAISE FND_API.G_EXC_ERROR;
450
451 end if;
452
453 if (l_STATUS <> 'PENDING') then
454
455 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Invalid loan extension.');
456 FND_MESSAGE.SET_NAME('LNS', 'LNS_EXTN_INVAL_EXTN');
457 FND_MSG_PUB.Add;
458 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
459 RAISE FND_API.G_EXC_ERROR;
460
461 end if;
462
463 end if;
464
465 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
466
467 END;
468
469
470
471
472 /*========================================================================
473 | PUBLIC PROCEDURE SAVE_LOAN_EXTENSION
474 |
475 | DESCRIPTION
476 | This procedure inserts/updates loan extension in lns_loan_extensions table
477 |
478 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
479 | None
480 |
481 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
482 | LogMessage
483 |
484 | PARAMETERS
485 | P_API_VERSION IN Standard in parameter
486 | P_INIT_MSG_LIST IN Standard in parameter
487 | P_COMMIT IN Standard in parameter
488 | P_VALIDATION_LEVEL IN Standard in parameter
489 | P_LOAN_EXT_REC IN OUT NOCOPY LNS_EXT_LOAN_PUB.LOAN_EXT_REC record
490 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
491 | X_MSG_COUNT OUT NOCOPY Standard out parameter
492 | X_MSG_DATA OUT NOCOPY Standard out parameter
493 |
494 | KNOWN ISSUES
495 | None
496 |
497 | NOTES
498 |
499 | MODIFICATION HISTORY
500 | Date Author Description of Changes
501 | 09-25-2007 scherkas Created
502 |
503 *=======================================================================*/
504 PROCEDURE SAVE_LOAN_EXTENSION(
505 P_API_VERSION IN NUMBER,
506 P_INIT_MSG_LIST IN VARCHAR2,
507 P_COMMIT IN VARCHAR2,
508 P_VALIDATION_LEVEL IN NUMBER,
509 P_LOAN_EXT_REC IN OUT NOCOPY LNS_EXT_LOAN_PUB.LOAN_EXT_REC,
510 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
511 X_MSG_COUNT OUT NOCOPY NUMBER,
512 X_MSG_DATA OUT NOCOPY VARCHAR2)
513 IS
514
515 /*-----------------------------------------------------------------------+
516 | Local Variable Declarations and initializations |
517 +-----------------------------------------------------------------------*/
518
519 l_api_name CONSTANT VARCHAR2(30) := 'SAVE_LOAN_EXTENSION';
520 l_api_version CONSTANT NUMBER := 1.0;
521 l_return_status VARCHAR2(1);
522 l_msg_count NUMBER;
523 l_msg_data VARCHAR2(32767);
524
525 l_NEW_TERM_REC LNS_EXT_LOAN_PUB.NEW_TERM_REC;
526 l_is_exist VARCHAR2(1) := 'N';
527
528 /*-----------------------------------------------------------------------+
529 | Cursor Declarations |
530 +-----------------------------------------------------------------------*/
531
532 BEGIN
533
534 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
535
536 -- Standard start of API savepoint
537 SAVEPOINT SAVE_LOAN_EXTENSION;
538 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Savepoint is established');
539
540 -- Standard call to check for call compatibility
541 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
542 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
543 END IF;
544
545 -- Initialize message list if p_init_msg_list is set to TRUE
546 IF FND_API.To_Boolean(p_init_msg_list) THEN
547 FND_MSG_PUB.initialize;
548 END IF;
549
550 -- Initialize API return status to success
551 l_return_status := FND_API.G_RET_STS_SUCCESS;
552
553 -- START OF BODY OF API
554
555 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Input:');
556 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_ID:' || P_LOAN_EXT_REC.LOAN_ID);
557 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_EXT_ID:' || P_LOAN_EXT_REC.LOAN_EXT_ID);
558 LogMessage(FND_LOG.LEVEL_STATEMENT, 'DESCRIPTION:' || P_LOAN_EXT_REC.DESCRIPTION);
559 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_TERM:' || P_LOAN_EXT_REC.EXT_TERM);
560 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_TERM_PERIOD:' || P_LOAN_EXT_REC.EXT_TERM_PERIOD);
561 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_BALLOON_TYPE:' || P_LOAN_EXT_REC.EXT_BALLOON_TYPE);
562 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_BALLOON_AMOUNT:' || P_LOAN_EXT_REC.EXT_BALLOON_AMOUNT);
563 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_AMORT_TERM:' || P_LOAN_EXT_REC.EXT_AMORT_TERM);
564 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_RATE:' || P_LOAN_EXT_REC.EXT_RATE);
565 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_SPREAD:' || P_LOAN_EXT_REC.EXT_SPREAD);
566 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_IO_FLAG:' || P_LOAN_EXT_REC.EXT_IO_FLAG);
567 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_FLOATING_FLAG:' || P_LOAN_EXT_REC.EXT_FLOATING_FLAG);
568 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_INDEX_DATE:' || P_LOAN_EXT_REC.EXT_INDEX_DATE);
569
570 if (P_LOAN_EXT_REC.LOAN_EXT_ID is null) then
571 VALIDATE_EXTN(P_LOAN_EXT_REC, 'INSERT');
572 else
573 VALIDATE_EXTN(P_LOAN_EXT_REC, 'UPDATE');
574 end if;
575
576 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling CALC_NEW_TERMS...');
577
578 l_NEW_TERM_REC.LOAN_ID := P_LOAN_EXT_REC.LOAN_ID;
579 l_NEW_TERM_REC.EXT_TERM := P_LOAN_EXT_REC.EXT_TERM;
580 l_NEW_TERM_REC.EXT_TERM_PERIOD := P_LOAN_EXT_REC.EXT_TERM_PERIOD;
581 l_NEW_TERM_REC.EXT_BALLOON_TYPE := P_LOAN_EXT_REC.EXT_BALLOON_TYPE;
582 l_NEW_TERM_REC.EXT_BALLOON_AMOUNT := P_LOAN_EXT_REC.EXT_BALLOON_AMOUNT;
583 l_NEW_TERM_REC.EXT_AMORT_TERM := P_LOAN_EXT_REC.EXT_AMORT_TERM;
584
585 CALC_NEW_TERMS(
586 P_API_VERSION => 1.0,
587 P_INIT_MSG_LIST => FND_API.G_TRUE,
588 P_COMMIT => FND_API.G_FALSE,
589 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
590 P_NEW_TERM_REC => l_NEW_TERM_REC,
591 X_RETURN_STATUS => l_return_status,
592 X_MSG_COUNT => l_msg_count,
593 X_MSG_DATA => l_msg_data);
594
595 if (l_return_status <> 'S') then
596 RAISE FND_API.G_EXC_ERROR;
597 end if;
598
599
600 BEGIN
601 SELECT
602 'Y' into l_is_exist
603 FROM
604 lns_loan_extensions
605 WHERE
606 loan_ext_id = P_LOAN_EXT_REC.LOAN_EXT_ID;
607
608 EXCEPTION
609 WHEN no_data_found THEN
610 l_is_exist := 'N';
611 END;
612
613 --if (P_LOAN_EXT_REC.LOAN_EXT_ID is null) then
614
615 if (l_is_exist <> 'Y') then
616
617 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting into lns_loan_extensions...');
618
619 if P_LOAN_EXT_REC.LOAN_EXT_ID is null then
620 select lns_loan_extensions_s.NEXTVAL into P_LOAN_EXT_REC.LOAN_EXT_ID from dual;
621 end if;
622
623 insert into lns_loan_extensions(
624 LOAN_EXT_ID,
625 LOAN_ID,
626 DESCRIPTION,
627 OLD_TERM,
628 OLD_TERM_PERIOD,
629 OLD_BALLOON_TYPE,
630 OLD_BALLOON_AMOUNT,
631 OLD_AMORT_TERM,
632 OLD_MATURITY_DATE,
633 OLD_INSTALLMENTS,
634 EXT_TERM,
635 EXT_TERM_PERIOD,
636 EXT_BALLOON_TYPE,
637 EXT_BALLOON_AMOUNT,
638 EXT_AMORT_TERM,
639 EXT_RATE,
640 EXT_SPREAD,
641 EXT_IO_FLAG,
642 EXT_FLOATING_FLAG,
643 EXT_INDEX_DATE,
644 NEW_TERM,
645 NEW_TERM_PERIOD,
646 NEW_BALLOON_TYPE,
647 NEW_BALLOON_AMOUNT,
648 NEW_AMORT_TERM,
649 NEW_MATURITY_DATE,
650 NEW_INSTALLMENTS,
651 STATUS,
652 APPR_REJECT_DATE,
653 APPR_REJECT_BY,
654 OBJECT_VERSION_NUMBER,
655 CREATION_DATE,
656 CREATED_BY,
657 LAST_UPDATE_DATE,
658 LAST_UPDATED_BY,
659 LAST_UPDATE_LOGIN)
660 values(
661 P_LOAN_EXT_REC.LOAN_EXT_ID,
662 P_LOAN_EXT_REC.LOAN_ID,
663 P_LOAN_EXT_REC.DESCRIPTION,
664 l_NEW_TERM_REC.OLD_TERM,
665 l_NEW_TERM_REC.OLD_TERM_PERIOD,
666 l_NEW_TERM_REC.OLD_BALLOON_TYPE,
667 l_NEW_TERM_REC.OLD_BALLOON_AMOUNT,
668 l_NEW_TERM_REC.OLD_AMORT_TERM,
669 l_NEW_TERM_REC.OLD_MATURITY_DATE,
670 l_NEW_TERM_REC.OLD_INSTALLMENTS,
671 P_LOAN_EXT_REC.EXT_TERM,
672 P_LOAN_EXT_REC.EXT_TERM_PERIOD,
673 P_LOAN_EXT_REC.EXT_BALLOON_TYPE,
674 P_LOAN_EXT_REC.EXT_BALLOON_AMOUNT,
675 P_LOAN_EXT_REC.EXT_AMORT_TERM,
676 P_LOAN_EXT_REC.EXT_RATE,
677 P_LOAN_EXT_REC.EXT_SPREAD,
678 P_LOAN_EXT_REC.EXT_IO_FLAG,
679 P_LOAN_EXT_REC.EXT_FLOATING_FLAG,
680 P_LOAN_EXT_REC.EXT_INDEX_DATE,
681 l_NEW_TERM_REC.NEW_TERM,
682 l_NEW_TERM_REC.NEW_TERM_PERIOD,
683 l_NEW_TERM_REC.NEW_BALLOON_TYPE,
684 l_NEW_TERM_REC.NEW_BALLOON_AMOUNT,
685 l_NEW_TERM_REC.NEW_AMORT_TERM,
686 l_NEW_TERM_REC.NEW_MATURITY_DATE,
687 l_NEW_TERM_REC.NEW_INSTALLMENTS,
688 'PENDING',
689 null,
690 null,
691 1,
692 sysdate,
693 LNS_UTILITY_PUB.CREATED_BY,
694 sysdate,
695 LNS_UTILITY_PUB.LAST_UPDATED_BY,
696 LNS_UTILITY_PUB.LAST_UPDATE_LOGIN);
697
698 else
699
700 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Updating lns_loan_extensions...');
701
702 update LNS_LOAN_EXTENSIONS set
703 DESCRIPTION = P_LOAN_EXT_REC.DESCRIPTION,
704 OLD_TERM = l_NEW_TERM_REC.OLD_TERM,
705 OLD_TERM_PERIOD = l_NEW_TERM_REC.OLD_TERM_PERIOD,
706 OLD_BALLOON_TYPE = l_NEW_TERM_REC.OLD_BALLOON_TYPE,
707 OLD_BALLOON_AMOUNT = l_NEW_TERM_REC.OLD_BALLOON_AMOUNT,
708 OLD_AMORT_TERM = l_NEW_TERM_REC.OLD_AMORT_TERM,
709 OLD_MATURITY_DATE = l_NEW_TERM_REC.OLD_MATURITY_DATE,
710 OLD_INSTALLMENTS = l_NEW_TERM_REC.OLD_INSTALLMENTS,
711 EXT_TERM = P_LOAN_EXT_REC.EXT_TERM,
712 EXT_TERM_PERIOD = P_LOAN_EXT_REC.EXT_TERM_PERIOD,
713 EXT_BALLOON_TYPE = P_LOAN_EXT_REC.EXT_BALLOON_TYPE,
714 EXT_BALLOON_AMOUNT = P_LOAN_EXT_REC.EXT_BALLOON_AMOUNT,
715 EXT_AMORT_TERM = P_LOAN_EXT_REC.EXT_AMORT_TERM,
716 EXT_RATE = P_LOAN_EXT_REC.EXT_RATE,
717 EXT_SPREAD = P_LOAN_EXT_REC.EXT_SPREAD,
718 EXT_IO_FLAG = P_LOAN_EXT_REC.EXT_IO_FLAG,
719 EXT_FLOATING_FLAG = P_LOAN_EXT_REC.EXT_FLOATING_FLAG,
720 EXT_INDEX_DATE = P_LOAN_EXT_REC.EXT_INDEX_DATE,
721 NEW_TERM = l_NEW_TERM_REC.NEW_TERM,
722 NEW_TERM_PERIOD = l_NEW_TERM_REC.NEW_TERM_PERIOD,
723 NEW_BALLOON_TYPE = l_NEW_TERM_REC.NEW_BALLOON_TYPE,
724 NEW_BALLOON_AMOUNT = l_NEW_TERM_REC.NEW_BALLOON_AMOUNT,
725 NEW_AMORT_TERM = l_NEW_TERM_REC.NEW_AMORT_TERM,
726 NEW_MATURITY_DATE = l_NEW_TERM_REC.NEW_MATURITY_DATE,
727 NEW_INSTALLMENTS = l_NEW_TERM_REC.NEW_INSTALLMENTS,
728 LAST_UPDATE_DATE = sysdate,
729 LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY,
730 LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
731 where LOAN_EXT_ID = P_LOAN_EXT_REC.LOAN_EXT_ID;
732
733 end if;
734
735 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done');
736
737 -- END OF BODY OF API
738
739 if P_COMMIT = FND_API.G_TRUE then
740 COMMIT WORK;
741 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Commited');
742 end if;
743
744 x_return_status := FND_API.G_RET_STS_SUCCESS;
745
746 -- Standard call to get message count and if count is 1, get message info
747 FND_MSG_PUB.Count_And_Get(
748 p_encoded => FND_API.G_FALSE,
749 p_count => x_msg_count,
750 p_data => x_msg_data);
751
752 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
753
754 EXCEPTION
755 WHEN FND_API.G_EXC_ERROR THEN
756 ROLLBACK TO SAVE_LOAN_EXTENSION;
757 x_return_status := FND_API.G_RET_STS_ERROR;
758 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
759 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
760 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
761 ROLLBACK TO SAVE_LOAN_EXTENSION;
762 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
763 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
764 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
765 WHEN OTHERS THEN
766 ROLLBACK TO SAVE_LOAN_EXTENSION;
767 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
768 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
769 FND_MSG_PUB.Add_Exc_Msg(l_api_name);
770 END IF;
771 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
772 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
773 END;
774
775
776
777
778 /*========================================================================
779 | PUBLIC PROCEDURE APPROVE_LOAN_EXTENSION
780 |
781 | DESCRIPTION
782 | This procedure approves loan extension and updates loan term data in
783 | lns_loan_headers_all from lns_loan_extensions table
784 |
785 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
786 | None
787 |
788 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
789 | LogMessage
790 |
791 | PARAMETERS
792 | P_API_VERSION IN Standard in parameter
793 | P_INIT_MSG_LIST IN Standard in parameter
794 | P_COMMIT IN Standard in parameter
795 | P_VALIDATION_LEVEL IN Standard in parameter
796 | P_LOAN_EXT_ID IN Loan extension ID
797 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
798 | X_MSG_COUNT OUT NOCOPY Standard out parameter
799 | X_MSG_DATA OUT NOCOPY Standard out parameter
800 |
801 | KNOWN ISSUES
802 | None
803 |
804 | NOTES
805 |
806 | MODIFICATION HISTORY
807 | Date Author Description of Changes
808 | 09-25-2007 scherkas Created
809 |
810 *=======================================================================*/
811 PROCEDURE APPROVE_LOAN_EXTENSION(
812 P_API_VERSION IN NUMBER,
813 P_INIT_MSG_LIST IN VARCHAR2,
814 P_COMMIT IN VARCHAR2,
815 P_VALIDATION_LEVEL IN NUMBER,
816 P_LOAN_EXT_ID IN NUMBER,
817 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
818 X_MSG_COUNT OUT NOCOPY NUMBER,
819 X_MSG_DATA OUT NOCOPY VARCHAR2)
820 IS
821
822 /*-----------------------------------------------------------------------+
823 | Local Variable Declarations and initializations |
824 +-----------------------------------------------------------------------*/
825
826 l_api_name CONSTANT VARCHAR2(30) := 'APPROVE_LOAN_EXTENSION';
827 l_api_version CONSTANT NUMBER := 1.0;
828 l_return_status VARCHAR2(1);
829 l_msg_count NUMBER;
830 l_msg_data VARCHAR2(32767);
831
832 l_LOAN_ID NUMBER;
833 l_OLD_INSTALLMENTS NUMBER;
834 l_NEW_TERM NUMBER;
835 l_NEW_TERM_PERIOD VARCHAR2(30);
836 l_NEW_AMORT_TERM NUMBER;
837 l_NEW_MATURITY_DATE DATE;
838 l_NEW_INSTALLMENTS NUMBER;
839 l_OBJECT_VERSION_NUMBER NUMBER;
840 l_EXT_RATE NUMBER;
841 l_EXT_SPREAD NUMBER;
842 l_EXT_IO_FLAG VARCHAR2(1);
843 l_EXT_INDEX_DATE DATE;
844 l_TERM_ID NUMBER;
845 l_REQUEST_ID number;
846 l_last_billed_installment number;
847 l_customized VARCHAR2(1);
848 l_NEW_BALLOON_TYPE VARCHAR2(30);
849 l_NEW_BALLOON_AMOUNT NUMBER;
850 l_EXT_FLOATING_FLAG VARCHAR2(1);
851
852 l_RATE_ID number;
853 l_RATE number;
854 l_BEGIN_INSTALLMENT number;
855 l_END_INSTALLMENT number;
856 l_INDEX_RATE number;
857 l_SPREAD number;
858 l_INTEREST_ONLY_FLAG VARCHAR2(1);
859 l_FLOATING_FLAG VARCHAR2(1);
860 i number;
861
862 l_loan_header_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type;
863
864 /*-----------------------------------------------------------------------+
865 | Cursor Declarations |
866 +-----------------------------------------------------------------------*/
867
868 -- query loan extension details
869 CURSOR loan_ext_cur(P_LOAN_EXT_ID number) IS
870 select
871 ext.LOAN_ID,
872 ext.OLD_INSTALLMENTS,
873 ext.NEW_TERM,
874 ext.NEW_TERM_PERIOD,
875 ext.NEW_BALLOON_TYPE,
876 ext.NEW_BALLOON_AMOUNT,
877 ext.NEW_AMORT_TERM,
878 ext.NEW_MATURITY_DATE,
879 ext.NEW_INSTALLMENTS,
880 ext.EXT_RATE,
881 ext.EXT_SPREAD,
882 ext.EXT_IO_FLAG,
883 ext.EXT_FLOATING_FLAG,
884 ext.EXT_INDEX_DATE,
885 loan.OBJECT_VERSION_NUMBER,
886 term.term_id,
887 nvl(loan.custom_payments_flag, 'N')
888 from lns_loan_extensions ext,
889 lns_loan_headers_all loan,
890 lns_terms term
891 where ext.LOAN_EXT_ID = P_LOAN_EXT_ID and
892 loan.LOAN_ID = ext.LOAN_ID and
893 term.loan_id = loan.LOAN_ID;
894
895 -- cursor to load rate schedule
896 cursor c_rate_sched(p_term_id NUMBER) IS
897 select RATE_ID,
898 CURRENT_INTEREST_RATE,
899 BEGIN_INSTALLMENT_NUMBER,
900 END_INSTALLMENT_NUMBER,
901 INDEX_RATE,
902 SPREAD,
903 INTEREST_ONLY_FLAG,
904 FLOATING_FLAG
905 from lns_rate_schedules
906 where term_id = p_term_id and
907 END_DATE_ACTIVE is null and
908 phase = 'TERM'
909 order by END_INSTALLMENT_NUMBER desc;
910
911 BEGIN
912
913 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
914
915 -- Standard start of API savepoint
916 SAVEPOINT APPROVE_LOAN_EXTENSION;
917 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Savepoint is established');
918
919 -- Standard call to check for call compatibility
920 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
921 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
922 END IF;
923
924 -- Initialize message list if p_init_msg_list is set to TRUE
925 --IF FND_API.To_Boolean(p_init_msg_list) THEN
926 -- FND_MSG_PUB.initialize;
927 --END IF;
928
929 -- Initialize API return status to success
930 l_return_status := FND_API.G_RET_STS_SUCCESS;
931
932 -- START OF BODY OF API
933
934 VALIDATE_EXTN(P_LOAN_EXT_ID, 'APPROVE');
935
936 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Querying extension details...');
937
938 open loan_ext_cur(P_LOAN_EXT_ID);
939 fetch loan_ext_cur
940 into l_LOAN_ID,
941 l_OLD_INSTALLMENTS,
942 l_NEW_TERM,
943 l_NEW_TERM_PERIOD,
944 l_NEW_BALLOON_TYPE,
945 l_NEW_BALLOON_AMOUNT,
946 l_NEW_AMORT_TERM,
947 l_NEW_MATURITY_DATE,
948 l_NEW_INSTALLMENTS,
949 l_EXT_RATE,
950 l_EXT_SPREAD,
951 l_EXT_IO_FLAG,
952 l_EXT_FLOATING_FLAG,
953 l_EXT_INDEX_DATE,
954 l_OBJECT_VERSION_NUMBER,
955 l_TERM_ID,
956 l_customized;
957 close loan_ext_cur;
958
959 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_ID: ' || l_LOAN_ID);
960 LogMessage(FND_LOG.LEVEL_STATEMENT, 'OLD_INSTALLMENTS: ' || l_OLD_INSTALLMENTS);
961 LogMessage(FND_LOG.LEVEL_STATEMENT, 'NEW_TERM: ' || l_NEW_TERM);
962 LogMessage(FND_LOG.LEVEL_STATEMENT, 'NEW_TERM_PERIOD: ' || l_NEW_TERM_PERIOD);
963 LogMessage(FND_LOG.LEVEL_STATEMENT, 'NEW_BALLOON_TYPE: ' || l_NEW_BALLOON_TYPE);
964 LogMessage(FND_LOG.LEVEL_STATEMENT, 'NEW_BALLOON_AMOUNT: ' || l_NEW_BALLOON_AMOUNT);
965 LogMessage(FND_LOG.LEVEL_STATEMENT, 'NEW_AMORT_TERM: ' || l_NEW_AMORT_TERM);
966 LogMessage(FND_LOG.LEVEL_STATEMENT, 'NEW_MATURITY_DATE: ' || l_NEW_MATURITY_DATE);
967 LogMessage(FND_LOG.LEVEL_STATEMENT, 'NEW_INSTALLMENTS: ' || l_NEW_INSTALLMENTS);
968 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_RATE: ' || l_EXT_RATE);
969 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_SPREAD: ' || l_EXT_SPREAD);
970 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_IO_FLAG: ' || l_EXT_IO_FLAG);
971 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_FLOATING_FLAG: ' || l_EXT_FLOATING_FLAG);
972 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_INDEX_DATE: ' || l_EXT_INDEX_DATE);
973 LogMessage(FND_LOG.LEVEL_STATEMENT, 'OBJECT_VERSION_NUMBER: ' || l_OBJECT_VERSION_NUMBER);
974 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_TERM_ID: ' || l_TERM_ID);
975 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_customized: ' || l_customized);
976
977 l_loan_header_rec.loan_id := l_LOAN_ID;
978 l_loan_header_rec.loan_term := l_NEW_TERM;
979 l_loan_header_rec.LOAN_TERM_PERIOD := l_NEW_TERM_PERIOD;
980 l_loan_header_rec.BALLOON_PAYMENT_TYPE := l_NEW_BALLOON_TYPE;
981 l_loan_header_rec.BALLOON_PAYMENT_AMOUNT := l_NEW_BALLOON_AMOUNT;
982 l_loan_header_rec.AMORTIZED_TERM := l_NEW_AMORT_TERM;
983 l_loan_header_rec.AMORTIZED_TERM_PERIOD := l_NEW_TERM_PERIOD;
984 l_loan_header_rec.LOAN_MATURITY_DATE := l_NEW_MATURITY_DATE;
985
986 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Updating loan...');
987 LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER,
988 P_LOAN_HEADER_REC => l_loan_header_rec,
989 P_INIT_MSG_LIST => FND_API.G_FALSE,
990 X_RETURN_STATUS => l_return_status,
991 X_MSG_COUNT => l_msg_count,
992 X_MSG_DATA => l_msg_data);
993 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done');
994
995 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Synching rate schedule...');
996
997 -- finding right rate row and update it
998 i := 0;
999 OPEN c_rate_sched(l_TERM_ID);
1000 LOOP
1001 i := i + 1;
1002 FETCH c_rate_sched INTO
1003 l_RATE_ID,
1004 l_RATE,
1005 l_BEGIN_INSTALLMENT,
1006 l_END_INSTALLMENT,
1007 l_INDEX_RATE,
1008 l_SPREAD,
1009 l_INTEREST_ONLY_FLAG,
1010 l_FLOATING_FLAG;
1011
1012 LogMessage(FND_LOG.LEVEL_STATEMENT, i || ') Rate ' || l_RATE || ': ' || l_BEGIN_INSTALLMENT || ' - ' || l_END_INSTALLMENT);
1013 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_INDEX_RATE = ' || l_INDEX_RATE);
1014 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_SPREAD = ' || l_SPREAD);
1015 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_INTEREST_ONLY_FLAG = ' || l_INTEREST_ONLY_FLAG);
1016 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_FLOATING_FLAG = ' || l_FLOATING_FLAG);
1017
1018 if l_NEW_INSTALLMENTS > l_END_INSTALLMENT then
1019
1020 if (l_INDEX_RATE = l_EXT_RATE and
1021 l_SPREAD = l_EXT_SPREAD and
1022 l_INTEREST_ONLY_FLAG = l_EXT_IO_FLAG and
1023 nvl(l_FLOATING_FLAG, 'N') = nvl(l_EXT_FLOATING_FLAG, 'N'))
1024 then
1025
1026 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Updating this row - set END_INSTALLMENT_NUMBER = ' || l_NEW_INSTALLMENTS);
1027
1028 update lns_rate_schedules
1029 set END_INSTALLMENT_NUMBER = l_NEW_INSTALLMENTS
1030 where term_id = l_TERM_ID and
1031 RATE_ID = l_RATE_ID;
1032
1033 else
1034
1035 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting into LNS_RATE_SCHEDULES...');
1036
1037 insert into LNS_RATE_SCHEDULES
1038 (RATE_ID
1039 ,TERM_ID
1040 ,INDEX_RATE
1041 ,SPREAD
1042 ,CURRENT_INTEREST_RATE
1043 ,START_DATE_ACTIVE
1044 ,END_DATE_ACTIVE
1045 ,CREATED_BY
1046 ,CREATION_DATE
1047 ,LAST_UPDATED_BY
1048 ,LAST_UPDATE_DATE
1049 ,LAST_UPDATE_LOGIN
1050 ,OBJECT_VERSION_NUMBER
1051 ,INDEX_DATE
1052 ,BEGIN_INSTALLMENT_NUMBER
1053 ,END_INSTALLMENT_NUMBER
1054 ,INTEREST_ONLY_FLAG
1055 ,FLOATING_FLAG
1056 ,PHASE)
1057 VALUES
1058 (LNS_RATE_SCHEDULES_S.nextval
1059 ,l_TERM_ID
1060 ,l_EXT_RATE
1061 ,l_EXT_SPREAD
1062 ,(l_EXT_RATE+l_EXT_SPREAD)
1063 ,sysdate
1064 ,null
1065 ,lns_utility_pub.created_by
1066 ,sysdate
1067 ,lns_utility_pub.last_updated_by
1068 ,sysdate
1069 ,lns_utility_pub.LAST_UPDATE_LOGIN
1070 ,1
1071 ,l_EXT_INDEX_DATE
1072 ,l_END_INSTALLMENT+1
1073 ,l_NEW_INSTALLMENTS
1074 ,l_EXT_IO_FLAG
1075 ,l_EXT_FLOATING_FLAG
1076 ,'TERM');
1077
1078 end if;
1079
1080 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done');
1081 exit;
1082
1083 elsif l_NEW_INSTALLMENTS >= l_BEGIN_INSTALLMENT and l_NEW_INSTALLMENTS <= l_END_INSTALLMENT then
1084
1085 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Updating this row - set END_INSTALLMENT_NUMBER = ' || l_NEW_INSTALLMENTS);
1086
1087 update lns_rate_schedules
1088 set END_INSTALLMENT_NUMBER = l_NEW_INSTALLMENTS
1089 where term_id = l_TERM_ID and
1090 RATE_ID = l_RATE_ID;
1091
1092 exit;
1093
1094 elsif l_NEW_INSTALLMENTS < l_BEGIN_INSTALLMENT then
1095
1096 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Deleting this row');
1097
1098 delete from lns_rate_schedules
1099 where term_id = l_TERM_ID and
1100 RATE_ID = l_RATE_ID;
1101
1102 end if;
1103
1104 END LOOP;
1105
1106 CLOSE c_rate_sched;
1107 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done synching');
1108
1109 if l_customized = 'Y' then
1110
1111 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Deleting from LNS_CUSTOM_PAYMNT_SCHEDS rows with DUE_DATE < l_NEW_MATURITY_DATE...');
1112 delete from LNS_CUSTOM_PAYMNT_SCHEDS
1113 where loan_id = l_LOAN_ID
1114 and DUE_DATE > l_NEW_MATURITY_DATE;
1115 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done');
1116
1117 end if;
1118
1119 -- fix for bug 6724561
1120 l_last_billed_installment := LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(l_LOAN_ID);
1121 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_last_billed_installment: ' || l_last_billed_installment);
1122
1123 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Updating LNS_LOAN_EXTENSIONS...');
1124
1125 update LNS_LOAN_EXTENSIONS
1126 set STATUS = 'APPROVED',
1127 APPR_REJECT_DATE = sysdate,
1128 APPR_REJECT_BY = LNS_UTILITY_PUB.USER_ID,
1129 LAST_UPDATE_DATE = sysdate,
1130 LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY,
1131 LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN,
1132 LAST_BILLED_INSTALLMENT = l_last_billed_installment
1133 where LOAN_EXT_ID = P_LOAN_EXT_ID;
1134
1135 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done');
1136
1137 -- END OF BODY OF API
1138
1139 /*
1140 -- fix for the bug 6724522: there is no need to regenerate agreement report because
1141 -- even if loan is extended the original amortization will be the same as during loan origination
1142
1143 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Submitting Loan Agreement Report cp...');
1144 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
1145 'LNS',
1146 'LNS_AGREEMENT',
1147 '', '', FALSE,
1148 l_LOAN_ID);
1149
1150 if l_request_id = 0 then
1151 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed');
1152 else
1153 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Success. Request id: ' || l_request_id);
1154 end if;
1155 */
1156
1157 if P_COMMIT = FND_API.G_TRUE then
1158 COMMIT WORK;
1159 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Commited');
1160 end if;
1161
1162 x_return_status := FND_API.G_RET_STS_SUCCESS;
1163
1164 -- Standard call to get message count and if count is 1, get message info
1165 FND_MSG_PUB.Count_And_Get(
1166 p_encoded => FND_API.G_FALSE,
1167 p_count => x_msg_count,
1168 p_data => x_msg_data);
1169
1170 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1171
1172 EXCEPTION
1173 WHEN FND_API.G_EXC_ERROR THEN
1174 ROLLBACK TO APPROVE_LOAN_EXTENSION;
1175 x_return_status := FND_API.G_RET_STS_ERROR;
1176 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1177 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
1178 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1179 ROLLBACK TO APPROVE_LOAN_EXTENSION;
1180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1181 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1182 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
1183 WHEN OTHERS THEN
1184 ROLLBACK TO APPROVE_LOAN_EXTENSION;
1185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1186 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1187 FND_MSG_PUB.Add_Exc_Msg(l_api_name);
1188 END IF;
1189 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1190 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
1191 END;
1192
1193
1194
1195
1196 /*========================================================================
1197 | PUBLIC PROCEDURE REJECT_LOAN_EXTENSION
1198 |
1199 | DESCRIPTION
1200 | This procedure rejects loan extension. No changes is made in lns_loan_headers_all table
1201 |
1202 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1203 | None
1204 |
1205 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1206 | LogMessage
1207 |
1208 | PARAMETERS
1209 | P_API_VERSION IN Standard in parameter
1210 | P_INIT_MSG_LIST IN Standard in parameter
1211 | P_COMMIT IN Standard in parameter
1212 | P_VALIDATION_LEVEL IN Standard in parameter
1213 | P_LOAN_EXT_ID IN Loan extension ID
1214 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
1215 | X_MSG_COUNT OUT NOCOPY Standard out parameter
1216 | X_MSG_DATA OUT NOCOPY Standard out parameter
1217 |
1218 | KNOWN ISSUES
1219 | None
1220 |
1221 | NOTES
1222 |
1223 | MODIFICATION HISTORY
1224 | Date Author Description of Changes
1225 | 09-25-2007 scherkas Created
1226 |
1227 *=======================================================================*/
1228 PROCEDURE REJECT_LOAN_EXTENSION(
1229 P_API_VERSION IN NUMBER,
1230 P_INIT_MSG_LIST IN VARCHAR2,
1231 P_COMMIT IN VARCHAR2,
1232 P_VALIDATION_LEVEL IN NUMBER,
1233 P_LOAN_EXT_ID IN NUMBER,
1234 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1235 X_MSG_COUNT OUT NOCOPY NUMBER,
1236 X_MSG_DATA OUT NOCOPY VARCHAR2)
1237 IS
1238
1239 /*-----------------------------------------------------------------------+
1240 | Local Variable Declarations and initializations |
1241 +-----------------------------------------------------------------------*/
1242
1243 l_api_name CONSTANT VARCHAR2(30) := 'REJECT_LOAN_EXTENSION';
1244 l_api_version CONSTANT NUMBER := 1.0;
1245 l_return_status VARCHAR2(1);
1246 l_msg_count NUMBER;
1247 l_msg_data VARCHAR2(32767);
1248
1249 /*-----------------------------------------------------------------------+
1250 | Cursor Declarations |
1251 +-----------------------------------------------------------------------*/
1252
1253 BEGIN
1254
1255 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1256
1257 -- Standard start of API savepoint
1258 SAVEPOINT REJECT_LOAN_EXTENSION;
1259 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Savepoint is established');
1260
1261 -- Standard call to check for call compatibility
1262 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1263 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1264 END IF;
1265
1266 -- Initialize message list if p_init_msg_list is set to TRUE
1267 --IF FND_API.To_Boolean(p_init_msg_list) THEN
1268 -- FND_MSG_PUB.initialize;
1269 --END IF;
1270
1271 -- Initialize API return status to success
1272 l_return_status := FND_API.G_RET_STS_SUCCESS;
1273
1274 -- START OF BODY OF API
1275
1276 VALIDATE_EXTN(P_LOAN_EXT_ID, 'REJECT');
1277
1278 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Updating LNS_LOAN_EXTENSIONS...');
1279
1280 update LNS_LOAN_EXTENSIONS
1281 set STATUS = 'REJECTED',
1282 APPR_REJECT_DATE = sysdate,
1283 APPR_REJECT_BY = LNS_UTILITY_PUB.USER_ID,
1284 LAST_UPDATE_DATE = sysdate,
1285 LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY,
1286 LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
1287 where LOAN_EXT_ID = P_LOAN_EXT_ID;
1288
1289 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done');
1290
1291 -- END OF BODY OF API
1292
1293 if P_COMMIT = FND_API.G_TRUE then
1294 COMMIT WORK;
1295 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Commited');
1296 end if;
1297
1298 x_return_status := FND_API.G_RET_STS_SUCCESS;
1299
1300 -- Standard call to get message count and if count is 1, get message info
1301 FND_MSG_PUB.Count_And_Get(
1302 p_encoded => FND_API.G_FALSE,
1303 p_count => x_msg_count,
1304 p_data => x_msg_data);
1305
1306 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1307
1308 EXCEPTION
1309 WHEN FND_API.G_EXC_ERROR THEN
1310 ROLLBACK TO REJECT_LOAN_EXTENSION;
1311 x_return_status := FND_API.G_RET_STS_ERROR;
1312 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1313 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
1314 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1315 ROLLBACK TO REJECT_LOAN_EXTENSION;
1316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1317 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1318 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
1319 WHEN OTHERS THEN
1320 ROLLBACK TO REJECT_LOAN_EXTENSION;
1321 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1322 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1323 FND_MSG_PUB.Add_Exc_Msg(l_api_name);
1324 END IF;
1325 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1326 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
1327 END;
1328
1329
1330
1331
1332 /*========================================================================
1333 | PUBLIC PROCEDURE CALC_NEW_TERMS
1334 |
1335 | DESCRIPTION
1336 | This procedure calculates and returns new loan terms based on input extension loan term data.
1337 |
1338 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1339 | None
1340 |
1341 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1342 | LogMessage
1343 |
1344 | PARAMETERS
1345 | P_API_VERSION IN Standard in parameter
1346 | P_INIT_MSG_LIST IN Standard in parameter
1347 | P_COMMIT IN Standard in parameter
1348 | P_VALIDATION_LEVEL IN Standard in parameter
1349 | P_EXT_LOAN_REC IN OUT NOCOPY LNS_EXT_LOAN_PUB.NEW_TERM_REC record
1350 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
1351 | X_MSG_COUNT OUT NOCOPY Standard out parameter
1352 | X_MSG_DATA OUT NOCOPY Standard out parameter
1353 |
1354 | KNOWN ISSUES
1355 | None
1356 |
1357 | NOTES
1358 |
1359 | MODIFICATION HISTORY
1360 | Date Author Description of Changes
1361 | 09-25-2007 scherkas Created
1362 |
1363 *=======================================================================*/
1364 PROCEDURE CALC_NEW_TERMS(
1365 P_API_VERSION IN NUMBER,
1366 P_INIT_MSG_LIST IN VARCHAR2,
1367 P_COMMIT IN VARCHAR2,
1368 P_VALIDATION_LEVEL IN NUMBER,
1369 P_NEW_TERM_REC IN OUT NOCOPY LNS_EXT_LOAN_PUB.NEW_TERM_REC,
1370 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1371 X_MSG_COUNT OUT NOCOPY NUMBER,
1372 X_MSG_DATA OUT NOCOPY VARCHAR2)
1373 IS
1374
1375 /*-----------------------------------------------------------------------+
1376 | Local Variable Declarations and initializations |
1377 +-----------------------------------------------------------------------*/
1378
1379 l_api_name CONSTANT VARCHAR2(30) := 'CALC_NEW_TERMS';
1380 l_api_version CONSTANT NUMBER := 1.0;
1381 l_return_status VARCHAR2(1);
1382 l_msg_count NUMBER;
1383 l_msg_data VARCHAR2(32767);
1384
1385 l_loan_start_date date;
1386 l_loan_payment_frequency VARCHAR2(30);
1387 l_extend_installments number;
1388 l_term1 number;
1389 l_ext_term1 number;
1390 l_am_term1 number;
1391 l_ext_am_term1 number;
1392 l_term_id number;
1393 l_first_payment_date date;
1394 l_intervals number;
1395 l_pay_in_arrears varchar2(1);
1396 l_pay_in_arrears_bool boolean;
1397 l_prin_first_pay_date date;
1398 l_prin_intervals number;
1399 l_prin_payment_frequency varchar2(30);
1400 l_prin_pay_in_arrears varchar2(1);
1401 l_prin_pay_in_arrears_bool boolean;
1402 l_pay_calc_method varchar2(30);
1403 l_amortization_frequency varchar2(30);
1404 l_customized VARCHAR2(1);
1405
1406 l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
1407
1408 /*-----------------------------------------------------------------------+
1409 | Cursor Declarations |
1410 +-----------------------------------------------------------------------*/
1411
1412 -- query existent loan data
1413 CURSOR loan_cur(P_LOAN_ID number) IS
1414 select
1415 loan.loan_start_date,
1416 loan.loan_term,
1417 loan.LOAN_TERM_PERIOD,
1418 loan.BALLOON_PAYMENT_TYPE,
1419 loan.BALLOON_PAYMENT_AMOUNT,
1420 loan.AMORTIZED_TERM,
1421 loan.LOAN_MATURITY_DATE,
1422 term.loan_payment_frequency,
1423 term.term_id,
1424 term.amortization_frequency,
1425 trunc(term.first_payment_date),
1426 decode(trunc(term.first_payment_date) - trunc(loan.loan_start_date), 0, 'N', 'Y'), -- calculate in advance or arrears
1427 nvl(term.PAYMENT_CALC_METHOD, 'EQUAL_PAYMENT'),
1428 trunc(nvl(term.prin_first_pay_date, term.first_payment_date)),
1429 nvl(term.prin_payment_frequency, term.loan_payment_frequency),
1430 decode(trunc(nvl(term.prin_first_pay_date, term.first_payment_date)) - trunc(loan.loan_start_date), 0, 'N', 'Y'),
1431 nvl(loan.custom_payments_flag, 'N')
1432 from lns_loan_headers_all loan,
1433 lns_terms term
1434 where loan.loan_id = P_LOAN_ID and
1435 loan.loan_id = term.loan_id;
1436
1437 -- query max installment number
1438 CURSOR rate_sched_cur(P_TERM_ID number) IS
1439 select max(end_installment_number)
1440 from LNS_RATE_SCHEDULES
1441 where term_id = P_TERM_ID and
1442 phase = 'TERM' and
1443 trunc(nvl(END_DATE_ACTIVE,(sysdate+1))) > trunc(sysdate);
1444
1445 -- query count of custom schedule rows with DUE_DATE < l_NEW_MATURITY_DATE
1446 CURSOR custom_sched_count(P_LOAN_ID number, P_MATURITY_DATE date) IS
1447 select count(1)
1448 from LNS_CUSTOM_PAYMNT_SCHEDS
1449 where loan_id = P_LOAN_ID
1450 and DUE_DATE <= P_MATURITY_DATE;
1451
1452 BEGIN
1453
1454 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1455
1456 -- Standard start of API savepoint
1457 SAVEPOINT CALC_NEW_TERMS;
1458 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Savepoint is established');
1459
1460 -- Standard call to check for call compatibility
1461 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1462 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1463 END IF;
1464
1465 -- Initialize message list if p_init_msg_list is set to TRUE
1466 --IF FND_API.To_Boolean(p_init_msg_list) THEN
1467 -- FND_MSG_PUB.initialize;
1468 --END IF;
1469
1470 -- Initialize API return status to success
1471 l_return_status := FND_API.G_RET_STS_SUCCESS;
1472
1473 -- START OF BODY OF API
1474
1475 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Input:');
1476 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'LOAN_ID:' || P_NEW_TERM_REC.LOAN_ID);
1477 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_TERM:' || P_NEW_TERM_REC.EXT_TERM);
1478 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_BALLOON_TYPE:' || P_NEW_TERM_REC.EXT_BALLOON_TYPE);
1479 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_BALLOON_AMOUNT:' || P_NEW_TERM_REC.EXT_BALLOON_AMOUNT);
1480 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_AMORT_TERM:' || P_NEW_TERM_REC.EXT_AMORT_TERM);
1481 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_TERM_PERIOD:' || P_NEW_TERM_REC.EXT_TERM_PERIOD);
1482
1483 open loan_cur(P_NEW_TERM_REC.LOAN_ID);
1484 fetch loan_cur
1485 into l_loan_start_date,
1486 P_NEW_TERM_REC.OLD_TERM,
1487 P_NEW_TERM_REC.OLD_TERM_PERIOD,
1488 P_NEW_TERM_REC.OLD_BALLOON_TYPE,
1489 P_NEW_TERM_REC.OLD_BALLOON_AMOUNT,
1490 P_NEW_TERM_REC.OLD_AMORT_TERM,
1491 P_NEW_TERM_REC.OLD_MATURITY_DATE,
1492 l_loan_payment_frequency,
1493 l_term_id,
1494 l_amortization_frequency,
1495 l_first_payment_date,
1496 l_pay_in_arrears,
1497 l_pay_calc_method,
1498 l_prin_first_pay_date,
1499 l_prin_payment_frequency,
1500 l_prin_pay_in_arrears,
1501 l_customized;
1502 close loan_cur;
1503
1504 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Current loan term data:');
1505 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_start_date: '|| l_loan_start_date);
1506 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_term: '|| P_NEW_TERM_REC.OLD_TERM);
1507 LogMessage(FND_LOG.LEVEL_STATEMENT, 'BALLOON_TYPE: '|| P_NEW_TERM_REC.OLD_BALLOON_TYPE);
1508 LogMessage(FND_LOG.LEVEL_STATEMENT, 'BALLOON_AMOUNT: '|| P_NEW_TERM_REC.OLD_BALLOON_AMOUNT);
1509 LogMessage(FND_LOG.LEVEL_STATEMENT, 'AMORTIZED_TERM: '|| P_NEW_TERM_REC.OLD_AMORT_TERM);
1510 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_TERM_PERIOD: '|| P_NEW_TERM_REC.OLD_TERM_PERIOD);
1511 LogMessage(FND_LOG.LEVEL_STATEMENT, 'maturity_date: '|| P_NEW_TERM_REC.OLD_MATURITY_DATE);
1512 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_payment_frequency: '|| l_loan_payment_frequency);
1513 LogMessage(FND_LOG.LEVEL_STATEMENT, 'term_id: '|| l_term_id);
1514 LogMessage(FND_LOG.LEVEL_STATEMENT, 'amortization_frequency: ' || l_amortization_frequency);
1515 LogMessage(FND_LOG.LEVEL_STATEMENT, 'first_payment_date: ' || l_first_payment_date);
1516 LogMessage(FND_LOG.LEVEL_STATEMENT, 'pay_in_arrears: ' || l_pay_in_arrears);
1517 LogMessage(FND_LOG.LEVEL_STATEMENT, 'pay_calc_method: ' || l_pay_calc_method);
1518 LogMessage(FND_LOG.LEVEL_STATEMENT, 'prin_first_pay_date: ' || l_prin_first_pay_date);
1519 LogMessage(FND_LOG.LEVEL_STATEMENT, 'prin_payment_frequency: ' || l_prin_payment_frequency);
1520 LogMessage(FND_LOG.LEVEL_STATEMENT, 'prin_pay_in_arrears: ' || l_prin_pay_in_arrears);
1521 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_customized: ' || l_customized);
1522
1523 open rate_sched_cur(l_term_id);
1524 fetch rate_sched_cur
1525 into P_NEW_TERM_REC.OLD_INSTALLMENTS;
1526 close rate_sched_cur;
1527
1528 LogMessage(FND_LOG.LEVEL_STATEMENT, 'INSTALLMENTS: '|| P_NEW_TERM_REC.OLD_INSTALLMENTS);
1529
1530 if P_NEW_TERM_REC.OLD_TERM_PERIOD = 'YEARS' then
1531 if P_NEW_TERM_REC.EXT_TERM_PERIOD = 'YEARS' then
1532 P_NEW_TERM_REC.NEW_TERM_PERIOD := 'YEARS';
1533 elsif P_NEW_TERM_REC.EXT_TERM_PERIOD = 'MONTHS' then
1534 P_NEW_TERM_REC.NEW_TERM_PERIOD := 'MONTHS';
1535 elsif P_NEW_TERM_REC.EXT_TERM_PERIOD = 'DAYS' then
1536 P_NEW_TERM_REC.NEW_TERM_PERIOD := 'DAYS';
1537 end if;
1538 elsif P_NEW_TERM_REC.OLD_TERM_PERIOD = 'MONTHS' then
1539 if P_NEW_TERM_REC.EXT_TERM_PERIOD = 'YEARS' then
1540 P_NEW_TERM_REC.NEW_TERM_PERIOD := 'MONTHS';
1541 elsif P_NEW_TERM_REC.EXT_TERM_PERIOD = 'MONTHS' then
1542 P_NEW_TERM_REC.NEW_TERM_PERIOD := 'MONTHS';
1543 elsif P_NEW_TERM_REC.EXT_TERM_PERIOD = 'DAYS' then
1544 P_NEW_TERM_REC.NEW_TERM_PERIOD := 'DAYS';
1545 end if;
1546 elsif P_NEW_TERM_REC.OLD_TERM_PERIOD = 'DAYS' then
1547 if P_NEW_TERM_REC.EXT_TERM_PERIOD = 'YEARS' then
1548 P_NEW_TERM_REC.NEW_TERM_PERIOD := 'DAYS';
1549 elsif P_NEW_TERM_REC.EXT_TERM_PERIOD = 'MONTHS' then
1550 P_NEW_TERM_REC.NEW_TERM_PERIOD := 'DAYS';
1551 elsif P_NEW_TERM_REC.EXT_TERM_PERIOD = 'DAYS' then
1552 P_NEW_TERM_REC.NEW_TERM_PERIOD := 'DAYS';
1553 end if;
1554 end if;
1555
1556 if P_NEW_TERM_REC.OLD_TERM_PERIOD = P_NEW_TERM_REC.EXT_TERM_PERIOD then
1557
1558 P_NEW_TERM_REC.NEW_TERM := P_NEW_TERM_REC.OLD_TERM + P_NEW_TERM_REC.EXT_TERM;
1559
1560 P_NEW_TERM_REC.NEW_BALLOON_TYPE := P_NEW_TERM_REC.EXT_BALLOON_TYPE;
1561 if P_NEW_TERM_REC.EXT_BALLOON_TYPE = 'TERM' then
1562 P_NEW_TERM_REC.NEW_AMORT_TERM := P_NEW_TERM_REC.OLD_AMORT_TERM + P_NEW_TERM_REC.EXT_AMORT_TERM;
1563 P_NEW_TERM_REC.NEW_BALLOON_AMOUNT := P_NEW_TERM_REC.OLD_BALLOON_AMOUNT;
1564 else
1565 P_NEW_TERM_REC.NEW_AMORT_TERM := P_NEW_TERM_REC.NEW_TERM;
1566 P_NEW_TERM_REC.NEW_BALLOON_AMOUNT := P_NEW_TERM_REC.EXT_BALLOON_AMOUNT;
1567 end if;
1568
1569 else
1570
1571 l_term1 := LNS_FIN_UTILS.intervalsInPeriod(
1572 p_period_number => P_NEW_TERM_REC.OLD_TERM
1573 ,p_period_type1 => P_NEW_TERM_REC.OLD_TERM_PERIOD
1574 ,p_period_type2 => P_NEW_TERM_REC.NEW_TERM_PERIOD);
1575
1576 l_ext_term1 := LNS_FIN_UTILS.intervalsInPeriod(
1577 p_period_number => P_NEW_TERM_REC.EXT_TERM
1578 ,p_period_type1 => P_NEW_TERM_REC.EXT_TERM_PERIOD
1579 ,p_period_type2 => P_NEW_TERM_REC.NEW_TERM_PERIOD);
1580
1581 P_NEW_TERM_REC.NEW_TERM := l_term1 + l_ext_term1;
1582
1583 P_NEW_TERM_REC.NEW_BALLOON_TYPE := P_NEW_TERM_REC.EXT_BALLOON_TYPE;
1584 if P_NEW_TERM_REC.EXT_BALLOON_TYPE = 'TERM' then
1585 l_am_term1 := LNS_FIN_UTILS.intervalsInPeriod(
1586 p_period_number => P_NEW_TERM_REC.OLD_AMORT_TERM
1587 ,p_period_type1 => P_NEW_TERM_REC.OLD_TERM_PERIOD
1588 ,p_period_type2 => P_NEW_TERM_REC.NEW_TERM_PERIOD);
1589
1590 l_ext_am_term1 := LNS_FIN_UTILS.intervalsInPeriod(
1591 p_period_number => P_NEW_TERM_REC.EXT_AMORT_TERM
1592 ,p_period_type1 => P_NEW_TERM_REC.EXT_TERM_PERIOD
1593 ,p_period_type2 => P_NEW_TERM_REC.NEW_TERM_PERIOD);
1594
1595 P_NEW_TERM_REC.NEW_AMORT_TERM := l_am_term1 + l_ext_am_term1;
1596 P_NEW_TERM_REC.NEW_BALLOON_AMOUNT := P_NEW_TERM_REC.OLD_BALLOON_AMOUNT;
1597 else
1598 P_NEW_TERM_REC.NEW_AMORT_TERM := P_NEW_TERM_REC.NEW_TERM;
1599 P_NEW_TERM_REC.NEW_BALLOON_AMOUNT := P_NEW_TERM_REC.EXT_BALLOON_AMOUNT;
1600 end if;
1601
1602 end if;
1603
1604 P_NEW_TERM_REC.NEW_MATURITY_DATE := lns_fin_utils.getMaturityDate(
1605 p_term => P_NEW_TERM_REC.NEW_TERM,
1606 p_term_period => P_NEW_TERM_REC.NEW_TERM_PERIOD,
1607 p_frequency => l_loan_payment_frequency,
1608 p_start_date => l_loan_start_date);
1609
1610
1611 if l_customized = 'N' then
1612
1613 -- calculating new number of installments
1614 if (l_pay_calc_method = 'SEPARATE_SCHEDULES') then
1615
1616 if l_pay_in_arrears = 'Y' then
1617 l_pay_in_arrears_bool := true;
1618 else
1619 l_pay_in_arrears_bool := false;
1620 end if;
1621
1622 if l_prin_pay_in_arrears = 'Y' then
1623 l_prin_pay_in_arrears_bool := true;
1624 else
1625 l_prin_pay_in_arrears_bool := false;
1626 end if;
1627
1628 l_intervals := lns_fin_utils.intervalsInPeriod(P_NEW_TERM_REC.NEW_TERM
1629 ,P_NEW_TERM_REC.NEW_TERM_PERIOD
1630 ,l_loan_payment_frequency);
1631
1632 l_prin_intervals := lns_fin_utils.intervalsInPeriod(P_NEW_TERM_REC.NEW_TERM
1633 ,P_NEW_TERM_REC.NEW_TERM_PERIOD
1634 ,l_prin_payment_frequency);
1635
1636 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_intervals: ' || l_intervals);
1637 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_prin_intervals: ' || l_prin_intervals);
1638
1639 l_payment_tbl := LNS_FIN_UTILS.buildSIPPaymentSchedule(
1640 p_loan_start_date => l_loan_start_date
1641 ,p_loan_maturity_date => P_NEW_TERM_REC.NEW_MATURITY_DATE
1642 ,p_int_first_pay_date => l_first_payment_date
1643 ,p_int_num_intervals => l_intervals
1644 ,p_int_interval_type => l_loan_payment_frequency
1645 ,p_int_pay_in_arrears => l_pay_in_arrears_bool
1646 ,p_prin_first_pay_date => l_prin_first_pay_date
1647 ,p_prin_num_intervals => l_prin_intervals
1648 ,p_prin_interval_type => l_prin_payment_frequency
1649 ,p_prin_pay_in_arrears => l_prin_pay_in_arrears_bool);
1650
1651 P_NEW_TERM_REC.NEW_INSTALLMENTS := l_payment_tbl.count;
1652
1653 else
1654
1655 P_NEW_TERM_REC.NEW_INSTALLMENTS := round(LNS_FIN_UTILS.intervalsInPeriod(
1656 p_period_number => P_NEW_TERM_REC.NEW_TERM
1657 ,p_period_type1 => P_NEW_TERM_REC.NEW_TERM_PERIOD
1658 ,p_period_type2 => l_loan_payment_frequency));
1659
1660 end if;
1661 else
1662
1663 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Querying number of custom schedule rows...');
1664 open custom_sched_count(P_NEW_TERM_REC.LOAN_ID, P_NEW_TERM_REC.NEW_MATURITY_DATE);
1665 fetch custom_sched_count
1666 into P_NEW_TERM_REC.NEW_INSTALLMENTS;
1667 close custom_sched_count;
1668
1669 end if;
1670
1671 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'New loan term data:');
1672 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'NEW_TERM: '|| P_NEW_TERM_REC.NEW_TERM);
1673 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'NEW_AMORT_TERM: '|| P_NEW_TERM_REC.NEW_AMORT_TERM);
1674 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'NEW_TERM_PERIOD: '|| P_NEW_TERM_REC.NEW_TERM_PERIOD);
1675 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'NEW_MATURITY_DATE: '|| P_NEW_TERM_REC.NEW_MATURITY_DATE);
1676 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'BEGIN_EXT_INSTAL_NUMBER: '|| P_NEW_TERM_REC.BEGIN_EXT_INSTAL_NUMBER);
1677 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'END_EXT_INSTAL_NUMBER: '|| P_NEW_TERM_REC.END_EXT_INSTAL_NUMBER);
1678 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'NEW_INSTALLMENTS: '|| P_NEW_TERM_REC.NEW_INSTALLMENTS);
1679
1680 if (P_NEW_TERM_REC.NEW_AMORT_TERM < P_NEW_TERM_REC.NEW_TERM) then
1681
1682 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Loan amortized term cannot be less than loan term.');
1683 FND_MESSAGE.SET_NAME('LNS', 'LNS_LOAN_TERM_INVALID');
1684 FND_MSG_PUB.Add;
1685 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
1686 RAISE FND_API.G_EXC_ERROR;
1687
1688 end if;
1689
1690 -- END OF BODY OF API
1691 /*
1692 if P_COMMIT = FND_API.G_TRUE then
1693 COMMIT WORK;
1694 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Commited');
1695 end if;
1696 */
1697 x_return_status := FND_API.G_RET_STS_SUCCESS;
1698
1699 -- Standard call to get message count and if count is 1, get message info
1700 FND_MSG_PUB.Count_And_Get(
1701 p_encoded => FND_API.G_FALSE,
1702 p_count => x_msg_count,
1703 p_data => x_msg_data);
1704
1705 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1706
1707 EXCEPTION
1708 WHEN FND_API.G_EXC_ERROR THEN
1709 --ROLLBACK TO CALC_NEW_TERMS;
1710 x_return_status := FND_API.G_RET_STS_ERROR;
1711 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1712 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'In exception');
1713 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1714 --ROLLBACK TO CALC_NEW_TERMS;
1715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1716 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1717 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'In exception');
1718 WHEN OTHERS THEN
1719 --ROLLBACK TO CALC_NEW_TERMS;
1720 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1721 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1722 FND_MSG_PUB.Add_Exc_Msg(l_api_name);
1723 END IF;
1724 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1725 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'In exception');
1726 END;
1727
1728
1729
1730
1731 BEGIN
1732 G_LOG_ENABLED := 'N';
1733 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
1734
1735 /* getting msg logging info */
1736 G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
1737 if (G_LOG_ENABLED = 'N') then
1738 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
1739 else
1740 G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
1741 end if;
1742
1743 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
1744 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_MSG_LEVEL: ' || G_MSG_LEVEL);
1745
1746 END;