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);
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);
25 CREATE OR REPLACE FUNCTION issueToShipping(pordertype TEXT,
28 pItemlocSeries INTEGER,
29 pTimestamp TIMESTAMP WITH TIME ZONE,
30 pinvhistid INTEGER) RETURNS INTEGER AS $$
31 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
32 -- See www.xtuple.com/CPAL for the full text of the software license.
34 _itemlocSeries INTEGER;
35 _timestamp TIMESTAMP WITH TIME ZONE;
51 IF (pTimestamp IS NULL) THEN
52 _timestamp := CURRENT_TIMESTAMP;
54 _timestamp := pTimestamp;
57 IF (pItemlocSeries = 0) THEN
58 _itemlocSeries := NEXTVAL('itemloc_series_seq');
60 _itemlocSeries := pItemlocSeries;
63 IF (pordertype = 'SO') THEN
65 -- Check site security
66 SELECT warehous_id INTO _warehouseid
67 FROM coitem,itemsite,site()
68 WHERE ((coitem_id=pitemid)
69 AND (itemsite_id=coitem_itemsite_id)
70 AND (warehous_id=itemsite_warehous_id));
76 -- Check for average cost items going negative
77 IF ( SELECT ( (itemsite_costmethod='A') AND
78 ((itemsite_qtyonhand - round(pQty * coitem_qty_invuomratio, 6)) < 0.0) )
79 FROM coitem JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
80 WHERE (coitem_id=pitemid) ) THEN
84 -- Check auto registration
85 IF ( SELECT COALESCE(itemsite_autoreg, FALSE)
86 FROM coitem JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
87 WHERE (coitem_id=pitemid) ) THEN
88 SELECT COALESCE(crmacct_cntct_id_1, -1) INTO _cntctid
89 FROM coitem JOIN cohead ON (cohead_id=coitem_cohead_id)
90 JOIN crmacct ON (crmacct_cust_id=cohead_cust_id)
91 WHERE (coitem_id=pitemid);
92 IF (_cntctid = -1) THEN
98 SELECT cohead_holdtype INTO _coholdtype
99 FROM coitem JOIN cohead ON (cohead_id=coitem_cohead_id)
100 WHERE (coitem_id=pitemid);
102 SELECT calcSalesOrderAmt(cohead_id) -
103 COALESCE(SUM(currToCurr(aropenalloc_curr_id, cohead_curr_id,
104 aropenalloc_amount, cohead_orderdate)),0) INTO _balance
105 FROM coitem JOIN cohead ON (cohead_id=coitem_cohead_id)
106 LEFT OUTER JOIN aropenalloc ON (aropenalloc_doctype='S' AND
107 aropenalloc_doc_id=cohead_id)
108 WHERE (coitem_id=pitemid)
111 --RAISE NOTICE 'issueToShipping - order balance is %', _balance;
112 IF ( (_coholdtype = 'C') AND (_balance > 0.0) ) THEN
114 ELSIF (_coholdtype = 'P') THEN
116 ELSIF (_coholdtype = 'R') THEN
120 SELECT shiphead_id INTO _shipheadid
121 FROM shiphead, coitem JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
122 WHERE ( (coitem_id=pitemid)
123 AND (shiphead_number=getOpenShipment(pordertype, coitem_cohead_id, itemsite_warehous_id)) );
125 SELECT NEXTVAL('shiphead_shiphead_id_seq') INTO _shipheadid;
127 _shipnumber := fetchShipmentNumber();
128 IF (_shipnumber < 0) THEN
133 ( shiphead_id, shiphead_number, shiphead_order_id, shiphead_order_type,
135 shiphead_sfstatus, shiphead_shipvia, shiphead_shipchrg_id,
136 shiphead_freight, shiphead_freight_curr_id,
137 shiphead_shipdate, shiphead_notes, shiphead_shipform_id )
138 SELECT _shipheadid, _shipnumber, coitem_cohead_id, pordertype,
141 CASE WHEN (cohead_shipchrg_id <= 0) THEN NULL
142 ELSE cohead_shipchrg_id
144 cohead_freight, cohead_curr_id,
145 _timestamp::DATE, cohead_shipcomments,
146 CASE WHEN cohead_shipform_id = -1 THEN NULL
147 ELSE cohead_shipform_id
150 WHERE ((coitem_cohead_id=cohead_id)
151 AND (coitem_id=pitemid) );
154 SET pack_shiphead_id = _shipheadid,
157 WHERE ((pack_head_id=coitem_cohead_id)
158 AND (pack_shiphead_id IS NULL)
159 AND (pack_head_type='SO')
160 AND (coitem_id=pitemid));
164 SET pack_printed = FALSE
166 WHERE ((pack_head_id=coitem_cohead_id)
167 AND (pack_shiphead_id=_shipheadid)
168 AND (pack_head_type='SO')
169 AND (coitem_id=pitemid));
172 -- Handle g/l transaction
173 SELECT postInvTrans( itemsite_id, 'SH', (pQty * coitem_qty_invuomratio),
175 formatSoNumber(coitem_id), shiphead_number,
176 ('Issue ' || item_number || ' to Shipping for customer ' || cohead_billtoname),
177 getPrjAccntId(cohead_prj_id, costcat_shipasset_accnt_id), costcat_asset_accnt_id,
178 _itemlocSeries, _timestamp, NULL, pinvhistid ) INTO _invhistid
179 FROM coitem, cohead, itemsite, item, costcat, shiphead
180 WHERE ( (coitem_cohead_id=cohead_id)
181 AND (coitem_itemsite_id=itemsite_id)
182 AND (itemsite_item_id=item_id)
183 AND (itemsite_costcat_id=costcat_id)
184 AND (coitem_id=pitemid)
185 AND (shiphead_id=_shipheadid) );
187 SELECT (invhist_unitcost * invhist_invqty) INTO _value
189 WHERE (invhist_id=_invhistid);
191 _shipitemid := nextval('shipitem_shipitem_id_seq');
193 ( shipitem_id, shipitem_shiphead_id, shipitem_orderitem_id, shipitem_qty,
194 shipitem_transdate, shipitem_trans_username, shipitem_invoiced,
195 shipitem_value, shipitem_invhist_id )
197 ( _shipitemid, _shipheadid, pitemid, pQty,
198 _timestamp, getEffectiveXtUser(), FALSE,
200 CASE WHEN _invhistid = -1 THEN
206 -- Handle reservation
207 IF (fetchmetricbool('EnableSOReservations')) THEN
208 -- Remember what was reserved so we can re-reserve if this issue is returned
209 INSERT INTO shipitemrsrv
210 (shipitemrsrv_shipitem_id, shipitemrsrv_qty)
211 SELECT _shipitemid, least(pQty,coitem_qtyreserved)
213 WHERE ((coitem_id=pitemid)
214 AND (coitem_qtyreserved > 0));
216 -- Update sales order
218 SET coitem_qtyreserved = noNeg(coitem_qtyreserved - pQty)
219 WHERE(coitem_id=pitemid);
222 -- Calculate shipment freight
223 SELECT calcShipFreight(_shipheadid) INTO _freight;
224 UPDATE shiphead SET shiphead_freight=_freight
225 WHERE (shiphead_id=_shipheadid);
227 ELSEIF (pordertype = 'TO') THEN
229 -- Check site security
230 IF (fetchMetricBool('MultiWhs')) THEN
231 SELECT warehous_id INTO _warehouseid
232 FROM toitem, tohead, site()
233 WHERE ( (toitem_id=pitemid)
234 AND (tohead_id=toitem_tohead_id)
235 AND (warehous_id=tohead_src_warehous_id) );
242 SELECT postInvTrans( itemsite_id, 'SH', pQty, 'S/R',
243 pordertype, formatToNumber(toitem_id), '', 'Issue to Shipping',
244 costcat_shipasset_accnt_id, costcat_asset_accnt_id,
245 _itemlocSeries, _timestamp) INTO _invhistid
246 FROM tohead, toitem, itemsite, costcat
247 WHERE ((tohead_id=toitem_tohead_id)
248 AND (itemsite_item_id=toitem_item_id)
249 AND (itemsite_warehous_id=tohead_src_warehous_id)
250 AND (itemsite_costcat_id=costcat_id)
251 AND (toitem_id=pitemid) );
253 SELECT (invhist_unitcost * invhist_invqty) INTO _value
255 WHERE (invhist_id=_invhistid);
257 SELECT shiphead_id INTO _shipheadid
258 FROM shiphead, toitem JOIN tohead ON (tohead_id=toitem_tohead_id)
259 WHERE ( (toitem_id=pitemid)
260 AND (shiphead_number=getOpenShipment(pordertype, tohead_id, tohead_src_warehous_id)) );
263 _shipheadid := NEXTVAL('shiphead_shiphead_id_seq');
265 _shipnumber := fetchShipmentNumber();
266 IF (_shipnumber < 0) THEN
271 ( shiphead_id, shiphead_number, shiphead_order_id, shiphead_order_type,
273 shiphead_sfstatus, shiphead_shipvia, shiphead_shipchrg_id,
274 shiphead_freight, shiphead_freight_curr_id,
275 shiphead_shipdate, shiphead_notes, shiphead_shipform_id )
276 SELECT _shipheadid, _shipnumber, tohead_id, pordertype,
278 'N', tohead_shipvia, tohead_shipchrg_id,
279 tohead_freight + SUM(toitem_freight), tohead_freight_curr_id,
280 _timestamp::DATE, tohead_shipcomments, tohead_shipform_id
282 WHERE ((toitem_tohead_id=tohead_id)
283 AND (tohead_id IN (SELECT toitem_tohead_id
285 WHERE (toitem_id=pitemid))) )
286 GROUP BY tohead_id, tohead_shipvia, tohead_shipchrg_id, tohead_freight,
287 tohead_freight_curr_id, tohead_shipcomments, tohead_shipform_id;
291 ( shipitem_shiphead_id, shipitem_orderitem_id, shipitem_qty,
292 shipitem_transdate, shipitem_trans_username, shipitem_invoiced,
293 shipitem_value, shipitem_invhist_id )
295 ( _shipheadid, pitemid, pQty,
296 _timestamp, getEffectiveXtUser(), FALSE,
298 CASE WHEN _invhistid = -1 THEN NULL
307 RETURN _itemlocSeries;