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