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