[Home] [Help]
PACKAGE BODY: APPS.OKL_EVE_PVT
Source
1 PACKAGE BODY okl_eve_pvt AS
2 /* $Header: OKLSEVEB.pls 120.1 2005/10/18 14:03:13 viselvar noship $ */
3
4 g_no_parent_record CONSTANT VARCHAR2(200) := 'OKC_NO_PARENT_RECORD';
5 g_unexpected_error CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
6 g_sqlerrm_token CONSTANT VARCHAR2(200) := 'SQLerrm';
7 g_sqlcode_token CONSTANT VARCHAR2(200) := 'SQLcode';
8 g_exception_halt_validation EXCEPTION;
9
10 PROCEDURE api_copy IS
11
12 BEGIN
13 NULL;
14 END api_copy;
15
16 PROCEDURE change_version IS
17
18 BEGIN
19 NULL;
20 END change_version;
21
22 PROCEDURE validate_end_of_term_ver_id(x_return_status OUT NOCOPY VARCHAR2
23 ,p_eve_rec IN okl_eve_rec) IS
24
25 BEGIN
26
27 -- initialize the return status
28
29 x_return_status := okl_api.g_ret_sts_success;
30
31 -- END_OF_TERM_VER_ID is a required field
32
33 IF (p_eve_rec.end_of_term_ver_id IS NULL OR p_eve_rec.end_of_term_ver_id = okl_api.g_miss_num) THEN
34 okl_api.set_message(p_app_name => g_app_name
35 ,p_msg_name => g_required_value
36 ,p_token1 => g_col_name_token
37 ,p_token1_value => 'END_OF_TERM_VER_ID');
38
39 -- notify caller of an error
40
41 x_return_status := okl_api.g_ret_sts_error;
42
43 -- halt further validation of this column
44
45 RAISE g_exception_halt_validation;
46 END IF;
47
48 EXCEPTION
49 WHEN g_exception_halt_validation THEN
50
51 -- no proccessing required. Validation can continue with the next column
52
53 NULL;
54 WHEN OTHERS THEN
55
56 -- store SQL error message on message stack for caller
57
58 okl_api.set_message(p_app_name => g_app_name
59 ,p_msg_name => g_unexpected_error
60 ,p_token1 => g_sqlcode_token
61 ,p_token1_value => sqlcode
62 ,p_token2 => g_sqlerrm_token
63 ,p_token2_value => sqlerrm);
64
65 -- notify caller of an UNEXPECTED error
66
67 x_return_status := okl_api.g_ret_sts_unexp_error;
68 END validate_end_of_term_ver_id;
69
70 PROCEDURE validate_version_number(x_return_status OUT NOCOPY VARCHAR2
71 ,p_eve_rec IN okl_eve_rec) IS
72
73 BEGIN
74
75 -- initialize the return status
76
77 x_return_status := okl_api.g_ret_sts_success;
78
79 -- version number is a mandatory field
80
81 IF (p_eve_rec.version_number IS NULL OR p_eve_rec.version_number = okl_api.g_miss_num) THEN
82 okl_api.set_message(p_app_name => g_app_name
83 ,p_msg_name => g_required_value
84 ,p_token1 => g_col_name_token
85 ,p_token1_value => 'version_number');
86
87 -- notify caller of an error
88
89 x_return_status := okl_api.g_ret_sts_error;
90
91 -- halt further validation of this column
92
93 RAISE g_exception_halt_validation;
94 END IF;
95
96 EXCEPTION
97 WHEN g_exception_halt_validation THEN
98
99 -- no proccessing required. Validation can continue with the next column
100
101 NULL;
102 WHEN OTHERS THEN
103
104 -- store SQL error message on message stack for caller
105
106 okl_api.set_message(p_app_name => g_app_name
107 ,p_msg_name => g_unexpected_error
108 ,p_token1 => g_sqlcode_token
109 ,p_token1_value => sqlcode
110 ,p_token2 => g_sqlerrm_token
111 ,p_token2_value => sqlerrm);
112
113 -- notify caller of an UNEXPECTED error
114
115 x_return_status := okl_api.g_ret_sts_unexp_error;
116 END validate_version_number;
117
118 PROCEDURE validate_end_of_term_id(x_return_status OUT NOCOPY VARCHAR2
119 ,p_eve_rec IN okl_eve_rec) IS
120
121 CURSOR pos_exists_csr IS
122 SELECT 'x'
123 FROM okl_fe_eo_terms_all_b
124 WHERE end_of_term_id = p_eve_rec.end_of_term_id;
125 l_dummy_var VARCHAR2(1) := '?';
126
127 BEGIN
128
129 -- Initialize the return status to success
130
131 x_return_status := okl_api.g_ret_sts_success;
132
133 IF (p_eve_rec.end_of_term_id IS NULL AND p_eve_rec.end_of_term_id = okl_api.g_miss_num) THEN
134 okl_api.set_message(p_app_name => g_app_name
135 ,p_msg_name => g_required_value
136 ,p_token1 => g_col_name_token
137 ,p_token1_value => 'END_OF_TERM_ID');
138
139 -- notify caller of an error
140
141 x_return_status := okc_api.g_ret_sts_error;
142 RAISE g_exception_halt_validation;
143 END IF;
144 OPEN pos_exists_csr;
145 FETCH pos_exists_csr INTO l_dummy_var ;
146 CLOSE pos_exists_csr;
147
148 -- if l_dummy_var is still set to default, data was not found
149
150 IF (l_dummy_var = '?') THEN
151 okl_api.set_message(p_app_name => g_app_name
152 ,p_msg_name => g_invalid_value
153 ,p_token1 => g_col_name_token
154 ,p_token1_value => 'end_of_term_id');
155
156 -- notify caller of an error
157
158 x_return_status := okc_api.g_ret_sts_error;
159 RAISE g_exception_halt_validation;
160 END IF;
161
162 EXCEPTION
163 WHEN g_exception_halt_validation THEN
164
165 -- no processing necessary; validation can continue
166 -- with the next column
167
168 NULL;
169 WHEN OTHERS THEN
170
171 -- store SQL error message on message stack for caller
172
173 okc_api.set_message(p_app_name => g_app_name
174 ,p_msg_name => g_unexpected_error
175 ,p_token1 => g_sqlcode_token
176 ,p_token1_value => sqlcode
177 ,p_token2 => g_sqlerrm_token
178 ,p_token2_value => sqlerrm);
179
180 -- notify caller of an UNEXPECTED error
181
182 x_return_status := okc_api.g_ret_sts_unexp_error;
183
184 -- verify that cursor was closed
185
186 IF pos_exists_csr%ISOPEN THEN
187 CLOSE pos_exists_csr;
188 END IF;
189
190 END validate_end_of_term_id;
191
192 PROCEDURE validate_effective_from_date(x_return_status OUT NOCOPY VARCHAR2
193 ,p_eve_rec IN okl_eve_rec) IS
194
195 BEGIN
196
197 -- Initialize the return status to success
198
199 x_return_status := okl_api.g_ret_sts_success;
200
201 IF (p_eve_rec.effective_from_date IS NULL OR p_eve_rec.effective_from_date = okl_api.g_miss_date) THEN
202 okl_api.set_message(p_app_name => g_app_name
203 ,p_msg_name => g_required_value
204 ,p_token1 => g_col_name_token
205 ,p_token1_value => 'EFFECTIVE_FROM_DATE');
206
207 -- notify caller of an error
208
209 x_return_status := okl_api.g_ret_sts_error;
210 RAISE g_exception_halt_validation;
211 END IF;
212
213 EXCEPTION
214 WHEN g_exception_halt_validation THEN
215
216 -- no processing necessary; validation can continue
217 -- with the next column
218
219 NULL;
220 WHEN OTHERS THEN
221
222 -- store SQL error message on message stack for caller
223
224 okc_api.set_message(p_app_name => g_app_name
225 ,p_msg_name => g_unexpected_error
226 ,p_token1 => g_sqlcode_token
227 ,p_token1_value => sqlcode
228 ,p_token2 => g_sqlerrm_token
229 ,p_token2_value => sqlerrm);
230
231 -- notify caller of an UNEXPECTED error
232
233 x_return_status := okc_api.g_ret_sts_unexp_error;
234 END validate_effective_from_date;
235
236 PROCEDURE validate_sts_code(x_return_status OUT NOCOPY VARCHAR2
237 ,p_eve_rec IN okl_eve_rec) IS
238
239 BEGIN
240
241 -- Initialize the return status to success
242
243 x_return_status := okl_api.g_ret_sts_success;
244
245 -- Column is mandatory
246
247 IF (p_eve_rec.sts_code IS NULL OR p_eve_rec.sts_code = okl_api.g_miss_char) THEN
248 okl_api.set_message(p_app_name => g_app_name
249 ,p_msg_name => g_required_value
250 ,p_token1 => g_col_name_token
251 ,p_token1_value => 'sts_code');
252
253 -- notify caller of an error
254
255 x_return_status := okl_api.g_ret_sts_error;
256 RAISE g_exception_halt_validation;
257 END IF;
258
259 -- Lookup Code Validation
260
261 x_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_PRC_STATUS'
262 ,p_lookup_code => p_eve_rec.sts_code);
263
264 IF (x_return_status = okc_api.g_ret_sts_error) THEN
265 okc_api.set_message(p_app_name => g_app_name
266 ,p_msg_name => g_invalid_value
267 ,p_token1 => g_col_name_token
268 ,p_token1_value => 'sts_code'); -- notify caller of an error
269 RAISE g_exception_halt_validation;
270 ELSIF (x_return_status = okc_api.g_ret_sts_unexp_error) THEN
271
272 -- notify caller of an error
273
274 x_return_status := okc_api.g_ret_sts_unexp_error;
275 RAISE g_exception_halt_validation;
276 END IF;
277
278 EXCEPTION
279 WHEN g_exception_halt_validation THEN
280
281 -- no processing necessary; validation can continue
282 -- with the next column
283
284 NULL;
285 WHEN OTHERS THEN
286
287 -- store SQL error message on message stack for caller
288
289 okc_api.set_message(p_app_name => g_app_name
290 ,p_msg_name => g_unexpected_error
291 ,p_token1 => g_sqlcode_token
292 ,p_token1_value => sqlcode
293 ,p_token2 => g_sqlerrm_token
294 ,p_token2_value => sqlerrm);
295
296 -- notify caller of an UNEXPECTED error
297
298 x_return_status := okc_api.g_ret_sts_unexp_error;
299 END validate_sts_code;
300
301 --------------------------------------------------------------------------------
302 -- Procedure get_rec for OKL_FE_EO_TERM_VERS
303 --------------------------------------------------------------------------------
304
305 FUNCTION get_rec(p_eve_rec IN okl_eve_rec
306 ,x_no_data_found OUT NOCOPY BOOLEAN) RETURN okl_eve_rec IS
307
308 CURSOR pve_pk_csr(p_id IN NUMBER) IS
309 SELECT end_of_term_ver_id
310 ,object_version_number
311 ,version_number
312 ,effective_from_date
313 ,effective_to_date
314 ,sts_code
315 ,end_of_term_id
316 ,attribute_category
317 ,attribute1
318 ,attribute2
319 ,attribute3
320 ,attribute4
321 ,attribute5
322 ,attribute6
323 ,attribute7
324 ,attribute8
325 ,attribute9
326 ,attribute10
327 ,attribute11
328 ,attribute12
329 ,attribute13
330 ,attribute14
331 ,attribute15
332 ,created_by
333 ,creation_date
334 ,last_updated_by
335 ,last_update_date
336 ,last_update_login
337 FROM okl_fe_eo_term_vers
338 WHERE okl_fe_eo_term_vers.end_of_term_ver_id = p_id;
339 l_eve_pk pve_pk_csr%ROWTYPE;
340 l_eve_rec okl_eve_rec;
341
342 BEGIN
343 x_no_data_found := true;
344
345 --Get current data base values
346
347 OPEN pve_pk_csr(p_eve_rec.end_of_term_ver_id);
348 FETCH pve_pk_csr INTO l_eve_rec.end_of_term_ver_id
349 ,l_eve_rec.object_version_number
350 ,l_eve_rec.version_number
351 ,l_eve_rec.effective_from_date
352 ,l_eve_rec.effective_to_date
353 ,l_eve_rec.sts_code
354 ,l_eve_rec.end_of_term_id
355 ,l_eve_rec.attribute_category
356 ,l_eve_rec.attribute1
357 ,l_eve_rec.attribute2
358 ,l_eve_rec.attribute3
359 ,l_eve_rec.attribute4
360 ,l_eve_rec.attribute5
361 ,l_eve_rec.attribute6
362 ,l_eve_rec.attribute7
363 ,l_eve_rec.attribute8
364 ,l_eve_rec.attribute9
365 ,l_eve_rec.attribute10
366 ,l_eve_rec.attribute11
367 ,l_eve_rec.attribute12
368 ,l_eve_rec.attribute13
369 ,l_eve_rec.attribute14
370 ,l_eve_rec.attribute15
371 ,l_eve_rec.created_by
372 ,l_eve_rec.creation_date
373 ,l_eve_rec.last_updated_by
374 ,l_eve_rec.last_update_date
375 ,l_eve_rec.last_update_login ;
376 x_no_data_found := pve_pk_csr%NOTFOUND;
377 CLOSE pve_pk_csr;
378 RETURN(l_eve_rec);
379 END get_rec;
380
381 FUNCTION get_rec(p_eve_rec IN okl_eve_rec) RETURN okl_eve_rec IS
382 l_row_notfound BOOLEAN := true;
383
384 BEGIN
385 RETURN(get_rec(p_eve_rec
386 ,l_row_notfound));
387 END get_rec;
388
389 FUNCTION null_out_defaults(p_eve_rec IN okl_eve_rec) RETURN okl_eve_rec IS
390 l_eve_rec okl_eve_rec := p_eve_rec;
391
392 BEGIN
393
394 IF (l_eve_rec.end_of_term_ver_id = okl_api.g_miss_num) THEN
395 l_eve_rec.end_of_term_ver_id := NULL;
396 END IF;
397
398 IF (l_eve_rec.object_version_number = okl_api.g_miss_num) THEN
399 l_eve_rec.object_version_number := NULL;
400 END IF;
401
402 IF (l_eve_rec.version_number = okl_api.g_miss_char) THEN
403 l_eve_rec.version_number := NULL;
404 END IF;
405
406 IF (l_eve_rec.effective_from_date = okl_api.g_miss_date) THEN
407 l_eve_rec.effective_from_date := NULL;
408 END IF;
409
410 IF (l_eve_rec.effective_to_date = okl_api.g_miss_date) THEN
411 l_eve_rec.effective_to_date := NULL;
412 END IF;
413
414 IF (l_eve_rec.sts_code = okl_api.g_miss_char) THEN
415 l_eve_rec.sts_code := NULL;
416 END IF;
417
418 IF (l_eve_rec.end_of_term_id = okl_api.g_miss_num) THEN
419 l_eve_rec.end_of_term_id := NULL;
420 END IF;
421
422 IF (l_eve_rec.attribute_category = okl_api.g_miss_char) THEN
423 l_eve_rec.attribute_category := NULL;
424 END IF;
425
426 IF (l_eve_rec.attribute1 = okl_api.g_miss_char) THEN
427 l_eve_rec.attribute1 := NULL;
428 END IF;
429
430 IF (l_eve_rec.attribute2 = okl_api.g_miss_char) THEN
431 l_eve_rec.attribute2 := NULL;
432 END IF;
433
434 IF (l_eve_rec.attribute3 = okl_api.g_miss_char) THEN
435 l_eve_rec.attribute3 := NULL;
436 END IF;
437
438 IF (l_eve_rec.attribute4 = okl_api.g_miss_char) THEN
439 l_eve_rec.attribute4 := NULL;
440 END IF;
441
442 IF (l_eve_rec.attribute5 = okl_api.g_miss_char) THEN
443 l_eve_rec.attribute5 := NULL;
444 END IF;
445
446 IF (l_eve_rec.attribute6 = okl_api.g_miss_char) THEN
447 l_eve_rec.attribute6 := NULL;
448 END IF;
449
450 IF (l_eve_rec.attribute7 = okl_api.g_miss_char) THEN
451 l_eve_rec.attribute7 := NULL;
452 END IF;
453
454 IF (l_eve_rec.attribute8 = okl_api.g_miss_char) THEN
455 l_eve_rec.attribute8 := NULL;
456 END IF;
457
458 IF (l_eve_rec.attribute9 = okl_api.g_miss_char) THEN
459 l_eve_rec.attribute9 := NULL;
460 END IF;
461
462 IF (l_eve_rec.attribute10 = okl_api.g_miss_char) THEN
463 l_eve_rec.attribute10 := NULL;
464 END IF;
465
466 IF (l_eve_rec.attribute11 = okl_api.g_miss_char) THEN
467 l_eve_rec.attribute11 := NULL;
468 END IF;
469
470 IF (l_eve_rec.attribute12 = okl_api.g_miss_char) THEN
471 l_eve_rec.attribute12 := NULL;
472 END IF;
473
474 IF (l_eve_rec.attribute13 = okl_api.g_miss_char) THEN
475 l_eve_rec.attribute13 := NULL;
476 END IF;
477
478 IF (l_eve_rec.attribute14 = okl_api.g_miss_char) THEN
479 l_eve_rec.attribute14 := NULL;
480 END IF;
481
482 IF (l_eve_rec.attribute15 = okl_api.g_miss_char) THEN
483 l_eve_rec.attribute15 := NULL;
484 END IF;
485
486 IF (l_eve_rec.created_by = okl_api.g_miss_num) THEN
487 l_eve_rec.created_by := NULL;
488 END IF;
489
490 IF (l_eve_rec.creation_date = okl_api.g_miss_date) THEN
491 l_eve_rec.creation_date := NULL;
492 END IF;
493
494 IF (l_eve_rec.last_updated_by = okl_api.g_miss_num) THEN
495 l_eve_rec.last_updated_by := NULL;
496 END IF;
497
498 IF (l_eve_rec.last_update_date = okl_api.g_miss_date) THEN
499 l_eve_rec.last_update_date := NULL;
500 END IF;
501
502 IF (l_eve_rec.last_update_login = okl_api.g_miss_num) THEN
503 l_eve_rec.last_update_login := NULL;
504 END IF;
505 RETURN(l_eve_rec);
506 END null_out_defaults;
507
508 FUNCTION get_seq_id RETURN NUMBER IS
509
510 BEGIN
511 RETURN(okc_p_util.raw_to_number(sys_guid()));
512 END get_seq_id;
513
514 FUNCTION validate_attributes(p_eve_rec IN okl_eve_rec) RETURN VARCHAR2 IS
515 l_return_status VARCHAR2(1) := okc_api.g_ret_sts_success;
516 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
517
518 BEGIN
519
520 -- validate the id
521
522 validate_end_of_term_ver_id(x_return_status => l_return_status
523 ,p_eve_rec => p_eve_rec);
524
525 -- store the highest degree of error
526
527 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
528 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
529 x_return_status := l_return_status;
530 END IF;
531 END IF;
532
533 -- validate version number
534
535 validate_version_number(x_return_status => l_return_status
536 ,p_eve_rec => p_eve_rec);
537
538 -- store the highest degree of error
539
540 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
541 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
542 x_return_status := l_return_status;
543 END IF;
544 END IF;
545
546 -- validate status
547
548 validate_sts_code(x_return_status => l_return_status
549 ,p_eve_rec => p_eve_rec);
550
551 -- store the highest degree of error
552
553 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
554 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
555 x_return_status := l_return_status;
556 END IF;
557 END IF;
558 validate_effective_from_date(x_return_status => l_return_status
559 ,p_eve_rec => p_eve_rec);
560
561 -- store the highest degree of error
562
563 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
564 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
565 x_return_status := l_return_status;
566 END IF;
567 END IF;
568 validate_end_of_term_id(x_return_status => l_return_status
569 ,p_eve_rec => p_eve_rec);
570
571 -- store the highest degree of error
572
573 IF (l_return_status <> okl_api.g_ret_sts_success) THEN
574 IF (x_return_status <> okl_api.g_ret_sts_unexp_error) THEN
575 x_return_status := l_return_status;
576 END IF;
577 END IF;
578 RETURN(x_return_status);
579 END validate_attributes;
580
581 FUNCTION validate_record(p_eve_rec IN okl_eve_rec) RETURN VARCHAR2 IS
582 l_return_status VARCHAR2(1) := okc_api.g_ret_sts_success;
583 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
584
585 BEGIN
586 IF (p_eve_rec.effective_to_date IS NOT NULL) THEN
587 IF (p_eve_rec.effective_from_date > p_eve_rec.effective_to_date) THEN
588 okl_api.set_message(p_app_name => g_app_name
589 ,p_msg_name => 'OKL_INVALID_EFFECTIVE_TO');
590 x_return_status := okl_api.g_ret_sts_error;
591 END IF;
592 END IF;
593 RETURN(x_return_status);
594 END validate_record;
595
596 -----------------
597 -- lock_row (REC)
598 -----------------
599
600 PROCEDURE lock_row(p_api_version IN NUMBER
601 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
602 ,x_return_status OUT NOCOPY VARCHAR2
603 ,x_msg_count OUT NOCOPY NUMBER
604 ,x_msg_data OUT NOCOPY VARCHAR2
605 ,p_def_eve_rec IN okl_eve_rec) IS
606 l_api_name CONSTANT VARCHAR2(61) := g_pkg_name || '.' || 'lock_row (REC)';
607 e_resource_busy EXCEPTION;
608
609 PRAGMA EXCEPTION_INIT(e_resource_busy, - 00054);
610
611 CURSOR lock_csr(p_def_eve_rec IN okl_eve_rec) IS
612 SELECT object_version_number
613 FROM okl_fe_eo_term_vers
614 WHERE end_of_term_ver_id = p_def_eve_rec.end_of_term_ver_id
615 AND object_version_number = p_def_eve_rec.object_version_number
616 FOR UPDATE OF object_version_number NOWAIT;
617
618 CURSOR lchk_csr(p_def_eve_rec IN okl_eve_rec) IS
619 SELECT object_version_number
620 FROM okl_fe_eo_term_vers
621 WHERE end_of_term_ver_id = p_def_eve_rec.end_of_term_ver_id;
622 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
623 l_object_version_number okl_fe_item_residual_all.object_version_number%TYPE;
624 lc_object_version_number okl_fe_item_residual_all.object_version_number%TYPE;
625 l_row_notfound BOOLEAN := false;
626 lc_row_notfound BOOLEAN := false;
627
628 BEGIN
629
630 BEGIN
631 OPEN lock_csr(p_def_eve_rec);
632 FETCH lock_csr INTO l_object_version_number ;
633 l_row_notfound := lock_csr%NOTFOUND;
634 CLOSE lock_csr;
635 EXCEPTION
636 WHEN e_resource_busy THEN
637
638 IF (lock_csr%ISOPEN) THEN
639 CLOSE lock_csr;
640 END IF;
641 okl_api.set_message(g_fnd_app
642 ,g_form_unable_to_reserve_rec);
643 RAISE app_exceptions.record_lock_exception;
644 END;
645
646 IF (l_row_notfound) THEN
647 OPEN lchk_csr(p_def_eve_rec);
648 FETCH lchk_csr INTO lc_object_version_number ;
649 lc_row_notfound := lchk_csr%NOTFOUND;
650 CLOSE lchk_csr;
651 END IF;
652
653 IF (lc_row_notfound) THEN
654 okl_api.set_message(g_fnd_app
655 ,g_form_record_deleted);
656 RAISE okl_api.g_exception_error;
657 ELSIF lc_object_version_number > p_def_eve_rec.object_version_number THEN
658 okl_api.set_message(g_fnd_app
659 ,g_form_record_changed);
660 RAISE okl_api.g_exception_error;
661 ELSIF lc_object_version_number <> p_def_eve_rec.object_version_number THEN
662 okl_api.set_message(g_fnd_app
663 ,g_form_record_changed);
664 RAISE okl_api.g_exception_error;
665 ELSIF lc_object_version_number = - 1 THEN
666 okl_api.set_message(g_app_name
667 ,g_record_logically_deleted);
668 RAISE okl_api.g_exception_error;
669 END IF;
670 x_return_status := l_return_status;
671 EXCEPTION
672 WHEN okl_api.g_exception_error THEN
673 x_return_status := g_ret_sts_error;
674 WHEN okl_api.g_exception_unexpected_error THEN
675 x_return_status := g_ret_sts_unexp_error;
676 WHEN OTHERS THEN
677 okl_api.set_message(p_app_name => g_app_name
678 ,p_msg_name => g_db_error
679 ,p_token1 => g_prog_name_token
680 ,p_token1_value => l_api_name
681 ,p_token2 => g_sqlcode_token
682 ,p_token2_value => sqlcode
683 ,p_token3 => g_sqlerrm_token
684 ,p_token3_value => sqlerrm);
685 x_return_status := g_ret_sts_unexp_error;
686 END lock_row;
687
688 -----------------
689 -- lock_row (TBL)
690 -----------------
691
692 PROCEDURE lock_row(p_api_version IN NUMBER
693 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
694 ,x_return_status OUT NOCOPY VARCHAR2
695 ,x_msg_count OUT NOCOPY NUMBER
696 ,x_msg_data OUT NOCOPY VARCHAR2
697 ,okl_eve_tbl IN okl_eve_tbl) IS
698 l_api_name CONSTANT VARCHAR2(61) := g_pkg_name || '.' || 'lock_row (TBL)';
699 l_return_status VARCHAR2(1) := g_ret_sts_success;
700 i BINARY_INTEGER;
701
702 BEGIN
703
704 IF (okl_eve_tbl.COUNT > 0) THEN
705 i := okl_eve_tbl.FIRST;
706
707 LOOP
708 IF okl_eve_tbl.EXISTS(i) THEN
709 lock_row(p_api_version => g_api_version
710 ,p_init_msg_list => g_false
711 ,x_return_status => l_return_status
712 ,x_msg_count => x_msg_count
713 ,x_msg_data => x_msg_data
714 ,p_def_eve_rec => okl_eve_tbl(i));
715 IF l_return_status = g_ret_sts_unexp_error THEN
716 RAISE okl_api.g_exception_unexpected_error;
717 ELSIF l_return_status = g_ret_sts_error THEN
718 RAISE okl_api.g_exception_error;
719 END IF;
720 EXIT WHEN(i = okl_eve_tbl.LAST);
721 i := okl_eve_tbl.next(i);
722 END IF;
723 END LOOP;
724
725 END IF;
726 x_return_status := l_return_status;
727 EXCEPTION
728 WHEN okl_api.g_exception_error THEN
729 x_return_status := g_ret_sts_error;
730 WHEN okl_api.g_exception_unexpected_error THEN
731 x_return_status := g_ret_sts_unexp_error;
732 WHEN OTHERS THEN
733 okl_api.set_message(p_app_name => g_app_name
734 ,p_msg_name => g_db_error
735 ,p_token1 => g_prog_name_token
736 ,p_token1_value => l_api_name
737 ,p_token2 => g_sqlcode_token
738 ,p_token2_value => sqlcode
739 ,p_token3 => g_sqlerrm_token
740 ,p_token3_value => sqlerrm);
741 x_return_status := g_ret_sts_unexp_error;
742 END lock_row;
743
744 --------------------------------------------------------------------------------
745 -- Procedure insert_row
746 --------------------------------------------------------------------------------
747
748 PROCEDURE insert_row(p_api_version IN NUMBER
749 ,p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false
750 ,x_return_status OUT NOCOPY VARCHAR2
751 ,x_msg_count OUT NOCOPY NUMBER
752 ,x_msg_data OUT NOCOPY VARCHAR2
753 ,p_eve_rec IN okl_eve_rec
754 ,x_eve_rec OUT NOCOPY okl_eve_rec) IS
755 l_api_version CONSTANT NUMBER := 1;
756 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
757 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
758 l_eve_rec okl_eve_rec := p_eve_rec;
759 l_def_eve_rec okl_eve_rec;
760
761 FUNCTION fill_who_columns(p_eve_rec IN okl_eve_rec) RETURN okl_eve_rec IS
762 l_eve_rec okl_eve_rec := p_eve_rec;
763
764 BEGIN
765 l_eve_rec.creation_date := SYSDATE;
766 l_eve_rec.created_by := fnd_global.user_id;
767 l_eve_rec.last_update_date := SYSDATE;
768 l_eve_rec.last_updated_by := fnd_global.user_id;
769 l_eve_rec.last_update_login := fnd_global.login_id;
770 RETURN(l_eve_rec);
771 END fill_who_columns;
772
773 FUNCTION set_attributes(p_eve_rec IN okl_eve_rec
774 ,x_eve_rec OUT NOCOPY okl_eve_rec) RETURN VARCHAR2 IS
775 l_return_status VARCHAR2(1) := okc_api.g_ret_sts_success;
776
777 BEGIN
778 x_eve_rec := p_eve_rec;
779 x_eve_rec.object_version_number := 1;
780 RETURN(l_return_status);
781 END set_attributes;
782
783 BEGIN
784 l_return_status := okc_api.start_activity(l_api_name
785 ,g_pkg_name
786 ,p_init_msg_list
787 ,l_api_version
788 ,p_api_version
789 ,'_PVT'
790 ,x_return_status);
791
792 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
793 RAISE okc_api.g_exception_unexpected_error;
794 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
795 RAISE okc_api.g_exception_error;
796 END IF;
797 l_return_status := set_attributes(p_eve_rec
798 ,l_eve_rec);
799
800 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
801 RAISE okc_api.g_exception_unexpected_error;
802 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
803 RAISE okc_api.g_exception_error;
804 END IF;
805 l_eve_rec := null_out_defaults(l_eve_rec);
806
807 -- Set Primary key value
808
809 l_eve_rec.end_of_term_ver_id := get_seq_id;
810
811 --Setting Item Attributes
812
813 l_return_status := set_attributes(l_eve_rec
814 ,l_def_eve_rec);
815
816 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
817 RAISE okc_api.g_exception_unexpected_error;
818 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
819 RAISE okc_api.g_exception_error;
820 END IF;
821 l_def_eve_rec := fill_who_columns(l_def_eve_rec);
822 l_return_status := validate_attributes(l_def_eve_rec);
823
824 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
825 RAISE okc_api.g_exception_unexpected_error;
826 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
827 RAISE okc_api.g_exception_error;
828 END IF;
829 l_return_status := validate_record(l_def_eve_rec);
830
831 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
832 RAISE okc_api.g_exception_unexpected_error;
833 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
834 RAISE okc_api.g_exception_error;
835 END IF;
836
837 INSERT INTO okl_fe_eo_term_vers
838 (end_of_term_ver_id
839 ,version_number
840 ,object_version_number
841 ,effective_from_date
842 ,effective_to_date
843 ,sts_code
844 ,end_of_term_id
845 ,attribute_category
846 ,attribute1
847 ,attribute2
848 ,attribute3
849 ,attribute4
850 ,attribute5
851 ,attribute6
852 ,attribute7
853 ,attribute8
854 ,attribute9
855 ,attribute10
856 ,attribute11
857 ,attribute12
858 ,attribute13
859 ,attribute14
860 ,attribute15
861 ,created_by
862 ,creation_date
863 ,last_updated_by
864 ,last_update_date
865 ,last_update_login)
866 VALUES (l_def_eve_rec.end_of_term_ver_id
867 ,l_def_eve_rec.version_number
868 ,l_def_eve_rec.object_version_number
869 ,l_def_eve_rec.effective_from_date
870 ,l_def_eve_rec.effective_to_date
871 ,l_def_eve_rec.sts_code
872 ,l_def_eve_rec.end_of_term_id
873 ,l_def_eve_rec.attribute_category
874 ,l_def_eve_rec.attribute1
875 ,l_def_eve_rec.attribute2
876 ,l_def_eve_rec.attribute3
877 ,l_def_eve_rec.attribute4
878 ,l_def_eve_rec.attribute5
879 ,l_def_eve_rec.attribute6
880 ,l_def_eve_rec.attribute7
881 ,l_def_eve_rec.attribute8
882 ,l_def_eve_rec.attribute9
883 ,l_def_eve_rec.attribute10
884 ,l_def_eve_rec.attribute11
885 ,l_def_eve_rec.attribute12
886 ,l_def_eve_rec.attribute13
887 ,l_def_eve_rec.attribute14
888 ,l_def_eve_rec.attribute15
889 ,l_def_eve_rec.created_by
890 ,l_def_eve_rec.creation_date
891 ,l_def_eve_rec.last_updated_by
892 ,l_def_eve_rec.last_update_date
893 ,l_def_eve_rec.last_update_login);
894
895 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
896 RAISE okc_api.g_exception_unexpected_error;
897 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
898 RAISE okc_api.g_exception_error;
899 END IF;
900
901 --Set OUT Values
902
903 x_eve_rec := l_def_eve_rec;
904 okc_api.end_activity(x_msg_count
905 ,x_msg_data);
906 EXCEPTION
907 WHEN g_exception_halt_validation THEN
908
909 -- No action necessary. Validation can continue to next attribute/column
910
911 NULL;
912 WHEN okc_api.g_exception_error THEN
913 x_return_status := okl_api.handle_exceptions(l_api_name
914 ,g_pkg_name
915 ,'OKL_API.G_RET_STS_ERROR'
916 ,x_msg_count
917 ,x_msg_data
918 ,'_PVT');
919 WHEN okc_api.g_exception_unexpected_error THEN
920 x_return_status := okl_api.handle_exceptions(l_api_name
921 ,g_pkg_name
922 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
923 ,x_msg_count
924 ,x_msg_data
925 ,'_PVT');
926 WHEN OTHERS THEN
927 x_return_status := okl_api.handle_exceptions(l_api_name
928 ,g_pkg_name
929 ,'OTHERS'
930 ,x_msg_count
931 ,x_msg_data
932 ,'_PVT');
933 END insert_row;
934
935 --------------------------------------------------------------------------------
936 -- Procedure insert_row_tbl
937 --------------------------------------------------------------------------------
938
939 PROCEDURE insert_row(p_api_version IN NUMBER
940 ,p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false
941 ,x_return_status OUT NOCOPY VARCHAR2
942 ,x_msg_count OUT NOCOPY NUMBER
943 ,x_msg_data OUT NOCOPY VARCHAR2
944 ,p_eve_tbl IN okl_eve_tbl
945 ,x_eve_tbl OUT NOCOPY okl_eve_tbl) IS
946 l_api_version CONSTANT NUMBER := 1;
947 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
948 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
949 i NUMBER := 0;
950 l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
951
952 BEGIN
953 okc_api.init_msg_list(p_init_msg_list);
954
955 -- Make sure PL/SQL table has records in it before passing
956
957 IF (p_eve_tbl.COUNT > 0) THEN
958 i := p_eve_tbl.FIRST;
959
960 LOOP
961 insert_row(p_api_version => p_api_version
962 ,p_init_msg_list => okc_api.g_false
963 ,x_return_status => x_return_status
964 ,x_msg_count => x_msg_count
965 ,x_msg_data => x_msg_data
966 ,p_eve_rec => p_eve_tbl(i)
967 ,x_eve_rec => x_eve_tbl(i));
968 IF x_return_status <> okc_api.g_ret_sts_success THEN
969 IF l_overall_status <> okc_api.g_ret_sts_unexp_error THEN
970 l_overall_status := x_return_status;
971 END IF;
972 END IF;
973 EXIT WHEN(i = p_eve_tbl.LAST);
974 i := p_eve_tbl.next(i);
975 END LOOP;
976 x_return_status := l_overall_status;
977 END IF;
978
979 EXCEPTION
980 WHEN g_exception_halt_validation THEN
981
982 -- No action necessary. Validation can continue to next attribute/column
983
984 NULL;
985 WHEN okc_api.g_exception_error THEN
986 x_return_status := okl_api.handle_exceptions(l_api_name
987 ,g_pkg_name
988 ,'OKL_API.G_RET_STS_ERROR'
989 ,x_msg_count
990 ,x_msg_data
991 ,'_PVT');
992 WHEN okc_api.g_exception_unexpected_error THEN
993 x_return_status := okl_api.handle_exceptions(l_api_name
994 ,g_pkg_name
995 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
996 ,x_msg_count
997 ,x_msg_data
998 ,'_PVT');
999 WHEN OTHERS THEN
1000 x_return_status := okl_api.handle_exceptions(l_api_name
1001 ,g_pkg_name
1002 ,'OTHERS'
1003 ,x_msg_count
1004 ,x_msg_data
1005 ,'_PVT');
1006 END insert_row;
1007
1008 --------------------------------------------------------------------------------
1009 -- Procedure update_row
1010 --------------------------------------------------------------------------------
1011
1012 PROCEDURE update_row(p_api_version IN NUMBER
1013 ,p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false
1014 ,x_return_status OUT NOCOPY VARCHAR2
1015 ,x_msg_count OUT NOCOPY NUMBER
1016 ,x_msg_data OUT NOCOPY VARCHAR2
1017 ,p_eve_rec IN okl_eve_rec
1018 ,x_eve_rec OUT NOCOPY okl_eve_rec) IS
1019 l_api_version CONSTANT NUMBER := 1;
1020 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
1021 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1022 l_eve_rec okl_eve_rec := p_eve_rec;
1023 l_def_eve_rec okl_eve_rec;
1024 lx_eve_rec okl_eve_rec;
1025
1026 FUNCTION fill_who_columns(p_eve_rec IN okl_eve_rec) RETURN okl_eve_rec IS
1027 l_eve_rec okl_eve_rec := p_eve_rec;
1028
1029 BEGIN
1030 l_eve_rec.last_update_date := SYSDATE;
1031 l_eve_rec.last_updated_by := fnd_global.user_id;
1032 l_eve_rec.last_update_login := fnd_global.login_id;
1033 RETURN(l_eve_rec);
1034 END fill_who_columns;
1035
1036 FUNCTION populate_new_record(p_eve_rec IN okl_eve_rec
1037 ,x_eve_rec OUT NOCOPY okl_eve_rec) RETURN VARCHAR2 IS
1038 l_eve_rec okl_eve_rec;
1039 l_row_notfound BOOLEAN := true;
1040 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1041
1042 BEGIN
1043 x_eve_rec := p_eve_rec;
1044
1045 --Get current database values
1046
1047 l_eve_rec := get_rec(p_eve_rec
1048 ,l_row_notfound);
1049
1050 IF (l_row_notfound) THEN
1051 l_return_status := okl_api.g_ret_sts_unexp_error;
1052 END IF;
1053
1054 IF (x_eve_rec.end_of_term_ver_id IS NULL) THEN
1055 x_eve_rec.end_of_term_ver_id := l_eve_rec.end_of_term_ver_id;
1056 END IF;
1057
1058 IF (x_eve_rec.object_version_number IS NULL) THEN
1059 x_eve_rec.object_version_number := l_eve_rec.object_version_number;
1060 END IF;
1061
1062 IF (x_eve_rec.version_number IS NULL) THEN
1063 x_eve_rec.version_number := l_eve_rec.version_number;
1064 END IF;
1065
1066 IF (x_eve_rec.effective_from_date IS NULL) THEN
1067 x_eve_rec.effective_from_date := l_eve_rec.effective_from_date;
1068 END IF;
1069
1070 IF (x_eve_rec.effective_to_date IS NULL) THEN
1071 x_eve_rec.effective_to_date := l_eve_rec.effective_to_date;
1072 END IF;
1073
1074 IF (x_eve_rec.sts_code IS NULL) THEN
1075 x_eve_rec.sts_code := l_eve_rec.sts_code;
1076 END IF;
1077
1078 IF (x_eve_rec.end_of_term_id IS NULL) THEN
1079 x_eve_rec.end_of_term_id := l_eve_rec.end_of_term_id;
1080 END IF;
1081
1082 IF (x_eve_rec.attribute_category IS NULL) THEN
1083 x_eve_rec.attribute_category := l_eve_rec.attribute_category;
1084 END IF;
1085
1086 IF (x_eve_rec.attribute1 IS NULL) THEN
1087 x_eve_rec.attribute1 := l_eve_rec.attribute1;
1088 END IF;
1089
1090 IF (x_eve_rec.attribute2 IS NULL) THEN
1091 x_eve_rec.attribute2 := l_eve_rec.attribute2;
1092 END IF;
1093
1094 IF (x_eve_rec.attribute3 IS NULL) THEN
1095 x_eve_rec.attribute3 := l_eve_rec.attribute3;
1096 END IF;
1097
1098 IF (x_eve_rec.attribute4 IS NULL) THEN
1099 x_eve_rec.attribute4 := l_eve_rec.attribute4;
1100 END IF;
1101
1102 IF (x_eve_rec.attribute5 IS NULL) THEN
1103 x_eve_rec.attribute5 := l_eve_rec.attribute5;
1104 END IF;
1105
1106 IF (x_eve_rec.attribute6 IS NULL) THEN
1107 x_eve_rec.attribute6 := l_eve_rec.attribute6;
1108 END IF;
1109
1110 IF (x_eve_rec.attribute7 IS NULL) THEN
1111 x_eve_rec.attribute7 := l_eve_rec.attribute7;
1112 END IF;
1113
1114 IF (x_eve_rec.attribute8 IS NULL) THEN
1115 x_eve_rec.attribute8 := l_eve_rec.attribute8;
1116 END IF;
1117
1118 IF (x_eve_rec.attribute9 IS NULL) THEN
1119 x_eve_rec.attribute9 := l_eve_rec.attribute9;
1120 END IF;
1121
1122 IF (x_eve_rec.attribute10 IS NULL) THEN
1123 x_eve_rec.attribute10 := l_eve_rec.attribute10;
1124 END IF;
1125
1126 IF (x_eve_rec.attribute11 IS NULL) THEN
1127 x_eve_rec.attribute11 := l_eve_rec.attribute11;
1128 END IF;
1129
1130 IF (x_eve_rec.attribute12 IS NULL) THEN
1131 x_eve_rec.attribute12 := l_eve_rec.attribute12;
1132 END IF;
1133
1134 IF (x_eve_rec.attribute13 IS NULL) THEN
1135 x_eve_rec.attribute13 := l_eve_rec.attribute13;
1136 END IF;
1137
1138 IF (x_eve_rec.attribute14 IS NULL) THEN
1139 x_eve_rec.attribute14 := l_eve_rec.attribute14;
1140 END IF;
1141
1142 IF (x_eve_rec.attribute15 IS NULL) THEN
1143 x_eve_rec.attribute15 := l_eve_rec.attribute15;
1144 END IF;
1145
1146 IF (x_eve_rec.created_by IS NULL) THEN
1147 x_eve_rec.created_by := l_eve_rec.created_by;
1148 END IF;
1149
1150 IF (x_eve_rec.creation_date IS NULL) THEN
1151 x_eve_rec.creation_date := l_eve_rec.creation_date;
1152 END IF;
1153
1154 IF (x_eve_rec.last_updated_by IS NULL) THEN
1155 x_eve_rec.last_updated_by := l_eve_rec.last_updated_by;
1156 END IF;
1157
1158 IF (x_eve_rec.last_update_date IS NULL) THEN
1159 x_eve_rec.last_update_date := l_eve_rec.last_update_date;
1160 END IF;
1161
1162 IF (x_eve_rec.last_update_login IS NULL) THEN
1163 x_eve_rec.last_update_login := l_eve_rec.last_update_login;
1164 END IF;
1165 RETURN(l_return_status);
1166 END populate_new_record;
1167
1168 FUNCTION set_attributes(p_eve_rec IN okl_eve_rec
1169 ,x_eve_rec OUT NOCOPY okl_eve_rec) RETURN VARCHAR2 IS
1170 l_return_status VARCHAR2(1) := okc_api.g_ret_sts_success;
1171
1172 BEGIN
1173 x_eve_rec := p_eve_rec;
1174 RETURN(l_return_status);
1175 END set_attributes;
1176
1177 BEGIN
1178 l_return_status := okc_api.start_activity(l_api_name
1179 ,g_pkg_name
1180 ,p_init_msg_list
1181 ,l_api_version
1182 ,p_api_version
1183 ,'_PVT'
1184 ,x_return_status);
1185
1186 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1187 RAISE okc_api.g_exception_unexpected_error;
1188 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1189 RAISE okc_api.g_exception_error;
1190 END IF;
1191
1192 --Setting Item Attributes
1193
1194 l_return_status := set_attributes(l_eve_rec
1195 ,lx_eve_rec);
1196
1197 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1198 RAISE okc_api.g_exception_unexpected_error;
1199 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1200 RAISE okc_api.g_exception_error;
1201 END IF;
1202 l_return_status := populate_new_record(lx_eve_rec
1203 ,l_def_eve_rec);
1204
1205 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1206 RAISE okc_api.g_exception_unexpected_error;
1207 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1208 RAISE okc_api.g_exception_error;
1209 END IF;
1210 l_def_eve_rec := null_out_defaults(l_def_eve_rec);
1211
1212 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1213 RAISE okl_api.g_exception_unexpected_error;
1214 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1215 RAISE okl_api.g_exception_error;
1216 END IF;
1217 l_def_eve_rec := fill_who_columns(l_def_eve_rec);
1218 l_return_status := validate_attributes(l_def_eve_rec);
1219
1220 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1221 RAISE okc_api.g_exception_unexpected_error;
1222 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1223 RAISE okc_api.g_exception_error;
1224 END IF;
1225 l_return_status := validate_record(l_def_eve_rec);
1226
1227 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1228 RAISE okc_api.g_exception_unexpected_error;
1229 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1230 RAISE okc_api.g_exception_error;
1231 END IF;
1232
1233 -- Lock the row before updating
1234
1235 lock_row(p_api_version => g_api_version
1236 ,p_init_msg_list => g_false
1237 ,x_return_status => l_return_status
1238 ,x_msg_count => x_msg_count
1239 ,x_msg_data => x_msg_data
1240 ,p_def_eve_rec => l_def_eve_rec);
1241
1242 IF (l_return_status = g_ret_sts_unexp_error) THEN
1243 RAISE okl_api.g_exception_unexpected_error;
1244 ELSIF (l_return_status = g_ret_sts_error) THEN
1245 RAISE okl_api.g_exception_error;
1246 END IF;
1247
1248 UPDATE okl_fe_eo_term_vers
1249 SET end_of_term_ver_id = l_def_eve_rec.end_of_term_ver_id
1250 ,object_version_number = l_def_eve_rec.object_version_number + 1
1251 ,version_number = l_def_eve_rec.version_number
1252 ,effective_from_date = l_def_eve_rec.effective_from_date
1253 ,effective_to_date = l_def_eve_rec.effective_to_date
1254 ,sts_code = l_def_eve_rec.sts_code
1255 ,end_of_term_id = l_def_eve_rec.end_of_term_id
1256 ,attribute_category = l_def_eve_rec.attribute_category
1257 ,attribute1 = l_def_eve_rec.attribute1
1258 ,attribute2 = l_def_eve_rec.attribute2
1259 ,attribute3 = l_def_eve_rec.attribute3
1260 ,attribute4 = l_def_eve_rec.attribute4
1261 ,attribute5 = l_def_eve_rec.attribute5
1262 ,attribute6 = l_def_eve_rec.attribute6
1263 ,attribute7 = l_def_eve_rec.attribute7
1264 ,attribute8 = l_def_eve_rec.attribute8
1265 ,attribute9 = l_def_eve_rec.attribute9
1266 ,attribute10 = l_def_eve_rec.attribute10
1267 ,attribute11 = l_def_eve_rec.attribute11
1268 ,attribute12 = l_def_eve_rec.attribute12
1269 ,attribute13 = l_def_eve_rec.attribute13
1270 ,attribute14 = l_def_eve_rec.attribute14
1271 ,attribute15 = l_def_eve_rec.attribute15
1272 ,created_by = l_def_eve_rec.created_by
1273 ,creation_date = l_def_eve_rec.creation_date
1274 ,last_updated_by = l_def_eve_rec.last_updated_by
1275 ,last_update_date = l_def_eve_rec.last_update_date
1276 ,last_update_login = l_def_eve_rec.last_update_login
1277 WHERE end_of_term_ver_id = l_def_eve_rec.end_of_term_ver_id;
1278
1279 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1280 RAISE okc_api.g_exception_unexpected_error;
1281 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1282 RAISE okc_api.g_exception_error;
1283 END IF;
1284
1285 --Set OUT Values
1286
1287 x_eve_rec := l_def_eve_rec;
1288 okc_api.end_activity(x_msg_count
1289 ,x_msg_data);
1290 EXCEPTION
1291 WHEN g_exception_halt_validation THEN
1292
1293 -- No action necessary. Validation can continue to next attribute/column
1294
1295 NULL;
1296 WHEN okc_api.g_exception_error THEN
1297 x_return_status := okl_api.handle_exceptions(l_api_name
1298 ,g_pkg_name
1299 ,'OKL_API.G_RET_STS_ERROR'
1300 ,x_msg_count
1301 ,x_msg_data
1302 ,'_PVT');
1303 WHEN okc_api.g_exception_unexpected_error THEN
1304 x_return_status := okl_api.handle_exceptions(l_api_name
1305 ,g_pkg_name
1306 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1307 ,x_msg_count
1308 ,x_msg_data
1309 ,'_PVT');
1310 WHEN OTHERS THEN
1311 x_return_status := okl_api.handle_exceptions(l_api_name
1312 ,g_pkg_name
1313 ,'OTHERS'
1314 ,x_msg_count
1315 ,x_msg_data
1316 ,'_PVT');
1317 END update_row;
1318
1319 --------------------------------------------------------------------------------
1320 -- Procedure insert_row_tbl
1321 --------------------------------------------------------------------------------
1322
1323 PROCEDURE update_row(p_api_version IN NUMBER
1324 ,p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false
1325 ,x_return_status OUT NOCOPY VARCHAR2
1326 ,x_msg_count OUT NOCOPY NUMBER
1327 ,x_msg_data OUT NOCOPY VARCHAR2
1328 ,p_eve_tbl IN okl_eve_tbl
1329 ,x_eve_tbl OUT NOCOPY okl_eve_tbl) IS
1330 l_api_version CONSTANT NUMBER := 1;
1331 l_api_name CONSTANT VARCHAR2(30) := 'update_row_tbl';
1332 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1333 i NUMBER := 0;
1334 l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1335
1336 BEGIN
1337 okl_api.init_msg_list(p_init_msg_list);
1338
1339 -- Make sure PL/SQL table has records in it before passing
1340
1341 IF (p_eve_tbl.COUNT > 0) THEN
1342 i := p_eve_tbl.FIRST;
1343
1344 LOOP
1345 update_row(p_api_version => p_api_version
1346 ,p_init_msg_list => okc_api.g_false
1347 ,x_return_status => x_return_status
1348 ,x_msg_count => x_msg_count
1349 ,x_msg_data => x_msg_data
1350 ,p_eve_rec => p_eve_tbl(i)
1351 ,x_eve_rec => x_eve_tbl(i));
1352 IF x_return_status <> okc_api.g_ret_sts_success THEN
1353 IF l_overall_status <> okc_api.g_ret_sts_unexp_error THEN
1354 l_overall_status := x_return_status;
1355 END IF;
1356 END IF;
1357 EXIT WHEN(i = p_eve_tbl.LAST);
1358 i := p_eve_tbl.next(i);
1359 END LOOP;
1360 x_return_status := l_overall_status;
1361 END IF;
1362
1363 EXCEPTION
1364 WHEN g_exception_halt_validation THEN
1365
1366 -- No action necessary. Validation can continue to next attribute/column
1367
1368 NULL;
1369 WHEN okc_api.g_exception_error THEN
1370 x_return_status := okl_api.handle_exceptions(l_api_name
1371 ,g_pkg_name
1372 ,'OKL_API.G_RET_STS_ERROR'
1373 ,x_msg_count
1374 ,x_msg_data
1375 ,'_PVT');
1376 WHEN okc_api.g_exception_unexpected_error THEN
1377 x_return_status := okl_api.handle_exceptions(l_api_name
1378 ,g_pkg_name
1379 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1380 ,x_msg_count
1381 ,x_msg_data
1382 ,'_PVT');
1383 WHEN OTHERS THEN
1384 x_return_status := okl_api.handle_exceptions(l_api_name
1385 ,g_pkg_name
1386 ,'OTHERS'
1387 ,x_msg_count
1388 ,x_msg_data
1389 ,'_PVT');
1390 END update_row;
1391
1392 --------------------------------------------------------------------------------
1393 -- Procedure delete_row_v
1394 --------------------------------------------------------------------------------
1395
1396 PROCEDURE delete_row(p_api_version IN NUMBER
1397 ,p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false
1398 ,x_return_status OUT NOCOPY VARCHAR2
1399 ,x_msg_count OUT NOCOPY NUMBER
1400 ,x_msg_data OUT NOCOPY VARCHAR2
1401 ,p_eve_rec IN okl_eve_rec) IS
1402 l_api_version CONSTANT NUMBER := 1;
1403 l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
1404 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1405 l_eve_rec okl_eve_rec := p_eve_rec;
1406
1407 BEGIN
1408 l_return_status := okc_api.start_activity(l_api_name
1409 ,g_pkg_name
1410 ,p_init_msg_list
1411 ,l_api_version
1412 ,p_api_version
1413 ,'_PVT'
1414 ,x_return_status);
1415
1416 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1417 RAISE okc_api.g_exception_unexpected_error;
1418 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1419 RAISE okc_api.g_exception_error;
1420 END IF;
1421
1422 DELETE FROM okl_fe_eo_term_vers
1423 WHERE end_of_term_ver_id = l_eve_rec.end_of_term_ver_id;
1424
1425 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1426 RAISE okc_api.g_exception_unexpected_error;
1427 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1428 RAISE okc_api.g_exception_error;
1429 END IF;
1430 okc_api.end_activity(x_msg_count
1431 ,x_msg_data);
1432 EXCEPTION
1433 WHEN g_exception_halt_validation THEN
1434
1435 -- No action necessary. Validation can continue to next attribute/column
1436
1437 NULL;
1438 WHEN okc_api.g_exception_error THEN
1439 x_return_status := okl_api.handle_exceptions(l_api_name
1440 ,g_pkg_name
1441 ,'OKL_API.G_RET_STS_ERROR'
1442 ,x_msg_count
1443 ,x_msg_data
1444 ,'_PVT');
1445 WHEN okc_api.g_exception_unexpected_error THEN
1446 x_return_status := okl_api.handle_exceptions(l_api_name
1447 ,g_pkg_name
1448 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1449 ,x_msg_count
1450 ,x_msg_data
1451 ,'_PVT');
1452 WHEN OTHERS THEN
1453 x_return_status := okl_api.handle_exceptions(l_api_name
1454 ,g_pkg_name
1455 ,'OTHERS'
1456 ,x_msg_count
1457 ,x_msg_data
1458 ,'_PVT');
1459 END delete_row;
1460
1461 --------------------------------------------------------------------------------
1462 -- Procedure delete_row_tbl
1463 --------------------------------------------------------------------------------
1464
1465 PROCEDURE delete_row(p_api_version IN NUMBER
1466 ,p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false
1467 ,x_return_status OUT NOCOPY VARCHAR2
1468 ,x_msg_count OUT NOCOPY NUMBER
1469 ,x_msg_data OUT NOCOPY VARCHAR2
1470 ,p_eve_tbl IN okl_eve_tbl) IS
1471 l_api_version CONSTANT NUMBER := 1;
1472 l_api_name CONSTANT VARCHAR2(30) := 'delete_row_tbl';
1473 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1474 i NUMBER := 0;
1475 l_overall_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1476
1477 BEGIN
1478 okc_api.init_msg_list(p_init_msg_list);
1479
1480 -- Make sure PL/SQL table has records in it before passing
1481
1482 IF (p_eve_tbl.COUNT > 0) THEN
1483 i := p_eve_tbl.FIRST;
1484
1485 LOOP
1486 delete_row(p_api_version => p_api_version
1487 ,p_init_msg_list => okc_api.g_false
1488 ,x_return_status => x_return_status
1489 ,x_msg_count => x_msg_count
1490 ,x_msg_data => x_msg_data
1491 ,p_eve_rec => p_eve_tbl(i));
1492 IF x_return_status <> okc_api.g_ret_sts_success THEN
1493 IF l_overall_status <> okc_api.g_ret_sts_unexp_error THEN
1494 l_overall_status := x_return_status;
1495 END IF;
1496 END IF;
1497 EXIT WHEN(i = p_eve_tbl.LAST);
1498 i := p_eve_tbl.next(i);
1499 END LOOP;
1500 x_return_status := l_overall_status;
1501 END IF;
1502
1503 EXCEPTION
1504 WHEN g_exception_halt_validation THEN
1505
1506 -- No action necessary. Validation can continue to next attribute/column
1507
1508 NULL;
1509 WHEN okc_api.g_exception_error THEN
1510 x_return_status := okl_api.handle_exceptions(l_api_name
1511 ,g_pkg_name
1512 ,'OKL_API.G_RET_STS_ERROR'
1513 ,x_msg_count
1514 ,x_msg_data
1515 ,'_PVT');
1516 WHEN okc_api.g_exception_unexpected_error THEN
1517 x_return_status := okl_api.handle_exceptions(l_api_name
1518 ,g_pkg_name
1519 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1520 ,x_msg_count
1521 ,x_msg_data
1522 ,'_PVT');
1523 WHEN OTHERS THEN
1524 x_return_status := okl_api.handle_exceptions(l_api_name
1525 ,g_pkg_name
1526 ,'OTHERS'
1527 ,x_msg_count
1528 ,x_msg_data
1529 ,'_PVT');
1530 END delete_row;
1531
1532 END okl_eve_pvt;