by Andrew Fuerste-Henry on May 7, 2020
UPDATE: Please check out our separate blog post about new credits created in your upgrade for old charges. You can see it here.
19.11 brings some big changes to how Koha records debits and credits in the database. These changes will make for clearer data and easier reporting, but are going to require updates to existing reports. Similar to the changes around overdue fines we saw in 19.05, the goal here is to both separate type from status in accountlines entries and to make the recorded values for type understandable without any sort of relabeling or consulting a key.
The bugs
The changes here are split across several bugs:
- [23805] Add a dedicated credit_types table
- [23049] Replace MANUAL_INV authorised value with a dedicated table
- [22563] Convert lost handling to use ‘status’ instead of multiple accounttypes
- [22610] SIP Payment Types should be moved out of accountype
- [6759] Use a different account type for account renewals than for new accounts
Schema changes
After all those bugs and patches, you’ll see some changes in the accountlines table in 19.11. Here’s the schema page for it.
The most relevant change here is that the accounttype field is gone. It’s been replaced with two separate fields, credit_type_code and debit_type_code. Credits will have a credit_type_code, debits will have a debit_type_code, and nothing should ever have both. You’ll also notice that those two new fields each link off to a new table.
Credit_type_code links to the account_credit_types table, which lists all of the possible credit types. These are all hard-coded and there is not a way to add custom credit types, though one can still define custom payment types as before.
Debit_type_code links to the account_debit_types table, which lists all of the possible debit types. There are several hard-coded values here for the basic debit types that Koha uses by default. You can add custom debit types through a new interface in the Administration module, which replaces the manual invoice authorized value.
Translating values
All of your existing accountlines will be translated during your upgrade to match the new data structure. Your reports will want to reference these new values, which will find both pre- and post-19.11 data.
One of the benefits of this new structure is that it becomes really easy to make your report include only credits or only debits. For only credits, use “WHERE credit_type_code is not NULL.” For only debits, change that to “WHERE debit_type_code is not NULL.”
If you’ve got existing reports that limit to specific types of credits or debits, those will need to be edited to the new values. For credits, that means translating the following 19.05 accounttype values to the given 19.11 credit_type_code counterparts:
- C becomes CREDIT
- CR becomes LOST_RETURN
- FOR becomes FORGIVEN
- PAY becomes PAYMENT
- PAY00 becomes PAYMENT with a payment_type of SIP00
- PAY01 becomes PAYMENT with a payment_type of SIP01
- PAY02 becomes PAYMENT with a payment_type of SIP02
- W becomes WRITEOFF
Any of those credit types might be paired with the status “VOID” (recorded in the field accountlines.status).
Translating debit codes gets a bit more complicated, as there are more possibilities and also more possible statuses. Translate the following 19.05 accounttype values into the given 19.11 debit_type_code and status counterparts:
- L becomes LOST with no status
- LR becomes LOST with the status RETURNED
- PF becomes PROCESSING
- N becomes NEW_CARD
- A becomes ACCOUNT for account creation and ACCOUNT_RENEW for account renewal
- RENT becomes RENT, RENT_DAILY, RENT_RENEW, or RENT_DAILY_RENEW, depending on the checkout period and whether it’s charged for an initial checkout or a renewal
The OVERDUE accounttype simply becomes the OVERDUE debit_type_code with the same statuses that were introduced in 19.05 (as explained here), but with one change. When an overdue item becomes lost, it’s OVERDUE accountline will change to status LOST rather than status RETURNED.
While these changes to Koha’s structure and processing are going to require updates to a number of reports, they are definitely going to result in cleaner data and easier reports going forward. Please let us know if you have any questions or reports you’d like help getting adjust to these new formats.
Read more by Andrew Fuerste-Henry