create or replace package body library as procedure copy ( src_package_id in apm_packages.package_id%TYPE, dst_package_id in apm_packages.package_id%TYPE, creation_user in acs_objects.creation_user%TYPE, creation_ip in acs_objects.creation_ip%TYPE ) is begin object_type.copy_all_object_types ( src_package_id => src_package_id, dst_package_id => dst_package_id, creation_user => creation_user, creation_ip => creation_ip ); psn_res_application.copy ( src_package_id => src_package_id, dst_package_id => dst_package_id, creation_user => creation_user, creation_ip => creation_ip ); end copy; end library; / show errors create or replace package body object_type as procedure copy_all_object_types ( src_package_id in acs_objects.context_id%TYPE, dst_package_id in acs_objects.context_id%TYPE, creation_user in acs_objects.creation_user%TYPE, creation_ip in acs_objects.creation_ip%TYPE ) is cursor c_questions (src_package_id integer) is select question_id, pretty_name, abstract_data_type, presentation_type, order_by, entry_explanation, tag_width, tag_height, help_text, target_object_type_id, references_question_id, tree_id, node_id, category_id, year_from, year_to, default_value, max_categories, defaults_question_id from sn_questions where question_id in (select map.question_id from sn_question_object_type_map map, sn_object_types ot where map.object_type_id = ot.object_type_id and ot.context_id = src_package_id); cursor c_object_types (src_package_id integer) is select object_type_id, short_name, pretty_name, pretty_plural, graphic, graphic_p, deleted_p, browse_p, public_p, create_p, sort_key, q1.new_id as new_short_description, q2.new_id as new_long_description, q3.new_id as new_public_until, q4.new_id as new_linked_question_id, default_age_filter, dst_package_id, archive_p, copy_p, sweeper, sweeper_action, sweeper_warning_time, sweeper_outdated_time, start_date, end_date from sn_object_types ot, km_temp_questions q1, km_temp_questions q2, km_temp_questions q3, km_temp_questions q4 where ot.context_id = src_package_id and q1.old_id(+) = ot.short_description and q2.old_id(+) = ot.long_description and q3.old_id(+) = ot.public_until and q4.old_id(+) = ot.linked_question_id; cursor c_answer_options is select option_id, tq.new_id as new_question_id, answer_option, sort_key from sn_answer_options ao, km_temp_questions tq where ao.question_id = tq.old_id; v_new_question_id sn_questions.question_id%type; v_new_object_type_id sn_object_types.object_type_id%type; v_new_answer_option_id sn_answer_options.option_id%type; v_count integer; v_new_target_object_type_id integer; begin -- Clean up data from previous call in this transaction. delete from km_temp_questions; delete from km_temp_object_types; delete from km_temp_answer_options; -- copy the questions for c1 in c_questions(src_package_id) loop v_new_question_id := question.insert_question ( v_pretty_name => c1.pretty_name, v_abstract_data_type => c1.abstract_data_type, v_order_by => c1.order_by, v_default_value => c1.default_value, v_entry_explanation => c1.entry_explanation, v_creation_user => creation_user, v_creation_ip => creation_ip, v_references_q_id => null); update sn_questions set presentation_type=c1.presentation_type, tag_width=c1.tag_width, tag_height = c1.tag_height, help_text = c1.help_text, tree_id = c1.tree_id, node_id = c1.node_id, category_id = c1.category_id, year_from = c1.year_from, year_to = c1.year_to, target_object_type_id = c1.target_object_type_id, max_categories = c1.max_categories, defaults_question_id = c1.defaults_question_id where question_id=v_new_question_id; insert into km_temp_questions (old_id, new_id) values (c1.question_id, v_new_question_id); end loop; -- correct references_question_id update sn_questions q set references_question_id = (select new_id from km_temp_questions where old_id = q.references_question_id ) where question_id in (select old_id from km_temp_questions); -- correct defaults_question_id update sn_questions q set defaults_question_id = (select new_id from km_temp_questions where old_id = q.defaults_question_id ) where question_id in (select old_id from km_temp_questions); for c2 in c_object_types(src_package_id) loop -- create the new object type v_new_object_type_id := acs_object.new ( object_type => 'sn_object_type', creation_date => sysdate, creation_user => creation_user, creation_ip => creation_ip, context_id => dst_package_id ); insert into sn_object_types (object_type_id, short_name, pretty_name, pretty_plural, graphic, graphic_p, deleted_p, browse_p, public_p, create_p, sort_key, short_description, long_description, public_until, linked_question_id, default_age_filter, context_id, archive_p, copy_p, sweeper, sweeper_action, sweeper_warning_time, sweeper_outdated_time, start_date, end_date) values (v_new_object_type_id, c2.short_name, c2.pretty_name, c2.pretty_plural, c2.graphic, c2.graphic_p, c2.deleted_p, c2.browse_p, c2.public_p, c2.create_p, c2.sort_key, c2.new_short_description, c2.new_long_description, c2.new_public_until, c2.new_linked_question_id, c2.default_age_filter, c2.dst_package_id, c2.archive_p, c2.copy_p, c2.sweeper, c2.sweeper_action, c2.sweeper_warning_time, c2.sweeper_outdated_time, c2.start_date, c2.end_date); insert into km_temp_object_types (old_id, new_id) values (c2.object_type_id, v_new_object_type_id); end loop; -- now map the shortname for c3 in (select tot.new_id as object_type_id, tq.new_id as short_description, map.position from sn_types_map_short_name map, km_temp_questions tq, km_temp_object_types tot where map.short_description = tq.old_id and map.object_type_id = tot.old_id) loop begin insert into sn_types_map_short_name (object_type_id, short_description, position) values (c3.object_type_id, c3.short_description, c3.position ); exception when others then null; end; end loop; -- map the answer options for c4 in c_answer_options loop select acs_object_id_seq.nextval into v_new_answer_option_id from dual; insert into sn_answer_options (option_id, question_id, answer_option, sort_key) values (v_new_answer_option_id, c4.new_question_id, c4.answer_option, c4.sort_key); insert into km_temp_answer_options (old_id, new_id) values (c4.option_id, v_new_answer_option_id); end loop; -- -50 is the dummy question id insert into km_temp_questions values (-50,-50); -- and now map the frigging questions to the more frigging object type for c5 in (select tq1.new_id as new_question_id, tot.new_id as new_object_type_id, sort_key, form_number, mandatory_p, question_state, tq2.new_id as new_parent_question_id, branch_p, branch_operator, branch_answer, tao.new_id as new_branch_answer_foreign_key, tq3.new_id as new_default_branch from sn_question_object_type_map map, km_temp_questions tq1, km_temp_questions tq2, km_temp_questions tq3, km_temp_answer_options tao, km_temp_object_types tot where map.question_id = tq1.old_id and map.object_type_id = tot.old_id and map.parent_question_id = tq2.old_id(+) and map.default_branch = tq3.old_id(+) and map.branch_answer_foreign_key = tao.old_id(+)) loop begin insert into sn_question_object_type_map (question_id, object_type_id, sort_key, form_number, mandatory_p, question_state, parent_question_id, branch_p, branch_operator, branch_answer, branch_answer_foreign_key, default_branch) values (c5.new_question_id, c5.new_object_type_id, c5.sort_key, c5.form_number, c5.mandatory_p, c5.question_state, c5.new_parent_question_id, c5.branch_p, c5.branch_operator, c5.branch_answer, c5.new_branch_answer_foreign_key, c5.new_default_branch); exception when others then null; end; end loop; -- and correct the target object type ids for c6 in ( select q2.target_object_type_id, q1.question_id from sn_questions q1, sn_questions q2, km_temp_questions tq where q1.question_id = tq.new_id and q2.question_id = tq.old_id and q2.target_object_type_id is not null and q1.question_id in (select new_id from km_temp_questions) ) loop select new_id into v_new_target_object_type_id from km_temp_object_types tot where tot.old_id = c6.target_object_type_id; update sn_questions q set target_object_type_id = v_new_target_object_type_id where question_id = c6.question_id; end loop; end copy_all_object_types; procedure copy_object_type ( v_object_type_id in sn_object_types.object_type_id%TYPE, v_target_context_id in acs_objects.context_id%TYPE, v_creation_user in acs_objects.creation_user%TYPE, v_creation_ip in acs_objects.creation_ip%TYPE ) is cursor c_questions (v_object_type_id integer) is select question_id, pretty_name, abstract_data_type, presentation_type, order_by, entry_explanation, tag_width, tag_height, help_text, target_object_type_id, references_question_id, tree_id, node_id, category_id, year_from, year_to, default_value, max_categories, defaults_question_id from sn_questions where question_id in (select map.question_id from sn_question_object_type_map map where map.object_type_id = v_object_type_id); cursor c_answer_options is select option_id, tq.new_id as new_question_id, answer_option, sort_key from sn_answer_options ao, km_temp_questions tq where ao.question_id = tq.old_id; v_new_question_id sn_questions.question_id%TYPE; v_new_object_type_id sn_object_types.object_type_id%TYPE; v_new_answer_option_id sn_answer_options.option_id%TYPE; v_count integer; begin -- Clean up data from previous call in this transaction. delete from km_temp_questions; delete from km_temp_answer_options; -- copy the questions -- target_object_type_id has to be nullified as it doesn't exist -- in the target community for c1 in c_questions(v_object_type_id) loop v_new_question_id := question.insert_question ( v_pretty_name => c1.pretty_name, v_abstract_data_type => c1.abstract_data_type, v_order_by => c1.order_by, v_default_value => c1.default_value, v_entry_explanation => c1.entry_explanation, v_creation_user => v_creation_user, v_creation_ip => v_creation_ip, v_references_q_id => null); update sn_questions set presentation_type=c1.presentation_type, tag_width=c1.tag_width, tag_height = c1.tag_height, help_text = c1.help_text, tree_id = c1.tree_id, node_id = c1.node_id, category_id = c1.category_id, year_from = c1.year_from, year_to = c1.year_to, max_categories = c1.max_categories, defaults_question_id = c1.defaults_question_id where question_id=v_new_question_id; insert into km_temp_questions (old_id, new_id) values (c1.question_id, v_new_question_id); end loop; -- correct references_question_id update sn_questions q set references_question_id = (select new_id from km_temp_questions where old_id = q.references_question_id ) where question_id in (select old_id from km_temp_questions); -- correct defaults_question_id update sn_questions q set defaults_question_id = (select new_id from km_temp_questions where old_id = q.defaults_question_id ) where question_id in (select old_id from km_temp_questions); -- create the new object type v_new_object_type_id := acs_object.new ( object_type => 'sn_object_type', creation_date => sysdate, creation_user => v_creation_user, creation_ip => v_creation_ip, context_id => v_target_context_id ); insert into sn_object_types (object_type_id, short_name, pretty_name, pretty_plural, graphic, graphic_p, deleted_p, browse_p, public_p, create_p, sort_key, short_description, long_description, public_until, linked_question_id, default_age_filter, context_id, archive_p, copy_p, sweeper, sweeper_action, sweeper_warning_time, sweeper_outdated_time, start_date, end_date) (select v_new_object_type_id, short_name, pretty_name, pretty_plural, graphic, graphic_p, deleted_p, browse_p, public_p, create_p, sort_key, q1.new_id as new_short_description, q2.new_id as new_long_description, q3.new_id as new_public_until, q4.new_id as new_linked_question_id, default_age_filter, v_target_context_id, archive_p, copy_p, sweeper, sweeper_action, sweeper_warning_time, sweeper_outdated_time, start_date, end_date from sn_object_types ot, km_temp_questions q1, km_temp_questions q2, km_temp_questions q3, km_temp_questions q4 where ot.object_type_id = v_object_type_id and q1.old_id(+) = ot.short_description and q2.old_id(+) = ot.long_description and q3.old_id(+) = ot.public_until and q4.old_id(+) = ot.linked_question_id); -- now map the shortname insert into sn_types_map_short_name (object_type_id, short_description, position ) (select v_new_object_type_id, tq.new_id, map.position from sn_types_map_short_name map, km_temp_questions tq where map.short_description = tq.old_id); -- map the answer options for c3 in c_answer_options loop select acs_object_id_seq.nextval into v_new_answer_option_id from dual; insert into sn_answer_options (option_id, question_id, answer_option, sort_key) values (v_new_answer_option_id, c3.new_question_id, c3.answer_option, c3.sort_key); insert into km_temp_answer_options (old_id, new_id) values (c3.option_id, v_new_answer_option_id); end loop; -- -50 is the dummy question id insert into km_temp_questions values (-50,-50); -- and now map the frigging questions to the more frigging object type insert into sn_question_object_type_map (question_id, object_type_id, sort_key, form_number, mandatory_p, question_state, parent_question_id, branch_p, branch_operator, branch_answer, branch_answer_foreign_key, default_branch) (select tq1.new_id as new_question_id, v_new_object_type_id, sort_key, form_number, mandatory_p, question_state, tq2.new_id as new_parent_question_id, branch_p, branch_operator, branch_answer, tao.new_id as new_branch_answer_foreign_key, tq3.new_id as new_default_branch from sn_question_object_type_map map, km_temp_questions tq1, km_temp_questions tq2, km_temp_questions tq3, km_temp_answer_options tao where map.question_id = tq1.old_id and map.parent_question_id = tq2.old_id(+) and map.default_branch = tq3.old_id(+) and map.branch_answer_foreign_key = tao.old_id(+)); end copy_object_type; procedure delete_all_object_types ( v_context_id in acs_objects.context_id%TYPE default null ) is cursor c_delete_all_object_types is select object_type_id from sn_object_types where context_id = v_context_id; begin for v_delete_all_object_types in c_delete_all_object_types loop object_type.delete_object_type(v_delete_all_object_types.object_type_id); end loop; end delete_all_object_types; function insert_object_type ( v_pretty_name in sn_object_types.pretty_name%TYPE, v_pretty_plural in sn_object_types.pretty_plural%TYPE, v_graphic in sn_object_types.graphic%TYPE, v_deleted_p in sn_object_types.deleted_p%TYPE, v_browse_p in sn_object_types.browse_p%TYPE, v_public_p in sn_object_types.public_p%TYPE, v_sort_key in sn_object_types.sort_key%TYPE, v_short_description in sn_object_types.short_description%TYPE, v_long_description in sn_object_types.long_description%TYPE, v_linked_question_id in sn_object_types.linked_question_id%TYPE, v_public_until in sn_object_types.public_until%TYPE, v_default_age_filter in sn_object_types.default_age_filter%TYPE, v_creation_user in acs_objects.creation_user%TYPE default null, v_creation_ip in acs_objects.creation_ip%TYPE default null, v_context_id in acs_objects.context_id%TYPE, v_short_name in sn_object_types.short_name%TYPE default null, v_insertion_date in acs_objects.creation_date%TYPE default sysdate ) return sn_object_types.object_type_id%TYPE is v_return_object_type_id acs_objects.object_id%TYPE; begin v_return_object_type_id := acs_object.new ( object_type => 'sn_object_type', creation_date => v_insertion_date, creation_user => v_creation_user, creation_ip => v_creation_ip, context_id => v_context_id ); insert into sn_object_types (object_type_id,pretty_name, pretty_plural, graphic, deleted_p, browse_p, public_p, sort_key, long_description, public_until, default_age_filter,context_id, short_name ) values (v_return_object_type_id,v_pretty_name, v_pretty_plural, v_graphic, v_deleted_p, v_browse_p, v_public_p, v_sort_key, v_long_description, v_public_until, v_default_age_filter, v_context_id, v_short_name); -- this function is _only_ used by the migration scripts. In the past -- we didn't have composite short descriptions. Hence the hack here is -- more than okay. if v_short_description is not null then insert into sn_types_map_short_name (object_type_id, short_description, position) values (v_return_object_type_id, v_short_description, 1); end if; return v_return_object_type_id; end insert_object_type; function insert_object_type ( v_pretty_name in sn_object_types.pretty_name%TYPE, v_pretty_plural in sn_object_types.pretty_plural%TYPE, v_browse_p in sn_object_types.browse_p%TYPE, v_default_age_filter in sn_object_types.default_age_filter%TYPE, v_creation_user in acs_objects.creation_user%TYPE default null, v_creation_ip in acs_objects.creation_ip%TYPE default null, v_context_id in acs_objects.context_id%TYPE default null, v_short_name in sn_object_types.short_name%TYPE default null, v_insertion_date in acs_objects.creation_date%TYPE default sysdate ) return sn_object_types.object_type_id%TYPE is v_return_object_type_id acs_objects.object_id%TYPE; begin v_return_object_type_id := acs_object.new ( object_type => 'sn_object_type', creation_date => v_insertion_date, creation_user => v_creation_user, creation_ip => v_creation_ip, context_id => v_context_id ); insert into sn_object_types (object_type_id,pretty_name, pretty_plural, browse_p, default_age_filter, context_id, short_name ) values (v_return_object_type_id,v_pretty_name, v_pretty_plural, v_browse_p, v_default_age_filter, v_context_id, v_short_name); return v_return_object_type_id; end insert_object_type; procedure delete_object_type ( v_object_type_id in sn_object_types.object_type_id%TYPE ) is cursor cur_question_object_type_map is select question_id from sn_question_object_type_map where object_type_id = v_object_type_id; begin object.delete_all_objects_of_a_type(v_object_type_id); update sn_object_types set long_description = null, linked_question_id = null, public_until = null where object_type_id = v_object_type_id; update sn_questions set target_object_type_id = null where target_object_type_id = v_object_type_id; for v_question_object_type_map in cur_question_object_type_map loop question.delete_question(v_question_object_type_map.question_id); end loop; delete from sn_types_map_short_name where object_type_id = v_object_type_id; delete from sn_table_name_map where object_type_id = v_object_type_id; delete from acs_permissions where object_id = v_object_type_id; acs_object.delete(v_object_type_id); delete from sn_object_types where object_type_id=v_object_type_id; end delete_object_type; end object_type; / show errors -- @cvs-id $Id: library-package-bodies.sql,v 1.1.1.1 2003/07/02 12:19:42 peterm Exp $ -- turn off sql plus variables substitution set define off create or replace package body object as function name ( object_id in sn_objects.object_id%TYPE ) return varchar2 is v_result sn_objects.one_line_description%TYPE; begin select nvl(one_line_description,'Unnamed ' || ot.pretty_name) into v_result from sn_objects o, sn_object_types ot where o.object_type_id = ot.object_type_id and o.object_id = name.object_id; return v_result; end name; procedure save_content ( v_object_id in sn_objects.object_id%TYPE, v_question_id in sn_questions.question_id%TYPE, v_html_p in sn_content.html_p%TYPE, v_content_length in integer, v_modifying_user in acs_objects.creation_user%TYPE, v_modifying_ip in acs_objects.modifying_ip%TYPE, v_update_date in acs_objects.last_modified%TYPE default sysdate ) is v_count_exists integer default 0; begin update sn_objects set last_modified = v_update_date, last_modifying_user_id = v_modifying_user where object_id = v_object_id; update acs_objects_description set datastore = 'a' where object_id = v_object_id; acs_object.last_modified ( v_object_id => v_object_id, v_modifying_user => v_modifying_user, v_modifying_ip => v_modifying_ip, v_last_modified => v_update_date); select count(*) into v_count_exists from sn_content where object_id=v_object_id and question_id=v_question_id; if v_count_exists = 0 then insert into sn_content (object_id, question_id, html_p) values (v_object_id, v_question_id, v_html_p); else if v_content_length = 0 then delete from sn_content where object_id=v_object_id and question_id=v_question_id; else update sn_content set html_p = v_html_p where object_id=v_object_id and question_id=v_question_id; end if; end if; end save_content; function insert_object ( v_object_type_id in sn_objects.object_type_id%TYPE, v_creation_user in acs_objects.creation_user%TYPE default null, v_creation_ip in acs_objects.creation_ip%TYPE default null, v_context_id in acs_objects.context_id%TYPE default null, v_insertion_date in acs_objects.creation_date%TYPE default sysdate ) return sn_objects.object_id%TYPE is v_return_object_id acs_objects.object_id%TYPE; begin v_return_object_id := acs_object.new ( object_type => 'sn_object', creation_date => v_insertion_date, creation_user => v_creation_user, creation_ip => v_creation_ip, context_id => v_context_id ); insert into sn_objects (object_id, original_author_id, creation_date, last_modifying_user_id, last_modified, object_type_id,context_id) values (v_return_object_id, v_creation_user, v_insertion_date, v_creation_user, v_insertion_date, v_object_type_id,v_context_id); return v_return_object_id; end insert_object; function insert_object ( v_object_id in acs_objects.object_id%TYPE, v_object_type_id in sn_objects.object_type_id%TYPE, v_creation_user in acs_objects.creation_user%TYPE default null, v_creation_ip in acs_objects.creation_ip%TYPE default null, v_context_id in acs_objects.context_id%TYPE default null, v_insertion_date in acs_objects.creation_date%TYPE default sysdate ) return sn_objects.object_id%TYPE is v_return_object_id acs_objects.object_id%TYPE; begin v_return_object_id := acs_object.new ( object_id => v_object_id, object_type => 'sn_object', creation_date => v_insertion_date, creation_user => v_creation_user, creation_ip => v_creation_ip, context_id => v_context_id ); insert into sn_objects (object_id, original_author_id, creation_date, last_modifying_user_id, last_modified, object_type_id,context_id) values (v_return_object_id, v_creation_user, v_insertion_date, v_creation_user, v_insertion_date, v_object_type_id,v_context_id); return v_return_object_id; end insert_object; function copy_object ( v_object_id in acs_objects.object_id%TYPE, v_target_id in acs_objects.object_id%TYPE default null, v_creation_user in acs_objects.creation_user%TYPE default null, v_creation_ip in acs_objects.creation_ip%TYPE default null, v_context_id in acs_objects.context_id%TYPE default null ) return sn_objects.object_id%TYPE is v_new_link_id sn_links.link_id%TYPE; v_return_object_id acs_objects.object_id%TYPE; begin v_return_object_id := acs_object.new ( object_type => 'sn_object', object_id => v_target_id, creation_date => sysdate, creation_user => v_creation_user, creation_ip => v_creation_ip, context_id => v_context_id ); acs_object.last_modified ( v_object_id => v_return_object_id, v_modifying_user => v_creation_user, v_modifying_ip => v_creation_ip, v_last_modified => sysdate ); -- copy sn_object insert into sn_objects (object_id, object_type_id, context_id, one_line_description, overview, overview_html_p, creation_date, original_author_id, last_modified, last_modifying_user_id, user_checkoff_date, public_until, public_p, archived_p, access_total, access_month, archiving_date, outdated_warning_date, expired_warning_date) (select v_return_object_id as object_id, object_type_id, context_id, one_line_description, overview, overview_html_p, sysdate as creation_date, v_creation_user as original_author_id, sysdate as last_modified, v_creation_user as last_modifying_user_id, null as user_checkoff_date, public_until, 'f' as public_p, 'f' as archived_p, 0 as access_total, 0 as access_month, null as archiving_date, null as outdated_warning_date, null as expired_warning_date from sn_objects o where o.object_id = v_object_id); -- copy categories insert into sw_object_category_map (category_id, object_id) (select category_id, v_return_object_id as object_id from sw_object_category_map ocm where ocm.object_id = v_object_id); -- copy options insert into sn_object_option_map (object_id, option_id) (select v_return_object_id as object_id, option_id from sn_object_option_map oom where oom.object_id = v_object_id and oom.option_id in (select ao.option_id from sn_question_object_type_map qotm, sn_objects o, sn_answer_options ao where qotm.object_type_id = o.object_type_id and qotm.question_state = 'active' and qotm.question_id = ao.question_id and o.object_id = v_object_id) ); -- copy content insert into sn_content (object_id, question_id, content, html_p) (select v_return_object_id as object_id, question_id, content, html_p from sn_content c where c.object_id = v_object_id and c.question_id in (select qotm.question_id from sn_question_object_type_map qotm, sn_objects o where qotm.object_type_id = o.object_type_id and qotm.question_state = 'active' and o.object_id = v_object_id) ); -- copy links for link in (select qlm.link_id, qlm.question_id from sn_question_link_map qlm, sn_objects o, sn_question_object_type_map qotm, sn_links l where qotm.object_type_id = o.object_type_id and qotm.question_state = 'active' and qlm.question_id = qotm.question_id and l.link_id = qlm.link_id and l.link_type = 'bi_directional' and o.object_id = v_object_id and l.object_id_a = o.object_id) loop select sn_links_seq.nextval into v_new_link_id from dual; insert into sn_links (link_id, link_type, object_id_a, object_id_b, link_comment, html_p, creation_user, creation_date) (select v_new_link_id, link_type, decode(object_id_a,v_object_id,v_return_object_id,object_id_a), decode(object_id_b,v_object_id,v_return_object_id,object_id_b), link_comment, html_p, v_creation_user as creation_user, sysdate as creation_date from sn_links l where l.link_id = link.link_id); insert into sn_question_link_map (link_id, question_id) values (v_new_link_id, link.question_id); end loop; return v_return_object_id; end copy_object; procedure audit_object ( v_object_id in sn_objects.object_id%TYPE, v_question_id in sn_questions.question_id%TYPE, v_last_modifying_user_id in acs_objects.modifying_ip%TYPE, v_modifying_ip in acs_objects.modifying_ip%TYPE, v_content in sn_audit_table.content%TYPE, v_last_modified in acs_objects.last_modified%TYPE default sysdate ) is begin insert into sn_audit_table (object_id, question_id, last_modified, last_modifying_user_id, content) values (v_object_id, v_question_id, v_last_modified, v_last_modifying_user_id, v_content); update sn_objects set last_modified = v_last_modified, last_modifying_user_id = v_last_modifying_user_id, outdated_warning_date = null where object_id = v_object_id; update acs_objects_description set datastore = 'a' where object_id = v_object_id; acs_object.last_modified ( v_object_id => v_object_id, v_modifying_user => v_last_modifying_user_id, v_modifying_ip => v_modifying_ip, v_last_modified => v_last_modified); end audit_object; procedure delete_object ( v_object_id in sn_objects.object_id%TYPE ) is cursor sn_comments is select comment_id from sn_comments where object_id= v_object_id; cursor bookmarks is select bookmark_id from bm_bookmarks where object_id= v_object_id; begin delete from recommendations_log where object_id = v_object_id; delete from sn_question_link_map where link_id in (select link_id from sn_links where object_id_a = v_object_id or object_id_b=v_object_id); delete from psn_attachments where application_id in (select application_id from psn_res_applications where object_id=v_object_id); delete from psn_res_application_roles where application_id in (select application_id from psn_res_applications where object_id=v_object_id); delete from psn_res_applications where object_id=v_object_id; delete from sn_links where object_id_a = v_object_id or object_id_b=v_object_id; delete from user_alert_history where object_id=v_object_id; delete from alerts where object_id=v_object_id; delete from user_alerts where object_id=v_object_id; for cur_val in bookmarks loop acs_object.delete(cur_val.bookmark_id); delete from bm_bookmarks where bookmark_id=cur_val.bookmark_id; end loop; delete from bm_shopping_cart where object_id= v_object_id; -- ratings for cur_val in sn_comments loop acs_object.delete(cur_val.comment_id); end loop; delete from sn_owner_history where object_id=v_object_id; delete from sn_content where object_id=v_object_id; delete from sn_audit_table where object_id=v_object_id; delete from sn_object_delete_reasons where object_id=v_object_id; delete from sn_object_option_map where object_id=v_object_id; delete from sn_access_counts where object_id = v_object_id; delete from acs_permissions where object_id = v_object_id; delete from sn_objects where object_id=v_object_id; acs_object.delete(v_object_id); end; procedure delete_all_objects_of_a_type ( v_object_type_id in sn_object_types.object_type_id%TYPE ) is cursor cur_objects_of_a_type is select object_id from sn_objects where object_type_id = v_object_type_id; rows_processed boolean ; begin -- delete dependencies update acs_objects set context_id=null where context_id in (select object_id from sn_objects where object_type_id = v_object_type_id); delete from user_alert_history where content_type_id=v_object_type_id; delete from alerts where content_type_id=v_object_type_id; delete from user_alerts where content_type_id=v_object_type_id; for v_objects_of_a_type in cur_objects_of_a_type loop object.delete_object(v_objects_of_a_type.object_id); end loop; end; procedure delete_all_objects_of_an_inst ( v_context_id in acs_objects.context_id%TYPE ) is begin for v_all_objects_of_inst in ( select object_id from sn_objects where context_id = v_context_id ) loop object.delete_object(v_all_objects_of_inst.object_id); end loop; end; end object; / show errors create or replace package body question as -- Returns true if this question is a root of a branch (at any level) function root_branch_p ( v_question_id in sn_questions.question_id%TYPE ) return char is v_result char(1); begin select decode(count(*),0,'f','t') into v_result from sn_question_object_type_map where parent_question_id=v_question_id and branch_p='t'; return v_result; end root_branch_p; PROCEDURE map_question_on_object ( v_question_id in sn_questions.question_id%TYPE, v_object_type_id in sn_objects.object_type_id%TYPE, v_form_number in sn_question_object_type_map.form_number%TYPE, v_mandatory_p in sn_question_object_type_map.mandatory_p%TYPE, v_question_state in sn_question_object_type_map.question_state%TYPE ) IS v_max_sort_key sn_question_object_type_map.sort_key%TYPE; BEGIN select max(sort_key) into v_max_sort_key from sn_question_object_type_map where object_type_id = v_object_type_id; v_max_sort_key := v_max_sort_key + 10; insert into sn_question_object_type_map (object_type_id, question_id, form_number, mandatory_p, question_state, branch_p, sort_key) values (v_object_type_id, v_question_id, v_form_number, v_mandatory_p, v_question_state, 'f', v_max_sort_key); -- DIRK: update last_modified_date END map_question_on_object; PROCEDURE delete_question ( v_question_id in sn_questions.question_id%TYPE ) is cursor cur_child_questions is select question_id from sn_question_object_type_map where parent_question_id = v_question_id; begin -- nullify all short or long descriptions or linked_question-id or -- public_until delete from sn_types_map_short_name where short_description = v_question_id; update sn_object_types set short_description = null where short_description = v_question_id; update sn_object_types set long_description = null where long_description = v_question_id; update sn_object_types set linked_question_id = null where linked_question_id = v_question_id; update sn_object_types set public_until = null where public_until = v_question_id; update sn_questions set references_question_id = null where references_question_id = v_question_id; -- delete all child questions for v_child_questions in cur_child_questions loop question.delete_question(v_child_questions.question_id); end loop; delete from sn_links where link_id in (select link_id from sn_question_link_map where question_id = v_question_id); delete from sn_question_link_map where question_id = v_question_id; delete from sn_question_object_type_map where question_id = v_question_id; delete from sn_content where question_id = v_question_id; delete from sn_audit_table where question_id = v_question_id; delete from sn_object_option_map where option_id in (select option_id from sn_answer_options where question_id = v_question_id); delete from sn_answer_options where question_id = v_question_id; acs_object.delete(v_question_id); delete from sn_questions where question_id = v_question_id; end delete_question; function insert_question ( v_question_id in sn_questions.question_id%TYPE default null, v_pretty_name in sn_questions.pretty_name%TYPE, v_abstract_data_type in sn_questions.abstract_data_type%TYPE, v_order_by in sn_questions.order_by%TYPE, v_default_value in sn_questions.default_value%TYPE, v_entry_explanation in sn_questions.entry_explanation%TYPE, v_creation_user in acs_objects.creation_user%TYPE default null, v_creation_ip in acs_objects.creation_ip%TYPE default null, v_context_id in acs_objects.context_id%TYPE default null, v_insertion_date in acs_objects.creation_date%TYPE default sysdate, v_references_q_id in sn_questions.question_id%TYPE ) return sn_questions.question_id%TYPE is v_return_question_id acs_objects.object_id%TYPE; v_presentation_type sn_questions.presentation_type%TYPE; v_max_sort_key sn_question_object_type_map.sort_key%TYPE; begin if v_question_id is null then v_return_question_id := acs_object.new ( object_type => 'sn_question', creation_date => v_insertion_date, creation_user => v_creation_user, creation_ip => v_creation_ip, context_id => null ); else v_return_question_id := acs_object.new ( object_id => v_question_id, object_type => 'sn_question', creation_date => v_insertion_date, creation_user => v_creation_user, creation_ip => v_creation_ip, context_id => null ); end if; if v_abstract_data_type = 'category' then v_presentation_type := 'select'; elsif v_abstract_data_type = 'other_category' then v_presentation_type := 'select'; elsif v_abstract_data_type = 'option' then v_presentation_type := 'select'; elsif v_abstract_data_type = 'integer' then v_presentation_type := 'shorttext'; else v_presentation_type := 'custom'; end if; insert into sn_questions (question_id, pretty_name, abstract_data_type, presentation_type, order_by, default_value, entry_explanation, references_question_id) values (v_return_question_id, v_pretty_name, v_abstract_data_type, v_presentation_type, v_order_by, v_default_value, v_entry_explanation, v_references_q_id); return v_return_question_id; end insert_question; procedure update_question ( v_question_id in sn_questions.question_id%TYPE, v_pretty_name in sn_questions.pretty_name%TYPE, v_abstract_data_type in sn_questions.abstract_data_type%TYPE, v_order_by in sn_questions.order_by%TYPE, v_default_value in sn_questions.default_value%TYPE, v_entry_explanation in sn_questions.entry_explanation%TYPE, v_modifying_user in acs_objects.creation_user%TYPE, v_modifying_ip in acs_objects.modifying_ip%TYPE, v_update_date in acs_objects.last_modified%TYPE default sysdate, v_references_q_id in sn_questions.question_id%TYPE, v_def_question_id in sn_questions.question_id%TYPE default null, v_max_categories in sn_questions.max_categories%TYPE default null, v_browse_p in sn_questions.browse_p%TYPE default 't' ) is begin update sn_questions set pretty_name = v_pretty_name, entry_explanation = v_entry_explanation, abstract_data_type = v_abstract_data_type, order_by = v_order_by, default_value = v_default_value, references_question_id = v_references_q_id, defaults_question_id = v_def_question_id, max_categories = v_max_categories, browse_p = v_browse_p where question_id = v_question_id; acs_object.last_modified ( v_object_id => v_question_id, v_modifying_user => v_modifying_user, v_modifying_ip => v_modifying_ip, v_last_modified => v_update_date); end update_question; procedure delete_all_questions( v_context_id in acs_objects.context_id%TYPE default null ) is cursor c_delete_all_questions is select q.question_id from sn_questions q, sn_question_object_type_map map, sn_object_types ot where ot.context_id = v_context_id and ot.object_type_id = map.object_type_id and map.question_id = q.question_id; begin for v_delete_all_questions in c_delete_all_questions loop question.delete_question(v_delete_all_questions.question_id); end loop; end delete_all_questions; end question; / show errors create or replace package body km_utilities as function km_ancestor_list ( v_object_id in integer) return varchar is result varchar(4000); begin for c1_rec in ( select '{' || sno.object_id || ' {' || sno.one_line_description || '}}' as l from sn_objects sno, km_flat_object_hierarchy fo where fo.child = v_object_id and fo.parent = sno.object_id order by distance desc ) loop result := concat(result, c1_rec.l || ' '); end loop; return substr(result, 1, length(result) - 1); end km_ancestor_list; function km_category_list ( v_object_id in integer, v_parent_node_id in varchar) return varchar is the_list varchar(4000); begin for c1_rec in ( select long_name from categories c, sw_flat_cat fc, sw_object_category_map cm where fc.parent = v_parent_node_id and fc.child_category_id = c.category_id and cm.category_id = c.category_id and cm.object_id = v_object_id ) loop the_list := concat(the_list, c1_rec.long_name || ', '); end loop; return substr(the_list, 1, length(the_list) - 2); end km_category_list; function km_string2xml (str varchar2) return varchar2 is retval varchar2(4000); begin retval := replace(str,'&','&'); retval := replace(retval,'<','<'); retval := replace(retval,'>','>'); return retval; end km_string2xml; procedure km_clob2xml (dest in out clob, source in clob) is offset integer := 1; len integer; text varchar2(4000); begin len := dbms_lob.getlength(source); WHILE len > offset LOOP text := DBMS_LOB.SUBSTR(source,3000, offset); text := replace(text,'&','&'); text := replace(text,'<','<'); text := replace(text,'>','>'); dbms_lob.writeappend(dest,length(text),text); offset := offset + 3000; END LOOP; end km_clob2xml; procedure km_string2xmlclob (dest in out clob, source varchar2) is offset integer := 1; len integer; text varchar2(4000); begin len := length(source); WHILE len > offset LOOP text := substr(source,offset,3000); text := replace(text,'&','&'); text := replace(text,'<','<'); text := replace(text,'>','>'); dbms_lob.writeappend(dest,length(text),text); offset := offset + 3000; END LOOP; end km_string2xmlclob; function km_xml ( id sn_objects.object_id%type) RETURN CLOB is v_pretty_name sn_object_types.pretty_name%TYPE; -- CONSTANTS CR CONSTANT VARCHAR2(2) := CHR(13); sn_objects_rec sn_objects%ROWTYPE; CURSOR km_cursor (p_object_type_id integer) IS SELECT b.question_id, b.pretty_name, b.abstract_data_type FROM sn_question_object_type_map a, sn_questions b WHERE a.object_type_id = p_object_type_id AND a.question_id = b.question_id AND abstract_data_type NOT IN (/*'child_object',*/ 'file', /*'user_link',*/ 'composite') AND question_state <> 'invisible' ORDER BY sort_key; CURSOR km_category (p_object_id integer) IS SELECT c.category_id, c.short_name FROM sw_object_category_map m, categories c WHERE m.object_id = p_object_id AND c.category_id = m.category_id; CURSOR g_comments (p_object_id integer) IS SELECT content FROM sn_comments WHERE object_id = p_object_id; CURSOR km_links (p_object_id integer, p_question_id integer) IS SELECT c.object_id linked_id, b.link_type link_type, b.object_id_b FROM sn_links b, sn_objects c, sn_question_link_map map WHERE b.object_id_a = p_object_id AND b.link_id = map.link_id AND map.question_id = p_question_id AND b.object_id_b = c.object_id and c.object_id is not null; CURSOR km_content (p_object_id integer, p_question_id integer) IS SELECT dbms_lob.substr(content,3999,1) answer FROM sn_content WHERE object_id = p_object_id AND question_id = p_question_id and content is not null UNION SELECT answer_option answer FROM sn_object_option_map a, sn_answer_options b WHERE a.object_id = p_object_id AND a.option_id = b.option_id AND b.question_id = p_question_id; tag_object varchar2(4000); tag_owner varchar(300); tag_text varchar(4000); tag_category varchar(4000); tag_comments clob; tag_content clob; text varchar (4000); i integer; /* -- VARIABLES -- */ tlob clob; /* variable for store function output*/ BEGIN DBMS_LOB.CREATETEMPORARY(tlob, TRUE, DBMS_LOB.SESSION); -- prepare temorary lob for store COMMENTS tag DBMS_LOB.CREATETEMPORARY(tag_comments, TRUE, DBMS_LOB.SESSION); -- prepare temorary lob for store object_content tag DBMS_LOB.CREATETEMPORARY(tag_content, TRUE, DBMS_LOB.SESSION); -- get various info on table and columns to index SELECT * INTO sn_objects_rec FROM sn_objects WHERE object_id = id ; SELECT pretty_name into v_pretty_name FROM sn_object_types a WHERE a.object_type_id = sn_objects_rec.object_type_id; v_pretty_name := km_string2xml(v_pretty_name); SELECT ''||CR||''||CR INTO tag_object FROM sn_object_types a WHERE a.object_type_id = sn_objects_rec.object_type_id; -- get contents for OWNER tag IF sn_objects_rec.original_author_id IS NOT NULL THEN SELECT first_names || ' ' || last_name INTO tag_owner FROM users WHERE user_id = sn_objects_rec.original_author_id; tag_owner := ' '||km_string2xml(tag_owner)||''||CR; END IF; -- get content for CATEGORY tag FOR ccat IN km_category(sn_objects_rec.object_id) LOOP tag_category := tag_category || ' '||km_string2xml(ccat.short_name)||''||CR; END LOOP; -- get content for COMMENTS tag -- at first look in general_comments table FOR gcom IN g_comments (sn_objects_rec.object_id) LOOP if nvl(dbms_lob.getlength(gcom.content),0) > 0 then DBMS_LOB.WRITEAPPEND(tag_comments, length(' '), ' '); km_clob2xml(tag_comments, gcom.content); DBMS_LOB.WRITEAPPEND(tag_comments, length(' '||CR), ' '||CR); end if; END LOOP; FOR cc IN km_cursor (sn_objects_rec.object_type_id) LOOP i := 0; FOR c1 IN km_content(sn_objects_rec.object_id, cc.question_id) LOOP IF i = 0 THEN text := ' '||CR; DBMS_LOB.WRITEAPPEND(tag_content,length(text),text); i := 1; END IF; DBMS_LOB.WRITEAPPEND(tag_content, length(' '), ' '); km_string2xmlclob(tag_content, c1.answer); DBMS_LOB.WRITEAPPEND(tag_content, length(''||CR), ''||CR); END LOOP; FOR c2 IN km_links(id, cc.question_id) LOOP IF i = 0 THEN text := ' '||CR; DBMS_LOB.WRITEAPPEND(tag_content,length(text),text); i := 1; END IF; text := ' '||CR; DBMS_LOB.WRITEAPPEND(tag_content, length(text), text); END LOOP; IF i <> 0 THEN DBMS_LOB.WRITEAPPEND(tag_content,length(' '||CR),' '||CR); END IF; END LOOP; -- Construct XML DOC DBMS_LOB.WRITEAPPEND (tlob,length(tag_object),tag_object); DBMS_LOB.WRITEAPPEND (tlob,length(tag_owner),tag_owner); IF tag_category is not null THEN DBMS_LOB.WRITEAPPEND (tlob,length(tag_category),tag_category); END IF; DBMS_LOB.APPEND(tlob,tag_comments); DBMS_LOB.APPEND (tlob,tag_content); DBMS_LOB.WRITEAPPEND (tlob,9 ,''); return tlob; END km_xml; procedure km_populate_flat_object_hier IS CURSOR child_cursor IS select object_id_b as object_id from sn_links where link_type in ('parent_child','uncle_nephew'); BEGIN delete from km_flat_object_hierarchy; FOR child IN child_cursor LOOP insert into km_flat_object_hierarchy select acs_object_id_seq.nextval, object_id_a, child.object_id, level, link_type from sn_links start with object_id_b = child.object_id and link_type in ('parent_child','uncle_nephew') connect by object_id_b = prior object_id_a and link_type in ('parent_child','uncle_nephew'); END LOOP; END km_populate_flat_object_hier; procedure km_logger ( v_who_is_logging varchar2, v_log_text varchar2) as pragma autonomous_transaction; begin insert into km_logger_table values (sysdate, v_who_is_logging, v_log_text ); commit; end km_logger; function shortname_string (p_object_id in sn_objects.object_id%TYPE, p_question_id1 in sn_questions.question_id%TYPE, p_question_id2 in sn_questions.question_id%TYPE, p_question_id3 in sn_questions.question_id%TYPE default 0) return varchar2 is v_result_string varchar2(4000) := ''; v_result_string1 varchar2(4000) := ''; v_result_string2 varchar2(4000) := ''; v_result_string3 varchar2(4000) := ''; v_length_string1 integer; v_length_string2 integer; v_length_string3 integer; begin begin select trim(dbms_lob.substr(content,1300,1)) into v_result_string1 from sn_content where question_id=p_question_id1 and object_id=p_object_id; exception when others then v_result_string1 := ''; end; begin select trim(dbms_lob.substr(content,1300,1)) into v_result_string2 from sn_content where question_id=p_question_id2 and object_id=p_object_id; exception when others then v_result_string2 := ''; end; begin if p_question_id3 > 0 then select trim(dbms_lob.substr(content,1300,1)) into v_result_string3 from sn_content where question_id=p_question_id3 and object_id=p_object_id; end if; exception when others then v_result_string3 := ''; end; v_length_string1 := length(v_result_string1); v_length_string2 := length(v_result_string2); v_length_string3 := length(v_result_string3); if (v_length_string1 > 0) then v_result_string := v_result_string1; if (v_length_string2 > 0) then v_result_string := v_result_string || ', ' || v_result_string2; end if; if (v_length_string3 > 0) then v_result_string := v_result_string || ', ' || v_result_string3; end if; else if (v_length_string2 > 0) then v_result_string := v_result_string2; if (v_length_string3 > 0) then v_result_string := v_result_string || ', ' || v_result_string3; end if; else if (v_length_string3 > 0) then v_result_string := v_result_string3; end if; end if; end if; return v_result_string; end shortname_string; function strip_html ( input_text in varchar2) return varchar2 is output varchar2(4000); i number; tag_start number; tag_end number; char_count number; begin i := 1; output := ''; while instr(input_text, '<', i) >= i loop tag_start := instr(input_text, '<', i); tag_end := instr(input_text, '>', tag_start); if tag_end > 0 then char_count := tag_start - i; if char_count > 0 then output := output || substr(input_text, i, char_count); end if; i := tag_end + 1; else exit; end if; end loop; output := output || substr(input_text, i); select replace(output, '\<', '<') into output from dual; select replace(output, '\>', '>') into output from dual; select replace(output, '\"', '"') into output from dual; return (output); end strip_html; function strip_html_and_limit_size (p_overview in sn_objects.overview%TYPE) return VARCHAR2 is v_overview sn_objects.overview%TYPE; begin v_overview := strip_html(p_overview); if (length(p_overview)>200) then v_overview := substr(v_overview,1,200) || '...'; end if; return v_overview; end strip_html_and_limit_size; end km_utilities; / show errors create or replace trigger km_flat_object_hier_insert_tr before insert on sn_links for each row begin if :new.link_type = 'parent_child' or :new.link_type = 'uncle_nephew' then insert into km_flat_object_hierarchy (object_hierarchy_id, parent, child, distance, link_type) values (acs_object_id_seq.nextval, :new.object_id_a, :new.object_id_b, 1, :new.link_type); insert into km_flat_object_hierarchy (object_hierarchy_id, parent, child, distance, link_type) select acs_object_id_seq.nextval, parent, :new.object_id_b, distance+1, :new.link_type from km_flat_object_hierarchy where child = :new.object_id_a; end if; end ; / show errors create or replace trigger km_flat_object_hier_delete_tr before delete on sn_links for each row begin if :old.link_type = 'parent_child' or :old.link_type = 'uncle_nephew' then delete from km_flat_object_hierarchy where child = :old.object_id_b; end if; end ; / show errors create or replace function im_convert( query in varchar2 default null ) return varchar2 is i number :=0; len number :=0; char varchar2(1); minusString varchar2(4000); plusString varchar2(4000); mainString varchar2(4000); mainAboutString varchar2(4000); finalString varchar2(4000); hasMain number :=0; hasPlus number :=0; hasMinus number :=0; token varchar2(4000); tokenStart number :=1; tokenFinish number :=0; inPhrase number :=0; inPlus number :=0; inWord number :=0; inMinus number :=0; completePhrase number :=0; completeWord number :=0; code number :=0; begin len := length(query); -- we iterate over the string to find special web operators for i in 1..len loop char := substr(query,i,1); if(char = '"') then if(inPhrase = 0) then inPhrase := 1; tokenStart := i; else inPhrase := 0; completePhrase := 1; tokenFinish := i-1; end if; elsif(char = ' ') then if(inPhrase = 0) then completeWord := 1; tokenFinish := i-1; end if; elsif(char = '+') then inPlus := 1; tokenStart := i+1; elsif((char = '-') and (i = tokenStart)) then inMinus :=1; tokenStart := i+1; end if; if(completeWord=1) then token := '{ '||substr(query,tokenStart,tokenFinish-tokenStart+1)||' }'; if(inPlus=1) then plusString := plusString||','||token||'*10'; hasPlus :=1; elsif(inMinus=1) then minusString := minusString||'OR '||token||' '; hasMinus :=1; else mainString := mainString||' NEAR '||token; mainAboutString := mainAboutString||' '||token; hasMain :=1; end if; tokenStart :=i+1; tokenFinish :=0; inPlus := 0; inMinus :=0; end if; completePhrase := 0; completeWord :=0; end loop; -- find the last token token := '{ '||substr(query,tokenStart,len-tokenStart+1)||' }'; if(inPlus=1) then plusString := plusString||','||token||'*10'; hasPlus :=1; elsif(inMinus=1) then minusString := minusString||'OR '||token||' '; hasMinus :=1; else mainString := mainString||' NEAR '||token; mainAboutString := mainAboutString||' '||token; hasMain :=1; end if; mainString := substr(mainString,6,length(mainString)-5); mainAboutString := replace(mainAboutString,'{',' '); mainAboutString := replace(mainAboutString,'}',' '); plusString := substr(plusString,2,length(plusString)-1); minusString := substr(minusString,4,length(minusString)-4); -- we find the components present and then process them based on the specific combinations code := hasMain*4+hasPlus*2+hasMinus; if(code = 7) then finalString := '('||plusString||','||mainString||'*2.0,about('||mainAboutString||')*0.5) NOT ('||minusString||')'; elsif (code = 6) then finalString := plusString||','||mainString||'*2.0'||',about('||mainAboutString||')*0.5'; elsif (code = 5) then finalString := '('||mainString||',about('||mainAboutString||')) NOT ('||minusString||')'; elsif (code = 4) then finalString := mainString; finalString := replace(finalString,'*1,',NULL); finalString := '('||finalString||')*2.0,about('||mainAboutString||')'; elsif (code = 3) then finalString := '('||plusString||') NOT ('||minusString||')'; elsif (code = 2) then finalString := plusString; elsif (code = 1) then -- not is a binary operator for intermedia text finalString := 'totallyImpossibleString'||' NOT ('||minusString||')'; elsif (code = 0) then finalString := ''; end if; return finalString; end; / create or replace package body psn_res_application as procedure create_magic_objects ( package_id in apm_packages.package_id%TYPE, creation_user in acs_objects.creation_user%TYPE default null, creation_ip in acs_objects.creation_ip%TYPE default null ) is v_role_object_id acs_objects.object_id%TYPE; v_language_object_id acs_objects.object_id%TYPE; v_proficiency_object_id acs_objects.object_id%TYPE; begin v_role_object_id := acs_object.new ( object_type => 'psn_tree_object', creation_date => sysdate, creation_user => creation_user, creation_ip => creation_ip, context_id => package_id ); acs_object.new_description ( v_object_id => v_role_object_id, v_shortname => 'Role Category Tree', v_overview => 'Role Category Tree for Knowledge Library #' || package_id, v_overview_html_p => 'f', v_package_id => package_id ); v_language_object_id := acs_object.new ( object_type => 'psn_tree_object', creation_date => sysdate, creation_user => creation_user, creation_ip => creation_ip, context_id => package_id ); acs_object.new_description ( v_object_id => v_language_object_id, v_shortname => 'Language Category Tree', v_overview => 'Language Category Tree for Knowledge Library #' || package_id, v_overview_html_p => 'f', v_package_id => package_id ); v_proficiency_object_id := acs_object.new ( object_type => 'psn_tree_object', creation_date => sysdate, creation_user => creation_user, creation_ip => creation_ip, context_id => package_id ); acs_object.new_description ( v_object_id => v_proficiency_object_id, v_shortname => 'Language Proficiency Category Tree', v_overview => 'Language Proficiency Category Tree for Knowledge Library #' || package_id, v_overview_html_p => 'f', v_package_id => package_id ); insert into psn_category_trees (package_id, role_magic_id, language_magic_id, proficiency_magic_id) values (package_id, v_role_object_id, v_language_object_id, v_proficiency_object_id); end create_magic_objects; procedure copy ( src_package_id in apm_packages.package_id%TYPE, dst_package_id in apm_packages.package_id%TYPE, creation_user in acs_objects.creation_user%TYPE, creation_ip in acs_objects.creation_ip%TYPE ) is begin -- Copy relations between magic objects and category trees. -- We do this with a cursor because some of the relations may not exist. for c in ( select r.object_id_two as tree_id, octr.subtree_root_node_id as node_id, ct2.role_magic_id as object_id from psn_category_trees ct1, psn_category_trees ct2, acs_rels r, object_category_tree_rels octr where ct1.package_id = src_package_id and ct2.package_id = dst_package_id and r.object_id_one = ct1.role_magic_id and r.rel_type = 'object_category_tree_rel' and octr.rel_id = r.rel_id union all select r.object_id_two, octr.subtree_root_node_id, ct2.language_magic_id from psn_category_trees ct1, psn_category_trees ct2, acs_rels r, object_category_tree_rels octr where ct1.package_id = src_package_id and ct2.package_id = dst_package_id and r.object_id_one = ct1.language_magic_id and r.rel_type = 'object_category_tree_rel' and octr.rel_id = r.rel_id union all select r.object_id_two, octr.subtree_root_node_id, ct2.proficiency_magic_id from psn_category_trees ct1, psn_category_trees ct2, acs_rels r, object_category_tree_rels octr where ct1.package_id = src_package_id and ct2.package_id = dst_package_id and r.object_id_one = ct1.proficiency_magic_id and r.rel_type = 'object_category_tree_rel' and octr.rel_id = r.rel_id ) loop category.map_tree(c.object_id, c.node_id, c.tree_id); end loop; end copy; end psn_res_application; / show errors