1 PACKAGE BODY cs_kb_conc_prog_pkg AS
2 /* $Header: csksynib.pls 120.2 2005/11/11 17:27:06 klou noship $ */
3
4 /* errbuf = err messages
5 retcode = 0 SUCCESS, 1 = warning, 2=error
6 */
7
8 /* bmode: S = sync OFAST=optimize fast, OFULL = optimize full,
9 R = REBUILD, DR = DROP/Recreate
10 */
11
12 -- **********************
13 -- PRIVATE DECLARATIONS
14 -- **********************
15
16 invalid_mode_error EXCEPTION;
17 invalid_action_error EXCEPTION;
18 drop_index_error EXCEPTION;
19 create_index_error EXCEPTION;
20 rebuild_cache_error EXCEPTION;
21
22 g_cs_short_name VARCHAR2(10) := UPPER('CS'); -- set at patching
23 g_apps_short_name VARCHAR2(10) := UPPER('APPS'); -- set at patching
24 -- New for bug 4321268
25 G_BATCH_SIZE NUMBER := 10000;
26
27 -- New internal procedures for bug 4321268
28 /*
29 * Populate solution text index.
30 *
31 */
32 PROCEDURE populate_set_index (
33 x_msg_error OUT NOCOPY VARCHAR2,
34 x_return_status OUT NOCOPY VARCHAR2
35 )
36 IS
37 CURSOR all_published_solutions IS
38 SELECT tl.rowid -- tl.set_id
39 FROM cs_kb_sets_tl tl, cs_kb_sets_b b
40 WHERE b.set_id = tl.set_id
41 AND b.status = 'PUB';
42
43
44 TYPE l_rowid_type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
45 l_rowid_list l_rowid_type;
46
47 l_soln_comp_index VARCHAR2(250) := 'CS_KB_SETS_TL_N3';
48
49 BEGIN
50 x_return_status := fnd_api.G_RET_STS_SUCCESS;
51
52 -- Fetch out the list of IDs for all published solutions
53 OPEN all_published_solutions;
54 LOOP
55 FETCH all_published_solutions BULK COLLECT INTO l_rowid_list limit G_BATCH_SIZE;
56 FORALL i IN l_rowid_list.FIRST..l_rowid_list.LAST
57 UPDATE cs_kb_sets_tl
58 SET composite_assoc_index = 'R'
59 -- ,last_update_date = SYSDATE --- do not checkin updating last_update_date
60 WHERE rowid = l_rowid_list(i);
61
62 COMMIT;
63 /*
64 -- click off the sync. program
65 launch_sync_request(
66 p_mode => 'S',
67 p_conc_request_name => 'CS_KB_SYNC_SOLUTIONS_INDEX',
68 x_msg_error => x_msg_error,
69 x_return_status => x_return_status );
70
71 IF x_return_status != fnd_api.G_RET_STS_SUCCESS THEN
72 EXIT;
73 END IF;
74
75 x_return_status := fnd_api.G_RET_STS_SUCCESS;
76 */
77
78 -- Check if all_published_solutions is notfound.
79 -- NOTE: this check should come at the end because for the last batch
80 -- the total number of sets being fetched may be less than the l_batch_size.
81 -- If l_set_id_list is not filled with the exact number as the l_batch_size,
82 -- all_published_solutons%notfound is true. Putting this at the end
83 -- guarantees we process the last batch.
84 EXIT WHEN all_published_solutions%NOTFOUND;
85 END LOOP;
86 CLOSE all_published_solutions;
87
88 Sync_index( l_soln_comp_index, 'S', 0 );
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 ROLLBACK; -- do not use savepoint because savepoint is cleared when commit.
93 x_msg_error := 'populate_set_index: '
94 ||fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '||SQLERRM;
95 x_return_status := fnd_api.G_RET_STS_ERROR;
96 END populate_set_index;
97
98 /*
99 * Populate element text index.
100 *
101 */
102 PROCEDURE populate_element_index (
103 x_msg_error OUT NOCOPY VARCHAR2,
104 x_return_status OUT NOCOPY VARCHAR2
105 )
106 IS
107 l_statement_comp_index VARCHAR2(250) := 'CS_KB_ELEMENTS_TL_N2';
108 BEGIN
109 x_return_status := fnd_api.G_RET_STS_SUCCESS;
110
111 SAVEPOINT populate_element_index_SAV;
112
113 -- We do not use bulk update in this case because the concurrent request
114 -- is incompatbile with itself. Even we kick off the sync. request, it
115 -- will be in pending status until "DR" or "R" request is finished. So,
116 UPDATE /*+ parallel(t) */ cs_kb_elements_tl t
117 SET t.composite_text_index = 'B';
118
119 COMMIT;
120
121 -- Reestablish savepoint, as commit cleared it.
122 SAVEPOINT populate_element_index_SAV;
123
124 -- Start synchronizing index.
125 Sync_index( l_statement_comp_index, 'S', 0 );
126
127 EXCEPTION
128 WHEN OTHERS THEN
129 ROLLBACK TO populate_element_index_SAV;
130 x_msg_error := 'populate_element_index: '
131 ||fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '||SQLERRM;
132 x_return_status := fnd_api.G_RET_STS_ERROR;
133 END populate_element_index;
134
135
136 /*
137 * Populate soluton categories text index.
138 *
139 */
140 PROCEDURE populate_soln_cat_index (
141 x_msg_error OUT NOCOPY VARCHAR2,
142 x_return_status OUT NOCOPY VARCHAR2
143 )
144 IS
145 index1 VARCHAR2(250) := 'CS_KB_SOLN_CAT_TL_N1';
146
147 BEGIN
148 x_return_status := fnd_api.G_RET_STS_SUCCESS;
149 SAVEPOINT populate_soln_cat_index_SAV;
150 UPDATE /*+ parallel(t) */ cs_kb_soln_categories_tl t
151 SET t.name = t.name;
152
153 COMMIT;
154
155 -- reestablish savepoint after commit.
156 SAVEPOINT populate_soln_cat_index_SAV;
157
158 -- Start index synchronization
159 Sync_index( index1, 'S', 0 );
160
161 EXCEPTION
162 WHEN OTHERS THEN
163 ROLLBACK TO populate_soln_cat_index_SAV;
164 x_msg_error := 'populate_sol_cat_index: '
165 ||fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '||SQLERRM;
166 x_return_status := fnd_api.G_RET_STS_ERROR;
167 END populate_soln_cat_index;
168
169
170 /*
171 * Populate forum index.
172 *
173 */
174 PROCEDURE populate_forum_index (
175 x_msg_error OUT NOCOPY VARCHAR2,
176 x_return_status OUT NOCOPY VARCHAR2
177 )
178 IS
179 index3 VARCHAR2(250) := 'CS_FORUM_MESSAGES_TL_N4';
180 BEGIN
181 x_return_status := fnd_api.G_RET_STS_SUCCESS;
182
183 SAVEPOINT populate_forum_index_SAV;
184
185 UPDATE /*+ parallel(t) */ cs_forum_messages_tl t
186 SET t.composite_assoc_col = 'B';
187
188 COMMIT;
189
190 -- reestablish savepoint after commit
191 SAVEPOINT populate_forum_index_SAV;
192
193 -- Start index synchronization
194 Sync_index( index3, 'S', 0 );
195 EXCEPTION
196 WHEN OTHERS THEN
197 ROLLBACK TO populate_forum_index_SAV;
198 x_msg_error := 'populate_forum_index: '
199 ||fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '||SQLERRM;
200 x_return_status := fnd_api.G_RET_STS_ERROR;
201 END populate_forum_index;
202 -- 4321268_new_apis_eof
203 /*
204 * get_max_parallel_worker: get THE job_queue_processes value.
205 */
206 FUNCTION get_max_parallel_worker RETURN NUMBER
207 IS
208 l_worker NUMBER := 0;
209
210 -- 4321268
211 -- Fetch the correct paremeters to calculate max. parallel workers.
212 CURSOR get_param_value(p_name IN varchar2) IS
213 SELECT to_number(nvl(VALUE, 0))
214 FROM v$parameter
215 WHERE name = lower(p_name);
216
217 l_cpu_count NUMBER;
218 l_thread_per_cpu NUMBER;
219 -- 4321268_eof
220 BEGIN
221 --4321268
222 OPEN get_param_value('cpu_count');
223 FETCH get_param_value INTO l_cpu_Count;
224 CLOSE get_param_value;
225
226 OPEN get_param_value('parallel_threads_per_cpu');
227 FETCH get_param_value INTO l_thread_per_cpu;
228 CLOSE get_param_value;
229
230 -- SELECT to_number(nvl(VALUE, 0)) INTO l_worker FROM v$parameter
231 -- WHERE NAME = 'job_queue_processes';
232 l_worker := l_cpu_count * l_thread_per_cpu;
233 --4321268
234 RETURN l_worker;
235 EXCEPTION
236 WHEN OTHERS THEN
237 RETURN l_worker;
238 END;
239
240 /*
241 * is_validate_mode: VALIDATE a synchronization MODE.
242 * RETURN 'Y' IF THE MODE IS valid. Otherwise RETURN 'N'.
243 */
244 FUNCTION is_validate_mode(bmode IN VARCHAR2) RETURN VARCHAR
245 IS
246 l_valid_mode VARCHAR2(1) := 'Y';
247 l_mode VARCHAR2(10) := bmode;
248 BEGIN
249 IF l_mode NOT IN ('S', 'R', 'OFAST', 'OFULL', 'RC', 'DR' ) THEN
250 l_valid_mode := 'N';
251 END IF;
252 RETURN l_valid_mode;
253
254 END;
255
256 /*
257 * do_create
258 * This PROCEDURE executes THE CREATE command.
259 */
260 PROCEDURE do_create ( p_create_cmd IN VARCHAR2,
261 p_index_name IN VARCHAR2,
262 p_index_version IN VARCHAR2,
263 x_msg_error OUT NOCOPY VARCHAR2,
264 x_return_status OUT NOCOPY VARCHAR2
265 )
266 IS
267
268 l_update VARCHAR2(1) := 'Y';
269 BEGIN
270 -- initialize return status
271 x_return_status := fnd_api.G_RET_STS_ERROR;
272
273 EXECUTE IMMEDIATE p_create_cmd;
274
275 x_return_status := fnd_api.G_RET_STS_SUCCESS;
276 EXCEPTION
277 WHEN OTHERS THEN
278 x_msg_error := 'do_create: '||p_index_name||' :'
279 ||fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '||SQLERRM;
280 x_return_status := fnd_api.G_RET_STS_ERROR;
281 END do_create;
282
283
284 /*
285 * resolve_parallel_indexing
286 */
287 FUNCTION resolve_parallel_indexing (
288 p_create_cmd IN VARCHAR2,
289 p_worker IN NUMBER DEFAULT 0
290 ) RETURN VARCHAR
291 IS
292 l_cmd VARCHAR2(500) := p_create_cmd;
293 l_worker NUMBER := p_worker;
294 l_max_worker NUMBER := get_max_parallel_worker;
295
296 --3576867
297 l_db_version NUMBER := null;
298 l_compatibility VARCHAR2(100) := null;
299 l_db_version_str VARCHAR2(100) := null;
300 BEGIN
301 --3576867
302 If p_worker is null Then
303 l_worker := 0;
304 End If;
305
306 DBMS_UTILITY.db_version(l_db_version_str, l_compatibility);
307 If l_db_version_str is null Then
308 l_db_version := 8;
309 Else
310 l_db_version := to_number(substr(l_db_version_str, 1,
311 (instr(l_db_version_str, '.'))-1));
312 End If;
313
314 If l_db_version Is Not Null Then
315 If l_db_version > 8 Then
316 IF l_worker > l_max_worker THEN
317 l_worker := l_max_worker;
318 END IF;
319 End if; -- l_db_version eof
320 Else
321 l_worker := 0;
322 End If;
323 -- 3576867 eof
324
325 IF l_worker > 0 THEN
326 l_cmd := l_cmd || ' parallel '||TO_CHAR(l_worker);
327 END IF;
328
329 RETURN l_cmd;
330 EXCEPTION
331 WHEN OTHERS THEN
332 -- any errors: do not append anything.
333 RETURN p_create_cmd;
334 END resolve_parallel_indexing;
335
336 -- ************************
337 -- PUBLIC IMPLEMENTATIONS
338 -- ************************
339
340 PROCEDURE Sync_index( index1 IN VARCHAR2,
341 bmode IN VARCHAR2,
342 pworker IN NUMBER DEFAULT 0)
343 IS
344 l_index_name VARCHAR2(300) := g_cs_short_name||'.'||index1;
345
346 BEGIN
347
348 IF bmode = 'S' THEN
349 AD_CTX_DDL.sync_index( l_index_name );
350 ELSIF bmode = 'OFAST' THEN
351 AD_CTX_DDL.OPTIMIZE_INDEX( l_index_name, CTX_DDL.OPTLEVEL_FAST, NULL, NULL );
352 ELSIF bmode = 'OFULL' THEN
353 AD_CTX_DDL.OPTIMIZE_INDEX( l_index_name, CTX_DDL.OPTLEVEL_FULL, NULL, NULL );
354 ELSIF bmode = 'R' THEN
355 -- 4321268: rebuild in parallel mode always. Serial online mode,
356 -- is taking care in the individual index program.
357 IF pworker IS NOT NULL AND pworker > 0 THEN
358
359 EXECUTE IMMEDIATE 'alter index ' || l_index_name ||' REBUILD parallel '|| to_char(pworker);
360 END IF;
361 -- 4321268_eof
362 ELSIF bmode = 'DR' THEN
363 -- logic to drop or create is taken in the individual api.
364 NULL;
365 ELSE
366 FND_FILE.PUT_LINE(FND_FILE.LOG,
367 fnd_message.get_string('CS', 'CS_KB_SYN_INDEX_INV_MODE'));
368 RAISE invalid_mode_error;
369 END IF;
370 END Sync_index;
371
372
373 /*
374 * Sync_All_index: synchronize ALL KM indices IN serial MODE.
375 * Deprecated since 11.5.10.
376 */
377 PROCEDURE Sync_All_Index (ERRBUF OUT NOCOPY VARCHAR2,
378 RETCODE OUT NOCOPY NUMBER,
379 BMODE IN VARCHAR2 DEFAULT NULL)
380 IS
381 BEGIN
382
383 -- Return successfully
384 errbuf := fnd_message.get_string('CS', 'CS_KB_C_SUCCESS');
385 retcode :=0;
386 END Sync_All_Index;
387
388 /*
389 * Create_Set_Index
390 * This PROCEDURE creates THE solution INDEX AND also populates THE INDEX
391 * content.
392 */
393 PROCEDURE Create_Set_Index
394 ( pworker IN NUMBER DEFAULT 0,
395 x_msg_error OUT NOCOPY VARCHAR2,
396 x_return_status OUT NOCOPY VARCHAR2
397 )
398 IS
399 l_create_cmmd VARCHAR2(500):= NULL;
400 l_index_version VARCHAR2(15) := '115.10.1';
401 l_index_name VARCHAR2(30) := 'cs_kb_sets_tl_N3';
402
403 BEGIN
404
405 l_create_cmmd :=
406 ' CREATE INDEX '||g_cs_short_name||'.'||l_index_name||' on '
407 || g_cs_short_name||'.cs_kb_sets_tl(composite_assoc_index) '
408 || ' INDEXTYPE IS ctxsys.context '
409 || ' parameters (''datastore '||g_apps_short_name||'.CS_KB_COMPOSITE_ELES '
410 || ' section group '||g_apps_short_name||'.CS_KB_BASIC_GRP '
411 || ' lexer '||g_apps_short_name||'.CS_KB_GLOBAL_LEXER language column SOURCE_LANG '
412 || ' wordlist '||g_apps_short_name||'.CS_KB_FUZZY_PREF '
413 --4321268
414 || ' storage ' ||g_apps_short_name||'.CS_KB_INDEX_STORAGE '; -- <-command not yet completed
415 -- 4321268_eof
416
417 x_return_status := fnd_api.G_RET_STS_ERROR;
418
419 -- 4321268
420 IF nvl(pworker,0) = 0 THEN
421 -- Create index online
422 -- 1. Create index without populate
423 l_create_cmmd := l_create_cmmd || ' nopopulate '') ';
424 ELSE
425 l_create_cmmd := l_create_cmmd || ''')';
426 l_create_cmmd := resolve_parallel_indexing(l_create_cmmd, pworker);
427 END IF;
428
429 -- 4321268_eof
430 do_create(
431 p_create_cmd => l_create_cmmd,
432 p_index_name => l_index_name,
433 p_index_version => l_index_version,
434 x_msg_error => x_msg_error,
435 x_return_status => x_return_status );
436
437 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
438 RAISE create_index_error;
439 END IF;
440
441 -- 4321268
442 IF nvl(pworker, 0) = 0 THEN
443 populate_set_index (
444 x_msg_error ,
445 x_return_status
446 );
447 END IF;
448 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
449 RAISE create_index_error;
450 END IF;
451 -- 4321268_eof
452
453
454 x_return_status := fnd_api.G_RET_STS_SUCCESS;
455
456 EXCEPTION
457 WHEN create_index_error THEN
458 NULL; -- x_msg_error is set in the do_create api.
459 WHEN others THEN
460 x_msg_error := 'Create_Set_Index: '
461 ||fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '||SQLERRM;
462 END Create_Set_Index;
463
464
465 /*
466 * Create_Element_Index
467 * This PROCEDURE creates THE STATEMENT INDEX AND also populates THE INDEX
468 * content.
469 */
470 PROCEDURE Create_Element_Index
471 ( pworker IN NUMBER DEFAULT 0,
472 x_msg_error OUT NOCOPY VARCHAR2,
473 x_return_status OUT NOCOPY VARCHAR2
474 )
475 IS
476 l_create_cmmd VARCHAR2(500):= NULL;
477
478 l_index_version VARCHAR2(15) := '115.10.1';
479 l_index_name VARCHAR2(30) := 'cs_kb_elements_tl_N2';
480 BEGIN
481 l_create_cmmd :=
482 ' CREATE INDEX '||g_cs_short_name||'.cs_kb_elements_tl_N2 on '
483 ||g_cs_short_name||'.cs_kb_elements_tl(composite_text_index) '
484 ||' INDEXTYPE IS ctxsys.context '
485 ||' parameters (''datastore '||g_apps_short_name||'.CS_KB_ELES '
486 ||' section group '||g_apps_short_name||'.CS_KB_BASIC_GRP '
487 ||' lexer '||g_apps_short_name||'.CS_KB_GLOBAL_LEXER language column SOURCE_LANG '
488 ||' wordlist '||g_apps_short_name||'.CS_KB_FUZZY_PREF '
489 --4321268
490 || ' storage ' ||g_apps_short_name||'.CS_KB_INDEX_STORAGE '; -- <-command not yet completed
491 -- 4321268_eof
492
493 x_return_status := fnd_api.G_RET_STS_ERROR;
494
495 -- 4321268
496 IF nvl(pworker,0) = 0 THEN
497 -- Create index online
498 -- 1. Create index without populate
499 l_create_cmmd := l_create_cmmd || ' nopopulate '') ';
500 ELSE
501 l_create_cmmd := l_create_cmmd || ''')';
502 l_create_cmmd := resolve_parallel_indexing(l_create_cmmd, pworker);
503
504 END IF;
505 -- 4321268_eof
506
507 do_create
508 ( p_create_cmd => l_create_cmmd,
509 p_index_name => l_index_name,
510 p_index_version => l_index_version,
511 x_msg_error => x_msg_error,
512 x_return_status => x_return_status
513 );
514
515 -- 4321268
516 IF nvl(pworker, 0) = 0 THEN
517 populate_element_index (
518 x_msg_error ,
519 x_return_status
520 );
521 END IF;
522 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
523 RAISE create_index_error;
524 END IF;
525 -- 4321268_eof
526
527 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
528 RAISE create_index_error;
529 END IF;
530 x_return_status := fnd_api.G_RET_STS_SUCCESS;
531
532 EXCEPTION
533 WHEN create_index_error THEN
534 NULL; -- x_msg_error is set in the do_create api.
535 WHEN others THEN
536 x_msg_error := 'Create_Element_Index: '
537 ||fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '||SQLERRM;
538 END Create_Element_Index;
539
540
541 /*
542 * Create_Soln_Cat_Index
543 * This PROCEDURE creates THE CATEGORY INDEX AND also populates THE INDEX
544 * content.
545 */
546 PROCEDURE Create_Soln_Cat_Index
547 ( pworker IN NUMBER DEFAULT 0,
548 x_msg_error OUT NOCOPY VARCHAR2,
549 x_return_status OUT NOCOPY VARCHAR2
550 )
551 IS
552 l_create_cmmd VARCHAR2(500):= NULL;
553 l_index_version VARCHAR2(15) := '115.10.1';
554 l_index_name VARCHAR2(30) := 'CS_KB_SOLN_CAT_TL_N1';
555
556 BEGIN
557 l_create_cmmd :=
558 ' CREATE INDEX '||g_cs_short_name||'.CS_KB_SOLN_CAT_TL_N1 on '
559 ||g_cs_short_name||'.cs_kb_soln_categories_tl(name) '
560 ||' INDEXTYPE IS ctxsys.context '
561 ||' parameters ('' '
562 ||' lexer '||g_apps_short_name||'.CS_KB_GLOBAL_LEXER language column SOURCE_LANG '
563 ||' wordlist '||g_apps_short_name||'.CS_KB_FUZZY_PREF '
564 --4321268
565 || ' storage ' ||g_apps_short_name||'.CS_KB_INDEX_STORAGE '; -- <-command not yet completed
566 -- 4321268_eof
567
568 x_return_status := fnd_api.G_RET_STS_ERROR;
569
570 -- 4321268
571 IF nvl(pworker,0) = 0 THEN
572 -- Create index online
573 -- 1. Create index without populate
574 l_create_cmmd := l_create_cmmd || ' nopopulate '') ';
575 ELSE
576
577 l_create_cmmd := l_create_cmmd || ''')';
578 l_create_cmmd := resolve_parallel_indexing(l_create_cmmd, pworker);
579
580 END IF;
581 -- 4321268_eof
582
583 do_create
584 ( p_create_cmd => l_create_cmmd,
585 p_index_name => l_index_name,
586 p_index_version => l_index_version,
587 x_msg_error => x_msg_error,
588 x_return_status => x_return_status
589 );
590
591 -- 4321268
592 IF nvl(pworker, 0) = 0 THEN
593 populate_soln_cat_index (
594 x_msg_error ,
595 x_return_status
596 );
597 END IF;
598 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
599 RAISE create_index_error;
600 END IF;
601 -- 4321268_eof
602 x_return_status := fnd_api.G_RET_STS_SUCCESS;
603
604 EXCEPTION
605 WHEN create_index_error THEN
606 NULL; -- x_msg_error is set in the do_create api.
607 WHEN others THEN
608 x_msg_error := 'Create_Element_Index: '
609 ||fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '||SQLERRM;
610 END Create_Soln_Cat_Index;
611
612 /*
613 * Create_Forum_Index
614 * This PROCEDURE creates THE forum INDEX AND also populates THE INDEX
615 * content.
616 */
617 PROCEDURE Create_Forum_Index
618 ( pworker IN NUMBER DEFAULT 0,
619 x_msg_error OUT NOCOPY VARCHAR2,
620 x_return_status OUT NOCOPY VARCHAR2
621 )
622 IS
623 l_create_cmmd VARCHAR2(500):= NULL;
624
625 l_index_version VARCHAR2(15) := '115.10.1';
626 l_index_name VARCHAR2(30) := 'cs_forum_messages_tl_n4';
627 BEGIN
628 l_create_cmmd :=
629 'create index '||g_cs_short_name||'.cs_forum_messages_tl_n4 '
630 || 'on '||g_cs_short_name||'.cs_forum_messages_tl(composite_assoc_col) '
631 || 'indextype is ctxsys.context parameters( '''
632 || 'datastore '||g_apps_short_name||'.CS_FORUM_MESG_ELES '
633 || ' section group '||g_apps_short_name||'.CS_KB_BASIC_GRP '
634 || 'lexer '||g_apps_short_name||'.CS_KB_GLOBAL_LEXER language column SOURCE_LANG '
635 || 'wordlist '||g_apps_short_name||'.CS_KB_FUZZY_PREF '
636 --4321268
637 || ' storage ' ||g_apps_short_name||'.CS_KB_INDEX_STORAGE '; -- <-command not yet completed
638 -- 4321268_eof
639
640 x_return_status := fnd_api.G_RET_STS_ERROR;
641
642 -- 4321268
643 IF nvl(pworker,0) = 0 THEN
644 -- Create index online
645 -- 1. Create index without populate
646 l_create_cmmd := l_create_cmmd || ' nopopulate '') ';
647 ELSE
648 l_create_cmmd := l_create_cmmd || ''')';
649 l_create_cmmd := resolve_parallel_indexing(l_create_cmmd, pworker);
650
651 END IF;
652 -- 4321268_eof
653
654 do_create
655 ( p_create_cmd => l_create_cmmd,
656 p_index_name => l_index_name,
657 p_index_version => l_index_version,
658 x_msg_error => x_msg_error,
659 x_return_status => x_return_status
660 );
661
662 -- 4321268
663 IF nvl(pworker, 0) = 0 THEN
664 populate_forum_index (
665 x_msg_error ,
666 x_return_status
667 );
668 END IF;
669 IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
670 RAISE create_index_error;
671 END IF;
672 -- 4321268_eof
673 x_return_status := fnd_api.G_RET_STS_SUCCESS;
674
675 EXCEPTION
676 WHEN create_index_error THEN
677 NULL; -- x_msg_error is set in the do_create api.
678 WHEN others THEN
679 x_msg_error := 'Create_Forum_Index: '
680 ||fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '||SQLERRM;
681 END Create_Forum_Index;
682
683
684
685 PROCEDURE Drop_Index
686 ( p_index_name IN VARCHAR,
687 x_msg_error OUT NOCOPY VARCHAR2,
688 x_return_status OUT NOCOPY VARCHAR2
689 )
690 IS
691 drop_index VARCHAR2(100) := NULL;
692
693 CURSOR get_index_cursor(p_index_name VARCHAR2, p_owner VARCHAR2) IS
694 SELECT COUNT(*) FROM dba_indexes
695 WHERE index_name = UPPER(p_index_name)
696 AND owner= UPPER(p_owner);
697
698 l_total NUMBER := 0;
699
700 BEGIN
701 x_return_status := fnd_api.G_RET_STS_ERROR;
702 IF p_index_name IS NULL THEN
703 RETURN;
704 END IF;
705
706 -- If only if the index exists:
707 OPEN get_index_cursor(p_index_name, g_cs_short_name);
708 FETCH get_index_cursor INTO l_total;
709 CLOSE get_index_cursor;
710
711 IF l_total > 0 THEN
712 drop_index := 'drop index '||g_cs_short_name||'.'||p_index_name||' force ';
713 EXECUTE IMMEDIATE drop_index;
714 END IF;
715
716 x_return_status := fnd_api.G_RET_STS_SUCCESS;
717
718 -- Logic to remove the index version in the
719 -- global system table.
720 EXCEPTION
721 WHEN others THEN
722 x_msg_error := 'Drop_Index: '||
723 fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '||
724 SQLERRM;
725 END Drop_Index;
726
727
728 /*
729 * Sync_Set_Index
730 * This PROCEDURE syncs THE Oracle Text INDEX FOR KM Solutions TO
731 * bring THE INDEX up-TO-DATE.
732 */
733 PROCEDURE Sync_Set_Index
734 ( errbuf OUT NOCOPY VARCHAR2,
735 retcode OUT NOCOPY NUMBER,
736 bmode IN VARCHAR2,
737 pworker IN NUMBER DEFAULT 0)
738 IS
739 CURSOR delay_marked_solns_batch_csr( c_batch_size NUMBER ) IS
740 SELECT set_id
741 FROM cs_kb_sets_b
742 -- 3679483
743 -- WHERE reindex_flag = 'Y'
744 WHERE reindex_flag = 'U'
745 -- 3679483 eof
746 AND ROWNUM <= c_batch_size;
747
748 l_solution_id NUMBER := 0;
749 l_soln_comp_index VARCHAR2(250) := 'CS_KB_SETS_TL_N3';
750 l_num_batch_rows_updated NUMBER := 0;
751 l_reindex_batch_size NUMBER := 300;
752 l_mode VARCHAR2(10) := bmode;
753
754 l_return_status VARCHAR2(1) := fnd_api.G_RET_STS_ERROR;
755 BEGIN
756
757 -- Initialize some variables
758 retcode := 2; -- init return val to FAIL
759
760 IF l_mode IS NULL THEN
761 l_mode := 'S';
762 END IF;
763
764 IF is_validate_mode(l_mode) = 'N' THEN
765 RAISE invalid_mode_error;
766 END IF;
767
768 -- check whether it is 'DR'
769 IF l_mode = 'DR' THEN
770 -- At this point we can assume that we can safely drop the index.
771 Drop_Index(l_soln_comp_index,
772 errbuf,
773 l_return_status);
774 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
775 RAISE drop_index_error;
776 END IF;
777
778 Create_Set_Index(pworker,
779 errbuf,
780 l_return_status);
781 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
782 RAISE create_index_error;
783 END IF;
784 ELSIF l_mode = 'RC' THEN
785 -- Rebuild content cache
786 Rebuild_Soln_Content_Cache( errbuf, retcode );
787 IF retcode <> 0 THEN
788 RAISE rebuild_cache_error;
789 END IF;
790 ELSE
791 fnd_profile.get('CS_KB_REINDEX_BATCH_SIZE', l_reindex_batch_size);
792 IF ( l_reindex_batch_size IS NULL ) THEN
793 l_reindex_batch_size := 300;
794 END IF;
795
796 -- Sync the composite solution index up-front.
797 -- This will bring any solutions already marked for reindexing
798 -- up-to date and make them searchable.
799 -- 4321268
800 IF (pworker IS NULL OR pworker = 0) AND l_mode = 'R' THEN
801 populate_set_index (
802 x_msg_error => errbuf,
803 x_return_status => l_return_status
804 );
805 Else
806 Sync_index( l_soln_comp_index, l_mode, PWORKER );
807 END IF;
808 -- 4321268_eof
809
810 -- Query up solutions that have been delay-marked for reindexing.
811 -- Loop through these solutions in batches (batch size defined by
812 -- profile option) and transfer the delay-mark to immediate mark.
813 -- After the mark transfer for each of these batches, sync the index
814 -- to make the batch of solutions searchable.
815 LOOP
816 l_num_batch_rows_updated := 0;
817
818 OPEN delay_marked_solns_batch_csr( l_reindex_batch_size );
819 LOOP
820 FETCH delay_marked_solns_batch_csr INTO l_solution_id;
821 -- Exit inner loop when there are no more delay-marked
822 -- statements in the batch
823 EXIT WHEN delay_marked_solns_batch_csr%NOTFOUND;
824
825 -- Immediately mark the solution composite text index column
826 UPDATE cs_kb_sets_tl
827 SET composite_assoc_index = 'U'
828 WHERE set_id = l_solution_id;
829
830 -- Clear the delayed index mark on the solution
831 UPDATE cs_kb_sets_b
832 SET reindex_flag = NULL
833 WHERE set_id = l_solution_id;
834
835 l_num_batch_rows_updated := l_num_batch_rows_updated + 1;
836 END LOOP;
837 CLOSE delay_marked_solns_batch_csr;
838 COMMIT;
839
840 -- Exit outer loop when there are no more rows to update
841 EXIT WHEN l_num_batch_rows_updated = 0;
842
843 -- Otherwise sync the index and loop again for the next batch
844 Sync_index( l_soln_comp_index, l_mode, PWORKER );
845
846 END LOOP;
847 END IF; -- l_mode check
848
849 -- klou (SRCHEFF)
850 -- Update magic word.
851 Update_Magic_Word;
852
853 -- Set return value and log message to Success
854 errbuf := fnd_message.get_string('CS', 'CS_KB_C_SUCCESS');
855 retcode := 0;
856
857 EXCEPTION
858 WHEN invalid_mode_error THEN
859 BEGIN
860 errbuf := fnd_message.get_string('CS', 'CS_KB_SYN_INDEX_INV_MODE');
861 END;
862 WHEN drop_index_error THEN
863 BEGIN
864 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
865 EXCEPTION
866 WHEN others THEN
867 NULL;
868 END;
869 WHEN create_index_error THEN
870 BEGIN
871 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
872 EXCEPTION
873 WHEN others THEN
874 NULL;
875 END;
876 WHEN others THEN
877 errbuf := 'Sync_Set_Index: '||
878 fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '||
879 SQLERRM;
880 -- Write out error to concurrent program log
881 BEGIN
882 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
883 EXCEPTION
884 WHEN others THEN
885 NULL;
886 END;
887
888 END Sync_Set_Index;
889
890
891
892 /*
893 * Sync_Element_Index
894 * This PROCEDURE syncs THE Oracle Text INDEX FOR KM Statements TO
895 * bring THE INDEX up-TO-DATE.
896 */
897 PROCEDURE Sync_Element_Index
898 ( ERRBUF OUT NOCOPY VARCHAR2,
899 RETCODE OUT NOCOPY NUMBER,
900 BMODE IN VARCHAR2,
901 pworker IN NUMBER DEFAULT 0)
902 IS
903 CURSOR delay_marked_stmts_batch_csr( c_batch_size NUMBER ) IS
904 SELECT element_id
905 FROM cs_kb_elements_b
906 -- 3679483
907 -- WHERE reindex_flag = 'Y'
908 WHERE reindex_flag = 'U'
909 -- 3679483 eof
910 AND ROWNUM <= c_batch_size;
911
912 l_statement_id NUMBER := 0;
913 l_statement_comp_index VARCHAR2(250) := 'CS_KB_ELEMENTS_TL_N2';
914 l_num_batch_rows_updated NUMBER := 0;
915 l_reindex_batch_size NUMBER := 300;
916 l_mode VARCHAR2(10) := bmode;
917 l_return_status VARCHAR2(1) := fnd_api.G_RET_STS_ERROR;
918 BEGIN
919 -- Initialize some variables
920 retcode := 2; -- init return val to FAIL
921
922 IF l_mode IS NULL THEN
923 l_mode := 'S';
924 END IF;
925
926 IF is_validate_mode(l_mode) = 'N' THEN
927 RAISE invalid_mode_error;
928 END IF;
929
930 -- check whether it is 'DR'
931 IF l_mode = 'DR' THEN
932 -- At this point we can assume that we can safely drop the index.
933 Drop_Index(l_statement_comp_index,
934 errbuf,
935 l_return_status);
936 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
937 RAISE drop_index_error;
938 END IF;
939
940 Create_Element_Index(pworker,
941 errbuf,
942 l_return_status);
943 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
944 RAISE create_index_error;
945 END IF;
946 ELSE
947 fnd_profile.get('CS_KB_REINDEX_BATCH_SIZE', l_reindex_batch_size);
948 IF ( l_reindex_batch_size IS NULL ) THEN
949 l_reindex_batch_size := 300;
950 END IF;
951
952 -- Sync the composite statement index up-front.
953 -- This will bring any statement already marked for reindexing
954 -- up-to date and make them searchable.
955 -- 4321268
956 IF (pworker IS NULL OR pworker = 0) AND l_mode = 'R' THEN
957 populate_element_index (
958 x_msg_error => errbuf,
959 x_return_status => l_return_status
960 );
961 ELSE
962 Sync_index( l_statement_comp_index, bmode, PWORKER );
963 END IF;
964 -- 4321268_eof
965 -- Query up statements that have been delay-marked for reindexing.
966 -- Loop through these statements in batches (batch size defined by
967 -- profile option) and transfer the delay-mark to immediate mark.
968 -- After the mark transfer for each of these batches, sync the index
969 -- to make the batch of statements searchable.
970 LOOP
971 l_num_batch_rows_updated := 0;
972
973 OPEN delay_marked_stmts_batch_csr( l_reindex_batch_size );
974 LOOP
975 FETCH delay_marked_stmts_batch_csr INTO l_statement_id;
976 -- Exit inner loop when there are no more delay-marked
977 -- statements in the batch
978 EXIT WHEN delay_marked_stmts_batch_csr%NOTFOUND;
979
980 -- Immediately mark the statement composite text index column
981 UPDATE cs_kb_elements_tl
982 SET composite_text_index = 'U'
983 WHERE element_id = l_statement_id;
984
985 -- Clear the delayed index mark on the statement
986 UPDATE cs_kb_elements_b
987 SET reindex_flag = NULL
988 WHERE element_id = l_statement_id;
989
990 l_num_batch_rows_updated := l_num_batch_rows_updated + 1;
991 END LOOP;
992 CLOSE delay_marked_stmts_batch_csr;
993 COMMIT;
994
995 -- Exit outer loop when there are no more rows to update
996 EXIT WHEN l_num_batch_rows_updated = 0;
997
998 -- Otherwise sync the index and loop again for the next batch
999 Sync_index( l_statement_comp_index, bmode );
1000
1001 END LOOP;
1002 END IF;
1003 -- Set return value and log message to Success
1004 errbuf := fnd_message.get_string('CS', 'CS_KB_C_SUCCESS');
1005 retcode := 0;
1006
1007 EXCEPTION
1008 WHEN invalid_mode_error THEN
1009 BEGIN
1010 errbuf := fnd_message.get_string('CS', 'CS_KB_SYN_INDEX_INV_MODE');
1011 END;
1012 WHEN drop_index_error THEN
1013 BEGIN
1014 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
1015 EXCEPTION
1016 WHEN others THEN
1017 NULL;
1018 END;
1019 WHEN create_index_error THEN
1020 BEGIN
1021 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
1022 EXCEPTION
1023 WHEN others THEN
1024 NULL;
1025 END;
1026 WHEN others THEN
1027 errbuf := 'Sync_Element_Index: '||
1028 fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '||
1029 SQLERRM;
1030 -- Write out error to concurrent program log
1031 BEGIN
1032 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
1033 EXCEPTION
1034 WHEN others THEN
1035 NULL;
1036 END;
1037 END Sync_Element_Index;
1038
1039
1040 PROCEDURE Sync_Forum_Index(ERRBUF OUT NOCOPY VARCHAR2,
1041 RETCODE OUT NOCOPY NUMBER,
1042 BMODE IN VARCHAR2,
1043 pworker IN NUMBER DEFAULT 0)
1044
1045 IS
1046 index3 VARCHAR2(250) := 'CS_FORUM_MESSAGES_TL_N4';
1047 l_mode VARCHAR2(10) := bmode;
1048 l_return_status VARCHAR2(1) := fnd_api.G_RET_STS_ERROR;
1049 BEGIN
1050 -- Initialize some variables
1051 retcode := 2; -- init return val to FAIL
1052
1053 IF l_mode IS NULL THEN
1054 l_mode := 'S';
1055 END IF;
1056
1057 IF is_validate_mode(l_mode) = 'N' THEN
1058 RAISE invalid_mode_error;
1059 END IF;
1060
1061 -- check whether it is 'DR'
1062 IF l_mode = 'DR' THEN
1063 -- At this point we can assume that we can safely drop the index.
1064 Drop_Index(index3,
1065 errbuf,
1066 l_return_status);
1067 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1068 RAISE drop_index_error;
1069 END IF;
1070
1071 Create_Forum_Index(pworker,
1072 errbuf,
1073 l_return_status);
1074 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1075 RAISE create_index_error;
1076 END IF;
1077 ELSE
1078 -- 4321268
1079 IF (pworker IS NULL OR pworker = 0) AND l_mode = 'R' THEN
1080 populate_forum_index (
1081 x_msg_error => errbuf,
1082 x_return_status => l_return_status
1083 );
1084 Else
1085 Sync_index( index3, l_mode, PWORKER );
1086 END IF;
1087 -- 4321268_eof
1088 END IF;
1089
1090 -- Return successfully
1091 errbuf := fnd_message.get_string('CS', 'CS_KB_C_SUCCESS');
1092 retcode := 0;
1093 EXCEPTION
1094 WHEN invalid_mode_error THEN
1095 BEGIN
1096 errbuf := fnd_message.get_string('CS', 'CS_KB_SYN_INDEX_INV_MODE');
1097 END;
1098 WHEN drop_index_error THEN
1099 BEGIN
1100 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
1101 EXCEPTION
1102 WHEN others THEN
1103 NULL;
1104 END;
1105 WHEN create_index_error THEN
1106 BEGIN
1107 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
1108 EXCEPTION
1109 WHEN others THEN
1110 NULL;
1111 END;
1112 WHEN others THEN
1113 errbuf := 'Sync_Forum_Index: '||
1114 fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '||
1115 SQLERRM;
1116 BEGIN
1117 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
1118 EXCEPTION
1119 WHEN others THEN
1120 NULL;
1121 END;
1122 END Sync_Forum_Index;
1123
1124 PROCEDURE Sync_Soln_Cat_Index(ERRBUF OUT NOCOPY VARCHAR2,
1125 RETCODE OUT NOCOPY NUMBER,
1126 BMODE IN VARCHAR2,
1127 pworker IN NUMBER DEFAULT 0)
1128 IS
1129 index1 VARCHAR2(250) := 'CS_KB_SOLN_CAT_TL_N1';
1130 l_mode VARCHAR2(10) := bmode;
1131 l_return_status VARCHAR2(1) := fnd_api.G_RET_STS_ERROR;
1132
1133 BEGIN
1134 retcode := 2;
1135
1136 IF l_mode IS NULL THEN
1137 l_mode := 'S';
1138 END IF;
1139
1140 IF is_validate_mode(l_mode) = 'N' THEN
1141 RAISE invalid_mode_error;
1142 END IF;
1143
1144 -- check whether it is 'DR'
1145 IF l_mode = 'DR' THEN
1146 -- At this point we can assume that we can safely drop the index.
1147 Drop_Index(index1,
1148 errbuf,
1149 l_return_status);
1150 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1151 RAISE drop_index_error;
1152 END IF;
1153
1154 Create_Soln_Cat_Index(pworker,
1155 errbuf,
1156 l_return_status);
1157 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
1158 RAISE create_index_error;
1159 END IF;
1160 ELSE
1161 -- 4321268
1162 IF (pworker IS NULL OR pworker = 0) AND l_mode = 'R' THEN
1163 populate_soln_cat_index (
1164 x_msg_error => errbuf,
1165 x_return_status => l_return_status
1166 );
1167 Else
1168 Sync_index( index1, l_mode, PWORKER );
1169 END IF;
1170 -- 4321268_eof
1171 END IF;
1172
1173 -- Return successfully
1174 errbuf := fnd_message.get_string('CS', 'CS_KB_C_SUCCESS');
1175 retcode := 0;
1176 EXCEPTION
1177 WHEN invalid_mode_error THEN
1178 BEGIN
1179 errbuf := fnd_message.get_string('CS', 'CS_KB_SYN_INDEX_INV_MODE');
1180 END;
1181 WHEN drop_index_error THEN
1182 BEGIN
1183 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
1184 EXCEPTION
1185 WHEN others THEN
1186 NULL;
1187 END;
1188 WHEN create_index_error THEN
1189 BEGIN
1190 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
1191 EXCEPTION
1192 WHEN others THEN
1193 NULL;
1194 END;
1195 WHEN others THEN
1196 errbuf := 'Sync_Soln_Cat_Index: '||
1197 fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '||
1198 SQLERRM;
1199 BEGIN
1200 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
1201 EXCEPTION
1202 WHEN others THEN
1203 NULL;
1204 END;
1205 END Sync_Soln_Cat_Index;
1206
1207 PROCEDURE del_sync_prog
1208 IS
1209 BEGIN
1210 fnd_program.delete_program ('CS_KB_SYNC_INDEX', 'CS');
1211 fnd_program.delete_executable ('CS_KB_SYNC_INDEX', 'CS');
1212 COMMIT;
1213 END del_sync_prog;
1214
1215
1216 PROCEDURE update_set_count_sum (ERRBUF OUT NOCOPY VARCHAR2,
1217 RETCODE OUT NOCOPY NUMBER)
1218 AS
1219
1220 TYPE list_of_def_id_type IS TABLE OF CS_KB_USED_SUM_DEFS_B.DEF_ID%TYPE
1221 INDEX BY BINARY_INTEGER;
1222 list_of_def_id list_of_def_id_type;
1223 TYPE list_of_days_type IS TABLE OF CS_KB_USED_SUM_DEFS_B.DAYS%TYPE
1224 INDEX BY BINARY_INTEGER;
1225 list_of_days list_of_days_type;
1226 i NUMBER(10);
1227 v_used_count CS_KB_SET_USED_SUMS.USED_COUNT%TYPE;
1228 current_date DATE;
1229 whether_exist NUMBER:=0;
1230 x_user_id NUMBER;
1231 x_login_id NUMBER;
1232
1233 CURSOR set_cursor IS
1234 SELECT SET_ID FROM CS_KB_SETS_B;
1235 BEGIN
1236 SELECT SysDate INTO current_date FROM dual;
1237 x_user_id := FND_GLOBAL.user_id;
1238 x_login_id := FND_GLOBAL.login_id;
1239
1240 SELECT def_id, days BULK COLLECT INTO list_of_def_id, list_of_days
1241 FROM CS_KB_USED_SUM_DEFS_B;
1242
1243 -- for each set
1244 FOR set_record IN set_cursor LOOP
1245
1246 -- for each used summary
1247 i:= list_of_def_id.FIRST;
1248 WHILE (i IS NOT NULL) LOOP
1249
1250 -- count
1251 SELECT count(H.HISTORY_ID) INTO v_used_count
1252 FROM CS_KB_HISTORIES_B H, CS_KB_SET_USED_HISTS USED_HISTS
1253 WHERE H.HISTORY_ID=USED_HISTS.HISTORY_ID AND
1254 USED_HISTS.SET_ID=set_record.set_id AND
1255 USED_HISTS.USED_TYPE=CS_KNOWLEDGE_PVT.G_PF AND
1256 ((current_date-H.entry_date)<=list_of_days(i));
1257
1258 IF(v_used_count> 0) THEN
1259
1260 -- insert or update to set_used_sum
1261 SELECT count(SET_ID) INTO whether_exist
1262 FROM CS_KB_SET_USED_SUMS
1263 WHERE SET_ID=set_record.SET_ID AND DEF_ID=list_of_def_id(i);
1264
1265 IF (whether_exist=0) THEN
1266
1267 INSERT INTO CS_KB_SET_USED_SUMS (
1268 SET_ID,
1269 DEF_ID,
1270 USED_COUNT,
1271 CREATION_DATE,
1272 CREATED_BY,
1273 LAST_UPDATE_DATE,
1274 LAST_UPDATED_BY,
1275 LAST_UPDATE_LOGIN)
1276 VALUES (
1277 set_record.set_id,
1278 list_of_def_id(i),
1279 v_used_count,
1280 current_date,
1281 x_user_id, --to_number(FND_PROFILE.VALUE ('USER_ID')),
1282 current_date,
1283 x_user_id, --to_number(FND_PROFILE.VALUE ('USER_ID')),
1284 x_login_id --to_number(FND_PROFILE.VALUE('LOGIN_ID'))
1285 );
1286
1287 ELSE
1288
1289 UPDATE CS_KB_SET_USED_SUMS SET
1290 USED_COUNT=v_used_count,
1291 LAST_UPDATE_DATE=current_date,
1292 LAST_UPDATED_BY=x_user_id, --to_number(FND_PROFILE.VALUE ('USER_ID')),
1293 LAST_UPDATE_LOGIN=x_login_id --to_number(FND_PROFILE.VALUE('LOGIN_ID'))
1294 WHERE set_id = set_record.set_id
1295 AND def_id = list_of_def_id(i);
1296 END IF;
1297
1298 ELSIF v_used_count = 0 THEN
1299
1300 DELETE FROM CS_KB_SET_USED_SUMS
1301 WHERE set_id = set_record.set_id
1302 AND def_id = list_of_def_id(i);
1303
1304 END IF;
1305
1306 i:=list_of_def_id.NEXT(i);
1307 END LOOP;
1308 END LOOP;
1309
1310 --clean up deleted summary definition entries
1311 DELETE FROM cs_kb_set_used_sums
1312 WHERE def_id NOT IN (SELECT def_id
1313 FROM cs_kb_used_sum_defs_b);
1314
1315 COMMIT;
1316 retcode := 0;
1317 END update_set_count_sum;
1318
1319
1320 -- klou (SRCHEFF), since 11.5.10
1321 /**
1322 * UPDATE THE magic word PROFILE.
1323 *
1324 */
1325 PROCEDURE Update_Magic_Word IS
1326 CURSOR Get_Magic_Word_Csr IS
1327 SELECT fnd_profile.value('CS_KB_SEARCH_NONEXIST_KEYWORD') FROM dual;
1328
1329 CURSOR Test_Magic_Word_Csr(p_keyword VARCHAR2) IS
1330 SELECT NULL
1331 FROM cs_kb_sets_vl SetEO
1332 WHERE
1333 contains(SetEO.composite_assoc_index, p_keyword, 10) >= 1
1334 AND ROWNUM < 2
1335 AND SetEO.status = 'PUB';
1336
1337 CURSOR Get_Random_Word_Csr IS
1338 /*
1339 dbms_random.string(opt => 'l', len => 8)
1340 different opt VALUES are:
1341 'u' -- upper case
1342 'l' -- lower case
1343 'a' -- alpha characters only (mixed case)
1344 'x' -- any alpha-numeric characters (upper)
1345 'p' -- any printable characters
1346 */
1347 SELECT dbms_random.string( 'l', 5) FROM dual;
1348
1349 l_magic_word VARCHAR2(240) := NULL;
1350 l_old_magic_word VARCHAR2(240) := NULL;
1351 l_result Test_Magic_Word_Csr%ROWTYPE;
1352
1353 BEGIN
1354 SAVEPOINT Update_Magic_Word_Sav;
1355 -- get magic word
1356 OPEN Get_Magic_Word_Csr;
1357 FETCH Get_Magic_Word_Csr INTO l_magic_word;
1358 CLOSE Get_Magic_Word_Csr;
1359
1360 IF l_magic_word IS NULL THEN
1361 OPEN Get_Random_Word_Csr;
1362 FETCH Get_Random_Word_Csr INTO l_magic_word;
1363 CLOSE Get_Random_Word_Csr;
1364 -- l_magic_word := 'xyxyz';
1365 END IF;
1366
1367 -- Backup l_magic_word
1368 l_old_magic_word := l_magic_word;
1369 --dbms_output.put_line('magic word is '||l_magic_word);
1370 LOOP
1371 OPEN Test_Magic_Word_Csr(l_magic_word);
1372 FETCH Test_Magic_Word_Csr INTO l_result;
1373 EXIT WHEN Test_Magic_Word_Csr%NOTFOUND;
1374 CLOSE Test_Magic_Word_Csr;
1375
1376 OPEN Get_Random_Word_Csr;
1377 FETCH Get_Random_Word_Csr INTO l_magic_word;
1378 CLOSE Get_Random_Word_Csr;
1379
1380 END LOOP;
1381
1382 IF Test_Magic_Word_Csr%ISOPEN THEN
1383 CLOSE Test_Magic_Word_Csr;
1384 END IF;
1385
1386 IF l_magic_word <> l_old_magic_word THEN
1387 -- Update profile
1388 IF Fnd_Profile.Save(
1389 X_NAME => 'CS_KB_SEARCH_NONEXIST_KEYWORD', /* Profile name you are setting */
1390 X_VALUE => l_magic_word, /* Profile value you are setting */
1391 X_LEVEL_NAME => 'SITE' /* 'SITE', 'APPL', 'RESP', or 'USER' */
1392 ) THEN
1393 COMMIT WORK;
1394 END IF;
1395 END IF;
1396
1397 EXCEPTION
1398 WHEN Others THEN
1399 ROLLBACK TO Update_Magic_Word_Sav;
1400 END Update_Magic_Word;
1401
1402
1403 PROCEDURE Update_Usage_Score(ERRBUF OUT NOCOPY VARCHAR2,
1404 RETCODE OUT NOCOPY NUMBER) AS
1405
1406 BEGIN
1407 SAVEPOINT Update_Usage_Score_Sav;
1408 Cs_Knowledge_Audit_Pvt.Update_Solution_Usage_Score(p_commit =>fnd_api.g_true);
1409 errbuf := fnd_message.get_string('CS', 'CS_KB_C_SUCCESS');
1410 retcode := 0;
1411 EXCEPTION
1412 WHEN OTHERS THEN
1413 ROLLBACK TO Update_Usage_Score_Sav;
1414 retcode := 2;
1415 errbuf := sqlerrm;
1416 BEGIN
1417 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
1418 EXCEPTION
1419 WHEN others THEN
1420 NULL;
1421 END;
1422
1423 END Update_Usage_Score;
1424
1425
1426 /*
1427 * Rebuild_Soln_Content_Cache
1428 * Repopulate THE solution content CACHE COLUMN FOR ALL published
1429 * solutions. Content CACHE entries will be commited IN batches.
1430 */
1431 PROCEDURE Rebuild_Soln_Content_Cache
1432 ( errbuf OUT nocopy VARCHAR2,
1433 retcode OUT nocopy NUMBER )
1434 IS
1435 CURSOR all_published_solutions IS
1436 SELECT tl.set_id
1437 FROM cs_kb_sets_tl tl, cs_kb_sets_b b
1438 WHERE b.set_id = tl.set_id
1439 AND b.status = 'PUB';
1440 TYPE solnIdListType IS TABLE OF cs_kb_sets_tl.set_id%TYPE INDEX BY BINARY_INTEGER;
1441 solnIdList solnIdListType;
1442 lCommitBatchSize NUMBER := 100;
1443 lCounter NUMBER := 0;
1444 BEGIN
1445 SAVEPOINT start_rebuild_cache;
1446
1447 -- Fetch out the list of IDs for all published solutions
1448 OPEN all_published_solutions;
1449 FETCH all_published_solutions BULK COLLECT INTO solnIdList;
1450 CLOSE all_published_solutions;
1451
1452 -- Loop through the solution id list and repopulate the content
1453 -- cache for each solution. Commit will be performed for every
1454 -- lCommitBatchSize repopulations performed.
1455 FOR i IN solnIdList.FIRST..solnIdList.LAST LOOP
1456 cs_kb_sync_index_pkg.populate_soln_content_cache(solnIdList(i));
1457 lCounter := lCounter + 1;
1458 IF ( lCounter = lCommitBatchSize ) THEN
1459 COMMIT;
1460 lCounter := 0;
1461 END IF;
1462 END LOOP;
1463 COMMIT;
1464 errbuf := fnd_message.get_string('CS', 'CS_KB_C_SUCCESS');
1465 retcode := 0;
1466 EXCEPTION
1467 WHEN others THEN
1468 ROLLBACK TO start_rebuild_cache;
1469 errbuf := 'Rebuild_Soln_Content_Cache: '||fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '||SQLERRM;
1470 -- Write out error to concurrent program log
1471 BEGIN
1472 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
1473 EXCEPTION
1474 WHEN others THEN
1475 NULL;
1476 END;
1477 END;
1478
1479 /*
1480 * Mark_Idx_on_Sec_Change
1481 * Mark text INDEX COLUMNS (solutions AND statements) WHEN KM
1482 * security setup changes. Marking THE text COLUMNS IS done OFF-line
1483 * IN a concurrent program TO give better UI response TIME.
1484 * THE way THE program works IS BY passing IN a security CHANGE
1485 * action TYPE code. FOR EACH action TYPE, there IS a LIST OF
1486 * PARAMETERS that get passed THROUGH parameter1-4.
1487 */
1488 PROCEDURE Mark_Idx_on_Sec_Change
1489 ( ERRBUF OUT NOCOPY VARCHAR2,
1490 RETCODE OUT NOCOPY NUMBER,
1491 SECURITY_CHANGE_ACTION_TYPE IN VARCHAR2 DEFAULT NULL,
1492 PARAMETER1 IN NUMBER DEFAULT NULL,
1493 PARAMETER2 IN NUMBER DEFAULT NULL )
1494 IS
1495 l_orig_visibility_id NUMBER := 0;
1496 l_orig_parent_category_id NUMBER := 0;
1497 l_visibility_id NUMBER := 0;
1498 l_category_id NUMBER := 0;
1499 l_cat_grp_id NUMBER := 0;
1500 BEGIN
1501 -- Initialize some variables
1502 retcode := ERROR; -- init return val to FAIL
1503
1504 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.get_string('CS', 'CS_KB_SYNC_IND_BEG')||' '|| 'Mark_Idx_on_Sec_Change');
1505
1506 -- Call out to appropriate helper function for the
1507 -- security setup change action type
1508 IF ( security_change_action_type = 'ADD_VIS' )
1509 THEN
1510 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.get_string('CS', 'CS_KB_SYNC_IND_PROC')||' '|| 'ADD_VIS');
1511 l_visibility_id := PARAMETER1;
1512 cs_kb_sync_index_pkg.Mark_Idx_on_Add_Vis( l_visibility_id );
1513 ELSIF ( security_change_action_type = 'REM_VIS' )
1514 THEN
1515 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.get_string('CS', 'CS_KB_SYNC_IND_PROC')||' '|| 'REM_VIS');
1516 l_visibility_id := PARAMETER1;
1517 cs_kb_sync_index_pkg.Mark_Idx_on_Rem_Vis( l_visibility_id );
1518 ELSIF ( security_change_action_type = 'CHANGE_CAT_VIS' )
1519 THEN
1520 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.get_string('CS', 'CS_KB_SYNC_IND_PROC')||' '|| 'CHANGE_CAT_VIS');
1521 l_category_id := PARAMETER1;
1522 l_orig_visibility_id := PARAMETER2;
1523 cs_kb_sync_index_pkg.Mark_Idx_on_Change_Cat_Vis( l_category_id, l_orig_visibility_id );
1524 ELSIF ( security_change_action_type = 'ADD_CAT_TO_CAT_GRP' )
1525 THEN
1526 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.get_string('CS', 'CS_KB_SYNC_IND_PROC')||' '|| 'ADD_CAT_TO_CAT_GRP');
1527 l_cat_grp_id := PARAMETER1;
1528 l_category_id := PARAMETER2;
1529 cs_kb_sync_index_pkg.Mark_Idx_on_Add_Cat_To_Cat_Grp( l_cat_grp_id, l_category_id );
1530 ELSIF ( security_change_action_type = 'REM_CAT_FROM_CAT_GRP' )
1531 THEN
1532 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.get_string('CS', 'CS_KB_SYNC_IND_PROC')||' '|| 'REM_CAT_FROM_CAT_GRP');
1533 l_cat_grp_id := PARAMETER1;
1534 l_category_id := PARAMETER2;
1535 cs_kb_sync_index_pkg.Mark_Idx_on_Rem_Cat_fr_Cat_Grp( l_cat_grp_id, l_category_id );
1536 ELSIF ( security_change_action_type = 'CHANGE_PARENT_CAT' )
1537 THEN
1538 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.get_string('CS', 'CS_KB_SYNC_IND_PROC')||' '|| 'CHANGE_PARENT_CAT');
1539 l_category_id := PARAMETER1;
1540 l_orig_parent_category_id := PARAMETER2;
1541 cs_kb_sync_index_pkg.Mark_Idx_on_Change_Parent_Cat( l_category_id, l_orig_parent_category_id );
1542 ELSE -- invalid action
1543
1544 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.get_string('CS', 'CS_KB_SYNC_IND_INV_TYPE'));
1545 -- 'Error: Invalid change security setup action type specified'
1546 RAISE invalid_action_error;
1547 END IF;
1548 COMMIT;
1549 -- Set return value and log message to Success
1550 FND_FILE.PUT_LINE(FND_FILE.LOG, fnd_message.get_string('CS', 'CS_KB_SYNC_SUCESS_END'));
1551 -- 'Successfully Completed.'
1552 errbuf := fnd_message.get_string('CS', 'CS_KB_C_SUCCESS');
1553 retcode := SUCCESS;
1554
1555 EXCEPTION
1556 WHEN invalid_action_error THEN
1557 BEGIN
1558 errbuf := fnd_message.get_string('CS', 'CS_KB_SYN_INDEX_INV_ACT');
1559 -- 'Invalid action specified'
1560 END;
1561 WHEN others THEN
1562 ROLLBACK;
1563 errbuf := 'Mark_Idx_on_Sec_Change: '||fnd_message.GET_STRING('CS','CS_KB_C_UNEXP_ERR')||' '||SQLERRM;
1564 -- Write out error to concurrent program log
1565 BEGIN
1566 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
1567 EXCEPTION
1568 WHEN others THEN
1569 NULL;
1570 END;
1571 END Mark_Idx_on_Sec_Change;
1572
1573 END CS_KB_CONC_PROG_PKG;
[Home] [Help]