[Home] [Help]
PACKAGE BODY: APPS.OKC_ART_BLK_PVT
Source
1 PACKAGE BODY OKC_ART_BLK_PVT AS
2 /* $Header: OKCVARTBLKB.pls 120.1 2011/09/28 14:19:21 serukull ship $ */
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 nvl(BVB.MRV_FLAG,'N')='N' -- Exempt MRV from validation
560 AND BVT.variable_code = BVB.variable_code;
561
562 l_art_id_tbl num_tbl_type;
563 l_art_ver_id_tbl num_tbl_type;
564 l_art_title_tbl varchar_tbl_type;
565 l_var_name_tbl varchar_tbl_type;
566 l_val_set_tbl num_tbl_type;
567
568 l_found BOOLEAN := FALSE;
569 errnum INTEGER := 0;
570 initerrnum INTEGER := 0;
571
572 BEGIN
573
574 IF (l_debug = 'Y') THEN
575 okc_debug.log('100: Entering variable_check_blk: p_id='||p_id);
576 END IF;
577 x_return_status := FND_API.G_RET_STS_SUCCESS;
578 x_qa_return_status := FND_API.G_RET_STS_SUCCESS;
579
580 errnum := px_validation_results.COUNT;
581 initerrnum := errnum;
582
583 OPEN l_disabled_var_csr(p_id);
584 FETCH l_disabled_var_csr BULK COLLECT INTO l_art_id_tbl, l_art_ver_id_tbl, l_art_title_tbl,
585 l_var_name_tbl;
586 IF ( l_art_id_tbl.COUNT > 0 ) THEN
587 l_found := TRUE;
588 END IF;
589 CLOSE l_disabled_var_csr;
590
591 IF (l_found) THEN
592 FOR i IN l_art_id_tbl.FIRST..l_art_id_tbl.LAST LOOP
593 errnum := errnum + 1;
594 px_validation_results(errnum).article_id := l_art_id_tbl(i);
595 px_validation_results(errnum).article_version_id := l_art_ver_id_tbl(i);
596 px_validation_results(errnum).article_title := l_art_title_tbl(i);
597 px_validation_results(errnum).error_code := G_CHK_ART_INACTIVE_VARIABLE;
598 FND_MESSAGE.set_name(G_APP_NAME, G_MSG_ART_INACTIVE_VARIABLE);
599 FND_MESSAGE.set_token('ARTICLE_TITLE', l_art_title_tbl(i));
600 FND_MESSAGE.set_token('VARIABLE_NAME', l_var_name_tbl(i));
601 px_validation_results(errnum).error_message := fnd_message.get;
602 END LOOP;
603 END IF;
604 l_found := FALSE;
605
606 OPEN l_invalid_valueset_csr(p_id);
607 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;
608 IF ( l_art_id_tbl.COUNT > 0 ) THEN
609 l_found := TRUE;
610 END IF;
611 CLOSE l_invalid_valueset_csr;
612
613 IF (l_found) THEN
614 FOR i IN l_art_id_tbl.FIRST..l_art_id_tbl.LAST LOOP
615
616 IF (l_val_set_tbl(i) = -99) THEN
617 errnum := errnum + 1;
618 px_validation_results(errnum).article_id := l_art_id_tbl(i);
619 px_validation_results(errnum).article_version_id := l_art_ver_id_tbl(i);
620 px_validation_results(errnum).article_title := l_art_title_tbl(i);
621 px_validation_results(errnum).error_code := G_CHK_ART_INVALID_VALUESET;
622 FND_MESSAGE.set_name(G_APP_NAME, G_MSG_ART_INVALID_VALUESET);
623 FND_MESSAGE.set_token('ARTICLE_TITLE', l_art_title_tbl(i));
624 FND_MESSAGE.set_token('VARIABLE_NAME', l_var_name_tbl(i));
625 px_validation_results(errnum).error_message := fnd_message.get;
626 END IF;
627
628 END LOOP;
629 END IF;
630
631 l_art_id_tbl.DELETE;
632 l_art_ver_id_tbl.DELETE;
633 l_art_title_tbl.DELETE;
634 l_var_name_tbl.DELETE;
635 l_val_set_tbl.DELETE;
636
637 IF (errnum > initerrnum) THEN
638 x_qa_return_status := FND_API.G_RET_STS_ERROR;
639 END IF;
640
641 IF (l_debug = 'Y') THEN
642 okc_debug.log('200: Leaving variable_check_blk: Success, x_qa_return_status='||x_qa_return_status);
643 END IF;
644
645 EXCEPTION
646
647 WHEN OTHERS THEN
648
649 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
650 IF (l_debug = 'Y') THEN
651 okc_debug.log('202: Leaving variable_check_blk: Unknown Error');
652 END IF;
653 IF (l_disabled_var_csr%ISOPEN) THEN
654 CLOSE l_disabled_var_csr;
655 END IF;
656 IF (l_invalid_valueset_csr%ISOPEN) THEN
657 CLOSE l_invalid_valueset_csr;
658 END IF;
659
660 IF FND_MSG_PUB.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
661 FND_MSG_PUB.add_exc_msg(G_PKG_NAME ,l_api_name );
662 END IF;
663
664 END variable_check_blk;
665
666 -----------------------------------------------------------------------------
667
668 PROCEDURE section_type_check_blk(
669 p_id IN NUMBER,
670 x_return_status OUT NOCOPY VARCHAR2 ,
671 x_qa_return_status OUT NOCOPY VARCHAR2 ,
672 px_validation_results IN OUT NOCOPY validation_tbl_type )
673 IS
674 l_api_name CONSTANT VARCHAR2(30) := 'section_type_check_blk';
675
676 -- check for article_type
677 CURSOR l_art_typ_csr (cp_id IN NUMBER, cp_date IN DATE) IS
678 SELECT TMP.article_id, TMP.article_version_id, nvl(TMP.display_name, TMP.article_title),
679 TMP.article_type
680 FROM OKC_ART_BLK_TEMP TMP
681 WHERE TMP.id = cp_id
682 AND TMP.adopt_asis_yn = 'N' --check not done for adopt as is clauses
683 AND NOT EXISTS (
684 SELECT '1' from FND_LOOKUPS F
685 WHERE F.lookup_type = 'OKC_SUBJECT'
686 AND F.lookup_code = TMP.article_type
687 AND trunc(cp_date) BETWEEN trunc(nvl(F.start_date_active, cp_date)) AND
688 nvl(F.end_date_active, cp_date));
689
690 -- check for default_section
691 CURSOR l_def_sec_csr (cp_id IN NUMBER, cp_date IN DATE) IS
692 SELECT TMP.article_id, TMP.article_version_id, nvl(TMP.display_name, TMP.article_title),
693 TMP.default_section
694 FROM OKC_ART_BLK_TEMP TMP
695 WHERE TMP.id = cp_id
696 AND TMP.adopt_asis_yn = 'N' --check not done for adopt as is clauses
697 AND TMP.default_section <> 'UNASSIGNED'
698 AND NOT EXISTS (
699 SELECT '1' from FND_LOOKUPS F
700 WHERE F.lookup_type = 'OKC_ARTICLE_SECTION'
701 AND F.lookup_code = TMP.default_section
702 AND trunc(cp_date) BETWEEN trunc(nvl(F.start_date_active, cp_date)) AND
703 nvl(F.end_date_active, cp_date));
704
705
706 l_art_id_tbl num_tbl_type;
707 l_art_ver_id_tbl num_tbl_type;
708 l_art_title_tbl varchar_tbl_type;
709 l_art_typ_tbl varchar_tbl_type;
710 l_def_sec_tbl varchar_tbl_type;
711
712 l_found BOOLEAN := FALSE;
713 errnum INTEGER := 0;
714 initerrnum INTEGER := 0;
715 l_date DATE := sysdate;
716
717 BEGIN
718
719 IF (l_debug = 'Y') THEN
720 okc_debug.log('100: Entering section_type_check_blk: p_id='||p_id);
721 END IF;
722 x_return_status := FND_API.G_RET_STS_SUCCESS;
723 x_qa_return_status := FND_API.G_RET_STS_SUCCESS;
724
725 errnum := px_validation_results.COUNT;
726 initerrnum := errnum;
727
728 OPEN l_art_typ_csr(p_id, l_date);
729 FETCH l_art_typ_csr BULK COLLECT INTO l_art_id_tbl, l_art_ver_id_tbl, l_art_title_tbl,
730 l_art_typ_tbl;
731 IF ( l_art_id_tbl.COUNT > 0 ) THEN
732 l_found := TRUE;
733 END IF;
734 CLOSE l_art_typ_csr;
735
736 IF (l_found) THEN
737 FOR i IN l_art_id_tbl.FIRST..l_art_id_tbl.LAST LOOP
738 errnum := errnum + 1;
739 px_validation_results(errnum).article_id := l_art_id_tbl(i);
740 px_validation_results(errnum).article_version_id := l_art_ver_id_tbl(i);
741 px_validation_results(errnum).article_title := l_art_title_tbl(i);
742 px_validation_results(errnum).error_code := G_CHK_ART_INVALID_TYPE;
743 FND_MESSAGE.set_name(G_APP_NAME, G_MSG_ART_INVALID_TYPE);
744 FND_MESSAGE.set_token('ARTICLE_TITLE', l_art_title_tbl(i));
745 FND_MESSAGE.set_token('ARTICLE_TYPE', l_art_typ_tbl(i));
746 px_validation_results(errnum).error_message := fnd_message.get;
747 END LOOP;
748 END IF;
749 l_found := FALSE;
750
751 OPEN l_def_sec_csr(p_id, l_date);
752 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;
753 IF ( l_art_id_tbl.COUNT > 0 ) THEN
754 l_found := TRUE;
755 END IF;
756 CLOSE l_def_sec_csr;
757
758 IF (l_found) THEN
759 FOR i IN l_art_id_tbl.FIRST..l_art_id_tbl.LAST LOOP
760 errnum := errnum + 1;
761 px_validation_results(errnum).article_id := l_art_id_tbl(i);
762 px_validation_results(errnum).article_version_id := l_art_ver_id_tbl(i);
763 px_validation_results(errnum).article_title := l_art_title_tbl(i);
764 px_validation_results(errnum).error_code := G_CHK_ART_INVALID_SECTION;
765 FND_MESSAGE.set_name(G_APP_NAME, G_MSG_ART_INVALID_SECTION);
766 FND_MESSAGE.set_token('ARTICLE_TITLE', l_art_title_tbl(i));
767 FND_MESSAGE.set_token('DEFAULT_SECTION', l_def_sec_tbl(i));
768 px_validation_results(errnum).error_message := fnd_message.get;
769 END LOOP;
770 END IF;
771
772 l_art_id_tbl.DELETE;
773 l_art_ver_id_tbl.DELETE;
774 l_art_title_tbl.DELETE;
775 l_art_typ_tbl.DELETE;
776 l_def_sec_tbl.DELETE;
777
778 IF (errnum > initerrnum) THEN
779 x_qa_return_status := FND_API.G_RET_STS_ERROR;
780 END IF;
781
782 IF (l_debug = 'Y') THEN
783 okc_debug.log('200: Leaving section_type_check_blk: Success, x_qa_return_status='||x_qa_return_status);
784 END IF;
785
786 EXCEPTION
787
788 WHEN OTHERS THEN
789
790 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
791 IF (l_debug = 'Y') THEN
792 okc_debug.log('201: Leaving section_type_check_blk: Unknown Error');
793 END IF;
794 IF (l_art_typ_csr%ISOPEN) THEN
795 CLOSE l_art_typ_csr;
796 END IF;
797 IF (l_def_sec_csr%ISOPEN) THEN
798 CLOSE l_def_sec_csr;
799 END IF;
800
801 IF FND_MSG_PUB.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
802 FND_MSG_PUB.add_exc_msg(G_PKG_NAME ,l_api_name );
803 END IF;
804
805 END section_type_check_blk;
806
807 -----------------------------------------------------------------------------
808
809 PROCEDURE update_art_version_status_blk(
810 p_id IN NUMBER ,
811 p_status IN VARCHAR2 ,
812 x_return_status OUT NOCOPY VARCHAR2 )
813 IS
814
815 l_api_name CONSTANT VARCHAR2(30) := 'update_art_version_status_blk';
816 l_date DATE := sysdate;
817
818 BEGIN
819
820 IF (l_debug = 'Y') THEN
821 okc_debug.log('100: Entering update_art_version_status_blk: p_id='||p_id||' p_status='||p_status);
822 END IF;
823
824 x_return_status := FND_API.G_RET_STS_SUCCESS;
825
826 IF (p_status = 'APPROVED') THEN
827
828 UPDATE OKC_ARTICLE_VERSIONS
829 SET
830 ARTICLE_STATUS = p_status,
831 -- date approved must also be updated
832 DATE_APPROVED = l_date,
833 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
834 LAST_UPDATED_BY = G_USER_ID,
835 LAST_UPDATE_LOGIN = G_LOGIN_ID,
836 LAST_UPDATE_DATE = l_date
837 WHERE
838 ARTICLE_VERSION_ID IN
839 (SELECT article_version_id FROM OKC_ART_BLK_TEMP
840 WHERE id = p_id AND adopt_asis_yn = 'N');
841 ELSE
842
843 UPDATE OKC_ARTICLE_VERSIONS
844 SET
845 ARTICLE_STATUS = p_status,
846 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
847 LAST_UPDATED_BY = G_USER_ID,
848 LAST_UPDATE_LOGIN = G_LOGIN_ID,
849 LAST_UPDATE_DATE = l_date
850 WHERE
851 ARTICLE_VERSION_ID IN
852 (SELECT article_version_id FROM OKC_ART_BLK_TEMP
853 WHERE id = p_id AND adopt_asis_yn = 'N');
854
855 END IF;
856
857 IF (l_debug = 'Y') THEN
858 okc_debug.log('200: Leaving update_art_version_status_blk: Success');
859 END IF;
860
861 EXCEPTION
862
863 WHEN OTHERS THEN
864 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
865 IF (l_debug = 'Y') THEN
866 okc_debug.log('201: Leaving update_art_version_status_blk: Unknown Error');
867 END IF;
868
869 IF FND_MSG_PUB.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
870 FND_MSG_PUB.add_exc_msg(G_PKG_NAME ,l_api_name );
871 END IF;
872
873 END update_art_version_status_blk;
874
875 -----------------------------------------------------------------------------
876
877 PROCEDURE update_adp_status_type_blk(
878 p_id IN NUMBER ,
879 p_local_org_id IN NUMBER,
880 p_adoption_status IN VARCHAR2 ,
881 p_adoption_type IN VARCHAR2 ,
882 p_type IN VARCHAR2,
883 x_return_status OUT NOCOPY VARCHAR2 )
884 IS
885
886 l_api_name CONSTANT VARCHAR2(30) := 'update_adp_status_type_blk';
887 l_date DATE := sysdate;
888
889 BEGIN
890
891 IF (l_debug = 'Y') THEN
892 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);
893 END IF;
894
895 x_return_status := FND_API.G_RET_STS_SUCCESS;
896
897 IF (p_type = 'ADOPTED') THEN
898
899 UPDATE OKC_ARTICLE_ADOPTIONS
900 SET
901 ADOPTION_TYPE = nvl(p_adoption_type, ADOPTION_TYPE),
902 ADOPTION_STATUS = nvl(p_adoption_status, ADOPTION_STATUS),
903 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
904 LAST_UPDATED_BY = G_USER_ID,
905 LAST_UPDATE_LOGIN = G_LOGIN_ID,
906 LAST_UPDATE_DATE = l_date
907 WHERE
908 GLOBAL_ARTICLE_VERSION_ID IN
909 (SELECT article_version_id FROM OKC_ART_BLK_TEMP
910 WHERE id = p_id AND adopt_asis_yn = 'Y')
911 AND LOCAL_ORG_ID = p_local_org_id;
912
913 ELSIF (p_type = 'LOCALIZED') THEN
914
915 UPDATE OKC_ARTICLE_ADOPTIONS
916 SET
917 ADOPTION_TYPE = nvl(p_adoption_type, ADOPTION_TYPE),
918 ADOPTION_STATUS = nvl(p_adoption_status, ADOPTION_STATUS),
919 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
920 LAST_UPDATED_BY = G_USER_ID,
921 LAST_UPDATE_LOGIN = G_LOGIN_ID,
922 LAST_UPDATE_DATE = l_date
923 WHERE
924 LOCAL_ARTICLE_VERSION_ID IN
925 (SELECT article_version_id FROM OKC_ART_BLK_TEMP
926 WHERE id = p_id AND adopt_asis_yn = 'N' AND localized_yn = 'Y')
927 AND LOCAL_ORG_ID = p_local_org_id;
928
929 ELSE
930 -- p_type not recognized
931 x_return_status := FND_API.G_RET_STS_ERROR;
932 FND_MESSAGE.set_name(G_APP_NAME, G_OKC_MSG_INVALID_ARGUMENT);
933 FND_MESSAGE.set_token('ARG_NAME', 'p_type');
934 FND_MESSAGE.set_token('ARG_VALUE', p_type);
935 FND_MSG_PUB.add;
936 RAISE FND_API.G_EXC_ERROR;
937
938 END IF;
939
940 IF (l_debug = 'Y') THEN
941 okc_debug.log('200: Leaving update_adp_status_type_blk: Success');
942 END IF;
943
944 EXCEPTION
945
946 WHEN FND_API.G_EXC_ERROR THEN
947 x_return_status := FND_API.G_RET_STS_ERROR;
948 IF (l_debug = 'Y') THEN
949 okc_debug.log('201: Leaving update_adp_status_type_blk: Error');
950 END IF;
951
952 WHEN OTHERS THEN
953 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
954 IF (l_debug = 'Y') THEN
955 okc_debug.log('202: Leaving update_adp_status_type_blk: Unknown Error');
956 END IF;
957
958 IF FND_MSG_PUB.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
959 FND_MSG_PUB.add_exc_msg(G_PKG_NAME ,l_api_name );
960 END IF;
961
962 END update_adp_status_type_blk;
963
964 -----------------------------------------------------------------------------
965
966 PROCEDURE update_prev_vers_enddate_blk(
967 p_id IN NUMBER ,
968 x_return_status OUT NOCOPY VARCHAR2 )
969
970 IS
971 l_api_name CONSTANT VARCHAR2(30) := 'update_prev_vers_enddate_blk';
972
973 --determine all previous versions that have a null end date
974 CURSOR l_prev_ver_csr(cp_id IN NUMBER) IS
975 SELECT PREV.article_version_id, TMP.start_date
976 FROM OKC_ART_BLK_TEMP TMP,
977 OKC_ARTICLE_VERSIONS PREV
978 WHERE TMP.id = cp_id AND
979 TMP.adopt_asis_yn = 'N' AND
980 PREV.article_id = TMP.article_id AND
981 PREV.article_version_id <> TMP.article_version_id AND
982 PREV.start_date = (SELECT max(VER.start_date)
983 FROM OKC_ARTICLE_VERSIONS VER
984 WHERE VER.article_id = TMP.article_id AND
985 VER.article_version_id <> TMP.article_version_id)
986 AND PREV.end_date IS NULL;
987
988 l_prev_ver_id_tbl num_tbl_type;
989 l_start_date_tbl date_tbl_type;
990 l_found BOOLEAN := FALSE;
991 l_date DATE := sysdate;
992 l_one_sec NUMBER := 1/86400; -- expressed as a part of a day, 24*60*60
993
994 BEGIN
995
996 IF (l_debug = 'Y') THEN
997 okc_debug.log('100: Entering update_prev_vers_enddate_blk: p_id='||p_id);
998 END IF;
999
1000 x_return_status := FND_API.G_RET_STS_SUCCESS;
1001
1002 OPEN l_prev_ver_csr(p_id);
1003 FETCH l_prev_ver_csr BULK COLLECT INTO l_prev_ver_id_tbl, l_start_date_tbl;
1004
1005 IF (l_prev_ver_id_tbl.COUNT > 0) THEN
1006 l_found := TRUE;
1007 END IF;
1008 CLOSE l_prev_ver_csr;
1009
1010 IF l_found THEN
1011 FORALL i IN l_prev_ver_id_tbl.FIRST..l_prev_ver_id_tbl.LAST
1012 UPDATE OKC_ARTICLE_VERSIONS
1013 SET
1014 END_DATE = l_start_date_tbl(i) - l_one_sec,
1015 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
1016 LAST_UPDATED_BY = G_USER_ID,
1017 LAST_UPDATE_LOGIN = G_LOGIN_ID,
1018 LAST_UPDATE_DATE = l_date
1019 WHERE
1020 ARTICLE_VERSION_ID = l_prev_ver_id_tbl(i);
1021 END IF;
1022
1023 l_prev_ver_id_tbl.DELETE;
1024 l_start_date_tbl.DELETE;
1025
1026 IF (l_debug = 'Y') THEN
1027 okc_debug.log('200: Leaving update_prev_vers_enddate_blk: Success');
1028 END IF;
1029
1030 EXCEPTION
1031
1032 WHEN OTHERS THEN
1033 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1034 IF (l_prev_ver_csr%ISOPEN) THEN
1035 CLOSE l_prev_ver_csr;
1036 END IF;
1037 IF (l_debug = 'Y') THEN
1038 okc_debug.log('201: Leaving update_prev_vers_enddate_blk: Unknown Error');
1039 END IF;
1040
1041 IF FND_MSG_PUB.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
1042 FND_MSG_PUB.add_exc_msg(G_PKG_NAME ,l_api_name );
1043 END IF;
1044
1045 END update_prev_vers_enddate_blk;
1046
1047 ----------------------------------------------------------------------------
1048
1049 --
1050 -- p_id OKC_ART_BLK_TEMP table is populated with a number of rows containing
1051 -- global article version ids, global article ids and the corresponding
1052 -- local org ids for which the relationships need to be adopted.
1053 -- p_id identifies these rows.
1054 --
1055 -- relationship will be adopted
1056 -- 1. from the relation ship table,
1057 -- if src_article_id = input article_id, the target must be ADOPTED for the local org
1058 -- if target_article_id = input article_id, the src must be ADOPTED for the local org
1059 -- 2. the relationship has not previously been adopted
1060 --
1061 PROCEDURE adopt_relationships_blk(
1062 p_id IN NUMBER,
1063 x_return_status OUT NOCOPY VARCHAR2 )
1064 IS
1065
1066 l_api_name CONSTANT VARCHAR2(30) := 'adopt_relationships_blk';
1067 l_date DATE := sysdate;
1068
1069 BEGIN
1070
1071 IF (l_debug = 'Y') THEN
1072 okc_debug.log('100: Entering adopt_relationships_blk: p_id='||p_id);
1073 END IF;
1074 x_return_status := FND_API.G_RET_STS_SUCCESS;
1075
1076 INSERT INTO OKC_ARTICLE_RELATNS_ALL
1077 (
1078 SOURCE_ARTICLE_ID,
1079 TARGET_ARTICLE_ID,
1080 ORG_ID,
1081 RELATIONSHIP_TYPE,
1082 OBJECT_VERSION_NUMBER,
1083 CREATED_BY,
1084 CREATION_DATE,
1085 LAST_UPDATED_BY,
1086 LAST_UPDATE_LOGIN,
1087 LAST_UPDATE_DATE
1088 )
1089 SELECT REL.source_article_id,
1090 REL.target_article_id,
1091 TMP.org_id,
1092 REL.relationship_type,
1093 1.0,
1094 G_USER_ID,
1095 l_date,
1096 G_USER_ID,
1097 G_LOGIN_ID,
1098 l_date
1099 FROM OKC_ART_BLK_TEMP TMP, OKC_ARTICLE_RELATNS_ALL REL
1100 WHERE TMP.id = p_id AND
1101 REL.org_id = G_GLOBAL_ORG_ID AND
1102 REL.source_article_id = TMP.article_id AND
1103 EXISTS
1104 (SELECT 1 FROM OKC_ARTICLE_VERSIONS AV1, OKC_ARTICLE_ADOPTIONS ADP
1105 WHERE AV1.article_id = REL.target_article_id AND
1106 ADP.global_article_version_id = AV1.article_version_id AND
1107 ADP.local_org_id = TMP.org_id AND
1108 ADP.adoption_type = 'ADOPTED')
1109 AND NOT EXISTS
1110 (SELECT 1 FROM OKC_ARTICLE_RELATNS_ALL ARL1
1111 WHERE REL.source_article_id = ARL1.source_article_id AND
1112 REL.target_article_id = ARL1.target_article_id AND
1113 REL.relationship_type = ARL1.relationship_type AND
1114 ARL1.org_id = TMP.org_id);
1115
1116 INSERT INTO OKC_ARTICLE_RELATNS_ALL
1117 (
1118 SOURCE_ARTICLE_ID,
1119 TARGET_ARTICLE_ID,
1120 ORG_ID,
1121 RELATIONSHIP_TYPE,
1122 OBJECT_VERSION_NUMBER,
1123 CREATED_BY,
1124 CREATION_DATE,
1125 LAST_UPDATED_BY,
1126 LAST_UPDATE_LOGIN,
1127 LAST_UPDATE_DATE
1128 )
1129 SELECT REL.source_article_id,
1130 REL.target_article_id,
1131 TMP.org_id,
1132 REL.relationship_type,
1133 1.0,
1134 G_USER_ID,
1135 l_date,
1136 G_USER_ID,
1137 G_LOGIN_ID,
1138 l_date
1139 FROM OKC_ART_BLK_TEMP TMP, OKC_ARTICLE_RELATNS_ALL REL
1140 WHERE TMP.id = p_id AND
1141 REL.org_id = G_GLOBAL_ORG_ID AND
1142 REL.target_article_id = TMP.article_id AND
1143 EXISTS
1144 (SELECT 1 FROM OKC_ARTICLE_VERSIONS AV1, OKC_ARTICLE_ADOPTIONS ADP
1145 WHERE AV1.article_id = REL.source_article_id AND
1146 ADP.global_article_version_id = AV1.article_version_id AND
1147 ADP.local_org_id = TMP.org_id AND
1148 ADP.adoption_type = 'ADOPTED')
1149 AND NOT EXISTS
1150 (SELECT 1 FROM OKC_ARTICLE_RELATNS_ALL ARL1
1151 WHERE REL.source_article_id = ARL1.source_article_id AND
1152 REL.target_article_id = ARL1.target_article_id AND
1153 REL.relationship_type = ARL1.relationship_type AND
1154 ARL1.org_id = TMP.org_id);
1155
1156
1157 IF (l_debug = 'Y') THEN
1158 okc_debug.log('200: Leaving adopt_relationships_blk: Success');
1159 END IF;
1160
1161 EXCEPTION
1162
1163 WHEN OTHERS THEN
1164 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1165
1166 IF (l_debug = 'Y') THEN
1167 okc_debug.log('201: Leaving adopt_relationships_blk: Unknown Error');
1168 END IF;
1169
1170 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR)
1171 THEN
1172 fnd_msg_pub.add_exc_msg(G_PKG_NAME ,l_api_name );
1173 END IF;
1174 END adopt_relationships_blk;
1175
1176 -----------------------------------------------------------------------------
1177
1178 PROCEDURE delete_relationships_blk(
1179 p_id IN NUMBER ,
1180 p_org_id IN NUMBER,
1181 x_return_status OUT NOCOPY VARCHAR2 )
1182 IS
1183
1184 l_api_name CONSTANT VARCHAR2(30) := 'delete_relationships_blk';
1185
1186 l_art_id_tbl num_tbl_type;
1187 l_art_ver_id_tbl num_tbl_type;
1188 l_found BOOLEAN := FALSE;
1189
1190 -- select only those article versions that are being adopted as is
1191 CURSOR l_art_id_csr(cp_id IN NUMBER) IS
1192 SELECT article_id, article_version_id
1193 FROM OKC_ART_BLK_TEMP TMP
1194 WHERE TMP.id = cp_id AND TMP.adopt_asis_yn = 'Y';
1195
1196 BEGIN
1197
1198 IF (l_debug = 'Y') THEN
1199 okc_debug.log('100: Entering delete_relationships_blk: p_id='||p_id||' p_org_id='||p_org_id);
1200 END IF;
1201
1202 x_return_status := FND_API.G_RET_STS_SUCCESS;
1203
1204 -- get the article id's first, makes the NOT EXISTS clause in Delete below, less costly
1205 OPEN l_art_id_csr(p_id);
1206 FETCH l_art_id_csr BULK COLLECT INTO l_art_id_tbl,l_art_ver_id_tbl;
1207
1208 IF (l_art_id_tbl.COUNT > 0) THEN
1209 l_found := TRUE;
1210 END IF;
1211
1212 CLOSE l_art_id_csr;
1213
1214
1215 IF (NOT l_found) THEN
1216 RETURN;
1217 END IF;
1218
1219 -- delete where source article has not been adopted
1220 FORALL i IN l_art_id_tbl.FIRST..l_art_id_tbl.LAST
1221 DELETE FROM OKC_ARTICLE_RELATNS_ALL
1222 WHERE org_id = p_org_id
1223 AND source_article_id = l_art_id_tbl(i)
1224 AND NOT EXISTS
1225 ( SELECT 1 FROM OKC_ARTICLE_ADOPTIONS ADP, OKC_ARTICLE_VERSIONS AV1
1226 WHERE AV1.article_id = l_art_id_tbl(i)
1227 AND AV1.article_version_id <> l_art_ver_id_tbl(i)
1228 AND ADP.adoption_type = 'ADOPTED'
1229 AND ADP.global_article_version_id = AV1.article_version_id
1230 AND ADP.local_org_id = p_org_id
1231 );
1232
1233 -- delete where target article has not been adopted
1234 FORALL i IN l_art_id_tbl.FIRST..l_art_id_tbl.LAST
1235 DELETE FROM OKC_ARTICLE_RELATNS_ALL
1236 WHERE org_id = p_org_id
1237 AND target_article_id = l_art_id_tbl(i)
1238 AND NOT EXISTS
1239 ( SELECT 1 FROM OKC_ARTICLE_ADOPTIONS ADP, OKC_ARTICLE_VERSIONS AV2
1240 WHERE AV2.article_id = l_art_id_tbl(i)
1241 AND AV2.article_version_id <> l_art_ver_id_tbl(i)
1242 AND ADP.adoption_type = 'ADOPTED'
1243 AND ADP.global_article_version_id = AV2.article_version_id
1244 AND ADP.local_org_id = p_org_id
1245 );
1246
1247 l_art_id_tbl.DELETE;
1248 l_art_ver_id_tbl.DELETE;
1249
1250 IF (l_debug = 'Y') THEN
1251 okc_debug.log('200: Leaving delete_relationships_blk: Success');
1252 END IF;
1253
1254 EXCEPTION
1255
1256 WHEN OTHERS THEN
1257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1258 IF (l_art_id_csr%ISOPEN) THEN
1259 CLOSE l_art_id_csr;
1260 END IF;
1261
1262 IF (l_debug = 'Y') THEN
1263 okc_debug.log('201: Leaving delete_relationships_blk: Unknown error');
1264 END IF;
1265
1266 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
1267 fnd_msg_pub.add_exc_msg(G_PKG_NAME ,l_api_name );
1268 END IF;
1269
1270 END delete_relationships_blk;
1271
1272 ---------------------------------------------------------------------
1273
1274 PROCEDURE validate_article_versions_blk(
1275 p_api_version IN NUMBER ,
1276 p_init_msg_list IN VARCHAR2 ,
1277 p_commit IN VARCHAR2 ,
1278 p_validation_level IN NUMBER ,
1279 x_return_status OUT NOCOPY VARCHAR2 ,
1280 x_msg_count OUT NOCOPY NUMBER ,
1281 x_msg_data OUT NOCOPY VARCHAR2 ,
1282
1283 p_org_id IN NUMBER ,
1284 p_art_ver_tbl IN num_tbl_type ,
1285 p_id IN NUMBER,
1286 x_qa_return_status OUT NOCOPY VARCHAR2,
1287 x_validation_results OUT NOCOPY validation_tbl_type )
1288
1289 IS
1290
1291 l_api_version NUMBER := 1.0;
1292 l_api_name VARCHAR2(30) := 'validate_article_versions_blk';
1293
1294 l_id NUMBER;
1295 l_return_status VARCHAR2(1);
1296 l_qa_return_status VARCHAR2(1);
1297
1298
1299 l_adopt_asis_art_id_tbl num_tbl_type;
1300 l_adopt_asis_art_ver_id_tbl num_tbl_type;
1301 l_adopt_asis_art_title_tbl varchar_tbl_type;
1302
1303 l_curr_msg_count NUMBER;
1304 l_local_result_status VARCHAR2(1);
1305 l_msg_count NUMBER;
1306 l_msg_data VARCHAR2(2000);
1307 l_earlier_local_version_id VARCHAR2(250);
1308
1309 l_err_num NUMBER;
1310 i NUMBER;
1311
1312 l_adopt_asis_count NUMBER := 0;
1313 l_global_count NUMBER := 0;
1314 l_localized_count NUMBER := 0;
1315
1316 -- get some details about adopt as is articles for filling the x_validation_results table
1317 CURSOR l_adopt_as_is_csr(cp_id IN NUMBER) IS
1318 SELECT TMP.article_id, TMP.article_version_id, nvl(TMP.display_name, TMP.article_title)
1319 FROM OKC_ART_BLK_TEMP TMP
1320 WHERE TMP.id = cp_id AND
1321 TMP.adopt_asis_yn = 'Y';
1322
1323
1324 BEGIN
1325
1326 IF (l_debug = 'Y') THEN
1327 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);
1328
1329 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);
1330
1331 IF (p_art_ver_tbl IS NOT NULL) THEN
1332 okc_debug.log('102: p_art_ver_tbl.COUNT='||p_art_ver_tbl.COUNT);
1333 FOR i in p_art_ver_tbl.FIRST..p_art_ver_tbl.LAST LOOP
1334 okc_debug.log('103: p_art_ver_tbl['||i||']='||p_art_ver_tbl(i));
1335 END LOOP;
1336 END IF;
1337 END IF;
1338
1339 -- standard initialization code
1340 SAVEPOINT val_article_versions_blk_PVT;
1341 G_GLOBAL_ORG_ID := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
1342 l_debug := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
1343
1344 IF NOT fnd_api.compatible_api_call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1345 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1346 END IF;
1347 IF fnd_api.to_boolean( p_init_msg_list ) THEN
1348 fnd_msg_pub.initialize;
1349 END IF;
1350 x_return_status := FND_API.G_RET_STS_SUCCESS;
1351 x_qa_return_status := FND_API.G_RET_STS_SUCCESS;
1352
1353 l_return_status := FND_API.G_RET_STS_SUCCESS;
1354 l_qa_return_status := FND_API.G_RET_STS_SUCCESS;
1355
1356 IF (p_validation_level <> FND_API.G_VALID_LEVEL_FULL) THEN
1357 RETURN;
1358 END IF;
1359
1360 IF (p_id IS NULL) THEN
1361 -- get the versions details only if p_id is not set
1362 get_version_details(
1363 p_org_id => p_org_id,
1364 p_art_ver_tbl => p_art_ver_tbl,
1365 x_return_status => x_return_status,
1366 x_id => l_id,
1367 x_adopt_asis_count => l_adopt_asis_count,
1368 x_global_count => l_global_count,
1369 x_localized_count => l_localized_count
1370 );
1371 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1372 RAISE FND_API.G_EXC_ERROR ;
1373 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1374 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1375 END IF;
1376 ELSE
1377 l_id := p_id;
1378 END IF;
1379
1380 variable_check_blk(
1381 p_id => l_id,
1382 x_return_status => x_return_status,
1383 x_qa_return_status => x_qa_return_status,
1384 px_validation_results => x_validation_results
1385 );
1386 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1387 RAISE FND_API.G_EXC_ERROR ;
1388 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1389 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1390 END IF;
1391
1392 section_type_check_blk(
1393 p_id => l_id,
1394 x_return_status => x_return_status,
1395 x_qa_return_status => x_qa_return_status,
1396 px_validation_results => x_validation_results
1397 );
1398 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1399 RAISE FND_API.G_EXC_ERROR ;
1400 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1401 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1402 END IF;
1403
1404 IF(p_org_id <> G_GLOBAL_ORG_ID) THEN
1405
1406 l_adopt_asis_art_id_tbl := num_tbl_type();
1407 l_adopt_asis_art_ver_id_tbl := num_tbl_type();
1408 --l_adopt_asis_art_title_tbl := varchar_tbl_type();
1409
1410 -- check adopt as is articles using existing code
1411 OPEN l_adopt_as_is_csr(l_id);
1412 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;
1413 CLOSE l_adopt_as_is_csr;
1414
1415 IF (l_adopt_asis_art_id_tbl.COUNT > 0) THEN
1416
1417 -- check adopt_as_is articles for adoption details
1418 i := l_adopt_asis_art_id_tbl.FIRST;
1419
1420 WHILE i is NOT NULL LOOP
1421
1422 -- reset all loop variables
1423 -- get the current message count, only new messages should be fetched and
1424 -- put in the x_validation_results table;
1425
1426 l_curr_msg_count := fnd_msg_pub.count_msg;
1427 l_local_result_status := FND_API.G_RET_STS_SUCCESS;
1428 l_msg_count := 0;
1429 l_msg_data := 0;
1430 l_earlier_local_version_id := 0;
1431
1432 IF (l_debug = 'Y') THEN
1433 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);
1434 END IF;
1435
1436 OKC_ADOPTIONS_GRP.check_adoption_details(
1437 p_api_version => 1.0,
1438 p_init_msg_list => FND_API.G_FALSE,
1439 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1440 x_return_status => l_local_result_status ,
1441 x_msg_count => l_msg_count,
1442 x_msg_data => l_msg_data,
1443 x_earlier_local_version_id => l_earlier_local_version_id,
1444 p_global_article_version_id => l_adopt_asis_art_ver_id_tbl(i),
1445 p_adoption_type => 'ADOPTED',
1446 p_local_org_id => p_org_id
1447 );
1448
1449 IF (l_debug = 'Y') THEN
1450 okc_debug.log('105: After OKC_ADOPTIONS_GRP.check_adoption_details x_return_status='||l_local_result_status);
1451 END IF;
1452
1453 IF (l_local_result_status = FND_API.G_RET_STS_ERROR) THEN -- the check failed
1454 x_qa_return_status := FND_API.G_RET_STS_ERROR;
1455
1456 FOR j in 1..(l_msg_count - l_curr_msg_count) LOOP
1457 l_err_num := x_validation_results.COUNT +1;
1458 x_validation_results(l_err_num).article_id := l_adopt_asis_art_id_tbl(i);
1459 x_validation_results(l_err_num).article_version_id := l_adopt_asis_art_ver_id_tbl(i);
1460 x_validation_results(l_err_num).article_title := l_adopt_asis_art_title_tbl(i);
1461 x_validation_results(l_err_num).error_code := G_CHK_INVALID_ADOPTION;
1462 -- get the new messages
1463 x_validation_results(l_err_num).error_message := fnd_msg_pub.get(p_msg_index => l_curr_msg_count +j, p_encoded => 'F');
1464 END LOOP;
1465
1466 ELSIF (l_local_result_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1467 -- for unexpected error get the error messages, put a different code
1468 -- and try to validate the next version
1469
1470 x_qa_return_status := FND_API.G_RET_STS_ERROR;
1471
1472 FOR j in 1..(l_msg_count - l_curr_msg_count) LOOP
1473 l_err_num := x_validation_results.COUNT +1;
1474 x_validation_results(l_err_num).article_id := l_adopt_asis_art_id_tbl(i);
1475 x_validation_results(l_err_num).article_version_id := l_adopt_asis_art_ver_id_tbl(i);
1476 x_validation_results(l_err_num).article_title := l_adopt_asis_art_title_tbl(i);
1477 x_validation_results(l_err_num).error_code := G_CHK_ADOPTION_UNEXP_ERROR;
1478 -- get the new messages
1479 x_validation_results(l_err_num).error_message := fnd_msg_pub.get(p_msg_index => l_curr_msg_count +j, p_encoded => 'F');
1480 END LOOP;
1481
1482 END IF;
1483
1484 i := l_adopt_asis_art_id_tbl.NEXT(i);
1485
1486 END LOOP;
1487
1488 END IF; -- of IF (l_adopt_asis_art_id_tbl.COUNT > 0) THEN
1489
1490 l_adopt_asis_art_id_tbl.DELETE;
1491 l_adopt_asis_art_ver_id_tbl.DELETE;
1492 l_adopt_asis_art_title_tbl.DELETE;
1493
1494 END IF; --of IF(p_org_id <> G_GLOBAL_ORG_ID) THEN
1495
1496
1497 -- if any errors are found set the appropriate return status
1498 IF (x_validation_results.COUNT >0 ) THEN
1499 x_qa_return_status := FND_API.G_RET_STS_ERROR;
1500 END IF;
1501
1502 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
1503 IF fnd_api.to_boolean( p_commit ) THEN
1504 COMMIT WORK;
1505 END IF;
1506
1507 IF (l_debug = 'Y') THEN
1508 okc_debug.log('200: Leaving validate_article_versions_blk: Success');
1509 END IF;
1510
1511
1512 EXCEPTION
1513
1514 WHEN FND_API.G_EXC_ERROR THEN
1515
1516 ROLLBACK TO val_article_versions_blk_PVT;
1517 x_return_status := FND_API.G_RET_STS_ERROR ;
1518
1519 IF (l_debug = 'Y') THEN
1520 okc_debug.log('201: Leaving validate_article_versions_blk: Error');
1521 END IF;
1522
1523 IF (l_adopt_as_is_csr%ISOPEN) THEN
1524 CLOSE l_adopt_as_is_csr;
1525 END IF;
1526 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
1527
1528 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1529
1530 ROLLBACK TO val_article_versions_blk_PVT;
1531 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1532
1533 IF (l_debug = 'Y') THEN
1534 okc_debug.log('202: Leaving validate_article_versions_blk: Unexpected Error');
1535 END IF;
1536
1537 IF (l_adopt_as_is_csr%ISOPEN) THEN
1538 CLOSE l_adopt_as_is_csr;
1539 END IF;
1540 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
1541
1542 WHEN OTHERS THEN
1543
1544 ROLLBACK TO val_article_versions_blk_PVT;
1545 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1546
1547 IF (l_debug = 'Y') THEN
1548 okc_debug.log('203: Leaving validate_article_versions_blk: Unknown Error');
1549 END IF;
1550
1551 IF (l_adopt_as_is_csr%ISOPEN) THEN
1552 CLOSE l_adopt_as_is_csr;
1553 END IF;
1554
1555 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR)
1556 THEN
1557 fnd_msg_pub.add_exc_msg(G_PKG_NAME ,l_api_name );
1558 END IF;
1559 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
1560
1561 END validate_article_versions_blk;
1562
1563 -----------------------------------------------------------------------------
1564
1565 PROCEDURE auto_adopt_articles_blk(
1566 p_api_version IN NUMBER ,
1567 p_init_msg_list IN VARCHAR2 ,
1568 p_commit IN VARCHAR2 ,
1569 x_return_status OUT NOCOPY VARCHAR2 ,
1570 x_msg_count OUT NOCOPY NUMBER ,
1571 x_msg_data OUT NOCOPY VARCHAR2 ,
1572
1573 p_id IN NUMBER)
1574
1575 IS
1576
1577 l_api_version NUMBER := 1.0;
1578 l_api_name VARCHAR2(30) := 'auto_adopt_articles_blk';
1579 l_date DATE := sysdate;
1580
1581 CURSOR l_org_info_csr(cp_global_org_id IN NUMBER) IS
1582 SELECT ORG.organization_id, decode(ORG.org_information1, 'Y', 'ADOPTED', 'AVAILABLE')
1583 FROM HR_ORGANIZATION_INFORMATION ORG
1584 WHERE ORG.org_information_context = 'OKC_TERMS_LIBRARY_DETAILS' AND
1585 ORG.organization_id <> cp_global_org_id;
1586
1587 CURSOR l_non_uniq_title_csr(cp_id IN NUMBER, cp_global_org_id IN NUMBER) IS
1588 -- check if article title is unique for the local org also
1589 -- if title is not unique in the local org, set to 'AVAILABLE' and NULL
1590 SELECT TMP.article_version_id , ART.org_id
1591 FROM OKC_ART_BLK_TEMP TMP, OKC_ARTICLES_ALL ART
1592 WHERE TMP.id = cp_id AND
1593 TMP.global_yn = 'Y' AND
1594 ART.article_title = TMP.article_title AND
1595 ART.org_id <> cp_global_org_id ;
1596
1597 l_org_id_tbl num_tbl_type;
1598 l_adp_typ_tbl varchar_tbl_type;
1599 l_non_uniq_art_ver_tbl num_tbl_type;
1600 l_non_uniq_org_id_tbl num_tbl_type;
1601
1602 l_found BOOLEAN := FALSE;
1603 l_id NUMBER;
1604
1605 BEGIN
1606
1607 IF (l_debug = 'Y') THEN
1608 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);
1609 END IF;
1610
1611 -- standard initialization code
1612 SAVEPOINT auto_adopt_articles_blk_PVT;
1613 G_GLOBAL_ORG_ID := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
1614 G_USER_ID := FND_GLOBAL.USER_ID;
1615 G_LOGIN_ID := FND_GLOBAL.LOGIN_ID;
1616 l_debug := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
1617
1618 IF NOT fnd_api.compatible_api_call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1619 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1620 END IF;
1621 IF fnd_api.to_boolean( p_init_msg_list ) THEN
1622 fnd_msg_pub.initialize;
1623 END IF;
1624 x_return_status := FND_API.G_RET_STS_SUCCESS;
1625
1626 OPEN l_org_info_csr(G_GLOBAL_ORG_ID);
1627 FETCH l_org_info_csr BULK COLLECT INTO l_org_id_tbl, l_adp_typ_tbl;
1628
1629 IF (l_org_id_tbl.COUNT > 0) THEN
1630 l_found := TRUE;
1631 END IF;
1632 CLOSE l_org_info_csr;
1633
1634 IF NOT l_found THEN
1635 -- no local orgs found
1636 RETURN;
1637 END IF;
1638
1639 -- first insert rows in adoptions table for each clause and local org
1640 FORALL i IN l_org_id_tbl.FIRST.. l_org_id_tbl.LAST
1641 INSERT INTO OKC_ARTICLE_ADOPTIONS
1642 (
1643 GLOBAL_ARTICLE_VERSION_ID,
1644 ADOPTION_TYPE,
1645 LOCAL_ORG_ID,
1646 ADOPTION_STATUS,
1647 LOCAL_ARTICLE_VERSION_ID,
1648 OBJECT_VERSION_NUMBER,
1649 CREATED_BY,
1650 CREATION_DATE,
1651 LAST_UPDATED_BY,
1652 LAST_UPDATE_LOGIN,
1653 LAST_UPDATE_DATE
1654 )
1655 SELECT TMP.article_version_id, l_adp_typ_tbl(i), l_org_id_tbl(i),
1656 decode(l_adp_typ_tbl(i), 'ADOPTED', 'APPROVED', NULL),
1657 NULL, 1, G_USER_ID, l_date,
1658 G_USER_ID, G_LOGIN_ID, l_date
1659 FROM OKC_ART_BLK_TEMP TMP
1660 WHERE TMP.id = p_id AND
1661 TMP.global_yn = 'Y';
1662
1663 -- now for local orgs, check if any clauses
1664 -- with the same title existed
1665 OPEN l_non_uniq_title_csr(p_id, G_GLOBAL_ORG_ID);
1666 FETCH l_non_uniq_title_csr BULK COLLECT INTO l_non_uniq_art_ver_tbl, l_non_uniq_org_id_tbl;
1667
1668 l_found := FALSE;
1669 IF (l_non_uniq_art_ver_tbl.COUNT > 0) THEN
1670 l_found := TRUE;
1671 END IF;
1672 CLOSE l_non_uniq_title_csr;
1673
1674 IF (l_found) THEN
1675 -- need to update adoption type to 'AVAILABLE', status to NULL
1676
1677 FORALL i IN l_non_uniq_art_ver_tbl.FIRST..l_non_uniq_art_ver_tbl.LAST
1678 UPDATE OKC_ARTICLE_ADOPTIONS
1679 SET
1680 ADOPTION_TYPE = 'AVAILABLE',
1681 ADOPTION_STATUS = NULL
1682 WHERE
1683 GLOBAL_ARTICLE_VERSION_ID = l_non_uniq_art_ver_tbl(i)
1684 AND LOCAL_ORG_ID = l_non_uniq_org_id_tbl(i);
1685
1686 END IF; -- end of if (l_found)
1687
1688
1689 -- now adopt relationship for those article versions/local orgs
1690 -- where adoption_type = 'ADOPTED'
1691 l_id := get_uniq_id;
1692 INSERT INTO OKC_ART_BLK_TEMP
1693 (
1694 ID,
1695 ARTICLE_ID,
1696 ARTICLE_VERSION_ID,
1697 ORG_ID
1698 )
1699 SELECT l_id, TMP.article_id, TMP.article_version_id, ADP.local_org_id
1700 FROM OKC_ART_BLK_TEMP TMP, OKC_ARTICLE_ADOPTIONS ADP
1701 WHERE TMP.id = p_id AND
1702 TMP.global_yn = 'Y' AND
1703 ADP.global_article_version_id = TMP.article_version_id AND
1704 ADP.adoption_type = 'ADOPTED';
1705
1706 adopt_relationships_blk(
1707 p_id => l_id,
1708 x_return_status => x_return_status
1709 );
1710 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1711 RAISE FND_API.G_EXC_ERROR ;
1712 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1713 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1714 END IF;
1715
1716 l_non_uniq_art_ver_tbl.DELETE;
1717 l_non_uniq_org_id_tbl.DELETE;
1718
1719 l_org_id_tbl.DELETE;
1720 l_adp_typ_tbl.DELETE;
1721
1722 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
1723 IF fnd_api.to_boolean( p_commit ) THEN
1724 COMMIT;
1725 END IF;
1726
1727 IF (l_debug = 'Y') THEN
1728 okc_debug.log('200: Leaving auto_adopt_articles_blk: Success');
1729 END IF;
1730
1731 EXCEPTION
1732
1733 WHEN FND_API.G_EXC_ERROR THEN
1734 ROLLBACK TO auto_adopt_articles_blk_PVT;
1735 x_return_status := FND_API.G_RET_STS_ERROR ;
1736
1737 IF (l_debug = 'Y') THEN
1738 okc_debug.log('201: Leaving auto_adopt_articles_blk: Error');
1739 END IF;
1740 IF (l_org_info_csr%ISOPEN) THEN
1741 CLOSE l_org_info_csr;
1742 END IF;
1743 IF (l_non_uniq_title_csr%ISOPEN) THEN
1744 CLOSE l_non_uniq_title_csr;
1745 END IF;
1746 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
1747
1748 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1749 ROLLBACK TO auto_adopt_articles_blk_PVT;
1750 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1751
1752 IF (l_debug = 'Y') THEN
1753 okc_debug.log('202: Leaving auto_adopt_articles_blk: Unexpected Error');
1754 END IF;
1755 IF (l_org_info_csr%ISOPEN) THEN
1756 CLOSE l_org_info_csr;
1757 END IF;
1758 IF (l_non_uniq_title_csr%ISOPEN) THEN
1759 CLOSE l_non_uniq_title_csr;
1760 END IF;
1761 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
1762
1763 WHEN OTHERS THEN
1764 ROLLBACK TO auto_adopt_articles_blk_PVT;
1765 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1766
1767 IF (l_debug = 'Y') THEN
1768 okc_debug.log('203: Leaving auto_adopt_articles_blk: Unknown Error');
1769 END IF;
1770 IF (l_org_info_csr%ISOPEN) THEN
1771 CLOSE l_org_info_csr;
1772 END IF;
1773 IF (l_non_uniq_title_csr%ISOPEN) THEN
1774 CLOSE l_non_uniq_title_csr;
1775 END IF;
1776
1777 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR)
1778 THEN
1779 fnd_msg_pub.add_exc_msg(G_PKG_NAME ,l_api_name );
1780 END IF;
1781 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
1782
1783 END auto_adopt_articles_blk;
1784
1785 -----------------------------------------------------------------------------
1786
1787
1788 -- IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL) THEN do validations
1789
1790 PROCEDURE pending_approval_blk(
1791 p_api_version IN NUMBER ,
1792 p_init_msg_list IN VARCHAR2 ,
1793 p_commit IN VARCHAR2 ,
1794 p_validation_level IN NUMBER ,
1795 x_return_status OUT NOCOPY VARCHAR2 ,
1796 x_msg_count OUT NOCOPY NUMBER ,
1797 x_msg_data OUT NOCOPY VARCHAR2 ,
1798
1799 p_org_id IN NUMBER ,
1800 p_art_ver_tbl IN num_tbl_type ,
1801 x_validation_results OUT NOCOPY validation_tbl_type )
1802 IS
1803
1804 l_api_version NUMBER := 1.0;
1805 l_api_name VARCHAR2(30) := 'pending_approval_blk';
1806
1807 l_qa_return_status VARCHAR2(1);
1808 l_id NUMBER := -1;
1809 l_rel_id NUMBER := -1;
1810
1811 l_adopt_asis_count NUMBER := 0;
1812 l_global_count NUMBER := 0;
1813 l_localized_count NUMBER := 0;
1814
1815 BEGIN
1816
1817 IF (l_debug = 'Y') THEN
1818 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);
1819
1820 okc_debug.log('101: In pending_approval_blk: p_validation_level='|| p_validation_level||' p_org_id='||p_org_id);
1821
1822 IF (p_art_ver_tbl IS NOT NULL) THEN
1823 okc_debug.log('102: p_art_ver_tbl.COUNT='||p_art_ver_tbl.COUNT);
1824 FOR i in p_art_ver_tbl.FIRST..p_art_ver_tbl.LAST LOOP
1825 okc_debug.log('103: p_art_ver_tbl['||i||']='||p_art_ver_tbl(i));
1826 END LOOP;
1827 END IF;
1828 END IF;
1829
1830 -- standard initialization code
1831 SAVEPOINT pending_approval_blk_PVT;
1832 G_GLOBAL_ORG_ID := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
1833 G_USER_ID := FND_GLOBAL.USER_ID;
1834 G_LOGIN_ID := FND_GLOBAL.LOGIN_ID;
1835 l_debug := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
1836
1837 IF NOT fnd_api.compatible_api_call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1838 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1839 END IF;
1840 IF fnd_api.to_boolean( p_init_msg_list ) THEN
1841 fnd_msg_pub.initialize;
1842 END IF;
1843 x_return_status := FND_API.G_RET_STS_SUCCESS;
1844
1845
1846 -- first populate the the temp table with all the relevant details
1847 get_version_details(
1848 p_org_id => p_org_id,
1849 p_art_ver_tbl => p_art_ver_tbl,
1850 x_return_status => x_return_status,
1851 x_id => l_id,
1852 x_adopt_asis_count => l_adopt_asis_count,
1853 x_global_count => l_global_count,
1854 x_localized_count => l_localized_count
1855 );
1856 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1857 RAISE FND_API.G_EXC_ERROR ;
1858 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1859 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1860 END IF;
1861
1862 -- check if all versions are in draft/rejected/null status first
1863 -- this is not part of normal qa, but a basic sanity check required
1864 -- for all status transitions
1865 status_check_blk(
1866 p_id => l_id,
1867 p_to_status => 'PENDING_APPROVAL',
1868 x_return_status => x_return_status,
1869 x_qa_return_status => l_qa_return_status,
1870 px_validation_results => x_validation_results
1871 );
1872 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1873 RAISE FND_API.G_EXC_ERROR ;
1874 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1875 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1876 ELSE
1877 -- status check failed
1878 IF (l_qa_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1879 x_return_status := FND_API.G_RET_STS_ERROR;
1880 RAISE FND_API.G_EXC_ERROR ;
1881 END IF;
1882 END IF;
1883
1884 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL) THEN
1885
1886 validate_article_versions_blk(
1887 p_api_version => 1.0 ,
1888 p_init_msg_list => FND_API.G_FALSE,
1889 p_commit => FND_API.G_FALSE,
1890 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1891 x_return_status => x_return_status,
1892 x_msg_count => x_msg_count,
1893 x_msg_data => x_msg_data,
1894
1895 p_org_id => p_org_id,
1896 -- call with null p_art_ver_tbl so that validate_article_versions_blk
1897 -- does not have to get the article details again
1898 p_art_ver_tbl => NULL,
1899 p_id => l_id,
1900 x_qa_return_status => l_qa_return_status,
1901 x_validation_results => x_validation_results
1902 );
1903
1904 -- whenever call another api check for return status,
1905 -- no need to check the return status for utility functions
1906
1907 -- in case of ERROR, the calling program will look into x_validation_results
1908 -- and should display them to user for corrective action
1909 -- for UNEXPECTED ERROR, the calling program would set the status
1910 -- as G_RET_STS_UNEXP_ERROR and exit
1911
1912 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1913 RAISE FND_API.G_EXC_ERROR ;
1914 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1915 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1916 ElSE
1917 -- validation executed successfully buth there where some
1918 -- validation errors. we should stop here
1919 IF (l_qa_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1920 x_return_status := FND_API.G_RET_STS_ERROR;
1921 RAISE FND_API.G_EXC_ERROR ;
1922 END IF;
1923 END IF;
1924
1925 END IF; --p_validation_level = FND_API.G_VALID_LEVEL_FULL
1926
1927
1928
1929 -- common for global/local/localized clauses, will do nothing for adopt as is clauses.
1930 update_art_version_status_blk(
1931 p_id => l_id,
1932 p_status => 'PENDING_APPROVAL',
1933 x_return_status => x_return_status
1934 );
1935 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1936 RAISE FND_API.G_EXC_ERROR ;
1937 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1938 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1939 END IF;
1940
1941
1942 IF (p_org_id <> G_GLOBAL_ORG_ID) THEN
1943 -- we are not in the global org
1944
1945 -- for localized clauses only
1946 IF (l_localized_count > 0) THEN
1947 update_adp_status_type_blk(
1948 p_id => l_id,
1949 p_local_org_id => p_org_id,
1950 p_adoption_status => 'PENDING_APPROVAL',
1951 p_adoption_type => 'LOCALIZED',
1952 p_type => 'LOCALIZED',
1953 x_return_status => x_return_status
1954 );
1955 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1956 RAISE FND_API.G_EXC_ERROR ;
1957 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1958 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1959 END IF;
1960 END IF;
1961
1962 -- we are adopting some article versions as is.
1963 IF (l_adopt_asis_count > 0) THEN
1964 -- 1. Update Adoption Row
1965 -- 2. Insert Relationships if first version of any articles are being adopted.
1966
1967 update_adp_status_type_blk(
1968 p_id => l_id,
1969 p_local_org_id => p_org_id,
1970 p_adoption_status => 'PENDING_APPROVAL',
1971 p_adoption_type => 'ADOPTED',
1972 p_type => 'ADOPTED',
1973 x_return_status => x_return_status
1974 );
1975 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1976 RAISE FND_API.G_EXC_ERROR ;
1977 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1978 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1979 END IF;
1980
1981 -- now adopt relationship for these articles
1982 -- first populate the temp table with whatever adopt_relationships_blk requires
1983 l_rel_id := get_uniq_id;
1984 INSERT INTO OKC_ART_BLK_TEMP
1985 (
1986 ID,
1987 ARTICLE_ID,
1988 ARTICLE_VERSION_ID,
1989 ORG_ID
1990 )
1991 SELECT l_rel_id, TMP.article_id, TMP.article_version_id, p_org_id
1992 FROM OKC_ART_BLK_TEMP TMP
1993 WHERE TMP.id = l_id AND
1994 TMP.adopt_asis_yn = 'Y';
1995
1996 adopt_relationships_blk(
1997 p_id => l_rel_id,
1998 x_return_status => x_return_status
1999 );
2000 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2001 RAISE FND_API.G_EXC_ERROR ;
2002 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2003 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2004 END IF;
2005
2006 END IF;
2007
2008 END IF; -- of IF (p_org_id <> G_GLOBAL_ORG_ID)
2009
2010 -- delete rows created in the temp table
2011 DELETE FROM OKC_ART_BLK_TEMP
2012 WHERE id IN (l_id, l_rel_id);
2013
2014 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2015 IF(FND_API.to_boolean(p_commit)) THEN
2016 COMMIT;
2017 END IF;
2018
2019 IF (l_debug = 'Y') THEN
2020 okc_debug.log('200: Leaving pending_approval_blk: Success');
2021 END IF;
2022
2023 EXCEPTION
2024
2025 WHEN FND_API.G_EXC_ERROR THEN
2026 ROLLBACK TO pending_approval_blk_PVT;
2027 x_return_status := FND_API.G_RET_STS_ERROR ;
2028
2029 IF (l_debug = 'Y') THEN
2030 okc_debug.log('201: Leaving pending_approval_blk: Error');
2031 END IF;
2032 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2033
2034 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2035 ROLLBACK TO pending_approval_blk_PVT;
2036 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2037
2038 IF (l_debug = 'Y') THEN
2039 okc_debug.log('202: Leaving pending_approval_blk: Unexpected Error');
2040 END IF;
2041
2042 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2043
2044 WHEN OTHERS THEN
2045 ROLLBACK TO pending_approval_blk_PVT;
2046 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2047
2048 IF (l_debug = 'Y') THEN
2049 okc_debug.log('203: Leaving pending_approval_blk: Unknown Error');
2050 END IF;
2051
2052 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR)
2053 THEN
2054 fnd_msg_pub.add_exc_msg(G_PKG_NAME ,l_api_name );
2055 END IF;
2056 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2057
2058 END pending_approval_blk;
2059
2060 ---------------------------------------------------------------------
2061
2062 PROCEDURE approve_blk(
2063 p_api_version IN NUMBER ,
2064 p_init_msg_list IN VARCHAR2 ,
2065 p_commit IN VARCHAR2 ,
2066 x_return_status OUT NOCOPY VARCHAR2 ,
2067 x_msg_count OUT NOCOPY NUMBER ,
2068 x_msg_data OUT NOCOPY VARCHAR2 ,
2069
2070 p_org_id IN NUMBER ,
2071 p_art_ver_tbl IN num_tbl_type,
2072 x_validation_results OUT NOCOPY validation_tbl_type )
2073 IS
2074
2075 l_api_version NUMBER := 1.0;
2076 l_api_name VARCHAR2(30) := 'approve_blk';
2077
2078 l_qa_return_status VARCHAR2(1);
2079 l_id NUMBER := -1;
2080
2081 l_adopt_asis_count NUMBER := 0;
2082 l_global_count NUMBER := 0;
2083 l_localized_count NUMBER := 0;
2084
2085 BEGIN
2086
2087 IF (l_debug = 'Y') THEN
2088 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);
2089
2090 okc_debug.log('101: In approve_blk: p_org_id='||p_org_id);
2091
2092 IF (p_art_ver_tbl IS NOT NULL) THEN
2093 okc_debug.log('102: p_art_ver_tbl.COUNT='||p_art_ver_tbl.COUNT);
2094 FOR i in p_art_ver_tbl.FIRST..p_art_ver_tbl.LAST LOOP
2095 okc_debug.log('103: p_art_ver_tbl['||i||']='||p_art_ver_tbl(i));
2096 END LOOP;
2097 END IF;
2098 END IF;
2099
2100 -- standard initialization code
2101 SAVEPOINT approve_blk_PVT;
2102 G_GLOBAL_ORG_ID := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
2103 G_USER_ID := FND_GLOBAL.USER_ID;
2104 G_LOGIN_ID := FND_GLOBAL.LOGIN_ID;
2105 l_debug := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
2106
2107 IF NOT fnd_api.compatible_api_call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2108 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2109 END IF;
2110 IF fnd_api.to_boolean( p_init_msg_list ) THEN
2111 fnd_msg_pub.initialize;
2112 END IF;
2113 x_return_status := FND_API.G_RET_STS_SUCCESS;
2114
2115
2116 -- first populate the the temp table with all the relevant details
2117 get_version_details(
2118 p_org_id => p_org_id,
2119 p_art_ver_tbl => p_art_ver_tbl,
2120 x_return_status => x_return_status,
2121 x_id => l_id,
2122 x_adopt_asis_count => l_adopt_asis_count,
2123 x_global_count => l_global_count,
2124 x_localized_count => l_localized_count
2125 );
2126 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2127 RAISE FND_API.G_EXC_ERROR ;
2128 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2129 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2130 END IF;
2131
2132 -- check if all versions are in pending approval status first
2133 -- this is not part of normal qa, but a basic sanity check required
2134 -- for all status transitions
2135 status_check_blk(
2136 p_id => l_id,
2137 p_to_status => 'APPROVED',
2138 x_return_status => x_return_status,
2139 x_qa_return_status => l_qa_return_status,
2140 px_validation_results => x_validation_results
2141 );
2142 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2143 RAISE FND_API.G_EXC_ERROR ;
2144 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2145 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2146 ELSE
2147 -- status check failed
2148 IF (l_qa_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2149 x_return_status := FND_API.G_RET_STS_ERROR;
2150 RAISE FND_API.G_EXC_ERROR ;
2151 END IF;
2152 END IF;
2153
2154
2155 -- common for global/local/localized clauses, will do nothing for adopt as is clauses.
2156 update_art_version_status_blk(
2157 p_id => l_id,
2158 p_status => 'APPROVED',
2159 x_return_status => x_return_status
2160 );
2161 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2162 RAISE FND_API.G_EXC_ERROR ;
2163 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2164 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2165 END IF;
2166
2167
2168 -- common for global/local/localized clauses, will do nothing for adopt as is clauses.
2169 update_prev_vers_enddate_blk(
2170 p_id => l_id,
2171 x_return_status => x_return_status
2172 );
2173 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2174 RAISE FND_API.G_EXC_ERROR ;
2175 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2176 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2177 END IF;
2178
2179
2180 IF (p_org_id = G_GLOBAL_ORG_ID) THEN
2181
2182 IF (l_global_count > 0) THEN
2183 -- kick off auto adoption
2184
2185 auto_adopt_articles_blk(
2186 p_api_version => 1.0,
2187 p_init_msg_list => FND_API.G_FALSE,
2188 p_commit => FND_API.G_FALSE,
2189 x_return_status => x_return_status ,
2190 x_msg_count => x_msg_count ,
2191 x_msg_data => x_msg_data ,
2192
2193 p_id => l_id
2194 );
2195 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2196 RAISE FND_API.G_EXC_ERROR ;
2197 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2199 END IF;
2200
2201 END IF;
2202
2203 ELSE
2204 -- we are not in the global org
2205
2206 IF (l_adopt_asis_count > 0) THEN
2207 -- we are adopting some article versions as is, so update Adoption Row
2208
2209 update_adp_status_type_blk(
2210 p_id => l_id,
2211 p_local_org_id => p_org_id,
2212 p_adoption_status => 'APPROVED',
2213 p_adoption_type => 'ADOPTED',
2214 p_type => 'ADOPTED',
2215 x_return_status => x_return_status
2216 );
2217 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2218 RAISE FND_API.G_EXC_ERROR ;
2219 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2221 END IF;
2222
2223 END IF;
2224
2225 IF (l_localized_count > 0) THEN
2226 -- if some articles are localized update adoption row
2227
2228 update_adp_status_type_blk(
2229 p_id => l_id,
2230 p_local_org_id => p_org_id,
2231 p_adoption_status => 'APPROVED',
2232 p_adoption_type => 'LOCALIZED',
2233 p_type => 'LOCALIZED',
2234 x_return_status => x_return_status
2235 );
2236 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2237 RAISE FND_API.G_EXC_ERROR ;
2238 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2239 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2240 END IF;
2241
2242 END IF;
2243
2244 END IF; -- of IF (p_org_id = G_GLOBAL_ORG_ID) THEN
2245
2246
2247 -- delete rows created in the temp table
2248 DELETE FROM OKC_ART_BLK_TEMP
2249 WHERE id = l_id;
2250
2251 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2252 IF(FND_API.to_boolean(p_commit)) THEN
2253 COMMIT;
2254 END IF;
2255
2256 IF (l_debug = 'Y') THEN
2257 okc_debug.log('200: Leaving approve_blk: Success');
2258 END IF;
2259
2260
2261 EXCEPTION
2262
2263 WHEN FND_API.G_EXC_ERROR THEN
2264 ROLLBACK TO approve_blk_PVT;
2265 x_return_status := FND_API.G_RET_STS_ERROR ;
2266
2267 IF (l_debug = 'Y') THEN
2268 okc_debug.log('201: Leaving approve_blk: Error');
2269 END IF;
2270 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2271
2272 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2273 ROLLBACK TO approve_blk_PVT;
2274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2275
2276 IF (l_debug = 'Y') THEN
2277 okc_debug.log('202: Leaving approve_blk: Unexpected Error');
2278 END IF;
2279 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2280
2281 WHEN OTHERS THEN
2282 ROLLBACK TO approve_blk_PVT;
2283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2284
2285 IF (l_debug = 'Y') THEN
2286 okc_debug.log('203: Leaving approve_blk: Unknown Error');
2287 END IF;
2288
2289 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR)
2290 THEN
2291 fnd_msg_pub.add_exc_msg(G_PKG_NAME ,l_api_name );
2292 END IF;
2293 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2294
2295 END approve_blk;
2296
2297 ---------------------------------------------------------------------
2298
2299 PROCEDURE reject_blk(
2300 p_api_version IN NUMBER ,
2301 p_init_msg_list IN VARCHAR2 ,
2302 p_commit IN VARCHAR2 ,
2303 x_return_status OUT NOCOPY VARCHAR2 ,
2304 x_msg_count OUT NOCOPY NUMBER ,
2305 x_msg_data OUT NOCOPY VARCHAR2 ,
2306
2307 p_org_id IN NUMBER ,
2308 p_art_ver_tbl IN num_tbl_type ,
2309 x_validation_results OUT NOCOPY validation_tbl_type )
2310 IS
2311
2312 l_api_version NUMBER := 1.0;
2313 l_api_name VARCHAR2(30) := 'reject_blk';
2314
2315 l_qa_return_status VARCHAR2(1);
2316 l_id NUMBER := -1;
2317
2318 l_adopt_asis_count NUMBER := 0;
2319 l_global_count NUMBER := 0;
2320 l_localized_count NUMBER := 0;
2321
2322 BEGIN
2323
2324 IF (l_debug = 'Y') THEN
2325 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);
2326
2327 okc_debug.log('101: In reject_blk: p_org_id='||p_org_id);
2328
2329 IF (p_art_ver_tbl IS NOT NULL) THEN
2330 okc_debug.log('102: p_art_ver_tbl.COUNT='||p_art_ver_tbl.COUNT);
2331 FOR i in p_art_ver_tbl.FIRST..p_art_ver_tbl.LAST LOOP
2332 okc_debug.log('103: p_art_ver_tbl['||i||']='||p_art_ver_tbl(i));
2333 END LOOP;
2334 END IF;
2335 END IF;
2336
2337 -- standard initialization code
2338 SAVEPOINT reject_blk_PVT;
2339 G_GLOBAL_ORG_ID := NVL(FND_PROFILE.VALUE('OKC_GLOBAL_ORG_ID'),-99);
2340 G_USER_ID := FND_GLOBAL.USER_ID;
2341 G_LOGIN_ID := FND_GLOBAL.LOGIN_ID;
2342 l_debug := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
2343
2344 IF NOT fnd_api.compatible_api_call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2345 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2346 END IF;
2347 IF fnd_api.to_boolean( p_init_msg_list ) THEN
2348 fnd_msg_pub.initialize;
2349 END IF;
2350 x_return_status := FND_API.G_RET_STS_SUCCESS;
2351
2352 -- first populate the the temp table with all the relevant details
2353 get_version_details(
2354 p_org_id => p_org_id,
2355 p_art_ver_tbl => p_art_ver_tbl,
2356 x_return_status => x_return_status,
2357 x_id => l_id,
2358 x_adopt_asis_count => l_adopt_asis_count,
2359 x_global_count => l_global_count,
2360 x_localized_count => l_localized_count
2361 );
2362 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2363 RAISE FND_API.G_EXC_ERROR ;
2364 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2365 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2366 END IF;
2367
2368 -- check if all versions are in pending approval status first
2369 -- this is not part of normal qa, but a basic sanity check required
2370 -- for all status transitions
2371 status_check_blk(
2372 p_id => l_id,
2373 p_to_status => 'REJECTED',
2374 x_return_status => x_return_status,
2375 x_qa_return_status => l_qa_return_status,
2376 px_validation_results => x_validation_results
2377 );
2378 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2379 RAISE FND_API.G_EXC_ERROR ;
2380 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2381 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2382 ELSE
2383 -- status check failed
2384 IF (l_qa_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2385 x_return_status := FND_API.G_RET_STS_ERROR;
2386 RAISE FND_API.G_EXC_ERROR ;
2387 END IF;
2388 END IF;
2389
2390
2391 -- common for global/local/localized clauses, will do nothing for adopt as is clauses.
2392 update_art_version_status_blk(
2393 p_id => l_id,
2394 p_status => 'REJECTED',
2395 x_return_status => x_return_status
2396 );
2397 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2398 RAISE FND_API.G_EXC_ERROR ;
2399 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2400 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2401 END IF;
2402
2403
2404
2405 IF (p_org_id <> G_GLOBAL_ORG_ID) THEN
2406 -- we are not in the global org, article for adoption as is are being rejected
2407
2408 IF (l_adopt_asis_count > 0) THEN
2409 -- we are adopting some article versions as is.
2410 -- 1. Update Adoption Row
2411 -- 2. Delete relationship rows if the first version is being rejected
2412
2413 update_adp_status_type_blk(
2414 p_id => l_id,
2415 p_local_org_id => p_org_id,
2416 p_adoption_status => 'REJECTED',
2417 p_adoption_type => 'AVAILABLE',
2418 p_type => 'ADOPTED',
2419 x_return_status => x_return_status
2420 );
2421 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2422 RAISE FND_API.G_EXC_ERROR ;
2423 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2424 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2425 END IF;
2426
2427 delete_relationships_blk(
2428 p_id => l_id,
2429 p_org_id => p_org_id,
2430 x_return_status => x_return_status
2431 );
2432 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2433 RAISE FND_API.G_EXC_ERROR ;
2434 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2435 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2436 END IF;
2437
2438 END IF;
2439
2440 IF (l_localized_count > 0) THEN
2441 -- update adoption row if localized
2442
2443 update_adp_status_type_blk(
2444 p_id => l_id,
2445 p_local_org_id => p_org_id,
2446 p_adoption_status => 'REJECTED',
2447 p_adoption_type => 'LOCALIZED',
2448 p_type => 'LOCALIZED',
2449 x_return_status => x_return_status
2450 );
2451 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2452 RAISE FND_API.G_EXC_ERROR ;
2453 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2454 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2455 END IF;
2456
2457 END IF;
2458
2459 END IF; -- of IF (p_org_id <> G_GLOBAL_ORG_ID) THEN
2460
2461
2462 -- delete rows created in the temp table
2463 DELETE FROM OKC_ART_BLK_TEMP
2464 WHERE id = l_id;
2465
2466 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2467 IF(FND_API.to_boolean(p_commit)) THEN
2468 COMMIT;
2469 END IF;
2470
2471 IF (l_debug = 'Y') THEN
2472 okc_debug.log('200: Leaving reject_blk: Success');
2473 END IF;
2474
2475
2476 EXCEPTION
2477
2478 WHEN FND_API.G_EXC_ERROR THEN
2479 ROLLBACK TO reject_blk_PVT;
2480 x_return_status := FND_API.G_RET_STS_ERROR ;
2481
2482 IF (l_debug = 'Y') THEN
2483 okc_debug.log('201: Leaving reject_blk: Error');
2484 END IF;
2485
2486 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2487
2488 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2489 ROLLBACK TO reject_blk_PVT;
2490 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2491
2492 IF (l_debug = 'Y') THEN
2493 okc_debug.log('202: Leaving reject_blk: Unexpected Error');
2494 END IF;
2495
2496 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2497
2498 WHEN OTHERS THEN
2499 ROLLBACK TO reject_blk_PVT;
2500 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2501
2502 IF (l_debug = 'Y') THEN
2503 okc_debug.log('203: Leaving reject_blk: Unknown Error');
2504 END IF;
2505
2506 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR)
2507 THEN
2508 fnd_msg_pub.add_exc_msg(G_PKG_NAME ,l_api_name );
2509 END IF;
2510 fnd_msg_pub.count_and_get( p_count => x_msg_count , p_data => x_msg_data);
2511
2512 END reject_blk;
2513
2514 -----------------------------------------------------------------------------
2515
2516 END OKC_ART_BLK_PVT;
2517
2518