CREATE OR REPLACE PROCEDURE trash_wl (trash_date IN varchar2)
AS
	stmt VARCHAR2(500);

/* Here's a stored procedure that will delete any and all  working lists that are a) older than a specified date AND b) are not stored in a folder beginning with the word 'Archive'.   The procedure, hereforth called "The Trasher" will:
1) Accept a date as an argument.  Please use 'mm-dd-yyyy' as your format. 
2) Disable the working list audit triggers.  This is necessary since it would take forever to run otherwise
3) Delete from working list child tables any items from working lists where rec_last_mod_date is BEFORE the input date AND are not in a folder beginning with the word 'Archive'
4) Delete the working list itself from the working_list table
5) Enable the working list triggers
6) Beat a hasty exit.
06/15/04 C. Fogelsong
01/02/07 modified by C. Fogelsong */
	
BEGIN

DBMS_OUTPUT.PUT_LINE('Deleting all working lists older than '|| trash_date);

/* delete the subtypes first */
/* need to disable triggers first or it will take forever to run */

	stmt:= 'alter trigger tr_working_list_element_data_d disable';
	execute immediate stmt;
	stmt:= 'alter trigger tr_working_list_eo_data_d disable';
	execute immediate stmt;
	stmt:= 'alter trigger tr_working_list_sci_data_d disable';
	execute immediate stmt;
	stmt:= 'alter trigger tr_working_list_site_data_d disable';
	execute immediate stmt;
	stmt:= 'alter trigger tr_working_list_sf_data_d disable';
	execute immediate stmt;
	stmt:= 'alter trigger tr_working_list_contact_data_d disable';
	execute immediate stmt;
	stmt:= 'alter trigger tr_working_list_elgroup_data_d disable';
	execute immediate stmt;
	stmt:= 'alter trigger tr_working_list_ref_data_d disable';
	execute immediate stmt;
	stmt:= 'alter trigger tr_working_list_ma_data_d disable';
	execute immediate stmt;

	DELETE FROM working_list_element_data
	WHERE	working_list_id in (SELECT wl.working_list_id
					 FROM	working_list wl,
						d_working_list_group wlg
					 WHERE	wl.rec_last_mod_date < to_date(trash_date, 'mm-dd-yyyy') and
						wl.working_list_group_id = wlg.working_list_group_id and
						UPPER(wlg.working_list_group_desc) NOT LIKE 'ARCHIVE%'	);

	COMMIT;

	


	DELETE FROM working_list_eo_data
	WHERE working_list_id in (SELECT wl.working_list_id
					 FROM	working_list wl,
						d_working_list_group wlg
					 WHERE	wl.rec_last_mod_date < to_date(trash_date, 'mm-dd-yyyy') and
						wl.working_list_group_id = wlg.working_list_group_id and
						UPPER(wlg.working_list_group_desc) NOT LIKE 'ARCHIVE%'	);

	COMMIT;

	
	DELETE FROM working_list_managed_area_data
	WHERE working_list_id in (SELECT wl.working_list_id
					 FROM	working_list wl,
						d_working_list_group wlg
					 WHERE	wl.rec_last_mod_date < to_date(trash_date, 'mm-dd-yyyy')  and
						wl.working_list_group_id = wlg.working_list_group_id and
						UPPER(wlg.working_list_group_desc) NOT LIKE 'ARCHIVE%'	);
	
	COMMIT;

	DELETE FROM working_list_scientific_data
	WHERE working_list_id in (SELECT wl.working_list_id
					 FROM	working_list wl,
						d_working_list_group wlg
					 WHERE	wl.rec_last_mod_date < to_date(trash_date, 'mm-dd-yyyy')  and
						wl.working_list_group_id = wlg.working_list_group_id and
						UPPER(wlg.working_list_group_desc) NOT LIKE 'ARCHIVE%'	);

	COMMIT;

	DELETE FROM working_list_site_data
	WHERE working_list_id in (SELECT wl.working_list_id
					 FROM	working_list wl,
						d_working_list_group wlg
					 WHERE	wl.rec_last_mod_date < to_date(trash_date, 'mm-dd-yyyy')  and
						wl.working_list_group_id = wlg.working_list_group_id and
						UPPER(wlg.working_list_group_desc) NOT LIKE 'ARCHIVE%'	);

	COMMIT;


	
	DELETE FROM working_list_src_feat_data
	WHERE working_list_id in (SELECT wl.working_list_id
					 FROM	working_list wl,
						d_working_list_group wlg
					 WHERE	wl.rec_last_mod_date < to_date(trash_date, 'mm-dd-yyyy')  and
						wl.working_list_group_id = wlg.working_list_group_id and
						UPPER(wlg.working_list_group_desc) NOT LIKE 'ARCHIVE%'	);

	COMMIT;

	
	DELETE FROM working_list_reference_data
		WHERE working_list_id in (SELECT wl.working_list_id
					 FROM	working_list wl,
						d_working_list_group wlg
					 WHERE	wl.rec_last_mod_date < to_date(trash_date, 'mm-dd-yyyy')  and
						wl.working_list_group_id = wlg.working_list_group_id and
						UPPER(wlg.working_list_group_desc) NOT LIKE 'ARCHIVE%'		);

	COMMIT;

	DELETE FROM working_list_contact_data
	WHERE working_list_id in (SELECT wl.working_list_id
					 FROM	working_list wl,
						d_working_list_group wlg
					 WHERE	wl.rec_last_mod_date < to_date(trash_date, 'mm-dd-yyyy')  and
						wl.working_list_group_id = wlg.working_list_group_id and
						UPPER(wlg.working_list_group_desc) NOT LIKE 'ARCHIVE%'	);

	COMMIT;

	DELETE FROM working_list_elgroup_data
	WHERE working_list_id in (SELECT wl.working_list_id
					 FROM	working_list wl,
						d_working_list_group wlg
					 WHERE	wl.rec_last_mod_date < to_date(trash_date, 'mm-dd-yyyy')  and
						wl.working_list_group_id = wlg.working_list_group_id and
						UPPER(wlg.working_list_group_desc) NOT LIKE 'ARCHIVE%'	);

/* now delete from main table */

	DELETE FROM working_list
	WHERE working_list_id in (SELECT wl.working_list_id
					 FROM	working_list wl,
						d_working_list_group wlg
					 WHERE	wl.rec_last_mod_date < to_date(trash_date, 'mm-dd-yyyy')  and
						wl.working_list_group_id = wlg.working_list_group_id and
						UPPER(wlg.working_list_group_desc) NOT LIKE 'ARCHIVE%'	);

	COMMIT;
	
/* re-enable the triggers */
	stmt:= 'alter trigger tr_working_list_element_data_d enable';
	execute immediate stmt;
	stmt:= 'alter trigger tr_working_list_eo_data_d enable';
	execute immediate stmt;
	stmt:= 'alter trigger tr_working_list_sci_data_d enable';
	execute immediate stmt;
	stmt:= 'alter trigger tr_working_list_site_data_d enable';
	execute immediate stmt;
	stmt:= 'alter trigger tr_working_list_sf_data_d enable';
	execute immediate stmt;
	stmt:= 'alter trigger tr_working_list_contact_data_d enable';
	execute immediate stmt;
	stmt:= 'alter trigger tr_working_list_elgroup_data_d enable';
	execute immediate stmt;
	stmt:= 'alter trigger tr_working_list_ref_data_d enable';
	execute immediate stmt;
	stmt:= 'alter trigger tr_working_list_ma_data_d enable';
	execute immediate stmt;
	
END;
/