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