[Home] [Help]
PACKAGE BODY: APPS.LNS_CUSTOM_PUB
Source
1 PACKAGE BODY LNS_CUSTOM_PUB AS
2 /* $Header: LNS_CUST_PUBP_B.pls 120.34.12020000.2 2012/07/19 20:54:28 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 G_CUSTOM_TBL LNS_CUSTOM_PUB.CUSTOM_TBL;
8 G_AMORT_METHOD VARCHAR2(30);
9 g_COMBINE_INT_WITH_LAST_PRIN VARCHAR2(1);
10 g_CUSTOM_SCHED_DESC VARCHAR2(100);
11
12 ---------------------------------------------------------------------------
13 -- internal package routines
14 ---------------------------------------------------------------------------
15
16 procedure logMessage(log_level in number
17 ,module in varchar2
18 ,message in varchar2)
19 is
20
21 begin
22
23 IF log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
24 FND_LOG.STRING(log_level, module, message);
25 END IF;
26 /*
27 if FND_GLOBAL.Conc_Request_Id is not null then
28 fnd_file.put_line(FND_FILE.LOG, message);
29 end if;
30 */
31 end;
32
33 /* this funciton will ensure the rows in the custom tbl are ordered by payment number
34 || will NOT validate that payment numbers are unique. this should be done prior to sorting
35 */
36 procedure sortRows(p_custom_tbl in out nocopy LNS_CUSTOM_PUB.custom_tbl)
37
38 is
39 l_return_tbl LNS_CUSTOM_PUB.custom_tbl;
40 j number;
41 l_tmp_row lns_custom_pub.custom_sched_type;
42 l_number number;
43 l_min number;
44 l_tmp number;
45
46 begin
47 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, ' - sorting the rows');
48 for i in 1..p_custom_tbl.count loop
49 l_min := p_custom_tbl(i).payment_number;
50
51 for j in i + 1..p_custom_tbl.count loop
52
53 if p_custom_tbl(j).payment_number < l_min then
54 l_min := p_custom_tbl(j).payment_number;
55 l_tmp_row := p_custom_tbl(i);
56 p_custom_tbl(i) := p_custom_tbl(j);
57 p_custom_tbl(j) := l_tmp_row;
58 end if;
59 end loop;
60 end loop;
61 end ;
62
63 /*=========================================================================
64 || PUBLIC PROCEDURE resetCustomSchedule
65 ||
66 || DESCRIPTION
67 ||
68 || Overview: resets a customized payment schedule for a loan
69 ||
70 || Parameter: loan_id => loan id to reset
71 ||
72 || Return value: standard API outputs
73 ||
74 || Source Tables: NA
75 ||
76 || Target Tables: LNS_CUSTOM_PAYMENT_SCHEDULE, LNS_LOAN_HEADER
77 ||
78 || Return value:
79 ||
80 || KNOWN ISSUES
81 || you cannot reset a customized loan once billing begins
82 || NOTES
83 ||
84 || MODIFICATION HISTORY
85 || Date Author Description of Changes
86 || 12/08/2003 11:35AM raverma Created
87 ||
88 *=======================================================================*/
89 procedure resetCustomSchedule(p_loan_id IN number
90 ,p_init_msg_list IN VARCHAR2
91 ,p_commit IN VARCHAR2
92 ,p_update_header IN boolean
93 ,x_return_status OUT NOCOPY VARCHAR2
94 ,x_msg_count OUT NOCOPY NUMBER
95 ,x_msg_data OUT NOCOPY VARCHAR2)
96
97 is
98 l_api_name varchar2(25);
99 l_msg_count NUMBER;
100 l_msg_data VARCHAR2(2000);
101 l_return_Status VARCHAR2(1);
102 l_last_installment_billed NUMBER;
103 l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
104 l_customized varchar2(1);
105 l_loan_header_rec LNS_LOAN_HEADER_PUB.LOAN_HEADER_REC_TYPE;
106 l_object_version number;
107 l_skip_update boolean;
108
109 cursor c_customized (p_loan_id number) is
110 SELECT nvl(h.custom_payments_flag, 'N')
111 FROM lns_loan_headers_all h
112 WHERE loan_id = p_loan_id;
113
114 begin
115
116 -- Standard Start of API savepoint
117 SAVEPOINT resetCustomSchedule;
118 l_api_name := 'resetCustomSchedule';
119
120 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
121
122 -- Initialize message list IF p_init_msg_list is set to TRUE.
123 IF FND_API.to_Boolean( p_init_msg_list ) THEN
124 FND_MSG_PUB.initialize;
125 END IF;
126
127 -- Initialize API return status to SUCCESS
128 x_return_status := FND_API.G_RET_STS_SUCCESS;
129
130 -- --------------------------------------------------------------------
131 -- Api body
132 -- --------------------------------------------------------------------
133 -- validate loan_id
134 lns_utility_pub.validate_any_id(p_api_version => 1.0
135 ,p_init_msg_list => 'T'
136 ,x_msg_count => l_msg_count
137 ,x_msg_data => l_msg_data
138 ,x_return_status => l_return_status
139 ,p_col_id => p_loan_id
140 ,p_col_name => 'LOAN_ID'
141 ,p_table_name => 'LNS_LOAN_HEADERS_ALL');
142
143 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
144 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
145 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
146 FND_MESSAGE.SET_TOKEN('VALUE', p_loan_ID);
147 FND_MSG_PUB.ADD;
148 RAISE FND_API.G_EXC_ERROR;
149 end if;
150
151 -- check to see if the loan is customized
152 open c_customized(p_loan_id);
153 fetch c_customized into l_customized;
154 close c_customized;
155 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - customized = ' || l_customized);
156
157 if l_customized = 'N' then
158 l_skip_update := true;
159 /* dont raise this error as per karamach conversation 12-1-2004
160 FND_MESSAGE.Set_Name('LNS', 'LNS_NOT_CUSTOMIZED');
161 FND_MSG_PUB.Add;
162 RAISE FND_API.G_EXC_ERROR;
163 */
164 else
165 -- loan is customized
166 l_skip_update := false;
167
168 -- check to see if the loan has ever been billed
169 l_last_installment_billed := LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id);
170 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - last installment ' || l_last_installment_billed);
171
172 if l_last_installment_billed > 0 then
173 FND_MESSAGE.Set_Name('LNS', 'LNS_LOAN_ALREADY_BILLED');
174 FND_MSG_PUB.Add;
175 RAISE FND_API.G_EXC_ERROR;
176 end if;
177
178 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - deleting custom rows');
179 delete
180 from lns_custom_paymnt_scheds
181 where loan_id = p_loan_id;
182
183 end if;
184
185 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - resetting header');
186 if p_update_header and not l_skip_update then
187 select object_version_number into l_object_version
188 from lns_loan_headers_all
189 where loan_id = p_loan_id;
190 l_loan_header_rec.loan_id := p_loan_id;
191 l_loan_header_rec.custom_payments_flag := 'N';
192 lns_loan_header_pub.update_loan(p_init_msg_list => FND_API.G_FALSE
193 ,p_loan_header_rec => l_loan_header_rec
194 ,p_object_version_number => l_object_version
195 ,x_return_status => l_return_status
196 ,x_msg_count => l_msg_count
197 ,x_msg_data => l_msg_data);
198
199 else
200 null;
201 end if;
202 -- --------------------------------------------------------------------
203 -- End of API body
204 -- --------------------------------------------------------------------
205
206 -- Standard check for p_commit
207 IF FND_API.to_Boolean(p_commit) THEN
208 COMMIT WORK;
209 END IF;
210
211 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
212
213 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
214
215 EXCEPTION
216 WHEN FND_API.G_EXC_ERROR THEN
217 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
218 x_return_status := FND_API.G_RET_STS_ERROR;
219 x_msg_count := l_msg_count;
220 x_msg_data := l_msg_data;
221 ROLLBACK TO resetCustomSchedule;
222 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
223
224 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
225 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
226 x_return_status := FND_API.G_RET_STS_ERROR;
227 x_msg_count := l_msg_count;
228 x_msg_data := l_msg_data;
229 ROLLBACK TO resetCustomSchedule;
230 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
231
232 WHEN OTHERS THEN
233 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
234 x_return_status := FND_API.G_RET_STS_ERROR;
235 x_msg_count := l_msg_count;
236 x_msg_data := l_msg_data;
237 ROLLBACK TO resetCustomSchedule;
238 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
239
240 end resetCustomSchedule;
241
242 /*=========================================================================
243 || PUBLIC PROCEDURE createCustomSchedule
244 ||
245 || DESCRIPTION
246 ||
247 || Overview: creates a custom payment schedule for a loan
248 ||
249 || Parameter: loan_id => loan id to customize
250 || p_custom_tbl => table of records about custom schedule
251 ||
252 || Return value: standard API outputs
253 ||
254 || Source Tables: NA
255 ||
256 || Target Tables: LNS_CUSTOM_PAYMENT_SCHEDULE
257 ||
258 || Return value:
259 ||
260 || KNOWN ISSUES
261 ||
262 || NOTES
263 ||
264 || MODIFICATION HISTORY
265 || Date Author Description of Changes
266 || 12/08/2003 11:35AM raverma Created
267 ||
268 *=======================================================================*/
269 procedure createCustomSchedule(p_custom_tbl IN CUSTOM_TBL
270 ,p_loan_id IN number
271 ,p_init_msg_list IN VARCHAR2
272 ,p_commit IN VARCHAR2
273 ,x_return_status OUT NOCOPY VARCHAR2
274 ,x_msg_count OUT NOCOPY NUMBER
275 ,x_msg_data OUT NOCOPY VARCHAR2
276 ,X_INVALID_INSTALLMENT_NUM OUT NOCOPY NUMBER)
277
278 is
279 l_msg_count NUMBER;
280 l_msg_data VARCHAR2(2000);
281 l_return_Status VARCHAR2(1);
282 l_installment NUMBER;
283 l_custom_rec custom_sched_type;
284 l_custom_sched_id NUMBER;
285 m number;
286
287 l_loan_header_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type;
288 l_object_version number;
289 g_object_version number;
290 l_custom_tbl LNS_CUSTOM_PUB.CUSTOM_TBL;
291 l_api_name varchar2(25);
292 l_loan_start_date date;
293 l_original_loan_amount number;
294 l_fee_amount number;
295
296 -- for fees
297 l_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
298 l_orig_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
299 l_fees_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
300 l_orig_fees_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
301 l_fee_basis_tbl LNS_FEE_ENGINE.FEE_BASIS_TBL;
302
303 -- total fees on the schedule by installment
304 cursor c_fees(p_loan_id number, p_installment number) is
305 select nvl(sum(sched.fee_amount), 0)
306 from lns_fee_schedules sched
307 ,lns_fees struct
308 where sched.loan_id = p_loan_id
309 and sched.fee_id = struct.fee_id
310 and fee_installment = p_installment
311 and active_flag = 'Y';
312
313 cursor c_loan_details(p_loan_id number) is
314 select loan_start_date, funded_amount
315 from lns_loan_headers
316 where loan_id = p_loan_id;
317
318 begin
319 l_api_name := 'createCustomSchedule';
320 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
321 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - loan_id: ' || p_loan_id);
322 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - rows received: ' || p_custom_tbl.count);
323 SAVEPOINT createCustomSchedule;
324
325 -- Initialize message list IF p_init_msg_list is set to TRUE.
326 IF FND_API.to_Boolean( p_init_msg_list ) THEN
327 FND_MSG_PUB.initialize;
328 END IF;
329
330 -- Initialize API return status to SUCCESS
331 x_return_status := FND_API.G_RET_STS_SUCCESS;
332
333 --
334 -- Api body
335 --
336 --l_custom_tbl := p_custom_tbl;
337 m := 0;
338
339 for j in 1..p_custom_tbl.count loop
340 if p_custom_tbl(j).payment_number > 0 then
341 m := m + 1;
342 l_custom_tbl(m) := p_custom_tbl(j);
343 end if;
344 end loop;
345
346 lns_custom_pub.validateCustomTable(p_cust_tbl => l_custom_tbl
347 ,p_loan_id => p_loan_id
348 ,p_create_flag => true
349 ,x_installment => l_installment
350 ,x_return_status => l_return_status
351 ,x_msg_count => l_msg_count
352 ,x_msg_data => l_msg_data);
353 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - validateCustom ' || l_return_status);
354
355 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
356 if l_installment is not null then
357 X_INVALID_INSTALLMENT_NUM := l_installment;
358 FND_MESSAGE.Set_Name('LNS', 'LNS_INVALID_INSTALLMENT');
359 FND_MESSAGE.SET_TOKEN('PARAMETER', 'INSTALLMENT');
360 FND_MESSAGE.SET_TOKEN('VALUE', l_installment);
361 FND_MSG_PUB.Add;
362 RAISE FND_API.G_EXC_ERROR;
363 end if;
364 FND_MESSAGE.Set_Name('LNS', 'LNS_VALIDATE_CUSTOM_ERROR');
365 FND_MSG_PUB.Add;
366 RAISE FND_API.G_EXC_ERROR;
367 end if;
368
369 open c_loan_details(p_loan_id);
370 fetch c_loan_details into l_loan_start_date, l_original_loan_amount;
371 close c_loan_details;
372
373 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - getting fee structures');
374 -- now we've passed validation initialize loan_begin_balance to calculate balances
375 l_orig_fee_structures := lns_fee_engine.getFeeStructures(p_loan_id => p_loan_id
376 ,p_fee_category => 'EVENT'
377 ,p_fee_type => 'EVENT_ORIGINATION'
378 ,p_installment => null
379 ,p_phase => 'TERM'
380 ,p_fee_id => null);
381
382 l_fee_structures := lns_fee_engine.getFeeStructures(p_loan_id => p_loan_id
383 ,p_fee_category => 'RECUR'
384 ,p_fee_type => null
385 ,p_installment => null
386 ,p_phase => 'TERM'
387 ,p_fee_id => null);
388 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': fee structures count is ' || l_fee_structures.count);
389 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': orig structures count is ' || l_orig_fee_structures.count);
390
391 m := 0;
392 -- 2-24-2005 raverma add 0 installment to amortization schedule
393 if l_orig_fee_structures.count > 0 then
394
395 open c_fees(p_loan_id, 0);
396 fetch c_fees into l_fee_amount;
397 close c_fees;
398
399 if l_fee_amount > 0 then
400 m := l_custom_tbl.count + 1;
401 l_custom_rec.payment_number := 0;
402 l_custom_rec.due_date := l_loan_start_date;
403 l_custom_rec.principal_amount := 0;
404 l_custom_rec.interest_amount := 0;
405 l_custom_rec.fee_amount := l_fee_amount;
406 l_custom_rec.other_amount := 0;
407 l_custom_rec.installment_begin_balance := l_original_loan_amount;
408 l_custom_rec.installment_end_balance := l_original_loan_amount;
409 l_custom_rec.INTEREST_PAID_TODATE := 0;
410 l_custom_rec.PRINCIPAL_PAID_TODATE := 0;
411 --l_custom_rec.fees_cumulative := l_fee_amount;
412 --l_custom_rec.other_cumulative := 0;
413 -- add the record to the amortization table
414 l_custom_rec.CURRENT_TERM_PAYMENT := l_fee_amount;
415 --l_custom_tbl(m) := l_custom_rec;
416 end if;
417
418 --l_orig_fees_tbl.delete;
419 l_fee_amount := 0;
420
421 end if;
422 l_custom_tbl(1).installment_begin_balance := lns_financials.getRemainingBalance(p_loan_id);
423
424 for k in 1..l_custom_tbl.count
425 loop
426 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'custom_schedule_id: ' || l_custom_tbl(k).custom_schedule_id);
427 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'principal_amount : ' || l_custom_tbl(k).principal_amount);
428 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'interest_amount : ' || l_custom_tbl(k).interest_amount);
429 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'due_date : ' || l_custom_tbl(k).due_date);
430 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'object_version_number : ' || l_custom_tbl(k).object_version_number);
431 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'installment_begin_balance : ' || l_custom_tbl(k).installment_begin_balance);
432
433 l_fee_basis_tbl(1).fee_basis_name := 'TOTAL_BAL';
434 l_fee_basis_tbl(1).fee_basis_amount := l_custom_tbl(k).installment_begin_balance;
435 l_fee_basis_tbl(2).fee_basis_name := 'ORIG_LOAN';
436 l_fee_basis_tbl(2).fee_basis_amount := l_original_loan_amount;
437
438 if k = 1 then
439 if l_orig_fee_structures.count > 0 then
440 lns_fee_engine.calculateFees(p_loan_id => p_loan_id
441 ,p_installment => k
442 ,p_fee_basis_tbl => l_fee_basis_tbl
443 ,p_fee_structures => l_orig_fee_structures
444 ,x_fees_tbl => l_orig_fees_tbl
445 ,x_return_status => l_return_status
446 ,x_msg_count => l_msg_count
447 ,x_msg_data => l_msg_data);
448 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': calculated origination fees ' || l_orig_fee_structures.count);
449 end if;
450 end if;
451
452 if l_fee_structures.count > 0 then
453 lns_fee_engine.calculateFees(p_loan_id => p_loan_id
454 ,p_installment => k
455 ,p_fee_basis_tbl => l_fee_basis_tbl
456 ,p_fee_structures => l_fee_structures
457 ,x_fees_tbl => l_fees_tbl
458 ,x_return_status => l_return_status
459 ,x_msg_count => l_msg_count
460 ,x_msg_data => l_msg_data);
461 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': calculated fees ' || l_fees_tbl.count);
462
463 end if;
464
465 for i in 1..l_orig_fees_tbl.count loop
466 l_fee_amount := l_fee_amount + l_orig_fees_tbl(i).FEE_AMOUNT;
467 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': orig calculated fees ' || l_fee_amount);
468 end loop;
469
470 for j in 1..l_fees_tbl.count loop
471 l_fee_amount := l_fee_amount + l_fees_tbl(j).FEE_AMOUNT;
472 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': recurring calculated fees ' || l_fee_amount);
473 end loop;
474
475 l_custom_rec.LOAN_ID := p_loan_id;
476 l_custom_rec.PAYMENT_NUMBER := l_custom_tbl(k).payment_number;
477 l_custom_rec.PRINCIPAL_AMOUNT := l_custom_tbl(k).principal_amount;
478 l_custom_rec.INTEREST_AMOUNT := l_custom_tbl(k).interest_amount;
479 --l_custom_rec.FEE_AMOUNT := l_custom_tbl(k).fee_amount;
480 l_custom_rec.FEE_AMOUNT := l_fee_amount;
481 l_custom_rec.OTHER_AMOUNT := l_custom_tbl(k).other_amount;
482 l_custom_rec.DUE_DATE := l_custom_tbl(k).due_date;
483 l_custom_rec.current_term_payment := l_custom_rec.FEE_AMOUNT + l_custom_rec.INTEREST_AMOUNT + l_custom_rec.PRINCIPAL_AMOUNT;
484 --l_custom_rec.OBJECT_VERSION_NUMBER := p_custom_tbl(k).object_version_number;
485 l_custom_rec.installment_begin_balance := l_custom_tbl(k).installment_begin_balance;
486 l_custom_rec.installment_end_balance := l_custom_tbl(k).installment_begin_balance - l_custom_tbl(k).principal_amount;
487
488 -- now calculate the balances
489 if l_custom_rec.installment_end_balance > 0 and k <> l_custom_tbl.count then
490 l_custom_tbl(k + 1).installment_begin_balance := l_custom_rec.installment_end_balance;
491 end if;
492
493 -- call api to update rows one-by-one for compliance reasons
494 lns_custom_pub.createCustomSched(P_CUSTOM_REC => l_custom_rec
495 ,x_return_status => l_return_status
496 ,x_custom_sched_id => l_custom_sched_id
497 ,x_msg_count => l_msg_Count
498 ,x_msg_data => l_msg_Data);
499 -- dbms_output.put_line('after create API ' || l_return_status);
500
501 end loop;
502
503 -- if we get this far now we update the header table flag for custom payments
504 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - attempting to update header set custom = Y');
505 select object_version_number into l_object_version
506 from lns_loan_headers_all
507 where loan_id = p_loan_id;
508 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_object_version ' || l_object_version);
509
510 l_loan_header_rec.loan_id := p_loan_id;
511 l_loan_header_rec.CUSTOM_PAYMENTS_FLAG := 'Y';
512
513 lns_loan_header_pub.update_loan(p_init_msg_list => FND_API.G_FALSE
514 ,p_loan_header_rec => l_loan_header_rec
515 ,P_OBJECT_VERSION_NUMBER => l_object_version
516 ,X_RETURN_STATUS => l_return_status
517 ,X_MSG_COUNT => l_msg_count
518 ,X_MSG_DATA => l_msg_data);
519 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_msg_count ' || l_msg_count);
520 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_msg_data ' || l_msg_data);
521
522 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - header update set custom = Y');
523
524 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
525 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - header update ERROR');
526 FND_MESSAGE.Set_Name('LNS', 'LNS_HEADER_UPDATE_ERROR');
527 FND_MSG_PUB.Add;
528 RAISE FND_API.G_EXC_ERROR;
529 end if;
530
531 IF FND_API.to_Boolean(p_commit)
532 THEN
533 COMMIT WORK;
534 END IF;
535
536 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
537 p_data => x_msg_data);
538
539 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
540
541 EXCEPTION
542 WHEN FND_API.G_EXC_ERROR THEN
543 FND_MESSAGE.Set_Name('LNS', 'LNS_CREATE_CUSTOM_ERROR');
544 FND_MSG_PUB.Add;
545 x_return_status := FND_API.G_RET_STS_ERROR;
546 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
547 ROLLBACK TO createCustomSchedule;
548
549 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
550 FND_MESSAGE.Set_Name('LNS', 'LNS_CREATE_CUSTOM_ERROR');
551 FND_MSG_PUB.Add;
552 x_return_status := FND_API.G_RET_STS_ERROR;
553 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
554 ROLLBACK TO createCustomSchedule;
555
556 WHEN OTHERS THEN
557 FND_MESSAGE.Set_Name('LNS', 'LNS_CREATE_CUSTOM_ERROR');
558 FND_MSG_PUB.Add;
559 x_return_status := FND_API.G_RET_STS_ERROR;
560 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
561 ROLLBACK TO createCustomSchedule;
562
563 end;
564
565
566 /*=========================================================================
567 || PUBLIC PROCEDURE updateCustomSchedule
568 ||
569 || DESCRIPTION
570 ||
571 || Overview: updates a custom payment schedule for a loan
572 ||
573 || Parameter: loan_id => loan id to customize
574 || p_custom_tbl => table of records about custom schedule
575 ||
576 || Return value: standard API outputs
577 ||
578 || Source Tables: NA
579 ||
580 || Target Tables: LNS_CUSTOM_PAYMENT_SCHEDULE
581 ||
582 || Return value:
583 ||
584 || KNOWN ISSUES
585 ||
586 || NOTES
587 ||
588 || MODIFICATION HISTORY
589 || Date Author Description of Changes
590 || 12/08/2003 11:35AM raverma Created
591 ||
592 *=======================================================================*/
593 procedure updateCustomSchedule(p_custom_tbl IN CUSTOM_TBL
594 ,p_loan_id IN number
595 ,p_init_msg_list IN VARCHAR2
596 ,p_commit IN VARCHAR2
597 ,x_return_status OUT NOCOPY VARCHAR2
598 ,x_msg_count OUT NOCOPY NUMBER
599 ,x_msg_data OUT NOCOPY VARCHAR2
600 ,X_INVALID_INSTALLMENT_NUM OUT NOCOPY NUMBER)
601
602 is
603 l_msg_count NUMBER;
604 l_msg_data VARCHAR2(2000);
605 l_return_Status VARCHAR2(1);
606 l_installment NUMBER;
607 l_custom_rec custom_sched_type;
608 l_total_amount NUMBER;
609 l_custom_tbl LNS_CUSTOM_PUB.CUSTOM_TBL;
610 l_custom_tbl2 LNS_CUSTOM_PUB.CUSTOM_TBL;
611
612 l_api_name varchar2(25);
613
614
615 /* destroy records already billed
616 */
617 cursor c_records_to_destroy (p_loan_id number) is
618 select count(1)
619 from lns_amortization_scheds
620 where reamortization_amount is null
621 and reversed_flag <> 'Y'
622 and loan_id = p_loan_id
623 and payment_number > 0
624 and parent_amortization_id is null;
625
626 l_records_to_destroy number;
627 l_num_records number;
628 l_records_to_copy number;
629 l_count number;
630
631 -- we will need to get the PK since runAmortization API does not reutrn PKs
632 cursor c_cust_sched_id (p_loan_id number, p_payment_number number) is
633 select custom_schedule_id, object_version_number
634 from lns_custom_paymnt_scheds
635 where loan_id = p_loan_id
636 and payment_number = p_payment_number;
637
638 begin
639 l_api_name := 'updateCustomSchedule';
640 SAVEPOINT updateCustomSchedule;
641 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
642
643 -- Initialize message list IF p_init_msg_list is set to TRUE.
644 IF FND_API.to_Boolean( p_init_msg_list ) THEN
645 FND_MSG_PUB.initialize;
646 END IF;
647
648 -- Initialize API return status to SUCCESS
649 x_return_status := FND_API.G_RET_STS_SUCCESS;
650
651 --
652 -- Api body
653 --
654 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - loan_id: ' || p_loan_id);
655 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - rows received: ' || p_custom_tbl.count);
656 l_custom_tbl2 := p_custom_tbl;
657
658 open c_records_to_destroy(p_loan_id);
659 fetch c_records_to_destroy into l_records_to_destroy;
660 close c_records_to_destroy;
661 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - destroy to: ' || l_records_to_destroy);
662 -- also destroy the 0th row
663 l_count := 0;
664 for k in 1..l_custom_tbl2.count loop
665
666 if l_custom_tbl2(k).payment_number > l_records_to_destroy then
667 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' adding '|| l_custom_tbl2(k).payment_number);
668 l_count := l_count + 1;
669 l_custom_tbl(l_count) := l_custom_tbl2(k);
670 end if;
671 end loop;
672
673 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - after clean up records');
674 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - num records is '|| l_custom_tbl.count);
675
676 lns_custom_pub.validateCustomTable(p_cust_tbl => l_custom_tbl
677 ,p_loan_id => p_loan_id
678 ,p_create_flag => false
679 ,x_installment => l_installment
680 ,x_return_status => l_return_status
681 ,x_msg_count => l_msg_count
682 ,x_msg_data => l_msg_data);
683
684 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
685 if l_installment is not null then
686 X_INVALID_INSTALLMENT_NUM := l_installment;
687 FND_MESSAGE.Set_Name('LNS', 'LNS_INVALID_INSTALLMENT');
688 FND_MESSAGE.SET_TOKEN('PARAMETER', 'INSTALLMENT');
689 FND_MESSAGE.SET_TOKEN('VALUE', l_installment);
690 FND_MSG_PUB.Add;
691 RAISE FND_API.G_EXC_ERROR;
692 end if;
693 FND_MESSAGE.Set_Name('LNS', 'LNS_VALIDATE_CUSTOM_ERROR');
694 FND_MSG_PUB.Add;
695 RAISE FND_API.G_EXC_ERROR;
696 end if;
697
698 -- now we've passed validation initialize loan_begin_balance to calculate balances
699 l_custom_tbl2(1).installment_begin_balance := lns_financials.getRemainingBalance(p_loan_id);
700
701 for k in 1..l_custom_tbl.count
702 loop
703
704 open c_cust_sched_id(p_loan_id, l_custom_tbl(k).payment_number);
705 fetch c_cust_sched_id into
706 l_custom_tbl(k).custom_schedule_id
707 ,l_custom_tbl(k).object_version_number;
708 close c_cust_sched_id;
709
710 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'custom_schedule_id: ' || l_custom_tbl(k).custom_schedule_id);
711 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'principal_amount : ' || l_custom_tbl(k).principal_amount);
712 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'interest_amount : ' || l_custom_tbl(k).interest_amount);
713 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'due_date : ' || l_custom_tbl(k).due_date);
714 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'object_version_number : ' || l_custom_tbl(k).object_version_number);
715 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'installment_begin_balance : ' || l_custom_tbl(k).installment_begin_balance);
716
717 l_custom_rec.CUSTOM_SCHEDULE_ID := l_custom_tbl(k).custom_schedule_id;
718 l_custom_rec.LOAN_ID := p_loan_id;
719 --l_custom_rec.PAYMENT_NUMBER := k;
720 l_custom_rec.PRINCIPAL_AMOUNT := l_custom_tbl(k).principal_amount;
721 l_custom_rec.INTEREST_AMOUNT := l_custom_tbl(k).interest_amount;
722 l_custom_rec.FEE_AMOUNT := l_custom_tbl(k).fee_amount;
723 l_custom_rec.OTHER_AMOUNT := l_custom_tbl(k).other_amount;
724 l_custom_rec.DUE_DATE := l_custom_tbl(k).due_date;
725 l_custom_rec.current_term_payment := l_custom_rec.Fee_AMOUNT + l_custom_rec.INTEREST_AMOUNT + l_custom_rec.PRINCIPAL_AMOUNT;
726 l_custom_rec.OBJECT_VERSION_NUMBER := l_custom_tbl(k).object_version_number;
727 l_custom_rec.installment_begin_balance := l_custom_tbl(k).installment_begin_balance;
728 l_custom_rec.installment_end_balance := l_custom_tbl(k).installment_begin_balance - l_custom_tbl(k).principal_amount;
729
730 -- now calculate the balances
731 if l_custom_rec.installment_end_balance > 0 and k <> l_custom_tbl.count then
732 l_custom_tbl(k+1).installment_begin_balance := l_custom_rec.installment_end_balance;
733 end if;
734
735 -- call api to update rows one-by-one for compliance reasons
736 lns_custom_pub.updateCustomSched(P_CUSTOM_REC => l_custom_rec
737 ,x_return_status => l_return_status
738 ,x_msg_count => l_msg_Count
739 ,x_msg_data => l_msg_Data);
740
741 end loop;
742
743 IF FND_API.to_Boolean(p_commit)
744 THEN
745 COMMIT WORK;
746 END IF;
747
748 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
749 p_data => x_msg_data);
750 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
751
752 EXCEPTION
753 WHEN FND_API.G_EXC_ERROR THEN
754 --FND_MESSAGE.Set_Name('LNS', 'LNS_UPDATE_CUSTOM_ERROR');
755 --FND_MSG_PUB.Add;
756 --RAISE FND_API.G_EXC_ERROR;
757 x_return_status := FND_API.G_RET_STS_ERROR;
758 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
759 ROLLBACK TO updateCustomSchedule;
760
761 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
762 --FND_MESSAGE.Set_Name('LNS', 'LNS_UPDATE_CUSTOM_ERROR');
763 --FND_MSG_PUB.Add;
764 --RAISE FND_API.G_EXC_ERROR;
765 x_return_status := FND_API.G_RET_STS_ERROR;
766 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
767 ROLLBACK TO updateCustomSchedule;
768
769 WHEN OTHERS THEN
770 --FND_MESSAGE.Set_Name('LNS', 'LNS_UPDATE_CUSTOM_ERROR');
771 --FND_MSG_PUB.Add;
772 --RAISE FND_API.G_EXC_ERROR;
773 x_return_status := FND_API.G_RET_STS_ERROR;
774 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
775 ROLLBACK TO updateCustomSchedule;
776
777 end;
778
779
780 procedure validateCustomTable(p_cust_tbl in CUSTOM_TBL
781 ,p_loan_id in number
782 ,p_create_flag in boolean
783 ,x_installment OUT NOCOPY NUMBER
784 ,x_return_status OUT NOCOPY VARCHAR2
785 ,x_msg_count OUT NOCOPY NUMBER
786 ,x_msg_data OUT NOCOPY VARCHAR2)
787 Is
788 l_count number;
789 l_amount number;
790 l_msg_count NUMBER;
791 l_msg_data VARCHAR2(2000);
792 l_return_Status VARCHAR2(1);
793 l_date DATE;
794 l_api_name varchar2(35);
795 l_cust_tbl custom_tbl;
796 l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
797
798 Begin
799 l_api_name := 'validateCustomTable';
800 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Begin');
801 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'in table count is ' || p_cust_tbl.count);
802 -- check if number of incoming rows matches rows on loan_id custom_table
803 -- only if this is an UPDATE
804 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'validate customtbl begin');
805 l_cust_tbl := p_cust_tbl;
806
807 l_loan_details := lns_financials.getLoanDetails(p_loan_id => p_loan_id
808 ,p_based_on_terms => 'CURRENT'
809 ,p_phase => 'TERM');
810 l_count := 0;
811 l_amount := 0;
812 -- destroy any rows prior to the last billed installment
813 -- order the rows --
814 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'before sort');
815 sortRows(p_custom_tbl => l_cust_tbl);
816 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'after sort');
817
818 -- checking updateCustomSchedule first
819 if not p_create_flag then
820 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'in update validation');
821 -- here i need to know only the number of rows that have not been billed
822 Execute Immediate
823 ' Select count(1) ' ||
824 ' From lns_amortization_scheds ' ||
825 ' where loan_id = :p_loan_id ' ||
826 ' and reversed_flag <> ''Y'' ' ||
827 ' and reamortization_amount is null ' ||
828 ' and payment_number > 0 ' ||
829 ' and parent_amortization_id is null '
830 into l_count
831 using p_loan_id;
832
833 --open c_installments(p_loan_id);
834 --fetch c_installments into l_installments;
835 --close c_installments;
836
837 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'original installments ' || l_loan_details.number_installments);
838 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'billed installments (without 0) is ' || l_count);
839
840 if l_loan_details.number_installments - l_count <> l_cust_tbl.count then
841 FND_MESSAGE.Set_Name('LNS', 'LNS_INVALID_NUM_ROWS');
842 FND_MSG_PUB.Add;
843 RAISE FND_API.G_EXC_ERROR;
844 end if;
845 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'passed update validation');
846 end if;
847
848 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'create / update validation');
849 -- now checking each row in the createCustomSchedule
850 for i in 1..l_cust_tbl.count
851 loop
852 /* the begin balance for the first row does not incorporate unpaid billed
853 if i = 1 then
854 -- check that first row in custom table is = remainingBalance
855 -- CHECK THIS WITH KARTHIK
856 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'check balance');
857 if l_cust_tbl(1).installment_begin_balance <> lns_financials.getRemainingBalance(p_loan_id) then
858 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'balance incorrect');
859 FND_MESSAGE.Set_Name('LNS', 'LNS_BEGIN_BALANCE_INCORRECT');
860 FND_MSG_PUB.Add;
861 RAISE FND_API.G_EXC_ERROR;
862 end if;
863 end if;
864 */
865 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'validate custom row');
866 validateCustomRow(p_custom_rec => l_cust_tbl(i)
867 ,x_return_status => l_return_status
868 ,x_msg_count => l_msg_count
869 ,x_msg_data => l_msg_data);
870
871 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
872 x_installment := i;
873 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'invalid installment found #' || i);
874 FND_MESSAGE.Set_Name('LNS', 'LNS_INVALID_INSTALLMENT');
875 FND_MESSAGE.SET_TOKEN('PARAMETER', 'INSTALLMENT');
876 FND_MESSAGE.SET_TOKEN('VALUE', i);
877 FND_MSG_PUB.Add;
878 RAISE FND_API.G_EXC_ERROR;
879 end if;
880
881 -- check for consecutive installments
882 if l_cust_tbl.exists(i+1) then
883 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'checking consecutive installments' || l_cust_tbl(i).payment_number || ' ' || l_cust_tbl(i+1).payment_number );
884 if l_cust_tbl(i).payment_number + 1 <> l_cust_tbl(i+1).payment_number then
885 FND_MESSAGE.Set_Name('LNS', 'LNS_NONSEQUENTIAL_INSTALLMENTS');
886 FND_MSG_PUB.Add;
887 RAISE FND_API.G_EXC_ERROR;
888 end if;
889 end if;
890
891 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'date checking');
892 -- check for consecutive dates
893 if l_date is null then
894 l_date := l_cust_tbl(i).due_date;
895 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'date is null');
896 else
897
898 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'PASS: ' || i || 'p_cust_tbl(i).due_date is : ' || l_cust_tbl(i).due_date);
899 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'l_date is: ' || l_date);
900 if p_cust_tbl(i).due_date <= l_date then
901 FND_MESSAGE.Set_Name('LNS', 'LNS_NONSEQUENTIAL_DATES');
902 FND_MSG_PUB.Add;
903 RAISE FND_API.G_EXC_ERROR;
904 end if;
905 l_date := l_cust_tbl(i).due_date;
906
907 end if;
908
909 l_amount := l_amount + l_cust_tbl(i).principal_amount;
910 end loop;
911
912 -- check if SUM of Prinicipal_Amount is equal to the Funded_Amount
913 -- or requested_amount, etc... based on loan_Status
914 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'final balance check');
915
916 --karamach bug5231822 l_loan_details.unbilled_principal does not return correct value for Direct loan
917 --if l_amount <> l_loan_details.unbilled_principal then
918 if l_amount <> l_loan_details.remaining_balance then
919 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'balance check incorrect');
920 FND_MESSAGE.Set_Name('LNS', 'LNS_BALANCE_INCORRECT');
921 FND_MSG_PUB.Add;
922 RAISE FND_API.G_EXC_ERROR;
923 end if;
924
925 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'after final balance check');
926 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
927
928 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
929
930 EXCEPTION
931
932 WHEN FND_API.G_EXC_ERROR THEN
933 --FND_MESSAGE.Set_Name('LNS', 'LNS_VALIDATE_CUSTOM_ERROR');
934 --FND_MSG_PUB.Add;
935 x_return_status := FND_API.G_RET_STS_ERROR;
936 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
937
938 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
939 --FND_MESSAGE.Set_Name('LNS', 'LNS_VALIDATE_CUSTOM_ERROR');
940 --FND_MSG_PUB.Add;
941 x_return_status := FND_API.G_RET_STS_ERROR;
942 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
943
944 WHEN OTHERS THEN
945 --FND_MESSAGE.Set_Name('LNS', 'LNS_VALIDATE_CUSTOM_ERROR');
946 --FND_MSG_PUB.Add;
947 x_return_status := FND_API.G_RET_STS_ERROR;
948 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
949 End;
950
951 /* procedure to validate a row in the LNS_CUSTOM_PAYMENT_SCHEDULE
952 ||
953 ||
954 ||
955 ||
956 || */
957 procedure validateCustomRow(p_custom_rec in CUSTOM_SCHED_TYPE
958 ,x_return_status OUT NOCOPY VARCHAR2
959 ,x_msg_count OUT NOCOPY NUMBER
960 ,x_msg_data OUT NOCOPY VARCHAR2)
961 is
962 l_msg_count NUMBER;
963 l_msg_data VARCHAR2(2000);
964 l_return_Status VARCHAR2(1);
965 l_api_name varchar2(30);
966
967 BEGIN
968
969 x_return_status := FND_API.G_RET_STS_SUCCESS;
970 l_api_name := 'validateCustRow';
971 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' validate One Row');
972 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' ' || p_custom_rec.PAYMENT_NUMBER );
973 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' ' || p_custom_rec.DUE_DATE );
974 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' ' || p_custom_rec.PRINCIPAL_AMOUNT );
975 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' ' || p_custom_rec.INTEREST_AMOUNT );
976
977 if p_custom_rec.due_Date is null then
978 FND_MESSAGE.Set_Name('LNS', 'LNS_NO_DUE_DATE');
979 FND_MSG_PUB.Add;
980 RAISE FND_API.G_EXC_ERROR;
981
982 elsif p_custom_rec.payment_number is null or p_custom_rec.payment_number < 1 then
983 FND_MESSAGE.Set_Name('LNS', 'LNS_INVALID_PAYMENT_NUMBER');
984 FND_MSG_PUB.Add;
985 RAISE FND_API.G_EXC_ERROR;
986
987 elsif p_custom_rec.PRINCIPAL_AMOUNT is not null and p_custom_rec.PRINCIPAL_AMOUNT < 0 then
988 FND_MESSAGE.Set_Name('LNS', 'LNS_PRINICIPAL_AMOUNT_ERROR');
989 FND_MSG_PUB.Add;
990 RAISE FND_API.G_EXC_ERROR;
991
992 elsif p_custom_rec.INTEREST_AMOUNT is not null and p_custom_rec.INTEREST_AMOUNT < 0 then
993 FND_MESSAGE.Set_Name('LNS', 'LNS_INTEREST_AMOUNT_ERROR');
994 FND_MSG_PUB.Add;
995 RAISE FND_API.G_EXC_ERROR;
996
997 elsif p_custom_rec.FEE_AMOUNT is not null and p_custom_rec.FEE_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 elsif p_custom_rec.OTHER_AMOUNT is not null and p_custom_rec.OTHER_AMOUNT < 0 then
1003 FND_MESSAGE.Set_Name('LNS', 'LNS_OTHER_AMOUNT_ERROR');
1004 FND_MSG_PUB.Add;
1005 RAISE FND_API.G_EXC_ERROR;
1006
1007 end if;
1008
1009 EXCEPTION
1010
1011 WHEN FND_API.G_EXC_ERROR THEN
1012 x_return_status := FND_API.G_RET_STS_ERROR;
1013 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1014
1015 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1016 x_return_status := FND_API.G_RET_STS_ERROR;
1017 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1018
1019 WHEN OTHERS THEN
1020 x_return_status := FND_API.G_RET_STS_ERROR;
1021 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1022
1023 END validateCustomRow;
1024
1025 procedure createCustomSched(P_CUSTOM_REC IN CUSTOM_SCHED_TYPE
1026 ,x_custom_sched_id OUT NOCOPY NUMBER
1027 ,x_return_status OUT NOCOPY VARCHAR2
1028 ,x_msg_count OUT NOCOPY NUMBER
1029 ,x_msg_data OUT NOCOPY VARCHAR2)
1030 is
1031 l_msg_count NUMBER;
1032 l_msg_data VARCHAR2(2000);
1033 l_return_Status VARCHAR2(1);
1034 l_custom_id NUMBER;
1035 l_api_name varchar2(25);
1036
1037 BEGIN
1038 l_api_name := 'createCustomSched';
1039 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1040
1041 -- dbms_output.put_line('createCustomAPI' );
1042 LNS_CUSTOM_PAYMNT_SCHEDS_PKG.INSERT_ROW(x_custom_schedule_id => l_custom_id
1043 ,P_LOAN_ID => P_CUSTOM_REC.LOAN_ID
1044 ,P_PAYMENT_NUMBER => P_CUSTOM_REC.PAYMENT_NUMBER
1045 ,P_DUE_DATE => P_CUSTOM_REC.DUE_DATE
1046 ,P_PRINCIPAL_AMOUNT => P_CUSTOM_REC.PRINCIPAL_AMOUNT
1047 ,P_INTEREST_AMOUNT => P_CUSTOM_REC.INTEREST_AMOUNT
1048 -- ,P_PRINCIPAL_BALANCE => P_CUSTOM_REC.PRINCIPAL_BALANCE
1049 ,P_FEE_AMOUNT => P_CUSTOM_REC.FEE_AMOUNT
1050 ,P_OTHER_AMOUNT => P_CUSTOM_REC.OTHER_AMOUNT
1051 ,p_INSTALLMENT_BEGIN_BALANCE => P_CUSTOM_REC.INSTALLMENT_BEGIN_BALANCE
1052 ,p_INSTALLMENT_END_BALANCE => P_CUSTOM_REC.INSTALLMENT_END_BALANCE
1053 ,p_CURRENT_TERM_PAYMENT => P_CUSTOM_REC.CURRENT_TERM_PAYMENT
1054 ,p_OBJECT_VERSION_NUMBER => 1
1055 ,p_ATTRIBUTE_CATEGORY => P_CUSTOM_REC.ATTRIBUTE_CATEGORY
1056 ,p_ATTRIBUTE1 => P_CUSTOM_REC.ATTRIBUTE1
1057 ,p_ATTRIBUTE2 => P_CUSTOM_REC.ATTRIBUTE2
1058 ,p_ATTRIBUTE3 => P_CUSTOM_REC.ATTRIBUTE3
1059 ,p_ATTRIBUTE4 => P_CUSTOM_REC.ATTRIBUTE4
1060 ,p_ATTRIBUTE5 => P_CUSTOM_REC.ATTRIBUTE5
1061 ,p_ATTRIBUTE6 => P_CUSTOM_REC.ATTRIBUTE6
1062 ,p_ATTRIBUTE7 => P_CUSTOM_REC.ATTRIBUTE7
1063 ,p_ATTRIBUTE8 => P_CUSTOM_REC.ATTRIBUTE8
1064 ,p_ATTRIBUTE9 => P_CUSTOM_REC.ATTRIBUTE9
1065 ,p_ATTRIBUTE10 => P_CUSTOM_REC.ATTRIBUTE10
1066 ,p_ATTRIBUTE11 => P_CUSTOM_REC.ATTRIBUTE11
1067 ,p_ATTRIBUTE12 => P_CUSTOM_REC.ATTRIBUTE12
1068 ,p_ATTRIBUTE13 => P_CUSTOM_REC.ATTRIBUTE13
1069 ,p_ATTRIBUTE14 => P_CUSTOM_REC.ATTRIBUTE14
1070 ,p_ATTRIBUTE15 => P_CUSTOM_REC.ATTRIBUTE15
1071 ,p_ATTRIBUTE16 => P_CUSTOM_REC.ATTRIBUTE16
1072 ,p_ATTRIBUTE17 => P_CUSTOM_REC.ATTRIBUTE17
1073 ,p_ATTRIBUTE18 => P_CUSTOM_REC.ATTRIBUTE18
1074 ,p_ATTRIBUTE19 => P_CUSTOM_REC.ATTRIBUTE19
1075 ,p_ATTRIBUTE20 => P_CUSTOM_REC.ATTRIBUTE20
1076 ,p_LOCK_PRIN => P_CUSTOM_REC.LOCK_PRIN
1077 ,p_LOCK_INT => P_CUSTOM_REC.LOCK_INT
1078 ,p_PRINCIPAL_PERCENT => P_CUSTOM_REC.PRINCIPAL_PERCENT);
1079
1080 x_custom_sched_id := l_custom_id;
1081 x_return_status := FND_API.G_RET_STS_SUCCESS;
1082
1083 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In createCustomSched: After call Insert_Row ID' || l_Custom_id );
1084
1085 END createCustomSched;
1086
1087 procedure updateCustomSched(P_CUSTOM_REC IN CUSTOM_SCHED_TYPE
1088 ,x_return_status OUT NOCOPY VARCHAR2
1089 ,x_msg_count OUT NOCOPY NUMBER
1090 ,x_msg_data OUT NOCOPY VARCHAR2)
1091 is
1092 l_msg_count NUMBER;
1093 l_msg_data VARCHAR2(2000);
1094 l_return_Status VARCHAR2(1);
1095 l_object_version NUMBER;
1096
1097 l_api_name varchar2(25);
1098
1099 BEGIN
1100 l_api_name := 'updateCustomSched';
1101 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1102
1103 LNS_UTILITY_PUB.GETOBJECTVERSION(P_TABLE_NAME => 'LNS_CUSTOM_PAYMNT_SCHEDS'
1104 ,P_PRIMARY_KEY_NAME => 'CUSTOM_SCHEDULE_ID'
1105 ,P_PRIMARY_KEY_VALUE => P_CUSTOM_REC.CUSTOM_SCHEDULE_ID
1106 ,P_OBJECT_VERSION_NUMBER => P_CUSTOM_REC.OBJECT_VERSION_NUMBER
1107 ,X_OBJECT_VERSION_NUMBER => l_object_version
1108 ,X_MSG_COUNT => l_msg_count
1109 ,X_MSG_DATA => l_msg_data
1110 ,X_RETURN_STATUS => l_return_status);
1111
1112 LNS_CUSTOM_PAYMNT_SCHEDS_PKG.Update_Row(p_CUSTOM_SCHEDULE_ID => P_CUSTOM_REC.CUSTOM_SCHEDULE_ID
1113 ,p_LOAN_ID => P_CUSTOM_REC.LOAN_ID
1114 ,p_PAYMENT_NUMBER => P_CUSTOM_REC.PAYMENT_NUMBER
1115 ,p_DUE_DATE => P_CUSTOM_REC.DUE_DATE
1116 ,p_PRINCIPAL_AMOUNT => P_CUSTOM_REC.PRINCIPAL_AMOUNT
1117 ,p_INTEREST_AMOUNT => P_CUSTOM_REC.INTEREST_AMOUNT
1118 -- ,p_PRINCIPAL_BALANCE => P_CUSTOM_REC.PRINCIPAL_BALANCE
1119 ,p_FEE_AMOUNT => P_CUSTOM_REC.FEE_AMOUNT
1120 ,p_OTHER_AMOUNT => P_CUSTOM_REC.OTHER_AMOUNT
1121 ,p_INSTALLMENT_BEGIN_BALANCE => P_CUSTOM_REC.INSTALLMENT_BEGIN_BALANCE
1122 ,p_INSTALLMENT_END_BALANCE => P_CUSTOM_REC.INSTALLMENT_END_BALANCE
1123 ,p_CURRENT_TERM_PAYMENT => P_CUSTOM_REC.CURRENT_TERM_PAYMENT
1124 ,p_OBJECT_VERSION_NUMBER => l_object_version
1125 ,p_ATTRIBUTE_CATEGORY => P_CUSTOM_REC.ATTRIBUTE_CATEGORY
1126 ,p_ATTRIBUTE1 => P_CUSTOM_REC.ATTRIBUTE1
1127 ,p_ATTRIBUTE2 => P_CUSTOM_REC.ATTRIBUTE2
1128 ,p_ATTRIBUTE3 => P_CUSTOM_REC.ATTRIBUTE3
1129 ,p_ATTRIBUTE4 => P_CUSTOM_REC.ATTRIBUTE4
1130 ,p_ATTRIBUTE5 => P_CUSTOM_REC.ATTRIBUTE5
1131 ,p_ATTRIBUTE6 => P_CUSTOM_REC.ATTRIBUTE6
1132 ,p_ATTRIBUTE7 => P_CUSTOM_REC.ATTRIBUTE7
1133 ,p_ATTRIBUTE8 => P_CUSTOM_REC.ATTRIBUTE8
1134 ,p_ATTRIBUTE9 => P_CUSTOM_REC.ATTRIBUTE9
1135 ,p_ATTRIBUTE10 => P_CUSTOM_REC.ATTRIBUTE10
1136 ,p_ATTRIBUTE11 => P_CUSTOM_REC.ATTRIBUTE11
1137 ,p_ATTRIBUTE12 => P_CUSTOM_REC.ATTRIBUTE12
1138 ,p_ATTRIBUTE13 => P_CUSTOM_REC.ATTRIBUTE13
1139 ,p_ATTRIBUTE14 => P_CUSTOM_REC.ATTRIBUTE14
1140 ,p_ATTRIBUTE15 => P_CUSTOM_REC.ATTRIBUTE15
1141 ,p_ATTRIBUTE16 => P_CUSTOM_REC.ATTRIBUTE16
1142 ,p_ATTRIBUTE17 => P_CUSTOM_REC.ATTRIBUTE17
1143 ,p_ATTRIBUTE18 => P_CUSTOM_REC.ATTRIBUTE18
1144 ,p_ATTRIBUTE19 => P_CUSTOM_REC.ATTRIBUTE19
1145 ,p_ATTRIBUTE20 => P_CUSTOM_REC.ATTRIBUTE20
1146 ,p_LOCK_PRIN => P_CUSTOM_REC.LOCK_PRIN
1147 ,p_LOCK_INT => P_CUSTOM_REC.LOCK_INT
1148 ,p_PRINCIPAL_PERCENT => P_CUSTOM_REC.PRINCIPAL_PERCENT);
1149
1150 x_return_status := FND_API.G_RET_STS_SUCCESS;
1151
1152 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In updateCustomSched: After call Insert_Row');
1153
1154 END updateCustomSched;
1155
1156
1157
1158
1159 /*
1160 This procedure ensures the rows in the custom tbl are ordered by due date.
1161 Validates that due dates are unique
1162 */
1163 procedure sortRowsByDate(p_custom_tbl in out nocopy LNS_CUSTOM_PUB.custom_tbl)
1164
1165 is
1166 l_custom_tbl LNS_CUSTOM_PUB.custom_tbl;
1167 i number;
1168 j number;
1169 l_temp LNS_CUSTOM_PUB.custom_sched_type;
1170 l_exist boolean;
1171
1172 begin
1173 l_custom_tbl := p_custom_tbl;
1174
1175 -- sort table by due_date
1176 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Sorting by due date...');
1177 for i in REVERSE 1..l_custom_tbl.count loop
1178 for j in 1..(i-1) loop
1179 if l_custom_tbl(j).DUE_DATE > l_custom_tbl(j+1).DUE_DATE then
1180 l_temp := l_custom_tbl(j);
1181 l_custom_tbl(j) := l_custom_tbl(j+1);
1182 l_custom_tbl(j+1) := l_temp;
1183 elsif l_custom_tbl(j).DUE_DATE = l_custom_tbl(j+1).DUE_DATE then
1184 if l_custom_tbl(j).LOCK_PRIN = 'N' or l_custom_tbl(j+1).LOCK_PRIN = 'N' then
1185 l_custom_tbl(j).LOCK_PRIN := 'N';
1186 l_custom_tbl(j).PRINCIPAL_AMOUNT := 0;
1187 l_custom_tbl(j).PRINCIPAL_PERCENT := null;
1188 end if;
1189 if l_custom_tbl(j).LOCK_INT = 'N' or l_custom_tbl(j+1).LOCK_INT = 'N' then
1190 l_custom_tbl(j).LOCK_INT := 'N';
1191 l_custom_tbl(j).INTEREST_AMOUNT := 0;
1192 end if;
1193 /*
1194 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Several installments have the same due date.');
1195 FND_MESSAGE.SET_NAME('LNS', 'LNS_DUE_DATE_DUPL');
1196 FND_MSG_PUB.Add;
1197 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1198 RAISE FND_API.G_EXC_ERROR;
1199 */
1200 end if;
1201 end loop;
1202 end loop;
1203
1204 p_custom_tbl.delete;
1205 for i in 1..l_custom_tbl.count loop
1206 l_exist := false;
1207 for j in 1..p_custom_tbl.count loop
1208 if l_custom_tbl(i).DUE_DATE = p_custom_tbl(j).DUE_DATE then
1209 l_exist := true;
1210 exit;
1211 end if;
1212 end loop;
1213
1214 if l_exist = false then
1215 p_custom_tbl(p_custom_tbl.count+1) := l_custom_tbl(i);
1216 end if;
1217 end loop;
1218
1219 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done.');
1220
1221 --p_custom_tbl := l_custom_tbl;
1222 end;
1223
1224
1225
1226
1227 /*
1228 This procedure filters the custom tbl from deleted rows
1229 */
1230 procedure filterCustSchedule(p_custom_tbl in out nocopy LNS_CUSTOM_PUB.custom_tbl)
1231
1232 is
1233 l_custom_tbl LNS_CUSTOM_PUB.custom_tbl;
1234 l_new_custom_tbl LNS_CUSTOM_PUB.custom_tbl;
1235 i number;
1236 j number;
1237
1238 begin
1239 l_custom_tbl := p_custom_tbl;
1240 j := 0;
1241
1242 -- filtering table from deleted rows
1243 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Filtering...');
1244 for i in 1..l_custom_tbl.count loop
1245 if l_custom_tbl(i).ACTION is null or l_custom_tbl(i).ACTION <> 'D' then
1246 j := j + 1;
1247 l_new_custom_tbl(j) := l_custom_tbl(i);
1248 end if;
1249 end loop;
1250 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done.');
1251
1252 p_custom_tbl := l_new_custom_tbl;
1253 end;
1254
1255
1256
1257 /*
1258 This procedure resolves relative dates and set actual due dates for all rows
1259 */
1260 procedure resolveRelativeDates(p_loan_details in LNS_CUSTOM_PUB.LOAN_DETAILS_REC,
1261 p_custom_tbl in out nocopy LNS_CUSTOM_PUB.custom_tbl)
1262
1263 is
1264 l_custom_tbl LNS_CUSTOM_PUB.custom_tbl;
1265 l_new_custom_tbl LNS_CUSTOM_PUB.custom_tbl;
1266 l_last_due_date date;
1267 l_term number;
1268 l_period varchar2(30);
1269 i number;
1270 l_count number;
1271 l_filtered boolean;
1272
1273 begin
1274 l_custom_tbl := p_custom_tbl;
1275
1276 l_count := 0;
1277 l_filtered := false;
1278
1279 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Resolving relative dates...');
1280 for i in 1..l_custom_tbl.count loop
1281
1282 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, i);
1283 if i = 1 then
1284 if p_loan_details.LAST_DUE_DATE is not null then
1285 l_last_due_date := p_loan_details.LAST_DUE_DATE;
1286 else
1287 l_last_due_date := p_loan_details.LOAN_START_DATE;
1288 end if;
1289 else
1290 l_last_due_date := l_custom_tbl(i-1).DUE_DATE;
1291 end if;
1292 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_last_due_date = ' || l_last_due_date);
1293
1294 if l_custom_tbl(i).RELATIVE_DATE is not null then
1295
1296 BEGIN
1297 --l_custom_tbl(i).RELATIVE_DATE := trim(' ' from l_custom_tbl(i).RELATIVE_DATE);
1298 --l_period := substr(l_custom_tbl(i).RELATIVE_DATE, -1, 1);
1299 --l_term := substr(l_custom_tbl(i).RELATIVE_DATE, 1, (length(l_custom_tbl(i).RELATIVE_DATE)-1));
1300
1301 l_custom_tbl(i).RELATIVE_DATE := REPLACE(l_custom_tbl(i).RELATIVE_DATE, 'LAST_DUE_DATE',
1302 'to_date(''' || to_char(l_last_due_date, 'DD-MM-YYYY') || ''', ''DD-MM-YYYY'')');
1303 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'RELATIVE_DATE1 = ' || l_custom_tbl(i).RELATIVE_DATE);
1304 l_custom_tbl(i).RELATIVE_DATE := REPLACE(l_custom_tbl(i).RELATIVE_DATE, 'LOAN_START_DATE',
1305 'to_date(''' || to_char(p_loan_details.LOAN_START_DATE, 'DD-MM-YYYY') || ''', ''DD-MM-YYYY'')');
1306 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'RELATIVE_DATE2 = ' || l_custom_tbl(i).RELATIVE_DATE);
1307 l_custom_tbl(i).RELATIVE_DATE := REPLACE(l_custom_tbl(i).RELATIVE_DATE, 'LOAN_MATURITY_DATE',
1308 'to_date(''' || to_char(p_loan_details.MATURITY_DATE, 'DD-MM-YYYY') || ''', ''DD-MM-YYYY'')');
1309 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'RELATIVE_DATE3 = ' || l_custom_tbl(i).RELATIVE_DATE);
1310
1311 Execute Immediate 'select ' || l_custom_tbl(i).RELATIVE_DATE || ' from dual' into l_custom_tbl(i).DUE_DATE;
1312
1313 EXCEPTION
1314 WHEN OTHERS THEN
1315 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'ERROR: ' || sqlerrm);
1316 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
1317 FND_MESSAGE.SET_TOKEN('PARAMETER', 'RELATIVE_DATE');
1318 FND_MESSAGE.SET_TOKEN('VALUE', l_custom_tbl(i).RELATIVE_DATE);
1319 FND_MSG_PUB.ADD;
1320 RAISE FND_API.G_EXC_ERROR;
1321 END;
1322 /*
1323 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'RELATIVE_DATE = ' || l_custom_tbl(i).RELATIVE_DATE);
1324 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_term = ' || l_term);
1325 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_period = ' || l_period);
1326 if l_period = 'D' then
1327 l_custom_tbl(i).DUE_DATE := l_last_due_date + l_term;
1328 elsif l_period = 'W' then
1329 l_custom_tbl(i).DUE_DATE := l_last_due_date + (l_term*7);
1330 elsif l_period = 'M' then
1331 l_custom_tbl(i).DUE_DATE := add_months(l_last_due_date, l_term);
1332 elsif l_period = 'Y' then
1333 l_custom_tbl(i).DUE_DATE := l_last_due_date + NUMTOYMINTERVAL(l_term,'YEAR');
1334 else
1335 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
1336 FND_MESSAGE.SET_TOKEN('PARAMETER', 'RELATIVE_DATE');
1337 FND_MESSAGE.SET_TOKEN('VALUE', l_custom_tbl(i).RELATIVE_DATE);
1338 FND_MSG_PUB.ADD;
1339 RAISE FND_API.G_EXC_ERROR;
1340 end if;
1341 */
1342 end if;
1343 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DUE_DATE = ' || l_custom_tbl(i).DUE_DATE);
1344
1345 l_custom_tbl(i).RELATIVE_DATE := null;
1346
1347 if l_custom_tbl(i).DUE_DATE is null then
1348 FND_MESSAGE.Set_Name('LNS', 'LNS_NO_DUE_DATE');
1349 FND_MSG_PUB.Add;
1350 RAISE FND_API.G_EXC_ERROR;
1351 end if;
1352
1353 if l_custom_tbl(i).DUE_DATE < p_loan_details.loan_start_date or
1354 (p_loan_details.LAST_DUE_DATE is not null and l_custom_tbl(i).DUE_DATE <= p_loan_details.LAST_DUE_DATE) or
1355 l_custom_tbl(i).DUE_DATE > p_loan_details.maturity_Date
1356 then
1357 l_filtered := true;
1358 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DUE_DATE is invalid');
1359 else
1360 l_last_due_date := l_custom_tbl(i).DUE_DATE;
1361 l_count := l_count + 1;
1362 l_new_custom_tbl(l_count) := l_custom_tbl(i);
1363 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Adding row to output table');
1364 end if;
1365
1366 end loop;
1367 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done.');
1368
1369 if l_filtered then
1370 FND_MESSAGE.SET_NAME('LNS', 'LNS_CUST_INVALID_DUEDATE_WARN');
1371 FND_MSG_PUB.ADD_DETAIL(p_message_type => FND_MSG_PUB.G_WARNING_MSG);
1372 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1373 end if;
1374
1375 p_custom_tbl := l_new_custom_tbl;
1376 end;
1377
1378
1379
1380 /*
1381 This procedure synchs rate schedule with new number of installments
1382 */
1383 procedure synchRateSchedule(p_term_id in number, p_num_installments in number)
1384
1385 is
1386
1387 /*-----------------------------------------------------------------------+
1388 | Local Variable Declarations and initializations |
1389 +-----------------------------------------------------------------------*/
1390
1391 l_RATE_ID number;
1392 l_RATE number;
1393 l_BEGIN_INSTALLMENT number;
1394 l_END_INSTALLMENT number;
1395 i number;
1396
1397 /*-----------------------------------------------------------------------+
1398 | Cursor Declarations |
1399 +-----------------------------------------------------------------------*/
1400
1401 -- cursor to load rate schedule
1402 cursor c_rate_sched(p_term_id NUMBER) IS
1403 select RATE_ID, CURRENT_INTEREST_RATE, BEGIN_INSTALLMENT_NUMBER, END_INSTALLMENT_NUMBER
1404 from lns_rate_schedules
1405 where term_id = p_term_id and
1406 END_DATE_ACTIVE is null and
1407 nvl(PHASE, 'TERM') = 'TERM'
1408 order by END_INSTALLMENT_NUMBER desc;
1409
1410 begin
1411
1412 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Synching rate schedule...');
1413 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_term_id: ' || p_term_id);
1414 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_num_installments: ' || p_num_installments);
1415
1416 -- finding right rate row and update it
1417 OPEN c_rate_sched(p_term_id);
1418 LOOP
1419 i := i + 1;
1420 FETCH c_rate_sched INTO
1421 l_RATE_ID,
1422 l_RATE,
1423 l_BEGIN_INSTALLMENT,
1424 l_END_INSTALLMENT;
1425
1426 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, i || ') Rate ' || l_RATE || ': ' || l_BEGIN_INSTALLMENT || ' - ' || l_END_INSTALLMENT);
1427
1428 if p_num_installments > l_END_INSTALLMENT then
1429
1430 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating this row - set END_INSTALLMENT_NUMBER = ' || p_num_installments);
1431
1432 update lns_rate_schedules
1433 set END_INSTALLMENT_NUMBER = p_num_installments
1434 where term_id = p_term_id and
1435 RATE_ID = l_RATE_ID;
1436
1437 exit;
1438
1439 elsif p_num_installments >= l_BEGIN_INSTALLMENT and p_num_installments <= l_END_INSTALLMENT then
1440
1441 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating this row - set END_INSTALLMENT_NUMBER = ' || p_num_installments);
1442
1443 update lns_rate_schedules
1444 set END_INSTALLMENT_NUMBER = p_num_installments
1445 where term_id = p_term_id and
1446 RATE_ID = l_RATE_ID;
1447
1448 exit;
1449
1450 elsif p_num_installments < l_BEGIN_INSTALLMENT then
1451
1452 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Deleting this row');
1453
1454 delete from lns_rate_schedules
1455 where term_id = p_term_id and
1456 RATE_ID = l_RATE_ID;
1457
1458 end if;
1459
1460 END LOOP;
1461
1462 CLOSE c_rate_sched;
1463 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done synching');
1464
1465 end;
1466
1467
1468
1469 /*
1470 This procedure synchs rate schedule with new number of installments in memory only, no changes to db
1471 */
1472 procedure synchRateSchedule(p_rate_tbl IN OUT NOCOPY LNS_FINANCIALS.RATE_SCHEDULE_TBL, p_num_installments in number)
1473
1474 is
1475
1476 /*-----------------------------------------------------------------------+
1477 | Local Variable Declarations and initializations |
1478 +-----------------------------------------------------------------------*/
1479
1480 l_RATE_ID number;
1481 l_RATE number;
1482 l_BEGIN_INSTALLMENT number;
1483 l_END_INSTALLMENT number;
1484 i number;
1485 l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
1486
1487 /*-----------------------------------------------------------------------+
1488 | Cursor Declarations |
1489 +-----------------------------------------------------------------------*/
1490
1491 begin
1492
1493 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Synching rate schedule...');
1494 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_num_installments: ' || p_num_installments);
1495
1496 l_rate_tbl := p_rate_tbl;
1497
1498 -- finding right rate row and update it
1499 for i in REVERSE 1..l_rate_tbl.count loop
1500
1501 l_RATE := l_rate_tbl(i).ANNUAL_RATE;
1502 l_BEGIN_INSTALLMENT := l_rate_tbl(i).BEGIN_INSTALLMENT_NUMBER;
1503 l_END_INSTALLMENT := l_rate_tbl(i).END_INSTALLMENT_NUMBER;
1504
1505 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, i || ') Rate ' || l_RATE || ': ' || l_BEGIN_INSTALLMENT || ' - ' || l_END_INSTALLMENT);
1506
1507 if p_num_installments > l_END_INSTALLMENT then
1508
1509 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating this row - set END_INSTALLMENT_NUMBER = ' || p_num_installments);
1510 l_rate_tbl(i).END_INSTALLMENT_NUMBER := p_num_installments;
1511
1512 exit;
1513
1514 elsif p_num_installments >= l_BEGIN_INSTALLMENT and p_num_installments <= l_END_INSTALLMENT then
1515
1516 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating this row - set END_INSTALLMENT_NUMBER = ' || p_num_installments);
1517 l_rate_tbl(i).END_INSTALLMENT_NUMBER := p_num_installments;
1518
1519 exit;
1520
1521 elsif p_num_installments < l_BEGIN_INSTALLMENT then
1522
1523 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Deleting this row');
1524 l_rate_tbl.delete(i);
1525
1526 end if;
1527
1528 END LOOP;
1529
1530 p_rate_tbl := l_rate_tbl;
1531 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done synching');
1532
1533 end;
1534
1535
1536
1537 -- This function returns payment schedule record
1538 -- introduced for bug 7319358
1539 function getPayment(P_LOAN_ID IN NUMBER, P_PAYMENT_NUMBER IN NUMBER) return LNS_FIN_UTILS.PAYMENT_SCHEDULE
1540 IS
1541
1542 /*-----------------------------------------------------------------------+
1543 | Local Variable Declarations and initializations |
1544 +-----------------------------------------------------------------------*/
1545
1546 l_api_name CONSTANT VARCHAR2(30) := 'getPayment';
1547 l_payment_schedule LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
1548 l_payment LNS_FIN_UTILS.PAYMENT_SCHEDULE;
1549 i number;
1550
1551 /*-----------------------------------------------------------------------+
1552 | Cursor Declarations |
1553 +-----------------------------------------------------------------------*/
1554
1555 BEGIN
1556
1557 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
1558 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
1559 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID: ' || P_LOAN_ID);
1560 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_PAYMENT_NUMBER: ' || P_PAYMENT_NUMBER);
1561
1562 l_payment_schedule := buildCustomPaySchedule(P_LOAN_ID);
1563 for i in 1..l_payment_schedule.count loop
1564 if P_PAYMENT_NUMBER = i then
1565 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Found payment ' || P_PAYMENT_NUMBER);
1566 l_payment := l_payment_schedule(i);
1567 exit;
1568 end if;
1569 end loop;
1570
1571 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
1572
1573 return l_payment;
1574 END;
1575
1576
1577
1578 function getLoanDetails(p_loan_id in number
1579 ,p_based_on_terms in varchar2) return LNS_CUSTOM_PUB.LOAN_DETAILS_REC
1580
1581 is
1582
1583 /*-----------------------------------------------------------------------+
1584 | Local Variable Declarations and initializations |
1585 +-----------------------------------------------------------------------*/
1586
1587 l_api_name CONSTANT VARCHAR2(30) := 'getLoanDetails';
1588 l_loan_Details LNS_CUSTOM_PUB.LOAN_DETAILS_REC;
1589 l_billed_principal number;
1590
1591 /*-----------------------------------------------------------------------+
1592 | Cursor Declarations |
1593 +-----------------------------------------------------------------------*/
1594 CURSOR c_loan_details(p_Loan_id NUMBER, p_based_on_terms varchar2) IS
1595 SELECT h.loan_id
1596 ,t.amortization_frequency
1597 ,t.loan_payment_frequency
1598 ,trunc(h.loan_start_date)
1599 ,h.funded_amount
1600 ,h.requested_amount
1601 ,lns_financials.getRemainingBalance(p_loan_id)
1602 ,trunc(h.loan_maturity_date)
1603 ,decode(p_based_on_terms, 'CURRENT', LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_loan_id, 'TERM'), 0)
1604 -- ,decode(p_based_on_terms, 'CURRENT', LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER_EXT(p_loan_id), -1)
1605 ,decode(nvl(t.day_count_method, 'PERIODIC30_360'), 'PERIODIC30_360', '30/360', t.day_count_method)
1606 ,nvl(h.custom_payments_flag, 'N')
1607 ,h.loan_status
1608 ,h.loan_currency
1609 ,curr.precision
1610 -- ,nvl(t.PAYMENT_CALC_METHOD, 'EQUAL_PAYMENT')
1611 ,decode(p_based_on_terms,
1612 'CURRENT', decode(nvl(h.custom_payments_flag, 'N'), 'Y', nvl(t.PAYMENT_CALC_METHOD, 'CUSTOM'),
1613 'N', nvl(t.PAYMENT_CALC_METHOD, 'EQUAL_PAYMENT')),
1614 decode(nvl(h.custom_payments_flag, 'N'), 'Y', nvl(t.ORIG_PAY_CALC_METHOD, 'CUSTOM'),
1615 'N', nvl(t.PAYMENT_CALC_METHOD, 'EQUAL_PAYMENT'))
1616 )
1617 ,t.CALCULATION_METHOD
1618 ,t.INTEREST_COMPOUNDING_FREQ
1619 ,nvl(t.CUSTOM_CALC_METHOD, 'NONE')
1620 -- ,nvl(t.CUSTOM_CALC_METHOD, decode(nvl(t.PAYMENT_CALC_METHOD, 'EQUAL_PAYMENT'), 'EQUAL_PAYMENT', 'EQUAL_PAYMENT',
1621 -- 'EQUAL_PAYMENT_STANDARD', 'EQUAL_PAYMENT', 'EQUAL_PRINCIPAL', 'EQUAL_PRINCIPAL', 'SEPARATE_SCHEDULES', 'EQUAL_PRINCIPAL'))
1622 ,t.ORIG_PAY_CALC_METHOD
1623 ,t.RATE_TYPE RATE_TYPE
1624 ,t.CEILING_RATE TERM_CEILING_RATE
1625 ,t.FLOOR_RATE TERM_FLOOR_RATE
1626 ,t.PERCENT_INCREASE TERM_PERCENT_INCREASE
1627 ,t.PERCENT_INCREASE_LIFE TERM_PERCENT_INCREASE_LIFE
1628 ,t.FIRST_PERCENT_INCREASE TERM_FIRST_PERCENT_INCREASE
1629 ,t.INDEX_RATE_ID TERM_INDEX_RATE_ID
1630 ,t.TERM_PROJECTED_RATE INITIAL_INTEREST_RATE
1631 ,nvl(lns_fin_utils.getActiveRate(h.loan_id), t.TERM_PROJECTED_RATE) LAST_INTEREST_RATE
1632 ,nvl(t.FIRST_RATE_CHANGE_DATE, t.NEXT_RATE_CHANGE_DATE) FIRST_RATE_CHANGE_DATE
1633 ,t.NEXT_RATE_CHANGE_DATE NEXT_RATE_CHANGE_DATE
1634 ,t.TERM_PROJECTED_RATE TERM_PROJECTED_RATE
1635 ,nvl(t.PENAL_INT_RATE, 0)
1636 ,nvl(t.PENAL_INT_GRACE_DAYS, 0)
1637 ,nvl(t.REAMORTIZE_ON_FUNDING, 'REST')
1638 ,nvl(h.ADD_REQUESTED_AMOUNT, 0)
1639 ,CUSTOM_SCHED_DESC
1640 ,nvl(t.CAPITALIZE_INT, 'N')
1641 FROM lns_loan_headers_all h
1642 ,lns_terms t
1643 ,fnd_currencies curr
1644 WHERE h.loan_id = p_loan_id
1645 AND h.loan_id = t.loan_id
1646 AND curr.currency_code = h.loan_currency;
1647
1648 cursor c_balanceInfo(p_loan_id NUMBER, p_phase varchar2) IS
1649 select nvl(sum(amort.PRINCIPAL_AMOUNT),0) -- billed principal
1650 ,nvl(sum(amort.PRINCIPAL_REMAINING),0) -- unpaid principal
1651 ,nvl(sum(amort.INTEREST_REMAINING),0) -- unpaid interest
1652 from LNS_AM_SCHEDS_V amort
1653 where amort.Loan_id = p_loan_id
1654 and amort.REVERSED_CODE = 'N'
1655 and amort.phase = p_phase;
1656
1657 -- cursor to get last bill due date
1658 cursor c_due_date(p_loan_id NUMBER) IS
1659 select trunc(max(DUE_DATE))
1660 from lns_amortization_scheds
1661 where loan_id = p_loan_id
1662 and (REVERSED_FLAG is null or REVERSED_FLAG = 'N')
1663 and parent_amortization_id is null
1664 and REAMORTIZATION_AMOUNT is null
1665 and nvl(phase, 'TERM') = 'TERM';
1666
1667 begin
1668
1669 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || '+');
1670 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Input:');
1671 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'p_loan_id: ' || p_loan_id);
1672 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'p_based_on_terms: ' || p_based_on_terms);
1673
1674 OPEN c_loan_details(p_loan_id, p_based_on_terms);
1675 FETCH c_loan_details INTO
1676 l_loan_details.loan_id
1677 ,l_loan_Details.amortization_frequency
1678 ,l_loan_Details.payment_frequency
1679 ,l_loan_Details.loan_start_date
1680 ,l_loan_details.funded_amount
1681 ,l_loan_details.requested_amount
1682 ,l_loan_details.remaining_balance
1683 ,l_loan_details.maturity_Date
1684 ,l_loan_details.last_installment_billed
1685 ,l_loan_details.day_count_method
1686 ,l_loan_details.custom_schedule
1687 ,l_loan_details.loan_status
1688 ,l_loan_details.loan_currency
1689 ,l_loan_details.currency_precision
1690 ,l_loan_details.PAYMENT_CALC_METHOD
1691 ,l_loan_details.CALCULATION_METHOD
1692 ,l_loan_details.INTEREST_COMPOUNDING_FREQ
1693 ,l_loan_details.CUSTOM_CALC_METHOD
1694 ,l_loan_details.ORIG_PAY_CALC_METHOD
1695 ,l_loan_details.RATE_TYPE -- fixed or variable
1696 ,l_loan_details.TERM_CEILING_RATE -- term ceiling rate
1697 ,l_loan_details.TERM_FLOOR_RATE -- term floor rate
1698 ,l_loan_details.TERM_ADJ_PERCENT_INCREASE -- term percentage increase btwn adjustments
1699 ,l_loan_details.TERM_LIFE_PERCENT_INCREASE -- term lifetime max adjustment for interest
1700 ,l_loan_details.TERM_FIRST_PERCENT_INCREASE -- term first percentage increase
1701 ,l_loan_details.TERM_INDEX_RATE_ID
1702 ,l_loan_details.INITIAL_INTEREST_RATE -- current phase only
1703 ,l_loan_details.LAST_INTEREST_RATE -- current phase only
1704 ,l_loan_details.FIRST_RATE_CHANGE_DATE -- current phase only
1705 ,l_loan_details.NEXT_RATE_CHANGE_DATE -- current phase only
1706 ,l_loan_details.TERM_PROJECTED_INTEREST_RATE -- term projected interest rate
1707 ,l_loan_details.PENAL_INT_RATE
1708 ,l_loan_details.PENAL_INT_GRACE_DAYS
1709 ,l_loan_details.REAMORTIZE_ON_FUNDING
1710 ,l_loan_details.ADD_REQUESTED_AMOUNT
1711 ,l_loan_details.CUSTOM_SCHED_DESC
1712 ,l_loan_details.CAPITALIZE_INT;
1713 close c_loan_details;
1714
1715 -- use this part of the procedure to differentiate between
1716 -- elements that are calculated differently for current and original
1717 -- amortization
1718 if p_based_on_terms = 'CURRENT' then
1719
1720 Begin
1721 -- get balance information
1722 open c_balanceInfo(p_loan_id, 'TERM');
1723 fetch c_balanceInfo into
1724 l_billed_principal
1725 ,l_loan_details.unpaid_principal
1726 ,l_loan_details.UNPAID_INTEREST;
1727 close c_balanceInfo;
1728 l_loan_details.billed_principal := l_billed_principal;
1729 l_loan_details.unbilled_principal := l_loan_details.funded_amount - l_billed_principal;
1730 Exception
1731 when no_data_found then
1732 l_loan_details.unpaid_principal := 0;
1733 l_loan_details.billed_principal := 0;
1734 l_loan_details.unbilled_principal := l_loan_details.funded_amount;
1735 l_loan_details.UNPAID_INTEREST := 0;
1736 End;
1737
1738 -- get last due date
1739 Begin
1740 open c_due_date(p_loan_id);
1741 fetch c_due_date into l_loan_details.LAST_DUE_DATE;
1742 close c_due_date;
1743 Exception
1744 when no_data_found then
1745 l_loan_details.LAST_DUE_DATE := null;
1746 End;
1747
1748 else
1749 l_loan_details.unpaid_principal := 0;
1750 l_loan_details.billed_principal := 0;
1751 l_loan_details.unbilled_principal := l_loan_details.funded_amount;
1752 l_loan_details.UNPAID_INTEREST := 0;
1753 l_loan_details.LAST_DUE_DATE := null;
1754 end if;
1755
1756 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Loan details:');
1757 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' custom_schedule: ' || l_loan_details.custom_schedule);
1758 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' amortization_frequency: ' || l_loan_details.amortization_frequency);
1759 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' payment_frequency: ' || l_loan_details.payment_frequency);
1760 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' loan start date: ' || l_loan_details.loan_start_date);
1761 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' funded_amount: ' || l_loan_details.funded_amount);
1762 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' remaining balance: ' || l_loan_details.remaining_balance);
1763 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' maturity_date: ' || l_loan_details.maturity_Date);
1764 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' last installment billed: ' || l_loan_details.last_installment_billed);
1765 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' day Count method: ' || l_loan_details.day_count_method);
1766 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' loan_status: ' || l_loan_details.loan_status);
1767 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' loan_currency: ' || l_loan_details.loan_currency);
1768 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' currency_precision: ' || l_loan_details.currency_precision);
1769 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' PAYMENT_CALC_METHOD: ' || l_loan_details.PAYMENT_CALC_METHOD);
1770 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' CALCULATION_METHOD: ' || l_loan_details.CALCULATION_METHOD);
1771 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' INTEREST_COMPOUNDING_FREQ: ' || l_loan_details.INTEREST_COMPOUNDING_FREQ);
1772 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' unpaid_principal: ' || l_loan_details.unpaid_principal);
1773 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' unbilled_principal: ' || l_loan_details.unbilled_principal);
1774 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' UNPAID_INTEREST: ' || l_loan_details.UNPAID_INTEREST);
1775 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' LAST_DUE_DATE: ' || l_loan_details.LAST_DUE_DATE);
1776 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' CUSTOM_CALC_METHOD: ' || l_loan_details.CUSTOM_CALC_METHOD);
1777 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' ORIG_PAY_CALC_METHOD: ' || l_loan_details.ORIG_PAY_CALC_METHOD);
1778 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || '-');
1779
1780 return l_loan_details;
1781
1782 Exception
1783 When No_Data_Found then
1784 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'LOAN ID: ' || p_loan_id || ' not found');
1785 FND_MESSAGE.Set_Name('LNS', 'LNS_INVALID_LOAN_ID');
1786 FND_MSG_PUB.Add;
1787 RAISE FND_API.G_EXC_ERROR;
1788
1789 When Others then
1790 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Err: ' || sqlerrm);
1791 RAISE FND_API.G_EXC_ERROR;
1792
1793 end getLoanDetails;
1794
1795
1796
1797 -- This procedure loads custom schedule from db
1798 procedure loadCustomSchedule(
1799 P_API_VERSION IN NUMBER,
1800 P_INIT_MSG_LIST IN VARCHAR2,
1801 P_COMMIT IN VARCHAR2,
1802 P_VALIDATION_LEVEL IN NUMBER,
1803 P_LOAN_ID IN NUMBER,
1804 P_BASED_ON_TERMS IN VARCHAR2,
1805 X_CUSTOM_SET_REC OUT NOCOPY LNS_CUSTOM_PUB.custom_settings_type,
1806 X_CUSTOM_TBL OUT NOCOPY LNS_CUSTOM_PUB.CUSTOM_TBL,
1807 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1808 X_MSG_COUNT OUT NOCOPY NUMBER,
1809 X_MSG_DATA OUT NOCOPY VARCHAR2)
1810 IS
1811
1812 /*-----------------------------------------------------------------------+
1813 | Local Variable Declarations and initializations |
1814 +-----------------------------------------------------------------------*/
1815
1816 l_api_name CONSTANT VARCHAR2(30) := 'loadCustomSchedule';
1817 l_api_version CONSTANT NUMBER := 1.0;
1818 l_return_status VARCHAR2(1);
1819 l_msg_count NUMBER;
1820 l_msg_data VARCHAR2(32767);
1821
1822 l_loan_details LNS_CUSTOM_PUB.LOAN_DETAILS_REC;
1823 l_custom_tbl LNS_CUSTOM_PUB.CUSTOM_TBL;
1824 l_CUSTOM_SET_REC LNS_CUSTOM_PUB.custom_settings_type;
1825 l_temp_row LNS_CUSTOM_PUB.custom_sched_type;
1826 l_amort_tbl LNS_FINANCIALS.AMORTIZATION_TBL;
1827 i number;
1828 j number;
1829
1830 /*-----------------------------------------------------------------------+
1831 | Cursor Declarations |
1832 +-----------------------------------------------------------------------*/
1833
1834 -- cursor to load custom schedule
1835 -- fix for bug 7026226: default PRINCIPAL_AMOUNT and INTEREST_AMOUNT to 0 if they are null
1836 cursor c_load_sched(p_loan_id NUMBER, p_begin_installment NUMBER) IS
1837 select
1838 CUSTOM_SCHEDULE_ID,
1839 LOAN_ID,
1840 PAYMENT_NUMBER,
1841 DUE_DATE,
1842 nvl(PRINCIPAL_AMOUNT, 0),
1843 PRINCIPAL_PERCENT,
1844 nvl(INTEREST_AMOUNT, 0),
1845 nvl(FEE_AMOUNT, 0),
1846 nvl(OTHER_AMOUNT, 0),
1847 nvl(LOCK_PRIN, 'Y'),
1848 nvl(LOCK_INT, 'Y')
1849 from LNS_CUSTOM_PAYMNT_SCHEDS
1850 where loan_id = p_loan_id
1851 and PAYMENT_NUMBER > p_begin_installment
1852 order by PAYMENT_NUMBER;
1853
1854 BEGIN
1855
1856 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
1857
1858 -- Standard start of API savepoint
1859 SAVEPOINT loadCustomSchedule;
1860 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Savepoint is established');
1861
1862 -- Standard call to check for call compatibility
1863 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1864 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1865 END IF;
1866
1867 -- Initialize message list if p_init_msg_list is set to TRUE
1868 IF FND_API.To_Boolean(p_init_msg_list) THEN
1869 FND_MSG_PUB.initialize;
1870 END IF;
1871
1872 -- Initialize API return status to success
1873 l_return_status := FND_API.G_RET_STS_SUCCESS;
1874
1875 -- START OF BODY OF API
1876
1877 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
1878 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID: ' || P_LOAN_ID);
1879 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_BASED_ON_TERMS: ' || P_BASED_ON_TERMS);
1880
1881 if P_LOAN_ID is null then
1882 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
1883 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
1884 FND_MESSAGE.SET_TOKEN('VALUE', P_LOAN_ID);
1885 FND_MSG_PUB.ADD;
1886 RAISE FND_API.G_EXC_ERROR;
1887 end if;
1888
1889 if P_BASED_ON_TERMS is null then
1890 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
1891 FND_MESSAGE.SET_TOKEN('PARAMETER', 'BASED_ON_TERMS');
1892 FND_MESSAGE.SET_TOKEN('VALUE', P_BASED_ON_TERMS);
1893 FND_MSG_PUB.ADD;
1894 RAISE FND_API.G_EXC_ERROR;
1895 end if;
1896
1897 l_loan_details := getLoanDetails(p_loan_Id => p_loan_id
1898 ,p_based_on_terms => p_based_on_terms);
1899
1900 if (l_loan_details.CUSTOM_SCHEDULE = 'N' or
1901 (l_loan_details.CUSTOM_SCHEDULE = 'Y' and l_loan_details.loan_status <> 'INCOMPLETE' and
1902 p_based_on_terms <> 'CURRENT' and l_loan_details.ORIG_PAY_CALC_METHOD is not null))
1903 then
1904
1905 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_FINANCIALS.runAmortization...');
1906 LNS_FINANCIALS.runAmortization(
1907 P_API_VERSION => 1.0,
1908 P_INIT_MSG_LIST => FND_API.G_TRUE,
1909 P_COMMIT => FND_API.G_FALSE,
1910 P_LOAN_ID => P_LOAN_ID,
1911 P_BASED_ON_TERMS => P_BASED_ON_TERMS,
1912 x_amort_tbl => l_amort_tbl,
1913 x_return_status => l_return_status,
1914 x_msg_count => l_msg_count,
1915 x_msg_data => l_msg_data);
1916
1917 IF l_return_status <> 'S' THEN
1918 RAISE FND_API.G_EXC_ERROR;
1919 END IF;
1920
1921 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Schedule from LNS_FINANCIALS.runAmortization:');
1922 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PN DD PRIN LP INT LI FEE OTH ID');
1923 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, '--- -------- ------- -- ------- -- ----- ----- ------');
1924
1925 j := 0;
1926 for i in 1..l_amort_tbl.count loop
1927
1928 if l_amort_tbl(i).INSTALLMENT_NUMBER > 0 then -- excluding 0-th installment from customization
1929 j := j + 1;
1930 l_custom_tbl(j).LOAN_ID := P_LOAN_ID;
1931 l_custom_tbl(j).PAYMENT_NUMBER := l_amort_tbl(i).INSTALLMENT_NUMBER;
1932 l_custom_tbl(j).DUE_DATE := l_amort_tbl(i).DUE_DATE;
1933 l_custom_tbl(j).PRINCIPAL_AMOUNT := l_amort_tbl(i).PRINCIPAL_AMOUNT;
1934
1935 if l_custom_tbl(j).PRINCIPAL_AMOUNT = 0 then
1936 l_custom_tbl(j).LOCK_PRIN := 'Y';
1937 else
1938 l_custom_tbl(j).LOCK_PRIN := 'N';
1939 end if;
1940
1941 l_custom_tbl(j).INTEREST_AMOUNT := l_amort_tbl(i).INTEREST_AMOUNT;
1942
1943 if l_amort_tbl(i).INTEREST_AMOUNT = 0 and l_amort_tbl(i).INTEREST_RATE <> 0 and l_amort_tbl(i).CURR_CAP_INT_AMOUNT = 0 then
1944 l_custom_tbl(j).LOCK_INT := 'D';
1945 elsif i < l_amort_tbl.count and l_amort_tbl(i).CURR_CAP_INT_AMOUNT > 0 then
1946 l_custom_tbl(j).LOCK_INT := 'C';
1947 else
1948 l_custom_tbl(j).LOCK_INT := 'N';
1949 end if;
1950
1951 l_custom_tbl(j).FEE_AMOUNT := l_amort_tbl(i).FEE_AMOUNT;
1952 l_custom_tbl(j).OTHER_AMOUNT := l_amort_tbl(i).OTHER_AMOUNT;
1953 l_custom_tbl(j).ACTION := 'I';
1954
1955 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME,
1956 l_custom_tbl(j).PAYMENT_NUMBER || ' ' ||
1957 l_custom_tbl(j).DUE_DATE || ' ' ||
1958 l_custom_tbl(j).PRINCIPAL_AMOUNT || ' ' ||
1959 l_custom_tbl(j).LOCK_PRIN || ' ' ||
1960 l_custom_tbl(j).INTEREST_AMOUNT || ' ' ||
1961 l_custom_tbl(j).LOCK_INT || ' ' ||
1962 l_custom_tbl(j).FEE_AMOUNT || ' ' ||
1963 l_custom_tbl(j).OTHER_AMOUNT || ' ' ||
1964 l_custom_tbl(j).CUSTOM_SCHEDULE_ID);
1965 end if;
1966
1967 end loop;
1968
1969 if l_loan_details.PAYMENT_CALC_METHOD = 'SEPARATE_SCHEDULES' then
1970 l_CUSTOM_SET_REC.AMORT_METHOD := 'EQUAL_PRINCIPAL';
1971 else
1972 l_CUSTOM_SET_REC.AMORT_METHOD := l_loan_details.PAYMENT_CALC_METHOD;
1973 end if;
1974 l_CUSTOM_SET_REC.DESCRIPTION := null;
1975
1976 else
1977
1978 i := 0;
1979 OPEN c_load_sched(p_loan_id, l_loan_details.LAST_INSTALLMENT_BILLED);
1980
1981 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Loading custom schedule from LNS_CUSTOM_PAYMNT_SCHEDS:');
1982 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PN DD PRIN(%) LP INT LI FEE OTH ID');
1983 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, '--- -------- ------- -- ------- -- ----- ----- ------');
1984
1985 LOOP
1986
1987 FETCH c_load_sched INTO
1988 l_temp_row.CUSTOM_SCHEDULE_ID,
1989 l_temp_row.LOAN_ID,
1990 l_temp_row.PAYMENT_NUMBER,
1991 l_temp_row.DUE_DATE,
1992 l_temp_row.PRINCIPAL_AMOUNT,
1993 l_temp_row.PRINCIPAL_PERCENT,
1994 l_temp_row.INTEREST_AMOUNT,
1995 l_temp_row.FEE_AMOUNT,
1996 l_temp_row.OTHER_AMOUNT,
1997 l_temp_row.LOCK_PRIN,
1998 l_temp_row.LOCK_INT;
1999 exit when c_load_sched%NOTFOUND;
2000
2001 i := i + 1;
2002 l_custom_tbl(i) := l_temp_row;
2003
2004 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME,
2005 l_custom_tbl(i).PAYMENT_NUMBER || ' ' ||
2006 l_custom_tbl(i).DUE_DATE || ' ' ||
2007 l_custom_tbl(i).PRINCIPAL_AMOUNT || '(' || l_custom_tbl(i).PRINCIPAL_PERCENT || '%) ' ||
2008 l_custom_tbl(i).LOCK_PRIN || ' ' ||
2009 l_custom_tbl(i).INTEREST_AMOUNT || ' ' ||
2010 l_custom_tbl(i).LOCK_INT || ' ' ||
2011 l_custom_tbl(i).FEE_AMOUNT || ' ' ||
2012 l_custom_tbl(i).OTHER_AMOUNT || ' ' ||
2013 l_custom_tbl(i).CUSTOM_SCHEDULE_ID);
2014
2015 END LOOP;
2016 CLOSE c_load_sched;
2017
2018 l_CUSTOM_SET_REC.AMORT_METHOD := l_loan_details.CUSTOM_CALC_METHOD;
2019 l_CUSTOM_SET_REC.DESCRIPTION := l_loan_details.CUSTOM_SCHED_DESC;
2020
2021 end if;
2022
2023 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Recalculating schedule...');
2024 LNS_CUSTOM_PUB.recalcCustomSchedule(
2025 P_API_VERSION => 1.0,
2026 P_INIT_MSG_LIST => FND_API.G_TRUE,
2027 P_COMMIT => FND_API.G_FALSE,
2028 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
2029 P_LOAN_ID => P_LOAN_ID,
2030 P_BASED_ON_TERMS => P_BASED_ON_TERMS,
2031 P_USE_RETAINED_DATA => 'N',
2032 P_CUSTOM_SET_REC => l_CUSTOM_SET_REC,
2033 P_CUSTOM_TBL => l_custom_tbl,
2034 x_return_status => l_return_status,
2035 x_msg_count => l_msg_count,
2036 x_msg_data => l_msg_data);
2037
2038 IF l_return_status <> 'S' THEN
2039 RAISE FND_API.G_EXC_ERROR;
2040 END IF;
2041
2042 if P_COMMIT = FND_API.G_TRUE then
2043 COMMIT WORK;
2044 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
2045 end if;
2046
2047 X_CUSTOM_TBL := l_CUSTOM_TBL;
2048 X_CUSTOM_SET_REC := l_CUSTOM_SET_REC;
2049
2050 -- END OF BODY OF API
2051 x_return_status := FND_API.G_RET_STS_SUCCESS;
2052
2053 -- Standard call to get message count and if count is 1, get message info
2054 FND_MSG_PUB.Count_And_Get(
2055 p_encoded => FND_API.G_FALSE,
2056 p_count => x_msg_count,
2057 p_data => x_msg_data);
2058
2059 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
2060
2061 EXCEPTION
2062 WHEN FND_API.G_EXC_ERROR THEN
2063 ROLLBACK TO loadCustomSchedule;
2064 x_return_status := FND_API.G_RET_STS_ERROR;
2065 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2066 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
2067 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2068 ROLLBACK TO loadCustomSchedule;
2069 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2070 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2071 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
2072 WHEN OTHERS THEN
2073 ROLLBACK TO loadCustomSchedule;
2074 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2075 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2076 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
2077 END;
2078
2079
2080
2081 -- This procedure recalculates custom schedule
2082 procedure recalcCustomSchedule(
2083 P_API_VERSION IN NUMBER,
2084 P_INIT_MSG_LIST IN VARCHAR2,
2085 P_COMMIT IN VARCHAR2,
2086 P_VALIDATION_LEVEL IN NUMBER,
2087 P_LOAN_ID IN NUMBER,
2088 P_BASED_ON_TERMS IN VARCHAR2,
2089 P_USE_RETAINED_DATA IN VARCHAR2,
2090 P_CUSTOM_SET_REC IN OUT NOCOPY LNS_CUSTOM_PUB.custom_settings_type,
2091 P_CUSTOM_TBL IN OUT NOCOPY LNS_CUSTOM_PUB.CUSTOM_TBL,
2092 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2093 X_MSG_COUNT OUT NOCOPY NUMBER,
2094 X_MSG_DATA OUT NOCOPY VARCHAR2)
2095 IS
2096
2097 /*-----------------------------------------------------------------------+
2098 | Local Variable Declarations and initializations |
2099 +-----------------------------------------------------------------------*/
2100
2101 l_api_name CONSTANT VARCHAR2(30) := 'recalcCustomSchedule';
2102 l_api_version CONSTANT NUMBER := 1.0;
2103 l_return_status VARCHAR2(1);
2104 l_msg_count NUMBER;
2105 l_msg_data VARCHAR2(32767);
2106
2107 l_loan_details LNS_CUSTOM_PUB.LOAN_DETAILS_REC;
2108 l_custom_tbl LNS_CUSTOM_PUB.CUSTOM_TBL;
2109 l_temp_row LNS_CUSTOM_PUB.custom_sched_type;
2110 l_rate_tbl LNS_FINANCIALS.RATE_SCHEDULE_TBL;
2111 l_rate_details LNS_FINANCIALS.INTEREST_RATE_REC;
2112
2113 l_compound_freq varchar2(30);
2114 l_remaining_balance_actual number;
2115 l_remaining_balance_theory number;
2116 -- l_remaining_balance_theory1 number;
2117 l_last_installment_billed number;
2118 l_calc_method varchar2(30);
2119 l_day_count_method varchar2(30);
2120 l_unbilled_principal number;
2121 l_num_unlocked_prin number;
2122 --l_pay_in_arrears boolean;
2123 l_period_begin_date date;
2124 l_period_end_date date;
2125 l_precision number;
2126 l_periodic_rate number;
2127 l_periodic_principal number;
2128 l_annualized_rate number;
2129 l_locked_prin number;
2130 l_unpaid_amount number;
2131 l_payment_freq varchar2(30);
2132 l_previous_annualized number;
2133 l_rate_to_calculate number;
2134 l_amortization_intervals number;
2135 l_periodic_payment number;
2136 l_unpaid_principal number;
2137 l_unpaid_interest number;
2138 l_num_installments number;
2139 i number;
2140 l_installment number;
2141 l_raw_rate number;
2142 l_norm_interest number;
2143 l_add_prin_interest number;
2144 l_add_int_interest number;
2145 l_add_start_date date;
2146 l_add_end_date date;
2147 l_penal_prin_interest number;
2148 l_penal_int_interest number;
2149 l_penal_interest number;
2150 l_prev_grace_end_date date;
2151 l_payment LNS_FIN_UTILS.PAYMENT_SCHEDULE;
2152 l_fees_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
2153 l_fee_basis_tbl LNS_FEE_ENGINE.FEE_BASIS_TBL;
2154
2155 l_fee_amount number;
2156 l_other_amount number;
2157 l_manual_fee_amount number;
2158 l_disb_header_id number;
2159 l_billed varchar2(1);
2160 n number;
2161 l_sum_periodic_principal number;
2162 l_date1 date;
2163 l_billed_principal number;
2164 l_detail_int_calc_flag boolean;
2165 l_increased_amount number;
2166 l_increased_amount1 number;
2167 l_begin_funded_amount number;
2168 l_end_funded_amount number;
2169 l_increase_amount_instal number;
2170 l_prev_increase_amount_instal number;
2171 l_begin_funded_amount_new number;
2172 l_fund_sched_count number;
2173 l_wtd_balance number;
2174 l_balance1 number;
2175 l_balance2 number;
2176 l_funded_amount number;
2177 l_deferred_int number;
2178 l_prev_deferred_int number;
2179 --l_default_funded_amount number;
2180 l_AMORT_METHOD varchar2(30);
2181 l_COMBINE_INT_WITH_LAST_PRIN varchar2(1);
2182 l_remaining_balance_theory1 number;
2183 l_prev_deferred_int1 number;
2184 l_norm_interest1 number;
2185 l_add_prin_interest1 number;
2186 l_add_int_interest1 number;
2187 l_penal_interest1 number;
2188 l_cap_int number;
2189 l_prev_cap_int number;
2190 l_prev_cap_int1 number;
2191 l_end_balance number;
2192 l_early_pay_cr number;
2193 l_early_pay_cr1 number;
2194 l_bill_on_appr_amounts varchar2(1);
2195
2196 l_norm_int_detail_str varchar2(2000);
2197 l_add_prin_int_detail_str varchar2(2000);
2198 l_add_int_int_detail_str varchar2(2000);
2199 l_penal_prin_int_detail_str varchar2(2000);
2200 l_penal_int_int_detail_str varchar2(2000);
2201 l_penal_int_detail_str varchar2(2000);
2202 l_deferred_int_detail_str varchar2(2000);
2203 l_norm_int_detail_str1 varchar2(2000);
2204 l_add_prin_int_detail_str1 varchar2(2000);
2205 l_add_int_int_detail_str1 varchar2(2000);
2206 l_penal_int_detail_str1 varchar2(2000);
2207 l_cap_int_detail_str varchar2(2000);
2208 l_early_pay_cr_detail_str varchar2(2000);
2209 l_early_pay_cr_detail_str1 varchar2(2000);
2210
2211 /*-----------------------------------------------------------------------+
2212 | Cursor Declarations |
2213 +-----------------------------------------------------------------------*/
2214
2215 -- get last bill date
2216 cursor c_get_last_bill_date(p_loan_id number, p_installment_number number) is
2217 select ACTIVITY_DATE
2218 from LNS_PRIN_TRX_ACTIVITIES_V
2219 where loan_id = p_loan_id
2220 and PAYMENT_NUMBER = p_installment_number
2221 and ACTIVITY_CODE in ('BILLING', 'START');
2222
2223 cursor c_get_funded_amount(p_loan_id number, p_installment_number number) is
2224 select FUNDED_AMOUNT, nvl(DEFERRED_INT_AMOUNT, 0), nvl(CAP_INT_AMOUNT, 0)
2225 from LNS_AMORTIZATION_SCHEDS
2226 where loan_id = p_loan_id
2227 and PAYMENT_NUMBER = p_installment_number
2228 and (REVERSED_FLAG is null or REVERSED_FLAG = 'N')
2229 and PARENT_AMORTIZATION_ID is null
2230 and nvl(PHASE, 'TERM') = 'TERM';
2231
2232 cursor c_fund_sched_exist(p_loan_id number) is
2233 select decode(loan.loan_class_code,
2234 'DIRECT', (select count(1) from lns_disb_headers where loan_id = p_loan_id and status is null and PAYMENT_REQUEST_DATE is not null),
2235 'ERS', (select count(1) from lns_loan_lines where loan_id = p_loan_id and (status is null or status = 'PENDING') and end_date is null))
2236 from lns_loan_headers_all loan
2237 where loan.loan_id = p_loan_id;
2238
2239 cursor c_get_bill_opt(p_loan_id number) is
2240 select nvl(BILL_ON_APPR_AMOUNT_FLAG, 'N')
2241 from lns_loan_headers_all
2242 where loan_id = p_loan_id;
2243
2244 BEGIN
2245
2246 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
2247
2248 -- Standard start of API savepoint
2249 SAVEPOINT recalcCustomSchedule;
2250 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Savepoint is established');
2251
2252 -- Standard call to check for call compatibility
2253 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2255 END IF;
2256
2257 -- Initialize message list if p_init_msg_list is set to TRUE
2258 IF FND_API.To_Boolean(p_init_msg_list) THEN
2259 FND_MSG_PUB.initialize;
2260 END IF;
2261
2262 -- Initialize API return status to success
2263 l_return_status := FND_API.G_RET_STS_SUCCESS;
2264
2265 -- START OF BODY OF API
2266
2267 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
2268 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID: ' || P_LOAN_ID);
2269 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_BASED_ON_TERMS: ' || P_BASED_ON_TERMS);
2270 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_USE_RETAINED_DATA: ' || P_USE_RETAINED_DATA);
2271 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'AMORT_METHOD: ' || P_CUSTOM_SET_REC.AMORT_METHOD);
2272 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DESCRIPTION: ' || P_CUSTOM_SET_REC.DESCRIPTION);
2273
2274 if P_LOAN_ID is null then
2275 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2276 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
2277 FND_MESSAGE.SET_TOKEN('VALUE', P_LOAN_ID);
2278 FND_MSG_PUB.ADD;
2279 RAISE FND_API.G_EXC_ERROR;
2280 end if;
2281
2282 if P_BASED_ON_TERMS is null then
2283 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2284 FND_MESSAGE.SET_TOKEN('PARAMETER', 'BASED_ON_TERMS');
2285 FND_MESSAGE.SET_TOKEN('VALUE', P_BASED_ON_TERMS);
2286 FND_MSG_PUB.ADD;
2287 RAISE FND_API.G_EXC_ERROR;
2288 end if;
2289
2290 if P_USE_RETAINED_DATA is not null and P_USE_RETAINED_DATA = 'Y' then
2291 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'count = ' || G_CUSTOM_TBL.count);
2292 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'G_AMORT_METHOD = ' || G_AMORT_METHOD);
2293 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'g_COMBINE_INT_WITH_LAST_PRIN = ' || g_COMBINE_INT_WITH_LAST_PRIN);
2294 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'g_CUSTOM_SCHED_DESC = ' || g_CUSTOM_SCHED_DESC);
2295
2296 l_CUSTOM_TBL := G_CUSTOM_TBL;
2297 l_AMORT_METHOD := G_AMORT_METHOD;
2298 l_COMBINE_INT_WITH_LAST_PRIN := g_COMBINE_INT_WITH_LAST_PRIN;
2299
2300 if l_AMORT_METHOD is null then
2301 l_AMORT_METHOD := 'NONE';
2302 end if;
2303 P_CUSTOM_SET_REC.AMORT_METHOD := l_AMORT_METHOD;
2304 P_CUSTOM_SET_REC.DESCRIPTION := g_CUSTOM_SCHED_DESC;
2305
2306 else
2307 if P_CUSTOM_SET_REC.AMORT_METHOD is null then
2308 P_CUSTOM_SET_REC.AMORT_METHOD := 'NONE';
2309 end if;
2310
2311 l_CUSTOM_TBL := P_CUSTOM_TBL;
2312 l_AMORT_METHOD := P_CUSTOM_SET_REC.AMORT_METHOD;
2313 l_COMBINE_INT_WITH_LAST_PRIN := 'N';
2314 end if;
2315
2316 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_AMORT_METHOD = ' || l_AMORT_METHOD);
2317 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_COMBINE_INT_WITH_LAST_PRIN = ' || l_COMBINE_INT_WITH_LAST_PRIN);
2318
2319 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input Schedule:');
2320 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PN RDD DD RATE BB UP UI PAY PRIN(%) LP INT LI EB ACT');
2321 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, '--- ------ -------- ---- ------ ------ ------ ------ --------- -- ------ -- ------ ---');
2322 for i in 1..l_custom_tbl.count loop
2323
2324 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME,
2325 l_custom_tbl(i).PAYMENT_NUMBER || ' ' ||
2326 l_custom_tbl(i).RELATIVE_DATE || ' ' ||
2327 l_custom_tbl(i).DUE_DATE || ' ' ||
2328 l_custom_tbl(i).INTEREST_RATE || ' ' ||
2329 l_custom_tbl(i).INSTALLMENT_BEGIN_BALANCE || ' ' ||
2330 l_custom_tbl(i).UNPAID_PRIN || ' ' ||
2331 l_custom_tbl(i).UNPAID_INT || ' ' ||
2332 l_custom_tbl(i).CURRENT_TERM_PAYMENT || ' ' ||
2333 l_custom_tbl(i).PRINCIPAL_AMOUNT || '(' || l_custom_tbl(i).PRINCIPAL_PERCENT || '%) ' ||
2334 l_custom_tbl(i).LOCK_PRIN || ' ' ||
2335 l_custom_tbl(i).INTEREST_AMOUNT || ' ' ||
2336 l_custom_tbl(i).LOCK_INT || ' ' ||
2337 l_custom_tbl(i).INSTALLMENT_END_BALANCE || ' ' ||
2338 l_custom_tbl(i).ACTION);
2339 end loop;
2340
2341 l_loan_details := getLoanDetails(p_loan_Id => p_loan_id
2342 ,p_based_on_terms => p_based_on_terms);
2343
2344 filterCustSchedule(l_custom_tbl);
2345 resolveRelativeDates(l_loan_details, l_custom_tbl);
2346 if l_custom_tbl.count = 0 then
2347
2348 -- fix for bug 7217204
2349 x_return_status := FND_API.G_RET_STS_SUCCESS;
2350 P_CUSTOM_TBL := l_CUSTOM_TBL;
2351 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Custom schedule is empty. Returning.');
2352 return;
2353
2354 end if;
2355
2356 sortRowsByDate(l_custom_tbl);
2357
2358 l_remaining_balance_theory := l_loan_details.requested_amount; --remaining_balance;
2359 l_remaining_balance_actual := l_loan_details.remaining_balance;
2360 l_last_installment_billed := l_loan_details.last_installment_billed;
2361 l_calc_method := l_loan_details.CALCULATION_METHOD;
2362 l_day_count_method := l_loan_details.day_count_method;
2363 l_unbilled_principal := l_loan_details.unbilled_principal;
2364 l_billed_principal := l_loan_details.billed_principal;
2365 l_unpaid_amount := l_loan_details.unpaid_principal + l_loan_details.UNPAID_INTEREST;
2366 l_compound_freq := l_loan_details.INTEREST_COMPOUNDING_FREQ;
2367 l_payment_freq := l_loan_details.PAYMENT_FREQUENCY;
2368 l_precision := l_loan_details.currency_precision;
2369 l_previous_annualized := -1;
2370 l_unpaid_principal := l_loan_details.unpaid_principal;
2371 l_unpaid_interest := l_loan_details.UNPAID_INTEREST;
2372 l_sum_periodic_principal := 0;
2373
2374 l_num_unlocked_prin := 0;
2375 l_locked_prin := 0;
2376 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Sorted table:');
2377 for i in 1..l_custom_tbl.count loop
2378
2379 l_custom_tbl(i).PAYMENT_NUMBER := l_last_installment_billed + i;
2380
2381 -- get start and end dates
2382 if l_custom_tbl(i).PAYMENT_NUMBER = (l_last_installment_billed + 1) then
2383 if l_custom_tbl(i).PAYMENT_NUMBER = 0 or l_custom_tbl(i).PAYMENT_NUMBER = 1 then
2384 l_period_begin_date := l_loan_details.LOAN_START_DATE;
2385 else
2386 l_period_begin_date := l_loan_details.LAST_DUE_DATE;
2387 end if;
2388 else
2389 l_period_begin_date := l_custom_tbl(i-1).DUE_DATE;
2390 end if;
2391 l_period_end_date := l_custom_tbl(i).DUE_DATE;
2392
2393 l_custom_tbl(i).PERIOD_START_DATE := l_period_begin_date;
2394 l_custom_tbl(i).PERIOD_END_DATE := l_period_end_date;
2395 l_custom_tbl(i).PERIOD := FND_DATE.DATE_TO_DISPLAYDATE(l_period_begin_date, 1) || ' - ' || FND_DATE.DATE_TO_DISPLAYDATE((l_period_end_date-1), 1);
2396
2397 l_funded_amount := LNS_FINANCIALS.getFundedAmount(p_loan_id, l_custom_tbl(i).PERIOD_START_DATE, p_based_on_terms);
2398 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_funded_amount: ' || l_funded_amount);
2399
2400 -- fix for bug 7026226: default PRINCIPAL_AMOUNT if its null
2401 if l_custom_tbl(i).PRINCIPAL_AMOUNT is null then
2402 l_custom_tbl(i).PRINCIPAL_AMOUNT := 0;
2403 end if;
2404
2405 -- fix for bug 7026226: default INTEREST_AMOUNT if its null
2406 if l_custom_tbl(i).INTEREST_AMOUNT is null then
2407 l_custom_tbl(i).INTEREST_AMOUNT := 0;
2408 end if;
2409
2410 -- default LOCK_PRIN
2411 -- fix for bug 8309391 - let to lock last prin row
2412 if l_custom_tbl(i).LOCK_PRIN is null then
2413 l_custom_tbl(i).LOCK_PRIN := 'N';
2414 end if;
2415
2416 -- default LOCK_INT
2417 if l_custom_tbl(i).LOCK_INT is null then
2418 l_custom_tbl(i).LOCK_INT := 'N';
2419 end if;
2420
2421 -- if principal percent is locked
2422 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'LOCK_PRIN: ' || l_custom_tbl(i).LOCK_PRIN);
2423 if l_custom_tbl(i).LOCK_PRIN = 'P' then
2424 l_custom_tbl(i).PRINCIPAL_AMOUNT := l_funded_amount * l_custom_tbl(i).PRINCIPAL_PERCENT/100;
2425 else
2426 if l_funded_amount = 0 then
2427 l_custom_tbl(i).PRINCIPAL_PERCENT := round(l_custom_tbl(i).PRINCIPAL_AMOUNT*100/1, 2);
2428 else
2429 l_custom_tbl(i).PRINCIPAL_PERCENT := round(l_custom_tbl(i).PRINCIPAL_AMOUNT*100/l_funded_amount, 2);
2430 end if;
2431 end if;
2432 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_custom_tbl(i).PRINCIPAL_AMOUNT: ' || l_custom_tbl(i).PRINCIPAL_AMOUNT);
2433 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_custom_tbl(i).PRINCIPAL_PERCENT: ' || l_custom_tbl(i).PRINCIPAL_PERCENT);
2434
2435 -- count number of unlocked principals and sum of locked principals
2436 if l_custom_tbl(i).LOCK_PRIN = 'N' then
2437 l_num_unlocked_prin := l_num_unlocked_prin + 1;
2438 else
2439 l_locked_prin := l_locked_prin + l_custom_tbl(i).PRINCIPAL_AMOUNT;
2440 end if;
2441
2442 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_custom_tbl(' || i || ').DUE_DATE: ' || l_custom_tbl(i).DUE_DATE);
2443
2444 end loop;
2445
2446 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Number of unlocked principals: ' || l_num_unlocked_prin);
2447 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Sum of locked principals: ' || l_locked_prin);
2448
2449 -- get rate schedule
2450 l_rate_tbl := lns_financials.getRateSchedule(p_loan_id, 'TERM');
2451
2452 -- synch rate schedule with current custom schedule
2453 l_num_installments := l_custom_tbl(l_custom_tbl.count).PAYMENT_NUMBER;
2454 synchRateSchedule(l_rate_tbl, l_num_installments);
2455
2456 if p_based_on_terms <> 'CURRENT' then
2457 open c_fund_sched_exist(p_loan_id);
2458 fetch c_fund_sched_exist into l_fund_sched_count;
2459 close c_fund_sched_exist;
2460
2461 if l_fund_sched_count = 0 then
2462 l_remaining_balance_theory := l_loan_details.requested_amount;
2463 else
2464 l_remaining_balance_theory := LNS_FINANCIALS.getFundedAmount(p_loan_id, l_loan_details.LOAN_START_DATE, p_based_on_terms);
2465 end if;
2466 else
2467 l_remaining_balance_theory := LNS_FINANCIALS.getFundedAmount(p_loan_id, l_loan_details.LOAN_START_DATE, p_based_on_terms);
2468 end if;
2469
2470 if p_based_on_terms = 'CURRENT' and l_last_installment_billed > 0 then
2471 l_begin_funded_amount := 0;
2472 open c_get_funded_amount(p_loan_id, l_last_installment_billed);
2473 fetch c_get_funded_amount into l_begin_funded_amount, l_prev_deferred_int, l_prev_cap_int;
2474 close c_get_funded_amount;
2475 else
2476 l_begin_funded_amount := 0; --l_remaining_balance_theory;
2477 l_prev_deferred_int := 0;
2478 l_prev_cap_int := 0;
2479
2480 open c_get_bill_opt(p_loan_id);
2481 fetch c_get_bill_opt into l_bill_on_appr_amounts;
2482 close c_get_bill_opt;
2483 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || 'l_bill_on_appr_amounts = ' || l_bill_on_appr_amounts);
2484 end if;
2485
2486 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_begin_funded_amount = ' || l_begin_funded_amount);
2487 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_prev_deferred_int = ' || l_prev_deferred_int);
2488 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_prev_cap_int = ' || l_prev_cap_int);
2489 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': CAPITALIZE_INT = ' || l_loan_details.CAPITALIZE_INT);
2490
2491 l_increase_amount_instal := -1;
2492 --l_default_funded_amount := LNS_FINANCIALS.getFundedAmount(p_loan_id, l_loan_details.LOAN_START_DATE, p_based_on_terms);
2493
2494 for i in 1..l_custom_tbl.count loop
2495
2496 --i := i + 1;
2497 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' ');
2498 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Row ' || i);
2499 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PAYMENT_NUMBER ' || l_custom_tbl(i).PAYMENT_NUMBER);
2500 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, '----------------------------------');
2501
2502 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PERIOD_START_DATE: ' || l_custom_tbl(i).PERIOD_START_DATE);
2503 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PERIOD_END_DATE: ' || l_custom_tbl(i).PERIOD_END_DATE);
2504 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PERIOD: ' || l_custom_tbl(i).PERIOD);
2505
2506 l_period_begin_date := l_custom_tbl(i).PERIOD_START_DATE;
2507 l_period_end_date := l_custom_tbl(i).PERIOD_END_DATE;
2508
2509 -- get rate
2510 l_rate_details := lns_financials.getRateDetails(p_installment => l_custom_tbl(i).PAYMENT_NUMBER
2511 ,p_rate_tbl => l_rate_tbl);
2512
2513 l_annualized_rate := l_rate_details.annual_rate;
2514 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'annualized_rate: ' || l_annualized_rate);
2515 l_custom_tbl(i).INTEREST_RATE := l_annualized_rate;
2516
2517 l_norm_interest := 0;
2518 l_add_prin_interest := 0;
2519 l_add_int_interest := 0;
2520 l_penal_prin_interest := 0;
2521 l_penal_int_interest := 0;
2522 l_penal_interest := 0;
2523 l_norm_int_detail_str := null;
2524 l_add_prin_int_detail_str := null;
2525 l_add_int_int_detail_str := null;
2526 l_penal_prin_int_detail_str := null;
2527 l_penal_int_int_detail_str := null;
2528 l_penal_int_detail_str := null;
2529 l_detail_int_calc_flag := false;
2530 l_increased_amount := 0;
2531 l_increased_amount1:= 0;
2532 l_prev_increase_amount_instal := l_increase_amount_instal;
2533 l_prev_cap_int1 := 0;
2534 l_cap_int := 0;
2535 l_early_pay_cr := 0;
2536 l_early_pay_cr_detail_str := null;
2537
2538 if l_deferred_int is null then
2539 l_deferred_int := 0;
2540 end if;
2541
2542 if l_fund_sched_count > 0 or p_based_on_terms = 'CURRENT' then
2543
2544 if l_custom_tbl(i).PAYMENT_NUMBER = (l_last_installment_billed + 1) then
2545
2546 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_custom_tbl(i).PAYMENT_NUMBER = (l_last_installment_billed + 1)');
2547
2548 if l_loan_details.LOAN_STATUS <> 'PAIDOFF' and l_loan_details.LOAN_STATUS <> 'CANCELLED' then
2549 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Getting funded amount for ' || l_custom_tbl(i).PERIOD_START_DATE);
2550 l_begin_funded_amount_new := LNS_FINANCIALS.getFundedAmount(p_loan_id, l_custom_tbl(i).PERIOD_START_DATE, p_based_on_terms);
2551 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_begin_funded_amount_new = ' || l_begin_funded_amount_new);
2552
2553 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Getting funded amount for ' || (l_custom_tbl(i).PERIOD_END_DATE-1));
2554 l_end_funded_amount := LNS_FINANCIALS.getFundedAmount(p_loan_id, (l_custom_tbl(i).PERIOD_END_DATE-1), p_based_on_terms);
2555 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_end_funded_amount = ' || l_end_funded_amount);
2556
2557 if l_end_funded_amount > l_begin_funded_amount then
2558 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_end_funded_amount > l_begin_funded_amount');
2559
2560 if l_end_funded_amount = l_begin_funded_amount_new then
2561 l_increase_amount_instal := i;
2562 else
2563 if l_loan_details.REAMORTIZE_ON_FUNDING = 'REST' then
2564 l_increase_amount_instal := i + 1;
2565 elsif l_loan_details.REAMORTIZE_ON_FUNDING = 'IMMEDIATELY' then
2566 l_increase_amount_instal := i;
2567 end if;
2568 end if;
2569
2570 elsif l_begin_funded_amount_new > l_begin_funded_amount then
2571 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_begin_funded_amount_new > l_begin_funded_amount');
2572 l_increase_amount_instal := i;
2573 end if;
2574
2575 l_detail_int_calc_flag := true;
2576
2577 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Getting funded amount for ' || (l_custom_tbl(i).PERIOD_START_DATE-1));
2578 l_begin_funded_amount := LNS_FINANCIALS.getFundedAmount(p_loan_id, (l_custom_tbl(i).PERIOD_START_DATE-1), p_based_on_terms);
2579 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_begin_funded_amount = ' || l_begin_funded_amount);
2580
2581 l_increased_amount := l_end_funded_amount - l_begin_funded_amount;
2582 l_begin_funded_amount := l_begin_funded_amount_new;
2583 l_increased_amount1 := l_end_funded_amount - l_begin_funded_amount;
2584 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_billed_principal = ' || l_billed_principal);
2585 l_remaining_balance_theory := l_begin_funded_amount - l_billed_principal;
2586 else
2587 l_remaining_balance_theory := 0;
2588 end if;
2589
2590 else
2591
2592 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_custom_tbl(i).PAYMENT_NUMBER > (l_last_installment_billed + 1)');
2593 if l_loan_details.loan_status <> 'PAIDOFF' and l_loan_details.LOAN_STATUS <> 'CANCELLED' then
2594 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Getting funded amount for ' || (l_custom_tbl(i).PERIOD_START_DATE-1));
2595 l_begin_funded_amount := LNS_FINANCIALS.getFundedAmount(p_loan_id, (l_custom_tbl(i).PERIOD_START_DATE-1), p_based_on_terms);
2596 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_begin_funded_amount = ' || l_begin_funded_amount);
2597
2598 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Getting funded amount for ' || l_custom_tbl(i).PERIOD_START_DATE);
2599 l_begin_funded_amount_new := LNS_FINANCIALS.getFundedAmount(p_loan_id, l_custom_tbl(i).PERIOD_START_DATE, p_based_on_terms);
2600 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_begin_funded_amount_new = ' || l_begin_funded_amount_new);
2601
2602 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Getting funded amount for ' || (l_custom_tbl(i).PERIOD_END_DATE-1));
2603 l_end_funded_amount := LNS_FINANCIALS.getFundedAmount(p_loan_id, (l_custom_tbl(i).PERIOD_END_DATE-1), p_based_on_terms);
2604 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_end_funded_amount = ' || l_end_funded_amount);
2605
2606 if l_end_funded_amount > l_begin_funded_amount then
2607 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_end_funded_amount > l_begin_funded_amount');
2608 l_detail_int_calc_flag := true;
2609
2610 if l_end_funded_amount = l_begin_funded_amount_new then
2611 l_increase_amount_instal := i;
2612 else
2613 if l_loan_details.REAMORTIZE_ON_FUNDING = 'REST' then
2614 l_increase_amount_instal := i + 1;
2615 elsif l_loan_details.REAMORTIZE_ON_FUNDING = 'IMMEDIATELY' then
2616 l_increase_amount_instal := i;
2617 end if;
2618 end if;
2619
2620 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_billed_principal = ' || l_billed_principal);
2621 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_sum_periodic_principal = ' || l_sum_periodic_principal);
2622
2623 l_increased_amount := l_end_funded_amount - l_begin_funded_amount;
2624 l_begin_funded_amount := l_begin_funded_amount_new;
2625 l_increased_amount1 := l_end_funded_amount - l_begin_funded_amount;
2626 l_remaining_balance_theory := l_begin_funded_amount - l_billed_principal - l_sum_periodic_principal;
2627 end if;
2628 else
2629 l_remaining_balance_theory := 0;
2630 end if;
2631
2632 end if;
2633
2634 elsif i = 1 and l_fund_sched_count = 0 and p_based_on_terms <> 'CURRENT' then
2635 l_increased_amount := l_loan_details.requested_amount;
2636 l_begin_funded_amount := l_loan_details.requested_amount;
2637 l_end_funded_amount := l_loan_details.requested_amount;
2638 else
2639 l_begin_funded_amount := l_loan_details.requested_amount;
2640 l_end_funded_amount := l_loan_details.requested_amount;
2641 end if;
2642
2643 if l_loan_details.REAMORTIZE_ON_FUNDING = 'NO' then
2644 l_increase_amount_instal := -1;
2645 end if;
2646
2647 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_begin_funded_amount = ' || l_begin_funded_amount);
2648 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_end_funded_amount = ' || l_end_funded_amount);
2649 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_remaining_balance_theory = ' || l_remaining_balance_theory);
2650 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_increased_amount = ' || l_increased_amount);
2651 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_increased_amount1 = ' || l_increased_amount1);
2652 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': LOCK_INT = ' || l_custom_tbl(i).LOCK_INT);
2653
2654 -- calc interest amount
2655 if l_custom_tbl(i).LOCK_INT = 'N' or l_custom_tbl(i).LOCK_INT = 'D' or l_custom_tbl(i).LOCK_INT = 'C' or
2656 l_loan_details.LOAN_STATUS = 'PAIDOFF' or l_loan_details.LOAN_STATUS = 'CANCELLED'
2657 then
2658
2659 if l_detail_int_calc_flag then
2660 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_detail_int_calc_flag = true');
2661 else
2662 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_detail_int_calc_flag = false');
2663 end if;
2664
2665 if (p_based_on_terms = 'CURRENT' and l_detail_int_calc_flag = true) then
2666
2667 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating normal interest...');
2668 LNS_FINANCIALS.CALC_NORM_INTEREST(p_loan_id => p_loan_id,
2669 p_calc_method => l_calc_method,
2670 p_period_start_date => l_period_begin_date,
2671 p_period_end_date => l_period_end_date,
2672 p_interest_rate => l_annualized_rate,
2673 p_day_count_method => l_day_count_method,
2674 p_payment_freq => l_payment_freq,
2675 p_compound_freq => l_compound_freq,
2676 p_adj_amount => l_sum_periodic_principal,
2677 p_CAP_AMOUNT => l_prev_cap_int,
2678 x_norm_interest => l_norm_interest,
2679 x_norm_int_details => l_norm_int_detail_str);
2680
2681 l_norm_interest := round(l_norm_interest, l_precision);
2682
2683 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));
2684 if (l_custom_tbl(i).PAYMENT_NUMBER)-1 >= 0 then
2685
2686 -- get additional interest start date
2687 open c_get_last_bill_date(p_loan_id, (l_custom_tbl(i).PAYMENT_NUMBER-1));
2688 fetch c_get_last_bill_date into l_add_start_date;
2689 close c_get_last_bill_date;
2690 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_add_start_date: ' || l_add_start_date);
2691
2692 -- get additional interest end date
2693 --l_add_end_date := l_period_end_date;
2694
2695 if trunc(sysdate) > trunc(l_period_end_date) then
2696 l_add_end_date := l_period_end_date;
2697 else
2698 l_add_end_date := sysdate;
2699 end if;
2700
2701 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_add_end_date: ' || l_add_end_date);
2702
2703 if (l_custom_tbl(i).PAYMENT_NUMBER-1) > 0 then
2704 l_payment := getPayment(p_loan_id, (l_custom_tbl(i).PAYMENT_NUMBER-1)); -- fix for bug 7319358
2705 l_prev_grace_end_date := l_payment.PERIOD_BEGIN_DATE + l_loan_details.PENAL_INT_GRACE_DAYS;
2706 else
2707 l_prev_grace_end_date := l_period_begin_date;
2708 end if;
2709 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_prev_grace_end_date: ' || l_prev_grace_end_date);
2710
2711 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating additional interest on unpaid principal...');
2712 -- calculate additional interest on unpaid principal
2713 LNS_FINANCIALS.CALC_ADD_INTEREST(p_loan_id => p_loan_id,
2714 p_calc_method => l_calc_method,
2715 p_period_start_date => l_add_start_date,
2716 p_period_end_date => l_add_end_date,
2717 p_interest_rate => l_annualized_rate,
2718 p_day_count_method => l_day_count_method,
2719 p_payment_freq => l_payment_freq,
2720 p_compound_freq => l_compound_freq,
2721 p_penal_int_rate => l_loan_details.PENAL_INT_RATE,
2722 p_prev_grace_end_date => l_prev_grace_end_date,
2723 p_grace_start_date => l_period_begin_date,
2724 p_grace_end_date => (l_period_begin_date + l_loan_details.PENAL_INT_GRACE_DAYS),
2725 p_target => 'UNPAID_PRIN',
2726 x_add_interest => l_add_prin_interest,
2727 x_penal_interest => l_penal_prin_interest,
2728 x_add_int_details => l_add_prin_int_detail_str,
2729 x_penal_int_details => l_penal_prin_int_detail_str);
2730 l_add_prin_interest := round(l_add_prin_interest, l_precision);
2731
2732 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating additional interest on unpaid interest...');
2733 -- calculate additional interest on unpaid interest
2734 LNS_FINANCIALS.CALC_ADD_INTEREST(p_loan_id => p_loan_id,
2735 p_calc_method => l_calc_method,
2736 p_period_start_date => l_add_start_date,
2737 p_period_end_date => l_add_end_date,
2738 p_interest_rate => l_annualized_rate,
2739 p_day_count_method => l_day_count_method,
2740 p_payment_freq => l_payment_freq,
2741 p_compound_freq => l_compound_freq,
2742 p_penal_int_rate => l_loan_details.PENAL_INT_RATE,
2743 p_prev_grace_end_date => l_prev_grace_end_date,
2744 p_grace_start_date => l_period_begin_date,
2745 p_grace_end_date => (l_period_begin_date + l_loan_details.PENAL_INT_GRACE_DAYS),
2746 p_target => 'UNPAID_INT',
2747 x_add_interest => l_add_int_interest,
2748 x_penal_interest => l_penal_int_interest,
2749 x_add_int_details => l_add_int_int_detail_str,
2750 x_penal_int_details => l_penal_int_int_detail_str);
2751 l_add_int_interest := round(l_add_int_interest, l_precision);
2752
2753 if l_penal_prin_int_detail_str is not null and l_penal_int_int_detail_str is not null then
2754 l_penal_int_detail_str := l_penal_prin_int_detail_str || '+<br>' || l_penal_int_int_detail_str;
2755 else
2756 l_penal_int_detail_str := l_penal_prin_int_detail_str || l_penal_int_int_detail_str;
2757 end if;
2758
2759 -- calculate interest credit on early payment
2760 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calculating early payment credit amount...');
2761 LNS_FINANCIALS.CALC_EARLY_PAY_CR(p_loan_id => p_loan_id,
2762 p_calc_method => l_calc_method,
2763 p_installment => l_custom_tbl(i).PAYMENT_NUMBER,
2764 p_interest_rate => l_previous_annualized,
2765 p_day_count_method => l_day_count_method,
2766 p_payment_freq => l_payment_freq,
2767 p_compound_freq => l_compound_freq,
2768 x_early_pay_cr => l_early_pay_cr,
2769 x_EARLY_PAY_CR_DETAILS => l_early_pay_cr_detail_str);
2770
2771 end if;
2772
2773 elsif (p_based_on_terms <> 'CURRENT' and l_detail_int_calc_flag = true and l_bill_on_appr_amounts = 'N') then
2774
2775 if (l_calc_method = 'SIMPLE') then
2776
2777 -- recalculate periodic rate for each period if day counting methodolgy varies
2778 l_periodic_rate := lns_financials.getPeriodicRate(
2779 p_payment_freq => l_payment_freq
2780 ,p_period_start_date => l_period_begin_date
2781 ,p_period_end_date => l_period_end_date
2782 ,p_annualized_rate => l_annualized_rate
2783 ,p_days_count_method => l_day_count_method
2784 ,p_target => 'INTEREST');
2785
2786 elsif (l_calc_method = 'COMPOUND') then
2787
2788 l_periodic_rate := lns_financials.getCompoundPeriodicRate(p_compound_freq => l_compound_freq
2789 ,p_payment_freq => l_payment_freq
2790 ,p_annualized_rate => l_annualized_rate
2791 ,p_period_start_date => l_period_begin_date
2792 ,p_period_end_date => l_period_end_date
2793 ,p_days_count_method => l_day_count_method
2794 ,p_target => 'INTEREST');
2795
2796 end if;
2797
2798 lns_financials.getWeightedBalance(p_loan_id => p_loan_id
2799 ,p_from_date => l_period_begin_date
2800 ,p_to_date => l_period_end_date
2801 ,p_calc_method => 'TARGET'
2802 ,p_phase => 'TERM'
2803 ,p_day_count_method => l_day_count_method
2804 ,p_adj_amount => l_sum_periodic_principal
2805 ,x_wtd_balance => l_wtd_balance
2806 ,x_begin_balance => l_balance1
2807 ,x_end_balance => l_balance2);
2808
2809 l_wtd_balance := l_wtd_balance + l_prev_cap_int;
2810 l_norm_interest := lns_financials.calculateInterest(p_amount => l_wtd_balance
2811 ,p_periodic_rate => l_periodic_rate
2812 ,p_compounding_period => null);
2813 l_norm_interest := round(l_norm_interest, l_precision);
2814
2815 l_norm_int_detail_str := l_norm_interest || ' (' ||
2816 'Period ' || FND_DATE.DATE_TO_DISPLAYDATE(l_period_begin_date, 1) || ' - ' || FND_DATE.DATE_TO_DISPLAYDATE((l_period_end_date-1), 1) ||
2817 ' * Balance ' || l_wtd_balance ||
2818 ' * Rate ' || l_annualized_rate || '%)';
2819 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_norm_int_detail_str);
2820
2821 else
2822
2823 if (l_calc_method = 'SIMPLE') then
2824
2825 l_periodic_rate := lns_financials.getPeriodicRate(
2826 p_payment_freq => l_payment_freq
2827 ,p_period_start_date => l_period_begin_date
2828 ,p_period_end_date => l_period_end_date
2829 ,p_annualized_rate => l_annualized_rate
2830 ,p_days_count_method => l_day_count_method
2831 ,p_target => 'INTEREST');
2832
2833 elsif (l_calc_method = 'COMPOUND') then
2834
2835 l_periodic_rate := lns_financials.getCompoundPeriodicRate(p_compound_freq => l_compound_freq
2836 ,p_payment_freq => l_payment_freq
2837 ,p_annualized_rate => l_annualized_rate
2838 ,p_period_start_date => l_period_begin_date
2839 ,p_period_end_date => l_period_end_date
2840 ,p_days_count_method => l_day_count_method
2841 ,p_target => 'INTEREST');
2842 end if;
2843
2844 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'periodic_rate: ' || l_periodic_rate);
2845 l_remaining_balance_theory1 := l_remaining_balance_theory + l_prev_cap_int;
2846 l_norm_interest := lns_financials.calculateInterest(p_amount => l_remaining_balance_theory1
2847 ,p_periodic_rate => l_periodic_rate
2848 ,p_compounding_period => null);
2849 l_norm_interest := round(l_norm_interest, l_precision);
2850
2851 l_norm_int_detail_str := l_norm_interest || ' (' ||
2852 'Period ' || FND_DATE.DATE_TO_DISPLAYDATE(l_period_begin_date, 1) || ' - ' || FND_DATE.DATE_TO_DISPLAYDATE((l_period_end_date-1), 1) ||
2853 ' * Balance ' || l_remaining_balance_theory1 ||
2854 ' * Rate ' || l_annualized_rate || '%)';
2855 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_norm_int_detail_str);
2856
2857 end if;
2858
2859 l_penal_interest := round(l_penal_prin_interest + l_penal_int_interest, l_precision);
2860
2861 l_deferred_int_detail_str := null;
2862 l_prev_deferred_int1 := l_prev_deferred_int;
2863 l_norm_interest1 := l_norm_interest;
2864 l_add_prin_interest1 := l_add_prin_interest;
2865 l_add_int_interest1 := l_add_int_interest;
2866 l_penal_interest1 := l_penal_interest;
2867 l_prev_cap_int1 := l_prev_cap_int;
2868 l_norm_int_detail_str1 := l_norm_int_detail_str;
2869 l_add_prin_int_detail_str1 := l_add_prin_int_detail_str;
2870 l_add_int_int_detail_str1 := l_add_int_int_detail_str;
2871 l_penal_int_detail_str1 := l_penal_int_detail_str;
2872 l_early_pay_cr_detail_str1 := l_early_pay_cr_detail_str;
2873 l_cap_int := l_prev_cap_int;
2874 l_early_pay_cr1 := l_early_pay_cr;
2875 l_cap_int_detail_str := null;
2876 if l_prev_cap_int <> 0 then
2877 l_cap_int_detail_str := 'Previously Capitalized Interest ' || l_prev_cap_int;
2878 end if;
2879
2880 if l_custom_tbl(i).LOCK_INT = 'D' then -- interest is deferred
2881 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': processing deferred interest...');
2882
2883 if l_prev_deferred_int <> 0 then
2884 l_deferred_int_detail_str := l_deferred_int_detail_str || 'Previously Deferred Interest ' || l_prev_deferred_int;
2885 end if;
2886 if l_norm_interest <> 0 then
2887 if l_deferred_int_detail_str is not null then
2888 l_deferred_int_detail_str := l_deferred_int_detail_str || ' +<br>';
2889 end if;
2890 l_deferred_int_detail_str := l_deferred_int_detail_str || 'Current Normal Interest ' || l_norm_int_detail_str;
2891 end if;
2892 if l_add_prin_interest <> 0 then
2893 if l_deferred_int_detail_str is not null then
2894 l_deferred_int_detail_str := l_deferred_int_detail_str || ' +<br>';
2895 end if;
2896 l_deferred_int_detail_str := l_deferred_int_detail_str || 'Current Add Int on Unpaid Prin ' || l_add_prin_int_detail_str;
2897 end if;
2898 if l_add_int_interest <> 0 then
2899 if l_deferred_int_detail_str is not null then
2900 l_deferred_int_detail_str := l_deferred_int_detail_str || ' +<br>';
2901 end if;
2902 l_deferred_int_detail_str := l_deferred_int_detail_str || 'Current Add Int on Unpaid Interest ' || l_add_int_int_detail_str;
2903 end if;
2904 if l_penal_interest <> 0 then
2905 if l_deferred_int_detail_str is not null then
2906 l_deferred_int_detail_str := l_deferred_int_detail_str || ' +<br>';
2907 end if;
2908 l_deferred_int_detail_str := l_deferred_int_detail_str || 'Current Penal Interest ' || l_penal_interest || ' (' || l_penal_int_detail_str || ')';
2909 end if;
2910 if l_early_pay_cr <> 0 then
2911 if l_deferred_int_detail_str is not null then
2912 l_deferred_int_detail_str := l_deferred_int_detail_str || ' -<br>';
2913 end if;
2914 l_deferred_int_detail_str := l_deferred_int_detail_str || 'Current Int Cr on Early Payment ' || l_early_pay_cr || ' (' || l_early_pay_cr_detail_str || ')';
2915 end if;
2916 if l_custom_tbl(i).INTEREST_AMOUNT <> 0 then
2917 if l_deferred_int_detail_str is not null then
2918 l_deferred_int_detail_str := l_deferred_int_detail_str || ' -<br>';
2919 end if;
2920 l_deferred_int_detail_str := l_deferred_int_detail_str || 'Current Frozen Interest ' || l_custom_tbl(i).INTEREST_AMOUNT;
2921 end if;
2922
2923 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_deferred_int_detail_str = ' || l_deferred_int_detail_str);
2924
2925 l_deferred_int := l_prev_deferred_int + l_norm_interest + l_add_prin_interest +
2926 l_add_int_interest + l_penal_interest - l_early_pay_cr - l_custom_tbl(i).INTEREST_AMOUNT;
2927
2928 l_norm_interest := round(l_custom_tbl(i).INTEREST_AMOUNT, l_precision);
2929 l_add_prin_interest := 0;
2930 l_add_int_interest := 0;
2931 l_penal_prin_interest := 0;
2932 l_penal_int_interest := 0;
2933 l_penal_interest := 0;
2934 l_prev_deferred_int := 0;
2935 l_prev_cap_int := 0;
2936 l_early_pay_cr := 0;
2937
2938 l_norm_int_detail_str := 'Interest amount is frozen and difference is deferred';
2939 l_add_prin_int_detail_str := null;
2940 l_add_int_int_detail_str := null;
2941 l_penal_prin_int_detail_str := null;
2942 l_penal_int_int_detail_str := null;
2943 l_penal_int_detail_str := null;
2944 l_early_pay_cr_detail_str := null;
2945
2946 elsif l_custom_tbl(i).LOCK_INT = 'C' and l_loan_details.CAPITALIZE_INT = 'Y' then
2947 -- interest is capitalized (capitalize all previously capitalized and deferred interest))
2948 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': processing capitalized interest...');
2949
2950 if l_prev_deferred_int <> 0 then
2951 if l_cap_int_detail_str is not null then
2952 l_cap_int_detail_str := l_cap_int_detail_str || ' +<br>';
2953 end if;
2954 l_cap_int_detail_str := l_cap_int_detail_str || 'Previously Deferred Interest ' || l_prev_deferred_int;
2955 end if;
2956 if l_norm_interest <> 0 then
2957 if l_cap_int_detail_str is not null then
2958 l_cap_int_detail_str := l_cap_int_detail_str || ' +<br>';
2959 end if;
2960 l_cap_int_detail_str := l_cap_int_detail_str || 'Current Normal Interest ' || l_norm_int_detail_str;
2961 end if;
2962 if l_add_prin_interest <> 0 then
2963 if l_cap_int_detail_str is not null then
2964 l_cap_int_detail_str := l_cap_int_detail_str || ' +<br>';
2965 end if;
2966 l_cap_int_detail_str := l_cap_int_detail_str || 'Current Add Int on Unpaid Prin ' || l_add_prin_int_detail_str;
2967 end if;
2968 if l_add_int_interest <> 0 then
2969 if l_cap_int_detail_str is not null then
2970 l_cap_int_detail_str := l_cap_int_detail_str || ' +<br>';
2971 end if;
2972 l_cap_int_detail_str := l_cap_int_detail_str || 'Current Add Int on Unpaid Interest ' || l_add_int_int_detail_str;
2973 end if;
2974 if l_penal_interest <> 0 then
2975 if l_cap_int_detail_str is not null then
2976 l_cap_int_detail_str := l_cap_int_detail_str || ' +<br>';
2977 end if;
2978 l_cap_int_detail_str := l_cap_int_detail_str || 'Current Penal Interest ' || l_penal_interest || ' (' || l_penal_int_detail_str || ')';
2979 end if;
2980 if l_early_pay_cr <> 0 then
2981 if l_cap_int_detail_str is not null then
2982 l_cap_int_detail_str := l_cap_int_detail_str || ' -<br>';
2983 end if;
2984 l_cap_int_detail_str := l_cap_int_detail_str || 'Current Int Cr on Early Payment ' || l_early_pay_cr || ' (' || l_early_pay_cr_detail_str || ')';
2985 end if;
2986
2987 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_cap_int_detail_str = ' || l_cap_int_detail_str);
2988
2989 l_cap_int := l_cap_int + l_prev_deferred_int + l_norm_interest + l_add_prin_interest +
2990 l_add_int_interest + l_penal_interest - l_early_pay_cr;
2991 l_custom_tbl(i).INTEREST_AMOUNT := l_prev_deferred_int + l_norm_interest + l_add_prin_interest +
2992 l_add_int_interest + l_penal_interest - l_early_pay_cr;
2993 l_norm_interest := 0;
2994 l_add_prin_interest := 0;
2995 l_add_int_interest := 0;
2996 l_penal_prin_interest := 0;
2997 l_penal_int_interest := 0;
2998 l_penal_interest := 0;
2999 l_prev_deferred_int := 0;
3000 l_prev_cap_int := 0;
3001 l_deferred_int := 0;
3002 l_early_pay_cr := 0;
3003
3004 l_norm_int_detail_str := 'Interest amount is capitalized';
3005 l_add_prin_int_detail_str := null;
3006 l_add_int_int_detail_str := null;
3007 l_penal_prin_int_detail_str := null;
3008 l_penal_int_int_detail_str := null;
3009 l_penal_int_detail_str := null;
3010 l_early_pay_cr_detail_str := null;
3011
3012 else -- interest is open (calculated by the system and billed)
3013 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': processing open interest...');
3014 l_custom_tbl(i).INTEREST_AMOUNT := l_cap_int + l_prev_deferred_int + l_norm_interest + l_add_prin_interest +
3015 l_add_int_interest + l_penal_interest - l_early_pay_cr;
3016 l_deferred_int := 0;
3017 l_cap_int := 0;
3018 end if;
3019
3020 else -- interest is frozen by user (can be overcharged or undercharged, not checked by the system)
3021 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': processing frozen interest...');
3022 l_norm_interest := round(l_custom_tbl(i).INTEREST_AMOUNT, l_precision);
3023 l_norm_int_detail_str := 'Interest amount is frozen';
3024 l_prev_deferred_int := 0;
3025 l_deferred_int := 0;
3026 l_deferred_int_detail_str := null;
3027 l_prev_cap_int := 0;
3028 end if;
3029
3030 l_custom_tbl(i).INTEREST_AMOUNT := round(l_custom_tbl(i).INTEREST_AMOUNT, l_precision);
3031
3032 if l_locked_prin > (l_remaining_balance_theory + l_increased_amount1) then
3033 l_locked_prin := l_remaining_balance_theory + l_increased_amount1;
3034 end if;
3035
3036 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': INTEREST_AMOUNT = ' || l_custom_tbl(i).INTEREST_AMOUNT);
3037 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_norm_interest = ' || l_norm_interest);
3038 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_add_prin_interest = ' || l_add_prin_interest);
3039 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_add_int_interest = ' || l_add_int_interest);
3040 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_penal_interest = ' || l_penal_interest);
3041 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_deferred_int = ' || l_deferred_int);
3042 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_cap_int = ' || l_cap_int);
3043 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_early_pay_cr = ' || l_early_pay_cr);
3044
3045 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': LOCK_PRIN = ' || l_custom_tbl(i).LOCK_PRIN);
3046 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_AMORT_METHOD = ' || l_AMORT_METHOD);
3047 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_increase_amount_instal = ' || l_increase_amount_instal);
3048
3049 -- based on amortization method calc prin or payment
3050 if l_AMORT_METHOD = 'EQUAL_PRINCIPAL' then
3051
3052 -- calc principal amount
3053 if i = 1 or l_increase_amount_instal = i or l_prev_increase_amount_instal = i then
3054 if l_num_unlocked_prin > 0 then
3055 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calculating periodic_principal...');
3056 l_periodic_principal := lns_financials.calculateEPPayment(p_loan_amount => (l_remaining_balance_theory - l_locked_prin + l_increased_amount1)
3057 ,p_num_intervals => l_num_unlocked_prin
3058 ,p_ending_balance=> 0
3059 ,p_pay_in_arrears=> true);
3060 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'periodic_principal: ' || l_periodic_principal);
3061 else
3062 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Number of unlocked principals: ' || l_num_unlocked_prin);
3063 l_periodic_principal := 0;
3064 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'periodic_principal: ' || l_periodic_principal);
3065 end if;
3066 end if;
3067
3068 if l_custom_tbl(i).LOCK_PRIN = 'N' then
3069 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT := periodic_principal');
3070 l_custom_tbl(i).PRINCIPAL_AMOUNT := l_periodic_principal;
3071 l_num_unlocked_prin := l_num_unlocked_prin - 1;
3072 end if;
3073
3074 if (l_remaining_balance_theory + l_increased_amount1) <= l_custom_tbl(i).PRINCIPAL_AMOUNT or
3075 l_num_unlocked_prin = 0
3076 then
3077 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_remaining_balance_theory <= PRINCIPAL_AMOUNT');
3078 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT := remaining_balance_theory');
3079 l_custom_tbl(i).PRINCIPAL_AMOUNT := l_remaining_balance_theory + l_increased_amount1;
3080 if (l_remaining_balance_theory + l_increased_amount1) < l_custom_tbl(i).PRINCIPAL_AMOUNT then
3081 l_custom_tbl(i).LOCK_PRIN := 'N';
3082 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Setting LOCK_PRIN = N');
3083 end if;
3084 else
3085 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'remaining_balance_theory > PRINCIPAL_AMOUNT');
3086 if i = l_custom_tbl.count then
3087 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Its the last row');
3088 -- fix for bug 8309391 - let to lock last prin row
3089 if l_custom_tbl(i).LOCK_PRIN = 'N' then
3090 if p_based_on_terms = 'CURRENT' and l_unbilled_principal > 0 then
3091 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT := unbilled_principal');
3092 l_custom_tbl(i).PRINCIPAL_AMOUNT := l_unbilled_principal;
3093 else
3094 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT := remaining_balance_theory');
3095 l_custom_tbl(i).PRINCIPAL_AMOUNT := l_remaining_balance_theory + l_increased_amount1;
3096 end if;
3097 end if;
3098 end if;
3099 end if;
3100
3101 l_custom_tbl(i).PRINCIPAL_AMOUNT := round(l_custom_tbl(i).PRINCIPAL_AMOUNT, l_precision);
3102 l_custom_tbl(i).CURRENT_TERM_PAYMENT := l_custom_tbl(i).PRINCIPAL_AMOUNT + l_custom_tbl(i).INTEREST_AMOUNT;
3103
3104 elsif l_AMORT_METHOD = 'EQUAL_PAYMENT' then
3105
3106 -- calc payment amount
3107 if (i = 1 or l_annualized_rate <> l_previous_annualized or
3108 (i > 1 and l_custom_tbl(i-1).LOCK_PRIN <> 'N' and l_custom_tbl(i).LOCK_PRIN = 'N') or
3109 (l_prev_increase_amount_instal = i or l_increase_amount_instal = i))
3110 then
3111 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calculating periodic_payment...');
3112
3113 if (l_calc_method = 'SIMPLE') then
3114
3115 l_rate_to_calculate := lns_financials.getPeriodicRate(
3116 p_payment_freq => l_payment_freq
3117 ,p_period_start_date => l_period_begin_date
3118 ,p_period_end_date => l_loan_details.maturity_Date
3119 ,p_annualized_rate => l_annualized_rate
3120 ,p_days_count_method => l_day_count_method
3121 ,p_target => 'PAYMENT');
3122
3123 elsif (l_calc_method = 'COMPOUND') then
3124
3125 l_rate_to_calculate := lns_financials.getCompoundPeriodicRate(p_compound_freq => l_compound_freq
3126 ,p_payment_freq => l_payment_freq
3127 ,p_annualized_rate => l_annualized_rate
3128 ,p_period_start_date => l_period_begin_date
3129 ,p_period_end_date => l_loan_details.maturity_Date
3130 ,p_days_count_method => l_day_count_method
3131 ,p_target => 'PAYMENT');
3132
3133 end if;
3134
3135 l_amortization_intervals := l_custom_tbl.count + 1 - i;
3136 l_periodic_payment := lns_financials.calculatePayment(p_loan_amount => (l_remaining_balance_theory + l_increased_amount1)
3137 ,p_periodic_rate => l_rate_to_calculate
3138 ,p_num_intervals => l_amortization_intervals
3139 ,p_ending_balance=> 0
3140 ,p_pay_in_arrears=> true);
3141
3142
3143 l_periodic_payment := round(l_periodic_payment, l_precision);
3144 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'periodic_payment: ' || l_periodic_payment);
3145 end if;
3146
3147 if l_custom_tbl(i).LOCK_PRIN = 'N' then
3148
3149 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'CURRENT_TERM_PAYMENT := l_periodic_payment');
3150 l_custom_tbl(i).CURRENT_TERM_PAYMENT := l_periodic_payment;
3151 l_custom_tbl(i).PRINCIPAL_AMOUNT := round(l_custom_tbl(i).CURRENT_TERM_PAYMENT - l_custom_tbl(i).INTEREST_AMOUNT, l_precision);
3152
3153 else
3154
3155 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'CURRENT_TERM_PAYMENT := PRINCIPAL_AMOUNT+INTEREST_AMOUNT');
3156 l_custom_tbl(i).CURRENT_TERM_PAYMENT := l_custom_tbl(i).PRINCIPAL_AMOUNT + l_custom_tbl(i).INTEREST_AMOUNT;
3157
3158 end if;
3159
3160 if (l_remaining_balance_theory + l_increased_amount1) <= l_custom_tbl(i).PRINCIPAL_AMOUNT then
3161 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_remaining_balance_theory <= PRINCIPAL_AMOUNT');
3162 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT := remaining_balance_theory');
3163 l_custom_tbl(i).PRINCIPAL_AMOUNT := l_remaining_balance_theory + l_increased_amount1;
3164 l_custom_tbl(i).CURRENT_TERM_PAYMENT := l_custom_tbl(i).PRINCIPAL_AMOUNT + l_custom_tbl(i).INTEREST_AMOUNT;
3165 if (l_remaining_balance_theory + l_increased_amount1) < l_custom_tbl(i).PRINCIPAL_AMOUNT then
3166 l_custom_tbl(i).LOCK_PRIN := 'N';
3167 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Setting LOCK_PRIN = N');
3168 end if;
3169 else
3170 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'remaining_balance_theory > PRINCIPAL_AMOUNT');
3171 if i = l_custom_tbl.count then
3172 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Its the last row');
3173 -- fix for bug 8309391 - let to lock last prin row
3174 if l_custom_tbl(i).LOCK_PRIN = 'N' then
3175 if p_based_on_terms = 'CURRENT' and l_unbilled_principal > 0 then
3176 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT := unbilled_principal');
3177 l_custom_tbl(i).PRINCIPAL_AMOUNT := l_unbilled_principal;
3178 else
3179 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT := remaining_balance_theory');
3180 l_custom_tbl(i).PRINCIPAL_AMOUNT := l_remaining_balance_theory + l_increased_amount1;
3181 end if;
3182 end if;
3183 l_custom_tbl(i).CURRENT_TERM_PAYMENT := l_custom_tbl(i).PRINCIPAL_AMOUNT + l_custom_tbl(i).INTEREST_AMOUNT;
3184 end if;
3185 end if;
3186
3187 elsif l_AMORT_METHOD = 'NONE' then
3188
3189 if (l_remaining_balance_theory + l_increased_amount1) <= l_custom_tbl(i).PRINCIPAL_AMOUNT then
3190 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_remaining_balance_theory <= PRINCIPAL_AMOUNT');
3191 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT := remaining_balance_theory');
3192 l_custom_tbl(i).PRINCIPAL_AMOUNT := l_remaining_balance_theory + l_increased_amount1;
3193 if (l_remaining_balance_theory + l_increased_amount1) < l_custom_tbl(i).PRINCIPAL_AMOUNT then
3194 l_custom_tbl(i).LOCK_PRIN := 'N';
3195 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Setting LOCK_PRIN = N');
3196 end if;
3197 else
3198 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'remaining_balance_theory > PRINCIPAL_AMOUNT');
3199 if i = l_custom_tbl.count then
3200 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Its the last row');
3201 -- fix for bug 8309391 - let to lock last prin row
3202 if l_custom_tbl(i).LOCK_PRIN = 'N' then
3203 if p_based_on_terms = 'CURRENT' and l_unbilled_principal > 0 then
3204 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT := unbilled_principal');
3205 l_custom_tbl(i).PRINCIPAL_AMOUNT := l_unbilled_principal;
3206 else
3207 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT := remaining_balance_theory');
3208 l_custom_tbl(i).PRINCIPAL_AMOUNT := l_remaining_balance_theory + l_increased_amount1;
3209 end if;
3210 end if;
3211 end if;
3212 end if;
3213
3214 l_custom_tbl(i).PRINCIPAL_AMOUNT := round(l_custom_tbl(i).PRINCIPAL_AMOUNT, l_precision);
3215 l_custom_tbl(i).CURRENT_TERM_PAYMENT := l_custom_tbl(i).PRINCIPAL_AMOUNT + l_custom_tbl(i).INTEREST_AMOUNT;
3216
3217 end if;
3218
3219 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': PRINCIPAL_AMOUNT = ' || l_custom_tbl(i).PRINCIPAL_AMOUNT);
3220
3221 l_custom_tbl(i).UNPAID_PRIN := l_unpaid_principal;
3222 l_custom_tbl(i).UNPAID_INT := l_unpaid_interest;
3223 l_custom_tbl(i).INSTALLMENT_BEGIN_BALANCE := l_remaining_balance_theory + l_prev_cap_int1;
3224 l_end_balance := l_remaining_balance_theory + l_increased_amount1 - l_custom_tbl(i).PRINCIPAL_AMOUNT;
3225 l_custom_tbl(i).INSTALLMENT_END_BALANCE := l_end_balance;
3226 if l_custom_tbl(i).INSTALLMENT_END_BALANCE > 0 then
3227 l_custom_tbl(i).INSTALLMENT_END_BALANCE := l_end_balance + l_prev_cap_int1;
3228 end if;
3229 l_custom_tbl(i).NORMAL_INT_AMOUNT := l_norm_interest;
3230 l_custom_tbl(i).ADD_PRIN_INT_AMOUNT := l_add_prin_interest;
3231 l_custom_tbl(i).ADD_INT_INT_AMOUNT := l_add_int_interest;
3232 l_custom_tbl(i).PENAL_INT_AMOUNT := l_penal_interest;
3233 l_custom_tbl(i).NORMAL_INT_DETAILS := l_norm_int_detail_str;
3234 l_custom_tbl(i).ADD_PRIN_INT_DETAILS := l_add_prin_int_detail_str;
3235 l_custom_tbl(i).ADD_INT_INT_DETAILS := l_add_int_int_detail_str;
3236 l_custom_tbl(i).PENAL_INT_DETAILS := l_penal_int_detail_str;
3237 l_custom_tbl(i).FUNDED_AMOUNT := l_end_funded_amount;
3238 l_custom_tbl(i).DEFERRED_INT_AMOUNT := l_deferred_int;
3239 l_custom_tbl(i).DEFERRED_INT_DETAILS := l_deferred_int_detail_str;
3240 l_custom_tbl(i).PREV_DEFERRED_INT_AMOUNT := l_prev_deferred_int;
3241 l_custom_tbl(i).CAP_INT_AMOUNT := l_cap_int;
3242 l_custom_tbl(i).CAP_INT_DETAILS := l_cap_int_detail_str;
3243 l_custom_tbl(i).PREV_CAP_INT_AMOUNT := l_prev_cap_int;
3244 l_custom_tbl(i).EARLY_PAY_CR_AMOUNT := l_early_pay_cr;
3245 l_custom_tbl(i).EARLY_PAY_CR_DETAILS := l_early_pay_cr_detail_str;
3246
3247 if l_custom_tbl(i).FUNDED_AMOUNT is null then
3248 l_custom_tbl(i).FUNDED_AMOUNT := 0;
3249 end if;
3250 l_custom_tbl(i).DISBURSEMENT_AMOUNT := l_increased_amount;
3251 if l_begin_funded_amount = 0 then
3252 l_custom_tbl(i).PRINCIPAL_PERCENT := round(l_custom_tbl(i).PRINCIPAL_AMOUNT*100/1, 2);
3253 else
3254 l_custom_tbl(i).PRINCIPAL_PERCENT := round(l_custom_tbl(i).PRINCIPAL_AMOUNT*100/l_begin_funded_amount, 2);
3255 end if;
3256
3257 if l_unbilled_principal > 0 then
3258 l_unbilled_principal := l_unbilled_principal - l_custom_tbl(i).PRINCIPAL_AMOUNT;
3259 end if;
3260
3261 -- special treatment for last principal installment if l_COMBINE_INT_WITH_LAST_PRIN = 'Y'
3262 if (l_custom_tbl(i).PRINCIPAL_AMOUNT > 0 and l_custom_tbl(i).INSTALLMENT_END_BALANCE = 0 and l_COMBINE_INT_WITH_LAST_PRIN = 'Y') or
3263 (l_custom_tbl(i).INSTALLMENT_END_BALANCE = 0 and i = l_custom_tbl.count and
3264 (l_custom_tbl(i).LOCK_INT = 'D' or l_custom_tbl(i).LOCK_INT = 'C'))
3265 then
3266
3267 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Including all previously defered and capitalized interest with last principal installment');
3268 l_custom_tbl(i).NORMAL_INT_AMOUNT := l_norm_interest1;
3269 l_custom_tbl(i).ADD_PRIN_INT_AMOUNT := l_add_prin_interest1;
3270 l_custom_tbl(i).ADD_INT_INT_AMOUNT := l_add_int_interest1;
3271 l_custom_tbl(i).PENAL_INT_AMOUNT := l_penal_interest1;
3272 l_custom_tbl(i).EARLY_PAY_CR_AMOUNT := l_early_pay_cr1;
3273 l_custom_tbl(i).NORMAL_INT_DETAILS := l_norm_int_detail_str1;
3274 l_custom_tbl(i).ADD_PRIN_INT_DETAILS := l_add_prin_int_detail_str1;
3275 l_custom_tbl(i).ADD_INT_INT_DETAILS := l_add_int_int_detail_str1;
3276 l_custom_tbl(i).PENAL_INT_DETAILS := l_penal_int_detail_str1;
3277 l_custom_tbl(i).EARLY_PAY_CR_DETAILS := l_early_pay_cr_detail_str1;
3278 l_custom_tbl(i).DEFERRED_INT_AMOUNT := 0;
3279 l_custom_tbl(i).DEFERRED_INT_DETAILS := null;
3280 l_custom_tbl(i).PREV_DEFERRED_INT_AMOUNT := l_prev_deferred_int1;
3281 l_custom_tbl(i).CAP_INT_AMOUNT := 0;
3282 l_custom_tbl(i).CAP_INT_DETAILS := null;
3283 l_custom_tbl(i).PREV_CAP_INT_AMOUNT := l_prev_cap_int1;
3284 l_custom_tbl(i).LOCK_INT := 'N';
3285
3286 l_custom_tbl(i).INTEREST_AMOUNT := l_prev_cap_int1 + l_prev_deferred_int1 + l_norm_interest1 + l_add_prin_interest1 +
3287 l_add_int_interest1 + l_penal_interest1 - l_early_pay_cr1;
3288
3289 end if;
3290
3291 l_custom_tbl(i).CURR_CAP_INT_AMOUNT := 0;
3292 if l_custom_tbl(i).LOCK_INT = 'C' and l_loan_details.CAPITALIZE_INT = 'Y' then
3293 l_custom_tbl(i).CURR_CAP_INT_AMOUNT := l_custom_tbl(i).INTEREST_AMOUNT;
3294 l_custom_tbl(i).INTEREST_AMOUNT := 0;
3295 end if;
3296
3297 if l_custom_tbl(i).CAP_INT_AMOUNT = 0 then
3298 l_custom_tbl(i).CAP_INT_DETAILS := null;
3299 end if;
3300 l_prev_cap_int := l_custom_tbl(i).CAP_INT_AMOUNT;
3301
3302 l_fees_tbl.delete;
3303 l_fee_amount := 0;
3304 l_other_amount := 0;
3305
3306 -- filling out basis table
3307 l_fee_basis_tbl(1).fee_basis_name := 'TOTAL_BAL';
3308 l_fee_basis_tbl(1).fee_basis_amount := l_custom_tbl(i).INSTALLMENT_BEGIN_BALANCE + l_custom_tbl(i).UNPAID_PRIN;
3309 l_fee_basis_tbl(2).fee_basis_name := 'ORIG_LOAN';
3310 l_fee_basis_tbl(2).fee_basis_amount := l_loan_details.requested_amount;
3311 l_fee_basis_tbl(3).fee_basis_name := 'TOTAL_DISB_AMT';
3312 l_fee_basis_tbl(3).fee_basis_amount := l_custom_tbl(i).FUNDED_AMOUNT;
3313 l_fee_basis_tbl(4).fee_basis_name := 'OVERDUE_PRIN';
3314 l_fee_basis_tbl(4).fee_basis_amount := l_custom_tbl(i).UNPAID_PRIN;
3315 l_fee_basis_tbl(5).fee_basis_name := 'OVERDUE_PRIN_INT';
3316 l_fee_basis_tbl(5).fee_basis_amount := l_custom_tbl(i).UNPAID_PRIN + l_custom_tbl(i).UNPAID_INT;
3317 l_fee_basis_tbl(6).fee_basis_name := 'IND_DISB_AMT';
3318 l_fee_basis_tbl(6).fee_basis_amount := l_custom_tbl(i).DISBURSEMENT_AMOUNT;
3319 l_fee_basis_tbl(7).fee_basis_name := 'TOTAL_UNDISB_AMT';
3320 l_fee_basis_tbl(7).fee_basis_amount := l_loan_details.requested_amount + l_loan_details.ADD_REQUESTED_AMOUNT - l_custom_tbl(i).FUNDED_AMOUNT;
3321 l_fee_basis_tbl(8).fee_basis_name := 'OVERDUE_INT';
3322 l_fee_basis_tbl(8).fee_basis_amount := l_custom_tbl(i).UNPAID_INT;
3323 l_fee_basis_tbl(9).fee_basis_name := 'CURR_LOAN';
3324 l_fee_basis_tbl(9).fee_basis_amount := l_loan_details.requested_amount + l_loan_details.ADD_REQUESTED_AMOUNT;
3325
3326 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calling LNS_FEE_ENGINE.getFeeDetails for this installment...');
3327 LNS_FEE_ENGINE.getFeeDetails(p_init_msg_list => FND_API.G_FALSE
3328 ,p_loan_id => p_loan_id
3329 ,p_installment => l_custom_tbl(i).PAYMENT_NUMBER
3330 ,p_fee_basis_tbl => l_fee_basis_tbl
3331 ,p_based_on_terms => p_based_on_terms
3332 ,p_phase => 'TERM'
3333 ,x_fees_tbl => l_fees_tbl
3334 ,x_return_status => l_return_status
3335 ,x_msg_count => l_msg_count
3336 ,x_msg_data => l_msg_data);
3337
3338 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_return_status = ' || l_return_status);
3339 if l_return_status <> 'S' then
3340 RAISE FND_API.G_EXC_ERROR;
3341 end if;
3342
3343 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': l_fees_tbl.count = ' || l_fees_tbl.count);
3344
3345 l_fee_amount := 0;
3346 for k in 1..l_fees_tbl.count loop
3347 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Fee ' || k);
3348 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': FEE_ID = ' || l_fees_tbl(k).FEE_ID);
3349 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': FEE_NAME = ' || l_fees_tbl(k).FEE_NAME);
3350 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': FEE_CATEGORY = ' || l_fees_tbl(k).FEE_CATEGORY);
3351 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': FEE_AMOUNT = ' || l_fees_tbl(k).FEE_AMOUNT);
3352 if l_fees_tbl(k).FEE_CATEGORY = 'MEMO' then
3353 l_other_amount := l_other_amount + l_fees_tbl(k).FEE_AMOUNT;
3354 else
3355 l_fee_amount := l_fee_amount + l_fees_tbl(k).FEE_AMOUNT;
3356 end if;
3357 end loop;
3358 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Total fee amount for this installment = ' || l_fee_amount);
3359
3360 l_custom_tbl(i).FEE_AMOUNT := l_fee_amount;
3361 l_custom_tbl(i).other_amount := l_other_amount;
3362 l_custom_tbl(i).CURRENT_TERM_PAYMENT := l_custom_tbl(i).PRINCIPAL_AMOUNT + l_custom_tbl(i).INTEREST_AMOUNT +
3363 l_custom_tbl(i).FEE_AMOUNT + l_custom_tbl(i).other_amount;
3364
3365 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, '********************************************');
3366 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'INSTALLMENT ' || l_custom_tbl(i).PAYMENT_NUMBER);
3367 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' ');
3368 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT: ' || l_custom_tbl(i).PRINCIPAL_AMOUNT);
3369 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_PERCENT: ' || l_custom_tbl(i).PRINCIPAL_PERCENT);
3370 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'INTEREST_AMOUNT: ' || l_custom_tbl(i).INTEREST_AMOUNT);
3371 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'FEE_AMOUNT: ' || l_custom_tbl(i).FEE_AMOUNT);
3372 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'CURRENT_TERM_PAYMENT: ' || l_custom_tbl(i).CURRENT_TERM_PAYMENT);
3373 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'INSTALLMENT_BEGIN_BALANCE: ' || l_custom_tbl(i).INSTALLMENT_BEGIN_BALANCE);
3374 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'INSTALLMENT_END_BALANCE: ' || l_custom_tbl(i).INSTALLMENT_END_BALANCE);
3375 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'remaining_balance_theory: ' || l_remaining_balance_theory);
3376 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'unbilled_principal: ' || l_unbilled_principal);
3377 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'UNPAID_PRIN: ' || l_custom_tbl(i).UNPAID_PRIN);
3378 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'UNPAID_INT: ' || l_custom_tbl(i).UNPAID_INT);
3379 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PREV_DEFERRED_INT_AMOUNT = ' || l_custom_tbl(i).PREV_DEFERRED_INT_AMOUNT );
3380 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'NORMAL_INT_AMOUNT = ' || l_custom_tbl(i).NORMAL_INT_AMOUNT );
3381 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'ADD_PRIN_INT_AMOUNT = ' || l_custom_tbl(i).ADD_PRIN_INT_AMOUNT );
3382 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'ADD_INT_INT_AMOUNT = ' || l_custom_tbl(i).ADD_INT_INT_AMOUNT );
3383 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PENAL_INT_AMOUNT = ' || l_custom_tbl(i).PENAL_INT_AMOUNT );
3384 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DEFERRED_INT_AMOUNT = ' || l_custom_tbl(i).DEFERRED_INT_AMOUNT );
3385 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'FUNDED_AMOUNT = ' || l_custom_tbl(i).FUNDED_AMOUNT );
3386 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'NORMAL_INT_DETAILS = ' || l_custom_tbl(i).NORMAL_INT_DETAILS );
3387 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'ADD_PRIN_INT_DETAILS = ' || l_custom_tbl(i).ADD_PRIN_INT_DETAILS );
3388 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'ADD_INT_INT_DETAILS = ' || l_custom_tbl(i).ADD_INT_INT_DETAILS );
3389 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PENAL_INT_DETAILS = ' || l_custom_tbl(i).PENAL_INT_DETAILS );
3390 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DEFERRED_INT_DETAILS = ' || l_custom_tbl(i).DEFERRED_INT_DETAILS );
3391 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PREV_CAP_INT_AMOUNT = ' || l_custom_tbl(i).PREV_CAP_INT_AMOUNT );
3392 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'CURR_CAP_INT_AMOUNT = ' || l_custom_tbl(i).CURR_CAP_INT_AMOUNT );
3393 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'CAP_INT_AMOUNT = ' || l_custom_tbl(i).CAP_INT_AMOUNT );
3394 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'CAP_INT_DETAILS = ' || l_custom_tbl(i).CAP_INT_DETAILS );
3395 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'EARLY_PAY_CR_AMOUNT = ' || l_custom_tbl(i).EARLY_PAY_CR_AMOUNT );
3396 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'EARLY_PAY_CR_DETAILS = ' || l_custom_tbl(i).EARLY_PAY_CR_DETAILS );
3397 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, '********************************************');
3398
3399 l_remaining_balance_theory := l_end_balance;
3400 l_sum_periodic_principal := l_sum_periodic_principal + l_custom_tbl(i).PRINCIPAL_AMOUNT;
3401 l_prev_deferred_int := l_custom_tbl(i).DEFERRED_INT_AMOUNT;
3402
3403 l_previous_annualized := l_annualized_rate;
3404 l_unpaid_principal := 0;
3405 l_unpaid_interest := 0;
3406
3407 end loop;
3408
3409 -- fix for bug 8309391 - give warning is amortization schedule does not bring loan balance to 0
3410 if l_custom_tbl(l_custom_tbl.count).INSTALLMENT_END_BALANCE > 0 then
3411 FND_MESSAGE.SET_NAME('LNS', 'LNS_LOAN_BAL_GREATER_ZERO');
3412 FND_MSG_PUB.ADD_DETAIL(p_message_type => FND_MSG_PUB.G_WARNING_MSG );
3413 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3414 end if;
3415
3416 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Output Schedule:');
3417 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PN DD RATE BB UP UI PAY PRIN(%) LP INT=N+AP+AI+PL+EI+DEF+CAP LI EB ACT');
3418 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, '--- -------- ---- ------ ------ ------ ------ --------- -- ------------------------- -- ------ ---');
3419 for i in 1..l_custom_tbl.count loop
3420 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME,
3421 l_custom_tbl(i).PAYMENT_NUMBER || ' ' ||
3422 l_custom_tbl(i).DUE_DATE || ' ' ||
3423 l_custom_tbl(i).INTEREST_RATE || ' ' ||
3424 l_custom_tbl(i).INSTALLMENT_BEGIN_BALANCE || ' ' ||
3425 l_custom_tbl(i).UNPAID_PRIN || ' ' ||
3426 l_custom_tbl(i).UNPAID_INT || ' ' ||
3427 l_custom_tbl(i).CURRENT_TERM_PAYMENT || ' ' ||
3428 l_custom_tbl(i).PRINCIPAL_AMOUNT || '(' || l_custom_tbl(i).PRINCIPAL_PERCENT || '%) ' ||
3429 l_custom_tbl(i).LOCK_PRIN || ' ' ||
3430 l_custom_tbl(i).INTEREST_AMOUNT || '=' || l_custom_tbl(i).NORMAL_INT_AMOUNT || '+' ||
3431 l_custom_tbl(i).ADD_PRIN_INT_AMOUNT || '+' || l_custom_tbl(i).ADD_INT_INT_AMOUNT || '+' ||
3432 l_custom_tbl(i).PENAL_INT_AMOUNT || '+' || l_custom_tbl(i).EARLY_PAY_CR_AMOUNT || '+' ||
3433 l_custom_tbl(i).DEFERRED_INT_AMOUNT || '+' || l_custom_tbl(i).CURR_CAP_INT_AMOUNT || ' ' ||
3434 l_custom_tbl(i).LOCK_INT || ' ' ||
3435 l_custom_tbl(i).INSTALLMENT_END_BALANCE || ' ' ||
3436 l_custom_tbl(i).ACTION);
3437 end loop;
3438 P_CUSTOM_TBL := l_CUSTOM_TBL;
3439
3440 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Output Settings:');
3441 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'AMORT_METHOD = ' || P_CUSTOM_SET_REC.AMORT_METHOD);
3442 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DESCRIPTION = ' || P_CUSTOM_SET_REC.DESCRIPTION);
3443
3444 if P_COMMIT = FND_API.G_TRUE then
3445 COMMIT WORK;
3446 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
3447 end if;
3448
3449 -- END OF BODY OF API
3450 x_return_status := FND_API.G_RET_STS_SUCCESS;
3451
3452 -- Standard call to get message count and if count is 1, get message info
3453 FND_MSG_PUB.Count_And_Get(
3454 p_encoded => FND_API.G_FALSE,
3455 p_count => x_msg_count,
3456 p_data => x_msg_data);
3457
3458 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
3459
3460 EXCEPTION
3461 WHEN FND_API.G_EXC_ERROR THEN
3462 ROLLBACK TO recalcCustomSchedule;
3463 x_return_status := FND_API.G_RET_STS_ERROR;
3464 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3465 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
3466 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3467 ROLLBACK TO recalcCustomSchedule;
3468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3469 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3470 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
3471 WHEN OTHERS THEN
3472 ROLLBACK TO recalcCustomSchedule;
3473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3474 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3475 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
3476 END;
3477
3478
3479
3480 -- This procedure saves custom schedule into db
3481 procedure saveCustomSchedule(
3482 P_API_VERSION IN NUMBER,
3483 P_INIT_MSG_LIST IN VARCHAR2,
3484 P_COMMIT IN VARCHAR2,
3485 P_VALIDATION_LEVEL IN NUMBER,
3486 P_LOAN_ID IN NUMBER,
3487 P_BASED_ON_TERMS IN VARCHAR2,
3488 P_USE_RETAINED_DATA IN VARCHAR2,
3489 P_CUSTOM_SET_REC IN OUT NOCOPY LNS_CUSTOM_PUB.custom_settings_type,
3490 P_CUSTOM_TBL IN OUT NOCOPY LNS_CUSTOM_PUB.CUSTOM_TBL,
3491 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3492 X_MSG_COUNT OUT NOCOPY NUMBER,
3493 X_MSG_DATA OUT NOCOPY VARCHAR2)
3494 IS
3495
3496 /*-----------------------------------------------------------------------+
3497 | Local Variable Declarations and initializations |
3498 +-----------------------------------------------------------------------*/
3499
3500 l_api_name CONSTANT VARCHAR2(30) := 'saveCustomSchedule';
3501 l_api_version CONSTANT NUMBER := 1.0;
3502 l_return_status VARCHAR2(1);
3503 l_msg_count NUMBER;
3504 l_msg_data VARCHAR2(32767);
3505
3506 l_loan_details LNS_CUSTOM_PUB.LOAN_DETAILS_REC;
3507 l_CUSTOM_TBL LNS_CUSTOM_PUB.CUSTOM_TBL;
3508 l_CUSTOM_SET_REC LNS_CUSTOM_PUB.custom_settings_type;
3509 l_custom_rec LNS_CUSTOM_PUB.custom_sched_type;
3510 l_loan_header_rec LNS_LOAN_HEADER_PUB.LOAN_HEADER_REC_TYPE;
3511 l_term_rec LNS_TERMS_PUB.loan_term_rec_type;
3512
3513 l_custom_sched_id number;
3514 l_object_version number;
3515 l_term_id number;
3516 l_num_installments number;
3517 i number;
3518 l_last_billed_due_date date;
3519 l_first_payment_date date;
3520 l_agreement_reason varchar2(500);
3521
3522 /*-----------------------------------------------------------------------+
3523 | Cursor Declarations |
3524 +-----------------------------------------------------------------------*/
3525
3526 CURSOR last_billed_due_date_cur(P_LOAN_ID NUMBER) IS
3527 SELECT trunc(max(DUE_DATE)),
3528 max(term.first_payment_date)
3529 FROM lns_amortization_scheds am,
3530 lns_loan_headers_all loan,
3531 lns_terms term
3532 WHERE loan.loan_id = P_LOAN_ID
3533 AND loan.loan_id = term.loan_id
3534 AND loan.loan_id = am.loan_id(+)
3535 AND (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
3536 AND am.REAMORTIZATION_AMOUNT is null
3537 AND am.PARENT_AMORTIZATION_ID is null
3538 AND nvl(am.phase, 'TERM') = 'TERM';
3539
3540 BEGIN
3541
3542 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
3543
3544 -- Standard start of API savepoint
3545 SAVEPOINT saveCustomSchedule;
3546 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Savepoint is established');
3547
3548 -- Standard call to check for call compatibility
3549 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3550 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3551 END IF;
3552
3553 -- Initialize message list if p_init_msg_list is set to TRUE
3554 IF FND_API.To_Boolean(p_init_msg_list) THEN
3555 FND_MSG_PUB.initialize;
3556 END IF;
3557
3558 -- Initialize API return status to success
3559 l_return_status := FND_API.G_RET_STS_SUCCESS;
3560
3561 -- START OF BODY OF API
3562
3563 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
3564 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID: ' || P_LOAN_ID);
3565 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_USE_RETAINED_DATA: ' || P_USE_RETAINED_DATA);
3566 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_BASED_ON_TERMS: ' || P_BASED_ON_TERMS);
3567 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'AMORT_METHOD: ' || P_CUSTOM_SET_REC.AMORT_METHOD);
3568 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DESCRIPTION: ' || P_CUSTOM_SET_REC.DESCRIPTION);
3569
3570 if P_LOAN_ID is null then
3571 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
3572 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
3573 FND_MESSAGE.SET_TOKEN('VALUE', P_LOAN_ID);
3574 FND_MSG_PUB.ADD;
3575 RAISE FND_API.G_EXC_ERROR;
3576 end if;
3577
3578 if P_BASED_ON_TERMS is null then
3579 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
3580 FND_MESSAGE.SET_TOKEN('PARAMETER', 'BASED_ON_TERMS');
3581 FND_MESSAGE.SET_TOKEN('VALUE', P_BASED_ON_TERMS);
3582 FND_MSG_PUB.ADD;
3583 RAISE FND_API.G_EXC_ERROR;
3584 end if;
3585
3586 if P_USE_RETAINED_DATA is not null and P_USE_RETAINED_DATA = 'Y' then
3587 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'G_AMORT_METHOD: ' || G_AMORT_METHOD);
3588 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'g_CUSTOM_SCHED_DESC: ' || g_CUSTOM_SCHED_DESC);
3589 l_CUSTOM_TBL := G_CUSTOM_TBL;
3590 l_CUSTOM_SET_REC.AMORT_METHOD := G_AMORT_METHOD;
3591 l_CUSTOM_SET_REC.DESCRIPTION := g_CUSTOM_SCHED_DESC;
3592 else
3593 if P_CUSTOM_SET_REC.AMORT_METHOD is null then
3594 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
3595 FND_MESSAGE.SET_TOKEN('PARAMETER', 'AMORT_METHOD');
3596 FND_MESSAGE.SET_TOKEN('VALUE', P_CUSTOM_SET_REC.AMORT_METHOD);
3597 FND_MSG_PUB.ADD;
3598 RAISE FND_API.G_EXC_ERROR;
3599 end if;
3600
3601 l_CUSTOM_TBL := P_CUSTOM_TBL;
3602 l_CUSTOM_SET_REC := P_CUSTOM_SET_REC;
3603 end if;
3604
3605 LNS_CUSTOM_PUB.recalcCustomSchedule(
3606 P_API_VERSION => 1.0,
3607 P_INIT_MSG_LIST => FND_API.G_TRUE,
3608 P_COMMIT => FND_API.G_FALSE,
3609 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
3610 P_LOAN_ID => P_LOAN_ID,
3611 P_BASED_ON_TERMS => P_BASED_ON_TERMS,
3612 P_USE_RETAINED_DATA => P_USE_RETAINED_DATA,
3613 P_CUSTOM_SET_REC => l_CUSTOM_SET_REC,
3614 P_CUSTOM_TBL => l_custom_tbl,
3615 x_return_status => l_return_status,
3616 x_msg_count => l_msg_count,
3617 x_msg_data => l_msg_data);
3618
3619 IF l_return_status <> 'S' THEN
3620 RAISE FND_API.G_EXC_ERROR;
3621 END IF;
3622
3623 l_loan_details := getLoanDetails(p_loan_Id => p_loan_id
3624 ,p_based_on_terms => P_BASED_ON_TERMS);
3625
3626 -- invalidate all existent rows in LNS_CUSTOM_PAYMNT_SCHEDS greater than l_loan_details.LAST_INSTALLMENT_BILLED
3627 update LNS_CUSTOM_PAYMNT_SCHEDS
3628 set PAYMENT_NUMBER = -1
3629 where loan_id = p_loan_id
3630 and PAYMENT_NUMBER > l_loan_details.LAST_INSTALLMENT_BILLED;
3631
3632 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updated records to -1');
3633
3634 -- insert and update valid rows
3635 for i in 1..l_custom_tbl.count loop
3636
3637 l_custom_rec := l_custom_tbl(i);
3638
3639 if l_custom_rec.LOAN_ID is null then
3640 l_custom_rec.LOAN_ID := p_loan_id;
3641 end if;
3642
3643 if l_custom_tbl(i).ACTION = 'U' and l_custom_tbl(i).CUSTOM_SCHEDULE_ID is not null then
3644
3645 -- getting info from lns_loan_headers_all
3646 select object_version_number
3647 into l_custom_rec.OBJECT_VERSION_NUMBER
3648 from LNS_CUSTOM_PAYMNT_SCHEDS
3649 where loan_id = p_loan_id and
3650 CUSTOM_SCHEDULE_ID = l_custom_tbl(i).CUSTOM_SCHEDULE_ID;
3651
3652
3653 -- call api to update rows one-by-one for compliance reasons
3654 lns_custom_pub.updateCustomSched(P_CUSTOM_REC => l_custom_rec
3655 ,x_return_status => l_return_status
3656 ,x_msg_count => l_msg_Count
3657 ,x_msg_data => l_msg_Data);
3658
3659 IF l_return_status <> 'S' THEN
3660 RAISE FND_API.G_EXC_ERROR;
3661 END IF;
3662
3663 elsif l_custom_tbl(i).ACTION is null or l_custom_tbl(i).ACTION = 'I' then
3664
3665 -- call api to update rows one-by-one for compliance reasons
3666 lns_custom_pub.createCustomSched(P_CUSTOM_REC => l_custom_rec
3667 ,x_return_status => l_return_status
3668 ,x_custom_sched_id => l_custom_sched_id
3669 ,x_msg_count => l_msg_Count
3670 ,x_msg_data => l_msg_Data);
3671
3672 IF l_return_status <> 'S' THEN
3673 RAISE FND_API.G_EXC_ERROR;
3674 END IF;
3675
3676 l_custom_tbl(i).CUSTOM_SCHEDULE_ID := l_custom_sched_id;
3677
3678 end if;
3679 /* commented out during fix for bug 9142180
3680 -- 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'
3681 if P_BASED_ON_TERMS = 'CURRENT' and i = 1 then
3682 l_term_rec.NEXT_PAYMENT_DUE_DATE := l_custom_tbl(i).DUE_DATE;
3683 l_loan_header_rec.BILLED_FLAG := 'N';
3684 end if;
3685 */
3686 end loop;
3687
3688 -- fix for bug 9142180: set NEXT_PAYMENT_DUE_DATE to last billed due date and set BILLED_FLAG to 'Y'
3689 if P_BASED_ON_TERMS = 'CURRENT' then
3690
3691 OPEN last_billed_due_date_cur(p_loan_id);
3692 FETCH last_billed_due_date_cur INTO l_last_billed_due_date, l_first_payment_date;
3693 CLOSE last_billed_due_date_cur;
3694
3695 if l_last_billed_due_date is not null then
3696 l_term_rec.NEXT_PAYMENT_DUE_DATE := l_last_billed_due_date;
3697 l_loan_header_rec.BILLED_FLAG := 'Y';
3698 else
3699 l_term_rec.NEXT_PAYMENT_DUE_DATE := l_first_payment_date;
3700 l_loan_header_rec.BILLED_FLAG := FND_API.G_MISS_CHAR;
3701 end if;
3702
3703 end if;
3704
3705 -- deleting all invalid rows
3706 delete from LNS_CUSTOM_PAYMNT_SCHEDS where loan_id = p_loan_id and PAYMENT_NUMBER = -1;
3707 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Deleted records with -1');
3708
3709 -- update all rows with action null
3710 for i in 1..l_custom_tbl.count loop
3711 l_custom_tbl(i).ACTION := null;
3712 end loop;
3713
3714 -- getting info from lns_loan_headers_all
3715 select object_version_number
3716 into l_object_version
3717 from lns_loan_headers_all
3718 where loan_id = p_loan_id;
3719
3720 -- update lns_loan_headers_all only if loan is not custom yet
3721 if l_loan_details.CUSTOM_SCHEDULE = 'N' or l_loan_header_rec.BILLED_FLAG is not null then
3722 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating lns_loan_headers_all w following values:');
3723 l_loan_header_rec.loan_id := P_LOAN_ID;
3724 l_loan_header_rec.CUSTOM_PAYMENTS_FLAG := 'Y';
3725
3726 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'CUSTOM_PAYMENTS_FLAG = ' || l_loan_header_rec.CUSTOM_PAYMENTS_FLAG);
3727 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'BILLED_FLAG = ' || l_loan_header_rec.BILLED_FLAG);
3728
3729 lns_loan_header_pub.update_loan(p_init_msg_list => FND_API.G_TRUE
3730 ,p_loan_header_rec => l_loan_header_rec
3731 ,P_OBJECT_VERSION_NUMBER => l_object_version
3732 ,X_RETURN_STATUS => l_return_status
3733 ,X_MSG_COUNT => l_msg_count
3734 ,X_MSG_DATA => l_msg_data);
3735
3736 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status: ' || l_return_status);
3737
3738 IF l_return_status = 'S' THEN
3739 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully updated lns_loan_headers_all');
3740 ELSE
3741 FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_LOAN_FAIL');
3742 FND_MSG_PUB.Add;
3743 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3744 RAISE FND_API.G_EXC_ERROR;
3745 end if;
3746 end if;
3747
3748 -- getting terms version for future update
3749 select term_id, object_version_number into l_term_id, l_object_version
3750 from lns_terms
3751 where loan_id = p_loan_id;
3752
3753 -- Updating terms
3754 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating lns_terms w following values:');
3755
3756 l_term_rec.TERM_ID := l_term_id;
3757 l_term_rec.LOAN_ID := p_loan_id;
3758 l_term_rec.PAYMENT_CALC_METHOD := 'CUSTOM';
3759 l_term_rec.CUSTOM_CALC_METHOD := l_CUSTOM_SET_REC.AMORT_METHOD;
3760 l_term_rec.CUSTOM_SCHED_DESC := l_CUSTOM_SET_REC.DESCRIPTION;
3761
3762 if l_loan_details.loan_status <> 'INCOMPLETE' and l_loan_details.CUSTOM_SCHEDULE = 'N' then
3763 l_term_rec.ORIG_PAY_CALC_METHOD := l_loan_details.PAYMENT_CALC_METHOD;
3764 elsif l_loan_details.loan_status = 'INCOMPLETE' then
3765 l_term_rec.ORIG_PAY_CALC_METHOD := FND_API.G_MISS_CHAR;
3766 end if;
3767
3768 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PAYMENT_CALC_METHOD = ' || l_term_rec.PAYMENT_CALC_METHOD);
3769 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'CUSTOM_CALC_METHOD = ' || l_term_rec.CUSTOM_CALC_METHOD);
3770 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'ORIG_PAY_CALC_METHOD = ' || l_term_rec.ORIG_PAY_CALC_METHOD);
3771 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'NEXT_PAYMENT_DUE_DATE = ' || l_term_rec.NEXT_PAYMENT_DUE_DATE);
3772 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'CUSTOM_SCHED_DESC = ' || l_term_rec.CUSTOM_SCHED_DESC);
3773
3774 LNS_TERMS_PUB.update_term(P_OBJECT_VERSION_NUMBER => l_object_version,
3775 p_init_msg_list => FND_API.G_FALSE,
3776 p_loan_term_rec => l_term_rec,
3777 X_RETURN_STATUS => l_return_status,
3778 X_MSG_COUNT => l_msg_count,
3779 X_MSG_DATA => l_msg_data);
3780
3781 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status: ' || l_return_status);
3782
3783 IF l_return_status = 'S' THEN
3784 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully update LNS_TERMS');
3785 ELSE
3786 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: LNS_TERMS_PUB.update_term returned error: ' || substr(l_msg_data,1,225));
3787 FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_TERM_FAIL');
3788 FND_MSG_PUB.Add;
3789 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3790 RAISE FND_API.G_EXC_ERROR;
3791 END IF;
3792
3793 -- getting number of installments and synching rate schedule
3794 if l_custom_tbl.count > 0 then
3795 l_num_installments := l_custom_tbl(l_custom_tbl.count).PAYMENT_NUMBER;
3796 else
3797 l_num_installments := l_loan_details.LAST_INSTALLMENT_BILLED;
3798 if l_num_installments = 0 then
3799 l_num_installments := 1;
3800 end if;
3801 end if;
3802
3803 synchRateSchedule(l_term_id, l_num_installments);
3804
3805 P_CUSTOM_TBL := l_CUSTOM_TBL;
3806 P_CUSTOM_SET_REC := l_CUSTOM_SET_REC;
3807
3808 if P_BASED_ON_TERMS = 'CURRENT' then
3809 FND_MESSAGE.SET_NAME('LNS', 'LNS_CUST_SCHED_AGR_REASON');
3810 FND_MSG_PUB.Add;
3811 l_agreement_reason := FND_MSG_PUB.Get(p_encoded => 'F');
3812 FND_MSG_PUB.DELETE_MSG(FND_MSG_PUB.COUNT_MSG);
3813
3814 LNS_REP_UTILS.STORE_LOAN_AGREEMENT_CP(p_loan_id, l_agreement_reason);
3815 end if;
3816
3817 if P_COMMIT = FND_API.G_TRUE then
3818 COMMIT WORK;
3819 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
3820 end if;
3821
3822 -- END OF BODY OF API
3823 x_return_status := FND_API.G_RET_STS_SUCCESS;
3824
3825 -- Standard call to get message count and if count is 1, get message info
3826 FND_MSG_PUB.Count_And_Get(
3827 p_encoded => FND_API.G_FALSE,
3828 p_count => x_msg_count,
3829 p_data => x_msg_data);
3830
3831 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
3832
3833 EXCEPTION
3834 WHEN FND_API.G_EXC_ERROR THEN
3835 ROLLBACK TO saveCustomSchedule;
3836 x_return_status := FND_API.G_RET_STS_ERROR;
3837 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3838 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
3839 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3840 ROLLBACK TO saveCustomSchedule;
3841 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3842 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3843 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
3844 WHEN OTHERS THEN
3845 ROLLBACK TO saveCustomSchedule;
3846 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3847 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3848 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
3849 END;
3850
3851
3852
3853 -- This procedure switches back from custom schedule to standard schedule in one shot
3854 -- Conditions: loan status is INCOMPLETE and loan has been already customized
3855 procedure uncustomizeSchedule(
3856 P_API_VERSION IN NUMBER,
3857 P_INIT_MSG_LIST IN VARCHAR2,
3858 P_COMMIT IN VARCHAR2,
3859 P_VALIDATION_LEVEL IN NUMBER,
3860 P_LOAN_ID IN NUMBER,
3861 P_ST_AMORT_METHOD IN VARCHAR2,
3862 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3863 X_MSG_COUNT OUT NOCOPY NUMBER,
3864 X_MSG_DATA OUT NOCOPY VARCHAR2)
3865 IS
3866
3867 /*-----------------------------------------------------------------------+
3868 | Local Variable Declarations and initializations |
3869 +-----------------------------------------------------------------------*/
3870
3871 l_api_name CONSTANT VARCHAR2(30) := 'uncustomizeSchedule';
3872 l_api_version CONSTANT NUMBER := 1.0;
3873 l_return_status VARCHAR2(1);
3874 l_msg_count NUMBER;
3875 l_msg_data VARCHAR2(32767);
3876
3877 l_loan_details LNS_CUSTOM_PUB.LOAN_DETAILS_REC;
3878 l_loan_header_rec LNS_LOAN_HEADER_PUB.LOAN_HEADER_REC_TYPE;
3879 l_term_rec LNS_TERMS_PUB.loan_term_rec_type;
3880
3881 l_object_version number;
3882 l_term_id number;
3883 l_BASED_ON_TERMS varchar2(30);
3884 l_num_installments number;
3885
3886 /*-----------------------------------------------------------------------+
3887 | Cursor Declarations |
3888 +-----------------------------------------------------------------------*/
3889
3890 BEGIN
3891
3892 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
3893
3894 -- Standard start of API savepoint
3895 SAVEPOINT uncustomizeSchedule;
3896 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Savepoint is established');
3897
3898 -- Standard call to check for call compatibility
3899 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3900 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3901 END IF;
3902
3903 -- Initialize message list if p_init_msg_list is set to TRUE
3904 IF FND_API.To_Boolean(p_init_msg_list) THEN
3905 FND_MSG_PUB.initialize;
3906 END IF;
3907
3908 -- Initialize API return status to success
3909 l_return_status := FND_API.G_RET_STS_SUCCESS;
3910
3911 -- START OF BODY OF API
3912
3913 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
3914 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID: ' || P_LOAN_ID);
3915 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_ST_AMORT_METHOD: ' || P_ST_AMORT_METHOD);
3916
3917 if P_LOAN_ID is null then
3918 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
3919 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
3920 FND_MESSAGE.SET_TOKEN('VALUE', P_LOAN_ID);
3921 FND_MSG_PUB.ADD;
3922 RAISE FND_API.G_EXC_ERROR;
3923 end if;
3924
3925 l_BASED_ON_TERMS := 'ORIGINATION';
3926 l_loan_details := getLoanDetails(p_loan_Id => p_loan_id
3927 ,p_based_on_terms => l_based_on_terms);
3928
3929 -- allow to save initial custom schedule only if this loan is in INCOMPLETE status and is not customized yet
3930 -- if l_loan_details.loan_status = 'INCOMPLETE' and l_loan_details.CUSTOM_SCHEDULE = 'Y' then
3931 if l_loan_details.loan_status = 'INCOMPLETE' then
3932
3933 delete from LNS_CUSTOM_PAYMNT_SCHEDS
3934 where loan_id = p_loan_id;
3935 /*
3936 -- getting info from lns_loan_headers_all
3937 select object_version_number
3938 into l_object_version
3939 from lns_loan_headers_all
3940 where loan_id = p_loan_id;
3941
3942 -- update lns_loan_headers_all only if loan is not custom yet
3943 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating lns_loan_headers_all set custom = N');
3944 l_loan_header_rec.loan_id := P_LOAN_ID;
3945 l_loan_header_rec.CUSTOM_PAYMENTS_FLAG := 'N';
3946
3947 lns_loan_header_pub.update_loan(p_init_msg_list => FND_API.G_TRUE
3948 ,p_loan_header_rec => l_loan_header_rec
3949 ,P_OBJECT_VERSION_NUMBER => l_object_version
3950 ,X_RETURN_STATUS => l_return_status
3951 ,X_MSG_COUNT => l_msg_count
3952 ,X_MSG_DATA => l_msg_data);
3953
3954 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'l_return_status: ' || l_return_status);
3955
3956 IF l_return_status = 'S' THEN
3957 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully updated lns_loan_headers_all');
3958 ELSE
3959 FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_LOAN_FAIL');
3960 FND_MSG_PUB.Add;
3961 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3962 RAISE FND_API.G_EXC_ERROR;
3963 end if;
3964
3965 -- getting terms version for future update
3966 select term_id, object_version_number into l_term_id, l_object_version
3967 from lns_terms
3968 where loan_id = p_loan_id;
3969
3970 -- Updating terms
3971 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating lns_terms w following values:');
3972
3973 l_term_rec.TERM_ID := l_term_id;
3974 l_term_rec.LOAN_ID := p_loan_id;
3975 l_term_rec.PAYMENT_CALC_METHOD := P_ST_AMORT_METHOD;
3976 l_term_rec.CUSTOM_CALC_METHOD := FND_API.G_MISS_CHAR;
3977 l_term_rec.ORIG_PAY_CALC_METHOD := FND_API.G_MISS_CHAR;
3978
3979 LNS_TERMS_PUB.update_term(P_OBJECT_VERSION_NUMBER => l_object_version,
3980 p_init_msg_list => FND_API.G_FALSE,
3981 p_loan_term_rec => l_term_rec,
3982 X_RETURN_STATUS => l_return_status,
3983 X_MSG_COUNT => l_msg_count,
3984 X_MSG_DATA => l_msg_data);
3985
3986 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status: ' || l_return_status);
3987
3988 IF l_return_status = 'S' THEN
3989 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully update LNS_TERMS');
3990 ELSE
3991 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: LNS_TERMS_PUB.update_term returned error: ' || substr(l_msg_data,1,225));
3992 FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_TERM_FAIL');
3993 FND_MSG_PUB.Add;
3994 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3995 RAISE FND_API.G_EXC_ERROR;
3996 END IF;
3997
3998 -- synching rate schedule
3999 l_num_installments := LNS_FIN_UTILS.getNumberInstallments(p_loan_id);
4000 synchRateSchedule(l_term_id, l_num_installments);
4001 */
4002 else
4003 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Nothing to update');
4004 end if;
4005
4006 if P_COMMIT = FND_API.G_TRUE then
4007 COMMIT WORK;
4008 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
4009 end if;
4010
4011 -- END OF BODY OF API
4012 x_return_status := FND_API.G_RET_STS_SUCCESS;
4013
4014 -- Standard call to get message count and if count is 1, get message info
4015 FND_MSG_PUB.Count_And_Get(
4016 p_encoded => FND_API.G_FALSE,
4017 p_count => x_msg_count,
4018 p_data => x_msg_data);
4019
4020 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
4021
4022 EXCEPTION
4023 WHEN FND_API.G_EXC_ERROR THEN
4024 ROLLBACK TO uncustomizeSchedule;
4025 x_return_status := FND_API.G_RET_STS_ERROR;
4026 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4027 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
4028 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4029 ROLLBACK TO uncustomizeSchedule;
4030 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4031 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4032 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
4033 WHEN OTHERS THEN
4034 ROLLBACK TO uncustomizeSchedule;
4035 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4036 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4037 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
4038 END;
4039
4040
4041
4042 -- This procedure generates custom schedule from clob and save it into db
4043 procedure customizeSchedule(
4044 P_API_VERSION IN NUMBER,
4045 P_INIT_MSG_LIST IN VARCHAR2,
4046 P_COMMIT IN VARCHAR2,
4047 P_VALIDATION_LEVEL IN NUMBER,
4048 P_LOAN_ID IN NUMBER,
4049 P_CLOB IN CLOB,
4050 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4051 X_MSG_COUNT OUT NOCOPY NUMBER,
4052 X_MSG_DATA OUT NOCOPY VARCHAR2)
4053 IS
4054
4055 /*-----------------------------------------------------------------------+
4056 | Local Variable Declarations and initializations |
4057 +-----------------------------------------------------------------------*/
4058
4059 l_api_name CONSTANT VARCHAR2(30) := 'customizeSchedule';
4060 l_CUSTOM_TBL LNS_CUSTOM_PUB.CUSTOM_TBL;
4061 l_CUSTOM_SET_REC LNS_CUSTOM_PUB.custom_settings_type;
4062
4063 /*-----------------------------------------------------------------------+
4064 | Cursor Declarations |
4065 +-----------------------------------------------------------------------*/
4066
4067 BEGIN
4068
4069 customizeSchedule(
4070 P_API_VERSION => 1.0,
4071 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
4072 P_COMMIT => P_COMMIT,
4073 P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
4074 P_LOAN_ID => P_LOAN_ID,
4075 P_CLOB => P_CLOB,
4076 X_CUSTOM_SET_REC => l_CUSTOM_SET_REC,
4077 X_CUSTOM_TBL => l_CUSTOM_TBL,
4078 x_return_status => x_return_status,
4079 x_msg_count => x_msg_count,
4080 x_msg_data => x_msg_data);
4081
4082 END;
4083
4084
4085
4086 procedure customizeSchedule(
4087 P_API_VERSION IN NUMBER,
4088 P_INIT_MSG_LIST IN VARCHAR2,
4089 P_COMMIT IN VARCHAR2,
4090 P_VALIDATION_LEVEL IN NUMBER,
4091 P_LOAN_ID IN NUMBER,
4092 P_CLOB IN CLOB,
4093 X_CUSTOM_SET_REC OUT NOCOPY LNS_CUSTOM_PUB.custom_settings_type,
4094 X_CUSTOM_TBL OUT NOCOPY LNS_CUSTOM_PUB.CUSTOM_TBL,
4095 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4096 X_MSG_COUNT OUT NOCOPY NUMBER,
4097 X_MSG_DATA OUT NOCOPY VARCHAR2)
4098 IS
4099
4100 /*-----------------------------------------------------------------------+
4101 | Local Variable Declarations and initializations |
4102 +-----------------------------------------------------------------------*/
4103
4104 l_api_name CONSTANT VARCHAR2(30) := 'customizeSchedule';
4105 l_api_version CONSTANT NUMBER := 1.0;
4106 l_return_status VARCHAR2(1);
4107 l_msg_count NUMBER;
4108 l_msg_data VARCHAR2(32767);
4109
4110 l_CUSTOM_TBL LNS_CUSTOM_PUB.CUSTOM_TBL;
4111 l_CUSTOM_SET_REC LNS_CUSTOM_PUB.custom_settings_type;
4112 l_AMORT_METHOD varchar2(30);
4113 l_BASED_ON_TERMS varchar2(30);
4114 l_loan_status varchar2(60);
4115
4116 /*-----------------------------------------------------------------------+
4117 | Cursor Declarations |
4118 +-----------------------------------------------------------------------*/
4119
4120 BEGIN
4121
4122 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
4123
4124 -- Standard start of API savepoint
4125 SAVEPOINT customizeSchedule;
4126 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Savepoint is established');
4127
4128 -- Standard call to check for call compatibility
4129 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4130 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4131 END IF;
4132
4133 -- Initialize message list if p_init_msg_list is set to TRUE
4134 IF FND_API.To_Boolean(p_init_msg_list) THEN
4135 FND_MSG_PUB.initialize;
4136 END IF;
4137
4138 -- Initialize API return status to success
4139 l_return_status := FND_API.G_RET_STS_SUCCESS;
4140
4141 -- START OF BODY OF API
4142
4143 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
4144 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID: ' || P_LOAN_ID);
4145
4146 if P_LOAN_ID is null then
4147 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
4148 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
4149 FND_MESSAGE.SET_TOKEN('VALUE', P_LOAN_ID);
4150 FND_MSG_PUB.ADD;
4151 RAISE FND_API.G_EXC_ERROR;
4152 end if;
4153
4154 parseClob(
4155 P_API_VERSION => 1.0,
4156 P_INIT_MSG_LIST => FND_API.G_TRUE,
4157 P_COMMIT => FND_API.G_FALSE,
4158 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
4159 P_CLOB => P_CLOB,
4160 P_RETAIN_DATA => 'Y',
4161 X_CUSTOM_SET_REC => l_CUSTOM_SET_REC,
4162 X_CUSTOM_TBL => l_CUSTOM_TBL,
4163 x_return_status => l_return_status,
4164 x_msg_count => l_msg_count,
4165 x_msg_data => l_msg_data);
4166
4167 IF l_return_status <> 'S' THEN
4168 RAISE FND_API.G_EXC_ERROR;
4169 END IF;
4170 /*
4171 -- getting CUSTOM_CALC_METHOD from lns_terms
4172 select CUSTOM_CALC_METHOD into l_AMORT_METHOD
4173 from lns_terms
4174 where loan_id = P_LOAN_ID;
4175 */
4176 select lh.loan_status into l_loan_status
4177 from lns_loan_headers_all lh
4178 where lh.loan_id = P_LOAN_ID;
4179
4180 if(l_loan_status = 'INCOMPLETE' or l_loan_status = 'PENDING') then
4181 l_BASED_ON_TERMS := 'ORIGINAL';
4182 else
4183 l_BASED_ON_TERMS := 'CURRENT';
4184 end if;
4185
4186 -- immediatly save it
4187 LNS_CUSTOM_PUB.saveCustomSchedule(
4188 P_API_VERSION => 1.0,
4189 P_INIT_MSG_LIST => FND_API.G_TRUE,
4190 P_COMMIT => FND_API.G_FALSE,
4191 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
4192 P_LOAN_ID => P_LOAN_ID,
4193 P_BASED_ON_TERMS => l_BASED_ON_TERMS,
4194 P_USE_RETAINED_DATA => 'Y',
4195 P_CUSTOM_SET_REC => l_CUSTOM_SET_REC,
4196 P_CUSTOM_TBL => l_CUSTOM_TBL,
4197 x_return_status => l_return_status,
4198 x_msg_count => l_msg_count,
4199 x_msg_data => l_msg_data);
4200
4201 IF l_return_status <> 'S' THEN
4202 RAISE FND_API.G_EXC_ERROR;
4203 END IF;
4204
4205 -- clear retained date
4206 LNS_CUSTOM_PUB.clearRetainedSchedule(
4207 P_API_VERSION => 1.0,
4208 P_INIT_MSG_LIST => FND_API.G_TRUE,
4209 P_COMMIT => FND_API.G_FALSE,
4210 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
4211 x_return_status => l_return_status,
4212 x_msg_count => l_msg_count,
4213 x_msg_data => l_msg_data);
4214
4215 IF l_return_status <> 'S' THEN
4216 RAISE FND_API.G_EXC_ERROR;
4217 END IF;
4218
4219 X_CUSTOM_TBL := l_CUSTOM_TBL;
4220 if P_COMMIT = FND_API.G_TRUE then
4221 COMMIT WORK;
4222 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
4223 end if;
4224
4225 -- END OF BODY OF API
4226 x_return_status := FND_API.G_RET_STS_SUCCESS;
4227
4228 -- Standard call to get message count and if count is 1, get message info
4229 FND_MSG_PUB.Count_And_Get(
4230 p_encoded => FND_API.G_FALSE,
4231 p_count => x_msg_count,
4232 p_data => x_msg_data);
4233
4234 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
4235
4236 EXCEPTION
4237 WHEN FND_API.G_EXC_ERROR THEN
4238 ROLLBACK TO customizeSchedule;
4239 x_return_status := FND_API.G_RET_STS_ERROR;
4240 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4241 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
4242 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4243 ROLLBACK TO customizeSchedule;
4244 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4245 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4246 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
4247 WHEN OTHERS THEN
4248 ROLLBACK TO customizeSchedule;
4249 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4250 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4251 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
4252 END;
4253
4254
4255
4256 /*
4257 This funciton will ensure the rows in the custom tbl are ordered by due date.
4258 Will validate that due dates are unique
4259 Return 1 - success; 0 - failed
4260 */
4261 function shiftRowsByDate(P_OLD_DUE_DATE IN DATE,
4262 P_NEW_DUE_DATE IN DATE,
4263 p_custom_tbl in out nocopy LNS_CUSTOM_PUB.custom_tbl) return NUMBER
4264 is
4265 l_custom_tbl LNS_CUSTOM_PUB.custom_tbl;
4266 i number;
4267 l_found boolean;
4268 l_shift_from_row number;
4269 l_month_diff number;
4270 l_day_diff number;
4271 l_old_date date;
4272
4273 begin
4274 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'shiftRowsByDate +');
4275
4276 if P_OLD_DUE_DATE is null then
4277 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_OLD_DUE_DATE is null. Exiting');
4278 return 0;
4279 end if;
4280
4281 if P_OLD_DUE_DATE is null or P_OLD_DUE_DATE = P_NEW_DUE_DATE then
4282 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_OLD_DUE_DATE = P_NEW_DUE_DATE. Exiting');
4283 return 1;
4284 end if;
4285
4286 l_custom_tbl := p_custom_tbl;
4287 l_found := false;
4288
4289 -- find row from which we will shift schedule
4290 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Looking for start shift row...');
4291 for i in 1..l_custom_tbl.count loop
4292 if l_custom_tbl(i).DUE_DATE = P_NEW_DUE_DATE then
4293 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Found row ' || i);
4294 l_shift_from_row := i;
4295 l_found := true;
4296
4297 l_month_diff := months_between(P_NEW_DUE_DATE, P_OLD_DUE_DATE);
4298 if sign(l_month_diff) = -1 then
4299 l_month_diff := ceil(l_month_diff);
4300 elsif sign(l_month_diff) = 1 then
4301 l_month_diff := floor(l_month_diff);
4302 end if;
4303 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_month_diff: ' || l_month_diff);
4304
4305 l_day_diff := P_NEW_DUE_DATE - add_months(P_OLD_DUE_DATE, l_month_diff);
4306 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_day_diff: ' || l_day_diff);
4307 exit;
4308 end if;
4309 end loop;
4310
4311 if l_found = false then
4312 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'No start shift row found. Exiting');
4313 return 1;
4314 end if;
4315
4316 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Shifting dates...');
4317 for i in l_shift_from_row..l_custom_tbl.count loop
4318
4319 if i = l_shift_from_row then
4320 l_old_date := P_OLD_DUE_DATE;
4321 else
4322 l_old_date := l_custom_tbl(i).DUE_DATE;
4323 l_custom_tbl(i).DUE_DATE := add_months(l_old_date, l_month_diff) + l_day_diff;
4324 end if;
4325 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_old_date || ' -> ' || l_custom_tbl(i).DUE_DATE);
4326
4327 end loop;
4328 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done shifting.');
4329
4330 p_custom_tbl := l_custom_tbl;
4331 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'shiftRowsByDate -');
4332
4333 return 1;
4334 end;
4335
4336
4337
4338
4339 -- This procedure recalculates custom schedule with shifting all subsequent due dates on a single due date change
4340 procedure shiftCustomSchedule(
4341 P_API_VERSION IN NUMBER,
4342 P_INIT_MSG_LIST IN VARCHAR2,
4343 P_COMMIT IN VARCHAR2,
4344 P_VALIDATION_LEVEL IN NUMBER,
4345 P_LOAN_ID IN NUMBER,
4346 P_OLD_DUE_DATE IN DATE,
4347 P_NEW_DUE_DATE IN DATE,
4348 P_BASED_ON_TERMS IN VARCHAR2,
4349 P_CUSTOM_SET_REC IN OUT NOCOPY LNS_CUSTOM_PUB.custom_settings_type,
4350 P_CUSTOM_TBL IN OUT NOCOPY LNS_CUSTOM_PUB.CUSTOM_TBL,
4351 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4352 X_MSG_COUNT OUT NOCOPY NUMBER,
4353 X_MSG_DATA OUT NOCOPY VARCHAR2)
4354 IS
4355
4356 /*-----------------------------------------------------------------------+
4357 | Local Variable Declarations and initializations |
4358 +-----------------------------------------------------------------------*/
4359
4360 l_api_name CONSTANT VARCHAR2(30) := 'shiftCustomSchedule';
4361 l_api_version CONSTANT NUMBER := 1.0;
4362 l_return_status VARCHAR2(1);
4363 l_msg_count NUMBER;
4364 l_msg_data VARCHAR2(32767);
4365
4366 l_custom_tbl LNS_CUSTOM_PUB.CUSTOM_TBL;
4367 l_CUSTOM_SET_REC LNS_CUSTOM_PUB.custom_settings_type;
4368 l_return number;
4369
4370 /*-----------------------------------------------------------------------+
4371 | Cursor Declarations |
4372 +-----------------------------------------------------------------------*/
4373
4374 BEGIN
4375
4376 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
4377
4378 -- Standard start of API savepoint
4379 SAVEPOINT shiftCustomSchedule;
4380 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Savepoint is established');
4381
4382 -- Standard call to check for call compatibility
4383 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4384 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4385 END IF;
4386
4387 -- Initialize message list if p_init_msg_list is set to TRUE
4388 IF FND_API.To_Boolean(p_init_msg_list) THEN
4389 FND_MSG_PUB.initialize;
4390 END IF;
4391
4392 -- Initialize API return status to success
4393 l_return_status := FND_API.G_RET_STS_SUCCESS;
4394
4395 -- START OF BODY OF API
4396
4397 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
4398 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID: ' || P_LOAN_ID);
4399 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_OLD_DUE_DATE: ' || P_OLD_DUE_DATE);
4400 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_NEW_DUE_DATE: ' || P_NEW_DUE_DATE);
4401 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_BASED_ON_TERMS: ' || P_BASED_ON_TERMS);
4402 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'AMORT_METHOD: ' || P_CUSTOM_SET_REC.AMORT_METHOD);
4403 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DESCRIPTION: ' || P_CUSTOM_SET_REC.DESCRIPTION);
4404
4405 if P_LOAN_ID is null then
4406 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
4407 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
4408 FND_MESSAGE.SET_TOKEN('VALUE', P_LOAN_ID);
4409 FND_MSG_PUB.ADD;
4410 RAISE FND_API.G_EXC_ERROR;
4411 end if;
4412
4413 if P_BASED_ON_TERMS is null then
4414 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
4415 FND_MESSAGE.SET_TOKEN('PARAMETER', 'BASED_ON_TERMS');
4416 FND_MESSAGE.SET_TOKEN('VALUE', P_BASED_ON_TERMS);
4417 FND_MSG_PUB.ADD;
4418 RAISE FND_API.G_EXC_ERROR;
4419 end if;
4420
4421 if P_CUSTOM_SET_REC.AMORT_METHOD is null then
4422 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
4423 FND_MESSAGE.SET_TOKEN('PARAMETER', 'AMORT_METHOD');
4424 FND_MESSAGE.SET_TOKEN('VALUE', P_CUSTOM_SET_REC.AMORT_METHOD);
4425 FND_MSG_PUB.ADD;
4426 RAISE FND_API.G_EXC_ERROR;
4427 end if;
4428
4429 if P_NEW_DUE_DATE is null then
4430 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
4431 FND_MESSAGE.SET_TOKEN('PARAMETER', 'NEW_DUE_DATE');
4432 FND_MESSAGE.SET_TOKEN('VALUE', P_NEW_DUE_DATE);
4433 FND_MSG_PUB.ADD;
4434 RAISE FND_API.G_EXC_ERROR;
4435 end if;
4436
4437 l_CUSTOM_TBL := P_CUSTOM_TBL;
4438 l_CUSTOM_SET_REC := P_CUSTOM_SET_REC;
4439
4440 filterCustSchedule(l_custom_tbl);
4441 if l_custom_tbl.count = 0 then
4442 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Custom amortization is empty.');
4443 FND_MESSAGE.SET_NAME('LNS', 'LNS_CUST_AMORT_EMPTY');
4444 FND_MSG_PUB.Add;
4445 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
4446 RAISE FND_API.G_EXC_ERROR;
4447 end if;
4448
4449 l_return := shiftRowsByDate(P_OLD_DUE_DATE => P_OLD_DUE_DATE,
4450 P_NEW_DUE_DATE => P_NEW_DUE_DATE,
4451 P_CUSTOM_TBL => l_CUSTOM_TBL);
4452
4453 if l_return = 0 then
4454 return;
4455 end if;
4456
4457 LNS_CUSTOM_PUB.recalcCustomSchedule(
4458 P_API_VERSION => 1.0,
4459 P_INIT_MSG_LIST => FND_API.G_TRUE,
4460 P_COMMIT => FND_API.G_FALSE,
4461 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
4462 P_LOAN_ID => P_LOAN_ID,
4463 P_BASED_ON_TERMS => P_BASED_ON_TERMS,
4464 P_USE_RETAINED_DATA => 'N',
4465 P_CUSTOM_SET_REC => l_CUSTOM_SET_REC,
4466 P_CUSTOM_TBL => l_custom_tbl,
4467 x_return_status => l_return_status,
4468 x_msg_count => l_msg_count,
4469 x_msg_data => l_msg_data);
4470
4471 IF l_return_status <> 'S' THEN
4472 RAISE FND_API.G_EXC_ERROR;
4473 END IF;
4474
4475 P_CUSTOM_TBL := l_CUSTOM_TBL;
4476
4477 if P_COMMIT = FND_API.G_TRUE then
4478 COMMIT WORK;
4479 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
4480 end if;
4481
4482 -- END OF BODY OF API
4483 x_return_status := FND_API.G_RET_STS_SUCCESS;
4484
4485 -- Standard call to get message count and if count is 1, get message info
4486 FND_MSG_PUB.Count_And_Get(
4487 p_encoded => FND_API.G_FALSE,
4488 p_count => x_msg_count,
4489 p_data => x_msg_data);
4490
4491 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
4492
4493 EXCEPTION
4494 WHEN FND_API.G_EXC_ERROR THEN
4495 ROLLBACK TO shiftCustomSchedule;
4496 x_return_status := FND_API.G_RET_STS_ERROR;
4497 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4498 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
4499 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4500 ROLLBACK TO shiftCustomSchedule;
4501 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4502 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4503 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
4504 WHEN OTHERS THEN
4505 ROLLBACK TO shiftCustomSchedule;
4506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4507 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4508 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
4509 END;
4510
4511
4512 procedure reBuildCustomdSchedule(
4513 P_API_VERSION IN NUMBER,
4514 P_INIT_MSG_LIST IN VARCHAR2,
4515 P_COMMIT IN VARCHAR2,
4516 P_VALIDATION_LEVEL IN NUMBER,
4517 P_LOAN_ID IN NUMBER,
4518 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4519 X_MSG_COUNT OUT NOCOPY NUMBER,
4520 X_MSG_DATA OUT NOCOPY VARCHAR2)
4521 IS
4522
4523 /*-----------------------------------------------------------------------+
4524 | Local Variable Declarations and initializations |
4525 +-----------------------------------------------------------------------*/
4526
4527 l_api_name CONSTANT VARCHAR2(30) := 'reBuildCustomdSchedule';
4528 l_api_version CONSTANT NUMBER := 1.0;
4529 l_return_status VARCHAR2(1);
4530 l_msg_count NUMBER;
4531 l_msg_data VARCHAR2(32767);
4532
4533 l_loan_details LNS_CUSTOM_PUB.LOAN_DETAILS_REC;
4534 l_loan_header_rec LNS_LOAN_HEADER_PUB.LOAN_HEADER_REC_TYPE;
4535 l_term_rec LNS_TERMS_PUB.loan_term_rec_type;
4536
4537 l_object_version number;
4538 l_term_id number;
4539 l_BASED_ON_TERMS varchar2(30);
4540 l_num_installments number;
4541 l_maturity_date DATE;
4542
4543 /*-----------------------------------------------------------------------+
4544 | Cursor Declarations |
4545 +-----------------------------------------------------------------------*/
4546
4547 BEGIN
4548
4549 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
4550
4551 -- Standard start of API savepoint
4552 SAVEPOINT reBuildCustomdSchedule;
4553 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Savepoint is established');
4554
4555 -- Standard call to check for call compatibility
4556 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4557 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4558 END IF;
4559
4560 -- Initialize message list if p_init_msg_list is set to TRUE
4561 IF FND_API.To_Boolean(p_init_msg_list) THEN
4562 FND_MSG_PUB.initialize;
4563 END IF;
4564
4565 -- Initialize API return status to success
4566 l_return_status := FND_API.G_RET_STS_SUCCESS;
4567
4568 -- START OF BODY OF API
4569
4570 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
4571 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID: ' || P_LOAN_ID);
4572
4573 if P_LOAN_ID is null then
4574 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
4575 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
4576 FND_MESSAGE.SET_TOKEN('VALUE', P_LOAN_ID);
4577 FND_MSG_PUB.ADD;
4578 RAISE FND_API.G_EXC_ERROR;
4579 end if;
4580
4581 l_BASED_ON_TERMS := 'ORIGINATION';
4582 l_loan_details := getLoanDetails(p_loan_Id => p_loan_id
4583 ,p_based_on_terms => l_based_on_terms);
4584
4585 l_maturity_date := l_loan_details.MATURITY_DATE;
4586
4587 -- allow to reBuild custom schedule only if this loan is in INCOMPLETE status
4588 --if l_loan_details.loan_status = 'INCOMPLETE' then
4589 if l_loan_details.loan_status = 'INCOMPLETE' and l_loan_details.CUSTOM_SCHEDULE = 'Y' then
4590
4591 delete from LNS_CUSTOM_PAYMNT_SCHEDS
4592 where loan_id = p_loan_id
4593 and due_date > l_maturity_date;
4594
4595 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Cust Rows might be deleted.');
4596
4597 else
4598 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Nothing to update');
4599 end if;
4600
4601 if P_COMMIT = FND_API.G_TRUE then
4602 COMMIT WORK;
4603 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
4604 end if;
4605
4606 -- END OF BODY OF API
4607 x_return_status := FND_API.G_RET_STS_SUCCESS;
4608
4609 -- Standard call to get message count and if count is 1, get message info
4610 FND_MSG_PUB.Count_And_Get(
4611 p_encoded => FND_API.G_FALSE,
4612 p_count => x_msg_count,
4613 p_data => x_msg_data);
4614
4615 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
4616
4617 EXCEPTION
4618 WHEN FND_API.G_EXC_ERROR THEN
4619 ROLLBACK TO reBuildCustomdSchedule;
4620 x_return_status := FND_API.G_RET_STS_ERROR;
4621 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4622 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
4623 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4624 ROLLBACK TO reBuildCustomdSchedule;
4625 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4626 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4627 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
4628 WHEN OTHERS THEN
4629 ROLLBACK TO reBuildCustomdSchedule;
4630 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4631 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4632 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
4633 END;
4634
4635
4636 -- This procedure builds custom payment schedule and returns LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL table
4637 function buildCustomPaySchedule(P_LOAN_ID IN NUMBER) return LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL
4638 IS
4639
4640 /*-----------------------------------------------------------------------+
4641 | Local Variable Declarations and initializations |
4642 +-----------------------------------------------------------------------*/
4643
4644 l_api_name CONSTANT VARCHAR2(30) := 'buildCustomPaySchedule';
4645 l_payment_schedule LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
4646 l_loan_details LNS_CUSTOM_PUB.LOAN_DETAILS_REC;
4647 l_due_date date;
4648 l_payment_number number;
4649 i number;
4650
4651 /*-----------------------------------------------------------------------+
4652 | Cursor Declarations |
4653 +-----------------------------------------------------------------------*/
4654
4655 -- cursor to get due_dates of already built installments
4656 cursor c_built_payments(p_loan_id NUMBER) IS
4657 select PAYMENT_NUMBER, DUE_DATE
4658 from lns_amortization_scheds
4659 where loan_id = p_loan_id
4660 and (REVERSED_FLAG is null or REVERSED_FLAG = 'N')
4661 and parent_amortization_id is null
4662 and REAMORTIZATION_AMOUNT is null
4663 and nvl(phase, 'TERM') = 'TERM'
4664 order by PAYMENT_NUMBER;
4665
4666 -- cursor to load custom schedule
4667 cursor c_load_sched(p_loan_id NUMBER, p_min_payment NUMBER) IS
4668 select PAYMENT_NUMBER, DUE_DATE
4669 from LNS_CUSTOM_PAYMNT_SCHEDS
4670 where loan_id = p_loan_id
4671 and PAYMENT_NUMBER >= p_min_payment
4672 order by PAYMENT_NUMBER;
4673
4674 BEGIN
4675
4676 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
4677 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
4678 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'P_LOAN_ID: ' || P_LOAN_ID);
4679
4680 l_loan_details := getLoanDetails(p_loan_id => p_loan_id
4681 ,p_based_on_terms => 'CURRENT');
4682
4683 if l_loan_details.CUSTOM_SCHEDULE = 'N' then
4684 return l_payment_schedule;
4685 end if;
4686
4687 i := 1;
4688 if l_loan_details.LAST_INSTALLMENT_BILLED > 0 then
4689
4690 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Loading dates from lns_amortization_scheds:');
4691 OPEN c_built_payments(p_loan_id);
4692 LOOP
4693
4694 FETCH c_built_payments INTO l_payment_number, l_due_date;
4695 exit when c_built_payments%NOTFOUND;
4696
4697 if i = 1 then
4698 l_payment_schedule(i).PERIOD_BEGIN_DATE := l_loan_details.LOAN_START_DATE;
4699 else
4700 l_payment_schedule(i).PERIOD_BEGIN_DATE := l_payment_schedule(i-1).PERIOD_END_DATE;
4701 end if;
4702
4703 l_payment_schedule(i).PERIOD_DUE_DATE := l_due_date;
4704 l_payment_schedule(i).PERIOD_END_DATE := l_due_date;
4705
4706 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Payment ' || l_payment_number);
4707 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PERIOD_BEGIN_DATE: ' || l_payment_schedule(i).PERIOD_BEGIN_DATE);
4708 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PERIOD_DUE_DATE: ' || l_payment_schedule(i).PERIOD_DUE_DATE);
4709 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PERIOD_END_DATE: ' || l_payment_schedule(i).PERIOD_END_DATE);
4710 i := i + 1;
4711
4712 END LOOP;
4713 CLOSE c_built_payments;
4714
4715 end if;
4716
4717 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Loading dates from LNS_CUSTOM_PAYMNT_SCHEDS:');
4718 OPEN c_load_sched(p_loan_id, i);
4719 LOOP
4720
4721 FETCH c_load_sched INTO l_payment_number, l_due_date;
4722 exit when c_load_sched%NOTFOUND;
4723
4724 if i = 1 then
4725 l_payment_schedule(i).PERIOD_BEGIN_DATE := l_loan_details.LOAN_START_DATE;
4726 else
4727 l_payment_schedule(i).PERIOD_BEGIN_DATE := l_payment_schedule(i-1).PERIOD_END_DATE;
4728 end if;
4729
4730 l_payment_schedule(i).PERIOD_DUE_DATE := l_due_date;
4731 l_payment_schedule(i).PERIOD_END_DATE := l_due_date;
4732
4733 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Payment ' || l_payment_number);
4734 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PERIOD_BEGIN_DATE: ' || l_payment_schedule(i).PERIOD_BEGIN_DATE);
4735 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PERIOD_DUE_DATE: ' || l_payment_schedule(i).PERIOD_DUE_DATE);
4736 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PERIOD_END_DATE: ' || l_payment_schedule(i).PERIOD_END_DATE);
4737 i := i + 1;
4738
4739 END LOOP;
4740 CLOSE c_load_sched;
4741
4742 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
4743
4744 return l_payment_schedule;
4745 END;
4746
4747
4748 -- added for bug 7716548
4749 -- This procedure adds installment to custom schedule only if it does not already exist
4750 procedure addMissingInstallment(
4751 P_API_VERSION IN NUMBER,
4752 P_INIT_MSG_LIST IN VARCHAR2,
4753 P_COMMIT IN VARCHAR2,
4754 P_VALIDATION_LEVEL IN NUMBER,
4755 P_INSTALLMENT_REC IN LNS_CUSTOM_PUB.custom_sched_type,
4756 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4757 X_MSG_COUNT OUT NOCOPY NUMBER,
4758 X_MSG_DATA OUT NOCOPY VARCHAR2)
4759 IS
4760
4761 /*-----------------------------------------------------------------------+
4762 | Local Variable Declarations and initializations |
4763 +-----------------------------------------------------------------------*/
4764
4765 l_api_name CONSTANT VARCHAR2(30) := 'addMissingInstallment';
4766 l_api_version CONSTANT NUMBER := 1.0;
4767 l_return_status VARCHAR2(1);
4768 l_msg_count NUMBER;
4769 l_msg_data VARCHAR2(32767);
4770
4771 l_loan_details LNS_CUSTOM_PUB.LOAN_DETAILS_REC;
4772 l_custom_sched_id NUMBER;
4773 l_INSTALLMENT_REC LNS_CUSTOM_PUB.custom_sched_type;
4774
4775 /*-----------------------------------------------------------------------+
4776 | Cursor Declarations |
4777 +-----------------------------------------------------------------------*/
4778
4779 -- check is such payment already exist
4780 cursor c_exist_installment(p_loan_id NUMBER, p_payment_number NUMBER) IS
4781 select custom_schedule_id
4782 from lns_custom_paymnt_scheds
4783 where loan_id = p_loan_id
4784 and payment_number = p_payment_number;
4785
4786 BEGIN
4787
4788 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
4789
4790 -- Standard start of API savepoint
4791 SAVEPOINT addMissingInstallment;
4792 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Savepoint is established');
4793
4794 -- Standard call to check for call compatibility
4795 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
4796 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4797 END IF;
4798
4799 -- Initialize message list if p_init_msg_list is set to TRUE
4800 IF FND_API.To_Boolean(p_init_msg_list) THEN
4801 FND_MSG_PUB.initialize;
4802 END IF;
4803
4804 -- Initialize API return status to success
4805 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
4806
4807 -- START OF BODY OF API
4808 l_INSTALLMENT_REC := P_INSTALLMENT_REC;
4809
4810 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
4811 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_INSTALLMENT_REC.LOAN_ID: ' || l_INSTALLMENT_REC.LOAN_ID);
4812 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_INSTALLMENT_REC.PAYMENT_NUMBER: ' || l_INSTALLMENT_REC.PAYMENT_NUMBER);
4813 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_INSTALLMENT_REC.DUE_DATE: ' || l_INSTALLMENT_REC.DUE_DATE);
4814 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_INSTALLMENT_REC.PRINCIPAL_AMOUNT: ' || l_INSTALLMENT_REC.PRINCIPAL_AMOUNT);
4815 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_INSTALLMENT_REC.INTEREST_AMOUNT: ' || l_INSTALLMENT_REC.INTEREST_AMOUNT);
4816 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_INSTALLMENT_REC.FEE_AMOUNT: ' || l_INSTALLMENT_REC.FEE_AMOUNT);
4817 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_INSTALLMENT_REC.OTHER_AMOUNT: ' || l_INSTALLMENT_REC.OTHER_AMOUNT);
4818 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_INSTALLMENT_REC.LOCK_PRIN: ' || l_INSTALLMENT_REC.LOCK_PRIN);
4819 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_INSTALLMENT_REC.LOCK_INT: ' || l_INSTALLMENT_REC.LOCK_INT);
4820
4821 if l_INSTALLMENT_REC.LOAN_ID is null then
4822 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
4823 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
4824 FND_MESSAGE.SET_TOKEN('VALUE', l_INSTALLMENT_REC.LOAN_ID);
4825 FND_MSG_PUB.ADD;
4826 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
4827 RAISE FND_API.G_EXC_ERROR;
4828 end if;
4829
4830 if l_INSTALLMENT_REC.PAYMENT_NUMBER is null then
4831 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
4832 FND_MESSAGE.SET_TOKEN('PARAMETER', 'PAYMENT_NUMBER');
4833 FND_MESSAGE.SET_TOKEN('VALUE', l_INSTALLMENT_REC.PAYMENT_NUMBER);
4834 FND_MSG_PUB.ADD;
4835 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
4836 RAISE FND_API.G_EXC_ERROR;
4837 end if;
4838
4839 if l_INSTALLMENT_REC.DUE_DATE is null then
4840 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
4841 FND_MESSAGE.SET_TOKEN('PARAMETER', 'DUE_DATE');
4842 FND_MESSAGE.SET_TOKEN('VALUE', l_INSTALLMENT_REC.DUE_DATE);
4843 FND_MSG_PUB.ADD;
4844 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
4845 RAISE FND_API.G_EXC_ERROR;
4846 end if;
4847
4848 open c_exist_installment(l_INSTALLMENT_REC.LOAN_ID, l_INSTALLMENT_REC.PAYMENT_NUMBER);
4849 fetch c_exist_installment into l_custom_sched_id;
4850 close c_exist_installment;
4851
4852 if l_custom_sched_id is not null then
4853 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.');
4854 return;
4855 end if;
4856
4857 l_loan_details := getLoanDetails(p_loan_id => l_INSTALLMENT_REC.LOAN_ID
4858 ,p_based_on_terms => 'CURRENT');
4859
4860 if l_loan_details.CUSTOM_SCHEDULE = 'N' then
4861 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Cannot add custom installment b/c schedule is not customized. Returning.');
4862 return;
4863 end if;
4864
4865 if l_INSTALLMENT_REC.PRINCIPAL_AMOUNT is null then
4866 l_INSTALLMENT_REC.PRINCIPAL_AMOUNT := 0;
4867 end if;
4868
4869 if l_INSTALLMENT_REC.INTEREST_AMOUNT is null then
4870 l_INSTALLMENT_REC.INTEREST_AMOUNT := 0;
4871 end if;
4872
4873 if l_INSTALLMENT_REC.FEE_AMOUNT is null then
4874 l_INSTALLMENT_REC.FEE_AMOUNT := 0;
4875 end if;
4876
4877 if l_INSTALLMENT_REC.OTHER_AMOUNT is null then
4878 l_INSTALLMENT_REC.OTHER_AMOUNT := 0;
4879 end if;
4880
4881 if l_INSTALLMENT_REC.LOCK_PRIN is null then
4882 l_INSTALLMENT_REC.LOCK_PRIN := 'Y';
4883 end if;
4884
4885 if l_INSTALLMENT_REC.LOCK_INT is null then
4886 l_INSTALLMENT_REC.LOCK_INT := 'Y';
4887 end if;
4888
4889 -- call api to insert new row
4890 lns_custom_pub.createCustomSched(P_CUSTOM_REC => l_INSTALLMENT_REC
4891 ,x_return_status => l_return_status
4892 ,x_custom_sched_id => l_custom_sched_id
4893 ,x_msg_count => l_msg_Count
4894 ,x_msg_data => l_msg_Data);
4895
4896 IF l_return_status <> 'S' THEN
4897 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Failed to insert custom schedule row');
4898 RAISE FND_API.G_EXC_ERROR;
4899 END IF;
4900
4901 if P_COMMIT = FND_API.G_TRUE then
4902 COMMIT WORK;
4903 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
4904 end if;
4905
4906 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Successfully added installment ' || l_INSTALLMENT_REC.PAYMENT_NUMBER || ' to custom schedule');
4907
4908 -- END OF BODY OF API
4909 x_return_status := FND_API.G_RET_STS_SUCCESS;
4910
4911 -- Standard call to get message count and if count is 1, get message info
4912 FND_MSG_PUB.Count_And_Get(
4913 p_encoded => FND_API.G_FALSE,
4914 p_count => x_msg_count,
4915 p_data => x_msg_data);
4916
4917 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
4918
4919 EXCEPTION
4920 WHEN FND_API.G_EXC_ERROR THEN
4921 ROLLBACK TO addMissingInstallment;
4922 x_return_status := FND_API.G_RET_STS_ERROR;
4923 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4924 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
4925 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4926 ROLLBACK TO addMissingInstallment;
4927 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4928 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4929 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
4930 WHEN OTHERS THEN
4931 ROLLBACK TO addMissingInstallment;
4932 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4933 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4934 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
4935 END;
4936
4937
4938
4939 -- This procedure parses and validates csv format clob and returnes data in form of custom schedule
4940 procedure parseClob(
4941 P_API_VERSION IN NUMBER,
4942 P_INIT_MSG_LIST IN VARCHAR2,
4943 P_COMMIT IN VARCHAR2,
4944 P_VALIDATION_LEVEL IN NUMBER,
4945 P_CLOB IN CLOB,
4946 P_RETAIN_DATA IN VARCHAR2,
4947 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4948 X_MSG_COUNT OUT NOCOPY NUMBER,
4949 X_MSG_DATA OUT NOCOPY VARCHAR2)
4950 IS
4951
4952 /*-----------------------------------------------------------------------+
4953 | Local Variable Declarations and initializations |
4954 +-----------------------------------------------------------------------*/
4955
4956 l_api_name CONSTANT VARCHAR2(30) := 'parseClob';
4957 l_CUSTOM_TBL LNS_CUSTOM_PUB.CUSTOM_TBL;
4958 l_CUSTOM_SET_REC LNS_CUSTOM_PUB.custom_settings_type;
4959
4960 /*-----------------------------------------------------------------------+
4961 | Cursor Declarations |
4962 +-----------------------------------------------------------------------*/
4963
4964 BEGIN
4965
4966 parseClob(
4967 P_API_VERSION => 1.0,
4968 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
4969 P_COMMIT => P_COMMIT,
4970 P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
4971 P_CLOB => P_CLOB,
4972 P_RETAIN_DATA => P_RETAIN_DATA,
4973 X_CUSTOM_SET_REC => l_CUSTOM_SET_REC,
4974 X_CUSTOM_TBL => l_CUSTOM_TBL,
4975 x_return_status => x_return_status,
4976 x_msg_count => x_msg_count,
4977 x_msg_data => x_msg_data);
4978
4979 END;
4980
4981
4982
4983
4984 -- This procedure parses and validates csv format clob and returnes data in form of custom schedule
4985 procedure parseClob(
4986 P_API_VERSION IN NUMBER,
4987 P_INIT_MSG_LIST IN VARCHAR2,
4988 P_COMMIT IN VARCHAR2,
4989 P_VALIDATION_LEVEL IN NUMBER,
4990 P_CLOB IN CLOB,
4991 P_RETAIN_DATA IN VARCHAR2,
4992 X_CUSTOM_SET_REC OUT NOCOPY LNS_CUSTOM_PUB.custom_settings_type,
4993 X_CUSTOM_TBL OUT NOCOPY LNS_CUSTOM_PUB.CUSTOM_TBL,
4994 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
4995 X_MSG_COUNT OUT NOCOPY NUMBER,
4996 X_MSG_DATA OUT NOCOPY VARCHAR2)
4997 IS
4998
4999 /*-----------------------------------------------------------------------+
5000 | Local Variable Declarations and initializations |
5001 +-----------------------------------------------------------------------*/
5002
5003 l_api_name CONSTANT VARCHAR2(30) := 'parseClob';
5004 l_api_version CONSTANT NUMBER := 1.0;
5005 l_return_status VARCHAR2(1);
5006 l_msg_count NUMBER;
5007 l_msg_data VARCHAR2(32767);
5008
5009 l_CUSTOM_TBL LNS_CUSTOM_PUB.CUSTOM_TBL;
5010 l_start number;
5011 l_record VARCHAR2(512);
5012 l_end number;
5013 l_clob_length BINARY_INTEGER;
5014 l_index number;
5015 l_comma_index number;
5016 l_record_count number;
5017 l_loan_status varchar2(30);
5018 l_element varchar2(256);
5019 l_date_pos number;
5020 l_rel_date_pos number;
5021 l_prin_pos number;
5022 l_prin_percent_pos number;
5023 l_int_pos number;
5024 l_lock_prin_pos number;
5025 l_lock_int_pos number;
5026 l_pos number;
5027 l_parameter varchar2(30);
5028 l_date_format varchar2(64);
5029 l_term number;
5030 l_period varchar2(30);
5031 l_command_pos number;
5032 l_loop_start number;
5033 l_loop_end number;
5034 l_iterations number;
5035 l_com_index number;
5036 l_end_com_index number;
5037 l_single_command varchar2(256);
5038 j number;
5039 l_due_date varchar2(256);
5040
5041 l_equal_index number;
5042 l_setting VARCHAR2(30);
5043 l_value VARCHAR2(100);
5044 l_AMORT_METHOD VARCHAR2(30);
5045 l_COMBINE_INT_WITH_LAST_PRIN VARCHAR2(1);
5046 l_DESCRIPTION VARCHAR2(100);
5047 l_comma_count number;
5048 l_dd_comma_index number;
5049 l_old_dd_string varchar2(256);
5050 l_new_dd_string varchar2(256);
5051
5052 /*-----------------------------------------------------------------------+
5053 | Cursor Declarations |
5054 +-----------------------------------------------------------------------*/
5055
5056 BEGIN
5057
5058 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' +');
5059
5060 -- Standard start of API savepoint
5061 SAVEPOINT parseClob;
5062 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Savepoint is established');
5063
5064 -- Standard call to check for call compatibility
5065 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
5066 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5067 END IF;
5068
5069 -- Initialize message list if p_init_msg_list is set to TRUE
5070 IF FND_API.To_Boolean(p_init_msg_list) THEN
5071 FND_MSG_PUB.initialize;
5072 END IF;
5073
5074 -- Initialize API return status to success
5075 l_return_status := FND_API.G_RET_STS_SUCCESS;
5076
5077 -- START OF BODY OF API
5078 l_COMBINE_INT_WITH_LAST_PRIN := 'N';
5079
5080 -- read clob
5081 l_clob_length := sys.DBMS_LOB.getlength(P_CLOB);
5082 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_clob_length = ' || l_clob_length);
5083
5084 if l_clob_length > 0 then
5085
5086 l_record_count := 0;
5087 l_start := 1;
5088 while l_start > 0 loop
5089
5090 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_start = ' || l_start);
5091 l_end := DBMS_LOB.INSTR (lob_loc => P_CLOB, pattern => FND_GLOBAL.LOCAL_CHR(10), offset => l_start);
5092 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_end = ' || l_end);
5093
5094 IF l_end > 0 THEN
5095 l_record :=
5096 RTRIM (
5097 DBMS_LOB.SUBSTR (lob_loc => P_CLOB,
5098 amount => LEAST (l_end - l_start, 512),
5099 offset => l_start
5100 ),
5101 FND_GLOBAL.LOCAL_CHR(13) || FND_GLOBAL.LOCAL_CHR(10)
5102 );
5103 l_start := l_end + 1;
5104 ELSE
5105 l_record := DBMS_LOB.SUBSTR (lob_loc => P_CLOB,
5106 amount => l_clob_length - l_start + 1,
5107 offset => l_start
5108 );
5109 l_start := 0;
5110 END IF;
5111
5112 l_record := replace(l_record, '"', '');
5113 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_record = ' || l_record);
5114
5115 if l_record is null then
5116 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_record is blank. Continuing to next record');
5117 CONTINUE;
5118 end if;
5119
5120 -- search for comma or equal in the string
5121 l_equal_index := INSTR(l_record, '=', 1);
5122 l_comma_index := 0;
5123 if l_equal_index = 0 then
5124 l_comma_index := INSTR(l_record, ',', 1);
5125 end if;
5126
5127 if l_comma_index = 0 and l_equal_index > 0 then -- this is custom schedule setting record
5128
5129 l_setting := UPPER(SUBSTR(l_record, 1, l_equal_index - 1));
5130 l_setting := trim(' ' from l_setting);
5131 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_setting = ' || l_setting);
5132
5133 l_value := SUBSTR(l_record, l_equal_index + 1);
5134 l_value := trim(' ' from l_value);
5135 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_value = ' || l_value);
5136
5137 if l_setting = 'AMORT_METHOD' then
5138 l_value := UPPER(l_value);
5139 if l_value <> 'EQUAL_PRINCIPAL' and l_value <> 'EQUAL_PAYMENT' and l_value <> 'NONE' then
5140 l_value := 'NONE';
5141 end if;
5142 l_AMORT_METHOD := l_value;
5143 elsif l_setting = 'COMBINE_INT_WITH_LAST_PRIN' then
5144 l_value := UPPER(l_value);
5145 L_COMBINE_INT_WITH_LAST_PRIN := SUBSTR(l_value, 1, 1);
5146 elsif l_setting = 'DESCRIPTION' then
5147 L_DESCRIPTION := l_value;
5148 end if;
5149
5150 elsif l_comma_index > 0 and l_equal_index = 0 then -- this is actual custom schedule record
5151
5152 l_record := l_record || ',';
5153 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_record = ' || l_record);
5154 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_record_count = ' || l_record_count);
5155
5156 -- init record
5157 if l_record_count = 0 then
5158 l_comma_count := length(TRANSLATE(UPPER(l_record),UPPER(', abcdefghijklmnopqrstuvxyz0123456789-;.()_='),','));
5159 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_comma_count = ' || l_comma_count);
5160 else
5161 l_CUSTOM_TBL(l_record_count).DUE_DATE := null;
5162 l_CUSTOM_TBL(l_record_count).RELATIVE_DATE := null;
5163 l_CUSTOM_TBL(l_record_count).PRINCIPAL_AMOUNT := 0;
5164 l_CUSTOM_TBL(l_record_count).PRINCIPAL_PERCENT := null;
5165 l_CUSTOM_TBL(l_record_count).INTEREST_AMOUNT := 0;
5166 l_CUSTOM_TBL(l_record_count).LOCK_PRIN := 'N';
5167 l_CUSTOM_TBL(l_record_count).LOCK_INT := 'N';
5168
5169 l_dd_comma_index := instr(l_record, ',', -1, l_comma_count);
5170 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_dd_comma_index = ' || l_dd_comma_index);
5171
5172 l_old_dd_string := SUBSTR(l_record, 1, l_dd_comma_index-1);
5173 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_old_dd_string = ' || l_old_dd_string);
5174
5175 l_new_dd_string := TRANSLATE(l_old_dd_string, ',', ';');
5176 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_new_dd_string = ' || l_new_dd_string);
5177
5178 l_record := replace(l_record, l_old_dd_string, l_new_dd_string);
5179 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_record = ' || l_record);
5180 end if;
5181
5182 l_pos := 1;
5183 l_index := 1;
5184 l_due_date := null;
5185 LOOP
5186 l_comma_index := INSTR(l_record, ',', l_index);
5187 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_comma_index = ' || l_comma_index);
5188
5189 EXIT WHEN l_comma_index = 0;
5190
5191 l_element := UPPER(SUBSTR(l_record, l_index, l_comma_index - l_index));
5192 l_element := trim(' ' from l_element);
5193 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_element = ' || l_element);
5194
5195 if l_record_count = 0 then
5196 if l_element = 'DUE DATE' then
5197 l_date_pos := l_pos;
5198 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_date_pos = ' || l_date_pos);
5199 l_date_format := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
5200 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_date_format = ' || l_date_format);
5201 elsif l_element = 'PRINCIPAL' then
5202 l_prin_pos := l_pos;
5203 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_prin_pos = ' || l_prin_pos);
5204 elsif l_element = 'INTEREST' then
5205 l_int_pos := l_pos;
5206 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_int_pos = ' || l_int_pos);
5207 elsif l_element = 'FREEZE PRINCIPAL FLAG' then
5208 l_lock_prin_pos := l_pos;
5209 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_lock_prin_pos = ' || l_lock_prin_pos);
5210 elsif l_element = 'FREEZE INTEREST FLAG' then
5211 l_lock_int_pos := l_pos;
5212 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_lock_int_pos = ' || l_lock_int_pos);
5213 elsif l_element = 'COMMAND' then
5214 l_command_pos := l_pos;
5215 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_command_pos = ' || l_command_pos);
5216 end if;
5217 else
5218 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_index = ' || l_index);
5219 BEGIN
5220 if l_date_pos = l_pos then
5221 l_parameter := 'DUE DATE';
5222 l_due_date := TRANSLATE(l_element, ';', ',');
5223 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DUE DATE = ' || l_due_date);
5224 elsif l_prin_pos = l_pos then
5225 l_parameter := 'PRINCIPAL';
5226 l_CUSTOM_TBL(l_record_count).PRINCIPAL_AMOUNT := l_element;
5227 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DUE DATE = ' || l_element);
5228 elsif l_int_pos = l_pos then
5229 l_parameter := 'INTEREST';
5230 l_CUSTOM_TBL(l_record_count).INTEREST_AMOUNT := l_element;
5231 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DUE DATE = ' || l_element);
5232 elsif l_lock_prin_pos = l_pos then
5233 l_parameter := 'FREEZE PRINCIPAL FLAG';
5234 l_CUSTOM_TBL(l_record_count).LOCK_PRIN := l_element;
5235 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DUE DATE = ' || l_element);
5236 elsif l_lock_int_pos = l_pos then
5237 l_parameter := 'FREEZE INTEREST FLAG';
5238 l_CUSTOM_TBL(l_record_count).LOCK_INT := l_element;
5239 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DUE DATE = ' || l_element);
5240 elsif l_command_pos = l_pos then
5241
5242 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'COMMAND = ' || l_element);
5243 l_com_index := 1;
5244 LOOP
5245 l_end_com_index := INSTR(l_element, ';', l_com_index);
5246 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_end_com_index = ' || l_end_com_index);
5247
5248 EXIT WHEN l_end_com_index = 0;
5249
5250 l_single_command := SUBSTR(l_element, l_com_index, l_end_com_index - l_com_index);
5251 l_single_command := trim(' ' from l_single_command);
5252 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_single_command = ' || l_single_command);
5253
5254 if l_single_command is null then
5255 exit;
5256 elsif SUBSTR(l_single_command, 1, 4) = 'LOOP' then
5257 l_loop_start := l_record_count;
5258 l_iterations := SUBSTR(l_single_command, 5) - 1;
5259 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loop_start = ' || l_loop_start);
5260 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_iterations = ' || l_iterations);
5261 elsif SUBSTR(l_single_command, 1, 4) = 'ENDL' then
5262 l_loop_end := l_record_count;
5263 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loop_end = ' || l_loop_end);
5264 end if;
5265 l_com_index := l_end_com_index + 1;
5266 END LOOP;
5267
5268 end if;
5269 EXCEPTION
5270 WHEN OTHERS THEN
5271 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
5272 FND_MESSAGE.SET_TOKEN('PARAMETER', l_parameter);
5273 FND_MESSAGE.SET_TOKEN('VALUE', l_element);
5274 FND_MSG_PUB.ADD;
5275 RAISE FND_API.G_EXC_ERROR;
5276 END;
5277 end if;
5278
5279 l_index := l_comma_index + 1;
5280 l_pos := l_pos + 1;
5281 END LOOP;
5282
5283 -- validation
5284 if l_record_count = 0 then
5285 if l_date_pos is null then
5286 FND_MESSAGE.Set_Name('LNS', 'LNS_NO_DUE_DATE');
5287 FND_MSG_PUB.Add;
5288 RAISE FND_API.G_EXC_ERROR;
5289 end if;
5290 else
5291 if l_due_date is null then
5292 FND_MESSAGE.Set_Name('LNS', 'LNS_NO_DUE_DATE');
5293 FND_MSG_PUB.Add;
5294 RAISE FND_API.G_EXC_ERROR;
5295 else
5296
5297 BEGIN
5298 -- testing date
5299 l_CUSTOM_TBL(l_record_count).DUE_DATE := to_date(l_due_date, l_date_format);
5300 l_CUSTOM_TBL(l_record_count).RELATIVE_DATE := null;
5301 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Setting l_due_date as DUE_DATE');
5302
5303 EXCEPTION
5304 WHEN OTHERS THEN
5305 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'to_date has failed. Setting l_due_date as RELATIVE_DATE');
5306 l_CUSTOM_TBL(l_record_count).RELATIVE_DATE := l_due_date;
5307 l_CUSTOM_TBL(l_record_count).DUE_DATE := null;
5308 END;
5309
5310 end if;
5311
5312 if l_CUSTOM_TBL(l_record_count).LOCK_PRIN is null or
5313 (l_CUSTOM_TBL(l_record_count).LOCK_PRIN <> 'Y' and
5314 l_CUSTOM_TBL(l_record_count).LOCK_PRIN <> 'P')
5315 then
5316 l_CUSTOM_TBL(l_record_count).LOCK_PRIN := 'N';
5317 end if;
5318
5319 if l_CUSTOM_TBL(l_record_count).LOCK_PRIN = 'P' then
5320 l_CUSTOM_TBL(l_record_count).PRINCIPAL_PERCENT := l_CUSTOM_TBL(l_record_count).PRINCIPAL_AMOUNT;
5321 l_CUSTOM_TBL(l_record_count).PRINCIPAL_AMOUNT := 0;
5322 else
5323 l_CUSTOM_TBL(l_record_count).PRINCIPAL_PERCENT := 0;
5324 end if;
5325
5326 if l_CUSTOM_TBL(l_record_count).PRINCIPAL_AMOUNT is null then
5327 l_CUSTOM_TBL(l_record_count).PRINCIPAL_AMOUNT := 0;
5328 end if;
5329 l_CUSTOM_TBL(l_record_count).PRINCIPAL_AMOUNT := abs(l_CUSTOM_TBL(l_record_count).PRINCIPAL_AMOUNT);
5330
5331 if l_CUSTOM_TBL(l_record_count).PRINCIPAL_PERCENT is null then
5332 l_CUSTOM_TBL(l_record_count).PRINCIPAL_PERCENT := 0;
5333 end if;
5334 l_CUSTOM_TBL(l_record_count).PRINCIPAL_PERCENT := abs(l_CUSTOM_TBL(l_record_count).PRINCIPAL_PERCENT);
5335
5336 if l_CUSTOM_TBL(l_record_count).INTEREST_AMOUNT is null then
5337 l_CUSTOM_TBL(l_record_count).INTEREST_AMOUNT := 0;
5338 end if;
5339 l_CUSTOM_TBL(l_record_count).INTEREST_AMOUNT := abs(l_CUSTOM_TBL(l_record_count).INTEREST_AMOUNT);
5340
5341 if l_CUSTOM_TBL(l_record_count).LOCK_INT is null or
5342 (l_CUSTOM_TBL(l_record_count).LOCK_INT <> 'Y' and
5343 l_CUSTOM_TBL(l_record_count).LOCK_INT <> 'D')
5344 then
5345 l_CUSTOM_TBL(l_record_count).LOCK_INT := 'N';
5346 end if;
5347
5348 end if;
5349
5350 -- creating rows for loop command
5351 if l_loop_start is not null and l_loop_end is not null then
5352
5353 for i in 1..l_iterations loop
5354 for j in l_loop_start..l_loop_end loop
5355 l_record_count := l_record_count + 1;
5356 l_CUSTOM_TBL(l_record_count) := l_CUSTOM_TBL(j);
5357 end loop;
5358 end loop;
5359
5360 l_loop_start := null;
5361 l_loop_end := null;
5362
5363 end if;
5364
5365 l_record_count := l_record_count + 1;
5366
5367 end if;
5368
5369 end loop;
5370
5371 end if;
5372
5373 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Validated custom schedule from clob:');
5374 for i in 1..l_custom_tbl.count loop
5375 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, i);
5376 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DUE_DATE = ' || l_CUSTOM_TBL(i).DUE_DATE );
5377 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'RELATIVE_DATE = ' || l_CUSTOM_TBL(i).RELATIVE_DATE );
5378 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_AMOUNT = ' || l_CUSTOM_TBL(i).PRINCIPAL_AMOUNT );
5379 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PRINCIPAL_PERCENT = ' || l_CUSTOM_TBL(i).PRINCIPAL_PERCENT );
5380 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'LOCK_PRIN = ' || l_CUSTOM_TBL(i).LOCK_PRIN );
5381 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'INTEREST_AMOUNT = ' || l_CUSTOM_TBL(i).INTEREST_AMOUNT );
5382 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'LOCK_INT = ' || l_CUSTOM_TBL(i).LOCK_INT );
5383 end loop;
5384
5385 X_CUSTOM_TBL := l_CUSTOM_TBL;
5386 X_CUSTOM_SET_REC.AMORT_METHOD := l_AMORT_METHOD;
5387 X_CUSTOM_SET_REC.DESCRIPTION := L_DESCRIPTION;
5388
5389 if P_RETAIN_DATA is not null and P_RETAIN_DATA = 'Y' then
5390 g_CUSTOM_TBL := l_CUSTOM_TBL;
5391 G_AMORT_METHOD := l_AMORT_METHOD;
5392 g_COMBINE_INT_WITH_LAST_PRIN := L_COMBINE_INT_WITH_LAST_PRIN;
5393 g_CUSTOM_SCHED_DESC := L_DESCRIPTION;
5394 end if;
5395
5396 if P_COMMIT = FND_API.G_TRUE then
5397 COMMIT WORK;
5398 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Commited');
5399 end if;
5400
5401 -- END OF BODY OF API
5402 x_return_status := FND_API.G_RET_STS_SUCCESS;
5403
5404 -- Standard call to get message count and if count is 1, get message info
5405 FND_MSG_PUB.Count_And_Get(
5406 p_encoded => FND_API.G_FALSE,
5407 p_count => x_msg_count,
5408 p_data => x_msg_data);
5409
5410 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, G_PKG_NAME || '.' || l_api_name || ' -');
5411
5412 EXCEPTION
5413 WHEN OTHERS THEN
5414 ROLLBACK TO parseClob;
5415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5416 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
5417 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Rollbacked');
5418 END;
5419
5420
5421
5422 procedure retainCustomSchedule(
5423 P_API_VERSION IN NUMBER,
5424 P_INIT_MSG_LIST IN VARCHAR2,
5425 P_COMMIT IN VARCHAR2,
5426 P_VALIDATION_LEVEL IN NUMBER,
5427 P_CUSTOM_SET_REC IN LNS_CUSTOM_PUB.custom_settings_type,
5428 P_CUSTOM_TBL IN LNS_CUSTOM_PUB.CUSTOM_TBL,
5429 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
5430 X_MSG_COUNT OUT NOCOPY NUMBER,
5431 X_MSG_DATA OUT NOCOPY VARCHAR2)
5432 IS
5433
5434 /*-----------------------------------------------------------------------+
5435 | Local Variable Declarations and initializations |
5436 +-----------------------------------------------------------------------*/
5437
5438 l_api_name CONSTANT VARCHAR2(30) := 'retainCustomSchedule';
5439
5440 /*-----------------------------------------------------------------------+
5441 | Cursor Declarations |
5442 +-----------------------------------------------------------------------*/
5443
5444 BEGIN
5445
5446 g_CUSTOM_TBL := P_CUSTOM_TBL;
5447 G_AMORT_METHOD := P_CUSTOM_SET_REC.AMORT_METHOD;
5448 g_COMBINE_INT_WITH_LAST_PRIN := 'N';
5449 g_CUSTOM_SCHED_DESC := P_CUSTOM_SET_REC.DESCRIPTION;
5450 x_return_status := FND_API.G_RET_STS_SUCCESS;
5451
5452 END;
5453
5454
5455 procedure clearRetainedSchedule(
5456 P_API_VERSION IN NUMBER,
5457 P_INIT_MSG_LIST IN VARCHAR2,
5458 P_COMMIT IN VARCHAR2,
5459 P_VALIDATION_LEVEL IN NUMBER,
5460 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
5461 X_MSG_COUNT OUT NOCOPY NUMBER,
5462 X_MSG_DATA OUT NOCOPY VARCHAR2)
5463 IS
5464
5465 /*-----------------------------------------------------------------------+
5466 | Local Variable Declarations and initializations |
5467 +-----------------------------------------------------------------------*/
5468
5469 l_api_name CONSTANT VARCHAR2(30) := 'retainCustomSchedule';
5470
5471 /*-----------------------------------------------------------------------+
5472 | Cursor Declarations |
5473 +-----------------------------------------------------------------------*/
5474
5475 BEGIN
5476
5477 g_CUSTOM_TBL.delete;
5478 G_AMORT_METHOD := null;
5479 g_COMBINE_INT_WITH_LAST_PRIN := null;
5480 g_CUSTOM_SCHED_DESC := null;
5481 x_return_status := FND_API.G_RET_STS_SUCCESS;
5482
5483 END;
5484
5485
5486 -- This procedure returns retained custom sched data in the package
5487 procedure getRetainedSchedule(
5488 P_API_VERSION IN NUMBER,
5489 P_INIT_MSG_LIST IN VARCHAR2,
5490 P_COMMIT IN VARCHAR2,
5491 P_VALIDATION_LEVEL IN NUMBER,
5492 X_CUSTOM_SET_REC OUT NOCOPY LNS_CUSTOM_PUB.custom_settings_type,
5493 X_CUSTOM_TBL OUT NOCOPY LNS_CUSTOM_PUB.CUSTOM_TBL,
5494 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
5495 X_MSG_COUNT OUT NOCOPY NUMBER,
5496 X_MSG_DATA OUT NOCOPY VARCHAR2)
5497 IS
5498
5499 l_api_name CONSTANT VARCHAR2(30) := 'getRetainedSchedule';
5500
5501 BEGIN
5502
5503 X_CUSTOM_SET_REC.AMORT_METHOD := G_AMORT_METHOD;
5504 X_CUSTOM_SET_REC.DESCRIPTION := g_CUSTOM_SCHED_DESC;
5505 X_CUSTOM_TBL := G_CUSTOM_TBL;
5506 x_return_status := FND_API.G_RET_STS_SUCCESS;
5507
5508 END;
5509
5510
5511
5512 END LNS_CUSTOM_PUB;
5513