Payment by billing type breakdown

Bug #1174498 reported by Mike Rylander on 2013-04-29
52
This bug affects 9 people
Affects Status Importance Assigned to Milestone
Evergreen
Wishlist
Unassigned

Bug Description

Teach Evergreen to maintain a payment-to-billing mat-view that maps a payment to all the billings it covers. The code attached here does not know about the 1.6-era controlled billing types, so it needs updating, but is otherwise close to ready after having a solid once-over.

Mike Rylander (mrylander) wrote :

And here is the IDL entry needed (again, 1.2/1.4 era).

Mike Rylander (mrylander) wrote :

This is a replacement to the (now removed) SQL from long ago. This one is from the same era, and has a minor set of changes the correct an issue spotted by Jeff Godin back then.

Jeff Godin (jgodin) on 2013-05-24
Changed in evergreen:
assignee: nobody → Jeff Godin (jgodin)
Jeff Godin (jgodin) wrote :

With some help from Mike Rylander, I'm working on this "vintage" code with the intention of bringing it current and preparing it for possible inclusion.

Current status: the stored proc functions without raising errors in a 2.2 snapshot of production, focusing on transactions with payments made on or after 2012-01-01.

Next steps: output will be checked for accuracy while some of the stated issues (lack of awareness of numeric billing types) is addressed.

Working branch to follow.

Ben Shum (bshum) on 2013-05-24
Changed in evergreen:
status: New → In Progress
Mike Rylander (mrylander) wrote :

Jeff, do you have this branch handy that I might help in final polishing?

Jeff Godin (jgodin) wrote :

We've been using mmpbbt in production for several weeks, and I'd like to shoot for getting it ready for pullrequest in time for the 2.6 beta. As such, I'm targeting it and shall endeavor to do some final tweaking with enough time for review.

Changed in evergreen:
milestone: none → 2.6.0-beta1
Dan Wells (dbw2) on 2014-02-27
Changed in evergreen:
milestone: 2.6.0-beta1 → 2.next
Blake GH (bmagic) wrote :

When trying to load this new table with existing data I get many unique constraint errors. I cannot seem to come up with a way of finding those unique constraint breakers. I think it has something to do with transactions containing refunds. I did have success inserting rows into the table by narrowing the query a bit:

CREATE OR REPLACE FUNCTION tmp_populate_p_b_bt () RETURNS BOOL AS $$
DECLARE
    p RECORD;
BEGIN
    FOR p IN
        SELECT DISTINCT xact
          FROM money.payment
          WHERE NOT voided
                AND amount > 0.0
    and xact in(select id from money.billable_xact where xact_start>'2014-01-01')
    and
    xact not in
    (
    select id from money.billable_xact where id in
(
select xact from (
select xact,count(*) from money.billing where amount>0 group by xact having count(*)>1
) as a
)
and id in
(
select xact from (
select xact,count(*) from money.payment where amount>0 group by xact having count(*)>1
) as b
) and xact_start> '2014-01-01'
)
    LOOP
...
...
..

This will only populate transactions since the beginning of the year. Of those transactions, only ones that didn't have multiple billing lines AND multiple payments.

In case anyone finds it useful.

Blake GH (bmagic) wrote :

Here are some details for one constraint example:

ERROR: duplicate key value violates unique constraint "x_p_b_once"
DETAIL: Key (xact, payment, billing)=(2504059, 188199, 3408744) already exists.

money.payment rows:
188199;2504059;"2014-08-18 16:27:43.876616-05";f;0.25;""
188198;2504059;"2014-08-18 16:27:24.274333-05";f;0.10;""

money.billing rows:
3413280;2504059;"2014-08-16 23:59:59-05";f;;"";0.15;"Overdue materials";1;"System Generated Overdue Fine"
3410418;2504059;"2014-08-15 23:59:59-05";f;;"";0.15;"Overdue materials";1;"System Generated Overdue Fine"
3408744;2504059;"2014-08-14 23:59:59-05";f;;"";0.15;"Overdue materials";1;"System Generated Overdue Fine"

So, it looks like the function has targeted the same billing id twice for the same payment. The 25 cents is paying billing line 3408744 two times. There must be something wrong with the logic or I don't have the correct function on my test machine.

Blake GH (bmagic) wrote :

I went ahead and put this in my working branch. I committed the original code from Jeff and Mike. Then I committed my changes.

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=commit;h=b37eb844ea81105c6ee7601b397f86c747b321c6

I found that the function was applying payments to the same billing lines. For each payment row, it would start over at the first billing row again. I added logic to keep track of the billing row. I also corrected an issue with voided billing rows by sorting the query by voided.

tags: added: pullrequest
Blake GH (bmagic) wrote :

Added the upgrade script

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/blake/LP1174498

We have been running this in production for over 6 months. Many of our libraries enjoy the extra reporting detail that this is providing!

Kathy Lussier (klussier) on 2015-06-08
Changed in evergreen:
status: In Progress → Triaged
assignee: Jeff Godin (jgodin) → nobody
Kathy Lussier (klussier) wrote :

Adding a comment that this code will need a release notes entry as well as tests. Maybe something to tackle during our community test day.

tags: added: needsreleasenote needstests
Blake GH (bmagic) on 2015-09-02
tags: added: billing
Ben Shum (bshum) wrote :

Changing tag to use the new singular "needstest" for consistency.

tags: added: needstest
removed: needstests
Blake GH (bmagic) wrote :

I just committed a change to this branch. We were seeing issues with EG 2.9. Specifically the account_adjustment payment type. Those lines should* match a billing line with an equal amount. The change does a better job of lining those up. Same link
http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/blake/LP1174498

Bill Erickson (berick) wrote :

Removing pullrequest tag, given 'needstests' and 'needsreleasenotes'.

tags: removed: pullrequest
Blake GH (bmagic) wrote :

Haven't got around to writing tests for this one. However, it looks like I might be hacking on this a bit in order to change the payment_ou to be set to the staff account's OU instead of the patron's OU in order to make it consistent with the "Cash Report" located in Local Administration.

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Duplicates of this bug

Other bug subscribers