1 CREATE OR REPLACE FUNCTION _locationTrigger () 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.
11 -- Start with privileges
12 IF (TG_OP = 'INSERT') THEN
13 SELECT checkPrivilege('MaintainLocations') INTO _check;
15 RAISE EXCEPTION 'You do not have privileges to add new Locations.';
18 SELECT checkPrivilege('MaintainLocations') INTO _check;
20 RAISE EXCEPTION 'You do not have privileges to alter a Location.';
25 IF ( (LENGTH(COALESCE(NEW.location_name,''))=0) AND
26 (LENGTH(COALESCE(NEW.location_aisle,''))=0) AND
27 (LENGTH(COALESCE(NEW.location_rack,''))=0) AND
28 (LENGTH(COALESCE(NEW.location_bin,''))=0) ) THEN
29 RAISE EXCEPTION 'You must supply a valid Location Identifier.';
33 IF (NEW.location_warehous_id IS NULL) THEN
34 RAISE EXCEPTION 'You must supply a valid Site.';
37 -- Location Identifier must be unique
38 SELECT location_id INTO _checkId
40 WHERE ( (UPPER(location_name)=UPPER(NEW.location_name))
41 AND (UPPER(location_aisle)=UPPER(NEW.location_aisle))
42 AND (UPPER(location_rack)=UPPER(NEW.location_rack))
43 AND (UPPER(location_bin)=UPPER(NEW.location_bin))
44 AND (location_warehous_id=NEW.location_warehous_id)
45 AND (location_id<>NEW.location_id) );
47 RAISE EXCEPTION 'You must supply a unique Location Identifier for this Site.';
50 -- Populate formatted name
51 IF (NEW.location_aisle IS NOT NULL) THEN
52 NEW.location_formatname := NEW.location_aisle;
54 NEW.location_formatname := '';
57 IF (NEW.location_rack IS NOT NULL) THEN
58 NEW.location_formatname := (NEW.location_formatname || NEW.location_rack);
61 IF (NEW.location_bin IS NOT NULL) THEN
62 NEW.location_formatname := (NEW.location_formatname || NEW.location_bin);
65 IF (NEW.location_name IS NOT NULL) THEN
66 NEW.location_formatname := (NEW.location_formatname || NEW.location_name);
72 $$ LANGUAGE 'plpgsql';
74 SELECT dropIfExists('TRIGGER', 'locationTrigger');
75 CREATE TRIGGER locationTrigger BEFORE INSERT OR UPDATE ON location FOR EACH ROW EXECUTE PROCEDURE _locationTrigger();
77 CREATE OR REPLACE FUNCTION _locationAfterTrigger () RETURNS TRIGGER AS $$
78 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
79 -- See www.xtuple.com/CPAL for the full text of the software license.
85 -- Maintain itemsite_qtyonhand and itemsite_nnqoh when location_netable changes
86 IF (TG_OP = 'UPDATE') THEN
87 IF (OLD.location_netable <> NEW.location_netable) THEN
88 FOR _itemloc IN SELECT * FROM itemloc WHERE (itemloc_location_id=NEW.location_id) LOOP
89 IF (NEW.location_netable) THEN
90 UPDATE itemsite SET itemsite_qtyonhand = itemsite_qtyonhand + _itemloc.itemloc_qty,
91 itemsite_nnqoh = itemsite_nnqoh - _itemloc.itemloc_qty
92 WHERE (itemsite_id=_itemloc.itemloc_itemsite_id);
94 UPDATE itemsite SET itemsite_qtyonhand = itemsite_qtyonhand - _itemloc.itemloc_qty,
95 itemsite_nnqoh = itemsite_nnqoh + _itemloc.itemloc_qty
96 WHERE (itemsite_id=_itemloc.itemloc_itemsite_id);
105 $$ LANGUAGE 'plpgsql';
107 SELECT dropIfExists('TRIGGER', 'locationAfterTrigger');
108 CREATE TRIGGER locationAfterTrigger AFTER INSERT OR UPDATE ON location FOR EACH ROW EXECUTE PROCEDURE _locationAfterTrigger();