[Home] [Help]
PACKAGE BODY: APPS.OKL_ETH_PVT
Source
1 PACKAGE BODY okl_eth_pvt AS
2 /* $Header: OKLSETHB.pls 120.6 2006/07/13 12:55:30 adagur noship $ */
3
4 g_no_parent_record CONSTANT VARCHAR2(200) := 'OKC_NO_PARENT_RECORD';
5 g_unexpected_error CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
6 g_sqlerrm_token CONSTANT VARCHAR2(200) := 'SQLerrm';
7 g_sqlcode_token CONSTANT VARCHAR2(200) := 'SQLcode';
8 g_exception_halt_validation EXCEPTION;
9
10 PROCEDURE api_copy IS
11
12 BEGIN
13 NULL;
14 END api_copy;
15
16 PROCEDURE change_version IS
17
18 BEGIN
19 NULL;
20 END change_version;
21
22 ---------------------------------------------------------------------------
23 -- PROCEDURE add_language
24 ---------------------------------------------------------------------------
25
26 PROCEDURE add_language IS
27
28 BEGIN
29
30 DELETE FROM OKL_FE_EO_TERMS_ALL_TL t
31 WHERE NOT EXISTS(SELECT NULL
32 FROM OKL_FE_EO_TERMS_ALL_B b
33 WHERE b.end_of_term_id = t.end_of_term_id);
34
35 UPDATE OKL_FE_EO_TERMS_ALL_TL t
36 SET(end_of_term_desc) = (SELECT
37 -- LANGUAGE,
38
39 -- B.LANGUAGE,
40
41 b.end_of_term_desc
42 FROM OKL_FE_EO_TERMS_ALL_TL b
43 WHERE b.end_of_term_id = t.end_of_term_id
44 AND b.language = t.source_lang)
45 WHERE (t.end_of_term_id, t.language) IN(SELECT subt.end_of_term_id ,subt.language
46 FROM OKL_FE_EO_TERMS_ALL_TL subb ,OKL_FE_EO_TERMS_ALL_TL subt
47 WHERE subb.end_of_term_id = subt.end_of_term_id AND subb.language = subt.language AND ( -- SUBB.LANGUAGE <> SUBT.LANGUAGE OR
48 subb.end_of_term_desc <> subt.end_of_term_desc OR (subb.language IS NOT NULL
49 AND subt.language IS NULL)
50 OR (subb.end_of_term_desc IS NULL AND subt.end_of_term_desc IS NOT NULL)));
51
52 INSERT INTO OKL_FE_EO_TERMS_ALL_TL
53 (end_of_term_id
54 ,language
55 ,source_lang
56 ,sfwt_flag
57 ,end_of_term_desc)
58 SELECT b.end_of_term_id
59 ,l.language_code
60 ,b.source_lang
61 ,b.sfwt_flag
62 ,b.end_of_term_desc
63 FROM OKL_FE_EO_TERMS_ALL_TL b
64 ,fnd_languages l
65 WHERE l.installed_flag IN('I', 'B')
66 AND b.language = userenv('LANG')
67 AND NOT EXISTS(SELECT NULL
68 FROM OKL_FE_EO_TERMS_ALL_TL t
69 WHERE t.end_of_term_id = b.end_of_term_id AND t.language = l.language_code);
70
71 END add_language;
72
73
74 PROCEDURE validate_end_of_term_id(x_return_status OUT NOCOPY VARCHAR2
75 ,p_ethv_rec IN okl_ethv_rec) IS
76
77 BEGIN
78
79 -- initialize the return status
80
81 x_return_status := okl_api.g_ret_sts_success;
82
83 -- END_OF_TERM_ID is a required field
84
85 IF (p_ethv_rec.end_of_term_id IS NULL OR p_ethv_rec.end_of_term_id = okl_api.g_miss_num) THEN
86 okl_api.set_message(p_app_name => g_app_name
87 ,p_msg_name => g_required_value
88 ,p_token1 => g_col_name_token
89 ,p_token1_value => 'END_OF_TERM_ID');
90
91 -- notify caller of an error
92
93 x_return_status := okl_api.g_ret_sts_error;
94
95 -- halt further validation of this column
96
97 RAISE g_exception_halt_validation;
98 END IF;
99
100 EXCEPTION
101 WHEN g_exception_halt_validation THEN
102
103 -- no proccessing required. Validation can continue with the next column
104
105 NULL;
106 WHEN OTHERS THEN
107
108 -- store SQL error message on message stack for caller
109
110 okc_api.set_message(p_app_name => g_app_name
111 ,p_msg_name => g_unexpected_error
112 ,p_token1 => g_sqlcode_token
113 ,p_token1_value => sqlcode
114 ,p_token2 => g_sqlerrm_token
115 ,p_token2_value => sqlerrm);
116
117 -- notify caller of an UNEXPECTED error
118
119 x_return_status := okc_api.g_ret_sts_unexp_error;
120 END validate_end_of_term_id;
121
122 PROCEDURE validate_object_version_number(x_return_status OUT NOCOPY VARCHAR2
123 ,p_ethv_rec IN okl_ethv_rec) IS
124
125 BEGIN
126
127 -- initialize the return status
128
129 x_return_status := okl_api.g_ret_sts_success;
130
131 -- object_version_number is a required field
132
133 IF (p_ethv_rec.object_version_number IS NULL OR p_ethv_rec.object_version_number = okl_api.g_miss_num) THEN
134 okl_api.set_message(p_app_name => g_app_name
135 ,p_msg_name => g_required_value
136 ,p_token1 => g_col_name_token
137 ,p_token1_value => 'object_version_number');
138
139 -- notify caller of an error
140
141 x_return_status := okl_api.g_ret_sts_error;
142
143 -- halt further validation of this column
144
145 RAISE g_exception_halt_validation;
146 END IF;
147
148 EXCEPTION
149 WHEN g_exception_halt_validation THEN
150
151 -- no proccessing required. Validation can continue with the next column
152
153 NULL;
154 WHEN OTHERS THEN
155
156 -- store SQL error message on message stack for caller
157
158 okc_api.set_message(p_app_name => g_app_name
159 ,p_msg_name => g_unexpected_error
160 ,p_token1 => g_sqlcode_token
161 ,p_token1_value => sqlcode
162 ,p_token2 => g_sqlerrm_token
163 ,p_token2_value => sqlerrm);
164
165 -- notify caller of an UNEXPECTED error
166
167 x_return_status := okc_api.g_ret_sts_unexp_error;
168 END validate_object_version_number;
169
170 PROCEDURE validate_org_id(x_return_status OUT NOCOPY VARCHAR2
171 ,p_ethv_rec IN okl_ethv_rec) IS
172
173 BEGIN
174
175 -- initialize return status
176
177 x_return_status := okc_api.g_ret_sts_success;
178
179 -- check org id validity using the generic function okl_util.check_org_id()
180
181 x_return_status := okl_util.check_org_id(p_ethv_rec.org_id);
182
183 IF (x_return_status = okc_api.g_ret_sts_error) THEN
184 okc_api.set_message(p_app_name => g_app_name
185 ,p_msg_name => g_invalid_value
186 ,p_token1 => g_col_name_token
187 ,p_token1_value => 'org_id');
188
189 -- notify caller of an error
190
191 RAISE g_exception_halt_validation;
192 ELSIF (x_return_status = okc_api.g_ret_sts_unexp_error) THEN
193
194 -- notify caller of an error
195
196 RAISE g_exception_halt_validation;
197 END IF;
198
199 EXCEPTION
200 WHEN g_exception_halt_validation THEN
201
202 -- No action necessary. Validation can continue to next attribute/column
203
204 NULL;
205 WHEN OTHERS THEN
206
207 -- store SQL error message on message stack for caller
208
209 okl_api.set_message(p_app_name => g_app_name
210 ,p_msg_name => g_unexpected_error
211 ,p_token1 => g_sqlcode_token
212 ,p_token1_value => sqlcode
213 ,p_token2 => g_sqlerrm_token
214 ,p_token2_value => sqlerrm);
215
216 -- notify caller of an UNEXPECTED error
217
218 x_return_status := okc_api.g_ret_sts_unexp_error;
219 END validate_org_id;
220
221 PROCEDURE validate_currency_code(x_return_status OUT NOCOPY VARCHAR2
222 ,p_ethv_rec IN okl_ethv_rec) IS
223
224 BEGIN
225
226 -- initialize return status
227
228 x_return_status := okc_api.g_ret_sts_success;
229
230 -- data is required
231
232 IF (p_ethv_rec.currency_code IS NOT NULL) AND (p_ethv_rec.currency_code <> okc_api.g_miss_char) THEN
233
234 x_return_status := okl_accounting_util.validate_currency_code(p_ethv_rec.currency_code);
235
236 IF (x_return_status <> okl_api.g_true) THEN
237 okc_api.set_message(p_app_name => g_app_name
238 ,p_msg_name => g_invalid_value
239 ,p_token1 => g_col_name_token
240 ,p_token1_value => 'currency_code');
241
242 -- halt further validation of this column
243
244 RAISE g_exception_halt_validation;
245 END IF;
246 END IF;
247
248 EXCEPTION
249 WHEN g_exception_halt_validation THEN
250 x_return_status := okc_api.g_ret_sts_error;
251 WHEN OTHERS THEN
252
253 -- store SQL error message on message stack for caller
254
255 okc_api.set_message(p_app_name => g_app_name
256 ,p_msg_name => g_unexpected_error
257 ,p_token1 => g_sqlcode_token
258 ,p_token1_value => sqlcode
259 ,p_token2 => g_sqlerrm_token
260 ,p_token2_value => sqlerrm);
261
262 -- notify caller of an UNEXPECTED error
263
264 x_return_status := okc_api.g_ret_sts_unexp_error;
265 END validate_currency_code;
266
267 PROCEDURE validate_eot_type_code(x_return_status OUT NOCOPY VARCHAR2
268 ,p_ethv_rec IN okl_ethv_rec) IS
269
270 BEGIN
271
272 -- initialize return status
273
274 x_return_status := okc_api.g_ret_sts_success;
275
276 -- data is required
277
278 IF (p_ethv_rec.eot_type_code IS NULL) OR (p_ethv_rec.eot_type_code = okc_api.g_miss_char) THEN
279 okc_api.set_message(p_app_name => g_app_name
280 ,p_msg_name => g_required_value
281 ,p_token1 => g_col_name_token
282 ,p_token1_value => 'EOT_TYPE_CODE');
283
284 -- notify caller of an error
285
286 x_return_status := okc_api.g_ret_sts_error;
287
288 -- halt further validation of this column
289
290 RAISE g_exception_halt_validation;
291 END IF;
292
293 -- Lookup Code Validation
294
295 x_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_RESIDUAL_TYPES'
296 ,p_lookup_code => p_ethv_rec.eot_type_code);
297
298 IF (x_return_status = okc_api.g_ret_sts_error) THEN
299 okc_api.set_message(p_app_name => g_app_name
300 ,p_msg_name => g_invalid_value
301 ,p_token1 => g_col_name_token
302 ,p_token1_value => 'EOT_TYPE_CODE'); -- notify caller of an error
303 RAISE g_exception_halt_validation;
304 ELSIF (x_return_status = okc_api.g_ret_sts_unexp_error) THEN
305
306 -- notify caller of an error
307
308 x_return_status := okc_api.g_ret_sts_unexp_error;
309 RAISE g_exception_halt_validation;
310 END IF;
311
312 EXCEPTION
313 WHEN g_exception_halt_validation THEN
314
315 -- no processing necessary; validation can continue
316 -- with the next column
317
318 NULL;
319 WHEN OTHERS THEN
320
321 -- store SQL error message on message stack for caller
322
323 okc_api.set_message(p_app_name => g_app_name
324 ,p_msg_name => g_unexpected_error
325 ,p_token1 => g_sqlcode_token
326 ,p_token1_value => sqlcode
327 ,p_token2 => g_sqlerrm_token
328 ,p_token2_value => sqlerrm);
329
330 -- notify caller of an UNEXPECTED error
331
332 x_return_status := okl_api.g_ret_sts_unexp_error;
333 END validate_eot_type_code;
334
335 PROCEDURE validate_product_id(x_return_status OUT NOCOPY VARCHAR2
336 ,p_ethv_rec IN okl_ethv_rec) IS
337
338 CURSOR product_id_exists_csr IS
339 SELECT 'x'
340 FROM okl_products_v
341 WHERE id = p_ethv_rec.product_id;
342 l_dummy_var VARCHAR2(1) := '?';
343
344 BEGIN
345
346 -- initialize return status
347
348 x_return_status := okc_api.g_ret_sts_success;
349
350 -- data is required
351
352 IF (p_ethv_rec.product_id IS NULL) OR (p_ethv_rec.product_id = okc_api.g_miss_num) THEN
353 okc_api.set_message(p_app_name => g_app_name
354 ,p_msg_name => g_required_value
355 ,p_token1 => g_col_name_token
356 ,p_token1_value => 'product_id');
357
358 -- notify caller of an error
359
360 x_return_status := okl_api.g_ret_sts_error;
361
362 -- halt further validation of this column
363
364 RAISE g_exception_halt_validation;
365 END IF;
366
367 -- enforce foreign key
368
369 OPEN product_id_exists_csr;
370 FETCH product_id_exists_csr INTO l_dummy_var ;
371 CLOSE product_id_exists_csr;
372
373 -- if l_dummy_var is still set to default, data was not found
374
375 IF (l_dummy_var = '?') THEN
376 okc_api.set_message(p_app_name => g_app_name
377 ,p_msg_name => g_invalid_value
378 ,p_token1 => g_col_name_token
379 ,p_token1_value => 'product_id');
380
381 -- notify caller of an error
382
383 x_return_status := okc_api.g_ret_sts_error;
384 END IF;
385
386 EXCEPTION
387 WHEN g_exception_halt_validation THEN
388
389 -- no processing necessary; validation can continue
390 -- with the next column
391
392 NULL;
393 WHEN OTHERS THEN
394
395 -- store SQL error message on message stack for caller
396
397 okc_api.set_message(p_app_name => g_app_name
398 ,p_msg_name => g_unexpected_error
399 ,p_token1 => g_sqlcode_token
400 ,p_token1_value => sqlcode
401 ,p_token2 => g_sqlerrm_token
402 ,p_token2_value => sqlerrm);
403
404 -- notify caller of an UNEXPECTED error
405
406 x_return_status := okc_api.g_ret_sts_unexp_error;
407
408 -- verify that cursor was closed
409
410 IF product_id_exists_csr%ISOPEN THEN
411 CLOSE product_id_exists_csr;
412 END IF;
413
414 END validate_product_id;
415
416 PROCEDURE validate_category_type_code(x_return_status OUT NOCOPY VARCHAR2
417 ,p_ethv_rec IN okl_ethv_rec) IS
418
419 BEGIN
420
421 -- initialize return status
422
423 x_return_status := okc_api.g_ret_sts_success;
424
425 -- data is required
426
427 IF (p_ethv_rec.category_type_code IS NULL) OR (p_ethv_rec.category_type_code = okc_api.g_miss_char) THEN
428 okc_api.set_message(p_app_name => g_app_name
429 ,p_msg_name => g_required_value
430 ,p_token1 => g_col_name_token
431 ,p_token1_value => 'CATEGORY_TYPE_CODE');
432
433 -- notify caller of an error
434
435 x_return_status := okl_api.g_ret_sts_error;
436
437 -- halt further validation of this column
438
439 RAISE g_exception_halt_validation;
440 END IF;
441
442 -- Lookup Code Validation
443
444 x_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_SOURCE_TYPES'
445 ,p_lookup_code => p_ethv_rec.category_type_code);
446
447 IF (x_return_status = okc_api.g_ret_sts_error) THEN
448 okc_api.set_message(p_app_name => g_app_name
449 ,p_msg_name => g_invalid_value
450 ,p_token1 => g_col_name_token
451 ,p_token1_value => 'CATEGORY_TYPE_CODE'); -- notify caller of an error
452 RAISE g_exception_halt_validation;
453 ELSIF (x_return_status = okc_api.g_ret_sts_unexp_error) THEN
454
455 -- notify caller of an error
456
457 x_return_status := okc_api.g_ret_sts_unexp_error;
458 RAISE g_exception_halt_validation;
459 END IF;
460
461 EXCEPTION
462 WHEN g_exception_halt_validation THEN
463
464 -- no processing necessary; validation can continue
465 -- with the next column
466
467 NULL;
468 WHEN OTHERS THEN
469
470 -- store SQL error message on message stack for caller
471
472 okc_api.set_message(p_app_name => g_app_name
473 ,p_msg_name => g_unexpected_error
474 ,p_token1 => g_sqlcode_token
475 ,p_token1_value => sqlcode
476 ,p_token2 => g_sqlerrm_token
477 ,p_token2_value => sqlerrm);
478
479 -- notify caller of an UNEXPECTED error
480
481 x_return_status := okc_api.g_ret_sts_unexp_error;
482 END validate_category_type_code;
483
484 PROCEDURE validate_orig_end_of_term_id(x_return_status OUT NOCOPY VARCHAR2
485 ,p_ethv_rec IN okl_ethv_rec) IS
486
487 CURSOR pos_exists_csr IS
488 SELECT 'x'
489 FROM okl_fe_eo_terms_all_b
490 WHERE end_of_term_id = p_ethv_rec.orig_end_of_term_id;
491 l_dummy_var VARCHAR2(1) := '?';
492
493 BEGIN
494
495 -- Initialize the return status to success
496
497 x_return_status := okl_api.g_ret_sts_success;
498
499 IF (p_ethv_rec.orig_end_of_term_id IS NOT NULL AND p_ethv_rec.orig_end_of_term_id <> okl_api.g_miss_num) THEN
500 OPEN pos_exists_csr;
501 FETCH pos_exists_csr INTO l_dummy_var ;
502 CLOSE pos_exists_csr;
503
504 -- if l_dummy_var is still set to default, data was not found
505
506 IF (l_dummy_var = '?') THEN
507 okl_api.set_message(p_app_name => g_app_name
508 ,p_msg_name => g_invalid_value
509 ,p_token1 => g_col_name_token
510 ,p_token1_value => 'ORIG_END_OF_TERM_ID');
511
512 -- notify caller of an error
513
514 x_return_status := okc_api.g_ret_sts_error;
515 RAISE g_exception_halt_validation;
516 END IF;
517 END IF;
518
519 EXCEPTION
520 WHEN g_exception_halt_validation THEN
521
522 -- no processing necessary; validation can continue
523 -- with the next column
524
525 NULL;
526 WHEN OTHERS THEN
527
528 -- store SQL error message on message stack for caller
529
530 okc_api.set_message(p_app_name => g_app_name
531 ,p_msg_name => g_unexpected_error
532 ,p_token1 => g_sqlcode_token
533 ,p_token1_value => sqlcode
534 ,p_token2 => g_sqlerrm_token
535 ,p_token2_value => sqlerrm);
536
537 -- notify caller of an UNEXPECTED error
538
539 x_return_status := okc_api.g_ret_sts_unexp_error;
540
541 -- verify that cursor was closed
542
543 IF pos_exists_csr%ISOPEN THEN
544 CLOSE pos_exists_csr;
545 END IF;
546
547 END validate_orig_end_of_term_id;
548
549 PROCEDURE validate_effective_from_date(x_return_status OUT NOCOPY VARCHAR2
550 ,p_ethv_rec IN okl_ethv_rec) IS
551
552 BEGIN
553
554 -- Initialize the return status to success
555
556 x_return_status := okl_api.g_ret_sts_success;
557
558 IF (p_ethv_rec.effective_from_date IS NULL OR p_ethv_rec.effective_from_date = okl_api.g_miss_date) 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 => 'EFFECTIVE_FROM_DATE');
563
564 -- notify caller of an error
565
566 x_return_status := okl_api.g_ret_sts_error;
567 RAISE g_exception_halt_validation;
568 END IF;
569
570 EXCEPTION
571 WHEN g_exception_halt_validation THEN
572
573 -- no processing necessary; validation can continue
574 -- with the next column
575
576 NULL;
577 WHEN OTHERS THEN
578
579 -- store SQL error message on message stack for caller
580
581 okc_api.set_message(p_app_name => g_app_name
582 ,p_msg_name => g_unexpected_error
583 ,p_token1 => g_sqlcode_token
584 ,p_token1_value => sqlcode
585 ,p_token2 => g_sqlerrm_token
586 ,p_token2_value => sqlerrm);
587
588 -- notify caller of an UNEXPECTED error
589
590 x_return_status := okc_api.g_ret_sts_unexp_error;
591 END validate_effective_from_date;
592
593 PROCEDURE validate_sts_code(x_return_status OUT NOCOPY VARCHAR2
594 ,p_ethv_rec IN okl_ethv_rec) IS
595
596 BEGIN
597
598 -- Initialize the return status to success
599
600 x_return_status := okl_api.g_ret_sts_success;
601
602 -- Column is mandatory
603
604 IF (p_ethv_rec.sts_code IS NULL OR p_ethv_rec.sts_code = okl_api.g_miss_char) THEN
605 okl_api.set_message(p_app_name => g_app_name
606 ,p_msg_name => g_required_value
607 ,p_token1 => g_col_name_token
608 ,p_token1_value => 'sts_code');
609
610 -- notify caller of an error
611
612 x_return_status := okl_api.g_ret_sts_error;
613 RAISE g_exception_halt_validation;
614 END IF;
615
616 -- Lookup Code Validation
617
618 x_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_PRC_STATUS'
619 ,p_lookup_code => p_ethv_rec.sts_code);
620
621 IF (x_return_status = okc_api.g_ret_sts_error) THEN
622 okc_api.set_message(p_app_name => g_app_name
623 ,p_msg_name => g_invalid_value
624 ,p_token1 => g_col_name_token
625 ,p_token1_value => 'sts_code'); -- notify caller of an error
626 RAISE g_exception_halt_validation;
627 ELSIF (x_return_status = okc_api.g_ret_sts_unexp_error) THEN
628
629 -- notify caller of an error
630
631 x_return_status := okc_api.g_ret_sts_unexp_error;
632 RAISE g_exception_halt_validation;
633 END IF;
634
635 EXCEPTION
636 WHEN g_exception_halt_validation THEN
637
638 -- no processing necessary; validation can continue
639 -- with the next column
640
641 NULL;
642 WHEN OTHERS THEN
643
644 -- store SQL error message on message stack for caller
645
646 okc_api.set_message(p_app_name => g_app_name
647 ,p_msg_name => g_unexpected_error
648 ,p_token1 => g_sqlcode_token
649 ,p_token1_value => sqlcode
650 ,p_token2 => g_sqlerrm_token
651 ,p_token2_value => sqlerrm);
652
653 -- notify caller of an UNEXPECTED error
654
655 x_return_status := okc_api.g_ret_sts_unexp_error;
656 END validate_sts_code;
657
658 --------------------------------------------------------------------------------
659 -- Procedure get_rec for OKL_FE_EO_TERMS_ALL_B
660 --------------------------------------------------------------------------------
661
662 FUNCTION get_rec(p_ethb_rec IN okl_ethb_rec
663 ,x_no_data_found OUT NOCOPY BOOLEAN) RETURN okl_ethb_rec IS
664
665 CURSOR posb_pk_csr(p_id IN NUMBER) IS
666 SELECT end_of_term_id
667 ,end_of_term_name
668 ,object_version_number
669 ,org_id
670 ,currency_code
671 ,eot_type_code
672 ,product_id
673 ,category_type_code
674 ,orig_end_of_term_id
675 ,sts_code
676 ,effective_from_date
677 ,effective_to_date
678 ,attribute_category
679 ,attribute1
680 ,attribute2
681 ,attribute3
682 ,attribute4
683 ,attribute5
684 ,attribute6
685 ,attribute7
686 ,attribute8
687 ,attribute9
688 ,attribute10
689 ,attribute11
690 ,attribute12
691 ,attribute13
692 ,attribute14
693 ,attribute15
694 ,created_by
695 ,creation_date
696 ,last_updated_by
697 ,last_update_date
698 ,last_update_login
699 FROM okl_fe_eo_terms_all_b
700 WHERE okl_fe_eo_terms_all_b.end_of_term_id = p_id;
701 l_ethb_pk posb_pk_csr%ROWTYPE;
702 l_ethb_rec okl_ethb_rec;
703
704 BEGIN
705 x_no_data_found := true;
706
707 --Get current data base values
708
709 OPEN posb_pk_csr(p_ethb_rec.end_of_term_id);
710 FETCH posb_pk_csr INTO l_ethb_rec.end_of_term_id
711 ,l_ethb_rec.end_of_term_name
712 ,l_ethb_rec.object_version_number
713 ,l_ethb_rec.org_id
714 ,l_ethb_rec.currency_code
715 ,l_ethb_rec.eot_type_code
716 ,l_ethb_rec.product_id
717 ,l_ethb_rec.category_type_code
718 ,l_ethb_rec.orig_end_of_term_id
719 ,l_ethb_rec.sts_code
720 ,l_ethb_rec.effective_from_date
721 ,l_ethb_rec.effective_to_date
722 ,l_ethb_rec.attribute_category
723 ,l_ethb_rec.attribute1
724 ,l_ethb_rec.attribute2
725 ,l_ethb_rec.attribute3
726 ,l_ethb_rec.attribute4
727 ,l_ethb_rec.attribute5
728 ,l_ethb_rec.attribute6
729 ,l_ethb_rec.attribute7
730 ,l_ethb_rec.attribute8
731 ,l_ethb_rec.attribute9
732 ,l_ethb_rec.attribute10
733 ,l_ethb_rec.attribute11
734 ,l_ethb_rec.attribute12
735 ,l_ethb_rec.attribute13
736 ,l_ethb_rec.attribute14
737 ,l_ethb_rec.attribute15
738 ,l_ethb_rec.created_by
739 ,l_ethb_rec.creation_date
740 ,l_ethb_rec.last_updated_by
741 ,l_ethb_rec.last_update_date
742 ,l_ethb_rec.last_update_login ;
743 x_no_data_found := posb_pk_csr%NOTFOUND;
744
745 CLOSE posb_pk_csr;
746 RETURN(l_ethb_rec);
747 END get_rec;
748
749 FUNCTION get_rec(p_ethb_rec IN okl_ethb_rec) RETURN okl_ethb_rec IS
750 l_row_notfound BOOLEAN := true;
751
752 BEGIN
753 RETURN(get_rec(p_ethb_rec
754 ,l_row_notfound));
755 END get_rec;
756
757 --------------------------------------------------------------------------------
758 -- Procedure get_rec forOKL_FE_EO_TERMS_ALL_TL
759 --------------------------------------------------------------------------------
760
761 FUNCTION get_rec(p_ethtl_rec IN okl_ethtl_rec
762 ,x_no_data_found OUT NOCOPY BOOLEAN) RETURN okl_ethtl_rec IS
763
764 CURSOR postl_pk_csr(p_id IN NUMBER
765 ,p_language IN VARCHAR2) IS
766 SELECT end_of_term_id
767 ,end_of_term_desc
768 ,language
769 ,source_lang
770 ,sfwt_flag
771 ,created_by
772 ,creation_date
773 ,last_updated_by
774 ,last_update_date
775 ,last_update_login
776 FROM okl_fe_eo_terms_all_tl
777 WHERE okl_fe_eo_terms_all_tl.end_of_term_id = p_id
778 AND okl_fe_eo_terms_all_tl.language = p_language;
779 l_ethtl_pk postl_pk_csr%ROWTYPE;
780 l_ethtl_rec okl_ethtl_rec;
781
782 BEGIN
783 x_no_data_found := true;
784
785 --Get current data base values
786
787 OPEN postl_pk_csr(p_ethtl_rec.end_of_term_id
788 ,p_ethtl_rec.language);
789 FETCH postl_pk_csr INTO l_ethtl_rec.end_of_term_id
790 ,l_ethtl_rec.end_of_term_desc
791 ,l_ethtl_rec.language
792 ,l_ethtl_rec.source_lang
793 ,l_ethtl_rec.sfwt_flag
794 ,l_ethtl_rec.created_by
795 ,l_ethtl_rec.creation_date
796 ,l_ethtl_rec.last_updated_by
797 ,l_ethtl_rec.last_update_date
798 ,l_ethtl_rec.last_update_login ;
799 x_no_data_found := postl_pk_csr%NOTFOUND;
800 CLOSE postl_pk_csr;
801 RETURN(l_ethtl_rec);
802 END get_rec;
803
804 FUNCTION get_rec(p_ethtl_rec IN okl_ethtl_rec) RETURN okl_ethtl_rec IS
805 l_row_notfound BOOLEAN := true;
806
807 BEGIN
808 RETURN(get_rec(p_ethtl_rec
809 ,l_row_notfound));
810 END get_rec;
811
812 --------------------------------------------------------------------------------
813 -- Procedure get_rec for OKL_FE_EO_TERMS_V
814 --------------------------------------------------------------------------------
815
816 FUNCTION get_rec(p_ethv_rec IN okl_ethv_rec
817 ,x_no_data_found OUT NOCOPY BOOLEAN) RETURN okl_ethv_rec IS
818
819 CURSOR posv_pk_csr(p_id IN NUMBER) IS
820 SELECT end_of_term_id
821 ,object_version_number
822 ,end_of_term_name
823 ,end_of_term_desc
824 ,org_id
825 ,currency_code
826 ,eot_type_code
827 ,product_id
828 ,category_type_code
829 ,orig_end_of_term_id
830 ,sts_code
831 ,effective_from_date
832 ,effective_to_date
833 ,attribute_category
834 ,attribute1
835 ,attribute2
836 ,attribute3
837 ,attribute4
838 ,attribute5
839 ,attribute6
840 ,attribute7
841 ,attribute8
842 ,attribute9
843 ,attribute10
844 ,attribute11
845 ,attribute12
846 ,attribute13
847 ,attribute14
848 ,attribute15
849 ,created_by
850 ,creation_date
851 ,last_updated_by
852 ,last_update_date
853 ,last_update_login
854 FROM okl_fe_eo_terms_v
855 WHERE okl_fe_eo_terms_v.end_of_term_id = p_id;
856 l_ethv_pk posv_pk_csr%ROWTYPE;
857 l_ethv_rec okl_ethv_rec;
858
859 BEGIN
860 x_no_data_found := true;
861
862 --Get current data base values
863
864 OPEN posv_pk_csr(p_ethv_rec.end_of_term_id);
865 FETCH posv_pk_csr INTO l_ethv_rec.end_of_term_id
866 ,l_ethv_rec.object_version_number
867 ,l_ethv_rec.end_of_term_name
868 ,l_ethv_rec.end_of_term_desc
869 ,l_ethv_rec.org_id
870 ,l_ethv_rec.currency_code
871 ,l_ethv_rec.eot_type_code
872 ,l_ethv_rec.product_id
873 ,l_ethv_rec.category_type_code
874 ,l_ethv_rec.orig_end_of_term_id
875 ,l_ethv_rec.sts_code
876 ,l_ethv_rec.effective_from_date
877 ,l_ethv_rec.effective_to_date
878 ,l_ethv_rec.attribute_category
879 ,l_ethv_rec.attribute1
880 ,l_ethv_rec.attribute2
881 ,l_ethv_rec.attribute3
882 ,l_ethv_rec.attribute4
883 ,l_ethv_rec.attribute5
884 ,l_ethv_rec.attribute6
885 ,l_ethv_rec.attribute7
886 ,l_ethv_rec.attribute8
887 ,l_ethv_rec.attribute9
888 ,l_ethv_rec.attribute10
889 ,l_ethv_rec.attribute11
890 ,l_ethv_rec.attribute12
891 ,l_ethv_rec.attribute13
892 ,l_ethv_rec.attribute14
893 ,l_ethv_rec.attribute15
894 ,l_ethv_rec.created_by
895 ,l_ethv_rec.creation_date
896 ,l_ethv_rec.last_updated_by
897 ,l_ethv_rec.last_update_date
898 ,l_ethv_rec.last_update_login ;
899 x_no_data_found := posv_pk_csr%NOTFOUND;
900 CLOSE posv_pk_csr;
901 RETURN(l_ethv_rec);
902 END get_rec;
903
904 FUNCTION get_rec(p_ethv_rec IN okl_ethv_rec) RETURN okl_ethv_rec IS
905 l_row_notfound BOOLEAN := true;
906
907 BEGIN
908 RETURN(get_rec(p_ethv_rec
909 ,l_row_notfound));
910 END get_rec;
911
912 --------------------------------------------------------------------------------
913 -- Procedure migrate
914 --------------------------------------------------------------------------------
915
916 PROCEDURE migrate(p_from IN okl_ethv_rec
917 ,p_to IN OUT NOCOPY okl_ethb_rec) IS
918
919 BEGIN
920 p_to.end_of_term_id := p_from.end_of_term_id;
921 p_to.end_of_term_name := p_from.end_of_term_name;
922 p_to.object_version_number := p_from.object_version_number;
923 p_to.org_id := p_from.org_id;
924 p_to.currency_code := p_from.currency_code;
925 p_to.eot_type_code := p_from.eot_type_code;
926 p_to.product_id := p_from.product_id;
927 p_to.category_type_code := p_from.category_type_code;
928 p_to.orig_end_of_term_id := p_from.orig_end_of_term_id;
929 p_to.sts_code := p_from.sts_code;
930 p_to.effective_from_date := p_from.effective_from_date;
931 p_to.effective_to_date := p_from.effective_to_date;
932 p_to.attribute_category := p_from.attribute_category;
933 p_to.attribute1 := p_from.attribute1;
934 p_to.attribute2 := p_from.attribute2;
935 p_to.attribute3 := p_from.attribute3;
936 p_to.attribute4 := p_from.attribute4;
937 p_to.attribute5 := p_from.attribute5;
938 p_to.attribute6 := p_from.attribute6;
939 p_to.attribute7 := p_from.attribute7;
940 p_to.attribute8 := p_from.attribute8;
941 p_to.attribute9 := p_from.attribute9;
942 p_to.attribute10 := p_from.attribute10;
943 p_to.attribute11 := p_from.attribute11;
944 p_to.attribute12 := p_from.attribute12;
945 p_to.attribute13 := p_from.attribute13;
946 p_to.attribute14 := p_from.attribute14;
947 p_to.attribute15 := p_from.attribute15;
948 p_to.created_by := p_from.created_by;
949 p_to.creation_date := p_from.creation_date;
950 p_to.last_updated_by := p_from.last_updated_by;
951 p_to.last_update_date := p_from.last_update_date;
952 p_to.last_update_login := p_from.last_update_login;
953 END migrate;
954
955 PROCEDURE migrate(p_from IN okl_ethb_rec
956 ,p_to IN OUT NOCOPY okl_ethv_rec) IS
957
958 BEGIN
959 p_to.end_of_term_id := p_from.end_of_term_id;
960 p_to.end_of_term_name := p_from.end_of_term_name;
961 p_to.object_version_number := p_from.object_version_number;
962 p_to.org_id := p_from.org_id;
963 p_to.currency_code := p_from.currency_code;
964 p_to.eot_type_code := p_from.eot_type_code;
965 p_to.product_id := p_from.product_id;
966 p_to.category_type_code := p_from.category_type_code;
967 p_to.orig_end_of_term_id := p_from.orig_end_of_term_id;
968 p_to.sts_code := p_from.sts_code;
969 p_to.effective_from_date := p_from.effective_from_date;
970 p_to.effective_to_date := p_from.effective_to_date;
971 p_to.attribute_category := p_from.attribute_category;
972 p_to.attribute1 := p_from.attribute1;
973 p_to.attribute2 := p_from.attribute2;
974 p_to.attribute3 := p_from.attribute3;
975 p_to.attribute4 := p_from.attribute4;
976 p_to.attribute5 := p_from.attribute5;
977 p_to.attribute6 := p_from.attribute6;
978 p_to.attribute7 := p_from.attribute7;
979 p_to.attribute8 := p_from.attribute8;
980 p_to.attribute9 := p_from.attribute9;
981 p_to.attribute10 := p_from.attribute10;
982 p_to.attribute11 := p_from.attribute11;
983 p_to.attribute12 := p_from.attribute12;
984 p_to.attribute13 := p_from.attribute13;
985 p_to.attribute14 := p_from.attribute14;
986 p_to.attribute15 := p_from.attribute15;
987 p_to.created_by := p_from.created_by;
988 p_to.creation_date := p_from.creation_date;
989 p_to.last_updated_by := p_from.last_updated_by;
990 p_to.last_update_date := p_from.last_update_date;
991 p_to.last_update_login := p_from.last_update_login;
992 END migrate;
993
994 PROCEDURE migrate(p_from IN okl_ethv_rec
995 ,p_to IN OUT NOCOPY okl_ethtl_rec) IS
996
997 BEGIN
998 p_to.end_of_term_id := p_from.end_of_term_id;
999 p_to.end_of_term_desc := p_from.end_of_term_desc;
1000 p_to.created_by := p_from.created_by;
1001 p_to.creation_date := p_from.creation_date;
1002 p_to.last_updated_by := p_from.last_updated_by;
1003 p_to.last_update_date := p_from.last_update_date;
1004 p_to.last_update_login := p_from.last_update_login;
1005 END migrate;
1006
1007 PROCEDURE migrate(p_from IN okl_ethtl_rec
1008 ,p_to IN OUT NOCOPY okl_ethv_rec) IS
1009
1010 BEGIN
1011 p_to.end_of_term_id := p_from.end_of_term_id;
1012 p_to.end_of_term_desc := p_from.end_of_term_desc;
1013 p_to.created_by := p_from.created_by;
1014 p_to.creation_date := p_from.creation_date;
1015 p_to.last_updated_by := p_from.last_updated_by;
1016 p_to.last_update_date := p_from.last_update_date;
1017 p_to.last_update_login := p_from.last_update_login;
1018 END migrate;
1019
1020 FUNCTION null_out_defaults(p_ethv_rec IN okl_ethv_rec) RETURN okl_ethv_rec IS
1021 l_ethv_rec okl_ethv_rec := p_ethv_rec;
1022
1023 BEGIN
1024
1025 IF (l_ethv_rec.end_of_term_id = okl_api.g_miss_num) THEN
1026 l_ethv_rec.end_of_term_id := NULL;
1027 END IF;
1028
1029 IF (l_ethv_rec.object_version_number = okl_api.g_miss_num) THEN
1030 l_ethv_rec.object_version_number := NULL;
1031 END IF;
1032
1033 IF (l_ethv_rec.end_of_term_name = okl_api.g_miss_char) THEN
1034 l_ethv_rec.end_of_term_name := NULL;
1035 END IF;
1036
1037 IF (l_ethv_rec.end_of_term_desc = okl_api.g_miss_char) THEN
1038 l_ethv_rec.end_of_term_desc := NULL;
1039 END IF;
1040
1041 IF (l_ethv_rec.org_id = okl_api.g_miss_num) THEN
1042 l_ethv_rec.org_id := NULL;
1043 END IF;
1044
1045 IF (l_ethv_rec.currency_code = okl_api.g_miss_char) THEN
1046 l_ethv_rec.currency_code := NULL;
1047 END IF;
1048
1049 IF (l_ethv_rec.eot_type_code = okl_api.g_miss_char) THEN
1050 l_ethv_rec.eot_type_code := NULL;
1051 END IF;
1052
1053 IF (l_ethv_rec.product_id = okl_api.g_miss_num) THEN
1054 l_ethv_rec.product_id := NULL;
1055 END IF;
1056
1057 IF (l_ethv_rec.category_type_code = okl_api.g_miss_char) THEN
1058 l_ethv_rec.category_type_code := NULL;
1059 END IF;
1060
1061 IF (l_ethv_rec.orig_end_of_term_id = okl_api.g_miss_num) THEN
1062 l_ethv_rec.orig_end_of_term_id := NULL;
1063 END IF;
1064
1065 IF (l_ethv_rec.sts_code = okl_api.g_miss_char) THEN
1066 l_ethv_rec.sts_code := NULL;
1067 END IF;
1068
1069 IF (l_ethv_rec.effective_from_date = okl_api.g_miss_date) THEN
1070 l_ethv_rec.effective_from_date := NULL;
1071 END IF;
1072
1073 IF (l_ethv_rec.effective_to_date = okl_api.g_miss_date) THEN
1074 l_ethv_rec.effective_to_date := NULL;
1075 END IF;
1076
1077 IF (l_ethv_rec.attribute_category = okl_api.g_miss_char) THEN
1078 l_ethv_rec.attribute_category := NULL;
1079 END IF;
1080
1081 IF (l_ethv_rec.attribute1 = okl_api.g_miss_char) THEN
1082 l_ethv_rec.attribute1 := NULL;
1083 END IF;
1084
1085 IF (l_ethv_rec.attribute2 = okl_api.g_miss_char) THEN
1086 l_ethv_rec.attribute2 := NULL;
1087 END IF;
1088
1089 IF (l_ethv_rec.attribute3 = okl_api.g_miss_char) THEN
1090 l_ethv_rec.attribute3 := NULL;
1091 END IF;
1092
1093 IF (l_ethv_rec.attribute4 = okl_api.g_miss_char) THEN
1094 l_ethv_rec.attribute4 := NULL;
1095 END IF;
1096
1097 IF (l_ethv_rec.attribute5 = okl_api.g_miss_char) THEN
1098 l_ethv_rec.attribute5 := NULL;
1099 END IF;
1100
1101 IF (l_ethv_rec.attribute6 = okl_api.g_miss_char) THEN
1102 l_ethv_rec.attribute6 := NULL;
1103 END IF;
1104
1105 IF (l_ethv_rec.attribute7 = okl_api.g_miss_char) THEN
1106 l_ethv_rec.attribute7 := NULL;
1107 END IF;
1108
1109 IF (l_ethv_rec.attribute8 = okl_api.g_miss_char) THEN
1110 l_ethv_rec.attribute8 := NULL;
1111 END IF;
1112
1113 IF (l_ethv_rec.attribute9 = okl_api.g_miss_char) THEN
1114 l_ethv_rec.attribute9 := NULL;
1115 END IF;
1116
1117 IF (l_ethv_rec.attribute10 = okl_api.g_miss_char) THEN
1118 l_ethv_rec.attribute10 := NULL;
1119 END IF;
1120
1121 IF (l_ethv_rec.attribute11 = okl_api.g_miss_char) THEN
1122 l_ethv_rec.attribute11 := NULL;
1123 END IF;
1124
1125 IF (l_ethv_rec.attribute12 = okl_api.g_miss_char) THEN
1126 l_ethv_rec.attribute12 := NULL;
1127 END IF;
1128
1129 IF (l_ethv_rec.attribute13 = okl_api.g_miss_char) THEN
1130 l_ethv_rec.attribute13 := NULL;
1131 END IF;
1132
1133 IF (l_ethv_rec.attribute14 = okl_api.g_miss_char) THEN
1134 l_ethv_rec.attribute14 := NULL;
1135 END IF;
1136
1137 IF (l_ethv_rec.attribute15 = okl_api.g_miss_char) THEN
1138 l_ethv_rec.attribute15 := NULL;
1139 END IF;
1140
1141 IF (l_ethv_rec.created_by = okl_api.g_miss_num) THEN
1142 l_ethv_rec.created_by := NULL;
1143 END IF;
1144
1145 IF (l_ethv_rec.creation_date = okl_api.g_miss_date) THEN
1146 l_ethv_rec.creation_date := NULL;
1147 END IF;
1148
1149 IF (l_ethv_rec.last_updated_by = okl_api.g_miss_num) THEN
1150 l_ethv_rec.last_updated_by := NULL;
1151 END IF;
1152
1153 IF (l_ethv_rec.last_update_date = okl_api.g_miss_date) THEN
1154 l_ethv_rec.last_update_date := NULL;
1155 END IF;
1156
1157 IF (l_ethv_rec.last_update_login = okl_api.g_miss_num) THEN
1158 l_ethv_rec.last_update_login := NULL;
1159 END IF;
1160 RETURN(l_ethv_rec);
1161 END null_out_defaults;
1162
1163 FUNCTION get_seq_id RETURN NUMBER IS
1164
1165 BEGIN
1166 RETURN(okc_p_util.raw_to_number(sys_guid()));
1167 END get_seq_id;
1168
1169 FUNCTION validate_attributes(p_ethv_rec IN okl_ethv_rec) RETURN VARCHAR2 IS
1170 l_return_status VARCHAR2(1) := okc_api.g_ret_sts_success;
1171 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1172
1173 BEGIN
1174
1175 -- validate the id
1176
1177 validate_end_of_term_id(x_return_status => l_return_status
1178 ,p_ethv_rec => p_ethv_rec);
1179
1180 -- store the highest degree of error
1181
1182 IF (l_return_status <> okc_api.g_ret_sts_success) THEN
1183 IF (x_return_status <> okc_api.g_ret_sts_unexp_error) THEN
1184 x_return_status := l_return_status;
1185 END IF;
1186 END IF;
1187 validate_object_version_number(x_return_status => l_return_status
1188 ,p_ethv_rec => p_ethv_rec);
1189
1190 -- store the highest degree of error
1191
1192 IF (l_return_status <> okc_api.g_ret_sts_success) THEN
1193 IF (x_return_status <> okc_api.g_ret_sts_unexp_error) THEN
1194 x_return_status := l_return_status;
1195 END IF;
1196 END IF;
1197 validate_org_id(x_return_status => l_return_status
1198 ,p_ethv_rec => p_ethv_rec);
1199
1200 -- store the highest degree of error
1201
1202 IF (l_return_status <> okc_api.g_ret_sts_success) THEN
1203 IF (x_return_status <> okc_api.g_ret_sts_unexp_error) THEN
1204 x_return_status := l_return_status;
1205 END IF;
1206 END IF;
1207 validate_currency_code(x_return_status => l_return_status
1208 ,p_ethv_rec => p_ethv_rec);
1209
1210 -- store the highest degree of error
1211
1212 IF (l_return_status <> okc_api.g_ret_sts_success) THEN
1213 IF (x_return_status <> okc_api.g_ret_sts_unexp_error) THEN
1214 x_return_status := l_return_status;
1215 END IF;
1216 END IF;
1217 validate_eot_type_code(x_return_status => l_return_status
1218 ,p_ethv_rec => p_ethv_rec);
1219
1220 -- store the highest degree of error
1221
1222 IF (l_return_status <> okc_api.g_ret_sts_success) THEN
1223 IF (x_return_status <> okc_api.g_ret_sts_unexp_error) THEN
1224 x_return_status := l_return_status;
1225 END IF;
1226 END IF;
1227 validate_product_id(x_return_status => l_return_status
1228 ,p_ethv_rec => p_ethv_rec);
1229
1230 -- store the highest degree of error
1231
1232 IF (l_return_status <> okc_api.g_ret_sts_success) THEN
1233 IF (x_return_status <> okc_api.g_ret_sts_unexp_error) THEN
1234 x_return_status := l_return_status;
1235 END IF;
1236 END IF;
1237 validate_category_type_code(x_return_status => l_return_status
1238 ,p_ethv_rec => p_ethv_rec);
1239
1240 -- store the highest degree of error
1241
1242 IF (l_return_status <> okc_api.g_ret_sts_success) THEN
1243 IF (x_return_status <> okc_api.g_ret_sts_unexp_error) THEN
1244 x_return_status := l_return_status;
1245 END IF;
1246 END IF;
1247 validate_orig_end_of_term_id(x_return_status => l_return_status
1248 ,p_ethv_rec => p_ethv_rec);
1249
1250 -- store the highest degree of error
1251
1252 IF (l_return_status <> okc_api.g_ret_sts_success) THEN
1253 IF (x_return_status <> okc_api.g_ret_sts_unexp_error) THEN
1254 x_return_status := l_return_status;
1255 END IF;
1256 END IF;
1257 validate_sts_code(x_return_status => l_return_status
1258 ,p_ethv_rec => p_ethv_rec);
1259
1260 -- store the highest degree of error
1261
1262 IF (l_return_status <> okc_api.g_ret_sts_success) THEN
1263 IF (x_return_status <> okc_api.g_ret_sts_unexp_error) THEN
1264 x_return_status := l_return_status;
1265 END IF;
1266 END IF;
1267 validate_effective_from_date(x_return_status => l_return_status
1268 ,p_ethv_rec => p_ethv_rec);
1269
1270 -- store the highest degree of error
1271
1272 IF (l_return_status <> okc_api.g_ret_sts_success) THEN
1273 IF (x_return_status <> okc_api.g_ret_sts_unexp_error) THEN
1274 x_return_status := l_return_status;
1275 END IF;
1276 END IF;
1277 RETURN(x_return_status);
1278 END validate_attributes;
1279
1280 FUNCTION validate_record(p_ethv_rec IN okl_ethv_rec) RETURN VARCHAR2 IS
1281 l_return_status VARCHAR2(1) := okc_api.g_ret_sts_success;
1282 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1283
1284 BEGIN
1285 IF (p_ethv_rec.effective_to_date IS NOT NULL) THEN
1286 IF (p_ethv_rec.effective_from_date > p_ethv_rec.effective_to_date) THEN
1287 okl_api.set_message(p_app_name => g_app_name
1288 ,p_msg_name => 'OKL_INVALID_EFFECTIVE_TO');
1289 x_return_status := okl_api.g_ret_sts_error;
1290 END IF;
1291 END IF;
1292 RETURN(x_return_status);
1293 END validate_record;
1294
1295 PROCEDURE lock_row(p_init_msg_list IN VARCHAR2
1296 ,x_return_status OUT NOCOPY VARCHAR2
1297 ,x_msg_count OUT NOCOPY NUMBER
1298 ,x_msg_data OUT NOCOPY VARCHAR2
1299 ,p_ethb_rec IN okl_ethb_rec) IS
1300 e_resource_busy EXCEPTION;
1301
1302 PRAGMA EXCEPTION_INIT(e_resource_busy, - 00054);
1303
1304 CURSOR lock_csr(p_ethb_rec IN okl_ethb_rec) IS
1305 SELECT object_version_number
1306 FROM okl_fe_eo_terms_all_b
1307 WHERE end_of_term_id = p_ethb_rec.end_of_term_id
1308 AND object_version_number = p_ethb_rec.object_version_number
1309 FOR UPDATE OF object_version_number NOWAIT;
1310
1311 CURSOR lchk_csr(p_ethb_rec IN okl_ethb_rec) IS
1312 SELECT object_version_number
1313 FROM okl_fe_eo_terms_all_b
1314 WHERE end_of_term_id = p_ethb_rec.end_of_term_id;
1315 l_api_version CONSTANT NUMBER := 1;
1316 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1317 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1318 l_object_version_number okl_fe_resi_cat_all_b.object_version_number%TYPE;
1319 lc_object_version_number okl_fe_resi_cat_all_b.object_version_number%TYPE;
1320 l_row_notfound BOOLEAN := false;
1321 lc_row_notfound BOOLEAN := false;
1322
1323 BEGIN
1324 l_return_status := okl_api.start_activity(l_api_name
1325 ,p_init_msg_list
1326 ,'_PVT'
1327 ,x_return_status);
1328
1329 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1330 RAISE okl_api.g_exception_unexpected_error;
1331 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1332 RAISE okl_api.g_exception_error;
1333 END IF;
1334
1335 BEGIN
1336 OPEN lock_csr(p_ethb_rec);
1337 FETCH lock_csr INTO l_object_version_number ;
1338 l_row_notfound := lock_csr%NOTFOUND;
1339 CLOSE lock_csr;
1340 EXCEPTION
1341 WHEN e_resource_busy THEN
1342
1343 IF (lock_csr%ISOPEN) THEN
1344 CLOSE lock_csr;
1345 END IF;
1346 okl_api.set_message(g_fnd_app
1347 ,g_form_unable_to_reserve_rec);
1348 RAISE app_exceptions.record_lock_exception;
1349 END;
1350
1351 IF (l_row_notfound) THEN
1352 OPEN lchk_csr(p_ethb_rec);
1353 FETCH lchk_csr INTO lc_object_version_number ;
1354 lc_row_notfound := lchk_csr%NOTFOUND;
1355 CLOSE lchk_csr;
1356 END IF;
1357
1358 IF (lc_row_notfound) THEN
1359 okl_api.set_message(g_fnd_app
1360 ,g_form_record_deleted);
1361 RAISE okl_api.g_exception_error;
1362 ELSIF lc_object_version_number > p_ethb_rec.object_version_number THEN
1363 okl_api.set_message(g_fnd_app
1364 ,g_form_record_changed);
1365 RAISE okl_api.g_exception_error;
1366 ELSIF lc_object_version_number <> p_ethb_rec.object_version_number THEN
1367 okl_api.set_message(g_fnd_app
1368 ,g_form_record_changed);
1369 RAISE okl_api.g_exception_error;
1370 ELSIF lc_object_version_number = - 1 THEN
1371 okl_api.set_message(g_app_name
1372 ,g_record_logically_deleted);
1373 RAISE okl_api.g_exception_error;
1374 END IF;
1375 okl_api.end_activity(x_msg_count
1376 ,x_msg_data);
1377 EXCEPTION
1378 WHEN g_exception_halt_validation THEN
1379 x_return_status := okl_api.handle_exceptions(l_api_name
1380 ,g_pkg_name
1381 ,'OKL_API.G_RET_STS_ERROR'
1382 ,x_msg_count
1383 ,x_msg_data
1384 ,'_PVT');
1385 WHEN okl_api.g_exception_error THEN
1386 x_return_status := okl_api.handle_exceptions(l_api_name
1387 ,g_pkg_name
1388 ,'OKL_API.G_RET_STS_ERROR'
1389 ,x_msg_count
1390 ,x_msg_data
1391 ,'_PVT');
1392 WHEN okl_api.g_exception_unexpected_error THEN
1393 x_return_status := okl_api.handle_exceptions(l_api_name
1394 ,g_pkg_name
1395 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1396 ,x_msg_count
1397 ,x_msg_data
1398 ,'_PVT');
1399 WHEN OTHERS THEN
1400 x_return_status := okl_api.handle_exceptions(l_api_name
1401 ,g_pkg_name
1402 ,'OTHERS'
1403 ,x_msg_count
1404 ,x_msg_data
1405 ,'_PVT');
1406 END lock_row;
1407
1408 ----------------------------------------
1409 -- lock_row_tl --
1410 ----------------------------------------
1411
1412 PROCEDURE lock_row(p_init_msg_list IN VARCHAR2
1413 ,x_return_status OUT NOCOPY VARCHAR2
1414 ,x_msg_count OUT NOCOPY NUMBER
1415 ,x_msg_data OUT NOCOPY VARCHAR2
1416 ,p_ethtl_rec IN okl_ethtl_rec) IS
1417 e_resource_busy EXCEPTION;
1418
1419 PRAGMA EXCEPTION_INIT(e_resource_busy, - 00054);
1420
1421 CURSOR lock_csr(p_ethtl_rec IN okl_ethtl_rec) IS
1422 SELECT *
1423 FROM okl_fe_eo_terms_all_tl
1424 WHERE end_of_term_id = p_ethtl_rec.end_of_term_id
1425 FOR UPDATE NOWAIT;
1426 l_api_version CONSTANT NUMBER := 1;
1427 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
1428 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1429 l_lock_var lock_csr%ROWTYPE;
1430 l_row_notfound BOOLEAN := false;
1431 lc_row_notfound BOOLEAN := false;
1432
1433 BEGIN
1434 l_return_status := okl_api.start_activity(l_api_name
1435 ,p_init_msg_list
1436 ,'_PVT'
1437 ,x_return_status);
1438
1439 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1440 RAISE okl_api.g_exception_unexpected_error;
1441 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1442 RAISE okl_api.g_exception_error;
1443 END IF;
1444
1445 BEGIN
1446 OPEN lock_csr(p_ethtl_rec);
1447 FETCH lock_csr INTO l_lock_var ;
1448 l_row_notfound := lock_csr%NOTFOUND;
1449 CLOSE lock_csr;
1450 EXCEPTION
1451 WHEN e_resource_busy THEN
1452
1453 IF (lock_csr%ISOPEN) THEN
1454 CLOSE lock_csr;
1455 END IF;
1456 okl_api.set_message(g_fnd_app
1457 ,g_form_unable_to_reserve_rec);
1458 RAISE app_exceptions.record_lock_exception;
1459 END;
1460
1461 IF (l_row_notfound) THEN
1462 okl_api.set_message(g_fnd_app
1463 ,g_form_record_deleted);
1464 RAISE okl_api.g_exception_error;
1465 END IF;
1466 okl_api.end_activity(x_msg_count
1467 ,x_msg_data);
1468 EXCEPTION
1469 WHEN g_exception_halt_validation THEN
1470 x_return_status := okl_api.handle_exceptions(l_api_name
1471 ,g_pkg_name
1472 ,'OKL_API.G_RET_STS_ERROR'
1473 ,x_msg_count
1474 ,x_msg_data
1475 ,'_PVT');
1476 WHEN okl_api.g_exception_error THEN
1477 x_return_status := okl_api.handle_exceptions(l_api_name
1478 ,g_pkg_name
1479 ,'OKL_API.G_RET_STS_ERROR'
1480 ,x_msg_count
1481 ,x_msg_data
1482 ,'_PVT');
1483 WHEN okl_api.g_exception_unexpected_error THEN
1484 x_return_status := okl_api.handle_exceptions(l_api_name
1485 ,g_pkg_name
1486 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1487 ,x_msg_count
1488 ,x_msg_data
1489 ,'_PVT');
1490 WHEN OTHERS THEN
1491 x_return_status := okl_api.handle_exceptions(l_api_name
1492 ,g_pkg_name
1493 ,'OTHERS'
1494 ,x_msg_count
1495 ,x_msg_data
1496 ,'_PVT');
1497 END lock_row;
1498
1499 ---------------------------------------
1500 -- lock_row_v --
1501 ---------------------------------------
1502
1503 PROCEDURE lock_row(p_api_version IN NUMBER
1504 ,p_init_msg_list IN VARCHAR2
1505 ,x_return_status OUT NOCOPY VARCHAR2
1506 ,x_msg_count OUT NOCOPY NUMBER
1507 ,x_msg_data OUT NOCOPY VARCHAR2
1508 ,p_ethv_rec IN okl_ethv_rec) IS
1509 l_api_version CONSTANT NUMBER := 1;
1510 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1511 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1512 l_ethb_rec okl_ethb_rec;
1513 l_ethtl_rec okl_ethtl_rec;
1514
1515 BEGIN
1516 l_return_status := okl_api.start_activity(l_api_name
1517 ,g_pkg_name
1518 ,p_init_msg_list
1519 ,l_api_version
1520 ,p_api_version
1521 ,'_PVT'
1522 ,x_return_status);
1523
1524 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1525 RAISE okl_api.g_exception_unexpected_error;
1526 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1527 RAISE okl_api.g_exception_error;
1528 END IF;
1529
1530 --------------------------------------
1531 -- Move VIEW record to "Child" records
1532 --------------------------------------
1533
1534 migrate(p_ethv_rec
1535 ,l_ethb_rec);
1536 migrate(p_ethv_rec
1537 ,l_ethtl_rec);
1538
1539 --------------------------------------------
1540 -- Call the LOCK_ROW for each child record
1541 --------------------------------------------
1542
1543 lock_row(p_init_msg_list
1544 ,x_return_status
1545 ,x_msg_count
1546 ,x_msg_data
1547 ,l_ethb_rec);
1548
1549 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1550 RAISE okl_api.g_exception_unexpected_error;
1551 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1552 RAISE okl_api.g_exception_error;
1553 END IF;
1554 lock_row(p_init_msg_list
1555 ,x_return_status
1556 ,x_msg_count
1557 ,x_msg_data
1558 ,l_ethtl_rec);
1559
1560 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1561 RAISE okl_api.g_exception_unexpected_error;
1562 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1563 RAISE okl_api.g_exception_error;
1564 END IF;
1565 okl_api.end_activity(x_msg_count
1566 ,x_msg_data);
1567 EXCEPTION
1568 WHEN g_exception_halt_validation THEN
1569 x_return_status := okl_api.handle_exceptions(l_api_name
1570 ,g_pkg_name
1571 ,'OKL_API.G_RET_STS_ERROR'
1572 ,x_msg_count
1573 ,x_msg_data
1574 ,'_PVT');
1575 WHEN okl_api.g_exception_error THEN
1576 x_return_status := okl_api.handle_exceptions(l_api_name
1577 ,g_pkg_name
1578 ,'OKL_API.G_RET_STS_ERROR'
1579 ,x_msg_count
1580 ,x_msg_data
1581 ,'_PVT');
1582 WHEN okl_api.g_exception_unexpected_error THEN
1583 x_return_status := okl_api.handle_exceptions(l_api_name
1584 ,g_pkg_name
1585 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1586 ,x_msg_count
1587 ,x_msg_data
1588 ,'_PVT');
1589 WHEN OTHERS THEN
1590 x_return_status := okl_api.handle_exceptions(l_api_name
1591 ,g_pkg_name
1592 ,'OTHERS'
1593 ,x_msg_count
1594 ,x_msg_data
1595 ,'_PVT');
1596 END lock_row;
1597
1598 --------------------------------------
1599 -- PL/SQL TBL lock_row_tbl --
1600 --------------------------------------
1601
1602 PROCEDURE lock_row(p_api_version IN NUMBER
1603 ,p_init_msg_list IN VARCHAR2
1604 ,x_return_status OUT NOCOPY VARCHAR2
1605 ,x_msg_count OUT NOCOPY NUMBER
1606 ,x_msg_data OUT NOCOPY VARCHAR2
1607 ,p_ethv_tbl IN okl_ethv_tbl) IS
1608 l_api_version CONSTANT NUMBER := 1;
1609 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1610 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1611
1612 -- Begin Post-Generation Change
1613 -- overall error status
1614
1615 l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1616
1617 -- End Post-Generation Change
1618
1619 i NUMBER := 0;
1620
1621 BEGIN
1622 okl_api.init_msg_list(p_init_msg_list);
1623
1624 -- Make sure PL/SQL table has records in it before passing
1625
1626 IF (p_ethv_tbl.COUNT > 0) THEN
1627 i := p_ethv_tbl.FIRST;
1628
1629 LOOP
1630 lock_row(p_api_version => p_api_version
1631 ,p_init_msg_list => okl_api.g_false
1632 ,x_return_status => x_return_status
1633 ,x_msg_count => x_msg_count
1634 ,x_msg_data => x_msg_data
1635 ,p_ethv_rec => p_ethv_tbl(i));
1636
1637 -- Begin Post-Generation Change
1638 -- store the highest degree of error
1639
1640 IF x_return_status <> okl_api.g_ret_sts_success THEN
1641 IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1642 l_overall_status := x_return_status;
1643 END IF;
1644 END IF;
1645
1646 -- End Post-Generation Change
1647
1648 EXIT WHEN(i = p_ethv_tbl.LAST);
1649 i := p_ethv_tbl.next(i);
1650 END LOOP;
1651
1652 -- Begin Post-Generation Change
1653 -- return overall status
1654
1655 x_return_status := l_overall_status;
1656
1657 -- End Post-Generation Change
1658
1659 END IF;
1660
1661 EXCEPTION
1662 WHEN g_exception_halt_validation THEN
1663 x_return_status := okl_api.handle_exceptions(l_api_name
1664 ,g_pkg_name
1665 ,'OKL_API.G_RET_STS_ERROR'
1666 ,x_msg_count
1667 ,x_msg_data
1668 ,'_PVT');
1669 WHEN okl_api.g_exception_error THEN
1670 x_return_status := okl_api.handle_exceptions(l_api_name
1671 ,g_pkg_name
1672 ,'OKL_API.G_RET_STS_ERROR'
1673 ,x_msg_count
1674 ,x_msg_data
1675 ,'_PVT');
1676 WHEN okl_api.g_exception_unexpected_error THEN
1677 x_return_status := okl_api.handle_exceptions(l_api_name
1678 ,g_pkg_name
1679 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1680 ,x_msg_count
1681 ,x_msg_data
1682 ,'_PVT');
1683 WHEN OTHERS THEN
1684 x_return_status := okl_api.handle_exceptions(l_api_name
1685 ,g_pkg_name
1686 ,'OTHERS'
1687 ,x_msg_count
1688 ,x_msg_data
1689 ,'_PVT');
1690 END lock_row;
1691
1692 --------------------------------------------------------------------------------
1693 -- Procedure insert_row_b
1694 --------------------------------------------------------------------------------
1695
1696 PROCEDURE insert_row(p_api_version IN NUMBER
1697 ,p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false
1698 ,x_return_status OUT NOCOPY VARCHAR2
1699 ,x_msg_count OUT NOCOPY NUMBER
1700 ,x_msg_data OUT NOCOPY VARCHAR2
1701 ,p_ethb_rec IN okl_ethb_rec
1702 ,x_ethb_rec OUT NOCOPY okl_ethb_rec) IS
1703 l_api_version CONSTANT NUMBER := 1;
1704 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
1705 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1706 l_ethb_rec okl_ethb_rec := p_ethb_rec;
1707
1708 FUNCTION set_attributes(p_ethb_rec IN okl_ethb_rec
1709 ,x_ethb_rec OUT NOCOPY okl_ethb_rec) RETURN VARCHAR2 IS
1710 l_return_status VARCHAR2(1) := okc_api.g_ret_sts_success;
1711
1712 BEGIN
1713 x_ethb_rec := p_ethb_rec;
1714 RETURN(l_return_status);
1715 END set_attributes;
1716
1717 BEGIN
1718 l_return_status := okc_api.start_activity(l_api_name
1719 ,g_pkg_name
1720 ,p_init_msg_list
1721 ,l_api_version
1722 ,p_api_version
1723 ,'_PVT'
1724 ,x_return_status);
1725
1726 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1727 RAISE okc_api.g_exception_unexpected_error;
1728 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1729 RAISE okc_api.g_exception_error;
1730 END IF;
1731
1732 --Setting Item Attributes
1733
1734 l_return_status := set_attributes(p_ethb_rec
1735 ,l_ethb_rec);
1736
1737 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1738 RAISE okc_api.g_exception_unexpected_error;
1739 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1740 RAISE okc_api.g_exception_error;
1741 END IF;
1742
1743 INSERT INTO okl_fe_eo_terms_all_b
1744 (end_of_term_id
1745 ,end_of_term_name
1746 ,object_version_number
1747 ,org_id
1748 ,currency_code
1749 ,eot_type_code
1750 ,product_id
1751 ,category_type_code
1752 ,orig_end_of_term_id
1753 ,sts_code
1754 ,effective_from_date
1755 ,effective_to_date
1756 ,attribute_category
1757 ,attribute1
1758 ,attribute2
1759 ,attribute3
1760 ,attribute4
1761 ,attribute5
1762 ,attribute6
1763 ,attribute7
1764 ,attribute8
1765 ,attribute9
1766 ,attribute10
1767 ,attribute11
1768 ,attribute12
1769 ,attribute13
1770 ,attribute14
1771 ,attribute15
1772 ,created_by
1773 ,creation_date
1774 ,last_updated_by
1775 ,last_update_date
1776 ,last_update_login)
1777 VALUES (l_ethb_rec.end_of_term_id
1778 ,l_ethb_rec.end_of_term_name
1779 ,l_ethb_rec.object_version_number
1780 ,l_ethb_rec.org_id
1781 ,l_ethb_rec.currency_code
1782 ,l_ethb_rec.eot_type_code
1783 ,l_ethb_rec.product_id
1784 ,l_ethb_rec.category_type_code
1785 ,l_ethb_rec.orig_end_of_term_id
1786 ,l_ethb_rec.sts_code
1787 ,l_ethb_rec.effective_from_date
1788 ,l_ethb_rec.effective_to_date
1789 ,l_ethb_rec.attribute_category
1790 ,l_ethb_rec.attribute1
1791 ,l_ethb_rec.attribute2
1792 ,l_ethb_rec.attribute3
1793 ,l_ethb_rec.attribute4
1794 ,l_ethb_rec.attribute5
1795 ,l_ethb_rec.attribute6
1796 ,l_ethb_rec.attribute7
1797 ,l_ethb_rec.attribute8
1798 ,l_ethb_rec.attribute9
1799 ,l_ethb_rec.attribute10
1800 ,l_ethb_rec.attribute11
1801 ,l_ethb_rec.attribute12
1802 ,l_ethb_rec.attribute13
1803 ,l_ethb_rec.attribute14
1804 ,l_ethb_rec.attribute15
1805 ,l_ethb_rec.created_by
1806 ,l_ethb_rec.creation_date
1807 ,l_ethb_rec.last_updated_by
1808 ,l_ethb_rec.last_update_date
1809 ,l_ethb_rec.last_update_login);
1810
1811 --Set OUT Values
1812
1813 x_ethb_rec := l_ethb_rec;
1814 okc_api.end_activity(x_msg_count
1815 ,x_msg_data);
1816 EXCEPTION
1817 WHEN g_exception_halt_validation THEN
1818
1819 -- No action necessary. Validation can continue to next attribute/column
1820
1821 NULL;
1822 WHEN okc_api.g_exception_error THEN
1823 x_return_status := okl_api.handle_exceptions(l_api_name
1824 ,g_pkg_name
1825 ,'OKL_API.G_RET_STS_ERROR'
1826 ,x_msg_count
1827 ,x_msg_data
1828 ,'_PVT');
1829 WHEN okc_api.g_exception_unexpected_error THEN
1830 x_return_status := okl_api.handle_exceptions(l_api_name
1831 ,g_pkg_name
1832 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1833 ,x_msg_count
1834 ,x_msg_data
1835 ,'_PVT');
1836 WHEN OTHERS THEN
1837 x_return_status := okl_api.handle_exceptions(l_api_name
1838 ,g_pkg_name
1839 ,'OTHERS'
1840 ,x_msg_count
1841 ,x_msg_data
1842 ,'_PVT');
1843 END insert_row;
1844
1845 --------------------------------------------------------------------------------
1846 -- Procedure insert_row_tl
1847 --------------------------------------------------------------------------------
1848
1849 PROCEDURE insert_row(p_api_version IN NUMBER
1850 ,p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false
1851 ,x_return_status OUT NOCOPY VARCHAR2
1852 ,x_msg_count OUT NOCOPY NUMBER
1853 ,x_msg_data OUT NOCOPY VARCHAR2
1854 ,p_ethtl_rec IN okl_ethtl_rec
1855 ,x_ethtl_rec OUT NOCOPY okl_ethtl_rec) IS
1856 l_api_version CONSTANT NUMBER := 1;
1857 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
1858 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1859 l_ethtl_rec okl_ethtl_rec := p_ethtl_rec;
1860
1861 CURSOR get_languages IS
1862 SELECT *
1863 FROM fnd_languages
1864 WHERE installed_flag IN('I', 'B');
1865
1866 FUNCTION set_attributes(p_ethtl_rec IN okl_ethtl_rec
1867 ,x_ethtl_rec OUT NOCOPY okl_ethtl_rec) RETURN VARCHAR2 IS
1868 l_return_status VARCHAR2(1) := okc_api.g_ret_sts_success;
1869
1870 BEGIN
1871 x_ethtl_rec := p_ethtl_rec;
1872 x_ethtl_rec.sfwt_flag := 'N';
1873 x_ethtl_rec.language := USERENV('LANG');
1874 x_ethtl_rec.source_lang := USERENV('LANG');
1875 RETURN(l_return_status);
1876 END set_attributes;
1877
1878 BEGIN
1879 l_return_status := okc_api.start_activity(l_api_name
1880 ,g_pkg_name
1881 ,p_init_msg_list
1882 ,l_api_version
1883 ,p_api_version
1884 ,'_PVT'
1885 ,x_return_status);
1886
1887 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1888 RAISE okc_api.g_exception_unexpected_error;
1889 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1890 RAISE okc_api.g_exception_error;
1891 END IF;
1892
1893 --Setting Item Attributes
1894
1895 l_return_status := set_attributes(p_ethtl_rec
1896 ,l_ethtl_rec);
1897
1898 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1899 RAISE okc_api.g_exception_unexpected_error;
1900 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1901 RAISE okc_api.g_exception_error;
1902 END IF;
1903
1904 FOR l_lang_rec IN get_languages LOOP
1905 l_ethtl_rec.language := l_lang_rec.language_code;
1906
1907 INSERT INTO okl_fe_eo_terms_all_tl
1908 (end_of_term_id
1909 ,end_of_term_desc
1910 ,language
1911 ,source_lang
1912 ,sfwt_flag
1913 ,created_by
1914 ,creation_date
1915 ,last_updated_by
1916 ,last_update_date
1917 ,last_update_login)
1918 VALUES (l_ethtl_rec.end_of_term_id
1919 ,l_ethtl_rec.end_of_term_desc
1920 ,l_ethtl_rec.language
1921 ,l_ethtl_rec.source_lang
1922 ,l_ethtl_rec.sfwt_flag
1923 ,l_ethtl_rec.created_by
1924 ,l_ethtl_rec.creation_date
1925 ,l_ethtl_rec.last_updated_by
1926 ,l_ethtl_rec.last_update_date
1927 ,l_ethtl_rec.last_update_login);
1928
1929 END LOOP;
1930
1931 --Set OUT Values
1932
1933 x_ethtl_rec := l_ethtl_rec;
1934 okc_api.end_activity(x_msg_count
1935 ,x_msg_data);
1936 EXCEPTION
1937 WHEN g_exception_halt_validation THEN
1938
1939 -- No action necessary. Validation can continue to next attribute/column
1940
1941 NULL;
1942 WHEN okc_api.g_exception_error THEN
1943 x_return_status := okl_api.handle_exceptions(l_api_name
1944 ,g_pkg_name
1945 ,'OKL_API.G_RET_STS_ERROR'
1946 ,x_msg_count
1947 ,x_msg_data
1948 ,'_PVT');
1949 WHEN okc_api.g_exception_unexpected_error THEN
1950 x_return_status := okl_api.handle_exceptions(l_api_name
1951 ,g_pkg_name
1952 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1953 ,x_msg_count
1954 ,x_msg_data
1955 ,'_PVT');
1956 WHEN OTHERS THEN
1957 x_return_status := okl_api.handle_exceptions(l_api_name
1958 ,g_pkg_name
1959 ,'OTHERS'
1960 ,x_msg_count
1961 ,x_msg_data
1962 ,'_PVT');
1963 END insert_row;
1964
1965 --------------------------------------------------------------------------------
1966 -- Procedure insert_row_v
1967 --------------------------------------------------------------------------------
1968
1969 PROCEDURE insert_row(p_api_version IN NUMBER
1970 ,p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false
1971 ,x_return_status OUT NOCOPY VARCHAR2
1972 ,x_msg_count OUT NOCOPY NUMBER
1973 ,x_msg_data OUT NOCOPY VARCHAR2
1974 ,p_ethv_rec IN okl_ethv_rec
1975 ,x_ethv_rec OUT NOCOPY okl_ethv_rec) IS
1976 l_api_version CONSTANT NUMBER := 1;
1977 l_api_name CONSTANT VARCHAR2(30) := 'v_insert_row';
1978 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1979 l_ethv_rec okl_ethv_rec;
1980 l_def_ethv_rec okl_ethv_rec;
1981 l_ethb_rec okl_ethb_rec;
1982 lx_ethb_rec okl_ethb_rec;
1983 l_ethtl_rec okl_ethtl_rec;
1984 lx_ethtl_rec okl_ethtl_rec;
1985
1986 FUNCTION fill_who_columns(p_ethv_rec IN okl_ethv_rec) RETURN okl_ethv_rec IS
1987 l_ethv_rec okl_ethv_rec := p_ethv_rec;
1988
1989 BEGIN
1990 l_ethv_rec.creation_date := SYSDATE;
1991 l_ethv_rec.created_by := fnd_global.user_id;
1992 l_ethv_rec.last_update_date := SYSDATE;
1993 l_ethv_rec.last_updated_by := fnd_global.user_id;
1994 l_ethv_rec.last_update_login := fnd_global.login_id;
1995 RETURN(l_ethv_rec);
1996 END fill_who_columns;
1997
1998 FUNCTION set_attributes(p_ethv_rec IN okl_ethv_rec
1999 ,x_ethv_rec OUT NOCOPY okl_ethv_rec) RETURN VARCHAR2 IS
2000 l_return_status VARCHAR2(1) := okc_api.g_ret_sts_success;
2001
2002 BEGIN
2003 x_ethv_rec := p_ethv_rec;
2004 x_ethv_rec.object_version_number := 1;
2005 x_ethv_rec.org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
2006 RETURN(l_return_status);
2007 END set_attributes;
2008
2009 BEGIN
2010 l_return_status := okc_api.start_activity(l_api_name
2011 ,g_pkg_name
2012 ,p_init_msg_list
2013 ,l_api_version
2014 ,p_api_version
2015 ,'_PVT'
2016 ,x_return_status);
2017
2018 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2019 RAISE okc_api.g_exception_unexpected_error;
2020 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2021 RAISE okc_api.g_exception_error;
2022 END IF;
2023 l_ethv_rec := null_out_defaults(p_ethv_rec);
2024
2025 -- Set Primary key value
2026
2027 l_ethv_rec.end_of_term_id := get_seq_id;
2028
2029 --Setting Item Attributes
2030
2031 l_return_status := set_attributes(l_ethv_rec
2032 ,l_def_ethv_rec);
2033
2034 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2035 RAISE okc_api.g_exception_unexpected_error;
2036 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2037 RAISE okc_api.g_exception_error;
2038 END IF;
2039 l_def_ethv_rec := fill_who_columns(l_def_ethv_rec);
2040 l_return_status := validate_attributes(l_def_ethv_rec);
2041
2042 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2043 RAISE okc_api.g_exception_unexpected_error;
2044 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2045 RAISE okc_api.g_exception_error;
2046 END IF;
2047 l_return_status := validate_record(l_def_ethv_rec);
2048
2049 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2050 RAISE okc_api.g_exception_unexpected_error;
2051 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2052 RAISE okc_api.g_exception_error;
2053 END IF;
2054 migrate(l_def_ethv_rec
2055 ,l_ethb_rec);
2056 migrate(l_def_ethv_rec
2057 ,l_ethtl_rec);
2058 insert_row(p_api_version
2059 ,p_init_msg_list
2060 ,l_return_status
2061 ,x_msg_count
2062 ,x_msg_data
2063 ,l_ethb_rec
2064 ,lx_ethb_rec);
2065
2066 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2067 RAISE okc_api.g_exception_unexpected_error;
2068 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2069 RAISE okc_api.g_exception_error;
2070 END IF;
2071 migrate(lx_ethb_rec
2072 ,l_def_ethv_rec);
2073 insert_row(p_api_version
2074 ,p_init_msg_list
2075 ,l_return_status
2076 ,x_msg_count
2077 ,x_msg_data
2078 ,l_ethtl_rec
2079 ,lx_ethtl_rec);
2080
2081 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2082 RAISE okc_api.g_exception_unexpected_error;
2083 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2084 RAISE okc_api.g_exception_error;
2085 END IF;
2086 migrate(lx_ethtl_rec
2087 ,l_def_ethv_rec);
2088
2089 --Set OUT Values
2090
2091 x_ethv_rec := l_def_ethv_rec;
2092 okc_api.end_activity(x_msg_count
2093 ,x_msg_data);
2094 EXCEPTION
2095 WHEN g_exception_halt_validation THEN
2096
2097 -- No action necessary. Validation can continue to next attribute/column
2098
2099 NULL;
2100 WHEN okc_api.g_exception_error THEN
2101 x_return_status := okl_api.handle_exceptions(l_api_name
2102 ,g_pkg_name
2103 ,'OKL_API.G_RET_STS_ERROR'
2104 ,x_msg_count
2105 ,x_msg_data
2106 ,'_PVT');
2107 WHEN okc_api.g_exception_unexpected_error THEN
2108 x_return_status := okl_api.handle_exceptions(l_api_name
2109 ,g_pkg_name
2110 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2111 ,x_msg_count
2112 ,x_msg_data
2113 ,'_PVT');
2114 WHEN OTHERS THEN
2115 x_return_status := okl_api.handle_exceptions(l_api_name
2116 ,g_pkg_name
2117 ,'OTHERS'
2118 ,x_msg_count
2119 ,x_msg_data
2120 ,'_PVT');
2121 END insert_row;
2122
2123 --------------------------------------------------------------------------------
2124 -- Procedure insert_row_tbl
2125 --------------------------------------------------------------------------------
2126
2127 PROCEDURE insert_row(p_api_version IN NUMBER
2128 ,p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false
2129 ,x_return_status OUT NOCOPY VARCHAR2
2130 ,x_msg_count OUT NOCOPY NUMBER
2131 ,x_msg_data OUT NOCOPY VARCHAR2
2132 ,p_ethv_tbl IN okl_ethv_tbl
2133 ,x_ethv_tbl OUT NOCOPY okl_ethv_tbl) IS
2134 l_api_version CONSTANT NUMBER := 1;
2135 l_api_name CONSTANT VARCHAR2(30) := 'v_insert_row';
2136 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2137 i NUMBER := 0;
2138 l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2139
2140 BEGIN
2141 okc_api.init_msg_list(p_init_msg_list);
2142
2143 -- Make sure PL/SQL table has records in it before passing
2144
2145 IF (p_ethv_tbl.COUNT > 0) THEN
2146 i := p_ethv_tbl.FIRST;
2147
2148 LOOP
2149 insert_row(p_api_version => p_api_version
2150 ,p_init_msg_list => okc_api.g_false
2151 ,x_return_status => x_return_status
2152 ,x_msg_count => x_msg_count
2153 ,x_msg_data => x_msg_data
2154 ,p_ethv_rec => p_ethv_tbl(i)
2155 ,x_ethv_rec => x_ethv_tbl(i));
2156 IF x_return_status <> okc_api.g_ret_sts_success THEN
2157 IF l_overall_status <> okc_api.g_ret_sts_unexp_error THEN
2158 l_overall_status := x_return_status;
2159 END IF;
2160 END IF;
2161 EXIT WHEN(i = p_ethv_tbl.LAST);
2162 i := p_ethv_tbl.next(i);
2163 END LOOP;
2164 x_return_status := l_overall_status;
2165 END IF;
2166
2167 EXCEPTION
2168 WHEN g_exception_halt_validation THEN
2169
2170 -- No action necessary. Validation can continue to next attribute/column
2171
2172 NULL;
2173 WHEN okc_api.g_exception_error THEN
2174 x_return_status := okl_api.handle_exceptions(l_api_name
2175 ,g_pkg_name
2176 ,'OKL_API.G_RET_STS_ERROR'
2177 ,x_msg_count
2178 ,x_msg_data
2179 ,'_PVT');
2180 WHEN okc_api.g_exception_unexpected_error THEN
2181 x_return_status := okl_api.handle_exceptions(l_api_name
2182 ,g_pkg_name
2183 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2184 ,x_msg_count
2185 ,x_msg_data
2186 ,'_PVT');
2187 WHEN OTHERS THEN
2188 x_return_status := okl_api.handle_exceptions(l_api_name
2189 ,g_pkg_name
2190 ,'OTHERS'
2191 ,x_msg_count
2192 ,x_msg_data
2193 ,'_PVT');
2194 END insert_row;
2195
2196 --------------------------------------------------------------------------------
2197 -- Procedure update_row_b
2198 --------------------------------------------------------------------------------
2199
2200 PROCEDURE update_row(p_api_version IN NUMBER
2201 ,p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false
2202 ,x_return_status OUT NOCOPY VARCHAR2
2203 ,x_msg_count OUT NOCOPY NUMBER
2204 ,x_msg_data OUT NOCOPY VARCHAR2
2205 ,p_ethb_rec IN okl_ethb_rec
2206 ,x_ethb_rec OUT NOCOPY okl_ethb_rec) IS
2207 l_api_version CONSTANT NUMBER := 1;
2208 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
2209 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2210 l_ethb_rec okl_ethb_rec := p_ethb_rec;
2211 l_def_ethb_rec okl_ethb_rec;
2212 l_row_notfound BOOLEAN := true;
2213
2214 FUNCTION set_attributes(p_ethb_rec IN okl_ethb_rec
2215 ,x_ethb_rec OUT NOCOPY okl_ethb_rec) RETURN VARCHAR2 IS
2216 l_return_status VARCHAR2(1) := okc_api.g_ret_sts_success;
2217
2218 BEGIN
2219 x_ethb_rec := p_ethb_rec;
2220 RETURN(l_return_status);
2221 END set_attributes;
2222
2223 BEGIN
2224 l_return_status := okc_api.start_activity(l_api_name
2225 ,g_pkg_name
2226 ,p_init_msg_list
2227 ,l_api_version
2228 ,p_api_version
2229 ,'_PVT'
2230 ,x_return_status);
2231
2232 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2233 RAISE okc_api.g_exception_unexpected_error;
2234 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2235 RAISE okc_api.g_exception_error;
2236 END IF;
2237
2238 --Setting Item Attributes
2239
2240 l_return_status := set_attributes(p_ethb_rec
2241 ,l_def_ethb_rec);
2242
2243 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2244 RAISE okc_api.g_exception_unexpected_error;
2245 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2246 RAISE okc_api.g_exception_error;
2247 END IF;
2248
2249 UPDATE okl_fe_eo_terms_all_b
2250 SET end_of_term_id = l_def_ethb_rec.end_of_term_id
2251 ,end_of_term_name = l_def_ethb_rec.end_of_term_name
2252 ,object_version_number = l_def_ethb_rec.object_version_number + 1
2253 ,org_id = l_def_ethb_rec.org_id
2254 ,currency_code = l_def_ethb_rec.currency_code
2255 ,eot_type_code = l_def_ethb_rec.eot_type_code
2256 ,product_id = l_def_ethb_rec.product_id
2257 ,category_type_code = l_def_ethb_rec.category_type_code
2258 ,orig_end_of_term_id = l_def_ethb_rec.orig_end_of_term_id
2259 ,sts_code = l_def_ethb_rec.sts_code
2260 ,effective_from_date = l_def_ethb_rec.effective_from_date
2261 ,effective_to_date = l_def_ethb_rec.effective_to_date
2262 ,attribute_category = l_def_ethb_rec.attribute_category
2263 ,attribute1 = l_def_ethb_rec.attribute1
2264 ,attribute2 = l_def_ethb_rec.attribute2
2265 ,attribute3 = l_def_ethb_rec.attribute3
2266 ,attribute4 = l_def_ethb_rec.attribute4
2267 ,attribute5 = l_def_ethb_rec.attribute5
2268 ,attribute6 = l_def_ethb_rec.attribute6
2269 ,attribute7 = l_def_ethb_rec.attribute7
2270 ,attribute8 = l_def_ethb_rec.attribute8
2271 ,attribute9 = l_def_ethb_rec.attribute9
2272 ,attribute10 = l_def_ethb_rec.attribute10
2273 ,attribute11 = l_def_ethb_rec.attribute11
2274 ,attribute12 = l_def_ethb_rec.attribute12
2275 ,attribute13 = l_def_ethb_rec.attribute13
2276 ,attribute14 = l_def_ethb_rec.attribute14
2277 ,attribute15 = l_def_ethb_rec.attribute15
2278 ,created_by = l_def_ethb_rec.created_by
2279 ,creation_date = l_def_ethb_rec.creation_date
2280 ,last_updated_by = l_def_ethb_rec.last_updated_by
2281 ,last_update_date = l_def_ethb_rec.last_update_date
2282 ,last_update_login = l_def_ethb_rec.last_update_login
2283 WHERE end_of_term_id = l_def_ethb_rec.end_of_term_id;
2284
2285 --Set OUT Values
2286
2287 x_ethb_rec := l_ethb_rec;
2288 okc_api.end_activity(x_msg_count
2289 ,x_msg_data);
2290 EXCEPTION
2291 WHEN g_exception_halt_validation THEN
2292
2293 -- No action necessary. Validation can continue to next attribute/column
2294
2295 NULL;
2296 WHEN okc_api.g_exception_error THEN
2297 x_return_status := okl_api.handle_exceptions(l_api_name
2298 ,g_pkg_name
2299 ,'OKL_API.G_RET_STS_ERROR'
2300 ,x_msg_count
2301 ,x_msg_data
2302 ,'_PVT');
2303 WHEN okc_api.g_exception_unexpected_error THEN
2304 x_return_status := okl_api.handle_exceptions(l_api_name
2305 ,g_pkg_name
2306 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2307 ,x_msg_count
2308 ,x_msg_data
2309 ,'_PVT');
2310 WHEN OTHERS THEN
2311 x_return_status := okl_api.handle_exceptions(l_api_name
2312 ,g_pkg_name
2313 ,'OTHERS'
2314 ,x_msg_count
2315 ,x_msg_data
2316 ,'_PVT');
2317 END update_row;
2318
2319 --------------------------------------------------------------------------------
2320 -- Procedure update_row_tl
2321 --------------------------------------------------------------------------------
2322
2323 PROCEDURE update_row(p_api_version IN NUMBER
2324 ,p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false
2325 ,x_return_status OUT NOCOPY VARCHAR2
2326 ,x_msg_count OUT NOCOPY NUMBER
2327 ,x_msg_data OUT NOCOPY VARCHAR2
2328 ,p_ethtl_rec IN okl_ethtl_rec
2329 ,x_ethtl_rec OUT NOCOPY okl_ethtl_rec) IS
2330 l_api_version CONSTANT NUMBER := 1;
2331 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
2332 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2333 l_ethtl_rec okl_ethtl_rec := p_ethtl_rec;
2334 l_def_ethtl_rec okl_ethtl_rec;
2335 l_row_notfound BOOLEAN := true;
2336
2337 FUNCTION set_attributes(p_ethtl_rec IN okl_ethtl_rec
2338 ,x_ethtl_rec OUT NOCOPY okl_ethtl_rec) RETURN VARCHAR2 IS
2339 l_return_status VARCHAR2(1) := okc_api.g_ret_sts_success;
2340
2341 BEGIN
2342 x_ethtl_rec := p_ethtl_rec;
2343 x_ethtl_rec.language := USERENV('LANG');
2344 x_ethtl_rec.source_lang := USERENV('LANG');
2345 RETURN(l_return_status);
2346 END set_attributes;
2347
2348 BEGIN
2349 l_return_status := okc_api.start_activity(l_api_name
2350 ,g_pkg_name
2351 ,p_init_msg_list
2352 ,l_api_version
2353 ,p_api_version
2354 ,'_PVT'
2355 ,x_return_status);
2356
2357 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2358 RAISE okc_api.g_exception_unexpected_error;
2359 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2360 RAISE okc_api.g_exception_error;
2361 END IF;
2362
2363 --Setting Item Attributes
2364
2365 l_return_status := set_attributes(p_ethtl_rec
2366 ,l_def_ethtl_rec);
2367
2368 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2369 RAISE okc_api.g_exception_unexpected_error;
2370 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2371 RAISE okc_api.g_exception_error;
2372 END IF;
2373
2374 UPDATE okl_fe_eo_terms_all_tl
2375 SET end_of_term_id = l_def_ethtl_rec.end_of_term_id
2376 ,end_of_term_desc = l_def_ethtl_rec.end_of_term_desc
2377 ,language = l_def_ethtl_rec.language
2378 ,source_lang = l_def_ethtl_rec.source_lang
2379 ,sfwt_flag = l_def_ethtl_rec.sfwt_flag
2380 ,created_by = l_def_ethtl_rec.created_by
2381 ,creation_date = l_def_ethtl_rec.creation_date
2382 ,last_updated_by = l_def_ethtl_rec.last_updated_by
2383 ,last_update_date = l_def_ethtl_rec.last_update_date
2384 ,last_update_login = l_def_ethtl_rec.last_update_login
2385 WHERE end_of_term_id = l_def_ethtl_rec.end_of_term_id
2386 AND language = l_def_ethtl_rec.language;
2387
2388 UPDATE okl_fe_eo_terms_all_tl
2389 SET sfwt_flag = 'Y'
2390 WHERE end_of_term_id = l_def_ethtl_rec.end_of_term_id
2391 AND source_lang <> USERENV('LANG');
2392
2393 --Set OUT Values
2394
2395 x_ethtl_rec := l_ethtl_rec;
2396 okc_api.end_activity(x_msg_count
2397 ,x_msg_data);
2398 EXCEPTION
2399 WHEN g_exception_halt_validation THEN
2400
2401 -- No action necessary. Validation can continue to next attribute/column
2402
2403 NULL;
2404 WHEN okc_api.g_exception_error THEN
2405 x_return_status := okl_api.handle_exceptions(l_api_name
2406 ,g_pkg_name
2407 ,'OKL_API.G_RET_STS_ERROR'
2408 ,x_msg_count
2409 ,x_msg_data
2410 ,'_PVT');
2411 WHEN okc_api.g_exception_unexpected_error THEN
2412 x_return_status := okl_api.handle_exceptions(l_api_name
2413 ,g_pkg_name
2414 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2415 ,x_msg_count
2416 ,x_msg_data
2417 ,'_PVT');
2418 WHEN OTHERS THEN
2419 x_return_status := okl_api.handle_exceptions(l_api_name
2420 ,g_pkg_name
2421 ,'OTHERS'
2422 ,x_msg_count
2423 ,x_msg_data
2424 ,'_PVT');
2425 END update_row;
2426
2427 --------------------------------------------------------------------------------
2428 -- Procedure insert_row_v
2429 --------------------------------------------------------------------------------
2430
2431 PROCEDURE update_row(p_api_version IN NUMBER
2432 ,p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false
2433 ,x_return_status OUT NOCOPY VARCHAR2
2434 ,x_msg_count OUT NOCOPY NUMBER
2435 ,x_msg_data OUT NOCOPY VARCHAR2
2436 ,p_ethv_rec IN okl_ethv_rec
2437 ,x_ethv_rec OUT NOCOPY okl_ethv_rec) IS
2438 l_api_version CONSTANT NUMBER := 1;
2439 l_api_name CONSTANT VARCHAR2(30) := 'v_insert_row';
2440 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2441 l_ethv_rec okl_ethv_rec := p_ethv_rec;
2442 l_def_ethv_rec okl_ethv_rec;
2443 lx_ethv_rec okl_ethv_rec;
2444 l_ethb_rec okl_ethb_rec;
2445 lx_ethb_rec okl_ethb_rec;
2446 l_ethtl_rec okl_ethtl_rec;
2447 lx_ethtl_rec okl_ethtl_rec;
2448
2449 FUNCTION fill_who_columns(p_ethv_rec IN okl_ethv_rec) RETURN okl_ethv_rec IS
2450 l_ethv_rec okl_ethv_rec := p_ethv_rec;
2451
2452 BEGIN
2453 l_ethv_rec.last_update_date := SYSDATE;
2454 l_ethv_rec.last_updated_by := fnd_global.user_id;
2455 l_ethv_rec.last_update_login := fnd_global.login_id;
2456 RETURN(l_ethv_rec);
2457 END fill_who_columns;
2458
2459 FUNCTION populate_new_record(p_ethv_rec IN okl_ethv_rec
2460 ,x_ethv_rec OUT NOCOPY okl_ethv_rec) RETURN VARCHAR2 IS
2461 l_ethv_rec okl_ethv_rec;
2462 l_row_notfound BOOLEAN := true;
2463 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2464
2465 BEGIN
2466 x_ethv_rec := p_ethv_rec;
2467
2468 --Get current database values
2469
2470 l_ethv_rec := get_rec(p_ethv_rec
2471 ,l_row_notfound);
2472
2473 IF (l_row_notfound) THEN
2474 l_return_status := okl_api.g_ret_sts_unexp_error;
2475 END IF;
2476
2477 IF (x_ethv_rec.end_of_term_id IS NULL) THEN
2478 x_ethv_rec.end_of_term_id := l_ethv_rec.end_of_term_id;
2479 END IF;
2480
2481 IF (x_ethv_rec.object_version_number IS NULL) THEN
2482 x_ethv_rec.object_version_number := l_ethv_rec.object_version_number;
2483 END IF;
2484
2485 IF (x_ethv_rec.end_of_term_name IS NULL) THEN
2486 x_ethv_rec.end_of_term_name := l_ethv_rec.end_of_term_name;
2487 END IF;
2488
2489 IF (x_ethv_rec.end_of_term_desc IS NULL) THEN
2490 x_ethv_rec.end_of_term_desc := l_ethv_rec.end_of_term_desc;
2491 END IF;
2492
2493 IF (x_ethv_rec.org_id IS NULL) THEN
2494 x_ethv_rec.org_id := l_ethv_rec.org_id;
2495 END IF;
2496
2497 IF (x_ethv_rec.currency_code IS NULL) THEN
2498 x_ethv_rec.currency_code := l_ethv_rec.currency_code;
2499 END IF;
2500
2501 IF (x_ethv_rec.eot_type_code IS NULL) THEN
2502 x_ethv_rec.eot_type_code := l_ethv_rec.eot_type_code;
2503 END IF;
2504
2505 IF (x_ethv_rec.product_id IS NULL) THEN
2506 x_ethv_rec.product_id := l_ethv_rec.product_id;
2507 END IF;
2508
2509 IF (x_ethv_rec.category_type_code IS NULL) THEN
2510 x_ethv_rec.category_type_code := l_ethv_rec.category_type_code;
2511 END IF;
2512
2513 IF (x_ethv_rec.orig_end_of_term_id IS NULL) THEN
2514 x_ethv_rec.orig_end_of_term_id := l_ethv_rec.orig_end_of_term_id;
2515 END IF;
2516
2517 IF (x_ethv_rec.sts_code IS NULL) THEN
2518 x_ethv_rec.sts_code := l_ethv_rec.sts_code;
2519 END IF;
2520
2521 IF (x_ethv_rec.effective_from_date IS NULL) THEN
2522 x_ethv_rec.effective_from_date := l_ethv_rec.effective_from_date;
2523 END IF;
2524
2525 IF (x_ethv_rec.effective_to_date IS NULL) THEN
2526 x_ethv_rec.effective_to_date := l_ethv_rec.effective_to_date;
2527 END IF;
2528
2529 IF (x_ethv_rec.attribute_category IS NULL) THEN
2530 x_ethv_rec.attribute_category := l_ethv_rec.attribute_category;
2531 END IF;
2532
2533 IF (x_ethv_rec.attribute1 IS NULL) THEN
2534 x_ethv_rec.attribute1 := l_ethv_rec.attribute1;
2535 END IF;
2536
2537 IF (x_ethv_rec.attribute2 IS NULL) THEN
2538 x_ethv_rec.attribute2 := l_ethv_rec.attribute2;
2539 END IF;
2540
2541 IF (x_ethv_rec.attribute3 IS NULL) THEN
2542 x_ethv_rec.attribute3 := l_ethv_rec.attribute3;
2543 END IF;
2544
2545 IF (x_ethv_rec.attribute4 IS NULL) THEN
2546 x_ethv_rec.attribute4 := l_ethv_rec.attribute4;
2547 END IF;
2548
2549 IF (x_ethv_rec.attribute5 IS NULL) THEN
2550 x_ethv_rec.attribute5 := l_ethv_rec.attribute5;
2551 END IF;
2552
2553 IF (x_ethv_rec.attribute6 IS NULL) THEN
2554 x_ethv_rec.attribute6 := l_ethv_rec.attribute6;
2555 END IF;
2556
2557 IF (x_ethv_rec.attribute7 IS NULL) THEN
2558 x_ethv_rec.attribute7 := l_ethv_rec.attribute7;
2559 END IF;
2560
2561 IF (x_ethv_rec.attribute8 IS NULL) THEN
2562 x_ethv_rec.attribute8 := l_ethv_rec.attribute8;
2563 END IF;
2564
2565 IF (x_ethv_rec.attribute9 IS NULL) THEN
2566 x_ethv_rec.attribute9 := l_ethv_rec.attribute9;
2567 END IF;
2568
2569 IF (x_ethv_rec.attribute10 IS NULL) THEN
2570 x_ethv_rec.attribute10 := l_ethv_rec.attribute10;
2571 END IF;
2572
2573 IF (x_ethv_rec.attribute11 IS NULL) THEN
2574 x_ethv_rec.attribute11 := l_ethv_rec.attribute11;
2575 END IF;
2576
2577 IF (x_ethv_rec.attribute12 IS NULL) THEN
2578 x_ethv_rec.attribute12 := l_ethv_rec.attribute12;
2579 END IF;
2580
2581 IF (x_ethv_rec.attribute13 IS NULL) THEN
2582 x_ethv_rec.attribute13 := l_ethv_rec.attribute13;
2583 END IF;
2584
2585 IF (x_ethv_rec.attribute14 IS NULL) THEN
2586 x_ethv_rec.attribute14 := l_ethv_rec.attribute14;
2587 END IF;
2588
2589 IF (x_ethv_rec.attribute15 IS NULL) THEN
2590 x_ethv_rec.attribute15 := l_ethv_rec.attribute15;
2591 END IF;
2592
2593 IF (x_ethv_rec.created_by IS NULL) THEN
2594 x_ethv_rec.created_by := l_ethv_rec.created_by;
2595 END IF;
2596
2597 IF (x_ethv_rec.creation_date IS NULL) THEN
2598 x_ethv_rec.creation_date := l_ethv_rec.creation_date;
2599 END IF;
2600
2601 IF (x_ethv_rec.last_updated_by IS NULL) THEN
2602 x_ethv_rec.last_updated_by := l_ethv_rec.last_updated_by;
2603 END IF;
2604
2605 IF (x_ethv_rec.last_update_date IS NULL) THEN
2606 x_ethv_rec.last_update_date := l_ethv_rec.last_update_date;
2607 END IF;
2608
2609 IF (x_ethv_rec.last_update_login IS NULL) THEN
2610 x_ethv_rec.last_update_login := l_ethv_rec.last_update_login;
2611 END IF;
2612 RETURN(l_return_status);
2613 END populate_new_record;
2614
2615 FUNCTION set_attributes(p_ethv_rec IN okl_ethv_rec
2616 ,x_ethv_rec OUT NOCOPY okl_ethv_rec) RETURN VARCHAR2 IS
2617 l_return_status VARCHAR2(1) := okc_api.g_ret_sts_success;
2618
2619 BEGIN
2620 x_ethv_rec := p_ethv_rec;
2621 RETURN(l_return_status);
2622 END set_attributes;
2623
2624 BEGIN
2625 l_return_status := okc_api.start_activity(l_api_name
2626 ,g_pkg_name
2627 ,p_init_msg_list
2628 ,l_api_version
2629 ,p_api_version
2630 ,'_PVT'
2631 ,x_return_status);
2632
2633 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2634 RAISE okc_api.g_exception_unexpected_error;
2635 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2636 RAISE okc_api.g_exception_error;
2637 END IF;
2638
2639 --Setting Item Attributes
2640
2641 l_return_status := set_attributes(l_ethv_rec
2642 ,lx_ethv_rec);
2643
2644 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2645 RAISE okc_api.g_exception_unexpected_error;
2646 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2647 RAISE okc_api.g_exception_error;
2648 END IF;
2649 l_return_status := populate_new_record(lx_ethv_rec
2650 ,l_def_ethv_rec);
2651
2652 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2653 RAISE okc_api.g_exception_unexpected_error;
2654 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2655 RAISE okc_api.g_exception_error;
2656 END IF;
2657 l_def_ethv_rec := null_out_defaults(l_def_ethv_rec);
2658 l_def_ethv_rec := fill_who_columns(l_def_ethv_rec);
2659 l_return_status := validate_attributes(l_def_ethv_rec);
2660
2661 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2662 RAISE okc_api.g_exception_unexpected_error;
2663 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2664 RAISE okc_api.g_exception_error;
2665 END IF;
2666 l_return_status := validate_record(l_def_ethv_rec);
2667
2668 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2669 RAISE okc_api.g_exception_unexpected_error;
2670 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2671 RAISE okc_api.g_exception_error;
2672 END IF;
2673
2674 --lock the row
2675
2676 lock_row(p_api_version => l_api_version
2677 ,p_init_msg_list => okl_api.g_false
2678 ,x_return_status => l_return_status
2679 ,x_msg_count => x_msg_count
2680 ,x_msg_data => x_msg_data
2681 ,p_ethv_rec => l_def_ethv_rec);
2682
2683 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2684 RAISE okl_api.g_exception_unexpected_error;
2685 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2686 RAISE okl_api.g_exception_error;
2687 END IF;
2688 migrate(l_def_ethv_rec
2689 ,l_ethb_rec);
2690 migrate(l_def_ethv_rec
2691 ,l_ethtl_rec);
2692 update_row(p_api_version
2693 ,p_init_msg_list
2694 ,x_return_status
2695 ,x_msg_count
2696 ,x_msg_data
2697 ,l_ethb_rec
2698 ,lx_ethb_rec);
2699
2700 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
2701 RAISE okc_api.g_exception_unexpected_error;
2702 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
2703 RAISE okc_api.g_exception_error;
2704 END IF;
2705 migrate(lx_ethb_rec
2706 ,l_def_ethv_rec);
2707 update_row(p_api_version
2708 ,p_init_msg_list
2709 ,x_return_status
2710 ,x_msg_count
2711 ,x_msg_data
2712 ,l_ethtl_rec
2713 ,lx_ethtl_rec);
2714
2715 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
2716 RAISE okc_api.g_exception_unexpected_error;
2717 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
2718 RAISE okc_api.g_exception_error;
2719 END IF;
2720 migrate(lx_ethtl_rec
2721 ,l_def_ethv_rec);
2722
2723 --Set OUT Values
2724
2725 x_ethv_rec := l_def_ethv_rec;
2726 okc_api.end_activity(x_msg_count
2727 ,x_msg_data);
2728 EXCEPTION
2729 WHEN g_exception_halt_validation THEN
2730
2731 -- No action necessary. Validation can continue to next attribute/column
2732
2733 NULL;
2734 WHEN okc_api.g_exception_error THEN
2735 x_return_status := okl_api.handle_exceptions(l_api_name
2736 ,g_pkg_name
2737 ,'OKL_API.G_RET_STS_ERROR'
2738 ,x_msg_count
2739 ,x_msg_data
2740 ,'_PVT');
2741 WHEN okc_api.g_exception_unexpected_error THEN
2742 x_return_status := okl_api.handle_exceptions(l_api_name
2743 ,g_pkg_name
2744 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2745 ,x_msg_count
2746 ,x_msg_data
2747 ,'_PVT');
2748 WHEN OTHERS THEN
2749 x_return_status := okl_api.handle_exceptions(l_api_name
2750 ,g_pkg_name
2751 ,'OTHERS'
2752 ,x_msg_count
2753 ,x_msg_data
2754 ,'_PVT');
2755 END update_row;
2756
2757 --------------------------------------------------------------------------------
2758 -- Procedure insert_row_tbl
2759 --------------------------------------------------------------------------------
2760
2761 PROCEDURE update_row(p_api_version IN NUMBER
2762 ,p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false
2763 ,x_return_status OUT NOCOPY VARCHAR2
2764 ,x_msg_count OUT NOCOPY NUMBER
2765 ,x_msg_data OUT NOCOPY VARCHAR2
2766 ,p_ethv_tbl IN okl_ethv_tbl
2767 ,x_ethv_tbl OUT NOCOPY okl_ethv_tbl) IS
2768 l_api_version CONSTANT NUMBER := 1;
2769 l_api_name CONSTANT VARCHAR2(30) := 'v_update_row';
2770 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2771 i NUMBER := 0;
2772 l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2773
2774 BEGIN
2775 okc_api.init_msg_list(p_init_msg_list);
2776
2777 -- Make sure PL/SQL table has records in it before passing
2778
2779 IF (p_ethv_tbl.COUNT > 0) THEN
2780 i := p_ethv_tbl.FIRST;
2781
2782 LOOP
2783 update_row(p_api_version => p_api_version
2784 ,p_init_msg_list => okc_api.g_false
2785 ,x_return_status => x_return_status
2786 ,x_msg_count => x_msg_count
2787 ,x_msg_data => x_msg_data
2788 ,p_ethv_rec => p_ethv_tbl(i)
2789 ,x_ethv_rec => x_ethv_tbl(i));
2790 IF x_return_status <> okc_api.g_ret_sts_success THEN
2791 IF l_overall_status <> okc_api.g_ret_sts_unexp_error THEN
2792 l_overall_status := x_return_status;
2793 END IF;
2794 END IF;
2795 EXIT WHEN(i = p_ethv_tbl.LAST);
2796 i := p_ethv_tbl.next(i);
2797 END LOOP;
2798 x_return_status := l_overall_status;
2799 END IF;
2800
2801 EXCEPTION
2802 WHEN g_exception_halt_validation THEN
2803
2804 -- No action necessary. Validation can continue to next attribute/column
2805
2806 NULL;
2807 WHEN okc_api.g_exception_error THEN
2808 x_return_status := okl_api.handle_exceptions(l_api_name
2809 ,g_pkg_name
2810 ,'OKL_API.G_RET_STS_ERROR'
2811 ,x_msg_count
2812 ,x_msg_data
2813 ,'_PVT');
2814 WHEN okc_api.g_exception_unexpected_error THEN
2815 x_return_status := okl_api.handle_exceptions(l_api_name
2816 ,g_pkg_name
2817 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2818 ,x_msg_count
2819 ,x_msg_data
2820 ,'_PVT');
2821 WHEN OTHERS THEN
2822 x_return_status := okl_api.handle_exceptions(l_api_name
2823 ,g_pkg_name
2824 ,'OTHERS'
2825 ,x_msg_count
2826 ,x_msg_data
2827 ,'_PVT');
2828 END update_row;
2829
2830 --------------------------------------------------------------------------------
2831 -- Procedure delete_row_b
2832 --------------------------------------------------------------------------------
2833
2834 PROCEDURE delete_row(p_api_version IN NUMBER
2835 ,p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false
2836 ,x_return_status OUT NOCOPY VARCHAR2
2837 ,x_msg_count OUT NOCOPY NUMBER
2838 ,x_msg_data OUT NOCOPY VARCHAR2
2839 ,p_ethb_rec IN okl_ethb_rec) IS
2840 l_api_version CONSTANT NUMBER := 1;
2841 l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
2842 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2843 l_ethb_rec okl_ethb_rec := p_ethb_rec;
2844 l_row_notfound BOOLEAN := true;
2845
2846 BEGIN
2847 l_return_status := okc_api.start_activity(l_api_name
2848 ,g_pkg_name
2849 ,p_init_msg_list
2850 ,l_api_version
2851 ,p_api_version
2852 ,'_PVT'
2853 ,x_return_status);
2854
2855 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2856 RAISE okc_api.g_exception_unexpected_error;
2857 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2858 RAISE okc_api.g_exception_error;
2859 END IF;
2860
2861 DELETE FROM okl_fe_eo_terms_all_b
2862 WHERE end_of_term_id = l_ethb_rec.end_of_term_id;
2863
2864 EXCEPTION
2865 WHEN g_exception_halt_validation THEN
2866
2867 -- No action necessary. Validation can continue to next attribute/column
2868
2869 NULL;
2870 WHEN okc_api.g_exception_error THEN
2871 x_return_status := okl_api.handle_exceptions(l_api_name
2872 ,g_pkg_name
2873 ,'OKL_API.G_RET_STS_ERROR'
2874 ,x_msg_count
2875 ,x_msg_data
2876 ,'_PVT');
2877 WHEN okc_api.g_exception_unexpected_error THEN
2878 x_return_status := okl_api.handle_exceptions(l_api_name
2879 ,g_pkg_name
2880 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2881 ,x_msg_count
2882 ,x_msg_data
2883 ,'_PVT');
2884 WHEN OTHERS THEN
2885 x_return_status := okl_api.handle_exceptions(l_api_name
2886 ,g_pkg_name
2887 ,'OTHERS'
2888 ,x_msg_count
2889 ,x_msg_data
2890 ,'_PVT');
2891 END delete_row;
2892
2893 --------------------------------------------------------------------------------
2894 -- Procedure delete_row_tl
2895 --------------------------------------------------------------------------------
2896
2897 PROCEDURE delete_row(p_api_version IN NUMBER
2898 ,p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false
2899 ,x_return_status OUT NOCOPY VARCHAR2
2900 ,x_msg_count OUT NOCOPY NUMBER
2901 ,x_msg_data OUT NOCOPY VARCHAR2
2902 ,p_ethtl_rec IN okl_ethtl_rec) IS
2903 l_api_version CONSTANT NUMBER := 1;
2904 l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
2905 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2906 l_ethtl_rec okl_ethtl_rec := p_ethtl_rec;
2907 l_row_notfound BOOLEAN := true;
2908
2909 FUNCTION set_attributes(p_ethtl_rec IN okl_ethtl_rec
2910 ,x_ethtl_rec OUT NOCOPY okl_ethtl_rec) RETURN VARCHAR2 IS
2911 l_return_status VARCHAR2(1) := okc_api.g_ret_sts_success;
2912
2913 BEGIN
2914 x_ethtl_rec := p_ethtl_rec;
2915 x_ethtl_rec.language := USERENV('LANG');
2916 x_ethtl_rec.source_lang := USERENV('LANG');
2917 RETURN(l_return_status);
2918 END set_attributes;
2919
2920 BEGIN
2921 l_return_status := okc_api.start_activity(l_api_name
2922 ,g_pkg_name
2923 ,p_init_msg_list
2924 ,l_api_version
2925 ,p_api_version
2926 ,'_PVT'
2927 ,x_return_status);
2928
2929 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2930 RAISE okc_api.g_exception_unexpected_error;
2931 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2932 RAISE okc_api.g_exception_error;
2933 END IF;
2934
2935 --Setting Item Attributes
2936
2937 l_return_status := set_attributes(p_ethtl_rec
2938 ,l_ethtl_rec);
2939
2940 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2941 RAISE okc_api.g_exception_unexpected_error;
2942 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2943 RAISE okc_api.g_exception_error;
2944 END IF;
2945
2946 DELETE FROM okl_fe_eo_terms_all_tl
2947 WHERE end_of_term_id = l_ethtl_rec.end_of_term_id;
2948
2949 EXCEPTION
2950 WHEN g_exception_halt_validation THEN
2951
2952 -- No action necessary. Validation can continue to next attribute/column
2953
2954 NULL;
2955 WHEN okc_api.g_exception_error THEN
2956 x_return_status := okl_api.handle_exceptions(l_api_name
2957 ,g_pkg_name
2958 ,'OKL_API.G_RET_STS_ERROR'
2959 ,x_msg_count
2960 ,x_msg_data
2961 ,'_PVT');
2962 WHEN okc_api.g_exception_unexpected_error THEN
2963 x_return_status := okl_api.handle_exceptions(l_api_name
2964 ,g_pkg_name
2965 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2966 ,x_msg_count
2967 ,x_msg_data
2968 ,'_PVT');
2969 WHEN OTHERS THEN
2970 x_return_status := okl_api.handle_exceptions(l_api_name
2971 ,g_pkg_name
2972 ,'OTHERS'
2973 ,x_msg_count
2974 ,x_msg_data
2975 ,'_PVT');
2976 END delete_row;
2977
2978 --------------------------------------------------------------------------------
2979 -- Procedure delete_row_v
2980 --------------------------------------------------------------------------------
2981
2982 PROCEDURE delete_row(p_api_version IN NUMBER
2983 ,p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false
2984 ,x_return_status OUT NOCOPY VARCHAR2
2985 ,x_msg_count OUT NOCOPY NUMBER
2986 ,x_msg_data OUT NOCOPY VARCHAR2
2987 ,p_ethv_rec IN okl_ethv_rec) IS
2988 l_api_version CONSTANT NUMBER := 1;
2989 l_api_name CONSTANT VARCHAR2(30) := 'v_delete_row';
2990 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2991 l_ethv_rec okl_ethv_rec := p_ethv_rec;
2992 l_ethb_rec okl_ethb_rec;
2993 l_ethtl_rec okl_ethtl_rec;
2994
2995 BEGIN
2996 l_return_status := okc_api.start_activity(l_api_name
2997 ,g_pkg_name
2998 ,p_init_msg_list
2999 ,l_api_version
3000 ,p_api_version
3001 ,'_PVT'
3002 ,x_return_status);
3003
3004 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
3005 RAISE okc_api.g_exception_unexpected_error;
3006 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
3007 RAISE okc_api.g_exception_error;
3008 END IF;
3009 migrate(l_ethv_rec
3010 ,l_ethb_rec);
3011 migrate(l_ethv_rec
3012 ,l_ethtl_rec);
3013 delete_row(p_api_version
3014 ,p_init_msg_list
3015 ,x_return_status
3016 ,x_msg_count
3017 ,x_msg_data
3018 ,l_ethb_rec);
3019
3020 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
3021 RAISE okc_api.g_exception_unexpected_error;
3022 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
3023 RAISE okc_api.g_exception_error;
3024 END IF;
3025 delete_row(p_api_version
3026 ,p_init_msg_list
3027 ,x_return_status
3028 ,x_msg_count
3029 ,x_msg_data
3030 ,l_ethtl_rec);
3031
3032 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
3033 RAISE okc_api.g_exception_unexpected_error;
3034 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
3035 RAISE okc_api.g_exception_error;
3036 END IF;
3037 okc_api.end_activity(x_msg_count
3038 ,x_msg_data);
3039 EXCEPTION
3040 WHEN g_exception_halt_validation THEN
3041
3042 -- No action necessary. Validation can continue to next attribute/column
3043
3044 NULL;
3045 WHEN okc_api.g_exception_error THEN
3046 x_return_status := okl_api.handle_exceptions(l_api_name
3047 ,g_pkg_name
3048 ,'OKL_API.G_RET_STS_ERROR'
3049 ,x_msg_count
3050 ,x_msg_data
3051 ,'_PVT');
3052 WHEN okc_api.g_exception_unexpected_error THEN
3053 x_return_status := okl_api.handle_exceptions(l_api_name
3054 ,g_pkg_name
3055 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
3056 ,x_msg_count
3057 ,x_msg_data
3058 ,'_PVT');
3059 WHEN OTHERS THEN
3060 x_return_status := okl_api.handle_exceptions(l_api_name
3061 ,g_pkg_name
3062 ,'OTHERS'
3063 ,x_msg_count
3064 ,x_msg_data
3065 ,'_PVT');
3066 END delete_row;
3067
3068 --------------------------------------------------------------------------------
3069 -- Procedure delete_row_tbl
3070 --------------------------------------------------------------------------------
3071
3072 PROCEDURE delete_row(p_api_version IN NUMBER
3073 ,p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false
3074 ,x_return_status OUT NOCOPY VARCHAR2
3075 ,x_msg_count OUT NOCOPY NUMBER
3076 ,x_msg_data OUT NOCOPY VARCHAR2
3077 ,p_ethv_tbl IN okl_ethv_tbl) IS
3078 l_api_version CONSTANT NUMBER := 1;
3079 l_api_name CONSTANT VARCHAR2(30) := 'v_delete_row';
3080 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
3081 i NUMBER := 0;
3082 l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
3083
3084 BEGIN
3085 okc_api.init_msg_list(p_init_msg_list);
3086
3087 -- Make sure PL/SQL table has records in it before passing
3088
3089 IF (p_ethv_tbl.COUNT > 0) THEN
3090 i := p_ethv_tbl.FIRST;
3091
3092 LOOP
3093 delete_row(p_api_version => p_api_version
3094 ,p_init_msg_list => okc_api.g_false
3095 ,x_return_status => x_return_status
3096 ,x_msg_count => x_msg_count
3097 ,x_msg_data => x_msg_data
3098 ,p_ethv_rec => p_ethv_tbl(i));
3099 IF x_return_status <> okc_api.g_ret_sts_success THEN
3100 IF l_overall_status <> okc_api.g_ret_sts_unexp_error THEN
3101 l_overall_status := x_return_status;
3102 END IF;
3103 END IF;
3104 EXIT WHEN(i = p_ethv_tbl.LAST);
3105 i := p_ethv_tbl.next(i);
3106 END LOOP;
3107 x_return_status := l_overall_status;
3108 END IF;
3109
3110 EXCEPTION
3111 WHEN g_exception_halt_validation THEN
3112
3113 -- No action necessary. Validation can continue to next attribute/column
3114
3115 NULL;
3116 WHEN okc_api.g_exception_error THEN
3117 x_return_status := okl_api.handle_exceptions(l_api_name
3118 ,g_pkg_name
3119 ,'OKL_API.G_RET_STS_ERROR'
3120 ,x_msg_count
3121 ,x_msg_data
3122 ,'_PVT');
3123 WHEN okc_api.g_exception_unexpected_error THEN
3124 x_return_status := okl_api.handle_exceptions(l_api_name
3125 ,g_pkg_name
3126 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
3127 ,x_msg_count
3128 ,x_msg_data
3129 ,'_PVT');
3130 WHEN OTHERS THEN
3131 x_return_status := okl_api.handle_exceptions(l_api_name
3132 ,g_pkg_name
3133 ,'OTHERS'
3134 ,x_msg_count
3135 ,x_msg_data
3136 ,'_PVT');
3137 END delete_row;
3138
3139 END okl_eth_pvt;