[Home] [Help]
PACKAGE BODY: APPS.OKL_VP_K_ARTICLE_PVT
Source
1 Package Body OKL_VP_K_ARTICLE_PVT AS
2 /* $Header: OKLRCARB.pls 120.1 2005/08/04 01:31:11 manumanu noship $ */
3 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
4 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_VP_K_ARTICLE_PVT';
5 G_CHILD_RECORD_FOUND CONSTANT varchar2(200) := 'OKC_CHILD_RECORD_FOUND';
6 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
7 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
8
9 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
10 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLERRM';
11 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLCODE';
12 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
13 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
14 G_API_TYPE VARCHAR2(3) := 'PVT';
15 ----------------------------------------------------------------------------
16 --Function to populate the articles record to be copied.
17 ----------------------------------------------------------------------------
18 FUNCTION get_catv_rec(p_cat_id IN NUMBER,
19 x_catv_rec OUT NOCOPY catv_rec_type)
20 RETURN VARCHAR2 IS
21 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
22 l_no_data_found BOOLEAN := TRUE;
23 CURSOR c_catv_rec IS
24 SELECT ID,
25 CHR_ID,
26 CLE_ID,
27 CAT_ID,
28 SFWT_FLAG,
29 SAV_SAE_ID,
30 SAV_SAV_RELEASE,
31 SBT_CODE,
32 DNZ_CHR_ID,
33 COMMENTS,
34 FULLTEXT_YN,
35 VARIATION_DESCRIPTION,
36 NAME,
37 -- TEXT,
38 ATTRIBUTE_CATEGORY,
39 ATTRIBUTE1,
40 ATTRIBUTE2,
41 ATTRIBUTE3,
42 ATTRIBUTE4,
43 ATTRIBUTE5,
44 ATTRIBUTE6,
45 ATTRIBUTE7,
46 ATTRIBUTE8,
47 ATTRIBUTE9,
48 ATTRIBUTE10,
49 ATTRIBUTE11,
50 ATTRIBUTE12,
51 ATTRIBUTE13,
52 ATTRIBUTE14,
53 ATTRIBUTE15,
54 CAT_TYPE
55 FROM OKC_K_ARTICLES_V
56 WHERE ID = p_cat_id;
57 BEGIN
58 OPEN c_catv_rec;
59 FETCH c_catv_rec
60 INTO x_catv_rec.ID,
61 x_catv_rec.CHR_ID,
62 x_catv_rec.CLE_ID,
63 x_catv_rec.CAT_ID,
64 x_catv_rec.SFWT_FLAG,
65 x_catv_rec.SAV_SAE_ID,
66 x_catv_rec.SAV_SAV_RELEASE,
67 x_catv_rec.SBT_CODE,
68 x_catv_rec.DNZ_CHR_ID,
69 x_catv_rec.COMMENTS,
70 x_catv_rec.FULLTEXT_YN,
71 x_catv_rec.VARIATION_DESCRIPTION,
72 x_catv_rec.NAME,
73 -- x_catv_rec.TEXT,
74 x_catv_rec.ATTRIBUTE_CATEGORY,
75 x_catv_rec.ATTRIBUTE1,
76 x_catv_rec.ATTRIBUTE2,
77 x_catv_rec.ATTRIBUTE3,
78 x_catv_rec.ATTRIBUTE4,
79 x_catv_rec.ATTRIBUTE5,
80 x_catv_rec.ATTRIBUTE6,
81 x_catv_rec.ATTRIBUTE7,
82 x_catv_rec.ATTRIBUTE8,
83 x_catv_rec.ATTRIBUTE9,
84 x_catv_rec.ATTRIBUTE10,
85 x_catv_rec.ATTRIBUTE11,
86 x_catv_rec.ATTRIBUTE12,
87 x_catv_rec.ATTRIBUTE13,
88 x_catv_rec.ATTRIBUTE14,
89 x_catv_rec.ATTRIBUTE15,
90 x_catv_rec.CAT_TYPE;
91 l_no_data_found := c_catv_rec%NOTFOUND;
92 CLOSE c_catv_rec;
93 IF l_no_data_found THEN
94 l_return_status := OKC_API.G_RET_STS_ERROR;
95 return(l_return_status);
96 ELSE
97 return(l_return_status);
98 END IF;
99 EXCEPTION
100 WHEN OTHERS THEN
101 -- store SQL error message on message stack for caller
102 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
103 -- notify caller of an UNEXPECTED error
104 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
105 return(l_return_status);
106 END get_catv_rec;
107 ----------------------------------------------------------------------------
108 --Function to populate the articles record to be copied.
109 ----------------------------------------------------------------------------
110 FUNCTION get_catv_rec(p_sae_id IN NUMBER,
111 p_sav_release IN VARCHAR2,
112 x_catv_rec OUT NOCOPY catv_rec_type)
113 RETURN VARCHAR2 IS
114 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
115 l_no_data_found BOOLEAN := TRUE;
116 CURSOR c_catv_rec IS
117 SELECT null,
118 null,
119 null,
120 null,
121 OKC_API.G_TRUE,
122 null,
123 null,
124 sae.SBT_CODE,
125 null,
126 null,
127 'Y',
128 null,
129 sae.NAME,
130 -- saev.TEXT,
131 null,
132 null,
133 null,
134 null,
135 null,
136 null,
137 null,
138 null,
139 null,
140 null,
141 null,
142 null,
143 null,
144 null,
145 null,
146 null,
147 'NSD'
148 FROM okc_std_art_versions_v saev
149 , okc_std_articles_v sae
150 WHERE sae.ID = p_sae_id
151 AND saev.sav_release = p_sav_release
152 AND sae.ID = saev.sae_id;
153 BEGIN
154 OPEN c_catv_rec;
155 FETCH c_catv_rec
156 INTO x_catv_rec.ID,
157 x_catv_rec.CHR_ID,
158 x_catv_rec.CLE_ID,
159 x_catv_rec.CAT_ID,
160 x_catv_rec.SFWT_FLAG,
161 x_catv_rec.SAV_SAE_ID,
162 x_catv_rec.SAV_SAV_RELEASE,
163 x_catv_rec.SBT_CODE,
164 x_catv_rec.DNZ_CHR_ID,
165 x_catv_rec.COMMENTS,
166 x_catv_rec.FULLTEXT_YN,
167 x_catv_rec.VARIATION_DESCRIPTION,
168 x_catv_rec.NAME,
169 -- x_catv_rec.TEXT,
170 x_catv_rec.ATTRIBUTE_CATEGORY,
171 x_catv_rec.ATTRIBUTE1,
172 x_catv_rec.ATTRIBUTE2,
173 x_catv_rec.ATTRIBUTE3,
174 x_catv_rec.ATTRIBUTE4,
175 x_catv_rec.ATTRIBUTE5,
176 x_catv_rec.ATTRIBUTE6,
177 x_catv_rec.ATTRIBUTE7,
178 x_catv_rec.ATTRIBUTE8,
179 x_catv_rec.ATTRIBUTE9,
180 x_catv_rec.ATTRIBUTE10,
181 x_catv_rec.ATTRIBUTE11,
182 x_catv_rec.ATTRIBUTE12,
183 x_catv_rec.ATTRIBUTE13,
184 x_catv_rec.ATTRIBUTE14,
185 x_catv_rec.ATTRIBUTE15,
186 x_catv_rec.CAT_TYPE;
187 l_no_data_found := c_catv_rec%NOTFOUND;
188 CLOSE c_catv_rec;
189 IF l_no_data_found THEN
190 l_return_status := OKC_API.G_RET_STS_ERROR;
191 return(l_return_status);
192 ELSE
193 return(l_return_status);
194 END IF;
195 EXCEPTION
196 WHEN OTHERS THEN
197 -- store SQL error message on message stack for caller
198 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
199 -- notify caller of an UNEXPECTED error
200 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
201 return(l_return_status);
202 END get_catv_rec;
203
204 -- Start of comments
205 --
206 -- Procedure Name : create_article_by_reference
207 -- Description : creates a reference to a standard article
208 -- Business Rules :
209 -- Parameters :
210 -- Version : 1.0
211 -- End of comments
212 PROCEDURE create_article_by_reference(
213 p_api_version IN NUMBER,
214 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
215 x_return_status OUT NOCOPY VARCHAR2,
216 x_msg_count OUT NOCOPY NUMBER,
217 x_msg_data OUT NOCOPY VARCHAR2,
218 p_sae_id IN NUMBER,
219 p_sae_release IN VARCHAR2,
220 p_chr_id IN NUMBER,
221 p_cle_id IN NUMBER DEFAULT NULL,
222 p_comments IN VARCHAR2
223 ) AS
224 l_api_name VARCHAR2(30) := 'create_article_by_reference';
225 l_api_version CONSTANT NUMBER := 1.0;
226 l_catv_rec catv_rec_type;
227 x_catv_rec catv_rec_type;
228 BEGIN
229 x_return_status := OKL_API.START_ACTIVITY(
230 p_api_name => l_api_name,
231 p_pkg_name => g_pkg_name,
232 p_init_msg_list => p_init_msg_list,
233 l_api_version => l_api_version,
234 p_api_version => p_api_version,
235 p_api_type => g_api_type,
236 x_return_status => x_return_status);
237 -- check if activity started successfully
238 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
239 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
240 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
241 raise OKL_API.G_EXCEPTION_ERROR;
242 End If;
243
244 x_return_status := get_catv_rec(p_sae_id => p_sae_id,
245 p_sav_release => p_sae_release,
246 x_catv_rec => l_catv_rec);
247 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
248 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
249 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
250 raise OKC_API.G_EXCEPTION_ERROR;
251 End If;
252
253 l_catv_rec.dnz_chr_id := p_chr_id;
254 if (p_cle_id is null) then
255 l_catv_rec.chr_id := p_chr_id;
256 l_catv_rec.cle_id := null;
257 else
258 l_catv_rec.chr_id := null;
259 l_catv_rec.cle_id := p_cle_id;
260 end if;
261 l_catv_rec.object_version_number := 1.0;
262 l_catv_rec.sfwt_flag := OKC_API.G_TRUE;
263 l_catv_rec.sav_sae_id := p_sae_id;
264 l_catv_rec.sav_sav_release := p_sae_release;
265 l_catv_rec.cat_type := 'STA';
266 l_catv_rec.fulltext_yn := 'Y';
267 l_catv_rec.comments := p_comments;
268 l_catv_rec.NAME := NULL;
269 l_catv_rec.SBT_CODE := NULL;
270
271
272 OKL_OKC_MIGRATION_A_PVT.insert_row(
273 p_api_version => l_api_version,
274 p_init_msg_list => p_init_msg_list,
275 x_return_status => x_return_status,
276 x_msg_count => x_msg_count,
277 x_msg_data => x_msg_data,
278 p_catv_rec => l_catv_rec,
279 x_catv_rec => x_catv_rec);
280 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
281 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
282 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
283 raise OKC_API.G_EXCEPTION_ERROR;
284 End If;
285 -- x_cat_id := x_catv_rec.id;
286 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
287 x_msg_data => x_msg_data);
288 EXCEPTION
289 when OKL_API.G_EXCEPTION_ERROR then
290 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
291 p_api_name => l_api_name,
292 p_pkg_name => g_pkg_name,
293 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
294 x_msg_count => x_msg_count,
295 x_msg_data => x_msg_data,
296 p_api_type => g_api_type);
297 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
298 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
299 p_api_name => l_api_name,
300 p_pkg_name => g_pkg_name,
301 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
302 x_msg_count => x_msg_count,
303 x_msg_data => x_msg_data,
304 p_api_type => g_api_type);
305 when OTHERS then
306 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
307 p_api_name => l_api_name,
308 p_pkg_name => g_pkg_name,
309 p_exc_name => 'OTHERS',
310 x_msg_count => x_msg_count,
311 x_msg_data => x_msg_data,
312 p_api_type => g_api_type);
313 END;
314
315
316 -- Start of comments
317 --
318 -- Procedure Name : copy_article
319 -- Description : creates a copy of a standard article
320 -- Business Rules :
321 -- Parameters :
322 -- Version : 1.0
323 -- End of comments
324 PROCEDURE create_article_by_copy(
325 p_api_version IN NUMBER,
326 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
327 x_return_status OUT NOCOPY VARCHAR2,
328 x_msg_count OUT NOCOPY NUMBER,
329 x_msg_data OUT NOCOPY VARCHAR2,
330 p_sae_id IN NUMBER,
331 p_sae_release IN VARCHAR2,
332 p_chr_id IN NUMBER,
333 p_cle_id IN NUMBER DEFAULT NULL,
334 p_comments IN VARCHAR2
335 ) AS
336 l_api_name VARCHAR2(30) := 'create_article_by_copy';
337 l_api_version CONSTANT NUMBER := 1.0;
338 l_catv_rec catv_rec_type;
339 x_catv_rec catv_rec_type;
340
341 CURSOR cur_text IS
342 SELECT text
343 FROM okc_std_art_versions_v
344 WHERE sae_id = p_sae_id
345 AND sav_release = p_sae_release;
346
347 l_clob_text CLOB;
348
349 BEGIN
350 x_return_status := OKL_API.START_ACTIVITY(
351 p_api_name => l_api_name,
352 p_pkg_name => g_pkg_name,
353 p_init_msg_list => p_init_msg_list,
354 l_api_version => l_api_version,
355 p_api_version => p_api_version,
356 p_api_type => g_api_type,
357 x_return_status => x_return_status);
358 -- check if activity started successfully
359 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
360 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
361 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
362 raise OKL_API.G_EXCEPTION_ERROR;
363 End If;
364 x_return_status := get_catv_rec(p_sae_id => p_sae_id,
365 p_sav_release => p_sae_release,
366 x_catv_rec => l_catv_rec);
367 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
368 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
369 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
370 raise OKC_API.G_EXCEPTION_ERROR;
371 End If;
372 l_catv_rec.dnz_chr_id := p_chr_id;
373 if (p_cle_id is null) then
374 l_catv_rec.chr_id := p_chr_id;
375 l_catv_rec.cle_id := null;
376 else
377 l_catv_rec.chr_id := null;
378 l_catv_rec.cle_id := p_cle_id;
379 end if;
380 l_catv_rec.object_version_number := 1.0;
381 l_catv_rec.sfwt_flag := OKC_API.G_TRUE;
382 l_catv_rec.comments := p_comments;
383 l_catv_rec.FULLTEXT_YN := NULL;
384 l_catv_rec.VARIATION_DESCRIPTION := NULL;
385 l_catv_rec.SAV_SAE_ID := NULL;
386 l_catv_rec.SAV_SAV_RELEASE := NULL;
387
388
389 OKL_OKC_MIGRATION_A_PVT.insert_row(
390 p_api_version => l_api_version,
391 p_init_msg_list => p_init_msg_list,
392 x_return_status => x_return_status,
393 x_msg_count => x_msg_count,
394 x_msg_data => x_msg_data,
395 p_catv_rec => l_catv_rec,
396 x_catv_rec => x_catv_rec);
397 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
398 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
399 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
400 raise OKC_API.G_EXCEPTION_ERROR;
401 End If;
402 -- Begin updating text
403 OPEN cur_text;
404 FETCH cur_text INTO l_clob_text;
405 IF(cur_text%found) THEN
406 NULL;
407 CLOSE cur_text;
408 ELSE
409 CLOSE cur_text;
410 --OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'article text cannot copy');
411 RAISE OKL_API.G_EXCEPTION_ERROR;
412 END IF;
413
414 -- Fix for bug 3254597. OKC 11.5.10 COMPATIBLE FIXES
415
416 --UPDATE okc_k_articles_v SET text = l_clob_text
417 --WHERE id = x_catv_rec.id;
418
419 x_return_status := OKC_UTIL.Copy_Articles_Varied_Text(
420 p_article_id => x_catv_rec.id, -- contract article id
421 p_sae_id => p_sae_id, -- standard article id
422 lang => USERENV('LANG'));
423 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
424 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
425 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
426 raise OKC_API.G_EXCEPTION_ERROR;
427 End If;
428
429 -- END FIX 3254597 --
430
431 -- x_cat_id := x_catv_rec.id;
432 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
433 x_msg_data => x_msg_data);
434 EXCEPTION
435 when OKC_API.G_EXCEPTION_ERROR then
436 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
437 p_api_name => l_api_name,
438 p_pkg_name => g_pkg_name,
439 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
440 x_msg_count => x_msg_count,
441 x_msg_data => x_msg_data,
442 p_api_type => g_api_type);
443 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
444 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
445 p_api_name => l_api_name,
446 p_pkg_name => g_pkg_name,
447 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
448 x_msg_count => x_msg_count,
449 x_msg_data => x_msg_data,
450 p_api_type => g_api_type);
451 when OTHERS then
452 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
453 p_api_name => l_api_name,
454 p_pkg_name => g_pkg_name,
455 p_exc_name => 'OTHERS',
456 x_msg_count => x_msg_count,
457 x_msg_data => x_msg_data,
458 p_api_type => g_api_type);
459 END;
460
461
462 -- Start of comments
463 --
464 -- Procedure Name : add_language_k_article
465 -- Description :
466 -- Business Rules :
467 -- Parameters :
468 -- Version : 1.0
469 -- End of comments
470 procedure add_language_k_article is
471 begin
472 null;
473 -- okl_okc_migration_a_pvt.add_language;
474 end add_language_k_article;
475
476
477 -- Start of comments
478 --
479 -- Procedure Name : create_k_article
480 -- Description :
481 -- Business Rules :
482 -- Parameters :
483 -- Version : 1.0
484 -- End of comments
485 procedure create_k_article(p_api_version IN NUMBER,
486 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
487 x_return_status OUT NOCOPY VARCHAR2,
488 x_msg_count OUT NOCOPY NUMBER,
489 x_msg_data OUT NOCOPY VARCHAR2,
490 p_catv_rec IN catv_rec_type,
491 x_catv_rec OUT NOCOPY catv_rec_type) is
492 l_catv_rec catv_rec_type := p_catv_rec;
493 l_cvmv_rec OKC_CVM_PVT.cvmv_rec_type;
494 x_out_rec OKC_CVM_PVT.cvmv_rec_type;
495 l_api_name VARCHAR2(30) := 'create_k_article';
496 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
497 begin
498
499 x_return_status := OKL_API.G_RET_STS_SUCCESS;
500 x_return_status := OKL_API.START_ACTIVITY(
501 p_api_name => l_api_name,
502 p_pkg_name => g_pkg_name,
503 p_init_msg_list => p_init_msg_list,
504 l_api_version => p_api_version,
505 p_api_version => p_api_version,
506 p_api_type => g_api_type,
507 x_return_status => x_return_status);
508 -- check if activity started successfully
509 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
510 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
511 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
512 raise OKL_API.G_EXCEPTION_ERROR;
513 End If;
514
515 -- Not null Validation for Article Name
516 IF ((l_catv_rec.name = OKL_API.G_MISS_CHAR) OR (l_catv_rec.name IS NULL)) THEN
517 OKL_API.SET_MESSAGE(p_app_name => g_app_name,p_msg_name => 'OKL_ARTICLE_NAME_REQUIRED');
518 x_return_status :=okl_api.g_ret_sts_error;
519 RAISE OKL_API.G_EXCEPTION_ERROR;
520 END IF;
521
522 IF (p_catv_rec.cat_type = 'STA') THEN
523 create_article_by_reference(
524 p_api_version,
525 p_init_msg_list,
526 x_return_status,
527 x_msg_count,
528 x_msg_data,
529 p_catv_rec.sav_sae_id,
530 p_catv_rec.sav_sav_release,
531 p_catv_rec.chr_id,
532 p_catv_rec.cle_id,
533 p_catv_rec.comments
534 );
535 ELSIF (p_catv_rec.cat_type = 'NSD') THEN
536 create_article_by_copy(
537 p_api_version,
538 p_init_msg_list,
539 x_return_status,
540 x_msg_count,
541 x_msg_data,
542 p_catv_rec.sav_sae_id,
543 p_catv_rec.sav_sav_release,
544 p_catv_rec.chr_id,
545 p_catv_rec.cle_id,
546 p_catv_rec.comments
547 );
548 ELSE
549 -- raise an error saying that the parameter cat_type can only have values in ('STA','NSD')
550 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'cat_type values in (STA,NSD)');
551 RAISE OKL_API.G_EXCEPTION_ERROR;
552 END IF;
553
554 /* Manu 29-Jun-2005 Begin */
555 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
556 OKL_VENDOR_PROGRAM_PVT.passed_to_incomplete(p_api_version => p_api_version
557 ,p_init_msg_list => p_init_msg_list
558 ,x_return_status => x_return_status
559 ,x_msg_count => x_msg_count
560 ,x_msg_data => x_msg_data
561 ,p_program_id => p_catv_rec.chr_id
562 );
563 END IF;
564 /* Manu 29-Jun-2005 END */
565
566
567 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
568 x_msg_data => x_msg_data);
569 EXCEPTION
570 when OKL_API.G_EXCEPTION_ERROR then
571 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
572 p_api_name => l_api_name,
573 p_pkg_name => g_pkg_name,
574 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
575 x_msg_count => x_msg_count,
576 x_msg_data => x_msg_data,
577 p_api_type => g_api_type);
578 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
579 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
580 p_api_name => l_api_name,
581 p_pkg_name => g_pkg_name,
582 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
583 x_msg_count => x_msg_count,
584 x_msg_data => x_msg_data,
585 p_api_type => g_api_type);
586 when OTHERS then
587 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
588 p_api_name => l_api_name,
589 p_pkg_name => g_pkg_name,
590 p_exc_name => 'OTHERS',
591 x_msg_count => x_msg_count,
592 x_msg_data => x_msg_data,
593 p_api_type => g_api_type);
594 end create_k_article;
595
596
597 -- Start of comments
598 --
599 -- Procedure Name : update_k_article
600 -- Description :
601 -- Business Rules :
602 -- Parameters :
603 -- Version : 1.0
604 -- End of comments
605 procedure update_k_article(p_api_version IN NUMBER,
606 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
607 x_return_status OUT NOCOPY VARCHAR2,
608 x_msg_count OUT NOCOPY NUMBER,
609 x_msg_data OUT NOCOPY VARCHAR2,
610 p_catv_rec IN catv_rec_type,
611 x_catv_rec OUT NOCOPY catv_rec_type) is
612 l_catv_rec catv_rec_type := p_catv_rec;
613
614 begin
615 if (l_catv_rec.CAT_TYPE = 'STA') then
616 l_catv_rec.NAME := NULL;
617 l_catv_rec.SBT_CODE := NULL;
618 elsif (l_catv_rec.CAT_TYPE = 'NSD') then
619 l_catv_rec.FULLTEXT_YN := NULL;
620 l_catv_rec.VARIATION_DESCRIPTION := NULL;
621 l_catv_rec.SAV_SAE_ID := NULL;
622 l_catv_rec.SAV_SAV_RELEASE := NULL;
623 end if;
624 okl_okc_migration_a_pvt.update_row(p_api_version => p_api_version,
625 p_init_msg_list => p_init_msg_list,
626 x_msg_count => x_msg_count,
627 x_msg_data => x_msg_data,
628 x_return_status => x_return_status,
629 p_catv_rec => l_catv_rec,
630 x_catv_rec => x_catv_rec);
631
632 /* Manu 29-Jun-2005 Begin */
633 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
634 OKL_VENDOR_PROGRAM_PVT.passed_to_incomplete(p_api_version => p_api_version
635 ,p_init_msg_list => p_init_msg_list
636 ,x_return_status => x_return_status
637 ,x_msg_count => x_msg_count
638 ,x_msg_data => x_msg_data
639 ,p_program_id => x_catv_rec.chr_id
640 );
641 END IF;
642 /* Manu 29-Jun-2005 END */
643
644
645 end update_k_article;
646
647
648 -- Start of comments
649 --
650 -- Procedure Name : delete_k_article
651 -- Description :
652 -- Business Rules :
653 -- Parameters :
654 -- Version : 1.0
655 -- End of comments
656 procedure delete_k_article(p_api_version IN NUMBER,
657 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
658 x_return_status OUT NOCOPY VARCHAR2,
659 x_msg_count OUT NOCOPY NUMBER,
660 x_msg_data OUT NOCOPY VARCHAR2,
661 p_catv_rec IN catv_rec_type) is
662 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
663 l_chr_id OKL_VP_ASSOCIATIONS.CHR_ID%TYPE;
664
665
666 CURSOR cur_get_chr_id IS
667 SELECT dnz_chr_id
668 FROM okc_k_articles_v
669 WHERE id = p_catv_rec.id;
670 BEGIN
671
672
673 /* Manu 29-Jun-2005 Begin */
674 OPEN cur_get_chr_id;
675 FETCH cur_get_chr_id INTO l_chr_id;
676 CLOSE cur_get_chr_id;
677 /* Manu 29-Jun-2005 END */
678
679 okl_okc_migration_a_pvt.delete_row(p_api_version => p_api_version,
680 p_init_msg_list => p_init_msg_list,
681 x_msg_count => x_msg_count,
682 x_msg_data => x_msg_data,
683 x_return_status => x_return_status,
684 p_catv_rec => p_catv_rec);
685
686
687 /* Manu 29-Jun-2005 Begin */
688 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
689 OKL_VENDOR_PROGRAM_PVT.passed_to_incomplete(p_api_version => p_api_version
690 ,p_init_msg_list => p_init_msg_list
691 ,x_return_status => x_return_status
692 ,x_msg_count => x_msg_count
693 ,x_msg_data => x_msg_data
694 ,p_program_id => l_chr_id
695 );
696 END IF;
697 /* Manu 29-Jun-2005 END */
698
699
700
701 END delete_k_article;
702
703 function emptyClob return clob is
704 c1 clob;
705 begin
706 dbms_lob.createtemporary(c1,true);
707 -- c1 := empty_clob();
708 dbms_lob.open(c1,dbms_lob.lob_readwrite);
709 dbms_lob.write(c1,1,1,' ');
710 return c1;
711 end;
712
713 END; -- Package Body OKL_VP_K_ARTICLE_PVT