WITH
        t_field AS (SELECT field FROM config.display_field_map WHERE name = 'title'),
        a_field AS (SELECT field FROM config.display_field_map WHERE name = 'author'),
        s_field AS (SELECT field FROM config.display_field_map WHERE name = 'series_title'),
        y_field AS (SELECT field FROM config.display_field_map WHERE name = 'pubdate')
    SELECT  h.id, h.request_time, h.capture_time, h.fulfillment_time, h.checkin_time,
            h.return_time, h.prev_check_time, h.expire_time, h.cancel_time, h.cancel_cause,
            h.cancel_note, h.target, h.current_copy, h.fulfillment_staff, h.fulfillment_lib,
            h.request_lib, h.requestor, h.usr, h.selection_ou, h.selection_depth, h.pickup_lib,
            h.hold_type, h.holdable_formats, h.phone_notify, h.email_notify, h.sms_notify,
            (SELECT name FROM config.sms_carrier WHERE id = h.sms_carrier) AS "sms_carrier",
            h.frozen, h.thaw_date, h.shelf_time, h.cut_in_line, h.mint_condition,
            h.shelf_expire_time, h.current_shelf_lib, h.behind_desk, h.hopeless_date,
            h.canceled_by, h.canceling_ws,

            CASE WHEN h.cancel_time IS NOT NULL THEN 6
                 WHEN h.frozen AND h.capture_time IS NULL THEN 7
                 WHEN h.current_shelf_lib IS NOT NULL AND h.current_shelf_lib <> h.pickup_lib THEN 8
                 WHEN h.fulfillment_time IS NOT NULL THEN 9
                 WHEN h.current_copy IS NULL THEN 1
                 WHEN h.capture_time IS NULL THEN 2
                 WHEN cp.status = 6 THEN 3
                 WHEN EXTRACT(EPOCH FROM COALESCE(NULLIF(BTRIM(hold_wait_time.value,'"'),''),'0 seconds')::INTERVAL) = 0 THEN 4
                 WHEN h.shelf_time + COALESCE(NULLIF(BTRIM(hold_wait_time.value,'"'),''),'0 seconds')::INTERVAL > NOW() THEN 5
                 ELSE 4
            END AS hold_status,

            (h.shelf_expire_time < 'today'::timestamptz OR h.cancel_time IS NOT NULL OR (h.current_shelf_lib IS NOT NULL AND h.current_shelf_lib <> h.pickup_lib)) AS clear_me,

            (h.usr <> h.requestor) AS is_staff_hold,

            cc.id AS cc_id, cc.label AS cc_label,

            pl.id AS pl_id, pl.parent_ou AS pl_parent_ou, pl.ou_type AS pl_ou_type,
            pl.ill_address AS pl_ill_address, pl.holds_address AS pl_holds_address,
            pl.mailing_address AS pl_mailing_address, pl.billing_address AS pl_billing_address,
            pl.shortname AS pl_shortname, pl.name AS pl_name, pl.email AS pl_email,
            pl.phone AS pl_phone, pl.opac_visible AS pl_opac_visible, pl.fiscal_calendar AS pl_fiscal_calendar,

            ol.shortname AS ol_shortname,
            cl.shortname AS cl_shortname,
            rl.shortname AS rl_shortname,
            sl.shortname AS sl_shortname,
            tl.shortname AS tl_shortname,
            ul.shortname AS ul_shortname,

            tr.id AS tr_id, tr.source_send_time AS tr_source_send_time, tr.dest_recv_time AS tr_dest_recv_time,
            tr.target_copy AS tr_target_copy, tr.source AS tr_source, tr.dest AS tr_dest, tr.prev_hop AS tr_prev_hop,
            tr.copy_status AS tr_copy_status, tr.persistant_transfer AS tr_persistant_transfer,
            tr.prev_dest AS tr_prev_dest, tr.hold AS tr_hold, tr.cancel_time AS tr_cancel_time,

            notes.count AS note_count,

            u.id AS usr_id, u.card AS usr_card, u.profile AS usr_profile, u.usrname AS usr_usrname,
            u.email AS usr_email, u.standing AS usr_standing, u.ident_type AS usr_ident_type,
            u.ident_value AS usr_ident_value, u.ident_type2 AS usr_ident_type2,
            u.ident_value2 AS usr_ident_value2, u.net_access_level AS usr_net_access_level,
            u.photo_url AS usr_photo_url, u.prefix AS usr_prefix, u.first_given_name AS usr_first_given_name,
            u.second_given_name AS usr_second_given_name, u.family_name AS usr_family_name,
            u.suffix AS usr_suffix, u.alias AS usr_alias, u.day_phone AS usr_day_phone,
            u.evening_phone AS usr_evening_phone, u.other_phone AS usr_other_phone,
            u.mailing_address AS usr_mailing_address, u.billing_address AS usr_billing_address,
            u.home_ou AS usr_home_ou, u.dob AS usr_dob, u.active AS usr_active,
            u.master_account AS usr_master_account, u.super_user AS usr_super_user,
            u.barred AS usr_barred, u.deleted AS usr_deleted, u.juvenile AS usr_juvenile,
            u.usrgroup AS usr_usrgroup, u.claims_returned_count AS usr_claims_returned_count,
            u.credit_forward_balance AS usr_credit_forward_balance, u.last_xact_id AS usr_last_xact_id,
            u.create_date AS usr_create_date,
            u.expire_date AS usr_expire_date, u.claims_never_checked_out_count AS usr_claims_never_checked_out_count,
            u.last_update_time AS usr_last_update_time,

            pgt.name as pgt_name,

            CASE WHEN NULLIF(u.alias,'') IS NOT NULL THEN
                u.alias
            ELSE
                u.first_given_name
            END AS usr_alias_or_first_given_name,

            CASE WHEN NULLIF(u.alias,'') IS NOT NULL THEN
                u.alias
            ELSE
                REGEXP_REPLACE(ARRAY_TO_STRING(ARRAY[
                    COALESCE(NULLIF(u.pref_family_name,''), u.family_name, ''),
                    COALESCE(NULLIF(u.pref_suffix,''), u.suffix, ''),
                    ', ',
                    COALESCE(NULLIF(u.pref_prefix,''), u.prefix, ''),
                    COALESCE(NULLIF(u.pref_first_given_name,''), u.first_given_name, ''),
                    COALESCE(NULLIF(u.pref_second_given_name,''), u.second_given_name, '')
                ], ' '), E'\s+,', ',')
            END AS usr_alias_or_display_name,

            REGEXP_REPLACE(ARRAY_TO_STRING(ARRAY[
                COALESCE(NULLIF(u.pref_family_name,''), u.family_name, ''),
                COALESCE(NULLIF(u.pref_suffix,''), u.suffix, ''),
                ', ',
                COALESCE(NULLIF(u.pref_prefix,''), u.prefix, ''),
                COALESCE(NULLIF(u.pref_first_given_name,''), u.first_given_name, ''),
                COALESCE(NULLIF(u.pref_second_given_name,''), u.second_given_name, '')
            ], ' '), E'\s+,', ',') AS usr_display_name,

            uc.id AS ucard_id, uc.barcode AS ucard_barcode, uc.usr AS ucard_usr, uc.active AS ucard_active,

            ru.id AS rusr_id, ru.card AS rusr_card, ru.profile AS rusr_profile, ru.usrname AS rusr_usrname,
            ru.email AS rusr_email, ru.standing AS rusr_standing, ru.ident_type AS rusr_ident_type,
            ru.ident_value AS rusr_ident_value, ru.ident_type2 AS rusr_ident_type2,
            ru.ident_value2 AS rusr_ident_value2, ru.net_access_level AS rusr_net_access_level,
            ru.photo_url AS rusr_photo_url, ru.prefix AS rusr_prefix, ru.first_given_name AS rusr_first_given_name,
            ru.second_given_name AS rusr_second_given_name, ru.family_name AS rusr_family_name,
            ru.suffix AS rusr_suffix, ru.alias AS rusr_alias, ru.day_phone AS rusr_day_phone,
            ru.evening_phone AS rusr_evening_phone, ru.other_phone AS rusr_other_phone,
            ru.mailing_address AS rusr_mailing_address, ru.billing_address AS rusr_billing_address,
            ru.home_ou AS rusr_home_ou, ru.dob AS rusr_dob, ru.active AS rusr_active,
            ru.master_account AS rusr_master_account, ru.super_user AS rusr_super_user,
            ru.barred AS rusr_barred, ru.deleted AS rusr_deleted, ru.juvenile AS rusr_juvenile,
            ru.usrgroup AS rusr_usrgroup, ru.claims_returned_count AS rusr_claims_returned_count,
            ru.credit_forward_balance AS rusr_credit_forward_balance, ru.last_xact_id AS rusr_last_xact_id,
            ru.create_date AS rusr_create_date,
            ru.expire_date AS rusr_expire_date, ru.claims_never_checked_out_count AS rusr_claims_never_checked_out_count,
            ru.last_update_time AS rusr_last_update_time,

            ruc.id AS rucard_id, ruc.barcode AS rucard_barcode, ruc.usr AS rucard_usr, ruc.active AS rucard_active,
            cuc.barcode AS canceled_by_barcode, cu.usrname AS canceled_by_usrname,
            caw.name as canceling_ws_name,

            cp.id AS cp_id, cp.circ_lib AS cp_circ_lib, cp.creator AS cp_creator, cp.call_number AS cp_call_number,
            cp.editor AS cp_editor, cp.create_date AS cp_create_date, cp.edit_date AS cp_edit_date,
            cp.copy_number AS cp_copy_number, cp.status AS cp_status, cp.location AS cp_location,
            cp.loan_duration AS cp_loan_duration, cp.fine_level AS cp_fine_level, cp.age_protect AS cp_age_protect,
            cp.circulate AS cp_circulate, cp.deposit AS cp_deposit, cp.ref AS cp_ref, cp.holdable AS cp_holdable,
            cp.deposit_amount AS cp_deposit_amount, cp.price AS cp_price, cp.barcode AS cp_barcode,
            cp.circ_modifier AS cp_circ_modifier, cp.circ_as_type AS cp_circ_as_type, cp.dummy_title AS cp_dummy_title,
            cp.dummy_author AS cp_dummy_author, cp.alert_message AS cp_alert_message, cp.opac_visible AS cp_opac_visible,
            cp.deleted AS cp_deleted, cp.floating AS cp_floating, cp.dummy_isbn AS cp_dummy_isbn,
            cp.status_changed_time AS cp_status_change_time, cp.active_date AS cp_active_date,
            cp.mint_condition AS cp_mint_condition, cp.cost AS cp_cost,

            cs.id AS cs_id, cs.name AS cs_name, cs.holdable AS cs_holdable, cs.opac_visible AS cs_opac_visible,
            cs.copy_active AS cs_copy_active, cs.restrict_copy_delete AS cs_restrict_copy_delete,
            cs.is_available AS cs_is_available,

            siss.label AS issuance_label,

            cn.id AS cn_id, cn.creator AS cn_creator, cn.create_date AS cn_create_date, cn.editor AS cn_editor,
            cn.edit_date AS cn_edit_date, cn.record AS cn_record, cn.owning_lib AS cn_owning_lib, cn.label AS cn_label,
            cn.deleted AS cn_deleted, cn.prefix AS cn_prefix, cn.suffix AS cn_suffix, cn.label_class AS cn_label_class,
            cn.label_sortkey AS cn_label_sortkey,

            p.id AS p_id, p.record AS p_record, p.label AS p_label, p.label_sortkey AS p_label_sortkey, p.deleted AS p_deleted,

            acnp.label AS ancp_label, acns.label AS ancs_label,
            TRIM(acnp.label || ' ' || cn.label || ' ' || acns.label) AS cn_full_label,

            r.bib_record AS record_id,

            t.value AS title,
            a.value AS author,
            s.value AS series_title,
            y.value AS pubdate,

            acpl.id AS acpl_id, acpl.name AS acpl_name, acpl.owning_lib AS acpl_owning_lib, acpl.holdable AS acpl_holdable,
            acpl.hold_verify AS acpl_hold_verify, acpl.opac_visible AS acpl_opac_visible, acpl.circulate AS acpl_circulate,
            acpl.label_prefix AS acpl_label_prefix, acpl.label_suffix AS acpl_label_suffix,
            acpl.checkin_alert AS acpl_checkin_alert, acpl.deleted AS acpl_deleted, acpl.url AS acpl_url,

            COALESCE(acplo.position, acpl_ordered.fallback_position) AS copy_location_order_position,

            ROW_NUMBER() OVER (
                PARTITION BY r.bib_record
                ORDER BY h.cut_in_line DESC NULLS LAST, h.request_time ASC
            ) AS relative_queue_position,

            EXTRACT(EPOCH FROM COALESCE(
                NULLIF(BTRIM(default_estimated_wait_interval.value,'"'),''),
                '0 seconds'
            )::INTERVAL) AS default_estimated_wait,

            EXTRACT(EPOCH FROM COALESCE(
                NULLIF(BTRIM(min_estimated_wait_interval.value,'"'),''),
                '0 seconds'
            )::INTERVAL) AS min_estimated_wait,

            COALESCE(hold_wait.potenials,0) AS potentials,
            COALESCE(hold_wait.other_holds,0) AS other_holds,
            COALESCE(hold_wait.total_wait_time,0) AS total_wait_time,

            n.count AS notification_count,
            n.max AS last_notification_time

      FROM  action.hold_request h
            JOIN reporter.hold_request_record r ON (r.id = h.id)
            JOIN actor.usr u ON (u.id = h.usr)
            JOIN permission.grp_tree pgt ON (u.profile = pgt.id)
            JOIN actor.card uc ON (uc.id = u.card)
            JOIN actor.usr ru ON (ru.id = h.requestor)
            JOIN actor.card ruc ON (ruc.id = ru.card)
            JOIN actor.org_unit pl ON (h.pickup_lib = pl.id)
            JOIN actor.org_unit rl ON (h.request_lib = rl.id)
            JOIN actor.org_unit sl ON (h.selection_ou = sl.id)
            JOIN actor.org_unit ul ON (u.home_ou = ul.id)
            JOIN t_field ON TRUE
            JOIN a_field ON TRUE
            JOIN s_field ON TRUE
            JOIN y_field ON TRUE
            LEFT JOIN actor.usr cu ON (h.canceled_by = cu.id)
            LEFT JOIN actor.card cuc ON (cu.card = cuc.id)
            LEFT JOIN actor.workstation caw ON (h.canceling_ws = caw.id)
            LEFT JOIN action.hold_request_cancel_cause cc ON (h.cancel_cause = cc.id)
            LEFT JOIN biblio.monograph_part p ON (h.hold_type = 'P' AND p.id = h.target)
            LEFT JOIN serial.issuance siss ON (h.hold_type = 'I' AND siss.id = h.target)
            LEFT JOIN asset.copy cp ON (h.current_copy = cp.id OR (h.hold_type IN ('C','F','R') AND cp.id = h.target))
            LEFT JOIN actor.org_unit cl ON (cp.circ_lib = cl.id)
            LEFT JOIN config.copy_status cs ON (cp.status = cs.id)
            LEFT JOIN asset.copy_location acpl ON (cp.location = acpl.id)
            LEFT JOIN asset.copy_location_order acplo ON (cp.location = acplo.location AND cp.circ_lib = acplo.org)
            LEFT JOIN (
                SELECT *, (ROW_NUMBER() OVER (ORDER BY name) + 1000000) AS fallback_position
                FROM asset.copy_location
            ) acpl_ordered ON (acpl_ordered.id = cp.location)
            LEFT JOIN asset.call_number cn ON ((cn.id = cp.call_number AND h.hold_type != 'V' ) OR (h.hold_type = 'V' AND cn.id = h.target))
            LEFT JOIN asset.call_number_prefix acnp ON (cn.prefix = acnp.id)
            LEFT JOIN asset.call_number_suffix acns ON (cn.suffix = acns.id)
            LEFT JOIN actor.org_unit ol ON (cn.owning_lib = ol.id)
            LEFT JOIN LATERAL (SELECT * FROM action.hold_transit_copy WHERE h.id = hold ORDER BY id DESC LIMIT 1) tr ON TRUE
            LEFT JOIN actor.org_unit tl ON (tr.source = tl.id)
            LEFT JOIN LATERAL (SELECT COUNT(*) FROM action.hold_request_note WHERE h.id = hold AND (pub = TRUE OR staff = true)) notes ON TRUE
            LEFT JOIN LATERAL (SELECT COUNT(*), MAX(notify_time) FROM action.hold_notification WHERE h.id = hold) n ON TRUE
            LEFT JOIN LATERAL (SELECT FIRST(value) AS value FROM metabib.display_entry WHERE source = r.bib_record AND field = t_field.field) t ON TRUE
            LEFT JOIN LATERAL (SELECT FIRST(value) AS value FROM metabib.display_entry WHERE source = r.bib_record AND field = a_field.field) a ON TRUE
            LEFT JOIN LATERAL (SELECT FIRST(value) AS value FROM metabib.display_entry WHERE source = r.bib_record AND field = s_field.field) s ON TRUE
            LEFT JOIN LATERAL (SELECT FIRST(value) AS value FROM metabib.display_entry WHERE source = r.bib_record AND field = y_field.field) y ON TRUE
            LEFT JOIN LATERAL actor.org_unit_ancestor_setting('circ.holds.default_estimated_wait_interval',u.home_ou) AS default_estimated_wait_interval ON TRUE
            LEFT JOIN LATERAL actor.org_unit_ancestor_setting('circ.holds.min_estimated_wait_interval',u.home_ou) AS min_estimated_wait_interval ON TRUE
            LEFT JOIN LATERAL actor.org_unit_ancestor_setting('circ.hold_shelf_status_delay',h.pickup_lib) AS hold_wait_time ON TRUE,
            LATERAL (
                SELECT  COUNT(*) AS potenials,
                        COUNT(DISTINCT hold) AS other_holds,
                        SUM(
                            EXTRACT(EPOCH FROM
                                COALESCE(
                                    cm.avg_wait_time,
                                    COALESCE(NULLIF(BTRIM(default_estimated_wait_interval.value,'"'),''),'0 seconds')::INTERVAL
                                )
                            )
                        ) AS total_wait_time
                  FROM  action.hold_copy_map m
                        JOIN asset.copy cp ON (cp.id = m.target_copy)
                        LEFT JOIN config.circ_modifier cm ON (cp.circ_modifier = cm.code)
                  WHERE m.hold = h.id
            ) AS hold_wait
      WHERE             (h.capture_time IS NULL OR (h.id = (
                    SELECT  id
                      FROM  action.hold_request recheck
                      WHERE recheck.current_copy = cp.id
                            AND recheck.capture_time IS NOT NULL
                      ORDER BY capture_time DESC
                      LIMIT 1
                )))

     AND cs.id = '8' AND cp.deleted = 'f' AND capture_time IS NOT NULL  AND current_shelf_lib = '4' AND fulfillment_time IS  NULL  ORDER BY shelf_expire_time
;