[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.3.12020000.2 2013/02/11 14:46:08 scherkas ship $ */
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_INDEX_DATE:' || P_LOAN_EXT_REC.EXT_INDEX_DATE);
568
569 if (P_LOAN_EXT_REC.LOAN_EXT_ID is null) then
570 VALIDATE_EXTN(P_LOAN_EXT_REC, 'INSERT');
571 else
572 VALIDATE_EXTN(P_LOAN_EXT_REC, 'UPDATE');
573 end if;
574
575 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling CALC_NEW_TERMS...');
576
577 l_NEW_TERM_REC.LOAN_ID := P_LOAN_EXT_REC.LOAN_ID;
578 l_NEW_TERM_REC.EXT_TERM := P_LOAN_EXT_REC.EXT_TERM;
579 l_NEW_TERM_REC.EXT_TERM_PERIOD := P_LOAN_EXT_REC.EXT_TERM_PERIOD;
580 l_NEW_TERM_REC.EXT_BALLOON_TYPE := P_LOAN_EXT_REC.EXT_BALLOON_TYPE;
581 l_NEW_TERM_REC.EXT_BALLOON_AMOUNT := P_LOAN_EXT_REC.EXT_BALLOON_AMOUNT;
582 l_NEW_TERM_REC.EXT_AMORT_TERM := P_LOAN_EXT_REC.EXT_AMORT_TERM;
583
584 CALC_NEW_TERMS(
585 P_API_VERSION => 1.0,
586 P_INIT_MSG_LIST => FND_API.G_TRUE,
587 P_COMMIT => FND_API.G_FALSE,
588 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
589 P_NEW_TERM_REC => l_NEW_TERM_REC,
590 X_RETURN_STATUS => l_return_status,
591 X_MSG_COUNT => l_msg_count,
592 X_MSG_DATA => l_msg_data);
593
594 if (l_return_status <> 'S') then
595 RAISE FND_API.G_EXC_ERROR;
596 end if;
597
598
599 BEGIN
600 SELECT
601 'Y' into l_is_exist
602 FROM
603 lns_loan_extensions
604 WHERE
605 loan_ext_id = P_LOAN_EXT_REC.LOAN_EXT_ID;
606
607 EXCEPTION
608 WHEN no_data_found THEN
609 l_is_exist := 'N';
610 END;
611
612 --if (P_LOAN_EXT_REC.LOAN_EXT_ID is null) then
613
614 if (l_is_exist <> 'Y') then
615
616 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting into lns_loan_extensions...');
617
618 if P_LOAN_EXT_REC.LOAN_EXT_ID is null then
619 select lns_loan_extensions_s.NEXTVAL into P_LOAN_EXT_REC.LOAN_EXT_ID from dual;
620 end if;
621
622 insert into lns_loan_extensions(
623 LOAN_EXT_ID,
624 LOAN_ID,
625 DESCRIPTION,
626 OLD_TERM,
627 OLD_TERM_PERIOD,
628 OLD_BALLOON_TYPE,
629 OLD_BALLOON_AMOUNT,
630 OLD_AMORT_TERM,
631 OLD_MATURITY_DATE,
632 OLD_INSTALLMENTS,
633 EXT_TERM,
634 EXT_TERM_PERIOD,
635 EXT_BALLOON_TYPE,
636 EXT_BALLOON_AMOUNT,
637 EXT_AMORT_TERM,
638 EXT_RATE,
639 EXT_SPREAD,
640 EXT_IO_FLAG,
641 EXT_INDEX_DATE,
642 NEW_TERM,
643 NEW_TERM_PERIOD,
644 NEW_BALLOON_TYPE,
645 NEW_BALLOON_AMOUNT,
646 NEW_AMORT_TERM,
647 NEW_MATURITY_DATE,
648 NEW_INSTALLMENTS,
649 STATUS,
650 APPR_REJECT_DATE,
651 APPR_REJECT_BY,
652 OBJECT_VERSION_NUMBER,
653 CREATION_DATE,
654 CREATED_BY,
655 LAST_UPDATE_DATE,
656 LAST_UPDATED_BY,
657 LAST_UPDATE_LOGIN)
658 values(
659 P_LOAN_EXT_REC.LOAN_EXT_ID,
660 P_LOAN_EXT_REC.LOAN_ID,
661 P_LOAN_EXT_REC.DESCRIPTION,
662 l_NEW_TERM_REC.OLD_TERM,
663 l_NEW_TERM_REC.OLD_TERM_PERIOD,
664 l_NEW_TERM_REC.OLD_BALLOON_TYPE,
665 l_NEW_TERM_REC.OLD_BALLOON_AMOUNT,
666 l_NEW_TERM_REC.OLD_AMORT_TERM,
667 l_NEW_TERM_REC.OLD_MATURITY_DATE,
668 l_NEW_TERM_REC.OLD_INSTALLMENTS,
669 P_LOAN_EXT_REC.EXT_TERM,
670 P_LOAN_EXT_REC.EXT_TERM_PERIOD,
671 P_LOAN_EXT_REC.EXT_BALLOON_TYPE,
672 P_LOAN_EXT_REC.EXT_BALLOON_AMOUNT,
673 P_LOAN_EXT_REC.EXT_AMORT_TERM,
674 P_LOAN_EXT_REC.EXT_RATE,
675 P_LOAN_EXT_REC.EXT_SPREAD,
676 P_LOAN_EXT_REC.EXT_IO_FLAG,
677 P_LOAN_EXT_REC.EXT_INDEX_DATE,
678 l_NEW_TERM_REC.NEW_TERM,
679 l_NEW_TERM_REC.NEW_TERM_PERIOD,
680 l_NEW_TERM_REC.NEW_BALLOON_TYPE,
681 l_NEW_TERM_REC.NEW_BALLOON_AMOUNT,
682 l_NEW_TERM_REC.NEW_AMORT_TERM,
683 l_NEW_TERM_REC.NEW_MATURITY_DATE,
684 l_NEW_TERM_REC.NEW_INSTALLMENTS,
685 'PENDING',
686 null,
687 null,
688 1,
689 sysdate,
690 LNS_UTILITY_PUB.CREATED_BY,
691 sysdate,
692 LNS_UTILITY_PUB.LAST_UPDATED_BY,
693 LNS_UTILITY_PUB.LAST_UPDATE_LOGIN);
694
695 else
696
697 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Updating lns_loan_extensions...');
698
699 update LNS_LOAN_EXTENSIONS set
700 DESCRIPTION = P_LOAN_EXT_REC.DESCRIPTION,
701 OLD_TERM = l_NEW_TERM_REC.OLD_TERM,
702 OLD_TERM_PERIOD = l_NEW_TERM_REC.OLD_TERM_PERIOD,
703 OLD_BALLOON_TYPE = l_NEW_TERM_REC.OLD_BALLOON_TYPE,
704 OLD_BALLOON_AMOUNT = l_NEW_TERM_REC.OLD_BALLOON_AMOUNT,
705 OLD_AMORT_TERM = l_NEW_TERM_REC.OLD_AMORT_TERM,
706 OLD_MATURITY_DATE = l_NEW_TERM_REC.OLD_MATURITY_DATE,
707 OLD_INSTALLMENTS = l_NEW_TERM_REC.OLD_INSTALLMENTS,
708 EXT_TERM = P_LOAN_EXT_REC.EXT_TERM,
709 EXT_TERM_PERIOD = P_LOAN_EXT_REC.EXT_TERM_PERIOD,
710 EXT_BALLOON_TYPE = P_LOAN_EXT_REC.EXT_BALLOON_TYPE,
711 EXT_BALLOON_AMOUNT = P_LOAN_EXT_REC.EXT_BALLOON_AMOUNT,
712 EXT_AMORT_TERM = P_LOAN_EXT_REC.EXT_AMORT_TERM,
713 EXT_RATE = P_LOAN_EXT_REC.EXT_RATE,
714 EXT_SPREAD = P_LOAN_EXT_REC.EXT_SPREAD,
715 EXT_IO_FLAG = P_LOAN_EXT_REC.EXT_IO_FLAG,
716 EXT_INDEX_DATE = P_LOAN_EXT_REC.EXT_INDEX_DATE,
717 NEW_TERM = l_NEW_TERM_REC.NEW_TERM,
718 NEW_TERM_PERIOD = l_NEW_TERM_REC.NEW_TERM_PERIOD,
719 NEW_BALLOON_TYPE = l_NEW_TERM_REC.NEW_BALLOON_TYPE,
720 NEW_BALLOON_AMOUNT = l_NEW_TERM_REC.NEW_BALLOON_AMOUNT,
721 NEW_AMORT_TERM = l_NEW_TERM_REC.NEW_AMORT_TERM,
722 NEW_MATURITY_DATE = l_NEW_TERM_REC.NEW_MATURITY_DATE,
723 NEW_INSTALLMENTS = l_NEW_TERM_REC.NEW_INSTALLMENTS,
724 LAST_UPDATE_DATE = sysdate,
725 LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY,
726 LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
727 where LOAN_EXT_ID = P_LOAN_EXT_REC.LOAN_EXT_ID;
728
729 end if;
730
731 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done');
732
733 -- END OF BODY OF API
734
735 if P_COMMIT = FND_API.G_TRUE then
736 COMMIT WORK;
737 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Commited');
738 end if;
739
740 x_return_status := FND_API.G_RET_STS_SUCCESS;
741
742 -- Standard call to get message count and if count is 1, get message info
743 FND_MSG_PUB.Count_And_Get(
744 p_encoded => FND_API.G_FALSE,
745 p_count => x_msg_count,
746 p_data => x_msg_data);
747
748 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
749
750 EXCEPTION
751 WHEN FND_API.G_EXC_ERROR THEN
752 ROLLBACK TO SAVE_LOAN_EXTENSION;
753 x_return_status := FND_API.G_RET_STS_ERROR;
754 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
755 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
756 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
757 ROLLBACK TO SAVE_LOAN_EXTENSION;
758 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
759 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
760 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
761 WHEN OTHERS THEN
762 ROLLBACK TO SAVE_LOAN_EXTENSION;
763 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
764 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
765 FND_MSG_PUB.Add_Exc_Msg(l_api_name);
766 END IF;
767 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
768 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
769 END;
770
771
772
773
774 /*========================================================================
775 | PUBLIC PROCEDURE APPROVE_LOAN_EXTENSION
776 |
777 | DESCRIPTION
778 | This procedure approves loan extension and updates loan term data in
779 | lns_loan_headers_all from lns_loan_extensions table
780 |
781 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
782 | None
783 |
784 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
785 | LogMessage
786 |
787 | PARAMETERS
788 | P_API_VERSION IN Standard in parameter
789 | P_INIT_MSG_LIST IN Standard in parameter
790 | P_COMMIT IN Standard in parameter
791 | P_VALIDATION_LEVEL IN Standard in parameter
792 | P_LOAN_EXT_ID IN Loan extension ID
793 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
794 | X_MSG_COUNT OUT NOCOPY Standard out parameter
795 | X_MSG_DATA OUT NOCOPY Standard out parameter
796 |
797 | KNOWN ISSUES
798 | None
799 |
800 | NOTES
801 |
802 | MODIFICATION HISTORY
803 | Date Author Description of Changes
804 | 09-25-2007 scherkas Created
805 |
806 *=======================================================================*/
807 PROCEDURE APPROVE_LOAN_EXTENSION(
808 P_API_VERSION IN NUMBER,
809 P_INIT_MSG_LIST IN VARCHAR2,
810 P_COMMIT IN VARCHAR2,
811 P_VALIDATION_LEVEL IN NUMBER,
812 P_LOAN_EXT_ID IN NUMBER,
813 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
814 X_MSG_COUNT OUT NOCOPY NUMBER,
815 X_MSG_DATA OUT NOCOPY VARCHAR2)
816 IS
817
818 /*-----------------------------------------------------------------------+
819 | Local Variable Declarations and initializations |
820 +-----------------------------------------------------------------------*/
821
822 l_api_name CONSTANT VARCHAR2(30) := 'APPROVE_LOAN_EXTENSION';
823 l_api_version CONSTANT NUMBER := 1.0;
824 l_return_status VARCHAR2(1);
825 l_msg_count NUMBER;
826 l_msg_data VARCHAR2(32767);
827
828 l_LOAN_ID NUMBER;
829 l_OLD_INSTALLMENTS NUMBER;
830 l_NEW_TERM NUMBER;
831 l_NEW_TERM_PERIOD VARCHAR2(30);
832 l_NEW_AMORT_TERM NUMBER;
833 l_NEW_MATURITY_DATE DATE;
834 l_NEW_INSTALLMENTS NUMBER;
835 l_OBJECT_VERSION_NUMBER NUMBER;
836 l_EXT_RATE NUMBER;
837 l_EXT_SPREAD NUMBER;
838 l_EXT_IO_FLAG VARCHAR2(1);
839 l_EXT_INDEX_DATE DATE;
840 l_TERM_ID NUMBER;
841 l_REQUEST_ID number;
842 l_last_billed_installment number;
843 l_customized VARCHAR2(1);
844 l_NEW_BALLOON_TYPE VARCHAR2(30);
845 l_NEW_BALLOON_AMOUNT NUMBER;
846
847 l_RATE_ID number;
848 l_RATE number;
849 l_BEGIN_INSTALLMENT number;
850 l_END_INSTALLMENT number;
851 l_INDEX_RATE number;
852 l_SPREAD number;
853 l_INTEREST_ONLY_FLAG VARCHAR2(1);
854 i number;
855 l_agreement_reason varchar2(500);
856 l_description VARCHAR2(30);
857 l_EXT_TERM number;
858 l_EXT_TERM_PERIOD VARCHAR2(30);
859
860 l_loan_header_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type;
861
862 /*-----------------------------------------------------------------------+
863 | Cursor Declarations |
864 +-----------------------------------------------------------------------*/
865
866 -- query loan extension details
867 CURSOR loan_ext_cur(P_LOAN_EXT_ID number) IS
868 select
869 ext.LOAN_ID,
870 ext.OLD_INSTALLMENTS,
871 ext.NEW_TERM,
872 ext.NEW_TERM_PERIOD,
873 ext.NEW_BALLOON_TYPE,
874 ext.NEW_BALLOON_AMOUNT,
875 ext.NEW_AMORT_TERM,
876 ext.NEW_MATURITY_DATE,
877 ext.NEW_INSTALLMENTS,
878 ext.EXT_RATE,
879 ext.EXT_SPREAD,
880 ext.EXT_IO_FLAG,
881 ext.EXT_INDEX_DATE,
882 loan.OBJECT_VERSION_NUMBER,
883 term.term_id,
884 nvl(loan.custom_payments_flag, 'N'),
885 ext.DESCRIPTION,
886 ext.EXT_TERM,
887 ext.EXT_TERM_PERIOD
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 from lns_rate_schedules
905 where term_id = p_term_id and
906 END_DATE_ACTIVE is null and
907 phase = 'TERM'
908 order by END_INSTALLMENT_NUMBER desc;
909
910 BEGIN
911
912 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
913
914 -- Standard start of API savepoint
915 SAVEPOINT APPROVE_LOAN_EXTENSION;
916 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Savepoint is established');
917
918 -- Standard call to check for call compatibility
919 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
920 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
921 END IF;
922
923 -- Initialize message list if p_init_msg_list is set to TRUE
924 --IF FND_API.To_Boolean(p_init_msg_list) THEN
925 -- FND_MSG_PUB.initialize;
926 --END IF;
927
928 -- Initialize API return status to success
929 l_return_status := FND_API.G_RET_STS_SUCCESS;
930
931 -- START OF BODY OF API
932
933 VALIDATE_EXTN(P_LOAN_EXT_ID, 'APPROVE');
934
935 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Querying extension details...');
936
937 open loan_ext_cur(P_LOAN_EXT_ID);
938 fetch loan_ext_cur
939 into l_LOAN_ID,
940 l_OLD_INSTALLMENTS,
941 l_NEW_TERM,
942 l_NEW_TERM_PERIOD,
943 l_NEW_BALLOON_TYPE,
944 l_NEW_BALLOON_AMOUNT,
945 l_NEW_AMORT_TERM,
946 l_NEW_MATURITY_DATE,
947 l_NEW_INSTALLMENTS,
948 l_EXT_RATE,
949 l_EXT_SPREAD,
950 l_EXT_IO_FLAG,
951 l_EXT_INDEX_DATE,
952 l_OBJECT_VERSION_NUMBER,
953 l_TERM_ID,
954 l_customized,
955 l_description,
956 l_EXT_TERM,
957 l_EXT_TERM_PERIOD;
958 close loan_ext_cur;
959
960 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_ID: ' || l_LOAN_ID);
961 LogMessage(FND_LOG.LEVEL_STATEMENT, 'OLD_INSTALLMENTS: ' || l_OLD_INSTALLMENTS);
962 LogMessage(FND_LOG.LEVEL_STATEMENT, 'NEW_TERM: ' || l_NEW_TERM);
963 LogMessage(FND_LOG.LEVEL_STATEMENT, 'NEW_TERM_PERIOD: ' || l_NEW_TERM_PERIOD);
964 LogMessage(FND_LOG.LEVEL_STATEMENT, 'NEW_BALLOON_TYPE: ' || l_NEW_BALLOON_TYPE);
965 LogMessage(FND_LOG.LEVEL_STATEMENT, 'NEW_BALLOON_AMOUNT: ' || l_NEW_BALLOON_AMOUNT);
966 LogMessage(FND_LOG.LEVEL_STATEMENT, 'NEW_AMORT_TERM: ' || l_NEW_AMORT_TERM);
967 LogMessage(FND_LOG.LEVEL_STATEMENT, 'NEW_MATURITY_DATE: ' || l_NEW_MATURITY_DATE);
968 LogMessage(FND_LOG.LEVEL_STATEMENT, 'NEW_INSTALLMENTS: ' || l_NEW_INSTALLMENTS);
969 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_RATE: ' || l_EXT_RATE);
970 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_SPREAD: ' || l_EXT_SPREAD);
971 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_IO_FLAG: ' || l_EXT_IO_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 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_description: ' || l_description);
977 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_EXT_TERM: ' || l_EXT_TERM);
978 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_EXT_TERM_PERIOD: ' || l_EXT_TERM_PERIOD);
979
980 l_loan_header_rec.loan_id := l_LOAN_ID;
981 l_loan_header_rec.loan_term := l_NEW_TERM;
982 l_loan_header_rec.LOAN_TERM_PERIOD := l_NEW_TERM_PERIOD;
983 l_loan_header_rec.BALLOON_PAYMENT_TYPE := l_NEW_BALLOON_TYPE;
984 l_loan_header_rec.BALLOON_PAYMENT_AMOUNT := l_NEW_BALLOON_AMOUNT;
985 l_loan_header_rec.AMORTIZED_TERM := l_NEW_AMORT_TERM;
986 l_loan_header_rec.AMORTIZED_TERM_PERIOD := l_NEW_TERM_PERIOD;
987 l_loan_header_rec.LOAN_MATURITY_DATE := l_NEW_MATURITY_DATE;
988
989 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Updating loan...');
990 LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER,
991 P_LOAN_HEADER_REC => l_loan_header_rec,
992 P_INIT_MSG_LIST => FND_API.G_FALSE,
993 X_RETURN_STATUS => l_return_status,
994 X_MSG_COUNT => l_msg_count,
995 X_MSG_DATA => l_msg_data);
996 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done');
997
998 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Synching rate schedule...');
999
1000 -- finding right rate row and update it
1001 i := 0;
1002 OPEN c_rate_sched(l_TERM_ID);
1003 LOOP
1004 i := i + 1;
1005 FETCH c_rate_sched INTO
1006 l_RATE_ID,
1007 l_RATE,
1008 l_BEGIN_INSTALLMENT,
1009 l_END_INSTALLMENT,
1010 l_INDEX_RATE,
1011 l_SPREAD,
1012 l_INTEREST_ONLY_FLAG;
1013
1014 LogMessage(FND_LOG.LEVEL_STATEMENT, i || ') Rate ' || l_RATE || ': ' || l_BEGIN_INSTALLMENT || ' - ' || l_END_INSTALLMENT);
1015 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_INDEX_RATE = ' || l_INDEX_RATE);
1016 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_SPREAD = ' || l_SPREAD);
1017 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_INTEREST_ONLY_FLAG = ' || l_INTEREST_ONLY_FLAG);
1018
1019 if l_NEW_INSTALLMENTS > l_END_INSTALLMENT then
1020
1021 if (l_INDEX_RATE = l_EXT_RATE and
1022 l_SPREAD = l_EXT_SPREAD and
1023 l_INTEREST_ONLY_FLAG = l_EXT_IO_FLAG)
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 ,PHASE)
1056 VALUES
1057 (LNS_RATE_SCHEDULES_S.nextval
1058 ,l_TERM_ID
1059 ,l_EXT_RATE
1060 ,l_EXT_SPREAD
1061 ,(l_EXT_RATE+l_EXT_SPREAD)
1062 ,sysdate
1063 ,null
1064 ,lns_utility_pub.created_by
1065 ,sysdate
1066 ,lns_utility_pub.last_updated_by
1067 ,sysdate
1068 ,lns_utility_pub.LAST_UPDATE_LOGIN
1069 ,1
1070 ,l_EXT_INDEX_DATE
1071 ,l_END_INSTALLMENT+1
1072 ,l_NEW_INSTALLMENTS
1073 ,l_EXT_IO_FLAG
1074 ,'TERM');
1075
1076 end if;
1077
1078 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done');
1079 exit;
1080
1081 elsif l_NEW_INSTALLMENTS >= l_BEGIN_INSTALLMENT and l_NEW_INSTALLMENTS <= l_END_INSTALLMENT then
1082
1083 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Updating this row - set END_INSTALLMENT_NUMBER = ' || l_NEW_INSTALLMENTS);
1084
1085 update lns_rate_schedules
1086 set END_INSTALLMENT_NUMBER = l_NEW_INSTALLMENTS
1087 where term_id = l_TERM_ID and
1088 RATE_ID = l_RATE_ID;
1089
1090 exit;
1091
1092 elsif l_NEW_INSTALLMENTS < l_BEGIN_INSTALLMENT then
1093
1094 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Deleting this row');
1095
1096 delete from lns_rate_schedules
1097 where term_id = l_TERM_ID and
1098 RATE_ID = l_RATE_ID;
1099
1100 end if;
1101
1102 END LOOP;
1103
1104 CLOSE c_rate_sched;
1105 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done synching');
1106
1107 if l_customized = 'Y' then
1108
1109 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Deleting from LNS_CUSTOM_PAYMNT_SCHEDS rows with DUE_DATE < l_NEW_MATURITY_DATE...');
1110 delete from LNS_CUSTOM_PAYMNT_SCHEDS
1111 where loan_id = l_LOAN_ID
1112 and DUE_DATE > l_NEW_MATURITY_DATE;
1113 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done');
1114
1115 end if;
1116
1117 -- fix for bug 6724561
1118 l_last_billed_installment := LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(l_LOAN_ID);
1119 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_last_billed_installment: ' || l_last_billed_installment);
1120
1121 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Updating LNS_LOAN_EXTENSIONS...');
1122
1123 update LNS_LOAN_EXTENSIONS
1124 set STATUS = 'APPROVED',
1125 APPR_REJECT_DATE = sysdate,
1126 APPR_REJECT_BY = LNS_UTILITY_PUB.USER_ID,
1127 LAST_UPDATE_DATE = sysdate,
1128 LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY,
1129 LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN,
1130 LAST_BILLED_INSTALLMENT = l_last_billed_installment
1131 where LOAN_EXT_ID = P_LOAN_EXT_ID;
1132
1133 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done');
1134
1135 -- END OF BODY OF API
1136
1137 -- Retrieve agreement reason
1138 FND_MESSAGE.SET_NAME('LNS', 'LNS_TERM_EXT_AGR_REASON');
1139 FND_MESSAGE.SET_TOKEN('EXT', l_description);
1140 FND_MESSAGE.SET_TOKEN('EXT_TERM', l_EXT_TERM);
1141 FND_MESSAGE.SET_TOKEN('EXT_TERM_PERIOD', lns_utility_pub.get_lookup_meaning('PERIOD', l_EXT_TERM_PERIOD));
1142 FND_MESSAGE.SET_TOKEN('NEW_MATURITY_DATE', l_NEW_MATURITY_DATE);
1143 FND_MSG_PUB.Add;
1144 l_agreement_reason := FND_MSG_PUB.Get(p_encoded => 'F');
1145 FND_MSG_PUB.DELETE_MSG(FND_MSG_PUB.COUNT_MSG);
1146
1147 LNS_REP_UTILS.STORE_LOAN_AGREEMENT_CP(l_LOAN_ID, l_agreement_reason);
1148
1149 if P_COMMIT = FND_API.G_TRUE then
1150 COMMIT WORK;
1151 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Commited');
1152 end if;
1153
1154 x_return_status := FND_API.G_RET_STS_SUCCESS;
1155
1156 -- Standard call to get message count and if count is 1, get message info
1157 FND_MSG_PUB.Count_And_Get(
1158 p_encoded => FND_API.G_FALSE,
1159 p_count => x_msg_count,
1160 p_data => x_msg_data);
1161
1162 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1163
1164 EXCEPTION
1165 WHEN FND_API.G_EXC_ERROR THEN
1166 ROLLBACK TO APPROVE_LOAN_EXTENSION;
1167 x_return_status := FND_API.G_RET_STS_ERROR;
1168 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1169 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
1170 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1171 ROLLBACK TO APPROVE_LOAN_EXTENSION;
1172 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1173 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1174 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
1175 WHEN OTHERS THEN
1176 ROLLBACK TO APPROVE_LOAN_EXTENSION;
1177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1178 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1179 FND_MSG_PUB.Add_Exc_Msg(l_api_name);
1180 END IF;
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 END;
1184
1185
1186
1187
1188 /*========================================================================
1189 | PUBLIC PROCEDURE REJECT_LOAN_EXTENSION
1190 |
1191 | DESCRIPTION
1192 | This procedure rejects loan extension. No changes is made in lns_loan_headers_all table
1193 |
1194 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1195 | None
1196 |
1197 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1198 | LogMessage
1199 |
1200 | PARAMETERS
1201 | P_API_VERSION IN Standard in parameter
1202 | P_INIT_MSG_LIST IN Standard in parameter
1203 | P_COMMIT IN Standard in parameter
1204 | P_VALIDATION_LEVEL IN Standard in parameter
1205 | P_LOAN_EXT_ID IN Loan extension ID
1206 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
1207 | X_MSG_COUNT OUT NOCOPY Standard out parameter
1208 | X_MSG_DATA OUT NOCOPY Standard out parameter
1209 |
1210 | KNOWN ISSUES
1211 | None
1212 |
1213 | NOTES
1214 |
1215 | MODIFICATION HISTORY
1216 | Date Author Description of Changes
1217 | 09-25-2007 scherkas Created
1218 |
1219 *=======================================================================*/
1220 PROCEDURE REJECT_LOAN_EXTENSION(
1221 P_API_VERSION IN NUMBER,
1222 P_INIT_MSG_LIST IN VARCHAR2,
1223 P_COMMIT IN VARCHAR2,
1224 P_VALIDATION_LEVEL IN NUMBER,
1225 P_LOAN_EXT_ID IN NUMBER,
1226 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1227 X_MSG_COUNT OUT NOCOPY NUMBER,
1228 X_MSG_DATA OUT NOCOPY VARCHAR2)
1229 IS
1230
1231 /*-----------------------------------------------------------------------+
1232 | Local Variable Declarations and initializations |
1233 +-----------------------------------------------------------------------*/
1234
1235 l_api_name CONSTANT VARCHAR2(30) := 'REJECT_LOAN_EXTENSION';
1236 l_api_version CONSTANT NUMBER := 1.0;
1237 l_return_status VARCHAR2(1);
1238 l_msg_count NUMBER;
1239 l_msg_data VARCHAR2(32767);
1240
1241 /*-----------------------------------------------------------------------+
1242 | Cursor Declarations |
1243 +-----------------------------------------------------------------------*/
1244
1245 BEGIN
1246
1247 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1248
1249 -- Standard start of API savepoint
1250 SAVEPOINT REJECT_LOAN_EXTENSION;
1251 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Savepoint is established');
1252
1253 -- Standard call to check for call compatibility
1254 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1255 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1256 END IF;
1257
1258 -- Initialize message list if p_init_msg_list is set to TRUE
1259 --IF FND_API.To_Boolean(p_init_msg_list) THEN
1260 -- FND_MSG_PUB.initialize;
1261 --END IF;
1262
1263 -- Initialize API return status to success
1264 l_return_status := FND_API.G_RET_STS_SUCCESS;
1265
1266 -- START OF BODY OF API
1267
1268 VALIDATE_EXTN(P_LOAN_EXT_ID, 'REJECT');
1269
1270 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Updating LNS_LOAN_EXTENSIONS...');
1271
1272 update LNS_LOAN_EXTENSIONS
1273 set STATUS = 'REJECTED',
1274 APPR_REJECT_DATE = sysdate,
1275 APPR_REJECT_BY = LNS_UTILITY_PUB.USER_ID,
1276 LAST_UPDATE_DATE = sysdate,
1277 LAST_UPDATED_BY = LNS_UTILITY_PUB.LAST_UPDATED_BY,
1278 LAST_UPDATE_LOGIN = LNS_UTILITY_PUB.LAST_UPDATE_LOGIN
1279 where LOAN_EXT_ID = P_LOAN_EXT_ID;
1280
1281 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done');
1282
1283 -- END OF BODY OF API
1284
1285 if P_COMMIT = FND_API.G_TRUE then
1286 COMMIT WORK;
1287 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Commited');
1288 end if;
1289
1290 x_return_status := FND_API.G_RET_STS_SUCCESS;
1291
1292 -- Standard call to get message count and if count is 1, get message info
1293 FND_MSG_PUB.Count_And_Get(
1294 p_encoded => FND_API.G_FALSE,
1295 p_count => x_msg_count,
1296 p_data => x_msg_data);
1297
1298 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1299
1300 EXCEPTION
1301 WHEN FND_API.G_EXC_ERROR THEN
1302 ROLLBACK TO REJECT_LOAN_EXTENSION;
1303 x_return_status := FND_API.G_RET_STS_ERROR;
1304 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1305 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
1306 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1307 ROLLBACK TO REJECT_LOAN_EXTENSION;
1308 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1309 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1310 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
1311 WHEN OTHERS THEN
1312 ROLLBACK TO REJECT_LOAN_EXTENSION;
1313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1314 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1315 FND_MSG_PUB.Add_Exc_Msg(l_api_name);
1316 END IF;
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 END;
1320
1321
1322
1323
1324 /*========================================================================
1325 | PUBLIC PROCEDURE CALC_NEW_TERMS
1326 |
1327 | DESCRIPTION
1328 | This procedure calculates and returns new loan terms based on input extension loan term data.
1329 |
1330 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1331 | None
1332 |
1333 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1334 | LogMessage
1335 |
1336 | PARAMETERS
1337 | P_API_VERSION IN Standard in parameter
1338 | P_INIT_MSG_LIST IN Standard in parameter
1339 | P_COMMIT IN Standard in parameter
1340 | P_VALIDATION_LEVEL IN Standard in parameter
1341 | P_EXT_LOAN_REC IN OUT NOCOPY LNS_EXT_LOAN_PUB.NEW_TERM_REC record
1342 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
1343 | X_MSG_COUNT OUT NOCOPY Standard out parameter
1344 | X_MSG_DATA OUT NOCOPY Standard out parameter
1345 |
1346 | KNOWN ISSUES
1347 | None
1348 |
1349 | NOTES
1350 |
1351 | MODIFICATION HISTORY
1352 | Date Author Description of Changes
1353 | 09-25-2007 scherkas Created
1354 |
1355 *=======================================================================*/
1356 PROCEDURE CALC_NEW_TERMS(
1357 P_API_VERSION IN NUMBER,
1358 P_INIT_MSG_LIST IN VARCHAR2,
1359 P_COMMIT IN VARCHAR2,
1360 P_VALIDATION_LEVEL IN NUMBER,
1361 P_NEW_TERM_REC IN OUT NOCOPY LNS_EXT_LOAN_PUB.NEW_TERM_REC,
1362 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1363 X_MSG_COUNT OUT NOCOPY NUMBER,
1364 X_MSG_DATA OUT NOCOPY VARCHAR2)
1365 IS
1366
1367 /*-----------------------------------------------------------------------+
1368 | Local Variable Declarations and initializations |
1369 +-----------------------------------------------------------------------*/
1370
1371 l_api_name CONSTANT VARCHAR2(30) := 'CALC_NEW_TERMS';
1372 l_api_version CONSTANT NUMBER := 1.0;
1373 l_return_status VARCHAR2(1);
1374 l_msg_count NUMBER;
1375 l_msg_data VARCHAR2(32767);
1376
1377 l_loan_start_date date;
1378 l_loan_payment_frequency VARCHAR2(30);
1379 l_extend_installments number;
1380 l_term1 number;
1381 l_ext_term1 number;
1382 l_am_term1 number;
1383 l_ext_am_term1 number;
1384 l_term_id number;
1385 l_first_payment_date date;
1386 l_intervals number;
1387 l_pay_in_arrears varchar2(1);
1388 l_pay_in_arrears_bool boolean;
1389 l_prin_first_pay_date date;
1390 l_prin_intervals number;
1391 l_prin_payment_frequency varchar2(30);
1392 l_prin_pay_in_arrears varchar2(1);
1393 l_prin_pay_in_arrears_bool boolean;
1394 l_pay_calc_method varchar2(30);
1395 l_amortization_frequency varchar2(30);
1396 l_customized VARCHAR2(1);
1397
1398 l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
1399 l_freq_schedule_tbl LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL;
1400 l_prin_freq_schedule_tbl LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL;
1401 l_int_freq_schedule_tbl LNS_FIN_UTILS.FREQUENCY_SCHEDULE_TBL;
1402
1403 /*-----------------------------------------------------------------------+
1404 | Cursor Declarations |
1405 +-----------------------------------------------------------------------*/
1406
1407 -- query existent loan data
1408 CURSOR loan_cur(P_LOAN_ID number) IS
1409 select
1410 loan.loan_start_date,
1411 loan.loan_term,
1412 loan.LOAN_TERM_PERIOD,
1413 loan.BALLOON_PAYMENT_TYPE,
1414 loan.BALLOON_PAYMENT_AMOUNT,
1415 loan.AMORTIZED_TERM,
1416 loan.LOAN_MATURITY_DATE,
1417 term.loan_payment_frequency,
1418 term.term_id,
1419 term.amortization_frequency,
1420 trunc(term.first_payment_date),
1421 decode(trunc(term.first_payment_date) - trunc(loan.loan_start_date), 0, 'N', 'Y'), -- calculate in advance or arrears
1422 nvl(term.PAYMENT_CALC_METHOD, 'EQUAL_PAYMENT'),
1423 trunc(nvl(term.prin_first_pay_date, term.first_payment_date)),
1424 nvl(term.prin_payment_frequency, term.loan_payment_frequency),
1425 decode(trunc(nvl(term.prin_first_pay_date, term.first_payment_date)) - trunc(loan.loan_start_date), 0, 'N', 'Y'),
1426 nvl(loan.custom_payments_flag, 'N')
1427 from lns_loan_headers_all loan,
1428 lns_terms term
1429 where loan.loan_id = P_LOAN_ID and
1430 loan.loan_id = term.loan_id;
1431
1432 -- query max installment number
1433 CURSOR rate_sched_cur(P_TERM_ID number) IS
1434 select max(end_installment_number)
1435 from LNS_RATE_SCHEDULES
1436 where term_id = P_TERM_ID and
1437 phase = 'TERM' and
1438 trunc(nvl(END_DATE_ACTIVE,(sysdate+1))) > trunc(sysdate);
1439
1440 -- query count of custom schedule rows with DUE_DATE < l_NEW_MATURITY_DATE
1441 CURSOR custom_sched_count(P_LOAN_ID number, P_MATURITY_DATE date) IS
1442 select count(1)
1443 from LNS_CUSTOM_PAYMNT_SCHEDS
1444 where loan_id = P_LOAN_ID
1445 and DUE_DATE <= P_MATURITY_DATE;
1446
1447 BEGIN
1448
1449 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1450
1451 -- Standard start of API savepoint
1452 SAVEPOINT CALC_NEW_TERMS;
1453 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Savepoint is established');
1454
1455 -- Standard call to check for call compatibility
1456 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1457 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1458 END IF;
1459
1460 -- Initialize message list if p_init_msg_list is set to TRUE
1461 --IF FND_API.To_Boolean(p_init_msg_list) THEN
1462 -- FND_MSG_PUB.initialize;
1463 --END IF;
1464
1465 -- Initialize API return status to success
1466 l_return_status := FND_API.G_RET_STS_SUCCESS;
1467
1468 -- START OF BODY OF API
1469
1470 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Input:');
1471 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'LOAN_ID:' || P_NEW_TERM_REC.LOAN_ID);
1472 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_TERM:' || P_NEW_TERM_REC.EXT_TERM);
1473 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_BALLOON_TYPE:' || P_NEW_TERM_REC.EXT_BALLOON_TYPE);
1474 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_BALLOON_AMOUNT:' || P_NEW_TERM_REC.EXT_BALLOON_AMOUNT);
1475 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_AMORT_TERM:' || P_NEW_TERM_REC.EXT_AMORT_TERM);
1476 LogMessage(FND_LOG.LEVEL_STATEMENT, 'EXT_TERM_PERIOD:' || P_NEW_TERM_REC.EXT_TERM_PERIOD);
1477
1478 open loan_cur(P_NEW_TERM_REC.LOAN_ID);
1479 fetch loan_cur
1480 into l_loan_start_date,
1481 P_NEW_TERM_REC.OLD_TERM,
1482 P_NEW_TERM_REC.OLD_TERM_PERIOD,
1483 P_NEW_TERM_REC.OLD_BALLOON_TYPE,
1484 P_NEW_TERM_REC.OLD_BALLOON_AMOUNT,
1485 P_NEW_TERM_REC.OLD_AMORT_TERM,
1489 l_amortization_frequency,
1486 P_NEW_TERM_REC.OLD_MATURITY_DATE,
1487 l_loan_payment_frequency,
1488 l_term_id,
1490 l_first_payment_date,
1491 l_pay_in_arrears,
1492 l_pay_calc_method,
1493 l_prin_first_pay_date,
1494 l_prin_payment_frequency,
1495 l_prin_pay_in_arrears,
1496 l_customized;
1497 close loan_cur;
1498
1499 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Current loan term data:');
1500 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_start_date: '|| l_loan_start_date);
1501 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_term: '|| P_NEW_TERM_REC.OLD_TERM);
1502 LogMessage(FND_LOG.LEVEL_STATEMENT, 'BALLOON_TYPE: '|| P_NEW_TERM_REC.OLD_BALLOON_TYPE);
1503 LogMessage(FND_LOG.LEVEL_STATEMENT, 'BALLOON_AMOUNT: '|| P_NEW_TERM_REC.OLD_BALLOON_AMOUNT);
1504 LogMessage(FND_LOG.LEVEL_STATEMENT, 'AMORTIZED_TERM: '|| P_NEW_TERM_REC.OLD_AMORT_TERM);
1505 LogMessage(FND_LOG.LEVEL_STATEMENT, 'LOAN_TERM_PERIOD: '|| P_NEW_TERM_REC.OLD_TERM_PERIOD);
1506 LogMessage(FND_LOG.LEVEL_STATEMENT, 'maturity_date: '|| P_NEW_TERM_REC.OLD_MATURITY_DATE);
1507 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_payment_frequency: '|| l_loan_payment_frequency);
1508 LogMessage(FND_LOG.LEVEL_STATEMENT, 'term_id: '|| l_term_id);
1509 LogMessage(FND_LOG.LEVEL_STATEMENT, 'amortization_frequency: ' || l_amortization_frequency);
1510 LogMessage(FND_LOG.LEVEL_STATEMENT, 'first_payment_date: ' || l_first_payment_date);
1511 LogMessage(FND_LOG.LEVEL_STATEMENT, 'pay_in_arrears: ' || l_pay_in_arrears);
1512 LogMessage(FND_LOG.LEVEL_STATEMENT, 'pay_calc_method: ' || l_pay_calc_method);
1513 LogMessage(FND_LOG.LEVEL_STATEMENT, 'prin_first_pay_date: ' || l_prin_first_pay_date);
1514 LogMessage(FND_LOG.LEVEL_STATEMENT, 'prin_payment_frequency: ' || l_prin_payment_frequency);
1515 LogMessage(FND_LOG.LEVEL_STATEMENT, 'prin_pay_in_arrears: ' || l_prin_pay_in_arrears);
1516 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_customized: ' || l_customized);
1517
1518 open rate_sched_cur(l_term_id);
1519 fetch rate_sched_cur
1520 into P_NEW_TERM_REC.OLD_INSTALLMENTS;
1521 close rate_sched_cur;
1522
1523 LogMessage(FND_LOG.LEVEL_STATEMENT, 'INSTALLMENTS: '|| P_NEW_TERM_REC.OLD_INSTALLMENTS);
1524
1525 if P_NEW_TERM_REC.OLD_TERM_PERIOD = 'YEARS' then
1526 if P_NEW_TERM_REC.EXT_TERM_PERIOD = 'YEARS' then
1527 P_NEW_TERM_REC.NEW_TERM_PERIOD := 'YEARS';
1528 elsif P_NEW_TERM_REC.EXT_TERM_PERIOD = 'MONTHS' then
1529 P_NEW_TERM_REC.NEW_TERM_PERIOD := 'MONTHS';
1530 elsif P_NEW_TERM_REC.EXT_TERM_PERIOD = 'DAYS' then
1531 P_NEW_TERM_REC.NEW_TERM_PERIOD := 'DAYS';
1532 end if;
1533 elsif P_NEW_TERM_REC.OLD_TERM_PERIOD = 'MONTHS' then
1534 if P_NEW_TERM_REC.EXT_TERM_PERIOD = 'YEARS' then
1535 P_NEW_TERM_REC.NEW_TERM_PERIOD := 'MONTHS';
1536 elsif P_NEW_TERM_REC.EXT_TERM_PERIOD = 'MONTHS' then
1537 P_NEW_TERM_REC.NEW_TERM_PERIOD := 'MONTHS';
1538 elsif P_NEW_TERM_REC.EXT_TERM_PERIOD = 'DAYS' then
1539 P_NEW_TERM_REC.NEW_TERM_PERIOD := 'DAYS';
1540 end if;
1541 elsif P_NEW_TERM_REC.OLD_TERM_PERIOD = 'DAYS' then
1542 if P_NEW_TERM_REC.EXT_TERM_PERIOD = 'YEARS' then
1543 P_NEW_TERM_REC.NEW_TERM_PERIOD := 'DAYS';
1544 elsif P_NEW_TERM_REC.EXT_TERM_PERIOD = 'MONTHS' then
1545 P_NEW_TERM_REC.NEW_TERM_PERIOD := 'DAYS';
1546 elsif P_NEW_TERM_REC.EXT_TERM_PERIOD = 'DAYS' then
1547 P_NEW_TERM_REC.NEW_TERM_PERIOD := 'DAYS';
1548 end if;
1549 end if;
1550
1551 if P_NEW_TERM_REC.OLD_TERM_PERIOD = P_NEW_TERM_REC.EXT_TERM_PERIOD then
1552
1553 P_NEW_TERM_REC.NEW_TERM := P_NEW_TERM_REC.OLD_TERM + P_NEW_TERM_REC.EXT_TERM;
1554
1555 P_NEW_TERM_REC.NEW_BALLOON_TYPE := P_NEW_TERM_REC.EXT_BALLOON_TYPE;
1556 if P_NEW_TERM_REC.EXT_BALLOON_TYPE = 'TERM' then
1557 P_NEW_TERM_REC.NEW_AMORT_TERM := P_NEW_TERM_REC.OLD_AMORT_TERM + P_NEW_TERM_REC.EXT_AMORT_TERM;
1558 P_NEW_TERM_REC.NEW_BALLOON_AMOUNT := P_NEW_TERM_REC.OLD_BALLOON_AMOUNT;
1559 else
1560 P_NEW_TERM_REC.NEW_AMORT_TERM := P_NEW_TERM_REC.NEW_TERM;
1561 P_NEW_TERM_REC.NEW_BALLOON_AMOUNT := P_NEW_TERM_REC.EXT_BALLOON_AMOUNT;
1562 end if;
1563
1564 else
1565
1566 l_term1 := LNS_FIN_UTILS.intervalsInPeriod(
1567 p_period_number => P_NEW_TERM_REC.OLD_TERM
1568 ,p_period_type1 => P_NEW_TERM_REC.OLD_TERM_PERIOD
1569 ,p_period_type2 => P_NEW_TERM_REC.NEW_TERM_PERIOD);
1570
1571 l_ext_term1 := LNS_FIN_UTILS.intervalsInPeriod(
1572 p_period_number => P_NEW_TERM_REC.EXT_TERM
1573 ,p_period_type1 => P_NEW_TERM_REC.EXT_TERM_PERIOD
1574 ,p_period_type2 => P_NEW_TERM_REC.NEW_TERM_PERIOD);
1575
1576 P_NEW_TERM_REC.NEW_TERM := l_term1 + l_ext_term1;
1577
1578 P_NEW_TERM_REC.NEW_BALLOON_TYPE := P_NEW_TERM_REC.EXT_BALLOON_TYPE;
1579 if P_NEW_TERM_REC.EXT_BALLOON_TYPE = 'TERM' then
1580 l_am_term1 := LNS_FIN_UTILS.intervalsInPeriod(
1581 p_period_number => P_NEW_TERM_REC.OLD_AMORT_TERM
1582 ,p_period_type1 => P_NEW_TERM_REC.OLD_TERM_PERIOD
1583 ,p_period_type2 => P_NEW_TERM_REC.NEW_TERM_PERIOD);
1584
1585 l_ext_am_term1 := LNS_FIN_UTILS.intervalsInPeriod(
1586 p_period_number => P_NEW_TERM_REC.EXT_AMORT_TERM
1587 ,p_period_type1 => P_NEW_TERM_REC.EXT_TERM_PERIOD
1588 ,p_period_type2 => P_NEW_TERM_REC.NEW_TERM_PERIOD);
1589
1590 P_NEW_TERM_REC.NEW_AMORT_TERM := l_am_term1 + l_ext_am_term1;
1591 P_NEW_TERM_REC.NEW_BALLOON_AMOUNT := P_NEW_TERM_REC.OLD_BALLOON_AMOUNT;
1592 else
1593 P_NEW_TERM_REC.NEW_AMORT_TERM := P_NEW_TERM_REC.NEW_TERM;
1594 P_NEW_TERM_REC.NEW_BALLOON_AMOUNT := P_NEW_TERM_REC.EXT_BALLOON_AMOUNT;
1595 end if;
1596
1597 end if;
1598
1599 P_NEW_TERM_REC.NEW_MATURITY_DATE := lns_fin_utils.getMaturityDate(
1600 p_term => P_NEW_TERM_REC.NEW_TERM,
1601 p_term_period => P_NEW_TERM_REC.NEW_TERM_PERIOD,
1602 p_frequency => l_loan_payment_frequency,
1603 p_start_date => l_loan_start_date);
1604
1605
1606 if l_customized = 'N' then
1607
1608 -- calculating new number of installments
1609 if (l_pay_calc_method = 'SEPARATE_SCHEDULES') then
1610 /*
1611 if l_pay_in_arrears = 'Y' then
1612 l_pay_in_arrears_bool := true;
1613 else
1614 l_pay_in_arrears_bool := false;
1615 end if;
1616
1617 if l_prin_pay_in_arrears = 'Y' then
1618 l_prin_pay_in_arrears_bool := true;
1619 else
1620 l_prin_pay_in_arrears_bool := false;
1621 end if;
1622
1623 l_payment_tbl := LNS_FIN_UTILS.buildSIPPaymentSchedule(
1624 p_loan_start_date => l_loan_start_date
1625 ,p_loan_maturity_date => P_NEW_TERM_REC.NEW_MATURITY_DATE
1626 ,p_int_first_pay_date => l_first_payment_date
1627 ,p_int_num_intervals => l_intervals
1628 ,p_int_interval_type => l_loan_payment_frequency
1629 ,p_int_pay_in_arrears => l_pay_in_arrears_bool
1630 ,p_prin_first_pay_date => l_prin_first_pay_date
1631 ,p_prin_num_intervals => l_prin_intervals
1632 ,p_prin_interval_type => l_prin_payment_frequency
1633 ,p_prin_pay_in_arrears => l_prin_pay_in_arrears_bool);
1634 */
1635 l_int_freq_schedule_tbl := LNS_FIN_UTILS.fetchFreqSchedule(
1636 P_LOAN_ID => P_NEW_TERM_REC.LOAN_ID,
1637 P_PHASE => 'TERM',
1638 P_COMPONENT => 'INT');
1639
1640 l_prin_freq_schedule_tbl := LNS_FIN_UTILS.fetchFreqSchedule(
1641 P_LOAN_ID => P_NEW_TERM_REC.LOAN_ID,
1642 P_PHASE => 'TERM',
1643 P_COMPONENT => 'PRIN');
1644
1645 l_payment_tbl := LNS_FIN_UTILS.buildSIPPaymentSchedule(
1646 p_loan_start_date => l_loan_start_date,
1647 p_loan_maturity_date => P_NEW_TERM_REC.NEW_MATURITY_DATE,
1648 p_prin_freq_schedule_tbl => l_prin_freq_schedule_tbl,
1649 p_int_freq_schedule_tbl => l_int_freq_schedule_tbl);
1650
1651 P_NEW_TERM_REC.NEW_INSTALLMENTS := l_payment_tbl.count;
1652
1653 else
1654 /*
1655 if l_pay_in_arrears = 'Y' then
1656 l_pay_in_arrears_bool := true;
1657 else
1658 l_pay_in_arrears_bool := false;
1659 end if;
1660
1661 l_payment_tbl := LNS_FIN_UTILS.buildPaymentSchedule(
1662 p_loan_start_date => l_loan_start_date
1663 ,p_loan_maturity_date => P_NEW_TERM_REC.NEW_MATURITY_DATE
1664 ,p_first_pay_date => l_first_payment_date
1665 ,p_num_intervals => null
1666 ,p_interval_type => l_loan_payment_frequency
1667 ,p_pay_in_arrears => l_pay_in_arrears_bool);
1668 */
1669
1670 l_freq_schedule_tbl := LNS_FIN_UTILS.fetchFreqSchedule(
1671 P_LOAN_ID => P_NEW_TERM_REC.LOAN_ID,
1672 P_PHASE => 'TERM',
1673 P_COMPONENT => 'PRIN_INT');
1674
1675 l_payment_tbl := LNS_FIN_UTILS.buildPaymentSchedule(
1676 p_loan_start_date => l_loan_start_date,
1677 p_loan_maturity_date => P_NEW_TERM_REC.NEW_MATURITY_DATE,
1678 p_freq_schedule_tbl => l_freq_schedule_tbl);
1679
1680 P_NEW_TERM_REC.NEW_INSTALLMENTS := l_payment_tbl.count;
1681
1682 end if;
1683 else
1684
1685 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Querying number of custom schedule rows...');
1686 open custom_sched_count(P_NEW_TERM_REC.LOAN_ID, P_NEW_TERM_REC.NEW_MATURITY_DATE);
1687 fetch custom_sched_count
1688 into P_NEW_TERM_REC.NEW_INSTALLMENTS;
1689 close custom_sched_count;
1690
1691 end if;
1692
1693 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'New loan term data:');
1694 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'NEW_TERM: '|| P_NEW_TERM_REC.NEW_TERM);
1695 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'NEW_AMORT_TERM: '|| P_NEW_TERM_REC.NEW_AMORT_TERM);
1696 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'NEW_TERM_PERIOD: '|| P_NEW_TERM_REC.NEW_TERM_PERIOD);
1697 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'NEW_MATURITY_DATE: '|| P_NEW_TERM_REC.NEW_MATURITY_DATE);
1698 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'BEGIN_EXT_INSTAL_NUMBER: '|| P_NEW_TERM_REC.BEGIN_EXT_INSTAL_NUMBER);
1699 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'END_EXT_INSTAL_NUMBER: '|| P_NEW_TERM_REC.END_EXT_INSTAL_NUMBER);
1700 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'NEW_INSTALLMENTS: '|| P_NEW_TERM_REC.NEW_INSTALLMENTS);
1701
1702 if (P_NEW_TERM_REC.NEW_AMORT_TERM < P_NEW_TERM_REC.NEW_TERM) then
1703
1704 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Loan amortized term cannot be less than loan term.');
1705 FND_MESSAGE.SET_NAME('LNS', 'LNS_LOAN_TERM_INVALID');
1706 FND_MSG_PUB.Add;
1707 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
1708 RAISE FND_API.G_EXC_ERROR;
1709
1710 end if;
1711
1712 -- END OF BODY OF API
1713 /*
1714 if P_COMMIT = FND_API.G_TRUE then
1715 COMMIT WORK;
1716 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Commited');
1717 end if;
1718 */
1719 x_return_status := FND_API.G_RET_STS_SUCCESS;
1720
1721 -- Standard call to get message count and if count is 1, get message info
1722 FND_MSG_PUB.Count_And_Get(
1723 p_encoded => FND_API.G_FALSE,
1724 p_count => x_msg_count,
1725 p_data => x_msg_data);
1726
1727 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1728
1729 EXCEPTION
1730 WHEN FND_API.G_EXC_ERROR THEN
1731 --ROLLBACK TO CALC_NEW_TERMS;
1732 x_return_status := FND_API.G_RET_STS_ERROR;
1733 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1734 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'In exception');
1735 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1736 --ROLLBACK TO CALC_NEW_TERMS;
1737 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1738 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1739 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'In exception');
1740 WHEN OTHERS THEN
1741 --ROLLBACK TO CALC_NEW_TERMS;
1742 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1743 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1744 FND_MSG_PUB.Add_Exc_Msg(l_api_name);
1745 END IF;
1746 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1747 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'In exception');
1748 END;
1749
1750
1751
1752
1753 BEGIN
1754 G_LOG_ENABLED := 'N';
1755 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
1756
1757 /* getting msg logging info */
1758 G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
1759 if (G_LOG_ENABLED = 'N') then
1760 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
1761 else
1762 G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
1763 end if;
1764
1765 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
1766 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_MSG_LEVEL: ' || G_MSG_LEVEL);
1767
1768 END;