[Home] [Help]
PACKAGE BODY: APPS.LNS_FEE_ASSIGNMENT_PUB
Source
1 PACKAGE BODY LNS_FEE_ASSIGNMENT_PUB AS
2 /* $Header: LNS_FASGM_PUBP_B.pls 120.22 2011/05/20 20:58:01 scherkas ship $ */
3
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7 -- G_DEBUG_COUNT CONSTANT NUMBER := 0;
8 -- G_DEBUG CONSTANT BOOLEAN := FALSE;
9
10 G_PKG_NAME CONSTANT VARCHAR(30) := 'LNS_FEE_ASSIGNMENT_PUB';
11 G_AF_DO_DEBUG CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
12
13 PROCEDURE Set_Defaults (p_FEE_ASSIGNMENT_rec IN OUT NOCOPY FEE_ASSIGNMENT_REC_TYPE
14 )
15 IS
16 BEGIN
17
18 IF (p_FEE_ASSIGNMENT_rec.begin_installment_number IS NULL) THEN
19 p_FEE_ASSIGNMENT_rec.begin_installment_number := LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_FEE_ASSIGNMENT_REC.loan_id);
20 END IF;
21
22 IF (p_FEE_ASSIGNMENT_rec.end_installment_number IS NULL) THEN
23 p_FEE_ASSIGNMENT_rec.end_installment_number := LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(p_FEE_ASSIGNMENT_REC.loan_id);
24 END IF;
25
26 IF (p_FEE_ASSIGNMENT_rec.phase IS NULL) THEN
27 p_FEE_ASSIGNMENT_rec.phase := 'TERM';
28 END IF;
29
30 END Set_Defaults;
31 --------------------------------------------------
32 -- declaration of private procedures and functions
33 --------------------------------------------------
34
35 PROCEDURE do_create_fee_assignment (
36 p_fee_assignment_rec IN OUT NOCOPY FEE_ASSIGNMENT_REC_TYPE,
37 x_fee_assignment_id OUT NOCOPY NUMBER,
38 x_return_status IN OUT NOCOPY VARCHAR2
39 );
40
41 PROCEDURE do_update_fee_assignment (
42 p_fee_assignment_rec IN OUT NOCOPY FEE_ASSIGNMENT_REC_TYPE,
43 p_object_version_number IN OUT NOCOPY NUMBER,
44 x_return_status IN OUT NOCOPY VARCHAR2
45 );
46
47 PROCEDURE do_delete_fee_assignment (
48 p_fee_assignment_id IN NUMBER,
49 x_return_status IN OUT NOCOPY VARCHAR2
50 );
51
52 /*===========================================================================+
53 | PROCEDURE
54 | do_create_fee_assignment
55 |
56 | DESCRIPTION
57 | Creates assignment.
58 |
59 | SCOPE - PRIVATE
60 |
61 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
62 |
63 | ARGUMENTS : IN:
64 | OUT:
65 | x_fee_assignment_id
66 | IN/OUT:
67 | p_fee_assignment_rec
68 | x_return_status
69 |
70 | RETURNS : NONE
71 |
72 | NOTES
73 |
74 | MODIFICATION HISTORY
75 |
76 | 22-APR-2004 Bernice Lam Created.
77 +===========================================================================*/
78
79 PROCEDURE do_create_fee_assignment(
80 p_fee_assignment_rec IN OUT NOCOPY FEE_ASSIGNMENT_REC_TYPE,
81 x_fee_assignment_id OUT NOCOPY NUMBER,
82 x_return_status IN OUT NOCOPY VARCHAR2
83 ) IS
84
85 l_fee_assignment_id NUMBER;
86 l_rowid ROWID;
87 l_dummy VARCHAR2(1);
88 l_msg_count NUMBER;
89 l_msg_data VARCHAR2(2000);
90
91 BEGIN
92
93 l_fee_assignment_id := p_fee_assignment_rec.fee_assignment_id;
94 l_rowid := NULL;
95
96 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
97 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin do_create_fee_assignment procedure');
98 END IF;
99
100 -- if primary key value is passed, check for uniqueness.
101 IF l_fee_assignment_id IS NOT NULL AND
102 l_fee_assignment_id <> FND_API.G_MISS_NUM
103 THEN
104 BEGIN
105 SELECT 'Y'
106 INTO l_dummy
107 FROM LNS_FEE_ASSIGNMENTS
108 WHERE fee_assignment_id = l_fee_assignment_id;
109
110 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_DUPLICATE_COLUMN');
111 FND_MESSAGE.SET_TOKEN('COLUMN', 'fee_assignment_id');
112 FND_MSG_PUB.ADD;
113 RAISE FND_API.G_EXC_ERROR;
114
115 EXCEPTION
116 WHEN NO_DATA_FOUND THEN
117 NULL;
118 END;
119 END IF;
120
121 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
122 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_create_fee_assignment procedure: Before call to LNS_FEE_ASSIGNMENTS_PKG.Insert_Row');
123 END IF;
124
125 -- call table-handler.
126 LNS_FEE_ASSIGNMENTS_PKG.Insert_Row (
127 X_FEE_ASSIGNMENT_ID => p_fee_assignment_rec.fee_assignment_id,
128 P_OBJECT_VERSION_NUMBER => 1,
129 P_LOAN_ID => p_fee_assignment_rec.loan_id,
130 P_FEE_ID => p_fee_assignment_rec.fee_id,
131 P_FEE => p_fee_assignment_rec.fee,
132 P_FEE_TYPE => p_fee_assignment_rec.fee_type,
133 P_FEE_BASIS => p_fee_assignment_rec.fee_basis,
134 P_NUMBER_GRACE_DAYS => p_fee_assignment_rec.number_grace_days,
135 P_START_DATE_ACTIVE => p_fee_assignment_rec.start_date_active,
136 P_END_DATE_ACTIVE => p_fee_assignment_rec.end_date_active,
137 P_CREATED_BY => p_fee_assignment_rec.created_by,
138 P_CREATION_DATE => p_fee_assignment_rec.creation_date,
139 P_LAST_UPDATED_BY => p_fee_assignment_rec.last_updated_by,
140 P_LAST_UPDATE_DATE => p_fee_assignment_rec.last_update_date,
141 P_LAST_UPDATE_LOGIN => p_fee_assignment_rec.last_update_login,
142 P_COLLECTED_THIRD_PARTY_FLAG => p_fee_assignment_rec.collected_third_party_flag,
143 P_RATE_TYPE => p_fee_assignment_rec.rate_type,
144 P_BEGIN_INSTALLMENT_NUMBER => p_fee_assignment_rec.begin_installment_number,
145 P_END_INSTALLMENT_NUMBER => p_fee_assignment_rec.end_installment_number,
146 P_NUMBER_OF_PAYMENTS => p_fee_assignment_rec.number_of_payments,
147 P_BILLING_OPTION => p_fee_assignment_rec.billing_option,
148 P_DISB_HEADER_ID => p_fee_assignment_rec.disb_header_id,
149 P_DELETE_DISABLED_FLAG => p_fee_assignment_rec.delete_disabled_flag,
150 P_OPEN_PHASE_FLAG => p_fee_assignment_rec.open_phase_flag,
151 P_PHASE => p_fee_assignment_rec.phase,
152 P_CUSTOM_PROCEDURE => p_fee_assignment_rec.custom_procedure
153 );
154
155 x_fee_assignment_id := p_fee_assignment_rec.fee_assignment_id;
156
157 If ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
158 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_create_fee_assignment procedure: After call to LNS_FEE_ASSIGNMENT.Insert_Row');
159 END IF;
160
161 END do_create_fee_assignment;
162
163
164 /*===========================================================================+
165 | PROCEDURE
166 | do_update_fee_assignment
167 |
168 | DESCRIPTION
169 | Updates assignment.
170 |
171 | SCOPE - PRIVATE
172 |
173 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
174 |
175 | ARGUMENTS : IN:
176 | OUT:
177 | IN/OUT:
178 | p_fee_assignment_rec
179 | p_object_version_number
180 | x_return_status
181 |
182 | RETURNS : NONE
183 |
184 | NOTES
185 |
186 | MODIFICATION HISTORY
187 |
188 | 22-APR-2004 Bernice Lam Created.
189 +===========================================================================*/
190
191 PROCEDURE do_update_fee_assignment(
192 p_fee_assignment_rec IN OUT NOCOPY FEE_ASSIGNMENT_REC_TYPE,
193 p_object_version_number IN OUT NOCOPY NUMBER,
194 x_return_status IN OUT NOCOPY VARCHAR2
195 ) IS
196
197 l_object_version_number NUMBER;
198 l_rowid ROWID;
199 ldup_rowid ROWID;
200
201 BEGIN
202
203 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
204 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin do_update_fee_assignment procedure');
205 END IF;
206
207 -- check whether record has been updated by another user. If not, lock it.
208 BEGIN
209 SELECT OBJECT_VERSION_NUMBER
210 INTO l_object_version_number
211 FROM LNS_FEE_ASSIGNMENTS
212 WHERE FEE_ASSIGNMENT_ID = p_fee_assignment_rec.fee_assignment_id
213 FOR UPDATE OF FEE_ASSIGNMENT_ID NOWAIT;
214
215 IF NOT
216 (
217 (p_object_version_number IS NULL AND l_object_version_number IS NULL)
218 OR
219 (p_object_version_number IS NOT NULL AND
220 l_object_version_number IS NOT NULL AND
221 p_object_version_number = l_object_version_number
222 )
223 )
224 THEN
225 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_RECORD_CHANGED');
226 FND_MESSAGE.SET_TOKEN('TABLE', 'lns_fee_assignments');
227 FND_MSG_PUB.ADD;
228 RAISE FND_API.G_EXC_ERROR;
229 END IF;
230
231 p_object_version_number := nvl(l_object_version_number, 1) + 1;
232
233 EXCEPTION WHEN NO_DATA_FOUND THEN
234 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_NO_RECORD');
235 FND_MESSAGE.SET_TOKEN('RECORD', 'assignment_rec');
236 FND_MESSAGE.SET_TOKEN('VALUE', NVL(TO_CHAR(p_fee_assignment_rec.fee_assignment_id), 'null'));
237 FND_MSG_PUB.ADD;
238 RAISE FND_API.G_EXC_ERROR;
239 END;
240
241 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
242 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_update_fee_assignment procedure: Before call to LNS_FEE_ASSIGNMENTS_PKG.Update_Row');
243 END IF;
244
245 -- log history
246 LNS_LOAN_HISTORY_PUB.log_record_pre(p_fee_assignment_rec.fee_assignment_id,
247 'FEE_ASSIGNMENT_ID',
248 'LNS_FEE_ASSIGNMENTS');
249
250 --Call to table-handler
251 LNS_FEE_ASSIGNMENTS_PKG.Update_Row (
252 P_FEE_ASSIGNMENT_ID => p_fee_assignment_rec.fee_assignment_id,
253 P_LOAN_ID => p_fee_assignment_rec.LOAN_ID,
254 P_FEE_ID => p_fee_assignment_rec.FEE_ID,
255 P_FEE => p_fee_assignment_rec.FEE,
256 P_FEE_TYPE => p_fee_assignment_rec.fee_type,
257 P_FEE_BASIS => p_fee_assignment_rec.fee_basis,
258 P_NUMBER_GRACE_DAYS => p_fee_assignment_rec.NUMBER_GRACE_DAYS,
259 P_LAST_UPDATED_BY => null,
260 P_LAST_UPDATE_DATE => null,
261 P_LAST_UPDATE_LOGIN => null,
262 P_OBJECT_VERSION_NUMBER => p_OBJECT_VERSION_NUMBER,
263 P_COLLECTED_THIRD_PARTY_FLAG => p_fee_assignment_rec.collected_third_party_flag,
264 P_BILLING_OPTION => p_fee_assignment_rec.billing_option,
265 P_RATE_TYPE => p_fee_assignment_rec.rate_type,
266 P_BEGIN_INSTALLMENT_NUMBER => p_fee_assignment_rec.begin_installment_number,
267 P_END_INSTALLMENT_NUMBER => p_fee_assignment_rec.end_installment_number,
268 P_NUMBER_OF_PAYMENTS => p_fee_assignment_rec.number_of_payments,
269 P_START_DATE_ACTIVE => null,
270 P_END_DATE_ACTIVE => p_fee_assignment_rec.end_date_active,
271 P_DISB_HEADER_ID => p_fee_assignment_rec.disb_header_id,
272 P_DELETE_DISABLED_FLAG => p_fee_assignment_rec.delete_disabled_flag,
273 P_OPEN_PHASE_FLAG => p_fee_assignment_rec.open_phase_flag,
274 P_PHASE => p_fee_assignment_rec.phase,
275 P_CUSTOM_PROCEDURE => p_fee_assignment_rec.custom_procedure
276 );
277
278 -- log record changes
279 LNS_LOAN_HISTORY_PUB.log_record_post(p_fee_assignment_rec.fee_assignment_id,
280 'FEE_ASSIGNMENT_ID',
281 'LNS_FEE_ASSIGNMENTS',
282 p_fee_assignment_rec.loan_id);
283
284 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
285 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_update_fee_assignment procedure: After call to LNS_FEE_ASSIGNMENTS_PKG.Update_Row');
286 END IF;
287
288 END do_update_fee_assignment;
289
290 /*===========================================================================+
291 | PROCEDURE
292 | do_delete_fee_assignment
293 |
294 | DESCRIPTION
295 | Deletes assignment.
296 |
297 | SCOPE - PRIVATE
298 |
299 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
300 |
301 | ARGUMENTS : IN:
302 | OUT:
303 | IN/OUT:
304 | p_fee_assignment_id
305 | p_object_version_number
306 | x_return_status
307 |
308 | RETURNS : NONE
309 |
310 | NOTES
311 |
312 | MODIFICATION HISTORY
313 |
314 | 22-APR-2004 Bernice Lam Created.
315 +===========================================================================*/
316
317 PROCEDURE do_delete_fee_assignment(
318 p_fee_assignment_id NUMBER,
319 x_return_status IN OUT NOCOPY VARCHAR2
320 ) IS
321
322 l_loan_id NUMBER;
323 l_fee_id NUMBER;
324 l_object_version_num NUMBER;
325 BEGIN
326
327 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
328 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin do_delete_fee_assignment procedure');
329 END IF;
330
331 IF p_fee_assignment_id IS NOT NULL AND
332 p_fee_assignment_id <> FND_API.G_MISS_NUM
333 THEN
334 -- check whether record has been deleted by another user. If not, lock it.
335 BEGIN
336 SELECT loan_id, fee_id, object_version_number
337 INTO l_loan_id, l_fee_id, l_object_version_num
338 FROM LNS_FEE_ASSIGNMENTS
339 WHERE FEE_ASSIGNMENT_ID = p_fee_assignment_id;
340
341 EXCEPTION
342 WHEN NO_DATA_FOUND THEN
343 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_NO_RECORD');
344 FND_MESSAGE.SET_TOKEN('RECORD', 'assignment_rec');
345 FND_MESSAGE.SET_TOKEN('VALUE', NVL(TO_CHAR(p_fee_assignment_id), 'null'));
346 FND_MSG_PUB.ADD;
347 RAISE FND_API.G_EXC_ERROR;
348 END;
349 END IF;
350
351 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
352 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_delete_fee_assignment procedure: Before call to LNS_FEE_ASSIGNMENTS_PKG.Delete_Row');
353 END IF;
354
355 -- log history
356 LNS_LOAN_HISTORY_PUB.log_record_pre(p_fee_assignment_id,
357 'FEE_ASSIGNMENT_ID',
358 'LNS_FEE_ASSIGNMENTS');
359
360 BEGIN
361
362 -- Update the end date active before physically deleting the record
363 -- to record it in history table
364
365 UPDATE LNS_FEE_ASSIGNMENTS
366 SET END_DATE_ACTIVE = SYSDATE
367 WHERE LOAN_ID = l_loan_id
368 AND FEE_ASSIGNMENT_ID = p_fee_assignment_id;
369
370
371 -- log record changes
372 LNS_LOAN_HISTORY_PUB.log_record_post(p_fee_assignment_id,
373 'FEE_ASSIGNMENT_ID',
374 'LNS_FEE_ASSIGNMENTS',
375 l_loan_id);
376
377 LNS_FEE_ASSIGNMENTS_PKG.Delete_Row(
378 P_FEE_ASSIGNMENT_ID => p_fee_assignment_id);
379
380
381 EXCEPTION
382 WHEN OTHERS THEN
383 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
384 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
385 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
386 FND_MSG_PUB.ADD;
387 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
388 END;
389
390
391
392 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
393 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_delete_fee_assignment procedure: After call to LNS_FEE_ASSIGNMENTS_PKG.Delete_Row');
394 END IF;
395
396 END do_delete_fee_assignment;
397
398 ----------------------------
399 -- body of public procedures
400 ----------------------------
401
402 /*===========================================================================+
403 | PROCEDURE
404 | create_fee_assignment
405 |
406 | DESCRIPTION
407 | Creates assignment.
408 |
409 | SCOPE - PUBLIC
410 |
411 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
412 |
413 | ARGUMENTS : IN:
414 | p_init_msg_list
415 | p_fee_assignment_rec
416 | OUT:
417 | x_return_status
418 | x_msg_count
419 | x_msg_data
420 | x_fee_assignment_id
421 | IN/OUT:
422 |
423 | RETURNS : NONE
424 |
425 | NOTES
426 |
427 | MODIFICATION HISTORY
428 | 22-APR-2004 Bernice Lam Created.
429 +===========================================================================*/
430
431 PROCEDURE create_fee_assignment (
432 p_init_msg_list IN VARCHAR2,
433 p_fee_assignment_rec IN FEE_ASSIGNMENT_REC_TYPE,
434 x_fee_assignment_id OUT NOCOPY NUMBER,
435 x_return_status OUT NOCOPY VARCHAR2,
436 x_msg_count OUT NOCOPY NUMBER,
437 x_msg_data OUT NOCOPY VARCHAR2
438 ) IS
439
440 l_api_name CONSTANT VARCHAR2(30) := 'create_fee_assignment';
441 l_fee_assignment_rec FEE_ASSIGNMENT_REC_TYPE;
442
443 l_fee_dtls_tbl LNS_FEE_ENGINE.FEE_CALC_TBL ;
444 l_loan_id NUMBER ;
445
446 BEGIN
447
448 l_fee_assignment_rec := p_fee_assignment_rec;
449 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
450 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin Create_fee_assignment procedure');
451 END IF;
452
453 -- standard start of API savepoint
454 SAVEPOINT create_fee_assignment;
455
456 -- initialize message list if p_init_msg_list is set to TRUE.
457 IF FND_API.to_Boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) THEN
458 FND_MSG_PUB.initialize;
459 END IF;
460
461 -- initialize API return status to success.
462 x_return_status := FND_API.G_RET_STS_SUCCESS;
463
464 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
465 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Create_fee_assignment procedure: Before call to do_create_fee_assignment proc');
466 END IF;
467
468 Set_Defaults(l_fee_assignment_rec);
469
470 -- call to business logic.
471 do_create_fee_assignment(
472 l_fee_assignment_rec,
473 x_fee_assignment_id,
474 x_return_status
475 );
476 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
477 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Create_fee_assignment procedure: After call to do_create_fee_assignment proc');
478 END IF;
479
480 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
481 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'THe feeType is '||l_fee_assignment_rec.FEE_TYPE||' and loan_id is '||l_fee_assignment_rec.LOAN_ID);
482 END IF;
483
484 /*
485 -- Bug # 4728114 - Insert Fee Record in lns_fee_schedules for Origination Fees
486 IF( ('EVENT_ORIGINATION'=l_fee_assignment_rec.FEE_TYPE) OR ('EVENT_CONVERSION'=l_fee_assignment_rec.FEE_TYPE) ) THEN
487
488 l_loan_id := l_fee_assignment_rec.LOAN_ID ;
489
490 l_fee_dtls_tbl(1).FEE_ID := l_fee_assignment_rec.FEE_ID ;
491 l_fee_dtls_tbl(1).FEE_AMOUNT := l_fee_assignment_rec.FEE;
492 l_fee_dtls_tbl(1).FEE_INSTALLMENT := l_fee_assignment_rec.BEGIN_INSTALLMENT_NUMBER ;
493 l_fee_dtls_tbl(1).BILLED_FLAG := 'N' ;
494 l_fee_dtls_tbl(1).ACTIVE_FLAG := 'Y' ;
495 l_fee_dtls_tbl(1).PHASE := l_fee_assignment_rec.PHASE;
496
497 lns_fee_engine.writefeeschedule('T','T',l_loan_id,l_fee_dtls_tbl,x_return_status,x_msg_count,x_msg_data);
498
499
500 END IF ;
501
502 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
503 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Create_fee_assignment procedure: After call to do_create_fee_assignment proc');
504 END IF;
505 */
506 EXCEPTION
507 WHEN FND_API.G_EXC_ERROR THEN
508 ROLLBACK TO create_fee_assignment;
509 x_return_status := FND_API.G_RET_STS_ERROR;
510 FND_MSG_PUB.Count_And_Get(
511 p_encoded => FND_API.G_FALSE,
512 p_count => x_msg_count,
513 p_data => x_msg_data);
514
515 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
516 ROLLBACK TO create_fee_assignment;
517 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
518 FND_MSG_PUB.Count_And_Get(
519 p_encoded => FND_API.G_FALSE,
520 p_count => x_msg_count,
521 p_data => x_msg_data);
522
523 WHEN OTHERS THEN
524 ROLLBACK TO create_fee_assignment;
525 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
526 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
527 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
528 FND_MSG_PUB.ADD;
529 FND_MSG_PUB.Count_And_Get(
530 p_encoded => FND_API.G_FALSE,
531 p_count => x_msg_count,
532 p_data => x_msg_data);
533
534 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
535 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'End Create_fee_assignment procedure');
536 END IF;
537
538 END create_fee_assignment;
539
540 /*===========================================================================+
541 | PROCEDURE
542 | update_fee_assignment
543 |
544 | DESCRIPTION
545 | Updates assignment.
546 |
547 | SCOPE - PUBLIC
548 |
549 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
550 |
551 | ARGUMENTS : IN:
552 | p_init_msg_list
553 | p_fee_assignment_rec
554 | OUT:
555 | x_return_status
556 | x_msg_count
557 | x_msg_data
558 | IN/OUT:
559 | p_object_version_number
560 |
561 | RETURNS : NONE
562 |
563 | NOTES
564 |
565 | MODIFICATION HISTORY
566 | 22-APR-2004 Bernice Lam Created
567 +===========================================================================*/
568
569 PROCEDURE update_fee_assignment (
570 p_init_msg_list IN VARCHAR2,
571 p_fee_assignment_rec IN FEE_ASSIGNMENT_REC_TYPE,
572 p_object_version_number IN OUT NOCOPY NUMBER,
573 x_return_status OUT NOCOPY VARCHAR2,
574 x_msg_count OUT NOCOPY NUMBER,
575 x_msg_data OUT NOCOPY VARCHAR2
576 ) IS
577
578 l_api_name CONSTANT VARCHAR2(30) := 'update_fee_assignment';
579 l_fee_assignment_rec FEE_ASSIGNMENT_REC_TYPE;
580 l_old_fee_assignment_rec FEE_ASSIGNMENT_REC_TYPE;
581
582 BEGIN
583
584 l_fee_assignment_rec := p_fee_assignment_rec;
585 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
586 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin Update_fee_assignment procedure');
587 END IF;
588
589 -- standard start of API savepoint
590 SAVEPOINT update_fee_assignment;
591
592 -- initialize message list if p_init_msg_list is set to TRUE.
593 IF FND_API.to_Boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) THEN
594 FND_MSG_PUB.initialize;
595 END IF;
596
597 -- initialize API return status to success.
598 x_return_status := FND_API.G_RET_STS_SUCCESS;
599 /*
600 -- Get old record. Will be used by history package.
601 get_fee_assignment_rec (
602 p_fee_assignment_id => l_fee_assignment_rec.fee_assignment_id,
603 x_fee_assignment_rec => l_old_fee_assignment_rec,
604 x_return_status => x_return_status,
605 x_msg_count => x_msg_count,
606 x_msg_data => x_msg_data );
607 */
608 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
609 RAISE FND_API.G_EXC_ERROR;
610 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
611 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
612 END IF;
613
614 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
615 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Update_fee_assignment procedure: Before call to do_update_fee_assignment proc');
616 END IF;
617
618 Set_Defaults(l_fee_assignment_rec);
619
620 -- call to business logic.
621 do_update_fee_assignment(
622 l_fee_assignment_rec,
623 p_object_version_number,
624 x_return_status
625 );
626
627 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
628 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Update_fee_assignment procedure: After call to do_update_fee_assignment proc');
629 END IF;
630
631 EXCEPTION
632 WHEN FND_API.G_EXC_ERROR THEN
633 ROLLBACK TO update_fee_assignment;
634 x_return_status := FND_API.G_RET_STS_ERROR;
635 FND_MSG_PUB.Count_And_Get(
636 p_encoded => FND_API.G_FALSE,
637 p_count => x_msg_count,
638 p_data => x_msg_data);
639
640 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
641 ROLLBACK TO update_fee_assignment;
642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
643 FND_MSG_PUB.Count_And_Get(
644 p_encoded => FND_API.G_FALSE,
645 p_count => x_msg_count,
646 p_data => x_msg_data);
647
648 WHEN OTHERS THEN
649 ROLLBACK TO update_fee_assignment;
650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
651 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
652 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
653 FND_MSG_PUB.ADD;
654 FND_MSG_PUB.Count_And_Get(
655 p_encoded => FND_API.G_FALSE,
656 p_count => x_msg_count,
657 p_data => x_msg_data);
658
659 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
660 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'End Update_fee_assignment procedure');
661 END IF;
662
663 END update_fee_assignment;
664
665 /*===========================================================================+
666 | PROCEDURE
667 | delete_fee_assignment
668 |
669 | DESCRIPTION
670 | Deletes assignment.
671 |
672 | SCOPE - PUBLIC
673 |
674 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
675 |
676 | ARGUMENTS : IN:
677 | p_init_msg_list
678 | p_fee_assignment_id
679 | OUT:
680 | x_return_status
681 | x_msg_count
682 | x_msg_data
683 | IN/OUT:
684 |
685 | RETURNS : NONE
686 |
687 | NOTES
688 |
689 | MODIFICATION HISTORY
690 | 22-APR-2004 Bernice Lam Created.
691 +===========================================================================*/
692
693 PROCEDURE delete_fee_assignment (
694 p_init_msg_list IN VARCHAR2,
695 p_fee_assignment_id IN NUMBER,
696 x_return_status OUT NOCOPY VARCHAR2,
697 x_msg_count OUT NOCOPY NUMBER,
698 x_msg_data OUT NOCOPY VARCHAR2
699 ) IS
700
701 l_api_name CONSTANT VARCHAR2(30) := 'delete_fee_assignment';
702 l_fee_assignment_id NUMBER;
703
704 BEGIN
705
706 l_fee_assignment_id := p_fee_assignment_id;
707 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
708 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin Delete_fee_assignment procedure');
709 END IF;
710
711 -- standard start of API savepoint
712 SAVEPOINT delete_fee_assignment;
713
714 -- initialize message list if p_init_msg_list is set to TRUE.
715 IF FND_API.to_Boolean(p_init_msg_list) THEN
716 FND_MSG_PUB.initialize;
717 END IF;
718
719 -- initialize API return status to success.
720 x_return_status := FND_API.G_RET_STS_SUCCESS;
721
722 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
723 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Delete_fee_assignment procedure: Before call to do_delete_fee_assignment proc');
724 END IF;
725
726 -- call to business logic.
727 do_delete_fee_assignment(
728 l_fee_assignment_id,
729 x_return_status
730 );
731
732 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
733 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Delete_fee_assignment procedure: After call to do_delete_fee_assignment proc');
734 END IF;
735
736 EXCEPTION
737 WHEN FND_API.G_EXC_ERROR THEN
738 ROLLBACK TO delete_fee_assignment;
739 x_return_status := FND_API.G_RET_STS_ERROR;
740 FND_MSG_PUB.Count_And_Get(
741 p_encoded => FND_API.G_FALSE,
742 p_count => x_msg_count,
743 p_data => x_msg_data);
744
745 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
746 ROLLBACK TO delete_fee_assignment;
747 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
748 FND_MSG_PUB.Count_And_Get(
749 p_encoded => FND_API.G_FALSE,
750 p_count => x_msg_count,
751 p_data => x_msg_data);
752
753 WHEN OTHERS THEN
754 ROLLBACK TO delete_fee_assignment;
755 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
756 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
757 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
758 FND_MSG_PUB.ADD;
759 FND_MSG_PUB.Count_And_Get(
760 p_encoded => FND_API.G_FALSE,
761 p_count => x_msg_count,
762 p_data => x_msg_data);
763
764 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
765 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'End Delete_fee_assignment procedure');
766 END IF;
767
768 END delete_fee_assignment;
769
770 FUNCTION IS_EXIST_FEE_ASSIGNMENT (
771 p_fee_id NUMBER
772 ) RETURN VARCHAR2 IS
773
774 CURSOR C_Is_Exist_Assignment (X_Fee_Id NUMBER) IS
775 SELECT 'X' FROM DUAL
776 WHERE EXISTS ( SELECT NULL FROM LNS_FEE_ASSIGNMENTS
777 WHERE FEE_ID = X_FEE_ID )
778 OR EXISTS ( SELECT NULL FROM LNS_LOAN_PRODUCT_LINES
779 WHERE LINE_REFERENCE_ID = X_FEE_ID
780 AND ( LOAN_PRODUCT_LINE_TYPE = 'FEE' OR LOAN_PRODUCT_LINE_TYPE='DISB_FEE' )
781 );
782
783
784 l_dummy VARCHAR2(1);
785
786 BEGIN
787
788 OPEN C_Is_Exist_Assignment (p_fee_id);
789 FETCH C_Is_Exist_Assignment INTO l_dummy;
790 IF C_Is_Exist_Assignment%FOUND THEN
791 CLOSE C_Is_Exist_Assignment;
792 RETURN 'Y';
793 END IF;
794 CLOSE C_Is_Exist_Assignment;
795 RETURN 'N';
796
797 END IS_EXIST_FEE_ASSIGNMENT;
798
799
800
801 -- Bug#6830765 Handled for billOptions SUBMIT_FOR_APPROVAL and AD_HOC
802 PROCEDURE create_LP_FEE_ASSIGNMENT(
803 P_LOAN_ID IN NUMBER ) IS
804
805 CURSOR loan_prod_fee ( c_loan_id NUMBER ) IS
806 select LNS_FEE_ASSIGNMENTS_S.NEXTVAL FEE_ASSIGNMENT_ID,
807 LnsLoanHeaders.LOAN_ID,
808 LnsFees.FEE_ID,
809 --decode(LnsFees.RATE_TYPE,'VARIABLE', lns_fee_engine.calculateFee(LnsFees.FEE_ID,LnsLoanHeaders.LOAN_ID) , LnsFees.FEE) FEE,
810 LnsFees.FEE,
811 LnsFees.FEE_TYPE,
812 LnsFees.FEE_BASIS,
813 LnsFees.NUMBER_GRACE_DAYS,
814 LnsFees.COLLECTED_THIRD_PARTY_FLAG,
815 LnsFees.RATE_TYPE,
816 decode(LnsFees.BILLING_OPTION,'ORIGINATION',0,
817 'SUBMIT_FOR_APPROVAL',0,
818 'AD_HOC',0,
819 'TERM_CONVERSION',0,
820 'BILL_WITH_INSTALLMENT',1,
821 (decode(LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER_EXT(LnsLoanHeaders.LOAN_ID) ,
822 -1 , 0 , LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER_EXT(LnsLoanHeaders.LOAN_ID)) + 1 )
823 ) BEGIN_INSTALLMENT_NUMBER,
824 decode(LnsFees.BILLING_OPTION,'ORIGINATION',0,
825 'SUBMIT_FOR_APPROVAL',0,
826 'AD_HOC',0,
827 'TERM_CONVERSION',0,
828 'BILL_WITH_INSTALLMENT',decode(LnsFees.fee_type, 'EVENT_LATE_CHARGE', lns_fin_utils.getnumberinstallments(LnsLoanHeaders.LOAN_ID), 1 ),
829 lns_fin_utils.getnumberinstallments(LnsLoanHeaders.LOAN_ID)) END_INSTALLMENT_NUMBER,
830
831 NULL NUMBER_OF_PAYMENTS,
832 LnsFees.BILLING_OPTION,
833 NULL CREATED_BY,
834 NULL CREATION_DATE,
835 NULL LAST_UPDATED_BY,
836 NULL LAST_UPDATE_DATE,
837 NULL LAST_UPDATE_LOGIN,
838 1 OBJECT_VERSION_NUMBER,
839 sysdate START_DATE_ACTIVE,
840 NULL END_DATE_ACTIVE,
841 NULL DISB_HEADER_ID,
842 LnsLoanProductLines.MANDATORY_FLAG,
843 NULL OPEN_PHASE_FLAG, -- We don't use this flag anywhere
844 NULL PHASE,
845 LnsFees.CUSTOM_PROCEDURE
846 FROM LNS_FEES LnsFees ,
847 LNS_LOAN_HEADERS LnsLoanHeaders ,
848 LNS_LOAN_PRODUCT_LINES LnsLoanProductLines
849
850 WHERE LnsLoanHeaders.LOAN_ID = c_loan_id
851 AND LnsLoanHeaders.PRODUCT_ID = LnsLoanProductLines.LOAN_PRODUCT_ID
852 AND LnsLoanProductLines.LOAN_PRODUCT_LINE_TYPE = 'FEE'
853 AND LnsLoanProductLines.LINE_REFERENCE_ID = LnsFees.FEE_ID ;
854
855 CURSOR fee_account_lines ( c_fee_id NUMBER ) IS
856 SELECT LINE_TYPE, ACCOUNT_NAME, CODE_COMBINATION_ID, ACCOUNT_TYPE, DISTRIBUTION_PERCENT, DISTRIBUTION_TYPE
857 FROM LNS_DEFAULT_DISTRIBS
858 WHERE ACCOUNT_NAME = 'FEE_RECEIVABLE' OR FEE_ID = c_fee_id ;
859
860
861 CURSOR current_loan_status ( c_loan_id NUMBER ) IS
862 SELECT LOAN_STATUS , CURRENT_PHASE
863 FROM LNS_LOAN_HEADERS LnsLoanHeaders
864 WHERE LnsLoanHeaders.LOAN_ID = c_loan_id ;
865
866
867 CURSOR loan_fee_exists ( c_loan_id NUMBER ) IS
868 SELECT 'Y'
869 FROM DUAL
870 WHERE
871 EXISTS
872 (SELECT NULL FROM LNS_FEE_ASSIGNMENTS LnsFeeAssignments
873 WHERE LnsFeeAssignments.LOAN_ID = c_loan_id)
874 OR EXISTS
875 (SELECT NULL FROM LNS_LOAN_HISTORIES_H
876 WHERE TABLE_NAME = 'LNS_FEE_ASSIGNMENTS' AND LOAN_ID = c_loan_id) ;
877
878
879
880 l_fee_assignment_rec fee_assignment_rec_type ;
881 l_fee_assignment_id NUMBER ;
882 x_return_status VARCHAR2(1) ;
883 l_loan_status LNS_LOAN_HEADERS.LOAN_STATUS%TYPE ;
884 l_loan_Current_phase LNS_LOAN_HEADERS.CURRENT_PHASE%TYPE ;
885 l_loan_fee_exists VARCHAR2(1) ;
886
887 l_line_type LNS_DEFAULT_DISTRIBS.LINE_TYPE%TYPE ;
888 l_account_name LNS_DEFAULT_DISTRIBS.ACCOUNT_NAME%TYPE ;
889 l_code_combination_id LNS_DEFAULT_DISTRIBS.CODE_COMBINATION_ID%TYPE ;
890 l_account_type LNS_DEFAULT_DISTRIBS.ACCOUNT_TYPE%TYPE ;
891 l_distribution_percent LNS_DEFAULT_DISTRIBS.DISTRIBUTION_PERCENT%TYPE ;
892 l_distribution_type LNS_DEFAULT_DISTRIBS.DISTRIBUTION_TYPE%TYPE ;
893
894 is_commit_needed BOOLEAN;
895 l_msg_count NUMBER;
896 l_msg_data VARCHAR2(32767);
897
898
899 BEGIN
900
901 --Initialize this variable to false. Change to true when a record is
902 --inserted into the table in this procedure
903 is_commit_needed := FALSE;
904
905 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
906 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin create_LP_FEE_ASSIGNMENT procedure');
907 END IF;
908
909 -- standard start of API savepoint
910 SAVEPOINT create_LP_FEE_ASSIGNMENT ;
911
912
913 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
914 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_FEE_ASSIGNMENT procedure: Before opening cursor current_loan_status ');
915 END IF;
916
917 OPEN current_loan_status(P_LOAN_ID) ;
918
919 FETCH current_loan_status INTO l_loan_status ,l_loan_Current_phase ;
920
921 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
922 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_FEE_ASSIGNMENT procedure: After opening cursor current_loan_status , loan status is '||l_loan_status ||' loan current phase is '||l_loan_Current_phase);
923 END IF;
924
925 /* If the loan current phase is not open or loan status is not Incomplete for Term loan , no fees assignment required */
926 IF( NOT ( ( l_loan_status='INCOMPLETE' AND l_loan_current_phase = 'TERM' ) OR ( l_loan_current_phase = 'OPEN' ) ) ) THEN
927 RETURN ;
928 END IF ;
929
930
931
932
933 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
934 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_FEE_ASSIGNMENT procedure: Before opening cursor loan_fee_exists ');
935 END IF;
936
937 OPEN loan_fee_exists(P_LOAN_ID) ;
938
939 FETCH loan_fee_exists INTO l_loan_fee_exists ;
940
941 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
942 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_FEE_ASSIGNMENT procedure: After opening cursor loan_fee_exists , loan fee exist status is ' || l_loan_fee_exists );
943 END IF;
944
945 /* If the loan fee count is not zero and there are already fees assigned to loan, no fees assignment required */
946 IF( l_loan_fee_exists = 'Y' ) THEN
947 RETURN ;
948 END IF ;
949
950
951
952 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
953 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_FEE_ASSIGNMENT procedure: Before opening cursor loan_prod_fee ');
954 END IF;
955
956 OPEN loan_prod_fee(P_LOAN_ID) ;
957
958 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
959 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_FEE_ASSIGNMENT procedure: After opening cursor loan_prod_fee , no of fees found is '||loan_prod_fee%ROWCOUNT);
960 END IF;
961
962
963 LOOP
964
965 FETCH loan_prod_fee INTO l_fee_assignment_rec ;
966 EXIT WHEN loan_prod_fee%NOTFOUND ;
967
968 l_fee_assignment_id := l_fee_assignment_rec.fee_assignment_id ;
969
970
971 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
972 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_FEE_ASSIGNMENT procedure: Before call to do_create_FEE_ASSIGNMENT proc for fee '||l_fee_assignment_rec.FEE_ID );
973 END IF;
974
975 IF (l_loan_current_phase = 'OPEN'
976 AND ( (l_fee_assignment_rec.FEE_TYPE = 'EVENT_ORIGINATION')
977 OR ( l_fee_assignment_rec.FEE_TYPE = 'EVENT_FUNDING')
978 )
979 ) THEN
980
981 l_fee_assignment_rec.phase := 'OPEN';
982 ELSE
983 l_fee_assignment_rec.phase := 'TERM';
984 END IF;
985
986 -- call to business logic.
987 --do_create_FEE_ASSIGNMENT( l_fee_assignment_rec ,
988 -- l_fee_assignment_id ,
989 -- x_return_status ) ;
990 -- Bug # 4728114 change to call from do_create_FEE_ASSIGNMENT to create_fee_assignment
991 create_fee_assignment('T',l_fee_assignment_rec,l_fee_assignment_id,x_return_status,l_msg_count,l_msg_data);
992
993
994 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
995 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_FEE_ASSIGNMENT procedure: After call to do_create_FEE_ASSIGNMENT proc for fee'|| l_fee_assignment_rec.FEE_ID ||' , return status is' || x_return_status);
996 END IF;
997
998
999 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1000 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_FEE_ASSIGNMENT procedure: Before opening cursor fee_account_lines ');
1001 END IF;
1002
1003 OPEN fee_account_lines(l_fee_assignment_rec.fee_id) ;
1004
1005 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1006 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_FEE_ASSIGNMENT procedure: After opening cursor fee_account_lines , no of accounting lines found is '||fee_account_lines%ROWCOUNT);
1007 END IF;
1008
1009
1010 LOOP
1011
1012 FETCH fee_account_lines INTO l_line_type , l_account_name, l_code_combination_id, l_account_type , l_distribution_percent , l_distribution_type ;
1013
1014 EXIT WHEN fee_account_lines%NOTFOUND ;
1015
1016 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1017 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_FEE_ASSIGNMENT procedure: Before inserting to lns_distributions record for accountint name '||l_account_name || ' and code comb id ' || l_code_combination_id);
1018 END IF;
1019
1020 Insert into lns_distributions
1021 (DISTRIBUTION_ID
1022 ,LOAN_ID
1023 ,LINE_TYPE
1024 ,ACCOUNT_NAME
1025 ,CODE_COMBINATION_ID
1026 ,ACCOUNT_TYPE
1027 ,DISTRIBUTION_PERCENT
1028 ,DISTRIBUTION_TYPE
1029 ,FEE_ID
1030 ,CREATION_DATE
1031 ,CREATED_BY
1032 ,LAST_UPDATE_DATE
1033 ,LAST_UPDATED_BY
1034 ,OBJECT_VERSION_NUMBER )
1035 values
1036 (LNS_DISTRIBUTIONS_S.nextval
1037 ,p_loan_id
1038 ,l_line_type
1039 ,l_account_name
1040 ,l_code_combination_id
1041 ,l_account_type
1042 ,l_distribution_percent
1043 ,l_distribution_type
1044 ,l_fee_assignment_rec.fee_id
1045 ,lns_utility_pub.creation_date
1046 ,lns_utility_pub.created_by
1047 ,lns_utility_pub.last_update_date
1048 ,lns_utility_pub.last_updated_by
1049 ,1) ;
1050
1051 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1052 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_FEE_ASSIGNMENT procedure: After inserting to lns_distributions record for accountint name '||l_account_name || ' and code comb id ' || l_code_combination_id);
1053 END IF;
1054
1055 END LOOP ;
1056
1057 CLOSE fee_account_lines ;
1058
1059 is_commit_needed := true;
1060
1061 END LOOP ;
1062
1063 --If records have been inserted into lns_fee_assignments table
1064 --they need to be committed since the commit does not happen on the UI
1065 --unless the user explicitly commits from the UI page
1066 IF (is_commit_needed = TRUE) THEN
1067 COMMIT WORK;
1068 END IF;
1069
1070 EXCEPTION
1071
1072 WHEN OTHERS THEN
1073 ROLLBACK TO create_LP_FEE_ASSIGNMENT ;
1074
1075 END create_LP_FEE_ASSIGNMENT ;
1076
1077
1078 PROCEDURE create_LP_DISB_FEE_ASSIGNMENT(
1079 P_DISB_HEADER_ID IN NUMBER , P_LOAN_PRODUCT_LINE_ID IN NUMBER, P_LOAN_ID IN NUMBER ) IS
1080
1081 CURSOR loan_prod_disb_fee ( c_disb_header_id NUMBER , c_loan_prod_line_id NUMBER, c_loan_id NUMBER ) IS
1082 select LNS_FEE_ASSIGNMENTS_S.NEXTVAL FEE_ASSIGNMENT_ID,
1083 c_loan_id LOAN_ID,
1084 LnsFees.FEE_ID,
1085 LnsFees.FEE,
1086 LnsFees.FEE_TYPE,
1087 LnsFees.FEE_BASIS,
1088 LnsFees.NUMBER_GRACE_DAYS,
1089 LnsFees.COLLECTED_THIRD_PARTY_FLAG,
1090 LnsFees.RATE_TYPE,
1091 decode(LnsFees.BILLING_OPTION,'ORIGINATION',0,
1092 'SUBMIT_FOR_APPROVAL',0,
1093 'BILL_WITH_INSTALLMENT',1,
1094 (decode(LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER_EXT((select loan_id from lns_disb_headers where disb_header_id = c_disb_header_id )) ,
1095 -1 , 0 , LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER_EXT((select loan_id from lns_disb_headers where disb_header_id = c_disb_header_id ))) + 1 )
1096 ) BEGIN_INSTALLMENT_NUMBER,
1097 decode(LnsFees.BILLING_OPTION,'ORIGINATION',0,
1098 'SUBMIT_FOR_APPROVAL',0,
1099 'BILL_WITH_INSTALLMENT',1,
1100 lns_fin_utils.getnumberinstallments((select loan_id from lns_disb_headers where disb_header_id = c_disb_header_id )) ) END_INSTALLMENT_NUMBER,
1101
1102 NULL NUMBER_OF_PAYMENTS,
1103 LnsFees.BILLING_OPTION,
1104 NULL CREATED_BY,
1105 NULL CREATION_DATE,
1106 NULL LAST_UPDATED_BY,
1107 NULL LAST_UPDATE_DATE,
1108 NULL LAST_UPDATE_LOGIN,
1109 1 OBJECT_VERSION_NUMBER,
1110 sysdate START_DATE_ACTIVE,
1111 NULL END_DATE_ACTIVE,
1112 c_disb_header_id DISB_HEADER_ID,
1113 LnsLoanProductLines.MANDATORY_FLAG,
1114 NULL OPEN_PHASE_FLAG,
1115 (select phase from lns_disb_headers where disb_header_id = c_disb_header_id) PHASE,
1116 LnsFees.CUSTOM_PROCEDURE
1117 FROM LNS_FEES LnsFees ,
1118 LNS_LOAN_PRODUCT_LINES LnsLoanProductLines
1119
1120 WHERE LnsLoanProductLines.PARENT_PRODUCT_LINES_ID = c_loan_prod_line_id
1121 AND LnsLoanProductLines.LOAN_PRODUCT_LINE_TYPE = 'DISB_FEE'
1122 AND LnsLoanProductLines.LINE_REFERENCE_ID = LnsFees.FEE_ID ;
1123
1124 CURSOR fee_account_lines ( c_fee_id NUMBER ) IS
1125 SELECT LINE_TYPE, ACCOUNT_NAME, CODE_COMBINATION_ID, ACCOUNT_TYPE, DISTRIBUTION_PERCENT, DISTRIBUTION_TYPE
1126 FROM LNS_DEFAULT_DISTRIBS
1127 WHERE ACCOUNT_NAME = 'FEE_RECEIVABLE' OR FEE_ID = c_fee_id ;
1128
1129
1130 CURSOR current_loan_status ( c_loan_id NUMBER ) IS
1131 SELECT LOAN_STATUS , CURRENT_PHASE
1132 FROM LNS_LOAN_HEADERS LnsLoanHeaders
1133 WHERE LnsLoanHeaders.LOAN_ID = c_loan_id ;
1134
1135
1136 CURSOR l_fee_acct_line_exists ( c_loan_id NUMBER ) IS
1137 SELECT 'Y'
1138 FROM DUAL
1139 WHERE
1140 EXISTS
1141 (SELECT * FROM LNS_DISTRIBUTIONS lnsDistribs
1142 WHERE lnsDistribs.ACCOUNT_NAME = 'FEE_RECEIVABLE' AND lnsDistribs.LOAN_ID = c_loan_id);
1143
1144
1145 l_fee_assignment_rec fee_assignment_rec_type ;
1146 l_fee_assignment_id NUMBER ;
1147 x_return_status VARCHAR2(1) ;
1148 l_loan_status LNS_LOAN_HEADERS.LOAN_STATUS%TYPE ;
1149 l_loan_current_phase LNS_LOAN_HEADERS.CURRENT_PHASE%TYPE ;
1150 l_loan_fee_acct_line_exists VARCHAR2(1);
1151
1152 l_line_type LNS_DEFAULT_DISTRIBS.LINE_TYPE%TYPE ;
1153 l_account_name LNS_DEFAULT_DISTRIBS.ACCOUNT_NAME%TYPE ;
1154 l_code_combination_id LNS_DEFAULT_DISTRIBS.CODE_COMBINATION_ID%TYPE ;
1155 l_account_type LNS_DEFAULT_DISTRIBS.ACCOUNT_TYPE%TYPE ;
1156 l_distribution_percent LNS_DEFAULT_DISTRIBS.DISTRIBUTION_PERCENT%TYPE ;
1157 l_distribution_type LNS_DEFAULT_DISTRIBS.DISTRIBUTION_TYPE%TYPE ;
1158
1159 is_commit_needed BOOLEAN;
1160 l_msg_count NUMBER;
1161 l_msg_data VARCHAR2(32767);
1162
1163 BEGIN
1164
1165
1166 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1167 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin create_LP_DISB_FEE_ASSIGNMENT procedure');
1168 END IF;
1169
1170 -- standard start of API savepoint
1171 SAVEPOINT create_LP_DISB_FEE_ASSIGNMENT ;
1172
1173 l_loan_fee_acct_line_exists := 'N';
1174
1175 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1176 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_DISB_FEE_ASSIGNMENT procedure: Before opening cursor current_loan_status ');
1177 END IF;
1178
1179 OPEN current_loan_status(P_LOAN_ID) ;
1180
1181 FETCH current_loan_status INTO l_loan_status ,l_loan_Current_phase ;
1182
1183 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1184 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_DISB_FEE_ASSIGNMENT procedure: After opening cursor current_loan_status , loan status is '||l_loan_status ||' loan current phase is '||l_loan_Current_phase);
1185 END IF;
1186
1187 /* If the loan current phase is not open or loan status is not Incomplete for Term loan , no disb fees assignment required */
1188 IF( NOT ( ( l_loan_status='INCOMPLETE' AND l_loan_current_phase = 'TERM' ) OR ( l_loan_current_phase = 'OPEN' ) ) ) THEN
1189 RETURN ;
1190 END IF ;
1191
1192 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1193 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_DISB_FEE_ASSIGNMENT procedure: Before opening cursor l_fee_acct_line_exists ');
1194 END IF;
1195
1196 OPEN l_fee_acct_line_exists(P_LOAN_ID) ;
1197
1198 FETCH l_fee_acct_line_exists INTO l_loan_fee_acct_line_exists ;
1199
1200 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1201 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_DISB_FEE_ASSIGNMENT procedure: After opening cursor loan_fee_exists , loan fee exist status is ' || l_loan_fee_acct_line_exists );
1202 END IF;
1203
1204
1205
1206 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1207 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_DISB_FEE_ASSIGNMENT procedure: Before opening cursor loan_prod_disb_fee ');
1208 END IF;
1209
1210 OPEN loan_prod_disb_fee(P_DISB_HEADER_ID , P_LOAN_PRODUCT_LINE_ID, P_LOAN_ID) ;
1211
1212 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1213 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_DISB_FEE_ASSIGNMENT procedure: After opening cursor loan_prod_disb_fee , no of fees found for disb_header '||p_disb_header_id||' are '||loan_prod_disb_fee%ROWCOUNT);
1214 END IF;
1215
1216
1217 LOOP
1218
1219 FETCH loan_prod_disb_fee INTO l_fee_assignment_rec ;
1220 EXIT WHEN loan_prod_disb_fee%NOTFOUND ;
1221
1222 l_fee_assignment_id := l_fee_assignment_rec.fee_assignment_id ;
1223
1224 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1225 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_DISB_FEE_ASSIGNMENT procedure: Before call to do_create_FEE_ASSIGNMENT proc for fee '||l_fee_assignment_rec.FEE_ID );
1226 END IF;
1227
1228 -- call to business logic.
1229 do_create_FEE_ASSIGNMENT( l_fee_assignment_rec ,
1230 l_fee_assignment_id ,
1231 x_return_status ) ;
1232
1233 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1234 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_DISB_FEE_ASSIGNMENT procedure: After call to do_create_FEE_ASSIGNMENT proc for fee'|| l_fee_assignment_rec.FEE_ID ||' , return status is' || x_return_status);
1235 END IF;
1236
1237
1238 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1239 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_DISB_FEE_ASSIGNMENT procedure: Before opening cursor fee_account_lines ');
1240 END IF;
1241
1242 OPEN fee_account_lines(l_fee_assignment_rec.fee_id) ;
1243
1244 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1245 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_DISB_FEE_ASSIGNMENT procedure: After opening cursor fee_account_lines , no of accounting lines found is '||fee_account_lines%ROWCOUNT);
1246 END IF;
1247
1248
1249 LOOP
1250 FETCH fee_account_lines INTO l_line_type , l_account_name, l_code_combination_id, l_account_type , l_distribution_percent , l_distribution_type ;
1251
1252 EXIT WHEN fee_account_lines%NOTFOUND ;
1253
1254 /* Only one set of fee account lines of type 'FEE_RECEIVBALE' should be there */
1255 IF( l_loan_fee_acct_line_exists <> 'Y' OR l_account_name <> 'FEE_RECEIVABLE') THEN
1256
1257 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1258 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_DISB_FEE_ASSIGNMENT procedure: Before inserting to lns_distributions record for accountint name '||l_account_name || ' and code comb id ' || l_code_combination_id);
1259 END IF;
1260
1261 Insert into lns_distributions
1262 (DISTRIBUTION_ID
1263 ,LOAN_ID
1264 ,LINE_TYPE
1265 ,ACCOUNT_NAME
1266 ,CODE_COMBINATION_ID
1267 ,ACCOUNT_TYPE
1268 ,DISTRIBUTION_PERCENT
1269 ,DISTRIBUTION_TYPE
1270 ,FEE_ID
1271 ,CREATION_DATE
1272 ,CREATED_BY
1273 ,LAST_UPDATE_DATE
1274 ,LAST_UPDATED_BY
1275 ,OBJECT_VERSION_NUMBER
1276 ,DISB_HEADER_ID )
1277 values
1278 (LNS_DISTRIBUTIONS_S.nextval
1279 ,p_loan_id
1280 ,l_line_type
1281 ,l_account_name
1282 ,l_code_combination_id
1283 ,l_account_type
1284 ,l_distribution_percent
1285 ,l_distribution_type
1286 ,l_fee_assignment_rec.fee_id
1287 ,lns_utility_pub.creation_date
1288 ,lns_utility_pub.created_by
1289 ,lns_utility_pub.last_update_date
1290 ,lns_utility_pub.last_updated_by
1291 ,1
1292 ,p_disb_header_id) ;
1293
1294 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1295 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_DISB_FEE_ASSIGNMENT procedure: After inserting to lns_distributions record for accounting name '||l_account_name || ' and code comb id ' || l_code_combination_id);
1296 END IF;
1297
1298 END IF;
1299
1300 END LOOP ;
1301
1302 IF (l_account_name = 'FEE_RECEIVABLE') THEN
1303 l_loan_fee_acct_line_exists := 'Y';
1304 END IF;
1305
1306 CLOSE fee_account_lines ;
1307
1308 END LOOP ;
1309
1310
1311 --If records have been inserted into lns_fee_assignments table
1312 --they need to be committed since the commit does not happen on the UI
1313 --unless the user explicitly commits from the UI page
1314 --IF (is_commit_needed = TRUE) THEN
1315 --COMMIT WORK;
1316 --END IF;
1317
1318 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1319 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'End create_LP_DISB_FEE_ASSIGNMENT procedure - ');
1320 END IF;
1321
1322 EXCEPTION
1323
1324 WHEN OTHERS THEN
1325 ROLLBACK TO create_LP_DISB_FEE_ASSIGNMENT ;
1326
1327 END create_LP_DISB_FEE_ASSIGNMENT ;
1328
1329
1330
1331 PROCEDURE delete_DISB_FEE_ASSIGNMENT(P_DISB_HEADER_ID IN NUMBER ) IS
1332
1333 CURSOR loan_disb_fee ( c_disb_header_id NUMBER ) IS
1334 select FEE_ASSIGNMENT_ID
1335 FROM LNS_FEE_ASSIGNMENTS LnsFeeAssignments
1336 WHERE LnsFeeAssignments.DISB_HEADER_ID = c_disb_header_id ;
1337
1338 l_fee_assignment_id NUMBER ;
1339 x_return_status VARCHAR2(1) ;
1340
1341 BEGIN
1342
1343
1344 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1345 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin delete_DISB_FEE_ASSIGNMENT procedure');
1346 END IF;
1347
1348 -- standard start of API savepoint
1349 SAVEPOINT delete_DISB_FEE_ASSIGNMENT ;
1350
1351
1352 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1353 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In delete_DISB_FEE_ASSIGNMENT procedure: Before opening cursor loan_disb_fee ');
1354 END IF;
1355
1356 OPEN loan_disb_fee(P_DISB_HEADER_ID) ;
1357
1358 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1359 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In delete_DISB_FEE_ASSIGNMENT procedure: After opening cursor loan_disb_fee , no of fees found is '||loan_disb_fee%ROWCOUNT);
1360 END IF;
1361
1362
1363 LOOP
1364
1365 FETCH loan_disb_fee INTO l_fee_assignment_id ;
1366 EXIT WHEN loan_disb_fee%NOTFOUND ;
1367
1368
1369
1370 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1371 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In delete_DISB_FEE_ASSIGNMENT procedure: Before call to do_delete_fee_assignment proc for fee_assignment_id '||l_fee_assignment_id );
1372 END IF;
1373
1374 -- call to business logic.
1375 do_delete_fee_assignment( l_fee_assignment_id ,
1376 x_return_status );
1377
1378
1379 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1380 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In delete_DISB_FEE_ASSIGNMENT procedure: After call to do_delete_fee_assignment proc for fee_assignment_id'|| l_fee_assignment_id ||' , return status is' || x_return_status);
1381 END IF;
1382
1383
1384
1385 END LOOP ;
1386
1387
1388
1389 EXCEPTION
1390
1391 WHEN OTHERS THEN
1392 ROLLBACK TO delete_DISB_FEE_ASSIGNMENT ;
1393
1394 END delete_DISB_FEE_ASSIGNMENT ;
1395
1396 /*===========================================================================+
1397 | FUNCTION
1398 | IS_LOAN_FASGM_EDITABLE
1399 |
1400 | DESCRIPTION
1401 | .
1402 |
1403 | SCOPE - PUBLIC
1404 |
1405 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1406 |
1407 | ARGUMENTS : IN:
1408 | IN: p_loan_id
1409 | p_fee_id
1410 |
1411 | RETURNS : NONE
1412 |
1413 | NOTES
1414 |
1415 | MODIFICATION HISTORY
1416 |
1417 | 26-OCT-2009 MBOLLI Bug#8904109 - Created.
1418 +===========================================================================*/
1419 FUNCTION IS_LOAN_FASGM_EDITABLE(p_loan_id in number
1420 ,p_fee_id in NUMBER
1421 ,p_disb_header_id in NUMBER
1422 ) return VARCHAR2 IS
1423
1424 l_updatableFlag VARCHAR2(1);
1425 l_fees_category lns_fees_all.fee_category%TYPE;
1426 l_fee_type lns_fees_all.fee_type%TYPE;
1427 l_status lns_loan_headers_all.loan_status%TYPE;
1428 l_disb_status lns_disb_headers.status%TYPE;
1429
1430 CURSOR c_fasgm_edit (c_loan_id NUMBER, c_fee_id NUMBER, c_disb_header_id NUMBER) IS
1431 SELECT 'X' FROM DUAL
1432 WHERE EXISTS ( SELECT NULL FROM LNS_FEE_ASSIGNMENTS fasgn
1433 WHERE (fasgn.LOAN_ID = c_loan_id
1434 OR (fasgn.DISB_HEADER_ID = c_disb_header_id ))
1435 AND fasgn.FEE_ID = c_fee_id
1436 AND nvl(trunc(fasgn.END_DATE_ACTIVE), trunc(SYSDATE)) >= trunc(SYSDATE)
1437 AND nvl(trunc(fasgn.START_DATE_ACTIVE), trunc(SYSDATE)) <= trunc(SYSDATE)
1438 )
1439 AND
1440 EXISTS ( SELECT NULL FROM LNS_FEE_SCHEDULES sched
1441 WHERE (sched.loan_id = c_loan_id OR sched.DISB_HEADER_ID = c_disb_header_id)
1442 AND sched.fee_id = c_fee_id
1443 AND sched.ACTIVE_FLAG = 'Y'
1444 AND sched.BILLED_FLAG = 'N'
1445 AND (NOT EXISTS
1446 (select 'X'
1447 from lns_amortization_scheds am
1448 ,lns_amortization_lines lines
1449 where lines.loan_id = c_loan_id
1450 and lines.fee_schedule_id = sched.fee_schedule_id
1451 and am.loan_id = lines.loan_id
1452 and NVL(am.reversed_flag, 'N') = 'N'
1453 --and am.payment_number = p_installment
1454 )
1455 or EXISTS
1456 (select 'X'
1457 from lns_amortization_scheds am
1458 ,lns_amortization_lines lines
1459 where lines.loan_id = c_loan_id
1460 and lines.fee_schedule_id = sched.fee_schedule_id
1461 and am.loan_id = lines.loan_id
1462 and am.reversed_flag = 'Y'
1463 --and am.payment_number = p_installment
1464 )
1465 )
1466 );
1467
1468 CURSOR c_fee_details (c_fee_id NUMBER) IS
1469 SELECT
1470 fees.fee_category, fees.fee_type
1471 FROM
1472 lns_fees_all fees
1473 WHERE
1474 fees.fee_id = c_fee_id;
1475
1476 CURSOR c_loan_details (c_disb_header_id NUMBER) IS
1477 SELECT
1478 hdr.loan_status
1479 FROM
1480 lns_loan_headers_all hdr
1481 WHERE
1482 hdr.loan_id in (select loan_id from lns_disb_headers where disb_header_id = c_disb_header_id);
1483
1484 CURSOR c_disb_details (c_disb_header_id NUMBER) IS
1485 SELECT
1486 disb.status
1487 FROM
1488 lns_disb_headers disb
1489 WHERE
1490 disb.disb_header_id = c_disb_header_id;
1491
1492 BEGIN
1493 l_updatableFlag := 'N';
1494
1495
1496 OPEN c_fee_details (p_fee_id);
1497
1498 FETCH c_fee_details INTO l_fees_category, l_fee_type;
1499 IF c_fee_details%NOTFOUND THEN
1500 CLOSE c_fee_details;
1501 return 'N';
1502 END IF;
1503 CLOSE c_fee_details;
1504
1505
1506 IF ( l_fees_category IS NOT NULL ) THEN
1507 IF NOT(l_fees_category = 'MEMO' OR (l_fees_category = 'EVENT' AND l_fee_type in ('EVENT_ORIGINATION', 'EVENT_CONVERSION', 'EVENT_FUNDING'))
1508 ) THEN
1509 return 'Y';
1510 END IF;
1511 END IF;
1512
1513 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1514 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, ' Before invoke cur c_fasgm_edit ');
1515 END IF;
1516
1517 OPEN c_fasgm_edit (p_loan_id, p_fee_id, p_disb_header_id);
1518 FETCH c_fasgm_edit INTO l_updatableFlag;
1519 IF c_fasgm_edit%FOUND THEN
1520 CLOSE c_fasgm_edit;
1521 return 'Y';
1522 END IF;
1523 CLOSE c_fasgm_edit;
1524
1525 -- All the disbursement fees can be edited before the loan becomes Active
1526 IF (p_disb_header_id IS NOT NULL) THEN
1527
1528 OPEN c_loan_details (p_disb_header_id);
1529
1530 FETCH c_loan_details INTO l_status;
1531
1532
1533 OPEN c_disb_details (p_disb_header_id);
1534
1535 FETCH c_disb_details INTO l_disb_status;
1536
1537
1538
1539 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1540 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'LoanStatus is '||l_status);
1541 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Disbursement Header Status is ' ||l_disb_status);
1542 END IF;
1543 CLOSE c_loan_details;
1544 CLOSE c_disb_details;
1545
1546 IF (l_disb_status is null) THEN --OR l_status NOT IN ('ACTIVE', 'DEFAULT', 'DELINQUENT')) THEN
1547 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1548 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Either Disb Header Status is null or Disb Fee is NOT billed, so edit = Y');
1549 END IF;
1550 return 'Y';
1551 END IF;
1552 END IF;
1553
1554
1555 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1556 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'End IS_LOAN_FASGM_EDITABLE is N');
1557 END IF;
1558
1559 return 'N';
1560
1561
1562 EXCEPTION
1563 WHEN OTHERS THEN
1564 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1565 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Exception in IS_LOAN_FASGM_EDITABLE is '||sqlerrm);
1566 END IF;
1567
1568
1569
1570 END IS_LOAN_FASGM_EDITABLE;
1571
1572 END LNS_FEE_ASSIGNMENT_PUB;