1 PACKAGE LNS_FINANCIALS AUTHID CURRENT_USER AS
2 /* $Header: LNS_FINANCIAL_S.pls 120.25.12020000.2 2012/07/19 20:57:04 scherkas ship $ */
3
4 /*========================================================================+
5 | Declare PUBLIC Data Types and Variables
6 +========================================================================*/
7
8 /*========================================================================+
9 | Interest Rate Rec is for capturing interest rate details
10 | Rate Schedule Table is to help handle multi-rate loans
11 +========================================================================*/
12 TYPE INTEREST_RATE_REC IS RECORD(RATE_ID NUMBER
13 ,BEGIN_DATE DATE
14 ,END_DATE DATE
15 ,ANNUAL_RATE NUMBER
16 ,SPREAD NUMBER
17 ,BEGIN_INSTALLMENT_NUMBER NUMBER
18 ,END_INSTALLMENT_NUMBER NUMBER
19 ,INTEREST_ONLY_FLAG VARCHAR2(1)
20 ,PHASE VARCHAR2(30)
21 ,FLOATING_FLAG VARCHAR2(1));
22
23 TYPE RATE_SCHEDULE_TBL IS TABLE OF INTEREST_RATE_REC INDEX BY BINARY_INTEGER;
24
25 /*========================================================================+
26 | This is the main record type for amortization
27 | The runAmortization procedures will return this information to UI
28 +========================================================================*/
29 TYPE AMORTIZATION_REC IS RECORD(INSTALLMENT_NUMBER NUMBER
30 ,DUE_DATE DATE
31 ,PERIOD_START_DATE DATE
32 ,PERIOD_END_DATE DATE
33 ,PRINCIPAL_AMOUNT NUMBER
34 ,INTEREST_AMOUNT NUMBER
35 ,NORMAL_INT_AMOUNT NUMBER
36 ,ADD_PRIN_INT_AMOUNT NUMBER
37 ,ADD_INT_INT_AMOUNT NUMBER
38 ,PENAL_INT_AMOUNT NUMBER
39 ,FEE_AMOUNT NUMBER
40 ,OTHER_AMOUNT NUMBER
41 ,BEGIN_BALANCE NUMBER
42 ,END_BALANCE NUMBER
43 ,TOTAL NUMBER
44 ,INTEREST_CUMULATIVE NUMBER
45 ,PRINCIPAL_CUMULATIVE NUMBER
46 ,FEES_CUMULATIVE NUMBER
47 ,OTHER_CUMULATIVE NUMBER
48 ,RATE_ID NUMBER
49 ,RATE_UNADJ NUMBER
50 ,RATE_CHANGE_FREQ VARCHAR2(30)
51 ,SOURCE VARCHAR2(30)
52 ,GRAND_TOTAL_FLAG VARCHAR2(1)
53 ,UNPAID_PRIN NUMBER
54 ,UNPAID_INT NUMBER
55 ,INTEREST_RATE NUMBER
56 ,FUNDED_AMOUNT NUMBER
57 ,NORMAL_INT_DETAILS VARCHAR2(2000)
58 ,ADD_PRIN_INT_DETAILS VARCHAR2(2000)
59 ,ADD_INT_INT_DETAILS VARCHAR2(2000)
60 ,PENAL_INT_DETAILS VARCHAR2(2000)
61 ,DISBURSEMENT_AMOUNT NUMBER
62 ,PERIOD VARCHAR2(200)
63 ,PREV_DEFERRED_INT_AMOUNT NUMBER
64 ,DEFERRED_INT_AMOUNT NUMBER
65 ,DEFERRED_INT_DETAILS VARCHAR2(2000)
66 ,PREV_CAP_INT_AMOUNT NUMBER
67 ,CURR_CAP_INT_AMOUNT NUMBER
68 ,CAP_INT_AMOUNT NUMBER
69 ,CAP_INT_DETAILS VARCHAR2(2000)
70 ,EARLY_PAY_CR_AMOUNT NUMBER
71 ,EARLY_PAY_CR_DETAILS VARCHAR2(2000));
72
73 TYPE AMORTIZATION_TBL IS TABLE OF AMORTIZATION_REC INDEX BY BINARY_INTEGER;
74
75 /*========================================================================+
76 | This record type will contain all the pertinent properties for a loan
77 | includes reamortization , currency, arrears, amounts
78 |+========================================================================*/
79 TYPE LOAN_DETAILS_REC IS RECORD(LOAN_ID NUMBER -- loan id
80 ,LOAN_TERM NUMBER -- term of the loan
81 ,LOAN_TERM_PERIOD VARCHAR2(30) -- term period of the loan
82 ,AMORTIZED_TERM NUMBER -- amotrtized term of the loan
83 ,AMORTIZED_TERM_PERIOD VARCHAR2(30) -- amotrtized term period of the loan
84 ,AMORTIZATION_FREQUENCY VARCHAR2(30)
85 ,PAYMENT_FREQUENCY VARCHAR2(30)
86 ,FIRST_PAYMENT_DATE DATE
87 ,LOAN_START_DATE DATE
88 ,REQUESTED_AMOUNT NUMBER
89 ,FUNDED_AMOUNT NUMBER
90 ,REMAINING_BALANCE NUMBER
91 ,PRINCIPAL_PAID_TO_DATE NUMBER
92 ,INTEREST_PAID_TO_DATE NUMBER
93 ,FEES_PAID_TO_DATE NUMBER
94 ,UNPAID_PRINCIPAL NUMBER
95 ,UNPAID_INTEREST NUMBER
96 ,UNBILLED_PRINCIPAL NUMBER
97 ,BILLED_PRINCIPAL NUMBER
98 ,MATURITY_DATE DATE
99 ,NUMBER_INSTALLMENTS NUMBER -- based off of TERM + TERM_PERIOD
100 ,NUM_AMORTIZATION_INTERVALS NUMBER -- based off of AMORTIZATION_TERM + AMORTIZATION_TERM_PERIOD
101 ,REAMORTIZE_OVERPAY VARCHAR2(1)
102 ,REAMORTIZE_UNDERPAY VARCHAR2(1)
103 ,REAMORTIZE_WITH_INTEREST VARCHAR2(1) -- for future use
104 ,REAMORTIZE_AMOUNT NUMBER
105 ,REAMORTIZE_FROM_INSTALLMENT NUMBER
106 ,REAMORTIZE_TO_INSTALLMENT NUMBER -- for future use
107 ,LAST_INSTALLMENT_BILLED NUMBER
108 ,DAY_COUNT_METHOD VARCHAR2(30) -- day count methodology
109 ,PAY_IN_ARREARS VARCHAR2(1) -- Y/N for pay in arrears
110 ,PAY_IN_ARREARS_BOOLEAN BOOLEAN -- boolean for pay in arrears
111 ,CUSTOM_SCHEDULE VARCHAR2(1) -- Y/N for custom payment schedule
112 ,LOAN_STATUS VARCHAR2(30) -- loan status
113 ,LAST_INTEREST_ACCRUAL DATE -- last interest accrual date
114 ,LAST_ACTIVITY VARCHAR2(30) -- for future use
115 ,LAST_ACTIVITY_DATE DATE -- last activity date on the loan
116 ,LOAN_CURRENCY VARCHAR2(15) -- loan currency
117 ,CURRENCY_PRECISION NUMBER -- currency precision
118 ,OPEN_TERM NUMBER -- open term
119 ,OPEN_TERM_PERIOD VARCHAR2(30) -- open term period
120 ,OPEN_PAYMENT_FREQUENCY VARCHAR2(30) -- payment freq during open phase
121 ,OPEN_FIRST_PAYMENT_DATE DATE -- open phase first pay date
122 ,OPEN_START_DATE DATE -- begin date of open phase
123 ,OPEN_MATURITY_DATE DATE -- maturity date of open phase
124 ,LOAN_PHASE VARCHAR2(30) -- OPEN or TERM for phase of the loan
125 ,BALLOON_PAYMENT_TYPE VARCHAR2(30) -- TERM or AMOUNT
126 ,BALLOON_PAYMENT_AMOUNT NUMBER -- if balloon type = AMOUNT then the actual amount
127 ,AMORTIZED_AMOUNT NUMBER -- amortized amount
128 ,RATE_TYPE VARCHAR2(30) -- fixed or variable
129 ,OPEN_RATE_CHG_FREQ VARCHAR2(30) -- how often rate changes during open phase
130 ,OPEN_INDEX_RATE_ID NUMBER -- index_rate_id
131 ,OPEN_INDEX_DATE DATE -- open index date
132 ,OPEN_CEILING_RATE NUMBER -- open ceiling rate
133 ,OPEN_FLOOR_RATE NUMBER -- open floor rate
134 ,OPEN_FIRST_PERCENT_INCREASE NUMBER -- open first percentage increase
135 ,OPEN_ADJ_PERCENT_INCREASE NUMBER -- open percentage increase btwn adjustments
136 ,OPEN_LIFE_PERCENT_INCREASE NUMBER -- open lifetime max adjustment for interest
137 ,TERM_RATE_CHG_FREQ VARCHAR2(30) -- how often rate changes during term phase
138 ,TERM_INDEX_RATE_ID NUMBER -- index_rate_id
139 ,TERM_INDEX_DATE DATE -- term index date
140 ,TERM_CEILING_RATE NUMBER -- term ceiling rate
141 ,TERM_FLOOR_RATE NUMBER -- term floor rate
142 ,TERM_FIRST_PERCENT_INCREASE NUMBER -- term first percentage increase
143 ,TERM_ADJ_PERCENT_INCREASE NUMBER -- term percentage increase btwn adjustments
144 ,TERM_LIFE_PERCENT_INCREASE NUMBER -- term lifetime max adjustment for interest
145 ,OPEN_TO_TERM_FLAG VARCHAR2(1)
146 ,OPEN_TO_TERM_EVENT VARCHAR2(30)
147 ,MULTIPLE_FUNDING_FLAG VARCHAR2(1)
148 ,SECONDARY_STATUS VARCHAR2(30)
149 ,OPEN_PROJECTED_INTEREST_RATE NUMBER -- open projected interest rate
150 ,TERM_PROJECTED_INTEREST_RATE NUMBER -- term projected interest rate
151 ,INITIAL_INTEREST_RATE NUMBER --
152 ,LAST_INTEREST_RATE NUMBER --
153 ,FIRST_RATE_CHANGE_DATE DATE
154 ,NEXT_RATE_CHANGE_DATE DATE
155 ,CALCULATION_METHOD VARCHAR2(30) -- interest calc method: simple, compound
156 ,INTEREST_COMPOUNDING_FREQ VARCHAR2(30)
157 ,PAYMENT_CALC_METHOD VARCHAR2(30) -- payment calc method: equal payment, equal principal
158 ,ORIG_PAY_CALC_METHOD VARCHAR2(30)
159 ,PRIN_FIRST_PAY_DATE DATE -- principal first payment date; used with PAYMENT_CALC_METHOD=SEPERATE_PRIN_INT
160 ,PRIN_PAYMENT_FREQUENCY VARCHAR2(30) -- principal payment freq; used with PAYMENT_CALC_METHOD=SEPERATE_PRIN_INT
161 ,PRIN_NUMBER_INSTALLMENTS NUMBER -- principal number of installments; used with PAYMENT_CALC_METHOD=SEPERATE_PRIN_INT
162 ,PRIN_AMORT_INSTALLMENTS NUMBER -- principal number of amortized installments; used with PAYMENT_CALC_METHOD=SEPERATE_PRIN_INT
163 ,PRIN_PAY_IN_ARREARS VARCHAR2(1) -- Y/N for principal pay in arrears; used with PAYMENT_CALC_METHOD=SEPERATE_PRIN_INT
164 ,PRIN_PAY_IN_ARREARS_BOOL BOOLEAN -- boolean for principal pay in arrears
165 ,EXTEND_FROM_INSTALLMENT NUMBER
166 ,ORIG_NUMBER_INSTALLMENTS NUMBER
167 ,PENAL_INT_RATE NUMBER
168 ,PENAL_INT_GRACE_DAYS NUMBER
169 ,REAMORTIZE_ON_FUNDING VARCHAR2(30)
170 ,ADD_REQUESTED_AMOUNT NUMBER
171 ,CALC_ADD_INT_UNPAID_PRIN VARCHAR2(1)
172 ,CALC_ADD_INT_UNPAID_INT VARCHAR2(1)
173 ,CAPITALIZE_INT VARCHAR2(1)
174 ,FLUCTUATE_EQ_PAY_AMOUNT VARCHAR2(1)
175 );
176
177 /*========================================================================+
178 | this is for the old payoff calculation
179 +========================================================================*/
183 ,TOTAL_INTEREST_REMAINING NUMBER
180 TYPE PAYOFF_REC is RECORD(TOTAL_PRINCIPAL_REMAINING NUMBER
181 ,UNPAID_PRINCIPAL NUMBER -- billed but not paid
182 ,UNBILLED_PRINCIPAL NUMBER -- not billed
184 ,UNPAID_INTEREST NUMBER -- billed but not paid
185 ,ADDITIONAL_INTEREST_DUE NUMBER -- this is the main calculation needs
186 ,TOTAL_FEES_REMAINING NUMBER
187 ,UNPAID_FEES NUMBER -- billed but not paid
188 ,ADDITIONAL_FEES_DUE NUMBER -- for future
189 ,DUE_DATE DATE);
190
191 TYPE PAYOFF_TBL is TABLE OF PAYOFF_REC INDEX BY BINARY_INTEGER;
192
193 /*========================================================================+
194 | new record type for payoff enhancements
195 | tbl type is extensible by "PURPOSE"
196 +========================================================================*/
197 TYPE PAYOFF_REC2 is record(PAYOFF_PURPOSE VARCHAR2(30)
198 ,BILLED_AMOUNT NUMBER
199 ,UNBILLED_AMOUNT NUMBER
200 ,TOTAL_AMOUNT NUMBER );
201
202 TYPE PAYOFF_TBL2 is TABLE OF PAYOFF_REC2 INDEX BY BINARY_INTEGER;
203
204 /*========================================================================+
205 | used to calculate average daily balance
206 +========================================================================*/
207 TYPE LOAN_ACTIVITY_REC is record(ACTIVITY_DATE DATE
208 ,ACTIVITY_AMOUNT NUMBER
209 ,ENDING_BALANCE NUMBER
210 ,DAYS_AT_BALANCE NUMBER);
211
212 TYPE LOAN_ACTIVITY_TBL is table of LOAN_ACTIVITY_REC index by binary_integer;
213
214 /*========================================================================+
215 | types for building payment schedule
216 +========================================================================*/
217 TYPE PAYMENT_SCHEDULE is record(PERIOD_BEGIN_DATE DATE
218 ,PERIOD_END_DATE DATE);
219
220 TYPE PAYMENT_SCHEDULE_TBL is table of PAYMENT_SCHEDULE index by binary_integer;
221
222 /*========================================================================+
223 | helper types
224 +========================================================================*/
225 TYPE DATE_TBL IS TABLE OF DATE INDEX BY BINARY_INTEGER;
226 type amount_tbl is table of number index by Binary_Integer;
227 type vchar_tbl is table of varchar2(30) index by binary_integer;
228
229 /*========================================================================+
230 | fees types
231 +========================================================================*/
232 TYPE FEES_REC IS RECORD(FEE_ID NUMBER
233 ,FEE_NAME VARCHAR2(50)
234 ,FEE_AMOUNT NUMBER
235 ,FEE_INSTALLMENT NUMBER
236 ,FEE_DESCRIPTION VARCHAR2(250)
237 ,FEE_SCHEDULE_ID NUMBER
238 ,FEE_WAIVABLE_FLAG VARCHAR2(1)
239 ,WAIVE_AMOUNT NUMBER
240 ,BILLED_FLAG VARCHAR2(1)
241 ,ACTIVE_FLAG VARCHAR2(1));
242
243 TYPE FEES_TBL IS TABLE OF FEES_REC INDEX BY BINARY_INTEGER;
244
245 procedure shiftLoan(p_loan_id in number
246 ,p_init_msg_list IN VARCHAR2
247 ,p_commit IN VARCHAR2
248 ,x_return_status OUT NOCOPY VARCHAR2
249 ,x_msg_count OUT NOCOPY NUMBER
250 ,x_msg_data OUT NOCOPY VARCHAR2);
251
252 procedure shiftLoanDates(p_loan_id in number
253 ,p_new_start_date in date
254 ,p_phase in varchar2
255 ,x_loan_details out NOCOPY lns_financials.loan_details_rec
256 ,x_dates_shifted_flag OUT NOCOPY VARCHAR2
257 ,x_return_status OUT NOCOPY VARCHAR2
258 ,x_msg_count OUT NOCOPY NUMBER
259 ,x_msg_data OUT NOCOPY VARCHAR2);
260
261 function getAverageDailyBalance(p_loan_id number
262 ,p_term_id number
263 ,p_from_date date
264 ,p_to_date date
265 ,p_calc_method number) return number;
266
267 procedure getWeightedBalance(p_loan_id in number
268 ,p_from_date in date
269 ,p_to_date in date
270 ,p_calc_method in varchar2
271 ,p_phase in varchar2
272 ,p_day_count_method in varchar2
273 ,p_adj_amount in number
274 ,x_wtd_balance out NOCOPY number
275 ,x_begin_balance out NOCOPY number
276 ,x_end_balance out NOCOPY number);
277 /*
278 function getWeightedBalance(p_loan_id number
279 ,p_from_date date
280 ,p_to_date date
281 ,p_calc_method varchar2
282 ,p_phase varchar2
283 ,p_day_count_method varchar2) return number;
284 */
285 procedure validatePayoff(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC
286 ,p_payoff_date in date
287 ,x_return_status OUT NOCOPY VARCHAR2
288 ,x_msg_count OUT NOCOPY NUMBER
292 procedure calculatePayoff(p_api_version IN NUMBER
289 ,x_msg_data OUT NOCOPY VARCHAR2);
290
291 -- payoff calculation
293 ,p_init_msg_list IN VARCHAR2
294 ,p_loan_id in number
295 ,p_payoff_date in date
296 ,x_payoff_tbl OUT NOCOPY LNS_FINANCIALS.PAYOFF_TBL2
297 ,x_return_status OUT NOCOPY VARCHAR2
298 ,x_msg_count OUT NOCOPY NUMBER
299 ,x_msg_data OUT NOCOPY VARCHAR2);
300
301 function getLoanDetails(p_loan_id in number
302 ,p_based_on_terms in varchar2
303 ,p_phase in varchar2) return LNS_FINANCIALS.LOAN_DETAILS_REC;
304
305 function compoundInterest(p_rate in number
306 ,p_period_value in number
307 ,p_period_type in varchar2) return number;
308
309 function getAnnualRate(p_loan_Id in number) return number;
310
311 function getActiveRate(p_loan_id in number) return number;
312
313 function getRemainingBalance(p_loan_id in number) return number;
314
315 function getPeriodicRate(p_payment_freq in varchar2
316 ,p_period_start_date in date
317 ,p_period_end_date in date
318 ,p_annualized_rate in number
319 ,p_days_count_method in varchar2
320 ,p_target in varchar2) return number;
321
322 function getWeightedRate(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC
323 ,p_start_date in date
324 ,p_end_date in date
325 ,p_rate_tbl in LNS_FINANCIALS.RATE_SCHEDULE_TBL) return number;
326
327 procedure amortizeEPLoan(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC
328 ,p_rate_schedule in LNS_FINANCIALS.RATE_SCHEDULE_TBL
329 ,p_based_on_terms IN VARCHAR2
330 ,p_installment_number in number
331 ,x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL);
332
333 procedure amortizeLoan(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC
334 ,p_rate_schedule in LNS_FINANCIALS.RATE_SCHEDULE_TBL
335 ,p_based_on_terms IN VARCHAR2
336 ,p_installment_number in number
337 ,x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL);
338
339 procedure amortizeLoan(p_loan_Id in number
340 ,p_based_on_terms IN VARCHAR2
341 ,p_installment_number in number
342 ,x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL);
343
344 procedure loanProjection(p_loan_details in LNS_FINANCIALS.LOAN_DETAILS_REC
345 ,p_based_on_terms in varchar2
346 ,p_rate_schedule in LNS_FINANCIALS.RATE_SCHEDULE_TBL
347 ,x_loan_amort_tbl out nocopy LNS_FINANCIALS.AMORTIZATION_TBL);
348
349 procedure runOpenProjection(p_init_msg_list IN VARCHAR2
350 ,p_loan_ID IN NUMBER
351 ,p_based_on_terms IN VARCHAR2
352 ,x_amort_tbl OUT NOCOPY LNS_FINANCIALS.AMORTIZATION_TBL
353 ,x_return_status OUT NOCOPY VARCHAR2
354 ,x_msg_count OUT NOCOPY NUMBER
355 ,x_msg_data OUT NOCOPY VARCHAR2);
356
357 procedure validateLoan(p_api_version IN NUMBER
358 ,p_init_msg_list IN VARCHAR2
359 ,p_loan_ID IN NUMBER
360 ,x_return_status OUT NOCOPY VARCHAR2
361 ,x_msg_count OUT NOCOPY NUMBER
362 ,x_msg_data OUT NOCOPY VARCHAR2);
363
364 procedure runAmortization(p_api_version IN NUMBER
365 ,p_init_msg_list IN VARCHAR2
366 ,p_commit IN VARCHAR2
367 ,p_loan_ID IN NUMBER
368 ,p_based_on_terms IN VARCHAR2
369 ,x_amort_tbl OUT NOCOPY LNS_FINANCIALS.AMORTIZATION_TBL
370 ,x_return_status OUT NOCOPY VARCHAR2
371 ,x_msg_count OUT NOCOPY NUMBER
372 ,x_msg_data OUT NOCOPY VARCHAR2);
373
374 function getRateSchedule(p_loan_id in number
375 ,p_phase in varchar2) return LNS_FINANCIALS.RATE_SCHEDULE_TBL;
376
377 --function getRateSchedule(p_loan_id in number) return LNS_FINANCIALS.RATE_SCHEDULE_TBL;
378
379 function getRateDetails(p_installment IN NUMBER
380 ,p_rate_tbl IN LNS_FINANCIALS.RATE_SCHEDULE_TBL) return LNS_FINANCIALS.INTEREST_RATE_REC;
381
382 function getRateDetails(p_date in date
383 ,p_rate_tbl in LNS_FINANCIALS.RATE_SCHEDULE_TBL) return LNS_FINANCIALS.INTEREST_RATE_REC;
384
385 function termlyPayment(p_termly_amount in number
386 ,p_annual_rate in number
387 ,p_loan_amount in number
388 ,p_payments_per_year in number
389 ,p_period_type in varchar2) return number;
390
391 procedure getInstallment(p_api_version IN NUMBER
392 ,p_init_msg_list IN VARCHAR2
393 ,p_commit IN VARCHAR2
394 ,p_loan_Id IN NUMBER
395 ,p_installment_number IN NUMBER
396 ,x_amortization_rec OUT NOCOPY LNS_FINANCIALS.AMORTIZATION_REC
397 ,x_fees_tbl OUT NOCOPY LNS_FINANCIALS.FEES_TBL
401
398 ,x_return_status OUT NOCOPY VARCHAR2
399 ,x_msg_count OUT NOCOPY NUMBER
400 ,x_msg_data OUT NOCOPY VARCHAR2);
402 procedure getOpenInstallment(p_init_msg_list IN VARCHAR2
403 ,p_loan_Id in number
404 ,p_installment_number in number
405 ,x_amortization_rec OUT NOCOPY LNS_FINANCIALS.AMORTIZATION_REC
406 ,x_fees_tbl OUT NOCOPY LNS_FINANCIALS.FEES_TBL
407 ,x_return_status OUT NOCOPY VARCHAR2
408 ,x_msg_count OUT NOCOPY NUMBER
409 ,x_msg_data OUT NOCOPY VARCHAR2);
410
411 function getRatesTable(p_index_rate_id in number
412 ,p_index_date in date
413 ,p_rate_change_frequency in varchar2
414 ,p_maturity_date in date) return LNS_FINANCIALS.RATE_SCHEDULE_TBL;
415
416
417 procedure preProcessInstallment(p_api_version IN NUMBER
418 ,p_init_msg_list IN VARCHAR2
419 ,p_commit IN VARCHAR2
420 ,p_loan_ID IN NUMBER
421 ,p_installment_number IN NUMBER
422 ,x_amortization_rec OUT NOCOPY LNS_FINANCIALS.AMORTIZATION_REC
423 ,x_return_status OUT NOCOPY VARCHAR2
424 ,x_msg_count OUT NOCOPY NUMBER
425 ,x_msg_data OUT NOCOPY VARCHAR2);
426
427 procedure preProcessOpenInstallment(p_init_msg_list IN VARCHAR2
428 ,p_commit IN VARCHAR2
429 ,p_loan_ID IN NUMBER
430 ,p_installment_number IN NUMBER
431 ,x_amortization_rec OUT NOCOPY LNS_FINANCIALS.AMORTIZATION_REC
432 ,x_return_status OUT NOCOPY VARCHAR2
433 ,x_msg_count OUT NOCOPY NUMBER
434 ,x_msg_data OUT NOCOPY VARCHAR2);
435
436 function calculateEPPayment(p_loan_amount in number
437 ,p_num_intervals in number
438 ,p_ending_balance in number
439 ,p_pay_in_arrears in boolean) return number;
440
441 function calculatePayment(p_loan_amount in number
442 ,p_periodic_rate in number
443 ,p_num_intervals in number
444 ,p_ending_balance in number
445 ,p_pay_in_arrears in boolean) return number;
446
447 function calculateInterest(p_amount in number
448 ,p_periodic_rate in number
449 ,p_compounding_period in varchar2) return number;
450
451 function calculateInterestRate(p_initial_rate in number
452 ,p_rate_to_compare in number
453 ,p_last_period_rate in number
454 ,p_max_first_adjustment in number
455 ,p_max_period_adjustment in number
456 ,p_max_lifetime_adjustment in number
457 ,p_ceiling_rate in number
458 ,p_floor_rate in number
459 ,p_installment_number in number) return number;
460
461 procedure floatingRatePostProcessing(p_loan_id IN NUMBER
462 ,p_init_msg_list IN VARCHAR2
463 ,p_commit IN VARCHAR2
464 ,p_installment_number IN NUMBER
465 ,p_period_begin_date IN DATE
466 ,p_interest_adjustment_freq IN VARCHAR2
467 ,p_annualized_interest_rate IN NUMBER
468 ,p_rate_id IN OUT NOCOPY NUMBER
469 ,p_phase IN VARCHAR2
470 ,x_return_status OUT NOCOPY VARCHAR2
471 ,x_msg_count OUT NOCOPY NUMBER
472 ,x_msg_data OUT NOCOPY VARCHAR2);
473
474
475 function getAPR(p_loan_id in number
476 ,p_term_id in number
477 ,p_actual_flag in varchar2) return number;
478
479 --function frequency2ppy(p_frequency in varchar2) return number;
480
481 function getCompoundPeriodicRate(p_compound_freq in varchar2
482 ,p_payment_freq in varchar2
483 ,p_annualized_rate in number
484 ,p_period_start_date in date
485 ,p_period_end_date in date
486 ,p_days_count_method in varchar2
487 ,p_target in varchar2) return number;
488
489 -- This procedure calculates normal interest
490 procedure CALC_NORM_INTEREST(p_loan_id in number,
491 p_calc_method in varchar2,
492 p_period_start_date in date,
493 p_period_end_date in date,
494 p_interest_rate in number,
495 p_day_count_method in varchar2,
496 p_payment_freq in varchar2,
497 p_compound_freq in varchar2,
501 x_norm_int_details out NOCOPY varchar2);
498 p_adj_amount in number,
499 p_CAP_AMOUNT in number default 0,
500 x_norm_interest out NOCOPY number,
502
503 -- This procedure calculates additional and penal interest
504 procedure CALC_ADD_INTEREST(p_loan_id in number,
505 p_calc_method in varchar2,
506 p_period_start_date in date,
507 p_period_end_date in date,
508 p_interest_rate in number,
509 p_day_count_method in varchar2,
510 p_payment_freq in varchar2,
511 p_compound_freq in varchar2,
512 p_penal_int_rate in number,
513 p_prev_grace_end_date in date,
514 p_grace_start_date in date,
515 p_grace_end_date in date,
516 p_target in varchar2,
517 x_add_interest out NOCOPY number,
518 x_penal_interest out NOCOPY number,
519 x_add_int_details out NOCOPY varchar2,
520 x_penal_int_details out NOCOPY varchar2);
521
522 function getFundedAmount(p_loan_id in number, p_date in date, p_based_on_terms varchar2) return number;
523
524 procedure calcLoanRemainingAmounts(p_api_version IN NUMBER
525 ,p_init_msg_list IN VARCHAR2
526 ,p_loan_id in number
527 ,p_date in date
528 ,p_reason in varchar2
529 ,x_payoff_tbl OUT NOCOPY LNS_FINANCIALS.PAYOFF_TBL2
530 ,x_return_status OUT NOCOPY VARCHAR2
531 ,x_msg_count OUT NOCOPY NUMBER
532 ,x_msg_data OUT NOCOPY VARCHAR2);
533
534 procedure CALC_EARLY_PAY_CR(p_loan_id in number,
535 p_calc_method in varchar2,
536 p_installment in number,
537 p_interest_rate in number,
538 p_day_count_method in varchar2,
539 p_payment_freq in varchar2,
540 p_compound_freq in varchar2,
541 x_early_pay_cr out NOCOPY number,
542 x_EARLY_PAY_CR_DETAILS out NOCOPY varchar2);
543
544 END LNS_FINANCIALS;
[Home] [Help]