1 PACKAGE LNS_CUSTOM_PUB AUTHID CURRENT_USER AS
2 /* $Header: LNS_CUST_PUBP_S.pls 120.9.12020000.2 2012/07/19 20:54:43 scherkas ship $ */
3
4 TYPE custom_sched_type IS RECORD(CUSTOM_SCHEDULE_ID NUMBER
5 ,LOAN_ID NUMBER
6 ,PAYMENT_NUMBER NUMBER
7 ,DUE_DATE DATE
8 ,PERIOD_START_DATE DATE
9 ,PERIOD_END_DATE DATE
10 ,PRINCIPAL_AMOUNT NUMBER
11 ,INTEREST_AMOUNT NUMBER
12 ,NORMAL_INT_AMOUNT NUMBER
13 ,ADD_PRIN_INT_AMOUNT NUMBER
14 ,ADD_INT_INT_AMOUNT NUMBER
15 ,PENAL_INT_AMOUNT NUMBER
16 ,PRINCIPAL_BALANCE NUMBER
17 ,FEE_AMOUNT NUMBER
18 ,OTHER_AMOUNT NUMBER
19 ,OBJECT_VERSION_NUMBER NUMBER
20 ,ATTRIBUTE_CATEGORY VARCHAR2(30)
21 ,ATTRIBUTE1 VARCHAR2(150)
22 ,ATTRIBUTE2 VARCHAR2(150)
23 ,ATTRIBUTE3 VARCHAR2(150)
24 ,ATTRIBUTE4 VARCHAR2(150)
25 ,ATTRIBUTE5 VARCHAR2(150)
26 ,ATTRIBUTE6 VARCHAR2(150)
27 ,ATTRIBUTE7 VARCHAR2(150)
28 ,ATTRIBUTE8 VARCHAR2(150)
29 ,ATTRIBUTE9 VARCHAR2(150)
30 ,ATTRIBUTE10 VARCHAR2(150)
31 ,ATTRIBUTE11 VARCHAR2(150)
32 ,ATTRIBUTE12 VARCHAR2(150)
33 ,ATTRIBUTE13 VARCHAR2(150)
34 ,ATTRIBUTE14 VARCHAR2(150)
35 ,ATTRIBUTE15 VARCHAR2(150)
36 ,ATTRIBUTE16 VARCHAR2(150)
37 ,ATTRIBUTE17 VARCHAR2(150)
38 ,ATTRIBUTE18 VARCHAR2(150)
39 ,ATTRIBUTE19 VARCHAR2(150)
40 ,ATTRIBUTE20 VARCHAR2(150)
41 ,CURRENT_TERM_PAYMENT NUMBER
42 ,INSTALLMENT_BEGIN_BALANCE NUMBER
43 ,INSTALLMENT_END_BALANCE NUMBER
44 ,PRINCIPAL_PAID_TODATE NUMBER
45 ,INTEREST_PAID_TODATE NUMBER
46 ,INTEREST_RATE NUMBER
47 ,UNPAID_PRIN NUMBER
48 ,UNPAID_INT NUMBER
49 ,LOCK_PRIN VARCHAR2(1)
50 ,LOCK_INT VARCHAR2(1)
51 ,ACTION VARCHAR2(1)
52 ,FUNDED_AMOUNT NUMBER
53 ,NORMAL_INT_DETAILS VARCHAR2(2000)
54 ,ADD_PRIN_INT_DETAILS VARCHAR2(2000)
55 ,ADD_INT_INT_DETAILS VARCHAR2(2000)
56 ,PENAL_INT_DETAILS VARCHAR2(2000)
57 ,DISBURSEMENT_AMOUNT NUMBER
58 ,PERIOD VARCHAR2(200)
59 ,PRINCIPAL_PERCENT NUMBER
60 ,RELATIVE_DATE VARCHAR2(256)
61 ,PREV_DEFERRED_INT_AMOUNT NUMBER
62 ,DEFERRED_INT_AMOUNT NUMBER
63 ,DEFERRED_INT_DETAILS VARCHAR2(2000)
64 ,PREV_CAP_INT_AMOUNT NUMBER
65 ,CURR_CAP_INT_AMOUNT NUMBER
66 ,CAP_INT_AMOUNT NUMBER
67 ,CAP_INT_DETAILS VARCHAR2(2000)
68 ,EARLY_PAY_CR_AMOUNT NUMBER
69 ,EARLY_PAY_CR_DETAILS VARCHAR2(2000));
70 Type custom_tbl is table of custom_sched_type index by binary_integer;
71
72 TYPE custom_settings_type IS RECORD(AMORT_METHOD VARCHAR2(30)
73 ,DESCRIPTION VARCHAR2(100));
74
75 TYPE LOAN_DETAILS_REC IS RECORD(LOAN_ID NUMBER -- loan id
76 ,AMORTIZATION_FREQUENCY VARCHAR2(30)
77 ,PAYMENT_FREQUENCY VARCHAR2(30)
78 ,LOAN_START_DATE DATE
79 ,FUNDED_AMOUNT NUMBER
80 ,requested_amount NUMBER
81 ,REMAINING_BALANCE NUMBER
82 ,UNPAID_PRINCIPAL NUMBER
83 ,UNPAID_INTEREST NUMBER
84 ,UNBILLED_PRINCIPAL NUMBER
85 ,BILLED_PRINCIPAL NUMBER
86 ,MATURITY_DATE DATE
87 ,LAST_INSTALLMENT_BILLED NUMBER
88 ,DAY_COUNT_METHOD VARCHAR2(30)
89 ,CUSTOM_SCHEDULE VARCHAR2(1) -- Y/N for custom payment schedule
90 ,LOAN_STATUS VARCHAR2(30) -- loan status
91 ,LOAN_CURRENCY VARCHAR2(15) -- loan currency
92 ,CURRENCY_PRECISION NUMBER -- currency precision
93 ,PAYMENT_CALC_METHOD VARCHAR2(30) -- payment calc method: equal payment, equal principal
94 ,CALCULATION_METHOD VARCHAR2(30) -- interest calc method: simple, compound
95 ,INTEREST_COMPOUNDING_FREQ VARCHAR2(30)
96 ,LAST_DUE_DATE DATE
97 ,CUSTOM_CALC_METHOD VARCHAR2(30)
98 ,ORIG_PAY_CALC_METHOD VARCHAR2(30)
99 ,RATE_TYPE VARCHAR2(30) -- fixed or variable
100 ,TERM_CEILING_RATE NUMBER -- term ceiling rate
101 ,TERM_FLOOR_RATE NUMBER -- term floor rate
102 ,TERM_FIRST_PERCENT_INCREASE NUMBER -- term first percentage increase
103 ,TERM_ADJ_PERCENT_INCREASE NUMBER -- term percentage increase btwn adjustments
104 ,TERM_LIFE_PERCENT_INCREASE NUMBER -- term lifetime max adjustment for interest
105 ,TERM_INDEX_RATE_ID NUMBER -- index_rate_id
106 ,INITIAL_INTEREST_RATE NUMBER
107 ,LAST_INTEREST_RATE NUMBER
108 ,FIRST_RATE_CHANGE_DATE DATE
109 ,NEXT_RATE_CHANGE_DATE DATE
110 ,TERM_PROJECTED_INTEREST_RATE NUMBER -- term projected interest rate
111 ,PENAL_INT_RATE NUMBER
112 ,PENAL_INT_GRACE_DAYS NUMBER
113 ,REAMORTIZE_ON_FUNDING VARCHAR2(30)
114 ,ADD_REQUESTED_AMOUNT NUMBER
115 ,COMBINE_INT_WITH_LAST_PRIN VARCHAR2(1)
116 ,CUSTOM_SCHED_DESC VARCHAR2(100)
117 ,CAPITALIZE_INT VARCHAR2(1)
118 );
119
120
121 procedure resetCustomSchedule(p_loan_id IN number
122 ,p_init_msg_list IN VARCHAR2
123 ,p_commit IN VARCHAR2
124 ,p_update_header IN boolean
125 ,x_return_status OUT NOCOPY VARCHAR2
126 ,x_msg_count OUT NOCOPY NUMBER
127 ,x_msg_data OUT NOCOPY VARCHAR2);
128
129 procedure createCustomSchedule(p_custom_tbl IN CUSTOM_TBL
130 ,p_loan_id IN number
131 ,p_init_msg_list IN VARCHAR2
132 ,p_commit IN VARCHAR2
133 ,x_return_status OUT NOCOPY VARCHAR2
134 ,x_msg_count OUT NOCOPY NUMBER
135 ,x_msg_data OUT NOCOPY VARCHAR2
136 ,X_INVALID_INSTALLMENT_NUM OUT NOCOPY NUMBER);
137
138 procedure updateCustomSchedule(p_custom_tbl IN CUSTOM_TBL
139 ,p_loan_id IN number
140 ,p_init_msg_list IN VARCHAR2
141 ,p_commit IN VARCHAR2
142 ,x_return_status OUT NOCOPY VARCHAR2
143 ,x_msg_count OUT NOCOPY NUMBER
144 ,x_msg_data OUT NOCOPY VARCHAR2
145 ,X_INVALID_INSTALLMENT_NUM OUT NOCOPY NUMBER);
146
147 procedure createCustomSched(P_CUSTOM_REC IN CUSTOM_SCHED_TYPE
148 ,x_custom_sched_id OUT NOCOPY NUMBER
149 ,x_return_status OUT NOCOPY VARCHAR2
150 ,x_msg_count OUT NOCOPY NUMBER
151 ,x_msg_data OUT NOCOPY VARCHAR2);
152
153 procedure updateCustomSched(P_CUSTOM_REC IN CUSTOM_SCHED_TYPE
154 ,x_return_status OUT NOCOPY VARCHAR2
155 ,x_msg_count OUT NOCOPY NUMBER
156 ,x_msg_data OUT NOCOPY VARCHAR2);
157
158 procedure validateCustomTable(p_cust_tbl in CUSTOM_TBL
159 ,p_loan_id in number
160 ,p_create_flag in boolean
161 ,x_installment OUT NOCOPY NUMBER
162 ,x_return_status OUT NOCOPY VARCHAR2
163 ,x_msg_count OUT NOCOPY NUMBER
164 ,x_msg_data OUT NOCOPY VARCHAR2);
165
166 procedure validateCustomRow(p_custom_rec in CUSTOM_SCHED_TYPE
167 ,x_return_status OUT NOCOPY VARCHAR2
168 ,x_msg_count OUT NOCOPY NUMBER
169 ,x_msg_data OUT NOCOPY VARCHAR2);
170
171 -- This procedure recalculates custom schedule
172 procedure recalcCustomSchedule(
173 P_API_VERSION IN NUMBER,
174 P_INIT_MSG_LIST IN VARCHAR2,
175 P_COMMIT IN VARCHAR2,
176 P_VALIDATION_LEVEL IN NUMBER,
177 P_LOAN_ID IN NUMBER,
178 P_BASED_ON_TERMS IN VARCHAR2,
179 P_USE_RETAINED_DATA IN VARCHAR2,
180 P_CUSTOM_SET_REC IN OUT NOCOPY LNS_CUSTOM_PUB.custom_settings_type,
181 P_CUSTOM_TBL IN OUT NOCOPY LNS_CUSTOM_PUB.CUSTOM_TBL,
182 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
183 X_MSG_COUNT OUT NOCOPY NUMBER,
184 X_MSG_DATA OUT NOCOPY VARCHAR2);
185
186 -- This procedure loads custom schedule from db
187 procedure loadCustomSchedule(
188 P_API_VERSION IN NUMBER,
189 P_INIT_MSG_LIST IN VARCHAR2,
190 P_COMMIT IN VARCHAR2,
191 P_VALIDATION_LEVEL IN NUMBER,
192 P_LOAN_ID IN NUMBER,
193 P_BASED_ON_TERMS IN VARCHAR2,
194 X_CUSTOM_SET_REC OUT NOCOPY LNS_CUSTOM_PUB.custom_settings_type,
195 X_CUSTOM_TBL OUT NOCOPY LNS_CUSTOM_PUB.CUSTOM_TBL,
196 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
197 X_MSG_COUNT OUT NOCOPY NUMBER,
198 X_MSG_DATA OUT NOCOPY VARCHAR2);
199
200 -- This procedure saves custom schedule into db
201 procedure saveCustomSchedule(
202 P_API_VERSION IN NUMBER,
203 P_INIT_MSG_LIST IN VARCHAR2,
204 P_COMMIT IN VARCHAR2,
205 P_VALIDATION_LEVEL IN NUMBER,
206 P_LOAN_ID IN NUMBER,
207 P_BASED_ON_TERMS IN VARCHAR2,
208 P_USE_RETAINED_DATA IN VARCHAR2,
209 P_CUSTOM_SET_REC IN OUT NOCOPY LNS_CUSTOM_PUB.custom_settings_type,
210 P_CUSTOM_TBL IN OUT NOCOPY LNS_CUSTOM_PUB.CUSTOM_TBL,
211 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
212 X_MSG_COUNT OUT NOCOPY NUMBER,
213 X_MSG_DATA OUT NOCOPY VARCHAR2);
214
215 -- This procedure generates custom schedule from clob, save it into db and
216 -- returns back new custom schedule
217 procedure customizeSchedule(
218 P_API_VERSION IN NUMBER,
219 P_INIT_MSG_LIST IN VARCHAR2,
220 P_COMMIT IN VARCHAR2,
221 P_VALIDATION_LEVEL IN NUMBER,
222 P_LOAN_ID IN NUMBER,
223 P_CLOB IN CLOB,
224 X_CUSTOM_SET_REC OUT NOCOPY LNS_CUSTOM_PUB.custom_settings_type,
225 X_CUSTOM_TBL OUT NOCOPY LNS_CUSTOM_PUB.CUSTOM_TBL,
226 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
227 X_MSG_COUNT OUT NOCOPY NUMBER,
228 X_MSG_DATA OUT NOCOPY VARCHAR2);
229
230 -- This procedure generates custom schedule from clob and save it into db
231 procedure customizeSchedule(
232 P_API_VERSION IN NUMBER,
236 P_LOAN_ID IN NUMBER,
233 P_INIT_MSG_LIST IN VARCHAR2,
234 P_COMMIT IN VARCHAR2,
235 P_VALIDATION_LEVEL IN NUMBER,
237 P_CLOB IN CLOB,
238 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
239 X_MSG_COUNT OUT NOCOPY NUMBER,
240 X_MSG_DATA OUT NOCOPY VARCHAR2);
241
242 -- This procedure switches back from custom schedule to standard schedule in one shot
243 -- Conditions: loan status is INCOMPLETE and loan has been already customized
244 procedure uncustomizeSchedule(
245 P_API_VERSION IN NUMBER,
246 P_INIT_MSG_LIST IN VARCHAR2,
247 P_COMMIT IN VARCHAR2,
248 P_VALIDATION_LEVEL IN NUMBER,
249 P_LOAN_ID IN NUMBER,
250 P_ST_AMORT_METHOD IN VARCHAR2,
251 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
252 X_MSG_COUNT OUT NOCOPY NUMBER,
253 X_MSG_DATA OUT NOCOPY VARCHAR2);
254
255 -- This procedure recalculates custom schedule with shifting all subsequent due dates on a single due date change
256 procedure shiftCustomSchedule(
257 P_API_VERSION IN NUMBER,
258 P_INIT_MSG_LIST IN VARCHAR2,
259 P_COMMIT IN VARCHAR2,
260 P_VALIDATION_LEVEL IN NUMBER,
261 P_LOAN_ID IN NUMBER,
262 P_OLD_DUE_DATE IN DATE,
263 P_NEW_DUE_DATE IN DATE,
264 P_BASED_ON_TERMS IN VARCHAR2,
265 P_CUSTOM_SET_REC IN OUT NOCOPY LNS_CUSTOM_PUB.custom_settings_type,
266 P_CUSTOM_TBL IN OUT NOCOPY LNS_CUSTOM_PUB.CUSTOM_TBL,
267 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
268 X_MSG_COUNT OUT NOCOPY NUMBER,
269 X_MSG_DATA OUT NOCOPY VARCHAR2);
270
271
272 -- This procedure rebuilds the custom schedule and delete rows whose dueDate > maturityDate
273 -- Conditions: loan status is INCOMPLETE
274 procedure reBuildCustomdSchedule(
275 P_API_VERSION IN NUMBER,
276 P_INIT_MSG_LIST IN VARCHAR2,
277 P_COMMIT IN VARCHAR2,
278 P_VALIDATION_LEVEL IN NUMBER,
279 P_LOAN_ID IN NUMBER,
280 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
281 X_MSG_COUNT OUT NOCOPY NUMBER,
282 X_MSG_DATA OUT NOCOPY VARCHAR2);
283
284
285 -- This procedure builds custom payment schedule and returns LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL table
286 function buildCustomPaySchedule(P_LOAN_ID IN NUMBER) return LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
287
288 -- added for bug 7716548
289 -- This procedure adds installment to custom schedule only if it does not already exist
290 procedure addMissingInstallment(
291 P_API_VERSION IN NUMBER,
292 P_INIT_MSG_LIST IN VARCHAR2,
293 P_COMMIT IN VARCHAR2,
294 P_VALIDATION_LEVEL IN NUMBER,
295 P_INSTALLMENT_REC IN LNS_CUSTOM_PUB.custom_sched_type,
296 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
297 X_MSG_COUNT OUT NOCOPY NUMBER,
298 X_MSG_DATA OUT NOCOPY VARCHAR2);
299
300 -- This procedure parses and validates csv format clob and returnes data in form of custom schedule
301 procedure parseClob(
302 P_API_VERSION IN NUMBER,
303 P_INIT_MSG_LIST IN VARCHAR2,
304 P_COMMIT IN VARCHAR2,
305 P_VALIDATION_LEVEL IN NUMBER,
306 P_CLOB IN CLOB,
307 P_RETAIN_DATA IN VARCHAR2,
308 X_CUSTOM_SET_REC OUT NOCOPY LNS_CUSTOM_PUB.custom_settings_type,
309 X_CUSTOM_TBL OUT NOCOPY LNS_CUSTOM_PUB.CUSTOM_TBL,
310 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
311 X_MSG_COUNT OUT NOCOPY NUMBER,
312 X_MSG_DATA OUT NOCOPY VARCHAR2);
313
314 -- This procedure parses and validates csv format clob and able to retain passed custom sched data in the package for further use
315 procedure parseClob(
316 P_API_VERSION IN NUMBER,
317 P_INIT_MSG_LIST IN VARCHAR2,
318 P_COMMIT IN VARCHAR2,
319 P_VALIDATION_LEVEL IN NUMBER,
320 P_CLOB IN CLOB,
321 P_RETAIN_DATA IN VARCHAR2,
322 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
323 X_MSG_COUNT OUT NOCOPY NUMBER,
324 X_MSG_DATA OUT NOCOPY VARCHAR2);
325
326 -- This procedure retains passed custom sched data in the package for further use
327 procedure retainCustomSchedule(
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 P_CUSTOM_SET_REC IN LNS_CUSTOM_PUB.custom_settings_type,
333 P_CUSTOM_TBL IN LNS_CUSTOM_PUB.CUSTOM_TBL,
334 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
335 X_MSG_COUNT OUT NOCOPY NUMBER,
336 X_MSG_DATA OUT NOCOPY VARCHAR2);
337
338 -- This procedure clears all retained custom sched data in the package
339 procedure clearRetainedSchedule(
340 P_API_VERSION IN NUMBER,
341 P_INIT_MSG_LIST IN VARCHAR2,
342 P_COMMIT IN VARCHAR2,
343 P_VALIDATION_LEVEL IN NUMBER,
344 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
345 X_MSG_COUNT OUT NOCOPY NUMBER,
346 X_MSG_DATA OUT NOCOPY VARCHAR2);
347
348 -- This procedure returns retained custom sched data in the package
349 procedure getRetainedSchedule(
350 P_API_VERSION IN NUMBER,
351 P_INIT_MSG_LIST IN VARCHAR2,
352 P_COMMIT IN VARCHAR2,
353 P_VALIDATION_LEVEL IN NUMBER,
354 X_CUSTOM_SET_REC OUT NOCOPY LNS_CUSTOM_PUB.custom_settings_type,
355 X_CUSTOM_TBL OUT NOCOPY LNS_CUSTOM_PUB.CUSTOM_TBL,
356 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
357 X_MSG_COUNT OUT NOCOPY NUMBER,
358 X_MSG_DATA OUT NOCOPY VARCHAR2);
359
360 END LNS_CUSTOM_PUB;
[Home] [Help]