1 PACKAGE LNS_BILLING_UTIL_PUB AUTHID CURRENT_USER AS
2 /* $Header: LNS_BILL_UTIL_S.pls 120.8.12020000.3 2013/02/11 14:51:46 scherkas ship $ */
3
4
5 /*========================================================================
6 | PUBLIC FUNCTION AMOUNT_PAID_YTD
7 |
8 | DESCRIPTION
9 | This procedure calculates next payment due.
10 |
11 | PSEUDO CODE/LOGIC
12 |
13 | PARAMETERS
14 | P_LOAN_ID IN Amount paid for Loan ID
15 | P_LINE_TYPE IN Amount paid for this line type
16 |
17 | KNOWN ISSUES
18 | None
19 |
20 | NOTES
21 |
22 | MODIFICATION HISTORY
23 | Date Author Description of Changes
24 | 30-12-2003 scherkas Created
25 |
26 *=======================================================================*/
27 FUNCTION AMOUNT_PAID_YTD(P_LOAN_ID IN NUMBER, P_LINE_TYPE VARCHAR2) RETURN NUMBER;
28
29
30
31 /*========================================================================
32 | PUBLIC FUNCTION NEXT_PAYMENT_DUE
33 |
34 | DESCRIPTION
35 | This procedure calculates amount paid YTD.
36 |
37 | PSEUDO CODE/LOGIC
38 |
39 | PARAMETERS
40 | P_LOAN_ID IN Last payment number for Loan ID
41 | P_LINE_TYPE Amount due for this line type
42 | P_PAYMENT_NUMBER Amount due for this payment number
43 |
44 | KNOWN ISSUES
45 | None
46 |
47 | NOTES
48 |
49 | MODIFICATION HISTORY
50 | Date Author Description of Changes
51 | 30-12-2003 scherkas Created
52 |
53 *=======================================================================*/
54 FUNCTION NEXT_PAYMENT_DUE(P_LOAN_ID IN NUMBER, P_LINE_TYPE IN VARCHAR2, P_PAYMENT_NUMBER IN NUMBER) RETURN NUMBER;
55
56
57
58 /*========================================================================
59 | PUBLIC FUNCTION AMOUNT_PAID_LAST_YEAR
60 |
61 | DESCRIPTION
62 | This procedure calculates principal paid last year.
63 |
64 | PSEUDO CODE/LOGIC
65 |
66 | PARAMETERS
67 | P_LOAN_ID IN Amount paid for Loan ID
68 | P_LINE_TYPE IN Amount paid for this line type
69 |
70 | KNOWN ISSUES
71 | None
72 |
73 | NOTES
74 |
75 | MODIFICATION HISTORY
76 | Date Author Description of Changes
77 | 30-12-2003 scherkas Created
78 |
79 *=======================================================================*/
80 FUNCTION AMOUNT_PAID_LAST_YEAR(P_LOAN_ID IN NUMBER, P_LINE_TYPE VARCHAR2) RETURN NUMBER;
81
82
83
84 /*========================================================================
85 | PUBLIC FUNCTION LAST_PAYMENT_NUMBER
86 |
87 | DESCRIPTION
88 | This procedure calculates last payment number.
89 |
90 | PSEUDO CODE/LOGIC
91 |
92 | PARAMETERS
93 | P_LOAN_ID IN Last payment number for Loan ID
94 |
95 | KNOWN ISSUES
96 | None
97 |
98 | NOTES
99 |
100 | MODIFICATION HISTORY
101 | Date Author Description of Changes
102 | 30-12-2003 scherkas Created
103 |
104 *=======================================================================*/
105 FUNCTION LAST_PAYMENT_NUMBER(P_LOAN_ID IN NUMBER) RETURN NUMBER;
106
107
108
109
110 /*========================================================================
111 | PUBLIC FUNCTION LAST_PAYMENT_NUMBER
112 |
113 | DESCRIPTION
114 | This procedure calculates last payment number.
115 |
116 | PSEUDO CODE/LOGIC
117 |
118 | PARAMETERS
119 | P_LOAN_ID IN Last payment number for Loan ID
120 | P_PHASE IN Phase
121 |
122 | KNOWN ISSUES
123 | None
124 |
125 | NOTES
126 | Any interesting aspect of the code in the package body which needs
127 | to be stated.
128 |
129 | MODIFICATION HISTORY
130 | Date Author Description of Changes
131 | 01-01-2004 scherkas Created
132 |
133 *=======================================================================*/
134 FUNCTION LAST_PAYMENT_NUMBER(P_LOAN_ID IN NUMBER, P_PHASE IN VARCHAR2) RETURN NUMBER;
135
136
137
138 /*========================================================================
139 | PUBLIC FUNCTION LAST_PAYMENT_NUMBER_EXT
140 |
141 | DESCRIPTION
142 | This procedure calculates last payment number taking in consideration 0-th installment.
143 | If any installment is billed ('0'/'1'/'2'/.....) then it returns the installment ELSE
144 | it returns '-1'.
145 |
146 | PSEUDO CODE/LOGIC
147 |
148 | PARAMETERS
149 | P_LOAN_ID IN Last payment number for Loan ID
150 |
151 | KNOWN ISSUES
152 | None
153 |
154 | NOTES
155 |
156 | MODIFICATION HISTORY
157 | Date Author Description of Changes
158 | 30-12-2003 scherkas Created
159 | 18-Aug-2009 Mbolli Bug#6830765 - Modified with old functionality
160 |
161 *=======================================================================*/
162 FUNCTION LAST_PAYMENT_NUMBER_EXT(P_LOAN_ID IN NUMBER) RETURN NUMBER;
163
164
165
166 /*========================================================================
167 | PUBLIC FUNCTION LAST_PAYMENT_NUMBER_EXT_1
168 |
169 | DESCRIPTION
170 | This procedure calculates last payment number taking in consideration 0-th installment.
171 | If any installment is billed ('0'/'1'/'2'/.....) then it returns the installment ELSE
172 | if any '0th' installments are scheduled(On Activation/On SubmitFor Approval Fees)
173 | then returns '0' ELSE '-1'
174 |
175 | PSEUDO CODE/LOGIC
176 |
177 | PARAMETERS
178 | P_LOAN_ID IN Last payment number for Loan ID
179 |
180 | KNOWN ISSUES
181 | None
182 |
183 | NOTES
184 |
185 | MODIFICATION HISTORY
186 | Date Author Description of Changes
187 | 18-Aug-2009 Mbolli Bug#6830765 - Created
188 |
189 *=======================================================================*/
190 FUNCTION LAST_PAYMENT_NUMBER_EXT_1(P_LOAN_ID IN NUMBER) RETURN NUMBER;
191
192 /*========================================================================
193 | PUBLIC FUNCTION LAST_PAYMENT_NUMBER_EXT_2
194 |
195 | DESCRIPTION
196 | This procedure calculates last payment number and it doesn't consdier '0th' installments
197 | and it returns '-1'.
198 | We created this to use for PREBILL_SINGLE_LOAN in LNS_BILLING_BATCH_PUB package
199 |
200 | PSEUDO CODE/LOGIC
201 |
202 | PARAMETERS
203 | P_LOAN_ID IN Last payment number for Loan ID
204 |
205 | KNOWN ISSUES
206 | None
207 |
208 | NOTES
209 |
210 | MODIFICATION HISTORY
211 | Date Author Description of Changes
212 | 18-Aug-2009 Mbolli Created
213 |
214 *=======================================================================*/
215 FUNCTION LAST_PAYMENT_NUMBER_EXT_2(P_LOAN_ID IN NUMBER) RETURN NUMBER;
216
217 FUNCTION LAST_PAYMENT_NUMBER_EXT_3(P_LOAN_ID IN NUMBER, P_PHASE IN VARCHAR2) RETURN NUMBER;
218
219 /*========================================================================
220 | PUBLIC FUNCTION LAST_AMORTIZATION_SCHED
221 |
222 | DESCRIPTION
223 | This procedure returns last amortization schedule id.
224 |
225 | PSEUDO CODE/LOGIC
226 |
227 | PARAMETERS
228 | P_LOAN_ID IN Last payment number for Loan ID
229 |
230 | KNOWN ISSUES
231 | None
232 |
233 | NOTES
234 |
235 | MODIFICATION HISTORY
236 | Date Author Description of Changes
237 | 30-12-2003 scherkas Created
238 |
239 *=======================================================================*/
240 FUNCTION LAST_AMORTIZATION_SCHED(P_LOAN_ID IN NUMBER) RETURN NUMBER;
241
242
243
244
245 /*========================================================================
246 | PUBLIC FUNCTION AMOUNT_OVERDUE
247 |
248 | DESCRIPTION
249 | This procedure calculates amount overdue.
250 |
251 | PSEUDO CODE/LOGIC
252 |
253 | PARAMETERS
254 | P_LOAN_ID IN Amount paid for Loan ID
255 | P_LINE_TYPE IN Amount paid for this line type
256 |
257 | KNOWN ISSUES
258 | None
259 |
260 | NOTES
261 |
262 | MODIFICATION HISTORY
263 | Date Author Description of Changes
264 | 30-12-2003 scherkas Created
265 |
266 *=======================================================================*/
267 FUNCTION AMOUNT_OVERDUE(P_LOAN_ID IN NUMBER, P_LINE_TYPE VARCHAR2) RETURN NUMBER;
268
269
270
271 /*========================================================================
272 | PUBLIC FUNCTION NUMBER_OVERDUE_BILLS
273 |
274 | DESCRIPTION
275 | This procedure calculates number of overdue bills.
276 |
277 | PSEUDO CODE/LOGIC
278 |
279 | PARAMETERS
280 | P_LOAN_ID IN Amount paid for Loan ID
281 |
282 | KNOWN ISSUES
283 | None
284 |
285 | NOTES
286 | Any interesting aspect of the code in the package body which needs
287 | to be stated.
288 |
289 | MODIFICATION HISTORY
290 | Date Author Description of Changes
291 | 01-01-2004 scherkas Created
292 |
293 *=======================================================================*/
294 FUNCTION NUMBER_OVERDUE_BILLS(P_LOAN_ID IN NUMBER) RETURN NUMBER;
295
296
297
298 /*========================================================================
299 | PUBLIC FUNCTION LAST_OVERDUE_DATE
300 |
301 | DESCRIPTION
302 | This procedure gets last overdue date.
303 |
304 | PSEUDO CODE/LOGIC
305 |
306 | PARAMETERS
307 | P_LOAN_ID IN Loan ID
308 |
309 | KNOWN ISSUES
310 | None
311 |
312 | NOTES
313 | Any interesting aspect of the code in the package body which needs
314 | to be stated.
315 |
316 | MODIFICATION HISTORY
317 | Date Author Description of Changes
318 | 01-01-2004 scherkas Created
319 |
320 *=======================================================================*/
321 FUNCTION LAST_OVERDUE_DATE(P_LOAN_ID IN NUMBER) RETURN DATE;
322
323
324
325 /*========================================================================
326 | PUBLIC FUNCTION OLDEST_OVERDUE_DATE
327 |
328 | DESCRIPTION
329 | This procedure gets oldest overdue date.
330 |
331 | PSEUDO CODE/LOGIC
332 |
333 | PARAMETERS
334 | P_LOAN_ID IN Loan ID
335 |
336 | KNOWN ISSUES
337 | None
338 |
339 | NOTES
340 | Any interesting aspect of the code in the package body which needs
341 | to be stated.
342 |
343 | MODIFICATION HISTORY
344 | Date Author Description of Changes
345 | 01-01-2004 scherkas Created
346 |
347 *=======================================================================*/
348 FUNCTION OLDEST_OVERDUE_DATE(P_LOAN_ID IN NUMBER) RETURN DATE;
349
350
351
352 /*========================================================================
353 | PUBLIC FUNCTION LAST_PAYMENT_AMOUNT
354 |
355 | DESCRIPTION
356 | This procedure gets last payment amount.
357 |
358 | PSEUDO CODE/LOGIC
359 |
360 | PARAMETERS
361 | P_LOAN_ID IN Amount for Loan ID
362 |
363 | KNOWN ISSUES
364 | None
365 |
366 | NOTES
367 | Any interesting aspect of the code in the package body which needs
368 | to be stated.
369 |
370 | MODIFICATION HISTORY
371 | Date Author Description of Changes
372 | 01-01-2004 scherkas Created
373 |
374 *=======================================================================*/
375 FUNCTION LAST_PAYMENT_AMOUNT(P_LOAN_ID IN NUMBER) RETURN NUMBER;
376
377
378
379 /*========================================================================
380 | PUBLIC FUNCTION LAST_PAYMENT_DATE
381 |
382 | DESCRIPTION
383 | This procedure gets last payment date.
384 |
385 | PSEUDO CODE/LOGIC
386 |
387 | PARAMETERS
388 | P_LOAN_ID IN Amount for Loan ID
389 |
390 | KNOWN ISSUES
391 | None
392 |
393 | NOTES
394 | Any interesting aspect of the code in the package body which needs
395 | to be stated.
396 |
397 | MODIFICATION HISTORY
398 | Date Author Description of Changes
399 | 01-01-2004 scherkas Created
400 |
401 *=======================================================================*/
402 FUNCTION LAST_PAYMENT_DATE(P_LOAN_ID IN NUMBER) RETURN DATE;
403
404 FUNCTION LAST_PAYMENT_DATE(P_LOAN_ID IN NUMBER, P_PHASE IN VARCHAR2) RETURN DATE;
405
406
407
408 /*========================================================================
409 | PUBLIC FUNCTION BALANCE_BY_ACT_DATE
410 |
411 | DESCRIPTION
412 | This procedure returns loan balance by date.
413 |
414 | PSEUDO CODE/LOGIC
415 |
416 | PARAMETERS
417 | P_LOAN_ID IN Loan ID
418 | P_DATE IN Date
419 |
420 | KNOWN ISSUES
421 | None
422 |
423 | NOTES
424 | Any interesting aspect of the code in the package body which needs
425 | to be stated.
426 |
427 | MODIFICATION HISTORY
428 | Date Author Description of Changes
429 | 02-01-2006 scherkas Created
430 |
431 *=======================================================================*/
432 FUNCTION LOAN_BALANCE_BY_DATE(P_LOAN_ID IN NUMBER, P_DATE IN DATE) RETURN NUMBER;
433
434 /*========================================================================
435 | PUBLIC FUNCTION GET_LOAN_REMAIN_AMOUNT
436 |
437 | DESCRIPTION
438 | This function returns funded_amount for the loans in staus
439 | ('INCOMPLETE','REJECTED','DELETED','PENDING','APPROVED','IN_FUNDING','FUNDING_ERROR')
440 | and remaining amount for the other loan statuses.
441 |
442 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
443 | None
444 |
445 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
446 | LogMessage
447 |
448 | PARAMETERS
449 | P_LOAN_ID IN Loan ID
450 |
451 | KNOWN ISSUES
452 | None
453 |
454 | NOTES
455 | Any interesting aspect of the code in the package body which needs
456 | to be stated.
457 |
458 | MODIFICATION HISTORY
459 | Date Author Description of Changes
460 | 22-APR-2009 MBOLLI Created for bug#8545962
461 |
462 *=======================================================================*/
466 | PUBLIC FUNCTION LAST_INSTALLMENT_OVERDUE_DATE
463 FUNCTION GET_LOAN_REMAIN_AMOUNT(P_LOAN_ID IN NUMBER) return NUMBER;
464
465 /*========================================================================
467 |
468 | DESCRIPTION
469 | This procedure gets last overdue date.
470 |
471 | PSEUDO CODE/LOGIC
472 |
473 | PARAMETERS
474 | P_LOAN_ID IN Loan ID
475 |
476 | KNOWN ISSUES
477 | None
478 |
479 | NOTES
480 | Any interesting aspect of the code in the package body which needs
481 | to be stated.
482 |
483 | MODIFICATION HISTORY
484 | Date Author Description of Changes
485 | 13-Jul-2009 mbolli Created
486 |
487 *=======================================================================*/
488 FUNCTION LAST_INSTALLMENT_OVERDUE_DATE(P_LOAN_ID IN NUMBER) RETURN DATE;
489
490
491 /*========================================================================
492 | PUBLIC PROCEDURE VALIDATE_AND_DEFAULT_GL_DATE
493 |
494 | DESCRIPTION
495 | This procedure validates and defaults gl_date. Created for bug 8859462
496 |
497 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
498 | None
499 |
500 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
501 | None
502 |
503 | PARAMETERS
504 | p_gl_date IN GL_DATE
505 | p_trx_date IN TRX_DATE
506 | p_set_of_books_id IN set_of_books_id
507 | x_default_gl_date OUT New GL_DATE
508 |
509 | KNOWN ISSUES
510 | None
511 |
512 | NOTES
513 | Any interesting aspect of the code in the package body which needs
514 | to be stated.
515 |
516 | MODIFICATION HISTORY
517 | Date Author Description of Changes
518 | 21-Sep-2009 scherkas Created
519 |
520 *=======================================================================*/
521 PROCEDURE VALIDATE_AND_DEFAULT_GL_DATE(p_gl_date in date,
522 p_trx_date in date,
523 p_set_of_books_id in number,
524 x_default_gl_date out NOCOPY date);
525
526
527 PROCEDURE GET_TRX_DATE(P_LOAN_ID IN NUMBER,
528 P_DUE_DATE IN DATE,
529 X_TRX_DATE OUT NOCOPY DATE,
530 X_RA_TERM_ID OUT NOCOPY NUMBER);
531
532
533 END LNS_BILLING_UTIL_PUB; -- Package spec