git.roojs.org Git - xtuple/blob - foundation-database/public/trigger_functions/shiptoinfo.sql

   1 CREATE OR REPLACE FUNCTION _shiptoinfoAfterTrigger () RETURNS TRIGGER AS $$
   2 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
   3 -- See www.xtuple.com/CPAL for the full text of the software license.
   4 DECLARE
   5   _cmnttypeid INTEGER;
   6
   7 BEGIN
   8   IF (NEW.shipto_default) THEN
   9     UPDATE shiptoinfo
  10     SET shipto_default = false
  11     WHERE ((shipto_cust_id=NEW.shipto_cust_id)
  12     AND (shipto_id <> NEW.shipto_id));
  13   END IF;
  14
  15   IF (SELECT fetchMetricBool('CustomerChangeLog')) THEN
  16 --  Cache the cmnttype_id for ChangeLog
  17     SELECT cmnttype_id INTO _cmnttypeid
  18     FROM cmnttype
  19     WHERE (cmnttype_name='ChangeLog');
  20     IF (FOUND) THEN
  21       IF (TG_OP = 'INSERT') THEN
  22         PERFORM postComment(_cmnttypeid, 'C', NEW.shipto_cust_id, 'Created');
  23
  24       ELSIF (TG_OP = 'UPDATE') THEN
  25         IF (OLD.shipto_name <> NEW.shipto_name) THEN
  26           PERFORM postComment( _cmnttypeid, 'C', NEW.shipto_cust_id,
  27                                ( NEW.shipto_name || ': Ship To Name Changed from "' || COALESCE(OLD.shipto_name, '') ||
  28                                  '" to "' || COALESCE(NEW.shipto_name, '') || '"' ) );
  29         END IF;
  30         IF (OLD.shipto_shipvia <> NEW.shipto_shipvia) THEN
  31           PERFORM postComment( _cmnttypeid, 'C', NEW.shipto_cust_id,
  32                                ( NEW.shipto_name || ': Ship To ShipVia Changed from "' || COALESCE(OLD.shipto_shipvia, '') ||
  33                                  '" to "' || COALESCE(NEW.shipto_shipvia, '') || '"' ) );
  34         END IF;
  35         IF (COALESCE(OLD.shipto_taxzone_id, -1) <> COALESCE(NEW.shipto_taxzone_id, -1)) THEN
  36           PERFORM postComment( _cmnttypeid, 'C', NEW.shipto_cust_id,
  37                                ( NEW.shipto_name || ': Ship To Tax Zone Changed from "' || COALESCE((SELECT taxzone_code
  38                                                                                             FROM taxzone
  39                                                                                             WHERE taxzone_id=OLD.shipto_taxzone_id), 'None') ||
  40                                  '" to "' || COALESCE((SELECT taxzone_code
  41                                               FROM taxzone
  42                                               WHERE taxzone_id=NEW.shipto_taxzone_id), 'None') || '"' ) );
  43         END IF;
  44         IF (OLD.shipto_shipzone_id <> NEW.shipto_shipzone_id) THEN
  45           PERFORM postComment( _cmnttypeid, 'C', NEW.shipto_cust_id,
  46                                ( NEW.shipto_name || ': Ship To Shipping Zone Changed from "' || (SELECT shipzone_name
  47                                                                                                  FROM shipzone
  48                                                                                                  WHERE shipzone_id=OLD.shipto_shipzone_id) ||
  49                                  '" to "' || (SELECT shipzone_name
  50                                               FROM shipzone
  51                                               WHERE shipzone_id=NEW.shipto_shipzone_id) || '"' ) );
  52         END IF;
  53         IF (OLD.shipto_salesrep_id <> NEW.shipto_salesrep_id) THEN
  54           PERFORM postComment( _cmnttypeid, 'C', NEW.shipto_cust_id,
  55                                ( NEW.shipto_name || ': Ship To Sales Rep Changed from "' || (SELECT salesrep_name
  56                                                                                              FROM salesrep
  57                                                                                              WHERE salesrep_id=OLD.shipto_salesrep_id) ||
  58                                  '" to "' || (SELECT salesrep_name
  59                                               FROM salesrep
  60                                               WHERE salesrep_id=NEW.shipto_salesrep_id) || '"' ) );
  61         END IF;
  62         IF (OLD.shipto_active <> NEW.shipto_active) THEN
  63           IF (NEW.shipto_active) THEN
  64             PERFORM postComment(_cmnttypeid, 'C', NEW.shipto_cust_id, (NEW.shipto_name || ': Ship To Activated'));
  65           ELSE
  66             PERFORM postComment(_cmnttypeid, 'C', NEW.shipto_cust_id, (NEW.shipto_name || ': Ship To Deactivated'));
  67           END IF;
  68         END IF;
  69       END IF;
  70     END IF;
  71   END IF;
  72
  73   RETURN NEW;
  74 END;
  75 $$ LANGUAGE 'plpgsql';
  76
  77 DROP TRIGGER shiptoinfoAfterTrigger ON shiptoinfo;
  78 CREATE TRIGGER shiptoinfoAfterTrigger AFTER INSERT OR UPDATE ON shiptoinfo FOR EACH ROW EXECUTE PROCEDURE _shiptoinfoAfterTrigger();
  79