1 SELECT dropIfExists('FUNCTION', 'postCountTag(integer, boolean, text)', 'public');
3 CREATE OR REPLACE FUNCTION postCountTag(INTEGER, BOOLEAN) RETURNS INTEGER AS $$
4 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
5 -- See www.xtuple.com/CPAL for the full text of the software license.
7 pInvcntid ALIAS FOR $1;
9 _avgCostingMethod TEXT;
14 _itemlocSeries INTEGER := 0;
23 SELECT COALESCE(fetchMetricText('CountAvgCostMethod'), 'STD') INTO _avgCostingMethod;
25 SELECT invcnt_id, invcnt_tagnumber, invcnt_qoh_after,
28 itemsite_id, itemsite_freeze,
30 itemsite_loccntrl, itemsite_location_id,
31 CASE WHEN (itemsite_costmethod = 'N') THEN 0
32 WHEN ( (itemsite_costmethod = 'A') AND
33 (itemsite_qtyonhand = 0) AND
34 (_avgCostingMethod = 'ACT') ) THEN actcost(itemsite_item_id)
35 WHEN ( (itemsite_costmethod = 'A') AND
36 (_avgCostingMethod IN ('ACT', 'AVG')) ) THEN avgcost(itemsite_id)
37 ELSE stdcost(itemsite_item_id)
38 END AS cost, itemsite_costmethod,
39 itemsite_controlmethod,
40 itemsite_value INTO _p
41 FROM invcnt, itemsite, item
42 WHERE ( (invcnt_itemsite_id=itemsite_id)
43 AND (itemsite_item_id=item_id)
44 AND (invcnt_qoh_after IS NOT NULL)
45 AND (NOT invcnt_posted)
46 AND (invcnt_id=pInvcntid) );
48 -- If the invcnt_location_id is not null then
49 -- call a separate function so as not to affect
50 -- the existing functionality.
51 IF (_p.invcnt_location_id IS NOT NULL) THEN
52 RETURN postCountTagLocation(pInvcntid, pThaw);
55 SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;
57 IF (_p.itemsite_freeze) THEN
58 SELECT invcnt_tagdate INTO _postDate
60 WHERE (invcnt_id=pInvcntid) ;
62 _postDate = CURRENT_TIMESTAMP;
67 -- Post the detail indicated by cntslips
68 IF ( (_p.itemsite_loccntrl) OR
69 (_p.itemsite_controlmethod IN ('L', 'S')) ) THEN
71 SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
73 -- Adjust any existing detail to 0
74 FOR _itemloc IN SELECT itemloc_id, itemloc_location_id,
75 itemloc_ls_id, itemloc_qty
77 WHERE (itemloc_itemsite_id=_p.itemsite_id) LOOP
81 -- Create the itemlocdist flushing records
82 INSERT INTO itemlocdist
83 ( itemlocdist_series, itemlocdist_source_type, itemlocdist_source_id,
84 itemlocdist_expiration,
85 itemlocdist_itemsite_id, itemlocdist_invhist_id, itemlocdist_flush )
87 ( _itemlocSeries, 'I', _itemloc.itemloc_id,
89 _p.itemsite_id, _invhistid, TRUE );
93 -- Clear the running detail Qty
96 -- Adjust the detail to the cntslip indicated value
97 FOR _cntslip IN SELECT cntslip_location_id, cntslip_lotserial,
98 cntslip_lotserial_expiration,
99 cntslip_lotserial_warrpurc,
100 SUM(cntslip_qty) AS qty,
102 FROM cntslip, invcnt, itemsite
103 WHERE ((cntslip_cnttag_id=pInvcntid)
104 AND (cntslip_cnttag_id=invcnt_id)
105 AND (invcnt_itemsite_id=itemsite_id))
106 GROUP BY cntslip_location_id, cntslip_lotserial,
107 cntslip_lotserial_expiration, cntslip_lotserial_warrpurc,itemsite_item_id LOOP
109 -- Handle the LotSerial
110 IF (LENGTH(_cntslip.cntslip_lotserial)>0) THEN
111 SELECT ls_id INTO _lsid
113 WHERE ((ls_item_id=_cntslip.itemsite_item_id)
114 AND (UPPER(ls_number)=UPPER(_cntslip.cntslip_lotserial)));
117 _lsid := NEXTVAL('ls_ls_id_seq');
119 VALUES (_lsid,_cntslip.itemsite_item_id,UPPER(_cntslip.cntslip_lotserial));
123 -- Track the running Qty
124 _runningQty := (_runningQty + _cntslip.qty);
127 -- Create the itemlocdist populating record
128 INSERT INTO itemlocdist
129 ( itemlocdist_series, itemlocdist_source_type, itemlocdist_source_id,
130 itemlocdist_itemsite_id,
131 itemlocdist_ls_id, itemlocdist_expiration, itemlocdist_warranty,
132 itemlocdist_qty, itemlocdist_invhist_id )
134 ( _itemlocSeries, 'L', _cntslip.cntslip_location_id,
136 _lsid, COALESCE(_cntslip.cntslip_lotserial_expiration, endOfTime()),
137 _cntslip.cntslip_lotserial_warrpurc,_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_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=_p.itemsite_qtyonhand,
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) );
233 IF ( SELECT metric_value
235 WHERE ((metric_name = 'EnableAsOfQOH')
236 AND (metric_value = 't'))) THEN
237 IF (NOT postIntoInvBalance(_invhistid)) THEN
238 RAISE EXCEPTION 'Post into Inventory Balance for invhist_id=% was unsuccessful',_invhistid;
243 -- Avoid negative value when average cost item
245 SET itemsite_qtyonhand=_p.invcnt_qoh_after,
247 itemsite_value = CASE WHEN ((itemsite_costmethod='A') AND (_p.itemsite_value + (_p.cost * (_p.invcnt_qoh_after - itemsite_qtyonhand))) < 0.0) THEN 0.0
248 ELSE (_p.itemsite_value + (_p.cost * (_p.invcnt_qoh_after - itemsite_qtyonhand)))
250 itemsite_datelastcount=_postDate
251 WHERE (itemsite_id=_p.itemsite_id);
253 -- Post the detail, if any
255 PERFORM distributeItemlocSeries(_itemlocSeries);
258 -- Thaw the itemsite if it's frozen
260 PERFORM thawItemSite(invcnt_itemsite_id)
262 WHERE (invcnt_id=pInvcntid);
266 PERFORM insertGLTransaction( 'I/M', 'CT', _p.invcnt_tagnumber, ('Post Count Tag #' || _p.invcnt_tagnumber || ' for Item ' || _p.item_number),
267 costcat_adjustment_accnt_id, costcat_asset_accnt_id, _invhistid,
268 ( (_p.invcnt_qoh_after - _p.itemsite_qtyonhand) * _p.cost), _postDate::DATE )
269 FROM invcnt, itemsite, costcat
270 WHERE ( (invcnt_itemsite_id=itemsite_id)
271 AND (itemsite_costcat_id=costcat_id)
272 AND (invcnt_id=pInvcntid) );
281 $$ LANGUAGE 'plpgsql';