3 -- These are copy level holds
4 CREATE TABLE legacy_copy_hold_insert AS
5 SELECT DISTINCT ON (jl.cat_key, jl.call_key, jl.item_key)
8 'C'::TEXT AS hold_type,
10 pou.id AS selection_lib,
11 CASE WHEN lh.hold_range = 'SYSTEM' THEN 0
12 WHEN lh.hold_range = 'GROUP' THEN 1
14 END AS selection_depth,
15 lh.hold_date AS request_time,
16 CASE WHEN lh.available IN ('Y','I') THEN now()
19 rou.id AS request_lib,
24 ON ( jl.cat_key = lh.cat_key
25 AND jl.call_key = lh.call_key
26 AND jl.item_key = lh.call_key )
27 JOIN asset.copy cp ON (cp.barcode = jl.item_id)
28 JOIN actor.usr au ON (au.id = lh.user_key)
29 JOIN actor.org_unit rou ON (rou.shortname = lh.placing_lib)
30 JOIN actor.org_unit pou ON (pou.shortname = lh.pickup_lib)
31 WHERE lh.hold_level = 'C';
33 -- And these are CN level holds
34 CREATE TABLE legacy_cn_hold_insert AS
35 SELECT DISTINCT ON (jl.cat_key, jl.call_key, jl.item_key)
36 cp.id AS current_copy,
37 cp.call_number AS target,
38 'V'::TEXT AS hold_type,
40 pou.id AS selection_lib,
41 CASE WHEN lh.hold_range = 'SYSTEM' THEN 0
42 WHEN lh.hold_range = 'GROUP' THEN 1
44 END AS selection_depth,
45 lh.hold_date AS request_time,
46 CASE WHEN lh.available = 'Y' THEN now()
49 rou.id AS request_lib,
54 ON ( jl.cat_key = lh.cat_key
55 AND jl.call_key = lh.call_key
56 AND jl.item_key = lh.call_key )
57 JOIN asset.copy cp ON (cp.barcode = jl.item_id)
58 JOIN actor.usr au ON (au.id = lh.user_key)
59 JOIN actor.org_unit rou ON (rou.shortname = lh.placing_lib)
60 JOIN actor.org_unit pou ON (pou.shortname = lh.pickup_lib)
61 WHERE lh.hold_level = 'A';
63 -- And these are CN level holds
64 CREATE TABLE legacy_title_hold_insert AS
65 SELECT DISTINCT ON (jl.cat_key, jl.call_key, jl.item_key)
66 cp.id AS current_copy,
68 'T'::TEXT AS hold_type,
70 pou.id AS selection_lib,
71 CASE WHEN lh.hold_range = 'SYSTEM' THEN 0
72 WHEN lh.hold_range = 'GROUP' THEN 1
74 END AS selection_depth,
75 lh.hold_date AS request_time,
76 CASE WHEN lh.available IN ('Y','I') THEN now()
79 rou.id AS request_lib,
84 ON ( jl.cat_key = lh.cat_key
85 AND jl.call_key = lh.call_key
86 AND jl.item_key = lh.call_key )
87 JOIN asset.copy cp ON (cp.barcode = jl.item_id)
88 JOIN asset.call_number cn ON (cp.call_number = cn.id)
89 JOIN actor.usr au ON (au.id = lh.user_key)
90 JOIN actor.org_unit rou ON (rou.shortname = lh.placing_lib)
91 JOIN actor.org_unit pou ON (pou.shortname = lh.pickup_lib)
92 WHERE lh.hold_level = 'T';