Some money.payment.xact do not exist in money.billable_xact.id

[Blake GH]

EG 2.4.1
EG 2.6.1

select * from money.payment where xact not in(select id from money.billable_xact)

should return 0 rows but it doesn't. There are payments referring to nothing. I haven't put my finger on it but there must be code that inserts rows into money.payment after getting the next sequence but fails to insert the row into money.billable_xact.

This manifests null/blank columns when running reports related to payments and billing.

[Chris Sharp (chrissharp123)]

Here's what I see in PINES:

evergreen=# select count(*) from money.payment where xact not in(select id from money.billable_xact);
  count
---------
 3081039
(1 row)

However, here's what I see when I check the xact ID against the action.all_circulation table:

evergreen=# select count(*) from money.payment where xact not in(select id from action.all_circulation);
 count
--------
 466335
(1 row)

That accounts for most of the payments not in the money.billable_xact table, but there are still 466,335 that don't attach to a transaction, whether archived or not.

Marking confirmed.

[Blake GH (bmagic)]

I just figured this out! Deleting patrons causes this!
When a patron is deleted, the action.circulation and money.billable_xact rows are deleted. However, all of the associated money.billing and money.payment rows remain.

Is that a bug?

[Michele Morgan (mmorgan)]

I'm inclined to mark this one Won't Fix.

Bug 1793802 and followup bug 1858448 address aging billing and payment data with circulations, but I'm not sure if this eliminates issues with reports.

If no objection to marking Won't Fix, and others concur, please mark it as such!