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