Phone notification errors evergreen

missed phone notifications.sql

-- Report on holds not picked up because of phone notify issues.

select aou.shortname "Patron Home Lib"
, acd.barcode "Patron Barcode", au.family_name, au.first_given_name
,ahr.shelf_expire_time "Holdshelf Expired"
,(select value from metabib.record_attr_flat where attr='icon_format' and id=bre.id limit 1) as Type
,ARRAY_TO_STRING(
    XPATH('//marc:datafield[@tag="245"][1]//text()',
      marc::XML, ARRAY[ARRAY['marc', 'http://www.loc.gov/MARC21/slim']]),' ') as "Title"
,bre.id
from action_trigger.event ate
join action.hold_request ahr on ahr.id=ate.target
join actor.usr au on ahr.usr=au.id
join actor.card acd on acd.id=au.card and acd.active
join actor.org_unit aou on aou.id=au.home_ou
join biblio.record_entry bre on ahr.target=bre.id
where
ate.event_def=120
and ahr.fulfillment_time is null
and ahr.cancel_time is not null
-- and ahr.phone_notify!=au.day_phone
and ahr.hold_type='T'
and ahr.cancel_cause=2
and au.active
and aou.parent_ou=101
and not exists ( --Ever checked out this title
  select 1 from action.circulation ac
  join asset.copy acp on acp.id=ac.target_copy
  join asset.call_number acn on acp.call_number=acn.id
  join biblio.record_entry bre on bre.id=acn.record
  where
  ahr.target=bre.id
  and ac.usr=au.id
  )
order by aou.shortname, au.family_name, au.first_given_name, ahr.shelf_expire_time