Open-ILS/src/sql/Pg/upgrade/0128.schema.billing-matt-views.sql

   1 BEGIN;
   2
   3 INSERT INTO config.upgrade_log (version) VALUES ('0128');
   4
   5 DROP VIEW money.open_usr_circulation_summary;
   6 DROP VIEW money.open_billable_xact_summary;
   7
   8 CREATE OR REPLACE VIEW money.billable_xact_summary AS
   9     SELECT * FROM money.materialized_billable_xact_summary;
  10
  11 CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
  12     SELECT * FROM money.billable_xact_summary_location_view
  13     WHERE xact_finish IS NULL;
  14
  15 CREATE OR REPLACE VIEW money.open_usr_circulation_summary AS
  16     SELECT
  17         usr,
  18         SUM(total_paid) AS total_paid,
  19         SUM(total_owed) AS total_owed,
  20         SUM(balance_owed) AS balance_owed
  21     FROM  money.materialized_billable_xact_summary
  22     WHERE xact_type = 'circulation' AND xact_finish IS NULL
  23     GROUP BY usr;
  24
  25 CREATE OR REPLACE VIEW money.usr_summary AS
  26     SELECT
  27         usr,
  28         sum(total_paid) AS total_paid,
  29         sum(total_owed) AS total_owed,
  30         sum(balance_owed) AS balance_owed
  31     FROM money.materialized_billable_xact_summary
  32     GROUP BY usr;
  33
  34 CREATE OR REPLACE VIEW money.open_usr_summary AS
  35     SELECT
  36         usr,
  37         sum(total_paid) AS total_paid,
  38         sum(total_owed) AS total_owed,
  39         sum(balance_owed) AS balance_owed
  40     FROM money.materialized_billable_xact_summary
  41     WHERE xact_finish IS NULL
  42     GROUP BY usr;
  43
  44 COMMIT;