Find fine notices sent by email

Clearinghouse Repository Polaris Repository
1

This script shows patrons who were sent Fine Notices via email during the dates specified.

This is NOT when the Fine was generated for the account, but when the NOTICE was sent by Polaris.

DECLARE @DeliveryOption VARCHAR(50) = 'Email Address';
DECLARE @NotificationDescriptionLike VARCHAR(50) = '%Fine%';
DECLARE @StartDate DATE = '2025-05-15';
DECLARE @EndDate DATE = '2025-05-29';

SELECT
    p.PatronID,
    p.Barcode,
    pr.NameFirst,
    pr.NameMiddle,
    pr.NameLast,
    pr.EmailAddress,
    nh.Amount AS FineAmountInNotification,
    nh.NoticeDate,
    sdo.DeliveryOption AS DeliveryMethodDescription,
    nt.Description AS NotificationTypeDescription
FROM
    Results.Polaris.NotificationHistory AS nh
JOIN
    Polaris.Polaris.Patrons AS p
    ON nh.PatronID = p.PatronID
JOIN
    Polaris.Polaris.PatronRegistration AS pr
    ON p.PatronID = pr.PatronID
JOIN
    Polaris.Polaris.SA_DeliveryOptions AS sdo
    ON nh.DeliveryOptionID = sdo.DeliveryOptionID
JOIN
    Polaris.Polaris.NotificationTypes AS nt
    ON nh.NotificationTypeID = nt.NotificationTypeID
WHERE
    sdo.DeliveryOption = @DeliveryOption -- Or your system's specific description for email, e.g., 'E-mail'
    AND (
        nt.Description LIKE @NotificationDescriptionLike   -- Catches 'Fine Notice', 'Overdue Fine', etc.
        ) -- Verify these descriptions against your Polaris.Polaris.NotificationTypes table
    AND nh.NoticeDate >= @StartDate
    AND nh.NoticeDate < DATEADD(day, 1, @EndDate)
2

A requested variation that only shows those where the fine notice went out, and the account had fines that were before the start date of looking for when the fine notice was generated. This seems to be a noted problem with Sierra to Polaris migrations.

It is also recommended to review information about how fine notices work.

DECLARE @DeliveryOption VARCHAR(50) = 'Email Address';
DECLARE @NotificationDescriptionLike VARCHAR(50) = '%Fine%';
DECLARE @StartDate DATE = '2025-05-15';
DECLARE @EndDate DATE = '2025-05-29';

SELECT
    p.PatronID,
    p.Barcode,
    pr.NameFirst,
    pr.NameMiddle,
    pr.NameLast,
    pr.EmailAddress,
    nh.Amount AS FineAmountInNotification,
    nh.NoticeDate,
    sdo.DeliveryOption AS DeliveryMethodDescription,
    nt.Description AS NotificationTypeDescription,
    pa.TxnDate AS ChargeTransactionDate,
    pa.TxnAmount AS ChargeTransactionAmount,
    patc.Description AS ChargeTypeDescription,
    pa.OutstandingAmount AS ChargeOutstandingAmount -- Added for visibility of the new condition
FROM
    Results.Polaris.NotificationHistory AS nh
JOIN
    Polaris.Polaris.Patrons AS p
    ON nh.PatronID = p.PatronID
JOIN
    Polaris.Polaris.PatronRegistration AS pr
    ON p.PatronID = pr.PatronID
JOIN
    Polaris.Polaris.SA_DeliveryOptions AS sdo
    ON nh.DeliveryOptionID = sdo.DeliveryOptionID
JOIN
    Polaris.Polaris.NotificationTypes AS nt
    ON nh.NotificationTypeID = nt.NotificationTypeID
JOIN
    Polaris.Polaris.PatronAccount AS pa
    ON p.PatronID = pa.PatronID
JOIN
    Polaris.Polaris.PatronAccTxnCodes AS patc
    ON pa.TxnCodeID = patc.TxnCodeID
WHERE
    sdo.DeliveryOption = @DeliveryOption
    AND nt.Description LIKE @NotificationDescriptionLike
    AND nh.NoticeDate >= @StartDate
    AND nh.NoticeDate < DATEADD(day, 1, @EndDate)
    AND pa.TxnDate < @StartDate                 -- Charge was added before @StartDate
    AND patc.Description LIKE '%Charge%'        -- Transaction is a charge
                                                -- You might need to adjust '%Charge%' to be more specific
                                                -- based on your PatronAccTxnCodes table data
                                                -- (e.g., 'Overdue Charge', 'Lost Item Fee').
    AND pa.OutstandingAmount > 0;               -- The charge still has an outstanding balance