1 SELECT dropIfExists('FUNCTION', 'postCountTag(integer, boolean, text)', 'public');
3 CREATE OR REPLACE FUNCTION postCountTag(pInvcntid INTEGER,
4 pThaw BOOLEAN) RETURNS INTEGER AS $$
5 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
6 -- See www.xtuple.com/CPAL for the full text of the software license.
8 _avgCostingMethod TEXT;
13 _itemlocSeries INTEGER := 0;
22 SELECT COALESCE(fetchMetricText('CountAvgCostMethod'), 'STD') INTO _avgCostingMethod;
24 SELECT invcnt_id, invcnt_tagnumber, invcnt_qoh_after,
27 itemsite_id, itemsite_freeze,
29 itemsite_loccntrl, itemsite_location_id,
30 CASE WHEN (itemsite_costmethod = 'N') THEN 0
31 WHEN ( (itemsite_costmethod = 'A') AND
32 (itemsite_qtyonhand = 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,
39 itemsite_value INTO _p
40 FROM invcnt, itemsite, item
41 WHERE ( (invcnt_itemsite_id=itemsite_id)
42 AND (itemsite_item_id=item_id)
43 AND (invcnt_qoh_after IS NOT NULL)
44 AND (NOT invcnt_posted)
45 AND (invcnt_id=pInvcntid) );
47 -- If the invcnt_location_id is not null then
48 -- call a separate function so as not to affect
49 -- the existing functionality.
50 IF (_p.invcnt_location_id IS NOT NULL) THEN
51 RETURN postCountTagLocation(pInvcntid, pThaw);
54 SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;
56 IF (_p.itemsite_freeze) THEN
57 SELECT invcnt_tagdate INTO _postDate
59 WHERE (invcnt_id=pInvcntid) ;
61 _postDate = CURRENT_TIMESTAMP;
66 -- Post the detail indicated by cntslips
67 IF ( (_p.itemsite_loccntrl) OR
68 (_p.itemsite_controlmethod IN ('L', 'S')) ) THEN
70 SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
72 -- Adjust any existing detail to 0
73 FOR _itemloc IN SELECT itemloc_id, itemloc_location_id,
74 itemloc_ls_id, itemloc_qty
76 WHERE (itemloc_itemsite_id=_p.itemsite_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,
106 cntslip_lotserial_expiration, 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,_cntslip.qty, _invhistid );
140 IF (_runningQty > _p.invcnt_qoh_after) THEN
141 -- The total Count Slip Qty is greater than the Count Tag Qty,
142 -- Don't post the Count.
145 ELSIF ( (_runningQty < _p.invcnt_qoh_after) AND
146 (_p.itemsite_controlmethod IN ('L', 'S')) ) THEN
147 -- The total Count Slip Qty is less than the Count Tag Qty,
148 -- and the Item Site is Lot/Serial controlled.
149 -- Don't post the Count.
152 ELSIF (_runningQty < _p.invcnt_qoh_after) THEN
153 IF ( (NOT _p.itemsite_loccntrl) OR
154 (_p.itemsite_location_id = -1) ) THEN
155 -- The total Count Slip Qty is less than the Count Tag Qty,
156 -- and there isn't a default location to post into.
157 -- Don't post the Count.
160 ELSIF ( SELECT (metric_value='f')
162 WHERE (metric_name='PostCountTagToDefault') ) THEN
163 -- The total Count Slip Qty is less than the Count Tag Qty,
164 -- and we don't post Count Tags to default Locations
165 -- Don't post the Count.
169 -- Distribute the remaining qty into the default location.
170 INSERT INTO itemlocdist
171 ( itemlocdist_series, itemlocdist_source_type, itemlocdist_source_id,
172 itemlocdist_itemsite_id,
173 itemlocdist_expiration,
174 itemlocdist_qty, itemlocdist_invhist_id )
175 SELECT _itemlocSeries, 'L', _p.itemsite_location_id,
178 (_p.invcnt_qoh_after - _runningQty), _invhistid;
184 -- The Count Slip Qty. must equal the Count Tag Qty.
188 -- If we shouldn't post the count then delete the itemlocdist records,
189 -- and return with the error.
190 IF (_errorCode <> 0) THEN
191 DELETE FROM itemlocdist
192 WHERE (itemlocdist_series=_itemlocSeries);
199 -- Mod. the Count Tag.
201 SET invcnt_qoh_before=_p.itemsite_qtyonhand,
202 invcnt_postdate=_postDate,
204 invcnt_invhist_id=_invhistid,
205 invcnt_post_username=getEffectiveXtUser()
206 WHERE (invcnt_id=pInvcntid);
208 -- Create the CC transaction
210 ( invhist_id, invhist_itemsite_id,
211 invhist_transdate, invhist_transtype, invhist_invqty,
212 invhist_qoh_before, invhist_qoh_after,
213 invhist_docnumber, invhist_comments,
214 invhist_invuom, invhist_unitcost, invhist_hasdetail,
215 invhist_costmethod, invhist_value_before, invhist_value_after,
217 SELECT _invhistid, itemsite_id,
218 _postDate, 'CC', (invcnt_qoh_after - invcnt_qoh_before),
219 invcnt_qoh_before, invcnt_qoh_after,
220 invcnt_tagnumber, invcnt_comments,
221 uom_name, _p.cost, _hasDetail,
222 _p.itemsite_costmethod, _p.itemsite_value,
223 _p.itemsite_value + (_p.cost * (invcnt_qoh_after - invcnt_qoh_before)),
225 FROM itemsite, invcnt, item, uom
226 WHERE ( (invcnt_itemsite_id=itemsite_id)
227 AND (itemsite_item_id=item_id)
228 AND (item_inv_uom_id=uom_id)
229 AND (itemsite_controlmethod <> 'N')
230 AND (invcnt_id=pInvcntid) );
232 IF ( SELECT metric_value
234 WHERE ((metric_name = 'EnableAsOfQOH')
235 AND (metric_value = 't'))) THEN
236 IF (NOT postIntoInvBalance(_invhistid)) THEN
237 RAISE EXCEPTION 'Post into Inventory Balance for invhist_id=% was unsuccessful',_invhistid;
242 -- Avoid negative value when average cost item
244 SET itemsite_qtyonhand=_p.invcnt_qoh_after,
245 itemsite_value = CASE WHEN ((itemsite_costmethod='A') AND
246 (_p.itemsite_value + (_p.cost * (_p.invcnt_qoh_after - itemsite_qtyonhand))) < 0.0) THEN 0.0
247 ELSE (_p.itemsite_value + (_p.cost * (_p.invcnt_qoh_after - itemsite_qtyonhand)))
249 itemsite_datelastcount=_postDate
250 WHERE (itemsite_id=_p.itemsite_id);
252 -- Post the detail, if any
254 PERFORM distributeItemlocSeries(_itemlocSeries);
257 -- Thaw the itemsite if it's frozen
259 PERFORM thawItemSite(invcnt_itemsite_id)
261 WHERE (invcnt_id=pInvcntid);
265 PERFORM insertGLTransaction( 'I/M', 'CT', _p.invcnt_tagnumber,
266 ('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) );