3 -- These are copy level holds
4 -- CREATE TABLE legacy_copy_hold_insert AS
5 INSERT INTO action.hold_request
6 (target, current_copy, hold_type, pickup_lib, selection_ou, selection_depth, request_time, capture_time, request_lib, requestor, usr)
7 SELECT cp.id AS target,
9 'C'::TEXT AS hold_type,
11 pou.id AS selection_ou,
12 CASE WHEN lh.hold_range = 'SYSTEM' THEN 0
13 WHEN lh.hold_range = 'GROUP' THEN 1
15 END AS selection_depth,
16 lh.hold_date AS request_time,
17 CASE WHEN lh.available IN ('Y','I') THEN now()
20 rou.id AS request_lib,
25 ON ( jl.cat_key = lh.cat_key
26 AND jl.call_key = lh.call_key
27 AND jl.item_key = lh.call_key )
28 JOIN asset.copy cp ON (cp.barcode = jl.item_id)
29 JOIN actor.usr au ON (au.id = lh.user_key)
30 JOIN actor.org_unit rou ON (rou.shortname = lh.placing_lib)
31 JOIN actor.org_unit pou ON (pou.shortname = lh.pickup_lib)
32 WHERE lh.hold_level = 'C';
34 -- And these are CN level holds
35 -- CREATE TABLE legacy_cn_hold_insert AS
36 INSERT INTO action.hold_request
37 (target, current_copy, hold_type, pickup_lib, selection_ou, selection_depth, request_time, capture_time, request_lib, requestor, usr)
38 SELECT cp.call_number AS target,
39 cp.id AS current_copy,
40 'V'::TEXT AS hold_type,
42 pou.id AS selection_ou,
43 CASE WHEN lh.hold_range = 'SYSTEM' THEN 0
44 WHEN lh.hold_range = 'GROUP' THEN 1
46 END AS selection_depth,
47 lh.hold_date AS request_time,
48 CASE WHEN lh.available = 'Y' THEN now()
51 rou.id AS request_lib,
56 ON ( jl.cat_key = lh.cat_key
57 AND jl.call_key = lh.call_key
58 AND jl.item_key = lh.call_key )
59 JOIN asset.copy cp ON (cp.barcode = jl.item_id)
60 JOIN actor.usr au ON (au.id = lh.user_key)
61 JOIN actor.org_unit rou ON (rou.shortname = lh.placing_lib)
62 JOIN actor.org_unit pou ON (pou.shortname = lh.pickup_lib)
63 WHERE lh.hold_level = 'A';
65 -- And these are CN level holds
66 -- CREATE TABLE legacy_title_hold_insert AS
67 INSERT INTO action.hold_request
68 (target, current_copy, hold_type, pickup_lib, selection_ou, selection_depth, request_time, capture_time, request_lib, requestor, usr)
69 SELECT lh.cat_key AS target,
70 cp.id AS current_copy,
71 'T'::TEXT AS hold_type,
73 pou.id AS selection_ou,
74 CASE WHEN lh.hold_range = 'SYSTEM' THEN 0
75 WHEN lh.hold_range = 'GROUP' THEN 1
77 END AS selection_depth,
78 lh.hold_date AS request_time,
79 CASE WHEN lh.available IN ('Y','I') THEN now()
82 rou.id AS request_lib,
87 ON ( jl.cat_key = lh.cat_key
88 AND jl.call_key = lh.call_key
89 AND jl.item_key = lh.call_key )
90 JOIN asset.copy cp ON (cp.barcode = jl.item_id)
91 JOIN actor.usr au ON (au.id = lh.user_key)
92 JOIN actor.org_unit rou ON (rou.shortname = lh.placing_lib)
93 JOIN actor.org_unit pou ON (pou.shortname = lh.pickup_lib)
94 WHERE lh.hold_level = 'T';