[Home] [Help]
PACKAGE BODY: APPS.LNS_SAMPLE_CUSTOM_CONDS
Source
1 PACKAGE BODY LNS_SAMPLE_CUSTOM_CONDS as
2 /* $Header: LNS_SMPL_CUSTOM_CONDS_B.pls 120.0.12010000.1 2010/03/22 15:30:21 scherkas noship $ */
3
4
5 /*=======================================================================+
6 | Package Global Constants
7 +=======================================================================*/
8 G_PKG_NAME CONSTANT VARCHAR2(30):= 'LNS_SAMPLE_CUSTOM_CONDS';
9 G_LOG_ENABLED varchar2(5);
10 G_MSG_LEVEL NUMBER;
11
12
13 /*========================================================================
14 | PRIVATE PROCEDURE LogMessage
15 |
16 | DESCRIPTION
17 | This procedure logs debug messages to db and to CM log
18 |
19 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
20 |
21 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
22 | None
23 |
24 | PARAMETERS
25 | p_msg_level IN Debug msg level
26 | p_msg IN Debug msg itself
27 |
28 | KNOWN ISSUES
29 | None
30 |
31 | NOTES
32 | Any interesting aspect of the code in the package body which needs
33 | to be stated.
34 |
35 | MODIFICATION HISTORY
36 | Date Author Description of Changes
37 | 04-02-2008 scherkas Created
38 |
39 *=======================================================================*/
40 Procedure LogMessage(p_msg_level IN NUMBER, p_msg in varchar2)
41 IS
42 BEGIN
43 if (p_msg_level >= G_MSG_LEVEL) then
44
45 FND_LOG.STRING(p_msg_level, G_PKG_NAME, p_msg);
46 if FND_GLOBAL.Conc_Request_Id is not null then
47 fnd_file.put_line(FND_FILE.LOG, p_msg);
48 end if;
49
50 end if;
51
52 EXCEPTION
53 WHEN OTHERS THEN
54 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
55 END;
56
57
58 /*========================================================================
59 | PUBLIC PROCEDURE VALIDATE_INCREASE_LOAN_AMOUNT1
60 |
61 | DESCRIPTION
62 | This procedure implements sample validation of increase loan amount - increase of loan amount must not be greater
63 | than 10% of original loan amount.
64 |
65 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
66 | None
67 |
68 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
69 |
70 |
71 | PSEUDO CODE/LOGIC
72 | 100%*(increase_amount/original_loan_amount) <= 10%
73 |
74 | PARAMETERS
75 | P_COND_ASSIGNMENT_ID IN Condition Assignment ID
76 | X_CONDITION_MET OUT NOCOPY Returned value that indicates if condition is met or not. Valid values Y, N
77 | X_ERROR OUT NOCOPY If condition is not met this returned error message explains why.
78 |
79 | KNOWN ISSUES
80 | None
81 |
82 | NOTES
83 | Any interesting aspect of the code in the package body which needs
84 | to be stated.
85 |
86 | MODIFICATION HISTORY
87 | Date Author Description of Changes
88 | 12-07-2009 scherkas Created
89 |
90 *=======================================================================*/
91 PROCEDURE VALIDATE_INCREASE_LOAN_AMOUNT1(
92 P_COND_ASSIGNMENT_ID IN NUMBER,
93 X_CONDITION_MET OUT NOCOPY VARCHAR2,
94 X_ERROR OUT NOCOPY VARCHAR2)
95 IS
96
97 /*-----------------------------------------------------------------------+
98 | Local Variable Declarations and initializations |
99 +-----------------------------------------------------------------------*/
100
101 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_INCREASE_LOAN_AMOUNT1';
102 l_LOAN_ID NUMBER;
103 l_CONDITION_ID NUMBER;
104 l_CONDITION_NAME VARCHAR2(50);
105 l_CONDITION_DESCRIPTION VARCHAR2(250);
106 l_CONDITION_TYPE VARCHAR2(30);
107 l_MANDATORY_FLAG VARCHAR2(1);
108 l_OWNER_OBJECT_ID NUMBER;
109 l_OWNER_TABLE VARCHAR2(100);
110 l_REQUESTED_AMOUNT NUMBER;
111 l_column varchar2(100);
112 l_ratio number;
113 l_pass_ratio number;
114 l_increase_amount number;
115 l_where_clause varchar2(2000);
116 l_query_str varchar2(2000);
117
118 /*-----------------------------------------------------------------------+
119 | Cursor Declarations |
120 +-----------------------------------------------------------------------*/
121
122 /* querying condition info */
123 CURSOR cond_info_cur(P_COND_ASSIGNMENT_ID number) IS
124 select cond_ass.LOAN_ID,
125 cond_ass.OWNER_OBJECT_ID,
126 cond_ass.OWNER_TABLE,
127 cond.CONDITION_ID,
128 cond.CONDITION_NAME,
129 cond.CONDITION_DESCRIPTION,
130 cond.CONDITION_TYPE,
131 cond.MANDATORY_FLAG
132 from LNS_CONDITIONS_VL cond,
133 LNS_COND_ASSIGNMENTS cond_ass
134 where cond_ass.COND_ASSIGNMENT_ID = P_COND_ASSIGNMENT_ID
135 and cond.condition_id = cond_ass.condition_id;
136
137 /* querying loan requested amount */
138 CURSOR loan_info_cur(P_LOAN_ID number) IS
139 select REQUESTED_AMOUNT
140 from lns_loan_headers_all
141 where loan_id = P_LOAN_ID;
142
143 BEGIN
144
145 X_CONDITION_MET := 'Y';
146 l_pass_ratio := 10;
147
148 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
149
150 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Input:');
151 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'P_COND_ASSIGNMENT_ID = ' || P_COND_ASSIGNMENT_ID);
152
153 if P_COND_ASSIGNMENT_ID is null then
154 X_CONDITION_MET := 'N';
155 X_ERROR := 'P_COND_ASSIGNMENT_ID must be set';
156 return;
157 end if;
158
159 /* querying condition info */
160 open cond_info_cur(P_COND_ASSIGNMENT_ID);
161 fetch cond_info_cur into
162 l_LOAN_ID,
163 l_OWNER_OBJECT_ID,
164 l_OWNER_TABLE,
165 l_CONDITION_ID,
166 l_CONDITION_NAME,
167 l_CONDITION_DESCRIPTION,
168 l_CONDITION_TYPE,
169 l_MANDATORY_FLAG;
170 close cond_info_cur;
171
172 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Condition info:');
173 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_LOAN_ID = ' || l_LOAN_ID);
174 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_ID = ' || l_CONDITION_ID);
175 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_NAME = ' || l_CONDITION_NAME);
176 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_DESCRIPTION = ' || l_CONDITION_DESCRIPTION);
177 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_TYPE = ' || l_CONDITION_TYPE);
178 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_MANDATORY_FLAG = ' || l_MANDATORY_FLAG);
179 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_OWNER_OBJECT_ID = ' || l_OWNER_OBJECT_ID);
180 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_OWNER_TABLE = ' || l_OWNER_TABLE);
181
182 /* querying loan requested amount */
183 open loan_info_cur(l_LOAN_ID);
184 fetch loan_info_cur into l_REQUESTED_AMOUNT;
185 close loan_info_cur;
186
187 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_REQUESTED_AMOUNT = ' || l_REQUESTED_AMOUNT);
188
189 /* querying increase loan amount */
190 if l_CONDITION_TYPE = 'LOAN_AMOUNT_ADJUSTMENT' then
191 l_column := 'ADJUSTMENT_AMOUNT';
192 l_where_clause := 'LOAN_AMOUNT_ADJ_ID';
193 elsif l_CONDITION_TYPE = 'ADDITIONAL_RECEIVABLE' then
194 l_column := 'REQUESTED_AMOUNT';
195 l_where_clause := 'LOAN_LINE_ID';
196 end if;
197
198 l_query_str := ' Select ' || l_column || ' From ' || l_OWNER_TABLE || ' where ' || l_where_clause || ' = ' || l_OWNER_OBJECT_ID;
199 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_query_str: ' || l_query_str);
200
201 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Executing query...');
202 Execute Immediate l_query_str into l_increase_amount;
203 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done');
204 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_increase_amount = ' || l_increase_amount);
205
206 l_ratio := 100*l_increase_amount/l_REQUESTED_AMOUNT;
207 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_ratio = ' || l_ratio);
208 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_pass_ratio = ' || l_pass_ratio);
209
210 if l_ratio > l_pass_ratio then
211 X_CONDITION_MET := 'N';
212 X_ERROR := 'Increase of loan amount must not be greater than ' || l_pass_ratio || '% of original loan amount.';
213 end if;
214
215 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
216
217 EXCEPTION
218 WHEN OTHERS THEN
219 X_ERROR := sqlerrm;
220 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME || '.' || l_api_name || ' - In exception. Error - ' || X_ERROR);
221
222 END;
223
224
225
226 /*========================================================================
227 | PUBLIC PROCEDURE VALIDATE_LOAN_APPR_COND1
228 |
229 | DESCRIPTION
230 | This procedure implements sample validation for loan approval.
231 | This procedure always returns success.
232 |
233 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
234 | None
235 |
236 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
237 |
238 |
239 | PSEUDO CODE/LOGIC
240 | 100%*(increase_amount/original_loan_amount) <= 10%
241 |
242 | PARAMETERS
243 | P_COND_ASSIGNMENT_ID IN Condition Assignment ID
244 | X_CONDITION_MET OUT NOCOPY Returned value that indicates if condition is met or not. Valid values Y, N
245 | X_ERROR OUT NOCOPY If condition is not met this returned error message explains why.
246 |
247 | KNOWN ISSUES
248 | None
249 |
250 | NOTES
251 | Any interesting aspect of the code in the package body which needs
252 | to be stated.
253 |
254 | MODIFICATION HISTORY
255 | Date Author Description of Changes
256 | 12-07-2009 scherkas Created
257 |
258 *=======================================================================*/
259 PROCEDURE VALIDATE_LOAN_APPR_COND1(
260 P_COND_ASSIGNMENT_ID IN NUMBER,
261 X_CONDITION_MET OUT NOCOPY VARCHAR2,
262 X_ERROR OUT NOCOPY VARCHAR2)
263 IS
264
265 /*-----------------------------------------------------------------------+
266 | Local Variable Declarations and initializations |
267 +-----------------------------------------------------------------------*/
268
269 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_LOAN_APPR_COND1';
270
271 /*-----------------------------------------------------------------------+
272 | Cursor Declarations |
273 +-----------------------------------------------------------------------*/
274
275 BEGIN
276
277 X_CONDITION_MET := 'Y';
278
279 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
280
281 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Input:');
282 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'P_COND_ASSIGNMENT_ID = ' || P_COND_ASSIGNMENT_ID);
283
284 if P_COND_ASSIGNMENT_ID is null then
285 X_CONDITION_MET := 'N';
286 X_ERROR := 'P_COND_ASSIGNMENT_ID must be set';
287 end if;
288
289 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
290
291 EXCEPTION
292 WHEN OTHERS THEN
293 X_ERROR := sqlerrm;
294 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME || '.' || l_api_name || ' - In exception. Error - ' || X_ERROR);
295
296 END;
297
298
299
300 /*========================================================================
301 | PUBLIC PROCEDURE VALIDATE_LOAN_APPR_COND2
302 |
303 | DESCRIPTION
304 | This procedure implements sample validation for loan approval.
305 | This procedure always returns failure.
306 |
307 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
308 | None
309 |
310 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
311 |
312 |
313 | PSEUDO CODE/LOGIC
314 | 100%*(increase_amount/original_loan_amount) <= 10%
315 |
316 | PARAMETERS
317 | P_COND_ASSIGNMENT_ID IN Condition Assignment ID
318 | X_CONDITION_MET OUT NOCOPY Returned value that indicates if condition is met or not. Valid values Y, N
319 | X_ERROR OUT NOCOPY If condition is not met this returned error message explains why.
320 |
321 | KNOWN ISSUES
322 | None
323 |
324 | NOTES
325 | Any interesting aspect of the code in the package body which needs
326 | to be stated.
327 |
328 | MODIFICATION HISTORY
329 | Date Author Description of Changes
330 | 12-07-2009 scherkas Created
331 |
332 *=======================================================================*/
333 PROCEDURE VALIDATE_LOAN_APPR_COND2(
334 P_COND_ASSIGNMENT_ID IN NUMBER,
335 X_CONDITION_MET OUT NOCOPY VARCHAR2,
336 X_ERROR OUT NOCOPY VARCHAR2)
337 IS
338
339 /*-----------------------------------------------------------------------+
340 | Local Variable Declarations and initializations |
341 +-----------------------------------------------------------------------*/
342
343 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_LOAN_APPR_COND2';
344
345 /*-----------------------------------------------------------------------+
346 | Cursor Declarations |
347 +-----------------------------------------------------------------------*/
348
349 BEGIN
350
351 X_CONDITION_MET := 'N';
352 X_ERROR := 'VALIDATE_LOAN_APPR_COND2 has failed';
353
354 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
355
356 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Input:');
357 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'P_COND_ASSIGNMENT_ID = ' || P_COND_ASSIGNMENT_ID);
358
359 if P_COND_ASSIGNMENT_ID is null then
360 X_CONDITION_MET := 'N';
361 X_ERROR := 'P_COND_ASSIGNMENT_ID must be set';
362 end if;
363
364 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
365
366 EXCEPTION
367 WHEN OTHERS THEN
368 X_ERROR := sqlerrm;
369 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME || '.' || l_api_name || ' - In exception. Error - ' || X_ERROR);
370
371 END;
372
373
374
375 /*========================================================================
376 | PUBLIC PROCEDURE VALIDATE_DISB_AMOUNT
377 |
378 | DESCRIPTION
379 | This procedure implements sample validation of disbursement amount - disbursement amount must be greater then
380 | or equal to 20% of current loan amount.
381 |
382 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
383 | None
384 |
385 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
386 |
387 |
388 | PSEUDO CODE/LOGIC
389 | 100%*(disbursement_amount/loan_amount) >= 20%
390 |
391 | PARAMETERS
392 | P_COND_ASSIGNMENT_ID IN Condition Assignment ID
393 | X_CONDITION_MET OUT NOCOPY Returned value that indicates if condition is met or not. Valid values Y, N
394 | X_ERROR OUT NOCOPY If condition is not met this returned error message explains why.
395 |
396 | KNOWN ISSUES
397 | None
398 |
399 | NOTES
400 | Any interesting aspect of the code in the package body which needs
401 | to be stated.
402 |
403 | MODIFICATION HISTORY
404 | Date Author Description of Changes
405 | 12-07-2009 scherkas Created
406 |
407 *=======================================================================*/
408 PROCEDURE VALIDATE_DISB_AMOUNT(
409 P_COND_ASSIGNMENT_ID IN NUMBER,
410 X_CONDITION_MET OUT NOCOPY VARCHAR2,
411 X_ERROR OUT NOCOPY VARCHAR2)
412 IS
413
414 /*-----------------------------------------------------------------------+
415 | Local Variable Declarations and initializations |
416 +-----------------------------------------------------------------------*/
417
418 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_DISB_AMOUNT';
419 l_LOAN_ID NUMBER;
420 l_CONDITION_ID NUMBER;
421 l_CONDITION_NAME VARCHAR2(50);
422 l_CONDITION_DESCRIPTION VARCHAR2(250);
423 l_CONDITION_TYPE VARCHAR2(30);
424 l_MANDATORY_FLAG VARCHAR2(1);
425 l_OWNER_OBJECT_ID NUMBER;
426 l_OWNER_TABLE VARCHAR2(100);
427 l_REQUESTED_AMOUNT NUMBER;
428 l_column varchar2(100);
429 l_ratio number;
430 l_pass_ratio number;
431 l_disb_amount number;
432 l_where_clause varchar2(2000);
433 l_query_str varchar2(2000);
434
435 /*-----------------------------------------------------------------------+
436 | Cursor Declarations |
437 +-----------------------------------------------------------------------*/
438
439 /* querying condition info */
440 CURSOR cond_info_cur(P_COND_ASSIGNMENT_ID number) IS
441 select cond_ass.LOAN_ID,
442 cond_ass.DISB_HEADER_ID,
443 'LNS_DISB_HEADERS',
444 cond.CONDITION_ID,
445 cond.CONDITION_NAME,
446 cond.CONDITION_DESCRIPTION,
447 cond.CONDITION_TYPE,
448 cond.MANDATORY_FLAG
449 from LNS_CONDITIONS_VL cond,
450 LNS_COND_ASSIGNMENTS cond_ass
451 where cond_ass.COND_ASSIGNMENT_ID = P_COND_ASSIGNMENT_ID
452 and cond.condition_id = cond_ass.condition_id;
453
454 /* querying loan requested amount */
455 CURSOR loan_info_cur(P_LOAN_ID number) IS
456 select REQUESTED_AMOUNT + nvl(ADD_REQUESTED_AMOUNT, 0)
457 from lns_loan_headers_all
458 where loan_id = P_LOAN_ID;
459
460 BEGIN
461
462 X_CONDITION_MET := 'Y';
463 l_pass_ratio := 20;
464
465 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
466
467 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Input:');
468 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'P_COND_ASSIGNMENT_ID = ' || P_COND_ASSIGNMENT_ID);
469
470 if P_COND_ASSIGNMENT_ID is null then
471 X_CONDITION_MET := 'N';
472 X_ERROR := 'P_COND_ASSIGNMENT_ID must be set';
473 return;
474 end if;
475
476 /* querying condition info */
477 open cond_info_cur(P_COND_ASSIGNMENT_ID);
478 fetch cond_info_cur into
479 l_LOAN_ID,
480 l_OWNER_OBJECT_ID,
481 l_OWNER_TABLE,
482 l_CONDITION_ID,
483 l_CONDITION_NAME,
484 l_CONDITION_DESCRIPTION,
485 l_CONDITION_TYPE,
486 l_MANDATORY_FLAG;
487 close cond_info_cur;
488
489 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Condition info:');
490 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_LOAN_ID = ' || l_LOAN_ID);
491 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_ID = ' || l_CONDITION_ID);
492 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_NAME = ' || l_CONDITION_NAME);
493 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_DESCRIPTION = ' || l_CONDITION_DESCRIPTION);
494 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_TYPE = ' || l_CONDITION_TYPE);
495 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_MANDATORY_FLAG = ' || l_MANDATORY_FLAG);
496 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_OWNER_OBJECT_ID = ' || l_OWNER_OBJECT_ID);
497 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_OWNER_TABLE = ' || l_OWNER_TABLE);
498
499 /* querying loan requested amount */
500 open loan_info_cur(l_LOAN_ID);
501 fetch loan_info_cur into l_REQUESTED_AMOUNT;
502 close loan_info_cur;
503
504 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_REQUESTED_AMOUNT = ' || l_REQUESTED_AMOUNT);
505
506 /* querying increase loan amount */
507 if l_CONDITION_TYPE = 'DISBURSEMENT' then
508 l_column := 'HEADER_AMOUNT';
509 l_where_clause := 'DISB_HEADER_ID';
510 end if;
511
512 l_query_str := ' Select ' || l_column || ' From ' || l_OWNER_TABLE || ' where ' || l_where_clause || ' = ' || l_OWNER_OBJECT_ID;
513 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_query_str: ' || l_query_str);
514
515 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Executing query...');
516 Execute Immediate l_query_str into l_disb_amount;
517 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done');
518 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_disb_amount = ' || l_disb_amount);
519
520 l_ratio := 100*l_disb_amount/l_REQUESTED_AMOUNT;
521 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_ratio = ' || l_ratio);
522 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_pass_ratio = ' || l_pass_ratio);
523
524 if l_ratio < l_pass_ratio then
525 X_CONDITION_MET := 'N';
526 X_ERROR := 'Disbursement amount must be at least ' || l_pass_ratio || '% of loan amount.';
527 end if;
528
529 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
530
531 EXCEPTION
532 WHEN OTHERS THEN
533 X_ERROR := sqlerrm;
534 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME || '.' || l_api_name || ' - In exception. Error - ' || X_ERROR);
535
536 END;
537
538
539
540 /*========================================================================
541 | PUBLIC PROCEDURE VALIDATE_DISB1_AMOUNT
542 |
543 | DESCRIPTION
544 | This procedure implements sample validation of disbursement amount - disbursement amount must be
545 | or equal to 50% of current loan amount.
546 |
547 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
548 | None
549 |
550 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
551 |
552 |
553 | PSEUDO CODE/LOGIC
554 | 100%*(disbursement_amount/loan_amount) = 50%
555 |
556 | PARAMETERS
557 | P_COND_ASSIGNMENT_ID IN Condition Assignment ID
558 | X_CONDITION_MET OUT NOCOPY Returned value that indicates if condition is met or not. Valid values Y, N
559 | X_ERROR OUT NOCOPY If condition is not met this returned error message explains why.
560 |
561 | KNOWN ISSUES
562 | None
563 |
564 | NOTES
565 | Any interesting aspect of the code in the package body which needs
566 | to be stated.
567 |
568 | MODIFICATION HISTORY
569 | Date Author Description of Changes
570 | 12-07-2009 scherkas Created
571 |
572 *=======================================================================*/
573 PROCEDURE VALIDATE_DISB1_AMOUNT(
574 P_COND_ASSIGNMENT_ID IN NUMBER,
575 X_CONDITION_MET OUT NOCOPY VARCHAR2,
576 X_ERROR OUT NOCOPY VARCHAR2)
577 IS
578
579 /*-----------------------------------------------------------------------+
580 | Local Variable Declarations and initializations |
581 +-----------------------------------------------------------------------*/
582
583 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_DISB1_AMOUNT';
584 l_LOAN_ID NUMBER;
585 l_CONDITION_ID NUMBER;
586 l_CONDITION_NAME VARCHAR2(50);
587 l_CONDITION_DESCRIPTION VARCHAR2(250);
588 l_CONDITION_TYPE VARCHAR2(30);
589 l_MANDATORY_FLAG VARCHAR2(1);
590 l_OWNER_OBJECT_ID NUMBER;
591 l_OWNER_TABLE VARCHAR2(100);
592 l_REQUESTED_AMOUNT NUMBER;
593 l_column varchar2(100);
594 l_ratio number;
595 l_pass_ratio number;
596 l_disb_amount number;
597 l_where_clause varchar2(2000);
598 l_query_str varchar2(2000);
599
600 /*-----------------------------------------------------------------------+
601 | Cursor Declarations |
602 +-----------------------------------------------------------------------*/
603
604 /* querying condition info */
605 CURSOR cond_info_cur(P_COND_ASSIGNMENT_ID number) IS
606 select cond_ass.LOAN_ID,
607 cond_ass.DISB_HEADER_ID,
608 'LNS_DISB_HEADERS',
609 cond.CONDITION_ID,
610 cond.CONDITION_NAME,
611 cond.CONDITION_DESCRIPTION,
612 cond.CONDITION_TYPE,
613 cond.MANDATORY_FLAG
614 from LNS_CONDITIONS_VL cond,
615 LNS_COND_ASSIGNMENTS cond_ass
616 where cond_ass.COND_ASSIGNMENT_ID = P_COND_ASSIGNMENT_ID
617 and cond.condition_id = cond_ass.condition_id;
618
619 /* querying loan requested amount */
620 CURSOR loan_info_cur(P_LOAN_ID number) IS
621 select REQUESTED_AMOUNT + nvl(ADD_REQUESTED_AMOUNT, 0)
622 from lns_loan_headers_all
623 where loan_id = P_LOAN_ID;
624
625 BEGIN
626
627 X_CONDITION_MET := 'Y';
628 l_pass_ratio := 50;
629
630 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
631
632 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Input:');
633 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'P_COND_ASSIGNMENT_ID = ' || P_COND_ASSIGNMENT_ID);
634
635 if P_COND_ASSIGNMENT_ID is null then
636 X_CONDITION_MET := 'N';
637 X_ERROR := 'P_COND_ASSIGNMENT_ID must be set';
638 return;
639 end if;
640
641 /* querying condition info */
642 open cond_info_cur(P_COND_ASSIGNMENT_ID);
643 fetch cond_info_cur into
644 l_LOAN_ID,
645 l_OWNER_OBJECT_ID,
646 l_OWNER_TABLE,
647 l_CONDITION_ID,
648 l_CONDITION_NAME,
649 l_CONDITION_DESCRIPTION,
650 l_CONDITION_TYPE,
651 l_MANDATORY_FLAG;
652 close cond_info_cur;
653
654 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Condition info:');
655 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_LOAN_ID = ' || l_LOAN_ID);
656 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_ID = ' || l_CONDITION_ID);
657 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_NAME = ' || l_CONDITION_NAME);
658 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_DESCRIPTION = ' || l_CONDITION_DESCRIPTION);
659 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_TYPE = ' || l_CONDITION_TYPE);
660 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_MANDATORY_FLAG = ' || l_MANDATORY_FLAG);
661 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_OWNER_OBJECT_ID = ' || l_OWNER_OBJECT_ID);
662 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_OWNER_TABLE = ' || l_OWNER_TABLE);
663
664 /* querying loan requested amount */
665 open loan_info_cur(l_LOAN_ID);
666 fetch loan_info_cur into l_REQUESTED_AMOUNT;
667 close loan_info_cur;
668
669 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_REQUESTED_AMOUNT = ' || l_REQUESTED_AMOUNT);
670
671 /* querying increase loan amount */
672 if l_CONDITION_TYPE = 'DISBURSEMENT' then
673 l_column := 'HEADER_AMOUNT';
674 l_where_clause := 'DISB_HEADER_ID';
675 end if;
676
677 l_query_str := ' Select ' || l_column || ' From ' || l_OWNER_TABLE || ' where ' || l_where_clause || ' = ' || l_OWNER_OBJECT_ID;
678 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_query_str: ' || l_query_str);
679
680 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Executing query...');
681 Execute Immediate l_query_str into l_disb_amount;
682 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done');
683 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_disb_amount = ' || l_disb_amount);
684
685 l_ratio := 100*l_disb_amount/l_REQUESTED_AMOUNT;
686 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_ratio = ' || l_ratio);
687 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_pass_ratio = ' || l_pass_ratio);
688
689 if l_ratio <> l_pass_ratio then
690 X_CONDITION_MET := 'N';
691 X_ERROR := 'Disbursement amount must be equal to ' || l_pass_ratio || '% of loan amount.';
692 end if;
693
694 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
695
696 EXCEPTION
697 WHEN OTHERS THEN
698 X_ERROR := sqlerrm;
699 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME || '.' || l_api_name || ' - In exception. Error - ' || X_ERROR);
700
701 END;
702
703
704
705 /*========================================================================
706 | PUBLIC PROCEDURE VALIDATE_NUM_DISB_IN_MONTH
707 |
708 | DESCRIPTION
709 | This procedure implements sample validation of disbursement -
710 | number of disbursements in any given month cannot be greater than 1
711 |
712 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
713 | None
714 |
715 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
716 |
717 |
718 | PSEUDO CODE/LOGIC
719 |
720 | PARAMETERS
721 | P_COND_ASSIGNMENT_ID IN Condition Assignment ID
722 | X_CONDITION_MET OUT NOCOPY Returned value that indicates if condition is met or not. Valid values Y, N
723 | X_ERROR OUT NOCOPY If condition is not met this returned error message explains why.
724 |
725 | KNOWN ISSUES
726 | None
727 |
728 | NOTES
729 | Any interesting aspect of the code in the package body which needs
730 | to be stated.
731 |
732 | MODIFICATION HISTORY
733 | Date Author Description of Changes
734 | 12-07-2009 scherkas Created
735 |
736 *=======================================================================*/
737 PROCEDURE VALIDATE_NUM_DISB_IN_MONTH(
738 P_COND_ASSIGNMENT_ID IN NUMBER,
739 X_CONDITION_MET OUT NOCOPY VARCHAR2,
740 X_ERROR OUT NOCOPY VARCHAR2)
741 IS
742
743 /*-----------------------------------------------------------------------+
744 | Local Variable Declarations and initializations |
745 +-----------------------------------------------------------------------*/
746
747 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_NUM_DISB_IN_MONTH';
748 l_LOAN_ID NUMBER;
749 l_CONDITION_ID NUMBER;
750 l_CONDITION_NAME VARCHAR2(50);
751 l_CONDITION_DESCRIPTION VARCHAR2(250);
752 l_CONDITION_TYPE VARCHAR2(30);
753 l_MANDATORY_FLAG VARCHAR2(1);
754 l_OWNER_OBJECT_ID NUMBER;
755 l_OWNER_TABLE VARCHAR2(100);
756 l_due_date DATE;
757 l_column varchar2(100);
758 l_ratio number;
759 l_pass_ratio number;
760 l_disb_amount number;
761 l_where_clause varchar2(2000);
762 l_query_str varchar2(2000);
763
764 /*-----------------------------------------------------------------------+
765 | Cursor Declarations |
766 +-----------------------------------------------------------------------*/
767
768 /* querying condition info */
769 CURSOR cond_info_cur(P_COND_ASSIGNMENT_ID number) IS
770 select cond_ass.LOAN_ID,
771 cond_ass.DISB_HEADER_ID,
772 'LNS_DISB_HEADERS',
773 cond.CONDITION_ID,
774 cond.CONDITION_NAME,
775 cond.CONDITION_DESCRIPTION,
776 cond.CONDITION_TYPE,
777 cond.MANDATORY_FLAG
778 from LNS_CONDITIONS_VL cond,
779 LNS_COND_ASSIGNMENTS cond_ass
780 where cond_ass.COND_ASSIGNMENT_ID = P_COND_ASSIGNMENT_ID
781 and cond.condition_id = cond_ass.condition_id;
782
783 /* querying loan requested amount */
784 CURSOR due_date_cur(P_DISB_HEADER_ID number) IS
785 select PAYMENT_REQUEST_DATE
786 from lns_disb_headers
787 where disb_header_id = P_DISB_HEADER_ID;
788
789 BEGIN
790
791 X_CONDITION_MET := 'Y';
792 l_pass_ratio := 1;
793
794 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
795
796 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Input:');
797 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'P_COND_ASSIGNMENT_ID = ' || P_COND_ASSIGNMENT_ID);
798
799 if P_COND_ASSIGNMENT_ID is null then
800 X_CONDITION_MET := 'N';
801 X_ERROR := 'P_COND_ASSIGNMENT_ID must be set';
802 return;
803 end if;
804
805 /* querying condition info */
806 open cond_info_cur(P_COND_ASSIGNMENT_ID);
807 fetch cond_info_cur into
808 l_LOAN_ID,
809 l_OWNER_OBJECT_ID,
810 l_OWNER_TABLE,
811 l_CONDITION_ID,
812 l_CONDITION_NAME,
813 l_CONDITION_DESCRIPTION,
814 l_CONDITION_TYPE,
815 l_MANDATORY_FLAG;
816 close cond_info_cur;
817
818 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Condition info:');
819 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_LOAN_ID = ' || l_LOAN_ID);
820 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_ID = ' || l_CONDITION_ID);
821 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_NAME = ' || l_CONDITION_NAME);
822 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_DESCRIPTION = ' || l_CONDITION_DESCRIPTION);
823 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_TYPE = ' || l_CONDITION_TYPE);
824 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_MANDATORY_FLAG = ' || l_MANDATORY_FLAG);
825 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_OWNER_OBJECT_ID = ' || l_OWNER_OBJECT_ID);
826 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_OWNER_TABLE = ' || l_OWNER_TABLE);
827
828 /* querying loan requested amount */
829 open due_date_cur(l_OWNER_OBJECT_ID);
830 fetch due_date_cur into l_due_date;
831 close due_date_cur;
832
833 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_due_date = ' || l_due_date);
834
835 /* querying increase loan amount */
836 if l_CONDITION_TYPE = 'DISBURSEMENT' then
837 l_column := 'count(1)';
838 l_where_clause := 'loan_id = :1 and trunc(PAYMENT_REQUEST_DATE, ''MONTH'') = trunc(:2' ||
839 ', ''MONTH'') and (status is null or status <> ''CANCELLED'')';
840 end if;
841
842 l_query_str := ' Select ' || l_column || ' From ' || l_OWNER_TABLE || ' where ' || l_where_clause;
843 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_query_str: ' || l_query_str);
844
845 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Executing query...');
846 Execute Immediate l_query_str into l_ratio USING l_LOAN_ID, l_due_date;
847 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done');
848 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_ratio = ' || l_ratio);
849
850 if l_ratio > l_pass_ratio then
851 X_CONDITION_MET := 'N';
852 X_ERROR := 'Number of disbursement in any given month cannot be greater than ' || l_pass_ratio;
853 end if;
854
855 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
856
857 EXCEPTION
858 WHEN OTHERS THEN
859 X_ERROR := sqlerrm;
860 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME || '.' || l_api_name || ' - In exception. Error - ' || X_ERROR);
861
862 END;
863
864
865
866 /*========================================================================
867 | PUBLIC PROCEDURE VALIDATE_NUM_DISB_IN_YEAR
868 |
869 | DESCRIPTION
870 | This procedure implements sample validation of disbursement -
871 | number of disbursements in calendar year cannot be greater than 4
872 |
873 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
874 | None
875 |
876 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
877 |
878 |
879 | PSEUDO CODE/LOGIC
880 |
881 | PARAMETERS
882 | P_COND_ASSIGNMENT_ID IN Condition Assignment ID
883 | X_CONDITION_MET OUT NOCOPY Returned value that indicates if condition is met or not. Valid values Y, N
884 | X_ERROR OUT NOCOPY If condition is not met this returned error message explains why.
885 |
886 | KNOWN ISSUES
887 | None
888 |
889 | NOTES
890 | Any interesting aspect of the code in the package body which needs
891 | to be stated.
892 |
893 | MODIFICATION HISTORY
894 | Date Author Description of Changes
895 | 12-07-2009 scherkas Created
896 |
897 *=======================================================================*/
898 PROCEDURE VALIDATE_NUM_DISB_IN_YEAR(
899 P_COND_ASSIGNMENT_ID IN NUMBER,
900 X_CONDITION_MET OUT NOCOPY VARCHAR2,
901 X_ERROR OUT NOCOPY VARCHAR2)
902 IS
903
904 /*-----------------------------------------------------------------------+
905 | Local Variable Declarations and initializations |
906 +-----------------------------------------------------------------------*/
907
908 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_NUM_DISB_IN_YEAR';
909 l_LOAN_ID NUMBER;
910 l_CONDITION_ID NUMBER;
911 l_CONDITION_NAME VARCHAR2(50);
912 l_CONDITION_DESCRIPTION VARCHAR2(250);
913 l_CONDITION_TYPE VARCHAR2(30);
914 l_MANDATORY_FLAG VARCHAR2(1);
915 l_OWNER_OBJECT_ID NUMBER;
916 l_OWNER_TABLE VARCHAR2(100);
917 l_due_date DATE;
918 l_column varchar2(100);
919 l_ratio number;
920 l_pass_ratio number;
921 l_disb_amount number;
922 l_where_clause varchar2(2000);
923 l_query_str varchar2(2000);
924
925 /*-----------------------------------------------------------------------+
926 | Cursor Declarations |
927 +-----------------------------------------------------------------------*/
928
929 /* querying condition info */
930 CURSOR cond_info_cur(P_COND_ASSIGNMENT_ID number) IS
931 select cond_ass.LOAN_ID,
932 cond_ass.DISB_HEADER_ID,
933 'LNS_DISB_HEADERS',
934 cond.CONDITION_ID,
935 cond.CONDITION_NAME,
936 cond.CONDITION_DESCRIPTION,
937 cond.CONDITION_TYPE,
938 cond.MANDATORY_FLAG
939 from LNS_CONDITIONS_VL cond,
940 LNS_COND_ASSIGNMENTS cond_ass
941 where cond_ass.COND_ASSIGNMENT_ID = P_COND_ASSIGNMENT_ID
942 and cond.condition_id = cond_ass.condition_id;
943
944 /* querying loan requested amount */
945 CURSOR due_date_cur(P_DISB_HEADER_ID number) IS
946 select PAYMENT_REQUEST_DATE
947 from lns_disb_headers
948 where disb_header_id = P_DISB_HEADER_ID;
949
950 BEGIN
951
952 X_CONDITION_MET := 'Y';
953 l_pass_ratio := 4;
954
955 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
956
957 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Input:');
958 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'P_COND_ASSIGNMENT_ID = ' || P_COND_ASSIGNMENT_ID);
959
960 if P_COND_ASSIGNMENT_ID is null then
961 X_CONDITION_MET := 'N';
962 X_ERROR := 'P_COND_ASSIGNMENT_ID must be set';
963 return;
964 end if;
965
966 /* querying condition info */
967 open cond_info_cur(P_COND_ASSIGNMENT_ID);
968 fetch cond_info_cur into
969 l_LOAN_ID,
970 l_OWNER_OBJECT_ID,
971 l_OWNER_TABLE,
972 l_CONDITION_ID,
973 l_CONDITION_NAME,
974 l_CONDITION_DESCRIPTION,
975 l_CONDITION_TYPE,
976 l_MANDATORY_FLAG;
977 close cond_info_cur;
978
979 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Condition info:');
980 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_LOAN_ID = ' || l_LOAN_ID);
981 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_ID = ' || l_CONDITION_ID);
982 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_NAME = ' || l_CONDITION_NAME);
983 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_DESCRIPTION = ' || l_CONDITION_DESCRIPTION);
984 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_CONDITION_TYPE = ' || l_CONDITION_TYPE);
985 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_MANDATORY_FLAG = ' || l_MANDATORY_FLAG);
986 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_OWNER_OBJECT_ID = ' || l_OWNER_OBJECT_ID);
987 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_OWNER_TABLE = ' || l_OWNER_TABLE);
988
989 /* querying loan requested amount */
990 open due_date_cur(l_OWNER_OBJECT_ID);
991 fetch due_date_cur into l_due_date;
992 close due_date_cur;
993
994 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_due_date = ' || l_due_date);
995
996 /* querying increase loan amount */
997 if l_CONDITION_TYPE = 'DISBURSEMENT' then
998 l_column := 'count(1)';
999 l_where_clause := 'loan_id = :1 and trunc(PAYMENT_REQUEST_DATE, ''YEAR'') = trunc(:2' ||
1000 ', ''YEAR'') and (status is null or status <> ''CANCELLED'')';
1001 end if;
1002
1003 l_query_str := ' Select ' || l_column || ' From ' || l_OWNER_TABLE || ' where ' || l_where_clause;
1004 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_query_str: ' || l_query_str);
1005
1006 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Executing query...');
1007 Execute Immediate l_query_str into l_ratio USING l_LOAN_ID, l_due_date;
1008 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Done');
1009 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_ratio = ' || l_ratio);
1010
1011 if l_ratio > l_pass_ratio then
1012 X_CONDITION_MET := 'N';
1013 X_ERROR := 'Number of disbursements in calendar year cannot be greater than ' || l_pass_ratio;
1014 end if;
1015
1016 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1017
1018 EXCEPTION
1019 WHEN OTHERS THEN
1020 X_ERROR := sqlerrm;
1021 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME || '.' || l_api_name || ' - In exception. Error - ' || X_ERROR);
1022
1023 END;
1024
1025
1026
1027 BEGIN
1028 G_LOG_ENABLED := 'N';
1029 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
1030
1031 /* getting msg logging info */
1032 G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
1033 if (G_LOG_ENABLED = 'N') then
1034 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
1035 else
1036 G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
1037 end if;
1038
1039 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
1040 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_MSG_LEVEL: ' || G_MSG_LEVEL);
1041
1042 END;