1 CREATE OR REPLACE FUNCTION sufficientInventoryToShipItem(TEXT, INTEGER) RETURNS INTEGER 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 pordertype ALIAS FOR $1;
6 porderitemid ALIAS FOR $2;
9 RETURN sufficientInventoryToShipItem(pordertype, porderitemid, NULL);
11 $$ LANGUAGE 'plpgsql';
13 CREATE OR REPLACE FUNCTION sufficientInventoryToShipItem(TEXT, INTEGER, NUMERIC) RETURNS INTEGER AS $$
14 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
15 -- See www.xtuple.com/CPAL for the full text of the software license.
17 pordertype ALIAS FOR $1;
18 porderitemid ALIAS FOR $2;
24 IF (porderitemid IS NULL) THEN
28 IF (pordertype = 'SO') THEN
29 IF ( SELECT fetchMetricBool('EnableSOReservations') ) THEN
30 IF (SELECT (itemsite_costmethod = 'J')
31 FROM coitem JOIN itemsite ON (coitem_itemsite_id=itemsite_id)
32 WHERE (coitem_id=porderitemid)) THEN
36 SELECT (((COALESCE(pqty, roundQty(item_fractional,
37 noNeg(coitem_qtyord - coitem_qtyshipped +
38 coitem_qtyreturned - qtyAtShipping(pordertype, coitem_id)
39 ))) - coitem_qtyreserved) * coitem_qty_invuomratio
40 ) <= qtyAvailable(itemsite_id))
42 (((COALESCE(pqty, roundQty(item_fractional,
43 noNeg(coitem_qtyord - coitem_qtyshipped +
44 coitem_qtyreturned - qtyAtShipping(pordertype, coitem_id)
45 ))) - coitem_qtyreserved) * coitem_qty_invuomratio
46 ) <= qtyunreserved(itemsite_id))
48 FROM coitem, itemsite, item
49 WHERE ((coitem_itemsite_id=itemsite_id)
50 AND (coitem_status <> 'X')
51 AND (NOT ((item_type IN ('R','J')) OR (itemsite_controlmethod = 'N')))
52 AND (itemsite_item_id=item_id)
53 AND (coitem_id=porderitemid));
55 SELECT (COALESCE(pqty, roundQty(item_fractional,
56 noNeg(coitem_qtyord - coitem_qtyshipped +
57 coitem_qtyreturned - qtyAtShipping(pordertype, coitem_id) - coitem_qtyreserved
58 ) * coitem_qty_invuomratio
60 ) <= qtyAvailable(itemsite_id))
62 FROM coitem, itemsite, item
63 WHERE ((coitem_itemsite_id=itemsite_id)
64 AND (coitem_status <> 'X')
65 AND (NOT ((item_type IN ('R','J')) OR (itemsite_controlmethod = 'N')))
66 AND (itemsite_item_id=item_id)
67 AND (coitem_id=porderitemid));
69 ELSEIF (pordertype = 'TO') THEN
70 SELECT (COALESCE(pqty, roundQty(item_fractional,
71 noNeg(toitem_qty_ordered - toitem_qty_shipped -
72 qtyAtShipping(pordertype, toitem_id)
75 ) <= qtyAvailable(itemsite_id)) INTO _isqtyavail
76 FROM toitem, tohead, itemsite, item
77 WHERE ((toitem_tohead_id=tohead_id)
78 AND (tohead_src_warehous_id=itemsite_warehous_id)
79 AND (toitem_item_id=itemsite_item_id)
80 AND (itemsite_warehous_id=tohead_src_warehous_id)
81 AND (itemsite_item_id=item_id)
82 AND (toitem_status <> 'X')
83 AND (NOT ((item_type IN ('R','J')) OR (itemsite_controlmethod = 'N')))
84 AND (toitem_id=porderitemid));
89 IF (NOT _isqtyavail) THEN
93 IF (pordertype = 'SO') THEN
94 SELECT (COALESCE((SELECT SUM(itemloc_qty)
96 WHERE (itemloc_itemsite_id=itemsite_id)), 0.0) >= roundQty(item_fractional,
97 COALESCE(pQty, noNeg( coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned -
98 qtyAtShipping(pordertype, coitem_id) )) * coitem_qty_invuomratio
100 FROM coitem, itemsite, item
101 WHERE ((coitem_itemsite_id=itemsite_id)
102 AND (itemsite_item_id=item_id)
103 AND (NOT ((item_type ='R') OR (itemsite_controlmethod = 'N')))
104 AND ((itemsite_controlmethod IN ('L', 'S')) OR (itemsite_loccntrl))
105 AND (coitem_id=porderitemid));
107 ELSEIF (pordertype = 'TO') THEN
108 SELECT (COALESCE((SELECT SUM(itemloc_qty)
110 WHERE (itemloc_itemsite_id=itemsite_id)), 0.0) >= roundQty(item_fractional,
111 noNeg( toitem_qty_ordered - toitem_qty_shipped -
112 qtyAtShipping(pordertype, toitem_id) )
114 FROM toitem, tohead, itemsite, item
115 WHERE ((toitem_tohead_id=tohead_id)
116 AND (tohead_src_warehous_id=itemsite_warehous_id)
117 AND (toitem_item_id=itemsite_item_id)
118 AND (itemsite_item_id=item_id)
119 AND (toitem_status <> 'X')
120 AND (NOT ((item_type ='R') OR (itemsite_costmethod = 'J') OR (itemsite_controlmethod = 'N')))
121 AND ((itemsite_controlmethod IN ('L', 'S')) OR (itemsite_loccntrl))
122 AND (toitem_id=porderitemid));
125 IF (NOT _isqtyavail) THEN
129 IF (pordertype = 'SO') THEN
130 IF ( SELECT fetchMetricBool('RequireSOReservations') ) THEN
131 SELECT (COALESCE(pqty, coitem_qtyreserved) <= coitem_qtyreserved)
133 FROM coitem, itemsite, item
134 WHERE ((coitem_itemsite_id=itemsite_id)
135 AND (coitem_status <> 'X')
136 AND (NOT ((item_type IN ('R','J')) OR (itemsite_controlmethod = 'N')))
137 AND (itemsite_item_id=item_id)
138 AND (coitem_id=porderitemid));
142 IF (NOT _isqtyavail) THEN
148 $$ LANGUAGE 'plpgsql';