[Home] [Help]
PACKAGE BODY: APPS.LNS_BILLING_UTIL_PUB
Source
1 PACKAGE BODY LNS_BILLING_UTIL_PUB AS
2 /* $Header: LNS_BILL_UTIL_B.pls 120.13.12020000.3 2013/02/11 14:52:20 scherkas ship $ */
3
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7
8 G_PKG_NAME CONSTANT VARCHAR2(30):= 'LNS_BILLING_UTIL_PUB';
9 G_LOG_ENABLED varchar2(5);
10 G_MSG_LEVEL NUMBER;
11
12
13 Procedure LogMessage(p_msg_level IN NUMBER, p_msg in varchar2)
14 IS
15 BEGIN
16
17 if (p_msg_level >= G_MSG_LEVEL) then
18 FND_LOG.STRING(p_msg_level, G_PKG_NAME, p_msg);
19 end if;
20
21 if FND_GLOBAL.Conc_Request_Id is not null then
22 fnd_file.put_line(FND_FILE.LOG, p_msg);
23 end if;
24
25 EXCEPTION
26 WHEN OTHERS THEN
27 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
28 END;
29
30
31
32 /*========================================================================
33 | PUBLIC FUNCTION LAST_PAYMENT_NUMBER
34 |
35 | DESCRIPTION
36 | This procedure calculates last payment number.
37 |
38 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
39 | None
40 |
41 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
42 | None
43 |
44 | PARAMETERS
45 | P_LOAN_ID IN Last payment number for Loan ID
46 |
47 | KNOWN ISSUES
48 | None
49 |
50 | NOTES
51 | Any interesting aspect of the code in the package body which needs
52 | to be stated.
53 |
54 | MODIFICATION HISTORY
55 | Date Author Description of Changes
56 | 01-01-2004 scherkas Created
57 |
58 *=======================================================================*/
59 FUNCTION LAST_PAYMENT_NUMBER(P_LOAN_ID IN NUMBER) RETURN NUMBER
60 IS
61
62 /*-----------------------------------------------------------------------+
63 | Local Variable Declarations and initializations |
64 +-----------------------------------------------------------------------*/
65 l_api_name CONSTANT VARCHAR2(30) := 'LAST_PAYMENT_NUMBER';
66 l_return NUMBER;
67
68 /*-----------------------------------------------------------------------+
69 | Cursor Declarations |
70 +-----------------------------------------------------------------------*/
71 /*
72 CURSOR get_data_crs(P_LOAN_ID NUMBER) IS
73 select nvl(max(am.PAYMENT_NUMBER), 0)
74 from LNS_AMORTIZATION_SCHEDS am,
75 lns_loan_headers head
76 where am.LOAN_ID = P_LOAN_ID and
77 am.LOAN_ID = head.LOAN_ID
78 and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
79 and am.PARENT_AMORTIZATION_ID is null
80 and nvl(am.PHASE, 'TERM') = nvl(head.CURRENT_PHASE, 'TERM');
81 */
82 BEGIN
83 /*
84 OPEN get_data_crs(P_LOAN_ID);
85 FETCH get_data_crs INTO l_return;
86 CLOSE get_data_crs;
87 */
88 return LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(P_LOAN_ID, null);
89 END;
90
91
92
93 /*========================================================================
94 | PUBLIC FUNCTION LAST_PAYMENT_NUMBER
95 |
96 | DESCRIPTION
97 | This procedure calculates last payment number.
98 |
99 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
100 | None
101 |
102 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
103 | None
104 |
105 | PARAMETERS
106 | P_LOAN_ID IN Last payment number for Loan ID
107 | P_PHASE IN Phase
108 |
109 | KNOWN ISSUES
110 | None
111 |
112 | NOTES
113 | Any interesting aspect of the code in the package body which needs
114 | to be stated.
115 |
116 | MODIFICATION HISTORY
117 | Date Author Description of Changes
118 | 01-01-2004 scherkas Created
119 |
120 *=======================================================================*/
121 FUNCTION LAST_PAYMENT_NUMBER(P_LOAN_ID IN NUMBER, P_PHASE IN VARCHAR2) RETURN NUMBER
122 IS
123
124 /*-----------------------------------------------------------------------+
125 | Local Variable Declarations and initializations |
126 +-----------------------------------------------------------------------*/
127 l_api_name CONSTANT VARCHAR2(30) := 'LAST_PAYMENT_NUMBER';
128 l_return NUMBER;
129
130 /*-----------------------------------------------------------------------+
131 | Cursor Declarations |
132 +-----------------------------------------------------------------------*/
133
134 CURSOR get_data_crs(P_LOAN_ID NUMBER, P_PHASE VARCHAR2) IS
135 select nvl(max(am.PAYMENT_NUMBER), 0)
136 from LNS_AMORTIZATION_SCHEDS am,
137 lns_loan_headers head
138 where am.LOAN_ID = P_LOAN_ID
139 and am.LOAN_ID = head.LOAN_ID
140 and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
141 and am.PARENT_AMORTIZATION_ID is null
142 and am.REAMORTIZATION_AMOUNT is null
143 and nvl(am.PHASE, 'TERM') = nvl(P_PHASE, nvl(head.CURRENT_PHASE, 'TERM'));
144
145 BEGIN
146 OPEN get_data_crs(P_LOAN_ID, P_PHASE);
147 FETCH get_data_crs INTO l_return;
148 CLOSE get_data_crs;
149
150 return l_return;
151 END;
152
153
154
155 /*========================================================================
156 | PUBLIC FUNCTION LAST_PAYMENT_NUMBER_EXT
157 |
158 | DESCRIPTION
159 | This procedure calculates last payment number taking in consideration 0-th installment.
160 | If any installment is billed ('0'/'1'/'2'/.....) then it returns the installment ELSE
161 | it returns '-1'.
162 |
163 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
164 | None
165 |
166 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
167 | None
168 |
169 | PARAMETERS
170 | P_LOAN_ID IN Last payment number for Loan ID
171 |
172 | KNOWN ISSUES
173 | None
174 |
175 | NOTES
176 | Any interesting aspect of the code in the package body which needs
177 | to be stated.
178 |
179 | MODIFICATION HISTORY
180 | Date Author Description of Changes
181 | 01-01-2004 scherkas Created
182 | 18-Aug-2009 Mbolli Bug#6830765 - Modified with old functionality
183 *=======================================================================*/
184 FUNCTION LAST_PAYMENT_NUMBER_EXT(P_LOAN_ID IN NUMBER) RETURN NUMBER
185 IS
186
187 /*-----------------------------------------------------------------------+
188 | Local Variable Declarations and initializations |
189 +-----------------------------------------------------------------------*/
190 l_api_name CONSTANT VARCHAR2(30) := 'LAST_PAYMENT_NUMBER_EXT';
191 l_return NUMBER;
192
193 /*-----------------------------------------------------------------------+
194 | Cursor Declarations |
195 +-----------------------------------------------------------------------*/
196
197 CURSOR get_data_crs(P_LOAN_ID NUMBER) IS
198 select nvl(max(am.PAYMENT_NUMBER), -1)
199 from LNS_AMORTIZATION_SCHEDS am,
200 lns_loan_headers head
201 where am.LOAN_ID = P_LOAN_ID and
202 am.LOAN_ID = head.LOAN_ID
203 and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
204 -- and am.PARENT_AMORTIZATION_ID is null
205 and am.REAMORTIZATION_AMOUNT is null -- fix for bug 7422383
206 and nvl(am.PHASE, 'TERM') = nvl(head.CURRENT_PHASE, 'TERM');
207
208 BEGIN
209 OPEN get_data_crs(P_LOAN_ID);
210 FETCH get_data_crs INTO l_return;
211 CLOSE get_data_crs;
212
213 return l_return;
214 END;
215
216
217 /*========================================================================
218 | PUBLIC FUNCTION LAST_PAYMENT_NUMBER_EXT_1
219 |
220 | DESCRIPTION
221 | This procedure calculates last payment number taking in consideration 0-th installment.
222 | If any installment is billed ('0'/'1'/'2'/.....) then it returns the installment ELSE
223 | if any '0th' installments are scheduled(On Activation/On SubmitFor Approval Fees)
224 | then returns '0' ELSE '-1'
225 |
226 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
227 | None
228 |
229 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
230 | None
231 |
232 | PARAMETERS
233 | P_LOAN_ID IN Last payment number for Loan ID
234 |
235 | KNOWN ISSUES
236 | None
237 |
238 | NOTES
239 | Any interesting aspect of the code in the package body which needs
240 | to be stated.
241 |
242 | MODIFICATION HISTORY
243 | Date Author Description of Changes
244 | 01-01-2004 scherkas Created
245 | 18-Aug-2009 Mbolli Bug#6830765 - Created
246 *=======================================================================*/
247 FUNCTION LAST_PAYMENT_NUMBER_EXT_1(P_LOAN_ID IN NUMBER) RETURN NUMBER
248 IS
249
250 /*-----------------------------------------------------------------------+
251 | Local Variable Declarations and initializations |
252 +-----------------------------------------------------------------------*/
253 l_api_name CONSTANT VARCHAR2(30) := 'LAST_PAYMENT_NUMBER_EXT_1';
254 l_return NUMBER;
255 l_zero_inst_count NUMBER;
256
257 /*-----------------------------------------------------------------------+
258 | Cursor Declarations |
259 +-----------------------------------------------------------------------*/
260
261 CURSOR get_data_crs(P_LOAN_ID NUMBER) IS
262 select nvl(max(am.PAYMENT_NUMBER), -1)
263 from LNS_AMORTIZATION_SCHEDS am,
264 lns_loan_headers head
265 where am.LOAN_ID = P_LOAN_ID and
266 am.LOAN_ID = head.LOAN_ID
267 and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
268 -- and am.PARENT_AMORTIZATION_ID is null
269 and am.REAMORTIZATION_AMOUNT is null -- fix for bug 7422383
270 and nvl(am.PHASE, 'TERM') = nvl(head.CURRENT_PHASE, 'TERM');
271
272 BEGIN
273 OPEN get_data_crs(P_LOAN_ID);
274 FETCH get_data_crs INTO l_return;
275 CLOSE get_data_crs;
276
277 IF l_return = -1 THEN
278
279 l_zero_inst_count := 0;
280
281 SELECT nvl(count(1),0)
282 INTO l_zero_inst_count
283 FROM lns_fee_schedules schd
284 ,lns_fees_all struct
285 ,lns_loan_headers_all loan
286 WHERE loan.loan_id = P_LOAN_ID
287 AND schd.loan_id = loan.loan_id
288 AND schd.fee_id = struct.fee_id
289 AND struct.fee_type = 'EVENT_ORIGINATION'
290 AND schd.fee_installment = 0
291 AND schd.active_flag = 'Y'
292 AND schd.phase = nvl(loan.CURRENT_PHASE, 'TERM');
293
294 IF l_zero_inst_count <= 0 THEN
295 l_return := 0;
296 END IF;
297
298 END IF;
299
300 return l_return;
301 END;
302
303
304 /*========================================================================
305 | PUBLIC FUNCTION LAST_PAYMENT_NUMBER_EXT_2
306 |
307 | DESCRIPTION
308 | This procedure calculates last payment number and it doesn't consdier '0th' installments
309 | and it returns '-1'.
310 | We created this to use for PREBILL_SINGLE_LOAN in LNS_BILLING_BATCH_PUB package
311 |
312 | PSEUDO CODE/LOGIC
313 |
314 | PARAMETERS
315 | P_LOAN_ID IN Last payment number for Loan ID
316 |
317 | KNOWN ISSUES
318 | None
319 |
320 | NOTES
321 |
322 | MODIFICATION HISTORY
323 | Date Author Description of Changes
324 | 18-Aug-2009 Mbolli Created
325 |
326 *=======================================================================*/
327 FUNCTION LAST_PAYMENT_NUMBER_EXT_2(P_LOAN_ID IN NUMBER) RETURN NUMBER
328 IS
329
330 /*-----------------------------------------------------------------------+
331 | Local Variable Declarations and initializations |
332 +-----------------------------------------------------------------------*/
333 l_api_name CONSTANT VARCHAR2(30) := 'LAST_PAYMENT_NUMBER_EXT';
334 l_return NUMBER;
335
336 /*-----------------------------------------------------------------------+
337 | Cursor Declarations |
338 +-----------------------------------------------------------------------*/
339
340 CURSOR get_data_crs(P_LOAN_ID NUMBER) IS
341 select nvl(max(am.PAYMENT_NUMBER), -1)
342 from LNS_AMORTIZATION_SCHEDS am,
343 lns_loan_headers head
344 where am.LOAN_ID = P_LOAN_ID and
345 am.LOAN_ID = head.LOAN_ID
346 and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
347 and am.PARENT_AMORTIZATION_ID is null
348 and am.REAMORTIZATION_AMOUNT is null -- fix for bug 7422383
349 and nvl(am.PHASE, 'TERM') = nvl(head.CURRENT_PHASE, 'TERM');
350
351 BEGIN
352 OPEN get_data_crs(P_LOAN_ID);
353 FETCH get_data_crs INTO l_return;
354 CLOSE get_data_crs;
355
356 return l_return;
357 END;
358
359
360 FUNCTION LAST_PAYMENT_NUMBER_EXT_3(P_LOAN_ID IN NUMBER, P_PHASE IN VARCHAR2) RETURN NUMBER
361 IS
362
363 /*-----------------------------------------------------------------------+
364 | Local Variable Declarations and initializations |
365 +-----------------------------------------------------------------------*/
366 l_api_name CONSTANT VARCHAR2(30) := 'LAST_PAYMENT_NUMBER_EXT_3';
367 l_return NUMBER;
368
369 /*-----------------------------------------------------------------------+
370 | Cursor Declarations |
371 +-----------------------------------------------------------------------*/
372
373 CURSOR get_data_crs(P_LOAN_ID NUMBER, P_PHASE VARCHAR2) IS
374 select nvl(max(PAYMENT_NUMBER), -1)
375 from LNS_AMORTIZATION_SCHEDS
376 where LOAN_ID = P_LOAN_ID
377 and (REVERSED_FLAG is null or REVERSED_FLAG = 'N')
378 and REAMORTIZATION_AMOUNT is null
379 and nvl(PHASE, 'TERM') = nvl(P_PHASE, 'TERM');
380
381 BEGIN
382 OPEN get_data_crs(P_LOAN_ID, P_PHASE);
383 FETCH get_data_crs INTO l_return;
384 CLOSE get_data_crs;
385
386 return l_return;
387 END;
388
389
390 /*========================================================================
391 | PUBLIC FUNCTION LAST_AMORTIZATION_SCHED
392 |
393 | DESCRIPTION
394 | This procedure returns last amortization schedule id.
395 |
396 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
397 | None
398 |
399 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
400 | None
401 |
402 | PARAMETERS
403 | P_LOAN_ID IN Last payment number for Loan ID
404 |
405 | KNOWN ISSUES
406 | None
407 |
408 | NOTES
409 | Any interesting aspect of the code in the package body which needs
410 | to be stated.
411 |
412 | MODIFICATION HISTORY
413 | Date Author Description of Changes
414 | 01-01-2004 scherkas Created
415 |
416 *=======================================================================*/
417 FUNCTION LAST_AMORTIZATION_SCHED(P_LOAN_ID IN NUMBER) RETURN NUMBER
418 IS
419
420 /*-----------------------------------------------------------------------+
421 | Local Variable Declarations and initializations |
422 +-----------------------------------------------------------------------*/
423 l_api_name CONSTANT VARCHAR2(30) := 'LAST_AMORTIZATION_SCHED';
424 l_return NUMBER;
425
426 /*-----------------------------------------------------------------------+
427 | Cursor Declarations |
428 +-----------------------------------------------------------------------*/
429
430 CURSOR get_data_crs(P_LOAN_ID NUMBER) IS
431 select nvl(max(am.AMORTIZATION_SCHEDULE_ID), -1)
432 from LNS_AMORTIZATION_SCHEDS am,
433 lns_loan_headers head
434 where am.LOAN_ID = P_LOAN_ID
435 and am.LOAN_ID = head.LOAN_ID
436 and am.PAYMENT_NUMBER = LAST_PAYMENT_NUMBER(am.LOAN_ID)
437 and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
438 and am.PARENT_AMORTIZATION_ID is null
439 and nvl(am.PHASE, 'TERM') = nvl(head.CURRENT_PHASE, 'TERM');
440
441 BEGIN
442 OPEN get_data_crs(P_LOAN_ID);
443 FETCH get_data_crs INTO l_return;
444 CLOSE get_data_crs;
445
446 return l_return;
447 END;
448
449
450
451 /*========================================================================
452 | PUBLIC FUNCTION NEXT_PAYMENT_DUE
453 |
454 | DESCRIPTION
455 | This procedure calculates next payment due.
456 |
457 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
458 | None
459 |
460 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
461 | None
462 |
463 | PARAMETERS
464 | P_LOAN_ID IN Last payment number for Loan ID
465 | P_LINE_TYPE Amount due for this line type
466 | P_PAYMENT_NUMBER Amount due for this payment number
467 |
468 | KNOWN ISSUES
469 | None
470 |
471 | NOTES
472 | Any interesting aspect of the code in the package body which needs
473 | to be stated.
474 |
475 | MODIFICATION HISTORY
476 | Date Author Description of Changes
477 | 01-01-2004 scherkas Created
478 | 01-19-2206 scherkas Obsoleted (use LNS_PAY_SUM_V instead)
479 |
480 *=======================================================================*/
481 FUNCTION NEXT_PAYMENT_DUE(P_LOAN_ID IN NUMBER, P_LINE_TYPE IN VARCHAR2, P_PAYMENT_NUMBER IN NUMBER) RETURN NUMBER
482 IS
483
484 /*-----------------------------------------------------------------------+
485 | Local Variable Declarations and initializations |
486 +-----------------------------------------------------------------------*/
487 l_api_name CONSTANT VARCHAR2(30) := 'NEXT_PAYMENT_DUE';
488 l_return NUMBER;
489
490 /*-----------------------------------------------------------------------+
491 | Cursor Declarations |
492 +-----------------------------------------------------------------------*/
493
494 BEGIN
495
496 return 0;
497 END;
498
499
500
501
502 /*========================================================================
503 | PUBLIC FUNCTION AMOUNT_PAID_YTD
504 |
505 | DESCRIPTION
506 | This procedure calculates amount paid YTD.
507 |
508 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
509 | None
510 |
511 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
512 | None
513 |
514 | PARAMETERS
515 | P_LOAN_ID IN Amount paid for Loan ID
516 | P_LINE_TYPE IN Amount paid for this line type
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_V instead)
529 |
530 *=======================================================================*/
531 FUNCTION AMOUNT_PAID_YTD(P_LOAN_ID IN NUMBER, P_LINE_TYPE VARCHAR2) RETURN NUMBER
532 IS
533
534 /*-----------------------------------------------------------------------+
535 | Local Variable Declarations and initializations |
536 +-----------------------------------------------------------------------*/
537 l_api_name CONSTANT VARCHAR2(30) := 'AMOUNT_PAID_YTD';
538 l_return NUMBER;
539
540 /*-----------------------------------------------------------------------+
541 | Cursor Declarations |
542 +-----------------------------------------------------------------------*/
543
544 BEGIN
545
546 return 0;
547 END;
548
549
550
551 /*========================================================================
552 | PUBLIC FUNCTION AMOUNT_PAID_LAST_YEAR
553 |
554 | DESCRIPTION
555 | This procedure calculates amount paid last year.
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 Amount paid for Loan ID
565 | P_LINE_TYPE IN Amount paid for this line type
566 |
567 | KNOWN ISSUES
568 | None
569 |
570 | NOTES
571 | Any interesting aspect of the code in the package body which needs
572 | to be stated.
573 |
574 | MODIFICATION HISTORY
575 | Date Author Description of Changes
576 | 01-01-2004 scherkas Created
577 | 01-19-2206 scherkas Obsoleted (use LNS_PAY_SUM_YEARLY_V instead)
578 |
579 *=======================================================================*/
580 FUNCTION AMOUNT_PAID_LAST_YEAR(P_LOAN_ID IN NUMBER, P_LINE_TYPE VARCHAR2) RETURN NUMBER
581 IS
582
583 /*-----------------------------------------------------------------------+
584 | Local Variable Declarations and initializations |
585 +-----------------------------------------------------------------------*/
586 l_api_name CONSTANT VARCHAR2(30) := 'AMOUNT_PAID_LAST_YEAR';
587 l_return NUMBER;
588
589 /*-----------------------------------------------------------------------+
590 | Cursor Declarations |
591 +-----------------------------------------------------------------------*/
592
593 BEGIN
594
595 return 0;
596 END;
597
598
599
600 /*========================================================================
601 | PUBLIC FUNCTION AMOUNT_OVERDUE
602 |
603 | DESCRIPTION
604 | This procedure calculates amount overdue.
605 |
606 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
607 | None
608 |
609 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
610 | None
611 |
612 | PARAMETERS
613 | P_LOAN_ID IN Amount paid for Loan ID
614 | P_LINE_TYPE IN Amount paid for this line type
615 |
616 | KNOWN ISSUES
617 | None
618 |
619 | NOTES
620 | Any interesting aspect of the code in the package body which needs
621 | to be stated.
622 |
623 | MODIFICATION HISTORY
624 | Date Author Description of Changes
625 | 01-01-2004 scherkas Created
626 | 01-19-2206 scherkas Obsoleted (use LNS_PAY_SUM_OVERDUE_V instead)
627 |
628 *=======================================================================*/
629 FUNCTION AMOUNT_OVERDUE(P_LOAN_ID IN NUMBER, P_LINE_TYPE VARCHAR2) RETURN NUMBER
630 IS
631
632 /*-----------------------------------------------------------------------+
633 | Local Variable Declarations and initializations |
634 +-----------------------------------------------------------------------*/
635 l_api_name CONSTANT VARCHAR2(30) := 'AMOUNT_OVERDUE';
636 l_return NUMBER;
637
638 /*-----------------------------------------------------------------------+
639 | Cursor Declarations |
640 +-----------------------------------------------------------------------*/
641
642 BEGIN
643
644 return 0;
645 END;
646
647
648
649 /*========================================================================
650 | PUBLIC FUNCTION NUMBER_OVERDUE_BILLS
651 |
652 | DESCRIPTION
653 | This procedure calculates number of overdue bills.
654 |
655 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
656 | None
657 |
658 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
659 | None
660 |
661 | PARAMETERS
662 | P_LOAN_ID IN Amount paid for Loan ID
663 |
664 | KNOWN ISSUES
665 | None
666 |
667 | NOTES
668 | Any interesting aspect of the code in the package body which needs
669 | to be stated.
670 |
671 | MODIFICATION HISTORY
672 | Date Author Description of Changes
673 | 01-01-2004 scherkas Created
674 | 01-19-2206 scherkas Obsoleted (use LNS_PAY_SUM_OVERDUE_V instead)
675 |
676 *=======================================================================*/
677 FUNCTION NUMBER_OVERDUE_BILLS(P_LOAN_ID IN NUMBER) RETURN NUMBER
678 IS
679
680 /*-----------------------------------------------------------------------+
681 | Local Variable Declarations and initializations |
682 +-----------------------------------------------------------------------*/
683 l_api_name CONSTANT VARCHAR2(30) := 'NUMBER_OVERDUE_BILLS';
684 l_return NUMBER;
685
686 /*-----------------------------------------------------------------------+
687 | Cursor Declarations |
688 +-----------------------------------------------------------------------*/
689
690 BEGIN
691
692 return 0;
693 END;
694
695
696
697 /*========================================================================
698 | PUBLIC FUNCTION LAST_OVERDUE_DATE
699 |
700 | DESCRIPTION
701 | This procedure calculates last overdue date.
702 |
703 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
704 | None
705 |
706 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
707 | None
708 |
709 | PARAMETERS
710 | P_LOAN_ID IN Loan ID
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 | 01-01-2004 scherkas Created
722 | 01-19-2206 scherkas Obsoleted (use LNS_PAY_SUM_OVERDUE_V instead)
723 |
724 *=======================================================================*/
725 FUNCTION LAST_OVERDUE_DATE(P_LOAN_ID IN NUMBER) RETURN DATE
726 IS
727
728 /*-----------------------------------------------------------------------+
729 | Local Variable Declarations and initializations |
730 +-----------------------------------------------------------------------*/
731 l_api_name CONSTANT VARCHAR2(30) := 'LAST_OVERDUE_DATE';
732 l_return DATE;
733
734 /*-----------------------------------------------------------------------+
735 | Cursor Declarations |
736 +-----------------------------------------------------------------------*/
737
738 BEGIN
739
740 return to_date(null);
741 END;
742
743
744
745 /*========================================================================
746 | PUBLIC FUNCTION OLDEST_OVERDUE_DATE
747 |
748 | DESCRIPTION
749 | This procedure calculates oldest overdue date.
750 |
751 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
752 | None
753 |
754 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
755 | None
756 |
757 | PARAMETERS
758 | P_LOAN_ID IN Loan ID
759 |
760 | KNOWN ISSUES
761 | None
762 |
763 | NOTES
764 | Any interesting aspect of the code in the package body which needs
765 | to be stated.
766 |
767 | MODIFICATION HISTORY
768 | Date Author Description of Changes
769 | 01-01-2004 scherkas Created
770 | 01-19-2206 scherkas Obsoleted (use LNS_PAY_SUM_OVERDUE_V instead)
771 |
772 *=======================================================================*/
773 FUNCTION OLDEST_OVERDUE_DATE(P_LOAN_ID IN NUMBER) RETURN DATE
774 IS
775
776 /*-----------------------------------------------------------------------+
777 | Local Variable Declarations and initializations |
778 +-----------------------------------------------------------------------*/
779 l_api_name CONSTANT VARCHAR2(30) := 'OLDEST_OVERDUE_DATE';
780 l_return DATE;
781
782 /*-----------------------------------------------------------------------+
783 | Cursor Declarations |
784 +-----------------------------------------------------------------------*/
785
786 BEGIN
787
788 return to_date(null);
789 END;
790
791
792
793 /*========================================================================
794 | PUBLIC FUNCTION LAST_PAYMENT_AMOUNT
795 |
796 | DESCRIPTION
797 | This procedure calculates amount overdue.
798 |
799 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
800 | None
801 |
802 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
803 | None
804 |
805 | PARAMETERS
806 | P_LOAN_ID IN Amount for Loan ID
807 |
808 | KNOWN ISSUES
809 | None
810 |
811 | NOTES
812 | Any interesting aspect of the code in the package body which needs
813 | to be stated.
814 |
815 | MODIFICATION HISTORY
816 | Date Author Description of Changes
817 | 01-01-2004 scherkas Created
818 | 01-19-2206 scherkas Obsoleted
819 |
820 *=======================================================================*/
821 FUNCTION LAST_PAYMENT_AMOUNT(P_LOAN_ID IN NUMBER) RETURN NUMBER
822 IS
823
824 /*-----------------------------------------------------------------------+
825 | Local Variable Declarations and initializations |
826 +-----------------------------------------------------------------------*/
827 l_api_name CONSTANT VARCHAR2(30) := 'LAST_PAYMENT_AMOUNT';
828 l_return NUMBER;
829
830 /*-----------------------------------------------------------------------+
831 | Cursor Declarations |
832 +-----------------------------------------------------------------------*/
833
834 BEGIN
835
836 return 0;
837 END;
838
839
840
841 /*========================================================================
842 | PUBLIC FUNCTION LAST_PAYMENT_DATE
843 |
844 | DESCRIPTION
845 | This procedure gets last payment date.
846 |
847 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
848 | None
849 |
850 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
851 | None
852 |
853 | PARAMETERS
854 | P_LOAN_ID IN Loan ID
855 |
856 | KNOWN ISSUES
857 | None
858 |
859 | NOTES
860 | Any interesting aspect of the code in the package body which needs
861 | to be stated.
862 |
863 | MODIFICATION HISTORY
864 | Date Author Description of Changes
865 | 01-01-2004 scherkas Created
866 | 01-19-2206 scherkas Obsoleted
867 |
868 *=======================================================================*/
869 FUNCTION LAST_PAYMENT_DATE(P_LOAN_ID IN NUMBER) RETURN DATE
870 IS
871
872 /*-----------------------------------------------------------------------+
873 | Local Variable Declarations and initializations |
874 +-----------------------------------------------------------------------*/
875 l_api_name CONSTANT VARCHAR2(30) := 'LAST_PAYMENT_DATE';
876 l_return DATE;
877
878 /*-----------------------------------------------------------------------+
879 | Cursor Declarations |
880 +-----------------------------------------------------------------------*/
881
882 BEGIN
883
884 return LNS_BILLING_UTIL_PUB.LAST_PAYMENT_DATE(P_LOAN_ID, null);
885
886 END;
887
888
889 FUNCTION LAST_PAYMENT_DATE(P_LOAN_ID IN NUMBER, P_PHASE IN VARCHAR2) RETURN DATE
890 IS
891
892 /*-----------------------------------------------------------------------+
893 | Local Variable Declarations and initializations |
894 +-----------------------------------------------------------------------*/
895 l_api_name CONSTANT VARCHAR2(30) := 'LAST_PAYMENT_DATE';
896 l_return DATE;
897
898 /*-----------------------------------------------------------------------+
899 | Cursor Declarations |
900 +-----------------------------------------------------------------------*/
901
902 CURSOR get_data_crs(P_LOAN_ID NUMBER, P_PHASE VARCHAR2) IS
903 select trunc(max(DUE_DATE))
904 from LNS_AMORTIZATION_SCHEDS am,
905 lns_loan_headers head
906 where am.LOAN_ID = P_LOAN_ID
907 and am.LOAN_ID = head.LOAN_ID
908 and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
909 and am.PARENT_AMORTIZATION_ID is null
910 and am.REAMORTIZATION_AMOUNT is null
911 and nvl(am.PHASE, 'TERM') = nvl(P_PHASE, nvl(head.CURRENT_PHASE, 'TERM'));
912
913 BEGIN
914
915 OPEN get_data_crs(P_LOAN_ID, P_PHASE);
916 FETCH get_data_crs INTO l_return;
917 CLOSE get_data_crs;
918
919 return l_return;
920
921 END;
922
923
924 /*========================================================================
925 | PUBLIC FUNCTION BALANCE_BY_ACT_DATE
926 |
927 | DESCRIPTION
928 | This procedure returns loan balance by date.
929 |
930 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
931 | None
932 |
933 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
934 | None
935 |
936 | PARAMETERS
937 | P_LOAN_ID IN Loan ID
938 | P_DATE IN Date
939 |
940 | KNOWN ISSUES
941 | None
942 |
943 | NOTES
944 | Any interesting aspect of the code in the package body which needs
945 | to be stated.
946 |
947 | MODIFICATION HISTORY
948 | Date Author Description of Changes
949 | 02-01-2006 scherkas Created
950 |
951 *=======================================================================*/
952 FUNCTION LOAN_BALANCE_BY_DATE(P_LOAN_ID IN NUMBER, P_DATE IN DATE) RETURN NUMBER
953 IS
954
955 /*-----------------------------------------------------------------------+
956 | Local Variable Declarations and initializations |
957 +-----------------------------------------------------------------------*/
958 l_api_name CONSTANT VARCHAR2(30) := 'LOAN_BALANCE_BY_DATE';
959 l_return NUMBER;
960
961 /*-----------------------------------------------------------------------+
962 | Cursor Declarations |
963 +-----------------------------------------------------------------------*/
964
965 CURSOR get_data_crs(P_LOAN_ID NUMBER, P_DATE DATE) IS
966 select loan.funded_amount -
967 (select nvl(sum(rec.amount_applied), 0)
968 from
969 ar_receivable_applications_all rec,
970 LNS_AMORTIZATION_SCHEDS am
971 where
972 rec.application_type = 'CASH' and
973 trunc(rec.apply_date) <= trunc(P_DATE) and
974 rec.APPLIED_CUSTOMER_TRX_ID = am.principal_trx_id and
975 rec.org_id = loan.org_id and
976 am.loan_id = loan.loan_id and
977 am.principal_trx_id is not null and
978 nvl(am.PHASE, 'TERM') = nvl(loan.CURRENT_PHASE, 'TERM'))
979 from lns_loan_headers_all loan
980 where loan.loan_id = P_LOAN_ID;
981
982 BEGIN
983 OPEN get_data_crs(P_LOAN_ID, P_DATE);
984 FETCH get_data_crs INTO l_return;
985 CLOSE get_data_crs;
986
987 return l_return;
988 END;
989
990
991 /*========================================================================
992 | PUBLIC FUNCTION GET_LOAN_REMAIN_AMOUNT
993 |
994 | DESCRIPTION
995 | This function returns funded_amount for the loans in staus
996 | ('INCOMPLETE','REJECTED','DELETED','PENDING','APPROVED','IN_FUNDING','FUNDING_ERROR')
997 | and remaining amount for the other loan statuses.
998 |
999 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1000 | None
1001 |
1002 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1003 | LogMessage
1004 |
1005 | PARAMETERS
1006 | P_LOAN_ID IN Loan ID
1007 |
1008 | KNOWN ISSUES
1009 | None
1010 |
1011 | NOTES
1012 | Any interesting aspect of the code in the package body which needs
1013 | to be stated.
1014 |
1015 | MODIFICATION HISTORY
1016 | Date Author Description of Changes
1017 | 22-APR-2009 MBOLLI Created for bug#8545962
1018 |
1019 *=======================================================================*/
1020 FUNCTION GET_LOAN_REMAIN_AMOUNT(P_LOAN_ID IN NUMBER) return NUMBER
1021 IS
1022
1023 /*-----------------------------------------------------------------------+
1024 | Local Variable Declarations and initializations |
1025 +-----------------------------------------------------------------------*/
1026
1027 l_api_name CONSTANT VARCHAR2(30) := 'GET_LOAN_REMAIN_AMOUNT';
1028 l_remain_amt NUMBER;
1029
1030
1031 /*-----------------------------------------------------------------------+
1032 | Cursor Declarations |
1033 +-----------------------------------------------------------------------*/
1034
1035 CURSOR c_remain_amt(P_LOAN_ID NUMBER) IS
1036 select TOTAL_PRINCIPAL_BALANCE
1037 from LNS_PAY_SUM_V
1038 where loan_id = P_LOAN_ID;
1039 /*
1040 SELECT
1041 (CASE WHEN LnsLoanHeaderEO.loan_status in ('INCOMPLETE','REJECTED','DELETED','PENDING','APPROVED','IN_FUNDING','FUNDING_ERROR','CANCELLED') OR LnsLoanHeaderEO.FUNDED_AMOUNT = 0 THEN LnsLoanHeaderEO.requested_amount
1042 ELSE
1043 LnsLoanHeaderEO.FUNDED_AMOUNT -
1044 (select nvl(SUM(abs(nvl(psa_prin.AMOUNT_APPLIED, 0))) - SUM(nvl(psa_prin.AMOUNT_ADJUSTED, 0)) + SUM(abs(nvl(psa_prin.AMOUNT_CREDITED, 0))), 0)
1045 from
1046 LNS_AMORTIZATION_SCHEDS am
1047 ,ar_payment_schedules_all psa_prin
1048 where
1049 am.loan_id = LnsLoanHeaderEO.loan_id
1050 and am.PHASE = LnsLoanHeaderEO.CURRENT_PHASE
1051 and am.PAYMENT_NUMBER <= LnsLoanHeaderEO.LAST_PAYMENT_NUMBER
1052 and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')
1053 and am.REAMORTIZATION_AMOUNT is null
1054 and psa_prin.customer_trx_id = am.principal_trx_id)
1055 END)
1056 FROM LNS_LOAN_HEADERS_ALL LnsLoanHeaderEO
1057 WHERE loan_id = P_LOAN_ID;
1058 */
1059
1060 BEGIN
1061
1062 -- LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1063
1064 -- START OF BODY OF API
1065 --LogMessage(FND_LOG.LEVEL_STATEMENT, 'Input loan_id = ' || P_LOAN_ID);
1066
1067
1068 /* verify input parameters */
1069 if P_LOAN_ID is null then
1070
1071 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: LoanId must be set.');
1072 FND_MESSAGE.SET_NAME('LNS', 'LNS_NO_LOAN');
1073 FND_MSG_PUB.Add;
1074 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
1075 RAISE FND_API.G_EXC_ERROR;
1076
1077 end if;
1078
1079 open c_remain_amt(P_LOAN_ID);
1080 fetch c_remain_amt into l_remain_amt;
1081 close c_remain_amt;
1082
1083 -- IF l_remain_amt is NULL THEN
1084 -- l_remain_amt := 0;
1085 -- END IF;
1086
1087 --LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1088 return l_remain_amt;
1089
1090 --EXCEPTION
1091 -- WHEN OTHERS THEN
1092 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'In exception of ' || l_api_name);
1093 END;
1094
1095
1096 /*========================================================================
1097 | PUBLIC FUNCTION LAST_INSTALLMENT_OVERDUE_DATE
1098 |
1099 | DESCRIPTION
1100 | This procedure calculates last installment overdue date.
1101 |
1102 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1103 | None
1104 |
1105 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1106 | None
1107 |
1108 | PARAMETERS
1109 | P_LOAN_ID IN Loan ID
1110 |
1111 | KNOWN ISSUES
1112 | None
1113 |
1114 | NOTES
1115 | Any interesting aspect of the code in the package body which needs
1116 | to be stated.
1117 |
1118 | MODIFICATION HISTORY
1119 | Date Author Description of Changes
1120 | 13-Jul-2009 mbolli Created
1121 |
1122 *=======================================================================*/
1123 FUNCTION LAST_INSTALLMENT_OVERDUE_DATE(P_LOAN_ID IN NUMBER) RETURN DATE
1124 IS
1125
1126 /*-----------------------------------------------------------------------+
1127 | Local Variable Declarations and initializations |
1128 +-----------------------------------------------------------------------*/
1129 l_api_name CONSTANT VARCHAR2(30) := 'LAST_INSTALLMENT_OVERDUE_DATE';
1130 l_return DATE;
1131
1132 /*-----------------------------------------------------------------------+
1133 | Cursor Declarations |
1134 +-----------------------------------------------------------------------*/
1135 CURSOR get_data_crs(P_LOAN_ID IN NUMBER) IS
1136 SELECT trunc(max(DUE_DATE))
1137 FROM lns_amortization_scheds
1138 WHERE loan_id = p_loan_id
1139 AND (REVERSED_FLAG is null or REVERSED_FLAG = 'N')
1140 AND REAMORTIZATION_AMOUNT is null
1141 AND nvl(phase, 'TERM') = 'TERM';
1142
1143 BEGIN
1144 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1145 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'Begin LNS_BILLING_UTIL_PUB.'||l_api_name||' Function + ');
1146 END IF;
1147 OPEN get_data_crs(P_LOAN_ID);
1148 FETCH get_data_crs INTO l_return;
1149 CLOSE get_data_crs;
1150
1151 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1152 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'End LNS_BILLING_UTIL_PUB.'||l_api_name||' Function - ');
1153 END IF;
1154
1155 return l_return;
1156 END;
1157
1158
1159
1160 /*========================================================================
1161 | PUBLIC PROCEDURE VALIDATE_AND_DEFAULT_GL_DATE
1162 |
1163 | DESCRIPTION
1164 | This procedure validates and defaults gl_date. Created for bug 8859462
1165 |
1166 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1167 | None
1168 |
1169 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1170 | None
1171 |
1172 | PARAMETERS
1173 | p_gl_date IN GL_DATE
1174 | p_trx_date IN TRX_DATE
1175 | p_set_of_books_id IN set_of_books_id
1176 | x_default_gl_date OUT New GL_DATE
1177 |
1178 | KNOWN ISSUES
1179 | None
1180 |
1181 | NOTES
1182 | Any interesting aspect of the code in the package body which needs
1183 | to be stated.
1184 |
1185 | MODIFICATION HISTORY
1186 | Date Author Description of Changes
1187 | 21-Sep-2009 scherkas Created
1188 |
1189 *=======================================================================*/
1190 PROCEDURE VALIDATE_AND_DEFAULT_GL_DATE(p_gl_date in date,
1191 p_trx_date in date,
1192 p_set_of_books_id in number,
1193 x_default_gl_date out NOCOPY date)
1194 IS
1195
1196 /*-----------------------------------------------------------------------+
1197 | Local Variable Declarations and initializations |
1198 +-----------------------------------------------------------------------*/
1199 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_AND_DEFAULT_GL_DATE';
1200 l_defaulting_rule_used varchar2(50);
1201 l_error_msg varchar2(100);
1202 l_return boolean;
1203 l_return_char varchar2(10);
1204
1205 /*-----------------------------------------------------------------------+
1206 | Cursor Declarations |
1207 +-----------------------------------------------------------------------*/
1208
1209 BEGIN
1210
1211 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1212
1213 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Input:');
1214 LogMessage(FND_LOG.LEVEL_STATEMENT, 'p_gl_date = ' || p_gl_date);
1215 LogMessage(FND_LOG.LEVEL_STATEMENT, 'p_trx_date = ' || p_trx_date);
1216 LogMessage(FND_LOG.LEVEL_STATEMENT, 'p_set_of_books_id = ' || p_set_of_books_id);
1217
1218 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling ARP_STANDARD.validate_and_default_gl_date...');
1219 l_return := ARP_STANDARD.validate_and_default_gl_date(
1220 gl_date => p_gl_date,
1221 trx_date => p_trx_date,
1222 validation_date1 => null,
1223 validation_date2 => null,
1224 validation_date3 => null,
1225 default_date1 => null,
1226 default_date2 => null,
1227 default_date3 => null,
1228 p_allow_not_open_flag => 'N',
1229 p_invoicing_rule_id => null,
1230 p_set_of_books_id => p_set_of_books_id,
1231 p_application_id => 222,
1232 default_gl_date => x_default_gl_date,
1233 defaulting_rule_used => l_defaulting_rule_used,
1234 error_message => l_error_msg);
1235
1236 if l_return then
1237 l_return_char := 'true';
1238 else
1239 l_return_char := 'false';
1240 end if;
1241
1242 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ARP_STANDARD.validate_and_default_gl_date returns:');
1243 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_return = ' || l_return_char);
1244 LogMessage(FND_LOG.LEVEL_STATEMENT, 'x_default_gl_date = ' || x_default_gl_date);
1245 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_defaulting_rule_used = ' || l_defaulting_rule_used);
1246 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_error_msg = ' || l_error_msg);
1247
1248 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1249
1250 END;
1251
1252
1253
1254 PROCEDURE GET_TRX_DATE(P_LOAN_ID IN NUMBER,
1255 P_DUE_DATE IN DATE,
1256 X_TRX_DATE OUT NOCOPY DATE,
1257 X_RA_TERM_ID OUT NOCOPY NUMBER)
1258 IS
1259
1260 /*-----------------------------------------------------------------------+
1261 | Local Variable Declarations and initializations |
1262 +-----------------------------------------------------------------------*/
1263 l_api_name CONSTANT VARCHAR2(30) := 'GET_TRX_DATE';
1264 l_days number;
1265 l_term_name VARCHAR2(15);
1266
1267 /*-----------------------------------------------------------------------+
1268 | Cursor Declarations |
1269 +-----------------------------------------------------------------------*/
1270 CURSOR get_ra_term IS
1271 select nvl(RA_TERM_ID , 5)
1272 from lns_system_options
1273 where org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
1274
1275 CURSOR validate_ra_term(P_TERM_ID IN NUMBER) IS
1276 SELECT line.DUE_DAYS, term.NAME
1277 FROM RA_TERMS term,
1278 ra_terms_lines line
1279 WHERE line.TERM_ID = P_TERM_ID and
1280 line.DUE_DAYS is not null and
1281 line.DUE_DATE is null and
1282 line.DUE_DAY_OF_MONTH is null and
1283 line.DUE_MONTHS_FORWARD is null and
1284 line.RELATIVE_AMOUNT = 100 and
1285 term.term_id = line.TERM_ID and
1286 term.BILLING_CYCLE_ID IS NULL and
1287 (SELECT count(1) FROM ra_terms_lines WHERE term_id = line.TERM_ID) = 1;
1288
1289 CURSOR get_term(P_TERM_ID IN NUMBER) IS
1290 SELECT NAME
1291 FROM RA_TERMS
1292 WHERE TERM_ID = P_TERM_ID;
1293
1294 BEGIN
1295
1296 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
1297 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Input:');
1298 LogMessage(FND_LOG.LEVEL_STATEMENT, 'P_LOAN_ID = ' || P_LOAN_ID);
1299 LogMessage(FND_LOG.LEVEL_STATEMENT, 'P_DUE_DATE = ' || P_DUE_DATE);
1300 X_RA_TERM_ID := 5;
1301 X_TRX_DATE := P_DUE_DATE;
1302
1303 if P_LOAN_ID is null then
1304 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
1305 FND_MESSAGE.SET_TOKEN('PARAMETER', 'P_LOAN_ID');
1306 FND_MESSAGE.SET_TOKEN('VALUE', 'null');
1307 FND_MSG_PUB.ADD;
1308 RAISE FND_API.G_EXC_ERROR;
1309 end if;
1310 if P_DUE_DATE is null then
1311 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_VALUE');
1312 FND_MESSAGE.SET_TOKEN('PARAMETER', 'P_DUE_DATE');
1313 FND_MESSAGE.SET_TOKEN('VALUE', 'null');
1314 FND_MSG_PUB.ADD;
1315 RAISE FND_API.G_EXC_ERROR;
1316 end if;
1317
1318 -- get ra_term_id
1319 OPEN get_ra_term;
1320 FETCH get_ra_term INTO X_RA_TERM_ID;
1321 CLOSE get_ra_term;
1322 LogMessage(FND_LOG.LEVEL_STATEMENT, 'ra_term_id = ' || X_RA_TERM_ID);
1323
1324 -- validate ra_term_id and get number of days for the term
1325 OPEN validate_ra_term(X_RA_TERM_ID);
1326 FETCH validate_ra_term INTO l_days, l_term_name;
1327 CLOSE validate_ra_term;
1328 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_days = ' || l_days);
1329 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_term_name = ' || l_term_name);
1330
1331 if l_days is null then
1332 OPEN get_term(X_RA_TERM_ID);
1333 FETCH get_term INTO l_term_name;
1334 CLOSE get_term;
1335 logMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Payment Term ' || l_term_name || '(term_id = ' || X_RA_TERM_ID || ') is invalid for using in Loans');
1336 l_days := 0;
1337 end if;
1338
1339 -- calc trx_date
1340 X_TRX_DATE := trunc(P_DUE_DATE - l_days);
1341 LogMessage(FND_LOG.LEVEL_STATEMENT, 'X_TRX_DATE = ' || X_TRX_DATE);
1342 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
1343
1344 END;
1345
1346
1347
1348 BEGIN
1349 G_LOG_ENABLED := 'N';
1350 G_MSG_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1351
1352 /* getting msg logging info */
1353 G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
1354 /*
1355 if (G_LOG_ENABLED = 'N') then
1356 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
1357 else
1358 G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
1359 end if;
1360 */
1361 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
1362 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_MSG_LEVEL: ' || G_MSG_LEVEL);
1363
1364 END LNS_BILLING_UTIL_PUB; -- Package body