2 -- code to handle updating kit parts better...
5 CREATE OR REPLACE FUNCTION explodekitmustdelete(integer, integer, integer, integer)
9 pSoheadid ALIAS FOR $1;
10 pLinenumber ALIAS FOR $2;
11 pSubnumber ALIAS FOR $3;
12 pItemsiteid ALIAS FOR $4;
21 SELECT getActiveRevId('BOM',itemsite_item_id), itemsite_warehous_id, itemsite_item_id
22 INTO _revid, _warehousid, _itemid
24 WHERE(itemsite_id=pItemsiteid);
26 RAISE EXCEPTION 'No Item Site for the specified line was found.';
29 SELECT count(itemsite_id) INTO _ret
31 FROM bomitem JOIN item ON (item_id=bomitem_item_id)
32 LEFT OUTER JOIN itemsite ON ((itemsite_item_id=item_id) AND (itemsite_warehous_id=_warehousid))
33 WHERE((bomitem_parent_item_id=_itemid)
34 AND (bomitem_rev_id=_revid)
35 AND (CURRENT_DATE BETWEEN bomitem_effective AND (bomitem_expires - 1)))
36 AND itemsite_createsopr = true
37 AND itemsite_createsopo = true
38 AND itemsite_createwo = true;
44 SELECT count(coitem_id) INTO _ret
48 coitem_cohead_id = pSoheadid
50 coitem_linenumber = pLinenumber
54 coitem_order_type IS NOT NULL
61 LANGUAGE plpgsql VOLATILE
63 ALTER FUNCTION explodekitmustdelete(integer, integer, integer, integer)
67 -- Function: explodekit(integer, integer, integer, integer, numeric, date, date, text)
69 -- DROP FUNCTION explodekit(integer, integer, integer, integer, numeric, date, date, text);
71 CREATE OR REPLACE FUNCTION explodekit(integer, integer, integer, integer, numeric, date, date, text)
75 pSoheadid ALIAS FOR $1;
76 pLinenumber ALIAS FOR $2;
77 pSubnumber ALIAS FOR $3;
78 pItemsiteid ALIAS FOR $4;
80 pScheddate ALIAS FOR $6;
81 pPromdate ALIAS FOR $7;
83 _subnumber INTEGER := COALESCE(pSubnumber,0);
91 _orderid INTEGER := 0;
96 SELECT getActiveRevId('BOM',itemsite_item_id), itemsite_warehous_id, itemsite_item_id
97 INTO _revid, _warehousid, _itemid
99 WHERE(itemsite_id=pItemsiteid);
101 RAISE EXCEPTION 'No Item Site for the specified line was found.';
104 -- if any of the items have itemsite_createsopr or itemsite_createsopo
105 -- then we have to trash all the items before starting..
113 itemsite_warehous_id,
114 COALESCE((itemsite_active AND item_active), false) AS active,
115 COALESCE((itemsite_sold AND item_sold), false) AS sold,
119 itemsite_createsopr,itemsite_createwo,itemsite_createsopo, itemsite_dropship,
121 itemuomtouomratio(item_id, bomitem_uom_id, item_inv_uom_id) AS invuomratio,
122 roundQty(itemuomfractionalbyuom(bomitem_item_id, bomitem_uom_id),(bomitem_qtyfxd + bomitem_qtyper * pQty) * (1 + bomitem_scrap)) AS qty
123 FROM bomitem JOIN item ON (item_id=bomitem_item_id)
124 LEFT OUTER JOIN itemsite ON ((itemsite_item_id=item_id) AND (itemsite_warehous_id=_warehousid))
125 WHERE((bomitem_parent_item_id=_itemid)
126 AND (bomitem_rev_id=_revid)
127 AND (CURRENT_DATE BETWEEN bomitem_effective AND (bomitem_expires - 1)))
128 ORDER BY bomitem_seqnumber LOOP
130 IF (NOT _item.active) THEN
131 RAISE EXCEPTION 'One or more of the components for the kit is inactive for the selected item site.';
132 ELSIF (NOT _item.sold) THEN
133 RAISE EXCEPTION 'One or more of the components for the kit is not sold for the selected item site.';
134 ELSIF (_item.item_type='F') THEN
135 -- not sure what this does?? F=???
138 SELECT explodeKit(pSoheadid, pLinenumber, _subnumber, _item.itemsite_id, _item.qty)
141 IF (_item.itemsite_createsopr) THEN
143 ELSIF (_item.itemsite_createsopo) THEN
145 ELSIF (_item.itemsite_createwo) THEN
150 _subnumber := _subnumber + 1;
152 -- IF THE LINE EXISTS.. then update. it..
154 SELECT coitem_id FROM coitem INTO _coitemid WHERE
155 coitem_cohead_id = pSoheadid
157 coitem_linenumber = pLinenumber
159 coitem_subnumber = _subnumber
165 _coitemid = nextval('coitem_coitem_id_seq');
166 raise notice 'coitem id: %',_coitemid;
168 (coitem_id, coitem_cohead_id,
169 coitem_linenumber, coitem_subnumber,
170 coitem_itemsite_id, coitem_status,
171 coitem_scheddate, coitem_promdate,
172 coitem_qtyord, coitem_qty_uom_id, coitem_qty_invuomratio,
173 coitem_qtyshipped, coitem_qtyreturned,
174 coitem_unitcost, coitem_custprice,
175 coitem_price, coitem_price_uom_id, coitem_price_invuomratio,
176 coitem_order_type, coitem_order_id,
177 coitem_custpn, coitem_memo,
179 VALUES (_coitemid, pSoheadid,
180 pLinenumber, _subnumber,
181 _item.itemsite_id, 'O',
182 pScheddate, pPromdate,
183 _item.qty, _item.bomitem_uom_id, _item.invuomratio,
185 stdCost(_item.item_id), 0,
186 0, _item.item_price_uom_id, 1,
191 IF (_type IS NOT NULL) THEN
193 RAISE EXCEPTION 'can not update coitems - use explodekitcanupdate to check first';
198 coitem_itemsite_id = _item.itemsite_id ,
200 coitem_scheddate = pScheddate ,
201 coitem_promdate = pPromdate ,
202 coitem_qtyord = _item.qty ,
203 coitem_qty_uom_id = _item.bomitem_uom_id ,
204 coitem_qty_invuomratio = _item.invuomratio ,
205 coitem_qtyshipped = 0 ,
206 coitem_qtyreturned = 0 ,
207 coitem_unitcost = stdCost(_item.item_id) ,
208 coitem_custprice = 0 ,
210 coitem_price_uom_id = _item.item_price_uom_id ,
211 coitem_price_invuomratio = 1 ,
212 coitem_order_type = _type ,
213 coitem_order_id = -1 ,
215 coitem_memo = pMemo ,
218 WHERE coitem_id = _coitemid;
223 IF (_item.itemsite_createsopr) THEN
224 SELECT createPR(cohead_number::INTEGER, 'S', _coitemid) INTO _orderid
226 WHERE (cohead_id=pSoheadid);
227 IF (_orderid > 0) THEN
228 UPDATE coitem SET coitem_order_id=_orderid
229 WHERE (coitem_id=_coitemid);
231 RAISE EXCEPTION 'Could not explode kit. CreatePR failed, result=%', _orderid;
235 IF (_item.itemsite_createsopo) THEN
236 SELECT itemsrc_id INTO _itemsrcid
238 WHERE ((itemsrc_item_id=_item.item_id)
239 AND (itemsrc_default));
241 GET DIAGNOSTICS _count = ROW_COUNT;
243 PERFORM createPurchaseToSale(_coitemid, _itemsrcid, _item.itemsite_dropship);
245 RAISE EXCEPTION 'Could not explode kit. One or more items are flagged as purchase-to-order for this site, but no default item source is defined.';
255 LANGUAGE plpgsql VOLATILE
257 ALTER FUNCTION explodekit(integer, integer, integer, integer, numeric, date, date, text)