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_usr_summary;
   7 DROP VIEW money.open_billable_xact_summary;
   8
   9 CREATE OR REPLACE VIEW money.billable_xact_summary AS
  10     SELECT * FROM money.materialized_billable_xact_summary;
  11
  12 CREATE OR REPLACE VIEW money.open_billable_xact_summary AS
  13     SELECT * FROM money.billable_xact_summary_location_view
  14     WHERE xact_finish IS NULL;
  15
  16 CREATE OR REPLACE VIEW money.open_usr_circulation_summary AS
  17     SELECT
  18         usr,
  19         SUM(total_paid) AS total_paid,
  20         SUM(total_owed) AS total_owed,
  21         SUM(balance_owed) AS balance_owed
  22     FROM  money.materialized_billable_xact_summary
  23     WHERE xact_type = 'circulation' AND xact_finish IS NULL
  24     GROUP BY usr;
  25
  26 CREATE OR REPLACE VIEW money.usr_summary AS
  27     SELECT
  28         usr,
  29         sum(total_paid) AS total_paid,
  30         sum(total_owed) AS total_owed,
  31         sum(balance_owed) AS balance_owed
  32     FROM money.materialized_billable_xact_summary
  33     GROUP BY usr;
  34
  35 CREATE OR REPLACE VIEW money.open_usr_summary AS
  36     SELECT
  37         usr,
  38         sum(total_paid) AS total_paid,
  39         sum(total_owed) AS total_owed,
  40         sum(balance_owed) AS balance_owed
  41     FROM money.materialized_billable_xact_summary
  42     WHERE xact_finish IS NULL
  43     GROUP BY usr;
  44
  45 COMMIT;