[Home] [Help]
PACKAGE BODY: APPS.OKL_LRV_PVT
Source
1 PACKAGE BODY okl_lrv_pvt AS
2 /* $Header: OKLSLRVB.pls 120.2 2005/09/30 11:01:07 asawanka noship $ */
3
4 --------------------------------------------------------------------------------
5 --PACKAGE CONSTANTS
6 --------------------------------------------------------------------------------
7
8 g_ret_sts_success CONSTANT varchar2(1) := okl_api.g_ret_sts_success;
9 g_ret_sts_unexp_error CONSTANT varchar2(1) := okl_api.g_ret_sts_unexp_error;
10 g_ret_sts_error CONSTANT varchar2(1) := okl_api.g_ret_sts_error;
11 g_db_error CONSTANT varchar2(12) := 'OKL_DB_ERROR';
12 g_prog_name_token CONSTANT varchar2(9) := 'PROG_NAME';
13 g_miss_char CONSTANT varchar2(1) := okl_api.g_miss_char;
14 g_miss_num CONSTANT number := okl_api.g_miss_num;
15 g_miss_date CONSTANT date := okl_api.g_miss_date;
16 g_no_parent_record CONSTANT varchar2(200) := 'OKC_NO_PARENT_RECORD';
17 g_unexpected_error CONSTANT varchar2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
18 g_sqlerrm_token CONSTANT varchar2(200) := 'SQLerrm';
19 g_sqlcode_token CONSTANT varchar2(200) := 'SQLcode';
20 g_exception_halt_validation EXCEPTION;
21
22 PROCEDURE api_copy IS
23
24 BEGIN
25 NULL;
26 END api_copy;
27
28 PROCEDURE change_version IS
29
30 BEGIN
31 NULL;
32 END change_version;
33
34 ---------------------------------------
35 -- lock_row for:OKL_FE_RATE_SET_VERSIONS --
36 ---------------------------------------
37
38 PROCEDURE lock_row(p_init_msg_list IN varchar2
39 ,x_return_status OUT NOCOPY varchar2
40 ,x_msg_count OUT NOCOPY number
41 ,x_msg_data OUT NOCOPY varchar2
42 ,p_lrvv_rec IN okl_lrvv_rec) IS
43 e_resource_busy EXCEPTION;
44
45 PRAGMA exception_init(e_resource_busy, - 00054);
46
47 CURSOR lock_csr(p_lrvv_rec IN okl_lrvv_rec) IS
48 SELECT object_version_number
49 FROM okl_fe_rate_set_versions
50 WHERE rate_set_version_id = p_lrvv_rec.rate_set_version_id
51 AND object_version_number = p_lrvv_rec.object_version_number
52 FOR UPDATE OF object_version_number NOWAIT;
53
54 CURSOR lchk_csr(p_lrvv_rec IN okl_lrvv_rec) IS
55 SELECT object_version_number
56 FROM okl_fe_rate_set_versions
57 WHERE rate_set_version_id = p_lrvv_rec.rate_set_version_id;
58 l_api_version CONSTANT number := 1;
59 l_api_name CONSTANT varchar2(30) := 'V_lock_row';
60 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
61 l_object_version_number okl_fe_rate_set_versions.object_version_number%TYPE;
62 lc_object_version_number okl_fe_rate_set_versions.object_version_number%TYPE;
63 l_row_notfound boolean := false;
64 lc_row_notfound boolean := false;
65
66 BEGIN
67 l_return_status := okl_api.start_activity(l_api_name
68 ,p_init_msg_list
69 ,'_PVT'
70 ,x_return_status);
71
72 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
73 RAISE okl_api.g_exception_unexpected_error;
74 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
75 RAISE okl_api.g_exception_error;
76 END IF;
77
78 BEGIN
79 OPEN lock_csr(p_lrvv_rec);
80 FETCH lock_csr INTO l_object_version_number ;
81 l_row_notfound := lock_csr%NOTFOUND;
82 CLOSE lock_csr;
83 EXCEPTION
84 WHEN e_resource_busy THEN
85
86 IF (lock_csr%ISOPEN) THEN
87 CLOSE lock_csr;
88 END IF;
89 okl_api.set_message(g_fnd_app, g_form_unable_to_reserve_rec);
90 RAISE app_exceptions.record_lock_exception;
91 END;
92
93 IF (l_row_notfound) THEN
94 OPEN lchk_csr(p_lrvv_rec);
95 FETCH lchk_csr INTO lc_object_version_number ;
96 lc_row_notfound := lchk_csr%NOTFOUND;
97 CLOSE lchk_csr;
98 END IF;
99
100 IF (lc_row_notfound) THEN
101 okl_api.set_message(g_fnd_app, g_form_record_deleted);
102 RAISE okl_api.g_exception_error;
103 ELSIF lc_object_version_number > p_lrvv_rec.object_version_number THEN
104 okl_api.set_message(g_fnd_app, g_form_record_changed);
105 RAISE okl_api.g_exception_error;
106 ELSIF lc_object_version_number <> p_lrvv_rec.object_version_number THEN
107 okl_api.set_message(g_fnd_app, g_form_record_changed);
108 RAISE okl_api.g_exception_error;
109 ELSIF lc_object_version_number = - 1 THEN
110 okl_api.set_message(g_app_name, g_record_logically_deleted);
111 RAISE okl_api.g_exception_error;
112 END IF;
113 okl_api.end_activity(x_msg_count, x_msg_data);
114 EXCEPTION
115 WHEN g_exception_halt_validation THEN
116 x_return_status := okl_api.handle_exceptions(l_api_name
117 ,g_pkg_name
118 ,'OKL_API.G_RET_STS_ERROR'
119 ,x_msg_count
120 ,x_msg_data
121 ,'_PVT');
122 WHEN okl_api.g_exception_error THEN
123 x_return_status := okl_api.handle_exceptions(l_api_name
124 ,g_pkg_name
125 ,'OKL_API.G_RET_STS_ERROR'
126 ,x_msg_count
127 ,x_msg_data
128 ,'_PVT');
129 WHEN okl_api.g_exception_unexpected_error THEN
130 x_return_status := okl_api.handle_exceptions(l_api_name
131 ,g_pkg_name
132 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
133 ,x_msg_count
134 ,x_msg_data
135 ,'_PVT');
136 WHEN OTHERS THEN
137 x_return_status := okl_api.handle_exceptions(l_api_name
138 ,g_pkg_name
139 ,'OTHERS'
140 ,x_msg_count
141 ,x_msg_data
142 ,'_PVT');
143 END lock_row;
144
145 --------------------------------------------------
146 -- PL/SQL TBL lock_row for: OKL_ITM_RSD_HDR --
147 --------------------------------------------------
148
149 PROCEDURE lock_row(p_api_version IN number
150 ,p_init_msg_list IN varchar2
151 ,x_return_status OUT NOCOPY varchar2
152 ,x_msg_count OUT NOCOPY number
153 ,x_msg_data OUT NOCOPY varchar2
154 ,p_lrvv_tbl IN okl_lrvv_tbl) IS
155 l_api_version CONSTANT number := 1;
156 l_api_name CONSTANT varchar2(30) := 'tbl_lock_row';
157 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
158
159 -- Begin Post-Generation Change
160 -- overall error status
161
162 l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
163
164 -- End Post-Generation Change
165
166 i number := 0;
167
168 BEGIN
169 okl_api.init_msg_list(p_init_msg_list);
170
171 -- Make sure PL/SQL table has records in it before passing
172
173 IF (p_lrvv_tbl.COUNT > 0) THEN
174 i := p_lrvv_tbl.FIRST;
175
176 LOOP
177 lock_row(p_init_msg_list => okl_api.g_false
178 ,x_return_status => x_return_status
179 ,x_msg_count => x_msg_count
180 ,x_msg_data => x_msg_data
181 ,p_lrvv_rec => p_lrvv_tbl(i));
182
183 -- Begin Post-Generation Change
184 -- store the highest degree of error
185
186 IF x_return_status <> okl_api.g_ret_sts_success THEN
187 IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
188 l_overall_status := x_return_status;
189 END IF;
190 END IF;
191
192 -- End Post-Generation Change
193
194 EXIT WHEN(i = p_lrvv_tbl.LAST);
195 i := p_lrvv_tbl.next(i);
196 END LOOP;
197
198 -- Begin Post-Generation Change
199 -- return overall status
200
201 x_return_status := l_overall_status;
202
203 -- End Post-Generation Change
204
205 END IF;
206
207 EXCEPTION
208 WHEN g_exception_halt_validation THEN
209 x_return_status := okl_api.handle_exceptions(l_api_name
210 ,g_pkg_name
211 ,'OKL_API.G_RET_STS_ERROR'
212 ,x_msg_count
213 ,x_msg_data
214 ,'_PVT');
215 WHEN okl_api.g_exception_error THEN
216 x_return_status := okl_api.handle_exceptions(l_api_name
217 ,g_pkg_name
218 ,'OKL_API.G_RET_STS_ERROR'
219 ,x_msg_count
220 ,x_msg_data
221 ,'_PVT');
222 WHEN okl_api.g_exception_unexpected_error THEN
223 x_return_status := okl_api.handle_exceptions(l_api_name
224 ,g_pkg_name
225 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
226 ,x_msg_count
227 ,x_msg_data
228 ,'_PVT');
229 WHEN OTHERS THEN
230 x_return_status := okl_api.handle_exceptions(l_api_name
231 ,g_pkg_name
232 ,'OTHERS'
233 ,x_msg_count
234 ,x_msg_data
235 ,'_PVT');
236 END lock_row;
237
238 -------------------------------------
239 -- Function Name : validate_id
240 -------------------------------------
241
242 FUNCTION validate_id(p_id IN number) RETURN varchar2 IS
243 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_id';
244
245 BEGIN
246
247
248 -- data is required
249
250 IF (p_id IS NULL) OR (p_id = okl_api.g_miss_num) THEN
251 okl_api.set_message(p_app_name => g_app_name
252 ,p_msg_name => g_required_value
253 ,p_token1 => g_col_name_token
254 ,p_token1_value => 'id');
255 RAISE okl_api.g_exception_error;
256 END IF;
257 RETURN g_ret_sts_success;
258 EXCEPTION
259 WHEN okl_api.g_exception_error THEN
260 RETURN g_ret_sts_error;
261 WHEN okl_api.g_exception_unexpected_error THEN
262 RETURN g_ret_sts_unexp_error;
263 WHEN OTHERS THEN
264 okl_api.set_message(p_app_name => g_app_name
265 ,p_msg_name => g_db_error
266 ,p_token1 => g_prog_name_token
267 ,p_token1_value => l_api_name
268 ,p_token2 => 'SQLCODE'
269 ,p_token2_value => sqlcode
270 ,p_token3 => 'SQLERRM'
271 ,p_token3_value => sqlerrm);
272 RETURN g_ret_sts_unexp_error;
273 END validate_id;
274
275 -------------------------------------------
276 -- Function validate_object_version_number
277 -------------------------------------------
278
279 FUNCTION validate_object_version_number(p_object_version_number IN number) RETURN varchar2 IS
280 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_object_version_number';
281
282 BEGIN
283
284 IF (p_object_version_number IS NULL) OR (p_object_version_number = g_miss_num) THEN
285 okl_api.set_message(p_app_name => g_app_name
286 ,p_msg_name => g_required_value
287 ,p_token1 => g_col_name_token
288 ,p_token1_value => 'object_version_number');
289 RAISE okl_api.g_exception_error;
290 END IF;
291 RETURN g_ret_sts_success;
292 EXCEPTION
293 WHEN okl_api.g_exception_error THEN
294 RETURN g_ret_sts_error;
295 WHEN okl_api.g_exception_unexpected_error THEN
296 RETURN g_ret_sts_unexp_error;
297 WHEN OTHERS THEN
298 okl_api.set_message(p_app_name => g_app_name
299 ,p_msg_name => g_db_error
300 ,p_token1 => g_prog_name_token
301 ,p_token1_value => l_api_name
302 ,p_token2 => 'SQLCODE'
303 ,p_token2_value => sqlcode
304 ,p_token3 => 'SQLERRM'
305 ,p_token3_value => sqlerrm);
306 RETURN g_ret_sts_unexp_error;
307 END validate_object_version_number;
308
309 --------------------------------------------
310 -- Function Name : validate_RATE_SET_ID
311 --------------------------------------------
312
313 FUNCTION validate_rate_set_id(p_rate_set_id IN number) RETURN varchar2 IS
314 l_dummy_var varchar2(1) := '?';
315 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_RATE_SET_ID';
316
317 -- select the ID of the parent record from the parent table
318
319 CURSOR l_lrs_hdr_csr IS
320 SELECT 'x'
321 FROM okl_ls_rt_fctr_sets_b
322 WHERE id = p_rate_set_id;
323
324 BEGIN
325
326
327 -- data is required
328
329 IF (p_rate_set_id IS NULL) OR (p_rate_set_id = okl_api.g_miss_num) THEN
330 okl_api.set_message(p_app_name => g_app_name
331 ,p_msg_name => g_required_value
332 ,p_token1 => g_col_name_token
333 ,p_token1_value => 'RATE_SET_ID');
334 RAISE okl_api.g_exception_error;
335 END IF;
336
337 -- enforce foreign key
338
339 OPEN l_lrs_hdr_csr;
340 FETCH l_lrs_hdr_csr INTO l_dummy_var ;
341 CLOSE l_lrs_hdr_csr;
342
343 -- if l_dummy_var is still set to default, data was not found
344
345 IF (l_dummy_var = '?') THEN
346 okl_api.set_message(p_app_name => g_app_name
347 ,p_msg_name => g_no_parent_record
348 ,p_token1 => g_col_name_token
349 ,p_token1_value => 'RATE_SET_ID'
350 ,p_token2 => g_child_table_token
351 ,p_token2_value => 'OKL_FE_RATE_SET_VERSIONS'
352 ,p_token3 => g_parent_table_token
353 ,p_token3_value => 'OKL_LS_RT_FCTR_SETS_B');
354 RAISE okl_api.g_exception_error;
355 END IF;
356 RETURN g_ret_sts_success;
357 EXCEPTION
358 WHEN okl_api.g_exception_error THEN
359
360 -- verify that cursor was closed
361
362 IF l_lrs_hdr_csr%ISOPEN THEN
363 CLOSE l_lrs_hdr_csr;
364 END IF;
365 RETURN g_ret_sts_error;
366 WHEN okl_api.g_exception_unexpected_error THEN
367
368 -- verify that cursor was closed
369
370 IF l_lrs_hdr_csr%ISOPEN THEN
371 CLOSE l_lrs_hdr_csr;
372 END IF;
373 RETURN g_ret_sts_unexp_error;
374 WHEN OTHERS THEN
375
376 -- verify that cursor was closed
377
378 IF l_lrs_hdr_csr%ISOPEN THEN
379 CLOSE l_lrs_hdr_csr;
380 END IF;
381 okl_api.set_message(p_app_name => g_app_name
382 ,p_msg_name => g_db_error
383 ,p_token1 => g_prog_name_token
384 ,p_token1_value => l_api_name
385 ,p_token2 => 'SQLCODE'
386 ,p_token2_value => sqlcode
387 ,p_token3 => 'SQLERRM'
388 ,p_token3_value => sqlerrm);
389 RETURN g_ret_sts_unexp_error;
390 END validate_rate_set_id;
391
392 ---------------------------------------------------
393 -- Function Name : validate_EFFECTIVE_FROM_DATE
394 ---------------------------------------------------
395
396 FUNCTION validate_effective_from_date(p_effective_from_date IN date) RETURN varchar2 IS
397 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_EFFECTIVE_FROM_DATE';
398
399 BEGIN
400
401
402 -- data is required
403
404 IF (p_effective_from_date IS NULL) OR (p_effective_from_date = okl_api.g_miss_date) THEN
405 okl_api.set_message(p_app_name => g_app_name
406 ,p_msg_name => g_required_value
407 ,p_token1 => g_col_name_token
408 ,p_token1_value => 'EFFECTIVE_FROM_DATE');
409 RAISE okl_api.g_exception_error;
410 END IF;
411 RETURN g_ret_sts_success;
412 EXCEPTION
413 WHEN okl_api.g_exception_error THEN
414 RETURN g_ret_sts_error;
415 WHEN okl_api.g_exception_unexpected_error THEN
416 RETURN g_ret_sts_unexp_error;
417 WHEN OTHERS THEN
418 okl_api.set_message(p_app_name => g_app_name
419 ,p_msg_name => g_db_error
420 ,p_token1 => g_prog_name_token
421 ,p_token1_value => l_api_name
422 ,p_token2 => 'SQLCODE'
423 ,p_token2_value => sqlcode
424 ,p_token3 => 'SQLERRM'
425 ,p_token3_value => sqlerrm);
426 RETURN g_ret_sts_unexp_error;
427 END validate_effective_from_date;
428
429 -----------------------------------------------------
430 -- Function Name : validate_arrears_yn
431 -----------------------------------------------------
432
433 FUNCTION validate_arrears_yn(p_arrears_yn IN varchar2) RETURN varchar2 IS
434 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
435 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_arrears_yn';
436
437 BEGIN
438
439
440 -- data is required
441
442 IF (p_arrears_yn IS NULL) OR (p_arrears_yn = okl_api.g_miss_char) THEN
443 RAISE okl_api.g_exception_error;
444 END IF;
445 l_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_YES_NO'
446 ,p_lookup_code => p_arrears_yn);
447
448 IF (l_return_status = okl_api.g_ret_sts_error) THEN
449 okl_api.set_message(p_app_name => g_app_name
450 ,p_msg_name => g_invalid_value
451 ,p_token1 => g_col_name_token
452 ,p_token1_value => 'arrears_yn');
453 RAISE okl_api.g_exception_error;
454 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
455 RAISE okl_api.g_exception_unexpected_error;
456 END IF;
457 RETURN g_ret_sts_success;
458 EXCEPTION
459 WHEN okl_api.g_exception_error THEN
460 RETURN g_ret_sts_error;
461 WHEN okl_api.g_exception_unexpected_error THEN
462 RETURN g_ret_sts_unexp_error;
463 WHEN OTHERS THEN
464 okl_api.set_message(p_app_name => g_app_name
465 ,p_msg_name => g_db_error
466 ,p_token1 => g_prog_name_token
467 ,p_token1_value => l_api_name
468 ,p_token2 => 'SQLCODE'
469 ,p_token2_value => sqlcode
470 ,p_token3 => 'SQLERRM'
471 ,p_token3_value => sqlerrm);
472 RETURN g_ret_sts_unexp_error;
473 END validate_arrears_yn;
474
475 -----------------------------------------------------
476 -- Function Name : validate_sts_code
477 -----------------------------------------------------
478
479 FUNCTION validate_sts_code(p_sts_code IN varchar2) RETURN varchar2 IS
480 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
481 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_sts_code';
482
483 BEGIN
484
485 -- data is required
486
487 IF (p_sts_code IS NULL) OR (p_sts_code = okl_api.g_miss_char) THEN
488 okl_api.set_message(p_app_name => g_app_name
489 ,p_msg_name => g_required_value
490 ,p_token1 => g_col_name_token
491 ,p_token1_value => 'sts_code');
492 RAISE okl_api.g_exception_error;
493 END IF;
494 l_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_PRC_STATUS'
495 ,p_lookup_code => p_sts_code);
496
497
498 IF (l_return_status = okl_api.g_ret_sts_error) THEN
499 okl_api.set_message(p_app_name => g_app_name
500 ,p_msg_name => g_invalid_value
501 ,p_token1 => g_col_name_token
502 ,p_token1_value => 'sts_code');
503 RAISE okl_api.g_exception_error;
504 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
505 RAISE okl_api.g_exception_unexpected_error;
506 END IF;
507 RETURN g_ret_sts_success;
508 EXCEPTION
509 WHEN okl_api.g_exception_error THEN
510 RETURN g_ret_sts_error;
511 WHEN okl_api.g_exception_unexpected_error THEN
512 RETURN g_ret_sts_unexp_error;
513 WHEN OTHERS THEN
514 okl_api.set_message(p_app_name => g_app_name
515 ,p_msg_name => g_db_error
516 ,p_token1 => g_prog_name_token
517 ,p_token1_value => l_api_name
518 ,p_token2 => 'SQLCODE'
519 ,p_token2_value => sqlcode
520 ,p_token3 => 'SQLERRM'
521 ,p_token3_value => sqlerrm);
522 RETURN g_ret_sts_unexp_error;
523 END validate_sts_code;
524
525 ---------------------------------------------------
526 -- Function Name : validate_version_number
527 ---------------------------------------------------
528
529 FUNCTION validate_version_number(p_version_number IN varchar2) RETURN varchar2 IS
530 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_version_number';
531
532 BEGIN
533
534
535 -- data is required
536
537 IF (p_version_number IS NULL) OR (p_version_number = okl_api.g_miss_char) THEN
538 okl_api.set_message(p_app_name => g_app_name
539 ,p_msg_name => g_required_value
540 ,p_token1 => g_col_name_token
541 ,p_token1_value => 'version_number');
542 RAISE okl_api.g_exception_error;
543 END IF;
544 RETURN g_ret_sts_success;
545 EXCEPTION
546 WHEN okl_api.g_exception_error THEN
547 RETURN g_ret_sts_error;
548 WHEN okl_api.g_exception_unexpected_error THEN
549 RETURN g_ret_sts_unexp_error;
550 WHEN OTHERS THEN
551 okl_api.set_message(p_app_name => g_app_name
552 ,p_msg_name => g_db_error
553 ,p_token1 => g_prog_name_token
554 ,p_token1_value => l_api_name
555 ,p_token2 => 'SQLCODE'
556 ,p_token2_value => sqlcode
557 ,p_token3 => 'SQLERRM'
558 ,p_token3_value => sqlerrm);
559 RETURN g_ret_sts_unexp_error;
560 END validate_version_number;
561
562 ---------------------------------------------------
563 -- Function Name : validate_rate
564 ---------------------------------------------------
565
566 FUNCTION validate_lrs_rate(p_lrs_rate IN number) RETURN varchar2 IS
567 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_LRS_RATE';
568
569 BEGIN
570
571 RETURN g_ret_sts_success;
572 EXCEPTION
573 WHEN okl_api.g_exception_error THEN
574 RETURN g_ret_sts_error;
575 WHEN okl_api.g_exception_unexpected_error THEN
576 RETURN g_ret_sts_unexp_error;
577 WHEN OTHERS THEN
578 okl_api.set_message(p_app_name => g_app_name
579 ,p_msg_name => g_db_error
580 ,p_token1 => g_prog_name_token
581 ,p_token1_value => l_api_name
582 ,p_token2 => 'SQLCODE'
583 ,p_token2_value => sqlcode
584 ,p_token3 => 'SQLERRM'
585 ,p_token3_value => sqlerrm);
586 RETURN g_ret_sts_unexp_error;
587 END validate_lrs_rate;
588
589 FUNCTION validate_residual_tolerance(p_residual_tolerance IN number) RETURN varchar2 IS
590 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_residual_tolerance';
591
592 BEGIN
593
594
595 IF (p_residual_tolerance IS NOT NULL) AND (p_residual_tolerance <> okl_api.g_miss_num) THEN
596 IF (p_residual_tolerance > 100 OR p_residual_tolerance < 0) THEN
597 okl_api.set_message(p_app_name => g_app_name
598 ,p_msg_name => g_invalid_value
599 ,p_token1 => g_col_name_token
600 ,p_token1_value => 'residual_tolerance');
601 RAISE okl_api.g_exception_error;
602 END IF;
603 END IF;
604 RETURN g_ret_sts_success;
605 EXCEPTION
606 WHEN okl_api.g_exception_error THEN
607 RETURN g_ret_sts_error;
608 WHEN okl_api.g_exception_unexpected_error THEN
609 RETURN g_ret_sts_unexp_error;
610 WHEN OTHERS THEN
611 okl_api.set_message(p_app_name => g_app_name
612 ,p_msg_name => g_db_error
613 ,p_token1 => g_prog_name_token
614 ,p_token1_value => l_api_name
615 ,p_token2 => 'SQLCODE'
616 ,p_token2_value => sqlcode
617 ,p_token3 => 'SQLERRM'
618 ,p_token3_value => sqlerrm);
619 RETURN g_ret_sts_unexp_error;
620 END validate_residual_tolerance;
621
622 FUNCTION validate_rate_tolerance(p_rate_tolerance IN number) RETURN varchar2 IS
623 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_rate_tolerance';
624
625 BEGIN
626
627
628 IF (p_rate_tolerance IS NOT NULL) AND (p_rate_tolerance <> okl_api.g_miss_num) THEN
629 IF (p_rate_tolerance > 100 OR p_rate_tolerance < 0) THEN
630 okl_api.set_message(p_app_name => g_app_name
631 ,p_msg_name => g_invalid_value
632 ,p_token1 => g_col_name_token
633 ,p_token1_value => 'rate_tolerance');
634 RAISE okl_api.g_exception_error;
635 END IF;
636 END IF;
637 RETURN g_ret_sts_success;
638 EXCEPTION
639 WHEN okl_api.g_exception_error THEN
640 RETURN g_ret_sts_error;
641 WHEN okl_api.g_exception_unexpected_error THEN
642 RETURN g_ret_sts_unexp_error;
643 WHEN OTHERS THEN
644 okl_api.set_message(p_app_name => g_app_name
645 ,p_msg_name => g_db_error
646 ,p_token1 => g_prog_name_token
647 ,p_token1_value => l_api_name
648 ,p_token2 => 'SQLCODE'
649 ,p_token2_value => sqlcode
650 ,p_token3 => 'SQLERRM'
651 ,p_token3_value => sqlerrm);
652 RETURN g_ret_sts_unexp_error;
653 END validate_rate_tolerance;
654
655
656 --------------------------------------------------------------------------------
657 -- Procedure get_rec for OKL_FE_RATE_SET_VERSIONS_V
658 --------------------------------------------------------------------------------
659
660 FUNCTION get_rec(p_lrvv_rec IN okl_lrvv_rec
661 ,x_no_data_found OUT NOCOPY boolean) RETURN okl_lrvv_rec IS
662
663 CURSOR lrvv_pk_csr(p_id IN number) IS
664 SELECT rate_set_version_id
665 ,object_version_number
666 ,arrears_yn
667 ,effective_from_date
668 ,effective_to_date
669 ,rate_set_id
670 ,end_of_term_ver_id
671 ,std_rate_tmpl_ver_id
672 ,adj_mat_version_id
673 ,version_number
674 ,lrs_rate
675 ,rate_tolerance
676 ,residual_tolerance
677 ,deferred_pmts
678 ,advance_pmts
679 ,sts_code
680 ,created_by
681 ,creation_date
682 ,last_updated_by
683 ,last_update_date
684 ,last_update_login
685 ,attribute_category
686 ,attribute1
687 ,attribute2
688 ,attribute3
689 ,attribute4
690 ,attribute5
691 ,attribute6
692 ,attribute7
693 ,attribute8
694 ,attribute9
695 ,attribute10
696 ,attribute11
697 ,attribute12
698 ,attribute13
699 ,attribute14
700 ,attribute15
701 ,standard_rate
702 FROM okl_fe_rate_set_versions_v
703 WHERE rate_set_version_id = p_id;
704 l_lrvv_pk lrvv_pk_csr%ROWTYPE;
705 l_lrvv_rec okl_lrvv_rec;
706
707 BEGIN
708 x_no_data_found := true;
709
710 --Get current data base values
711
712
713 OPEN lrvv_pk_csr(p_lrvv_rec.rate_set_version_id);
714 FETCH lrvv_pk_csr INTO l_lrvv_rec.rate_set_version_id
715 ,l_lrvv_rec.object_version_number
716 ,l_lrvv_rec.arrears_yn
717 ,l_lrvv_rec.effective_from_date
718 ,l_lrvv_rec.effective_to_date
719 ,l_lrvv_rec.rate_set_id
720 ,l_lrvv_rec.end_of_term_ver_id
721 ,l_lrvv_rec.std_rate_tmpl_ver_id
722 ,l_lrvv_rec.adj_mat_version_id
723 ,l_lrvv_rec.version_number
724 ,l_lrvv_rec.lrs_rate
725 ,l_lrvv_rec.rate_tolerance
726 ,l_lrvv_rec.residual_tolerance
727 ,l_lrvv_rec.deferred_pmts
728 ,l_lrvv_rec.advance_pmts
729 ,l_lrvv_rec.sts_code
730 ,l_lrvv_rec.created_by
731 ,l_lrvv_rec.creation_date
732 ,l_lrvv_rec.last_updated_by
733 ,l_lrvv_rec.last_update_date
734 ,l_lrvv_rec.last_update_login
735 ,l_lrvv_rec.attribute_category
736 ,l_lrvv_rec.attribute1
737 ,l_lrvv_rec.attribute2
738 ,l_lrvv_rec.attribute3
739 ,l_lrvv_rec.attribute4
740 ,l_lrvv_rec.attribute5
741 ,l_lrvv_rec.attribute6
742 ,l_lrvv_rec.attribute7
743 ,l_lrvv_rec.attribute8
744 ,l_lrvv_rec.attribute9
745 ,l_lrvv_rec.attribute10
746 ,l_lrvv_rec.attribute11
747 ,l_lrvv_rec.attribute12
748 ,l_lrvv_rec.attribute13
749 ,l_lrvv_rec.attribute14
750 ,l_lrvv_rec.attribute15
751 ,l_lrvv_rec.standard_rate;
752 x_no_data_found := lrvv_pk_csr%NOTFOUND;
753 CLOSE lrvv_pk_csr;
754 RETURN(l_lrvv_rec);
755 END get_rec;
756
757 FUNCTION get_rec(p_lrvv_rec IN okl_lrvv_rec) RETURN okl_lrvv_rec IS
758 l_row_notfound boolean := true;
759
760 BEGIN
761 RETURN(get_rec(p_lrvv_rec, l_row_notfound));
762 END get_rec;
763
764 FUNCTION null_out_defaults(p_lrvv_rec IN okl_lrvv_rec) RETURN okl_lrvv_rec IS
765 l_lrvv_rec okl_lrvv_rec := p_lrvv_rec;
766
767 BEGIN
768
769 IF (l_lrvv_rec.rate_set_version_id = okl_api.g_miss_num) THEN
770 l_lrvv_rec.rate_set_version_id := NULL;
771 END IF;
772
773 IF (l_lrvv_rec.object_version_number = okl_api.g_miss_num) THEN
774 l_lrvv_rec.object_version_number := NULL;
775 END IF;
776
777 IF (l_lrvv_rec.arrears_yn = okl_api.g_miss_char) THEN
778 l_lrvv_rec.arrears_yn := NULL;
779 END IF;
780
781 IF (l_lrvv_rec.effective_from_date = okl_api.g_miss_date) THEN
782 l_lrvv_rec.effective_from_date := NULL;
783 END IF;
784
785 IF (l_lrvv_rec.effective_to_date = okl_api.g_miss_date) THEN
786 l_lrvv_rec.effective_to_date := NULL;
787 END IF;
788
789 IF (l_lrvv_rec.rate_set_id = okl_api.g_miss_num) THEN
790 l_lrvv_rec.rate_set_id := NULL;
791 END IF;
792
793 IF (l_lrvv_rec.end_of_term_ver_id = okl_api.g_miss_num) THEN
794 l_lrvv_rec.end_of_term_ver_id := NULL;
795 END IF;
796
797 IF (l_lrvv_rec.std_rate_tmpl_ver_id = okl_api.g_miss_num) THEN
798 l_lrvv_rec.std_rate_tmpl_ver_id := NULL;
799 END IF;
800
801 IF (l_lrvv_rec.adj_mat_version_id = okl_api.g_miss_num) THEN
802 l_lrvv_rec.adj_mat_version_id := NULL;
803 END IF;
804
805 IF (l_lrvv_rec.version_number = okl_api.g_miss_char) THEN
806 l_lrvv_rec.version_number := NULL;
807 END IF;
808
809 IF (l_lrvv_rec.lrs_rate = okl_api.g_miss_num) THEN
810 l_lrvv_rec.lrs_rate := NULL;
811 END IF;
812
813 IF (l_lrvv_rec.rate_tolerance = okl_api.g_miss_num) THEN
814 l_lrvv_rec.rate_tolerance := NULL;
815 END IF;
816
817 IF (l_lrvv_rec.residual_tolerance = okl_api.g_miss_num) THEN
818 l_lrvv_rec.residual_tolerance := NULL;
819 END IF;
820
821 IF (l_lrvv_rec.deferred_pmts = okl_api.g_miss_num) THEN
822 l_lrvv_rec.deferred_pmts := NULL;
823 END IF;
824
825 IF (l_lrvv_rec.advance_pmts = okl_api.g_miss_num) THEN
826 l_lrvv_rec.advance_pmts := NULL;
827 END IF;
828
829 IF (l_lrvv_rec.sts_code = okl_api.g_miss_char) THEN
830 l_lrvv_rec.sts_code := NULL;
831 END IF;
832
833 IF (l_lrvv_rec.created_by = okl_api.g_miss_num) THEN
834 l_lrvv_rec.created_by := NULL;
835 END IF;
836
837 IF (l_lrvv_rec.creation_date = okl_api.g_miss_date) THEN
838 l_lrvv_rec.creation_date := NULL;
839 END IF;
840
841 IF (l_lrvv_rec.last_updated_by = okl_api.g_miss_num) THEN
842 l_lrvv_rec.last_updated_by := NULL;
843 END IF;
844
845 IF (l_lrvv_rec.last_update_date = okl_api.g_miss_date) THEN
846 l_lrvv_rec.last_update_date := NULL;
847 END IF;
848
849 IF (l_lrvv_rec.last_update_login = okl_api.g_miss_num) THEN
850 l_lrvv_rec.last_update_login := NULL;
851 END IF;
852
853 IF (l_lrvv_rec.attribute_category = okl_api.g_miss_char) THEN
854 l_lrvv_rec.attribute_category := NULL;
855 END IF;
856
857 IF (l_lrvv_rec.attribute1 = okl_api.g_miss_char) THEN
858 l_lrvv_rec.attribute1 := NULL;
859 END IF;
860
861 IF (l_lrvv_rec.attribute2 = okl_api.g_miss_char) THEN
862 l_lrvv_rec.attribute2 := NULL;
863 END IF;
864
865 IF (l_lrvv_rec.attribute3 = okl_api.g_miss_char) THEN
866 l_lrvv_rec.attribute3 := NULL;
867 END IF;
868
869 IF (l_lrvv_rec.attribute4 = okl_api.g_miss_char) THEN
870 l_lrvv_rec.attribute4 := NULL;
871 END IF;
872
873 IF (l_lrvv_rec.attribute5 = okl_api.g_miss_char) THEN
874 l_lrvv_rec.attribute5 := NULL;
875 END IF;
876
877 IF (l_lrvv_rec.attribute6 = okl_api.g_miss_char) THEN
878 l_lrvv_rec.attribute6 := NULL;
879 END IF;
880
881 IF (l_lrvv_rec.attribute7 = okl_api.g_miss_char) THEN
882 l_lrvv_rec.attribute7 := NULL;
883 END IF;
884
885 IF (l_lrvv_rec.attribute8 = okl_api.g_miss_char) THEN
886 l_lrvv_rec.attribute8 := NULL;
887 END IF;
888
889 IF (l_lrvv_rec.attribute9 = okl_api.g_miss_char) THEN
890 l_lrvv_rec.attribute9 := NULL;
891 END IF;
892
893 IF (l_lrvv_rec.attribute10 = okl_api.g_miss_char) THEN
894 l_lrvv_rec.attribute10 := NULL;
895 END IF;
896
897 IF (l_lrvv_rec.attribute11 = okl_api.g_miss_char) THEN
898 l_lrvv_rec.attribute11 := NULL;
899 END IF;
900
901 IF (l_lrvv_rec.attribute12 = okl_api.g_miss_char) THEN
902 l_lrvv_rec.attribute12 := NULL;
903 END IF;
904
905 IF (l_lrvv_rec.attribute13 = okl_api.g_miss_char) THEN
906 l_lrvv_rec.attribute13 := NULL;
907 END IF;
908
909 IF (l_lrvv_rec.attribute14 = okl_api.g_miss_char) THEN
910 l_lrvv_rec.attribute14 := NULL;
911 END IF;
912
913 IF (l_lrvv_rec.attribute15 = okl_api.g_miss_char) THEN
914 l_lrvv_rec.attribute15 := NULL;
915 END IF;
916
917 IF (l_lrvv_rec.standard_rate = okl_api.g_miss_num) THEN
918 l_lrvv_rec.standard_rate := NULL;
919 END IF;
920 RETURN(l_lrvv_rec);
921 END null_out_defaults;
922
923 FUNCTION get_seq_id RETURN number IS
924
925 BEGIN
926 RETURN(okc_p_util.raw_to_number(sys_guid()));
927 END get_seq_id;
928
929 FUNCTION validate_attributes(p_lrvv_rec IN okl_lrvv_rec) RETURN varchar2 IS
930 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
931 x_return_status varchar2(1) := okl_api.g_ret_sts_success;
932 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_attributes';
933
934 BEGIN
935
936 -- call each column-level validation
937
938
939 l_return_status := validate_id(p_lrvv_rec.rate_set_version_id);
940
941 IF (l_return_status = g_ret_sts_unexp_error) THEN
942 RAISE okl_api.g_exception_unexpected_error;
943 ELSIF (l_return_status = g_ret_sts_error) THEN
944 RAISE okl_api.g_exception_error;
945 END IF;
946 l_return_status := validate_object_version_number(p_lrvv_rec.object_version_number);
947
948 IF (l_return_status = g_ret_sts_unexp_error) THEN
949 RAISE okl_api.g_exception_unexpected_error;
950 ELSIF (l_return_status = g_ret_sts_error) THEN
951 RAISE okl_api.g_exception_error;
952 END IF;
953 l_return_status := validate_arrears_yn(p_lrvv_rec.arrears_yn);
954
955 IF (l_return_status = g_ret_sts_unexp_error) THEN
956 RAISE okl_api.g_exception_unexpected_error;
957 ELSIF (l_return_status = g_ret_sts_error) THEN
958 RAISE okl_api.g_exception_error;
959 END IF;
960 l_return_status := validate_effective_from_date(p_lrvv_rec.effective_from_date);
961
962 IF (l_return_status = g_ret_sts_unexp_error) THEN
963 RAISE okl_api.g_exception_unexpected_error;
964 ELSIF (l_return_status = g_ret_sts_error) THEN
965 RAISE okl_api.g_exception_error;
966 END IF;
967 l_return_status := validate_rate_set_id(p_lrvv_rec.rate_set_id);
968
969 IF (l_return_status = g_ret_sts_unexp_error) THEN
970 RAISE okl_api.g_exception_unexpected_error;
971 ELSIF (l_return_status = g_ret_sts_error) THEN
972 RAISE okl_api.g_exception_error;
973 END IF;
974 l_return_status := validate_rate_tolerance(p_lrvv_rec.rate_tolerance);
975
976 IF (l_return_status = g_ret_sts_unexp_error) THEN
977 RAISE okl_api.g_exception_unexpected_error;
978 ELSIF (l_return_status = g_ret_sts_error) THEN
979 RAISE okl_api.g_exception_error;
980 END IF;
981 l_return_status := validate_residual_tolerance(p_lrvv_rec.residual_tolerance);
982
983 IF (l_return_status = g_ret_sts_unexp_error) THEN
984 RAISE okl_api.g_exception_unexpected_error;
985 ELSIF (l_return_status = g_ret_sts_error) THEN
986 RAISE okl_api.g_exception_error;
987 END IF;
988 l_return_status := validate_lrs_rate(p_lrvv_rec.lrs_rate);
989
990 IF (l_return_status = g_ret_sts_unexp_error) THEN
991 RAISE okl_api.g_exception_unexpected_error;
992 ELSIF (l_return_status = g_ret_sts_error) THEN
993 RAISE okl_api.g_exception_error;
994 END IF;
995 l_return_status := validate_sts_code(p_lrvv_rec.sts_code);
996
997 IF (l_return_status = g_ret_sts_unexp_error) THEN
998 RAISE okl_api.g_exception_unexpected_error;
999 ELSIF (l_return_status = g_ret_sts_error) THEN
1000 RAISE okl_api.g_exception_error;
1001 END IF;
1002 l_return_status := validate_version_number(p_lrvv_rec.version_number);
1003
1004 IF (l_return_status = g_ret_sts_unexp_error) THEN
1005 RAISE okl_api.g_exception_unexpected_error;
1006 ELSIF (l_return_status = g_ret_sts_error) THEN
1007 RAISE okl_api.g_exception_error;
1008 END IF;
1009 RETURN(x_return_status);
1010 EXCEPTION
1011 WHEN okl_api.g_exception_error THEN
1012 RETURN g_ret_sts_error;
1013 WHEN okl_api.g_exception_unexpected_error THEN
1014 RETURN g_ret_sts_unexp_error;
1015 WHEN OTHERS THEN
1016 okl_api.set_message(p_app_name => g_app_name
1017 ,p_msg_name => g_db_error
1018 ,p_token1 => g_prog_name_token
1019 ,p_token1_value => l_api_name
1020 ,p_token2 => 'SQLCODE'
1021 ,p_token2_value => sqlcode
1022 ,p_token3 => 'SQLERRM'
1023 ,p_token3_value => sqlerrm);
1024 RETURN g_ret_sts_unexp_error;
1025 END validate_attributes;
1026
1027 FUNCTION validate_record(p_lrvv_rec IN okl_lrvv_rec) RETURN varchar2 IS
1028
1029 CURSOR l_pve_csr IS
1030 SELECT 'x'
1031 FROM okl_fe_eo_term_vers
1032 WHERE end_of_term_ver_id = p_lrvv_rec.end_of_term_ver_id
1033 AND p_lrvv_rec.effective_from_date BETWEEN effective_from_date AND nvl(effective_to_date, p_lrvv_rec.effective_from_date + 1);
1034
1035 CURSOR l_srv_csr IS
1036 SELECT 'x'
1037 FROM okl_fe_std_rt_tmp_vers
1038 WHERE std_rate_tmpl_ver_id = p_lrvv_rec.std_rate_tmpl_ver_id
1039 AND p_lrvv_rec.effective_from_date BETWEEN effective_from_date AND nvl(effective_to_date, p_lrvv_rec.effective_from_date + 1);
1040
1041 CURSOR l_pal_csr IS
1042 SELECT 'x'
1043 FROM okl_fe_adj_mat_versions
1044 WHERE adj_mat_version_id = p_lrvv_rec.adj_mat_version_id
1045 AND p_lrvv_rec.effective_from_date BETWEEN effective_from_date AND nvl(effective_to_date, p_lrvv_rec.effective_from_date + 1);
1046 l_dummy_var varchar2(1) := '?';
1047 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1048 x_return_status varchar2(1) := okl_api.g_ret_sts_success;
1049 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_record';
1050
1051 BEGIN
1052
1053 --If eff_to is not null then if eff_from > eff_to, its error
1054
1055 IF p_lrvv_rec.effective_to_date IS NOT NULL THEN
1056 IF p_lrvv_rec.effective_from_date > p_lrvv_rec.effective_to_date THEN
1057 okl_api.set_message(p_app_name => g_app_name
1058 ,p_msg_name => g_invalid_value
1059 ,p_token1 => g_col_name_token
1060 ,p_token1_value => 'Effective To');
1061 RAISE okl_api.g_exception_error;
1062 END IF;
1063 END IF;
1064
1065 --validate that the eff_from of lrs is between purchase option version eff from and to
1066
1067 OPEN l_pve_csr;
1068 FETCH l_pve_csr INTO l_dummy_var ;
1069 CLOSE l_pve_csr;
1070
1071 -- if l_dummy_var is still set to default, data was not found
1072
1073 IF (l_dummy_var = '?') THEN
1074 okl_api.set_message(p_app_name => g_app_name
1075 ,p_msg_name => g_no_parent_record
1076 ,p_token1 => g_col_name_token
1077 ,p_token1_value => 'END_OF_TERM_VER_ID'
1078 ,p_token2 => g_child_table_token
1079 ,p_token2_value => 'OKL_FE_RATE_SET_VERSIONS'
1080 ,p_token3 => g_parent_table_token
1081 ,p_token3_value => 'OKL_FE_EO_TERM_VERS');
1082 RAISE okl_api.g_exception_error;
1083 END IF;
1084 l_dummy_var := '?';
1085
1086 --validate that the eff_from of lrs is between srt version eff from and to
1087
1088 IF p_lrvv_rec.std_rate_tmpl_ver_id IS NOT NULL AND p_lrvv_rec.std_rate_tmpl_ver_id <> g_miss_num THEN
1089 OPEN l_srv_csr;
1090 FETCH l_srv_csr INTO l_dummy_var ;
1091 CLOSE l_srv_csr;
1092
1093 -- if l_dummy_var is still set to default, data was not found
1094
1095 IF (l_dummy_var = '?') THEN
1096 okl_api.set_message(p_app_name => g_app_name
1097 ,p_msg_name => g_no_parent_record
1098 ,p_token1 => g_col_name_token
1099 ,p_token1_value => 'STD_RATE_TMPL_VER_ID'
1100 ,p_token2 => g_child_table_token
1101 ,p_token2_value => 'OKL_FE_RATE_SET_VERSIONS'
1102 ,p_token3 => g_parent_table_token
1103 ,p_token3_value => 'OKL_FE_SRT_VERSIONS');
1104 RAISE okl_api.g_exception_error;
1105 END IF;
1106 END IF;
1107 l_dummy_var := '?';
1108
1109
1110 --validate that the eff_from of lrs is between pam version eff from and to
1111
1112 IF p_lrvv_rec.adj_mat_version_id IS NOT NULL AND p_lrvv_rec.adj_mat_version_id <> g_miss_num THEN
1113 OPEN l_pal_csr;
1114 FETCH l_pal_csr INTO l_dummy_var ;
1115 CLOSE l_pal_csr;
1116
1117 -- if l_dummy_var is still set to default, data was not found
1118
1119 IF (l_dummy_var = '?') THEN
1120 okl_api.set_message(p_app_name => g_app_name
1121 ,p_msg_name => g_no_parent_record
1122 ,p_token1 => g_col_name_token
1123 ,p_token1_value => 'PAM_VERSION_ID'
1124 ,p_token2 => g_child_table_token
1125 ,p_token2_value => 'OKL_FE_RATE_SET_VERSIONS'
1126 ,p_token3 => g_parent_table_token
1127 ,p_token3_value => 'OKL_FE_ADJ_MAT_VERSIONS');
1128 RAISE okl_api.g_exception_error;
1129 END IF;
1130 END IF;
1131
1132 --validate that either of srt verion or rate are present
1133
1134 IF p_lrvv_rec.lrs_rate IS NULL OR p_lrvv_rec.lrs_rate = g_miss_num THEN
1135 IF p_lrvv_rec.std_rate_tmpl_ver_id IS NULL OR p_lrvv_rec.std_rate_tmpl_ver_id = g_miss_num THEN
1136 okl_api.set_message(p_app_name => g_app_name
1137 ,p_msg_name => 'OKL_SRT_OR_RATE_SHUD_EXISTS');
1138 RAISE okl_api.g_exception_error;
1139 END IF;
1140 END IF;
1141 RETURN(x_return_status);
1142 EXCEPTION
1143 WHEN okl_api.g_exception_error THEN
1144 RETURN g_ret_sts_error;
1145 WHEN okl_api.g_exception_unexpected_error THEN
1146 RETURN g_ret_sts_unexp_error;
1147 WHEN OTHERS THEN
1148 okl_api.set_message(p_app_name => g_app_name
1149 ,p_msg_name => g_db_error
1150 ,p_token1 => g_prog_name_token
1151 ,p_token1_value => l_api_name
1152 ,p_token2 => 'SQLCODE'
1153 ,p_token2_value => sqlcode
1154 ,p_token3 => 'SQLERRM'
1155 ,p_token3_value => sqlerrm);
1156 RETURN g_ret_sts_unexp_error;
1157 END validate_record;
1158
1159 --------------------------------------------------------------------------------
1160 -- Procedure insert_row_V
1161 --------------------------------------------------------------------------------
1162
1163 PROCEDURE insert_row(p_api_version IN number
1164 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1165 ,x_return_status OUT NOCOPY varchar2
1166 ,x_msg_count OUT NOCOPY number
1167 ,x_msg_data OUT NOCOPY varchar2
1168 ,p_lrvv_rec IN okl_lrvv_rec
1169 ,x_lrvv_rec OUT NOCOPY okl_lrvv_rec) IS
1170 l_api_version CONSTANT number := 1;
1171 l_api_name CONSTANT varchar2(30) := 'insert_row';
1172 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1173 l_lrvv_rec okl_lrvv_rec := p_lrvv_rec;
1174 l_def_lrvv_rec okl_lrvv_rec;
1175
1176 FUNCTION fill_who_columns(p_lrvv_rec IN okl_lrvv_rec) RETURN okl_lrvv_rec IS
1177 l_lrvv_rec okl_lrvv_rec := p_lrvv_rec;
1178
1179 BEGIN
1180 l_lrvv_rec.creation_date := sysdate;
1181 l_lrvv_rec.created_by := fnd_global.user_id;
1182 l_lrvv_rec.last_update_date := sysdate;
1183 l_lrvv_rec.last_updated_by := fnd_global.user_id;
1184 l_lrvv_rec.last_update_login := fnd_global.login_id;
1185 RETURN(l_lrvv_rec);
1186 END fill_who_columns;
1187
1188 FUNCTION set_attributes(p_lrvv_rec IN okl_lrvv_rec
1189 ,x_lrvv_rec OUT NOCOPY okl_lrvv_rec) RETURN varchar2 IS
1190 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1191
1192 BEGIN
1193 x_lrvv_rec := p_lrvv_rec;
1194 x_lrvv_rec.object_version_number := 1;
1195
1196 -- Set Primary key value
1197
1198 x_lrvv_rec.rate_set_version_id := get_seq_id;
1199 RETURN(l_return_status);
1200 END set_attributes;
1201
1202 BEGIN
1203 l_return_status := okl_api.start_activity(l_api_name
1204 ,g_pkg_name
1205 ,p_init_msg_list
1206 ,l_api_version
1207 ,p_api_version
1208 ,'_PVT'
1209 ,x_return_status);
1210
1211 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1212 RAISE okl_api.g_exception_unexpected_error;
1213 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1214 RAISE okl_api.g_exception_error;
1215 END IF;
1216
1217 --null out defaults
1218
1219 l_lrvv_rec := null_out_defaults(p_lrvv_rec);
1220
1221 --Setting Item Attributes
1222
1223 l_return_status := set_attributes(l_lrvv_rec, l_def_lrvv_rec);
1224
1225 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1226 RAISE okl_api.g_exception_unexpected_error;
1227 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1228 RAISE okl_api.g_exception_error;
1229 END IF;
1230
1231 --fill who columns
1232
1233 l_def_lrvv_rec := fill_who_columns(l_def_lrvv_rec);
1234
1235 --validate attributes
1236
1237
1238 l_return_status := validate_attributes(l_def_lrvv_rec);
1239
1240
1241 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1242 RAISE okl_api.g_exception_unexpected_error;
1243 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1244 RAISE okl_api.g_exception_error;
1245 END IF;
1246
1247 --validate record
1248
1249
1250 l_return_status := validate_record(l_def_lrvv_rec);
1251
1252 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1253 RAISE okl_api.g_exception_unexpected_error;
1254 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1255 RAISE okl_api.g_exception_error;
1256 END IF;
1257
1258 --insert into table
1259
1260 INSERT INTO okl_fe_rate_set_versions
1261 (rate_set_version_id
1262 ,object_version_number
1263 ,arrears_yn
1264 ,effective_from_date
1265 ,effective_to_date
1266 ,rate_set_id
1267 ,end_of_term_ver_id
1268 ,std_rate_tmpl_ver_id
1269 ,adj_mat_version_id
1270 ,version_number
1271 ,lrs_rate
1272 ,rate_tolerance
1273 ,residual_tolerance
1274 ,deferred_pmts
1275 ,advance_pmts
1276 ,sts_code
1277 ,created_by
1278 ,creation_date
1279 ,last_updated_by
1280 ,last_update_date
1281 ,last_update_login
1282 ,attribute_category
1283 ,attribute1
1284 ,attribute2
1285 ,attribute3
1286 ,attribute4
1287 ,attribute5
1288 ,attribute6
1289 ,attribute7
1290 ,attribute8
1291 ,attribute9
1292 ,attribute10
1293 ,attribute11
1294 ,attribute12
1295 ,attribute13
1296 ,attribute14
1297 ,attribute15
1298 ,standard_rate)
1299 VALUES (l_def_lrvv_rec.rate_set_version_id
1300 ,l_def_lrvv_rec.object_version_number
1301 ,l_def_lrvv_rec.arrears_yn
1302 ,l_def_lrvv_rec.effective_from_date
1303 ,l_def_lrvv_rec.effective_to_date
1304 ,l_def_lrvv_rec.rate_set_id
1305 ,l_def_lrvv_rec.end_of_term_ver_id
1306 ,l_def_lrvv_rec.std_rate_tmpl_ver_id
1307 ,l_def_lrvv_rec.adj_mat_version_id
1308 ,l_def_lrvv_rec.version_number
1309 ,l_def_lrvv_rec.lrs_rate
1310 ,l_def_lrvv_rec.rate_tolerance
1311 ,l_def_lrvv_rec.residual_tolerance
1312 ,l_def_lrvv_rec.deferred_pmts
1313 ,l_def_lrvv_rec.advance_pmts
1314 ,l_def_lrvv_rec.sts_code
1315 ,l_def_lrvv_rec.created_by
1316 ,l_def_lrvv_rec.creation_date
1317 ,l_def_lrvv_rec.last_updated_by
1318 ,l_def_lrvv_rec.last_update_date
1319 ,l_def_lrvv_rec.last_update_login
1320 ,l_def_lrvv_rec.attribute_category
1321 ,l_def_lrvv_rec.attribute1
1322 ,l_def_lrvv_rec.attribute2
1323 ,l_def_lrvv_rec.attribute3
1324 ,l_def_lrvv_rec.attribute4
1325 ,l_def_lrvv_rec.attribute5
1326 ,l_def_lrvv_rec.attribute6
1327 ,l_def_lrvv_rec.attribute7
1328 ,l_def_lrvv_rec.attribute8
1329 ,l_def_lrvv_rec.attribute9
1330 ,l_def_lrvv_rec.attribute10
1331 ,l_def_lrvv_rec.attribute11
1332 ,l_def_lrvv_rec.attribute12
1333 ,l_def_lrvv_rec.attribute13
1334 ,l_def_lrvv_rec.attribute14
1335 ,l_def_lrvv_rec.attribute15
1336 ,l_def_lrvv_rec.standard_rate);
1337
1338 --Set OUT Values
1339
1340 x_lrvv_rec := l_def_lrvv_rec;
1341 x_return_status := l_return_status;
1342 okl_api.end_activity(x_msg_count, x_msg_data);
1343 EXCEPTION
1344 WHEN g_exception_halt_validation THEN
1345
1346 -- No action necessary. Validation can continue to next attribute/column
1347
1348 NULL;
1349 WHEN okl_api.g_exception_error THEN
1350 x_return_status := okl_api.handle_exceptions(l_api_name
1351 ,g_pkg_name
1352 ,'OKL_API.G_RET_STS_ERROR'
1353 ,x_msg_count
1354 ,x_msg_data
1355 ,'_PVT');
1356 WHEN okl_api.g_exception_unexpected_error THEN
1357 x_return_status := okl_api.handle_exceptions(l_api_name
1358 ,g_pkg_name
1359 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1360 ,x_msg_count
1361 ,x_msg_data
1362 ,'_PVT');
1363 WHEN OTHERS THEN
1364 x_return_status := okl_api.handle_exceptions(l_api_name
1365 ,g_pkg_name
1366 ,'OTHERS'
1367 ,x_msg_count
1368 ,x_msg_data
1369 ,'_PVT');
1370 END insert_row;
1371
1372 --------------------------------------------------------------------------------
1373 -- Procedure insert_row_tbl
1374 --------------------------------------------------------------------------------
1375
1376 PROCEDURE insert_row(p_api_version IN number
1377 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1378 ,x_return_status OUT NOCOPY varchar2
1379 ,x_msg_count OUT NOCOPY number
1380 ,x_msg_data OUT NOCOPY varchar2
1381 ,p_lrvv_tbl IN okl_lrvv_tbl
1382 ,x_lrvv_tbl OUT NOCOPY okl_lrvv_tbl) IS
1383 l_api_version CONSTANT number := 1;
1384 l_api_name CONSTANT varchar2(30) := 'v_insert_row';
1385 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1386 i number := 0;
1387 l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
1388
1389 BEGIN
1390 okl_api.init_msg_list(p_init_msg_list);
1391
1392 -- Make sure PL/SQL table has records in it before passing
1393
1394 IF (p_lrvv_tbl.COUNT > 0) THEN
1395 i := p_lrvv_tbl.FIRST;
1396
1397 LOOP
1398 insert_row(p_api_version => p_api_version
1399 ,p_init_msg_list => okl_api.g_false
1400 ,x_return_status => x_return_status
1401 ,x_msg_count => x_msg_count
1402 ,x_msg_data => x_msg_data
1403 ,p_lrvv_rec => p_lrvv_tbl(i)
1404 ,x_lrvv_rec => x_lrvv_tbl(i));
1405 IF x_return_status <> okl_api.g_ret_sts_success THEN
1406 IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1407 l_overall_status := x_return_status;
1408 END IF;
1409 END IF;
1410 EXIT WHEN(i = p_lrvv_tbl.LAST);
1411 i := p_lrvv_tbl.next(i);
1412 END LOOP;
1413 x_return_status := l_overall_status;
1414 END IF;
1415
1416 EXCEPTION
1417 WHEN g_exception_halt_validation THEN
1418
1419 -- No action necessary. Validation can continue to next attribute/column
1420
1421 NULL;
1422 WHEN okl_api.g_exception_error THEN
1423 x_return_status := okl_api.handle_exceptions(l_api_name
1424 ,g_pkg_name
1425 ,'OKL_API.G_RET_STS_ERROR'
1426 ,x_msg_count
1427 ,x_msg_data
1428 ,'_PVT');
1429 WHEN okl_api.g_exception_unexpected_error THEN
1430 x_return_status := okl_api.handle_exceptions(l_api_name
1431 ,g_pkg_name
1432 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1433 ,x_msg_count
1434 ,x_msg_data
1435 ,'_PVT');
1436 WHEN OTHERS THEN
1437 x_return_status := okl_api.handle_exceptions(l_api_name
1438 ,g_pkg_name
1439 ,'OTHERS'
1440 ,x_msg_count
1441 ,x_msg_data
1442 ,'_PVT');
1443 END insert_row;
1444
1445 --------------------------------------------------------------------------------
1446 -- Procedure update_row
1447 --------------------------------------------------------------------------------
1448
1449 PROCEDURE update_row(p_api_version IN number
1450 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1451 ,x_return_status OUT NOCOPY varchar2
1452 ,x_msg_count OUT NOCOPY number
1453 ,x_msg_data OUT NOCOPY varchar2
1454 ,p_lrvv_rec IN okl_lrvv_rec
1455 ,x_lrvv_rec OUT NOCOPY okl_lrvv_rec) IS
1456 l_api_version CONSTANT number := 1;
1457 l_api_name CONSTANT varchar2(30) := 'update_row';
1458 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1459 l_lrvv_rec okl_lrvv_rec := p_lrvv_rec;
1460 l_def_lrvv_rec okl_lrvv_rec;
1461 l_row_notfound boolean := true;
1462
1463 FUNCTION fill_who_columns(p_lrvv_rec IN okl_lrvv_rec) RETURN okl_lrvv_rec IS
1464 l_lrvv_rec okl_lrvv_rec := p_lrvv_rec;
1465
1466 BEGIN
1467 l_lrvv_rec.last_update_date := sysdate;
1468 l_lrvv_rec.last_updated_by := fnd_global.user_id;
1469 l_lrvv_rec.last_update_login := fnd_global.login_id;
1470 RETURN(l_lrvv_rec);
1471 END fill_who_columns;
1472
1473 FUNCTION populate_new_record(p_lrvv_rec IN okl_lrvv_rec
1474 ,x_lrvv_rec OUT NOCOPY okl_lrvv_rec) RETURN varchar2 IS
1475 l_lrvv_rec okl_lrvv_rec;
1476 l_row_notfound boolean := true;
1477 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1478
1479 BEGIN
1480 x_lrvv_rec := p_lrvv_rec;
1481
1482 --Get current database values
1483
1484 l_lrvv_rec := get_rec(p_lrvv_rec, l_row_notfound);
1485
1486 IF (l_row_notfound) THEN
1487 l_return_status := okl_api.g_ret_sts_unexp_error;
1488 END IF;
1489
1490 IF (x_lrvv_rec.rate_set_version_id IS NULL) THEN
1491 x_lrvv_rec.rate_set_version_id := l_lrvv_rec.rate_set_version_id;
1492 END IF;
1493
1494 IF (x_lrvv_rec.arrears_yn IS NULL) THEN
1495 x_lrvv_rec.arrears_yn := l_lrvv_rec.arrears_yn;
1496 END IF;
1497
1498 IF (x_lrvv_rec.effective_from_date IS NULL) THEN
1499 x_lrvv_rec.effective_from_date := l_lrvv_rec.effective_from_date;
1500 END IF;
1501
1502 IF (x_lrvv_rec.effective_to_date IS NULL) THEN
1503 x_lrvv_rec.effective_to_date := l_lrvv_rec.effective_to_date;
1504 END IF;
1505
1506 IF (x_lrvv_rec.rate_set_id IS NULL) THEN
1507 x_lrvv_rec.rate_set_id := l_lrvv_rec.rate_set_id;
1508 END IF;
1509
1510 IF (x_lrvv_rec.end_of_term_ver_id IS NULL) THEN
1511 x_lrvv_rec.end_of_term_ver_id := l_lrvv_rec.end_of_term_ver_id;
1512 END IF;
1513
1514 IF (x_lrvv_rec.std_rate_tmpl_ver_id IS NULL) THEN
1515 x_lrvv_rec.std_rate_tmpl_ver_id := l_lrvv_rec.std_rate_tmpl_ver_id;
1516 END IF;
1517
1518 IF (x_lrvv_rec.adj_mat_version_id IS NULL) THEN
1519 x_lrvv_rec.adj_mat_version_id := l_lrvv_rec.adj_mat_version_id;
1520 END IF;
1521
1522 IF (x_lrvv_rec.version_number IS NULL) THEN
1523 x_lrvv_rec.version_number := l_lrvv_rec.version_number;
1524 END IF;
1525
1526 IF (x_lrvv_rec.lrs_rate IS NULL) THEN
1527 x_lrvv_rec.lrs_rate := l_lrvv_rec.lrs_rate;
1528 END IF;
1529
1530 IF (x_lrvv_rec.rate_tolerance IS NULL) THEN
1531 x_lrvv_rec.rate_tolerance := l_lrvv_rec.rate_tolerance;
1532 END IF;
1533
1534 IF (x_lrvv_rec.residual_tolerance IS NULL) THEN
1535 x_lrvv_rec.residual_tolerance := l_lrvv_rec.residual_tolerance;
1536 END IF;
1537
1538 IF (x_lrvv_rec.deferred_pmts IS NULL) THEN
1539 x_lrvv_rec.deferred_pmts := l_lrvv_rec.deferred_pmts;
1540 END IF;
1541
1542 IF (x_lrvv_rec.advance_pmts IS NULL) THEN
1543 x_lrvv_rec.advance_pmts := l_lrvv_rec.advance_pmts;
1544 END IF;
1545
1546 IF (x_lrvv_rec.sts_code IS NULL) THEN
1547 x_lrvv_rec.sts_code := l_lrvv_rec.sts_code;
1548 END IF;
1549
1550 IF (x_lrvv_rec.created_by IS NULL) THEN
1551 x_lrvv_rec.created_by := l_lrvv_rec.created_by;
1552 END IF;
1553
1554 IF (x_lrvv_rec.creation_date IS NULL) THEN
1555 x_lrvv_rec.creation_date := l_lrvv_rec.creation_date;
1556 END IF;
1557
1558 IF (x_lrvv_rec.last_updated_by IS NULL) THEN
1559 x_lrvv_rec.last_updated_by := l_lrvv_rec.last_updated_by;
1560 END IF;
1561
1562 IF (x_lrvv_rec.last_update_date IS NULL) THEN
1563 x_lrvv_rec.last_update_date := l_lrvv_rec.last_update_date;
1564 END IF;
1565
1566 IF (x_lrvv_rec.last_update_login IS NULL) THEN
1567 x_lrvv_rec.last_update_login := l_lrvv_rec.last_update_login;
1568 END IF;
1569
1570 IF (x_lrvv_rec.attribute_category IS NULL) THEN
1571 x_lrvv_rec.attribute_category := l_lrvv_rec.attribute_category;
1572 END IF;
1573
1574 IF (x_lrvv_rec.attribute1 IS NULL) THEN
1575 x_lrvv_rec.attribute1 := l_lrvv_rec.attribute1;
1576 END IF;
1577
1578 IF (x_lrvv_rec.attribute2 IS NULL) THEN
1579 x_lrvv_rec.attribute2 := l_lrvv_rec.attribute2;
1580 END IF;
1581
1582 IF (x_lrvv_rec.attribute3 IS NULL) THEN
1583 x_lrvv_rec.attribute3 := l_lrvv_rec.attribute3;
1584 END IF;
1585
1586 IF (x_lrvv_rec.attribute4 IS NULL) THEN
1587 x_lrvv_rec.attribute4 := l_lrvv_rec.attribute4;
1588 END IF;
1589
1590 IF (x_lrvv_rec.attribute5 IS NULL) THEN
1591 x_lrvv_rec.attribute5 := l_lrvv_rec.attribute5;
1592 END IF;
1593
1594 IF (x_lrvv_rec.attribute6 IS NULL) THEN
1595 x_lrvv_rec.attribute6 := l_lrvv_rec.attribute6;
1596 END IF;
1597
1598 IF (x_lrvv_rec.attribute7 IS NULL) THEN
1599 x_lrvv_rec.attribute7 := l_lrvv_rec.attribute7;
1600 END IF;
1601
1602 IF (x_lrvv_rec.attribute8 IS NULL) THEN
1603 x_lrvv_rec.attribute8 := l_lrvv_rec.attribute8;
1604 END IF;
1605
1606 IF (x_lrvv_rec.attribute9 IS NULL) THEN
1607 x_lrvv_rec.attribute9 := l_lrvv_rec.attribute9;
1608 END IF;
1609
1610 IF (x_lrvv_rec.attribute10 IS NULL) THEN
1611 x_lrvv_rec.attribute10 := l_lrvv_rec.attribute10;
1612 END IF;
1613
1614 IF (x_lrvv_rec.attribute11 IS NULL) THEN
1615 x_lrvv_rec.attribute11 := l_lrvv_rec.attribute11;
1616 END IF;
1617
1618 IF (x_lrvv_rec.attribute12 IS NULL) THEN
1619 x_lrvv_rec.attribute12 := l_lrvv_rec.attribute12;
1620 END IF;
1621
1622 IF (x_lrvv_rec.attribute13 IS NULL) THEN
1623 x_lrvv_rec.attribute13 := l_lrvv_rec.attribute13;
1624 END IF;
1625
1626 IF (x_lrvv_rec.attribute14 IS NULL) THEN
1627 x_lrvv_rec.attribute14 := l_lrvv_rec.attribute14;
1628 END IF;
1629
1630 IF (x_lrvv_rec.attribute15 IS NULL) THEN
1631 x_lrvv_rec.attribute15 := l_lrvv_rec.attribute15;
1632 END IF;
1633
1634 IF (x_lrvv_rec.standard_rate IS NULL) THEN
1635 x_lrvv_rec.standard_rate := l_lrvv_rec.standard_rate;
1636 END IF;
1637 RETURN(l_return_status);
1638 END populate_new_record;
1639
1640 FUNCTION set_attributes(p_lrvv_rec IN okl_lrvv_rec
1641 ,x_lrvv_rec OUT NOCOPY okl_lrvv_rec) RETURN varchar2 IS
1642 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1643
1644 BEGIN
1645 x_lrvv_rec := p_lrvv_rec;
1646 RETURN(l_return_status);
1647 END set_attributes;
1648
1649 BEGIN
1650 l_return_status := okl_api.start_activity(l_api_name
1651 ,g_pkg_name
1652 ,p_init_msg_list
1653 ,l_api_version
1654 ,p_api_version
1655 ,'_PVT'
1656 ,x_return_status);
1657
1658 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1659 RAISE okl_api.g_exception_unexpected_error;
1660 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1661 RAISE okl_api.g_exception_error;
1662 END IF;
1663
1664 --Setting Item Attributes
1665
1666 l_return_status := set_attributes(p_lrvv_rec, l_lrvv_rec);
1667
1668 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1669 RAISE okl_api.g_exception_unexpected_error;
1670 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1671 RAISE okl_api.g_exception_error;
1672 END IF;
1673
1674 --populate new record
1675
1676 l_return_status := populate_new_record(l_lrvv_rec, l_def_lrvv_rec);
1677
1678 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1679 RAISE okl_api.g_exception_unexpected_error;
1680 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1681 RAISE okl_api.g_exception_error;
1682 END IF;
1683
1684 --null out g miss values
1685
1686 l_def_lrvv_rec := null_out_defaults(l_def_lrvv_rec);
1687
1688 --fill who columns
1689
1690
1691 l_def_lrvv_rec := fill_who_columns(l_def_lrvv_rec);
1692
1693
1694 --validate attributes
1695
1696 l_return_status := validate_attributes(l_def_lrvv_rec);
1697
1698 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1699 RAISE okl_api.g_exception_unexpected_error;
1700 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1701 RAISE okl_api.g_exception_error;
1702 END IF;
1703
1704
1705 --validate record
1706
1707 l_return_status := validate_record(l_def_lrvv_rec);
1708
1709 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1710 RAISE okl_api.g_exception_unexpected_error;
1711 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1712 RAISE okl_api.g_exception_error;
1713 END IF;
1714
1715 --lock the row
1716
1717 lock_row(p_init_msg_list => okl_api.g_false
1718 ,x_return_status => l_return_status
1719 ,x_msg_count => x_msg_count
1720 ,x_msg_data => x_msg_data
1721 ,p_lrvv_rec => l_def_lrvv_rec);
1722
1723 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1724 RAISE okl_api.g_exception_unexpected_error;
1725 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1726 RAISE okl_api.g_exception_error;
1727 END IF;
1728 --update the record
1729
1730 UPDATE okl_fe_rate_set_versions
1731 SET rate_set_version_id = l_def_lrvv_rec.rate_set_version_id
1732 ,object_version_number = l_def_lrvv_rec.object_version_number + 1
1733 ,arrears_yn = l_def_lrvv_rec.arrears_yn
1734 ,effective_from_date = l_def_lrvv_rec.effective_from_date
1735 ,effective_to_date = l_def_lrvv_rec.effective_to_date
1736 ,rate_set_id = l_def_lrvv_rec.rate_set_id
1737 ,end_of_term_ver_id = l_def_lrvv_rec.end_of_term_ver_id
1738 ,std_rate_tmpl_ver_id = l_def_lrvv_rec.std_rate_tmpl_ver_id
1739 ,adj_mat_version_id = l_def_lrvv_rec.adj_mat_version_id
1740 ,version_number = l_def_lrvv_rec.version_number
1741 ,lrs_rate = l_def_lrvv_rec.lrs_rate
1742 ,rate_tolerance = l_def_lrvv_rec.rate_tolerance
1743 ,residual_tolerance = l_def_lrvv_rec.residual_tolerance
1744 ,deferred_pmts = l_def_lrvv_rec.deferred_pmts
1745 ,advance_pmts = l_def_lrvv_rec.advance_pmts
1746 ,sts_code = l_def_lrvv_rec.sts_code
1747 ,created_by = l_def_lrvv_rec.created_by
1748 ,creation_date = l_def_lrvv_rec.creation_date
1749 ,last_updated_by = l_def_lrvv_rec.last_updated_by
1750 ,last_update_date = l_def_lrvv_rec.last_update_date
1751 ,last_update_login = l_def_lrvv_rec.last_update_login
1752 ,attribute_category = l_def_lrvv_rec.attribute_category
1753 ,attribute1 = l_def_lrvv_rec.attribute1
1754 ,attribute2 = l_def_lrvv_rec.attribute2
1755 ,attribute3 = l_def_lrvv_rec.attribute3
1756 ,attribute4 = l_def_lrvv_rec.attribute4
1757 ,attribute5 = l_def_lrvv_rec.attribute5
1758 ,attribute6 = l_def_lrvv_rec.attribute6
1759 ,attribute7 = l_def_lrvv_rec.attribute7
1760 ,attribute8 = l_def_lrvv_rec.attribute8
1761 ,attribute9 = l_def_lrvv_rec.attribute9
1762 ,attribute10 = l_def_lrvv_rec.attribute10
1763 ,attribute11 = l_def_lrvv_rec.attribute11
1764 ,attribute12 = l_def_lrvv_rec.attribute12
1765 ,attribute13 = l_def_lrvv_rec.attribute13
1766 ,attribute14 = l_def_lrvv_rec.attribute14
1767 ,attribute15 = l_def_lrvv_rec.attribute15
1768 ,standard_rate = l_def_lrvv_rec.standard_rate
1769 WHERE rate_set_version_id = l_def_lrvv_rec.rate_set_version_id;
1770
1771 --Set OUT Values
1772
1773 x_lrvv_rec := l_def_lrvv_rec;
1774 x_return_status := l_return_status;
1775 okl_api.end_activity(x_msg_count, x_msg_data);
1776
1777 EXCEPTION
1778 WHEN g_exception_halt_validation THEN
1779
1780 -- No action necessary. Validation can continue to next attribute/column
1781
1782 NULL;
1783 WHEN okl_api.g_exception_error THEN
1784 x_return_status := okl_api.handle_exceptions(l_api_name
1785 ,g_pkg_name
1786 ,'OKL_API.G_RET_STS_ERROR'
1787 ,x_msg_count
1788 ,x_msg_data
1789 ,'_PVT');
1790 WHEN okl_api.g_exception_unexpected_error THEN
1791 x_return_status := okl_api.handle_exceptions(l_api_name
1792 ,g_pkg_name
1793 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1794 ,x_msg_count
1795 ,x_msg_data
1796 ,'_PVT');
1797 WHEN OTHERS THEN
1798 x_return_status := okl_api.handle_exceptions(l_api_name
1799 ,g_pkg_name
1800 ,'OTHERS'
1801 ,x_msg_count
1802 ,x_msg_data
1803 ,'_PVT');
1804 END update_row;
1805
1806 --------------------------------------------------------------------------------
1807 -- Procedure insert_row_tbl
1808 --------------------------------------------------------------------------------
1809
1810 PROCEDURE update_row(p_api_version IN number
1811 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1812 ,x_return_status OUT NOCOPY varchar2
1813 ,x_msg_count OUT NOCOPY number
1814 ,x_msg_data OUT NOCOPY varchar2
1815 ,p_lrvv_tbl IN okl_lrvv_tbl
1816 ,x_lrvv_tbl OUT NOCOPY okl_lrvv_tbl) IS
1817 l_api_version CONSTANT number := 1;
1818 l_api_name CONSTANT varchar2(30) := 'v_update_row';
1819 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1820 i number := 0;
1821 l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
1822
1823 BEGIN
1824 okl_api.init_msg_list(p_init_msg_list);
1825
1826 -- Make sure PL/SQL table has records in it before passing
1827
1828 IF (p_lrvv_tbl.COUNT > 0) THEN
1829 i := p_lrvv_tbl.FIRST;
1830
1831 LOOP
1832 update_row(p_api_version => p_api_version
1833 ,p_init_msg_list => okl_api.g_false
1834 ,x_return_status => x_return_status
1835 ,x_msg_count => x_msg_count
1836 ,x_msg_data => x_msg_data
1837 ,p_lrvv_rec => p_lrvv_tbl(i)
1838 ,x_lrvv_rec => x_lrvv_tbl(i));
1839 IF x_return_status <> okl_api.g_ret_sts_success THEN
1840 IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1841 l_overall_status := x_return_status;
1842 END IF;
1843 END IF;
1844 EXIT WHEN(i = p_lrvv_tbl.LAST);
1845 i := p_lrvv_tbl.next(i);
1846 END LOOP;
1847 x_return_status := l_overall_status;
1848 END IF;
1849
1850 EXCEPTION
1851 WHEN g_exception_halt_validation THEN
1852
1853 -- No action necessary. Validation can continue to next attribute/column
1854
1855 NULL;
1856 WHEN okl_api.g_exception_error THEN
1857 x_return_status := okl_api.handle_exceptions(l_api_name
1858 ,g_pkg_name
1859 ,'OKL_API.G_RET_STS_ERROR'
1860 ,x_msg_count
1861 ,x_msg_data
1862 ,'_PVT');
1863 WHEN okl_api.g_exception_unexpected_error THEN
1864 x_return_status := okl_api.handle_exceptions(l_api_name
1865 ,g_pkg_name
1866 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1867 ,x_msg_count
1868 ,x_msg_data
1869 ,'_PVT');
1870 WHEN OTHERS THEN
1871 x_return_status := okl_api.handle_exceptions(l_api_name
1872 ,g_pkg_name
1873 ,'OTHERS'
1874 ,x_msg_count
1875 ,x_msg_data
1876 ,'_PVT');
1877 END update_row;
1878
1879 --------------------------------------------------------------------------------
1880 -- Procedure delete_row
1881 --------------------------------------------------------------------------------
1882
1883 PROCEDURE delete_row(p_api_version IN number
1884 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1885 ,x_return_status OUT NOCOPY varchar2
1886 ,x_msg_count OUT NOCOPY number
1887 ,x_msg_data OUT NOCOPY varchar2
1888 ,p_lrvv_rec IN okl_lrvv_rec) IS
1889 l_api_version CONSTANT number := 1;
1890 l_api_name CONSTANT varchar2(30) := 'delete_row';
1891 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1892 l_lrvv_rec okl_lrvv_rec := p_lrvv_rec;
1893 l_row_notfound boolean := true;
1894
1895 BEGIN
1896 l_return_status := okl_api.start_activity(l_api_name
1897 ,g_pkg_name
1898 ,p_init_msg_list
1899 ,l_api_version
1900 ,p_api_version
1901 ,'_PVT'
1902 ,x_return_status);
1903
1904 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1905 RAISE okl_api.g_exception_unexpected_error;
1906 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1907 RAISE okl_api.g_exception_error;
1908 END IF;
1909
1910 DELETE FROM okl_fe_rate_set_versions
1911 WHERE rate_set_version_id = l_lrvv_rec.rate_set_version_id;
1912 x_return_status := l_return_status;
1913 okl_api.end_activity(x_msg_count, x_msg_data);
1914 EXCEPTION
1915 WHEN g_exception_halt_validation THEN
1916
1917 -- No action necessary. Validation can continue to next attribute/column
1918
1919 NULL;
1920 WHEN okl_api.g_exception_error THEN
1921 x_return_status := okl_api.handle_exceptions(l_api_name
1922 ,g_pkg_name
1923 ,'OKL_API.G_RET_STS_ERROR'
1924 ,x_msg_count
1925 ,x_msg_data
1926 ,'_PVT');
1927 WHEN okl_api.g_exception_unexpected_error THEN
1928 x_return_status := okl_api.handle_exceptions(l_api_name
1929 ,g_pkg_name
1930 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1931 ,x_msg_count
1932 ,x_msg_data
1933 ,'_PVT');
1934 WHEN OTHERS THEN
1935 x_return_status := okl_api.handle_exceptions(l_api_name
1936 ,g_pkg_name
1937 ,'OTHERS'
1938 ,x_msg_count
1939 ,x_msg_data
1940 ,'_PVT');
1941 END delete_row;
1942
1943 --------------------------------------------------------------------------------
1944 -- Procedure delete_row_tbl
1945 --------------------------------------------------------------------------------
1946
1947 PROCEDURE delete_row(p_api_version IN number
1948 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1949 ,x_return_status OUT NOCOPY varchar2
1950 ,x_msg_count OUT NOCOPY number
1951 ,x_msg_data OUT NOCOPY varchar2
1952 ,p_lrvv_tbl IN okl_lrvv_tbl) IS
1953 l_api_version CONSTANT number := 1;
1954 l_api_name CONSTANT varchar2(30) := 'v_delete_row';
1955 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1956 i number := 0;
1957 l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
1958
1959 BEGIN
1960 okl_api.init_msg_list(p_init_msg_list);
1961
1962 -- Make sure PL/SQL table has records in it before passing
1963
1964 IF (p_lrvv_tbl.COUNT > 0) THEN
1965 i := p_lrvv_tbl.FIRST;
1966
1967 LOOP
1968 delete_row(p_api_version => p_api_version
1969 ,p_init_msg_list => okl_api.g_false
1970 ,x_return_status => x_return_status
1971 ,x_msg_count => x_msg_count
1972 ,x_msg_data => x_msg_data
1973 ,p_lrvv_rec => p_lrvv_tbl(i));
1974 IF x_return_status <> okl_api.g_ret_sts_success THEN
1975 IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1976 l_overall_status := x_return_status;
1977 END IF;
1978 END IF;
1979 EXIT WHEN(i = p_lrvv_tbl.LAST);
1980 i := p_lrvv_tbl.next(i);
1981 END LOOP;
1982 x_return_status := l_overall_status;
1983 END IF;
1984
1985 EXCEPTION
1986 WHEN g_exception_halt_validation THEN
1987
1988 -- No action necessary. Validation can continue to next attribute/column
1989
1990 NULL;
1991 WHEN okl_api.g_exception_error THEN
1992 x_return_status := okl_api.handle_exceptions(l_api_name
1993 ,g_pkg_name
1994 ,'OKL_API.G_RET_STS_ERROR'
1995 ,x_msg_count
1996 ,x_msg_data
1997 ,'_PVT');
1998 WHEN okl_api.g_exception_unexpected_error THEN
1999 x_return_status := okl_api.handle_exceptions(l_api_name
2000 ,g_pkg_name
2001 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2002 ,x_msg_count
2003 ,x_msg_data
2004 ,'_PVT');
2005 WHEN OTHERS THEN
2006 x_return_status := okl_api.handle_exceptions(l_api_name
2007 ,g_pkg_name
2008 ,'OTHERS'
2009 ,x_msg_count
2010 ,x_msg_data
2011 ,'_PVT');
2012 END delete_row;
2013
2014 END okl_lrv_pvt;