2 CREATE OR REPLACE FUNCTION qtyAtLocation(pItemsiteid INTEGER,
3 pLocationid INTEGER) RETURNS NUMERIC STABLE AS $$
4 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
5 -- See www.xtuple.com/CPAL for the full text of the software license.
10 SELECT CASE WHEN (pLocationid IS NULL) THEN itemsite_qtyonhand
11 ELSE COALESCE(SUM(itemloc_qty), 0.0)
13 FROM itemsite LEFT OUTER JOIN itemloc ON (itemloc_itemsite_id=itemsite_id)
14 WHERE ((itemsite_id=pItemsiteid)
15 AND (itemloc_location_id=pLocationid))
16 GROUP BY itemsite_qtyonhand;