Deleted Items Billed and Overdue

Clearinghouse Repository Polaris Repository
, , ,
1

This query looks for overdue and replacement charges on patron accounts that are tied to items that have been deleted and removed from the system.

SELECT
	pr.PatronFullName AS [Patron Name],
	p.Barcode AS [Patron Barcode],
	po.DisplayName AS [Patron Branch],
	padir.ItemRecordID,
	padir.BrowseTitle AS [Title],
	padir.BrowseAuthor AS [Author],
	padir.MaterialType AS [Material Type],
	frc.FeeDescription AS [Fee Description],
	CAST(pa.OutstandingAmount AS DECIMAL(20,2)) AS [Outstanding Balance]
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 (p.PatronID = pr.PatronID) 
INNER JOIN -- Patron assigned branch
	Polaris.Polaris.Organizations po WITH (NOLOCK)
	ON (po.OrganizationID = p.OrganizationID)
INNER JOIN
	Polaris.Polaris.FeeReasonCodes frc WITH (NOLOCK)
	ON (frc.FeeReasonCodeID = pa.FeeReasonCodeID)
INNER JOIN -- Pull in deleted item records
	Polaris.Polaris.PatronAcctDeletedItemRecords padir WITH (NOLOCK)
	ON (padir.ItemRecordID = pa.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)
AND -- 0 = Overdue and -1 = Replacement Cost
	pa.FeeReasonCodeID IN (-1,0)
ORDER BY
	po.DisplayName,
	padir.ItemRecordID
1 Like
2

Hi Daniel,

Can you write this so it shows patrons who have lost items linked to them, but the item has been deleted? I’d like to have the Patron Name, Patron Barcode, Title of Item, and Item Barcode. Thanks for any help you can provide.

1 Like
3

Ah! So kind of the reverse of this, right? You’re looking for the patron first, and the item second.

Yeah, I gotta do some SQL work over the next day or two anyway so I think I can throw this in! :smiley: I’ll post about it and tag you when it’s ready!

4

Thank you!! I have a couple other scripts that I used to have, and now I can’t find them. Can I email you?

1 Like
5

Sure! daniel.messer at lsslibraries dot com will work!

6

Okay, I’ve never dropped code into a reply before, but I see no reason why that shouldn’t work. That in mind, this query is a sort-of reverse process from the Deleted Items Billed and Overdue query. Instead of looking for deleted items and the patrons they’re attached to, this query looks for patrons and the deleted items attached to them.

SELECT
	pr.PatronFullName AS [Patron Name],
	p.Barcode AS [Patron Barcode],
	padir.BrowseTitle AS [Title],
	padir.Barcode AS [Item Barcode],
	frc.FeeDescription AS [Fee Description]
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)
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)
AND -- 0 = Overdue and -1 = Replacement Cost
	pa.FeeReasonCodeID IN (-1,0)
ORDER BY
	pr.PatronFullName,
	padir.BrowseTitle
1 Like