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