Open-ILS/src/sql/Pg/upgrade/0151.schema.acq.spending-limits.sql

   1 BEGIN;
   2
   3 INSERT INTO config.upgrade_log (version) VALUES ('0151'); -- Scott McKellar
   4
   5 ALTER TABLE actor.org_unit
   6         ADD COLUMN spend_warning_percent INT
   7         CONSTRAINT spend_warning_percent_limit
   8                 CHECK( spend_warning_percent <= 100 );
   9
  10 ALTER TABLE actor.org_unit
  11         ADD COLUMN spend_limit_percent INT
  12         CONSTRAINT spend_limit_percent_limit
  13                 CHECK( spend_limit_percent <= 100 );
  14
  15 CREATE OR REPLACE FUNCTION acq.default_spend_limit( org_unit_id IN INT )
  16 RETURNS INTEGER AS $$
  17 DECLARE
  18         org     INT;
  19         key_id  INT;
  20         percent INT;
  21         parent  INT;
  22 BEGIN
  23         org := org_unit_id;
  24         WHILE percent IS NULL LOOP
  25                 SELECT
  26                         id,
  27                         spend_limit_percent,
  28                         parent_ou
  29                 INTO
  30                         key_id,
  31                         percent,
  32                         parent
  33                 FROM
  34                         actor.org_unit
  35                 WHERE
  36                         id = org;
  37                 --
  38                 IF key_id IS NULL THEN
  39                         RAISE EXCEPTION 'Org_unit id % is not valid', org_unit_id;
  40                 END IF;
  41                 --
  42                 IF parent IS NULL THEN
  43                         EXIT;
  44                 ELSE
  45                         org := parent;
  46                 END IF;
  47         END LOOP;
  48         --
  49         IF percent IS NULL THEN
  50                 RETURN 0;              -- Last-ditch default
  51         ELSE
  52                 RETURN percent;
  53         END IF;
  54 END;
  55 $$ LANGUAGE 'plpgsql';
  56
  57 CREATE OR REPLACE FUNCTION acq.default_warning_limit( org_unit_id IN INT )
  58 RETURNS INTEGER AS $$
  59 DECLARE
  60         org     INT;
  61         key_id  INT;
  62         percent INT;
  63         parent  INT;
  64 BEGIN
  65         org := org_unit_id;
  66         WHILE percent IS NULL LOOP
  67                 SELECT
  68                         id,
  69                         spend_warning_percent,
  70                         parent_ou
  71                 INTO
  72                         key_id,
  73                         percent,
  74                         parent
  75                 FROM
  76                         actor.org_unit
  77                 WHERE
  78                         id = org;
  79                 --
  80                 IF key_id IS NULL THEN
  81                         RAISE EXCEPTION 'Org_unit id % is not valid', org_unit_id;
  82                 END IF;
  83                 --
  84                 IF parent IS NULL THEN
  85                         EXIT;
  86                 ELSE
  87                         org := parent;
  88                 END IF;
  89         END LOOP;
  90         --
  91         IF percent IS NULL THEN
  92                 RETURN 10;             -- Last-ditch default
  93         ELSE
  94                 RETURN percent;
  95         END IF;
  96 END;
  97 $$ LANGUAGE 'plpgsql';
  98
  99 COMMIT;
 100
 101 -- If there is no auditor schema, the following ALTERs
 102 -- will fail, and that's okay.  The first one will fail
 103 -- if the fiscal_calendar column is already present.
 104
 105 ALTER TABLE auditor.actor_org_unit_history
 106         ADD COLUMN fiscal_calendar INT;
 107
 108 ALTER TABLE auditor.actor_org_unit_history
 109         ADD COLUMN spend_warning_percent INT;
 110
 111 ALTER TABLE auditor.actor_org_unit_history
 112         ADD COLUMN spend_limit_percent INT;
 113