[Home] [Help]
PACKAGE BODY: APPS.OKC_ART_BLK_PVT
Source
1 PACKAGE BODY OKC_ART_BLK_PVT AS
2 /* $Header: OKCVARTBLKB.pls 120.0 2005/05/25 19:19:50 appldev noship $ */
3
4 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
5 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_ART_BLK_PVT';
6
7 G_MSG_ART_INACTIVE_VARIABLE CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_INV_VAR';
8 G_MSG_ART_INVALID_VALUESET CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_INV_VAL';
9 G_MSG_ART_INVALID_SECTION CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_DEF_SEC';
10 G_MSG_ART_INVALID_TYPE CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_INV_TYP';
11 G_MSG_INVALID_STS_CHANGE CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_INV_STS';
12
13 G_CHK_ART_INACTIVE_VARIABLE CONSTANT VARCHAR2(30) := 'CHECK_ART_INV_VAR';
14 G_CHK_ART_INVALID_VALUESET CONSTANT VARCHAR2(30) := 'CHECK_ART_INV_VAL';
15 G_CHK_ART_INVALID_SECTION CONSTANT VARCHAR2(30) := 'CHECK_ART_DEF_SEC';
16 G_CHK_ART_INVALID_TYPE CONSTANT VARCHAR2(30) := 'CHECK_ART_INV_TYP';
17 G_CHK_INVALID_STS_CHANGE CONSTANT VARCHAR2(30) := 'CHECK_ART_INV_STS';
18
19 G_MSG_ART_INV_CALLING_ORG CONSTANT VARCHAR2(30) := 'OKC_CHECK_ART_INV_CAL_ORG';
20 G_CHK_INVALID_ADOPTION CONSTANT VARCHAR2(30) := 'CHECK_ART_INV_ADP';
21 G_CHK_ADOPTION_UNEXP_ERROR CONSTANT VARCHAR2(30) := 'CHECK_ART_ADP_UNP_ERR';
22 G_OKC_MSG_INVALID_ARGUMENT CONSTANT VARCHAR2(200) := 'OKC_INVALID_ARGUMENT';
23 -- ARG_NAME ARG_VALUE is invalid.
24
25 G_GLOBAL_ORG_ID NUMBER;
26 G_USER_ID NUMBER;
27 G_LOGIN_ID NUMBER;
28
29 l_debug VARCHAR2(1);
30
31 ---------- Internal Procedures BEGIN ---------------------------
32
33 PROCEDURE get_version_details(
34 p_org_id IN NUMBER ,
35 p_art_ver_tbl IN num_tbl_type ,
36 x_return_status OUT NOCOPY VARCHAR2,
37 x_id OUT NOCOPY NUMBER ,
38 x_adopt_asis_count OUT NOCOPY NUMBER ,
39 x_global_count OUT NOCOPY NUMBER ,
40 x_localized_count OUT NOCOPY NUMBER);
41
42 PROCEDURE status_check_blk(
43 p_id IN NUMBER ,
44 p_to_status IN VARCHAR2 ,
45 x_return_status OUT NOCOPY VARCHAR2 ,
46 x_qa_return_status OUT NOCOPY VARCHAR2 ,
47 px_validation_results IN OUT NOCOPY validation_tbl_type );
48
49 PROCEDURE variable_check_blk(
50 p_id IN NUMBER,
51 x_return_status OUT NOCOPY VARCHAR2 ,
52 x_qa_return_status OUT NOCOPY VARCHAR2 ,
53 px_validation_results IN OUT NOCOPY validation_tbl_type );
54
55 PROCEDURE section_type_check_blk(
56 p_id IN NUMBER,
57 x_return_status OUT NOCOPY VARCHAR2 ,
58 x_qa_return_status OUT NOCOPY VARCHAR2 ,
59 px_validation_results IN OUT NOCOPY validation_tbl_type );
60
61 PROCEDURE update_art_version_status_blk(
62 p_id IN NUMBER ,
63 p_status IN VARCHAR2 ,
64 x_return_status OUT NOCOPY VARCHAR2 );
65
66 PROCEDURE update_adp_status_type_blk(
67 p_id IN NUMBER ,
68 p_local_org_id IN NUMBER,
69 p_adoption_status IN VARCHAR2 ,
70 p_adoption_type IN VARCHAR2 ,
71 p_type IN VARCHAR2,
72 x_return_status OUT NOCOPY VARCHAR2 );
73
74 PROCEDURE update_prev_vers_enddate_blk(
75 p_id IN NUMBER ,
76 x_return_status OUT NOCOPY VARCHAR2 );
77
78 PROCEDURE adopt_relationships_blk(
79 p_id IN NUMBER,
80 x_return_status OUT NOCOPY VARCHAR2 );
81
82 PROCEDURE delete_relationships_blk(
83 p_id IN NUMBER,
84 p_org_id IN NUMBER,
85 x_return_status OUT NOCOPY VARCHAR2 );
86
87 ---------- Internal Procedures END -----------------------------
88
89
90 --------------------------------------------------------------------------------------------
91 FUNCTION get_uniq_id RETURN NUMBER
92 IS
93
94 l_api_name CONSTANT VARCHAR2(30) := 'get_version_details';
95 l_num NUMBER;
96
97 CURSOR c1 IS
98 SELECT OKC_ART_BLK_TEMP_S1.NEXTVAL FROM DUAL;
99 BEGIN
100
101 OPEN c1;
102 FETCH c1 INTO l_num;
103 CLOSE c1;
104
105 RETURN l_num;
106
107 EXCEPTION
108 WHEN OTHERS THEN
109
110 IF (l_debug = 'Y') THEN
111 okc_debug.log('201: Leaving get_uniq_id: Unknown error');
112 END IF;
113
114 IF (c1%ISOPEN) THEN
115 CLOSE c1;
116 END IF;
117
118 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR)
119 THEN
120 fnd_msg_pub.add_exc_msg(G_PKG_NAME ,l_api_name );
121 END IF;
122 RAISE;
123 END;
124
125 -----------------------------------------------------------------------------
126
127 PROCEDURE get_version_details(
128 p_org_id IN NUMBER ,
129 p_art_ver_tbl IN num_tbl_type ,
130 x_return_status OUT NOCOPY VARCHAR2,
131 x_id OUT NOCOPY NUMBER ,
132 x_adopt_asis_count OUT NOCOPY NUMBER ,
133 x_global_count OUT NOCOPY NUMBER ,
134 x_localized_count OUT NOCOPY NUMBER)
135 IS
136
137 l_api_name CONSTANT VARCHAR2(30) := 'get_version_details';
138 l_total_count NUMBER := 0;
139 l_invalid BOOLEAN := FALSE;
140
141 CURSOR l_count_csr (cp_id IN NUMBER) IS
142 SELECT sum(to_number(decode(adopt_asis_yn, 'Y', '1', '0'))) adopt_is_count,
143 sum(to_number(decode(global_yn, 'Y', '1', '0'))) global_count,
144 sum(to_number(decode(localized_yn, 'Y', '1', '0'))) localized_count,
145 count(*) total_count
146 FROM OKC_ART_BLK_TEMP
147 WHERE id = cp_id;
148
149 BEGIN
150
151 IF (l_debug = 'Y') THEN
152 okc_debug.log('100: Entering get_version_details: p_org_id='||p_org_id);
153 END IF;
154
155 x_return_status := FND_API.G_RET_STS_SUCCESS;
156 IF (p_art_ver_tbl IS NULL ) THEN
157 l_invalid := TRUE;
158 ELSIF (p_art_ver_tbl.COUNT < 1) THEN
159 l_invalid := TRUE;
160 END IF;
161 IF (l_invalid) THEN
162 x_return_status := FND_API.G_RET_STS_ERROR;
163 FND_MESSAGE.set_name(G_APP_NAME, G_OKC_MSG_INVALID_ARGUMENT);
164 FND_MESSAGE.set_token('ARG_NAME', 'p_art_ver_tbl');
165 FND_MESSAGE.set_token('ARG_VALUE', 'NULL');
166 FND_MSG_PUB.add;
167 RAISE fnd_api.g_exc_error;
168 END IF;
169
170 IF (p_org_id IS NULL) THEN
171 FND_MESSAGE.set_name(G_APP_NAME, G_OKC_MSG_INVALID_ARGUMENT);
172 FND_MESSAGE.set_token('ARG_NAME', 'p_org_id');
173 FND_MESSAGE.set_token('ARG_VALUE', 'p_org_id');
174 FND_MSG_PUB.add;
175 RAISE fnd_api.g_exc_error;
176 END IF;
177
178
179 x_id := get_uniq_id;
180
181 IF ( p_org_id = G_GLOBAL_ORG_ID) THEN
182 -- if we are in global org, store the list of articles with global_yn = Y
183
184 FORALL i in p_art_ver_tbl.FIRST..p_art_ver_tbl.LAST
185 INSERT INTO OKC_ART_BLK_TEMP
186 (
187 ID,
188 ARTICLE_ID,
189 ARTICLE_VERSION_ID,
190 ORG_ID,
191 ARTICLE_TITLE,
192 DISPLAY_NAME,
193 ARTICLE_TYPE,
194 DEFAULT_SECTION,
195 STATUS,
196 START_DATE,
197 ADOPT_ASIS_YN,
198 GLOBAL_YN,
199 LOCALIZED_YN
200 )
201 SELECT x_id, ART.article_id, VER.article_version_id, ART.org_id,
202 ART.ARTICLE_TITLE, VER.DISPLAY_NAME, ART.ARTICLE_TYPE,
203 nvl(VER.DEFAULT_SECTION, 'UNASSIGNED'), nvl(VER.ARTICLE_STATUS,'DRAFT'),
204 VER.start_date, 'N', nvl(VER.GLOBAL_YN, 'N'), 'N'
205 FROM OKC_ARTICLES_ALL ART, OKC_ARTICLE_VERSIONS VER
206 WHERE VER.article_version_id = p_art_ver_tbl(i)
207 AND ART.ARTICLE_ID = VER.ARTICLE_ID
208 AND ART.ORG_ID = G_GLOBAL_ORG_ID;
209
210 ELSE
211 -- we are not in the global org,
212 -- get the local/localized articles
213 FORALL i in p_art_ver_tbl.FIRST..p_art_ver_tbl.LAST
214 INSERT INTO OKC_ART_BLK_TEMP
215 (
216 ID,
217 ARTICLE_ID,
218 ARTICLE_VERSION_ID,
219 ORG_ID,
220 ARTICLE_TITLE,
221 DISPLAY_NAME,
222 ARTICLE_TYPE,
223 DEFAULT_SECTION,
224 STATUS,
225 START_DATE,
226 ADOPT_ASIS_YN,
227 GLOBAL_YN,
228 LOCALIZED_YN
229 )
230 SELECT x_id, ART.article_id, VER.article_version_id, ART.org_id,
231 ART.ARTICLE_TITLE, VER.DISPLAY_NAME, ART.ARTICLE_TYPE,
232 nvl(VER.DEFAULT_SECTION, 'UNASSIGNED'), nvl(VER.ARTICLE_STATUS,'DRAFT'),
233 VER.start_date,'N', 'N', decode(VER.ADOPTION_TYPE, 'LOCALIZED', 'Y', 'N')
234 FROM OKC_ARTICLES_ALL ART, OKC_ARTICLE_VERSIONS VER
235 WHERE VER.article_version_id = p_art_ver_tbl(i)
236 AND ART.ARTICLE_ID = VER.ARTICLE_ID
237 AND ART.ORG_ID = p_org_id;
238
239 -- get the adopt as is articles
240 FORALL i in p_art_ver_tbl.FIRST..p_art_ver_tbl.LAST
241 INSERT INTO OKC_ART_BLK_TEMP
242 (
243 ID,
244 ARTICLE_ID,
245 ARTICLE_VERSION_ID,
246 ORG_ID,
247 ARTICLE_TITLE,
248 DISPLAY_NAME,
249 ARTICLE_TYPE,
250 DEFAULT_SECTION,
251 STATUS,
252 START_DATE,
253 ADOPT_ASIS_YN,
254 GLOBAL_YN,
255 LOCALIZED_YN
256 )
257 SELECT x_id, ART.article_id, VER.article_version_id, ART.org_id,
258 ART.ARTICLE_TITLE, VER.DISPLAY_NAME, ART.ARTICLE_TYPE,
259 nvl(VER.DEFAULT_SECTION, 'UNASSIGNED'), nvl(ADP.ADOPTION_STATUS,'DRAFT'),
260 VER.start_date,'Y', 'N', 'N'
261 FROM OKC_ARTICLES_ALL ART, OKC_ARTICLE_VERSIONS VER, OKC_ARTICLE_ADOPTIONS ADP
262 WHERE VER.article_version_id = p_art_ver_tbl(i)
263 AND ART.ARTICLE_ID = VER.ARTICLE_ID
264 AND ART.ORG_ID = G_GLOBAL_ORG_ID
265 AND ADP.GLOBAL_ARTICLE_VERSION_ID = VER.ARTICLE_VERSION_ID
266 AND ADP.LOCAL_ORG_ID = p_org_id;
267
268 END IF; -- of IF/ELSE ( p_org_id = G_GLOBAL_ORG_ID)
269
270 OPEN l_count_csr(x_id);
271 FETCH l_count_csr INTO x_adopt_asis_count, x_global_count, x_localized_count, l_total_count;
272 CLOSE l_count_csr;
273
274 IF (l_total_count <> p_art_ver_tbl.COUNT) THEN
275
276 IF (l_debug = 'Y') THEN
277 okc_debug.log('198: l_total_count='||l_total_count||' p_art_ver_tbl.COUNT='||p_art_ver_tbl.COUNT);
278 okc_debug.log('199: This indicates that some article versions belonged to an org other than input org='||p_org_id);
279 END IF;
280 x_return_status := FND_API.G_RET_STS_ERROR;
281 FND_MESSAGE.set_name(G_APP_NAME, G_OKC_MSG_INVALID_ARGUMENT);
282 FND_MESSAGE.set_token('ARG_NAME', 'p_org_id');
283 FND_MESSAGE.set_token('ARG_VALUE', p_org_id);
284 FND_MSG_PUB.add;
285 RAISE fnd_api.g_exc_error;
286 END IF;
287
288 IF (l_debug = 'Y') THEN
289 okc_debug.log('200: Leaving get_version_details: Success, x_id='||x_id||' x_adopt_asis_count='|| x_adopt_asis_count||' x_global_count='|| x_global_count|| ' x_localized_count='||x_localized_count);
290 END IF;
291
292 EXCEPTION
293
294 WHEN FND_API.G_EXC_ERROR THEN
295 x_return_status := FND_API.G_RET_STS_ERROR;
296 IF (l_count_csr%ISOPEN) THEN
297 CLOSE l_count_csr;
298 END IF;
299
300 IF (l_debug = 'Y') THEN
301 okc_debug.log('201: Leaving get_version_details: Error');
302 END IF;
303
304 WHEN OTHERS THEN
305 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
306 IF (l_count_csr%ISOPEN) THEN
307 CLOSE l_count_csr;
308 END IF;
309
310 IF (l_debug = 'Y') THEN
311 okc_debug.log('202: Leaving get_version_details: Unknown error');
312 END IF;
313
314 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR)
315 THEN
316 fnd_msg_pub.add_exc_msg(G_PKG_NAME ,l_api_name );
317 END IF;
318
319 END get_version_details;
320
321 -----------------------------------------------------------------------------
322
323 PROCEDURE status_check_blk(
324 p_id IN NUMBER ,
325 p_to_status IN VARCHAR2 ,
326 x_return_status OUT NOCOPY VARCHAR2 ,
327 x_qa_return_status OUT NOCOPY VARCHAR2 ,
328 px_validation_results IN OUT NOCOPY validation_tbl_type )
329 IS
330
331 l_api_name CONSTANT VARCHAR2(30) := 'status_check_blk';
332
333 CURSOR l_pend_app_csr IS
334 SELECT article_id, article_version_id, nvl(display_name, article_title),
335 nvl(status, 'DRAFT')
336 FROM OKC_ART_BLK_TEMP
337 WHERE id = p_id AND
338 status NOT IN ('DRAFT', 'REJECTED');
339
340 CURSOR l_app_rej_csr IS
341 SELECT article_id, article_version_id, nvl(display_name, article_title),
342 nvl(status, 'DRAFT')
343 FROM OKC_ART_BLK_TEMP
344 WHERE id = p_id AND
345 status <> 'PENDING_APPROVAL';
346
347 CURSOR l_status_csr is
348 SELECT NVL(lookup_code, 'X'), NVL(meaning, 'UNDEFINED')
349 FROM fnd_lookup_values_vl
350 WHERE lookup_type = 'OKC_ARTICLE_STATUS';
351
352 errnum INTEGER := 0;
353 initerrnum INTEGER := 0;
354
355 l_found BOOLEAN := FALSE;
356
357 l_art_id_tbl num_tbl_type;
358 l_art_ver_id_tbl num_tbl_type;
359 l_art_title_tbl varchar_tbl_type;
360 l_ver_from_status_tbl varchar_tbl_type;
361
362 l_status_code_tbl varchar_tbl_type;
363 l_status_meaning_tbl varchar_tbl_type;
364
365 l_to_status_meaning FND_LOOKUP_VALUES_VL.MEANING%TYPE;
366 l_from_status_meaning FND_LOOKUP_VALUES_VL.MEANING%TYPE;
367
368 FUNCTION get_status_meaning(p_status IN VARCHAR2) RETURN VARCHAR2
369 IS
370 BEGIN
371
372 IF ((l_status_code_tbl IS NOT NULL) AND (l_status_meaning_tbl IS NOT NULL)) THEN
373
374 FOR i IN l_status_code_tbl.FIRST..l_status_code_tbl.LAST LOOP
375
376 IF (trim(l_status_code_tbl(i)) = trim(nvl(p_status,'Y'))) THEN
377 RETURN l_status_meaning_tbl(i);
378 END IF;
379
380 END LOOP;
381 END IF;
382
383 -- if we reach here, then not able to find the status meaning, return status code as is.
384 RETURN p_status;
385 END get_status_meaning;
386
387
388 BEGIN
389
390 IF (l_debug = 'Y') THEN
391 okc_debug.log('100: Entering status_check_blk: p_id='||p_id||' p_to_status='||p_to_status);
392 END IF;
393 x_return_status := FND_API.G_RET_STS_SUCCESS;
394 x_qa_return_status := FND_API.G_RET_STS_SUCCESS;
395
396 errnum := px_validation_results.COUNT;
397 initerrnum := errnum;
398
399 IF (nvl(p_to_status,'X') = 'PENDING_APPROVAL') THEN
400
401 OPEN l_pend_app_csr;
402 FETCH l_pend_app_csr BULK COLLECT INTO l_art_id_tbl, l_art_ver_id_tbl,
403 l_art_title_tbl, l_ver_from_status_tbl;
404
405 IF (l_art_id_tbl.COUNT > 0) THEN
406 l_found := TRUE;
407 END IF;
408 CLOSE l_pend_app_csr;
409
410 ELSIF (nvl(p_to_status,'X') IN ('APPROVED', 'REJECTED')) THEN
411
412 OPEN l_app_rej_csr;
413 FETCH l_app_rej_csr BULK COLLECT INTO l_art_id_tbl, l_art_ver_id_tbl,
414 l_art_title_tbl, l_ver_from_status_tbl;
415
416 IF (l_art_id_tbl.COUNT > 0) THEN
417 l_found := TRUE;
418 END IF;
419 CLOSE l_app_rej_csr;
420
421 ELSE
422 -- status change not recognized
423 x_return_status := FND_API.G_RET_STS_ERROR;
424 FND_MESSAGE.set_name(G_APP_NAME, G_OKC_MSG_INVALID_ARGUMENT);
425 FND_MESSAGE.set_token('ARG_NAME', 'p_to_status');
426 FND_MESSAGE.set_token('ARG_VALUE', p_to_status);
427 FND_MSG_PUB.add;
428 RAISE FND_API.G_EXC_ERROR;
429
430 END IF;
431
432 IF (l_found) THEN
433
434 OPEN l_status_csr;
435 FETCH l_status_csr BULK COLLECT INTO l_status_code_tbl, l_status_meaning_tbl;
436 CLOSE l_status_csr;
437
438 l_to_status_meaning := get_status_meaning(p_to_status);
439
440 FOR i IN l_art_id_tbl.FIRST..l_art_id_tbl.LAST LOOP
441
442 errnum := errnum + 1;
443 px_validation_results(errnum).article_id := l_art_id_tbl(i);
444 px_validation_results(errnum).article_version_id := l_art_ver_id_tbl(i);
445 px_validation_results(errnum).article_title := l_art_title_tbl(i);
446 px_validation_results(errnum).error_code := G_CHK_INVALID_STS_CHANGE;
447
448 FND_MESSAGE.set_name(G_APP_NAME, G_MSG_INVALID_STS_CHANGE);
449 FND_MESSAGE.set_token('ARTICLE_TITLE', l_art_title_tbl(i));
450 FND_MESSAGE.set_token('FROM_STATUS', get_status_meaning(l_ver_from_status_tbl(i)));
451 FND_MESSAGE.set_token('TO_STATUS', l_to_status_meaning);
452
453 px_validation_results(errnum).error_message := FND_MESSAGE.get;
454
455 END LOOP;
456
457 l_status_code_tbl.DELETE;
458 l_status_meaning_tbl.DELETE;
459
460 END IF;
461
462 l_art_id_tbl.DELETE;
463 l_art_ver_id_tbl.DELETE;
464 l_art_title_tbl.DELETE;
465 l_ver_from_status_tbl.DELETE;
466
467
468
469 IF (errnum > initerrnum) THEN
470 x_qa_return_status := FND_API.G_RET_STS_ERROR;
471 END IF;
472
473 IF (l_debug = 'Y') THEN
474 okc_debug.log('200: Leaving status_check_blk: Success, x_qa_return_status='||x_qa_return_status);
475 END IF;
476
477 EXCEPTION
478
479 WHEN FND_API.G_EXC_ERROR THEN
480 x_return_status := FND_API.G_RET_STS_ERROR ;
481 IF (l_debug = 'Y') THEN
482 okc_debug.log('201: Leaving status_check_blk: Error');
483 END IF;
484 IF (l_pend_app_csr%ISOPEN) THEN
485 CLOSE l_pend_app_csr;
486 END IF;
487 IF (l_app_rej_csr%ISOPEN) THEN
488 CLOSE l_app_rej_csr;
489 END IF;
490 IF (l_status_csr%ISOPEN) THEN
491 CLOSE l_status_csr;
492 END IF;
493
494 WHEN OTHERS THEN
495
496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
497 IF (l_debug = 'Y') THEN
498 okc_debug.log('202: Leaving status_check_blk: Unknown Error');
499 END IF;
500 IF (l_pend_app_csr%ISOPEN) THEN
501 CLOSE l_pend_app_csr;
502 END IF;
503 IF (l_app_rej_csr%ISOPEN) THEN
504 CLOSE l_app_rej_csr;
505 END IF;
506 IF (l_status_csr%ISOPEN) THEN
507 CLOSE l_status_csr;
508 END IF;
509
510 IF FND_MSG_PUB.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
511 FND_MSG_PUB.add_exc_msg(G_PKG_NAME ,l_api_name );
512 END IF;
513
514 END status_check_blk;
515
516 -----------------------------------------------------------------------------
517
518 PROCEDURE variable_check_blk(
519 p_id IN NUMBER,
520 x_return_status OUT NOCOPY VARCHAR2 ,
521 x_qa_return_status OUT NOCOPY VARCHAR2 ,
522 px_validation_results IN OUT NOCOPY validation_tbl_type )
523 IS
524 l_api_name CONSTANT VARCHAR2(30) := 'variable_check_blk';
525
526 -- check for disabled variables
527 CURSOR l_disabled_var_csr (cp_id IN NUMBER) IS
528 SELECT TMP.article_id, TMP.article_version_id, nvl(TMP.display_name, TMP.article_title),
529 BVT.variable_name
530 FROM OKC_BUS_VARIABLES_TL BVT,
531 OKC_BUS_VARIABLES_B BVB,
532 OKC_ARTICLE_VARIABLES AAV,
533 OKC_ART_BLK_TEMP TMP
534 WHERE TMP.id = cp_id
535 AND TMP.adopt_asis_yn = 'N' --check not done for adopt as is clauses
536 AND AAV.article_version_id = TMP.article_version_id
537 AND BVB.variable_code = AAV.variable_code
538 AND nvl(BVB.disabled_yn,'N') = 'Y'
539 AND BVT.language = userenv('LANG')
540 AND BVT.variable_code = BVB.variable_code;
541
542 -- check for user-defined variables with invalid value sets
543 CURSOR l_invalid_valueset_csr (cp_id IN NUMBER) IS
544 SELECT TMP.article_id, TMP.article_version_id, nvl(TMP.display_name, TMP.article_title),
545 BVT.variable_name,
546 nvl(FVS.flex_value_set_id, -99)
547 FROM OKC_BUS_VARIABLES_TL BVT,
548 FND_FLEX_VALUE_SETS FVS,
549 OKC_BUS_VARIABLES_B BVB,
550 OKC_ARTICLE_VARIABLES AAV,
551 OKC_ART_BLK_TEMP TMP
552 WHERE TMP.id = cp_id
553 AND TMP.adopt_asis_yn = 'N' --check not done for adopt as is clauses
554 AND AAV.article_version_id = TMP.article_version_id
555 AND BVB.variable_code = AAV.variable_code
556 AND BVB.variable_type = 'U'
557 AND BVB.value_set_id = FVS.flex_value_set_id (+)
558 AND BVT.language = userenv('LANG')
559 AND BVT.variable_code = BVB.variable_code;
560
561 l_art_id_tbl num_tbl_type;
562 l_art_ver_id_tbl num_tbl_type;
563 l_art_title_tbl varchar_tbl_type;
564 l_var_name_tbl varchar_tbl_type;
565 l_val_set_tbl num_tbl_type;
566
567 l_found BOOLEAN := FALSE;
568 errnum INTEGER := 0;
569 initerrnum INTEGER := 0;
570
571 BEGIN
572
573 IF (l_debug = 'Y') THEN
574 okc_debug.log('100: Entering variable_check_blk: p_id='||p_id);
575 END IF;
576 x_return_status := FND_API.G_RET_STS_SUCCESS;
577 x_qa_return_status := FND_API.G_RET_STS_SUCCESS;
578
579 errnum := px_validation_results.COUNT;
580 initerrnum := errnum;
581
582 OPEN l_disabled_var_csr(p_id);
583 FETCH l_disabled_var_csr BULK COLLECT INTO l_art_id_tbl, l_art_ver_id_tbl, l_art_title_tbl,
584 l_var_name_tbl;
585 IF ( l_art_id_tbl.COUNT > 0 ) THEN
586 l_found := TRUE;
587 END IF;
588 CLOSE l_disabled_var_csr;
589
590 IF (l_found) THEN
591 FOR i IN l_art_id_tbl.FIRST..l_art_id_tbl.LAST LOOP
592 errnum := errnum + 1;
593 px_validation_results(errnum).article_id := l_art_id_tbl(i);
594 px_validation_results(errnum).article_version_id := l_art_ver_id_tbl(i);
595 px_validation_results(errnum).article_title := l_art_title_tbl(i);
596 px_validation_results(errnum).error_code := G_CHK_ART_INACTIVE_VARIABLE;
597 FND_MESSAGE.set_name(G_APP_NAME, G_MSG_ART_INACTIVE_VARIABLE);
598 FND_MESSAGE.set_token('ARTICLE_TITLE', l_art_title_tbl(i));
599 FND_MESSAGE.set_token('VARIABLE_NAME', l_var_name_tbl(i));
600 px_validation_results(errnum).error_message := fnd_message.get;
601 END LOOP;
602 END IF;
603 l_found := FALSE;
604
605 OPEN l_invalid_valueset_csr(p_id);
606 FETCH l_invalid_valueset_csr BULK COLLECT INTO l_art_id_tbl, l_art_ver_id_tbl, l_art_title_tbl, l_var_name_tbl, l_val_set_tbl;
607 IF ( l_art_id_tbl.COUNT > 0 ) THEN
608 l_found := TRUE;
609 END IF;
610 CLOSE l_invalid_valueset_csr;
611
612 IF (l_found) THEN
613 FOR i IN l_art_id_tbl.FIRST..l_art_id_tbl.LAST LOOP
614
615 IF (l_val_set_tbl(i) = -99) THEN
616 errnum := errnum + 1;
617 px_validation_results(errnum).article_id := l_art_id_tbl(i);
618 px_validation_results(errnum).article_version_id := l_art_ver_id_tbl(i);
619 px_validation_results(errnum).article_title := l_art_title_tbl(i);
620 px_validation_results(errnum).error_code := G_CHK_ART_INVALID_VALUESET;
621 FND_MESSAGE.set_name(G_APP_NAME, G_MSG_ART_INVALID_VALUESET);
622 FND_MESSAGE.set_token('ARTICLE_TITLE', l_art_title_tbl(i));
623 FND_MESSAGE.set_token('VARIABLE_NAME', l_var_name_tbl(i));
624 px_validation_results(errnum).error_message := fnd_message.get;
625 END IF;
626
627 END LOOP;
628 END IF;
629
630 l_art_id_tbl.DELETE;
631 l_art_ver_id_tbl.DELETE;
632 l_art_title_tbl.DELETE;
633 l_var_name_tbl.DELETE;
634 l_val_set_tbl.DELETE;
635
636 IF (errnum > initerrnum) THEN
637 x_qa_return_status := FND_API.G_RET_STS_ERROR;
638 END IF;
639
640 IF (l_debug = 'Y') THEN
641 okc_debug.log('200: Leaving variable_check_blk: Success, x_qa_return_status='||x_qa_return_status);
642 END IF;
643
644 EXCEPTION
645
646 WHEN OTHERS THEN
647
648 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
649 IF (l_debug = 'Y') THEN
650 okc_debug.log('202: Leaving variable_check_blk: Unknown Error');
651 END IF;
652 IF (l_disabled_var_csr%ISOPEN) THEN
653 CLOSE l_disabled_var_csr;
654 END IF;
655 IF (l_invalid_valueset_csr%ISOPEN) THEN
656 CLOSE l_invalid_valueset_csr;
657 END IF;
658
659 IF FND_MSG_PUB.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
660 FND_MSG_PUB.add_exc_msg(G_PKG_NAME ,l_api_name );
661 END IF;
662
663 END variable_check_blk;
664
665 -----------------------------------------------------------------------------
666
667 PROCEDURE section_type_check_blk(
668 p_id IN NUMBER,
669 x_return_status OUT NOCOPY VARCHAR2 ,
670 x_qa_return_status OUT NOCOPY VARCHAR2 ,
671 px_validation_results IN OUT NOCOPY validation_tbl_type )
672 IS
673 l_api_name CONSTANT VARCHAR2(30) := 'section_type_check_blk';
674
675 -- check for article_type
676 CURSOR l_art_typ_csr (cp_id IN NUMBER, cp_date IN DATE) IS
677 SELECT TMP.article_id, TMP.article_version_id, nvl(TMP.display_name, TMP.article_title),
678 TMP.article_type
679 FROM OKC_ART_BLK_TEMP TMP
680 WHERE TMP.id = cp_id
681 AND TMP.adopt_asis_yn = 'N' --check not done for adopt as is clauses
682 AND NOT EXISTS (
683 SELECT '1' from FND_LOOKUPS F
684 WHERE F.lookup_type = 'OKC_SUBJECT'
685 AND F.lookup_code = TMP.article_type
686 AND trunc(cp_date) BETWEEN trunc(nvl(F.start_date_active, cp_date)) AND
687 nvl(F.end_date_active, cp_date));
688
689 -- check for default_section
690 CURSOR l_def_sec_csr (cp_id IN NUMBER, cp_date IN DATE) IS
691 SELECT TMP.article_id, TMP.article_version_id, nvl(TMP.display_name, TMP.article_title),
692 TMP.default_section
693 FROM OKC_ART_BLK_TEMP TMP
694 WHERE TMP.id = cp_id
695 AND TMP.adopt_asis_yn = 'N' --check not done for adopt as is clauses
696 AND TMP.default_section <> 'UNASSIGNED'
697 AND NOT EXISTS (
698 SELECT '1' from FND_LOOKUPS F
699 WHERE F.lookup_type = 'OKC_ARTICLE_SECTION'
700 AND F.lookup_code = TMP.default_section
701 AND trunc(cp_date) BETWEEN trunc(nvl(F.start_date_active, cp_date)) AND
702 nvl(F.end_date_active, cp_date));
703
704
705 l_art_id_tbl num_tbl_type;
706 l_art_ver_id_tbl num_tbl_type;
707 l_art_title_tbl varchar_tbl_type;
708 l_art_typ_tbl varchar_tbl_type;
709 l_def_sec_tbl varchar_tbl_type;
710
711 l_found BOOLEAN := FALSE;
712 errnum INTEGER := 0;
713 initerrnum INTEGER := 0;
714 l_date DATE := sysdate;
715
716 BEGIN
717
718 IF (l_debug = 'Y') THEN
719 okc_debug.log('100: Entering section_type_check_blk: p_id='||p_id);
720 END IF;
721 x_return_status := FND_API.G_RET_STS_SUCCESS;
722 x_qa_return_status := FND_API.G_RET_STS_SUCCESS;
723
724 errnum := px_validation_results.COUNT;
725 initerrnum := errnum;
726
727 OPEN l_art_typ_csr(p_id, l_date);
728 FETCH l_art_typ_csr BULK COLLECT INTO l_art_id_tbl, l_art_ver_id_tbl, l_art_title_tbl,
729 l_art_typ_tbl;
730 IF ( l_art_id_tbl.COUNT > 0 ) THEN
731 l_found := TRUE;
732 END IF;
733 CLOSE l_art_typ_csr;
734
735 IF (l_found) THEN
736 FOR i IN l_art_id_tbl.FIRST..l_art_id_tbl.LAST LOOP
737 errnum := errnum + 1;
738 px_validation_results(errnum).article_id := l_art_id_tbl(i);
739 px_validation_results(errnum).article_version_id := l_art_ver_id_tbl(i);
740 px_validation_results(errnum).article_title := l_art_title_tbl(i);
741 px_validation_results(errnum).error_code := G_CHK_ART_INVALID_TYPE;
742 FND_MESSAGE.set_name(G_APP_NAME, G_MSG_ART_INVALID_TYPE);
743 FND_MESSAGE.set_token('ARTICLE_TITLE', l_art_title_tbl(i));
744 FND_MESSAGE.set_token('ARTICLE_TYPE', l_art_typ_tbl(i));
745 px_validation_results(errnum).error_message := fnd_message.get;
746 END LOOP;
747 END IF;
748 l_found := FALSE;
749
750 OPEN l_def_sec_csr(p_id, l_date);
751 FETCH l_def_sec_csr BULK COLLECT INTO l_art_id_tbl, l_art_ver_id_tbl, l_art_title_tbl, l_def_sec_tbl;
752 IF ( l_art_id_tbl.COUNT > 0 ) THEN
753 l_found := TRUE;
754 END IF;
755 CLOSE l_def_sec_csr;
756
757 IF (l_found) THEN
758 FOR i IN l_art_id_tbl.FIRST..l_art_id_tbl.LAST LOOP
759 errnum := errnum + 1;
760 px_validation_results(errnum).article_id := l_art_id_tbl(i);
761 px_validation_results(errnum).article_version_id := l_art_ver_id_tbl(i);
762 px_validation_results(errnum).article_title := l_art_title_tbl(i);
763 px_validation_results(errnum).error_code := G_CHK_ART_INVALID_SECTION;
764 FND_MESSAGE.set_name(G_APP_NAME, G_MSG_ART_INVALID_SECTION);
765 FND_MESSAGE.set_token('ARTICLE_TITLE', l_art_title_tbl(i));
766 FND_MESSAGE.set_token('DEFAULT_SECTION', l_def_sec_tbl(i));
767 px_validation_results(errnum).error_message := fnd_message.get;
768 END LOOP;
769 END IF;
770
771 l_art_id_tbl.DELETE;
772 l_art_ver_id_tbl.DELETE;
773 l_art_title_tbl.DELETE;
774 l_art_typ_tbl.DELETE;
775 l_def_sec_tbl.DELETE;
776
777 IF (errnum > initerrnum) THEN
778 x_qa_return_status := FND_API.G_RET_STS_ERROR;
779 END IF;
780
781 IF (l_debug = 'Y') THEN
782 okc_debug.log('200: Leaving section_type_check_blk: Success, x_qa_return_status='||x_qa_return_status);
783 END IF;
784
785 EXCEPTION
786
787 WHEN OTHERS THEN
788
789 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
790 IF (l_debug = 'Y') THEN
791 okc_debug.log('201: Leaving section_type_check_blk: Unknown Error');
792 END IF;
793 IF (l_art_typ_csr%ISOPEN) THEN
794 CLOSE l_art_typ_csr;
795 END IF;
796 IF (l_def_sec_csr%ISOPEN) THEN
797 CLOSE l_def_sec_csr;
798 END IF;
799
800 IF FND_MSG_PUB.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
801 FND_MSG_PUB.add_exc_msg(G_PKG_NAME ,l_api_name );
802 END IF;
803
804 END section_type_check_blk;
805
806 -----------------------------------------------------------------------------
807
808 PROCEDURE update_art_version_status_blk(
809 p_id IN NUMBER ,
810 p_status IN VARCHAR2 ,
811 x_return_status OUT NOCOPY VARCHAR2 )
812 IS
813
814 l_api_name CONSTANT VARCHAR2(30) := 'update_art_version_status_blk';
815 l_date DATE := sysdate;
816
817 BEGIN
818
819 IF (l_debug = 'Y') THEN
820 okc_debug.log('100: Entering update_art_version_status_blk: p_id='||p_id||' p_status='||p_status);
821 END IF;
822
823 x_return_status := FND_API.G_RET_STS_SUCCESS;
824
825 IF (p_status = 'APPROVED') THEN
826
827 UPDATE OKC_ARTICLE_VERSIONS
828 SET
829 ARTICLE_STATUS = p_status,
830 -- date approved must also be updated
831 DATE_APPROVED = l_date,
832 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
833 LAST_UPDATED_BY = G_USER_ID,
834 LAST_UPDATE_LOGIN = G_LOGIN_ID,
835 LAST_UPDATE_DATE = l_date
836 WHERE
837 ARTICLE_VERSION_ID IN
838 (SELECT article_version_id FROM OKC_ART_BLK_TEMP
839 WHERE id = p_id AND adopt_asis_yn = 'N');
840 ELSE
841
842 UPDATE OKC_ARTICLE_VERSIONS
843 SET
844 ARTICLE_STATUS = p_status,
845 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
846 LAST_UPDATED_BY = G_USER_ID,
847 LAST_UPDATE_LOGIN = G_LOGIN_ID,
848 LAST_UPDATE_DATE = l_date
849 WHERE
850 ARTICLE_VERSION_ID IN
851 (SELECT article_version_id FROM OKC_ART_BLK_TEMP
852 WHERE id = p_id AND adopt_asis_yn = 'N');
853
854 END IF;
855
856 IF (l_debug = 'Y') THEN
857 okc_debug.log('200: Leaving update_art_version_status_blk: Success');
858 END IF;
859
860 EXCEPTION
861
862 WHEN OTHERS THEN
863 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
864 IF (l_debug = 'Y') THEN
865 okc_debug.log('201: Leaving update_art_version_status_blk: Unknown Error');
866 END IF;
867
868 IF FND_MSG_PUB.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
869 FND_MSG_PUB.add_exc_msg(G_PKG_NAME ,l_api_name );
870 END IF;
871
872 END update_art_version_status_blk;
873
874 -----------------------------------------------------------------------------
875
876 PROCEDURE update_adp_status_type_blk(
877 p_id IN NUMBER ,
878 p_local_org_id IN NUMBER,
879 p_adoption_status IN VARCHAR2 ,
880 p_adoption_type IN VARCHAR2 ,
881 p_type IN VARCHAR2,
882 x_return_status OUT NOCOPY VARCHAR2 )
883 IS
884
885 l_api_name CONSTANT VARCHAR2(30) := 'update_adp_status_type_blk';
886 l_date DATE := sysdate;
887
888 BEGIN
889
890 IF (l_debug = 'Y') THEN
891 okc_debug.log('100: Entering update_adp_status_type_blk: p_id='||p_id||' p_local_org_id='||p_local_org_id||' p_adoption_status='||p_adoption_status||' p_adoption_type='||p_adoption_type||' p_type='||p_type);
892 END IF;
893
894 x_return_status := FND_API.G_RET_STS_SUCCESS;
895
896 IF (p_type = 'ADOPTED') THEN
897
898 UPDATE OKC_ARTICLE_ADOPTIONS
899 SET
900 ADOPTION_TYPE = nvl(p_adoption_type, ADOPTION_TYPE),
901 ADOPTION_STATUS = nvl(p_adoption_status, ADOPTION_STATUS),
902 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
903 LAST_UPDATED_BY = G_USER_ID,
904 LAST_UPDATE_LOGIN = G_LOGIN_ID,
905 LAST_UPDATE_DATE = l_date
906 WHERE
907 GLOBAL_ARTICLE_VERSION_ID IN
908 (SELECT article_version_id FROM OKC_ART_BLK_TEMP
909 WHERE id = p_id AND adopt_asis_yn = 'Y')
910 AND LOCAL_ORG_ID = p_local_org_id;
911
912 ELSIF (p_type = 'LOCALIZED') THEN
913
914 UPDATE OKC_ARTICLE_ADOPTIONS
915 SET
916 ADOPTION_TYPE = nvl(p_adoption_type, ADOPTION_TYPE),
917 ADOPTION_STATUS = nvl(p_adoption_status, ADOPTION_STATUS),
918 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
919 LAST_UPDATED_BY = G_USER_ID,
920 LAST_UPDATE_LOGIN = G_LOGIN_ID,
921 LAST_UPDATE_DATE = l_date
922 WHERE
923 LOCAL_ARTICLE_VERSION_ID IN
924 (SELECT article_version_id FROM OKC_ART_BLK_TEMP
925 WHERE id = p_id AND adopt_asis_yn = 'N' AND localized_yn = 'Y')
926 AND LOCAL_ORG_ID = p_local_org_id;
927
928 ELSE
929 -- p_type not recognized
930 x_return_status := FND_API.G_RET_STS_ERROR;
931 FND_MESSAGE.set_name(G_APP_NAME, G_OKC_MSG_INVALID_ARGUMENT);
932 FND_MESSAGE.set_token('ARG_NAME', 'p_type');
933 FND_MESSAGE.set_token('ARG_VALUE', p_type);
934 FND_MSG_PUB.add;
935 RAISE FND_API.G_EXC_ERROR;
936
937 END IF;
938
939 IF (l_debug = 'Y') THEN
940 okc_debug.log('200: Leaving update_adp_status_type_blk: Success');
941 END IF;
942
943 EXCEPTION
944
945 WHEN FND_API.G_EXC_ERROR THEN
946 x_return_status := FND_API.G_RET_STS_ERROR;
947 IF (l_debug = 'Y') THEN
948 okc_debug.log('201: Leaving update_adp_status_type_blk: Error');
949 END IF;
950
951 WHEN OTHERS THEN
952 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
953 IF (l_debug = 'Y') THEN
954 okc_debug.log('202: Leaving update_adp_status_type_blk: Unknown Error');
955 END IF;
956
957 IF FND_MSG_PUB.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
958 FND_MSG_PUB.add_exc_msg(G_PKG_NAME ,l_api_name );
959 END IF;
960
961 END update_adp_status_type_blk;
962
963 -----------------------------------------------------------------------------
964
965 PROCEDURE update_prev_vers_enddate_blk(
966 p_id IN NUMBER ,
967 x_return_status OUT NOCOPY VARCHAR2 )
968
969 IS
970 l_api_name CONSTANT VARCHAR2(30) := 'update_prev_vers_enddate_blk';
971
972 --determine all previous versions that have a null end date
973 CURSOR l_prev_ver_csr(cp_id IN NUMBER) IS
974 SELECT PREV.article_version_id, TMP.start_date
975 FROM OKC_ART_BLK_TEMP TMP,
976 OKC_ARTICLE_VERSIONS PREV
977 WHERE TMP.id = cp_id AND
978 TMP.adopt_asis_yn = 'N' AND
979 PREV.article_id = TMP.article_id AND
980 PREV.article_version_id <> TMP.article_version_id AND
981 PREV.start_date = (SELECT max(VER.start_date)
982 FROM OKC_ARTICLE_VERSIONS VER
983 WHERE VER.article_id = TMP.article_id AND
984 VER.article_version_id <> TMP.article_version_id)
985 AND PREV.end_date IS NULL;
986
987 l_prev_ver_id_tbl num_tbl_type;
988 l_start_date_tbl date_tbl_type;
989 l_found BOOLEAN := FALSE;
990 l_date DATE := sysdate;
991 l_one_sec NUMBER := 1/86400; -- expressed as a part of a day, 24*60*60
992
993 BEGIN
994
995 IF (l_debug = 'Y') THEN
996 okc_debug.log('100: Entering update_prev_vers_enddate_blk: p_id='||p_id);
997 END IF;
998
999 x_return_status := FND_API.G_RET_STS_SUCCESS;
1000
1001 OPEN l_prev_ver_csr(p_id);
1002 FETCH l_prev_ver_csr BULK COLLECT INTO l_prev_ver_id_tbl, l_start_date_tbl;
1003
1004 IF (l_prev_ver_id_tbl.COUNT > 0) THEN
1005 l_found := TRUE;
1006 END IF;
1007 CLOSE l_prev_ver_csr;
1008
1009 IF l_found THEN
1010 FORALL i IN l_prev_ver_id_tbl.FIRST..l_prev_ver_id_tbl.LAST
1011 UPDATE OKC_ARTICLE_VERSIONS
1012 SET
1013 END_DATE = l_start_date_tbl(i) - l_one_sec,
1014 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
1015 LAST_UPDATED_BY = G_USER_ID,
1016 LAST_UPDATE_LOGIN = G_LOGIN_ID,
1017 LAST_UPDATE_DATE = l_date
1018 WHERE
1019 ARTICLE_VERSION_ID = l_prev_ver_id_tbl(i);
1020 END IF;
1021
1022 l_prev_ver_id_tbl.DELETE;
1023 l_start_date_tbl.DELETE;
1024
1025 IF (l_debug = 'Y') THEN
1026 okc_debug.log('200: Leaving update_prev_vers_enddate_blk: Success');
1027 END IF;
1028
1029 EXCEPTION
1030
1031 WHEN OTHERS THEN
1032 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1033 IF (l_prev_ver_csr%ISOPEN) THEN
1034 CLOSE l_prev_ver_csr;
1035 END IF;
1036 IF (l_debug = 'Y') THEN
1037 okc_debug.log('201: Leaving update_prev_vers_enddate_blk: Unknown Error');
1038 END IF;
1039
1040 IF FND_MSG_PUB.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
1041 FND_MSG_PUB.add_exc_msg(G_PKG_NAME ,l_api_name );
1042 END IF;
1043
1044 END update_prev_vers_enddate_blk;
1045
1046 ----------------------------------------------------------------------------
1047
1048 --
1049 -- p_id OKC_ART_BLK_TEMP table is populated with a number of rows containing
1050 -- global article version ids, global article ids and the corresponding
1051 -- local org ids for which the relationships need to be adopted.
1052 -- p_id identifies these rows.
1053 --
1054 -- relationship will be adopted
1055 -- 1. from the relation ship table,
1056 -- if src_article_id = input article_id, the target must be ADOPTED for the local org
1057 -- if target_article_id = input article_id, the src must be ADOPTED for the local org
1058 -- 2. the relationship has not previously been adopted
1059 --
1060 PROCEDURE adopt_relationships_blk(
1061 p_id IN NUMBER,
1062 x_return_status OUT NOCOPY VARCHAR2 )
1063 IS
1064
1065 l_api_name CONSTANT VARCHAR2(30) := 'adopt_relationships_blk';
1066 l_date DATE := sysdate;
1067
1068 BEGIN
1069
1070 IF (l_debug = 'Y') THEN
1071 okc_debug.log('100: Entering adopt_relationships_blk: p_id='||p_id);
1072 END IF;
1073 x_return_status := FND_API.G_RET_STS_SUCCESS;
1074
1075 INSERT INTO OKC_ARTICLE_RELATNS_ALL
1076 (
1077 SOURCE_ARTICLE_ID,
1078 TARGET_ARTICLE_ID,
1079 ORG_ID,
1080 RELATIONSHIP_TYPE,
1081 OBJECT_VERSION_NUMBER,
1082 CREATED_BY,
1083 CREATION_DATE,
1084 LAST_UPDATED_BY,
1085 LAST_UPDATE_LOGIN,
1086 LAST_UPDATE_DATE
1087 )
1088 SELECT REL.source_article_id,
1089 REL.target_article_id,
1090 TMP.org_id,
1091 REL.relationship_type,
1092 1.0,
1093 G_USER_ID,
1094 l_date,
1095 G_USER_ID,
1096 G_LOGIN_ID,
1097 l_date
1098 FROM OKC_ART_BLK_TEMP TMP, OKC_ARTICLE_RELATNS_ALL REL
1099 WHERE TMP.id = p_id AND
1100 REL.org_id = G_GLOBAL_ORG_ID AND
1101 REL.source_article_id = TMP.article_id AND
1102 EXISTS
1103 (SELECT 1 FROM OKC_ARTICLE_VERSIONS AV1, OKC_ARTICLE_ADOPTIONS ADP
1104 WHERE AV1.article_id = REL.target_article_id AND
1105 ADP.global_article_version_id = AV1.article_version_id AND
1106 ADP.local_org_id = TMP.org_id AND
1107 ADP.adoption_type = 'ADOPTED')
1108 AND NOT EXISTS
1109 (SELECT 1 FROM OKC_ARTICLE_RELATNS_ALL ARL1
1110 WHERE REL.source_article_id = ARL1.source_article_id AND
1111 REL.target_article_id = ARL1.target_article_id AND
1112 REL.relationship_type = ARL1.relationship_type AND
1113 ARL1.org_id = TMP.org_id);
1114
1115 INSERT INTO OKC_ARTICLE_RELATNS_ALL
1116 (
1117 SOURCE_ARTICLE_ID,
1118 TARGET_ARTICLE_ID,
1119 ORG_ID,
1120 RELATIONSHIP_TYPE,
1121 OBJECT_VERSION_NUMBER,
1122 CREATED_BY,
1123 CREATION_DATE,
1124 LAST_UPDATED_BY,
1125 LAST_UPDATE_LOGIN,
1126 LAST_UPDATE_DATE
1127 )
1128 SELECT REL.source_article_id,
1129 REL.target_article_id,
1130 TMP.org_id,
1131 REL.relationship_type,
1132 1.0,
1133 G_USER_ID,
1134 l_date,
1135 G_USER_ID,
1136 G_LOGIN_ID,
1137 l_date
1138 FROM OKC_ART_BLK_TEMP TMP, OKC_ARTICLE_RELATNS_ALL REL
1139 WHERE TMP.id = p_id AND
1140 REL.org_id = G_GLOBAL_ORG_ID AND
1141 REL.target_article_id = TMP.article_id AND
1142 EXISTS
1143 (SELECT 1 FROM OKC_ARTICLE_VERSIONS AV1, OKC_ARTICLE_ADOPTIONS ADP
1144 WHERE AV1.article_id = REL.source_article_id AND
1145 ADP.global_article_version_id = AV1.article_version_id AND
1146 ADP.local_org_id = TMP.org_id AND
1147 ADP.adoption_type = 'ADOPTED')
1148 AND NOT EXISTS
1149 (SELECT 1 FROM OKC_ARTICLE_RELATNS_ALL ARL1
1150 WHERE REL.source_article_id = ARL1.source_article_id AND
1151 REL.target_article_id = ARL1.target_article_id AND
1152 REL.relationship_type = ARL1.relationship_type AND
1153 ARL1.org_id = TMP.org_id);
1154
1155
1156 IF (l_debug = 'Y') THEN
1157 okc_debug.log('200: Leaving adopt_relationships_blk: Success');
1158 END IF;
1159
1160 EXCEPTION
1161
1162 WHEN OTHERS THEN
1163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1164
1165 IF (l_debug = 'Y') THEN
1166 okc_debug.log('201: Leaving adopt_relationships_blk: Unknown Error');
1167 END IF;
1168
1169 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR)
1170 THEN
1171 fnd_msg_pub.add_exc_msg(G_PKG_NAME ,l_api_name );
1172 END IF;
1173 END adopt_relationships_blk;
1174
1175 -----------------------------------------------------------------------------
1176
1177 PROCEDURE delete_relationships_blk(
1178 p_id IN NUMBER ,
1179 p_org_id IN NUMBER,
1180 x_return_status OUT NOCOPY VARCHAR2 )
1181 IS
1182
1183 l_api_name CONSTANT VARCHAR2(30) := 'delete_relationships_blk';
1184
1185 l_art_id_tbl num_tbl_type;
1186 l_art_ver_id_tbl num_tbl_type;
1187 l_found BOOLEAN := FALSE;
1188
1189 -- select only those article versions that are being adopted as is
1190 CURSOR l_art_id_csr(cp_id IN NUMBER) IS
1191 SELECT article_id, article_version_id
1192 FROM OKC_ART_BLK_TEMP TMP
1193 WHERE TMP.id = cp_id AND TMP.adopt_asis_yn = 'Y';
1194
1195 BEGIN
1196
1197 IF (l_debug = 'Y') THEN
1198 okc_debug.log('100: Entering delete_relationships_blk: p_id='||p_id||' p_org_id='||p_org_id);
1199 END IF;
1200
1201 x_return_status := FND_API.G_RET_STS_SUCCESS;
1202
1203 -- get the article id's first, makes the NOT EXISTS clause in Delete below, less costly
1204 OPEN l_art_id_csr(p_id);
1205 FETCH l_art_id_csr BULK COLLECT INTO l_art_id_tbl,l_art_ver_id_tbl;
1206
1207 IF (l_art_id_tbl.COUNT > 0) THEN
1208 l_found := TRUE;
1209 END IF;
1210
1211 CLOSE l_art_id_csr;
1212
1213
1214 IF (NOT l_found) THEN
1215 RETURN;
1216 END IF;
1217
1218 -- delete where source article has not been adopted
1219 FORALL i IN l_art_id_tbl.FIRST..l_art_id_tbl.LAST
1220 DELETE FROM OKC_ARTICLE_RELATNS_ALL
1221 WHERE org_id = p_org_id
1222 AND source_article_id = l_art_id_tbl(i)
1223 AND NOT EXISTS
1224 ( SELECT 1 FROM OKC_ARTICLE_ADOPTIONS ADP, OKC_ARTICLE_VERSIONS AV1
1225 WHERE AV1.article_id = l_art_id_tbl(i)
1226 AND AV1.article_version_id <> l_art_ver_id_tbl(i)
1227 AND ADP.adoption_type = 'ADOPTED'
1228 AND ADP.global_article_version_id = AV1.article_version_id
1229 AND ADP.local_org_id = p_org_id
1230 );
1231
1232 -- delete where target article has not been adopted
1233 FORALL i IN l_art_id_tbl.FIRST..l_art_id_tbl.LAST
1234 DELETE FROM OKC_ARTICLE_RELATNS_ALL
1235 WHERE org_id = p_org_id
1236 AND target_article_id = l_art_id_tbl(i)
1237 AND NOT EXISTS
1238 ( SELECT 1 FROM OKC_ARTICLE_ADOPTIONS ADP, OKC_ARTICLE_VERSIONS AV2
1239 WHERE AV2.article_id = l_art_id_tbl(i)
1240 AND AV2.article_version_id <> l_art_ver_id_tbl(i)
1241 AND ADP.adoption_type = 'ADOPTED'
1242 AND ADP.global_article_version_id = AV2.article_version_id
1243 AND ADP.local_org_id = p_org_id
1244 );
1245
1246 l_art_id_tbl.DELETE;
1247 l_art_ver_id_tbl.DELETE;
1248
1249 IF (l_debug = 'Y') THEN
1250 okc_debug.log('200: Leaving delete_relationships_blk: Success');
1251 END IF;
1252
1253 EXCEPTION
1254
1255 WHEN OTHERS THEN
1256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1257 IF (l_art_id_csr%ISOPEN) THEN
1258 CLOSE l_art_id_csr;
1259 END IF;
1260
1261 IF (l_debug = 'Y') THEN
1262 okc_debug.log('201: Leaving delete_relationships_blk: Unknown error');
1263 END IF;
1264
1265 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
1266 fnd_msg_pub.add_exc_msg(G_PKG_NAME ,l_api_name );
1267 END IF;
1268
1269 END delete_relationships_blk;
1270
1271 ---------------------------------------------------------------------
1272
1273 PROCEDURE validate_article_versions_blk(
1274 p_api_version IN NUMBER ,
1275 p_init_msg_list IN VARCHAR2 ,
1276 p_commit IN VARCHAR2 ,
1277 p_validation_level IN NUMBER ,
1278 x_return_status OUT NOCOPY VARCHAR2 ,
1279 x_msg_count OUT NOCOPY NUMBER ,
1280 x_msg_data OUT NOCOPY VARCHAR2 ,
1281
1282 p_org_id IN NUMBER ,
1283 p_art_ver_tbl IN num_tbl_type ,
1284 p_id IN NUMBER,
1285 x_qa_return_status OUT NOCOPY VARCHAR2,
1286 x_validation_results OUT NOCOPY validation_tbl_type )
1287
1288 IS
1289
1290 l_api_version NUMBER := 1.0;
1291 l_api_name VARCHAR2(30) := 'validate_article_versions_blk';
1292
1293 l_id NUMBER;
1294 l_return_status VARCHAR2(1);
1295 l_qa_return_status VARCHAR2(1);
1296
1297
1298 l_adopt_asis_art_id_tbl num_tbl_type;
1299 l_adopt_asis_art_ver_id_tbl num_tbl_type;
1300 l_adopt_asis_art_title_tbl varchar_tbl_type;
1301
1302 l_curr_msg_count NUMBER;
1303 l_local_result_status VARCHAR2(1);
1304 l_msg_count NUMBER;
1305 l_msg_data VARCHAR2(2000);
1306 l_earlier_local_version_id VARCHAR2(250);
1307
1308 l_err_num NUMBER;
1309 i NUMBER;
1310
1311 l_adopt_asis_count NUMBER := 0;
1312 l_global_count NUMBER := 0;
1313 l_localized_count NUMBER := 0;
1314
1315 -- get some details about adopt as is articles for filling the x_validation_results table
1316 CURSOR l_adopt_as_is_csr(cp_id IN NUMBER) IS
1317 SELECT TMP.article_id, TMP.article_version_id, nvl(TMP.display_name, TMP.article_title)
1318 FROM OKC_ART_BLK_TEMP TMP
1319 WHERE TMP.id = cp_id AND
1320 TMP.adopt_asis_yn = 'Y';
1321
1322
1323 BEGIN
1324
1325 IF (l_debug = 'Y') THEN
1326 okc_debug.log('100: Entering validate_article_versions_blk: p_api_version='||p_api_version||' p_init_msg_list='||p_init_msg_list||' p_commit='||p_commit);
1327
1328 okc_debug.log('101: In validate_article_versions_blk: p_validation_level='|| p_validation_level||' p_org_id='||p_org_id||' p_id='||p_id);
1329
1330 IF (p_art_ver_tbl IS NOT NULL) THEN
1331 okc_debug.log('102: p_art_ver_tbl.COUNT='||p_art_ver_tbl.COUNT);
1332 FOR i in p_art_ver_tbl.FIRST..p_art_ver_tbl.LAST LOOP
1333 okc_debug.log('103: p_art_ver_tbl['||i||']='||p_art_ver_tbl(i));
1334 END LOOP;
1335 END IF;
1336 END IF;
1337
1338 -- standard initialization code
1339 SAVEPOINT val_article_versions_blk_PVT;
1340 G_GLOBAL_ORG_ID := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
1341 l_debug := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
1342
1343 IF NOT fnd_api.compatible_api_call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1345 END IF;
1346 IF fnd_api.to_boolean( p_init_msg_list ) THEN
1347 fnd_msg_pub.initialize;
1348 END IF;
1349 x_return_status := FND_API.G_RET_STS_SUCCESS;
1350 x_qa_return_status := FND_API.G_RET_STS_SUCCESS;
1351
1352 l_return_status := FND_API.G_RET_STS_SUCCESS;
1353 l_qa_return_status := FND_API.G_RET_STS_SUCCESS;
1354
1355 IF (p_validation_level <> FND_API.G_VALID_LEVEL_FULL) THEN
1356 RETURN;
1357 END IF;
1358
1359 IF (p_id IS NULL) THEN
1360 -- get the versions details only if p_id is not set
1361 get_version_details(
1362 p_org_id => p_org_id,
1363 p_art_ver_tbl => p_art_ver_tbl,
1364 x_return_status => x_return_status,
1365 x_id => l_id,
1366 x_adopt_asis_count => l_adopt_asis_count,
1367 x_global_count => l_global_count,
1368 x_localized_count => l_localized_count
1369 );
1370 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1371 RAISE FND_API.G_EXC_ERROR ;
1372 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1373 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1374 END IF;
1375 ELSE
1376 l_id := p_id;
1377 END IF;
1378
1379 variable_check_blk(
1380 p_id => l_id,
1381 x_return_status => x_return_status,
1382 x_qa_return_status => x_qa_return_status,
1383 px_validation_results => x_validation_results
1384 );
1385 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1386 RAISE FND_API.G_EXC_ERROR ;
1387 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1388 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1389 END IF;
1390
1391 section_type_check_blk(
1392 p_id => l_id,
1393 x_return_status => x_return_status,
1394 x_qa_return_status => x_qa_return_status,
1395 px_validation_results => x_validation_results
1396 );
1397 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1398 RAISE FND_API.G_EXC_ERROR ;
1399 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1400 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1401 END IF;
1402
1403 IF(p_org_id <> G_GLOBAL_ORG_ID) THEN
1404
1405 l_adopt_asis_art_id_tbl := num_tbl_type();
1406 l_adopt_asis_art_ver_id_tbl := num_tbl_type();
1407 --l_adopt_asis_art_title_tbl := varchar_tbl_type();
1408
1409 -- check adopt as is articles using existing code
1410 OPEN l_adopt_as_is_csr(l_id);
1411 FETCH l_adopt_as_is_csr BULK COLLECT INTO l_adopt_asis_art_id_tbl, l_adopt_asis_art_ver_id_tbl, l_adopt_asis_art_title_tbl;
1412 CLOSE l_adopt_as_is_csr;
1413
1414 IF (l_adopt_asis_art_id_tbl.COUNT > 0) THEN
1415
1416 -- check adopt_as_is articles for adoption details
1417 i := l_adopt_asis_art_id_tbl.FIRST;
1418
1419 WHILE i is NOT NULL LOOP
1420
1421 -- reset all loop variables
1422 -- get the current message count, only new messages should be fetched and
1423 -- put in the x_validation_results table;
1424
1425 l_curr_msg_count := fnd_msg_pub.count_msg;
1426 l_local_result_status := FND_API.G_RET_STS_SUCCESS;
1427 l_msg_count := 0;
1428 l_msg_data := 0;
1429 l_earlier_local_version_id := 0;
1430
1431 IF (l_debug = 'Y') THEN
1432 okc_debug.log('104: Calling OKC_ADOPTIONS_GRP.check_adoption_details p_global_article_version_id='||l_adopt_asis_art_ver_id_tbl(i)|| ' p_adoption_type=ADOPTED p_local_org_id='||p_org_id);
1433 END IF;
1434
1435 OKC_ADOPTIONS_GRP.check_adoption_details(
1436 p_api_version => 1.0,
1437 p_init_msg_list => FND_API.G_FALSE,
1438 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1439 x_return_status => l_local_result_status ,
1440 x_msg_count => l_msg_count,
1441 x_msg_data => l_msg_data,
1442 x_earlier_local_version_id => l_earlier_local_version_id,
1443 p_global_article_version_id => l_adopt_asis_art_ver_id_tbl(i),
1444 p_adoption_type => 'ADOPTED',
1445 p_local_org_id => p_org_id
1446 );
1447
1448 IF (l_debug = 'Y') THEN
1449 okc_debug.log('105: After OKC_ADOPTIONS_GRP.check_adoption_details x_return_status='||l_local_result_status);
1450 END IF;
1451
1452 IF (l_local_result_status = FND_API.G_RET_STS_ERROR) THEN -- the check failed
1453 x_qa_return_status := FND_API.G_RET_STS_ERROR;
1454
1455 FOR j in 1..(l_msg_count - l_curr_msg_count) LOOP
1456 l_err_num := x_validation_results.COUNT +1;
1457 x_validation_results(l_err_num).article_id := l_adopt_asis_art_id_tbl(i);
1458 x_validation_results(l_err_num).article_version_id := l_adopt_asis_art_ver_id_tbl(i);
1459 x_validation_results(l_err_num).article_title := l_adopt_asis_art_title_tbl(i);
1460 x_validation_results(l_err_num).error_code := G_CHK_INVALID_ADOPTION;
1461 -- get the new messages
1462 x_validation_results(l_err_num).error_message := fnd_msg_pub.get(p_msg_index => l_curr_msg_count +j, p_encoded => 'F');
1463 END LOOP;
1464
1465 ELSIF (l_local_result_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1466 -- for unexpected error get the error messages, put a different code
1467 -- and try to validate the next version
1468
1469 x_qa_return_status := FND_API.G_RET_STS_ERROR;
1470
1471 FOR j in 1..(l_msg_count - l_curr_msg_count) LOOP
1472 l_err_num := x_validation_results.COUNT +1;
1473 x_validation_results(l_err_num).article_id := l_adopt_asis_art_id_tbl(i);
1474 x_validation_results(l_err_num).article_version_id := l_adopt_asis_art_ver_id_tbl(i);
1475 x_validation_results(l_err_num).article_title := l_adopt_asis_art_title_tbl(i);
1476 x_validation_results(l_err_num).error_code := G_CHK_ADOPTION_UNEXP_ERROR;
1477 -- get the new messages
1478 x_validation_results(l_err_num).error_message := fnd_msg_pub.get(p_msg_index => l_curr_msg_count +j, p_encoded => 'F');
1479 END LOOP;
1480
1481 END IF;
1482
1483 i := l_adopt_asis_art_id_tbl.NEXT(i);
1484
1485 END LOOP;
1486
1487 END IF; -- of IF (l_adopt_asis_art_id_tbl.COUNT > 0) THEN
1488
1489 l_adopt_asis_art_id_tbl.DELETE;
1490 l_adopt_asis_art_ver_id_tbl.DELETE;
1491 l_adopt_asis_art_title_tbl.DELETE;
1492
1493 END IF; --of IF(p_org_id <> G_GLOBAL_ORG_ID) THEN
1494
1495
1496 -- if any errors are found set the appropriate return status
1497 IF (x_validation_results.COUNT >0 ) THEN
1498 x_qa_return_status := FND_API.G_RET_STS_ERROR;
1499 END IF;
1500
1501 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
1502 IF fnd_api.to_boolean( p_commit ) THEN
1503 COMMIT WORK;
1504 END IF;
1505
1506 IF (l_debug = 'Y') THEN
1507 okc_debug.log('200: Leaving validate_article_versions_blk: Success');
1508 END IF;
1509
1510
1511 EXCEPTION
1512
1513 WHEN FND_API.G_EXC_ERROR THEN
1514
1515 ROLLBACK TO val_article_versions_blk_PVT;
1516 x_return_status := FND_API.G_RET_STS_ERROR ;
1517
1518 IF (l_debug = 'Y') THEN
1519 okc_debug.log('201: Leaving validate_article_versions_blk: Error');
1520 END IF;
1521
1522 IF (l_adopt_as_is_csr%ISOPEN) THEN
1523 CLOSE l_adopt_as_is_csr;
1524 END IF;
1525 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
1526
1527 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1528
1529 ROLLBACK TO val_article_versions_blk_PVT;
1530 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1531
1532 IF (l_debug = 'Y') THEN
1533 okc_debug.log('202: Leaving validate_article_versions_blk: Unexpected Error');
1534 END IF;
1535
1536 IF (l_adopt_as_is_csr%ISOPEN) THEN
1537 CLOSE l_adopt_as_is_csr;
1538 END IF;
1539 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
1540
1541 WHEN OTHERS THEN
1542
1543 ROLLBACK TO val_article_versions_blk_PVT;
1544 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1545
1546 IF (l_debug = 'Y') THEN
1547 okc_debug.log('203: Leaving validate_article_versions_blk: Unknown Error');
1548 END IF;
1549
1550 IF (l_adopt_as_is_csr%ISOPEN) THEN
1551 CLOSE l_adopt_as_is_csr;
1552 END IF;
1553
1554 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR)
1555 THEN
1556 fnd_msg_pub.add_exc_msg(G_PKG_NAME ,l_api_name );
1557 END IF;
1558 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
1559
1560 END validate_article_versions_blk;
1561
1562 -----------------------------------------------------------------------------
1563
1564 PROCEDURE auto_adopt_articles_blk(
1565 p_api_version IN NUMBER ,
1566 p_init_msg_list IN VARCHAR2 ,
1567 p_commit IN VARCHAR2 ,
1568 x_return_status OUT NOCOPY VARCHAR2 ,
1569 x_msg_count OUT NOCOPY NUMBER ,
1570 x_msg_data OUT NOCOPY VARCHAR2 ,
1571
1572 p_id IN NUMBER)
1573
1574 IS
1575
1576 l_api_version NUMBER := 1.0;
1577 l_api_name VARCHAR2(30) := 'auto_adopt_articles_blk';
1578 l_date DATE := sysdate;
1579
1580 CURSOR l_org_info_csr(cp_global_org_id IN NUMBER) IS
1581 SELECT ORG.organization_id, decode(ORG.org_information1, 'Y', 'ADOPTED', 'AVAILABLE')
1582 FROM HR_ORGANIZATION_INFORMATION ORG
1583 WHERE ORG.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS' AND
1584 ORG.organization_id <> cp_global_org_id;
1585
1586 CURSOR l_non_uniq_title_csr(cp_id IN NUMBER, cp_global_org_id IN NUMBER) IS
1587 -- check if article title is unique for the local org also
1588 -- if title is not unique in the local org, set to 'AVAILABLE' and NULL
1589 SELECT TMP.article_version_id , ART.org_id
1590 FROM OKC_ART_BLK_TEMP TMP, OKC_ARTICLES_ALL ART
1591 WHERE TMP.id = cp_id AND
1592 TMP.global_yn = 'Y' AND
1593 ART.article_title = TMP.article_title AND
1594 ART.org_id <> cp_global_org_id ;
1595
1596 l_org_id_tbl num_tbl_type;
1597 l_adp_typ_tbl varchar_tbl_type;
1598 l_non_uniq_art_ver_tbl num_tbl_type;
1599 l_non_uniq_org_id_tbl num_tbl_type;
1600
1601 l_found BOOLEAN := FALSE;
1602 l_id NUMBER;
1603
1604 BEGIN
1605
1606 IF (l_debug = 'Y') THEN
1607 okc_debug.log('100: Entering auto_adopt_articles_blk: p_api_version='||p_api_version||' p_init_msg_list='||p_init_msg_list||' p_commit='||p_commit||' p_id='||p_id);
1608 END IF;
1609
1610 -- standard initialization code
1611 SAVEPOINT auto_adopt_articles_blk_PVT;
1612 G_GLOBAL_ORG_ID := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
1613 G_USER_ID := FND_GLOBAL.USER_ID;
1614 G_LOGIN_ID := FND_GLOBAL.LOGIN_ID;
1615 l_debug := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
1616
1617 IF NOT fnd_api.compatible_api_call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1618 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1619 END IF;
1620 IF fnd_api.to_boolean( p_init_msg_list ) THEN
1621 fnd_msg_pub.initialize;
1622 END IF;
1623 x_return_status := FND_API.G_RET_STS_SUCCESS;
1624
1625 OPEN l_org_info_csr(G_GLOBAL_ORG_ID);
1626 FETCH l_org_info_csr BULK COLLECT INTO l_org_id_tbl, l_adp_typ_tbl;
1627
1628 IF (l_org_id_tbl.COUNT > 0) THEN
1629 l_found := TRUE;
1630 END IF;
1631 CLOSE l_org_info_csr;
1632
1633 IF NOT l_found THEN
1634 -- no local orgs found
1635 RETURN;
1636 END IF;
1637
1638 -- first insert rows in adoptions table for each clause and local org
1639 FORALL i IN l_org_id_tbl.FIRST.. l_org_id_tbl.LAST
1640 INSERT INTO OKC_ARTICLE_ADOPTIONS
1641 (
1642 GLOBAL_ARTICLE_VERSION_ID,
1643 ADOPTION_TYPE,
1644 LOCAL_ORG_ID,
1645 ADOPTION_STATUS,
1646 LOCAL_ARTICLE_VERSION_ID,
1647 OBJECT_VERSION_NUMBER,
1648 CREATED_BY,
1649 CREATION_DATE,
1650 LAST_UPDATED_BY,
1651 LAST_UPDATE_LOGIN,
1652 LAST_UPDATE_DATE
1653 )
1654 SELECT TMP.article_version_id, l_adp_typ_tbl(i), l_org_id_tbl(i),
1655 decode(l_adp_typ_tbl(i), 'ADOPTED', 'APPROVED', NULL),
1656 NULL, 1, G_USER_ID, l_date,
1657 G_USER_ID, G_LOGIN_ID, l_date
1658 FROM OKC_ART_BLK_TEMP TMP
1659 WHERE TMP.id = p_id AND
1660 TMP.global_yn = 'Y';
1661
1662 -- now for local orgs, check if any clauses
1663 -- with the same title existed
1664 OPEN l_non_uniq_title_csr(p_id, G_GLOBAL_ORG_ID);
1665 FETCH l_non_uniq_title_csr BULK COLLECT INTO l_non_uniq_art_ver_tbl, l_non_uniq_org_id_tbl;
1666
1667 l_found := FALSE;
1668 IF (l_non_uniq_art_ver_tbl.COUNT > 0) THEN
1669 l_found := TRUE;
1670 END IF;
1671 CLOSE l_non_uniq_title_csr;
1672
1673 IF (l_found) THEN
1674 -- need to update adoption type to 'AVAILABLE', status to NULL
1675
1676 FORALL i IN l_non_uniq_art_ver_tbl.FIRST..l_non_uniq_art_ver_tbl.LAST
1677 UPDATE OKC_ARTICLE_ADOPTIONS
1678 SET
1679 ADOPTION_TYPE = 'AVAILABLE',
1680 ADOPTION_STATUS = NULL
1681 WHERE
1682 GLOBAL_ARTICLE_VERSION_ID = l_non_uniq_art_ver_tbl(i)
1683 AND LOCAL_ORG_ID = l_non_uniq_org_id_tbl(i);
1684
1685 END IF; -- end of if (l_found)
1686
1687
1688 -- now adopt relationship for those article versions/local orgs
1689 -- where adoption_type = 'ADOPTED'
1690 l_id := get_uniq_id;
1691 INSERT INTO OKC_ART_BLK_TEMP
1692 (
1693 ID,
1694 ARTICLE_ID,
1695 ARTICLE_VERSION_ID,
1696 ORG_ID
1697 )
1698 SELECT l_id, TMP.article_id, TMP.article_version_id, ADP.local_org_id
1699 FROM OKC_ART_BLK_TEMP TMP, OKC_ARTICLE_ADOPTIONS ADP
1700 WHERE TMP.id = p_id AND
1701 TMP.global_yn = 'Y' AND
1702 ADP.global_article_version_id = TMP.article_version_id AND
1703 ADP.adoption_type = 'ADOPTED';
1704
1705 adopt_relationships_blk(
1706 p_id => l_id,
1707 x_return_status => x_return_status
1708 );
1709 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1710 RAISE FND_API.G_EXC_ERROR ;
1711 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1712 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1713 END IF;
1714
1715 l_non_uniq_art_ver_tbl.DELETE;
1716 l_non_uniq_org_id_tbl.DELETE;
1717
1718 l_org_id_tbl.DELETE;
1719 l_adp_typ_tbl.DELETE;
1720
1721 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
1722 IF fnd_api.to_boolean( p_commit ) THEN
1723 COMMIT;
1724 END IF;
1725
1726 IF (l_debug = 'Y') THEN
1727 okc_debug.log('200: Leaving auto_adopt_articles_blk: Success');
1728 END IF;
1729
1730 EXCEPTION
1731
1732 WHEN FND_API.G_EXC_ERROR THEN
1733 ROLLBACK TO auto_adopt_articles_blk_PVT;
1734 x_return_status := FND_API.G_RET_STS_ERROR ;
1735
1736 IF (l_debug = 'Y') THEN
1737 okc_debug.log('201: Leaving auto_adopt_articles_blk: Error');
1738 END IF;
1739 IF (l_org_info_csr%ISOPEN) THEN
1740 CLOSE l_org_info_csr;
1741 END IF;
1742 IF (l_non_uniq_title_csr%ISOPEN) THEN
1743 CLOSE l_non_uniq_title_csr;
1744 END IF;
1745 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
1746
1747 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1748 ROLLBACK TO auto_adopt_articles_blk_PVT;
1749 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1750
1751 IF (l_debug = 'Y') THEN
1752 okc_debug.log('202: Leaving auto_adopt_articles_blk: Unexpected Error');
1753 END IF;
1754 IF (l_org_info_csr%ISOPEN) THEN
1755 CLOSE l_org_info_csr;
1756 END IF;
1757 IF (l_non_uniq_title_csr%ISOPEN) THEN
1758 CLOSE l_non_uniq_title_csr;
1759 END IF;
1760 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
1761
1762 WHEN OTHERS THEN
1763 ROLLBACK TO auto_adopt_articles_blk_PVT;
1764 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1765
1766 IF (l_debug = 'Y') THEN
1767 okc_debug.log('203: Leaving auto_adopt_articles_blk: Unknown Error');
1768 END IF;
1769 IF (l_org_info_csr%ISOPEN) THEN
1770 CLOSE l_org_info_csr;
1771 END IF;
1772 IF (l_non_uniq_title_csr%ISOPEN) THEN
1773 CLOSE l_non_uniq_title_csr;
1774 END IF;
1775
1776 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR)
1777 THEN
1778 fnd_msg_pub.add_exc_msg(G_PKG_NAME ,l_api_name );
1779 END IF;
1780 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
1781
1782 END auto_adopt_articles_blk;
1783
1784 -----------------------------------------------------------------------------
1785
1786
1787 -- IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL) THEN do validations
1788
1789 PROCEDURE pending_approval_blk(
1790 p_api_version IN NUMBER ,
1791 p_init_msg_list IN VARCHAR2 ,
1792 p_commit IN VARCHAR2 ,
1793 p_validation_level IN NUMBER ,
1794 x_return_status OUT NOCOPY VARCHAR2 ,
1795 x_msg_count OUT NOCOPY NUMBER ,
1796 x_msg_data OUT NOCOPY VARCHAR2 ,
1797
1798 p_org_id IN NUMBER ,
1799 p_art_ver_tbl IN num_tbl_type ,
1800 x_validation_results OUT NOCOPY validation_tbl_type )
1801 IS
1802
1803 l_api_version NUMBER := 1.0;
1804 l_api_name VARCHAR2(30) := 'pending_approval_blk';
1805
1806 l_qa_return_status VARCHAR2(1);
1807 l_id NUMBER := -1;
1808 l_rel_id NUMBER := -1;
1809
1810 l_adopt_asis_count NUMBER := 0;
1811 l_global_count NUMBER := 0;
1812 l_localized_count NUMBER := 0;
1813
1814 BEGIN
1815
1816 IF (l_debug = 'Y') THEN
1817 okc_debug.log('100: Entering pending_approval_blk: p_api_version='||p_api_version||' p_init_msg_list='||p_init_msg_list||' p_commit='||p_commit);
1818
1819 okc_debug.log('101: In pending_approval_blk: p_validation_level='|| p_validation_level||' p_org_id='||p_org_id);
1820
1821 IF (p_art_ver_tbl IS NOT NULL) THEN
1822 okc_debug.log('102: p_art_ver_tbl.COUNT='||p_art_ver_tbl.COUNT);
1823 FOR i in p_art_ver_tbl.FIRST..p_art_ver_tbl.LAST LOOP
1824 okc_debug.log('103: p_art_ver_tbl['||i||']='||p_art_ver_tbl(i));
1825 END LOOP;
1826 END IF;
1827 END IF;
1828
1829 -- standard initialization code
1830 SAVEPOINT pending_approval_blk_PVT;
1831 G_GLOBAL_ORG_ID := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
1832 G_USER_ID := FND_GLOBAL.USER_ID;
1833 G_LOGIN_ID := FND_GLOBAL.LOGIN_ID;
1834 l_debug := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
1835
1836 IF NOT fnd_api.compatible_api_call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1837 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1838 END IF;
1839 IF fnd_api.to_boolean( p_init_msg_list ) THEN
1840 fnd_msg_pub.initialize;
1841 END IF;
1842 x_return_status := FND_API.G_RET_STS_SUCCESS;
1843
1844
1845 -- first populate the the temp table with all the relevant details
1846 get_version_details(
1847 p_org_id => p_org_id,
1848 p_art_ver_tbl => p_art_ver_tbl,
1849 x_return_status => x_return_status,
1850 x_id => l_id,
1851 x_adopt_asis_count => l_adopt_asis_count,
1852 x_global_count => l_global_count,
1853 x_localized_count => l_localized_count
1854 );
1855 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1856 RAISE FND_API.G_EXC_ERROR ;
1857 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1858 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1859 END IF;
1860
1861 -- check if all versions are in draft/rejected/null status first
1862 -- this is not part of normal qa, but a basic sanity check required
1863 -- for all status transitions
1864 status_check_blk(
1865 p_id => l_id,
1866 p_to_status => 'PENDING_APPROVAL',
1867 x_return_status => x_return_status,
1868 x_qa_return_status => l_qa_return_status,
1869 px_validation_results => x_validation_results
1870 );
1871 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1872 RAISE FND_API.G_EXC_ERROR ;
1873 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1874 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1875 ELSE
1876 -- status check failed
1877 IF (l_qa_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1878 x_return_status := FND_API.G_RET_STS_ERROR;
1879 RAISE FND_API.G_EXC_ERROR ;
1880 END IF;
1881 END IF;
1882
1883 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL) THEN
1884
1885 validate_article_versions_blk(
1886 p_api_version => 1.0 ,
1887 p_init_msg_list => FND_API.G_FALSE,
1888 p_commit => FND_API.G_FALSE,
1889 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1890 x_return_status => x_return_status,
1891 x_msg_count => x_msg_count,
1892 x_msg_data => x_msg_data,
1893
1894 p_org_id => p_org_id,
1895 -- call with null p_art_ver_tbl so that validate_article_versions_blk
1896 -- does not have to get the article details again
1897 p_art_ver_tbl => NULL,
1898 p_id => l_id,
1899 x_qa_return_status => l_qa_return_status,
1900 x_validation_results => x_validation_results
1901 );
1902
1903 -- whenever call another api check for return status,
1904 -- no need to check the return status for utility functions
1905
1906 -- in case of ERROR, the calling program will look into x_validation_results
1907 -- and should display them to user for corrective action
1908 -- for UNEXPECTED ERROR, the calling program would set the status
1909 -- as G_RET_STS_UNEXP_ERROR and exit
1910
1911 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1912 RAISE FND_API.G_EXC_ERROR ;
1913 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1914 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1915 ElSE
1916 -- validation executed successfully buth there where some
1917 -- validation errors. we should stop here
1918 IF (l_qa_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1919 x_return_status := FND_API.G_RET_STS_ERROR;
1920 RAISE FND_API.G_EXC_ERROR ;
1921 END IF;
1922 END IF;
1923
1924 END IF; --p_validation_level = FND_API.G_VALID_LEVEL_FULL
1925
1926
1927
1928 -- common for global/local/localized clauses, will do nothing for adopt as is clauses.
1929 update_art_version_status_blk(
1930 p_id => l_id,
1931 p_status => 'PENDING_APPROVAL',
1932 x_return_status => x_return_status
1933 );
1934 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1935 RAISE FND_API.G_EXC_ERROR ;
1936 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1937 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1938 END IF;
1939
1940
1941 IF (p_org_id <> G_GLOBAL_ORG_ID) THEN
1942 -- we are not in the global org
1943
1944 -- for localized clauses only
1945 IF (l_localized_count > 0) THEN
1946 update_adp_status_type_blk(
1947 p_id => l_id,
1948 p_local_org_id => p_org_id,
1949 p_adoption_status => 'PENDING_APPROVAL',
1950 p_adoption_type => 'LOCALIZED',
1951 p_type => 'LOCALIZED',
1952 x_return_status => x_return_status
1953 );
1954 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1955 RAISE FND_API.G_EXC_ERROR ;
1956 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1957 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1958 END IF;
1959 END IF;
1960
1961 -- we are adopting some article versions as is.
1962 IF (l_adopt_asis_count > 0) THEN
1963 -- 1. Update Adoption Row
1964 -- 2. Insert Relationships if first version of any articles are being adopted.
1965
1966 update_adp_status_type_blk(
1967 p_id => l_id,
1968 p_local_org_id => p_org_id,
1969 p_adoption_status => 'PENDING_APPROVAL',
1970 p_adoption_type => 'ADOPTED',
1971 p_type => 'ADOPTED',
1972 x_return_status => x_return_status
1973 );
1974 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1975 RAISE FND_API.G_EXC_ERROR ;
1976 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1977 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1978 END IF;
1979
1980 -- now adopt relationship for these articles
1981 -- first populate the temp table with whatever adopt_relationships_blk requires
1982 l_rel_id := get_uniq_id;
1983 INSERT INTO OKC_ART_BLK_TEMP
1984 (
1985 ID,
1986 ARTICLE_ID,
1987 ARTICLE_VERSION_ID,
1988 ORG_ID
1989 )
1990 SELECT l_rel_id, TMP.article_id, TMP.article_version_id, p_org_id
1991 FROM OKC_ART_BLK_TEMP TMP
1992 WHERE TMP.id = l_id AND
1993 TMP.adopt_asis_yn = 'Y';
1994
1995 adopt_relationships_blk(
1996 p_id => l_rel_id,
1997 x_return_status => x_return_status
1998 );
1999 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2000 RAISE FND_API.G_EXC_ERROR ;
2001 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2002 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2003 END IF;
2004
2005 END IF;
2006
2007 END IF; -- of IF (p_org_id <> G_GLOBAL_ORG_ID)
2008
2009 -- delete rows created in the temp table
2010 DELETE FROM OKC_ART_BLK_TEMP
2011 WHERE id IN (l_id, l_rel_id);
2012
2013 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2014 IF(FND_API.to_boolean(p_commit)) THEN
2015 COMMIT;
2016 END IF;
2017
2018 IF (l_debug = 'Y') THEN
2019 okc_debug.log('200: Leaving pending_approval_blk: Success');
2020 END IF;
2021
2022 EXCEPTION
2023
2024 WHEN FND_API.G_EXC_ERROR THEN
2025 ROLLBACK TO pending_approval_blk_PVT;
2026 x_return_status := FND_API.G_RET_STS_ERROR ;
2027
2028 IF (l_debug = 'Y') THEN
2029 okc_debug.log('201: Leaving pending_approval_blk: Error');
2030 END IF;
2031 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2032
2033 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2034 ROLLBACK TO pending_approval_blk_PVT;
2035 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2036
2037 IF (l_debug = 'Y') THEN
2038 okc_debug.log('202: Leaving pending_approval_blk: Unexpected Error');
2039 END IF;
2040
2041 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2042
2043 WHEN OTHERS THEN
2044 ROLLBACK TO pending_approval_blk_PVT;
2045 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2046
2047 IF (l_debug = 'Y') THEN
2048 okc_debug.log('203: Leaving pending_approval_blk: Unknown Error');
2049 END IF;
2050
2051 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR)
2052 THEN
2053 fnd_msg_pub.add_exc_msg(G_PKG_NAME ,l_api_name );
2054 END IF;
2055 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2056
2057 END pending_approval_blk;
2058
2059 ---------------------------------------------------------------------
2060
2061 PROCEDURE approve_blk(
2062 p_api_version IN NUMBER ,
2063 p_init_msg_list IN VARCHAR2 ,
2064 p_commit IN VARCHAR2 ,
2065 x_return_status OUT NOCOPY VARCHAR2 ,
2066 x_msg_count OUT NOCOPY NUMBER ,
2067 x_msg_data OUT NOCOPY VARCHAR2 ,
2068
2069 p_org_id IN NUMBER ,
2070 p_art_ver_tbl IN num_tbl_type,
2071 x_validation_results OUT NOCOPY validation_tbl_type )
2072 IS
2073
2074 l_api_version NUMBER := 1.0;
2075 l_api_name VARCHAR2(30) := 'approve_blk';
2076
2077 l_qa_return_status VARCHAR2(1);
2078 l_id NUMBER := -1;
2079
2080 l_adopt_asis_count NUMBER := 0;
2081 l_global_count NUMBER := 0;
2082 l_localized_count NUMBER := 0;
2083
2084 BEGIN
2085
2086 IF (l_debug = 'Y') THEN
2087 okc_debug.log('100: Entering approve_blk: p_api_version='||p_api_version||' p_init_msg_list='||p_init_msg_list||' p_commit='||p_commit);
2088
2089 okc_debug.log('101: In approve_blk: p_org_id='||p_org_id);
2090
2091 IF (p_art_ver_tbl IS NOT NULL) THEN
2092 okc_debug.log('102: p_art_ver_tbl.COUNT='||p_art_ver_tbl.COUNT);
2093 FOR i in p_art_ver_tbl.FIRST..p_art_ver_tbl.LAST LOOP
2094 okc_debug.log('103: p_art_ver_tbl['||i||']='||p_art_ver_tbl(i));
2095 END LOOP;
2096 END IF;
2097 END IF;
2098
2099 -- standard initialization code
2100 SAVEPOINT approve_blk_PVT;
2101 G_GLOBAL_ORG_ID := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
2102 G_USER_ID := FND_GLOBAL.USER_ID;
2103 G_LOGIN_ID := FND_GLOBAL.LOGIN_ID;
2104 l_debug := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
2105
2106 IF NOT fnd_api.compatible_api_call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2108 END IF;
2109 IF fnd_api.to_boolean( p_init_msg_list ) THEN
2110 fnd_msg_pub.initialize;
2111 END IF;
2112 x_return_status := FND_API.G_RET_STS_SUCCESS;
2113
2114
2115 -- first populate the the temp table with all the relevant details
2116 get_version_details(
2117 p_org_id => p_org_id,
2118 p_art_ver_tbl => p_art_ver_tbl,
2119 x_return_status => x_return_status,
2120 x_id => l_id,
2121 x_adopt_asis_count => l_adopt_asis_count,
2122 x_global_count => l_global_count,
2123 x_localized_count => l_localized_count
2124 );
2125 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2126 RAISE FND_API.G_EXC_ERROR ;
2127 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2128 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2129 END IF;
2130
2131 -- check if all versions are in pending approval status first
2132 -- this is not part of normal qa, but a basic sanity check required
2133 -- for all status transitions
2134 status_check_blk(
2135 p_id => l_id,
2136 p_to_status => 'APPROVED',
2137 x_return_status => x_return_status,
2138 x_qa_return_status => l_qa_return_status,
2139 px_validation_results => x_validation_results
2140 );
2141 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2142 RAISE FND_API.G_EXC_ERROR ;
2143 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2144 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2145 ELSE
2146 -- status check failed
2147 IF (l_qa_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2148 x_return_status := FND_API.G_RET_STS_ERROR;
2149 RAISE FND_API.G_EXC_ERROR ;
2150 END IF;
2151 END IF;
2152
2153
2154 -- common for global/local/localized clauses, will do nothing for adopt as is clauses.
2155 update_art_version_status_blk(
2156 p_id => l_id,
2157 p_status => 'APPROVED',
2158 x_return_status => x_return_status
2159 );
2160 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2161 RAISE FND_API.G_EXC_ERROR ;
2162 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2163 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2164 END IF;
2165
2166
2167 -- common for global/local/localized clauses, will do nothing for adopt as is clauses.
2168 update_prev_vers_enddate_blk(
2169 p_id => l_id,
2170 x_return_status => x_return_status
2171 );
2172 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2173 RAISE FND_API.G_EXC_ERROR ;
2174 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2175 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2176 END IF;
2177
2178
2179 IF (p_org_id = G_GLOBAL_ORG_ID) THEN
2180
2181 IF (l_global_count > 0) THEN
2182 -- kick off auto adoption
2183
2184 auto_adopt_articles_blk(
2185 p_api_version => 1.0,
2186 p_init_msg_list => FND_API.G_FALSE,
2187 p_commit => FND_API.G_FALSE,
2188 x_return_status => x_return_status ,
2189 x_msg_count => x_msg_count ,
2190 x_msg_data => x_msg_data ,
2191
2192 p_id => l_id
2193 );
2194 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2195 RAISE FND_API.G_EXC_ERROR ;
2196 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2198 END IF;
2199
2200 END IF;
2201
2202 ELSE
2203 -- we are not in the global org
2204
2205 IF (l_adopt_asis_count > 0) THEN
2206 -- we are adopting some article versions as is, so update Adoption Row
2207
2208 update_adp_status_type_blk(
2209 p_id => l_id,
2210 p_local_org_id => p_org_id,
2211 p_adoption_status => 'APPROVED',
2212 p_adoption_type => 'ADOPTED',
2213 p_type => 'ADOPTED',
2214 x_return_status => x_return_status
2215 );
2216 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2217 RAISE FND_API.G_EXC_ERROR ;
2218 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2219 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2220 END IF;
2221
2222 END IF;
2223
2224 IF (l_localized_count > 0) THEN
2225 -- if some articles are localized update adoption row
2226
2227 update_adp_status_type_blk(
2228 p_id => l_id,
2229 p_local_org_id => p_org_id,
2230 p_adoption_status => 'APPROVED',
2231 p_adoption_type => 'LOCALIZED',
2232 p_type => 'LOCALIZED',
2233 x_return_status => x_return_status
2234 );
2235 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2236 RAISE FND_API.G_EXC_ERROR ;
2237 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2238 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2239 END IF;
2240
2241 END IF;
2242
2243 END IF; -- of IF (p_org_id = G_GLOBAL_ORG_ID) THEN
2244
2245
2246 -- delete rows created in the temp table
2247 DELETE FROM OKC_ART_BLK_TEMP
2248 WHERE id = l_id;
2249
2250 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2251 IF(FND_API.to_boolean(p_commit)) THEN
2252 COMMIT;
2253 END IF;
2254
2255 IF (l_debug = 'Y') THEN
2256 okc_debug.log('200: Leaving approve_blk: Success');
2257 END IF;
2258
2259
2260 EXCEPTION
2261
2262 WHEN FND_API.G_EXC_ERROR THEN
2263 ROLLBACK TO approve_blk_PVT;
2264 x_return_status := FND_API.G_RET_STS_ERROR ;
2265
2266 IF (l_debug = 'Y') THEN
2267 okc_debug.log('201: Leaving approve_blk: Error');
2268 END IF;
2269 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2270
2271 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2272 ROLLBACK TO approve_blk_PVT;
2273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2274
2275 IF (l_debug = 'Y') THEN
2276 okc_debug.log('202: Leaving approve_blk: Unexpected Error');
2277 END IF;
2278 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2279
2280 WHEN OTHERS THEN
2281 ROLLBACK TO approve_blk_PVT;
2282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2283
2284 IF (l_debug = 'Y') THEN
2285 okc_debug.log('203: Leaving approve_blk: Unknown Error');
2286 END IF;
2287
2288 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR)
2289 THEN
2290 fnd_msg_pub.add_exc_msg(G_PKG_NAME ,l_api_name );
2291 END IF;
2292 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2293
2294 END approve_blk;
2295
2296 ---------------------------------------------------------------------
2297
2298 PROCEDURE reject_blk(
2299 p_api_version IN NUMBER ,
2300 p_init_msg_list IN VARCHAR2 ,
2301 p_commit IN VARCHAR2 ,
2302 x_return_status OUT NOCOPY VARCHAR2 ,
2303 x_msg_count OUT NOCOPY NUMBER ,
2304 x_msg_data OUT NOCOPY VARCHAR2 ,
2305
2306 p_org_id IN NUMBER ,
2307 p_art_ver_tbl IN num_tbl_type ,
2308 x_validation_results OUT NOCOPY validation_tbl_type )
2309 IS
2310
2311 l_api_version NUMBER := 1.0;
2312 l_api_name VARCHAR2(30) := 'reject_blk';
2313
2314 l_qa_return_status VARCHAR2(1);
2315 l_id NUMBER := -1;
2316
2317 l_adopt_asis_count NUMBER := 0;
2318 l_global_count NUMBER := 0;
2319 l_localized_count NUMBER := 0;
2320
2321 BEGIN
2322
2323 IF (l_debug = 'Y') THEN
2324 okc_debug.log('100: Entering reject_blk: p_api_version='||p_api_version||' p_init_msg_list='||p_init_msg_list||' p_commit='||p_commit);
2325
2326 okc_debug.log('101: In reject_blk: p_org_id='||p_org_id);
2327
2328 IF (p_art_ver_tbl IS NOT NULL) THEN
2329 okc_debug.log('102: p_art_ver_tbl.COUNT='||p_art_ver_tbl.COUNT);
2330 FOR i in p_art_ver_tbl.FIRST..p_art_ver_tbl.LAST LOOP
2331 okc_debug.log('103: p_art_ver_tbl['||i||']='||p_art_ver_tbl(i));
2332 END LOOP;
2333 END IF;
2334 END IF;
2335
2336 -- standard initialization code
2337 SAVEPOINT reject_blk_PVT;
2338 G_GLOBAL_ORG_ID := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
2339 G_USER_ID := FND_GLOBAL.USER_ID;
2340 G_LOGIN_ID := FND_GLOBAL.LOGIN_ID;
2341 l_debug := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
2342
2343 IF NOT fnd_api.compatible_api_call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2345 END IF;
2346 IF fnd_api.to_boolean( p_init_msg_list ) THEN
2347 fnd_msg_pub.initialize;
2348 END IF;
2349 x_return_status := FND_API.G_RET_STS_SUCCESS;
2350
2351 -- first populate the the temp table with all the relevant details
2352 get_version_details(
2353 p_org_id => p_org_id,
2354 p_art_ver_tbl => p_art_ver_tbl,
2355 x_return_status => x_return_status,
2356 x_id => l_id,
2357 x_adopt_asis_count => l_adopt_asis_count,
2358 x_global_count => l_global_count,
2359 x_localized_count => l_localized_count
2360 );
2361 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2362 RAISE FND_API.G_EXC_ERROR ;
2363 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2364 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2365 END IF;
2366
2367 -- check if all versions are in pending approval status first
2368 -- this is not part of normal qa, but a basic sanity check required
2369 -- for all status transitions
2370 status_check_blk(
2371 p_id => l_id,
2372 p_to_status => 'REJECTED',
2373 x_return_status => x_return_status,
2374 x_qa_return_status => l_qa_return_status,
2375 px_validation_results => x_validation_results
2376 );
2377 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2378 RAISE FND_API.G_EXC_ERROR ;
2379 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2381 ELSE
2382 -- status check failed
2383 IF (l_qa_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2384 x_return_status := FND_API.G_RET_STS_ERROR;
2385 RAISE FND_API.G_EXC_ERROR ;
2386 END IF;
2387 END IF;
2388
2389
2390 -- common for global/local/localized clauses, will do nothing for adopt as is clauses.
2391 update_art_version_status_blk(
2392 p_id => l_id,
2393 p_status => 'REJECTED',
2394 x_return_status => x_return_status
2395 );
2396 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2397 RAISE FND_API.G_EXC_ERROR ;
2398 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2400 END IF;
2401
2402
2403
2404 IF (p_org_id <> G_GLOBAL_ORG_ID) THEN
2405 -- we are not in the global org, article for adoption as is are being rejected
2406
2407 IF (l_adopt_asis_count > 0) THEN
2408 -- we are adopting some article versions as is.
2409 -- 1. Update Adoption Row
2410 -- 2. Delete relationship rows if the first version is being rejected
2411
2412 update_adp_status_type_blk(
2413 p_id => l_id,
2414 p_local_org_id => p_org_id,
2415 p_adoption_status => 'REJECTED',
2416 p_adoption_type => 'AVAILABLE',
2417 p_type => 'ADOPTED',
2418 x_return_status => x_return_status
2419 );
2420 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2421 RAISE FND_API.G_EXC_ERROR ;
2422 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2424 END IF;
2425
2426 delete_relationships_blk(
2427 p_id => l_id,
2428 p_org_id => p_org_id,
2429 x_return_status => x_return_status
2430 );
2431 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2432 RAISE FND_API.G_EXC_ERROR ;
2433 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2434 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2435 END IF;
2436
2437 END IF;
2438
2439 IF (l_localized_count > 0) THEN
2440 -- update adoption row if localized
2441
2442 update_adp_status_type_blk(
2443 p_id => l_id,
2444 p_local_org_id => p_org_id,
2445 p_adoption_status => 'REJECTED',
2446 p_adoption_type => 'LOCALIZED',
2447 p_type => 'LOCALIZED',
2448 x_return_status => x_return_status
2449 );
2450 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2451 RAISE FND_API.G_EXC_ERROR ;
2452 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2453 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2454 END IF;
2455
2456 END IF;
2457
2458 END IF; -- of IF (p_org_id <> G_GLOBAL_ORG_ID) THEN
2459
2460
2461 -- delete rows created in the temp table
2462 DELETE FROM OKC_ART_BLK_TEMP
2463 WHERE id = l_id;
2464
2465 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2466 IF(FND_API.to_boolean(p_commit)) THEN
2467 COMMIT;
2468 END IF;
2469
2470 IF (l_debug = 'Y') THEN
2471 okc_debug.log('200: Leaving reject_blk: Success');
2472 END IF;
2473
2474
2475 EXCEPTION
2476
2477 WHEN FND_API.G_EXC_ERROR THEN
2478 ROLLBACK TO reject_blk_PVT;
2479 x_return_status := FND_API.G_RET_STS_ERROR ;
2480
2481 IF (l_debug = 'Y') THEN
2482 okc_debug.log('201: Leaving reject_blk: Error');
2483 END IF;
2484
2485 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2486
2487 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2488 ROLLBACK TO reject_blk_PVT;
2489 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2490
2491 IF (l_debug = 'Y') THEN
2492 okc_debug.log('202: Leaving reject_blk: Unexpected Error');
2493 END IF;
2494
2495 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2496
2497 WHEN OTHERS THEN
2498 ROLLBACK TO reject_blk_PVT;
2499 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2500
2501 IF (l_debug = 'Y') THEN
2502 okc_debug.log('203: Leaving reject_blk: Unknown Error');
2503 END IF;
2504
2505 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR)
2506 THEN
2507 fnd_msg_pub.add_exc_msg(G_PKG_NAME ,l_api_name );
2508 END IF;
2509 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2510
2511 END reject_blk;
2512
2513 -----------------------------------------------------------------------------
2514
2515 END OKC_ART_BLK_PVT;
2516
2517