[Koha-bugs] [Bug 29559] Update creates "unexpected type" entries for standard codes in debit and credit types

[bugzilla-daemon at bugs.koha-community.org]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29559

--- Comment #2 from Katrin Fischer <katrin.fischer at 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.