1 CREATE OR REPLACE FUNCTION updateStdCost(INTEGER, NUMERIC, NUMERIC, TEXT, TEXT) RETURNS BOOLEAN 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 pItemcostid ALIAS FOR $1;
8 pDocNumber ALIAS FOR $4;
16 IF (pNewcost IS NULL) THEN
21 IF (pOldcost IS NULL) THEN
27 IF (_newcost > 0) THEN
29 SET itemcost_stdcost=_newcost,
30 itemcost_posted=CURRENT_DATE
31 WHERE (itemcost_id=pItemcostid);
34 -- Distribute to G/L, debit Inventory Asset, credit Inventory Cost Variance
35 FOR _r IN SELECT itemsite_id, (itemsite_qtyonhand + itemsite_nnqoh) AS totalQty,
36 costcat_invcost_accnt_id, costcat_asset_accnt_id,
38 FROM itemcost, itemsite, costcat
39 WHERE ( (itemsite_item_id=itemcost_item_id)
40 AND (itemsite_costcat_id=costcat_id)
41 AND (itemsite_costmethod != 'A')
42 AND ((itemsite_qtyonhand + itemsite_nnqoh) <> 0)
43 AND (itemcost_id=pItemcostid) ) LOOP
44 -- IF (_newcost <> _oldcost) THEN
45 -- RAISE NOTICE 'itemcost_id = %, Qty = %, Old Cost = %, New Cost = %', pItemcostid, _r.totalQty, _oldcost, _newcost;
47 PERFORM insertGLTransaction( 'P/D', '', pDocNumber, pNotes,
48 _r.costcat_invcost_accnt_id, _r.costcat_asset_accnt_id, _r.itemsite_id,
49 ((_newcost - _oldcost) * _r.totalQty),
51 -- Update Itemsite Value if not Average Cost
52 IF (_r.itemsite_costmethod <> 'A') THEN
53 -- RAISE NOTICE 'itemsite_id = %, Qty = %, New Cost = %', _r.itemsite_id, _r.totalQty, _newcost;
54 UPDATE itemsite SET itemsite_value=(_r.totalQty * stdCost(itemsite_item_id))
55 WHERE (itemsite_id=_r.itemsite_id);
59 IF (_newcost = 0) THEN
61 WHERE (itemcost_id=pItemcostid);
66 IF ( SELECT metric_value
68 WHERE ((metric_name = 'EnableAsOfQOH')
69 AND (metric_value = 't'))) THEN
70 IF (pNewcost IS NOT NULL) THEN
73 IF (pOldcost IS NULL) THEN
78 -- Distribute to G/L, debit Inventory Asset, credit Inventory Cost Variance
79 PERFORM postValueIntoInvBalance(
82 asofinvqty(itemsite_id,current_date),
83 asofinvnn(itemsite_id,current_date),
87 JOIN item ON (itemsite_item_id=item_id)
88 JOIN itemcost ON (itemcost_item_id=item_id)
89 WHERE((itemsite_costmethod = 'S')
90 AND (itemcost_id=pItemcostid));
96 $$ LANGUAGE 'plpgsql';
99 CREATE OR REPLACE FUNCTION updateStdCost(INTEGER, TEXT, BOOLEAN, NUMERIC) RETURNS INTEGER AS $$
100 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
101 -- See www.xtuple.com/CPAL for the full text of the software license.
103 pItemid ALIAS FOR $1;
104 pCostType ALIAS FOR $2;
108 _oldCost NUMERIC := 0;
114 IF (pCost IS NULL) THEN
120 SELECT itemcost_id, itemcost_stdCost, item_number
121 INTO _itemcostid, _oldCost, _itemNumber
122 FROM itemcost, costelem, item
123 WHERE ((itemcost_costelem_id=costelem_id)
124 AND (itemcost_item_id=item_id)
125 AND (item_id=pItemid)
126 AND (itemcost_lowlevel=pLevel)
127 AND (costelem_type=pCosttype));
128 -- RAISE NOTICE 'updateStdCost(%, %, %, %) has itemcost_id % and stdcost %',
129 -- pItemid, pCostType, plevel, _newCost, _itemcostid, _oldCost;
131 IF (NOT FOUND) AND (_newCost > 0) THEN
132 SELECT NEXTVAL('itemcost_itemcost_id_seq') INTO _itemcostid;
133 RAISE NOTICE 'updateStdCost() inserting itemcost_id %', _itemcostid;
135 (itemcost_id, itemcost_item_id, itemcost_costelem_id,
136 itemcost_lowlevel, itemcost_stdcost, itemcost_posted,
137 itemcost_actcost, itemcost_updated)
139 _itemcostid, pItemid, costelem_id,
140 pLevel, _newCost, CURRENT_DATE,
143 WHERE (costelem_type=pCosttype);
146 IF (_itemcostid IS NOT NULL) THEN
147 SELECT updateStdCost(_itemcostid, _newCost, _oldCost, 'Post Cost',
148 ('Set Standard Cost - ' || pCosttype || ' for item ' || _itemNumber)) INTO _updateRet;
156 $$ LANGUAGE 'plpgsql';