Index: openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.0d7-5.0d9.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.0d7-5.0d9.sql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.0d7-5.0d9.sql	24 Sep 2003 12:20:27 -0000	1.1
+++ openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-5.0d7-5.0d9.sql	24 Sep 2003 17:25:05 -0000	1.2
@@ -10,117 +10,121 @@
 drop table apm_package_file_types cascade constraints;
 drop table apm_package_files cascade constraints;
 drop view apm_file_info;
--- Recreating apm_version_package to remove the add_file and remove_file procs and functions
+-- Recreating apm_version_package and apm_package packages to remove the following procs and functions:
+-- apm_package_version.add_file
+-- apm_package_version.remove_file
+-- apm_package.enable
+-- apm_package.disable
 create or replace package apm_package_version
 as
   function new (
-    version_id                  in apm_package_versions.version_id%TYPE
-                                        default null,
-    package_key                 in apm_package_versions.package_key%TYPE,
-    version_name                in apm_package_versions.version_name%TYPE 
-                                        default null,
-    version_uri                 in apm_package_versions.version_uri%TYPE,
-    summary                     in apm_package_versions.summary%TYPE,
-    description_format          in apm_package_versions.description_format%TYPE,
-    description                 in apm_package_versions.description%TYPE,
-    release_date                in apm_package_versions.release_date%TYPE,
-    vendor                      in apm_package_versions.vendor%TYPE,
-    vendor_uri                  in apm_package_versions.vendor_uri%TYPE,
+    version_id			in apm_package_versions.version_id%TYPE
+					default null,
+    package_key			in apm_package_versions.package_key%TYPE,
+    version_name		in apm_package_versions.version_name%TYPE 
+					default null,
+    version_uri			in apm_package_versions.version_uri%TYPE,
+    summary			in apm_package_versions.summary%TYPE,
+    description_format		in apm_package_versions.description_format%TYPE,
+    description			in apm_package_versions.description%TYPE,
+    release_date		in apm_package_versions.release_date%TYPE,
+    vendor			in apm_package_versions.vendor%TYPE,
+    vendor_uri			in apm_package_versions.vendor_uri%TYPE,
     auto_mount                  in apm_package_versions.auto_mount%TYPE,
-    installed_p                 in apm_package_versions.installed_p%TYPE
-                                        default 'f',
-    data_model_loaded_p         in apm_package_versions.data_model_loaded_p%TYPE
-                                        default 'f'
+    installed_p			in apm_package_versions.installed_p%TYPE
+					default 'f',
+    data_model_loaded_p		in apm_package_versions.data_model_loaded_p%TYPE
+				        default 'f'
   ) return apm_package_versions.version_id%TYPE;
 
   procedure delete (
-      version_id                in apm_packages.package_id%TYPE
+      version_id		in apm_packages.package_id%TYPE
   );
 
   procedure enable (
-       version_id                       in apm_package_versions.version_id%TYPE
+       version_id			in apm_package_versions.version_id%TYPE
   );
 
   procedure disable (
-       version_id                       in apm_package_versions.version_id%TYPE
+       version_id			in apm_package_versions.version_id%TYPE
   );
 
  function edit (
-      new_version_id            in apm_package_versions.version_id%TYPE
-                                default null,
-      version_id                in apm_package_versions.version_id%TYPE,
-      version_name              in apm_package_versions.version_name%TYPE 
-                                default null,
-      version_uri               in apm_package_versions.version_uri%TYPE,
-      summary                   in apm_package_versions.summary%TYPE,
-      description_format        in apm_package_versions.description_format%TYPE,
-      description               in apm_package_versions.description%TYPE,
-      release_date              in apm_package_versions.release_date%TYPE,
-      vendor                    in apm_package_versions.vendor%TYPE,
-      vendor_uri                in apm_package_versions.vendor_uri%TYPE,
+      new_version_id		in apm_package_versions.version_id%TYPE
+				default null,
+      version_id		in apm_package_versions.version_id%TYPE,
+      version_name		in apm_package_versions.version_name%TYPE 
+				default null,
+      version_uri		in apm_package_versions.version_uri%TYPE,
+      summary			in apm_package_versions.summary%TYPE,
+      description_format	in apm_package_versions.description_format%TYPE,
+      description		in apm_package_versions.description%TYPE,
+      release_date		in apm_package_versions.release_date%TYPE,
+      vendor			in apm_package_versions.vendor%TYPE,
+      vendor_uri		in apm_package_versions.vendor_uri%TYPE,
       auto_mount                in apm_package_versions.auto_mount%TYPE,
-      installed_p               in apm_package_versions.installed_p%TYPE
-                                default 'f',
-      data_model_loaded_p       in apm_package_versions.data_model_loaded_p%TYPE
-                                default 'f'
+      installed_p		in apm_package_versions.installed_p%TYPE
+				default 'f',
+      data_model_loaded_p	in apm_package_versions.data_model_loaded_p%TYPE
+				default 'f'
     ) return apm_package_versions.version_id%TYPE;
 
   -- Add an interface provided by this version.
   function add_interface(
-    interface_id                in apm_package_dependencies.dependency_id%TYPE
-                                default null,
-    version_id                  in apm_package_versions.version_id%TYPE,
-    interface_uri               in apm_package_dependencies.service_uri%TYPE,
-    interface_version           in apm_package_dependencies.service_version%TYPE
+    interface_id		in apm_package_dependencies.dependency_id%TYPE
+			        default null,
+    version_id			in apm_package_versions.version_id%TYPE,
+    interface_uri		in apm_package_dependencies.service_uri%TYPE,
+    interface_version		in apm_package_dependencies.service_version%TYPE
   ) return apm_package_dependencies.dependency_id%TYPE;
 
   procedure remove_interface(
-    interface_id                in apm_package_dependencies.dependency_id%TYPE
+    interface_id		in apm_package_dependencies.dependency_id%TYPE
   );
 
   procedure remove_interface(
-    interface_uri               in apm_package_dependencies.service_uri%TYPE,
-    interface_version           in apm_package_dependencies.service_version%TYPE,
-    version_id                  in apm_package_versions.version_id%TYPE
+    interface_uri		in apm_package_dependencies.service_uri%TYPE,
+    interface_version		in apm_package_dependencies.service_version%TYPE,
+    version_id			in apm_package_versions.version_id%TYPE
   );
 
   -- Add a requirement for this version.  A requirement is some interface that this
   -- version depends on.
   function add_dependency(
-    dependency_id               in apm_package_dependencies.dependency_id%TYPE
-                                default null,
-    version_id                  in apm_package_versions.version_id%TYPE,
-    dependency_uri              in apm_package_dependencies.service_uri%TYPE,
-    dependency_version          in apm_package_dependencies.service_version%TYPE
+    dependency_id		in apm_package_dependencies.dependency_id%TYPE
+			        default null,
+    version_id			in apm_package_versions.version_id%TYPE,
+    dependency_uri		in apm_package_dependencies.service_uri%TYPE,
+    dependency_version		in apm_package_dependencies.service_version%TYPE
   ) return apm_package_dependencies.dependency_id%TYPE;
 
   procedure remove_dependency(
-    dependency_id               in apm_package_dependencies.dependency_id%TYPE
+    dependency_id		in apm_package_dependencies.dependency_id%TYPE
   );
 
   procedure remove_dependency(
-    dependency_uri              in apm_package_dependencies.service_uri%TYPE,
-    dependency_version          in apm_package_dependencies.service_version%TYPE,
-    version_id                  in apm_package_versions.version_id%TYPE
+    dependency_uri		in apm_package_dependencies.service_uri%TYPE,
+    dependency_version		in apm_package_dependencies.service_version%TYPE,
+    version_id			in apm_package_versions.version_id%TYPE
   );
 
   -- Given a version_name (e.g. 3.2a), return
   -- something that can be lexicographically sorted.
   function sortable_version_name (
-    version_name                in apm_package_versions.version_name%TYPE
+    version_name		in apm_package_versions.version_name%TYPE
   ) return varchar2;
 
   -- Given two version names, return 1 if one > two, -1 if two > one, 0 otherwise. 
   -- Deprecate?
   function version_name_greater(
-    version_name_one            in apm_package_versions.version_name%TYPE,
-    version_name_two            in apm_package_versions.version_name%TYPE
+    version_name_one		in apm_package_versions.version_name%TYPE,
+    version_name_two		in apm_package_versions.version_name%TYPE
   ) return integer;
 
   function upgrade_p(
-    path                        in varchar2,
-    initial_version_name        in apm_package_versions.version_name%TYPE,
-    final_version_name          in apm_package_versions.version_name%TYPE
+    path			in varchar2,
+    initial_version_name	in apm_package_versions.version_name%TYPE,
+    final_version_name		in apm_package_versions.version_name%TYPE
    ) return integer;
 
   procedure upgrade(
@@ -134,37 +138,37 @@
 create or replace package body apm_package_version 
 as
     function new (
-      version_id                in apm_package_versions.version_id%TYPE
-                                default null,
-      package_key               in apm_package_versions.package_key%TYPE,
-      version_name              in apm_package_versions.version_name%TYPE 
-                                default null,
-      version_uri               in apm_package_versions.version_uri%TYPE,
-      summary                   in apm_package_versions.summary%TYPE,
-      description_format        in apm_package_versions.description_format%TYPE,
-      description               in apm_package_versions.description%TYPE,
-      release_date              in apm_package_versions.release_date%TYPE,
-      vendor                    in apm_package_versions.vendor%TYPE,
-      vendor_uri                in apm_package_versions.vendor_uri%TYPE,
+      version_id		in apm_package_versions.version_id%TYPE
+				default null,
+      package_key		in apm_package_versions.package_key%TYPE,
+      version_name		in apm_package_versions.version_name%TYPE 
+				default null,
+      version_uri		in apm_package_versions.version_uri%TYPE,
+      summary			in apm_package_versions.summary%TYPE,
+      description_format	in apm_package_versions.description_format%TYPE,
+      description		in apm_package_versions.description%TYPE,
+      release_date		in apm_package_versions.release_date%TYPE,
+      vendor			in apm_package_versions.vendor%TYPE,
+      vendor_uri		in apm_package_versions.vendor_uri%TYPE,
       auto_mount                in apm_package_versions.auto_mount%TYPE,
-      installed_p               in apm_package_versions.installed_p%TYPE
-                                default 'f',
-      data_model_loaded_p       in apm_package_versions.data_model_loaded_p%TYPE
-                                default 'f'
+      installed_p		in apm_package_versions.installed_p%TYPE
+				default 'f',
+      data_model_loaded_p	in apm_package_versions.data_model_loaded_p%TYPE
+				default 'f'
     ) return apm_package_versions.version_id%TYPE
     is
       v_version_id apm_package_versions.version_id%TYPE;
     begin
       if version_id is null then
          select acs_object_id_seq.nextval
-         into v_version_id
-         from dual;
+	 into v_version_id
+	 from dual;
       else
          v_version_id := version_id;
       end if;
-        v_version_id := acs_object.new(
-                object_id => v_version_id,
-                object_type => 'apm_package_version'
+	v_version_id := acs_object.new(
+		object_id => v_version_id,
+		object_type => 'apm_package_version'
         );
       insert into apm_package_versions
       (version_id, package_key, version_name, version_uri, summary, description_format, description,
@@ -174,11 +178,11 @@
        summary, description_format, description,
        release_date, vendor, vendor_uri, auto_mount,
        installed_p, data_model_loaded_p);
-      return v_version_id;              
+      return v_version_id;		
     end new;
 
     procedure delete (
-      version_id                in apm_packages.package_id%TYPE
+      version_id		in apm_packages.package_id%TYPE
     )
     is
     begin
@@ -189,91 +193,90 @@
       where version_id = apm_package_version.delete.version_id;
 
       delete from apm_package_versions 
-        where version_id = apm_package_version.delete.version_id;
+	where version_id = apm_package_version.delete.version_id;
 
       acs_object.delete(apm_package_version.delete.version_id);
 
     end delete;
 
     procedure enable (
-       version_id                       in apm_package_versions.version_id%TYPE
+       version_id			in apm_package_versions.version_id%TYPE
     )
     is
     begin
       update apm_package_versions set enabled_p = 't'
-      where version_id = enable.version_id;     
+      where version_id = enable.version_id;	
     end enable;
     
     procedure disable (
-       version_id                       in apm_package_versions.version_id%TYPE
+       version_id			in apm_package_versions.version_id%TYPE
     )
     is
     begin
       update apm_package_versions 
       set enabled_p = 'f'
-      where version_id = disable.version_id;    
+      where version_id = disable.version_id;	
     end disable;
 
-
   function copy(
-        version_id in apm_package_versions.version_id%TYPE,
-        new_version_id in apm_package_versions.version_id%TYPE default null,
-        new_version_name in apm_package_versions.version_name%TYPE,
-        new_version_uri in apm_package_versions.version_uri%TYPE
+	version_id in apm_package_versions.version_id%TYPE,
+	new_version_id in apm_package_versions.version_id%TYPE default null,
+	new_version_name in apm_package_versions.version_name%TYPE,
+	new_version_uri in apm_package_versions.version_uri%TYPE
   ) return apm_package_versions.version_id%TYPE
     is
-        v_version_id integer;
+	v_version_id integer;
     begin
-        v_version_id := acs_object.new(
-                object_id => new_version_id,
-                object_type => 'apm_package_version'
+	v_version_id := acs_object.new(
+		object_id => new_version_id,
+		object_type => 'apm_package_version'
         );    
 
-        insert into apm_package_versions(version_id, package_key, version_name,
-                                        version_uri, summary, description_format, description,
-                                        release_date, vendor, vendor_uri, auto_mount)
-            select v_version_id, package_key, copy.new_version_name,
-                   copy.new_version_uri, summary, description_format, description,
-                   release_date, vendor, vendor_uri, auto_mount
-            from apm_package_versions
-            where version_id = copy.version_id;
+	insert into apm_package_versions(version_id, package_key, version_name,
+					version_uri, summary, description_format, description,
+					release_date, vendor, vendor_uri, auto_mount)
+	    select v_version_id, package_key, copy.new_version_name,
+		   copy.new_version_uri, summary, description_format, description,
+		   release_date, vendor, vendor_uri, auto_mount
+	    from apm_package_versions
+	    where version_id = copy.version_id;
     
-        insert into apm_package_dependencies(dependency_id, version_id, dependency_type, service_uri, service_version)
-            select acs_object_id_seq.nextval, v_version_id, dependency_type, service_uri, service_version
-            from apm_package_dependencies
-            where version_id = copy.version_id;
+	insert into apm_package_dependencies(dependency_id, version_id, dependency_type, service_uri, service_version)
+	    select acs_object_id_seq.nextval, v_version_id, dependency_type, service_uri, service_version
+	    from apm_package_dependencies
+	    where version_id = copy.version_id;
     
         insert into apm_package_callbacks (version_id, type, proc)
                 select v_version_id, type, proc
                 from apm_package_callbacks
                 where version_id = copy.version_id;
     
-        insert into apm_package_owners(version_id, owner_uri, owner_name, sort_key)
-            select v_version_id, owner_uri, owner_name, sort_key
-            from apm_package_owners
-            where version_id = copy.version_id;
+	insert into apm_package_owners(version_id, owner_uri, owner_name, sort_key)
+	    select v_version_id, owner_uri, owner_name, sort_key
+	    from apm_package_owners
+	    where version_id = copy.version_id;
     
-        return v_version_id;
+	return v_version_id;
     end copy;
     
     function edit (
-      new_version_id            in apm_package_versions.version_id%TYPE
-                                default null,
-      version_id                in apm_package_versions.version_id%TYPE,
-      version_name              in apm_package_versions.version_name%TYPE 
-                                default null,
-      version_uri               in apm_package_versions.version_uri%TYPE,
-      summary                   in apm_package_versions.summary%TYPE,
-      description_format        in apm_package_versions.description_format%TYPE,
-      description               in apm_package_versions.description%TYPE,
-      release_date              in apm_package_versions.release_date%TYPE,
-      vendor                    in apm_package_versions.vendor%TYPE,
-      vendor_uri                in apm_package_versions.vendor_uri%TYPE,
+      new_version_id		in apm_package_versions.version_id%TYPE
+				default null,
+      version_id		in apm_package_versions.version_id%TYPE,
+      version_name		in apm_package_versions.version_name%TYPE 
+				default null,
+      version_uri		in apm_package_versions.version_uri%TYPE,
+      summary			in apm_package_versions.summary%TYPE,
+      description_format	in apm_package_versions.description_format%TYPE,
+      description		in apm_package_versions.description%TYPE,
+      release_date		in apm_package_versions.release_date%TYPE,
+      vendor			in apm_package_versions.vendor%TYPE,
+      vendor_uri		in apm_package_versions.vendor_uri%TYPE,
       auto_mount                in apm_package_versions.auto_mount%TYPE,
-      installed_p               in apm_package_versions.installed_p%TYPE
-                                default 'f',
-      data_model_loaded_p       in apm_package_versions.data_model_loaded_p%TYPE
-                                default 'f'
+      installed_p		in apm_package_versions.installed_p%TYPE
+				default 'f',
+      data_model_loaded_p	in apm_package_versions.data_model_loaded_p%TYPE
+				default 'f'
     ) return apm_package_versions.version_id%TYPE
     is 
       v_version_id apm_package_versions.version_id%TYPE;
@@ -286,37 +289,37 @@
        and version_name = edit.version_name;
        if version_unchanged_p <> 1 then
          v_version_id := copy(
-                         version_id => edit.version_id,
-                         new_version_id => edit.new_version_id,
-                         new_version_name => edit.version_name,
-                         new_version_uri => edit.version_uri
-                        );
+			 version_id => edit.version_id,
+			 new_version_id => edit.new_version_id,
+			 new_version_name => edit.version_name,
+			 new_version_uri => edit.version_uri
+			);
          else 
-           v_version_id := edit.version_id;                     
+	   v_version_id := edit.version_id;			
        end if;
        
        update apm_package_versions 
-                set version_uri = edit.version_uri,
-                summary = edit.summary,
-                description_format = edit.description_format,
-                description = edit.description,
-                release_date = trunc(sysdate),
-                vendor = edit.vendor,
-                vendor_uri = edit.vendor_uri,
+		set version_uri = edit.version_uri,
+		summary = edit.summary,
+		description_format = edit.description_format,
+		description = edit.description,
+		release_date = trunc(sysdate),
+		vendor = edit.vendor,
+		vendor_uri = edit.vendor_uri,
                 auto_mount = edit.auto_mount,
-                installed_p = edit.installed_p,
-                data_model_loaded_p = edit.data_model_loaded_p
-            where version_id = v_version_id;
-        return v_version_id;
+		installed_p = edit.installed_p,
+		data_model_loaded_p = edit.data_model_loaded_p
+	    where version_id = v_version_id;
+	return v_version_id;
     end edit;
 
 -- Add an interface provided by this version.
   function add_interface(
-    interface_id                in apm_package_dependencies.dependency_id%TYPE
-                                default null,
-    version_id                  in apm_package_versions.version_id%TYPE,
-    interface_uri               in apm_package_dependencies.service_uri%TYPE,
-    interface_version           in apm_package_dependencies.service_version%TYPE
+    interface_id		in apm_package_dependencies.dependency_id%TYPE
+			        default null,
+    version_id			in apm_package_versions.version_id%TYPE,
+    interface_uri		in apm_package_dependencies.service_uri%TYPE,
+    interface_version		in apm_package_dependencies.service_version%TYPE
   ) return apm_package_dependencies.dependency_id%TYPE
   is
     v_dep_id apm_package_dependencies.dependency_id%TYPE;
@@ -331,12 +334,12 @@
       (dependency_id, version_id, dependency_type, service_uri, service_version)
       values
       (v_dep_id, add_interface.version_id, 'provides', add_interface.interface_uri,
-        add_interface.interface_version);
+	add_interface.interface_version);
       return v_dep_id;
   end add_interface;
 
   procedure remove_interface(
-    interface_id                in apm_package_dependencies.dependency_id%TYPE
+    interface_id		in apm_package_dependencies.dependency_id%TYPE
   )
   is
   begin
@@ -345,9 +348,9 @@
   end remove_interface;
 
   procedure remove_interface(
-    interface_uri               in apm_package_dependencies.service_uri%TYPE,
-    interface_version           in apm_package_dependencies.service_version%TYPE,
-    version_id                  in apm_package_versions.version_id%TYPE
+    interface_uri		in apm_package_dependencies.service_uri%TYPE,
+    interface_version		in apm_package_dependencies.service_version%TYPE,
+    version_id			in apm_package_versions.version_id%TYPE
   )
   is
       v_dep_id apm_package_dependencies.dependency_id%TYPE;
@@ -361,11 +364,11 @@
   -- Add a requirement for this version.  A requirement is some interface that this
   -- version depends on.
   function add_dependency(
-    dependency_id               in apm_package_dependencies.dependency_id%TYPE
-                                default null,
-    version_id                  in apm_package_versions.version_id%TYPE,
-    dependency_uri              in apm_package_dependencies.service_uri%TYPE,
-    dependency_version          in apm_package_dependencies.service_version%TYPE
+    dependency_id		in apm_package_dependencies.dependency_id%TYPE
+			        default null,
+    version_id			in apm_package_versions.version_id%TYPE,
+    dependency_uri		in apm_package_dependencies.service_uri%TYPE,
+    dependency_version		in apm_package_dependencies.service_version%TYPE
   ) return apm_package_dependencies.dependency_id%TYPE
   is
     v_dep_id apm_package_dependencies.dependency_id%TYPE;
@@ -380,12 +383,12 @@
       (dependency_id, version_id, dependency_type, service_uri, service_version)
       values
       (v_dep_id, add_dependency.version_id, 'requires', add_dependency.dependency_uri,
-        add_dependency.dependency_version);
+	add_dependency.dependency_version);
       return v_dep_id;
   end add_dependency;
 
   procedure remove_dependency(
-    dependency_id               in apm_package_dependencies.dependency_id%TYPE
+    dependency_id		in apm_package_dependencies.dependency_id%TYPE
   )
   is
   begin
@@ -395,9 +398,9 @@
 
 
   procedure remove_dependency(
-    dependency_uri              in apm_package_dependencies.service_uri%TYPE,
-    dependency_version          in apm_package_dependencies.service_version%TYPE,
-    version_id                  in apm_package_versions.version_id%TYPE
+    dependency_uri		in apm_package_dependencies.service_uri%TYPE,
+    dependency_version		in apm_package_dependencies.service_version%TYPE,
+    version_id			in apm_package_versions.version_id%TYPE
   )
   is
     v_dep_id apm_package_dependencies.dependency_id%TYPE;
@@ -409,138 +412,138 @@
   end remove_dependency;
 
    function sortable_version_name (
-    version_name                in apm_package_versions.version_name%TYPE
+    version_name		in apm_package_versions.version_name%TYPE
   ) return varchar2
     is
         a_fields integer;
-        a_start integer;
-        a_end   integer;
-        a_order varchar2(1000);
-        a_char  char(1);
-        a_seen_letter char(1) := 'f';
+	a_start integer;
+	a_end   integer;
+	a_order varchar2(1000);
+	a_char  char(1);
+	a_seen_letter char(1) := 'f';
     begin
         a_fields := 0;
-        a_start := 1;
-        loop
-            a_end := a_start;
+	a_start := 1;
+	loop
+	    a_end := a_start;
     
-            -- keep incrementing a_end until we run into a non-number        
-            while substr(version_name, a_end, 1) >= '0' and substr(version_name, a_end, 1) <= '9' loop
-                a_end := a_end + 1;
-            end loop;
-            if a_end = a_start then
-                return -1;
-                -- raise_application_error(-20000, 'Expected number at position ' || a_start);
-            end if;
-            if a_end - a_start > 4 then
-                return -1;
-                -- raise_application_error(-20000, 'Numbers within versions can only be up to 4 digits long');
-            end if;
+	    -- keep incrementing a_end until we run into a non-number        
+	    while substr(version_name, a_end, 1) >= '0' and substr(version_name, a_end, 1) <= '9' loop
+		a_end := a_end + 1;
+	    end loop;
+	    if a_end = a_start then
+	    	return -1;
+		-- raise_application_error(-20000, 'Expected number at position ' || a_start);
+	    end if;
+	    if a_end - a_start > 4 then
+	    	return -1;
+		-- raise_application_error(-20000, 'Numbers within versions can only be up to 4 digits long');
+	    end if;
     
-            -- zero-pad and append the number
-            a_order := a_order || substr('0000', 1, 4 - (a_end - a_start)) ||
-                substr(version_name, a_start, a_end - a_start) || '.';
+	    -- zero-pad and append the number
+	    a_order := a_order || substr('0000', 1, 4 - (a_end - a_start)) ||
+		substr(version_name, a_start, a_end - a_start) || '.';
             a_fields := a_fields + 1;
-            if a_end > length(version_name) then
-                -- end of string - we're outta here
-                if a_seen_letter = 'f' then
-                    -- append the "final" suffix if there haven't been any letters
-                    -- so far (i.e., not development/alpha/beta)
-                    a_order := a_order || lpad(' ',(7 - a_fields)*5,'0000.') || '  3F.';
-                end if;
-                return a_order;
-            end if;
+	    if a_end > length(version_name) then
+		-- end of string - we're outta here
+		if a_seen_letter = 'f' then
+		    -- append the "final" suffix if there haven't been any letters
+		    -- so far (i.e., not development/alpha/beta)
+		    a_order := a_order || lpad(' ',(7 - a_fields)*5,'0000.') || '  3F.';
+		end if;
+		return a_order;
+	    end if;
     
-            -- what's the next character? if a period, just skip it
-            a_char := substr(version_name, a_end, 1);
-            if a_char = '.' then
-                null;
-            else
-                -- if the next character was a letter, append the appropriate characters
-                if a_char = 'd' then
-                    a_order := a_order || lpad(' ',(7 - a_fields)*5,'0000.') || '  0D.';
-                elsif a_char = 'a' then
-                    a_order := a_order || lpad(' ',(7 - a_fields)*5,'0000.') || '  1A.';
-                elsif a_char = 'b' then
-                    a_order := a_order || lpad(' ',(7 - a_fields)*5,'0000.') || '  2B.';
-                end if;
+	    -- what's the next character? if a period, just skip it
+	    a_char := substr(version_name, a_end, 1);
+	    if a_char = '.' then
+		null;
+	    else
+		-- if the next character was a letter, append the appropriate characters
+		if a_char = 'd' then
+		    a_order := a_order || lpad(' ',(7 - a_fields)*5,'0000.') || '  0D.';
+		elsif a_char = 'a' then
+		    a_order := a_order || lpad(' ',(7 - a_fields)*5,'0000.') || '  1A.';
+		elsif a_char = 'b' then
+		    a_order := a_order || lpad(' ',(7 - a_fields)*5,'0000.') || '  2B.';
+		end if;
     
-                -- can't have something like 3.3a1b2 - just one letter allowed!
-                if a_seen_letter = 't' then
-                    return -1;
-                    -- raise_application_error(-20000, 'Not allowed to have two letters in version name '''
-                    --  || version_name || '''');
-                end if;
-                a_seen_letter := 't';
+		-- can't have something like 3.3a1b2 - just one letter allowed!
+		if a_seen_letter = 't' then
+		    return -1;
+		    -- raise_application_error(-20000, 'Not allowed to have two letters in version name '''
+		    --	|| version_name || '''');
+		end if;
+		a_seen_letter := 't';
     
-                -- end of string - we're done!
-                if a_end = length(version_name) then
-                    return a_order;
-                end if;
-            end if;
-            a_start := a_end + 1;
-        end loop;
+		-- end of string - we're done!
+		if a_end = length(version_name) then
+		    return a_order;
+		end if;
+	    end if;
+	    a_start := a_end + 1;
+	end loop;
     end sortable_version_name;
 
   function version_name_greater(
-    version_name_one            in apm_package_versions.version_name%TYPE,
-    version_name_two            in apm_package_versions.version_name%TYPE
+    version_name_one		in apm_package_versions.version_name%TYPE,
+    version_name_two		in apm_package_versions.version_name%TYPE
   ) return integer is
-        a_order_a varchar2(1000);
-        a_order_b varchar2(1000);
+	a_order_a varchar2(1000);
+	a_order_b varchar2(1000);
     begin
-        a_order_a := sortable_version_name(version_name_one);
-        a_order_b := sortable_version_name(version_name_two);
-        if a_order_a < a_order_b then
-            return -1;
-        elsif a_order_a > a_order_b then
-            return 1;
-        end if;
-        return 0;
+	a_order_a := sortable_version_name(version_name_one);
+	a_order_b := sortable_version_name(version_name_two);
+	if a_order_a < a_order_b then
+	    return -1;
+	elsif a_order_a > a_order_b then
+	    return 1;
+	end if;
+	return 0;
     end version_name_greater;
 
   function upgrade_p(
-    path                        in varchar2,
-    initial_version_name        in apm_package_versions.version_name%TYPE,
-    final_version_name          in apm_package_versions.version_name%TYPE
+    path			in varchar2,
+    initial_version_name	in apm_package_versions.version_name%TYPE,
+    final_version_name		in apm_package_versions.version_name%TYPE
    ) return integer
     is
-        v_pos1 integer;
-        v_pos2 integer;
-        v_path varchar2(1500);
-        v_version_from apm_package_versions.version_name%TYPE;
-        v_version_to apm_package_versions.version_name%TYPE;
+	v_pos1 integer;
+	v_pos2 integer;
+	v_path varchar2(1500);
+	v_version_from apm_package_versions.version_name%TYPE;
+	v_version_to apm_package_versions.version_name%TYPE;
     begin
 
-        -- Set v_path to the tail of the path (the file name).
-        v_path := substr(upgrade_p.path, instr(upgrade_p.path, '/', -1) + 1);
+	-- Set v_path to the tail of the path (the file name).
+	v_path := substr(upgrade_p.path, instr(upgrade_p.path, '/', -1) + 1);
 
-        -- Remove the extension, if it's .sql.
-        v_pos1 := instr(v_path, '.', -1);
-        if v_pos1 > 0 and substr(v_path, v_pos1) = '.sql' then
-            v_path := substr(v_path, 1, v_pos1 - 1);
-        end if;
+	-- Remove the extension, if it's .sql.
+	v_pos1 := instr(v_path, '.', -1);
+	if v_pos1 > 0 and substr(v_path, v_pos1) = '.sql' then
+	    v_path := substr(v_path, 1, v_pos1 - 1);
+	end if;
 
-        -- Figure out the from/to version numbers for the individual file.
-        v_pos1 := instr(v_path, '-', -1, 2);
-        v_pos2 := instr(v_path, '-', -1);
-        if v_pos1 = 0 or v_pos2 = 0 then
-            -- There aren't two hyphens in the file name. Bail.
-            return 0;
-        end if;
+	-- Figure out the from/to version numbers for the individual file.
+	v_pos1 := instr(v_path, '-', -1, 2);
+	v_pos2 := instr(v_path, '-', -1);
+	if v_pos1 = 0 or v_pos2 = 0 then
+	    -- There aren't two hyphens in the file name. Bail.
+	    return 0;
+	end if;
 
-        v_version_from := substr(v_path, v_pos1 + 1, v_pos2 - v_pos1 - 1);
-        v_version_to := substr(v_path, v_pos2 + 1);
+	v_version_from := substr(v_path, v_pos1 + 1, v_pos2 - v_pos1 - 1);
+	v_version_to := substr(v_path, v_pos2 + 1);
 
-        if version_name_greater(upgrade_p.initial_version_name, v_version_from) <= 0 and
-           version_name_greater(upgrade_p.final_version_name, v_version_to) >= 0 then
-            return 1;
-        end if;
+	if version_name_greater(upgrade_p.initial_version_name, v_version_from) <= 0 and
+	   version_name_greater(upgrade_p.final_version_name, v_version_to) >= 0 then
+	    return 1;
+	end if;
 
-        return 0;
+	return 0;
     exception when others then
-        -- Invalid version number.
-        return 0;
+	-- Invalid version number.
+	return 0;
     end upgrade_p;
     
   procedure upgrade(
@@ -549,17 +552,311 @@
   is
   begin
     update apm_package_versions
-        set enabled_p = 'f',
-            installed_p = 'f'
-        where package_key = (select package_key from apm_package_versions
-                             where version_id = upgrade.version_id);
+    	set enabled_p = 'f',
+	    installed_p = 'f'
+	where package_key = (select package_key from apm_package_versions
+	    	    	     where version_id = upgrade.version_id);
     update apm_package_versions
-        set enabled_p = 't',
-            installed_p = 't'
-        where version_id = upgrade.version_id;                    
+    	set enabled_p = 't',
+	    installed_p = 't'
+	where version_id = upgrade.version_id;			  
     
   end upgrade;
 
 end apm_package_version;
 /
 show errors
+
+create or replace package apm_package
+as
+
+function new (
+  package_id		in apm_packages.package_id%TYPE 
+			default null,
+  instance_name		in apm_packages.instance_name%TYPE
+			default null,
+  package_key		in apm_packages.package_key%TYPE,
+  object_type		in acs_objects.object_type%TYPE
+			default 'apm_package', 
+  creation_date		in acs_objects.creation_date%TYPE 
+			default sysdate,
+  creation_user		in acs_objects.creation_user%TYPE 
+			default null,
+  creation_ip		in acs_objects.creation_ip%TYPE 
+			default null,
+  context_id		in acs_objects.context_id%TYPE 
+			default null
+  ) return apm_packages.package_id%TYPE;
+
+  procedure delete (
+   package_id		in apm_packages.package_id%TYPE
+  );
+
+  function initial_install_p (
+	package_key		in apm_packages.package_key%TYPE
+  ) return integer;
+
+  function singleton_p (
+	package_key		in apm_packages.package_key%TYPE
+  ) return integer;
+
+  function num_instances (
+	package_key		in apm_package_types.package_key%TYPE
+  ) return integer;
+
+  function name (
+    package_id		in apm_packages.package_id%TYPE
+  ) return varchar2;
+
+  function highest_version (
+   package_key		in apm_package_types.package_key%TYPE
+  ) return apm_package_versions.version_id%TYPE;
+  
+    function parent_id (
+        package_id in apm_packages.package_id%TYPE
+    ) return apm_packages.package_id%TYPE;
+
+end apm_package;
+/
+show errors
+
+create or replace package body apm_package
+as
+  procedure initialize_parameters (
+    package_id			in apm_packages.package_id%TYPE,
+    package_key		        in apm_package_types.package_key%TYPE
+  )
+  is
+   v_value_id apm_parameter_values.value_id%TYPE;
+   cursor cur is
+       select parameter_id, default_value
+       from apm_parameters
+       where package_key = initialize_parameters.package_key;
+  begin
+    -- need to initialize all params for this type
+    for cur_val in cur
+      loop
+        v_value_id := apm_parameter_value.new(
+          package_id => initialize_parameters.package_id,
+          parameter_id => cur_val.parameter_id,
+          attr_value => cur_val.default_value
+        ); 
+      end loop;   
+  end initialize_parameters;
+
+ function new (
+  package_id		in apm_packages.package_id%TYPE 
+			default null,
+  instance_name		in apm_packages.instance_name%TYPE
+			default null,
+  package_key		in apm_packages.package_key%TYPE,
+  object_type		in acs_objects.object_type%TYPE
+			default 'apm_package', 
+  creation_date		in acs_objects.creation_date%TYPE 
+			default sysdate,
+  creation_user		in acs_objects.creation_user%TYPE 
+			default null,
+  creation_ip		in acs_objects.creation_ip%TYPE 
+			default null,
+  context_id		in acs_objects.context_id%TYPE 
+			default null
+  ) return apm_packages.package_id%TYPE
+  is 
+   v_singleton_p integer;
+   v_package_type apm_package_types.package_type%TYPE;
+   v_num_instances integer;
+   v_package_id apm_packages.package_id%TYPE;
+   v_instance_name apm_packages.instance_name%TYPE; 
+  begin
+   v_singleton_p := apm_package.singleton_p(
+			package_key => apm_package.new.package_key
+		    );
+   v_num_instances := apm_package.num_instances(
+			package_key => apm_package.new.package_key
+		    );
+  
+   if v_singleton_p = 1 and v_num_instances >= 1 then
+       select package_id into v_package_id 
+       from apm_packages
+       where package_key = apm_package.new.package_key;
+       return v_package_id;
+   else
+       v_package_id := acs_object.new(
+          object_id => package_id,
+          object_type => object_type,
+          creation_date => creation_date,
+          creation_user => creation_user,
+	  creation_ip => creation_ip,
+	  context_id => context_id
+	 );
+       if instance_name is null then 
+	 v_instance_name := package_key || ' ' || v_package_id;
+       else
+	 v_instance_name := instance_name;
+       end if;
+
+       select package_type into v_package_type
+       from apm_package_types
+       where package_key = apm_package.new.package_key;
+
+       insert into apm_packages
+       (package_id, package_key, instance_name)
+       values
+       (v_package_id, package_key, v_instance_name);
+
+       if v_package_type = 'apm_application' then
+	   insert into apm_applications
+	   (application_id)
+	   values
+	   (v_package_id);
+       else
+	   insert into apm_services
+	   (service_id)
+	   values
+	   (v_package_id);
+       end if;
+
+       initialize_parameters(
+	   package_id => v_package_id,
+	   package_key => apm_package.new.package_key
+       );
+       return v_package_id;
+
+  end if;
+end new;
+  
+  procedure delete (
+   package_id		in apm_packages.package_id%TYPE
+  )
+  is
+    cursor all_values is
+    	select value_id from apm_parameter_values
+	where package_id = apm_package.delete.package_id;
+    cursor all_site_nodes is
+    	select node_id from site_nodes
+	where object_id = apm_package.delete.package_id;
+  begin
+    -- Delete all parameters.
+    for cur_val in all_values loop
+    	apm_parameter_value.delete(value_id => cur_val.value_id);
+    end loop;    
+    delete from apm_applications where application_id = apm_package.delete.package_id;
+    delete from apm_services where service_id = apm_package.delete.package_id;
+    delete from apm_packages where package_id = apm_package.delete.package_id;
+    -- Delete the site nodes for the objects.
+    for cur_val in all_site_nodes loop
+    	site_node.delete(cur_val.node_id);
+    end loop;
+    -- Delete the object.
+    acs_object.delete (
+	object_id => package_id
+    );
+   end delete;
+
+    function initial_install_p (
+	package_key		in apm_packages.package_key%TYPE
+    ) return integer
+    is
+        v_initial_install_p integer;
+    begin
+        select 1 into v_initial_install_p
+	from apm_package_types
+	where package_key = initial_install_p.package_key
+        and initial_install_p = 't';
+	return v_initial_install_p;
+	
+	exception 
+	    when NO_DATA_FOUND
+            then
+                return 0;
+    end initial_install_p;
+
+    function singleton_p (
+	package_key		in apm_packages.package_key%TYPE
+    ) return integer
+    is
+        v_singleton_p integer;
+    begin
+        select 1 into v_singleton_p
+	from apm_package_types
+	where package_key = singleton_p.package_key
+        and singleton_p = 't';
+	return v_singleton_p;
+	
+	exception 
+	    when NO_DATA_FOUND
+            then
+                return 0;
+    end singleton_p;
+
+    function num_instances (
+	package_key		in apm_package_types.package_key%TYPE
+    ) return integer
+    is
+        v_num_instances integer;
+    begin
+        select count(*) into v_num_instances
+	from apm_packages
+	where package_key = num_instances.package_key;
+        return v_num_instances;
+	
+	exception
+	    when NO_DATA_FOUND
+	    then
+	        return 0;
+    end num_instances;
+
+  function name (
+    package_id		in apm_packages.package_id%TYPE
+  ) return varchar2
+  is
+    v_result apm_packages.instance_name%TYPE;
+  begin
+    select instance_name into v_result
+    from apm_packages
+    where package_id = name.package_id;
+
+    return v_result;
+  end name;
+
+   function highest_version (
+     package_key		in apm_package_types.package_key%TYPE
+   ) return apm_package_versions.version_id%TYPE
+   is
+     v_version_id apm_package_versions.version_id%TYPE;
+   begin
+     select version_id into v_version_id
+	from apm_package_version_info i 
+	where apm_package_version.sortable_version_name(version_name) = 
+             (select max(apm_package_version.sortable_version_name(v.version_name))
+	             from apm_package_version_info v where v.package_key = highest_version.package_key)
+	and package_key = highest_version.package_key;
+     return v_version_id;
+     exception
+         when NO_DATA_FOUND
+         then
+         return 0;
+   end highest_version;
+
+    function parent_id (
+        package_id in apm_packages.package_id%TYPE
+    ) return apm_packages.package_id%TYPE
+    is
+        v_package_id apm_packages.package_id%TYPE;
+    begin
+        select sn1.object_id
+        into v_package_id
+        from site_nodes sn1
+        where sn1.node_id = (select sn2.parent_id
+                             from site_nodes sn2
+                             where sn2.object_id = apm_package.parent_id.package_id);
+
+        return v_package_id;
+
+        exception when NO_DATA_FOUND then
+            return -1;
+    end parent_id;
+
+end apm_package;
+/
+show errors
