1 CREATE OR REPLACE FUNCTION woinvavailmatl(integer, integer, boolean, boolean)
2 RETURNS SETOF woinvav 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.
8 pshowshortage ALIAS FOR $3;
9 pshowlowinventory ALIAS FOR $4;
10 _subrow woinvav%ROWTYPE;
15 _qry := 'SELECT itemsite_id,
30 (qoh + ordered - wobalance) AS woavail,
31 (qoh + ordered - allocated) AS totalavail,
33 FROM(SELECT itemsite_id,
44 noNeg(qtyNetable(itemsite_id)) AS qoh,
45 noNeg(itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, womatl_qtyreq - womatl_qtyiss)) AS wobalance,
46 qtyAllocated(itemsite_id, womatl_duedate) AS allocated,
47 qtyOrdered(itemsite_id, womatl_duedate) AS ordered,
48 CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel
49 FROM womatl, wo, itemsite, item, uom
50 WHERE ((wo_id = womatl_wo_id)
51 AND (womatl_itemsite_id = itemsite_id)
52 AND (itemsite_item_id=item_id)
53 AND (womatl_uom_id=uom_id)
54 AND (NOT womatl_createwo OR womatl_createwo IS NULL))';
55 _qry := _qry || ' AND (wo_id=' || pwoid || ') ORDER BY item_number) AS data';
56 IF(pshowshortage) THEN
57 _qry := _qry || ' WHERE (((qoh + ordered - allocated) < 0) OR ((qoh + ordered - wobalance) < 0)) ';
59 IF(pshowlowinventory AND NOT pshowshortage) THEN
60 _qry := _qry || ' WHERE (((qoh - allocated) < 0) OR ((qoh - wobalance) < 0)) ';
68 _subrow.woinvav_itemsite_id := _subx.itemsite_id;
69 _subrow.woinvav_womatl_id := _subx.womatl_id;
70 _subrow.woinvav_type := _subx.item_type;
71 _subrow.woinvav_item_wo_number := _subx.item_number;
72 _subrow.woinvav_descrip := _subx.item_descrip1 || ' ' || _subx.item_descrip2;
73 _subrow.woinvav_uomname := _subx.uom_name;
74 _subrow.woinvav_qoh := _subx.qoh;
75 _subrow.woinvav_balance := _subx.wobalance;
76 _subrow.woinvav_allocated := _subx.allocated;
77 _subrow.woinvav_ordered := _subx.ordered;
78 _subrow.woinvav_woavail := _subx.woavail;
79 _subrow.woinvav_totalavail := _subx.totalavail;
80 _subrow.woinvav_reorderlevel := _subx.reorderlevel;
81 _subrow.woinvav_level := plevel;
86 $$ LANGUAGE 'plpgsql';