1 CREATE OR REPLACE FUNCTION balanceItemsite(INTEGER) 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 pItemsiteid ALIAS FOR $1;
6 _itemlocseries INTEGER;
13 -- Make sure that that passed Itemsite is MLC or Lot/Serial controlled
14 IF ( ( SELECT (NOT ( (itemsite_loccntrl) OR (itemsite_controlmethod IN ('L', 'S')) ))
16 WHERE (itemsite_id=pItemsiteid) ) ) THEN
20 IF ( ( SELECT itemsite_freeze
22 WHERE(itemsite_id=pItemsiteid) ) ) THEN
26 -- Calculate the Netable portion
27 SELECT COALESCE(SUM(itemloc_qty), 0) INTO _balanced
28 FROM itemloc LEFT OUTER JOIN location ON (itemloc_location_id=location_id)
29 WHERE ( ( (location_id IS NULL) OR (location_netable) )
30 AND (itemloc_itemsite_id=pItemsiteid) );
32 -- Post an AD Transaction for the Netable portion
33 SELECT invAdjustment( itemsite_id, (_balanced - itemsite_qtyonhand),
34 'Balance', 'Inventory Balance' ) INTO _itemlocseries
36 WHERE (itemsite_id=pItemsiteid);
38 -- Post the itemloc series which will postIntoTrialBalance and postInvHist
39 PERFORM postItemlocSeries(_itemlocseries);
41 -- Kill the resultant distribution records
42 DELETE FROM itemlocdist
43 WHERE (itemlocdist_series=_itemlocseries);
45 -- Calculate and write the Non-Netable portion directly
46 SELECT COALESCE(SUM(itemloc_qty), 0) INTO _nnQoh
47 FROM itemloc, location
48 WHERE ( (itemloc_location_id=location_id)
49 AND (NOT location_netable)
50 AND (itemloc_itemsite_id=pItemsiteid) );
53 SET itemsite_nnqoh = _nnQoh
54 WHERE (itemsite_id=pItemsiteid);
59 $$ LANGUAGE 'plpgsql';