[Home] [Help]
PACKAGE BODY: APPS.OKL_LRF_PVT
Source
1 PACKAGE BODY okl_lrf_pvt AS
2 /* $Header: OKLSLRFB.pls 120.6 2005/07/05 12:34:42 asawanka noship $ */
3
4 ----------
5 -- get_rec
6 ----------
7
8 FUNCTION get_rec(p_id IN number
9 ,x_return_status OUT NOCOPY varchar2) RETURN lrfv_rec_type IS
10 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'get_rec';
11 l_lrfv_rec lrfv_rec_type;
12
13 BEGIN
14
15 SELECT id
16 ,object_version_number
17 ,lrt_id
18 ,term_in_months
19 ,residual_value_percent
20 ,interest_rate
21 ,lease_rate_factor
22 ,created_by
23 ,creation_date
24 ,last_updated_by
25 ,last_update_date
26 ,last_update_login
27 ,attribute_category
28 ,attribute1
29 ,attribute2
30 ,attribute3
31 ,attribute4
32 ,attribute5
33 ,attribute6
34 ,attribute7
35 ,attribute8
36 ,attribute9
37 ,attribute10
38 ,attribute11
39 ,attribute12
40 ,attribute13
41 ,attribute14
42 ,attribute15
43 ,rate_set_version_id
44 INTO l_lrfv_rec.id
45 ,l_lrfv_rec.object_version_number
46 ,l_lrfv_rec.lrt_id
47 ,l_lrfv_rec.term_in_months
48 ,l_lrfv_rec.residual_value_percent
49 ,l_lrfv_rec.interest_rate
50 ,l_lrfv_rec.lease_rate_factor
51 ,l_lrfv_rec.created_by
52 ,l_lrfv_rec.creation_date
53 ,l_lrfv_rec.last_updated_by
54 ,l_lrfv_rec.last_update_date
55 ,l_lrfv_rec.last_update_login
56 ,l_lrfv_rec.attribute_category
57 ,l_lrfv_rec.attribute1
58 ,l_lrfv_rec.attribute2
59 ,l_lrfv_rec.attribute3
60 ,l_lrfv_rec.attribute4
61 ,l_lrfv_rec.attribute5
62 ,l_lrfv_rec.attribute6
63 ,l_lrfv_rec.attribute7
64 ,l_lrfv_rec.attribute8
65 ,l_lrfv_rec.attribute9
66 ,l_lrfv_rec.attribute10
67 ,l_lrfv_rec.attribute11
68 ,l_lrfv_rec.attribute12
69 ,l_lrfv_rec.attribute13
70 ,l_lrfv_rec.attribute14
71 ,l_lrfv_rec.attribute15
72 ,l_lrfv_rec.rate_set_version_id
73 FROM okl_ls_rt_fctr_ents_v lrfv
74 WHERE lrfv.id = p_id;
75 x_return_status := g_ret_sts_success;
76 RETURN l_lrfv_rec;
77 EXCEPTION
78 WHEN okl_api.g_exception_error THEN
79 x_return_status := g_ret_sts_error;
80 WHEN okl_api.g_exception_unexpected_error THEN
81 x_return_status := g_ret_sts_unexp_error;
82 WHEN OTHERS THEN
83 okl_api.set_message(p_app_name => g_app_name
84 ,p_msg_name => g_db_error
85 ,p_token1 => g_prog_name_token
86 ,p_token1_value => l_api_name
87 ,p_token2 => g_sqlcode_token
88 ,p_token2_value => sqlcode
89 ,p_token3 => g_sqlerrm_token
90 ,p_token3_value => sqlerrm);
91 x_return_status := g_ret_sts_unexp_error;
92 END get_rec;
93
94 ---------------------------------------------------------------------------
95 -- FUNCTION null_out_defaults for: OKL_LS_RT_FCTR_ENTS_V
96 ---------------------------------------------------------------------------
97
98 FUNCTION null_out_defaults(p_lrfv_rec IN lrfv_rec_type) RETURN lrfv_rec_type IS
99 l_lrfv_rec lrfv_rec_type := p_lrfv_rec;
100
101 BEGIN
102
103 IF (l_lrfv_rec.id = g_miss_num) THEN
104 l_lrfv_rec.id := NULL;
105 END IF;
106
107 IF (l_lrfv_rec.object_version_number = g_miss_num) THEN
108 l_lrfv_rec.object_version_number := NULL;
109 END IF;
110
111 IF (l_lrfv_rec.lrt_id = g_miss_num) THEN
112 l_lrfv_rec.lrt_id := NULL;
113 END IF;
114
115 IF (l_lrfv_rec.term_in_months = g_miss_num) THEN
116 l_lrfv_rec.term_in_months := NULL;
117 END IF;
118
119 IF (l_lrfv_rec.residual_value_percent = g_miss_num) THEN
120 l_lrfv_rec.residual_value_percent := NULL;
121 END IF;
122
123 IF (l_lrfv_rec.interest_rate = g_miss_num) THEN
124 l_lrfv_rec.interest_rate := NULL;
125 END IF;
126
127 IF (l_lrfv_rec.lease_rate_factor = g_miss_num) THEN
128 l_lrfv_rec.lease_rate_factor := NULL;
129 END IF;
130
131 IF (l_lrfv_rec.created_by = g_miss_num) THEN
132 l_lrfv_rec.created_by := NULL;
133 END IF;
134
135 IF (l_lrfv_rec.creation_date = g_miss_date) THEN
136 l_lrfv_rec.creation_date := NULL;
137 END IF;
138
139 IF (l_lrfv_rec.last_updated_by = g_miss_num) THEN
140 l_lrfv_rec.last_updated_by := NULL;
141 END IF;
142
143 IF (l_lrfv_rec.last_update_date = g_miss_date) THEN
144 l_lrfv_rec.last_update_date := NULL;
145 END IF;
146
147 IF (l_lrfv_rec.last_update_login = g_miss_num) THEN
148 l_lrfv_rec.last_update_login := NULL;
149 END IF;
150
151 IF (l_lrfv_rec.attribute_category = g_miss_char) THEN
152 l_lrfv_rec.attribute_category := NULL;
153 END IF;
154
155 IF (l_lrfv_rec.attribute1 = g_miss_char) THEN
156 l_lrfv_rec.attribute1 := NULL;
157 END IF;
158
159 IF (l_lrfv_rec.attribute2 = g_miss_char) THEN
160 l_lrfv_rec.attribute2 := NULL;
161 END IF;
162
163 IF (l_lrfv_rec.attribute3 = g_miss_char) THEN
164 l_lrfv_rec.attribute3 := NULL;
165 END IF;
166
167 IF (l_lrfv_rec.attribute4 = g_miss_char) THEN
168 l_lrfv_rec.attribute4 := NULL;
169 END IF;
170
171 IF (l_lrfv_rec.attribute5 = g_miss_char) THEN
172 l_lrfv_rec.attribute5 := NULL;
173 END IF;
174
175 IF (l_lrfv_rec.attribute6 = g_miss_char) THEN
176 l_lrfv_rec.attribute6 := NULL;
177 END IF;
178
179 IF (l_lrfv_rec.attribute7 = g_miss_char) THEN
180 l_lrfv_rec.attribute7 := NULL;
181 END IF;
182
183 IF (l_lrfv_rec.attribute8 = g_miss_char) THEN
184 l_lrfv_rec.attribute8 := NULL;
185 END IF;
186
187 IF (l_lrfv_rec.attribute9 = g_miss_char) THEN
188 l_lrfv_rec.attribute9 := NULL;
189 END IF;
190
191 IF (l_lrfv_rec.attribute10 = g_miss_char) THEN
192 l_lrfv_rec.attribute10 := NULL;
193 END IF;
194
195 IF (l_lrfv_rec.attribute11 = g_miss_char) THEN
196 l_lrfv_rec.attribute11 := NULL;
197 END IF;
198
199 IF (l_lrfv_rec.attribute12 = g_miss_char) THEN
200 l_lrfv_rec.attribute12 := NULL;
201 END IF;
202
203 IF (l_lrfv_rec.attribute13 = g_miss_char) THEN
204 l_lrfv_rec.attribute13 := NULL;
205 END IF;
206
207 IF (l_lrfv_rec.attribute14 = g_miss_char) THEN
208 l_lrfv_rec.attribute14 := NULL;
209 END IF;
210
211 IF (l_lrfv_rec.attribute15 = g_miss_char) THEN
212 l_lrfv_rec.attribute15 := NULL;
213 END IF;
214
215 IF (l_lrfv_rec.rate_set_version_id = g_miss_num) THEN
216 l_lrfv_rec.rate_set_version_id := NULL;
217 END IF;
218
219 RETURN(l_lrfv_rec);
220 END null_out_defaults;
221
222 ---------------------------------
223 -- PROCEDURE validate_id
224 ---------------------------------
225
226 PROCEDURE validate_id(x_return_status OUT NOCOPY varchar2
227 ,p_id IN number) IS
228 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_id';
229
230 BEGIN
231
232 IF p_id IS NULL THEN
233 okl_api.set_message(p_app_name => g_app_name
234 ,p_msg_name => g_required_value
235 ,p_token1 => g_col_name_token
236 ,p_token1_value => 'id');
237 RAISE okl_api.g_exception_error;
238 END IF;
239 x_return_status := g_ret_sts_success;
240 EXCEPTION
241 WHEN okl_api.g_exception_error THEN
242 x_return_status := g_ret_sts_error;
243 WHEN okl_api.g_exception_unexpected_error THEN
244 x_return_status := g_ret_sts_unexp_error;
245 WHEN OTHERS THEN
246 okl_api.set_message(p_app_name => g_app_name
247 ,p_msg_name => g_db_error
248 ,p_token1 => g_prog_name_token
249 ,p_token1_value => l_api_name
250 ,p_token2 => g_sqlcode_token
251 ,p_token2_value => sqlcode
252 ,p_token3 => g_sqlerrm_token
253 ,p_token3_value => sqlerrm);
254 x_return_status := g_ret_sts_unexp_error;
255 END validate_id;
256
257 PROCEDURE validate_object_version_number(x_return_status OUT NOCOPY varchar2
258 ,p_object_version_number IN number) IS
259 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_object_version_number';
260
261 BEGIN
262
263 IF (p_object_version_number IS NULL) OR (p_object_version_number = g_miss_num) THEN
264 okl_api.set_message(p_app_name => g_app_name
265 ,p_msg_name => g_required_value
266 ,p_token1 => g_col_name_token
267 ,p_token1_value => 'object_version_number');
268 RAISE okl_api.g_exception_error;
269 END IF;
270 x_return_status := g_ret_sts_success;
271 EXCEPTION
272 WHEN okl_api.g_exception_error THEN
273 x_return_status := g_ret_sts_error;
274 WHEN okl_api.g_exception_unexpected_error THEN
275 x_return_status := g_ret_sts_unexp_error;
276 WHEN OTHERS THEN
277 okl_api.set_message(p_app_name => g_app_name
278 ,p_msg_name => g_db_error
279 ,p_token1 => g_prog_name_token
280 ,p_token1_value => l_api_name
281 ,p_token2 => g_sqlcode_token
282 ,p_token2_value => sqlcode
283 ,p_token3 => g_sqlerrm_token
284 ,p_token3_value => sqlerrm);
285 x_return_status := g_ret_sts_unexp_error;
286 END validate_object_version_number;
287
288 ---------------------------------
289 -- PROCEDURE validate_lrt_id
290 ---------------------------------
291
292 PROCEDURE validate_lrt_id(x_return_status OUT NOCOPY varchar2
293 ,p_lrt_id IN number) IS
294 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_lrt_id';
295
296 CURSOR c_lrt_id IS
297 SELECT 'x'
298 FROM okl_ls_rt_fctr_sets_b
299 WHERE id = p_lrt_id;
300 l_dummy varchar2(1);
301
302 BEGIN
303
304 IF p_lrt_id IS NULL THEN
305 okl_api.set_message(p_app_name => g_app_name
306 ,p_msg_name => g_required_value
307 ,p_token1 => g_col_name_token
308 ,p_token1_value => 'lrt_id');
309 RAISE okl_api.g_exception_error;
310 END IF;
311 OPEN c_lrt_id;
312 FETCH c_lrt_id INTO l_dummy ;
313 CLOSE c_lrt_id;
314
315 IF l_dummy IS NULL THEN
316 okl_api.set_message(p_app_name => g_app_name
317 ,p_msg_name => g_invalid_value
318 ,p_token1 => g_col_name_token
319 ,p_token1_value => 'lrt_id');
320 RAISE okl_api.g_exception_error;
321 END IF;
322 x_return_status := g_ret_sts_success;
323 EXCEPTION
324 WHEN okl_api.g_exception_error THEN
325 x_return_status := g_ret_sts_error;
326 WHEN okl_api.g_exception_unexpected_error THEN
327 x_return_status := g_ret_sts_unexp_error;
328 WHEN OTHERS THEN
329 okl_api.set_message(p_app_name => g_app_name
330 ,p_msg_name => g_db_error
331 ,p_token1 => g_prog_name_token
332 ,p_token1_value => l_api_name
333 ,p_token2 => g_sqlcode_token
334 ,p_token2_value => sqlcode
335 ,p_token3 => g_sqlerrm_token
336 ,p_token3_value => sqlerrm);
337 x_return_status := g_ret_sts_unexp_error;
338 END validate_lrt_id;
339
340 ---------------------------------
341 -- PROCEDURE validate_term_in_months
342 ---------------------------------
343
344 PROCEDURE validate_term_in_months(x_return_status OUT NOCOPY varchar2
345 ,p_term_in_months IN number) IS
346 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_term_in_months';
347
348 BEGIN
349
350 IF p_term_in_months IS NULL THEN
351 okl_api.set_message(p_app_name => g_app_name
352 ,p_msg_name => g_required_value
353 ,p_token1 => g_col_name_token
354 ,p_token1_value => 'term_in_months');
355 RAISE okl_api.g_exception_error;
356 END IF;
357
358 IF p_term_in_months <= 0 THEN
359 okl_api.set_message(okl_api.g_app_name, 'OKL_INVALID_TERM');
360 RAISE okl_api.g_exception_error;
361 END IF;
362
363 IF trunc(p_term_in_months) <> p_term_in_months THEN
364 okl_api.set_message(okl_api.g_app_name, 'OKL_INVALID_TERM2');
365 RAISE okl_api.g_exception_error;
366 END IF;
367 x_return_status := g_ret_sts_success;
368 EXCEPTION
369 WHEN okl_api.g_exception_error THEN
370 x_return_status := g_ret_sts_error;
371 WHEN okl_api.g_exception_unexpected_error THEN
372 x_return_status := g_ret_sts_unexp_error;
373 WHEN OTHERS THEN
374 okl_api.set_message(p_app_name => g_app_name
375 ,p_msg_name => g_db_error
376 ,p_token1 => g_prog_name_token
377 ,p_token1_value => l_api_name
378 ,p_token2 => g_sqlcode_token
379 ,p_token2_value => sqlcode
380 ,p_token3 => g_sqlerrm_token
381 ,p_token3_value => sqlerrm);
382 x_return_status := g_ret_sts_unexp_error;
383 END validate_term_in_months;
384
385 ---------------------------------
386 -- PROCEDURE validate_rv_percent
387 ---------------------------------
388
389 PROCEDURE validate_rv_percent(x_return_status OUT NOCOPY varchar2
390 ,p_residual_value_percent IN number) IS
391 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_rv_percent';
392
393 BEGIN
394
395
396 IF p_residual_value_percent IS NULL THEN
397 okl_api.set_message(p_app_name => g_app_name
398 ,p_msg_name => g_required_value
399 ,p_token1 => g_col_name_token
400 ,p_token1_value => 'residual_value_percent');
401 RAISE okl_api.g_exception_error;
402 END IF;
403
404 IF p_residual_value_percent < 0 OR p_residual_value_percent >= 100 THEN
405 okl_api.set_message(okl_api.g_app_name, 'OKL_INVALID_RV');
406 RAISE okl_api.g_exception_error;
407 END IF;
408 x_return_status := g_ret_sts_success;
409 EXCEPTION
410 WHEN okl_api.g_exception_error THEN
411 x_return_status := g_ret_sts_error;
412 WHEN okl_api.g_exception_unexpected_error THEN
413 x_return_status := g_ret_sts_unexp_error;
414 WHEN OTHERS THEN
415 okl_api.set_message(p_app_name => g_app_name
416 ,p_msg_name => g_db_error
417 ,p_token1 => g_prog_name_token
418 ,p_token1_value => l_api_name
419 ,p_token2 => g_sqlcode_token
420 ,p_token2_value => sqlcode
421 ,p_token3 => g_sqlerrm_token
422 ,p_token3_value => sqlerrm);
423 x_return_status := g_ret_sts_unexp_error;
424 END validate_rv_percent;
425
426 ---------------------------------
427 -- PROCEDURE validate_interest_rate
428 ---------------------------------
429
430 PROCEDURE validate_interest_rate(x_return_status OUT NOCOPY varchar2
431 ,p_interest_rate IN number) IS
432 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_interest_rate';
433
434 BEGIN
435
436 IF p_interest_rate IS NULL THEN
437 okl_api.set_message(p_app_name => g_app_name
438 ,p_msg_name => g_required_value
439 ,p_token1 => g_col_name_token
440 ,p_token1_value => 'interest_rate');
441 RAISE okl_api.g_exception_error;
442 END IF;
443 x_return_status := g_ret_sts_success;
444 EXCEPTION
445 WHEN okl_api.g_exception_error THEN
446 x_return_status := g_ret_sts_error;
447 WHEN okl_api.g_exception_unexpected_error THEN
448 x_return_status := g_ret_sts_unexp_error;
449 WHEN OTHERS THEN
450 okl_api.set_message(p_app_name => g_app_name
451 ,p_msg_name => g_db_error
452 ,p_token1 => g_prog_name_token
453 ,p_token1_value => l_api_name
454 ,p_token2 => g_sqlcode_token
455 ,p_token2_value => sqlcode
456 ,p_token3 => g_sqlerrm_token
457 ,p_token3_value => sqlerrm);
458 x_return_status := g_ret_sts_unexp_error;
459 END validate_interest_rate;
460
461 ---------------------------------
462 -- PROCEDURE validate_lease_rate_factor
463 ---------------------------------
464
465 PROCEDURE validate_lease_rate_factor (x_return_status OUT NOCOPY varchar2
466 ,p_lease_rate_factor IN number) IS
467 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_lease_rate_factor';
468
469 BEGIN
470
471 IF p_lease_rate_factor IS NULL THEN
472 okl_api.set_message(p_app_name => g_app_name
473 ,p_msg_name => g_required_value
474 ,p_token1 => g_col_name_token
475 ,p_token1_value => 'lease_rate_factor');
476 RAISE okl_api.g_exception_error;
477 END IF;
478 x_return_status := g_ret_sts_success;
479 EXCEPTION
480 WHEN okl_api.g_exception_error THEN
481 x_return_status := g_ret_sts_error;
482 WHEN okl_api.g_exception_unexpected_error THEN
483 x_return_status := g_ret_sts_unexp_error;
484 WHEN OTHERS THEN
485 okl_api.set_message(p_app_name => g_app_name
486 ,p_msg_name => g_db_error
487 ,p_token1 => g_prog_name_token
488 ,p_token1_value => l_api_name
489 ,p_token2 => g_sqlcode_token
490 ,p_token2_value => sqlcode
491 ,p_token3 => g_sqlerrm_token
492 ,p_token3_value => sqlerrm);
493 x_return_status := g_ret_sts_unexp_error;
494 END validate_lease_rate_factor;
495
496 ------------------------------------------
497 -- PROCEDURE validate_rate_set_version_id
498 ------------------------------------------
499
500 PROCEDURE validate_rate_set_version_id(x_return_status OUT NOCOPY varchar2
501 ,p_rate_set_version_id IN number) IS
502 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_lrt_id';
503
504 CURSOR c_rate_set_version_id IS
505 SELECT 'x'
506 FROM okl_fe_rate_set_versions
507 WHERE rate_set_version_id = p_rate_set_version_id;
508 l_dummy varchar2(1);
509
510 BEGIN
511
512 IF p_rate_set_version_id IS NULL THEN
513 okl_api.set_message(p_app_name => g_app_name
514 ,p_msg_name => g_required_value
515 ,p_token1 => g_col_name_token
516 ,p_token1_value => 'RATE_SET_VERSION_ID');
517 RAISE okl_api.g_exception_error;
518 END IF;
519 OPEN c_rate_set_version_id;
520 FETCH c_rate_set_version_id INTO l_dummy ;
521 CLOSE c_rate_set_version_id;
522
523 IF l_dummy IS NULL THEN
524 okl_api.set_message(p_app_name => g_app_name
525 ,p_msg_name => g_invalid_value
526 ,p_token1 => g_col_name_token
527 ,p_token1_value => 'RATE_SET_VERSION_ID');
528 RAISE okl_api.g_exception_error;
529 END IF;
530 x_return_status := g_ret_sts_success;
531 EXCEPTION
532 WHEN okl_api.g_exception_error THEN
533 x_return_status := g_ret_sts_error;
534 WHEN okl_api.g_exception_unexpected_error THEN
535 x_return_status := g_ret_sts_unexp_error;
536 WHEN OTHERS THEN
537 okl_api.set_message(p_app_name => g_app_name
538 ,p_msg_name => g_db_error
539 ,p_token1 => g_prog_name_token
540 ,p_token1_value => l_api_name
541 ,p_token2 => g_sqlcode_token
542 ,p_token2_value => sqlcode
543 ,p_token3 => g_sqlerrm_token
544 ,p_token3_value => sqlerrm);
545 x_return_status := g_ret_sts_unexp_error;
546 END validate_rate_set_version_id;
547
548 ----------------------
549 -- validate_attributes
550 ----------------------
551
552 FUNCTION validate_attributes(p_lrfv_rec IN lrfv_rec_type) RETURN varchar2 IS
553 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_attributes';
554 l_return_status varchar2(1);
555
556 BEGIN
557
558 -- ***
559 -- id
560 -- ***
561
562 validate_id(l_return_status, p_lrfv_rec.id);
563
564 IF (l_return_status = g_ret_sts_unexp_error) THEN
565 RAISE okl_api.g_exception_unexpected_error;
566 ELSIF (l_return_status = g_ret_sts_error) THEN
567 RAISE okl_api.g_exception_error;
568 END IF;
569
570 -- ***
571 -- object_version_number
572 -- ***
573
574 validate_object_version_number(l_return_status
575 ,p_lrfv_rec.object_version_number);
576
577 IF (l_return_status = g_ret_sts_unexp_error) THEN
578 RAISE okl_api.g_exception_unexpected_error;
579 ELSIF (l_return_status = g_ret_sts_error) THEN
580 RAISE okl_api.g_exception_error;
581 END IF;
582
583 -- ***
584 -- lrt_id
585 -- ***
586
587 validate_lrt_id(l_return_status, p_lrfv_rec.lrt_id);
588
589 IF (l_return_status = g_ret_sts_unexp_error) THEN
590 RAISE okl_api.g_exception_unexpected_error;
591 ELSIF (l_return_status = g_ret_sts_error) THEN
592 RAISE okl_api.g_exception_error;
593 END IF;
594
595 -- ***
596 -- term_in_months
597 -- ***
598
599 validate_term_in_months(l_return_status, p_lrfv_rec.term_in_months);
600
601 IF (l_return_status = g_ret_sts_unexp_error) THEN
602 RAISE okl_api.g_exception_unexpected_error;
603 ELSIF (l_return_status = g_ret_sts_error) THEN
604 RAISE okl_api.g_exception_error;
605 END IF;
606
607 -- ***
608 -- residual_value_percent
609 -- ***
610
611 validate_rv_percent(l_return_status, p_lrfv_rec.residual_value_percent);
612
613 IF (l_return_status = g_ret_sts_unexp_error) THEN
614 RAISE okl_api.g_exception_unexpected_error;
615 ELSIF (l_return_status = g_ret_sts_error) THEN
616 RAISE okl_api.g_exception_error;
617 END IF;
618
619 -- ***
620 -- interest_rate
621 -- ***
622
623 validate_interest_rate(l_return_status, p_lrfv_rec.interest_rate);
624
625 IF (l_return_status = g_ret_sts_unexp_error) THEN
626 RAISE okl_api.g_exception_unexpected_error;
627 ELSIF (l_return_status = g_ret_sts_error) THEN
628 RAISE okl_api.g_exception_error;
629 END IF;
630
631 -- ***
632 -- lease_rate_factor
633 -- ***
634
635 validate_lease_rate_factor (l_return_status
636 ,p_lrfv_rec.lease_rate_factor);
637
638 IF (l_return_status = g_ret_sts_unexp_error) THEN
639 RAISE okl_api.g_exception_unexpected_error;
640 ELSIF (l_return_status = g_ret_sts_error) THEN
641 RAISE okl_api.g_exception_error;
642 END IF;
643
644 -- ***
645 -- RATE_SET_VERSION_ID
646 -- ***
647
648 validate_rate_set_version_id(l_return_status
649 ,p_lrfv_rec.rate_set_version_id);
650
651 IF (l_return_status = g_ret_sts_unexp_error) THEN
652 RAISE okl_api.g_exception_unexpected_error;
653 ELSIF (l_return_status = g_ret_sts_error) THEN
654 RAISE okl_api.g_exception_error;
655 END IF;
656
657
658 RETURN g_ret_sts_success;
659 EXCEPTION
660 WHEN okl_api.g_exception_error THEN
661 RETURN g_ret_sts_error;
662 WHEN okl_api.g_exception_unexpected_error THEN
663 RETURN g_ret_sts_unexp_error;
664 WHEN OTHERS THEN
665 okl_api.set_message(p_app_name => g_app_name
666 ,p_msg_name => g_db_error
667 ,p_token1 => g_prog_name_token
668 ,p_token1_value => l_api_name
669 ,p_token2 => g_sqlcode_token
670 ,p_token2_value => sqlcode
671 ,p_token3 => g_sqlerrm_token
672 ,p_token3_value => sqlerrm);
673 RETURN g_ret_sts_unexp_error;
674 END validate_attributes;
675
676 ------------------
677 -- validate_record
678 ------------------
679
680 FUNCTION validate_record(p_lrfv_rec IN lrfv_rec_type) RETURN varchar2 IS
681 l_dummy varchar2(1);
682 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_record';
683
684 --asawanka start changed 'lrt_id = p_lrfv_rec.lrt_id' to 'rate_set_version_id=p_lrfv_rec.rate_set_version_id'
685
686 CURSOR c_enforce_uk IS
687 SELECT 'x'
688 FROM okl_ls_rt_fctr_ents
689 WHERE rate_set_version_id = p_lrfv_rec.rate_set_version_id --lrt_id = p_lrfv_rec.lrt_id
690 AND term_in_months = p_lrfv_rec.term_in_months
691 AND residual_value_percent = p_lrfv_rec.residual_value_percent
692 AND interest_rate = p_lrfv_rec.interest_rate
693 AND id <> p_lrfv_rec.id;
694
695 BEGIN
696 OPEN c_enforce_uk;
697 FETCH c_enforce_uk INTO l_dummy ;
698 CLOSE c_enforce_uk;
699
700 IF l_dummy IS NOT NULL THEN
701 okl_api.set_message(p_app_name => g_app_name
702 ,p_msg_name => 'OKL_LRF_EXISTS');
703 RAISE okl_api.g_exception_error;
704 END IF;
705 RETURN g_ret_sts_success;
706 EXCEPTION
707 WHEN okl_api.g_exception_error THEN
708 RETURN g_ret_sts_error;
709 WHEN okl_api.g_exception_unexpected_error THEN
710 RETURN g_ret_sts_unexp_error;
711 WHEN OTHERS THEN
712 okl_api.set_message(p_app_name => g_app_name
713 ,p_msg_name => g_db_error
714 ,p_token1 => g_prog_name_token
715 ,p_token1_value => l_api_name
716 ,p_token2 => g_sqlcode_token
717 ,p_token2_value => sqlcode
718 ,p_token3 => g_sqlerrm_token
719 ,p_token3_value => sqlerrm);
720 RETURN g_ret_sts_unexp_error;
721 END validate_record;
722
723 ---------------------
724 -- validate_row (REC)
725 ---------------------
726
727 PROCEDURE validate_row(p_api_version IN number
728 ,p_init_msg_list IN varchar2 DEFAULT okc_api.g_false
729 ,x_return_status OUT NOCOPY varchar2
730 ,x_msg_count OUT NOCOPY number
731 ,x_msg_data OUT NOCOPY varchar2
732 ,p_lrfv_rec IN lrfv_rec_type) IS
733 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_row (REC)';
734 l_return_status varchar2(1);
735
736 BEGIN
737 l_return_status := validate_attributes(p_lrfv_rec);
738
739 IF (l_return_status = g_ret_sts_unexp_error) THEN
740 RAISE okl_api.g_exception_unexpected_error;
741 ELSIF (l_return_status = g_ret_sts_error) THEN
742 RAISE okl_api.g_exception_error;
743 END IF;
744 l_return_status := validate_record(p_lrfv_rec);
745
746 IF (l_return_status = g_ret_sts_unexp_error) THEN
747 RAISE okl_api.g_exception_unexpected_error;
748 ELSIF (l_return_status = g_ret_sts_error) THEN
749 RAISE okl_api.g_exception_error;
750 END IF;
751
752 EXCEPTION
753 WHEN okl_api.g_exception_error THEN
754 x_return_status := g_ret_sts_error;
755 WHEN okl_api.g_exception_unexpected_error THEN
756 x_return_status := g_ret_sts_unexp_error;
757 WHEN OTHERS THEN
758 okl_api.set_message(p_app_name => g_app_name
759 ,p_msg_name => g_db_error
760 ,p_token1 => g_prog_name_token
761 ,p_token1_value => l_api_name
762 ,p_token2 => g_sqlcode_token
763 ,p_token2_value => sqlcode
764 ,p_token3 => g_sqlerrm_token
765 ,p_token3_value => sqlerrm);
766 x_return_status := g_ret_sts_unexp_error;
767 END validate_row;
768
769 ---------------------
770 -- validate_row (TBL)
771 ---------------------
772
773 PROCEDURE validate_row(p_api_version IN number
774 ,p_init_msg_list IN varchar2 DEFAULT okc_api.g_false
775 ,x_return_status OUT NOCOPY varchar2
776 ,x_msg_count OUT NOCOPY number
777 ,x_msg_data OUT NOCOPY varchar2
778 ,p_lrfv_tbl IN lrfv_tbl_type) IS
779 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_row (TBL)';
780 l_return_status varchar2(1) := g_ret_sts_success;
781 i binary_integer;
782
783 BEGIN
784
785 IF (p_lrfv_tbl.COUNT > 0) THEN
786 i := p_lrfv_tbl.FIRST;
787
788 LOOP
789 IF p_lrfv_tbl.EXISTS(i) THEN
790 validate_row(p_api_version => g_api_version
791 ,p_init_msg_list => g_false
792 ,x_return_status => l_return_status
793 ,x_msg_count => x_msg_count
794 ,x_msg_data => x_msg_data
795 ,p_lrfv_rec => p_lrfv_tbl(i));
796 IF l_return_status = g_ret_sts_error THEN
797 RAISE okl_api.g_exception_error;
798 ELSIF l_return_status = g_ret_sts_unexp_error THEN
799 RAISE okl_api.g_exception_unexpected_error;
800 END IF;
801 EXIT WHEN i = p_lrfv_tbl.LAST;
802 i := p_lrfv_tbl.next(i);
803 END IF;
804 END LOOP;
805
806 END IF;
807 x_return_status := l_return_status;
808 EXCEPTION
809 WHEN okl_api.g_exception_error THEN
810 x_return_status := g_ret_sts_error;
811 WHEN okl_api.g_exception_unexpected_error THEN
812 x_return_status := g_ret_sts_unexp_error;
813 WHEN OTHERS THEN
814 okl_api.set_message(p_app_name => g_app_name
815 ,p_msg_name => g_db_error
816 ,p_token1 => g_prog_name_token
817 ,p_token1_value => l_api_name
818 ,p_token2 => g_sqlcode_token
819 ,p_token2_value => sqlcode
820 ,p_token3 => g_sqlerrm_token
821 ,p_token3_value => sqlerrm);
822 x_return_status := g_ret_sts_unexp_error;
823 END validate_row;
824
825 -------------------
826 -- insert_row (REC)
827 -------------------
828
829 PROCEDURE insert_row(p_api_version IN number
830 ,p_init_msg_list IN varchar2 DEFAULT okc_api.g_false
831 ,x_return_status OUT NOCOPY varchar2
832 ,x_msg_count OUT NOCOPY number
833 ,x_msg_data OUT NOCOPY varchar2
834 ,p_lrfv_rec IN lrfv_rec_type
835 ,x_lrfv_rec OUT NOCOPY lrfv_rec_type) IS
836 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'insert_row (REC)';
837 l_return_status varchar2(1);
838 l_lrfv_rec lrfv_rec_type;
839
840 BEGIN
841 l_lrfv_rec := null_out_defaults(p_lrfv_rec);
842 l_lrfv_rec.id := okc_p_util.raw_to_number(sys_guid());
843 l_lrfv_rec.object_version_number := 1;
844 l_lrfv_rec.creation_date := sysdate;
845 l_lrfv_rec.created_by := fnd_global.user_id;
846 l_lrfv_rec.last_update_date := sysdate;
847 l_lrfv_rec.last_updated_by := fnd_global.user_id;
848 l_lrfv_rec.last_update_login := fnd_global.login_id;
849
850 l_return_status := validate_attributes(l_lrfv_rec);
851
852 IF (l_return_status = g_ret_sts_unexp_error) THEN
853 RAISE okl_api.g_exception_unexpected_error;
854 ELSIF (l_return_status = g_ret_sts_error) THEN
855 RAISE okl_api.g_exception_error;
856 END IF;
857 l_return_status := validate_record(l_lrfv_rec);
858
859 IF (l_return_status = g_ret_sts_unexp_error) THEN
860 RAISE okl_api.g_exception_unexpected_error;
861 ELSIF (l_return_status = g_ret_sts_error) THEN
862 RAISE okl_api.g_exception_error;
863 END IF;
864
865 INSERT INTO okl_ls_rt_fctr_ents
866 (id
867 ,object_version_number
868 ,lrt_id
869 ,term_in_months
870 ,residual_value_percent
871 ,interest_rate
872 ,lease_rate_factor
873 ,created_by
874 ,creation_date
875 ,last_updated_by
876 ,last_update_date
877 ,last_update_login
878 ,attribute_category
879 ,attribute1
880 ,attribute2
881 ,attribute3
882 ,attribute4
883 ,attribute5
884 ,attribute6
885 ,attribute7
886 ,attribute8
887 ,attribute9
888 ,attribute10
889 ,attribute11
890 ,attribute12
891 ,attribute13
892 ,attribute14
893 ,attribute15
894 ,rate_set_version_id)
895 VALUES (l_lrfv_rec.id
896 ,l_lrfv_rec.object_version_number
897 ,l_lrfv_rec.lrt_id
898 ,l_lrfv_rec.term_in_months
899 ,l_lrfv_rec.residual_value_percent
900 ,l_lrfv_rec.interest_rate
901 ,l_lrfv_rec.lease_rate_factor
902 ,l_lrfv_rec.created_by
903 ,l_lrfv_rec.creation_date
904 ,l_lrfv_rec.last_updated_by
905 ,l_lrfv_rec.last_update_date
906 ,l_lrfv_rec.last_update_login
907 ,l_lrfv_rec.attribute_category
908 ,l_lrfv_rec.attribute1
909 ,l_lrfv_rec.attribute2
910 ,l_lrfv_rec.attribute3
911 ,l_lrfv_rec.attribute4
912 ,l_lrfv_rec.attribute5
913 ,l_lrfv_rec.attribute6
914 ,l_lrfv_rec.attribute7
915 ,l_lrfv_rec.attribute8
916 ,l_lrfv_rec.attribute9
917 ,l_lrfv_rec.attribute10
918 ,l_lrfv_rec.attribute11
919 ,l_lrfv_rec.attribute12
920 ,l_lrfv_rec.attribute13
921 ,l_lrfv_rec.attribute14
922 ,l_lrfv_rec.attribute15
923 ,l_lrfv_rec.rate_set_version_id);
924
925 x_lrfv_rec := l_lrfv_rec;
926 x_return_status := l_return_status;
927 EXCEPTION
928 WHEN okl_api.g_exception_error THEN
929 x_return_status := g_ret_sts_error;
930 WHEN okl_api.g_exception_unexpected_error THEN
931 x_return_status := g_ret_sts_unexp_error;
932 WHEN OTHERS THEN
933 okl_api.set_message(p_app_name => g_app_name
934 ,p_msg_name => g_db_error
935 ,p_token1 => g_prog_name_token
936 ,p_token1_value => l_api_name
937 ,p_token2 => g_sqlcode_token
938 ,p_token2_value => sqlcode
939 ,p_token3 => g_sqlerrm_token
940 ,p_token3_value => sqlerrm);
941 x_return_status := g_ret_sts_unexp_error;
942 END insert_row;
943
944 -------------------
945 -- insert_row (TBL)
946 -------------------
947
948 PROCEDURE insert_row(p_api_version IN number
949 ,p_init_msg_list IN varchar2 DEFAULT okc_api.g_false
950 ,x_return_status OUT NOCOPY varchar2
951 ,x_msg_count OUT NOCOPY number
952 ,x_msg_data OUT NOCOPY varchar2
953 ,p_lrfv_tbl IN lrfv_tbl_type
954 ,x_lrfv_tbl OUT NOCOPY lrfv_tbl_type) IS
955 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'insert_row (TBL)';
956 l_return_status varchar2(1) := g_ret_sts_success;
957 i binary_integer;
958
959 BEGIN
960
961 IF (p_lrfv_tbl.COUNT > 0) THEN
962 i := p_lrfv_tbl.FIRST;
963
964 LOOP
965 IF p_lrfv_tbl.EXISTS(i) THEN
966 insert_row(p_api_version => g_api_version
967 ,p_init_msg_list => g_false
968 ,x_return_status => l_return_status
969 ,x_msg_count => x_msg_count
970 ,x_msg_data => x_msg_data
971 ,p_lrfv_rec => p_lrfv_tbl(i)
972 ,x_lrfv_rec => x_lrfv_tbl(i));
973 IF l_return_status = g_ret_sts_unexp_error THEN
974 RAISE okl_api.g_exception_unexpected_error;
975 ELSIF l_return_status = g_ret_sts_error THEN
976 RAISE okl_api.g_exception_error;
977 END IF;
978 EXIT WHEN(i = p_lrfv_tbl.LAST);
979 i := p_lrfv_tbl.next(i);
980 END IF;
981 END LOOP;
982
983 END IF;
984 x_return_status := g_ret_sts_success;
985 EXCEPTION
986 WHEN okl_api.g_exception_error THEN
987 x_return_status := g_ret_sts_error;
988 WHEN okl_api.g_exception_unexpected_error THEN
989 x_return_status := g_ret_sts_unexp_error;
990 WHEN OTHERS THEN
991 okl_api.set_message(p_app_name => g_app_name
992 ,p_msg_name => g_db_error
993 ,p_token1 => g_prog_name_token
994 ,p_token1_value => l_api_name
995 ,p_token2 => g_sqlcode_token
996 ,p_token2_value => sqlcode
997 ,p_token3 => g_sqlerrm_token
998 ,p_token3_value => sqlerrm);
999 x_return_status := g_ret_sts_unexp_error;
1000 END insert_row;
1001
1002 -----------------
1003 -- lock_row (REC)
1004 -----------------
1005
1006 PROCEDURE lock_row(p_api_version IN number
1007 ,p_init_msg_list IN varchar2 DEFAULT okc_api.g_false
1008 ,x_return_status OUT NOCOPY varchar2
1009 ,x_msg_count OUT NOCOPY number
1010 ,x_msg_data OUT NOCOPY varchar2
1011 ,p_lrfv_rec IN lrfv_rec_type) IS
1012 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'lock_row (REC)';
1013 e_resource_busy EXCEPTION;
1014
1015 PRAGMA exception_init(e_resource_busy, - 00054);
1016
1017 CURSOR lock_csr(p_lrfv_rec IN lrfv_rec_type) IS
1018 SELECT object_version_number
1019 FROM okl_ls_rt_fctr_ents
1020 WHERE id = p_lrfv_rec.id
1021 AND object_version_number = p_lrfv_rec.object_version_number
1022 FOR UPDATE OF object_version_number NOWAIT;
1023
1024 CURSOR lchk_csr(p_lrfv_rec IN lrfv_rec_type) IS
1025 SELECT object_version_number
1026 FROM okl_ls_rt_fctr_ents
1027 WHERE id = p_lrfv_rec.id;
1028 l_return_status varchar2(1);
1029 l_object_version_number okl_ls_rt_fctr_ents.object_version_number%TYPE;
1030 lc_object_version_number okl_ls_rt_fctr_ents.object_version_number%TYPE;
1031 l_row_notfound boolean := false;
1032 lc_row_notfound boolean := false;
1033
1034 BEGIN
1035
1036 BEGIN
1037 OPEN lock_csr(p_lrfv_rec);
1038 FETCH lock_csr INTO l_object_version_number ;
1039 l_row_notfound := lock_csr%NOTFOUND;
1040 CLOSE lock_csr;
1041 EXCEPTION
1042 WHEN e_resource_busy THEN
1043
1044 IF (lock_csr%ISOPEN) THEN
1045 CLOSE lock_csr;
1046 END IF;
1047 okc_api.set_message(g_fnd_app, g_form_unable_to_reserve_rec);
1048 RAISE app_exceptions.record_lock_exception;
1049 END;
1050
1051 IF (l_row_notfound) THEN
1052 OPEN lchk_csr(p_lrfv_rec);
1053 FETCH lchk_csr INTO lc_object_version_number ;
1054 lc_row_notfound := lchk_csr%NOTFOUND;
1055 CLOSE lchk_csr;
1056 END IF;
1057
1058 IF (lc_row_notfound) THEN
1059 okc_api.set_message(g_fnd_app, g_form_record_deleted);
1060 RAISE okc_api.g_exception_error;
1061 ELSIF lc_object_version_number > p_lrfv_rec.object_version_number THEN
1062 okc_api.set_message(g_fnd_app, g_form_record_changed);
1063 RAISE okc_api.g_exception_error;
1064 ELSIF lc_object_version_number <> p_lrfv_rec.object_version_number THEN
1065 okc_api.set_message(g_fnd_app, g_form_record_changed);
1066 RAISE okc_api.g_exception_error;
1067 ELSIF lc_object_version_number = - 1 THEN
1068 okc_api.set_message(g_app_name, g_record_logically_deleted);
1069 RAISE okc_api.g_exception_error;
1070 END IF;
1071 x_return_status := l_return_status;
1072 EXCEPTION
1073 WHEN okl_api.g_exception_error THEN
1074 x_return_status := g_ret_sts_error;
1075 WHEN okl_api.g_exception_unexpected_error THEN
1076 x_return_status := g_ret_sts_unexp_error;
1077 WHEN OTHERS THEN
1078 okl_api.set_message(p_app_name => g_app_name
1079 ,p_msg_name => g_db_error
1080 ,p_token1 => g_prog_name_token
1081 ,p_token1_value => l_api_name
1082 ,p_token2 => g_sqlcode_token
1083 ,p_token2_value => sqlcode
1084 ,p_token3 => g_sqlerrm_token
1085 ,p_token3_value => sqlerrm);
1086 x_return_status := g_ret_sts_unexp_error;
1087 END lock_row;
1088
1089 -----------------
1090 -- lock_row (TBL)
1091 -----------------
1092
1093 PROCEDURE lock_row(p_api_version IN number
1094 ,p_init_msg_list IN varchar2 DEFAULT okc_api.g_false
1095 ,x_return_status OUT NOCOPY varchar2
1096 ,x_msg_count OUT NOCOPY number
1097 ,x_msg_data OUT NOCOPY varchar2
1098 ,p_lrfv_tbl IN lrfv_tbl_type) IS
1099 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'lock_row (TBL)';
1100 l_return_status varchar2(1) := g_ret_sts_success;
1101 i binary_integer;
1102
1103 BEGIN
1104
1105 IF (p_lrfv_tbl.COUNT > 0) THEN
1106 i := p_lrfv_tbl.FIRST;
1107
1108 LOOP
1109 IF p_lrfv_tbl.EXISTS(i) THEN
1110 lock_row(p_api_version => g_api_version
1111 ,p_init_msg_list => g_false
1112 ,x_return_status => l_return_status
1113 ,x_msg_count => x_msg_count
1114 ,x_msg_data => x_msg_data
1115 ,p_lrfv_rec => p_lrfv_tbl(i));
1116 IF l_return_status = g_ret_sts_unexp_error THEN
1117 RAISE okl_api.g_exception_unexpected_error;
1118 ELSIF l_return_status = g_ret_sts_error THEN
1119 RAISE okl_api.g_exception_error;
1120 END IF;
1121 EXIT WHEN(i = p_lrfv_tbl.LAST);
1122 i := p_lrfv_tbl.next(i);
1123 END IF;
1124 END LOOP;
1125
1126 END IF;
1127 x_return_status := l_return_status;
1128 EXCEPTION
1129 WHEN okl_api.g_exception_error THEN
1130 x_return_status := g_ret_sts_error;
1131 WHEN okl_api.g_exception_unexpected_error THEN
1132 x_return_status := g_ret_sts_unexp_error;
1133 WHEN OTHERS THEN
1134 okl_api.set_message(p_app_name => g_app_name
1135 ,p_msg_name => g_db_error
1136 ,p_token1 => g_prog_name_token
1137 ,p_token1_value => l_api_name
1138 ,p_token2 => g_sqlcode_token
1139 ,p_token2_value => sqlcode
1140 ,p_token3 => g_sqlerrm_token
1141 ,p_token3_value => sqlerrm);
1142 x_return_status := g_ret_sts_unexp_error;
1143 END lock_row;
1144
1145 ------------------------------------------
1146 -- update_row for:OKL_LS_RT_FCTR_ENTS_V --
1147 ------------------------------------------
1148
1149 PROCEDURE update_row(p_api_version IN number
1150 ,p_init_msg_list IN varchar2 DEFAULT okc_api.g_false
1151 ,x_return_status OUT NOCOPY varchar2
1152 ,x_msg_count OUT NOCOPY number
1153 ,x_msg_data OUT NOCOPY varchar2
1154 ,p_lrfv_rec IN lrfv_rec_type
1155 ,x_lrfv_rec OUT NOCOPY lrfv_rec_type) IS
1156 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'update_row (_V)';
1157 l_return_status varchar2(1);
1158 l_lrfv_rec lrfv_rec_type := p_lrfv_rec;
1159
1160 ----------------------
1161 -- populate_new_record
1162 ----------------------
1163
1164 FUNCTION populate_new_record(p_lrfv_rec IN lrfv_rec_type
1165 ,x_lrfv_rec OUT NOCOPY lrfv_rec_type) RETURN varchar2 IS
1166 l_return_status varchar2(1);
1167 l_db_lrfv_rec lrfv_rec_type;
1168
1169 BEGIN
1170 x_lrfv_rec := p_lrfv_rec;
1171 l_db_lrfv_rec := get_rec(p_lrfv_rec.id, l_return_status);
1172
1173 IF l_return_status = g_ret_sts_unexp_error THEN
1174 RAISE okl_api.g_exception_unexpected_error;
1175 ELSIF l_return_status = g_ret_sts_error THEN
1176 RAISE okl_api.g_exception_error;
1177 END IF;
1178
1179 -- Do NOT default the following 4 standard attributes from the DB
1180 -- object_version_number
1181 -- last_update_date
1182 -- last_update_by
1183 -- last_update_login
1184
1185 IF (x_lrfv_rec.id IS NULL) THEN
1186 x_lrfv_rec.id := l_db_lrfv_rec.id;
1187 END IF;
1188
1189 IF (x_lrfv_rec.lrt_id IS NULL) THEN
1190 x_lrfv_rec.lrt_id := l_db_lrfv_rec.lrt_id;
1191 END IF;
1192
1193 IF (x_lrfv_rec.term_in_months IS NULL) THEN
1194 x_lrfv_rec.term_in_months := l_db_lrfv_rec.term_in_months;
1195 END IF;
1196
1197 IF (x_lrfv_rec.residual_value_percent IS NULL) THEN
1198 x_lrfv_rec.residual_value_percent := l_db_lrfv_rec.residual_value_percent;
1199 END IF;
1200
1201 IF (x_lrfv_rec.interest_rate IS NULL) THEN
1202 x_lrfv_rec.interest_rate := l_db_lrfv_rec.interest_rate;
1203 END IF;
1204
1205 IF (x_lrfv_rec.lease_rate_factor IS NULL) THEN
1206 x_lrfv_rec.lease_rate_factor := l_db_lrfv_rec.lease_rate_factor;
1207 END IF;
1208
1209 IF (x_lrfv_rec.created_by IS NULL) THEN
1210 x_lrfv_rec.created_by := l_db_lrfv_rec.created_by;
1211 END IF;
1212
1213 IF (x_lrfv_rec.creation_date IS NULL) THEN
1214 x_lrfv_rec.creation_date := l_db_lrfv_rec.creation_date;
1215 END IF;
1216
1217 IF (x_lrfv_rec.attribute_category IS NULL) THEN
1218 x_lrfv_rec.attribute_category := l_db_lrfv_rec.attribute_category;
1219 END IF;
1220
1221 IF (x_lrfv_rec.attribute1 IS NULL) THEN
1222 x_lrfv_rec.attribute1 := l_db_lrfv_rec.attribute1;
1223 END IF;
1224
1225 IF (x_lrfv_rec.attribute2 IS NULL) THEN
1226 x_lrfv_rec.attribute2 := l_db_lrfv_rec.attribute2;
1227 END IF;
1228
1229 IF (x_lrfv_rec.attribute3 IS NULL) THEN
1230 x_lrfv_rec.attribute3 := l_db_lrfv_rec.attribute3;
1231 END IF;
1232
1233 IF (x_lrfv_rec.attribute4 IS NULL) THEN
1234 x_lrfv_rec.attribute4 := l_db_lrfv_rec.attribute4;
1235 END IF;
1236
1237 IF (x_lrfv_rec.attribute5 IS NULL) THEN
1238 x_lrfv_rec.attribute5 := l_db_lrfv_rec.attribute5;
1239 END IF;
1240
1241 IF (x_lrfv_rec.attribute6 IS NULL) THEN
1242 x_lrfv_rec.attribute6 := l_db_lrfv_rec.attribute6;
1243 END IF;
1244
1245 IF (x_lrfv_rec.attribute7 IS NULL) THEN
1246 x_lrfv_rec.attribute7 := l_db_lrfv_rec.attribute7;
1247 END IF;
1248
1249 IF (x_lrfv_rec.attribute8 IS NULL) THEN
1250 x_lrfv_rec.attribute8 := l_db_lrfv_rec.attribute8;
1251 END IF;
1252
1253 IF (x_lrfv_rec.attribute9 IS NULL) THEN
1254 x_lrfv_rec.attribute9 := l_db_lrfv_rec.attribute9;
1255 END IF;
1256
1257 IF (x_lrfv_rec.attribute10 IS NULL) THEN
1258 x_lrfv_rec.attribute10 := l_db_lrfv_rec.attribute10;
1259 END IF;
1260
1261 IF (x_lrfv_rec.attribute11 IS NULL) THEN
1262 x_lrfv_rec.attribute11 := l_db_lrfv_rec.attribute11;
1263 END IF;
1264
1265 IF (x_lrfv_rec.attribute12 IS NULL) THEN
1266 x_lrfv_rec.attribute12 := l_db_lrfv_rec.attribute12;
1267 END IF;
1268
1269 IF (x_lrfv_rec.attribute13 IS NULL) THEN
1270 x_lrfv_rec.attribute13 := l_db_lrfv_rec.attribute13;
1271 END IF;
1272
1273 IF (x_lrfv_rec.attribute14 IS NULL) THEN
1274 x_lrfv_rec.attribute14 := l_db_lrfv_rec.attribute14;
1275 END IF;
1276
1277 IF (x_lrfv_rec.attribute15 IS NULL) THEN
1278 x_lrfv_rec.attribute15 := l_db_lrfv_rec.attribute15;
1279 END IF;
1280
1281 IF (x_lrfv_rec.rate_set_version_id IS NULL) THEN
1282 x_lrfv_rec.rate_set_version_id := l_db_lrfv_rec.rate_set_version_id;
1283 END IF;
1284
1285 RETURN l_return_status;
1286 EXCEPTION
1287 WHEN okl_api.g_exception_error THEN
1288 x_return_status := g_ret_sts_error;
1289 WHEN okl_api.g_exception_unexpected_error THEN
1290 x_return_status := g_ret_sts_unexp_error;
1291 WHEN OTHERS THEN
1292 okl_api.set_message(p_app_name => g_app_name
1293 ,p_msg_name => g_db_error
1294 ,p_token1 => g_prog_name_token
1295 ,p_token1_value => l_api_name
1296 ,p_token2 => g_sqlcode_token
1297 ,p_token2_value => sqlcode
1298 ,p_token3 => g_sqlerrm_token
1299 ,p_token3_value => sqlerrm);
1300 x_return_status := g_ret_sts_unexp_error;
1301 END populate_new_record;
1302
1303 BEGIN
1304 l_return_status := populate_new_record(p_lrfv_rec, l_lrfv_rec);
1305
1306 IF (l_return_status = g_ret_sts_unexp_error) THEN
1307 RAISE okl_api.g_exception_unexpected_error;
1308 ELSIF (l_return_status = g_ret_sts_error) THEN
1309 RAISE okl_api.g_exception_error;
1310 END IF;
1311
1312 --null out g miss values
1313
1314 l_lrfv_rec := null_out_defaults(l_lrfv_rec);
1315 l_lrfv_rec.last_update_date := sysdate;
1316 l_lrfv_rec.last_updated_by := fnd_global.user_id;
1317 l_lrfv_rec.last_update_login := fnd_global.login_id;
1318 l_return_status := validate_attributes(l_lrfv_rec);
1319
1320 IF (l_return_status = g_ret_sts_unexp_error) THEN
1321 RAISE okl_api.g_exception_unexpected_error;
1322 ELSIF (l_return_status = g_ret_sts_error) THEN
1323 RAISE okl_api.g_exception_error;
1324 END IF;
1325 l_return_status := validate_record(l_lrfv_rec);
1326
1327 IF (l_return_status = g_ret_sts_unexp_error) THEN
1328 RAISE okl_api.g_exception_unexpected_error;
1329 ELSIF (l_return_status = g_ret_sts_error) THEN
1330 RAISE okl_api.g_exception_error;
1331 END IF;
1332 lock_row(p_api_version => g_api_version
1333 ,p_init_msg_list => g_false
1334 ,x_return_status => l_return_status
1335 ,x_msg_count => x_msg_count
1336 ,x_msg_data => x_msg_data
1337 ,p_lrfv_rec => l_lrfv_rec);
1338
1339 IF (l_return_status = g_ret_sts_unexp_error) THEN
1340 RAISE okl_api.g_exception_unexpected_error;
1341 ELSIF (l_return_status = g_ret_sts_error) THEN
1342 RAISE okl_api.g_exception_error;
1343 END IF;
1344
1345 UPDATE okl_ls_rt_fctr_ents
1346 SET object_version_number = l_lrfv_rec.object_version_number + 1
1347 ,lrt_id = l_lrfv_rec.lrt_id
1348 ,term_in_months = l_lrfv_rec.term_in_months
1349 ,residual_value_percent = l_lrfv_rec.residual_value_percent
1350 ,interest_rate = l_lrfv_rec.interest_rate
1351 ,lease_rate_factor = l_lrfv_rec.lease_rate_factor
1352 ,created_by = l_lrfv_rec.created_by
1353 ,creation_date = l_lrfv_rec.creation_date
1354 ,last_updated_by = l_lrfv_rec.last_updated_by
1355 ,last_update_date = l_lrfv_rec.last_update_date
1356 ,last_update_login = l_lrfv_rec.last_update_login
1357 ,attribute_category = l_lrfv_rec.attribute_category
1358 ,attribute1 = l_lrfv_rec.attribute1
1359 ,attribute2 = l_lrfv_rec.attribute2
1360 ,attribute3 = l_lrfv_rec.attribute3
1361 ,attribute4 = l_lrfv_rec.attribute4
1362 ,attribute5 = l_lrfv_rec.attribute5
1363 ,attribute6 = l_lrfv_rec.attribute6
1364 ,attribute7 = l_lrfv_rec.attribute7
1365 ,attribute8 = l_lrfv_rec.attribute8
1366 ,attribute9 = l_lrfv_rec.attribute9
1367 ,attribute10 = l_lrfv_rec.attribute10
1368 ,attribute11 = l_lrfv_rec.attribute11
1369 ,attribute12 = l_lrfv_rec.attribute12
1370 ,attribute13 = l_lrfv_rec.attribute13
1371 ,attribute14 = l_lrfv_rec.attribute14
1372 ,attribute15 = l_lrfv_rec.attribute15
1373 ,rate_set_version_id = l_lrfv_rec.rate_set_version_id
1374 WHERE id = l_lrfv_rec.id;
1375 x_lrfv_rec := l_lrfv_rec;
1376 x_return_status := l_return_status;
1377 EXCEPTION
1378 WHEN okl_api.g_exception_error THEN
1379 x_return_status := g_ret_sts_error;
1380 WHEN okl_api.g_exception_unexpected_error THEN
1381 x_return_status := g_ret_sts_unexp_error;
1382 WHEN OTHERS THEN
1383 okl_api.set_message(p_app_name => g_app_name
1384 ,p_msg_name => g_db_error
1385 ,p_token1 => g_prog_name_token
1386 ,p_token1_value => l_api_name
1387 ,p_token2 => g_sqlcode_token
1388 ,p_token2_value => sqlcode
1389 ,p_token3 => g_sqlerrm_token
1390 ,p_token3_value => sqlerrm);
1391 x_return_status := g_ret_sts_unexp_error;
1392 END update_row;
1393
1394 -------------------
1395 -- update_row (TBL)
1396 -------------------
1397
1398 PROCEDURE update_row(p_api_version IN number
1399 ,p_init_msg_list IN varchar2 DEFAULT okc_api.g_false
1400 ,x_return_status OUT NOCOPY varchar2
1401 ,x_msg_count OUT NOCOPY number
1402 ,x_msg_data OUT NOCOPY varchar2
1403 ,p_lrfv_tbl IN lrfv_tbl_type
1404 ,x_lrfv_tbl OUT NOCOPY lrfv_tbl_type) IS
1405 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'update_row (TBL)';
1406 l_return_status varchar2(1) := g_ret_sts_success;
1407 i binary_integer;
1408
1409 BEGIN
1410
1411 IF (p_lrfv_tbl.COUNT > 0) THEN
1412 i := p_lrfv_tbl.FIRST;
1413
1414 LOOP
1415 IF p_lrfv_tbl.EXISTS(i) THEN
1416 update_row(p_api_version => g_api_version
1417 ,p_init_msg_list => g_false
1418 ,x_return_status => l_return_status
1419 ,x_msg_count => x_msg_count
1420 ,x_msg_data => x_msg_data
1421 ,p_lrfv_rec => p_lrfv_tbl(i)
1422 ,x_lrfv_rec => x_lrfv_tbl(i));
1423 IF l_return_status = g_ret_sts_unexp_error THEN
1424 RAISE okl_api.g_exception_unexpected_error;
1425 ELSIF l_return_status = g_ret_sts_error THEN
1426 RAISE okl_api.g_exception_error;
1427 END IF;
1428 EXIT WHEN(i = p_lrfv_tbl.LAST);
1429 i := p_lrfv_tbl.next(i);
1430 END IF;
1431 END LOOP;
1432
1433 END IF;
1434 x_return_status := l_return_status;
1435 EXCEPTION
1436 WHEN okl_api.g_exception_error THEN
1437 x_return_status := g_ret_sts_error;
1438 WHEN okl_api.g_exception_unexpected_error THEN
1439 x_return_status := g_ret_sts_unexp_error;
1440 WHEN OTHERS THEN
1441 okl_api.set_message(p_app_name => g_app_name
1442 ,p_msg_name => g_db_error
1443 ,p_token1 => g_prog_name_token
1444 ,p_token1_value => l_api_name
1445 ,p_token2 => g_sqlcode_token
1446 ,p_token2_value => sqlcode
1447 ,p_token3 => g_sqlerrm_token
1448 ,p_token3_value => sqlerrm);
1449 x_return_status := g_ret_sts_unexp_error;
1450 END update_row;
1451
1452 -------------------
1453 -- delete_row (REC)
1454 -------------------
1455
1456 PROCEDURE delete_row(p_api_version IN number
1457 ,p_init_msg_list IN varchar2 DEFAULT okc_api.g_false
1458 ,x_return_status OUT NOCOPY varchar2
1459 ,x_msg_count OUT NOCOPY number
1460 ,x_msg_data OUT NOCOPY varchar2
1461 ,p_lrfv_rec IN lrfv_rec_type) IS
1462 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'delete_row (REC)';
1463
1464 BEGIN
1465
1466 DELETE FROM okl_ls_rt_fctr_ents
1467 WHERE id = p_lrfv_rec.id;
1468 x_return_status := g_ret_sts_success;
1469 EXCEPTION
1470 WHEN okl_api.g_exception_error THEN
1471 x_return_status := g_ret_sts_error;
1472 WHEN okl_api.g_exception_unexpected_error THEN
1473 x_return_status := g_ret_sts_unexp_error;
1474 WHEN OTHERS THEN
1475 okl_api.set_message(p_app_name => g_app_name
1476 ,p_msg_name => g_db_error
1477 ,p_token1 => g_prog_name_token
1478 ,p_token1_value => l_api_name
1479 ,p_token2 => g_sqlcode_token
1480 ,p_token2_value => sqlcode
1481 ,p_token3 => g_sqlerrm_token
1482 ,p_token3_value => sqlerrm);
1483 x_return_status := g_ret_sts_unexp_error;
1484 END delete_row;
1485
1486 -------------------
1487 -- delete_row (TBL)
1488 -------------------
1489
1490 PROCEDURE delete_row(p_api_version IN number
1491 ,p_init_msg_list IN varchar2 DEFAULT okc_api.g_false
1492 ,x_return_status OUT NOCOPY varchar2
1493 ,x_msg_count OUT NOCOPY number
1494 ,x_msg_data OUT NOCOPY varchar2
1495 ,p_lrfv_tbl IN lrfv_tbl_type) IS
1496 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'delete_row (TBL)';
1497 l_return_status varchar2(1) := g_ret_sts_success;
1498 i binary_integer;
1499
1500 BEGIN
1501
1502 IF (p_lrfv_tbl.COUNT > 0) THEN
1503 i := p_lrfv_tbl.FIRST;
1504
1505 LOOP
1506 IF p_lrfv_tbl.EXISTS(i) THEN
1507 delete_row(p_api_version => g_api_version
1508 ,p_init_msg_list => g_false
1509 ,x_return_status => l_return_status
1510 ,x_msg_count => x_msg_count
1511 ,x_msg_data => x_msg_data
1512 ,p_lrfv_rec => p_lrfv_tbl(i));
1513 IF l_return_status = g_ret_sts_unexp_error THEN
1514 RAISE okl_api.g_exception_unexpected_error;
1515 ELSIF l_return_status = g_ret_sts_error THEN
1516 RAISE okl_api.g_exception_error;
1517 END IF;
1518 EXIT WHEN(i = p_lrfv_tbl.LAST);
1519 i := p_lrfv_tbl.next(i);
1520 END IF;
1521 END LOOP;
1522
1523 END IF;
1524 x_return_status := l_return_status;
1525 EXCEPTION
1526 WHEN okl_api.g_exception_error THEN
1527 x_return_status := g_ret_sts_error;
1528 WHEN okl_api.g_exception_unexpected_error THEN
1529 x_return_status := g_ret_sts_unexp_error;
1530 WHEN OTHERS THEN
1531 okl_api.set_message(p_app_name => g_app_name
1532 ,p_msg_name => g_db_error
1533 ,p_token1 => g_prog_name_token
1534 ,p_token1_value => l_api_name
1535 ,p_token2 => g_sqlcode_token
1536 ,p_token2_value => sqlcode
1537 ,p_token3 => g_sqlerrm_token
1538 ,p_token3_value => sqlerrm);
1539 x_return_status := g_ret_sts_unexp_error;
1540 END delete_row;
1541
1542 END okl_lrf_pvt;