1 PACKAGE BODY LNS_BILLING_UTIL_PUB AS
2 /* $Header: LNS_BILL_UTIL_B.pls 120.5.12010000.2 2008/10/02 18:39:40 scherkas ship $ */
3
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7
8 G_PKG_NAME CONSTANT VARCHAR2(30):= 'LNS_BILLING_UTIL_PUB';
9
10
11
12 /*========================================================================
13 | PUBLIC FUNCTION LAST_PAYMENT_NUMBER
14 |
15 | DESCRIPTION
16 | This procedure calculates last payment number.
17 |
18 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
19 | None
20 |
21 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
22 | None
23 |
24 | PARAMETERS
25 | P_LOAN_ID IN Last payment number for Loan ID
26 |
27 | KNOWN ISSUES
28 | None
29 |
30 | NOTES
31 | Any interesting aspect of the code in the package body which needs
32 | to be stated.
33 |
34 | MODIFICATION HISTORY
35 | Date Author Description of Changes
36 | 01-01-2004 scherkas Created
37 |
38 *=======================================================================*/
39 FUNCTION LAST_PAYMENT_NUMBER(P_LOAN_ID IN NUMBER) RETURN NUMBER
40 IS
41
42 /*-----------------------------------------------------------------------+
43 | Local Variable Declarations and initializations |
44 +-----------------------------------------------------------------------*/
45 l_api_name CONSTANT VARCHAR2(30) := 'LAST_PAYMENT_NUMBER';
46 l_return NUMBER;
47
48 /*-----------------------------------------------------------------------+
49 | Cursor Declarations |
50 +-----------------------------------------------------------------------*/
51 /*
52 CURSOR get_data_crs(P_LOAN_ID NUMBER) IS
53 select nvl(max(am.PAYMENT_NUMBER), 0)
54 from LNS_AMORTIZATION_SCHEDS am,
55 lns_loan_headers head
56 where am.LOAN_ID = P_LOAN_ID and
57 am.LOAN_ID = head.LOAN_ID
58 and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
59 and am.PARENT_AMORTIZATION_ID is null
60 and nvl(am.PHASE, 'TERM') = nvl(head.CURRENT_PHASE, 'TERM');
61 */
62 BEGIN
63 /*
64 OPEN get_data_crs(P_LOAN_ID);
65 FETCH get_data_crs INTO l_return;
66 CLOSE get_data_crs;
67 */
68 return LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(P_LOAN_ID, null);
69 END;
70
71
72
73 /*========================================================================
74 | PUBLIC FUNCTION LAST_PAYMENT_NUMBER
75 |
76 | DESCRIPTION
77 | This procedure calculates last payment number.
78 |
79 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
80 | None
81 |
82 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
83 | None
84 |
85 | PARAMETERS
86 | P_LOAN_ID IN Last payment number for Loan ID
87 | P_PHASE IN Phase
88 |
89 | KNOWN ISSUES
90 | None
91 |
92 | NOTES
93 | Any interesting aspect of the code in the package body which needs
94 | to be stated.
95 |
96 | MODIFICATION HISTORY
97 | Date Author Description of Changes
98 | 01-01-2004 scherkas Created
99 |
100 *=======================================================================*/
101 FUNCTION LAST_PAYMENT_NUMBER(P_LOAN_ID IN NUMBER, P_PHASE IN VARCHAR2) RETURN NUMBER
102 IS
103
104 /*-----------------------------------------------------------------------+
105 | Local Variable Declarations and initializations |
106 +-----------------------------------------------------------------------*/
107 l_api_name CONSTANT VARCHAR2(30) := 'LAST_PAYMENT_NUMBER';
108 l_return NUMBER;
109
110 /*-----------------------------------------------------------------------+
111 | Cursor Declarations |
112 +-----------------------------------------------------------------------*/
113
114 CURSOR get_data_crs(P_LOAN_ID NUMBER, P_PHASE VARCHAR2) IS
115 select nvl(max(am.PAYMENT_NUMBER), 0)
116 from LNS_AMORTIZATION_SCHEDS am,
117 lns_loan_headers head
118 where am.LOAN_ID = P_LOAN_ID
119 and am.LOAN_ID = head.LOAN_ID
120 and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
121 and am.PARENT_AMORTIZATION_ID is null
122 and am.REAMORTIZATION_AMOUNT is null
123 and nvl(am.PHASE, 'TERM') = nvl(P_PHASE, nvl(head.CURRENT_PHASE, 'TERM'));
124
125 BEGIN
126 OPEN get_data_crs(P_LOAN_ID, P_PHASE);
127 FETCH get_data_crs INTO l_return;
128 CLOSE get_data_crs;
129
130 return l_return;
131 END;
132
133
134
135 /*========================================================================
136 | PUBLIC FUNCTION LAST_PAYMENT_NUMBER_EXT
137 |
138 | DESCRIPTION
139 | This procedure calculates last payment number taking in consideration 0-th installment
140 |
141 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
142 | None
143 |
144 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
145 | None
146 |
147 | PARAMETERS
148 | P_LOAN_ID IN Last payment number for Loan ID
149 |
150 | KNOWN ISSUES
151 | None
152 |
153 | NOTES
154 | Any interesting aspect of the code in the package body which needs
155 | to be stated.
156 |
157 | MODIFICATION HISTORY
158 | Date Author Description of Changes
159 | 01-01-2004 scherkas Created
160 |
161 *=======================================================================*/
162 FUNCTION LAST_PAYMENT_NUMBER_EXT(P_LOAN_ID IN NUMBER) RETURN NUMBER
163 IS
164
165 /*-----------------------------------------------------------------------+
166 | Local Variable Declarations and initializations |
167 +-----------------------------------------------------------------------*/
168 l_api_name CONSTANT VARCHAR2(30) := 'LAST_PAYMENT_NUMBER_EXT';
169 l_return NUMBER;
170
171 /*-----------------------------------------------------------------------+
172 | Cursor Declarations |
173 +-----------------------------------------------------------------------*/
174
175 CURSOR get_data_crs(P_LOAN_ID NUMBER) IS
176 select nvl(max(am.PAYMENT_NUMBER), -1)
177 from LNS_AMORTIZATION_SCHEDS am,
178 lns_loan_headers head
179 where am.LOAN_ID = P_LOAN_ID and
180 am.LOAN_ID = head.LOAN_ID
181 and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
182 -- and am.PARENT_AMORTIZATION_ID is null
183 and am.REAMORTIZATION_AMOUNT is null -- fix for bug 7422383
184 and nvl(am.PHASE, 'TERM') = nvl(head.CURRENT_PHASE, 'TERM');
185
186 BEGIN
187 OPEN get_data_crs(P_LOAN_ID);
188 FETCH get_data_crs INTO l_return;
189 CLOSE get_data_crs;
190
191 return l_return;
192 END;
193
194
195
196 /*========================================================================
197 | PUBLIC FUNCTION LAST_AMORTIZATION_SCHED
198 |
199 | DESCRIPTION
200 | This procedure returns last amortization schedule id.
201 |
202 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
203 | None
204 |
205 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
206 | None
207 |
208 | PARAMETERS
209 | P_LOAN_ID IN Last payment number for Loan ID
210 |
211 | KNOWN ISSUES
212 | None
213 |
214 | NOTES
215 | Any interesting aspect of the code in the package body which needs
216 | to be stated.
217 |
218 | MODIFICATION HISTORY
219 | Date Author Description of Changes
220 | 01-01-2004 scherkas Created
221 |
222 *=======================================================================*/
223 FUNCTION LAST_AMORTIZATION_SCHED(P_LOAN_ID IN NUMBER) RETURN NUMBER
224 IS
225
226 /*-----------------------------------------------------------------------+
227 | Local Variable Declarations and initializations |
228 +-----------------------------------------------------------------------*/
229 l_api_name CONSTANT VARCHAR2(30) := 'LAST_AMORTIZATION_SCHED';
230 l_return NUMBER;
231
232 /*-----------------------------------------------------------------------+
233 | Cursor Declarations |
234 +-----------------------------------------------------------------------*/
235
236 CURSOR get_data_crs(P_LOAN_ID NUMBER) IS
237 select nvl(max(am.AMORTIZATION_SCHEDULE_ID), -1)
238 from LNS_AMORTIZATION_SCHEDS am,
239 lns_loan_headers head
240 where am.LOAN_ID = P_LOAN_ID
241 and am.LOAN_ID = head.LOAN_ID
242 and am.PAYMENT_NUMBER = LAST_PAYMENT_NUMBER(am.LOAN_ID)
243 and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
244 and am.PARENT_AMORTIZATION_ID is null
245 and nvl(am.PHASE, 'TERM') = nvl(head.CURRENT_PHASE, 'TERM');
246
247 BEGIN
248 OPEN get_data_crs(P_LOAN_ID);
249 FETCH get_data_crs INTO l_return;
250 CLOSE get_data_crs;
251
252 return l_return;
253 END;
254
255
256
257 /*========================================================================
258 | PUBLIC FUNCTION NEXT_PAYMENT_DUE
259 |
260 | DESCRIPTION
261 | This procedure calculates next payment due.
262 |
263 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
264 | None
265 |
266 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
267 | None
268 |
269 | PARAMETERS
270 | P_LOAN_ID IN Last payment number for Loan ID
271 | P_LINE_TYPE Amount due for this line type
272 | P_PAYMENT_NUMBER Amount due for this payment number
273 |
274 | KNOWN ISSUES
275 | None
276 |
277 | NOTES
278 | Any interesting aspect of the code in the package body which needs
279 | to be stated.
280 |
281 | MODIFICATION HISTORY
282 | Date Author Description of Changes
283 | 01-01-2004 scherkas Created
284 | 01-19-2206 scherkas Obsoleted (use LNS_PAY_SUM_V instead)
285 |
286 *=======================================================================*/
287 FUNCTION NEXT_PAYMENT_DUE(P_LOAN_ID IN NUMBER, P_LINE_TYPE IN VARCHAR2, P_PAYMENT_NUMBER IN NUMBER) RETURN NUMBER
288 IS
289
290 /*-----------------------------------------------------------------------+
291 | Local Variable Declarations and initializations |
292 +-----------------------------------------------------------------------*/
293 l_api_name CONSTANT VARCHAR2(30) := 'NEXT_PAYMENT_DUE';
294 l_return NUMBER;
295
296 /*-----------------------------------------------------------------------+
297 | Cursor Declarations |
298 +-----------------------------------------------------------------------*/
299
300 BEGIN
301
302 return 0;
303 END;
304
305
306
307
308 /*========================================================================
309 | PUBLIC FUNCTION AMOUNT_PAID_YTD
310 |
311 | DESCRIPTION
312 | This procedure calculates amount paid YTD.
313 |
314 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
315 | None
316 |
317 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
318 | None
319 |
320 | PARAMETERS
321 | P_LOAN_ID IN Amount paid for Loan ID
322 | P_LINE_TYPE IN Amount paid for this line type
323 |
324 | KNOWN ISSUES
325 | None
326 |
327 | NOTES
328 | Any interesting aspect of the code in the package body which needs
329 | to be stated.
330 |
331 | MODIFICATION HISTORY
332 | Date Author Description of Changes
333 | 01-01-2004 scherkas Created
334 | 01-19-2206 scherkas Obsoleted (use LNS_PAY_SUM_V instead)
335 |
336 *=======================================================================*/
337 FUNCTION AMOUNT_PAID_YTD(P_LOAN_ID IN NUMBER, P_LINE_TYPE VARCHAR2) RETURN NUMBER
338 IS
339
340 /*-----------------------------------------------------------------------+
341 | Local Variable Declarations and initializations |
342 +-----------------------------------------------------------------------*/
343 l_api_name CONSTANT VARCHAR2(30) := 'AMOUNT_PAID_YTD';
344 l_return NUMBER;
345
346 /*-----------------------------------------------------------------------+
347 | Cursor Declarations |
348 +-----------------------------------------------------------------------*/
349
350 BEGIN
351
352 return 0;
353 END;
354
355
356
357 /*========================================================================
358 | PUBLIC FUNCTION AMOUNT_PAID_LAST_YEAR
359 |
360 | DESCRIPTION
361 | This procedure calculates amount paid last year.
362 |
363 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
364 | None
365 |
366 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
367 | None
368 |
369 | PARAMETERS
370 | P_LOAN_ID IN Amount paid for Loan ID
371 | P_LINE_TYPE IN Amount paid for this line type
372 |
373 | KNOWN ISSUES
374 | None
375 |
376 | NOTES
377 | Any interesting aspect of the code in the package body which needs
378 | to be stated.
379 |
380 | MODIFICATION HISTORY
381 | Date Author Description of Changes
382 | 01-01-2004 scherkas Created
383 | 01-19-2206 scherkas Obsoleted (use LNS_PAY_SUM_YEARLY_V instead)
384 |
385 *=======================================================================*/
386 FUNCTION AMOUNT_PAID_LAST_YEAR(P_LOAN_ID IN NUMBER, P_LINE_TYPE VARCHAR2) RETURN NUMBER
387 IS
388
389 /*-----------------------------------------------------------------------+
390 | Local Variable Declarations and initializations |
391 +-----------------------------------------------------------------------*/
392 l_api_name CONSTANT VARCHAR2(30) := 'AMOUNT_PAID_LAST_YEAR';
393 l_return NUMBER;
394
395 /*-----------------------------------------------------------------------+
396 | Cursor Declarations |
397 +-----------------------------------------------------------------------*/
398
399 BEGIN
400
401 return 0;
402 END;
403
404
405
406 /*========================================================================
407 | PUBLIC FUNCTION AMOUNT_OVERDUE
408 |
409 | DESCRIPTION
410 | This procedure calculates amount overdue.
411 |
412 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
413 | None
414 |
418 | PARAMETERS
415 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
416 | None
417 |
419 | P_LOAN_ID IN Amount paid for Loan ID
420 | P_LINE_TYPE IN Amount paid for this line type
421 |
422 | KNOWN ISSUES
423 | None
424 |
425 | NOTES
426 | Any interesting aspect of the code in the package body which needs
427 | to be stated.
428 |
429 | MODIFICATION HISTORY
430 | Date Author Description of Changes
431 | 01-01-2004 scherkas Created
432 | 01-19-2206 scherkas Obsoleted (use LNS_PAY_SUM_OVERDUE_V instead)
433 |
434 *=======================================================================*/
435 FUNCTION AMOUNT_OVERDUE(P_LOAN_ID IN NUMBER, P_LINE_TYPE VARCHAR2) RETURN NUMBER
436 IS
437
438 /*-----------------------------------------------------------------------+
439 | Local Variable Declarations and initializations |
440 +-----------------------------------------------------------------------*/
441 l_api_name CONSTANT VARCHAR2(30) := 'AMOUNT_OVERDUE';
442 l_return NUMBER;
443
444 /*-----------------------------------------------------------------------+
445 | Cursor Declarations |
446 +-----------------------------------------------------------------------*/
447
448 BEGIN
449
450 return 0;
451 END;
452
453
454
455 /*========================================================================
456 | PUBLIC FUNCTION NUMBER_OVERDUE_BILLS
457 |
458 | DESCRIPTION
459 | This procedure calculates number of overdue bills.
460 |
461 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
462 | None
463 |
464 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
465 | None
466 |
467 | PARAMETERS
468 | P_LOAN_ID IN Amount paid for Loan ID
469 |
470 | KNOWN ISSUES
471 | None
472 |
473 | NOTES
474 | Any interesting aspect of the code in the package body which needs
475 | to be stated.
476 |
477 | MODIFICATION HISTORY
478 | Date Author Description of Changes
479 | 01-01-2004 scherkas Created
480 | 01-19-2206 scherkas Obsoleted (use LNS_PAY_SUM_OVERDUE_V instead)
481 |
482 *=======================================================================*/
483 FUNCTION NUMBER_OVERDUE_BILLS(P_LOAN_ID IN NUMBER) RETURN NUMBER
484 IS
485
486 /*-----------------------------------------------------------------------+
487 | Local Variable Declarations and initializations |
488 +-----------------------------------------------------------------------*/
489 l_api_name CONSTANT VARCHAR2(30) := 'NUMBER_OVERDUE_BILLS';
490 l_return NUMBER;
491
492 /*-----------------------------------------------------------------------+
493 | Cursor Declarations |
494 +-----------------------------------------------------------------------*/
495
496 BEGIN
497
498 return 0;
499 END;
500
501
502
503 /*========================================================================
504 | PUBLIC FUNCTION LAST_OVERDUE_DATE
505 |
506 | DESCRIPTION
507 | This procedure calculates last overdue date.
508 |
509 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
510 | None
511 |
512 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
513 | None
514 |
515 | PARAMETERS
516 | P_LOAN_ID IN Loan ID
517 |
518 | KNOWN ISSUES
519 | None
520 |
521 | NOTES
522 | Any interesting aspect of the code in the package body which needs
523 | to be stated.
524 |
525 | MODIFICATION HISTORY
526 | Date Author Description of Changes
527 | 01-01-2004 scherkas Created
528 | 01-19-2206 scherkas Obsoleted (use LNS_PAY_SUM_OVERDUE_V instead)
529 |
530 *=======================================================================*/
531 FUNCTION LAST_OVERDUE_DATE(P_LOAN_ID IN NUMBER) RETURN DATE
532 IS
533
534 /*-----------------------------------------------------------------------+
535 | Local Variable Declarations and initializations |
536 +-----------------------------------------------------------------------*/
537 l_api_name CONSTANT VARCHAR2(30) := 'LAST_OVERDUE_DATE';
538 l_return DATE;
539
540 /*-----------------------------------------------------------------------+
541 | Cursor Declarations |
542 +-----------------------------------------------------------------------*/
543
544 BEGIN
545
546 return to_date(null);
547 END;
548
549
550
551 /*========================================================================
552 | PUBLIC FUNCTION OLDEST_OVERDUE_DATE
553 |
554 | DESCRIPTION
555 | This procedure calculates oldest overdue date.
556 |
557 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
558 | None
559 |
560 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
561 | None
562 |
563 | PARAMETERS
564 | P_LOAN_ID IN Loan ID
565 |
566 | KNOWN ISSUES
567 | None
568 |
569 | NOTES
570 | Any interesting aspect of the code in the package body which needs
571 | to be stated.
572 |
573 | MODIFICATION HISTORY
574 | Date Author Description of Changes
575 | 01-01-2004 scherkas Created
576 | 01-19-2206 scherkas Obsoleted (use LNS_PAY_SUM_OVERDUE_V instead)
577 |
578 *=======================================================================*/
579 FUNCTION OLDEST_OVERDUE_DATE(P_LOAN_ID IN NUMBER) RETURN DATE
580 IS
581
582 /*-----------------------------------------------------------------------+
583 | Local Variable Declarations and initializations |
584 +-----------------------------------------------------------------------*/
585 l_api_name CONSTANT VARCHAR2(30) := 'OLDEST_OVERDUE_DATE';
586 l_return DATE;
587
588 /*-----------------------------------------------------------------------+
589 | Cursor Declarations |
590 +-----------------------------------------------------------------------*/
591
592 BEGIN
593
594 return to_date(null);
595 END;
596
597
598
599 /*========================================================================
600 | PUBLIC FUNCTION LAST_PAYMENT_AMOUNT
601 |
602 | DESCRIPTION
603 | This procedure calculates amount overdue.
604 |
605 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
606 | None
607 |
608 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
609 | None
610 |
611 | PARAMETERS
612 | P_LOAN_ID IN Amount for Loan ID
613 |
614 | KNOWN ISSUES
615 | None
616 |
617 | NOTES
618 | Any interesting aspect of the code in the package body which needs
619 | to be stated.
620 |
621 | MODIFICATION HISTORY
622 | Date Author Description of Changes
623 | 01-01-2004 scherkas Created
624 | 01-19-2206 scherkas Obsoleted
625 |
626 *=======================================================================*/
627 FUNCTION LAST_PAYMENT_AMOUNT(P_LOAN_ID IN NUMBER) RETURN NUMBER
628 IS
629
630 /*-----------------------------------------------------------------------+
631 | Local Variable Declarations and initializations |
632 +-----------------------------------------------------------------------*/
633 l_api_name CONSTANT VARCHAR2(30) := 'LAST_PAYMENT_AMOUNT';
634 l_return NUMBER;
635
636 /*-----------------------------------------------------------------------+
637 | Cursor Declarations |
638 +-----------------------------------------------------------------------*/
639
640 BEGIN
641
642 return 0;
643 END;
644
645
646
647 /*========================================================================
648 | PUBLIC FUNCTION LAST_PAYMENT_DATE
649 |
650 | DESCRIPTION
651 | This procedure gets last payment date.
652 |
653 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
654 | None
655 |
656 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
657 | None
658 |
659 | PARAMETERS
660 | P_LOAN_ID IN Loan ID
661 |
662 | KNOWN ISSUES
663 | None
664 |
665 | NOTES
666 | Any interesting aspect of the code in the package body which needs
667 | to be stated.
668 |
669 | MODIFICATION HISTORY
670 | Date Author Description of Changes
671 | 01-01-2004 scherkas Created
672 | 01-19-2206 scherkas Obsoleted
673 |
674 *=======================================================================*/
675 FUNCTION LAST_PAYMENT_DATE(P_LOAN_ID IN NUMBER) RETURN DATE
676 IS
677
678 /*-----------------------------------------------------------------------+
679 | Local Variable Declarations and initializations |
680 +-----------------------------------------------------------------------*/
681 l_api_name CONSTANT VARCHAR2(30) := 'LAST_PAYMENT_DATE';
682 l_return DATE;
683
684 /*-----------------------------------------------------------------------+
685 | Cursor Declarations |
686 +-----------------------------------------------------------------------*/
687
688 BEGIN
689
690 return to_date(null);
691 END;
692
693
694
695
696 /*========================================================================
697 | PUBLIC FUNCTION BALANCE_BY_ACT_DATE
698 |
699 | DESCRIPTION
700 | This procedure returns loan balance by date.
701 |
702 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
703 | None
704 |
705 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
706 | None
707 |
708 | PARAMETERS
709 | P_LOAN_ID IN Loan ID
710 | P_DATE IN Date
711 |
712 | KNOWN ISSUES
713 | None
714 |
715 | NOTES
716 | Any interesting aspect of the code in the package body which needs
717 | to be stated.
718 |
719 | MODIFICATION HISTORY
720 | Date Author Description of Changes
721 | 02-01-2006 scherkas Created
722 |
723 *=======================================================================*/
724 FUNCTION LOAN_BALANCE_BY_DATE(P_LOAN_ID IN NUMBER, P_DATE IN DATE) RETURN NUMBER
725 IS
726
727 /*-----------------------------------------------------------------------+
728 | Local Variable Declarations and initializations |
729 +-----------------------------------------------------------------------*/
730 l_api_name CONSTANT VARCHAR2(30) := 'LOAN_BALANCE_BY_DATE';
731 l_return NUMBER;
732
733 /*-----------------------------------------------------------------------+
734 | Cursor Declarations |
735 +-----------------------------------------------------------------------*/
736
737 CURSOR get_data_crs(P_LOAN_ID NUMBER, P_DATE DATE) IS
738 select loan.funded_amount -
739 (select nvl(sum(rec.amount_applied), 0)
740 from
741 ar_receivable_applications_all rec,
742 LNS_AMORTIZATION_SCHEDS am
743 where
744 rec.application_type = 'CASH' and
745 trunc(rec.apply_date) <= trunc(P_DATE) and
746 rec.APPLIED_CUSTOMER_TRX_ID = am.principal_trx_id and
747 rec.org_id = loan.org_id and
748 am.loan_id = loan.loan_id and
749 am.principal_trx_id is not null and
750 nvl(am.PHASE, 'TERM') = nvl(loan.CURRENT_PHASE, 'TERM'))
751 from lns_loan_headers_all loan
752 where loan.loan_id = P_LOAN_ID;
753
754 BEGIN
755 OPEN get_data_crs(P_LOAN_ID, P_DATE);
756 FETCH get_data_crs INTO l_return;
757 CLOSE get_data_crs;
758
759 return l_return;
760 END;
761
762
763 END LNS_BILLING_UTIL_PUB; -- Package body
[Home] [Help]