After an update from 18.11 to 20.11.11 we notice that there are a lot of "Unexpected type found during upgrade (Voided)" in the patron accounts. It looks like 19.12.00.027 / Bug 24532 and maybe others are related to this. Following entries have been added: select * from account_credit_types; LOST select * from account_debit_types; Pay W It could be related to voiding entries, but I am not fully understanding the issue yet.
I've noticed the same thing recently on a 18.05 to 21.05 upgrade. We don't have many libraries working with money, so I hadn't noticed it previously.
Adding my findings here in hope that we can still improve the update scripts some: IDENTIFY PROBLEMS select code, description from account_debit_types where description LIKE "Unexpected%" union select code, description from account_credit_types where description LIKE "Unexpected%" * Some problems might be related to entries with amount/amountoutstanding 0. Most of those have status VOID. * Another issue seems to be with written off L (=LOST) fees. * And last, but not least: reversed fines. ------------------ GROUP 1: Pay, VOID, amount/amountoutstanding = 0 debit_type_code: Pay credit_type_code: NULL amount: 0 amount_outstanding: 0 status: VOID Some with entries in account_offsets Some with no entries in account_offsets Possible fix: UPDATE accountlines set credit_type_code = "PAYMENT", debit_type_code = NULL where debit_type_code = "PAY" and status ="VOID" and amount = 0 and amountoutstanding = 0; ------------------ GORUP 2: Pay NOT VOID, not reversed, amount/amountoutstanding = 0 debit_type_code: Pay credit_type_code: NULL amount: 0 amount_outstanding: 0 status: NULL select * from accountlines where (debit_type_code="Pay" or credit_type_code = "Pay") and amount = 0 and amountoutstanding = 0 and description not like "%Reversed%"; Possible fix: UPDATE accountlines set credit_type_code = "PAYMENT", debit_type_code = NULL where (debit_type_code ="Pay" or credit_type_code = "Pay") and amount = 0 and amountoutstanding = 0 and description not like "%Reversed%"; ------------------ GROUP 3: Pay NOT VOID not Reversed, amountoutstanding = 0, amount > 0 debit_type_code: Pay credit_type_code: NULL amount: > 0 amount_outstanding: 0 status: NULL select * from accountlines where (debit_type_code ="Pay" or credit_type_code = "Pay") and description not like "%Reversed%" and amountoutstanding = 0; I am not sure if they are linked to the reversed entries from Group 4, haven't been able to resolve those. ------------------ GROUP 4: Pay Reversed decription: like "%Reversed%" In part these entries have been reversed multiple times with mulitple "Reversed" in the description. I haven't been able to find a solution to those. select * from accountlines where (debit_type_code ="Pay" or credit_type_code = "Pay") and description like "%Reversed%" ------------------ GROUP 5: W, VOID, amount/amountoutstanding = 0 debit_type_code: W credit_type_code: NULL amount: 0 amount_outstanding: 0 status: VOID select * from accountlines WHERE amount = 0 and amountoutstanding = 0 and debit_type_code = "W" and status = "VOID"; Possible fix: UPDATE accountlines set credit_type_code = "WRITEOFF", debit_type_code = NULL WHERE amount = 0 and amountoutstanding = 0 and debit_type_code = "W" and status = "VOID"; ------------------ GROUP 6: W, NOT VOID, amountoutstanding IS NULL debit_type_code W credit_type_code NULL amount: positive value (should be negative) amount_outstanding: NULL status: NULL This has been a rather big group with about 400 entries out of 3200 total. select * from accountlines WHERE debit_type_code = "W" and status IS NULL and amountoutstanding IS NULL; Possible fix: UPDATE accountlines SET amount = amount*-1, credit_type_code = "WRITEOFF", debit_type_code = NULL, amountoutstanding = 0 WHERE debit_type_code = "W" and status IS NULL and amountoutstanding IS NULL; ------------------ GROUP 7: LOST debit_type_code NULL credit_type_code LOST amount: negative value amount_outstanding: NULL status: NULL interface: upgrade LOST exists as a debit type code, but has been added by the update as a credit type code. In account_offsets when looking for the accountlines_id in debit_id and credit_id, we find entries with the debit_id only. credit_id is always filled in, linked to a writeoff line. select * from accountlines WHERE credit_type_code = "LOST" and amount < 0 and amountoutstanding is NULL; Possible fix: UPDATE accountlines SET debit_type_code = "LOST", credit_type_code = NULL, amount = amount*-1, amountoutstanding = 0 WHERE credit_type_code = "LOST" and amount < 0 and amountoutstanding is NULL;
It looks like the values I have are a bit different too... Debits: C LOST_RETURN Pay W Credits: LOST OVERDUE
We share Pay, W and LOST - W was a lot in our database, maybe this could be the first to focus on?
(In reply to Katrin Fischer from comment #4) > We share Pay, W and LOST - W was a lot in our database, maybe this could be > the first to focus on? Sure, sounds good to me. CREDIT: - LOST DEBIT: - Pay - W -- I've taken a look at a 19.11 database: - it looks like "Unexpected type found during upgrade" appears for "W" - there's no evidence of a "Pay" type. - In this case "LOST" is shown as a debit type with a description of "Lost Item" and is marked "is_system". -- Looking at a 20.11 database, I see "LOST" as as a debit and neither "W" nor "Pay" exist. (But this database doesn't have any accountlines...) -- Looking at older databases and the tables "account_credit_types" and "account_debit_types" don't exist... Looking now at Bug 23049...
(In reply to David Cook from comment #5) > CREDIT: > - LOST > > DEBIT: > - Pay > - W I think Martin might be best placed to comment on this one as I think he authored the changes... If we look at the database update for 19.06.00.044, it looks like the values in accountlines were updated as follows: W -> WRITEOFF Pay -> PAYMENT Yikes... looking at the accountlines on a 19.11 system and it's a mess. In one example, the user has 2 debits of $80 and the balance is 0. The W is a Writeoff which should've been a credit to balance the account. The LOST fee is from May 2016 and the writeoff is June 2017. Don't know if that's significant. Must've been a bug back then that never got fixed quite right. -- My 21.05 system started as a 16.11 system I think, so the problem was between 16.11 and 19.11 it seems. -- But yeah... as Martin suggests at https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24532#c1 the solution isn't obvious...
I think overall the math is probably correct - otherwise we'd have a lot of upset people. My advice to libraries so far has been to uncheck the "Can be manually added" box, and add a corresponding description of 'Writeoff', 'Payment', 'Credit', etc. They'll appear in legacy accountlines but won't be able to be added going forward. It sucks but it might be the best we can do without trying to re-write years of accounting data... ...but I also never touch Koha's accounting code, so hopefully Martin or Kyle have a better idea than me.
I think we could do better, but it might just be too late now. I believe that in the history of Koha we sometimes had negative/positive values in the amount, that did look almost the same in the interface (apart from some color coding issues) and the math accounted for that. And that these are probably among those that didn't get migrated correctly.