Open-ILS/src/sql/Pg/upgrade/0177.schema.cancel_reason.sql

   1 BEGIN;
   2
   3 INSERT INTO config.upgrade_log (version) VALUES ('0177'); -- Scott McKellar
   4
   5 CREATE TABLE acq.cancel_reason (
   6         id            SERIAL            PRIMARY KEY,
   7         org_unit      INTEGER           NOT NULL REFERENCES actor.org_unit( id )
   8                                         DEFERRABLE INITIALLY DEFERRED,
   9         label         TEXT              NOT NULL,
  10         description   TEXT              NOT NULL,
  11         CONSTRAINT acq_cancel_reason_one_per_org_unit UNIQUE( org_unit, label )
  12 );
  13
  14 -- Reserve ids 1-999 for stock reasons
  15 -- Reserve ids 1000-1999 for EDI reasons
  16 -- 2000+ are available for staff to create
  17
  18 SELECT SETVAL('acq.cancel_reason_id_seq'::TEXT, 2000);
  19
  20 ALTER TABLE acq.purchase_order
  21         ADD COLUMN cancel_reason        INT REFERENCES acq.cancel_reason( id )
  22                                             DEFERRABLE INITIALLY DEFERRED;
  23
  24 ALTER TABLE acq.lineitem
  25         ADD COLUMN cancel_reason        INT REFERENCES acq.cancel_reason( id )
  26                                             DEFERRABLE INITIALLY DEFERRED;
  27
  28 COMMIT;