[Home] [Help]
PACKAGE BODY: APPS.OKL_ARTICLE_PUB
Source
1 PACKAGE BODY OKL_ARTICLE_PUB AS
2 /* $Header: OKLPKATB.pls 115.2 2002/03/24 23:21:43 pkm ship $ */
3 G_API_TYPE VARCHAR2(3) := 'PUB';
4 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
5 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
6 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
7
8
9 ----------------------------------------------------------------------------
10 --Function to populate the articles record to be copied.
11 ----------------------------------------------------------------------------
12 FUNCTION get_catv_rec(p_cat_id IN NUMBER,
13 x_catv_rec OUT NOCOPY okc_k_article_pub.catv_rec_type)
14 RETURN VARCHAR2 IS
15 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
16 l_no_data_found BOOLEAN := TRUE;
17
18 CURSOR c_catv_rec IS
19 SELECT ID,
20 CHR_ID,
21 CLE_ID,
22 CAT_ID,
23 SFWT_FLAG,
24 SAV_SAE_ID,
25 SAV_SAV_RELEASE,
26 SBT_CODE,
27 DNZ_CHR_ID,
28 COMMENTS,
29 FULLTEXT_YN,
30 VARIATION_DESCRIPTION,
31 NAME,
32 TEXT,
33 ATTRIBUTE_CATEGORY,
34 ATTRIBUTE1,
35 ATTRIBUTE2,
36 ATTRIBUTE3,
37 ATTRIBUTE4,
38 ATTRIBUTE5,
39 ATTRIBUTE6,
40 ATTRIBUTE7,
41 ATTRIBUTE8,
42 ATTRIBUTE9,
43 ATTRIBUTE10,
44 ATTRIBUTE11,
45 ATTRIBUTE12,
46 ATTRIBUTE13,
47 ATTRIBUTE14,
48 ATTRIBUTE15,
49 CAT_TYPE
50 FROM OKC_K_ARTICLES_V
51 WHERE ID = p_cat_id;
52 BEGIN
53 OPEN c_catv_rec;
54 FETCH c_catv_rec
55 INTO x_catv_rec.ID,
56 x_catv_rec.CHR_ID,
57 x_catv_rec.CLE_ID,
58 x_catv_rec.CAT_ID,
59 x_catv_rec.SFWT_FLAG,
60 x_catv_rec.SAV_SAE_ID,
61 x_catv_rec.SAV_SAV_RELEASE,
62 x_catv_rec.SBT_CODE,
63 x_catv_rec.DNZ_CHR_ID,
64 x_catv_rec.COMMENTS,
65 x_catv_rec.FULLTEXT_YN,
66 x_catv_rec.VARIATION_DESCRIPTION,
67 x_catv_rec.NAME,
68 x_catv_rec.TEXT,
69 x_catv_rec.ATTRIBUTE_CATEGORY,
70 x_catv_rec.ATTRIBUTE1,
71 x_catv_rec.ATTRIBUTE2,
72 x_catv_rec.ATTRIBUTE3,
73 x_catv_rec.ATTRIBUTE4,
74 x_catv_rec.ATTRIBUTE5,
75 x_catv_rec.ATTRIBUTE6,
76 x_catv_rec.ATTRIBUTE7,
77 x_catv_rec.ATTRIBUTE8,
78 x_catv_rec.ATTRIBUTE9,
79 x_catv_rec.ATTRIBUTE10,
80 x_catv_rec.ATTRIBUTE11,
81 x_catv_rec.ATTRIBUTE12,
82 x_catv_rec.ATTRIBUTE13,
83 x_catv_rec.ATTRIBUTE14,
84 x_catv_rec.ATTRIBUTE15,
85 x_catv_rec.CAT_TYPE;
86
87 l_no_data_found := c_catv_rec%NOTFOUND;
88 CLOSE c_catv_rec;
89 IF l_no_data_found THEN
90 l_return_status := OKC_API.G_RET_STS_ERROR;
91 return(l_return_status);
92 ELSE
93 return(l_return_status);
94 END IF;
95 EXCEPTION
96 WHEN OTHERS THEN
97 -- store SQL error message on message stack for caller
98 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
99 -- notify caller of an UNEXPECTED error
100 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
101 return(l_return_status);
102
103 END get_catv_rec;
104
105 ----------------------------------------------------------------------------
106 --Function to populate the articles record to be copied.
107 ----------------------------------------------------------------------------
108 FUNCTION get_catv_rec(p_sae_id IN NUMBER,
109 p_sav_release IN VARCHAR2,
110 x_catv_rec OUT NOCOPY okc_k_article_pub.catv_rec_type)
111 RETURN VARCHAR2 IS
112 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
113 l_no_data_found BOOLEAN := TRUE;
114
115 CURSOR c_catv_rec IS
116 SELECT null,
117 null,
118 null,
119 null,
120 OKC_API.G_TRUE,
121 null,
122 null,
123 sae.SBT_CODE,
124 null,
125 null,
126 'Y',
127 null,
128 sae.NAME,
129 saev.TEXT,
130 null,
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 'NSD'
147 FROM okc_std_art_versions_v saev
148 , okc_std_articles_v sae
149 WHERE sae.ID = p_sae_id
150 AND saev.sav_release = p_sav_release
151 AND sae.ID = saev.sae_id;
152 BEGIN
153 OPEN c_catv_rec;
154 FETCH c_catv_rec
155 INTO x_catv_rec.ID,
156 x_catv_rec.CHR_ID,
157 x_catv_rec.CLE_ID,
158 x_catv_rec.CAT_ID,
159 x_catv_rec.SFWT_FLAG,
160 x_catv_rec.SAV_SAE_ID,
161 x_catv_rec.SAV_SAV_RELEASE,
162 x_catv_rec.SBT_CODE,
163 x_catv_rec.DNZ_CHR_ID,
164 x_catv_rec.COMMENTS,
165 x_catv_rec.FULLTEXT_YN,
166 x_catv_rec.VARIATION_DESCRIPTION,
167 x_catv_rec.NAME,
168 x_catv_rec.TEXT,
169 x_catv_rec.ATTRIBUTE_CATEGORY,
170 x_catv_rec.ATTRIBUTE1,
171 x_catv_rec.ATTRIBUTE2,
172 x_catv_rec.ATTRIBUTE3,
173 x_catv_rec.ATTRIBUTE4,
174 x_catv_rec.ATTRIBUTE5,
175 x_catv_rec.ATTRIBUTE6,
176 x_catv_rec.ATTRIBUTE7,
177 x_catv_rec.ATTRIBUTE8,
178 x_catv_rec.ATTRIBUTE9,
179 x_catv_rec.ATTRIBUTE10,
180 x_catv_rec.ATTRIBUTE11,
181 x_catv_rec.ATTRIBUTE12,
182 x_catv_rec.ATTRIBUTE13,
183 x_catv_rec.ATTRIBUTE14,
184 x_catv_rec.ATTRIBUTE15,
185 x_catv_rec.CAT_TYPE;
186
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
200 -- notify caller of an UNEXPECTED error
201 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
202 return(l_return_status);
203
204 END get_catv_rec;
205
206
207 -- Start of comments
208 --
209 -- Procedure Name : reference_article
210 -- Description : creates a reference to a standard article
211 -- Business Rules :
212 -- Parameters :
213 -- Version : 1.0
214 -- End of comments
215 PROCEDURE reference_article(
216 p_api_version IN NUMBER,
217 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
218 x_return_status OUT NOCOPY VARCHAR2,
219 x_msg_count OUT NOCOPY NUMBER,
220 x_msg_data OUT NOCOPY VARCHAR2,
221 p_sae_id IN NUMBER,
222 p_sae_release IN VARCHAR2,
223 p_chr_id IN NUMBER,
224 p_cle_id IN NUMBER DEFAULT NULL,
225 x_cat_id OUT NOCOPY NUMBER
226 ) AS
227
228 l_api_name VARCHAR2(30) := 'reference_article';
229 l_api_version CONSTANT NUMBER := 1.0;
230
231 l_catv_rec OKC_K_ARTICLE_PUB.catv_rec_type;
232 x_catv_rec OKC_K_ARTICLE_PUB.catv_rec_type;
233
234 BEGIN
235
236 x_return_status := OKC_API.START_ACTIVITY(
237 p_api_name => l_api_name,
238 p_pkg_name => g_pkg_name,
239 p_init_msg_list => p_init_msg_list,
240 l_api_version => l_api_version,
241 p_api_version => p_api_version,
242 p_api_type => g_api_type,
243 x_return_status => x_return_status);
244
245 -- check if activity started successfully
246 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
247 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
248 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
249 raise OKC_API.G_EXCEPTION_ERROR;
250 End If;
251
252 l_catv_rec.dnz_chr_id := p_chr_id;
253 if (p_cle_id is null) then
254 l_catv_rec.chr_id := p_chr_id;
255 l_catv_rec.cle_id := null;
256 else
257 l_catv_rec.chr_id := null;
258 l_catv_rec.cle_id := p_cle_id;
259 end if;
260 l_catv_rec.object_version_number := 1.0;
261 l_catv_rec.sfwt_flag := OKC_API.G_TRUE;
262 l_catv_rec.sav_sae_id := p_sae_id;
263 l_catv_rec.sav_sav_release := p_sae_release;
264 l_catv_rec.cat_type := 'STA';
265 l_catv_rec.fulltext_yn := 'Y';
266
267
268 OKC_K_ARTICLE_PUB.create_k_article(
269 p_api_version => l_api_version,
270 p_init_msg_list => p_init_msg_list,
271 x_return_status => x_return_status,
272 x_msg_count => x_msg_count,
273 x_msg_data => x_msg_data,
274 p_catv_rec => l_catv_rec,
275 x_catv_rec => x_catv_rec);
276
277 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
278 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
279 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
280 raise OKC_API.G_EXCEPTION_ERROR;
281 End If;
282
283 x_cat_id := x_catv_rec.id;
284
285 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count,
286 x_msg_data => x_msg_data);
287 EXCEPTION
288 when OKC_API.G_EXCEPTION_ERROR then
289 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
290 p_api_name => l_api_name,
291 p_pkg_name => g_pkg_name,
292 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
293 x_msg_count => x_msg_count,
294 x_msg_data => x_msg_data,
295 p_api_type => g_api_type);
296
297 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
298 x_return_status := OKC_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
306 when OTHERS then
307 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
308 p_api_name => l_api_name,
309 p_pkg_name => g_pkg_name,
310 p_exc_name => 'OTHERS',
311 x_msg_count => x_msg_count,
312 x_msg_data => x_msg_data,
313 p_api_type => g_api_type);
314 END;
315
316
317 -- Start of comments
318 --
319 -- Procedure Name : copy_article
320 -- Description : creates a copy of a standard article
321 -- Business Rules :
322 -- Parameters :
323 -- Version : 1.0
324 -- End of comments
325 PROCEDURE copy_article(
326 p_api_version IN NUMBER,
327 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
328 x_return_status OUT NOCOPY VARCHAR2,
329 x_msg_count OUT NOCOPY NUMBER,
330 x_msg_data OUT NOCOPY VARCHAR2,
331 p_sae_id IN NUMBER,
332 p_sae_release IN VARCHAR2,
333 p_chr_id IN NUMBER,
334 p_cle_id IN NUMBER DEFAULT NULL,
335 x_cat_id OUT NOCOPY NUMBER
336 ) AS
337
338 l_api_name VARCHAR2(30) := 'copy_article';
339 l_api_version CONSTANT NUMBER := 1.0;
340
341 l_catv_rec OKC_K_ARTICLE_PUB.catv_rec_type;
342 x_catv_rec OKC_K_ARTICLE_PUB.catv_rec_type;
343
344 BEGIN
345
346 x_return_status := OKC_API.START_ACTIVITY(
347 p_api_name => l_api_name,
348 p_pkg_name => g_pkg_name,
349 p_init_msg_list => p_init_msg_list,
350 l_api_version => l_api_version,
351 p_api_version => p_api_version,
352 p_api_type => g_api_type,
353 x_return_status => x_return_status);
354
355 -- check if activity started successfully
356 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
357 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
358 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
359 raise OKC_API.G_EXCEPTION_ERROR;
360 End If;
361
362 x_return_status := get_catv_rec(p_sae_id => p_sae_id,
363 p_sav_release => p_sae_release,
364 x_catv_rec => l_catv_rec);
365
366 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
367 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
368 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
369 raise OKC_API.G_EXCEPTION_ERROR;
370 End If;
371
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
383 OKC_K_ARTICLE_PUB.create_k_article(
384 p_api_version => l_api_version,
385 p_init_msg_list => p_init_msg_list,
386 x_return_status => x_return_status,
387 x_msg_count => x_msg_count,
388 x_msg_data => x_msg_data,
389 p_catv_rec => l_catv_rec,
390 x_catv_rec => x_catv_rec);
391
392 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
393 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
394 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
395 raise OKC_API.G_EXCEPTION_ERROR;
396 End If;
397
398 x_cat_id := x_catv_rec.id;
399
400 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count,
401 x_msg_data => x_msg_data);
402 EXCEPTION
403 when OKC_API.G_EXCEPTION_ERROR then
404 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
405 p_api_name => l_api_name,
406 p_pkg_name => g_pkg_name,
407 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
408 x_msg_count => x_msg_count,
409 x_msg_data => x_msg_data,
410 p_api_type => g_api_type);
411
412 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
413 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
414 p_api_name => l_api_name,
415 p_pkg_name => g_pkg_name,
416 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
417 x_msg_count => x_msg_count,
418 x_msg_data => x_msg_data,
419 p_api_type => g_api_type);
420
421 when OTHERS then
422 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
423 p_api_name => l_api_name,
424 p_pkg_name => g_pkg_name,
425 p_exc_name => 'OTHERS',
426 x_msg_count => x_msg_count,
427 x_msg_data => x_msg_data,
428 p_api_type => g_api_type);
429 END;
430
431
432 -- Start of comments
433 --
434 -- Procedure Name : delete_article
435 -- Description : deletes an article from a contract
436 -- Business Rules :
437 -- Parameters :
438 -- Version : 1.0
439 -- End of comments
440 PROCEDURE delete_article(
441 p_api_version IN NUMBER,
442 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
443 x_return_status OUT NOCOPY VARCHAR2,
444 x_msg_count OUT NOCOPY NUMBER,
445 x_msg_data OUT NOCOPY VARCHAR2,
446 p_cat_id IN NUMBER
447 ) AS
448
449 l_api_name VARCHAR2(30) := 'delete_article';
450 l_api_version CONSTANT NUMBER := 1.0;
451
452 l_catv_rec OKC_K_ARTICLE_PUB.catv_rec_type;
453
454 BEGIN
455
456 x_return_status := OKC_API.START_ACTIVITY(
457 p_api_name => l_api_name,
458 p_pkg_name => g_pkg_name,
459 p_init_msg_list => p_init_msg_list,
460 l_api_version => l_api_version,
461 p_api_version => p_api_version,
462 p_api_type => g_api_type,
463 x_return_status => x_return_status);
464
465 -- check if activity started successfully
466 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
467 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
468 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
469 raise OKC_API.G_EXCEPTION_ERROR;
470 End If;
471
472 x_return_status := get_catv_rec(p_cat_id => p_cat_id,
473 x_catv_rec => l_catv_rec);
474
475 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
476 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
477 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
478 raise OKC_API.G_EXCEPTION_ERROR;
479 End If;
480
481 OKC_K_ARTICLE_PUB.delete_k_article(
482 p_api_version => l_api_version,
483 p_init_msg_list => p_init_msg_list,
484 x_return_status => x_return_status,
485 x_msg_count => x_msg_count,
486 x_msg_data => x_msg_data,
487 p_catv_rec => l_catv_rec);
488
489 If (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
490 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
491 Elsif (x_return_status = OKC_API.G_RET_STS_ERROR) then
492 raise OKC_API.G_EXCEPTION_ERROR;
493 End If;
494
495 OKC_API.END_ACTIVITY(x_msg_count => x_msg_count,
496 x_msg_data => x_msg_data);
497 EXCEPTION
498 when OKC_API.G_EXCEPTION_ERROR then
499 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
500 p_api_name => l_api_name,
501 p_pkg_name => g_pkg_name,
502 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
503 x_msg_count => x_msg_count,
504 x_msg_data => x_msg_data,
505 p_api_type => g_api_type);
506
507 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
508 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
509 p_api_name => l_api_name,
510 p_pkg_name => g_pkg_name,
511 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
512 x_msg_count => x_msg_count,
513 x_msg_data => x_msg_data,
514 p_api_type => g_api_type);
515
516 when OTHERS then
517 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
518 p_api_name => l_api_name,
519 p_pkg_name => g_pkg_name,
520 p_exc_name => 'OTHERS',
521 x_msg_count => x_msg_count,
522 x_msg_data => x_msg_data,
523 p_api_type => g_api_type);
524 END;
525
526
527 END OKL_ARTICLE_PUB;