1 --Upgrade Script for 2.7.4 to 2.8.0
2 \set eg_version '''2.8.0'''
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.8.0', :eg_version);
6 SELECT evergreen.upgrade_deps_block_check('0902', :eg_version);
8 CREATE OR REPLACE FUNCTION action.hold_request_clear_map () RETURNS TRIGGER AS $$
10 DELETE FROM action.hold_copy_map WHERE hold = NEW.id;
15 CREATE TRIGGER hold_request_clear_map_tgr
16 AFTER UPDATE ON action.hold_request
19 (NEW.cancel_time IS NOT NULL AND OLD.cancel_time IS NULL)
20 OR (NEW.fulfillment_time IS NOT NULL AND OLD.fulfillment_time IS NULL)
22 EXECUTE PROCEDURE action.hold_request_clear_map();
26 SELECT evergreen.upgrade_deps_block_check('0903', :eg_version);
28 INSERT INTO config.org_unit_setting_type
29 (name, grp, label, description, datatype)
31 ('circ.void_lost_on_claimsreturned',
33 oils_i18n_gettext('circ.void_lost_on_claimsreturned',
34 'Void lost item billing when claims returned',
36 oils_i18n_gettext('circ.void_lost_on_claimsreturned',
37 'Void lost item billing when claims returned',
38 'coust', 'description'),
40 ('circ.void_lost_proc_fee_on_claimsreturned',
42 oils_i18n_gettext('circ.void_lost_proc_fee_on_claimsreturned',
43 'Void lost item processing fee when claims returned',
45 oils_i18n_gettext('circ.void_lost_proc_fee_on_claimsreturned',
46 'Void lost item processing fee when claims returned',
47 'coust', 'description'),
50 INSERT INTO config.org_unit_setting_type
51 (name, grp, label, description, datatype)
53 ('circ.void_longoverdue_on_claimsreturned',
55 oils_i18n_gettext('circ.void_longoverdue_on_claimsreturned',
56 'Void long overdue item billing when claims returned',
58 oils_i18n_gettext('circ.void_longoverdue_on_claimsreturned',
59 'Void long overdue item billing when claims returned',
60 'coust', 'description'),
62 ('circ.void_longoverdue_proc_fee_on_claimsreturned',
64 oils_i18n_gettext('circ.void_longoverdue_proc_fee_on_claimsreturned',
65 'Void long overdue item processing fee when claims returned',
67 oils_i18n_gettext('circ.void_longoverdue_proc_fee_on_claimsreturned',
68 'Void long overdue item processing fee when claims returned',
69 'coust', 'description'),
73 SELECT evergreen.upgrade_deps_block_check('0907', :eg_version);
75 INSERT into config.org_unit_setting_type
76 ( name, grp, label, description, datatype ) VALUES
78 ( 'circ.checkin.lost_zero_balance.do_not_change',
80 'Do not change fines/fees on zero-balance LOST transaction',
81 'When an item has been marked lost and all fines/fees have been completely paid on the transaction, do not void or reinstate any fines/fees EVEN IF circ.void_lost_on_checkin and/or circ.void_lost_proc_fee_on_checkin are enabled',
86 SELECT evergreen.upgrade_deps_block_check('0909', :eg_version);
88 ALTER TABLE vandelay.authority_match
89 ADD COLUMN match_score INT NOT NULL DEFAULT 0;
91 -- support heading=TRUE match set points
92 ALTER TABLE vandelay.match_set_point
93 ADD COLUMN heading BOOLEAN NOT NULL DEFAULT FALSE,
94 DROP CONSTRAINT vmsp_need_a_tag_or_a_ff_or_a_bo,
95 ADD CONSTRAINT vmsp_need_a_tag_or_a_ff_or_a_heading_or_a_bo
97 (tag IS NOT NULL AND svf IS NULL AND heading IS FALSE AND bool_op IS NULL) OR
98 (tag IS NULL AND svf IS NOT NULL AND heading IS FALSE AND bool_op IS NULL) OR
99 (tag IS NULL AND svf IS NULL AND heading IS TRUE AND bool_op IS NULL) OR
100 (tag IS NULL AND svf IS NULL AND heading IS FALSE AND bool_op IS NOT NULL)
103 CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set(
104 match_set_id INTEGER,
109 root vandelay.match_set_point;
111 SELECT * INTO root FROM vandelay.match_set_point
112 WHERE parent IS NULL AND match_set = match_set_id;
114 RETURN vandelay.get_expr_from_match_set_point(
115 root, tags_rstore, auth_heading);
119 -- backwards compat version so we don't have
120 -- to modify vandelay.match_set_test_marcxml()
121 CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set(
122 match_set_id INTEGER,
126 RETURN vandelay.get_expr_from_match_set(
127 match_set_id, tags_rstore, NULL);
132 DROP FUNCTION IF EXISTS
133 vandelay.get_expr_from_match_set_point(vandelay.match_set_point, HSTORE);
135 CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set_point(
136 node vandelay.match_set_point,
145 child vandelay.match_set_point;
147 SELECT ARRAY_AGG(id) INTO children FROM vandelay.match_set_point
148 WHERE parent = node.id;
150 IF ARRAY_LENGTH(children, 1) > 0 THEN
151 this_op := vandelay._get_expr_render_one(node);
154 WHILE children[i] IS NOT NULL LOOP
155 SELECT * INTO child FROM vandelay.match_set_point
156 WHERE id = children[i];
158 q := q || ' ' || this_op || ' ';
161 q := q || vandelay.get_expr_from_match_set_point(
162 child, tags_rstore, auth_heading);
166 ELSIF node.bool_op IS NULL THEN
167 PERFORM vandelay._get_expr_push_qrow(node);
168 PERFORM vandelay._get_expr_push_jrow(node, tags_rstore, auth_heading);
169 RETURN vandelay._get_expr_render_one(node);
177 DROP FUNCTION IF EXISTS
178 vandelay._get_expr_push_jrow(vandelay.match_set_point, HSTORE);
180 CREATE OR REPLACE FUNCTION vandelay._get_expr_push_jrow(
181 node vandelay.match_set_point,
196 -- remember $1 is tags_rstore, and $2 is svf_rstore
197 -- a non-NULL auth_heading means we're matching authority records
199 IF auth_heading IS NOT NULL THEN
200 rec_table := 'authority.full_rec';
202 rec_table := 'metabib.full_rec';
206 SELECT COUNT(*) INTO jrow_count FROM _vandelay_tmp_jrows;
207 IF jrow_count > 0 THEN
208 my_using := ' USING (record)';
209 my_join := 'FULL OUTER JOIN';
215 IF node.tag IS NOT NULL THEN
216 caseless := (node.tag IN ('020', '022', '024'));
218 IF node.subfield IS NOT NULL THEN
219 tagkey := tagkey || node.subfield;
237 my_alias := 'n' || node.id::TEXT;
239 jrow := my_join || ' (SELECT *, ';
240 IF node.tag IS NOT NULL THEN
241 jrow := jrow || node.quality ||
242 ' AS quality FROM ' || rec_table || ' mfr WHERE mfr.tag = ''' ||
244 IF node.subfield IS NOT NULL THEN
245 jrow := jrow || ' AND mfr.subfield = ''' ||
246 node.subfield || '''';
248 jrow := jrow || ' AND (';
249 jrow := jrow || vandelay._node_tag_comparisons(caseless, op, tags_rstore, tagkey);
250 jrow := jrow || ')) ' || my_alias || my_using || E'\n';
252 IF auth_heading IS NOT NULL THEN -- authority record
253 IF node.heading AND auth_heading <> '' THEN
254 jrow := jrow || 'id AS record, ' || node.quality ||
255 ' AS quality FROM authority.record_entry are ' ||
256 ' WHERE are.heading = ''' || auth_heading || '''';
257 jrow := jrow || ') ' || my_alias || my_using || E'\n';
260 jrow := jrow || 'id AS record, ' || node.quality ||
261 ' AS quality FROM metabib.record_attr_flat mraf WHERE mraf.attr = ''' ||
262 node.svf || ''' AND mraf.value ' || op || ' $2->''' || node.svf || ''') ' ||
263 my_alias || my_using || E'\n';
266 INSERT INTO _vandelay_tmp_jrows (j) VALUES (jrow);
271 CREATE OR REPLACE FUNCTION vandelay.match_set_test_authxml(
272 match_set_id INTEGER, record_xml TEXT
273 ) RETURNS SETOF vandelay.match_set_test_result AS $$
284 tags_rstore := vandelay.flatten_marc_hstore(record_xml);
286 SELECT normalize_heading INTO heading
287 FROM authority.normalize_heading(record_xml);
289 CREATE TEMPORARY TABLE _vandelay_tmp_qrows (q INTEGER);
290 CREATE TEMPORARY TABLE _vandelay_tmp_jrows (j TEXT);
292 -- generate the where clause and return that directly (into wq), and as
293 -- a side-effect, populate the _vandelay_tmp_[qj]rows tables.
294 wq := vandelay.get_expr_from_match_set(
295 match_set_id, tags_rstore, heading);
297 query_ := 'SELECT DISTINCT(record), ';
299 -- qrows table is for the quality bits we add to the SELECT clause
301 'COALESCE(n' || q::TEXT || '.quality, 0)', ' + '
302 ) INTO coal FROM _vandelay_tmp_qrows;
304 -- our query string so far is the SELECT clause and the inital FROM.
305 -- no JOINs yet nor the WHERE clause
306 query_ := query_ || coal || ' AS quality ' || E'\n';
308 -- jrows table is for the joins we must make (and the real text conditions)
309 SELECT STRING_AGG(j, E'\n') INTO joins
310 FROM _vandelay_tmp_jrows;
312 -- add those joins and the where clause to our query.
313 query_ := query_ || joins || E'\n';
315 query_ := query_ || 'JOIN authority.record_entry are ON (are.id = record) '
316 || 'WHERE ' || wq || ' AND not are.deleted';
318 -- this will return rows of record,quality
319 FOR rec IN EXECUTE query_ USING tags_rstore LOOP
323 DROP TABLE _vandelay_tmp_qrows;
324 DROP TABLE _vandelay_tmp_jrows;
329 CREATE OR REPLACE FUNCTION vandelay.measure_auth_record_quality
330 ( xml TEXT, match_set_id INT ) RETURNS INT AS $_$
334 test vandelay.match_set_quality%ROWTYPE;
337 FOR test IN SELECT * FROM vandelay.match_set_quality
338 WHERE match_set = match_set_id LOOP
339 IF test.tag IS NOT NULL THEN
340 FOR rvalue IN SELECT value FROM vandelay.flatten_marc( xml )
341 WHERE tag = test.tag AND subfield = test.subfield LOOP
342 IF test.value = rvalue THEN
343 out_q := out_q + test.quality;
351 $_$ LANGUAGE PLPGSQL;
355 CREATE OR REPLACE FUNCTION vandelay.match_authority_record() RETURNS TRIGGER AS $func$
357 incoming_existing_id TEXT;
358 test_result vandelay.match_set_test_result%ROWTYPE;
362 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
366 DELETE FROM vandelay.authority_match WHERE queued_record = NEW.id;
368 SELECT q.match_set INTO match_set FROM vandelay.authority_queue q WHERE q.id = NEW.queue;
370 IF match_set IS NOT NULL THEN
371 NEW.quality := vandelay.measure_auth_record_quality( NEW.marc, match_set );
374 -- Perfect matches on 901$c exit early with a match with high quality.
375 incoming_existing_id :=
376 oils_xpath_string('//*[@tag="901"]/*[@code="c"][1]', NEW.marc);
378 IF incoming_existing_id IS NOT NULL AND incoming_existing_id != '' THEN
379 SELECT id INTO tmp_rec FROM authority.record_entry WHERE id = incoming_existing_id::bigint;
380 IF tmp_rec IS NOT NULL THEN
381 INSERT INTO vandelay.authority_match (queued_record, eg_record, match_score, quality)
386 -- note: no match_set means quality==0
387 vandelay.measure_auth_record_quality( b.marc, match_set )
388 FROM authority.record_entry b
389 WHERE id = incoming_existing_id::bigint;
393 IF match_set IS NULL THEN
397 FOR test_result IN SELECT * FROM
398 vandelay.match_set_test_authxml(match_set, NEW.marc) LOOP
400 INSERT INTO vandelay.authority_match ( queued_record, eg_record, match_score, quality )
405 vandelay.measure_auth_record_quality( b.marc, match_set )
406 FROM authority.record_entry b
407 WHERE id = test_result.record;
413 $func$ LANGUAGE PLPGSQL;
415 CREATE TRIGGER zz_match_auths_trigger
416 BEFORE INSERT OR UPDATE ON vandelay.queued_authority_record
417 FOR EACH ROW EXECUTE PROCEDURE vandelay.match_authority_record();
419 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_record_with_best ( import_id BIGINT, merge_profile_id INT, lwm_ratio_value_p NUMERIC ) RETURNS BOOL AS $$
422 lwm_ratio_value NUMERIC;
425 lwm_ratio_value := COALESCE(lwm_ratio_value_p, 0.0);
427 PERFORM * FROM vandelay.queued_authority_record WHERE import_time IS NOT NULL AND id = import_id;
430 -- RAISE NOTICE 'already imported, cannot auto-overlay'
434 SELECT m.eg_record INTO eg_id
435 FROM vandelay.authority_match m
436 JOIN vandelay.queued_authority_record qr ON (m.queued_record = qr.id)
437 JOIN vandelay.authority_queue q ON (qr.queue = q.id)
438 JOIN authority.record_entry r ON (r.id = m.eg_record)
439 WHERE m.queued_record = import_id
440 AND qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC >= lwm_ratio_value
441 ORDER BY m.match_score DESC, -- required match score
442 qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC DESC, -- quality tie breaker
443 m.id -- when in doubt, use the first match
446 IF eg_id IS NULL THEN
447 -- RAISE NOTICE 'incoming record is not of high enough quality';
451 RETURN vandelay.overlay_authority_record( import_id, eg_id, merge_profile_id );
457 SELECT evergreen.upgrade_deps_block_check('0910', :eg_version);
459 CREATE TABLE actor.usr_message (
460 id SERIAL PRIMARY KEY,
461 usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
463 message TEXT NOT NULL,
464 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
465 deleted BOOL NOT NULL DEFAULT FALSE,
466 read_date TIMESTAMP WITH TIME ZONE,
467 sending_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
469 CREATE INDEX aum_usr ON actor.usr_message (usr);
471 CREATE RULE protect_usr_message_delete AS
472 ON DELETE TO actor.usr_message DO INSTEAD (
473 UPDATE actor.usr_message
475 WHERE OLD.id = actor.usr_message.id
478 ALTER TABLE action_trigger.event_definition
479 ADD COLUMN message_template TEXT,
480 ADD COLUMN message_usr_path TEXT,
481 ADD COLUMN message_library_path TEXT,
482 ADD COLUMN message_title TEXT;
484 CREATE FUNCTION actor.convert_usr_note_to_message () RETURNS TRIGGER AS $$
487 IF TG_OP = 'UPDATE' THEN
488 IF OLD.pub = TRUE THEN
493 INSERT INTO actor.usr_message (usr, title, message, sending_lib)
494 VALUES (NEW.usr, NEW.title, NEW.value, (SELECT home_ou FROM actor.usr WHERE id = NEW.creator));
501 CREATE TRIGGER convert_usr_note_to_message_tgr
502 AFTER INSERT OR UPDATE ON actor.usr_note
503 FOR EACH ROW EXECUTE PROCEDURE actor.convert_usr_note_to_message();
505 CREATE VIEW actor.usr_message_limited
506 AS SELECT * FROM actor.usr_message;
508 CREATE FUNCTION actor.restrict_usr_message_limited () RETURNS TRIGGER AS $$
510 IF TG_OP = 'UPDATE' THEN
511 UPDATE actor.usr_message
512 SET read_date = NEW.read_date,
513 deleted = NEW.deleted
521 CREATE TRIGGER restrict_usr_message_limited_tgr
522 INSTEAD OF UPDATE OR INSERT OR DELETE ON actor.usr_message_limited
523 FOR EACH ROW EXECUTE PROCEDURE actor.restrict_usr_message_limited();
525 -- and copy over existing public user notes as (read) patron messages
526 INSERT INTO actor.usr_message (usr, title, message, sending_lib, create_date, read_date)
527 SELECT aun.usr, title, value, home_ou, aun.create_date, NOW()
528 FROM actor.usr_note aun
529 JOIN actor.usr au ON (au.id = aun.usr)
534 SELECT evergreen.upgrade_deps_block_check('0911', :eg_version);
536 -- Auto-cancelled, no target
537 INSERT INTO action_trigger.event_definition (
538 id, active, owner, name, hook,
539 validator, reactor, delay, delay_field,
540 group_field, message_usr_path, message_library_path, message_title,
543 51, FALSE, 1, 'Hold Cancelled (No Target) User Message', 'hold_request.cancel.expire_no_target',
544 'HoldIsCancelled', 'NOOP_True', '30 minutes', 'cancel_time',
545 'usr', 'usr', 'usr.home_ou', 'Hold Request Cancelled',
548 [%- user = target.0.usr -%]
549 The following holds were cancelled because no items were found to fullfil them.
551 [% FOR hold IN target %]
552 Title: [% hold.bib_rec.bib_record.simple_record.title %]
553 Author: [% hold.bib_rec.bib_record.simple_record.author %]
554 Library: [% hold.pickup_lib.name %]
555 Request Date: [% date.format(helpers.format_date(hold.request_time), '%Y-%m-%d') %]
560 INSERT INTO action_trigger.environment (event_def, path) VALUES
563 (51, 'bib_rec.bib_record.simple_record');
566 -- Cancelled by staff
567 INSERT INTO action_trigger.event_definition (
568 id, active, owner, name, hook,
569 validator, reactor, delay, delay_field,
570 group_field, message_usr_path, message_library_path, message_title,
573 52, FALSE, 1, 'Hold Cancelled (Staff) User Message', 'hold_request.cancel.staff',
574 'HoldIsCancelled', 'NOOP_True', '30 minutes', 'cancel_time',
575 'usr', 'usr', 'usr.home_ou', 'Hold Request Cancelled',
578 [%- user = target.0.usr -%]
579 The following holds were cancelled by a staff member.
581 [% FOR hold IN target %]
582 Title: [% hold.bib_rec.bib_record.simple_record.title %]
583 Author: [% hold.bib_rec.bib_record.simple_record.author %]
584 Library: [% hold.pickup_lib.name %]
585 Request Date: [% date.format(helpers.format_date(hold.request_time), '%Y-%m-%d') %]
586 Cancellation Note: [% hold.cancel_note %]
591 INSERT INTO action_trigger.environment (event_def, path) VALUES
594 (52, 'bib_rec.bib_record.simple_record');
598 INSERT INTO action_trigger.event_definition (
599 id, active, owner, name, hook,
600 validator, reactor, delay, delay_field,
601 group_field, message_usr_path, message_library_path, message_title,
604 53, TRUE, 1, 'Hold Cancelled (Shelf-Expired) User Message', 'hold_request.cancel.expire_holds_shelf',
605 'HoldIsCancelled', 'NOOP_True', '30 minutes', 'cancel_time',
606 'usr', 'usr', 'usr.home_ou', 'Hold Request Cancelled',
609 [%- user = target.0.usr -%]
610 The following holds were cancelled because they were never picked up.
612 [% FOR hold IN target %]
613 Title: [% hold.bib_rec.bib_record.simple_record.title %]
614 Author: [% hold.bib_rec.bib_record.simple_record.author %]
615 Library: [% hold.pickup_lib.name %]
616 Request Date: [% date.format(helpers.format_date(hold.request_time), '%Y-%m-%d') %]
617 Pickup By: [% date.format(helpers.format_date(hold.shelf_expire_time), '%Y-%m-%d') %]
622 INSERT INTO action_trigger.environment (event_def, path) VALUES
625 (53, 'bib_rec.bib_record.simple_record');
629 SELECT evergreen.upgrade_deps_block_check('0912', :eg_version);
631 ALTER TABLE asset.copy_location ADD COLUMN deleted BOOLEAN NOT NULL DEFAULT FALSE;
633 CREATE OR REPLACE RULE protect_copy_location_delete AS
634 ON DELETE TO asset.copy_location DO INSTEAD (
635 UPDATE asset.copy_location SET deleted = TRUE WHERE OLD.id = asset.copy_location.id;
636 UPDATE acq.lineitem_detail SET location = NULL WHERE location = OLD.id;
637 DELETE FROM asset.copy_location_order WHERE location = OLD.id;
638 DELETE FROM asset.copy_location_group_map WHERE location = OLD.id;
639 DELETE FROM config.circ_limit_set_copy_loc_map WHERE copy_loc = OLD.id;
642 ALTER TABLE asset.copy_location DROP CONSTRAINT acl_name_once_per_lib;
643 CREATE UNIQUE INDEX acl_name_once_per_lib ON asset.copy_location (name, owning_lib) WHERE deleted = FALSE OR deleted IS FALSE;
645 CREATE OR REPLACE FUNCTION asset.acp_location_fixer()
646 RETURNS TRIGGER AS $$
648 new_copy_location INT;
650 IF (TG_OP = 'UPDATE') THEN
651 IF NEW.location = OLD.location AND NEW.call_number = OLD.call_number AND NEW.circ_lib = OLD.circ_lib THEN
655 SELECT INTO new_copy_location acpl.id FROM asset.copy_location acpl JOIN actor.org_unit_ancestors_distance((SELECT owning_lib FROM asset.call_number WHERE id = NEW.call_number)) aouad ON acpl.owning_lib = aouad.id WHERE deleted IS FALSE AND name = (SELECT name FROM asset.copy_location WHERE id = NEW.location) ORDER BY distance LIMIT 1;
656 IF new_copy_location IS NULL THEN
657 SELECT INTO new_copy_location acpl.id FROM asset.copy_location acpl JOIN actor.org_unit_ancestors_distance(NEW.circ_lib) aouad ON acpl.owning_lib = aouad.id WHERE deleted IS FALSE AND name = (SELECT name FROM asset.copy_location WHERE id = NEW.location) ORDER BY distance LIMIT 1;
659 IF new_copy_location IS NOT NULL THEN
660 NEW.location = new_copy_location;
666 CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
671 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
673 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
678 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
679 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
682 actor.org_unit_descendants(ans.id) d
683 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
684 JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted)
685 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
689 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
696 $f$ LANGUAGE PLPGSQL;
698 CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
703 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
705 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
710 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
711 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
714 actor.org_unit_descendants(ans.id) d
715 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
716 JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted)
717 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
721 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
728 $f$ LANGUAGE PLPGSQL;
730 CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
735 JOIN asset.call_number acn ON acp.call_number = acn.id
736 JOIN asset.copy_location acpl ON acp.location = acpl.id
737 JOIN config.copy_status ccs ON acp.status = ccs.id
740 AND acp.holdable = true
741 AND acpl.holdable = true
742 AND ccs.holdable = true
743 AND acp.deleted = false
744 AND acpl.deleted = false
745 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
752 $f$ LANGUAGE PLPGSQL;
754 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
759 JOIN asset.call_number acn ON acp.call_number = acn.id
760 JOIN asset.copy_location acpl ON acp.location = acpl.id
761 JOIN config.copy_status ccs ON acp.status = ccs.id
762 JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
764 mmsm.metarecord = rid
765 AND acp.holdable = true
766 AND acpl.holdable = true
767 AND ccs.holdable = true
768 AND acp.deleted = false
769 AND acpl.deleted = false
770 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
777 $f$ LANGUAGE PLPGSQL;
779 CREATE OR REPLACE FUNCTION asset.refresh_opac_visible_copies_mat_view () RETURNS VOID AS $$
781 TRUNCATE TABLE asset.opac_visible_copies;
783 INSERT INTO asset.opac_visible_copies (copy_id, circ_lib, record)
784 SELECT cp.id, cp.circ_lib, cn.record
786 JOIN asset.call_number cn ON (cn.id = cp.call_number)
787 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
788 JOIN asset.copy_location cl ON (cp.location = cl.id)
789 JOIN config.copy_status cs ON (cp.status = cs.id)
790 JOIN biblio.record_entry b ON (cn.record = b.id)
800 SELECT cp.id, cp.circ_lib, pbcm.peer_record AS record
802 JOIN biblio.peer_bib_copy_map pbcm ON (pbcm.target_copy = cp.id)
803 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
804 JOIN asset.copy_location cl ON (cp.location = cl.id)
805 JOIN config.copy_status cs ON (cp.status = cs.id)
815 CREATE OR REPLACE FUNCTION asset.cache_copy_visibility () RETURNS TRIGGER as $func$
822 do_add BOOLEAN := false;
823 do_remove BOOLEAN := false;
826 SELECT cp.id, cp.circ_lib, cn.record, cn.id AS call_number, cp.location, cp.status
828 JOIN asset.call_number cn ON (cn.id = cp.call_number)
829 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
830 JOIN asset.copy_location cl ON (cp.location = cl.id)
831 JOIN config.copy_status cs ON (cp.status = cs.id)
832 JOIN biblio.record_entry b ON (cn.record = b.id)
843 SELECT cp.id, cp.circ_lib, pbcm.peer_record AS record, NULL AS call_number, cp.location, cp.status
845 JOIN biblio.peer_bib_copy_map pbcm ON (pbcm.target_copy = cp.id)
846 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
847 JOIN asset.copy_location cl ON (cp.location = cl.id)
848 JOIN config.copy_status cs ON (cp.status = cs.id)
857 INSERT INTO asset.opac_visible_copies (copy_id, circ_lib, record)
858 SELECT DISTINCT ON (id, record) id, circ_lib, record FROM (
864 remove_query := $$ DELETE FROM asset.opac_visible_copies WHERE copy_id IN ( SELECT id FROM asset.copy WHERE $$;
866 IF TG_TABLE_NAME = 'peer_bib_copy_map' THEN
867 IF TG_OP = 'INSERT' THEN
868 add_peer_query := add_peer_query || ' AND cp.id = ' || NEW.target_copy || ' AND pbcm.peer_record = ' || NEW.peer_record;
869 EXECUTE add_front || add_peer_query || add_back;
872 remove_query := 'DELETE FROM asset.opac_visible_copies WHERE copy_id = ' || OLD.target_copy || ' AND record = ' || OLD.peer_record || ';';
873 EXECUTE remove_query;
878 IF TG_OP = 'INSERT' THEN
880 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
881 add_base_query := add_base_query || ' AND cp.id = ' || NEW.id;
882 EXECUTE add_front || add_base_query || add_back;
889 -- handle items first, since with circulation activity
890 -- their statuses change frequently
891 IF TG_TABLE_NAME IN ('copy', 'unit') THEN
893 IF OLD.location <> NEW.location OR
894 OLD.call_number <> NEW.call_number OR
895 OLD.status <> NEW.status OR
896 OLD.circ_lib <> NEW.circ_lib THEN
897 -- any of these could change visibility, but
898 -- we'll save some queries and not try to calculate
899 -- the change directly
904 IF OLD.deleted <> NEW.deleted THEN
912 IF OLD.opac_visible <> NEW.opac_visible THEN
913 IF OLD.opac_visible THEN
915 ELSIF NOT do_remove THEN -- handle edge case where deleted item
916 -- is also marked opac_visible
924 DELETE FROM asset.opac_visible_copies WHERE copy_id = NEW.id;
927 add_base_query := add_base_query || ' AND cp.id = ' || NEW.id;
928 add_peer_query := add_peer_query || ' AND cp.id = ' || NEW.id;
929 EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back;
936 IF TG_TABLE_NAME IN ('call_number', 'copy_location', 'record_entry') THEN -- these have a 'deleted' column
938 IF OLD.deleted AND NEW.deleted THEN -- do nothing
942 ELSIF NEW.deleted THEN -- remove rows
944 IF TG_TABLE_NAME = 'call_number' THEN
945 DELETE FROM asset.opac_visible_copies WHERE copy_id IN (SELECT id FROM asset.copy WHERE call_number = NEW.id);
946 ELSIF TG_TABLE_NAME = 'copy_location' THEN
947 DELETE FROM asset.opac_visible_copies WHERE copy_id IN (SELECT id FROM asset.copy WHERE location = NEW.id);
948 ELSIF TG_TABLE_NAME = 'record_entry' THEN
949 DELETE FROM asset.opac_visible_copies WHERE record = NEW.id;
954 ELSIF OLD.deleted THEN -- add rows
956 IF TG_TABLE_NAME = 'call_number' THEN
957 add_base_query := add_base_query || ' AND cn.id = ' || NEW.id;
958 EXECUTE add_front || add_base_query || add_back;
959 ELSIF TG_TABLE_NAME = 'copy_location' THEN
960 add_base_query := add_base_query || 'AND cl.id = ' || NEW.id;
961 EXECUTE add_front || add_base_query || add_back;
962 ELSIF TG_TABLE_NAME = 'record_entry' THEN
963 add_base_query := add_base_query || ' AND cn.record = ' || NEW.id;
964 add_peer_query := add_peer_query || ' AND pbcm.peer_record = ' || NEW.id;
965 EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back;
974 IF TG_TABLE_NAME = 'call_number' THEN
976 IF OLD.record <> NEW.record THEN
977 -- call number is linked to different bib
978 remove_query := remove_query || 'call_number = ' || NEW.id || ');';
979 EXECUTE remove_query;
980 add_base_query := add_base_query || ' AND cn.id = ' || NEW.id;
981 EXECUTE add_front || add_base_query || add_back;
988 IF TG_TABLE_NAME IN ('record_entry') THEN
989 RETURN NEW; -- don't have 'opac_visible'
992 -- actor.org_unit, asset.copy_location, asset.copy_status
993 IF NEW.opac_visible = OLD.opac_visible THEN -- do nothing
997 ELSIF NEW.opac_visible THEN -- add rows
999 IF TG_TABLE_NAME = 'org_unit' THEN
1000 add_base_query := add_base_query || ' AND cp.circ_lib = ' || NEW.id;
1001 add_peer_query := add_peer_query || ' AND cp.circ_lib = ' || NEW.id;
1002 ELSIF TG_TABLE_NAME = 'copy_location' THEN
1003 add_base_query := add_base_query || ' AND cp.location = ' || NEW.id;
1004 add_peer_query := add_peer_query || ' AND cp.location = ' || NEW.id;
1005 ELSIF TG_TABLE_NAME = 'copy_status' THEN
1006 add_base_query := add_base_query || ' AND cp.status = ' || NEW.id;
1007 add_peer_query := add_peer_query || ' AND cp.status = ' || NEW.id;
1010 EXECUTE add_front || add_base_query || ' UNION ' || add_peer_query || add_back;
1014 IF TG_TABLE_NAME = 'org_unit' THEN
1015 remove_query := 'DELETE FROM asset.opac_visible_copies WHERE circ_lib = ' || NEW.id || ';';
1016 ELSIF TG_TABLE_NAME = 'copy_location' THEN
1017 remove_query := remove_query || 'location = ' || NEW.id || ');';
1018 ELSIF TG_TABLE_NAME = 'copy_status' THEN
1019 remove_query := remove_query || 'status = ' || NEW.id || ');';
1022 EXECUTE remove_query;
1028 $func$ LANGUAGE PLPGSQL;
1030 -- updated copy location validity test to disallow deleted locations
1031 CREATE OR REPLACE FUNCTION vandelay.ingest_items ( import_id BIGINT, attr_def_id BIGINT ) RETURNS SETOF vandelay.import_item AS $$
1042 deposit_amount TEXT;
1056 tmp_attr_set RECORD;
1057 attr_set vandelay.import_item%ROWTYPE;
1064 SELECT * INTO attr_def FROM vandelay.import_item_attr_definition WHERE id = attr_def_id;
1068 attr_set.definition := attr_def.id;
1070 -- Build the combined XPath
1074 WHEN attr_def.owning_lib IS NULL THEN 'null()'
1075 WHEN LENGTH( attr_def.owning_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.owning_lib || '"]'
1076 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.owning_lib
1081 WHEN attr_def.circ_lib IS NULL THEN 'null()'
1082 WHEN LENGTH( attr_def.circ_lib ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_lib || '"]'
1083 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_lib
1088 WHEN attr_def.call_number IS NULL THEN 'null()'
1089 WHEN LENGTH( attr_def.call_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.call_number || '"]'
1090 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.call_number
1095 WHEN attr_def.copy_number IS NULL THEN 'null()'
1096 WHEN LENGTH( attr_def.copy_number ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.copy_number || '"]'
1097 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.copy_number
1102 WHEN attr_def.status IS NULL THEN 'null()'
1103 WHEN LENGTH( attr_def.status ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.status || '"]'
1104 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.status
1109 WHEN attr_def.location IS NULL THEN 'null()'
1110 WHEN LENGTH( attr_def.location ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.location || '"]'
1111 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.location
1116 WHEN attr_def.circulate IS NULL THEN 'null()'
1117 WHEN LENGTH( attr_def.circulate ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circulate || '"]'
1118 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circulate
1123 WHEN attr_def.deposit IS NULL THEN 'null()'
1124 WHEN LENGTH( attr_def.deposit ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit || '"]'
1125 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit
1130 WHEN attr_def.deposit_amount IS NULL THEN 'null()'
1131 WHEN LENGTH( attr_def.deposit_amount ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.deposit_amount || '"]'
1132 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.deposit_amount
1137 WHEN attr_def.ref IS NULL THEN 'null()'
1138 WHEN LENGTH( attr_def.ref ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.ref || '"]'
1139 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.ref
1144 WHEN attr_def.holdable IS NULL THEN 'null()'
1145 WHEN LENGTH( attr_def.holdable ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.holdable || '"]'
1146 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.holdable
1151 WHEN attr_def.price IS NULL THEN 'null()'
1152 WHEN LENGTH( attr_def.price ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.price || '"]'
1153 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.price
1158 WHEN attr_def.barcode IS NULL THEN 'null()'
1159 WHEN LENGTH( attr_def.barcode ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.barcode || '"]'
1160 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.barcode
1165 WHEN attr_def.circ_modifier IS NULL THEN 'null()'
1166 WHEN LENGTH( attr_def.circ_modifier ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_modifier || '"]'
1167 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_modifier
1172 WHEN attr_def.circ_as_type IS NULL THEN 'null()'
1173 WHEN LENGTH( attr_def.circ_as_type ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.circ_as_type || '"]'
1174 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.circ_as_type
1179 WHEN attr_def.alert_message IS NULL THEN 'null()'
1180 WHEN LENGTH( attr_def.alert_message ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.alert_message || '"]'
1181 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.alert_message
1186 WHEN attr_def.opac_visible IS NULL THEN 'null()'
1187 WHEN LENGTH( attr_def.opac_visible ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.opac_visible || '"]'
1188 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.opac_visible
1193 WHEN attr_def.pub_note IS NULL THEN 'null()'
1194 WHEN LENGTH( attr_def.pub_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.pub_note || '"]'
1195 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.pub_note
1199 WHEN attr_def.priv_note IS NULL THEN 'null()'
1200 WHEN LENGTH( attr_def.priv_note ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.priv_note || '"]'
1201 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.priv_note
1206 WHEN attr_def.internal_id IS NULL THEN 'null()'
1207 WHEN LENGTH( attr_def.internal_id ) = 1 THEN '//*[@tag="' || attr_def.tag || '"]/*[@code="' || attr_def.internal_id || '"]'
1208 ELSE '//*[@tag="' || attr_def.tag || '"]/*' || attr_def.internal_id
1214 owning_lib || '|' ||
1216 call_number || '|' ||
1217 copy_number || '|' ||
1222 deposit_amount || '|' ||
1227 circ_modifier || '|' ||
1228 circ_as_type || '|' ||
1229 alert_message || '|' ||
1232 internal_id || '|' ||
1237 FROM oils_xpath_table( 'id', 'marc', 'vandelay.queued_bib_record', xpath, 'id = ' || import_id )
1238 AS t( id INT, ol TEXT, clib TEXT, cn TEXT, cnum TEXT, cs TEXT, cl TEXT, circ TEXT,
1239 dep TEXT, dep_amount TEXT, r TEXT, hold TEXT, pr TEXT, bc TEXT, circ_mod TEXT,
1240 circ_as TEXT, amessage TEXT, note TEXT, pnote TEXT, internal_id TEXT, opac_vis TEXT )
1243 attr_set.import_error := NULL;
1244 attr_set.error_detail := NULL;
1245 attr_set.deposit_amount := NULL;
1246 attr_set.copy_number := NULL;
1247 attr_set.price := NULL;
1248 attr_set.circ_modifier := NULL;
1249 attr_set.location := NULL;
1250 attr_set.barcode := NULL;
1251 attr_set.call_number := NULL;
1253 IF tmp_attr_set.pr != '' THEN
1254 tmp_str = REGEXP_REPLACE(tmp_attr_set.pr, E'[^0-9\\.]', '', 'g');
1255 IF tmp_str = '' THEN
1256 attr_set.import_error := 'import.item.invalid.price';
1257 attr_set.error_detail := tmp_attr_set.pr; -- original value
1258 RETURN NEXT attr_set; CONTINUE;
1260 attr_set.price := tmp_str::NUMERIC(8,2);
1263 IF tmp_attr_set.dep_amount != '' THEN
1264 tmp_str = REGEXP_REPLACE(tmp_attr_set.dep_amount, E'[^0-9\\.]', '', 'g');
1265 IF tmp_str = '' THEN
1266 attr_set.import_error := 'import.item.invalid.deposit_amount';
1267 attr_set.error_detail := tmp_attr_set.dep_amount;
1268 RETURN NEXT attr_set; CONTINUE;
1270 attr_set.deposit_amount := tmp_str::NUMERIC(8,2);
1273 IF tmp_attr_set.cnum != '' THEN
1274 tmp_str = REGEXP_REPLACE(tmp_attr_set.cnum, E'[^0-9]', '', 'g');
1275 IF tmp_str = '' THEN
1276 attr_set.import_error := 'import.item.invalid.copy_number';
1277 attr_set.error_detail := tmp_attr_set.cnum;
1278 RETURN NEXT attr_set; CONTINUE;
1280 attr_set.copy_number := tmp_str::INT;
1283 IF tmp_attr_set.ol != '' THEN
1284 SELECT id INTO attr_set.owning_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.ol); -- INT
1286 attr_set.import_error := 'import.item.invalid.owning_lib';
1287 attr_set.error_detail := tmp_attr_set.ol;
1288 RETURN NEXT attr_set; CONTINUE;
1292 IF tmp_attr_set.clib != '' THEN
1293 SELECT id INTO attr_set.circ_lib FROM actor.org_unit WHERE shortname = UPPER(tmp_attr_set.clib); -- INT
1295 attr_set.import_error := 'import.item.invalid.circ_lib';
1296 attr_set.error_detail := tmp_attr_set.clib;
1297 RETURN NEXT attr_set; CONTINUE;
1301 IF tmp_attr_set.cs != '' THEN
1302 SELECT id INTO attr_set.status FROM config.copy_status WHERE LOWER(name) = LOWER(tmp_attr_set.cs); -- INT
1304 attr_set.import_error := 'import.item.invalid.status';
1305 attr_set.error_detail := tmp_attr_set.cs;
1306 RETURN NEXT attr_set; CONTINUE;
1310 IF COALESCE(tmp_attr_set.circ_mod, '') = '' THEN
1312 -- no circ mod defined, see if we should apply a default
1313 SELECT INTO attr_set.circ_modifier TRIM(BOTH '"' FROM value)
1314 FROM actor.org_unit_ancestor_setting(
1315 'vandelay.item.circ_modifier.default',
1319 -- make sure the value from the org setting is still valid
1320 PERFORM 1 FROM config.circ_modifier WHERE code = attr_set.circ_modifier;
1322 attr_set.import_error := 'import.item.invalid.circ_modifier';
1323 attr_set.error_detail := tmp_attr_set.circ_mod;
1324 RETURN NEXT attr_set; CONTINUE;
1329 SELECT code INTO attr_set.circ_modifier FROM config.circ_modifier WHERE code = tmp_attr_set.circ_mod;
1331 attr_set.import_error := 'import.item.invalid.circ_modifier';
1332 attr_set.error_detail := tmp_attr_set.circ_mod;
1333 RETURN NEXT attr_set; CONTINUE;
1337 IF tmp_attr_set.circ_as != '' THEN
1338 SELECT code INTO attr_set.circ_as_type FROM config.coded_value_map WHERE ctype = 'item_type' AND code = tmp_attr_set.circ_as;
1340 attr_set.import_error := 'import.item.invalid.circ_as_type';
1341 attr_set.error_detail := tmp_attr_set.circ_as;
1342 RETURN NEXT attr_set; CONTINUE;
1346 IF COALESCE(tmp_attr_set.cl, '') = '' THEN
1347 -- no location specified, see if we should apply a default
1349 SELECT INTO attr_set.location TRIM(BOTH '"' FROM value)
1350 FROM actor.org_unit_ancestor_setting(
1351 'vandelay.item.copy_location.default',
1355 -- make sure the value from the org setting is still valid
1356 PERFORM 1 FROM asset.copy_location
1357 WHERE id = attr_set.location AND NOT deleted;
1359 attr_set.import_error := 'import.item.invalid.location';
1360 attr_set.error_detail := tmp_attr_set.cs;
1361 RETURN NEXT attr_set; CONTINUE;
1365 -- search up the org unit tree for a matching copy location
1366 WITH RECURSIVE anscestor_depth AS (
1370 FROM actor.org_unit ou
1371 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
1372 WHERE ou.id = COALESCE(attr_set.owning_lib, attr_set.circ_lib)
1377 FROM actor.org_unit ou
1378 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
1379 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
1380 ) SELECT cpl.id INTO attr_set.location
1381 FROM anscestor_depth a
1382 JOIN asset.copy_location cpl ON (cpl.owning_lib = a.id)
1383 WHERE LOWER(cpl.name) = LOWER(tmp_attr_set.cl)
1385 ORDER BY a.depth DESC
1389 attr_set.import_error := 'import.item.invalid.location';
1390 attr_set.error_detail := tmp_attr_set.cs;
1391 RETURN NEXT attr_set; CONTINUE;
1395 attr_set.circulate :=
1396 LOWER( SUBSTRING( tmp_attr_set.circ, 1, 1)) IN ('t','y','1')
1397 OR LOWER(tmp_attr_set.circ) = 'circulating'; -- BOOL
1400 LOWER( SUBSTRING( tmp_attr_set.dep, 1, 1 ) ) IN ('t','y','1')
1401 OR LOWER(tmp_attr_set.dep) = 'deposit'; -- BOOL
1403 attr_set.holdable :=
1404 LOWER( SUBSTRING( tmp_attr_set.hold, 1, 1 ) ) IN ('t','y','1')
1405 OR LOWER(tmp_attr_set.hold) = 'holdable'; -- BOOL
1407 attr_set.opac_visible :=
1408 LOWER( SUBSTRING( tmp_attr_set.opac_vis, 1, 1 ) ) IN ('t','y','1')
1409 OR LOWER(tmp_attr_set.opac_vis) = 'visible'; -- BOOL
1412 LOWER( SUBSTRING( tmp_attr_set.r, 1, 1 ) ) IN ('t','y','1')
1413 OR LOWER(tmp_attr_set.r) = 'reference'; -- BOOL
1415 attr_set.call_number := tmp_attr_set.cn; -- TEXT
1416 attr_set.barcode := tmp_attr_set.bc; -- TEXT,
1417 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
1418 attr_set.pub_note := tmp_attr_set.note; -- TEXT,
1419 attr_set.priv_note := tmp_attr_set.pnote; -- TEXT,
1420 attr_set.alert_message := tmp_attr_set.amessage; -- TEXT,
1421 attr_set.internal_id := tmp_attr_set.internal_id::BIGINT;
1423 RETURN NEXT attr_set;
1432 $$ LANGUAGE PLPGSQL;
1438 SELECT evergreen.upgrade_deps_block_check('0915', :eg_version);
1440 INSERT INTO permission.perm_list (id, code, description)
1443 'TOTAL_HOLD_COPY_RATIO_EXCEEDED.override',
1446 'Override the TOTAL_HOLD_COPY_RATIO_EXCEEDED event',
1452 INSERT INTO permission.perm_list (id, code, description)
1455 'AVAIL_HOLD_COPY_RATIO_EXCEEDED.override',
1458 'Override the AVAIL_HOLD_COPY_RATIO_EXCEEDED event',