[Home] [Help]
PACKAGE BODY: APPS.LNS_OCM_ADP_PUB
Source
1 PACKAGE BODY LNS_OCM_ADP_PUB AS
2 /*$Header: LNS_ADP_PUBP_B.pls 120.7 2006/02/22 03:13:59 hikumar noship $ */
3
4
5
6 /*===========================================================================+
7 | FUNCTION - loan_request_amount
8 |
9 |
10 | DESCRIPTION - Returns the requested amount for loan.
11 |
12 | SCOPE - PUBLIC
13 |
14 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
15 |
16 | ARGUMENTS : IN:
17 | OUT:
18 | x_resultout
19 | x_errormsg
20 | IN/OUT:
21 |
22 | RETURNS : Loan Requested Amount
23 |
24 | NOTES
25 |
26 | MODIFICATION HISTORY
27 | 01-SEP-2005 Hitesh Kumar Created.
28 +===========================================================================*/
29
30 FUNCTION loan_request_amount(x_resultout OUT NOCOPY VARCHAR2,
31 x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
32
33 l_cr_loan_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_source_column1 ) ;
34 l_cr_currency VARCHAR2(30) := OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_TRX_CURRENCY ;
35
36 l_loan_req_amt lns_loan_headers_all.requested_amount%TYPE ;
37 l_loan_currency lns_loan_headers_all.loan_currency%TYPE ;
38
39 BEGIN
40
41 x_resultout := FND_API.G_RET_STS_SUCCESS;
42 BEGIN
43 SELECT requested_amount , loan_currency
44 INTO l_loan_req_amt , l_loan_currency
45 FROM lns_loan_headers_all
46 WHERE loan_id = l_cr_loan_id ;
47
48
49 IF(l_cr_currency<>l_loan_currency) THEN
50 l_loan_req_amt := gl_currency_api.convert_amount(l_loan_currency ,l_cr_currency , sysdate, null , l_loan_req_amt);
51 END IF ;
52
53 EXCEPTION
54 WHEN NO_DATA_FOUND THEN
55 l_loan_req_amt := 0 ;
56 WHEN OTHERS THEN
57 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
58 x_errormsg := sqlerrm;
59 END;
60
61
62
63
64 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
65 to_char(l_loan_req_amt) ;
66
67 RETURN to_char( NVL(l_loan_req_amt,0) );
68
69
70 END loan_request_amount ;
71
72
73
74
75 /*===========================================================================+
76 | FUNCTION is_secured_loan
77 |
78 |
79 | DESCRIPTION Checks if the given loan is secured loan or not
80 |
81 |
82 | SCOPE - PUBLIC
83 |
84 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
85 |
86 | ARGUMENTS : IN:
87 | OUT:
88 | x_resultout
89 | x_errormsg
90 | IN/OUT:
91 |
92 | RETURNS : Y - if the loan is secured
93 | N - If the loan is not secured
94 |
95 | NOTES
96 |
97 | MODIFICATION HISTORY
98 | 01-SEP-2005 Hitesh Kumar Created.
99 +===========================================================================*/
100
101
102 FUNCTION is_secured_loan (x_resultout OUT NOCOPY VARCHAR2,
103 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
104
105 l_cr_loan_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_source_column1 ) ;
106
107 l_loan_subtype lns_loan_headers_all.loan_subtype%TYPE ;
108 l_loan_secured VARCHAR2(1) ;
109
110
111 BEGIN
112
113 x_resultout := FND_API.G_RET_STS_SUCCESS;
114 BEGIN
115 SELECT loan_subtype
116 INTO l_loan_subtype
117 FROM lns_loan_headers_all
118 WHERE loan_id = l_cr_loan_id ;
119
120 IF(l_loan_subtype = 'SECURED' ) THEN
121 l_loan_secured := 'Y' ;
122 ELSE
123 l_loan_secured := 'N' ;
124 END IF ;
125
126
127 EXCEPTION
128 WHEN NO_DATA_FOUND THEN
129 l_loan_secured := 'N' ;
130 WHEN OTHERS THEN
131 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
132 x_errormsg := sqlerrm;
133 END;
134
135
136 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
137 NVL(l_loan_secured,'N') ;
138
139 RETURN NVL(l_loan_secured,'N');
140
141
142 END is_secured_loan ;
143
144
145 /*===========================================================================+
146 | FUNCTION loan_collateral_percentage
147 |
148 |
149 | DESCRIPTION
150 |
151 |
152 | SCOPE - PUBLIC
153 |
154 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
155 |
156 | ARGUMENTS : IN:
157 | OUT:
158 | x_resultout
159 | x_errormsg
160 | IN/OUT:
161 |
162 | RETURNS : Collateral percentage required for this loan
163 |
164 | NOTES
165 |
166 | MODIFICATION HISTORY
167 | 01-SEP-2005 Hitesh Kumar Created.
168 +===========================================================================*/
169
170 FUNCTION loan_collateral_percentage (x_resultout OUT NOCOPY VARCHAR2,
171 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
172
173 l_cr_loan_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_source_column1 ) ;
174
175 l_loan_collateral_perecent lns_loan_headers_all.collateral_percent%TYPE ;
176
177
178 BEGIN
179
180 x_resultout := FND_API.G_RET_STS_SUCCESS;
181 BEGIN
182 SELECT collateral_percent
183 INTO l_loan_collateral_perecent
184 FROM lns_loan_headers_all
185 WHERE loan_id = l_cr_loan_id ;
186
187
188
189 EXCEPTION
190 WHEN NO_DATA_FOUND THEN
191 l_loan_collateral_perecent := 0 ;
192 WHEN OTHERS THEN
193 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
194 x_errormsg := sqlerrm;
195 END;
196
197
198 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
199 to_char(l_loan_collateral_perecent) ;
200
201 RETURN to_char( NVL(l_loan_collateral_perecent,0) );
202
203
204 END loan_collateral_percentage ;
205
206
207
208 /*===========================================================================+
209 | FUNCTION initial_intrest_rate
210 |
211 |
212 | DESCRIPTION Returns the initial interest rate for this loan.
213 | Thats the interest rate for installement number 1
214 |
215 |
216 | SCOPE - PUBLIC
217 |
218 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
219 |
220 | ARGUMENTS : IN:
221 | OUT:
222 | x_resultout
223 | x_errormsg
224 | IN/OUT:
225 |
226 | RETURNS : Initial Interest Rate
227 |
228 | NOTES
229 |
230 | MODIFICATION HISTORY
231 | 01-SEP-2005 Hitesh Kumar Created.
232 +===========================================================================*/
233
234 FUNCTION initial_intrest_rate (x_resultout OUT NOCOPY VARCHAR2,
235 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
236 l_cr_loan_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_source_column1 ) ;
237
238 l_initial_interest_rate lns_rate_schedules.current_interest_rate%TYPE ;
239
240
241 BEGIN
242
243 x_resultout := FND_API.G_RET_STS_SUCCESS;
244 BEGIN
245 SELECT lrs.current_interest_rate
246 INTO l_initial_interest_rate
247 FROM lns_rate_schedules lrs,
248 lns_loan_headers_all llh ,
249 lns_terms lt
250 WHERE llh.loan_id = l_cr_loan_id
251 AND lt.loan_id = llh.loan_id
252 AND lrs.term_id = lt.term_id
253 AND lrs.begin_installment_number = 1
254 AND llh.CURRENT_PHASE = lrs.PHASE;
255
256
257
258 EXCEPTION
259 WHEN NO_DATA_FOUND THEN
260 l_initial_interest_rate := 0 ;
261 WHEN OTHERS THEN
262 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
263 x_errormsg := sqlerrm;
264 END;
265
266
267 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
268 to_char(l_initial_interest_rate) ;
269
270 RETURN to_char( NVL(l_initial_interest_rate,0) );
271
272
273 END initial_intrest_rate ;
274
275
276
277
278 /*===========================================================================+
279 | FUNCTION number_of_coborrowers
280 |
281 |
282 | DESCRIPTION Number of co-borrowers for the current loan
283 |
284 |
285 | SCOPE - PUBLIC
286 |
287 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
288 |
289 | ARGUMENTS : IN:
290 | OUT:
291 | x_resultout
292 | x_errormsg
293 | IN/OUT:
294 |
295 | RETURNS : Number of Co-Borrowers
296 |
297 | NOTES
298 |
299 | MODIFICATION HISTORY
300 | 01-SEP-2005 Hitesh Kumar Created.
301 +===========================================================================*/
302
303 FUNCTION number_of_coborrowers (x_resultout OUT NOCOPY VARCHAR2,
304 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
305
306 l_cr_loan_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_source_column1 ) ;
307
308 l_number_of_coborrowers NUMBER ;
309
310
311 BEGIN
312
313 x_resultout := FND_API.G_RET_STS_SUCCESS;
314 BEGIN
315 SELECT count(*)
316 INTO l_number_of_coborrowers
317 FROM LNS_PARTICIPANTS lp
318 WHERE lp.loan_id = l_cr_loan_id
319 AND lp.loan_participant_type = 'COBORROWER' ;
320
321
322 EXCEPTION
323 WHEN NO_DATA_FOUND THEN
324 l_number_of_coborrowers := 0;
325 WHEN OTHERS THEN
326 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
327 x_errormsg := sqlerrm;
328 END;
329
330
331 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
332 to_char(l_number_of_coborrowers) ;
333
334 RETURN to_char( NVL(l_number_of_coborrowers,0) );
335
336
337 END number_of_coborrowers ;
338
339
340
341
342 /*===========================================================================+
343 | FUNCTION is_having_coborrowers
344 |
345 |
346 | DESCRIPTION Checks if the current loan has co-borrowers or not
347 |
348 |
349 | SCOPE - PUBLIC
350 |
351 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
352 |
353 | ARGUMENTS : IN:
354 | OUT:
355 | x_resultout
356 | x_errormsg
357 | IN/OUT:
358 |
359 | RETURNS : Y - if loan has co-borrower(s)
360 | N - if loan has no co-borrower
361 |
362 | NOTES
363 |
364 | MODIFICATION HISTORY
365 | 01-SEP-2005 Hitesh Kumar Created.
366 +===========================================================================*/
367
368
369 FUNCTION is_having_coborrowers (x_resultout OUT NOCOPY VARCHAR2,
370 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
371
372 l_cr_loan_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_source_column1 ) ;
373
374 l_is_having_coborrowers VARCHAR2(1) ;
375
376
377 BEGIN
378
379 x_resultout := FND_API.G_RET_STS_SUCCESS;
380 BEGIN
381 SELECT NVL ( (SELECT 'Y'
382 FROM DUAL
383 WHERE EXISTS ( SELECT null
384 FROM LNS_PARTICIPANTS lp
385 WHERE lp.loan_id = l_cr_loan_id
386 AND lp.loan_participant_type = 'COBORROWER'))
387 ,'N' ) INTO l_is_having_coborrowers
388 FROM DUAL ;
389
390
391 EXCEPTION
392 WHEN NO_DATA_FOUND THEN
393 l_is_having_coborrowers := 'N' ;
394 WHEN OTHERS THEN
395 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
396 x_errormsg := sqlerrm;
397 END;
398
399
400 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
401 l_is_having_coborrowers ;
402
403 RETURN l_is_having_coborrowers ;
404
405
406
407
408 END is_having_coborrowers ;
409
410
411
412 /*===========================================================================+
413 | FUNCTION number_of_guarantors
414 |
415 |
416 | DESCRIPTION Returns the number of guarantors for current loan
417 |
418 |
419 | SCOPE - PUBLIC
420 |
421 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
422 |
423 | ARGUMENTS : IN:
424 | OUT:
425 | x_resultout
426 | x_errormsg
427 | IN/OUT:
428 |
429 | RETURNS : Number of Guarantors
430 |
431 | NOTES
432 |
433 | MODIFICATION HISTORY
434 | 01-SEP-2005 Hitesh Kumar Created.
435 +===========================================================================*/
436
437 FUNCTION number_of_guarantors (x_resultout OUT NOCOPY VARCHAR2,
438 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
439
440 l_cr_loan_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_source_column1 ) ;
441
442 l_number_of_guarantors NUMBER ;
443
444
445 BEGIN
446
447 x_resultout := FND_API.G_RET_STS_SUCCESS;
448 BEGIN
449 SELECT count(*)
450 INTO l_number_of_guarantors
451 FROM LNS_PARTICIPANTS lp
452 WHERE lp.loan_id = l_cr_loan_id
453 AND lp.loan_participant_type = 'GUARANTOR' ;
454
455
456 EXCEPTION
457 WHEN NO_DATA_FOUND THEN
458 l_number_of_guarantors := 0;
459 WHEN OTHERS THEN
460 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
461 x_errormsg := sqlerrm;
462 END;
463
464
465 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
466 to_char(l_number_of_guarantors) ;
467
468 RETURN to_char( NVL(l_number_of_guarantors,0) );
469
470
471 END number_of_guarantors ;
472
473
474
475 /*===========================================================================+
476 | FUNCTION is_having_guarantors
477 |
478 |
479 | DESCRIPTION Checks if the loan has any guarantors or not
480 |
481 |
482 | SCOPE - PUBLIC
483 |
484 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
485 |
486 | ARGUMENTS : IN:
487 | OUT:
488 | x_resultout
489 | x_errormsg
490 | IN/OUT:
491 |
492 | RETURNS : Y - if the loan has guarantor(s)
493 | N - if the loan has no guarantor
494 |
495 | NOTES
496 |
497 | MODIFICATION HISTORY
498 | 01-SEP-2005 Hitesh Kumar Created.
499 +===========================================================================*/
500
501
502 FUNCTION is_having_guarantors (x_resultout OUT NOCOPY VARCHAR2,
503 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
504
505 l_cr_loan_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_source_column1 ) ;
506
507 l_is_having_guarantors VARCHAR2(1) ;
508
509
510 BEGIN
511
512 x_resultout := FND_API.G_RET_STS_SUCCESS;
513 BEGIN
514 SELECT NVL ( (SELECT 'Y'
515 FROM DUAL
516 WHERE EXISTS ( SELECT null
517 FROM LNS_PARTICIPANTS lp
518 WHERE lp.loan_id = l_cr_loan_id
519 AND lp.loan_participant_type = 'GUARANTOR'))
520 ,'N' ) INTO l_is_having_guarantors
521 FROM DUAL ;
522
523
524 EXCEPTION
525 WHEN NO_DATA_FOUND THEN
526 l_is_having_guarantors := 'N' ;
527 WHEN OTHERS THEN
528 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
529 x_errormsg := sqlerrm;
530 END;
531
532
533 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
534 l_is_having_guarantors ;
535
536 RETURN l_is_having_guarantors ;
537
538
539 END is_having_guarantors ;
540
541
542
543 /*===========================================================================+
544 | FUNCTION required_collateral_amount
545 |
546 |
547 | DESCRIPTION Collateral amount required for this loan , calculated based
548 | on loan-to-value ratio.
549 |
550 |
551 | SCOPE - PUBLIC
552 |
553 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
554 |
555 | ARGUMENTS : IN:
556 | OUT:
557 | x_resultout
558 | x_errormsg
559 | IN/OUT:
560 |
561 | RETURNS : Required Collateral Amount
562 |
563 | NOTES
564 |
565 | MODIFICATION HISTORY
566 | 01-SEP-2005 Hitesh Kumar Created.
567 +===========================================================================*/
568
569 FUNCTION required_collateral_amount (x_resultout OUT NOCOPY VARCHAR2,
570 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
571
572 l_cr_loan_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_source_column1 ) ;
573 l_cr_currency VARCHAR2(30) := OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_TRX_CURRENCY ;
574
575 l_req_coll_amount NUMBER ;
576 l_loan_currency lns_loan_headers_all.loan_currency%TYPE ;
577
578
579 BEGIN
580
581 x_resultout := FND_API.G_RET_STS_SUCCESS;
582 BEGIN
583
584 SELECT ( loan.requested_amount * nvl(loan.collateral_percent,0) / 100 ) ,
585 loan_currency
586 INTO l_req_coll_amount , l_loan_currency
587 FROM lns_loan_headers_all loan
588 WHERE loan.loan_id = l_cr_loan_id ;
589
590
591 IF(l_cr_currency<>l_loan_currency) THEN
592 l_req_coll_amount := gl_currency_api.convert_amount(l_loan_currency ,l_cr_currency , sysdate, null , l_req_coll_amount);
593 END IF ;
594
595 EXCEPTION
596 WHEN NO_DATA_FOUND THEN
597 l_req_coll_amount := 0 ;
598 WHEN OTHERS THEN
599 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
600 x_errormsg := sqlerrm;
601 END;
602
603
604 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
605 to_char(nvl(l_req_coll_amount,0)) ;
606
607 RETURN to_char( NVL(l_req_coll_amount,0) ) ;
608
609
610 END required_collateral_amount ;
611
612
613
614 /*===========================================================================+
615 | FUNCTION total_collateral_amount
616 |
617 |
618 | DESCRIPTION Total collateral amount pledged against this loan.
619 |
620 | SCOPE - PUBLIC
621 |
622 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
623 |
624 | ARGUMENTS : IN:
625 | OUT:
626 | x_resultout
627 | x_errormsg
628 | IN/OUT:
629 |
630 | RETURNS : Total Collateral Amount Pledged
631 |
632 | NOTES
633 |
634 | MODIFICATION HISTORY
635 | 01-SEP-2005 Hitesh Kumar Created.
636 +===========================================================================*/
637
638 FUNCTION total_collateral_amount (x_resultout OUT NOCOPY VARCHAR2,
639 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
640
641 l_cr_loan_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_source_column1 ) ;
642 l_cr_currency VARCHAR2(30) := OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_TRX_CURRENCY ;
643
644 l_total_coll_amount NUMBER ;
645 l_loan_currency lns_loan_headers_all.loan_currency%TYPE ;
646
647
648 BEGIN
649
650 x_resultout := FND_API.G_RET_STS_SUCCESS;
651 BEGIN
652
653 SELECT (select nvl(sum(laa.pledged_amount),0)
654 from lns_asset_assignments laa
655 where laa.loan_id = loan.loan_id
656 and (laa.end_date_active is null or trunc(laa.end_date_active) > trunc(sysdate))
657 and exists (select 1 from lns_assets la where la.asset_id = laa.asset_id)) , loan.loan_currency
658 INTO l_total_coll_amount , l_loan_currency
659 FROM lns_loan_headers_all loan
660 WHERE loan.loan_id = l_cr_loan_id ;
661
662 IF(l_cr_currency<>l_loan_currency) THEN
663 l_total_coll_amount := gl_currency_api.convert_amount(l_loan_currency ,l_cr_currency , sysdate, null , l_total_coll_amount);
664 END IF ;
665
666 EXCEPTION
667 WHEN NO_DATA_FOUND THEN
668 l_total_coll_amount := 0 ;
669 WHEN OTHERS THEN
670 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
671 x_errormsg := sqlerrm;
672 END;
673
674
675 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
676 to_char(nvl(l_total_coll_amount,0) ) ;
677
678 RETURN to_char( NVL(l_total_coll_amount,0) ) ;
679
680
681 END total_collateral_amount ;
682
683
684
685 /*===========================================================================+
686 | FUNCTION deliquency_cond_amount
687 |
688 |
689 | DESCRIPTION Deliquency condition amount for this loan.
690 |
691 |
692 | SCOPE - PUBLIC
693 |
694 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
695 |
696 | ARGUMENTS : IN:
697 | OUT:
698 | x_resultout
699 | x_errormsg
700 | IN/OUT:
701 |
702 | RETURNS : Deliquency Condition Amount
703 |
704 | NOTES
705 |
706 | MODIFICATION HISTORY
707 | 01-SEP-2005 Hitesh Kumar Created.
708 +===========================================================================*/
709
710 FUNCTION deliquency_cond_amount (x_resultout OUT NOCOPY VARCHAR2,
711 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
712
713 l_cr_loan_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_source_column1 ) ;
714 l_cr_currency VARCHAR2(30) := OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_TRX_CURRENCY ;
715
716
717 l_deliquency_amount NUMBER ;
718 l_loan_currency lns_loan_headers_all.loan_currency%TYPE ;
719
720
721 BEGIN
722
723 x_resultout := FND_API.G_RET_STS_SUCCESS;
724 BEGIN
725 SELECT lt.delinquency_threshold_amount , llh.loan_currency
726 INTO l_deliquency_amount , l_loan_currency
727 FROM lns_terms lt , lns_loan_headers_all llh
728 WHERE llh.loan_id = l_cr_loan_id
729 AND lt.loan_id = llh.loan_id ;
730
731
732 IF(l_cr_currency<>l_loan_currency) THEN
733 l_deliquency_amount := gl_currency_api.convert_amount(l_loan_currency ,l_cr_currency , sysdate, null , l_deliquency_amount);
734 END IF ;
735
736 EXCEPTION
737 WHEN NO_DATA_FOUND THEN
738 l_deliquency_amount := 0 ;
739 WHEN OTHERS THEN
740 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
741 x_errormsg := sqlerrm;
742 END;
743
744
745 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
746 to_char( nvl(l_deliquency_amount,0) ) ;
747
748 RETURN to_char( NVL(l_deliquency_amount,0) ) ;
749
750
751 END deliquency_cond_amount ;
752
753
754
755
756 /*===========================================================================+
757 | FUNCTION total_assets_valuation_amt
758 |
759 |
760 | DESCRIPTION Total assets valuation amount for the loan party.
761 | If the credit request party is Primary Borrower , sum all the
762 | the assets of Primary Borrower and all Co-Borrower(s) of the loan.
763 | Else if the credit request party is Guarantor , sum the assets
764 | of that party only.
765 |
766 | Convert the assets value amount to credit request currency.
767 |
768 | SCOPE - PUBLIC
769 |
770 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
771 |
772 | ARGUMENTS : IN:
773 | OUT:
774 | x_resultout
775 | x_errormsg
776 | IN/OUT:
777 |
778 | RETURNS :
779 |
780 | NOTES
781 |
782 | MODIFICATION HISTORY
783 | 01-SEP-2005 Hitesh Kumar Created.
784 +===========================================================================*/
785
786 FUNCTION total_assets_valuation_amt (x_resultout OUT NOCOPY VARCHAR2,
787 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
788
789 CURSOR party_total_assets(C_LOAN_ID NUMBER , C_PARTY_ID NUMBER) IS
790 SELECT sum(VALUATION), CURRENCY_CODE
791 FROM LNS_ASSETS
792 WHERE asset_owner_id IN (
793 SELECT HZ_PARTY_ID FROM LNS_PARTICIPANTS
794 WHERE LOAN_ID = ( select loan_id from lns_participants
795 where loan_id = C_LOAN_ID and hz_party_id = C_PARTY_ID
796 and loan_participant_type = 'PRIMARY_BORROWER' )
797 AND ( LOAN_PARTICIPANT_TYPE = 'PRIMARY_BORROWER' OR LOAN_PARTICIPANT_TYPE ='COBORROWER' )
798
799 UNION ALL
800
801 SELECT HZ_PARTY_ID FROM LNS_PARTICIPANTS
802 WHERE LOAN_ID = C_LOAN_ID
803 AND HZ_PARTY_ID = C_PARTY_ID
804 AND LOAN_PARTICIPANT_TYPE = 'GUARANTOR'
805 )
806 and (end_date_active is null
807 or trunc(end_date_active) > trunc(sysdate))
808 group by CURRENCY_CODE ;
809
810
811 l_cr_loan_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_source_column1 ) ;
812 l_cr_party_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_party_id ) ;
813 l_cr_currency VARCHAR2(30) := OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_TRX_CURRENCY ;
814
815 l_asset_amount NUMBER ;
816 l_asset_currency lns_assets.currency_code%TYPE ;
817 l_total_assets_amount NUMBER ;
818
819
820
821
822 BEGIN
823
824 x_resultout := FND_API.G_RET_STS_SUCCESS;
825 BEGIN
826
827 l_total_assets_amount := 0 ;
828 OPEN party_total_assets(l_cr_loan_id , l_cr_party_id) ;
829
830 LOOP
831
832 FETCH party_total_assets INTO l_asset_amount , l_asset_currency ;
833 EXIT WHEN party_total_assets%NOTFOUND ;
834
835 IF(l_cr_currency<>l_asset_currency) THEN
836 l_asset_amount := gl_currency_api.convert_amount(l_asset_currency ,l_cr_currency , sysdate, null , l_asset_amount);
837 END IF ;
838
839 l_total_assets_amount := l_total_assets_amount + l_asset_amount ;
840
841 END LOOP ;
842
843
844
845 EXCEPTION
846 WHEN NO_DATA_FOUND THEN
847 l_total_assets_amount := 0 ;
848 WHEN OTHERS THEN
849 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
850 x_errormsg := sqlerrm;
851 END;
852
853
854 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
855 to_char( NVL(l_total_assets_amount,0) ) ;
856
857 RETURN to_char( NVL(l_total_assets_amount,0) ) ;
858
859
860 END total_assets_valuation_amt ;
861
862
863
864 /*===========================================================================+
865 | FUNCTION total_assets_pledged_amt
866 |
867 |
868 | DESCRIPTION Total un-pledged amount of the assets of the party.
869 | If the credit request party is loan Primary Borrower , sum
870 | the unpledged amount of all assets of Primary Borrower and
871 | Co-Borrower of this loan.
872 | Else if the credint request party is a Guarantor , then sum
873 | the unpledged amout of all assets of Guarantor only.
874 |
875 | SCOPE - PUBLIC
876 |
877 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
878 |
879 | ARGUMENTS : IN:
880 | OUT:
881 | x_resultout
882 | x_errormsg
883 | IN/OUT:
884 |
885 | RETURNS : Total Pledged Amount across all Loans of all assets.
886 |
887 | NOTES
888 |
889 | MODIFICATION HISTORY
890 | 01-SEP-2005 Hitesh Kumar Created.
891 +===========================================================================*/
892
893 FUNCTION total_assets_pledged_amt (x_resultout OUT NOCOPY VARCHAR2,
894 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
895
896 CURSOR party_pledged_assets(C_LOAN_ID NUMBER , C_PARTY_ID NUMBER) IS
897 SELECT sum(nvl( (select sum (assign.pledged_amount) from lns_asset_assignments assign where LnsAssets.asset_id = assign.asset_id(+) and (assign.end_date_active is null or trunc(assign.end_date_active) > trunc(sysdate)) )
898 ,0) ), CURRENCY_CODE
899 FROM LNS_ASSETS LnsAssets
900 WHERE asset_owner_id IN (
901 SELECT HZ_PARTY_ID FROM LNS_PARTICIPANTS
902 WHERE LOAN_ID = ( select loan_id from lns_participants
903 where loan_id = C_LOAN_ID and hz_party_id = C_PARTY_ID
904 and loan_participant_type = 'PRIMARY_BORROWER' )
905 AND ( LOAN_PARTICIPANT_TYPE = 'PRIMARY_BORROWER' OR LOAN_PARTICIPANT_TYPE ='COBORROWER' )
906
907 UNION ALL
908
909 SELECT HZ_PARTY_ID FROM LNS_PARTICIPANTS
910 WHERE LOAN_ID = C_LOAN_ID
911 AND HZ_PARTY_ID = C_PARTY_ID
912 AND LOAN_PARTICIPANT_TYPE = 'GUARANTOR'
913 )
914 and (end_date_active is null
915 or trunc(end_date_active) > trunc(sysdate))
916 group by CURRENCY_CODE ;
917
918
919 l_cr_loan_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_source_column1 ) ;
920 l_cr_party_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_party_id ) ;
921 l_cr_currency VARCHAR2(30) := OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_TRX_CURRENCY ;
922
923 l_asset_pledged_amt NUMBER ;
924 l_asset_currency lns_assets.currency_code%TYPE ;
925 l_total_assets_pledged_amt NUMBER ;
926
927
928
929
930 BEGIN
931
932 x_resultout := FND_API.G_RET_STS_SUCCESS;
933 BEGIN
934
935 l_total_assets_pledged_amt := 0 ;
936 OPEN party_pledged_assets(l_cr_loan_id , l_cr_party_id) ;
937
938 LOOP
939
940 FETCH party_pledged_assets INTO l_asset_pledged_amt , l_asset_currency ;
941 EXIT WHEN party_pledged_assets%NOTFOUND ;
942
943 IF(l_cr_currency<>l_asset_currency) THEN
944 l_asset_pledged_amt := gl_currency_api.convert_amount(l_asset_currency ,l_cr_currency , sysdate, null , l_asset_pledged_amt);
945 END IF ;
946
947 l_total_assets_pledged_amt := l_total_assets_pledged_amt + l_asset_pledged_amt ;
948
949 END LOOP ;
950
951
952
953 EXCEPTION
954 WHEN NO_DATA_FOUND THEN
955 l_total_assets_pledged_amt := 0 ;
956 WHEN OTHERS THEN
957 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
958 x_errormsg := sqlerrm;
959 END;
960
961
962 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
963 to_char( NVL(l_total_assets_pledged_amt,0) ) ;
964
965 RETURN to_char( NVL(l_total_assets_pledged_amt,0) ) ;
966
967
968 END total_assets_pledged_amt ;
969
970
971
972 /*===========================================================================+
973 | FUNCTION total_assets_available_amt
974 |
975 |
976 | DESCRIPTION Total assets available amount for the party.
977 | If the credit request party is Loan's Primary Borrower , sum the
978 | available amount for all assets of Primary Borrower and all Co-Borrower(s)
979 | of the loan.
980 | Else if the credit request party is Loan's Guarantor , sum the
981 | available amount for all assets of the guarantor.
982 |
983 | SCOPE - PUBLIC
984 |
985 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
986 |
987 | ARGUMENTS : IN:
988 | OUT:
989 | x_resultout
990 | x_errormsg
991 | IN/OUT:
992 |
993 | RETURNS : Total Available amount of assets to be pledged.
994 |
995 | NOTES
996 |
997 | MODIFICATION HISTORY
998 | 01-SEP-2005 Hitesh Kumar Created.
999 +===========================================================================*/
1000
1001 FUNCTION total_assets_available_amt (x_resultout OUT NOCOPY VARCHAR2,
1002 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
1003
1004 CURSOR party_available_assets(C_LOAN_ID NUMBER , C_PARTY_ID NUMBER) IS
1005 SELECT sum( nvl(LnsAssets.valuation,0) - nvl( (select sum (assign.pledged_amount) from lns_asset_assignments
1006 assign where LnsAssets.asset_id = assign.asset_id(+) and (assign.end_date_active is null or trunc(assign.end_date_active) > trunc(sysdate)) )
1007 ,0) ), CURRENCY_CODE
1008 FROM LNS_ASSETS LnsAssets
1009 WHERE asset_owner_id IN (
1010 SELECT HZ_PARTY_ID FROM LNS_PARTICIPANTS
1011 WHERE LOAN_ID = ( select loan_id from lns_participants where loan_id = C_LOAN_ID and hz_party_id = C_PARTY_ID and loan_participant_type = 'PRIMARY_BORROWER' )
1012 AND ( LOAN_PARTICIPANT_TYPE = 'PRIMARY_BORROWER' OR LOAN_PARTICIPANT_TYPE ='COBORROWER' )
1013
1014 UNION ALL
1015
1016 SELECT HZ_PARTY_ID FROM LNS_PARTICIPANTS
1017 WHERE LOAN_ID = C_LOAN_ID
1018 AND HZ_PARTY_ID = C_PARTY_ID
1019 AND LOAN_PARTICIPANT_TYPE = 'GUARANTOR'
1020 )
1021 or trunc(end_date_active) > trunc(sysdate)
1022 group by CURRENCY_CODE ;
1023
1024
1025
1026 l_cr_loan_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_source_column1 ) ;
1027 l_cr_party_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_party_id ) ;
1028 l_cr_currency VARCHAR2(30) := OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_TRX_CURRENCY ;
1029
1030 l_asset_available_amt NUMBER ;
1031 l_asset_currency lns_assets.currency_code%TYPE ;
1032 l_total_assets_available_amt NUMBER ;
1033
1034
1035
1036
1037 BEGIN
1038
1039 x_resultout := FND_API.G_RET_STS_SUCCESS;
1040 BEGIN
1041
1042 l_total_assets_available_amt := 0 ;
1043 OPEN party_available_assets(l_cr_loan_id , l_cr_party_id) ;
1044
1045 LOOP
1046
1047 FETCH party_available_assets INTO l_asset_available_amt , l_asset_currency ;
1048 EXIT WHEN party_available_assets%NOTFOUND ;
1049
1050 IF(l_cr_currency<>l_asset_currency) THEN
1051 l_asset_available_amt := gl_currency_api.convert_amount(l_asset_currency ,l_cr_currency , sysdate, null , l_asset_available_amt);
1052 END IF ;
1053
1054 l_total_assets_available_amt := l_total_assets_available_amt + l_asset_available_amt ;
1055
1056 END LOOP ;
1057
1058
1059
1060 EXCEPTION
1061 WHEN NO_DATA_FOUND THEN
1062 l_total_assets_available_amt := 0 ;
1063 WHEN OTHERS THEN
1064 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1065 x_errormsg := sqlerrm;
1066 END;
1067
1068
1069 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
1070 to_char( NVL(l_total_assets_available_amt,0) ) ;
1071
1072 RETURN to_char( NVL(l_total_assets_available_amt,0) ) ;
1073
1074
1075 END total_assets_available_amt ;
1076
1077
1078
1079
1080 /*===========================================================================+
1081 | FUNCTION number_active_loans
1082 |
1083 |
1084 | DESCRIPTION Number of active loans where the credit request party is
1085 | Primary Borrower or Co-Borrower.
1086 |
1087 | SCOPE - PUBLIC
1088 |
1089 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1090 |
1091 | ARGUMENTS : IN:
1092 | OUT:
1093 | x_resultout
1094 | x_errormsg
1095 | IN/OUT:
1096 |
1097 | RETURNS : Number of Active Loans for this Party.
1098 |
1099 | NOTES
1100 |
1101 | MODIFICATION HISTORY
1102 | 01-SEP-2005 Hitesh Kumar Created.
1103 +===========================================================================*/
1104
1105 FUNCTION number_active_loans ( x_resultout OUT NOCOPY VARCHAR2,
1106 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
1107
1108
1109 l_cr_party_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_party_id ) ;
1110
1111 l_count_active_loans NUMBER ;
1112
1113
1114 BEGIN
1115
1116 x_resultout := FND_API.G_RET_STS_SUCCESS;
1117 BEGIN
1118
1119 SELECT count(*)
1120 INTO l_count_active_loans
1121 FROM lns_loan_headers_all
1122 WHERE loan_id IN ( SELECT loan_id
1123 FROM lns_participants
1124 WHERE hz_party_id = l_cr_party_id
1125 AND ( loan_participant_type = 'PRIMARY_BORROWER'
1126 OR loan_participant_type = 'COBORROWER')
1127 )
1128 AND loan_status = 'ACTIVE' ;
1129
1130
1131 EXCEPTION
1132 WHEN NO_DATA_FOUND THEN
1133 l_count_active_loans := 0 ;
1134 WHEN OTHERS THEN
1135 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1136 x_errormsg := sqlerrm;
1137 END;
1138
1139
1140 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
1141 to_char( NVL(l_count_active_loans,0) ) ;
1142
1143 RETURN to_char( NVL(l_count_active_loans,0) ) ;
1144
1145
1146 END number_active_loans ;
1147
1148
1149
1150
1151 /*===========================================================================+
1152 | FUNCTION balance_amt_active_loans
1153 |
1154 |
1155 | DESCRIPTION Total outstanding balance of all active loans where the
1156 | credit request party is Primary Borrower or Co-Borrower.
1157 |
1158 | SCOPE - PUBLIC
1159 |
1160 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1161 |
1162 | ARGUMENTS : IN:
1163 | OUT:
1164 | x_resultout
1165 | x_errormsg
1166 | IN/OUT:
1167 |
1168 | RETURNS : Outstanding Balance across all Active Loans.
1169 |
1170 | NOTES
1171 |
1172 | MODIFICATION HISTORY
1173 | 01-SEP-2005 Hitesh Kumar Created.
1174 +===========================================================================*/
1175
1176 FUNCTION balance_amt_active_loans ( x_resultout OUT NOCOPY VARCHAR2,
1177 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
1178
1179 CURSOR active_loans_balance(C_PARTY_ID NUMBER) IS
1180 SELECT sum(lps.total_principal_balance) , llh.loan_currency
1181 FROM lns_loan_headers_all llh ,
1182 LNS_PAY_SUM_V lps
1183 WHERE llh.loan_id = lps.loan_id
1184 AND llh.loan_id IN ( SELECT loan_id
1185 FROM lns_participants
1186 WHERE hz_party_id = c_party_id
1187 AND ( loan_participant_type = 'PRIMARY_BORROWER'
1188 OR loan_participant_type = 'COBORROWER')
1189 )
1190 AND llh.loan_status = 'ACTIVE'
1191 GROUP BY loan_currency ;
1192
1193
1194 l_cr_party_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_party_id ) ;
1195 l_cr_currency VARCHAR2(30) := OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_TRX_CURRENCY ;
1196
1197 l_loan_balance_amt NUMBER ;
1198 l_loan_currency lns_loan_headers_all.loan_currency%TYPE ;
1199 l_total_balance_amt NUMBER ;
1200
1201
1202 BEGIN
1203
1204 x_resultout := FND_API.G_RET_STS_SUCCESS;
1205 BEGIN
1206
1207 l_total_balance_amt := 0 ;
1208 OPEN active_loans_balance(l_cr_party_id) ;
1209
1210 LOOP
1211
1212 FETCH active_loans_balance INTO l_loan_balance_amt , l_loan_currency ;
1213 EXIT WHEN active_loans_balance%NOTFOUND ;
1214
1215 IF(l_cr_currency<>l_loan_currency) THEN
1216 l_loan_balance_amt := gl_currency_api.convert_amount(l_loan_currency ,l_cr_currency , sysdate, null , l_loan_balance_amt);
1217 END IF ;
1218
1219 l_total_balance_amt := l_total_balance_amt + l_loan_balance_amt ;
1220
1221 END LOOP ;
1222
1223
1224
1225 EXCEPTION
1226 WHEN NO_DATA_FOUND THEN
1227 l_total_balance_amt := 0 ;
1228 WHEN OTHERS THEN
1229 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1230 x_errormsg := sqlerrm;
1231 END;
1232
1233
1234 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
1235 to_char( NVL(l_total_balance_amt,0) ) ;
1236
1237 RETURN to_char( NVL(l_total_balance_amt,0) ) ;
1238
1239
1240 END balance_amt_active_loans ;
1241
1242
1243
1244
1245 /*===========================================================================+
1246 | FUNCTION number_pending_loans
1247 |
1248 |
1249 | DESCRIPTION Number of loans pending approval where the credit request party is
1250 | Primary Borrower or Co-Borrower.
1251 |
1252 | SCOPE - PUBLIC
1253 |
1254 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1255 |
1256 | ARGUMENTS : IN:
1257 | OUT:
1258 | x_resultout
1259 | x_errormsg
1260 | IN/OUT:
1261 |
1262 | RETURNS : Number of Loans Pending Approval for this Party.
1263 |
1264 | NOTES
1265 |
1266 | MODIFICATION HISTORY
1267 | 01-SEP-2005 Hitesh Kumar Created.
1268 +===========================================================================*/
1269
1270 FUNCTION number_pending_loans ( x_resultout OUT NOCOPY VARCHAR2,
1271 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
1272
1273
1274 l_cr_party_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_party_id ) ;
1275
1276 l_count_pending_loans NUMBER ;
1277
1278
1279 BEGIN
1280
1281 x_resultout := FND_API.G_RET_STS_SUCCESS;
1282 BEGIN
1283
1284 SELECT count(*)
1285 INTO l_count_pending_loans
1286 FROM lns_loan_headers_all
1287 WHERE loan_id IN ( SELECT loan_id
1288 FROM lns_participants
1289 WHERE hz_party_id = l_cr_party_id
1290 AND ( loan_participant_type = 'PRIMARY_BORROWER'
1291 OR loan_participant_type = 'COBORROWER')
1292 )
1293 AND loan_status = 'PENDING' ;
1294
1295
1296 EXCEPTION
1297 WHEN NO_DATA_FOUND THEN
1298 l_count_pending_loans := 0 ;
1299 WHEN OTHERS THEN
1300 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1301 x_errormsg := sqlerrm;
1302 END;
1303
1304
1305 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
1306 to_char( NVL(l_count_pending_loans,0) ) ;
1307
1308 RETURN to_char( NVL(l_count_pending_loans,0) ) ;
1309
1310
1311 END number_pending_loans ;
1312
1313
1314
1315 /*===========================================================================+
1316 | FUNCTION balance_amt_pending_loans
1317 |
1318 |
1319 | DESCRIPTION Total outstanding balance of all loans pending approval where the
1320 | credit request party is Primary Borrower or Co-Borrower.
1321 |
1322 | SCOPE - PUBLIC
1323 |
1324 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1325 |
1326 | ARGUMENTS : IN:
1327 | OUT:
1328 | x_resultout
1329 | x_errormsg
1330 | IN/OUT:
1331 |
1332 | RETURNS : Outstanding Balance across all Loans Pending Approval.
1333 |
1334 | NOTES
1335 |
1336 | MODIFICATION HISTORY
1337 | 01-SEP-2005 Hitesh Kumar Created.
1338 +===========================================================================*/
1339
1340 FUNCTION balance_amt_pending_loans ( x_resultout OUT NOCOPY VARCHAR2,
1341 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
1342
1343
1344 CURSOR pending_loans_balance(C_PARTY_ID NUMBER) IS
1345 SELECT sum(lps.total_principal_balance) , llh.loan_currency
1346 FROM lns_loan_headers_all llh ,
1347 LNS_PAY_SUM_V lps
1348 WHERE llh.loan_id = lps.loan_id
1349 AND llh.loan_id IN ( SELECT loan_id
1350 FROM lns_participants
1351 WHERE hz_party_id = c_party_id
1352 AND ( loan_participant_type = 'PRIMARY_BORROWER'
1353 OR loan_participant_type = 'COBORROWER')
1354 )
1355 AND llh.loan_status = 'PENDING'
1356 GROUP BY loan_currency ;
1357
1358
1359 l_cr_party_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_party_id ) ;
1360 l_cr_currency VARCHAR2(30) := OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_TRX_CURRENCY ;
1361
1362 l_loan_balance_amt NUMBER ;
1363 l_loan_currency lns_loan_headers_all.loan_currency%TYPE ;
1364 l_total_balance_amt NUMBER ;
1365
1366
1367 BEGIN
1368
1369 x_resultout := FND_API.G_RET_STS_SUCCESS;
1370 BEGIN
1371
1372 l_total_balance_amt := 0 ;
1373 OPEN pending_loans_balance(l_cr_party_id) ;
1374
1375 LOOP
1376
1377 FETCH pending_loans_balance INTO l_loan_balance_amt , l_loan_currency ;
1378 EXIT WHEN pending_loans_balance%NOTFOUND ;
1379
1380 IF(l_cr_currency<>l_loan_currency) THEN
1381 l_loan_balance_amt := gl_currency_api.convert_amount(l_loan_currency ,l_cr_currency , sysdate, null , l_loan_balance_amt);
1382 END IF ;
1383
1384 l_total_balance_amt := l_total_balance_amt + l_loan_balance_amt ;
1385
1386 END LOOP ;
1387
1388
1389
1390 EXCEPTION
1391 WHEN NO_DATA_FOUND THEN
1392 l_total_balance_amt := 0 ;
1393 WHEN OTHERS THEN
1394 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1395 x_errormsg := sqlerrm;
1396 END;
1397
1398
1399 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
1400 to_char( NVL(l_total_balance_amt,0) ) ;
1401
1402 RETURN to_char( NVL(l_total_balance_amt,0) ) ;
1403
1404 END balance_amt_pending_loans ;
1405
1406
1407
1408
1409 /*===========================================================================+
1410 | FUNCTION number_delinquent_loans
1411 |
1412 |
1413 | DESCRIPTION Number of delinquent loans where the credit request party is
1414 | Primary Borrower or Co-Borrower.
1415 |
1416 | SCOPE - PUBLIC
1417 |
1418 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1419 |
1420 | ARGUMENTS : IN:
1421 | OUT:
1422 | x_resultout
1423 | x_errormsg
1424 | IN/OUT:
1425 |
1426 | RETURNS : Number of Delinquent Loans for this Party.
1427 |
1428 | NOTES
1429 |
1430 | MODIFICATION HISTORY
1431 | 01-SEP-2005 Hitesh Kumar Created.
1432 +===========================================================================*/
1433
1434 FUNCTION number_delinquent_loans ( x_resultout OUT NOCOPY VARCHAR2,
1435 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
1436
1437
1438 l_cr_party_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_party_id ) ;
1439
1440 l_count_delinquent_loans NUMBER ;
1441
1442
1443 BEGIN
1444
1445 x_resultout := FND_API.G_RET_STS_SUCCESS;
1446 BEGIN
1447
1448 SELECT count(*)
1449 INTO l_count_delinquent_loans
1450 FROM lns_loan_headers_all
1451 WHERE loan_id IN ( SELECT loan_id
1452 FROM lns_participants
1453 WHERE hz_party_id = l_cr_party_id
1454 AND ( loan_participant_type = 'PRIMARY_BORROWER'
1455 OR loan_participant_type = 'COBORROWER')
1456 )
1457 AND loan_status = 'DELINQUENT' ;
1458
1459
1460 EXCEPTION
1461 WHEN NO_DATA_FOUND THEN
1462 l_count_delinquent_loans := 0 ;
1463 WHEN OTHERS THEN
1464 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1465 x_errormsg := sqlerrm;
1466 END;
1467
1468
1469 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
1470 to_char( NVL(l_count_delinquent_loans,0) ) ;
1471
1472 RETURN to_char( NVL(l_count_delinquent_loans,0) ) ;
1473
1474
1475 END number_delinquent_loans ;
1476
1477
1478
1479
1480 /*===========================================================================+
1481 | FUNCTION balance_amt_delinquent_loans
1482 |
1483 |
1484 | DESCRIPTION Total outstanding balance of all delinquent loans where the
1485 | credit request party is Primary Borrower or Co-Borrower.
1486 |
1487 | SCOPE - PUBLIC
1488 |
1489 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1490 |
1491 | ARGUMENTS : IN:
1492 | OUT:
1493 | x_resultout
1494 | x_errormsg
1495 | IN/OUT:
1496 |
1497 | RETURNS : Outstanding Balance across all Delinquent Loans.
1498 |
1499 | NOTES
1500 |
1501 | MODIFICATION HISTORY
1502 | 01-SEP-2005 Hitesh Kumar Created.
1503 +===========================================================================*/
1504 FUNCTION balance_amt_delinquent_loans ( x_resultout OUT NOCOPY VARCHAR2,
1505 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
1506
1507
1508 CURSOR delinquent_loans_balance(C_PARTY_ID NUMBER) IS
1509 SELECT sum(lps.total_principal_balance) , llh.loan_currency
1510 FROM lns_loan_headers_all llh ,
1511 LNS_PAY_SUM_V lps
1512 WHERE llh.loan_id = lps.loan_id
1513 AND llh.loan_id IN ( SELECT loan_id
1514 FROM lns_participants
1515 WHERE hz_party_id = c_party_id
1516 AND ( loan_participant_type = 'PRIMARY_BORROWER'
1517 OR loan_participant_type = 'COBORROWER')
1518 )
1519 AND llh.loan_status = 'DELINQUENT'
1520 GROUP BY loan_currency ;
1521
1522
1523 l_cr_party_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_party_id ) ;
1524 l_cr_currency VARCHAR2(30) := OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_TRX_CURRENCY ;
1525
1526 l_loan_balance_amt NUMBER ;
1527 l_loan_currency lns_loan_headers_all.loan_currency%TYPE ;
1528 l_total_balance_amt NUMBER ;
1529
1530
1531 BEGIN
1532
1533 x_resultout := FND_API.G_RET_STS_SUCCESS;
1534 BEGIN
1535
1536 l_total_balance_amt := 0 ;
1537 OPEN delinquent_loans_balance(l_cr_party_id) ;
1538
1539 LOOP
1540
1541 FETCH delinquent_loans_balance INTO l_loan_balance_amt , l_loan_currency ;
1542 EXIT WHEN delinquent_loans_balance%NOTFOUND ;
1543
1544 IF(l_cr_currency<>l_loan_currency) THEN
1545 l_loan_balance_amt := gl_currency_api.convert_amount(l_loan_currency ,l_cr_currency , sysdate, null , l_loan_balance_amt);
1546 END IF ;
1547
1548 l_total_balance_amt := l_total_balance_amt + l_loan_balance_amt ;
1549
1550 END LOOP ;
1551
1552
1553
1554 EXCEPTION
1555 WHEN NO_DATA_FOUND THEN
1556 l_total_balance_amt := 0 ;
1557 WHEN OTHERS THEN
1558 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1559 x_errormsg := sqlerrm;
1560 END;
1561
1562
1563 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
1564 to_char( NVL(l_total_balance_amt,0) ) ;
1565
1566 RETURN to_char( NVL(l_total_balance_amt,0) ) ;
1567
1568
1569 END balance_amt_delinquent_loans ;
1570
1571
1572
1573
1574 /*===========================================================================+
1575 | FUNCTION number_default_loans
1576 |
1577 |
1578 | DESCRIPTION Number of defaulted loans where the credit request party is
1579 | Primary Borrower or Co-Borrower.
1580 |
1581 | SCOPE - PUBLIC
1582 |
1583 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1584 |
1585 | ARGUMENTS : IN:
1586 | OUT:
1587 | x_resultout
1588 | x_errormsg
1589 | IN/OUT:
1590 |
1591 | RETURNS : Number of Defaulted Loans for this Party.
1592 |
1593 | NOTES
1594 |
1595 | MODIFICATION HISTORY
1596 | 01-SEP-2005 Hitesh Kumar Created.
1597 +===========================================================================*/
1598
1599 FUNCTION number_default_loans ( x_resultout OUT NOCOPY VARCHAR2,
1600 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
1601
1602
1603 l_cr_party_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_party_id ) ;
1604
1605 l_count_default_loans NUMBER ;
1606
1607
1608 BEGIN
1609
1610 x_resultout := FND_API.G_RET_STS_SUCCESS;
1611 BEGIN
1612
1613 SELECT count(*)
1614 INTO l_count_default_loans
1615 FROM lns_loan_headers_all
1616 WHERE loan_id IN ( SELECT loan_id
1617 FROM lns_participants
1618 WHERE hz_party_id = l_cr_party_id
1619 AND ( loan_participant_type = 'PRIMARY_BORROWER'
1620 OR loan_participant_type = 'COBORROWER')
1621 )
1622 AND loan_status = 'DEFAULT' ;
1623
1624
1625 EXCEPTION
1626 WHEN NO_DATA_FOUND THEN
1627 l_count_default_loans := 0 ;
1628 WHEN OTHERS THEN
1629 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1630 x_errormsg := sqlerrm;
1631 END;
1632
1633
1634 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
1635 to_char( NVL(l_count_default_loans,0) ) ;
1636
1637 RETURN to_char( NVL(l_count_default_loans,0) ) ;
1638
1639 END number_default_loans ;
1640
1641
1642
1643 /*===========================================================================+
1644 | FUNCTION balance_amt_default_loans
1645 |
1646 |
1647 | DESCRIPTION Total outstanding balance of all defaulted loans where the
1648 | credit request party is Primary Borrower or Co-Borrower.
1649 |
1650 | SCOPE - PUBLIC
1651 |
1652 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1653 |
1654 | ARGUMENTS : IN:
1655 | OUT:
1656 | x_resultout
1657 | x_errormsg
1658 | IN/OUT:
1659 |
1660 | RETURNS : Outstanding Balance across all Defaulted Loans.
1661 |
1662 | NOTES
1663 |
1664 | MODIFICATION HISTORY
1665 | 01-SEP-2005 Hitesh Kumar Created.
1666 +===========================================================================*/
1667
1668 FUNCTION balance_amt_default_loans ( x_resultout OUT NOCOPY VARCHAR2,
1669 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
1670
1671
1672 CURSOR default_loans_balance(C_PARTY_ID NUMBER) IS
1673 SELECT sum(lps.total_principal_balance) , llh.loan_currency
1674 FROM lns_loan_headers_all llh ,
1675 LNS_PAY_SUM_V lps
1676 WHERE llh.loan_id = lps.loan_id
1677 AND llh.loan_id IN ( SELECT loan_id
1678 FROM lns_participants
1679 WHERE hz_party_id = c_party_id
1680 AND ( loan_participant_type = 'PRIMARY_BORROWER'
1681 OR loan_participant_type = 'COBORROWER')
1682 )
1683 AND llh.loan_status = 'DEFAULT'
1684 GROUP BY loan_currency ;
1685
1686
1687 l_cr_party_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_party_id ) ;
1688 l_cr_currency VARCHAR2(30) := OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_TRX_CURRENCY ;
1689
1690 l_loan_balance_amt NUMBER ;
1691 l_loan_currency lns_loan_headers_all.loan_currency%TYPE ;
1692 l_total_balance_amt NUMBER ;
1693
1694
1695 BEGIN
1696
1697 x_resultout := FND_API.G_RET_STS_SUCCESS;
1698 BEGIN
1699
1700 l_total_balance_amt := 0 ;
1701 OPEN default_loans_balance(l_cr_party_id) ;
1702
1703 LOOP
1704
1705 FETCH default_loans_balance INTO l_loan_balance_amt , l_loan_currency ;
1706 EXIT WHEN default_loans_balance%NOTFOUND ;
1707
1708 IF(l_cr_currency<>l_loan_currency) THEN
1709 l_loan_balance_amt := gl_currency_api.convert_amount(l_loan_currency ,l_cr_currency , sysdate, null , l_loan_balance_amt);
1710 END IF ;
1711
1712 l_total_balance_amt := l_total_balance_amt + l_loan_balance_amt ;
1713
1714 END LOOP ;
1715
1716
1717
1718 EXCEPTION
1719 WHEN NO_DATA_FOUND THEN
1720 l_total_balance_amt := 0 ;
1721 WHEN OTHERS THEN
1722 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1723 x_errormsg := sqlerrm;
1724 END;
1725
1726
1727 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
1728 to_char( NVL(l_total_balance_amt,0) ) ;
1729
1730 RETURN to_char( NVL(l_total_balance_amt,0) ) ;
1731
1732 END balance_amt_default_loans ;
1733
1734
1735
1736
1737 /*===========================================================================+
1738 | FUNCTION number_paidoff_loans
1739 |
1740 |
1741 | DESCRIPTION Number of Paid-Off loans where the credit request party is
1742 | Primary Borrower or Co-Borrower.
1743 |
1744 | SCOPE - PUBLIC
1745 |
1746 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1747 |
1748 | ARGUMENTS : IN:
1749 | OUT:
1750 | x_resultout
1751 | x_errormsg
1752 | IN/OUT:
1753 |
1754 | RETURNS : Number of Paid-Off Loans for this Party.
1755 |
1756 | NOTES
1757 |
1758 | MODIFICATION HISTORY
1759 | 01-SEP-2005 Hitesh Kumar Created.
1760 +===========================================================================*/
1761
1762 FUNCTION number_paidoff_loans ( x_resultout OUT NOCOPY VARCHAR2,
1763 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
1764
1765
1766 l_cr_party_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_party_id ) ;
1767
1768 l_count_paidoff_loans NUMBER ;
1769
1770
1771 BEGIN
1772
1773 x_resultout := FND_API.G_RET_STS_SUCCESS;
1774 BEGIN
1775
1776 SELECT count(*)
1777 INTO l_count_paidoff_loans
1778 FROM lns_loan_headers_all
1779 WHERE loan_id IN ( SELECT loan_id
1780 FROM lns_participants
1781 WHERE hz_party_id = l_cr_party_id
1782 AND ( loan_participant_type = 'PRIMARY_BORROWER'
1783 OR loan_participant_type = 'COBORROWER')
1784 )
1785 AND loan_status = 'PAIDOFF' ;
1786
1787
1788 EXCEPTION
1789 WHEN NO_DATA_FOUND THEN
1790 l_count_paidoff_loans := 0 ;
1791 WHEN OTHERS THEN
1792 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1793 x_errormsg := sqlerrm;
1794 END;
1795
1796
1797 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
1798 to_char( NVL(l_count_paidoff_loans,0) ) ;
1799
1800 RETURN to_char( NVL(l_count_paidoff_loans,0) ) ;
1801
1802
1803 END number_paidoff_loans ;
1804
1805
1806
1807 /*===========================================================================+
1808 | FUNCTION balance_amt_paidoff_loans
1809 |
1810 |
1811 | DESCRIPTION Total outstanding balance of all paid-off loans where the
1812 | credit request party is Primary Borrower or Co-Borrower.
1813 |
1814 | SCOPE - PUBLIC
1815 |
1816 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1817 |
1818 | ARGUMENTS : IN:
1819 | OUT:
1820 | x_resultout
1821 | x_errormsg
1822 | IN/OUT:
1823 |
1824 | RETURNS : Outstanding Balance across all Paid-Off Loans.
1825 |
1826 | NOTES
1827 |
1828 | MODIFICATION HISTORY
1829 | 01-SEP-2005 Hitesh Kumar Created.
1830 +===========================================================================*/
1831 FUNCTION balance_amt_paidoff_loans ( x_resultout OUT NOCOPY VARCHAR2,
1832 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
1833
1834
1835 CURSOR paidoff_loans_balance(C_PARTY_ID NUMBER) IS
1836 SELECT sum(lps.total_principal_balance) , llh.loan_currency
1837 FROM lns_loan_headers_all llh ,
1838 LNS_PAY_SUM_V lps
1839 WHERE llh.loan_id = lps.loan_id
1840 AND llh.loan_id IN ( SELECT loan_id
1841 FROM lns_participants
1842 WHERE hz_party_id = c_party_id
1843 AND ( loan_participant_type = 'PRIMARY_BORROWER'
1844 OR loan_participant_type = 'COBORROWER')
1845 )
1846 AND llh.loan_status = 'PAIDOFF'
1847 GROUP BY loan_currency ;
1848
1849
1850 l_cr_party_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_party_id ) ;
1851 l_cr_currency VARCHAR2(30) := OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_TRX_CURRENCY ;
1852
1853 l_loan_balance_amt NUMBER ;
1854 l_loan_currency lns_loan_headers_all.loan_currency%TYPE ;
1855 l_total_balance_amt NUMBER ;
1856
1857
1858 BEGIN
1859
1860 x_resultout := FND_API.G_RET_STS_SUCCESS;
1861 BEGIN
1862
1863 l_total_balance_amt := 0 ;
1864 OPEN paidoff_loans_balance(l_cr_party_id) ;
1865
1866 LOOP
1867
1868 FETCH paidoff_loans_balance INTO l_loan_balance_amt , l_loan_currency ;
1869 EXIT WHEN paidoff_loans_balance%NOTFOUND ;
1870
1871 IF(l_cr_currency<>l_loan_currency) THEN
1872 l_loan_balance_amt := gl_currency_api.convert_amount(l_loan_currency ,l_cr_currency , sysdate, null , l_loan_balance_amt);
1873 END IF ;
1874
1875 l_total_balance_amt := l_total_balance_amt + l_loan_balance_amt ;
1876
1877 END LOOP ;
1878
1879
1880
1881 EXCEPTION
1882 WHEN NO_DATA_FOUND THEN
1883 l_total_balance_amt := 0 ;
1884 WHEN OTHERS THEN
1885 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1886 x_errormsg := sqlerrm;
1887 END;
1888
1889
1890 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
1891 to_char( NVL(l_total_balance_amt,0) ) ;
1892
1893 RETURN to_char( NVL(l_total_balance_amt,0) ) ;
1894
1895
1896 END balance_amt_paidoff_loans ;
1897
1898
1899
1900
1901 /*===========================================================================+
1902 | FUNCTION total_active_loans
1903 |
1904 |
1905 | DESCRIPTION Total active loans where credit request party is a Primary Borrower
1906 | or a Co-Borrower.
1907 | If the party is current loan's primary borrower , count all loans of
1908 | primary borrower and all co-borrower(s).
1909 | Else if the party is current loan's guarantor , count all loans of the
1910 | guarantor.
1911 |
1912 |
1913 | SCOPE - PUBLIC
1914 |
1915 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1916 |
1917 | ARGUMENTS : IN:
1918 | OUT:
1919 | x_resultout
1920 | x_errormsg
1921 | IN/OUT:
1922 |
1923 | RETURNS : Total Number of Active Loans for all Party on this Loan Application.
1924 |
1925 | NOTES
1926 |
1927 | MODIFICATION HISTORY
1928 | 01-SEP-2005 Hitesh Kumar Created.
1929 +===========================================================================*/
1930
1931 FUNCTION total_active_loans ( x_resultout OUT NOCOPY VARCHAR2,
1932 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
1933
1934 l_cr_party_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_party_id ) ;
1935 l_cr_loan_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_source_column1 ) ;
1936
1937 l_total_active_loans NUMBER ;
1938
1939
1940 BEGIN
1941
1942 x_resultout := FND_API.G_RET_STS_SUCCESS;
1943 BEGIN
1944
1945 SELECT count(*)
1946 INTO l_total_active_loans
1947 FROM lns_loan_headers_all
1948 WHERE loan_id IN ( SELECT loan_id
1949 FROM lns_participants
1950 WHERE hz_party_id IN ( SELECT hz_party_id
1951 FROM lns_participants
1952 WHERE loan_id = l_cr_loan_id
1953 AND (loan_participant_type = 'PRIMARY_BORROWER' or loan_participant_type = 'COBORROWER' )
1954 AND EXISTS ( select null from lns_participants
1955 where loan_id = l_cr_loan_id
1956 and hz_party_id = l_cr_party_id
1957 and loan_participant_type = 'PRIMARY_BORROWER' )
1958
1959 UNION ALL
1960
1961 SELECT hz_party_id
1962 FROM lns_participants
1963 WHERE loan_id = l_cr_loan_id
1964 AND hz_party_id = l_cr_party_id
1965 AND loan_participant_type = 'GUARANTOR'
1966 )
1967 AND ( loan_participant_type = 'PRIMARY_BORROWER' OR loan_participant_type = 'COBORROWER' )
1968 )
1969 AND loan_status = 'ACTIVE' ;
1970
1971
1972 EXCEPTION
1973 WHEN NO_DATA_FOUND THEN
1974 l_total_active_loans := 0 ;
1975 WHEN OTHERS THEN
1976 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
1977 x_errormsg := sqlerrm;
1978 END;
1979
1980
1981 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
1982 to_char( NVL(l_total_active_loans,0) ) ;
1983
1984 RETURN to_char( NVL(l_total_active_loans,0) ) ;
1985
1986
1987 End total_active_loans ;
1988
1989
1990
1991
1992 /*===========================================================================+
1993 | FUNCTION total_bal_amt_active_loans
1994 |
1995 |
1996 | DESCRIPTION Total outstanding balance accross all active loans where the
1997 | credit request party is a Primary Borrower or Co-Borrower.
1998 | If the party is current loan's primary borrower , sum for all loans of
1999 | primary borrower and all co-borrower(s).
2000 | Else if the party is current loan's guarantor , sum for all loans of the
2001 | guarantor.
2002 |
2003 |
2004 | SCOPE - PUBLIC
2005 |
2006 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2007 |
2008 | ARGUMENTS : IN:
2009 | OUT:
2010 | x_resultout
2011 | x_errormsg
2012 | IN/OUT:
2013 |
2014 | RETURNS : Total Outstanding Balance accross all Active Loans for all Party on this Loan Application.
2015 |
2016 | NOTES
2017 |
2018 | MODIFICATION HISTORY
2019 | 01-SEP-2005 Hitesh Kumar Created.
2020 +===========================================================================*/
2021
2022 FUNCTION total_bal_amt_active_loans ( x_resultout OUT NOCOPY VARCHAR2,
2023 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
2024
2025
2026 CURSOR total_active_loans_balance(C_LOAN_ID NUMBER , C_PARTY_ID NUMBER) IS
2027 SELECT sum( nvl(lps.total_principal_balance,0) ) , llh.loan_currency
2028 FROM lns_loan_headers_all llh ,
2029 LNS_PAY_SUM_V lps
2030 WHERE llh.loan_id = lps.loan_id
2031 AND llh.loan_id IN ( SELECT loan_id
2032 FROM lns_participants
2033 WHERE hz_party_id IN ( SELECT hz_party_id
2034 FROM lns_participants
2035 WHERE loan_id = c_loan_id
2036 AND (loan_participant_type = 'PRIMARY_BORROWER' or loan_participant_type = 'COBORROWER' )
2037 AND EXISTS ( select null from lns_participants where loan_id = c_loan_id and hz_party_id = c_party_id and loan_participant_type = 'PRIMARY_BORROWER' )
2038
2039 UNION ALL
2040
2041 SELECT hz_party_id
2042 FROM lns_participants
2043 WHERE loan_id = c_loan_id
2044 AND hz_party_id = c_party_id
2045 AND loan_participant_type = 'GUARANTOR'
2046 )
2047
2048 AND ( loan_participant_type = 'PRIMARY_BORROWER' OR loan_participant_type = 'COBORROWER' )
2049 )
2050 AND llh.loan_status = 'ACTIVE'
2051 GROUP BY loan_currency ;
2052
2053
2054 l_cr_party_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_party_id ) ;
2055 l_cr_loan_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_source_column1 ) ;
2056 l_cr_currency VARCHAR2(30) := OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_TRX_CURRENCY ;
2057
2058 l_loan_balance_amt NUMBER ;
2059 l_loan_currency lns_loan_headers_all.loan_currency%TYPE ;
2060 l_total_balance_amt NUMBER ;
2061
2062
2063 BEGIN
2064
2065 x_resultout := FND_API.G_RET_STS_SUCCESS;
2066 BEGIN
2067
2068 l_total_balance_amt := 0 ;
2069 OPEN total_active_loans_balance(l_cr_loan_id ,l_cr_party_id) ;
2070
2071 LOOP
2072
2073 FETCH total_active_loans_balance INTO l_loan_balance_amt , l_loan_currency ;
2074 EXIT WHEN total_active_loans_balance%NOTFOUND ;
2075
2076 IF(l_cr_currency<>l_loan_currency) THEN
2077 l_loan_balance_amt := gl_currency_api.convert_amount(l_loan_currency ,l_cr_currency , sysdate, null , l_loan_balance_amt);
2078 END IF ;
2079
2080 l_total_balance_amt := l_total_balance_amt + l_loan_balance_amt ;
2081
2082 END LOOP ;
2083
2084
2085 EXCEPTION
2086 WHEN NO_DATA_FOUND THEN
2087 l_total_balance_amt := 0 ;
2088 WHEN OTHERS THEN
2089 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2090 x_errormsg := sqlerrm;
2091 END;
2092
2093
2094 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
2095 to_char( NVL(l_total_balance_amt,0) ) ;
2096
2097 RETURN to_char( NVL(l_total_balance_amt,0) ) ;
2098
2099
2100
2101 END total_bal_amt_active_loans ;
2102
2103
2104 /*===========================================================================+
2105 | FUNCTION total_deliquent_loans
2106 |
2107 |
2108 | DESCRIPTION Total deliquent loans where credit request party is a Primary Borrower
2109 | or a Co-Borrower.
2110 | If the party is current loan's primary borrower , count all loans of
2111 | primary borrower and all co-borrower(s).
2112 | Else if the party is current loan's guarantor , count all loans of the
2113 | guarantor.
2114 |
2115 |
2116 | SCOPE - PUBLIC
2117 |
2118 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2119 |
2120 | ARGUMENTS : IN:
2121 | OUT:
2122 | x_resultout
2123 | x_errormsg
2124 | IN/OUT:
2125 |
2126 | RETURNS : Total Number of Deliquent Loans for all Party on this Loan Application.
2127 |
2128 | NOTES
2129 |
2130 | MODIFICATION HISTORY
2131 | 01-SEP-2005 Hitesh Kumar Created.
2132 +===========================================================================*/
2133
2134 FUNCTION total_deliquent_loans ( x_resultout OUT NOCOPY VARCHAR2,
2135 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
2136
2137 l_cr_party_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_party_id ) ;
2138 l_cr_loan_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_source_column1 ) ;
2139
2140 l_total_deliquent_loans NUMBER ;
2141
2142
2143 BEGIN
2144
2145 x_resultout := FND_API.G_RET_STS_SUCCESS;
2146 BEGIN
2147
2148 SELECT count(*)
2149 INTO l_total_deliquent_loans
2150 FROM lns_loan_headers_all
2151 WHERE loan_id IN ( SELECT loan_id
2152 FROM lns_participants
2153 WHERE hz_party_id IN ( SELECT hz_party_id
2154 FROM lns_participants
2155 WHERE loan_id = l_cr_loan_id
2156 AND (loan_participant_type = 'PRIMARY_BORROWER' or loan_participant_type = 'COBORROWER' )
2157 AND EXISTS ( select null from lns_participants
2158 where loan_id = l_cr_loan_id
2159 and hz_party_id = l_cr_party_id
2160 and loan_participant_type = 'PRIMARY_BORROWER' )
2161
2162 UNION ALL
2163
2164 SELECT hz_party_id
2165 FROM lns_participants
2166 WHERE loan_id = l_cr_loan_id
2167 AND hz_party_id = l_cr_party_id
2168 AND loan_participant_type = 'GUARANTOR'
2169 )
2170 AND ( loan_participant_type = 'PRIMARY_BORROWER' OR loan_participant_type = 'COBORROWER' )
2171 )
2172 AND loan_status = 'DELINQUENT' ;
2173
2174
2175 EXCEPTION
2176 WHEN NO_DATA_FOUND THEN
2177 l_total_deliquent_loans := 0 ;
2178 WHEN OTHERS THEN
2179 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2180 x_errormsg := sqlerrm;
2181 END;
2182
2183
2184 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
2185 to_char( NVL(l_total_deliquent_loans,0) ) ;
2186
2187 RETURN to_char( NVL(l_total_deliquent_loans,0) ) ;
2188
2189 END total_deliquent_loans ;
2190
2191
2192
2193
2194 /*===========================================================================+
2195 | FUNCTION total_overdue_amt_active_loans
2196 |
2197 |
2198 | DESCRIPTION Total overdue amount accross all active loans where the
2199 | credit request party is a Primary Borrower or Co-Borrower.
2200 | If the party is current loan's primary borrower , sum for all loans of
2201 | primary borrower and all co-borrower(s).
2202 | Else if the party is current loan's guarantor , sum for all loans of the
2203 | guarantor.
2204 |
2205 |
2206 | SCOPE - PUBLIC
2207 |
2208 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2209 |
2210 | ARGUMENTS : IN:
2211 | OUT:
2212 | x_resultout
2213 | x_errormsg
2214 | IN/OUT:
2215 |
2216 | RETURNS : Total Overdue Amount accross all Active Loans for all Party on this Loan Application.
2217 |
2218 | NOTES
2219 |
2220 | MODIFICATION HISTORY
2221 | 01-SEP-2005 Hitesh Kumar Created.
2222 +===========================================================================*/
2223
2224 FUNCTION total_overdue_amt_active_loans ( x_resultout OUT NOCOPY VARCHAR2,
2225 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
2226
2227 CURSOR total_active_loans_overdue(C_LOAN_ID NUMBER , C_PARTY_ID NUMBER) IS
2228 SELECT sum( nvl(lps.total_overdue ,0) ) , llh.loan_currency
2229 FROM lns_loan_headers_all llh ,
2230 LNS_PAY_SUM_OVERDUE_V lps
2231 WHERE llh.loan_id = lps.loan_id
2232 AND llh.loan_id IN ( SELECT loan_id
2233 FROM lns_participants
2234 WHERE hz_party_id IN ( SELECT hz_party_id
2235 FROM lns_participants
2236 WHERE loan_id = c_loan_id
2237 AND (loan_participant_type = 'PRIMARY_BORROWER' or loan_participant_type = 'COBORROWER' )
2238 AND EXISTS ( select null from lns_participants where loan_id = c_loan_id and hz_party_id = c_party_id and loan_participant_type = 'PRIMARY_BORROWER' )
2239
2240 UNION ALL
2241
2242 SELECT hz_party_id
2243 FROM lns_participants
2244 WHERE loan_id = c_loan_id
2245 AND hz_party_id = c_party_id
2246 AND loan_participant_type = 'GUARANTOR'
2247 )
2248
2249 AND ( loan_participant_type = 'PRIMARY_BORROWER' OR loan_participant_type = 'COBORROWER' )
2250 )
2251 AND llh.loan_status = 'ACTIVE'
2252 GROUP BY loan_currency ;
2253
2254
2255 l_cr_party_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_party_id ) ;
2256 l_cr_loan_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_source_column1 ) ;
2257 l_cr_currency VARCHAR2(30) := OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_TRX_CURRENCY ;
2258
2259 l_loan_overdue_amt NUMBER ;
2260 l_loan_currency lns_loan_headers_all.loan_currency%TYPE ;
2261 l_total_overdue_amt NUMBER ;
2262
2263
2264 BEGIN
2265
2266 x_resultout := FND_API.G_RET_STS_SUCCESS;
2267 BEGIN
2268
2269 l_total_overdue_amt := 0 ;
2270 OPEN total_active_loans_overdue(l_cr_loan_id ,l_cr_party_id) ;
2271
2272 LOOP
2273
2274 FETCH total_active_loans_overdue INTO l_loan_overdue_amt , l_loan_currency ;
2275 EXIT WHEN total_active_loans_overdue%NOTFOUND ;
2276
2277 IF(l_cr_currency<>l_loan_currency) THEN
2278 l_loan_overdue_amt := gl_currency_api.convert_amount(l_loan_currency ,l_cr_currency , sysdate, null , l_loan_overdue_amt);
2279 END IF ;
2280
2281 l_total_overdue_amt := l_total_overdue_amt + l_loan_overdue_amt ;
2282
2283 END LOOP ;
2284
2285
2286 EXCEPTION
2287 WHEN NO_DATA_FOUND THEN
2288 l_total_overdue_amt := 0 ;
2289 WHEN OTHERS THEN
2290 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2291 x_errormsg := sqlerrm;
2292 END;
2293
2294
2295 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
2296 to_char( NVL(l_total_overdue_amt,0) ) ;
2297
2298 RETURN to_char( NVL(l_total_overdue_amt,0) ) ;
2299
2300
2301 END total_overdue_amt_active_loans ;
2302
2303
2304
2305
2306 /*===========================================================================+
2307 | FUNCTION total_defaulted_loans
2308 |
2309 |
2310 | DESCRIPTION Total defaulted loans where credit request party is a Primary Borrower
2311 | or a Co-Borrower.
2312 | If the party is current loan's primary borrower , count all loans of
2313 | primary borrower and all co-borrower(s).
2314 | Else if the party is current loan's guarantor , count all loans of the
2315 | guarantor.
2316 |
2317 |
2318 | SCOPE - PUBLIC
2319 |
2320 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2321 |
2322 | ARGUMENTS : IN:
2323 | OUT:
2324 | x_resultout
2325 | x_errormsg
2326 | IN/OUT:
2327 |
2328 | RETURNS : Total Number of Defaulted Loans for all Party on this Loan Application.
2329 |
2330 | NOTES
2331 |
2332 | MODIFICATION HISTORY
2333 | 01-SEP-2005 Hitesh Kumar Created.
2334 +===========================================================================*/
2335
2336 FUNCTION total_defaulted_loans ( x_resultout OUT NOCOPY VARCHAR2,
2337 x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
2338
2339 l_cr_party_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_party_id ) ;
2340 l_cr_loan_id NUMBER := to_number(OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_source_column1 ) ;
2341
2342 l_total_default_loans NUMBER ;
2343
2344
2345 BEGIN
2346
2347 x_resultout := FND_API.G_RET_STS_SUCCESS;
2348 BEGIN
2349
2350 SELECT count(*)
2351 INTO l_total_default_loans
2352 FROM lns_loan_headers_all
2353 WHERE loan_id IN ( SELECT loan_id
2354 FROM lns_participants
2355 WHERE hz_party_id IN ( SELECT hz_party_id
2356 FROM lns_participants
2357 WHERE loan_id = l_cr_loan_id
2358 AND (loan_participant_type = 'PRIMARY_BORROWER' or loan_participant_type = 'COBORROWER' )
2359 AND EXISTS ( select null from lns_participants
2360 where loan_id = l_cr_loan_id
2361 and hz_party_id = l_cr_party_id
2362 and loan_participant_type = 'PRIMARY_BORROWER' )
2363
2364 UNION ALL
2365
2366 SELECT hz_party_id
2367 FROM lns_participants
2368 WHERE loan_id = l_cr_loan_id
2369 AND hz_party_id = l_cr_party_id
2370 AND loan_participant_type = 'GUARANTOR'
2371 )
2372 AND ( loan_participant_type = 'PRIMARY_BORROWER' OR loan_participant_type = 'COBORROWER' )
2373 )
2374 AND loan_status = 'DEFAULT' ;
2375
2376
2377 EXCEPTION
2378 WHEN NO_DATA_FOUND THEN
2379 l_total_default_loans := 0 ;
2380 WHEN OTHERS THEN
2381 x_resultout := FND_API.G_RET_STS_UNEXP_ERROR;
2382 x_errormsg := sqlerrm;
2383 END;
2384
2385
2386 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.P_data_point_value :=
2387 to_char( NVL(l_total_default_loans,0) ) ;
2388
2389 RETURN to_char( NVL(l_total_default_loans,0) ) ;
2390
2391
2392 END total_defaulted_loans ;
2393
2394
2395
2396
2397 END LNS_OCM_ADP_PUB ;
2398