[Home] [Help]
PACKAGE BODY: APPS.OKL_ECC_PVT
Source
1 PACKAGE BODY okl_ecc_pvt AS
2 /* $Header: OKLSECCB.pls 120.7 2007/01/09 08:41:56 abhsaxen 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 -- PROCEDURE add_language
36 ---------------------------------------------------------------------------
37
38 PROCEDURE add_language IS
39
40 BEGIN
41
42 DELETE FROM OKL_FE_CRIT_CAT_DEF_TL t
43 WHERE NOT EXISTS(SELECT NULL
44 FROM OKL_FE_CRIT_CAT_DEF_B b
45 WHERE b.crit_cat_def_id = t.crit_cat_def_id);
46
47 UPDATE OKL_FE_CRIT_CAT_DEF_TL t
48 SET(crit_cat_desc) = (SELECT
49 -- LANGUAGE,
50
51 -- B.LANGUAGE,
52
53 b.crit_cat_desc
54 FROM OKL_FE_CRIT_CAT_DEF_TL b
55 WHERE b.crit_cat_def_id = t.crit_cat_def_id
56 AND b.language = t.source_lang)
57 WHERE (t.crit_cat_def_id, t.language) IN(SELECT subt.crit_cat_def_id ,subt.language
58 FROM OKL_FE_CRIT_CAT_DEF_TL subb ,OKL_FE_CRIT_CAT_DEF_TL subt
59 WHERE subb.crit_cat_def_id = subt.crit_cat_def_id AND subb.language = subt.language AND ( -- SUBB.LANGUAGE <> SUBT.LANGUAGE OR
60 subb.crit_cat_desc <> subt.crit_cat_desc OR (subb.language IS NOT NULL
61 AND subt.language IS NULL)
62 OR (subb.crit_cat_desc IS NULL AND subt.crit_cat_desc IS NOT NULL)));
63
64 INSERT INTO OKL_FE_CRIT_CAT_DEF_TL
65 (crit_cat_def_id,
66 language,
67 source_lang,
68 sfwt_flag,
69 crit_cat_desc,
70 CREATED_BY,
71 CREATION_DATE,
72 LAST_UPDATED_BY,
73 LAST_UPDATE_DATE,
74 LAST_UPDATE_LOGIN)
75 SELECT b.crit_cat_def_id,
76 l.language_code,
77 b.source_lang,
78 b.sfwt_flag,
79 b.crit_cat_desc,
80 b.CREATED_BY,
81 b.CREATION_DATE,
82 b.LAST_UPDATED_BY,
83 b.LAST_UPDATE_DATE,
84 b.LAST_UPDATE_LOGIN
85 FROM OKL_FE_CRIT_CAT_DEF_TL b
86 ,fnd_languages l
87 WHERE l.installed_flag IN('I', 'B')
88 AND b.language = userenv('LANG')
89 AND NOT EXISTS(SELECT NULL
90 FROM OKL_FE_CRIT_CAT_DEF_TL t
91 WHERE t.crit_cat_def_id = b.crit_cat_def_id AND t.language = l.language_code);
92
93 END add_language;
94
95
96 --------------------------------------------------------------------------------
97 -- Procedure migrate v to b
98 --------------------------------------------------------------------------------
99
100 PROCEDURE migrate(p_from IN okl_eccv_rec
101 ,p_to IN OUT NOCOPY okl_eccb_rec) IS
102
103 BEGIN
104 p_to.crit_cat_def_id := p_from.crit_cat_def_id;
105 p_to.object_version_number := p_from.object_version_number;
106 p_to.crit_cat_name := p_from.crit_cat_name;
107 p_to.ecc_ac_flag := p_from.ecc_ac_flag;
108 p_to.orig_crit_cat_def_id := p_from.orig_crit_cat_def_id;
109 p_to.value_type_code := p_from.value_type_code;
110 p_to.data_type_code := p_from.data_type_code;
111 p_to.enabled_yn := p_from.enabled_yn;
112 p_to.seeded_yn := p_from.seeded_yn;
113 p_to.function_id := p_from.function_id;
114 p_to.source_yn := p_from.source_yn;
115 p_to.sql_statement := p_from.sql_statement;
116 p_to.created_by := p_from.created_by;
117 p_to.creation_date := p_from.creation_date;
118 p_to.last_updated_by := p_from.last_updated_by;
119 p_to.last_update_date := p_from.last_update_date;
120 p_to.last_update_login := p_from.last_update_login;
121 END migrate;
122
123 --------------------------------------------------------------------------------
124 -- Procedure migrate b to v
125 --------------------------------------------------------------------------------
126
127 PROCEDURE migrate(p_from IN okl_eccb_rec
128 ,p_to IN OUT NOCOPY okl_eccv_rec) IS
129
130 BEGIN
131 p_to.crit_cat_def_id := p_from.crit_cat_def_id;
132 p_to.ecc_ac_flag := p_from.ecc_ac_flag;
133 p_to.object_version_number := p_from.object_version_number;
134 p_to.crit_cat_name := p_from.crit_cat_name;
135 p_to.orig_crit_cat_def_id := p_from.orig_crit_cat_def_id;
136 p_to.value_type_code := p_from.value_type_code;
137 p_to.data_type_code := p_from.data_type_code;
138 p_to.enabled_yn := p_from.enabled_yn;
139 p_to.seeded_yn := p_from.seeded_yn;
140 p_to.function_id := p_from.function_id;
141 p_to.source_yn := p_from.source_yn;
142 p_to.sql_statement := p_from.sql_statement;
143 p_to.created_by := p_from.created_by;
144 p_to.creation_date := p_from.creation_date;
145 p_to.last_updated_by := p_from.last_updated_by;
146 p_to.last_update_date := p_from.last_update_date;
147 p_to.last_update_login := p_from.last_update_login;
148 END migrate;
149
150 --------------------------------------------------------------------------------
151 -- Procedure migrate v to tl
152 --------------------------------------------------------------------------------
153
154 PROCEDURE migrate(p_from IN okl_eccv_rec
155 ,p_to IN OUT NOCOPY okl_ecctl_rec) IS
156
157 BEGIN
158 p_to.crit_cat_def_id := p_from.crit_cat_def_id;
159 p_to.sfwt_flag := p_from.sfwt_flag;
160 p_to.crit_cat_desc := p_from.crit_cat_desc;
161 p_to.created_by := p_from.created_by;
162 p_to.creation_date := p_from.creation_date;
163 p_to.last_updated_by := p_from.last_updated_by;
164 p_to.last_update_date := p_from.last_update_date;
165 p_to.last_update_login := p_from.last_update_login;
166 END migrate;
167
168 --------------------------------------------------------------------------------
169 -- Procedure migrate tl to v
170 --------------------------------------------------------------------------------
171
172 PROCEDURE migrate(p_from IN okl_ecctl_rec
173 ,p_to IN OUT NOCOPY okl_eccv_rec) IS
174
175 BEGIN
176 p_to.crit_cat_def_id := p_from.crit_cat_def_id;
177 p_to.sfwt_flag := p_from.sfwt_flag;
178 p_to.crit_cat_desc := p_from.crit_cat_desc;
179 p_to.created_by := p_from.created_by;
180 p_to.creation_date := p_from.creation_date;
181 p_to.last_updated_by := p_from.last_updated_by;
182 p_to.last_update_date := p_from.last_update_date;
183 p_to.last_update_login := p_from.last_update_login;
184 END migrate;
185
186 ------------------------------------
187 -- FUNCTION validate_crit_cat_def_id
188 ------------------------------------
189
190 FUNCTION validate_crit_cat_def_id(p_crit_cat_def_id IN number) RETURN varchar2 IS
191 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_crit_cat_def_id';
192
193 BEGIN
194
195 --crit_cat_def_id is required
196
197 IF ((p_crit_cat_def_id IS NULL) OR (p_crit_cat_def_id = g_miss_num)) THEN
198 okl_api.set_message(p_app_name => g_app_name
199 ,p_msg_name => g_required_value
200 ,p_token1 => g_col_name_token
201 ,p_token1_value => 'crit_cat_def_id');
202 RAISE okl_api.g_exception_error;
203 END IF;
204 RETURN g_ret_sts_success;
205 EXCEPTION
206 WHEN okl_api.g_exception_error THEN
207 RETURN g_ret_sts_error;
208 WHEN okl_api.g_exception_unexpected_error THEN
209 RETURN g_ret_sts_unexp_error;
210 WHEN OTHERS THEN
211 okl_api.set_message(p_app_name => g_app_name
212 ,p_msg_name => g_db_error
213 ,p_token1 => g_prog_name_token
214 ,p_token1_value => l_api_name
215 ,p_token2 => 'SQLCODE'
216 ,p_token2_value => sqlcode
217 ,p_token3 => 'SQLERRM'
218 ,p_token3_value => sqlerrm);
219 RETURN g_ret_sts_unexp_error;
220 END validate_crit_cat_def_id;
221
222 -------------------------------------------
223 -- Function validate_object_version_number
224 -------------------------------------------
225
226 FUNCTION validate_object_version_number(p_object_version_number IN number) RETURN varchar2 IS
227 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_object_version_number';
228
229 BEGIN
230
231 --object_version_number is required
232
233 IF (p_object_version_number IS NULL) OR (p_object_version_number = g_miss_num) THEN
234 okl_api.set_message(p_app_name => g_app_name
235 ,p_msg_name => g_required_value
236 ,p_token1 => g_col_name_token
237 ,p_token1_value => 'object_version_number');
238 RAISE okl_api.g_exception_error;
239 END IF;
240 RETURN g_ret_sts_success;
241 EXCEPTION
242 WHEN okl_api.g_exception_error THEN
243 RETURN g_ret_sts_error;
244 WHEN okl_api.g_exception_unexpected_error THEN
245 RETURN g_ret_sts_unexp_error;
246 WHEN OTHERS THEN
247 okl_api.set_message(p_app_name => g_app_name
248 ,p_msg_name => g_db_error
249 ,p_token1 => g_prog_name_token
250 ,p_token1_value => l_api_name
251 ,p_token2 => 'SQLCODE'
252 ,p_token2_value => sqlcode
253 ,p_token3 => 'SQLERRM'
254 ,p_token3_value => sqlerrm);
255 RETURN g_ret_sts_unexp_error;
256 END validate_object_version_number;
257
258 ---------------------------------
259 -- FUNCTION validate_name
260 ---------------------------------
261
262 FUNCTION validate_name(p_name IN varchar2) RETURN varchar2 IS
263 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_name';
264
265 BEGIN
266
267 --name is required
268
269 IF ((p_name IS NULL) OR (p_name = g_miss_char)) THEN
270 okl_api.set_message(p_app_name => g_app_name
271 ,p_msg_name => g_required_value
272 ,p_token1 => g_col_name_token
273 ,p_token1_value => 'Name');
274 RAISE okl_api.g_exception_error;
275 END IF;
276 RETURN g_ret_sts_success;
277 EXCEPTION
278 WHEN okl_api.g_exception_error THEN
279 RETURN g_ret_sts_error;
280 WHEN okl_api.g_exception_unexpected_error THEN
281 RETURN g_ret_sts_unexp_error;
282 WHEN OTHERS THEN
283 okl_api.set_message(p_app_name => g_app_name
284 ,p_msg_name => g_db_error
285 ,p_token1 => g_prog_name_token
286 ,p_token1_value => l_api_name
287 ,p_token2 => 'SQLCODE'
288 ,p_token2_value => sqlcode
289 ,p_token3 => 'SQLERRM'
290 ,p_token3_value => sqlerrm);
291 RETURN g_ret_sts_unexp_error;
292 END validate_name;
293
294 -------------------------------------
295 -- FUNCTION validate_value_type_code
296 -------------------------------------
297
298 FUNCTION validate_value_type_code(p_value_type_code IN varchar2) RETURN varchar2 IS
299 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_value_type_code';
300 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
301
302 BEGIN
303
304 --value_type_code is required
305
306 IF ((p_value_type_code IS NULL) OR (p_value_type_code = g_miss_char)) THEN
307 okl_api.set_message(p_app_name => g_app_name
308 ,p_msg_name => g_required_value
309 ,p_token1 => g_col_name_token
310 ,p_token1_value => 'VALUE_TYPE_CODE');
311 RAISE okl_api.g_exception_error;
312 END IF;
313
314 --value_type_code should belong to lookup type OKL_ECC_VALUE_TYPE
315
316 l_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_ECC_VALUE_TYPE'
317 ,p_lookup_code => p_value_type_code);
318
319 IF (l_return_status = okl_api.g_ret_sts_error) THEN
320 okl_api.set_message(p_app_name => g_app_name
321 ,p_msg_name => g_invalid_value
322 ,p_token1 => g_col_name_token
323 ,p_token1_value => 'VALUE_TYPE_CODE');
324 RAISE okl_api.g_exception_error;
325 END IF;
326 RETURN g_ret_sts_success;
327 EXCEPTION
328 WHEN okl_api.g_exception_error THEN
329 RETURN g_ret_sts_error;
330 WHEN okl_api.g_exception_unexpected_error THEN
331 RETURN g_ret_sts_unexp_error;
332 WHEN OTHERS THEN
333 okl_api.set_message(p_app_name => g_app_name
334 ,p_msg_name => g_db_error
335 ,p_token1 => g_prog_name_token
336 ,p_token1_value => l_api_name
337 ,p_token2 => 'SQLCODE'
338 ,p_token2_value => sqlcode
339 ,p_token3 => 'SQLERRM'
340 ,p_token3_value => sqlerrm);
341 RETURN g_ret_sts_unexp_error;
342 END validate_value_type_code;
343
344 ---------------------------------
345 -- Function validate_ECC_AC_FLAG
346 ---------------------------------
347
348 FUNCTION validate_ecc_ac_flag(p_ecc_ac_flag IN varchar2) RETURN varchar2 IS
349 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_ecc_ac_flag';
350
351 BEGIN
352
353 --ecc_ac_flag is required
354
355 IF ((p_ecc_ac_flag IS NULL) OR (p_ecc_ac_flag = g_miss_char)) THEN
356 okl_api.set_message(p_app_name => g_app_name
357 ,p_msg_name => g_required_value
358 ,p_token1 => g_col_name_token
359 ,p_token1_value => 'ECC_AC_FLAG');
360 RAISE okl_api.g_exception_error;
361 END IF;
362
363 -- ecc_ac_flag should either be ECC or AC
364
365 IF ( NOT ((p_ecc_ac_flag = 'ECC') OR (p_ecc_ac_flag = 'AC'))) THEN
366 okl_api.set_message(p_app_name => g_app_name
367 ,p_msg_name => g_invalid_value
368 ,p_token1 => g_col_name_token
369 ,p_token1_value => 'ECC_AC_FLAG');
370 RAISE okl_api.g_exception_error;
371 END IF;
372 RETURN g_ret_sts_success;
373 EXCEPTION
374 WHEN okl_api.g_exception_error THEN
375 RETURN g_ret_sts_error;
376 WHEN okl_api.g_exception_unexpected_error THEN
377 RETURN g_ret_sts_unexp_error;
378 WHEN OTHERS THEN
379 okl_api.set_message(p_app_name => g_app_name
380 ,p_msg_name => g_db_error
381 ,p_token1 => g_prog_name_token
382 ,p_token1_value => l_api_name
383 ,p_token2 => 'SQLCODE'
384 ,p_token2_value => sqlcode
385 ,p_token3 => 'SQLERRM'
386 ,p_token3_value => sqlerrm);
387 RETURN g_ret_sts_unexp_error;
388 END validate_ecc_ac_flag;
389
390 ------------------------------------
391 -- FUNCTION validate_data_type_code
392 ------------------------------------
393
394 FUNCTION validate_data_type_code(p_data_type_code IN varchar2) RETURN varchar2 IS
395 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_data_type_code';
396 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
397
398 BEGIN
399
400 --data_type_code is required
401
402 IF ((p_data_type_code IS NULL) OR (p_data_type_code = g_miss_char)) THEN
403 okl_api.set_message(p_app_name => g_app_name
404 ,p_msg_name => g_required_value
405 ,p_token1 => g_col_name_token
406 ,p_token1_value => 'DATA_TYPE_CODE');
407 RAISE okl_api.g_exception_error;
408 END IF;
409
410 --data_type_code should belong to lookup type OKL_ECC_DATA_TYPE
411
412 l_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_ECC_DATA_TYPE'
413 ,p_lookup_code => p_data_type_code);
414
415 IF (l_return_status = okl_api.g_ret_sts_error) THEN
416 okl_api.set_message(p_app_name => g_app_name
417 ,p_msg_name => g_invalid_value
418 ,p_token1 => g_col_name_token
419 ,p_token1_value => 'DATA_TYPE_CODE');
420 RAISE okl_api.g_exception_error;
421 END IF;
422 RETURN g_ret_sts_success;
423 EXCEPTION
424 WHEN okl_api.g_exception_error THEN
425 RETURN g_ret_sts_error;
426 WHEN okl_api.g_exception_unexpected_error THEN
427 RETURN g_ret_sts_unexp_error;
428 WHEN OTHERS THEN
429 okl_api.set_message(p_app_name => g_app_name
430 ,p_msg_name => g_db_error
431 ,p_token1 => g_prog_name_token
432 ,p_token1_value => l_api_name
433 ,p_token2 => 'SQLCODE'
434 ,p_token2_value => sqlcode
435 ,p_token3 => 'SQLERRM'
436 ,p_token3_value => sqlerrm);
437 RETURN g_ret_sts_unexp_error;
438 END validate_data_type_code;
439
440 ---------------------------------
441 -- FUNCTION validate_seeded_yn
442 ---------------------------------
443
444 FUNCTION validate_seeded_yn(p_seeded_yn IN varchar2) RETURN varchar2 IS
445 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_seeded_yn';
446 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
447
448 BEGIN
449
450 --seeded_yn is required
451
452 IF ((p_seeded_yn IS NULL) OR (p_seeded_yn = g_miss_char)) THEN
453 okl_api.set_message(p_app_name => g_app_name
454 ,p_msg_name => g_required_value
455 ,p_token1 => g_col_name_token
456 ,p_token1_value => 'seeded_yn');
457 RAISE okl_api.g_exception_error;
458 END IF;
459
460 --seeded_yn should belong to lookup type OKL_YES_NO
461
462 l_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_YES_NO'
463 ,p_lookup_code => p_seeded_yn);
464
465 IF (l_return_status = okl_api.g_ret_sts_error) THEN
466 okl_api.set_message(p_app_name => g_app_name
467 ,p_msg_name => g_invalid_value
468 ,p_token1 => g_col_name_token
469 ,p_token1_value => 'seeded_yn');
470 RAISE okl_api.g_exception_error;
471 END IF;
472 RETURN g_ret_sts_success;
473 EXCEPTION
474 WHEN okl_api.g_exception_error THEN
475 RETURN g_ret_sts_error;
476 WHEN okl_api.g_exception_unexpected_error THEN
477 RETURN g_ret_sts_unexp_error;
478 WHEN OTHERS THEN
479 okl_api.set_message(p_app_name => g_app_name
480 ,p_msg_name => g_db_error
481 ,p_token1 => g_prog_name_token
482 ,p_token1_value => l_api_name
483 ,p_token2 => 'SQLCODE'
484 ,p_token2_value => sqlcode
485 ,p_token3 => 'SQLERRM'
486 ,p_token3_value => sqlerrm);
487 RETURN g_ret_sts_unexp_error;
488 END validate_seeded_yn;
489
490 ---------------------------------
491 -- FUNCTION validate_enabled_yn
492 ---------------------------------
493
494 FUNCTION validate_enabled_yn(p_enabled_yn IN varchar2) RETURN varchar2 IS
495 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_enabled_yn';
496 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
497
498 BEGIN
499
500 --enabled_yn is required
501
502 IF ((p_enabled_yn IS NULL) OR (p_enabled_yn = g_miss_char)) THEN
503 okl_api.set_message(p_app_name => g_app_name
504 ,p_msg_name => g_required_value
505 ,p_token1 => g_col_name_token
506 ,p_token1_value => 'enabled_yn');
507 RAISE okl_api.g_exception_error;
508 END IF;
509
510 --enabled_yn should belong to lookup type OKL_YES_NO
511
512 l_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_YES_NO'
513 ,p_lookup_code => p_enabled_yn);
514
515 IF (l_return_status = okl_api.g_ret_sts_error) THEN
516 okl_api.set_message(p_app_name => g_app_name
517 ,p_msg_name => g_invalid_value
518 ,p_token1 => g_col_name_token
519 ,p_token1_value => 'enabled_yn');
520 RAISE okl_api.g_exception_error;
521 END IF;
522 RETURN g_ret_sts_success;
523 EXCEPTION
524 WHEN okl_api.g_exception_error THEN
525 RETURN g_ret_sts_error;
526 WHEN okl_api.g_exception_unexpected_error THEN
527 RETURN g_ret_sts_unexp_error;
528 WHEN OTHERS THEN
529 okl_api.set_message(p_app_name => g_app_name
530 ,p_msg_name => g_db_error
531 ,p_token1 => g_prog_name_token
532 ,p_token1_value => l_api_name
533 ,p_token2 => 'SQLCODE'
534 ,p_token2_value => sqlcode
535 ,p_token3 => 'SQLERRM'
536 ,p_token3_value => sqlerrm);
537 RETURN g_ret_sts_unexp_error;
538 END validate_enabled_yn;
539
540 ---------------------------------
541 -- FUNCTION validate_source_yn
542 ---------------------------------
543
544 FUNCTION validate_source_yn(p_source_yn IN varchar2) RETURN varchar2 IS
545 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_source_yn';
546 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
547
548 BEGIN
549
550 --source_yn is required
551
552 IF ((p_source_yn IS NULL) OR (p_source_yn = g_miss_char)) THEN
553 okl_api.set_message(p_app_name => g_app_name
554 ,p_msg_name => g_required_value
555 ,p_token1 => g_col_name_token
556 ,p_token1_value => 'source_yn');
557 RAISE okl_api.g_exception_error;
558 END IF;
559
560 --source_yn should belong to lookup type OKL_YES_NO
561
562 l_return_status := okl_util.check_lookup_code(p_lookup_type => 'OKL_YES_NO'
563 ,p_lookup_code => p_source_yn);
564
565 IF (l_return_status = okl_api.g_ret_sts_error) THEN
566 okl_api.set_message(p_app_name => g_app_name
567 ,p_msg_name => g_invalid_value
568 ,p_token1 => g_col_name_token
569 ,p_token1_value => 'source_yn');
570 RAISE okl_api.g_exception_error;
571 END IF;
572 RETURN g_ret_sts_success;
573 EXCEPTION
574 WHEN okl_api.g_exception_error THEN
575 RETURN g_ret_sts_error;
576 WHEN okl_api.g_exception_unexpected_error THEN
577 RETURN g_ret_sts_unexp_error;
578 WHEN OTHERS THEN
579 okl_api.set_message(p_app_name => g_app_name
580 ,p_msg_name => g_db_error
581 ,p_token1 => g_prog_name_token
582 ,p_token1_value => l_api_name
583 ,p_token2 => 'SQLCODE'
584 ,p_token2_value => sqlcode
585 ,p_token3 => 'SQLERRM'
586 ,p_token3_value => sqlerrm);
587 RETURN g_ret_sts_unexp_error;
588 END validate_source_yn;
589
590 ---------------------------------------------------------------------------
591 -- PROCEDURE lock_row
592 ---------------------------------------------------------------------------
593 ---------------------------------------
594 -- lock_row for:OKL_FE_CRIT_CAT_DEF_B --
595 ---------------------------------------
596
597 PROCEDURE lock_row(p_init_msg_list IN varchar2
598 ,x_return_status OUT NOCOPY varchar2
599 ,x_msg_count OUT NOCOPY number
600 ,x_msg_data OUT NOCOPY varchar2
601 ,p_eccb_rec IN okl_eccb_rec) IS
602 e_resource_busy EXCEPTION;
603
604 PRAGMA exception_init(e_resource_busy, - 00054);
605
606 CURSOR lock_csr(p_eccb_rec IN okl_eccb_rec) IS
607 SELECT object_version_number
608 FROM okl_fe_crit_cat_def_b
609 WHERE crit_cat_def_id = p_eccb_rec.crit_cat_def_id
610 AND object_version_number = p_eccb_rec.object_version_number
611 FOR UPDATE OF object_version_number NOWAIT;
612
613 CURSOR lchk_csr(p_eccb_rec IN okl_eccb_rec) IS
614 SELECT object_version_number
615 FROM okl_fe_crit_cat_def_b
616 WHERE crit_cat_def_id = p_eccb_rec.crit_cat_def_id;
617 l_api_version CONSTANT number := 1;
618 l_api_name CONSTANT varchar2(30) := 'B_lock_row';
619 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
620 l_object_version_number okl_fe_crit_cat_def_b.object_version_number%TYPE;
621 lc_object_version_number okl_fe_crit_cat_def_b.object_version_number%TYPE;
622 l_row_notfound boolean := false;
623 lc_row_notfound boolean := false;
624
625 BEGIN
626 l_return_status := okl_api.start_activity(l_api_name
627 ,p_init_msg_list
628 ,'_PVT'
629 ,x_return_status);
630
631 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
632 RAISE okl_api.g_exception_unexpected_error;
633 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
634 RAISE okl_api.g_exception_error;
635 END IF;
636
637 BEGIN
638 OPEN lock_csr(p_eccb_rec);
639 FETCH lock_csr INTO l_object_version_number ;
640 l_row_notfound := lock_csr%NOTFOUND;
641 CLOSE lock_csr;
642 EXCEPTION
643 WHEN e_resource_busy THEN
644
645 IF (lock_csr%ISOPEN) THEN
646 CLOSE lock_csr;
647 END IF;
648 okl_api.set_message(g_fnd_app, g_form_unable_to_reserve_rec);
649 RAISE app_exceptions.record_lock_exception;
650 END;
651
652 IF (l_row_notfound) THEN
653 OPEN lchk_csr(p_eccb_rec);
654 FETCH lchk_csr INTO lc_object_version_number ;
655 lc_row_notfound := lchk_csr%NOTFOUND;
656 CLOSE lchk_csr;
657 END IF;
658
659 IF (lc_row_notfound) THEN
660 okl_api.set_message(g_fnd_app, g_form_record_deleted);
661 RAISE okl_api.g_exception_error;
662 ELSIF lc_object_version_number > p_eccb_rec.object_version_number THEN
663 okl_api.set_message(g_fnd_app, g_form_record_changed);
664 RAISE okl_api.g_exception_error;
665 ELSIF lc_object_version_number <> p_eccb_rec.object_version_number THEN
666 okl_api.set_message(g_fnd_app, g_form_record_changed);
667 RAISE okl_api.g_exception_error;
668 ELSIF lc_object_version_number = - 1 THEN
669 okl_api.set_message(g_app_name, g_record_logically_deleted);
670 RAISE okl_api.g_exception_error;
671 END IF;
672 okl_api.end_activity(x_msg_count, x_msg_data);
673 EXCEPTION
674 WHEN g_exception_halt_validation THEN
675 x_return_status := okl_api.handle_exceptions(l_api_name
676 ,g_pkg_name
677 ,'OKL_API.G_RET_STS_ERROR'
678 ,x_msg_count
679 ,x_msg_data
680 ,'_PVT');
681 WHEN okl_api.g_exception_error THEN
682 x_return_status := okl_api.handle_exceptions(l_api_name
683 ,g_pkg_name
684 ,'OKL_API.G_RET_STS_ERROR'
685 ,x_msg_count
686 ,x_msg_data
687 ,'_PVT');
688 WHEN okl_api.g_exception_unexpected_error THEN
689 x_return_status := okl_api.handle_exceptions(l_api_name
690 ,g_pkg_name
691 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
692 ,x_msg_count
693 ,x_msg_data
694 ,'_PVT');
695 WHEN OTHERS THEN
696 x_return_status := okl_api.handle_exceptions(l_api_name
697 ,g_pkg_name
698 ,'OTHERS'
699 ,x_msg_count
700 ,x_msg_data
701 ,'_PVT');
702 END lock_row;
703
704 ----------------------------------------
705 -- lock_row for:OKL_FE_CRIT_CAT_DEF_TL --
706 ----------------------------------------
707
708 PROCEDURE lock_row(p_init_msg_list IN varchar2
709 ,x_return_status OUT NOCOPY varchar2
710 ,x_msg_count OUT NOCOPY number
711 ,x_msg_data OUT NOCOPY varchar2
712 ,p_ecctl_rec IN okl_ecctl_rec) IS
713 e_resource_busy EXCEPTION;
714
715 PRAGMA exception_init(e_resource_busy, - 00054);
716
717 CURSOR lock_csr(p_ecctl_rec IN okl_ecctl_rec) IS
718 SELECT *
719 FROM okl_fe_crit_cat_def_tl
720 WHERE crit_cat_def_id = p_ecctl_rec.crit_cat_def_id
721 FOR UPDATE NOWAIT;
722 l_api_version CONSTANT number := 1;
723 l_api_name CONSTANT varchar2(30) := 'TL_lock_row';
724 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
725 l_lock_var lock_csr%ROWTYPE;
726 l_row_notfound boolean := false;
727 lc_row_notfound boolean := false;
728
729 BEGIN
730 l_return_status := okl_api.start_activity(l_api_name
731 ,p_init_msg_list
732 ,'_PVT'
733 ,x_return_status);
734
735 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
736 RAISE okl_api.g_exception_unexpected_error;
737 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
738 RAISE okl_api.g_exception_error;
739 END IF;
740
741 BEGIN
742 OPEN lock_csr(p_ecctl_rec);
743 FETCH lock_csr INTO l_lock_var ;
744 l_row_notfound := lock_csr%NOTFOUND;
745 CLOSE lock_csr;
746 EXCEPTION
747 WHEN e_resource_busy THEN
748
749 IF (lock_csr%ISOPEN) THEN
750 CLOSE lock_csr;
751 END IF;
752 okl_api.set_message(g_fnd_app, g_form_unable_to_reserve_rec);
753 RAISE app_exceptions.record_lock_exception;
754 END;
755
756 IF (l_row_notfound) THEN
757 okl_api.set_message(g_fnd_app, g_form_record_deleted);
758 RAISE okl_api.g_exception_error;
759 END IF;
760 okl_api.end_activity(x_msg_count, x_msg_data);
761 EXCEPTION
762 WHEN g_exception_halt_validation THEN
763 x_return_status := okl_api.handle_exceptions(l_api_name
764 ,g_pkg_name
765 ,'OKL_API.G_RET_STS_ERROR'
766 ,x_msg_count
767 ,x_msg_data
768 ,'_PVT');
769 WHEN okl_api.g_exception_error THEN
770 x_return_status := okl_api.handle_exceptions(l_api_name
771 ,g_pkg_name
772 ,'OKL_API.G_RET_STS_ERROR'
773 ,x_msg_count
774 ,x_msg_data
775 ,'_PVT');
776 WHEN okl_api.g_exception_unexpected_error THEN
777 x_return_status := okl_api.handle_exceptions(l_api_name
778 ,g_pkg_name
779 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
780 ,x_msg_count
781 ,x_msg_data
782 ,'_PVT');
783 WHEN OTHERS THEN
784 x_return_status := okl_api.handle_exceptions(l_api_name
785 ,g_pkg_name
786 ,'OTHERS'
787 ,x_msg_count
788 ,x_msg_data
789 ,'_PVT');
790 END lock_row;
791
792 ---------------------------------------
793 -- lock_row for:OKL_TXL_AR_INV_LNS_V --
794 ---------------------------------------
795
796 PROCEDURE lock_row(p_api_version IN number
797 ,p_init_msg_list IN varchar2
798 ,x_return_status OUT NOCOPY varchar2
799 ,x_msg_count OUT NOCOPY number
800 ,x_msg_data OUT NOCOPY varchar2
801 ,p_eccv_rec IN okl_eccv_rec) IS
802 l_api_version CONSTANT number := 1;
803 l_api_name CONSTANT varchar2(30) := 'V_lock_row';
804 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
805 l_eccb_rec okl_eccb_rec;
806 l_ecctl_rec okl_ecctl_rec;
807
808 BEGIN
809 l_return_status := okl_api.start_activity(l_api_name
810 ,g_pkg_name
811 ,p_init_msg_list
812 ,l_api_version
813 ,p_api_version
814 ,'_PVT'
815 ,x_return_status);
816
817 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
818 RAISE okl_api.g_exception_unexpected_error;
819 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
820 RAISE okl_api.g_exception_error;
821 END IF;
822
823 --------------------------------------
824 -- Move VIEW record to "Child" records
825 --------------------------------------
826
827 migrate(p_eccv_rec, l_eccb_rec);
828 migrate(p_eccv_rec, l_ecctl_rec);
829
830 --------------------------------------------
831 -- Call the LOCK_ROW for each child record
832 --------------------------------------------
833
834 lock_row(p_init_msg_list
835 ,x_return_status
836 ,x_msg_count
837 ,x_msg_data
838 ,l_eccb_rec);
839
840 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
841 RAISE okl_api.g_exception_unexpected_error;
842 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
843 RAISE okl_api.g_exception_error;
844 END IF;
845 lock_row(p_init_msg_list
846 ,x_return_status
847 ,x_msg_count
848 ,x_msg_data
849 ,l_ecctl_rec);
850
851 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
852 RAISE okl_api.g_exception_unexpected_error;
853 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
854 RAISE okl_api.g_exception_error;
855 END IF;
856 okl_api.end_activity(x_msg_count, x_msg_data);
857 EXCEPTION
858 WHEN g_exception_halt_validation THEN
859 x_return_status := okl_api.handle_exceptions(l_api_name
860 ,g_pkg_name
861 ,'OKL_API.G_RET_STS_ERROR'
862 ,x_msg_count
863 ,x_msg_data
864 ,'_PVT');
865 WHEN okl_api.g_exception_error THEN
866 x_return_status := okl_api.handle_exceptions(l_api_name
867 ,g_pkg_name
868 ,'OKL_API.G_RET_STS_ERROR'
869 ,x_msg_count
870 ,x_msg_data
871 ,'_PVT');
872 WHEN okl_api.g_exception_unexpected_error THEN
873 x_return_status := okl_api.handle_exceptions(l_api_name
874 ,g_pkg_name
875 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
876 ,x_msg_count
877 ,x_msg_data
878 ,'_PVT');
879 WHEN OTHERS THEN
880 x_return_status := okl_api.handle_exceptions(l_api_name
881 ,g_pkg_name
882 ,'OTHERS'
883 ,x_msg_count
884 ,x_msg_data
885 ,'_PVT');
886 END lock_row;
887
888 --------------------------------------
889 -- PL/SQL TBL lock_row for:TILV_TBL --
890 --------------------------------------
891
892 PROCEDURE lock_row(p_api_version IN number
893 ,p_init_msg_list IN varchar2
894 ,x_return_status OUT NOCOPY varchar2
895 ,x_msg_count OUT NOCOPY number
896 ,x_msg_data OUT NOCOPY varchar2
897 ,p_eccv_tbl IN okl_eccv_tbl) IS
898 l_api_version CONSTANT number := 1;
899 l_api_name CONSTANT varchar2(30) := 'V_tbl_lock_row';
900 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
901
902 -- Begin Post-Generation Change
903 -- overall error status
904
905 l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
906
907 -- End Post-Generation Change
908
909 i number := 0;
910
911 BEGIN
912 okl_api.init_msg_list(p_init_msg_list);
913
914 -- Make sure PL/SQL table has records in it before passing
915
916 IF (p_eccv_tbl.COUNT > 0) THEN
917 i := p_eccv_tbl.FIRST;
918
919 LOOP
920 lock_row(p_api_version => p_api_version
921 ,p_init_msg_list => okl_api.g_false
922 ,x_return_status => x_return_status
923 ,x_msg_count => x_msg_count
924 ,x_msg_data => x_msg_data
925 ,p_eccv_rec => p_eccv_tbl(i));
926
927 -- Begin Post-Generation Change
928 -- store the highest degree of error
929
930 IF x_return_status <> okl_api.g_ret_sts_success THEN
931 IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
932 l_overall_status := x_return_status;
933 END IF;
934 END IF;
935
936 -- End Post-Generation Change
937
938 EXIT WHEN(i = p_eccv_tbl.LAST);
939 i := p_eccv_tbl.next(i);
940 END LOOP;
941
942 -- Begin Post-Generation Change
943 -- return overall status
944
945 x_return_status := l_overall_status;
946
947 -- End Post-Generation Change
948
949 END IF;
950
951 EXCEPTION
952 WHEN g_exception_halt_validation THEN
953 x_return_status := okl_api.handle_exceptions(l_api_name
954 ,g_pkg_name
955 ,'OKL_API.G_RET_STS_ERROR'
956 ,x_msg_count
957 ,x_msg_data
958 ,'_PVT');
959 WHEN okl_api.g_exception_error THEN
960 x_return_status := okl_api.handle_exceptions(l_api_name
961 ,g_pkg_name
962 ,'OKL_API.G_RET_STS_ERROR'
963 ,x_msg_count
964 ,x_msg_data
965 ,'_PVT');
966 WHEN okl_api.g_exception_unexpected_error THEN
967 x_return_status := okl_api.handle_exceptions(l_api_name
968 ,g_pkg_name
969 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
970 ,x_msg_count
971 ,x_msg_data
972 ,'_PVT');
973 WHEN OTHERS THEN
974 x_return_status := okl_api.handle_exceptions(l_api_name
975 ,g_pkg_name
976 ,'OTHERS'
977 ,x_msg_count
978 ,x_msg_data
979 ,'_PVT');
980 END lock_row;
981
982 --------------------------------------------------------------------------------
983 -- Procedure get_rec for OKL_FE_CRIT_CAT_DEF_B
984 --------------------------------------------------------------------------------
985
986 FUNCTION get_rec(p_eccb_rec IN okl_eccb_rec
987 ,x_no_data_found OUT NOCOPY boolean) RETURN okl_eccb_rec IS
988
989 CURSOR eccb_pk_csr(p_id IN number) IS
990 SELECT crit_cat_def_id
991 ,object_version_number
992 ,crit_cat_name
993 ,ecc_ac_flag
994 ,orig_crit_cat_def_id
995 ,value_type_code
996 ,data_type_code
997 ,enabled_yn
998 ,seeded_yn
999 ,function_id
1000 ,source_yn
1001 ,sql_statement
1002 ,created_by
1003 ,creation_date
1004 ,last_updated_by
1005 ,last_update_date
1006 ,last_update_login
1007 FROM okl_fe_crit_cat_def_b
1008 WHERE okl_fe_crit_cat_def_b.crit_cat_def_id = p_id;
1009 l_eccb_pk eccb_pk_csr%ROWTYPE;
1010 l_eccb_rec okl_eccb_rec;
1011
1012 BEGIN
1013 x_no_data_found := true;
1014
1015 --Get current data base values
1016
1017 OPEN eccb_pk_csr(p_eccb_rec.crit_cat_def_id);
1018 FETCH eccb_pk_csr INTO l_eccb_rec.crit_cat_def_id
1019 ,l_eccb_rec.object_version_number
1020 ,l_eccb_rec.crit_cat_name
1021 ,l_eccb_rec.ecc_ac_flag
1022 ,l_eccb_rec.orig_crit_cat_def_id
1023 ,l_eccb_rec.value_type_code
1024 ,l_eccb_rec.data_type_code
1025 ,l_eccb_rec.enabled_yn
1026 ,l_eccb_rec.seeded_yn
1027 ,l_eccb_rec.function_id
1028 ,l_eccb_rec.source_yn
1029 ,l_eccb_rec.sql_statement
1030 ,l_eccb_rec.created_by
1031 ,l_eccb_rec.creation_date
1032 ,l_eccb_rec.last_updated_by
1033 ,l_eccb_rec.last_update_date
1034 ,l_eccb_rec.last_update_login ;
1035 x_no_data_found := eccb_pk_csr%NOTFOUND;
1036 CLOSE eccb_pk_csr;
1037 RETURN(l_eccb_rec);
1038 END get_rec;
1039
1040 FUNCTION get_rec(p_eccb_rec IN okl_eccb_rec) RETURN okl_eccb_rec IS
1041 l_row_notfound boolean := true;
1042
1043 BEGIN
1044 RETURN(get_rec(p_eccb_rec, l_row_notfound));
1045 END get_rec;
1046
1047 --------------------------------------------------------------------------------
1048 -- Procedure get_rec for OKL_FE_CRIT_CAT_DEF_TL
1049 --------------------------------------------------------------------------------
1050
1051 FUNCTION get_rec(p_ecctl_rec IN okl_ecctl_rec
1052 ,x_no_data_found OUT NOCOPY boolean) RETURN okl_ecctl_rec IS
1053
1054 CURSOR ecctl_pk_csr(p_id IN number
1055 ,p_language IN varchar2) IS
1056 SELECT crit_cat_def_id
1057 ,language
1058 ,source_lang
1059 ,sfwt_flag
1060 ,crit_cat_desc
1061 ,created_by
1062 ,creation_date
1063 ,last_updated_by
1064 ,last_update_date
1065 ,last_update_login
1066 FROM okl_fe_crit_cat_def_tl
1067 WHERE okl_fe_crit_cat_def_tl.crit_cat_def_id = p_id
1068 AND okl_fe_crit_cat_def_tl.language = p_language;
1069 l_ecctl_pk ecctl_pk_csr%ROWTYPE;
1070 l_ecctl_rec okl_ecctl_rec;
1071
1072 BEGIN
1073 x_no_data_found := true;
1074
1075 --Get current data base values
1076
1077 OPEN ecctl_pk_csr(p_ecctl_rec.crit_cat_def_id, p_ecctl_rec.language);
1078 FETCH ecctl_pk_csr INTO l_ecctl_rec.crit_cat_def_id
1079 ,l_ecctl_rec.language
1080 ,l_ecctl_rec.source_lang
1081 ,l_ecctl_rec.sfwt_flag
1082 ,l_ecctl_rec.crit_cat_desc
1083 ,l_ecctl_rec.created_by
1084 ,l_ecctl_rec.creation_date
1085 ,l_ecctl_rec.last_updated_by
1086 ,l_ecctl_rec.last_update_date
1087 ,l_ecctl_rec.last_update_login ;
1088 x_no_data_found := ecctl_pk_csr%NOTFOUND;
1089 CLOSE ecctl_pk_csr;
1090 RETURN(l_ecctl_rec);
1091 END get_rec;
1092
1093 FUNCTION get_rec(p_ecctl_rec IN okl_ecctl_rec) RETURN okl_ecctl_rec IS
1094 l_row_notfound boolean := true;
1095
1096 BEGIN
1097 RETURN(get_rec(p_ecctl_rec, l_row_notfound));
1098 END get_rec;
1099
1100 --------------------------------------------------------------------------------
1101 -- Procedure get_rec for OKL_FE_CRIT_CAT_DEF_V
1102 --------------------------------------------------------------------------------
1103
1104 FUNCTION get_rec(p_eccv_rec IN okl_eccv_rec
1105 ,x_no_data_found OUT NOCOPY boolean) RETURN okl_eccv_rec IS
1106
1107 CURSOR eccv_pk_csr(p_id IN number) IS
1108 SELECT crit_cat_def_id
1109 ,object_version_number
1110 ,ecc_ac_flag
1111 ,orig_crit_cat_def_id
1112 ,crit_cat_name
1113 ,crit_cat_desc
1114 ,sfwt_flag
1115 ,value_type_code
1116 ,data_type_code
1117 ,enabled_yn
1118 ,seeded_yn
1119 ,function_id
1120 ,source_yn
1121 ,sql_statement
1122 ,created_by
1123 ,creation_date
1124 ,last_updated_by
1125 ,last_update_date
1126 ,last_update_login
1127 FROM okl_fe_crit_cat_def_v
1128 WHERE okl_fe_crit_cat_def_v.crit_cat_def_id = p_id;
1129 l_eccv_pk eccv_pk_csr%ROWTYPE;
1130 l_eccv_rec okl_eccv_rec;
1131
1132 BEGIN
1133 x_no_data_found := true;
1134
1135 --Get current data base values
1136
1137 OPEN eccv_pk_csr(p_eccv_rec.crit_cat_def_id);
1138 FETCH eccv_pk_csr INTO l_eccv_rec.crit_cat_def_id
1139 ,l_eccv_rec.object_version_number
1140 ,l_eccv_rec.ecc_ac_flag
1141 ,l_eccv_rec.orig_crit_cat_def_id
1142 ,l_eccv_rec.crit_cat_name
1143 ,l_eccv_rec.crit_cat_desc
1144 ,l_eccv_rec.sfwt_flag
1145 ,l_eccv_rec.value_type_code
1146 ,l_eccv_rec.data_type_code
1147 ,l_eccv_rec.enabled_yn
1148 ,l_eccv_rec.seeded_yn
1149 ,l_eccv_rec.function_id
1150 ,l_eccv_rec.source_yn
1151 ,l_eccv_rec.sql_statement
1152 ,l_eccv_rec.created_by
1153 ,l_eccv_rec.creation_date
1154 ,l_eccv_rec.last_updated_by
1155 ,l_eccv_rec.last_update_date
1156 ,l_eccv_rec.last_update_login ;
1157 x_no_data_found := eccv_pk_csr%NOTFOUND;
1158 CLOSE eccv_pk_csr;
1159 RETURN(l_eccv_rec);
1160 END get_rec;
1161
1162 FUNCTION get_rec(p_eccv_rec IN okl_eccv_rec) RETURN okl_eccv_rec IS
1163 l_row_notfound boolean := true;
1164
1165 BEGIN
1166 RETURN(get_rec(p_eccv_rec, l_row_notfound));
1167 END get_rec;
1168
1169 FUNCTION null_out_defaults(p_eccv_rec IN okl_eccv_rec) RETURN okl_eccv_rec IS
1170 l_eccv_rec okl_eccv_rec := p_eccv_rec;
1171
1172 BEGIN
1173
1174 IF (l_eccv_rec.crit_cat_def_id = okl_api.g_miss_num) THEN
1175 l_eccv_rec.crit_cat_def_id := NULL;
1176 END IF;
1177
1178 IF (l_eccv_rec.object_version_number = okl_api.g_miss_num) THEN
1179 l_eccv_rec.object_version_number := NULL;
1180 END IF;
1181
1182 IF (l_eccv_rec.ecc_ac_flag = okl_api.g_miss_char) THEN
1183 l_eccv_rec.ecc_ac_flag := NULL;
1184 END IF;
1185
1186 IF (l_eccv_rec.orig_crit_cat_def_id = okl_api.g_miss_num) THEN
1187 l_eccv_rec.orig_crit_cat_def_id := NULL;
1188 END IF;
1189
1190 IF (l_eccv_rec.crit_cat_name = okl_api.g_miss_char) THEN
1191 l_eccv_rec.crit_cat_name := NULL;
1192 END IF;
1193
1194 IF (l_eccv_rec.crit_cat_desc = okl_api.g_miss_char) THEN
1195 l_eccv_rec.crit_cat_desc := NULL;
1196 END IF;
1197
1198 IF (l_eccv_rec.sfwt_flag = okl_api.g_miss_char) THEN
1199 l_eccv_rec.sfwt_flag := NULL;
1200 END IF;
1201
1202 IF (l_eccv_rec.value_type_code = okl_api.g_miss_char) THEN
1203 l_eccv_rec.value_type_code := NULL;
1204 END IF;
1205
1206 IF (l_eccv_rec.data_type_code = okl_api.g_miss_char) THEN
1207 l_eccv_rec.data_type_code := NULL;
1208 END IF;
1209
1210 IF (l_eccv_rec.enabled_yn = okl_api.g_miss_char) THEN
1211 l_eccv_rec.enabled_yn := NULL;
1212 END IF;
1213
1214 IF (l_eccv_rec.seeded_yn = okl_api.g_miss_char) THEN
1215 l_eccv_rec.seeded_yn := NULL;
1216 END IF;
1217
1218 IF (l_eccv_rec.function_id = okl_api.g_miss_num) THEN
1219 l_eccv_rec.function_id := NULL;
1220 END IF;
1221
1222 IF (l_eccv_rec.source_yn = okl_api.g_miss_char) THEN
1223 l_eccv_rec.source_yn := NULL;
1224 END IF;
1225
1226 IF (l_eccv_rec.sql_statement = okl_api.g_miss_char) THEN
1227 l_eccv_rec.sql_statement := NULL;
1228 END IF;
1229
1230 IF (l_eccv_rec.created_by = okl_api.g_miss_num) THEN
1231 l_eccv_rec.created_by := NULL;
1232 END IF;
1233
1234 IF (l_eccv_rec.creation_date = okl_api.g_miss_date) THEN
1235 l_eccv_rec.creation_date := NULL;
1236 END IF;
1237
1238 IF (l_eccv_rec.last_updated_by = okl_api.g_miss_num) THEN
1239 l_eccv_rec.last_updated_by := NULL;
1240 END IF;
1241
1242 IF (l_eccv_rec.last_update_date = okl_api.g_miss_date) THEN
1243 l_eccv_rec.last_update_date := NULL;
1244 END IF;
1245
1246 IF (l_eccv_rec.last_update_login = okl_api.g_miss_num) THEN
1247 l_eccv_rec.last_update_login := NULL;
1248 END IF;
1249 RETURN(l_eccv_rec);
1250 END null_out_defaults;
1251
1252 FUNCTION get_seq_id RETURN number IS
1253
1254 BEGIN
1255 RETURN(okc_p_util.raw_to_number(sys_guid()));
1256 END get_seq_id;
1257
1258 -------------------------------------------------------------------------------
1259 -----validate_attributes
1260 -------------------------------------------------------------------------------
1261
1262 FUNCTION validate_attributes(p_eccv_rec IN okl_eccv_rec) RETURN varchar2 IS
1263 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1264 x_return_status varchar2(1) := okl_api.g_ret_sts_success;
1265 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_attributes';
1266
1267 BEGIN
1268
1269 -- ***
1270 -- id
1271 -- ***
1272
1273 l_return_status := validate_crit_cat_def_id(p_eccv_rec.crit_cat_def_id);
1274
1275 IF (l_return_status = g_ret_sts_unexp_error) THEN
1276 RAISE okl_api.g_exception_unexpected_error;
1277 ELSIF (l_return_status = g_ret_sts_error) THEN
1278 RAISE okl_api.g_exception_error;
1279 END IF;
1280
1281 -- ***
1282 -- object_version_number
1283 -- ***
1284
1285 l_return_status := validate_object_version_number(p_eccv_rec.object_version_number);
1286
1287 IF (l_return_status = g_ret_sts_unexp_error) THEN
1288 RAISE okl_api.g_exception_unexpected_error;
1289 ELSIF (l_return_status = g_ret_sts_error) THEN
1290 RAISE okl_api.g_exception_error;
1291 END IF;
1292
1293 -- ***
1294 -- name
1295 -- ***
1296
1297 l_return_status := validate_name(p_eccv_rec.crit_cat_name);
1298
1299 IF (l_return_status = g_ret_sts_unexp_error) THEN
1300 RAISE okl_api.g_exception_unexpected_error;
1301 ELSIF (l_return_status = g_ret_sts_error) THEN
1302 RAISE okl_api.g_exception_error;
1303 END IF;
1304
1305 -- ***
1306 -- ECC_AC_FLAG
1307 -- ***
1308
1309 l_return_status := validate_ecc_ac_flag(p_eccv_rec.ecc_ac_flag);
1310
1311 IF (l_return_status = g_ret_sts_unexp_error) THEN
1312 RAISE okl_api.g_exception_unexpected_error;
1313 ELSIF (l_return_status = g_ret_sts_error) THEN
1314 RAISE okl_api.g_exception_error;
1315 END IF;
1316
1317 -- ***
1318 -- VALUE_TYPE_CODE
1319 -- ***
1320
1321 l_return_status := validate_value_type_code(p_eccv_rec.value_type_code);
1322
1323 IF (l_return_status = g_ret_sts_unexp_error) THEN
1324 RAISE okl_api.g_exception_unexpected_error;
1325 ELSIF (l_return_status = g_ret_sts_error) THEN
1326 RAISE okl_api.g_exception_error;
1327 END IF;
1328
1329 -- ***
1330 -- DATA_TYPE_CODE
1331 -- ***
1332
1333 l_return_status := validate_data_type_code(p_eccv_rec.data_type_code);
1334
1335 IF (l_return_status = g_ret_sts_unexp_error) THEN
1336 RAISE okl_api.g_exception_unexpected_error;
1337 ELSIF (l_return_status = g_ret_sts_error) THEN
1338 RAISE okl_api.g_exception_error;
1339 END IF;
1340
1341 -- ***
1342 -- seeded_yn
1343 -- ***
1344
1345 l_return_status := validate_seeded_yn(p_eccv_rec.seeded_yn);
1346
1347 IF (l_return_status = g_ret_sts_unexp_error) THEN
1348 RAISE okl_api.g_exception_unexpected_error;
1349 ELSIF (l_return_status = g_ret_sts_error) THEN
1350 RAISE okl_api.g_exception_error;
1351 END IF;
1352
1353 -- ***
1354 -- enabled_yn
1355 -- ***
1356
1357 l_return_status := validate_enabled_yn(p_eccv_rec.enabled_yn);
1358
1359 IF (l_return_status = g_ret_sts_unexp_error) THEN
1360 RAISE okl_api.g_exception_unexpected_error;
1361 ELSIF (l_return_status = g_ret_sts_error) THEN
1362 RAISE okl_api.g_exception_error;
1363 END IF;
1364
1365 -- ***
1366 -- source_yn
1367 -- ***
1368
1369 l_return_status := validate_source_yn(p_eccv_rec.source_yn);
1370
1371 IF (l_return_status = g_ret_sts_unexp_error) THEN
1372 RAISE okl_api.g_exception_unexpected_error;
1373 ELSIF (l_return_status = g_ret_sts_error) THEN
1374 RAISE okl_api.g_exception_error;
1375 END IF;
1376 RETURN(x_return_status);
1377 EXCEPTION
1378 WHEN okl_api.g_exception_error THEN
1379 RETURN g_ret_sts_error;
1380 WHEN okl_api.g_exception_unexpected_error THEN
1381 RETURN g_ret_sts_unexp_error;
1382 WHEN OTHERS THEN
1383 okl_api.set_message(p_app_name => g_app_name
1384 ,p_msg_name => g_db_error
1385 ,p_token1 => g_prog_name_token
1386 ,p_token1_value => l_api_name
1387 ,p_token2 => 'SQLCODE'
1388 ,p_token2_value => sqlcode
1389 ,p_token3 => 'SQLERRM'
1390 ,p_token3_value => sqlerrm);
1391 RETURN g_ret_sts_unexp_error;
1392 END validate_attributes;
1393
1394 FUNCTION validate_record(p_eccv_rec IN okl_eccv_rec) RETURN varchar2 IS
1395 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1396 x_return_status varchar2(1) := okl_api.g_ret_sts_success;
1397 l_api_name CONSTANT varchar2(61) := g_pkg_name || '.' || 'validate_attributes';
1398
1399 BEGIN
1400 RETURN(x_return_status);
1401 EXCEPTION
1402 WHEN okl_api.g_exception_error THEN
1403 RETURN g_ret_sts_error;
1404 WHEN okl_api.g_exception_unexpected_error THEN
1405 RETURN g_ret_sts_unexp_error;
1406 WHEN OTHERS THEN
1407 okl_api.set_message(p_app_name => g_app_name
1408 ,p_msg_name => g_db_error
1409 ,p_token1 => g_prog_name_token
1410 ,p_token1_value => l_api_name
1411 ,p_token2 => g_sqlcode_token
1412 ,p_token2_value => sqlcode
1413 ,p_token3 => g_sqlerrm_token
1414 ,p_token3_value => sqlerrm);
1415 RETURN g_ret_sts_unexp_error;
1416 END validate_record;
1417
1418 --------------------------------------------------------------------------------
1419 -- Procedure insert_row_b
1420 --------------------------------------------------------------------------------
1421
1422 PROCEDURE insert_row(p_api_version IN number
1423 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1424 ,x_return_status OUT NOCOPY varchar2
1425 ,x_msg_count OUT NOCOPY number
1426 ,x_msg_data OUT NOCOPY varchar2
1427 ,p_eccb_rec IN okl_eccb_rec
1428 ,x_eccb_rec OUT NOCOPY okl_eccb_rec) IS
1429 l_api_version CONSTANT number := 1;
1430 l_api_name CONSTANT varchar2(30) := 'insert_row_b';
1431 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1432 l_eccb_rec okl_eccb_rec := p_eccb_rec;
1433 temp number;
1434
1435 FUNCTION set_attributes(p_eccb_rec IN okl_eccb_rec
1436 ,x_eccb_rec OUT NOCOPY okl_eccb_rec) RETURN varchar2 IS
1437 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1438
1439 BEGIN
1440 x_eccb_rec := p_eccb_rec;
1441 RETURN(l_return_status);
1442 END set_attributes;
1443
1444 BEGIN
1445 l_return_status := okl_api.start_activity(l_api_name
1446 ,g_pkg_name
1447 ,p_init_msg_list
1448 ,l_api_version
1449 ,p_api_version
1450 ,'_PVT'
1451 ,x_return_status);
1452
1453 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1454 RAISE okl_api.g_exception_unexpected_error;
1455 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1456 RAISE okl_api.g_exception_error;
1457 END IF;
1458
1459 --Setting Item Attributes
1460
1461 l_return_status := set_attributes(p_eccb_rec, l_eccb_rec);
1462
1463 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1464 RAISE okl_api.g_exception_unexpected_error;
1465 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1466 RAISE okl_api.g_exception_error;
1467 END IF;
1468
1469 INSERT INTO okl_fe_crit_cat_def_b
1470 (crit_cat_def_id
1471 ,object_version_number
1472 ,crit_cat_name
1473 ,ecc_ac_flag
1474 ,orig_crit_cat_def_id
1475 ,value_type_code
1476 ,data_type_code
1477 ,enabled_yn
1478 ,seeded_yn
1479 ,function_id
1480 ,source_yn
1481 ,sql_statement
1482 ,created_by
1483 ,creation_date
1484 ,last_updated_by
1485 ,last_update_date
1486 ,last_update_login)
1487 VALUES (l_eccb_rec.crit_cat_def_id
1488 ,l_eccb_rec.object_version_number
1489 ,l_eccb_rec.crit_cat_name
1490 ,l_eccb_rec.ecc_ac_flag
1491 ,l_eccb_rec.orig_crit_cat_def_id
1492 ,l_eccb_rec.value_type_code
1493 ,l_eccb_rec.data_type_code
1494 ,l_eccb_rec.enabled_yn
1495 ,l_eccb_rec.seeded_yn
1496 ,l_eccb_rec.function_id
1497 ,l_eccb_rec.source_yn
1498 ,l_eccb_rec.sql_statement
1499 ,l_eccb_rec.created_by
1500 ,l_eccb_rec.creation_date
1501 ,l_eccb_rec.last_updated_by
1502 ,l_eccb_rec.last_update_date
1503 ,l_eccb_rec.last_update_login);
1504
1505 --Set OUT Values
1506
1507 x_eccb_rec := l_eccb_rec;
1508 x_return_status := l_return_status;
1509 okl_api.end_activity(x_msg_count, x_msg_data);
1510 EXCEPTION
1511 WHEN g_exception_halt_validation THEN
1512
1513 -- No action necessary. Validation can continue to next attribute/column
1514
1515 NULL;
1516 WHEN okl_api.g_exception_error THEN
1517 x_return_status := okl_api.handle_exceptions(l_api_name
1518 ,g_pkg_name
1519 ,'OKL_API.G_RET_STS_ERROR'
1520 ,x_msg_count
1521 ,x_msg_data
1522 ,'_PVT');
1523 WHEN okl_api.g_exception_unexpected_error THEN
1524 x_return_status := okl_api.handle_exceptions(l_api_name
1525 ,g_pkg_name
1526 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1527 ,x_msg_count
1528 ,x_msg_data
1529 ,'_PVT');
1530 WHEN OTHERS THEN
1531 x_return_status := okl_api.handle_exceptions(l_api_name
1532 ,g_pkg_name
1533 ,'OTHERS'
1534 ,x_msg_count
1535 ,x_msg_data
1536 ,'_PVT');
1537 END insert_row;
1538
1539 --------------------------------------------------------------------------------
1540 -- Procedure insert_row_tl
1541 --------------------------------------------------------------------------------
1542
1543 PROCEDURE insert_row(p_api_version IN number
1544 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1545 ,x_return_status OUT NOCOPY varchar2
1546 ,x_msg_count OUT NOCOPY number
1547 ,x_msg_data OUT NOCOPY varchar2
1548 ,p_ecctl_rec IN okl_ecctl_rec
1549 ,x_ecctl_rec OUT NOCOPY okl_ecctl_rec) IS
1550 l_api_version CONSTANT number := 1;
1551 l_api_name CONSTANT varchar2(30) := 'insert_row_tl';
1552 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1553 l_ecctl_rec okl_ecctl_rec := p_ecctl_rec;
1554
1555 CURSOR get_languages IS
1556 SELECT *
1557 FROM fnd_languages
1558 WHERE installed_flag IN('I', 'B');
1559
1560 FUNCTION set_attributes(p_ecctl_rec IN okl_ecctl_rec
1561 ,x_ecctl_rec OUT NOCOPY okl_ecctl_rec) RETURN varchar2 IS
1562 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1563
1564 BEGIN
1565 x_ecctl_rec := p_ecctl_rec;
1566 x_ecctl_rec.language := userenv('LANG');
1567 x_ecctl_rec.source_lang := userenv('LANG');
1568 RETURN(l_return_status);
1569 END set_attributes;
1570
1571 BEGIN
1572 l_return_status := okl_api.start_activity(l_api_name
1573 ,g_pkg_name
1574 ,p_init_msg_list
1575 ,l_api_version
1576 ,p_api_version
1577 ,'_PVT'
1578 ,x_return_status);
1579
1580 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1581 RAISE okl_api.g_exception_unexpected_error;
1582 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1583 RAISE okl_api.g_exception_error;
1584 END IF;
1585
1586 --Setting Item Attributes
1587
1588 l_return_status := set_attributes(p_ecctl_rec, l_ecctl_rec);
1589
1590 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1591 RAISE okl_api.g_exception_unexpected_error;
1592 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1593 RAISE okl_api.g_exception_error;
1594 END IF;
1595
1596 FOR l_lang_rec IN get_languages LOOP
1597 l_ecctl_rec.language := l_lang_rec.language_code;
1598
1599 IF l_lang_rec.language_code = userenv('LANG') THEN
1600 l_ecctl_rec.sfwt_flag := 'N';
1601 ELSE
1602 l_ecctl_rec.sfwt_flag := 'Y';
1603 END IF;
1604
1605 INSERT INTO okl_fe_crit_cat_def_tl
1606 (crit_cat_def_id
1607 ,language
1608 ,source_lang
1609 ,sfwt_flag
1610 ,crit_cat_desc
1611 ,created_by
1612 ,creation_date
1613 ,last_updated_by
1614 ,last_update_date
1615 ,last_update_login)
1616 VALUES (l_ecctl_rec.crit_cat_def_id
1617 ,l_ecctl_rec.language
1618 ,l_ecctl_rec.source_lang
1619 ,l_ecctl_rec.sfwt_flag
1620 ,l_ecctl_rec.crit_cat_desc
1621 ,l_ecctl_rec.created_by
1622 ,l_ecctl_rec.creation_date
1623 ,l_ecctl_rec.last_updated_by
1624 ,l_ecctl_rec.last_update_date
1625 ,l_ecctl_rec.last_update_login);
1626
1627 END LOOP;
1628
1629 --Set OUT Values
1630
1631 x_ecctl_rec := l_ecctl_rec;
1632 x_return_status := l_return_status;
1633 okl_api.end_activity(x_msg_count, x_msg_data);
1634 EXCEPTION
1635 WHEN g_exception_halt_validation THEN
1636
1637 -- No action necessary. Validation can continue to next attribute/column
1638
1639 NULL;
1640 WHEN okl_api.g_exception_error THEN
1641 x_return_status := okl_api.handle_exceptions(l_api_name
1642 ,g_pkg_name
1643 ,'OKL_API.G_RET_STS_ERROR'
1644 ,x_msg_count
1645 ,x_msg_data
1646 ,'_PVT');
1647 WHEN okl_api.g_exception_unexpected_error THEN
1648 x_return_status := okl_api.handle_exceptions(l_api_name
1649 ,g_pkg_name
1650 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1651 ,x_msg_count
1652 ,x_msg_data
1653 ,'_PVT');
1654 WHEN OTHERS THEN
1655 x_return_status := okl_api.handle_exceptions(l_api_name
1656 ,g_pkg_name
1657 ,'OTHERS'
1658 ,x_msg_count
1659 ,x_msg_data
1660 ,'_PVT');
1661 END insert_row;
1662
1663 --------------------------------------------------------------------------------
1664 -- Procedure insert_row_v
1665 --------------------------------------------------------------------------------
1666
1667 PROCEDURE insert_row(p_api_version IN number
1668 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1669 ,x_return_status OUT NOCOPY varchar2
1670 ,x_msg_count OUT NOCOPY number
1671 ,x_msg_data OUT NOCOPY varchar2
1672 ,p_eccv_rec IN okl_eccv_rec
1673 ,x_eccv_rec OUT NOCOPY okl_eccv_rec) IS
1674 l_api_version CONSTANT number := 1;
1675 l_api_name CONSTANT varchar2(30) := 'v_insert_row';
1676 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1677 l_eccv_rec okl_eccv_rec;
1678 l_def_eccv_rec okl_eccv_rec;
1679 l_eccb_rec okl_eccb_rec;
1680 lx_eccb_rec okl_eccb_rec;
1681 l_ecctl_rec okl_ecctl_rec;
1682 lx_ecctl_rec okl_ecctl_rec;
1683
1684 FUNCTION fill_who_columns(p_eccv_rec IN okl_eccv_rec) RETURN okl_eccv_rec IS
1685 l_eccv_rec okl_eccv_rec := p_eccv_rec;
1686
1687 BEGIN
1688 l_eccv_rec.creation_date := sysdate;
1689 l_eccv_rec.created_by := fnd_global.user_id;
1690 l_eccv_rec.last_update_date := sysdate;
1691 l_eccv_rec.last_updated_by := fnd_global.user_id;
1692 l_eccv_rec.last_update_login := fnd_global.login_id;
1693 RETURN(l_eccv_rec);
1694 END fill_who_columns;
1695
1696 FUNCTION set_attributes(p_eccv_rec IN okl_eccv_rec
1697 ,x_eccv_rec OUT NOCOPY okl_eccv_rec) RETURN varchar2 IS
1698 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1699
1700 BEGIN
1701 x_eccv_rec := p_eccv_rec;
1702 x_eccv_rec.crit_cat_name := upper(p_eccv_rec.crit_cat_name);
1703 x_eccv_rec.object_version_number := 1;
1704 x_eccv_rec.sfwt_flag := 'N';
1705 RETURN(l_return_status);
1706 END set_attributes;
1707
1708 BEGIN
1709 l_return_status := okl_api.start_activity(l_api_name
1710 ,g_pkg_name
1711 ,p_init_msg_list
1712 ,l_api_version
1713 ,p_api_version
1714 ,'_PVT'
1715 ,x_return_status);
1716
1717 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1718 RAISE okl_api.g_exception_unexpected_error;
1719 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1720 RAISE okl_api.g_exception_error;
1721 END IF;
1722
1723 --null out defaults
1724
1725 l_eccv_rec := null_out_defaults(p_eccv_rec);
1726
1727 -- Set Primary key value
1728
1729 l_eccv_rec.crit_cat_def_id := get_seq_id; --Setting Item Attributes
1730 l_return_status := set_attributes(l_eccv_rec, l_def_eccv_rec);
1731
1732 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1733 RAISE okl_api.g_exception_unexpected_error;
1734 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1735 RAISE okl_api.g_exception_error;
1736 END IF;
1737
1738 -- fill who columns
1739
1740 l_def_eccv_rec := fill_who_columns(l_def_eccv_rec);
1741
1742 --validate attributes
1743
1744 l_return_status := validate_attributes(l_def_eccv_rec);
1745
1746 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1747 RAISE okl_api.g_exception_unexpected_error;
1748 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1749 RAISE okl_api.g_exception_error;
1750 END IF;
1751
1752 --validate record
1753
1754 l_return_status := validate_record(l_def_eccv_rec);
1755
1756 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1757 RAISE okl_api.g_exception_unexpected_error;
1758 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1759 RAISE okl_api.g_exception_error;
1760 END IF;
1761
1762 --migrate v to b
1763
1764 migrate(l_def_eccv_rec, l_eccb_rec);
1765
1766 --migrate v to tl
1767
1768 migrate(l_def_eccv_rec, l_ecctl_rec);
1769
1770 --call b insert_row
1771
1772 insert_row(p_api_version
1773 ,p_init_msg_list
1774 ,l_return_status
1775 ,x_msg_count
1776 ,x_msg_data
1777 ,l_eccb_rec
1778 ,lx_eccb_rec);
1779
1780 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1781 RAISE okl_api.g_exception_unexpected_error;
1782 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1783 RAISE okl_api.g_exception_error;
1784 END IF;
1785
1786 --migrate back b to v
1787
1788 migrate(lx_eccb_rec, l_def_eccv_rec);
1789
1790 --call tl insert row
1791
1792 insert_row(p_api_version
1793 ,p_init_msg_list
1794 ,l_return_status
1795 ,x_msg_count
1796 ,x_msg_data
1797 ,l_ecctl_rec
1798 ,lx_ecctl_rec);
1799
1800 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1801 RAISE okl_api.g_exception_unexpected_error;
1802 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1803 RAISE okl_api.g_exception_error;
1804 END IF;
1805
1806 --migrate back tl to v
1807
1808 migrate(lx_ecctl_rec, l_def_eccv_rec);
1809
1810 --Set OUT Values
1811
1812 x_eccv_rec := l_def_eccv_rec;
1813 x_return_status := l_return_status;
1814 okl_api.end_activity(x_msg_count, x_msg_data);
1815 EXCEPTION
1816 WHEN g_exception_halt_validation THEN
1817
1818 -- No action necessary. Validation can continue to next attribute/column
1819
1820 NULL;
1821 WHEN okl_api.g_exception_error THEN
1822 x_return_status := okl_api.handle_exceptions(l_api_name
1823 ,g_pkg_name
1824 ,'OKL_API.G_RET_STS_ERROR'
1825 ,x_msg_count
1826 ,x_msg_data
1827 ,'_PVT');
1828 WHEN okl_api.g_exception_unexpected_error THEN
1829 x_return_status := okl_api.handle_exceptions(l_api_name
1830 ,g_pkg_name
1831 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1832 ,x_msg_count
1833 ,x_msg_data
1834 ,'_PVT');
1835 WHEN OTHERS THEN
1836 x_return_status := okl_api.handle_exceptions(l_api_name
1837 ,g_pkg_name
1838 ,'OTHERS'
1839 ,x_msg_count
1840 ,x_msg_data
1841 ,'_PVT');
1842 END insert_row;
1843
1844 --------------------------------------------------------------------------------
1845 -- Procedure insert_row_tbl
1846 --------------------------------------------------------------------------------
1847
1848 PROCEDURE insert_row(p_api_version IN number
1849 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1850 ,x_return_status OUT NOCOPY varchar2
1851 ,x_msg_count OUT NOCOPY number
1852 ,x_msg_data OUT NOCOPY varchar2
1853 ,p_eccv_tbl IN okl_eccv_tbl
1854 ,x_eccv_tbl OUT NOCOPY okl_eccv_tbl) IS
1855 l_api_version CONSTANT number := 1;
1856 l_api_name CONSTANT varchar2(30) := 'tbl_insert_row';
1857 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1858 i number := 0;
1859 l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
1860
1861 BEGIN
1862 okl_api.init_msg_list(p_init_msg_list);
1863
1864 -- Make sure PL/SQL table has records in it before passing
1865
1866 IF (p_eccv_tbl.COUNT > 0) THEN
1867 i := p_eccv_tbl.FIRST;
1868
1869 LOOP
1870 insert_row(p_api_version => p_api_version
1871 ,p_init_msg_list => okl_api.g_false
1872 ,x_return_status => x_return_status
1873 ,x_msg_count => x_msg_count
1874 ,x_msg_data => x_msg_data
1875 ,p_eccv_rec => p_eccv_tbl(i)
1876 ,x_eccv_rec => x_eccv_tbl(i));
1877 IF x_return_status <> okl_api.g_ret_sts_success THEN
1878 IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
1879 l_overall_status := x_return_status;
1880 END IF;
1881 END IF;
1882 EXIT WHEN(i = p_eccv_tbl.LAST);
1883 i := p_eccv_tbl.next(i);
1884 END LOOP;
1885 x_return_status := l_overall_status;
1886 END IF;
1887
1888 EXCEPTION
1889 WHEN g_exception_halt_validation THEN
1890
1891 -- No action necessary. Validation can continue to next attribute/column
1892
1893 NULL;
1894 WHEN okl_api.g_exception_error THEN
1895 x_return_status := okl_api.handle_exceptions(l_api_name
1896 ,g_pkg_name
1897 ,'OKL_API.G_RET_STS_ERROR'
1898 ,x_msg_count
1899 ,x_msg_data
1900 ,'_PVT');
1901 WHEN okl_api.g_exception_unexpected_error THEN
1902 x_return_status := okl_api.handle_exceptions(l_api_name
1903 ,g_pkg_name
1904 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
1905 ,x_msg_count
1906 ,x_msg_data
1907 ,'_PVT');
1908 WHEN OTHERS THEN
1909 x_return_status := okl_api.handle_exceptions(l_api_name
1910 ,g_pkg_name
1911 ,'OTHERS'
1912 ,x_msg_count
1913 ,x_msg_data
1914 ,'_PVT');
1915 END insert_row;
1916
1917 --------------------------------------------------------------------------------
1918 -- Procedure update_row_b
1919 --------------------------------------------------------------------------------
1920
1921 PROCEDURE update_row(p_api_version IN number
1922 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1923 ,x_return_status OUT NOCOPY varchar2
1924 ,x_msg_count OUT NOCOPY number
1925 ,x_msg_data OUT NOCOPY varchar2
1926 ,p_eccb_rec IN okl_eccb_rec
1927 ,x_eccb_rec OUT NOCOPY okl_eccb_rec) IS
1928 l_api_version CONSTANT number := 1;
1929 l_api_name CONSTANT varchar2(30) := 'update_row_b';
1930 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1931 l_eccb_rec okl_eccb_rec := p_eccb_rec;
1932 l_def_eccb_rec okl_eccb_rec;
1933 l_row_notfound boolean := true;
1934
1935 FUNCTION set_attributes(p_eccb_rec IN okl_eccb_rec
1936 ,x_eccb_rec OUT NOCOPY okl_eccb_rec) RETURN varchar2 IS
1937 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
1938
1939 BEGIN
1940 x_eccb_rec := p_eccb_rec;
1941 RETURN(l_return_status);
1942 END set_attributes;
1943
1944 BEGIN
1945 l_return_status := okl_api.start_activity(l_api_name
1946 ,g_pkg_name
1947 ,p_init_msg_list
1948 ,l_api_version
1949 ,p_api_version
1950 ,'_PVT'
1951 ,x_return_status);
1952
1953 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1954 RAISE okl_api.g_exception_unexpected_error;
1955 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1956 RAISE okl_api.g_exception_error;
1957 END IF;
1958
1959 --Setting Item Attributes
1960
1961 l_return_status := set_attributes(p_eccb_rec, l_eccb_rec);
1962
1963 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1964 RAISE okl_api.g_exception_unexpected_error;
1965 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1966 RAISE okl_api.g_exception_error;
1967 END IF;
1968
1969 UPDATE okl_fe_crit_cat_def_b
1970 SET crit_cat_def_id = l_eccb_rec.crit_cat_def_id
1971 ,object_version_number = l_eccb_rec.object_version_number + 1
1972 ,crit_cat_name = l_eccb_rec.crit_cat_name
1973 ,ecc_ac_flag = l_eccb_rec.ecc_ac_flag
1974 ,orig_crit_cat_def_id = l_eccb_rec.orig_crit_cat_def_id
1975 ,value_type_code = l_eccb_rec.value_type_code
1976 ,data_type_code = l_eccb_rec.data_type_code
1977 ,enabled_yn = l_eccb_rec.enabled_yn
1978 ,seeded_yn = l_eccb_rec.seeded_yn
1979 ,function_id = l_eccb_rec.function_id
1980 ,source_yn = l_eccb_rec.source_yn
1981 ,sql_statement = l_eccb_rec.sql_statement
1982 ,created_by = l_eccb_rec.created_by
1983 ,creation_date = l_eccb_rec.creation_date
1984 ,last_updated_by = l_eccb_rec.last_updated_by
1985 ,last_update_date = l_eccb_rec.last_update_date
1986 ,last_update_login = l_eccb_rec.last_update_login
1987 WHERE crit_cat_def_id = l_eccb_rec.crit_cat_def_id;
1988
1989 --Set OUT Values
1990
1991 x_eccb_rec := l_eccb_rec;
1992 okl_api.end_activity(x_msg_count, x_msg_data);
1993 x_return_status := l_return_status;
1994 EXCEPTION
1995 WHEN g_exception_halt_validation THEN
1996
1997 -- No action necessary. Validation can continue to next attribute/column
1998
1999 NULL;
2000 WHEN okl_api.g_exception_error THEN
2001 x_return_status := okl_api.handle_exceptions(l_api_name
2002 ,g_pkg_name
2003 ,'OKL_API.G_RET_STS_ERROR'
2004 ,x_msg_count
2005 ,x_msg_data
2006 ,'_PVT');
2007 WHEN okl_api.g_exception_unexpected_error THEN
2008 x_return_status := okl_api.handle_exceptions(l_api_name
2009 ,g_pkg_name
2010 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2011 ,x_msg_count
2012 ,x_msg_data
2013 ,'_PVT');
2014 WHEN OTHERS THEN
2015 x_return_status := okl_api.handle_exceptions(l_api_name
2016 ,g_pkg_name
2017 ,'OTHERS'
2018 ,x_msg_count
2019 ,x_msg_data
2020 ,'_PVT');
2021 END update_row;
2022
2023 --------------------------------------------------------------------------------
2024 -- Procedure update_row_tl
2025 --------------------------------------------------------------------------------
2026
2027 PROCEDURE update_row(p_api_version IN number
2028 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
2029 ,x_return_status OUT NOCOPY varchar2
2030 ,x_msg_count OUT NOCOPY number
2031 ,x_msg_data OUT NOCOPY varchar2
2032 ,p_ecctl_rec IN okl_ecctl_rec
2033 ,x_ecctl_rec OUT NOCOPY okl_ecctl_rec) IS
2034 l_api_version CONSTANT number := 1;
2035 l_api_name CONSTANT varchar2(30) := 'update_row_tl';
2036 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
2037 l_ecctl_rec okl_ecctl_rec := p_ecctl_rec;
2038 l_def_ecctl_rec okl_ecctl_rec;
2039 l_row_notfound boolean := true;
2040
2041 FUNCTION set_attributes(p_ecctl_rec IN okl_ecctl_rec
2042 ,x_ecctl_rec OUT NOCOPY okl_ecctl_rec) RETURN varchar2 IS
2043 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
2044
2045 BEGIN
2046 x_ecctl_rec := p_ecctl_rec;
2047 x_ecctl_rec.language := userenv('LANG');
2048 x_ecctl_rec.source_lang := userenv('LANG');
2049 RETURN(l_return_status);
2050 END set_attributes;
2051
2052 BEGIN
2053 l_return_status := okl_api.start_activity(l_api_name
2054 ,g_pkg_name
2055 ,p_init_msg_list
2056 ,l_api_version
2057 ,p_api_version
2058 ,'_PVT'
2059 ,x_return_status);
2060
2061 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2062 RAISE okl_api.g_exception_unexpected_error;
2063 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2064 RAISE okl_api.g_exception_error;
2065 END IF;
2066
2067 --Setting Item Attributes
2068
2069 l_return_status := set_attributes(p_ecctl_rec, l_ecctl_rec);
2070
2071 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2072 RAISE okl_api.g_exception_unexpected_error;
2073 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2074 RAISE okl_api.g_exception_error;
2075 END IF;
2076
2077 UPDATE okl_fe_crit_cat_def_tl
2078 SET crit_cat_def_id = l_ecctl_rec.crit_cat_def_id
2079 ,source_lang = l_ecctl_rec.source_lang
2080 ,crit_cat_desc = l_ecctl_rec.crit_cat_desc
2081 ,created_by = l_ecctl_rec.created_by
2082 ,creation_date = l_ecctl_rec.creation_date
2083 ,last_updated_by = l_ecctl_rec.last_updated_by
2084 ,last_update_date = l_ecctl_rec.last_update_date
2085 ,last_update_login = l_ecctl_rec.last_update_login
2086 WHERE crit_cat_def_id = l_ecctl_rec.crit_cat_def_id;
2087
2088 UPDATE okl_fe_crit_cat_def_tl
2089 SET sfwt_flag = 'Y'
2090 WHERE crit_cat_def_id = l_ecctl_rec.crit_cat_def_id
2091 AND source_lang <> userenv('LANG');
2092
2093 --Set OUT Values
2094
2095 x_ecctl_rec := l_ecctl_rec;
2096 x_return_status := l_return_status;
2097 okl_api.end_activity(x_msg_count, x_msg_data);
2098 EXCEPTION
2099 WHEN g_exception_halt_validation THEN
2100
2101 -- No action necessary. Validation can continue to next attribute/column
2102
2103 NULL;
2104 WHEN okl_api.g_exception_error THEN
2105 x_return_status := okl_api.handle_exceptions(l_api_name
2106 ,g_pkg_name
2107 ,'OKL_API.G_RET_STS_ERROR'
2108 ,x_msg_count
2109 ,x_msg_data
2110 ,'_PVT');
2111 WHEN okl_api.g_exception_unexpected_error THEN
2112 x_return_status := okl_api.handle_exceptions(l_api_name
2113 ,g_pkg_name
2114 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2115 ,x_msg_count
2116 ,x_msg_data
2117 ,'_PVT');
2118 WHEN OTHERS THEN
2119 x_return_status := okl_api.handle_exceptions(l_api_name
2120 ,g_pkg_name
2121 ,'OTHERS'
2122 ,x_msg_count
2123 ,x_msg_data
2124 ,'_PVT');
2125 END update_row;
2126
2127 --------------------------------------------------------------------------------
2128 -- Procedure update_row_v
2129 --------------------------------------------------------------------------------
2130
2131 PROCEDURE update_row(p_api_version IN number
2132 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
2133 ,x_return_status OUT NOCOPY varchar2
2134 ,x_msg_count OUT NOCOPY number
2135 ,x_msg_data OUT NOCOPY varchar2
2136 ,p_eccv_rec IN okl_eccv_rec
2137 ,x_eccv_rec OUT NOCOPY okl_eccv_rec) IS
2138 l_api_version CONSTANT number := 1;
2139 l_api_name CONSTANT varchar2(30) := 'v_update_row';
2140 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
2141 l_eccv_rec okl_eccv_rec := p_eccv_rec;
2142 l_def_eccv_rec okl_eccv_rec;
2143 lx_def_eccv_rec okl_eccv_rec;
2144 l_eccb_rec okl_eccb_rec;
2145 lx_eccb_rec okl_eccb_rec;
2146 l_ecctl_rec okl_ecctl_rec;
2147 lx_ecctl_rec okl_ecctl_rec;
2148
2149 FUNCTION fill_who_columns(p_eccv_rec IN okl_eccv_rec) RETURN okl_eccv_rec IS
2150 l_eccv_rec okl_eccv_rec := p_eccv_rec;
2151
2152 BEGIN
2153 l_eccv_rec.last_update_date := sysdate;
2154 l_eccv_rec.last_updated_by := fnd_global.user_id;
2155 l_eccv_rec.last_update_login := fnd_global.login_id;
2156 RETURN(l_eccv_rec);
2157 END fill_who_columns;
2158
2159 FUNCTION populate_new_record(p_eccv_rec IN okl_eccv_rec
2160 ,x_eccv_rec OUT NOCOPY okl_eccv_rec) RETURN varchar2 IS
2161 l_eccv_rec okl_eccv_rec;
2162 l_row_notfound boolean := true;
2163 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
2164
2165 BEGIN
2166 x_eccv_rec := p_eccv_rec;
2167
2168 --Get current database values
2169
2170 l_eccv_rec := get_rec(p_eccv_rec, l_row_notfound);
2171
2172 IF (l_row_notfound) THEN
2173 l_return_status := okl_api.g_ret_sts_unexp_error;
2174 END IF;
2175
2176 --dont default object_version_number, last_updated_by, last_updat_date and last_update_login
2177
2178 IF (x_eccv_rec.crit_cat_def_id IS NULL) THEN
2179 x_eccv_rec.crit_cat_def_id := l_eccv_rec.crit_cat_def_id;
2180 END IF;
2181
2182 IF (x_eccv_rec.ecc_ac_flag IS NULL) THEN
2183 x_eccv_rec.ecc_ac_flag := l_eccv_rec.ecc_ac_flag;
2184 END IF;
2185
2186 IF (x_eccv_rec.orig_crit_cat_def_id IS NULL) THEN
2187 x_eccv_rec.orig_crit_cat_def_id := l_eccv_rec.orig_crit_cat_def_id;
2188 END IF;
2189
2190 IF (x_eccv_rec.crit_cat_name IS NULL) THEN
2191 x_eccv_rec.crit_cat_name := l_eccv_rec.crit_cat_name;
2192 END IF;
2193
2194 IF (x_eccv_rec.crit_cat_desc IS NULL) THEN
2195 x_eccv_rec.crit_cat_desc := l_eccv_rec.crit_cat_desc;
2196 END IF;
2197
2198 IF (x_eccv_rec.sfwt_flag IS NULL) THEN
2199 x_eccv_rec.sfwt_flag := l_eccv_rec.sfwt_flag;
2200 END IF;
2201
2202 IF (x_eccv_rec.value_type_code IS NULL) THEN
2203 x_eccv_rec.value_type_code := l_eccv_rec.value_type_code;
2204 END IF;
2205
2206 IF (x_eccv_rec.data_type_code IS NULL) THEN
2207 x_eccv_rec.data_type_code := l_eccv_rec.data_type_code;
2208 END IF;
2209
2210 IF (x_eccv_rec.enabled_yn IS NULL) THEN
2211 x_eccv_rec.enabled_yn := l_eccv_rec.enabled_yn;
2212 END IF;
2213
2214 IF (x_eccv_rec.seeded_yn IS NULL) THEN
2215 x_eccv_rec.seeded_yn := l_eccv_rec.seeded_yn;
2216 END IF;
2217
2218 IF (x_eccv_rec.function_id IS NULL) THEN
2219 x_eccv_rec.function_id := l_eccv_rec.function_id;
2220 END IF;
2221
2222 IF (x_eccv_rec.source_yn IS NULL) THEN
2223 x_eccv_rec.source_yn := l_eccv_rec.source_yn;
2224 END IF;
2225
2226 IF (x_eccv_rec.sql_statement IS NULL) THEN
2227 x_eccv_rec.sql_statement := l_eccv_rec.sql_statement;
2228 END IF;
2229
2230 IF (x_eccv_rec.created_by IS NULL) THEN
2231 x_eccv_rec.created_by := l_eccv_rec.created_by;
2232 END IF;
2233
2234 IF (x_eccv_rec.creation_date IS NULL) THEN
2235 x_eccv_rec.creation_date := l_eccv_rec.creation_date;
2236 END IF;
2237 RETURN(l_return_status);
2238 END populate_new_record;
2239
2240 FUNCTION set_attributes(p_eccv_rec IN okl_eccv_rec
2241 ,x_eccv_rec OUT NOCOPY okl_eccv_rec) RETURN varchar2 IS
2242 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
2243
2244 BEGIN
2245 x_eccv_rec := p_eccv_rec;
2246 RETURN(l_return_status);
2247 END set_attributes;
2248
2249 BEGIN
2250 l_return_status := okl_api.start_activity(l_api_name
2251 ,g_pkg_name
2252 ,p_init_msg_list
2253 ,l_api_version
2254 ,p_api_version
2255 ,'_PVT'
2256 ,x_return_status);
2257
2258 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2259 RAISE okl_api.g_exception_unexpected_error;
2260 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2261 RAISE okl_api.g_exception_error;
2262 END IF;
2263
2264 --Setting Item Attributes
2265
2266 l_return_status := set_attributes(l_eccv_rec, lx_def_eccv_rec);
2267
2268 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2269 RAISE okl_api.g_exception_unexpected_error;
2270 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2271 RAISE okl_api.g_exception_error;
2272 END IF;
2273
2274 --default the unmodified values from the database
2275
2276 l_return_status := populate_new_record(lx_def_eccv_rec, l_def_eccv_rec);
2277
2278 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2279 RAISE okl_api.g_exception_unexpected_error;
2280 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2281 RAISE okl_api.g_exception_error;
2282 END IF;
2283
2284 --null out the G Miss values
2285
2286 l_def_eccv_rec := null_out_defaults(l_def_eccv_rec);
2287
2288 --fill who columns
2289
2290 l_def_eccv_rec := fill_who_columns(l_def_eccv_rec);
2291
2292 --validate attributes
2293
2294 l_return_status := validate_attributes(l_def_eccv_rec);
2295
2296 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2297 RAISE okl_api.g_exception_unexpected_error;
2298 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2299 RAISE okl_api.g_exception_error;
2300 END IF;
2301
2302 --validate record
2303
2304 l_return_status := validate_record(l_def_eccv_rec);
2305
2306 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2307 RAISE okl_api.g_exception_unexpected_error;
2308 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2309 RAISE okl_api.g_exception_error;
2310 END IF;
2311
2312 --lock the row
2313
2314 lock_row(p_api_version => l_api_version
2315 ,p_init_msg_list => okl_api.g_false
2316 ,x_return_status => l_return_status
2317 ,x_msg_count => x_msg_count
2318 ,x_msg_data => x_msg_data
2319 ,p_eccv_rec => l_def_eccv_rec);
2320
2321 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2322 RAISE okl_api.g_exception_unexpected_error;
2323 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2324 RAISE okl_api.g_exception_error;
2325 END IF;
2326
2327 --migrate v to b
2328
2329 migrate(l_def_eccv_rec, l_eccb_rec);
2330
2331 --migrate v to tl
2332
2333 migrate(l_def_eccv_rec, l_ecctl_rec);
2334
2335 --call b update_row
2336
2337 update_row(p_api_version
2338 ,p_init_msg_list
2339 ,l_return_status
2340 ,x_msg_count
2341 ,x_msg_data
2342 ,l_eccb_rec
2343 ,lx_eccb_rec);
2344
2345 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2346 RAISE okl_api.g_exception_unexpected_error;
2347 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2348 RAISE okl_api.g_exception_error;
2349 END IF; --migrate back b to v
2350 migrate(lx_eccb_rec, l_def_eccv_rec);
2351
2352 --call tl update row
2353
2354 update_row(p_api_version
2355 ,p_init_msg_list
2356 ,l_return_status
2357 ,x_msg_count
2358 ,x_msg_data
2359 ,l_ecctl_rec
2360 ,lx_ecctl_rec);
2361
2362 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2363 RAISE okl_api.g_exception_unexpected_error;
2364 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2365 RAISE okl_api.g_exception_error;
2366 END IF;
2367 migrate(lx_ecctl_rec, l_def_eccv_rec);
2368
2369 --Set OUT Values
2370
2371 x_eccv_rec := l_def_eccv_rec;
2372 okl_api.end_activity(x_msg_count, x_msg_data);
2373 x_return_status := l_return_status;
2374 EXCEPTION
2375 WHEN g_exception_halt_validation THEN
2376
2377 -- No action necessary. Validation can continue to next attribute/column
2378
2379 NULL;
2380 WHEN okl_api.g_exception_error THEN
2381 x_return_status := okl_api.handle_exceptions(l_api_name
2382 ,g_pkg_name
2383 ,'OKL_API.G_RET_STS_ERROR'
2384 ,x_msg_count
2385 ,x_msg_data
2386 ,'_PVT');
2387 WHEN okl_api.g_exception_unexpected_error THEN
2388 x_return_status := okl_api.handle_exceptions(l_api_name
2389 ,g_pkg_name
2390 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2391 ,x_msg_count
2392 ,x_msg_data
2393 ,'_PVT');
2394 WHEN OTHERS THEN
2395 x_return_status := okl_api.handle_exceptions(l_api_name
2396 ,g_pkg_name
2397 ,'OTHERS'
2398 ,x_msg_count
2399 ,x_msg_data
2400 ,'_PVT');
2401 END update_row;
2402
2403 --------------------------------------------------------------------------------
2404 -- Procedure update_row_tbl
2405 --------------------------------------------------------------------------------
2406
2407 PROCEDURE update_row(p_api_version IN number
2408 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
2409 ,x_return_status OUT NOCOPY varchar2
2410 ,x_msg_count OUT NOCOPY number
2411 ,x_msg_data OUT NOCOPY varchar2
2412 ,p_eccv_tbl IN okl_eccv_tbl
2413 ,x_eccv_tbl OUT NOCOPY okl_eccv_tbl) IS
2414 l_api_version CONSTANT number := 1;
2415 l_api_name CONSTANT varchar2(30) := 'tbl_update_row';
2416 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
2417 i number := 0;
2418 l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
2419
2420 BEGIN
2421 okl_api.init_msg_list(p_init_msg_list);
2422
2423 -- Make sure PL/SQL table has records in it before passing
2424
2425 IF (p_eccv_tbl.COUNT > 0) THEN
2426 i := p_eccv_tbl.FIRST;
2427
2428 LOOP
2429 update_row(p_api_version => p_api_version
2430 ,p_init_msg_list => okl_api.g_false
2431 ,x_return_status => x_return_status
2432 ,x_msg_count => x_msg_count
2433 ,x_msg_data => x_msg_data
2434 ,p_eccv_rec => p_eccv_tbl(i)
2435 ,x_eccv_rec => x_eccv_tbl(i));
2436 IF x_return_status <> okl_api.g_ret_sts_success THEN
2437 IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
2438 l_overall_status := x_return_status;
2439 END IF;
2440 END IF;
2441 EXIT WHEN(i = p_eccv_tbl.LAST);
2442 i := p_eccv_tbl.next(i);
2443 END LOOP;
2444 x_return_status := l_overall_status;
2445 END IF;
2446
2447 EXCEPTION
2448 WHEN g_exception_halt_validation THEN
2449
2450 -- No action necessary. Validation can continue to next attribute/column
2451
2452 NULL;
2453 WHEN okl_api.g_exception_error THEN
2454 x_return_status := okl_api.handle_exceptions(l_api_name
2455 ,g_pkg_name
2456 ,'OKL_API.G_RET_STS_ERROR'
2457 ,x_msg_count
2458 ,x_msg_data
2459 ,'_PVT');
2460 WHEN okl_api.g_exception_unexpected_error THEN
2461 x_return_status := okl_api.handle_exceptions(l_api_name
2462 ,g_pkg_name
2463 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2464 ,x_msg_count
2465 ,x_msg_data
2466 ,'_PVT');
2467 WHEN OTHERS THEN
2468 x_return_status := okl_api.handle_exceptions(l_api_name
2469 ,g_pkg_name
2470 ,'OTHERS'
2471 ,x_msg_count
2472 ,x_msg_data
2473 ,'_PVT');
2474 END update_row;
2475
2476 --------------------------------------------------------------------------------
2477 -- Procedure delete_row_b
2478 --------------------------------------------------------------------------------
2479
2480 PROCEDURE delete_row(p_api_version IN number
2481 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
2482 ,x_return_status OUT NOCOPY varchar2
2483 ,x_msg_count OUT NOCOPY number
2484 ,x_msg_data OUT NOCOPY varchar2
2485 ,p_eccb_rec IN okl_eccb_rec) IS
2486 l_api_version CONSTANT number := 1;
2487 l_api_name CONSTANT varchar2(30) := 'delete_row_b';
2488 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
2489 l_eccb_rec okl_eccb_rec := p_eccb_rec;
2490 l_row_notfound boolean := true;
2491
2492 BEGIN
2493 l_return_status := okl_api.start_activity(l_api_name
2494 ,g_pkg_name
2495 ,p_init_msg_list
2496 ,l_api_version
2497 ,p_api_version
2498 ,'_PVT'
2499 ,x_return_status);
2500
2501 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2502 RAISE okl_api.g_exception_unexpected_error;
2503 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2504 RAISE okl_api.g_exception_error;
2505 END IF;
2506
2507 DELETE FROM okl_fe_crit_cat_def_b
2508 WHERE crit_cat_def_id = l_eccb_rec.crit_cat_def_id;
2509 x_return_status := l_return_status;
2510 okl_api.end_activity(x_msg_count, x_msg_data);
2511 EXCEPTION
2512 WHEN g_exception_halt_validation THEN
2513
2514 -- No action necessary. Validation can continue to next attribute/column
2515
2516 NULL;
2517 WHEN okl_api.g_exception_error THEN
2518 x_return_status := okl_api.handle_exceptions(l_api_name
2519 ,g_pkg_name
2520 ,'OKL_API.G_RET_STS_ERROR'
2521 ,x_msg_count
2522 ,x_msg_data
2523 ,'_PVT');
2524 WHEN okl_api.g_exception_unexpected_error THEN
2525 x_return_status := okl_api.handle_exceptions(l_api_name
2526 ,g_pkg_name
2527 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2528 ,x_msg_count
2529 ,x_msg_data
2530 ,'_PVT');
2531 WHEN OTHERS THEN
2532 x_return_status := okl_api.handle_exceptions(l_api_name
2533 ,g_pkg_name
2534 ,'OTHERS'
2535 ,x_msg_count
2536 ,x_msg_data
2537 ,'_PVT');
2538 END delete_row;
2539
2540 --------------------------------------------------------------------------------
2541 -- Procedure delete_row_tl
2542 --------------------------------------------------------------------------------
2543
2544 PROCEDURE delete_row(p_api_version IN number
2545 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
2546 ,x_return_status OUT NOCOPY varchar2
2547 ,x_msg_count OUT NOCOPY number
2548 ,x_msg_data OUT NOCOPY varchar2
2549 ,p_ecctl_rec IN okl_ecctl_rec) IS
2550 l_api_version CONSTANT number := 1;
2551 l_api_name CONSTANT varchar2(30) := 'delete_row_tl';
2552 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
2553 l_ecctl_rec okl_ecctl_rec := p_ecctl_rec;
2554 l_row_notfound boolean := true;
2555
2556 FUNCTION set_attributes(p_ecctl_rec IN okl_ecctl_rec
2557 ,x_ecctl_rec OUT NOCOPY okl_ecctl_rec) RETURN varchar2 IS
2558 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
2559
2560 BEGIN
2561 x_ecctl_rec := p_ecctl_rec;
2562 x_ecctl_rec.language := userenv('LANG');
2563 x_ecctl_rec.source_lang := userenv('LANG');
2564 RETURN(l_return_status);
2565 END set_attributes;
2566
2567 BEGIN
2568 l_return_status := okl_api.start_activity(l_api_name
2569 ,g_pkg_name
2570 ,p_init_msg_list
2571 ,l_api_version
2572 ,p_api_version
2573 ,'_PVT'
2574 ,x_return_status);
2575
2576 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2577 RAISE okl_api.g_exception_unexpected_error;
2578 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2579 RAISE okl_api.g_exception_error;
2580 END IF;
2581
2582 --Setting Item Attributes
2583
2584 l_return_status := set_attributes(p_ecctl_rec, l_ecctl_rec);
2585
2586 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2587 RAISE okl_api.g_exception_unexpected_error;
2588 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2589 RAISE okl_api.g_exception_error;
2590 END IF;
2591
2592 DELETE FROM okl_fe_crit_cat_def_tl
2593 WHERE crit_cat_def_id = l_ecctl_rec.crit_cat_def_id;
2594 x_return_status := l_return_status;
2595 okl_api.end_activity(x_msg_count, x_msg_data);
2596 EXCEPTION
2597 WHEN g_exception_halt_validation THEN
2598
2599 -- No action necessary. Validation can continue to next attribute/column
2600
2601 NULL;
2602 WHEN okl_api.g_exception_error THEN
2603 x_return_status := okl_api.handle_exceptions(l_api_name
2604 ,g_pkg_name
2605 ,'OKL_API.G_RET_STS_ERROR'
2606 ,x_msg_count
2607 ,x_msg_data
2608 ,'_PVT');
2609 WHEN okl_api.g_exception_unexpected_error THEN
2610 x_return_status := okl_api.handle_exceptions(l_api_name
2611 ,g_pkg_name
2612 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2613 ,x_msg_count
2614 ,x_msg_data
2615 ,'_PVT');
2616 WHEN OTHERS THEN
2617 x_return_status := okl_api.handle_exceptions(l_api_name
2618 ,g_pkg_name
2619 ,'OTHERS'
2620 ,x_msg_count
2621 ,x_msg_data
2622 ,'_PVT');
2623 END delete_row;
2624
2625 --------------------------------------------------------------------------------
2626 -- Procedure delete_row_v
2627 --------------------------------------------------------------------------------
2628
2629 PROCEDURE delete_row(p_api_version IN number
2630 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
2631 ,x_return_status OUT NOCOPY varchar2
2632 ,x_msg_count OUT NOCOPY number
2633 ,x_msg_data OUT NOCOPY varchar2
2634 ,p_eccv_rec IN okl_eccv_rec) IS
2635 l_api_version CONSTANT number := 1;
2636 l_api_name CONSTANT varchar2(30) := 'v_delete_row';
2637 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
2638 l_eccv_rec okl_eccv_rec := p_eccv_rec;
2639 l_eccb_rec okl_eccb_rec;
2640 l_ecctl_rec okl_ecctl_rec;
2641
2642 BEGIN
2643 l_return_status := okl_api.start_activity(l_api_name
2644 ,g_pkg_name
2645 ,p_init_msg_list
2646 ,l_api_version
2647 ,p_api_version
2648 ,'_PVT'
2649 ,x_return_status);
2650
2651 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2652 RAISE okl_api.g_exception_unexpected_error;
2653 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2654 RAISE okl_api.g_exception_error;
2655 END IF;
2656 migrate(l_eccv_rec, l_eccb_rec);
2657 migrate(l_eccv_rec, l_ecctl_rec);
2658 delete_row(p_api_version
2659 ,p_init_msg_list
2660 ,l_return_status
2661 ,x_msg_count
2662 ,x_msg_data
2663 ,l_eccb_rec);
2664
2665 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2666 RAISE okl_api.g_exception_unexpected_error;
2667 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2668 RAISE okl_api.g_exception_error;
2669 END IF;
2670 delete_row(p_api_version
2671 ,p_init_msg_list
2672 ,l_return_status
2673 ,x_msg_count
2674 ,x_msg_data
2675 ,l_ecctl_rec);
2676
2677 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2678 RAISE okl_api.g_exception_unexpected_error;
2679 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2680 RAISE okl_api.g_exception_error;
2681 END IF;
2682 x_return_status := l_return_status;
2683 okl_api.end_activity(x_msg_count, x_msg_data);
2684 EXCEPTION
2685 WHEN g_exception_halt_validation THEN
2686
2687 -- No action necessary. Validation can continue to next attribute/column
2688
2689 NULL;
2690 WHEN okl_api.g_exception_error THEN
2691 x_return_status := okl_api.handle_exceptions(l_api_name
2692 ,g_pkg_name
2693 ,'OKL_API.G_RET_STS_ERROR'
2694 ,x_msg_count
2695 ,x_msg_data
2696 ,'_PVT');
2697 WHEN okl_api.g_exception_unexpected_error THEN
2698 x_return_status := okl_api.handle_exceptions(l_api_name
2699 ,g_pkg_name
2700 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2701 ,x_msg_count
2702 ,x_msg_data
2703 ,'_PVT');
2704 WHEN OTHERS THEN
2705 x_return_status := okl_api.handle_exceptions(l_api_name
2706 ,g_pkg_name
2707 ,'OTHERS'
2708 ,x_msg_count
2709 ,x_msg_data
2710 ,'_PVT');
2711 END delete_row;
2712
2713 --------------------------------------------------------------------------------
2714 -- Procedure delete_row_tbl
2715 --------------------------------------------------------------------------------
2716
2717 PROCEDURE delete_row(p_api_version IN number
2718 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
2719 ,x_return_status OUT NOCOPY varchar2
2720 ,x_msg_count OUT NOCOPY number
2721 ,x_msg_data OUT NOCOPY varchar2
2722 ,p_eccv_tbl IN okl_eccv_tbl) IS
2723 l_api_version CONSTANT number := 1;
2724 l_api_name CONSTANT varchar2(30) := 'tbl_delete_row';
2725 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
2726 i number := 0;
2727 l_overall_status varchar2(1) := okl_api.g_ret_sts_success;
2728
2729 BEGIN
2730 okl_api.init_msg_list(p_init_msg_list);
2731
2732 -- Make sure PL/SQL table has records in it before passing
2733
2734 IF (p_eccv_tbl.COUNT > 0) THEN
2735 i := p_eccv_tbl.FIRST;
2736
2737 LOOP
2738 delete_row(p_api_version => p_api_version
2739 ,p_init_msg_list => okl_api.g_false
2740 ,x_return_status => x_return_status
2741 ,x_msg_count => x_msg_count
2742 ,x_msg_data => x_msg_data
2743 ,p_eccv_rec => p_eccv_tbl(i));
2744 IF x_return_status <> okl_api.g_ret_sts_success THEN
2745 IF l_overall_status <> okl_api.g_ret_sts_unexp_error THEN
2746 l_overall_status := x_return_status;
2747 END IF;
2748 END IF;
2749 EXIT WHEN(i = p_eccv_tbl.LAST);
2750 i := p_eccv_tbl.next(i);
2751 END LOOP;
2752 x_return_status := l_overall_status;
2753 END IF;
2754
2755 EXCEPTION
2756 WHEN g_exception_halt_validation THEN
2757
2758 -- No action necessary. Validation can continue to next attribute/column
2759
2760 NULL;
2761 WHEN okl_api.g_exception_error THEN
2762 x_return_status := okl_api.handle_exceptions(l_api_name
2763 ,g_pkg_name
2764 ,'OKL_API.G_RET_STS_ERROR'
2765 ,x_msg_count
2766 ,x_msg_data
2767 ,'_PVT');
2768 WHEN okl_api.g_exception_unexpected_error THEN
2769 x_return_status := okl_api.handle_exceptions(l_api_name
2770 ,g_pkg_name
2771 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
2772 ,x_msg_count
2773 ,x_msg_data
2774 ,'_PVT');
2775 WHEN OTHERS THEN
2776 x_return_status := okl_api.handle_exceptions(l_api_name
2777 ,g_pkg_name
2778 ,'OTHERS'
2779 ,x_msg_count
2780 ,x_msg_data
2781 ,'_PVT');
2782 END delete_row;
2783
2784 -------------------------------------------------------------------------------
2785 -- Procedure TRANSLATE_ROW
2786 -------------------------------------------------------------------------------
2787
2788 PROCEDURE TRANSLATE_ROW(p_eccv_rec IN okl_eccv_rec,
2789 p_owner IN VARCHAR2,
2790 p_last_update_date IN VARCHAR2,
2791 x_return_status OUT NOCOPY VARCHAR2) IS
2792 f_luby NUMBER; -- entity owner in file
2793 f_ludate DATE; -- entity update date in file
2794 db_luby NUMBER; -- entity owner in db
2795 db_ludate DATE; -- entity update date in db
2796
2797 BEGIN
2798 -- Translate owner to file_last_updated_by
2799 f_luby := fnd_load_util.owner_id(p_owner);
2800
2801 -- Translate char last_update_date to date
2802 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
2803
2804 SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE
2805 INTO db_luby, db_ludate
2806 FROM OKL_FE_CRIT_CAT_DEF_TL
2807 where crit_cat_def_id = p_eccv_rec.crit_cat_def_id
2808 and USERENV('LANG') =language;
2809
2810 IF(fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
2811 db_ludate, '')) then
2812 UPDATE OKL_FE_CRIT_CAT_DEF_TL
2813 SET
2814 CRIT_CAT_DESC = p_eccv_rec.crit_cat_desc,
2815 LAST_UPDATE_DATE = f_ludate,
2816 LAST_UPDATED_BY = f_luby,
2817 LAST_UPDATE_LOGIN = 0,
2818 SOURCE_LANG = USERENV('LANG')
2819 WHERE CRIT_CAT_DEF_ID = to_number(p_eccv_rec.crit_cat_def_id)
2820 AND USERENV('LANG') IN (language,source_lang);
2821 END IF;
2822 END TRANSLATE_ROW;
2823
2824 -------------------------------------------------------------------------------
2825 -- Procedure LOAD_ROW
2826 -------------------------------------------------------------------------------
2827
2828 PROCEDURE LOAD_ROW(p_eccv_rec IN okl_eccv_rec,
2829 p_owner IN VARCHAR2,
2830 p_last_update_date IN VARCHAR2,
2831 x_return_status OUT NOCOPY VARCHAR2) IS
2832 id NUMBER;
2833 f_luby NUMBER; -- entity owner in file
2834 f_ludate DATE; -- entity update date in file
2835 db_luby NUMBER; -- entity owner in db
2836 db_ludate DATE; -- entity update date in db
2837 BEGIN
2838 -- Translate owner to file_last_updated_by
2839 f_luby := fnd_load_util.owner_id(p_owner);
2840
2841 -- Translate char last_update_date to date
2842 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
2843
2844 BEGIN
2845 SELECT ID , LAST_UPDATED_BY, LAST_UPDATE_DATE
2846 INTO id, db_luby, db_ludate
2847 FROM OKL_FE_CRIT_CAT_DEF_B
2848 where crit_cat_def_id = p_eccv_rec.crit_cat_def_id;
2849
2850 IF(fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
2851 db_ludate, '')) then
2852 --Update _b
2853 UPDATE okl_fe_crit_cat_def_b
2854 SET crit_cat_def_id = p_eccv_rec.crit_cat_def_id
2855 ,object_version_number = p_eccv_rec.object_version_number + 1
2856 ,crit_cat_name = p_eccv_rec.crit_cat_name
2857 ,ecc_ac_flag = p_eccv_rec.ecc_ac_flag
2858 ,orig_crit_cat_def_id = p_eccv_rec.orig_crit_cat_def_id
2859 ,value_type_code = p_eccv_rec.value_type_code
2860 ,data_type_code = p_eccv_rec.data_type_code
2861 ,enabled_yn = p_eccv_rec.enabled_yn
2862 ,seeded_yn = p_eccv_rec.seeded_yn
2863 ,function_id = p_eccv_rec.function_id
2864 ,source_yn = p_eccv_rec.source_yn
2865 ,sql_statement = p_eccv_rec.sql_statement
2866 ,last_updated_by = f_luby
2867 ,last_update_date = f_ludate
2868 ,last_update_login = 0
2869 WHERE crit_cat_def_id = p_eccv_rec.crit_cat_def_id;
2870 --Update _TL
2871 UPDATE OKL_FE_CRIT_CAT_DEF_TL
2872 SET
2873 CRIT_CAT_DESC = p_eccv_rec.crit_cat_desc,
2874 LAST_UPDATE_DATE = f_ludate,
2875 LAST_UPDATED_BY = f_luby,
2876 LAST_UPDATE_LOGIN = 0,
2877 SOURCE_LANG = USERENV('LANG')
2878 WHERE CRIT_CAT_DEF_ID = TO_NUMBER(p_eccv_rec.crit_cat_def_id)
2879 AND USERENV('LANG') IN (language,source_lang);
2880
2881 if (sql%notfound) then
2882
2883 INSERT INTO OKL_FE_CRIT_CAT_DEF_TL
2884 (CRIT_CAT_DEF_ID,
2885 LANGUAGE,
2886 SOURCE_LANG,
2887 SFWT_FLAG,
2888 CRIT_CAT_DESC,
2889 CREATED_BY,
2890 CREATION_DATE,
2891 LAST_UPDATED_BY,
2892 LAST_UPDATE_DATE,
2893 LAST_UPDATE_LOGIN
2894 )
2895 SELECT
2896 TO_NUMBER(p_eccv_rec.crit_cat_def_id),
2897 L.LANGUAGE_CODE,
2898 USERENV('LANG'),
2899 decode(L.LANGUAGE_CODE,userenv('LANG'),'N','Y'),
2900 p_eccv_rec.crit_cat_desc,
2901 f_luby,
2902 f_ludate,
2903 f_luby,
2904 f_ludate,
2905 0
2906 FROM FND_LANGUAGES L
2907 WHERE L.INSTALLED_FLAG IN ('I','B')
2908 AND NOT EXISTS
2909 (SELECT NULL
2910 FROM OKL_FE_CRIT_CAT_DEF_TL TL
2911 WHERE TL.CRIT_CAT_DEF_ID = TO_NUMBER(p_eccv_rec.crit_cat_def_id)
2912 AND TL.LANGUAGE = L.LANGUAGE_CODE );
2913 end if;
2914
2915 END IF;
2916
2917 END;
2918 EXCEPTION
2919 when no_data_found then
2920 INSERT INTO okl_fe_crit_cat_def_b
2921 (crit_cat_def_id
2922 ,object_version_number
2923 ,crit_cat_name
2924 ,ecc_ac_flag
2925 ,orig_crit_cat_def_id
2926 ,value_type_code
2927 ,data_type_code
2928 ,enabled_yn
2929 ,seeded_yn
2930 ,function_id
2931 ,source_yn
2932 ,sql_statement
2933 ,created_by
2934 ,creation_date
2935 ,last_updated_by
2936 ,last_update_date
2937 ,last_update_login)
2938 VALUES (p_eccv_rec.crit_cat_def_id
2939 ,p_eccv_rec.object_version_number
2940 ,p_eccv_rec.crit_cat_name
2941 ,p_eccv_rec.ecc_ac_flag
2942 ,p_eccv_rec.orig_crit_cat_def_id
2943 ,p_eccv_rec.value_type_code
2944 ,p_eccv_rec.data_type_code
2945 ,p_eccv_rec.enabled_yn
2946 ,p_eccv_rec.seeded_yn
2947 ,p_eccv_rec.function_id
2948 ,p_eccv_rec.source_yn
2949 ,p_eccv_rec.sql_statement
2950 ,f_luby
2951 ,f_ludate
2952 ,f_luby
2953 ,f_ludate
2954 ,0);
2955 --Insert Into TL
2956 INSERT INTO OKL_FE_CRIT_CAT_DEF_TL
2957 (CRIT_CAT_DEF_ID,
2958 LANGUAGE,
2959 SOURCE_LANG,
2960 SFWT_FLAG,
2961 CRIT_CAT_DESC,
2962 CREATED_BY,
2963 CREATION_DATE,
2964 LAST_UPDATED_BY,
2965 LAST_UPDATE_DATE,
2966 LAST_UPDATE_LOGIN
2967 )
2968 SELECT
2969 p_eccv_rec.crit_cat_def_id,
2970 L.LANGUAGE_CODE,
2971 userenv('LANG'),
2972 decode(L.LANGUAGE_CODE,userenv('LANG'),'N','Y'),
2973 p_eccv_rec.crit_cat_desc,
2974 f_luby,
2975 f_ludate,
2976 f_luby,
2977 f_ludate,
2978 0
2979 FROM FND_LANGUAGES L
2980 WHERE L.INSTALLED_FLAG IN ('I','B')
2981 AND NOT EXISTS
2982 (SELECT 1
2983 FROM OKL_FE_CRIT_CAT_DEF_TL TL
2984 WHERE TL.CRIT_CAT_DEF_ID = TO_NUMBER(p_eccv_rec.crit_cat_def_id)
2985 AND TL.LANGUAGE = L.LANGUAGE_CODE);
2986
2987 END LOAD_ROW;
2988
2989 -------------------------------------------------------------------------------
2990 -- Procedure LOAD_SEED_ROW
2991 -------------------------------------------------------------------------------
2992
2993 PROCEDURE LOAD_SEED_ROW(p_upload_mode IN VARCHAR2,
2994 p_crit_cat_def_id IN VARCHAR2,
2995 p_object_version_number IN VARCHAR2,
2996 p_ecc_ac_flag IN VARCHAR2,
2997 p_crit_cat_name IN VARCHAR2,
2998 p_orig_crit_cat_def_id IN VARCHAR2,
2999 p_value_type_code IN VARCHAR2,
3000 p_data_type_code IN VARCHAR2,
3001 p_enabled_yn IN VARCHAR2,
3002 p_seeded_yn IN VARCHAR2,
3003 p_function_id IN VARCHAR2,
3004 p_source_yn IN VARCHAR2,
3005 p_sql_statement IN VARCHAR2,
3006 p_trans_crit_cat_desc IN VARCHAR2,
3007 p_owner IN VARCHAR2,
3008 p_last_update_date IN VARCHAR2) IS
3009 l_api_version CONSTANT number := 1;
3010 l_api_name CONSTANT varchar2(30) := 'LOAD_SEED_ROW';
3011 l_return_status varchar2(1) := okl_api.g_ret_sts_success;
3012 l_msg_count number;
3013 l_msg_data varchar2(4000);
3014 l_init_msg_list VARCHAR2(1):= 'T';
3015 l_eccv_rec okl_eccv_rec;
3016 BEGIN
3017 --Prepare Record Structure for Insert/Update
3018 l_eccv_rec.crit_cat_def_id := TO_NUMBER(p_crit_cat_def_id);
3019 l_eccv_rec.object_version_number := TO_NUMBER(p_object_version_number);
3020 l_eccv_rec.ecc_ac_flag := p_ecc_ac_flag;
3021 l_eccv_rec.orig_crit_cat_def_id := TO_NUMBER(p_orig_crit_cat_def_id);
3022 l_eccv_rec.crit_cat_name := p_crit_cat_name;
3023 l_eccv_rec.crit_cat_desc := p_trans_crit_cat_desc;
3024 l_eccv_rec.value_type_code := p_value_type_code;
3025 l_eccv_rec.data_type_code := p_data_type_code;
3026 l_eccv_rec.enabled_yn := p_enabled_yn;
3027 l_eccv_rec.seeded_yn := p_seeded_yn;
3028 l_eccv_rec.function_id := p_function_id;
3029 l_eccv_rec.source_yn := p_source_yn;
3030 l_eccv_rec.sql_statement := p_sql_statement;
3031 IF(p_upload_mode = 'NLS') then
3032 OKL_ECC_PVT.TRANSLATE_ROW(p_eccv_rec => l_eccv_rec,
3033 p_owner => p_owner,
3034 p_last_update_date => p_last_update_date,
3035 x_return_status => l_return_status);
3036 ELSE
3037 OKL_ECC_PVT.LOAD_ROW(p_eccv_rec => l_eccv_rec,
3038 p_owner => p_owner,
3039 p_last_update_date => p_last_update_date,
3040 x_return_status => l_return_status);
3041 END IF;
3042 END LOAD_SEED_ROW;
3043
3044 END okl_ecc_pvt;
3045