2 -- copy location groups
7 -- This applies locations to copies without regard for the type
8 -- of record of each copy. This leads to nonsensical copy / location
9 -- pairings. (E.g. a violin concerto copy with a "Newspapers" location).
10 -- An improvement would be to create locations first, then select
11 -- locations for copies as they are inserted. Time will tell if there will
12 -- ever be a large enough variety of data to do this in a meaningful way.
13 -- Also, it's kind of a pain, so, maybe later..
15 INSERT INTO asset.copy_location (owning_lib, name) VALUES
25 (2, 'Juvenile Non-Fiction'),
26 (3, 'Juvenile Non-Fiction'),
27 (2, 'Young Adult Fiction'),
28 (4, 'Young Adult Fiction'),
29 (5, 'Young Adult Fiction'),
30 (6, 'Young Adult Fiction'),
31 (2, 'Children''s Fiction'),
32 (3, 'Children''s Fiction'),
38 (4, 'Science Fiction'),
39 (7, 'Science Fiction'),
46 (6, 'Federal Documents');
49 -- non-holdable, non-circulating
50 INSERT INTO asset.copy_location
51 (owning_lib, name, holdable, circulate) VALUES
52 (4, 'Periodicals', FALSE, FALSE),
53 (6, 'Periodicals', FALSE, FALSE),
54 (5, 'Magazines', FALSE, FALSE),
55 (7, 'Magazines', FALSE, FALSE),
56 (4, 'Newspapers', FALSE, FALSE);
59 INSERT INTO asset.copy_location (owning_lib, name, holdable) VALUES
60 (4, 'Reserves', FALSE),
61 (5, 'Reserves', FALSE),
62 (3, 'Reserves', FALSE),
65 (2, 'Special Collections', FALSE),
66 (6, 'Special Collections', FALSE),
67 (7, 'Special Collections', FALSE);
70 -- different settings per org level
71 INSERT INTO asset.copy_location
72 (owning_lib, name, holdable, opac_visible, circulate) VALUES
73 (2, 'Display', FALSE, FALSE, TRUE),
74 (4, 'Display', FALSE, FALSE, FALSE),
75 (5, 'Display', TRUE, FALSE, FALSE),
76 (6, 'Display', TRUE, FALSE, FALSE),
77 (7, 'Display', FALSE, FALSE, FALSE),
78 (1, 'New Arrivals', TRUE, TRUE, TRUE),
79 (2, 'New Arrivals', FALSE, TRUE, TRUE),
80 (4, 'New Arrivals', TRUE, TRUE, FALSE),
81 (5, 'New Arrivals', TRUE, TRUE, TRUE);
83 -- copy location groups
85 INSERT INTO asset.copy_location_group (name, owner)
86 VALUES ('Juvenile Collection', 2);
88 INSERT INTO asset.copy_location_group_map (lgroup, location)
89 SELECT CURRVAL('asset.copy_location_group_id_seq'), id
90 FROM asset.copy_location
91 WHERE owning_lib IN (2, 4, 5) AND
94 'Young Adult Fiction',
95 'Children''s Fiction',
97 'Juvenile Non-Fiction'
100 INSERT INTO asset.copy_location_group (name, owner)
101 VALUES ('Local Interest Collection', 3);
103 INSERT INTO asset.copy_location_group_map (lgroup, location)
104 SELECT CURRVAL('asset.copy_location_group_id_seq'), id
105 FROM asset.copy_location
106 WHERE owning_lib IN (3, 6, 7) AND
110 'Special Collections',
114 -- Distribute copies evenly across copy locations whose owning_lib
115 -- matches the copy circ lib. To provide some level of repeatable
116 -- outcome, we loop instead of applying locations at randon within
117 -- a given owning_lib.
119 DECLARE cur_loc INTEGER;
120 DECLARE cur_copy asset.copy%ROWTYPE;
121 DECLARE cur_cn INTEGER;
126 FOR cur_copy IN SELECT * FROM asset.copy
127 WHERE location = 1 ORDER BY circ_lib, call_number, id LOOP
129 -- Move to the next copy location if we are changing call numbers.
130 -- This provides some visual consistency between call numbers and
131 -- copy locations and helps avoid having practically every copy in
132 -- view residing in a different location.
133 IF cur_cn <> cur_copy.call_number THEN
135 -- find the next location for the current copy's circ lib
136 SELECT INTO cur_loc id FROM asset.copy_location
137 WHERE owning_lib = cur_copy.circ_lib AND id > cur_loc
141 -- start back over at the front of the list
143 SELECT INTO cur_loc id FROM asset.copy_location
144 WHERE owning_lib = cur_copy.circ_lib AND id > cur_loc
149 -- no copy location at this circ lib, leave the default (1)
153 cur_cn := cur_copy.call_number;
156 UPDATE asset.copy SET location = cur_loc WHERE id = cur_copy.id;