Items Charged as Replacement With a Circulating Status
If a patron has a lost item, and they’ve paid a portion of the replacement fee, but then they hand it in? Polaris will not automatically wave the fines and fees. The item can begin circulating as normal but the patron remains on the hook for the fines. This query looks for items that have been charged to a patron’s account, yet they have a status that suggests they’re circulating. Check the results and see if you need to waive some fines on a patron’s account.
SELECT
p.Barcode AS "Patron Barcode",
pr.PatronFullName AS "Patron Name",
CAST(ac.OutstandingAmount AS DECIMAL(20, 2)) AS "Balance",
cir.Barcode AS "Item Barcode",
br.BrowseTitle AS "Title",
cir.LastCircTransactionDate AS "Item Last Activity"
FROM
Polaris.Polaris.PatronAccount ac WITH (NOLOCK)
INNER JOIN
Polaris.Polaris.Patrons p WITH (NOLOCK) ON p.PatronID = ac.PatronID
INNER JOIN
Polaris.Polaris.PatronRegistration pr WITH (NOLOCK) ON p.PatronID = pr.PatronID
INNER JOIN
Polaris.Polaris.Organizations gov WITH (NOLOCK) ON ac.OrganizationID = gov.OrganizationID
LEFT JOIN
Polaris.Polaris.CircItemRecords cir WITH (NOLOCK) ON ac.ItemRecordID = cir.ItemRecordID
LEFT JOIN
Polaris.Polaris.BibliographicRecords br WITH (NOLOCK) ON cir.AssociatedBibRecordID = br.BibliographicRecordID
WHERE
ac.OrganizationID IN (1, 2, 3, 4, 5) -- Limit by given organizations
AND
cir.AssignedBranchID IN (3, 4, 5) -- Limit by assigned branch
AND
cir.ItemStatusID IN (1,2,3,4,5,6,8,9,19,21) -- All of these are circulating statuses
AND
ac.TxnID IN (
SELECT
DISTINCT TxnID
FROM
Polaris.Polaris.PatronAccount WITH (NOLOCK)
WHERE
TxnCodeID IN (1)
AND OutstandingAmount > 0
)
AND
ac.FeeReasonCodeID IN (-1) -- Specifically checks for Replacement Cost