CREATE OR REPLACE PROCEDURE HDMS.trash_wl_by_user (trash_user IN varchar2)
AS
	stmt VARCHAR2(500);

BEGIN

DBMS_OUTPUT.PUT_LINE('Deleting all working lists with user '|| trash_user);

/* 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,
						security_users su
					 WHERE	wl.user_id  = su.security_users_id
						and su.security_users_name = trash_user and
						wl.working_list_group_id = wlg.working_list_group_id and
						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,
						security_users su
					 WHERE	wl.user_id  = su.security_users_id
						and su.security_users_name = trash_user and
						wl.working_list_group_id = wlg.working_list_group_id and
						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,
						security_users su
					 WHERE	wl.user_id  = su.security_users_id
						and su.security_users_name = trash_user and
						wl.working_list_group_id = wlg.working_list_group_id and
						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,
						security_users su
					 WHERE	wl.user_id  = su.security_users_id
						and su.security_users_name = trash_user and
						wl.working_list_group_id = wlg.working_list_group_id and
						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,
						security_users su
					 WHERE	wl.user_id  = su.security_users_id
						and su.security_users_name = trash_user and
						wl.working_list_group_id = wlg.working_list_group_id and
						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,
						security_users su
					 WHERE	wl.user_id  = su.security_users_id
						and su.security_users_name = trash_user and
						wl.working_list_group_id = wlg.working_list_group_id and
						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,
						security_users su
					 WHERE	wl.user_id  = su.security_users_id
						and su.security_users_name = trash_user and
						wl.working_list_group_id = wlg.working_list_group_id and
						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,
						security_users su
					 WHERE	wl.user_id  = su.security_users_id
						and su.security_users_name = trash_user and
						wl.working_list_group_id = wlg.working_list_group_id and
						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,
						security_users su
					 WHERE	wl.user_id  = su.security_users_id
						and su.security_users_name = trash_user and
						wl.working_list_group_id = wlg.working_list_group_id and
						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,
						security_users su
					 WHERE	wl.user_id  = su.security_users_id
						and su.security_users_name = trash_user and
						wl.working_list_group_id = wlg.working_list_group_id and
						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;
/