2 CREATE OR REPLACE FUNCTION indentedwo(integer, boolean, boolean, boolean) 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.
8 pshowmatl ALIAS FOR $3;
9 pshowindent ALIAS FOR $4;
11 _subrow wodata%ROWTYPE;
17 --The wodata_id_type column is used to indicate the source of the wodata_id
18 --there are three different tables used wo, womatl and womatlvar
19 --wodata_id_type = 1 = wo_id
20 --wodata_id_type = 2 = womatl_id
21 --wodata_id_type = 3 = wooper_id
24 --get top level works orders
26 SELECT wo_id,wo_number,wo_subnumber,wo_status,wo_startdate,
27 wo_duedate,wo_adhoc,wo_itemsite_id,qtyAvailable(itemsite_id) AS availableqoh,
28 wo_qtyord,wo_qtyrcv,wo_prodnotes, item_number,
29 item_descrip1, item_descrip2, item_listprice, uom_name
30 FROM wo, itemsite, item, uom
31 WHERE ((wo_id = pwoid)
32 AND (itemsite_id = wo_itemsite_id)
33 AND (itemsite_item_id=item_id)
34 AND (item_inv_uom_id=uom_id))
35 ORDER BY wo_number, wo_subnumber
37 _row.wodata_id := _x.wo_id;
38 _row.wodata_id_type := 1;
39 _row.wodata_number := _x.wo_number;
40 _row.wodata_subnumber := _x.wo_subnumber;
41 _row.wodata_itemnumber := _x.item_number;
42 _row.wodata_descrip := _x.item_descrip1 || '-' || _x.item_descrip2;
43 _row.wodata_status := _x.wo_status;
44 _row.wodata_startdate := _x.wo_startdate;
45 _row.wodata_duedate := _x.wo_duedate;
46 _row.wodata_adhoc := _x.wo_adhoc;
47 _row.wodata_itemsite_id := _x.wo_itemsite_id;
48 _row.wodata_custprice := _x.item_listprice;
49 _row.wodata_listprice := _x.item_listprice;
50 _row.wodata_qoh := _x.availableqoh;
51 _row.wodata_short := noneg(_x.wo_qtyord - _x.wo_qtyrcv);
52 _row.wodata_qtyrcv := _x.wo_qtyrcv;
53 _row.wodata_qtyordreq := _x.wo_qtyord;
54 _row.wodata_qtyuom := _x.uom_name;
55 _row.wodata_scrap := 0;
56 _row.wodata_notes := _x.wo_prodnotes;
57 _row.wodata_level := _level;
59 IF (pshowmatl AND NOT pshowops) THEN
62 SELECT * FROM indentedwomatl(pwoid, _level)
68 IF ((pshowmatl OR pshowindent) AND NOT pshowops) THEN
69 --expand next level down
71 SELECT * FROM indentedwo(_x.wo_id, NULL, _level + 1, pshowmatl, pshowindent)
78 --expand materials not on operations
81 SELECT * FROM indentedwomatl(pwoid, -1, _level)
87 IF (pshowmatl OR pshowindent) THEN
88 --expand next level down
90 SELECT * FROM indentedwo(_x.wo_id, -1, _level + 1, pshowmatl, pshowindent)
98 SELECT * FROM xtmfg.indentedwoops(pwoid,_level)
103 --expand materials on operations
105 SELECT * FROM indentedwomatl(pwoid, _opx.wodata_id, _level + 1)
111 IF (pshowmatl OR pshowindent) THEN
112 --expand next level down
114 SELECT * FROM indentedwo(_x.wo_id, _opx.wodata_id, _level + 2, pshowmatl, pshowindent)
124 $$ LANGUAGE 'plpgsql';
126 CREATE OR REPLACE FUNCTION indentedwo(integer, integer, integer, boolean, boolean) RETURNS SETOF wodata AS $$
127 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
128 -- See www.xtuple.com/CPAL for the full text of the software license.
131 pwooperid ALIAS FOR $2;
133 pshowmatl ALIAS FOR $4;
134 pshowindent ALIAS FOR $5;
144 --The wodata id column is used to indicate the source of the id
145 --there are three different tables used wo, womatl and womatlvar
146 --wodata_id_type = 1 = wo_id
147 --wodata_id_type = 2 = womatl_id
148 --wodata_id_type = 3 = wooper_id
149 _level := (plevel + 1);
150 --find all WO with the ordid of the next level up
151 _qry := 'SELECT wo_id,wo_number,wo_subnumber,wo_status,wo_startdate,wo_duedate,
152 wo_adhoc,wo_itemsite_id,qtyAvailable(itemsite_id) AS availableqoh,wo_qtyord,wo_qtyrcv, wo_prodnotes,
153 item_number,item_descrip1, item_descrip2, item_listprice, uom_name,
154 womatl_qtyiss, womatl_scrap, womatl_wooper_id
155 FROM itemsite, wo, item, uom, womatl
156 WHERE ((wo_ordid = ' || pwoid || ')
157 AND (wo_ordtype = ''W'')
158 AND (itemsite_item_id=item_id)
159 AND (item_inv_uom_id=uom_id)
160 AND (wo_womatl_id=womatl_id)
161 AND (wo_itemsite_id = itemsite_id) ';
163 IF (pwooperid IS NOT NULL) THEN
164 _qry := _qry || ' AND (womatl_wooper_id=' || pwooperid || ') ';
167 _qry := _qry || ') ORDER BY wo_number, wo_subnumber';
168 /* if (pwooperid IS NOT NULL) THEN
169 raise exception 'stop %',_qry;
174 _row.wodata_id := _x.wo_id;
175 _row.wodata_id_type := 1;
176 _row.wodata_number := _x.wo_number;
177 _row.wodata_subnumber := _x.wo_subnumber;
178 _row.wodata_itemnumber := _x.item_number;
179 _row.wodata_descrip := _x.item_descrip1 || '-' || _x.item_descrip2;
180 _row.wodata_status := _x.wo_status;
181 _row.wodata_startdate := _x.wo_startdate;
182 _row.wodata_duedate := _x.wo_duedate;
183 _row.wodata_adhoc := _x.wo_adhoc;
184 _row.wodata_itemsite_id := _x.wo_itemsite_id;
185 _row.wodata_custprice := _x.item_listprice;
186 _row.wodata_listprice := _x.item_listprice;
187 _row.wodata_qoh := _x.availableqoh;
188 _row.wodata_short := noneg(_x.wo_qtyord - _x.wo_qtyrcv);
189 _row.wodata_qtyiss := _x.womatl_qtyiss;
190 _row.wodata_qtyrcv := _x.wo_qtyrcv;
191 _row.wodata_qtyordreq := _x.wo_qtyord;
192 _row.wodata_scrap := _x.womatl_scrap;
193 _row.wodata_notes := _x.wo_prodnotes;
194 _row.wodata_level := plevel;
196 --if indentation require expand next level
197 IF (pshowindent AND pwooperid IS NULL) THEN
198 IF (pshowmatl AND pshowindent) THEN
199 --get materials for this level
201 SELECT * FROM indentedwomatl(_x.wo_id, plevel)
207 IF (pshowindent) THEN
208 --expand lower levels
210 SELECT * FROM indentedwo(_x.wo_id, NULL, _level, pshowmatl, pshowindent )
216 ELSIF (pshowindent) THEN --Handle operations
217 --expand materials not on operations
220 SELECT * FROM indentedwomatl(_x.wo_id, -1, plevel)
226 --expand next level down not on operations
228 SELECT * FROM indentedwo(_x.wo_id, -1, _level, pshowmatl, pshowindent)
235 SELECT * FROM xtmfg.indentedwoops(_x.wo_id,plevel)
240 --expand materials on operations
242 SELECT * FROM indentedwomatl(_x.wo_id, _opx.wodata_id, _level)
245 -- raise exception 'stop %',_opx.wodata_id;
249 --expand next level down
251 SELECT * FROM indentedwo(_x.wo_id, _opx.wodata_id, _level + 2, pshowmatl, pshowindent)
261 $$ LANGUAGE 'plpgsql';