1 CREATE OR REPLACE FUNCTION _soitemTrigger() 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.
5 _changelog BOOLEAN := FALSE;
14 SELECT checkPrivilege('MaintainSalesOrders') OR checkPrivilege('ShipOrders') OR checkPrivilege('IssueStockToShipping') INTO _check;
16 RAISE EXCEPTION 'You do not have privileges to alter a Sales Order.';
19 IF ( SELECT fetchMetricBool('SalesOrderChangeLog') ) THEN
23 IF (TG_OP IN ('INSERT','UPDATE')) THEN
24 IF (NEW.coitem_scheddate IS NULL) THEN
25 IF (fetchmetricbool('AllowASAPShipSchedules')) THEN
26 NEW.coitem_scheddate := current_date;
28 RAISE EXCEPTION 'A schedule date is required.';
35 SELECT COALESCE(item_type,'')='K'
38 WHERE((itemsite_item_id=item_id)
39 AND (itemsite_id=_rec.coitem_itemsite_id));
40 _kit := COALESCE(_kit, false);
42 IF(_kit AND _rec.coitem_status <> 'C' AND _rec.coitem_status <> 'X') THEN
45 FROM coitem JOIN shipitem ON (shipitem_orderitem_id=coitem_id)
46 JOIN shiphead ON (shiphead_id=shipitem_shiphead_id AND shiphead_order_type='SO')
47 WHERE((coitem_cohead_id=_rec.coitem_cohead_id)
48 AND (coitem_linenumber=_rec.coitem_linenumber)
49 AND (coitem_subnumber > 0))
51 HAVING (SUM(shipitem_qty) > 0)
58 IF (TG_OP ='UPDATE') THEN
59 IF ((OLD.coitem_status <> 'C') AND (NEW.coitem_status = 'C')) THEN
60 SELECT qtyAtShipping(NEW.coitem_id) INTO _atShipping;
61 IF (_atShipping > 0) THEN
62 RAISE EXCEPTION 'Line % cannot be Closed at this time as there is inventory at shipping.',NEW.coitem_linenumber;
67 IF (TG_OP = 'INSERT') THEN
68 PERFORM postEvent('SoitemCreated', 'S', NEW.coitem_id,
69 itemsite_warehous_id, (cohead_number || '-' || NEW.coitem_linenumber),
70 NULL, NULL, NULL, NULL)
72 WHERE ( (cohead_id=NEW.coitem_cohead_id)
73 AND (itemsite_id=NEW.coitem_itemsite_id)
74 AND (NEW.coitem_scheddate <= (CURRENT_DATE + itemsite_eventfence)) );
77 PERFORM postComment('ChangeLog', 'SI', NEW.coitem_id, 'Created');
80 --Set defaults if no values passed
81 NEW.coitem_linenumber := COALESCE(NEW.coitem_linenumber,
82 (SELECT (COALESCE(MAX(coitem_linenumber), 0) + 1)
84 WHERE (coitem_cohead_id=NEW.coitem_cohead_id)));
85 NEW.coitem_status := COALESCE(NEW.coitem_status,'O');
86 NEW.coitem_scheddate := COALESCE(NEW.coitem_scheddate,
87 (SELECT MIN(coitem_scheddate)
89 WHERE (coitem_cohead_id=NEW.coitem_cohead_id)));
90 NEW.coitem_memo := COALESCE(NEW.coitem_memo,'');
91 NEW.coitem_prcost := COALESCE(NEW.coitem_prcost,0);
92 NEW.coitem_warranty := COALESCE(NEW.coitem_warranty,false);
94 IF (NEW.coitem_status='O') THEN
95 UPDATE cohead SET cohead_status = 'O'
96 WHERE ((cohead_id=NEW.coitem_cohead_id)
97 AND (cohead_status='C'));
102 ELSIF (TG_OP = 'UPDATE') THEN
103 IF (NEW.coitem_qtyord <> OLD.coitem_qtyord) THEN
106 RAISE EXCEPTION 'You can not change the qty ordered for a Kit item when one or more of its components have shipped inventory.';
109 PERFORM postEvent('SoitemQtyChanged', 'S', NEW.coitem_id,
110 itemsite_warehous_id, (cohead_number || '-' || NEW.coitem_linenumber),
111 NEW.coitem_qtyord, OLD.coitem_qtyord, NULL, NULL)
112 FROM cohead, itemsite
113 WHERE ( (cohead_id=NEW.coitem_cohead_id)
114 AND (itemsite_id=NEW.coitem_itemsite_id)
115 AND ( (NEW.coitem_scheddate <= (CURRENT_DATE + itemsite_eventfence))
116 OR (OLD.coitem_scheddate <= (CURRENT_DATE + itemsite_eventfence)) ) );
119 PERFORM postComment( 'ChangeLog', 'SI', NEW.coitem_id,
120 ( 'Changed Qty. Ordered from ' || formatQty(OLD.coitem_qtyord) ||
121 ' to ' || formatQty(NEW.coitem_qtyord) ) );
126 IF (NEW.coitem_price <> OLD.coitem_price) THEN
128 PERFORM postComment( 'ChangeLog', 'SI', NEW.coitem_id,
129 ( 'Changed Unit Price from ' || formatPrice(OLD.coitem_price) ||
130 ' to ' || formatPrice(NEW.coitem_price) ) );
135 IF (NEW.coitem_scheddate <> OLD.coitem_scheddate) THEN
136 PERFORM postEvent('SoitemSchedDateChanged', 'S', NEW.coitem_id,
137 itemsite_warehous_id, (cohead_number || '-' || NEW.coitem_linenumber),
138 NULL, NULL, NEW.coitem_scheddate, OLD.coitem_scheddate)
139 FROM cohead, itemsite
140 WHERE ( (cohead_id=NEW.coitem_cohead_id)
141 AND (itemsite_id=NEW.coitem_itemsite_id)
142 AND ( (NEW.coitem_scheddate <= (CURRENT_DATE + itemsite_eventfence))
143 OR (OLD.coitem_scheddate <= (CURRENT_DATE + itemsite_eventfence)) ) );
146 PERFORM postComment( 'ChangeLog', 'SI', NEW.coitem_id,
147 ( 'Changed Sched. Date from ' || formatDate(OLD.coitem_scheddate) ||
148 ' to ' || formatDate(NEW.coitem_scheddate)) );
153 IF ((NEW.coitem_status = 'C') AND (OLD.coitem_status <> 'C')) THEN
154 NEW.coitem_closedate = CURRENT_TIMESTAMP;
155 NEW.coitem_close_username = getEffectiveXtUser();
156 NEW.coitem_qtyreserved := 0;
159 PERFORM postComment('ChangeLog', 'SI', NEW.coitem_id, 'Closed');
163 IF ((NEW.coitem_status <> 'C') AND (OLD.coitem_status = 'C')) THEN
164 NEW.coitem_closedate = NULL;
165 NEW.coitem_close_username = NULL;
168 PERFORM postComment('ChangeLog', 'SI', NEW.coitem_id, 'Reopened');
172 IF ((NEW.coitem_status = 'X') AND (OLD.coitem_status <> 'X')) THEN
173 IF ((OLD.coitem_order_type = 'W') AND
174 (SELECT wo_status IN ('O', 'E', 'R')
176 WHERE (wo_id=OLD.coitem_order_id))) THEN
177 -- Close any associated W/O
178 PERFORM closeWo(OLD.coitem_order_id, FALSE, CURRENT_DATE);
179 ELSIF (OLD.coitem_order_type = 'R') THEN
180 -- Delete any associated P/R
181 PERFORM deletePr(OLD.coitem_order_id);
184 NEW.coitem_qtyreserved := 0;
187 PERFORM postComment('ChangeLog', 'SI', NEW.coitem_id, 'Canceled');
188 PERFORM postComment('ChangeLog', 'S', NEW.coitem_cohead_id, 'Line # '|| NEW.coitem_linenumber ||' Canceled');
191 PERFORM postEvent('SoitemCancelled', 'S', OLD.coitem_id,
192 itemsite_warehous_id, (cohead_number || '-' || OLD.coitem_linenumber),
193 NULL, NULL, NULL, NULL)
194 FROM cohead, itemsite
195 WHERE ( (cohead_id=OLD.coitem_cohead_id)
196 AND (itemsite_id=OLD.coitem_itemsite_id)
197 AND (OLD.coitem_scheddate <= (CURRENT_DATE + itemsite_eventfence)) );
201 IF ((NEW.coitem_qtyreserved <> OLD.coitem_qtyreserved) AND (_changelog)) THEN
202 PERFORM postComment('ChangeLog', 'SI', NEW.coitem_id, 'Changed Qty Reserved to '|| NEW.coitem_qtyreserved);
207 NEW.coitem_lastupdated = CURRENT_TIMESTAMP;
209 -- Handle status for header
210 IF (TG_OP = 'UPDATE') THEN
211 IF (OLD.coitem_status <> NEW.coitem_status) THEN
212 IF ( (SELECT (count(*) < 1)
214 WHERE ((coitem_cohead_id=NEW.coitem_cohead_id)
215 AND (coitem_id != NEW.coitem_id)
216 AND (coitem_status='O')) ) AND (NEW.coitem_status<>'O') ) THEN
217 UPDATE cohead SET cohead_status = 'C'
218 WHERE ((cohead_id=NEW.coitem_cohead_id)
219 AND (cohead_status='O'));
221 UPDATE cohead SET cohead_status = 'O'
222 WHERE ((cohead_id=NEW.coitem_cohead_id)
223 AND (cohead_status='C'));
231 $$ LANGUAGE 'plpgsql';
233 SELECT dropIfExists('TRIGGER', 'soitemTrigger');
234 CREATE TRIGGER soitemTrigger BEFORE INSERT OR UPDATE ON coitem FOR EACH ROW EXECUTE PROCEDURE _soitemTrigger();
236 CREATE OR REPLACE FUNCTION _soitemBeforeTrigger() RETURNS TRIGGER AS $$
237 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
238 -- See www.xtuple.com/CPAL for the full text of the software license.
247 --Determine if this is a kit for later processing
248 SELECT COALESCE(item_type,'')='K'
251 WHERE((itemsite_item_id=item_id)
252 AND (itemsite_id=NEW.coitem_itemsite_id));
253 _kit := COALESCE(_kit, false);
255 IF (TG_OP = 'INSERT') THEN
257 -- If this is imported, go ahead and insert default characteristics
258 IF (NEW.coitem_imported) THEN
259 INSERT INTO charass (charass_target_type, charass_target_id, charass_char_id, charass_value, charass_price)
260 SELECT 'SI', NEW.coitem_id, char_id, charass_value,
261 itemcharprice(item_id,char_id,charass_value,cohead_cust_id,cohead_shipto_id,NEW.coitem_qtyord,cohead_curr_id,cohead_orderdate)
263 SELECT DISTINCT char_id, char_name, charass_value, item_id, cohead_cust_id, cohead_shipto_id, cohead_curr_id, cohead_orderdate
264 FROM cohead, charass, char, itemsite, item
265 WHERE((itemsite_id=NEW.coitem_itemsite_id)
266 AND (itemsite_item_id=item_id)
267 AND (charass_target_type='I')
268 AND (charass_target_id=item_id)
269 AND (charass_default)
270 AND (char_id=charass_char_id)
271 AND (cohead_id=NEW.coitem_cohead_id))
272 ORDER BY char_name) AS data;
276 -- Create work order and process if flagged to do so
277 IF ((NEW.coitem_order_type='W') AND (NEW.coitem_order_id=-1)) THEN
278 SELECT createwo(CAST(cohead_number AS INTEGER),
279 NEW.coitem_itemsite_id,
281 validateOrderQty(NEW.coitem_itemsite_id, NEW.coitem_qtyord, TRUE),
283 NEW.coitem_scheddate,
287 cohead_prj_id) INTO NEW.coitem_order_id
288 FROM cohead, itemsite
289 WHERE ((cohead_id=NEW.coitem_cohead_id)
290 AND (itemsite_id=NEW.coitem_itemsite_id));
293 (charass_target_type, charass_target_id,
294 charass_char_id, charass_value)
295 SELECT 'W', NEW.coitem_order_id, charass_char_id, charass_value
297 WHERE ((charass_target_type='SI')
298 AND (charass_target_id=NEW.coitem_id));
301 IF (TG_OP = 'UPDATE') THEN
302 -- Update P/R date if applicable
304 IF (NEW.coitem_scheddate <> OLD.coitem_scheddate AND NEW.coitem_order_type='R' AND NEW.coitem_order_id > 1) THEN
305 UPDATE pr SET pr_duedate = NEW.coitem_scheddate WHERE (pr_order_id=NEW.coitem_id AND pr_order_type='S');
308 -- If closing or cancelling and there is a job item work order, then close job and distribute remaining costs
309 IF ((NEW.coitem_status = 'C' AND OLD.coitem_status <> 'C')
310 OR (NEW.coitem_status = 'X' AND OLD.coitem_status <> 'X'))
311 AND (OLD.coitem_order_id > -1) THEN
313 SELECT wo_id, wo_wipvalue INTO _r
314 FROM wo,itemsite,item
315 WHERE ((wo_ordtype='S')
316 AND (wo_ordid=OLD.coitem_id)
317 AND (itemsite_id=wo_itemsite_id)
318 AND (item_id=itemsite_item_id)
319 AND (itemsite_costmethod = 'J'));
322 IF (_r.wo_wipvalue > 0) THEN
323 -- Distribute to G/L, debit Cost of Sales, credit WIP
324 PERFORM MIN(insertGLTransaction( 'W/O', 'WO', formatWoNumber(NEW.coitem_order_id), 'Job Closed Incomplete',
325 costcat_wip_accnt_id,
326 CASE WHEN (COALESCE(NEW.coitem_cos_accnt_id, -1) != -1)
327 THEN NEW.coitem_cos_accnt_id
328 WHEN (NEW.coitem_warranty=TRUE)
329 THEN resolveCOWAccount(itemsite_id, cohead_cust_id, cohead_saletype_id, cohead_shipzone_id)
330 ELSE resolveCOSAccount(itemsite_id, cohead_cust_id, cohead_saletype_id, cohead_shipzone_id)
332 -1, _r.wo_wipvalue, current_date ))
333 FROM itemsite, costcat, cohead
334 WHERE ((itemsite_id=NEW.coitem_itemsite_id)
335 AND (itemsite_costcat_id=costcat_id)
336 AND (cohead_id=NEW.coitem_cohead_id));
342 WHERE (wo_id = _r.wo_id);
347 -- Likewise, reopen the job if line reopened
348 IF ((NEW.coitem_status != 'C' AND OLD.coitem_status = 'C')
349 OR (NEW.coitem_status != 'X' AND OLD.coitem_status = 'X'))
350 AND (OLD.coitem_order_id > -1) THEN
354 WHERE ((wo_ordtype = 'S')
355 AND (wo_ordid=NEW.coitem_id)
356 AND (wo_itemsite_id=itemsite_id)
357 AND (itemsite_item_id=item_id)
358 AND (itemsite_costmethod='J'));
361 -- Handle links to Return Authorization
362 IF (fetchMetricBool('EnableReturnAuth')) THEN
365 WHERE ((raitem_new_coitem_id=NEW.coitem_id)
366 AND (rahead_id=raitem_rahead_id));
368 IF ((_r.raitem_qtyauthorized <> NEW.coitem_qtyord OR
369 _r.raitem_qty_uom_id <> NEW.coitem_qty_uom_id OR
370 _r.raitem_qty_invuomratio <> NEW.coitem_qty_invuomratio OR
371 _r.raitem_price_uom_id <> NEW.coitem_price_uom_id OR
372 _r.raitem_price_invuomratio <> NEW.coitem_price_invuomratio)
373 AND NOT (NEW.coitem_status = 'X' AND _r.raitem_qtyauthorized = 0)) THEN
374 RAISE EXCEPTION 'Quantities for line item % may only be changed on the Return Authorization that created it.',NEW.coitem_linenumber;
376 IF (OLD.coitem_warranty <> NEW.coitem_warranty) THEN
377 UPDATE raitem SET raitem_warranty = NEW.coitem_warranty
378 WHERE((raitem_new_coitem_id=NEW.coitem_id)
379 AND (raitem_warranty != NEW.coitem_warranty));
381 IF (OLD.coitem_cos_accnt_id <> NEW.coitem_cos_accnt_id) THEN
382 UPDATE raitem SET raitem_cos_accnt_id = NEW.coitem_cos_accnt_id
383 WHERE((raitem_new_coitem_id=NEW.coitem_id)
384 AND (COALESCE(raitem_cos_accnt_id,-1) != COALESCE(NEW.coitem_cos_accnt_id,-1)));
386 IF (OLD.coitem_taxtype_id <> NEW.coitem_taxtype_id) THEN
387 UPDATE raitem SET raitem_taxtype_id = NEW.coitem_taxtype_id
388 WHERE((raitem_new_coitem_id=NEW.coitem_id)
389 AND (COALESCE(raitem_taxtype_id,-1) != COALESCE(NEW.coitem_taxtype_id,-1)));
391 IF (OLD.coitem_scheddate <> NEW.coitem_scheddate) THEN
392 UPDATE raitem SET raitem_scheddate = NEW.coitem_scheddate
393 WHERE((raitem_new_coitem_id=NEW.coitem_id)
394 AND (raitem_scheddate != NEW.coitem_scheddate));
396 IF (OLD.coitem_memo <> NEW.coitem_memo) THEN
397 UPDATE raitem SET raitem_notes = NEW.coitem_memo
398 WHERE((raitem_new_coitem_id=NEW.coitem_id)
399 AND (raitem_notes != NEW.coitem_memo));
401 IF ((OLD.coitem_qtyshipped <> NEW.coitem_qtyshipped) AND
402 (NEW.coitem_qtyshipped >= _r.raitem_qtyauthorized) AND
403 ((_r.raitem_disposition = 'S') OR
404 (_r.raitem_status = 'O') AND
405 (_r.raitem_disposition IN ('P','V')) AND
406 (_r.raitem_qtyreceived >= _r.raitem_qtyauthorized))) THEN
407 UPDATE raitem SET raitem_status = 'C'
408 WHERE (raitem_new_coitem_id=NEW.coitem_id);
416 $$ LANGUAGE 'plpgsql';
418 SELECT dropIfExists('TRIGGER', 'soitemBeforeTrigger');
419 CREATE TRIGGER soitemBeforeTrigger BEFORE INSERT OR UPDATE ON coitem FOR EACH ROW EXECUTE PROCEDURE _soitemBeforeTrigger();
420 -- TODO: there are two BEFORE triggers. should these be merged?
423 CREATE OR REPLACE FUNCTION _soitemAfterTrigger() RETURNS TRIGGER AS $$
424 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
425 -- See www.xtuple.com/CPAL for the full text of the software license.
443 --Cache some information
446 WHERE (cohead_id=_rec.coitem_cohead_id);
448 --Determine if this is a kit for later processing
449 SELECT COALESCE(item_type,'')='K', item_fractional
450 INTO _kit, _fractional
452 WHERE((itemsite_item_id=item_id)
453 AND (itemsite_id=_rec.coitem_itemsite_id));
454 _kit := COALESCE(_kit, false);
455 _fractional := COALESCE(_fractional, false);
459 IF (TG_OP = 'INSERT') THEN
460 -- Create Sub Lines for Kit Components
461 PERFORM explodeKit(NEW.coitem_cohead_id, NEW.coitem_linenumber, 0, NEW.coitem_itemsite_id,
462 NEW.coitem_qtyord, NEW.coitem_scheddate, NEW.coitem_promdate, NEW.coitem_memo);
463 IF (fetchMetricBool('KitComponentInheritCOS')) THEN
464 -- Update kit line item COS
466 SET coitem_cos_accnt_id = CASE WHEN (COALESCE(NEW.coitem_cos_accnt_id, -1) != -1)
467 THEN NEW.coitem_cos_accnt_id
468 WHEN (NEW.coitem_warranty)
469 THEN resolveCOWAccount(NEW.coitem_itemsite_id, _r.cohead_cust_id, _r.cohead_saletype_id, _r.cohead_shipzone_id)
470 ELSE resolveCOSAccount(NEW.coitem_itemsite_id, _r.cohead_cust_id, _r.cohead_saletype_id, _r.cohead_shipzone_id)
472 WHERE((coitem_cohead_id=NEW.coitem_cohead_id)
473 AND (coitem_linenumber = NEW.coitem_linenumber)
474 AND (coitem_subnumber > 0));
477 IF (TG_OP = 'UPDATE') THEN
478 IF (NEW.coitem_qtyord <> OLD.coitem_qtyord) THEN
479 -- Recreate Sub Lines for Kit Components
483 WHERE ( (coitem_cohead_id=OLD.coitem_cohead_id)
484 AND (coitem_linenumber=OLD.coitem_linenumber)
485 AND (coitem_subnumber > 0) )
487 SELECT deleteSoItem(_coitemid) INTO _result;
488 IF (_result < 0) THEN
489 RAISE EXCEPTION 'Error deleting kit components: deleteSoItem(integer) Error:%', _result;
493 PERFORM explodeKit(NEW.coitem_cohead_id, NEW.coitem_linenumber, 0, NEW.coitem_itemsite_id,
494 NEW.coitem_qtyord, NEW.coitem_scheddate, NEW.coitem_promdate);
496 IF ( (NEW.coitem_qtyord <> OLD.coitem_qtyord) OR
497 (NEW.coitem_cos_accnt_id <> OLD.coitem_cos_accnt_id) ) THEN
498 IF (fetchMetricBool('KitComponentInheritCOS')) THEN
499 -- Update kit line item COS
501 SET coitem_cos_accnt_id = CASE WHEN (COALESCE(NEW.coitem_cos_accnt_id, -1) != -1)
502 THEN NEW.coitem_cos_accnt_id
503 WHEN (NEW.coitem_warranty)
504 THEN resolveCOWAccount(NEW.coitem_itemsite_id, _r.cohead_cust_id, _r.cohead_saletype_id, _r.cohead_shipzone_id)
505 ELSE resolveCOSAccount(NEW.coitem_itemsite_id, _r.cohead_cust_id, _r.cohead_saletype_id, _r.cohead_shipzone_id)
507 WHERE((coitem_cohead_id=NEW.coitem_cohead_id)
508 AND (coitem_linenumber = NEW.coitem_linenumber)
509 AND (coitem_subnumber > 0));
512 IF (NEW.coitem_scheddate <> OLD.coitem_scheddate) THEN
513 -- Update kit line item Schedule Date
515 SET coitem_scheddate = NEW.coitem_scheddate
516 WHERE((coitem_cohead_id=NEW.coitem_cohead_id)
517 AND (coitem_linenumber = NEW.coitem_linenumber)
518 AND (coitem_subnumber > 0));
523 IF (TG_OP = 'INSERT') THEN
524 -- Create Purchase Request if flagged to do so
525 IF ((NEW.coitem_order_type='R') AND (NEW.coitem_order_id=-1)) THEN
526 SELECT createPR(CAST(cohead_number AS INTEGER), 'S', NEW.coitem_id) INTO _orderid
528 WHERE (cohead_id=NEW.coitem_cohead_id);
529 IF (_orderid > 0) THEN
530 UPDATE coitem SET coitem_order_id=_orderid
531 WHERE (coitem_id=NEW.coitem_id);
533 RAISE EXCEPTION 'CreatePR failed, result=%', _orderid;
537 -- Create Purchase Order if flagged to do so
538 IF ((NEW.coitem_order_type='P') AND (NEW.coitem_order_id=-1)) THEN
539 SELECT itemsrc_id INTO _itemsrcid
540 FROM itemsite JOIN itemsrc ON (itemsrc_item_id=itemsite_item_id AND itemsrc_default)
541 WHERE (itemsite_id=NEW.coitem_itemsite_id)
542 AND (NOT itemsite_stocked);
544 SELECT createPurchaseToSale(NEW.coitem_id,
547 CASE WHEN (NEW.coitem_prcost=0.0) THEN NULL
548 ELSE NEW.coitem_prcost
551 WHERE (itemsite_id=NEW.coitem_itemsite_id);
552 IF (_orderid > 0) THEN
553 UPDATE coitem SET coitem_order_id=_orderid
554 WHERE (coitem_id=NEW.coitem_id);
556 RAISE EXCEPTION 'CreatePurchaseToSale failed, result=%', _orderid;
561 -- Update Purchase Order comments
562 IF (NEW.coitem_order_type='P') THEN
563 UPDATE poitem SET poitem_comments=NEW.coitem_memo
564 WHERE ((poitem_order_id=NEW.coitem_id) AND (poitem_order_type='S'));
568 IF (TG_OP = 'UPDATE') THEN
569 IF (NEW.coitem_order_type = 'P') THEN
570 --If soitem is cancelled
571 IF ((NEW.coitem_status = 'X') AND (OLD.coitem_status <> 'X')) THEN
572 PERFORM postEvent('PoItemSoCancelled', 'P', poitem_id,
573 itemsite_warehous_id,
574 (pohead_number || '-' || poitem_linenumber || ':' || item_number),
575 NULL, NULL, NULL, NULL)
576 FROM poitem JOIN itemsite ON (itemsite_id=poitem_itemsite_id)
577 JOIN item ON (item_id=itemsite_item_id)
578 JOIN pohead ON (pohead_id=poitem_pohead_id)
579 WHERE ( (poitem_id=OLD.coitem_order_id)
580 AND (poitem_duedate <= (CURRENT_DATE + itemsite_eventfence)) );
581 --If soitem notes changed
582 ELSIF (NEW.coitem_memo <> OLD.coitem_memo) THEN
583 UPDATE poitem SET poitem_comments=NEW.coitem_memo
584 WHERE ((poitem_order_id=NEW.coitem_id) AND (poitem_order_type='S'));
589 IF (_rec.coitem_subnumber > 0) THEN
593 WHERE((coitem_cohead_id=_rec.coitem_cohead_id)
594 AND (coitem_linenumber=_rec.coitem_linenumber)
595 AND (coitem_subnumber = 0));
598 WHERE((coitem_cohead_id=_rec.coitem_cohead_id)
599 AND (coitem_linenumber=_rec.coitem_linenumber)
600 AND (coitem_subnumber <> _rec.coitem_subnumber)
601 AND (coitem_subnumber > 0)
602 AND (coitem_status = 'O'))) > 0) THEN
605 _pstat := _rec.coitem_status;
609 IF(TG_OP = 'INSERT') THEN
610 IF (_rec.coitem_subnumber > 0 AND _rec.coitem_status = 'O') THEN
613 ELSIF (TG_OP = 'UPDATE') THEN
614 IF (_rec.coitem_subnumber > 0 AND _rec.coitem_status = 'O') THEN
618 IF ((NEW.coitem_status = 'C') AND (OLD.coitem_status <> 'C')) THEN
621 SET coitem_status='C'
622 WHERE((coitem_cohead_id=OLD.coitem_cohead_id)
623 AND (coitem_linenumber=OLD.coitem_linenumber)
624 AND (coitem_status='O')
625 AND (coitem_subnumber > 0));
629 IF ((NEW.coitem_status = 'X') AND (OLD.coitem_status <> 'X')) THEN
632 SET coitem_status='X'
633 WHERE((coitem_cohead_id=OLD.coitem_cohead_id)
634 AND (coitem_linenumber=OLD.coitem_linenumber)
635 AND (coitem_status='O')
636 AND (coitem_subnumber > 0));
640 IF(NEW.coitem_status = 'O' AND OLD.coitem_status <> 'O') THEN
643 SET coitem_status='O'
644 WHERE((coitem_cohead_id=OLD.coitem_cohead_id)
645 AND (coitem_linenumber=OLD.coitem_linenumber)
646 AND ((coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) > 0)
647 AND (coitem_subnumber > 0));
653 IF ((_kstat IS NOT NULL) AND (_pstat IS NOT NULL) AND (_rec.coitem_subnumber > 0) AND (_kstat <> _pstat)) THEN
655 SET coitem_status = _pstat
656 WHERE((coitem_cohead_id=_rec.coitem_cohead_id)
657 AND (coitem_linenumber=_rec.coitem_linenumber)
658 AND (coitem_subnumber = 0));
661 --If auto calculate freight, recalculate cohead_freight
662 IF (SELECT cohead_calcfreight FROM cohead WHERE (cohead_id=NEW.coitem_cohead_id)) THEN
663 UPDATE cohead SET cohead_freight = COALESCE(
664 (SELECT SUM(freightdata_total) FROM freightDetail('SO',
671 WHERE cohead_id=NEW.coitem_cohead_id;
676 $$ LANGUAGE 'plpgsql';
678 SELECT dropIfExists('TRIGGER', 'soitemAfterTrigger');
679 CREATE TRIGGER soitemAfterTrigger AFTER INSERT OR UPDATE ON coitem FOR EACH ROW EXECUTE PROCEDURE _soitemAfterTrigger();
681 CREATE OR REPLACE FUNCTION _soitemBeforeDeleteTrigger() RETURNS TRIGGER AS $$
682 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
683 -- See www.xtuple.com/CPAL for the full text of the software license.
687 _kit BOOLEAN := FALSE;
688 _shipped BOOLEAN := FALSE;
689 _coitemid INTEGER := 0;
690 _result INTEGER := 0;
695 IF NOT (checkPrivilege('MaintainSalesOrders')) THEN
696 RAISE EXCEPTION 'You do not have privileges to alter a Sales Order.';
699 -- Cache some information
701 FROM cohead, itemsite, item
702 WHERE ( (cohead_id=OLD.coitem_cohead_id)
703 AND (itemsite_id=OLD.coitem_itemsite_id)
704 AND (item_id=itemsite_item_id) );
706 _kit := (COALESCE(_r.item_type,'')='K');
708 -- Check for shipped kit components
709 IF(_kit AND OLD.coitem_status <> 'C' AND OLD.coitem_status <> 'X') THEN
710 IF (EXISTS (SELECT coitem_id
711 FROM coitem JOIN shipitem ON (shipitem_orderitem_id=coitem_id)
712 JOIN shiphead ON (shiphead_id=shipitem_shiphead_id AND shiphead_order_type='SO')
713 WHERE ((coitem_cohead_id=OLD.coitem_cohead_id)
714 AND (coitem_linenumber=OLD.coitem_linenumber)
715 AND (coitem_subnumber > 0))
717 HAVING (SUM(shipitem_qty) > 0)
723 IF(_kit AND _shipped) THEN
724 RAISE EXCEPTION 'You can not delete this Sales Order Line as it has several sub components that have already been shipped.';
728 WHERE ( (comment_source='SI')
729 AND (comment_source_id=OLD.coitem_id) );
732 WHERE ((charass_target_type='SI')
733 AND (charass_target_id=OLD.coitem_id));
735 -- Delete Sub Lines for Kit Components
736 IF (OLD.coitem_subnumber = 0) THEN
740 WHERE ( (coitem_cohead_id=OLD.coitem_cohead_id)
741 AND (coitem_linenumber=OLD.coitem_linenumber)
742 AND (coitem_subnumber > 0) )
744 SELECT deleteSoItem(_coitemid) INTO _result;
745 IF (_result < 0) THEN
746 IF NOT (_r.itemsite_createsopo AND (_result = -10 OR _result = -20)) THEN
747 RAISE EXCEPTION 'Error deleting kit components: deleteSoItem(integer) Error:%', _result;
753 IF (OLD.coitem_scheddate <= (CURRENT_DATE + _r.itemsite_eventfence)) THEN
754 PERFORM postEvent('SoitemCancelled', 'S', OLD.coitem_id,
755 _r.itemsite_warehous_id, (_r.cohead_number || '-' || OLD.coitem_linenumber),
756 NULL, NULL, NULL, NULL);
761 $$ LANGUAGE 'plpgsql';
763 SELECT dropIfExists('TRIGGER', 'soitemBeforeDeleteTrigger');
764 CREATE TRIGGER soitemBeforeDeleteTrigger BEFORE DELETE ON coitem FOR EACH ROW EXECUTE PROCEDURE _soitemBeforeDeleteTrigger();
766 CREATE OR REPLACE FUNCTION _soitemAfterDeleteTrigger() RETURNS TRIGGER AS $$
767 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
768 -- See www.xtuple.com/CPAL for the full text of the software license.
773 IF (OLD.coitem_status = 'O') THEN
774 IF ( (SELECT (count(*) < 1)
776 WHERE ((coitem_cohead_id=OLD.coitem_cohead_id)
777 AND (coitem_id != OLD.coitem_id)
778 AND (coitem_status = 'O')) ) ) THEN
779 UPDATE cohead SET cohead_status = 'C'
780 WHERE ((cohead_id=OLD.coitem_cohead_id)
781 AND (cohead_status='O'));
787 $$ LANGUAGE 'plpgsql';
789 SELECT dropIfExists('TRIGGER', 'soitemAfterDeleteTrigger');
790 CREATE TRIGGER soitemAfterDeleteTrigger AFTER DELETE ON coitem FOR EACH ROW EXECUTE PROCEDURE _soitemAfterDeleteTrigger();