[Home] [Help]
PACKAGE BODY: APPS.LNS_FEE_ENGINE
Source
1 PACKAGE BODY LNS_FEE_ENGINE AS
2 /* $Header: LNS_FEE_ENGINE_B.pls 120.3.12010000.3 2008/11/05 16:23:38 gparuchu ship $ */
3
4 --------------------------------------------
5 -- declaration of global variables and types
6 --------------------------------------------
7 G_DEBUG_COUNT NUMBER := 0;
8 G_DEBUG BOOLEAN := FALSE;
9 G_FILE_NAME CONSTANT VARCHAR2(30) := 'LNS_FEE_ENGINE_B.pls';
10
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'LNS_FEE_ENGINE';
12 -- G_AF_DO_DEBUG VARCHAR2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
13 G_DAYS_COUNT NUMBER;
14 G_DAYS_IN_YEAR NUMBER;
15
16 --------------------------------------------
17 -- internal package routines
18 --------------------------------------------
19
20 procedure logMessage(log_level in number
21 ,module in varchar2
22 ,message in varchar2)
23 is
24
25 begin
26
27 IF log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
28 FND_LOG.STRING(log_level, module, message);
29 END IF;
30
31 end;
32
33 /*
34 function loanHasAssignment(p_loan_id IN NUMBER
35 ,p_assignment_type IN VARCHAR2) return boolean
36 is
37 vSql varchar2(250);
38 TYPE refCur IS REF CURSOR;
39 c_hasAssign refCur;
40 l_return boolean;
41 l_tmp varchar2(1);
42
43 begin
44
45 vSql := 'Select ''X'' ' ||
46 ' From lns_assignments ' ||
47 'Where exists ' ||
48 ' (Select assignment_id ' ||
49 ' From lns_assignments ' ||
50 ' Where loan_id = :a1 ' ||
51 ' and assignment_type = :b1)';
52 -- dbms_output.put_line('plsql is ' || vSql);
53 open c_hasAssign for
54 vSql
55 using p_loan_id
56 ,p_assignment_type;
57 FETCH c_hasAssign INTO l_tmp;
58
59 if c_hasAssign%FOUND then
60 l_return := true;
61 else
62 l_return := false;
63 end if;
64 CLOSE c_hasAssign;
65
66 return l_return;
67
68 end loanHasAssignment;
69 */
70
71 /*=========================================================================
72 || PUBLIC PROCEDURE reprocessFees LNS.B
73 ||
74 || DESCRIPTION
75 || Overview: processes fees for CONVERSION EVENT or DISBURSEMENT
76 ||
77 || PSEUDO CODE/LOGIC
78 || 1. check if fees needs to be billed
79 || 2. calculate all fees
80 || 3. writeFees to the fee schedule
81 || 4. bill fees as manual billing
82 ||
83 || PARAMETERS
84 || Parameter: p_loan_id => loan_id
85 || p_Phase => 'OPEN' then p_disb_head_id must not be null
86 || 'TERM' then p_disb_head_id must be null
87 || p_disb_head_id
88 ||
89 || Return value:
90 || standard
91 || KNOWN ISSUES
92 ||
93 || NOTES
94 ||
95 || MODIFICATION HISTORY
96 || Date Author Description of Changes
97 || 07/28/2005 raverma created
98 *=======================================================================*/
99 procedure processDisbursementFees(p_init_msg_list in varchar2
100 ,p_commit in varchar2
101 ,p_phase in varchar2
102 ,p_loan_id in number
103 ,p_disb_head_id in number
104 ,x_return_status out nocopy varchar2
105 ,x_msg_count out nocopy number
106 ,x_msg_data out nocopy varchar2)
107 is
108 l_api_name varchar2(25);
109 l_write_fee_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
110 l_fee_id number;
111 l_fee_basis varchar2(25);
112 l_fee_amount number;
113 l_fee_description varchar2(60);
114 l_last_payment_number number;
115 l_return_status VARCHAR2(1);
116 l_msg_count NUMBER;
117 l_msg_data VARCHAR2(32767);
118 l_BILL_HEADERS_TBL LNS_BILLING_BATCH_PUB.BILL_HEADERS_TBL;
119 l_BILL_LINES_TBL LNS_BILLING_BATCH_PUB.BILL_LINES_TBL;
120 l_fees_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
121 i number;
122
123 -- get disbursement fees
124 cursor c_DisbursementFees(p_disb_head_id number) is
125 select ass.fee_id
126 ,decode(ass.rate_type, 'FIXED', fee.fee, 'VARIABLE', fee.fee/100 * head.header_amount)
127 ,fee.fee_description
128 ,ass.fee_basis
129 from lns_fee_assignments ass
130 ,lns_disb_headers head
131 ,lns_fees_all fee
132 where ass.loan_id is null
133 and ass.disb_header_id = head.disb_header_id
134 and fee.fee_id = ass.fee_id
135 and ass.disb_header_id = p_disb_head_id;
136
137 cursor c_ConversionFees(p_loan_id number) is
138 select ass.fee_id
139 ,decode(ass.rate_type, 'FIXED', fee.fee, 'VARIABLE', fee.fee/100)
140 ,fee.fee_description
141 ,ass.fee_basis
142 from lns_fee_assignments ass
143 ,lns_fees_all fee
144 where ass.fee_type = 'EVENT_CONVERSION'
145 and loan_id = p_loan_id;
146
147 -- fee basis for TOTAL_DISB_AMT
148 cursor c_totalDisbursed(p_loan_id number) is
149 select sum(l.line_amount)
150 from lns_disb_lines l
151 ,lns_disb_headers h
152 where h.disb_header_id = l.disb_header_id
153 and l.status = 'FULLY_FUNDED'
154 and h.loan_id = p_loan_id;
155 --
156 cursor c_origLoanAmt(p_loan_id number) is
157 select requested_amount
158 from lns_loan_headers_all
159 where loan_id = p_loan_id;
160
161 cursor c_lastPaymentNumber(p_loan_id number) is
162 select nvl(last_payment_number, 0)
163 from lns_loan_headers_all
164 where loan_id = p_loan_id;
165
166
167 begin
168 l_api_name := 'processDisbursementFees';
169 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
170 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - loan_id ' || p_loan_id);
171 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_phase' || p_phase);
172 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_disb_head_id' || p_disb_head_id);
173
174 -- Standard Start of API savepoint
175 SAVEPOINT processDisbursementFees;
176
177 -- Initialize message list IF p_init_msg_list is set to TRUE.
178 IF FND_API.to_Boolean(p_init_msg_list) THEN
179 FND_MSG_PUB.initialize;
180 END IF;
181
182 -- Initialize API return status to SUCCESS
183 x_return_status := FND_API.G_RET_STS_SUCCESS;
184
185 -- ---------------------------------------------------------------------
186 -- Api body
187 -- ---------------------------------------------------------------------
188 i := 0;
189 -- 1. check if fees needs to be billed
190 if p_phase = 'OPEN' then
191 OPEN c_DisbursementFees(p_disb_head_id);
192 LOOP
193 i := i + 1;
194 FETCH c_DisbursementFees INTO
195 l_fee_id
196 ,l_fee_amount
197 ,l_fee_description
198 ,l_fee_basis;
199 EXIT WHEN c_DisbursementFees%NOTFOUND;
200
201 -- 2. calculate all fees
202 if l_fee_basis = 'TOTAL_DISB_AMT' then
203 open c_totalDisbursed(p_loan_id);
204 fetch c_totalDisbursed into l_fee_amount;
205 close c_totalDisbursed;
206 end if;
207 l_write_fee_tbl(i).fee_id := l_fee_id;
208 l_write_fee_tbl(i).fee_amount := l_fee_amount;
209 l_write_fee_tbl(i).fee_installment := 0;
210 l_write_fee_tbl(i).fee_description := l_fee_description;
211 l_write_fee_tbl(i).disb_header_id := p_disb_head_id;
212
213 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee #: ' || i);
214 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee id: ' || l_fee_id);
215 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee amount: ' || l_fee_amount);
216 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_fee_description: ' || l_fee_description);
217 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_fee_basis ' || l_fee_basis);
218
219 l_fee_amount := null;
220 l_fee_id := null;
221 l_fee_basis := null;
222 l_fee_description := null;
223
224 END LOOP;
225
226 elsif p_phase = 'TERM' then
227
228 OPEN c_ConversionFees(p_disb_head_id);
229 LOOP
230 i := i + 1;
231 FETCH c_ConversionFees INTO
232 l_fee_id
233 ,l_fee_amount
234 ,l_fee_description
235 ,l_fee_basis;
236 EXIT WHEN c_ConversionFees%NOTFOUND;
237
238 if l_fee_basis = 'TOTAL_DISB_AMT' then
239 open c_totalDisbursed(p_loan_id);
240 fetch c_totalDisbursed into l_fee_amount;
241 close c_totalDisbursed;
242 elsif l_fee_basis = 'ORIG_LOAN' then
243 open c_origLoanAmt(p_loan_id);
244 fetch c_origLoanAmt into l_fee_amount;
245 close c_origLoanAmt;
246 end if;
247
248 l_write_fee_tbl(i).fee_id := l_fee_id;
249 l_write_fee_tbl(i).fee_amount := l_fee_amount;
250 l_write_fee_tbl(i).fee_installment := 0;
251 l_write_fee_tbl(i).fee_description := l_fee_description;
252 l_write_fee_tbl(i).disb_header_id := p_disb_head_id;
253
254 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee #: ' || i);
255 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee id: ' || l_fee_id);
256 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee amount: ' || l_fee_amount);
257 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_fee_description: ' || l_fee_description);
258 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_fee_basis ' || l_fee_basis);
259
260 l_fee_amount := null;
261 l_fee_id := null;
262 l_fee_basis := null;
263 l_fee_description := null;
264 end loop;
265 end if;
266
267 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - i ' || i);
268 if l_write_fee_tbl.count > 0 then
269 -- 3. writeFees to the fee schedule
270 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - writing fees schedule');
271 lns_fee_engine.writeFeeSchedule(p_init_msg_list => p_init_msg_list
272 ,p_commit => p_commit
273 ,p_loan_id => p_loan_id
274 ,p_fees_tbl => l_write_fee_tbl
275 ,x_return_status => l_return_status
276 ,x_msg_count => l_msg_count
277 ,x_msg_data => l_msg_data);
278 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - writing fees schedule status ' || l_return_status);
279 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - getting fee schedule ' || l_return_status);
280 getFeeSchedule(p_init_msg_list => p_init_msg_list
281 ,p_loan_id => p_loan_id
282 ,p_installment_number => 0
283 ,p_disb_header_id => p_disb_head_id
284 ,x_fees_tbl => l_fees_tbl
285 ,x_return_status => l_return_status
286 ,x_msg_count => l_msg_count
287 ,x_msg_data => l_msg_data);
288 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
289 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_SCHEDULE_READ_ERROR');
290 FND_MSG_PUB.ADD;
291 RAISE FND_API.G_EXC_ERROR;
292 end if;
293 end if;
294
295 -- 4. bill fees as manual billing
296 i := l_fees_tbl.count;
297 if i > 0 then
298 open c_lastPaymentNumber(p_loan_id);
299 fetch c_lastPaymentNumber into l_last_payment_number;
300 close c_lastPaymentNumber;
301 l_BILL_HEADERS_TBL(1).HEADER_ID := 101;
302 l_BILL_HEADERS_TBL(1).LOAN_ID := p_loan_id;
303 l_BILL_HEADERS_TBL(1).ASSOC_PAYMENT_NUM := l_last_payment_number;
304 l_BILL_HEADERS_TBL(1).DUE_DATE := sysdate;
305 end if;
306
307 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee schedule count is' || i);
308 for k in 1..i loop
309 l_BILL_LINES_TBL(k).LINE_ID := 102 + k;
310 l_BILL_LINES_TBL(k).HEADER_ID := 101;
311 l_BILL_LINES_TBL(k).LINE_AMOUNT := l_fees_tbl(k).fee_amount;
312 l_BILL_LINES_TBL(k).LINE_TYPE := 'FEE';
313 l_BILL_LINES_TBL(k).LINE_DESC := l_fees_tbl(k).fee_description;
314 end loop;
315
316 if i > 0 then
317 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - creating off cycle bills' || i);
318 /*
319 LNS_BILLING_BATCH_PUB.CREATE_OFFCYCLE_BILLS(P_API_VERSION => 1.0
320 ,P_INIT_MSG_LIST => FND_API.G_TRUE
321 ,P_COMMIT => FND_API.G_TRUE
322 ,P_VALIDATION_LEVEL => 100
323 ,P_BILL_HEADERS_TBL => l_BILL_HEADERS_TBL
324 ,P_BILL_LINES_TBL => l_BILL_LINES_TBL
325 ,x_return_status => l_return_status
326 ,x_msg_count => l_msg_count
327 ,x_msg_data => l_msg_data);
328 */
329 l_return_status := FND_API.G_RET_STS_SUCCESS;
330 /* - Bug#6961250 Commented bcoz the above code is commented, so that the
331 FeeSchedule is not billed and its flag doesn't be a billedFlag.
332 if l_return_status = FND_API.G_RET_STS_SUCCESS then
333 update lns_fee_schedules
334 set billed_flag = 'Y'
335 where disb_header_id = p_disb_head_id;
336 end if;
337 */
338 end if;
339
340 -- ---------------------------------------------------------------------
341 -- End of API body
342 -- ---------------------------------------------------------------------
343 IF FND_API.to_Boolean(p_commit) THEN
344 COMMIT WORK;
345 END IF;
346 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
347
348 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
349
350 EXCEPTION
351 WHEN FND_API.G_EXC_ERROR THEN
352 x_return_status := FND_API.G_RET_STS_ERROR;
353 x_msg_count := l_msg_count;
354 x_msg_data := l_msg_data;
355 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
356 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
357 ROLLBACK TO processDisbursementFees;
358
359 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
360 x_return_status := FND_API.G_RET_STS_ERROR;
361 x_msg_count := l_msg_count;
362 x_msg_data := l_msg_data;
363 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
364 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
365 ROLLBACK TO processDisbursementFees;
366
367 WHEN OTHERS THEN
368 x_return_status := FND_API.G_RET_STS_ERROR;
369 x_msg_count := l_msg_count;
370 x_msg_data := l_msg_data;
371 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
372 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
373 ROLLBACK TO processDisbursementFees;
374
375 end processDisbursementFees;
376
377 /*=========================================================================
378 || PUBLIC PROCEDURE reprocessFees LNS.B
379 ||
380 || DESCRIPTION
381 || Overview: reprocesses fees when a loan is rebilled
382 || will recalculate and write late fees and manual fees
383 || will write these new fees to fee schedule
384 || NOTE: recurring fees are processed during billing API call
385 ||
386 || PSEUDO CODE/LOGIC
387 ||
388 || PARAMETERS
389 || Parameter: p_loan_id => loan_id
390 || p_installment => installment number for the loan
391 ||
392 || Return value:
393 || standard
394 || KNOWN ISSUES
395 ||
396 || NOTES
397 ||
398 || MODIFICATION HISTORY
399 || Date Author Description of Changes
400 || 3/15/2005 raverma Created
401 ||
402 *=======================================================================*/
403 procedure reprocessFees(p_loan_id in number
404 ,p_installment_number in number
405 ,p_init_msg_list in varchar2
406 ,p_commit in varchar2
407 ,x_return_status out nocopy varchar2
408 ,x_msg_count out nocopy number
409 ,x_msg_data out nocopy varchar2)
410 is
411
412 l_api_name varchar2(15);
413 l_write_fee_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
414 l_fee_id number;
415 l_fee_amount number;
416 l_fee_description varchar2(250);
417 l_return_status VARCHAR2(1);
418 l_msg_count NUMBER;
419 l_msg_data VARCHAR2(32767);
420 i number;
421
422 -- get the manual fee_ids that were added and billed for the installment
423 -- also include origination fees
424 -- need to get the max (amortization_id) in case of multiple credits
425 cursor c_manual_fees (p_loan_id number, p_installment number) is
426 select sched.fee_id
427 ,sched.fee_description
428 from lns_fee_schedules sched
429 ,lns_fees fees
430 ,lns_amortization_lines lines
431 ,lns_amortization_scheds am
432 where fees.fee_id = sched.fee_id
433 and lines.fee_schedule_id = sched.fee_schedule_id
434 and lines.amortization_schedule_id = am.amortization_schedule_id
435 and am.amortization_schedule_id =
436 (select max(am2.amortization_schedule_id)
437 from lns_amortization_scheds am2
438 where am2.reversed_flag = 'Y'
439 and am2.loan_id = p_loan_id )
440 and am.reamortization_amount is null
441 and sched.fee_installment = p_installment
442 and ((fees.fee_category = 'MANUAL')
443 OR (fees.fee_category = 'EVENT' AND fees.fee_type = 'EVENT_ORIGINATION'))
444 and sched.loan_id = am.loan_id
445 and am.loan_id = p_loan_id
446 and sched.active_flag = 'Y'
447 and sched.billed_flag = 'Y';
448
449 begin
450 l_api_name := 'reprocessFees';
451 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
452 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - loan_id ' || p_loan_id);
453 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - installment' || p_installment_number);
454
455 -- Standard Start of API savepoint
456 SAVEPOINT reprocessFees;
457
458 -- Initialize message list IF p_init_msg_list is set to TRUE.
459 IF FND_API.to_Boolean(p_init_msg_list) THEN
460 FND_MSG_PUB.initialize;
461 END IF;
462
463 -- Initialize API return status to SUCCESS
464 x_return_status := FND_API.G_RET_STS_SUCCESS;
465
466 -- ---------------------------------------------------------------------
467 -- Api body
468 -- ---------------------------------------------------------------------
469 -- initialize any variables here
470 i := 0;
471
472 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - about to reprocess late fees');
473 -- processing late fees will rewrite them to fee_schedules
474 lns_fee_engine.processLateFees(p_loan_id => p_loan_id
475 ,p_init_msg_list => p_init_msg_list
476 ,p_commit => p_commit
477 ,x_return_status => l_return_status
478 ,x_msg_count => l_msg_count
479 ,x_msg_data => l_msg_data);
480
481 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - reprocess late fees status ' || l_return_status);
482 -- we will rewrite any previously written manual fees onto the fee schedule
483 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - finding manual and origination fees');
484 OPEN c_manual_fees(p_loan_id, p_installment_number);
485 LOOP
486 i := i + 1;
487 FETCH c_manual_fees INTO
488 l_fee_id
489 --,l_fee_amount
490 ,l_fee_description;
491 EXIT WHEN c_manual_fees%NOTFOUND;
492
493 l_fee_amount := lns_fee_engine.calculateFee(p_fee_id => l_fee_id
494 ,p_loan_id => p_loan_id);
495 l_write_fee_tbl(i).fee_id := l_fee_id;
496 l_write_fee_tbl(i).fee_amount := l_fee_amount;
497 l_write_fee_tbl(i).fee_installment := p_installment_number;
498 l_write_fee_tbl(i).fee_description := l_fee_description;
499
500 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee #: ' || i);
501 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee id: ' || l_fee_id);
502 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee amount: ' || l_fee_amount);
503 END LOOP;
504
505 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - writing fees schedule');
506 lns_fee_engine.writeFeeSchedule(p_init_msg_list => p_init_msg_list
507 ,p_commit => p_commit
508 ,p_loan_id => p_loan_id
509 ,p_fees_tbl => l_write_fee_tbl
510 ,x_return_status => l_return_status
511 ,x_msg_count => l_msg_count
512 ,x_msg_data => l_msg_data);
513 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - writing fees schedule status ' || l_return_status);
514
515 -- ---------------------------------------------------------------------
516 -- End of API body
517 -- ---------------------------------------------------------------------
518 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
519
520 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
521
522 EXCEPTION
523 WHEN FND_API.G_EXC_ERROR THEN
524 x_return_status := FND_API.G_RET_STS_ERROR;
525 x_msg_count := l_msg_count;
526 x_msg_data := l_msg_data;
527 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
528 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
529 ROLLBACK TO reprocessFees;
530
531 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
532 x_return_status := FND_API.G_RET_STS_ERROR;
533 x_msg_count := l_msg_count;
534 x_msg_data := l_msg_data;
535 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
536 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
537 ROLLBACK TO reprocessFees;
538
539 WHEN OTHERS THEN
540 x_return_status := FND_API.G_RET_STS_ERROR;
541 x_msg_count := l_msg_count;
542 x_msg_data := l_msg_data;
543 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
544 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
545 ROLLBACK TO reprocessFees;
546
547 end reprocessFees;
548
549 /*=========================================================================
550 || PUBLIC PROCEDURE getFeeStructures LNS.B
551 ||
552 || DESCRIPTION
553 || Overview: returns structure of fees for a given loan
554 ||
555 || PSEUDO CODE/LOGIC
556 ||
557 || PARAMETERS
558 || Parameter: p_loan_id => loan_id
559 || p_fee_category => fee category
560 || p_fee_type => fee type
561 || p_installment => installment number for the loan
562 ||
563 || Return value:
564 || table of fee structures needed to calculate fees
565 || KNOWN ISSUES
566 ||
567 || NOTES
568 ||
569 || MODIFICATION HISTORY
570 || Date Author Description of Changes
571 || 1/20/2005 GWBush2 Created
572 ||
573 *=======================================================================*/
574 function getFeeStructures (p_fee_id in number) return LNS_FEE_ENGINE.FEE_STRUCTURE_TBL
575
576 is
577
578 l_fee_id number;
579 l_fee_name varchar2(50);
580 l_fee_type varchar2(30);
581 l_fee_category varchar2(30);
582 l_fee number;
583 l_fee_basis varchar2(30);
584 l_billing_option varchar2(30);
585 l_rate_type varchar2(30);
586 l_number_grace_days number;
587 l_minimum_overdue_amount number;
588 l_begin_installment_number number;
589 l_end_installment_number number;
590 l_fee_editable_flag varchar2(1);
591 l_fee_waivable_flag varchar2(1);
592 i number := 0;
593 l_fee_struct_tbl LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
594 l_api_name varchar2(25);
595
596 cursor c_fees (fee_id number) is
597 SELECT fees.fee_id
598 ,fees.fee_name
599 ,fees.fee_type
600 ,fees.fee_category
601 ,decode(fees.rate_type, 'FIXED', fees.fee, 'VARIABLE', fees.fee/100)
602 ,fees.fee_basis
603 ,fees.billing_option
604 ,fees.rate_type
605 ,fees.number_grace_days
606 ,fees.minimum_overdue_amount
607 ,0
608 ,0
609 ,nvl(fees.fee_editable_flag,'N')
610 ,nvl(fees.fee_waivable_flag,'N')
611 from lns_fees_all fees
612 where fees.fee_id = p_fee_id;
613
614 begin
615 l_api_name := 'getFeeStructures';
616
617 open c_fees(p_fee_id) ;
618 i := i + 1;
619 fetch c_fees into
620 l_fee_id
621 ,l_fee_name
622 ,l_fee_type
623 ,l_fee_category
624 ,l_fee
625 ,l_fee_basis
626 ,l_billing_option
627 ,l_rate_type
628 ,l_number_grace_days
629 ,l_minimum_overdue_amount
630 ,l_begin_installment_number
631 ,l_end_installment_number
632 ,l_fee_editable_flag
633 ,l_fee_waivable_flag;
634 l_fee_struct_tbl(i).fee_id := l_fee_id;
635 l_fee_struct_tbl(i).fee_name := l_fee_name;
636 l_fee_struct_tbl(i).fee_type := l_fee_type;
637 l_fee_struct_tbl(i).fee_category := l_fee_category;
638 l_fee_struct_tbl(i).fee_amount := l_fee;
639 l_fee_struct_tbl(i).fee_basis := l_fee_basis;
640 l_fee_struct_tbl(i).fee_billing_option := l_billing_option;
641 l_fee_struct_tbl(i).fee_rate_type := l_rate_type;
642 l_fee_struct_tbl(i).number_grace_days := l_number_grace_days;
643 l_fee_struct_tbl(i).minimum_overdue_amount := l_minimum_overdue_amount;
644 l_fee_struct_tbl(i).fee_from_installment := l_begin_installment_number;
645 l_fee_struct_tbl(i).fee_to_installment := l_end_installment_number;
646 l_fee_struct_tbl(i).fee_editable_flag := l_fee_editable_flag;
647 l_fee_struct_tbl(i).fee_waivable_flag := l_fee_waivable_flag;
648 close c_fees;
649
650 return l_fee_struct_tbl;
651
652 exception
653 when no_data_found then
654 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - no fee structures found');
655 end getFeeStructures;
656
657 /*=========================================================================
658 || PUBLIC PROCEDURE getFeeStructures LNS.B
659 ||
660 || DESCRIPTION
661 || Overview: returns structure of fees for a given loan
662 ||
663 || PSEUDO CODE/LOGIC
664 ||
665 || PARAMETERS
666 || Parameter: p_loan_id => loan_id
667 || p_fee_category => fee category
668 || p_fee_type => fee type
669 || p_installment => installment number for the loan
670 ||
671 || Return value:
672 || table of fee structures needed to calculate fees
673 || KNOWN ISSUES
674 ||
675 || NOTES
676 ||
677 || MODIFICATION HISTORY
678 || Date Author Description of Changes
679 || 3/29/2004 8:40PM raverma Created
680 ||
681 *=======================================================================*/
682 function getFeeStructures(p_loan_id in number
683 ,p_fee_category in varchar2
684 ,p_fee_type in varchar2
685 ,p_installment in number
686 ,p_fee_id in number) return LNS_FEE_ENGINE.FEE_STRUCTURE_TBL
687
688 is
689
690 l_fee_id number;
691 l_fee_name varchar2(50);
692 l_fee_type varchar2(30);
693 l_fee_category varchar2(30);
694 l_fee number;
695 l_fee_basis varchar2(30);
696 l_billing_option varchar2(30);
697 l_rate_type varchar2(30);
698 l_number_grace_days number;
699 l_minimum_overdue_amount number;
700 l_fee_basis_rule varchar2(30);
701 l_begin_installment_number number;
702 l_end_installment_number number;
703 l_fee_editable_flag varchar2(1);
704 l_fee_waivable_flag varchar2(1);
705 i number := 0;
706 l_fee_struct_tbl LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
707 --vPLSQL varchar2(2000);
708 --Type refCur is ref cursor;
709 --sql_Cur refCur;
710 l_api_name varchar2(25);
711
712 cursor c_fees(p_loan_id number, p_fee_id number, p_fee_category varchar, p_fee_type varchar, p_installment number) is
713 SELECT fees.fee_id
714 ,fees.fee_name
715 ,fees.fee_type
716 ,fees.fee_category
717 ,decode(fees.fee_category, 'MEMO', decode(fees.rate_type, 'FIXED', Assgn.fee, 'VARIABLE', Assgn.fee/100), decode(fees.rate_type, 'FIXED', fees.fee, 'VARIABLE', fees.fee/100))
718 ,fees.fee_basis
719 ,assgn.billing_option
720 ,fees.rate_type
721 ,fees.number_grace_days
722 ,fees.minimum_overdue_amount
723 ,fees.fee_basis_rule
724 ,nvl(assgn.begin_installment_number,0)
725 ,nvl(assgn.end_installment_number,0)
726 ,nvl(fees.fee_editable_flag,'N')
727 ,nvl(fees.fee_waivable_flag,'N')
728 from lns_fee_assignments assgn
729 ,lns_fees_all fees
730 where assgn.loan_id = nvl(p_loan_id, assgn.loan_id)
731 and assgn.fee_id = fees.fee_id
732 and nvl(trunc(assgn.end_date_active), trunc(sysdate) + 1) >= trunc(sysdate)
733 and nvl(trunc(assgn.start_date_active), trunc(sysdate) - 1) <= trunc(sysdate)
734 and (((fees.fee_category = nvl(p_fee_category, fees.fee_category) and
735 fees.fee_type = nvl(p_fee_type, fees.fee_type)))
736 OR
737 ((fees.fee_category = nvl(p_fee_category, fees.fee_category) and fees.fee_type is null)))
738 AND assgn.begin_installment_number <= nvl(p_installment, assgn.begin_installment_number)
739 AND assgn.end_installment_number >= nvl(p_installment, assgn.end_installment_number)
740 and fees.fee_id = nvl(p_fee_id, fees.fee_id);
741
742 begin
743 l_api_name := 'getFeeStructures';
744 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_loan_id ' || p_loan_id);
745 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee_category ' || p_fee_category);
746 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee_type ' || p_fee_type);
747 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_installment ' || p_installment);
748 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_fee_id ' || p_fee_id);
749
750 open c_fees(p_loan_id, p_fee_id, p_fee_category, p_fee_type, p_installment);
751 LOOP
752 i := i + 1;
753 fetch c_fees into
754 l_fee_id
755 ,l_fee_name
756 ,l_fee_type
757 ,l_fee_category
758 ,l_fee
759 ,l_fee_basis
760 ,l_billing_option
761 ,l_rate_type
762 ,l_number_grace_days
763 ,l_minimum_overdue_amount
764 ,l_fee_basis_rule
765 ,l_begin_installment_number
766 ,l_end_installment_number
767 ,l_fee_editable_flag
768 ,l_fee_waivable_flag;
769 exit when c_fees%notfound;
770 l_fee_struct_tbl(i).fee_id := l_fee_id;
771 l_fee_struct_tbl(i).fee_name := l_fee_name;
772 l_fee_struct_tbl(i).fee_type := l_fee_type;
773 l_fee_struct_tbl(i).fee_category := l_fee_category;
774 l_fee_struct_tbl(i).fee_amount := l_fee;
775 l_fee_struct_tbl(i).fee_basis := l_fee_basis;
776 l_fee_struct_tbl(i).fee_billing_option := l_billing_option;
777 l_fee_struct_tbl(i).fee_rate_type := l_rate_type;
778 l_fee_struct_tbl(i).number_grace_days := l_number_grace_days;
779 l_fee_struct_tbl(i).minimum_overdue_amount := l_minimum_overdue_amount;
780 l_fee_struct_tbl(i).fee_basis_rule := l_fee_basis_rule;
781 l_fee_struct_tbl(i).fee_from_installment := l_begin_installment_number;
782 l_fee_struct_tbl(i).fee_to_installment := l_end_installment_number;
783 l_fee_struct_tbl(i).fee_editable_flag := l_fee_editable_flag;
784 l_fee_struct_tbl(i).fee_waivable_flag := l_fee_waivable_flag;
785 end loop;
786 close c_fees;
787
788 return l_fee_struct_tbl;
789
790 exception
791 when no_data_found then
792 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - no fee structures found');
793 end getFeeStructures;
794
795 /*=========================================================================
796 || PUBLIC PROCEDURE getDisbursementFeeStructures R12
797 ||
798 || DESCRIPTION
799 || Overview: returns structure of fees for a given loan
800 ||
801 || PSEUDO CODE/LOGIC
802 ||
803 || PARAMETERS
804 || Parameter: p_loan_id => loan_id
805 || p_fee_category => fee category
806 || p_fee_type => fee type
807 || p_installment => installment number for the loan
808 || p_from/to_date => dates of disbursement
809 ||
810 || Return value:
811 || table of fee structures needed to calculate disbursement fees
812 || KNOWN ISSUES
813 ||
814 || NOTES
815 ||
816 || MODIFICATION HISTORY
817 || Date Author Description of Changes
818 || 7/22/2005 8:40PM raverma Created
819 *=======================================================================*/
820 function getDisbursementFeeStructures(p_loan_id in number
821 ,p_fee_category in varchar2
822 ,p_fee_type in varchar2
823 ,p_from_date in date
824 ,p_to_date in date
825 ,p_disb_header_id in number
826 ,p_fee_id in number) return LNS_FEE_ENGINE.FEE_STRUCTURE_TBL
827 is
828
829 l_fee_id number;
830 l_fee_name varchar2(50);
831 l_fee_type varchar2(30);
832 l_fee_category varchar2(30);
833 l_fee number;
834 l_fee_basis varchar2(30);
835 l_billing_option varchar2(30);
836 l_rate_type varchar2(30);
837 i number := 0;
838 l_fee_struct_tbl LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
839 l_api_name varchar2(50);
840 l_begin_installment_number number;
841 l_end_installment_number number;
842 l_disbursement_date date;
843 l_disbursement_amount number;
844 l_disb_header_id number;
845
846 cursor c_fees(p_loan_id number, p_fee_category varchar2, p_fee_type varchar2
847 ,p_from_date date, p_to_date date, p_disb_header_id number, p_fee_id number) IS
848 SELECT fees.fee_id
849 ,fees.fee_name
850 ,fees.fee_type
851 ,fees.fee_category
852 ,decode(fees.rate_type, 'FIXED', fees.fee, 'VARIABLE', fees.fee/100)
853 ,fees.fee_basis
854 ,assgn.billing_option
855 ,fees.rate_type
856 ,0
857 ,0
858 ,dh.disb_header_id
859 ,dh.target_date
860 ,dh.header_amount
861 from lns_fee_assignments assgn
862 ,lns_fees_all fees
863 ,lns_disb_headers dh
864 where dh.loan_id = nvl(p_loan_id, dh.loan_id)
865 and dh.disb_header_id = nvl(p_disb_header_id, dh.disb_header_id)
866 and fees.fee_id = nvl(p_fee_id, fees.fee_id)
867 and assgn.fee_id = fees.fee_id
868 and nvl(trunc(assgn.end_date_active), trunc(sysdate) + 1) >= trunc(sysdate)
869 and nvl(trunc(assgn.start_date_active), trunc(sysdate) - 1) <= trunc(sysdate)
870 and (((fees.fee_category = nvl(p_fee_category, fees.fee_category) and
871 fees.fee_type = nvl(p_fee_type, fees.fee_type)))
872 OR
873 ((fees.fee_category = nvl(p_fee_category, fees.fee_category) and fees.fee_type is null)))
874 and assgn.disb_header_id = dh.disb_header_id
875 and dh.target_date >= nvl(p_from_date, dh.target_date -1)
876 and dh.target_date < nvl(p_to_date, dh.target_date + 1);
877
878 begin
879 l_api_name := 'getDisbursementFeeStructures';
880 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee_category ' || p_fee_category);
881 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee_type ' || p_fee_type);
882 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_from_date ' || p_from_date);
883 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_to_Date ' || p_to_Date);
884 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_loan_id ' || p_loan_id);
885 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_disb_header_id ' || p_disb_header_id);
886 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_fee_id ' || p_fee_id);
887
888 open c_fees(p_loan_id, p_fee_category, p_fee_type, p_from_date, p_to_date, p_disb_header_id, p_fee_id);
889 LOOP
890 i := i + 1;
891 fetch c_fees into
892 l_fee_id
893 ,l_fee_name
894 ,l_fee_type
895 ,l_fee_category
896 ,l_fee
897 ,l_fee_basis
898 ,l_billing_option
899 ,l_rate_type
900 ,l_begin_installment_number
901 ,l_end_installment_number
902 ,l_disb_header_id
903 ,l_disbursement_date
904 ,l_disbursement_amount;
905 exit when c_fees%notfound;
906 l_fee_struct_tbl(i).fee_id := l_fee_id;
907 l_fee_struct_tbl(i).fee_name := l_fee_name;
908 l_fee_struct_tbl(i).fee_type := l_fee_type;
909 l_fee_struct_tbl(i).fee_category := l_fee_category;
910 l_fee_struct_tbl(i).fee_amount := l_fee;
911 l_fee_struct_tbl(i).fee_basis := l_fee_basis;
912 l_fee_struct_tbl(i).fee_billing_option := l_billing_option;
913 l_fee_struct_tbl(i).fee_rate_type := l_rate_type;
914 l_fee_struct_tbl(i).fee_from_installment := l_begin_installment_number;
915 l_fee_struct_tbl(i).fee_to_installment := l_end_installment_number;
916 l_fee_struct_tbl(i).disb_header_id := l_disb_header_id;
917 l_fee_struct_tbl(i).disbursement_date := l_disbursement_date;
918 l_fee_struct_tbl(i).disbursement_amount := l_disbursement_amount;
919 end loop;
920 close c_fees;
921
922 return l_fee_struct_tbl;
923
924 exception
925 when no_data_found then
926 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - no fee structures found');
927 end getDisbursementFeeStructures;
928
929
930 /*=========================================================================
931 || PUBLIC PROCEDURE calculateFee ----- R12
932 ||
933 || DESCRIPTION
934 || Overview: this will calculate amount for a single fee for fee assignment page
935 ||
936 ||
937 || PSEUDO CODE/LOGIC
938 ||
939 || PARAMETERS
940 || Parameter: p_fee_id = fee to calculate
941 || p_disb_header_id = disbursement to calculate for
942 ||
943 || KNOWN ISSUES
944 ||
945 || NOTES
946 ||
947 || MODIFICATION HISTORY
948 || Date Author Description of Changes
949 || 7/29/2005 8:40PM raverma Created
950 ||
951 *=======================================================================*/
952 function calculateFee(p_fee_id in number
953 ,p_disb_header_id in number
954 ,p_loan_id in number)return number
955 is
956
957 -- fee basis for originalLoanAmount
958 cursor c_origLoanAmt(p_loan_id number) is
959 select requested_amount
960 from lns_loan_headers_all
961 where loan_id = p_loan_id;
962
963 cursor c_disbAmount(p_disb_header_id number) is
964 select header_amount
965 from lns_disb_headers
966 where disb_header_id = p_disb_header_id;
967
968 l_original_loan_amount number;
969 l_orig_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
970 l_fee_calc_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
971 l_fee_basis_tbl LNS_FEE_ENGINE.FEE_BASIS_TBL;
972 l_api_name varchar2(25);
973 l_return_status VARCHAR2(1);
974 l_msg_count NUMBER;
975 l_msg_data VARCHAR2(32767);
976 l_amount number;
977 l_disb_amount number;
978
979 begin
980
981 l_amount := 0;
982 l_api_name := 'calculateFee';
983 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_fee_id ' || p_fee_id);
984 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_disb_header_id ' || p_disb_header_id );
985 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - calculating ' || p_loan_id);
986
987 if p_disb_header_id is null then
988
989 l_amount := calculateFee(p_fee_id => p_fee_id
990 ,p_loan_id => p_loan_id);
991
992 elsif p_disb_header_id is not null then
993
994 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - getting DIsb fee structures');
995 l_orig_fee_structures := lns_fee_engine.getDisbursementFeeStructures(p_loan_id => null
996 ,p_fee_category => 'EVENT'
997 ,p_fee_type => 'EVENT_ORIGINATION'
998 ,p_from_date => null
999 ,p_to_date => null
1000 ,p_disb_header_id => p_disb_header_id
1001 ,p_fee_id => p_fee_id);
1002 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - struct count is ' || l_orig_fee_structures.count);
1003 open c_origLoanAmt(p_loan_id);
1004 fetch c_origLoanAmt into l_original_loan_amount;
1005 close c_origLoanAmt;
1006 l_fee_basis_tbl(1).fee_basis_name := 'ORIG_LOAN';
1007 l_fee_basis_tbl(1).fee_basis_amount := l_original_loan_amount;
1008 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - orig_loan ' || l_original_loan_amount);
1009
1010 if l_orig_fee_structures.count = 0 then
1011 -- this fee has not yet been assigned to the loan, we need to get the fee structure from LNS_FEES, NOT LNS_FEE_ASSIGNMENTS
1012 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee is unassigned ');
1013 l_orig_fee_structures := getFeeStructures(p_fee_id => p_fee_id);
1014 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - structs ' || l_orig_fee_structures.count);
1015 open c_disbAmount(p_disb_header_id);
1016 fetch c_disbAmount into l_disb_amount;
1017 close c_disbAmount;
1018 l_fee_basis_tbl(2).fee_basis_name := 'IND_DISB_AMT';
1019 l_fee_basis_tbl(2).fee_basis_amount := l_disb_amount;
1020 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - disb amount ' || l_disb_amount);
1021 else
1022 l_fee_basis_tbl(2).fee_basis_name := 'IND_DISB_AMT';
1023 l_fee_basis_tbl(2).fee_basis_amount := l_orig_fee_structures(1).DISBURSEMENT_AMOUNT;
1024 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - disb amount ' || l_orig_fee_structures(1).DISBURSEMENT_AMOUNT);
1025 end if;
1026
1027
1028 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - calculating fees');
1029 calculateFees(p_loan_id => p_loan_id
1030 ,p_fee_basis_tbl => l_fee_basis_tbl
1031 ,p_installment => 0
1032 ,p_fee_structures => l_orig_fee_structures
1033 ,x_fees_tbl => l_fee_calc_tbl
1034 ,x_return_status => l_return_status
1035 ,x_msg_count => l_msg_count
1036 ,x_msg_data => l_msg_data);
1037 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fees count is ' || l_fee_calc_tbl.count);
1038 for k in 1..l_fee_calc_tbl.count
1039 loop
1040 l_amount := l_amount + l_fee_calc_tbl(k).FEE_AMOUNT;
1041 end loop;
1042
1043 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee total is ' || l_amount);
1044
1045 end if;
1046
1047 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee total is ' || l_amount);
1048 return l_amount;
1049
1050 exception
1051 when others then
1052 return l_amount;
1053
1054 end calculateFee;
1055
1056
1057 /*=========================================================================
1058 || PUBLIC PROCEDURE calculateFee ----- LNS.B
1059 ||
1060 || DESCRIPTION
1061 || Overview: this will calculate amount for a single fee for fee assignment page
1062 || fee MUST be assigned to the loan
1063 ||
1064 || PSEUDO CODE/LOGIC
1065 ||
1066 || PARAMETERS
1067 || Parameter: p_fee_id = fee to calculate
1068 || p_loan_id = loan to calculate for
1069 ||
1070 || KNOWN ISSUES
1071 ||
1072 || NOTES
1073 ||
1074 || MODIFICATION HISTORY
1075 || Date Author Description of Changes
1076 || 1/10/2005 8:40PM raverma Created
1077 ||
1078 *=======================================================================*/
1079 function calculateFee(p_fee_id IN NUMBER
1080 ,p_loan_id IN NUMBER) return number
1081 is
1082 l_api_name varchar2(25);
1083 l_return_status VARCHAR2(1);
1084 l_msg_count NUMBER;
1085 l_msg_data VARCHAR2(32767);
1086 l_calc_fee number;
1087 i number;
1088 l_fee_calc_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
1089 l_fee_basis_tbl LNS_FEE_ENGINE.FEE_BASIS_TBL;
1090 l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
1091 l_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
1092 l_installment Number;
1093
1094 cursor c_from_installment(p_fee_id number, p_loan_id number) is
1095 select nvl(BEGIN_INSTALLMENT_NUMBER,0)
1096 from lns_fee_assignments
1097 where fee_id = p_fee_id
1098 and loan_id = p_loan_id;
1099
1100 begin
1101
1102 l_calc_fee := 0;
1103
1104 l_api_name := 'calculateFee';
1105 -- compute the installment based on p_fee_assignment_id
1106 l_fee_structures := lns_fee_engine.getFeeStructures(p_loan_id => p_loan_id
1107 ,p_fee_category => null
1108 ,p_fee_type => null
1109 ,p_installment => null
1110 ,p_fee_id => p_fee_id);
1111 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - structs ' || l_fee_structures.count);
1112
1113 if l_fee_structures.count = 0 then
1114 -- this fee has not yet been assigned to the loan, we need to get the fee structure from LNS_FEES, NOT LNS_FEE_ASSIGNMENTS
1115 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee is unassigned ');
1116 l_fee_structures := getFeeStructures(p_fee_id => p_fee_id);
1117 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - structs ' || l_fee_structures.count);
1118 l_installment := l_fee_structures(1).fee_from_installment;
1119 else
1120 -- the fee has been assigned to the loan, get the fee_installment from the assignments table
1121 begin
1122 open c_from_installment(p_fee_id, p_loan_id);
1123 fetch c_from_installment into l_installment;
1124 close c_from_installment;
1125 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_installment ' || l_installment);
1126 exception
1127 when
1128 no_data_found then
1129 l_installment := 0;
1130 end;
1131 end if;
1132
1133 -- build the fee bases
1134 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - getting details');
1135 l_loan_details := lns_financials.getLoanDetails(p_loan_id => p_loan_id
1136 ,p_based_on_terms => 'ORIGINAL'
1137 ,p_phase => 'TERM');
1138
1139 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_installment ' || l_installment);
1140
1141 l_fee_basis_tbl(1).fee_basis_name := 'ORIG_LOAN';
1142 l_fee_basis_tbl(1).fee_basis_amount := l_loan_details.funded_amount;
1143 l_fee_basis_tbl(2).fee_basis_name := 'PREPAYMENT_AMOUNT';
1144 l_fee_basis_tbl(2).fee_basis_amount := l_loan_details.remaining_balance;
1145 l_fee_basis_tbl(3).fee_basis_name := 'TOTAL_BAL';
1146 l_fee_basis_tbl(3).fee_basis_amount := l_loan_details.remaining_balance;
1147 l_fee_basis_tbl(4).fee_basis_name := 'CURR_BAL';
1148 l_fee_basis_tbl(4).fee_basis_amount := 0;
1149 l_fee_basis_tbl(5).fee_basis_name := 'OVERDUE_PRIN';
1150 l_fee_basis_tbl(5).fee_basis_amount := 0;
1151 l_fee_basis_tbl(6).fee_basis_name := 'OVERDUE_PRIN_INT';
1152 l_fee_basis_tbl(6).fee_basis_amount := 0;
1153 l_fee_basis_tbl(7).fee_basis_name := 'IND_DISB_AMT';
1154 l_fee_basis_tbl(7).fee_basis_amount := l_loan_details.funded_amount;
1155 l_fee_basis_tbl(8).fee_basis_name := 'TOTAL_DISB_AMT';
1156 l_fee_basis_tbl(8).fee_basis_amount := l_loan_details.funded_amount;
1157
1158 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - calculating fees');
1159 calculateFees(p_loan_id => p_loan_id
1160 ,p_fee_basis_tbl => l_fee_basis_tbl
1161 ,p_installment => l_installment
1162 ,p_fee_structures => l_fee_structures
1163 ,x_fees_tbl => l_fee_calc_tbl
1164 ,x_return_status => l_return_status
1165 ,x_msg_count => l_msg_count
1166 ,x_msg_data => l_msg_data);
1167
1168 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fees count is ' || l_fee_calc_tbl.count);
1169 for k in 1..l_fee_calc_tbl.count
1170 loop
1171 l_calc_fee := l_calc_fee + l_fee_calc_tbl(k).FEE_AMOUNT;
1172 end loop;
1173
1174 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee total is ' || l_calc_fee);
1175 return l_calc_fee;
1176
1177 exception
1178 when others then
1179 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - ERROR ' || sqlerrm);
1180 return -1;
1181
1182 end calculateFee;
1183
1184 /*=========================================================================
1185 || PUBLIC PROCEDURE calculateFees ----- LNS.B
1186 ||
1187 || DESCRIPTION
1188 || Overview: this is main fee calculation engine
1189 ||
1190 || PSEUDO CODE/LOGIC
1191 ||
1192 || PARAMETERS
1193 || Parameter: p_fee_structures => fee structuring for the loan
1194 || p_fee_basis_tbl => fee bases
1195 || p_installment => installment number
1196 ||
1197 || Return value:
1198 || x_fees_tbl table of fees for a given installment
1199 || KNOWN ISSUES
1200 ||
1201 || NOTES
1202 ||
1203 || MODIFICATION HISTORY
1204 || Date Author Description of Changes
1205 || 12/16/2004 8:40PM raverma Created
1206 ||
1207 *=======================================================================*/
1208 procedure calculateFees(p_loan_id in number
1209 ,p_fee_basis_tbl in LNS_FEE_ENGINE.FEE_BASIS_TBL
1210 ,p_installment in number
1211 ,p_fee_structures IN LNS_FEE_ENGINE.FEE_STRUCTURE_TBL
1212 ,x_fees_tbl OUT nocopy LNS_FEE_ENGINE.FEE_CALC_TBL
1213 ,x_return_status out nocopy varchar2
1214 ,x_msg_count out nocopy number
1215 ,x_msg_data out nocopy varchar2)
1216 is
1217 l_api_name varchar2(25);
1218 l_basis_amount number;
1219 l_total_fees number;
1220 l_fees_tbl lns_fee_engine.fee_calc_tbl;
1221 k number;
1222 l_fee number;
1223 l_precision number;
1224 l_intervals number;
1225
1226 cursor c_precision (p_loan_id number)
1227 is
1228 SELECT fndc.precision
1229 FROM lns_loan_headers_all lnh
1230 ,fnd_currencies fndc
1231 WHERE lnh.loan_id = p_loan_id
1232 and lnh.loan_currency = fndc.currency_code;
1233
1234 begin
1235
1236 l_api_name := 'calculateFees';
1237 l_total_fees := 0;
1238 l_intervals := 1;
1239
1240 -- first figure out if fee applies to the current installment
1241 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1242 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - # fee structures ' || p_fee_structures.count);
1243 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - # fee basis ' || p_fee_basis_tbl.count);
1244
1245 open c_precision(p_loan_id);
1246 fetch c_precision into l_precision;
1247 close c_precision;
1248
1249 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - # fee precision ' || l_precision);
1250
1251 for f in 1..p_fee_structures.count loop
1252 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' -------- STRUCTURE ' || f);
1253 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - from inst ' || p_fee_structures(f).fee_from_installment );
1254 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - to inst ' || p_fee_structures(f).fee_to_installment );
1255 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - basis ' || p_fee_structures(f).fee_basis );
1256 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - billing option ' || p_fee_structures(f).fee_billing_option);
1257 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - rate ' || p_fee_structures(f).fee_rate_type );
1258
1259 l_fee := 0;
1260
1261 if p_installment >= p_fee_structures(f).fee_from_installment and
1262 p_installment <= p_fee_structures(f).fee_to_installment then
1263
1264 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fees ' || f || ' applies to this installment ');
1265
1266 if p_fee_structures(f).fee_billing_option = 'EQUALLY' then
1267 l_intervals := p_fee_structures(f).fee_to_installment - p_fee_structures(f).fee_from_installment + 1;
1268 end if;
1269
1270 if p_fee_structures(f).fee_rate_type = 'FIXED' then
1271 if p_installment = p_fee_structures(f).fee_to_installment then
1272 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - amount is : ' || p_fee_structures(f).fee_amount);
1273 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - raw: ' || p_fee_structures(f).fee_amount / l_intervals);
1274 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - raw2 - ' || round((p_fee_structures(f).fee_amount / l_intervals),2) * (l_intervals - 1));
1275 l_fee := p_fee_structures(f).fee_amount - round((p_fee_structures(f).fee_amount / l_intervals),l_precision) * (l_intervals - 1);
1276 else
1277 l_fee := round(p_fee_structures(f).fee_amount / l_intervals, l_precision);
1278 end if;
1279
1280 elsif p_fee_structures(f).fee_rate_type = 'VARIABLE' then
1281 begin
1282 k := 0;
1283 LOOP
1284 k := k + 1;
1285 --logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee basis ' || p_fee_basis_tbl(k).fee_basis_name);
1286 --logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee basis amount ' || p_fee_basis_tbl(k).fee_basis_amount);
1287 EXIT WHEN p_fee_basis_tbl(k).fee_basis_name = p_fee_structures(f).fee_basis;
1288 END LOOP;
1289 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee basis ' || p_fee_basis_tbl(k).fee_basis_name);
1290 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee basis amount ' || p_fee_basis_tbl(k).fee_basis_amount);
1291
1292 l_basis_amount := p_fee_basis_tbl(k).fee_basis_amount;
1293
1294 if p_installment = p_fee_structures(f).fee_to_installment then
1295 l_fee := (p_fee_structures(f).fee_amount * l_basis_amount) - round((p_fee_structures(f).fee_amount / l_intervals),l_precision) * (l_intervals - 1);
1296 else
1297 l_fee := round(p_fee_structures(f).fee_amount * l_basis_amount / l_intervals,l_precision) ;
1298 end if;
1299
1300 exception
1301 when no_data_found then
1302 l_fee := -1;
1303 --FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_CALC_ERROR');
1304 --FND_MSG_PUB.ADD;
1305 --RAISE FND_API.G_EXC_ERROR;
1306 end;
1307 end if;
1308
1309 end if;
1310 --assign output table of fees for each fee structure
1311 l_fees_tbl(f).fee_id := p_fee_structures(f).fee_id;
1312 l_fees_tbl(f).fee_name := p_fee_structures(f).fee_name;
1313 l_fees_tbl(f).fee_amount := round(l_fee, l_precision);
1314 l_fees_tbl(f).fee_installment := p_installment;
1315 l_fees_tbl(f).fee_description := null;
1316 l_fees_tbl(f).fee_schedule_id := -1; --assign this AFTER insert into fee_schedules
1317 l_fees_tbl(f).fee_waivable_flag := 'N';
1318 l_fees_tbl(f).FEE_DELETABLE_FLAG := 'N'; --from getFeeStructures
1319 l_fees_tbl(f).FEE_EDITABLE_FLAG := 'N'; --from getFeeStructures
1320 l_total_fees := l_total_fees + l_fees_tbl(f).fee_amount;
1321 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee amount' || l_fee);
1322
1323 end loop;
1324
1325 x_fees_tbl := l_fees_tbl;
1326 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - calculated fees: ' || l_total_fees);
1327
1328 end calculateFees;
1329
1330 /*=========================================================================
1331 || PUBLIC PROCEDURE getFeeSchedule
1332 ||
1333 || DESCRIPTION
1334 || Overview: this procedure will return a table of fees off of the fee
1335 || schedule for the given installment
1336 ||
1337 || THIS WILL BE CALLED BY MAIN AMORTIZATION FUNCTION TO RETURN ACTUAL FEES
1338 || TO BE BILLED ON A LOAN
1339 ||
1340 || PSEUDO CODE/LOGIC
1341 ||
1342 || PARAMETERS
1343 || Parameter: p_fee_structure_tbl => represents a table of fees
1344 || p_loan_id => loan_id
1345 ||
1346 || Return value:
1347 || standard
1348 || KNOWN ISSUES
1349 ||
1350 || NOTES
1351 ||
1352 || MODIFICATION HISTORY
1353 || Date Author Description of Changes
1354 || 12/1/2004 8:40PM raverma Created
1355 || 7/29.2005 raverma check for disb_header_id is null
1356 *=======================================================================*/
1357 procedure getFeeSchedule(p_init_msg_list in varchar2
1358 ,p_loan_id in number
1359 ,p_installment_number in number
1360 ,p_disb_header_id in number
1361 ,x_fees_tbl OUT NOCOPY LNS_FEE_ENGINE.FEE_CALC_TBL
1362 ,x_return_status out nocopy varchar2
1363 ,x_msg_count out nocopy number
1364 ,x_msg_data out nocopy varchar2)
1365 is
1366 l_api_name varchar2(25);
1367 l_return_status VARCHAR2(1);
1368 l_msg_count NUMBER;
1369 l_msg_data VARCHAR2(32767);
1370
1371 i number;
1372 l_fee_rec FEE_CALC_REC;
1373 l_fee_schedule_id number;
1374 l_fee_id number;
1375 l_fee_amount number;
1376 l_fee_name varchar2(50);
1377 l_fee_installment number;
1378 l_fee_description varchar2(250);
1379 l_fee_waivable_flag varchar2(1);
1380 l_fee_category varchar2(30);
1381 l_fee_type varchar2(30);
1382 l_fee_deletable_flag varchar2(1);
1383 l_fee_editable_flag varchar2(1);
1384
1385 -- unbilled fees on the schedule
1386 cursor c_fees(p_loan_id number, p_installment number) is
1387 select sched.fee_schedule_id
1388 ,sched.fee_id
1389 ,sched.fee_amount - nvl(sched.waived_amount, 0)
1390 ,struct.fee_name
1391 ,struct.fee_category
1392 ,struct.fee_type
1393 ,sched.fee_installment
1394 ,struct.fee_description
1395 ,sched.fee_waivable_flag -- should be struct right
1396 ,decode(struct.fee_category, 'MANUAL', 'Y', 'N')
1397 ,nvl(struct.fee_editable_flag, 'N')
1398 from lns_fee_schedules sched
1399 ,lns_fees struct
1400 where sched.loan_id = p_loan_id
1401 and sched.fee_id = struct.fee_id
1402 and fee_installment = p_installment
1403 and active_flag = 'Y'
1404 and billed_flag = 'N' -- deduce this based on parent records
1405 -- Bug#6961250 commented below line as for disbFees, disb_header_id is
1406 -- NOT NULL
1407 -- and disb_header_id is null
1408 and (not exists
1409 (select 'X'
1410 from lns_amortization_scheds am
1411 ,lns_amortization_lines lines
1412 where lines.loan_id = p_loan_id
1413 and lines.fee_schedule_id = sched.fee_schedule_id
1414 and am.loan_id = lines.loan_id
1415 and NVL(am.reversed_flag, 'N') = 'N'
1416 and am.payment_number = p_installment)
1417 or exists
1418 (select 'X'
1419 from lns_amortization_scheds am
1420 ,lns_amortization_lines lines
1421 where lines.loan_id = p_loan_id
1422 and lines.fee_schedule_id = sched.fee_schedule_id
1423 and am.loan_id = lines.loan_id
1424 and am.reversed_flag = 'Y'
1425 and am.payment_number = p_installment));
1426
1427 -- for openPhase
1428 cursor c_feeSchedule(p_disb_header_id number) is
1429 select sched.fee_id
1430 ,sched.fee_amount - nvl(sched.waived_amount, 0)
1431 ,struct.fee_name
1432 ,struct.fee_description
1433 from lns_fee_schedules sched
1434 ,lns_fees_all struct
1435 where disb_header_id = p_disb_header_id
1436 and sched.fee_id = struct.fee_id;
1437
1438 begin
1439
1440 l_api_name := 'getFees';
1441 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1442
1443 -- Standard Start of API savepoint
1444 SAVEPOINT getFees;
1445
1446 -- Initialize message list IF p_init_msg_list is set to TRUE.
1447 IF FND_API.to_Boolean(p_init_msg_list) THEN
1448 FND_MSG_PUB.initialize;
1449 END IF;
1450
1451 -- Initialize API return status to SUCCESS
1452 x_return_status := FND_API.G_RET_STS_SUCCESS;
1453
1454 -- ---------------------------------------------------------------------
1455 -- Api body
1456 -- ---------------------------------------------------------------------
1457 -- initialize any variables here
1458 i := 0;
1459
1460 if p_disb_header_id is null then
1461 OPEN c_fees(p_loan_id, p_installment_number);
1462 LOOP
1463 i := i + 1;
1464 FETCH c_fees INTO
1465 l_fee_schedule_id
1466 ,l_fee_id
1467 ,l_fee_amount
1468 ,l_fee_name
1469 ,l_fee_category
1470 ,l_fee_type
1471 ,l_fee_installment
1472 ,l_fee_description
1473 ,l_fee_waivable_flag
1474 ,l_fee_deletable_flag
1475 ,l_fee_editable_flag;
1476 EXIT WHEN c_fees%NOTFOUND;
1477
1478 l_fee_rec.fee_schedule_id := l_fee_schedule_id;
1479 l_fee_rec.fee_id := l_fee_id;
1480 l_fee_rec.fee_amount := l_fee_amount;
1481 l_fee_rec.fee_name := l_fee_name;
1482 l_fee_rec.fee_category := l_fee_category;
1483 l_fee_rec.fee_type := l_fee_type;
1484 l_fee_rec.fee_installment := l_fee_installment;
1485 l_fee_rec.fee_description := l_fee_description;
1486 l_fee_rec.fee_waivable_flag := l_fee_waivable_flag;
1487 l_fee_rec.fee_deletable_flag := l_fee_deletable_flag;
1488 l_fee_rec.fee_editable_flag := l_fee_editable_flag;
1489
1490 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee #: ' || i);
1491 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee schedule id: ' || l_fee_rec.fee_schedule_id);
1492 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee id: ' || l_fee_rec.fee_id);
1493 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee amount: ' || l_fee_rec.fee_amount);
1494 x_fees_tbl(i) := l_fee_rec;
1495 END LOOP;
1496 elsif p_disb_header_id is not null then
1497 OPEN c_feeSchedule(p_disb_header_id);
1498 LOOP
1499 i := i + 1;
1500 FETCH c_feeSchedule INTO
1501 l_fee_id
1502 ,l_fee_amount
1503 ,l_fee_name
1504 --,l_fee_category
1505 --,l_fee_type
1506 --,l_fee_installment
1507 ,l_fee_description;
1508 --,l_fee_waivable_flag
1509 --,l_fee_deletable_flag
1510 --,l_fee_editable_flag;
1511 EXIT WHEN c_feeSchedule%NOTFOUND;
1512
1513 --l_fee_rec.fee_schedule_id := l_fee_schedule_id;
1514 l_fee_rec.fee_id := l_fee_id;
1515 l_fee_rec.fee_amount := l_fee_amount;
1516 l_fee_rec.fee_name := l_fee_name;
1517 --l_fee_rec.fee_category := l_fee_category;
1518 --l_fee_rec.fee_type := l_fee_type;
1519 --l_fee_rec.fee_installment := l_fee_installment;
1520 l_fee_rec.fee_description := l_fee_description;
1521 --l_fee_rec.fee_waivable_flag := l_fee_waivable_flag;
1522 --l_fee_rec.fee_deletable_flag := l_fee_deletable_flag;
1523 --l_fee_rec.fee_editable_flag := l_fee_editable_flag;
1524
1525 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee #: ' || i);
1526 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee schedule id: ' || l_fee_rec.fee_schedule_id);
1527 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee id: ' || l_fee_rec.fee_id);
1528 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee amount: ' || l_fee_rec.fee_amount);
1529 x_fees_tbl(i) := l_fee_rec;
1530 END LOOP;
1531 end if;
1532
1533 -- ---------------------------------------------------------------------
1534 -- End of API body
1535 -- ---------------------------------------------------------------------
1536 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1537
1538 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1539
1540 EXCEPTION
1541 WHEN FND_API.G_EXC_ERROR THEN
1542 x_return_status := FND_API.G_RET_STS_ERROR;
1543 x_msg_count := l_msg_count;
1544 x_msg_data := l_msg_data;
1545 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1546 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1547 ROLLBACK TO getFees;
1548
1549 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1550 x_return_status := FND_API.G_RET_STS_ERROR;
1551 x_msg_count := l_msg_count;
1552 x_msg_data := l_msg_data;
1553 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1554 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1555 ROLLBACK TO getFees;
1556
1557 WHEN OTHERS THEN
1558 x_return_status := FND_API.G_RET_STS_ERROR;
1559 x_msg_count := l_msg_count;
1560 x_msg_data := l_msg_data;
1561 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1562 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1563 ROLLBACK TO getFees;
1564 end getFeeSchedule;
1565
1566 /*=========================================================================
1567 || PUBLIC PROCEDURE getFeeDetails
1568 ||
1569 || DESCRIPTION
1570 || Overview: this procedure will return a table of fee details
1571 ||
1572 || PSEUDO CODE/LOGIC
1573 ||
1574 || PARAMETERS
1575 || Parameter: p_installment => fees for an installment
1576 || p_loan_id => loan_id
1577 || p_fee_basis_tbl => needed if we are calculating virtual records
1578 ||
1579 ||
1580 || Return value:
1581 || standard
1582 || KNOWN ISSUES
1583 ||
1584 || NOTES
1585 ||
1586 || MODIFICATION HISTORY
1587 || Date Author Description of Changes
1588 || 1/6/2005 8:40PM raverma Created
1589 || 1/19/2005 raverma added in calls to display origination fees
1590 *=======================================================================*/
1591 procedure getFeeDetails(p_init_msg_list in varchar2
1592 ,p_loan_id in number
1593 ,p_installment in number
1594 ,p_fee_basis_tbl in LNS_FEE_ENGINE.FEE_BASIS_TBL
1595 ,p_based_on_terms in varchar2
1596 ,p_phase in varchar2
1597 ,x_fees_tbl out nocopy LNS_FEE_ENGINE.FEE_CALC_TBL
1598 ,x_return_status out nocopy varchar2
1599 ,x_msg_count out nocopy number
1600 ,x_msg_data out nocopy varchar2)
1601
1602 is
1603 l_api_name varchar2(25);
1604 l_return_status VARCHAR2(1);
1605 l_msg_count NUMBER;
1606 l_msg_data VARCHAR2(32767);
1607 iFeeCount number;
1608 l_last_installment number;
1609 l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
1610 l_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL; --use for recurring
1611 l_orig_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL; --use for origination
1612 l_conv_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL; --use for conversion
1613 l_fees_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
1614 l_virtual_fees_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
1615 i number;
1616
1617 -- for disbursement phase
1618 l_fee_basis_tbl LNS_FEE_ENGINE.FEE_BASIS_TBL;
1619 l_payment_tbl LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
1620
1621 -- conversion fees
1622 cursor c_conv_fees(p_loan_id number) is
1623 select fee.fee_description
1624 ,fee.fee_name
1625 ,ass.fee
1626 ,fee.fee_category
1627 ,fee.fee_type
1628 from lns_fee_assignments ass
1629 ,lns_fees_all fee
1630 where ass.loan_id = p_loan_id
1631 and fee.fee_id = ass.fee_id
1632 and ass.fee_type = 'EVENT_CONVERSION';
1633
1634 begin
1635
1636 l_api_name := 'getFeeDetails';
1637 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1638
1639 -- Standard Start of API savepoint
1640 SAVEPOINT getFeeDetails;
1641
1642 -- Initialize message list IF p_init_msg_list is set to TRUE.
1643 IF FND_API.to_Boolean(p_init_msg_list) THEN
1644 FND_MSG_PUB.initialize;
1645 END IF;
1646
1647 -- Initialize API return status to SUCCESS
1648 x_return_status := FND_API.G_RET_STS_SUCCESS;
1649
1650 -- ---------------------------------------------------------------------
1651 -- Api body
1652 -- ---------------------------------------------------------------------
1653
1654 i := 0;
1655 select LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER_EXT(p_loan_id) + 1
1656 into l_last_installment
1657 from dual;
1658
1659 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_installment is: ' || p_installment);
1660 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_based_on_terms: ' || p_based_on_terms);
1661 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee_basis passed is: ' || p_fee_basis_tbl.count);
1662 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_phase is: ' || p_phase);
1663 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - last installment is: ' || l_last_installment);
1664
1665 if p_phase = 'OPEN' then
1666
1667 --if p_installment <= l_last_installment and p_based_on_terms = 'CURRENT' then
1668
1669
1670 --elsif -- we going by original amortization schedule
1671 -- get disbursement_ids for timePeriod
1672 l_loan_Details := lns_financials.getLoanDetails(p_loan_id, p_based_on_terms, 'OPEN');
1673
1674 -- 0. buildPaymentSchedule
1675 l_payment_tbl := lns_fin_utils.buildPaymentSchedule(p_loan_start_date => l_loan_Details.loan_start_date
1676 ,p_loan_maturity_date => l_loan_details.maturity_date
1677 ,p_first_pay_date => l_loan_details.first_payment_date
1678 ,p_num_intervals => l_loan_details.number_installments
1679 ,p_interval_type => l_loan_details.payment_frequency
1680 ,p_pay_in_arrears => l_loan_details.pay_in_arrears_boolean);
1681
1682 -- fetch all disbursement_ids for given period
1683 l_fee_structures := lns_fee_engine.getDisbursementFeeStructures(p_loan_id => p_loan_id
1684 ,p_fee_category => null
1685 ,p_fee_type => null
1686 ,p_from_date => l_payment_tbl(p_installment).period_begin_date
1687 ,p_to_date => l_payment_tbl(p_installment).period_end_date
1688 ,p_disb_header_id => null
1689 ,p_fee_id => null);
1690
1691 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': fee structures count is ' || l_fee_structures.count);
1692
1693 -- calculate the fees one by one
1694 for j in 1..l_fee_structures.count
1695 loop
1696 -- get the fee basis
1697 -- get the total disbursed thru the target date of the fee
1698 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': IND_DISB_AMOUNT ' || l_fee_structures(j).disbursement_amount);
1699 l_fee_basis_tbl(1).fee_basis_name := 'IND_DISB_AMT';
1700 l_fee_basis_tbl(1).fee_basis_amount := l_fee_structures(j).disbursement_amount;
1701 l_fee_basis_tbl(2).fee_basis_name := 'ORIG_LOAN';
1702 l_fee_basis_tbl(2).fee_basis_amount := l_loan_details.funded_amount;
1703 l_orig_fee_structures(1) := l_fee_structures(j);
1704 lns_fee_engine.calculateFees(p_loan_id => p_loan_id
1705 ,p_installment => 0
1706 ,p_fee_basis_tbl => l_fee_basis_tbl
1707 ,p_fee_structures => l_fee_structures
1708 ,x_fees_tbl => l_virtual_fees_tbl
1709 ,x_return_status => l_return_status
1710 ,x_msg_count => l_msg_count
1711 ,x_msg_data => l_msg_data);
1712 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': calculated origination fees ' || l_virtual_fees_tbl.count);
1713
1714 end loop;
1715
1716 iFeeCount := 0;
1717 for k in 1..l_virtual_fees_tbl.count loop
1718 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - k ' || k);
1719 l_fees_tbl(iFeeCount + k) := l_virtual_fees_tbl(k);
1720 l_fees_tbl(iFeeCount + k).FEE_DESCRIPTION := l_fee_structures(k).fee_description;
1721 l_fees_tbl(iFeeCount + k).FEE_CATEGORY := l_fee_structures(k).fee_category;
1722 l_fees_tbl(iFeeCount + k).FEE_TYPE := l_fee_structures(k).fee_type;
1723 end loop;
1724
1725 --end if; -- p_installment / p_based_on_terms
1726
1727 elsif p_phase = 'TERM' then
1728
1729 -- this installment is actual installment on current amortization
1730 if p_installment <= l_last_installment and p_based_on_terms = 'CURRENT' then
1731
1732 lns_fee_engine.getFeeSchedule(p_init_msg_list => p_init_msg_list
1733 ,p_loan_id => p_loan_id
1734 ,p_installment_number => p_installment
1735 ,p_disb_header_id => null
1736 ,x_fees_tbl => l_fees_tbl
1737 ,x_return_status => l_return_status
1738 ,x_msg_count => l_msg_count
1739 ,x_msg_data => l_msg_data);
1740
1741 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1742 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_SCHEDULE_READ_ERROR');
1743 FND_MSG_PUB.ADD;
1744 RAISE FND_API.G_EXC_ERROR;
1745 end if;
1746
1747 iFeeCount := l_fees_tbl.count;
1748 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - found real fees ' || l_fees_tbl.count);
1749
1750 -- get any recurring fees yet to be placed on the fee schedule
1751 l_fee_structures := lns_fee_engine.getFeeStructures(p_loan_id => p_loan_id
1752 ,p_fee_category => 'RECUR'
1753 ,p_fee_type => null
1754 ,p_installment => p_installment
1755 ,p_fee_id => null);
1756
1757 if l_fee_structures.count > 0 then
1758 lns_fee_engine.calculateFees(p_loan_id => p_loan_id
1759 ,p_installment => p_installment
1760 ,p_fee_basis_tbl => p_fee_basis_tbl
1761 ,p_fee_structures => l_fee_structures
1762 ,x_fees_tbl => l_virtual_fees_tbl
1763 ,x_return_status => l_return_status
1764 ,x_msg_count => l_msg_count
1765 ,x_msg_data => l_msg_data);
1766
1767 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - virtual recurring fees ' || l_virtual_fees_tbl.count);
1768 -- append virtual records to end of actual records
1769 for k in 1..l_virtual_fees_tbl.count loop
1770 l_fees_tbl(iFeeCount + k) := l_virtual_fees_tbl(k);
1771 end loop;
1772 end if;
1773
1774 iFeeCount := l_fees_tbl.count;
1775
1776 else -- we going by original amortization schedule
1777
1778 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - calculating ALL virtual fees ');
1779
1780 -- 08-02-05 raverma add conversion fees
1781 if p_installment = 0 then
1782 open c_conv_fees(p_loan_id);
1783 LOOP
1784 i := i + 1;
1785 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - conv fee found ' || i);
1786 FETCH c_conv_fees into
1787 l_fees_tbl(i).fee_description
1788 ,l_fees_tbl(i).fee_name
1789 ,l_fees_tbl(i).fee_amount
1790 ,l_fees_tbl(i).fee_category
1791 ,l_fees_tbl(i).fee_type;
1792 --l_fees_tbl(i).fee_schedule_id = -1;
1793 EXIT WHEN c_conv_fees%NOTFOUND;
1794 -- append virtual records to end of actual records
1795 iFeeCount := l_fees_tbl.count;
1796 end loop;
1797 end if;
1798 -- get all recurring fees yet to be placed on the fee schedule
1799 -- project amounts according to amortization schedule amounts
1800 l_fee_structures := lns_fee_engine.getFeeStructures(p_loan_id => p_loan_id
1801 ,p_fee_category => 'RECUR'
1802 ,p_fee_type => null
1803 ,p_installment => p_installment
1804 ,p_fee_id => null);
1805 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee structures ' || l_fee_structures.count);
1806
1807 if l_fee_structures.count > 0 then
1808 lns_fee_engine.calculateFees(p_loan_id => p_loan_id
1809 ,p_installment => p_installment
1810 ,p_fee_basis_tbl => p_fee_basis_tbl
1811 ,p_fee_structures => l_fee_structures
1812 ,x_fees_tbl => l_virtual_fees_tbl
1813 ,x_return_status => l_return_status
1814 ,x_msg_count => l_msg_count
1815 ,x_msg_data => l_msg_data);
1816 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - found virtual recurring fees ' || l_virtual_fees_tbl.count);
1817 -- append virtual records to end of actual records
1818 iFeeCount := l_fees_tbl.count;
1819 for k in 1..l_virtual_fees_tbl.count loop
1820 l_fees_tbl(iFeeCount + k) := l_virtual_fees_tbl(k);
1821 l_fees_tbl(iFeeCount + k).FEE_DESCRIPTION := l_fee_structures(k).fee_description;
1822 l_fees_tbl(iFeeCount + k).FEE_CATEGORY := l_fee_structures(k).fee_category;
1823 l_fees_tbl(iFeeCount + k).FEE_TYPE := l_fee_structures(k).fee_type;
1824 end loop;
1825 l_virtual_fees_tbl.delete;
1826 end if;
1827
1828 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - virtual FEES COUNT FINAL ' || l_fees_tbl.count);
1829 iFeeCount := l_fees_tbl.count;
1830 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - found iFeeCount fees ' || iFeeCount);
1831 if p_installment <= 1 then
1832 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - checking origination fees' );
1833 l_orig_fee_structures := lns_fee_engine.getFeeStructures(p_loan_id => p_loan_id
1834 ,p_fee_category => 'EVENT'
1835 ,p_fee_type => 'EVENT_ORIGINATION'
1836 ,p_installment => p_installment
1837 ,p_fee_id => null);
1838 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee structures ' || l_orig_fee_structures.count);
1839
1840 if l_orig_fee_structures.count > 0 then
1841 lns_fee_engine.calculateFees(p_loan_id => p_loan_id
1842 ,p_installment => p_installment
1843 ,p_fee_basis_tbl => p_fee_basis_tbl
1844 ,p_fee_structures => l_orig_fee_structures
1845 ,x_fees_tbl => l_virtual_fees_tbl
1846 ,x_return_status => l_return_status
1847 ,x_msg_count => l_msg_count
1848 ,x_msg_data => l_msg_data);
1849
1850 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - virtual orig fees ' || l_virtual_fees_tbl.count);
1851 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - iFeeCount ' || iFeeCount);
1852 -- append virtual records to end of actual records
1853 for k in 1..l_virtual_fees_tbl.count loop
1854 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - k ' || k);
1855 l_fees_tbl(iFeeCount + k) := l_virtual_fees_tbl(k);
1856 l_fees_tbl(iFeeCount + k).FEE_DESCRIPTION := l_orig_fee_structures(k).fee_description;
1857 l_fees_tbl(iFeeCount + k).FEE_CATEGORY := l_orig_fee_structures(k).fee_category;
1858 l_fees_tbl(iFeeCount + k).FEE_TYPE := l_orig_fee_structures(k).fee_type;
1859 end loop;
1860
1861 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - virtual FEES COUNT FINAL ' || l_fees_tbl.count);
1862 end if;
1863 end if;
1864 end if; --p_based_on_terms
1865 end if; --p_phase
1866
1867 x_fees_tbl := l_fees_tbl;
1868
1869 -- ---------------------------------------------------------------------
1870 -- End of API body
1871 -- ---------------------------------------------------------------------
1872 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1873
1874 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1875
1876 EXCEPTION
1877 WHEN FND_API.G_EXC_ERROR THEN
1878 x_return_status := FND_API.G_RET_STS_ERROR;
1879 x_msg_count := l_msg_count;
1880 x_msg_data := l_msg_data;
1881 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1882 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1883 ROLLBACK TO getFeeDetails;
1884
1885 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1886 x_return_status := FND_API.G_RET_STS_ERROR;
1887 x_msg_count := l_msg_count;
1888 x_msg_data := l_msg_data;
1889 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1890 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1891 ROLLBACK TO getFeeDetails;
1892
1893 WHEN OTHERS THEN
1894 x_return_status := FND_API.G_RET_STS_ERROR;
1895 x_msg_count := l_msg_count;
1896 x_msg_data := l_msg_data;
1897 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1898 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1899 ROLLBACK TO getFeeDetails;
1900
1901 end getFeeDetails;
1902
1903
1904 /*=========================================================================
1905 || PUBLIC PROCEDURE writeFees
1906 ||
1907 || DESCRIPTION
1908 || Overview: this procedure will validate and write a table of fees to
1909 || the fee_schedule table
1910 ||
1911 || PSEUDO CODE/LOGIC
1912 ||
1913 || PARAMETERS
1914 || Parameter: p_fee_structure_tbl => represents a table of fees
1915 || p_loan_id => loan_id
1916 ||
1917 || Return value:
1918 || standard
1919 || KNOWN ISSUES
1920 ||
1921 || NOTES
1922 ||
1923 || MODIFICATION HISTORY
1924 || Date Author Description of Changes
1925 || 1/7/2005 8:40PM raverma Created
1926 ||
1927 *=======================================================================*/
1928 procedure updateFeeSchedule(p_init_msg_list in varchar2
1929 ,p_commit in varchar2
1930 ,p_loan_id in number
1931 ,p_fees_tbl IN LNS_FEE_ENGINE.FEE_CALC_TBL
1932 ,x_return_status out nocopy varchar2
1933 ,x_msg_count out nocopy number
1934 ,x_msg_data out nocopy varchar2)
1935 is
1936 l_api_name varchar2(25);
1937 l_return_status VARCHAR2(1);
1938 l_msg_count NUMBER;
1939 l_msg_data VARCHAR2(32767);
1940
1941 l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
1942 l_fee_schedule_id NUMBER;
1943 i number;
1944
1945 cursor c_fee_schedule_id (p_loan_id number, p_fee_id number) is
1946 select fee_schedule_id
1947 from lns_fee_schedules
1948 where loan_id = p_loan_id
1949 and fee_id = p_fee_id
1950 and billed_flag = 'N'
1951 and active_flag = 'Y';
1952
1953 l_precision number;
1954 l_intervals number;
1955 l_phase varchar2(30);
1956
1957 cursor c_phase(p_loan_id number) is
1958 select nvl(current_phase, 'TERM')
1959 from lns_loan_headers
1960 where loan_id = p_loan_id;
1961
1962 cursor c_precision (p_loan_id number)
1963 is
1964 SELECT fndc.precision
1965 FROM lns_loan_headers lnh
1966 ,fnd_currencies fndc
1967 WHERE lnh.loan_id = p_loan_id
1968 and lnh.loan_currency = fndc.currency_code;
1969 begin
1970
1971 l_api_name := 'updateFeeSchedule';
1972 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1973
1974 -- Standard Start of API savepoint
1975 SAVEPOINT updateFeeSchedule;
1976
1977 -- Initialize message list IF p_init_msg_list is set to TRUE.
1978 IF FND_API.to_Boolean(p_init_msg_list) THEN
1979 FND_MSG_PUB.initialize;
1980 END IF;
1981
1982 -- Initialize API return status to SUCCESS
1983 x_return_status := FND_API.G_RET_STS_SUCCESS;
1984
1985 -- ---------------------------------------------------------------------
1986 -- Api body
1987 -- ---------------------------------------------------------------------
1988 -- initialize any variables here
1989
1990 open c_precision(p_loan_id);
1991 fetch c_precision into l_precision;
1992 close c_precision;
1993
1994 lns_utility_pub.validate_any_id(p_api_version => 1.0
1995 ,p_init_msg_list => p_init_msg_list
1996 ,x_msg_count => l_msg_count
1997 ,x_msg_data => l_msg_data
1998 ,x_return_status => l_return_status
1999 ,p_col_id => p_loan_id
2000 ,p_col_name => 'LOAN_ID'
2001 ,p_table_name => 'LNS_LOAN_HEADERS_ALL');
2002
2003 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2004 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2005 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
2006 FND_MESSAGE.SET_TOKEN('VALUE', p_loan_ID);
2007 FND_MSG_PUB.ADD;
2008 RAISE FND_API.G_EXC_ERROR;
2009 end if;
2010
2011 open c_phase(p_loan_id);
2012 fetch c_phase into l_phase;
2013 close c_phase;
2014
2015 l_loan_details := lns_financials.getLoanDetails(p_loan_id, 'CURRENT', l_phase);
2016 i := p_fees_tbl.count;
2017 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - found ' || i || 'fee structures');
2018
2019 -- validate all structures
2020 for k in 1..i loop
2021
2022 -- first validation as per june : do not add fees far into the future
2023 if p_fees_tbl(k).fee_installment > l_loan_details.LAST_INSTALLMENT_BILLED + 1 then
2024 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_INSTALLMENT_ERROR3');
2025 FND_MSG_PUB.ADD;
2026 RAISE FND_API.G_EXC_ERROR;
2027 end if;
2028
2029
2030 if p_fees_tbl(k).fee_installment < l_loan_details.LAST_INSTALLMENT_BILLED then
2031 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_INSTALLMENT_ERROR1');
2032 FND_MSG_PUB.ADD;
2033 RAISE FND_API.G_EXC_ERROR;
2034 end if;
2035
2036 if p_fees_tbl(k).fee_installment > l_loan_details.NUMBER_INSTALLMENTS then
2037 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_INSTALLMENT_ERROR2');
2038 FND_MSG_PUB.ADD;
2039 RAISE FND_API.G_EXC_ERROR;
2040 end if;
2041
2042 if p_fees_tbl(k).FEE_AMOUNT is null or p_fees_tbl(k).FEE_AMOUNT < 0 then
2043 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_FEE_AMOUNT');
2044 FND_MSG_PUB.ADD;
2045 RAISE FND_API.G_EXC_ERROR;
2046 end if;
2047
2048 if p_fees_tbl(k).FEE_ID is not null then
2049 lns_utility_pub.validate_any_id(p_api_version => 1.0
2050 ,p_init_msg_list => p_init_msg_list
2051 ,x_msg_count => l_msg_count
2052 ,x_msg_data => l_msg_data
2053 ,x_return_status => l_return_status
2054 ,p_col_id => p_fees_tbl(k).FEE_ID
2055 ,p_col_name => 'FEE_ID'
2056 ,p_table_name => 'LNS_FEES');
2057
2058 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2059 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2060 FND_MESSAGE.SET_TOKEN('PARAMETER', 'FEE_ID');
2061 FND_MESSAGE.SET_TOKEN('VALUE', p_fees_tbl(k).FEE_ID);
2062 FND_MSG_PUB.ADD;
2063 RAISE FND_API.G_EXC_ERROR;
2064 end if;
2065
2066 end if;
2067
2068 if p_fees_tbl(k).FEE_SCHEDULE_ID is null then
2069 -- we have an origination fee
2070 open c_fee_schedule_id(p_loan_id, p_fees_tbl(k).FEE_ID);
2071 fetch c_fee_schedule_id into l_fee_schedule_id;
2072 close c_fee_schedule_id;
2073
2074 else
2075 l_fee_schedule_id := p_fees_tbl(k).FEE_SCHEDULE_ID;
2076
2077 end if;
2078 -- fee structure has been validated write to the fee schedule
2079 LNS_FEE_SCHEDULES_PKG.UPDATE_ROW(P_FEE_SCHEDULE_ID => l_fee_schedule_id
2080 ,P_FEE_ID => p_fees_tbl(k).FEE_ID
2081 ,P_LOAN_ID => p_loan_id
2082 ,P_FEE_AMOUNT => round(p_fees_tbl(k).FEE_AMOUNT,l_precision)
2083 ,P_FEE_INSTALLMENT => p_fees_tbl(k).FEE_INSTALLMENT
2084 ,P_FEE_DESCRIPTION => p_fees_tbl(k).FEE_DESCRIPTION
2085 ,P_ACTIVE_FLAG => p_fees_tbl(k).ACTIVE_FLAG --'Y'
2086 ,P_BILLED_FLAG => p_fees_tbl(k).BILLED_FLAG --'N'
2087 ,P_FEE_WAIVABLE_FLAG => p_fees_tbl(k).FEE_WAIVABLE_FLAG
2088 ,P_WAIVED_AMOUNT => null
2089 ,P_LAST_UPDATED_BY => lns_utility_pub.last_updated_by
2090 ,P_LAST_UPDATE_DATE => lns_utility_pub.last_update_date
2091 ,P_LAST_UPDATE_LOGIN => null
2092 ,P_PROGRAM_ID => null
2093 ,P_REQUEST_ID => null
2094 ,P_OBJECT_VERSION_NUMBER => 1);
2095 end loop;
2096 -- ---------------------------------------------------------------------
2097 -- End of API body
2098 -- ---------------------------------------------------------------------
2099
2100 IF FND_API.to_Boolean(p_commit) THEN
2101 COMMIT WORK;
2102 END IF;
2103
2104 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2105
2106 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
2107
2108 EXCEPTION
2109 WHEN FND_API.G_EXC_ERROR THEN
2110 x_return_status := FND_API.G_RET_STS_ERROR;
2111 x_msg_count := l_msg_count;
2112 x_msg_data := l_msg_data;
2113 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2114 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2115 ROLLBACK TO updateFeeSchedule;
2116
2117 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2118 x_return_status := FND_API.G_RET_STS_ERROR;
2119 x_msg_count := l_msg_count;
2120 x_msg_data := l_msg_data;
2121 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2122 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2123 ROLLBACK TO updateFeeSchedule;
2124
2125 WHEN OTHERS THEN
2126 x_return_status := FND_API.G_RET_STS_ERROR;
2127 x_msg_count := l_msg_count;
2128 x_msg_data := l_msg_data;
2129 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2130 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2131 ROLLBACK TO updateFeeSchedule;
2132
2133 end updateFeeSchedule;
2134
2135 /*=========================================================================
2136 || PUBLIC PROCEDURE writeFees
2137 ||
2138 || DESCRIPTION
2139 || Overview: this procedure will validate and write a table of fees to
2140 || the fee_schedule table
2141 ||
2142 || PSEUDO CODE/LOGIC
2143 ||
2144 || PARAMETERS
2145 || Parameter: p_fee_structure_tbl => represents a table of fees
2146 || p_loan_id => loan_id
2147 ||
2148 || Return value:
2149 || standard
2150 || KNOWN ISSUES
2151 ||
2152 || NOTES
2153 ||
2154 || MODIFICATION HISTORY
2155 || Date Author Description of Changes
2156 || 12/1/2004 8:40PM raverma Created
2157 || 07/21/2005 raverma support OPEN phase
2158 *=======================================================================*/
2159 procedure writeFeeSchedule(p_init_msg_list in varchar2
2160 ,p_commit in varchar2
2161 ,p_loan_id in number
2162 ,p_fees_tbl IN OUT NOCOPY LNS_FEE_ENGINE.FEE_CALC_TBL
2163 ,x_return_status out nocopy varchar2
2164 ,x_msg_count out nocopy number
2165 ,x_msg_data out nocopy varchar2)
2166
2167 is
2168 l_api_name varchar2(25);
2169 l_return_status VARCHAR2(1);
2170 l_msg_count NUMBER;
2171 l_msg_data VARCHAR2(32767);
2172
2173 l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
2174 l_fee_schedule_id NUMBER;
2175 i number;
2176
2177 cursor c_phase(p_loan_id number) is
2178 select nvl(current_phase, 'TERM')
2179 from lns_loan_headers_all
2180 where loan_id = p_loan_id;
2181
2182 l_phase varchar2(30);
2183
2184 begin
2185
2186 l_api_name := 'writeFeeSchedule';
2187 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2188
2189 -- Standard Start of API savepoint
2190 SAVEPOINT writeFeeSchedule;
2191
2192 -- Initialize message list IF p_init_msg_list is set to TRUE.
2193 IF FND_API.to_Boolean(p_init_msg_list) THEN
2194 FND_MSG_PUB.initialize;
2195 END IF;
2196
2197 -- Initialize API return status to SUCCESS
2198 x_return_status := FND_API.G_RET_STS_SUCCESS;
2199
2200 -- ---------------------------------------------------------------------
2201 -- Api body
2202 -- ---------------------------------------------------------------------
2203 -- initialize any variables here
2204 lns_utility_pub.validate_any_id(p_api_version => 1.0
2205 ,p_init_msg_list => p_init_msg_list
2206 ,x_msg_count => l_msg_count
2207 ,x_msg_data => l_msg_data
2208 ,x_return_status => l_return_status
2209 ,p_col_id => p_loan_id
2210 ,p_col_name => 'LOAN_ID'
2211 ,p_table_name => 'LNS_LOAN_HEADERS_ALL');
2212
2213 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2214 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2215 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
2216 FND_MESSAGE.SET_TOKEN('VALUE', p_loan_ID);
2217 FND_MSG_PUB.ADD;
2218 RAISE FND_API.G_EXC_ERROR;
2219 end if;
2220
2221 open c_phase(p_loan_id);
2222 fetch c_phase into l_phase;
2223 close c_phase;
2224
2225 l_loan_details := lns_financials.getLoanDetails(p_loan_id, 'CURRENT', l_phase);
2226 i := p_fees_tbl.count;
2227 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - found ' || i || 'fee structures');
2228
2229 -- validate all structures
2230 for k in 1..i loop
2231
2232 -- bypass this validation temporarily until more time for disbursement fees
2233 if p_fees_tbl(k).disb_header_id is null then
2234 -- first validation as per june : do not add fees far into the future
2235 if p_fees_tbl(k).fee_installment > l_loan_details.LAST_INSTALLMENT_BILLED + 1 then
2236 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_INSTALLMENT_ERROR3');
2237 FND_MSG_PUB.ADD;
2238 RAISE FND_API.G_EXC_ERROR;
2239 end if;
2240
2241 if p_fees_tbl(k).fee_installment < l_loan_details.LAST_INSTALLMENT_BILLED then
2242 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_INSTALLMENT_ERROR1');
2243 FND_MSG_PUB.ADD;
2244 RAISE FND_API.G_EXC_ERROR;
2245 end if;
2246
2247 if p_fees_tbl(k).fee_installment > l_loan_details.NUMBER_INSTALLMENTS then
2248 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_INSTALLMENT_ERROR2');
2249 FND_MSG_PUB.ADD;
2250 RAISE FND_API.G_EXC_ERROR;
2251 end if;
2252
2253 if p_fees_tbl(k).FEE_AMOUNT is null or p_fees_tbl(k).FEE_AMOUNT <= 0 then
2254 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_FEE_AMOUNT');
2255 FND_MSG_PUB.ADD;
2256 RAISE FND_API.G_EXC_ERROR;
2257 end if;
2258
2259 if p_fees_tbl(k).FEE_ID is not null then
2260 lns_utility_pub.validate_any_id(p_api_version => 1.0
2261 ,p_init_msg_list => p_init_msg_list
2262 ,x_msg_count => l_msg_count
2263 ,x_msg_data => l_msg_data
2264 ,x_return_status => l_return_status
2265 ,p_col_id => p_fees_tbl(k).FEE_ID
2266 ,p_col_name => 'FEE_ID'
2267 ,p_table_name => 'LNS_FEES');
2268
2269 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2270 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2271 FND_MESSAGE.SET_TOKEN('PARAMETER', 'FEE_ID');
2272 FND_MESSAGE.SET_TOKEN('VALUE', p_fees_tbl(k).FEE_ID);
2273 FND_MSG_PUB.ADD;
2274 RAISE FND_API.G_EXC_ERROR;
2275 end if;
2276
2277 end if;
2278 else
2279 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - validation bypassed');
2280 end if; -- disb_header_id
2281 --
2282 select lns_fee_schedule_s.nextval
2283 into l_fee_schedule_id
2284 from dual;
2285
2286 -- fee structure has been validated write to the fee schedule
2287 LNS_FEE_SCHEDULES_PKG.INSERT_ROW(X_FEE_SCHEDULE_ID => l_fee_schedule_id
2288 ,P_FEE_ID => p_fees_tbl(k).FEE_ID
2289 ,P_LOAN_ID => p_loan_id
2290 ,P_FEE_AMOUNT => round(p_fees_tbl(k).FEE_AMOUNT, l_loan_details.currency_precision)
2291 ,P_FEE_INSTALLMENT => p_fees_tbl(k).FEE_INSTALLMENT
2292 ,P_FEE_DESCRIPTION => p_fees_tbl(k).FEE_DESCRIPTION
2293 ,P_ACTIVE_FLAG => 'Y'
2294 ,P_BILLED_FLAG => 'N'
2295 ,P_FEE_WAIVABLE_FLAG => p_fees_tbl(k).FEE_WAIVABLE_FLAG
2296 ,P_WAIVED_AMOUNT => null
2297 ,P_CREATED_BY => lns_utility_pub.created_by
2298 ,P_CREATION_DATE => lns_utility_pub.creation_date
2299 ,P_LAST_UPDATED_BY => lns_utility_pub.last_updated_by
2300 ,P_LAST_UPDATE_DATE => lns_utility_pub.last_update_date
2301 ,P_LAST_UPDATE_LOGIN => null
2302 ,P_PROGRAM_ID => null
2303 ,P_REQUEST_ID => null
2304 ,P_OBJECT_VERSION_NUMBER => 1
2305 ,P_DISB_HEADER_ID => p_fees_tbl(k).DISB_HEADER_ID);
2306
2307 p_fees_tbl(k).fee_schedule_id := l_fee_schedule_id;
2308
2309 end loop;
2310
2311 -- ---------------------------------------------------------------------
2312 -- End of API body
2313 -- ---------------------------------------------------------------------
2314 IF FND_API.to_Boolean(p_commit) THEN
2315 COMMIT WORK;
2316 END IF;
2317
2318 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2319
2320 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
2321
2322 EXCEPTION
2323 WHEN FND_API.G_EXC_ERROR THEN
2324 x_return_status := FND_API.G_RET_STS_ERROR;
2325 x_msg_count := l_msg_count;
2326 x_msg_data := l_msg_data;
2327 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2328 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2329 ROLLBACK TO writeFeeSchedule;
2330
2331 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2332 x_return_status := FND_API.G_RET_STS_ERROR;
2333 x_msg_count := l_msg_count;
2334 x_msg_data := l_msg_data;
2335 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2336 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2337 ROLLBACK TO writeFeeSchedule;
2338
2339 WHEN OTHERS THEN
2340 x_return_status := FND_API.G_RET_STS_ERROR;
2341 x_msg_count := l_msg_count;
2342 x_msg_data := l_msg_data;
2343 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2344 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2345 ROLLBACK TO writeFeeSchedule;
2346
2347 end writeFeeSchedule;
2348
2349 /*=========================================================================
2350 || PUBLIC PROCEDURE processFees
2351 ||
2352 || DESCRIPTION
2353 || Overview: this procedure will be the hook for the application to
2354 || get, calculate and write fees to the fee schedule
2355 ||
2356 || PSEUDO CODE/LOGIC
2357 ||
2358 || PARAMETERS
2359 || Parameter: p_installment_number=> installment number to process
2360 || p_loan_header_rec => header level infor about loan
2361 || p_amortization_rec => installment level info about loan
2362 || p_fee_structures => TABLE of Fee_Category/Fee_Types to process
2363 || p_loan_id => loan_id
2364 || x_fees_tbl => table of records inserted
2365 ||
2366 || Return value:
2367 || standard
2368 || KNOWN ISSUES
2369 ||
2370 || NOTES
2371 ||
2372 || MODIFICATION HISTORY
2373 || Date Author Description of Changes
2374 || 12/1/2004 8:40PM raverma Created
2375 *=======================================================================*/
2376 procedure processFees(p_init_msg_list in varchar2
2377 ,p_commit in varchar2
2378 ,p_loan_id in number
2379 ,p_installment_number in number
2380 ,p_fee_basis_tbl in LNS_FEE_ENGINE.FEE_BASIS_TBL
2381 ,p_fee_structures in LNS_FEE_ENGINE.FEE_STRUCTURE_TBL
2382 ,x_fees_tbl OUT NOCOPY LNS_FEE_ENGINE.FEE_CALC_TBL
2383 ,x_return_status out nocopy varchar2
2384 ,x_msg_count out nocopy number
2385 ,x_msg_data out nocopy varchar2)
2386 is
2387
2388 l_api_name varchar2(25);
2389 l_return_status VARCHAR2(1);
2390 l_msg_count NUMBER;
2391 l_msg_data VARCHAR2(32767);
2392
2393 l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
2394 l_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
2395 l_fee_schedule_id NUMBER;
2396 l_fee_calc_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
2397 l_fee_category varchar2(30);
2398 l_fee_type varchar2(30);
2399 i number;
2400 l_processed_fees number;
2401
2402 cursor c_processed(p_loan_id number, p_installment number, p_category varchar2, p_type varchar2) is
2403 select nvl(sum(fee_amount), 0)
2404 from lns_fee_schedules sched
2405 ,lns_fees fees
2406 where sched.loan_id = p_loan_id
2407 and sched.fee_id = fees.fee_id
2408 and sched.fee_installment = p_installment
2409 and sched.active_flag = 'Y'
2410 and sched.billed_flag = 'Y' -- deduce this based on parent records
2411 and fees.fee_category = p_category
2412 and fees.fee_type = p_type
2413 and (exists
2414 (select 'X'
2415 from lns_amortization_scheds am
2416 ,lns_amortization_lines lines
2417 where am.loan_id = p_loan_id
2418 and am.amortization_schedule_id = lines.amortization_schedule_id
2419 and lines.fee_schedule_id = sched.fee_schedule_id
2420 and NVL(am.reversed_flag, 'N') = 'N'
2421 and am.payment_number = p_installment));
2422 begin
2423
2424 l_api_name := 'processFees';
2425 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2426
2427 -- Standard Start of API savepoint
2428 SAVEPOINT processFees;
2429
2430 -- Initialize message list IF p_init_msg_list is set to TRUE.
2431 IF FND_API.to_Boolean(p_init_msg_list) THEN
2432 FND_MSG_PUB.initialize;
2433 END IF;
2434
2435 -- Initialize API return status to SUCCESS
2436 x_return_status := FND_API.G_RET_STS_SUCCESS;
2437 l_processed_fees:= 0;
2438
2439 -- ---------------------------------------------------------------------
2440 -- Api body
2441 -- ---------------------------------------------------------------------
2442 -- initialize any variables here
2443 /* encapsulated API
2444 1. getFeeStructures for a particular event(s)
2445 2. calculate for installment
2446 3. write to schedule
2447 */
2448 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fees super structures to process ' || p_fee_structures.count);
2449 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - loan id ' || p_loan_id);
2450 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - installment ' || p_installment_number);
2451 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fees basis count ' || p_fee_basis_tbl.count);
2452
2453 open c_processed(p_loan_id, p_installment_number, p_fee_structures(1).fee_category, p_fee_structures(1).fee_type);
2454 fetch c_processed into l_processed_fees;
2455 close c_processed;
2456 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fees already processed ' || l_processed_fees);
2457
2458 if l_processed_fees = 0 then
2459 for i in 1..p_fee_structures.count loop
2460
2461 l_fee_category := p_fee_structures(i).fee_category;
2462 l_fee_type := p_fee_structures(i).fee_type;
2463
2464 if p_fee_structures(i).fee_category is null AND p_fee_structures(i).fee_type is null then
2465 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_CAT_TYPE_MISSING');
2466 FND_MSG_PUB.ADD;
2467 RAISE FND_API.G_EXC_ERROR;
2468 end if;
2469
2470 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee category ' || l_fee_category);
2471 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee type ' || l_fee_type);
2472 l_fee_structures := lns_fee_engine.getFeeStructures(p_loan_id => p_loan_id
2473 ,p_fee_category => l_fee_category
2474 ,p_fee_type => l_fee_type
2475 ,p_installment => p_installment_number
2476 ,p_fee_id => null);
2477 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee structures count is ' || l_fee_structures.count);
2478
2479 lns_fee_engine.calculateFees(p_loan_id => p_loan_id
2480 ,p_fee_basis_tbl => p_fee_basis_tbl
2481 ,p_installment => p_installment_number
2482 ,p_fee_structures => l_fee_structures
2483 ,x_fees_tbl => l_fee_calc_tbl
2484 ,x_return_status => l_return_status
2485 ,x_msg_count => l_msg_count
2486 ,x_msg_data => l_msg_data);
2487 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2488 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_CALCULATION_FAILURE');
2489 FND_MSG_PUB.ADD;
2490 RAISE FND_API.G_EXC_ERROR;
2491 end if;
2492
2493 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - calculated fees count is ' || l_fee_calc_tbl.count);
2494 lns_fee_engine.writeFeeSchedule(p_init_msg_list => p_init_msg_list
2495 ,p_commit => p_commit
2496 ,p_loan_id => p_loan_id
2497 ,p_fees_tbl => l_fee_calc_tbl
2498 ,x_return_status => l_return_status
2499 ,x_msg_count => l_msg_count
2500 ,x_msg_data => l_msg_data);
2501 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2502 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_WRITE_FAILURE');
2503 FND_MSG_PUB.ADD;
2504 RAISE FND_API.G_EXC_ERROR;
2505 end if;
2506 end loop;
2507 end if;
2508
2509 for k in 1..l_fee_calc_tbl.count loop
2510 x_fees_tbl(k) := l_fee_calc_tbl(k);
2511 end loop;
2512
2513 -- ---------------------------------------------------------------------
2514 -- End of API body
2515 -- ---------------------------------------------------------------------
2516
2517 IF FND_API.to_Boolean(p_commit) THEN
2518 COMMIT WORK;
2519 END IF;
2520
2521 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2522
2523 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
2524
2525 EXCEPTION
2526 WHEN FND_API.G_EXC_ERROR THEN
2527 x_return_status := FND_API.G_RET_STS_ERROR;
2528 x_msg_count := l_msg_count;
2529 x_msg_data := l_msg_data;
2530 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2531 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2532 ROLLBACK TO processFees;
2533
2534 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2535 x_return_status := FND_API.G_RET_STS_ERROR;
2536 x_msg_count := l_msg_count;
2537 x_msg_data := l_msg_data;
2538 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2539 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2540 ROLLBACK TO processFees;
2541
2542 WHEN OTHERS THEN
2543 x_return_status := FND_API.G_RET_STS_ERROR;
2544 x_msg_count := l_msg_count;
2545 x_msg_data := l_msg_data;
2546 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2547 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2548 ROLLBACK TO processFees;
2549
2550 end processFees;
2551
2552 /*=========================================================================
2553 || PUBLIC PROCEDURE waiveFee
2554 ||
2555 || DESCRIPTION
2556 || Overview: this procedure will waive a fee
2557 ||
2558 || PSEUDO CODE/LOGIC
2559 ||
2560 || PARAMETERS
2561 || Parameter: p_loan_id => loan_id
2562 || p_fee_schedule_id => pk
2563 || p_waive_amount => amount to be waived
2564 ||
2565 || Return value:
2566 || standard
2567 || KNOWN ISSUES
2568 ||
2569 || NOTES
2570 ||
2571 || MODIFICATION HISTORY
2572 || Date Author Description of Changes
2573 || 12/1/2004 8:40PM raverma Created
2574 ||
2575 *=======================================================================*/
2576 procedure waiveFee(p_init_msg_list in varchar2
2577 ,p_commit in varchar2
2578 ,p_loan_id in number
2579 ,p_fee_schedule_id in number
2580 ,p_waive_amount in number
2581 ,x_return_status out nocopy varchar2
2582 ,x_msg_count out nocopy number
2583 ,x_msg_data out nocopy varchar2)
2584 is
2585 l_api_name varchar2(25);
2586 l_return_status VARCHAR2(1);
2587 l_msg_count NUMBER;
2588 l_msg_data VARCHAR2(32767);
2589 l_fee_amount_remaining NUMBER;
2590 l_waived_amount NUMBER; --prior waived amount
2591
2592 -- so the only rule i can think of is the
2593 -- waived_amount cannot be > fee_amount less any previously waived amount
2594 cursor c_fee_waive_amount(p_fee_schedule_id number) is
2595 select sched.fee_amount - nvl(sched.waived_amount, 0) amount_remaining
2596 ,nvl(sched.waived_amount, 0) previously_waived
2597 from lns_fee_schedules sched
2598 where sched.loan_id = p_loan_id
2599 and sched.fee_schedule_id = p_fee_schedule_id
2600 and sched.fee_waivable_flag = 'Y'
2601 and sched.active_flag = 'Y'
2602 and sched.billed_flag = 'N';
2603
2604 begin
2605
2606 l_api_name := 'waiveFee';
2607 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2608
2609 -- Standard Start of API savepoint
2610 SAVEPOINT waiveFee;
2611
2612 -- Initialize message list IF p_init_msg_list is set to TRUE.
2613 IF FND_API.to_Boolean(p_init_msg_list) THEN
2614 FND_MSG_PUB.initialize;
2615 END IF;
2616
2617 -- Initialize API return status to SUCCESS
2618 x_return_status := FND_API.G_RET_STS_SUCCESS;
2619
2620 -- ---------------------------------------------------------------------
2621 -- Api body
2622 -- ---------------------------------------------------------------------
2623 lns_utility_pub.validate_any_id(p_api_version => 1.0
2624 ,p_init_msg_list => 'T'
2625 ,x_msg_count => l_msg_count
2626 ,x_msg_data => l_msg_data
2627 ,x_return_status => l_return_status
2628 ,p_col_id => p_loan_id
2629 ,p_col_name => 'LOAN_ID'
2630 ,p_table_name => 'LNS_LOAN_HEADERS_ALL');
2631
2632 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2633 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2634 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
2635 FND_MESSAGE.SET_TOKEN('VALUE', p_loan_ID);
2636 FND_MSG_PUB.ADD;
2637 RAISE FND_API.G_EXC_ERROR;
2638 end if;
2639
2640 lns_utility_pub.validate_any_id(p_api_version => 1.0
2641 ,p_init_msg_list => 'T'
2642 ,x_msg_count => l_msg_count
2643 ,x_msg_data => l_msg_data
2644 ,x_return_status => l_return_status
2645 ,p_col_id => p_fee_schedule_id
2646 ,p_col_name => 'FEE_SCHEDULE_ID'
2647 ,p_table_name => 'LNS_FEE_SCHEDULES');
2648
2649 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2650 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2651 FND_MESSAGE.SET_TOKEN('PARAMETER', 'FEE_SCHEDULE_ID');
2652 FND_MESSAGE.SET_TOKEN('VALUE', p_fee_schedule_ID);
2653 FND_MSG_PUB.ADD;
2654 RAISE FND_API.G_EXC_ERROR;
2655 end if;
2656
2657 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - amount to waive ' || p_waive_amount);
2658
2659 if p_waive_amount is null then
2660 FND_MESSAGE.SET_NAME('LNS', 'LNS_WAIVE_AMOUNT_INVALID');
2661 FND_MSG_PUB.ADD;
2662 RAISE FND_API.G_EXC_ERROR;
2663 else
2664 open c_fee_waive_amount(p_fee_schedule_id);
2665 fetch c_fee_waive_amount into l_fee_amount_remaining, l_waived_amount;
2666 close c_fee_waive_amount;
2667
2668 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - amount remain ' || l_fee_amount_remaining);
2669 if p_waive_amount > l_fee_amount_remaining or p_waive_amount < 0 then
2670 FND_MESSAGE.SET_NAME('LNS', 'LNS_WAIVE_AMOUNT_INVALID');
2671 FND_MSG_PUB.ADD;
2672 RAISE FND_API.G_EXC_ERROR;
2673 end if;
2674
2675 -- fee is valid and waive amount is valid. update the fee schedule
2676 -- check on updating object_version_number
2677 lns_fee_schedules_pkg.update_row(P_FEE_SCHEDULE_ID => p_fee_schedule_id
2678 ,P_FEE_ID => null
2679 ,P_LOAN_ID => p_loan_id
2680 ,P_FEE_AMOUNT => null
2681 ,P_FEE_INSTALLMENT => null
2682 ,P_FEE_DESCRIPTION => null
2683 ,P_ACTIVE_FLAG => null
2684 ,P_BILLED_FLAG => null
2685 ,P_FEE_WAIVABLE_FLAG => null
2686 ,P_WAIVED_AMOUNT => p_waive_amount + l_waived_amount
2687 ,P_LAST_UPDATED_BY => lns_utility_pub.last_updated_by
2688 ,P_LAST_UPDATE_DATE => lns_utility_pub.last_update_date
2689 ,P_LAST_UPDATE_LOGIN => lns_utility_pub.last_update_login
2690 ,P_PROGRAM_ID => null
2691 ,P_REQUEST_ID => null
2692 ,P_OBJECT_VERSION_NUMBER => null);
2693
2694
2695 end if;
2696 -- ---------------------------------------------------------------------
2697 -- End of API body
2698 -- ---------------------------------------------------------------------
2699
2700 IF FND_API.to_Boolean(p_commit) THEN
2701 COMMIT WORK;
2702 END IF;
2703
2704 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2705
2706 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
2707
2708 EXCEPTION
2709 WHEN FND_API.G_EXC_ERROR THEN
2710 x_return_status := FND_API.G_RET_STS_ERROR;
2711 x_msg_count := l_msg_count;
2712 x_msg_data := l_msg_data;
2713 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2714 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2715 ROLLBACK TO waiveFee;
2716
2717 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2718 x_return_status := FND_API.G_RET_STS_ERROR;
2719 x_msg_count := l_msg_count;
2720 x_msg_data := l_msg_data;
2721 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2722 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2723 ROLLBACK TO waiveFee;
2724
2725 WHEN OTHERS THEN
2726 x_return_status := FND_API.G_RET_STS_ERROR;
2727 x_msg_count := l_msg_count;
2728 x_msg_data := l_msg_data;
2729 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2730 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2731 ROLLBACK TO waiveFee;
2732
2733 end waiveFee;
2734
2735 /*=========================================================================
2736 ||
2737 || PUBLIC PROCEDURE getFeesTotal
2738 ||
2739 || DESCRIPTION
2740 || Overview: this procedure will get the sum of fees for a given loan
2741 || for a given fee category/type , billed flag and waived flag
2742 || PSEUDO CODE/LOGIC
2743 ||
2744 || PARAMETERS
2745 || Parameter: p_loan_id => loan_id
2746 ||
2747 || Return value:
2748 || sum of fees for a loan
2749 || KNOWN ISSUES
2750 ||
2751 || NOTES
2752 ||
2753 || MODIFICATION HISTORY
2754 || Date Author Description of Changes
2755 || 12/16/2004 8:40PM raverma Created
2756 ||
2757 *=======================================================================*/
2758 function getFeesTotal(p_loan_id in number
2759 ,p_fee_category in varchar2
2760 ,p_fee_type in varchar2
2761 ,p_billed_flag in varchar2
2762 ,p_waived_flag in varchar2) return number
2763 is
2764
2765 l_api_name varchar2(25);
2766 l_return_status VARCHAR2(1);
2767 l_msg_count NUMBER;
2768 l_msg_data VARCHAR2(32767);
2769 vPLSQL varchar2(1000);
2770 Type refCur is ref cursor;
2771 sql_Cur refCur;
2772
2773 l_total number;
2774
2775 begin
2776 l_api_name := 'getFeesTotal';
2777
2778 vPLSQL := 'SELECT decode(:p_waived_flag, ''Y'', nvl(sum(sched.waived_amount),0), nvl(sum(sched.fee_amount) - sum(sched.waived_amount),0))' ||
2779 ' from lns_fee_schedules sched ' ||
2780 --' ,lns_fee_assignments assign ' ||
2781 --' where assgn.fee_id = sched.fee_id ' ||
2782 ' Where sched.loan_id = :p_loan_id ' ||
2783 ' and sched.billed_flag = :p_billed_flag ' ||
2784 ' and sched.active_flag = ''Y'' ';
2785
2786 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee_category ' || p_fee_category);
2787 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee_tpye ' || p_fee_type);
2788 if p_fee_category is not null then
2789 vPLSQL := vPLSQL || ' AND fees.fee_category = ''' || p_fee_category || '''';
2790 end if;
2791
2792 if p_fee_type is not null then
2793 vPLSQL := vPLSQL || ' AND fees.fee_type = ''' || p_fee_type || '''';
2794 end if;
2795
2796 /*
2797 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - include p_memo_fees ' || p_memo_fees);
2798 if p_memo_fees = 'Y' then
2799 vPLSQL := vPLSQL || ' AND fees.fee_category = ''MEMO''';
2800 else
2801 vPLSQL := vPLSQL || ' AND fees.fee_category <> ''MEMO''';
2802 end if;
2803 */
2804 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - sql ' || vPLSQL);
2805 open sql_cur for
2806 vPLSQL
2807 using p_waived_flag, p_loan_id, p_billed_flag;
2808 fetch sql_cur into l_total;
2809 close sql_cur;
2810
2811 return l_total;
2812
2813 end getFeesTotal;
2814
2815 /*=========================================================================
2816 || PUBLIC PROCEDURE processLateFees
2817 ||
2818 || DESCRIPTION
2819 || Overview: this procedure will determine late fees, calculate them and
2820 || write them to the lns_fee_schedules
2821 || PSEUDO CODE/LOGIC
2822 ||
2823 || PARAMETERS
2824 || Parameter: p_loan_id => loan_id
2825 ||
2826 || Return value:
2827 || standard
2828 || KNOWN ISSUES
2829 ||
2830 || NOTES
2831 ||
2832 || MODIFICATION HISTORY
2833 || Date Author Description of Changes
2834 || 1/31/2005 8:40PM raverma Created
2835 ||
2836 *=======================================================================*/
2837 procedure processLateFees(p_loan_id in number
2838 ,p_init_msg_list in varchar2
2839 ,p_commit in varchar2
2840 ,x_return_status out nocopy varchar2
2841 ,x_msg_count out nocopy number
2842 ,x_msg_data out nocopy varchar2)
2843 is
2844
2845 l_api_name varchar2(25);
2846 l_return_status VARCHAR2(1);
2847 l_msg_count NUMBER;
2848 l_msg_data VARCHAR2(32767);
2849
2850 l_late_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
2851 l_late_fee_structure LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
2852 l_fee_basis_tbl LNS_FEE_ENGINE.FEE_BASIS_TBL;
2853 l_fee_calc_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
2854 l_fee_calc_tbl_full LNS_FEE_ENGINE.FEE_CALC_TBL;
2855 i number;
2856 l_last_installment number;
2857 l_amount_overdue number;
2858
2859 vPLSQL varchar2(4000);
2860 Type refCur is ref cursor;
2861 sql_Cur refCur;
2862
2863 begin
2864
2865 l_api_name := 'processLateFees';
2866 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2867
2868 -- Standard Start of API savepoint
2869 SAVEPOINT processLateFees;
2870
2871 -- Initialize message list IF p_init_msg_list is set to TRUE.
2872 IF FND_API.to_Boolean(p_init_msg_list) THEN
2873 FND_MSG_PUB.initialize;
2874 END IF;
2875
2876 -- Initialize API return status to SUCCESS
2877 x_return_status := FND_API.G_RET_STS_SUCCESS;
2878
2879 -- ---------------------------------------------------------------------
2880 -- Api body
2881 -- ---------------------------------------------------------------------
2882 -- initialize any variables here
2883 --
2884 -- 1. getLastInstallment
2885 --1. getFeeStructures for a particular event(s) / installment
2886 -- 2. getInvoices for lastinstallment
2887 --2. calculate for installment
2888 --3. write to fee schedule
2889 i := 0;
2890 select LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER_EXT(p_loan_id)
2891 into l_last_installment
2892 from dual;
2893
2894 if l_last_installment > 0 then
2895
2896 -- first part is for TERM PHASE
2897 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - last installment ' || l_last_installment);
2898 FND_FILE.PUT_LINE(FND_FILE.LOG, 'processing last installment ' || l_last_installment);
2899 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - loan_id ' || p_loan_id);
2900 l_late_fee_structures := lns_fee_engine.getFeeStructures(p_loan_id => p_loan_id
2901 ,p_fee_category => 'EVENT'
2902 ,p_fee_type => 'EVENT_LATE_CHARGE'
2903 ,p_installment => l_last_installment
2904 ,p_fee_id => null);
2905 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - after api');
2906
2907 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - late fee structures ' || l_late_fee_structures.count);
2908 FND_FILE.PUT_LINE(FND_FILE.LOG, 'fee structures ' || l_late_fee_structures.count);
2909 for x in 1..l_late_fee_structures.count loop
2910
2911 -- get the amount overdue for the last installment (P or P+I depends on fee structure)
2912 vPlSql := 'select ' ||
2913 ' nvl(sum(amount_due_remaining),0) ' ||
2914 ' from lns_amortization_scheds am ' ||
2915 ' ,ar_payment_schedules ps ' ||
2916 ' ,lns_fee_assignments fass ' ||
2917 ' ,lns_fees fees ' ||
2918 ' where am.loan_id = :p_loan_id and ' ||
2919 ' am.payment_number = :p_installment and ' ||
2920 ' fees.fee_id = :p_fee_id and ' ||
2921 ' ps.amount_due_remaining > 0 and ' ||
2922 ' am.loan_id = fass.loan_id and ' ||
2923 ' fees.fee_id = fass.fee_id and ' ||
2924 ' am.reamortization_amount is null and ' ||
2925 ' am.reversed_flag <> ''Y'' and ' ||
2926 ' am.due_date + nvl(fees.number_grace_days, 0) < trunc(sysdate) ' ||
2927 ' and (not exists ' ||
2928 ' (select ''X'' ' ||
2929 ' from lns_fee_schedules sched ' ||
2930 ' where sched.loan_id = am.loan_id ' ||
2931 ' and fee_id = fees.fee_id ' ||
2932 ' and fee_installment = am.payment_number + 1' ||
2933 ' and billed_flag = ''N'' ' ||
2934 ' and active_flag = ''Y'' )) and ' ||
2935 ' (am.principal_trx_id = ps.customer_trx_id ';
2936
2937 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee_basis rule ' || l_late_fee_structures(x).fee_basis_rule);
2938 if l_late_fee_structures(x).fee_basis_rule = 'OVERDUE_PRIN' then
2939 vPlSql := vPlSql || ')';
2940 elsif l_late_fee_structures(x).fee_basis_rule = 'OVERDUE_PRIN_INT' then
2941 vPlSql := vPlSql || 'OR am.interest_trx_id = ps.customer_trx_id)';
2942 end if;
2943 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - last installment ' || l_last_installment);
2944
2945 l_amount_overdue := 0;
2946 open sql_cur for
2947 vPLSQL
2948 using p_loan_id, l_last_installment, l_late_fee_structures(x).fee_id;
2949 fetch sql_cur into
2950 l_amount_overdue;
2951 close sql_cur;
2952
2953 if l_amount_overdue > l_late_fee_structures(x).minimum_overdue_amount and l_amount_overdue > 0 then
2954 -- we have a late fee
2955 i := i + 1;
2956
2957 l_fee_basis_tbl(1).fee_basis_name := 'OVERDUE_PRIN';
2958 l_fee_basis_tbl(1).fee_basis_amount := l_amount_overdue;
2959 l_fee_basis_tbl(2).fee_basis_name := 'OVERDUE_PRIN_INT';
2960 l_fee_basis_tbl(2).fee_basis_amount := l_amount_overdue;
2961
2962 l_late_fee_structure(1) := l_late_fee_structures(x);
2963
2964 lns_fee_engine.calculateFees(p_loan_id => p_loan_id
2965 ,p_fee_basis_tbl => l_fee_basis_tbl
2966 ,p_installment => l_last_installment + 1
2967 ,p_fee_structures => l_late_fee_structure
2968 ,x_fees_tbl => l_fee_calc_tbl
2969 ,x_return_status => l_return_status
2970 ,x_msg_count => l_msg_count
2971 ,x_msg_data => l_msg_data);
2972 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2973 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_CALCULATION_FAILURE');
2974 FND_MSG_PUB.ADD;
2975 RAISE FND_API.G_EXC_ERROR;
2976 end if;
2977
2978 -- assign this so we can write the fee
2979 l_fee_calc_tbl_full(i) := l_fee_calc_tbl(1);
2980
2981 end if;
2982
2983 end loop;
2984
2985 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - calculated fees count is ' || l_fee_calc_tbl_full.count);
2986 FND_FILE.PUT_LINE(FND_FILE.LOG, 'calculated fees count is ' || l_fee_calc_tbl_full.count);
2987 lns_fee_engine.writeFeeSchedule(p_init_msg_list => p_init_msg_list
2988 ,p_commit => p_commit
2989 ,p_loan_id => p_loan_id
2990 ,p_fees_tbl => l_fee_calc_tbl_full
2991 ,x_return_status => l_return_status
2992 ,x_msg_count => l_msg_count
2993 ,x_msg_data => l_msg_data);
2994 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2995 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_WRITE_FAILURE');
2996 FND_MSG_PUB.ADD;
2997 RAISE FND_API.G_EXC_ERROR;
2998 end if;
2999
3000 end if; -- l_last_installment > 1
3001 -- ---------------------------------------------------------------------
3002 -- End of API body
3003 -- ---------------------------------------------------------------------
3004
3005 IF FND_API.to_Boolean(p_commit) THEN
3006 COMMIT WORK;
3007 END IF;
3008
3009 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3010
3011 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
3012
3013 EXCEPTION
3014 WHEN FND_API.G_EXC_ERROR THEN
3015 x_return_status := FND_API.G_RET_STS_ERROR;
3016 x_msg_count := l_msg_count;
3017 x_msg_data := l_msg_data;
3018 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3019 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
3020 ROLLBACK TO processLateFees;
3021
3022 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3023 x_return_status := FND_API.G_RET_STS_ERROR;
3024 x_msg_count := l_msg_count;
3025 x_msg_data := l_msg_data;
3026 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3027 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
3028 ROLLBACK TO processLateFees;
3029
3030 WHEN OTHERS THEN
3031 x_return_status := FND_API.G_RET_STS_ERROR;
3032 x_msg_count := l_msg_count;
3033 x_msg_data := l_msg_data;
3034 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3035 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
3036 ROLLBACK TO processLateFees;
3037
3038
3039 end processLateFees;
3040
3041
3042 PROCEDURE LOAN_LATE_FEES_CONCUR(ERRBUF OUT NOCOPY VARCHAR2
3043 ,RETCODE OUT NOCOPY VARCHAR2
3044 ,P_BORROWER_ID IN NUMBER
3045 ,P_LOAN_ID IN NUMBER)
3046
3047 is
3048 l_msg_count number;
3049 l_msg_data varchar2(500);
3050 l_return_Status varchar2(1);
3051 my_message varchar2(2000);
3052 l_processed_fees number;
3053 l_fee_records1 number;
3054 l_fee_records2 number;
3055 l_loan_id number;
3056
3057 cursor c_borrower_loans(p_borrower_id number) is
3058 select loan_id
3059 from lns_loan_headers
3060 where loan_status in ('ACTIVE', 'DELINQUENT', 'DEFAULT')
3061 and primary_borrower_id = p_borrower_id;
3062
3063 cursor c_all_active_loans is
3064 select loan_id
3065 from lns_loan_headers
3066 where loan_status in ('ACTIVE', 'DELINQUENT', 'DEFAULT');
3067
3068 BEGIN
3069
3070
3071 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Beginning Loans Late Fee Assessment');
3072 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_LOAN_ID ' || p_loan_id);
3073 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_BORROWER_ID ' || p_borrower_id);
3074
3075 select count(1) into l_fee_records1
3076 from lns_fee_schedules
3077 where active_flag = 'Y'
3078 and billed_flag = 'N';
3079
3080 if p_loan_id is not null then
3081 FND_FILE.PUT_LINE(FND_FILE.LOG, 'processing single loan ' || p_loan_id);
3082 lns_fee_engine.processLateFees(p_loan_id => p_loan_id
3083 ,p_init_msg_list => FND_API.G_TRUE
3084 ,p_commit => FND_API.G_TRUE
3085 ,x_return_status => l_return_status
3086 ,x_msg_count => l_msg_count
3087 ,x_msg_data => l_msg_data);
3088
3089 elsif p_borrower_id is not null then
3090
3091 open c_borrower_loans(p_borrower_id);
3092 loop
3093 fetch c_borrower_loans
3094 into l_loan_id;
3095
3096 exit when c_borrower_loans%notfound;
3097
3098 FND_FILE.PUT_LINE(FND_FILE.LOG, 'processing loan ' || l_loan_id);
3099 lns_fee_engine.processLateFees(p_loan_id => l_loan_id
3100 ,p_init_msg_list => FND_API.G_TRUE
3101 ,p_commit => FND_API.G_TRUE
3102 ,x_return_status => l_return_status
3103 ,x_msg_count => l_msg_count
3104 ,x_msg_data => l_msg_data);
3105 FND_FILE.PUT_LINE(FND_FILE.LOG, 'FAP status ' || l_return_status);
3106
3107 end loop;
3108
3109 else
3110 open c_all_active_loans;
3111 loop
3112 fetch c_all_active_loans
3113 into l_loan_id;
3114
3115 exit when c_all_active_loans%notfound;
3116
3117 FND_FILE.PUT_LINE(FND_FILE.LOG, 'processing loan ' || l_loan_id);
3118 lns_fee_engine.processLateFees(p_loan_id => l_loan_id
3119 ,p_init_msg_list => FND_API.G_TRUE
3120 ,p_commit => FND_API.G_TRUE
3121 ,x_return_status => l_return_status
3122 ,x_msg_count => l_msg_count
3123 ,x_msg_data => l_msg_data);
3124 FND_FILE.PUT_LINE(FND_FILE.LOG, 'FAP status ' || l_return_status);
3125
3126 end loop;
3127
3128 end if;
3129
3130 select count(1) into l_fee_records2
3131 from lns_fee_schedules
3132 where active_flag = 'Y'
3133 and billed_flag = 'N';
3134
3135 l_processed_fees := l_fee_records2 - l_fee_records1;
3136 FND_FILE.PUT_LINE(FND_FILE.LOG, 'added ' || l_processed_fees || ' into fee schedule');
3137
3138 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3139 RAISE FND_API.G_EXC_ERROR;
3140 else
3141 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Late Fee Assessment: PROCESS COMPLETED SUCCESFULLY.');
3142 end if;
3143
3144 EXCEPTION
3145 -- note do not set retcode when error is expected
3146 WHEN FND_API.G_EXC_ERROR THEN
3147 RETCODE := -1;
3148 ERRBUF := l_msg_data;
3149 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN FAP: ' || sqlerrm || ERRBUF);
3150 if l_msg_count > 0 then
3151 FOR l_index IN 1..l_msg_count LOOP
3152 my_message := FND_MSG_PUB.Get(p_msg_index => l_index, p_encoded => 'F');
3153 FND_FILE.PUT_LINE(FND_FILE.LOG, my_message);
3154 END LOOP;
3155 end if;
3156
3157 WHEN OTHERS THEN
3158 RETCODE := -1;
3159 ERRBUF := l_msg_data;
3160 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN FAP: ' || sqlerrm || ERRBUF);
3161 if l_msg_count > 0 then
3162 FOR l_index IN 1..l_msg_count LOOP
3163 my_message := FND_MSG_PUB.Get(p_msg_index => l_index, p_encoded => 'F');
3164 FND_FILE.PUT_LINE(FND_FILE.LOG, my_message);
3165 END LOOP;
3166 end if;
3167
3168 END LOAN_LATE_FEES_CONCUR;
3169
3170
3171
3172 END LNS_FEE_ENGINE;