[Home] [Help]
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
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;
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
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;
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
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
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:
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,
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
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
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:
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,
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
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
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
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
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;
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: */