[Home] [Help]
PACKAGE BODY: APPS.LNS_CUSTOM_PUB
Source
1 PACKAGE BODY LNS_CUSTOM_PUB AS
2 /* $Header: LNS_CUST_PUBP_B.pls 120.2.12010000.4 2009/01/21 20:22:42 scherkas ship $ */
3 G_DEBUG_COUNT CONSTANT NUMBER := 0;
4 G_DEBUG CONSTANT BOOLEAN := FALSE;
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'LNS_CUSTOM_PUB';
7
8
9 ---------------------------------------------------------------------------
10 -- internal package routines
11 ---------------------------------------------------------------------------
12
13 procedure logMessage(log_level in number
14 ,module in varchar2
15 ,message in varchar2)
16 is
17
18 begin
19
20 IF log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
21 FND_LOG.STRING(log_level, module, message);
22 END IF;
23
24 if FND_GLOBAL.Conc_Request_Id is not null then
25 fnd_file.put_line(FND_FILE.LOG, message);
26 end if;
27
28 end;
29
30 /* this funciton will ensure the rows in the custom tbl are ordered by payment number
31 || will NOT validate that payment numbers are unique. this should be done prior to sorting
32 */
33 procedure sortRows(p_custom_tbl in out nocopy LNS_CUSTOM_PUB.custom_tbl)
34
35 is
36 l_return_tbl LNS_CUSTOM_PUB.custom_tbl;
37 j number;
38 l_tmp_row lns_custom_pub.custom_sched_type;
39 l_number number;
40 l_min number;
41 l_tmp number;
42
43 begin
44 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, ' - sorting the rows');
45 for i in 1..p_custom_tbl.count loop
46 l_min := p_custom_tbl(i).payment_number;
47
48 for j in i + 1..p_custom_tbl.count loop
49
50 if p_custom_tbl(j).payment_number < l_min then
51 l_min := p_custom_tbl(j).payment_number;
52 l_tmp_row := p_custom_tbl(i);
53 p_custom_tbl(i) := p_custom_tbl(j);
54 p_custom_tbl(j) := l_tmp_row;
55 end if;
56 end loop;
57 end loop;
58 end ;
59
60 /*=========================================================================
61 || PUBLIC PROCEDURE resetCustomSchedule
62 ||
63 || DESCRIPTION
64 ||
65 || Overview: resets a customized payment schedule for a loan
66 ||
67 || Parameter: loan_id => loan id to reset
68 ||
69 || Return value: standard API outputs
70 ||
71 || Source Tables: NA
72 ||
73 || Target Tables: LNS_CUSTOM_PAYMENT_SCHEDULE, LNS_LOAN_HEADER
74 ||
75 || Return value:
76 ||
77 || KNOWN ISSUES
78 || you cannot reset a customized loan once billing begins
79 || NOTES
80 ||
81 || MODIFICATION HISTORY
82 || Date Author Description of Changes
83 || 12/08/2003 11:35AM raverma Created
84 ||
85 *=======================================================================*/
86 procedure resetCustomSchedule(p_loan_id IN number
87 ,p_init_msg_list IN VARCHAR2
88 ,p_commit IN VARCHAR2
89 ,p_update_header IN boolean
90 ,x_return_status OUT NOCOPY VARCHAR2
91 ,x_msg_count OUT NOCOPY NUMBER
92 ,x_msg_data OUT NOCOPY VARCHAR2)
93
94 is
95 l_api_name varchar2(25);
96 l_msg_count NUMBER;
97 l_msg_data VARCHAR2(2000);
98 l_return_Status VARCHAR2(1);
99 l_last_installment_billed NUMBER;
100 l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
101 l_customized varchar2(1);
102 l_loan_header_rec LNS_LOAN_HEADER_PUB.LOAN_HEADER_REC_TYPE;
103 l_object_version number;
104 l_skip_update boolean;
105
106 cursor c_customized (p_loan_id number) is
107 SELECT nvl(h.custom_payments_flag, 'N')
108 FROM lns_loan_headers_all h
109 WHERE loan_id = p_loan_id;
110
111 begin
112
113 -- Standard Start of API savepoint
114 SAVEPOINT resetCustomSchedule;
115 l_api_name := 'resetCustomSchedule';
116
117 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
118
119 -- Initialize message list IF p_init_msg_list is set to TRUE.
120 IF FND_API.to_Boolean( p_init_msg_list ) THEN
121 FND_MSG_PUB.initialize;
122 END IF;
123
124 -- Initialize API return status to SUCCESS
125 x_return_status := FND_API.G_RET_STS_SUCCESS;
126
127 -- --------------------------------------------------------------------
128 -- Api body
129 -- --------------------------------------------------------------------
130 -- validate loan_id
131 lns_utility_pub.validate_any_id(p_api_version => 1.0
132 ,p_init_msg_list => 'T'
133 ,x_msg_count => l_msg_count
134 ,x_msg_data => l_msg_data
135 ,x_return_status => l_return_status
136 ,p_col_id => p_loan_id
137 ,p_col_name => 'LOAN_ID'
138 ,p_table_name => 'LNS_LOAN_HEADERS_ALL');
139
140 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
141 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
142 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
143 FND_MESSAGE.SET_TOKEN('VALUE', p_loan_ID);
144 FND_MSG_PUB.ADD;
145 RAISE FND_API.G_EXC_ERROR;
146 end if;
147
148 -- check to see if the loan is customized
149 open c_customized(p_loan_id);
150 fetch c_customized into l_customized;
151 close c_customized;
152 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - customized = ' || l_customized);
153
154 if l_customized = 'N' then
155 l_skip_update := true;
156 /* dont raise this error as per karamach conversation 12-1-2004
157 FND_MESSAGE.Set_Name('LNS', 'LNS_NOT_CUSTOMIZED');
158 FND_MSG_PUB.Add;
159 RAISE FND_API.G_EXC_ERROR;
160 */
161 else
162 -- loan is customized
163 l_skip_update := false;
164
165 -- check to see if the loan has ever been billed
166 l_last_installment_billed := LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id);
167 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - last installment ' || l_last_installment_billed);
168
169 if l_last_installment_billed > 0 then
170 FND_MESSAGE.Set_Name('LNS', 'LNS_LOAN_ALREADY_BILLED');
171 FND_MSG_PUB.Add;
172 RAISE FND_API.G_EXC_ERROR;
173 end if;
174
175 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - deleting custom rows');
176 delete
177 from lns_custom_paymnt_scheds
178 where loan_id = p_loan_id;
179
180 end if;
181
182 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - resetting header');
183 if p_update_header and not l_skip_update then
184 select object_version_number into l_object_version
185 from lns_loan_headers_all
186 where loan_id = p_loan_id;
187 l_loan_header_rec.loan_id := p_loan_id;
188 l_loan_header_rec.custom_payments_flag := 'N';
189 lns_loan_header_pub.update_loan(p_init_msg_list => FND_API.G_FALSE
190 ,p_loan_header_rec => l_loan_header_rec
191 ,p_object_version_number => l_object_version
192 ,x_return_status => l_return_status
193 ,x_msg_count => l_msg_count
194 ,x_msg_data => l_msg_data);
195
196 else
197 null;
198 end if;
199 -- --------------------------------------------------------------------
200 -- End of API body
201 -- --------------------------------------------------------------------
202
203 -- Standard check for p_commit
204 IF FND_API.to_Boolean(p_commit) THEN
205 COMMIT WORK;
206 END IF;
207
208 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
209
210 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
211
212 EXCEPTION
213 WHEN FND_API.G_EXC_ERROR THEN
214 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
215 x_return_status := FND_API.G_RET_STS_ERROR;
216 x_msg_count := l_msg_count;
217 x_msg_data := l_msg_data;
218 ROLLBACK TO resetCustomSchedule;
219 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
220
221 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
222 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
223 x_return_status := FND_API.G_RET_STS_ERROR;
224 x_msg_count := l_msg_count;
225 x_msg_data := l_msg_data;
226 ROLLBACK TO resetCustomSchedule;
227 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
228
229 WHEN OTHERS THEN
230 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
231 x_return_status := FND_API.G_RET_STS_ERROR;
232 x_msg_count := l_msg_count;
233 x_msg_data := l_msg_data;
234 ROLLBACK TO resetCustomSchedule;
235 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
236
237 end resetCustomSchedule;
238
239 /*=========================================================================
240 || PUBLIC PROCEDURE createCustomSchedule
241 ||
242 || DESCRIPTION
243 ||
244 || Overview: creates a custom payment schedule for a loan
245 ||
246 || Parameter: loan_id => loan id to customize
247 || p_custom_tbl => table of records about custom schedule
248 ||
249 || Return value: standard API outputs
250 ||
251 || Source Tables: NA
252 ||
253 || Target Tables: LNS_CUSTOM_PAYMENT_SCHEDULE
254 ||
255 || Return value:
256 ||
257 || KNOWN ISSUES
258 ||
259 || NOTES
260 ||
261 || MODIFICATION HISTORY
262 || Date Author Description of Changes
263 || 12/08/2003 11:35AM raverma Created
264 ||
265 *=======================================================================*/
266 procedure createCustomSchedule(p_custom_tbl IN CUSTOM_TBL
267 ,p_loan_id IN number
268 ,p_init_msg_list IN VARCHAR2
269 ,p_commit IN VARCHAR2
270 ,x_return_status OUT NOCOPY VARCHAR2
271 ,x_msg_count OUT NOCOPY NUMBER
272 ,x_msg_data OUT NOCOPY VARCHAR2
273 ,X_INVALID_INSTALLMENT_NUM OUT NOCOPY NUMBER)
274
275 is
276 l_msg_count NUMBER;
277 l_msg_data VARCHAR2(2000);
278 l_return_Status VARCHAR2(1);
279 l_installment NUMBER;
280 l_custom_rec custom_sched_type;
281 l_custom_sched_id NUMBER;
282 m number;
283
284 l_loan_header_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type;
285 l_object_version number;
286 g_object_version number;
287 l_custom_tbl LNS_CUSTOM_PUB.CUSTOM_TBL;
288 l_api_name varchar2(25);
289 l_loan_start_date date;
290 l_original_loan_amount number;
291 l_fee_amount number;
292
293 -- for fees
294 l_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
295 l_orig_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
296 l_fees_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
297 l_orig_fees_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
298 l_fee_basis_tbl LNS_FEE_ENGINE.FEE_BASIS_TBL;
299
300 -- total fees on the schedule by installment
301 cursor c_fees(p_loan_id number, p_installment number) is
302 select nvl(sum(sched.fee_amount), 0)
303 from lns_fee_schedules sched
304 ,lns_fees struct
305 where sched.loan_id = p_loan_id
306 and sched.fee_id = struct.fee_id
307 and fee_installment = p_installment
308 and active_flag = 'Y';
309
310 cursor c_loan_details(p_loan_id number) is
311 select loan_start_date, funded_amount
312 from lns_loan_headers
313 where loan_id = p_loan_id;
314
315 begin
316 l_api_name := 'createCustomSchedule';
317 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
318 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - loan_id: ' || p_loan_id);
319 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - rows received: ' || p_custom_tbl.count);
320 SAVEPOINT createCustomSchedule;
321
322 -- Initialize message list IF p_init_msg_list is set to TRUE.
323 IF FND_API.to_Boolean( p_init_msg_list ) THEN
324 FND_MSG_PUB.initialize;
325 END IF;
326
327 -- Initialize API return status to SUCCESS
328 x_return_status := FND_API.G_RET_STS_SUCCESS;
329
330 --
331 -- Api body
332 --
333 --l_custom_tbl := p_custom_tbl;
334 m := 0;
335
336 for j in 1..p_custom_tbl.count loop
337 if p_custom_tbl(j).payment_number > 0 then
338 m := m + 1;
339 l_custom_tbl(m) := p_custom_tbl(j);
340 end if;
341 end loop;
342
343 lns_custom_pub.validateCustomTable(p_cust_tbl => l_custom_tbl
344 ,p_loan_id => p_loan_id
345 ,p_create_flag => true
346 ,x_installment => l_installment
347 ,x_return_status => l_return_status
348 ,x_msg_count => l_msg_count
349 ,x_msg_data => l_msg_data);
350 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - validateCustom ' || l_return_status);
351
352 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
353 if l_installment is not null then
354 X_INVALID_INSTALLMENT_NUM := l_installment;
355 FND_MESSAGE.Set_Name('LNS', 'LNS_INVALID_INSTALLMENT');
356 FND_MESSAGE.SET_TOKEN('PARAMETER', 'INSTALLMENT');
357 FND_MESSAGE.SET_TOKEN('VALUE', l_installment);
358 FND_MSG_PUB.Add;
359 RAISE FND_API.G_EXC_ERROR;
360 end if;
361 FND_MESSAGE.Set_Name('LNS', 'LNS_VALIDATE_CUSTOM_ERROR');
362 FND_MSG_PUB.Add;
363 RAISE FND_API.G_EXC_ERROR;
364 end if;
365
366 open c_loan_details(p_loan_id);
367 fetch c_loan_details into l_loan_start_date, l_original_loan_amount;
368 close c_loan_details;
369
370 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - getting fee structures');
371 -- now we've passed validation initialize loan_begin_balance to calculate balances
372 l_orig_fee_structures := lns_fee_engine.getFeeStructures(p_loan_id => p_loan_id
373 ,p_fee_category => 'EVENT'
374 ,p_fee_type => 'EVENT_ORIGINATION'
375 ,p_installment => null
376 ,p_fee_id => null);
377
378 l_fee_structures := lns_fee_engine.getFeeStructures(p_loan_id => p_loan_id
379 ,p_fee_category => 'RECUR'
380 ,p_fee_type => null
381 ,p_installment => null
382 ,p_fee_id => null);
383 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': fee structures count is ' || l_fee_structures.count);
384 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': orig structures count is ' || l_orig_fee_structures.count);
385
386 m := 0;
387 -- 2-24-2005 raverma add 0 installment to amortization schedule
388 if l_orig_fee_structures.count > 0 then
389
390 open c_fees(p_loan_id, 0);
391 fetch c_fees into l_fee_amount;
392 close c_fees;
393
394 if l_fee_amount > 0 then
395 m := l_custom_tbl.count + 1;
396 l_custom_rec.payment_number := 0;
397 l_custom_rec.due_date := l_loan_start_date;
398 l_custom_rec.principal_amount := 0;
399 l_custom_rec.interest_amount := 0;
400 l_custom_rec.fee_amount := l_fee_amount;
401 l_custom_rec.other_amount := 0;
402 l_custom_rec.installment_begin_balance := l_original_loan_amount;
403 l_custom_rec.installment_end_balance := l_original_loan_amount;
404 l_custom_rec.INTEREST_PAID_TODATE := 0;
405 l_custom_rec.PRINCIPAL_PAID_TODATE := 0;
406 --l_custom_rec.fees_cumulative := l_fee_amount;
407 --l_custom_rec.other_cumulative := 0;
408 -- add the record to the amortization table
409 l_custom_rec.CURRENT_TERM_PAYMENT := l_fee_amount;
410 --l_custom_tbl(m) := l_custom_rec;
411 end if;
412
413 --l_orig_fees_tbl.delete;
414 l_fee_amount := 0;
415
416 end if;
417 l_custom_tbl(1).installment_begin_balance := lns_financials.getRemainingBalance(p_loan_id);
418
419 for k in 1..l_custom_tbl.count
420 loop
421 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'custom_schedule_id: ' || l_custom_tbl(k).custom_schedule_id);
422 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'principal_amount : ' || l_custom_tbl(k).principal_amount);
423 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'interest_amount : ' || l_custom_tbl(k).interest_amount);
424 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'due_date : ' || l_custom_tbl(k).due_date);
425 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'object_version_number : ' || l_custom_tbl(k).object_version_number);
426 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'installment_begin_balance : ' || l_custom_tbl(k).installment_begin_balance);
427
428 l_fee_basis_tbl(1).fee_basis_name := 'TOTAL_BAL';
429 l_fee_basis_tbl(1).fee_basis_amount := l_custom_tbl(k).installment_begin_balance;
430 l_fee_basis_tbl(2).fee_basis_name := 'ORIG_LOAN';
431 l_fee_basis_tbl(2).fee_basis_amount := l_original_loan_amount;
432
433 if k = 1 then
434 if l_orig_fee_structures.count > 0 then
435 lns_fee_engine.calculateFees(p_loan_id => p_loan_id
436 ,p_installment => k
437 ,p_fee_basis_tbl => l_fee_basis_tbl
438 ,p_fee_structures => l_orig_fee_structures
439 ,x_fees_tbl => l_orig_fees_tbl
440 ,x_return_status => l_return_status
441 ,x_msg_count => l_msg_count
442 ,x_msg_data => l_msg_data);
443 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': calculated origination fees ' || l_orig_fee_structures.count);
444 end if;
445 end if;
446
447 if l_fee_structures.count > 0 then
448 lns_fee_engine.calculateFees(p_loan_id => p_loan_id
449 ,p_installment => k
450 ,p_fee_basis_tbl => l_fee_basis_tbl
451 ,p_fee_structures => l_fee_structures
452 ,x_fees_tbl => l_fees_tbl
453 ,x_return_status => l_return_status
454 ,x_msg_count => l_msg_count
455 ,x_msg_data => l_msg_data);
456 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': calculated fees ' || l_fees_tbl.count);
457
458 end if;
459
460 for i in 1..l_orig_fees_tbl.count loop
461 l_fee_amount := l_fee_amount + l_orig_fees_tbl(i).FEE_AMOUNT;
462 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': orig calculated fees ' || l_fee_amount);
463 end loop;
464
465 for j in 1..l_fees_tbl.count loop
466 l_fee_amount := l_fee_amount + l_fees_tbl(j).FEE_AMOUNT;
467 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': recurring calculated fees ' || l_fee_amount);
468 end loop;
469
470 l_custom_rec.LOAN_ID := p_loan_id;
471 l_custom_rec.PAYMENT_NUMBER := l_custom_tbl(k).payment_number;
472 l_custom_rec.PRINCIPAL_AMOUNT := l_custom_tbl(k).principal_amount;
473 l_custom_rec.INTEREST_AMOUNT := l_custom_tbl(k).interest_amount;
474 --l_custom_rec.FEE_AMOUNT := l_custom_tbl(k).fee_amount;
475 l_custom_rec.FEE_AMOUNT := l_fee_amount;
476 l_custom_rec.OTHER_AMOUNT := l_custom_tbl(k).other_amount;
477 l_custom_rec.DUE_DATE := l_custom_tbl(k).due_date;
478 l_custom_rec.current_term_payment := l_custom_rec.FEE_AMOUNT + l_custom_rec.INTEREST_AMOUNT + l_custom_rec.PRINCIPAL_AMOUNT;
479 --l_custom_rec.OBJECT_VERSION_NUMBER := p_custom_tbl(k).object_version_number;
480 l_custom_rec.installment_begin_balance := l_custom_tbl(k).installment_begin_balance;
481 l_custom_rec.installment_end_balance := l_custom_tbl(k).installment_begin_balance - l_custom_tbl(k).principal_amount;
482
483 -- now calculate the balances
484 if l_custom_rec.installment_end_balance > 0 and k <> l_custom_tbl.count then
485 l_custom_tbl(k + 1).installment_begin_balance := l_custom_rec.installment_end_balance;
486 end if;
487
488 -- call api to update rows one-by-one for compliance reasons
489 lns_custom_pub.createCustomSched(P_CUSTOM_REC => l_custom_rec
490 ,x_return_status => l_return_status
491 ,x_custom_sched_id => l_custom_sched_id
492 ,x_msg_count => l_msg_Count
493 ,x_msg_data => l_msg_Data);
494 -- dbms_output.put_line('after create API ' || l_return_status);
495
496 end loop;
497
498 -- if we get this far now we update the header table flag for custom payments
499 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - attempting to update header set custom = Y');
500 select object_version_number into l_object_version
501 from lns_loan_headers_all
502 where loan_id = p_loan_id;
503 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_object_version ' || l_object_version);
504
505 l_loan_header_rec.loan_id := p_loan_id;
506 l_loan_header_rec.CUSTOM_PAYMENTS_FLAG := 'Y';
507
508 lns_loan_header_pub.update_loan(p_init_msg_list => FND_API.G_FALSE
509 ,p_loan_header_rec => l_loan_header_rec
510 ,P_OBJECT_VERSION_NUMBER => l_object_version
511 ,X_RETURN_STATUS => l_return_status
512 ,X_MSG_COUNT => l_msg_count
513 ,X_MSG_DATA => l_msg_data);
514 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_msg_count ' || l_msg_count);
515 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_msg_data ' || l_msg_data);
516
517 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - header update set custom = Y');
518
519 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
520 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - header update ERROR');
521 FND_MESSAGE.Set_Name('LNS', 'LNS_HEADER_UPDATE_ERROR');
522 FND_MSG_PUB.Add;
523 RAISE FND_API.G_EXC_ERROR;
524 end if;
525
526 IF FND_API.to_Boolean(p_commit)
527 THEN
528 COMMIT WORK;
529 END IF;
530
531 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
532 p_data => x_msg_data);
533
534 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
535
536 EXCEPTION
537 WHEN FND_API.G_EXC_ERROR THEN
538 FND_MESSAGE.Set_Name('LNS', 'LNS_CREATE_CUSTOM_ERROR');
539 FND_MSG_PUB.Add;
540 x_return_status := FND_API.G_RET_STS_ERROR;
541 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
542 ROLLBACK TO createCustomSchedule;
543
544 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
545 FND_MESSAGE.Set_Name('LNS', 'LNS_CREATE_CUSTOM_ERROR');
546 FND_MSG_PUB.Add;
547 x_return_status := FND_API.G_RET_STS_ERROR;
548 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
549 ROLLBACK TO createCustomSchedule;
550
551 WHEN OTHERS THEN
552 FND_MESSAGE.Set_Name('LNS', 'LNS_CREATE_CUSTOM_ERROR');
553 FND_MSG_PUB.Add;
554 x_return_status := FND_API.G_RET_STS_ERROR;
555 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
556 ROLLBACK TO createCustomSchedule;
557
558 end;
559
560
561 /*=========================================================================
562 || PUBLIC PROCEDURE updateCustomSchedule
563 ||
564 || DESCRIPTION
565 ||
566 || Overview: updates a custom payment schedule for a loan
567 ||
568 || Parameter: loan_id => loan id to customize
569 || p_custom_tbl => table of records about custom schedule
570 ||
571 || Return value: standard API outputs
572 ||
573 || Source Tables: NA
574 ||
575 || Target Tables: LNS_CUSTOM_PAYMENT_SCHEDULE
576 ||
577 || Return value:
578 ||
579 || KNOWN ISSUES
580 ||
581 || NOTES
582 ||
583 || MODIFICATION HISTORY
584 || Date Author Description of Changes
585 || 12/08/2003 11:35AM raverma Created
586 ||
587 *=======================================================================*/
588 procedure updateCustomSchedule(p_custom_tbl IN CUSTOM_TBL
589 ,p_loan_id IN number
590 ,p_init_msg_list IN VARCHAR2
591 ,p_commit IN VARCHAR2
592 ,x_return_status OUT NOCOPY VARCHAR2
593 ,x_msg_count OUT NOCOPY NUMBER
594 ,x_msg_data OUT NOCOPY VARCHAR2
595 ,X_INVALID_INSTALLMENT_NUM OUT NOCOPY NUMBER)
596
597 is
598 l_msg_count NUMBER;
599 l_msg_data VARCHAR2(2000);
600 l_return_Status VARCHAR2(1);
601 l_installment NUMBER;
602 l_custom_rec custom_sched_type;
603 l_total_amount NUMBER;
604 l_custom_tbl LNS_CUSTOM_PUB.CUSTOM_TBL;
605 l_custom_tbl2 LNS_CUSTOM_PUB.CUSTOM_TBL;
606
607 l_api_name varchar2(25);
608
609
610 /* destroy records already billed
611 */
612 cursor c_records_to_destroy (p_loan_id number) is
613 select count(1)
614 from lns_amortization_scheds
615 where reamortization_amount is null
616 and reversed_flag <> 'Y'
617 and loan_id = p_loan_id
618 and payment_number > 0
619 and parent_amortization_id is null;
620
621 l_records_to_destroy number;
622 l_num_records number;
623 l_records_to_copy number;
624 l_count number;
625
626 -- we will need to get the PK since runAmortization API does not reutrn PKs
627 cursor c_cust_sched_id (p_loan_id number, p_payment_number number) is
628 select custom_schedule_id, object_version_number
629 from lns_custom_paymnt_scheds
630 where loan_id = p_loan_id
631 and payment_number = p_payment_number;
632
633 begin
634 l_api_name := 'updateCustomSchedule';
635 SAVEPOINT updateCustomSchedule;
636 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
637
638 -- Initialize message list IF p_init_msg_list is set to TRUE.
639 IF FND_API.to_Boolean( p_init_msg_list ) THEN
640 FND_MSG_PUB.initialize;
641 END IF;
642
643 -- Initialize API return status to SUCCESS
644 x_return_status := FND_API.G_RET_STS_SUCCESS;
645
646 --
647 -- Api body
648 --
649 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - loan_id: ' || p_loan_id);
650 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - rows received: ' || p_custom_tbl.count);
651 l_custom_tbl2 := p_custom_tbl;
652
653 open c_records_to_destroy(p_loan_id);
654 fetch c_records_to_destroy into l_records_to_destroy;
655 close c_records_to_destroy;
656 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - destroy to: ' || l_records_to_destroy);
657 -- also destroy the 0th row
658 l_count := 0;
659 for k in 1..l_custom_tbl2.count loop
660
661 if l_custom_tbl2(k).payment_number > l_records_to_destroy then
662 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' adding '|| l_custom_tbl2(k).payment_number);
663 l_count := l_count + 1;
664 l_custom_tbl(l_count) := l_custom_tbl2(k);
665 end if;
666 end loop;
667
668 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - after clean up records');
669 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - num records is '|| l_custom_tbl.count);
670
671 lns_custom_pub.validateCustomTable(p_cust_tbl => l_custom_tbl
672 ,p_loan_id => p_loan_id
673 ,p_create_flag => false
674 ,x_installment => l_installment
675 ,x_return_status => l_return_status
676 ,x_msg_count => l_msg_count
677 ,x_msg_data => l_msg_data);
678
679 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
680 if l_installment is not null then
681 X_INVALID_INSTALLMENT_NUM := l_installment;
682 FND_MESSAGE.Set_Name('LNS', 'LNS_INVALID_INSTALLMENT');
683 FND_MESSAGE.SET_TOKEN('PARAMETER', 'INSTALLMENT');
684 FND_MESSAGE.SET_TOKEN('VALUE', l_installment);
685 FND_MSG_PUB.Add;
686 RAISE FND_API.G_EXC_ERROR;
687 end if;
688 FND_MESSAGE.Set_Name('LNS', 'LNS_VALIDATE_CUSTOM_ERROR');
689 FND_MSG_PUB.Add;
690 RAISE FND_API.G_EXC_ERROR;
691 end if;
692
693 -- now we've passed validation initialize loan_begin_balance to calculate balances
694 l_custom_tbl2(1).installment_begin_balance := lns_financials.getRemainingBalance(p_loan_id);
695
696 for k in 1..l_custom_tbl.count
697 loop
698
699 open c_cust_sched_id(p_loan_id, l_custom_tbl(k).payment_number);
700 fetch c_cust_sched_id into
701 l_custom_tbl(k).custom_schedule_id
702 ,l_custom_tbl(k).object_version_number;
703 close c_cust_sched_id;
704
705 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'custom_schedule_id: ' || l_custom_tbl(k).custom_schedule_id);
706 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'principal_amount : ' || l_custom_tbl(k).principal_amount);
707 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'interest_amount : ' || l_custom_tbl(k).interest_amount);
708 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'due_date : ' || l_custom_tbl(k).due_date);
709 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'object_version_number : ' || l_custom_tbl(k).object_version_number);
710 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'installment_begin_balance : ' || l_custom_tbl(k).installment_begin_balance);
711
712 l_custom_rec.CUSTOM_SCHEDULE_ID := l_custom_tbl(k).custom_schedule_id;
713 l_custom_rec.LOAN_ID := p_loan_id;
714 --l_custom_rec.PAYMENT_NUMBER := k;
715 l_custom_rec.PRINCIPAL_AMOUNT := l_custom_tbl(k).principal_amount;
716 l_custom_rec.INTEREST_AMOUNT := l_custom_tbl(k).interest_amount;
717 l_custom_rec.FEE_AMOUNT := l_custom_tbl(k).fee_amount;
718 l_custom_rec.OTHER_AMOUNT := l_custom_tbl(k).other_amount;
719 l_custom_rec.DUE_DATE := l_custom_tbl(k).due_date;
720 l_custom_rec.current_term_payment := l_custom_rec.Fee_AMOUNT + l_custom_rec.INTEREST_AMOUNT + l_custom_rec.PRINCIPAL_AMOUNT;
721 l_custom_rec.OBJECT_VERSION_NUMBER := l_custom_tbl(k).object_version_number;
722 l_custom_rec.installment_begin_balance := l_custom_tbl(k).installment_begin_balance;
723 l_custom_rec.installment_end_balance := l_custom_tbl(k).installment_begin_balance - l_custom_tbl(k).principal_amount;
724
725 -- now calculate the balances
726 if l_custom_rec.installment_end_balance > 0 and k <> l_custom_tbl.count then
727 l_custom_tbl(k+1).installment_begin_balance := l_custom_rec.installment_end_balance;
728 end if;
729
730 -- call api to update rows one-by-one for compliance reasons
731 lns_custom_pub.updateCustomSched(P_CUSTOM_REC => l_custom_rec
732 ,x_return_status => l_return_status
733 ,x_msg_count => l_msg_Count
734 ,x_msg_data => l_msg_Data);
735
736 end loop;
737
738 IF FND_API.to_Boolean(p_commit)
739 THEN
740 COMMIT WORK;
741 END IF;
742
743 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
744 p_data => x_msg_data);
745 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
746
747 EXCEPTION
748 WHEN FND_API.G_EXC_ERROR THEN
749 --FND_MESSAGE.Set_Name('LNS', 'LNS_UPDATE_CUSTOM_ERROR');
750 --FND_MSG_PUB.Add;
751 --RAISE FND_API.G_EXC_ERROR;
752 x_return_status := FND_API.G_RET_STS_ERROR;
753 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
754 ROLLBACK TO updateCustomSchedule;
755
756 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
757 --FND_MESSAGE.Set_Name('LNS', 'LNS_UPDATE_CUSTOM_ERROR');
758 --FND_MSG_PUB.Add;
759 --RAISE FND_API.G_EXC_ERROR;
760 x_return_status := FND_API.G_RET_STS_ERROR;
761 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
762 ROLLBACK TO updateCustomSchedule;
763
764 WHEN OTHERS THEN
765 --FND_MESSAGE.Set_Name('LNS', 'LNS_UPDATE_CUSTOM_ERROR');
766 --FND_MSG_PUB.Add;
767 --RAISE FND_API.G_EXC_ERROR;
768 x_return_status := FND_API.G_RET_STS_ERROR;
769 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
770 ROLLBACK TO updateCustomSchedule;
771
772 end;
773
774
775 procedure validateCustomTable(p_cust_tbl in CUSTOM_TBL
776 ,p_loan_id in number
777 ,p_create_flag in boolean
778 ,x_installment OUT NOCOPY NUMBER
779 ,x_return_status OUT NOCOPY VARCHAR2
780 ,x_msg_count OUT NOCOPY NUMBER
781 ,x_msg_data OUT NOCOPY VARCHAR2)
782 Is
783 l_count number;
784 l_amount number;
785 l_msg_count NUMBER;
786 l_msg_data VARCHAR2(2000);
787 l_return_Status VARCHAR2(1);
788 l_date DATE;
789 l_api_name varchar2(35);
790 l_cust_tbl custom_tbl;
791 l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
792
793 Begin
794 l_api_name := 'validateCustomTable';
795 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Begin');
796 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'in table count is ' || p_cust_tbl.count);
797 -- check if number of incoming rows matches rows on loan_id custom_table
798 -- only if this is an UPDATE
799 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'validate customtbl begin');
800 l_cust_tbl := p_cust_tbl;
801
802 l_loan_details := lns_financials.getLoanDetails(p_loan_id => p_loan_id
803 ,p_based_on_terms => 'CURRENT'
804 ,p_phase => 'TERM');
805 l_count := 0;
806 l_amount := 0;
807 -- destroy any rows prior to the last billed installment
808 -- order the rows --
809 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'before sort');
810 sortRows(p_custom_tbl => l_cust_tbl);
811 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'after sort');
812
813 -- checking updateCustomSchedule first
814 if not p_create_flag then
815 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'in update validation');
816 -- here i need to know only the number of rows that have not been billed
817 Execute Immediate
818 ' Select count(1) ' ||
819 ' From lns_amortization_scheds ' ||
820 ' where loan_id = :p_loan_id ' ||
821 ' and reversed_flag <> ''Y'' ' ||
822 ' and reamortization_amount is null ' ||
823 ' and payment_number > 0 ' ||
824 ' and parent_amortization_id is null '
825 into l_count
826 using p_loan_id;
827
828 --open c_installments(p_loan_id);
829 --fetch c_installments into l_installments;
830 --close c_installments;
831
832 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'original installments ' || l_loan_details.number_installments);
833 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'billed installments (without 0) is ' || l_count);
834
835 if l_loan_details.number_installments - l_count <> l_cust_tbl.count then
836 FND_MESSAGE.Set_Name('LNS', 'LNS_INVALID_NUM_ROWS');
837 FND_MSG_PUB.Add;
838 RAISE FND_API.G_EXC_ERROR;
839 end if;
840 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'passed update validation');
841 end if;
842
843 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'create / update validation');
844 -- now checking each row in the createCustomSchedule
845 for i in 1..l_cust_tbl.count
846 loop
847 /* the begin balance for the first row does not incorporate unpaid billed
848 if i = 1 then
849 -- check that first row in custom table is = remainingBalance
850 -- CHECK THIS WITH KARTHIK
851 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'check balance');
852 if l_cust_tbl(1).installment_begin_balance <> lns_financials.getRemainingBalance(p_loan_id) then
853 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'balance incorrect');
854 FND_MESSAGE.Set_Name('LNS', 'LNS_BEGIN_BALANCE_INCORRECT');
855 FND_MSG_PUB.Add;
856 RAISE FND_API.G_EXC_ERROR;
857 end if;
858 end if;
859 */
860 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'validate custom row');
861 validateCustomRow(p_custom_rec => l_cust_tbl(i)
862 ,x_return_status => l_return_status
863 ,x_msg_count => l_msg_count
864 ,x_msg_data => l_msg_data);
865
866 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
867 x_installment := i;
868 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'invalid installment found #' || i);
869 FND_MESSAGE.Set_Name('LNS', 'LNS_INVALID_INSTALLMENT');
870 FND_MESSAGE.SET_TOKEN('PARAMETER', 'INSTALLMENT');
871 FND_MESSAGE.SET_TOKEN('VALUE', i);
872 FND_MSG_PUB.Add;
873 RAISE FND_API.G_EXC_ERROR;
874 end if;
875
876 -- check for consecutive installments
877 if l_cust_tbl.exists(i+1) then
878 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'checking consecutive installments' || l_cust_tbl(i).payment_number || ' ' || l_cust_tbl(i+1).payment_number );
879 if l_cust_tbl(i).payment_number + 1 <> l_cust_tbl(i+1).payment_number then
880 FND_MESSAGE.Set_Name('LNS', 'LNS_NONSEQUENTIAL_INSTALLMENTS');
881 FND_MSG_PUB.Add;
882 RAISE FND_API.G_EXC_ERROR;
883 end if;
884 end if;
885
886 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'date checking');
887 -- check for consecutive dates
888 if l_date is null then
889 l_date := l_cust_tbl(i).due_date;
890 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'date is null');
891 else
892
893 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'PASS: ' || i || 'p_cust_tbl(i).due_date is : ' || l_cust_tbl(i).due_date);
894 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'l_date is: ' || l_date);
895 if p_cust_tbl(i).due_date <= l_date then
896 FND_MESSAGE.Set_Name('LNS', 'LNS_NONSEQUENTIAL_DATES');
897 FND_MSG_PUB.Add;
898 RAISE FND_API.G_EXC_ERROR;
899 end if;
900 l_date := l_cust_tbl(i).due_date;
901
902 end if;
903
904 l_amount := l_amount + l_cust_tbl(i).principal_amount;
905 end loop;
906
907 -- check if SUM of Prinicipal_Amount is equal to the Funded_Amount
908 -- or requested_amount, etc... based on loan_Status
909 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'final balance check');
910
911 --karamach bug5231822 l_loan_details.unbilled_principal does not return correct value for Direct loan
912 --if l_amount <> l_loan_details.unbilled_principal then
913 if l_amount <> l_loan_details.remaining_balance then
914 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'balance check incorrect');
915 FND_MESSAGE.Set_Name('LNS', 'LNS_BALANCE_INCORRECT');
916 FND_MSG_PUB.Add;
917 RAISE FND_API.G_EXC_ERROR;
918 end if;
919
920 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'after final balance check');
921 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
922
923 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
924
925 EXCEPTION
926
927 WHEN FND_API.G_EXC_ERROR THEN
928 --FND_MESSAGE.Set_Name('LNS', 'LNS_VALIDATE_CUSTOM_ERROR');
929 --FND_MSG_PUB.Add;
930 x_return_status := FND_API.G_RET_STS_ERROR;
931 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
932
933 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
934 --FND_MESSAGE.Set_Name('LNS', 'LNS_VALIDATE_CUSTOM_ERROR');
935 --FND_MSG_PUB.Add;
936 x_return_status := FND_API.G_RET_STS_ERROR;
937 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
938
939 WHEN OTHERS THEN
940 --FND_MESSAGE.Set_Name('LNS', 'LNS_VALIDATE_CUSTOM_ERROR');
941 --FND_MSG_PUB.Add;
942 x_return_status := FND_API.G_RET_STS_ERROR;
943 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
944 End;
945
946 /* procedure to validate a row in the LNS_CUSTOM_PAYMENT_SCHEDULE
947 ||
948 ||
949 ||
950 ||
951 || */
952 procedure validateCustomRow(p_custom_rec in CUSTOM_SCHED_TYPE
953 ,x_return_status OUT NOCOPY VARCHAR2
954 ,x_msg_count OUT NOCOPY NUMBER
955 ,x_msg_data OUT NOCOPY VARCHAR2)
956 is
957 l_msg_count NUMBER;
958 l_msg_data VARCHAR2(2000);
959 l_return_Status VARCHAR2(1);
960 l_api_name varchar2(30);
961
962 BEGIN
963
964 x_return_status := FND_API.G_RET_STS_SUCCESS;
965 l_api_name := 'validateCustRow';
966 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' validate One Row');
967 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' ' || p_custom_rec.PAYMENT_NUMBER );
968 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' ' || p_custom_rec.DUE_DATE );
969 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' ' || p_custom_rec.PRINCIPAL_AMOUNT );
970 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' ' || p_custom_rec.INTEREST_AMOUNT );
971
972 if p_custom_rec.due_Date is null then
973 FND_MESSAGE.Set_Name('LNS', 'LNS_NO_DUE_DATE');
974 FND_MSG_PUB.Add;
975 RAISE FND_API.G_EXC_ERROR;
976
977 elsif p_custom_rec.payment_number is null or p_custom_rec.payment_number < 1 then
978 FND_MESSAGE.Set_Name('LNS', 'LNS_INVALID_PAYMENT_NUMBER');
979 FND_MSG_PUB.Add;
980 RAISE FND_API.G_EXC_ERROR;
981
982 elsif p_custom_rec.PRINCIPAL_AMOUNT is not null and p_custom_rec.PRINCIPAL_AMOUNT < 0 then
983 FND_MESSAGE.Set_Name('LNS', 'LNS_PRINICIPAL_AMOUNT_ERROR');
984 FND_MSG_PUB.Add;
985 RAISE FND_API.G_EXC_ERROR;
986
987 elsif p_custom_rec.INTEREST_AMOUNT is not null and p_custom_rec.INTEREST_AMOUNT < 0 then
988 FND_MESSAGE.Set_Name('LNS', 'LNS_INTEREST_AMOUNT_ERROR');
989 FND_MSG_PUB.Add;
990 RAISE FND_API.G_EXC_ERROR;
991
992 elsif p_custom_rec.FEE_AMOUNT is not null and p_custom_rec.FEE_AMOUNT < 0 then
993 FND_MESSAGE.Set_Name('LNS', 'LNS_OTHER_AMOUNT_ERROR');
994 FND_MSG_PUB.Add;
995 RAISE FND_API.G_EXC_ERROR;
996
997 elsif p_custom_rec.OTHER_AMOUNT is not null and p_custom_rec.OTHER_AMOUNT < 0 then
998 FND_MESSAGE.Set_Name('LNS', 'LNS_OTHER_AMOUNT_ERROR');
999 FND_MSG_PUB.Add;
1000 RAISE FND_API.G_EXC_ERROR;
1001
1002 end if;
1003
1004 EXCEPTION
1005
1006 WHEN FND_API.G_EXC_ERROR THEN
1007 x_return_status := FND_API.G_RET_STS_ERROR;
1008 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1009
1010 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1011 x_return_status := FND_API.G_RET_STS_ERROR;
1012 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1013
1014 WHEN OTHERS THEN
1015 x_return_status := FND_API.G_RET_STS_ERROR;
1016 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1017
1018 END validateCustomRow;
1019
1020 procedure createCustomSched(P_CUSTOM_REC IN CUSTOM_SCHED_TYPE
1021 ,x_custom_sched_id OUT NOCOPY NUMBER
1022 ,x_return_status OUT NOCOPY VARCHAR2
1023 ,x_msg_count OUT NOCOPY NUMBER
1024 ,x_msg_data OUT NOCOPY VARCHAR2)
1025 is
1026 l_msg_count NUMBER;
1027 l_msg_data VARCHAR2(2000);
1028 l_return_Status VARCHAR2(1);
1029 l_custom_id NUMBER;
1030 l_api_name varchar2(25);
1031
1032 BEGIN
1033 l_api_name := 'createCustomSched';
1034 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1035
1036 -- dbms_output.put_line('createCustomAPI' );
1037 LNS_CUSTOM_PAYMNT_SCHEDS_PKG.INSERT_ROW(x_custom_schedule_id => l_custom_id
1038 ,P_LOAN_ID => P_CUSTOM_REC.LOAN_ID
1039 ,P_PAYMENT_NUMBER => P_CUSTOM_REC.PAYMENT_NUMBER
1040 ,P_DUE_DATE => P_CUSTOM_REC.DUE_DATE
1041 ,P_PRINCIPAL_AMOUNT => P_CUSTOM_REC.PRINCIPAL_AMOUNT
1042 ,P_INTEREST_AMOUNT => P_CUSTOM_REC.INTEREST_AMOUNT
1043 -- ,P_PRINCIPAL_BALANCE => P_CUSTOM_REC.PRINCIPAL_BALANCE
1044 ,P_FEE_AMOUNT => P_CUSTOM_REC.FEE_AMOUNT
1045 ,P_OTHER_AMOUNT => P_CUSTOM_REC.OTHER_AMOUNT
1046 ,p_INSTALLMENT_BEGIN_BALANCE => P_CUSTOM_REC.INSTALLMENT_BEGIN_BALANCE
1047 ,p_INSTALLMENT_END_BALANCE => P_CUSTOM_REC.INSTALLMENT_END_BALANCE
1048 ,p_CURRENT_TERM_PAYMENT => P_CUSTOM_REC.CURRENT_TERM_PAYMENT
1049 ,p_OBJECT_VERSION_NUMBER => 1
1050 ,p_ATTRIBUTE_CATEGORY => P_CUSTOM_REC.ATTRIBUTE_CATEGORY
1051 ,p_ATTRIBUTE1 => P_CUSTOM_REC.ATTRIBUTE1
1052 ,p_ATTRIBUTE2 => P_CUSTOM_REC.ATTRIBUTE2
1053 ,p_ATTRIBUTE3 => P_CUSTOM_REC.ATTRIBUTE3
1054 ,p_ATTRIBUTE4 => P_CUSTOM_REC.ATTRIBUTE4
1055 ,p_ATTRIBUTE5 => P_CUSTOM_REC.ATTRIBUTE5
1056 ,p_ATTRIBUTE6 => P_CUSTOM_REC.ATTRIBUTE6
1057 ,p_ATTRIBUTE7 => P_CUSTOM_REC.ATTRIBUTE7
1058 ,p_ATTRIBUTE8 => P_CUSTOM_REC.ATTRIBUTE8
1059 ,p_ATTRIBUTE9 => P_CUSTOM_REC.ATTRIBUTE9
1060 ,p_ATTRIBUTE10 => P_CUSTOM_REC.ATTRIBUTE10
1061 ,p_ATTRIBUTE11 => P_CUSTOM_REC.ATTRIBUTE11
1062 ,p_ATTRIBUTE12 => P_CUSTOM_REC.ATTRIBUTE12
1063 ,p_ATTRIBUTE13 => P_CUSTOM_REC.ATTRIBUTE13
1064 ,p_ATTRIBUTE14 => P_CUSTOM_REC.ATTRIBUTE14
1065 ,p_ATTRIBUTE15 => P_CUSTOM_REC.ATTRIBUTE15
1066 ,p_ATTRIBUTE16 => P_CUSTOM_REC.ATTRIBUTE16
1067 ,p_ATTRIBUTE17 => P_CUSTOM_REC.ATTRIBUTE17
1068 ,p_ATTRIBUTE18 => P_CUSTOM_REC.ATTRIBUTE18
1069 ,p_ATTRIBUTE19 => P_CUSTOM_REC.ATTRIBUTE19
1070 ,p_ATTRIBUTE20 => P_CUSTOM_REC.ATTRIBUTE20
1071 ,p_LOCK_PRIN => P_CUSTOM_REC.LOCK_PRIN
1072 ,p_LOCK_INT => P_CUSTOM_REC.LOCK_INT);
1073
1074 x_custom_sched_id := l_custom_id;
1075 x_return_status := FND_API.G_RET_STS_SUCCESS;
1076
1077 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In createCustomSched: After call Insert_Row ID' || l_Custom_id );
1078
1079 END createCustomSched;
1080
1081 procedure updateCustomSched(P_CUSTOM_REC IN CUSTOM_SCHED_TYPE
1082 ,x_return_status OUT NOCOPY VARCHAR2
1083 ,x_msg_count OUT NOCOPY NUMBER
1084 ,x_msg_data OUT NOCOPY VARCHAR2)
1085 is
1086 l_msg_count NUMBER;
1087 l_msg_data VARCHAR2(2000);
1088 l_return_Status VARCHAR2(1);
1089 l_object_version NUMBER;
1090
1091 l_api_name varchar2(25);
1092
1093 BEGIN
1094 l_api_name := 'updateCustomSched';
1095 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1096
1097 LNS_UTILITY_PUB.GETOBJECTVERSION(P_TABLE_NAME => 'LNS_CUSTOM_PAYMNT_SCHEDS'
1098 ,P_PRIMARY_KEY_NAME => 'CUSTOM_SCHEDULE_ID'
1099 ,P_PRIMARY_KEY_VALUE => P_CUSTOM_REC.CUSTOM_SCHEDULE_ID
1100 ,P_OBJECT_VERSION_NUMBER => P_CUSTOM_REC.OBJECT_VERSION_NUMBER
1101 ,X_OBJECT_VERSION_NUMBER => l_object_version
1102 ,X_MSG_COUNT => l_msg_count
1103 ,X_MSG_DATA => l_msg_data
1104 ,X_RETURN_STATUS => l_return_status);
1105
1106 LNS_CUSTOM_PAYMNT_SCHEDS_PKG.Update_Row(p_CUSTOM_SCHEDULE_ID => P_CUSTOM_REC.CUSTOM_SCHEDULE_ID
1107 ,p_LOAN_ID => P_CUSTOM_REC.LOAN_ID
1108 ,p_PAYMENT_NUMBER => P_CUSTOM_REC.PAYMENT_NUMBER
1109 ,p_DUE_DATE => P_CUSTOM_REC.DUE_DATE
1110 ,p_PRINCIPAL_AMOUNT => P_CUSTOM_REC.PRINCIPAL_AMOUNT
1111 ,p_INTEREST_AMOUNT => P_CUSTOM_REC.INTEREST_AMOUNT
1112 -- ,p_PRINCIPAL_BALANCE => P_CUSTOM_REC.PRINCIPAL_BALANCE
1113 ,p_FEE_AMOUNT => P_CUSTOM_REC.FEE_AMOUNT
1114 ,p_OTHER_AMOUNT => P_CUSTOM_REC.OTHER_AMOUNT
1115 ,p_INSTALLMENT_BEGIN_BALANCE => P_CUSTOM_REC.INSTALLMENT_BEGIN_BALANCE
1116 ,p_INSTALLMENT_END_BALANCE => P_CUSTOM_REC.INSTALLMENT_END_BALANCE
1117 ,p_CURRENT_TERM_PAYMENT => P_CUSTOM_REC.CURRENT_TERM_PAYMENT
1118 ,p_OBJECT_VERSION_NUMBER => l_object_version
1119 ,p_ATTRIBUTE_CATEGORY => P_CUSTOM_REC.ATTRIBUTE_CATEGORY
1120 ,p_ATTRIBUTE1 => P_CUSTOM_REC.ATTRIBUTE1
1121 ,p_ATTRIBUTE2 => P_CUSTOM_REC.ATTRIBUTE2
1122 ,p_ATTRIBUTE3 => P_CUSTOM_REC.ATTRIBUTE3
1123 ,p_ATTRIBUTE4 => P_CUSTOM_REC.ATTRIBUTE4
1124 ,p_ATTRIBUTE5 => P_CUSTOM_REC.ATTRIBUTE5
1125 ,p_ATTRIBUTE6 => P_CUSTOM_REC.ATTRIBUTE6
1126 ,p_ATTRIBUTE7 => P_CUSTOM_REC.ATTRIBUTE7
1127 ,p_ATTRIBUTE8 => P_CUSTOM_REC.ATTRIBUTE8
1128 ,p_ATTRIBUTE9 => P_CUSTOM_REC.ATTRIBUTE9
1129 ,p_ATTRIBUTE10 => P_CUSTOM_REC.ATTRIBUTE10
1130 ,p_ATTRIBUTE11 => P_CUSTOM_REC.ATTRIBUTE11
1131 ,p_ATTRIBUTE12 => P_CUSTOM_REC.ATTRIBUTE12
1132 ,p_ATTRIBUTE13 => P_CUSTOM_REC.ATTRIBUTE13
1133 ,p_ATTRIBUTE14 => P_CUSTOM_REC.ATTRIBUTE14
1134 ,p_ATTRIBUTE15 => P_CUSTOM_REC.ATTRIBUTE15
1135 ,p_ATTRIBUTE16 => P_CUSTOM_REC.ATTRIBUTE16
1136 ,p_ATTRIBUTE17 => P_CUSTOM_REC.ATTRIBUTE17
1137 ,p_ATTRIBUTE18 => P_CUSTOM_REC.ATTRIBUTE18
1138 ,p_ATTRIBUTE19 => P_CUSTOM_REC.ATTRIBUTE19
1139 ,p_ATTRIBUTE20 => P_CUSTOM_REC.ATTRIBUTE20
1140 ,p_LOCK_PRIN => P_CUSTOM_REC.LOCK_PRIN
1141 ,p_LOCK_INT => P_CUSTOM_REC.LOCK_INT);
1142
1143 x_return_status := FND_API.G_RET_STS_SUCCESS;
1144
1145 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In updateCustomSched: After call Insert_Row');
1146
1147 END updateCustomSched;
1148
1149
1150
1151
1152 /*
1153 This funciton will ensure the rows in the custom tbl are ordered by due date.
1154 Will validate that due dates are unique
1155 */
1156 procedure sortRowsByDate(p_custom_tbl in out nocopy LNS_CUSTOM_PUB.custom_tbl)
1157
1158 is
1159 l_custom_tbl LNS_CUSTOM_PUB.custom_tbl;
1160 i number;
1161 j number;
1162 l_temp LNS_CUSTOM_PUB.custom_sched_type;
1163
1164 begin
1165 l_custom_tbl := p_custom_tbl;
1166
1167 -- sort table by due_date
1168 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Sorting by due date...');
1169 for i in REVERSE 1..l_custom_tbl.count loop
1170 for j in 1..(i-1) loop
1171 if l_custom_tbl(j).DUE_DATE > l_custom_tbl(j+1).DUE_DATE then
1172 l_temp := l_custom_tbl(j);
1173 l_custom_tbl(j) := l_custom_tbl(j+1);
1174 l_custom_tbl(j+1) := l_temp;
1175 elsif l_custom_tbl(j).DUE_DATE = l_custom_tbl(j+1).DUE_DATE then
1176 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Several installments have the same due date.');
1177 FND_MESSAGE.SET_NAME('LNS', 'LNS_DUE_DATE_DUPL');
1178 FND_MSG_PUB.Add;
1179 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1180 RAISE FND_API.G_EXC_ERROR;
1181 end if;
1182 end loop;
1183 end loop;
1184 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done sorting.');
1185
1186 p_custom_tbl := l_custom_tbl;
1187 end;
1188
1189
1190
1191
1192 /*
1193 This procedure will filter the custom tbl from deleted rows
1194 */
1195 procedure filterCustSchedule(p_custom_tbl in out nocopy LNS_CUSTOM_PUB.custom_tbl)
1196
1197 is
1198 l_custom_tbl LNS_CUSTOM_PUB.custom_tbl;
1199 l_new_custom_tbl LNS_CUSTOM_PUB.custom_tbl;
1200 i number;
1201 j number;
1202
1203 begin
1204 l_custom_tbl := p_custom_tbl;
1205 j := 0;
1206
1207 -- filtering table from deleted rows
1208 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Filtering...');
1209 for i in 1..l_custom_tbl.count loop
1210 if l_custom_tbl(i).ACTION is null or l_custom_tbl(i).ACTION <> 'D' then
1211 j := j + 1;
1212 l_new_custom_tbl(j) := l_custom_tbl(i);
1213 end if;
1214 end loop;
1215 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done filtering.');
1216
1217 p_custom_tbl := l_new_custom_tbl;
1218 end;
1219
1220
1221 /*
1222 This procedure synchs rate schedule with new number of installments
1223 */
1224 procedure synchRateSchedule(p_term_id in number, p_num_installments in number)
1225
1226 is
1227
1228 /*-----------------------------------------------------------------------+
1229 | Local Variable Declarations and initializations |
1230 +-----------------------------------------------------------------------*/
1231
1232 l_RATE_ID number;
1233 l_RATE number;
1234 l_BEGIN_INSTALLMENT number;
1235 l_END_INSTALLMENT number;
1236 i number;
1237
1238 /*-----------------------------------------------------------------------+
1239 | Cursor Declarations |
1240 +-----------------------------------------------------------------------*/
1241
1242 -- cursor to load rate schedule
1243 cursor c_rate_sched(p_term_id NUMBER) IS
1244 select RATE_ID, CURRENT_INTEREST_RATE, BEGIN_INSTALLMENT_NUMBER, END_INSTALLMENT_NUMBER
1245 from lns_rate_schedules
1246 where term_id = p_term_id and
1247 END_DATE_ACTIVE is null and
1248 nvl(PHASE, 'TERM') = 'TERM'
1249 order by END_INSTALLMENT_NUMBER desc;
1250
1251 begin
1252
1253 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Synching rate schedule...');
1254 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_term_id: ' || p_term_id);
1255 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_num_installments: ' || p_num_installments);
1256
1257 -- finding right rate row and update it
1258 OPEN c_rate_sched(p_term_id);
1259 LOOP
1260 i := i + 1;
1261 FETCH c_rate_sched INTO
1262 l_RATE_ID,
1263 l_RATE,
1264 l_BEGIN_INSTALLMENT,
1265 l_END_INSTALLMENT;
1266
1267 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, i || ') Rate ' || l_RATE || ': ' || l_BEGIN_INSTALLMENT || ' - ' || l_END_INSTALLMENT);
1268
1269 if p_num_installments > l_END_INSTALLMENT then
1270
1271 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating this row - set END_INSTALLMENT_NUMBER = ' || p_num_installments);
1272
1273 update lns_rate_schedules
1274 set END_INSTALLMENT_NUMBER = p_num_installments
1275 where term_id = p_term_id and
1276 RATE_ID = l_RATE_ID;
1277
1278 exit;
1279
1280 elsif p_num_installments >= l_BEGIN_INSTALLMENT and p_num_installments <= l_END_INSTALLMENT then
1281
1282 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating this row - set END_INSTALLMENT_NUMBER = ' || p_num_installments);
1283
1284 update lns_rate_schedules
1285 set END_INSTALLMENT_NUMBER = p_num_installments
1286 where term_id = p_term_id and
1287 RATE_ID = l_RATE_ID;
1288
1289 exit;
1290
1291 elsif p_num_installments < l_BEGIN_INSTALLMENT then
1292
1293 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Deleting this row');
1294
1295 delete from lns_rate_schedules
1296 where term_id = p_term_id and
1297 RATE_ID = l_RATE_ID;
1298
1299 end if;
1300
1301 END LOOP;
1302
1303 CLOSE c_rate_sched;
1304 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done synching');
1305
1306 end;
1307
1308
1309
1310 /*
1311 This procedure synchs rate schedule with new number of installments in memory only, no changes to db
1312 */
1313 procedure synchRateSchedule(p_rate_tbl IN OUT NOCOPY LNS_FINANCIALS.RATE_SCHEDULE_TBL, p_num_installments in number)
1314
1315 is
1316
1317 /*-----------------------------------------------------------------------+
1318 | Local Variable Declarations and initializations |
1319 +-----------------------------------------------------------------------*/
1320
1321 l_RATE_ID number;
1322 l_RATE number;
1323 l_BEGIN_INSTALLMENT number;
1324 l_END_INSTALLMENT number;
1325 i number;
1326 l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
1327
1328 /*-----------------------------------------------------------------------+
1329 | Cursor Declarations |
1330 +-----------------------------------------------------------------------*/
1331
1332 begin
1333
1334 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Synching rate schedule...');
1335 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_num_installments: ' || p_num_installments);
1336
1337 l_rate_tbl := p_rate_tbl;
1338
1339 -- finding right rate row and update it
1340 for i in REVERSE 1..l_rate_tbl.count loop
1341
1342 l_RATE := l_rate_tbl(i).ANNUAL_RATE;
1343 l_BEGIN_INSTALLMENT := l_rate_tbl(i).BEGIN_INSTALLMENT_NUMBER;
1344 l_END_INSTALLMENT := l_rate_tbl(i).END_INSTALLMENT_NUMBER;
1345
1346 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, i || ') Rate ' || l_RATE || ': ' || l_BEGIN_INSTALLMENT || ' - ' || l_END_INSTALLMENT);
1347
1348 if p_num_installments > l_END_INSTALLMENT then
1349
1350 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating this row - set END_INSTALLMENT_NUMBER = ' || p_num_installments);
1351 l_rate_tbl(i).END_INSTALLMENT_NUMBER := p_num_installments;
1352
1353 exit;
1354
1355 elsif p_num_installments >= l_BEGIN_INSTALLMENT and p_num_installments <= l_END_INSTALLMENT then
1356
1357 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating this row - set END_INSTALLMENT_NUMBER = ' || p_num_installments);
1358 l_rate_tbl(i).END_INSTALLMENT_NUMBER := p_num_installments;
1359
1360 exit;
1361
1362 elsif p_num_installments < l_BEGIN_INSTALLMENT then
1363
1364 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Deleting this row');
1365 l_rate_tbl.delete(i);
1366
1367 end if;
1368
1369 END LOOP;
1370
1371 p_rate_tbl := l_rate_tbl;
1372 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done synching');
1373
1374 end;
1375
1376
1377
1378 -- This function returns payment schedule record
1379 -- introduced for bug 7319358
1380 function getPayment(P_LOAN_ID IN NUMBER, P_PAYMENT_NUMBER IN NUMBER) return LNS_FIN_UTILS.PAYMENT_SCHEDULE
1381 IS
1382
1383 /*-----------------------------------------------------------------------+
1384 | Local Variable Declarations and initializations |
1385 +-----------------------------------------------------------------------*/
1386
1387 l_api_name CONSTANT VARCHAR2(30) := 'getPayment';
1388 l_payment_schedule LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
1389 l_payment LNS_FIN_UTILS.PAYMENT_SCHEDULE;
1390 i number;
1391
1392 /*-----------------------------------------------------------------------+
1393 | Cursor Declarations |
1394 +-----------------------------------------------------------------------*/
1395
1396 BEGIN
1397
1398 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
1399 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
1400 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID: ' || P_LOAN_ID);
1401 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_PAYMENT_NUMBER: ' || P_PAYMENT_NUMBER);
1402
1403 l_payment_schedule := buildCustomPaySchedule(P_LOAN_ID);
1404 for i in 1..l_payment_schedule.count loop
1405 if P_PAYMENT_NUMBER = i then
1406 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Found payment ' || P_PAYMENT_NUMBER);
1407 l_payment := l_payment_schedule(i);
1408 exit;
1409 end if;
1410 end loop;
1411
1412 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
1413
1414 return l_payment;
1415 END;
1416
1417
1418
1419 function getLoanDetails(p_loan_id in number
1420 ,p_based_on_terms in varchar2) return LNS_CUSTOM_PUB.LOAN_DETAILS_REC
1421
1422 is
1423
1424 /*-----------------------------------------------------------------------+
1425 | Local Variable Declarations and initializations |
1426 +-----------------------------------------------------------------------*/
1427
1428 l_api_name CONSTANT VARCHAR2(30) := 'getLoanDetails';
1429 l_loan_Details LNS_CUSTOM_PUB.LOAN_DETAILS_REC;
1430 l_billed_principal number;
1431
1432 /*-----------------------------------------------------------------------+
1433 | Cursor Declarations |
1434 +-----------------------------------------------------------------------*/
1435 CURSOR c_loan_details(p_Loan_id NUMBER, p_based_on_terms varchar2) IS
1436 SELECT h.loan_id
1437 ,t.amortization_frequency
1438 ,t.loan_payment_frequency
1439 ,trunc(h.loan_start_date)
1440 ,h.funded_amount
1441 ,lns_financials.getRemainingBalance(p_loan_id)
1442 ,trunc(h.loan_maturity_date)
1443 ,decode(p_based_on_terms, 'CURRENT', LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id), 0)
1444 -- ,decode(p_based_on_terms, 'CURRENT', LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER_EXT(p_loan_id), -1)
1445 ,decode(nvl(t.day_count_method, 'PERIODIC30_360'), 'PERIODIC30_360', '30/360', t.day_count_method)
1446 ,nvl(h.custom_payments_flag, 'N')
1447 ,h.loan_status
1448 ,h.loan_currency
1449 ,curr.precision
1450 -- ,nvl(t.PAYMENT_CALC_METHOD, 'EQUAL_PAYMENT')
1451 ,decode(p_based_on_terms,
1452 'CURRENT', decode(nvl(h.custom_payments_flag, 'N'), 'Y', nvl(t.PAYMENT_CALC_METHOD, 'CUSTOM'),
1453 'N', nvl(t.PAYMENT_CALC_METHOD, 'EQUAL_PAYMENT')),
1454 decode(nvl(h.custom_payments_flag, 'N'), 'Y', nvl(t.ORIG_PAY_CALC_METHOD, 'CUSTOM'),
1455 'N', nvl(t.PAYMENT_CALC_METHOD, 'EQUAL_PAYMENT'))
1456 )
1457 ,t.CALCULATION_METHOD
1458 ,t.INTEREST_COMPOUNDING_FREQ
1459 ,nvl(t.CUSTOM_CALC_METHOD, 'NONE')
1460 -- ,nvl(t.CUSTOM_CALC_METHOD, decode(nvl(t.PAYMENT_CALC_METHOD, 'EQUAL_PAYMENT'), 'EQUAL_PAYMENT', 'EQUAL_PAYMENT',
1461 -- 'EQUAL_PAYMENT_STANDARD', 'EQUAL_PAYMENT', 'EQUAL_PRINCIPAL', 'EQUAL_PRINCIPAL', 'SEPARATE_SCHEDULES', 'EQUAL_PRINCIPAL'))
1462 ,t.ORIG_PAY_CALC_METHOD
1463 ,t.RATE_TYPE RATE_TYPE
1464 ,t.CEILING_RATE TERM_CEILING_RATE
1465 ,t.FLOOR_RATE TERM_FLOOR_RATE
1466 ,t.PERCENT_INCREASE TERM_PERCENT_INCREASE
1467 ,t.PERCENT_INCREASE_LIFE TERM_PERCENT_INCREASE_LIFE
1468 ,t.FIRST_PERCENT_INCREASE TERM_FIRST_PERCENT_INCREASE
1469 ,t.INDEX_RATE_ID TERM_INDEX_RATE_ID
1470 ,t.TERM_PROJECTED_RATE INITIAL_INTEREST_RATE
1471 ,nvl(lns_fin_utils.getActiveRate(h.loan_id), t.TERM_PROJECTED_RATE) LAST_INTEREST_RATE
1472 ,nvl(t.FIRST_RATE_CHANGE_DATE, t.NEXT_RATE_CHANGE_DATE) FIRST_RATE_CHANGE_DATE
1473 ,t.NEXT_RATE_CHANGE_DATE NEXT_RATE_CHANGE_DATE
1474 ,t.TERM_PROJECTED_RATE TERM_PROJECTED_RATE
1475 ,nvl(t.PENAL_INT_RATE, 0)
1476 ,nvl(t.PENAL_INT_GRACE_DAYS, 0)
1477
1478 FROM lns_loan_headers_all h
1479 ,lns_terms t
1480 ,fnd_currencies curr
1481 WHERE h.loan_id = p_loan_id
1482 AND h.loan_id = t.loan_id
1483 AND curr.currency_code = h.loan_currency;
1484
1485 cursor c_balanceInfo(p_loan_id NUMBER, p_phase varchar2) IS
1486 select nvl(sum(amort.PRINCIPAL_AMOUNT),0) -- billed principal
1487 ,nvl(sum(amort.PRINCIPAL_REMAINING),0) -- unpaid principal
1488 ,nvl(sum(amort.INTEREST_REMAINING),0) -- unpaid interest
1489 from LNS_AM_SCHEDS_V amort
1490 where amort.Loan_id = p_loan_id
1491 and amort.REVERSED_CODE = 'N'
1492 and amort.phase = p_phase;
1493
1494 -- cursor to get last bill due date
1495 cursor c_due_date(p_loan_id NUMBER) IS
1496 select trunc(max(DUE_DATE))
1497 from lns_amortization_scheds
1498 where loan_id = p_loan_id
1499 and (REVERSED_FLAG is null or REVERSED_FLAG = 'N')
1500 --and parent_amortization_id is null
1501 and REAMORTIZATION_AMOUNT is null
1502 and nvl(phase, 'TERM') = 'TERM';
1503
1504 begin
1505
1506 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || '+');
1507 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Input:');
1508 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'p_loan_id: ' || p_loan_id);
1509 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'p_based_on_terms: ' || p_based_on_terms);
1510
1511 OPEN c_loan_details(p_loan_id, p_based_on_terms);
1512 FETCH c_loan_details INTO
1513 l_loan_details.loan_id
1514 ,l_loan_Details.amortization_frequency
1515 ,l_loan_Details.payment_frequency
1516 ,l_loan_Details.loan_start_date
1517 ,l_loan_details.funded_amount
1518 ,l_loan_details.remaining_balance
1519 ,l_loan_details.maturity_Date
1520 ,l_loan_details.last_installment_billed
1521 ,l_loan_details.day_count_method
1522 ,l_loan_details.custom_schedule
1523 ,l_loan_details.loan_status
1524 ,l_loan_details.loan_currency
1525 ,l_loan_details.currency_precision
1526 ,l_loan_details.PAYMENT_CALC_METHOD
1527 ,l_loan_details.CALCULATION_METHOD
1528 ,l_loan_details.INTEREST_COMPOUNDING_FREQ
1529 ,l_loan_details.CUSTOM_CALC_METHOD
1530 ,l_loan_details.ORIG_PAY_CALC_METHOD
1531 ,l_loan_details.RATE_TYPE -- fixed or variable
1532 ,l_loan_details.TERM_CEILING_RATE -- term ceiling rate
1533 ,l_loan_details.TERM_FLOOR_RATE -- term floor rate
1534 ,l_loan_details.TERM_ADJ_PERCENT_INCREASE -- term percentage increase btwn adjustments
1535 ,l_loan_details.TERM_LIFE_PERCENT_INCREASE -- term lifetime max adjustment for interest
1536 ,l_loan_details.TERM_FIRST_PERCENT_INCREASE -- term first percentage increase
1537 ,l_loan_details.TERM_INDEX_RATE_ID
1538 ,l_loan_details.INITIAL_INTEREST_RATE -- current phase only
1539 ,l_loan_details.LAST_INTEREST_RATE -- current phase only
1540 ,l_loan_details.FIRST_RATE_CHANGE_DATE -- current phase only
1541 ,l_loan_details.NEXT_RATE_CHANGE_DATE -- current phase only
1542 ,l_loan_details.TERM_PROJECTED_INTEREST_RATE -- term projected interest rate
1543 ,l_loan_details.PENAL_INT_RATE
1544 ,l_loan_details.PENAL_INT_GRACE_DAYS;
1545 close c_loan_details;
1546
1547 -- use this part of the procedure to differentiate between
1548 -- elements that are calculated differently for current and original
1549 -- amortization
1550 if p_based_on_terms = 'CURRENT' then
1551
1552 Begin
1553 -- get balance information
1554 open c_balanceInfo(p_loan_id, 'TERM');
1555 fetch c_balanceInfo into
1556 l_billed_principal
1557 ,l_loan_details.unpaid_principal
1558 ,l_loan_details.UNPAID_INTEREST;
1559 close c_balanceInfo;
1560 l_loan_details.unbilled_principal := l_loan_details.funded_amount - l_billed_principal;
1561 Exception
1562 when no_data_found then
1563 l_loan_details.unpaid_principal := 0;
1564 l_loan_details.unbilled_principal := l_loan_details.funded_amount;
1565 l_loan_details.UNPAID_INTEREST := 0;
1566 End;
1567
1568 -- get last due date
1569 Begin
1570 open c_due_date(p_loan_id);
1571 fetch c_due_date into l_loan_details.LAST_DUE_DATE;
1572 close c_due_date;
1573 Exception
1574 when no_data_found then
1575 l_loan_details.LAST_DUE_DATE := null;
1576 End;
1577
1578 else
1579 l_loan_details.unpaid_principal := 0;
1580 l_loan_details.unbilled_principal := l_loan_details.funded_amount;
1581 l_loan_details.UNPAID_INTEREST := 0;
1582 l_loan_details.LAST_DUE_DATE := null;
1583 end if;
1584
1585 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Loan details:');
1586 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' custom_schedule: ' || l_loan_details.custom_schedule);
1587 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' amortization_frequency: ' || l_loan_details.amortization_frequency);
1588 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' payment_frequency: ' || l_loan_details.payment_frequency);
1589 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' loan start date: ' || l_loan_details.loan_start_date);
1590 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' funded_amount: ' || l_loan_details.funded_amount);
1591 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' remaining balance: ' || l_loan_details.remaining_balance);
1592 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' maturity_date: ' || l_loan_details.maturity_Date);
1593 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' last installment billed: ' || l_loan_details.last_installment_billed);
1594 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' day Count method: ' || l_loan_details.day_count_method);
1595 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' loan_status: ' || l_loan_details.loan_status);
1596 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' loan_currency: ' || l_loan_details.loan_currency);
1597 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' currency_precision: ' || l_loan_details.currency_precision);
1598 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' PAYMENT_CALC_METHOD: ' || l_loan_details.PAYMENT_CALC_METHOD);
1599 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' CALCULATION_METHOD: ' || l_loan_details.CALCULATION_METHOD);
1600 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' INTEREST_COMPOUNDING_FREQ: ' || l_loan_details.INTEREST_COMPOUNDING_FREQ);
1601 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' unpaid_principal: ' || l_loan_details.unpaid_principal);
1602 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' unbilled_principal: ' || l_loan_details.unbilled_principal);
1603 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' UNPAID_INTEREST: ' || l_loan_details.UNPAID_INTEREST);
1604 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' LAST_DUE_DATE: ' || l_loan_details.LAST_DUE_DATE);
1605 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' CUSTOM_CALC_METHOD: ' || l_loan_details.CUSTOM_CALC_METHOD);
1606 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' ORIG_PAY_CALC_METHOD: ' || l_loan_details.ORIG_PAY_CALC_METHOD);
1607 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || '-');
1608
1609 return l_loan_details;
1610
1611 Exception
1612 When No_Data_Found then
1613 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'LOAN ID: ' || p_loan_id || ' not found');
1614 FND_MESSAGE.Set_Name('LNS', 'LNS_INVALID_LOAN_ID');
1615 FND_MSG_PUB.Add;
1616 RAISE FND_API.G_EXC_ERROR;
1617
1618 When Others then
1619 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Err: ' || sqlerrm);
1620 RAISE FND_API.G_EXC_ERROR;
1621
1622 end getLoanDetails;
1623
1624
1625
1626 -- This procedure loads custom schedule from db
1627 procedure loadCustomSchedule(
1628 P_API_VERSION IN NUMBER,
1629 P_INIT_MSG_LIST IN VARCHAR2,
1630 P_COMMIT IN VARCHAR2,
1631 P_VALIDATION_LEVEL IN NUMBER,
1632 P_LOAN_ID IN NUMBER,
1633 P_BASED_ON_TERMS IN VARCHAR2,
1634 X_AMORT_METHOD OUT NOCOPY VARCHAR2,
1635 X_CUSTOM_TBL OUT NOCOPY LNS_CUSTOM_PUB.CUSTOM_TBL,
1636 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1637 X_MSG_COUNT OUT NOCOPY NUMBER,
1638 X_MSG_DATA OUT NOCOPY VARCHAR2)
1639 IS
1640
1641 /*-----------------------------------------------------------------------+
1642 | Local Variable Declarations and initializations |
1643 +-----------------------------------------------------------------------*/
1644
1645 l_api_name CONSTANT VARCHAR2(30) := 'loadCustomSchedule';
1646 l_api_version CONSTANT NUMBER := 1.0;
1647 l_return_status VARCHAR2(1);
1648 l_msg_count NUMBER;
1649 l_msg_data VARCHAR2(32767);
1650
1651 l_loan_details LNS_CUSTOM_PUB.LOAN_DETAILS_REC;
1652 l_custom_tbl LNS_CUSTOM_PUB.CUSTOM_TBL;
1653 l_temp_row LNS_CUSTOM_PUB.custom_sched_type;
1654 l_amort_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
1655 i number;
1656 j number;
1657
1658 /*-----------------------------------------------------------------------+
1659 | Cursor Declarations |
1660 +-----------------------------------------------------------------------*/
1661
1662 -- cursor to load custom schedule
1663 -- fix for bug 7026226: default PRINCIPAL_AMOUNT and INTEREST_AMOUNT to 0 if they are null
1664 cursor c_load_sched(p_loan_id NUMBER, p_begin_installment NUMBER) IS
1665 select
1666 CUSTOM_SCHEDULE_ID,
1667 LOAN_ID,
1668 PAYMENT_NUMBER,
1669 DUE_DATE,
1670 nvl(PRINCIPAL_AMOUNT, 0),
1671 nvl(INTEREST_AMOUNT, 0),
1672 nvl(FEE_AMOUNT, 0),
1673 nvl(OTHER_AMOUNT, 0),
1674 nvl(LOCK_PRIN, 'Y'),
1675 nvl(LOCK_INT, 'Y')
1676 from LNS_CUSTOM_PAYMNT_SCHEDS
1677 where loan_id = p_loan_id
1678 and PAYMENT_NUMBER > p_begin_installment
1679 order by PAYMENT_NUMBER;
1680
1681 BEGIN
1682
1683 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
1684
1685 -- Standard start of API savepoint
1686 SAVEPOINT loadCustomSchedule;
1687 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Savepoint is established');
1688
1689 -- Standard call to check for call compatibility
1690 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1691 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1692 END IF;
1693
1694 -- Initialize message list if p_init_msg_list is set to TRUE
1695 IF FND_API.To_Boolean(p_init_msg_list) THEN
1696 FND_MSG_PUB.initialize;
1697 END IF;
1698
1699 -- Initialize API return status to success
1700 l_return_status := FND_API.G_RET_STS_SUCCESS;
1701
1702 -- START OF BODY OF API
1703
1704 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
1705 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID: ' || P_LOAN_ID);
1706 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_BASED_ON_TERMS: ' || P_BASED_ON_TERMS);
1707
1708 if P_LOAN_ID is null then
1709 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
1710 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
1711 FND_MESSAGE.SET_TOKEN('VALUE', P_LOAN_ID);
1712 FND_MSG_PUB.ADD;
1713 RAISE FND_API.G_EXC_ERROR;
1714 end if;
1715
1716 if P_BASED_ON_TERMS is null then
1717 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
1718 FND_MESSAGE.SET_TOKEN('PARAMETER', 'BASED_ON_TERMS');
1719 FND_MESSAGE.SET_TOKEN('VALUE', P_BASED_ON_TERMS);
1720 FND_MSG_PUB.ADD;
1721 RAISE FND_API.G_EXC_ERROR;
1722 end if;
1723
1724 l_loan_details := getLoanDetails(p_loan_Id => p_loan_id
1725 ,p_based_on_terms => p_based_on_terms);
1726
1727 if (l_loan_details.CUSTOM_SCHEDULE = 'N' or
1728 (l_loan_details.CUSTOM_SCHEDULE = 'Y' and l_loan_details.loan_status <> 'INCOMPLETE' and
1729 p_based_on_terms <> 'CURRENT' and l_loan_details.ORIG_PAY_CALC_METHOD is not null))
1730 then
1731
1732 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_FINANCIALS.runAmortization...');
1733 LNS_FINANCIALS.runAmortization(
1734 P_API_VERSION => 1.0,
1735 P_INIT_MSG_LIST => FND_API.G_TRUE,
1736 P_COMMIT => FND_API.G_FALSE,
1737 P_LOAN_ID => P_LOAN_ID,
1738 P_BASED_ON_TERMS => P_BASED_ON_TERMS,
1739 x_amort_tbl => l_amort_tbl,
1740 x_return_status => l_return_status,
1741 x_msg_count => l_msg_count,
1742 x_msg_data => l_msg_data);
1743
1744 IF l_return_status <> 'S' THEN
1745 RAISE FND_API.G_EXC_ERROR;
1746 END IF;
1747
1748 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Schedule from LNS_FINANCIALS.runAmortization:');
1749 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PN DD PRIN LP INT LI FEE OTH ID');
1750 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, '--- -------- ------- -- ------- -- ----- ----- ------');
1751
1752 j := 0;
1753 for i in 1..l_amort_tbl.count loop
1754
1755 if l_amort_tbl(i).INSTALLMENT_NUMBER > 0 then -- excluding 0-th installment from customization
1756 j := j + 1;
1757 l_custom_tbl(j).LOAN_ID := P_LOAN_ID;
1758 l_custom_tbl(j).PAYMENT_NUMBER := l_amort_tbl(i).INSTALLMENT_NUMBER;
1759 l_custom_tbl(j).DUE_DATE := l_amort_tbl(i).DUE_DATE;
1760 l_custom_tbl(j).PRINCIPAL_AMOUNT := l_amort_tbl(i).PRINCIPAL_AMOUNT;
1761 l_custom_tbl(j).LOCK_PRIN := 'Y';
1762 l_custom_tbl(j).INTEREST_AMOUNT := l_amort_tbl(i).INTEREST_AMOUNT;
1763 l_custom_tbl(j).LOCK_INT := 'Y';
1764 l_custom_tbl(j).FEE_AMOUNT := l_amort_tbl(i).FEE_AMOUNT;
1765 l_custom_tbl(j).OTHER_AMOUNT := l_amort_tbl(i).OTHER_AMOUNT;
1766 l_custom_tbl(j).ACTION := 'I';
1767
1768 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME,
1769 l_custom_tbl(j).PAYMENT_NUMBER || ' ' ||
1770 l_custom_tbl(j).DUE_DATE || ' ' ||
1771 l_custom_tbl(j).PRINCIPAL_AMOUNT || ' ' ||
1772 l_custom_tbl(j).LOCK_PRIN || ' ' ||
1773 l_custom_tbl(j).INTEREST_AMOUNT || ' ' ||
1774 l_custom_tbl(j).LOCK_INT || ' ' ||
1775 l_custom_tbl(j).FEE_AMOUNT || ' ' ||
1776 l_custom_tbl(j).OTHER_AMOUNT || ' ' ||
1777 l_custom_tbl(j).CUSTOM_SCHEDULE_ID);
1778 end if;
1779
1780 end loop;
1781
1782 else
1783
1784 i := 0;
1785 OPEN c_load_sched(p_loan_id, l_loan_details.LAST_INSTALLMENT_BILLED);
1786
1787 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Loading custom schedule from LNS_CUSTOM_PAYMNT_SCHEDS:');
1788 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PN DD PRIN LP INT LI FEE OTH ID');
1789 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, '--- -------- ------- -- ------- -- ----- ----- ------');
1790
1791 LOOP
1792
1793 FETCH c_load_sched INTO
1794 l_temp_row.CUSTOM_SCHEDULE_ID,
1795 l_temp_row.LOAN_ID,
1796 l_temp_row.PAYMENT_NUMBER,
1797 l_temp_row.DUE_DATE,
1798 l_temp_row.PRINCIPAL_AMOUNT,
1799 l_temp_row.INTEREST_AMOUNT,
1800 l_temp_row.FEE_AMOUNT,
1801 l_temp_row.OTHER_AMOUNT,
1802 l_temp_row.LOCK_PRIN,
1803 l_temp_row.LOCK_INT;
1804 exit when c_load_sched%NOTFOUND;
1805
1806 i := i + 1;
1807 l_custom_tbl(i) := l_temp_row;
1808
1809 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME,
1810 l_custom_tbl(i).PAYMENT_NUMBER || ' ' ||
1811 l_custom_tbl(i).DUE_DATE || ' ' ||
1812 l_custom_tbl(i).PRINCIPAL_AMOUNT || ' ' ||
1813 l_custom_tbl(i).LOCK_PRIN || ' ' ||
1814 l_custom_tbl(i).INTEREST_AMOUNT || ' ' ||
1815 l_custom_tbl(i).LOCK_INT || ' ' ||
1816 l_custom_tbl(i).FEE_AMOUNT || ' ' ||
1817 l_custom_tbl(i).OTHER_AMOUNT || ' ' ||
1818 l_custom_tbl(i).CUSTOM_SCHEDULE_ID);
1819
1820 END LOOP;
1821 CLOSE c_load_sched;
1822
1823 end if;
1824
1825 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Recalculating schedule...');
1826 LNS_CUSTOM_PUB.recalcCustomSchedule(
1827 P_API_VERSION => 1.0,
1828 P_INIT_MSG_LIST => FND_API.G_TRUE,
1829 P_COMMIT => FND_API.G_FALSE,
1830 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1831 P_LOAN_ID => P_LOAN_ID,
1832 P_AMORT_METHOD => l_loan_details.CUSTOM_CALC_METHOD,
1833 P_BASED_ON_TERMS => P_BASED_ON_TERMS,
1834 P_CUSTOM_TBL => l_custom_tbl,
1835 x_return_status => l_return_status,
1836 x_msg_count => l_msg_count,
1837 x_msg_data => l_msg_data);
1838
1839 IF l_return_status <> 'S' THEN
1840 RAISE FND_API.G_EXC_ERROR;
1841 END IF;
1842
1843 if P_COMMIT = FND_API.G_TRUE then
1844 COMMIT WORK;
1845 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
1846 end if;
1847
1848 X_CUSTOM_TBL := l_CUSTOM_TBL;
1849 X_AMORT_METHOD := l_loan_details.CUSTOM_CALC_METHOD;
1850
1851 -- END OF BODY OF API
1852 x_return_status := FND_API.G_RET_STS_SUCCESS;
1853
1854 -- Standard call to get message count and if count is 1, get message info
1855 FND_MSG_PUB.Count_And_Get(
1856 p_encoded => FND_API.G_FALSE,
1857 p_count => x_msg_count,
1858 p_data => x_msg_data);
1859
1860 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
1861
1862 EXCEPTION
1863 WHEN FND_API.G_EXC_ERROR THEN
1864 ROLLBACK TO loadCustomSchedule;
1865 x_return_status := FND_API.G_RET_STS_ERROR;
1866 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1867 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
1868 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1869 ROLLBACK TO loadCustomSchedule;
1870 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1871 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1872 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
1873 WHEN OTHERS THEN
1874 ROLLBACK TO loadCustomSchedule;
1875 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1876 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1877 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1878 END IF;
1879 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1880 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
1881 END;
1882
1883
1884
1885 -- This procedure recalculates custom schedule
1886 procedure recalcCustomSchedule(
1887 P_API_VERSION IN NUMBER,
1888 P_INIT_MSG_LIST IN VARCHAR2,
1889 P_COMMIT IN VARCHAR2,
1890 P_VALIDATION_LEVEL IN NUMBER,
1891 P_LOAN_ID IN NUMBER,
1892 P_AMORT_METHOD IN VARCHAR2,
1893 P_BASED_ON_TERMS IN VARCHAR2,
1894 P_CUSTOM_TBL IN OUT NOCOPY LNS_CUSTOM_PUB.CUSTOM_TBL,
1895 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1896 X_MSG_COUNT OUT NOCOPY NUMBER,
1897 X_MSG_DATA OUT NOCOPY VARCHAR2)
1898 IS
1899
1900 /*-----------------------------------------------------------------------+
1901 | Local Variable Declarations and initializations |
1902 +-----------------------------------------------------------------------*/
1903
1904 l_api_name CONSTANT VARCHAR2(30) := 'recalcCustomSchedule';
1905 l_api_version CONSTANT NUMBER := 1.0;
1906 l_return_status VARCHAR2(1);
1907 l_msg_count NUMBER;
1908 l_msg_data VARCHAR2(32767);
1909
1910 l_loan_details LNS_CUSTOM_PUB.LOAN_DETAILS_REC;
1911 l_custom_tbl LNS_CUSTOM_PUB.CUSTOM_TBL;
1912 l_temp_row LNS_CUSTOM_PUB.custom_sched_type;
1913 l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
1914 l_rate_details LNS_FINANCIALS.INTEREST_RATE_REC;
1915
1916 l_compound_freq varchar2(30);
1917 l_remaining_balance_actual number;
1918 l_remaining_balance_theory number;
1919 l_remaining_balance_theory1 number;
1920 l_last_installment_billed number;
1921 l_calc_method varchar2(30);
1922 l_day_count_method varchar2(30);
1923 l_unbilled_principal number;
1924 l_num_unlocked_prin number;
1925 --l_pay_in_arrears boolean;
1926 l_period_begin_date date;
1927 l_period_end_date date;
1928 l_precision number;
1929 l_periodic_rate number;
1930 l_periodic_principal number;
1931 l_annualized_rate number;
1932 l_locked_prin number;
1933 l_unpaid_amount number;
1934 l_payment_freq varchar2(30);
1935 l_previous_annualized number;
1936 l_rate_to_calculate number;
1937 l_amortization_intervals number;
1938 l_periodic_payment number;
1939 l_unpaid_principal number;
1940 l_unpaid_interest number;
1941 l_num_installments number;
1942 i number;
1943 l_installment number;
1944 l_raw_rate number;
1945 l_norm_interest number;
1946 l_add_prin_interest number;
1947 l_add_int_interest number;
1948 l_add_start_date date;
1949 l_add_end_date date;
1950 l_penal_prin_interest number;
1951 l_penal_int_interest number;
1952 l_penal_interest number;
1953 l_prev_grace_end_date date;
1954 l_payment LNS_FIN_UTILS.PAYMENT_SCHEDULE;
1955
1956 -- for fees
1957 l_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
1958 l_memo_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
1959 l_orig_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
1960 l_memo_fees_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
1961 l_orig_fees_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
1962 l_fees_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
1963 l_fee_basis_tbl LNS_FEE_ENGINE.FEE_BASIS_TBL;
1964 l_fee_amount number;
1965 l_other_amount number;
1966 l_manual_fee_amount number;
1967
1968 /*-----------------------------------------------------------------------+
1969 | Cursor Declarations |
1970 +-----------------------------------------------------------------------*/
1971
1972 -- total fees on the schedule by installment
1973 cursor c_fees(p_loan_id number, p_installment number) is
1974 select nvl(sum(sched.fee_amount), 0)
1975 from lns_fee_schedules sched
1976 ,lns_fees struct
1977 where sched.loan_id = p_loan_id
1978 and sched.fee_id = struct.fee_id
1979 and fee_installment = p_installment
1980 and active_flag = 'Y';
1981
1982 -- manual fees
1983 cursor c_manual_fees(p_loan_id number, p_installment number) is
1984 select sum(nvl(fee_amount,0))
1985 from lns_fee_schedules sch,
1986 lns_fees fees
1987 where sch.active_flag = 'Y'
1988 and sch.billed_flag = 'N'
1989 and fees.fee_id = sch.fee_id
1990 and ((fees.fee_category = 'MANUAL')
1991 OR (fees.fee_category = 'EVENT' AND fees.fee_type = 'EVENT_LATE_CHARGE'))
1992 and sch.loan_id = p_loan_id
1993 and fee_installment = p_installment;
1994
1995 -- get last bill date
1996 cursor c_get_last_bill_date(p_loan_id number, p_installment_number number) is
1997 select ACTIVITY_DATE
1998 from LNS_PRIN_TRX_ACTIVITIES_V
1999 where loan_id = p_loan_id
2000 and PAYMENT_NUMBER = p_installment_number
2001 and ACTIVITY_CODE in ('BILLING', 'START');
2002
2003 BEGIN
2004
2005 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
2006
2007 -- Standard start of API savepoint
2008 SAVEPOINT recalcCustomSchedule;
2009 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Savepoint is established');
2010
2011 -- Standard call to check for call compatibility
2012 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2013 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2014 END IF;
2015
2016 -- Initialize message list if p_init_msg_list is set to TRUE
2017 IF FND_API.To_Boolean(p_init_msg_list) THEN
2018 FND_MSG_PUB.initialize;
2019 END IF;
2020
2021 -- Initialize API return status to success
2022 l_return_status := FND_API.G_RET_STS_SUCCESS;
2023
2024 -- START OF BODY OF API
2025
2026 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
2027 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID: ' || P_LOAN_ID);
2028 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_BASED_ON_TERMS: ' || P_BASED_ON_TERMS);
2029 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_AMORT_METHOD: ' || P_AMORT_METHOD);
2030
2031 if P_LOAN_ID is null then
2032 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2033 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
2034 FND_MESSAGE.SET_TOKEN('VALUE', P_LOAN_ID);
2035 FND_MSG_PUB.ADD;
2036 RAISE FND_API.G_EXC_ERROR;
2037 end if;
2038
2039 if P_BASED_ON_TERMS is null then
2040 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2041 FND_MESSAGE.SET_TOKEN('PARAMETER', 'BASED_ON_TERMS');
2042 FND_MESSAGE.SET_TOKEN('VALUE', P_BASED_ON_TERMS);
2043 FND_MSG_PUB.ADD;
2044 RAISE FND_API.G_EXC_ERROR;
2045 end if;
2046
2047 if P_AMORT_METHOD is null then
2048 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2049 FND_MESSAGE.SET_TOKEN('PARAMETER', 'AMORT_METHOD');
2050 FND_MESSAGE.SET_TOKEN('VALUE', P_AMORT_METHOD);
2051 FND_MSG_PUB.ADD;
2052 RAISE FND_API.G_EXC_ERROR;
2053 end if;
2054
2055 l_CUSTOM_TBL := P_CUSTOM_TBL;
2056
2057 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input Schedule:');
2058 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PN DD RATE BB UP UI PAY PRIN LP INT LI EB ACT');
2059 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, '--- -------- ---- ------ ------ ------ ------ ------ -- ------ -- ------ ---');
2060 for i in 1..l_custom_tbl.count loop
2061
2062 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME,
2063 l_custom_tbl(i).PAYMENT_NUMBER || ' ' ||
2064 l_custom_tbl(i).DUE_DATE || ' ' ||
2065 l_custom_tbl(i).INTEREST_RATE || ' ' ||
2066 l_custom_tbl(i).INSTALLMENT_BEGIN_BALANCE || ' ' ||
2067 l_custom_tbl(i).UNPAID_PRIN || ' ' ||
2068 l_custom_tbl(i).UNPAID_INT || ' ' ||
2069 l_custom_tbl(i).CURRENT_TERM_PAYMENT || ' ' ||
2070 l_custom_tbl(i).PRINCIPAL_AMOUNT || ' ' ||
2071 l_custom_tbl(i).LOCK_PRIN || ' ' ||
2072 l_custom_tbl(i).INTEREST_AMOUNT || ' ' ||
2073 l_custom_tbl(i).LOCK_INT || ' ' ||
2074 l_custom_tbl(i).INSTALLMENT_END_BALANCE || ' ' ||
2075 l_custom_tbl(i).ACTION);
2076 end loop;
2077
2078 filterCustSchedule(l_custom_tbl);
2079 if l_custom_tbl.count = 0 then
2080
2081 -- fix for bug 7217204
2082 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Custom schedule is empty. Returning.');
2083 return;
2084 /*
2085 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Custom amortization is empty.');
2086 FND_MESSAGE.SET_NAME('LNS', 'LNS_CUST_AMORT_EMPTY');
2087 FND_MSG_PUB.Add;
2088 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2089 RAISE FND_API.G_EXC_ERROR;
2090 */
2091 end if;
2092
2093 l_loan_details := getLoanDetails(p_loan_Id => p_loan_id
2094 ,p_based_on_terms => p_based_on_terms);
2095 sortRowsByDate(l_custom_tbl);
2096
2097 l_remaining_balance_theory := l_loan_details.remaining_balance;
2098 l_remaining_balance_theory1 := l_loan_details.remaining_balance;
2099 l_remaining_balance_actual := l_loan_details.remaining_balance;
2100 l_last_installment_billed := l_loan_details.last_installment_billed;
2101 l_calc_method := l_loan_details.CALCULATION_METHOD;
2102 l_day_count_method := l_loan_details.day_count_method;
2103 l_unbilled_principal := l_loan_details.unbilled_principal;
2104 l_unpaid_amount := l_loan_details.unpaid_principal + l_loan_details.UNPAID_INTEREST;
2105 l_compound_freq := l_loan_details.INTEREST_COMPOUNDING_FREQ;
2106 l_payment_freq := l_loan_details.PAYMENT_FREQUENCY;
2107 --l_pay_in_arrears := l_loan_details.pay_in_arrears_boolean;
2108 l_precision := l_loan_details.currency_precision;
2109 l_previous_annualized := -1;
2110 l_unpaid_principal := l_loan_details.unpaid_principal;
2111 l_unpaid_interest := l_loan_details.UNPAID_INTEREST;
2112 /*
2113 if l_last_installment_billed is null then
2114 l_last_installment_billed := 0;
2115 end if;
2116 */
2117 if P_BASED_ON_TERMS = 'CURRENT' then
2118 if (l_calc_method = 'SIMPLE') then
2119 l_remaining_balance_theory1 := l_remaining_balance_actual;
2120 elsif (l_calc_method = 'COMPOUND') then
2121 l_remaining_balance_theory1 := l_remaining_balance_actual + l_unpaid_interest;
2122 end if;
2123 l_remaining_balance_theory := l_remaining_balance_actual - l_unpaid_principal;
2124 end if;
2125
2126 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_remaining_balance_theory: ' || l_remaining_balance_theory);
2127 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_remaining_balance_theory1: ' || l_remaining_balance_theory1);
2128
2129 l_num_unlocked_prin := 0;
2130 l_locked_prin := 0;
2131 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Sorted table:');
2132 for i in 1..l_custom_tbl.count loop
2133
2134 if l_custom_tbl(i).DUE_DATE < l_loan_details.loan_start_date then
2135 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Installment due date cannot be earlier then loan start date.');
2136 FND_MESSAGE.SET_NAME('LNS', 'LNS_EARLIER_LN_START_DATE');
2137 FND_MSG_PUB.Add;
2138 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2139 RAISE FND_API.G_EXC_ERROR;
2140 end if;
2141
2142 if l_loan_details.LAST_DUE_DATE is not null then
2143 if l_custom_tbl(i).DUE_DATE <= l_loan_details.LAST_DUE_DATE then
2144 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Installment due date cannot be earlier or equal to due date of the last billed installment.');
2145 FND_MESSAGE.SET_NAME('LNS', 'LNS_EARLIER_LAST_BILLED_DD');
2146 FND_MSG_PUB.Add;
2147 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2148 RAISE FND_API.G_EXC_ERROR;
2149 end if;
2150 end if;
2151
2152 if l_custom_tbl(i).DUE_DATE > l_loan_details.maturity_Date then
2153
2154 if i = l_custom_tbl.count then -- fix for bug 6920780: if its last installment and due date is beyond maturity date - set it to maturity date
2155 l_custom_tbl(i).DUE_DATE := l_loan_details.maturity_Date;
2156 else
2157 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Installment due date cannot be later then loan maturity date.');
2158 FND_MESSAGE.SET_NAME('LNS', 'LNS_LATER_LN_MATUR_DATE');
2159 FND_MSG_PUB.Add;
2160 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2161 RAISE FND_API.G_EXC_ERROR;
2162 end if;
2163
2164 end if;
2165
2166 l_custom_tbl(i).PAYMENT_NUMBER := l_last_installment_billed + i;
2167
2168 -- fix for bug 7026226: default PRINCIPAL_AMOUNT if its null
2169 if l_custom_tbl(i).PRINCIPAL_AMOUNT is null then
2170 l_custom_tbl(i).PRINCIPAL_AMOUNT := 0;
2171 end if;
2172
2173 -- fix for bug 7026226: default INTEREST_AMOUNT if its null
2174 if l_custom_tbl(i).INTEREST_AMOUNT is null then
2175 l_custom_tbl(i).INTEREST_AMOUNT := 0;
2176 end if;
2177
2178 -- default LOCK_PRIN
2179 if l_custom_tbl(i).LOCK_PRIN is null then
2180 l_custom_tbl(i).LOCK_PRIN := 'Y';
2181 elsif i = l_custom_tbl.count then
2182 l_custom_tbl(i).LOCK_PRIN := 'N';
2183 end if;
2184
2185 -- default LOCK_INT
2186 if l_custom_tbl(i).LOCK_INT is null then
2187 l_custom_tbl(i).LOCK_INT := 'Y';
2188 --elsif i = l_custom_tbl.count then
2189 -- l_custom_tbl(i).LOCK_INT := 'N';
2190 end if;
2191
2192 -- count number of unlocked principals and sum of locked principals
2193 if l_custom_tbl(i).LOCK_PRIN = 'N' then
2194 l_num_unlocked_prin := l_num_unlocked_prin + 1;
2195 elsif l_custom_tbl(i).LOCK_PRIN = 'Y' then
2196 l_locked_prin := l_locked_prin + l_custom_tbl(i).PRINCIPAL_AMOUNT;
2197 end if;
2198
2199 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_custom_tbl(' || i || ').DUE_DATE: ' || l_custom_tbl(i).DUE_DATE);
2200
2201 end loop;
2202
2203 if l_locked_prin > l_remaining_balance_theory then
2204 l_locked_prin := l_remaining_balance_theory;
2205 end if;
2206
2207 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Number of unlocked principals: ' || l_num_unlocked_prin);
2208 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Sum of locked principals: ' || l_locked_prin);
2209
2210 -- get rate schedule
2211 l_rate_tbl := lns_financials.getRateSchedule(p_loan_id, 'TERM');
2212
2213 -- synch rate schedule with current custom schedule
2214 l_num_installments := l_custom_tbl(l_custom_tbl.count).PAYMENT_NUMBER;
2215 synchRateSchedule(l_rate_tbl, l_num_installments);
2216
2217 --getting fees
2218 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'getting fee structures');
2219 l_orig_fee_structures := lns_fee_engine.getFeeStructures(p_loan_id => p_loan_id
2220 ,p_fee_category => 'EVENT'
2221 ,p_fee_type => 'EVENT_ORIGINATION'
2222 ,p_installment => null
2223 ,p_fee_id => null);
2224
2225 l_fee_structures := lns_fee_engine.getFeeStructures(p_loan_id => p_loan_id
2226 ,p_fee_category => 'RECUR'
2227 ,p_fee_type => null
2228 ,p_installment => null
2229 ,p_fee_id => null);
2230
2231 l_memo_fee_structures := lns_fee_engine.getFeeStructures(p_loan_id => p_loan_id
2232 ,p_fee_category => 'MEMO'
2233 ,p_fee_type => null
2234 ,p_installment => null
2235 ,p_fee_id => null);
2236
2237 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'fee structures count is ' || l_fee_structures.count);
2238 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'orig structures count is ' || l_orig_fee_structures.count);
2239 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'memo fee structures count is ' || l_memo_fee_structures.count);
2240
2241 for i in 1..l_custom_tbl.count loop
2242
2243 --i := i + 1;
2244 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' ');
2245 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Row ' || i);
2246
2247 -- get start and end dates
2248 if l_custom_tbl(i).PAYMENT_NUMBER = (l_last_installment_billed + 1) then
2249 if l_custom_tbl(i).PAYMENT_NUMBER = 0 or l_custom_tbl(i).PAYMENT_NUMBER = 1 then
2250 l_period_begin_date := l_loan_details.LOAN_START_DATE;
2251 else
2252 l_period_begin_date := l_loan_details.LAST_DUE_DATE;
2253 end if;
2254 else
2255 l_period_begin_date := l_custom_tbl(i-1).DUE_DATE;
2256 end if;
2257 l_period_end_date := l_custom_tbl(i).DUE_DATE;
2258 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Period: ' || l_period_begin_date || ' - ' || l_period_end_date);
2259
2260 l_custom_tbl(i).PERIOD_START_DATE := l_period_begin_date;
2261 l_custom_tbl(i).PERIOD_END_DATE := l_period_end_date;
2262
2263 -- get rate
2264 l_rate_details := lns_financials.getRateDetails(p_installment => l_custom_tbl(i).PAYMENT_NUMBER
2265 ,p_rate_tbl => l_rate_tbl);
2266 if l_loan_details.rate_type = 'FIXED' OR (l_loan_details.rate_type = 'VARIABLE' and l_rate_details.floating_flag = 'N') then
2267
2268 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'FIXED OR NOT FLOATING ');
2269 l_annualized_rate := l_rate_details.annual_rate;
2270
2271 elsif l_loan_details.rate_type = 'VARIABLE' and l_rate_details.floating_flag = 'Y' then
2272
2273 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'FLOATING ');
2274 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_loan_details.LAST_INTEREST_RATE ' || l_loan_details.LAST_INTEREST_RATE);
2275 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_loan_details.NEXT_RATE_CHANGE_DATE ' || l_loan_details.NEXT_RATE_CHANGE_DATE);
2276 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_loan_details.TERM_INDEX_RATE_ID ' || l_loan_details.TERM_INDEX_RATE_ID);
2277 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_loan_details.INITIAL_INTEREST_RATE ' || l_loan_details.INITIAL_INTEREST_RATE);
2278 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_loan_details.LAST_INTEREST_RATE ' || l_loan_details.LAST_INTEREST_RATE);
2279 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_loan_details.TERM_FIRST_PERCENT_INCREASE ' || l_loan_details.TERM_FIRST_PERCENT_INCREASE);
2280 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_loan_details.TERM_ADJ_PERCENT_INCREASE ' || l_loan_details.TERM_ADJ_PERCENT_INCREASE);
2281 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_loan_details.TERM_LIFE_PERCENT_INCREASE ' || l_loan_details.TERM_LIFE_PERCENT_INCREASE);
2282 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_loan_details.TERM_CEILING_RATE ' || l_loan_details.TERM_CEILING_RATE);
2283 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_loan_details.TERM_FLOOR_RATE ' || l_loan_details.TERM_FLOOR_RATE);
2284 -- if float and no rate then error;
2285 -- we only need to get the new rate if
2286 -- 1. no rate has ever been calculated (i.e. 1st installment)
2287 -- 2. the billing date is beyond the next_rate_change_date
2288 -- 3. make sure to add spread and abide by rate rules
2289 if l_period_begin_date < l_loan_details.NEXT_RATE_CHANGE_DATE and l_custom_tbl(i).PAYMENT_NUMBER > 1 then
2290
2291 -- no need to recalculate interest
2292 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'FLOATING NO RECALC');
2293 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_details.last_interest_rate:' || l_loan_details.last_interest_rate);
2294 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'spread:' || l_rate_details.spread);
2295 l_annualized_rate := l_loan_details.last_interest_rate + l_rate_details.spread;
2296
2297 --elsif l_custom_tbl(i).PAYMENT_NUMBER = 1 OR l_period_begin_date > l_loan_details.NEXT_RATE_CHANGE_DATE then
2298 else
2299
2300 if P_BASED_ON_TERMS = 'CURRENT' then
2301
2302 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': FLOATING RECALCULATE');
2303
2304 l_rate_details.ANNUAL_RATE := lns_fin_utils.getRateForDate(l_loan_details.TERM_INDEX_RATE_ID
2305 ,l_period_begin_date);
2306
2307 -- raise error as rates does not exist
2308 if l_rate_details.ANNUAL_RATE is null then
2309 FND_MESSAGE.SET_NAME('LNS', 'LNS_RATES_ERROR');
2310 FND_MSG_PUB.ADD;
2311 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': rates missing for INDEX_ID ' || l_loan_details.TERM_INDEX_RATE_ID);
2312 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': rates missing for DATE ' || l_loan_details.NEXT_RATE_CHANGE_DATE);
2313 RAISE FND_API.G_EXC_ERROR;
2314 end if;
2315
2316 -- add the spread into the rate
2317 l_raw_rate := l_rate_details.ANNUAL_RATE + l_rate_details.spread;
2318 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_raw_rate ' || l_raw_rate);
2319
2320 -- adjust the rate as per rate rules
2321 l_annualized_rate := lns_financials.calculateInterestRate(
2322 p_initial_rate => l_loan_details.INITIAL_INTEREST_RATE
2323 ,p_rate_to_compare => l_raw_rate
2324 ,p_last_period_rate => l_loan_details.LAST_INTEREST_RATE
2325 ,p_max_first_adjustment => l_loan_details.TERM_FIRST_PERCENT_INCREASE
2326 ,p_max_period_adjustment => l_loan_details.TERM_ADJ_PERCENT_INCREASE
2327 ,p_max_lifetime_adjustment => l_loan_details.TERM_LIFE_PERCENT_INCREASE
2328 ,p_ceiling_rate => l_loan_details.TERM_CEILING_RATE
2329 ,p_floor_rate => l_loan_details.TERM_FLOOR_RATE
2330 ,p_installment_number => l_custom_tbl(i).PAYMENT_NUMBER);
2331
2332 else -- origination
2333
2334 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': ASSIGNING PROJECTED RATE ');
2335 l_annualized_rate := l_loan_details.TERM_PROJECTED_INTEREST_RATE;
2336
2337 end if; -- P_BASED_ON_TERMS
2338
2339 end if; -- recalculate rate
2340
2341 end if; -- floating or not
2342
2343 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'annualized_rate: ' || l_annualized_rate);
2344 l_custom_tbl(i).INTEREST_RATE := l_annualized_rate;
2345
2346 l_norm_interest := 0;
2347 l_add_prin_interest := 0;
2348 l_add_int_interest := 0;
2349 l_penal_prin_interest := 0;
2350 l_penal_int_interest := 0;
2351 l_penal_interest := 0;
2352
2353 -- calc interest amount
2354 if l_custom_tbl(i).LOCK_INT = 'N' or l_loan_details.LOAN_STATUS = 'PAIDOFF' then
2355
2356 if l_custom_tbl(i).PAYMENT_NUMBER = (l_last_installment_billed + 1) then
2357
2358 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating normal interest...');
2359 l_norm_interest := LNS_FINANCIALS.CALC_NORM_INTEREST(p_loan_id => p_loan_id,
2360 p_calc_method => l_calc_method,
2361 p_period_start_date => l_period_begin_date,
2362 p_period_end_date => l_period_end_date,
2363 p_interest_rate => l_annualized_rate,
2364 p_day_count_method => l_day_count_method,
2365 p_payment_freq => l_payment_freq,
2366 p_compound_freq => l_compound_freq);
2367
2368 l_norm_interest := round(l_norm_interest, l_precision);
2369
2370 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || 'l_custom_tbl(i).PAYMENT_NUMBER-1: ' || (l_custom_tbl(i).PAYMENT_NUMBER-1));
2371 if (l_custom_tbl(i).PAYMENT_NUMBER)-1 >= 0 then
2372
2373 -- get additional interest start date
2374 open c_get_last_bill_date(p_loan_id, (l_custom_tbl(i).PAYMENT_NUMBER-1));
2375 fetch c_get_last_bill_date into l_add_start_date;
2376 close c_get_last_bill_date;
2377 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_add_start_date: ' || l_add_start_date);
2378
2379 -- get additional interest end date
2380 if trunc(sysdate) > trunc(l_period_end_date) then
2381 l_add_end_date := l_period_end_date;
2382 else
2383 l_add_end_date := sysdate;
2384 end if;
2385 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_add_end_date: ' || l_add_end_date);
2386
2387 if (l_custom_tbl(i).PAYMENT_NUMBER-1) > 0 then
2388 l_payment := getPayment(p_loan_id, (l_custom_tbl(i).PAYMENT_NUMBER-1)); -- fix for bug 7319358
2389 l_prev_grace_end_date := l_payment.PERIOD_BEGIN_DATE + l_loan_details.PENAL_INT_GRACE_DAYS;
2390 else
2391 l_prev_grace_end_date := l_period_begin_date;
2392 end if;
2393 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_prev_grace_end_date: ' || l_prev_grace_end_date);
2394
2395 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating additional interest on unpaid principal...');
2396 -- calculate additional interest on unpaid principal
2397 LNS_FINANCIALS.CALC_ADD_INTEREST(p_loan_id => p_loan_id,
2398 p_calc_method => l_calc_method,
2399 p_period_start_date => l_add_start_date,
2400 p_period_end_date => l_add_end_date,
2401 p_interest_rate => l_annualized_rate,
2402 p_day_count_method => l_day_count_method,
2403 p_payment_freq => l_payment_freq,
2404 p_compound_freq => l_compound_freq,
2405 p_penal_int_rate => l_loan_details.PENAL_INT_RATE,
2406 p_prev_grace_end_date => l_prev_grace_end_date,
2407 p_grace_start_date => l_period_begin_date,
2408 p_grace_end_date => (l_period_begin_date + l_loan_details.PENAL_INT_GRACE_DAYS),
2409 p_target => 'UNPAID_PRIN',
2410 x_add_interest => l_add_prin_interest,
2411 x_penal_interest => l_penal_prin_interest);
2412 l_add_prin_interest := round(l_add_prin_interest, l_precision);
2413
2414 if (l_calc_method = 'COMPOUND') then
2415
2416 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating additional interest on unpaid interest...');
2417 -- calculate additional interest on unpaid interest
2418 LNS_FINANCIALS.CALC_ADD_INTEREST(p_loan_id => p_loan_id,
2419 p_calc_method => l_calc_method,
2420 p_period_start_date => l_add_start_date,
2421 p_period_end_date => l_add_end_date,
2422 p_interest_rate => l_annualized_rate,
2423 p_day_count_method => l_day_count_method,
2424 p_payment_freq => l_payment_freq,
2425 p_compound_freq => l_compound_freq,
2426 p_penal_int_rate => l_loan_details.PENAL_INT_RATE,
2427 p_prev_grace_end_date => l_prev_grace_end_date,
2428 p_grace_start_date => l_period_begin_date,
2429 p_grace_end_date => (l_period_begin_date + l_loan_details.PENAL_INT_GRACE_DAYS),
2430 p_target => 'UNPAID_INT',
2431 x_add_interest => l_add_int_interest,
2432 x_penal_interest => l_penal_int_interest);
2433 l_add_int_interest := round(l_add_int_interest, l_precision);
2434
2435 end if;
2436
2437 end if;
2438
2439 else
2440
2441 if (l_calc_method = 'SIMPLE') then
2442
2443 -- recalculate periodic rate for each period if day counting methodolgy varies
2444 l_periodic_rate := lns_financials.getPeriodicRate(
2445 p_period_start_date => l_period_begin_date
2446 ,p_period_end_date => l_period_end_date
2447 ,p_annualized_rate => l_annualized_rate
2448 ,p_days_count_method => l_day_count_method);
2449
2450 elsif (l_calc_method = 'COMPOUND') then
2451
2452 l_periodic_rate := lns_financials.getCompoundPeriodicRate(p_compound_freq => l_compound_freq
2453 ,p_payment_freq => l_payment_freq
2454 ,p_annualized_rate => l_annualized_rate
2455 ,p_period_start_date => l_period_begin_date
2456 ,p_period_end_date => l_period_end_date
2457 ,p_days_count_method => l_day_count_method);
2458 end if;
2459
2460 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'periodic_rate: ' || l_periodic_rate);
2461 l_norm_interest := lns_financials.calculateInterest(p_amount => l_remaining_balance_theory1
2462 ,p_periodic_rate => l_periodic_rate
2463 ,p_compounding_period => null);
2464 l_norm_interest := round(l_norm_interest, l_precision);
2465 end if;
2466
2467 l_penal_interest := round(l_penal_prin_interest + l_penal_int_interest, l_precision);
2468 l_custom_tbl(i).INTEREST_AMOUNT := l_norm_interest + l_add_prin_interest + l_add_int_interest + l_penal_interest;
2469
2470 else
2471 l_norm_interest := round(l_custom_tbl(i).INTEREST_AMOUNT, l_precision);
2472 end if;
2473
2474 l_custom_tbl(i).INTEREST_AMOUNT := round(l_custom_tbl(i).INTEREST_AMOUNT, l_precision);
2475
2476 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_norm_interest = ' || l_norm_interest);
2477 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_add_prin_interest = ' || l_add_prin_interest);
2478 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_add_int_interest = ' || l_add_int_interest);
2479 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_penal_interest = ' || l_penal_interest);
2480 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': INTEREST_AMOUNT = ' || l_custom_tbl(i).INTEREST_AMOUNT);
2481
2482 -- based on amortization method calc prin or payment
2483 if P_AMORT_METHOD = 'EQUAL_PRINCIPAL' then
2484
2485 -- calc principal amount
2486 if i = 1 then
2487 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calculating periodic_principal...');
2488
2489 l_periodic_principal := lns_financials.calculateEPPayment(p_loan_amount => (l_remaining_balance_theory - l_locked_prin)
2490 ,p_num_intervals => l_num_unlocked_prin
2491 ,p_ending_balance=> 0
2492 ,p_pay_in_arrears=> true);
2493
2494 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'periodic_principal: ' || l_periodic_principal);
2495 end if;
2496
2497 if l_custom_tbl(i).LOCK_PRIN = 'N' then
2498 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT := periodic_principal');
2499 l_custom_tbl(i).PRINCIPAL_AMOUNT := l_periodic_principal;
2500 end if;
2501
2502 if l_remaining_balance_theory <= l_custom_tbl(i).PRINCIPAL_AMOUNT then
2503 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_remaining_balance_theory <= PRINCIPAL_AMOUNT');
2504 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT := remaining_balance_theory');
2505 l_custom_tbl(i).PRINCIPAL_AMOUNT := l_remaining_balance_theory;
2506 l_custom_tbl(i).LOCK_PRIN := 'N';
2507 else
2508 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'remaining_balance_theory > PRINCIPAL_AMOUNT');
2509 if i = l_custom_tbl.count then
2510 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Its the last row');
2511 if p_based_on_terms = 'CURRENT' and l_unbilled_principal > 0 then
2512 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT := unbilled_principal');
2513 l_custom_tbl(i).PRINCIPAL_AMOUNT := l_unbilled_principal;
2514 else
2515 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT := remaining_balance_theory');
2516 l_custom_tbl(i).PRINCIPAL_AMOUNT := l_remaining_balance_theory;
2517 end if;
2518 end if;
2519 end if;
2520
2521 l_custom_tbl(i).PRINCIPAL_AMOUNT := round(l_custom_tbl(i).PRINCIPAL_AMOUNT, l_precision);
2522 l_custom_tbl(i).CURRENT_TERM_PAYMENT := l_custom_tbl(i).PRINCIPAL_AMOUNT + l_custom_tbl(i).INTEREST_AMOUNT;
2523
2524 elsif P_AMORT_METHOD = 'EQUAL_PAYMENT' then
2525
2526 -- calc principal amount
2527 if (i = 1 or l_annualized_rate <> l_previous_annualized or
2528 (i > 1 and l_custom_tbl(i-1).LOCK_PRIN = 'Y' and l_custom_tbl(i).LOCK_PRIN = 'N'))
2529 then
2530 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calculating periodic_payment...');
2531
2532 if (l_calc_method = 'SIMPLE') then
2533
2534 l_rate_to_calculate := lns_fin_utils.convertRate(p_annualized_rate => l_annualized_rate
2535 ,p_amortization_frequency => l_payment_freq);
2536
2537 elsif (l_calc_method = 'COMPOUND') then
2538
2539 l_rate_to_calculate := lns_financials.getCompoundPeriodicRate(p_compound_freq => l_compound_freq
2540 ,p_payment_freq => l_payment_freq
2541 ,p_annualized_rate => l_annualized_rate
2542 ,p_period_start_date => null
2543 ,p_period_end_date => null
2544 ,p_days_count_method => l_day_count_method);
2545
2546 end if;
2547
2548 l_amortization_intervals := l_custom_tbl.count + 1 - i;
2549 l_periodic_payment := lns_financials.calculatePayment(p_loan_amount => l_remaining_balance_theory
2550 ,p_periodic_rate => l_rate_to_calculate
2551 ,p_num_intervals => l_amortization_intervals
2552 ,p_ending_balance=> 0
2553 ,p_pay_in_arrears=> true);
2554
2555
2556 l_periodic_payment := round(l_periodic_payment, l_precision);
2557 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'periodic_payment: ' || l_periodic_payment);
2558 end if;
2559
2560 if l_custom_tbl(i).LOCK_PRIN = 'N' then
2561
2562 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'CURRENT_TERM_PAYMENT := l_periodic_payment');
2563 l_custom_tbl(i).CURRENT_TERM_PAYMENT := l_periodic_payment;
2564 l_custom_tbl(i).PRINCIPAL_AMOUNT := round(l_custom_tbl(i).CURRENT_TERM_PAYMENT - l_custom_tbl(i).INTEREST_AMOUNT, l_precision);
2565
2566 else
2567
2568 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'CURRENT_TERM_PAYMENT := PRINCIPAL_AMOUNT+INTEREST_AMOUNT');
2569 l_custom_tbl(i).CURRENT_TERM_PAYMENT := l_custom_tbl(i).PRINCIPAL_AMOUNT + l_custom_tbl(i).INTEREST_AMOUNT;
2570
2571 end if;
2572
2573 if l_remaining_balance_theory <= l_custom_tbl(i).PRINCIPAL_AMOUNT then
2574 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_remaining_balance_theory <= PRINCIPAL_AMOUNT');
2575 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT := remaining_balance_theory');
2576 l_custom_tbl(i).PRINCIPAL_AMOUNT := l_remaining_balance_theory;
2577 l_custom_tbl(i).CURRENT_TERM_PAYMENT := l_custom_tbl(i).PRINCIPAL_AMOUNT + l_custom_tbl(i).INTEREST_AMOUNT;
2578 l_custom_tbl(i).LOCK_PRIN := 'N';
2579 else
2580 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'remaining_balance_theory > PRINCIPAL_AMOUNT');
2581 if i = l_custom_tbl.count then
2582 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Its the last row');
2583 if p_based_on_terms = 'CURRENT' and l_unbilled_principal > 0 then
2584 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT := unbilled_principal');
2585 l_custom_tbl(i).PRINCIPAL_AMOUNT := l_unbilled_principal;
2586 else
2587 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT := remaining_balance_theory');
2588 l_custom_tbl(i).PRINCIPAL_AMOUNT := l_remaining_balance_theory;
2589 end if;
2590 l_custom_tbl(i).CURRENT_TERM_PAYMENT := l_custom_tbl(i).PRINCIPAL_AMOUNT + l_custom_tbl(i).INTEREST_AMOUNT;
2591 end if;
2592 end if;
2593
2594 elsif P_AMORT_METHOD = 'NONE' then
2595
2596 if l_remaining_balance_theory <= l_custom_tbl(i).PRINCIPAL_AMOUNT then
2597 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_remaining_balance_theory <= PRINCIPAL_AMOUNT');
2598 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT := remaining_balance_theory');
2599 l_custom_tbl(i).PRINCIPAL_AMOUNT := l_remaining_balance_theory;
2600 l_custom_tbl(i).LOCK_PRIN := 'N';
2601 else
2602 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'remaining_balance_theory > PRINCIPAL_AMOUNT');
2603 if i = l_custom_tbl.count then
2604 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Its the last row');
2605 if p_based_on_terms = 'CURRENT' and l_unbilled_principal > 0 then
2606 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT := unbilled_principal');
2607 l_custom_tbl(i).PRINCIPAL_AMOUNT := l_unbilled_principal;
2608 else
2609 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT := remaining_balance_theory');
2610 l_custom_tbl(i).PRINCIPAL_AMOUNT := l_remaining_balance_theory;
2611 end if;
2612 end if;
2613 end if;
2614
2615 l_custom_tbl(i).PRINCIPAL_AMOUNT := round(l_custom_tbl(i).PRINCIPAL_AMOUNT, l_precision);
2616 l_custom_tbl(i).CURRENT_TERM_PAYMENT := l_custom_tbl(i).PRINCIPAL_AMOUNT + l_custom_tbl(i).INTEREST_AMOUNT;
2617
2618 end if;
2619
2620 -- calculating fees
2621 l_fee_basis_tbl(1).fee_basis_name := 'TOTAL_BAL';
2622 l_fee_basis_tbl(1).fee_basis_amount := l_remaining_balance_theory + l_unpaid_principal;
2623 l_fee_basis_tbl(2).fee_basis_name := 'ORIG_LOAN';
2624 l_fee_basis_tbl(2).fee_basis_amount := l_loan_details.FUNDED_AMOUNT;
2625
2626 if l_custom_tbl(i).PAYMENT_NUMBER = 1 then
2627 if l_orig_fee_structures.count > 0 then
2628 lns_fee_engine.calculateFees(p_loan_id => p_loan_id
2629 ,p_installment => l_custom_tbl(i).PAYMENT_NUMBER
2630 ,p_fee_basis_tbl => l_fee_basis_tbl
2631 ,p_fee_structures => l_orig_fee_structures
2632 ,x_fees_tbl => l_orig_fees_tbl
2633 ,x_return_status => l_return_status
2634 ,x_msg_count => l_msg_count
2635 ,x_msg_data => l_msg_data);
2636 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'calculated origination fees ' || l_orig_fee_structures.count);
2637 end if;
2638 end if;
2639
2640 -- calculate the memo fees
2641 if l_memo_fee_structures.count > 0 then
2642 lns_fee_engine.calculateFees(p_loan_id => p_loan_id
2643 ,p_installment => l_custom_tbl(i).PAYMENT_NUMBER
2644 ,p_fee_basis_tbl => l_fee_basis_tbl
2645 ,p_fee_structures => l_memo_fee_structures
2646 ,x_fees_tbl => l_memo_fees_tbl
2647 ,x_return_status => l_return_status
2648 ,x_msg_count => l_msg_count
2649 ,x_msg_data => l_msg_data);
2650 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'calculated memo fees ' || l_memo_fees_tbl.count);
2651
2652 end if;
2653
2654 if l_fee_structures.count > 0 then
2655 lns_fee_engine.calculateFees(p_loan_id => p_loan_id
2656 ,p_installment => l_custom_tbl(i).PAYMENT_NUMBER
2657 ,p_fee_basis_tbl => l_fee_basis_tbl
2658 ,p_fee_structures => l_fee_structures
2659 ,x_fees_tbl => l_fees_tbl
2660 ,x_return_status => l_return_status
2661 ,x_msg_count => l_msg_count
2662 ,x_msg_data => l_msg_data);
2663 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'calculated fees ' || l_fees_tbl.count);
2664
2665 end if;
2666
2667 l_fee_amount := 0;
2668 l_other_amount := 0;
2669 l_manual_fee_amount := 0;
2670
2671 for j in 1..l_orig_fees_tbl.count loop
2672 l_fee_amount := l_fee_amount + l_orig_fees_tbl(j).FEE_AMOUNT;
2673 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'orig calculated fees ' || l_fee_amount);
2674 end loop;
2675
2676 for j in 1..l_fees_tbl.count loop
2677 l_fee_amount := l_fee_amount + l_fees_tbl(j).FEE_AMOUNT;
2678 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'recurring calculated fees ' || l_fee_amount);
2679 end loop;
2680
2681 for j in 1..l_memo_fees_tbl.count loop
2682 l_other_amount := l_other_amount + l_memo_fees_tbl(j).FEE_AMOUNT;
2683 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'other calculated fees ' || l_other_amount);
2684 end loop;
2685
2686 -- get the manual fees
2687 open c_manual_fees(p_loan_id, l_custom_tbl(i).PAYMENT_NUMBER);
2688 fetch c_manual_fees into l_manual_fee_amount;
2689 close c_manual_fees;
2690
2691 if l_manual_fee_amount is null then
2692 l_manual_fee_amount := 0;
2693 end if;
2694 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'manual fee amount is '|| l_manual_fee_amount);
2695 l_fee_amount := l_fee_amount + l_manual_fee_amount;
2696
2697 l_custom_tbl(i).FEE_AMOUNT := l_fee_amount;
2698 l_custom_tbl(i).other_amount := l_other_amount;
2699
2700 l_custom_tbl(i).CURRENT_TERM_PAYMENT := l_custom_tbl(i).CURRENT_TERM_PAYMENT + l_custom_tbl(i).FEE_AMOUNT + l_custom_tbl(i).other_amount;
2701 l_custom_tbl(i).UNPAID_PRIN := l_unpaid_principal;
2702 l_custom_tbl(i).UNPAID_INT := l_unpaid_interest;
2703 l_custom_tbl(i).INSTALLMENT_BEGIN_BALANCE := l_remaining_balance_theory;
2704 l_custom_tbl(i).INSTALLMENT_END_BALANCE := l_remaining_balance_theory - l_custom_tbl(i).PRINCIPAL_AMOUNT;
2705 l_custom_tbl(i).NORMAL_INT_AMOUNT := l_norm_interest;
2706 l_custom_tbl(i).ADD_PRIN_INT_AMOUNT := l_add_prin_interest;
2707 l_custom_tbl(i).ADD_INT_INT_AMOUNT := l_add_int_interest;
2708 l_custom_tbl(i).PENAL_INT_AMOUNT := l_penal_interest;
2709
2710 if l_unbilled_principal > 0 then
2711 l_unbilled_principal := l_unbilled_principal - l_custom_tbl(i).PRINCIPAL_AMOUNT;
2712 end if;
2713
2714 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT: ' || l_custom_tbl(i).PRINCIPAL_AMOUNT);
2715 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'INTEREST_AMOUNT: ' || l_custom_tbl(i).INTEREST_AMOUNT);
2716 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'FEE_AMOUNT: ' || l_custom_tbl(i).FEE_AMOUNT);
2717 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'CURRENT_TERM_PAYMENT: ' || l_custom_tbl(i).CURRENT_TERM_PAYMENT);
2718 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'INSTALLMENT_BEGIN_BALANCE: ' || l_custom_tbl(i).INSTALLMENT_BEGIN_BALANCE);
2719 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'INSTALLMENT_END_BALANCE: ' || l_custom_tbl(i).INSTALLMENT_END_BALANCE);
2720 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'remaining_balance_theory: ' || l_remaining_balance_theory);
2721 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'remaining_balance_theory1: ' || l_remaining_balance_theory1);
2722 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'unbilled_principal: ' || l_unbilled_principal);
2723 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'UNPAID_PRIN: ' || l_custom_tbl(i).UNPAID_PRIN);
2724 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'UNPAID_INT: ' || l_custom_tbl(i).UNPAID_INT);
2725
2726 l_remaining_balance_theory := l_remaining_balance_theory - l_custom_tbl(i).PRINCIPAL_AMOUNT;
2727 if (l_last_installment_billed >= 0) and (l_last_installment_billed + 1 = l_custom_tbl(i).PAYMENT_NUMBER)
2728 and p_based_on_terms = 'CURRENT' then
2729 l_remaining_balance_theory1 := l_remaining_balance_theory;
2730 else
2731 l_remaining_balance_theory1 := l_remaining_balance_theory1 - l_custom_tbl(i).PRINCIPAL_AMOUNT;
2732 end if;
2733
2734 l_previous_annualized := l_annualized_rate;
2735 l_unpaid_principal := 0;
2736 l_unpaid_interest := 0;
2737 l_orig_fees_tbl.delete;
2738 l_memo_fees_tbl.delete;
2739 l_fees_tbl.delete;
2740
2741 end loop;
2742
2743
2744 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Output Schedule:');
2745 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PN DD RATE BB UP UI PAY PRIN LP INT LI EB ACT');
2746 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, '--- -------- ---- ------ ------ ------ ------ ------ -- ------ -- ------ ---');
2747 for i in 1..l_custom_tbl.count loop
2748 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME,
2749 l_custom_tbl(i).PAYMENT_NUMBER || ' ' ||
2750 l_custom_tbl(i).DUE_DATE || ' ' ||
2751 l_custom_tbl(i).INTEREST_RATE || ' ' ||
2752 l_custom_tbl(i).INSTALLMENT_BEGIN_BALANCE || ' ' ||
2753 l_custom_tbl(i).UNPAID_PRIN || ' ' ||
2754 l_custom_tbl(i).UNPAID_INT || ' ' ||
2755 l_custom_tbl(i).CURRENT_TERM_PAYMENT || ' ' ||
2756 l_custom_tbl(i).PRINCIPAL_AMOUNT || ' ' ||
2757 l_custom_tbl(i).LOCK_PRIN || ' ' ||
2758 l_custom_tbl(i).INTEREST_RATE || '=' || l_custom_tbl(i).NORMAL_INT_AMOUNT || '+' || l_custom_tbl(i).ADD_PRIN_INT_AMOUNT || '+' || l_custom_tbl(i).ADD_INT_INT_AMOUNT || '+' || l_custom_tbl(i).PENAL_INT_AMOUNT || ' ' ||
2759 l_custom_tbl(i).LOCK_INT || ' ' ||
2760 l_custom_tbl(i).INSTALLMENT_END_BALANCE || ' ' ||
2761 l_custom_tbl(i).ACTION);
2762 end loop;
2763
2764 P_CUSTOM_TBL := l_CUSTOM_TBL;
2765
2766 if P_COMMIT = FND_API.G_TRUE then
2767 COMMIT WORK;
2768 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
2769 end if;
2770
2771 -- END OF BODY OF API
2772 x_return_status := FND_API.G_RET_STS_SUCCESS;
2773
2774 -- Standard call to get message count and if count is 1, get message info
2775 FND_MSG_PUB.Count_And_Get(
2776 p_encoded => FND_API.G_FALSE,
2777 p_count => x_msg_count,
2778 p_data => x_msg_data);
2779
2780 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
2781
2782 EXCEPTION
2783 WHEN FND_API.G_EXC_ERROR THEN
2784 ROLLBACK TO recalcCustomSchedule;
2785 x_return_status := FND_API.G_RET_STS_ERROR;
2786 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2787 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
2788 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2789 ROLLBACK TO recalcCustomSchedule;
2790 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2791 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2792 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
2793 WHEN OTHERS THEN
2794 ROLLBACK TO recalcCustomSchedule;
2795 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2796 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
2797 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2798 END IF;
2799 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2800 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
2801 END;
2802
2803
2804
2805 -- This procedure saves custom schedule into db
2806 procedure saveCustomSchedule(
2807 P_API_VERSION IN NUMBER,
2808 P_INIT_MSG_LIST IN VARCHAR2,
2809 P_COMMIT IN VARCHAR2,
2810 P_VALIDATION_LEVEL IN NUMBER,
2811 P_LOAN_ID IN NUMBER,
2812 P_AMORT_METHOD IN VARCHAR2,
2813 P_BASED_ON_TERMS IN VARCHAR2,
2814 P_CUSTOM_TBL IN OUT NOCOPY LNS_CUSTOM_PUB.CUSTOM_TBL,
2815 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2816 X_MSG_COUNT OUT NOCOPY NUMBER,
2817 X_MSG_DATA OUT NOCOPY VARCHAR2)
2818 IS
2819
2820 /*-----------------------------------------------------------------------+
2821 | Local Variable Declarations and initializations |
2822 +-----------------------------------------------------------------------*/
2823
2824 l_api_name CONSTANT VARCHAR2(30) := 'saveCustomSchedule';
2825 l_api_version CONSTANT NUMBER := 1.0;
2826 l_return_status VARCHAR2(1);
2827 l_msg_count NUMBER;
2828 l_msg_data VARCHAR2(32767);
2829
2830 l_loan_details LNS_CUSTOM_PUB.LOAN_DETAILS_REC;
2831 l_CUSTOM_TBL LNS_CUSTOM_PUB.CUSTOM_TBL;
2832 l_custom_rec LNS_CUSTOM_PUB.custom_sched_type;
2833 l_loan_header_rec LNS_LOAN_HEADER_PUB.LOAN_HEADER_REC_TYPE;
2834 l_term_rec LNS_TERMS_PUB.loan_term_rec_type;
2835
2836 l_custom_sched_id number;
2837 l_object_version number;
2838 l_term_id number;
2839 l_num_installments number;
2840 i number;
2841
2842 /*-----------------------------------------------------------------------+
2843 | Cursor Declarations |
2844 +-----------------------------------------------------------------------*/
2845
2846 BEGIN
2847
2848 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
2849
2850 -- Standard start of API savepoint
2851 SAVEPOINT saveCustomSchedule;
2852 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Savepoint is established');
2853
2854 -- Standard call to check for call compatibility
2855 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2856 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2857 END IF;
2858
2859 -- Initialize message list if p_init_msg_list is set to TRUE
2860 IF FND_API.To_Boolean(p_init_msg_list) THEN
2861 FND_MSG_PUB.initialize;
2862 END IF;
2863
2864 -- Initialize API return status to success
2865 l_return_status := FND_API.G_RET_STS_SUCCESS;
2866
2867 -- START OF BODY OF API
2868
2869 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
2870 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID: ' || P_LOAN_ID);
2871 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_AMORT_METHOD: ' || P_AMORT_METHOD);
2872 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_BASED_ON_TERMS: ' || P_BASED_ON_TERMS);
2873
2874 if P_LOAN_ID is null then
2875 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2876 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
2877 FND_MESSAGE.SET_TOKEN('VALUE', P_LOAN_ID);
2878 FND_MSG_PUB.ADD;
2879 RAISE FND_API.G_EXC_ERROR;
2880 end if;
2881
2882 if P_BASED_ON_TERMS is null then
2883 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2884 FND_MESSAGE.SET_TOKEN('PARAMETER', 'BASED_ON_TERMS');
2885 FND_MESSAGE.SET_TOKEN('VALUE', P_BASED_ON_TERMS);
2886 FND_MSG_PUB.ADD;
2887 RAISE FND_API.G_EXC_ERROR;
2888 end if;
2889
2890 if P_AMORT_METHOD is null then
2891 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2892 FND_MESSAGE.SET_TOKEN('PARAMETER', 'AMORT_METHOD');
2893 FND_MESSAGE.SET_TOKEN('VALUE', P_AMORT_METHOD);
2894 FND_MSG_PUB.ADD;
2895 RAISE FND_API.G_EXC_ERROR;
2896 end if;
2897
2898 l_CUSTOM_TBL := P_CUSTOM_TBL;
2899
2900 LNS_CUSTOM_PUB.recalcCustomSchedule(
2901 P_API_VERSION => 1.0,
2902 P_INIT_MSG_LIST => FND_API.G_TRUE,
2903 P_COMMIT => FND_API.G_FALSE,
2904 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
2905 P_LOAN_ID => P_LOAN_ID,
2906 P_AMORT_METHOD => P_AMORT_METHOD,
2907 P_BASED_ON_TERMS => P_BASED_ON_TERMS,
2908 P_CUSTOM_TBL => l_custom_tbl,
2909 x_return_status => l_return_status,
2910 x_msg_count => l_msg_count,
2911 x_msg_data => l_msg_data);
2912
2913 IF l_return_status <> 'S' THEN
2914 RAISE FND_API.G_EXC_ERROR;
2915 END IF;
2916
2917 l_loan_details := getLoanDetails(p_loan_Id => p_loan_id
2918 ,p_based_on_terms => P_BASED_ON_TERMS);
2919
2920 -- invalidate all existent rows in LNS_CUSTOM_PAYMNT_SCHEDS greater than l_loan_details.LAST_INSTALLMENT_BILLED
2921 update LNS_CUSTOM_PAYMNT_SCHEDS
2922 set PAYMENT_NUMBER = -1
2923 where loan_id = p_loan_id
2924 and PAYMENT_NUMBER > l_loan_details.LAST_INSTALLMENT_BILLED;
2925
2926 -- insert and update valid rows
2927 for i in 1..l_custom_tbl.count loop
2928
2929 l_custom_rec := l_custom_tbl(i);
2930
2931 if l_custom_tbl(i).ACTION is null or l_custom_tbl(i).ACTION = 'U' then
2932
2933 -- getting info from lns_loan_headers_all
2934 select object_version_number
2935 into l_custom_rec.OBJECT_VERSION_NUMBER
2936 from LNS_CUSTOM_PAYMNT_SCHEDS
2937 where loan_id = p_loan_id and
2938 CUSTOM_SCHEDULE_ID = l_custom_tbl(i).CUSTOM_SCHEDULE_ID;
2939
2940
2941 -- call api to update rows one-by-one for compliance reasons
2942 lns_custom_pub.updateCustomSched(P_CUSTOM_REC => l_custom_rec
2943 ,x_return_status => l_return_status
2944 ,x_msg_count => l_msg_Count
2945 ,x_msg_data => l_msg_Data);
2946
2947 IF l_return_status <> 'S' THEN
2948 RAISE FND_API.G_EXC_ERROR;
2949 END IF;
2950
2951 elsif l_custom_tbl(i).ACTION = 'I' then
2952
2953 -- call api to update rows one-by-one for compliance reasons
2954 lns_custom_pub.createCustomSched(P_CUSTOM_REC => l_custom_rec
2955 ,x_return_status => l_return_status
2956 ,x_custom_sched_id => l_custom_sched_id
2957 ,x_msg_count => l_msg_Count
2958 ,x_msg_data => l_msg_Data);
2959
2960 IF l_return_status <> 'S' THEN
2961 RAISE FND_API.G_EXC_ERROR;
2962 END IF;
2963
2964 l_custom_tbl(i).CUSTOM_SCHEDULE_ID := l_custom_sched_id;
2965
2966 end if;
2967
2968 -- fix for bug 7143022: set next payment due date to due date of the first to be billed custom installment and set BILLED_FLAG to 'N'
2969 if P_BASED_ON_TERMS = 'CURRENT' and i = 1 then
2970 l_term_rec.NEXT_PAYMENT_DUE_DATE := l_custom_tbl(i).DUE_DATE;
2971 l_loan_header_rec.BILLED_FLAG := 'N';
2972 end if;
2973
2974 end loop;
2975
2976 -- deleting all invalid rows
2977 delete from LNS_CUSTOM_PAYMNT_SCHEDS where loan_id = p_loan_id and PAYMENT_NUMBER = -1;
2978
2979 -- update all rows with action null
2980 for i in 1..l_custom_tbl.count loop
2981 l_custom_tbl(i).ACTION := null;
2982 end loop;
2983
2984 -- getting info from lns_loan_headers_all
2985 select object_version_number
2986 into l_object_version
2987 from lns_loan_headers_all
2988 where loan_id = p_loan_id;
2989
2990 -- update lns_loan_headers_all only if loan is not custom yet
2991 if l_loan_details.CUSTOM_SCHEDULE = 'N' or l_loan_header_rec.BILLED_FLAG is not null then
2992 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating lns_loan_headers_all');
2993 l_loan_header_rec.loan_id := P_LOAN_ID;
2994 l_loan_header_rec.CUSTOM_PAYMENTS_FLAG := 'Y';
2995
2996 lns_loan_header_pub.update_loan(p_init_msg_list => FND_API.G_TRUE
2997 ,p_loan_header_rec => l_loan_header_rec
2998 ,P_OBJECT_VERSION_NUMBER => l_object_version
2999 ,X_RETURN_STATUS => l_return_status
3000 ,X_MSG_COUNT => l_msg_count
3001 ,X_MSG_DATA => l_msg_data);
3002
3003 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'l_return_status: ' || l_return_status);
3004
3005 IF l_return_status = 'S' THEN
3006 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully updated lns_loan_headers_all');
3007 ELSE
3008 FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_LOAN_FAIL');
3009 FND_MSG_PUB.Add;
3010 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3011 RAISE FND_API.G_EXC_ERROR;
3012 end if;
3013 end if;
3014
3015 -- getting terms version for future update
3016 select term_id, object_version_number into l_term_id, l_object_version
3017 from lns_terms
3018 where loan_id = p_loan_id;
3019
3020 -- Updating terms
3021 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating lns_terms w following values:');
3022
3023 l_term_rec.TERM_ID := l_term_id;
3024 l_term_rec.LOAN_ID := p_loan_id;
3025 l_term_rec.PAYMENT_CALC_METHOD := 'CUSTOM';
3026 l_term_rec.CUSTOM_CALC_METHOD := P_AMORT_METHOD;
3027
3028 if l_loan_details.loan_status <> 'INCOMPLETE' and l_loan_details.CUSTOM_SCHEDULE = 'N' then
3029 l_term_rec.ORIG_PAY_CALC_METHOD := l_loan_details.PAYMENT_CALC_METHOD;
3030 elsif l_loan_details.loan_status = 'INCOMPLETE' then
3031 l_term_rec.ORIG_PAY_CALC_METHOD := FND_API.G_MISS_CHAR;
3032 end if;
3033
3034 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'CUSTOM_CALC_METHOD: ' || l_term_rec.CUSTOM_CALC_METHOD);
3035
3036 LNS_TERMS_PUB.update_term(P_OBJECT_VERSION_NUMBER => l_object_version,
3037 p_init_msg_list => FND_API.G_FALSE,
3038 p_loan_term_rec => l_term_rec,
3039 X_RETURN_STATUS => l_return_status,
3040 X_MSG_COUNT => l_msg_count,
3041 X_MSG_DATA => l_msg_data);
3042
3043 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status: ' || l_return_status);
3044
3045 IF l_return_status = 'S' THEN
3046 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully update LNS_TERMS');
3047 ELSE
3048 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: LNS_TERMS_PUB.update_term returned error: ' || substr(l_msg_data,1,225));
3049 FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_TERM_FAIL');
3050 FND_MSG_PUB.Add;
3051 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3052 RAISE FND_API.G_EXC_ERROR;
3053 END IF;
3054
3055 -- getting number of installments and synching rate schedule
3056 l_num_installments := l_custom_tbl(l_custom_tbl.count).PAYMENT_NUMBER;
3057 synchRateSchedule(l_term_id, l_num_installments);
3058
3059 if P_COMMIT = FND_API.G_TRUE then
3060 COMMIT WORK;
3061 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
3062 end if;
3063
3064 P_CUSTOM_TBL := l_CUSTOM_TBL;
3065
3066 -- END OF BODY OF API
3067 x_return_status := FND_API.G_RET_STS_SUCCESS;
3068
3069 -- Standard call to get message count and if count is 1, get message info
3070 FND_MSG_PUB.Count_And_Get(
3071 p_encoded => FND_API.G_FALSE,
3072 p_count => x_msg_count,
3073 p_data => x_msg_data);
3074
3075 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
3076
3077 EXCEPTION
3078 WHEN FND_API.G_EXC_ERROR THEN
3079 ROLLBACK TO saveCustomSchedule;
3080 x_return_status := FND_API.G_RET_STS_ERROR;
3081 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3082 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
3083 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3084 ROLLBACK TO saveCustomSchedule;
3085 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3086 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3087 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
3088 WHEN OTHERS THEN
3089 ROLLBACK TO saveCustomSchedule;
3090 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3091 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
3092 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3093 END IF;
3094 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3095 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
3096 END;
3097
3098
3099
3100 -- This procedure switches back from custom schedule to standard schedule in one shot
3101 -- Conditions: loan status is INCOMPLETE and loan has been already customized
3102 procedure uncustomizeSchedule(
3103 P_API_VERSION IN NUMBER,
3104 P_INIT_MSG_LIST IN VARCHAR2,
3105 P_COMMIT IN VARCHAR2,
3106 P_VALIDATION_LEVEL IN NUMBER,
3107 P_LOAN_ID IN NUMBER,
3108 P_ST_AMORT_METHOD IN VARCHAR2,
3109 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3110 X_MSG_COUNT OUT NOCOPY NUMBER,
3111 X_MSG_DATA OUT NOCOPY VARCHAR2)
3112 IS
3113
3114 /*-----------------------------------------------------------------------+
3115 | Local Variable Declarations and initializations |
3116 +-----------------------------------------------------------------------*/
3117
3118 l_api_name CONSTANT VARCHAR2(30) := 'uncustomizeSchedule';
3119 l_api_version CONSTANT NUMBER := 1.0;
3120 l_return_status VARCHAR2(1);
3121 l_msg_count NUMBER;
3122 l_msg_data VARCHAR2(32767);
3123
3124 l_loan_details LNS_CUSTOM_PUB.LOAN_DETAILS_REC;
3125 l_loan_header_rec LNS_LOAN_HEADER_PUB.LOAN_HEADER_REC_TYPE;
3126 l_term_rec LNS_TERMS_PUB.loan_term_rec_type;
3127
3128 l_object_version number;
3129 l_term_id number;
3130 l_BASED_ON_TERMS varchar2(30);
3131 l_num_installments number;
3132
3133 /*-----------------------------------------------------------------------+
3134 | Cursor Declarations |
3135 +-----------------------------------------------------------------------*/
3136
3137 BEGIN
3138
3139 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
3140
3141 -- Standard start of API savepoint
3142 SAVEPOINT uncustomizeSchedule;
3143 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Savepoint is established');
3144
3145 -- Standard call to check for call compatibility
3146 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3147 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3148 END IF;
3149
3150 -- Initialize message list if p_init_msg_list is set to TRUE
3151 IF FND_API.To_Boolean(p_init_msg_list) THEN
3152 FND_MSG_PUB.initialize;
3153 END IF;
3154
3155 -- Initialize API return status to success
3156 l_return_status := FND_API.G_RET_STS_SUCCESS;
3157
3158 -- START OF BODY OF API
3159
3160 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
3161 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID: ' || P_LOAN_ID);
3162 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_ST_AMORT_METHOD: ' || P_ST_AMORT_METHOD);
3163
3164 if P_LOAN_ID is null then
3165 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
3166 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
3167 FND_MESSAGE.SET_TOKEN('VALUE', P_LOAN_ID);
3168 FND_MSG_PUB.ADD;
3169 RAISE FND_API.G_EXC_ERROR;
3170 end if;
3171
3172 l_BASED_ON_TERMS := 'ORIGINATION';
3173 l_loan_details := getLoanDetails(p_loan_Id => p_loan_id
3174 ,p_based_on_terms => l_based_on_terms);
3175
3176 -- allow to save initial custom schedule only if this loan is in INCOMPLETE status and is not customized yet
3177 -- if l_loan_details.loan_status = 'INCOMPLETE' and l_loan_details.CUSTOM_SCHEDULE = 'Y' then
3178 if l_loan_details.loan_status = 'INCOMPLETE' then
3179
3180 delete from LNS_CUSTOM_PAYMNT_SCHEDS
3181 where loan_id = p_loan_id;
3182 /*
3183 -- getting info from lns_loan_headers_all
3184 select object_version_number
3185 into l_object_version
3186 from lns_loan_headers_all
3187 where loan_id = p_loan_id;
3188
3189 -- update lns_loan_headers_all only if loan is not custom yet
3190 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating lns_loan_headers_all set custom = N');
3191 l_loan_header_rec.loan_id := P_LOAN_ID;
3192 l_loan_header_rec.CUSTOM_PAYMENTS_FLAG := 'N';
3193
3194 lns_loan_header_pub.update_loan(p_init_msg_list => FND_API.G_TRUE
3195 ,p_loan_header_rec => l_loan_header_rec
3196 ,P_OBJECT_VERSION_NUMBER => l_object_version
3197 ,X_RETURN_STATUS => l_return_status
3198 ,X_MSG_COUNT => l_msg_count
3199 ,X_MSG_DATA => l_msg_data);
3200
3201 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'l_return_status: ' || l_return_status);
3202
3203 IF l_return_status = 'S' THEN
3204 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully updated lns_loan_headers_all');
3205 ELSE
3206 FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_LOAN_FAIL');
3207 FND_MSG_PUB.Add;
3208 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3209 RAISE FND_API.G_EXC_ERROR;
3210 end if;
3211
3212 -- getting terms version for future update
3213 select term_id, object_version_number into l_term_id, l_object_version
3214 from lns_terms
3215 where loan_id = p_loan_id;
3216
3217 -- Updating terms
3218 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating lns_terms w following values:');
3219
3220 l_term_rec.TERM_ID := l_term_id;
3221 l_term_rec.LOAN_ID := p_loan_id;
3222 l_term_rec.PAYMENT_CALC_METHOD := P_ST_AMORT_METHOD;
3223 l_term_rec.CUSTOM_CALC_METHOD := FND_API.G_MISS_CHAR;
3224 l_term_rec.ORIG_PAY_CALC_METHOD := FND_API.G_MISS_CHAR;
3225
3226 LNS_TERMS_PUB.update_term(P_OBJECT_VERSION_NUMBER => l_object_version,
3227 p_init_msg_list => FND_API.G_FALSE,
3228 p_loan_term_rec => l_term_rec,
3229 X_RETURN_STATUS => l_return_status,
3230 X_MSG_COUNT => l_msg_count,
3231 X_MSG_DATA => l_msg_data);
3232
3233 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status: ' || l_return_status);
3234
3235 IF l_return_status = 'S' THEN
3236 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully update LNS_TERMS');
3237 ELSE
3238 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: LNS_TERMS_PUB.update_term returned error: ' || substr(l_msg_data,1,225));
3239 FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_TERM_FAIL');
3240 FND_MSG_PUB.Add;
3241 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3242 RAISE FND_API.G_EXC_ERROR;
3243 END IF;
3244
3245 -- synching rate schedule
3246 l_num_installments := LNS_FIN_UTILS.getNumberInstallments(p_loan_id);
3247 synchRateSchedule(l_term_id, l_num_installments);
3248 */
3249 else
3250 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Nothing to update');
3251 end if;
3252
3253 if P_COMMIT = FND_API.G_TRUE then
3254 COMMIT WORK;
3255 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
3256 end if;
3257
3258 -- END OF BODY OF API
3259 x_return_status := FND_API.G_RET_STS_SUCCESS;
3260
3261 -- Standard call to get message count and if count is 1, get message info
3262 FND_MSG_PUB.Count_And_Get(
3263 p_encoded => FND_API.G_FALSE,
3264 p_count => x_msg_count,
3265 p_data => x_msg_data);
3266
3267 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
3268
3269 EXCEPTION
3270 WHEN FND_API.G_EXC_ERROR THEN
3271 ROLLBACK TO uncustomizeSchedule;
3272 x_return_status := FND_API.G_RET_STS_ERROR;
3273 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3274 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
3275 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3276 ROLLBACK TO uncustomizeSchedule;
3277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3278 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3279 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
3280 WHEN OTHERS THEN
3281 ROLLBACK TO uncustomizeSchedule;
3282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3283 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
3284 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3285 END IF;
3286 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3287 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
3288 END;
3289
3290
3291
3292 -- This procedure switches from standard schedule to custom schedule in one shot
3293 -- Conditions: loan status is INCOMPLETE and loan has not been customized yet
3294 procedure customizeSchedule(
3295 P_API_VERSION IN NUMBER,
3296 P_INIT_MSG_LIST IN VARCHAR2,
3297 P_COMMIT IN VARCHAR2,
3298 P_VALIDATION_LEVEL IN NUMBER,
3299 P_LOAN_ID IN NUMBER,
3300 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3301 X_MSG_COUNT OUT NOCOPY NUMBER,
3302 X_MSG_DATA OUT NOCOPY VARCHAR2)
3303 IS
3304
3305 /*-----------------------------------------------------------------------+
3306 | Local Variable Declarations and initializations |
3307 +-----------------------------------------------------------------------*/
3308
3309 l_api_name CONSTANT VARCHAR2(30) := 'customizeSchedule';
3310 l_api_version CONSTANT NUMBER := 1.0;
3311 l_return_status VARCHAR2(1);
3312 l_msg_count NUMBER;
3313 l_msg_data VARCHAR2(32767);
3314
3315 l_loan_details LNS_CUSTOM_PUB.LOAN_DETAILS_REC;
3316 l_custom_tbl LNS_CUSTOM_PUB.CUSTOM_TBL;
3317 l_AMORT_METHOD varchar2(30);
3318 l_BASED_ON_TERMS varchar2(30);
3319
3320 /*-----------------------------------------------------------------------+
3321 | Cursor Declarations |
3322 +-----------------------------------------------------------------------*/
3323
3324 BEGIN
3325
3326 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
3327
3328 -- Standard start of API savepoint
3329 SAVEPOINT customizeSchedule;
3330 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Savepoint is established');
3331
3332 -- Standard call to check for call compatibility
3333 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3335 END IF;
3336
3337 -- Initialize message list if p_init_msg_list is set to TRUE
3338 IF FND_API.To_Boolean(p_init_msg_list) THEN
3339 FND_MSG_PUB.initialize;
3340 END IF;
3341
3342 -- Initialize API return status to success
3343 l_return_status := FND_API.G_RET_STS_SUCCESS;
3344
3345 -- START OF BODY OF API
3346
3347 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
3348 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID: ' || P_LOAN_ID);
3349
3350 if P_LOAN_ID is null then
3351 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
3352 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
3353 FND_MESSAGE.SET_TOKEN('VALUE', P_LOAN_ID);
3354 FND_MSG_PUB.ADD;
3355 RAISE FND_API.G_EXC_ERROR;
3356 end if;
3357
3358 l_BASED_ON_TERMS := 'ORIGINATION';
3359 l_loan_details := getLoanDetails(p_loan_Id => p_loan_id
3360 ,p_based_on_terms => l_based_on_terms);
3361
3362 -- allow to save initial custom schedule only if this loan is in INCOMPLETE status and is not customized yet
3363 if l_loan_details.loan_status = 'INCOMPLETE' and l_loan_details.CUSTOM_SCHEDULE = 'N' then
3364
3365 -- load initial schedule
3366 LNS_CUSTOM_PUB.loadCustomSchedule(
3367 P_API_VERSION => 1.0,
3368 P_INIT_MSG_LIST => FND_API.G_TRUE,
3369 P_COMMIT => FND_API.G_FALSE,
3370 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
3371 P_LOAN_ID => P_LOAN_ID,
3372 P_BASED_ON_TERMS => l_BASED_ON_TERMS,
3373 X_AMORT_METHOD => l_AMORT_METHOD,
3374 X_CUSTOM_TBL => l_custom_tbl,
3375 x_return_status => l_return_status,
3376 x_msg_count => l_msg_count,
3377 x_msg_data => l_msg_data);
3378
3379 IF l_return_status <> 'S' THEN
3380 RAISE FND_API.G_EXC_ERROR;
3381 END IF;
3382
3383 -- immediatly save it
3384 LNS_CUSTOM_PUB.saveCustomSchedule(
3385 P_API_VERSION => 1.0,
3386 P_INIT_MSG_LIST => FND_API.G_TRUE,
3387 P_COMMIT => FND_API.G_FALSE,
3388 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
3389 P_LOAN_ID => P_LOAN_ID,
3390 P_BASED_ON_TERMS => l_BASED_ON_TERMS,
3391 P_AMORT_METHOD => l_AMORT_METHOD,
3392 P_CUSTOM_TBL => l_custom_tbl,
3393 x_return_status => l_return_status,
3394 x_msg_count => l_msg_count,
3395 x_msg_data => l_msg_data);
3396
3397 IF l_return_status <> 'S' THEN
3398 RAISE FND_API.G_EXC_ERROR;
3399 END IF;
3400
3401 end if;
3402
3403 if P_COMMIT = FND_API.G_TRUE then
3404 COMMIT WORK;
3405 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
3406 end if;
3407
3408 -- END OF BODY OF API
3409 x_return_status := FND_API.G_RET_STS_SUCCESS;
3410
3411 -- Standard call to get message count and if count is 1, get message info
3412 FND_MSG_PUB.Count_And_Get(
3413 p_encoded => FND_API.G_FALSE,
3414 p_count => x_msg_count,
3415 p_data => x_msg_data);
3416
3417 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
3418
3419 EXCEPTION
3420 WHEN FND_API.G_EXC_ERROR THEN
3421 ROLLBACK TO customizeSchedule;
3422 x_return_status := FND_API.G_RET_STS_ERROR;
3423 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3424 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
3425 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3426 ROLLBACK TO customizeSchedule;
3427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3428 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3429 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
3430 WHEN OTHERS THEN
3431 ROLLBACK TO customizeSchedule;
3432 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3433 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
3434 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3435 END IF;
3436 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3437 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
3438 END;
3439
3440
3441
3442 /*
3443 This funciton will ensure the rows in the custom tbl are ordered by due date.
3444 Will validate that due dates are unique
3445 Return 1 - success; 0 - failed
3446 */
3447 function shiftRowsByDate(P_OLD_DUE_DATE IN DATE,
3448 P_NEW_DUE_DATE IN DATE,
3449 p_custom_tbl in out nocopy LNS_CUSTOM_PUB.custom_tbl) return NUMBER
3450 is
3451 l_custom_tbl LNS_CUSTOM_PUB.custom_tbl;
3452 i number;
3453 l_found boolean;
3454 l_shift_from_row number;
3455 l_month_diff number;
3456 l_day_diff number;
3457 l_old_date date;
3458
3459 begin
3460 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'shiftRowsByDate +');
3461
3462 if P_OLD_DUE_DATE is null then
3463 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_OLD_DUE_DATE is null. Exiting');
3464 return 0;
3465 end if;
3466
3467 if P_OLD_DUE_DATE is null or P_OLD_DUE_DATE = P_NEW_DUE_DATE then
3468 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_OLD_DUE_DATE = P_NEW_DUE_DATE. Exiting');
3469 return 1;
3470 end if;
3471
3472 l_custom_tbl := p_custom_tbl;
3473 l_found := false;
3474
3475 -- find row from which we will shift schedule
3476 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Looking for start shift row...');
3477 for i in 1..l_custom_tbl.count loop
3478 if l_custom_tbl(i).DUE_DATE = P_NEW_DUE_DATE then
3479 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Found row ' || i);
3480 l_shift_from_row := i;
3481 l_found := true;
3482
3483 l_month_diff := months_between(P_NEW_DUE_DATE, P_OLD_DUE_DATE);
3484 if sign(l_month_diff) = -1 then
3485 l_month_diff := ceil(l_month_diff);
3486 elsif sign(l_month_diff) = 1 then
3487 l_month_diff := floor(l_month_diff);
3488 end if;
3489 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_month_diff: ' || l_month_diff);
3490
3491 l_day_diff := P_NEW_DUE_DATE - add_months(P_OLD_DUE_DATE, l_month_diff);
3492 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_day_diff: ' || l_day_diff);
3493 exit;
3494 end if;
3495 end loop;
3496
3497 if l_found = false then
3498 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'No start shift row found. Exiting');
3499 return 1;
3500 end if;
3501
3502 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Shifting dates...');
3503 for i in l_shift_from_row..l_custom_tbl.count loop
3504
3505 if i = l_shift_from_row then
3506 l_old_date := P_OLD_DUE_DATE;
3507 else
3508 l_old_date := l_custom_tbl(i).DUE_DATE;
3509 l_custom_tbl(i).DUE_DATE := add_months(l_old_date, l_month_diff) + l_day_diff;
3510 end if;
3511 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_old_date || ' -> ' || l_custom_tbl(i).DUE_DATE);
3512
3513 end loop;
3514 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done shifting.');
3515
3516 p_custom_tbl := l_custom_tbl;
3517 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'shiftRowsByDate -');
3518
3519 return 1;
3520 end;
3521
3522
3523
3524
3525 -- This procedure recalculates custom schedule with shifting all subsequent due dates on a single due date change
3526 procedure shiftCustomSchedule(
3527 P_API_VERSION IN NUMBER,
3528 P_INIT_MSG_LIST IN VARCHAR2,
3529 P_COMMIT IN VARCHAR2,
3530 P_VALIDATION_LEVEL IN NUMBER,
3531 P_LOAN_ID IN NUMBER,
3532 P_OLD_DUE_DATE IN DATE,
3533 P_NEW_DUE_DATE IN DATE,
3534 P_AMORT_METHOD IN VARCHAR2,
3535 P_BASED_ON_TERMS IN VARCHAR2,
3536 P_CUSTOM_TBL IN OUT NOCOPY LNS_CUSTOM_PUB.CUSTOM_TBL,
3537 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3538 X_MSG_COUNT OUT NOCOPY NUMBER,
3539 X_MSG_DATA OUT NOCOPY VARCHAR2)
3540 IS
3541
3542 /*-----------------------------------------------------------------------+
3543 | Local Variable Declarations and initializations |
3544 +-----------------------------------------------------------------------*/
3545
3546 l_api_name CONSTANT VARCHAR2(30) := 'shiftCustomSchedule';
3547 l_api_version CONSTANT NUMBER := 1.0;
3548 l_return_status VARCHAR2(1);
3549 l_msg_count NUMBER;
3550 l_msg_data VARCHAR2(32767);
3551
3552 l_custom_tbl LNS_CUSTOM_PUB.CUSTOM_TBL;
3553 l_return number;
3554
3555 /*-----------------------------------------------------------------------+
3556 | Cursor Declarations |
3557 +-----------------------------------------------------------------------*/
3558
3559 BEGIN
3560
3561 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
3562
3563 -- Standard start of API savepoint
3564 SAVEPOINT shiftCustomSchedule;
3565 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Savepoint is established');
3566
3567 -- Standard call to check for call compatibility
3568 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3569 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3570 END IF;
3571
3572 -- Initialize message list if p_init_msg_list is set to TRUE
3573 IF FND_API.To_Boolean(p_init_msg_list) THEN
3574 FND_MSG_PUB.initialize;
3575 END IF;
3576
3577 -- Initialize API return status to success
3578 l_return_status := FND_API.G_RET_STS_SUCCESS;
3579
3580 -- START OF BODY OF API
3581
3582 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
3583 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID: ' || P_LOAN_ID);
3584 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_OLD_DUE_DATE: ' || P_OLD_DUE_DATE);
3585 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_NEW_DUE_DATE: ' || P_NEW_DUE_DATE);
3586 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_AMORT_METHOD: ' || P_AMORT_METHOD);
3587 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_BASED_ON_TERMS: ' || P_BASED_ON_TERMS);
3588
3589 if P_LOAN_ID is null then
3590 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
3591 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
3592 FND_MESSAGE.SET_TOKEN('VALUE', P_LOAN_ID);
3593 FND_MSG_PUB.ADD;
3594 RAISE FND_API.G_EXC_ERROR;
3595 end if;
3596
3597 if P_BASED_ON_TERMS is null then
3598 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
3599 FND_MESSAGE.SET_TOKEN('PARAMETER', 'BASED_ON_TERMS');
3600 FND_MESSAGE.SET_TOKEN('VALUE', P_BASED_ON_TERMS);
3601 FND_MSG_PUB.ADD;
3602 RAISE FND_API.G_EXC_ERROR;
3603 end if;
3604
3605 if P_AMORT_METHOD is null then
3606 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
3607 FND_MESSAGE.SET_TOKEN('PARAMETER', 'AMORT_METHOD');
3608 FND_MESSAGE.SET_TOKEN('VALUE', P_AMORT_METHOD);
3609 FND_MSG_PUB.ADD;
3610 RAISE FND_API.G_EXC_ERROR;
3611 end if;
3612
3613 if P_NEW_DUE_DATE is null then
3614 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
3615 FND_MESSAGE.SET_TOKEN('PARAMETER', 'NEW_DUE_DATE');
3616 FND_MESSAGE.SET_TOKEN('VALUE', P_NEW_DUE_DATE);
3617 FND_MSG_PUB.ADD;
3618 RAISE FND_API.G_EXC_ERROR;
3619 end if;
3620
3621 l_CUSTOM_TBL := P_CUSTOM_TBL;
3622
3623 filterCustSchedule(l_custom_tbl);
3624 if l_custom_tbl.count = 0 then
3625 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Custom amortization is empty.');
3626 FND_MESSAGE.SET_NAME('LNS', 'LNS_CUST_AMORT_EMPTY');
3627 FND_MSG_PUB.Add;
3628 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3629 RAISE FND_API.G_EXC_ERROR;
3630 end if;
3631
3632 l_return := shiftRowsByDate(P_OLD_DUE_DATE => P_OLD_DUE_DATE,
3633 P_NEW_DUE_DATE => P_NEW_DUE_DATE,
3634 P_CUSTOM_TBL => l_CUSTOM_TBL);
3635
3636 if l_return = 0 then
3637 return;
3638 end if;
3639
3640 LNS_CUSTOM_PUB.recalcCustomSchedule(
3641 P_API_VERSION => 1.0,
3642 P_INIT_MSG_LIST => FND_API.G_TRUE,
3643 P_COMMIT => FND_API.G_FALSE,
3644 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
3645 P_LOAN_ID => P_LOAN_ID,
3646 P_AMORT_METHOD => P_AMORT_METHOD,
3647 P_BASED_ON_TERMS => P_BASED_ON_TERMS,
3648 P_CUSTOM_TBL => l_custom_tbl,
3649 x_return_status => l_return_status,
3650 x_msg_count => l_msg_count,
3651 x_msg_data => l_msg_data);
3652
3653 IF l_return_status <> 'S' THEN
3654 RAISE FND_API.G_EXC_ERROR;
3655 END IF;
3656
3657 P_CUSTOM_TBL := l_CUSTOM_TBL;
3658
3659 if P_COMMIT = FND_API.G_TRUE then
3660 COMMIT WORK;
3661 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
3662 end if;
3663
3664 -- END OF BODY OF API
3665 x_return_status := FND_API.G_RET_STS_SUCCESS;
3666
3667 -- Standard call to get message count and if count is 1, get message info
3668 FND_MSG_PUB.Count_And_Get(
3669 p_encoded => FND_API.G_FALSE,
3670 p_count => x_msg_count,
3671 p_data => x_msg_data);
3672
3673 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
3674
3675 EXCEPTION
3676 WHEN FND_API.G_EXC_ERROR THEN
3677 ROLLBACK TO shiftCustomSchedule;
3678 x_return_status := FND_API.G_RET_STS_ERROR;
3679 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3680 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
3681 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3682 ROLLBACK TO shiftCustomSchedule;
3683 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3684 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3685 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
3686 WHEN OTHERS THEN
3687 ROLLBACK TO shiftCustomSchedule;
3688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3689 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
3690 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3691 END IF;
3692 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3693 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
3694 END;
3695
3696
3697 procedure reBuildCustomdSchedule(
3698 P_API_VERSION IN NUMBER,
3699 P_INIT_MSG_LIST IN VARCHAR2,
3700 P_COMMIT IN VARCHAR2,
3701 P_VALIDATION_LEVEL IN NUMBER,
3702 P_LOAN_ID IN NUMBER,
3703 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3704 X_MSG_COUNT OUT NOCOPY NUMBER,
3705 X_MSG_DATA OUT NOCOPY VARCHAR2)
3706 IS
3707
3708 /*-----------------------------------------------------------------------+
3709 | Local Variable Declarations and initializations |
3710 +-----------------------------------------------------------------------*/
3711
3712 l_api_name CONSTANT VARCHAR2(30) := 'reBuildCustomdSchedule';
3713 l_api_version CONSTANT NUMBER := 1.0;
3714 l_return_status VARCHAR2(1);
3715 l_msg_count NUMBER;
3716 l_msg_data VARCHAR2(32767);
3717
3718 l_loan_details LNS_CUSTOM_PUB.LOAN_DETAILS_REC;
3719 l_loan_header_rec LNS_LOAN_HEADER_PUB.LOAN_HEADER_REC_TYPE;
3720 l_term_rec LNS_TERMS_PUB.loan_term_rec_type;
3721
3722 l_object_version number;
3723 l_term_id number;
3724 l_BASED_ON_TERMS varchar2(30);
3725 l_num_installments number;
3726 l_maturity_date DATE;
3727
3728 /*-----------------------------------------------------------------------+
3729 | Cursor Declarations |
3730 +-----------------------------------------------------------------------*/
3731
3732 BEGIN
3733
3734 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
3735
3736 -- Standard start of API savepoint
3737 SAVEPOINT reBuildCustomdSchedule;
3738 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Savepoint is established');
3739
3740 -- Standard call to check for call compatibility
3741 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3742 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3743 END IF;
3744
3745 -- Initialize message list if p_init_msg_list is set to TRUE
3746 IF FND_API.To_Boolean(p_init_msg_list) THEN
3747 FND_MSG_PUB.initialize;
3748 END IF;
3749
3750 -- Initialize API return status to success
3751 l_return_status := FND_API.G_RET_STS_SUCCESS;
3752
3753 -- START OF BODY OF API
3754
3755 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
3756 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID: ' || P_LOAN_ID);
3757
3758 if P_LOAN_ID is null then
3759 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
3760 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
3761 FND_MESSAGE.SET_TOKEN('VALUE', P_LOAN_ID);
3762 FND_MSG_PUB.ADD;
3763 RAISE FND_API.G_EXC_ERROR;
3764 end if;
3765
3766 l_BASED_ON_TERMS := 'ORIGINATION';
3767 l_loan_details := getLoanDetails(p_loan_Id => p_loan_id
3768 ,p_based_on_terms => l_based_on_terms);
3769
3770 l_maturity_date := l_loan_details.MATURITY_DATE;
3771
3772 -- allow to reBuild custom schedule only if this loan is in INCOMPLETE status
3773 --if l_loan_details.loan_status = 'INCOMPLETE' then
3774 if l_loan_details.loan_status = 'INCOMPLETE' and l_loan_details.CUSTOM_SCHEDULE = 'Y' then
3775
3776 delete from LNS_CUSTOM_PAYMNT_SCHEDS
3777 where loan_id = p_loan_id
3778 and due_date > l_maturity_date;
3779
3780 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Cust Rows might be deleted.');
3781
3782 else
3783 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Nothing to update');
3784 end if;
3785
3786 if P_COMMIT = FND_API.G_TRUE then
3787 COMMIT WORK;
3788 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
3789 end if;
3790
3791 -- END OF BODY OF API
3792 x_return_status := FND_API.G_RET_STS_SUCCESS;
3793
3794 -- Standard call to get message count and if count is 1, get message info
3795 FND_MSG_PUB.Count_And_Get(
3796 p_encoded => FND_API.G_FALSE,
3797 p_count => x_msg_count,
3798 p_data => x_msg_data);
3799
3800 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
3801
3802 EXCEPTION
3803 WHEN FND_API.G_EXC_ERROR THEN
3804 ROLLBACK TO reBuildCustomdSchedule;
3805 x_return_status := FND_API.G_RET_STS_ERROR;
3806 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3807 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
3808 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3809 ROLLBACK TO reBuildCustomdSchedule;
3810 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3811 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3812 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
3813 WHEN OTHERS THEN
3814 ROLLBACK TO reBuildCustomdSchedule;
3815 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3816 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
3817 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3818 END IF;
3819 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3820 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
3821 END;
3822
3823
3824 -- This procedure builds custom payment schedule and returns LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL table
3825 function buildCustomPaySchedule(P_LOAN_ID IN NUMBER) return LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL
3826 IS
3827
3828 /*-----------------------------------------------------------------------+
3829 | Local Variable Declarations and initializations |
3830 +-----------------------------------------------------------------------*/
3831
3832 l_api_name CONSTANT VARCHAR2(30) := 'buildCustomPaySchedule';
3833 l_payment_schedule LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
3834 l_loan_details LNS_CUSTOM_PUB.LOAN_DETAILS_REC;
3835 l_due_date date;
3836 l_payment_number number;
3837 i number;
3838
3839 /*-----------------------------------------------------------------------+
3840 | Cursor Declarations |
3841 +-----------------------------------------------------------------------*/
3842
3843 -- cursor to get due_dates of already built installments
3844 cursor c_built_payments(p_loan_id NUMBER) IS
3845 select PAYMENT_NUMBER, DUE_DATE
3846 from lns_amortization_scheds
3847 where loan_id = p_loan_id
3848 and (REVERSED_FLAG is null or REVERSED_FLAG = 'N')
3849 and parent_amortization_id is null
3850 and REAMORTIZATION_AMOUNT is null
3851 and nvl(phase, 'TERM') = 'TERM'
3852 order by PAYMENT_NUMBER;
3853
3854 -- cursor to load custom schedule
3855 cursor c_load_sched(p_loan_id NUMBER, p_min_payment NUMBER) IS
3856 select PAYMENT_NUMBER, DUE_DATE
3857 from LNS_CUSTOM_PAYMNT_SCHEDS
3858 where loan_id = p_loan_id
3859 and PAYMENT_NUMBER >= p_min_payment
3860 order by PAYMENT_NUMBER;
3861
3862 BEGIN
3863
3864 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
3865 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
3866 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID: ' || P_LOAN_ID);
3867
3868 l_loan_details := getLoanDetails(p_loan_id => p_loan_id
3869 ,p_based_on_terms => 'CURRENT');
3870
3871 if l_loan_details.CUSTOM_SCHEDULE = 'N' then
3872 return l_payment_schedule;
3873 end if;
3874
3875 i := 1;
3876 if l_loan_details.LAST_INSTALLMENT_BILLED > 0 then
3877
3878 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Loading dates from lns_amortization_scheds:');
3879 OPEN c_built_payments(p_loan_id);
3880 LOOP
3881
3882 FETCH c_built_payments INTO l_payment_number, l_due_date;
3883 exit when c_built_payments%NOTFOUND;
3884
3885 if i = 1 then
3886 l_payment_schedule(i).PERIOD_BEGIN_DATE := l_loan_details.LOAN_START_DATE;
3887 else
3888 l_payment_schedule(i).PERIOD_BEGIN_DATE := l_payment_schedule(i-1).PERIOD_END_DATE;
3889 end if;
3890
3891 l_payment_schedule(i).PERIOD_DUE_DATE := l_due_date;
3892 l_payment_schedule(i).PERIOD_END_DATE := l_due_date;
3893
3894 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Payment ' || l_payment_number);
3895 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PERIOD_BEGIN_DATE: ' || l_payment_schedule(i).PERIOD_BEGIN_DATE);
3896 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PERIOD_DUE_DATE: ' || l_payment_schedule(i).PERIOD_DUE_DATE);
3897 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PERIOD_END_DATE: ' || l_payment_schedule(i).PERIOD_END_DATE);
3898 i := i + 1;
3899
3900 END LOOP;
3901 CLOSE c_built_payments;
3902
3903 end if;
3904
3905 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Loading dates from LNS_CUSTOM_PAYMNT_SCHEDS:');
3906 OPEN c_load_sched(p_loan_id, i);
3907 LOOP
3908
3909 FETCH c_load_sched INTO l_payment_number, l_due_date;
3910 exit when c_load_sched%NOTFOUND;
3911
3912 if i = 1 then
3913 l_payment_schedule(i).PERIOD_BEGIN_DATE := l_loan_details.LOAN_START_DATE;
3914 else
3915 l_payment_schedule(i).PERIOD_BEGIN_DATE := l_payment_schedule(i-1).PERIOD_END_DATE;
3916 end if;
3917
3918 l_payment_schedule(i).PERIOD_DUE_DATE := l_due_date;
3919 l_payment_schedule(i).PERIOD_END_DATE := l_due_date;
3920
3921 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Payment ' || l_payment_number);
3922 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PERIOD_BEGIN_DATE: ' || l_payment_schedule(i).PERIOD_BEGIN_DATE);
3923 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PERIOD_DUE_DATE: ' || l_payment_schedule(i).PERIOD_DUE_DATE);
3924 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PERIOD_END_DATE: ' || l_payment_schedule(i).PERIOD_END_DATE);
3925 i := i + 1;
3926
3927 END LOOP;
3928 CLOSE c_load_sched;
3929
3930 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
3931
3932 return l_payment_schedule;
3933 END;
3934
3935
3936 -- added for bug 7716548
3937 -- This procedure adds installment to custom schedule only if it does not already exist
3938 procedure addMissingInstallment(
3939 P_API_VERSION IN NUMBER,
3940 P_INIT_MSG_LIST IN VARCHAR2,
3941 P_COMMIT IN VARCHAR2,
3942 P_VALIDATION_LEVEL IN NUMBER,
3943 P_INSTALLMENT_REC IN LNS_CUSTOM_PUB.custom_sched_type,
3944 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3945 X_MSG_COUNT OUT NOCOPY NUMBER,
3946 X_MSG_DATA OUT NOCOPY VARCHAR2)
3947 IS
3948
3949 /*-----------------------------------------------------------------------+
3950 | Local Variable Declarations and initializations |
3951 +-----------------------------------------------------------------------*/
3952
3953 l_api_name CONSTANT VARCHAR2(30) := 'addMissingInstallment';
3954 l_api_version CONSTANT NUMBER := 1.0;
3955 l_return_status VARCHAR2(1);
3956 l_msg_count NUMBER;
3957 l_msg_data VARCHAR2(32767);
3958
3959 l_loan_details LNS_CUSTOM_PUB.LOAN_DETAILS_REC;
3960 l_custom_sched_id NUMBER;
3961 l_INSTALLMENT_REC LNS_CUSTOM_PUB.custom_sched_type;
3962
3963 /*-----------------------------------------------------------------------+
3964 | Cursor Declarations |
3965 +-----------------------------------------------------------------------*/
3966
3967 -- check is such payment already exist
3968 cursor c_exist_installment(p_loan_id NUMBER, p_payment_number NUMBER) IS
3969 select custom_schedule_id
3970 from lns_custom_paymnt_scheds
3971 where loan_id = p_loan_id
3972 and payment_number = p_payment_number;
3973
3974 BEGIN
3975
3976 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
3977
3978 -- Standard start of API savepoint
3979 SAVEPOINT addMissingInstallment;
3980 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Savepoint is established');
3981
3982 -- Standard call to check for call compatibility
3983 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3984 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3985 END IF;
3986
3987 -- Initialize message list if p_init_msg_list is set to TRUE
3988 IF FND_API.To_Boolean(p_init_msg_list) THEN
3989 FND_MSG_PUB.initialize;
3990 END IF;
3991
3992 -- Initialize API return status to success
3993 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3994
3995 -- START OF BODY OF API
3996 l_INSTALLMENT_REC := P_INSTALLMENT_REC;
3997
3998 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
3999 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_INSTALLMENT_REC.LOAN_ID: ' || l_INSTALLMENT_REC.LOAN_ID);
4000 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_INSTALLMENT_REC.PAYMENT_NUMBER: ' || l_INSTALLMENT_REC.PAYMENT_NUMBER);
4001 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_INSTALLMENT_REC.DUE_DATE: ' || l_INSTALLMENT_REC.DUE_DATE);
4002 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_INSTALLMENT_REC.PRINCIPAL_AMOUNT: ' || l_INSTALLMENT_REC.PRINCIPAL_AMOUNT);
4003 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_INSTALLMENT_REC.INTEREST_AMOUNT: ' || l_INSTALLMENT_REC.INTEREST_AMOUNT);
4004 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_INSTALLMENT_REC.FEE_AMOUNT: ' || l_INSTALLMENT_REC.FEE_AMOUNT);
4005 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_INSTALLMENT_REC.OTHER_AMOUNT: ' || l_INSTALLMENT_REC.OTHER_AMOUNT);
4006 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_INSTALLMENT_REC.LOCK_PRIN: ' || l_INSTALLMENT_REC.LOCK_PRIN);
4007 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_INSTALLMENT_REC.LOCK_INT: ' || l_INSTALLMENT_REC.LOCK_INT);
4008
4009 if l_INSTALLMENT_REC.LOAN_ID is null then
4010 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
4011 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
4012 FND_MESSAGE.SET_TOKEN('VALUE', l_INSTALLMENT_REC.LOAN_ID);
4013 FND_MSG_PUB.ADD;
4014 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
4015 RAISE FND_API.G_EXC_ERROR;
4016 end if;
4017
4018 if l_INSTALLMENT_REC.PAYMENT_NUMBER is null then
4019 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
4020 FND_MESSAGE.SET_TOKEN('PARAMETER', 'PAYMENT_NUMBER');
4021 FND_MESSAGE.SET_TOKEN('VALUE', l_INSTALLMENT_REC.PAYMENT_NUMBER);
4022 FND_MSG_PUB.ADD;
4023 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
4024 RAISE FND_API.G_EXC_ERROR;
4025 end if;
4026
4027 if l_INSTALLMENT_REC.DUE_DATE is null then
4028 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
4029 FND_MESSAGE.SET_TOKEN('PARAMETER', 'DUE_DATE');
4030 FND_MESSAGE.SET_TOKEN('VALUE', l_INSTALLMENT_REC.DUE_DATE);
4031 FND_MSG_PUB.ADD;
4032 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
4033 RAISE FND_API.G_EXC_ERROR;
4034 end if;
4035
4036 open c_exist_installment(l_INSTALLMENT_REC.LOAN_ID, l_INSTALLMENT_REC.PAYMENT_NUMBER);
4037 fetch c_exist_installment into l_custom_sched_id;
4038 close c_exist_installment;
4039
4040 if l_custom_sched_id is not null then
4041 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Payment ' || l_INSTALLMENT_REC.PAYMENT_NUMBER || ' already exist in the custom schedule (l_custom_sched_id = ' || l_custom_sched_id || '). Returning.');
4042 return;
4043 end if;
4044
4045 l_loan_details := getLoanDetails(p_loan_id => l_INSTALLMENT_REC.LOAN_ID
4046 ,p_based_on_terms => 'CURRENT');
4047
4048 if l_loan_details.CUSTOM_SCHEDULE = 'N' then
4049 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Cannot add custom installment b/c schedule is not customized. Returning.');
4050 return;
4051 end if;
4052
4053 if l_INSTALLMENT_REC.PRINCIPAL_AMOUNT is null then
4054 l_INSTALLMENT_REC.PRINCIPAL_AMOUNT := 0;
4055 end if;
4056
4057 if l_INSTALLMENT_REC.INTEREST_AMOUNT is null then
4058 l_INSTALLMENT_REC.INTEREST_AMOUNT := 0;
4059 end if;
4060
4061 if l_INSTALLMENT_REC.FEE_AMOUNT is null then
4062 l_INSTALLMENT_REC.FEE_AMOUNT := 0;
4063 end if;
4064
4065 if l_INSTALLMENT_REC.OTHER_AMOUNT is null then
4066 l_INSTALLMENT_REC.OTHER_AMOUNT := 0;
4067 end if;
4068
4069 if l_INSTALLMENT_REC.LOCK_PRIN is null then
4070 l_INSTALLMENT_REC.LOCK_PRIN := 'Y';
4071 end if;
4072
4073 if l_INSTALLMENT_REC.LOCK_INT is null then
4074 l_INSTALLMENT_REC.LOCK_INT := 'Y';
4075 end if;
4076
4077 -- call api to insert new row
4078 lns_custom_pub.createCustomSched(P_CUSTOM_REC => l_INSTALLMENT_REC
4079 ,x_return_status => l_return_status
4080 ,x_custom_sched_id => l_custom_sched_id
4081 ,x_msg_count => l_msg_Count
4082 ,x_msg_data => l_msg_Data);
4083
4084 IF l_return_status <> 'S' THEN
4085 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Failed to insert custom schedule row');
4086 RAISE FND_API.G_EXC_ERROR;
4087 END IF;
4088
4089 if P_COMMIT = FND_API.G_TRUE then
4090 COMMIT WORK;
4091 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
4092 end if;
4093
4094 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Successfully added installment ' || l_INSTALLMENT_REC.PAYMENT_NUMBER || ' to custom schedule');
4095
4096 -- END OF BODY OF API
4097 x_return_status := FND_API.G_RET_STS_SUCCESS;
4098
4099 -- Standard call to get message count and if count is 1, get message info
4100 FND_MSG_PUB.Count_And_Get(
4101 p_encoded => FND_API.G_FALSE,
4102 p_count => x_msg_count,
4103 p_data => x_msg_data);
4104
4105 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
4106
4107 EXCEPTION
4108 WHEN FND_API.G_EXC_ERROR THEN
4109 ROLLBACK TO addMissingInstallment;
4110 x_return_status := FND_API.G_RET_STS_ERROR;
4111 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4112 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
4113 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4114 ROLLBACK TO addMissingInstallment;
4115 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4116 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4117 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
4118 WHEN OTHERS THEN
4119 ROLLBACK TO addMissingInstallment;
4120 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4121 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
4122 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
4123 END IF;
4124 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4125 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
4126 END;
4127
4128
4129 END LNS_CUSTOM_PUB;
4130