"public/functions/purgepostedcounttags.sql",
"public/functions/purgeshipments.sql",
"public/functions/qtyallocated.sql",
+ "public/functions/qtyatlocation.sql",
"public/functions/qtyatshipping.sql",
"public/functions/qtyavailable.sql",
"public/functions/qtyinshipment.sql",
"public/functions/qtylocation.sql",
+ "public/functions/qtynetable.sql",
"public/functions/qtyordered.sql",
"public/functions/qtypr.sql",
"public/functions/qtyreserved.sql",
DECLARE
_value NUMERIC;
_qoh NUMERIC;
- _qohnn NUMERIC;
BEGIN
- SELECT itemsite_value, itemsite_qtyonhand, itemsite_nnqoh
- INTO _value, _qoh, _qohnn
+ SELECT itemsite_value, itemsite_qtyonhand
+ INTO _value, _qoh
FROM itemsite
WHERE(itemsite_id=pItemsiteid);
- IF (_qoh = 0.0 AND _qohnn = 0.0) THEN
+ IF (_qoh = 0.0) THEN
RETURN 0.0;
END IF;
- RETURN _value / (_qoh + _qohnn);
+ RETURN _value / _qoh;
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION balanceItemsite(INTEGER) RETURNS INTEGER AS $$
+CREATE OR REPLACE FUNCTION balanceItemsite(pItemsiteid INTEGER) RETURNS INTEGER AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pItemsiteid ALIAS FOR $1;
_itemlocseries INTEGER;
_balanced NUMERIC;
_qoh NUMERIC;
- _nnQoh NUMERIC;
BEGIN
RETURN -1;
END IF;
--- Calculate the Netable portion
+-- Calculate the qoh
SELECT COALESCE(SUM(itemloc_qty), 0) INTO _balanced
- FROM itemloc LEFT OUTER JOIN location ON (itemloc_location_id=location_id)
- WHERE ( ( (location_id IS NULL) OR (location_netable) )
- AND (itemloc_itemsite_id=pItemsiteid) );
+ FROM itemloc
+ WHERE (itemloc_itemsite_id=pItemsiteid);
--- Post an AD Transaction for the Netable portion
+-- Post an AD Transaction
SELECT invAdjustment( itemsite_id, (_balanced - itemsite_qtyonhand),
'Balance', 'Inventory Balance' ) INTO _itemlocseries
FROM itemsite
DELETE FROM itemlocdist
WHERE (itemlocdist_series=_itemlocseries);
--- Calculate and write the Non-Netable portion directly
- SELECT COALESCE(SUM(itemloc_qty), 0) INTO _nnQoh
- FROM itemloc, location
- WHERE ( (itemloc_location_id=location_id)
- AND (NOT location_netable)
- AND (itemloc_itemsite_id=pItemsiteid) );
-
- UPDATE itemsite
- SET itemsite_nnqoh = _nnQoh
- WHERE (itemsite_id=pItemsiteid);
-
RETURN 1;
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
\ No newline at end of file
-CREATE OR REPLACE FUNCTION copyItemSite(pitemsiteid INTEGER,
- pdestwhsid INTEGER) RETURNS INTEGER AS $$
+CREATE OR REPLACE FUNCTION copyItemSite(pItemsiteid INTEGER,
+ pDestWhsid INTEGER) RETURNS INTEGER AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
- RETURN copyItemSite(pitemsiteid, pdestwhsid, NULL);
+ RETURN copyItemSite(pItemsiteid, pDestWhsid, NULL);
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION copyItemSite(pitemsiteid INTEGER,
- pdestwhsid INTEGER,
- pdestitemid INTEGER) RETURNS INTEGER AS $$
+CREATE OR REPLACE FUNCTION copyItemSite(pItemsiteid INTEGER,
+ pDestWhsid INTEGER,
+ pDestItemid INTEGER) RETURNS INTEGER AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pitemsiteid ALIAS FOR $1;
- pdestwhsid ALIAS FOR $2;
_destwhs whsinfo%ROWTYPE;
_new itemsite%ROWTYPE;
_supplywarehousid INTEGER := NULL;
-- make a copy of the old itemsite
SELECT * INTO _new
FROM itemsite
- WHERE (itemsite_id=pitemsiteid);
+ WHERE (itemsite_id=pItemsiteid);
IF (NOT FOUND) THEN
RETURN -1;
END IF;
-- if there is no dest warehouse then perhaps the user is manually copying it
- IF (pdestwhsid IS NOT NULL) THEN
+ IF (pDestWhsid IS NOT NULL) THEN
SELECT * INTO _destwhs
FROM whsinfo
- WHERE (warehous_id=pdestwhsid);
+ WHERE (warehous_id=pDestWhsid);
IF (NOT FOUND) THEN
RETURN -2;
END IF;
SELECT itemsite_id INTO _new.itemsite_id
FROM itemsite
- WHERE ((itemsite_item_id=COALESCE(pdestitemid, _new.itemsite_item_id))
- AND (itemsite_warehous_id=pdestwhsid OR
- (itemsite_warehous_id IS NULL AND pdestwhsid IS NULL)));
+ WHERE ((itemsite_item_id=COALESCE(pDestItemid, _new.itemsite_item_id))
+ AND (itemsite_warehous_id=pDestWhsid OR
+ (itemsite_warehous_id IS NULL AND pDestWhsid IS NULL)));
IF (FOUND) THEN
RETURN _new.itemsite_id;
END IF;
SELECT itemsite_id INTO _new.itemsite_supply_itemsite_id
FROM itemsite
WHERE (itemsite_warehous_id=_supplywarehousid)
- AND (itemsite_item_id=pdestitemid);
+ AND (itemsite_item_id=pDestItemid);
END IF;
END IF;
-- now override the things we know have to change
_new.itemsite_id := NEXTVAL('itemsite_itemsite_id_seq');
- _new.itemsite_warehous_id := pdestwhsid;
+ _new.itemsite_warehous_id := pDestWhsid;
_new.itemsite_qtyonhand := 0;
_new.itemsite_value := 0;
_new.itemsite_datelastcount := NULL;
_new.itemsite_datelastused := NULL;
- _new.itemsite_nnqoh := 0;
_new.itemsite_location_id := -1;
_new.itemsite_recvlocation_id := -1;
_new.itemsite_issuelocation_id := -1;
_new.itemsite_location := NULL;
_new.itemsite_location_comments := NULL;
_new.itemsite_notes := 'Transit Warehouse';
- _new.itemsite_nnqoh := 0;
_new.itemsite_createwo := FALSE;
_new.itemsite_costcat_id := _destwhs.warehous_costcat_id;
_new.itemsite_supply_itemsite_id := NULL;
itemsite_soldranking, itemsite_createpr,
itemsite_location, itemsite_location_comments,
itemsite_notes, itemsite_perishable,
- itemsite_nnqoh, itemsite_autoabcclass,
+ itemsite_autoabcclass,
itemsite_ordergroup, itemsite_disallowblankwip,
itemsite_maxordqty, itemsite_mps_timefence,
itemsite_createwo, itemsite_warrpurc,
itemsite_location_dist, itemsite_recvlocation_dist,
itemsite_issuelocation_dist
) VALUES (
- _new.itemsite_id, COALESCE(pdestitemid, _new.itemsite_item_id),
+ _new.itemsite_id, COALESCE(pDestItemid, _new.itemsite_item_id),
_new.itemsite_warehous_id, _new.itemsite_qtyonhand,
_new.itemsite_costmethod, _new.itemsite_value,
_new.itemsite_reorderlevel, _new.itemsite_ordertoqty,
_new.itemsite_soldranking, _new.itemsite_createpr,
_new.itemsite_location, _new.itemsite_location_comments,
_new.itemsite_notes, _new.itemsite_perishable,
- _new.itemsite_nnqoh, _new.itemsite_autoabcclass,
+ _new.itemsite_autoabcclass,
_new.itemsite_ordergroup, _new.itemsite_disallowblankwip,
_new.itemsite_maxordqty, _new.itemsite_mps_timefence,
_new.itemsite_createwo, _new.itemsite_warrpurc,
RETURN _new.itemsite_id;
END;
-$$ LANGUAGE 'plpgsql';
\ No newline at end of file
+$$ LANGUAGE plpgsql;
\ No newline at end of file
-CREATE OR REPLACE FUNCTION deleteItemSite(INTEGER) RETURNS INTEGER AS $$
+CREATE OR REPLACE FUNCTION deleteItemSite(pItemsiteid INTEGER) RETURNS INTEGER AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pItemsiteid ALIAS FOR $1;
_result INTEGER;
_lotserial BOOLEAN;
_bbom BOOLEAN;
BEGIN
- IF ( ( SELECT ( (itemsite_qtyonhand <> 0) OR (itemsite_nnqoh <> 0) )
+ IF ( ( SELECT (itemsite_qtyonhand <> 0)
FROM itemsite
WHERE (itemsite_id=pItemsiteid) ) ) THEN
RETURN -9;
RETURN 0;
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION detailedNNQOH(INTEGER, BOOLEAN) RETURNS NUMERIC AS '
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
+--
+-- Deprecated
+--
DECLARE
pItemsiteid ALIAS FOR $1;
pABS ALIAS FOR $2;
-CREATE OR REPLACE FUNCTION detailedQOH(INTEGER, BOOLEAN) RETURNS NUMERIC AS '
+CREATE OR REPLACE FUNCTION detailedQOH(pItemsiteid INTEGER,
+ pABS BOOLEAN) RETURNS NUMERIC AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pItemsiteid ALIAS FOR $1;
- pABS ALIAS FOR $2;
_qoh NUMERIC;
BEGIN
IF (pABS) THEN
SELECT SUM(noNeg(itemloc_qty)) INTO _qoh
- FROM itemloc LEFT OUTER JOIN location ON (itemloc_location_id=location_id)
- WHERE ( ( (location_id IS NULL) OR (location_netable) )
- AND (itemloc_itemsite_id=pItemsiteid) );
+ FROM itemloc
+ WHERE (itemloc_itemsite_id=pItemsiteid);
ELSE
SELECT SUM(itemloc_qty) INTO _qoh
- FROM itemloc LEFT OUTER JOIN location ON (itemloc_location_id=location_id)
- WHERE ( ( (location_id IS NULL) OR (location_netable) )
- AND (itemloc_itemsite_id=pItemsiteid) );
+ FROM itemloc
+ WHERE (itemloc_itemsite_id=pItemsiteid);
END IF;
IF (_qoh IS NULL) THEN
- _qoh := 0;
+ _qoh := 0.0;
END IF;
RETURN _qoh;
END;
-' LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION distributeItemlocSeries(INTEGER) RETURNS INTEGER AS $$
+CREATE OR REPLACE FUNCTION distributeItemlocSeries(pItemlocSeries INTEGER) RETURNS INTEGER AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pItemlocSeries ALIAS FOR $1;
_distCounter INTEGER;
_itemlocdist RECORD;
_itemlocid INTEGER;
WHERE (itemloc_id=_itemlocid);
END IF;
--- Adjust QOH if this itemlocdist is to/from a non-netable location
- IF ( SELECT (NOT location_netable)
- FROM itemloc, location
- WHERE ( (itemloc_location_id=location_id)
- AND (itemloc_id=_itemlocid) ) ) THEN
-
--- Record the netable->non-netable (or visaveras) invhist
- SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;
- INSERT INTO invhist
- ( invhist_id, invhist_itemsite_id,
- invhist_transtype, invhist_invqty,
- invhist_qoh_before, invhist_qoh_after,
- invhist_docnumber, invhist_comments,
- invhist_invuom, invhist_unitcost,
- invhist_costmethod, invhist_value_before, invhist_value_after,
- invhist_series )
- SELECT _invhistid, itemsite_id,
- 'NN', (_itemlocdist.qty * -1),
- itemsite_qtyonhand, (itemsite_qtyonhand - _itemlocdist.qty),
- invhist_docnumber, invhist_comments,
- uom_name, stdCost(item_id),
- itemsite_costmethod, itemsite_value,
- (itemsite_value + (_itemlocdist.qty * -1 * CASE WHEN(itemsite_costmethod='A') THEN avgcost(itemsite_id)
- ELSE stdCost(itemsite_item_id)
- END)),
- _itemlocdist.series
- FROM item, itemsite, invhist, uom
- WHERE ((itemsite_item_id=item_id)
- AND (item_inv_uom_id=uom_id)
- AND (itemsite_controlmethod <> 'N')
- AND (itemsite_id=_itemlocdist.itemsiteid)
- AND (invhist_id=_itemlocdist.invhistid));
-
--- Adjust the parent itemsite
- IF (NOT _itemlocdist.itemsite_freeze) THEN
- UPDATE itemsite
- SET itemsite_qtyonhand = (itemsite_qtyonhand - _itemlocdist.qty),
- itemsite_nnqoh = (itemsite_nnqoh + _itemlocdist.qty)
- FROM itemloc
- WHERE ((itemloc_itemsite_id=itemsite_id)
- AND (itemloc_id=_itemlocid));
- END IF;
- END IF;
-
END IF;
-- If, after the distribution, the target itemloc_qty = 0, delete the itemloc
RETURN _distCounter;
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION distributeToLocations(INTEGER) RETURNS INTEGER AS $$
+CREATE OR REPLACE FUNCTION distributeToLocations(pItemlocdistid INTEGER) RETURNS INTEGER AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pItemlocdistid ALIAS FOR $1;
_distCounter INTEGER;
_itemlocdist RECORD;
_itemlocid INTEGER;
END IF;
END IF;
--- Adjust QOH if this itemlocdist is to/from a non-netable location
- IF ( SELECT (NOT location_netable)
- FROM itemloc, location
- WHERE ((itemloc_location_id=location_id)
- AND (itemloc_id=_itemlocid)) ) THEN
-
--- Record the invhist record for the netable->non-netable (or visaversa)
- INSERT INTO invhist
- ( invhist_itemsite_id,
- invhist_transtype, invhist_invqty,
- invhist_qoh_before, invhist_qoh_after,
- invhist_docnumber, invhist_comments,
- invhist_invuom, invhist_unitcost,
- invhist_costmethod, invhist_value_before, invhist_value_after,
- invhist_series )
- SELECT itemsite_id,
- 'NN', (_itemlocdist.qty * -1),
- itemsite_qtyonhand, (itemsite_qtyonhand - _itemlocdist.qty),
- invhist_docnumber, invhist_comments,
- uom_name, stdCost(item_id),
- itemsite_costmethod, itemsite_value,
- (itemsite_value + (_itemlocdist.qty * -1 * CASE WHEN(itemsite_costmethod='A') THEN avgcost(itemsite_id)
- ELSE stdCost(itemsite_item_id)
- END)),
- _itemlocdist.series
- FROM item, itemsite, invhist, uom
- WHERE ( (itemsite_item_id=item_id)
- AND (item_inv_uom_id=uom_id)
- AND (itemsite_controlmethod <> 'N')
- AND (itemsite_id=_itemlocdist.itemsiteid)
- AND (invhist_id=_itemlocdist.invhistid) );
-
--- Update the itemsite_qoh
- IF (NOT _itemlocdist.itemsite_freeze) THEN
- UPDATE itemsite
- SET itemsite_qtyonhand = (itemsite_qtyonhand - _itemlocdist.qty),
- itemsite_nnqoh = (itemsite_nnqoh + _itemlocdist.qty)
- FROM itemloc
- WHERE ((itemloc_itemsite_id=itemsite_id)
- AND (itemloc_id=_itemlocid));
- END IF;
- END IF;
-
-- Cache the running qty.
_runningQty := _runningQty + _itemlocdist.qty;
RETURN _distCounter;
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
AND (invcitem_item_id=itemsite_item_id)
AND (invcitem_warehous_id=itemsite_warehous_id)
AND (invcitem_linenumber=coitem_linenumber))) > 0)) THEN 'P'
- WHEN (coitem_status='O' AND (itemsite_qtyonhand - qtyAllocated(itemsite_id, CURRENT_DATE)
+ WHEN (coitem_status='O' AND (qtyNetable(itemsite_id) - qtyAllocated(itemsite_id, CURRENT_DATE)
+ qtyOrdered(itemsite_id, CURRENT_DATE))
>= ((coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * coitem_qty_invuomratio)) THEN 'R'
ELSE coitem_status END
--get top level works orders
FOR _x IN
SELECT wo_id,wo_number,wo_subnumber,wo_status,wo_startdate,
- wo_duedate,wo_adhoc,wo_itemsite_id,itemsite_qtyonhand,
+ wo_duedate,wo_adhoc,wo_itemsite_id,qtyNetable(itemsite_id) AS netableqoh,
wo_qtyord,wo_qtyrcv,wo_prodnotes, item_number,
item_descrip1, item_descrip2, item_listprice, uom_name
FROM wo, itemsite, item, uom
_row.wodata_itemsite_id := _x.wo_itemsite_id;
_row.wodata_custprice := _x.item_listprice;
_row.wodata_listprice := _x.item_listprice;
- _row.wodata_qoh := _x.itemsite_qtyonhand;
+ _row.wodata_qoh := _x.netableqoh;
_row.wodata_short := noneg(_x.wo_qtyord - _x.wo_qtyrcv);
_row.wodata_qtyrcv := _x.wo_qtyrcv;
_row.wodata_qtyordreq := _x.wo_qtyord;
_level := (plevel + 1);
--find all WO with the ordid of the next level up
_qry := 'SELECT wo_id,wo_number,wo_subnumber,wo_status,wo_startdate,wo_duedate,
- wo_adhoc,wo_itemsite_id,itemsite_qtyonhand,wo_qtyord,wo_qtyrcv, wo_prodnotes,
+ wo_adhoc,wo_itemsite_id,qtyNetable(itemsite_id) AS netableqoh,wo_qtyord,wo_qtyrcv, wo_prodnotes,
item_number,item_descrip1, item_descrip2, item_listprice, uom_name,
womatl_qtyiss, womatl_scrap, womatl_wooper_id
FROM itemsite, wo, item, uom, womatl
_row.wodata_itemsite_id := _x.wo_itemsite_id;
_row.wodata_custprice := _x.item_listprice;
_row.wodata_listprice := _x.item_listprice;
- _row.wodata_qoh := _x.itemsite_qtyonhand;
+ _row.wodata_qoh := _x.netableqoh;
_row.wodata_short := noneg(_x.wo_qtyord - _x.wo_qtyrcv);
_row.wodata_qtyiss := _x.womatl_qtyiss;
_row.wodata_qtyrcv := _x.wo_qtyrcv;
_qry := 'SELECT womatl_id, wo_number, wo_subnumber,
wo_startdate, womatl_duedate, womatl_itemsite_id,
- itemsite_qtyonhand, womatl_qtyreq, womatl_qtyiss,
+ qtyNetable(itemsite_id) AS netableqoh, womatl_qtyreq, womatl_qtyiss,
womatl_qtyper, womatl_qtyreq, womatl_scrap,
womatl_ref, womatl_notes, womatl_price, item_listprice,
item_number, item_descrip1, item_descrip2,
_subrow.wodata_itemsite_id := _subx.womatl_itemsite_id;
_subrow.wodata_custprice := _subx.womatl_price;
_subrow.wodata_listprice := _subx.item_listprice;
- _subrow.wodata_qoh := _subx.itemsite_qtyonhand;
- IF((_subx.itemsite_qtyonhand > (_subx.womatl_qtyreq - _subx.womatl_qtyiss))) THEN
+ _subrow.wodata_qoh := _subx.netableqoh;
+ IF((_subx.netableqoh > (_subx.womatl_qtyreq - _subx.womatl_qtyiss))) THEN
_subrow.wodata_short := 0;
ELSE
- _subrow.wodata_short := (_subx.womatl_qtyreq - _subx.womatl_qtyiss) - _subx.itemsite_qtyonhand;
+ _subrow.wodata_short := (_subx.womatl_qtyreq - _subx.womatl_qtyiss) - _subx.netableqoh;
END IF;
_subrow.wodata_qtyper := _subx.womatl_qtyper;
_subrow.wodata_qtyiss := _subx.womatl_qtyiss;
-CREATE OR REPLACE FUNCTION initialDistribution(INTEGER, INTEGER) RETURNS INTEGER AS $$
+CREATE OR REPLACE FUNCTION initialDistribution(pItemsiteid INTEGER,
+ pLocationid INTEGER) RETURNS INTEGER AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pItemsiteid ALIAS FOR $1;
- pLocationid ALIAS FOR $2;
_itemlocid INTEGER;
_invhistid INTEGER;
_itemlocSeries INTEGER;
( _invhistid, pLocationid, _r.itemloc_ls_id,
_r.itemloc_qty, 0, _r.itemloc_qty );
--- Adjust QOH if this itemlocdist is to/from a non-netable location
- IF ( SELECT (NOT location_netable)
- FROM location
- WHERE (location_id=pLocationid) ) THEN
-
- INSERT INTO invhist
- ( invhist_itemsite_id, invhist_series,
- invhist_transtype, invhist_invqty,
- invhist_qoh_before, invhist_qoh_after,
- invhist_comments,
- invhist_invuom, invhist_unitcost,
- invhist_costmethod, invhist_value_before, invhist_value_after )
- SELECT itemsite_id, _itemlocSeries,
- 'NN', (_r.itemloc_qty * -1),
- _r.itemloc_qty, 0,
- 'Initial Distribution',
- uom_name, stdCost(item_id),
- itemsite_costmethod, itemsite_value, itemsite_value
- FROM itemsite, item, uom
- WHERE ( (itemsite_item_id=item_id)
- AND (item_inv_uom_id=uom_id)
- AND (itemsite_controlmethod <> 'N')
- AND (itemsite_id=pItemsiteid) );
-
- UPDATE itemsite
- SET itemsite_nnqoh = (itemsite_nnqoh + _r.itemloc_qty),
- itemsite_qtyonhand = (itemsite_qtyonhand - _r.itemloc_qty)
- WHERE (itemsite_id=pItemsiteid);
-
- END IF;
-
END LOOP;
ELSE
FROM itemsite
WHERE (itemsite_id=pItemsiteid);
--- Adjust QOH if this itemlocdist is to/from a non-netable location
- IF ( SELECT (NOT location_netable)
- FROM location
- WHERE (location_id=pLocationid) ) THEN
-
- INSERT INTO invhist
- ( invhist_itemsite_id, invhist_series,
- invhist_transtype, invhist_invqty,
- invhist_qoh_before, invhist_qoh_after,
- invhist_comments,
- invhist_invuom, invhist_unitcost,
- invhist_costmethod, invhist_value_before, invhist_value_after )
- SELECT itemsite_id, _itemlocSeries,
- 'NN', (itemloc_qty * -1),
- itemloc_qty, 0,
- 'Initial Distribution',
- uom_name, stdCost(item_id),
- itemsite_costmethod, itemsite_value, itemsite_value
- FROM itemloc, itemsite, item, uom
- WHERE ( (itemsite_item_id=item_id)
- AND (item_inv_uom_id=uom_id)
- AND (itemsite_controlmethod <> 'N')
- AND (itemloc_itemsite_id=itemsite_id)
- AND (itemloc_id=_itemlocid) );
-
- UPDATE itemsite
- SET itemsite_nnqoh = itemsite_qtyonhand,
- itemsite_qtyonhand = 0
- FROM itemloc
- WHERE ( (itemloc_itemsite_id=itemsite_id)
- AND (itemloc_id=_itemlocid) );
-
- END IF;
-
END IF;
RETURN _itemlocid;
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION itemInventoryUOMInUse(INTEGER) RETURNS BOOLEAN AS '
+CREATE OR REPLACE FUNCTION itemInventoryUOMInUse(pItemid INTEGER) RETURNS BOOLEAN AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pItemid ALIAS FOR $1;
_uomid INTEGER;
_result INTEGER;
BEGIN
SELECT itemsite_id INTO _result
FROM itemsite WHERE ( (itemsite_item_id=pItemid)
- AND ((itemsite_qtyonhand <> 0) OR (itemsite_nnqoh <> 0)) )
+ AND (itemsite_qtyonhand <> 0) )
LIMIT 1;
IF (FOUND) THEN
RETURN TRUE;
RETURN FALSE;
END;
-' LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
SELECT dropIfExists('FUNCTION', 'postCountTag(integer, boolean, text)', 'public');
-CREATE OR REPLACE FUNCTION postCountTag(INTEGER, BOOLEAN) RETURNS INTEGER AS $$
+CREATE OR REPLACE FUNCTION postCountTag(pInvcntid INTEGER,
+ pThaw BOOLEAN) RETURNS INTEGER AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pInvcntid ALIAS FOR $1;
- pThaw ALIAS FOR $2;
_avgCostingMethod TEXT;
_invhistid INTEGER;
_postDate TIMESTAMP;
-- Avoid negative value when average cost item
UPDATE itemsite
SET itemsite_qtyonhand=_p.invcnt_qoh_after,
- itemsite_nnqoh = 0,
- itemsite_value = CASE WHEN ((itemsite_costmethod='A') AND (_p.itemsite_value + (_p.cost * (_p.invcnt_qoh_after - itemsite_qtyonhand))) < 0.0) THEN 0.0
+ itemsite_value = CASE WHEN ((itemsite_costmethod='A') AND
+ (_p.itemsite_value + (_p.cost * (_p.invcnt_qoh_after - itemsite_qtyonhand))) < 0.0) THEN 0.0
ELSE (_p.itemsite_value + (_p.cost * (_p.invcnt_qoh_after - itemsite_qtyonhand)))
END,
itemsite_datelastcount=_postDate
END IF;
-- Distribute to G/L
- PERFORM insertGLTransaction( 'I/M', 'CT', _p.invcnt_tagnumber, ('Post Count Tag #' || _p.invcnt_tagnumber || ' for Item ' || _p.item_number),
+ PERFORM insertGLTransaction( 'I/M', 'CT', _p.invcnt_tagnumber,
+ ('Post Count Tag #' || _p.invcnt_tagnumber || ' for Item ' || _p.item_number),
costcat_adjustment_accnt_id, costcat_asset_accnt_id, _invhistid,
( (_p.invcnt_qoh_after - _p.itemsite_qtyonhand) * _p.cost), _postDate::DATE )
FROM invcnt, itemsite, costcat
END IF;
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION postCountTagLocation(INTEGER, BOOLEAN) RETURNS INTEGER AS $$
+CREATE OR REPLACE FUNCTION postCountTagLocation(pInvcntid INTEGER,
+ pThaw BOOLEAN) RETURNS INTEGER AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pInvcntid ALIAS FOR $1;
- pThaw ALIAS FOR $2;
_avgCostingMethod TEXT;
_invhistid INTEGER;
_postDate TIMESTAMP;
_itemloc RECORD;
_cntslip RECORD;
_origLocQty NUMERIC;
- _netable BOOLEAN;
_lsid INTEGER;
BEGIN
itemsite_loccntrl, COALESCE(invcnt_location_id, -1) AS itemsite_location_id,
CASE WHEN (itemsite_costmethod = 'N') THEN 0
WHEN ( (itemsite_costmethod = 'A') AND
- ((itemsite_qtyonhand + itemsite_nnqoh) = 0) AND
+ (itemsite_qtyonhand = 0.0) AND
(_avgCostingMethod = 'ACT') ) THEN actcost(itemsite_item_id)
WHEN ( (itemsite_costmethod = 'A') AND
(_avgCostingMethod IN ('ACT', 'AVG')) ) THEN avgcost(itemsite_id)
RETURN -9;
END IF;
- SELECT COALESCE(SUM(itemloc_qty),0.0), location_netable INTO _origLocQty,_netable
- FROM itemloc,location
+ SELECT COALESCE(SUM(itemloc_qty),0.0) INTO _origLocQty
+ FROM itemloc
WHERE ((itemloc_itemsite_id=_p.itemsite_id)
- AND (location_id=itemloc_location_id)
- AND (itemloc_location_id=_p.invcnt_location_id))
- GROUP BY location_netable;
+ AND (itemloc_location_id=_p.invcnt_location_id));
IF (NOT FOUND) THEN
_origLocQty := 0.0;
- _netable := TRUE;
END IF;
SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;
AND (invcnt_id=pInvcntid) );
-- Update the QOH
- IF (_netable) THEN
- UPDATE itemsite
- SET itemsite_qtyonhand= itemsite_qtyonhand + (_p.invcnt_qoh_after - _origLocQty),
- itemsite_datelastcount=_postDate
- WHERE (itemsite_id=_p.itemsite_id);
- UPDATE itemsite
- SET itemsite_value = (itemsite_qtyonhand + itemsite_nnqoh) * _p.cost
- WHERE (itemsite_id=_p.itemsite_id);
- ELSE
- UPDATE itemsite
- SET itemsite_nnqoh = itemsite_nnqoh + (_p.invcnt_qoh_after - _origLocQty),
- itemsite_datelastcount=_postDate
- WHERE (itemsite_id=_p.itemsite_id);
- UPDATE itemsite
- SET itemsite_value = (itemsite_qtyonhand + itemsite_nnqoh) * _p.cost
- WHERE (itemsite_id=_p.itemsite_id);
- END IF;
+ UPDATE itemsite
+ SET itemsite_qtyonhand= itemsite_qtyonhand + (_p.invcnt_qoh_after - _origLocQty),
+ itemsite_datelastcount=_postDate
+ WHERE (itemsite_id=_p.itemsite_id);
+ UPDATE itemsite
+ SET itemsite_value = itemsite_qtyonhand * _p.cost
+ WHERE (itemsite_id=_p.itemsite_id);
-- Post the detail, if any
IF (_hasDetail) THEN
END IF;
-- Distribute to G/L
- PERFORM insertGLTransaction( 'I/M', 'CT', _p.invcnt_tagnumber, ('Post Count Tag #' || _p.invcnt_tagnumber || ' for Item ' || _p.item_number),
+ PERFORM insertGLTransaction( 'I/M', 'CT', _p.invcnt_tagnumber,
+ ('Post Count Tag #' || _p.invcnt_tagnumber || ' for Item ' || _p.item_number),
costcat_adjustment_accnt_id, costcat_asset_accnt_id, _invhistid,
( (_p.invcnt_qoh_after - _origLocQty) * _p.cost), CURRENT_DATE )
FROM invcnt, itemsite, costcat
RETURN 0;
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
--- /dev/null
+
+CREATE OR REPLACE FUNCTION qtyAtLocation(pItemsiteid INTEGER,
+ pLocationid INTEGER) RETURNS NUMERIC STABLE AS $$
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- See www.xtuple.com/CPAL for the full text of the software license.
+DECLARE
+ _qty NUMERIC := 0.0;
+
+BEGIN
+ SELECT CASE WHEN (pLocationid IS NULL) THEN itemsite_qtyonhand
+ ELSE COALESCE(SUM(itemloc_qty), 0.0)
+ END INTO _qty
+ FROM itemsite LEFT OUTER JOIN itemloc ON (itemloc_itemsite_id=itemsite_id)
+ WHERE ((itemsite_id=pItemsiteid)
+ AND (itemloc_location_id=pLocationid))
+ GROUP BY itemsite_qtyonhand;
+
+ RETURN _qty;
+
+END;
+$$ LANGUAGE plpgsql;
BEGIN
- RETURN ( ( SELECT itemsite_qtyonhand
+ RETURN ( ( SELECT qtyNetable(itemsite_id)
FROM itemsite
WHERE (itemsite_id=pItemsiteid) ) +
(SELECT qtyOrdered(pItemsiteid, pLookAheadDays)) -
BEGIN
- RETURN ( ( SELECT itemsite_qtyonhand
+ RETURN ( ( SELECT qtyNetable(itemsite_id)
FROM itemsite
WHERE (itemsite_id=pItemsiteid) ) +
(SELECT qtyOrdered(pItemsiteid, (pDate - CURRENT_DATE))) -
--- /dev/null
+
+CREATE OR REPLACE FUNCTION qtyNetable(pItemsiteId INTEGER) RETURNS NUMERIC STABLE AS $$
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- See www.xtuple.com/CPAL for the full text of the software license.
+DECLARE
+ _qty NUMERIC = 0.0;
+
+BEGIN
+ _qty := qtyNetable(pItemsiteId, TRUE);
+
+ RETURN _qty;
+
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION qtyNetable(pItemsiteId INTEGER,
+ pNetable BOOLEAN) RETURNS NUMERIC STABLE AS $$
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- See www.xtuple.com/CPAL for the full text of the software license.
+DECLARE
+ _qty NUMERIC = 0.0;
+
+BEGIN
+ IF (pNetable) THEN
+ -- Summarize itemloc qty for this itemsite/netable locations
+ -- or use itemsite_qtyonhand for regular/non-lot
+ SELECT COALESCE(SUM(itemloc_qty), itemsite_qtyonhand) INTO _qty
+ FROM itemsite LEFT OUTER JOIN itemloc ON (itemloc_itemsite_id=itemsite_id)
+ LEFT OUTER JOIN location ON (location_id=itemloc_location_id)
+ WHERE (itemsite_id=pItemsiteId)
+ AND ((location_id IS NULL) OR (location_netable))
+ GROUP BY itemsite_qtyonhand;
+ ELSE
+ -- Summarize itemloc qty for this itemsite/non-netable locations
+ SELECT COALESCE(SUM(itemloc_qty), 0.0) INTO _qty
+ FROM itemloc JOIN location ON (location_id=itemloc_location_id)
+ WHERE (itemloc_itemsite_id=pItemsiteId)
+ AND (NOT location_netable);
+ END IF;
+
+ RETURN _qty;
+
+END;
+$$ LANGUAGE plpgsql;
BEGIN
RETURN relocateInventory($1, $2, $3, $4, $5, CURRENT_TIMESTAMP);
END;
-$$ LANGUAGE 'plpgsql';
-
-CREATE OR REPLACE FUNCTION relocateInventory(INTEGER, INTEGER, INTEGER, NUMERIC, TEXT, TIMESTAMP WITH TIME ZONE) RETURNS INTEGER AS $$
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION relocateInventory(pSourceItemlocid INTEGER,
+ pTargetLocationid INTEGER,
+ pItemsiteid INTEGER,
+ pQty NUMERIC,
+ pComments TEXT,
+ pGLDistTS TIMESTAMP WITH TIME ZONE) RETURNS INTEGER AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pSourceItemlocid ALIAS FOR $1;
- pTargetLocationid ALIAS FOR $2;
- pItemsiteid ALIAS FOR $3;
- pQty ALIAS FOR $4;
- pComments ALIAS FOR $5;
- _GlDistTS TIMESTAMP WITH TIME ZONE := $6;
+ _GlDistTS TIMESTAMP WITH TIME ZONE;
_targetItemlocid INTEGER;
_invhistid INTEGER;
_p RECORD;
BEGIN
- IF ((_GlDistTS IS NULL) OR (CAST(_GlDistTS AS date)=CURRENT_DATE)) THEN
+ IF ((pGlDistTS IS NULL) OR (CAST(pGlDistTS AS date)=CURRENT_DATE)) THEN
_GlDistTS := CURRENT_TIMESTAMP;
+ ELSE
+ _GLDistTS := pGLDistTS;
END IF;
-- Make sure the passed itemsite points to a real item
itemloc_itemsite_id AS itemsiteid,
itemloc_expiration,
itemloc_warrpurc,
- itemloc_qty,
- sourceloc.location_netable AS sourcenet,
- targetloc.location_netable AS targetnet INTO _p
- FROM itemloc, location AS sourceloc, location AS targetloc
- WHERE ( (itemloc_location_id=sourceloc.location_id)
- AND (targetloc.location_id=pTargetLocationid)
- AND (itemloc_id=pSourceItemlocid) );
+ itemloc_qty INTO _p
+ FROM itemloc
+ WHERE (itemloc_id=pSourceItemlocid);
-- Check to make sure the qty being transfered exists
IF (_p.itemloc_qty < pQty) THEN
SET invhist_hasdetail=TRUE
WHERE (invhist_id=_invhistid);
--- Post in incomming or outgoing NN transaction if required
- IF (_p.sourcenet <> _p.targetnet) THEN
- IF (_p.targetnet) THEN
- _qty = (pQty * -1);
- ELSE
- _qty = pQty;
- END IF;
-
- INSERT INTO invhist
- ( invhist_itemsite_id,
- invhist_transtype, invhist_invqty,
- invhist_qoh_before, invhist_qoh_after,
- invhist_docnumber, invhist_comments, invhist_transdate,
- invhist_invuom, invhist_unitcost, invhist_costmethod,
- invhist_value_before, invhist_value_after, invhist_series)
- SELECT itemsite_id,
- 'NN', (_qty * -1),
- itemsite_qtyonhand, (itemsite_qtyonhand - _qty),
- '', '', _GlDistTS,
- uom_name,
- CASE WHEN (itemsite_costmethod='A') THEN avgcost(itemsite_id)
- ELSE stdCost(item_id)
- END, itemsite_costmethod,
- itemsite_value, itemsite_value, _itemlocSeries
- FROM item, itemsite, uom
- WHERE ( (itemsite_item_id=item_id)
- ANd (item_inv_uom_id=uom_id)
- AND (itemsite_controlmethod <> 'N')
- AND (itemsite_id=_p.itemsiteid) );
-
- UPDATE itemsite
- SET itemsite_qtyonhand = (itemsite_qtyonhand - _qty),
- itemsite_nnqoh = (itemsite_nnqoh + _qty)
- WHERE (itemsite_id=_p.itemsiteid);
- END IF;
-
-- Check to see if there is anything left at the target Itemloc and delete if not
-- Could be zero if relocate increased a negative quantity to zero
DELETE FROM itemloc
RETURN _invhistid;
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
noNeg(coitem_qtyord - coitem_qtyshipped +
coitem_qtyreturned - qtyAtShipping(pordertype, coitem_id)
))) - coitem_qtyreserved) * coitem_qty_invuomratio
- ) <= itemsite_qtyonhand)
+ ) <= qtyNetable(itemsite_id))
AND
(((COALESCE(pqty, roundQty(item_fractional,
noNeg(coitem_qtyord - coitem_qtyshipped +
coitem_qtyreturned - qtyAtShipping(pordertype, coitem_id) - coitem_qtyreserved
) * coitem_qty_invuomratio
)
- ) <= itemsite_qtyonhand)
+ ) <= qtyNetable(itemsite_id))
INTO _isqtyavail
FROM coitem, itemsite, item
WHERE ((coitem_itemsite_id=itemsite_id)
qtyAtShipping(pordertype, toitem_id)
)
)
- ) <= itemsite_qtyonhand) INTO _isqtyavail
+ ) <= qtyNetable(itemsite_id)) INTO _isqtyavail
FROM toitem, tohead, itemsite, item
WHERE ((toitem_tohead_id=tohead_id)
AND (tohead_src_warehous_id=itemsite_warehous_id)
-CREATE OR REPLACE FUNCTION thawItemSite(INTEGER) RETURNS INTEGER AS $$
+CREATE OR REPLACE FUNCTION thawItemSite(pItemsiteid INTEGER) RETURNS INTEGER AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pItemsiteid ALIAS FOR $1;
_qoh NUMERIC := 0;
- _netable_qoh NUMERIC := 0;
- _nonnetable_qoh NUMERIC := 0;
_value NUMERIC := 0;
_itemlocid INTEGER;
_itemloc RECORD;
-- Cache the initial qty of the itemloc specified by the
-- itemsite/location/lot/serial
- SELECT itemloc_id, itemloc_qty, COALESCE(location_netable, TRUE) AS location_netable
+ SELECT itemloc_id, itemloc_qty
INTO _itemloc
- FROM itemloc LEFT OUTER JOIN location ON (location_id=itemloc_location_id)
+ FROM itemloc
WHERE ( (itemloc_itemsite_id=pItemsiteid)
AND (itemloc_location_id=_coarse.invdetail_location_id)
AND (COALESCE(itemloc_ls_id,-1)=COALESCE(_coarse.invdetail_ls_id,-1))
0, endOfTime() );
_qoh := 0.0;
- _netable_qoh := 0.0;
- _nonnetable_qoh := 0.0;
ELSE
_itemlocid := _itemloc.itemloc_id;
_qoh := _itemloc.itemloc_qty;
- IF (_itemloc.location_netable) THEN
- _netable_qoh := _itemloc.itemloc_qty;
- ELSE
- _nonnetable_qoh := _itemloc.itemloc_qty;
- END IF;
END IF;
-- Now step through each unposted invdetail record for a given
-- Update the running qoh
_qoh = (_qoh + _fine.invdetail_qty);
- IF (_itemloc.location_netable) THEN
- _netable_qoh := (_netable_qoh + _fine.invdetail_qty);
- ELSE
- _nonnetable_qoh := (_nonnetable_qoh + _fine.invdetail_qty);
- END IF;
END LOOP;
END LOOP;
--- _qoh can be used for the netable qoh because of the negative NN transactions
--- change to update qtyonhand with _netable_qoh
UPDATE itemsite
- SET itemsite_qtyonhand = _netable_qoh,
- itemsite_nnqoh = _nonnetable_qoh,
+ SET itemsite_qtyonhand = _qoh,
itemsite_value = CASE WHEN ((itemsite_costmethod='A') AND (_value < 0.0)) THEN 0.0
ELSE _value END
WHERE(itemsite_id=pItemsiteid);
RETURN pItemsiteid;
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION updateStdCost(INTEGER, NUMERIC, NUMERIC, TEXT, TEXT) RETURNS BOOLEAN AS $$
+CREATE OR REPLACE FUNCTION updateStdCost(pItemcostid INTEGER,
+ pNewcost NUMERIC,
+ pOldcost NUMERIC,
+ pDocNumber TEXT,
+ pNotes TEXT) RETURNS BOOLEAN AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
- pItemcostid ALIAS FOR $1;
- pNewcost ALIAS FOR $2;
- pOldcost ALIAS FOR $3;
- pDocNumber ALIAS FOR $4;
- pNotes ALIAS FOR $5;
_itemcostid INTEGER;
_r RECORD;
_newcost NUMERIC;
END IF;
-- Distribute to G/L, debit Inventory Asset, credit Inventory Cost Variance
- FOR _r IN SELECT itemsite_id, (itemsite_qtyonhand + itemsite_nnqoh) AS totalQty,
+ FOR _r IN SELECT itemsite_id, itemsite_qtyonhand AS totalQty,
costcat_invcost_accnt_id, costcat_asset_accnt_id,
itemsite_costmethod
FROM itemcost, itemsite, costcat
WHERE ( (itemsite_item_id=itemcost_item_id)
AND (itemsite_costcat_id=costcat_id)
AND (itemsite_costmethod != 'A')
- AND ((itemsite_qtyonhand + itemsite_nnqoh) <> 0)
+ AND (itemsite_qtyonhand <> 0.0)
AND (itemcost_id=pItemcostid) ) LOOP
-- IF (_newcost <> _oldcost) THEN
-- RAISE NOTICE 'itemcost_id = %, Qty = %, Old Cost = %, New Cost = %', pItemcostid, _r.totalQty, _oldcost, _newcost;
RETURN -1;
END;
-$$ LANGUAGE 'plpgsql';
+$$ LANGUAGE plpgsql;
item_descrip1,
item_descrip2,
uom_name,
- noNeg(itemsite_qtyonhand) AS qoh,
+ noNeg(qtyNetable(itemsite_id)) AS qoh,
noNeg(wo_qtyord - wo_qtyrcv) AS wobalance,
qtyAllocated(itemsite_id, wo_duedate) AS allocated,
qtyOrdered(itemsite_id, wo_duedate) AS ordered,
item_descrip1,
item_descrip2,
uom_name,
- noNeg(itemsite_qtyonhand) AS qoh,
+ noNeg(qtyNetable(itemsite_id)) AS qoh,
noNeg(itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, womatl_qtyreq - womatl_qtyiss)) AS wobalance,
qtyAllocated(itemsite_id, womatl_duedate) AS allocated,
qtyOrdered(itemsite_id, womatl_duedate) AS ordered,
item_descrip1,
item_descrip2,
uom_name,
- noNeg(itemsite_qtyonhand) AS qoh,
+ noNeg(qtyNetable(itemsite_id)) AS qoh,
noNeg(wo_qtyord - wo_qtyrcv) AS wobalance,
qtyAllocated(itemsite_id, wo_duedate) AS allocated,
qtyOrdered(itemsite_id, wo_duedate) AS ordered,
item_descrip1,
item_descrip2,
uom_name,
- noNeg(itemsite_qtyonhand) AS qoh,
+ noNeg(qtyNetable(itemsite_id)) AS qoh,
noNeg(itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, womatl_qtyreq - womatl_qtyiss)) AS wobalance,
qtyAllocated(itemsite_id, womatl_duedate) AS allocated,
qtyOrdered(itemsite_id, womatl_duedate) AS ordered,
invcnt_tagdate AS tagdate,
item_number, (item_descrip1 || item_descrip2) AS item_descrip, warehous_code,
CASE WHEN (location_id IS NOT NULL) THEN
- location_name
+ formatLocationName(location_id)
ELSE <? value("all") ?> END AS loc_specific,
- CASE WHEN (invcnt_location_id IS NOT NULL)
- THEN (SELECT SUM(itemloc_qty)
- FROM itemloc JOIN location ON (location_id=itemloc_location_id)
- WHERE ((itemloc_itemsite_id=itemsite_id)
- AND (itemloc_location_id=invcnt_location_id)
- AND (location_netable)))
- ELSE itemsite_qtyonhand
- END AS qoh,
- CASE WHEN (invcnt_location_id IS NOT NULL)
- THEN (SELECT SUM(itemloc_qty)
- FROM itemloc JOIN location ON (location_id=itemloc_location_id)
- WHERE ((itemloc_itemsite_id=itemsite_id)
- AND (itemloc_location_id=invcnt_location_id)
- AND (NOT location_netable)))
- ELSE itemsite_nnqoh
- END AS nnqoh,
+ qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) AS qoh,
<? if exists("showSlips") ?>
calcTotalSlipQty(invcnt_id) AS qohafter,
- (calcTotalSlipQty(invcnt_id) - (itemsite_qtyonhand + itemsite_nnqoh)) AS variance,
+ (calcTotalSlipQty(invcnt_id) - qtyAtLocation(invcnt_itemsite_id, invcnt_location_id)) AS variance,
CASE WHEN (calcTotalSlipQty(invcnt_id) IS NULL) THEN NULL
- WHEN (((itemsite_qtyonhand + itemsite_nnqoh) = 0) AND (calcTotalSlipQty(invcnt_id) > 0)) THEN 1
- WHEN (((itemsite_qtyonhand + itemsite_nnqoh) = 0) AND (calcTotalSlipQty(invcnt_id) < 0)) THEN -1
- WHEN (((itemsite_qtyonhand + itemsite_nnqoh) = 0) AND (calcTotalSlipQty(invcnt_id) = 0)) THEN 0
- ELSE ((1 - (calcTotalSlipQty(invcnt_id) / (itemsite_qtyonhand + itemsite_nnqoh))) * -1)
+ WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (calcTotalSlipQty(invcnt_id) > 0)) THEN 1
+ WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (calcTotalSlipQty(invcnt_id) < 0)) THEN -1
+ WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (calcTotalSlipQty(invcnt_id) = 0)) THEN 0
+ ELSE ((1 - (calcTotalSlipQty(invcnt_id) / qtyAtLocation(invcnt_itemsite_id, invcnt_location_id))) * -1)
END AS varianceprcnt,
- (itemCost(itemsite_id) * (calcTotalSlipQty(invcnt_id) - (itemsite_qtyonhand + itemsite_nnqoh))) AS variancecost,
+ (itemCost(itemsite_id) * (calcTotalSlipQty(invcnt_id) - qtyAtLocation(invcnt_itemsite_id, invcnt_location_id))) AS variancecost,
<? else ?>
COALESCE(invcnt_qoh_after, 0.0) AS qohafter,
- (invcnt_qoh_after - (itemsite_qtyonhand + itemsite_nnqoh)) AS variance,
+ (invcnt_qoh_after - qtyAtLocation(invcnt_itemsite_id, invcnt_location_id)) AS variance,
CASE WHEN (invcnt_qoh_after IS NULL) THEN NULL
- WHEN (((itemsite_qtyonhand + itemsite_nnqoh) = 0) AND (invcnt_qoh_after > 0)) THEN 1
- WHEN (((itemsite_qtyonhand + itemsite_nnqoh) = 0) AND (invcnt_qoh_after < 0)) THEN -1
- WHEN (((itemsite_qtyonhand + itemsite_nnqoh) = 0) AND (invcnt_qoh_after = 0)) THEN 0
- ELSE ((1 - (invcnt_qoh_after / (itemsite_qtyonhand + itemsite_nnqoh))) * -1)
+ WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (invcnt_qoh_after > 0)) THEN 1
+ WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (invcnt_qoh_after < 0)) THEN -1
+ WHEN ((qtyAtLocation(invcnt_itemsite_id, invcnt_location_id) = 0) AND (invcnt_qoh_after = 0)) THEN 0
+ ELSE ((1 - (invcnt_qoh_after / qtyAtLocation(invcnt_itemsite_id, invcnt_location_id))) * -1)
END AS varianceprcnt,
- (itemCost(itemsite_id) * (invcnt_qoh_after - (itemsite_qtyonhand + itemsite_nnqoh))) AS variancecost,
+ (itemCost(itemsite_id) * (invcnt_qoh_after - qtyAtLocation(invcnt_itemsite_id, invcnt_location_id))) AS variancecost,
<? endif ?>
item_number AS orderby,
0 AS xtindentrole
'' AS warehous_code,
'' AS loc_specific,
NULL AS qoh,
- NULL AS nnqoh,
cntslip_qty AS qohafter,
NULL AS variance, NULL AS varianceprcnt, 0 AS variancecost,
item_number AS orderby,
GROUP BY cohead_id, item_number, cust_number,
cust_name, cohead_orderdate, pack_id, coitem_scheddate
<? if exists("onlyShowShortages") ?>
- HAVING (MIN(noNeg(itemsite_qtyonhand) +
+ HAVING (MIN(noNeg(qtyNetable(itemsite_id)) +
qtyOrdered(itemsite_id, coitem_scheddate) -
qtyAllocated(itemsite_id, coitem_scheddate)) < 0
- OR MIN(noNeg(itemsite_qtyonhand) +
+ OR MIN(noNeg(qtyNetable(itemsite_id)) +
qtyOrdered(itemsite_id, coitem_scheddate) -
noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned)) < 0
)
item_number,
item_number AS trueitem_number,
(item_descrip1 || ' ' || item_descrip2) AS descrip,
- uom_name, noNeg(itemsite_qtyonhand) AS qoh,
+ uom_name, noNeg(qtyNetable(itemsite_id)) AS qoh,
noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) AS sobalance,
qtyAllocated(itemsite_id, coitem_scheddate) AS allocated,
qtyOrdered(itemsite_id, coitem_scheddate) AS ordered,
WHERE(custtype_code ~ <? value("custtype_pattern") ?>)))
<? endif ?>
<? if exists("onlyShowShortages") ?>
- AND ((noNeg(itemsite_qtyonhand) + qtyOrdered(itemsite_id, coitem_scheddate) - qtyAllocated(itemsite_id, coitem_scheddate) < 0)
- OR (noNeg(itemsite_qtyonhand) + qtyOrdered(itemsite_id, coitem_scheddate) - noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) < 0))
+ AND ((noNeg(qtyNetable(itemsite_id)) + qtyOrdered(itemsite_id, coitem_scheddate) - qtyAllocated(itemsite_id, coitem_scheddate) < 0)
+ OR (noNeg(qtyNetable(itemsite_id)) + qtyOrdered(itemsite_id, coitem_scheddate) - noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) < 0))
<? endif ?>
)
<? if exists("showWoSupply") ?>
AND (wo_status IN ('E','R','I'))
AND (wo_qtyord-wo_qtyrcv > 0)
AND (noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned-qtyatshipping(coitem_id)) >
- (SELECT itemsite_qtyonhand FROM itemsite WHERE (itemsite_id=coitem_itemsite_id))))
+ (SELECT qtyNetable(itemsite_id) FROM itemsite WHERE (itemsite_id=coitem_itemsite_id))))
WHERE ((coitem_cohead_id=cohead_id)
AND (coitem_itemsite_id=itemsite_id)
AND (itemsite_warehous_id=warehous_id)
WHERE(custtype_code ~ <? value("custtype_pattern") ?>)))
<? endif ?>
<? if exists("onlyShowShortages") ?>
- AND ((noNeg(itemsite_qtyonhand) + qtyOrdered(itemsite_id, coitem_scheddate) - qtyAllocated(itemsite_id, coitem_scheddate) < 0)
- OR (noNeg(itemsite_qtyonhand) + qtyOrdered(itemsite_id, coitem_scheddate) - noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) < 0))
+ AND ((noNeg(qtyNetable(itemsite_id)) + qtyOrdered(itemsite_id, coitem_scheddate) - qtyAllocated(itemsite_id, coitem_scheddate) < 0)
+ OR (noNeg(qtyNetable(itemsite_id)) + qtyOrdered(itemsite_id, coitem_scheddate) - noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) < 0))
<? endif ?>
)
<? endif ?>
END AS altId,
item_number, item_descrip1, item_descrip2, item_inv_uom_id,
warehous_id, warehous_code, itemsite_leadtime,
- itemsite_qtyonhand AS qoh,
+ qtyNetable(itemsite_id) AS qoh,
CASE WHEN itemsite_useparams THEN itemsite_reorderlevel
ELSE 0.0
END AS reorderlevel,
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
-SELECT formatQty(itemsite_qtyonhand) AS f_qoh,itemsite_qtyonhand
- , qtyAllocated(itemsite_id, findPeriodStart(<? value("cursorId") ?>),
+SELECT qtyNetable(itemsite_id) AS qoh, formatQty(qtyNetable(itemsite_id)) AS f_qoh,
+ qtyAllocated(itemsite_id, findPeriodStart(<? value("cursorId") ?>),
findPeriodEnd(<? value("cursorId") ?>)) AS allocations<? value("counter") ?>,
qtyOrdered(itemsite_id, findPeriodStart(<? value("cursorId") ?>),
findPeriodEnd(<? value("cursorId") ?>)) AS orders<? value("counter") ?>,
ib.*,
((bomdata_qtyreq::NUMERIC * <? value("buildQty") ?>) * (1 + bomdata_scrap::NUMERIC)) AS pendalloc,
qtyAllocated(itemsite_id, DATE(<? value("buildDate") ?>)) AS totalalloc,
- noNeg(itemsite_qtyonhand) AS qoh,
+ noNeg(qtyNetable(itemsite_id)) AS qoh,
qtyOrdered(itemsite_id, DATE(<? value("buildDate") ?>)) AS ordered
FROM indentedBOM(<? value("item_id") ?>,
getActiveRevId('BOM', <? value("item_id") ?>),
((itemuomtouom(bomitem_item_id, bomitem_uom_id, NULL,
(bomitem_qtyfxd + bomitem_qtyper * <? value("buildQty") ?>) * (1 + bomitem_scrap)))) AS pendalloc,
qtyAllocated(itemsite_id, DATE(<? value("buildDate") ?>)) AS totalalloc,
- noNeg(itemsite_qtyonhand) AS qoh,
+ noNeg(qtyNetable(itemsite_id)) AS qoh,
qtyOrdered(itemsite_id, DATE(<? value("buildDate") ?>)) AS ordered,
CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel
FROM itemsite, item, bomitem(<? value("item_id") ?>), uom
-- See www.xtuple.com/CPAL for the full text of the software license.
SELECT pr_id, itemsite_id, itemsite_qtyonhand, itemsite_reorderlevel,
+ qtyNetable(itemsite_id) AS netableqoh, qtyNetable(itemsite_id, FALSE) AS nonnetableqoh,
item_number, (item_descrip1 || ' ' || item_descrip2) AS description,
pr.*,
CASE WHEN (pr_order_type='W') THEN ('W/O ' || ( SELECT formatWoNumber(womatl_wo_id)
defaultlocation,
reorderlevel, formatQty(reorderlevel) AS f_reorderlevel,
qoh, formatQty(qoh) AS f_qoh,
- nnqoh, formatQty(nnqoh) AS f_nnqoh,
- CASE WHEN (itemsite_loccntrl) THEN nnqoh END AS f_nnqoh,
+ netqoh, formatQty(netqoh) AS f_netqoh,
+ nonnetqoh, formatQty(netqoh) AS f_nonnetqoh,
cost, (cost * qoh) AS value,
- CASE WHEN (itemsite_loccntrl) THEN (cost * nnqoh) END AS nnvalue,
+ (cost * netqoh) AS netvalue,
+ (cost * nonnetqoh) AS nonnetvalue,
CASE WHEN(itemsite_costmethod='A') THEN 'Average'
WHEN(itemsite_costmethod='S') THEN 'Standard'
WHEN(itemsite_costmethod='J') THEN 'Job'
END AS costmethod,
<? if exists("showValue") ?>
formatMoney(cost) AS f_cost, (cost * qoh) AS f_value,
- CASE WHEN (itemsite_loccntrl) THEN (cost * nnqoh) END AS f_nnvalue,
+ formatMoney(cost * netqoh) AS f_netvalue,
+ formatMoney(cost * nonnetqoh) AS f_nonnetvalue,
CASE WHEN(itemsite_costmethod='A') THEN 'Average'
WHEN(itemsite_costmethod='S') THEN 'Standard'
WHEN(itemsite_costmethod='J') THEN 'Job'
<? endif ?>
'qty' AS reorderlevel_xtnumericrole,
'qty' AS qoh_xtnumericrole,
- 'qty' AS f_nnqoh_xtnumericrole,
+ 'qty' AS netqoh_xtnumericrole,
+ 'qty' AS nonnetqoh_xtnumericrole,
0 AS qoh_xttotalrole,
- 0 AS nnqoh_xttotalrole,
+ 0 AS netqoh_xttotalrole,
+ 0 AS nonnetqoh_xttotalrole,
'cost' AS cost_xtnumericrole,
'curr' AS value_xtnumericrole,
- 'curr' AS nnvalue_xtnumericrole,
+ 'curr' AS netvalue_xtnumericrole,
+ 'curr' AS nonnetvalue_xtnumericrole,
0 AS value_xttotalrole,
- 0 AS nnvalue_xttotalrole,
- <? value("na") ?> AS nnqoh_xtnullrole,
- <? value("na") ?> AS nnvalue_xtnullrole,
+ 0 AS netvalue_xttotalrole,
+ 0 AS nonnetvalue_xttotalrole,
+ <? value("na") ?> AS netqoh_xtnullrole,
+ <? value("na") ?> AS nonnetqoh_xtnullrole,
+ <? value("na") ?> AS netvalue_xtnullrole,
+ <? value("na") ?> AS nonnetvalue_xtnullrole,
CASE WHEN (qoh < 0) THEN 'error' END AS qoh_qtforegroundrole,
CASE WHEN (reorderlevel > qoh) THEN 'warning' END AS qoh_qtforegroundrole
FROM (
CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel,
<? if exists("asOf") ?>
COALESCE(invbal_qoh_ending,0) AS qoh,
- COALESCE(invbal_nn_ending,0) AS nnqoh,
+ COALESCE(invbal_qoh_ending,0) AS netqoh,
+ COALESCE(invbal_nn_ending,0) AS nonnetqoh,
<? else ?>
itemsite_qtyonhand AS qoh,
- itemsite_nnqoh AS nnqoh,
+ qtyNetable(itemsite_id) AS netqoh,
+ qtyNetable(itemsite_id, FALSE) AS nonnetqoh,
<? endif ?>
<? if exists("useStandardCosts") ?>
stdcost(item_id) AS cost
COALESCE((invbal_value_ending / CASE WHEN(invbal_qoh_ending=0) THEN 1
ELSE invbal_qoh_ending END),0) AS cost
<? else ?>
- (itemsite_value / CASE WHEN((itemsite_qtyonhand + itemsite_nnqoh)=0) THEN 1
- ELSE (itemsite_qtyonhand + itemsite_nnqoh) END) AS cost
+ (itemsite_value / CASE WHEN(itemsite_qtyonhand=0) THEN 1
+ ELSE itemsite_qtyonhand END) AS cost
<? endif ?>
<? endif ?>
FROM item, whsinfo, classcode, uom, costcat, itemsite
SELECT itemsite_id, itemtype, warehous_code, item_number,
(item_descrip1 || ' ' || item_descrip2) AS itemdescrip,
reorderdate, reorderlevel,
- (itemsite_qtyonhand - qtyAllocated(itemsite_id, reorderdate) +
+ (qtyNetable(itemsite_id) - qtyAllocated(itemsite_id, reorderdate) +
qtyOrdered(itemsite_id, reorderdate)) AS projavail,
'qty' AS reorderlevel_xtnumericrole,
'qty' AS projavail_xtnumericrole
<? value("lookAheadDays") ?>,
<? value("includePlannedOrders") ?>)
AS reorderdate,
- itemsite_qtyonhand, reorderlevel
+ reorderlevel
FROM (SELECT itemsite_id, itemsite_item_id,
- itemsite_warehous_id, itemsite_qtyonhand,
+ itemsite_warehous_id,
CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel
ELSE 0.0
END AS reorderlevel
ELSE 1
END AS xtindentrole,
spplytype, ordrnumbr,
- itemsite_qtyonhand,
- 'qty' AS itemsite_qtyonhand_xtnumericrole
+ netableqoh,
+ 'qty' AS netableqoh_xtnumericrole
<? if exists("includeReservations") ?>
,
reserved,
WHEN coitem_order_type='R' THEN (pr_number || '-' || pr_subnumber)
ELSE TEXT (' ')
END AS ordrnumbr,
- itemsite_qtyonhand
+ qtyNetable(itemsite_id) AS netableqoh
<? if exists("includeReservations") ?>
,
coitem_qtyreserved AS reserved,
- itemsite_qtyonhand - qtyreserved(itemsite_id) AS reservable
+ qtyNetable(itemsite_id) - qtyreserved(itemsite_id) AS reservable
<? endif?>
FROM cohead
JOIN coitem ON (coitem_cohead_id=cohead_id)
-- See www.xtuple.com/CPAL for the full text of the software license.
SELECT s_itemsite_id, warehous_code, item_number, itemdescrip,
- qtyonhand, reorderlevel, leadtime, itemsub_rank,
+ netableqoh, reorderlevel, leadtime, itemsub_rank,
allocated, ordered, available,
- 'qty' AS qtyonhand_xtnumericrole,
+ 'qty' AS netableqoh_xtnumericrole,
'qty' AS allocated_xtnumericrole,
'qty' AS ordered_xtnumericrole,
'qty' AS reorderlevel_xtnumericrole,
warehous_code, item_number,
(item_descrip1 || ' ' || item_descrip2) AS itemdescrip,
<? if exists("normalize") ?>
- (sub.itemsite_qtyonhand * itemsub_uomratio) AS qtyonhand,
+ (qtyNetable(sub.itemsite_id) * itemsub_uomratio) AS netableqoh,
(CASE WHEN(sub.itemsite_useparams)
THEN sub.itemsite_reorderlevel
ELSE 0.0
END * itemsub_uomratio) AS reorderlevel,
sub.itemsite_leadtime AS leadtime, itemsub_rank,
<? else ?>
- (sub.itemsite_qtyonhand) AS qtyonhand,
+ (qtyNetable(sub.itemsite_id)) AS netableqoh,
CASE WHEN(sub.itemsite_useparams) THEN sub.itemsite_reorderlevel
ELSE 0.0
END AS reorderlevel,
<? if exists("normalize") ?>
(qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime) * itemsub_uomratio) AS allocated,
(qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime) * itemsub_uomratio) AS ordered,
- ((sub.itemsite_qtyonhand * itemsub_uomratio)
+ ((qtyNetable(sub.itemsite_id) * itemsub_uomratio)
+ (qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime) * itemsub_uomratio)
- (qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime) * itemsub_uomratio)) AS available
<? else ?>
(qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime)) AS allocated,
(qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime)) AS ordered,
- (sub.itemsite_qtyonhand + qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime)
+ (qtyNetable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime)
- qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime)) AS available
<? endif ?>
<? elseif exists("byDays") ?>
<? if exists("normalize") ?>
(qtyAllocated(sub.itemsite_id, <? value("days") ?>) * itemsub_uomratio) AS allocated,
(qtyOrdered(sub.itemsite_id, <? value("days") ?>) * itemsub_uomratio) AS ordered,
- ((sub.itemsite_qtyonhand * itemsub_uomratio) + (qtyOrdered(sub.itemsite_id, <? value("days") ?>) * itemsub_uomratio)
+ ((qtyNetable(sub.itemsite_id) * itemsub_uomratio) + (qtyOrdered(sub.itemsite_id, <? value("days") ?>) * itemsub_uomratio)
- (qtyAllocated(sub.itemsite_id, <? value("days") ?>) * itemsub_uomratio)) AS available
<? else ?>
(qtyAllocated(sub.itemsite_id, <? value("days") ?>)) AS allocated,
(qtyOrdered(sub.itemsite_id, <? value("days") ?>)) AS ordered,
- (sub.itemsite_qtyonhand + qtyOrdered(sub.itemsite_id, <? value("days") ?>)
+ (qtyNetable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, <? value("days") ?>)
- qtyAllocated(sub.itemsite_id, <? value("days") ?>)) AS available
<? endif ?>
<? elseif exists("byDate") ?>
<? if exists("normalize") ?>
(qtyAllocated(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE)) * itemsub_uomratio) AS allocated,
(qtyOrdered(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE)) * itemsub_uomratio) AS ordered,
- ((sub.itemsite_qtyonhand * itemsub_uomratio) + (qtyOrdered(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE)) * itemsub_uomratio)
+ ((qtyNetable(sub.itemsite_id) * itemsub_uomratio) + (qtyOrdered(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE)) * itemsub_uomratio)
- (qtyAllocated(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE)) * itemsub_uomratio)) AS available
<? else ?>
(qtyAllocated(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE))) AS allocated,
(qtyOrdered(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE))) AS ordered,
- (sub.itemsite_qtyonhand + qtyOrdered(sub.itemsite_id,
+ (qtyNetable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id,
(<? value("date") ?> - CURRENT_DATE)) - qtyAllocated(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE))) AS available
<? endif ?>
<? endif ?>
(item_descrip1 || ' ' || item_descrip2) AS itemdescrip, uom_name,
itemsite_qtyonhand,
detailedQOH(itemsite_id, FALSE) AS detailedqoh,
- itemsite_nnqoh,
- detailedNNQOH(itemsite_id, FALSE) AS detailednnqoh,
'qty' AS itemsite_qtyonhand_xtnumericrole,
- 'qty' AS detailedqoh_xtnumericrole,
- 'qty' AS itemsite_nnqoh_xtnumericrole,
- 'qty' AS detailednnqoh_xtnumericrole
+ 'qty' AS detailedqoh_xtnumericrole
FROM whsinfo, item, itemsite, uom
WHERE ( (itemsite_item_id=item_id)
AND (item_inv_uom_id=uom_id)
AND (itemsite_warehous_id=warehous_id)
AND ((itemsite_loccntrl) OR (itemsite_controlmethod IN ('L', 'S')))
- AND ((itemsite_qtyonhand <> detailedQOH(itemsite_id, FALSE))
- OR (itemsite_nnqoh <> detailedNNQOH(itemsite_id, FALSE)))
+ AND (itemsite_qtyonhand <> detailedQOH(itemsite_id, FALSE))
<? if exists("classcode_id") ?>
AND (item_classcode_id=<? value("classcode_id") ?>)
<? elseif exists("classcode_pattern") ?>
END AS altId,
item_number, item_descrip1, item_descrip2, item_inv_uom_id,
warehous_id, warehous_code, itemsite_leadtime,
- itemsite_qtyonhand AS qtyonhand,
+ qtyNetable(itemsite_id) AS qtyonhand,
CASE WHEN itemsite_useparams THEN itemsite_reorderlevel
ELSE 0.0
END AS reorderlevel,
cohead_id, cohead_number, cust_number, cust_name,
item_number, (item_descrip1 || ' ' || item_descrip2) AS item_description,
uom_name, item_picklist,
- noNeg(itemsite_qtyonhand) AS qoh,
+ noNeg(qtyNetable(itemsite_id)) AS qoh,
noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) AS sobalance,
qtyAllocated(itemsite_id, coitem_scheddate) AS allocated,
qtyOrdered(itemsite_id, coitem_scheddate) AS ordered,
AND (wo_status IN ('E','R','I'))
AND (wo_qtyord-wo_qtyrcv > 0)
AND (noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned-qtyatshipping(coitem_id)) >
- (SELECT itemsite_qtyonhand FROM itemsite WHERE (itemsite_id=coitem_itemsite_id))))
+ (SELECT qtyNetable(itemsite_id) AS netableqoh FROM itemsite WHERE (itemsite_id=coitem_itemsite_id))))
<? endif ?>
WHERE((coitem_cohead_id=cohead_id)
AND (cohead_cust_id=cust_id)
FROM ( SELECT itemsite_id, coitem_id,
item_number, (item_descrip1 || ' ' || item_descrip2) AS item_description,
uom_name, item_picklist,
- noNeg(itemsite_qtyonhand) AS qoh,
+ noNeg(qtyNetable(itemsite_id)) AS qoh,
noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) AS sobalance,
qtyAllocated(itemsite_id, coitem_scheddate) AS allocated,
qtyOrdered(itemsite_id, coitem_scheddate) AS ordered,
AND (wo_status IN ('E','R','I'))
AND (wo_qtyord-wo_qtyrcv > 0)
AND (noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned-qtyatshipping(coitem_id)) >
- (SELECT itemsite_qtyonhand FROM itemsite WHERE (itemsite_id=coitem_itemsite_id))))
+ (SELECT qtyNetable(itemsite_id) AS netableqoh FROM itemsite WHERE (itemsite_id=coitem_itemsite_id))))
<? endif ?>
WHERE ( (coitem_cohead_id=cohead_id)
AND (coitem_itemsite_id=itemsite_id)
END AS altId,
item_number, item_descrip1, item_descrip2, item_inv_uom_id,
warehous_id, warehous_code, itemsite_leadtime,
- itemsite_qtyonhand AS qtyonhand,
+ qtyNetable(itemsite_id) AS qtyonhand,
CASE WHEN itemsite_useparams THEN itemsite_reorderlevel
ELSE 0.0
END AS reorderlevel,
AND (invcitem_warehous_id=itemsite_warehous_id)
AND (invcitem_linenumber=coitem_linenumber)
AND (cohead_id=coitem_cohead_id))) > 0)) THEN 'P'
- WHEN (coitem_status='O' AND (itemsite_qtyonhand - qtyAllocated(itemsite_id, CURRENT_DATE)
+ WHEN (coitem_status='O' AND (qtyNetable(itemsite_id) - qtyAllocated(itemsite_id, CURRENT_DATE)
+ qtyOrdered(itemsite_id, CURRENT_DATE))
>= (coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned)) THEN 'R'
ELSE coitem_status
<sql>SELECT warehous_code, item_number, item_descrip1, item_descrip2,
formatDate(reorderdate) AS f_reorderdate,
formatQty(reorderlevel) AS f_reorderlevel,
- formatQty((itemsite_qtyonhand - qtyAllocated(itemsite_id, reorderdate) + qtyOrdered(itemsite_id, reorderdate))) AS f_projavail,
+ formatQty((qtyNetable(itemsite_id) - qtyAllocated(itemsite_id, reorderdate) + qtyOrdered(itemsite_id, reorderdate))) AS f_projavail,
reorderdate
FROM ( SELECT itemsite_id,
CASE WHEN (item_type IN ('M', 'B', 'T')) THEN 1
ELSE 3
END AS itemtype,
warehous_code, item_number, item_descrip1, item_descrip2,
- reorderDate(itemsite_id, <? value("lookAheadDays") ?>, <? if exists("includePlannedOrder") ?>true<? else ?>false<? endif ?>) AS reorderdate, itemsite_qtyonhand,
+ reorderDate(itemsite_id, <? value("lookAheadDays") ?>, <? if exists("includePlannedOrder") ?>true<? else ?>false<? endif ?>) AS reorderdate,
reorderlevel
FROM ( SELECT itemsite_id, itemsite_item_id, itemsite_warehous_id,
- itemsite_qtyonhand,
CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel
FROM itemsite
WHERE ((true)
<querysource>
<name>info</name>
<sql>SELECT
- formatQty(itemsite_qtyonhand) AS qoh,
+ formatQty(qtyNetable(itemsite_id)) AS qoh,
formatQty(CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END) AS reorderlevel,
formatQty(CASE WHEN(itemsite_useparams) THEN itemsite_ordertoqty ELSE 0.0 END) AS ordertoqty,
formatQty(CASE WHEN(itemsite_useparams) THEN itemsite_multordqty ELSE 0.0 END) AS multorderqty
item_number,
item_descrip1,
item_descrip2,
- formatQty(sub.itemsite_qtyonhand) AS f_qtyonhand,
+ formatQty(qtyNetable(sub.itemsite_id)) AS f_qtyonhand,
formatQty(CASE WHEN(sub.itemsite_useparams) THEN sub.itemsite_reorderlevel ELSE 0.0 END) AS f_reorderlevel,
sub.itemsite_leadtime as leadtime,
<? if exists("byDays") ?>
formatQty(qtyAllocated(sub.itemsite_id, <? value("byDays") ?>)) AS f_allocated,
formatQty(qtyOrdered(sub.itemsite_id, <? value("byDays") ?>)) AS f_ordered,
- formatQty(sub.itemsite_qtyonhand + qtyOrdered(sub.itemsite_id, <? value("byDays") ?>) - qtyAllocated(sub.itemsite_id, <? value("byDays") ?>)) as f_avail
+ formatQty(qtyNetable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, <? value("byDays") ?>) - qtyAllocated(sub.itemsite_id, <? value("byDays") ?>)) as f_avail
<? elseif exists("byDate") ?>
formatQty(qtyAllocated(sub.itemsite_id, (<? value("byDate") ?> - CURRENT_DATE))) AS f_allocated,
formatQty(qtyOrdered(sub.itemsite_id, (<? value("byDate") ?> - CURRENT_DATE))) AS f_ordered,
- formatQty(sub.itemsite_qtyonhand + qtyOrdered(sub.itemsite_id, (<? value("byDate") ?> - CURRENT_DATE)) - qtyAllocated(sub.itemsite_id, (<? value("byDate") ?> - CURRENT_DATE))) as f_avail
+ formatQty(qtyNetable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, (<? value("byDate") ?> - CURRENT_DATE)) - qtyAllocated(sub.itemsite_id, (<? value("byDate") ?> - CURRENT_DATE))) as f_avail
<? else ?>
formatQty(qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime)) AS f_allocated,
formatQty(qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime)) AS f_ordered,
- formatQty(sub.itemsite_qtyonhand + qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime) - qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime)) as f_avail
+ formatQty(qtyNetable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime) - qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime)) as f_avail
<? endif ?>
FROM item, itemsite AS sub, itemsite AS root, whsinfo, itemsub
WHERE ((sub.itemsite_item_id=item_id)
RAISE NOTICE 'Deleting item site detail records,';
- SELECT SUM(itemloc_qty) INTO _qty
- FROM itemloc, location
- WHERE ((itemloc_location_id=location_id)
- AND (NOT location_netable)
- AND (itemloc_itemsite_id=OLD.itemsite_id));
-
- IF (_qty != 0) THEN
- UPDATE itemsite
- SET itemsite_qtyonhand = itemsite_qtyonhand + _qty,
- itemsite_nnqoh = itemsite_nnqoh - _qty
- WHERE (itemsite_id=OLD.itemsite_id);
- END IF;
-
DELETE FROM itemloc
WHERE (itemloc_itemsite_id=OLD.itemsite_id);
END IF;
BEGIN
- -- Maintain itemsite_qtyonhand and itemsite_nnqoh when location_netable changes
- IF (TG_OP = 'UPDATE') THEN
- IF (OLD.location_netable <> NEW.location_netable) THEN
- FOR _itemloc IN SELECT * FROM itemloc WHERE (itemloc_location_id=NEW.location_id) LOOP
- IF (NEW.location_netable) THEN
- UPDATE itemsite SET itemsite_qtyonhand = itemsite_qtyonhand + _itemloc.itemloc_qty,
- itemsite_nnqoh = itemsite_nnqoh - _itemloc.itemloc_qty
- WHERE (itemsite_id=_itemloc.itemloc_itemsite_id);
- ELSE
- UPDATE itemsite SET itemsite_qtyonhand = itemsite_qtyonhand - _itemloc.itemloc_qty,
- itemsite_nnqoh = itemsite_nnqoh + _itemloc.itemloc_qty
- WHERE (itemsite_id=_itemloc.itemloc_itemsite_id);
- END IF;
- END LOOP;
- END IF;
- END IF;
-
RETURN NEW;
END;
projects / xtuple / commitdiff