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