From 6d24cb9a1b3245ce8e97491503c3b8cc0e48fe9e 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 +++++++--------- .../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 e67c0a77f9..19a88c7141 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.43.2