[Home] [Help]
PACKAGE BODY: APPS.LNS_FEE_ENGINE
Source
1 PACKAGE BODY LNS_FEE_ENGINE AS
2 /* $Header: LNS_FEE_ENGINE_B.pls 120.29 2011/09/14 13:55:58 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 IF log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
27 FND_LOG.STRING(log_level, module, message);
28 END IF;
29
30 EXCEPTION
31 WHEN OTHERS THEN
32 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'ERROR: ' || sqlerrm);
33 end;
34
35 /*
36 function loanHasAssignment(p_loan_id IN NUMBER
37 ,p_assignment_type IN VARCHAR2) return boolean
38 is
39 vSql varchar2(250);
40 TYPE refCur IS REF CURSOR;
41 c_hasAssign refCur;
42 l_return boolean;
43 l_tmp varchar2(1);
44
45 begin
46
47 vSql := 'Select ''X'' ' ||
48 ' From lns_assignments ' ||
49 'Where exists ' ||
50 ' (Select assignment_id ' ||
51 ' From lns_assignments ' ||
52 ' Where loan_id = :a1 ' ||
53 ' and assignment_type = :b1)';
54 -- dbms_output.put_line('plsql is ' || vSql);
55 open c_hasAssign for
56 vSql
57 using p_loan_id
58 ,p_assignment_type;
59 FETCH c_hasAssign INTO l_tmp;
60
61 if c_hasAssign%FOUND then
62 l_return := true;
63 else
64 l_return := false;
65 end if;
66 CLOSE c_hasAssign;
67
68 return l_return;
69
70 end loanHasAssignment;
71 */
72
73 /*=========================================================================
74 || PUBLIC PROCEDURE reprocessFees LNS.B
75 ||
76 || DESCRIPTION
77 || Overview: processes fees for CONVERSION EVENT or DISBURSEMENT
78 ||
79 || PSEUDO CODE/LOGIC
80 || 1. check if fees needs to be billed
81 || 2. calculate all fees
82 || 3. writeFees to the fee schedule
83 || 4. bill fees as manual billing
84 ||
85 || PARAMETERS
86 || Parameter: p_loan_id => loan_id
87 || p_Phase => 'OPEN' then p_disb_head_id must not be null
88 || 'TERM' then p_disb_head_id must be null
89 || p_disb_head_id
90 ||
91 || Return value:
92 || standard
93 || KNOWN ISSUES
94 ||
95 || NOTES
96 ||
97 || MODIFICATION HISTORY
98 || Date Author Description of Changes
99 || 07/28/2005 raverma created
100 *=======================================================================*/
101 procedure processDisbursementFees(p_init_msg_list in varchar2
102 ,p_commit in varchar2
103 ,p_phase in varchar2
104 ,p_loan_id in number
105 ,p_disb_head_id in number
106 ,x_return_status out nocopy varchar2
107 ,x_msg_count out nocopy number
108 ,x_msg_data out nocopy varchar2)
109 is
110 l_api_name varchar2(25);
111 l_write_fee_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
112 l_fee_id number;
113 l_fee_basis varchar2(25);
114 l_fee_amount number;
115 l_fee_description varchar2(60);
116 l_last_payment_number number;
117 l_return_status VARCHAR2(1);
118 l_msg_count NUMBER;
119 l_msg_data VARCHAR2(32767);
120 l_BILL_HEADERS_TBL LNS_BILLING_BATCH_PUB.BILL_HEADERS_TBL;
121 l_BILL_LINES_TBL LNS_BILLING_BATCH_PUB.BILL_LINES_TBL;
122 l_fees_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
123 i number;
124 l_fee_installment number;
125 l_phase varchar2(30);
126
127 -- get disbursement fees
128 cursor c_DisbursementFees(p_disb_head_id number) is
129 select ass.fee_id
130 --,decode(ass.rate_type, 'FIXED', nvl(ass.fee,fee.fee), 'VARIABLE', nvl(ass.fee,fee.fee)/100 * head.header_amount)
131 ,decode(ass.rate_type, 'FIXED', nvl(ass.fee,fee.fee), 'VARIABLE', lns_fee_engine.calculateFee(ass.fee_id, head.disb_header_id, head.LOAN_ID))
132 ,fee.fee_description
133 ,ass.fee_basis
134 ,nvl(ass.begin_installment_number, 0) -- fix for bug 8928398
135 ,nvl(ass.phase, 'TERM')
136 from lns_fee_assignments ass
137 ,lns_disb_headers head
138 ,lns_fees_all fee
139 where ass.loan_id is null
140 and ass.disb_header_id = head.disb_header_id
141 and fee.fee_id = ass.fee_id
142 and ass.disb_header_id = p_disb_head_id;
143
144 cursor c_ConversionFees(c_loan_id number) is
145 select ass.fee_id
146 ,decode(ass.rate_type, 'FIXED', nvl(ass.fee,fee.fee), 'VARIABLE', nvl(ass.fee,fee.fee)/100)
147 ,fee.fee_description
148 ,ass.fee_basis
149 from lns_fee_assignments ass
150 ,lns_fees_all fee
151 where ass.fee_type = 'EVENT_CONVERSION'
152 and ass.fee_id = fee.fee_id
153 and loan_id = c_loan_id;
154
155 -- fee basis for TOTAL_DISB_AMT
156 cursor c_totalDisbursed(p_loan_id number) is
157 select sum(l.line_amount)
158 from lns_disb_lines l
159 ,lns_disb_headers h
160 where h.disb_header_id = l.disb_header_id
161 and l.status = 'FULLY_FUNDED'
162 and h.loan_id = p_loan_id;
163 --
164 cursor c_origLoanAmt(p_loan_id number) is
165 select requested_amount
166 from lns_loan_headers_all
167 where loan_id = p_loan_id;
168
169 cursor c_lastPaymentNumber(p_loan_id number) is
170 select nvl(last_payment_number, 0)
171 from lns_loan_headers_all
172 where loan_id = p_loan_id;
173
174
175 begin
176 l_api_name := 'processDisbursementFees';
177 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
178 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - loan_id ' || p_loan_id);
179 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_phase' || p_phase);
180 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_disb_head_id' || p_disb_head_id);
181
182 -- Standard Start of API savepoint
183 SAVEPOINT processDisbursementFees;
184
185 -- Initialize message list IF p_init_msg_list is set to TRUE.
186 IF FND_API.to_Boolean(p_init_msg_list) THEN
187 FND_MSG_PUB.initialize;
188 END IF;
189
190 -- Initialize API return status to SUCCESS
191 x_return_status := FND_API.G_RET_STS_SUCCESS;
192
193 -- ---------------------------------------------------------------------
194 -- Api body
195 -- ---------------------------------------------------------------------
196 i := 0;
197 -- 1. check if fees needs to be billed
198 -- if p_phase = 'OPEN' then
199 OPEN c_DisbursementFees(p_disb_head_id);
200 LOOP
201 i := i + 1;
202 FETCH c_DisbursementFees INTO
203 l_fee_id
204 ,l_fee_amount
205 ,l_fee_description
206 ,l_fee_basis
207 ,l_fee_installment -- fix for bug 8928398
208 ,l_phase;
209 EXIT WHEN c_DisbursementFees%NOTFOUND;
210
211 -- 2. calculate all fees
212 if l_fee_basis = 'TOTAL_DISB_AMT' then
213 open c_totalDisbursed(p_loan_id);
214 fetch c_totalDisbursed into l_fee_amount;
215 close c_totalDisbursed;
216 end if;
217
218 l_write_fee_tbl(i).fee_id := l_fee_id;
219 l_write_fee_tbl(i).fee_amount := l_fee_amount;
220 l_write_fee_tbl(i).fee_installment := l_fee_installment; -- fix for bug 8928398
221 l_write_fee_tbl(i).fee_description := l_fee_description;
222 l_write_fee_tbl(i).disb_header_id := p_disb_head_id;
223 l_write_fee_tbl(i).phase := l_phase;
224
225 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee #: ' || i);
226 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee id: ' || l_fee_id);
227 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee amount: ' || l_fee_amount);
228 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_fee_description: ' || l_fee_description);
229 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_fee_basis ' || l_fee_basis);
230 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_fee_installment ' || l_fee_installment);
231 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_phase ' || l_phase);
232
233 END LOOP;
234
235
236 /* Now, Conversion fees inserted into feeSchedules table when this fee is assigned
237 elsif p_phase = 'TERM' then
238
239
240 OPEN c_ConversionFees(p_loan_id);
241 LOOP
242 i := i + 1;
243 FETCH c_ConversionFees INTO
244 l_fee_id
245 ,l_fee_amount
246 ,l_fee_description
247 ,l_fee_basis;
248 EXIT WHEN c_ConversionFees%NOTFOUND;
249
250 if l_fee_basis = 'TOTAL_DISB_AMT' then
251 open c_totalDisbursed(p_loan_id);
252 fetch c_totalDisbursed into l_fee_amount;
253 close c_totalDisbursed;
254 elsif l_fee_basis = 'ORIG_LOAN' then
255 open c_origLoanAmt(p_loan_id);
256 fetch c_origLoanAmt into l_fee_amount;
257 close c_origLoanAmt;
258 end if;
259
260 l_write_fee_tbl(i).fee_id := l_fee_id;
261 l_write_fee_tbl(i).fee_amount := l_fee_amount;
262 l_write_fee_tbl(i).fee_installment := 0;
263 l_write_fee_tbl(i).fee_description := l_fee_description;
264 l_write_fee_tbl(i).disb_header_id := p_disb_head_id;
265 l_write_fee_tbl(i).phase := p_phase;
266
267 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee #: ' || i);
268 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee id: ' || l_fee_id);
269 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee amount: ' || l_fee_amount);
270 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_fee_description: ' || l_fee_description);
271 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_fee_basis ' || l_fee_basis);
272
273 l_fee_amount := null;
274 l_fee_id := null;
275 l_fee_basis := null;
276 l_fee_description := null;
277 END LOOP;
278 CLOSE c_ConversionFees;
279 */
280 -- end if; -- if p_phase = 'OPEN'
281
282 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - i ' || i);
283 if l_write_fee_tbl.count > 0 then
284 -- 3. writeFees to the fee schedule
285 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - writing fees schedule');
286 lns_fee_engine.writeFeeSchedule(p_init_msg_list => p_init_msg_list
287 ,p_commit => p_commit
288 ,p_loan_id => p_loan_id
289 ,p_fees_tbl => l_write_fee_tbl
290 ,x_return_status => l_return_status
291 ,x_msg_count => l_msg_count
292 ,x_msg_data => l_msg_data);
293 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - writing fees schedule status ' || l_return_status);
294
295 end if; -- l_write_fee_tb.count > 0
296
297
298 -- ---------------------------------------------------------------------
299 -- End of API body
300 -- ---------------------------------------------------------------------
301 IF FND_API.to_Boolean(p_commit) THEN
302 COMMIT WORK;
303 END IF;
304 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
305
306 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
307
308 EXCEPTION
309 WHEN FND_API.G_EXC_ERROR THEN
310 x_return_status := FND_API.G_RET_STS_ERROR;
311 x_msg_count := l_msg_count;
312 x_msg_data := l_msg_data;
313 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
314 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
315 ROLLBACK TO processDisbursementFees;
316
317 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
318 x_return_status := FND_API.G_RET_STS_ERROR;
319 x_msg_count := l_msg_count;
320 x_msg_data := l_msg_data;
321 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
322 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
323 ROLLBACK TO processDisbursementFees;
324
325 WHEN OTHERS THEN
326 x_return_status := FND_API.G_RET_STS_ERROR;
327 x_msg_count := l_msg_count;
328 x_msg_data := l_msg_data;
329 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
330 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
331 ROLLBACK TO processDisbursementFees;
332
333 end processDisbursementFees;
334
335 /*=========================================================================
336 || PUBLIC PROCEDURE reprocessFees LNS.B
337 ||
338 || DESCRIPTION
339 || Overview: reprocesses fees when a loan is rebilled
340 || will recalculate and write late fees and manual fees
341 || will write these new fees to fee schedule
342 || NOTE: recurring fees are processed during billing API call
343 ||
344 || PSEUDO CODE/LOGIC
345 ||
346 || PARAMETERS
347 || Parameter: p_loan_id => loan_id
348 || p_installment => installment number for the loan
349 || p_phase => phase of the loan
350 ||
351 || Return value:
352 || standard
353 || KNOWN ISSUES
354 ||
355 || NOTES
356 ||
357 || MODIFICATION HISTORY
358 || Date Author Description of Changes
359 || 3/15/2005 raverma Created
360 ||
361 *=======================================================================*/
362 procedure reprocessFees(p_init_msg_list in varchar2
363 ,p_commit in varchar2
364 ,p_loan_id in number
365 ,p_installment_number in number
366 ,p_phase in varchar2
367 ,x_return_status out nocopy varchar2
368 ,x_msg_count out nocopy number
369 ,x_msg_data out nocopy varchar2)
370 is
371
372 l_api_name varchar2(15);
373 l_write_fee_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
374 l_fee_id number;
375 l_fee_amount number;
376 l_fee_description varchar2(250);
377 l_return_status VARCHAR2(1);
378 l_msg_count NUMBER;
379 l_msg_data VARCHAR2(32767);
380 i number;
381 l_phase VARCHAR2(30);
382
383 -- get the manual fee_ids that were added and billed for the installment
384 -- also include origination fees
385 -- need to get the max (amortization_id) in case of multiple credits
386 cursor c_manual_fees (c_loan_id number, c_installment number, c_phase varchar2) is
387 select sched.fee_id
388 ,sched.fee_description
389 ,nvl(sched.phase, 'TERM')
390 from lns_fee_schedules sched
391 ,lns_fees fees
392 ,lns_amortization_lines lines
393 ,lns_amortization_scheds am
394 where fees.fee_id = sched.fee_id
395 and lines.fee_schedule_id = sched.fee_schedule_id
396 and lines.amortization_schedule_id = am.amortization_schedule_id
397 and am.amortization_schedule_id =
398 (select max(am2.amortization_schedule_id)
399 from lns_amortization_scheds am2
400 where am2.reversed_flag = 'Y'
401 and am2.loan_id = c_loan_id )
402 and am.reamortization_amount is null
403 and sched.fee_installment = c_installment
404 and ((fees.fee_category = 'MANUAL')
405 OR (fees.fee_category = 'EVENT' AND fees.fee_type = 'EVENT_ORIGINATION'))
406 and sched.loan_id = am.loan_id
407 and am.loan_id = c_loan_id
408 and sched.active_flag = 'Y'
409 and sched.billed_flag = 'Y'
410 and nvl(sched.phase, 'TERM') = nvl(c_phase, 'TERM');
411
412 begin
413 l_api_name := 'reprocessFees';
414 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
415 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - loan_id ' || p_loan_id);
416 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - installment' || p_installment_number);
417
418 -- Standard Start of API savepoint
419 SAVEPOINT reprocessFees;
420
421 -- Initialize message list IF p_init_msg_list is set to TRUE.
422 IF FND_API.to_Boolean(p_init_msg_list) THEN
423 FND_MSG_PUB.initialize;
424 END IF;
425
426 -- Initialize API return status to SUCCESS
427 x_return_status := FND_API.G_RET_STS_SUCCESS;
428
429 -- ---------------------------------------------------------------------
430 -- Api body
431 -- ---------------------------------------------------------------------
432 -- initialize any variables here
433 i := 0;
434
435 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - about to reprocess late fees');
436
437 l_phase := nvl(p_phase, 'TERM');
438
439 -- processing late fees will rewrite them to fee_schedules
440 lns_fee_engine.processLateFees(p_init_msg_list => p_init_msg_list
441 ,p_commit => p_commit
442 ,p_loan_id => p_loan_id
443 ,p_phase => l_phase
444 ,x_return_status => l_return_status
445 ,x_msg_count => l_msg_count
446 ,x_msg_data => l_msg_data);
447
448 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - reprocess late fees status ' || l_return_status);
449 -- we will rewrite any previously written manual fees onto the fee schedule
450 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - finding manual and origination fees');
451 OPEN c_manual_fees(p_loan_id, p_installment_number, l_phase);
452 LOOP
453 i := i + 1;
454 FETCH c_manual_fees INTO
455 l_fee_id
456 --,l_fee_amount
457 ,l_fee_description
458 ,l_phase;
459 EXIT WHEN c_manual_fees%NOTFOUND;
460
461 l_fee_amount := lns_fee_engine.calculateFee(p_fee_id => l_fee_id
462 ,p_loan_id => p_loan_id
463 ,p_phase => l_phase);
464
465 l_write_fee_tbl(i).fee_id := l_fee_id;
466 l_write_fee_tbl(i).fee_amount := l_fee_amount;
467 l_write_fee_tbl(i).fee_installment := p_installment_number;
468 l_write_fee_tbl(i).fee_description := l_fee_description;
469 l_write_fee_tbl(i).phase := l_phase;
470
471 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee #: ' || i);
472 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee id: ' || l_fee_id);
473 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee amount: ' || l_fee_amount);
474 END LOOP;
475
476 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - writing fees schedule');
477 lns_fee_engine.writeFeeSchedule(p_init_msg_list => p_init_msg_list
478 ,p_commit => p_commit
479 ,p_loan_id => p_loan_id
480 ,p_fees_tbl => l_write_fee_tbl
481 ,x_return_status => l_return_status
482 ,x_msg_count => l_msg_count
483 ,x_msg_data => l_msg_data);
484 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - writing fees schedule status ' || l_return_status);
485
486 -- ---------------------------------------------------------------------
487 -- End of API body
488 -- ---------------------------------------------------------------------
489 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
490
491 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
492
493 EXCEPTION
494 WHEN FND_API.G_EXC_ERROR THEN
495 x_return_status := FND_API.G_RET_STS_ERROR;
496 x_msg_count := l_msg_count;
497 x_msg_data := l_msg_data;
498 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
499 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
500 ROLLBACK TO reprocessFees;
501
502 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
503 x_return_status := FND_API.G_RET_STS_ERROR;
504 x_msg_count := l_msg_count;
505 x_msg_data := l_msg_data;
506 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
507 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
508 ROLLBACK TO reprocessFees;
509
510 WHEN OTHERS THEN
511 x_return_status := FND_API.G_RET_STS_ERROR;
512 x_msg_count := l_msg_count;
513 x_msg_data := l_msg_data;
514 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
515 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
516 ROLLBACK TO reprocessFees;
517
518 end reprocessFees;
519
520 /*=========================================================================
521 || PUBLIC FUNCTION getFeeStructures LNS.B
522 ||
523 || DESCRIPTION
524 || Overview: returns structure of fees for a given loan
525 ||
526 || PSEUDO CODE/LOGIC
527 ||
528 || PARAMETERS
529 || Parameter: p_loan_id => loan_id
530 || p_fee_category => fee category
531 || p_fee_type => fee type
532 || p_installment => installment number for the loan
533 ||
534 || Return value:
535 || table of fee structures needed to calculate fees
536 || KNOWN ISSUES
537 ||
538 || NOTES
539 ||
540 || MODIFICATION HISTORY
541 || Date Author Description of Changes
542 || 1/20/2005 GWBush2 Created
543 ||
544 *=======================================================================*/
545 function getFeeStructures (p_fee_id in number) return LNS_FEE_ENGINE.FEE_STRUCTURE_TBL
546
547 is
548
549 l_fee_id number;
550 l_fee_name varchar2(50);
551 l_fee_type varchar2(30);
552 l_fee_category varchar2(30);
553 l_fee number;
554 l_fee_basis varchar2(30);
555 l_billing_option varchar2(30);
556 l_rate_type varchar2(30);
557 l_number_grace_days number;
558 l_minimum_overdue_amount number;
559 l_begin_installment_number number;
560 l_end_installment_number number;
561 l_fee_editable_flag varchar2(1);
562 l_fee_waivable_flag varchar2(1);
563 i number := 0;
564 l_fee_struct_tbl LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
565 l_api_name varchar2(25);
566
567 cursor c_fees (fee_id number) is
568 SELECT fees.fee_id
569 ,fees.fee_name
570 ,fees.fee_type
571 ,fees.fee_category
572 ,decode(fees.rate_type, 'FIXED', fees.fee, 'VARIABLE', fees.fee/100)
573 ,fees.fee_basis
574 ,fees.billing_option
575 ,fees.rate_type
576 ,fees.number_grace_days
577 ,fees.minimum_overdue_amount
578 ,0
579 ,0
580 ,nvl(fees.fee_editable_flag,'N')
581 ,nvl(fees.fee_waivable_flag,'N')
582 from lns_fees_all fees
583 where fees.fee_id = p_fee_id;
584
585 begin
586 l_api_name := 'getFeeStructures';
587
588 open c_fees(p_fee_id) ;
589 i := i + 1;
590 fetch c_fees into
591 l_fee_id
592 ,l_fee_name
593 ,l_fee_type
594 ,l_fee_category
595 ,l_fee
596 ,l_fee_basis
597 ,l_billing_option
598 ,l_rate_type
599 ,l_number_grace_days
600 ,l_minimum_overdue_amount
601 ,l_begin_installment_number
602 ,l_end_installment_number
603 ,l_fee_editable_flag
604 ,l_fee_waivable_flag;
605
606 l_fee_struct_tbl(i).fee_id := l_fee_id;
607 l_fee_struct_tbl(i).fee_name := l_fee_name;
608 l_fee_struct_tbl(i).fee_type := l_fee_type;
609 l_fee_struct_tbl(i).fee_category := l_fee_category;
610 l_fee_struct_tbl(i).fee_amount := l_fee;
611 l_fee_struct_tbl(i).fee_basis := l_fee_basis;
612 l_fee_struct_tbl(i).fee_billing_option := l_billing_option;
613 l_fee_struct_tbl(i).fee_rate_type := l_rate_type;
614 l_fee_struct_tbl(i).number_grace_days := l_number_grace_days;
615 l_fee_struct_tbl(i).minimum_overdue_amount := l_minimum_overdue_amount;
616 l_fee_struct_tbl(i).fee_from_installment := l_begin_installment_number;
617 l_fee_struct_tbl(i).fee_to_installment := l_end_installment_number;
618 l_fee_struct_tbl(i).fee_editable_flag := l_fee_editable_flag;
619 l_fee_struct_tbl(i).fee_waivable_flag := l_fee_waivable_flag;
620 close c_fees;
621
622 return l_fee_struct_tbl;
623
624 exception
625 when no_data_found then
626 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - no fee structures found');
627 end getFeeStructures;
628
629 /*=========================================================================
630 || PUBLIC FUNCTION getFeeStructures LNS.B
631 ||
632 || DESCRIPTION
633 || Overview: returns structure of fees for a given loan
634 ||
635 || PSEUDO CODE/LOGIC
636 ||
637 || PARAMETERS
638 || Parameter: p_loan_id => loan_id
639 || p_fee_category => fee category
640 || p_fee_type => fee type
641 || p_installment => installment number for the loan
642 ||
643 || Return value:
644 || table of fee structures needed to calculate fees
645 || KNOWN ISSUES
646 ||
647 || NOTES
648 ||
649 || MODIFICATION HISTORY
650 || Date Author Description of Changes
651 || 3/29/2004 8:40PM raverma Created
652 || 15-Sep-2009 MBOLLI Bug#8904071 - Returned fee_description along with other column values
653 *=======================================================================*/
654 function getFeeStructures(p_loan_id in number
655 ,p_fee_category in varchar2
656 ,p_fee_type in varchar2
657 ,p_installment in number
658 ,p_phase in varchar2
659 ,p_fee_id in number
660 ,p_billing_option in varchar2 DEFAULT NULL) return LNS_FEE_ENGINE.FEE_STRUCTURE_TBL
661
662 is
663
664 l_fee_id number;
665 l_fee_name varchar2(50);
666 l_fee_description varchar2(250);
667 l_fee_type varchar2(30);
668 l_fee_category varchar2(30);
669 l_fee number;
670 l_fee_basis varchar2(30);
671 l_billing_option varchar2(30);
672 l_rate_type varchar2(30);
673 l_number_grace_days number;
674 l_minimum_overdue_amount number;
675 l_fee_basis_rule varchar2(30);
676 l_begin_installment_number number;
677 l_end_installment_number number;
678 l_fee_editable_flag varchar2(1);
679 l_fee_waivable_flag varchar2(1);
680 i number := 0;
681 l_fee_struct_tbl LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
682 l_phase varchar2(30);
683 l_CUSTOM_PROCEDURE varchar2(250);
684 l_disb_header_id number;
685
686 --vPLSQL varchar2(2000);
687 --Type refCur is ref cursor;
688 --sql_Cur refCur;
689 l_api_name varchar2(25);
690
691 cursor c_fees(p_loan_id number,
692 p_fee_id number,
693 p_fee_category varchar2,
694 p_fee_type varchar2,
695 p_installment number,
696 c_phase varchar2,
697 p_billing_option varchar2) is
698 SELECT fees.fee_id
699 ,fees.fee_name
700 ,fees.fee_description
701 ,fees.fee_type
702 ,fees.fee_category
703 -- Bug#8915683, Now the FeeAssignment is updated for all fees. So use the assignment feeAmount
704 ,decode(fees.rate_type, 'FIXED', nvl(Assgn.fee,fees.fee), 'VARIABLE', nvl(Assgn.fee,fees.fee)/100)
705 ,fees.fee_basis
706 ,assgn.billing_option
707 ,fees.rate_type
708 ,fees.number_grace_days
709 ,fees.minimum_overdue_amount
710 ,fees.fee_basis_rule
711 ,nvl(assgn.begin_installment_number,0)
712 ,nvl(assgn.end_installment_number,0)
713 ,nvl(fees.fee_editable_flag,'N')
714 ,nvl(fees.fee_waivable_flag,'N')
715 ,assgn.disb_header_id
716 ,nvl(assgn.phase, 'TERM')
717 ,nvl(assgn.CUSTOM_PROCEDURE, fees.CUSTOM_PROCEDURE)
718 from lns_fee_assignments assgn
719 ,lns_fees_all fees
720 where assgn.loan_id = nvl(p_loan_id, assgn.loan_id)
721 and assgn.fee_id = fees.fee_id
722 and nvl(trunc(assgn.end_date_active), trunc(sysdate) + 1) >= trunc(sysdate)
723 and nvl(trunc(assgn.start_date_active), trunc(sysdate) - 1) <= trunc(sysdate)
724 and (((fees.fee_category = nvl(p_fee_category, fees.fee_category) and
725 fees.fee_type = nvl(p_fee_type, fees.fee_type)))
726 OR
727 ((fees.fee_category = nvl(p_fee_category, fees.fee_category) and fees.fee_type is null)))
728 and assgn.begin_installment_number <= nvl(p_installment, assgn.begin_installment_number)
729 and assgn.end_installment_number >= nvl(p_installment, assgn.end_installment_number)
730 and nvl(assgn.phase, 'TERM') = c_phase
731 and fees.fee_id = nvl(p_fee_id, fees.fee_id)
732 and assgn.billing_option = nvl(p_billing_option, assgn.billing_option);
733
734 begin
735 l_api_name := 'getFeeStructures';
736 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_loan_id ' || p_loan_id);
737 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee_category ' || p_fee_category);
738 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee_type ' || p_fee_type);
739 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_installment ' || p_installment);
740 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_phase ' || p_phase);
741 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_fee_id ' || p_fee_id);
742 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_billing_option ' || p_billing_option);
743
744 l_phase := nvl(p_phase, 'TERM');
745
746 open c_fees(p_loan_id, p_fee_id, p_fee_category, p_fee_type, p_installment, l_phase, p_billing_option);
747 LOOP
748 i := i + 1;
749 fetch c_fees into
750 l_fee_id
751 ,l_fee_name
752 ,l_fee_description
753 ,l_fee_type
754 ,l_fee_category
755 ,l_fee
756 ,l_fee_basis
757 ,l_billing_option
758 ,l_rate_type
759 ,l_number_grace_days
760 ,l_minimum_overdue_amount
761 ,l_fee_basis_rule
762 ,l_begin_installment_number
763 ,l_end_installment_number
764 ,l_fee_editable_flag
765 ,l_fee_waivable_flag
766 ,l_disb_header_id
767 ,l_phase
768 ,l_CUSTOM_PROCEDURE;
769
770 exit when c_fees%notfound;
771 l_fee_struct_tbl(i).fee_id := l_fee_id;
772 l_fee_struct_tbl(i).fee_name := l_fee_name;
773 l_fee_struct_tbl(i).fee_type := l_fee_type;
774 l_fee_struct_tbl(i).fee_category := l_fee_category;
775 l_fee_struct_tbl(i).fee_description := l_fee_description;
776 l_fee_struct_tbl(i).fee_amount := l_fee;
777 l_fee_struct_tbl(i).fee_basis := l_fee_basis;
778 l_fee_struct_tbl(i).fee_billing_option := l_billing_option;
779 l_fee_struct_tbl(i).fee_rate_type := l_rate_type;
780 l_fee_struct_tbl(i).number_grace_days := l_number_grace_days;
781 l_fee_struct_tbl(i).minimum_overdue_amount := l_minimum_overdue_amount;
782 l_fee_struct_tbl(i).fee_basis_rule := l_fee_basis_rule;
783 l_fee_struct_tbl(i).fee_from_installment := l_begin_installment_number;
784 l_fee_struct_tbl(i).fee_to_installment := l_end_installment_number;
785 l_fee_struct_tbl(i).fee_editable_flag := l_fee_editable_flag;
786 l_fee_struct_tbl(i).fee_waivable_flag := l_fee_waivable_flag;
787 l_fee_struct_tbl(i).disb_header_id := l_disb_header_id;
788 l_fee_struct_tbl(i).phase := l_phase;
789 l_fee_struct_tbl(i).CUSTOM_PROCEDURE := l_CUSTOM_PROCEDURE;
790
791 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, ' ');
792 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - structure #' || i);
793 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_fee_struct_tbl(i).fee_id = ' || l_fee_struct_tbl(i).fee_id);
794 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_fee_struct_tbl(i).fee_name = ' || l_fee_struct_tbl(i).fee_name);
795 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_fee_struct_tbl(i).fee_type = ' || l_fee_struct_tbl(i).fee_type);
796 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_fee_struct_tbl(i).fee_category = ' || l_fee_struct_tbl(i).fee_category);
797 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_fee_struct_tbl(i).fee_description = ' || l_fee_struct_tbl(i).fee_description);
798 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_fee_struct_tbl(i).fee_amount = ' || l_fee_struct_tbl(i).fee_amount);
799 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_fee_struct_tbl(i).fee_basis = ' || l_fee_struct_tbl(i).fee_basis);
800 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_fee_struct_tbl(i).fee_basis_rule = ' || l_fee_struct_tbl(i).fee_basis_rule);
801 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_fee_struct_tbl(i).fee_billing_option = ' || l_fee_struct_tbl(i).fee_billing_option);
802 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_fee_struct_tbl(i).fee_rate_type = ' || l_fee_struct_tbl(i).fee_rate_type);
803 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_fee_struct_tbl(i).number_grace_days = ' || l_fee_struct_tbl(i).number_grace_days);
804 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_fee_struct_tbl(i).minimum_overdue_amount = ' || l_fee_struct_tbl(i).minimum_overdue_amount);
805 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_fee_struct_tbl(i).fee_from_installment = ' || l_fee_struct_tbl(i).fee_from_installment);
806 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_fee_struct_tbl(i).fee_to_installment = ' || l_fee_struct_tbl(i).fee_to_installment);
807 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_fee_struct_tbl(i).fee_editable_flag = ' || l_fee_struct_tbl(i).fee_editable_flag);
808 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_fee_struct_tbl(i).disb_header_id = ' || l_fee_struct_tbl(i).disb_header_id);
809 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_fee_struct_tbl(i).phase = ' || l_fee_struct_tbl(i).phase);
810 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_fee_struct_tbl(i).CUSTOM_PROCEDURE = ' || l_fee_struct_tbl(i).CUSTOM_PROCEDURE);
811
812 end loop;
813 close c_fees;
814
815 return l_fee_struct_tbl;
816
817 exception
818 when no_data_found then
819 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - no fee structures found');
820 end getFeeStructures;
821
822 /*=========================================================================
823 || PUBLIC FUNCTION getDisbursementFeeStructures R12
824 ||
825 || DESCRIPTION
826 || Overview: returns structure of fees for a given loan
827 ||
828 || PSEUDO CODE/LOGIC
829 ||
830 || PARAMETERS
831 || Parameter: p_loan_id => loan_id
832 || p_installment => installment number for the loan
833 || p_phase => the disbursement phase
834 || p_disb_header_id =>
835 || p_fee_id =>
836 ||
837 || Return value:
838 || table of fee structures needed to calculate disbursement fees
839 || KNOWN ISSUES
840 ||
841 || NOTES
842 ||
843 || MODIFICATION HISTORY
844 || Date Author Description of Changes
845 || 7/22/2005 8:40PM raverma Created
846 || 15-Sep-2009 MBOLLI Bug#8904071 - Returned fee_description along with other column values
847 *=======================================================================*/
848 function getDisbursementFeeStructures(p_loan_id in number
849 ,p_installment_no in number
850 ,p_phase in varchar2
851 ,p_disb_header_id in number
852 ,p_fee_id in number) return LNS_FEE_ENGINE.FEE_STRUCTURE_TBL
853 is
854
855 l_fee_id number;
856 l_fee_name varchar2(50);
857 l_fee_description varchar(250);
858 l_fee_type varchar2(30);
859 l_fee_category varchar2(30);
860 l_fee number;
861 l_fee_basis varchar2(30);
862 l_billing_option varchar2(30);
863 l_rate_type varchar2(30);
864 i number := 0;
865 l_fee_struct_tbl LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
866 l_api_name varchar2(50);
867 l_begin_installment_number number;
868 l_end_installment_number number;
869 l_disbursement_date date;
870 l_disbursement_amount number;
871 l_disb_header_id number;
872 l_phase varchar2(30);
873
874 cursor c_fees(p_loan_id number, p_installment_no number, c_phase varchar2
875 , p_disb_header_id number, p_fee_id number) IS
876 SELECT fees.fee_id
877 ,fees.fee_name
878 ,fees.fee_description
879 ,fees.fee_type
880 ,fees.fee_category
881 ,decode(fees.rate_type, 'FIXED', nvl(Assgn.fee,fees.fee), 'VARIABLE', nvl(Assgn.fee,fees.fee)/100)
882 ,fees.fee_basis
883 ,assgn.billing_option
884 ,fees.rate_type
885 ,nvl(assgn.begin_installment_number,0)
886 ,nvl(assgn.end_installment_number,0)
887 ,dh.disb_header_id
888 ,dh.target_date
889 ,dh.header_amount
890 ,nvl(assgn.phase, 'TERM')
891 from lns_fee_assignments assgn
892 ,lns_fees_all fees
893 ,lns_disb_headers dh
894 where dh.loan_id = nvl(p_loan_id, dh.loan_id)
895 and dh.disb_header_id = nvl(p_disb_header_id, dh.disb_header_id)
896 and fees.fee_id = nvl(p_fee_id, fees.fee_id)
897 and assgn.fee_id = fees.fee_id
898 and assgn.disb_header_id = dh.disb_header_id
899 and nvl(trunc(assgn.end_date_active), trunc(sysdate) + 1) >= trunc(sysdate)
900 and nvl(trunc(assgn.start_date_active), trunc(sysdate) - 1) <= trunc(sysdate)
901 and fees.fee_category = 'EVENT'
902 and fees.fee_type = 'EVENT_FUNDING'
903 and assgn.begin_installment_number <= nvl(p_installment_no, assgn.begin_installment_number)
904 and assgn.end_installment_number >= nvl(p_installment_no, assgn.end_installment_number)
905 and nvl(assgn.phase, 'TERM') = c_phase;
906
907 begin
908 l_api_name := 'getDisbursementFeeStructures';
909 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_installment_no ' || p_installment_no);
910 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_phase ' || p_phase);
911 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_loan_id ' || p_loan_id);
912 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_disb_header_id ' || p_disb_header_id);
913 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_fee_id ' || p_fee_id);
914
915 l_phase := nvl(p_phase, 'TERM');
916
917
918 open c_fees(p_loan_id, p_installment_no, l_phase, p_disb_header_id, p_fee_id);
919 LOOP
920 i := i + 1;
921 fetch c_fees into
922 l_fee_id
923 ,l_fee_name
924 ,l_fee_description
925 ,l_fee_type
926 ,l_fee_category
927 ,l_fee
928 ,l_fee_basis
929 ,l_billing_option
930 ,l_rate_type
931 ,l_begin_installment_number
932 ,l_end_installment_number
933 ,l_disb_header_id
934 ,l_disbursement_date
935 ,l_disbursement_amount
936 ,l_phase;
937 exit when c_fees%notfound;
938 l_fee_struct_tbl(i).fee_id := l_fee_id;
939 l_fee_struct_tbl(i).fee_name := l_fee_name;
940 l_fee_struct_tbl(i).fee_description :=l_fee_description;
941 l_fee_struct_tbl(i).fee_type := l_fee_type;
942 l_fee_struct_tbl(i).fee_category := l_fee_category;
943 l_fee_struct_tbl(i).fee_amount := l_fee;
944 l_fee_struct_tbl(i).fee_basis := l_fee_basis;
945 l_fee_struct_tbl(i).fee_billing_option := l_billing_option;
946 l_fee_struct_tbl(i).fee_rate_type := l_rate_type;
947 l_fee_struct_tbl(i).fee_from_installment := l_begin_installment_number;
948 l_fee_struct_tbl(i).fee_to_installment := l_end_installment_number;
949 l_fee_struct_tbl(i).disb_header_id := l_disb_header_id;
950 l_fee_struct_tbl(i).disbursement_date := l_disbursement_date;
951 l_fee_struct_tbl(i).disbursement_amount := l_disbursement_amount;
952 l_fee_struct_tbl(i).phase := l_phase;
953 end loop;
954 close c_fees;
955
956 return l_fee_struct_tbl;
957
958 exception
959 when no_data_found then
960 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - no fee structures found');
961 end getDisbursementFeeStructures;
962
963
964 /*=========================================================================
965 || PUBLIC PROCEDURE calculateFee ----- R12
966 ||
967 || DESCRIPTION
968 || Overview: this will calculate amount for a single fee for fee assignment page
969 ||
970 ||
971 || PSEUDO CODE/LOGIC
972 ||
973 || PARAMETERS
974 || Parameter: p_fee_id = fee to calculate
975 || p_disb_header_id = disbursement to calculate for
976 ||
977 || KNOWN ISSUES
978 ||
979 || NOTES
980 ||
981 || MODIFICATION HISTORY
982 || Date Author Description of Changes
983 || 7/29/2005 8:40PM raverma Created
984 ||
985 *=======================================================================*/
986 function calculateFee(p_fee_id in number
987 ,p_disb_header_id in number
988 ,p_loan_id in number) return number
989 is
990
991 -- fee basis for originalLoanAmount
992 cursor c_origLoanAmt(p_loan_id number) is
993 select requested_amount, nvl(current_phase, 'TERM')
994 from lns_loan_headers_all
995 where loan_id = p_loan_id;
996
997 cursor c_disbAmount(p_disb_header_id number) is
998 select header_amount
999 from lns_disb_headers
1000 where disb_header_id = p_disb_header_id;
1001
1002 cursor c_from_installment(c_fee_id number, c_disb_header_id number, c_phase varchar2) is
1003 select nvl(BEGIN_INSTALLMENT_NUMBER,0)
1004 from lns_fee_assignments
1005 where fee_id = c_fee_id
1006 and disb_header_id = c_disb_header_id
1007 and nvl(phase, 'TERM') = c_phase;
1008
1009 l_original_loan_amount number;
1010 l_orig_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
1011 l_fee_calc_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
1012 l_fee_basis_tbl LNS_FEE_ENGINE.FEE_BASIS_TBL;
1013 l_api_name varchar2(25);
1014 l_return_status VARCHAR2(1);
1015 l_msg_count NUMBER;
1016 l_msg_data VARCHAR2(32767);
1017 l_amount number;
1018 l_disb_amount number;
1019 l_phase VARCHAR2(30);
1020 l_installment NUMBER;
1021
1022 begin
1023
1024 l_amount := 0;
1025 l_api_name := 'calculateFee';
1026 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_fee_id ' || p_fee_id);
1027 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_disb_header_id ' || p_disb_header_id );
1028 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - calculating ' || p_loan_id);
1029
1030 open c_origLoanAmt(p_loan_id);
1031 fetch c_origLoanAmt into l_original_loan_amount, l_phase;
1032 close c_origLoanAmt;
1033
1034 if p_disb_header_id is null then
1035
1036 l_amount := calculateFee(p_fee_id => p_fee_id
1037 ,p_loan_id => p_loan_id
1038 ,p_phase => l_phase);
1039
1040 elsif p_disb_header_id is not null then
1041
1042 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - getting DIsb fee structures');
1043 l_orig_fee_structures := lns_fee_engine.getDisbursementFeeStructures(p_loan_id => null
1044 ,p_installment_no => null
1045 -- Bug#9255294, Change after adding new column phase in disbHdr table
1046 ,p_phase => l_phase
1047 ,p_disb_header_id => p_disb_header_id
1048 ,p_fee_id => p_fee_id);
1049 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - struct count is ' || l_orig_fee_structures.count);
1050
1051 l_fee_basis_tbl(1).fee_basis_name := 'ORIG_LOAN';
1052 l_fee_basis_tbl(1).fee_basis_amount := l_original_loan_amount;
1053 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - orig_loan ' || l_original_loan_amount);
1054
1055 IF l_orig_fee_structures.count = 0 THEN
1056 -- this fee has not yet been assigned to the loan, we need to get the fee structure from LNS_FEES, NOT
1057 -- FROM LNS_FEE_ASSIGNMENTS
1058 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee is unassigned ');
1059 l_orig_fee_structures := getFeeStructures(p_fee_id => p_fee_id);
1060 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - structs ' || l_orig_fee_structures.count);
1061
1062 l_installment := l_orig_fee_structures(1).fee_from_installment;
1063
1064 open c_disbAmount(p_disb_header_id);
1065 fetch c_disbAmount into l_disb_amount;
1066 close c_disbAmount;
1067
1068 l_fee_basis_tbl(2).fee_basis_name := 'IND_DISB_AMT';
1069 l_fee_basis_tbl(2).fee_basis_amount := l_disb_amount;
1070 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - disb amount ' || l_disb_amount);
1071 ELSE
1072 l_fee_basis_tbl(2).fee_basis_name := 'IND_DISB_AMT';
1073 l_fee_basis_tbl(2).fee_basis_amount := l_orig_fee_structures(1).DISBURSEMENT_AMOUNT;
1074 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - disb amount ' || l_orig_fee_structures(1).DISBURSEMENT_AMOUNT);
1075
1076 begin
1077 open c_from_installment(p_fee_id, p_disb_header_id, l_phase);
1078 fetch c_from_installment into l_installment;
1079 close c_from_installment;
1080 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_installment ' || l_installment);
1081 exception
1082 when no_data_found then
1083 l_installment := 0;
1084 end;
1085
1086 END IF;
1087
1088
1089 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - calculating fees');
1090 calculateFees(p_loan_id => p_loan_id
1091 ,p_fee_basis_tbl => l_fee_basis_tbl
1092 ,p_installment => l_installment
1093 ,p_fee_structures => l_orig_fee_structures
1094 ,x_fees_tbl => l_fee_calc_tbl
1095 ,x_return_status => l_return_status
1096 ,x_msg_count => l_msg_count
1097 ,x_msg_data => l_msg_data);
1098 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fees count is ' || l_fee_calc_tbl.count);
1099 for k in 1..l_fee_calc_tbl.count
1100 loop
1101 l_amount := l_amount + l_fee_calc_tbl(k).FEE_AMOUNT;
1102 end loop;
1103
1104 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee total is ' || l_amount);
1105
1106 end if;
1107
1108 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee total is ' || l_amount);
1109 return l_amount;
1110
1111 exception
1112 when others then
1113 return l_amount;
1114
1115 end calculateFee;
1116
1117
1118 /*=========================================================================
1119 || PUBLIC PROCEDURE calculateFee ----- LNS.B
1120 ||
1121 || DESCRIPTION
1122 || Overview: this will calculate amount for a single fee for fee assignment page
1123 || fee MUST be assigned to the loan
1124 ||
1125 || PSEUDO CODE/LOGIC
1126 ||
1127 || PARAMETERS
1128 || Parameter: p_fee_id = fee to calculate
1129 || p_loan_id = loan to calculate for
1130 || p_phase = phase of the fee
1131 ||
1132 || KNOWN ISSUES
1133 ||
1134 || NOTES
1135 ||
1136 || MODIFICATION HISTORY
1137 || Date Author Description of Changes
1138 || 1/10/2005 8:40PM raverma Created
1139 ||
1140 *=======================================================================*/
1141 function calculateFee(p_fee_id IN NUMBER
1142 ,p_loan_id IN NUMBER
1143 ,p_phase IN VARCHAR2) return number
1144 is
1145 l_api_name varchar2(25);
1146 l_return_status VARCHAR2(1);
1147 l_msg_count NUMBER;
1148 l_msg_data VARCHAR2(32767);
1149 l_calc_fee number;
1150 i number;
1151 l_fee_calc_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
1152 l_fee_basis_tbl LNS_FEE_ENGINE.FEE_BASIS_TBL;
1153 l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
1154 l_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
1155 l_installment Number;
1156 l_phase varchar2(30);
1157
1158 l_loan_status varchar2(30);
1159 l_based_on_terms varchar2(30);
1160
1161 cursor c_from_installment(c_fee_id number, c_loan_id number, c_phase varchar2) is
1162 select nvl(BEGIN_INSTALLMENT_NUMBER,0)
1163 from lns_fee_assignments
1164 where fee_id = c_fee_id
1165 and loan_id = c_loan_id
1166 and nvl(phase, 'TERM') = c_phase;
1167
1168 begin
1169
1170 l_calc_fee := 0;
1171
1172 l_api_name := 'calculateFee';
1173
1174 select lh.loan_status into l_loan_status
1175 from lns_loan_headers_all lh
1176 where lh.loan_id = P_LOAN_ID;
1177 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_loan_status = ' || l_loan_status);
1178
1179 if(l_loan_status = 'INCOMPLETE' or l_loan_status = 'PENDING') then
1180 l_based_on_terms := 'ORIGINAL';
1181 else
1182 l_based_on_terms := 'CURRENT';
1183 end if;
1184 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_based_on_terms = ' || l_based_on_terms);
1185
1186 l_phase := nvl(p_phase, 'TERM');
1187 -- compute the installment based on p_fee_assignment_id
1188 l_fee_structures := lns_fee_engine.getFeeStructures(p_loan_id => p_loan_id
1189 ,p_fee_category => null
1190 ,p_fee_type => null
1191 ,p_installment => null
1192 ,p_phase => l_phase
1193 ,p_fee_id => p_fee_id);
1194 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - structs ' || l_fee_structures.count);
1195
1196 if l_fee_structures.count = 0 then
1197 -- this fee has not yet been assigned to the loan, we need to get the fee structure from LNS_FEES, NOT LNS_FEE_ASSIGNMENTS
1198 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee is unassigned ');
1199 l_fee_structures := getFeeStructures(p_fee_id => p_fee_id);
1200 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - structs ' || l_fee_structures.count);
1201 l_installment := l_fee_structures(1).fee_from_installment;
1202 else
1203 -- the fee has been assigned to the loan, get the fee_installment from the assignments table
1204
1205 begin
1206 open c_from_installment(p_fee_id, p_loan_id, l_phase);
1207 fetch c_from_installment into l_installment;
1208 close c_from_installment;
1209 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_installment ' || l_installment);
1210 exception
1211 when
1212 no_data_found then
1213 l_installment := 0;
1214 end;
1215 end if;
1216
1217 -- build the fee bases
1218 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - getting details');
1219 l_loan_details := lns_financials.getLoanDetails(p_loan_id, l_based_on_terms, l_phase);
1220
1221 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_installment ' || l_installment);
1222
1223 l_fee_basis_tbl(1).fee_basis_name := 'TOTAL_BAL';
1224 l_fee_basis_tbl(1).fee_basis_amount := l_loan_details.remaining_balance;
1225 l_fee_basis_tbl(2).fee_basis_name := 'ORIG_LOAN';
1226 l_fee_basis_tbl(2).fee_basis_amount := l_loan_details.requested_amount;
1227 l_fee_basis_tbl(3).fee_basis_name := 'TOTAL_DISB_AMT';
1228 l_fee_basis_tbl(3).fee_basis_amount := l_loan_details.funded_amount;
1229 l_fee_basis_tbl(4).fee_basis_name := 'OVERDUE_PRIN';
1230 l_fee_basis_tbl(4).fee_basis_amount := l_loan_details.unpaid_principal;
1231 l_fee_basis_tbl(5).fee_basis_name := 'OVERDUE_PRIN_INT';
1232 l_fee_basis_tbl(5).fee_basis_amount := l_loan_details.unpaid_principal + l_loan_details.UNPAID_INTEREST;
1233 l_fee_basis_tbl(6).fee_basis_name := 'IND_DISB_AMT';
1234 l_fee_basis_tbl(6).fee_basis_amount := l_loan_details.funded_amount;
1235 l_fee_basis_tbl(7).fee_basis_name := 'TOTAL_UNDISB_AMT';
1236 l_fee_basis_tbl(7).fee_basis_amount := l_loan_details.requested_amount + l_loan_details.ADD_REQUESTED_AMOUNT - l_loan_details.FUNDED_AMOUNT;
1237 l_fee_basis_tbl(8).fee_basis_name := 'OVERDUE_INT';
1238 l_fee_basis_tbl(8).fee_basis_amount := l_loan_details.UNPAID_INTEREST;
1239 l_fee_basis_tbl(9).fee_basis_name := 'CURR_LOAN';
1240 l_fee_basis_tbl(9).fee_basis_amount := l_loan_details.requested_amount + l_loan_details.ADD_REQUESTED_AMOUNT;
1241
1242 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - calculating fees');
1243 calculateFees(p_loan_id => p_loan_id
1244 ,p_fee_basis_tbl => l_fee_basis_tbl
1245 ,p_installment => l_installment
1246 ,p_fee_structures => l_fee_structures
1247 ,x_fees_tbl => l_fee_calc_tbl
1248 ,x_return_status => l_return_status
1249 ,x_msg_count => l_msg_count
1250 ,x_msg_data => l_msg_data);
1251
1252 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fees count is ' || l_fee_calc_tbl.count);
1253 for k in 1..l_fee_calc_tbl.count
1254 loop
1255 l_calc_fee := l_calc_fee + l_fee_calc_tbl(k).FEE_AMOUNT;
1256 end loop;
1257
1258 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee total is ' || l_calc_fee);
1259 return l_calc_fee;
1260
1261 exception
1262 when others then
1263 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - ERROR ' || sqlerrm);
1264 return -1;
1265
1266 end calculateFee;
1267
1268 /*=========================================================================
1269 || PUBLIC PROCEDURE calculateFees ----- LNS.B
1270 ||
1271 || DESCRIPTION
1272 || Overview: this is main fee calculation engine
1273 ||
1274 || PSEUDO CODE/LOGIC
1275 ||
1276 || PARAMETERS
1277 || Parameter: p_fee_structures => fee structuring for the loan
1278 || p_fee_basis_tbl => fee bases
1279 || p_installment => installment number
1280 ||
1281 || Return value:
1282 || x_fees_tbl table of fees for a given installment
1283 || KNOWN ISSUES
1284 ||
1285 || NOTES
1286 ||
1287 || MODIFICATION HISTORY
1288 || Date Author Description of Changes
1289 || 12/16/2004 8:40PM raverma Created
1290 ||
1291 *=======================================================================*/
1292 procedure calculateFees(p_loan_id in number
1293 ,p_fee_basis_tbl in LNS_FEE_ENGINE.FEE_BASIS_TBL
1294 ,p_installment in number
1295 ,p_fee_structures IN LNS_FEE_ENGINE.FEE_STRUCTURE_TBL
1296 ,x_fees_tbl OUT nocopy LNS_FEE_ENGINE.FEE_CALC_TBL
1297 ,x_return_status out nocopy varchar2
1298 ,x_msg_count out nocopy number
1299 ,x_msg_data out nocopy varchar2)
1300 is
1301 l_api_name varchar2(25);
1302 l_basis_amount number;
1303 l_total_fees number;
1304 l_fees_tbl lns_fee_engine.fee_calc_tbl;
1305 k number;
1306 l_fee number;
1307 l_precision number;
1308 l_intervals number;
1309 l_error varchar2(2000);
1310 l_plsql_block varchar2(2000);
1311
1312 cursor c_precision (p_loan_id number)
1313 is
1314 SELECT fndc.precision
1315 FROM lns_loan_headers_all lnh
1316 ,fnd_currencies fndc
1317 WHERE lnh.loan_id = p_loan_id
1318 and lnh.loan_currency = fndc.currency_code;
1319
1320 begin
1321
1322 l_api_name := 'calculateFees';
1323 l_total_fees := 0;
1324 l_intervals := 1;
1325 x_return_status := FND_API.G_RET_STS_SUCCESS;
1326
1327 -- first figure out if fee applies to the current installment
1328 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1329 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - p_loan_id = ' || p_loan_id);
1330 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - p_installment = ' || p_installment);
1331 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - # fee structures ' || p_fee_structures.count);
1332 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - # fee basis ' || p_fee_basis_tbl.count);
1333
1334 open c_precision(p_loan_id);
1335 fetch c_precision into l_precision;
1336 close c_precision;
1337
1338 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - # fee precision ' || l_precision);
1339
1340 for f in 1..p_fee_structures.count loop
1341 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' -------- STRUCTURE ' || f);
1342 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - from inst = ' || p_fee_structures(f).fee_from_installment);
1343 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - to inst = ' || p_fee_structures(f).fee_to_installment);
1344 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - basis = ' || p_fee_structures(f).fee_basis);
1345 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - billing option = ' || p_fee_structures(f).fee_billing_option);
1346 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - rate = ' || p_fee_structures(f).fee_rate_type);
1347 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - phase = ' || p_fee_structures(f).phase);
1348 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - CUSTOM_PROCEDURE = ' || p_fee_structures(f).CUSTOM_PROCEDURE);
1349
1350 l_fee := 0;
1351
1352 if p_installment >= p_fee_structures(f).fee_from_installment and
1353 p_installment <= p_fee_structures(f).fee_to_installment then
1354
1355 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - fees ' || f || ' applies to this installment ');
1356
1357 if p_fee_structures(f).fee_billing_option = 'EQUALLY' then
1358 l_intervals := p_fee_structures(f).fee_to_installment - p_fee_structures(f).fee_from_installment + 1;
1359 end if;
1360
1361 if p_fee_structures(f).fee_rate_type = 'FIXED' then
1362
1363 if p_installment = p_fee_structures(f).fee_to_installment then
1364 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - amount = : ' || p_fee_structures(f).fee_amount);
1365 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - raw: ' || p_fee_structures(f).fee_amount / l_intervals);
1366 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - raw2 - ' || round((p_fee_structures(f).fee_amount / l_intervals),2) * (l_intervals - 1));
1367 l_fee := p_fee_structures(f).fee_amount - round((p_fee_structures(f).fee_amount / l_intervals),l_precision) * (l_intervals - 1);
1368 else
1369 l_fee := round(p_fee_structures(f).fee_amount / l_intervals, l_precision);
1370 end if;
1371
1372 elsif p_fee_structures(f).fee_rate_type = 'VARIABLE' then
1373
1374 begin
1375 k := 0;
1376 LOOP
1377 k := k + 1;
1378 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee basis = ' || p_fee_basis_tbl(k).fee_basis_name);
1379 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee basis amount = ' || p_fee_basis_tbl(k).fee_basis_amount);
1380 EXIT WHEN p_fee_basis_tbl(k).fee_basis_name = p_fee_structures(f).fee_basis;
1381 END LOOP;
1382 --logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - fee basis ' || p_fee_basis_tbl(k).fee_basis_name);
1383 --logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - fee basis amount ' || p_fee_basis_tbl(k).fee_basis_amount);
1384
1385 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BASISES MATCH!');
1386 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - percent = ' || p_fee_structures(f).fee_amount);
1387 l_basis_amount := p_fee_basis_tbl(k).fee_basis_amount;
1388
1389 if p_installment = p_fee_structures(f).fee_to_installment then
1390 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);
1391 else
1392 l_fee := round(p_fee_structures(f).fee_amount * l_basis_amount / l_intervals,l_precision) ;
1393 end if;
1394
1395 exception
1396 when no_data_found then
1397 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - in exception');
1398 l_fee := 0;
1399 --FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_CALC_ERROR');
1400 --FND_MSG_PUB.ADD;
1401 --RAISE FND_API.G_EXC_ERROR;
1402 end;
1403
1404 elsif p_fee_structures(f).fee_rate_type = 'CUSTOM' then
1405
1406 l_plsql_block := 'BEGIN ' || p_fee_structures(f).CUSTOM_PROCEDURE || '(:1, :2, :3, :4, :5); END;';
1407 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_plsql_block = ' || l_plsql_block);
1408
1409 BEGIN
1410
1411 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - Calling...');
1412
1413 EXECUTE IMMEDIATE l_plsql_block
1414 USING
1415 IN p_loan_id,
1416 IN p_fee_structures(f).FEE_ID,
1417 IN p_installment,
1418 OUT l_fee,
1419 OUT l_error;
1420
1421 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Done');
1422
1423 EXCEPTION
1424 WHEN OTHERS THEN
1425 FND_MESSAGE.SET_NAME('LNS', 'LNS_CUSTOM_FEE_FAILED');
1426 FND_MESSAGE.SET_TOKEN('FEE_NAME' ,p_fee_structures(f).FEE_NAME);
1427 FND_MESSAGE.SET_TOKEN('ERROR_MESG' ,SQLERRM);
1428 FND_MSG_PUB.ADD;
1429 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1430 l_fee := 0;
1431 END;
1432
1433 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_fee = ' || l_fee);
1434 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_error = ' || l_error);
1435
1436 if l_error is not null then
1437 FND_MESSAGE.SET_NAME('LNS', 'LNS_CUSTOM_FEE_FAILED');
1438 FND_MESSAGE.SET_TOKEN('FEE_NAME' ,p_fee_structures(f).FEE_NAME);
1439 FND_MESSAGE.SET_TOKEN('ERROR_MESG' ,l_error);
1440 FND_MSG_PUB.ADD;
1441 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1442 l_fee := 0;
1443 end if;
1444
1445 end if;
1446
1447 end if;
1448 --assign output table of fees for each fee structure
1449 l_fees_tbl(f).fee_id := p_fee_structures(f).fee_id;
1450 l_fees_tbl(f).fee_name := p_fee_structures(f).fee_name;
1451 l_fees_tbl(f).fee_amount := round(l_fee, l_precision);
1452 l_fees_tbl(f).fee_installment := p_installment;
1453 l_fees_tbl(f).phase := nvl(p_fee_structures(f).phase, 'TERM');
1454 l_fees_tbl(f).disb_header_id := p_fee_structures(f).disb_header_id;
1455 -- Bug#8904071
1456 l_fees_tbl(f).fee_description := p_fee_structures(f).fee_description;
1457 l_fees_tbl(f).FEE_CATEGORY := p_fee_structures(f).fee_category;
1458 l_fees_tbl(f).FEE_TYPE := p_fee_structures(f).fee_type;
1459 l_fees_tbl(f).FEE_BILLING_OPTION := p_fee_structures(f).fee_billing_option;
1460
1461 l_fees_tbl(f).fee_schedule_id := -1; --assign this AFTER insert into fee_schedules
1462 l_fees_tbl(f).fee_waivable_flag := 'N';
1463 l_fees_tbl(f).FEE_DELETABLE_FLAG := 'N'; --from getFeeStructures
1464 l_fees_tbl(f).FEE_EDITABLE_FLAG := 'N'; --from getFeeStructures
1465 l_total_fees := l_total_fees + l_fees_tbl(f).fee_amount;
1466
1467 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee amount = ' || l_fee);
1468
1469 end loop;
1470
1471 x_fees_tbl := l_fees_tbl;
1472 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - calculated fees: ' || l_total_fees);
1473
1474 end calculateFees;
1475
1476 /*=========================================================================
1477 || PUBLIC PROCEDURE getFeeSchedule
1478 ||
1479 || DESCRIPTION
1480 || Overview: this procedure will return a table of fees off of the fee
1481 || schedule for the given installment
1482 ||
1483 || THIS WILL BE CALLED BY MAIN AMORTIZATION FUNCTION TO RETURN ACTUAL FEES
1484 || TO BE BILLED ON A LOAN
1485 ||
1486 || PSEUDO CODE/LOGIC
1487 ||
1488 || PARAMETERS
1489 || Parameter: p_fee_structure_tbl => represents a table of fees
1490 || p_loan_id => loan_id
1491 ||
1492 || Return value:
1493 || standard
1494 || KNOWN ISSUES
1495 ||
1496 || NOTES
1497 ||
1498 || MODIFICATION HISTORY
1499 || Date Author Description of Changes
1500 || 12/1/2004 8:40PM raverma Created
1501 || 7/29.2005 raverma check for disb_header_id is null
1502 || 02-Sep-2009 mbolli Bug#8848018 - Added new field fee_billig_option
1503 *=======================================================================*/
1504 procedure getFeeSchedule(p_init_msg_list in varchar2
1505 ,p_loan_id in number
1506 ,p_installment_number in number
1507 ,p_disb_header_id in number
1508 ,p_phase in varchar2
1509 ,x_fees_tbl OUT NOCOPY LNS_FEE_ENGINE.FEE_CALC_TBL
1510 ,x_return_status out nocopy varchar2
1511 ,x_msg_count out nocopy number
1512 ,x_msg_data out nocopy varchar2)
1513 is
1514 l_api_name varchar2(25);
1515 l_return_status VARCHAR2(1);
1516 l_msg_count NUMBER;
1517 l_msg_data VARCHAR2(32767);
1518
1519 i number;
1520 l_fee_rec FEE_CALC_REC;
1521 l_fee_schedule_id number;
1522 l_fee_id number;
1523 l_fee_amount number;
1524 l_fee_name varchar2(50);
1525 l_fee_installment number;
1526 l_fee_description varchar2(250);
1527 l_fee_waivable_flag varchar2(1);
1528 l_fee_category varchar2(30);
1529 l_fee_type varchar2(30);
1530 l_fee_deletable_flag varchar2(1);
1531 l_fee_editable_flag varchar2(1);
1532 l_fee_billing_option varchar2(30);
1533 l_phase varchar2(30);
1534
1535 -- unbilled fees on the schedule
1536 cursor c_fees(c_loan_id number, c_installment number, c_phase varchar2) is
1537 select sched.fee_schedule_id
1538 ,sched.fee_id
1539 ,sched.fee_amount - nvl(sched.waived_amount, 0)
1540 ,struct.fee_name
1541 ,struct.fee_category
1542 ,struct.fee_type
1543 ,sched.fee_installment
1544 ,struct.fee_description
1545 ,sched.fee_waivable_flag -- should be struct right
1546 ,decode(struct.fee_category, 'MANUAL', 'Y', 'N')
1547 ,nvl(struct.fee_editable_flag, 'N')
1548 ,struct.billing_option
1549 ,sched.phase
1550 from lns_fee_schedules sched
1551 ,lns_fees struct
1552 where sched.loan_id = c_loan_id
1553 and sched.fee_id = struct.fee_id
1554 and fee_installment = c_installment
1555 and nvl(phase, 'TERM') = c_phase
1556 and active_flag = 'Y'
1557 and billed_flag = 'N' -- deduce this based on parent records
1558 -- Bug#6961250 commented below line as for disbFees, disb_header_id is
1559 -- NOT NULL
1560 -- and disb_header_id is null
1561 and (not exists
1562 (select 'X'
1563 from lns_amortization_scheds am
1564 ,lns_amortization_lines lines
1565 where lines.loan_id = c_loan_id
1566 and lines.fee_schedule_id = sched.fee_schedule_id
1567 and am.loan_id = lines.loan_id
1568 and NVL(am.reversed_flag, 'N') = 'N'
1569 and am.payment_number = c_installment)
1570 or exists
1571 (select 'X'
1572 from lns_amortization_scheds am
1573 ,lns_amortization_lines lines
1574 where lines.loan_id = c_loan_id
1575 and lines.fee_schedule_id = sched.fee_schedule_id
1576 and am.loan_id = lines.loan_id
1577 and am.reversed_flag = 'Y'
1578 and am.payment_number = c_installment));
1579
1580 -- for openPhase
1581 cursor c_feeSchedule(c_disb_header_id number, c_phase varchar2) is
1582 select sched.fee_id
1583 ,sched.fee_amount - nvl(sched.waived_amount, 0)
1584 ,struct.fee_name
1585 ,struct.fee_description
1586 ,sched.phase
1587 from lns_fee_schedules sched
1588 ,lns_fees_all struct
1589 where disb_header_id = c_disb_header_id
1590 and sched.fee_id = struct.fee_id
1591 and nvl(sched.phase, 'TERM') = c_phase;
1592
1593 begin
1594
1595 l_api_name := 'getFeeSchedule';
1596 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1597
1598 -- Standard Start of API savepoint
1599 SAVEPOINT getFees;
1600
1601 -- Initialize message list IF p_init_msg_list is set to TRUE.
1602 IF FND_API.to_Boolean(p_init_msg_list) THEN
1603 FND_MSG_PUB.initialize;
1604 END IF;
1605
1606 -- Initialize API return status to SUCCESS
1607 x_return_status := FND_API.G_RET_STS_SUCCESS;
1608
1609 -- ---------------------------------------------------------------------
1610 -- Api body
1611 -- ---------------------------------------------------------------------
1612 -- initialize any variables here
1613 i := 0;
1614
1615 l_phase := nvl(p_phase, 'TERM');
1616
1617 if p_disb_header_id is null then
1618 OPEN c_fees(p_loan_id, p_installment_number, l_phase);
1619 LOOP
1620 i := i + 1;
1621 FETCH c_fees INTO
1622 l_fee_schedule_id
1623 ,l_fee_id
1624 ,l_fee_amount
1625 ,l_fee_name
1626 ,l_fee_category
1627 ,l_fee_type
1628 ,l_fee_installment
1629 ,l_fee_description
1630 ,l_fee_waivable_flag
1631 ,l_fee_deletable_flag
1632 ,l_fee_editable_flag
1633 ,l_fee_billing_option
1634 ,l_phase;
1635 EXIT WHEN c_fees%NOTFOUND;
1636
1637 l_fee_rec.fee_schedule_id := l_fee_schedule_id;
1638 l_fee_rec.fee_id := l_fee_id;
1639 l_fee_rec.fee_amount := l_fee_amount;
1640 l_fee_rec.fee_name := l_fee_name;
1641 l_fee_rec.fee_category := l_fee_category;
1642 l_fee_rec.fee_type := l_fee_type;
1643 l_fee_rec.fee_installment := l_fee_installment;
1644 l_fee_rec.fee_description := l_fee_description;
1645 l_fee_rec.fee_waivable_flag := l_fee_waivable_flag;
1646 l_fee_rec.fee_deletable_flag := l_fee_deletable_flag;
1647 l_fee_rec.fee_editable_flag := l_fee_editable_flag;
1648 l_fee_rec.fee_billing_option := l_fee_billing_option;
1649 l_fee_rec.phase := l_phase;
1650
1651 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee #: ' || i);
1652 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee schedule id: ' || l_fee_rec.fee_schedule_id);
1653 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee id: ' || l_fee_rec.fee_id);
1654 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee amount: ' || l_fee_rec.fee_amount);
1655 x_fees_tbl(i) := l_fee_rec;
1656 END LOOP;
1657 elsif p_disb_header_id is not null then
1658 OPEN c_feeSchedule(p_disb_header_id, l_phase);
1659 LOOP
1660 i := i + 1;
1661 FETCH c_feeSchedule INTO
1662 l_fee_id
1663 ,l_fee_amount
1664 ,l_fee_name
1665 --,l_fee_category
1666 --,l_fee_type
1667 --,l_fee_installment
1668 ,l_fee_description
1669 --,l_fee_waivable_flag
1670 --,l_fee_deletable_flag
1671 --,l_fee_editable_flag
1672 ,l_phase;
1673 EXIT WHEN c_feeSchedule%NOTFOUND;
1674
1675 --l_fee_rec.fee_schedule_id := l_fee_schedule_id;
1676 l_fee_rec.fee_id := l_fee_id;
1677 l_fee_rec.fee_amount := l_fee_amount;
1678 l_fee_rec.fee_name := l_fee_name;
1679 --l_fee_rec.fee_category := l_fee_category;
1680 --l_fee_rec.fee_type := l_fee_type;
1681 --l_fee_rec.fee_installment := l_fee_installment;
1682 l_fee_rec.fee_description := l_fee_description;
1683 --l_fee_rec.fee_waivable_flag := l_fee_waivable_flag;
1684 --l_fee_rec.fee_deletable_flag := l_fee_deletable_flag;
1685 --l_fee_rec.fee_editable_flag := l_fee_editable_flag;
1686 l_fee_rec.phase := l_phase;
1687
1688 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee #: ' || i);
1689 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee schedule id: ' || l_fee_rec.fee_schedule_id);
1690 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee id: ' || l_fee_rec.fee_id);
1691 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee amount: ' || l_fee_rec.fee_amount);
1692 x_fees_tbl(i) := l_fee_rec;
1693 END LOOP;
1694 end if;
1695
1696 -- ---------------------------------------------------------------------
1697 -- End of API body
1698 -- ---------------------------------------------------------------------
1699 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1700
1701 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1702
1703 EXCEPTION
1704 WHEN FND_API.G_EXC_ERROR THEN
1705 x_return_status := FND_API.G_RET_STS_ERROR;
1706 x_msg_count := l_msg_count;
1707 x_msg_data := l_msg_data;
1708 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1709 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1710 ROLLBACK TO getFees;
1711
1712 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1713 x_return_status := FND_API.G_RET_STS_ERROR;
1714 x_msg_count := l_msg_count;
1715 x_msg_data := l_msg_data;
1716 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1717 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1718 ROLLBACK TO getFees;
1719
1720 WHEN OTHERS THEN
1721 x_return_status := FND_API.G_RET_STS_ERROR;
1722 x_msg_count := l_msg_count;
1723 x_msg_data := l_msg_data;
1724 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1725 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1726 ROLLBACK TO getFees;
1727 end getFeeSchedule;
1728
1729 procedure getFeeDetails(p_init_msg_list in varchar2
1730 ,p_loan_id in number
1731 ,p_installment in number
1732 ,p_fee_basis_tbl in LNS_FEE_ENGINE.FEE_BASIS_TBL
1733 ,p_based_on_terms in varchar2
1734 ,p_phase in varchar2
1735 ,x_fees_tbl out nocopy LNS_FEE_ENGINE.FEE_CALC_TBL
1736 ,x_return_status out nocopy varchar2
1737 ,x_msg_count out nocopy number
1738 ,x_msg_data out nocopy varchar2)
1739
1740 IS
1741
1742 l_api_name varchar2(25);
1743 l_return_status VARCHAR2(1);
1744 l_msg_count NUMBER;
1745 l_msg_data VARCHAR2(32767);
1746
1747 i NUMBER;
1748 n NUMBER;
1749 l_schd varchar2(1);
1750 allFeeCount NUMBER;
1751 l_loan_id NUMBER;
1752 l_phase VARCHAR2(15);
1753 l_last_installment number;
1754
1755 l_fee_basis_tbl LNS_FEE_ENGINE.FEE_BASIS_TBL;
1756
1757 l_recur_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
1758 l_new_recur_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
1759 l_memo_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
1760 l_funding_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
1761 l_new_fund_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
1762 l_orig_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
1763 l_new_orig_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
1764
1765 l_all_fees_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
1766 l_recur_fees_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
1767 l_memo_fees_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
1768 l_funding_fees_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
1769 l_orig_fees_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
1770
1771 -- unbilled fees on the schedule
1772 cursor c_schd_fees(c_loan_id number, c_installment number, c_phase varchar2) is
1773 select sched.fee_schedule_id
1774 ,sched.fee_id
1775 ,sched.fee_amount - nvl(sched.waived_amount, 0)
1776 ,struct.fee_name
1777 ,struct.fee_category
1778 ,struct.fee_type
1779 ,sched.fee_installment
1780 ,struct.fee_description
1781 ,sched.fee_waivable_flag -- should be struct right
1782 ,decode(struct.fee_category, 'MANUAL', 'Y', 'N')
1783 ,decode(struct.fee_category, 'MANUAL', nvl(struct.fee_editable_flag, 'N'), 'N')
1784 ,struct.billing_option
1785 ,sched.phase
1786 from lns_fee_schedules sched
1787 ,lns_fees struct
1788 where sched.loan_id = c_loan_id
1789 and sched.fee_id = struct.fee_id
1790 and fee_installment = c_installment
1791 and nvl(phase, 'TERM') = c_phase
1792 and active_flag = 'Y'
1793 and billed_flag = 'N' -- deduce this based on parent records
1794 and (not exists
1795 (select 'X'
1796 from lns_amortization_scheds am
1797 ,lns_amortization_lines lines
1798 where lines.loan_id = c_loan_id
1799 and lines.fee_schedule_id = sched.fee_schedule_id
1800 and am.loan_id = lines.loan_id
1801 and am.payment_number = c_installment));
1802
1803 cursor c_schd_fees1(c_loan_id number, c_installment number, c_phase varchar2) is
1804 select sched.fee_schedule_id
1805 ,sched.fee_id
1806 ,sched.fee_amount - nvl(sched.waived_amount, 0)
1807 ,struct.fee_name
1808 ,struct.fee_category
1809 ,struct.fee_type
1810 ,sched.fee_installment
1811 ,struct.fee_description
1812 ,sched.fee_waivable_flag -- should be struct right
1813 ,decode(struct.fee_category, 'MANUAL', 'Y', 'N')
1814 ,decode(struct.fee_category, 'MANUAL', nvl(struct.fee_editable_flag, 'N'), 'N')
1815 ,struct.billing_option
1816 ,sched.phase
1817 from lns_fee_schedules sched
1818 ,lns_fees struct
1819 where sched.loan_id = c_loan_id
1820 and sched.fee_id = struct.fee_id
1821 and fee_installment = c_installment
1822 and nvl(phase, 'TERM') = c_phase
1823 and active_flag = 'Y'
1824 and struct.fee_category = 'MANUAL';
1825
1826 -- filtering out fees for current stage
1827 CURSOR cur_fee_schd_inst_exist(c_loan_id NUMBER, c_fee_id NUMBER, c_installment NUMBER, c_phase VARCHAR2, c_disb_header_id NUMBER) IS
1828 SELECT 'X'
1829 FROM LNS_FEE_SCHEDULES schd
1830 WHERE schd.fee_id = c_fee_id
1831 AND schd.fee_installment = c_installment
1832 AND schd.loan_id = c_loan_id
1833 AND schd.ACTIVE_FLAG = 'Y'
1834 --AND schd.billed_flag = 'N'
1835 AND schd.phase = c_phase
1836 AND NVL(schd.disb_header_id, -1) = NVL(c_disb_header_id, -1);
1837
1838 -- filtering out fees for origination stage
1839 CURSOR cur_fee_schd_inst_exist1(c_loan_id NUMBER, c_fee_id NUMBER, c_installment NUMBER, c_phase VARCHAR2, c_disb_header_id NUMBER) IS
1840 SELECT 'X'
1841 FROM LNS_FEE_SCHEDULES schd
1842 WHERE schd.fee_id = c_fee_id
1843 AND schd.fee_installment = c_installment
1844 AND schd.loan_id = c_loan_id
1845 AND schd.ACTIVE_FLAG = 'Y'
1846 AND schd.phase = c_phase
1847 AND NVL(schd.disb_header_id, -1) = NVL(c_disb_header_id, -1);
1848
1849 BEGIN
1850
1851 l_api_name := 'getFeeDetails';
1852 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1853
1854 -- Standard Start of API savepoint
1855 SAVEPOINT getFeeDetails;
1856
1857 -- Initialize message list IF p_init_msg_list is set to TRUE.
1858 IF FND_API.to_Boolean(p_init_msg_list) THEN
1859 FND_MSG_PUB.initialize;
1860 END IF;
1861
1862 -- Initialize API return status to SUCCESS
1863 x_return_status := FND_API.G_RET_STS_SUCCESS;
1864
1865 -- ---------------------------------------------------------------------
1866 -- Api body
1867 -- ---------------------------------------------------------------------
1868
1869 i := 0;
1870 allFeeCount := 0;
1871
1872 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Input:');
1873 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_loan_id is: ' || p_loan_id);
1874 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_installment is: ' || p_installment);
1875 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_based_on_terms: ' || p_based_on_terms);
1876 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee_basis passed is: ' || p_fee_basis_tbl.count);
1877 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_phase is: ' || p_phase);
1878
1879 l_phase := nvl(p_phase, 'TERM');
1880 l_loan_id := p_loan_id;
1881 l_fee_basis_tbl := p_fee_basis_tbl;
1882
1883 select LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER_EXT(p_loan_id)
1884 into l_last_installment
1885 from dual;
1886
1887 if (l_last_installment+1) = p_installment then
1888
1889 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - processing late fees');
1890 lns_fee_engine.processLateFees(p_loan_id => l_loan_id
1891 ,p_init_msg_list => 'F'
1892 ,p_commit => 'F'
1893 ,p_phase => l_phase
1894 ,x_return_status => l_return_status
1895 ,x_msg_count => l_msg_count
1896 ,x_msg_data => l_msg_data);
1897
1898 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - l_return_status = ' || l_return_status);
1899 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
1900 FND_MESSAGE.SET_NAME('LNS', 'LNS_PROCESS_FEE_ERROR');
1901 FND_MSG_PUB.ADD;
1902 RAISE FND_API.G_EXC_ERROR;
1903 end if;
1904
1905 end if;
1906
1907 -- Get Scheduled fees
1908 IF p_based_on_terms = 'CURRENT' THEN
1909 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - cursor c_schd_fees');
1910 OPEN c_schd_fees(l_loan_id, p_installment, l_phase);
1911 LOOP
1912 i := i + 1;
1913 FETCH c_schd_fees INTO
1914 l_all_fees_tbl(i).fee_schedule_id
1915 ,l_all_fees_tbl(i).fee_id
1916 ,l_all_fees_tbl(i).fee_amount
1917 ,l_all_fees_tbl(i).fee_name
1918 ,l_all_fees_tbl(i).fee_category
1919 ,l_all_fees_tbl(i).fee_type
1920 ,l_all_fees_tbl(i).fee_installment
1921 ,l_all_fees_tbl(i).fee_description
1922 ,l_all_fees_tbl(i).fee_waivable_flag
1923 ,l_all_fees_tbl(i).fee_deletable_flag
1924 ,l_all_fees_tbl(i).fee_editable_flag
1925 ,l_all_fees_tbl(i).fee_billing_option
1926 ,l_all_fees_tbl(i).phase;
1927 EXIT WHEN c_schd_fees%NOTFOUND;
1928
1929 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee #: ' || i);
1930 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee schedule id: ' || l_all_fees_tbl(i).fee_schedule_id);
1931 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee id: ' || l_all_fees_tbl(i).fee_id);
1932 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee amount: ' || l_all_fees_tbl(i).fee_amount);
1933
1934 END LOOP;
1935
1936 CLOSE c_schd_fees;
1937 ELSE
1938 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - cursor c_schd_fees1');
1939 OPEN c_schd_fees1(l_loan_id, p_installment, l_phase);
1940 LOOP
1941 i := i + 1;
1942 FETCH c_schd_fees1 INTO
1943 l_all_fees_tbl(i).fee_schedule_id
1944 ,l_all_fees_tbl(i).fee_id
1945 ,l_all_fees_tbl(i).fee_amount
1946 ,l_all_fees_tbl(i).fee_name
1947 ,l_all_fees_tbl(i).fee_category
1948 ,l_all_fees_tbl(i).fee_type
1949 ,l_all_fees_tbl(i).fee_installment
1950 ,l_all_fees_tbl(i).fee_description
1951 ,l_all_fees_tbl(i).fee_waivable_flag
1952 ,l_all_fees_tbl(i).fee_deletable_flag
1953 ,l_all_fees_tbl(i).fee_editable_flag
1954 ,l_all_fees_tbl(i).fee_billing_option
1955 ,l_all_fees_tbl(i).phase;
1956 EXIT WHEN c_schd_fees1%NOTFOUND;
1957
1958 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee #: ' || i);
1959 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee schedule id: ' || l_all_fees_tbl(i).fee_schedule_id);
1960 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee id: ' || l_all_fees_tbl(i).fee_id);
1961 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee amount: ' || l_all_fees_tbl(i).fee_amount);
1962
1963 END LOOP;
1964
1965 CLOSE c_schd_fees1;
1966 END IF;
1967
1968 allFeeCount := l_all_fees_tbl.count;
1969 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Total fees are ' || allFeeCount);
1970
1971
1972 --------------------
1973 -- recurring fees
1974 --------------------
1975 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || '- getting recurring fee structures');
1976 l_recur_fee_structures := lns_fee_engine.getFeeStructures(p_loan_id => l_loan_id
1977 ,p_fee_category => 'RECUR'
1978 ,p_fee_type => null
1979 ,p_installment => p_installment
1980 ,p_phase => l_phase
1981 ,p_fee_id => null);
1982 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Recurring fee structures count = ' || l_recur_fee_structures.count);
1983
1984 -- filtering out recurring fees based on input details
1985 n := 0;
1986 for m in 1..l_recur_fee_structures.count loop
1987
1988 l_schd := null;
1989 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' ');
1990 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' Recurring fee ' || m);
1991 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' l_loan_id = ' || l_loan_id);
1992 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' fee_id = ' || l_recur_fee_structures(m).FEE_ID);
1993 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' p_installment = ' || p_installment);
1994 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' l_phase = ' || l_phase);
1995 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' disb_header_id = ' || l_recur_fee_structures(m).disb_header_id);
1996
1997 -- filtering out fees based on p_based_on_terms
1998 IF p_based_on_terms = 'CURRENT' THEN
1999 OPEN cur_fee_schd_inst_exist(l_loan_id, l_recur_fee_structures(m).fee_id, p_installment, l_phase, l_recur_fee_structures(m).disb_header_id);
2000 FETCH cur_fee_schd_inst_exist into l_schd;
2001 CLOSE cur_fee_schd_inst_exist;
2002 END IF;
2003
2004 if l_schd is null then
2005 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' Adding ' || l_recur_fee_structures(m).FEE_ID);
2006 n := n + 1;
2007 l_new_recur_fee_structures(n) := l_recur_fee_structures(m);
2008 else
2009 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' Filtering out ' || l_recur_fee_structures(m).FEE_ID);
2010 end if;
2011 end loop;
2012
2013 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Recurring fee structures count is ' || l_new_recur_fee_structures.count);
2014
2015 l_recur_fees_tbl.delete;
2016 if l_new_recur_fee_structures.count > 0 then
2017 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calling calculateFees for recurring fees...');
2018 lns_fee_engine.calculateFees(p_loan_id => l_loan_id
2019 ,p_installment => p_installment
2020 ,p_fee_basis_tbl => l_fee_basis_tbl
2021 ,p_fee_structures => l_new_recur_fee_structures
2022 ,x_fees_tbl => l_recur_fees_tbl
2023 ,x_return_status => l_return_status
2024 ,x_msg_count => l_msg_count
2025 ,x_msg_data => l_msg_data);
2026 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': calculated recurring fees ' || l_recur_fees_tbl.count);
2027 end if;
2028
2029 FOR k in 1..l_recur_fees_tbl.count LOOP
2030 l_all_fees_tbl(allFeeCount + k) := l_recur_fees_tbl(k);
2031 END LOOP;
2032
2033 allFeeCount := l_all_fees_tbl.count;
2034 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Total fees are ' || allFeeCount);
2035
2036 --------------------
2037 -- origination fees
2038 --------------------
2039 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || '- getting origination fee structures');
2040 l_orig_fee_structures := lns_fee_engine.getFeeStructures(p_loan_id => l_loan_id
2041 ,p_fee_category => 'EVENT'
2042 ,p_fee_type => 'EVENT_ORIGINATION'
2043 ,p_installment => p_installment
2044 ,p_phase => l_phase
2045 ,p_fee_id => null);
2046
2047 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': origination fee structures count is ' || l_orig_fee_structures.count);
2048
2049 n := 0;
2050 for m in 1..l_orig_fee_structures.count loop
2051
2052 l_schd := null;
2053 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' ');
2054 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' Origination fee ' || m);
2055 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' l_loan_id = ' || l_loan_id);
2056 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' fee_id = ' || l_orig_fee_structures(m).FEE_ID);
2057 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' p_installment = ' || p_installment);
2058 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' l_phase = ' || l_phase);
2059 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' disb_header_id = ' || l_orig_fee_structures(m).disb_header_id);
2060
2061 -- filtering out fees based on p_based_on_terms
2062 IF p_based_on_terms = 'CURRENT' THEN
2063 OPEN cur_fee_schd_inst_exist(l_loan_id, l_orig_fee_structures(m).fee_id, p_installment, l_phase, l_orig_fee_structures(m).disb_header_id);
2064 FETCH cur_fee_schd_inst_exist into l_schd;
2065 CLOSE cur_fee_schd_inst_exist;
2066 END IF;
2067
2068 if l_schd is null then
2069 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' Adding ' || l_orig_fee_structures(m).FEE_ID);
2070 n := n + 1;
2071 l_new_orig_fee_structures(n) := l_orig_fee_structures(m);
2072 else
2073 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' Filtering out ' || l_orig_fee_structures(m).FEE_ID);
2074 end if;
2075 end loop;
2076
2077 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Origination fee structures count is ' || l_new_orig_fee_structures.count);
2078
2079 l_orig_fees_tbl.delete;
2080 if l_new_orig_fee_structures.count > 0 then
2081 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calling calculateFees for origination fees...');
2082 lns_fee_engine.calculateFees(p_loan_id => l_loan_id
2083 ,p_installment => p_installment
2084 ,p_fee_basis_tbl => l_fee_basis_tbl
2085 ,p_fee_structures => l_new_orig_fee_structures
2086 ,x_fees_tbl => l_orig_fees_tbl
2087 ,x_return_status => l_return_status
2088 ,x_msg_count => l_msg_count
2089 ,x_msg_data => l_msg_data);
2090 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': calculated funding fees ' || l_orig_fees_tbl.count);
2091 end if;
2092
2093 FOR k in 1..l_orig_fees_tbl.count LOOP
2094 l_all_fees_tbl(allFeeCount + k) := l_orig_fees_tbl(k);
2095 END LOOP;
2096
2097 allFeeCount := l_all_fees_tbl.count;
2098
2099
2100 IF p_based_on_terms <> 'CURRENT' THEN
2101
2102 -- memo fees
2103 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || '- getting memo fee structures');
2104 l_memo_fee_structures := lns_fee_engine.getFeeStructures(p_loan_id => l_loan_id
2105 ,p_fee_category => 'MEMO'
2106 ,p_fee_type => null
2107 ,p_installment => p_installment
2108 ,p_phase => l_phase
2109 ,p_fee_id => null);
2110 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': memo fee structures count = ' || l_memo_fee_structures.count);
2111
2112 l_memo_fees_tbl.delete;
2113 if l_memo_fee_structures.count > 0 then
2114 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calling calculateFees for memo fees...');
2115 lns_fee_engine.calculateFees(p_loan_id => l_loan_id
2116 ,p_installment => p_installment
2117 ,p_fee_basis_tbl => l_fee_basis_tbl
2118 ,p_fee_structures => l_memo_fee_structures
2119 ,x_fees_tbl => l_memo_fees_tbl
2120 ,x_return_status => l_return_status
2121 ,x_msg_count => l_msg_count
2122 ,x_msg_data => l_msg_data);
2123
2124 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': calculated memo fees = ' || l_memo_fees_tbl.count);
2125 end if;
2126
2127 FOR k in 1..l_memo_fees_tbl.count LOOP
2128 l_all_fees_tbl(allFeeCount + k) := l_memo_fees_tbl(k);
2129 END LOOP;
2130
2131 allFeeCount := l_all_fees_tbl.count;
2132 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Total Scheduled Unbilled fees are ' || allFeeCount);
2133
2134
2135 -- funding fees
2136 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || '- getting funding fee structures');
2137 l_funding_fee_structures := lns_fee_engine.getFeeStructures(p_loan_id => l_loan_id
2138 ,p_fee_category => 'EVENT'
2139 ,p_fee_type => 'EVENT_FUNDING'
2140 ,p_installment => p_installment
2141 ,p_phase => l_phase
2142 ,p_fee_id => null);
2143
2144 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': funding fee structures count is ' || l_funding_fee_structures.count);
2145
2146 -- filtering out fees based on p_based_on_terms
2147 n := 0;
2148 for m in 1..l_funding_fee_structures.count loop
2149
2150 l_schd := null;
2151 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' l_loan_id = ' || l_loan_id);
2152 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' fee_id = ' || l_funding_fee_structures(m).FEE_ID);
2153 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' p_installment = ' || p_installment);
2154 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' l_phase = ' || l_phase);
2155 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' disb_header_id = ' || l_funding_fee_structures(m).disb_header_id);
2156
2157 OPEN cur_fee_schd_inst_exist(l_loan_id, l_funding_fee_structures(m).fee_id, p_installment, l_phase, l_funding_fee_structures(m).disb_header_id);
2158 FETCH cur_fee_schd_inst_exist into l_schd;
2159 CLOSE cur_fee_schd_inst_exist;
2160
2161 if l_schd is null then
2162 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' Fee ' || l_funding_fee_structures(m).FEE_ID || ' is not scheduled yet');
2163 n := n + 1;
2164 l_new_fund_fee_structures(n) := l_funding_fee_structures(m);
2165 else
2166 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' Fee ' || l_funding_fee_structures(m).FEE_ID || ' is already scheduled for installment '||p_installment);
2167 end if;
2168 end loop;
2169
2170 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Unscheduled funding fee structures count is ' || l_new_fund_fee_structures.count);
2171
2172 l_funding_fees_tbl.delete;
2173 if l_new_fund_fee_structures.count > 0 then
2174 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Calling calculateFees for funding fees...');
2175 lns_fee_engine.calculateFees(p_loan_id => l_loan_id
2176 ,p_installment => p_installment
2177 ,p_fee_basis_tbl => l_fee_basis_tbl
2178 ,p_fee_structures => l_new_fund_fee_structures
2179 ,x_fees_tbl => l_funding_fees_tbl
2180 ,x_return_status => l_return_status
2181 ,x_msg_count => l_msg_count
2182 ,x_msg_data => l_msg_data);
2183 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': calculated funding fees ' || l_funding_fees_tbl.count);
2184 end if;
2185
2186 FOR k in 1..l_funding_fees_tbl.count LOOP
2187 l_all_fees_tbl(allFeeCount + k) := l_funding_fees_tbl(k);
2188 END LOOP;
2189
2190 allFeeCount := l_all_fees_tbl.count;
2191 END IF; -- if based_on_terms = 'CURRENT'
2192
2193 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ': Total Fees for installment '||p_installment||' are '|| allFeeCount);
2194
2195 x_fees_tbl := l_all_fees_tbl;
2196
2197 -- ---------------------------------------------------------------------
2198 -- End of API body
2199 -- ---------------------------------------------------------------------
2200 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2201
2202 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
2203
2204 EXCEPTION
2205 WHEN FND_API.G_EXC_ERROR THEN
2206 x_return_status := FND_API.G_RET_STS_ERROR;
2207 x_msg_count := l_msg_count;
2208 x_msg_data := l_msg_data;
2209 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2210 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2211 ROLLBACK TO getFeeDetails;
2212
2213 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2214 x_return_status := FND_API.G_RET_STS_ERROR;
2215 x_msg_count := l_msg_count;
2216 x_msg_data := l_msg_data;
2217 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2218 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2219 ROLLBACK TO getFeeDetails;
2220
2221 WHEN OTHERS THEN
2222 x_return_status := FND_API.G_RET_STS_ERROR;
2223 x_msg_count := l_msg_count;
2224 x_msg_data := l_msg_data;
2225 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2226 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2227 ROLLBACK TO getFeeDetails;
2228
2229 END getFeeDetails;
2230
2231
2232 /*=========================================================================
2233 || PUBLIC PROCEDURE updateFeeSchedule
2234 ||
2235 || DESCRIPTION
2236 || Overview: this procedure will validate and update a table of fees to
2237 || the fee_schedule table
2238 ||
2239 || PSEUDO CODE/LOGIC
2240 ||
2241 || PARAMETERS
2242 || Parameter: p_fees_tbl => represents a table of fees
2243 || p_loan_id => loan_id
2244 ||
2245 || Return value:
2246 || standard
2247 || KNOWN ISSUES
2248 ||
2249 || NOTES
2250 ||
2251 || MODIFICATION HISTORY
2252 || Date Author Description of Changes
2253 || 1/7/2005 8:40PM raverma Created
2254 ||
2255 *=======================================================================*/
2256 procedure updateFeeSchedule(p_init_msg_list in varchar2
2257 ,p_commit in varchar2
2258 ,p_loan_id in number
2259 ,p_fees_tbl IN LNS_FEE_ENGINE.FEE_CALC_TBL
2260 ,x_return_status out nocopy varchar2
2261 ,x_msg_count out nocopy number
2262 ,x_msg_data out nocopy varchar2)
2263 is
2264 l_api_name varchar2(25);
2265 l_return_status VARCHAR2(1);
2266 l_msg_count NUMBER;
2267 l_msg_data VARCHAR2(32767);
2268
2269 l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
2270 l_fee_schedule_id NUMBER;
2271 i number;
2272
2273 cursor c_fee_schedule_id (p_loan_id number, p_fee_id number) is
2274 select fee_schedule_id
2275 from lns_fee_schedules
2276 where loan_id = p_loan_id
2277 and fee_id = p_fee_id
2278 and billed_flag = 'N'
2279 and active_flag = 'Y';
2280
2281 l_precision number;
2282 l_intervals number;
2283 l_phase varchar2(30);
2284
2285 cursor c_phase(p_loan_id number) is
2286 select nvl(current_phase, 'TERM')
2287 from lns_loan_headers
2288 where loan_id = p_loan_id;
2289
2290 cursor c_precision (p_loan_id number)
2291 is
2292 SELECT fndc.precision
2293 FROM lns_loan_headers lnh
2294 ,fnd_currencies fndc
2295 WHERE lnh.loan_id = p_loan_id
2296 and lnh.loan_currency = fndc.currency_code;
2297 begin
2298
2299 l_api_name := 'updateFeeSchedule';
2300 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2301
2302 -- Standard Start of API savepoint
2303 SAVEPOINT updateFeeSchedule;
2304
2305 -- Initialize message list IF p_init_msg_list is set to TRUE.
2306 IF FND_API.to_Boolean(p_init_msg_list) THEN
2307 FND_MSG_PUB.initialize;
2308 END IF;
2309
2310 -- Initialize API return status to SUCCESS
2311 x_return_status := FND_API.G_RET_STS_SUCCESS;
2312
2313 -- ---------------------------------------------------------------------
2314 -- Api body
2315 -- ---------------------------------------------------------------------
2316 -- initialize any variables here
2317
2318 open c_precision(p_loan_id);
2319 fetch c_precision into l_precision;
2320 close c_precision;
2321
2322 lns_utility_pub.validate_any_id(p_api_version => 1.0
2323 ,p_init_msg_list => p_init_msg_list
2324 ,x_msg_count => l_msg_count
2325 ,x_msg_data => l_msg_data
2326 ,x_return_status => l_return_status
2327 ,p_col_id => p_loan_id
2328 ,p_col_name => 'LOAN_ID'
2329 ,p_table_name => 'LNS_LOAN_HEADERS_ALL');
2330
2331 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2332 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2333 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
2334 FND_MESSAGE.SET_TOKEN('VALUE', p_loan_ID);
2335 FND_MSG_PUB.ADD;
2336 RAISE FND_API.G_EXC_ERROR;
2337 end if;
2338
2339 open c_phase(p_loan_id);
2340 fetch c_phase into l_phase;
2341 close c_phase;
2342
2343 l_loan_details := lns_financials.getLoanDetails(p_loan_id, 'CURRENT', l_phase);
2344 i := p_fees_tbl.count;
2345 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - found ' || i || 'fee structures');
2346
2347 -- validate all structures
2348 for k in 1..i loop
2349
2350 if p_fees_tbl(k).fee_installment > -1 then
2351
2352 -- first validation as per june : do not add fees far into the future
2353 if p_fees_tbl(k).fee_installment > l_loan_details.LAST_INSTALLMENT_BILLED + 1 then
2354 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_INSTALLMENT_ERROR3');
2355 FND_MSG_PUB.ADD;
2356 RAISE FND_API.G_EXC_ERROR;
2357 end if;
2358
2359
2360 if p_fees_tbl(k).fee_installment < l_loan_details.LAST_INSTALLMENT_BILLED then
2361 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_INSTALLMENT_ERROR1');
2362 FND_MSG_PUB.ADD;
2363 RAISE FND_API.G_EXC_ERROR;
2364 end if;
2365
2366 if p_fees_tbl(k).fee_installment > l_loan_details.NUMBER_INSTALLMENTS then
2367 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_INSTALLMENT_ERROR2');
2368 FND_MSG_PUB.ADD;
2369 RAISE FND_API.G_EXC_ERROR;
2370 end if;
2371
2372 end if;
2373
2374 if p_fees_tbl(k).FEE_AMOUNT is null or p_fees_tbl(k).FEE_AMOUNT < 0 then
2375 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_FEE_AMOUNT');
2376 FND_MSG_PUB.ADD;
2377 RAISE FND_API.G_EXC_ERROR;
2378 end if;
2379
2380 if p_fees_tbl(k).FEE_ID is not null then
2381 lns_utility_pub.validate_any_id(p_api_version => 1.0
2382 ,p_init_msg_list => p_init_msg_list
2383 ,x_msg_count => l_msg_count
2384 ,x_msg_data => l_msg_data
2385 ,x_return_status => l_return_status
2386 ,p_col_id => p_fees_tbl(k).FEE_ID
2387 ,p_col_name => 'FEE_ID'
2388 ,p_table_name => 'LNS_FEES');
2389
2390 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2391 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2392 FND_MESSAGE.SET_TOKEN('PARAMETER', 'FEE_ID');
2393 FND_MESSAGE.SET_TOKEN('VALUE', p_fees_tbl(k).FEE_ID);
2394 FND_MSG_PUB.ADD;
2395 RAISE FND_API.G_EXC_ERROR;
2396 end if;
2397
2398 end if;
2399
2400 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Fee_schedule_id is '||p_fees_tbl(k).FEE_SCHEDULE_ID);
2401
2402 if p_fees_tbl(k).FEE_SCHEDULE_ID is null then
2403 -- we have an origination fee
2404 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'p_loan_id : '||p_loan_id||' and fee_id : '||p_fees_tbl(k).FEE_ID);
2405 open c_fee_schedule_id(p_loan_id, p_fees_tbl(k).FEE_ID);
2406 fetch c_fee_schedule_id into l_fee_schedule_id;
2407 close c_fee_schedule_id;
2408
2409 else
2410 l_fee_schedule_id := p_fees_tbl(k).FEE_SCHEDULE_ID;
2411
2412 end if;
2413
2414 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Fee_schedule_id is '||p_fees_tbl(k).FEE_SCHEDULE_ID);
2415
2416 -- fee structure has been validated write to the fee schedule
2417 LNS_FEE_SCHEDULES_PKG.UPDATE_ROW(P_FEE_SCHEDULE_ID => l_fee_schedule_id
2418 ,P_FEE_ID => p_fees_tbl(k).FEE_ID
2419 ,P_LOAN_ID => p_loan_id
2420 ,P_FEE_AMOUNT => round(p_fees_tbl(k).FEE_AMOUNT,l_precision)
2421 ,P_FEE_INSTALLMENT => p_fees_tbl(k).FEE_INSTALLMENT
2422 ,P_FEE_DESCRIPTION => p_fees_tbl(k).FEE_DESCRIPTION
2423 ,P_ACTIVE_FLAG => p_fees_tbl(k).ACTIVE_FLAG --'Y'
2424 ,P_BILLED_FLAG => p_fees_tbl(k).BILLED_FLAG --'N'
2425 ,P_FEE_WAIVABLE_FLAG => p_fees_tbl(k).FEE_WAIVABLE_FLAG
2426 ,P_WAIVED_AMOUNT => null
2427 ,P_LAST_UPDATED_BY => lns_utility_pub.last_updated_by
2428 ,P_LAST_UPDATE_DATE => lns_utility_pub.last_update_date
2429 ,P_LAST_UPDATE_LOGIN => null
2430 ,P_PROGRAM_ID => null
2431 ,P_REQUEST_ID => null
2432 ,P_OBJECT_VERSION_NUMBER => 1
2433 ,P_DISB_HEADER_ID => p_fees_tbl(k).DISB_HEADER_ID
2434 ,P_PHASE => p_fees_tbl(k).PHASE);
2435 end loop;
2436 -- ---------------------------------------------------------------------
2437 -- End of API body
2438 -- ---------------------------------------------------------------------
2439
2440 IF FND_API.to_Boolean(p_commit) THEN
2441 COMMIT WORK;
2442 END IF;
2443
2444 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2445
2446 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
2447
2448 EXCEPTION
2449 WHEN FND_API.G_EXC_ERROR THEN
2450 x_return_status := FND_API.G_RET_STS_ERROR;
2451 x_msg_count := l_msg_count;
2452 x_msg_data := l_msg_data;
2453 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2454 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2455 ROLLBACK TO updateFeeSchedule;
2456
2457 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2458 x_return_status := FND_API.G_RET_STS_ERROR;
2459 x_msg_count := l_msg_count;
2460 x_msg_data := l_msg_data;
2461 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2462 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2463 ROLLBACK TO updateFeeSchedule;
2464
2465 WHEN OTHERS THEN
2466 x_return_status := FND_API.G_RET_STS_ERROR;
2467 x_msg_count := l_msg_count;
2468 x_msg_data := l_msg_data;
2469 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2470 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2471 ROLLBACK TO updateFeeSchedule;
2472
2473 end updateFeeSchedule;
2474
2475 /*=========================================================================
2476 || PUBLIC PROCEDURE writeFees
2477 ||
2478 || DESCRIPTION
2479 || Overview: this procedure will validate and write a table of fees to
2480 || the fee_schedule table
2481 ||
2482 || PSEUDO CODE/LOGIC
2483 ||
2484 || PARAMETERS
2485 || Parameter: p_fee_structure_tbl => represents a table of fees
2486 || p_loan_id => loan_id
2487 ||
2488 || Return value:
2489 || standard
2490 || KNOWN ISSUES
2491 ||
2492 || NOTES
2493 ||
2494 || MODIFICATION HISTORY
2495 || Date Author Description of Changes
2496 || 12/1/2004 8:40PM raverma Created
2497 || 07/21/2005 raverma support OPEN phase
2498 *=======================================================================*/
2499 procedure writeFeeSchedule(p_init_msg_list in varchar2
2500 ,p_commit in varchar2
2501 ,p_loan_id in number
2502 ,p_fees_tbl IN OUT NOCOPY LNS_FEE_ENGINE.FEE_CALC_TBL
2503 ,x_return_status out nocopy varchar2
2504 ,x_msg_count out nocopy number
2505 ,x_msg_data out nocopy varchar2)
2506
2507 is
2508 l_api_name varchar2(25);
2509 l_return_status VARCHAR2(1);
2510 l_msg_count NUMBER;
2511 l_msg_data VARCHAR2(32767);
2512
2513 l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
2514 l_fee_schedule_id NUMBER;
2515 i number;
2516 l_phase varchar2(30);
2517
2518 cursor c_phase(p_loan_id number) is
2519 select nvl(current_phase, 'TERM')
2520 from lns_loan_headers_all
2521 where loan_id = p_loan_id;
2522
2523 begin
2524
2525 l_api_name := 'writeFeeSchedule';
2526 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2527
2528 -- Standard Start of API savepoint
2529 SAVEPOINT writeFeeSchedule;
2530
2531 -- Initialize message list IF p_init_msg_list is set to TRUE.
2532 IF FND_API.to_Boolean(p_init_msg_list) THEN
2533 FND_MSG_PUB.initialize;
2534 END IF;
2535
2536 -- Initialize API return status to SUCCESS
2537 x_return_status := FND_API.G_RET_STS_SUCCESS;
2538
2539 -- ---------------------------------------------------------------------
2540 -- Api body
2541 -- ---------------------------------------------------------------------
2542 -- initialize any variables here
2543 lns_utility_pub.validate_any_id(p_api_version => 1.0
2544 ,p_init_msg_list => p_init_msg_list
2545 ,x_msg_count => l_msg_count
2546 ,x_msg_data => l_msg_data
2547 ,x_return_status => l_return_status
2548 ,p_col_id => p_loan_id
2549 ,p_col_name => 'LOAN_ID'
2550 ,p_table_name => 'LNS_LOAN_HEADERS_ALL');
2551
2552 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2553 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2554 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
2555 FND_MESSAGE.SET_TOKEN('VALUE', p_loan_ID);
2556 FND_MSG_PUB.ADD;
2557 RAISE FND_API.G_EXC_ERROR;
2558 end if;
2559
2560 open c_phase(p_loan_id);
2561 fetch c_phase into l_phase;
2562 close c_phase;
2563
2564 l_loan_details := lns_financials.getLoanDetails(p_loan_id, 'CURRENT', l_phase);
2565 i := p_fees_tbl.count;
2566 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - found ' || i || 'fee structures');
2567
2568 -- validate all structures
2569 for k in 1..i loop
2570
2571 -- bypass this validation temporarily until more time for disbursement fees
2572 if p_fees_tbl(k).disb_header_id is null then
2573
2574 if p_fees_tbl(k).fee_installment > -1 then
2575
2576 -- first validation as per june : do not add fees far into the future
2577 if p_fees_tbl(k).fee_installment > l_loan_details.LAST_INSTALLMENT_BILLED + 1 then
2578 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_INSTALLMENT_ERROR3');
2579 FND_MSG_PUB.ADD;
2580 RAISE FND_API.G_EXC_ERROR;
2581 end if;
2582
2583 if p_fees_tbl(k).fee_installment < l_loan_details.LAST_INSTALLMENT_BILLED then
2584 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_INSTALLMENT_ERROR1');
2585 FND_MSG_PUB.ADD;
2586 RAISE FND_API.G_EXC_ERROR;
2587 end if;
2588
2589 if p_fees_tbl(k).fee_installment > l_loan_details.NUMBER_INSTALLMENTS then
2590 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_INSTALLMENT_ERROR2');
2591 FND_MSG_PUB.ADD;
2592 RAISE FND_API.G_EXC_ERROR;
2593 end if;
2594
2595 end if;
2596
2597 if p_fees_tbl(k).FEE_AMOUNT is null or p_fees_tbl(k).FEE_AMOUNT < 0 then
2598 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_FEE_AMOUNT');
2599 FND_MSG_PUB.ADD;
2600 RAISE FND_API.G_EXC_ERROR;
2601 end if;
2602
2603 if p_fees_tbl(k).FEE_ID is not null then
2604 lns_utility_pub.validate_any_id(p_api_version => 1.0
2605 ,p_init_msg_list => p_init_msg_list
2606 ,x_msg_count => l_msg_count
2607 ,x_msg_data => l_msg_data
2608 ,x_return_status => l_return_status
2609 ,p_col_id => p_fees_tbl(k).FEE_ID
2610 ,p_col_name => 'FEE_ID'
2611 ,p_table_name => 'LNS_FEES');
2612
2613 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2614 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
2615 FND_MESSAGE.SET_TOKEN('PARAMETER', 'FEE_ID');
2616 FND_MESSAGE.SET_TOKEN('VALUE', p_fees_tbl(k).FEE_ID);
2617 FND_MSG_PUB.ADD;
2618 RAISE FND_API.G_EXC_ERROR;
2619 end if;
2620
2621 end if;
2622 else
2623 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - validation bypassed');
2624 end if; -- disb_header_id
2625 --
2626 select lns_fee_schedule_s.nextval
2627 into l_fee_schedule_id
2628 from dual;
2629
2630 -- fee structure has been validated write to the fee schedule
2631 LNS_FEE_SCHEDULES_PKG.INSERT_ROW(X_FEE_SCHEDULE_ID => l_fee_schedule_id
2632 ,P_FEE_ID => p_fees_tbl(k).FEE_ID
2633 ,P_LOAN_ID => p_loan_id
2634 ,P_FEE_AMOUNT => round(p_fees_tbl(k).FEE_AMOUNT, l_loan_details.currency_precision)
2635 ,P_FEE_INSTALLMENT => p_fees_tbl(k).FEE_INSTALLMENT
2636 ,P_FEE_DESCRIPTION => p_fees_tbl(k).FEE_DESCRIPTION
2637 ,P_ACTIVE_FLAG => 'Y'
2638 ,P_BILLED_FLAG => 'N'
2639 ,P_FEE_WAIVABLE_FLAG => p_fees_tbl(k).FEE_WAIVABLE_FLAG
2640 ,P_WAIVED_AMOUNT => null
2641 ,P_CREATED_BY => lns_utility_pub.created_by
2642 ,P_CREATION_DATE => lns_utility_pub.creation_date
2643 ,P_LAST_UPDATED_BY => lns_utility_pub.last_updated_by
2644 ,P_LAST_UPDATE_DATE => lns_utility_pub.last_update_date
2645 ,P_LAST_UPDATE_LOGIN => null
2646 ,P_PROGRAM_ID => null
2647 ,P_REQUEST_ID => null
2648 ,P_OBJECT_VERSION_NUMBER => 1
2649 ,P_DISB_HEADER_ID => p_fees_tbl(k).DISB_HEADER_ID
2650 ,P_PHASE => p_fees_tbl(k).PHASE);
2651
2652 p_fees_tbl(k).fee_schedule_id := l_fee_schedule_id;
2653
2654 end loop;
2655
2656 -- ---------------------------------------------------------------------
2657 -- End of API body
2658 -- ---------------------------------------------------------------------
2659 IF FND_API.to_Boolean(p_commit) THEN
2660 COMMIT WORK;
2661 END IF;
2662
2663 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2664
2665 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
2666
2667 EXCEPTION
2668 WHEN FND_API.G_EXC_ERROR THEN
2669 x_return_status := FND_API.G_RET_STS_ERROR;
2670 x_msg_count := l_msg_count;
2671 x_msg_data := l_msg_data;
2672 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2673 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2674 ROLLBACK TO writeFeeSchedule;
2675
2676 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2677 x_return_status := FND_API.G_RET_STS_ERROR;
2678 x_msg_count := l_msg_count;
2679 x_msg_data := l_msg_data;
2680 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2681 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2682 ROLLBACK TO writeFeeSchedule;
2683
2684 WHEN OTHERS THEN
2685 x_return_status := FND_API.G_RET_STS_ERROR;
2686 x_msg_count := l_msg_count;
2687 x_msg_data := l_msg_data;
2688 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2689 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2690 ROLLBACK TO writeFeeSchedule;
2691
2692 end writeFeeSchedule;
2693
2694 /*=========================================================================
2695 || PUBLIC procedure processFees
2696 ||
2697 || DESCRIPTION
2698 || Overview: this procedure will be the hook for the application to
2699 || get, calculate and write fees to the fee schedule
2700 ||
2701 || PSEUDO CODE/LOGIC
2702 ||
2703 || PARAMETERS
2704 || Parameter: p_installment_number=> installment number to process
2705 || p_loan_header_rec => header level infor about loan
2706 || p_amortization_rec => installment level info about loan
2707 || p_fee_structures => TABLE of Fee_Category/Fee_Types to process
2708 || p_loan_id => loan_id
2709 || x_fees_tbl => table of records inserted
2710 ||
2711 || Return value:
2712 || standard
2713 || KNOWN ISSUES
2714 ||
2715 || NOTES
2716 ||
2717 || MODIFICATION HISTORY
2718 || Date Author Description of Changes
2719 || 12/1/2004 8:40PM raverma Created
2720 *=======================================================================*/
2721 procedure processFees(p_init_msg_list in varchar2
2722 ,p_commit in varchar2
2723 ,p_loan_id in number
2724 ,p_installment_number in number
2725 ,p_fee_basis_tbl in LNS_FEE_ENGINE.FEE_BASIS_TBL
2726 ,p_fee_structures in LNS_FEE_ENGINE.FEE_STRUCTURE_TBL
2727 ,x_fees_tbl OUT NOCOPY LNS_FEE_ENGINE.FEE_CALC_TBL
2728 ,x_return_status out nocopy varchar2
2729 ,x_msg_count out nocopy number
2730 ,x_msg_data out nocopy varchar2)
2731 is
2732
2733 l_api_name varchar2(25);
2734 l_return_status VARCHAR2(1);
2735 l_msg_count NUMBER;
2736 l_msg_data VARCHAR2(32767);
2737
2738 l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
2739 l_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
2740 l_fee_schedule_id NUMBER;
2741 l_fee_calc_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
2742 l_fee_category varchar2(30);
2743 l_fee_type varchar2(30);
2744 i number;
2745 l_processed_fees number;
2746 l_billed_flag VARCHAR2(1);
2747 writeCount NUMBER;
2748 updateCount NUMBER;
2749 l_phase VARCHAR2(30);
2750 l_write_fee_calc_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
2751 l_update_fee_calc_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
2752 l_inserted_fees number;
2753
2754 cursor c_processed(c_loan_id number,
2755 c_installment number,
2756 c_category varchar2,
2757 c_type varchar2,
2758 c_phase varchar2,
2759 C_BILLING_OPTION VARCHAR2) is
2760 select nvl(sum(fee_amount), 0)
2761 from lns_fee_schedules sched
2762 ,lns_fees fees
2763 where sched.loan_id = c_loan_id
2764 and sched.fee_id = fees.fee_id
2765 and sched.fee_installment = c_installment
2766 and sched.active_flag = 'Y'
2767 and sched.billed_flag = 'Y' -- deduce this based on parent records
2768 and fees.fee_category = c_category
2769 and fees.fee_type = c_type
2770 and nvl(sched.phase, 'TERM') = c_phase
2771 and fees.BILLING_OPTION = nvl(C_BILLING_OPTION, fees.BILLING_OPTION)
2772 and (exists
2773 (select 'X'
2774 from lns_amortization_scheds am
2775 ,lns_amortization_lines lines
2776 where am.loan_id = c_loan_id
2777 and am.amortization_schedule_id = lines.amortization_schedule_id
2778 and lines.fee_schedule_id = sched.fee_schedule_id
2779 and NVL(am.reversed_flag, 'N') = 'N'
2780 and am.payment_number = c_installment));
2781 begin
2782
2783 l_api_name := 'processFees';
2784 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2785
2786 -- Standard Start of API savepoint
2787 SAVEPOINT processFees;
2788
2789 -- Initialize message list IF p_init_msg_list is set to TRUE.
2790 IF FND_API.to_Boolean(p_init_msg_list) THEN
2791 FND_MSG_PUB.initialize;
2792 END IF;
2793
2794 -- Initialize API return status to SUCCESS
2795 x_return_status := FND_API.G_RET_STS_SUCCESS;
2796 l_processed_fees:= 0;
2797 l_inserted_fees := 0;
2798
2799 -- ---------------------------------------------------------------------
2800 -- Api body
2801 -- ---------------------------------------------------------------------
2802 -- initialize any variables here
2803 /* encapsulated API
2804 1. getFeeStructures for a particular event(s)
2805 2. calculate for installment
2806 3. write to schedule
2807 */
2808 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_fee_structures.count = ' || p_fee_structures.count);
2809 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_fee_basis_tbl.count = ' || p_fee_basis_tbl.count);
2810
2811 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_loan_id = ' || p_loan_id);
2812 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - p_installment_number = ' || p_installment_number);
2813 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee_category = ' || p_fee_structures(1).fee_category);
2814 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee_type = ' || p_fee_structures(1).fee_type);
2815 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - phase = ' || p_fee_structures(1).phase);
2816 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - FEE_BILLING_OPTION = ' || p_fee_structures(1).FEE_BILLING_OPTION);
2817
2818 open c_processed(p_loan_id,
2819 p_installment_number,
2820 p_fee_structures(1).fee_category,
2821 p_fee_structures(1).fee_type,
2822 p_fee_structures(1).phase,
2823 p_fee_structures(1).FEE_BILLING_OPTION);
2824 fetch c_processed into l_processed_fees;
2825 close c_processed;
2826
2827 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fees already processed ' || l_processed_fees);
2828
2829 if l_processed_fees = 0 then
2830 for i in 1..p_fee_structures.count loop
2831
2832 l_fee_category := p_fee_structures(i).fee_category;
2833 l_fee_type := p_fee_structures(i).fee_type;
2834 l_phase := nvl(p_fee_structures(i).phase, 'TERM');
2835
2836 if p_fee_structures(i).fee_category is null AND p_fee_structures(i).fee_type is null then
2837 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_CAT_TYPE_MISSING');
2838 FND_MSG_PUB.ADD;
2839 RAISE FND_API.G_EXC_ERROR;
2840 end if;
2841
2842 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee category ' || l_fee_category);
2843 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee type ' || l_fee_type);
2844 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - phase ' || l_phase);
2845
2846 l_fee_structures := lns_fee_engine.getFeeStructures(p_loan_id => p_loan_id
2847 ,p_fee_category => l_fee_category
2848 ,p_fee_type => l_fee_type
2849 ,p_installment => p_installment_number
2850 ,p_phase => l_phase
2851 ,p_fee_id => null
2852 ,p_billing_option => p_fee_structures(i).FEE_BILLING_OPTION);
2853 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee structures count is ' || l_fee_structures.count);
2854
2855 lns_fee_engine.calculateFees(p_loan_id => p_loan_id
2856 ,p_fee_basis_tbl => p_fee_basis_tbl
2857 ,p_installment => p_installment_number
2858 ,p_fee_structures => l_fee_structures
2859 ,x_fees_tbl => l_fee_calc_tbl
2860 ,x_return_status => l_return_status
2861 ,x_msg_count => l_msg_count
2862 ,x_msg_data => l_msg_data);
2863 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2864 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_CALCULATION_FAILURE');
2865 FND_MSG_PUB.ADD;
2866 RAISE FND_API.G_EXC_ERROR;
2867 end if;
2868
2869 -- Bug#8830789 - To restrict the insertion of duplicate feeSchedule record for the same FeeId and installment of a loan.
2870 -- Delete the existed record and insert the new record
2871 -- Note:- Manual Fee doesn't be scheduled so no ManualFee records exist in FeeSchedule table.
2872
2873 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee calc table count is ' || l_fee_calc_tbl.count);
2874 writeCount := 0;
2875 updateCount := 0;
2876 l_write_fee_calc_tbl.delete;
2877 l_update_fee_calc_tbl.delete;
2878
2879 FOR f in 1..l_fee_calc_tbl.count LOOP
2880 l_fee_schedule_id := NULL;
2881
2882 BEGIN
2883 SELECT fee_schedule_id, billed_flag INTO l_fee_schedule_id, l_billed_flag
2884 FROM lns_fee_schedules
2885 WHERE loan_id = p_loan_id
2886 AND fee_id = l_fee_calc_tbl(f).fee_id
2887 AND fee_installment = l_fee_calc_tbl(f).fee_installment
2888 AND nvl(phase, 'TERM') = l_fee_calc_tbl(f).phase
2889 AND active_flag = 'Y'
2890 AND billed_flag = 'N';
2891 EXCEPTION
2892 WHEN NO_DATA_FOUND THEN
2893 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - No records in feeShcd table for ' ||l_fee_calc_tbl(f).FEE_ID||' at installment '|| l_fee_calc_tbl(f).fee_installment);
2894 l_fee_schedule_id := NULL;
2895 END;
2896
2897 IF l_fee_schedule_id IS NULL THEN
2898 -- Insert the FeeSchedule Record
2899
2900 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Inserting calculated fee with fee_id as ' ||l_fee_calc_tbl(f).FEE_ID);
2901 writeCount := writeCount + 1;
2902 l_write_fee_calc_tbl(writeCount) := l_fee_calc_tbl(f);
2903
2904 ELSE
2905 -- Update the existed FeeSchedule record
2906
2907 IF l_billed_flag = 'Y' THEN
2908 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' -The feeSchd '||l_fee_schedule_id||' is already billed. So dont update the record' );
2909 ELSE
2910 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Updating calculated fee for fee_schedule_id '||l_fee_schedule_id);
2911 updateCount := updateCount + 1;
2912 l_update_fee_calc_tbl(updateCount) := l_fee_calc_tbl(f);
2913 l_update_fee_calc_tbl(updateCount).FEE_SCHEDULE_ID := l_fee_schedule_id;
2914 END IF;
2915 END IF;
2916 END LOOP;
2917
2918 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' Total No of New Records are '||writeCount );
2919 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' Total No of Updatable Records are '||updateCount );
2920
2921 IF (writeCount > 0) THEN
2922 lns_fee_engine.writeFeeSchedule(p_init_msg_list => p_init_msg_list
2923 ,p_commit => p_commit
2924 ,p_loan_id => p_loan_id
2925 ,p_fees_tbl => l_write_fee_calc_tbl
2926 ,x_return_status => l_return_status
2927 ,x_msg_count => l_msg_count
2928 ,x_msg_data => l_msg_data);
2929 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2930 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_WRITE_FAILURE');
2931 FND_MSG_PUB.ADD;
2932 RAISE FND_API.G_EXC_ERROR;
2933 end if;
2934
2935 for k in 1..l_write_fee_calc_tbl.count loop
2936 l_inserted_fees := l_inserted_fees + 1;
2937 x_fees_tbl(l_inserted_fees) := l_write_fee_calc_tbl(k);
2938 end loop;
2939
2940 END IF;
2941
2942 IF (updateCount > 0) THEN
2943 lns_fee_engine.updateFeeSchedule(p_init_msg_list => p_init_msg_list
2944 ,p_commit => p_commit
2945 ,p_loan_id => p_loan_id
2946 ,p_fees_tbl => l_update_fee_calc_tbl
2947 ,x_return_status => l_return_status
2948 ,x_msg_count => l_msg_count
2949 ,x_msg_data => l_msg_data);
2950 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
2951 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_UPDATE_FAILURE');
2952 FND_MSG_PUB.ADD;
2953 RAISE FND_API.G_EXC_ERROR;
2954 end if;
2955 END IF;
2956 end loop;
2957 end if;
2958
2959 -- ---------------------------------------------------------------------
2960 -- End of API body
2961 -- ---------------------------------------------------------------------
2962
2963 IF FND_API.to_Boolean(p_commit) THEN
2964 COMMIT WORK;
2965 END IF;
2966
2967 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2968
2969 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
2970
2971 EXCEPTION
2972 WHEN FND_API.G_EXC_ERROR THEN
2973 x_return_status := FND_API.G_RET_STS_ERROR;
2974 x_msg_count := l_msg_count;
2975 x_msg_data := l_msg_data;
2976 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2977 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2978 ROLLBACK TO processFees;
2979
2980 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2981 x_return_status := FND_API.G_RET_STS_ERROR;
2982 x_msg_count := l_msg_count;
2983 x_msg_data := l_msg_data;
2984 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2985 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2986 ROLLBACK TO processFees;
2987
2988 WHEN OTHERS THEN
2989 x_return_status := FND_API.G_RET_STS_ERROR;
2990 x_msg_count := l_msg_count;
2991 x_msg_data := l_msg_data;
2992 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2993 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2994 ROLLBACK TO processFees;
2995
2996 end processFees;
2997
2998 /*=========================================================================
2999 || PUBLIC PROCEDURE waiveFee
3000 ||
3001 || DESCRIPTION
3002 || Overview: this procedure will waive a fee
3003 ||
3004 || PSEUDO CODE/LOGIC
3005 ||
3006 || PARAMETERS
3007 || Parameter: p_loan_id => loan_id
3008 || p_fee_schedule_id => pk
3009 || p_waive_amount => amount to be waived
3010 ||
3011 || Return value:
3012 || standard
3013 || KNOWN ISSUES
3014 ||
3015 || NOTES
3016 ||
3017 || MODIFICATION HISTORY
3018 || Date Author Description of Changes
3019 || 12/1/2004 8:40PM raverma Created
3020 ||
3021 *=======================================================================*/
3022 procedure waiveFee(p_init_msg_list in varchar2
3023 ,p_commit in varchar2
3024 ,p_loan_id in number
3025 ,p_fee_schedule_id in number
3026 ,p_waive_amount in number
3027 ,x_return_status out nocopy varchar2
3028 ,x_msg_count out nocopy number
3029 ,x_msg_data out nocopy varchar2)
3030 is
3031 l_api_name varchar2(25);
3032 l_return_status VARCHAR2(1);
3033 l_msg_count NUMBER;
3034 l_msg_data VARCHAR2(32767);
3035 l_fee_amount_remaining NUMBER;
3036 l_waived_amount NUMBER; --prior waived amount
3037
3038 -- so the only rule i can think of is the
3039 -- waived_amount cannot be > fee_amount less any previously waived amount
3040 cursor c_fee_waive_amount(c_fee_schedule_id number) is
3041 select sched.fee_amount - nvl(sched.waived_amount, 0) amount_remaining
3042 ,nvl(sched.waived_amount, 0) previously_waived
3043 from lns_fee_schedules sched
3044 where sched.loan_id = p_loan_id
3045 and sched.fee_schedule_id = c_fee_schedule_id
3046 and sched.fee_waivable_flag = 'Y'
3047 and sched.active_flag = 'Y'
3048 and sched.billed_flag = 'N';
3049
3050 begin
3051
3052 l_api_name := 'waiveFee';
3053 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
3054
3055 -- Standard Start of API savepoint
3056 SAVEPOINT waiveFee;
3057
3058 -- Initialize message list IF p_init_msg_list is set to TRUE.
3059 IF FND_API.to_Boolean(p_init_msg_list) THEN
3060 FND_MSG_PUB.initialize;
3061 END IF;
3062
3063 -- Initialize API return status to SUCCESS
3064 x_return_status := FND_API.G_RET_STS_SUCCESS;
3065
3066 -- ---------------------------------------------------------------------
3067 -- Api body
3068 -- ---------------------------------------------------------------------
3069 lns_utility_pub.validate_any_id(p_api_version => 1.0
3070 ,p_init_msg_list => 'T'
3071 ,x_msg_count => l_msg_count
3072 ,x_msg_data => l_msg_data
3073 ,x_return_status => l_return_status
3074 ,p_col_id => p_loan_id
3075 ,p_col_name => 'LOAN_ID'
3076 ,p_table_name => 'LNS_LOAN_HEADERS_ALL');
3077
3078 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3079 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
3080 FND_MESSAGE.SET_TOKEN('PARAMETER', 'LOAN_ID');
3081 FND_MESSAGE.SET_TOKEN('VALUE', p_loan_ID);
3082 FND_MSG_PUB.ADD;
3083 RAISE FND_API.G_EXC_ERROR;
3084 end if;
3085
3086 lns_utility_pub.validate_any_id(p_api_version => 1.0
3087 ,p_init_msg_list => 'T'
3088 ,x_msg_count => l_msg_count
3089 ,x_msg_data => l_msg_data
3090 ,x_return_status => l_return_status
3091 ,p_col_id => p_fee_schedule_id
3092 ,p_col_name => 'FEE_SCHEDULE_ID'
3093 ,p_table_name => 'LNS_FEE_SCHEDULES');
3094
3095 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3096 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
3097 FND_MESSAGE.SET_TOKEN('PARAMETER', 'FEE_SCHEDULE_ID');
3098 FND_MESSAGE.SET_TOKEN('VALUE', p_fee_schedule_ID);
3099 FND_MSG_PUB.ADD;
3100 RAISE FND_API.G_EXC_ERROR;
3101 end if;
3102
3103 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - amount to waive ' || p_waive_amount);
3104
3105 if p_waive_amount is null then
3106 FND_MESSAGE.SET_NAME('LNS', 'LNS_WAIVE_AMOUNT_INVALID');
3107 FND_MSG_PUB.ADD;
3108 RAISE FND_API.G_EXC_ERROR;
3109 else
3110 open c_fee_waive_amount(p_fee_schedule_id);
3111 fetch c_fee_waive_amount into l_fee_amount_remaining, l_waived_amount;
3112 close c_fee_waive_amount;
3113
3114 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - amount remain ' || l_fee_amount_remaining);
3115 if p_waive_amount > l_fee_amount_remaining or p_waive_amount < 0 then
3116 FND_MESSAGE.SET_NAME('LNS', 'LNS_WAIVE_AMOUNT_INVALID');
3117 FND_MSG_PUB.ADD;
3118 RAISE FND_API.G_EXC_ERROR;
3119 end if;
3120
3121 -- fee is valid and waive amount is valid. update the fee schedule
3122 -- check on updating object_version_number
3123 lns_fee_schedules_pkg.update_row(P_FEE_SCHEDULE_ID => p_fee_schedule_id
3124 ,P_FEE_ID => null
3125 ,P_LOAN_ID => p_loan_id
3126 ,P_FEE_AMOUNT => null
3127 ,P_FEE_INSTALLMENT => null
3128 ,P_FEE_DESCRIPTION => null
3129 ,P_ACTIVE_FLAG => null
3130 ,P_BILLED_FLAG => null
3131 ,P_FEE_WAIVABLE_FLAG => null
3132 ,P_WAIVED_AMOUNT => p_waive_amount + l_waived_amount
3133 ,P_LAST_UPDATED_BY => lns_utility_pub.last_updated_by
3134 ,P_LAST_UPDATE_DATE => lns_utility_pub.last_update_date
3135 ,P_LAST_UPDATE_LOGIN => lns_utility_pub.last_update_login
3136 ,P_PROGRAM_ID => null
3137 ,P_REQUEST_ID => null
3138 ,P_OBJECT_VERSION_NUMBER => null
3139 ,P_DISB_HEADER_ID => null
3140 ,P_PHASE => null);
3141
3142
3143 end if;
3144 -- ---------------------------------------------------------------------
3145 -- End of API body
3146 -- ---------------------------------------------------------------------
3147
3148 IF FND_API.to_Boolean(p_commit) THEN
3149 COMMIT WORK;
3150 END IF;
3151
3152 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3153
3154 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
3155
3156 EXCEPTION
3157 WHEN FND_API.G_EXC_ERROR THEN
3158 x_return_status := FND_API.G_RET_STS_ERROR;
3159 x_msg_count := l_msg_count;
3160 x_msg_data := l_msg_data;
3161 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3162 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
3163 ROLLBACK TO waiveFee;
3164
3165 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3166 x_return_status := FND_API.G_RET_STS_ERROR;
3167 x_msg_count := l_msg_count;
3168 x_msg_data := l_msg_data;
3169 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3170 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
3171 ROLLBACK TO waiveFee;
3172
3173 WHEN OTHERS THEN
3174 x_return_status := FND_API.G_RET_STS_ERROR;
3175 x_msg_count := l_msg_count;
3176 x_msg_data := l_msg_data;
3177 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3178 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
3179 ROLLBACK TO waiveFee;
3180
3181 end waiveFee;
3182
3183 /*=========================================================================
3184 ||
3185 || PUBLIC PROCEDURE getFeesTotal
3186 ||
3187 || DESCRIPTION
3188 || Overview: this procedure will get the sum of fees for a given loan
3189 || for a given fee category/type , billed flag and waived flag
3190 || PSEUDO CODE/LOGIC
3191 ||
3192 || PARAMETERS
3193 || Parameter: p_loan_id => loan_id
3194 ||
3195 || Return value:
3196 || sum of fees for a loan
3197 || KNOWN ISSUES
3198 ||
3199 || NOTES
3200 ||
3201 || MODIFICATION HISTORY
3202 || Date Author Description of Changes
3203 || 12/16/2004 8:40PM raverma Created
3204 ||
3205 *=======================================================================*/
3206 function getFeesTotal(p_loan_id in number
3207 ,p_fee_category in varchar2
3208 ,p_fee_type in varchar2
3209 ,p_billed_flag in varchar2
3210 ,p_waived_flag in varchar2) return number
3211 is
3212
3213 l_api_name varchar2(25);
3214 l_return_status VARCHAR2(1);
3215 l_msg_count NUMBER;
3216 l_msg_data VARCHAR2(32767);
3217 vPLSQL varchar2(1000);
3218 Type refCur is ref cursor;
3219 sql_Cur refCur;
3220
3221 l_total number;
3222
3223 begin
3224 l_api_name := 'getFeesTotal';
3225
3226 vPLSQL := 'SELECT decode(:p_waived_flag, ''Y'', nvl(sum(sched.waived_amount),0), nvl(sum(sched.fee_amount) - sum(sched.waived_amount),0))' ||
3227 ' from lns_fee_schedules sched ' ||
3228 --' ,lns_fee_assignments assign ' ||
3229 --' where assgn.fee_id = sched.fee_id ' ||
3230 ' Where sched.loan_id = :p_loan_id ' ||
3231 ' and sched.billed_flag = :p_billed_flag ' ||
3232 ' and sched.active_flag = ''Y'' ';
3233
3234 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee_category ' || p_fee_category);
3235 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee_tpye ' || p_fee_type);
3236 if p_fee_category is not null then
3237 vPLSQL := vPLSQL || ' AND fees.fee_category = ''' || p_fee_category || '''';
3238 end if;
3239
3240 if p_fee_type is not null then
3241 vPLSQL := vPLSQL || ' AND fees.fee_type = ''' || p_fee_type || '''';
3242 end if;
3243
3244 /*
3245 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - include p_memo_fees ' || p_memo_fees);
3246 if p_memo_fees = 'Y' then
3247 vPLSQL := vPLSQL || ' AND fees.fee_category = ''MEMO''';
3248 else
3249 vPLSQL := vPLSQL || ' AND fees.fee_category <> ''MEMO''';
3250 end if;
3251 */
3252 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - sql ' || vPLSQL);
3253 open sql_cur for
3254 vPLSQL
3255 using p_waived_flag, p_loan_id, p_billed_flag;
3256 fetch sql_cur into l_total;
3257 close sql_cur;
3258
3259 return l_total;
3260
3261 end getFeesTotal;
3262
3263 /*=========================================================================
3264 || PUBLIC PROCEDURE processLateFees
3265 ||
3266 || DESCRIPTION
3267 || Overview: this procedure will determine late fees, calculate them and
3268 || write them to the lns_fee_schedules
3269 || PSEUDO CODE/LOGIC
3270 ||
3271 || PARAMETERS
3272 || Parameter: p_loan_id => loan_id
3273 ||
3274 || Return value:
3275 || standard
3276 || KNOWN ISSUES
3277 ||
3278 || NOTES
3279 ||
3280 || MODIFICATION HISTORY
3281 || Date Author Description of Changes
3282 || 1/31/2005 8:40PM raverma Created
3283 ||
3284 *=======================================================================*/
3285 procedure processLateFees(p_init_msg_list in varchar2
3286 ,p_commit in varchar2
3287 ,p_loan_id in number
3288 ,p_phase in varchar2
3289 ,x_return_status out nocopy varchar2
3290 ,x_msg_count out nocopy number
3291 ,x_msg_data out nocopy varchar2)
3292 is
3293
3294 l_api_name varchar2(25);
3295 l_return_status VARCHAR2(1);
3296 l_msg_count NUMBER;
3297 l_msg_data VARCHAR2(32767);
3298
3299 l_late_fee_structures LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
3300 l_late_fee_structure LNS_FEE_ENGINE.FEE_STRUCTURE_TBL;
3301 l_fee_basis_tbl LNS_FEE_ENGINE.FEE_BASIS_TBL;
3302 l_fee_calc_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
3303 -- l_fee_calc_tbl_full LNS_FEE_ENGINE.FEE_CALC_TBL;
3304 i number;
3305 l_last_installment number;
3306 l_amount_overdue number;
3307 l_phase VARCHAR2(30);
3308
3309 vPLSQL varchar2(4000);
3310 Type refCur is ref cursor;
3311 sql_Cur refCur;
3312 writeCount NUMBER;
3313 updateCount NUMBER;
3314 deleteCount NUMBER;
3315 l_fee_schedule_id number;
3316 l_current_installment number;
3317 l_due_date date;
3318 l_late_date date;
3319 l_prin_overdue number;
3320 l_int_overdue number;
3321
3322 l_write_fee_calc_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
3323 l_update_fee_calc_tbl LNS_FEE_ENGINE.FEE_CALC_TBL;
3324 l_loan_details LNS_FINANCIALS.LOAN_DETAILS_REC;
3325
3326 CURSOR last_instal_cur(P_LOAN_ID number, p_payment_number number) IS
3327 select DUE_DATE
3328 from LNS_AMORTIZATION_SCHEDS
3329 where LOAN_ID = P_LOAN_ID
3330 and PAYMENT_NUMBER = p_payment_number
3331 and PARENT_AMORTIZATION_ID is null
3332 and (REVERSED_FLAG is null or REVERSED_FLAG = 'N');
3333
3334 cursor c_prin_overdue(p_loan_id number, p_date date) is
3335 select (ACTUAL_BALANCE - THEORETICAL_BALANCE)
3336 from LNS_PRIN_TRX_ACTIVITIES_V
3337 where loan_id = p_loan_id and
3338 trunc(ACTIVITY_DATE) <= trunc(p_date)
3339 and ACTIVITY_CODE not in ('DISBURSEMENT', 'INVOICE_ADDED')
3340 order by activity_date desc, display_order desc;
3341
3342 cursor c_int_overdue(p_loan_id number, p_date date) is
3343 select (THEORETICAL_BALANCE - ACTUAL_BALANCE)
3344 from LNS_INT_TRX_ACTIVITIES_V
3345 where loan_id = p_loan_id
3346 and trunc(ACTIVITY_DATE) <= trunc(p_date)
3347 order by activity_date desc, display_order desc;
3348
3349 begin
3350
3351 l_api_name := 'processLateFees';
3352 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
3353
3354 -- Standard Start of API savepoint
3355 SAVEPOINT processLateFees;
3356
3357 -- Initialize message list IF p_init_msg_list is set to TRUE.
3358 IF FND_API.to_Boolean(p_init_msg_list) THEN
3359 FND_MSG_PUB.initialize;
3360 END IF;
3361
3362 -- Initialize API return status to SUCCESS
3363 x_return_status := FND_API.G_RET_STS_SUCCESS;
3364
3365 -- ---------------------------------------------------------------------
3366 -- Api body
3367 -- ---------------------------------------------------------------------
3368
3369 i := 0;
3370 writeCount := 0;
3371 updateCount := 0;
3372 deleteCount := 0;
3373 l_write_fee_calc_tbl.delete;
3374 l_update_fee_calc_tbl.delete;
3375
3376 l_phase := nvl(p_phase, 'TERM');
3377
3378 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - p_loan_id = ' || p_loan_id);
3379 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_phase = ' || l_phase);
3380
3381 l_loan_details := lns_financials.getLoanDetails(p_loan_id, 'CURRENT', l_phase);
3382
3383 l_last_installment := l_loan_details.last_installment_billed;
3384 l_current_installment := l_last_installment + 1;
3385 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_last_installment = ' || l_last_installment);
3386 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_current_installment = ' || l_current_installment);
3387
3388 if l_last_installment > 0 then
3389
3390 open last_instal_cur(p_loan_id, l_last_installment);
3391 fetch last_instal_cur into l_due_date;
3392 close last_instal_cur;
3393 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_due_date = ' || l_due_date);
3394
3395 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - Calling getFeeStructures...');
3396 l_late_fee_structures := lns_fee_engine.getFeeStructures(p_loan_id => p_loan_id
3397 ,p_fee_category => 'EVENT'
3398 ,p_fee_type => 'EVENT_LATE_CHARGE'
3399 ,p_installment => null
3400 ,p_phase => l_phase
3401 ,p_fee_id => null);
3402 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - after getFeeStructures api');
3403 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_late_fee_structures.count = ' || l_late_fee_structures.count);
3404
3405 for x in 1..l_late_fee_structures.count loop
3406
3407 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' ');
3408 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' ' || l_late_fee_structures(x).fee_name);
3409
3410 l_late_date := l_due_date + l_late_fee_structures(x).NUMBER_GRACE_DAYS;
3411 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_late_date = ' || l_late_date);
3412
3413 open c_prin_overdue(p_loan_id, l_late_date);
3414 fetch c_prin_overdue into l_prin_overdue;
3415 close c_prin_overdue;
3416 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_prin_overdue = ' || l_prin_overdue);
3417
3418 open c_int_overdue(p_loan_id, l_late_date);
3419 fetch c_int_overdue into l_int_overdue;
3420 close c_int_overdue;
3421 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_int_overdue = ' || l_int_overdue);
3422
3423 l_fee_basis_tbl(1).fee_basis_name := 'OVERDUE_PRIN_INT';
3424 l_fee_basis_tbl(1).fee_basis_amount := l_prin_overdue + l_int_overdue;
3425 l_fee_basis_tbl(2).fee_basis_name := 'OVERDUE_INT';
3426 l_fee_basis_tbl(2).fee_basis_amount := l_int_overdue;
3427 l_fee_basis_tbl(3).fee_basis_name := 'OVERDUE_PRIN';
3428 l_fee_basis_tbl(3).fee_basis_amount := l_prin_overdue;
3429
3430 if trunc(l_late_date) < trunc(sysdate) then
3431 if l_late_fee_structures(x).fee_basis_rule = 'OVERDUE_PRIN_INT' then
3432 l_amount_overdue := l_prin_overdue + l_int_overdue;
3433 elsif l_late_fee_structures(x).fee_basis_rule = 'OVERDUE_PRIN' then
3434 l_amount_overdue := l_prin_overdue;
3435 elsif l_late_fee_structures(x).fee_basis_rule = 'OVERDUE_INT' then
3436 l_amount_overdue := l_int_overdue;
3437 end if;
3438 else
3439 l_amount_overdue := 0;
3440 end if;
3441
3442 l_late_fee_structure(1) := l_late_fee_structures(x);
3443
3444 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_amount_overdue = ' || l_amount_overdue);
3445 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - minimum_overdue_amount = ' || l_late_fee_structure(1).minimum_overdue_amount);
3446
3447 if l_amount_overdue > l_late_fee_structure(1).minimum_overdue_amount and l_amount_overdue > 0 then
3448
3449 i := i + 1;
3450 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - Calling calculateFees...');
3451 lns_fee_engine.calculateFees(p_loan_id => p_loan_id
3452 ,p_fee_basis_tbl => l_fee_basis_tbl
3453 ,p_installment => l_current_installment
3454 ,p_fee_structures => l_late_fee_structure
3455 ,x_fees_tbl => l_fee_calc_tbl
3456 ,x_return_status => l_return_status
3457 ,x_msg_count => l_msg_count
3458 ,x_msg_data => l_msg_data);
3459 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_return_status = ' || l_return_status);
3460 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3461 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_CALCULATION_FAILURE');
3462 FND_MSG_PUB.ADD;
3463 RAISE FND_API.G_EXC_ERROR;
3464 end if;
3465
3466 BEGIN
3467 SELECT fee_schedule_id INTO l_fee_schedule_id
3468 FROM lns_fee_schedules
3469 WHERE loan_id = p_loan_id
3470 AND fee_id = l_fee_calc_tbl(1).fee_id
3471 AND fee_installment = l_fee_calc_tbl(1).fee_installment
3472 AND nvl(phase, 'TERM') = l_fee_calc_tbl(1).phase
3473 AND active_flag = 'Y'
3474 AND billed_flag = 'N';
3475 EXCEPTION
3476 WHEN NO_DATA_FOUND THEN
3477 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - No records in feeShcd table for ' ||l_fee_calc_tbl(1).FEE_ID||' at installment '|| l_fee_calc_tbl(1).fee_installment);
3478 l_fee_schedule_id := NULL;
3479 END;
3480
3481 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - l_fee_schedule_id = ' || l_fee_schedule_id);
3482 IF l_fee_schedule_id IS NULL THEN
3483 -- Insert the FeeSchedule Record
3484
3485 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - Inserting fee with fee_id = ' || l_fee_calc_tbl(1).FEE_ID);
3486 writeCount := writeCount + 1;
3487 l_write_fee_calc_tbl(writeCount) := l_fee_calc_tbl(1);
3488
3489 ELSE
3490 -- Update the existed FeeSchedule record
3491
3492 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - Updating fee for fee_schedule_id = ' || l_fee_schedule_id);
3493 updateCount := updateCount + 1;
3494 l_update_fee_calc_tbl(updateCount) := l_fee_calc_tbl(1);
3495 l_update_fee_calc_tbl(updateCount).FEE_SCHEDULE_ID := l_fee_schedule_id;
3496
3497 END IF;
3498
3499 else
3500 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - Deleting fee with fee_id = ' || l_late_fee_structure(1).fee_id);
3501
3502 delete from lns_fee_schedules
3503 WHERE loan_id = p_loan_id
3504 AND fee_id = l_late_fee_structure(1).fee_id
3505 AND fee_installment = l_current_installment
3506 AND nvl(phase, 'TERM') = l_late_fee_structure(1).phase
3507 AND active_flag = 'Y'
3508 AND billed_flag = 'N';
3509
3510 deleteCount := deleteCount + 1;
3511 end if;
3512
3513 end loop;
3514
3515 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' Total No of New Records = ' || writeCount );
3516 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' Total No of Updatable Records = ' || updateCount );
3517 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' Total No of Deleted Records = ' || deleteCount );
3518
3519 IF (writeCount > 0) THEN
3520 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' Calling writeFeeSchedule...');
3521 lns_fee_engine.writeFeeSchedule(p_init_msg_list => p_init_msg_list
3522 ,p_commit => p_commit
3523 ,p_loan_id => p_loan_id
3524 ,p_fees_tbl => l_write_fee_calc_tbl
3525 ,x_return_status => l_return_status
3526 ,x_msg_count => l_msg_count
3527 ,x_msg_data => l_msg_data);
3528 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3529 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_WRITE_FAILURE');
3530 FND_MSG_PUB.ADD;
3531 RAISE FND_API.G_EXC_ERROR;
3532 end if;
3533
3534 END IF;
3535
3536 IF (updateCount > 0) THEN
3537 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' Calling updateFeeSchedule...');
3538 lns_fee_engine.updateFeeSchedule(p_init_msg_list => p_init_msg_list
3539 ,p_commit => p_commit
3540 ,p_loan_id => p_loan_id
3541 ,p_fees_tbl => l_update_fee_calc_tbl
3542 ,x_return_status => l_return_status
3543 ,x_msg_count => l_msg_count
3544 ,x_msg_data => l_msg_data);
3545 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3546 FND_MESSAGE.SET_NAME('LNS', 'LNS_FEE_UPDATE_FAILURE');
3547 FND_MSG_PUB.ADD;
3548 RAISE FND_API.G_EXC_ERROR;
3549 end if;
3550 END IF;
3551
3552 end if; -- l_last_installment > 1
3553 -- ---------------------------------------------------------------------
3554 -- End of API body
3555 -- ---------------------------------------------------------------------
3556
3557 IF FND_API.to_Boolean(p_commit) THEN
3558 COMMIT WORK;
3559 END IF;
3560
3561 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3562
3563 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
3564
3565 EXCEPTION
3566 WHEN FND_API.G_EXC_ERROR THEN
3567 x_return_status := FND_API.G_RET_STS_ERROR;
3568 x_msg_count := l_msg_count;
3569 x_msg_data := l_msg_data;
3570 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3571 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
3572 ROLLBACK TO processLateFees;
3573
3574 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3575 x_return_status := FND_API.G_RET_STS_ERROR;
3576 x_msg_count := l_msg_count;
3577 x_msg_data := l_msg_data;
3578 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3579 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
3580 ROLLBACK TO processLateFees;
3581
3582 WHEN OTHERS THEN
3583 x_return_status := FND_API.G_RET_STS_ERROR;
3584 x_msg_count := l_msg_count;
3585 x_msg_data := l_msg_data;
3586 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3587 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
3588 ROLLBACK TO processLateFees;
3589
3590
3591 end processLateFees;
3592
3593
3594 PROCEDURE LOAN_LATE_FEES_CONCUR(ERRBUF OUT NOCOPY VARCHAR2
3595 ,RETCODE OUT NOCOPY VARCHAR2
3596 ,P_BORROWER_ID IN NUMBER
3597 ,P_LOAN_ID IN NUMBER)
3598
3599 is
3600 l_msg_count number;
3601 l_msg_data varchar2(500);
3602 l_return_Status varchar2(1);
3603 my_message varchar2(2000);
3604 l_processed_fees number;
3605 l_fee_records1 number;
3606 l_fee_records2 number;
3607 l_loan_id number;
3608 l_phase varchar2(30);
3609
3610 cursor c_borrower_loans(p_borrower_id number) is
3611 select loan_id, current_phase
3612 from lns_loan_headers
3613 where loan_status in ('ACTIVE', 'DELINQUENT', 'DEFAULT')
3614 and primary_borrower_id = p_borrower_id;
3615
3616 cursor c_all_active_loans is
3617 select loan_id, current_phase
3618 from lns_loan_headers
3619 where loan_status in ('ACTIVE', 'DELINQUENT', 'DEFAULT');
3620
3621 cursor c_loan_det(c_loan_id NUMBER) is
3622 select current_phase
3623 from lns_loan_headers
3624 where loan_id = c_loan_id
3625 and loan_status in ('ACTIVE', 'DELINQUENT', 'DEFAULT');
3626
3627 BEGIN
3628
3629
3630 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Beginning Loans Late Fee Assessment');
3631 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_LOAN_ID ' || p_loan_id);
3632 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_BORROWER_ID ' || p_borrower_id);
3633
3634 select count(1) into l_fee_records1
3635 from lns_fee_schedules
3636 where active_flag = 'Y'
3637 and billed_flag = 'N';
3638
3639 if p_loan_id is not null then
3640 FND_FILE.PUT_LINE(FND_FILE.LOG, 'processing single loan ' || p_loan_id);
3641 OPEN c_loan_det(p_loan_id);
3642 FETCH c_loan_det INTO l_phase;
3643 CLOSE c_loan_det;
3644 lns_fee_engine.processLateFees(p_init_msg_list => FND_API.G_TRUE
3645 ,p_commit => FND_API.G_TRUE
3646 ,p_loan_id => p_loan_id
3647 ,p_phase => l_phase
3648 ,x_return_status => l_return_status
3649 ,x_msg_count => l_msg_count
3650 ,x_msg_data => l_msg_data);
3651
3652 elsif p_borrower_id is not null then
3653
3654 open c_borrower_loans(p_borrower_id);
3655 loop
3656 fetch c_borrower_loans
3657 into l_loan_id, l_phase;
3658
3659 exit when c_borrower_loans%notfound;
3660
3661 FND_FILE.PUT_LINE(FND_FILE.LOG, 'processing loan ' || l_loan_id);
3662 lns_fee_engine.processLateFees(p_init_msg_list => FND_API.G_TRUE
3663 ,p_commit => FND_API.G_TRUE
3664 ,p_loan_id => l_loan_id
3665 ,p_phase => l_phase
3666 ,x_return_status => l_return_status
3667 ,x_msg_count => l_msg_count
3668 ,x_msg_data => l_msg_data);
3669 FND_FILE.PUT_LINE(FND_FILE.LOG, 'FAP status ' || l_return_status);
3670
3671 end loop;
3672
3673 else
3674 open c_all_active_loans;
3675 loop
3676 fetch c_all_active_loans
3677 into l_loan_id, l_phase;
3678
3679 exit when c_all_active_loans%notfound;
3680
3681 FND_FILE.PUT_LINE(FND_FILE.LOG, 'processing loan ' || l_loan_id);
3682 lns_fee_engine.processLateFees(p_init_msg_list => FND_API.G_TRUE
3683 ,p_commit => FND_API.G_TRUE
3684 ,p_loan_id => l_loan_id
3685 ,p_phase => l_phase
3686 ,x_return_status => l_return_status
3687 ,x_msg_count => l_msg_count
3688 ,x_msg_data => l_msg_data);
3689 FND_FILE.PUT_LINE(FND_FILE.LOG, 'FAP status ' || l_return_status);
3690
3691 end loop;
3692
3693 end if;
3694
3695 select count(1) into l_fee_records2
3696 from lns_fee_schedules
3697 where active_flag = 'Y'
3698 and billed_flag = 'N';
3699
3700 l_processed_fees := l_fee_records2 - l_fee_records1;
3701 FND_FILE.PUT_LINE(FND_FILE.LOG, 'added ' || l_processed_fees || ' into fee schedule');
3702
3703 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3704 RAISE FND_API.G_EXC_ERROR;
3705 else
3706 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Late Fee Assessment: PROCESS COMPLETED SUCCESFULLY.');
3707 end if;
3708
3709 EXCEPTION
3710 -- note do not set retcode when error is expected
3711 WHEN FND_API.G_EXC_ERROR THEN
3712 RETCODE := -1;
3713 ERRBUF := l_msg_data;
3714 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN FAP: ' || sqlerrm || ERRBUF);
3715 if l_msg_count > 0 then
3716 FOR l_index IN 1..l_msg_count LOOP
3717 my_message := FND_MSG_PUB.Get(p_msg_index => l_index, p_encoded => 'F');
3718 FND_FILE.PUT_LINE(FND_FILE.LOG, my_message);
3719 END LOOP;
3720 end if;
3721
3722 WHEN OTHERS THEN
3723 RETCODE := -1;
3724 ERRBUF := l_msg_data;
3725 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN FAP: ' || sqlerrm || ERRBUF);
3726 if l_msg_count > 0 then
3727 FOR l_index IN 1..l_msg_count LOOP
3728 my_message := FND_MSG_PUB.Get(p_msg_index => l_index, p_encoded => 'F');
3729 FND_FILE.PUT_LINE(FND_FILE.LOG, my_message);
3730 END LOOP;
3731 end if;
3732
3733 END LOAN_LATE_FEES_CONCUR;
3734
3735
3736 /*=========================================================================
3737 || PUBLIC PROCEDURE getSubmitForApprFeeSchedule
3738 ||
3739 || DESCRIPTION
3740 || Overview: this procedure will return a table of fees off of the fee
3741 || schedule of 'At Submit for Approval' fees
3742 ||
3743 || THIS WILL BE CALLED BY BILL_SING_LOAN_SUBMIT_APPR_FEE FUNCTION TO RETURN
3744 || 'AT SUBMIT FOR APPROVAL' FEES TO BE BILLED ON A LOAN
3745 ||
3746 || PSEUDO CODE/LOGIC
3747 ||
3748 || PARAMETERS
3749 || Parameter: p_fee_structure_tbl => represents a table of fees
3750 || p_loan_id => loan_id
3751 ||
3752 || Return value:
3753 || standard
3754 || KNOWN ISSUES
3755 ||
3756 || NOTES
3757 ||
3758 || MODIFICATION HISTORY
3759 || Date Author Description of Changes
3760 || 07-JUL-2009 mbolli Bug#6830765 - Created
3761 || 26-Oct-2009 mbolli Bug#8937530 - Retrieve the BilledFlag ="Y" submitFroApprFee also
3762 *=======================================================================*/
3763 procedure getSubmitForApprFeeSchedule(p_init_msg_list in varchar2
3764 ,p_loan_id in number
3765 ,p_billed_flag in varchar2
3766 ,x_fees_tbl OUT NOCOPY LNS_FEE_ENGINE.FEE_CALC_TBL
3767 ,x_return_status out nocopy varchar2
3768 ,x_msg_count out nocopy number
3769 ,x_msg_data out nocopy varchar2)
3770 is
3771 l_api_name varchar2(50);
3772 l_return_status VARCHAR2(1);
3773 l_msg_count NUMBER;
3774 l_msg_data VARCHAR2(32767);
3775
3776 i number;
3777 l_fee_rec LNS_FEE_ENGINE.FEE_CALC_REC;
3778 l_fee_schedule_id number;
3779 l_fee_id number;
3780 l_fee_amount number;
3781 l_fee_name varchar2(50);
3782 l_fee_installment number;
3783 l_fee_description varchar2(250);
3784 l_fee_waivable_flag varchar2(1);
3785 l_fee_category varchar2(30);
3786 l_fee_type varchar2(30);
3787 l_fee_deletable_flag varchar2(1);
3788 l_fee_editable_flag varchar2(1);
3789 l_phase varchar2(30);
3790
3791 -- Billed/Unbilled submitApproval fees on the schedule
3792 cursor c_submit_appr_fees(c_loan_id number, c_installment number, c_billed_flag varchar2) is
3793 select sched.fee_schedule_id
3794 ,sched.fee_id
3795 ,sched.fee_amount - nvl(sched.waived_amount, 0)
3796 ,struct.fee_name
3797 ,struct.fee_category
3798 ,struct.fee_type
3799 ,sched.fee_installment
3800 ,struct.fee_description
3801 ,sched.fee_waivable_flag -- should be struct right
3802 ,decode(struct.fee_category, 'MANUAL', 'Y', 'N')
3803 ,nvl(struct.fee_editable_flag, 'N')
3804 ,nvl(sched.phase, 'TERM')
3805 from lns_fee_schedules sched
3806 ,lns_fees struct
3807 where sched.loan_id = c_loan_id
3808 and sched.fee_id = struct.fee_id
3809 and fee_installment = c_installment
3810 and struct.FEE_CATEGORY = 'EVENT'
3811 and struct.FEE_TYPE = 'EVENT_ORIGINATION'
3812 and struct.BILLING_OPTION = 'SUBMIT_FOR_APPROVAL'
3813 and active_flag = 'Y'
3814 and billed_flag = c_billed_flag
3815 and (not exists
3816 (select 'X'
3817 from lns_amortization_scheds am
3818 ,lns_amortization_lines lines
3819 where lines.loan_id = c_loan_id
3820 and lines.fee_schedule_id = sched.fee_schedule_id
3821 and am.loan_id = lines.loan_id
3822 and NVL(am.reversed_flag, 'N') = 'N'
3823 and am.payment_number = c_installment
3824 and am.amortization_schedule_id = (select max(amortization_schedule_id)
3825 from lns_amortization_lines amlines2
3826 where amlines2.fee_schedule_id = lines.fee_schedule_id)
3827 )
3828 or exists
3829 (select 'X'
3830 from lns_amortization_scheds am
3831 ,lns_amortization_lines lines
3832 where lines.loan_id = c_loan_id
3833 and lines.fee_schedule_id = sched.fee_schedule_id
3834 and am.loan_id = lines.loan_id
3835 and am.reversed_flag = 'Y'
3836 and am.payment_number = c_installment
3837 and am.amortization_schedule_id = (select max(amortization_schedule_id)
3838 from lns_amortization_lines amlines2
3839 where amlines2.fee_schedule_id = lines.fee_schedule_id)
3840 ));
3841
3842
3843 begin
3844
3845 l_api_name := 'getSubmitForApprFeeSchedule';
3846 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
3847
3848 -- Standard Start of API savepoint
3849 SAVEPOINT getSubmitForApprFeeSchedule;
3850
3851 -- Initialize message list IF p_init_msg_list is set to TRUE.
3852 IF FND_API.to_Boolean(p_init_msg_list) THEN
3853 FND_MSG_PUB.initialize;
3854 END IF;
3855
3856 -- Initialize API return status to SUCCESS
3857 x_return_status := FND_API.G_RET_STS_SUCCESS;
3858
3859 -- ---------------------------------------------------------------------
3860 -- Api body
3861 -- ---------------------------------------------------------------------
3862 -- initialize any variables here
3863 i := 0;
3864 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'The p_billed_flag is :'||p_billed_flag);
3865 OPEN c_submit_appr_fees(p_loan_id, 0, p_billed_flag);
3866 LOOP
3867
3868 FETCH c_submit_appr_fees INTO
3869 l_fee_schedule_id
3870 ,l_fee_id
3871 ,l_fee_amount
3872 ,l_fee_name
3873 ,l_fee_category
3874 ,l_fee_type
3875 ,l_fee_installment
3876 ,l_fee_description
3877 ,l_fee_waivable_flag
3878 ,l_fee_deletable_flag
3879 ,l_fee_editable_flag
3880 ,l_phase;
3881 EXIT WHEN c_submit_appr_fees%NOTFOUND;
3882
3883 i := i + 1;
3884 l_fee_rec.fee_schedule_id := l_fee_schedule_id;
3885 l_fee_rec.fee_id := l_fee_id;
3886 l_fee_rec.fee_amount := l_fee_amount;
3887 l_fee_rec.fee_name := l_fee_name;
3888 l_fee_rec.fee_category := l_fee_category;
3889 l_fee_rec.fee_type := l_fee_type;
3890 l_fee_rec.fee_installment := l_fee_installment;
3891 l_fee_rec.fee_description := l_fee_description;
3892 l_fee_rec.fee_waivable_flag := l_fee_waivable_flag;
3893 l_fee_rec.fee_deletable_flag := l_fee_deletable_flag;
3894 l_fee_rec.fee_editable_flag := l_fee_editable_flag;
3895 l_fee_rec.phase := l_phase;
3896
3897 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - SubmtApproval fee #: ' || i);
3898 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee schedule id: ' || l_fee_rec.fee_schedule_id);
3899 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee id: ' || l_fee_rec.fee_id);
3900 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - fee amount: ' || l_fee_rec.fee_amount);
3901 x_fees_tbl(i) := l_fee_rec;
3902 END LOOP;
3903
3904 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - Total No. of SubmitApproval Fees are: ' || i);
3905
3906 -- ---------------------------------------------------------------------
3907 -- End of API body
3908 -- ---------------------------------------------------------------------
3909 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3910
3911 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
3912
3913 EXCEPTION
3914 WHEN FND_API.G_EXC_ERROR THEN
3915 x_return_status := FND_API.G_RET_STS_ERROR;
3916 x_msg_count := l_msg_count;
3917 x_msg_data := l_msg_data;
3918 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3919 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
3920 ROLLBACK TO getSubmitForApprFeeSchedule;
3921
3922 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3923 x_return_status := FND_API.G_RET_STS_ERROR;
3924 x_msg_count := l_msg_count;
3925 x_msg_data := l_msg_data;
3926 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3927 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
3928 ROLLBACK TO getSubmitForApprFeeSchedule;
3929
3930 WHEN OTHERS THEN
3931 x_return_status := FND_API.G_RET_STS_ERROR;
3932 x_msg_count := l_msg_count;
3933 x_msg_data := l_msg_data;
3934 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3935 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
3936 ROLLBACK TO getSubmitForApprFeeSchedule;
3937
3938 end getSubmitForApprFeeSchedule;
3939
3940 /*=========================================================================
3941 || PUBLIC PROCEDURE SET_DISB_FEES_INSTALL
3942 ||
3943 || DESCRIPTION
3944 || Overview: this procedure will update the feeInstallments(begin and end) for the given disb_header_id
3945 ||
3946 ||
3947 || PSEUDO CODE/LOGIC
3948 ||
3949 || PARAMETERS
3950 || Parameter: p_disb_header_id => disbursement header id
3951 ||
3952 || Return value:
3953 || standard
3954 || KNOWN ISSUES
3955 ||
3956 || NOTES
3957 ||
3958 || MODIFICATION HISTORY
3959 || Date Author Description of Changes
3960 || 16-FEB-2010 mbolli Bug#9255294 - Created
3961 *=======================================================================*/
3962 procedure SET_DISB_FEES_INSTALL(p_init_msg_list in varchar2
3963 ,p_disb_header_id in varchar2
3964 ,x_return_status out nocopy varchar2
3965 ,x_msg_count out nocopy number
3966 ,x_msg_data out nocopy varchar2)
3967 is
3968 l_api_name varchar2(50);
3969 l_return_status VARCHAR2(1);
3970 l_msg_count NUMBER;
3971 l_msg_data VARCHAR2(32767);
3972 l_inst_no NUMBER;
3973
3974 l_fee_assignment_rec LNS_FEE_ASSIGNMENT_PUB.FEE_ASSIGNMENT_REC_TYPE;
3975
3976 begin
3977
3978 l_api_name := 'SET_DISB_FEES_INSTALL';
3979
3980 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
3981
3982 -- Standard Start of API savepoint
3983 SAVEPOINT SET_DISB_FEES_INSTALL;
3984
3985 -- Initialize message list IF p_init_msg_list is set to TRUE.
3986 IF FND_API.to_Boolean(p_init_msg_list) THEN
3987 FND_MSG_PUB.initialize;
3988 END IF;
3989
3990 -- Initialize API return status to SUCCESS
3991 x_return_status := FND_API.G_RET_STS_SUCCESS;
3992
3993 -- ---------------------------------------------------------------------
3994 -- Api body
3995 -- ---------------------------------------------------------------------
3996 -- initialize any variables here
3997
3998 -- Update the feeInstallment of the fundignFees of this disbursement
3999 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Updating the feeAssignment installments of disb_hdr_id: '||p_disb_header_id);
4000
4001 l_inst_no := LNS_FIN_UTILS.getNextInstForDisbursement(p_disb_header_id);
4002
4003 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Installment No is : '||l_inst_no);
4004
4005 IF (l_inst_no IS NULL OR l_inst_no = -1) THEN
4006 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Call to LNS_FIN_UTIL.getNextInstForDisbursement failed');
4007 ELSE
4008
4009 UPDATE lns_fee_assignments
4010 SET begin_installment_number = l_inst_no
4011 ,end_installment_number = l_inst_no
4012 ,object_version_number = object_version_number + 1
4013 ,last_updated_by = LNS_UTILITY_PUB.last_updated_by
4014 ,last_update_date = LNS_UTILITY_PUB.last_update_date
4015 ,last_update_login = LNS_UTILITY_PUB.last_update_login
4016 WHERE disb_header_id =p_disb_header_id;
4017
4018 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Updated feeAssignments '||SQL%ROWCOUNT);
4019
4020 END IF;
4021
4022 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
4023
4024 EXCEPTION
4025 WHEN FND_API.G_EXC_ERROR THEN
4026 x_return_status := FND_API.G_RET_STS_ERROR;
4027 x_msg_count := l_msg_count;
4028 x_msg_data := l_msg_data;
4029 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4030 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
4031 ROLLBACK TO SET_DISB_FEES_INSTALL;
4032
4033 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4034 x_return_status := FND_API.G_RET_STS_ERROR;
4035 x_msg_count := l_msg_count;
4036 x_msg_data := l_msg_data;
4037 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4038 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
4039 ROLLBACK TO SET_DISB_FEES_INSTALL;
4040
4041 WHEN OTHERS THEN
4042 x_return_status := FND_API.G_RET_STS_ERROR;
4043 x_msg_count := l_msg_count;
4044 x_msg_data := l_msg_data;
4045 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4046 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
4047 ROLLBACK TO SET_DISB_FEES_INSTALL;
4048
4049
4050 END SET_DISB_FEES_INSTALL;
4051
4052 END LNS_FEE_ENGINE;