[Home] [Help]
PACKAGE BODY: APPS.LNS_SUBMIT_FOR_APPROVAL_PUB
Source
1 PACKAGE BODY LNS_SUBMIT_FOR_APPROVAL_PUB as
2 /* $Header: LNS_SUBMIT_FOR_APPROVAL_PUB_B.pls 120.0.12000000.4 2007/05/09 11:35:07 mbolli noship $ */
3 /*=======================================================================+
4 | Package Global Constants
5 +=======================================================================*/
6 G_PKG_NAME CONSTANT VARCHAR2(30):= 'LNS_SUBMIT_FOR_APPROVAL_PUB';
7 G_LOG_ENABLED varchar2(5);
8 G_MSG_LEVEL NUMBER;
9 g_errors_rec Loan_Sub_For_Appr_err_type := Loan_Sub_For_Appr_err_type();
10 g_error_count number := 0;
11 PROCEDURE IS_LOAN_AMOUNT_VALID(P_LOAN_ID IN NUMBER, P_STATUS OUT NOCOPY VARCHAR2);
12 PROCEDURE IS_LOAN_TO_VALUE_MET(P_LOAN_ID IN NUMBER, P_STATUS OUT NOCOPY VARCHAR2);
13 PROCEDURE VALIDATE_LOAN(P_LOAN_ID IN NUMBER, P_VALID OUT NOCOPY VARCHAR2);
14 PROCEDURE IS_COLLATERAL_EXIST(P_LOAN_ID IN NUMBER, P_EXIST OUT NOCOPY VARCHAR2);
15 PROCEDURE GET_LOAN_APPROVAL_ACCESS(P_LOAN_ID IN NUMBER, P_ACCESS OUT NOCOPY VARCHAR2);
16 PROCEDURE ARE_CONDITIONS_MET(P_LOAN_ID IN NUMBER,
17 P_LOAN_STATUS IN VARCHAR2,
18 P_LOAN_PHASE IN VARCHAR2,
19 P_OPEN_TO_TERM_EVENT IN VARCHAR2,
20 P_COND_MET OUT NOCOPY VARCHAR2
21 );
22 /*========================================================================
23 | PRIVATE PROCEDURE LogMessage
24 |
25 | DESCRIPTION
26 | This procedure logs debug messages to db and to CM log
27 |
28 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
29 |
30 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
31 | None
32 |
33 | PARAMETERS
34 | p_msg_level IN Debug msg level
35 | p_msg IN Debug msg itself
36 |
37 | KNOWN ISSUES
38 | None
39 |
40 |
41 | NOTES
42 | Any interesting aspect of the code in the package body which needs
43 | to be stated.
44 |
45 | MODIFICATION HISTORY
46 | Date Author Description of Changes
47 | 28-Mar-2007 MBOLLI Created
48 |
49 *=======================================================================*/
50 Procedure LogMessage(p_msg_level IN NUMBER, p_msg in varchar2)
51 IS
52 BEGIN
53 if (p_msg_level >= G_MSG_LEVEL) then
54
55 FND_LOG.STRING(p_msg_level, G_PKG_NAME, p_msg);
56
57 end if;
58
59 EXCEPTION
60 WHEN OTHERS THEN
61 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
62 END;
63 /*========================================================================
64 | PRIVATE PROCEDURE LogErrors
65 |
66 | DESCRIPTION
67 | This procedure logs debug messages to db and to CM log
68 |
69 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
70 |
71 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
72 | None
73 |
74 | PARAMETERS
75 | p_msg_level IN Debug msg level
76 | p_msg IN Debug msg itself
77 |
78 | KNOWN ISSUES
79 | None
80 |
81 |
82 | NOTES
83 | This procedure builds the error message and stores it (alongwith
84 | other columns in LNS_LOAN_API_ERRORS_GT) in g_errors_rec.
85 |
86 | MODIFICATION HISTORY
87 | Date Author Description of Changes
88 | 28-Mar-2007 MBOLLI Created
89 |
90 *=======================================================================*/
91 Procedure LogErrors( p_message_name IN VARCHAR2
92 ,p_line_number IN NUMBER DEFAULT NULL
93 ,p_token1 IN VARCHAR2 DEFAULT NULL
94 ,p_token2 IN VARCHAR2 DEFAULT NULL
95 ,p_token3 IN VARCHAR2 DEFAULT NULL
96 ,p_loan_id IN VARCHAR2 DEFAULT NULL
97 ,p_api_name IN VARCHAR2 DEFAULT NULL
98 ,p_err_msg IN VARCHAR2 DEFAULT NULL)
99 IS
100 l_text LNS_LOAN_API_ERRORS_GT.MESSAGE_TEXT%TYPE;
101 BEGIN
102 fnd_message.set_name('LNS', p_message_name);
103 if p_token1 is NOT NULL THEN
104 fnd_message.set_token('TOKEN1',p_token1);
105 end if;
106 IF p_token2 is NOT NULL THEN
107 fnd_message.set_token('TOKEN2',p_token2);
108 END IF;
109 IF p_token3 is NOT NULL THEN
110 fnd_message.set_token('TOKEN3',p_token3);
111 END IF;
112 IF p_loan_id is NOT NULL THEN
113 fnd_message.set_token('LOANNUMBER',p_loan_id);
114 END IF;
115 IF p_api_name is NOT NULL THEN
116 fnd_message.set_token('APINAME',p_api_name);
117 END IF;
118 IF p_err_msg is NOT NULL THEN
119 fnd_message.set_token('ERRMSG',p_err_msg);
120 END IF;
121
122 l_text := substrb(fnd_message.get,1,2000);
123 g_error_count := g_error_count+1;
124 g_errors_rec.extend(1);
125 g_errors_rec(g_error_count).ERROR_NUMBER := g_error_count;
126 g_errors_rec(g_error_count).MESSAGE_NAME := p_message_name;
127 g_errors_rec(g_error_count).MESSAGE_TEXT := l_text;
128 g_errors_rec(g_error_count).LINE_NUMBER := p_line_number;
129
130 EXCEPTION
131 WHEN OTHERS THEN
132 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
133 END;
134 /*========================================================================
135 | PRIVATE PROCEDURE InsertErrors
136 |
137 | DESCRIPTION
138 | This procedure logs debug messages to db and to CM log
139 |
140 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
141 |
142 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
143 | None
144 |
145 | PARAMETERS
146 | p_msg_level IN Debug msg level
147 | p_msg IN Debug msg itself
148 |
149 | KNOWN ISSUES
150 | None
151 |
152 |
153 | NOTES
154 | Any interesting aspect of the code in the package body which needs
155 | to be stated.
156 |
157 | MODIFICATION HISTORY
158 | Date Author Description of Changes
159 | 28-Mar-2007 MBOLLI Created
160 |
161 *=======================================================================*/
162 Procedure InsertErrors
163 IS
164 BEGIN
165 FORALL i in 1..g_errors_rec.COUNT
166 insert into LNS_LOAN_API_ERRORS_GT
167 VALUES
168 g_errors_rec(i);
169 EXCEPTION
170 WHEN OTHERS THEN
171 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
172 END;
173
174
175
176 PROCEDURE SUBMIT_FOR_APPROVAL(
177 P_API_VERSION IN NUMBER,
178 P_COMMIT IN VARCHAR2,
179 P_APPROVAL_ACTION_REC IN LNS_APPROVAL_ACTION_PUB.APPROVAL_ACTION_REC_TYPE,
180 P_AUTO_FUNDING_FLAG IN VARCHAR2,
181 X_ACTION_ID OUT NOCOPY NUMBER,
182 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
183 X_MSG_COUNT OUT NOCOPY NUMBER) IS
184 /*-----------------------------------------------------------------------+
185 | Local Variable Declarations and initializations |
186 +-----------------------------------------------------------------------*/
187
188 l_api_name CONSTANT VARCHAR2(30) := 'SUBMIT_FOR_APPROVAL';
189 l_api_version CONSTANT NUMBER := 1.0;
190 l_return_status VARCHAR2(1);
191 l_msg_count NUMBER;
192 l_msg_data VARCHAR2(32767);
193 l_loan_id NUMBER;
194 l_loan_class LNS_LOAN_HEADERS.LOAN_CLASS_CODE%TYPE;
195 l_loan_status LNS_LOAN_HEADERS.LOAN_STATUS%TYPE;
196 l_sec_status LNS_LOAN_HEADERS.SECONDARY_STATUS%TYPE;
197 l_credit_review_flag LNS_LOAN_HEADERS.CREDIT_REVIEW_FLAG%TYPE;
198 l_curr_phase LNS_LOAN_HEADERS.CURRENT_PHASE%TYPE;
199 l_open_to_term_event LNS_LOAN_HEADERS.OPEN_TO_TERM_EVENT%TYPE;
200 l_are_conditions_met VARCHAR2(1);
201 l_loan_approval_access VARCHAR2(1);
202 l_validate_loan VARCHAR2(1);
203 l_amount_valid VARCHAR2(10);
204 l_loan_value_met VARCHAR2(1);
205 l_collateral_exist VARCHAR2(1);
206 l_ready_for_submit_approval VARCHAR2(1);
207 l_apply_submit_for_approval VARCHAR2(1);
208 -- l_auto_funding_flag VARCHAR2(1);
209 l_approval_action_rec LNS_APPROVAL_ACTION_PUB.APPROVAL_ACTION_REC_TYPE;
210 l_action_id NUMBER;
211 l_collateral_percent VARCHAR2(3);
212
213 /*-----------------------------------------------------------------------+
214 | Cursor Declarations |
215 +-----------------------------------------------------------------------*/
216
217 CURSOR Cur_loan_details(p_loan_id NUMBER) IS
218 SELECT
219 loan_class_code, loan_status, secondary_status, credit_review_flag, current_phase, open_to_term_event
220 FROM
221 lns_loan_headers
222 WHERE
223 loan_id = p_loan_id;
224
225 BEGIN
226 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
227
228 l_loan_id := P_APPROVAL_ACTION_REC.loan_id;
229
230 IF ((l_loan_id IS NULL) OR (trim(l_loan_id) = '')) THEN
231 LogErrors(p_message_name=>'LNS_LCREATE_NULL_VALUE'
232 ,p_token1=>' loan_Id is '||l_loan_id);
233 END IF;
234
235 OPEN Cur_loan_details(l_loan_id);
236
237
238 FETCH Cur_loan_details into
239 l_loan_class, l_loan_status, l_sec_status, l_credit_review_flag, l_curr_phase, l_open_to_term_event;
240
241 IF Cur_loan_details%NOTFOUND THEN
242 LogErrors(p_message_name=>'LNS_LCREATE_INVALID_ATTRIBUTE'
243 ,p_token1=> 'Loan ID = '||l_loan_id);
244 RAISE FND_API.G_EXC_ERROR;
245 END IF;
246 CLOSE Cur_loan_details;
247
248 IF l_loan_status = 'INCOMPLETE' THEN
249 IF (l_credit_review_flag = 'Y' AND ( l_sec_status IS NULL OR l_sec_status NOT IN ('IN_CREDIT_REVIEW', 'CREDIT_REVIEW_COMPLETE'))) THEN
250 LogErrors(p_message_name=>'LNS_CREDIT_REVIEW_REQUIRED'
251 ,p_loan_id=>l_loan_id);
252 ELSIF (l_credit_review_flag = 'Y' AND ( l_sec_status IS NULL OR l_sec_status = 'CREDIT_REVIEW_COMPLETE')) THEN
253 LogErrors(p_message_name=>'LNS_LOAN_UNFREEZE'
254 ,p_loan_id=>l_loan_id);
255 ELSIF (l_sec_status NOT IN ('IN_CREDIT_REVIEW') OR l_sec_status IS NULL) THEN
256 ARE_CONDITIONS_MET(l_loan_id, l_loan_status, l_curr_phase, l_open_to_term_event, l_are_conditions_met);
257 IF l_are_conditions_met = 'N' THEN
258 LogErrors(p_message_name=>'LNS_NOT_ALL_COND_MET');
259 ELSE
260 GET_LOAN_APPROVAL_ACCESS(l_loan_id, l_loan_approval_access);
261 IF (l_loan_approval_access = 'N') THEN
262 l_ready_for_submit_approval := 'Y';
263 ELSE
264 LogErrors(p_message_name=>'LNS_LOAN_APPROVAL_NOT_REQUIRED'
265 ,p_loan_id=>l_loan_id);
266 END IF;
267
268 END IF;
269
270 END IF;
271 END IF;
272
273 IF g_error_count = 0 THEN
274 l_apply_submit_for_approval := 'Y';
275 ELSE
276 RAISE FND_API.G_EXC_ERROR;
277 END IF;
278
279 IF (l_ready_for_submit_approval = 'Y') THEN
280 l_apply_submit_for_approval := 'N';
281
282 --SELECT VALIDATE_LOAN(l_loan_id) into l_validate_loan FROM DUAL;
283 --SELECT IS_LOAN_AMOUNT_VALID(l_loan_id) into l_amount_valid FROM DUAL;
284 --SELECT IS_LOAN_TO_VALUE_MET(l_loan_id) into l_loan_value_met FROM DUAL;
285 --SELECT IS_COLLATERAL_EXIST(l_loan_id) into l_collateral_exist FROM DUAL;
286
287 VALIDATE_LOAN(l_loan_id,l_validate_loan);
288 IS_LOAN_AMOUNT_VALID(l_loan_id,l_amount_valid);
289 IS_LOAN_TO_VALUE_MET(l_loan_id,l_loan_value_met);
290 IS_COLLATERAL_EXIST(l_loan_id,l_collateral_exist);
291
292 IF l_validate_loan = 'N' THEN
293 LogErrors(p_message_name=>'LNS_FEE_INSTLMNT_ERROR'
294 ,p_token1=>' for loan ID '||l_loan_id);
295 ELSIF ((l_loan_class = 'ERS') AND (NOT l_amount_valid = 'VALID')) THEN
296 LogErrors(p_message_name=>'LNS_LOAN_AMOUNT_ERROR'
297 ,p_token1=>' for loan ID '||l_loan_id);
298
299 ELSIF l_loan_value_met = 'N' THEN
300 SELECT
301 to_char(loan.collateral_percent) || '%' into l_collateral_percent
302 FROM
303 lns_loan_headers loan
304 WHERE
305 loan.loan_id = l_loan_id;
306
307 LogErrors(p_message_name=>'LNS_LOAN_TO_VALUE_ERROR'
308 ,p_token1=>'#COLLATERAL_PERCENT'
309 ,p_token2=>l_collateral_percent);
310
311 ELSIF l_collateral_exist = 'N' THEN
312 LogErrors(p_message_name=>'LNS_MISSING_COLLATERAL_ERROR'
313 ,p_token1=>' for loan ID '||l_loan_id);
314 ELSIF l_loan_class = 'DIRECT' THEN
315 lns_funding_pub.validate_disb_for_appr(
316 P_API_VERSION => 1.0,
317 P_INIT_MSG_LIST => FND_API.G_TRUE,
318 P_COMMIT => FND_API.G_FALSE,
319 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
320 P_LOAN_ID => l_loan_id,
321 X_RETURN_STATUS => l_return_status,
322 X_MSG_COUNT => l_msg_count,
323 X_MSG_DATA => l_msg_data
324 );
325 IF l_return_status <> 'S' THEN
326 LogErrors(p_message_name=>'LNS_PLSQL_API_ERROR'
327 ,p_api_name => 'lns_funding_pub.validate_disb_for_app()'
328 ,p_err_msg => l_msg_data);
329 RAISE FND_API.G_EXC_ERROR;
330 END IF;
331 END IF;
332
333 IF g_error_count = 0 THEN
334 l_apply_submit_for_approval := 'Y';
335 ELSE
336 RAISE FND_API.G_EXC_ERROR;
337 END IF;
338 END IF;
339
340 IF l_apply_submit_for_approval = 'Y' THEN
341
342 IF (l_loan_class = 'DIRECT') THEN
343 lns_funding_pub.set_autofunding(
344 P_API_VERSION => 1.0,
345 P_INIT_MSG_LIST => FND_API.G_TRUE,
346 P_COMMIT => FND_API.G_FALSE,
347 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
348 P_LOAN_ID => l_loan_id,
349 P_AUTOFUNDING_FLAG => p_auto_funding_flag,
350 X_RETURN_STATUS => l_return_status,
351 X_MSG_COUNT => l_msg_count,
352 X_MSG_DATA => l_msg_data
353 );
354 END IF;
355
356 IF l_return_status <> 'S' THEN
357 LogErrors(p_message_name=>'LNS_PLSQL_API_ERROR'
358 ,p_api_name => 'lns_funding_pub.set_autofunding()'
359 ,p_err_msg => l_msg_data);
360 RAISE FND_API.G_EXC_ERROR;
361 END IF;
362
363
364 LNS_APPROVAL_ACTION_PUB.create_approval_action (p_init_msg_list => FND_API.G_TRUE,
365 p_approval_action_rec => p_approval_action_rec,
366 x_action_id => l_action_id,
367 X_RETURN_STATUS => l_return_status,
368 X_MSG_COUNT => l_msg_count,
369 X_MSG_DATA => l_msg_data
370 );
371
372 IF l_return_status <> 'S' THEN
373 LogErrors(p_message_name=>'LNS_PLSQL_API_ERROR'
374 ,p_api_name => 'LNS_APPROVAL_ACTION_PUB.create_approval_action()'
375 ,p_err_msg => l_msg_data);
376 RAISE FND_API.G_EXC_ERROR;
377 END IF;
378 END IF;
379
380 IF g_error_count > 0 THEN
381 InsertErrors;
382 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
383 X_MSG_COUNT := g_error_count;
384 ELSE
385 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
386 X_MSG_COUNT := 0;
387 END IF;
388 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
389 EXCEPTION
390 WHEN FND_API.G_EXC_ERROR THEN
391 InsertErrors;
392 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
393 X_MSG_COUNT := g_error_count;
394
395 WHEN OTHERS THEN
396 InsertErrors;
397 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
398 X_MSG_COUNT := g_error_count;
399 -- raise;
400
401 END SUBMIT_FOR_APPROVAL;
402
403
404
405 /*========================================================================
406 | PROCEDURE VALIDATE_LOAN
407 |
408 | DESCRIPTION
409 | This procedure checks whether the fee installment has any error
410 | in table lns_fee_assignments.
411 |
412 | PSEUDO CODE/LOGIC
413 |
414 | PARAMETERS
415 | P_LOAN_ID IN Loan Id
416 | P_VALID IN Valid Status
417 | KNOWN ISSUES
418 | None
419 |
420 | NOTES
421 |
422 | MODIFICATION HISTORY
423 | Date Author Description of Changes
424 | 29-Mar-2007 Mbolli Created
425 |
426 *=======================================================================*/
427
428 PROCEDURE VALIDATE_LOAN(P_LOAN_ID IN NUMBER, P_VALID OUT NOCOPY VARCHAR2)
429 IS
430
431 /*-----------------------------------------------------------------------+
432 | Local Variable Declarations and initializations |
433 +-----------------------------------------------------------------------*/
434 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_LOAN';
435 l_return_status VARCHAR2(1);
436 l_msg_count NUMBER;
437 l_msg_data VARCHAR2(32767);
438 l_count NUMBER := -1;
439
440 BEGIN
441
442 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
443
444 SELECT
445 count(1) into l_count
446 FROM
447 lns_fee_assignments
448 WHERE
449 loan_id = p_loan_id
450 and end_installment_number > lns_fin_utils.getnumberinstallments(p_loan_id);
451
452 IF l_count > 0 THEN
453 p_valid := 'N';
454 ELSE
455 p_valid := 'Y';
456 END IF;
457
458 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
459
460 END VALIDATE_LOAN;
461
462
463
464 /*========================================================================
465 | PROCEDURE IS_LOAN_TO_VALUE_MET
466 |
467 | DESCRIPTION
468 | This procedure returns 'Y' if the loan amount value meets the assets.
469 |
470 | PSEUDO CODE/LOGIC
471 |
472 | PARAMETERS
473 | P_LOAN_ID IN Loan Id
474 | P_STATUS IN Value Met Status
475 |
476 | KNOWN ISSUES
477 | None
478 |
479 | NOTES
480 |
481 | MODIFICATION HISTORY
482 | Date Author Description of Changes
483 | 29-Mar-2007 Mbolli Created
484 |
485 *=======================================================================*/
486
487 PROCEDURE IS_LOAN_TO_VALUE_MET(P_LOAN_ID IN NUMBER, P_STATUS OUT NOCOPY VARCHAR2)
488 IS
489
490 /*-----------------------------------------------------------------------+
491 | Local Variable Declarations and initializations |
492 +-----------------------------------------------------------------------*/
493 l_api_name CONSTANT VARCHAR2(30) := 'IS_LOAN_TO_VALUE_MET';
494 l_count NUMBER;
495
496 /*-----------------------------------------------------------------------+
497 | Cursor Declarations |
498 +-----------------------------------------------------------------------*/
499
500 CURSOR Cur_Loan_Value_Met(p_loan_id IN NUMBER) IS
501 SELECT
502 count(1)
503 FROM
504 lns_loan_headers loan
505 WHERE
506 loan.loan_id = p_loan_id
507 and (loan.loan_subtype <> 'SECURED'
508 OR loan.collateral_percent <=
509 (SELECT
510 nvl(sum(a.pledged_amount)/loan.requested_amount,0) * 100
511 FROM
512 lns_asset_assignments a
513 WHERE
514 a.loan_id = loan.loan_id
515 and ( a.end_date_active is null OR trunc(a.end_date_active) >= trunc(loan.loan_maturity_date) )
516 )
517 );
518 BEGIN
519
520 p_status := 'N';
521 l_count := -1;
522 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
523
524
525 IF (NOT((p_loan_id IS NULL) OR (trim(p_loan_id) = ''))) THEN
526 OPEN Cur_Loan_Value_Met(p_loan_id);
527 FETCH Cur_Loan_Value_Met into l_count;
528 CLOSE Cur_Loan_Value_Met;
529
530 IF l_count > 0 THEN
531 p_status := 'Y';
532 END IF;
533 END IF;
534
535 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
536
537 END IS_LOAN_TO_VALUE_MET;
538
539
540 /*========================================================================
541 | PROCEDURE IS_COLLATERAL_EXIST
542 |
543 | DESCRIPTION
544 | This procedure returns 'Y' if the secured loan contains atleast
545 | one asset.
546 |
547 | PSEUDO CODE/LOGIC
548 |
549 | PARAMETERS
550 | P_LOAN_ID IN Loan Id
551 | P_EXIST OUT Exist status
552 |
553 | KNOWN ISSUES
554 | None
555 |
556 | NOTES
557 |
558 | MODIFICATION HISTORY
559 | Date Author Description of Changes
560 | 29-Mar-2007 Mbolli Created
561 |
562 *=======================================================================*/
563
564 PROCEDURE IS_COLLATERAL_EXIST(P_LOAN_ID IN NUMBER, P_EXIST OUT NOCOPY VARCHAR2)
565 IS
566
567 /*-----------------------------------------------------------------------+
568 | Local Variable Declarations and initializations |
569 +-----------------------------------------------------------------------*/
570 l_api_name CONSTANT VARCHAR2(30) := 'IS_COLLATERAL_EXIST';
571 l_count NUMBER;
572
573 /*-----------------------------------------------------------------------+
574 | Cursor Declarations |
575 +-----------------------------------------------------------------------*/
576
577 CURSOR Cur_Collateral_Exist(p_loan_id IN NUMBER) IS
578 SELECT
579 COUNT(1)
580 FROM
581 lns_loan_headers l
582 WHERE
583 l.loan_Id = p_loan_id
584 and (l.loan_subtype <> 'SECURED'
585 OR exists
586 (SELECT
587 1
588 FROM
589 lns_asset_assignments a
590 WHERE
591 a.loan_id = l.loan_id
592 )
593 );
594 BEGIN
595
596 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
597
598 p_exist := 'N';
599 l_count := -1;
600 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
601
602 IF (NOT ((p_loan_id IS NULL) OR (trim(p_loan_id) = ''))) THEN
603 OPEN Cur_Collateral_Exist(p_loan_id);
604 FETCH Cur_Collateral_Exist into l_count;
605 CLOSE Cur_Collateral_Exist;
606
607 IF l_count > 0 THEN
608 p_exist := 'Y';
609 END IF;
610 END IF;
611
612 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
613
614 END IS_COLLATERAL_EXIST;
615
616 /*========================================================================
617 | PROCEDURE ARE_CONDITIONS_MET
618 |
619 | DESCRIPTION
620 | This procedure returns 'Y' if this loan meets all the conditions
621 |
622 | PSEUDO CODE/LOGIC
623 |
624 | PARAMETERS
625 | P_LOAN_ID IN Loan Id
626 | P_LOAN_STATUS IN Loan Status
627 | P_LOAN_PHASE IN Loan Current Phase
628 | P_OPEN_TO_TERM_EVENT IN Open to Term Event
629 | P_COND_MET OUT Conditions Met
630 |
631 | KNOWN ISSUES
632 | None
633 |
634 | NOTES
635 |
636 | MODIFICATION HISTORY
637 | Date Author Description of Changes
638 | 29-Mar-2007 Mbolli Created
639 |
640 *=======================================================================*/
641
642 PROCEDURE ARE_CONDITIONS_MET(P_LOAN_ID IN NUMBER,
643 P_LOAN_STATUS IN VARCHAR2,
644 P_LOAN_PHASE IN VARCHAR2,
645 P_OPEN_TO_TERM_EVENT IN VARCHAR2,
646 P_COND_MET OUT NOCOPY VARCHAR2
647 )
648 IS
649
650 /*-----------------------------------------------------------------------+
651 | Local Variable Declarations and initializations |
652 +-----------------------------------------------------------------------*/
653 l_api_name CONSTANT VARCHAR2(30) := 'ARE_CONDITIONS_MET';
654 l_condition_type VARCHAR2(30);
655 l_count NUMBER;
656
657 /*-----------------------------------------------------------------------+
658 | Cursor Declarations |
659 +-----------------------------------------------------------------------*/
660
661 BEGIN
662 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
663
664 p_cond_met := 'Y';
665 l_condition_type := '-';
666 l_count := -1;
667
668 IF ( p_loan_phase = 'OPEN' AND p_open_to_term_event = 'MANUAL' AND p_loan_status = 'INCOMPLETE' ) THEN
669 l_condition_type := 'CONVERSION';
670 ELSIF ( p_loan_phase = 'OPEN' AND p_open_to_term_event = 'MANUAL' AND p_loan_status = 'ACTIVE' ) THEN
671 l_condition_type := 'APPROVAL';
672 END IF;
673
674 SELECT
675 count(1) into l_count
676 FROM
677 lns_cond_assignments
678 WHERE
679 loan_id = p_loan_id
680 and mandatory_flag = 'Y'
681 and (condition_met_flag is NULL OR condition_met_flag = 'N')
682 and (end_date_active is null or end_date_active > sysdate)
683 and condition_id NOT IN
684 (SELECT
685 condition_id
686 FROM
687 lns_conditions
688 WHERE
689 condition_type = l_condition_type
690 );
691
692
693 IF l_count > 0 THEN
694 p_cond_met := 'N';
695 END IF;
696
697 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
698 END ARE_CONDITIONS_MET;
699
700 /*========================================================================
701 | PROCEDURE GET_LOAN_APPROVAL_ACCESS
702 |
703 | DESCRIPTION
704 | This procedure checks if the loan agent has access to approve the loan
705 | based on the loan product settings.
706 |
707 | PSEUDO CODE/LOGIC
708 |
709 | PARAMETERS
710 | P_LOAN_ID IN Loan Id
711 | P_ACCESS OUT Access status
712 |
713 | KNOWN ISSUES
714 | None
715 |
716 | NOTES
717 |
718 | MODIFICATION HISTORY
719 | Date Author Description of Changes
720 | 29-Mar-2007 Mbolli Created
721 |
722 *=======================================================================*/
723
724 PROCEDURE GET_LOAN_APPROVAL_ACCESS(P_LOAN_ID IN NUMBER, P_ACCESS OUT NOCOPY VARCHAR2)
725 IS
726
727 /*-----------------------------------------------------------------------+
728 | Local Variable Declarations and initializations |
729 +-----------------------------------------------------------------------*/
730 l_api_name CONSTANT VARCHAR2(30) := 'GET_LOAN_APPROVAL_ACCESS';
731 l_appr_req_flag VARCHAR2(1);
732
733 /*-----------------------------------------------------------------------+
734 | Cursor Declarations |
735 +-----------------------------------------------------------------------*/
736
737 BEGIN
738 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
739
740 SELECT
741 NVL(LOAN_APPR_REQ_FLAG,'Y') into l_appr_req_flag
742 FROM
743 lns_loan_products_all prod, lns_loan_headers_all loan
744 WHERE
745 prod.loan_product_id = loan.product_id
746 and loan.loan_id = p_loan_id;
747
748 IF l_appr_req_flag = 'Y' THEN
749 p_access := 'N';
750 ELSE
751 p_access := 'y';
752 END IF;
753
754 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
755
756
757 END GET_LOAN_APPROVAL_ACCESS;
758
759
760
761
762 /*========================================================================
763 | PROCEDURE IS_LOAN_AMOUNT_VALID
764 |
765 | DESCRIPTION
766 | This procedure returns valid if invoice amount >= loan amount
767 | otherwise returns description of invoice amount
768 |
769 | PSEUDO CODE/LOGIC
770 |
771 | PARAMETERS
772 | P_LOAN_ID IN Loan Id
773 | P_STATUS IN Valid status
774 | KNOWN ISSUES
775 | None
776 |
777 | NOTES
778 |
779 | MODIFICATION HISTORY
780 | Date Author Description of Changes
781 | 29-Mar-2007 Mbolli Created
782 |
783 *=======================================================================*/
784
785 PROCEDURE IS_LOAN_AMOUNT_VALID(P_LOAN_ID IN NUMBER, P_STATUS OUT NOCOPY VARCHAR2)
786 IS
787
788 /*-----------------------------------------------------------------------+
789 | Local Variable Declarations and initializations |
790 +-----------------------------------------------------------------------*/
791 l_api_name CONSTANT VARCHAR2(30) := 'IS_LOAN_AMOUNT_VALID';
792 l_inv_amt NUMBER;
793 l_req_amt NUMBER;
794
795 /*-----------------------------------------------------------------------+
796 | Cursor Declarations |
797 +-----------------------------------------------------------------------*/
798
799 CURSOR Csr_Loan_Amounts(p_loan_id IN NUMBER) IS
800 SELECT
801 nvl(sum(pmt_sch.AMOUNT_DUE_REMAINING),0) invoice_balance_amount, nvl(sum(lines.requested_amount),0) requested_amount
802 FROM
803 ar_payment_schedules pmt_sch, lns_Loan_Lines lines
804 WHERE
805 nvl(lines.installment_number, 1) = pmt_sch.terms_sequence_number
806 and pmt_sch.customer_trx_id = lines.reference_id
807 and lines.loan_Id = p_loan_id
808 and lines.end_date is null
809 and lines.reference_type = 'RECEIVABLE';
810
811 BEGIN
812 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
813 p_status := 'INVALID';
814 IF( NOT((p_loan_id IS NULL) OR (trim(p_loan_id) = ''))) THEN
815 p_status := 'INVALID';
816 OPEN Csr_Loan_Amounts(p_loan_id);
817 FETCH Csr_Loan_Amounts into l_inv_amt, l_req_amt;
818 CLOSE Csr_Loan_Amounts;
819 IF (l_inv_amt >= l_req_amt) THEN
820 p_status := 'VALID';
821 END IF;
822 END IF;
823
824 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
825 END IS_LOAN_AMOUNT_VALID;
826
827 BEGIN
828 G_LOG_ENABLED := 'N';
829 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
830
831 /* getting msg logging info */
832 G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
833 if (G_LOG_ENABLED = 'N') then
834 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
835 else
836 G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
837 end if;
838
839 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
840 END LNS_SUBMIT_FOR_APPROVAL_PUB;