3 -- These are copy level holds
4 -- CREATE TABLE legacy_copy_hold_insert AS
5 INSERT INTO action.hold_request
6 (id, target, current_copy, hold_type, pickup_lib, selection_ou, selection_depth, request_time, capture_time, request_lib, requestor, usr)
7 SELECT lh.hold_key AS id,
10 'C'::TEXT AS hold_type,
12 pou.id AS selection_ou,
13 CASE WHEN lh.hold_range = 'SYSTEM' THEN 0
14 WHEN lh.hold_range = 'GROUP' THEN 1
16 END AS selection_depth,
17 lh.hold_date AS request_time,
18 CASE WHEN lh.available IN ('Y','I') THEN now()
21 rou.id AS request_lib,
26 ON ( jl.cat_key = lh.cat_key
27 AND jl.call_key = lh.call_key
28 AND jl.item_key = lh.call_key )
29 JOIN asset.copy cp ON (cp.barcode = jl.item_id)
30 JOIN actor.usr au ON (au.id = lh.user_key)
31 JOIN actor.org_unit rou ON (rou.shortname = lh.placing_lib)
32 JOIN actor.org_unit pou ON (pou.shortname = lh.pickup_lib)
33 WHERE lh.hold_level = 'C';
35 -- And these are CN level holds
36 -- CREATE TABLE legacy_cn_hold_insert AS
37 INSERT INTO action.hold_request
38 (id, target, current_copy, hold_type, pickup_lib, selection_ou, selection_depth, request_time, capture_time, request_lib, requestor, usr)
39 SELECT lh.hold_key AS id,
40 cp.call_number AS target,
41 cp.id AS current_copy,
42 'V'::TEXT AS hold_type,
44 pou.id AS selection_ou,
45 CASE WHEN lh.hold_range = 'SYSTEM' THEN 0
46 WHEN lh.hold_range = 'GROUP' THEN 1
48 END AS selection_depth,
49 lh.hold_date AS request_time,
50 CASE WHEN lh.available = 'Y' THEN now()
53 rou.id AS request_lib,
58 ON ( jl.cat_key = lh.cat_key
59 AND jl.call_key = lh.call_key
60 AND jl.item_key = lh.call_key )
61 JOIN asset.copy cp ON (cp.barcode = jl.item_id)
62 JOIN actor.usr au ON (au.id = lh.user_key)
63 JOIN actor.org_unit rou ON (rou.shortname = lh.placing_lib)
64 JOIN actor.org_unit pou ON (pou.shortname = lh.pickup_lib)
65 WHERE lh.hold_level = 'A';
67 -- And these are CN level holds
68 -- CREATE TABLE legacy_title_hold_insert AS
69 INSERT INTO action.hold_request
70 (id, target, current_copy, hold_type, pickup_lib, selection_ou, selection_depth, request_time, capture_time, request_lib, requestor, usr)
71 SELECT lh.hold_key AS id,
73 cp.id AS current_copy,
74 'T'::TEXT AS hold_type,
76 pou.id AS selection_ou,
77 CASE WHEN lh.hold_range = 'SYSTEM' THEN 0
78 WHEN lh.hold_range = 'GROUP' THEN 1
80 END AS selection_depth,
81 lh.hold_date AS request_time,
82 CASE WHEN lh.available IN ('Y','I') THEN now()
85 rou.id AS request_lib,
90 ON ( jl.cat_key = lh.cat_key
91 AND jl.call_key = lh.call_key
92 AND jl.item_key = lh.call_key )
93 JOIN asset.copy cp ON (cp.barcode = jl.item_id)
94 JOIN actor.usr au ON (au.id = lh.user_key)
95 JOIN actor.org_unit rou ON (rou.shortname = lh.placing_lib)
96 JOIN actor.org_unit pou ON (pou.shortname = lh.pickup_lib)
97 WHERE lh.hold_level = 'T';
99 SELECT SETVAL('action.hold_request_id_seq',(SELECT MAX(id) FROM action.hold_request),TRUE);