--- /dev/null
+/*
+ * Copyright (C) 2011 Equinox Software, Inc.
+ * Galen Charlton <gmc@esilibrary.com>
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU General Public License
+ * as published by the Free Software Foundation; either version 2
+ * of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ * GNU General Public License for more details.
+ *
+ */
+
+DROP SCHEMA IF EXISTS munge CASCADE;
+
+BEGIN;
+
+CREATE SCHEMA munge;
+
+COMMENT ON SCHEMA munge IS $$
+munge - Evergreen database utilities
+
+The schema munge contains stored procedures used for maintaining
+an Evergreen database, including migration and data update
+routines. Routines in munge will not necessarily have a
+user interface exposed in the Evergreen staff client or OPAC,
+and will typically be invoked from the command line. However,
+other Evergreen application code is free to make use of munge.
+$$;
+
+-- original version of routine proposed by Ben Ostrowsky on 2011-04-13
+CREATE OR REPLACE FUNCTION munge.change_copy_call_number(copy_id asset.copy.id%TYPE, new_label asset.call_number.label%TYPE) RETURNS BOOLEAN AS $$
+DECLARE
+ current_acn asset.call_number%ROWTYPE;
+ new_acn_id asset.call_number.id%TYPE;
+BEGIN
+
+ -- bail out if new label is invalid; we will not touch
+ -- located URIs in this routine
+ IF new_label = '##URI##' OR new_label IS NULL THEN
+ RETURN false;
+ END IF;
+
+ -- get current volume
+ SELECT acn.* INTO current_acn
+ FROM asset.copy ac
+ JOIN asset.call_number acn ON (acn.id = ac.call_number)
+ WHERE ac.id = copy_id;
+ IF NOT FOUND THEN
+ RETURN false;
+ END IF;
+
+ -- bail out if the label already is ##URI##
+ IF current_acn.label = '##URI##' THEN
+ RETURN false;
+ END IF;
+
+ -- bail out if the call number label is already correct
+ IF current_acn.label = new_label THEN
+ RETURN false;
+ END IF;
+
+ -- bail out if we're a precat
+ IF current_acn.id = -1 THEN
+ RETURN false;
+ END IF;
+
+ -- Check whether we already have a destination volume available
+ SELECT id INTO new_acn_id FROM asset.call_number
+ WHERE
+ record = current_acn.record AND
+ owning_lib = current_acn.owning_lib AND
+ label = new_label AND
+ NOT deleted;
+
+ -- Create destination volume if needed
+ IF NOT FOUND THEN
+ -- if at least on other item is attached to the current volume, create a
+ -- new volume so as to not change the the other item's call number label, otherwise
+ -- change the label in place
+ PERFORM 1 FROM asset.copy WHERE NOT deleted AND id <> copy_id AND call_number = current_acn.id;
+ IF FOUND THEN
+ INSERT INTO asset.call_number (creator, editor, record, owning_lib, label)
+ VALUES (1, 1, current_acn.record, current_acn.owning_lib, new_label);
+ SELECT CURRVAL('asset.call_number_id_seq') INTO new_acn_id;
+ ELSE
+ UPDATE asset.call_number
+ SET editor = 1,
+ label = new_label,
+ edit_date = NOW()
+ WHERE id = current_acn.id;
+ END IF;
+ END IF;
+
+ IF new_acn_id IS NOT NULL THEN
+ -- Move copy to destination
+ UPDATE asset.copy SET call_number = new_acn_id WHERE id = copy_id;
+
+ -- Delete source volume if it is now empty
+ PERFORM 1 FROM asset.copy WHERE call_number = current_acn.id AND NOT deleted;
+ IF NOT FOUND THEN
+ DELETE FROM asset.call_number WHERE id = current_acn.id;
+ END IF;
+ END IF;
+
+ RETURN true;
+END;
+$$ LANGUAGE PLPGSQL;
+
+COMMENT ON FUNCTION munge.change_copy_call_number(asset.copy.id%TYPE, asset.call_number.label%TYPE) IS $$
+Change the call number label associated with the specified item,
+creating, relinking, and deleting volume records as needed. The
+call number associated with any other items attached to the target
+item's original volume will remain unchanged.
+
+Accepts the following parameters:
+
+copy_id - asset.copy.id of the copy to change
+new_label - new call number string
+
+The return value is a boolean indicating whether the copy call
+number was changed or not.
+
+This routine will refuse to touch or create located URIs.
+$$;
+
+COMMIT;