[Home] [Help]
PACKAGE BODY: APPS.OKL_LRT_PVT
Source
1 PACKAGE BODY okl_lrt_pvt AS
2 /* $Header: OKLSLRTB.pls 120.14 2007/08/08 12:47:59 arajagop noship $ */
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE add_language
6 ---------------------------------------------------------------------------
7
8 PROCEDURE add_language IS
9
10 BEGIN
11
12 DELETE FROM okl_ls_rt_fctr_sets_tl t
13 WHERE NOT EXISTS(SELECT NULL
14 FROM OKL_LS_RT_FTR_SETS_ALL_B b
15 WHERE b.id = t.id);
16
17 UPDATE okl_ls_rt_fctr_sets_tl t
18 SET(description) = (SELECT
19 -- LANGUAGE,
20
21 -- B.LANGUAGE,
22
23 b.description
24 FROM okl_ls_rt_fctr_sets_tl b
25 WHERE b.id = t.id
26 AND b.language = t.source_lang)
27 WHERE (t.id, t.language) IN(SELECT subt.id ,subt.language
28 FROM okl_ls_rt_fctr_sets_tl subb ,okl_ls_rt_fctr_sets_tl subt
29 WHERE subb.id = subt.id AND subb.language = subt.language AND ( -- SUBB.LANGUAGE <> SUBT.LANGUAGE OR
30 subb.description <> subt.description OR (subb.language IS NOT NULL
31 AND subt.language IS NULL)
32 OR (subb.description IS NULL AND subt.description IS NOT NULL)));
33
34 INSERT INTO okl_ls_rt_fctr_sets_tl
35 (id
36 ,language
37 ,source_lang
38 ,sfwt_flag
39 ,description)
40 SELECT b.id
41 ,l.language_code
42 ,b.source_lang
43 ,b.sfwt_flag
44 ,b.description
45 FROM okl_ls_rt_fctr_sets_tl b
46 ,fnd_languages l
47 WHERE l.installed_flag IN('I', 'B')
48 AND b.language = userenv('LANG')
49 AND NOT EXISTS(SELECT NULL
50 FROM okl_ls_rt_fctr_sets_tl t
51 WHERE t.id = b.id AND t.language = l.language_code);
52
53 END add_language;
54
55 ----------
56 -- get_rec
57 ----------
58
59 FUNCTION get_rec(p_id IN number
60 ,x_return_status OUT NOCOPY varchar2) RETURN lrtv_rec_type IS
61 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'get_rec';
62 l_lrtv_rec lrtv_rec_type;
63
64 BEGIN
65
66 SELECT id
67 ,object_version_number
68 ,sfwt_flag
69 ,try_id
70 ,pdt_id
71 ,rate
72 ,frq_code
73 ,arrears_yn
74 ,start_date
75 ,end_date
76 ,name
77 ,description
78 ,created_by
79 ,creation_date
80 ,last_updated_by
81 ,last_update_date
82 ,last_update_login
83 ,attribute_category
84 ,attribute1
85 ,attribute2
86 ,attribute3
87 ,attribute4
88 ,attribute5
89 ,attribute6
90 ,attribute7
91 ,attribute8
92 ,attribute9
93 ,attribute10
94 ,attribute11
95 ,attribute12
96 ,attribute13
97 ,attribute14
98 ,attribute15
99 ,sts_code
100 ,org_id
101 ,currency_code
102 ,lrs_type_code
103 ,end_of_term_id
104 ,orig_rate_set_id
105 INTO l_lrtv_rec.id
106 ,l_lrtv_rec.object_version_number
107 ,l_lrtv_rec.sfwt_flag
108 ,l_lrtv_rec.try_id
109 ,l_lrtv_rec.pdt_id
110 ,l_lrtv_rec.rate
111 ,l_lrtv_rec.frq_code
112 ,l_lrtv_rec.arrears_yn
113 ,l_lrtv_rec.start_date
114 ,l_lrtv_rec.end_date
115 ,l_lrtv_rec.name
116 ,l_lrtv_rec.description
117 ,l_lrtv_rec.created_by
118 ,l_lrtv_rec.creation_date
119 ,l_lrtv_rec.last_updated_by
120 ,l_lrtv_rec.last_update_date
121 ,l_lrtv_rec.last_update_login
122 ,l_lrtv_rec.attribute_category
123 ,l_lrtv_rec.attribute1
124 ,l_lrtv_rec.attribute2
125 ,l_lrtv_rec.attribute3
126 ,l_lrtv_rec.attribute4
127 ,l_lrtv_rec.attribute5
128 ,l_lrtv_rec.attribute6
129 ,l_lrtv_rec.attribute7
130 ,l_lrtv_rec.attribute8
131 ,l_lrtv_rec.attribute9
132 ,l_lrtv_rec.attribute10
133 ,l_lrtv_rec.attribute11
134 ,l_lrtv_rec.attribute12
135 ,l_lrtv_rec.attribute13
136 ,l_lrtv_rec.attribute14
137 ,l_lrtv_rec.attribute15
138 ,l_lrtv_rec.sts_code
139 ,l_lrtv_rec.org_id
140 ,l_lrtv_rec.currency_code
141 ,l_lrtv_rec.lrs_type_code
142 ,l_lrtv_rec.end_of_term_id
143 ,l_lrtv_rec.orig_rate_set_id
144 FROM okl_ls_rt_fctr_sets_v lrtv
145 WHERE lrtv.id = p_id;
146 x_return_status := g_ret_sts_success;
147 RETURN l_lrtv_rec;
148 EXCEPTION
149 WHEN okl_api.g_exception_error THEN
150 x_return_status := g_ret_sts_error;
151 WHEN okl_api.g_exception_unexpected_error THEN
152 x_return_status := g_ret_sts_unexp_error;
153 WHEN OTHERS THEN
154 okl_api.set_message(p_app_name => g_app_name
155 ,p_msg_name => g_db_error
156 ,p_token1 => g_prog_name_token
157 ,p_token1_value => l_api_name
158 ,p_token2 => g_sqlcode_token
159 ,p_token2_value => sqlcode
160 ,p_token3 => g_sqlerrm_token
161 ,p_token3_value => sqlerrm);
162 x_return_status := g_ret_sts_unexp_error;
163 END get_rec;
164
165 --------------------
166 -- null_out_defaults
167 --------------------
168
169 FUNCTION null_out_defaults(p_lrtv_rec IN lrtv_rec_type) RETURN lrtv_rec_type IS
170 l_lrtv_rec lrtv_rec_type := p_lrtv_rec;
171
172 BEGIN
173
174 IF (l_lrtv_rec.id = g_miss_num) THEN
175 l_lrtv_rec.id := NULL;
176 END IF;
177
178 IF (l_lrtv_rec.object_version_number = g_miss_num) THEN
179 l_lrtv_rec.object_version_number := NULL;
180 END IF;
181
182 IF (l_lrtv_rec.sfwt_flag = g_miss_char) THEN
183 l_lrtv_rec.sfwt_flag := NULL;
184 END IF;
185
186 IF (l_lrtv_rec.try_id = g_miss_num) THEN
187 l_lrtv_rec.try_id := NULL;
188 END IF;
189
190 IF (l_lrtv_rec.pdt_id = g_miss_num) THEN
191 l_lrtv_rec.pdt_id := NULL;
192 END IF;
193
194 IF (l_lrtv_rec.rate = g_miss_num) THEN
195 l_lrtv_rec.rate := NULL;
196 END IF;
197
198 IF (l_lrtv_rec.frq_code = g_miss_char) THEN
199 l_lrtv_rec.frq_code := NULL;
200 END IF;
201
202 IF (l_lrtv_rec.arrears_yn = g_miss_char) THEN
203 l_lrtv_rec.arrears_yn := NULL;
204 END IF;
205
206 IF (l_lrtv_rec.start_date = g_miss_date) THEN
207 l_lrtv_rec.start_date := NULL;
208 END IF;
209
210 IF (l_lrtv_rec.end_date = g_miss_date) THEN
211 l_lrtv_rec.end_date := NULL;
212 END IF;
213
214 IF (l_lrtv_rec.name = g_miss_char) THEN
215 l_lrtv_rec.name := NULL;
216 END IF;
217
218 IF (l_lrtv_rec.description = g_miss_char) THEN
219 l_lrtv_rec.description := NULL;
220 END IF;
221
222 IF (l_lrtv_rec.created_by = g_miss_num) THEN
223 l_lrtv_rec.created_by := NULL;
224 END IF;
225
226 IF (l_lrtv_rec.creation_date = g_miss_date) THEN
227 l_lrtv_rec.creation_date := NULL;
228 END IF;
229
230 IF (l_lrtv_rec.last_updated_by = g_miss_num) THEN
231 l_lrtv_rec.last_updated_by := NULL;
232 END IF;
233
234 IF (l_lrtv_rec.last_update_date = g_miss_date) THEN
235 l_lrtv_rec.last_update_date := NULL;
236 END IF;
237
238 IF (l_lrtv_rec.last_update_login = g_miss_num) THEN
239 l_lrtv_rec.last_update_login := NULL;
240 END IF;
241
242 IF (l_lrtv_rec.attribute_category = g_miss_char) THEN
243 l_lrtv_rec.attribute_category := NULL;
244 END IF;
245
246 IF (l_lrtv_rec.attribute1 = g_miss_char) THEN
247 l_lrtv_rec.attribute1 := NULL;
248 END IF;
249
250 IF (l_lrtv_rec.attribute2 = g_miss_char) THEN
251 l_lrtv_rec.attribute2 := NULL;
252 END IF;
253
254 IF (l_lrtv_rec.attribute3 = g_miss_char) THEN
255 l_lrtv_rec.attribute3 := NULL;
256 END IF;
257
258 IF (l_lrtv_rec.attribute4 = g_miss_char) THEN
259 l_lrtv_rec.attribute4 := NULL;
260 END IF;
261
262 IF (l_lrtv_rec.attribute5 = g_miss_char) THEN
263 l_lrtv_rec.attribute5 := NULL;
264 END IF;
265
266 IF (l_lrtv_rec.attribute6 = g_miss_char) THEN
267 l_lrtv_rec.attribute6 := NULL;
268 END IF;
269
270 IF (l_lrtv_rec.attribute7 = g_miss_char) THEN
271 l_lrtv_rec.attribute7 := NULL;
272 END IF;
273
274 IF (l_lrtv_rec.attribute8 = g_miss_char) THEN
275 l_lrtv_rec.attribute8 := NULL;
276 END IF;
277
278 IF (l_lrtv_rec.attribute9 = g_miss_char) THEN
279 l_lrtv_rec.attribute9 := NULL;
280 END IF;
281
282 IF (l_lrtv_rec.attribute10 = g_miss_char) THEN
283 l_lrtv_rec.attribute10 := NULL;
284 END IF;
285
286 IF (l_lrtv_rec.attribute11 = g_miss_char) THEN
287 l_lrtv_rec.attribute11 := NULL;
288 END IF;
289
290 IF (l_lrtv_rec.attribute12 = g_miss_char) THEN
291 l_lrtv_rec.attribute12 := NULL;
292 END IF;
293
294 IF (l_lrtv_rec.attribute13 = g_miss_char) THEN
295 l_lrtv_rec.attribute13 := NULL;
296 END IF;
297
298 IF (l_lrtv_rec.attribute14 = g_miss_char) THEN
299 l_lrtv_rec.attribute14 := NULL;
300 END IF;
301
302 IF (l_lrtv_rec.attribute15 = g_miss_char) THEN
303 l_lrtv_rec.attribute15 := NULL;
304 END IF;
305
306 IF (l_lrtv_rec.sts_code = g_miss_char) THEN
307 l_lrtv_rec.sts_code := NULL;
308 END IF;
309
310 IF (l_lrtv_rec.org_id = g_miss_num) THEN
311 l_lrtv_rec.org_id := NULL;
312 END IF;
313
314 IF (l_lrtv_rec.currency_code = g_miss_char) THEN
315 l_lrtv_rec.currency_code := NULL;
316 END IF;
317
318 IF (l_lrtv_rec.lrs_type_code = g_miss_char) THEN
319 l_lrtv_rec.lrs_type_code := NULL;
320 END IF;
321
322 IF (l_lrtv_rec.end_of_term_id = g_miss_num) THEN
323 l_lrtv_rec.end_of_term_id := NULL;
324 END IF;
325
326 IF (l_lrtv_rec.orig_rate_set_id = g_miss_num) THEN
327 l_lrtv_rec.orig_rate_set_id := NULL;
328 END IF;
329
330 RETURN(l_lrtv_rec);
331 END null_out_defaults;
332
333 ---------------------------------
334 -- PROCEDURE validate_id
335 ---------------------------------
336
337 PROCEDURE validate_id(x_return_status OUT NOCOPY varchar2
338 ,p_id IN number) IS
339 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_id';
340
341 BEGIN
342
343 IF p_id IS NULL THEN
344 okl_api.set_message(p_app_name => g_app_name
345 ,p_msg_name => g_required_value
346 ,p_token1 => g_col_name_token
347 ,p_token1_value => 'id');
348 RAISE okl_api.g_exception_error;
349 END IF;
350 x_return_status := g_ret_sts_success;
351 EXCEPTION
352 WHEN okl_api.g_exception_error THEN
353 x_return_status := g_ret_sts_error;
354 WHEN okl_api.g_exception_unexpected_error THEN
355 x_return_status := g_ret_sts_unexp_error;
356 WHEN OTHERS THEN
357 okl_api.set_message(p_app_name => g_app_name
358 ,p_msg_name => g_db_error
359 ,p_token1 => g_prog_name_token
360 ,p_token1_value => l_api_name
361 ,p_token2 => g_sqlcode_token
362 ,p_token2_value => sqlcode
363 ,p_token3 => g_sqlerrm_token
364 ,p_token3_value => sqlerrm);
365 x_return_status := g_ret_sts_unexp_error;
366 END validate_id;
367
368 PROCEDURE validate_object_version_number(x_return_status OUT NOCOPY varchar2
369 ,p_object_version_number IN number) IS
370 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_object_version_number';
371
372 BEGIN
373
374 IF (p_object_version_number IS NULL) OR (p_object_version_number = g_miss_num) THEN
375 okl_api.set_message(p_app_name => g_app_name
376 ,p_msg_name => g_required_value
377 ,p_token1 => g_col_name_token
381 x_return_status := g_ret_sts_success;
378 ,p_token1_value => 'object_version_number');
379 RAISE okl_api.g_exception_error;
380 END IF;
382 EXCEPTION
383 WHEN okl_api.g_exception_error THEN
384 x_return_status := g_ret_sts_error;
385 WHEN okl_api.g_exception_unexpected_error THEN
386 x_return_status := g_ret_sts_unexp_error;
387 WHEN OTHERS THEN
388 okl_api.set_message(p_app_name => g_app_name
389 ,p_msg_name => g_db_error
390 ,p_token1 => g_prog_name_token
391 ,p_token1_value => l_api_name
392 ,p_token2 => g_sqlcode_token
393 ,p_token2_value => sqlcode
394 ,p_token3 => g_sqlerrm_token
395 ,p_token3_value => sqlerrm);
396 x_return_status := g_ret_sts_unexp_error;
397 END validate_object_version_number;
398
399 ---------------------------------
400 -- PROCEDURE validate_rate
401 ---------------------------------
402
403 PROCEDURE validate_rate(x_return_status OUT NOCOPY varchar2
404 ,p_rate IN number) IS
405 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_rate';
406
407 BEGIN
408
409 IF p_rate IS NULL THEN
410 okl_api.set_message(p_app_name => g_app_name
411 ,p_msg_name => g_required_value
412 ,p_token1 => g_col_name_token
413 ,p_token1_value => 'rate');
414 RAISE okl_api.g_exception_error;
415 END IF;
416 x_return_status := g_ret_sts_success;
417 EXCEPTION
418 WHEN okl_api.g_exception_error THEN
419 x_return_status := g_ret_sts_error;
420 WHEN okl_api.g_exception_unexpected_error THEN
421 x_return_status := g_ret_sts_unexp_error;
422 WHEN OTHERS THEN
423 okl_api.set_message(p_app_name => g_app_name
424 ,p_msg_name => g_db_error
425 ,p_token1 => g_prog_name_token
426 ,p_token1_value => l_api_name
427 ,p_token2 => g_sqlcode_token
428 ,p_token2_value => sqlcode
429 ,p_token3 => g_sqlerrm_token
430 ,p_token3_value => sqlerrm);
431 x_return_status := g_ret_sts_unexp_error;
432 END validate_rate;
433
434 ---------------------------------
435 -- PROCEDURE validate_arrears_yn
436 ---------------------------------
437
438 PROCEDURE validate_arrears_yn(x_return_status OUT NOCOPY varchar2
439 ,p_arrears_yn IN varchar2) IS
440 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_arrears_yn';
441
442 CURSOR c_yes_no IS
443 SELECT 'x'
444 FROM fnd_lookups
445 WHERE lookup_type = 'OKL_YES_NO' AND lookup_code = p_arrears_yn;
446 l_dummy varchar2(1);
447
448 BEGIN
449
450 IF p_arrears_yn IS NULL THEN
451 okl_api.set_message(p_app_name => g_app_name
452 ,p_msg_name => g_required_value
453 ,p_token1 => g_col_name_token
454 ,p_token1_value => 'arrears_yn');
455 RAISE okl_api.g_exception_error;
456 END IF;
457 OPEN c_yes_no;
458 FETCH c_yes_no INTO l_dummy ;
459 CLOSE c_yes_no;
460
461 IF l_dummy IS NULL THEN
462 okl_api.set_message(p_app_name => g_app_name
463 ,p_msg_name => g_invalid_value
464 ,p_token1 => g_col_name_token
465 ,p_token1_value => 'arrears_yn');
466 RAISE okl_api.g_exception_error;
467 END IF;
468 x_return_status := g_ret_sts_success;
469 EXCEPTION
470 WHEN okl_api.g_exception_error THEN
471 x_return_status := g_ret_sts_error;
472 WHEN okl_api.g_exception_unexpected_error THEN
473 x_return_status := g_ret_sts_unexp_error;
474 WHEN OTHERS THEN
475 okl_api.set_message(p_app_name => g_app_name
476 ,p_msg_name => g_db_error
477 ,p_token1 => g_prog_name_token
478 ,p_token1_value => l_api_name
479 ,p_token2 => g_sqlcode_token
480 ,p_token2_value => sqlcode
481 ,p_token3 => g_sqlerrm_token
482 ,p_token3_value => sqlerrm);
483 x_return_status := g_ret_sts_unexp_error;
484 END validate_arrears_yn;
485
486 ---------------------------------
487 -- PROCEDURE validate_frq_code
488 ---------------------------------
489
490 PROCEDURE validate_frq_code(x_return_status OUT NOCOPY varchar2
491 ,p_frq_code IN varchar2) IS
492 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_frq_code';
493
494 CURSOR c_frq_code IS
495 SELECT 'x'
496 FROM fnd_lookups
500 BEGIN
497 WHERE lookup_type = 'OKL_FREQUENCY' AND lookup_code = p_frq_code;
498 l_dummy varchar2(1);
499
501
502 IF p_frq_code IS NULL THEN
503 okl_api.set_message(p_app_name => g_app_name
504 ,p_msg_name => g_required_value
505 ,p_token1 => g_col_name_token
506 ,p_token1_value => 'frq_code');
507 RAISE okl_api.g_exception_error;
508 END IF;
509 OPEN c_frq_code;
510 FETCH c_frq_code INTO l_dummy ;
511 CLOSE c_frq_code;
512
513 IF l_dummy IS NULL THEN
514 okl_api.set_message(p_app_name => g_app_name
515 ,p_msg_name => g_invalid_value
516 ,p_token1 => g_col_name_token
517 ,p_token1_value => 'frq_code');
518 RAISE okl_api.g_exception_error;
519 END IF;
520 x_return_status := g_ret_sts_success;
521 EXCEPTION
522 WHEN okl_api.g_exception_error THEN
523 x_return_status := g_ret_sts_error;
524 WHEN okl_api.g_exception_unexpected_error THEN
525 x_return_status := g_ret_sts_unexp_error;
526 WHEN OTHERS THEN
527 okl_api.set_message(p_app_name => g_app_name
528 ,p_msg_name => g_db_error
529 ,p_token1 => g_prog_name_token
530 ,p_token1_value => l_api_name
531 ,p_token2 => g_sqlcode_token
532 ,p_token2_value => sqlcode
533 ,p_token3 => g_sqlerrm_token
534 ,p_token3_value => sqlerrm);
535 x_return_status := g_ret_sts_unexp_error;
536 END validate_frq_code;
537
538 ---------------------------------
539 -- PROCEDURE validate_name
540 ---------------------------------
541
542 PROCEDURE validate_name(x_return_status OUT NOCOPY varchar2
543 ,p_name IN varchar2) IS
544 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_name';
545
546 BEGIN
547
548 IF p_name IS NULL THEN
549 okl_api.set_message(p_app_name => g_app_name
550 ,p_msg_name => g_required_value
551 ,p_token1 => g_col_name_token
552 ,p_token1_value => 'name');
553 RAISE okl_api.g_exception_error;
554 END IF;
555 x_return_status := g_ret_sts_success;
556 EXCEPTION
557 WHEN okl_api.g_exception_error THEN
558 x_return_status := g_ret_sts_error;
559 WHEN okl_api.g_exception_unexpected_error THEN
560 x_return_status := g_ret_sts_unexp_error;
561 WHEN OTHERS THEN
562 okl_api.set_message(p_app_name => g_app_name
563 ,p_msg_name => g_db_error
564 ,p_token1 => g_prog_name_token
565 ,p_token1_value => l_api_name
566 ,p_token2 => g_sqlcode_token
567 ,p_token2_value => sqlcode
568 ,p_token3 => g_sqlerrm_token
569 ,p_token3_value => sqlerrm);
570 x_return_status := g_ret_sts_unexp_error;
571 END validate_name;
572
573 -------------------------------------
574 -- PROCEDURE validate_lrs_type_code
575 -------------------------------------
576
577 PROCEDURE validate_lrs_type_code(x_return_status OUT NOCOPY varchar2
578 ,p_type_code IN varchar2) IS
579 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_lrs_type_code';
580
581 CURSOR c_type_code IS
582 SELECT 'x'
583 FROM fnd_lookups
584 WHERE lookup_type = 'OKL_LRS_TYPES' AND lookup_code = p_type_code;
585 l_dummy varchar2(1) := '?';
586
587 BEGIN
588
589 IF p_type_code IS NULL THEN
590 okl_api.set_message(p_app_name => g_app_name
591 ,p_msg_name => g_required_value
592 ,p_token1 => g_col_name_token
593 ,p_token1_value => 'LRS_TYPE_CODE');
594 RAISE okl_api.g_exception_error;
595 END IF;
596 OPEN c_type_code;
597 FETCH c_type_code INTO l_dummy ;
598 CLOSE c_type_code;
599
600 IF l_dummy = '?' THEN
601 okl_api.set_message(p_app_name => g_app_name
602 ,p_msg_name => g_invalid_value
603 ,p_token1 => g_col_name_token
604 ,p_token1_value => 'LRS_TYPE_CODE');
605 RAISE okl_api.g_exception_error;
606 END IF;
607 x_return_status := g_ret_sts_success;
608 EXCEPTION
609 WHEN okl_api.g_exception_error THEN
610 x_return_status := g_ret_sts_error;
611 WHEN okl_api.g_exception_unexpected_error THEN
612 x_return_status := g_ret_sts_unexp_error;
613 WHEN OTHERS THEN
614 okl_api.set_message(p_app_name => g_app_name
615 ,p_msg_name => g_db_error
616 ,p_token1 => g_prog_name_token
617 ,p_token1_value => l_api_name
621 ,p_token3_value => sqlerrm);
618 ,p_token2 => g_sqlcode_token
619 ,p_token2_value => sqlcode
620 ,p_token3 => g_sqlerrm_token
622 x_return_status := g_ret_sts_unexp_error;
623 END validate_lrs_type_code;
624
625 --------------------------------------
626 -- PROCEDURE validate_STS_CODE
627 --------------------------------------
628
629 PROCEDURE validate_sts_code(x_return_status OUT NOCOPY varchar2
630 ,p_sts_code IN varchar2) IS
631 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_STS_CODE';
632
633 CURSOR c_sts_code IS
634 SELECT 'x'
635 FROM fnd_lookups
636 WHERE lookup_type = 'OKL_PRC_STATUS' AND lookup_code = p_sts_code;
637 l_dummy varchar2(1);
638
639 BEGIN
640
641 IF p_sts_code IS NULL THEN
642 okl_api.set_message(p_app_name => g_app_name
643 ,p_msg_name => g_required_value
644 ,p_token1 => g_col_name_token
645 ,p_token1_value => 'STS_CODE');
646 RAISE okl_api.g_exception_error;
647 END IF;
648 OPEN c_sts_code;
649 FETCH c_sts_code INTO l_dummy ;
650 CLOSE c_sts_code;
651
652 IF l_dummy IS NULL THEN
653 okl_api.set_message(p_app_name => g_app_name
654 ,p_msg_name => g_invalid_value
655 ,p_token1 => g_col_name_token
656 ,p_token1_value => 'STS_CODE');
657 RAISE okl_api.g_exception_error;
658 END IF;
659 x_return_status := g_ret_sts_success;
660 EXCEPTION
661 WHEN okl_api.g_exception_error THEN
662 x_return_status := g_ret_sts_error;
663 WHEN okl_api.g_exception_unexpected_error THEN
664 x_return_status := g_ret_sts_unexp_error;
665 WHEN OTHERS THEN
666 okl_api.set_message(p_app_name => g_app_name
667 ,p_msg_name => g_db_error
668 ,p_token1 => g_prog_name_token
669 ,p_token1_value => l_api_name
670 ,p_token2 => g_sqlcode_token
671 ,p_token2_value => sqlcode
672 ,p_token3 => g_sqlerrm_token
673 ,p_token3_value => sqlerrm);
674 x_return_status := g_ret_sts_unexp_error;
675 END validate_sts_code;
676
677 ---------------------------------
678 -- PROCEDURE validate_org_id
679 ---------------------------------
680
681 PROCEDURE validate_org_id(x_return_status OUT NOCOPY varchar2
682 ,p_org_id IN number) IS
683 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_org_id';
684 l_return_status varchar2(3);
685
686 BEGIN
687 l_return_status := okl_util.check_org_id(p_org_id, 'N');
688
689 IF (l_return_status = okl_api.g_ret_sts_error) THEN
690 okl_api.set_message(p_app_name => g_app_name
691 ,p_msg_name => g_invalid_value
692 ,p_token1 => g_col_name_token
693 ,p_token1_value => 'org_id');
694 RAISE okl_api.g_exception_error;
695 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
696
697 RAISE okl_api.g_exception_unexpected_error;
698 END IF;
699 x_return_status := g_ret_sts_success;
700 EXCEPTION
701 WHEN okl_api.g_exception_error THEN
702 x_return_status := g_ret_sts_error;
703 WHEN okl_api.g_exception_unexpected_error THEN
704 x_return_status := g_ret_sts_unexp_error;
705 WHEN OTHERS THEN
706 okl_api.set_message(p_app_name => g_app_name
707 ,p_msg_name => g_db_error
708 ,p_token1 => g_prog_name_token
709 ,p_token1_value => l_api_name
710 ,p_token2 => g_sqlcode_token
711 ,p_token2_value => sqlcode
712 ,p_token3 => g_sqlerrm_token
713 ,p_token3_value => sqlerrm);
714 x_return_status := g_ret_sts_unexp_error;
715 END validate_org_id;
716
717 ---------------------------------
718 -- PROCEDURE validate_END_OF_TERM_ID
719 ---------------------------------
720
721 PROCEDURE validate_end_of_term_id(x_return_status OUT NOCOPY varchar2
722 ,p_end_of_term_id IN number) IS
723 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_END_OF_TERM_ID';
724
725 CURSOR c_end_of_term_id IS
726 SELECT 'X'
727 FROM okl_fe_eo_terms_v
728 WHERE end_of_term_id = p_end_of_term_id;
729 l_dummy varchar2(1) := '?';
730
731 BEGIN
732
733 IF p_end_of_term_id IS NULL THEN
734 okl_api.set_message(p_app_name => g_app_name
735 ,p_msg_name => g_required_value
736 ,p_token1 => g_col_name_token
737 ,p_token1_value => 'END_OF_TERM_ID');
738 RAISE okl_api.g_exception_error;
739 END IF;
743
740 OPEN c_end_of_term_id;
741 FETCH c_end_of_term_id INTO l_dummy ;
742 CLOSE c_end_of_term_id;
744 IF l_dummy = '?' THEN
745 okl_api.set_message(p_app_name => okl_api.g_app_name
746 ,p_msg_name => 'OKC_NO_PARENT_RECORD'
747 ,p_token1 => okl_api.g_col_name_token
748 ,p_token1_value => 'END_OF_TERM_ID'
749 ,p_token2 => okl_api.g_child_table_token
750 ,p_token2_value => 'OKL_LS_RT_FCTR_SETS_B'
751 ,p_token3 => okl_api.g_parent_table_token
752 ,p_token3_value => 'OKL_FE_EO_TERMS_V');
753 RAISE okl_api.g_exception_error;
754 END IF;
755 x_return_status := g_ret_sts_success;
756 EXCEPTION
757 WHEN okl_api.g_exception_error THEN
758 x_return_status := g_ret_sts_error;
759 WHEN okl_api.g_exception_unexpected_error THEN
760 x_return_status := g_ret_sts_unexp_error;
761 WHEN OTHERS THEN
762 okl_api.set_message(p_app_name => g_app_name
763 ,p_msg_name => g_db_error
764 ,p_token1 => g_prog_name_token
765 ,p_token1_value => l_api_name
766 ,p_token2 => g_sqlcode_token
767 ,p_token2_value => sqlcode
768 ,p_token3 => g_sqlerrm_token
769 ,p_token3_value => sqlerrm);
770 x_return_status := g_ret_sts_unexp_error;
771 END validate_end_of_term_id;
772
773 ---------------------------------
774 -- PROCEDURE validate_currency_code
775 ---------------------------------
776
777 PROCEDURE validate_currency_code(x_return_status OUT NOCOPY varchar2
778 ,p_currency_code IN varchar2) IS
779 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_currency_code';
780
781 BEGIN
782
783 IF p_currency_code IS NULL THEN
784 okl_api.set_message(p_app_name => g_app_name
785 ,p_msg_name => g_required_value
786 ,p_token1 => g_col_name_token
787 ,p_token1_value => 'currency_code');
788 RAISE okl_api.g_exception_error;
789 END IF;
790 x_return_status := g_ret_sts_success;
791 EXCEPTION
792 WHEN okl_api.g_exception_error THEN
793 x_return_status := g_ret_sts_error;
794 WHEN okl_api.g_exception_unexpected_error THEN
795 x_return_status := g_ret_sts_unexp_error;
796 WHEN OTHERS THEN
797 okl_api.set_message(p_app_name => g_app_name
798 ,p_msg_name => g_db_error
799 ,p_token1 => g_prog_name_token
800 ,p_token1_value => l_api_name
801 ,p_token2 => g_sqlcode_token
802 ,p_token2_value => sqlcode
803 ,p_token3 => g_sqlerrm_token
804 ,p_token3_value => sqlerrm);
805 x_return_status := g_ret_sts_unexp_error;
806 END validate_currency_code;
807
808 --------------------------------
809 -- PROCEDURE validate_attributes
810 --------------------------------
811
812 FUNCTION validate_attributes(p_lrtv_rec IN lrtv_rec_type) RETURN varchar2 IS
813 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_attributes';
814 x_return_status varchar2(1);
815
816 BEGIN
817
818 --
819
820 validate_id(x_return_status, p_lrtv_rec.id);
821
822 IF x_return_status = g_ret_sts_error THEN
823 RAISE okl_api.g_exception_error;
824 ELSIF x_return_status = g_ret_sts_unexp_error THEN
825 RAISE okl_api.g_exception_unexpected_error;
826 END IF;
827
828 --
829
830 validate_object_version_number(x_return_status
831 ,p_lrtv_rec.object_version_number);
832
833 IF x_return_status = g_ret_sts_error THEN
834 RAISE okl_api.g_exception_error;
835 ELSIF x_return_status = g_ret_sts_unexp_error THEN
836 RAISE okl_api.g_exception_unexpected_error;
837 END IF;
838
839 validate_frq_code(x_return_status, p_lrtv_rec.frq_code);
840
841 IF x_return_status = g_ret_sts_error THEN
842 RAISE okl_api.g_exception_error;
843 ELSIF x_return_status = g_ret_sts_unexp_error THEN
844 RAISE okl_api.g_exception_unexpected_error;
845 END IF;
846
847 validate_name(x_return_status, p_lrtv_rec.name);
848
849 IF x_return_status = g_ret_sts_error THEN
850 RAISE okl_api.g_exception_error;
851 ELSIF x_return_status = g_ret_sts_unexp_error THEN
852 RAISE okl_api.g_exception_unexpected_error;
853 END IF;
854
855 validate_sts_code(x_return_status, p_lrtv_rec.sts_code);
856
857 IF x_return_status = g_ret_sts_error THEN
858 RAISE okl_api.g_exception_error;
859 ELSIF x_return_status = g_ret_sts_unexp_error THEN
860 RAISE okl_api.g_exception_unexpected_error;
861 END IF;
862 validate_lrs_type_code(x_return_status, p_lrtv_rec.lrs_type_code);
863
864 IF x_return_status = g_ret_sts_error THEN
868 END IF;
865 RAISE okl_api.g_exception_error;
866 ELSIF x_return_status = g_ret_sts_unexp_error THEN
867 RAISE okl_api.g_exception_unexpected_error;
869
870 validate_org_id(x_return_status, p_lrtv_rec.org_id);
871
872 IF x_return_status = g_ret_sts_error THEN
873 RAISE okl_api.g_exception_error;
874 ELSIF x_return_status = g_ret_sts_unexp_error THEN
875 RAISE okl_api.g_exception_unexpected_error;
876 END IF;
877 validate_end_of_term_id(x_return_status, p_lrtv_rec.end_of_term_id);
878
879 IF x_return_status = g_ret_sts_error THEN
880 RAISE okl_api.g_exception_error;
881 ELSIF x_return_status = g_ret_sts_unexp_error THEN
882 RAISE okl_api.g_exception_unexpected_error;
883 END IF;
884
885 validate_currency_code(x_return_status, p_lrtv_rec.currency_code);
886
887 IF x_return_status = g_ret_sts_error THEN
888 RAISE okl_api.g_exception_error;
889 ELSIF x_return_status = g_ret_sts_unexp_error THEN
890 RAISE okl_api.g_exception_unexpected_error;
891 END IF;
892
893 RETURN g_ret_sts_success;
894 EXCEPTION
895 WHEN okl_api.g_exception_error THEN
896 RETURN g_ret_sts_error;
897 WHEN okl_api.g_exception_unexpected_error THEN
898 RETURN g_ret_sts_unexp_error;
899 WHEN OTHERS THEN
900 okl_api.set_message(p_app_name => g_app_name
901 ,p_msg_name => g_db_error
902 ,p_token1 => g_prog_name_token
903 ,p_token1_value => l_api_name
904 ,p_token2 => g_sqlcode_token
905 ,p_token2_value => sqlcode
906 ,p_token3 => g_sqlerrm_token
907 ,p_token3_value => sqlerrm);
908 RETURN g_ret_sts_unexp_error;
909 END validate_attributes;
910
911 ------------------
912 -- validate_record
913 ------------------
914
915 FUNCTION validate_record(p_lrtv_rec IN lrtv_rec_type) RETURN varchar2 IS
916 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_record';
917
918 BEGIN
919 RETURN g_ret_sts_success;
920 EXCEPTION
921 WHEN okl_api.g_exception_error THEN
922 RETURN g_ret_sts_error;
923 WHEN okl_api.g_exception_unexpected_error THEN
924 RETURN g_ret_sts_unexp_error;
925 WHEN OTHERS THEN
926 okl_api.set_message(p_app_name => g_app_name
927 ,p_msg_name => g_db_error
928 ,p_token1 => g_prog_name_token
929 ,p_token1_value => l_api_name
930 ,p_token2 => g_sqlcode_token
931 ,p_token2_value => sqlcode
932 ,p_token3 => g_sqlerrm_token
933 ,p_token3_value => sqlerrm);
934 RETURN g_ret_sts_unexp_error;
935 END validate_record;
936
937 ----------------
938 -- migrate (V-B)
939 ----------------
940
941 PROCEDURE migrate(p_from IN lrtv_rec_type
942 ,p_to IN OUT NOCOPY lrt_rec_type) IS
943
944 BEGIN
945 p_to.id := p_from.id;
946 p_to.object_version_number := p_from.object_version_number;
947 p_to.name := p_from.name;
948 p_to.arrears_yn := p_from.arrears_yn;
949 p_to.start_date := p_from.start_date;
950 p_to.end_date := p_from.end_date;
951 p_to.pdt_id := p_from.pdt_id;
952 p_to.rate := p_from.rate;
953 p_to.try_id := p_from.try_id;
954 p_to.frq_code := p_from.frq_code;
955 p_to.created_by := p_from.created_by;
956 p_to.creation_date := p_from.creation_date;
957 p_to.last_updated_by := p_from.last_updated_by;
958 p_to.last_update_date := p_from.last_update_date;
959 p_to.last_update_login := p_from.last_update_login;
960 p_to.attribute_category := p_from.attribute_category;
961 p_to.attribute1 := p_from.attribute1;
962 p_to.attribute2 := p_from.attribute2;
963 p_to.attribute3 := p_from.attribute3;
964 p_to.attribute4 := p_from.attribute4;
965 p_to.attribute5 := p_from.attribute5;
966 p_to.attribute6 := p_from.attribute6;
967 p_to.attribute7 := p_from.attribute7;
968 p_to.attribute8 := p_from.attribute8;
969 p_to.attribute9 := p_from.attribute9;
970 p_to.attribute10 := p_from.attribute10;
971 p_to.attribute11 := p_from.attribute11;
972 p_to.attribute12 := p_from.attribute12;
973 p_to.attribute13 := p_from.attribute13;
974 p_to.attribute14 := p_from.attribute14;
975 p_to.attribute15 := p_from.attribute15;
976 p_to.sts_code := p_from.sts_code;
977 p_to.org_id := p_from.org_id;
978 p_to.currency_code := p_from.currency_code;
979 p_to.lrs_type_code := p_from.lrs_type_code;
980 p_to.end_of_term_id := p_from.end_of_term_id;
981 p_to.orig_rate_set_id := p_from.orig_rate_set_id;
982
983 END migrate;
984
985 ----------------
986 -- migrate (V-TL)
987 ----------------
988
989 PROCEDURE migrate(p_from IN lrtv_rec_type
990 ,p_to IN OUT NOCOPY lrttl_rec_type) IS
991
992 BEGIN
993 p_to.id := p_from.id;
994 p_to.description := p_from.description;
998 p_to.last_update_date := p_from.last_update_date;
995 p_to.created_by := p_from.created_by;
996 p_to.creation_date := p_from.creation_date;
997 p_to.last_updated_by := p_from.last_updated_by;
999 p_to.last_update_login := p_from.last_update_login;
1000 END migrate;
1001
1002 ---------------------
1003 -- validate_row (REC)
1004 ---------------------
1005
1006 PROCEDURE validate_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_lrtv_rec IN lrtv_rec_type) IS
1012 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_row (REC)';
1013 l_return_status varchar2(1);
1014
1015 BEGIN
1016 l_return_status := validate_attributes(p_lrtv_rec);
1017
1018 IF (l_return_status = g_ret_sts_unexp_error) THEN
1019 RAISE okl_api.g_exception_unexpected_error;
1020 ELSIF (l_return_status = g_ret_sts_error) THEN
1021 RAISE okl_api.g_exception_error;
1022 END IF;
1023 l_return_status := validate_record(p_lrtv_rec);
1024
1025 IF (l_return_status = g_ret_sts_unexp_error) THEN
1026 RAISE okl_api.g_exception_unexpected_error;
1027 ELSIF (l_return_status = g_ret_sts_error) THEN
1028 RAISE okl_api.g_exception_error;
1029 END IF;
1030
1031 EXCEPTION
1032 WHEN okl_api.g_exception_error THEN
1033 x_return_status := g_ret_sts_error;
1034 WHEN okl_api.g_exception_unexpected_error THEN
1035 x_return_status := g_ret_sts_unexp_error;
1036 WHEN OTHERS THEN
1037 okl_api.set_message(p_app_name => g_app_name
1038 ,p_msg_name => g_db_error
1039 ,p_token1 => g_prog_name_token
1040 ,p_token1_value => l_api_name
1041 ,p_token2 => g_sqlcode_token
1042 ,p_token2_value => sqlcode
1043 ,p_token3 => g_sqlerrm_token
1044 ,p_token3_value => sqlerrm);
1045 x_return_status := g_ret_sts_unexp_error;
1046 END validate_row;
1047
1048 ---------------------
1049 -- validate_row (TBL)
1050 ---------------------
1051
1052 PROCEDURE validate_row(p_api_version IN number
1053 ,p_init_msg_list IN varchar2 DEFAULT okc_api.g_false
1054 ,x_return_status OUT NOCOPY varchar2
1055 ,x_msg_count OUT NOCOPY number
1056 ,x_msg_data OUT NOCOPY varchar2
1057 ,p_lrtv_tbl IN lrtv_tbl_type) IS
1058 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_row (TBL)';
1059 l_return_status varchar2(1) := g_ret_sts_success;
1060 i binary_integer;
1061
1062 BEGIN
1063
1064 IF (p_lrtv_tbl.COUNT > 0) THEN
1065 i := p_lrtv_tbl.FIRST;
1066
1067 LOOP
1068 IF p_lrtv_tbl.EXISTS(i) THEN
1069 validate_row(p_api_version => g_api_version
1070 ,p_init_msg_list => g_false
1071 ,x_return_status => l_return_status
1072 ,x_msg_count => x_msg_count
1073 ,x_msg_data => x_msg_data
1074 ,p_lrtv_rec => p_lrtv_tbl(i));
1075 IF l_return_status = g_ret_sts_error THEN
1076 RAISE okl_api.g_exception_error;
1077 ELSIF l_return_status = g_ret_sts_unexp_error THEN
1078 RAISE okl_api.g_exception_unexpected_error;
1079 END IF;
1080 EXIT WHEN i = p_lrtv_tbl.LAST;
1081 i := p_lrtv_tbl.next(i);
1082 END IF;
1083 END LOOP;
1084
1085 END IF;
1086 x_return_status := l_return_status;
1087 EXCEPTION
1088 WHEN okl_api.g_exception_error THEN
1089 x_return_status := g_ret_sts_error;
1090 WHEN okl_api.g_exception_unexpected_error THEN
1091 x_return_status := g_ret_sts_unexp_error;
1092 WHEN OTHERS THEN
1093 okl_api.set_message(p_app_name => g_app_name
1094 ,p_msg_name => g_db_error
1095 ,p_token1 => g_prog_name_token
1096 ,p_token1_value => l_api_name
1097 ,p_token2 => g_sqlcode_token
1098 ,p_token2_value => sqlcode
1099 ,p_token3 => g_sqlerrm_token
1100 ,p_token3_value => sqlerrm);
1101 x_return_status := g_ret_sts_unexp_error;
1102 END validate_row;
1103
1104 -----------------
1105 -- insert_row (B)
1106 -----------------
1107
1108 PROCEDURE insert_row(x_return_status OUT NOCOPY varchar2
1109 ,p_lrt_rec IN lrt_rec_type) IS
1110 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'insert_row (B)';
1111 l_lrt_rec lrt_rec_type := p_lrt_rec;
1112
1113 BEGIN
1114
1115 INSERT INTO okl_ls_rt_fctr_sets_b
1116 (id
1120 ,start_date
1117 ,object_version_number
1118 ,name
1119 ,arrears_yn
1121 ,end_date
1122 ,pdt_id
1123 ,rate
1124 ,try_id
1125 ,frq_code
1126 ,created_by
1127 ,creation_date
1128 ,last_updated_by
1129 ,last_update_date
1130 ,last_update_login
1131 ,attribute_category
1132 ,attribute1
1133 ,attribute2
1134 ,attribute3
1135 ,attribute4
1136 ,attribute5
1137 ,attribute6
1138 ,attribute7
1139 ,attribute8
1140 ,attribute9
1141 ,attribute10
1142 ,attribute11
1143 ,attribute12
1144 ,attribute13
1145 ,attribute14
1146 ,attribute15
1147 ,sts_code
1148 ,org_id
1149 ,currency_code
1150 ,lrs_type_code
1151 ,end_of_term_id
1152 ,orig_rate_set_id)
1153 VALUES (l_lrt_rec.id
1154 ,l_lrt_rec.object_version_number
1155 ,l_lrt_rec.name
1156 ,l_lrt_rec.arrears_yn
1157 ,l_lrt_rec.start_date
1158 ,l_lrt_rec.end_date
1159 ,l_lrt_rec.pdt_id
1160 ,l_lrt_rec.rate
1161 ,l_lrt_rec.try_id
1162 ,l_lrt_rec.frq_code
1163 ,l_lrt_rec.created_by
1164 ,l_lrt_rec.creation_date
1165 ,l_lrt_rec.last_updated_by
1166 ,l_lrt_rec.last_update_date
1167 ,l_lrt_rec.last_update_login
1168 ,l_lrt_rec.attribute_category
1169 ,l_lrt_rec.attribute1
1170 ,l_lrt_rec.attribute2
1171 ,l_lrt_rec.attribute3
1172 ,l_lrt_rec.attribute4
1173 ,l_lrt_rec.attribute5
1174 ,l_lrt_rec.attribute6
1175 ,l_lrt_rec.attribute7
1176 ,l_lrt_rec.attribute8
1177 ,l_lrt_rec.attribute9
1178 ,l_lrt_rec.attribute10
1179 ,l_lrt_rec.attribute11
1180 ,l_lrt_rec.attribute12
1181 ,l_lrt_rec.attribute13
1182 ,l_lrt_rec.attribute14
1183 ,l_lrt_rec.attribute15
1184 ,l_lrt_rec.sts_code
1185 ,l_lrt_rec.org_id
1186 ,l_lrt_rec.currency_code
1187 ,l_lrt_rec.lrs_type_code
1188 ,l_lrt_rec.end_of_term_id
1189 ,l_lrt_rec.orig_rate_set_id);
1190 x_return_status := g_ret_sts_success;
1191 EXCEPTION
1192 WHEN okl_api.g_exception_error THEN
1193 x_return_status := g_ret_sts_error;
1194 WHEN okl_api.g_exception_unexpected_error THEN
1195 x_return_status := g_ret_sts_unexp_error;
1196 WHEN OTHERS THEN
1197 okl_api.set_message(p_app_name => g_app_name
1198 ,p_msg_name => g_db_error
1199 ,p_token1 => g_prog_name_token
1200 ,p_token1_value => l_api_name
1201 ,p_token2 => g_sqlcode_token
1202 ,p_token2_value => sqlcode
1203 ,p_token3 => g_sqlerrm_token
1204 ,p_token3_value => sqlerrm);
1205 x_return_status := g_ret_sts_unexp_error;
1206 END insert_row;
1207
1208 ------------------
1209 -- insert_row (TL)
1210 ------------------
1211
1212 PROCEDURE insert_row(x_return_status OUT NOCOPY varchar2
1213 ,p_lrttl_rec IN lrttl_rec_type) IS
1214 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'insert_row (TL)';
1215
1216 CURSOR get_languages IS
1217 SELECT language_code
1218 FROM fnd_languages
1219 WHERE installed_flag IN('I', 'B');
1220 l_sfwt_flag varchar2(1);
1221 l_miss_flag varchar2(1) := 'Y';
1222
1223 BEGIN
1224
1225 FOR l_lang_rec IN get_languages LOOP
1226
1227 IF l_lang_rec.language_code = userenv('LANG') THEN
1228 l_sfwt_flag := 'N';
1229 ELSE
1230 l_sfwt_flag := 'Y';
1231 END IF;
1232
1233 IF l_lang_rec.language_code = userenv('LANG') THEN
1234 l_miss_flag := 'N';
1235 END IF;
1236
1237 INSERT INTO okl_ls_rt_fctr_sets_tl
1238 (id
1239 ,language
1240 ,source_lang
1241 ,sfwt_flag
1242 ,description
1243 ,created_by
1244 ,creation_date
1245 ,last_updated_by
1246 ,last_update_date
1247 ,last_update_login)
1248 VALUES (p_lrttl_rec.id
1249 ,l_lang_rec.language_code
1250 ,userenv('LANG')
1251 ,l_sfwt_flag
1252 ,p_lrttl_rec.description
1253 ,p_lrttl_rec.created_by
1254 ,p_lrttl_rec.creation_date
1255 ,p_lrttl_rec.last_updated_by
1256 ,p_lrttl_rec.last_update_date
1260
1257 ,p_lrttl_rec.last_update_login);
1258
1259 END LOOP;
1261 IF l_miss_flag = 'Y' THEN
1262 okl_api.set_message(p_app_name => g_app_name
1263 ,p_msg_name => 'OKL_LANG_NOT_INSTALLED'
1264 ,p_token1 => 'LANG_CODE'
1265 ,p_token1_value => userenv('LANG'));
1266 END IF;
1267 x_return_status := g_ret_sts_success;
1268 EXCEPTION
1269 WHEN okl_api.g_exception_error THEN
1270 x_return_status := g_ret_sts_error;
1271 WHEN okl_api.g_exception_unexpected_error THEN
1272 x_return_status := g_ret_sts_unexp_error;
1273 WHEN OTHERS THEN
1274 okl_api.set_message(p_app_name => g_app_name
1275 ,p_msg_name => g_db_error
1276 ,p_token1 => g_prog_name_token
1277 ,p_token1_value => l_api_name
1278 ,p_token2 => g_sqlcode_token
1279 ,p_token2_value => sqlcode
1280 ,p_token3 => g_sqlerrm_token
1281 ,p_token3_value => sqlerrm);
1282 x_return_status := g_ret_sts_unexp_error;
1283 END insert_row;
1284
1285 -------------------
1286 -- insert_row (REC)
1287 -------------------
1288
1289 PROCEDURE insert_row(p_api_version IN number
1290 ,p_init_msg_list IN varchar2 DEFAULT okc_api.g_false
1291 ,x_return_status OUT NOCOPY varchar2
1292 ,x_msg_count OUT NOCOPY number
1293 ,x_msg_data OUT NOCOPY varchar2
1294 ,p_lrtv_rec IN lrtv_rec_type
1295 ,x_lrtv_rec OUT NOCOPY lrtv_rec_type) IS
1296 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'insert_row (REC)';
1297 l_return_status varchar2(1);
1298 l_lrtv_rec lrtv_rec_type;
1299 l_lrt_rec lrt_rec_type;
1300 l_lrttl_rec lrttl_rec_type;
1301
1302 BEGIN
1303 l_lrtv_rec := null_out_defaults(p_lrtv_rec);
1304 l_lrtv_rec.id := okc_p_util.raw_to_number(sys_guid());
1305 l_lrtv_rec.object_version_number := 1;
1306 l_lrtv_rec.sfwt_flag := 'N';
1307 l_lrtv_rec.creation_date := sysdate;
1308 l_lrtv_rec.created_by := fnd_global.user_id;
1309 l_lrtv_rec.last_update_date := sysdate;
1310 l_lrtv_rec.last_updated_by := fnd_global.user_id;
1311 l_lrtv_rec.last_update_login := fnd_global.login_id;
1312 l_lrtv_rec.org_id := mo_global.get_current_org_id();
1313
1314 --default depricated columns
1315
1316 l_lrtv_rec.pdt_id := - 1;
1317 l_lrtv_rec.try_id := - 1;
1318 l_lrtv_rec.arrears_yn := 'NA';
1319 l_return_status := validate_attributes(l_lrtv_rec);
1320
1321 IF (l_return_status = g_ret_sts_unexp_error) THEN
1322 RAISE okl_api.g_exception_unexpected_error;
1323 ELSIF (l_return_status = g_ret_sts_error) THEN
1324 RAISE okl_api.g_exception_error;
1325 END IF;
1326
1327 l_return_status := validate_record(l_lrtv_rec);
1328
1329 IF (l_return_status = g_ret_sts_unexp_error) THEN
1330 RAISE okl_api.g_exception_unexpected_error;
1331 ELSIF (l_return_status = g_ret_sts_error) THEN
1332 RAISE okl_api.g_exception_error;
1333 END IF;
1334 migrate(l_lrtv_rec, l_lrt_rec);
1335 migrate(l_lrtv_rec, l_lrttl_rec);
1336 insert_row(x_return_status => l_return_status
1337 ,p_lrt_rec => l_lrt_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 insert_row(x_return_status => l_return_status
1345 ,p_lrttl_rec => l_lrttl_rec);
1346
1347 IF (l_return_status = g_ret_sts_unexp_error) THEN
1348 RAISE okl_api.g_exception_unexpected_error;
1349 ELSIF (l_return_status = g_ret_sts_error) THEN
1350 RAISE okl_api.g_exception_error;
1351 END IF;
1352 x_lrtv_rec := l_lrtv_rec;
1353 x_return_status := l_return_status;
1354 EXCEPTION
1355 WHEN okl_api.g_exception_error THEN
1356 x_return_status := g_ret_sts_error;
1357 WHEN okl_api.g_exception_unexpected_error THEN
1358 x_return_status := g_ret_sts_unexp_error;
1359 WHEN OTHERS THEN
1360 okl_api.set_message(p_app_name => g_app_name
1361 ,p_msg_name => g_db_error
1362 ,p_token1 => g_prog_name_token
1363 ,p_token1_value => l_api_name
1364 ,p_token2 => g_sqlcode_token
1365 ,p_token2_value => sqlcode
1366 ,p_token3 => g_sqlerrm_token
1367 ,p_token3_value => sqlerrm);
1368 x_return_status := g_ret_sts_unexp_error;
1369 END insert_row;
1370
1371 -------------------
1372 -- insert_row (TBL)
1373 -------------------
1374
1375 PROCEDURE insert_row(p_api_version IN number
1376 ,p_init_msg_list IN varchar2 DEFAULT okc_api.g_false
1377 ,x_return_status OUT NOCOPY varchar2
1381 ,x_lrtv_tbl OUT NOCOPY lrtv_tbl_type) IS
1378 ,x_msg_count OUT NOCOPY number
1379 ,x_msg_data OUT NOCOPY varchar2
1380 ,p_lrtv_tbl IN lrtv_tbl_type
1382 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'insert_row (TBL)';
1383 l_return_status varchar2(1) := g_ret_sts_success;
1384 i binary_integer;
1385
1386 BEGIN
1387
1388 IF (p_lrtv_tbl.COUNT > 0) THEN
1389 i := p_lrtv_tbl.FIRST;
1390
1391 LOOP
1392 IF p_lrtv_tbl.EXISTS(i) THEN
1393 insert_row(p_api_version => g_api_version
1394 ,p_init_msg_list => g_false
1395 ,x_return_status => l_return_status
1396 ,x_msg_count => x_msg_count
1397 ,x_msg_data => x_msg_data
1398 ,p_lrtv_rec => p_lrtv_tbl(i)
1399 ,x_lrtv_rec => x_lrtv_tbl(i));
1400 IF l_return_status = g_ret_sts_unexp_error THEN
1401 RAISE okl_api.g_exception_unexpected_error;
1402 ELSIF l_return_status = g_ret_sts_error THEN
1403 RAISE okl_api.g_exception_error;
1404 END IF;
1405 EXIT WHEN(i = p_lrtv_tbl.LAST);
1406 i := p_lrtv_tbl.next(i);
1407 END IF;
1408 END LOOP;
1409
1410 END IF;
1411 x_return_status := g_ret_sts_success;
1412 EXCEPTION
1413 WHEN okl_api.g_exception_error THEN
1414 x_return_status := g_ret_sts_error;
1415 WHEN okl_api.g_exception_unexpected_error THEN
1416 x_return_status := g_ret_sts_unexp_error;
1417 WHEN OTHERS THEN
1418 okl_api.set_message(p_app_name => g_app_name
1419 ,p_msg_name => g_db_error
1420 ,p_token1 => g_prog_name_token
1421 ,p_token1_value => l_api_name
1422 ,p_token2 => g_sqlcode_token
1423 ,p_token2_value => sqlcode
1424 ,p_token3 => g_sqlerrm_token
1425 ,p_token3_value => sqlerrm);
1426 x_return_status := g_ret_sts_unexp_error;
1427 END insert_row;
1428
1429 ---------------
1430 -- lock_row (B)
1431 ---------------
1432
1433 PROCEDURE lock_row(x_return_status OUT NOCOPY varchar2
1434 ,p_lrt_rec IN lrt_rec_type) IS
1435 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'lock_row (TL)';
1436 e_resource_busy EXCEPTION;
1437
1438 PRAGMA exception_init(e_resource_busy, - 00054);
1439
1440 CURSOR lock_csr(p_lrt_rec IN lrt_rec_type) IS
1441 SELECT object_version_number
1442 FROM okl_ls_rt_fctr_sets_b
1443 WHERE id = p_lrt_rec.id
1444 AND object_version_number = p_lrt_rec.object_version_number
1445 FOR UPDATE OF object_version_number NOWAIT;
1446
1447 CURSOR lchk_csr(p_lrt_rec IN lrt_rec_type) IS
1448 SELECT object_version_number
1449 FROM okl_ls_rt_fctr_sets_b
1450 WHERE id = p_lrt_rec.id;
1451 l_return_status varchar2(1) := g_ret_sts_success;
1452 l_object_version_number okl_ls_rt_fctr_sets_b.object_version_number%TYPE;
1453 lc_object_version_number okl_ls_rt_fctr_sets_b.object_version_number%TYPE;
1454 l_row_notfound boolean := false;
1455 lc_row_notfound boolean := false;
1456
1457 BEGIN
1458
1459 BEGIN
1460 OPEN lock_csr(p_lrt_rec);
1461 FETCH lock_csr INTO l_object_version_number ;
1462 l_row_notfound := lock_csr%NOTFOUND;
1463 CLOSE lock_csr;
1464 EXCEPTION
1465 WHEN e_resource_busy THEN
1466
1467 IF (lock_csr%ISOPEN) THEN
1468 CLOSE lock_csr;
1469 END IF;
1470 okc_api.set_message(g_fnd_app, g_form_unable_to_reserve_rec);
1471 RAISE app_exceptions.record_lock_exception;
1472 END;
1473
1474 IF (l_row_notfound) THEN
1475 OPEN lchk_csr(p_lrt_rec);
1476 FETCH lchk_csr INTO lc_object_version_number ;
1477 lc_row_notfound := lchk_csr%NOTFOUND;
1478 CLOSE lchk_csr;
1479 END IF;
1480
1481 IF (lc_row_notfound) THEN
1482 okc_api.set_message(g_fnd_app, g_form_record_deleted);
1483 RAISE okl_api.g_exception_error;
1484 ELSIF lc_object_version_number > p_lrt_rec.object_version_number THEN
1485 okc_api.set_message(g_fnd_app, g_form_record_changed);
1486 RAISE okl_api.g_exception_error;
1487 ELSIF lc_object_version_number <> p_lrt_rec.object_version_number THEN
1488 okc_api.set_message(g_fnd_app, g_form_record_changed);
1489 RAISE okl_api.g_exception_error;
1490 ELSIF lc_object_version_number = - 1 THEN
1491 okc_api.set_message(g_app_name, g_record_logically_deleted);
1492 RAISE okl_api.g_exception_error;
1493 END IF;
1494 x_return_status := l_return_status;
1495 EXCEPTION
1496 WHEN okl_api.g_exception_error THEN
1497 x_return_status := g_ret_sts_error;
1498 WHEN okl_api.g_exception_unexpected_error THEN
1499 x_return_status := g_ret_sts_unexp_error;
1500 WHEN OTHERS THEN
1501 okl_api.set_message(p_app_name => g_app_name
1505 ,p_token2 => g_sqlcode_token
1502 ,p_msg_name => g_db_error
1503 ,p_token1 => g_prog_name_token
1504 ,p_token1_value => l_api_name
1506 ,p_token2_value => sqlcode
1507 ,p_token3 => g_sqlerrm_token
1508 ,p_token3_value => sqlerrm);
1509 x_return_status := g_ret_sts_unexp_error;
1510 END lock_row;
1511
1512 ---------------
1513 -- lock_row (TL)
1514 ---------------
1515
1516 PROCEDURE lock_row(x_return_status OUT NOCOPY varchar2
1517 ,p_lrttl_rec IN lrttl_rec_type) IS
1518 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'lock_row (TL)';
1519 e_resource_busy EXCEPTION;
1520
1521 PRAGMA exception_init(e_resource_busy, - 00054);
1522
1523 CURSOR lock_csr(p_lrttl_rec IN lrttl_rec_type) IS
1524 SELECT *
1525 FROM okl_ls_rt_fctr_sets_tl
1526 WHERE id = p_lrttl_rec.id
1527 FOR UPDATE NOWAIT;
1528 l_return_status varchar2(1) := g_ret_sts_success;
1529 l_lock_var lock_csr%ROWTYPE;
1530 l_row_notfound boolean := false;
1531 lc_row_notfound boolean := false;
1532
1533 BEGIN
1534
1535 BEGIN
1536 OPEN lock_csr(p_lrttl_rec);
1537 FETCH lock_csr INTO l_lock_var ;
1538 l_row_notfound := lock_csr%NOTFOUND;
1539 CLOSE lock_csr;
1540 EXCEPTION
1541 WHEN e_resource_busy THEN
1542
1543 IF (lock_csr%ISOPEN) THEN
1544 CLOSE lock_csr;
1545 END IF;
1546 okl_api.set_message(g_fnd_app, g_form_unable_to_reserve_rec);
1547 RAISE app_exceptions.record_lock_exception;
1548 END;
1549
1550 IF l_row_notfound THEN
1551 okl_api.set_message(g_fnd_app, g_form_record_deleted);
1552 RAISE okl_api.g_exception_error;
1553 END IF;
1554 x_return_status := l_return_status;
1555 EXCEPTION
1556 WHEN okl_api.g_exception_error THEN
1557 x_return_status := g_ret_sts_error;
1558 WHEN okl_api.g_exception_unexpected_error THEN
1559 x_return_status := g_ret_sts_unexp_error;
1560 WHEN OTHERS THEN
1561 okl_api.set_message(p_app_name => g_app_name
1562 ,p_msg_name => g_db_error
1563 ,p_token1 => g_prog_name_token
1564 ,p_token1_value => l_api_name
1565 ,p_token2 => g_sqlcode_token
1566 ,p_token2_value => sqlcode
1567 ,p_token3 => g_sqlerrm_token
1568 ,p_token3_value => sqlerrm);
1569 x_return_status := g_ret_sts_unexp_error;
1570 END lock_row;
1571
1572 -----------------
1573 -- lock_row (REC)
1574 -----------------
1575
1576 PROCEDURE lock_row(p_api_version IN number
1577 ,p_init_msg_list IN varchar2 DEFAULT okc_api.g_false
1578 ,x_return_status OUT NOCOPY varchar2
1579 ,x_msg_count OUT NOCOPY number
1580 ,x_msg_data OUT NOCOPY varchar2
1581 ,p_lrtv_rec IN lrtv_rec_type) IS
1582 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'lock_row (REC)';
1583 l_return_status varchar2(1);
1584 l_lrt_rec lrt_rec_type;
1585 l_lrttl_rec lrttl_rec_type;
1586
1587 BEGIN
1588 migrate(p_lrtv_rec, l_lrt_rec);
1589 migrate(p_lrtv_rec, l_lrttl_rec);
1590 lock_row(x_return_status => l_return_status
1591 ,p_lrt_rec => l_lrt_rec);
1592
1593 IF (l_return_status = g_ret_sts_unexp_error) THEN
1594 RAISE okl_api.g_exception_unexpected_error;
1595 ELSIF (l_return_status = g_ret_sts_error) THEN
1596 RAISE okl_api.g_exception_error;
1597 END IF;
1598 lock_row(x_return_status => l_return_status
1599 ,p_lrttl_rec => l_lrttl_rec);
1600
1601 IF (l_return_status = g_ret_sts_unexp_error) THEN
1602 RAISE okl_api.g_exception_unexpected_error;
1603 ELSIF (l_return_status = g_ret_sts_error) THEN
1604 RAISE okl_api.g_exception_error;
1605 END IF;
1606 x_return_status := l_return_status;
1607 EXCEPTION
1608 WHEN okl_api.g_exception_error THEN
1609 x_return_status := g_ret_sts_error;
1610 WHEN okl_api.g_exception_unexpected_error THEN
1611 x_return_status := g_ret_sts_unexp_error;
1612 WHEN OTHERS THEN
1613 okl_api.set_message(p_app_name => g_app_name
1614 ,p_msg_name => g_db_error
1615 ,p_token1 => g_prog_name_token
1616 ,p_token1_value => l_api_name
1617 ,p_token2 => g_sqlcode_token
1618 ,p_token2_value => sqlcode
1619 ,p_token3 => g_sqlerrm_token
1620 ,p_token3_value => sqlerrm);
1621 x_return_status := g_ret_sts_unexp_error;
1622 END lock_row;
1623
1624 -----------------
1625 -- lock_row (TBL)
1626 -----------------
1627
1628 PROCEDURE lock_row(p_api_version IN number
1632 ,x_msg_data OUT NOCOPY varchar2
1629 ,p_init_msg_list IN varchar2 DEFAULT okc_api.g_false
1630 ,x_return_status OUT NOCOPY varchar2
1631 ,x_msg_count OUT NOCOPY number
1633 ,p_lrtv_tbl IN lrtv_tbl_type) IS
1634 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'lock_row (TBL)';
1635 l_return_status varchar2(1) := g_ret_sts_success;
1636 i binary_integer;
1637
1638 BEGIN
1639
1640 IF (p_lrtv_tbl.COUNT > 0) THEN
1641 i := p_lrtv_tbl.FIRST;
1642
1643 LOOP
1644 IF p_lrtv_tbl.EXISTS(i) THEN
1645 lock_row(p_api_version => g_api_version
1646 ,p_init_msg_list => g_false
1647 ,x_return_status => l_return_status
1648 ,x_msg_count => x_msg_count
1649 ,x_msg_data => x_msg_data
1650 ,p_lrtv_rec => p_lrtv_tbl(i));
1651 IF l_return_status = g_ret_sts_unexp_error THEN
1652 RAISE okl_api.g_exception_unexpected_error;
1653 ELSIF l_return_status = g_ret_sts_error THEN
1654 RAISE okl_api.g_exception_error;
1655 END IF;
1656 EXIT WHEN(i = p_lrtv_tbl.LAST);
1657 i := p_lrtv_tbl.next(i);
1658 END IF;
1659 END LOOP;
1660
1661 END IF;
1662 x_return_status := l_return_status;
1663 EXCEPTION
1664 WHEN okl_api.g_exception_error THEN
1665 x_return_status := g_ret_sts_error;
1666 WHEN okl_api.g_exception_unexpected_error THEN
1667 x_return_status := g_ret_sts_unexp_error;
1668 WHEN OTHERS THEN
1669 okl_api.set_message(p_app_name => g_app_name
1670 ,p_msg_name => g_db_error
1671 ,p_token1 => g_prog_name_token
1672 ,p_token1_value => l_api_name
1673 ,p_token2 => g_sqlcode_token
1674 ,p_token2_value => sqlcode
1675 ,p_token3 => g_sqlerrm_token
1676 ,p_token3_value => sqlerrm);
1677 x_return_status := g_ret_sts_unexp_error;
1678 END lock_row;
1679
1680 -----------------
1681 -- update_row (B)
1682 -----------------
1683
1684 PROCEDURE update_row(x_return_status OUT NOCOPY varchar2
1685 ,p_lrt_rec IN lrt_rec_type) IS
1686 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'update_row (B)';
1687
1688 BEGIN
1689
1690 UPDATE okl_ls_rt_fctr_sets_b
1691 SET object_version_number = p_lrt_rec.object_version_number + 1
1692 ,name = p_lrt_rec.name
1693 ,arrears_yn = p_lrt_rec.arrears_yn
1694 ,start_date = p_lrt_rec.start_date
1695 ,end_date = p_lrt_rec.end_date
1696 ,pdt_id = p_lrt_rec.pdt_id
1697 ,rate = p_lrt_rec.rate
1698 ,try_id = p_lrt_rec.try_id
1699 ,frq_code = p_lrt_rec.frq_code
1700 ,created_by = p_lrt_rec.created_by
1701 ,creation_date = p_lrt_rec.creation_date
1702 ,last_updated_by = p_lrt_rec.last_updated_by
1703 ,last_update_date = p_lrt_rec.last_update_date
1704 ,last_update_login = p_lrt_rec.last_update_login
1705 ,attribute_category = p_lrt_rec.attribute_category
1706 ,attribute1 = p_lrt_rec.attribute1
1707 ,attribute2 = p_lrt_rec.attribute2
1708 ,attribute3 = p_lrt_rec.attribute3
1709 ,attribute4 = p_lrt_rec.attribute4
1710 ,attribute5 = p_lrt_rec.attribute5
1711 ,attribute6 = p_lrt_rec.attribute6
1712 ,attribute7 = p_lrt_rec.attribute7
1713 ,attribute8 = p_lrt_rec.attribute8
1714 ,attribute9 = p_lrt_rec.attribute9
1715 ,attribute10 = p_lrt_rec.attribute10
1716 ,attribute11 = p_lrt_rec.attribute11
1717 ,attribute12 = p_lrt_rec.attribute12
1718 ,attribute13 = p_lrt_rec.attribute13
1719 ,attribute14 = p_lrt_rec.attribute14
1720 ,attribute15 = p_lrt_rec.attribute15
1721 ,sts_code = p_lrt_rec.sts_code
1722 ,org_id = p_lrt_rec.org_id
1723 ,currency_code = p_lrt_rec.currency_code
1724 ,lrs_type_code = p_lrt_rec.lrs_type_code
1725 ,end_of_term_id = p_lrt_rec.end_of_term_id
1726 ,orig_rate_set_id = p_lrt_rec.orig_rate_set_id
1727 WHERE id = p_lrt_rec.id;
1728 x_return_status := g_ret_sts_success;
1729 EXCEPTION
1730 WHEN okl_api.g_exception_error THEN
1731 x_return_status := g_ret_sts_error;
1732 WHEN okl_api.g_exception_unexpected_error THEN
1733 x_return_status := g_ret_sts_unexp_error;
1734 WHEN OTHERS THEN
1735 okl_api.set_message(p_app_name => g_app_name
1736 ,p_msg_name => g_db_error
1737 ,p_token1 => g_prog_name_token
1738 ,p_token1_value => l_api_name
1739 ,p_token2 => g_sqlcode_token
1740 ,p_token2_value => sqlcode
1741 ,p_token3 => g_sqlerrm_token
1742 ,p_token3_value => sqlerrm);
1743 x_return_status := g_ret_sts_unexp_error;
1744 END update_row;
1745
1746 ------------------
1747 -- update_row (TL)
1751 ,p_lrttl_rec IN lrttl_rec_type) IS
1748 ------------------
1749
1750 PROCEDURE update_row(x_return_status OUT NOCOPY varchar2
1752 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'update_row (TL)';
1753
1754 CURSOR c_lang_setup IS
1755 SELECT 'Y'
1756 FROM fnd_languages
1757 WHERE installed_flag IN('I', 'B') AND language_code = userenv('LANG');
1758
1759 CURSOR c_lang_found IS
1760 SELECT 'Y'
1761 FROM okl_ls_rt_fctr_sets_tl
1762 WHERE id = p_lrttl_rec.id AND language = userenv('LANG');
1763 l_lang_setup varchar2(1) := 'N';
1764 l_lang_found varchar2(1) := 'N';
1765
1766 BEGIN
1767 OPEN c_lang_setup;
1768 FETCH c_lang_setup INTO l_lang_setup ;
1769 CLOSE c_lang_setup;
1770
1771 IF l_lang_setup = 'N' THEN
1772 okl_api.set_message(p_app_name => g_app_name
1773 ,p_msg_name => 'OKL_LANG_NOT_INSTALLED'
1774 ,p_token1 => 'LANG_CODE'
1775 ,p_token1_value => userenv('LANG'));
1776 END IF;
1777 OPEN c_lang_found;
1778 FETCH c_lang_found INTO l_lang_found ;
1779 CLOSE c_lang_found;
1780
1781 IF l_lang_found = 'N' THEN
1782
1783 INSERT INTO okl_ls_rt_fctr_sets_tl
1784 (id
1785 ,language
1786 ,source_lang
1787 ,sfwt_flag
1788 ,description
1789 ,created_by
1790 ,creation_date
1791 ,last_updated_by
1792 ,last_update_date
1793 ,last_update_login)
1794 VALUES (p_lrttl_rec.id
1795 ,userenv('LANG')
1796 ,userenv('LANG')
1797 ,'N'
1798 ,p_lrttl_rec.description
1799 ,p_lrttl_rec.created_by
1800 ,p_lrttl_rec.creation_date
1801 ,p_lrttl_rec.last_updated_by
1802 ,p_lrttl_rec.last_update_date
1803 ,p_lrttl_rec.last_update_login);
1804
1805 END IF;
1806
1807 UPDATE okl_ls_rt_fctr_sets_tl
1808 SET description = p_lrttl_rec.description
1809 ,source_lang = userenv('LANG')
1810 ,created_by = p_lrttl_rec.created_by
1811 ,creation_date = p_lrttl_rec.creation_date
1812 ,last_updated_by = p_lrttl_rec.last_updated_by
1813 ,last_update_date = p_lrttl_rec.last_update_date
1814 ,last_update_login = p_lrttl_rec.last_update_login
1815 WHERE id = p_lrttl_rec.id;
1816
1817 UPDATE okl_ls_rt_fctr_sets_tl
1818 SET sfwt_flag = 'Y'
1819 WHERE id = p_lrttl_rec.id AND source_lang <> language;
1820
1821 UPDATE okl_ls_rt_fctr_sets_tl
1822 SET sfwt_flag = 'N'
1823 WHERE id = p_lrttl_rec.id AND source_lang = language;
1824 x_return_status := g_ret_sts_success;
1825 EXCEPTION
1826 WHEN okl_api.g_exception_error THEN
1827 x_return_status := g_ret_sts_error;
1828 WHEN okl_api.g_exception_unexpected_error THEN
1829 x_return_status := g_ret_sts_unexp_error;
1830 WHEN OTHERS THEN
1831 okl_api.set_message(p_app_name => g_app_name
1832 ,p_msg_name => g_db_error
1833 ,p_token1 => g_prog_name_token
1834 ,p_token1_value => l_api_name
1835 ,p_token2 => g_sqlcode_token
1836 ,p_token2_value => sqlcode
1837 ,p_token3 => g_sqlerrm_token
1838 ,p_token3_value => sqlerrm);
1839 x_return_status := g_ret_sts_unexp_error;
1840 END update_row;
1841
1842 -------------------
1843 -- update_row (REC)
1844 -------------------
1845
1846 PROCEDURE update_row(p_api_version IN number
1847 ,p_init_msg_list IN varchar2 DEFAULT okc_api.g_false
1848 ,x_return_status OUT NOCOPY varchar2
1849 ,x_msg_count OUT NOCOPY number
1850 ,x_msg_data OUT NOCOPY varchar2
1851 ,p_lrtv_rec IN lrtv_rec_type
1852 ,x_lrtv_rec OUT NOCOPY lrtv_rec_type) IS
1853 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'update_row (REC)';
1854 l_return_status varchar2(1);
1855 l_lrtv_rec lrtv_rec_type;
1856 l_lrt_rec lrt_rec_type;
1857 l_lrttl_rec lrttl_rec_type;
1858 l_lrfv_tbl okl_lrf_pvt.lrfv_tbl_type;
1859 lx_lrfv_tbl okl_lrf_pvt.lrfv_tbl_type;
1860 i binary_integer := 0;
1861 l_old_rate number;
1862
1863 CURSOR c_lrf_rec(p_lrt_id number) IS
1864 SELECT id
1865 FROM okl_ls_rt_fctr_ents
1866 WHERE lrt_id = p_lrt_id;
1867
1868 ----------------------
1869 -- populate_new_record
1870 ----------------------
1871
1872 FUNCTION populate_new_record(p_lrtv_rec IN lrtv_rec_type
1873 ,x_lrtv_rec OUT NOCOPY lrtv_rec_type) RETURN varchar2 IS
1874 l_return_status varchar2(1);
1875 l_db_lrtv_rec lrtv_rec_type;
1876
1877 BEGIN
1881 IF l_return_status = g_ret_sts_unexp_error THEN
1878 x_lrtv_rec := p_lrtv_rec;
1879 l_db_lrtv_rec := get_rec(p_lrtv_rec.id, l_return_status);
1880
1882 RAISE okl_api.g_exception_unexpected_error;
1883 ELSIF l_return_status = g_ret_sts_error THEN
1884 RAISE okl_api.g_exception_error;
1885 END IF;
1886
1887 -- Do NOT default the following 4 standard attributes from the DB
1888 -- object_version_number
1889 -- last_update_date
1890 -- last_update_by
1891 -- last_update_login
1892
1893 IF (x_lrtv_rec.id IS NULL) THEN
1894 x_lrtv_rec.id := l_db_lrtv_rec.id;
1895 END IF;
1896
1897 IF (x_lrtv_rec.try_id IS NULL) THEN
1898 x_lrtv_rec.try_id := l_db_lrtv_rec.try_id;
1899 END IF;
1900
1901 IF (x_lrtv_rec.pdt_id IS NULL) THEN
1902 x_lrtv_rec.pdt_id := l_db_lrtv_rec.pdt_id;
1903 END IF;
1904
1905 IF (x_lrtv_rec.rate IS NULL) THEN
1906 x_lrtv_rec.rate := l_db_lrtv_rec.rate;
1907 END IF;
1908
1909 IF (x_lrtv_rec.frq_code IS NULL) THEN
1910 x_lrtv_rec.frq_code := l_db_lrtv_rec.frq_code;
1911 END IF;
1912
1913 IF (x_lrtv_rec.arrears_yn IS NULL) THEN
1914 x_lrtv_rec.arrears_yn := l_db_lrtv_rec.arrears_yn;
1915 END IF;
1916
1917 IF (x_lrtv_rec.start_date IS NULL) THEN
1918 x_lrtv_rec.start_date := l_db_lrtv_rec.start_date;
1919 END IF;
1920
1921 IF (x_lrtv_rec.end_date IS NULL) THEN
1922 x_lrtv_rec.end_date := l_db_lrtv_rec.end_date;
1923 END IF;
1924
1925 IF (x_lrtv_rec.name IS NULL) THEN
1926 x_lrtv_rec.name := l_db_lrtv_rec.name;
1927 END IF;
1928
1929 IF (x_lrtv_rec.description IS NULL) THEN
1930 x_lrtv_rec.description := l_db_lrtv_rec.description;
1931 END IF;
1932
1933 IF (x_lrtv_rec.created_by IS NULL) THEN
1934 x_lrtv_rec.created_by := l_db_lrtv_rec.created_by;
1935 END IF;
1936
1937 IF (x_lrtv_rec.creation_date IS NULL) THEN
1938 x_lrtv_rec.creation_date := l_db_lrtv_rec.creation_date;
1939 END IF;
1940
1941 IF (x_lrtv_rec.attribute_category IS NULL) THEN
1942 x_lrtv_rec.attribute_category := l_db_lrtv_rec.attribute_category;
1943 END IF;
1944
1945 IF (x_lrtv_rec.attribute1 IS NULL) THEN
1946 x_lrtv_rec.attribute1 := l_db_lrtv_rec.attribute1;
1947 END IF;
1948
1949 IF (x_lrtv_rec.attribute2 IS NULL) THEN
1950 x_lrtv_rec.attribute2 := l_db_lrtv_rec.attribute2;
1951 END IF;
1952
1953 IF (x_lrtv_rec.attribute3 IS NULL) THEN
1954 x_lrtv_rec.attribute3 := l_db_lrtv_rec.attribute3;
1955 END IF;
1956
1957 IF (x_lrtv_rec.attribute4 IS NULL) THEN
1958 x_lrtv_rec.attribute4 := l_db_lrtv_rec.attribute4;
1959 END IF;
1960
1961 IF (x_lrtv_rec.attribute5 IS NULL) THEN
1962 x_lrtv_rec.attribute5 := l_db_lrtv_rec.attribute5;
1963 END IF;
1964
1965 IF (x_lrtv_rec.attribute6 IS NULL) THEN
1966 x_lrtv_rec.attribute6 := l_db_lrtv_rec.attribute6;
1967 END IF;
1968
1969 IF (x_lrtv_rec.attribute7 IS NULL) THEN
1970 x_lrtv_rec.attribute7 := l_db_lrtv_rec.attribute7;
1971 END IF;
1972
1973 IF (x_lrtv_rec.attribute8 IS NULL) THEN
1974 x_lrtv_rec.attribute8 := l_db_lrtv_rec.attribute8;
1975 END IF;
1976
1977 IF (x_lrtv_rec.attribute9 IS NULL) THEN
1978 x_lrtv_rec.attribute9 := l_db_lrtv_rec.attribute9;
1979 END IF;
1980
1981 IF (x_lrtv_rec.attribute10 IS NULL) THEN
1982 x_lrtv_rec.attribute10 := l_db_lrtv_rec.attribute10;
1983 END IF;
1984
1985 IF (x_lrtv_rec.attribute11 IS NULL) THEN
1986 x_lrtv_rec.attribute11 := l_db_lrtv_rec.attribute11;
1987 END IF;
1988
1989 IF (x_lrtv_rec.attribute12 IS NULL) THEN
1990 x_lrtv_rec.attribute12 := l_db_lrtv_rec.attribute12;
1991 END IF;
1992
1993 IF (x_lrtv_rec.attribute13 IS NULL) THEN
1994 x_lrtv_rec.attribute13 := l_db_lrtv_rec.attribute13;
1995 END IF;
1996
1997 IF (x_lrtv_rec.attribute14 IS NULL) THEN
1998 x_lrtv_rec.attribute14 := l_db_lrtv_rec.attribute14;
1999 END IF;
2000
2001 IF (x_lrtv_rec.attribute15 IS NULL) THEN
2002 x_lrtv_rec.attribute15 := l_db_lrtv_rec.attribute15;
2003 END IF;
2004
2005 IF (x_lrtv_rec.sts_code IS NULL) THEN
2006 x_lrtv_rec.sts_code := l_db_lrtv_rec.sts_code;
2007 END IF;
2008
2009 IF (x_lrtv_rec.org_id IS NULL) THEN
2010 x_lrtv_rec.org_id := l_db_lrtv_rec.org_id;
2011 END IF;
2012
2013 IF (x_lrtv_rec.currency_code IS NULL) THEN
2014 x_lrtv_rec.currency_code := l_db_lrtv_rec.currency_code;
2015 END IF;
2016
2017 IF (x_lrtv_rec.lrs_type_code IS NULL) THEN
2018 x_lrtv_rec.lrs_type_code := l_db_lrtv_rec.lrs_type_code;
2019 END IF;
2020
2021 IF (x_lrtv_rec.end_of_term_id IS NULL) THEN
2022 x_lrtv_rec.end_of_term_id := l_db_lrtv_rec.end_of_term_id;
2023 END IF;
2024
2025 IF (x_lrtv_rec.orig_rate_set_id IS NULL) THEN
2026 x_lrtv_rec.orig_rate_set_id := l_db_lrtv_rec.orig_rate_set_id;
2027 END IF;
2031 EXCEPTION
2028
2029
2030 RETURN l_return_status;
2032 WHEN okl_api.g_exception_error THEN
2033 x_return_status := g_ret_sts_error;
2034 WHEN okl_api.g_exception_unexpected_error THEN
2035 x_return_status := g_ret_sts_unexp_error;
2036 WHEN OTHERS THEN
2037 okl_api.set_message(p_app_name => g_app_name
2038 ,p_msg_name => g_db_error
2039 ,p_token1 => g_prog_name_token
2040 ,p_token1_value => l_api_name
2041 ,p_token2 => g_sqlcode_token
2042 ,p_token2_value => sqlcode
2043 ,p_token3 => g_sqlerrm_token
2044 ,p_token3_value => sqlerrm);
2045 x_return_status := g_ret_sts_unexp_error;
2046 END populate_new_record;
2047
2048 BEGIN
2049 l_return_status := populate_new_record(p_lrtv_rec, l_lrtv_rec);
2050
2051 IF (l_return_status = g_ret_sts_unexp_error) THEN
2052 RAISE okl_api.g_exception_unexpected_error;
2053 ELSIF (l_return_status = g_ret_sts_error) THEN
2054 RAISE okl_api.g_exception_error;
2055 END IF;
2056
2057 --null out g miss values
2058
2059 l_lrtv_rec := null_out_defaults(l_lrtv_rec);
2060
2061 l_lrtv_rec.sfwt_flag := 'N';
2062 l_lrtv_rec.last_update_date := sysdate;
2063 l_lrtv_rec.last_updated_by := fnd_global.user_id;
2064 l_lrtv_rec.last_update_login := fnd_global.login_id;
2065 l_return_status := validate_attributes(l_lrtv_rec);
2066
2067 IF (l_return_status = g_ret_sts_unexp_error) THEN
2068 RAISE okl_api.g_exception_unexpected_error;
2069 ELSIF (l_return_status = g_ret_sts_error) THEN
2070 RAISE okl_api.g_exception_error;
2071 END IF;
2072 l_return_status := validate_record(l_lrtv_rec);
2073
2074 IF (l_return_status = g_ret_sts_unexp_error) THEN
2075 RAISE okl_api.g_exception_unexpected_error;
2076 ELSIF (l_return_status = g_ret_sts_error) THEN
2077 RAISE okl_api.g_exception_error;
2078 END IF;
2079
2080 lock_row(p_api_version => g_api_version
2081 ,p_init_msg_list => g_false
2082 ,x_return_status => l_return_status
2083 ,x_msg_count => x_msg_count
2084 ,x_msg_data => x_msg_data
2085 ,p_lrtv_rec => l_lrtv_rec);
2086
2087 IF (l_return_status = g_ret_sts_unexp_error) THEN
2088 RAISE okl_api.g_exception_unexpected_error;
2089 ELSIF (l_return_status = g_ret_sts_error) THEN
2090 RAISE okl_api.g_exception_error;
2091 END IF;
2092 migrate(l_lrtv_rec, l_lrt_rec);
2093 migrate(l_lrtv_rec, l_lrttl_rec);
2094 update_row(x_return_status => l_return_status
2095 ,p_lrt_rec => l_lrt_rec);
2096
2097 IF (l_return_status = g_ret_sts_unexp_error) THEN
2098 RAISE okl_api.g_exception_unexpected_error;
2099 ELSIF (l_return_status = g_ret_sts_error) THEN
2100 RAISE okl_api.g_exception_error;
2101 END IF;
2102
2103 update_row(x_return_status => l_return_status
2104 ,p_lrttl_rec => l_lrttl_rec);
2105
2106 IF (l_return_status = g_ret_sts_unexp_error) THEN
2107 RAISE okl_api.g_exception_unexpected_error;
2108 ELSIF (l_return_status = g_ret_sts_error) THEN
2109 RAISE okl_api.g_exception_error;
2110 END IF;
2111 x_return_status := l_return_status;
2112 x_lrtv_rec := l_lrtv_rec;
2113 EXCEPTION
2114 WHEN okl_api.g_exception_error THEN
2115 x_return_status := g_ret_sts_error;
2116 WHEN okl_api.g_exception_unexpected_error THEN
2117 x_return_status := g_ret_sts_unexp_error;
2118 WHEN OTHERS THEN
2119 okl_api.set_message(p_app_name => g_app_name
2120 ,p_msg_name => g_db_error
2121 ,p_token1 => g_prog_name_token
2122 ,p_token1_value => l_api_name
2123 ,p_token2 => g_sqlcode_token
2124 ,p_token2_value => sqlcode
2125 ,p_token3 => g_sqlerrm_token
2126 ,p_token3_value => sqlerrm);
2127 x_return_status := g_ret_sts_unexp_error;
2128 END update_row;
2129
2130 -------------------
2131 -- update_row (TBL)
2132 -------------------
2133
2134 PROCEDURE update_row(p_api_version IN number
2135 ,p_init_msg_list IN varchar2 DEFAULT okc_api.g_false
2136 ,x_return_status OUT NOCOPY varchar2
2137 ,x_msg_count OUT NOCOPY number
2138 ,x_msg_data OUT NOCOPY varchar2
2139 ,p_lrtv_tbl IN lrtv_tbl_type
2140 ,x_lrtv_tbl OUT NOCOPY lrtv_tbl_type) IS
2141 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'update_row (TBL)';
2142 l_return_status varchar2(1) := g_ret_sts_success;
2143 i binary_integer;
2144
2145 BEGIN
2146
2147 IF (p_lrtv_tbl.COUNT > 0) THEN
2148 i := p_lrtv_tbl.FIRST;
2149
2150 LOOP
2151 IF p_lrtv_tbl.EXISTS(i) THEN
2152 update_row(p_api_version => g_api_version
2156 ,x_msg_data => x_msg_data
2153 ,p_init_msg_list => g_false
2154 ,x_return_status => l_return_status
2155 ,x_msg_count => x_msg_count
2157 ,p_lrtv_rec => p_lrtv_tbl(i)
2158 ,x_lrtv_rec => x_lrtv_tbl(i));
2159 IF l_return_status = g_ret_sts_unexp_error THEN
2160 RAISE okl_api.g_exception_unexpected_error;
2161 ELSIF l_return_status = g_ret_sts_error THEN
2162 RAISE okl_api.g_exception_error;
2163 END IF;
2164 EXIT WHEN(i = p_lrtv_tbl.LAST);
2165 i := p_lrtv_tbl.next(i);
2166 END IF;
2167 END LOOP;
2168
2169 END IF;
2170 x_return_status := l_return_status;
2171 EXCEPTION
2172 WHEN okl_api.g_exception_error THEN
2173 x_return_status := g_ret_sts_error;
2174 WHEN okl_api.g_exception_unexpected_error THEN
2175 x_return_status := g_ret_sts_unexp_error;
2176 WHEN OTHERS THEN
2177 okl_api.set_message(p_app_name => g_app_name
2178 ,p_msg_name => g_db_error
2179 ,p_token1 => g_prog_name_token
2180 ,p_token1_value => l_api_name
2181 ,p_token2 => g_sqlcode_token
2182 ,p_token2_value => sqlcode
2183 ,p_token3 => g_sqlerrm_token
2184 ,p_token3_value => sqlerrm);
2185 x_return_status := g_ret_sts_unexp_error;
2186 END update_row;
2187
2188 -----------------
2189 -- delete_row (B)
2190 -----------------
2191
2192 PROCEDURE delete_row(p_init_msg_list IN varchar2 DEFAULT okc_api.g_false
2193 ,x_return_status OUT NOCOPY varchar2
2194 ,x_msg_count OUT NOCOPY number
2195 ,x_msg_data OUT NOCOPY varchar2
2196 ,p_lrt_rec IN lrt_rec_type) IS
2197 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'delete_row (B)';
2198
2199 BEGIN
2200
2201 DELETE FROM okl_ls_rt_fctr_ents
2202 WHERE lrt_id = p_lrt_rec.id;
2203
2204 DELETE FROM okl_ls_rt_fctr_sets_b
2205 WHERE id = p_lrt_rec.id;
2206 x_return_status := g_ret_sts_success;
2207 EXCEPTION
2208 WHEN okl_api.g_exception_error THEN
2209 x_return_status := g_ret_sts_error;
2210 WHEN okl_api.g_exception_unexpected_error THEN
2211 x_return_status := g_ret_sts_unexp_error;
2212 WHEN OTHERS THEN
2213 okl_api.set_message(p_app_name => g_app_name
2214 ,p_msg_name => g_db_error
2215 ,p_token1 => g_prog_name_token
2216 ,p_token1_value => l_api_name
2217 ,p_token2 => g_sqlcode_token
2218 ,p_token2_value => sqlcode
2219 ,p_token3 => g_sqlerrm_token
2220 ,p_token3_value => sqlerrm);
2221 x_return_status := g_ret_sts_unexp_error;
2222 END delete_row;
2223
2224 ------------------
2225 -- delete_row (TL)
2226 ------------------
2227
2228 PROCEDURE delete_row(p_init_msg_list IN varchar2 DEFAULT okc_api.g_false
2229 ,x_return_status OUT NOCOPY varchar2
2230 ,x_msg_count OUT NOCOPY number
2231 ,x_msg_data OUT NOCOPY varchar2
2232 ,p_lrttl_rec IN lrttl_rec_type) IS
2233 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'delete_row (TL)';
2234
2235 BEGIN
2236
2237 DELETE FROM okl_ls_rt_fctr_sets_tl
2238 WHERE id = p_lrttl_rec.id;
2239 x_return_status := g_ret_sts_success;
2240 EXCEPTION
2241 WHEN okl_api.g_exception_error THEN
2242 x_return_status := g_ret_sts_error;
2243 WHEN okl_api.g_exception_unexpected_error THEN
2244 x_return_status := g_ret_sts_unexp_error;
2245 WHEN OTHERS THEN
2246 okl_api.set_message(p_app_name => g_app_name
2247 ,p_msg_name => g_db_error
2248 ,p_token1 => g_prog_name_token
2249 ,p_token1_value => l_api_name
2250 ,p_token2 => g_sqlcode_token
2251 ,p_token2_value => sqlcode
2252 ,p_token3 => g_sqlerrm_token
2253 ,p_token3_value => sqlerrm);
2254 x_return_status := g_ret_sts_unexp_error;
2255 END delete_row;
2256
2257 -------------------
2258 -- delete_row (REC)
2259 -------------------
2260
2261 PROCEDURE delete_row(p_api_version IN number
2262 ,p_init_msg_list IN varchar2 DEFAULT okc_api.g_false
2263 ,x_return_status OUT NOCOPY varchar2
2264 ,x_msg_count OUT NOCOPY number
2265 ,x_msg_data OUT NOCOPY varchar2
2266 ,p_lrtv_rec IN lrtv_rec_type) IS
2267 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'delete_row (REC)';
2268 l_return_status varchar2(1);
2269 l_lrtv_rec lrtv_rec_type := p_lrtv_rec;
2270 l_lrttl_rec lrttl_rec_type;
2271 l_lrt_rec lrt_rec_type;
2272
2273 BEGIN
2274 migrate(l_lrtv_rec, l_lrttl_rec);
2275 migrate(l_lrtv_rec, l_lrt_rec);
2276 delete_row(p_init_msg_list
2277 ,l_return_status
2278 ,x_msg_count
2279 ,x_msg_data
2280 ,l_lrttl_rec);
2281
2282 IF (l_return_status = g_ret_sts_unexp_error) THEN
2283 RAISE okl_api.g_exception_unexpected_error;
2284 ELSIF (l_return_status = g_ret_sts_error) THEN
2285 RAISE okl_api.g_exception_error;
2286 END IF;
2287 delete_row(p_init_msg_list
2288 ,l_return_status
2289 ,x_msg_count
2290 ,x_msg_data
2291 ,l_lrt_rec);
2292
2293 IF (l_return_status = g_ret_sts_unexp_error) THEN
2294 RAISE okl_api.g_exception_unexpected_error;
2295 ELSIF (l_return_status = g_ret_sts_error) THEN
2296 RAISE okl_api.g_exception_error;
2297 END IF;
2298 x_return_status := l_return_status;
2299 EXCEPTION
2300 WHEN okl_api.g_exception_error THEN
2301 x_return_status := g_ret_sts_error;
2302 WHEN okl_api.g_exception_unexpected_error THEN
2303 x_return_status := g_ret_sts_unexp_error;
2304 WHEN OTHERS THEN
2305 okl_api.set_message(p_app_name => g_app_name
2306 ,p_msg_name => g_db_error
2307 ,p_token1 => g_prog_name_token
2308 ,p_token1_value => l_api_name
2309 ,p_token2 => g_sqlcode_token
2310 ,p_token2_value => sqlcode
2311 ,p_token3 => g_sqlerrm_token
2312 ,p_token3_value => sqlerrm);
2313 x_return_status := g_ret_sts_unexp_error;
2314 END delete_row;
2315
2316 -------------------
2317 -- delete_row (TBL)
2318 -------------------
2319
2320 PROCEDURE delete_row(p_api_version IN number
2321 ,p_init_msg_list IN varchar2 DEFAULT okc_api.g_false
2322 ,x_return_status OUT NOCOPY varchar2
2323 ,x_msg_count OUT NOCOPY number
2324 ,x_msg_data OUT NOCOPY varchar2
2325 ,p_lrtv_tbl IN lrtv_tbl_type) IS
2326 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'delete_row (TBL)';
2327 l_return_status varchar2(1) := g_ret_sts_success;
2328 i binary_integer;
2329
2330 BEGIN
2331
2332 IF (p_lrtv_tbl.COUNT > 0) THEN
2333 i := p_lrtv_tbl.FIRST;
2334
2335 LOOP
2336 IF p_lrtv_tbl.EXISTS(i) THEN
2337 delete_row(p_api_version => g_api_version
2338 ,p_init_msg_list => g_false
2339 ,x_return_status => l_return_status
2340 ,x_msg_count => x_msg_count
2341 ,x_msg_data => x_msg_data
2342 ,p_lrtv_rec => p_lrtv_tbl(i));
2343 IF l_return_status = g_ret_sts_unexp_error THEN
2344 RAISE okl_api.g_exception_unexpected_error;
2345 ELSIF l_return_status = g_ret_sts_error THEN
2346 RAISE okl_api.g_exception_error;
2347 END IF;
2348 EXIT WHEN(i = p_lrtv_tbl.LAST);
2349 i := p_lrtv_tbl.next(i);
2350 END IF;
2351 END LOOP;
2352
2353 END IF;
2354 x_return_status := l_return_status;
2355 EXCEPTION
2356 WHEN okl_api.g_exception_error THEN
2357 x_return_status := g_ret_sts_error;
2358 WHEN okl_api.g_exception_unexpected_error THEN
2359 x_return_status := g_ret_sts_unexp_error;
2360 WHEN OTHERS THEN
2361 okl_api.set_message(p_app_name => g_app_name
2362 ,p_msg_name => g_db_error
2363 ,p_token1 => g_prog_name_token
2364 ,p_token1_value => l_api_name
2365 ,p_token2 => g_sqlcode_token
2366 ,p_token2_value => sqlcode
2367 ,p_token3 => g_sqlerrm_token
2368 ,p_token3_value => sqlerrm);
2369 x_return_status := g_ret_sts_unexp_error;
2370 END delete_row;
2371
2372 END okl_lrt_pvt;