[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.5 2010/04/08 14:51:57 gparuchu ship $ */
3
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7 G_PKG_NAME CONSTANT VARCHAR2(30):= 'LNS_COND_ASSIGNMENT_PUB';
8 G_LOG_ENABLED varchar2(5);
9 G_MSG_LEVEL NUMBER;
10
11
12 --------------------------------------------------
13 -- declaration of private procedures and functions
14 --------------------------------------------------
15
16
17 /*========================================================================
18 | PRIVATE PROCEDURE LogMessage
19 |
20 | DESCRIPTION
21 | This procedure logs debug messages to db and to CM log
22 |
23 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
24 |
25 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
26 | None
27 |
28 | PARAMETERS
29 | p_msg_level IN Debug msg level
30 | p_msg IN Debug msg itself
31 |
32 | KNOWN ISSUES
33 | None
34 |
35 | NOTES
36 | Any interesting aspect of the code in the package body which needs
37 | to be stated.
38 |
39 | MODIFICATION HISTORY
40 | Date Author Description of Changes
41 | 04-02-2008 scherkas Created
42 |
43 *=======================================================================*/
44 Procedure LogMessage(p_msg_level IN NUMBER, p_msg in varchar2)
45 IS
46 BEGIN
47 if (p_msg_level >= G_MSG_LEVEL) then
48
49 FND_LOG.STRING(p_msg_level, G_PKG_NAME, p_msg);
50 if FND_GLOBAL.Conc_Request_Id is not null then
51 fnd_file.put_line(FND_FILE.LOG, p_msg);
52 end if;
53
54 end if;
55
56 EXCEPTION
57 WHEN OTHERS THEN
58 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
59 END;
60
61
62
63 PROCEDURE Set_Defaults (p_COND_ASSIGNMENT_rec IN OUT NOCOPY COND_ASSIGNMENT_REC_TYPE
64 )
65 IS
66 BEGIN
67
68 IF (p_COND_ASSIGNMENT_rec.mandatory_flag IS NULL) THEN
69 p_COND_ASSIGNMENT_rec.mandatory_flag := 'N';
70 END IF;
71
72 IF (p_COND_ASSIGNMENT_rec.condition_met_flag IS NULL) THEN
73 p_COND_ASSIGNMENT_rec.condition_met_flag := 'N';
74 END IF;
75
76 END Set_Defaults;
77
78
79 PROCEDURE do_create_COND_ASSIGNMENT (
80 p_COND_ASSIGNMENT_rec IN OUT NOCOPY COND_ASSIGNMENT_REC_TYPE,
81 x_COND_ASSIGNMENT_id OUT NOCOPY NUMBER,
82 x_return_status IN OUT NOCOPY VARCHAR2
83 );
84
85 PROCEDURE do_update_COND_ASSIGNMENT (
86 p_COND_ASSIGNMENT_rec IN OUT NOCOPY COND_ASSIGNMENT_REC_TYPE,
87 p_object_version_number IN OUT NOCOPY NUMBER,
88 x_return_status IN OUT NOCOPY VARCHAR2
89 );
90
91 PROCEDURE do_delete_COND_ASSIGNMENT (
92 p_COND_ASSIGNMENT_id IN NUMBER,
93 x_return_status IN OUT NOCOPY VARCHAR2
94 );
95
96 /*===========================================================================+
97 | PROCEDURE
98 | do_create_COND_ASSIGNMENT
99 |
100 | DESCRIPTION
101 | Creates condition assignment.
102 |
103 | SCOPE - PRIVATE
104 |
105 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
106 |
107 | ARGUMENTS : IN:
108 | OUT:
109 | x_COND_ASSIGNMENT_id
110 | IN/OUT:
111 | p_COND_ASSIGNMENT_rec
112 | x_return_status
113 |
114 | RETURNS : NONE
115 |
116 | NOTES
117 |
118 | MODIFICATION HISTORY
119 |
120 | 06-Jan-2004 Bernice Lam Created.
121 +===========================================================================*/
122
123 PROCEDURE do_create_COND_ASSIGNMENT(
124 p_COND_ASSIGNMENT_rec IN OUT NOCOPY COND_ASSIGNMENT_REC_TYPE,
125 x_COND_ASSIGNMENT_id OUT NOCOPY NUMBER,
126 x_return_status IN OUT NOCOPY VARCHAR2
127 ) IS
128
129 l_COND_ASSIGNMENT_id NUMBER;
130 -- l_rowid ROWID;
131 l_dummy VARCHAR2(1);
132 l_msg_count NUMBER;
133 l_msg_data VARCHAR2(2000);
134
135 BEGIN
136
137 l_COND_ASSIGNMENT_id := p_COND_ASSIGNMENT_rec.cond_assignment_id;
138 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
139 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin do_create_COND_ASSIGNMENT procedure');
140 END IF;
141
142 -- if primary key value is passed, check for uniqueness.
143 IF l_COND_ASSIGNMENT_id IS NOT NULL AND
144 l_COND_ASSIGNMENT_id <> FND_API.G_MISS_NUM
145 THEN
146 BEGIN
147 SELECT 'Y'
148 INTO l_dummy
149 FROM LNS_COND_ASSIGNMENTS
150 WHERE cond_assignment_id = l_COND_ASSIGNMENT_id;
151
152 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_DUPLICATE_COLUMN');
153 FND_MESSAGE.SET_TOKEN('COLUMN', 'cond_assignment_id');
154 FND_MSG_PUB.ADD;
155 RAISE FND_API.G_EXC_ERROR;
156
157 EXCEPTION
158 WHEN NO_DATA_FOUND THEN
159 NULL;
160 END;
161 END IF;
162
163 Set_Defaults(p_COND_ASSIGNMENT_rec);
164
165 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
166 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');
167 END IF;
168
169 -- call table-handler.
170 LNS_COND_ASSIGNMENTS_PKG.Insert_Row (
171 X_COND_ASSIGNMENT_ID => p_COND_ASSIGNMENT_rec.cond_assignment_id,
172 P_OBJECT_VERSION_NUMBER => 1,
173 P_LOAN_ID => p_COND_ASSIGNMENT_rec.loan_id,
174 P_CONDITION_ID => p_COND_ASSIGNMENT_rec.condition_id,
175 P_CONDITION_DESCRIPTION => p_COND_ASSIGNMENT_rec.condition_description,
176 P_CONDITION_MET_FLAG => p_COND_ASSIGNMENT_rec.condition_met_flag,
177 P_FULFILLMENT_DATE => p_COND_ASSIGNMENT_rec.fulfillment_date,
178 P_FULFILLMENT_UPDATED_BY => p_COND_ASSIGNMENT_rec.fulfillment_updated_by,
179 P_MANDATORY_FLAG => p_COND_ASSIGNMENT_rec.mandatory_flag,
180 P_CREATED_BY => p_COND_ASSIGNMENT_rec.created_by,
181 P_CREATION_DATE => p_COND_ASSIGNMENT_rec.creation_date,
182 P_LAST_UPDATED_BY => p_COND_ASSIGNMENT_rec.last_updated_by,
183 P_LAST_UPDATE_DATE => p_COND_ASSIGNMENT_rec.last_update_date,
184 P_LAST_UPDATE_LOGIN => p_COND_ASSIGNMENT_rec.last_update_login,
185 P_START_DATE_ACTIVE => sysdate,
186 P_END_DATE_ACTIVE => null,
187 P_DISB_HEADER_ID => p_COND_ASSIGNMENT_rec.DISB_HEADER_ID,
188 P_DELETE_DISABLED_FLAG => p_COND_ASSIGNMENT_rec.DELETE_DISABLED_FLAG,
189 P_OWNER_OBJECT_ID => p_COND_ASSIGNMENT_rec.OWNER_OBJECT_ID,
190 P_OWNER_TABLE => p_COND_ASSIGNMENT_rec.OWNER_TABLE
191
192 );
193
194 x_COND_ASSIGNMENT_id := p_COND_ASSIGNMENT_rec.cond_assignment_id;
195 x_return_status := 'S';
196
197 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
198 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In do_create_COND_ASSIGNMENT procedure: After call to LNS_COND_ASSIGNMENT.Insert_Row');
199 END IF;
200
201 END do_create_COND_ASSIGNMENT;
202
203
204 /*===========================================================================+
205 | PROCEDURE
206 | do_update_COND_ASSIGNMENT
207 |
208 | DESCRIPTION
209 | Updates condition assignment.
210 |
211 | SCOPE - PRIVATE
212 |
213 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
214 |
215 | ARGUMENTS : IN:
216 | OUT:
217 | IN/OUT:
218 | p_COND_ASSIGNMENT_rec
219 | p_object_version_number
220 | x_return_status
221 |
222 | RETURNS : NONE
223 |
224 | NOTES
225 |
226 | MODIFICATION HISTORY
227 |
228 | 22-APR-2004 Bernice Lam Created.
229 +===========================================================================*/
230
231 PROCEDURE do_update_COND_ASSIGNMENT(
232 p_COND_ASSIGNMENT_rec IN OUT NOCOPY COND_ASSIGNMENT_REC_TYPE,
233 p_object_version_number IN OUT NOCOPY NUMBER,
234 x_return_status IN OUT NOCOPY VARCHAR2
235 ) IS
236
237 l_object_version_number NUMBER;
238 -- l_rowid ROWID;
239 ldup_rowid ROWID;
240
241 BEGIN
242
243 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
244 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin do_update_COND_ASSIGNMENT procedure');
245 END IF;
246
247 -- check whether record has been updated by another user. If not, lock it.
248 BEGIN
249 SELECT OBJECT_VERSION_NUMBER
250 INTO l_object_version_number
251 FROM LNS_COND_ASSIGNMENTS
252 WHERE COND_ASSIGNMENT_ID = p_COND_ASSIGNMENT_rec.cond_assignment_id
253 FOR UPDATE OF COND_ASSIGNMENT_ID NOWAIT;
254
255 IF NOT
256 (
257 (p_object_version_number IS NULL AND l_object_version_number IS NULL)
258 OR
259 (p_object_version_number IS NOT NULL AND
260 l_object_version_number IS NOT NULL AND
261 p_object_version_number = l_object_version_number
262 )
263 )
264 THEN
265 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_RECORD_CHANGED');
266 FND_MESSAGE.SET_TOKEN('TABLE', 'lns_COND_ASSIGNMENTs');
267 FND_MSG_PUB.ADD;
268 RAISE FND_API.G_EXC_ERROR;
269 END IF;
270
271 p_object_version_number := nvl(l_object_version_number, 1) + 1;
272
273 EXCEPTION WHEN NO_DATA_FOUND THEN
274 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_NO_RECORD');
275 FND_MESSAGE.SET_TOKEN('RECORD', 'cond_assignment_rec');
276 FND_MESSAGE.SET_TOKEN('VALUE', NVL(TO_CHAR(p_COND_ASSIGNMENT_rec.cond_assignment_id), 'null'));
277 FND_MSG_PUB.ADD;
278 RAISE FND_API.G_EXC_ERROR;
279 END;
280
281 Set_Defaults(p_COND_ASSIGNMENT_rec);
282
283 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
284 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');
285 END IF;
286
287 -- log history
288 LNS_LOAN_HISTORY_PUB.log_record_pre(p_COND_ASSIGNMENT_rec.cond_assignment_id,
289 'COND_ASSIGNMENT_ID',
290 'LNS_COND_ASSIGNMENTS');
291
292 --Call to table-handler
293 LNS_COND_ASSIGNMENTS_PKG.Update_Row (
294 P_COND_ASSIGNMENT_ID => p_COND_ASSIGNMENT_rec.cond_assignment_id,
295 P_OBJECT_VERSION_NUMBER => p_OBJECT_VERSION_NUMBER,
296 P_LOAN_ID => p_COND_ASSIGNMENT_rec.LOAN_ID,
297 P_CONDITION_ID => p_COND_ASSIGNMENT_rec.CONDITION_ID,
298 P_CONDITION_DESCRIPTION => p_COND_ASSIGNMENT_rec.CONDITION_DESCRIPTION,
299 P_CONDITION_MET_FLAG => p_COND_ASSIGNMENT_rec.CONDITION_MET_FLAG,
300 P_FULFILLMENT_DATE => p_COND_ASSIGNMENT_rec.FULFILLMENT_DATE,
301 P_FULFILLMENT_UPDATED_BY => p_COND_ASSIGNMENT_rec.FULFILLMENT_UPDATED_BY,
302 P_MANDATORY_FLAG => p_COND_ASSIGNMENT_rec.MANDATORY_FLAG,
303 P_LAST_UPDATED_BY => NULL,
304 P_LAST_UPDATE_DATE => NULL,
305 P_LAST_UPDATE_LOGIN => NULL,
306 P_START_DATE_ACTIVE => NULL,
307 P_END_DATE_ACTIVE => NULL,
308 P_DISB_HEADER_ID => p_COND_ASSIGNMENT_rec.DISB_HEADER_ID,
309 P_DELETE_DISABLED_FLAG => p_COND_ASSIGNMENT_rec.DELETE_DISABLED_FLAG,
310 P_OWNER_OBJECT_ID => p_COND_ASSIGNMENT_rec.OWNER_OBJECT_ID,
311 P_OWNER_TABLE => p_COND_ASSIGNMENT_rec.OWNER_TABLE
312 );
313
314 -- log record changes
315 LNS_LOAN_HISTORY_PUB.log_record_post(p_COND_ASSIGNMENT_rec.cond_assignment_id,
316 'COND_ASSIGNMENT_ID',
317 'LNS_COND_ASSIGNMENTS',
318 p_COND_ASSIGNMENT_rec.loan_id);
319
320 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
321 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');
322 END IF;
323
324 END do_update_COND_ASSIGNMENT;
325
326 /*===========================================================================+
327 | PROCEDURE
328 | do_delete_COND_ASSIGNMENT
329 |
330 | DESCRIPTION
331 | Deletes cond assignment.
332 |
333 | SCOPE - PRIVATE
334 |
335 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
336 |
337 | ARGUMENTS : IN:
338 | OUT:
339 | IN/OUT:
340 | p_COND_ASSIGNMENT_id
341 | p_object_version_number
342 | x_return_status
343 |
344 | RETURNS : NONE
345 |
346 | NOTES
347 |
348 | MODIFICATION HISTORY
349 |
350 | 06-Jan-2004 Bernice Lam Created.
351 +===========================================================================*/
352
353 PROCEDURE do_delete_COND_ASSIGNMENT(
354 p_COND_ASSIGNMENT_id NUMBER,
355 x_return_status IN OUT NOCOPY VARCHAR2
356 ) IS
357
358 l_loan_id NUMBER;
359 l_object_version_num NUMBER;
360 l_cond_assign_rec COND_ASSIGNMENT_REC_TYPE;
361
362 BEGIN
363
364 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
365 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin do_delete_COND_ASSIGNMENT procedure');
366 END IF;
367
368 IF p_COND_ASSIGNMENT_id IS NOT NULL AND
369 p_COND_ASSIGNMENT_id <> FND_API.G_MISS_NUM
370 THEN
371 -- check whether record has been deleted by another user. If not, lock it.
372 BEGIN
373 SELECT loan_id, object_version_number
374 INTO l_loan_id, l_object_version_num
375 FROM LNS_COND_ASSIGNMENTS
376 WHERE COND_ASSIGNMENT_ID = p_COND_ASSIGNMENT_id
377 FOR UPDATE NOWAIT;
378
379 EXCEPTION
380 WHEN NO_DATA_FOUND THEN
381 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_NO_RECORD');
382 FND_MESSAGE.SET_TOKEN('RECORD', 'cond_assignment_rec');
383 FND_MESSAGE.SET_TOKEN('VALUE', NVL(TO_CHAR(p_COND_ASSIGNMENT_id), 'null'));
384 FND_MSG_PUB.ADD;
385 RAISE FND_API.G_EXC_ERROR;
386 END;
387 END IF;
388
389
390 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
391 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');
392 END IF;
393
394 -- log history
395 LNS_LOAN_HISTORY_PUB.log_record_pre(p_cond_assignment_id,
396 'COND_ASSIGNMENT_ID',
397 'LNS_COND_ASSIGNMENTS');
398
399 BEGIN
400
401 UPDATE LNS_COND_ASSIGNMENTS
402 SET END_DATE_ACTIVE = SYSDATE,
403 OBJECT_VERSION_NUMBER = nvl(l_object_version_num, 1) + 1
404 WHERE COND_ASSIGNMENT_ID = p_cond_assignment_id;
405
406 EXCEPTION
407 WHEN OTHERS THEN
408 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
409 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
410 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
411 FND_MSG_PUB.ADD;
412 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
413 END;
414
415 /*
416 --Call to table-handler
417 LNS_COND_ASSIGNMENTS_PKG.Update_Row (
418 P_COND_ASSIGNMENT_ID => p_COND_ASSIGNMENT_ID,
419 P_OBJECT_VERSION_NUMBER => null,
420 P_LOAN_ID => p_COND_ASSIGNMENT_rec.LOAN_ID,
421 P_CONDITION_ID => p_COND_ASSIGNMENT_rec.CONDITION_ID,
422 P_CONDITION_DESCRIPTION => p_COND_ASSIGNMENT_rec.CONDITION_DESCRIPTION,
423 P_CONDITION_MET_FLAG => p_COND_ASSIGNMENT_rec.CONDITION_MET_FLAG,
424 P_FULFILLMENT_DATE => p_COND_ASSIGNMENT_rec.FULFILLMENT_DATE,
425 P_FULFILLMENT_UPDATED_BY => p_COND_ASSIGNMENT_rec.FULFILLMENT_UPDATED_BY,
426 P_MANDATORY_FLAG => p_COND_ASSIGNMENT_rec.MANDATORY_FLAG,
427 P_LAST_UPDATED_BY => NULL,
428 P_LAST_UPDATE_DATE => NULL,
429 P_LAST_UPDATE_LOGIN => NULL,
430 P_START_DATE_ACTIVE => NULL,
431 P_END_DATE_ACTIVE => sysdate
432 );
433 */
434 -- log record changes
435 LNS_LOAN_HISTORY_PUB.log_record_post(p_cond_assignment_id,
436 'COND_ASSIGNMENT_ID',
437 'LNS_COND_ASSIGNMENTS',
438 l_loan_id);
439
440 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
441 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');
442 END IF;
443
444 END do_delete_COND_ASSIGNMENT;
445
446 ----------------------------
447 -- body of public procedures
448 ----------------------------
449
450 /*===========================================================================+
451 | PROCEDURE
452 | create_COND_ASSIGNMENT
453 |
454 | DESCRIPTION
455 | Creates cond assignment.
456 |
457 | SCOPE - PUBLIC
458 |
459 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
460 |
461 | ARGUMENTS : IN:
462 | p_init_msg_list
463 | p_COND_ASSIGNMENT_rec
464 | OUT:
465 | x_return_status
466 | x_msg_count
467 | x_msg_data
468 | x_COND_ASSIGNMENT_id
469 | IN/OUT:
470 |
471 | RETURNS : NONE
472 |
473 | NOTES
474 |
475 | MODIFICATION HISTORY
476 | 22-APR-2004 Bernice Lam Created.
477 +===========================================================================*/
478
479 PROCEDURE create_COND_ASSIGNMENT (
480 p_init_msg_list IN VARCHAR2,
481 p_COND_ASSIGNMENT_rec IN COND_ASSIGNMENT_REC_TYPE,
482 x_COND_ASSIGNMENT_id OUT NOCOPY NUMBER,
483 x_return_status OUT NOCOPY VARCHAR2,
484 x_msg_count OUT NOCOPY NUMBER,
485 x_msg_data OUT NOCOPY VARCHAR2
486 ) IS
487
488 l_api_name CONSTANT VARCHAR2(30) := 'create_COND_ASSIGNMENT';
489 l_COND_ASSIGNMENT_rec COND_ASSIGNMENT_REC_TYPE;
490
491 BEGIN
492
493 l_COND_ASSIGNMENT_rec := p_COND_ASSIGNMENT_rec;
494 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
495 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin Create_COND_ASSIGNMENT procedure');
496 END IF;
497
498 -- standard start of API savepoint
499 SAVEPOINT create_COND_ASSIGNMENT;
500
501 -- initialize message list if p_init_msg_list is set to TRUE.
502 IF FND_API.to_Boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) THEN
503 FND_MSG_PUB.initialize;
504 END IF;
505
506 -- initialize API return status to success.
507 x_return_status := FND_API.G_RET_STS_SUCCESS;
508
509 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
510 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Create_COND_ASSIGNMENT procedure: Before call to do_create_COND_ASSIGNMENT proc');
511 END IF;
512
513 -- call to business logic.
514 do_create_COND_ASSIGNMENT(
515 l_COND_ASSIGNMENT_rec,
516 x_COND_ASSIGNMENT_id,
517 x_return_status
518 );
519
520 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
521 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Create_COND_ASSIGNMENT procedure: After call to do_create_COND_ASSIGNMENT proc');
522 END IF;
523
524 EXCEPTION
525 WHEN FND_API.G_EXC_ERROR THEN
526 ROLLBACK TO create_COND_ASSIGNMENT;
527 x_return_status := FND_API.G_RET_STS_ERROR;
528 FND_MSG_PUB.Count_And_Get(
529 p_encoded => FND_API.G_FALSE,
530 p_count => x_msg_count,
531 p_data => x_msg_data);
532
533 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
534 ROLLBACK TO create_COND_ASSIGNMENT;
535 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
536 FND_MSG_PUB.Count_And_Get(
537 p_encoded => FND_API.G_FALSE,
538 p_count => x_msg_count,
539 p_data => x_msg_data);
540
541 WHEN OTHERS THEN
542 ROLLBACK TO create_COND_ASSIGNMENT;
543 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
544 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
545 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
546 FND_MSG_PUB.ADD;
547 FND_MSG_PUB.Count_And_Get(
548 p_encoded => FND_API.G_FALSE,
549 p_count => x_msg_count,
550 p_data => x_msg_data);
551
552 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
553 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'End Create_COND_ASSIGNMENT procedure');
554 END IF;
555
556 END create_COND_ASSIGNMENT;
557
558 /*===========================================================================+
559 | PROCEDURE
560 | update_COND_ASSIGNMENT
561 |
562 | DESCRIPTION
563 | Updates condition assignment.
564 |
565 | SCOPE - PUBLIC
566 |
567 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
568 |
569 | ARGUMENTS : IN:
570 | p_init_msg_list
571 | p_COND_ASSIGNMENT_rec
572 | OUT:
573 | x_return_status
574 | x_msg_count
575 | x_msg_data
576 | IN/OUT:
577 | p_object_version_number
578 |
579 | RETURNS : NONE
580 |
581 | NOTES
582 |
583 | MODIFICATION HISTORY
584 | 22-APR-2004 Bernice Lam Created
585 +===========================================================================*/
586
587 PROCEDURE update_COND_ASSIGNMENT (
588 p_init_msg_list IN VARCHAR2,
589 p_COND_ASSIGNMENT_rec IN COND_ASSIGNMENT_REC_TYPE,
590 p_object_version_number IN OUT NOCOPY NUMBER,
591 x_return_status OUT NOCOPY VARCHAR2,
592 x_msg_count OUT NOCOPY NUMBER,
593 x_msg_data OUT NOCOPY VARCHAR2
594 ) IS
595
596 l_api_name CONSTANT VARCHAR2(30) := 'update_COND_ASSIGNMENT';
597 l_COND_ASSIGNMENT_rec COND_ASSIGNMENT_REC_TYPE;
598 l_old_COND_ASSIGNMENT_rec COND_ASSIGNMENT_REC_TYPE;
599
600 BEGIN
601
602 l_COND_ASSIGNMENT_rec := p_COND_ASSIGNMENT_rec;
603 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
604 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin Update_COND_ASSIGNMENT procedure');
605 END IF;
606
607 -- standard start of API savepoint
608 SAVEPOINT update_COND_ASSIGNMENT;
609
610 -- initialize message list if p_init_msg_list is set to TRUE.
611 IF FND_API.to_Boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) THEN
612 FND_MSG_PUB.initialize;
613 END IF;
614
615 -- initialize API return status to success.
616 x_return_status := FND_API.G_RET_STS_SUCCESS;
617 /*
618 -- Get old record. Will be used by history package.
619 get_COND_ASSIGNMENT_rec (
620 p_COND_ASSIGNMENT_id => l_COND_ASSIGNMENT_rec.assignment_id,
621 x_COND_ASSIGNMENT_rec => l_old_COND_ASSIGNMENT_rec,
622 x_return_status => x_return_status,
623 x_msg_count => x_msg_count,
624 x_msg_data => x_msg_data );
625 */
626 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
627 RAISE FND_API.G_EXC_ERROR;
628 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
629 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
630 END IF;
631
632 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
633 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Update_COND_ASSIGNMENT procedure: Before call to do_update_COND_ASSIGNMENT proc');
634 END IF;
635
636 -- call to business logic.
637 do_update_COND_ASSIGNMENT(
638 l_COND_ASSIGNMENT_rec,
639 p_object_version_number,
640 x_return_status
641 );
642
643 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
644 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Update_COND_ASSIGNMENT procedure: After call to do_update_COND_ASSIGNMENT proc');
645 END IF;
646
647 EXCEPTION
648 WHEN FND_API.G_EXC_ERROR THEN
649 ROLLBACK TO update_COND_ASSIGNMENT;
650 x_return_status := FND_API.G_RET_STS_ERROR;
651 FND_MSG_PUB.Count_And_Get(
652 p_encoded => FND_API.G_FALSE,
653 p_count => x_msg_count,
654 p_data => x_msg_data);
655
656 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
657 ROLLBACK TO update_COND_ASSIGNMENT;
658 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
659 FND_MSG_PUB.Count_And_Get(
660 p_encoded => FND_API.G_FALSE,
661 p_count => x_msg_count,
662 p_data => x_msg_data);
663
664 WHEN OTHERS THEN
665 ROLLBACK TO update_COND_ASSIGNMENT;
666 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
667 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
668 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
669 FND_MSG_PUB.ADD;
670 FND_MSG_PUB.Count_And_Get(
671 p_encoded => FND_API.G_FALSE,
672 p_count => x_msg_count,
673 p_data => x_msg_data);
674
675 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
676 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'End Update_COND_ASSIGNMENT procedure');
677 END IF;
678
679 END update_COND_ASSIGNMENT;
680
681 /*===========================================================================+
682 | PROCEDURE
683 | delete_COND_ASSIGNMENT
684 |
685 | DESCRIPTION
686 | Deletes assignment.
687 |
688 | SCOPE - PUBLIC
689 |
690 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
691 |
692 | ARGUMENTS : IN:
693 | p_init_msg_list
694 | p_COND_ASSIGNMENT_id
695 | OUT:
696 | x_return_status
697 | x_msg_count
698 | x_msg_data
699 | IN/OUT:
700 |
701 | RETURNS : NONE
702 |
703 | NOTES
704 |
705 | MODIFICATION HISTORY
706 | 22-APR-2004 Bernice Lam Created.
707 +===========================================================================*/
708
709 PROCEDURE delete_COND_ASSIGNMENT (
710 p_init_msg_list IN VARCHAR2,
711 p_COND_ASSIGNMENT_id IN NUMBER,
712 x_return_status OUT NOCOPY VARCHAR2,
713 x_msg_count OUT NOCOPY NUMBER,
714 x_msg_data OUT NOCOPY VARCHAR2
715 ) IS
716
717 l_api_name CONSTANT VARCHAR2(30) := 'delete_COND_ASSIGNMENT';
718 l_COND_ASSIGNMENT_id NUMBER;
719
720 BEGIN
721
722 l_COND_ASSIGNMENT_id := p_COND_ASSIGNMENT_id;
723 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
724 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin Delete_COND_ASSIGNMENT procedure');
725 END IF;
726
727 -- standard start of API savepoint
728 SAVEPOINT delete_COND_ASSIGNMENT;
729
730 -- initialize message list if p_init_msg_list is set to TRUE.
731 IF FND_API.to_Boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) THEN
732 FND_MSG_PUB.initialize;
733 END IF;
734
735 -- initialize API return status to success.
736 x_return_status := FND_API.G_RET_STS_SUCCESS;
737
738 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
739 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Delete_COND_ASSIGNMENT procedure: Before call to do_delete_COND_ASSIGNMENT proc');
740 END IF;
741
742 -- call to business logic.
743 do_delete_COND_ASSIGNMENT(
744 l_COND_ASSIGNMENT_id,
745 x_return_status
746 );
747
748 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
749 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In Delete_COND_ASSIGNMENT procedure: After call to do_delete_COND_ASSIGNMENT proc');
750 END IF;
751
752 EXCEPTION
753 WHEN FND_API.G_EXC_ERROR THEN
754 ROLLBACK TO delete_COND_ASSIGNMENT;
755 x_return_status := FND_API.G_RET_STS_ERROR;
756 FND_MSG_PUB.Count_And_Get(
757 p_encoded => FND_API.G_FALSE,
758 p_count => x_msg_count,
759 p_data => x_msg_data);
760
761 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
762 ROLLBACK TO delete_COND_ASSIGNMENT;
763 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
764 FND_MSG_PUB.Count_And_Get(
765 p_encoded => FND_API.G_FALSE,
766 p_count => x_msg_count,
767 p_data => x_msg_data);
768
769 WHEN OTHERS THEN
770 ROLLBACK TO delete_COND_ASSIGNMENT;
771 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
772 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
773 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
774 FND_MSG_PUB.ADD;
775 FND_MSG_PUB.Count_And_Get(
776 p_encoded => FND_API.G_FALSE,
777 p_count => x_msg_count,
778 p_data => x_msg_data);
779
780 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
781 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'End Delete_COND_ASSIGNMENT procedure');
782 END IF;
783
784 END delete_COND_ASSIGNMENT;
785
786
787 PROCEDURE create_LP_COND_ASSIGNMENT(
788 P_LOAN_ID IN NUMBER ) IS
789
790 CURSOR loan_prod_cond ( c_loan_id NUMBER ) IS
791 select LNS_COND_ASSIGNMENTS_S.NEXTVAL COND_ASSIGNMENT_ID,
792 LnsLoanHeaders.LOAN_ID,
793 LnsConditions.CONDITION_ID,
794 LnsConditions.CONDITION_DESCRIPTION,
795 LnsLoanProductLines.MANDATORY_FLAG
796
797 FROM LNS_CONDITIONS LnsConditions ,
798 LNS_LOAN_HEADERS LnsLoanHeaders ,
799 LNS_LOAN_PRODUCT_LINES LnsLoanProductLines
800
801 WHERE LnsLoanHeaders.LOAN_ID = c_loan_id
802 AND LnsLoanHeaders.PRODUCT_ID = LnsLoanProductLines.LOAN_PRODUCT_ID
803 AND LnsLoanProductLines.LOAN_PRODUCT_LINE_TYPE = 'CONDITION'
804 AND LnsLoanProductLines.LINE_REFERENCE_ID = LnsCOnditions.CONDITION_ID ;
805
806
807 CURSOR current_loan_status ( c_loan_id NUMBER ) IS
808 SELECT LOAN_STATUS , CURRENT_PHASE
809 FROM LNS_LOAN_HEADERS LnsLoanHeaders
810 WHERE LnsLoanHeaders.LOAN_ID = c_loan_id ;
811
812
813 CURSOR loan_cond_count ( c_loan_id NUMBER ) IS
814 SELECT count(COND_ASSIGNMENT_ID)
815 FROM LNS_COND_ASSIGNMENTS_VL LnsCondAssignments
816 WHERE LnsCondAssignments.LOAN_ID = c_loan_id ;
817
818
819 l_cond_assignment_id NUMBER ;
820 l_loan_id NUMBER ;
821 l_condition_id NUMBER ;
822 l_cond_desc LNS_CONDITIONS.CONDITION_DESCRIPTION%TYPE ;
823 l_mandatory_flag LNS_LOAN_PRODUCT_LINES.MANDATORY_FLAG%TYPE ;
824 l_cond_assignment_rec cond_assignment_rec_type ;
825 x_return_status VARCHAR2(1) ;
826 l_loan_status LNS_LOAN_HEADERS.LOAN_STATUS%TYPE ;
827 l_loan_current_phase LNS_LOAN_HEADERS.CURRENT_PHASE%TYPE ;
828 l_loan_cond_count NUMBER ;
829 is_commit_needed BOOLEAN;
830
831 BEGIN
832 --Initialize this variable to false. Change to true when a record is
833 --inserted into the table in this procedure
834 is_commit_needed := FALSE;
835
836 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
837 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin create_LP_COND_ASSIGNMENT procedure');
838 END IF;
839
840 -- standard start of API savepoint
841 SAVEPOINT create_LP_COND_ASSIGNMENT;
842
843
844 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
845 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_COND_ASSIGNMENT procedure: Before opening cursor current_loan_status ');
846 END IF;
847
848 OPEN current_loan_status(P_LOAN_ID) ;
849
850 FETCH current_loan_status INTO l_loan_status ,l_loan_Current_phase ;
851
852 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
853 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);
854 END IF;
855
856 /* If the loan current phase is not open or loan status is not Incomplete for Term loan , no conditions assignment required */
857 IF( NOT ( ( l_loan_status='INCOMPLETE' AND l_loan_current_phase = 'TERM' ) OR ( l_loan_current_phase = 'OPEN' ) ) ) THEN
858 RETURN ;
859 END IF ;
860
861
862
863
864 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
865 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'create_LP_COND_ASSIGNMENT procedure: Before opening cursor loan_cond_count ');
866 END IF;
867
868 OPEN loan_cond_count(P_LOAN_ID) ;
869
870 FETCH loan_cond_count INTO l_loan_cond_count ;
871
872 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
873 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 );
874 END IF;
875
876 /* If the loan condition count is not zero and there are already conditions assigned to loan, no conditions assignment required */
877 IF( l_loan_cond_count <> 0 ) THEN
878 RETURN ;
879 END IF ;
880
881
882
883 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
884 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In create_LP_COND_ASSIGNMENT procedure: Before opening cursor loan_prod_cond ');
885 END IF;
886
887 OPEN loan_prod_cond(P_LOAN_ID) ;
888
889 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
890 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);
891 END IF;
892
893
894
895 LOOP
896
897 FETCH loan_prod_cond INTO l_cond_assignment_id ,l_loan_id,l_condition_id,l_cond_desc,l_mandatory_flag ;
898 EXIT WHEN loan_prod_cond%NOTFOUND ;
899
900 l_cond_assignment_rec.COND_ASSIGNMENT_ID := l_cond_assignment_id ;
901 l_cond_assignment_rec.LOAN_ID := l_loan_id ;
902 l_cond_assignment_rec.CONDITION_ID := l_condition_id ;
903 l_cond_assignment_rec.CONDITION_DESCRIPTION := l_cond_desc ;
904 l_cond_assignment_rec.CONDITION_MET_FLAG := 'N' ;
905 l_cond_assignment_rec.FULFILLMENT_DATE := NULL ;
906 l_cond_assignment_rec.FULFILLMENT_UPDATED_BY := NULL ;
907 l_cond_assignment_rec.MANDATORY_FLAG := l_mandatory_flag ;
908 l_cond_assignment_rec.CREATED_BY := NULL ;
909 l_cond_assignment_rec.CREATION_DATE := NULL ;
910 l_cond_assignment_rec.LAST_UPDATED_BY := NULL ;
911 l_cond_assignment_rec.LAST_UPDATE_DATE := NULL ;
912 l_cond_assignment_rec.LAST_UPDATE_LOGIN := NULL ;
913 l_cond_assignment_rec.OBJECT_VERSION_NUMBER := 1 ;
914 l_cond_assignment_rec.DISB_HEADER_ID := NULL ;
915 l_cond_assignment_rec.DELETE_DISABLED_FLAG := l_mandatory_flag ;
916 l_cond_assignment_rec.OWNER_OBJECT_ID := NULL ;
917 l_cond_assignment_rec.OWNER_TABLE := NULL ;
918
919
920 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
921 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);
922 END IF;
923
924 -- call to business logic.
925 do_create_COND_ASSIGNMENT( l_cond_assignment_rec ,
926 l_cond_assignment_id ,
927 x_return_status ) ;
928
929 is_commit_needed := true;
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_COND_ASSIGNMENT procedure: After call to do_create_COND_ASSIGNMENT proc for condition'|| l_condition_id ||' , return status is' || x_return_status);
933 END IF;
934
935
936
937 END LOOP ;
938
939 --If records have been inserted into lns_cond_assignments table
940 --they need to be committed since the commit does not happen on the UI
941 --unless the user explicitly commits from the UI page
942 IF (is_commit_needed = TRUE) THEN
943 COMMIT WORK;
944 END IF;
945
946 EXCEPTION
947
948 WHEN OTHERS THEN
949 ROLLBACK TO create_LP_COND_ASSIGNMENT;
950
951 END create_LP_COND_ASSIGNMENT ;
952
953
954 PROCEDURE create_LP_DISB_COND_ASSIGNMENT(
955 P_LOAN_ID IN NUMBER,
956 P_DISB_HEADER_ID IN NUMBER ,
957 P_LOAN_PRODUCT_LINE_ID IN NUMBER)
958 IS
959
960 CURSOR loan_prod_disb_cond ( c_loan_prod_line_id NUMBER ) IS
961 select LnsConditions.CONDITION_ID,
962 LnsConditions.CONDITION_DESCRIPTION,
963 LnsLoanProductLines.MANDATORY_FLAG
964 FROM LNS_CONDITIONS_VL LnsConditions ,
965 LNS_LOAN_PRODUCT_LINES LnsLoanProductLines
966 WHERE LnsLoanProductLines.PARENT_PRODUCT_LINES_ID = c_loan_prod_line_id
967 AND LnsLoanProductLines.LOAN_PRODUCT_LINE_TYPE = 'DISB_CONDITION'
968 AND LnsLoanProductLines.LINE_REFERENCE_ID = LnsCOnditions.CONDITION_ID
969 UNION
970 select LnsConditions.CONDITION_ID,
971 LnsConditions.CONDITION_DESCRIPTION,
972 LnsLoanProductLines.MANDATORY_FLAG
973 FROM LNS_CONDITIONS_VL LnsConditions ,
974 LNS_LOAN_PRODUCT_LINES LnsLoanProductLines
975 WHERE LnsLoanProductLines.loan_product_id =
976 (SELECT loan_product_id FROM LNS_LOAN_PRODUCT_LINES WHERE loan_product_lines_id = c_loan_prod_line_id)
977 AND LnsLoanProductLines.LOAN_PRODUCT_LINE_TYPE = 'CONDITION'
978 AND LnsLoanProductLines.LINE_REFERENCE_ID = LnsCOnditions.CONDITION_ID
979 and LnsCOnditions.condition_type = 'DISBURSEMENT';
980
981 l_cond_assignment_id NUMBER ;
982 l_temp_cond_assignment_id NUMBER ;
983 l_condition_id NUMBER ;
984 l_cond_desc LNS_CONDITIONS.CONDITION_DESCRIPTION%TYPE ;
985 l_mandatory_flag LNS_LOAN_PRODUCT_LINES.MANDATORY_FLAG%TYPE ;
986 l_cond_assignment_rec cond_assignment_rec_type ;
987 x_return_status VARCHAR2(1) ;
988
989 BEGIN
990
991 logMessage(FND_LOG.LEVEL_STATEMENT, 'Begin create_LP_DISB_COND_ASSIGNMENT procedure');
992
993 logMessage(FND_LOG.LEVEL_STATEMENT, 'P_LOAN_ID = ' || P_LOAN_ID);
994 logMessage(FND_LOG.LEVEL_STATEMENT, 'P_DISB_HEADER_ID = ' || P_DISB_HEADER_ID);
995 logMessage(FND_LOG.LEVEL_STATEMENT, 'P_LOAN_PRODUCT_LINE_ID = ' || P_LOAN_PRODUCT_LINE_ID);
996
997 -- standard start of API savepoint
998 SAVEPOINT create_LP_DISB_COND_ASSIGNMENT;
999
1000 logMessage(FND_LOG.LEVEL_STATEMENT, 'In create_LP_DISB_COND_ASSIGNMENT procedure: Before opening cursor loan_prod_disb_cond ');
1001
1002 OPEN loan_prod_disb_cond(P_LOAN_PRODUCT_LINE_ID) ;
1003
1004 logMessage(FND_LOG.LEVEL_STATEMENT, 'In create_LP_DISB_COND_ASSIGNMENT procedure: After opening cursor loan_prod_disb_cond , no of conditions found is '||loan_prod_disb_cond%ROWCOUNT);
1005 LOOP
1006
1007 FETCH loan_prod_disb_cond INTO l_condition_id,l_cond_desc,l_mandatory_flag ;
1008 EXIT WHEN loan_prod_disb_cond%NOTFOUND ;
1009
1010 select LNS_COND_ASSIGNMENTS_S.NEXTVAL into l_temp_cond_assignment_id from DUAL;
1011 l_cond_assignment_rec.COND_ASSIGNMENT_ID := l_temp_cond_assignment_id;
1012
1013 --commented below line as it was throwing error PLS-00357 during the build by release team
1014 --l_cond_assignment_rec.COND_ASSIGNMENT_ID := LNS_COND_ASSIGNMENTS_S.NEXTVAL;
1015
1016 l_cond_assignment_rec.LOAN_ID := P_LOAN_ID ;
1017 l_cond_assignment_rec.CONDITION_ID := l_condition_id ;
1018 l_cond_assignment_rec.CONDITION_DESCRIPTION := l_cond_desc ;
1019 l_cond_assignment_rec.CONDITION_MET_FLAG := 'N' ;
1020 l_cond_assignment_rec.FULFILLMENT_DATE := NULL ;
1021 l_cond_assignment_rec.FULFILLMENT_UPDATED_BY := NULL ;
1022 l_cond_assignment_rec.MANDATORY_FLAG := l_mandatory_flag ;
1023 l_cond_assignment_rec.CREATED_BY := NULL ;
1024 l_cond_assignment_rec.CREATION_DATE := NULL ;
1025 l_cond_assignment_rec.LAST_UPDATED_BY := NULL ;
1026 l_cond_assignment_rec.LAST_UPDATE_DATE := NULL ;
1027 l_cond_assignment_rec.LAST_UPDATE_LOGIN := NULL ;
1028 l_cond_assignment_rec.OBJECT_VERSION_NUMBER := 1 ;
1029 l_cond_assignment_rec.DISB_HEADER_ID := P_DISB_HEADER_ID ;
1030 l_cond_assignment_rec.DELETE_DISABLED_FLAG := l_mandatory_flag ;
1031 l_cond_assignment_rec.OWNER_OBJECT_ID := NULL ;
1032 l_cond_assignment_rec.OWNER_TABLE := 'LNS_DISB_HEADERS' ;
1033
1034 logMessage(FND_LOG.LEVEL_STATEMENT, 'In create_LP_DISB_COND_ASSIGNMENT procedure: Before call to do_create_COND_ASSIGNMENT proc for condition '|| l_condition_id);
1035
1036 -- call to business logic.
1037 do_create_COND_ASSIGNMENT( l_cond_assignment_rec ,
1038 l_cond_assignment_id ,
1039 x_return_status ) ;
1040
1041 logMessage(FND_LOG.LEVEL_STATEMENT, '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);
1042
1043 END LOOP ;
1044
1045 EXCEPTION
1046
1047 WHEN OTHERS THEN
1048 ROLLBACK TO create_LP_DISB_COND_ASSIGNMENT;
1049
1050 END create_LP_DISB_COND_ASSIGNMENT ;
1051
1052
1053
1054
1055 PROCEDURE delete_DISB_COND_ASSIGNMENT( P_DISB_HEADER_ID IN NUMBER ) IS
1056
1057 CURSOR loan_disb_cond ( c_disb_header_id NUMBER ) IS
1058 SELECT COND_ASSIGNMENT_ID
1059 FROM LNS_COND_ASSIGNMENTS LnsCondAssignments
1060 WHERE LnsCondAssignments.DISB_HEADER_ID = c_disb_header_id ;
1061
1062 l_cond_assignment_id NUMBER ;
1063 x_return_status VARCHAR2(1) ;
1064
1065 BEGIN
1066
1067
1068 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1069 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin delete_DISB_COND_ASSIGNMENT procedure');
1070 END IF;
1071
1072 -- standard start of API savepoint
1073 SAVEPOINT delete_DISB_COND_ASSIGNMENT;
1074
1075
1076 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1077 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'In delete_DISB_COND_ASSIGNMENT procedure: Before opening cursor loan_disb_cond ');
1078 END IF;
1079
1080 OPEN loan_disb_cond(P_DISB_HEADER_ID ) ;
1081
1082 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1083 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);
1084 END IF;
1085
1086
1087
1088 LOOP
1089
1090 FETCH loan_disb_cond INTO l_cond_assignment_id ;
1091 EXIT WHEN loan_disb_cond%NOTFOUND ;
1092
1093
1094 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1095 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);
1096 END IF;
1097
1098 -- call to business logic.
1099 do_delete_COND_ASSIGNMENT(l_cond_assignment_id ,
1100 x_return_status ) ;
1101
1102 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1103 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);
1104 END IF;
1105
1106
1107
1108 END LOOP ;
1109
1110
1111
1112 EXCEPTION
1113
1114 WHEN OTHERS THEN
1115 ROLLBACK TO delete_DISB_COND_ASSIGNMENT;
1116
1117 END delete_DISB_COND_ASSIGNMENT ;
1118
1119
1120
1121 FUNCTION IS_EXIST_COND_ASSIGNMENT (
1122 p_condition_id NUMBER
1123 ) RETURN VARCHAR2 IS
1124
1125 CURSOR C_Is_Exist_Assignment (X_COND_Id NUMBER) IS
1126 SELECT 'X' FROM DUAL
1127 WHERE EXISTS ( SELECT NULL FROM LNS_COND_ASSIGNMENTS
1128 WHERE CONDITION_ID = X_COND_ID )
1129 OR EXISTS ( SELECT NULL FROM LNS_LOAN_PRODUCT_LINES
1130 WHERE LINE_REFERENCE_ID = X_COND_ID
1131 AND ( LOAN_PRODUCT_LINE_TYPE = 'CONDITION' OR LOAN_PRODUCT_LINE_TYPE='DISB_CONDITION' )
1132 );
1133
1134 l_dummy VARCHAR2(1);
1135
1136 BEGIN
1137
1138 OPEN C_Is_Exist_Assignment (p_condition_id);
1139 FETCH C_Is_Exist_Assignment INTO l_dummy;
1140 IF C_Is_Exist_Assignment%FOUND THEN
1141 CLOSE C_Is_Exist_Assignment;
1142 RETURN 'Y';
1143 END IF;
1144 CLOSE C_Is_Exist_Assignment;
1145 RETURN 'N';
1146
1147 END IS_EXIST_COND_ASSIGNMENT;
1148
1149
1150
1151 PROCEDURE VALIDATE_CUSTOM_CONDITIONS(
1152 P_API_VERSION IN NUMBER,
1153 P_INIT_MSG_LIST IN VARCHAR2,
1154 P_COMMIT IN VARCHAR2,
1155 P_VALIDATION_LEVEL IN NUMBER,
1156 P_OWNER_OBJECT_ID IN NUMBER,
1157 P_CONDITION_TYPE IN VARCHAR2,
1158 P_COMPLETE_FLAG IN VARCHAR2,
1159 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1160 X_MSG_COUNT OUT NOCOPY NUMBER,
1161 X_MSG_DATA OUT NOCOPY VARCHAR2)
1162 IS
1163
1164 /*-----------------------------------------------------------------------+
1165 | Local Variable Declarations and initializations |
1166 +-----------------------------------------------------------------------*/
1167
1168 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_CUSTOM_CONDITIONS';
1169 l_api_version CONSTANT NUMBER := 1.0;
1170 l_return_status VARCHAR2(1);
1171 l_msg_count NUMBER;
1172 l_msg_data VARCHAR2(32767);
1173
1174 l_COND_ASSIGNMENT_ID NUMBER;
1175 l_CONDITION_ID NUMBER;
1176 l_CONDITION_NAME VARCHAR2(50);
1177 l_CONDITION_DESCRIPTION VARCHAR2(250);
1178 l_CONDITION_TYPE VARCHAR2(30);
1179 l_MANDATORY_FLAG VARCHAR2(1);
1180 l_CUSTOM_PROCEDURE VARCHAR2(250);
1181 i number;
1182 l_success_count number;
1183 l_failed_count number;
1184 l_version_number number;
1185
1186 l_cond_assignment_tbl LNS_COND_ASSIGNMENT_PUB.cond_assignment_tbl_type;
1187 /*-----------------------------------------------------------------------+
1188 | Cursor Declarations |
1189 +-----------------------------------------------------------------------*/
1190
1191 /* querying all custom conditions for specified object */
1192 CURSOR custom_conditions_cur(P_OWNER_OBJECT_ID number, P_CONDITION_TYPE varchar2) IS
1193 select cond_ass.COND_ASSIGNMENT_ID,
1194 cond.CONDITION_ID,
1195 cond.CONDITION_NAME,
1196 cond.CONDITION_DESCRIPTION,
1197 cond.CONDITION_TYPE,
1198 cond_ass.MANDATORY_FLAG,
1199 cond.CUSTOM_PROCEDURE
1200 from LNS_CONDITIONS_VL cond,
1201 LNS_COND_ASSIGNMENTS cond_ass
1202 where decode(P_CONDITION_TYPE, 'APPROVAL', cond_ass.LOAN_ID,
1203 'CONVERSION', cond_ass.LOAN_ID,
1204 'DISBURSEMENT', cond_ass.DISB_HEADER_ID,
1205 cond_ass.OWNER_OBJECT_ID) = P_OWNER_OBJECT_ID
1206 and cond.custom_procedure is not null
1207 and cond.condition_type = P_CONDITION_TYPE
1208 and cond.condition_id = cond_ass.condition_id
1209 and cond_ass.END_DATE_ACTIVE is null;
1210
1211 BEGIN
1212
1213 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1214
1215 -- Standard start of API savepoint
1216 SAVEPOINT VALIDATE_CUSTOM_CONDITIONS;
1217 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Savepoint is established');
1218
1219 -- Standard call to check for call compatibility
1220 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1221 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1222 END IF;
1223
1224 -- Initialize message list if p_init_msg_list is set to TRUE
1225 IF FND_API.To_Boolean(p_init_msg_list) THEN
1226 FND_MSG_PUB.initialize;
1227 END IF;
1228
1229 -- Initialize API return status to success
1230 l_return_status := FND_API.G_RET_STS_SUCCESS;
1231
1232 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Input:');
1233 LogMessage(FND_LOG.LEVEL_STATEMENT, 'P_OWNER_OBJECT_ID = ' || P_OWNER_OBJECT_ID);
1234 LogMessage(FND_LOG.LEVEL_STATEMENT, 'P_CONDITION_TYPE = ' || P_CONDITION_TYPE);
1235 LogMessage(FND_LOG.LEVEL_STATEMENT, 'P_COMPLETE_FLAG = ' || P_COMPLETE_FLAG);
1236
1237 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Querying for custom conditions...');
1238 l_success_count := 0;
1239 l_failed_count := 0;
1240 i := 0;
1241 open custom_conditions_cur(P_OWNER_OBJECT_ID, P_CONDITION_TYPE);
1242 LOOP
1243
1244 fetch custom_conditions_cur into
1245 l_COND_ASSIGNMENT_ID,
1246 l_CONDITION_ID,
1247 l_CONDITION_NAME,
1248 l_CONDITION_DESCRIPTION,
1249 l_CONDITION_TYPE,
1250 l_MANDATORY_FLAG,
1251 l_CUSTOM_PROCEDURE;
1252 exit when custom_conditions_cur%NOTFOUND;
1253
1254 i := i + 1;
1255 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Custom condition ' || i);
1256 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_COND_ASSIGNMENT_ID = ' || l_COND_ASSIGNMENT_ID);
1257 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_ID = ' || l_CONDITION_ID);
1258 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_NAME = ' || l_CONDITION_NAME);
1259 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_DESCRIPTION = ' || l_CONDITION_DESCRIPTION);
1260 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_TYPE = ' || l_CONDITION_TYPE);
1261 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_MANDATORY_FLAG = ' || l_MANDATORY_FLAG);
1262 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CUSTOM_PROCEDURE = ' || l_CUSTOM_PROCEDURE);
1263
1264 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Validating custom condition ' || l_CONDITION_NAME || '...');
1265 VALIDATE_CUSTOM_CONDITION(
1266 P_API_VERSION => 1.0,
1267 P_INIT_MSG_LIST => FND_API.G_FALSE,
1268 P_COMMIT => FND_API.G_FALSE,
1269 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1270 P_COND_ASSIGNMENT_ID => l_COND_ASSIGNMENT_ID,
1271 X_RETURN_STATUS => l_return_status,
1272 X_MSG_COUNT => l_msg_count,
1273 X_MSG_DATA => l_msg_data);
1274
1275 IF l_return_status = 'S' THEN
1276 l_success_count := l_success_count + 1;
1277 if P_COMPLETE_FLAG = 'Y' then
1278 l_cond_assignment_tbl(i).COND_ASSIGNMENT_ID := l_COND_ASSIGNMENT_ID;
1279 l_cond_assignment_tbl(i).CONDITION_MET_FLAG := 'Y';
1280 l_cond_assignment_tbl(i).FULFILLMENT_DATE := sysdate;
1281 l_cond_assignment_tbl(i).FULFILLMENT_UPDATED_BY := lns_utility_pub.user_id;
1282 l_cond_assignment_tbl(i).MANDATORY_FLAG := l_MANDATORY_FLAG;
1283 end if;
1284 ELSE
1285 l_failed_count := l_failed_count + 1;
1286 END IF;
1287
1288 END LOOP;
1289 close custom_conditions_cur;
1290
1291 LogMessage(FND_LOG.LEVEL_STATEMENT, '-------------');
1292 if i = 0 then
1293 LogMessage(FND_LOG.LEVEL_STATEMENT, 'No custom conditions found. Exiting.');
1294 x_return_status := FND_API.G_RET_STS_SUCCESS;
1295 return;
1296 end if;
1297
1298 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully validated custom conditions: ' || l_success_count);
1299 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Failed custom conditions: ' || l_failed_count);
1300 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Total validated custom conditions: ' || i);
1301
1302 if l_failed_count > 0 then
1303 RAISE FND_API.G_EXC_ERROR;
1304 end if;
1305
1306 -- completing all custom conditions if P_COMPLETE_FLAG = 'Y'
1307 if P_COMPLETE_FLAG = 'Y' then
1308 FOR j IN 1..l_cond_assignment_tbl.COUNT LOOP
1309
1310 select OBJECT_VERSION_NUMBER into l_version_number
1311 from LNS_COND_ASSIGNMENTS
1312 where COND_ASSIGNMENT_ID = l_cond_assignment_tbl(j).COND_ASSIGNMENT_ID;
1313
1314 update_COND_ASSIGNMENT (
1315 p_init_msg_list => FND_API.G_FALSE,
1316 p_COND_ASSIGNMENT_rec => l_cond_assignment_tbl(j),
1317 p_object_version_number => l_version_number,
1318 X_RETURN_STATUS => l_return_status,
1319 X_MSG_COUNT => l_msg_count,
1320 X_MSG_DATA => l_msg_data);
1321
1322 if l_return_status <> 'S' then
1323 RAISE FND_API.G_EXC_ERROR;
1324 end if;
1325
1326 END LOOP;
1327 end if;
1328
1329 if P_COMMIT = FND_API.G_TRUE then
1330 COMMIT WORK;
1331 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Commited');
1332 end if;
1333
1334 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Successfully validated all custom conditions for object ' || P_OWNER_OBJECT_ID || '; type ' || P_CONDITION_TYPE);
1335
1336 -- END OF BODY OF API
1337 x_return_status := FND_API.G_RET_STS_SUCCESS;
1338
1339 -- Standard call to get message count and if count is 1, get message info
1340 FND_MSG_PUB.Count_And_Get(
1341 p_encoded => FND_API.G_FALSE,
1342 p_count => x_msg_count,
1343 p_data => x_msg_data);
1344
1345 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1346
1347 EXCEPTION
1348 WHEN FND_API.G_EXC_ERROR THEN
1349 ROLLBACK TO VALIDATE_CUSTOM_CONDITIONS;
1350 x_return_status := FND_API.G_RET_STS_ERROR;
1351 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1352 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
1353 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1354 ROLLBACK TO VALIDATE_CUSTOM_CONDITIONS;
1355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1356 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1357 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
1358 WHEN OTHERS THEN
1359 ROLLBACK TO VALIDATE_CUSTOM_CONDITIONS;
1360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1361 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1362 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1363 END IF;
1364 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1365 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Rollbacked');
1366 END;
1367
1368
1369
1370
1371 PROCEDURE VALIDATE_CUSTOM_CONDITION(
1372 P_API_VERSION IN NUMBER,
1373 P_INIT_MSG_LIST IN VARCHAR2,
1374 P_COMMIT IN VARCHAR2,
1375 P_VALIDATION_LEVEL IN NUMBER,
1376 P_COND_ASSIGNMENT_ID IN NUMBER,
1377 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1378 X_MSG_COUNT OUT NOCOPY NUMBER,
1379 X_MSG_DATA OUT NOCOPY VARCHAR2)
1380 IS
1381
1382 /*-----------------------------------------------------------------------+
1383 | Local Variable Declarations and initializations |
1384 +-----------------------------------------------------------------------*/
1385
1386 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_CUSTOM_CONDITION';
1387 l_api_version CONSTANT NUMBER := 1.0;
1388 l_return_status VARCHAR2(1);
1389 l_msg_count NUMBER;
1390 l_msg_data VARCHAR2(32767);
1391
1392 l_CONDITION_ID NUMBER;
1393 l_CONDITION_NAME VARCHAR2(50);
1394 l_CONDITION_DESCRIPTION VARCHAR2(250);
1395 l_CONDITION_TYPE VARCHAR2(30);
1396 l_MANDATORY_FLAG VARCHAR2(1);
1397 l_CUSTOM_PROCEDURE VARCHAR2(250);
1398 l_result varchar2(1);
1399 l_error varchar2(2000);
1400 l_plsql_block varchar2(2000);
1401
1402 /*-----------------------------------------------------------------------+
1403 | Cursor Declarations |
1404 +-----------------------------------------------------------------------*/
1405
1406 /* querying condition info */
1407 CURSOR cond_info_cur(P_COND_ASSIGNMENT_ID number) IS
1408 select cond.CONDITION_ID,
1409 cond.CONDITION_NAME,
1410 cond.CONDITION_DESCRIPTION,
1411 cond.CONDITION_TYPE,
1412 cond_ass.MANDATORY_FLAG,
1413 cond.CUSTOM_PROCEDURE
1414 from LNS_CONDITIONS_VL cond,
1415 LNS_COND_ASSIGNMENTS cond_ass
1416 where cond_ass.cond_assignment_id = P_COND_ASSIGNMENT_ID
1417 and cond.condition_id = cond_ass.condition_id
1418 and cond_ass.END_DATE_ACTIVE is null;
1419
1420 BEGIN
1421
1422 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1423
1424 -- Standard call to check for call compatibility
1425 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1426 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1427 END IF;
1428
1429 -- Initialize message list if p_init_msg_list is set to TRUE
1430 IF FND_API.To_Boolean(p_init_msg_list) THEN
1431 FND_MSG_PUB.initialize;
1432 END IF;
1433
1434 -- Initialize API return status to success
1435 l_return_status := FND_API.G_RET_STS_SUCCESS;
1436
1437 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Input:');
1438 LogMessage(FND_LOG.LEVEL_STATEMENT, 'P_COND_ASSIGNMENT_ID = ' || P_COND_ASSIGNMENT_ID);
1439
1440 /* querying condition info */
1441 open cond_info_cur(P_COND_ASSIGNMENT_ID);
1442 fetch cond_info_cur into
1443 l_CONDITION_ID,
1444 l_CONDITION_NAME,
1445 l_CONDITION_DESCRIPTION,
1446 l_CONDITION_TYPE,
1447 l_MANDATORY_FLAG,
1448 l_CUSTOM_PROCEDURE;
1449 close cond_info_cur;
1450
1451 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Condition info:');
1452 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_ID = ' || l_CONDITION_ID);
1453 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_NAME = ' || l_CONDITION_NAME);
1454 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_DESCRIPTION = ' || l_CONDITION_DESCRIPTION);
1455 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_TYPE = ' || l_CONDITION_TYPE);
1456 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_MANDATORY_FLAG = ' || l_MANDATORY_FLAG);
1457 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CUSTOM_PROCEDURE = ' || l_CUSTOM_PROCEDURE);
1458
1459 if l_CUSTOM_PROCEDURE is null then
1460 LogMessage(FND_LOG.LEVEL_STATEMENT, 'This is not custom condition. Exiting.');
1461 return;
1462 end if;
1463
1464 l_plsql_block := 'BEGIN ' || l_CUSTOM_PROCEDURE || '(:1, :2, :3); END;';
1465
1466 BEGIN
1467
1468 logMessage(FND_LOG.LEVEL_STATEMENT, 'l_plsql_block = ' || l_plsql_block);
1469 logMessage(FND_LOG.LEVEL_STATEMENT, 'Calling...');
1470
1471 EXECUTE IMMEDIATE l_plsql_block
1472 USING
1473 IN P_COND_ASSIGNMENT_ID,
1474 OUT l_result,
1475 OUT l_error;
1476
1477 logMessage(FND_LOG.LEVEL_STATEMENT, 'Done');
1478
1479 EXCEPTION
1480 WHEN OTHERS THEN
1481 FND_MESSAGE.SET_NAME('LNS', 'LNS_CUSTOM_COND_FAILED');
1482 FND_MESSAGE.SET_TOKEN('COND_NAME' ,l_CONDITION_NAME);
1483 FND_MESSAGE.SET_TOKEN('ERROR_MESG' ,SQLERRM);
1484 FND_MSG_PUB.ADD;
1485 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
1486 RAISE FND_API.G_EXC_ERROR;
1487 END;
1488
1489 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_result = ' || l_result);
1490 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_error = ' || l_error);
1491
1492 if l_result = 'N' then
1493 FND_MESSAGE.SET_NAME('LNS', 'LNS_CUSTOM_COND_FAILED');
1494 FND_MESSAGE.SET_TOKEN('COND_NAME' ,l_CONDITION_NAME);
1495 FND_MESSAGE.SET_TOKEN('ERROR_MESG' ,l_error);
1496 FND_MSG_PUB.ADD;
1497 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
1498 RAISE FND_API.G_EXC_ERROR;
1499 end if;
1500
1501 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Successfully validated custom condition ' || l_CONDITION_NAME);
1502
1503 -- END OF BODY OF API
1504 x_return_status := FND_API.G_RET_STS_SUCCESS;
1505
1506 -- Standard call to get message count and if count is 1, get message info
1507 FND_MSG_PUB.Count_And_Get(
1508 p_encoded => FND_API.G_FALSE,
1509 p_count => x_msg_count,
1510 p_data => x_msg_data);
1511
1512 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1513
1514 EXCEPTION
1515 WHEN FND_API.G_EXC_ERROR THEN
1516 x_return_status := FND_API.G_RET_STS_ERROR;
1517 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1518 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1520 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1521 WHEN OTHERS THEN
1522 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1523 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1524 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1525 END IF;
1526 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1527 END;
1528
1529
1530
1531 PROCEDURE DEFAULT_COND_ASSIGNMENTS(
1532 P_API_VERSION IN NUMBER,
1533 P_INIT_MSG_LIST IN VARCHAR2,
1534 P_COMMIT IN VARCHAR2,
1535 P_VALIDATION_LEVEL IN NUMBER,
1536 P_LOAN_ID IN NUMBER,
1537 P_OWNER_OBJECT_ID IN NUMBER,
1538 P_CONDITION_TYPE IN VARCHAR2,
1539 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1540 X_MSG_COUNT OUT NOCOPY NUMBER,
1541 X_MSG_DATA OUT NOCOPY VARCHAR2)
1542 IS
1543
1544 /*-----------------------------------------------------------------------+
1545 | Local Variable Declarations and initializations |
1546 +-----------------------------------------------------------------------*/
1547
1548 l_api_name CONSTANT VARCHAR2(30) := 'DEFAULT_COND_ASSIGNMENTS';
1549 l_api_version CONSTANT NUMBER := 1.0;
1550 l_return_status VARCHAR2(1);
1551 l_msg_count NUMBER;
1552 l_msg_data VARCHAR2(32767);
1553 l_cond_name VARCHAR2(50);
1554 i number;
1555 l_cond_assignment_id number;
1556
1557 l_cond_assignment_rec COND_ASSIGNMENT_REC_TYPE;
1558
1559 /*-----------------------------------------------------------------------+
1560 | Cursor Declarations |
1561 +-----------------------------------------------------------------------*/
1562
1563 /* querying condition info */
1564 CURSOR cond_info_cur(P_LOAN_ID number, P_CONDITION_TYPE varchar2) IS
1565 select cond_ass.CONDITION_ID,
1566 cond.CONDITION_NAME,
1567 cond.CONDITION_DESCRIPTION,
1568 cond_ass.MANDATORY_FLAG,
1569 cond_ass.DELETE_DISABLED_FLAG
1570 from LNS_CONDITIONS_VL cond,
1571 LNS_COND_ASSIGNMENTS cond_ass
1572 where cond_ass.LOAN_ID = P_LOAN_ID
1573 and cond.CONDITION_TYPE = P_CONDITION_TYPE
1574 and cond.condition_id = cond_ass.condition_id
1575 and decode(cond.CONDITION_TYPE, 'DISBURSEMENT', cond_ass.DISB_HEADER_ID, cond_ass.OWNER_OBJECT_ID) is null
1576 and cond_ass.END_DATE_ACTIVE is null;
1577
1578 BEGIN
1579
1580 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1581
1582 -- Standard start of API savepoint
1583 SAVEPOINT DEFAULT_COND_ASSIGNMENTS;
1584 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Savepoint is established');
1585
1586 -- Standard call to check for call compatibility
1587 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1588 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1589 END IF;
1590
1591 -- Initialize message list if p_init_msg_list is set to TRUE
1592 IF FND_API.To_Boolean(p_init_msg_list) THEN
1593 FND_MSG_PUB.initialize;
1594 END IF;
1595
1596 -- Initialize API return status to success
1597 l_return_status := FND_API.G_RET_STS_SUCCESS;
1598
1599 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Input:');
1600 LogMessage(FND_LOG.LEVEL_STATEMENT, 'P_LOAN_ID = ' || P_LOAN_ID);
1601 LogMessage(FND_LOG.LEVEL_STATEMENT, 'P_OWNER_OBJECT_ID = ' || P_OWNER_OBJECT_ID);
1602 LogMessage(FND_LOG.LEVEL_STATEMENT, 'P_CONDITION_TYPE = ' || P_CONDITION_TYPE);
1603
1604 if P_LOAN_ID is null then
1605 FND_MESSAGE.SET_NAME( 'LNS', 'LNS_API_MISSING_COLUMN' );
1606 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'P_LOAN_ID' );
1607 FND_MSG_PUB.ADD;
1608 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
1609 RAISE FND_API.G_EXC_ERROR;
1610 end if;
1611
1612 if P_OWNER_OBJECT_ID is null then
1613 FND_MESSAGE.SET_NAME( 'LNS', 'LNS_API_MISSING_COLUMN' );
1614 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'P_OWNER_OBJECT_ID' );
1615 FND_MSG_PUB.ADD;
1616 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
1617 RAISE FND_API.G_EXC_ERROR;
1618 end if;
1619
1620 if P_CONDITION_TYPE is null then
1621 FND_MESSAGE.SET_NAME( 'LNS', 'LNS_API_MISSING_COLUMN' );
1622 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'P_CONDITION_TYPE' );
1623 FND_MSG_PUB.ADD;
1624 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
1625 RAISE FND_API.G_EXC_ERROR;
1626 end if;
1627
1628 if P_CONDITION_TYPE <> 'LOAN_AMOUNT_ADJUSTMENT' and
1629 P_CONDITION_TYPE <> 'ADDITIONAL_RECEIVABLE' and
1630 P_CONDITION_TYPE <> 'DISBURSEMENT'
1631 then
1632 FND_MESSAGE.SET_NAME( 'LNS', 'LNS_INVALID_VALUE' );
1633 FND_MESSAGE.SET_TOKEN( 'PARAMETER', 'P_CONDITION_TYPE' );
1634 FND_MESSAGE.SET_TOKEN( 'VALUE', P_CONDITION_TYPE );
1635 FND_MSG_PUB.ADD;
1636 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
1637 RAISE FND_API.G_EXC_ERROR;
1638 end if;
1639
1640 i := 0;
1641 open cond_info_cur(P_LOAN_ID, P_CONDITION_TYPE);
1642 LOOP
1643
1644 fetch cond_info_cur into
1645 l_cond_assignment_rec.CONDITION_ID,
1646 l_cond_name,
1647 l_cond_assignment_rec.CONDITION_DESCRIPTION,
1648 l_cond_assignment_rec.MANDATORY_FLAG,
1649 l_cond_assignment_rec.DELETE_DISABLED_FLAG;
1650 exit when cond_info_cur%NOTFOUND;
1651
1652 l_cond_assignment_rec.cond_assignment_id := null;
1653 l_cond_assignment_rec.LOAN_ID := P_LOAN_ID;
1654 l_cond_assignment_rec.CONDITION_MET_FLAG := 'N';
1655 l_cond_assignment_rec.FULFILLMENT_DATE := NULL ;
1656 l_cond_assignment_rec.FULFILLMENT_UPDATED_BY := NULL;
1657 l_cond_assignment_rec.CREATED_BY := NULL;
1658 l_cond_assignment_rec.CREATION_DATE := NULL;
1659 l_cond_assignment_rec.LAST_UPDATED_BY := NULL;
1660 l_cond_assignment_rec.LAST_UPDATE_DATE := NULL;
1661 l_cond_assignment_rec.LAST_UPDATE_LOGIN := NULL;
1662 l_cond_assignment_rec.OBJECT_VERSION_NUMBER := 1;
1663
1664 if P_CONDITION_TYPE = 'LOAN_AMOUNT_ADJUSTMENT' then
1665 l_cond_assignment_rec.OWNER_OBJECT_ID := P_OWNER_OBJECT_ID;
1666 l_cond_assignment_rec.OWNER_TABLE := 'LNS_LOAN_AMOUNT_ADJS';
1667 l_cond_assignment_rec.DISB_HEADER_ID := NULL;
1668 elsif P_CONDITION_TYPE = 'ADDITIONAL_RECEIVABLE' then
1669 l_cond_assignment_rec.OWNER_OBJECT_ID := P_OWNER_OBJECT_ID;
1670 l_cond_assignment_rec.OWNER_TABLE := 'LNS_LOAN_LINES';
1671 l_cond_assignment_rec.DISB_HEADER_ID := NULL;
1672 elsif P_CONDITION_TYPE = 'DISBURSEMENT' then
1673 l_cond_assignment_rec.OWNER_OBJECT_ID := null;
1674 l_cond_assignment_rec.OWNER_TABLE := 'LNS_DISB_HEADERS';
1675 l_cond_assignment_rec.DISB_HEADER_ID := P_OWNER_OBJECT_ID;
1676 end if;
1677
1678 i := i + 1;
1679 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Creating cond_ass ' || i);
1680 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CONDITION_ID = ' || l_cond_assignment_rec.CONDITION_ID);
1681 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CONDITION_NAME = ' || l_cond_name);
1682 LogMessage(FND_LOG.LEVEL_STATEMENT, 'CONDITION_DESCRIPTION = ' || l_cond_assignment_rec.CONDITION_DESCRIPTION);
1683 LogMessage(FND_LOG.LEVEL_STATEMENT, 'MANDATORY_FLAG = ' || l_cond_assignment_rec.MANDATORY_FLAG);
1684 LogMessage(FND_LOG.LEVEL_STATEMENT, 'DELETE_DISABLED_FLAG = ' || l_cond_assignment_rec.DELETE_DISABLED_FLAG);
1685 LogMessage(FND_LOG.LEVEL_STATEMENT, 'OWNER_OBJECT_ID = ' || l_cond_assignment_rec.OWNER_OBJECT_ID);
1686 LogMessage(FND_LOG.LEVEL_STATEMENT, 'OWNER_TABLE = ' || l_cond_assignment_rec.OWNER_TABLE);
1687 LogMessage(FND_LOG.LEVEL_STATEMENT, 'DISB_HEADER_ID = ' || l_cond_assignment_rec.DISB_HEADER_ID);
1688
1689 -- call to business logic.
1690 do_create_COND_ASSIGNMENT(l_cond_assignment_rec,
1691 l_cond_assignment_id,
1692 x_return_status) ;
1693 LogMessage(FND_LOG.LEVEL_STATEMENT, 'COND_ASSIGNMENT_ID = ' || l_cond_assignment_id);
1694
1695 END LOOP;
1696 close cond_info_cur;
1697
1698 if P_COMMIT = FND_API.G_TRUE then
1699 COMMIT WORK;
1700 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Commited');
1701 end if;
1702
1703 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Successfully created ' || i || ' condition assignments');
1704
1705 -- END OF BODY OF API
1706 x_return_status := FND_API.G_RET_STS_SUCCESS;
1707
1708 -- Standard call to get message count and if count is 1, get message info
1709 FND_MSG_PUB.Count_And_Get(
1710 p_encoded => FND_API.G_FALSE,
1711 p_count => x_msg_count,
1712 p_data => x_msg_data);
1713
1714 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1715
1716 EXCEPTION
1717 WHEN FND_API.G_EXC_ERROR THEN
1718 ROLLBACK TO DEFAULT_COND_ASSIGNMENTS;
1719 x_return_status := FND_API.G_RET_STS_ERROR;
1720 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1721 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1722 ROLLBACK TO DEFAULT_COND_ASSIGNMENTS;
1723 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1724 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1725 WHEN OTHERS THEN
1726 ROLLBACK TO DEFAULT_COND_ASSIGNMENTS;
1727 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1728 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1729 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1730 END IF;
1731 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1732 END;
1733
1734
1735
1736 PROCEDURE VALIDATE_NONCUSTOM_CONDITIONS(
1737 P_API_VERSION IN NUMBER,
1738 P_INIT_MSG_LIST IN VARCHAR2,
1739 P_COMMIT IN VARCHAR2,
1740 P_VALIDATION_LEVEL IN NUMBER,
1741 P_OWNER_OBJECT_ID IN NUMBER,
1742 P_CONDITION_TYPE IN VARCHAR2,
1743 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1744 X_MSG_COUNT OUT NOCOPY NUMBER,
1745 X_MSG_DATA OUT NOCOPY VARCHAR2)
1746 IS
1747
1748 /*-----------------------------------------------------------------------+
1749 | Local Variable Declarations and initializations |
1750 +-----------------------------------------------------------------------*/
1751
1752 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_NONCUSTOM_CONDITIONS';
1753 l_api_version CONSTANT NUMBER := 1.0;
1754 l_return_status VARCHAR2(1);
1755 l_msg_count NUMBER;
1756 l_msg_data VARCHAR2(32767);
1757 l_cond_count NUMBER;
1758
1759 /*-----------------------------------------------------------------------+
1760 | Cursor Declarations |
1761 +-----------------------------------------------------------------------*/
1762
1763 -- checking for number of not met non-custom conditions
1764 CURSOR conditions_cur(P_OWNER_OBJECT_ID number, P_CONDITION_TYPE varchar2) IS
1765 select count(1)
1766 from LNS_CONDITIONS_VL cond,
1767 LNS_COND_ASSIGNMENTS cond_ass
1768 where
1769 decode(P_CONDITION_TYPE, 'APPROVAL', cond_ass.LOAN_ID,
1770 'CONVERSION', cond_ass.LOAN_ID,
1771 'DISBURSEMENT', cond_ass.DISB_HEADER_ID,
1772 cond_ass.OWNER_OBJECT_ID) = P_OWNER_OBJECT_ID
1773 and cond.custom_procedure is null
1774 and cond.condition_type = P_CONDITION_TYPE
1775 and cond.condition_id = cond_ass.condition_id
1776 and cond_ass.END_DATE_ACTIVE is null
1777 and cond_ass.MANDATORY_FLAG = 'Y'
1778 and (cond_ass.CONDITION_MET_FLAG is null or cond_ass.CONDITION_MET_FLAG = 'N');
1779
1780 BEGIN
1781
1782 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1783
1784 -- Standard call to check for call compatibility
1785 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1786 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1787 END IF;
1788
1789 -- Initialize message list if p_init_msg_list is set to TRUE
1790 IF FND_API.To_Boolean(p_init_msg_list) THEN
1791 FND_MSG_PUB.initialize;
1792 END IF;
1793
1794 -- Initialize API return status to success
1795 l_return_status := FND_API.G_RET_STS_SUCCESS;
1796
1797 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Input:');
1798 LogMessage(FND_LOG.LEVEL_STATEMENT, 'P_OWNER_OBJECT_ID = ' || P_OWNER_OBJECT_ID);
1799 LogMessage(FND_LOG.LEVEL_STATEMENT, 'P_CONDITION_TYPE = ' || P_CONDITION_TYPE);
1800
1801 -- checking for number of not met non-custom conditions
1802 open conditions_cur(P_OWNER_OBJECT_ID, P_CONDITION_TYPE);
1803 fetch conditions_cur into l_cond_count;
1804 close conditions_cur;
1805 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_cond_count = ' || l_cond_count);
1806
1807 if l_cond_count > 0 then
1808 FND_MESSAGE.SET_NAME('LNS', 'LNS_NOT_ALL_COND_MET');
1809 FND_MSG_PUB.Add;
1810 RAISE FND_API.G_EXC_ERROR;
1811 end if;
1812
1813 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Successfully validated all non-custom conditions for object ' || P_OWNER_OBJECT_ID || '; type ' || P_CONDITION_TYPE);
1814
1815 -- END OF BODY OF API
1816 x_return_status := FND_API.G_RET_STS_SUCCESS;
1817
1818 -- Standard call to get message count and if count is 1, get message info
1819 FND_MSG_PUB.Count_And_Get(
1820 p_encoded => FND_API.G_FALSE,
1821 p_count => x_msg_count,
1822 p_data => x_msg_data);
1823
1824 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1825
1826 EXCEPTION
1827 WHEN FND_API.G_EXC_ERROR THEN
1828 x_return_status := FND_API.G_RET_STS_ERROR;
1829 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1830 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1832 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1833 WHEN OTHERS THEN
1834 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1835 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)THEN
1836 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1837 END IF;
1838 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1839 END;
1840
1841
1842
1843
1844 BEGIN
1845 G_LOG_ENABLED := 'N';
1846 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
1847
1848 /* getting msg logging info */
1849 G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
1850 if (G_LOG_ENABLED = 'N') then
1851 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
1852 else
1853 G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
1854 end if;
1855
1856 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
1857 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_MSG_LEVEL: ' || G_MSG_LEVEL);
1858
1859 END LNS_COND_ASSIGNMENT_PUB;