[Home] [Help]
PACKAGE BODY: APPS.OKS_CVP_PVT
Source
1 Package Body OKS_CVP_PVT AS
2 /* $Header: OKSSCVPB.pls 120.0 2005/05/25 18:10:08 appldev noship $ */
3 ---------------------------------------------------------------------------
4 -- PROCEDURE load_error_tbl
5 ---------------------------------------------------------------------------
6 PROCEDURE load_error_tbl (
7 px_error_rec IN OUT NOCOPY OKC_API.ERROR_REC_TYPE,
8 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
9
10 j INTEGER := NVL(px_error_tbl.LAST, 0) + 1;
11 last_msg_idx INTEGER := FND_MSG_PUB.COUNT_MSG;
12 l_msg_idx INTEGER := FND_MSG_PUB.G_NEXT;
13 BEGIN
14 -- FND_MSG_PUB has a small error in it. If we call FND_MSG_PUB.COUNT_AND_GET before
15 -- we call FND_MSG_PUB.GET, the variable FND_MSG_PUB uses to control the index of the
16 -- message stack gets set to 1. This makes sense until we call FND_MSG_PUB.GET which
17 -- automatically increments the index by 1, (making it 2), however, when the GET function
18 -- attempts to pull message 2, we get a NO_DATA_FOUND exception because there isn't any
19 -- message 2. To circumvent this problem, check the amount of messages and compensate.
20 -- Again, this error only occurs when 1 message is on the stack because COUNT_AND_GET
21 -- will only update the index variable when 1 and only 1 message is on the stack.
22 IF (last_msg_idx = 1) THEN
23 l_msg_idx := FND_MSG_PUB.G_FIRST;
24 END IF;
25 LOOP
26 fnd_msg_pub.get(
27 p_msg_index => l_msg_idx,
28 p_encoded => fnd_api.g_false,
29 p_data => px_error_rec.msg_data,
30 p_msg_index_out => px_error_rec.msg_count);
31 px_error_tbl(j) := px_error_rec;
32 j := j + 1;
33 EXIT WHEN (px_error_rec.msg_count = last_msg_idx);
34 END LOOP;
35 END load_error_tbl;
36 ---------------------------------------------------------------------------
37 -- FUNCTION find_highest_exception
38 ---------------------------------------------------------------------------
39 -- Finds the highest exception (G_RET_STS_UNEXP_ERROR)
40 -- in a OKC_API.ERROR_TBL_TYPE, and returns it.
41 FUNCTION find_highest_exception(
42 p_error_tbl IN OKC_API.ERROR_TBL_TYPE
43 ) RETURN VARCHAR2 IS
44 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
45 i INTEGER := 1;
46 BEGIN
47 IF (p_error_tbl.COUNT > 0) THEN
48 i := p_error_tbl.FIRST;
49 LOOP
50 IF (p_error_tbl(i).error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
51 IF (l_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
52 l_return_status := p_error_tbl(i).error_type;
53 END IF;
54 END IF;
55 EXIT WHEN (i = p_error_tbl.LAST);
56 i := p_error_tbl.NEXT(i);
57 END LOOP;
58 END IF;
59 RETURN(l_return_status);
60 END find_highest_exception;
61 ---------------------------------------------------------------------------
62 -- FUNCTION get_seq_id
63 ---------------------------------------------------------------------------
64 FUNCTION get_seq_id RETURN NUMBER IS
65 BEGIN
66 RETURN(okc_p_util.raw_to_number(sys_guid()));
67 END get_seq_id;
68
69 ---------------------------------------------------------------------------
70 -- PROCEDURE qc
71 ---------------------------------------------------------------------------
72 PROCEDURE qc IS
73 BEGIN
74 null;
75 END qc;
76
77 ---------------------------------------------------------------------------
78 -- PROCEDURE change_version
79 ---------------------------------------------------------------------------
80 PROCEDURE change_version IS
81 BEGIN
82 null;
83 END change_version;
84
85 ---------------------------------------------------------------------------
86 -- PROCEDURE api_copy
87 ---------------------------------------------------------------------------
88 PROCEDURE api_copy IS
89 BEGIN
90 null;
91 END api_copy;
92
93 ---------------------------------------------------------------------------
94 -- PROCEDURE add_language
95 ---------------------------------------------------------------------------
96 PROCEDURE add_language IS
97 BEGIN
98 DELETE FROM OKS_COV_TYPES_TL T
99 WHERE NOT EXISTS (
100 SELECT NULL
101 FROM OKS_COV_TYPES_B B
102 WHERE B.CODE =T.CODE
103 );
104
105 UPDATE OKS_COV_TYPES_TL T SET(
106 MEANING,
107 DESCRIPTION) = (SELECT
108 B.MEANING,
109 B.DESCRIPTION
110 FROM OKS_COV_TYPES_TL B
111 WHERE B.CODE = T.CODE
112 AND B.LANGUAGE = T.SOURCE_LANG)
113 WHERE ( T.CODE,
114 T.LANGUAGE)
115 IN (SELECT
116 SUBT.CODE,
117 SUBT.LANGUAGE
118 FROM OKS_COV_TYPES_TL SUBB, OKS_COV_TYPES_TL SUBT
119 WHERE SUBB.CODE = SUBT.CODE
120 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
121 AND (SUBB.MEANING <> SUBT.MEANING
122 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
123 OR (SUBB.MEANING IS NOT NULL AND SUBT.MEANING IS NULL)
124 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
125 ));
126
127 INSERT INTO OKS_COV_TYPES_TL (
128 CODE,
129 LANGUAGE,
130 SOURCE_LANG,
131 SFWT_FLAG,
132 MEANING,
133 DESCRIPTION,
134 CREATED_BY,
135 CREATION_DATE,
136 LAST_UPDATED_BY,
137 LAST_UPDATE_LOGIN,
138 LAST_UPDATE_DATE)
139 SELECT
140 B.CODE,
141 L.LANGUAGE_CODE,
142 B.SOURCE_LANG,
143 B.SFWT_FLAG,
144 B.MEANING,
145 B.DESCRIPTION,
146 B.CREATED_BY,
147 B.CREATION_DATE,
148 B.LAST_UPDATED_BY,
149 B.LAST_UPDATE_LOGIN,
150 B.LAST_UPDATE_DATE
151 FROM OKS_COV_TYPES_TL B, FND_LANGUAGES L
152 WHERE L.INSTALLED_FLAG IN ('I', 'B')
153 AND B.LANGUAGE = USERENV('LANG')
154 AND NOT EXISTS (
155 SELECT NULL
156 FROM OKS_COV_TYPES_TL T
157 WHERE T.CODE = B.CODE
158 AND T.LANGUAGE = L.LANGUAGE_CODE
159 );
160 END add_language;
161
162 ---------------------------------------------------------------------------
163 -- FUNCTION get_rec for: OKS_COV_TYPES_V
164 ---------------------------------------------------------------------------
165 FUNCTION get_rec (
166 p_cvpv_rec IN cvpv_rec_type,
167 x_no_data_found OUT NOCOPY BOOLEAN
168 ) RETURN cvpv_rec_type IS
169 CURSOR oks_cvt_pk_csr (p_code IN VARCHAR2) IS
170 SELECT
171 CODE,
172 MEANING,
173 DESCRIPTION,
174 IMPORTANCE_LEVEL,
175 SFWT_FLAG,
176 ENABLED_FLAG,
177 START_DATE_ACTIVE,
178 END_DATE_ACTIVE,
179 CREATED_BY,
180 CREATION_DATE,
181 LAST_UPDATED_BY,
182 LAST_UPDATE_LOGIN,
183 LAST_UPDATE_DATE,
184 ATTRIBUTE_CATEGORY,
185 ATTRIBUTE1,
186 ATTRIBUTE2,
187 ATTRIBUTE3,
188 ATTRIBUTE4,
189 ATTRIBUTE5,
190 ATTRIBUTE6,
191 ATTRIBUTE7,
192 ATTRIBUTE8,
193 ATTRIBUTE9,
194 ATTRIBUTE10,
195 ATTRIBUTE11,
196 ATTRIBUTE12,
197 ATTRIBUTE13,
198 ATTRIBUTE14,
199 ATTRIBUTE15
200 FROM Oks_Cov_Types_V
201 WHERE oks_cov_types_v.code = p_code;
202 l_oks_cvt_pk oks_cvt_pk_csr%ROWTYPE;
203 l_cvpv_rec cvpv_rec_type;
204 BEGIN
205 x_no_data_found := TRUE;
206 -- Get current database values
207 OPEN oks_cvt_pk_csr (p_cvpv_rec.code);
208 FETCH oks_cvt_pk_csr INTO
209 l_cvpv_rec.code,
210 l_cvpv_rec.meaning,
211 l_cvpv_rec.description,
212 l_cvpv_rec.importance_level,
213 l_cvpv_rec.sfwt_flag,
214 l_cvpv_rec.enabled_flag,
215 l_cvpv_rec.start_date_active,
216 l_cvpv_rec.end_date_active,
217 l_cvpv_rec.created_by,
218 l_cvpv_rec.creation_date,
219 l_cvpv_rec.last_updated_by,
220 l_cvpv_rec.last_update_login,
221 l_cvpv_rec.last_update_date,
222 l_cvpv_rec.attribute_category,
223 l_cvpv_rec.attribute1,
224 l_cvpv_rec.attribute2,
225 l_cvpv_rec.attribute3,
226 l_cvpv_rec.attribute4,
227 l_cvpv_rec.attribute5,
228 l_cvpv_rec.attribute6,
229 l_cvpv_rec.attribute7,
230 l_cvpv_rec.attribute8,
231 l_cvpv_rec.attribute9,
232 l_cvpv_rec.attribute10,
233 l_cvpv_rec.attribute11,
234 l_cvpv_rec.attribute12,
235 l_cvpv_rec.attribute13,
236 l_cvpv_rec.attribute14,
237 l_cvpv_rec.attribute15;
238 x_no_data_found := oks_cvt_pk_csr%NOTFOUND;
239 CLOSE oks_cvt_pk_csr;
240 RETURN(l_cvpv_rec);
241 END get_rec;
242
243 ------------------------------------------------------------------
244 -- This version of get_rec sets error messages if no data found --
245 ------------------------------------------------------------------
246 FUNCTION get_rec (
247 p_cvpv_rec IN cvpv_rec_type,
248 x_return_status OUT NOCOPY VARCHAR2
249 ) RETURN cvpv_rec_type IS
250 l_cvpv_rec cvpv_rec_type;
251 l_row_notfound BOOLEAN := TRUE;
252 BEGIN
253 x_return_status := OKC_API.G_RET_STS_SUCCESS;
254 l_cvpv_rec := get_rec(p_cvpv_rec, l_row_notfound);
255 IF (l_row_notfound) THEN
256 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'CODE');
257 x_return_status := OKC_API.G_RET_STS_ERROR;
258 END IF;
259 RETURN(l_cvpv_rec);
260 END get_rec;
261 -----------------------------------------------------------
262 -- So we don't have to pass an "l_row_notfound" variable --
263 -----------------------------------------------------------
264 FUNCTION get_rec (
265 p_cvpv_rec IN cvpv_rec_type
266 ) RETURN cvpv_rec_type IS
267 l_row_not_found BOOLEAN := TRUE;
268 BEGIN
269 RETURN(get_rec(p_cvpv_rec, l_row_not_found));
270 END get_rec;
271 ---------------------------------------------------------------------------
272 -- FUNCTION get_rec for: OKS_COV_TYPES_TL
273 ---------------------------------------------------------------------------
274 FUNCTION get_rec (
275 p_oks_cov_types_tl_rec IN oks_cov_types_tl_rec_type,
276 x_no_data_found OUT NOCOPY BOOLEAN
277 ) RETURN oks_cov_types_tl_rec_type IS
278 CURSOR oks_cov_types_tl_pk_csr (p_code IN VARCHAR2,
279 p_language IN VARCHAR2) IS
280 SELECT
281 CODE,
282 LANGUAGE,
283 SOURCE_LANG,
284 SFWT_FLAG,
285 MEANING,
286 DESCRIPTION,
287 CREATED_BY,
288 CREATION_DATE,
289 LAST_UPDATED_BY,
290 LAST_UPDATE_LOGIN,
291 LAST_UPDATE_DATE
292 FROM Oks_Cov_Types_Tl
293 WHERE oks_cov_types_tl.code = p_code
294 AND oks_cov_types_tl.language = p_language;
295 l_oks_cov_types_tl_pk oks_cov_types_tl_pk_csr%ROWTYPE;
296 l_oks_cov_types_tl_rec oks_cov_types_tl_rec_type;
297 BEGIN
298 x_no_data_found := TRUE;
299 -- Get current database values
300 OPEN oks_cov_types_tl_pk_csr (p_oks_cov_types_tl_rec.code,
301 p_oks_cov_types_tl_rec.language);
302 FETCH oks_cov_types_tl_pk_csr INTO
303 l_oks_cov_types_tl_rec.code,
304 l_oks_cov_types_tl_rec.language,
305 l_oks_cov_types_tl_rec.source_lang,
306 l_oks_cov_types_tl_rec.sfwt_flag,
307 l_oks_cov_types_tl_rec.meaning,
308 l_oks_cov_types_tl_rec.description,
309 l_oks_cov_types_tl_rec.created_by,
310 l_oks_cov_types_tl_rec.creation_date,
311 l_oks_cov_types_tl_rec.last_updated_by,
312 l_oks_cov_types_tl_rec.last_update_login,
313 l_oks_cov_types_tl_rec.last_update_date;
314 x_no_data_found := oks_cov_types_tl_pk_csr%NOTFOUND;
315 CLOSE oks_cov_types_tl_pk_csr;
316 RETURN(l_oks_cov_types_tl_rec);
317 END get_rec;
318
319 ------------------------------------------------------------------
320 -- This version of get_rec sets error messages if no data found --
321 ------------------------------------------------------------------
322 FUNCTION get_rec (
323 p_oks_cov_types_tl_rec IN oks_cov_types_tl_rec_type,
324 x_return_status OUT NOCOPY VARCHAR2
325 ) RETURN oks_cov_types_tl_rec_type IS
326 l_oks_cov_types_tl_rec oks_cov_types_tl_rec_type;
327 l_row_notfound BOOLEAN := TRUE;
328 BEGIN
329 x_return_status := OKC_API.G_RET_STS_SUCCESS;
330 l_oks_cov_types_tl_rec := get_rec(p_oks_cov_types_tl_rec, l_row_notfound);
331 IF (l_row_notfound) THEN
332 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'CODE');
333 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'LANGUAGE');
334 x_return_status := OKC_API.G_RET_STS_ERROR;
335 END IF;
336 RETURN(l_oks_cov_types_tl_rec);
337 END get_rec;
338 -----------------------------------------------------------
339 -- So we don't have to pass an "l_row_notfound" variable --
340 -----------------------------------------------------------
341 FUNCTION get_rec (
342 p_oks_cov_types_tl_rec IN oks_cov_types_tl_rec_type
343 ) RETURN oks_cov_types_tl_rec_type IS
344 l_row_not_found BOOLEAN := TRUE;
345 BEGIN
346 RETURN(get_rec(p_oks_cov_types_tl_rec, l_row_not_found));
347 END get_rec;
348 ---------------------------------------------------------------------------
349 -- FUNCTION get_rec for: OKS_COV_TYPES_B
350 ---------------------------------------------------------------------------
351 FUNCTION get_rec (
352 p_cvp_rec IN cvp_rec_type,
353 x_no_data_found OUT NOCOPY BOOLEAN
354 ) RETURN cvp_rec_type IS
355 CURSOR oks_cov_types_b_pk_csr (p_code IN VARCHAR2) IS
356 SELECT
357 CODE,
358 IMPORTANCE_LEVEL,
359 ENABLED_FLAG,
360 START_DATE_ACTIVE,
361 END_DATE_ACTIVE,
362 CREATED_BY,
363 CREATION_DATE,
364 LAST_UPDATED_BY,
365 LAST_UPDATE_LOGIN,
366 LAST_UPDATE_DATE,
367 ATTRIBUTE_CATEGORY,
368 ATTRIBUTE1,
369 ATTRIBUTE2,
370 ATTRIBUTE3,
371 ATTRIBUTE4,
372 ATTRIBUTE5,
373 ATTRIBUTE6,
374 ATTRIBUTE7,
375 ATTRIBUTE8,
376 ATTRIBUTE9,
377 ATTRIBUTE10,
378 ATTRIBUTE11,
379 ATTRIBUTE12,
380 ATTRIBUTE13,
381 ATTRIBUTE14,
382 ATTRIBUTE15
383 FROM Oks_Cov_Types_B
384 WHERE oks_cov_types_b.code = p_code;
385 l_oks_cov_types_b_pk oks_cov_types_b_pk_csr%ROWTYPE;
386 l_cvp_rec cvp_rec_type;
387 BEGIN
388 x_no_data_found := TRUE;
389 -- Get current database values
390 OPEN oks_cov_types_b_pk_csr (p_cvp_rec.code);
391 FETCH oks_cov_types_b_pk_csr INTO
392 l_cvp_rec.code,
393 l_cvp_rec.importance_level,
394 l_cvp_rec.enabled_flag,
395 l_cvp_rec.start_date_active,
396 l_cvp_rec.end_date_active,
397 l_cvp_rec.created_by,
398 l_cvp_rec.creation_date,
399 l_cvp_rec.last_updated_by,
400 l_cvp_rec.last_update_login,
401 l_cvp_rec.last_update_date,
402 l_cvp_rec.attribute_category,
403 l_cvp_rec.attribute1,
404 l_cvp_rec.attribute2,
405 l_cvp_rec.attribute3,
406 l_cvp_rec.attribute4,
407 l_cvp_rec.attribute5,
408 l_cvp_rec.attribute6,
409 l_cvp_rec.attribute7,
410 l_cvp_rec.attribute8,
411 l_cvp_rec.attribute9,
412 l_cvp_rec.attribute10,
413 l_cvp_rec.attribute11,
414 l_cvp_rec.attribute12,
415 l_cvp_rec.attribute13,
416 l_cvp_rec.attribute14,
417 l_cvp_rec.attribute15;
418 x_no_data_found := oks_cov_types_b_pk_csr%NOTFOUND;
419 CLOSE oks_cov_types_b_pk_csr;
420 RETURN(l_cvp_rec);
421 END get_rec;
422
423 ------------------------------------------------------------------
424 -- This version of get_rec sets error messages if no data found --
425 ------------------------------------------------------------------
426 FUNCTION get_rec (
427 p_cvp_rec IN cvp_rec_type,
428 x_return_status OUT NOCOPY VARCHAR2
429 ) RETURN cvp_rec_type IS
430 l_cvp_rec cvp_rec_type;
431 l_row_notfound BOOLEAN := TRUE;
432 BEGIN
433 x_return_status := OKC_API.G_RET_STS_SUCCESS;
434 l_cvp_rec := get_rec(p_cvp_rec, l_row_notfound);
435 IF (l_row_notfound) THEN
436 OKC_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'CODE');
437 x_return_status := OKC_API.G_RET_STS_ERROR;
438 END IF;
439 RETURN(l_cvp_rec);
440 END get_rec;
441 -----------------------------------------------------------
442 -- So we don't have to pass an "l_row_notfound" variable --
443 -----------------------------------------------------------
444 FUNCTION get_rec (
445 p_cvp_rec IN cvp_rec_type
446 ) RETURN cvp_rec_type IS
447 l_row_not_found BOOLEAN := TRUE;
448 BEGIN
449 RETURN(get_rec(p_cvp_rec, l_row_not_found));
450 END get_rec;
451 ---------------------------------------------------------------------------
452 -- FUNCTION null_out_defaults for: OKS_COV_TYPES_V
453 ---------------------------------------------------------------------------
454 FUNCTION null_out_defaults (
455 p_cvpv_rec IN cvpv_rec_type
456 ) RETURN cvpv_rec_type IS
457 l_cvpv_rec cvpv_rec_type := p_cvpv_rec;
458 BEGIN
459 IF (l_cvpv_rec.code = OKC_API.G_MISS_CHAR ) THEN
460 l_cvpv_rec.code := NULL;
461 END IF;
462 IF (l_cvpv_rec.meaning = OKC_API.G_MISS_CHAR ) THEN
463 l_cvpv_rec.meaning := NULL;
464 END IF;
465 IF (l_cvpv_rec.description = OKC_API.G_MISS_CHAR ) THEN
466 l_cvpv_rec.description := NULL;
467 END IF;
468 IF (l_cvpv_rec.importance_level = OKC_API.G_MISS_NUM ) THEN
469 l_cvpv_rec.importance_level := NULL;
470 END IF;
471 IF (l_cvpv_rec.sfwt_flag = OKC_API.G_MISS_CHAR ) THEN
472 l_cvpv_rec.sfwt_flag := NULL;
473 END IF;
474 IF (l_cvpv_rec.enabled_flag = OKC_API.G_MISS_CHAR ) THEN
475 l_cvpv_rec.enabled_flag := NULL;
476 END IF;
477 IF (l_cvpv_rec.start_date_active = OKC_API.G_MISS_DATE ) THEN
478 l_cvpv_rec.start_date_active := NULL;
479 END IF;
480 IF (l_cvpv_rec.end_date_active = OKC_API.G_MISS_DATE ) THEN
481 l_cvpv_rec.end_date_active := NULL;
482 END IF;
483 IF (l_cvpv_rec.created_by = OKC_API.G_MISS_NUM ) THEN
484 l_cvpv_rec.created_by := NULL;
485 END IF;
486 IF (l_cvpv_rec.creation_date = OKC_API.G_MISS_DATE ) THEN
487 l_cvpv_rec.creation_date := NULL;
488 END IF;
489 IF (l_cvpv_rec.last_updated_by = OKC_API.G_MISS_NUM ) THEN
490 l_cvpv_rec.last_updated_by := NULL;
491 END IF;
492 IF (l_cvpv_rec.last_update_login = OKC_API.G_MISS_NUM ) THEN
493 l_cvpv_rec.last_update_login := NULL;
494 END IF;
495 IF (l_cvpv_rec.last_update_date = OKC_API.G_MISS_DATE ) THEN
496 l_cvpv_rec.last_update_date := NULL;
497 END IF;
498 IF (l_cvpv_rec.attribute_category = OKC_API.G_MISS_CHAR ) THEN
499 l_cvpv_rec.attribute_category := NULL;
500 END IF;
501 IF (l_cvpv_rec.attribute1 = OKC_API.G_MISS_CHAR ) THEN
502 l_cvpv_rec.attribute1 := NULL;
503 END IF;
504 IF (l_cvpv_rec.attribute2 = OKC_API.G_MISS_CHAR ) THEN
505 l_cvpv_rec.attribute2 := NULL;
506 END IF;
507 IF (l_cvpv_rec.attribute3 = OKC_API.G_MISS_CHAR ) THEN
508 l_cvpv_rec.attribute3 := NULL;
509 END IF;
510 IF (l_cvpv_rec.attribute4 = OKC_API.G_MISS_CHAR ) THEN
511 l_cvpv_rec.attribute4 := NULL;
512 END IF;
513 IF (l_cvpv_rec.attribute5 = OKC_API.G_MISS_CHAR ) THEN
514 l_cvpv_rec.attribute5 := NULL;
515 END IF;
516 IF (l_cvpv_rec.attribute6 = OKC_API.G_MISS_CHAR ) THEN
517 l_cvpv_rec.attribute6 := NULL;
518 END IF;
519 IF (l_cvpv_rec.attribute7 = OKC_API.G_MISS_CHAR ) THEN
520 l_cvpv_rec.attribute7 := NULL;
521 END IF;
522 IF (l_cvpv_rec.attribute8 = OKC_API.G_MISS_CHAR ) THEN
523 l_cvpv_rec.attribute8 := NULL;
524 END IF;
525 IF (l_cvpv_rec.attribute9 = OKC_API.G_MISS_CHAR ) THEN
526 l_cvpv_rec.attribute9 := NULL;
527 END IF;
528 IF (l_cvpv_rec.attribute10 = OKC_API.G_MISS_CHAR ) THEN
529 l_cvpv_rec.attribute10 := NULL;
530 END IF;
531 IF (l_cvpv_rec.attribute11 = OKC_API.G_MISS_CHAR ) THEN
532 l_cvpv_rec.attribute11 := NULL;
533 END IF;
534 IF (l_cvpv_rec.attribute12 = OKC_API.G_MISS_CHAR ) THEN
535 l_cvpv_rec.attribute12 := NULL;
536 END IF;
537 IF (l_cvpv_rec.attribute13 = OKC_API.G_MISS_CHAR ) THEN
538 l_cvpv_rec.attribute13 := NULL;
539 END IF;
540 IF (l_cvpv_rec.attribute14 = OKC_API.G_MISS_CHAR ) THEN
541 l_cvpv_rec.attribute14 := NULL;
542 END IF;
543 IF (l_cvpv_rec.attribute15 = OKC_API.G_MISS_CHAR ) THEN
544 l_cvpv_rec.attribute15 := NULL;
545 END IF;
546 RETURN(l_cvpv_rec);
547 END null_out_defaults;
548 -----------------------------------
549 -- Validate_Attributes for: CODE --
550 -----------------------------------
551 PROCEDURE validate_code(
552 x_return_status OUT NOCOPY VARCHAR2,
553 p_code IN VARCHAR2) IS
554 BEGIN
555 x_return_status := OKC_API.G_RET_STS_SUCCESS;
556 IF (p_code = OKC_API.G_MISS_CHAR OR
557 p_code IS NULL)
558 THEN
559 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'code');
560 x_return_status := OKC_API.G_RET_STS_ERROR;
561 RAISE G_EXCEPTION_HALT_VALIDATION;
562 END IF;
563 EXCEPTION
564 WHEN G_EXCEPTION_HALT_VALIDATION THEN
565 null;
566 WHEN OTHERS THEN
567 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
568 ,p_msg_name => G_UNEXPECTED_ERROR
569 ,p_token1 => G_SQLCODE_TOKEN
570 ,p_token1_value => SQLCODE
571 ,p_token2 => G_SQLERRM_TOKEN
572 ,p_token2_value => SQLERRM);
573 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
574 END validate_code;
575 --------------------------------------
576 -- Validate_Attributes for: MEANING --
577 --------------------------------------
578 PROCEDURE validate_meaning(
579 x_return_status OUT NOCOPY VARCHAR2,
580 p_meaning IN VARCHAR2) IS
581 BEGIN
582 x_return_status := OKC_API.G_RET_STS_SUCCESS;
583 IF (p_meaning = OKC_API.G_MISS_CHAR OR
584 p_meaning IS NULL)
585 THEN
586 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'meaning');
587 x_return_status := OKC_API.G_RET_STS_ERROR;
588 RAISE G_EXCEPTION_HALT_VALIDATION;
589 END IF;
590 EXCEPTION
591 WHEN G_EXCEPTION_HALT_VALIDATION THEN
592 null;
593 WHEN OTHERS THEN
594 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
595 ,p_msg_name => G_UNEXPECTED_ERROR
596 ,p_token1 => G_SQLCODE_TOKEN
597 ,p_token1_value => SQLCODE
598 ,p_token2 => G_SQLERRM_TOKEN
599 ,p_token2_value => SQLERRM);
600 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
601 END validate_meaning;
602 -------------------------------------------
603 -- Validate_Attributes for: ENABLED_FLAG --
604 -------------------------------------------
605 PROCEDURE validate_enabled_flag(
606 x_return_status OUT NOCOPY VARCHAR2,
607 p_enabled_flag IN VARCHAR2) IS
608 BEGIN
609 x_return_status := OKC_API.G_RET_STS_SUCCESS;
610 IF (p_enabled_flag = OKC_API.G_MISS_CHAR OR
611 p_enabled_flag IS NULL)
612 THEN
613 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'enabled_flag');
614 x_return_status := OKC_API.G_RET_STS_ERROR;
615 RAISE G_EXCEPTION_HALT_VALIDATION;
616 END IF;
617 EXCEPTION
618 WHEN G_EXCEPTION_HALT_VALIDATION THEN
619 null;
620 WHEN OTHERS THEN
621 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
622 ,p_msg_name => G_UNEXPECTED_ERROR
623 ,p_token1 => G_SQLCODE_TOKEN
624 ,p_token1_value => SQLCODE
625 ,p_token2 => G_SQLERRM_TOKEN
626 ,p_token2_value => SQLERRM);
627 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
628 END validate_enabled_flag;
629 ---------------------------------------------------------------------------
630 -- FUNCTION Validate_Attributes
631 ---------------------------------------------------------------------------
632 ---------------------------------------------
633 -- Validate_Attributes for:OKS_COV_TYPES_V --
634 ---------------------------------------------
635 FUNCTION Validate_Attributes (
636 p_cvpv_rec IN cvpv_rec_type
637 ) RETURN VARCHAR2 IS
638 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
639 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
640 BEGIN
641 -----------------------------
642 -- Column Level Validation --
643 -----------------------------
644 -- ***
645 -- code
646 -- ***
647 validate_code(x_return_status, p_cvpv_rec.code);
648 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
649 l_return_status := x_return_status;
650 RAISE G_EXCEPTION_HALT_VALIDATION;
651 END IF;
652
653 -- ***
654 -- meaning
655 -- ***
656 validate_meaning(x_return_status, p_cvpv_rec.meaning);
657 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
658 l_return_status := x_return_status;
659 RAISE G_EXCEPTION_HALT_VALIDATION;
660 END IF;
661
662 -- ***
663 -- enabled_flag
664 -- ***
665 validate_enabled_flag(x_return_status, p_cvpv_rec.enabled_flag);
666 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
667 l_return_status := x_return_status;
668 RAISE G_EXCEPTION_HALT_VALIDATION;
669 END IF;
670
671 RETURN(l_return_status);
672 EXCEPTION
673 WHEN G_EXCEPTION_HALT_VALIDATION THEN
674 RETURN(l_return_status);
675 WHEN OTHERS THEN
676 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
677 ,p_msg_name => G_UNEXPECTED_ERROR
678 ,p_token1 => G_SQLCODE_TOKEN
679 ,p_token1_value => SQLCODE
680 ,p_token2 => G_SQLERRM_TOKEN
681 ,p_token2_value => SQLERRM);
682 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
683 RETURN(l_return_status);
684 END Validate_Attributes;
685 ---------------------------------------------------------------------------
686 -- PROCEDURE Validate_Record
687 ---------------------------------------------------------------------------
688 -----------------------------------------
689 -- Validate Record for:OKS_COV_TYPES_V --
690 -----------------------------------------
691 FUNCTION Validate_Record (
692 p_cvpv_rec IN cvpv_rec_type,
693 p_db_cvpv_rec IN cvpv_rec_type
694 ) RETURN VARCHAR2 IS
695 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
696 BEGIN
697 RETURN (l_return_status);
698 END Validate_Record;
699 FUNCTION Validate_Record (
700 p_cvpv_rec IN cvpv_rec_type
701 ) RETURN VARCHAR2 IS
702 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
703 l_db_cvpv_rec cvpv_rec_type := get_rec(p_cvpv_rec);
704 BEGIN
705 l_return_status := Validate_Record(p_cvpv_rec => p_cvpv_rec,
706 p_db_cvpv_rec => l_db_cvpv_rec);
707 RETURN (l_return_status);
708 END Validate_Record;
709
710 ---------------------------------------------------------------------------
711 -- PROCEDURE Migrate
712 ---------------------------------------------------------------------------
713 PROCEDURE migrate (
714 p_from IN cvpv_rec_type,
715 p_to IN OUT NOCOPY oks_cov_types_tl_rec_type
716 ) IS
717 BEGIN
718 p_to.code := p_from.code;
719 p_to.sfwt_flag := p_from.sfwt_flag;
720 p_to.meaning := p_from.meaning;
721 p_to.description := p_from.description;
722 p_to.created_by := p_from.created_by;
723 p_to.creation_date := p_from.creation_date;
724 p_to.last_updated_by := p_from.last_updated_by;
725 p_to.last_update_login := p_from.last_update_login;
726 p_to.last_update_date := p_from.last_update_date;
727 END migrate;
728 PROCEDURE migrate (
729 p_from IN oks_cov_types_tl_rec_type,
730 p_to IN OUT NOCOPY cvpv_rec_type
731 ) IS
732 BEGIN
733 p_to.code := p_from.code;
734 p_to.meaning := p_from.meaning;
735 p_to.description := p_from.description;
736 p_to.sfwt_flag := p_from.sfwt_flag;
737 p_to.created_by := p_from.created_by;
738 p_to.creation_date := p_from.creation_date;
739 p_to.last_updated_by := p_from.last_updated_by;
740 p_to.last_update_login := p_from.last_update_login;
741 p_to.last_update_date := p_from.last_update_date;
742 END migrate;
743 PROCEDURE migrate (
744 p_from IN cvpv_rec_type,
745 p_to IN OUT NOCOPY cvp_rec_type
746 ) IS
747 BEGIN
748 p_to.code := p_from.code;
749 p_to.importance_level := p_from.importance_level;
750 p_to.enabled_flag := p_from.enabled_flag;
751 p_to.start_date_active := p_from.start_date_active;
752 p_to.end_date_active := p_from.end_date_active;
753 p_to.created_by := p_from.created_by;
754 p_to.creation_date := p_from.creation_date;
755 p_to.last_updated_by := p_from.last_updated_by;
756 p_to.last_update_login := p_from.last_update_login;
757 p_to.last_update_date := p_from.last_update_date;
758 p_to.attribute_category := p_from.attribute_category;
759 p_to.attribute1 := p_from.attribute1;
760 p_to.attribute2 := p_from.attribute2;
761 p_to.attribute3 := p_from.attribute3;
762 p_to.attribute4 := p_from.attribute4;
763 p_to.attribute5 := p_from.attribute5;
764 p_to.attribute6 := p_from.attribute6;
765 p_to.attribute7 := p_from.attribute7;
766 p_to.attribute8 := p_from.attribute8;
767 p_to.attribute9 := p_from.attribute9;
768 p_to.attribute10 := p_from.attribute10;
769 p_to.attribute11 := p_from.attribute11;
770 p_to.attribute12 := p_from.attribute12;
771 p_to.attribute13 := p_from.attribute13;
772 p_to.attribute14 := p_from.attribute14;
773 p_to.attribute15 := p_from.attribute15;
774 END migrate;
775 PROCEDURE migrate (
776 p_from IN cvp_rec_type,
777 p_to IN OUT NOCOPY cvpv_rec_type
778 ) IS
779 BEGIN
780 p_to.code := p_from.code;
781 p_to.importance_level := p_from.importance_level;
782 p_to.enabled_flag := p_from.enabled_flag;
783 p_to.start_date_active := p_from.start_date_active;
784 p_to.end_date_active := p_from.end_date_active;
785 p_to.created_by := p_from.created_by;
786 p_to.creation_date := p_from.creation_date;
787 p_to.last_updated_by := p_from.last_updated_by;
788 p_to.last_update_login := p_from.last_update_login;
789 p_to.last_update_date := p_from.last_update_date;
790 p_to.attribute_category := p_from.attribute_category;
791 p_to.attribute1 := p_from.attribute1;
792 p_to.attribute2 := p_from.attribute2;
793 p_to.attribute3 := p_from.attribute3;
794 p_to.attribute4 := p_from.attribute4;
795 p_to.attribute5 := p_from.attribute5;
796 p_to.attribute6 := p_from.attribute6;
797 p_to.attribute7 := p_from.attribute7;
798 p_to.attribute8 := p_from.attribute8;
799 p_to.attribute9 := p_from.attribute9;
800 p_to.attribute10 := p_from.attribute10;
801 p_to.attribute11 := p_from.attribute11;
802 p_to.attribute12 := p_from.attribute12;
803 p_to.attribute13 := p_from.attribute13;
804 p_to.attribute14 := p_from.attribute14;
805 p_to.attribute15 := p_from.attribute15;
806 END migrate;
807 ---------------------------------------------------------------------------
808 -- PROCEDURE validate_row
809 ---------------------------------------------------------------------------
810 --------------------------------------
811 -- validate_row for:OKS_COV_TYPES_V --
812 --------------------------------------
813 PROCEDURE validate_row(
814 p_api_version IN NUMBER,
815 p_init_msg_list IN VARCHAR2,
816 x_return_status OUT NOCOPY VARCHAR2,
817 x_msg_count OUT NOCOPY NUMBER,
818 x_msg_data OUT NOCOPY VARCHAR2,
819 p_cvpv_rec IN cvpv_rec_type) IS
820
821 l_api_version CONSTANT NUMBER := 1;
822 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
823 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
824 l_cvpv_rec cvpv_rec_type := p_cvpv_rec;
825 l_cvp_rec cvp_rec_type;
826 l_oks_cov_types_tl_rec oks_cov_types_tl_rec_type;
827 BEGIN
828 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
829 G_PKG_NAME,
830 p_init_msg_list,
831 l_api_version,
832 p_api_version,
833 '_PVT',
834 x_return_status);
835 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
836 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
837 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
838 RAISE OKC_API.G_EXCEPTION_ERROR;
839 END IF;
840 --- Validate all non-missing attributes (Item Level Validation)
841 l_return_status := Validate_Attributes(l_cvpv_rec);
842 --- If any errors happen abort API
843 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
844 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
845 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
846 RAISE OKC_API.G_EXCEPTION_ERROR;
847 END IF;
848 l_return_status := Validate_Record(l_cvpv_rec);
849 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
850 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
851 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
852 RAISE OKC_API.G_EXCEPTION_ERROR;
853 END IF;
854 x_return_status := l_return_status;
855 EXCEPTION
856 WHEN OKC_API.G_EXCEPTION_ERROR THEN
857 x_return_status := OKC_API.HANDLE_EXCEPTIONS
858 (
859 l_api_name,
860 G_PKG_NAME,
861 'OKC_API.G_RET_STS_ERROR',
862 x_msg_count,
863 x_msg_data,
864 '_PVT'
865 );
866 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
867 x_return_status := OKC_API.HANDLE_EXCEPTIONS
868 (
869 l_api_name,
870 G_PKG_NAME,
871 'OKC_API.G_RET_STS_UNEXP_ERROR',
872 x_msg_count,
873 x_msg_data,
874 '_PVT'
875 );
876 WHEN OTHERS THEN
877 x_return_status := OKC_API.HANDLE_EXCEPTIONS
878 (
879 l_api_name,
880 G_PKG_NAME,
881 'OTHERS',
882 x_msg_count,
883 x_msg_data,
884 '_PVT'
885 );
886 END validate_row;
887 -------------------------------------------------
888 -- PL/SQL TBL validate_row for:OKS_COV_TYPES_V --
889 -------------------------------------------------
890 PROCEDURE validate_row(
891 p_api_version IN NUMBER,
892 p_init_msg_list IN VARCHAR2,
893 x_return_status OUT NOCOPY VARCHAR2,
894 x_msg_count OUT NOCOPY NUMBER,
895 x_msg_data OUT NOCOPY VARCHAR2,
896 p_cvpv_tbl IN cvpv_tbl_type,
897 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
898
899 l_api_version CONSTANT NUMBER := 1;
900 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
901 i NUMBER := 0;
902 BEGIN
903 OKC_API.init_msg_list(p_init_msg_list);
904 -- Make sure PL/SQL table has records in it before passing
905 IF (p_cvpv_tbl.COUNT > 0) THEN
906 i := p_cvpv_tbl.FIRST;
907 LOOP
908 DECLARE
909 l_error_rec OKC_API.ERROR_REC_TYPE;
910 BEGIN
911 l_error_rec.api_name := l_api_name;
912 l_error_rec.api_package := G_PKG_NAME;
913 l_error_rec.idx := i;
914 validate_row (
915 p_api_version => p_api_version,
916 p_init_msg_list => OKC_API.G_FALSE,
917 x_return_status => l_error_rec.error_type,
918 x_msg_count => l_error_rec.msg_count,
919 x_msg_data => l_error_rec.msg_data,
920 p_cvpv_rec => p_cvpv_tbl(i));
921 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
922 l_error_rec.sqlcode := SQLCODE;
923 load_error_tbl(l_error_rec, px_error_tbl);
924 ELSE
925 x_msg_count := l_error_rec.msg_count;
926 x_msg_data := l_error_rec.msg_data;
927 END IF;
928 EXCEPTION
929 WHEN OKC_API.G_EXCEPTION_ERROR THEN
930 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
931 l_error_rec.sqlcode := SQLCODE;
932 load_error_tbl(l_error_rec, px_error_tbl);
933 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
934 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
935 l_error_rec.sqlcode := SQLCODE;
936 load_error_tbl(l_error_rec, px_error_tbl);
937 WHEN OTHERS THEN
938 l_error_rec.error_type := 'OTHERS';
939 l_error_rec.sqlcode := SQLCODE;
940 load_error_tbl(l_error_rec, px_error_tbl);
941 END;
942 EXIT WHEN (i = p_cvpv_tbl.LAST);
943 i := p_cvpv_tbl.NEXT(i);
944 END LOOP;
945 END IF;
946 -- Loop through the error_tbl to find the error with the highest severity
947 -- and return it.
948 x_return_status := find_highest_exception(px_error_tbl);
949 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
950 EXCEPTION
951 WHEN OKC_API.G_EXCEPTION_ERROR THEN
952 x_return_status := OKC_API.HANDLE_EXCEPTIONS
953 (
954 l_api_name,
955 G_PKG_NAME,
956 'OKC_API.G_RET_STS_ERROR',
957 x_msg_count,
958 x_msg_data,
959 '_PVT'
960 );
961 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
962 x_return_status := OKC_API.HANDLE_EXCEPTIONS
963 (
964 l_api_name,
965 G_PKG_NAME,
966 'OKC_API.G_RET_STS_UNEXP_ERROR',
967 x_msg_count,
968 x_msg_data,
969 '_PVT'
970 );
971 WHEN OTHERS THEN
972 x_return_status := OKC_API.HANDLE_EXCEPTIONS
973 (
974 l_api_name,
975 G_PKG_NAME,
976 'OTHERS',
977 x_msg_count,
978 x_msg_data,
979 '_PVT'
980 );
981 END validate_row;
982
983 -------------------------------------------------
984 -- PL/SQL TBL validate_row for:OKS_COV_TYPES_V --
985 -------------------------------------------------
986 PROCEDURE validate_row(
987 p_api_version IN NUMBER,
988 p_init_msg_list IN VARCHAR2,
989 x_return_status OUT NOCOPY VARCHAR2,
990 x_msg_count OUT NOCOPY NUMBER,
991 x_msg_data OUT NOCOPY VARCHAR2,
992 p_cvpv_tbl IN cvpv_tbl_type) IS
993
994 l_api_version CONSTANT NUMBER := 1;
995 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
996 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
997 l_error_tbl OKC_API.ERROR_TBL_TYPE;
998 BEGIN
999 OKC_API.init_msg_list(p_init_msg_list);
1000 -- Make sure PL/SQL table has records in it before passing
1001 IF (p_cvpv_tbl.COUNT > 0) THEN
1002 validate_row (
1003 p_api_version => p_api_version,
1004 p_init_msg_list => OKC_API.G_FALSE,
1005 x_return_status => x_return_status,
1006 x_msg_count => x_msg_count,
1007 x_msg_data => x_msg_data,
1008 p_cvpv_tbl => p_cvpv_tbl,
1009 px_error_tbl => l_error_tbl);
1010 END IF;
1011 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1012 EXCEPTION
1013 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1014 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1015 (
1016 l_api_name,
1017 G_PKG_NAME,
1018 'OKC_API.G_RET_STS_ERROR',
1019 x_msg_count,
1020 x_msg_data,
1021 '_PVT'
1022 );
1023 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1024 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1025 (
1026 l_api_name,
1027 G_PKG_NAME,
1028 'OKC_API.G_RET_STS_UNEXP_ERROR',
1029 x_msg_count,
1030 x_msg_data,
1031 '_PVT'
1032 );
1033 WHEN OTHERS THEN
1034 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1035 (
1036 l_api_name,
1037 G_PKG_NAME,
1038 'OTHERS',
1039 x_msg_count,
1040 x_msg_data,
1041 '_PVT'
1042 );
1043 END validate_row;
1044
1045 ---------------------------------------------------------------------------
1046 -- PROCEDURE insert_row
1047 ---------------------------------------------------------------------------
1048 ------------------------------------
1049 -- insert_row for:OKS_COV_TYPES_B --
1050 ------------------------------------
1051 PROCEDURE insert_row(
1052 p_init_msg_list IN VARCHAR2,
1053 x_return_status OUT NOCOPY VARCHAR2,
1054 x_msg_count OUT NOCOPY NUMBER,
1055 x_msg_data OUT NOCOPY VARCHAR2,
1056 p_cvp_rec IN cvp_rec_type,
1057 x_cvp_rec OUT NOCOPY cvp_rec_type) IS
1058
1059 l_api_version CONSTANT NUMBER := 1;
1060 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1061 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1062 l_cvp_rec cvp_rec_type := p_cvp_rec;
1063 l_def_cvp_rec cvp_rec_type;
1064 ----------------------------------------
1065 -- Set_Attributes for:OKS_COV_TYPES_B --
1066 ----------------------------------------
1067 FUNCTION Set_Attributes (
1068 p_cvp_rec IN cvp_rec_type,
1069 x_cvp_rec OUT NOCOPY cvp_rec_type
1070 ) RETURN VARCHAR2 IS
1071 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1072 BEGIN
1073 x_cvp_rec := p_cvp_rec;
1074 RETURN(l_return_status);
1075 END Set_Attributes;
1076 BEGIN
1077 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1078 p_init_msg_list,
1079 '_PVT',
1080 x_return_status);
1081 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1082 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1083 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1084 RAISE OKC_API.G_EXCEPTION_ERROR;
1085 END IF;
1086 --- Setting item atributes
1087 l_return_status := Set_Attributes(
1088 p_cvp_rec, -- IN
1089 l_cvp_rec); -- OUT
1090 --- If any errors happen abort API
1091 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1092 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1093 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1094 RAISE OKC_API.G_EXCEPTION_ERROR;
1095 END IF;
1096 INSERT INTO OKS_COV_TYPES_B(
1097 code,
1098 importance_level,
1099 enabled_flag,
1100 start_date_active,
1101 end_date_active,
1102 created_by,
1103 creation_date,
1104 last_updated_by,
1105 last_update_login,
1106 last_update_date,
1107 attribute_category,
1108 attribute1,
1109 attribute2,
1110 attribute3,
1111 attribute4,
1112 attribute5,
1113 attribute6,
1114 attribute7,
1115 attribute8,
1116 attribute9,
1117 attribute10,
1118 attribute11,
1119 attribute12,
1120 attribute13,
1121 attribute14,
1122 attribute15)
1123 VALUES (
1124 l_cvp_rec.code,
1125 l_cvp_rec.importance_level,
1126 l_cvp_rec.enabled_flag,
1127 l_cvp_rec.start_date_active,
1128 l_cvp_rec.end_date_active,
1129 l_cvp_rec.created_by,
1130 l_cvp_rec.creation_date,
1131 l_cvp_rec.last_updated_by,
1132 l_cvp_rec.last_update_login,
1133 l_cvp_rec.last_update_date,
1134 l_cvp_rec.attribute_category,
1135 l_cvp_rec.attribute1,
1136 l_cvp_rec.attribute2,
1137 l_cvp_rec.attribute3,
1138 l_cvp_rec.attribute4,
1139 l_cvp_rec.attribute5,
1140 l_cvp_rec.attribute6,
1141 l_cvp_rec.attribute7,
1142 l_cvp_rec.attribute8,
1143 l_cvp_rec.attribute9,
1144 l_cvp_rec.attribute10,
1145 l_cvp_rec.attribute11,
1146 l_cvp_rec.attribute12,
1147 l_cvp_rec.attribute13,
1148 l_cvp_rec.attribute14,
1149 l_cvp_rec.attribute15);
1150 -- Set OUT values
1151 x_cvp_rec := l_cvp_rec;
1152 x_return_status := l_return_status;
1153 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1154 EXCEPTION
1155 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1156 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1157 (
1158 l_api_name,
1159 G_PKG_NAME,
1160 'OKC_API.G_RET_STS_ERROR',
1161 x_msg_count,
1162 x_msg_data,
1163 '_PVT'
1164 );
1165 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1166 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1167 (
1168 l_api_name,
1169 G_PKG_NAME,
1170 'OKC_API.G_RET_STS_UNEXP_ERROR',
1171 x_msg_count,
1172 x_msg_data,
1173 '_PVT'
1174 );
1175 WHEN OTHERS THEN
1176 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1177 (
1178 l_api_name,
1179 G_PKG_NAME,
1180 'OTHERS',
1181 x_msg_count,
1182 x_msg_data,
1183 '_PVT'
1184 );
1185 END insert_row;
1186 -------------------------------------
1187 -- insert_row for:OKS_COV_TYPES_TL --
1188 -------------------------------------
1189 PROCEDURE insert_row(
1190 p_init_msg_list IN VARCHAR2,
1191 x_return_status OUT NOCOPY VARCHAR2,
1192 x_msg_count OUT NOCOPY NUMBER,
1193 x_msg_data OUT NOCOPY VARCHAR2,
1194 p_oks_cov_types_tl_rec IN oks_cov_types_tl_rec_type,
1195 x_oks_cov_types_tl_rec OUT NOCOPY oks_cov_types_tl_rec_type) IS
1196
1197 l_api_version CONSTANT NUMBER := 1;
1198 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
1199 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1200 l_oks_cov_types_tl_rec oks_cov_types_tl_rec_type := p_oks_cov_types_tl_rec;
1201 l_def_oks_cov_types_tl_rec oks_cov_types_tl_rec_type;
1202 CURSOR get_languages IS
1203 SELECT *
1204 FROM FND_LANGUAGES
1205 WHERE INSTALLED_FLAG IN ('I', 'B');
1206 -----------------------------------------
1207 -- Set_Attributes for:OKS_COV_TYPES_TL --
1208 -----------------------------------------
1209 FUNCTION Set_Attributes (
1210 p_oks_cov_types_tl_rec IN oks_cov_types_tl_rec_type,
1211 x_oks_cov_types_tl_rec OUT NOCOPY oks_cov_types_tl_rec_type
1212 ) RETURN VARCHAR2 IS
1213 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1214 BEGIN
1215 x_oks_cov_types_tl_rec := p_oks_cov_types_tl_rec;
1216 x_oks_cov_types_tl_rec.LANGUAGE := USERENV('LANG');
1217 x_oks_cov_types_tl_rec.SOURCE_LANG := USERENV('LANG');
1218 RETURN(l_return_status);
1219 END Set_Attributes;
1220 BEGIN
1221 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1222 p_init_msg_list,
1223 '_PVT',
1224 x_return_status);
1225 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1226 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1227 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1228 RAISE OKC_API.G_EXCEPTION_ERROR;
1229 END IF;
1230 --- Setting item attributes
1231 l_return_status := Set_Attributes(
1232 p_oks_cov_types_tl_rec, -- IN
1233 l_oks_cov_types_tl_rec); -- OUT
1234 --- If any errors happen abort API
1235 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1236 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1237 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1238 RAISE OKC_API.G_EXCEPTION_ERROR;
1239 END IF;
1240 FOR l_lang_rec IN get_languages LOOP
1241 l_oks_cov_types_tl_rec.language := l_lang_rec.language_code;
1242 INSERT INTO OKS_COV_TYPES_TL(
1243 code,
1244 language,
1245 source_lang,
1246 sfwt_flag,
1247 meaning,
1248 description,
1249 created_by,
1250 creation_date,
1251 last_updated_by,
1252 last_update_login,
1253 last_update_date)
1254 VALUES (
1255 l_oks_cov_types_tl_rec.code,
1256 l_oks_cov_types_tl_rec.language,
1257 l_oks_cov_types_tl_rec.source_lang,
1258 l_oks_cov_types_tl_rec.sfwt_flag,
1259 l_oks_cov_types_tl_rec.meaning,
1260 l_oks_cov_types_tl_rec.description,
1261 l_oks_cov_types_tl_rec.created_by,
1262 l_oks_cov_types_tl_rec.creation_date,
1263 l_oks_cov_types_tl_rec.last_updated_by,
1264 l_oks_cov_types_tl_rec.last_update_login,
1265 l_oks_cov_types_tl_rec.last_update_date);
1266 END LOOP;
1267 -- Set OUT values
1268 x_oks_cov_types_tl_rec := l_oks_cov_types_tl_rec;
1269 x_return_status := l_return_status;
1270 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1271 EXCEPTION
1272 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1273 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1274 (
1275 l_api_name,
1276 G_PKG_NAME,
1277 'OKC_API.G_RET_STS_ERROR',
1278 x_msg_count,
1279 x_msg_data,
1280 '_PVT'
1281 );
1282 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1283 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1284 (
1285 l_api_name,
1286 G_PKG_NAME,
1287 'OKC_API.G_RET_STS_UNEXP_ERROR',
1288 x_msg_count,
1289 x_msg_data,
1290 '_PVT'
1291 );
1292 WHEN OTHERS THEN
1293 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1294 (
1295 l_api_name,
1296 G_PKG_NAME,
1297 'OTHERS',
1298 x_msg_count,
1299 x_msg_data,
1300 '_PVT'
1301 );
1302 END insert_row;
1303 -------------------------------------
1304 -- insert_row for :OKS_COV_TYPES_V --
1305 -------------------------------------
1306 PROCEDURE insert_row(
1307 p_api_version IN NUMBER,
1308 p_init_msg_list IN VARCHAR2,
1309 x_return_status OUT NOCOPY VARCHAR2,
1310 x_msg_count OUT NOCOPY NUMBER,
1311 x_msg_data OUT NOCOPY VARCHAR2,
1312 p_cvpv_rec IN cvpv_rec_type,
1313 x_cvpv_rec OUT NOCOPY cvpv_rec_type) IS
1314
1315 l_api_version CONSTANT NUMBER := 1;
1316 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1317 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1318 l_cvpv_rec cvpv_rec_type := p_cvpv_rec;
1319 l_def_cvpv_rec cvpv_rec_type;
1320 l_cvp_rec cvp_rec_type;
1321 lx_cvp_rec cvp_rec_type;
1322 l_oks_cov_types_tl_rec oks_cov_types_tl_rec_type;
1323 lx_oks_cov_types_tl_rec oks_cov_types_tl_rec_type;
1324 -------------------------------
1325 -- FUNCTION fill_who_columns --
1326 -------------------------------
1327 FUNCTION fill_who_columns (
1328 p_cvpv_rec IN cvpv_rec_type
1329 ) RETURN cvpv_rec_type IS
1330 l_cvpv_rec cvpv_rec_type := p_cvpv_rec;
1331 BEGIN
1332 l_cvpv_rec.CREATION_DATE := SYSDATE;
1333 l_cvpv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1334 l_cvpv_rec.LAST_UPDATE_DATE := l_cvpv_rec.CREATION_DATE;
1335 l_cvpv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1336 l_cvpv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1337 RETURN(l_cvpv_rec);
1338 END fill_who_columns;
1339 ----------------------------------------
1340 -- Set_Attributes for:OKS_COV_TYPES_V --
1341 ----------------------------------------
1342 FUNCTION Set_Attributes (
1343 p_cvpv_rec IN cvpv_rec_type,
1344 x_cvpv_rec OUT NOCOPY cvpv_rec_type
1345 ) RETURN VARCHAR2 IS
1346 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1347 BEGIN
1348 x_cvpv_rec := p_cvpv_rec;
1349 x_cvpv_rec.SFWT_FLAG := 'N';
1350 RETURN(l_return_status);
1351 END Set_Attributes;
1352 BEGIN
1353 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1354 G_PKG_NAME,
1355 p_init_msg_list,
1356 l_api_version,
1357 p_api_version,
1358 '_PVT',
1359 x_return_status);
1360 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1361 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1362 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1363 RAISE OKC_API.G_EXCEPTION_ERROR;
1364 END IF;
1365 l_cvpv_rec := null_out_defaults(p_cvpv_rec);
1366 -- Set primary key value
1367 -- Error: Primary Key Column "CODE"
1368 -- Does not have a NUMBER datatype, cannot assign get_seq_id
1369 -- Setting item attributes
1370 l_return_Status := Set_Attributes(
1371 l_cvpv_rec, -- IN
1372 l_def_cvpv_rec); -- OUT
1373 --- If any errors happen abort API
1374 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1375 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1376 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1377 RAISE OKC_API.G_EXCEPTION_ERROR;
1378 END IF;
1379 l_def_cvpv_rec := fill_who_columns(l_def_cvpv_rec);
1380 --- Validate all non-missing attributes (Item Level Validation)
1381 l_return_status := Validate_Attributes(l_def_cvpv_rec);
1382 --- If any errors happen abort API
1383 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1384 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1385 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1386 RAISE OKC_API.G_EXCEPTION_ERROR;
1387 END IF;
1388 l_return_status := Validate_Record(l_def_cvpv_rec);
1389 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1390 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1391 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1392 RAISE OKC_API.G_EXCEPTION_ERROR;
1393 END IF;
1394 -----------------------------------------
1395 -- Move VIEW record to "Child" records --
1396 -----------------------------------------
1397 migrate(l_def_cvpv_rec, l_cvp_rec);
1398 migrate(l_def_cvpv_rec, l_oks_cov_types_tl_rec);
1399 -----------------------------------------------
1400 -- Call the INSERT_ROW for each child record --
1401 -----------------------------------------------
1402 insert_row(
1403 p_init_msg_list,
1404 l_return_status,
1405 x_msg_count,
1406 x_msg_data,
1407 l_cvp_rec,
1408 lx_cvp_rec
1409 );
1410 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1411 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1412 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1413 RAISE OKC_API.G_EXCEPTION_ERROR;
1414 END IF;
1415 migrate(lx_cvp_rec, l_def_cvpv_rec);
1416 insert_row(
1417 p_init_msg_list,
1418 l_return_status,
1419 x_msg_count,
1420 x_msg_data,
1421 l_oks_cov_types_tl_rec,
1422 lx_oks_cov_types_tl_rec
1423 );
1424 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1425 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1426 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1427 RAISE OKC_API.G_EXCEPTION_ERROR;
1428 END IF;
1429 migrate(lx_oks_cov_types_tl_rec, l_def_cvpv_rec);
1430 -- Set OUT values
1431 x_cvpv_rec := l_def_cvpv_rec;
1432 x_return_status := l_return_status;
1433 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1434 EXCEPTION
1435 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1436 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1437 (
1438 l_api_name,
1439 G_PKG_NAME,
1440 'OKC_API.G_RET_STS_ERROR',
1441 x_msg_count,
1442 x_msg_data,
1443 '_PVT'
1444 );
1445 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1446 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1447 (
1448 l_api_name,
1449 G_PKG_NAME,
1450 'OKC_API.G_RET_STS_UNEXP_ERROR',
1451 x_msg_count,
1452 x_msg_data,
1453 '_PVT'
1454 );
1455 WHEN OTHERS THEN
1456 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1457 (
1458 l_api_name,
1459 G_PKG_NAME,
1460 'OTHERS',
1461 x_msg_count,
1462 x_msg_data,
1463 '_PVT'
1464 );
1465 END insert_row;
1466 ----------------------------------------
1467 -- PL/SQL TBL insert_row for:CVPV_TBL --
1468 ----------------------------------------
1469 PROCEDURE insert_row(
1470 p_api_version IN NUMBER,
1471 p_init_msg_list IN VARCHAR2,
1472 x_return_status OUT NOCOPY VARCHAR2,
1473 x_msg_count OUT NOCOPY NUMBER,
1474 x_msg_data OUT NOCOPY VARCHAR2,
1475 p_cvpv_tbl IN cvpv_tbl_type,
1476 x_cvpv_tbl OUT NOCOPY cvpv_tbl_type,
1477 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
1478
1479 l_api_version CONSTANT NUMBER := 1;
1480 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
1481 i NUMBER := 0;
1482 BEGIN
1483 OKC_API.init_msg_list(p_init_msg_list);
1484 -- Make sure PL/SQL table has records in it before passing
1485 IF (p_cvpv_tbl.COUNT > 0) THEN
1486 i := p_cvpv_tbl.FIRST;
1487 LOOP
1488 DECLARE
1489 l_error_rec OKC_API.ERROR_REC_TYPE;
1490 BEGIN
1491 l_error_rec.api_name := l_api_name;
1492 l_error_rec.api_package := G_PKG_NAME;
1493 l_error_rec.idx := i;
1494 insert_row (
1495 p_api_version => p_api_version,
1496 p_init_msg_list => OKC_API.G_FALSE,
1497 x_return_status => l_error_rec.error_type,
1498 x_msg_count => l_error_rec.msg_count,
1499 x_msg_data => l_error_rec.msg_data,
1500 p_cvpv_rec => p_cvpv_tbl(i),
1501 x_cvpv_rec => x_cvpv_tbl(i));
1502 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
1503 l_error_rec.sqlcode := SQLCODE;
1504 load_error_tbl(l_error_rec, px_error_tbl);
1505 ELSE
1506 x_msg_count := l_error_rec.msg_count;
1507 x_msg_data := l_error_rec.msg_data;
1508 END IF;
1509 EXCEPTION
1510 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1511 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
1512 l_error_rec.sqlcode := SQLCODE;
1513 load_error_tbl(l_error_rec, px_error_tbl);
1514 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1515 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
1516 l_error_rec.sqlcode := SQLCODE;
1517 load_error_tbl(l_error_rec, px_error_tbl);
1518 WHEN OTHERS THEN
1519 l_error_rec.error_type := 'OTHERS';
1520 l_error_rec.sqlcode := SQLCODE;
1521 load_error_tbl(l_error_rec, px_error_tbl);
1522 END;
1523 EXIT WHEN (i = p_cvpv_tbl.LAST);
1524 i := p_cvpv_tbl.NEXT(i);
1525 END LOOP;
1526 END IF;
1527 -- Loop through the error_tbl to find the error with the highest severity
1528 -- and return it.
1529 x_return_status := find_highest_exception(px_error_tbl);
1530 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1531 EXCEPTION
1532 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1533 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1534 (
1535 l_api_name,
1536 G_PKG_NAME,
1537 'OKC_API.G_RET_STS_ERROR',
1538 x_msg_count,
1539 x_msg_data,
1540 '_PVT'
1541 );
1542 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1543 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1544 (
1545 l_api_name,
1546 G_PKG_NAME,
1547 'OKC_API.G_RET_STS_UNEXP_ERROR',
1548 x_msg_count,
1549 x_msg_data,
1550 '_PVT'
1551 );
1552 WHEN OTHERS THEN
1553 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1554 (
1555 l_api_name,
1556 G_PKG_NAME,
1557 'OTHERS',
1558 x_msg_count,
1559 x_msg_data,
1560 '_PVT'
1561 );
1562 END insert_row;
1563
1564 ----------------------------------------
1565 -- PL/SQL TBL insert_row for:CVPV_TBL --
1566 ----------------------------------------
1567 -- This procedure is the same as the one above except it does not have a "px_error_tbl" argument.
1568 -- This procedure was create for backward compatibility and simply is a wrapper for the one above.
1569 PROCEDURE insert_row(
1570 p_api_version IN NUMBER,
1571 p_init_msg_list IN VARCHAR2,
1572 x_return_status OUT NOCOPY VARCHAR2,
1573 x_msg_count OUT NOCOPY NUMBER,
1574 x_msg_data OUT NOCOPY VARCHAR2,
1575 p_cvpv_tbl IN cvpv_tbl_type,
1576 x_cvpv_tbl OUT NOCOPY cvpv_tbl_type) IS
1577
1578 l_api_version CONSTANT NUMBER := 1;
1579 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1580 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1581 l_error_tbl OKC_API.ERROR_TBL_TYPE;
1582 BEGIN
1583 OKC_API.init_msg_list(p_init_msg_list);
1584 -- Make sure PL/SQL table has records in it before passing
1585 IF (p_cvpv_tbl.COUNT > 0) THEN
1586 insert_row (
1587 p_api_version => p_api_version,
1588 p_init_msg_list => OKC_API.G_FALSE,
1589 x_return_status => x_return_status,
1590 x_msg_count => x_msg_count,
1591 x_msg_data => x_msg_data,
1592 p_cvpv_tbl => p_cvpv_tbl,
1593 x_cvpv_tbl => x_cvpv_tbl,
1594 px_error_tbl => l_error_tbl);
1595 END IF;
1596 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1597 EXCEPTION
1598 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1599 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1600 (
1601 l_api_name,
1602 G_PKG_NAME,
1603 'OKC_API.G_RET_STS_ERROR',
1604 x_msg_count,
1605 x_msg_data,
1606 '_PVT'
1607 );
1608 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1609 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1610 (
1611 l_api_name,
1612 G_PKG_NAME,
1613 'OKC_API.G_RET_STS_UNEXP_ERROR',
1614 x_msg_count,
1615 x_msg_data,
1616 '_PVT'
1617 );
1618 WHEN OTHERS THEN
1619 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1620 (
1621 l_api_name,
1622 G_PKG_NAME,
1623 'OTHERS',
1624 x_msg_count,
1625 x_msg_data,
1626 '_PVT'
1627 );
1628 END insert_row;
1629
1630 ---------------------------------------------------------------------------
1631 -- PROCEDURE lock_row
1632 ---------------------------------------------------------------------------
1633 ----------------------------------
1634 -- lock_row for:OKS_COV_TYPES_B --
1635 ----------------------------------
1636 PROCEDURE lock_row(
1637 p_init_msg_list IN VARCHAR2,
1638 x_return_status OUT NOCOPY VARCHAR2,
1639 x_msg_count OUT NOCOPY NUMBER,
1640 x_msg_data OUT NOCOPY VARCHAR2,
1641 p_cvp_rec IN cvp_rec_type) IS
1642
1643 E_Resource_Busy EXCEPTION;
1644 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1645 CURSOR lock_csr (p_cvp_rec IN cvp_rec_type) IS
1646 SELECT *
1647 FROM OKS_COV_TYPES_B
1648 WHERE CODE = p_cvp_rec.code
1649 FOR UPDATE NOWAIT;
1650
1651 l_api_version CONSTANT NUMBER := 1;
1652 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1653 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1654 l_lock_var lock_csr%ROWTYPE;
1655 l_row_notfound BOOLEAN := FALSE;
1656 lc_row_notfound BOOLEAN := FALSE;
1657 BEGIN
1658 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1659 p_init_msg_list,
1660 '_PVT',
1661 x_return_status);
1662 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1663 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1664 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1665 RAISE OKC_API.G_EXCEPTION_ERROR;
1666 END IF;
1667 BEGIN
1668 OPEN lock_csr(p_cvp_rec);
1669 FETCH lock_csr INTO l_lock_var;
1670 l_row_notfound := lock_csr%NOTFOUND;
1671 CLOSE lock_csr;
1672 EXCEPTION
1673 WHEN E_Resource_Busy THEN
1674 IF (lock_csr%ISOPEN) THEN
1675 CLOSE lock_csr;
1676 END IF;
1677 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1678 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1679 END;
1680
1681 IF ( l_row_notfound ) THEN
1682 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1683 RAISE OKC_API.G_EXCEPTION_ERROR;
1684 ELSE
1685 IF (l_lock_var.code <> p_cvp_rec.code) THEN
1686 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1687 RAISE OKC_API.G_EXCEPTION_ERROR;
1688 END IF;
1689 IF (l_lock_var.importance_level <> p_cvp_rec.importance_level) THEN
1690 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1691 RAISE OKC_API.G_EXCEPTION_ERROR;
1692 END IF;
1693 IF (l_lock_var.enabled_flag <> p_cvp_rec.enabled_flag) THEN
1694 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1695 RAISE OKC_API.G_EXCEPTION_ERROR;
1696 END IF;
1697 IF (l_lock_var.start_date_active <> p_cvp_rec.start_date_active) THEN
1698 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1699 RAISE OKC_API.G_EXCEPTION_ERROR;
1700 END IF;
1701 IF (l_lock_var.end_date_active <> p_cvp_rec.end_date_active) THEN
1702 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1703 RAISE OKC_API.G_EXCEPTION_ERROR;
1704 END IF;
1705 IF (l_lock_var.created_by <> p_cvp_rec.created_by) THEN
1706 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1707 RAISE OKC_API.G_EXCEPTION_ERROR;
1708 END IF;
1709 IF (l_lock_var.creation_date <> p_cvp_rec.creation_date) THEN
1710 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1711 RAISE OKC_API.G_EXCEPTION_ERROR;
1712 END IF;
1713 IF (l_lock_var.last_updated_by <> p_cvp_rec.last_updated_by) THEN
1714 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1715 RAISE OKC_API.G_EXCEPTION_ERROR;
1716 END IF;
1717 IF (l_lock_var.last_update_login <> p_cvp_rec.last_update_login) THEN
1718 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1719 RAISE OKC_API.G_EXCEPTION_ERROR;
1720 END IF;
1721 IF (l_lock_var.last_update_date <> p_cvp_rec.last_update_date) THEN
1722 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1723 RAISE OKC_API.G_EXCEPTION_ERROR;
1724 END IF;
1725 IF (l_lock_var.attribute_category <> p_cvp_rec.attribute_category) THEN
1726 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1727 RAISE OKC_API.G_EXCEPTION_ERROR;
1728 END IF;
1729 IF (l_lock_var.attribute1 <> p_cvp_rec.attribute1) THEN
1730 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1731 RAISE OKC_API.G_EXCEPTION_ERROR;
1732 END IF;
1733 IF (l_lock_var.attribute2 <> p_cvp_rec.attribute2) THEN
1734 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1735 RAISE OKC_API.G_EXCEPTION_ERROR;
1736 END IF;
1737 IF (l_lock_var.attribute3 <> p_cvp_rec.attribute3) THEN
1738 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1739 RAISE OKC_API.G_EXCEPTION_ERROR;
1740 END IF;
1741 IF (l_lock_var.attribute4 <> p_cvp_rec.attribute4) THEN
1742 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1743 RAISE OKC_API.G_EXCEPTION_ERROR;
1744 END IF;
1745 IF (l_lock_var.attribute5 <> p_cvp_rec.attribute5) THEN
1746 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1747 RAISE OKC_API.G_EXCEPTION_ERROR;
1748 END IF;
1749 IF (l_lock_var.attribute6 <> p_cvp_rec.attribute6) THEN
1750 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1751 RAISE OKC_API.G_EXCEPTION_ERROR;
1752 END IF;
1753 IF (l_lock_var.attribute7 <> p_cvp_rec.attribute7) THEN
1754 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1755 RAISE OKC_API.G_EXCEPTION_ERROR;
1756 END IF;
1757 IF (l_lock_var.attribute8 <> p_cvp_rec.attribute8) THEN
1758 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1759 RAISE OKC_API.G_EXCEPTION_ERROR;
1760 END IF;
1761 IF (l_lock_var.attribute9 <> p_cvp_rec.attribute9) THEN
1762 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1763 RAISE OKC_API.G_EXCEPTION_ERROR;
1764 END IF;
1765 IF (l_lock_var.attribute10 <> p_cvp_rec.attribute10) THEN
1766 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1767 RAISE OKC_API.G_EXCEPTION_ERROR;
1768 END IF;
1769 IF (l_lock_var.attribute11 <> p_cvp_rec.attribute11) THEN
1770 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1771 RAISE OKC_API.G_EXCEPTION_ERROR;
1772 END IF;
1773 IF (l_lock_var.attribute12 <> p_cvp_rec.attribute12) THEN
1774 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1775 RAISE OKC_API.G_EXCEPTION_ERROR;
1776 END IF;
1777 IF (l_lock_var.attribute13 <> p_cvp_rec.attribute13) THEN
1778 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1779 RAISE OKC_API.G_EXCEPTION_ERROR;
1780 END IF;
1781 IF (l_lock_var.attribute14 <> p_cvp_rec.attribute14) THEN
1782 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1783 RAISE OKC_API.G_EXCEPTION_ERROR;
1784 END IF;
1785 IF (l_lock_var.attribute15 <> p_cvp_rec.attribute15) THEN
1786 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1787 RAISE OKC_API.G_EXCEPTION_ERROR;
1788 END IF;
1789 END IF;
1790 x_return_status := l_return_status;
1791 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1792 EXCEPTION
1793 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1794 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1795 (
1796 l_api_name,
1797 G_PKG_NAME,
1798 'OKC_API.G_RET_STS_ERROR',
1799 x_msg_count,
1800 x_msg_data,
1801 '_PVT'
1802 );
1803 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1804 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1805 (
1806 l_api_name,
1807 G_PKG_NAME,
1808 'OKC_API.G_RET_STS_UNEXP_ERROR',
1809 x_msg_count,
1810 x_msg_data,
1811 '_PVT'
1812 );
1813 WHEN OTHERS THEN
1814 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1815 (
1816 l_api_name,
1817 G_PKG_NAME,
1818 'OTHERS',
1819 x_msg_count,
1820 x_msg_data,
1821 '_PVT'
1822 );
1823 END lock_row;
1824 -----------------------------------
1825 -- lock_row for:OKS_COV_TYPES_TL --
1826 -----------------------------------
1827 PROCEDURE lock_row(
1828 p_init_msg_list IN VARCHAR2,
1829 x_return_status OUT NOCOPY VARCHAR2,
1830 x_msg_count OUT NOCOPY NUMBER,
1831 x_msg_data OUT NOCOPY VARCHAR2,
1832 p_oks_cov_types_tl_rec IN oks_cov_types_tl_rec_type) IS
1833
1834 E_Resource_Busy EXCEPTION;
1835 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1836 CURSOR lock_csr (p_oks_cov_types_tl_rec IN oks_cov_types_tl_rec_type) IS
1837 SELECT *
1838 FROM OKS_COV_TYPES_TL
1839 WHERE CODE = p_oks_cov_types_tl_rec.code
1840 FOR UPDATE NOWAIT;
1841
1842 l_api_version CONSTANT NUMBER := 1;
1843 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
1844 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1845 l_lock_var lock_csr%ROWTYPE;
1846 l_row_notfound BOOLEAN := FALSE;
1847 lc_row_notfound BOOLEAN := FALSE;
1848 BEGIN
1849 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1850 p_init_msg_list,
1851 '_PVT',
1852 x_return_status);
1853 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1854 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1855 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1856 RAISE OKC_API.G_EXCEPTION_ERROR;
1857 END IF;
1858 BEGIN
1859 OPEN lock_csr(p_oks_cov_types_tl_rec);
1860 FETCH lock_csr INTO l_lock_var;
1861 l_row_notfound := lock_csr%NOTFOUND;
1862 CLOSE lock_csr;
1863 EXCEPTION
1864 WHEN E_Resource_Busy THEN
1865 IF (lock_csr%ISOPEN) THEN
1866 CLOSE lock_csr;
1867 END IF;
1868 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1869 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1870 END;
1871
1872 IF ( l_row_notfound ) THEN
1873 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1874 RAISE OKC_API.G_EXCEPTION_ERROR;
1875 END IF;
1876 x_return_status := l_return_status;
1877 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1878 EXCEPTION
1879 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1880 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1881 (
1882 l_api_name,
1883 G_PKG_NAME,
1884 'OKC_API.G_RET_STS_ERROR',
1885 x_msg_count,
1886 x_msg_data,
1887 '_PVT'
1888 );
1889 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1890 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1891 (
1892 l_api_name,
1893 G_PKG_NAME,
1894 'OKC_API.G_RET_STS_UNEXP_ERROR',
1895 x_msg_count,
1896 x_msg_data,
1897 '_PVT'
1898 );
1899 WHEN OTHERS THEN
1900 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1901 (
1902 l_api_name,
1903 G_PKG_NAME,
1904 'OTHERS',
1905 x_msg_count,
1906 x_msg_data,
1907 '_PVT'
1908 );
1909 END lock_row;
1910 -----------------------------------
1911 -- lock_row for: OKS_COV_TYPES_V --
1912 -----------------------------------
1913 PROCEDURE lock_row(
1914 p_api_version IN NUMBER,
1915 p_init_msg_list IN VARCHAR2,
1916 x_return_status OUT NOCOPY VARCHAR2,
1917 x_msg_count OUT NOCOPY NUMBER,
1918 x_msg_data OUT NOCOPY VARCHAR2,
1919 p_cvpv_rec IN cvpv_rec_type) IS
1920
1921 l_api_version CONSTANT NUMBER := 1;
1922 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1923 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1924 l_oks_cov_types_tl_rec oks_cov_types_tl_rec_type;
1925 l_cvp_rec cvp_rec_type;
1926 BEGIN
1927 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1928 G_PKG_NAME,
1929 p_init_msg_list,
1930 l_api_version,
1931 p_api_version,
1932 '_PVT',
1933 x_return_status);
1934 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1935 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1936 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1937 RAISE OKC_API.G_EXCEPTION_ERROR;
1938 END IF;
1939 -----------------------------------------
1940 -- Move VIEW record to "Child" records --
1941 -----------------------------------------
1942 migrate(p_cvpv_rec, l_oks_cov_types_tl_rec);
1943 migrate(p_cvpv_rec, l_cvp_rec);
1944 ---------------------------------------------
1945 -- Call the LOCK_ROW for each child record --
1946 ---------------------------------------------
1947 lock_row(
1948 p_init_msg_list,
1949 l_return_status,
1950 x_msg_count,
1951 x_msg_data,
1952 l_oks_cov_types_tl_rec
1953 );
1954 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1955 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1956 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1957 RAISE OKC_API.G_EXCEPTION_ERROR;
1958 END IF;
1959 lock_row(
1960 p_init_msg_list,
1961 l_return_status,
1962 x_msg_count,
1963 x_msg_data,
1964 l_cvp_rec
1965 );
1966 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1967 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1968 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1969 RAISE OKC_API.G_EXCEPTION_ERROR;
1970 END IF;
1971 x_return_status := l_return_status;
1972 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1973 EXCEPTION
1974 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1975 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1976 (
1977 l_api_name,
1978 G_PKG_NAME,
1979 'OKC_API.G_RET_STS_ERROR',
1980 x_msg_count,
1981 x_msg_data,
1982 '_PVT'
1983 );
1984 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1985 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1986 (
1987 l_api_name,
1988 G_PKG_NAME,
1989 'OKC_API.G_RET_STS_UNEXP_ERROR',
1990 x_msg_count,
1991 x_msg_data,
1992 '_PVT'
1993 );
1994 WHEN OTHERS THEN
1995 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1996 (
1997 l_api_name,
1998 G_PKG_NAME,
1999 'OTHERS',
2000 x_msg_count,
2001 x_msg_data,
2002 '_PVT'
2003 );
2004 END lock_row;
2005 --------------------------------------
2006 -- PL/SQL TBL lock_row for:CVPV_TBL --
2007 --------------------------------------
2008 PROCEDURE lock_row(
2009 p_api_version IN NUMBER,
2010 p_init_msg_list IN VARCHAR2,
2011 x_return_status OUT NOCOPY VARCHAR2,
2012 x_msg_count OUT NOCOPY NUMBER,
2013 x_msg_data OUT NOCOPY VARCHAR2,
2014 p_cvpv_tbl IN cvpv_tbl_type,
2015 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2016
2017 l_api_version CONSTANT NUMBER := 1;
2018 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
2019 i NUMBER := 0;
2020 BEGIN
2021 OKC_API.init_msg_list(p_init_msg_list);
2022 -- Make sure PL/SQL table has recrods in it before passing
2023 IF (p_cvpv_tbl.COUNT > 0) THEN
2024 i := p_cvpv_tbl.FIRST;
2025 LOOP
2026 DECLARE
2027 l_error_rec OKC_API.ERROR_REC_TYPE;
2028 BEGIN
2029 l_error_rec.api_name := l_api_name;
2030 l_error_rec.api_package := G_PKG_NAME;
2031 l_error_rec.idx := i;
2032 lock_row(
2033 p_api_version => p_api_version,
2034 p_init_msg_list => OKC_API.G_FALSE,
2035 x_return_status => l_error_rec.error_type,
2036 x_msg_count => l_error_rec.msg_count,
2037 x_msg_data => l_error_rec.msg_data,
2038 p_cvpv_rec => p_cvpv_tbl(i));
2039 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2040 l_error_rec.sqlcode := SQLCODE;
2041 load_error_tbl(l_error_rec, px_error_tbl);
2042 ELSE
2043 x_msg_count := l_error_rec.msg_count;
2044 x_msg_data := l_error_rec.msg_data;
2045 END IF;
2046 EXCEPTION
2047 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2048 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2049 l_error_rec.sqlcode := SQLCODE;
2050 load_error_tbl(l_error_rec, px_error_tbl);
2051 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2052 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2053 l_error_rec.sqlcode := SQLCODE;
2054 load_error_tbl(l_error_rec, px_error_tbl);
2055 WHEN OTHERS THEN
2056 l_error_rec.error_type := 'OTHERS';
2057 l_error_rec.sqlcode := SQLCODE;
2058 load_error_tbl(l_error_rec, px_error_tbl);
2059 END;
2060 EXIT WHEN (i = p_cvpv_tbl.LAST);
2061 i := p_cvpv_tbl.NEXT(i);
2062 END LOOP;
2063 END IF;
2064 -- Loop through the error_tbl to find the error with the highest severity
2065 -- and return it.
2066 x_return_status := find_highest_exception(px_error_tbl);
2067 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2068 EXCEPTION
2069 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2070 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2071 (
2072 l_api_name,
2073 G_PKG_NAME,
2074 'OKC_API.G_RET_STS_ERROR',
2075 x_msg_count,
2076 x_msg_data,
2077 '_PVT'
2078 );
2079 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2080 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2081 (
2082 l_api_name,
2083 G_PKG_NAME,
2084 'OKC_API.G_RET_STS_UNEXP_ERROR',
2085 x_msg_count,
2086 x_msg_data,
2087 '_PVT'
2088 );
2089 WHEN OTHERS THEN
2090 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2091 (
2092 l_api_name,
2093 G_PKG_NAME,
2094 'OTHERS',
2095 x_msg_count,
2096 x_msg_data,
2097 '_PVT'
2098 );
2099 END lock_row;
2100 --------------------------------------
2101 -- PL/SQL TBL lock_row for:CVPV_TBL --
2102 --------------------------------------
2103 PROCEDURE lock_row(
2104 p_api_version IN NUMBER,
2105 p_init_msg_list IN VARCHAR2,
2106 x_return_status OUT NOCOPY VARCHAR2,
2107 x_msg_count OUT NOCOPY NUMBER,
2108 x_msg_data OUT NOCOPY VARCHAR2,
2109 p_cvpv_tbl IN cvpv_tbl_type) IS
2110
2111 l_api_version CONSTANT NUMBER := 1;
2112 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2113 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2114 l_error_tbl OKC_API.ERROR_TBL_TYPE;
2115 BEGIN
2116 OKC_API.init_msg_list(p_init_msg_list);
2117 -- Make sure PL/SQL table has recrods in it before passing
2118 IF (p_cvpv_tbl.COUNT > 0) THEN
2119 lock_row(
2120 p_api_version => p_api_version,
2121 p_init_msg_list => OKC_API.G_FALSE,
2122 x_return_status => x_return_status,
2123 x_msg_count => x_msg_count,
2124 x_msg_data => x_msg_data,
2125 p_cvpv_tbl => p_cvpv_tbl,
2126 px_error_tbl => l_error_tbl);
2127 END IF;
2128 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2129 EXCEPTION
2130 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2131 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2132 (
2133 l_api_name,
2134 G_PKG_NAME,
2135 'OKC_API.G_RET_STS_ERROR',
2136 x_msg_count,
2137 x_msg_data,
2138 '_PVT'
2139 );
2140 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2141 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2142 (
2143 l_api_name,
2144 G_PKG_NAME,
2145 'OKC_API.G_RET_STS_UNEXP_ERROR',
2146 x_msg_count,
2147 x_msg_data,
2148 '_PVT'
2149 );
2150 WHEN OTHERS THEN
2151 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2152 (
2153 l_api_name,
2154 G_PKG_NAME,
2155 'OTHERS',
2156 x_msg_count,
2157 x_msg_data,
2158 '_PVT'
2159 );
2160 END lock_row;
2161 ---------------------------------------------------------------------------
2162 -- PROCEDURE update_row
2163 ---------------------------------------------------------------------------
2164 ------------------------------------
2165 -- update_row for:OKS_COV_TYPES_B --
2166 ------------------------------------
2167 PROCEDURE update_row(
2168 p_init_msg_list IN VARCHAR2,
2169 x_return_status OUT NOCOPY VARCHAR2,
2170 x_msg_count OUT NOCOPY NUMBER,
2171 x_msg_data OUT NOCOPY VARCHAR2,
2172 p_cvp_rec IN cvp_rec_type,
2173 x_cvp_rec OUT NOCOPY cvp_rec_type) IS
2174
2175 l_api_version CONSTANT NUMBER := 1;
2176 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
2177 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2178 l_cvp_rec cvp_rec_type := p_cvp_rec;
2179 l_def_cvp_rec cvp_rec_type;
2180 l_row_notfound BOOLEAN := TRUE;
2181 ----------------------------------
2182 -- FUNCTION populate_new_record --
2183 ----------------------------------
2184 FUNCTION populate_new_record (
2185 p_cvp_rec IN cvp_rec_type,
2186 x_cvp_rec OUT NOCOPY cvp_rec_type
2187 ) RETURN VARCHAR2 IS
2188 l_cvp_rec cvp_rec_type;
2189 l_row_notfound BOOLEAN := TRUE;
2190 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2191 BEGIN
2192 x_cvp_rec := p_cvp_rec;
2193 -- Get current database values
2194 l_cvp_rec := get_rec(p_cvp_rec, l_return_status);
2195 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2196 IF (x_cvp_rec.code = OKC_API.G_MISS_CHAR)
2197 THEN
2198 x_cvp_rec.code := l_cvp_rec.code;
2199 END IF;
2200 IF (x_cvp_rec.importance_level = OKC_API.G_MISS_NUM)
2201 THEN
2202 x_cvp_rec.importance_level := l_cvp_rec.importance_level;
2203 END IF;
2204 IF (x_cvp_rec.enabled_flag = OKC_API.G_MISS_CHAR)
2205 THEN
2206 x_cvp_rec.enabled_flag := l_cvp_rec.enabled_flag;
2207 END IF;
2208 IF (x_cvp_rec.start_date_active = OKC_API.G_MISS_DATE)
2209 THEN
2210 x_cvp_rec.start_date_active := l_cvp_rec.start_date_active;
2211 END IF;
2212 IF (x_cvp_rec.end_date_active = OKC_API.G_MISS_DATE)
2213 THEN
2214 x_cvp_rec.end_date_active := l_cvp_rec.end_date_active;
2215 END IF;
2216 IF (x_cvp_rec.created_by = OKC_API.G_MISS_NUM)
2217 THEN
2218 x_cvp_rec.created_by := l_cvp_rec.created_by;
2219 END IF;
2220 IF (x_cvp_rec.creation_date = OKC_API.G_MISS_DATE)
2221 THEN
2222 x_cvp_rec.creation_date := l_cvp_rec.creation_date;
2223 END IF;
2224 IF (x_cvp_rec.last_updated_by = OKC_API.G_MISS_NUM)
2225 THEN
2226 x_cvp_rec.last_updated_by := l_cvp_rec.last_updated_by;
2227 END IF;
2228 IF (x_cvp_rec.last_update_login = OKC_API.G_MISS_NUM)
2229 THEN
2230 x_cvp_rec.last_update_login := l_cvp_rec.last_update_login;
2231 END IF;
2232 IF (x_cvp_rec.last_update_date = OKC_API.G_MISS_DATE)
2233 THEN
2234 x_cvp_rec.last_update_date := l_cvp_rec.last_update_date;
2235 END IF;
2236 IF (x_cvp_rec.attribute_category = OKC_API.G_MISS_CHAR)
2237 THEN
2238 x_cvp_rec.attribute_category := l_cvp_rec.attribute_category;
2239 END IF;
2240 IF (x_cvp_rec.attribute1 = OKC_API.G_MISS_CHAR)
2241 THEN
2242 x_cvp_rec.attribute1 := l_cvp_rec.attribute1;
2243 END IF;
2244 IF (x_cvp_rec.attribute2 = OKC_API.G_MISS_CHAR)
2245 THEN
2246 x_cvp_rec.attribute2 := l_cvp_rec.attribute2;
2247 END IF;
2248 IF (x_cvp_rec.attribute3 = OKC_API.G_MISS_CHAR)
2249 THEN
2250 x_cvp_rec.attribute3 := l_cvp_rec.attribute3;
2251 END IF;
2252 IF (x_cvp_rec.attribute4 = OKC_API.G_MISS_CHAR)
2253 THEN
2254 x_cvp_rec.attribute4 := l_cvp_rec.attribute4;
2255 END IF;
2256 IF (x_cvp_rec.attribute5 = OKC_API.G_MISS_CHAR)
2257 THEN
2258 x_cvp_rec.attribute5 := l_cvp_rec.attribute5;
2259 END IF;
2260 IF (x_cvp_rec.attribute6 = OKC_API.G_MISS_CHAR)
2261 THEN
2262 x_cvp_rec.attribute6 := l_cvp_rec.attribute6;
2263 END IF;
2264 IF (x_cvp_rec.attribute7 = OKC_API.G_MISS_CHAR)
2265 THEN
2266 x_cvp_rec.attribute7 := l_cvp_rec.attribute7;
2267 END IF;
2268 IF (x_cvp_rec.attribute8 = OKC_API.G_MISS_CHAR)
2269 THEN
2270 x_cvp_rec.attribute8 := l_cvp_rec.attribute8;
2271 END IF;
2272 IF (x_cvp_rec.attribute9 = OKC_API.G_MISS_CHAR)
2273 THEN
2274 x_cvp_rec.attribute9 := l_cvp_rec.attribute9;
2275 END IF;
2276 IF (x_cvp_rec.attribute10 = OKC_API.G_MISS_CHAR)
2277 THEN
2278 x_cvp_rec.attribute10 := l_cvp_rec.attribute10;
2279 END IF;
2280 IF (x_cvp_rec.attribute11 = OKC_API.G_MISS_CHAR)
2281 THEN
2282 x_cvp_rec.attribute11 := l_cvp_rec.attribute11;
2283 END IF;
2284 IF (x_cvp_rec.attribute12 = OKC_API.G_MISS_CHAR)
2285 THEN
2286 x_cvp_rec.attribute12 := l_cvp_rec.attribute12;
2287 END IF;
2288 IF (x_cvp_rec.attribute13 = OKC_API.G_MISS_CHAR)
2289 THEN
2290 x_cvp_rec.attribute13 := l_cvp_rec.attribute13;
2291 END IF;
2292 IF (x_cvp_rec.attribute14 = OKC_API.G_MISS_CHAR)
2293 THEN
2294 x_cvp_rec.attribute14 := l_cvp_rec.attribute14;
2295 END IF;
2296 IF (x_cvp_rec.attribute15 = OKC_API.G_MISS_CHAR)
2297 THEN
2298 x_cvp_rec.attribute15 := l_cvp_rec.attribute15;
2299 END IF;
2300 END IF;
2301 RETURN(l_return_status);
2302 END populate_new_record;
2303 ----------------------------------------
2304 -- Set_Attributes for:OKS_COV_TYPES_B --
2305 ----------------------------------------
2306 FUNCTION Set_Attributes (
2307 p_cvp_rec IN cvp_rec_type,
2308 x_cvp_rec OUT NOCOPY cvp_rec_type
2309 ) RETURN VARCHAR2 IS
2310 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2311 BEGIN
2312 x_cvp_rec := p_cvp_rec;
2313 RETURN(l_return_status);
2314 END Set_Attributes;
2315 BEGIN
2316 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2317 p_init_msg_list,
2318 '_PVT',
2319 x_return_status);
2320 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2321 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2322 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2323 RAISE OKC_API.G_EXCEPTION_ERROR;
2324 END IF;
2325 --- Setting item attributes
2326 l_return_status := Set_Attributes(
2327 p_cvp_rec, -- IN
2328 l_cvp_rec); -- OUT
2329 --- If any errors happen abort API
2330 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2331 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2332 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2333 RAISE OKC_API.G_EXCEPTION_ERROR;
2334 END IF;
2335 l_return_status := populate_new_record(l_cvp_rec, l_def_cvp_rec);
2336 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2337 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2338 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2339 RAISE OKC_API.G_EXCEPTION_ERROR;
2340 END IF;
2341 UPDATE OKS_COV_TYPES_B
2342 SET IMPORTANCE_LEVEL = l_def_cvp_rec.importance_level,
2343 ENABLED_FLAG = l_def_cvp_rec.enabled_flag,
2344 START_DATE_ACTIVE = l_def_cvp_rec.start_date_active,
2345 END_DATE_ACTIVE = l_def_cvp_rec.end_date_active,
2346 CREATED_BY = l_def_cvp_rec.created_by,
2347 CREATION_DATE = l_def_cvp_rec.creation_date,
2348 LAST_UPDATED_BY = l_def_cvp_rec.last_updated_by,
2349 LAST_UPDATE_LOGIN = l_def_cvp_rec.last_update_login,
2350 LAST_UPDATE_DATE = l_def_cvp_rec.last_update_date,
2351 ATTRIBUTE_CATEGORY = l_def_cvp_rec.attribute_category,
2352 ATTRIBUTE1 = l_def_cvp_rec.attribute1,
2353 ATTRIBUTE2 = l_def_cvp_rec.attribute2,
2354 ATTRIBUTE3 = l_def_cvp_rec.attribute3,
2355 ATTRIBUTE4 = l_def_cvp_rec.attribute4,
2356 ATTRIBUTE5 = l_def_cvp_rec.attribute5,
2357 ATTRIBUTE6 = l_def_cvp_rec.attribute6,
2358 ATTRIBUTE7 = l_def_cvp_rec.attribute7,
2359 ATTRIBUTE8 = l_def_cvp_rec.attribute8,
2360 ATTRIBUTE9 = l_def_cvp_rec.attribute9,
2361 ATTRIBUTE10 = l_def_cvp_rec.attribute10,
2362 ATTRIBUTE11 = l_def_cvp_rec.attribute11,
2363 ATTRIBUTE12 = l_def_cvp_rec.attribute12,
2364 ATTRIBUTE13 = l_def_cvp_rec.attribute13,
2365 ATTRIBUTE14 = l_def_cvp_rec.attribute14,
2366 ATTRIBUTE15 = l_def_cvp_rec.attribute15
2367 WHERE CODE = l_def_cvp_rec.code;
2368
2369 x_cvp_rec := l_cvp_rec;
2370 x_return_status := l_return_status;
2371 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2372 EXCEPTION
2373 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2374 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2375 (
2376 l_api_name,
2377 G_PKG_NAME,
2378 'OKC_API.G_RET_STS_ERROR',
2379 x_msg_count,
2380 x_msg_data,
2381 '_PVT'
2382 );
2383 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2384 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2385 (
2386 l_api_name,
2387 G_PKG_NAME,
2388 'OKC_API.G_RET_STS_UNEXP_ERROR',
2389 x_msg_count,
2390 x_msg_data,
2391 '_PVT'
2392 );
2393 WHEN OTHERS THEN
2394 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2395 (
2396 l_api_name,
2397 G_PKG_NAME,
2398 'OTHERS',
2399 x_msg_count,
2400 x_msg_data,
2401 '_PVT'
2402 );
2403 END update_row;
2404 -------------------------------------
2405 -- update_row for:OKS_COV_TYPES_TL --
2406 -------------------------------------
2407 PROCEDURE update_row(
2408 p_init_msg_list IN VARCHAR2,
2409 x_return_status OUT NOCOPY VARCHAR2,
2410 x_msg_count OUT NOCOPY NUMBER,
2411 x_msg_data OUT NOCOPY VARCHAR2,
2412 p_oks_cov_types_tl_rec IN oks_cov_types_tl_rec_type,
2413 x_oks_cov_types_tl_rec OUT NOCOPY oks_cov_types_tl_rec_type) IS
2414
2415 l_api_version CONSTANT NUMBER := 1;
2416 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
2417 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2418 l_oks_cov_types_tl_rec oks_cov_types_tl_rec_type := p_oks_cov_types_tl_rec;
2419 l_def_oks_cov_types_tl_rec oks_cov_types_tl_rec_type;
2420 l_row_notfound BOOLEAN := TRUE;
2421 ----------------------------------
2422 -- FUNCTION populate_new_record --
2423 ----------------------------------
2424 FUNCTION populate_new_record (
2425 p_oks_cov_types_tl_rec IN oks_cov_types_tl_rec_type,
2426 x_oks_cov_types_tl_rec OUT NOCOPY oks_cov_types_tl_rec_type
2427 ) RETURN VARCHAR2 IS
2428 l_oks_cov_types_tl_rec oks_cov_types_tl_rec_type;
2429 l_row_notfound BOOLEAN := TRUE;
2430 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2431 BEGIN
2432 x_oks_cov_types_tl_rec := p_oks_cov_types_tl_rec;
2433 -- Get current database values
2434 l_oks_cov_types_tl_rec := get_rec(p_oks_cov_types_tl_rec, l_return_status);
2435 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2436 IF (x_oks_cov_types_tl_rec.code = OKC_API.G_MISS_CHAR)
2437 THEN
2438 x_oks_cov_types_tl_rec.code := l_oks_cov_types_tl_rec.code;
2439 END IF;
2440 IF (x_oks_cov_types_tl_rec.language = OKC_API.G_MISS_CHAR)
2441 THEN
2442 x_oks_cov_types_tl_rec.language := l_oks_cov_types_tl_rec.language;
2443 END IF;
2444 IF (x_oks_cov_types_tl_rec.source_lang = OKC_API.G_MISS_CHAR)
2445 THEN
2446 x_oks_cov_types_tl_rec.source_lang := l_oks_cov_types_tl_rec.source_lang;
2447 END IF;
2448 IF (x_oks_cov_types_tl_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
2449 THEN
2450 x_oks_cov_types_tl_rec.sfwt_flag := l_oks_cov_types_tl_rec.sfwt_flag;
2451 END IF;
2452 IF (x_oks_cov_types_tl_rec.meaning = OKC_API.G_MISS_CHAR)
2453 THEN
2454 x_oks_cov_types_tl_rec.meaning := l_oks_cov_types_tl_rec.meaning;
2455 END IF;
2456 IF (x_oks_cov_types_tl_rec.description = OKC_API.G_MISS_CHAR)
2457 THEN
2458 x_oks_cov_types_tl_rec.description := l_oks_cov_types_tl_rec.description;
2459 END IF;
2460 IF (x_oks_cov_types_tl_rec.created_by = OKC_API.G_MISS_NUM)
2461 THEN
2462 x_oks_cov_types_tl_rec.created_by := l_oks_cov_types_tl_rec.created_by;
2463 END IF;
2464 IF (x_oks_cov_types_tl_rec.creation_date = OKC_API.G_MISS_DATE)
2465 THEN
2466 x_oks_cov_types_tl_rec.creation_date := l_oks_cov_types_tl_rec.creation_date;
2467 END IF;
2468 IF (x_oks_cov_types_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
2469 THEN
2470 x_oks_cov_types_tl_rec.last_updated_by := l_oks_cov_types_tl_rec.last_updated_by;
2471 END IF;
2472 IF (x_oks_cov_types_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
2473 THEN
2474 x_oks_cov_types_tl_rec.last_update_login := l_oks_cov_types_tl_rec.last_update_login;
2475 END IF;
2476 IF (x_oks_cov_types_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
2477 THEN
2478 x_oks_cov_types_tl_rec.last_update_date := l_oks_cov_types_tl_rec.last_update_date;
2479 END IF;
2480 END IF;
2481 RETURN(l_return_status);
2482 END populate_new_record;
2483 -----------------------------------------
2484 -- Set_Attributes for:OKS_COV_TYPES_TL --
2485 -----------------------------------------
2486 FUNCTION Set_Attributes (
2487 p_oks_cov_types_tl_rec IN oks_cov_types_tl_rec_type,
2488 x_oks_cov_types_tl_rec OUT NOCOPY oks_cov_types_tl_rec_type
2489 ) RETURN VARCHAR2 IS
2490 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2491 BEGIN
2492 x_oks_cov_types_tl_rec := p_oks_cov_types_tl_rec;
2493 x_oks_cov_types_tl_rec.LANGUAGE := USERENV('LANG');
2494 x_oks_cov_types_tl_rec.LANGUAGE := USERENV('LANG');
2495 RETURN(l_return_status);
2496 END Set_Attributes;
2497 BEGIN
2498 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2499 p_init_msg_list,
2500 '_PVT',
2501 x_return_status);
2502 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2503 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2504 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2505 RAISE OKC_API.G_EXCEPTION_ERROR;
2506 END IF;
2507 --- Setting item attributes
2508 l_return_status := Set_Attributes(
2509 p_oks_cov_types_tl_rec, -- IN
2510 l_oks_cov_types_tl_rec); -- OUT
2511 --- If any errors happen abort API
2512 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2513 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2514 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2515 RAISE OKC_API.G_EXCEPTION_ERROR;
2516 END IF;
2517 l_return_status := populate_new_record(l_oks_cov_types_tl_rec, l_def_oks_cov_types_tl_rec);
2518 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2519 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2520 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2521 RAISE OKC_API.G_EXCEPTION_ERROR;
2522 END IF;
2523 UPDATE OKS_COV_TYPES_TL
2524 SET MEANING = l_def_oks_cov_types_tl_rec.meaning,
2525 DESCRIPTION = l_def_oks_cov_types_tl_rec.description,
2526 CREATED_BY = l_def_oks_cov_types_tl_rec.created_by,
2527 CREATION_DATE = l_def_oks_cov_types_tl_rec.creation_date,
2528 LAST_UPDATED_BY = l_def_oks_cov_types_tl_rec.last_updated_by,
2529 LAST_UPDATE_LOGIN = l_def_oks_cov_types_tl_rec.last_update_login,
2530 LAST_UPDATE_DATE = l_def_oks_cov_types_tl_rec.last_update_date
2531 WHERE CODE = l_def_oks_cov_types_tl_rec.code
2532 AND SOURCE_LANG = USERENV('LANG');
2533
2534 UPDATE OKS_COV_TYPES_TL
2535 SET SFWT_FLAG = 'Y'
2536 WHERE CODE = l_def_oks_cov_types_tl_rec.code
2537 AND SOURCE_LANG <> USERENV('LANG');
2538
2539 x_oks_cov_types_tl_rec := l_oks_cov_types_tl_rec;
2540 x_return_status := l_return_status;
2541 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2542 EXCEPTION
2543 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2544 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2545 (
2546 l_api_name,
2547 G_PKG_NAME,
2548 'OKC_API.G_RET_STS_ERROR',
2549 x_msg_count,
2550 x_msg_data,
2551 '_PVT'
2552 );
2553 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2554 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2555 (
2556 l_api_name,
2557 G_PKG_NAME,
2558 'OKC_API.G_RET_STS_UNEXP_ERROR',
2559 x_msg_count,
2560 x_msg_data,
2561 '_PVT'
2562 );
2563 WHEN OTHERS THEN
2564 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2565 (
2566 l_api_name,
2567 G_PKG_NAME,
2568 'OTHERS',
2569 x_msg_count,
2570 x_msg_data,
2571 '_PVT'
2572 );
2573 END update_row;
2574 ------------------------------------
2575 -- update_row for:OKS_COV_TYPES_V --
2576 ------------------------------------
2577 PROCEDURE update_row(
2578 p_api_version IN NUMBER,
2579 p_init_msg_list IN VARCHAR2,
2580 x_return_status OUT NOCOPY VARCHAR2,
2581 x_msg_count OUT NOCOPY NUMBER,
2582 x_msg_data OUT NOCOPY VARCHAR2,
2583 p_cvpv_rec IN cvpv_rec_type,
2584 x_cvpv_rec OUT NOCOPY cvpv_rec_type) IS
2585
2586 l_api_version CONSTANT NUMBER := 1;
2587 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2588 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2589 l_cvpv_rec cvpv_rec_type := p_cvpv_rec;
2590 l_def_cvpv_rec cvpv_rec_type;
2591 l_db_cvpv_rec cvpv_rec_type;
2592 l_cvp_rec cvp_rec_type;
2593 lx_cvp_rec cvp_rec_type;
2594 l_oks_cov_types_tl_rec oks_cov_types_tl_rec_type;
2595 lx_oks_cov_types_tl_rec oks_cov_types_tl_rec_type;
2596 -------------------------------
2597 -- FUNCTION fill_who_columns --
2598 -------------------------------
2599 FUNCTION fill_who_columns (
2600 p_cvpv_rec IN cvpv_rec_type
2601 ) RETURN cvpv_rec_type IS
2602 l_cvpv_rec cvpv_rec_type := p_cvpv_rec;
2603 BEGIN
2604 l_cvpv_rec.LAST_UPDATE_DATE := SYSDATE;
2605 l_cvpv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2606 l_cvpv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2607 RETURN(l_cvpv_rec);
2608 END fill_who_columns;
2609 ----------------------------------
2610 -- FUNCTION populate_new_record --
2611 ----------------------------------
2612 FUNCTION populate_new_record (
2613 p_cvpv_rec IN cvpv_rec_type,
2614 x_cvpv_rec OUT NOCOPY cvpv_rec_type
2615 ) RETURN VARCHAR2 IS
2616 l_row_notfound BOOLEAN := TRUE;
2617 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2618 BEGIN
2619 x_cvpv_rec := p_cvpv_rec;
2620 -- Get current database values
2621 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
2622 -- so it may be verified through LOCK_ROW.
2623 l_db_cvpv_rec := get_rec(p_cvpv_rec, l_return_status);
2624 IF (l_return_status = OKC_API.G_RET_STS_SUCCESS) THEN
2625 IF (x_cvpv_rec.code = OKC_API.G_MISS_CHAR)
2626 THEN
2627 x_cvpv_rec.code := l_db_cvpv_rec.code;
2628 END IF;
2629 IF (x_cvpv_rec.meaning = OKC_API.G_MISS_CHAR)
2630 THEN
2631 x_cvpv_rec.meaning := l_db_cvpv_rec.meaning;
2632 END IF;
2633 IF (x_cvpv_rec.description = OKC_API.G_MISS_CHAR)
2634 THEN
2635 x_cvpv_rec.description := l_db_cvpv_rec.description;
2636 END IF;
2637 IF (x_cvpv_rec.importance_level = OKC_API.G_MISS_NUM)
2638 THEN
2639 x_cvpv_rec.importance_level := l_db_cvpv_rec.importance_level;
2640 END IF;
2641 IF (x_cvpv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
2642 THEN
2643 x_cvpv_rec.sfwt_flag := l_db_cvpv_rec.sfwt_flag;
2644 END IF;
2645 IF (x_cvpv_rec.enabled_flag = OKC_API.G_MISS_CHAR)
2646 THEN
2647 x_cvpv_rec.enabled_flag := l_db_cvpv_rec.enabled_flag;
2648 END IF;
2649 IF (x_cvpv_rec.start_date_active = OKC_API.G_MISS_DATE)
2650 THEN
2651 x_cvpv_rec.start_date_active := l_db_cvpv_rec.start_date_active;
2652 END IF;
2653 IF (x_cvpv_rec.end_date_active = OKC_API.G_MISS_DATE)
2654 THEN
2655 x_cvpv_rec.end_date_active := l_db_cvpv_rec.end_date_active;
2656 END IF;
2657 IF (x_cvpv_rec.created_by = OKC_API.G_MISS_NUM)
2658 THEN
2659 x_cvpv_rec.created_by := l_db_cvpv_rec.created_by;
2660 END IF;
2661 IF (x_cvpv_rec.creation_date = OKC_API.G_MISS_DATE)
2662 THEN
2663 x_cvpv_rec.creation_date := l_db_cvpv_rec.creation_date;
2664 END IF;
2665 IF (x_cvpv_rec.last_updated_by = OKC_API.G_MISS_NUM)
2666 THEN
2667 x_cvpv_rec.last_updated_by := l_db_cvpv_rec.last_updated_by;
2668 END IF;
2669 IF (x_cvpv_rec.last_update_login = OKC_API.G_MISS_NUM)
2670 THEN
2671 x_cvpv_rec.last_update_login := l_db_cvpv_rec.last_update_login;
2672 END IF;
2673 IF (x_cvpv_rec.last_update_date = OKC_API.G_MISS_DATE)
2674 THEN
2675 x_cvpv_rec.last_update_date := l_db_cvpv_rec.last_update_date;
2676 END IF;
2677 IF (x_cvpv_rec.attribute_category = OKC_API.G_MISS_CHAR)
2678 THEN
2679 x_cvpv_rec.attribute_category := l_db_cvpv_rec.attribute_category;
2680 END IF;
2681 IF (x_cvpv_rec.attribute1 = OKC_API.G_MISS_CHAR)
2682 THEN
2683 x_cvpv_rec.attribute1 := l_db_cvpv_rec.attribute1;
2684 END IF;
2685 IF (x_cvpv_rec.attribute2 = OKC_API.G_MISS_CHAR)
2686 THEN
2687 x_cvpv_rec.attribute2 := l_db_cvpv_rec.attribute2;
2688 END IF;
2689 IF (x_cvpv_rec.attribute3 = OKC_API.G_MISS_CHAR)
2690 THEN
2691 x_cvpv_rec.attribute3 := l_db_cvpv_rec.attribute3;
2692 END IF;
2693 IF (x_cvpv_rec.attribute4 = OKC_API.G_MISS_CHAR)
2694 THEN
2695 x_cvpv_rec.attribute4 := l_db_cvpv_rec.attribute4;
2696 END IF;
2697 IF (x_cvpv_rec.attribute5 = OKC_API.G_MISS_CHAR)
2698 THEN
2699 x_cvpv_rec.attribute5 := l_db_cvpv_rec.attribute5;
2700 END IF;
2701 IF (x_cvpv_rec.attribute6 = OKC_API.G_MISS_CHAR)
2702 THEN
2703 x_cvpv_rec.attribute6 := l_db_cvpv_rec.attribute6;
2704 END IF;
2705 IF (x_cvpv_rec.attribute7 = OKC_API.G_MISS_CHAR)
2706 THEN
2707 x_cvpv_rec.attribute7 := l_db_cvpv_rec.attribute7;
2708 END IF;
2709 IF (x_cvpv_rec.attribute8 = OKC_API.G_MISS_CHAR)
2710 THEN
2711 x_cvpv_rec.attribute8 := l_db_cvpv_rec.attribute8;
2712 END IF;
2713 IF (x_cvpv_rec.attribute9 = OKC_API.G_MISS_CHAR)
2714 THEN
2715 x_cvpv_rec.attribute9 := l_db_cvpv_rec.attribute9;
2716 END IF;
2717 IF (x_cvpv_rec.attribute10 = OKC_API.G_MISS_CHAR)
2718 THEN
2719 x_cvpv_rec.attribute10 := l_db_cvpv_rec.attribute10;
2720 END IF;
2721 IF (x_cvpv_rec.attribute11 = OKC_API.G_MISS_CHAR)
2722 THEN
2723 x_cvpv_rec.attribute11 := l_db_cvpv_rec.attribute11;
2724 END IF;
2725 IF (x_cvpv_rec.attribute12 = OKC_API.G_MISS_CHAR)
2726 THEN
2727 x_cvpv_rec.attribute12 := l_db_cvpv_rec.attribute12;
2728 END IF;
2729 IF (x_cvpv_rec.attribute13 = OKC_API.G_MISS_CHAR)
2730 THEN
2731 x_cvpv_rec.attribute13 := l_db_cvpv_rec.attribute13;
2732 END IF;
2733 IF (x_cvpv_rec.attribute14 = OKC_API.G_MISS_CHAR)
2734 THEN
2735 x_cvpv_rec.attribute14 := l_db_cvpv_rec.attribute14;
2736 END IF;
2737 IF (x_cvpv_rec.attribute15 = OKC_API.G_MISS_CHAR)
2738 THEN
2739 x_cvpv_rec.attribute15 := l_db_cvpv_rec.attribute15;
2740 END IF;
2741 END IF;
2742 RETURN(l_return_status);
2743 END populate_new_record;
2744 ----------------------------------------
2745 -- Set_Attributes for:OKS_COV_TYPES_V --
2746 ----------------------------------------
2747 FUNCTION Set_Attributes (
2748 p_cvpv_rec IN cvpv_rec_type,
2749 x_cvpv_rec OUT NOCOPY cvpv_rec_type
2750 ) RETURN VARCHAR2 IS
2751 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2752 BEGIN
2753 x_cvpv_rec := p_cvpv_rec;
2754 RETURN(l_return_status);
2755 END Set_Attributes;
2756 BEGIN
2757 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2758 G_PKG_NAME,
2759 p_init_msg_list,
2760 l_api_version,
2761 p_api_version,
2762 '_PVT',
2763 x_return_status);
2764 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2765 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2766 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2767 RAISE OKC_API.G_EXCEPTION_ERROR;
2768 END IF;
2769 --- Setting item attributes
2770 l_return_status := Set_Attributes(
2771 p_cvpv_rec, -- IN
2772 x_cvpv_rec); -- OUT
2773 --- If any errors happen abort API
2774 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2775 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2776 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2777 RAISE OKC_API.G_EXCEPTION_ERROR;
2778 END IF;
2779 l_return_status := populate_new_record(l_cvpv_rec, l_def_cvpv_rec);
2780 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2781 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2782 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2783 RAISE OKC_API.G_EXCEPTION_ERROR;
2784 END IF;
2785 l_def_cvpv_rec := fill_who_columns(l_def_cvpv_rec);
2786 --- Validate all non-missing attributes (Item Level Validation)
2787 l_return_status := Validate_Attributes(l_def_cvpv_rec);
2788 --- If any errors happen abort API
2789 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2790 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2791 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2792 RAISE OKC_API.G_EXCEPTION_ERROR;
2793 END IF;
2794 l_return_status := Validate_Record(l_def_cvpv_rec, l_db_cvpv_rec);
2795 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2796 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2797 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2798 RAISE OKC_API.G_EXCEPTION_ERROR;
2799 END IF;
2800
2801 -- Lock the Record
2802 /* lock_row(
2803 p_api_version => p_api_version,
2804 p_init_msg_list => p_init_msg_list,
2805 x_return_status => l_return_status,
2806 x_msg_count => x_msg_count,
2807 x_msg_data => x_msg_data,
2808 p_cvpv_rec => p_cvpv_rec);
2809 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2810 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2811 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2812 RAISE OKC_API.G_EXCEPTION_ERROR;
2813 END IF;*/
2814
2815 -----------------------------------------
2816 -- Move VIEW record to "Child" records --
2817 -----------------------------------------
2818 migrate(l_def_cvpv_rec, l_cvp_rec);
2819 migrate(l_def_cvpv_rec, l_oks_cov_types_tl_rec);
2820 -----------------------------------------------
2821 -- Call the UPDATE_ROW for each child record --
2822 -----------------------------------------------
2823 update_row(
2824 p_init_msg_list,
2825 l_return_status,
2826 x_msg_count,
2827 x_msg_data,
2828 l_cvp_rec,
2829 lx_cvp_rec
2830 );
2831 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2832 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2833 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2834 RAISE OKC_API.G_EXCEPTION_ERROR;
2835 END IF;
2836 migrate(lx_cvp_rec, l_def_cvpv_rec);
2837 update_row(
2838 p_init_msg_list,
2839 l_return_status,
2840 x_msg_count,
2841 x_msg_data,
2842 l_oks_cov_types_tl_rec,
2843 lx_oks_cov_types_tl_rec
2844 );
2845 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2846 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2847 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2848 RAISE OKC_API.G_EXCEPTION_ERROR;
2849 END IF;
2850 migrate(lx_oks_cov_types_tl_rec, l_def_cvpv_rec);
2851 x_cvpv_rec := l_def_cvpv_rec;
2852 x_return_status := l_return_status;
2853 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2854 EXCEPTION
2855 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2856 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2857 (
2858 l_api_name,
2859 G_PKG_NAME,
2860 'OKC_API.G_RET_STS_ERROR',
2861 x_msg_count,
2862 x_msg_data,
2863 '_PVT'
2864 );
2865 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2866 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2867 (
2868 l_api_name,
2869 G_PKG_NAME,
2870 'OKC_API.G_RET_STS_UNEXP_ERROR',
2871 x_msg_count,
2872 x_msg_data,
2873 '_PVT'
2874 );
2875 WHEN OTHERS THEN
2876 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2877 (
2878 l_api_name,
2879 G_PKG_NAME,
2880 'OTHERS',
2881 x_msg_count,
2882 x_msg_data,
2883 '_PVT'
2884 );
2885 END update_row;
2886 ----------------------------------------
2887 -- PL/SQL TBL update_row for:cvpv_tbl --
2888 ----------------------------------------
2889 PROCEDURE update_row(
2890 p_api_version IN NUMBER,
2891 p_init_msg_list IN VARCHAR2,
2892 x_return_status OUT NOCOPY VARCHAR2,
2893 x_msg_count OUT NOCOPY NUMBER,
2894 x_msg_data OUT NOCOPY VARCHAR2,
2895 p_cvpv_tbl IN cvpv_tbl_type,
2896 x_cvpv_tbl OUT NOCOPY cvpv_tbl_type,
2897 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
2898
2899 l_api_version CONSTANT NUMBER := 1;
2900 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
2901 i NUMBER := 0;
2902 BEGIN
2903 OKC_API.init_msg_list(p_init_msg_list);
2904 -- Make sure PL/SQL table has records in it before passing
2905 IF (p_cvpv_tbl.COUNT > 0) THEN
2906 i := p_cvpv_tbl.FIRST;
2907 LOOP
2908 DECLARE
2909 l_error_rec OKC_API.ERROR_REC_TYPE;
2910 BEGIN
2911 l_error_rec.api_name := l_api_name;
2912 l_error_rec.api_package := G_PKG_NAME;
2913 l_error_rec.idx := i;
2914 update_row (
2915 p_api_version => p_api_version,
2916 p_init_msg_list => OKC_API.G_FALSE,
2917 x_return_status => l_error_rec.error_type,
2918 x_msg_count => l_error_rec.msg_count,
2919 x_msg_data => l_error_rec.msg_data,
2920 p_cvpv_rec => p_cvpv_tbl(i),
2921 x_cvpv_rec => x_cvpv_tbl(i));
2922 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
2923 l_error_rec.sqlcode := SQLCODE;
2924 load_error_tbl(l_error_rec, px_error_tbl);
2925 ELSE
2926 x_msg_count := l_error_rec.msg_count;
2927 x_msg_data := l_error_rec.msg_data;
2928 END IF;
2929 EXCEPTION
2930 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2931 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
2932 l_error_rec.sqlcode := SQLCODE;
2933 load_error_tbl(l_error_rec, px_error_tbl);
2934 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2935 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
2936 l_error_rec.sqlcode := SQLCODE;
2937 load_error_tbl(l_error_rec, px_error_tbl);
2938 WHEN OTHERS THEN
2939 l_error_rec.error_type := 'OTHERS';
2940 l_error_rec.sqlcode := SQLCODE;
2941 load_error_tbl(l_error_rec, px_error_tbl);
2942 END;
2943 EXIT WHEN (i = p_cvpv_tbl.LAST);
2944 i := p_cvpv_tbl.NEXT(i);
2945 END LOOP;
2946 END IF;
2947 -- Loop through the error_tbl to find the error with the highest severity
2948 -- and return it.
2949 x_return_status := find_highest_exception(px_error_tbl);
2950 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2951 EXCEPTION
2952 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2953 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2954 (
2955 l_api_name,
2956 G_PKG_NAME,
2957 'OKC_API.G_RET_STS_ERROR',
2958 x_msg_count,
2959 x_msg_data,
2960 '_PVT'
2961 );
2962 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2963 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2964 (
2965 l_api_name,
2966 G_PKG_NAME,
2967 'OKC_API.G_RET_STS_UNEXP_ERROR',
2968 x_msg_count,
2969 x_msg_data,
2970 '_PVT'
2971 );
2972 WHEN OTHERS THEN
2973 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2974 (
2975 l_api_name,
2976 G_PKG_NAME,
2977 'OTHERS',
2978 x_msg_count,
2979 x_msg_data,
2980 '_PVT'
2981 );
2982 END update_row;
2983
2984 ----------------------------------------
2985 -- PL/SQL TBL update_row for:CVPV_TBL --
2986 ----------------------------------------
2987 -- This procedure is the same as the one above except it does not have a "px_error_tbl" argument.
2988 -- This procedure was create for backward compatibility and simply is a wrapper for the one above.
2989 PROCEDURE update_row(
2990 p_api_version IN NUMBER,
2991 p_init_msg_list IN VARCHAR2,
2992 x_return_status OUT NOCOPY VARCHAR2,
2993 x_msg_count OUT NOCOPY NUMBER,
2994 x_msg_data OUT NOCOPY VARCHAR2,
2995 p_cvpv_tbl IN cvpv_tbl_type,
2996 x_cvpv_tbl OUT NOCOPY cvpv_tbl_type) IS
2997
2998 l_api_version CONSTANT NUMBER := 1;
2999 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
3000 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3001 l_error_tbl OKC_API.ERROR_TBL_TYPE;
3002 BEGIN
3003 OKC_API.init_msg_list(p_init_msg_list);
3004 -- Make sure PL/SQL table has records in it before passing
3005 IF (p_cvpv_tbl.COUNT > 0) THEN
3006 update_row (
3007 p_api_version => p_api_version,
3008 p_init_msg_list => OKC_API.G_FALSE,
3009 x_return_status => x_return_status,
3010 x_msg_count => x_msg_count,
3011 x_msg_data => x_msg_data,
3012 p_cvpv_tbl => p_cvpv_tbl,
3013 x_cvpv_tbl => x_cvpv_tbl,
3014 px_error_tbl => l_error_tbl);
3015 END IF;
3016 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3017 EXCEPTION
3018 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3019 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3020 (
3021 l_api_name,
3022 G_PKG_NAME,
3023 'OKC_API.G_RET_STS_ERROR',
3024 x_msg_count,
3025 x_msg_data,
3026 '_PVT'
3027 );
3028 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3029 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3030 (
3031 l_api_name,
3032 G_PKG_NAME,
3033 'OKC_API.G_RET_STS_UNEXP_ERROR',
3034 x_msg_count,
3035 x_msg_data,
3036 '_PVT'
3037 );
3038 WHEN OTHERS THEN
3039 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3040 (
3041 l_api_name,
3042 G_PKG_NAME,
3043 'OTHERS',
3044 x_msg_count,
3045 x_msg_data,
3046 '_PVT'
3047 );
3048 END update_row;
3049
3050 ---------------------------------------------------------------------------
3051 -- PROCEDURE delete_row
3052 ---------------------------------------------------------------------------
3053 ------------------------------------
3054 -- delete_row for:OKS_COV_TYPES_B --
3055 ------------------------------------
3056 PROCEDURE delete_row(
3057 p_init_msg_list IN VARCHAR2,
3058 x_return_status OUT NOCOPY VARCHAR2,
3059 x_msg_count OUT NOCOPY NUMBER,
3060 x_msg_data OUT NOCOPY VARCHAR2,
3061 p_cvp_rec IN cvp_rec_type) IS
3062
3063 l_api_version CONSTANT NUMBER := 1;
3064 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
3065 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3066 l_cvp_rec cvp_rec_type := p_cvp_rec;
3067 l_row_notfound BOOLEAN := TRUE;
3068 BEGIN
3069 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3070 p_init_msg_list,
3071 '_PVT',
3072 x_return_status);
3073 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3074 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3075 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3076 RAISE OKC_API.G_EXCEPTION_ERROR;
3077 END IF;
3078
3079 DELETE FROM OKS_COV_TYPES_B
3080 WHERE CODE = p_cvp_rec.code;
3081
3082 x_return_status := l_return_status;
3083 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3084 EXCEPTION
3085 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3086 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3087 (
3088 l_api_name,
3089 G_PKG_NAME,
3090 'OKC_API.G_RET_STS_ERROR',
3091 x_msg_count,
3092 x_msg_data,
3093 '_PVT'
3094 );
3095 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3096 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3097 (
3098 l_api_name,
3099 G_PKG_NAME,
3100 'OKC_API.G_RET_STS_UNEXP_ERROR',
3101 x_msg_count,
3102 x_msg_data,
3103 '_PVT'
3104 );
3105 WHEN OTHERS THEN
3106 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3107 (
3108 l_api_name,
3109 G_PKG_NAME,
3110 'OTHERS',
3111 x_msg_count,
3112 x_msg_data,
3113 '_PVT'
3114 );
3115 END delete_row;
3116 -------------------------------------
3117 -- delete_row for:OKS_COV_TYPES_TL --
3118 -------------------------------------
3119 PROCEDURE delete_row(
3120 p_init_msg_list IN VARCHAR2,
3121 x_return_status OUT NOCOPY VARCHAR2,
3122 x_msg_count OUT NOCOPY NUMBER,
3123 x_msg_data OUT NOCOPY VARCHAR2,
3124 p_oks_cov_types_tl_rec IN oks_cov_types_tl_rec_type) IS
3125
3126 l_api_version CONSTANT NUMBER := 1;
3127 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
3128 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3129 l_oks_cov_types_tl_rec oks_cov_types_tl_rec_type := p_oks_cov_types_tl_rec;
3130 l_row_notfound BOOLEAN := TRUE;
3131 BEGIN
3132 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3133 p_init_msg_list,
3134 '_PVT',
3135 x_return_status);
3136 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3137 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3138 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3139 RAISE OKC_API.G_EXCEPTION_ERROR;
3140 END IF;
3141
3142 DELETE FROM OKS_COV_TYPES_TL
3143 WHERE CODE = p_oks_cov_types_tl_rec.code;
3144
3145 x_return_status := l_return_status;
3146 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3147 EXCEPTION
3148 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3149 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3150 (
3151 l_api_name,
3152 G_PKG_NAME,
3153 'OKC_API.G_RET_STS_ERROR',
3154 x_msg_count,
3155 x_msg_data,
3156 '_PVT'
3157 );
3158 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3159 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3160 (
3161 l_api_name,
3162 G_PKG_NAME,
3163 'OKC_API.G_RET_STS_UNEXP_ERROR',
3164 x_msg_count,
3165 x_msg_data,
3166 '_PVT'
3167 );
3168 WHEN OTHERS THEN
3169 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3170 (
3171 l_api_name,
3172 G_PKG_NAME,
3173 'OTHERS',
3174 x_msg_count,
3175 x_msg_data,
3176 '_PVT'
3177 );
3178 END delete_row;
3179 ------------------------------------
3180 -- delete_row for:OKS_COV_TYPES_V --
3181 ------------------------------------
3182 PROCEDURE delete_row(
3183 p_api_version IN NUMBER,
3184 p_init_msg_list IN VARCHAR2,
3185 x_return_status OUT NOCOPY VARCHAR2,
3186 x_msg_count OUT NOCOPY NUMBER,
3187 x_msg_data OUT NOCOPY VARCHAR2,
3188 p_cvpv_rec IN cvpv_rec_type) IS
3189
3190 l_api_version CONSTANT NUMBER := 1;
3191 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3192 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3193 l_cvpv_rec cvpv_rec_type := p_cvpv_rec;
3194 l_oks_cov_types_tl_rec oks_cov_types_tl_rec_type;
3195 l_cvp_rec cvp_rec_type;
3196 BEGIN
3197 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3198 G_PKG_NAME,
3199 p_init_msg_list,
3200 l_api_version,
3201 p_api_version,
3202 '_PVT',
3203 x_return_status);
3204 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3205 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3206 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3207 RAISE OKC_API.G_EXCEPTION_ERROR;
3208 END IF;
3209 -----------------------------------------
3210 -- Move VIEW record to "Child" records --
3211 -----------------------------------------
3212 migrate(l_cvpv_rec, l_oks_cov_types_tl_rec);
3213 migrate(l_cvpv_rec, l_cvp_rec);
3214 -----------------------------------------------
3215 -- Call the DELETE_ROW for each child record --
3216 -----------------------------------------------
3217 delete_row(
3218 p_init_msg_list,
3219 l_return_status,
3220 x_msg_count,
3221 x_msg_data,
3222 l_oks_cov_types_tl_rec
3223 );
3224 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3225 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3226 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3227 RAISE OKC_API.G_EXCEPTION_ERROR;
3228 END IF;
3229 delete_row(
3230 p_init_msg_list,
3231 l_return_status,
3232 x_msg_count,
3233 x_msg_data,
3234 l_cvp_rec
3235 );
3236 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3237 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3238 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3239 RAISE OKC_API.G_EXCEPTION_ERROR;
3240 END IF;
3241 x_return_status := l_return_status;
3242 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3243 EXCEPTION
3244 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3245 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3246 (
3247 l_api_name,
3248 G_PKG_NAME,
3249 'OKC_API.G_RET_STS_ERROR',
3250 x_msg_count,
3251 x_msg_data,
3252 '_PVT'
3253 );
3254 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3255 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3256 (
3257 l_api_name,
3258 G_PKG_NAME,
3259 'OKC_API.G_RET_STS_UNEXP_ERROR',
3260 x_msg_count,
3261 x_msg_data,
3262 '_PVT'
3263 );
3264 WHEN OTHERS THEN
3265 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3266 (
3267 l_api_name,
3268 G_PKG_NAME,
3269 'OTHERS',
3270 x_msg_count,
3271 x_msg_data,
3272 '_PVT'
3273 );
3274 END delete_row;
3275 -----------------------------------------------
3276 -- PL/SQL TBL delete_row for:OKS_COV_TYPES_V --
3277 -----------------------------------------------
3278 PROCEDURE delete_row(
3279 p_api_version IN NUMBER,
3280 p_init_msg_list IN VARCHAR2,
3281 x_return_status OUT NOCOPY VARCHAR2,
3282 x_msg_count OUT NOCOPY NUMBER,
3283 x_msg_data OUT NOCOPY VARCHAR2,
3284 p_cvpv_tbl IN cvpv_tbl_type,
3285 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
3286
3287 l_api_version CONSTANT NUMBER := 1;
3288 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
3289 i NUMBER := 0;
3290 BEGIN
3291 OKC_API.init_msg_list(p_init_msg_list);
3292 -- Make sure PL/SQL table has records in it before passing
3293 IF (p_cvpv_tbl.COUNT > 0) THEN
3294 i := p_cvpv_tbl.FIRST;
3295 LOOP
3296 DECLARE
3297 l_error_rec OKC_API.ERROR_REC_TYPE;
3298 BEGIN
3299 l_error_rec.api_name := l_api_name;
3300 l_error_rec.api_package := G_PKG_NAME;
3301 l_error_rec.idx := i;
3302 delete_row (
3303 p_api_version => p_api_version,
3304 p_init_msg_list => OKC_API.G_FALSE,
3305 x_return_status => l_error_rec.error_type,
3306 x_msg_count => l_error_rec.msg_count,
3307 x_msg_data => l_error_rec.msg_data,
3308 p_cvpv_rec => p_cvpv_tbl(i));
3309 IF (l_error_rec.error_type <> OKC_API.G_RET_STS_SUCCESS) THEN
3310 l_error_rec.sqlcode := SQLCODE;
3311 load_error_tbl(l_error_rec, px_error_tbl);
3312 ELSE
3313 x_msg_count := l_error_rec.msg_count;
3314 x_msg_data := l_error_rec.msg_data;
3315 END IF;
3316 EXCEPTION
3317 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3318 l_error_rec.error_type := OKC_API.G_RET_STS_ERROR;
3319 l_error_rec.sqlcode := SQLCODE;
3320 load_error_tbl(l_error_rec, px_error_tbl);
3321 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3322 l_error_rec.error_type := OKC_API.G_RET_STS_UNEXP_ERROR;
3323 l_error_rec.sqlcode := SQLCODE;
3324 load_error_tbl(l_error_rec, px_error_tbl);
3325 WHEN OTHERS THEN
3326 l_error_rec.error_type := 'OTHERS';
3327 l_error_rec.sqlcode := SQLCODE;
3328 load_error_tbl(l_error_rec, px_error_tbl);
3329 END;
3330 EXIT WHEN (i = p_cvpv_tbl.LAST);
3331 i := p_cvpv_tbl.NEXT(i);
3332 END LOOP;
3333 END IF;
3334 -- Loop through the error_tbl to find the error with the highest severity
3335 -- and return it.
3336 x_return_status := find_highest_exception(px_error_tbl);
3337 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3338 EXCEPTION
3339 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3340 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3341 (
3342 l_api_name,
3343 G_PKG_NAME,
3344 'OKC_API.G_RET_STS_ERROR',
3345 x_msg_count,
3346 x_msg_data,
3347 '_PVT'
3348 );
3349 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3350 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3351 (
3352 l_api_name,
3353 G_PKG_NAME,
3354 'OKC_API.G_RET_STS_UNEXP_ERROR',
3355 x_msg_count,
3356 x_msg_data,
3357 '_PVT'
3358 );
3359 WHEN OTHERS THEN
3360 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3361 (
3362 l_api_name,
3363 G_PKG_NAME,
3364 'OTHERS',
3365 x_msg_count,
3366 x_msg_data,
3367 '_PVT'
3368 );
3369 END delete_row;
3370
3371 -----------------------------------------------
3372 -- PL/SQL TBL delete_row for:OKS_COV_TYPES_V --
3373 -----------------------------------------------
3374 PROCEDURE delete_row(
3375 p_api_version IN NUMBER,
3376 p_init_msg_list IN VARCHAR2,
3377 x_return_status OUT NOCOPY VARCHAR2,
3378 x_msg_count OUT NOCOPY NUMBER,
3379 x_msg_data OUT NOCOPY VARCHAR2,
3380 p_cvpv_tbl IN cvpv_tbl_type) IS
3381
3382 l_api_version CONSTANT NUMBER := 1;
3383 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3384 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3385 l_error_tbl OKC_API.ERROR_TBL_TYPE;
3386 BEGIN
3387 OKC_API.init_msg_list(p_init_msg_list);
3388 -- Make sure PL/SQL table has records in it before passing
3389 IF (p_cvpv_tbl.COUNT > 0) THEN
3390 delete_row (
3391 p_api_version => p_api_version,
3392 p_init_msg_list => OKC_API.G_FALSE,
3393 x_return_status => x_return_status,
3394 x_msg_count => x_msg_count,
3395 x_msg_data => x_msg_data,
3396 p_cvpv_tbl => p_cvpv_tbl,
3397 px_error_tbl => l_error_tbl);
3398 END IF;
3399 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3400 EXCEPTION
3401 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3402 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3403 (
3404 l_api_name,
3405 G_PKG_NAME,
3406 'OKC_API.G_RET_STS_ERROR',
3407 x_msg_count,
3408 x_msg_data,
3409 '_PVT'
3410 );
3411 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3412 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3413 (
3414 l_api_name,
3415 G_PKG_NAME,
3416 'OKC_API.G_RET_STS_UNEXP_ERROR',
3417 x_msg_count,
3418 x_msg_data,
3419 '_PVT'
3420 );
3421 WHEN OTHERS THEN
3422 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3423 (
3424 l_api_name,
3425 G_PKG_NAME,
3426 'OTHERS',
3427 x_msg_count,
3428 x_msg_data,
3429 '_PVT'
3430 );
3431 END delete_row;
3432 ---------------------------------------------------------------------------
3433 -- PROCEDURE Migrate Coverage Types
3434 ---------------------------------------------------------------------------
3435 PROCEDURE MIGRATE_COVERAGE_TYPES(p_api_version IN NUMBER,
3436 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3437 x_return_status OUT NOCOPY VARCHAR2,
3438 x_msg_count OUT NOCOPY NUMBER,
3439 x_msg_data OUT NOCOPY VARCHAR2)
3440
3441 IS
3442 l_api_version CONSTANT NUMBER := 1;
3443 l_api_name CONSTANT VARCHAR2(30) := 'V_MIGRATE_COVTYPES';
3444 l_api_name2 CONSTANT VARCHAR2(30) := 'V_MIGRATE_COVTYPES_LANG';
3445 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3446 v_code FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE ;
3447 v_exist BOOLEAN;
3448 v_sourcelang_null BOOLEAN :=FALSE;
3449 v_source_lang VARCHAR2(4);
3450 v_lang VARCHAR2(30);
3451 l_init_msg_list VARCHAR2(1):= 'F';
3452 l_msg_count NUMBER;
3453 l_msg_data VARCHAR2(2000);
3454 CURSOR CUR_GET_COV_TYPES IS
3455 SELECT F.LANGUAGE , F.SOURCE_LANG,F.LOOKUP_CODE , F.MEANING , F.DESCRIPTION , F.ENABLED_FLAG,
3456 F.CREATED_BY, F.CREATION_DATE,F.START_DATE_ACTIVE , F.END_DATE_ACTIVE,
3457 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,ATTRIBUTE5, ATTRIBUTE6,
3458 ATTRIBUTE7, ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,
3459 ATTRIBUTE13, ATTRIBUTE14,ATTRIBUTE15
3460 FROM FND_LOOKUP_VALUES F
3461 WHERE F.LOOKUP_TYPE = 'OKSCVETYPE';
3462
3463
3464 CURSOR CUR_CHECK_EXISTS_B(v_code VARCHAR2) IS
3465 SELECT 'X' FROM OKS_COV_TYPES_B
3466 WHERE CODE = v_code ;
3467
3468 CURSOR CUR_CHECK_EXISTS_TL(v_code VARCHAR2, v_lang VARCHAR2) IS
3469 SELECT 'X' FROM OKS_COV_TYPES_TL
3470 WHERE CODE = v_code
3471 AND LANGUAGE = v_lang ;
3472
3473 CURSOR CUR_CHECK_SOURCELANG_NULL IS
3474 SELECT 'X' FROM OKS_COV_TYPES_TL
3475 WHERE SOURCE_LANG IS NULL;
3476
3477
3478 BEGIN
3479 --Modified to pass source_lang and SFWT_FLAG for bug 3807465
3480 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3481 G_PKG_NAME,
3482 p_init_msg_list,
3483 l_api_version,
3484 p_api_version,
3485 '_PVT',
3486 x_return_status);
3487 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3488 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3489 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3490 RAISE OKC_API.G_EXCEPTION_ERROR;
3491 END IF;
3492
3493 FOR C1 IN CUR_GET_COV_TYPES
3494 LOOP
3495 v_code:= C1.LOOKUP_CODE;
3496 v_lang:= C1.LANGUAGE;
3497 v_source_lang:= C1.SOURCE_LANG;
3498 v_exist:= FALSE ;
3499
3500 FOR C2 IN CUR_CHECK_EXISTS_B(v_code)
3501 LOOP
3502 v_exist:= TRUE;
3503 END LOOP ;
3504
3505 IF (v_exist = FALSE) THEN
3506 INSERT INTO OKS_COV_TYPES_B(CODE,
3507 IMPORTANCE_LEVEL,
3508 ENABLED_FLAG,
3509 START_DATE_ACTIVE,
3510 END_DATE_ACTIVE,
3511 CREATED_BY,
3512 CREATION_DATE,
3513 LAST_UPDATED_BY,
3514 LAST_UPDATE_LOGIN,
3515 LAST_UPDATE_DATE,
3516 ATTRIBUTE1,
3517 ATTRIBUTE2,
3518 ATTRIBUTE3,
3519 ATTRIBUTE4,
3520 ATTRIBUTE5,
3521 ATTRIBUTE6,
3522 ATTRIBUTE7,
3523 ATTRIBUTE8,
3524 ATTRIBUTE9,
3525 ATTRIBUTE10,
3526 ATTRIBUTE11,
3527 ATTRIBUTE12,
3528 ATTRIBUTE13,
3529 ATTRIBUTE14,
3530 ATTRIBUTE15
3531 )
3532 VALUES
3533
3534 (C1.LOOKUP_CODE,
3535 NULL, -- IMPORTANCE_LEVEL
3536 C1.ENABLED_FLAG,
3537 C1.START_DATE_ACTIVE,
3538 C1.END_DATE_ACTIVE,
3539 C1.CREATED_BY,
3540 C1.CREATION_DATE,
3541 1,
3542 0,
3543 SYSDATE,
3544 C1.ATTRIBUTE1,
3545 C1.ATTRIBUTE2,
3546 C1.ATTRIBUTE3,
3547 C1.ATTRIBUTE4,
3548 C1.ATTRIBUTE5,
3549 C1.ATTRIBUTE6,
3550 C1.ATTRIBUTE7,
3551 C1.ATTRIBUTE8,
3552 C1.ATTRIBUTE9,
3553 C1.ATTRIBUTE10,
3554 C1.ATTRIBUTE11,
3555 C1.ATTRIBUTE12,
3556 C1.ATTRIBUTE13,
3557 C1.ATTRIBUTE14,
3558 C1.ATTRIBUTE15
3559 );
3560 END IF ;
3561 v_exist:= FALSE;
3562 FOR C3 IN CUR_CHECK_EXISTS_TL(v_code, v_lang)
3563 LOOP
3564 v_exist:= TRUE;
3565 END LOOP ;
3566
3567 IF (v_exist = FALSE) THEN
3568 INSERT INTO OKS_COV_TYPES_TL(CODE,
3569 LANGUAGE,
3570 SOURCE_LANG,
3571 SFWT_FLAG,
3572 MEANING,
3573 DESCRIPTION,
3574 CREATED_BY,
3575 CREATION_DATE,
3576 LAST_UPDATED_BY,
3577 LAST_UPDATE_LOGIN,
3578 LAST_UPDATE_DATE)
3579 VALUES
3580 (C1.LOOKUP_CODE,
3581 C1.LANGUAGE,
3582 C1.SOURCE_LANG,
3583 'N',
3584 C1.MEANING,
3585 C1.DESCRIPTION,
3586 C1.CREATED_BY,
3587 C1.CREATION_DATE,
3588 1,
3589 0,
3590 SYSDATE);
3591 END IF ;
3592 END LOOP ;
3593 --To populate source_lang and sfwt_flag for bug 3807465
3594
3595 BEGIN
3596 FOR C4 IN CUR_CHECK_SOURCELANG_NULL
3597 LOOP
3598 v_sourcelang_null:= TRUE;
3599 END LOOP ;
3600
3601 IF v_sourcelang_null= TRUE THEN
3602 UPDATE OKS_COV_TYPES_TL
3603 SET SOURCE_LANG=userenv('LANG'),SFWT_FLAG='N'
3604 WHERE SOURCE_LANG IS NULL;
3605 END IF;
3606 EXCEPTION
3607 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3608 ROLLBACK ;
3609 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3610 (
3611 l_api_name2,
3612 G_PKG_NAME,
3613 'OKC_API.G_RET_STS_ERROR',
3614 x_msg_count,
3615 x_msg_data,
3616 '_PVT'
3617 );
3618 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3619 ROLLBACK ;
3620 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3621 (
3622 l_api_name2,
3623 G_PKG_NAME,
3624 'OKC_API.G_RET_STS_UNEXP_ERROR',
3625 x_msg_count,
3626 x_msg_data,
3627 '_PVT'
3628 );
3629 WHEN OTHERS THEN
3630 ROLLBACK ;
3631 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3632 (
3633 l_api_name2,
3634 G_PKG_NAME,
3635 'OTHERS',
3636 x_msg_count,
3637 x_msg_data,
3638 '_PVT'
3639 );
3640 END;
3641 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
3642
3643 EXCEPTION
3644 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3645 ROLLBACK ;
3646 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3647 (
3648 l_api_name,
3649 G_PKG_NAME,
3650 'OKC_API.G_RET_STS_ERROR',
3651 x_msg_count,
3652 x_msg_data,
3653 '_PVT'
3654 );
3655
3656 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3657 ROLLBACK ;
3658 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3659 (
3660 l_api_name,
3661 G_PKG_NAME,
3662 'OKC_API.G_RET_STS_UNEXP_ERROR',
3663 x_msg_count,
3664 x_msg_data,
3665 '_PVT'
3666 );
3667 WHEN OTHERS THEN
3668 ROLLBACK ;
3669 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3670 (
3671 l_api_name,
3672 G_PKG_NAME,
3673 'OTHERS',
3674 x_msg_count,
3675 x_msg_data,
3676 '_PVT'
3677 );
3678
3679
3680 END MIGRATE_COVERAGE_TYPES;
3681
3682 /* chkrishn old procedure
3683 ---------------------------------------------------------------------------
3684 -- PROCEDURE Delete Coverage Types
3685 ---------------------------------------------------------------------------
3686
3687
3688 PROCEDURE DELETE_COVERAGE_TYPES( p_api_version IN NUMBER,
3689 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3690 x_return_status OUT NOCOPY VARCHAR2,
3691 x_msg_count OUT NOCOPY NUMBER,
3692 x_msg_data OUT NOCOPY VARCHAR2)
3693 IS
3694
3695 l_api_version CONSTANT NUMBER := 1;
3696 l_api_name CONSTANT VARCHAR2(40) := 'V_DELETE_COVEREAGE_TYPES';
3697 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3698
3699 BEGIN
3700
3701 BEGIN
3702 delete FND_LOOKUP_VALUES
3703 where lookup_code in
3704 (SELECT code FROM oks_cov_types_v)
3705 and lookup_type = 'OKSCVETYPE';
3706 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
3707 EXCEPTION
3708 WHEN OTHERS THEN
3709 NULL;
3710
3711 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3712 (
3713 l_api_name,
3714 G_PKG_NAME,
3715 'OTHERS',
3716 x_msg_count,
3717 x_msg_data,
3718 '_PVT'
3719 );
3720 END;
3721 BEGIN
3722 delete from FND_LOOKUP_TYPES_TL
3723 where LOOKUP_TYPE = 'OKSCVETYPE'
3724 and not exists (SELECT 'x' from FND_LOOKUP_VALUES
3725 WHERE lookup_type = 'OKSCVETYPE');
3726 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
3727
3728 EXCEPTION
3729 WHEN OTHERS THEN
3730 NULL;
3731
3732 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3733 (
3734 l_api_name,
3735 G_PKG_NAME,
3736 'OTHERS',
3737 x_msg_count,
3738 x_msg_data,
3739 '_PVT'
3740 );
3741 END;
3742 BEGIN
3743 delete from FND_LOOKUP_TYPES
3744 where LOOKUP_TYPE = 'OKSCVETYPE'
3745 and not exists (SELECT 'x' from FND_LOOKUP_VALUES
3746 WHERE lookup_type = 'OKSCVETYPE');
3747
3748 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
3749 EXCEPTION
3750 WHEN OTHERS THEN
3751 NULL;
3752
3753 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3754 (
3755 l_api_name,
3756 G_PKG_NAME,
3757 'OTHERS',
3758 x_msg_count,
3759 x_msg_data,
3760 '_PVT'
3761 );
3762
3763 END ;
3764 END ;
3765 */
3766
3767 ---------------------------------------------------------------------------
3768 -- PROCEDURE Delete Coverage Types
3769 ---------------------------------------------------------------------------
3770
3771
3772 PROCEDURE DELETE_COVERAGE_TYPES( p_api_version IN NUMBER,
3773 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3774 x_return_status OUT NOCOPY VARCHAR2,
3775 x_msg_count OUT NOCOPY NUMBER,
3776 x_msg_data OUT NOCOPY VARCHAR2)
3777 IS
3778
3779 l_api_version CONSTANT NUMBER := 1;
3780 l_api_name CONSTANT VARCHAR2(40) := 'V_DELETE_COVEREAGE_TYPES';
3781 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3782 --BEGIN
3783 BEGIN
3784
3785 update FND_LOOKUP_VALUES
3786 set END_DATE_ACTIVE = sysdate-1
3787 where lookup_code in
3788 (SELECT code FROM oks_cov_types_v)
3789 and lookup_type = 'OKSCVETYPE';
3790
3791
3792 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
3793 EXCEPTION
3794 WHEN OTHERS THEN
3795 NULL;
3796
3797 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3798 (
3799 l_api_name,
3800 G_PKG_NAME,
3801 'OTHERS',
3802 x_msg_count,
3803 x_msg_data,
3804 '_PVT'
3805 );
3806 END delete_coverage_types;
3807
3808
3809 END;