[Home] [Help]
PACKAGE BODY: APPS.LNS_TERMS_PUB
Source
1 PACKAGE BODY LNS_TERMS_PUB AS
2 /*$Header: LNS_TERMS_PUBP_B.pls 120.8.12010000.6 2008/12/31 14:02:41 gparuchu ship $ */
3
4 --------------------------------------------
5 -- declaration of global variables and types
6 --------------------------------------------
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'LNS_TERMS_PUB';
9
10 --------------------------------------------------
11 -- declaration of private procedures and functions
12 --------------------------------------------------
13
14 PROCEDURE do_create_term (p_loan_term_rec IN OUT NOCOPY LOAN_TERM_REC_TYPE
15 ,x_term_id OUT NOCOPY NUMBER
16 ,x_return_status IN OUT NOCOPY VARCHAR2);
17
18 PROCEDURE do_update_term (p_loan_term_rec IN OUT NOCOPY LOAN_TERM_REC_TYPE
19 ,p_object_version_number IN OUT NOCOPY NUMBER
20 ,x_return_status IN OUT NOCOPY VARCHAR2);
21
22 procedure logMessage(log_level in number
23 ,module in varchar2
24 ,message in varchar2)
25 is
26
27 begin
28
29 IF log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
30 FND_LOG.STRING(log_level, module, message);
31 END IF;
32
33 end;
34
35
36 -----------------------------
37 -- body of private procedures
38 -----------------------------
39
40 /*===========================================================================+
41 | PROCEDURE
42 | do_create_term
43 |
44 | DESCRIPTION
45 | Creates term.
46 |
47 | SCOPE - PRIVATE
48 |
49 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
50 |
51 | ARGUMENTS : IN:
52 | OUT:
53 | x_term_id
54 | IN/OUT:
55 | p_loan_term_rec
56 | x_return_status
57 |
58 | RETURNS : NONE
59 |
60 | NOTES
61 |
62 | MODIFICATION HISTORY
63 |
64 | 21-Dec-2003 Karthik Ramachandran Created.
65 +===========================================================================*/
66 PROCEDURE do_create_term (p_loan_term_rec IN OUT NOCOPY LOAN_TERM_REC_TYPE
67 ,x_term_id OUT NOCOPY NUMBER
68 ,x_return_status IN OUT NOCOPY VARCHAR2) IS
69
70 l_term_id NUMBER;
71 l_rowid ROWID := NULL;
72 l_dummy VARCHAR2(1);
73 l_msg_count NUMBER;
74 l_msg_data VARCHAR2(2000);
75 l_loan_start_date DATE;
76 l_pmt_start_date DATE;
77
78 Cursor c_get_loan_start_date(p_loan_id number) is
79 select loan_start_date from lns_loan_headers_all
80 where loan_id=p_loan_id;
81
82 BEGIN
83 l_term_id := p_loan_term_rec.term_id;
84
85 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, ' - Begin do_create_term procedure');
86 -- if primary key value is passed, check for uniqueness.
87 IF l_term_id IS NOT NULL AND
88 l_term_id <> FND_API.G_MISS_NUM
89 THEN
90 BEGIN
91 SELECT 'Y'
92 INTO l_dummy
93 FROM LNS_TERMS
94 WHERE term_id = l_term_id;
95
96 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_DUPLICATE_COLUMN');
97 FND_MESSAGE.SET_TOKEN('COLUMN', 'term_id');
98 FND_MSG_PUB.ADD;
99 RAISE FND_API.G_EXC_ERROR;
100
101 EXCEPTION
102 WHEN NO_DATA_FOUND THEN
103 NULL;
104 END;
105 END IF;
106
107 -- begin raverma 01-24-2006 add validation for day count
108 validate_term (p_init_msg_list => FND_API.G_FALSE
109 ,p_loan_term_rec => p_loan_term_rec
110 ,x_return_status => x_return_status
111 ,x_msg_count => l_msg_count
112 ,x_msg_data => l_msg_data);
113
114 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
115 RAISE FND_API.G_EXC_ERROR;
116 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
117 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
118 END IF;
119
120 --Set default values for billing
121 IF (p_loan_term_rec.payment_application_order is null) THEN
122 p_loan_term_rec.payment_application_order := 'INT_PRIN';
123 END IF;
124 IF (p_loan_term_rec.loan_payment_frequency is null) THEN
125 p_loan_term_rec.loan_payment_frequency := 'MONTHLY';
126 END IF;
127
128 IF (p_loan_term_rec.first_payment_date is null) THEN
129 open c_get_loan_start_date(p_loan_term_rec.loan_id);
130 fetch c_get_loan_start_date into l_loan_start_date;
131 close c_get_loan_start_date;
132 if (l_loan_start_date is not null) then
133 l_pmt_start_date := lns_fin_utils.getNextDate(p_date => l_loan_start_date
134 ,p_interval_type => p_loan_term_rec.loan_payment_frequency
135 ,p_direction => 1);
136 end if;
137 if (l_pmt_start_date is not null) then
138 p_loan_term_rec.first_payment_date := l_pmt_start_date;
139 end if;
140 END IF;
141
142 IF (p_loan_term_rec.next_payment_due_date is null) THEN
143 p_loan_term_rec.next_payment_due_date := p_loan_term_rec.first_payment_date;
144 END IF;
145
146 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, ' - In do_create_term procedure: Before call to LNS_TERMS_PKG.Insert_Row');
147
148 -- call table-handler.
149 LNS_TERMS_PKG.Insert_Row (X_TERM_ID => p_loan_term_rec.term_id
150 ,X_LOAN_ID => p_loan_term_rec.loan_id
151 ,X_OBJECT_VERSION_NUMBER => 1
152 ,X_DAY_COUNT_METHOD => p_loan_term_rec.day_count_method
153 ,X_BASED_ON_BALANCE => p_loan_term_rec.based_on_balance
154 ,X_FIRST_RATE_CHANGE_DATE => p_loan_term_rec.first_rate_change_date
155 ,X_NEXT_RATE_CHANGE_DATE => p_loan_term_rec.next_rate_change_date
156 ,X_PERCENT_INCREASE => p_loan_term_rec.percent_increase
157 ,X_PERCENT_INCREASE_TERM => p_loan_term_rec.percent_increase_term
158 ,X_PAYMENT_APPLICATION_ORDER => p_loan_term_rec.payment_application_order
159 ,X_PREPAY_PENALTY_FLAG => p_loan_term_rec.prepay_penalty_flag
160 ,X_PREPAY_PENALTY_DATE => p_loan_term_rec.prepay_penalty_date
161 ,X_CEILING_RATE => p_loan_term_rec.ceiling_rate
162 ,X_FLOOR_RATE => p_loan_term_rec.floor_rate
163 ,X_DELINQUENCY_THRESHOLD_NUMBER => p_loan_term_rec.delinquency_threshold_number
164 ,X_DELINQUENCY_THRESHOLD_AMOUNT => p_loan_term_rec.delinquency_threshold_amount
165 ,X_CALCULATION_METHOD => p_loan_term_rec.calculation_method
166 ,X_REAMORTIZE_UNDER_PAYMENT => p_loan_term_rec.reamortize_under_payment
167 ,X_REAMORTIZE_OVER_PAYMENT => p_loan_term_rec.reamortize_over_payment
168 ,X_REAMORTIZE_WITH_INTEREST => p_loan_term_rec.reamortize_with_interest
169 ,X_LOAN_PAYMENT_FREQUENCY => p_loan_term_rec.loan_payment_frequency
170 ,X_INTEREST_COMPOUNDING_FREQ => p_loan_term_rec.interest_compounding_freq
171 ,X_AMORTIZATION_FREQUENCY => p_loan_term_rec.amortization_frequency
172 ,X_NUMBER_GRACE_DAYS => p_loan_term_rec.number_grace_days
173 ,X_RATE_TYPE => p_loan_term_rec.rate_type
174 ,X_INDEX_NAME => p_loan_term_rec.index_name
175 ,X_ADJUSTMENT_FREQUENCY => p_loan_term_rec.adjustment_frequency
176 ,X_ADJUSTMENT_FREQUENCY_TYPE => p_loan_term_rec.adjustment_frequency_type
177 ,X_FIXED_RATE_PERIOD => p_loan_term_rec.fixed_rate_period
178 ,X_FIXED_RATE_PERIOD_TYPE => p_loan_term_rec.fixed_rate_period_type
179 ,X_FIRST_PAYMENT_DATE => p_loan_term_rec.first_payment_date
180 ,X_NEXT_PAYMENT_DUE_DATE => p_loan_term_rec.next_payment_due_date
181 ,X_OPEN_FIRST_PAYMENT_DATE => p_loan_term_rec.open_first_payment_date
182 ,X_OPEN_PAYMENT_FREQUENCY => p_loan_term_rec.open_payment_frequency
183 ,X_OPEN_NEXT_PAYMENT_DATE => p_loan_term_rec.open_next_payment_date
184 ,X_LOCK_IN_DATE => p_loan_term_rec.lock_in_date
185 ,X_LOCK_TO_DATE => p_loan_term_rec.lock_to_date
186 ,X_RATE_CHANGE_FREQUENCY => p_loan_term_rec.rate_change_frequency
187 ,X_INDEX_RATE_ID => p_loan_term_rec.index_rate_id
188 ,X_PERCENT_INCREASE_LIFE => p_loan_term_rec.PERCENT_INCREASE_LIFE
189 ,X_FIRST_PERCENT_INCREASE => p_loan_term_rec.FIRST_PERCENT_INCREASE
190 ,X_OPEN_PERCENT_INCREASE => p_loan_term_rec.OPEN_PERCENT_INCREASE
191 ,X_OPEN_PERCENT_INCREASE_LIFE => p_loan_term_rec.OPEN_PERCENT_INCREASE_LIFE
192 ,X_OPEN_FIRST_PERCENT_INCREASE => p_loan_term_rec.OPEN_FIRST_PERCENT_INCREASE
193 ,X_PMT_APPL_ORDER_SCOPE => p_loan_term_rec.PMT_APPL_ORDER_SCOPE
194 ,X_OPEN_CEILING_RATE => p_loan_term_rec.OPEN_CEILING_RATE
195 ,X_OPEN_FLOOR_RATE => p_loan_term_rec.OPEN_FLOOR_RATE
196 ,X_OPEN_INDEX_DATE => p_loan_term_rec.OPEN_INDEX_DATE
197 ,X_TERM_INDEX_DATE => p_loan_term_rec.TERM_INDEX_DATE
198 ,X_OPEN_PROJECTED_RATE => p_loan_term_rec.OPEN_PROJECTED_RATE
199 ,X_TERM_PROJECTED_RATE => p_loan_term_rec.TERM_PROJECTED_RATE
200 ,X_PAYMENT_CALC_METHOD => p_loan_term_rec.PAYMENT_CALC_METHOD
201 ,X_CUSTOM_CALC_METHOD => p_loan_term_rec.CUSTOM_CALC_METHOD
202 ,X_ORIG_PAY_CALC_METHOD => p_loan_term_rec.ORIG_PAY_CALC_METHOD
203 ,X_PRIN_FIRST_PAY_DATE => p_loan_term_rec.PRIN_FIRST_PAY_DATE
204 ,X_PRIN_PAYMENT_FREQUENCY => p_loan_term_rec.PRIN_PAYMENT_FREQUENCY
205 ,X_PENAL_INT_RATE => p_loan_term_rec.PENAL_INT_RATE
206 ,X_PENAL_INT_GRACE_DAYS => p_loan_term_rec.PENAL_INT_GRACE_DAYS
207 );
208
209 x_term_id := p_loan_term_rec.term_id;
210
211 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, ' - In do_create_term procedure: After call to LNS_TERMS.Insert_Row');
212
213 END do_create_term;
214
215
216 /*===========================================================================+
217 | PROCEDURE
218 | do_update_term
219 |
220 | DESCRIPTION
221 | Updates term.
222 |
223 | SCOPE - PRIVATE
224 |
225 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
226 |
227 | ARGUMENTS : IN:
228 | OUT:
229 | IN/OUT:
230 | p_loan_term_rec
231 | p_object_version_number
232 | x_return_status
233 |
234 | RETURNS : NONE
235 |
236 | NOTES
237 |
238 | MODIFICATION HISTORY
239 |
240 | 21-Dec-2003 Karthik Ramachandran Created.
241 +===========================================================================*/
242
243 PROCEDURE do_update_term(p_loan_term_rec IN OUT NOCOPY LOAN_TERM_REC_TYPE
244 ,p_object_version_number IN OUT NOCOPY NUMBER
245 ,x_return_status IN OUT NOCOPY VARCHAR2) IS
246
247 l_object_version_number NUMBER;
248 l_rowid ROWID;
249 ldup_rowid ROWID;
250 l_msg_count NUMBER;
251 l_msg_data VARCHAR2(2000);
252
253 BEGIN
254
255 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, ' - Begin do_update_term procedure');
256
257 -- check whether record has been updated by another user. If not, lock it.
258 BEGIN
259 SELECT OBJECT_VERSION_NUMBER,
260 ROWID
261 INTO l_object_version_number,
262 l_rowid
263 FROM LNS_TERMS
264 WHERE TERM_ID = p_loan_term_rec.term_id
265 FOR UPDATE OF TERM_ID NOWAIT;
266
267 IF NOT
268 (
269 (p_object_version_number IS NULL AND l_object_version_number IS NULL)
270 OR
271 (p_object_version_number IS NOT NULL AND
272 l_object_version_number IS NOT NULL AND
273 p_object_version_number = l_object_version_number
274 )
275 )
276 THEN
277 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_RECORD_CHANGED');
278 FND_MESSAGE.SET_TOKEN('TABLE', 'lns_terms');
279 FND_MSG_PUB.ADD;
280 RAISE FND_API.G_EXC_ERROR;
281 END IF;
282
283
284 EXCEPTION WHEN NO_DATA_FOUND THEN
285 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_NO_RECORD');
286 FND_MESSAGE.SET_TOKEN('RECORD', 'loan_term_rec');
287 FND_MESSAGE.SET_TOKEN('VALUE', NVL(TO_CHAR(p_loan_term_rec.term_id), 'null'));
288 FND_MSG_PUB.ADD;
289 RAISE FND_API.G_EXC_ERROR;
290 END;
291
292 -- begin raverma 01-24-2006 add validation for day count
293 validate_term (p_init_msg_list => FND_API.G_FALSE
294 ,p_loan_term_rec => p_loan_term_rec
295 ,x_return_status => x_return_status
296 ,x_msg_count => l_msg_count
297 ,x_msg_data => l_msg_data);
298
299 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
300 RAISE FND_API.G_EXC_ERROR;
301 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
302 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
303 END IF;
304
305 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, ' - In do_update_term procedure: Before call to LNS_TERMS_PKG.Update_Row');
306
307 p_object_version_number := nvl(l_object_version_number, 1) + 1;
308
309 --Call to table-handler
310 LNS_TERMS_PKG.Update_Row (
311 X_Rowid => l_rowid,
312 X_TERM_ID => p_loan_term_rec.term_id,
313 X_LOAN_ID => p_loan_term_rec.loan_id,
314 X_OBJECT_VERSION_NUMBER => p_object_version_number,
315 X_DAY_COUNT_METHOD => p_loan_term_rec.day_count_method,
316 X_BASED_ON_BALANCE => p_loan_term_rec.based_on_balance,
317 X_FIRST_RATE_CHANGE_DATE => p_loan_term_rec.first_rate_change_date,
318 X_NEXT_RATE_CHANGE_DATE => p_loan_term_rec.next_rate_change_date,
319 X_PERCENT_INCREASE => p_loan_term_rec.percent_increase,
320 X_PERCENT_INCREASE_TERM => p_loan_term_rec.percent_increase_term,
321 X_PAYMENT_APPLICATION_ORDER => p_loan_term_rec.payment_application_order,
322 X_PREPAY_PENALTY_FLAG => p_loan_term_rec.prepay_penalty_flag,
323 X_PREPAY_PENALTY_DATE => p_loan_term_rec.prepay_penalty_date,
324 X_CEILING_RATE => p_loan_term_rec.ceiling_rate,
325 X_FLOOR_RATE => p_loan_term_rec.floor_rate,
326 X_DELINQUENCY_THRESHOLD_NUMBER => p_loan_term_rec.delinquency_threshold_number,
327 X_DELINQUENCY_THRESHOLD_AMOUNT => p_loan_term_rec.delinquency_threshold_amount,
328 X_CALCULATION_METHOD => p_loan_term_rec.calculation_method,
329 X_REAMORTIZE_UNDER_PAYMENT => p_loan_term_rec.reamortize_under_payment,
330 X_REAMORTIZE_OVER_PAYMENT => p_loan_term_rec.reamortize_over_payment,
331 X_REAMORTIZE_WITH_INTEREST => p_loan_term_rec.reamortize_with_interest,
332 X_LOAN_PAYMENT_FREQUENCY => p_loan_term_rec.loan_payment_frequency,
333 X_INTEREST_COMPOUNDING_FREQ => p_loan_term_rec.interest_compounding_freq,
334 X_AMORTIZATION_FREQUENCY => p_loan_term_rec.amortization_frequency,
335 X_NUMBER_GRACE_DAYS => p_loan_term_rec.number_grace_days,
336 X_RATE_TYPE => p_loan_term_rec.rate_type,
337 X_INDEX_NAME => p_loan_term_rec.index_name,
338 X_ADJUSTMENT_FREQUENCY => p_loan_term_rec.adjustment_frequency,
339 X_ADJUSTMENT_FREQUENCY_TYPE => p_loan_term_rec.adjustment_frequency_type,
340 X_FIXED_RATE_PERIOD => p_loan_term_rec.fixed_rate_period,
341 X_FIXED_RATE_PERIOD_TYPE => p_loan_term_rec.fixed_rate_period_type,
342 X_FIRST_PAYMENT_DATE => p_loan_term_rec.first_payment_date,
343 X_NEXT_PAYMENT_DUE_DATE => p_loan_term_rec.next_payment_due_date,
344 X_OPEN_FIRST_PAYMENT_DATE => p_loan_term_rec.open_first_payment_date,
345 X_OPEN_PAYMENT_FREQUENCY => p_loan_term_rec.open_payment_frequency,
346 X_OPEN_NEXT_PAYMENT_DATE => p_loan_term_rec.open_next_payment_date,
347 X_LOCK_IN_DATE => p_loan_term_rec.lock_in_date,
348 X_LOCK_TO_DATE => p_loan_term_rec.lock_to_date,
349 X_RATE_CHANGE_FREQUENCY => p_loan_term_rec.rate_change_frequency,
350 X_INDEX_RATE_ID => p_loan_term_rec.index_rate_id,
351 X_PERCENT_INCREASE_LIFE => p_loan_term_rec.PERCENT_INCREASE_LIFE,
352 X_FIRST_PERCENT_INCREASE => p_loan_term_rec.FIRST_PERCENT_INCREASE,
353 X_OPEN_PERCENT_INCREASE => p_loan_term_rec.OPEN_PERCENT_INCREASE,
354 X_OPEN_PERCENT_INCREASE_LIFE => p_loan_term_rec.OPEN_PERCENT_INCREASE_LIFE,
355 X_OPEN_FIRST_PERCENT_INCREASE => p_loan_term_rec.OPEN_FIRST_PERCENT_INCREASE,
356 X_PMT_APPL_ORDER_SCOPE => p_loan_term_rec.PMT_APPL_ORDER_SCOPE,
357 X_OPEN_CEILING_RATE => p_loan_term_rec.OPEN_CEILING_RATE,
358 X_OPEN_FLOOR_RATE => p_loan_term_rec.OPEN_FLOOR_RATE,
359 X_OPEN_INDEX_DATE => p_loan_term_rec.OPEN_INDEX_DATE,
360 X_TERM_INDEX_DATE => p_loan_term_rec.TERM_INDEX_DATE,
361 X_OPEN_PROJECTED_RATE => p_loan_term_rec.OPEN_PROJECTED_RATE,
362 X_TERM_PROJECTED_RATE => p_loan_term_rec.TERM_PROJECTED_RATE,
363 X_PAYMENT_CALC_METHOD => p_loan_term_rec.PAYMENT_CALC_METHOD,
364 X_CUSTOM_CALC_METHOD => p_loan_term_rec.CUSTOM_CALC_METHOD,
365 X_ORIG_PAY_CALC_METHOD => p_loan_term_rec.ORIG_PAY_CALC_METHOD,
366 X_PRIN_FIRST_PAY_DATE => p_loan_term_rec.PRIN_FIRST_PAY_DATE,
367 X_PRIN_PAYMENT_FREQUENCY => p_loan_term_rec.PRIN_PAYMENT_FREQUENCY,
368 X_PENAL_INT_RATE => p_loan_term_rec.PENAL_INT_RATE,
369 X_PENAL_INT_GRACE_DAYS => p_loan_term_rec.PENAL_INT_GRACE_DAYS
370 );
371
372 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, ' - In do_update_term procedure: After call to LNS_TERMS_PKG.Update_Row');
373
374 END do_update_term;
375
376 PROCEDURE default_delinquency_amount(
377 p_term_id IN NUMBER,
378 p_loan_id IN NUMBER,
379 p_object_version_number IN OUT NOCOPY NUMBER,
380 x_return_status IN OUT NOCOPY VARCHAR2,
381 x_msg_count OUT NOCOPY NUMBER,
382 x_msg_data OUT NOCOPY VARCHAR2
383 ) IS
384
385 l_amortization_rec LNS_FINANCIALS.AMORTIZATION_REC;
386 l_delinq_amt NUMBER := NULL;
387 l_loan_term_rec LOAN_TERM_REC_TYPE;
388 l_msg_count NUMBER;
389 l_msg_data VARCHAR2(2000);
390 l_fees_tbl LNS_FINANCIALS.FEES_TBL;
391
392 BEGIN
393
394 -- need to default delinquency threshold amount Bug # 3795150
395
396 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, ' - In default_delinquency_amount procedure: Before call to default_delinquency_amount');
397
398
399 -- raverma (added fees table call)
400 lns_financials.getInstallment(
401 p_api_version => 1.0,
402 p_init_msg_list => FND_API.G_FALSE,
403 p_commit => FND_API.G_FALSE,
404 p_loan_Id => p_loan_id,
405 p_installment_number => 1,
406 x_amortization_rec => l_amortization_rec,
407 x_fees_tbl => l_fees_tbl,
408 X_RETURN_STATUS => x_return_status,
409 X_MSG_COUNT => l_msg_count,
410 X_MSG_DATA => l_msg_data);
411
412 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
413 l_delinq_amt := nvl(l_amortization_rec.principal_amount, 0) + nvl(l_amortization_rec.interest_amount, 0) + nvl(l_amortization_rec.fee_amount, 0);
414
415 l_loan_term_rec.delinquency_threshold_amount := l_delinq_amt;
416 l_loan_term_rec.loan_id := p_loan_id;
417 l_loan_term_rec.term_id := p_term_id;
418
419 LNS_TERMS_PUB.update_term(
420 p_init_msg_list => FND_API.G_FALSE,
421 p_loan_term_rec => l_loan_term_rec,
422 p_object_version_number => p_object_version_number,
423 X_RETURN_STATUS => x_return_status,
424 X_MSG_COUNT => l_msg_count,
425 X_MSG_DATA => l_msg_data);
426
427 END IF;
428
429 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, ' - In default_delinquency_amount procedure: After call to default_delinquency_amount');
430
431 END default_delinquency_amount;
432
433
434 ----------------------------
435 -- body of public procedures
436 ----------------------------
437
438 /*===========================================================================+
439 | PROCEDURE
440 | create_term
441 |
442 | DESCRIPTION
443 | Creates term.
444 |
445 | SCOPE - PUBLIC
446 |
447 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
448 |
449 | ARGUMENTS : IN:
450 | p_init_msg_list
451 | p_loan_term_rec
452 | OUT:
453 | x_return_status
454 | x_msg_count
455 | x_msg_data
456 | x_term_id
457 | IN/OUT:
458 |
459 | RETURNS : NONE
460 |
461 | NOTES
462 |
463 | MODIFICATION HISTORY
464 | 21-Dec-2003 Karthik Ramachandran Created.
465 +===========================================================================*/
466
467 PROCEDURE create_term (
468 p_init_msg_list IN VARCHAR2,
469 p_loan_term_rec IN LOAN_TERM_REC_TYPE,
470 x_term_id OUT NOCOPY NUMBER,
471 x_return_status OUT NOCOPY VARCHAR2,
472 x_msg_count OUT NOCOPY NUMBER,
473 x_msg_data OUT NOCOPY VARCHAR2
474 ) IS
475
476 l_api_name CONSTANT VARCHAR2(30) := 'create_term';
477 l_loan_term_rec LOAN_TERM_REC_TYPE;
478
479 BEGIN
480 l_loan_term_rec := p_loan_term_rec;
481
482 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, ' - Begin Create_Term procedure');
483
484 -- standard start of API savepoint
485 SAVEPOINT create_term;
486
487 -- initialize message list if p_init_msg_list is set to TRUE.
488 IF (p_init_msg_list is not null and FND_API.to_Boolean(p_init_msg_list)) THEN
489 FND_MSG_PUB.initialize;
490 END IF;
491
492 -- initialize API return status to success.
493 x_return_status := FND_API.G_RET_STS_SUCCESS;
494
495 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, ' - In Create_Term procedure: Before call to do_create_term proc');
496
497 -- call to business logic.
498 do_create_term(
499 l_loan_term_rec,
500 x_term_id,
501 x_return_status
502 );
503
504 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, ' - In Create_Term procedure: After call to do_create_term proc');
505
506 EXCEPTION
507 WHEN FND_API.G_EXC_ERROR THEN
508 ROLLBACK TO create_term;
509 x_return_status := FND_API.G_RET_STS_ERROR;
510 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
511 p_count => x_msg_count,
512 p_data => x_msg_data);
513
514 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
515 ROLLBACK TO create_term;
516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
517 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
518 p_count => x_msg_count,
519 p_data => x_msg_data);
520
521 WHEN OTHERS THEN
522 ROLLBACK TO create_term;
523 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
524 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
525 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
526 FND_MSG_PUB.ADD;
527 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
528 p_count => x_msg_count,
529 p_data => x_msg_data);
530
531 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
532 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'End Create_Term procedure');
533 END IF;
534
535 END create_term;
536
537 /*===========================================================================+
538 | PROCEDURE
539 | update_term
540 |
541 | DESCRIPTION
542 | Updates term.
543 |
544 | SCOPE - PUBLIC
545 |
546 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
547 |
548 | ARGUMENTS : IN:
549 | p_init_msg_list
550 | p_loan_term_rec
551 | OUT:
552 | x_return_status
553 | x_msg_count
554 | x_msg_data
555 | IN/OUT:
556 | p_object_version_number
557 |
558 | RETURNS : NONE
559 |
560 | NOTES
561 |
562 | MODIFICATION HISTORY
563 | 21-Dec-2003 Karthik Ramachandran Created.
564 +===========================================================================*/
565
566 PROCEDURE update_term (
567 p_init_msg_list IN VARCHAR2,
568 p_loan_term_rec IN LOAN_TERM_REC_TYPE,
569 p_object_version_number IN OUT NOCOPY NUMBER,
570 x_return_status OUT NOCOPY VARCHAR2,
571 x_msg_count OUT NOCOPY NUMBER,
572 x_msg_data OUT NOCOPY VARCHAR2
573 ) IS
574
575 l_api_name CONSTANT VARCHAR2(30) := 'update_term';
576 l_loan_term_rec LOAN_TERM_REC_TYPE;
577 l_old_loan_term_rec LOAN_TERM_REC_TYPE;
578
579 BEGIN
580
581 l_loan_term_rec := p_loan_term_rec;
582
583 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, ' - Begin Update_Term procedure');
584
585 -- standard start of API savepoint
586 SAVEPOINT update_term;
587
588 -- initialize message list if p_init_msg_list is set to TRUE.
589 IF (p_init_msg_list is not null and FND_API.to_Boolean(p_init_msg_list)) THEN
590 FND_MSG_PUB.initialize;
591 END IF;
592
593 -- initialize API return status to success.
594 x_return_status := FND_API.G_RET_STS_SUCCESS;
595
596 -- Get old record. Will be used by history package.
597 get_loan_term_rec (
598 p_init_msg_list => FND_API.G_FALSE,
599 p_term_id => l_loan_term_rec.term_id,
600 x_loan_term_rec => l_old_loan_term_rec,
601 x_return_status => x_return_status,
602 x_msg_count => x_msg_count,
603 x_msg_data => x_msg_data );
604
605 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
606 RAISE FND_API.G_EXC_ERROR;
607 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
608 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
609 END IF;
610
611 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, ' - In Update_Term procedure: Before call to do_update_term proc');
612
613 -- call to business logic.
614 do_update_term(
615 l_loan_term_rec,
616 p_object_version_number,
617 x_return_status
618 );
619
620 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, ' - In Update_Term procedure: After call to do_update_term proc');
621
622 EXCEPTION
623 WHEN FND_API.G_EXC_ERROR THEN
624 ROLLBACK TO update_term;
625 x_return_status := FND_API.G_RET_STS_ERROR;
626 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
627 p_count => x_msg_count,
628 p_data => x_msg_data);
629
630 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
631 ROLLBACK TO update_term;
632 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
633 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
634 p_count => x_msg_count,
635 p_data => x_msg_data);
636
637 WHEN OTHERS THEN
638 ROLLBACK TO update_term;
639 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
640 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
641 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
642 FND_MSG_PUB.ADD;
643 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
644 p_count => x_msg_count,
645 p_data => x_msg_data);
646
647 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
648 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'End Update_Term procedure');
649 END IF;
650
651 END update_term;
652
653 /*===========================================================================+
654 | PROCEDURE
655 | validate_term
656 |
657 | DESCRIPTION
658 | Validates term.
659 |
660 | SCOPE - PUBLIC
661 |
662 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
663 |
664 | ARGUMENTS : IN:
665 | p_init_msg_list
666 | p_loan_term_rec
667 | OUT:
668 | x_return_status
669 | x_msg_count
670 | x_msg_data
671 | IN/OUT:
672 |
673 | RETURNS : NONE
674 |
675 | NOTES
676 |
677 | MODIFICATION HISTORY
678 | 18-Jan-2004 Karthik Ramachandran Created.
679 || 22-Jan-2006 raverma implement day count validation
680 +===========================================================================*/
681
682 PROCEDURE validate_term (
683 p_init_msg_list IN VARCHAR2,
684 p_loan_term_rec IN LOAN_TERM_REC_TYPE,
685 x_return_status OUT NOCOPY VARCHAR2,
686 x_msg_count OUT NOCOPY NUMBER,
687 x_msg_data OUT NOCOPY VARCHAR2
688 ) IS
689
690 l_api_name CONSTANT VARCHAR2(30) := 'validate_term';
691 l_loan_term_rec LOAN_TERM_REC_TYPE;
692
693 /*
694 cursor c_validate_day_count(p_loan_id in number) is
695 select day_count_method
696 ,amortization_frequency
697 from lns_terms
698 where loan_id = p_loan_id;
699 */
700 BEGIN
701 l_loan_term_rec := p_loan_term_rec;
702
703 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, ' - Begin Validate_Term procedure');
704
705 -- initialize API return status to success.
706 x_return_status := FND_API.G_RET_STS_SUCCESS;
707
708 -- day count method must be ACTUAL/ACTUAL if less than MONTHLY AMORTIZATION
709 --open c_validate_day_count(l_loan_term_rec.loan_id);
710 --fetch c_validate_day_count into l_loan_term_rec.DAY_COUNT_METHOD, l_loan_term_rec.AMORTIZATION_FREQUENCY;
711 --close c_validate_day_count;
712
713 if ((p_loan_term_rec.AMORTIZATION_FREQUENCY = 'BIWEEKLY' or
714 p_loan_term_rec.AMORTIZATION_FREQUENCY = 'SEMI-MONTHLY' or
715 p_loan_term_rec.AMORTIZATION_FREQUENCY = 'WEEKLY') AND
716 (p_loan_term_rec.DAY_COUNT_METHOD <> 'ACTUAL_ACTUAL' AND
717 p_loan_term_rec.DAY_COUNT_METHOD <> 'ACTUAL_360' AND
718 p_loan_term_rec.DAY_COUNT_METHOD <> 'ACTUAL_365' AND
719 p_loan_term_rec.DAY_COUNT_METHOD <> 'ACTUAL_365L'))
720 then
721 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_DAY_COUNT');
722 FND_MSG_PUB.ADD;
723 RAISE FND_API.G_EXC_ERROR;
724 end if;
725
726 --Standard call to get message count and if count is 1, get message info.
727 FND_MSG_PUB.Count_And_Get(
728 p_encoded => FND_API.G_FALSE,
729 p_count => x_msg_count,
730 p_data => x_msg_data );
731
732 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, ' - End Validate_Term procedure');
733
734
735 EXCEPTION
736 WHEN FND_API.G_EXC_ERROR THEN
737 x_return_status := FND_API.G_RET_STS_ERROR;
738
739 FND_MSG_PUB.Count_And_Get(
740 p_encoded => FND_API.G_FALSE,
741 p_count => x_msg_count,
742 p_data => x_msg_data );
743
744 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
745 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
746
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 WHEN OTHERS THEN
753 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
754 FND_MESSAGE.SET_NAME( 'LNS', 'LNS_API_OTHERS_EXCEP' );
755 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
756 FND_MSG_PUB.ADD;
757 FND_MSG_PUB.Count_And_Get(
758 p_encoded => FND_API.G_FALSE,
759 p_count => x_msg_count,
760 p_data => x_msg_data );
761
762 END validate_term;
763
764 /*===========================================================================+
765 | PROCEDURE
766 | get_loan_term_rec
767 |
768 | DESCRIPTION
769 | Gets current record.
770 |
771 | SCOPE - PUBLIC
772 |
773 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
774 |
775 | ARGUMENTS : IN:
776 | p_init_msg_list
777 | p_term_id
778 | OUT:
779 | x_loan_term_rec
780 | x_return_status
781 | x_msg_count
782 | x_msg_data
783 | IN/OUT:
784 |
785 | RETURNS : NONE
786 |
787 | NOTES
788 |
789 | MODIFICATION HISTORY
790 | 21-Dec-2003 Karthik Ramachandran Created.
791 +===========================================================================*/
792
793 PROCEDURE get_loan_term_rec (
794 p_init_msg_list IN VARCHAR2,
795 p_term_id IN NUMBER,
796 x_loan_term_rec OUT NOCOPY LOAN_TERM_REC_TYPE,
797 x_return_status OUT NOCOPY VARCHAR2,
798 x_msg_count OUT NOCOPY NUMBER,
799 x_msg_data OUT NOCOPY VARCHAR2
800 ) IS
801
802 l_api_name CONSTANT VARCHAR2(30) := 'get_loan_term_rec';
803
804 BEGIN
805
806 --Initialize message list if p_init_msg_list is set to TRUE.
807 IF (p_init_msg_list is not null AND FND_API.to_Boolean(p_init_msg_list)) THEN
808 FND_MSG_PUB.initialize;
809 END IF;
810
811 --Initialize API return status to success.
812 x_return_status := FND_API.G_RET_STS_SUCCESS;
813
814 --Check whether primary key has been passed in.
815 IF p_term_id IS NULL OR p_term_id = FND_API.G_MISS_NUM THEN
816 FND_MESSAGE.SET_NAME( 'LNS', 'LNS_API_MISSING_COLUMN' );
817 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'term_id' );
818 FND_MSG_PUB.ADD;
819 RAISE FND_API.G_EXC_ERROR;
820 END IF;
821
822 x_loan_term_rec.term_id := p_term_id;
823
824 --Call to table-handler
825 LNS_TERMS_PKG.Select_Row (
826 X_TERM_ID => x_loan_term_rec.term_id,
827 X_LOAN_ID => x_loan_term_rec.loan_id,
828 X_DAY_COUNT_METHOD => x_loan_term_rec.day_count_method,
829 X_BASED_ON_BALANCE => x_loan_term_rec.based_on_balance,
830 X_FIRST_RATE_CHANGE_DATE => x_loan_term_rec.first_rate_change_date,
831 X_NEXT_RATE_CHANGE_DATE => x_loan_term_rec.next_rate_change_date,
832 X_PERCENT_INCREASE => x_loan_term_rec.percent_increase,
833 X_PERCENT_INCREASE_TERM => x_loan_term_rec.percent_increase_term,
834 X_PAYMENT_APPLICATION_ORDER => x_loan_term_rec.payment_application_order,
835 X_PREPAY_PENALTY_FLAG => x_loan_term_rec.prepay_penalty_flag,
836 X_PREPAY_PENALTY_DATE => x_loan_term_rec.prepay_penalty_date,
837 X_CEILING_RATE => x_loan_term_rec.ceiling_rate,
838 X_FLOOR_RATE => x_loan_term_rec.floor_rate,
839 X_DELINQUENCY_THRESHOLD_NUMBER => x_loan_term_rec.delinquency_threshold_number,
840 X_DELINQUENCY_THRESHOLD_AMOUNT => x_loan_term_rec.delinquency_threshold_amount,
841 X_CALCULATION_METHOD => x_loan_term_rec.calculation_method,
842 X_REAMORTIZE_UNDER_PAYMENT => x_loan_term_rec.reamortize_under_payment,
843 X_REAMORTIZE_OVER_PAYMENT => x_loan_term_rec.reamortize_over_payment,
844 X_REAMORTIZE_WITH_INTEREST => x_loan_term_rec.reamortize_with_interest,
845 X_LOAN_PAYMENT_FREQUENCY => x_loan_term_rec.loan_payment_frequency,
846 X_INTEREST_COMPOUNDING_FREQ => x_loan_term_rec.interest_compounding_freq,
847 X_AMORTIZATION_FREQUENCY => x_loan_term_rec.amortization_frequency,
848 X_NUMBER_GRACE_DAYS => x_loan_term_rec.number_grace_days,
849 X_RATE_TYPE => x_loan_term_rec.rate_type,
850 X_INDEX_NAME => x_loan_term_rec.index_name,
851 X_ADJUSTMENT_FREQUENCY => x_loan_term_rec.adjustment_frequency,
852 X_ADJUSTMENT_FREQUENCY_TYPE => x_loan_term_rec.adjustment_frequency_type,
853 X_FIXED_RATE_PERIOD => x_loan_term_rec.fixed_rate_period,
854 X_FIXED_RATE_PERIOD_TYPE => x_loan_term_rec.fixed_rate_period_type,
855 X_FIRST_PAYMENT_DATE => x_loan_term_rec.first_payment_date,
856 X_NEXT_PAYMENT_DUE_DATE => x_loan_term_rec.next_payment_due_date,
857 X_OPEN_FIRST_PAYMENT_DATE => x_loan_term_rec.open_first_payment_date,
858 X_OPEN_PAYMENT_FREQUENCY => x_loan_term_rec.open_payment_frequency,
859 X_OPEN_NEXT_PAYMENT_DATE => x_loan_term_rec.open_next_payment_date,
860 X_LOCK_IN_DATE => x_loan_term_rec.lock_in_date,
861 X_LOCK_TO_DATE => x_loan_term_rec.lock_to_date,
862 X_RATE_CHANGE_FREQUENCY => x_loan_term_rec.rate_change_frequency,
863 X_INDEX_RATE_ID => x_loan_term_rec.index_rate_id,
864 X_PERCENT_INCREASE_LIFE => x_loan_term_rec.PERCENT_INCREASE_LIFE,
865 X_FIRST_PERCENT_INCREASE => x_loan_term_rec.FIRST_PERCENT_INCREASE,
866 X_OPEN_PERCENT_INCREASE => x_loan_term_rec.OPEN_PERCENT_INCREASE,
867 X_OPEN_PERCENT_INCREASE_LIFE => x_loan_term_rec.OPEN_PERCENT_INCREASE_LIFE,
868 X_OPEN_FIRST_PERCENT_INCREASE => x_loan_term_rec.OPEN_FIRST_PERCENT_INCREASE,
869 X_PMT_APPL_ORDER_SCOPE => x_loan_term_rec.PMT_APPL_ORDER_SCOPE,
870 X_OPEN_CEILING_RATE => x_loan_term_rec.OPEN_CEILING_RATE,
871 X_OPEN_FLOOR_RATE => x_loan_term_rec.OPEN_FLOOR_RATE,
872 X_OPEN_INDEX_DATE => x_loan_term_rec.OPEN_INDEX_DATE,
873 X_TERM_INDEX_DATE => x_loan_term_rec.TERM_INDEX_DATE,
874 X_OPEN_PROJECTED_RATE => x_loan_term_rec.OPEN_PROJECTED_RATE,
875 X_TERM_PROJECTED_RATE => x_loan_term_rec.TERM_PROJECTED_RATE,
876 X_PAYMENT_CALC_METHOD => x_loan_term_rec.PAYMENT_CALC_METHOD,
877 X_CUSTOM_CALC_METHOD => x_loan_term_rec.CUSTOM_CALC_METHOD,
878 X_ORIG_PAY_CALC_METHOD => x_loan_term_rec.ORIG_PAY_CALC_METHOD,
879 X_PRIN_FIRST_PAY_DATE => x_loan_term_rec.prin_first_pay_date,
880 X_PRIN_PAYMENT_FREQUENCY => x_loan_term_rec.prin_payment_frequency,
881 X_PENAL_INT_RATE => x_loan_term_rec.PENAL_INT_RATE,
882 X_PENAL_INT_GRACE_DAYS => x_loan_term_rec.PENAL_INT_GRACE_DAYS
883 );
884
885 --Standard call to get message count and if count is 1, get message info.
886 FND_MSG_PUB.Count_And_Get(
887 p_encoded => FND_API.G_FALSE,
888 p_count => x_msg_count,
889 p_data => x_msg_data );
890
891 EXCEPTION
892 WHEN FND_API.G_EXC_ERROR THEN
893 x_return_status := FND_API.G_RET_STS_ERROR;
894
895 FND_MSG_PUB.Count_And_Get(
896 p_encoded => FND_API.G_FALSE,
897 p_count => x_msg_count,
898 p_data => x_msg_data );
899
900 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
901 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
902
903 FND_MSG_PUB.Count_And_Get(
904 p_encoded => FND_API.G_FALSE,
905 p_count => x_msg_count,
906 p_data => x_msg_data );
907
908 WHEN OTHERS THEN
909 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
910 FND_MESSAGE.SET_NAME( 'LNS', 'LNS_API_OTHERS_EXCEP' );
911 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
912 FND_MSG_PUB.ADD;
913 FND_MSG_PUB.Count_And_Get(
914 p_encoded => FND_API.G_FALSE,
915 p_count => x_msg_count,
916 p_data => x_msg_data );
917
918 END get_loan_term_rec;
919
920
921 /*========================================================================+
922 | PROCEDURE
923 | validate_rate_schedule
924 |
925 | DESCRIPTION
926 | validates rate schedule and terms
927 |
928 | SCOPE - PUBLIC
929 |
930 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
931 |
932 | ARGUMENTS : IN:
933 | p_loan_id
934 |
935 | OUT:
936 | x_return_status
937 | x_msg_count
938 | x_msg_data
939 |
940 | RETURNS : NONE
941 |
942 | NOTES
943 | #1 we will only allow the interest only period(s) to be on the beginning of
944 | the loan. once a row is found in the rate schedule(s) that is not interest
945 | only, then subsequent rows MAY NOT be interest only
946 |
947 | #2 first payment date MUST be within the interest only period. We will not
948 | allow for negative amortization at this time
949 |
950 | #3 loans that are interest only shall not re-amortize in the case of
951 | overpayments and vice-versa loans that re-amortize may not contain an
952 | interest only period on the rate schedule
953 |
954 | MODIFICATION HISTORY
955 | 3-Nov-2004 raverma Created. BUSH2004
956 +=======================================================================*/
957 PROCEDURE validate_rate_schedule(p_loan_id IN NUMBER
958 ,x_return_status IN OUT NOCOPY VARCHAR2
959 ,x_msg_count OUT NOCOPY NUMBER
960 ,x_msg_data OUT NOCOPY VARCHAR2)
961 is
962 l_rate_schedule LNS_FINANCIALS.RATE_SCHEDULE_TBL;
963 l_api_name varchar2(25);
964 l_interest_only varchar2(1);
965 l_disallow_interest_only boolean;
966 l_interest_only_rows number;
967 l_last_interest_inst number;
968 l_reamortize_overpay varchar2(1);
969 l_first_payment_date date;
970
971 cursor c_interest_only_exists (p_loan_id number) is
972 select count(1)
973 from lns_rate_schedules rs,
974 lns_terms term
975 where term.loan_id = p_loan_id
976 and term.term_id = rs.term_id
977 and rs.interest_only_flag = 'Y'
978 and rs.phase <> 'OPEN';
979
980 cursor c_max_int_installment(p_loan_id number) is
981 select max(end_installment_number)
982 from lns_rate_schedules rs,
983 lns_terms term
984 where term.loan_id = p_loan_id
985 and term.term_id = rs.term_id
986 and rs.interest_only_flag = 'Y'
987 and rs.phase <> 'OPEN';
988
989 cursor c_reamortization(p_loan_id number) is
990 select nvl(reamortize_over_payment, 'N')
991 from lns_terms
992 where loan_id = p_loan_id;
993
994 cursor c_first_payment_date(p_loan_id number) is
995 select first_payment_date
996 from lns_terms
997 where loan_id = p_loan_id;
998
999 begin
1000 l_api_name := 'validate_rate_schedule';
1001
1002 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1003
1004 -- Initialize API return status to SUCCESS
1005 x_return_status := FND_API.G_RET_STS_SUCCESS;
1006
1007
1008 open c_interest_only_exists(p_loan_id);
1009 fetch c_interest_only_exists into l_interest_only_rows;
1010 close c_interest_only_exists;
1011
1012 --dbms_output.put_line('interest only # ' || l_interest_only_rows);
1013 if l_interest_only_rows > 0 then
1014
1015 -- check to see if there is a reamortization = 'Y'
1016 open c_reamortization(p_loan_id);
1017 fetch c_reamortization into l_reamortize_overpay;
1018 close c_reamortization;
1019
1020 --dbms_output.put_line('reamortize ' || l_reamortize_overpay);
1021 if l_reamortize_overpay = 'Y' then
1022 FND_MESSAGE.SET_NAME('LNS', 'LNS_NO_REAMORTIZE');
1023 FND_MSG_PUB.Add;
1024 RAISE FND_API.G_EXC_ERROR;
1025 end if;
1026
1027 /* removing this validation as per conversation with ravi on 11-3-2004
1028 || he wants users to be able to enter in funky interest only combinations
1029 */
1030 /*
1031 if l_interest_only_rows > 1 then
1032
1033 l_rate_schedule := lns_financials.getRateSchedule(p_loan_id);
1034 l_interest_only := 'Y';
1035
1036 -- this will ensure that interest only periods are on the begining
1037 -- of the loan and are contiguous
1038 for k in 1..l_rate_schedule.count
1039 loop
1040 if l_rate_schedule(k).interest_only_flag = 'Y' and l_interest_only = 'N' then
1041 dbms_output.put_line('output non continuous');
1042 FND_MESSAGE.SET_NAME('LNS', 'LNS_INTEREST_ONLY_BREAK');
1043 FND_MSG_PUB.Add;
1044 RAISE FND_API.G_EXC_ERROR;
1045 end if;
1046 l_interest_only := l_rate_schedule(k).interest_only_flag;
1047
1048 end loop;
1049 end if;
1050 */
1051 end if;
1052
1053 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1054
1055 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - End');
1056
1057
1058 EXCEPTION
1059
1060 WHEN FND_API.G_EXC_ERROR THEN
1061 x_return_status := FND_API.G_RET_STS_ERROR;
1062 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1063 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1064 END IF;
1065
1066 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1067 x_return_status := FND_API.G_RET_STS_ERROR;
1068 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1069 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1070 END IF;
1071
1072 WHEN OTHERS THEN
1073 x_return_status := FND_API.G_RET_STS_ERROR;
1074 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1075 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1076 END IF;
1077
1078 end validate_rate_schedule;
1079
1080
1081 END LNS_TERMS_PUB;