1 PACKAGE BODY CS_KB_SYNC_INDEX_PKG AS
2 /* $Header: csksyncb.pls 120.0 2005/06/01 15:37:28 appldev noship $ */
3
4 -- ***********************************
5 -- Declarations for private procedures
6 -- ***********************************
7 -- bug 3359609
8 Sync_Set_Index_Error EXCEPTION;
9 Sync_Element_Index_Error EXCEPTION;
10
11 /*
12 * Immediate_Mark_Soln_And_Stmts
13 * Mark text index column dirty for reindexing for a solution
14 * version and all of its statements.
15 */
16 PROCEDURE Immediate_Mark_Soln_And_Stmts( p_solution_id number );
17
18 /*
19 * Immediate_Mark_Stmt_And_Solns
20 * Mark text index column dirty for reindexing for a statement
21 * and all of the solutions it is used in.
22 */
23 PROCEDURE Immediate_Mark_Stmt_And_Solns( p_statement_id number );
24
25 -- ********************************
26 -- Public Procedure Implementations
27 -- ********************************
28
29 /*
30 * Populate_Soln_Content_Cache
31 * Populate a solution's content cache, for a given
32 * language, with the cacheable synthesized text content
33 */
34 procedure Populate_Soln_Content_Cache
35 ( p_solution_id in number, p_lang in varchar2 )
36 is
37 l_cache clob;
38 l_content clob;
39 l_content_len number;
40 l_dest_pos number := 1;
41 l_src_pos number := 1;
42 begin
43 -- First fetch the existing content cache LOB locator
44 select content_cache into l_cache
45 from cs_kb_sets_tl
46 where set_id = p_solution_id and language = p_lang
47 for update;
48
49 -- If the LOB locator is null, we need to initialize one
50 if( l_cache is null ) then
51 -- populate cache LOB locator with an empty CLOB
52 update cs_kb_sets_tl
53 set content_cache = empty_clob()
54 where set_id = p_solution_id and language = p_lang;
55
56 -- re-fetch the cache lob locator
57 select content_cache into l_cache
58 from cs_kb_sets_tl
59 where set_id = p_solution_id and language = p_lang
60 for update;
61 end if;
62
63 -- Create a temporary CLOB and populate it with the synthesized
64 -- solution content, to be indexed
65 dbms_lob.createtemporary(l_content, TRUE);
66 cs_kb_ctx_pkg.synthesize_solution_content
67 ( p_solution_id, p_lang, l_content );
68
69 -- Copy the synthesized solution content into the content cache
70 -- through the LOB locator
71 l_content_len := dbms_lob.getlength(l_content);
72 dbms_lob.open(l_cache, DBMS_LOB.LOB_READWRITE);
73 dbms_lob.copy(l_cache, l_content, l_content_len, l_dest_pos, l_src_pos );
74 dbms_lob.close(l_cache);
75
76 -- Free up the temporary CLOB created earlier
77 dbms_lob.freetemporary(l_content);
78 end Populate_Soln_Content_Cache;
79
80
81 /*
82 * Populate_Soln_Content_Cache
83 * Populate a solution's content cache, for all installed
84 * languages, with the cacheable synthesized text content
85 */
86 procedure Populate_Soln_Content_Cache( p_solution_id in number )
87 is
88 l_language_code varchar2(4);
89
90 -- 01-Dec-2003 Removed into from cursor for 8.1.7 compliance
91 cursor installed_langs is
92 select language_code --into l_language_code
93 from fnd_languages where installed_flag in ('I','B');
94 begin
95 -- For each installed language, populate the solution content
96 -- cache CLOB
97 for language in installed_langs
98 loop
99 Populate_Soln_Content_Cache( p_solution_id, language.language_code );
100 end loop;
101 end Populate_Soln_Content_Cache;
102
103 /*
104 * Request_Sync_Index
105 * This procedure submits a concurrent request
106 * to sync KM indexes.
107 *
108 * Notes:
109 * As ad-hoc KM Sync-Index requests are submitted, we expect
110 * one to be running, and further requests to be pending.
111 * We only need ONE pending request, not a whole backlog. So
112 * here, were will check if there is already a pending request,
113 * simply don't submit another one.
114 * Note that when we check for pending requests, we should
115 * filter out SCHEDULED pending requests, which may not run
116 * for some time, depending on the schedule. We are detecting
117 * for pending requests that will get run as soon as possible.
118 */
119 PROCEDURE Request_Sync_KM_Indexes
120 ( x_request_id OUT NOCOPY NUMBER,
121 x_return_status OUT NOCOPY VARCHAR2 )
122 is
123 l_request_id NUMBER;
124 l_return_status VARCHAR2(1) := fnd_api.G_RET_STS_ERROR;
125 begin
126 -- bug 3359609
127 Request_Sync_Set_index(l_request_id,
128 l_return_status);
129 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS
130 THEN
131 RAISE Sync_Set_Index_Error;
132 END IF;
133
134 Request_Sync_Element_Index(l_request_id,
135 l_return_status);
136 IF l_return_status <> fnd_api.G_RET_STS_SUCCESS
137 THEN
138 RAISE Sync_Element_Index_Error;
139 END IF;
140 x_request_id := l_request_id;
141 x_return_status := l_return_status;
142
143 exception
144 when others then
145 x_request_id := 0;
146 x_return_status := fnd_api.G_RET_STS_ERROR;
147 end Request_Sync_KM_Indexes;
148
149
150 /*
151 * Request_Mark_Idx_on_Sec_Change
152 * This procedure submits a concurrent request
153 * to mark the solution and statement text indexes when
154 * KM security setup changes.
155 */
156 PROCEDURE Request_Mark_Idx_on_Sec_Change
157 ( p_security_change_action_type IN VARCHAR2,
158 p_parameter1 IN NUMBER default null,
159 p_parameter2 IN NUMBER default null,
160 x_request_id OUT NOCOPY NUMBER,
161 x_return_status OUT NOCOPY VARCHAR2 )
162 is
163 l_request_id NUMBER;
164 l_CS_appsname VARCHAR2(2) := 'CS';
165 l_mark_idx_progname VARCHAR2(30) := 'CS_KB_MARK_IDX_ON_SEC_CHG';
166 -- l_num_pending_requests NUMBER := 0;
167 l_return_status VARCHAR2(1) := fnd_api.G_RET_STS_ERROR;
168 begin
169
170 l_request_id :=
171 fnd_request.submit_request
172 ( application => l_CS_appsname,
173 program => l_mark_idx_progname,
174 description => null,
175 start_time => null,
176 sub_request => FALSE,
177 argument1 => p_security_change_action_type,
178 argument2 => p_parameter1,
179 argument3 => p_parameter2 );
180
181 if( l_request_id > 0 )
182 then
183 l_return_status := fnd_api.G_RET_STS_SUCCESS;
184 else
185 l_return_status := fnd_api.G_RET_STS_ERROR;
186 end if;
187
188 x_request_id := l_request_id;
189 x_return_status := l_return_status;
190 exception
191 when others then
192 x_request_id := 0;
193 x_return_status := fnd_api.G_RET_STS_ERROR;
194 end Request_Mark_Idx_on_Sec_Change;
195
196
197 /*
198 * Mark_Idxs_on_Pub_Soln
199 * Mark all appropriate text indexes after a solution is
200 * published.
201 */
202 PROCEDURE Mark_Idxs_on_Pub_Soln( p_solution_number varchar2 )
203 is
204 l_new_soln_id number;
205 l_prev_pub_soln_id number;
206
207 CURSOR Get_Out IS
208 select set_id --into l_prev_pub_soln_id
209 from cs_kb_sets_b
210 where set_number = p_solution_number
211 and status = 'OUT'
212 order by creation_date desc;
213
214 begin
215 -- First fetch the solution id for the newly
216 -- published version of this solution
217 select set_id into l_new_soln_id
218 from cs_kb_sets_b
219 where set_number = p_solution_number
220 and status = 'PUB';
221
222 -- IMMEDIATE mark newly published solution and its
223 -- statements for indexing
224 if( l_new_soln_id is not null )
225 then
226 Immediate_Mark_Soln_And_Stmts( l_new_soln_id );
227 end if;
228
229 -- Fetch the solution id for the previous published
230 -- version of this solution, if there is one
231 --select max(set_id) into l_prev_pub_soln_id
232 --from cs_kb_sets_b
233 --where set_number = p_solution_number
234 --and status = 'OUT';
235 -- BugFix 3993200 - Sequence Id Fix
236 OPEN Get_Out;
237 FETCH Get_Out INTO l_prev_pub_soln_id;
238 CLOSE Get_Out;
239
240 -- IMMEDIATE mark previous published solution and its
241 -- statements for indexing, if there is one
242 if( l_prev_pub_soln_id is not null )
243 then
244 Immediate_Mark_Soln_And_Stmts( l_prev_pub_soln_id );
245 end if;
246 end Mark_Idxs_on_Pub_Soln;
247
248 /*
249 * Mark_Idxs_on_Obs_Soln
250 * Mark all appropriate text indexes after a solution is
251 * obsoleted.
252 */
253 PROCEDURE Mark_Idxs_on_Obs_Soln( p_solution_number varchar2 )
254 is
255 l_new_soln_id number;
256 l_prev_pub_soln_id number;
257
258 CURSOR Get_Out IS
259 select set_id --into l_prev_pub_soln_id
260 from cs_kb_sets_b
261 where set_number = p_solution_number
262 and status = 'OUT'
263 order by creation_date desc;
264
265 begin
266 -- First fetch the solution id for the newly
267 -- obsoleted version of this solution
268 select set_id into l_new_soln_id
269 from cs_kb_sets_b
270 where set_number = p_solution_number
271 and status = 'OBS';
272
273 -- IMMEDIATE mark newly obsoleted solution and its
274 -- statements for indexing
275 if( l_new_soln_id is not null )
276 then
277 Immediate_Mark_Soln_And_Stmts( l_new_soln_id );
278 end if;
279
280 -- Fetch the solution id for the previous published
281 -- version of this solution, if there is one
282 --select max(set_id) into l_prev_pub_soln_id
283 --from cs_kb_sets_b
284 --where set_number = p_solution_number
285 --and status = 'OUT';
286 -- BugFix 3993200 - Sequence Id Fix
287 OPEN Get_Out;
288 FETCH Get_Out INTO l_prev_pub_soln_id;
289 CLOSE Get_Out;
290
291 -- IMMEDIATE mark previous published solution and its
292 -- statements for indexing, if there is one
293 if( l_prev_pub_soln_id is not null )
294 then
295 Immediate_Mark_Soln_And_Stmts( l_prev_pub_soln_id );
296 end if;
297 end Mark_Idxs_on_Obs_Soln;
298
299 /*
300 * Mark_Idxs_on_Global_Stmt_Upd
301 * Mark all appropriate text indexes after a global statement
302 * update is performed.
303 */
304 PROCEDURE Mark_Idxs_on_Global_Stmt_Upd( p_statement_id number )
305 is
306 begin
307 -- IMMEDIATE mark the statement and all of the solutions linked
308 -- to it.
309 Immediate_Mark_Stmt_And_Solns( p_statement_id );
310 end Mark_Idxs_on_Global_Stmt_Upd;
311
312
313 /*
314 * Mark_Idx_on_Add_Vis
315 * Mark Solution and Statement text indexes when a new visibility
316 * level is added.
317 */
318 PROCEDURE Mark_Idx_on_Add_Vis( p_added_vis_pos number )
319 is
320 begin
321 -- DELAYED Mark all solutions of higher visibility position
322 -- than the added visibility level
323 update cs_kb_sets_b
324 set reindex_flag = 'U'
325 where set_id in
326 ( select a.set_id
327 from cs_kb_sets_b a, cs_kb_visibilities_b b
328 where a.visibility_id = b.visibility_id
329 and b.position >= p_added_vis_pos );
330
331 -- DELAYED Mark all statements linked to solutions having a higher
332 -- visibility position than the added visibility level.
333 update cs_kb_elements_b
334 set reindex_flag = 'U'
335 where element_id in
336 ( select unique c.element_id
337 from cs_kb_sets_b a, cs_kb_visibilities_b b, cs_kb_set_eles c
338 where a.visibility_id = b.visibility_id
339 and a.set_id = c.set_id
340 and b.position >= p_added_vis_pos );
341
342 -- DELAYED Mark all solutions contained in categories having a higher
343 -- category visibility positions than the added visibility level
344 update cs_kb_sets_b
345 set reindex_flag = 'U'
346 where set_id in
347 ( select unique a.set_id
348 from cs_kb_set_categories a, cs_kb_soln_categories_b b,
349 cs_kb_visibilities_b c
350 where a.category_id = b.category_id
351 and b.visibility_id = c.visibility_id
352 and c.position >= p_added_vis_pos );
353
354 -- DELAYED Mark all statements linked to solutions contained
355 -- in categories having a higher category visibility positions than
356 -- the added visibility level
357 update cs_kb_elements_b
358 set reindex_flag = 'U'
359 where element_id in
360 ( select unique d.element_id
361 from cs_kb_set_categories a, cs_kb_soln_categories_b b,
362 cs_kb_visibilities_b c, cs_kb_set_eles d
363 where a.category_id = b.category_id
364 and a.set_id = d.set_id
365 and b.visibility_id = c.visibility_id
366 and c.position >= p_added_vis_pos );
367 end Mark_Idx_on_Add_Vis;
368
369 /*
370 * Mark_Idx_on_Rem_Vis
371 * Mark Solution and Statement text indexes when a visibility is
372 * removed.
373 */
374 PROCEDURE Mark_Idx_on_Rem_Vis( p_removed_vis_pos number )
375 is
376 begin
377 -- IMMEDIATELY Mark all solutions of higher visibility position
378 -- than the removed visibility level
379 update cs_kb_sets_b
380 set reindex_flag = 'U'
381 where set_id in
382 ( select a.set_id
383 from cs_kb_sets_b a, cs_kb_visibilities_b b
384 where a.visibility_id = b.visibility_id
385 and b.position >= p_removed_vis_pos );
386
387 -- IMMEDIATELY Mark all statements linked to solutions having a higher
388 -- visibility position than the removed visibility level.
389 update cs_kb_elements_b
390 set reindex_flag = 'U'
391 where element_id in
392 ( select unique c.element_id
393 from cs_kb_sets_b a, cs_kb_visibilities_b b, cs_kb_set_eles c
394 where a.visibility_id = b.visibility_id
395 and a.set_id = c.set_id
396 and b.position >= p_removed_vis_pos );
397
398 -- IMMEDIATELY Mark all solutions contained in categories having a higher
399 -- category visibility positions than the removed visibility level
400 update cs_kb_sets_b
401 set reindex_flag = 'U'
402 where set_id in
403 ( select unique a.set_id
404 from cs_kb_set_categories a, cs_kb_soln_categories_b b,
405 cs_kb_visibilities_b c
406 where a.category_id = b.category_id
407 and b.visibility_id = c.visibility_id
408 and c.position >= p_removed_vis_pos );
409
410 -- IMMEDIATELY Mark all statements linked to solutions contained
411 -- in categories having a higher category visibility positions than
412 -- the removed visibility level
413 update cs_kb_elements_b
414 set reindex_flag = 'U'
415 where element_id in
416 ( select unique d.element_id
417 from cs_kb_set_categories a, cs_kb_soln_categories_b b,
418 cs_kb_visibilities_b c, cs_kb_set_eles d
419 where a.category_id = b.category_id
420 and a.set_id = d.set_id
421 and b.visibility_id = c.visibility_id
422 and c.position >= p_removed_vis_pos );
423 end Mark_Idx_on_Rem_Vis;
424
425 /*
426 * Mark_Idx_on_Change_Cat_Vis
427 * Mark Solution and Statement text indexes when a Solution Category's
428 * visibility level changes.
429 */
430 PROCEDURE Mark_Idx_on_Change_Cat_Vis( p_cat_id number, p_orig_vis_id number )
431 is
432 l_orig_cat_vis_pos number;
433 l_new_cat_vis_pos number;
434 begin
435 -- Fetch the visibility position for the categories original visibility
436 -- and the categories new visibility
437 select position into l_orig_cat_vis_pos
438 from cs_kb_visibilities_b
439 where visibility_id = p_orig_vis_id;
440
441 select v.position into l_new_cat_vis_pos
442 from cs_kb_soln_categories_b c, cs_kb_visibilities_b v
443 where c.category_id = p_cat_id
444 and c.visibility_id = v.visibility_id;
445
446 -- Mark solutions and statements in DELAYED mode if the
447 -- category's visibility changed to a less secure level
448 if ( l_new_cat_vis_pos > l_orig_cat_vis_pos )
449 then
450 -- DELAYED Mark all solutions within the changed category
451 update cs_kb_sets_b
452 set reindex_flag = 'U'
453 where set_id in
454 ( select set_id
455 from cs_kb_set_categories
456 where category_id = p_cat_id );
457
458 -- DELAYED Mark all statements linked to the solutions
459 -- within the changed category
460 update cs_kb_elements_b
461 set reindex_flag = 'U'
462 where element_id in
463 ( select b.element_id
464 from cs_kb_set_categories a, cs_kb_set_eles b
465 where a.set_id = b.set_id
466 and a.category_id = p_cat_id );
467 elsif ( l_new_cat_vis_pos < l_orig_cat_vis_pos )
468 then
469 -- Else if the category's visibility changed to a more secure level,
470 -- IMMEDIATELY mark the solutions and statements
471
472 -- IMMEDIATELY Mark all solutions within the changed category
473 update cs_kb_sets_tl
474 set composite_assoc_index = 'U'
475 where set_id in
476 ( select set_id
477 from cs_kb_set_categories
478 where category_id = p_cat_id );
479
480 -- IMMEDIATELY Mark all statements linked to the solutions within the
481 -- changed category
482 update cs_kb_elements_tl
483 set composite_text_index = 'U'
484 where element_id in
485 ( select b.element_id
486 from cs_kb_set_categories a, cs_kb_set_eles b
487 where a.set_id = b.set_id
488 and a.category_id = p_cat_id );
489 else
490 -- Otherwise, the category visibilities have not change so do nothing.
491 null;
492 end if;
493 end Mark_Idx_on_Change_Cat_Vis;
494
495 /*
496 * Mark_Idx_on_Change_Parent_Cat
497 * Mark Solution and Statement text indexes when a Solution Category's
498 * parent category changes.
499 */
500 PROCEDURE Mark_Idx_on_Change_Parent_Cat( p_cat_id number, p_orig_parent_cat_id number )
501 is
502 cursor get_descendent_sets(cp_cat_id number) is
503 select b.set_id
504 from cs_kb_set_categories c, cs_kb_sets_b b
505 where c.category_id in
506 (
507 select category_id
508 from cs_kb_soln_categories_b
509 start with category_id = cp_cat_id
510 connect by prior category_id = Parent_category_id
511 )
512 and c.set_id = b.set_id
513 and b.status = 'PUB';
514 l_set_id number;
515
516 begin
517
518 -- IMMEDIATELY Mark all solutions under the changed category
519 open get_descendent_sets(p_cat_id);
520 loop
521 fetch get_descendent_sets into l_set_id;
522 exit when get_descendent_sets%notfound;
523 update cs_kb_sets_tl
524 set composite_assoc_index = 'U'
525 where set_id = l_set_id;
526 -- Update the content cache, cause the full path is now changed.
527 Populate_Soln_Content_Cache( l_set_id );
528 -- IMMEDIATELY Mark all statements linked to this solution.
529 update cs_kb_elements_tl
530 set composite_text_index = 'U'
531 where element_id in
532 ( select element_id
533 from cs_kb_set_eles
534 where set_id = l_set_id);
535 end loop;
536 close get_descendent_sets;
537
538 end Mark_Idx_on_Change_Parent_Cat;
539
540 /*
541 * Mark_Idxs_For_Multi_Soln
542 * Mark Solution and Statement text indexes when a Solution Category's
543 * parent category changes.
544 */
545 PROCEDURE Mark_Idxs_For_Multi_Soln( p_set_ids JTF_NUMBER_TABLE )
546 is
547 cursor is_published_soln (cp_set_id number) is
548 select set_id
549 from cs_kb_sets_b
550 where set_id = cp_set_id
551 and status = 'PUB';
552 l_set_id number;
553 begin
554 for i in 1..p_set_ids.count loop
555 -- Check if the solution referenced is not published yet.
556 l_set_id := null;
557 open is_published_soln(p_set_ids(i));
558 fetch is_published_soln into l_set_id;
559 close is_published_soln;
560
561 if(l_set_id is not null) then
562 -- IMMEDIATELY Mark this solution.
563 update cs_kb_sets_tl
564 set composite_assoc_index = 'U'
565 where set_id = l_set_id;
566
567 -- Update the content cache, cause the full path is now changed.
568 Populate_Soln_Content_Cache( l_set_id );
569
570 -- IMMEDIATELY Mark all statements linked to this solution.
571 update cs_kb_elements_tl
572 set composite_text_index = 'U'
573 where element_id in
574 ( select element_id
575 from cs_kb_set_eles
576 where set_id = l_set_id);
577 end if;
578 end loop;
579 end Mark_Idxs_For_Multi_Soln;
580 /*
581 * Mark_Idx_on_Add_Cat_To_Cat_Grp
582 * Mark Solution and Statement text indexes when a Category is
583 * added to a Category Group.
584 */
585 PROCEDURE Mark_Idx_on_Add_Cat_To_Cat_Grp( p_cat_grp_id number, p_cat_id number )
586 is
587 begin
588 -- DELAYED Mark all solutions in the removed category and
589 -- all of its subcategories, recursively
590 update cs_kb_sets_b
591 set reindex_flag = 'U'
592 where set_id in
593 ( select unique set_id
594 from cs_kb_set_categories
595 where category_id in
596 ( select category_id
597 from cs_kb_soln_categories_b
598 start with category_id = p_cat_id
599 connect by prior category_id = parent_category_id ));
600
601 -- DELAYED Mark all statements linked to all solutions in the
602 -- removed category and all of its subcategories, recursively
603 update cs_kb_elements_b
604 set reindex_flag = 'U'
605 where element_id in
606 ( select unique b.element_id
607 from cs_kb_set_categories a, cs_kb_set_eles b
608 where a.set_id = b.set_id
609 and a.category_id in
610 ( select category_id
611 from cs_kb_soln_categories_b
612 start with category_id = p_cat_id
613 connect by prior category_id = parent_category_id ));
614 end Mark_Idx_on_Add_Cat_To_Cat_Grp;
615
616 /*
617 * Mark_Idx_on_Rem_Cat_fr_Cat_Grp
618 * Mark Solution and Statement text indexes when a Category is
619 * removed from a Category Group.
620 */
621 PROCEDURE Mark_Idx_on_Rem_Cat_fr_Cat_Grp( p_cat_grp_id number, p_cat_id number )
622 is
623 begin
624 -- IMMEDIATELY Mark all solutions in the removed category
625 -- and all of its subcategories, recursively
626 update cs_kb_sets_tl
627 set composite_assoc_index = 'U'
628 where set_id in
629 ( select unique set_id
630 from cs_kb_set_categories
631 where category_id in
632 ( select category_id
633 from cs_kb_soln_categories_b
634 start with category_id = p_cat_id
635 connect by prior category_id = parent_category_id ));
636
637 -- IMMEDIATELY Mark all statements linked to all solutions in
638 -- the removed category and all of its subcategories, recursively
639 update cs_kb_elements_tl
640 set composite_text_index = 'U'
641 where element_id in
642 ( select unique b.element_id
643 from cs_kb_set_categories a, cs_kb_set_eles b
644 where a.set_id = b.set_id
645 and a.category_id in
646 ( select category_id
647 from cs_kb_soln_categories_b
648 start with category_id = p_cat_id
649 connect by prior category_id = parent_category_id ));
650 end Mark_Idx_on_Rem_Cat_fr_Cat_Grp;
651
652 -- *********************************
653 -- Private Procedure Implementations
654 -- *********************************
655
656 /*
657 * Immediate_Mark_Soln_And_Stmts
658 * Mark text index column dirty for reindexing for a solution
659 * version and all of its statements.
660 */
661 PROCEDURE Immediate_Mark_Soln_And_Stmts( p_solution_id number )
662 is
663 begin
664 -- IMMEDIATE Mark the solution version for indexing
665 update cs_kb_sets_tl
666 set composite_assoc_index = 'U'
667 where set_id = p_solution_id;
668
669 -- (3377135)
670 -- Update content cache
671 populate_soln_content_cache(p_solution_id);
672 -- end (3377135)
673
674 -- IMMEDIATE Mark all of the statements linked to the
675 -- solution version
676 update cs_kb_elements_tl
677 set composite_text_index = 'U'
678 where element_id in
679 ( select element_id from cs_kb_set_eles
680 where set_id = p_solution_id );
681 end;
682
683 /*
684 * Immediate_Mark_Stmt_And_Solns
685 * Mark text index column dirty for reindexing for a statement
686 * and all of the solutions it is used in.
687 */
688 PROCEDURE Immediate_Mark_Stmt_And_Solns( p_statement_id number )
689 is
690 --(3377135)
691 CURSOR get_related_sets (p_statement_id NUMBER)
692 IS
693 select se.set_id from cs_kb_set_eles se, cs_kb_sets_b sb
694 where se.element_id = p_statement_id
695 and se.set_id = sb.set_id
696 and sb.status = 'PUB';
697 TYPE list_set_ids IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
698 l_set_ids list_set_ids;
699 i NUMBER;
700 begin
701 -- IMMEDIATE Mark the statement for indexing
702 update cs_kb_elements_tl
703 set composite_text_index = 'U'
704 where element_id = p_statement_id;
705
706 --start (3377135)
707 -- update content cache
708 OPEN get_related_sets(p_statement_id);
709 FETCH get_related_sets BULK COLLECT INTO l_set_ids;
710 CLOSE get_related_sets;
711
712 i := l_set_ids.FIRST; -- (3580163)
713 while i is not null loop
714 populate_soln_content_cache(l_set_ids(i));
715
716 --Mark the solution for update
717 UPDATE cs_kb_sets_tl
718 set composite_assoc_index = 'U'
719 where set_id = l_set_ids(i);
720
721 i := l_set_ids.NEXT(i);
722 END LOOP;
723
724 /*
725 -- IMMEDIATE Mark all of the published solutions linked to the
726 -- statement
727 update cs_kb_sets_tl
728 set composite_assoc_index = 'U'
729 where set_id in
730 ( select se.set_id from cs_kb_set_eles se,
731 cs_kb_sets_b sb
732 where se.element_id = p_statement_id
733 and se.set_id = sb.set_id
734 and sb.status = 'PUB');
735 */
736 -- end (3377135)
737
738 end;
739
740 /*
741 * Request_Sync_Set_Index
742 * This procedure submits a concurrent request
743 * to sync KM set index.
744 */
745 PROCEDURE Request_Sync_Set_Index
746 ( x_request_id OUT NOCOPY NUMBER,
747 x_return_status OUT NOCOPY VARCHAR2 )
748 IS
749 l_request_id NUMBER;
750 l_CS_appsname VARCHAR2(2) := 'CS';
751 l_sync_idx_progname VARCHAR2(100) := 'CS_KB_SYNC_SOLUTIONS_INDEX';
752 l_sync_mode VARCHAR2(1) := 'S';
753 l_pending_phase_code VARCHAR2(1) := 'P';
754 l_num_pending_requests NUMBER := 0;
755 l_return_status VARCHAR2(1) := fnd_api.G_RET_STS_ERROR;
756 begin
757
758 -- Detect how many Pending, but not scheduled, KM Sync-Index
759 -- concurrent program requests there are.
760 select count(*)
761 into l_num_pending_requests
762 from fnd_concurrent_programs cp,
763 fnd_application ap,
764 fnd_concurrent_requests cr
765 where ap.application_short_name = l_CS_appsname
766 and cp.concurrent_program_name = l_sync_idx_progname
767 and cp.application_id = ap.application_id
768 and cr.concurrent_program_id = cp.concurrent_program_id
769 and cr.phase_code = l_pending_phase_code
770 and cr.requested_start_date <= sysdate;
771
772 -- If there are no unscheduled pending KM Sync-Index concurrent
773 -- requests, then submit one. Otherwise, since there is already
774 -- an unscheduled pending request, which will be run as soon as
775 -- possible, there is no need to submit another request.
776 if( l_num_pending_requests = 0 )
777 then
778 l_request_id :=
779 fnd_request.submit_request
780 ( application => l_CS_appsname,
781 program => l_sync_idx_progname,
782 description => null,
783 start_time => null,
784 sub_request => FALSE,
785 argument1 => l_sync_mode );
786
787 if( l_request_id > 0 )
788 then
789 l_return_status := fnd_api.G_RET_STS_SUCCESS;
790 end if;
791 else
792 -- There is already a pending request, so just return success
793 l_request_id := 0;
794 l_return_status := fnd_api.G_RET_STS_SUCCESS;
795 end if;
796
797 x_request_id := l_request_id;
798 x_return_status := l_return_status;
799 EXCEPTION
800 WHEN OTHERS THEN
801 x_request_id := 0;
802 x_return_status := fnd_api.G_RET_STS_ERROR;
803 END Request_Sync_Set_Index;
804
805
806 /*
807 * Request_Sync_Element_Index
808 * This procedure submits a concurrent request
809 * to sync KM element index.
810 */
811 PROCEDURE Request_Sync_Element_Index
812 ( x_request_id OUT NOCOPY NUMBER,
813 x_return_status OUT NOCOPY VARCHAR2 )
814 IS
815 l_request_id NUMBER;
816 l_CS_appsname VARCHAR2(2) := 'CS';
817 --l_sync_idx_progname VARCHAR2(16) := 'CS_KB_SYNC_INDEX';
818 -- Call new solution synchronization request
819 l_sync_idx_progname VARCHAR2(100) := 'CS_KB_SYNC_STATEMENTS_INDEX';
820 l_sync_mode VARCHAR2(1) := 'S';
821 l_pending_phase_code VARCHAR2(1) := 'P';
822 l_num_pending_requests NUMBER := 0;
823 l_return_status VARCHAR2(1) := fnd_api.G_RET_STS_ERROR;
824 BEGIN
825
826 -- Detect how many Pending, but not scheduled, KM Sync-Index
827 -- concurrent program requests there are.
828 select count(*)
829 into l_num_pending_requests
830 from fnd_concurrent_programs cp,
831 fnd_application ap,
832 fnd_concurrent_requests cr
833 where ap.application_short_name = l_CS_appsname
834 and cp.concurrent_program_name = l_sync_idx_progname
835 and cp.application_id = ap.application_id
836 and cr.concurrent_program_id = cp.concurrent_program_id
837 and cr.phase_code = l_pending_phase_code
838 and cr.requested_start_date <= sysdate;
839
840 -- If there are no unscheduled pending KM Sync-Index concurrent
841 -- requests, then submit one. Otherwise, since there is already
842 -- an unscheduled pending request, which will be run as soon as
843 -- possible, there is no need to submit another request.
844 if( l_num_pending_requests = 0 )
845 then
846 l_request_id :=
847 fnd_request.submit_request
848 ( application => l_CS_appsname,
849 program => l_sync_idx_progname,
850 description => null,
851 start_time => null,
852 sub_request => FALSE,
853 argument1 => l_sync_mode );
854
855 if( l_request_id > 0 )
856 then
857 l_return_status := fnd_api.G_RET_STS_SUCCESS;
858 end if;
859 else
860 -- There is already a pending request, so just return success
861 l_request_id := 0;
862 l_return_status := fnd_api.G_RET_STS_SUCCESS;
863 end if;
864
865 x_request_id := l_request_id;
866 x_return_status := l_return_status;
867 EXCEPTION
868 WHEN OTHERS THEN
869 x_request_id := 0;
870 x_return_status := fnd_api.G_RET_STS_ERROR;
871 END Request_Sync_Element_Index;
872
873
874 end CS_KB_SYNC_INDEX_PKG;
[Home] [Help]