1 PACKAGE LNS_CUSTOM_PUB AS
2 /* $Header: LNS_CUST_PUBP_S.pls 120.0.12010000.4 2009/01/21 20:21:36 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
53 Type custom_tbl is table of custom_sched_type index by binary_integer;
54
55 TYPE LOAN_DETAILS_REC IS RECORD(LOAN_ID NUMBER -- loan id
56 ,AMORTIZATION_FREQUENCY VARCHAR2(30)
57 ,PAYMENT_FREQUENCY VARCHAR2(30)
58 ,LOAN_START_DATE DATE
59 ,FUNDED_AMOUNT NUMBER
60 ,REMAINING_BALANCE NUMBER
61 ,UNPAID_PRINCIPAL NUMBER
62 ,UNPAID_INTEREST NUMBER
63 ,UNBILLED_PRINCIPAL NUMBER
64 ,MATURITY_DATE DATE
65 ,LAST_INSTALLMENT_BILLED NUMBER
66 ,DAY_COUNT_METHOD VARCHAR2(30)
67 ,CUSTOM_SCHEDULE VARCHAR2(1) -- Y/N for custom payment schedule
68 ,LOAN_STATUS VARCHAR2(30) -- loan status
69 ,LOAN_CURRENCY VARCHAR2(15) -- loan currency
70 ,CURRENCY_PRECISION NUMBER -- currency precision
71 ,PAYMENT_CALC_METHOD VARCHAR2(30) -- payment calc method: equal payment, equal principal
72 ,CALCULATION_METHOD VARCHAR2(30) -- interest calc method: simple, compound
73 ,INTEREST_COMPOUNDING_FREQ VARCHAR2(30)
74 ,LAST_DUE_DATE DATE
75 ,CUSTOM_CALC_METHOD VARCHAR2(30)
76 ,ORIG_PAY_CALC_METHOD VARCHAR2(30)
77 ,RATE_TYPE VARCHAR2(30) -- fixed or variable
78 ,TERM_CEILING_RATE NUMBER -- term ceiling rate
79 ,TERM_FLOOR_RATE NUMBER -- term floor rate
80 ,TERM_FIRST_PERCENT_INCREASE NUMBER -- term first percentage increase
81 ,TERM_ADJ_PERCENT_INCREASE NUMBER -- term percentage increase btwn adjustments
82 ,TERM_LIFE_PERCENT_INCREASE NUMBER -- term lifetime max adjustment for interest
83 ,TERM_INDEX_RATE_ID NUMBER -- index_rate_id
84 ,INITIAL_INTEREST_RATE NUMBER
85 ,LAST_INTEREST_RATE NUMBER
86 ,FIRST_RATE_CHANGE_DATE DATE
87 ,NEXT_RATE_CHANGE_DATE DATE
88 ,TERM_PROJECTED_INTEREST_RATE NUMBER -- term projected interest rate
89 ,PENAL_INT_RATE NUMBER
90 ,PENAL_INT_GRACE_DAYS NUMBER);
91
92
93 procedure resetCustomSchedule(p_loan_id IN number
94 ,p_init_msg_list IN VARCHAR2
95 ,p_commit IN VARCHAR2
96 ,p_update_header IN boolean
97 ,x_return_status OUT NOCOPY VARCHAR2
98 ,x_msg_count OUT NOCOPY NUMBER
99 ,x_msg_data OUT NOCOPY VARCHAR2);
100
101 procedure createCustomSchedule(p_custom_tbl IN CUSTOM_TBL
102 ,p_loan_id IN number
103 ,p_init_msg_list IN VARCHAR2
104 ,p_commit IN VARCHAR2
105 ,x_return_status OUT NOCOPY VARCHAR2
106 ,x_msg_count OUT NOCOPY NUMBER
107 ,x_msg_data OUT NOCOPY VARCHAR2
108 ,X_INVALID_INSTALLMENT_NUM OUT NOCOPY NUMBER);
109
110 procedure updateCustomSchedule(p_custom_tbl IN CUSTOM_TBL
111 ,p_loan_id IN number
112 ,p_init_msg_list IN VARCHAR2
113 ,p_commit IN VARCHAR2
114 ,x_return_status OUT NOCOPY VARCHAR2
115 ,x_msg_count OUT NOCOPY NUMBER
116 ,x_msg_data OUT NOCOPY VARCHAR2
117 ,X_INVALID_INSTALLMENT_NUM OUT NOCOPY NUMBER);
118
119 procedure createCustomSched(P_CUSTOM_REC IN CUSTOM_SCHED_TYPE
120 ,x_custom_sched_id OUT NOCOPY NUMBER
121 ,x_return_status OUT NOCOPY VARCHAR2
122 ,x_msg_count OUT NOCOPY NUMBER
123 ,x_msg_data OUT NOCOPY VARCHAR2);
124
125 procedure updateCustomSched(P_CUSTOM_REC IN CUSTOM_SCHED_TYPE
126 ,x_return_status OUT NOCOPY VARCHAR2
127 ,x_msg_count OUT NOCOPY NUMBER
128 ,x_msg_data OUT NOCOPY VARCHAR2);
129
130 procedure validateCustomTable(p_cust_tbl in CUSTOM_TBL
131 ,p_loan_id in number
132 ,p_create_flag in boolean
133 ,x_installment OUT NOCOPY NUMBER
134 ,x_return_status OUT NOCOPY VARCHAR2
135 ,x_msg_count OUT NOCOPY NUMBER
136 ,x_msg_data OUT NOCOPY VARCHAR2);
137
138 procedure validateCustomRow(p_custom_rec in CUSTOM_SCHED_TYPE
139 ,x_return_status OUT NOCOPY VARCHAR2
140 ,x_msg_count OUT NOCOPY NUMBER
141 ,x_msg_data OUT NOCOPY VARCHAR2);
142
143 -- This procedure recalculates custom schedule
144 procedure recalcCustomSchedule(
145 P_API_VERSION IN NUMBER,
146 P_INIT_MSG_LIST IN VARCHAR2,
147 P_COMMIT IN VARCHAR2,
148 P_VALIDATION_LEVEL IN NUMBER,
149 P_LOAN_ID IN NUMBER,
150 P_AMORT_METHOD IN VARCHAR2,
151 P_BASED_ON_TERMS IN VARCHAR2,
152 P_CUSTOM_TBL IN OUT NOCOPY LNS_CUSTOM_PUB.CUSTOM_TBL,
153 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
154 X_MSG_COUNT OUT NOCOPY NUMBER,
155 X_MSG_DATA OUT NOCOPY VARCHAR2);
156
157 -- This procedure loads custom schedule from db
158 procedure loadCustomSchedule(
159 P_API_VERSION IN NUMBER,
160 P_INIT_MSG_LIST IN VARCHAR2,
161 P_COMMIT IN VARCHAR2,
162 P_VALIDATION_LEVEL IN NUMBER,
163 P_LOAN_ID IN NUMBER,
164 P_BASED_ON_TERMS IN VARCHAR2,
165 X_AMORT_METHOD OUT NOCOPY VARCHAR2,
166 X_CUSTOM_TBL OUT NOCOPY LNS_CUSTOM_PUB.CUSTOM_TBL,
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 saves custom schedule into db
172 procedure saveCustomSchedule(
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_AMORT_METHOD IN VARCHAR2,
179 P_BASED_ON_TERMS IN VARCHAR2,
180 P_CUSTOM_TBL IN OUT NOCOPY LNS_CUSTOM_PUB.CUSTOM_TBL,
181 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
182 X_MSG_COUNT OUT NOCOPY NUMBER,
183 X_MSG_DATA OUT NOCOPY VARCHAR2);
184
185 -- This procedure switches from standard schedule to custom schedule in one shot
186 -- Conditions: loan status is INCOMPLETE and loan has not been customized yet
187 procedure customizeSchedule(
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 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
194 X_MSG_COUNT OUT NOCOPY NUMBER,
195 X_MSG_DATA OUT NOCOPY VARCHAR2);
196
197 -- This procedure switches back from custom schedule to standard schedule in one shot
198 -- Conditions: loan status is INCOMPLETE and loan has been already customized
199 procedure uncustomizeSchedule(
200 P_API_VERSION IN NUMBER,
201 P_INIT_MSG_LIST IN VARCHAR2,
202 P_COMMIT IN VARCHAR2,
203 P_VALIDATION_LEVEL IN NUMBER,
204 P_LOAN_ID IN NUMBER,
205 P_ST_AMORT_METHOD IN VARCHAR2,
206 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
207 X_MSG_COUNT OUT NOCOPY NUMBER,
208 X_MSG_DATA OUT NOCOPY VARCHAR2);
209
210 -- This procedure recalculates custom schedule with shifting all subsequent due dates on a single due date change
211 procedure shiftCustomSchedule(
212 P_API_VERSION IN NUMBER,
213 P_INIT_MSG_LIST IN VARCHAR2,
214 P_COMMIT IN VARCHAR2,
215 P_VALIDATION_LEVEL IN NUMBER,
216 P_LOAN_ID IN NUMBER,
217 P_OLD_DUE_DATE IN DATE,
218 P_NEW_DUE_DATE IN DATE,
219 P_AMORT_METHOD IN VARCHAR2,
220 P_BASED_ON_TERMS IN VARCHAR2,
221 P_CUSTOM_TBL IN OUT NOCOPY LNS_CUSTOM_PUB.CUSTOM_TBL,
222 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
223 X_MSG_COUNT OUT NOCOPY NUMBER,
224 X_MSG_DATA OUT NOCOPY VARCHAR2);
225
226
227 -- This procedure rebuilds the custom schedule and delete rows whose dueDate > maturityDate
228 -- Conditions: loan status is INCOMPLETE
229 procedure reBuildCustomdSchedule(
230 P_API_VERSION IN NUMBER,
231 P_INIT_MSG_LIST IN VARCHAR2,
232 P_COMMIT IN VARCHAR2,
233 P_VALIDATION_LEVEL IN NUMBER,
234 P_LOAN_ID IN NUMBER,
235 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
236 X_MSG_COUNT OUT NOCOPY NUMBER,
237 X_MSG_DATA OUT NOCOPY VARCHAR2);
238
239
240 -- This procedure builds custom payment schedule and returns LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL table
241 function buildCustomPaySchedule(P_LOAN_ID IN NUMBER) return LNS_FIN_UTILS.PAYMENT_SCHEDULE_TBL;
242
243 -- added for bug 7716548
244 -- This procedure adds installment to custom schedule only if it does not already exist
245 procedure addMissingInstallment(
246 P_API_VERSION IN NUMBER,
247 P_INIT_MSG_LIST IN VARCHAR2,
248 P_COMMIT IN VARCHAR2,
249 P_VALIDATION_LEVEL IN NUMBER,
250 P_INSTALLMENT_REC IN LNS_CUSTOM_PUB.custom_sched_type,
251 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
252 X_MSG_COUNT OUT NOCOPY NUMBER,
253 X_MSG_DATA OUT NOCOPY VARCHAR2);
254
255 END LNS_CUSTOM_PUB;
[Home] [Help]