[Home] [Help]
PACKAGE BODY: APPS.OKL_LRF_INTERFACE_PVT
Source
1 PACKAGE BODY OKL_LRF_INTERFACE_PVT AS
2 /* $Header: OKLRLRIB.pls 120.3 2005/07/05 12:32:09 asawanka noship $*/
3
4 ------------------------------------------------------------------------------
5 -- PROCEDURE report_error
6 ------------------------------------------------------------------------------
7 PROCEDURE report_error(x_msg_count OUT NOCOPY NUMBER,
8 x_msg_data OUT NOCOPY VARCHAR2) IS
9
10 x_msg_index_out NUMBER;
11 x_msg_out VARCHAR2(2000);
12
13 BEGIN
14
15 null;
16
17 END report_error;
18
19
20 PROCEDURE validate_lrt_id( p_lrt_id IN NUMBER
21 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
22
23 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
24 l_dummy_var VARCHAR2(1) := '?';
25
26 -- select the ID of the parent record from the parent table
27 CURSOR c_lrt_id IS
28 SELECT 'x'
29 FROM OKL_LS_RT_FCTR_SETS_B
30 WHERE ID = p_lrt_id;
31
32 BEGIN
33 null;
34
35 END validate_lrt_id;
36
37 -- Enforces unique key.
38 FUNCTION Validate_Record (p_lrfv_rec IN okl_lrf_interface_pvt.lrf_rec_type
39 ,x_return_status OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 IS
40
41 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
42 l_dummy_var VARCHAR2(1) := '?';
43
44 CURSOR c_enforce_UK IS
45 SELECT 'x'
46 FROM OKL_LS_RT_FCTR_ENTS
47 WHERE LRT_ID = p_lrfv_rec.lrt_id
48 AND TERM_IN_MONTHS = p_lrfv_rec.term_in_months
49 AND RESIDUAL_VALUE_PERCENT = p_lrfv_rec.residual_value_percent;
50 --AND LEASE_RATE_FACTOR = p_lrfv_rec.lease_rate_factor;
51 BEGIN
52
53 null;
54
55 END Validate_Record;
56
57
58 PROCEDURE validate_term_in_months(
59 x_return_status OUT NOCOPY VARCHAR2,
60 p_term_in_months IN NUMBER) IS
61 BEGIN
62 null;
63 END validate_term_in_months;
64
65
66 PROCEDURE validate_residual_val(
67 x_return_status OUT NOCOPY VARCHAR2,
68 p_residual_value_percent IN NUMBER) IS
69 BEGIN
70 null;
71 END validate_residual_val;
72
73
74 PROCEDURE validate_lease_rate_factor(
75 x_return_status OUT NOCOPY VARCHAR2,
76 p_lease_rate_factor IN NUMBER) IS
77 BEGIN
78 null;
79 END validate_lease_rate_factor;
80
81
82 ------------------------------------------------------------------------------
83 -- PROCEDURE Update_Interface_Status
84 -- It Changes Status to Interface Table
85 -- Calls:
86 -- None
87 -- Called By:
88 -- check_input_record
89 -- load_input_record
90 ------------------------------------------------------------------------------
91 PROCEDURE Update_Interface_Status (p_batch_number IN NUMBER
92 ,p_status IN VARCHAR2
93 ,p_lrt_id IN NUMBER
94 ,p_term IN NUMBER
95 ,p_interest_rate IN NUMBER
96 ,p_lrf IN NUMBER
97 ,p_rv_percent IN NUMBER
98 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
99
100 x_proc_name VARCHAR2(35) := 'UPDATE_INTERFACE_STATUS';
101 update_failed EXCEPTION;
102
103 BEGIN
104
105 null;
106
107 END Update_Interface_Status;
108
109
110 ------------------------------------------------------------------------------
111 -- PROCEDURE Load_Input_Record
112 -- It Reads data from Interface Tables and Validates. During process of validation it
113 -- stacks Error, if any, and returns ERROR status to calling process.
114 -- Calls:
115 -- report_error
116 -- update_interface_status
117 -- Called By:
118 -- process_record
119 ------------------------------------------------------------------------------
120 PROCEDURE Load_Input_Record(
121 p_init_msg_list IN VARCHAR2,
122 x_return_status OUT NOCOPY VARCHAR2,
123 x_msg_count OUT NOCOPY NUMBER,
124 x_msg_data OUT NOCOPY VARCHAR2,
125 p_batch_number IN NUMBER,
126 p_lrt_id IN NUMBER,
127 x_total_loaded OUT NOCOPY NUMBER
128 ) IS
129
130 l_proc_name CONSTANT VARCHAR2(30) := 'LOAD_INPUT_RECORD';
131 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
132
133 lx_total_loaded NUMBER := 0;
134
135 l_lrf_rec okl_lrf_pvt.lrfv_rec_type;
136 lx_lrf_rec okl_lrf_pvt.lrfv_rec_type;
137
138 CURSOR c_validated_rec IS
139 SELECT
140 lease_rate_factor
141 ,residual_value_percent
142 ,term_in_months
143 ,interest_rate
144 FROM okl_lrf_interface
145 WHERE batch_number = p_batch_number
146 AND status = 'VALIDATED';
147
148 BEGIN -- Actual Procedure Starts Here
149 null;
150 END Load_Input_Record;
151
152
153 ------------------------------------------------------------------------------
154 PROCEDURE Check_Input_Record(
155 p_init_msg_list IN VARCHAR2,
156 x_return_status OUT NOCOPY VARCHAR2,
157 x_msg_count OUT NOCOPY NUMBER,
158 x_msg_data OUT NOCOPY VARCHAR2,
159 p_batch_number IN NUMBER,
160 p_lrt_id IN NUMBER,
161 x_total_checked OUT NOCOPY NUMBER,
162 x_total_failed OUT NOCOPY NUMBER
163 ) IS
164
165 l_batch_number NUMBER := p_batch_number;
166 l_record_status VARCHAR2(3);
167 l_batch_status VARCHAR2(3);
168 l_total_checked NUMBER := 0;
169 l_rec_valid VARCHAR2(1);
170
171 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
172 l_proc_name CONSTANT VARCHAR2(30) := 'check_input_record';
173
174 l_lrf_rec okl_lrf_interface_pvt.lrf_rec_type;
175
176 l_validation_failed VARCHAR2(1);
177 l_failed_count NUMBER := 0;
178 validation_failed EXCEPTION;
179
180 -- add cursor to get lrf values
181 CURSOR c_lrf_batch (b_batch_number IN NUMBER ) IS
182 SELECT term_in_months, lease_rate_factor, residual_value_percent, interest_rate
183 FROM okl_lrf_interface
184 WHERE batch_number = b_batch_number
185 AND status in ('NEW','FAILED','ERROR','AUTOGEN');
186
187 BEGIN
188 null;
189 END Check_Input_Record;
190
191
192 ------------------------------------------------------------------------------
193 -- PROCEDURE Process_Record
194 -- It Validates Input record and Load record after SUCCESSFUL validation
195 -- Calls:
196 -- Check_Inout_Record
197 -- Load_Input_Record
198 -- Report Error
199 -- Called by:
200 -- Starting point
201 ------------------------------------------------------------------------------
202 PROCEDURE Process_Record (
203 errbuf OUT NOCOPY VARCHAR2
204 ,retcode OUT NOCOPY VARCHAR2
205 ,p_batch_number IN NUMBER
206 ) IS
207
208 X_Progress VARCHAR2(3) := NULL;
209 l_proc_name CONSTANT VARCHAR2(30) := 'PROCESS_RECORD';
210 X_msg_count NUMBER;
211 X_msg_data VARCHAR2(2000);
212 X_return_status VARCHAR2(200);
213 x_total_checked NUMBER := 0;
214 x_total_failed NUMBER := 0;
215 x_total_loaded NUMBER := 0;
216 param_error EXCEPTION;
217 p_lrt_id NUMBER := p_batch_number;
218 l_lrt_id NUMBER := p_batch_number;
219
220 BEGIN
221
222 null;
223 END Process_Record;
224
225
226 ------------------------------------------------------------------------------
227 -- PROCEDURE Purge_Record
228 -- It deletes records from the lrf interface table.
229 ------------------------------------------------------------------------------
230 PROCEDURE purge_record (
231 errbuf OUT NOCOPY VARCHAR2
232 ,retcode OUT NOCOPY VARCHAR2
233 ,p_batch_number IN NUMBER
234 ,p_status IN VARCHAR2
235 ) IS
236
237 x_progress VARCHAR2(3) := NULL;
238 l_proc_name CONSTANT VARCHAR2(30) := 'PROCESS_RECORD';
239 x_msg_count NUMBER;
240 x_msg_data VARCHAR2(2000);
241 x_return_status VARCHAR2(200);
242 x_total_purged NUMBER := 0;
243 l_batch_number NUMBER := p_batch_number;
244 l_status VARCHAR2(30) := p_status;
245 param_error EXCEPTION;
246
247 BEGIN
248
249 null;
250
251 END purge_record;
252
253
254
255
256 --*********************** check Interface **************************************
257
258 --+++++++++++++++++++++++ Load Interface +++++++++++++++++++++++++++++++++++++++
259
260
261
262
263 --------------------------------- Load Interface ------------------------------------
264 -- Function to submit the concurrent request for Contract Import.
265
266 /* Not being used
267 FUNCTION Submit_Imported_LRFs(
268 p_api_version IN NUMBER,
269 p_init_msg_list IN VARCHAR2,
270 x_return_status OUT NOCOPY VARCHAR2,
271 x_msg_count OUT NOCOPY NUMBER,
272 x_msg_data OUT NOCOPY VARCHAR2,
273 p_batch_number IN NUMBER,
274 p_lrt_id IN NUMBER
275 )
276 RETURN NUMBER
277 IS
278
279 x_request_id NUMBER;
280
281 l_start_date VARCHAR2(30);
282 l_end_date VARCHAR2(30);
283
284
285 BEGIN
286
287 null;
288
289 END Submit_Imported_LRFs;
290 --not being used
291 */
292
293
294 PROCEDURE GENERATE_LEASE_RATE_FACTORS
295 (
296 p_init_msg_list IN VARCHAR2
297 ,x_return_status OUT NOCOPY VARCHAR2
298 ,x_msg_count OUT NOCOPY NUMBER
299 ,x_msg_data OUT NOCOPY VARCHAR2
300 ,P_LRT_ID IN NUMBER
301 ,P_RATE_UPPER_RANGE IN NUMBER
302 ,P_RATE_LOWER_RANGE IN NUMBER -- MIN 0
303 ,P_RATE_INTERVAL IN NUMBER
304 --
305 ,P_TERM_UPPER_RANGE IN NUMBER
306 ,P_TERM_LOWER_RANGE IN NUMBER -- MIN 0
307 ,P_TERM_INTERVAL IN NUMBER -- IN MONTHS
308 --
309 ,P_RV_UPPER_RANGE IN NUMBER -- MAX 100
310 ,P_RV_LOWER_RANGE IN NUMBER -- MIN 0
311 ,P_RV_INTERVAL IN NUMBER
312 ,x_lease_rate_tbl OUT NOCOPY lease_rate_tbl
313 ) IS
314
315 L_RATE_SET_ID NUMBER := P_LRT_ID;
316 L_ARREARS_YN VARCHAR2(2);
317 L_ARREARS NUMBER;
318 L_FREQ NUMBER;
319 L_LRF NUMBER;
320 L_TERM NUMBER;
321 L_RV NUMBER;
322 L_RATE NUMBER;
323 --
324 L_RATE_UPPER_RANGE NUMBER := P_RATE_UPPER_RANGE;
325 L_RATE_LOWER_RANGE NUMBER := P_RATE_LOWER_RANGE; -- MIN 0
326 L_RATE_INTERVAL NUMBER := P_RATE_INTERVAL;
327 --
328 L_TERM_UPPER_RANGE NUMBER := P_TERM_UPPER_RANGE;
329 L_TERM_LOWER_RANGE NUMBER := P_TERM_LOWER_RANGE; -- MIN 0
330 L_TERM_INTERVAL NUMBER := P_TERM_INTERVAL; -- IN MONTHS
331 --
332 L_RV_UPPER_RANGE NUMBER := P_RV_UPPER_RANGE; -- MAX 100
333 L_RV_LOWER_RANGE NUMBER := P_RV_LOWER_RANGE; -- MIN 0
334 L_RV_INTERVAL NUMBER := P_RV_INTERVAL;
335 --
336 L_TERM_LIMIT NUMBER := 0;
337 L_RV_LIMIT NUMBER := 0;
338 L_RATE_LIMIT NUMBER := 0;
339 I NUMBER := 0;
340 --
341 L_LEASE_RATE_TBL LEASE_RATE_TBL;
342 --
343 L_RETURN_STATUS VARCHAR2(1) := G_RET_STS_SUCCESS;
344 l_api_name CONSTANT VARCHAR2(30) := 'Generate_Lease_Rate_Factors';
345 l_api_version CONSTANT NUMBER := 1;
346 param_error EXCEPTION;
347 --
348 CURSOR C_RATE_SET (B_LRT_ID IN NUMBER) IS
349 SELECT DECODE(ARREARS_YN,'N',1,0), DECODE(FRQ_CODE,'M',12,'Q',3,'S',2,'A',1,0)
350 FROM OKL_LS_RT_FCTR_SETS_B
351 WHERE ID = B_LRT_ID;
352 --
353 BEGIN
354
355
356 null;
357 END GENERATE_LEASE_RATE_FACTORS;
358
359
360 ---------------
361 -- generate_lrf
362 ---------------
363 PROCEDURE generate_lrf (errbuf OUT NOCOPY VARCHAR2
364 ,retcode OUT NOCOPY VARCHAR2
365 ,p_batch_number IN NUMBER
366 ,p_term_lower_range IN NUMBER
367 ,p_term_upper_range IN NUMBER
368 ,p_term_interval IN NUMBER
369 ,p_rv_lower_range IN NUMBER
370 ,p_rv_upper_range IN NUMBER
371 ,p_rv_interval IN NUMBER) IS
372
373 l_proc_name CONSTANT VARCHAR2(30) := 'generate_lrf';
374 x_msg_count NUMBER;
375 x_msg_data VARCHAR2(2000);
376 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
377 param_error EXCEPTION;
378 p_lrt_id NUMBER := p_batch_number;
379 l_lrt_id NUMBER := p_batch_number;
380 l_hdr_rate NUMBER;
381
382 LX_LEASE_RATE_TBL LEASE_RATE_TBL;
383
384 BEGIN
385
386
387 null;
388 END GENERATE_LRF;
389
390 END OKL_LRF_INTERFACE_PVT;