SELECT
 "ahopl".capture_time,
 "ahopl".current_copy,
 "ahopl".email_notify,
 "ahopl".expire_time,
 "ahopl".fulfillment_lib,
 "ahopl".fulfillment_staff,
 "ahopl".fulfillment_time,
 "ahopl".hold_type,
 "ahopl".holdable_formats,
 "ahopl".id,
 "ahopl".phone_notify,
 "ahopl".sms_notify,
 "ahopl".sms_carrier,
 "ahopl".pickup_lib,
 "ahopl".prev_check_time,
 "ahopl".request_lib,
 "ahopl".request_time,
 "ahopl".requestor,
 "ahopl".selection_depth,
 "ahopl".selection_ou,
 "ahopl".target,
 "ahopl".usr,
 "ahopl".cancel_time,
 "ahopl".frozen,
 "ahopl".thaw_date,
 "ahopl".shelf_time,
 "ahopl".cancel_cause,
 "ahopl".cancel_note,
 "ahopl".cut_in_line,
 "ahopl".mint_condition,
 "ahopl".shelf_expire_time,
 "ahopl".current_shelf_lib,
 "ahopl".acq_request,
 "ahopl".copy_location_order_position,
 "ahopl".usr_first_given_name,
 "ahopl".usr_second_given_name,
 "ahopl".usr_family_name,
 "ahopl".usr_prefix,
 "ahopl".usr_suffix,
 "ahopl".usr_alias_or_first_given_name,
 "ahopl".usr_display_name,
 "ahopl".usr_alias_or_display_name,
 "ahopl".usr_alias,
 "ahopl".call_number_label,
 "ahopl".issuance_label,
 "ahopl".is_staff_hold,
 "ahopl".potential_copies,
 "ahopl".behind_desk,
 "ahopl".hopeless_date
 FROM (
                        SELECT DISTINCT
                                ahr.*,
                                COALESCE(acplo.POSITION, acpl_ordered.fallback_position) AS
                                        copy_location_order_position,
                                CASE WHEN au.alias IS NOT NULL THEN
                                        au.alias
                                ELSE
                                        au.first_given_name
                                END AS usr_alias_or_first_given_name,
                                au.first_given_name AS usr_first_given_name,
                                au.second_given_name AS usr_second_given_name,
                                au.family_name AS usr_family_name,
                                au.prefix AS usr_prefix,
                                au.suffix AS usr_suffix,
                                au.alias AS usr_alias,
                                CASE WHEN au.alias IS NOT NULL THEN
                                        au.alias
                                ELSE
                                        REGEXP_REPLACE(ARRAY_TO_STRING(ARRAY[
                                                COALESCE(au.family_name, ''),
                                                COALESCE(au.suffix, ''),
                                                ', ',
                                                COALESCE(au.prefix, ''),
                                                COALESCE(au.first_given_name, ''),
                                                COALESCE(au.second_given_name, '')
                                        ], ' '), E'\\s+,', ',')
                                END AS usr_alias_or_display_name,
                                REGEXP_REPLACE(ARRAY_TO_STRING(ARRAY[
                                        COALESCE(au.family_name, ''),
                                        COALESCE(au.suffix, ''),
                                        ', ',
                                        COALESCE(au.prefix, ''),
                                        COALESCE(au.first_given_name, ''),
                                        COALESCE(au.second_given_name, '')
                                ], ' '), E'\\s+,', ',') AS usr_display_name,
                                TRIM(acnp.label || ' ' || acn.label || ' ' || acns.label)
                                        AS call_number_label,
                                siss.label AS issuance_label,
                                (ahr.usr <> ahr.requestor) AS is_staff_hold,
                                ahcm_1.copy_count AS potential_copies
                        FROM action.hold_request ahr
                        JOIN asset.copy acp ON (acp.id = ahr.current_copy)
                        JOIN asset.call_number acn ON (acp.call_number = acn.id)
                        JOIN asset.call_number_prefix acnp ON (acn.prefix = acnp.id)
                        JOIN asset.call_number_suffix acns ON (acn.suffix = acns.id)
                        JOIN actor.usr au ON (au.id = ahr.usr)
                        JOIN (
                                SELECT *, (ROW_NUMBER() OVER (ORDER BY name) + 1000000) AS fallback_position
                                FROM asset.copy_location
                        ) acpl_ordered ON (acpl_ordered.id = acp.location)
                        LEFT JOIN actor.usr_standing_penalty ausp
                                ON (ahr.usr = ausp.usr AND (ausp.stop_date IS NULL OR ausp.stop_date > NOW()))
                        LEFT JOIN config.standing_penalty csp
                                ON (
                                        csp.id = ausp.standing_penalty AND
                                        csp.block_list LIKE '%CAPTURE%' AND (
                                                (csp.org_depth IS NULL AND ahr.pickup_lib = ausp.org_unit) OR
                                                (csp.org_depth IS NOT NULL AND ahr.pickup_lib IN (
                                                        SELECT id FROM actor.org_unit_descendants(ausp.org_unit, csp.org_depth))
                                                )
                                        )
                                )
                        JOIN (
                                SELECT COUNT(target_copy) AS copy_count, hold
                                FROM action.hold_copy_map
                                GROUP BY 2
                        ) ahcm_1 ON (ahcm_1.hold = ahr.id)
                        LEFT JOIN serial.issuance siss
                                ON (ahr.hold_type = 'I' AND siss.id = ahr.target)
                        LEFT JOIN asset.copy_location_order acplo
                                ON (acp.location = acplo.location AND
                                        acp.circ_lib = acplo.org)
                        WHERE
                                ahr.capture_time IS NULL AND
                                ahr.cancel_time IS NULL AND
                                csp.id IS NULL AND
                                (ahr.expire_time IS NULL OR ahr.expire_time > NOW()) AND
                                acp.STATUS IN (0,7)
                        ) AS "ahopl"
LEFT JOIN action.hold_request_note AS "__ahrn_1" ON ( "__ahrn_1".hold = "ahopl".id )
LEFT JOIN actor.org_unit AS "__aou_1" ON ( "__aou_1".id = "ahopl".request_lib )
LEFT JOIN actor.org_unit AS "__aou_2" ON ( "__aou_2".id = "ahopl".pickup_lib )
LEFT JOIN actor.usr AS "__au_1" ON ( "__au_1".id = "ahopl".usr )
LEFT JOIN config.sms_carrier AS "__csc_1" ON ( "__csc_1".id = "ahopl".sms_carrier )
LEFT JOIN actor.org_unit AS "__aou_3" ON ( "__aou_3".id = "ahopl".selection_ou )
LEFT JOIN asset.copy AS "__acp_1" ON ( "__acp_1".id = "ahopl".current_copy )
LEFT JOIN asset.call_number AS "__acn_1" ON ( "__acn_1".id = "__acp_1".call_number )
LEFT JOIN asset.call_number_prefix AS "__acnp_1" ON ( "__acnp_1".id = "__acn_1".prefix )
LEFT JOIN asset.call_number_suffix AS "__acns_1" ON ( "__acns_1".id = "__acn_1".suffix )
LEFT JOIN biblio.record_entry AS "__bre_1" ON ( "__bre_1".id = "__acn_1".record )
LEFT JOIN reporter.materialized_simple_record AS "__rmsr_1" ON ( "__rmsr_1".id = "__bre_1".id )
LEFT JOIN config.copy_status AS "__ccs_1" ON ( "__ccs_1".id = "__acp_1".STATUS )
LEFT JOIN asset.copy_location AS "__acpl_1" ON ( "__acpl_1".id = "__acp_1".location )
LEFT JOIN actor.org_unit AS "__aou_4" ON ( "__aou_4".id = "__acp_1".circ_lib )
WHERE ( "__aou_4".id = '298' )
ORDER BY "__acpl_1".name ASC, "__acn_1".label_sortkey ASC LIMIT 25 OFFSET 0;