begin
  acs_object_type.create_type(
    object_type => 'wf_ticket',
    pretty_name => 'Ticket',
    pretty_plural => 'Tickets',
    table_name => 'wf_tickets',
    id_column => 'ticket_id',
    package_name => 'wf_ticket',
    name_method => 'wf_ticket.name'
  );

  -- XXX fill in all the attributes in later.
end;
/
show errors

create table wf_tickets (
  ticket_id		integer
			constraint wf_tickets_pk
			primary key
			constraint wf_tickets_acs_object_fk
			references acs_objects on delete cascade,
  subject		varchar(400),
  description		varchar(4000),
  severity		integer,
  priority		integer
);

create or replace package wf_ticket
is
    function new (
	ticket_id	in wf_tickets.ticket_id%TYPE default null,
        subject		in wf_tickets.subject%TYPE,
        description     in wf_tickets.description%TYPE,
	severity	in wf_tickets.severity%TYPE default 3,
	priority 	in wf_tickets.priority%TYPE default 3,
	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
    ) return integer;

    procedure delete (
	ticket_id 	in wf_tickets.ticket_id%TYPE
    );

    function name (
	ticket_id	in wf_tickets.ticket_id%TYPE
    ) return varchar;
end;
/
show errors;


create or replace package body wf_ticket
is
    function new (
	ticket_id	in wf_tickets.ticket_id%TYPE default null,
        subject		in wf_tickets.subject%TYPE,
        description     in wf_tickets.description%TYPE,
	severity	in wf_tickets.severity%TYPE default 3,
	priority 	in wf_tickets.priority%TYPE default 3,
	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
    ) return integer
    is
        v_ticket_id number;
	v_case_id number;
    begin
        v_ticket_id := acs_object.new(
	    object_id => ticket_id,	
	    object_type => 'wf_ticket',
	    creation_date => new.creation_date,
	    creation_user => new.creation_user,
	    creation_ip	=> new.creation_ip
        );

        insert into wf_tickets (ticket_id, subject, description, severity, priority)
	values (v_ticket_id, new.subject, new.description, new.severity, new.priority);

	v_case_id := workflow_case.new(
	    workflow_key => 'ticket_wf',
	    object_id => v_ticket_id,
	    creation_date => new.creation_date,
	    creation_user => new.creation_user,
	    creation_ip => new.creation_ip
        );
 	
	workflow_case.start_case(
	    case_id => v_case_id,
	    creation_user => new.creation_user,
	    creation_ip => new.creation_ip
        );
	
        return v_ticket_id;
    end;

    procedure delete (
	ticket_id 	in wf_tickets.ticket_id%TYPE
    )
    is
    begin
	delete from wf_tickets where ticket_id = wf_ticket.delete.ticket_id;
        acs_object.delete(wf_ticket.delete.ticket_id);
    end;

    function name (
	ticket_id	in wf_tickets.ticket_id%TYPE
    ) return varchar
    is
        v_subject wf_tickets.subject%TYPE;
    begin
	select subject into v_subject
	from   wf_tickets
	where  ticket_id = name.ticket_id;
	return v_subject;
    end;

end;
/
show errors;