-- @cvs-id $Id: library-packages.sql,v 1.1 2003/07/02 12:19:42 peterm Exp $

create or replace package 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
    );
end library;
/

create or replace package question
as
  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, 
        v_creation_ip           in acs_objects.creation_ip%TYPE, 
        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 default null
  ) return sn_questions.question_id%TYPE;

  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 default null,
	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'
  );

  procedure delete_question (
        v_question_id		in sn_questions.question_id%TYPE
  );

  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
  );

  function root_branch_p (
     v_question_id		in sn_questions.question_id%TYPE
  ) return char;

  procedure delete_all_questions(
        v_context_id            in acs_objects.context_id%TYPE         
  ) ;

end question;
/
show errors

create or replace package object_type
as
  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, 
        v_creation_ip           in acs_objects.creation_ip%TYPE, 
        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;

  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, 
        v_creation_ip           in acs_objects.creation_ip%TYPE, 
        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;

  procedure delete_object_type (
        v_object_type_id	in sn_object_types.object_type_id%TYPE
  );

  procedure delete_all_object_types (
        v_context_id            in acs_objects.context_id%TYPE         
  );

  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
  );

  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
  );

end object_type;
/
show errors

create or replace package object
as
  function name (
      object_id    in sn_objects.object_id%TYPE
  ) return varchar2;

  function insert_object (
	v_object_type_id        in sn_objects.object_type_id%TYPE,
        v_creation_user         in acs_objects.creation_user%TYPE, 
        v_creation_ip           in acs_objects.creation_ip%TYPE,  
        v_context_id            in acs_objects.context_id%TYPE,
        v_insertion_date        in acs_objects.creation_date%TYPE default sysdate
  ) return sn_objects.object_id%TYPE;

  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;

  procedure delete_object (
        v_object_id		in sn_objects.object_id%TYPE
  );

  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
        );

  procedure delete_all_objects_of_a_type (
        v_object_type_id	in sn_object_types.object_type_id%TYPE
  );

  procedure delete_all_objects_of_an_inst (
        v_context_id            in acs_objects.context_id%TYPE
  );

  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
        );

  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;

end object;
/
show errors


create or replace package km_utilities
as
  function km_ancestor_list (
    v_object_id in integer) 
  return varchar;

  function km_category_list (
    v_object_id in integer, 
    v_parent_node_id in varchar) 
  return varchar;

  function km_xml ( 
    id sn_objects.object_id%type) 
  return clob;

  procedure km_populate_flat_object_hier;

  procedure km_logger (
    v_who_is_logging varchar2, 
    v_log_text varchar2);

  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;

  function strip_html_and_limit_size
  (p_overview           in sn_objects.overview%TYPE)
  return VARCHAR2;

end km_utilities;
/
show errors

create or replace package 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
    );

    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
    );

end psn_res_application;
/
show errors