[Home] [Help]
PACKAGE BODY: APPS.OKL_ECO_PVT
Source
1 PACKAGE BODY okl_eco_pvt AS
2 /* $Header: OKLSECOB.pls 120.3 2006/12/07 08:58:16 ssdeshpa 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_CRIT_CAT_OBJECTS --
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_eco_rec IN okl_eco_rec) IS
43 e_resource_busy EXCEPTION;
44
45 PRAGMA exception_init(e_resource_busy, - 00054);
46
47 CURSOR lock_csr(p_eco_rec IN okl_eco_rec) IS
48 SELECT object_version_number
49 FROM okl_fe_crit_cat_objects
50 WHERE object_class_id = p_eco_rec.object_class_id
51 AND object_version_number = p_eco_rec.object_version_number
52 FOR UPDATE OF object_version_number NOWAIT;
53
54 CURSOR lchk_csr(p_eco_rec IN okl_eco_rec) IS
55 SELECT object_version_number
56 FROM okl_fe_crit_cat_objects
57 WHERE object_class_id = p_eco_rec.object_class_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_crit_cat_objects.object_version_number%TYPE;
62 lc_object_version_number okl_fe_crit_cat_objects.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_eco_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_eco_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_eco_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_eco_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_CRIT_CAT_OBJECTS --
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_eco_tbl IN okl_eco_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_eco_tbl.COUNT > 0) THEN
174 i := p_eco_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_eco_rec => p_eco_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_eco_tbl.LAST);
195 i := p_eco_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_object_class_id
240 ---------------------------------------------------
241
242 FUNCTION validate_object_class_id(p_object_class_id IN number) RETURN varchar2 IS
243 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_object_class_id';
244
245 BEGIN
246
247 -- object_class_id is required
248
249 IF (p_object_class_id IS NULL) OR (p_object_class_id = okl_api.g_miss_num) THEN
250 okl_api.set_message(p_app_name => g_app_name
251 ,p_msg_name => g_required_value
252 ,p_token1 => g_col_name_token
253 ,p_token1_value => 'object_class_id');
254 RAISE okl_api.g_exception_error;
255 END IF;
256 RETURN g_ret_sts_success;
257 EXCEPTION
258 WHEN okl_api.g_exception_error THEN
259 RETURN g_ret_sts_error;
260 WHEN okl_api.g_exception_unexpected_error THEN
261 RETURN g_ret_sts_unexp_error;
262 WHEN OTHERS THEN
263 okl_api.set_message(p_app_name => g_app_name
264 ,p_msg_name => g_db_error
265 ,p_token1 => g_prog_name_token
266 ,p_token1_value => l_api_name
267 ,p_token2 => 'SQLCODE'
268 ,p_token2_value => sqlcode
269 ,p_token3 => 'SQLERRM'
270 ,p_token3_value => sqlerrm);
271 RETURN g_ret_sts_unexp_error;
272 END validate_object_class_id;
273
274 ---------------------------------------------------
275 -- Function Name : validate_object_version_number
276 ---------------------------------------------------
277
278 FUNCTION validate_object_version_number(p_object_version_number IN number) RETURN varchar2 IS
279 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_object_version_number';
280
281 BEGIN
282
283 -- object_version_numbe is required
284
285 IF (p_object_version_number IS NULL) OR (p_object_version_number = okl_api.g_miss_num) THEN
286 okl_api.set_message(p_app_name => g_app_name
287 ,p_msg_name => g_required_value
288 ,p_token1 => g_col_name_token
289 ,p_token1_value => 'object_version_number');
290 RAISE okl_api.g_exception_error;
291 END IF;
292 RETURN g_ret_sts_success;
293 EXCEPTION
294 WHEN okl_api.g_exception_error THEN
295 RETURN g_ret_sts_error;
296 WHEN okl_api.g_exception_unexpected_error THEN
297 RETURN g_ret_sts_unexp_error;
298 WHEN OTHERS THEN
299 okl_api.set_message(p_app_name => g_app_name
300 ,p_msg_name => g_db_error
301 ,p_token1 => g_prog_name_token
302 ,p_token1_value => l_api_name
303 ,p_token2 => 'SQLCODE'
304 ,p_token2_value => sqlcode
305 ,p_token3 => 'SQLERRM'
306 ,p_token3_value => sqlerrm);
307 RETURN g_ret_sts_unexp_error;
308 END validate_object_version_number;
309
310 ----------------------------------------------
311 -- Function Name : validate_object_class_code
312 ----------------------------------------------
313
314 FUNCTION validate_object_class_code(p_object_class_code IN varchar2) RETURN varchar2 IS
315 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_object_class_code';
316 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
317
318 BEGIN
319
320 -- object_class_code is required
321
322 IF (p_object_class_code IS NULL) OR (p_object_class_code = okl_api.g_miss_char) THEN
323 okl_api.set_message(p_app_name => g_app_name
324 ,p_msg_name => g_required_value
325 ,p_token1 => g_col_name_token
326 ,p_token1_value => 'object_class_code');
327 RAISE okl_api.g_exception_error;
328 END IF;
329
330 --object_class_code should belong to llokup type OKL_ECC_OBJECT_CLASSES
331
332 l_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_ECC_OBJECT_CLASSES'
333 ,p_lookup_code => p_object_class_code);
334
335 IF (l_return_status = okl_api.g_ret_sts_error) THEN
336 okl_api.set_message(p_app_name => g_app_name
337 ,p_msg_name => g_invalid_value
338 ,p_token1 => g_col_name_token
339 ,p_token1_value => 'object_class_code');
340 RAISE okl_api.g_exception_error;
341 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
342 RAISE okl_api.g_exception_unexpected_error;
343 END IF;
344 RETURN g_ret_sts_success;
345 EXCEPTION
346 WHEN okl_api.g_exception_error THEN
347 RETURN g_ret_sts_error;
348 WHEN okl_api.g_exception_unexpected_error THEN
349 RETURN g_ret_sts_unexp_error;
350 WHEN OTHERS THEN
351 okl_api.set_message(p_app_name => g_app_name
352 ,p_msg_name => g_db_error
353 ,p_token1 => g_prog_name_token
354 ,p_token1_value => l_api_name
355 ,p_token2 => 'SQLCODE'
356 ,p_token2_value => sqlcode
357 ,p_token3 => 'SQLERRM'
358 ,p_token3_value => sqlerrm);
359 RETURN g_ret_sts_unexp_error;
360 END validate_object_class_code;
361
362 -------------------------------------------------
363 -- Function Name : validate_crit_cat_def_id
364 -------------------------------------------------
365
366 FUNCTION validate_crit_cat_def_id(p_crit_cat_def_id IN number) RETURN varchar2 IS
367 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_crit_cat_def_id';
368 l_dummy_var varchar2(1) := '?';
369
370 -- select the ID of the parent record from the parent table
371
372 CURSOR l_ecc_csr IS
373 SELECT 'x'
374 FROM okl_fe_crit_cat_def_v
375 WHERE crit_cat_def_id = p_crit_cat_def_id;
376
377 BEGIN
378
379 -- crit_cat_def_id is required
380
381 IF (p_crit_cat_def_id IS NULL) OR (p_crit_cat_def_id = okl_api.g_miss_num) THEN
382 okl_api.set_message(p_app_name => g_app_name
383 ,p_msg_name => g_required_value
384 ,p_token1 => g_col_name_token
385 ,p_token1_value => 'CRIT_CAT_DEF_IDUPD');
386
387 -- halt further validation of this column
388
389 RAISE okl_api.g_exception_error;
390 END IF;
391
392 -- enforce foreign key
393
394 OPEN l_ecc_csr;
395 FETCH l_ecc_csr INTO l_dummy_var ;
396 CLOSE l_ecc_csr;
397
398 -- if l_dummy_var is still set to default, data was not found
399
400 IF (l_dummy_var = '?') THEN
401 okl_api.set_message(p_app_name => g_app_name
402 ,p_msg_name => g_no_parent_record
403 ,p_token1 => g_col_name_token
404 ,p_token1_value => 'CRIT_CAT_DEF_ID'
405 ,p_token2 => g_child_table_token
406 ,p_token2_value => 'OKL_FE_CRIT_CAT_OBJECTS'
407 ,p_token3 => g_parent_table_token
408 ,p_token3_value => 'OKL_FE_CRIT_CAT_DEF_V');
409 RAISE okl_api.g_exception_error;
410 END IF;
411 RETURN g_ret_sts_success;
412 EXCEPTION
413 WHEN okl_api.g_exception_error THEN
414
415 -- verify that cursor was closed
416
417 IF l_ecc_csr%ISOPEN THEN
418 CLOSE l_ecc_csr;
419 END IF;
420 RETURN g_ret_sts_error;
421 WHEN okl_api.g_exception_unexpected_error THEN
422
423 -- verify that cursor was closed
424
425 IF l_ecc_csr%ISOPEN THEN
426 CLOSE l_ecc_csr;
427 END IF;
428 RETURN g_ret_sts_unexp_error;
429 WHEN OTHERS THEN
430
431 -- verify that cursor was closed
432
433 IF l_ecc_csr%ISOPEN THEN
434 CLOSE l_ecc_csr;
435 END IF;
436 okl_api.set_message(p_app_name => g_app_name
437 ,p_msg_name => g_db_error
438 ,p_token1 => g_prog_name_token
439 ,p_token1_value => l_api_name
440 ,p_token2 => 'SQLCODE'
441 ,p_token2_value => sqlcode
442 ,p_token3 => 'SQLERRM'
443 ,p_token3_value => sqlerrm);
444 RETURN g_ret_sts_unexp_error;
445 END validate_crit_cat_def_id;
446
447 ---------------------------------------------------------------------------
448 -- PROCEDURE Validate_Attributes
449 ---------------------------------------------------------------------------
450
451 FUNCTION validate_attributes(p_eco_rec IN okl_eco_rec) RETURN varchar2 IS
452 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
453 x_return_status varchar2(1) := okl_api.g_ret_sts_success;
454 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_attributes';
455
456 BEGIN
457
458 -- call each column-level validation
459
460 /*******************/
461
462 -- object_class_id
463
464 /*******************/
465
466 l_return_status := validate_object_class_id(p_eco_rec.object_class_id);
467
468 IF (l_return_status = g_ret_sts_unexp_error) THEN
469 RAISE okl_api.g_exception_unexpected_error;
470 ELSIF (l_return_status = g_ret_sts_error) THEN
471 RAISE okl_api.g_exception_error;
472 END IF;
473
474 /***********************/
475
476 -- object_version_number
477
478 /***********************/
479
480 l_return_status := validate_object_version_number(p_eco_rec.object_version_number);
481
482 IF (l_return_status = g_ret_sts_unexp_error) THEN
483 RAISE okl_api.g_exception_unexpected_error;
484 ELSIF (l_return_status = g_ret_sts_error) THEN
485 RAISE okl_api.g_exception_error;
486 END IF;
487
488 /*******************/
489
490 -- crit_cat_def_id
491
492 /*******************/
493
494 l_return_status := validate_crit_cat_def_id(p_eco_rec.crit_cat_def_id);
495
496 IF (l_return_status = g_ret_sts_unexp_error) THEN
497 RAISE okl_api.g_exception_unexpected_error;
498 ELSIF (l_return_status = g_ret_sts_error) THEN
499 RAISE okl_api.g_exception_error;
500 END IF;
501
502 /*******************/
503
504 -- object_class_code
505
506 /*******************/
507
508 l_return_status := validate_object_class_code(p_eco_rec.object_class_code);
509
510 IF (l_return_status = g_ret_sts_unexp_error) THEN
511 RAISE okl_api.g_exception_unexpected_error;
512 ELSIF (l_return_status = g_ret_sts_error) THEN
513 RAISE okl_api.g_exception_error;
514 END IF;
515 RETURN(x_return_status);
516 EXCEPTION
517 WHEN okl_api.g_exception_error THEN
518 RETURN g_ret_sts_error;
519 WHEN okl_api.g_exception_unexpected_error THEN
520 RETURN g_ret_sts_unexp_error;
521 WHEN OTHERS THEN
522 okl_api.set_message(p_app_name => g_app_name
523 ,p_msg_name => g_db_error
524 ,p_token1 => g_prog_name_token
525 ,p_token1_value => l_api_name
526 ,p_token2 => 'SQLCODE'
527 ,p_token2_value => sqlcode
528 ,p_token3 => 'SQLERRM'
529 ,p_token3_value => sqlerrm);
530 RETURN g_ret_sts_unexp_error;
531 END validate_attributes;
532
533 --------------------------------------------
534 -- Validate_Record for:OKL_FE_CRIT_CAT_OBJECTS --
535 --------------------------------------------
536
537 FUNCTION validate_record(p_eco_rec IN okl_eco_rec) RETURN varchar2 IS
538 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
539 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_record';
540
541 BEGIN
542 RETURN(l_return_status);
543 END validate_record;
544
545 --------------------------------------------------------------------------------
546 -- Procedure get_rec for OKL_FE_CRIT_CAT_OBJECTS
547 --------------------------------------------------------------------------------
548
549 FUNCTION get_rec(p_eco_rec IN okl_eco_rec
550 ,x_no_data_found OUT NOCOPY boolean) RETURN okl_eco_rec IS
551
552 CURSOR eco_pk_csr(p_id IN number) IS
553 SELECT object_class_id
554 ,object_version_number
555 ,crit_cat_def_id
556 ,object_class_code
557 ,created_by
558 ,creation_date
559 ,last_updated_by
560 ,last_update_date
561 ,last_update_login
562 FROM okl_fe_crit_cat_objects
563 WHERE okl_fe_crit_cat_objects.object_class_id = p_id;
564 l_eco_pk eco_pk_csr%ROWTYPE;
565 l_eco_rec okl_eco_rec;
566
567 BEGIN
568 x_no_data_found := true;
569
570 --Get current data base values
571
572 OPEN eco_pk_csr(p_eco_rec.object_class_id);
573 FETCH eco_pk_csr INTO l_eco_rec.object_class_id
574 ,l_eco_rec.object_version_number
575 ,l_eco_rec.crit_cat_def_id
576 ,l_eco_rec.object_class_code
577 ,l_eco_rec.created_by
578 ,l_eco_rec.creation_date
579 ,l_eco_rec.last_updated_by
580 ,l_eco_rec.last_update_date
581 ,l_eco_rec.last_update_login ;
582 x_no_data_found := eco_pk_csr%NOTFOUND;
583 CLOSE eco_pk_csr;
584 RETURN(l_eco_rec);
585 END get_rec;
586
587 FUNCTION get_rec(p_eco_rec IN okl_eco_rec) RETURN okl_eco_rec IS
588 l_row_notfound boolean := true;
589
590 BEGIN
591 RETURN(get_rec(p_eco_rec, l_row_notfound));
592 END get_rec;
593
594 FUNCTION null_out_defaults(p_eco_rec IN okl_eco_rec) RETURN okl_eco_rec IS
595 l_eco_rec okl_eco_rec := p_eco_rec;
596
597 BEGIN
598
599 IF (l_eco_rec.object_class_id = okl_api.g_miss_num) THEN
600 l_eco_rec.object_class_id := NULL;
601 END IF;
602
603 IF (l_eco_rec.object_version_number = g_miss_num) THEN
604 l_eco_rec.object_version_number := NULL;
605 END IF;
606
607 IF (l_eco_rec.crit_cat_def_id = okl_api.g_miss_num) THEN
608 l_eco_rec.crit_cat_def_id := NULL;
609 END IF;
610
611 IF (l_eco_rec.object_class_code = okl_api.g_miss_char) THEN
612 l_eco_rec.object_class_code := NULL;
613 END IF;
614
615 IF (l_eco_rec.created_by = okl_api.g_miss_num) THEN
616 l_eco_rec.created_by := NULL;
617 END IF;
618
619 IF (l_eco_rec.creation_date = okl_api.g_miss_date) THEN
620 l_eco_rec.creation_date := NULL;
621 END IF;
622
623 IF (l_eco_rec.last_updated_by = okl_api.g_miss_num) THEN
624 l_eco_rec.last_updated_by := NULL;
625 END IF;
626
627 IF (l_eco_rec.last_update_date = okl_api.g_miss_date) THEN
628 l_eco_rec.last_update_date := NULL;
629 END IF;
630
631 IF (l_eco_rec.last_update_login = okl_api.g_miss_num) THEN
632 l_eco_rec.last_update_login := NULL;
633 END IF;
634 RETURN(l_eco_rec);
635 END null_out_defaults;
636
637 FUNCTION get_seq_id RETURN number IS
638
639 BEGIN
640 RETURN(okc_p_util.raw_to_number(sys_guid()));
641 END get_seq_id;
642
643 --------------------------------------------------------------------------------
644 -- Procedure insert_row
645 --------------------------------------------------------------------------------
646
647 PROCEDURE insert_row(p_api_version IN number
648 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
649 ,x_return_status OUT NOCOPY varchar2
650 ,x_msg_count OUT NOCOPY number
651 ,x_msg_data OUT NOCOPY varchar2
652 ,p_eco_rec IN okl_eco_rec
653 ,x_eco_rec OUT NOCOPY okl_eco_rec) IS
654 l_api_version CONSTANT number := 1;
655 l_api_name CONSTANT varchar2(30) := 'insert_row';
656 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
657 l_eco_rec okl_eco_rec;
658 l_def_eco_rec okl_eco_rec;
659
660 FUNCTION fill_who_columns(p_eco_rec IN okl_eco_rec) RETURN okl_eco_rec IS
661 l_eco_rec okl_eco_rec := p_eco_rec;
662
663 BEGIN
664 l_eco_rec.creation_date := sysdate;
665 l_eco_rec.created_by := fnd_global.user_id;
666 l_eco_rec.last_update_date := sysdate;
667 l_eco_rec.last_updated_by := fnd_global.user_id;
668 l_eco_rec.last_update_login := fnd_global.login_id;
669 RETURN(l_eco_rec);
670 END fill_who_columns;
671
672 FUNCTION set_attributes(p_eco_rec IN okl_eco_rec
673 ,x_eco_rec OUT NOCOPY okl_eco_rec) RETURN varchar2 IS
674 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
675
676 BEGIN
677 x_eco_rec := p_eco_rec;
681
678 x_eco_rec.object_version_number := 1;
679
680 -- Set Primary key value
682 x_eco_rec.object_class_id := get_seq_id;
683 RETURN(l_return_status);
684 END set_attributes;
685
686 BEGIN
687 l_return_status := okl_api.start_activity(l_api_name
688 ,g_pkg_name
689 ,p_init_msg_list
690 ,l_api_version
691 ,p_api_version
692 ,'_PVT'
693 ,x_return_status);
694
695 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
696 RAISE okl_api.g_exception_unexpected_error;
697 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
698 RAISE okl_api.g_exception_error;
699 END IF;
700
701 --null out defaults
702
703 l_eco_rec := null_out_defaults(p_eco_rec);
704
705 --Setting Item Attributes
706
707 l_return_status := set_attributes(l_eco_rec, l_def_eco_rec);
708
709 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
710 RAISE okl_api.g_exception_unexpected_error;
711 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
712 RAISE okl_api.g_exception_error;
713 END IF;
714
715 --fill who columns
716
717 l_def_eco_rec := fill_who_columns(l_def_eco_rec);
718
719 --validate attributes
720
721 l_return_status := validate_attributes(l_def_eco_rec);
722
723 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
724 RAISE okl_api.g_exception_unexpected_error;
725 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
726 RAISE okl_api.g_exception_error;
727 END IF;
728
729 --validate record
730
731 l_return_status := validate_record(l_def_eco_rec);
732
733 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
734 RAISE okl_api.g_exception_unexpected_error;
735 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
736 RAISE okl_api.g_exception_error;
737 END IF;
738
739 --insert into table
740
741 INSERT INTO okl_fe_crit_cat_objects
742 (object_class_id
743 ,object_version_number
744 ,crit_cat_def_id
745 ,object_class_code
746 ,created_by
747 ,creation_date
748 ,last_updated_by
749 ,last_update_date
750 ,last_update_login)
751 VALUES (l_def_eco_rec.object_class_id
752 ,l_def_eco_rec.object_version_number
753 ,l_def_eco_rec.crit_cat_def_id
754 ,l_def_eco_rec.object_class_code
755 ,l_def_eco_rec.created_by
756 ,l_def_eco_rec.creation_date
757 ,l_def_eco_rec.last_updated_by
758 ,l_def_eco_rec.last_update_date
759 ,l_def_eco_rec.last_update_login);
760
761 --Set OUT Values
762
763 x_eco_rec := l_def_eco_rec;
764 x_return_status := l_return_status;
765 okl_api.end_activity(x_msg_count, x_msg_data);
766 EXCEPTION
767 WHEN g_exception_halt_validation THEN
768
769 -- No action necessary. Validation can continue to next attribute/column
770
771 NULL;
772 WHEN okl_api.g_exception_error THEN
773 x_return_status := okl_api.handle_exceptions(l_api_name
774 ,g_pkg_name
775 ,'OKL_API.G_RET_STS_ERROR'
776 ,x_msg_count
777 ,x_msg_data
778 ,'_PVT');
779 WHEN okl_api.g_exception_unexpected_error THEN
780 x_return_status := okl_api.handle_exceptions(l_api_name
781 ,g_pkg_name
782 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
783 ,x_msg_count
784 ,x_msg_data
785 ,'_PVT');
786 WHEN OTHERS THEN
787 x_return_status := okl_api.handle_exceptions(l_api_name
788 ,g_pkg_name
789 ,'OTHERS'
790 ,x_msg_count
791 ,x_msg_data
792 ,'_PVT');
793 END insert_row;
794
795 --------------------------------------------------------------------------------
796 -- Procedure insert_row_tbl
797 --------------------------------------------------------------------------------
798
799 PROCEDURE insert_row(p_api_version IN number
800 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
801 ,x_return_status OUT NOCOPY varchar2
802 ,x_msg_count OUT NOCOPY number
803 ,x_msg_data OUT NOCOPY varchar2
804 ,p_eco_tbl IN okl_eco_tbl
805 ,x_eco_tbl OUT NOCOPY okl_eco_tbl) IS
806 l_api_version CONSTANT number := 1;
807 l_api_name CONSTANT varchar2(30) := 'insert_row_tbl';
811
808 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
809 i number := 0;
810 l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
812 BEGIN
813 okl_api.init_msg_list(p_init_msg_list);
814
815 -- Make sure PL/SQL table has records in it before passing
816
817 IF (p_eco_tbl.COUNT > 0) THEN
818 i := p_eco_tbl.FIRST;
819
820 LOOP
821 insert_row(p_api_version => p_api_version
822 ,p_init_msg_list => okl_api.g_false
823 ,x_return_status => x_return_status
824 ,x_msg_count => x_msg_count
825 ,x_msg_data => x_msg_data
826 ,p_eco_rec => p_eco_tbl(i)
827 ,x_eco_rec => x_eco_tbl(i));
828 IF x_return_status <> okl_api.g_ret_sts_success THEN
829 IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
830 l_overall_status := x_return_status;
831 END IF;
832 END IF;
833 EXIT WHEN(i = p_eco_tbl.LAST);
834 i := p_eco_tbl.next(i);
835 END LOOP;
836 x_return_status := l_overall_status;
837 END IF;
838
839 EXCEPTION
840 WHEN g_exception_halt_validation THEN
841
842 -- No action necessary. Validation can continue to next attribute/column
843
844 NULL;
845 WHEN okl_api.g_exception_error THEN
846 x_return_status := okl_api.handle_exceptions(l_api_name
847 ,g_pkg_name
848 ,'OKL_API.G_RET_STS_ERROR'
849 ,x_msg_count
850 ,x_msg_data
851 ,'_PVT');
852 WHEN okl_api.g_exception_unexpected_error THEN
853 x_return_status := okl_api.handle_exceptions(l_api_name
854 ,g_pkg_name
855 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
856 ,x_msg_count
857 ,x_msg_data
858 ,'_PVT');
859 WHEN OTHERS THEN
860 x_return_status := okl_api.handle_exceptions(l_api_name
861 ,g_pkg_name
862 ,'OTHERS'
863 ,x_msg_count
864 ,x_msg_data
865 ,'_PVT');
866 END insert_row;
867
868 --------------------------------------------------------------------------------
869 -- Procedure update_row
870 --------------------------------------------------------------------------------
871
872 PROCEDURE update_row(p_api_version IN number
873 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
874 ,x_return_status OUT NOCOPY varchar2
875 ,x_msg_count OUT NOCOPY number
876 ,x_msg_data OUT NOCOPY varchar2
877 ,p_eco_rec IN okl_eco_rec
878 ,x_eco_rec OUT NOCOPY okl_eco_rec) IS
879 l_api_version CONSTANT number := 1;
880 l_api_name CONSTANT varchar2(30) := 'update_row';
881 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
882 l_eco_rec okl_eco_rec := p_eco_rec;
883 l_def_eco_rec okl_eco_rec;
884 l_row_notfound boolean := true;
885
886 FUNCTION fill_who_columns(p_eco_rec IN okl_eco_rec) RETURN okl_eco_rec IS
887 l_eco_rec okl_eco_rec := p_eco_rec;
888
889 BEGIN
890 l_eco_rec.last_update_date := sysdate;
891 l_eco_rec.last_updated_by := fnd_global.user_id;
892 l_eco_rec.last_update_login := fnd_global.login_id;
893 RETURN(l_eco_rec);
894 END fill_who_columns;
895
896 FUNCTION populate_new_record(p_eco_rec IN okl_eco_rec
897 ,x_eco_rec OUT NOCOPY okl_eco_rec) RETURN varchar2 IS
898 l_eco_rec okl_eco_rec;
899 l_row_notfound boolean := true;
900 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
901
902 BEGIN
903 x_eco_rec := p_eco_rec;
904
905 --Get current database values
906
907 l_eco_rec := get_rec(p_eco_rec, l_row_notfound);
908
909 IF (l_row_notfound) THEN
910 l_return_status := okl_api.g_ret_sts_unexp_error;
911 END IF;
912
913 --dont default object_version_number, last_updated_by, last_updat_date and last_update_login
914
915 IF (x_eco_rec.object_class_id IS NULL) THEN
916 x_eco_rec.object_class_id := l_eco_rec.object_class_id;
917 END IF;
918
919 IF (x_eco_rec.crit_cat_def_id IS NULL) THEN
920 x_eco_rec.crit_cat_def_id := l_eco_rec.crit_cat_def_id;
921 END IF;
922
923 IF (x_eco_rec.object_class_code IS NULL) THEN
924 x_eco_rec.object_class_code := l_eco_rec.object_class_code;
925 END IF;
926
927 IF (x_eco_rec.created_by IS NULL) THEN
928 x_eco_rec.created_by := l_eco_rec.created_by;
929 END IF;
930
931 IF (x_eco_rec.creation_date IS NULL) THEN
932 x_eco_rec.creation_date := l_eco_rec.creation_date;
933 END IF;
934 RETURN(l_return_status);
935 END populate_new_record;
936
940
937 FUNCTION set_attributes(p_eco_rec IN okl_eco_rec
938 ,x_eco_rec OUT NOCOPY okl_eco_rec) RETURN varchar2 IS
939 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
941 BEGIN
942 x_eco_rec := p_eco_rec;
943 RETURN(l_return_status);
944 END set_attributes;
945
946 BEGIN
947
948 --start activity
949
950 l_return_status := okl_api.start_activity(l_api_name
951 ,g_pkg_name
952 ,p_init_msg_list
953 ,l_api_version
954 ,p_api_version
955 ,'_PVT'
956 ,x_return_status);
957
958 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
959 RAISE okl_api.g_exception_unexpected_error;
960 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
961 RAISE okl_api.g_exception_error;
962 END IF;
963
964 --Setting Item Attributes
965
966 l_return_status := set_attributes(p_eco_rec, l_eco_rec);
967
968 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
969 RAISE okl_api.g_exception_unexpected_error;
970 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
971 RAISE okl_api.g_exception_error;
972 END IF;
973
974 --populate new record
975
976 l_return_status := populate_new_record(l_eco_rec, l_def_eco_rec);
977
978 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
979 RAISE okl_api.g_exception_unexpected_error;
980 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
981 RAISE okl_api.g_exception_error;
982 END IF;
983
984 --null out G Miss values
985
986 l_def_eco_rec := null_out_defaults(l_def_eco_rec);
987
988 --fill who columns
989
990 l_def_eco_rec := fill_who_columns(l_def_eco_rec);
991
992 --validate attributes
993
994 l_return_status := validate_attributes(l_def_eco_rec);
995
996 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
997 RAISE okl_api.g_exception_unexpected_error;
998 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
999 RAISE okl_api.g_exception_error;
1000 END IF;
1001
1002 --validate record
1003
1004 l_return_status := validate_record(l_def_eco_rec);
1005
1006 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1007 RAISE okl_api.g_exception_unexpected_error;
1008 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1009 RAISE okl_api.g_exception_error;
1010 END IF;
1011
1012 --lock the row
1013
1014 lock_row(p_init_msg_list => okl_api.g_false
1015 ,x_return_status => l_return_status
1016 ,x_msg_count => x_msg_count
1017 ,x_msg_data => x_msg_data
1018 ,p_eco_rec => l_def_eco_rec);
1019
1020 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1021 RAISE okl_api.g_exception_unexpected_error;
1022 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1023 RAISE okl_api.g_exception_error;
1024 END IF;
1025
1026 --update the record
1027
1028 UPDATE okl_fe_crit_cat_objects
1029 SET object_class_id = l_def_eco_rec.object_class_id
1030 ,object_version_number = l_def_eco_rec.object_version_number + 1
1031 ,crit_cat_def_id = l_def_eco_rec.crit_cat_def_id
1032 ,object_class_code = l_def_eco_rec.object_class_code
1033 ,created_by = l_def_eco_rec.created_by
1034 ,creation_date = l_def_eco_rec.creation_date
1035 ,last_updated_by = l_def_eco_rec.last_updated_by
1036 ,last_update_date = l_def_eco_rec.last_update_date
1037 ,last_update_login = l_def_eco_rec.last_update_login
1038 WHERE object_class_id = l_def_eco_rec.object_class_id;
1039
1040 --Set OUT Values
1041
1042 x_eco_rec := l_def_eco_rec;
1043 x_return_status := l_return_status;
1044
1045 --end activity
1046
1047 okl_api.end_activity(x_msg_count, x_msg_data);
1048 EXCEPTION
1049 WHEN g_exception_halt_validation THEN
1050
1051 -- No action necessary. Validation can continue to next attribute/column
1052
1053 NULL;
1054 WHEN okl_api.g_exception_error THEN
1055 x_return_status := okl_api.handle_exceptions(l_api_name
1056 ,g_pkg_name
1057 ,'OKL_API.G_RET_STS_ERROR'
1058 ,x_msg_count
1059 ,x_msg_data
1060 ,'_PVT');
1061 WHEN okl_api.g_exception_unexpected_error THEN
1062 x_return_status := okl_api.handle_exceptions(l_api_name
1063 ,g_pkg_name
1064 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1065 ,x_msg_count
1066 ,x_msg_data
1067 ,'_PVT');
1068 WHEN OTHERS THEN
1069 x_return_status := okl_api.handle_exceptions(l_api_name
1070 ,g_pkg_name
1071 ,'OTHERS'
1075 END update_row;
1072 ,x_msg_count
1073 ,x_msg_data
1074 ,'_PVT');
1076
1077 --------------------------------------------------------------------------------
1078 -- Procedure update_row_tbl
1079 --------------------------------------------------------------------------------
1080
1081 PROCEDURE update_row(p_api_version IN number
1082 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1083 ,x_return_status OUT NOCOPY varchar2
1084 ,x_msg_count OUT NOCOPY number
1085 ,x_msg_data OUT NOCOPY varchar2
1086 ,p_eco_tbl IN okl_eco_tbl
1087 ,x_eco_tbl OUT NOCOPY okl_eco_tbl) IS
1088 l_api_version CONSTANT number := 1;
1089 l_api_name CONSTANT varchar2(30) := 'update_row_tbl';
1090 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1091 i number := 0;
1092 l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
1093
1094 BEGIN
1095 okl_api.init_msg_list(p_init_msg_list);
1096
1097 -- Make sure PL/SQL table has records in it before passing
1098
1099 IF (p_eco_tbl.COUNT > 0) THEN
1100 i := p_eco_tbl.FIRST;
1101
1102 LOOP
1103 update_row(p_api_version => p_api_version
1104 ,p_init_msg_list => okl_api.g_false
1105 ,x_return_status => x_return_status
1106 ,x_msg_count => x_msg_count
1107 ,x_msg_data => x_msg_data
1108 ,p_eco_rec => p_eco_tbl(i)
1109 ,x_eco_rec => x_eco_tbl(i));
1110 IF x_return_status <> okl_api.g_ret_sts_success THEN
1111 IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1112 l_overall_status := x_return_status;
1113 END IF;
1114 END IF;
1115 EXIT WHEN(i = p_eco_tbl.LAST);
1116 i := p_eco_tbl.next(i);
1117 END LOOP;
1118 x_return_status := l_overall_status;
1119 END IF;
1120
1121 EXCEPTION
1122 WHEN g_exception_halt_validation THEN
1123
1124 -- No action necessary. Validation can continue to next attribute/column
1125
1126 NULL;
1127 WHEN okl_api.g_exception_error THEN
1128 x_return_status := okl_api.handle_exceptions(l_api_name
1129 ,g_pkg_name
1130 ,'OKL_API.G_RET_STS_ERROR'
1131 ,x_msg_count
1132 ,x_msg_data
1133 ,'_PVT');
1134 WHEN okl_api.g_exception_unexpected_error THEN
1135 x_return_status := okl_api.handle_exceptions(l_api_name
1136 ,g_pkg_name
1137 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1138 ,x_msg_count
1139 ,x_msg_data
1140 ,'_PVT');
1141 WHEN OTHERS THEN
1142 x_return_status := okl_api.handle_exceptions(l_api_name
1143 ,g_pkg_name
1144 ,'OTHERS'
1145 ,x_msg_count
1146 ,x_msg_data
1147 ,'_PVT');
1148 END update_row;
1149
1150 --------------------------------------------------------------------------------
1151 -- Procedure delete_row
1152 --------------------------------------------------------------------------------
1153
1154 PROCEDURE delete_row(p_api_version IN number
1155 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1156 ,x_return_status OUT NOCOPY varchar2
1157 ,x_msg_count OUT NOCOPY number
1158 ,x_msg_data OUT NOCOPY varchar2
1159 ,p_eco_rec IN okl_eco_rec) IS
1160 l_api_version CONSTANT number := 1;
1161 l_api_name CONSTANT varchar2(30) := 'delete_row';
1162 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1163 l_eco_rec okl_eco_rec := p_eco_rec;
1164 l_row_notfound boolean := true;
1165
1166 BEGIN
1167 l_return_status := okl_api.start_activity(l_api_name
1168 ,g_pkg_name
1169 ,p_init_msg_list
1170 ,l_api_version
1171 ,p_api_version
1172 ,'_PVT'
1173 ,x_return_status);
1174
1175 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1176 RAISE okl_api.g_exception_unexpected_error;
1177 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1178 RAISE okl_api.g_exception_error;
1179 END IF;
1180
1181 DELETE FROM okl_fe_crit_cat_objects
1182 WHERE object_class_id = l_eco_rec.object_class_id;
1183
1184 EXCEPTION
1185 WHEN g_exception_halt_validation THEN
1186
1187 -- No action necessary. Validation can continue to next attribute/column
1188
1189 NULL;
1190 WHEN okl_api.g_exception_error THEN
1194 ,x_msg_count
1191 x_return_status := okl_api.handle_exceptions(l_api_name
1192 ,g_pkg_name
1193 ,'OKL_API.G_RET_STS_ERROR'
1195 ,x_msg_data
1196 ,'_PVT');
1197 WHEN okl_api.g_exception_unexpected_error THEN
1198 x_return_status := okl_api.handle_exceptions(l_api_name
1199 ,g_pkg_name
1200 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1201 ,x_msg_count
1202 ,x_msg_data
1203 ,'_PVT');
1204 WHEN OTHERS THEN
1205 x_return_status := okl_api.handle_exceptions(l_api_name
1206 ,g_pkg_name
1207 ,'OTHERS'
1208 ,x_msg_count
1209 ,x_msg_data
1210 ,'_PVT');
1211 END delete_row;
1212
1213 --------------------------------------------------------------------------------
1214 -- Procedure delete_row_tbl
1215 --------------------------------------------------------------------------------
1216
1217 PROCEDURE delete_row(p_api_version IN number
1218 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1219 ,x_return_status OUT NOCOPY varchar2
1220 ,x_msg_count OUT NOCOPY number
1221 ,x_msg_data OUT NOCOPY varchar2
1222 ,p_eco_tbl IN okl_eco_tbl) IS
1223 l_api_version CONSTANT number := 1;
1224 l_api_name CONSTANT varchar2(30) := 'delete_row_tbl';
1225 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1226 i number := 0;
1227 l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
1228
1229 BEGIN
1230 okl_api.init_msg_list(p_init_msg_list);
1231
1232 -- Make sure PL/SQL table has records in it before passing
1233
1234 IF (p_eco_tbl.COUNT > 0) THEN
1235 i := p_eco_tbl.FIRST;
1236
1237 LOOP
1238 delete_row(p_api_version => p_api_version
1239 ,p_init_msg_list => okl_api.g_false
1240 ,x_return_status => x_return_status
1241 ,x_msg_count => x_msg_count
1242 ,x_msg_data => x_msg_data
1243 ,p_eco_rec => p_eco_tbl(i));
1244 IF x_return_status <> okl_api.g_ret_sts_success THEN
1245 IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1246 l_overall_status := x_return_status;
1247 END IF;
1248 END IF;
1249 EXIT WHEN(i = p_eco_tbl.LAST);
1250 i := p_eco_tbl.next(i);
1251 END LOOP;
1252 x_return_status := l_overall_status;
1253 END IF;
1254
1255 EXCEPTION
1256 WHEN g_exception_halt_validation THEN
1257
1258 -- No action necessary. Validation can continue to next attribute/column
1259
1260 NULL;
1261 WHEN okl_api.g_exception_error THEN
1262 x_return_status := okl_api.handle_exceptions(l_api_name
1263 ,g_pkg_name
1264 ,'OKL_API.G_RET_STS_ERROR'
1265 ,x_msg_count
1266 ,x_msg_data
1267 ,'_PVT');
1268 WHEN okl_api.g_exception_unexpected_error THEN
1269 x_return_status := okl_api.handle_exceptions(l_api_name
1270 ,g_pkg_name
1271 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1272 ,x_msg_count
1273 ,x_msg_data
1274 ,'_PVT');
1275 WHEN OTHERS THEN
1276 x_return_status := okl_api.handle_exceptions(l_api_name
1277 ,g_pkg_name
1278 ,'OTHERS'
1279 ,x_msg_count
1280 ,x_msg_data
1281 ,'_PVT');
1282 END delete_row;
1283 ----------------------------------------
1284 -- Procedure LOAD_SEED_ROW
1285 ----------------------------------------
1286 PROCEDURE LOAD_SEED_ROW(p_object_class_id IN VARCHAR2,
1287 p_object_version_number IN VARCHAR2,
1288 p_crit_cat_def_id IN VARCHAR2,
1289 p_object_class_code IN VARCHAR2,
1290 p_owner IN VARCHAR2,
1291 p_last_update_date IN VARCHAR2) IS
1292
1293 id NUMBER;
1294 f_luby NUMBER; -- entity owner in file
1295 f_ludate DATE; -- entity update date in file
1296 db_luby NUMBER; -- entity owner in db
1297 db_ludate DATE; -- entity update date in db
1298 l_api_version CONSTANT number := 1;
1299 l_api_name CONSTANT varchar2(30) := 'LOAD_SEED_ROW';
1300 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1301 l_msg_count number;
1302 l_msg_data varchar2(4000);
1303 l_init_msg_list VARCHAR2(1):= 'T';
1304
1305 BEGIN
1306 -- Translate owner to file_last_updated_by
1307 f_luby := fnd_load_util.owner_id(p_owner);
1308 -- Translate char last_update_date to date
1309 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
1310
1311 BEGIN
1312
1313 SELECT ID , LAST_UPDATED_BY, LAST_UPDATE_DATE
1314 into id, db_luby, db_ludate
1315 from OKL_FE_CRIT_CAT_OBJECTS
1316 where OBJECT_CLASS_ID = p_object_class_id;
1317
1318 IF(fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
1319 db_ludate, '')) then
1320 UPDATE OKL_FE_CRIT_CAT_OBJECTS
1321 SET
1322 OBJECT_VERSION_NUMBER = p_object_version_number,
1323 CRIT_CAT_DEF_ID = to_number(p_crit_cat_def_id),
1324 OBJECT_CLASS_CODE = p_object_class_code,
1325 LAST_UPDATE_DATE = f_ludate,
1326 LAST_UPDATED_BY = f_luby,
1327 LAST_UPDATE_LOGIN = 0
1328 WHERE OBJECT_CLASS_ID = to_number(p_object_class_id);
1329 END IF;
1330 exception
1331 when no_data_found then
1332 INSERT INTO OKL_FE_CRIT_CAT_OBJECTS
1333 (OBJECT_CLASS_ID,
1334 OBJECT_VERSION_NUMBER,
1335 CRIT_CAT_DEF_ID,
1336 OBJECT_CLASS_CODE,
1337 CREATED_BY,
1338 CREATION_DATE,
1339 LAST_UPDATED_BY,
1340 LAST_UPDATE_DATE,
1341 LAST_UPDATE_LOGIN
1342 )
1343 VALUES(
1344 TO_NUMBER(p_object_class_id),
1345 p_OBJECT_VERSION_NUMBER,
1346 TO_NUMBER(p_crit_cat_def_id),
1347 p_object_class_code,
1348 f_luby,
1349 f_ludate,
1350 f_luby,
1351 f_ludate,
1352 0);
1353 END;
1354 END LOAD_SEED_ROW;
1355
1356 END okl_eco_pvt;