1 CREATE OR REPLACE FUNCTION issueToShipping(INTEGER, NUMERIC) RETURNS INTEGER AS $$
2 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
3 -- See www.xtuple.com/CPAL for the full text of the software license.
5 RETURN issueToShipping('SO', $1, $2, 0, CURRENT_TIMESTAMP);
9 CREATE OR REPLACE FUNCTION issueToShipping(INTEGER, NUMERIC, INTEGER) RETURNS INTEGER AS $$
10 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
11 -- See www.xtuple.com/CPAL for the full text of the software license.
13 RETURN issueToShipping('SO', $1, $2, $3, CURRENT_TIMESTAMP);
15 $$ LANGUAGE 'plpgsql';
17 CREATE OR REPLACE FUNCTION issueToShipping(TEXT, INTEGER, NUMERIC, INTEGER, TIMESTAMP WITH TIME ZONE) RETURNS INTEGER AS $$
18 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
19 -- See www.xtuple.com/CPAL for the full text of the software license.
21 RETURN issueToShipping($1, $2, $3, $4, $5, NULL);
23 $$ LANGUAGE 'plpgsql';
25 CREATE OR REPLACE FUNCTION issueToShipping(TEXT, INTEGER, NUMERIC, INTEGER, TIMESTAMP WITH TIME ZONE, INTEGER) RETURNS INTEGER AS $$
26 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
27 -- See www.xtuple.com/CPAL for the full text of the software license.
29 pordertype ALIAS FOR $1;
32 _itemlocSeries INTEGER := $4;
33 _timestamp TIMESTAMP WITH TIME ZONE := $5;
34 pinvhistid ALIAS FOR $6;
49 IF (_timestamp IS NULL) THEN
50 _timestamp := CURRENT_TIMESTAMP;
53 IF (_itemlocSeries = 0) THEN
54 _itemlocSeries := NEXTVAL('itemloc_series_seq');
57 IF (pordertype = 'SO') THEN
59 -- Check site security
60 SELECT warehous_id INTO _warehouseid
61 FROM coitem,itemsite,site()
62 WHERE ((coitem_id=pitemid)
63 AND (itemsite_id=coitem_itemsite_id)
64 AND (warehous_id=itemsite_warehous_id));
70 -- Check for average cost items going negative
71 IF ( SELECT ( (itemsite_costmethod='A') AND
72 ((itemsite_qtyonhand - round(pQty * coitem_qty_invuomratio, 6)) < 0.0) )
73 FROM coitem JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
74 WHERE (coitem_id=pitemid) ) THEN
78 -- Check auto registration
79 IF ( SELECT COALESCE(itemsite_autoreg, FALSE)
80 FROM coitem JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
81 WHERE (coitem_id=pitemid) ) THEN
82 SELECT COALESCE(crmacct_cntct_id_1, -1) INTO _cntctid
83 FROM coitem JOIN cohead ON (cohead_id=coitem_cohead_id)
84 JOIN crmacct ON (crmacct_cust_id=cohead_cust_id)
85 WHERE (coitem_id=pitemid);
86 IF (_cntctid = -1) THEN
91 SELECT shiphead_id INTO _shipheadid
92 FROM shiphead, coitem JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
93 WHERE ( (coitem_id=pitemid)
94 AND (shiphead_number=getOpenShipment(pordertype, coitem_cohead_id, itemsite_warehous_id)) );
96 SELECT NEXTVAL('shiphead_shiphead_id_seq') INTO _shipheadid;
98 _shipnumber := fetchShipmentNumber();
99 IF (_shipnumber < 0) THEN
103 SELECT cohead_holdtype INTO _coholdtype
105 WHERE ((cohead_id=coitem_cohead_id)
106 AND (coitem_id=pitemid));
108 IF (_coholdtype = 'C') THEN
110 ELSIF (_coholdtype = 'P') THEN
112 ELSIF (_coholdtype = 'R') THEN
117 ( shiphead_id, shiphead_number, shiphead_order_id, shiphead_order_type,
119 shiphead_sfstatus, shiphead_shipvia, shiphead_shipchrg_id,
120 shiphead_freight, shiphead_freight_curr_id,
121 shiphead_shipdate, shiphead_notes, shiphead_shipform_id )
122 SELECT _shipheadid, _shipnumber, coitem_cohead_id, pordertype,
125 CASE WHEN (cohead_shipchrg_id <= 0) THEN NULL
126 ELSE cohead_shipchrg_id
128 cohead_freight, cohead_curr_id,
129 _timestamp::DATE, cohead_shipcomments,
130 CASE WHEN cohead_shipform_id = -1 THEN NULL
131 ELSE cohead_shipform_id
134 WHERE ((coitem_cohead_id=cohead_id)
135 AND (coitem_id=pitemid) );
138 SET pack_shiphead_id = _shipheadid,
141 WHERE ((pack_head_id=coitem_cohead_id)
142 AND (pack_shiphead_id IS NULL)
143 AND (pack_head_type='SO')
144 AND (coitem_id=pitemid));
148 SET pack_printed = FALSE
150 WHERE ((pack_head_id=coitem_cohead_id)
151 AND (pack_shiphead_id=_shipheadid)
152 AND (pack_head_type='SO')
153 AND (coitem_id=pitemid));
156 -- Handle g/l transaction
157 SELECT postInvTrans( itemsite_id, 'SH', (pQty * coitem_qty_invuomratio),
159 formatSoNumber(coitem_id), shiphead_number,
160 ('Issue ' || item_number || ' to Shipping for customer ' || cohead_billtoname),
161 getPrjAccntId(cohead_prj_id, costcat_shipasset_accnt_id), costcat_asset_accnt_id,
162 _itemlocSeries, _timestamp, NULL, pinvhistid ) INTO _invhistid
163 FROM coitem, cohead, itemsite, item, costcat, shiphead
164 WHERE ( (coitem_cohead_id=cohead_id)
165 AND (coitem_itemsite_id=itemsite_id)
166 AND (itemsite_item_id=item_id)
167 AND (itemsite_costcat_id=costcat_id)
168 AND (coitem_id=pitemid)
169 AND (shiphead_id=_shipheadid) );
171 SELECT (invhist_unitcost * invhist_invqty) INTO _value
173 WHERE (invhist_id=_invhistid);
175 _shipitemid := nextval('shipitem_shipitem_id_seq');
177 ( shipitem_id, shipitem_shiphead_id, shipitem_orderitem_id, shipitem_qty,
178 shipitem_transdate, shipitem_trans_username, shipitem_invoiced,
179 shipitem_value, shipitem_invhist_id )
181 ( _shipitemid, _shipheadid, pitemid, pQty,
182 _timestamp, getEffectiveXtUser(), FALSE,
184 CASE WHEN _invhistid = -1 THEN
190 -- Handle reservation
191 IF (fetchmetricbool('EnableSOReservations')) THEN
192 -- Remember what was reserved so we can re-reserve if this issue is returned
193 INSERT INTO shipitemrsrv
194 (shipitemrsrv_shipitem_id, shipitemrsrv_qty)
195 SELECT _shipitemid, least(pQty,coitem_qtyreserved)
197 WHERE ((coitem_id=pitemid)
198 AND (coitem_qtyreserved > 0));
200 -- Update sales order
202 SET coitem_qtyreserved = noNeg(coitem_qtyreserved - pQty)
203 WHERE(coitem_id=pitemid);
206 -- Calculate shipment freight
207 SELECT calcShipFreight(_shipheadid) INTO _freight;
208 UPDATE shiphead SET shiphead_freight=_freight
209 WHERE (shiphead_id=_shipheadid);
211 ELSEIF (pordertype = 'TO') THEN
213 -- Check site security
214 IF (fetchMetricBool('MultiWhs')) THEN
215 SELECT warehous_id INTO _warehouseid
216 FROM toitem, tohead, site()
217 WHERE ( (toitem_id=pitemid)
218 AND (tohead_id=toitem_tohead_id)
219 AND (warehous_id=tohead_src_warehous_id) );
226 SELECT postInvTrans( itemsite_id, 'SH', pQty, 'S/R',
227 pordertype, formatToNumber(toitem_id), '', 'Issue to Shipping',
228 costcat_shipasset_accnt_id, costcat_asset_accnt_id,
229 _itemlocSeries, _timestamp) INTO _invhistid
230 FROM tohead, toitem, itemsite, costcat
231 WHERE ((tohead_id=toitem_tohead_id)
232 AND (itemsite_item_id=toitem_item_id)
233 AND (itemsite_warehous_id=tohead_src_warehous_id)
234 AND (itemsite_costcat_id=costcat_id)
235 AND (toitem_id=pitemid) );
237 SELECT (invhist_unitcost * invhist_invqty) INTO _value
239 WHERE (invhist_id=_invhistid);
241 SELECT shiphead_id INTO _shipheadid
242 FROM shiphead, toitem JOIN tohead ON (tohead_id=toitem_tohead_id)
243 WHERE ( (toitem_id=pitemid)
244 AND (shiphead_number=getOpenShipment(pordertype, tohead_id, tohead_src_warehous_id)) );
247 _shipheadid := NEXTVAL('shiphead_shiphead_id_seq');
249 _shipnumber := fetchShipmentNumber();
250 IF (_shipnumber < 0) THEN
255 ( shiphead_id, shiphead_number, shiphead_order_id, shiphead_order_type,
257 shiphead_sfstatus, shiphead_shipvia, shiphead_shipchrg_id,
258 shiphead_freight, shiphead_freight_curr_id,
259 shiphead_shipdate, shiphead_notes, shiphead_shipform_id )
260 SELECT _shipheadid, _shipnumber, tohead_id, pordertype,
262 'N', tohead_shipvia, tohead_shipchrg_id,
263 tohead_freight + SUM(toitem_freight), tohead_freight_curr_id,
264 _timestamp::DATE, tohead_shipcomments, tohead_shipform_id
266 WHERE ((toitem_tohead_id=tohead_id)
267 AND (tohead_id IN (SELECT toitem_tohead_id
269 WHERE (toitem_id=pitemid))) )
270 GROUP BY tohead_id, tohead_shipvia, tohead_shipchrg_id, tohead_freight,
271 tohead_freight_curr_id, tohead_shipcomments, tohead_shipform_id;
275 ( shipitem_shiphead_id, shipitem_orderitem_id, shipitem_qty,
276 shipitem_transdate, shipitem_trans_username, shipitem_invoiced,
277 shipitem_value, shipitem_invhist_id )
279 ( _shipheadid, pitemid, pQty,
280 _timestamp, getEffectiveXtUser(), FALSE,
282 CASE WHEN _invhistid = -1 THEN NULL
291 RETURN _itemlocSeries;
294 $$ LANGUAGE 'plpgsql';