This query pulls patrons with existing charges on their accounts for deleted items. The fee reason is provided in the query so you can see what the charge was for. (Overdues, replacement, damage, etc) Additionally, we’re pulling the TxnID from PatronAccount. The bulk waiving feature requires TxnIDs to know which fines/fees to waive so, by modifying this query, you could use it as a basis for a bulk waive to clear fines/fees for items removed from the system.
-- Hosted libraries can't use temp tables so let's use a CTE
-- This CTE pulls in item deletion data from PolarisTransactions
WITH ItemDeletedDates AS (
SELECT
item.numValue,
th.TranClientDate
FROM
PolarisTransactions.Polaris.TransactionHeaders th WITH (NOLOCK)
INNER JOIN -- Get the ItemRecordID
PolarisTransactions.Polaris.TransactionDetails item WITH (NOLOCK)
ON (th.TransactionID = item.TransactionID AND item.TransactionSubTypeID = 38)
AND -- Item record deleted
th.TransactionTypeID = 3007
AND -- Limit only to deleted items in PatronAcctDeletedItemRecords
item.numValue IN (
SELECT ItemRecordID
FROM Polaris.Polaris.PatronAcctDeletedItemRecords WITH (NOLOCK))
) -- End ItemDeletedDates CTE
--- D A T A D E L I V E R Y ---
SELECT
pa.TxnID AS [TransactionID], -- This would be useful if you're going to use the bulk waive functionality
padir.ItemRecordID AS [Item Number],
idd.TranClientDate AS [Deletion Date],
padir.BrowseTitle AS [Title],
p.PatronID AS [Patron Number],
p.Barcode AS [Patron Barcode],
pa.TxnDate AS [Charge Date],
CAST(pa.TxnAmount AS DECIMAL(20,2)) [Charge Amount],
frc.FeeDescription AS [Fee Reason]
FROM
Polaris.Polaris.PatronAccount pa WITH (NOLOCK)
INNER JOIN
Polaris.Polaris.Patrons p WITH (NOLOCK)
ON (p.PatronID = pa.PatronID)
INNER JOIN
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK)
ON (pr.PatronID = pa.PatronID)
INNER JOIN
Polaris.Polaris.FeeReasonCodes frc WITH (NOLOCK)
ON (frc.FeeReasonCodeID = pa.FeeReasonCodeID)
INNER JOIN
Polaris.Polaris.PatronAcctDeletedItemRecords padir WITH (NOLOCK)
ON (padir.ItemRecordID = pa.ItemRecordID)
INNER JOIN -- Use the ItemRecordID brought in by the CTE
ItemDeletedDates idd
ON (idd.numValue = padir.ItemRecordID)
WHERE -- Look for patrons with outstanding balances
pa.TxnID IN (
SELECT DISTINCT TxnID
FROM Polaris.Polaris.PatronAccount WITH (NOLOCK)
WHERE TxnCodeID IN (1)
AND OutstandingAmount > 0)
-- Uncomment the line below and drop in FeeReasonCodeIDs below if you want to limit to specific types of charges
--AND pa.FeeReasonCodeID IN (-1,0) -- 0 = Overdue and -1 = Replacement Cost
ORDER BY
idd.TranClientDate DESC;