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