--
-- packages/ticket-tracker/sql/ttracker-drop.sql
--
-- 
-- @author Phong Nguyen (phong@arsdigita.com)
-- @author Tony Tseng (tony@arsidigta.com)
-- 
-- @creation-date 2000-11-15
--
-- @cvs-id $Id: ticket-tracker-drop.sql,v 1.1 2002/07/09 17:35:13 rmello Exp $
--

-- drop the workflow process
@@ ttracker-workflow-drop.sql

declare
    -- the cursor to iterate through all comments on tickets
    cursor comment_cur is
        select gc.comment_id 
	  from general_comments gc,
	       ttracker_tickets tt
	 where gc.object_id = tt.ticket_id;

    -- the curser to iterate through all tickets
    cursor ticket_cur is
        select ticket_id
          from ttracker_tickets;
    -- the cursor to iterate through all categories
    cursor category_cur is
        select category_id
          from ttracker_categories;
begin   
    -- delete all comments on tickets 	        
    for comment_rec in comment_cur loop
        acs_message.delete(comment_rec.comment_id);
    end loop;

    -- delete all tickets
    for ticket_rec in ticket_cur loop
        ttracker_ticket.delete(ticket_rec.ticket_id);
    end loop;

    -- delete all categories
    for category_rec in category_cur loop
        ttracker_category.delete(category_rec.category_id);
    end loop;
        
    acs_object_type.drop_type ('ttracker_ticket');
    acs_object_type.drop_type ('ttracker_category');
end;
/
show errors

drop table ttracker_tickets;
drop table ttracker_categories;
drop table ttracker_options;

drop sequence ttracker_option_id_sequence;

drop package ttracker_util;
drop package ttracker_callback;
drop package ttracker_category;
drop package ttracker_option;
drop package ttracker_ticket;