1 CREATE OR REPLACE FUNCTION postCountTagLocation(pInvcntid INTEGER,
2 pThaw BOOLEAN) RETURNS INTEGER 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.
6 _avgCostingMethod TEXT;
11 _itemlocSeries INTEGER := 0;
20 SELECT COALESCE(fetchMetricText('CountAvgCostMethod'), 'STD') INTO _avgCostingMethod;
22 SELECT invcnt_id, invcnt_tagnumber, invcnt_qoh_after,
23 invcnt_location_id, invcnt_tagdate,
25 itemsite_id, itemsite_freeze,
27 itemsite_loccntrl, COALESCE(invcnt_location_id, -1) AS itemsite_location_id,
28 CASE WHEN (itemsite_costmethod = 'N') THEN 0
29 WHEN ( (itemsite_costmethod = 'A') AND
30 (itemsite_qtyonhand = 0.0) AND
31 (_avgCostingMethod = 'ACT') ) THEN actcost(itemsite_item_id)
32 WHEN ( (itemsite_costmethod = 'A') AND
33 (_avgCostingMethod IN ('ACT', 'AVG')) ) THEN avgcost(itemsite_id)
34 ELSE stdcost(itemsite_item_id)
35 END AS cost, itemsite_costmethod,
36 itemsite_controlmethod, itemsite_value INTO _p
37 FROM invcnt, itemsite, item
38 WHERE ( (invcnt_itemsite_id=itemsite_id)
39 AND (itemsite_item_id=item_id)
40 AND (invcnt_qoh_after IS NOT NULL)
41 AND (NOT invcnt_posted)
42 AND (invcnt_id=pInvcntid) );
47 SELECT COALESCE(SUM(itemloc_qty),0.0) INTO _origLocQty
49 WHERE ((itemloc_itemsite_id=_p.itemsite_id)
50 AND (itemloc_location_id=_p.invcnt_location_id));
55 SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;
57 IF (_p.itemsite_freeze) THEN
58 _postDate := _p.invcnt_tagdate;
60 _postDate := CURRENT_TIMESTAMP;
65 -- Post the detail indicated by cntslips
66 IF ( (_p.itemsite_loccntrl) OR
67 (_p.itemsite_controlmethod IN ('L', 'S')) ) THEN
69 SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
71 -- Adjust any existing detail to 0
72 FOR _itemloc IN SELECT itemloc_id, itemloc_location_id,
73 itemloc_ls_id, itemloc_qty
75 WHERE ((itemloc_itemsite_id=_p.itemsite_id)
76 AND (itemloc_location_id=_p.invcnt_location_id)) LOOP
80 -- Create the itemlocdist flushing records
81 INSERT INTO itemlocdist
82 ( itemlocdist_series, itemlocdist_source_type, itemlocdist_source_id,
83 itemlocdist_expiration,
84 itemlocdist_itemsite_id, itemlocdist_invhist_id, itemlocdist_flush )
86 ( _itemlocSeries, 'I', _itemloc.itemloc_id,
88 _p.itemsite_id, _invhistid, TRUE );
92 -- Clear the running detail Qty
95 -- Adjust the detail to the cntslip indicated value
96 FOR _cntslip IN SELECT cntslip_location_id, cntslip_lotserial,
97 cntslip_lotserial_expiration,
98 cntslip_lotserial_warrpurc,
99 SUM(cntslip_qty) AS qty,
101 FROM cntslip,invcnt,itemsite
102 WHERE ((cntslip_cnttag_id=pInvcntid)
103 AND (cntslip_cnttag_id=invcnt_id)
104 AND (invcnt_itemsite_id=itemsite_id))
105 GROUP BY cntslip_location_id, cntslip_lotserial, cntslip_lotserial_expiration,
106 cntslip_lotserial_warrpurc, itemsite_item_id LOOP
108 -- Handle the LotSerial
109 IF (LENGTH(_cntslip.cntslip_lotserial)>0) THEN
110 SELECT ls_id INTO _lsid
112 WHERE ((ls_item_id=_cntslip.itemsite_item_id)
113 AND (UPPER(ls_number)=UPPER(_cntslip.cntslip_lotserial)));
116 _lsid := NEXTVAL('ls_ls_id_seq');
118 VALUES (_lsid,_cntslip.itemsite_item_id,UPPER(_cntslip.cntslip_lotserial));
122 -- Track the running Qty
123 _runningQty := (_runningQty + _cntslip.qty);
126 -- Create the itemlocdist populating record
127 INSERT INTO itemlocdist
128 ( itemlocdist_series, itemlocdist_source_type, itemlocdist_source_id,
129 itemlocdist_itemsite_id,
130 itemlocdist_ls_id, itemlocdist_expiration, itemlocdist_warranty,
131 itemlocdist_qty, itemlocdist_invhist_id )
133 ( _itemlocSeries, 'L', _cntslip.cntslip_location_id,
135 _lsid, COALESCE(_cntslip.cntslip_lotserial_expiration, endOfTime()),
136 _cntslip.cntslip_lotserial_warrpurc,
137 _cntslip.qty, _invhistid );
141 IF (_runningQty > _p.invcnt_qoh_after) THEN
142 -- The total Count Slip Qty is greater than the Count Tag Qty,
143 -- Don't post the Count.
146 ELSIF ( (_runningQty < _p.invcnt_qoh_after) AND
147 (_p.itemsite_controlmethod IN ('L', 'S')) ) THEN
148 -- The total Count Slip Qty is less than the Count Tag Qty,
149 -- and the Item Site is Lot/Serial controlled.
150 -- Don't post the Count.
153 ELSIF (_runningQty < _p.invcnt_qoh_after) THEN
154 IF ( (NOT _p.itemsite_loccntrl) OR
155 (_p.itemsite_location_id = -1) ) THEN
156 -- The total Count Slip Qty is less than the Count Tag Qty,
157 -- and there isn't a default location to post into.
158 -- Don't post the Count.
161 ELSIF ( SELECT (metric_value='f')
163 WHERE (metric_name='PostCountTagToDefault') ) THEN
164 -- The total Count Slip Qty is less than the Count Tag Qty,
165 -- and we don't post Count Tags to default Locations
166 -- Don't post the Count.
170 -- Distribute the remaining qty into the default location.
171 INSERT INTO itemlocdist
172 ( itemlocdist_series, itemlocdist_source_type, itemlocdist_source_id,
173 itemlocdist_itemsite_id,
174 itemlocdist_ls_id, itemlocdist_expiration,
175 itemlocdist_qty, itemlocdist_invhist_id )
176 SELECT _itemlocSeries, 'L', _p.itemsite_location_id,
179 (_p.invcnt_qoh_after - _runningQty), _invhistid;
185 -- The Count Slip Qty. must equal the Count Tag Qty.
189 -- If we shouldn't post the count then delete the itemlocdist records,
190 -- and return with the error.
191 IF (_errorCode <> 0) THEN
192 DELETE FROM itemlocdist
193 WHERE (itemlocdist_series=_itemlocSeries);
200 -- Mod. the Count Tag.
202 SET invcnt_qoh_before=_origLocQty,
203 invcnt_postdate=_postDate,
205 invcnt_invhist_id=_invhistid,
206 invcnt_post_username=getEffectiveXtUser()
207 WHERE (invcnt_id=pInvcntid);
209 -- Create the CC transaction
211 ( invhist_id, invhist_itemsite_id,
212 invhist_transdate, invhist_transtype, invhist_invqty,
213 invhist_qoh_before, invhist_qoh_after,
214 invhist_docnumber, invhist_comments,
215 invhist_invuom, invhist_unitcost, invhist_hasdetail,
216 invhist_costmethod, invhist_value_before, invhist_value_after,
218 SELECT _invhistid, itemsite_id,
219 _postDate, 'CC', (invcnt_qoh_after - invcnt_qoh_before),
220 invcnt_qoh_before, invcnt_qoh_after,
221 invcnt_tagnumber, invcnt_comments,
222 uom_name, _p.cost, _hasDetail,
223 _p.itemsite_costmethod, _p.itemsite_value,
224 _p.itemsite_value + (_p.cost * (invcnt_qoh_after - invcnt_qoh_before)),
226 FROM itemsite, invcnt, item, uom
227 WHERE ( (invcnt_itemsite_id=itemsite_id)
228 AND (itemsite_item_id=item_id)
229 AND (item_inv_uom_id=uom_id)
230 AND (itemsite_controlmethod <> 'N')
231 AND (invcnt_id=pInvcntid) );
235 SET itemsite_qtyonhand= itemsite_qtyonhand + (_p.invcnt_qoh_after - _origLocQty),
236 itemsite_datelastcount=_postDate
237 WHERE (itemsite_id=_p.itemsite_id);
239 SET itemsite_value = itemsite_qtyonhand * _p.cost
240 WHERE (itemsite_id=_p.itemsite_id);
242 -- Post the detail, if any
244 PERFORM distributeItemlocSeries(_itemlocSeries);
247 -- Thaw the itemsite if it's frozen
249 PERFORM thawItemSite(invcnt_itemsite_id)
251 WHERE (invcnt_id=pInvcntid);
255 PERFORM insertGLTransaction( 'I/M', 'CT', _p.invcnt_tagnumber,
256 ('Post Count Tag #' || _p.invcnt_tagnumber || ' for Item ' || _p.item_number),
257 costcat_adjustment_accnt_id, costcat_asset_accnt_id, _invhistid,
258 ( (_p.invcnt_qoh_after - _origLocQty) * _p.cost), CURRENT_DATE )
259 FROM invcnt, itemsite, costcat
260 WHERE ( (invcnt_itemsite_id=itemsite_id)
261 AND (itemsite_costcat_id=costcat_id)
262 AND (invcnt_id=pInvcntid) );