2 CREATE OR REPLACE FUNCTION indentedwomatl(integer, integer) RETURNS SETOF wodata 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.
12 SELECT * FROM indentedwomatl(pwoid, NULL::integer, plevel)
18 $$ LANGUAGE 'plpgsql';
21 CREATE OR REPLACE FUNCTION indentedwomatl(integer, integer, integer) RETURNS SETOF wodata AS $$
22 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
23 -- See www.xtuple.com/CPAL for the full text of the software license.
26 pwooperid ALIAS FOR $2;
29 _subrow wodata%ROWTYPE;
35 --The wodata id column is used to indicate the source of the id
36 --there are three different tables used wo, womatl and womatlvar
37 --wodata_id_type = 1 = wo_id
38 --wodata_id_type = 2 = womatl_id
39 --wodata_id_type = 3 = wooper_id
41 _qry := 'SELECT womatl_id, wo_number, wo_subnumber,
42 wo_startdate, womatl_duedate, womatl_itemsite_id,
43 qtyNetable(itemsite_id) AS netableqoh, womatl_qtyreq, womatl_qtyiss,
44 womatl_qtyper, womatl_qtyreq, womatl_scrap,
45 womatl_ref, womatl_notes, womatl_price, item_listprice,
46 item_number, item_descrip1, item_descrip2,
48 FROM womatl, wo, itemsite, item, uom
49 WHERE ((wo_id = womatl_wo_id)
50 AND (wo_id = ' || pwoid || ')
51 AND (womatl_itemsite_id = itemsite_id)
52 AND (itemsite_item_id=item_id)
53 AND (womatl_uom_id=uom_id) ';
54 -- Need to display in case child w/o is deleted
55 -- AND (NOT womatl_createwo OR womatl_createwo IS NULL) ';
57 IF (pwooperid IS NOT NULL) THEN
58 _qry := _qry || 'AND (womatl_wooper_id=' || pwooperid || ')';
61 _qry := _qry || ') ORDER BY item_number;';
64 SELECT wo_status FROM wo WHERE wo_id = pwoid LIMIT 1 INTO _status;
69 _subrow.wodata_id := _subx.womatl_id;
70 _subrow.wodata_id_type := 2;
71 _subrow.wodata_number := _subx.wo_number;
72 _subrow.wodata_subnumber := _subx.wo_subnumber;
73 _subrow.wodata_itemnumber := _subx.item_number;
74 _subrow.wodata_descrip := _subx.item_descrip1 || '-' || _subx.item_descrip2;
75 _subrow.wodata_status := _status;
76 _subrow.wodata_startdate := _subx.wo_startdate;
77 _subrow.wodata_duedate := _subx.womatl_duedate;
78 _subrow.wodata_itemsite_id := _subx.womatl_itemsite_id;
79 _subrow.wodata_custprice := _subx.womatl_price;
80 _subrow.wodata_listprice := _subx.item_listprice;
81 _subrow.wodata_qoh := _subx.netableqoh;
82 IF((_subx.netableqoh > (_subx.womatl_qtyreq - _subx.womatl_qtyiss))) THEN
83 _subrow.wodata_short := 0;
85 _subrow.wodata_short := (_subx.womatl_qtyreq - _subx.womatl_qtyiss) - _subx.netableqoh;
87 _subrow.wodata_qtyper := _subx.womatl_qtyper;
88 _subrow.wodata_qtyiss := _subx.womatl_qtyiss;
89 _subrow.wodata_qtyordreq := _subx.womatl_qtyreq;
90 _subrow.wodata_qtyuom := _subx.uom_name;
91 _subrow.wodata_scrap := _subx.womatl_scrap;
92 _subrow.wodata_notes := _subx.womatl_notes;
93 _subrow.wodata_ref := _subx.womatl_ref;
94 _subrow.wodata_level := _level;
100 $$ LANGUAGE 'plpgsql';