2 CREATE OR REPLACE FUNCTION qtyAvailable(pItemsiteId INTEGER) RETURNS NUMERIC STABLE 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.
9 _qty := qtyAvailable(pItemsiteId, TRUE);
16 CREATE OR REPLACE FUNCTION qtyAvailable(pItemsiteId INTEGER,
17 pUsable BOOLEAN) RETURNS NUMERIC STABLE AS $$
18 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
19 -- See www.xtuple.com/CPAL for the full text of the software license.
25 -- Summarize itemloc qty for this itemsite/usable locations
26 -- or use itemsite_qtyonhand for regular/non-lot
27 SELECT COALESCE(SUM(itemloc_qty), itemsite_qtyonhand) INTO _qty
28 FROM itemsite LEFT OUTER JOIN itemloc ON (itemloc_itemsite_id=itemsite_id)
29 LEFT OUTER JOIN location ON (location_id=itemloc_location_id)
30 WHERE (itemsite_id=pItemsiteId)
31 AND ((location_id IS NULL) OR (COALESCE(location_usable, true)))
32 GROUP BY itemsite_qtyonhand;
34 -- Summarize itemloc qty for this itemsite/non-usable locations
35 SELECT COALESCE(SUM(itemloc_qty), 0.0) INTO _qty
36 FROM itemloc JOIN location ON (location_id=itemloc_location_id)
37 WHERE (itemloc_itemsite_id=pItemsiteId)
38 AND (NOT COALESCE(location_usable, true));
46 CREATE OR REPLACE FUNCTION qtyAvailable(pItemsiteid INTEGER,
47 pLookAheadDays INTEGER) RETURNS NUMERIC STABLE AS $$
48 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
49 -- See www.xtuple.com/CPAL for the full text of the software license.
54 RETURN ( ( SELECT qtyAvailable(itemsite_id)
56 WHERE (itemsite_id=pItemsiteid) ) +
57 (SELECT qtyOrdered(pItemsiteid, pLookAheadDays)) -
58 (SELECT qtyAllocated(pitemsiteid, pLookAheadDays)) );
63 CREATE OR REPLACE FUNCTION qtyAvailable(pItemsiteid INTEGER,
64 pDate DATE) RETURNS NUMERIC STABLE AS $$
65 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
66 -- See www.xtuple.com/CPAL for the full text of the software license.
71 RETURN ( ( SELECT qtyAvailable(itemsite_id)
73 WHERE (itemsite_id=pItemsiteid) ) +
74 (SELECT qtyOrdered(pItemsiteid, (pDate - CURRENT_DATE))) -
75 (SELECT qtyAllocated(pItemsiteid, (pDate - CURRENT_DATE))) );