Open-ILS/src/sql/Pg/upgrade/1192.schema.fix_circ_aging.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('1192', :eg_version);
   4
   5 CREATE OR REPLACE FUNCTION action.age_circ_on_delete () RETURNS TRIGGER AS $$
   6 DECLARE
   7 found char := 'N';
   8 BEGIN
   9
  10     -- If there are any renewals for this circulation, don't archive or delete
  11     -- it yet.   We'll do so later, when we archive and delete the renewals.
  12
  13     SELECT 'Y' INTO found
  14     FROM action.circulation
  15     WHERE parent_circ = OLD.id
  16     LIMIT 1;
  17
  18     IF found = 'Y' THEN
  19         RETURN NULL;  -- don't delete
  20         END IF;
  21
  22     -- Archive a copy of the old row to action.aged_circulation
  23
  24     INSERT INTO action.aged_circulation
  25         (id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
  26         copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
  27         circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
  28         stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
  29         max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
  30         max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
  31         auto_renewal, auto_renewal_remaining)
  32       SELECT
  33         id,usr_post_code, usr_home_ou, usr_profile, usr_birth_year, copy_call_number, copy_location,
  34         copy_owning_lib, copy_circ_lib, copy_bib_record, xact_start, xact_finish, target_copy,
  35         circ_lib, circ_staff, checkin_staff, checkin_lib, renewal_remaining, grace_period, due_date,
  36         stop_fines_time, checkin_time, create_time, duration, fine_interval, recurring_fine,
  37         max_fine, phone_renewal, desk_renewal, opac_renewal, duration_rule, recurring_fine_rule,
  38         max_fine_rule, stop_fines, workstation, checkin_workstation, checkin_scan_time, parent_circ,
  39         auto_renewal, auto_renewal_remaining
  40         FROM action.all_circulation WHERE id = OLD.id;
  41
  42     -- Migrate billings and payments to aged tables
  43
  44     INSERT INTO money.aged_billing
  45         SELECT * FROM money.billing WHERE xact = OLD.id;
  46
  47     INSERT INTO money.aged_payment
  48         SELECT * FROM money.payment_view WHERE xact = OLD.id;
  49
  50     DELETE FROM money.payment WHERE xact = OLD.id;
  51     DELETE FROM money.billing WHERE xact = OLD.id;
  52
  53     RETURN OLD;
  54 END;
  55 $$ LANGUAGE 'plpgsql';
  56
  57 COMMIT;