1 CREATE OR REPLACE FUNCTION _itemCostTrigger() 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.
7 IF ( (TG_OP = 'INSERT') AND (NOT checkPrivilege('CreateCosts')) AND (NOT checkPrivilege('PostVouchers')) ) THEN
8 RAISE EXCEPTION 'You do not have privileges to enter Item Costs.';
11 IF ( (TG_OP = 'UPDATE') AND (NOT checkPrivilege('EnterActualCosts')) AND (NOT checkPrivilege('PostVouchers')) AND (NOT checkPrivilege('UpdateActualCosts')) AND (NOT checkPrivilege('PostActualCosts')) AND (NOT checkPrivilege('PostStandardCosts')) ) THEN
12 RAISE EXCEPTION 'You do not have privileges to update Item Costs.';
15 IF ( (TG_OP = 'DELETE') AND (NOT checkPrivilege('DeleteCosts')) ) THEN
16 RAISE EXCEPTION 'You do not have privileges to delete Item Costs.';
19 IF (TG_OP = 'UPDATE') THEN
20 IF (NEW.itemcost_actcost <> OLD.itemcost_actcost OR
21 NEW.itemcost_curr_id <> OLD.itemcost_curr_id) THEN
23 ( costhist_item_id, costhist_costelem_id, costhist_type,
24 costhist_lowlevel, costhist_username, costhist_date,
25 costhist_oldcost, costhist_newcost,
26 costhist_oldcurr_id, costhist_newcurr_id )
28 ( NEW.itemcost_item_id, NEW.itemcost_costelem_id, 'A',
29 NEW.itemcost_lowlevel, getEffectiveXtUser(), CURRENT_TIMESTAMP,
30 OLD.itemcost_actcost, NEW.itemcost_actcost,
31 OLD.itemcost_curr_id, NEW.itemcost_curr_id );
34 IF (NEW.itemcost_stdcost <> OLD.itemcost_stdcost) THEN
36 ( costhist_item_id, costhist_costelem_id, costhist_type,
37 costhist_lowlevel, costhist_username, costhist_date,
38 costhist_oldcost, costhist_newcost,
39 costhist_oldcurr_id, costhist_newcurr_id )
41 ( NEW.itemcost_item_id, NEW.itemcost_costelem_id, 'S',
42 NEW.itemcost_lowlevel, getEffectiveXtUser(), CURRENT_TIMESTAMP,
43 OLD.itemcost_stdcost, NEW.itemcost_stdcost,
44 baseCurrId(), baseCurrId() );
49 ELSIF (TG_OP = 'INSERT') THEN
51 ( costhist_item_id, costhist_costelem_id, costhist_type,
52 costhist_lowlevel, costhist_username, costhist_date,
53 costhist_oldcost, costhist_newcost,
54 costhist_oldcurr_id, costhist_newcurr_id )
56 ( NEW.itemcost_item_id, NEW.itemcost_costelem_id, 'N',
57 NEW.itemcost_lowlevel, getEffectiveXtUser(), CURRENT_TIMESTAMP,
58 0, NEW.itemcost_actcost,
59 baseCurrId(), NEW.itemcost_curr_id );
63 ELSIF (TG_OP = 'DELETE') THEN
65 ( costhist_item_id, costhist_costelem_id, costhist_type,
66 costhist_lowlevel, costhist_username, costhist_date,
67 costhist_oldcost, costhist_newcost,
68 costhist_oldcurr_id, costhist_newcurr_id )
70 ( OLD.itemcost_item_id, OLD.itemcost_costelem_id, 'D',
71 OLD.itemcost_lowlevel, getEffectiveXtUser(), CURRENT_TIMESTAMP,
72 OLD.itemcost_stdcost, 0,
73 OLD.itemcost_curr_id, baseCurrId() );
79 $$ LANGUAGE 'plpgsql';
81 DROP TRIGGER itemCostTrigger ON itemcost;
82 CREATE TRIGGER itemCostTrigger BEFORE INSERT OR UPDATE OR DELETE ON itemcost FOR EACH ROW EXECUTE PROCEDURE _itemCostTrigger();
86 CREATE OR REPLACE FUNCTION _itemCostAfterTrigger() RETURNS TRIGGER AS $$
87 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
88 -- See www.xtuple.com/CPAL for the full text of the software license.
95 _standardCost NUMERIC;
99 -- Create Event if Standard or Actual Cost is greater than Max Cost
101 IF NOT EXISTS(SELECT 1
103 JOIN evnttype ON (evnttype_id = evntnot_evnttype_id)
104 JOIN usrpref ON (evntnot_username = usrpref_username)
106 evnttype_name = 'CostExceedsMaxDesired'
107 AND usrpref_name = 'active'
108 AND usrpref_value = 't')
113 SELECT item_number, item_maxcost, actcost(item_id), stdcost(item_id) INTO _itemNumber, _maxCost, _actualCost, _standardCost
115 WHERE (item_id=NEW.itemcost_item_id);
117 IF (_maxCost > 0.0) THEN
118 -- IF (_standardCost > _maxCost)
119 IF NOT EXISTS(SELECT 1 --COUNT(evntlog_id)
122 WHERE evntlog_evnttype_id = evnttype_id
123 AND evntlog_number LIKE
124 (_itemNumber || ' -Standard- New:' || '%')
126 AND (evntlog_dispatched IS NULL)
127 AND CAST(evntlog_evnttime AS DATE) = current_date
130 AND (_standardCost > _maxCost) THEN
133 IF (TG_OP = 'INSERT') THEN
137 _oldStdCost := OLD.itemcost_stdcost;
138 _oldActCost := OLD.itemcost_stdcost;
140 PERFORM postEvent('CostExceedsMaxDesired', NULL, NEW.itemcost_item_id,
141 itemsite_warehous_id,
142 (_itemNumber || ' -Standard- ' ||
143 'New: ' || formatCost(_standardCost) ||
144 ' Max: '|| formatCost(_MaxCost)),
145 NEW.itemcost_stdcost, _oldStdCost,
148 WHERE (itemsite_item_id=NEW.itemcost_item_id);
153 WHERE evntlog_evnttype_id = evnttype_id
154 AND evntlog_number LIKE
155 (_itemNumber || ' -Actual- New:' || '%')
157 AND (evntlog_dispatched IS NULL)
158 AND CAST(evntlog_evnttime AS DATE) = current_date
161 AND (_actualCost > _maxCost)
164 PERFORM postEvent('CostExceedsMaxDesired', NULL, NEW.itemcost_item_id,
165 itemsite_warehous_id,
166 (_itemNumber || ' -Actual- ' ||
167 'New: ' || formatCost(_actualCost) ||
168 ' Max: '|| formatCost(_MaxCost)),
169 NEW.itemcost_actcost, _oldActCost,
172 WHERE (itemsite_item_id=NEW.itemcost_item_id);
179 $$ LANGUAGE 'plpgsql';
181 DROP TRIGGER itemCostAfterTrigger ON itemcost;
182 CREATE TRIGGER itemCostAfterTrigger AFTER INSERT OR UPDATE ON itemcost FOR EACH ROW EXECUTE PROCEDURE _itemCostAfterTrigger();