1 CREATE OR REPLACE FUNCTION _itemsiteTrigger () 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.
10 -- Cache some information
11 -- Added item_number as part of feature request 21645
12 SELECT item_type, item_number INTO _r
14 WHERE (item_id=NEW.itemsite_item_id);
16 -- Override values to avoid invalid data combinations
17 IF (_r.item_type IN ('J','R','S')) THEN
18 NEW.itemsite_planning_type := 'N';
21 IF (_r.item_type = 'L') THEN
22 NEW.itemsite_planning_type := 'S';
25 IF (TG_OP = 'UPDATE') THEN
26 IF ( (NEW.itemsite_qtyonhand <> OLD.itemsite_qtyonhand) ) THEN
27 IF (OLD.itemsite_freeze) THEN
28 NEW.itemsite_qtyonhand := OLD.itemsite_qtyonhand;
30 NEW.itemsite_datelastused := CURRENT_DATE;
33 IF ( (NEW.itemsite_qtyonhand < 0) AND (OLD.itemsite_qtyonhand >= 0) AND (NEW.itemsite_eventfence > 0) ) THEN
34 PERFORM postEvent('QOHBelowZero', 'I', NEW.itemsite_id,
36 (item_number || '/' || warehous_code),
37 NULL, NULL, NULL, NULL)
39 WHERE (item_id=NEW.itemsite_item_id)
40 AND (warehous_id=NEW.itemsite_warehous_id);
43 IF ( (NEW.itemsite_value <> OLD.itemsite_value) AND (OLD.itemsite_freeze) ) THEN
44 NEW.itemsite_value := OLD.itemsite_value;
48 -- Added item_number to error messages displayed to fulfill Feature Request 21645
49 IF (NEW.itemsite_qtyonhand < 0 AND NEW.itemsite_costmethod = 'A') THEN
50 RAISE EXCEPTION 'Itemsite (%) is set to use average costing and is not allowed to have a negative quantity on hand.', 'ID: ' || NEW.itemsite_id || ', Item: ' || _r.item_number;
51 ELSIF (NEW.itemsite_value < 0 AND NEW.itemsite_costmethod = 'A') THEN
52 RAISE EXCEPTION 'This transaction results in a negative itemsite value. Itemsite (%) is set to use average costing and is not allowed to have a negative value.', 'ID: ' || NEW.itemsite_id || ', Item: ' || _r.item_number; END IF;
54 -- Handle the ChangeLog
55 IF ( SELECT (metric_value='t')
57 WHERE (metric_name='ItemSiteChangeLog') ) THEN
59 -- Cache the cmnttype_id for ChangeLog
60 SELECT cmnttype_id INTO _cmnttypeid
62 WHERE (cmnttype_name='ChangeLog');
64 IF (TG_OP = 'INSERT') THEN
65 PERFORM postComment(_cmnttypeid, 'IS', NEW.itemsite_id, 'Created');
67 ELSIF (TG_OP = 'UPDATE') THEN
69 IF (OLD.itemsite_plancode_id <> NEW.itemsite_plancode_id) THEN
70 PERFORM postComment( _cmnttypeid, 'IS', NEW.itemsite_id,
71 ( 'Planner Code Changed from "' || oldplancode.plancode_code ||
72 '" to "' || newplancode.plancode_code || '"' ) )
73 FROM plancode AS oldplancode, plancode AS newplancode
74 WHERE ( (oldplancode.plancode_id=OLD.itemsite_plancode_id)
75 AND (newplancode.plancode_id=NEW.itemsite_plancode_id) );
78 IF (NEW.itemsite_reorderlevel <> OLD.itemsite_reorderlevel) THEN
79 PERFORM postComment( _cmnttypeid, 'IS', NEW.itemsite_id,
80 ( 'Reorder Level Changed from ' || formatQty(OLD.itemsite_reorderlevel) ||
81 ' to ' || formatQty(NEW.itemsite_reorderlevel ) ) );
84 IF (NEW.itemsite_ordertoqty <> OLD.itemsite_ordertoqty) THEN
85 PERFORM postComment( _cmnttypeid, 'IS', NEW.itemsite_id,
86 ( 'Order Up To Changed from ' || formatQty(OLD.itemsite_ordertoqty) ||
87 ' to ' || formatQty(NEW.itemsite_ordertoqty ) ) );
90 IF (NEW.itemsite_leadtime <> OLD.itemsite_leadtime) THEN
91 PERFORM postComment( _cmnttypeid, 'IS', NEW.itemsite_id,
92 ( 'Itemsite Leadtime Changed from ' || formatQty(OLD.itemsite_leadtime) ||
93 ' to ' || formatQty(NEW.itemsite_leadtime ) ) );
96 IF (NEW.itemsite_abcclass <> OLD.itemsite_abcclass) THEN
97 PERFORM postComment( _cmnttypeid, 'IS', NEW.itemsite_id,
98 ( 'Itemsite ABC Class Changed from ' || COALESCE(OLD.itemsite_abcclass, 'None') ||
99 ' to ' || COALESCE(NEW.itemsite_abcclass,'None') ) );
102 IF (NEW.itemsite_controlmethod <> OLD.itemsite_controlmethod) THEN
103 PERFORM postComment( _cmnttypeid, 'IS', NEW.itemsite_id,
104 ( 'Itemsite Control Method Changed from ' || COALESCE(OLD.itemsite_controlmethod,'None') ||
105 ' to ' || COALESCE(NEW.itemsite_controlmethod,'None') ) );
108 IF (OLD.itemsite_sold <> NEW.itemsite_sold) THEN
109 PERFORM postComment( _cmnttypeid, 'IS', NEW.itemsite_id,
110 CASE WHEN (NEW.itemsite_sold) THEN 'Sold Changed from FALSE to TRUE'
111 ELSE 'Sold Changed from TRUE to FALSE'
115 IF (OLD.itemsite_active <> NEW.itemsite_active) THEN
116 IF (NEW.itemsite_active) THEN
117 PERFORM postComment(_cmnttypeid, 'IS', NEW.itemsite_id, 'Activated');
119 PERFORM postComment(_cmnttypeid, 'IS', NEW.itemsite_id, 'Deactivated');
130 $$ LANGUAGE 'plpgsql';
132 SELECT dropIfExists('trigger', 'itemsiteTrigger');
133 CREATE TRIGGER itemsiteTrigger BEFORE INSERT OR UPDATE ON itemsite FOR EACH ROW EXECUTE PROCEDURE _itemsiteTrigger();
135 CREATE OR REPLACE FUNCTION _itemsiteAfterTrigger () RETURNS TRIGGER AS $$
136 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
137 -- See www.xtuple.com/CPAL for the full text of the software license.
140 _wasLocationControl BOOLEAN;
141 _isLocationControl BOOLEAN;
142 _wasLotSerial BOOLEAN;
143 _isLotSerial BOOLEAN;
144 _wasPerishable BOOLEAN;
145 _isPerishable BOOLEAN;
154 SELECT fetchMetricText('Application') INTO _application;
156 -- Check if we are doing maintenance
157 IF (TG_OP = 'INSERT') THEN
159 ELSIF (TG_OP = 'UPDATE') THEN
160 IF ((OLD.itemsite_item_id != NEW.itemsite_item_id)
161 OR (OLD.itemsite_warehous_id != NEW.itemsite_warehous_id)
162 OR (OLD.itemsite_reorderlevel != NEW.itemsite_reorderlevel)
163 OR (OLD.itemsite_ordertoqty != NEW.itemsite_ordertoqty)
164 OR (OLD.itemsite_cyclecountfreq != NEW.itemsite_cyclecountfreq)
165 OR (OLD.itemsite_planning_type != NEW.itemsite_planning_type)
166 OR (OLD.itemsite_posupply != NEW.itemsite_posupply)
167 OR (OLD.itemsite_wosupply != NEW.itemsite_wosupply)
168 OR (OLD.itemsite_loccntrl != NEW.itemsite_loccntrl)
169 OR (OLD.itemsite_safetystock != NEW.itemsite_safetystock)
170 OR (OLD.itemsite_minordqty != NEW.itemsite_minordqty)
171 OR (OLD.itemsite_multordqty != NEW.itemsite_multordqty)
172 OR (OLD.itemsite_leadtime != NEW.itemsite_leadtime)
173 OR (OLD.itemsite_abcclass != NEW.itemsite_abcclass)
174 OR (OLD.itemsite_controlmethod != NEW.itemsite_controlmethod)
175 OR (OLD.itemsite_active != NEW.itemsite_active)
176 OR (OLD.itemsite_plancode_id != NEW.itemsite_plancode_id)
177 OR (OLD.itemsite_costcat_id != NEW.itemsite_costcat_id)
178 OR (OLD.itemsite_eventfence != NEW.itemsite_eventfence)
179 OR (OLD.itemsite_sold != NEW.itemsite_sold)
180 OR (OLD.itemsite_stocked != NEW.itemsite_stocked)
181 OR (OLD.itemsite_location_id != NEW.itemsite_location_id)
182 OR (OLD.itemsite_recvlocation_id != NEW.itemsite_recvlocation_id)
183 OR (OLD.itemsite_issuelocation_id != NEW.itemsite_issuelocation_id)
184 OR (OLD.itemsite_location_dist != NEW.itemsite_location_dist)
185 OR (OLD.itemsite_recvlocation_dist != NEW.itemsite_recvlocation_dist)
186 OR (OLD.itemsite_issuelocation_dist != NEW.itemsite_issuelocation_dist)
187 OR (OLD.itemsite_useparams != NEW.itemsite_useparams)
188 OR (OLD.itemsite_useparamsmanual != NEW.itemsite_useparamsmanual)
189 OR (OLD.itemsite_soldranking != NEW.itemsite_soldranking)
190 OR (OLD.itemsite_createpr != NEW.itemsite_createpr)
191 OR (OLD.itemsite_location != NEW.itemsite_location)
192 OR (OLD.itemsite_location_comments != NEW.itemsite_location_comments)
193 OR (OLD.itemsite_notes != NEW.itemsite_notes)
194 OR (OLD.itemsite_perishable != NEW.itemsite_perishable)
195 OR (OLD.itemsite_autoabcclass != NEW.itemsite_autoabcclass)
196 OR (OLD.itemsite_ordergroup != NEW.itemsite_ordergroup)
197 OR (OLD.itemsite_disallowblankwip != NEW.itemsite_disallowblankwip)
198 OR (OLD.itemsite_maxordqty != NEW.itemsite_maxordqty)
199 OR (OLD.itemsite_mps_timefence != NEW.itemsite_mps_timefence)
200 OR (OLD.itemsite_createwo != NEW.itemsite_createwo)
201 OR (OLD.itemsite_warrpurc != NEW.itemsite_warrpurc)
202 OR (OLD.itemsite_costmethod != NEW.itemsite_costmethod)
203 OR (OLD.itemsite_autoreg != NEW.itemsite_autoreg)
204 OR (OLD.itemsite_lsseq_id != NEW.itemsite_lsseq_id) ) THEN
205 IF (OLD.itemsite_item_id != NEW.itemsite_item_id) THEN
206 RAISE EXCEPTION 'The item number on an itemsite may not be changed.';
207 ELSIF (OLD.itemsite_warehous_id != NEW.itemsite_warehous_id) THEN
208 RAISE EXCEPTION 'The warehouse code on an itemsite may not be changed.';
216 IF (_maint) THEN -- Begin Maintenance
218 IF ( NOT checkPrivilege('MaintainItemSites') ) THEN
219 RAISE EXCEPTION 'You do not have privileges to maintain Item Sites.';
222 -- Override values to avoid invalid data combinations
223 IF (NOT NEW.itemsite_posupply) THEN
225 itemsite_createpr = FALSE
226 WHERE (itemsite_id=NEW.itemsite_id);
228 IF (NOT NEW.itemsite_wosupply) THEN
230 itemsite_createwo = FALSE
231 WHERE (itemsite_id=NEW.itemsite_id);
234 IF (NEW.itemsite_controlmethod NOT IN ('S','L')) THEN
236 itemsite_perishable = FALSE,
237 itemsite_warrpurc = FALSE,
238 itemsite_autoreg = FALSE,
239 itemsite_lsseq_id = NULL
240 WHERE (itemsite_id=NEW.itemsite_id);
243 IF (NOT NEW.itemsite_loccntrl) THEN
245 itemsite_disallowblankwip = FALSE
246 WHERE (itemsite_id=NEW.itemsite_id);
249 IF (NOT NEW.itemsite_useparams) THEN
251 itemsite_reorderlevel = 0,
252 itemsite_ordertoqty = 0,
253 itemsite_minordqty = 0,
254 itemsite_maxordqty = 0,
255 itemsite_multordqty = 0,
256 itemsite_useparamsmanual = FALSE
257 WHERE (itemsite_id = NEW.itemsite_id);
262 -- Both insert and update
263 IF ( (NEW.itemsite_controlmethod IN ('S', 'L')) AND
264 (NEW.itemsite_location_dist OR NEW.itemsite_recvlocation_dist OR NEW.itemsite_issuelocation_dist) ) THEN
265 RAISE EXCEPTION 'You cannot auto-distribute Lot/Serial controlled Item Sites.';
268 IF (TG_OP = 'INSERT') THEN
270 IF ( (NEW.itemsite_loccntrl) AND (NEW.itemsite_warehous_id IS NOT NULL) ) THEN
271 IF (SELECT count(*)=0
273 WHERE ((location_warehous_id=NEW.itemsite_warehous_id)
274 AND ( (NOT location_restrict) OR
275 ( (location_restrict) AND
276 (location_id IN ( SELECT locitem_location_id
278 WHERE (locitem_item_id=NEW.itemsite_item_id) ) ) ) ))) THEN
279 RAISE EXCEPTION 'You must first create at least one valid
280 Location for this Item Site before it may be
285 --This could be made a table constraint later, but do not want to create a big problem
286 --for users with problematic legacy data over a relatively trivial problem for now,
287 --so we will just check moving forword.
288 IF (NEW.itemsite_stocked AND NEW.itemsite_reorderlevel<=0) THEN
289 RAISE EXCEPTION 'Stocked items must have postive reorder level specified.';
293 IF (TG_OP = 'UPDATE') THEN
294 --This could be made a table constraint later, but do not want to create a big problem
295 --for users with problematic legacy data over a relatively trivial problem for now,
296 --so we will just check moving forword.
297 IF ((NEW.itemsite_stocked)
298 AND (NEW.itemsite_stocked != OLD.itemsite_stocked) --Avoid checking unless explicitly changed
299 AND (NEW.itemsite_reorderlevel<=0)) THEN
300 RAISE EXCEPTION 'Stocked items must have postive reorder level specified.';
304 IF (TG_OP = 'UPDATE') THEN
307 IF (NOT OLD.itemsite_loccntrl AND NEW.itemsite_loccntrl) THEN
308 IF (SELECT count(*)=0
310 WHERE ((location_warehous_id=NEW.itemsite_warehous_id)
311 AND ( (NOT location_restrict) OR
312 ( (location_restrict) AND
313 (location_id IN ( SELECT locitem_location_id
315 WHERE (locitem_item_id=NEW.itemsite_item_id) ) ) ) ))) THEN
316 RAISE EXCEPTION 'You must first create at least one valid
317 Location for this Item Site before it may be
322 -- Update detail records based on control method changes
323 _wasLocationControl := OLD.itemsite_loccntrl;
324 _isLocationControl := NEW.itemsite_loccntrl;
325 _wasLotSerial := OLD.itemsite_controlmethod IN ('S','L');
326 _isLotSerial := NEW.itemsite_controlmethod IN ('S','L');
327 _wasPerishable := OLD.itemsite_perishable;
328 _isPerishable := NEW.itemsite_perishable;
331 IF ( (_wasLocationControl) AND (_isLocationControl) ) THEN
333 ELSIF ( (NOT _wasLocationControl) AND (NOT _isLocationControl) ) THEN
335 ELSIF ( (NOT _wasLocationControl) AND (_isLocationControl) ) THEN
337 ELSIF ( (_wasLocationControl) AND (NOT _isLocationControl) ) THEN
341 IF ( (_wasLotSerial) AND (_isLotSerial) ) THEN
342 _state := _state + 1;
343 ELSIF ( (NOT _wasLotSerial) AND (NOT _isLotSerial) ) THEN
344 _state := _state + 2;
345 ELSIF ( (NOT _wasLotSerial) AND (_isLotSerial) ) THEN
346 _state := _state + 3;
347 ELSIF ( (_wasLotSerial) AND (NOT _isLotSerial) ) THEN
348 _state := _state + 4;
351 IF ( (_application = 'Standard') AND (_state IN (41, 43, 14, 34, 24, 42, 44)) ) THEN
352 -- Check for Reservations
353 IF (SELECT COUNT(*) > 0
354 FROM itemloc JOIN reserve ON (reserve_supply_id=itemloc_id AND reserve_supply_type='I')
355 WHERE (itemloc_itemsite_id=OLD.itemsite_id)) THEN
356 RAISE EXCEPTION 'Sales Order Reservations by Location exist for this Item Site';
360 IF (_state IN (41, 43)) THEN
361 PERFORM consolidateLotSerial(OLD.itemsite_id);
362 ELSIF (_state IN (14, 34)) THEN
363 PERFORM consolidateLocations(OLD.itemsite_id);
364 ELSIF (_state IN (24, 42, 44)) THEN
366 RAISE NOTICE 'Deleting item site detail records,';
369 WHERE (itemloc_itemsite_id=OLD.itemsite_id);
372 IF (NEW.itemsite_qtyonhand != 0) THEN
373 -- Handle detail creation
374 -- Create itemloc records if they do not exist
375 IF (_state IN (23, 32, 33)) THEN
377 ( itemloc_itemsite_id, itemloc_location_id,
378 itemloc_expiration, itemloc_qty )
380 ( NEW.itemsite_id, -1,
381 endOfTime(), NEW.itemsite_qtyonhand );
384 -- Handle Location distribution
385 IF (_state IN (31, 32, 33, 34)) THEN
386 IF (SELECT (COUNT(*)=1)
388 WHERE ((location_id=NEW.itemsite_location_id)
389 AND (location_warehous_id=NEW.itemsite_warehous_id)
390 AND ( (NOT location_restrict) OR
391 ( (location_restrict) AND
392 (location_id IN ( SELECT locitem_location_id
394 WHERE (locitem_item_id=NEW.itemsite_item_id) ) ) ) ))) THEN
395 PERFORM initialDistribution(NEW.itemsite_id, NEW.itemsite_location_id);
397 RAISE EXCEPTION 'A valid default location must be selected to distribute existing inventory to.';
401 -- Handle Lot/Serial distribution
402 IF ( (_state = 13) OR (_state = 23) OR (_state = 33) OR (_state = 43) ) THEN
403 RAISE NOTICE 'You should now use the Reassign Lot/Serial # window to assign Lot/Serial #s.';
406 IF (OLD.itemsite_costmethod='A' AND NEW.itemsite_costmethod='S') THEN
407 -- TODO: Average costing cost method change
408 SELECT stdcost(NEW.itemsite_item_id) * NEW.itemsite_qtyonhand
410 _variance := _cost - NEW.itemsite_value;
411 NEW.itemsite_value := _cost;
412 IF(_variance <> 0.0) THEN
413 PERFORM insertGLTransaction( 'P/D', '', '', 'Itemsite converted from Average to Standard cost.',
414 costcat_invcost_accnt_id, costcat_asset_accnt_id, NEW.itemsite_id,
415 _variance, CURRENT_DATE )
417 WHERE(costcat_id=NEW.itemsite_costcat_id);
418 UPDATE itemsite SET itemsite_value = _cost WHERE (itemsite_id = NEW.itemsite_id);
424 IF ( (_application = 'Standard') AND (_wasPerishable) AND (NOT _isPerishable) ) THEN
425 UPDATE itemloc SET itemloc_expiration = endOfTime()
426 WHERE (itemloc_itemsite_id = OLD.itemsite_id);
427 PERFORM consolidateLotSerial(OLD.itemsite_id);
430 -- If Planning Type changed to None then delete all Planned Orders
431 IF ( (_application = 'Standard') AND (TG_OP = 'UPDATE') ) THEN
432 IF (NEW.itemsite_planning_type = 'N' AND OLD.itemsite_planning_type <> 'N') THEN
433 PERFORM deletePlannedOrder(planord_id, TRUE)
435 WHERE (planord_itemsite_id=NEW.itemsite_id);
439 END IF; -- End Maintenance
444 $$ LANGUAGE 'plpgsql';
446 SELECT dropIfExists('trigger', 'itemsiteAfterTrigger');
447 CREATE TRIGGER itemsiteAfterTrigger AFTER INSERT OR UPDATE ON itemsite FOR EACH ROW EXECUTE PROCEDURE _itemsiteAfterTrigger();