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