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