Some reports sources still display wrong last billing type and note

[Chris Sharp]

As originally reported in bug 1206936, certain reports sources do not consistently show the correct last billing type or note. The views I identified in comment 1 of that ticket are here:

-- money.billable_xact_summary_new
-- money.billable_xact_with_void_summary
-- money.open_transaction_billing_summary
-- money.open_transaction_billing_type_summary
-- money.open_transaction_payment_summary
-- money.transaction_billing_type_summary
-- money.transaction_billing_with_void_summary
-- money.transaction_payment_summary
-- money.transaction_payment_with_void_summary

We've hit an issue with a commonly-used PINES report that needs this fixed.

Evergreen 2.11.1-ish
OpenSRF 2.4.1
PostgreSQL 9.4
Ubuntu 14.04 LTS

[Mike Rylander (mrylander)]

Chris,

It looks like we may not have to go full-on window function to fix those. Consider the following test of a redefinition of the money.open_transaction_billing_type_summary view:

-- Given a billable transaction exhibiting the issue, replace XXXX below.

SELECT
    billing.xact,
    last(billing.billing_type ORDER BY billing.billing_ts) AS last_billing_type,
    last(billing.note ORDER BY billing.billing_ts) AS last_billing_note,
    max(billing.billing_ts) AS last_billing_ts,
    sum(COALESCE(billing.amount, 0::numeric)) AS total_owed
   FROM money.billing
  WHERE billing.voided IS FALSE AND billing.xact = XXXX
  GROUP BY billing.xact, billing.billing_type;

In my testing, this is just as fast as the one not using the ORDER BY clause in the aggregate functions.