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,'&','&amp;');
    retval := replace(retval,'<','&lt;');
    retval := replace(retval,'>','&gt;');
    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,'&','&amp;');
      text := replace(text,'<','&lt;');
      text := replace(text,'>','&gt;');
      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,'&','&amp;');
      text := replace(text,'<','&lt;');
      text := replace(text,'>','&gt;');
      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 '<?xml version="1.0"?>'||CR||'<object type_id="'||ltrim(to_char(sn_objects_rec.object_type_id))||'" type_name="'
      ||v_pretty_name ||'" instance_id="'||ltrim(to_char(id))||'">'||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 := ' <owner id="'||ltrim(to_char(sn_objects_rec.original_author_id))||'">'||km_string2xml(tag_owner)||'</owner>'||CR; 
    END IF;
     
    -- get content for CATEGORY tag
    FOR ccat IN km_category(sn_objects_rec.object_id) LOOP
      tag_category := tag_category || ' <category id="'||ltrim(to_char(ccat.category_id))||'">'||km_string2xml(ccat.short_name)||'</category>'||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(' <comments>'), ' <comments>');
         km_clob2xml(tag_comments, gcom.content);
         DBMS_LOB.WRITEAPPEND(tag_comments, length(' </comments>'||CR), ' </comments>'||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 := '  <question id="'||ltrim(to_char(cc.question_id))||'" name="'||km_string2xml(cc.pretty_name)||'">'||CR;
  	  DBMS_LOB.WRITEAPPEND(tag_content,length(text),text);
  	  i := 1;
        END IF;
        DBMS_LOB.WRITEAPPEND(tag_content, length('    <answer>'), '    <answer>');

        km_string2xmlclob(tag_content, c1.answer);
        DBMS_LOB.WRITEAPPEND(tag_content, length('</answer>'||CR), '</answer>'||CR);
      END LOOP;
      FOR c2 IN km_links(id, cc.question_id) LOOP
        IF i = 0 THEN
          text := '  <question id="'||ltrim(to_char(cc.question_id))||'" name="'||km_string2xml(cc.pretty_name)||'">'||CR;
  	  DBMS_LOB.WRITEAPPEND(tag_content,length(text),text);
	  i := 1;
        END IF;
        text := '    <answer type="link" link_type="'||c2.link_type||'" linked_object_instace_id="'||ltrim(to_char(c2.linked_id))||'"></answer>'||CR;
        DBMS_LOB.WRITEAPPEND(tag_content, length(text), text);      	
      END LOOP;
      IF i <> 0 THEN
        DBMS_LOB.WRITEAPPEND(tag_content,length('  </question>'||CR),'  </question>'||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 ,'</object>');
    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, '\&lt;', '<') into output from dual;
          select replace(output, '\&gt;', '>') into output from dual;
          select replace(output, '\&quot;', '"') 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
