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