2 CREATE OR REPLACE FUNCTION woinvavail(integer, boolean, boolean, boolean, boolean)
3 RETURNS SETOF woinvav 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.
8 pshowchildindent ALIAS FOR $2;
9 pshowchildsum ALIAS FOR $3;
10 pshowshortage ALIAS FOR $4;
11 pshowlowinventory ALIAS FOR $5;
13 _subrow woinvav%ROWTYPE;
21 IF(pshowchildindent) THEN
38 (qoh + ordered - wobalance) AS woavail,
39 (qoh + ordered - allocated) AS totalavail
49 noNeg(itemsite_qtyonhand) AS qoh,
50 noNeg(wo_qtyord - wo_qtyrcv) AS wobalance,
51 qtyAllocated(itemsite_id, wo_duedate) AS allocated,
52 qtyOrdered(itemsite_id, wo_duedate) AS ordered,
53 CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel
54 FROM wo, itemsite, item, uom
55 WHERE ((wo_id = pwoid)
56 AND (itemsite_id = wo_itemsite_id)
57 AND (itemsite_item_id=item_id)
58 AND (item_inv_uom_id=uom_id))
59 ORDER BY wo_number, wo_subnumber) AS data
61 _row.woinvav_itemsite_id := _x.itemsite_id;
62 _row.woinvav_womatl_id := -1;
63 _row.woinvav_type := _x.item_type;
64 _row.woinvav_item_wo_number := _x.wo_number || '-' || _x.wo_subnumber;
65 _row.woinvav_descrip := _x.item_descrip1 || ' ' || _x.item_descrip2;
66 _row.woinvav_uomname := _x.uom_name;
67 _row.woinvav_qoh := _x.qoh;
68 _row.woinvav_balance := _x.wobalance;
69 _row.woinvav_allocated := _x.allocated;
70 _row.woinvav_ordered := _x.ordered;
71 _row.woinvav_woavail := _x.woavail;
72 _row.woinvav_totalavail := _x.totalavail;
73 _row.woinvav_reorderlevel := _x.reorderlevel;
74 _row.woinvav_level := 0;
76 --get materials for this level
78 SELECT * FROM woinvavailmatl(_x.wo_id, 1, pshowshortage, pshowlowinventory)
83 SELECT * FROM woinvavail(_x.wo_id, 1, pshowshortage, pshowlowinventory)
89 SELECT wo_number FROM wo WHERE wo_id=pwoid LIMIT 1 INTO _wonumber;
90 --display a single level sum of work order requirements
91 _qry := 'SELECT wo_id,
105 (qoh + ordered - wobalance) AS woavail,
106 (qoh + ordered - allocated) AS totalavail
116 noNeg(itemsite_qtyonhand) AS qoh,
117 noNeg(itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, womatl_qtyreq - womatl_qtyiss)) AS wobalance,
118 qtyAllocated(itemsite_id, womatl_duedate) AS allocated,
119 qtyOrdered(itemsite_id, womatl_duedate) AS ordered,
120 CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel
121 FROM wo, womatl, itemsite, item, uom
122 WHERE (womatl_wo_id=wo_id)
123 AND (womatl_itemsite_id=itemsite_id)
124 AND (itemsite_item_id=item_id)
125 AND (item_inv_uom_id=uom_id) ';
126 IF(pshowchildsum) THEN
127 _qry := _qry || ' AND (wo_number=' || _wonumber || ')';
129 _qry := _qry || ' AND (womatl_wo_id=' || pwoid || ')';
131 _qry := _qry || ' ORDER BY item_number) AS data ';
132 IF(pshowshortage) THEN
133 _qry := _qry || ' WHERE (((qoh + ordered - allocated) < 0) OR ((qoh + ordered - wobalance) < 0)) ';
135 IF(pshowlowinventory AND NOT pshowshortage) THEN
136 _qry := _qry || ' WHERE (((qoh - allocated) < 0) OR ((qoh - wobalance) < 0)) ';
142 _row.woinvav_itemsite_id := _x.itemsite_id;
143 _row.woinvav_womatl_id := _x.womatl_id;
144 _row.woinvav_type := _x.item_type;
145 _row.woinvav_item_wo_number := _x.item_number;
146 _row.woinvav_descrip := _x.item_descrip1 || ' ' || _x.item_descrip2;
147 _row.woinvav_uomname := _x.uom_name;
148 _row.woinvav_qoh := _x.qoh;
149 _row.woinvav_balance := _x.wobalance;
150 _row.woinvav_allocated := _x.allocated;
151 _row.woinvav_ordered := _x.ordered;
152 _row.woinvav_woavail := _x.woavail;
153 _row.woinvav_totalavail := _x.totalavail;
154 _row.woinvav_reorderlevel := _x.reorderlevel;
155 _row.woinvav_level := 0;
161 $$ LANGUAGE 'plpgsql';
163 CREATE OR REPLACE FUNCTION woinvavail(integer, integer, boolean, boolean)
164 RETURNS SETOF woinvav AS $$
165 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
166 -- See www.xtuple.com/CPAL for the full text of the software license.
170 pshowshortage ALIAS FOR $3;
171 pshowlowinventory ALIAS FOR $4;
172 _row woinvav%ROWTYPE;
194 (qoh + ordered - wobalance) AS woavail,
195 (qoh + ordered - allocated) AS totalavail
205 noNeg(itemsite_qtyonhand) AS qoh,
206 noNeg(wo_qtyord - wo_qtyrcv) AS wobalance,
207 qtyAllocated(itemsite_id, wo_duedate) AS allocated,
208 qtyOrdered(itemsite_id, wo_duedate) AS ordered,
209 CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel
210 FROM wo, itemsite, item, uom
211 WHERE ((wo_ordid = pwoid AND wo_ordtype = 'W')
212 AND NOT (wo_status = 'C')
213 AND (itemsite_id = wo_itemsite_id)
214 AND (itemsite_item_id=item_id)
215 AND (item_inv_uom_id=uom_id))
216 ORDER BY wo_number, wo_subnumber) AS data
218 _row.woinvav_itemsite_id := _x.itemsite_id;
219 _row.woinvav_womatl_id := -1;
220 _row.woinvav_type := _x.item_type;
221 _row.woinvav_item_wo_number := _x.wo_number || '-' || _x.wo_subnumber;
222 _row.woinvav_descrip := _x.item_descrip1 || ' ' || _x.item_descrip2;
223 _row.woinvav_uomname := _x.uom_name;
224 _row.woinvav_qoh := _x.qoh;
225 _row.woinvav_balance := _x.wobalance;
226 _row.woinvav_allocated := _x.allocated;
227 _row.woinvav_ordered := _x.ordered;
228 _row.woinvav_woavail := _x.woavail;
229 _row.woinvav_totalavail := _x.totalavail;
230 _row.woinvav_reorderlevel := _x.reorderlevel;
231 _row.woinvav_level := plevel;
233 --get materials for this level
235 SELECT * FROM woinvavailmatl(_x.wo_id, plevel + 1, pshowshortage, pshowlowinventory)
241 SELECT * FROM woinvavail(_x.wo_id, plevel + 1, pshowshortage, pshowlowinventory)
248 $$ LANGUAGE 'plpgsql';