[Home] [Help]
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';
307:
308: -- IMMEDIATE mark newly published solution and its
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;
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
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;
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
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
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:
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,
461: is
462: begin
463: -- IMMEDIATELY Mark all solutions of higher visibility position
464: -- than the removed visibility level
465: update cs_kb_sets_b
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
465: update cs_kb_sets_b
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
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:
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,
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
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
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
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
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;
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: */