https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29559 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on| |24532 CC| |jonathan.druart+koha@gmail. | |com Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24532 [Bug 24532] Some account types are converted to debits when they shouldn't be -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29559 --- Comment #2 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- 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; -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29559 --- Comment #4 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- We share Pay, W and LOST - W was a lot in our database, maybe this could be the first to focus on? -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29559 --- Comment #6 from David Cook <dcook@prosentient.com.au> --- (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... -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29559 --- Comment #8 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- 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. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.