1 CREATE OR REPLACE FUNCTION _cntslipTrigger() 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 IF (TG_OP = ''DELETE'') THEN
11 SELECT itemsite_loccntrl, itemsite_controlmethod,
12 cntslip_posted, cntslip_lotserial, cntslip_comments,
13 cntslip_number, cntslip_qty INTO _p
14 FROM cntslip, invcnt, itemsite
15 WHERE ( (cntslip_cnttag_id=invcnt_id)
16 AND (invcnt_itemsite_id=itemsite_id)
17 AND (cntslip_id=OLD.cntslip_id) );
19 IF(_p.cntslip_posted) THEN
21 Count Slip #'' || _p.cntslip_number ||
22 '' deleted '' || formatQty(_p.cntslip_qty) ) INTO _comments;
24 -- Add the Location name if the itemsite is MLC
25 IF (_p.itemsite_loccntrl) THEN
26 SELECT ( '', Location:'' || location_name ) INTO _temp
27 FROM location, cntslip
28 WHERE ( (cntslip_location_id=location_id)
29 AND (cntslip_id=OLD.cntslip_id) );
31 _comments := (_comments || _temp);
34 -- Add the Lot/Serial if the itemsite is Lot or Serial controlled
35 IF (_p.itemsite_controlmethod = ''L'') THEN
36 _comments := (_comments || ( '', Lot #:'' || _p.cntslip_lotserial));
37 ELSIF (_p.itemsite_controlmethod = ''S'') THEN
38 _comments := (_comments || ( '', Serial #:'' || _p.cntslip_lotserial));
41 _comments := (_comments || '' '' || _p.cntslip_comments);
44 SET invcnt_qoh_after = ( COALESCE(invcnt_qoh_after, 0) - cntslip_qty),
45 invcnt_comments = (invcnt_comments || _comments)
47 WHERE ( (cntslip_cnttag_id=invcnt_id)
48 AND (NOT invcnt_posted)
49 AND (cntslip_id=OLD.cntslip_id) );
60 DROP TRIGGER IF EXISTS cntslipTrigger ON cntslip;
61 CREATE TRIGGER cntslipTrigger BEFORE INSERT OR UPDATE OR DELETE ON cntslip FOR EACH ROW EXECUTE PROCEDURE _cntslipTrigger();