Hold Matrix Matchpoint SQL Report

-- really verbose alternative for getting the rules for in-db holds rules
-- rather than just select * config.hold_matrix_matchpoint
-- almost a necessity for a consortium with varying rules or any library
-- with intricate polices because in-db holds policies are much easier to
-- handle via SQL than through the staff client
-- script has IDs for admins and look ups to names for easy reference
-- tested on 2.1 and 2.4
-- (c) Rogan Hamby, 2012, roganhamby.com
-- Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License
select hmm.id AS "Hold Matchpoint ID", hmm.active AS "Active",
hmm.user_home_ou AS "User Home ID", ahou.shortname AS "User Home Library",
hmm.request_ou AS "Requesting Org ID", arou.shortname AS "Requesting Library",
hmm.pickup_ou AS "Pickup Org ID", apou.shortname AS "Pickup Library",
hmm.item_owning_ou AS "Item Owning Org ID", aoou.shortname AS "Item Owning Library",
hmm.item_circ_ou AS "Item Circ Org ID", acou.shortname AS "Item Circulating Library",
hmm.usr_grp AS "User Group ID", upgt.name AS "User Group",
hmm.requestor_grp AS "Requestor Group ID", rpgt.name AS "Requestor Group",
hmm.circ_modifier AS "Circulation Modifier",
hmm.marc_type AS "MARC Type",
hmm.marc_form AS "MARC Form",
hmm.marc_vr_format AS "MARC Video Format",
hmm.marc_bib_level AS "MARC Bib Level",
hmm.ref_flag AS "Reference Flag",
hmm.juvenile_flag AS "Juvenile Flag",
hmm.age_hold_protect_rule AS "Age Hold Rule ID", rap.name AS "Age Hold Rule Name", rap.age AS "Age Hold Interval", rap.prox AS "Age Hold Prox",
hmm.holdable AS "Holdable Flag",
hmm.distance_is_from_owner AS "Distance is from Owner",
hmm.transit_range AS "Transit Range ID", aout.depth AS "Transit Range Depth",
hmm.max_holds AS "Max Holds",
hmm.include_frozen_holds AS "Include Frozen Holds Flag",
hmm.stop_blocked_user AS "Stop Blocked User Flag",
hmm.strict_ou_match AS "Strict Org Unit Match Flag"
from config.hold_matrix_matchpoint hmm
left outer join actor.org_unit AS ahou ON ahou.id = hmm.user_home_ou
left outer join actor.org_unit AS arou ON arou.id = hmm.request_ou
left outer join actor.org_unit AS apou ON apou.id = hmm.pickup_ou
left outer join actor.org_unit AS aoou ON aoou.id = hmm.item_owning_ou
left outer join actor.org_unit AS acou ON acou.id = hmm.item_circ_ou
left outer join permission.grp_tree AS upgt ON upgt.id = hmm.usr_grp
left outer join permission.grp_tree AS rpgt ON rpgt.id = hmm.requestor_grp
left outer join config.rule_age_hold_protect rap ON rap.id = hmm.age_hold_protect_rule
left outer join actor.org_unit_type aout ON aout.id = hmm.transit_range
;


-- shorter version
SELECT hmm.id AS "Hold Matchpoint ID", hmm.active AS "Active",
hmm.user_home_ou AS "User Home ID", ahou.shortname AS "User Home Library",
hmm.item_owning_ou AS "Item Owning Org ID", aoou.shortname AS "Item Owning Library",
hmm.usr_grp AS "User Group ID", upgt.NAME AS "User Group",
hmm.requestor_grp AS "Requestor Group ID", rpgt.NAME AS "Requestor Group",
hmm.circ_modifier AS "Circulation Modifier", hmm.ref_flag AS "Reference Flag",
hmm.holdable AS "Holdable Flag", hmm.distance_is_from_owner AS "Distance is from Owner",
hmm.transit_range AS "Transit Range ID", aout.depth AS "Transit Range Depth",
hmm.max_holds AS "Max Holds", hmm.stop_blocked_user AS "Stop Blocked User Flag",
hmm.strict_ou_match AS "Strict Org Unit Match Flag"
FROM config.hold_matrix_matchpoint hmm
LEFT OUTER JOIN actor.org_unit AS ahou ON ahou.id = hmm.user_home_ou
LEFT OUTER JOIN actor.org_unit AS arou ON arou.id = hmm.request_ou
LEFT OUTER JOIN actor.org_unit AS apou ON apou.id = hmm.pickup_ou
LEFT OUTER JOIN actor.org_unit AS aoou ON aoou.id = hmm.item_owning_ou
LEFT OUTER JOIN actor.org_unit AS acou ON acou.id = hmm.item_circ_ou
LEFT OUTER JOIN permission.grp_tree AS upgt ON upgt.id = hmm.usr_grp
LEFT OUTER JOIN permission.grp_tree AS rpgt ON rpgt.id = hmm.requestor_grp
LEFT OUTER JOIN config.rule_age_hold_protect rap ON rap.id = hmm.age_hold_protect_rule
LEFT OUTER JOIN actor.org_unit_type aout ON aout.id = hmm.transit_range
;