[Home] [Help]
PACKAGE BODY: APPS.LNS_COND_ASSIGNMENT_PUB
Source
1 PACKAGE BODY LNS_COND_ASSIGNMENT_PUB AS
2 /* $Header: LNS_CASGM_PUBP_B.pls 120.7.12010000.2 2008/08/26 20:41:59 mbolli ship $ */
3
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7 -- G_DEBUG_COUNT NUMBER := 0;
8 -- G_DEBUG BOOLEAN := FALSE;
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'LNS_COND_ASSIGNMENT_PUB';
11 G_AF_DO_DEBUG CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
12
13 --------------------------------------------------
14 -- declaration of private procedures and functions
15 --------------------------------------------------
16
17 PROCEDURE Set_Defaults (p_COND_ASSIGNMENT_rec IN OUT NOCOPY COND_ASSIGNMENT_REC_TYPE
18 )
19 IS
20 BEGIN
21
22 IF (p_COND_ASSIGNMENT_rec.mandatory_flag IS NULL) THEN
23 p_COND_ASSIGNMENT_rec.mandatory_flag := 'N';
24 END IF;
25
26 IF (p_COND_ASSIGNMENT_rec.condition_met_flag IS NULL) THEN
27 p_COND_ASSIGNMENT_rec.condition_met_flag := 'N';
28 END IF;
29
30 END Set_Defaults;
31
32
33 PROCEDURE do_create_COND_ASSIGNMENT (
34 p_COND_ASSIGNMENT_rec IN OUT NOCOPY COND_ASSIGNMENT_REC_TYPE,
35 x_COND_ASSIGNMENT_id OUT NOCOPY NUMBER,
36 x_return_status IN OUT NOCOPY VARCHAR2
37 );
38
39 PROCEDURE do_update_COND_ASSIGNMENT (
40 p_COND_ASSIGNMENT_rec IN OUT NOCOPY COND_ASSIGNMENT_REC_TYPE,
41 p_object_version_number IN OUT NOCOPY NUMBER,
42 x_return_status IN OUT NOCOPY VARCHAR2
43 );
44
45 PROCEDURE do_delete_COND_ASSIGNMENT (
46 p_COND_ASSIGNMENT_id IN NUMBER,
47 x_return_status IN OUT NOCOPY VARCHAR2
48 );
49
50 /*===========================================================================+
51 | PROCEDURE
52 | do_create_COND_ASSIGNMENT
53 |
54 | DESCRIPTION
55 | Creates condition assignment.
56 |
57 | SCOPE - PRIVATE
58 |
59 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
60 |
61 | ARGUMENTS : IN:
62 | OUT:
63 | x_COND_ASSIGNMENT_id
64 | IN/OUT:
65 | p_COND_ASSIGNMENT_rec
66 | x_return_status
67 |
68 | RETURNS : NONE
69 |
70 | NOTES
71 |
72 | MODIFICATION HISTORY
73 |
74 | 06-Jan-2004 Bernice Lam Created.
75 +===========================================================================*/
76
77 PROCEDURE do_create_COND_ASSIGNMENT(
78 p_COND_ASSIGNMENT_rec IN OUT NOCOPY COND_ASSIGNMENT_REC_TYPE,
79 x_COND_ASSIGNMENT_id OUT NOCOPY NUMBER,
80 x_return_status IN OUT NOCOPY VARCHAR2
81 ) IS
82
83 l_COND_ASSIGNMENT_id NUMBER;
84 -- l_rowid ROWID;
85 l_dummy VARCHAR2(1);
86 l_msg_count NUMBER;
87 l_msg_data VARCHAR2(2000);
88
89 BEGIN
90
91 l_COND_ASSIGNMENT_id := p_COND_ASSIGNMENT_rec.cond_assignment_id;
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_COND_ASSIGNMENT procedure');
94 END IF;
95
96 -- if primary key value is passed, check for uniqueness.
97 IF l_COND_ASSIGNMENT_id IS NOT NULL AND
98 l_COND_ASSIGNMENT_id <> FND_API.G_MISS_NUM
99 THEN
100 BEGIN
101 SELECT 'Y'
102 INTO l_dummy
103 FROM LNS_COND_ASSIGNMENTS
104 WHERE cond_assignment_id = l_COND_ASSIGNMENT_id;
105
106 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_DUPLICATE_COLUMN');
107 FND_MESSAGE.SET_TOKEN('COLUMN', 'cond_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 Set_Defaults(p_COND_ASSIGNMENT_rec);
118
119 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
120 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_create_COND_ASSIGNMENT procedure: Before call to LNS_COND_ASSIGNMENTS_PKG.Insert_Row');
121 END IF;
122
123 -- call table-handler.
124 LNS_COND_ASSIGNMENTS_PKG.Insert_Row (
125 X_COND_ASSIGNMENT_ID => p_COND_ASSIGNMENT_rec.cond_assignment_id,
126 P_OBJECT_VERSION_NUMBER => 1,
127 P_LOAN_ID => p_COND_ASSIGNMENT_rec.loan_id,
128 P_CONDITION_ID => p_COND_ASSIGNMENT_rec.condition_id,
129 P_CONDITION_DESCRIPTION => p_COND_ASSIGNMENT_rec.condition_description,
130 P_CONDITION_MET_FLAG => p_COND_ASSIGNMENT_rec.condition_met_flag,
131 P_FULFILLMENT_DATE => p_COND_ASSIGNMENT_rec.fulfillment_date,
132 P_FULFILLMENT_UPDATED_BY => p_COND_ASSIGNMENT_rec.fulfillment_updated_by,
133 P_MANDATORY_FLAG => p_COND_ASSIGNMENT_rec.mandatory_flag,
134 P_CREATED_BY => p_COND_ASSIGNMENT_rec.created_by,
135 P_CREATION_DATE => p_COND_ASSIGNMENT_rec.creation_date,
136 P_LAST_UPDATED_BY => p_COND_ASSIGNMENT_rec.last_updated_by,
137 P_LAST_UPDATE_DATE => p_COND_ASSIGNMENT_rec.last_update_date,
138 P_LAST_UPDATE_LOGIN => p_COND_ASSIGNMENT_rec.last_update_login,
139 P_START_DATE_ACTIVE => sysdate,
140 P_END_DATE_ACTIVE => null,
141 P_DISB_HEADER_ID => p_COND_ASSIGNMENT_rec.DISB_HEADER_ID,
142 P_DELETE_DISABLED_FLAG => p_COND_ASSIGNMENT_rec.DELETE_DISABLED_FLAG
143
144 );
145
146 x_COND_ASSIGNMENT_id := p_COND_ASSIGNMENT_rec.cond_assignment_id;
147
148 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
149 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_create_COND_ASSIGNMENT procedure: After call to LNS_COND_ASSIGNMENT.Insert_Row');
150 END IF;
151
152 END do_create_COND_ASSIGNMENT;
153
154
155 /*===========================================================================+
156 | PROCEDURE
157 | do_update_COND_ASSIGNMENT
158 |
159 | DESCRIPTION
160 | Updates condition assignment.
161 |
162 | SCOPE - PRIVATE
163 |
164 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
165 |
166 | ARGUMENTS : IN:
167 | OUT:
168 | IN/OUT:
169 | p_COND_ASSIGNMENT_rec
170 | p_object_version_number
171 | x_return_status
172 |
173 | RETURNS : NONE
174 |
175 | NOTES
176 |
177 | MODIFICATION HISTORY
178 |
179 | 22-APR-2004 Bernice Lam Created.
180 +===========================================================================*/
181
182 PROCEDURE do_update_COND_ASSIGNMENT(
183 p_COND_ASSIGNMENT_rec IN OUT NOCOPY COND_ASSIGNMENT_REC_TYPE,
184 p_object_version_number IN OUT NOCOPY NUMBER,
185 x_return_status IN OUT NOCOPY VARCHAR2
186 ) IS
187
188 l_object_version_number NUMBER;
189 -- l_rowid ROWID;
190 ldup_rowid ROWID;
191
192 BEGIN
193
194 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
195 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin do_update_COND_ASSIGNMENT procedure');
196 END IF;
197
198 -- check whether record has been updated by another user. If not, lock it.
199 BEGIN
200 SELECT OBJECT_VERSION_NUMBER
201 INTO l_object_version_number
202 FROM LNS_COND_ASSIGNMENTS
203 WHERE COND_ASSIGNMENT_ID = p_COND_ASSIGNMENT_rec.cond_assignment_id
204 FOR UPDATE OF COND_ASSIGNMENT_ID NOWAIT;
205
206 IF NOT
207 (
208 (p_object_version_number IS NULL AND l_object_version_number IS NULL)
209 OR
210 (p_object_version_number IS NOT NULL AND
211 l_object_version_number IS NOT NULL AND
212 p_object_version_number = l_object_version_number
213 )
214 )
215 THEN
216 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_RECORD_CHANGED');
217 FND_MESSAGE.SET_TOKEN('TABLE', 'lns_COND_ASSIGNMENTs');
218 FND_MSG_PUB.ADD;
219 RAISE FND_API.G_EXC_ERROR;
220 END IF;
221
222 p_object_version_number := nvl(l_object_version_number, 1) + 1;
223
224 EXCEPTION WHEN NO_DATA_FOUND THEN
225 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_NO_RECORD');
226 FND_MESSAGE.SET_TOKEN('RECORD', 'cond_assignment_rec');
227 FND_MESSAGE.SET_TOKEN('VALUE', NVL(TO_CHAR(p_COND_ASSIGNMENT_rec.cond_assignment_id), 'null'));
228 FND_MSG_PUB.ADD;
229 RAISE FND_API.G_EXC_ERROR;
230 END;
231
232 Set_Defaults(p_COND_ASSIGNMENT_rec);
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_COND_ASSIGNMENT procedure: Before call to LNS_COND_ASSIGNMENTS_PKG.Update_Row');
236 END IF;
237
238 -- log history
239 LNS_LOAN_HISTORY_PUB.log_record_pre(p_COND_ASSIGNMENT_rec.cond_assignment_id,
240 'COND_ASSIGNMENT_ID',
241 'LNS_COND_ASSIGNMENTS');
242
243 --Call to table-handler
244 LNS_COND_ASSIGNMENTS_PKG.Update_Row (
245 P_COND_ASSIGNMENT_ID => p_COND_ASSIGNMENT_rec.cond_assignment_id,
246 P_OBJECT_VERSION_NUMBER => p_OBJECT_VERSION_NUMBER,
247 P_LOAN_ID => p_COND_ASSIGNMENT_rec.LOAN_ID,
248 P_CONDITION_ID => p_COND_ASSIGNMENT_rec.CONDITION_ID,
249 P_CONDITION_DESCRIPTION => p_COND_ASSIGNMENT_rec.CONDITION_DESCRIPTION,
250 P_CONDITION_MET_FLAG => p_COND_ASSIGNMENT_rec.CONDITION_MET_FLAG,
251 P_FULFILLMENT_DATE => p_COND_ASSIGNMENT_rec.FULFILLMENT_DATE,
252 P_FULFILLMENT_UPDATED_BY => p_COND_ASSIGNMENT_rec.FULFILLMENT_UPDATED_BY,
253 P_MANDATORY_FLAG => p_COND_ASSIGNMENT_rec.MANDATORY_FLAG,
254 P_LAST_UPDATED_BY => NULL,
255 P_LAST_UPDATE_DATE => NULL,
256 P_LAST_UPDATE_LOGIN => NULL,
257 P_START_DATE_ACTIVE => NULL,
258 P_END_DATE_ACTIVE => NULL,
259 P_DISB_HEADER_ID => p_COND_ASSIGNMENT_rec.DISB_HEADER_ID,
260 P_DELETE_DISABLED_FLAG => p_COND_ASSIGNMENT_rec.DELETE_DISABLED_FLAG
261 );
262
263 -- log record changes
264 LNS_LOAN_HISTORY_PUB.log_record_post(p_COND_ASSIGNMENT_rec.cond_assignment_id,
265 'COND_ASSIGNMENT_ID',
266 'LNS_COND_ASSIGNMENTS',
267 p_COND_ASSIGNMENT_rec.loan_id);
268
269 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
270 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_update_COND_ASSIGNMENT procedure: After call to LNS_COND_ASSIGNMENTS_PKG.Update_Row');
271 END IF;
272
273 END do_update_COND_ASSIGNMENT;
274
275 /*===========================================================================+
276 | PROCEDURE
277 | do_delete_COND_ASSIGNMENT
278 |
279 | DESCRIPTION
280 | Deletes cond assignment.
281 |
282 | SCOPE - PRIVATE
283 |
284 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
285 |
286 | ARGUMENTS : IN:
287 | OUT:
288 | IN/OUT:
289 | p_COND_ASSIGNMENT_id
290 | p_object_version_number
291 | x_return_status
292 |
293 | RETURNS : NONE
294 |
295 | NOTES
296 |
297 | MODIFICATION HISTORY
298 |
299 | 06-Jan-2004 Bernice Lam Created.
300 +===========================================================================*/
301
302 PROCEDURE do_delete_COND_ASSIGNMENT(
303 p_COND_ASSIGNMENT_id NUMBER,
304 x_return_status IN OUT NOCOPY VARCHAR2
305 ) IS
306
307 l_loan_id NUMBER;
308 l_object_version_num NUMBER;
309 l_cond_assign_rec COND_ASSIGNMENT_REC_TYPE;
310
311 BEGIN
312
313 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
314 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin do_delete_COND_ASSIGNMENT procedure');
315 END IF;
316
317 IF p_COND_ASSIGNMENT_id IS NOT NULL AND
318 p_COND_ASSIGNMENT_id <> FND_API.G_MISS_NUM
319 THEN
320 -- check whether record has been deleted by another user. If not, lock it.
321 BEGIN
322 SELECT loan_id, object_version_number
323 INTO l_loan_id, l_object_version_num
324 FROM LNS_COND_ASSIGNMENTS
325 WHERE COND_ASSIGNMENT_ID = p_COND_ASSIGNMENT_id
326 FOR UPDATE NOWAIT;
327
328 EXCEPTION
329 WHEN NO_DATA_FOUND THEN
330 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_NO_RECORD');
331 FND_MESSAGE.SET_TOKEN('RECORD', 'cond_assignment_rec');
332 FND_MESSAGE.SET_TOKEN('VALUE', NVL(TO_CHAR(p_COND_ASSIGNMENT_id), 'null'));
333 FND_MSG_PUB.ADD;
334 RAISE FND_API.G_EXC_ERROR;
335 END;
336 END IF;
337
338
339 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
340 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_delete_COND_ASSIGNMENT procedure: Before call to LNS_COND_ASSIGNMENTS_PKG.Delete_Row');
341 END IF;
342
343 -- log history
344 LNS_LOAN_HISTORY_PUB.log_record_pre(p_cond_assignment_id,
345 'COND_ASSIGNMENT_ID',
346 'LNS_COND_ASSIGNMENTS');
347
348 BEGIN
349
350 UPDATE LNS_COND_ASSIGNMENTS
351 SET END_DATE_ACTIVE = SYSDATE,
352 OBJECT_VERSION_NUMBER = nvl(l_object_version_num, 1) + 1
353 WHERE COND_ASSIGNMENT_ID = p_cond_assignment_id;
354
355 EXCEPTION
356 WHEN OTHERS THEN
357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
358 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
359 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
360 FND_MSG_PUB.ADD;
361 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
362 END;
363
364 /*
365 --Call to table-handler
366 LNS_COND_ASSIGNMENTS_PKG.Update_Row (
367 P_COND_ASSIGNMENT_ID => p_COND_ASSIGNMENT_ID,
368 P_OBJECT_VERSION_NUMBER => null,
369 P_LOAN_ID => p_COND_ASSIGNMENT_rec.LOAN_ID,
370 P_CONDITION_ID => p_COND_ASSIGNMENT_rec.CONDITION_ID,
371 P_CONDITION_DESCRIPTION => p_COND_ASSIGNMENT_rec.CONDITION_DESCRIPTION,
372 P_CONDITION_MET_FLAG => p_COND_ASSIGNMENT_rec.CONDITION_MET_FLAG,
373 P_FULFILLMENT_DATE => p_COND_ASSIGNMENT_rec.FULFILLMENT_DATE,
374 P_FULFILLMENT_UPDATED_BY => p_COND_ASSIGNMENT_rec.FULFILLMENT_UPDATED_BY,
375 P_MANDATORY_FLAG => p_COND_ASSIGNMENT_rec.MANDATORY_FLAG,
376 P_LAST_UPDATED_BY => NULL,
377 P_LAST_UPDATE_DATE => NULL,
378 P_LAST_UPDATE_LOGIN => NULL,
379 P_START_DATE_ACTIVE => NULL,
380 P_END_DATE_ACTIVE => sysdate
381 );
382 */
383 -- log record changes
384 LNS_LOAN_HISTORY_PUB.log_record_post(p_cond_assignment_id,
385 'COND_ASSIGNMENT_ID',
386 'LNS_COND_ASSIGNMENTS',
387 l_loan_id);
388
389 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
390 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_delete_COND_ASSIGNMENT procedure: After call to LNS_COND_ASSIGNMENTS_PKG.Delete_Row');
391 END IF;
392
393 END do_delete_COND_ASSIGNMENT;
394
395 ----------------------------
396 -- body of public procedures
397 ----------------------------
398
399 /*===========================================================================+
400 | PROCEDURE
401 | create_COND_ASSIGNMENT
402 |
403 | DESCRIPTION
404 | Creates cond assignment.
405 |
406 | SCOPE - PUBLIC
407 |
408 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
409 |
410 | ARGUMENTS : IN:
411 | p_init_msg_list
412 | p_COND_ASSIGNMENT_rec
413 | OUT:
414 | x_return_status
415 | x_msg_count
416 | x_msg_data
417 | x_COND_ASSIGNMENT_id
418 | IN/OUT:
419 |
420 | RETURNS : NONE
421 |
422 | NOTES
423 |
424 | MODIFICATION HISTORY
425 | 22-APR-2004 Bernice Lam Created.
426 +===========================================================================*/
427
428 PROCEDURE create_COND_ASSIGNMENT (
429 p_init_msg_list IN VARCHAR2,
430 p_COND_ASSIGNMENT_rec IN COND_ASSIGNMENT_REC_TYPE,
431 x_COND_ASSIGNMENT_id OUT NOCOPY NUMBER,
432 x_return_status OUT NOCOPY VARCHAR2,
433 x_msg_count OUT NOCOPY NUMBER,
434 x_msg_data OUT NOCOPY VARCHAR2
435 ) IS
436
437 l_api_name CONSTANT VARCHAR2(30) := 'create_COND_ASSIGNMENT';
438 l_COND_ASSIGNMENT_rec COND_ASSIGNMENT_REC_TYPE;
439
440 BEGIN
441
442 l_COND_ASSIGNMENT_rec := p_COND_ASSIGNMENT_rec;
443 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
444 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin Create_COND_ASSIGNMENT procedure');
445 END IF;
446
447 -- standard start of API savepoint
448 SAVEPOINT create_COND_ASSIGNMENT;
449
450 -- initialize message list if p_init_msg_list is set to TRUE.
451 IF FND_API.to_Boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) THEN
452 FND_MSG_PUB.initialize;
453 END IF;
454
455 -- initialize API return status to success.
456 x_return_status := FND_API.G_RET_STS_SUCCESS;
457
458 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
459 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Create_COND_ASSIGNMENT procedure: Before call to do_create_COND_ASSIGNMENT proc');
460 END IF;
461
462 -- call to business logic.
463 do_create_COND_ASSIGNMENT(
464 l_COND_ASSIGNMENT_rec,
465 x_COND_ASSIGNMENT_id,
466 x_return_status
467 );
468
469 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
470 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Create_COND_ASSIGNMENT procedure: After call to do_create_COND_ASSIGNMENT proc');
471 END IF;
472
473 EXCEPTION
474 WHEN FND_API.G_EXC_ERROR THEN
475 ROLLBACK TO create_COND_ASSIGNMENT;
476 x_return_status := FND_API.G_RET_STS_ERROR;
477 FND_MSG_PUB.Count_And_Get(
478 p_encoded => FND_API.G_FALSE,
479 p_count => x_msg_count,
480 p_data => x_msg_data);
481
482 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
483 ROLLBACK TO create_COND_ASSIGNMENT;
484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
485 FND_MSG_PUB.Count_And_Get(
486 p_encoded => FND_API.G_FALSE,
487 p_count => x_msg_count,
488 p_data => x_msg_data);
489
490 WHEN OTHERS THEN
491 ROLLBACK TO create_COND_ASSIGNMENT;
492 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
493 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
494 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
495 FND_MSG_PUB.ADD;
496 FND_MSG_PUB.Count_And_Get(
497 p_encoded => FND_API.G_FALSE,
498 p_count => x_msg_count,
499 p_data => x_msg_data);
500
501 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
502 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'End Create_COND_ASSIGNMENT procedure');
503 END IF;
504
505 END create_COND_ASSIGNMENT;
506
507 /*===========================================================================+
508 | PROCEDURE
509 | update_COND_ASSIGNMENT
510 |
511 | DESCRIPTION
512 | Updates condition assignment.
513 |
514 | SCOPE - PUBLIC
515 |
516 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
517 |
518 | ARGUMENTS : IN:
519 | p_init_msg_list
520 | p_COND_ASSIGNMENT_rec
521 | OUT:
522 | x_return_status
523 | x_msg_count
524 | x_msg_data
525 | IN/OUT:
526 | p_object_version_number
527 |
528 | RETURNS : NONE
529 |
530 | NOTES
531 |
532 | MODIFICATION HISTORY
533 | 22-APR-2004 Bernice Lam Created
534 +===========================================================================*/
535
536 PROCEDURE update_COND_ASSIGNMENT (
537 p_init_msg_list IN VARCHAR2,
538 p_COND_ASSIGNMENT_rec IN COND_ASSIGNMENT_REC_TYPE,
539 p_object_version_number IN OUT NOCOPY NUMBER,
540 x_return_status OUT NOCOPY VARCHAR2,
541 x_msg_count OUT NOCOPY NUMBER,
542 x_msg_data OUT NOCOPY VARCHAR2
543 ) IS
544
545 l_api_name CONSTANT VARCHAR2(30) := 'update_COND_ASSIGNMENT';
546 l_COND_ASSIGNMENT_rec COND_ASSIGNMENT_REC_TYPE;
547 l_old_COND_ASSIGNMENT_rec COND_ASSIGNMENT_REC_TYPE;
548
549 BEGIN
550
551 l_COND_ASSIGNMENT_rec := p_COND_ASSIGNMENT_rec;
552 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
553 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin Update_COND_ASSIGNMENT procedure');
554 END IF;
555
556 -- standard start of API savepoint
557 SAVEPOINT update_COND_ASSIGNMENT;
558
559 -- initialize message list if p_init_msg_list is set to TRUE.
560 IF FND_API.to_Boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) THEN
561 FND_MSG_PUB.initialize;
562 END IF;
563
564 -- initialize API return status to success.
565 x_return_status := FND_API.G_RET_STS_SUCCESS;
566 /*
567 -- Get old record. Will be used by history package.
568 get_COND_ASSIGNMENT_rec (
569 p_COND_ASSIGNMENT_id => l_COND_ASSIGNMENT_rec.assignment_id,
570 x_COND_ASSIGNMENT_rec => l_old_COND_ASSIGNMENT_rec,
571 x_return_status => x_return_status,
572 x_msg_count => x_msg_count,
573 x_msg_data => x_msg_data );
574 */
575 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
576 RAISE FND_API.G_EXC_ERROR;
577 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
578 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
579 END IF;
580
581 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
582 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Update_COND_ASSIGNMENT procedure: Before call to do_update_COND_ASSIGNMENT proc');
583 END IF;
584
585 -- call to business logic.
586 do_update_COND_ASSIGNMENT(
587 l_COND_ASSIGNMENT_rec,
588 p_object_version_number,
589 x_return_status
590 );
591
592 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
593 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Update_COND_ASSIGNMENT procedure: After call to do_update_COND_ASSIGNMENT proc');
594 END IF;
595
596 EXCEPTION
597 WHEN FND_API.G_EXC_ERROR THEN
598 ROLLBACK TO update_COND_ASSIGNMENT;
599 x_return_status := FND_API.G_RET_STS_ERROR;
600 FND_MSG_PUB.Count_And_Get(
601 p_encoded => FND_API.G_FALSE,
602 p_count => x_msg_count,
603 p_data => x_msg_data);
604
605 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
606 ROLLBACK TO update_COND_ASSIGNMENT;
607 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
608 FND_MSG_PUB.Count_And_Get(
609 p_encoded => FND_API.G_FALSE,
610 p_count => x_msg_count,
611 p_data => x_msg_data);
612
613 WHEN OTHERS THEN
614 ROLLBACK TO update_COND_ASSIGNMENT;
615 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
616 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
617 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
618 FND_MSG_PUB.ADD;
619 FND_MSG_PUB.Count_And_Get(
620 p_encoded => FND_API.G_FALSE,
621 p_count => x_msg_count,
622 p_data => x_msg_data);
623
624 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
625 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'End Update_COND_ASSIGNMENT procedure');
626 END IF;
627
628 END update_COND_ASSIGNMENT;
629
630 /*===========================================================================+
631 | PROCEDURE
632 | delete_COND_ASSIGNMENT
633 |
634 | DESCRIPTION
635 | Deletes assignment.
636 |
637 | SCOPE - PUBLIC
638 |
639 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
640 |
641 | ARGUMENTS : IN:
642 | p_init_msg_list
643 | p_COND_ASSIGNMENT_id
644 | OUT:
645 | x_return_status
646 | x_msg_count
647 | x_msg_data
648 | IN/OUT:
649 |
650 | RETURNS : NONE
651 |
652 | NOTES
653 |
654 | MODIFICATION HISTORY
655 | 22-APR-2004 Bernice Lam Created.
656 +===========================================================================*/
657
658 PROCEDURE delete_COND_ASSIGNMENT (
659 p_init_msg_list IN VARCHAR2,
660 p_COND_ASSIGNMENT_id IN NUMBER,
661 x_return_status OUT NOCOPY VARCHAR2,
662 x_msg_count OUT NOCOPY NUMBER,
663 x_msg_data OUT NOCOPY VARCHAR2
664 ) IS
665
666 l_api_name CONSTANT VARCHAR2(30) := 'delete_COND_ASSIGNMENT';
667 l_COND_ASSIGNMENT_id NUMBER;
668
669 BEGIN
670
671 l_COND_ASSIGNMENT_id := p_COND_ASSIGNMENT_id;
672 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
673 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin Delete_COND_ASSIGNMENT procedure');
674 END IF;
675
676 -- standard start of API savepoint
677 SAVEPOINT delete_COND_ASSIGNMENT;
678
679 -- initialize message list if p_init_msg_list is set to TRUE.
680 IF FND_API.to_Boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) THEN
681 FND_MSG_PUB.initialize;
682 END IF;
683
684 -- initialize API return status to success.
685 x_return_status := FND_API.G_RET_STS_SUCCESS;
686
687 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
688 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Delete_COND_ASSIGNMENT procedure: Before call to do_delete_COND_ASSIGNMENT proc');
689 END IF;
690
691 -- call to business logic.
692 do_delete_COND_ASSIGNMENT(
693 l_COND_ASSIGNMENT_id,
694 x_return_status
695 );
696
697 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
698 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Delete_COND_ASSIGNMENT procedure: After call to do_delete_COND_ASSIGNMENT proc');
699 END IF;
700
701 EXCEPTION
702 WHEN FND_API.G_EXC_ERROR THEN
703 ROLLBACK TO delete_COND_ASSIGNMENT;
704 x_return_status := FND_API.G_RET_STS_ERROR;
705 FND_MSG_PUB.Count_And_Get(
706 p_encoded => FND_API.G_FALSE,
707 p_count => x_msg_count,
708 p_data => x_msg_data);
709
710 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
711 ROLLBACK TO delete_COND_ASSIGNMENT;
712 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
713 FND_MSG_PUB.Count_And_Get(
714 p_encoded => FND_API.G_FALSE,
715 p_count => x_msg_count,
716 p_data => x_msg_data);
717
718 WHEN OTHERS THEN
719 ROLLBACK TO delete_COND_ASSIGNMENT;
720 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
721 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
722 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
723 FND_MSG_PUB.ADD;
724 FND_MSG_PUB.Count_And_Get(
725 p_encoded => FND_API.G_FALSE,
726 p_count => x_msg_count,
727 p_data => x_msg_data);
728
729 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
730 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'End Delete_COND_ASSIGNMENT procedure');
731 END IF;
732
733 END delete_COND_ASSIGNMENT;
734
735
736 PROCEDURE create_LP_COND_ASSIGNMENT(
737 P_LOAN_ID IN NUMBER ) IS
738
739 CURSOR loan_prod_cond ( c_loan_id NUMBER ) IS
740 select LNS_COND_ASSIGNMENTS_S.NEXTVAL COND_ASSIGNMENT_ID,
741 LnsLoanHeaders.LOAN_ID,
742 LnsConditions.CONDITION_ID,
743 LnsConditions.CONDITION_DESCRIPTION,
744 LnsLoanProductLines.MANDATORY_FLAG
745
746 FROM LNS_CONDITIONS LnsConditions ,
747 LNS_LOAN_HEADERS LnsLoanHeaders ,
748 LNS_LOAN_PRODUCT_LINES LnsLoanProductLines
749
750 WHERE LnsLoanHeaders.LOAN_ID = c_loan_id
751 AND LnsLoanHeaders.PRODUCT_ID = LnsLoanProductLines.LOAN_PRODUCT_ID
752 AND LnsLoanProductLines.LOAN_PRODUCT_LINE_TYPE = 'CONDITION'
753 AND LnsLoanProductLines.LINE_REFERENCE_ID = LnsCOnditions.CONDITION_ID ;
754
755
756 CURSOR current_loan_status ( c_loan_id NUMBER ) IS
757 SELECT LOAN_STATUS , CURRENT_PHASE
758 FROM LNS_LOAN_HEADERS LnsLoanHeaders
759 WHERE LnsLoanHeaders.LOAN_ID = c_loan_id ;
760
761
762 CURSOR loan_cond_count ( c_loan_id NUMBER ) IS
763 SELECT count(COND_ASSIGNMENT_ID)
764 FROM LNS_COND_ASSIGNMENTS_VL LnsCondAssignments
765 WHERE LnsCondAssignments.LOAN_ID = c_loan_id ;
766
767
768 l_cond_assignment_id NUMBER ;
769 l_loan_id NUMBER ;
770 l_condition_id NUMBER ;
771 l_cond_desc LNS_CONDITIONS.CONDITION_DESCRIPTION%TYPE ;
772 l_mandatory_flag LNS_LOAN_PRODUCT_LINES.MANDATORY_FLAG%TYPE ;
773 l_cond_assignment_rec cond_assignment_rec_type ;
774 x_return_status VARCHAR2(1) ;
775 l_loan_status LNS_LOAN_HEADERS.LOAN_STATUS%TYPE ;
776 l_loan_current_phase LNS_LOAN_HEADERS.CURRENT_PHASE%TYPE ;
777 l_loan_cond_count NUMBER ;
778 is_commit_needed BOOLEAN;
779
780 BEGIN
781 --Initialize this variable to false. Change to true when a record is
782 --inserted into the table in this procedure
783 is_commit_needed := FALSE;
784
785 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
786 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin create_LP_COND_ASSIGNMENT procedure');
787 END IF;
788
789 -- standard start of API savepoint
790 SAVEPOINT create_LP_COND_ASSIGNMENT;
791
792
793 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
794 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_COND_ASSIGNMENT procedure: Before opening cursor current_loan_status ');
795 END IF;
796
797 OPEN current_loan_status(P_LOAN_ID) ;
798
799 FETCH current_loan_status INTO l_loan_status ,l_loan_Current_phase ;
800
801 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
802 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_COND_ASSIGNMENT procedure: After opening cursor current_loan_status , loan status is '||l_loan_status ||' loan current phase is '||l_loan_Current_phase);
803 END IF;
804
805 /* If the loan current phase is not open or loan status is not Incomplete for Term loan , no conditions assignment required */
806 IF( NOT ( ( l_loan_status='INCOMPLETE' AND l_loan_current_phase = 'TERM' ) OR ( l_loan_current_phase = 'OPEN' ) ) ) THEN
807 RETURN ;
808 END IF ;
809
810
811
812
813 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
814 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'create_LP_COND_ASSIGNMENT procedure: Before opening cursor loan_cond_count ');
815 END IF;
816
817 OPEN loan_cond_count(P_LOAN_ID) ;
818
819 FETCH loan_cond_count INTO l_loan_cond_count ;
820
821 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
822 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_COND_ASSIGNMENT procedure: After opening cursor loan_fee_count , loan condition count is '||l_loan_cond_count );
823 END IF;
824
825 /* If the loan condition count is not zero and there are already conditions assigned to loan, no conditions assignment required */
826 IF( l_loan_cond_count <> 0 ) THEN
827 RETURN ;
828 END IF ;
829
830
831
832 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
833 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_COND_ASSIGNMENT procedure: Before opening cursor loan_prod_cond ');
834 END IF;
835
836 OPEN loan_prod_cond(P_LOAN_ID) ;
837
838 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
839 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_COND_ASSIGNMENT procedure: After opening cursor loan_prod_cond , no of conditions found is '||loan_prod_cond%ROWCOUNT);
840 END IF;
841
842
843
844 LOOP
845
846 FETCH loan_prod_cond INTO l_cond_assignment_id ,l_loan_id,l_condition_id,l_cond_desc,l_mandatory_flag ;
847 EXIT WHEN loan_prod_cond%NOTFOUND ;
848
849 l_cond_assignment_rec.COND_ASSIGNMENT_ID := l_cond_assignment_id ;
850 l_cond_assignment_rec.LOAN_ID := l_loan_id ;
851 l_cond_assignment_rec.CONDITION_ID := l_condition_id ;
852 l_cond_assignment_rec.CONDITION_DESCRIPTION := l_cond_desc ;
853 l_cond_assignment_rec.CONDITION_MET_FLAG := 'N' ;
854 l_cond_assignment_rec.FULFILLMENT_DATE := NULL ;
855 l_cond_assignment_rec.FULFILLMENT_UPDATED_BY := NULL ;
856 l_cond_assignment_rec.MANDATORY_FLAG := l_mandatory_flag ;
857 l_cond_assignment_rec.CREATED_BY := NULL ;
858 l_cond_assignment_rec.CREATION_DATE := NULL ;
859 l_cond_assignment_rec.LAST_UPDATED_BY := NULL ;
860 l_cond_assignment_rec.LAST_UPDATE_DATE := NULL ;
861 l_cond_assignment_rec.LAST_UPDATE_LOGIN := NULL ;
862 l_cond_assignment_rec.OBJECT_VERSION_NUMBER := 1 ;
863 l_cond_assignment_rec.DISB_HEADER_ID := NULL ;
864 l_cond_assignment_rec.DELETE_DISABLED_FLAG := l_mandatory_flag ;
865
866
867 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
868 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_COND_ASSIGNMENT procedure: Before call to do_create_COND_ASSIGNMENT proc for condition'|| l_condition_id);
869 END IF;
870
871 -- call to business logic.
872 do_create_COND_ASSIGNMENT( l_cond_assignment_rec ,
873 l_cond_assignment_id ,
874 x_return_status ) ;
875
876 is_commit_needed := true;
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, 'In create_LP_COND_ASSIGNMENT procedure: After call to do_create_COND_ASSIGNMENT proc for condition'|| l_condition_id ||' , return status is' || x_return_status);
880 END IF;
881
882
883
884 END LOOP ;
885
886 --If records have been inserted into lns_cond_assignments table
887 --they need to be committed since the commit does not happen on the UI
888 --unless the user explicitly commits from the UI page
889 IF (is_commit_needed = TRUE) THEN
890 COMMIT WORK;
891 END IF;
892
893 EXCEPTION
894
895 WHEN OTHERS THEN
896 ROLLBACK TO create_LP_COND_ASSIGNMENT;
897
898 END create_LP_COND_ASSIGNMENT ;
899
900
901 PROCEDURE create_LP_DISB_COND_ASSIGNMENT(
902 P_DISB_HEADER_ID IN NUMBER , P_LOAN_PRODUCT_LINE_ID IN NUMBER) IS
903
904 CURSOR loan_prod_disb_cond ( c_loan_prod_line_id NUMBER ) IS
905 select LNS_COND_ASSIGNMENTS_S.NEXTVAL COND_ASSIGNMENT_ID,
906 LnsConditions.CONDITION_ID,
907 LnsConditions.CONDITION_DESCRIPTION,
908 LnsLoanProductLines.MANDATORY_FLAG
909
910 FROM LNS_CONDITIONS_VL LnsConditions ,
911 LNS_LOAN_PRODUCT_LINES LnsLoanProductLines
912
913 WHERE LnsLoanProductLines.PARENT_PRODUCT_LINES_ID = c_loan_prod_line_id
914 AND LnsLoanProductLines.LOAN_PRODUCT_LINE_TYPE = 'DISB_CONDITION'
915 AND LnsLoanProductLines.LINE_REFERENCE_ID = LnsCOnditions.CONDITION_ID ;
916
917 l_cond_assignment_id NUMBER ;
918 l_condition_id NUMBER ;
919 l_cond_desc LNS_CONDITIONS.CONDITION_DESCRIPTION%TYPE ;
920 l_mandatory_flag LNS_LOAN_PRODUCT_LINES.MANDATORY_FLAG%TYPE ;
921
922 l_cond_assignment_rec cond_assignment_rec_type ;
923 x_return_status VARCHAR2(1) ;
924
925 BEGIN
926
927
928 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
929 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin create_LP_DISB_COND_ASSIGNMENT procedure');
930 END IF;
931
932 -- standard start of API savepoint
933 SAVEPOINT create_LP_DISB_COND_ASSIGNMENT;
934
935
936 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
937 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_DISB_COND_ASSIGNMENT procedure: Before opening cursor loan_prod_disb_cond ');
938 END IF;
939
940 OPEN loan_prod_disb_cond(P_LOAN_PRODUCT_LINE_ID) ;
941
942 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
943 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_DISB_COND_ASSIGNMENT procedure: After opening cursor loan_prod_disb_cond , no of conditions found is '||loan_prod_disb_cond%ROWCOUNT);
944 END IF;
945
946
947
948 LOOP
949
950 FETCH loan_prod_disb_cond INTO l_cond_assignment_id ,l_condition_id,l_cond_desc,l_mandatory_flag ;
951 EXIT WHEN loan_prod_disb_cond%NOTFOUND ;
952
953 l_cond_assignment_rec.COND_ASSIGNMENT_ID := l_cond_assignment_id ;
954 l_cond_assignment_rec.LOAN_ID := NULL ;
955 l_cond_assignment_rec.CONDITION_ID := l_condition_id ;
956 l_cond_assignment_rec.CONDITION_DESCRIPTION := l_cond_desc ;
957 l_cond_assignment_rec.CONDITION_MET_FLAG := 'N' ;
958 l_cond_assignment_rec.FULFILLMENT_DATE := NULL ;
959 l_cond_assignment_rec.FULFILLMENT_UPDATED_BY := NULL ;
960 l_cond_assignment_rec.MANDATORY_FLAG := l_mandatory_flag ;
961 l_cond_assignment_rec.CREATED_BY := NULL ;
962 l_cond_assignment_rec.CREATION_DATE := NULL ;
963 l_cond_assignment_rec.LAST_UPDATED_BY := NULL ;
964 l_cond_assignment_rec.LAST_UPDATE_DATE := NULL ;
965 l_cond_assignment_rec.LAST_UPDATE_LOGIN := NULL ;
966 l_cond_assignment_rec.OBJECT_VERSION_NUMBER := 1 ;
967 l_cond_assignment_rec.DISB_HEADER_ID := P_DISB_HEADER_ID ;
968 l_cond_assignment_rec.DELETE_DISABLED_FLAG := l_mandatory_flag ;
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_DISB_COND_ASSIGNMENT procedure: Before call to do_create_COND_ASSIGNMENT proc for condition'|| l_condition_id);
973 END IF;
974
975 -- call to business logic.
976 do_create_COND_ASSIGNMENT( l_cond_assignment_rec ,
977 l_cond_assignment_id ,
978 x_return_status ) ;
979
980 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
981 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_DISB_COND_ASSIGNMENT procedure: After call to do_create_COND_ASSIGNMENT proc for condition'|| l_condition_id ||' , return status is' || x_return_status);
982 END IF;
983
984
985
986 END LOOP ;
987
988
989
990 EXCEPTION
991
992 WHEN OTHERS THEN
993 ROLLBACK TO create_LP_DISB_COND_ASSIGNMENT;
994
995 END create_LP_DISB_COND_ASSIGNMENT ;
996
997
998
999
1000 PROCEDURE delete_DISB_COND_ASSIGNMENT( P_DISB_HEADER_ID IN NUMBER ) IS
1001
1002 CURSOR loan_disb_cond ( c_disb_header_id NUMBER ) IS
1003 SELECT COND_ASSIGNMENT_ID
1004 FROM LNS_COND_ASSIGNMENTS LnsCondAssignments
1005 WHERE LnsCondAssignments.DISB_HEADER_ID = c_disb_header_id ;
1006
1007 l_cond_assignment_id NUMBER ;
1008 x_return_status VARCHAR2(1) ;
1009
1010 BEGIN
1011
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, 'Begin delete_DISB_COND_ASSIGNMENT procedure');
1015 END IF;
1016
1017 -- standard start of API savepoint
1018 SAVEPOINT delete_DISB_COND_ASSIGNMENT;
1019
1020
1021 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1022 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In delete_DISB_COND_ASSIGNMENT procedure: Before opening cursor loan_disb_cond ');
1023 END IF;
1024
1025 OPEN loan_disb_cond(P_DISB_HEADER_ID ) ;
1026
1027 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1028 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In delete_DISB_COND_ASSIGNMENT procedure: After opening cursor loan_disb_cond , no of conditions found is '||loan_disb_cond%ROWCOUNT);
1029 END IF;
1030
1031
1032
1033 LOOP
1034
1035 FETCH loan_disb_cond INTO l_cond_assignment_id ;
1036 EXIT WHEN loan_disb_cond%NOTFOUND ;
1037
1038
1039 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1040 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In delete_DISB_COND_ASSIGNMENT procedure: Before call to do_delete_COND_ASSIGNMENT proc for cond_assignment_id'|| l_cond_assignment_id);
1041 END IF;
1042
1043 -- call to business logic.
1044 do_delete_COND_ASSIGNMENT(l_cond_assignment_id ,
1045 x_return_status ) ;
1046
1047 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1048 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In delete_DISB_COND_ASSIGNMENT procedure: After call to do_delete_COND_ASSIGNMENT proc for cond_assignment_id'|| l_cond_assignment_id ||' , return status is' || x_return_status);
1049 END IF;
1050
1051
1052
1053 END LOOP ;
1054
1055
1056
1057 EXCEPTION
1058
1059 WHEN OTHERS THEN
1060 ROLLBACK TO delete_DISB_COND_ASSIGNMENT;
1061
1062 END delete_DISB_COND_ASSIGNMENT ;
1063
1064
1065
1066 FUNCTION IS_EXIST_COND_ASSIGNMENT (
1067 p_condition_id NUMBER
1068 ) RETURN VARCHAR2 IS
1069
1070 CURSOR C_Is_Exist_Assignment (X_COND_Id NUMBER) IS
1071 SELECT 'X' FROM DUAL
1072 WHERE EXISTS ( SELECT NULL FROM LNS_COND_ASSIGNMENTS
1073 WHERE CONDITION_ID = X_COND_ID )
1074 OR EXISTS ( SELECT NULL FROM LNS_LOAN_PRODUCT_LINES
1075 WHERE LINE_REFERENCE_ID = X_COND_ID
1076 AND ( LOAN_PRODUCT_LINE_TYPE = 'CONDITION' OR LOAN_PRODUCT_LINE_TYPE='DISB_CONDITION' )
1077 );
1078
1079 l_dummy VARCHAR2(1);
1080
1081 BEGIN
1082
1083 OPEN C_Is_Exist_Assignment (p_condition_id);
1084 FETCH C_Is_Exist_Assignment INTO l_dummy;
1085 IF C_Is_Exist_Assignment%FOUND THEN
1086 CLOSE C_Is_Exist_Assignment;
1087 RETURN 'Y';
1088 END IF;
1089 CLOSE C_Is_Exist_Assignment;
1090 RETURN 'N';
1091
1092 END IS_EXIST_COND_ASSIGNMENT;
1093
1094
1095
1096 END LNS_COND_ASSIGNMENT_PUB;