In Sierra we used to be able to find items that were “Lost and Paid”. That doesn’t seem to be an option in Polaris. Items have the status of Lost even after the patron has paid for the item. Is there a way to get a list of “Lost” items that haven’t been paid for and also a list of “Lost” items that have been paid for? Someone in our Innovative Basecamp suggested this:
“In an SQL query, you could use the PatronAccount table to see if there is a charge linked to the item.”
However we are about to switch over from Basecamp to Support and Basecamp doesn’t have the time right now to work on it for us.
PatronA has the item go to lost, they get charged a replacement fee
PatronA returns the item, but does NOT pay the replacement fee and that fee doesn’t get waived
PatronB checks out the SAME item and that item goes to lost, they get charged a replacement fee
PatronB pays the replacement fee
So, the item IS lost, and there is still a replacement fee on PatronA’s account but they didn’t have the item most recently.
Should the show up in the search/report or not? Basically, should the item only appear on the report if the most recent patron has an outstanding balance or should it show up if ANY patron has an outstanding balance related to that item? What if instead of a remaining replacement charge there was an overdue charge for any patron out there? Or a damaged part charge? Should they show up?
Finally, you might try going to ahead and deleting an item in your test system. Polaris retains a surprising amount of data on deleted items, it might be enough for your audit trail purposes. Though it is true that a deleted item’s barcode won’t be “found” when you check something in, so if that is important than you do need to keep the item records around.
Here is a starting point that can be used in the item find tool, but as you can guess this will cause the item to show up if there are ANY outstanding balances for ANY patrons that have a feereasoncodeid of (9, 8, -1) you should check your system for what those codes mean and adjust as needed.
SELECT DISTINCT
it.ItemRecordID
FROM
Polaris.Polaris.ItemRecords AS it
JOIN
Polaris.Polaris.PatronAccount AS charge
ON it.ItemRecordID = charge.ItemRecordID
WHERE
it.ItemStatusID = 7
AND charge.FeeReasonCodeID IN (9, 8, -1)
AND charge.OutstandingAmount > 0
and it.RecordStatusID != 4
Thanks @wesochuck You gave us enough to start with and asked the right questions for us to tweak it a bit to get the expected results.
SELECT DISTINCT
it.ItemRecordID As "ID",
it.Barcode AS "Barcode"
FROM
ItemRecords AS it
JOIN
PatronAccount AS charge
ON it.ItemRecordID = charge.ItemRecordID
WHERE
it.ItemStatusID = 7
AND charge.FeeReasonCodeID IN (8, -1)
AND charge.PaymentMethodID IN (11, 12, 13)
AND charge.OutstandingAmount > 0
and it.RecordStatusID !=4
I run a report in Simply Reports for items with a status of Lost. Two of the output columns we put in the report are “Patron barcode for lost item” and “Patron full name for lost item”. I then sort the report by these columns. If the columns are empty those are my Lost and Paid For items.
Thanks so much @otplanne. There were more results in the Simply Reports than in the query. But they all do seem to be valid lost and paid items. So I’ll use the simply reports. But because I’m curious I would like to get the same results from both so we need to tweak our query more. Also I noticed that items are showing up in our query that have only partial paid lost items and we don’t want those to be included in completely lost and paid items since they still have a balance owing.
