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