2 CREATE OR REPLACE FUNCTION explodeKit(INTEGER, INTEGER, INTEGER, INTEGER, NUMERIC) RETURNS INTEGER AS $$
3 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
4 -- See www.xtuple.com/CPAL for the full text of the software license.
6 pSoheadid ALIAS FOR $1;
7 pLinenumber ALIAS FOR $2;
8 pSubnumber ALIAS FOR $3;
9 pItemsiteid ALIAS FOR $4;
12 RETURN explodeKit(pSoheadid, pLinenumber, pSubnumber, pItemsiteid, pQty, CURRENT_DATE, NULL);
14 $$ LANGUAGE 'plpgsql';
16 CREATE OR REPLACE FUNCTION explodeKit(INTEGER, INTEGER, INTEGER, INTEGER, NUMERIC, DATE, DATE) RETURNS INTEGER AS $$
17 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
18 -- See www.xtuple.com/CPAL for the full text of the software license.
20 pSoheadid ALIAS FOR $1;
21 pLinenumber ALIAS FOR $2;
22 pSubnumber ALIAS FOR $3;
23 pItemsiteid ALIAS FOR $4;
25 pScheddate ALIAS FOR $6;
26 pPromdate ALIAS FOR $7;
28 RETURN explodeKit(pSoheadid, pLinenumber, pSubnumber, pItemsiteid, pQty, CURRENT_DATE, NULL, '');
30 $$ LANGUAGE 'plpgsql';
32 CREATE OR REPLACE FUNCTION explodeKit(INTEGER, INTEGER, INTEGER, INTEGER, NUMERIC, DATE, DATE, TEXT) RETURNS INTEGER AS $$
33 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
34 -- See www.xtuple.com/CPAL for the full text of the software license.
36 pSoheadid ALIAS FOR $1;
37 pLinenumber ALIAS FOR $2;
38 pSubnumber ALIAS FOR $3;
39 pItemsiteid ALIAS FOR $4;
41 pScheddate ALIAS FOR $6;
42 pPromdate ALIAS FOR $7;
44 _subnumber INTEGER := COALESCE(pSubnumber,0);
52 _orderid INTEGER := 0;
56 SELECT getActiveRevId('BOM',itemsite_item_id), itemsite_warehous_id, itemsite_item_id
57 INTO _revid, _warehousid, _itemid
59 WHERE(itemsite_id=pItemsiteid);
61 RAISE EXCEPTION 'No Item Site for the specified line was found.';
68 COALESCE((itemsite_active AND item_active), false) AS active,
69 COALESCE((itemsite_sold AND item_sold), false) AS sold,
73 itemsite_createsopr,itemsite_createwo,itemsite_createsopo, itemsite_dropship,
75 itemuomtouomratio(item_id, bomitem_uom_id, item_inv_uom_id) AS invuomratio,
76 roundQty(itemuomfractionalbyuom(bomitem_item_id, bomitem_uom_id),(bomitem_qtyfxd + bomitem_qtyper * pQty) * (1 + bomitem_scrap)) AS qty
77 FROM bomitem JOIN item ON (item_id=bomitem_item_id)
78 LEFT OUTER JOIN itemsite ON ((itemsite_item_id=item_id) AND (itemsite_warehous_id=_warehousid))
79 WHERE((bomitem_parent_item_id=_itemid)
80 AND (bomitem_rev_id=_revid)
81 AND (CURRENT_DATE BETWEEN bomitem_effective AND (bomitem_expires - 1)))
82 ORDER BY bomitem_seqnumber LOOP
83 IF (NOT _item.active) THEN
84 RAISE EXCEPTION 'One or more of the components for the kit is inactive for the selected item site.';
85 ELSIF (NOT _item.sold) THEN
86 RAISE EXCEPTION 'One or more of the components for the kit is not sold for the selected item site.';
87 ELSIF (_item.item_type='F') THEN
88 SELECT explodeKit(pSoheadid, pLinenumber, _subnumber, _item.itemsite_id, _item.qty)
91 IF (_item.itemsite_createsopr) THEN
93 ELSIF (_item.itemsite_createsopo) THEN
95 ELSIF (_item.itemsite_createwo) THEN
100 _subnumber := _subnumber + 1;
101 _coitemid = nextval('coitem_coitem_id_seq');
102 raise notice 'coitem id: %',_coitemid;
104 (coitem_id, coitem_cohead_id,
105 coitem_linenumber, coitem_subnumber,
106 coitem_itemsite_id, coitem_status,
107 coitem_scheddate, coitem_promdate,
108 coitem_qtyord, coitem_qty_uom_id, coitem_qty_invuomratio,
109 coitem_qtyshipped, coitem_qtyreturned,
110 coitem_unitcost, coitem_custprice,
111 coitem_price, coitem_price_uom_id, coitem_price_invuomratio,
112 coitem_order_type, coitem_order_id,
113 coitem_custpn, coitem_memo,
115 VALUES (_coitemid, pSoheadid,
116 pLinenumber, _subnumber,
117 _item.itemsite_id, 'O',
118 pScheddate, pPromdate,
119 _item.qty, _item.bomitem_uom_id, _item.invuomratio,
121 stdCost(_item.item_id), 0,
122 0, _item.item_price_uom_id, 1,
127 IF (_item.itemsite_createsopr) THEN
128 SELECT createPR(cohead_number::INTEGER, 'S', _coitemid) INTO _orderid
130 WHERE (cohead_id=pSoheadid);
131 IF (_orderid > 0) THEN
132 UPDATE coitem SET coitem_order_id=_orderid
133 WHERE (coitem_id=_coitemid);
135 RAISE EXCEPTION 'Could not explode kit. CreatePR failed, result=%', _orderid;
139 IF (_item.itemsite_createsopo) THEN
140 SELECT itemsrc_id INTO _itemsrcid
142 WHERE ((itemsrc_item_id=_item.item_id)
143 AND (itemsrc_default));
145 GET DIAGNOSTICS _count = ROW_COUNT;
147 PERFORM createPurchaseToSale(_coitemid, _itemsrcid, _item.itemsite_dropship);
149 RAISE WARNING 'One or more Kit items are flagged as purchase-to-order for this site, but no default item source is defined.';
158 $$ LANGUAGE 'plpgsql';