https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29559
--- Comment #2 from Katrin Fischer <[email protected]> --- 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. _______________________________________________ Koha-bugs mailing list [email protected] https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
