1 CREATE OR REPLACE FUNCTION postCountTagLocation(INTEGER, BOOLEAN) 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 pInvcntid ALIAS FOR $1;
7 _avgCostingMethod TEXT;
12 _itemlocSeries INTEGER := 0;
22 SELECT COALESCE(fetchMetricText('CountAvgCostMethod'), 'STD') INTO _avgCostingMethod;
24 SELECT invcnt_id, invcnt_tagnumber, invcnt_qoh_after,
25 invcnt_location_id, invcnt_tagdate,
27 itemsite_id, itemsite_freeze,
29 itemsite_loccntrl, COALESCE(invcnt_location_id, -1) AS itemsite_location_id,
30 CASE WHEN (itemsite_costmethod = 'N') THEN 0
31 WHEN ( (itemsite_costmethod = 'A') AND
32 ((itemsite_qtyonhand + itemsite_nnqoh) = 0) AND
33 (_avgCostingMethod = 'ACT') ) THEN actcost(itemsite_item_id)
34 WHEN ( (itemsite_costmethod = 'A') AND
35 (_avgCostingMethod IN ('ACT', 'AVG')) ) THEN avgcost(itemsite_id)
36 ELSE stdcost(itemsite_item_id)
37 END AS cost, itemsite_costmethod,
38 itemsite_controlmethod, itemsite_value INTO _p
39 FROM invcnt, itemsite, item
40 WHERE ( (invcnt_itemsite_id=itemsite_id)
41 AND (itemsite_item_id=item_id)
42 AND (invcnt_qoh_after IS NOT NULL)
43 AND (NOT invcnt_posted)
44 AND (invcnt_id=pInvcntid) );
49 SELECT COALESCE(SUM(itemloc_qty),0.0), location_netable INTO _origLocQty,_netable
51 WHERE ((itemloc_itemsite_id=_p.itemsite_id)
52 AND (location_id=itemloc_location_id)
53 AND (itemloc_location_id=_p.invcnt_location_id))
54 GROUP BY location_netable;
60 SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;
62 IF (_p.itemsite_freeze) THEN
63 _postDate := _p.invcnt_tagdate;
65 _postDate := CURRENT_TIMESTAMP;
70 -- Post the detail indicated by cntslips
71 IF ( (_p.itemsite_loccntrl) OR
72 (_p.itemsite_controlmethod IN ('L', 'S')) ) THEN
74 SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
76 -- Adjust any existing detail to 0
77 FOR _itemloc IN SELECT itemloc_id, itemloc_location_id,
78 itemloc_ls_id, itemloc_qty
80 WHERE ((itemloc_itemsite_id=_p.itemsite_id)
81 AND (itemloc_location_id=_p.invcnt_location_id)) LOOP
85 -- Create the itemlocdist flushing records
86 INSERT INTO itemlocdist
87 ( itemlocdist_series, itemlocdist_source_type, itemlocdist_source_id,
88 itemlocdist_expiration,
89 itemlocdist_itemsite_id, itemlocdist_invhist_id, itemlocdist_flush )
91 ( _itemlocSeries, 'I', _itemloc.itemloc_id,
93 _p.itemsite_id, _invhistid, TRUE );
97 -- Clear the running detail Qty
100 -- Adjust the detail to the cntslip indicated value
101 FOR _cntslip IN SELECT cntslip_location_id, cntslip_lotserial,
102 cntslip_lotserial_expiration,
103 cntslip_lotserial_warrpurc,
104 SUM(cntslip_qty) AS qty,
106 FROM cntslip,invcnt,itemsite
107 WHERE ((cntslip_cnttag_id=pInvcntid)
108 AND (cntslip_cnttag_id=invcnt_id)
109 AND (invcnt_itemsite_id=itemsite_id))
110 GROUP BY cntslip_location_id, cntslip_lotserial, cntslip_lotserial_expiration,
111 cntslip_lotserial_warrpurc, itemsite_item_id LOOP
113 -- Handle the LotSerial
114 IF (LENGTH(_cntslip.cntslip_lotserial)>0) THEN
115 SELECT ls_id INTO _lsid
117 WHERE ((ls_item_id=_cntslip.itemsite_item_id)
118 AND (UPPER(ls_number)=UPPER(_cntslip.cntslip_lotserial)));
121 _lsid := NEXTVAL('ls_ls_id_seq');
123 VALUES (_lsid,_cntslip.itemsite_item_id,UPPER(_cntslip.cntslip_lotserial));
127 -- Track the running Qty
128 _runningQty := (_runningQty + _cntslip.qty);
131 -- Create the itemlocdist populating record
132 INSERT INTO itemlocdist
133 ( itemlocdist_series, itemlocdist_source_type, itemlocdist_source_id,
134 itemlocdist_itemsite_id,
135 itemlocdist_ls_id, itemlocdist_expiration, itemlocdist_warranty,
136 itemlocdist_qty, itemlocdist_invhist_id )
138 ( _itemlocSeries, 'L', _cntslip.cntslip_location_id,
140 _lsid, COALESCE(_cntslip.cntslip_lotserial_expiration, endOfTime()),
141 _cntslip.cntslip_lotserial_warrpurc,
142 _cntslip.qty, _invhistid );
146 IF (_runningQty > _p.invcnt_qoh_after) THEN
147 -- The total Count Slip Qty is greater than the Count Tag Qty,
148 -- Don't post the Count.
151 ELSIF ( (_runningQty < _p.invcnt_qoh_after) AND
152 (_p.itemsite_controlmethod IN ('L', 'S')) ) THEN
153 -- The total Count Slip Qty is less than the Count Tag Qty,
154 -- and the Item Site is Lot/Serial controlled.
155 -- Don't post the Count.
158 ELSIF (_runningQty < _p.invcnt_qoh_after) THEN
159 IF ( (NOT _p.itemsite_loccntrl) OR
160 (_p.itemsite_location_id = -1) ) THEN
161 -- The total Count Slip Qty is less than the Count Tag Qty,
162 -- and there isn't a default location to post into.
163 -- Don't post the Count.
166 ELSIF ( SELECT (metric_value='f')
168 WHERE (metric_name='PostCountTagToDefault') ) THEN
169 -- The total Count Slip Qty is less than the Count Tag Qty,
170 -- and we don't post Count Tags to default Locations
171 -- Don't post the Count.
175 -- Distribute the remaining qty into the default location.
176 INSERT INTO itemlocdist
177 ( itemlocdist_series, itemlocdist_source_type, itemlocdist_source_id,
178 itemlocdist_itemsite_id,
179 itemlocdist_ls_id, itemlocdist_expiration,
180 itemlocdist_qty, itemlocdist_invhist_id )
181 SELECT _itemlocSeries, 'L', _p.itemsite_location_id,
184 (_p.invcnt_qoh_after - _runningQty), _invhistid;
190 -- The Count Slip Qty. must equal the Count Tag Qty.
194 -- If we shouldn't post the count then delete the itemlocdist records,
195 -- and return with the error.
196 IF (_errorCode <> 0) THEN
197 DELETE FROM itemlocdist
198 WHERE (itemlocdist_series=_itemlocSeries);
205 -- Mod. the Count Tag.
207 SET invcnt_qoh_before=_origLocQty,
208 invcnt_postdate=_postDate,
210 invcnt_invhist_id=_invhistid,
211 invcnt_post_username=getEffectiveXtUser()
212 WHERE (invcnt_id=pInvcntid);
214 -- Create the CC transaction
216 ( invhist_id, invhist_itemsite_id,
217 invhist_transdate, invhist_transtype, invhist_invqty,
218 invhist_qoh_before, invhist_qoh_after,
219 invhist_docnumber, invhist_comments,
220 invhist_invuom, invhist_unitcost, invhist_hasdetail,
221 invhist_costmethod, invhist_value_before, invhist_value_after,
223 SELECT _invhistid, itemsite_id,
224 _postDate, 'CC', (invcnt_qoh_after - invcnt_qoh_before),
225 invcnt_qoh_before, invcnt_qoh_after,
226 invcnt_tagnumber, invcnt_comments,
227 uom_name, _p.cost, _hasDetail,
228 _p.itemsite_costmethod, _p.itemsite_value,
229 _p.itemsite_value + (_p.cost * (invcnt_qoh_after - invcnt_qoh_before)),
231 FROM itemsite, invcnt, item, uom
232 WHERE ( (invcnt_itemsite_id=itemsite_id)
233 AND (itemsite_item_id=item_id)
234 AND (item_inv_uom_id=uom_id)
235 AND (itemsite_controlmethod <> 'N')
236 AND (invcnt_id=pInvcntid) );
241 SET itemsite_qtyonhand= itemsite_qtyonhand + (_p.invcnt_qoh_after - _origLocQty),
242 itemsite_datelastcount=_postDate
243 WHERE (itemsite_id=_p.itemsite_id);
245 SET itemsite_value = (itemsite_qtyonhand + itemsite_nnqoh) * _p.cost
246 WHERE (itemsite_id=_p.itemsite_id);
249 SET itemsite_nnqoh = itemsite_nnqoh + (_p.invcnt_qoh_after - _origLocQty),
250 itemsite_datelastcount=_postDate
251 WHERE (itemsite_id=_p.itemsite_id);
253 SET itemsite_value = (itemsite_qtyonhand + itemsite_nnqoh) * _p.cost
254 WHERE (itemsite_id=_p.itemsite_id);
257 -- Post the detail, if any
259 PERFORM distributeItemlocSeries(_itemlocSeries);
262 -- Thaw the itemsite if it's frozen
264 PERFORM thawItemSite(invcnt_itemsite_id)
266 WHERE (invcnt_id=pInvcntid);
270 PERFORM insertGLTransaction( 'I/M', 'CT', _p.invcnt_tagnumber, ('Post Count Tag #' || _p.invcnt_tagnumber || ' for Item ' || _p.item_number),
271 costcat_adjustment_accnt_id, costcat_asset_accnt_id, _invhistid,
272 ( (_p.invcnt_qoh_after - _origLocQty) * _p.cost), CURRENT_DATE )
273 FROM invcnt, itemsite, costcat
274 WHERE ( (invcnt_itemsite_id=itemsite_id)
275 AND (itemsite_costcat_id=costcat_id)
276 AND (invcnt_id=pInvcntid) );
280 $$ LANGUAGE 'plpgsql';