Evergreen 3.4beta1+ (Tested on 3.4.1)
LP Bug #1793802 introduced two new tables aged_payment and aged_billing, (along with their "all" counterpart views).
In testing 3.4.1 in preparation for our upgrade, I noticed the aged_payment table in particular strips too much relevant data.
When a payment is aged, it appears to be removed from its corresponding type-specific payment table, such as cash_payment, forgive_payment, etc, and is moved to the aged_payment table.
You can run this query as an example to see that no payments simultaneously exist in the aged_payment table and the bnm_payment table. You can use any type-specific table for pay2 in this query.
select *
from money.aged_payment pay
join money.bnm_payment pay2 on pay2.xact=pay.xact
limit 100
This creates a major problem. The type-specific payment tables include information on Accepting User and Cash Drawer, which tells us who and where the payment was accepted.
This information isn't in the aged_payment table.
So, for every circulation we age that had payments attached, we now lose where the payments were made and who accepted the payments. This is a major problem for CW MARS as we age circulations after 7 days of return. That means a patron could make a payment on a long checked in circulation, and later that day that payment could be aged. As far as Evergreen is concerned at that point, there is no way to know that library accepted that payment, just that it was made somewhere.
Where this is most immediately seen is in the the Cash Report found under Local Administration. This report is severely affected by aging payments. When comparing a monthly cash drawer from just last month on a server running 3.2.8 and another running 3.4.1, the one running 3.4.1 was calculating roughly half of the amount as 3.2.8.
This is a major issue in our network because libraries rely on cash reports to compare their cash drawer and ensure they're taking in the correct amount of money. We also have roughly a dozen reports that libraries can run that list payments accepted by a certain library. These reports are no longer accurate as no aged payments are listed in them since aging payments strips away accepting user and cash drawer, (accepting workstation).
In the short term, we may want to remove this aging function from release 3.4 before more networks go live on the release. Libraries may not be realizing payments are "disappearing".
In the long term, I would argue the aged tables should contain more data so that libraries can track payments made at their library regardless of if the payment belongs to an aged circulation.
There is also precedent for keeping this type of data. The aged_circulation data contains a ton of library tracking data such as checkout and checkin library and workstation, item owning library, etc. Similarly, if we are aging payments and billings, we should ensure a library can keep track of their data.
Just quickly looking through the database and client, I've come up with the following changes to keep the tables relevant and useful:
all_payments and aged_payment tables need:
-- Accepting_User (for both active and aged payments in ALL)
-- Cash_drawer (for both active and aged payments in ALL)
---- Cash_drawer will be NULL for non-drawer payments.
-- Billing (for account_adjustments; for both active and aged payments in ALL)
all_billings and aged_billing tables need:
--Billing_location (for grocery bills; for both active and aged billings in ALL)
For credit cards, there are also the following fields, but it may not be necessary to keep this information:
--cc_number
--cc_processer
--cc_order_number
--approval_code
** OPAC Credit card payments store the patron's ID as accepting_user. When aging credit card payments, we may want to NULL out the accepting_user field or assign it a '1'. Otherwise the link between user and payment/circ is kept: https://bugs.launchpad.net/evergreen/+bug/1417148
Changes to the Reporter:
--all_payments and all_billings need to stay linked in the Reporter from the Aged Circulation source and Combined Aged and Active Circulations source.
--all_payments need to provide a link in the reporter interface to actor.usr via accepting_user and actor.workstation via cash_drawer.
--all_payments and all_billings need to provide a link to money.billable_transaction via xact.
** It needs to be possible to start at Combined Circs and obtain the barcode/login for accepting user as well as the workstation owning library for the cash drawer regardless of if the circulation is aged or active.
Local Admin > Cash Report
--The Cash Report needs to be updated to work with aged payments. Once accepting_user and cash_drawer are added to all_payments, it may be possible to point to this table.
Finally, there are also two views in the reporter schema, (that I'm not sure are CW MARS specific or not). But xact_billing_totals and xact_payment_totals are currently using the active billing and payment tables, respectively. They should be updated to use all_billings and all_payments.
There may be other places in the Reporter or staff client that are affected by these tables, but no others come to mind right away.
Marking this bug confirmed and setting the priority to High as this will have a far reaching affect on cash reporting even when a system does not routinely age circulations. Each time a patron record is deleted their circulations are aged, so any associated payments will also be aged and ability to track those payments will be lost.
To confirm this, I ran a cash report for a library for a 1 month period.
Desk payments totaled $718.80.
I then deleted a single patron whose record had expired and who had paid a number of charges during the same time period. I ran the cash report again for the same time period.
Desk payments now total $589.50.
It's vital to be able to track all payments, aged or otherwise, back to where they were received.