[Home] [Help]
PACKAGE BODY: APPS.OKL_ECV_PVT
Source
1 PACKAGE BODY okl_ecv_pvt AS
2 /* $Header: OKLSECVB.pls 120.1 2005/10/30 04:59:23 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_CRITERION_VALUES --
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_ecv_rec IN okl_ecv_rec) IS
43 e_resource_busy EXCEPTION;
44
45 PRAGMA exception_init(e_resource_busy, - 00054);
46
47 CURSOR lock_csr(p_ecv_rec IN okl_ecv_rec) IS
48 SELECT object_version_number
49 FROM okl_fe_criterion_values
50 WHERE criterion_value_id = p_ecv_rec.criterion_value_id
51 AND object_version_number = p_ecv_rec.object_version_number
52 FOR UPDATE OF object_version_number NOWAIT;
53
54 CURSOR lchk_csr(p_ecv_rec IN okl_ecv_rec) IS
55 SELECT object_version_number
56 FROM okl_fe_criterion_values
57 WHERE criterion_value_id = p_ecv_rec.criterion_value_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_criterion_values.object_version_number%TYPE;
62 lc_object_version_number okl_fe_criterion_values.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_ecv_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_ecv_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_ecv_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_ecv_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_CRITERION_VALUES --
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_ecv_tbl IN okl_ecv_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_ecv_tbl.COUNT > 0) THEN
174 i := p_ecv_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_ecv_rec => p_ecv_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_ecv_tbl.LAST);
195 i := p_ecv_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_criterion_value_id(p_criterion_value_id IN number) RETURN varchar2 IS
243 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_CRITERION_VALUE_ID';
244
245 BEGIN
246
247 --
248 -- data is required
249
250 IF (p_criterion_value_id IS NULL) OR (p_criterion_value_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 => 'CRITERION_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_criterion_value_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_CRITERIA_ID
311 ------------------------------------------
312
313 FUNCTION validate_criteria_id(p_criteria_id IN number) RETURN varchar2 IS
314 l_dummy_var varchar2(1) := '?';
315 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_CRITERIA_ID';
316
317 -- select the ID of the parent record from the parent table
318
319 CURSOR l_ecl_csr IS
320 SELECT 'x'
321 FROM okl_fe_criteria
322 WHERE criteria_id = p_criteria_id;
323
324 BEGIN
325
326 --
327 -- data is required
328
329 IF (p_criteria_id IS NULL) OR (p_criteria_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 => 'CRITERIA_ID');
334 RAISE okl_api.g_exception_error;
335 END IF;
336
337 -- enforce foreign key
338
339 OPEN l_ecl_csr;
340 FETCH l_ecl_csr INTO l_dummy_var ;
341 CLOSE l_ecl_csr;
342
343 -- if l_dummy_var is still set to default, data was not found
344
345 IF (l_dummy_var = '?') THEN
346 okl_api.set_message(p_app_name => g_app_name
347 ,p_msg_name => g_no_parent_record
348 ,p_token1 => g_col_name_token
349 ,p_token1_value => 'CRITERIA_ID'
350 ,p_token2 => g_child_table_token
351 ,p_token2_value => 'OKL_FE_CRITERION_VALUES'
352 ,p_token3 => g_parent_table_token
353 ,p_token3_value => 'OKL_FE_CRITERIA');
354 RAISE okl_api.g_exception_error;
355 END IF;
356 RETURN g_ret_sts_success;
357 EXCEPTION
358 WHEN okl_api.g_exception_error THEN
359
360 -- verify that cursor was closed
361
362 IF l_ecl_csr%ISOPEN THEN
363 CLOSE l_ecl_csr;
364 END IF;
365 RETURN g_ret_sts_error;
366 WHEN okl_api.g_exception_unexpected_error THEN
367
368 -- verify that cursor was closed
369
370 IF l_ecl_csr%ISOPEN THEN
371 CLOSE l_ecl_csr;
372 END IF;
373 RETURN g_ret_sts_unexp_error;
374 WHEN OTHERS THEN
375
376 -- verify that cursor was closed
377
378 IF l_ecl_csr%ISOPEN THEN
379 CLOSE l_ecl_csr;
380 END IF;
381 okl_api.set_message(p_app_name => g_app_name
382 ,p_msg_name => g_db_error
383 ,p_token1 => g_prog_name_token
384 ,p_token1_value => l_api_name
385 ,p_token2 => 'SQLCODE'
386 ,p_token2_value => sqlcode
387 ,p_token3 => 'SQLERRM'
388 ,p_token3_value => sqlerrm);
389 RETURN g_ret_sts_unexp_error;
390 END validate_criteria_id;
391
392 --------------------------------------------------------------------------------
393 -- Procedure get_rec for OKL_FE_CRITERION_VALUES
394 --------------------------------------------------------------------------------
395
396 FUNCTION get_rec(p_ecv_rec IN okl_ecv_rec
397 ,x_no_data_found OUT NOCOPY boolean) RETURN okl_ecv_rec IS
398
399 CURSOR ecv_pk_csr(p_id IN number) IS
400 SELECT criterion_value_id
401 ,object_version_number
402 ,criteria_id
403 ,operator_code
404 ,crit_cat_value2
405 ,crit_cat_value1
406 ,adjustment_factor
407 ,created_by
408 ,creation_date
409 ,last_updated_by
410 ,last_update_date
411 ,last_update_login
412 ,attribute_category
413 ,attribute1
414 ,attribute2
415 ,attribute3
416 ,attribute4
417 ,attribute5
418 ,attribute6
419 ,attribute7
420 ,attribute8
421 ,attribute9
422 ,attribute10
423 ,attribute11
424 ,attribute12
425 ,attribute13
426 ,attribute14
427 ,attribute15
428 FROM okl_fe_criterion_values
429 WHERE okl_fe_criterion_values.criterion_value_id = p_id;
430 l_ecv_pk ecv_pk_csr%ROWTYPE;
431 l_ecv_rec okl_ecv_rec;
432
433 BEGIN
434 x_no_data_found := true;
435
436 --Get current data base values
437
438 OPEN ecv_pk_csr(p_ecv_rec.criterion_value_id);
439 FETCH ecv_pk_csr INTO l_ecv_rec.criterion_value_id
440 ,l_ecv_rec.object_version_number
441 ,l_ecv_rec.criteria_id
442 ,l_ecv_rec.operator_code
443 ,l_ecv_rec.crit_cat_value2
444 ,l_ecv_rec.crit_cat_value1
445 ,l_ecv_rec.adjustment_factor
446 ,l_ecv_rec.created_by
447 ,l_ecv_rec.creation_date
448 ,l_ecv_rec.last_updated_by
449 ,l_ecv_rec.last_update_date
450 ,l_ecv_rec.last_update_login
451 ,l_ecv_rec.attribute_category
452 ,l_ecv_rec.attribute1
453 ,l_ecv_rec.attribute2
454 ,l_ecv_rec.attribute3
455 ,l_ecv_rec.attribute4
456 ,l_ecv_rec.attribute5
457 ,l_ecv_rec.attribute6
458 ,l_ecv_rec.attribute7
459 ,l_ecv_rec.attribute8
460 ,l_ecv_rec.attribute9
461 ,l_ecv_rec.attribute10
462 ,l_ecv_rec.attribute11
463 ,l_ecv_rec.attribute12
464 ,l_ecv_rec.attribute13
465 ,l_ecv_rec.attribute14
466 ,l_ecv_rec.attribute15 ;
467 x_no_data_found := ecv_pk_csr%NOTFOUND;
468 CLOSE ecv_pk_csr;
469 RETURN(l_ecv_rec);
470 END get_rec;
471
472 FUNCTION get_rec(p_ecv_rec IN okl_ecv_rec) RETURN okl_ecv_rec IS
473 l_row_notfound boolean := true;
474
475 BEGIN
476 RETURN(get_rec(p_ecv_rec, l_row_notfound));
477 END get_rec;
478
479 FUNCTION null_out_defaults(p_ecv_rec IN okl_ecv_rec) RETURN okl_ecv_rec IS
480 l_ecv_rec okl_ecv_rec := p_ecv_rec;
481
482 BEGIN
483
484 IF (l_ecv_rec.criterion_value_id = okl_api.g_miss_num) THEN
485 l_ecv_rec.criterion_value_id := NULL;
486 END IF;
487
488 IF (l_ecv_rec.object_version_number = okl_api.g_miss_num) THEN
489 l_ecv_rec.object_version_number := NULL;
490 END IF;
491
492 IF (l_ecv_rec.criteria_id = okl_api.g_miss_num) THEN
493 l_ecv_rec.criteria_id := NULL;
494 END IF;
495
496 IF (l_ecv_rec.operator_code = okl_api.g_miss_char) THEN
497 l_ecv_rec.operator_code := NULL;
498 END IF;
499
500 IF (l_ecv_rec.crit_cat_value2 = okl_api.g_miss_char) THEN
501 l_ecv_rec.crit_cat_value2 := NULL;
502 END IF;
503
504 IF (l_ecv_rec.crit_cat_value1 = okl_api.g_miss_char) THEN
505 l_ecv_rec.crit_cat_value1 := NULL;
506 END IF;
507
508 IF (l_ecv_rec.adjustment_factor = okl_api.g_miss_num) THEN
509 l_ecv_rec.adjustment_factor := NULL;
510 END IF;
511
512 IF (l_ecv_rec.created_by = okl_api.g_miss_num) THEN
513 l_ecv_rec.created_by := NULL;
514 END IF;
515
516 IF (l_ecv_rec.creation_date = okl_api.g_miss_date) THEN
517 l_ecv_rec.creation_date := NULL;
518 END IF;
519
520 IF (l_ecv_rec.last_updated_by = okl_api.g_miss_num) THEN
521 l_ecv_rec.last_updated_by := NULL;
522 END IF;
523
524 IF (l_ecv_rec.last_update_date = okl_api.g_miss_date) THEN
525 l_ecv_rec.last_update_date := NULL;
526 END IF;
527
528 IF (l_ecv_rec.last_update_login = okl_api.g_miss_num) THEN
529 l_ecv_rec.last_update_login := NULL;
530 END IF;
531
532 IF (l_ecv_rec.attribute_category = g_miss_char) THEN
533 l_ecv_rec.attribute_category := NULL;
534 END IF;
535
536 IF (l_ecv_rec.attribute1 = g_miss_char) THEN
537 l_ecv_rec.attribute1 := NULL;
538 END IF;
539
540 IF (l_ecv_rec.attribute2 = g_miss_char) THEN
541 l_ecv_rec.attribute2 := NULL;
542 END IF;
543
544 IF (l_ecv_rec.attribute3 = g_miss_char) THEN
545 l_ecv_rec.attribute3 := NULL;
546 END IF;
547
548 IF (l_ecv_rec.attribute4 = g_miss_char) THEN
549 l_ecv_rec.attribute4 := NULL;
550 END IF;
551
552 IF (l_ecv_rec.attribute5 = g_miss_char) THEN
553 l_ecv_rec.attribute5 := NULL;
554 END IF;
555
556 IF (l_ecv_rec.attribute6 = g_miss_char) THEN
557 l_ecv_rec.attribute6 := NULL;
558 END IF;
559
560 IF (l_ecv_rec.attribute7 = g_miss_char) THEN
561 l_ecv_rec.attribute7 := NULL;
562 END IF;
563
564 IF (l_ecv_rec.attribute8 = g_miss_char) THEN
565 l_ecv_rec.attribute8 := NULL;
566 END IF;
567
568 IF (l_ecv_rec.attribute9 = g_miss_char) THEN
569 l_ecv_rec.attribute9 := NULL;
570 END IF;
571
572 IF (l_ecv_rec.attribute10 = g_miss_char) THEN
573 l_ecv_rec.attribute10 := NULL;
574 END IF;
575
576 IF (l_ecv_rec.attribute11 = g_miss_char) THEN
577 l_ecv_rec.attribute11 := NULL;
578 END IF;
579
580 IF (l_ecv_rec.attribute12 = g_miss_char) THEN
581 l_ecv_rec.attribute12 := NULL;
582 END IF;
583
584 IF (l_ecv_rec.attribute13 = g_miss_char) THEN
585 l_ecv_rec.attribute13 := NULL;
586 END IF;
587
588 IF (l_ecv_rec.attribute14 = g_miss_char) THEN
589 l_ecv_rec.attribute14 := NULL;
590 END IF;
591
592 IF (l_ecv_rec.attribute15 = g_miss_char) THEN
593 l_ecv_rec.attribute15 := NULL;
594 END IF;
595 RETURN(l_ecv_rec);
596 END null_out_defaults;
597
598 FUNCTION get_seq_id RETURN number IS
599
600 BEGIN
601 RETURN(okc_p_util.raw_to_number(sys_guid()));
602 END get_seq_id;
603
604 FUNCTION validate_attributes(p_ecv_rec IN okl_ecv_rec) RETURN varchar2 IS
605 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
606 x_return_status varchar2(1) := okl_api.g_ret_sts_success;
607 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_attributes';
608
609 BEGIN
610
611 -- call each column-level validation
612 --
613
614 --validate CRITERION_VALUE_ID
615
616 l_return_status := validate_criterion_value_id(p_ecv_rec.criterion_value_id);
617
618 IF (l_return_status = g_ret_sts_unexp_error) THEN
619 RAISE okl_api.g_exception_unexpected_error;
620 ELSIF (l_return_status = g_ret_sts_error) THEN
621 RAISE okl_api.g_exception_error;
622 END IF;
623
624 --validate object version number
625
626 l_return_status := validate_object_version_number(p_ecv_rec.object_version_number);
627
628 IF (l_return_status = g_ret_sts_unexp_error) THEN
629 RAISE okl_api.g_exception_unexpected_error;
630 ELSIF (l_return_status = g_ret_sts_error) THEN
631 RAISE okl_api.g_exception_error;
632 END IF;
633
634 --validate CRITERIA_ID
635
636 l_return_status := validate_criteria_id(p_ecv_rec.criteria_id);
637
638 IF (l_return_status = g_ret_sts_unexp_error) THEN
639 RAISE okl_api.g_exception_unexpected_error;
640 ELSIF (l_return_status = g_ret_sts_error) THEN
641 RAISE okl_api.g_exception_error;
642 END IF;
643 RETURN(x_return_status);
644 EXCEPTION
645 WHEN okl_api.g_exception_error THEN
646 RETURN g_ret_sts_error;
647 WHEN okl_api.g_exception_unexpected_error THEN
648 RETURN g_ret_sts_unexp_error;
649 WHEN OTHERS THEN
650 okl_api.set_message(p_app_name => g_app_name
651 ,p_msg_name => g_db_error
652 ,p_token1 => g_prog_name_token
653 ,p_token1_value => l_api_name
654 ,p_token2 => 'SQLCODE'
655 ,p_token2_value => sqlcode
656 ,p_token3 => 'SQLERRM'
657 ,p_token3_value => sqlerrm);
658 RETURN g_ret_sts_unexp_error;
659 END validate_attributes;
660
661 FUNCTION validate_record(p_ecv_rec IN OUT NOCOPY okl_ecv_rec) RETURN varchar2 IS
662 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
663 x_return_status varchar2(1) := okl_api.g_ret_sts_success;
664 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_attributes';
665 i number;
666 d date;
667
668 BEGIN
669
670 --do all the validations only if p_ecv_rec.validate_record = 'Y'
671 --in other cases data is coming from database and not from screen so CRIT_CAT_NUMVAL,
672 --CRIT_CAT_DATEVAL will not be present. so no need to the validations
673
674 IF p_ecv_rec.validate_record = 'Y' THEN
675 IF p_ecv_rec.data_type_code = 'NUMBER' THEN
676 IF p_ecv_rec.crit_cat_numval1 IS NULL OR p_ecv_rec.crit_cat_numval1 = okl_api.g_miss_num THEN
677 okl_api.set_message(p_app_name => g_app_name
678 ,p_msg_name => g_required_value
679 ,p_token1 => g_col_name_token
680 ,p_token1_value => 'CRIT_CAT_NUMVAL1');
681 RAISE okl_api.g_exception_error;
682 ELSE
683 p_ecv_rec.crit_cat_value1 := fnd_number.number_to_canonical(p_ecv_rec.crit_cat_numval1);
684 IF p_ecv_rec.crit_cat_numval2 IS NOT NULL AND NOT p_ecv_rec.crit_cat_numval2 = g_miss_num THEN
685 p_ecv_rec.crit_cat_value2 := fnd_number.number_to_canonical(p_ecv_rec.crit_cat_numval2);
686 ELSE
687 p_ecv_rec.crit_cat_value2 := NULL;
688 END IF;
689 END IF;
690 ELSIF p_ecv_rec.data_type_code = 'DATE' THEN
691 IF p_ecv_rec.crit_cat_dateval1 IS NULL OR p_ecv_rec.crit_cat_dateval1 = okl_api.g_miss_date THEN
692 okl_api.set_message(p_app_name => g_app_name
693 ,p_msg_name => g_required_value
694 ,p_token1 => g_col_name_token
695 ,p_token1_value => 'CRIT_CAT_DATEVAL1');
696 RAISE okl_api.g_exception_error;
697 ELSE
698 p_ecv_rec.crit_cat_value1 := fnd_date.date_to_canonical(p_ecv_rec.crit_cat_dateval1);
699 IF p_ecv_rec.crit_cat_dateval2 IS NOT NULL AND NOT p_ecv_rec.crit_cat_dateval2 = g_miss_date THEN
700 p_ecv_rec.crit_cat_value2 := fnd_date.date_to_canonical(p_ecv_rec.crit_cat_dateval2);
701 ELSE
702 p_ecv_rec.crit_cat_value2 := NULL;
703 END IF;
704 END IF;
705 ELSIF p_ecv_rec.data_type_code = 'VARCHAR2' THEN
706 IF p_ecv_rec.crit_cat_value1 IS NULL OR p_ecv_rec.crit_cat_value1 = okl_api.g_miss_char THEN
707 okl_api.set_message(p_app_name => g_app_name
708 ,p_msg_name => g_required_value
709 ,p_token1 => g_col_name_token
710 ,p_token1_value => 'CRIT_CAT_VALUE1');
711 RAISE okl_api.g_exception_error;
712 END IF;
713 END IF;
714
715 --if value type code is RANGE then min should be < than max
716
717 IF p_ecv_rec.value_type_code = 'RANGE' THEN
718 IF p_ecv_rec.data_type_code = 'NUMBER' THEN
719
720 --CRIT_CAT_NUMVAL2 is required for Range and Number
721
722 IF p_ecv_rec.crit_cat_numval2 IS NULL OR p_ecv_rec.crit_cat_numval2 = g_miss_num THEN
723 okl_api.set_message(p_app_name => g_app_name
724 ,p_msg_name => g_required_value
725 ,p_token1 => g_col_name_token
726 ,p_token1_value => 'CRIT_CAT_NUMVAL2');
727 RAISE okl_api.g_exception_error;
728 END IF;
729 IF p_ecv_rec.crit_cat_numval1 > p_ecv_rec.crit_cat_numval2 THEN
730 okl_api.set_message(p_app_name => g_app_name
731 ,p_msg_name => 'OKL_MIN_VAL_GRTR_THAN_MAX_VAL');
732 RAISE okl_api.g_exception_error;
733 END IF;
734 ELSIF p_ecv_rec.data_type_code = 'DATE' THEN
735
736 --CRIT_CAT_DATEVAL2 is required for Range and Number
737
738 IF p_ecv_rec.crit_cat_dateval2 IS NULL OR p_ecv_rec.crit_cat_dateval2 = g_miss_date THEN
739 okl_api.set_message(p_app_name => g_app_name
740 ,p_msg_name => g_required_value
741 ,p_token1 => g_col_name_token
742 ,p_token1_value => 'CRIT_CAT_DATEVAL2');
743 RAISE okl_api.g_exception_error;
744 END IF;
745 IF p_ecv_rec.crit_cat_dateval1 > p_ecv_rec.crit_cat_dateval2 THEN
746 okl_api.set_message(p_app_name => g_app_name
747 ,p_msg_name => 'OKL_MIN_VAL_GRTR_THAN_MAX_VAL');
748 RAISE okl_api.g_exception_error;
749 END IF;
750 END IF;
751 END IF;
752
753 --validate that if data type='VARCHAR2' and VALUE type = 'SINGLE then operator must be in (EQ,NE)
754
755 IF p_ecv_rec.data_type_code = 'VARCHAR2' THEN
756 IF p_ecv_rec.value_type_code = 'SINGLE' THEN
757 IF p_ecv_rec.operator_code NOT IN('EQ', 'NE') THEN
758 okl_api.set_message(p_app_name => g_app_name
759 ,p_msg_name => 'OKL_INVALID_OP_SINGLE_VARCHAR2');
760 RAISE okl_api.g_exception_error;
761 END IF;
762 END IF;
763 END IF;
764 END IF;
765
766 --if value type = multiple and source_yn=n then populate crit_cat_value1 into crit_Cat_value2
767
768 IF p_ecv_rec.value_type_code = 'MULTIPLE' AND p_ecv_rec.source_yn = 'N' THEN
769 p_ecv_rec.crit_cat_value2 := p_ecv_rec.crit_cat_value1;
770 END IF;
771 RETURN(x_return_status);
772 EXCEPTION
773 WHEN okl_api.g_exception_error THEN
774 RETURN g_ret_sts_error;
775 WHEN okl_api.g_exception_unexpected_error THEN
776 RETURN g_ret_sts_unexp_error;
777 WHEN OTHERS THEN
778 okl_api.set_message(p_app_name => g_app_name
779 ,p_msg_name => g_db_error
780 ,p_token1 => g_prog_name_token
781 ,p_token1_value => l_api_name
782 ,p_token2 => 'SQLCODE'
783 ,p_token2_value => sqlcode
784 ,p_token3 => 'SQLERRM'
785 ,p_token3_value => sqlerrm);
786 RETURN g_ret_sts_unexp_error;
787 END validate_record;
788
789 --------------------------------------------------------------------------------
790 -- Procedure insert_row
791 --------------------------------------------------------------------------------
792
793 PROCEDURE insert_row(p_api_version IN number
794 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
795 ,x_return_status OUT NOCOPY varchar2
796 ,x_msg_count OUT NOCOPY number
797 ,x_msg_data OUT NOCOPY varchar2
798 ,p_ecv_rec IN okl_ecv_rec
799 ,x_ecv_rec OUT NOCOPY okl_ecv_rec) IS
800 l_api_version CONSTANT number := 1;
801 l_api_name CONSTANT varchar2(30) := 'insert_row';
802 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
803 l_ecv_rec okl_ecv_rec;
804 l_def_ecv_rec okl_ecv_rec;
805
806 FUNCTION fill_who_columns(p_ecv_rec IN okl_ecv_rec) RETURN okl_ecv_rec IS
807 l_ecv_rec okl_ecv_rec := p_ecv_rec;
808
809 BEGIN
810 l_ecv_rec.creation_date := sysdate;
811 l_ecv_rec.created_by := fnd_global.user_id;
812 l_ecv_rec.last_update_date := sysdate;
813 l_ecv_rec.last_updated_by := fnd_global.user_id;
814 l_ecv_rec.last_update_login := fnd_global.login_id;
815 RETURN(l_ecv_rec);
816 END fill_who_columns;
817
818 FUNCTION set_attributes(p_ecv_rec IN okl_ecv_rec
819 ,x_ecv_rec OUT NOCOPY okl_ecv_rec) RETURN varchar2 IS
820 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
821
822 BEGIN
823 x_ecv_rec := p_ecv_rec;
824 x_ecv_rec.object_version_number := 1;
825
826 -- Set Primary key value
827
828 x_ecv_rec.criterion_value_id := get_seq_id;
829 RETURN(l_return_status);
830 END set_attributes;
831
832 BEGIN
833 l_return_status := okl_api.start_activity(l_api_name
834 ,g_pkg_name
835 ,p_init_msg_list
836 ,l_api_version
837 ,p_api_version
838 ,'_PVT'
839 ,x_return_status);
840
841 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
842 RAISE okl_api.g_exception_unexpected_error;
843 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
844 RAISE okl_api.g_exception_error;
845 END IF;
846
847 --null out defaults
848
849 l_ecv_rec := null_out_defaults(p_ecv_rec);
850
851 --Setting Item Attributes
852
853 l_return_status := set_attributes(l_ecv_rec, l_def_ecv_rec);
854
855 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
856 RAISE okl_api.g_exception_unexpected_error;
857 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
858 RAISE okl_api.g_exception_error;
859 END IF;
860
861 --fill who columns
862
863 l_def_ecv_rec := fill_who_columns(l_def_ecv_rec);
864
865 --validate attributes
866 --
867
868 l_return_status := validate_attributes(l_def_ecv_rec);
869
870 --
871
872 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
873 RAISE okl_api.g_exception_unexpected_error;
874 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
875 RAISE okl_api.g_exception_error;
876 END IF;
877
878 --validate record
879 --
880
881 l_return_status := validate_record(l_def_ecv_rec);
882
883 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
884 RAISE okl_api.g_exception_unexpected_error;
885 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
886 RAISE okl_api.g_exception_error;
887 END IF; --insert into table
888
889 INSERT INTO okl_fe_criterion_values
890 (criterion_value_id
891 ,object_version_number
892 ,criteria_id
893 ,operator_code
894 ,crit_cat_value2
895 ,crit_cat_value1
896 ,adjustment_factor
897 ,created_by
898 ,creation_date
899 ,last_updated_by
900 ,last_update_date
901 ,last_update_login
902 ,attribute_category
903 ,attribute1
904 ,attribute2
905 ,attribute3
906 ,attribute4
907 ,attribute5
908 ,attribute6
909 ,attribute7
910 ,attribute8
911 ,attribute9
912 ,attribute10
913 ,attribute11
914 ,attribute12
915 ,attribute13
916 ,attribute14
917 ,attribute15)
918 VALUES (l_def_ecv_rec.criterion_value_id
919 ,l_def_ecv_rec.object_version_number
920 ,l_def_ecv_rec.criteria_id
921 ,l_def_ecv_rec.operator_code
922 ,l_def_ecv_rec.crit_cat_value2
923 ,l_def_ecv_rec.crit_cat_value1
924 ,l_def_ecv_rec.adjustment_factor
925 ,l_def_ecv_rec.created_by
926 ,l_def_ecv_rec.creation_date
927 ,l_def_ecv_rec.last_updated_by
928 ,l_def_ecv_rec.last_update_date
929 ,l_def_ecv_rec.last_update_login
930 ,l_def_ecv_rec.attribute_category
931 ,l_def_ecv_rec.attribute1
932 ,l_def_ecv_rec.attribute2
933 ,l_def_ecv_rec.attribute3
934 ,l_def_ecv_rec.attribute4
935 ,l_def_ecv_rec.attribute5
936 ,l_def_ecv_rec.attribute6
937 ,l_def_ecv_rec.attribute7
938 ,l_def_ecv_rec.attribute8
939 ,l_def_ecv_rec.attribute9
940 ,l_def_ecv_rec.attribute10
941 ,l_def_ecv_rec.attribute11
942 ,l_def_ecv_rec.attribute12
943 ,l_def_ecv_rec.attribute13
944 ,l_def_ecv_rec.attribute14
945 ,l_def_ecv_rec.attribute15);
946
947 --Set OUT Values
948
949 x_ecv_rec := l_def_ecv_rec;
950 x_return_status := l_return_status;
951 okl_api.end_activity(x_msg_count, x_msg_data);
952 EXCEPTION
953 WHEN g_exception_halt_validation THEN
954
955 -- No action necessary. Validation can continue to next attribute/column
956
957 NULL;
958 WHEN okl_api.g_exception_error THEN
959 x_return_status := okl_api.handle_exceptions(l_api_name
960 ,g_pkg_name
961 ,'OKL_API.G_RET_STS_ERROR'
962 ,x_msg_count
963 ,x_msg_data
964 ,'_PVT');
965 WHEN okl_api.g_exception_unexpected_error THEN
966 x_return_status := okl_api.handle_exceptions(l_api_name
967 ,g_pkg_name
968 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
969 ,x_msg_count
970 ,x_msg_data
971 ,'_PVT');
972 WHEN OTHERS THEN
973 x_return_status := okl_api.handle_exceptions(l_api_name
974 ,g_pkg_name
975 ,'OTHERS'
976 ,x_msg_count
977 ,x_msg_data
978 ,'_PVT');
979 END insert_row;
980
981 --------------------------------------------------------------------------------
982 -- Procedure insert_row_tbl
983 --------------------------------------------------------------------------------
984
985 PROCEDURE insert_row(p_api_version IN number
986 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
987 ,x_return_status OUT NOCOPY varchar2
988 ,x_msg_count OUT NOCOPY number
989 ,x_msg_data OUT NOCOPY varchar2
990 ,p_ecv_tbl IN okl_ecv_tbl
991 ,x_ecv_tbl OUT NOCOPY okl_ecv_tbl) IS
992 l_api_version CONSTANT number := 1;
993 l_api_name CONSTANT varchar2(30) := 'insert_row_tbl';
994 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
995 i number := 0;
996 l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
997
998 BEGIN
999 okl_api.init_msg_list(p_init_msg_list);
1000
1001 -- Make sure PL/SQL table has records in it before passing
1002
1003 IF (p_ecv_tbl.COUNT > 0) THEN
1004 i := p_ecv_tbl.FIRST;
1005
1006 LOOP
1007 insert_row(p_api_version => p_api_version
1008 ,p_init_msg_list => okl_api.g_false
1009 ,x_return_status => x_return_status
1010 ,x_msg_count => x_msg_count
1011 ,x_msg_data => x_msg_data
1012 ,p_ecv_rec => p_ecv_tbl(i)
1013 ,x_ecv_rec => x_ecv_tbl(i));
1014 IF x_return_status <> okl_api.g_ret_sts_success THEN
1015 IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1016 l_overall_status := x_return_status;
1017 END IF;
1018 END IF;
1019 EXIT WHEN(i = p_ecv_tbl.LAST);
1020 i := p_ecv_tbl.next(i);
1021 END LOOP;
1022 x_return_status := l_overall_status;
1023 END IF;
1024
1025 EXCEPTION
1026 WHEN g_exception_halt_validation THEN
1027
1028 -- No action necessary. Validation can continue to next attribute/column
1029
1030 NULL;
1031 WHEN okl_api.g_exception_error THEN
1032 x_return_status := okl_api.handle_exceptions(l_api_name
1033 ,g_pkg_name
1034 ,'OKL_API.G_RET_STS_ERROR'
1035 ,x_msg_count
1036 ,x_msg_data
1037 ,'_PVT');
1038 WHEN okl_api.g_exception_unexpected_error THEN
1039 x_return_status := okl_api.handle_exceptions(l_api_name
1040 ,g_pkg_name
1041 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1042 ,x_msg_count
1043 ,x_msg_data
1044 ,'_PVT');
1045 WHEN OTHERS THEN
1046 x_return_status := okl_api.handle_exceptions(l_api_name
1047 ,g_pkg_name
1048 ,'OTHERS'
1049 ,x_msg_count
1050 ,x_msg_data
1051 ,'_PVT');
1052 END insert_row;
1053
1054 --------------------------------------------------------------------------------
1055 -- Procedure update_row
1056 --------------------------------------------------------------------------------
1057
1058 PROCEDURE update_row(p_api_version IN number
1059 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1060 ,x_return_status OUT NOCOPY varchar2
1061 ,x_msg_count OUT NOCOPY number
1062 ,x_msg_data OUT NOCOPY varchar2
1063 ,p_ecv_rec IN okl_ecv_rec
1064 ,x_ecv_rec OUT NOCOPY okl_ecv_rec) IS
1065 l_api_version CONSTANT number := 1;
1066 l_api_name CONSTANT varchar2(30) := 'update_row';
1067 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1068 l_ecv_rec okl_ecv_rec := p_ecv_rec;
1069 l_def_ecv_rec okl_ecv_rec;
1070 l_row_notfound boolean := true;
1071
1072 FUNCTION fill_who_columns(p_ecv_rec IN okl_ecv_rec) RETURN okl_ecv_rec IS
1073 l_ecv_rec okl_ecv_rec := p_ecv_rec;
1074
1075 BEGIN
1076 l_ecv_rec.last_update_date := sysdate;
1077 l_ecv_rec.last_updated_by := fnd_global.user_id;
1078 l_ecv_rec.last_update_login := fnd_global.login_id;
1079 RETURN(l_ecv_rec);
1080 END fill_who_columns;
1081
1082 FUNCTION populate_new_record(p_ecv_rec IN okl_ecv_rec
1083 ,x_ecv_rec OUT NOCOPY okl_ecv_rec) RETURN varchar2 IS
1084 l_ecv_rec okl_ecv_rec;
1085 l_row_notfound boolean := true;
1086 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1087
1088 BEGIN
1089 x_ecv_rec := p_ecv_rec;
1090
1091 --Get current database values
1092
1093 l_ecv_rec := get_rec(p_ecv_rec, l_row_notfound);
1094
1095 IF (l_row_notfound) THEN
1096 okl_api.set_message(g_fnd_app, g_form_record_deleted);
1097 l_return_status := okl_api.g_ret_sts_unexp_error;
1098 END IF;
1099
1100 IF (x_ecv_rec.criterion_value_id IS NULL) THEN
1101 x_ecv_rec.criterion_value_id := l_ecv_rec.criterion_value_id;
1102 END IF;
1103
1104 IF (x_ecv_rec.criteria_id IS NULL) THEN
1105 x_ecv_rec.criteria_id := l_ecv_rec.criteria_id;
1106 END IF;
1107
1108 IF (x_ecv_rec.operator_code IS NULL) THEN
1109 x_ecv_rec.operator_code := l_ecv_rec.operator_code;
1110 END IF;
1111
1112 IF (x_ecv_rec.crit_cat_value2 IS NULL) THEN
1113 x_ecv_rec.crit_cat_value2 := l_ecv_rec.crit_cat_value2;
1114 END IF;
1115
1116 IF (x_ecv_rec.crit_cat_value1 IS NULL) THEN
1117 x_ecv_rec.crit_cat_value1 := l_ecv_rec.crit_cat_value1;
1118 END IF;
1119
1120 IF (x_ecv_rec.adjustment_factor IS NULL) THEN
1121 x_ecv_rec.adjustment_factor := l_ecv_rec.adjustment_factor;
1122 END IF;
1123
1124 IF (x_ecv_rec.created_by IS NULL) THEN
1125 x_ecv_rec.created_by := l_ecv_rec.created_by;
1126 END IF;
1127
1128 IF (x_ecv_rec.creation_date IS NULL) THEN
1129 x_ecv_rec.creation_date := l_ecv_rec.creation_date;
1130 END IF;
1131
1132 IF (x_ecv_rec.attribute_category IS NULL) THEN
1133 x_ecv_rec.attribute_category := l_ecv_rec.attribute_category;
1134 END IF;
1135
1136 IF (x_ecv_rec.attribute1 IS NULL) THEN
1137 x_ecv_rec.attribute1 := l_ecv_rec.attribute1;
1138 END IF;
1139
1140 IF (x_ecv_rec.attribute2 IS NULL) THEN
1141 x_ecv_rec.attribute2 := l_ecv_rec.attribute2;
1142 END IF;
1143
1144 IF (x_ecv_rec.attribute3 IS NULL) THEN
1145 x_ecv_rec.attribute3 := l_ecv_rec.attribute3;
1146 END IF;
1147
1148 IF (x_ecv_rec.attribute4 IS NULL) THEN
1149 x_ecv_rec.attribute4 := l_ecv_rec.attribute4;
1150 END IF;
1151
1152 IF (x_ecv_rec.attribute5 IS NULL) THEN
1153 x_ecv_rec.attribute5 := l_ecv_rec.attribute5;
1154 END IF;
1155
1156 IF (x_ecv_rec.attribute6 IS NULL) THEN
1157 x_ecv_rec.attribute6 := l_ecv_rec.attribute6;
1158 END IF;
1159
1160 IF (x_ecv_rec.attribute7 IS NULL) THEN
1161 x_ecv_rec.attribute7 := l_ecv_rec.attribute7;
1162 END IF;
1163
1164 IF (x_ecv_rec.attribute8 IS NULL) THEN
1165 x_ecv_rec.attribute8 := l_ecv_rec.attribute8;
1166 END IF;
1167
1168 IF (x_ecv_rec.attribute9 IS NULL) THEN
1169 x_ecv_rec.attribute9 := l_ecv_rec.attribute9;
1170 END IF;
1171
1172 IF (x_ecv_rec.attribute10 IS NULL) THEN
1173 x_ecv_rec.attribute10 := l_ecv_rec.attribute10;
1174 END IF;
1175
1176 IF (x_ecv_rec.attribute11 IS NULL) THEN
1177 x_ecv_rec.attribute11 := l_ecv_rec.attribute11;
1178 END IF;
1179
1180 IF (x_ecv_rec.attribute12 IS NULL) THEN
1181 x_ecv_rec.attribute12 := l_ecv_rec.attribute12;
1182 END IF;
1183
1184 IF (x_ecv_rec.attribute13 IS NULL) THEN
1185 x_ecv_rec.attribute13 := l_ecv_rec.attribute13;
1186 END IF;
1187
1188 IF (x_ecv_rec.attribute14 IS NULL) THEN
1189 x_ecv_rec.attribute14 := l_ecv_rec.attribute14;
1190 END IF;
1191
1192 IF (x_ecv_rec.attribute15 IS NULL) THEN
1193 x_ecv_rec.attribute15 := l_ecv_rec.attribute15;
1194 END IF;
1195 RETURN(l_return_status);
1196 END populate_new_record;
1197
1198 FUNCTION set_attributes(p_ecv_rec IN okl_ecv_rec
1199 ,x_ecv_rec OUT NOCOPY okl_ecv_rec) RETURN varchar2 IS
1200 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1201
1202 BEGIN
1203 x_ecv_rec := p_ecv_rec;
1204
1205 -- x_ecv_rec.OBJECT_VERSION_NUMBER := NVL(x_ecv_rec.OBJECT_VERSION_NUMBER,0)+1;
1206
1207 RETURN(l_return_status);
1208 END set_attributes;
1209
1210 BEGIN
1211 l_return_status := okl_api.start_activity(l_api_name
1212 ,g_pkg_name
1213 ,p_init_msg_list
1214 ,l_api_version
1215 ,p_api_version
1216 ,'_PVT'
1217 ,x_return_status);
1218
1219 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1220 RAISE okl_api.g_exception_unexpected_error;
1221 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1222 RAISE okl_api.g_exception_error;
1223 END IF;
1224
1225 --Setting Item Attributes
1226
1227 l_return_status := set_attributes(p_ecv_rec, l_ecv_rec);
1228
1229 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1230 RAISE okl_api.g_exception_unexpected_error;
1231 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1232 RAISE okl_api.g_exception_error;
1233 END IF;
1234
1235 --populate new record
1236
1237 l_return_status := populate_new_record(l_ecv_rec, l_def_ecv_rec);
1238
1239 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1240 RAISE okl_api.g_exception_unexpected_error;
1241 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1242 RAISE okl_api.g_exception_error;
1243 END IF;
1244
1245 --null out g_miss_values
1246
1247 l_def_ecv_rec := null_out_defaults(l_def_ecv_rec);
1248
1249 --fill who columns
1250 --
1251
1252 l_def_ecv_rec := fill_who_columns(l_def_ecv_rec);
1253
1254 --
1255 --validate attributes
1256
1257 l_return_status := validate_attributes(l_def_ecv_rec);
1258
1259 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1260 RAISE okl_api.g_exception_unexpected_error;
1261 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1262 RAISE okl_api.g_exception_error;
1263 END IF;
1264
1265 --
1266 --validate record
1267 --
1268
1269 l_return_status := validate_record(l_def_ecv_rec);
1270
1271 --
1272
1273 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1274 RAISE okl_api.g_exception_unexpected_error;
1275 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1276 RAISE okl_api.g_exception_error;
1277 END IF;
1278
1279 --lock the row
1280
1281 lock_row(p_init_msg_list => okl_api.g_false
1282 ,x_return_status => l_return_status
1283 ,x_msg_count => x_msg_count
1284 ,x_msg_data => x_msg_data
1285 ,p_ecv_rec => l_def_ecv_rec);
1286
1287 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1288 RAISE okl_api.g_exception_unexpected_error;
1289 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1290 RAISE okl_api.g_exception_error;
1291 END IF;
1292
1293 --
1294 --update the record
1295
1296 UPDATE okl_fe_criterion_values
1297 SET criterion_value_id = l_def_ecv_rec.criterion_value_id
1298 ,object_version_number = l_def_ecv_rec.object_version_number + 1
1299 ,criteria_id = l_def_ecv_rec.criteria_id
1300 ,operator_code = l_def_ecv_rec.operator_code
1301 ,crit_cat_value2 = l_def_ecv_rec.crit_cat_value2
1302 ,crit_cat_value1 = l_def_ecv_rec.crit_cat_value1
1303 ,adjustment_factor = l_def_ecv_rec.adjustment_factor
1304 ,created_by = l_def_ecv_rec.created_by
1305 ,creation_date = l_def_ecv_rec.creation_date
1306 ,last_updated_by = l_def_ecv_rec.last_updated_by
1307 ,last_update_date = l_def_ecv_rec.last_update_date
1308 ,last_update_login = l_def_ecv_rec.last_update_login
1309 ,attribute_category = l_def_ecv_rec.attribute_category
1310 ,attribute1 = l_def_ecv_rec.attribute1
1311 ,attribute2 = l_def_ecv_rec.attribute2
1312 ,attribute3 = l_def_ecv_rec.attribute3
1313 ,attribute4 = l_def_ecv_rec.attribute4
1314 ,attribute5 = l_def_ecv_rec.attribute5
1315 ,attribute6 = l_def_ecv_rec.attribute6
1316 ,attribute7 = l_def_ecv_rec.attribute7
1317 ,attribute8 = l_def_ecv_rec.attribute8
1318 ,attribute9 = l_def_ecv_rec.attribute9
1319 ,attribute10 = l_def_ecv_rec.attribute10
1320 ,attribute11 = l_def_ecv_rec.attribute11
1321 ,attribute12 = l_def_ecv_rec.attribute12
1322 ,attribute13 = l_def_ecv_rec.attribute13
1323 ,attribute14 = l_def_ecv_rec.attribute14
1324 ,attribute15 = l_def_ecv_rec.attribute15
1325 WHERE criterion_value_id = l_def_ecv_rec.criterion_value_id;
1326
1327 --Set OUT Values
1328
1329 x_ecv_rec := l_def_ecv_rec;
1330 x_return_status := l_return_status;
1331 okl_api.end_activity(x_msg_count, x_msg_data);
1332 EXCEPTION
1333 WHEN g_exception_halt_validation THEN
1334
1335 -- No action necessary. Validation can continue to next attribute/column
1336
1337 NULL;
1338 WHEN okl_api.g_exception_error THEN
1339 x_return_status := okl_api.handle_exceptions(l_api_name
1340 ,g_pkg_name
1341 ,'OKL_API.G_RET_STS_ERROR'
1342 ,x_msg_count
1343 ,x_msg_data
1344 ,'_PVT');
1345 WHEN okl_api.g_exception_unexpected_error THEN
1346 x_return_status := okl_api.handle_exceptions(l_api_name
1347 ,g_pkg_name
1348 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1349 ,x_msg_count
1350 ,x_msg_data
1351 ,'_PVT');
1352 WHEN OTHERS THEN
1353 x_return_status := okl_api.handle_exceptions(l_api_name
1354 ,g_pkg_name
1355 ,'OTHERS'
1356 ,x_msg_count
1357 ,x_msg_data
1358 ,'_PVT');
1359 END update_row;
1360
1361 --------------------------------------------------------------------------------
1362 -- Procedure update_row_tbl
1363 --------------------------------------------------------------------------------
1364
1365 PROCEDURE update_row(p_api_version IN number
1366 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1367 ,x_return_status OUT NOCOPY varchar2
1368 ,x_msg_count OUT NOCOPY number
1369 ,x_msg_data OUT NOCOPY varchar2
1370 ,p_ecv_tbl IN okl_ecv_tbl
1371 ,x_ecv_tbl OUT NOCOPY okl_ecv_tbl) IS
1372 l_api_version CONSTANT number := 1;
1373 l_api_name CONSTANT varchar2(30) := 'update_row_tbl';
1374 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1375 i number := 0;
1376 l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
1377
1378 BEGIN
1379 okl_api.init_msg_list(p_init_msg_list);
1380
1381 -- Make sure PL/SQL table has records in it before passing
1382
1383 IF (p_ecv_tbl.COUNT > 0) THEN
1384 i := p_ecv_tbl.FIRST;
1385
1386 LOOP
1387 update_row(p_api_version => p_api_version
1388 ,p_init_msg_list => okl_api.g_false
1389 ,x_return_status => x_return_status
1390 ,x_msg_count => x_msg_count
1391 ,x_msg_data => x_msg_data
1392 ,p_ecv_rec => p_ecv_tbl(i)
1393 ,x_ecv_rec => x_ecv_tbl(i));
1394 IF x_return_status <> okl_api.g_ret_sts_success THEN
1395 IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1396 l_overall_status := x_return_status;
1397 END IF;
1398 END IF;
1399 EXIT WHEN(i = p_ecv_tbl.LAST);
1400 i := p_ecv_tbl.next(i);
1401 END LOOP;
1402 x_return_status := l_overall_status;
1403 END IF;
1404
1405 EXCEPTION
1406 WHEN g_exception_halt_validation THEN
1407
1408 -- No action necessary. Validation can continue to next attribute/column
1409
1410 NULL;
1411 WHEN okl_api.g_exception_error THEN
1412 x_return_status := okl_api.handle_exceptions(l_api_name
1413 ,g_pkg_name
1414 ,'OKL_API.G_RET_STS_ERROR'
1415 ,x_msg_count
1416 ,x_msg_data
1417 ,'_PVT');
1418 WHEN okl_api.g_exception_unexpected_error THEN
1419 x_return_status := okl_api.handle_exceptions(l_api_name
1420 ,g_pkg_name
1421 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1422 ,x_msg_count
1423 ,x_msg_data
1424 ,'_PVT');
1425 WHEN OTHERS THEN
1426 x_return_status := okl_api.handle_exceptions(l_api_name
1427 ,g_pkg_name
1428 ,'OTHERS'
1429 ,x_msg_count
1430 ,x_msg_data
1431 ,'_PVT');
1432 END update_row;
1433
1434 --------------------------------------------------------------------------------
1435 -- Procedure delete_row
1436 --------------------------------------------------------------------------------
1437
1438 PROCEDURE delete_row(p_api_version IN number
1439 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1440 ,x_return_status OUT NOCOPY varchar2
1441 ,x_msg_count OUT NOCOPY number
1442 ,x_msg_data OUT NOCOPY varchar2
1443 ,p_ecv_rec IN okl_ecv_rec) IS
1444 l_api_version CONSTANT number := 1;
1445 l_api_name CONSTANT varchar2(30) := 'delete_row';
1446 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1447 l_ecv_rec okl_ecv_rec := p_ecv_rec;
1448 l_row_notfound boolean := true;
1449
1450 BEGIN
1451 l_return_status := okl_api.start_activity(l_api_name
1452 ,g_pkg_name
1453 ,p_init_msg_list
1454 ,l_api_version
1455 ,p_api_version
1456 ,'_PVT'
1457 ,x_return_status);
1458
1459 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1460 RAISE okl_api.g_exception_unexpected_error;
1461 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1462 RAISE okl_api.g_exception_error;
1463 END IF;
1464
1465 DELETE FROM okl_fe_criterion_values
1466 WHERE criterion_value_id = l_ecv_rec.criterion_value_id;
1467 x_return_status := l_return_status;
1468 okl_api.end_activity(x_msg_count, x_msg_data);
1469 EXCEPTION
1470 WHEN g_exception_halt_validation THEN
1471
1472 -- No action necessary. Validation can continue to next attribute/column
1473
1474 NULL;
1475 WHEN okl_api.g_exception_error THEN
1476 x_return_status := okl_api.handle_exceptions(l_api_name
1477 ,g_pkg_name
1478 ,'OKL_API.G_RET_STS_ERROR'
1479 ,x_msg_count
1480 ,x_msg_data
1481 ,'_PVT');
1482 WHEN okl_api.g_exception_unexpected_error THEN
1483 x_return_status := okl_api.handle_exceptions(l_api_name
1484 ,g_pkg_name
1485 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1486 ,x_msg_count
1487 ,x_msg_data
1488 ,'_PVT');
1489 WHEN OTHERS THEN
1490 x_return_status := okl_api.handle_exceptions(l_api_name
1491 ,g_pkg_name
1492 ,'OTHERS'
1493 ,x_msg_count
1494 ,x_msg_data
1495 ,'_PVT');
1496 END delete_row;
1497
1498 --------------------------------------------------------------------------------
1499 -- Procedure delete_row_tbl
1500 --------------------------------------------------------------------------------
1501
1502 PROCEDURE delete_row(p_api_version IN number
1503 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1504 ,x_return_status OUT NOCOPY varchar2
1505 ,x_msg_count OUT NOCOPY number
1506 ,x_msg_data OUT NOCOPY varchar2
1507 ,p_ecv_tbl IN okl_ecv_tbl) IS
1508 l_api_version CONSTANT number := 1;
1509 l_api_name CONSTANT varchar2(30) := 'v_delete_row';
1510 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1511 i number := 0;
1512 l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
1513
1514 BEGIN
1515 okl_api.init_msg_list(p_init_msg_list);
1516
1517 -- Make sure PL/SQL table has records in it before passing
1518
1519 IF (p_ecv_tbl.COUNT > 0) THEN
1520 i := p_ecv_tbl.FIRST;
1521
1522 LOOP
1523 delete_row(p_api_version => p_api_version
1524 ,p_init_msg_list => okl_api.g_false
1525 ,x_return_status => x_return_status
1526 ,x_msg_count => x_msg_count
1527 ,x_msg_data => x_msg_data
1528 ,p_ecv_rec => p_ecv_tbl(i));
1529 IF x_return_status <> okl_api.g_ret_sts_success THEN
1530 IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1531 l_overall_status := x_return_status;
1532 END IF;
1533 END IF;
1534 EXIT WHEN(i = p_ecv_tbl.LAST);
1535 i := p_ecv_tbl.next(i);
1536 END LOOP;
1537 x_return_status := l_overall_status;
1538 END IF;
1539
1540 EXCEPTION
1541 WHEN g_exception_halt_validation THEN
1542
1543 -- No action necessary. Validation can continue to next attribute/column
1544
1545 NULL;
1546 WHEN okl_api.g_exception_error THEN
1547 x_return_status := okl_api.handle_exceptions(l_api_name
1548 ,g_pkg_name
1549 ,'OKL_API.G_RET_STS_ERROR'
1550 ,x_msg_count
1551 ,x_msg_data
1552 ,'_PVT');
1553 WHEN okl_api.g_exception_unexpected_error THEN
1554 x_return_status := okl_api.handle_exceptions(l_api_name
1555 ,g_pkg_name
1556 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1557 ,x_msg_count
1558 ,x_msg_data
1559 ,'_PVT');
1560 WHEN OTHERS THEN
1561 x_return_status := okl_api.handle_exceptions(l_api_name
1562 ,g_pkg_name
1563 ,'OTHERS'
1564 ,x_msg_count
1565 ,x_msg_data
1566 ,'_PVT');
1567 END delete_row;
1568
1569 END okl_ecv_pvt;