If any of you use an outside vendor for phone notifications, can you tell me what happens in the system when the vendor is unable to reach the patron?
We export all of our phone calls to Unique. They will attempt to contact a patron up to 3 times and if they can’t reach the patron or leave a voicemail, they will report back to Polaris with a notification status of “5” - “Call Not Completed - No answer.”
However, according to the PAPI documentation, Polaris will only revert to print notice for specific notification statuses:
As a result, we have a bunch of patrons who get stuck in the notificaiton queue. Since they never receive one of the overdue notifications, they are never eligible for billing notices and they just sit as long overdues. I’ve tried to get Unique to change the status from “Call not completed” to something more serious like “maximum number of retries exceeded” but they keep avoiding the issue.
Does anyone else do this or have a better solution?
I think we’ve had to write a custom SQL job to do the rollover to mail because of PAPI limitations. I’m paging my colleague @mfields to see if he can add some details and/or post the code we use.
Yep, as Wes indicated I ended up writing a sql job to roll the unsuccessful notices to print for our phone notice app. Depending on what kind of access you have to your database you might not have the ability to run the update command to do the rollover though, and you’re probably stuck with a PAPI solution. Even if Unique doesn’t want to play ball and update the notices with a status the rolls them to print you could use PAPI and update them yourself if you wanted. I’d be curious how they can really argue against updating them with a status of ‘10 - max retries exceeded’ at the end of the day to roll them over though since, like you said, that ‘notice gets updated’ piece is vital to the entire overdue process.
This is the code behind the stored procedure that does our rollover:
CREATE PROCEDURE [dbo].[TwilioMailRollover]
@libraryString varchar(255)
AS
BEGIN
SET NOCOUNT ON;
update results.polaris.NotificationQueue
set DeliveryOptionID = 1, Processed = 1
from results.polaris.NotificationQueue nq
join polaris.polaris.Patrons p on
p.PatronID = nq.PatronID
join polaris.polaris.Organizations o on
p.OrganizationID = o.OrganizationID
where nq.PatronID in
(
select PatronID
from PolarisTransactions.polaris.NotificationLog nl
where DeliveryOptionID in ( 3,4,5 )
and NotificationDateTime > cast(getdate() as date)
and NotificationStatusID != 1
and not exists (
select 1
from PolarisTransactions.Polaris.NotificationLog nl2
where nl2.PatronID = nl.PatronID
and nl2.NotificationStatusID = 1
and cast(nl2.NotificationDateTime as date) = cast(nl.NotificationDateTime as date)
and nl2.DeliveryOptionID = nl.DeliveryOptionID
)
group by patronid
having COUNT(*) > 1
)
and nq.DeliveryOptionID in (3, 4, 5)
and o.ParentOrganizationID in ( select Item from CLC_Notices.dbo.CLC_Custom_SplitString(@libraryString, ';') )
select @@rowcount
END
GO
