1 PACKAGE LNS_BILLING_BATCH_PUB AUTHID CURRENT_USER as
2 /* $Header: LNS_BILLING_S.pls 120.13.12020000.2 2012/07/19 20:52:37 scherkas ship $ */
3
4 /*=======================================================================+
5 | Declare PUBLIC Data Types and Variables
6 +=======================================================================*/
7
8 TYPE LOAN_TO_BILL_REC IS RECORD(
9 LOAN_ID NUMBER,
10 OBJECT_VERSION_NUMBER NUMBER,
11 LOAN_NUMBER VARCHAR2(60),
12 LOAN_DESCRIPTION VARCHAR2(250),
13 FUNDED_AMOUNT NUMBER,
14 FIRST_PAYMENT_DATE DATE,
15 NEXT_PAYMENT_DUE_DATE DATE,
16 NEXT_PAYMENT_LATE_DATE DATE,
17 BILLED_FLAG VARCHAR2(1),
18 LOAN_CURRENCY VARCHAR2(15),
19 CUST_ACCOUNT_ID NUMBER,
20 BILL_TO_ADDRESS_ID NUMBER,
21 LOAN_PAYMENT_FREQUENCY VARCHAR2(30),
22 NUMBER_GRACE_DAYS NUMBER,
23 PAYMENT_APPLICATION_ORDER VARCHAR2(30),
24 CUSTOM_PAYMENTS_FLAG VARCHAR2(1),
25 LAST_PAYMENT_NUMBER NUMBER,
26 NEXT_PAYMENT_NUMBER NUMBER,
27 NEXT_PRINCIPAL_AMOUNT NUMBER,
28 NEXT_INTEREST_AMOUNT NUMBER,
29 NEXT_FEE_AMOUNT NUMBER,
30 NEXT_AMORTIZATION_ID NUMBER,
31 RATE_ID NUMBER,
32 PARENT_AMORTIZATION_ID NUMBER,
33 EXCHANGE_RATE_TYPE VARCHAR2(30),
34 EXCHANGE_DATE DATE,
35 EXCHANGE_RATE NUMBER,
36 ORG_ID NUMBER,
37 LEGAL_ENTITY_ID NUMBER,
38 CURRENT_PHASE VARCHAR2(30),
39 FORGIVENESS_FLAG VARCHAR2(1),
40 FORGIVENESS_PERCENT NUMBER,
41 DISABLE_BILLING_FLAG VARCHAR2(1),
42 PRINCIPAL_BALANCE NUMBER,
43 DEFERRED_INT_AMOUNT NUMBER,
44 CURR_CAP_INT_AMOUNT NUMBER,
45 CAP_INT_AMOUNT NUMBER
46 );
47 TYPE LOANS_TO_BILL_TBL IS TABLE OF LOAN_TO_BILL_REC INDEX BY BINARY_INTEGER;
48
49 TYPE LOAN_NEXT_DD_REC IS RECORD(
50 LOAN_ID NUMBER,
51 LOAN_NUMBER VARCHAR2(60),
52 OBJECT_VERSION_NUMBER NUMBER,
53 NEXT_PAYMENT_NUMBER NUMBER,
54 CUSTOM_PAYMENTS_FLAG VARCHAR2(1)
55 );
56
57 TYPE REVERSE_REC IS RECORD(
58 TRX_NUMBER VARCHAR2(20),
59 CUSTOMER_TRX_ID NUMBER,
60 PAYMENT_SCHEDULE_ID NUMBER,
61 CUSTOMER_TRX_LINE_ID NUMBER,
62 LINE_TYPE VARCHAR2(30),
63 TRX_AMOUNT NUMBER,
64 APPLIED_AMOUNT NUMBER,
65 ORG_ID NUMBER
66 );
67 TYPE REVERSE_TBL IS TABLE OF REVERSE_REC INDEX BY BINARY_INTEGER;
68
69 TYPE BILL_HEADER_REC IS RECORD(
70 HEADER_ID NUMBER,
71 LOAN_ID NUMBER,
72 ASSOC_PAYMENT_NUM NUMBER,
73 DUE_DATE DATE,
74 CUST_ACCOUNT_ID NUMBER,
75 BILL_TO_ADDRESS_ID NUMBER
76 );
77 TYPE BILL_HEADERS_TBL IS TABLE OF BILL_HEADER_REC INDEX BY BINARY_INTEGER;
78
79 TYPE BILL_LINE_REC IS RECORD(
80 HEADER_ID NUMBER,
81 LINE_ID NUMBER,
82 CUSTOMER_TRX_ID NUMBER,
83 PAYMENT_SCHEDULE_ID NUMBER,
84 CUSTOMER_TRX_LINE_ID NUMBER,
85 LINE_REF_ID NUMBER,
86 LINE_AMOUNT NUMBER,
87 LINE_TYPE VARCHAR2(30),
88 LINE_DESC VARCHAR2(240),
89 CASH_RECEIPT_ID NUMBER,
90 APPLY_AMOUNT NUMBER,
91 PAYMENT_ORDER NUMBER,
92 FEE_SCHEDULE_ID NUMBER,
93 LINE_DETAILS VARCHAR2(2000)
94 );
95 TYPE BILL_LINES_TBL IS TABLE OF BILL_LINE_REC INDEX BY BINARY_INTEGER;
96
97 TYPE INVOICE_DETAILS_REC IS RECORD(CUST_TRX_ID NUMBER
98 ,PAYMENT_SCHEDULE_ID NUMBER
99 ,INVOICE_NUMBER VARCHAR2(60)
100 ,INSTALLMENT_NUMBER NUMBER
101 ,TRANSACTION_TYPE VARCHAR2(20) --change this to translated type
102 ,ORIGINAL_AMOUNT NUMBER
103 ,REMAINING_AMOUNT NUMBER
104 ,FORGIVENESS_AMOUNT NUMBER
105 ,TRX_DATE DATE
106 ,DUE_DATE DATE
107 ,GL_DATE DATE
108 ,PURPOSE VARCHAR2(30)
109 ,BILLED_FLAG VARCHAR2(1)
110 ,INVOICE_CURRENCY VARCHAR2(15)
111 ,EXCHANGE_RATE NUMBER);
112
113 TYPE INVOICE_DETAILS_TBL IS TABLE OF INVOICE_DETAILS_REC INDEX BY BINARY_INTEGER;
114
115 TYPE CASH_RECEIPT_REC IS RECORD(CASH_RECEIPT_ID NUMBER
116 ,RECEIPT_AMOUNT NUMBER
117 ,RECEIPT_CURRENCY VARCHAR2(10)
118 ,EXCHANGE_RATE NUMBER
119 ,EXCHANGE_DATE DATE
120 ,EXCHANGE_RATE_TYPE VARCHAR2(30)
121 ,ORIGINAL_CURRENCY VARCHAR2(10)
122 ,RECEIPT_NUMBER VARCHAR2(30)
123 ,APPLY_DATE DATE
124 ,GL_DATE DATE);
125
126 TYPE CASH_RECEIPT_TBL IS TABLE OF CASH_RECEIPT_REC INDEX BY BINARY_INTEGER;
127
128 TYPE AMORTIZATION_SCHED_TBL IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
129
130
131 PROCEDURE CREATE_AR_ADJ(P_TYPE IN VARCHAR2,
132 P_PAYMENT_SCHEDULE_ID IN NUMBER,
133 P_RECEIVABLES_TRX_ID IN NUMBER,
134 P_AMOUNT IN NUMBER,
135 P_APPLY_DATE IN DATE,
136 P_GL_DATE IN DATE,
137 P_CUSTOMER_TRX_LINE_ID IN NUMBER,
138 P_CODE_COMBINATION_ID IN NUMBER,
139 P_USSGL_TRX_CODE IN VARCHAR2,
140 P_REASON_CODE IN VARCHAR2,
141 P_COMMENTS IN VARCHAR2,
142 X_ADJ_ID OUT NOCOPY NUMBER,
143 X_ADJ_NUMBER OUT NOCOPY VARCHAR2,
144 P_ORG_ID IN NUMBER);
145
146
147 /*========================================================================
148 | PUBLIC PROCEDURE LNS_BILLING_CONCUR
149 |
150 | DESCRIPTION
151 | This procedure gets called from CM to start billing engine.
152 |
153 | PSEUDO CODE/LOGIC
154 |
155 | PARAMETERS
156 | ERRBUF OUT Returns errors to CM
157 | RETCODE OUT Returns error code to CM
158 | TYPE_ID IN Inputs loan type id
159 | PRODUCT_ID IN Inputs loan product id
160 | BORROWER_ID IN Inputs borrower id
161 | LOAN_ID IN Inputs loan
162 | FROM_DAYS_TO_DD IN Inputs from days
163 | TO_DAYS_TO_DD IN Inputs to days
164 |
165 | KNOWN ISSUES
166 | None
167 |
168 | NOTES
169 |
170 | MODIFICATION HISTORY
171 | Date Author Description of Changes
172 | 30-12-2003 scherkas Created
173 |
174 *=======================================================================*/
175 PROCEDURE LNS_BILLING_CONCUR(
176 ERRBUF OUT NOCOPY VARCHAR2,
177 RETCODE OUT NOCOPY VARCHAR2,
178 TYPE_ID IN NUMBER,
179 PRODUCT_ID IN NUMBER,
180 BORROWER_ID IN NUMBER,
181 LOAN_ID IN NUMBER,
182 FROM_DAYS_TO_DD IN NUMBER,
183 TO_DAYS_TO_DD IN NUMBER);
184
185
186 /*========================================================================
187 | PUBLIC PROCEDURE LNS_RVRS_PMT_CONCUR
188 |
189 | DESCRIPTION
190 | This procedure gets called from CM to start reversal engine.
191 |
192 | PSEUDO CODE/LOGIC
193 |
194 | PARAMETERS
195 | ERRBUF OUT Returns errors to CM
196 | RETCODE OUT Returns error code to CM
197 | LOAN_ID IN Inputs loan
198 | REBILL_FLAG IN Inputs rebill flag
199 |
200 | KNOWN ISSUES
201 | None
202 |
203 | NOTES
204 |
205 | MODIFICATION HISTORY
206 | Date Author Description of Changes
207 | 30-12-2003 scherkas Created
208 |
209 *=======================================================================*/
210 PROCEDURE LNS_RVRS_PMT_CONCUR(
211 ERRBUF OUT NOCOPY VARCHAR2,
212 RETCODE OUT NOCOPY VARCHAR2,
213 LOAN_ID IN NUMBER,
214 REBILL_FLAG IN VARCHAR2);
215
216
217
218 /*========================================================================
219 | PUBLIC PROCEDURE LNS_ADJUST_RECEIV_CONCUR
220 |
221 | DESCRIPTION
222 | This procedure got called from concurent manager to adjust original receivables for a loan
223 |
224 | PSEUDO CODE/LOGIC
225 |
226 | PARAMETERS
227 | ERRBUF OUT Returns errors to CM
228 | RETCODE OUT Returns error code to CM
229 | LOAN_ID IN Inputs loan
230 |
231 | KNOWN ISSUES
232 | None
233 |
234 | NOTES
235 | Any interesting aspect of the code in the package body which needs
236 | to be stated.
237 |
238 | MODIFICATION HISTORY
239 | Date Author Description of Changes
240 | 01-01-2004 scherkas Created
241 |
242 *=======================================================================*/
243 PROCEDURE LNS_ADJUST_RECEIV_CONCUR(
244 ERRBUF OUT NOCOPY VARCHAR2,
245 RETCODE OUT NOCOPY VARCHAR2,
246 LOAN_ID IN NUMBER);
247
248
249
250 /*========================================================================
251 | PUBLIC PROCEDURE BILL_LOANS
252 |
253 | DESCRIPTION
254 | This procedure searches for loans to bill and process them one by one.
255 |
256 | PSEUDO CODE/LOGIC
257 |
258 | PARAMETERS
259 | P_API_VERSION IN Standard in parameter
260 | P_INIT_MSG_LIST IN Standard in parameter
261 | P_COMMIT IN Standard in parameter
262 | P_VALIDATION_LEVEL IN Standard in parameter
263 | P_TYPE_ID IN Inputs loan type id
264 | P_PRODUCT_ID IN Inputs loan product id
265 | P_BORROWER_ID IN Inputs borrower id
266 | P_LOAN_ID IN Inputs loan id
267 | P_FROM_DAYS_TO_DD IN Inputs from days
268 | P_TO_DAYS_TO_DD IN Inputs to days
269 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
270 | X_MSG_COUNT OUT NOCOPY Standard out parameter
271 | X_MSG_DATA OUT NOCOPY Standard out parameter
272 |
273 | KNOWN ISSUES
274 | None
275 |
276 | NOTES
277 |
278 | MODIFICATION HISTORY
279 | Date Author Description of Changes
280 | 30-12-2003 scherkas Created
281 |
282 *=======================================================================*/
283 PROCEDURE BILL_LOANS(
284 P_API_VERSION IN NUMBER,
285 P_INIT_MSG_LIST IN VARCHAR2,
286 P_COMMIT IN VARCHAR2,
287 P_VALIDATION_LEVEL IN NUMBER,
288 P_TYPE_ID IN NUMBER,
289 P_PRODUCT_ID IN NUMBER,
290 P_BORROWER_ID IN NUMBER,
291 P_LOAN_ID IN NUMBER,
292 P_FROM_DAYS_TO_DD IN NUMBER,
293 P_TO_DAYS_TO_DD IN NUMBER,
294 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
295 X_MSG_COUNT OUT NOCOPY NUMBER,
296 X_MSG_DATA OUT NOCOPY VARCHAR2);
297
298
299
300 /*========================================================================
301 | PUBLIC PROCEDURE CALC_PAST_DUE_LOANS_NEXT_DD
302 |
303 | DESCRIPTION
304 | This procedure searches for overdue loans and recalc new due days.
305 |
306 | PSEUDO CODE/LOGIC
307 |
308 | PARAMETERS
309 | P_API_VERSION IN Standard in parameter
310 | P_INIT_MSG_LIST IN Standard in parameter
311 | P_COMMIT IN Standard in parameter
312 | P_VALIDATION_LEVEL IN Standard in parameter
313 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
314 | X_MSG_COUNT OUT NOCOPY Standard out parameter
315 | X_MSG_DATA OUT NOCOPY Standard out parameter
316 |
317 | KNOWN ISSUES
318 | None
319 |
320 | NOTES
321 |
322 | MODIFICATION HISTORY
323 | Date Author Description of Changes
324 | 30-12-2003 scherkas Created
325 |
326 *=======================================================================*/
327 PROCEDURE CALC_PAST_DUE_LOANS_NEXT_DD(
328 P_API_VERSION IN NUMBER,
329 P_INIT_MSG_LIST IN VARCHAR2,
330 P_COMMIT IN VARCHAR2,
331 P_VALIDATION_LEVEL IN NUMBER,
332 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
333 X_MSG_COUNT OUT NOCOPY NUMBER,
334 X_MSG_DATA OUT NOCOPY VARCHAR2);
335
336
337
338 /*========================================================================
339 | PUBLIC PROCEDURE REVERSE_LAST_AMORTIZATION
340 |
341 | DESCRIPTION
342 | This procedure searches and reverses last loans amortization.
346 | PARAMETERS
343 |
344 | PSEUDO CODE/LOGIC
345 |
347 | P_API_VERSION IN Standard in parameter
348 | P_INIT_MSG_LIST IN Standard in parameter
349 | P_COMMIT IN Standard in parameter
350 | P_VALIDATION_LEVEL IN Standard in parameter
351 | P_LOAN_ID IN Inputs loan id
352 | P_REBILL_FLAG IN Inputs rebill flag
353 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
354 | X_MSG_COUNT OUT NOCOPY Standard out parameter
355 | X_MSG_DATA OUT NOCOPY Standard out parameter
356 |
357 | KNOWN ISSUES
358 | None
359 |
360 | NOTES
361 |
362 | MODIFICATION HISTORY
363 | Date Author Description of Changes
364 | 30-12-2003 scherkas Created
365 |
366 *=======================================================================*/
367 PROCEDURE REVERSE_LAST_AMORTIZATION(
368 P_API_VERSION IN NUMBER,
369 P_INIT_MSG_LIST IN VARCHAR2,
370 P_COMMIT IN VARCHAR2,
371 P_VALIDATION_LEVEL IN NUMBER,
372 P_LOAN_ID IN NUMBER,
373 P_REBILL_FLAG IN VARCHAR2,
374 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
375 X_MSG_COUNT OUT NOCOPY NUMBER,
376 X_MSG_DATA OUT NOCOPY VARCHAR2);
377
378 /*========================================================================
379 | PUBLIC PROCEDURE CREDIT_AMORTIZATION_PARTIAL
380 |
381 | DESCRIPTION
382 | This procedure will credit a portion of the last amortization.
383 | The portion can be principal interest or fees
384 |
385 | PSEUDO CODE/LOGIC
386 |
387 | PARAMETERS
388 | P_API_VERSION IN Standard in parameter
389 | P_INIT_MSG_LIST IN Standard in parameter
390 | P_COMMIT IN Standard in parameter
391 | P_VALIDATION_LEVEL IN Standard in parameter
392 | P_LOAN_ID IN Inputs loan id
393 | P_LINE_TYPE IN PRIN or INT or FEE
394 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
395 | X_MSG_COUNT OUT NOCOPY Standard out parameter
396 | X_MSG_DATA OUT NOCOPY Standard out parameter
397 |
398 | KNOWN ISSUES
399 | None
400 |
401 | NOTES
402 |
403 | MODIFICATION HISTORY
404 | Date Author Description of Changes
405 | 10-12-2004 raverma Created
406 |
407 *=======================================================================*/
408 PROCEDURE CREDIT_AMORTIZATION_PARTIAL(P_API_VERSION IN NUMBER
409 ,P_INIT_MSG_LIST IN VARCHAR2
410 ,P_COMMIT IN VARCHAR2
411 ,P_VALIDATION_LEVEL IN NUMBER
412 ,P_LOAN_ID IN NUMBER
413 ,P_LINE_TYPE IN VARCHAR2
414 ,X_RETURN_STATUS OUT NOCOPY VARCHAR
415 ,X_MSG_COUNT OUT NOCOPY NUMBER
416 ,X_MSG_DATA OUT NOCOPY VARCHAR2);
417
418
419 /*========================================================================
420 | PUBLIC PROCEDURE CREATE_SINGLE_OFFCYCLE_BILL
421 |
422 | DESCRIPTION
423 | This procedure creates single manual bill.
424 |
425 | PSEUDO CODE/LOGIC
426 |
427 | PARAMETERS
428 | P_API_VERSION IN Standard in parameter
429 | P_INIT_MSG_LIST IN Standard in parameter
430 | P_COMMIT IN Standard in parameter
431 | P_VALIDATION_LEVEL IN Standard in parameter
432 | P_BILL_HEADER_REC IN Manual bill header record
433 | P_BILL_LINES_TBL IN Manual bill lines
434 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
435 | X_MSG_COUNT OUT NOCOPY Standard out parameter
436 | X_MSG_DATA OUT NOCOPY Standard out parameter
437 |
438 | KNOWN ISSUES
439 | None
440 |
441 | NOTES
442 |
443 | MODIFICATION HISTORY
444 | Date Author Description of Changes
445 | 30-12-2003 scherkas Created
446 |
447 *=======================================================================*/
448 PROCEDURE CREATE_SINGLE_OFFCYCLE_BILL(
449 P_API_VERSION IN NUMBER,
450 P_INIT_MSG_LIST IN VARCHAR2,
451 P_COMMIT IN VARCHAR2,
452 P_VALIDATION_LEVEL IN NUMBER,
453 P_BILL_HEADER_REC IN LNS_BILLING_BATCH_PUB.BILL_HEADER_REC,
454 P_BILL_LINES_TBL IN LNS_BILLING_BATCH_PUB.BILL_LINES_TBL,
455 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
456 X_MSG_COUNT OUT NOCOPY NUMBER,
457 X_MSG_DATA OUT NOCOPY VARCHAR2);
458
459
460
461 /*========================================================================
462 | PUBLIC PROCEDURE CREATE_OFFCYCLE_BILLS
463 |
464 | DESCRIPTION
465 | This procedure creates multiple manual bills.
466 |
467 | PSEUDO CODE/LOGIC
468 |
469 | PARAMETERS
470 | P_API_VERSION IN Standard in parameter
471 | P_INIT_MSG_LIST IN Standard in parameter
472 | P_COMMIT IN Standard in parameter
473 | P_VALIDATION_LEVEL IN Standard in parameter
474 | P_BILL_HEADERS_TBL IN Manual bill headers
475 | P_BILL_LINES_TBL IN Manual bill lines
479 |
476 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
477 | X_MSG_COUNT OUT NOCOPY Standard out parameter
478 | X_MSG_DATA OUT NOCOPY Standard out parameter
480 | KNOWN ISSUES
481 | None
482 |
483 | NOTES
484 |
485 | MODIFICATION HISTORY
486 | Date Author Description of Changes
487 | 30-12-2003 scherkas Created
488 |
489 *=======================================================================*/
490 PROCEDURE CREATE_OFFCYCLE_BILLS(
491 P_API_VERSION IN NUMBER,
492 P_INIT_MSG_LIST IN VARCHAR2,
493 P_COMMIT IN VARCHAR2,
494 P_VALIDATION_LEVEL IN NUMBER,
495 P_BILL_HEADERS_TBL IN LNS_BILLING_BATCH_PUB.BILL_HEADERS_TBL,
496 P_BILL_LINES_TBL IN LNS_BILLING_BATCH_PUB.BILL_LINES_TBL,
497 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
498 X_MSG_COUNT OUT NOCOPY NUMBER,
499 X_MSG_DATA OUT NOCOPY VARCHAR2);
500
501
502
503 /*========================================================================
504 | PUBLIC PROCEDURE REVERSE_OFFCYCLE_BILL
505 |
506 | DESCRIPTION
507 | This procedure reverses a single manual bill.
508 |
509 | PSEUDO CODE/LOGIC
510 |
511 | PARAMETERS
512 | P_API_VERSION IN Standard in parameter
513 | P_INIT_MSG_LIST IN Standard in parameter
514 | P_COMMIT IN Standard in parameter
515 | P_VALIDATION_LEVEL IN Standard in parameter
516 | P_AMORTIZATION_ID IN Input amortization id to reverse
517 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
518 | X_MSG_COUNT OUT NOCOPY Standard out parameter
519 | X_MSG_DATA OUT NOCOPY Standard out parameter
520 |
521 | KNOWN ISSUES
522 | None
523 |
524 | NOTES
525 |
526 | MODIFICATION HISTORY
527 | Date Author Description of Changes
528 | 30-12-2003 scherkas Created
529 |
530 *=======================================================================*/
531 PROCEDURE REVERSE_OFFCYCLE_BILL(
532 P_API_VERSION IN NUMBER,
533 P_INIT_MSG_LIST IN VARCHAR2,
534 P_COMMIT IN VARCHAR2,
535 P_VALIDATION_LEVEL IN NUMBER,
536 P_AMORTIZATION_ID IN NUMBER,
537 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
538 X_MSG_COUNT OUT NOCOPY NUMBER,
539 X_MSG_DATA OUT NOCOPY VARCHAR2);
540
541
542
543
544 /*========================================================================
545 | PUBLIC PROCEDURE ADJUST_ORIGINAL_RECEIVABLE
546 |
547 | DESCRIPTION
548 | This procedure adjusts loans original receivable in AR
549 |
550 | PSEUDO CODE/LOGIC
551 |
552 | PARAMETERS
553 | P_API_VERSION IN Standard in parameter
554 | P_INIT_MSG_LIST IN Standard in parameter
555 | P_COMMIT IN Standard in parameter
556 | P_VALIDATION_LEVEL IN Standard in parameter
557 | P_LOAN_ID IN Loan ID
558 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
559 | X_MSG_COUNT OUT NOCOPY Standard out parameter
560 | X_MSG_DATA OUT NOCOPY Standard out parameter
561 |
562 | KNOWN ISSUES
563 | None
564 |
565 | NOTES
566 |
567 | MODIFICATION HISTORY
568 | Date Author Description of Changes
569 | 01-01-2004 scherkas Created
570 |
571 *=======================================================================*/
572 PROCEDURE ADJUST_ORIGINAL_RECEIVABLE(
573 P_API_VERSION IN NUMBER,
574 P_INIT_MSG_LIST IN VARCHAR2,
575 P_COMMIT IN VARCHAR2,
576 P_VALIDATION_LEVEL IN NUMBER,
577 P_LOAN_ID IN NUMBER,
578 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
579 X_MSG_COUNT OUT NOCOPY NUMBER,
580 X_MSG_DATA OUT NOCOPY VARCHAR2);
581
582
583 /*========================================================================
584 | PUBLIC PROCEDURE PREBILL_SINGLE_LOAN
585 |
586 | DESCRIPTION
587 | This procedure prebill (do initial billing) for single loan
588 |
589 | PSEUDO CODE/LOGIC
590 |
591 | PARAMETERS
592 | P_API_VERSION IN Standard in parameter
593 | P_INIT_MSG_LIST IN Standard in parameter
594 | P_COMMIT IN Standard in parameter
595 | P_VALIDATION_LEVEL IN Standard in parameter
596 | P_LOAN_ID IN Loan ID
597 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
598 | X_MSG_COUNT OUT NOCOPY Standard out parameter
599 | X_MSG_DATA OUT NOCOPY Standard out parameter
600 |
601 | KNOWN ISSUES
602 | None
603 |
604 | NOTES
605 | Any interesting aspect of the code in the package body which needs
606 | to be stated.
607 |
608 | MODIFICATION HISTORY
609 | Date Author Description of Changes
610 | 12-23-2004 scherkas Created
611 |
612 *=======================================================================*/
613 PROCEDURE PREBILL_SINGLE_LOAN(
614 P_API_VERSION IN NUMBER,
615 P_INIT_MSG_LIST IN VARCHAR2,
616 P_COMMIT IN VARCHAR2,
617 P_VALIDATION_LEVEL IN NUMBER,
618 P_LOAN_ID IN NUMBER,
619 X_BILLED_YN OUT NOCOPY VARCHAR2,
623
620 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
621 X_MSG_COUNT OUT NOCOPY NUMBER,
622 X_MSG_DATA OUT NOCOPY VARCHAR2);
624
625
626 /*========================================================================
627 | PUBLIC PROCEDURE GENERATE_BILLING_STATEMENT_XML
628 |
629 | DESCRIPTION
630 | This procedure creates the billing statement xml for single loan
631 |
632 | PSEUDO CODE/LOGIC
633 |
634 | PARAMETERS
635 | P_LOAN_ID IN NUMBER
636 | P_AMORTIZATION_SCHEDULE_ID IN NUMBER
637 | KNOWN ISSUES
638 | None
639 |
640 | NOTES
641 | Any interesting aspect of the code in the package body which needs
642 | to be stated.
643 |
644 | MODIFICATION HISTORY
645 | Date Author Description of Changes
646 | 04-24-2004 karamach Created
647 | 05-04-2004 scherkas Modified
648 | 06-19-2004 karamach Changed to use DBMS_XMLQUERY (8i pkg) instead of DBMS_XMLGEN (9i pkg)
649 | to be compatible with the supported Oracle database version 8.1.7.4
650 | and avoid 9i dependency
651 |
652 *=======================================================================*/
653 PROCEDURE GENERATE_BILLING_STATEMENT_XML(p_loan_id IN NUMBER,
654 p_amortization_schedule_id IN NUMBER);
655
656
657 /*========================================================================
658 | PUBLIC PROCEDURE PROCESS_PAID_LOANS
659 |
660 | DESCRIPTION
661 | This procedure sets still active paid off loans to status PAIDOFF
662 |
663 | PSEUDO CODE/LOGIC
664 |
665 | PARAMETERS
666 | P_API_VERSION IN Standard in parameter
667 | P_INIT_MSG_LIST IN Standard in parameter
668 | P_COMMIT IN Standard in parameter
669 | P_VALIDATION_LEVEL IN Standard in parameter
670 | P_LOAN_ID IN Loan
671 | P_PAYOFF_DATE IN Pay off date
672 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
673 | X_MSG_COUNT OUT NOCOPY Standard out parameter
674 | X_MSG_DATA OUT NOCOPY Standard out parameter
675
676 | KNOWN ISSUES
677 | None
678 |
679 | NOTES
680 | Any interesting aspect of the code in the package body which needs
681 | to be stated.
682 |
683 | MODIFICATION HISTORY
684 | Date Author Description of Changes
685 | 01-01-2004 scherkas Created
686 |
687 *=======================================================================*/
688 PROCEDURE PROCESS_PAID_LOANS(
689 P_API_VERSION IN NUMBER,
690 P_INIT_MSG_LIST IN VARCHAR2,
691 P_COMMIT IN VARCHAR2,
692 P_VALIDATION_LEVEL IN NUMBER,
693 P_LOAN_ID IN NUMBER,
694 P_PAYOFF_DATE IN DATE,
695 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
696 X_MSG_COUNT OUT NOCOPY NUMBER,
697 X_MSG_DATA OUT NOCOPY VARCHAR2);
698
699
700 /*========================================================================
701 | PUBLIC PROCEDURE GET_NEXT_INSTAL_TO_BILL
702 |
703 | DESCRIPTION
704 | This procedure returns next installment to be billed in LNS_BILLING_BATCH_PUB.INVOICE_DETAILS_TBL format
705 |
706 | PSEUDO CODE/LOGIC
707 |
708 | PARAMETERS
709 | P_API_VERSION IN Standard in parameter
710 | P_INIT_MSG_LIST IN Standard in parameter
711 | P_COMMIT IN Standard in parameter
712 | P_VALIDATION_LEVEL IN Standard in parameter
713 | P_LOAN_ID IN Loan ID
714 | X_INVOICES_TBL OUT NOCOPY LNS_BILLING_BATCH_PUB.INVOICE_DETAILS_TBL,
715 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
716 | X_MSG_COUNT OUT NOCOPY Standard out parameter
717 | X_MSG_DATA OUT NOCOPY Standard out parameter
718 |
719 | KNOWN ISSUES
720 | None
721 |
722 | NOTES
723 | Any interesting aspect of the code in the package body which needs
724 | to be stated.
725 |
726 | MODIFICATION HISTORY
727 | Date Author Description of Changes
728 | 03-17-2009 scherkas Created for bug
729 |
730 *=======================================================================*/
731 PROCEDURE GET_NEXT_INSTAL_TO_BILL(
732 P_API_VERSION IN NUMBER,
733 P_INIT_MSG_LIST IN VARCHAR2,
734 P_COMMIT IN VARCHAR2,
735 P_VALIDATION_LEVEL IN NUMBER,
736 P_LOAN_ID IN NUMBER,
737 X_INVOICES_TBL OUT NOCOPY LNS_BILLING_BATCH_PUB.INVOICE_DETAILS_TBL,
738 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
739 X_MSG_COUNT OUT NOCOPY NUMBER,
740 X_MSG_DATA OUT NOCOPY VARCHAR2);
741
742
743 /*========================================================================
744 | PUBLIC PROCEDURE BILL_AND_PAY_NEXT_INSTAL
745 |
746 | DESCRIPTION
747 | This procedure bills and pays next scheduled installment
748 |
749 | PSEUDO CODE/LOGIC
750 |
751 | PARAMETERS
752 | P_API_VERSION IN Standard in parameter
753 | P_INIT_MSG_LIST IN Standard in parameter
754 | P_COMMIT IN Standard in parameter
755 | P_VALIDATION_LEVEL IN Standard in parameter
756 | P_LOAN_ID IN Loan ID
757 | P_CASH_RECEIPTS_TBL IN LNS_BILLING_BATCH_PUB.CASH_RECEIPT_TBL
758 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
762 | KNOWN ISSUES
759 | X_MSG_COUNT OUT NOCOPY Standard out parameter
760 | X_MSG_DATA OUT NOCOPY Standard out parameter
761 |
763 | None
764 |
765 | NOTES
766 | Any interesting aspect of the code in the package body which needs
767 | to be stated.
768 |
769 | MODIFICATION HISTORY
770 | Date Author Description of Changes
771 | 03-17-2009 scherkas Created for bug
772 |
773 *=======================================================================*/
774 PROCEDURE BILL_AND_PAY_NEXT_INSTAL(
775 P_API_VERSION IN NUMBER,
776 P_INIT_MSG_LIST IN VARCHAR2,
777 P_COMMIT IN VARCHAR2,
778 P_VALIDATION_LEVEL IN NUMBER,
779 P_LOAN_ID IN NUMBER,
780 P_CASH_RECEIPTS_TBL IN LNS_BILLING_BATCH_PUB.CASH_RECEIPT_TBL,
781 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
782 X_MSG_COUNT OUT NOCOPY NUMBER,
783 X_MSG_DATA OUT NOCOPY VARCHAR2);
784
785
786
787 /*========================================================================
788 | PUBLIC PROCEDURE PAY_INSTALLMENTS
789 |
790 | DESCRIPTION
791 | This procedure applies cash receipts to given installments
792 |
793 | PSEUDO CODE/LOGIC
794 |
795 | PARAMETERS
796 | P_API_VERSION IN Standard in parameter
797 | P_INIT_MSG_LIST IN Standard in parameter
798 | P_COMMIT IN Standard in parameter
799 | P_VALIDATION_LEVEL IN Standard in parameter
800 | P_LOAN_ID IN Loan ID
801 | P_AM_SCHED_TBL IN LNS_BILLING_BATCH_PUB.AMORTIZATION_SCHED_TBL
802 | P_CASH_RECEIPTS_TBL IN LNS_BILLING_BATCH_PUB.CASH_RECEIPT_TBL
803 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
804 | X_MSG_COUNT OUT NOCOPY Standard out parameter
805 | X_MSG_DATA OUT NOCOPY Standard out parameter
806 |
807 | KNOWN ISSUES
808 | None
809 |
810 | NOTES
811 | Any interesting aspect of the code in the package body which needs
812 | to be stated.
813 |
814 | MODIFICATION HISTORY
815 | Date Author Description of Changes
816 | 03-17-2009 scherkas Created for bug
817 |
818 *=======================================================================*/
819 PROCEDURE PAY_INSTALLMENTS(
820 P_API_VERSION IN NUMBER,
821 P_INIT_MSG_LIST IN VARCHAR2,
822 P_COMMIT IN VARCHAR2,
823 P_VALIDATION_LEVEL IN NUMBER,
824 P_LOAN_ID IN NUMBER,
825 P_AM_SCHED_TBL IN LNS_BILLING_BATCH_PUB.AMORTIZATION_SCHED_TBL,
826 P_CASH_RECEIPTS_TBL IN LNS_BILLING_BATCH_PUB.CASH_RECEIPT_TBL,
827 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
828 X_MSG_COUNT OUT NOCOPY NUMBER,
829 X_MSG_DATA OUT NOCOPY VARCHAR2);
830
831
832
833
834 /*========================================================================
835 | PUBLIC FUNCTION CAN_BILL_NEXT_INSTAL
836 |
837 | DESCRIPTION
838 | This function returns true/false is loan ready to bill next installment
839 |
840 | PSEUDO CODE/LOGIC
841 |
842 | PARAMETERS
843 | P_LOAN_ID IN Loan ID
844 |
845 | KNOWN ISSUES
846 | None
847 |
848 | NOTES
849 | Any interesting aspect of the code in the package body which needs
850 | to be stated.
851 |
852 | MODIFICATION HISTORY
853 | Date Author Description of Changes
854 | 03-17-2009 scherkas Created for bug
855 |
856 *=======================================================================*/
857 FUNCTION CAN_BILL_NEXT_INSTAL(P_LOAN_ID IN NUMBER) return BOOLEAN;
858
859
860
861 /*========================================================================
862 | PUBLIC PROCEDURE GET_FORGIVENESS_AMOUNT
863 |
864 | DESCRIPTION
865 | This procedure returns forgiveness amount based on forgiveness settings and passed amount
866 |
867 | PSEUDO CODE/LOGIC
868 |
869 | PARAMETERS
870 | P_LOAN_ID IN Loan ID
871 | P_AMOUNT IN Passed amount
872 | X_FORGIVENESS_AMOUNT OUT NOCOPY Returned forgiveness amount
873 | X_REMAINING_AMOUNT OUT NOCOPY Returned remianing amount
874 |
875 | KNOWN ISSUES
876 | None
877 |
878 | NOTES
879 | Any interesting aspect of the code in the package body which needs
880 | to be stated.
881 |
882 | MODIFICATION HISTORY
883 | Date Author Description of Changes
884 | 04-27-2008 scherkas Created for bug 8400747
885 |
886 *=======================================================================*/
887 PROCEDURE GET_FORGIVENESS_AMOUNT(
888 P_LOAN_ID IN NUMBER,
889 P_AMOUNT IN NUMBER,
890 X_FORGIVENESS_AMOUNT OUT NOCOPY NUMBER,
891 X_REMAINING_AMOUNT OUT NOCOPY NUMBER);
892
893
894
895 /*========================================================================
896 | PUBLIC PROCEDURE GET_BILLED_INSTALLMENT
897 |
898 | DESCRIPTION
899 | This procedure returns already billed installment in LNS_BILLING_BATCH_PUB.INVOICE_DETAILS_TBL format
900 |
901 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
902 | None
903 |
904 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
908 | PARAMETERS
905 | init
906 | LogMessage
907 |
909 | P_API_VERSION IN Standard in parameter
910 | P_INIT_MSG_LIST IN Standard in parameter
911 | P_COMMIT IN Standard in parameter
912 | P_VALIDATION_LEVEL IN Standard in parameter
913 | P_LOAN_ID IN Loan ID
914 | P_AM_SCHED_ID IN Amortization sched ID
915 | X_INVOICES_TBL OUT NOCOPY LNS_BILLING_BATCH_PUB.INVOICE_DETAILS_TBL,
916 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
917 | X_MSG_COUNT OUT NOCOPY Standard out parameter
918 | X_MSG_DATA OUT NOCOPY Standard out parameter
919 |
920 | KNOWN ISSUES
921 | None
922 |
923 | NOTES
924 | Any interesting aspect of the code in the package body which needs
925 | to be stated.
926 |
927 | MODIFICATION HISTORY
928 | Date Author Description of Changes
929 | 04-27-2009 scherkas Created for bug 8468532
930 |
931 *=======================================================================*/
932 PROCEDURE GET_BILLED_INSTALLMENT(
933 P_API_VERSION IN NUMBER,
934 P_INIT_MSG_LIST IN VARCHAR2,
935 P_COMMIT IN VARCHAR2,
936 P_VALIDATION_LEVEL IN NUMBER,
937 P_LOAN_ID IN NUMBER,
938 P_AM_SCHED_ID IN NUMBER,
939 X_INVOICES_TBL OUT NOCOPY LNS_BILLING_BATCH_PUB.INVOICE_DETAILS_TBL,
940 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
941 X_MSG_COUNT OUT NOCOPY NUMBER,
942 X_MSG_DATA OUT NOCOPY VARCHAR2);
943
944
945
946 /*========================================================================
947 | PUBLIC PROCEDURE BILL_AND_PAY_OFFCYCLE_BILLS
948 |
949 | DESCRIPTION
950 | This procedure bills and pays manual (offcycle) installment
951 |
952 | PSEUDO CODE/LOGIC
953 |
954 | PARAMETERS
955 | P_API_VERSION IN Standard in parameter
956 | P_INIT_MSG_LIST IN Standard in parameter
957 | P_COMMIT IN Standard in parameter
958 | P_VALIDATION_LEVEL IN Standard in parameter
959 | P_LOAN_ID IN Loan ID
960 | P_BILL_HEADERS_TBL IN LNS_BILLING_BATCH_PUB.BILL_HEADERS_TBL,
961 | P_BILL_LINES_TBL IN LNS_BILLING_BATCH_PUB.BILL_LINES_TBL,
962 | P_CASH_RECEIPTS_TBL IN LNS_BILLING_BATCH_PUB.CASH_RECEIPT_TBL
963 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
964 | X_MSG_COUNT OUT NOCOPY Standard out parameter
965 | X_MSG_DATA OUT NOCOPY Standard out parameter
966 |
967 | KNOWN ISSUES
968 | None
969 |
970 | NOTES
971 | Any interesting aspect of the code in the package body which needs
972 | to be stated.
973 |
974 | MODIFICATION HISTORY
975 | Date Author Description of Changes
976 | 04-28-2009 scherkas Created for bug
977 |
978 *=======================================================================*/
979 PROCEDURE BILL_AND_PAY_OFFCYCLE_BILLS(
980 P_API_VERSION IN NUMBER,
981 P_INIT_MSG_LIST IN VARCHAR2,
982 P_COMMIT IN VARCHAR2,
983 P_VALIDATION_LEVEL IN NUMBER,
984 P_LOAN_ID IN NUMBER,
985 P_BILL_HEADERS_TBL IN LNS_BILLING_BATCH_PUB.BILL_HEADERS_TBL,
986 P_BILL_LINES_TBL IN LNS_BILLING_BATCH_PUB.BILL_LINES_TBL,
987 P_CASH_RECEIPTS_TBL IN LNS_BILLING_BATCH_PUB.CASH_RECEIPT_TBL,
988 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
989 X_MSG_COUNT OUT NOCOPY NUMBER,
990 X_MSG_DATA OUT NOCOPY VARCHAR2);
991
992 -- Bug#6830765
993 PROCEDURE BILL_SING_LOAN_SUBMIT_APPR_FEE(
994 P_API_VERSION IN NUMBER,
995 P_INIT_MSG_LIST IN VARCHAR2,
996 P_COMMIT IN VARCHAR2,
997 P_VALIDATION_LEVEL IN NUMBER,
998 P_LOAN_ID IN NUMBER,
999 X_BILLED_YN OUT NOCOPY VARCHAR2,
1000 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1001 X_MSG_COUNT OUT NOCOPY NUMBER,
1002 X_MSG_DATA OUT NOCOPY VARCHAR2);
1003
1004
1005 /*========================================================================
1006 | PUBLIC FUNCTION IS_BILLING_DISABLED
1007 |
1008 | DESCRIPTION
1009 | This function returns Y/N is loan is ready to bill or not
1010 |
1011 | PSEUDO CODE/LOGIC
1012 |
1013 | PARAMETERS
1014 | P_LOAN_ID IN Loan ID
1015 |
1016 | KNOWN ISSUES
1017 | None
1018 |
1019 | NOTES
1020 | Any interesting aspect of the code in the package body which needs
1021 | to be stated.
1022 |
1023 | MODIFICATION HISTORY
1024 | Date Author Description of Changes
1025 | 16-Nov-2009 MBOLLI Created for bug#9090782
1026 |
1027 *=======================================================================*/
1028 FUNCTION IS_BILLING_DISABLED(P_LOAN_ID IN NUMBER) return VARCHAR2;
1029
1030
1031 /*========================================================================
1032 | PUBLIC PROCEDURE ADJUST_ADD_RECEIVABLE
1033 |
1034 | DESCRIPTION
1035 | This procedure adjusts loans additional receivable in AR
1036 |
1037 | PSEUDO CODE/LOGIC
1038 |
1039 | PARAMETERS
1040 | P_API_VERSION IN Standard in parameter
1041 | P_INIT_MSG_LIST IN Standard in parameter
1042 | P_COMMIT IN Standard in parameter
1043 | P_VALIDATION_LEVEL IN Standard in parameter
1044 | P_LOAN_LINE_ID IN Loan Line ID
1045 | X_RETURN_STATUS OUT NOCOPY Standard out parameter
1046 | X_MSG_COUNT OUT NOCOPY Standard out parameter
1047 | X_MSG_DATA OUT NOCOPY Standard out parameter
1048 |
1049 | KNOWN ISSUES
1050 | None
1051 |
1052 | NOTES
1053 | Any interesting aspect of the code in the package body which needs
1054 | to be stated.
1055 |
1056 | MODIFICATION HISTORY
1057 | Date Author Description of Changes
1058 | 03-05-2010 scherkas Created
1059 |
1060 *=======================================================================*/
1061 PROCEDURE ADJUST_ADD_RECEIVABLE(
1062 P_API_VERSION IN NUMBER,
1063 P_INIT_MSG_LIST IN VARCHAR2,
1064 P_COMMIT IN VARCHAR2,
1065 P_VALIDATION_LEVEL IN NUMBER,
1066 P_LOAN_LINE_ID IN NUMBER,
1067 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1068 X_MSG_COUNT OUT NOCOPY NUMBER,
1069 X_MSG_DATA OUT NOCOPY VARCHAR2);
1070
1071
1072 -- this procedure checks if specified principal amount is available to be billed for the specified date
1073 PROCEDURE VALIDATE_PRIN_AMOUNT(P_LOAN_ID NUMBER, P_PRIN_AMOUNT NUMBER, P_DATE DATE);
1074
1075 -- this function returns available amount to be billed for the specified date
1076 FUNCTION GET_AVAILABLE_PRIN_AMOUNT(P_LOAN_ID NUMBER, P_DATE DATE) return NUMBER;
1077
1078 END LNS_BILLING_BATCH_PUB;
[Home] [Help]