create or replace package us_selection
as
	function new (
		 selection_id		in us_selections.selection_id%TYPE default null,
		 package_id		in us_selections.package_id%TYPE default null,
		 title			in us_selections.title%TYPE,
		 description		in us_selections.description%TYPE default null,
		 full_sql		in us_selections.full_sql%TYPE default null,
		 bind_vars		in us_selections.bind_vars%TYPE default null,
		 context_id		in acs_objects.context_id%TYPE default null,
		 creation_user		in acs_objects.creation_user%TYPE default null,
		 creation_date		in acs_objects.creation_date%TYPE default sysdate,
		 creation_ip		in acs_objects.creation_ip%TYPE default null
	) return integer;

	procedure delete (
		 selection_id		in us_selections.selection_id%TYPE
	);

	function name (
		 selection_id		in us_selections.selection_id%TYPE
	) return varchar2;

	procedure edit (
		 selection_id		in us_selections.selection_id%TYPE,
		 title			in us_selections.title%TYPE,
		 description		in us_selections.description%TYPE default null,
		 full_sql		in us_selections.full_sql%TYPE default null,
		 bind_vars		in us_selections.bind_vars%TYPE default null,
		 modifying_user		in acs_objects.modifying_user%TYPE default null,
		 modifying_ip		in acs_objects.modifying_ip%TYPE default null
	);
end us_selection;
/
show errors


create or replace package body us_selection
as
	function new (
		 selection_id		in us_selections.selection_id%TYPE default null,
		 package_id		in us_selections.package_id%TYPE default null,
		 title			in us_selections.title%TYPE,
		 description		in us_selections.description%TYPE default null,
		 full_sql		in us_selections.full_sql%TYPE default null,
		 bind_vars		in us_selections.bind_vars%TYPE default null,
		 context_id		in acs_objects.context_id%TYPE default null,
		 creation_user		in acs_objects.creation_user%TYPE default null,
		 creation_date		in acs_objects.creation_date%TYPE default sysdate,
		 creation_ip		in acs_objects.creation_ip%TYPE default null
	) return integer
	is
		v_selection_id	us_selections.selection_id%TYPE;
	begin
                v_selection_id := acs_object.new (
                        object_id       =>      new.selection_id,
                        object_type     =>      'us_selection',
                        creation_date   =>      new.creation_date,
                        creation_user   =>      new.creation_user,
                        creation_ip     =>      new.creation_ip,
                        context_id      =>      new.context_id
                );

		insert into acs_named_objects (object_id, object_name, package_id) 
        	 values (v_selection_id, title, package_id);

		insert into us_selections
		       (selection_id, package_id, title, description, full_sql, bind_vars)
		values
		       (v_selection_id, package_id, title, description, full_sql, bind_vars);
		return v_selection_id;
	end new;

	procedure delete (
		 selection_id		in us_selections.selection_id%TYPE
	) is
	begin
		delete from acs_objects where context_id = us_selection.delete.selection_id;
		delete from us_selections where selection_id = us_selection.delete.selection_id;
		acs_object.delete(selection_id);
	end delete;

	function name (
		 selection_id		in us_selections.selection_id%TYPE
	) return varchar2
	is
		v_title	us_selections.title%TYPE;
	begin
		select title into v_title
		from us_selections
		where selection_id = name.selection_id;

		return v_title;
	end name;

	procedure edit (
		 selection_id		in us_selections.selection_id%TYPE,
		 title			in us_selections.title%TYPE,
		 description		in us_selections.description%TYPE default null,
		 full_sql		in us_selections.full_sql%TYPE default null,
		 bind_vars		in us_selections.bind_vars%TYPE default null,
		 modifying_user		in acs_objects.modifying_user%TYPE default null,
		 modifying_ip		in acs_objects.modifying_ip%TYPE default null
	) is
	begin
		update us_selections
		set title = edit.title,
		    description = edit.description,
		    full_sql = edit.full_sql,
		    bind_vars = edit.bind_vars
		where selection_id = edit.selection_id;

		update acs_named_objects
		set object_name = edit.title
		where object_id = edit.selection_id;

		update acs_objects
		set modifying_user = edit.modifying_user,
		    modifying_ip = edit.modifying_ip
		where object_id = edit.selection_id;
	end edit;
end us_selection;
/
show errors