From 5af5faaf3d890c449549bb41183e7a3fc03b5439 Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Mon, 30 Nov 2015 09:48:26 -0500 Subject: [PATCH] LP#1206936 - Fix wrong billing info in money.transaction_billing_summary The money.transaction_billing_summary view was showing the wrong last billing type and last billing note for certain transactions. This fix, from Dan Scott, in turn from Mike Rylander, recreates that view so that it depends on the speedier and more accurate money.materialized_billable_xact_summary view. Signed-off-by: Chris Sharp Signed-off-by: Dan Wells --- Open-ILS/src/sql/Pg/080.schema.money.sql | 16 +++++++--------- .../src/sql/Pg/upgrade/XXXX.schema.correct_mtbs_view.sql | 13 +++++++++++++ 2 files changed, 20 insertions(+), 9 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.correct_mtbs_view.sql diff --git a/Open-ILS/src/sql/Pg/080.schema.money.sql b/Open-ILS/src/sql/Pg/080.schema.money.sql index f614da8584..f85fc33778 100644 --- a/Open-ILS/src/sql/Pg/080.schema.money.sql +++ b/Open-ILS/src/sql/Pg/080.schema.money.sql @@ -95,15 +95,13 @@ CREATE OR REPLACE VIEW money.transaction_billing_type_summary AS ORDER BY MAX(billing_ts); CREATE OR REPLACE VIEW money.transaction_billing_summary AS - SELECT xact, - LAST(billing_type) AS last_billing_type, - LAST(note) AS last_billing_note, - MAX(billing_ts) AS last_billing_ts, - SUM(COALESCE(amount,0)) AS total_owed - FROM money.billing - WHERE voided IS FALSE - GROUP BY xact - ORDER BY MAX(billing_ts); + SELECT id as xact, + last_billing_type, + last_billing_note, + last_billing_ts, + total_owed + FROM money.materialized_billable_xact_summary; + CREATE OR REPLACE VIEW money.transaction_payment_summary AS SELECT xact, diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.correct_mtbs_view.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.correct_mtbs_view.sql new file mode 100644 index 0000000000..c45b3c6d18 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.correct_mtbs_view.sql @@ -0,0 +1,13 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE OR REPLACE VIEW money.transaction_billing_summary AS + SELECT id as xact, + last_billing_type, + last_billing_note, + last_billing_ts, + total_owed + FROM money.materialized_billable_xact_summary; + +COMMIT; -- 2.11.0