Bug 29559

Summary: Update creates "unexpected type" entries for standard codes in debit and credit types
Product: Koha Reporter: Katrin Fischer <katrin.fischer>
Component: Fines and feesAssignee: Bugs List <koha-bugs>
Status: CLOSED WONTFIX QA Contact: Testopia <testopia>
Severity: major    
Priority: P5 - low CC: dcook, jonathan.druart, martin.renvoize
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Bug Depends on: 24532    
Bug Blocks:    

Description Katrin Fischer 2021-11-23 09:34:57 UTC
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.
Comment 1 David Cook 2021-11-24 22:32:56 UTC
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.
Comment 2 Katrin Fischer 2021-11-25 10:27:41 UTC
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;
Comment 3 David Cook 2021-12-02 01:48:12 UTC
It looks like the values I have are a bit different too...

Debits:
C
LOST_RETURN
Pay
W

Credits:
LOST
OVERDUE
Comment 4 Katrin Fischer 2021-12-02 08:39:37 UTC
We share Pay, W and LOST - W was a lot in our database, maybe this could be the first to focus on?
Comment 5 David Cook 2021-12-02 23:22:06 UTC
(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...
Comment 6 David Cook 2021-12-02 23:56:56 UTC
(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...
Comment 7 David Cook 2021-12-03 00:04:32 UTC
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.
Comment 8 Katrin Fischer 2022-01-09 00:25:47 UTC
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.