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 (qtyNetable(itemsite_id) - qtyAllocated(itemsite_id, CURRENT_DATE)
+ WHEN (coitem_status='O' AND (qtyAvailable(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,qtyNetable(itemsite_id) AS netableqoh,
+ wo_duedate,wo_adhoc,wo_itemsite_id,qtyAvailable(itemsite_id) AS availableqoh,
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.netableqoh;
+ _row.wodata_qoh := _x.availableqoh;
_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,qtyNetable(itemsite_id) AS netableqoh,wo_qtyord,wo_qtyrcv, wo_prodnotes,
+ wo_adhoc,wo_itemsite_id,qtyAvailable(itemsite_id) AS availableqoh,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.netableqoh;
+ _row.wodata_qoh := _x.availableqoh;
_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,
- qtyNetable(itemsite_id) AS netableqoh, womatl_qtyreq, womatl_qtyiss,
+ qtyAvailable(itemsite_id) AS availableqoh, 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.netableqoh;
- IF((_subx.netableqoh > (_subx.womatl_qtyreq - _subx.womatl_qtyiss))) THEN
+ _subrow.wodata_qoh := _subx.availableqoh;
+ IF((_subx.availableqoh > (_subx.womatl_qtyreq - _subx.womatl_qtyiss))) THEN
_subrow.wodata_short := 0;
ELSE
- _subrow.wodata_short := (_subx.womatl_qtyreq - _subx.womatl_qtyiss) - _subx.netableqoh;
+ _subrow.wodata_short := (_subx.womatl_qtyreq - _subx.womatl_qtyiss) - _subx.availableqoh;
END IF;
_subrow.wodata_qtyper := _subx.womatl_qtyper;
_subrow.wodata_qtyiss := _subx.womatl_qtyiss;
-CREATE OR REPLACE FUNCTION qtyAvailable(INTEGER, INTEGER) RETURNS NUMERIC AS '
+
+CREATE OR REPLACE FUNCTION qtyAvailable(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 := qtyAvailable(pItemsiteId, TRUE);
+
+ RETURN _qty;
+
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION qtyAvailable(pItemsiteId INTEGER,
+ pUsable 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 (pUsable) THEN
+ -- Summarize itemloc qty for this itemsite/usable 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 (COALESCE(location_usable, true)))
+ GROUP BY itemsite_qtyonhand;
+ ELSE
+ -- Summarize itemloc qty for this itemsite/non-usable 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 COALESCE(location_usable, true));
+ END IF;
+
+ RETURN _qty;
+
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION qtyAvailable(pItemsiteid INTEGER,
+ pLookAheadDays 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
- pItemsiteid ALIAS FOR $1;
- pLookAheadDays ALIAS FOR $2;
BEGIN
- RETURN ( ( SELECT qtyNetable(itemsite_id)
+ RETURN ( ( SELECT qtyAvailable(itemsite_id)
FROM itemsite
WHERE (itemsite_id=pItemsiteid) ) +
(SELECT qtyOrdered(pItemsiteid, pLookAheadDays)) -
(SELECT qtyAllocated(pitemsiteid, pLookAheadDays)) );
END;
-' LANGUAGE 'plpgsql' STABLE;
+$$ LANGUAGE plpgsql;
-CREATE OR REPLACE FUNCTION qtyAvailable(INTEGER, DATE) RETURNS NUMERIC AS '
+CREATE OR REPLACE FUNCTION qtyAvailable(pItemsiteid INTEGER,
+ pDate DATE) 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
- pItemsiteid ALIAS FOR $1;
- pDate ALIAS FOR $2;
BEGIN
- RETURN ( ( SELECT qtyNetable(itemsite_id)
+ RETURN ( ( SELECT qtyAvailable(itemsite_id)
FROM itemsite
WHERE (itemsite_id=pItemsiteid) ) +
(SELECT qtyOrdered(pItemsiteid, (pDate - CURRENT_DATE))) -
(SELECT qtyAllocated(pItemsiteid, (pDate - CURRENT_DATE))) );
END;
-' LANGUAGE 'plpgsql' STABLE;
+$$ LANGUAGE plpgsql;
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))
+ AND ((location_id IS NULL) OR (COALESCE(location_netable, true)))
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);
+ AND (NOT COALESCE(location_netable, true));
END IF;
RETURN _qty;
noNeg(coitem_qtyord - coitem_qtyshipped +
coitem_qtyreturned - qtyAtShipping(pordertype, coitem_id)
))) - coitem_qtyreserved) * coitem_qty_invuomratio
- ) <= qtyNetable(itemsite_id))
+ ) <= qtyAvailable(itemsite_id))
AND
(((COALESCE(pqty, roundQty(item_fractional,
noNeg(coitem_qtyord - coitem_qtyshipped +
coitem_qtyreturned - qtyAtShipping(pordertype, coitem_id) - coitem_qtyreserved
) * coitem_qty_invuomratio
)
- ) <= qtyNetable(itemsite_id))
+ ) <= qtyAvailable(itemsite_id))
INTO _isqtyavail
FROM coitem, itemsite, item
WHERE ((coitem_itemsite_id=itemsite_id)
qtyAtShipping(pordertype, toitem_id)
)
)
- ) <= qtyNetable(itemsite_id)) INTO _isqtyavail
+ ) <= qtyAvailable(itemsite_id)) INTO _isqtyavail
FROM toitem, tohead, itemsite, item
WHERE ((toitem_tohead_id=tohead_id)
AND (tohead_src_warehous_id=itemsite_warehous_id)
item_descrip1,
item_descrip2,
uom_name,
- noNeg(qtyNetable(itemsite_id)) AS qoh,
+ noNeg(qtyAvailable(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(qtyNetable(itemsite_id)) AS qoh,
+ noNeg(qtyAvailable(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(qtyNetable(itemsite_id)) AS qoh,
+ noNeg(qtyAvailable(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(qtyNetable(itemsite_id)) AS qoh,
+ noNeg(qtyAvailable(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,
select xt.add_index('location', 'location_formatname','location_location_formatname_idx', 'btree', 'public');
UPDATE location SET location_formatname=formatLocationName(location_id) WHERE location_formatname IS NULL;
+-- incident 23507:change how qoh, qoh available, and qoh netable are determined
+select xt.add_column('location','location_usable', 'BOOLEAN', NULL, 'public');
+do $$
+begin
+if fetchMetricText('ServerVersion') < '4.7.0' then
+ update location set location_usable=true;
+ update itemsite set itemsite_qtyonhand=(itemsite_qtyonhand + itemsite_nnqoh);
+ -- drop column itemsite_nnqoh ???
+end if;
+end$$;
\ No newline at end of file
-- See www.xtuple.com/CPAL for the full text of the software license.
SELECT id, type, locationname, defaultlocation,
- location_netable, lotserial, f_expiration, expired,
- qty, qtytagged, (qty + qtytagged) AS balance,
- 'qty' AS qty_xtnumericrole,
- 'qty' AS qtytagged_xtnumericrole,
- 'qty' AS balance_xtnumericrole,
- CASE WHEN expired THEN 'error' END AS qtforegroundrole,
- CASE WHEN expired THEN 'error'
- WHEN defaultlocation AND expired = false THEN 'altemphasis'
- ELSE null END AS defaultlocation_qtforegroundrole,
- CASE WHEN expired THEN 'error'
- WHEN qty > 0 AND expired = false THEN 'altemphasis'
- ELSE null END AS qty_qtforegroundrole
- FROM (
- <? if exists("cNoIncludeLotSerial") ?>
- SELECT location_id AS id, <? value("locationType") ?> AS type,
- formatLocationName(location_id) AS locationname,
- CASE WHEN (<? value("transtype") ?> = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE
- WHEN (<? value("transtype") ?> = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE
- WHEN (<? value("transtype") ?> = 'O' AND location_id=itemsite_location_id) THEN TRUE
- ELSE FALSE
- END AS defaultlocation,
- location_netable,
- TEXT('') AS lotserial,
- TEXT(<? value("na") ?>) AS f_expiration, FALSE AS expired,
- qtyLocation(location_id, NULL, NULL, NULL, itemsite_id, itemlocdist_order_type, itemlocdist_order_id, itemlocdist_id) AS qty,
- itemlocdistQty(location_id, itemlocdist_id) AS qtytagged
- FROM itemlocdist, location, itemsite
- WHERE ( (itemlocdist_itemsite_id=itemsite_id)
- AND (itemsite_loccntrl)
- AND (itemsite_warehous_id=location_warehous_id)
- AND (validLocation(location_id, itemsite_id))
- AND (itemlocdist_id=<? value("itemlocdist_id") ?>) )
- <? elseif exists("cIncludeLotSerial") ?>
- SELECT itemloc_id AS id, <? value("itemlocType") ?> AS type,
- COALESCE(formatLocationName(location_id),
- <? value("undefined") ?>) AS locationname,
- (location_id IS NOT NULL AND
- CASE WHEN (<? value("transtype") ?> = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE
- WHEN (<? value("transtype") ?> = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE
- WHEN (<? value("transtype") ?> = 'O' AND location_id=itemsite_location_id) THEN TRUE
- ELSE FALSE
- END) AS defaultlocation,
- COALESCE(location_netable, false) AS location_netable,
- ls_number AS lotserial,
- CASE WHEN (itemsite_perishable) THEN formatDate(itemloc_expiration)
- ELSE <? value("na") ?>
- END AS f_expiration,
- CASE WHEN (itemsite_perishable) THEN (itemloc_expiration < CURRENT_DATE)
- ELSE FALSE
- END AS expired,
- qtyLocation(itemloc_location_id, itemloc_ls_id, itemloc_expiration, itemloc_warrpurc, itemsite_id, itemlocdist_order_type, itemlocdist_order_id, itemlocdist_id) AS qty,
- ( SELECT COALESCE(SUM(target.itemlocdist_qty), 0)
- FROM itemlocdist AS target
- WHERE ( (target.itemlocdist_source_type='I')
- AND (target.itemlocdist_source_id=itemloc_id)
- AND (target.itemlocdist_itemlocdist_id=source.itemlocdist_id)) ) AS qtytagged
- FROM itemlocdist AS source, itemsite, itemloc
- LEFT OUTER JOIN location ON (itemloc_location_id=location_id)
- LEFT OUTER JOIN ls ON (itemloc_ls_id=ls_id)
- WHERE ( (source.itemlocdist_itemsite_id=itemsite_id)
- AND (itemloc_itemsite_id=itemsite_id)
- AND (source.itemlocdist_id=<? value("itemlocdist_id") ?>) )
- UNION
- SELECT location_id AS id, <? value("locationType") ?> AS type,
- formatLocationName(location_id) AS locationname,
- CASE WHEN (<? value("transtype") ?> = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE
- WHEN (<? value("transtype") ?> = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE
- WHEN (<? value("transtype") ?> = 'O' AND location_id=itemsite_location_id) THEN TRUE
- ELSE FALSE
- END AS defaultlocation,
- location_netable,
- TEXT('') AS lotserial,
- TEXT(<? value("na") ?>) AS f_expiration, FALSE AS expired,
- qtyLocation(location_id, NULL, NULL, NULL, itemsite_id, itemlocdist_order_type, itemlocdist_order_id, itemlocdist_id) AS qty,
- itemlocdistQty(location_id, itemlocdist_id) AS qtytagged
- FROM itemlocdist, location, itemsite
- WHERE ( (itemlocdist_itemsite_id=itemsite_id)
- AND (itemsite_loccntrl)
- AND (itemsite_warehous_id=location_warehous_id)
- AND (validLocation(location_id, itemsite_id))
- AND (itemsite_id=<? value("itemsite_id") ?> )
- AND (location_id NOT IN (SELECT DISTINCT itemloc_location_id FROM itemloc WHERE (itemloc_itemsite_id=itemsite_id)))
- AND (itemlocdist_id=<? value("itemlocdist_id") ?>) )
- <? endif ?>
- ) AS data
- WHERE ((TRUE)
- <? if exists("showOnlyTagged") ?>
- AND (qtytagged != 0)
- <? endif ?>
- <? if exists("showQtyOnly") ?>
- AND (qty > 0)
- <? endif ?>
- ) ORDER BY locationname;
+ location_netable, location_usable, lotserial, f_expiration, expired,
+ qty, qtytagged, (qty + qtytagged) AS balance,
+ 'qty' AS qty_xtnumericrole,
+ 'qty' AS qtytagged_xtnumericrole,
+ 'qty' AS balance_xtnumericrole,
+ CASE WHEN expired THEN 'error' END AS qtforegroundrole,
+ CASE WHEN defaultlocation AND expired = false THEN 'altemphasis'
+ WHEN expired THEN 'error'
+ ELSE null END AS defaultlocation_qtforegroundrole,
+ CASE WHEN expired THEN 'error'
+ WHEN qty > 0 AND expired = false THEN 'altemphasis'
+ ELSE null END AS qty_qtforegroundrole
+FROM (
+ <? if exists("cNoIncludeLotSerial") ?>
+ SELECT location_id AS id, <? value("locationType") ?> AS type,
+ formatLocationName(location_id) AS locationname,
+ CASE WHEN (<? value("transtype") ?> = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE
+ WHEN (<? value("transtype") ?> = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE
+ WHEN (<? value("transtype") ?> = 'O' AND location_id=itemsite_location_id) THEN TRUE
+ ELSE FALSE
+ END AS defaultlocation,
+ COALESCE(location_netable, true) AS location_netable,
+ COALESCE(location_usable, true) AS location_usable,
+ TEXT('') AS lotserial,
+ TEXT(<? value("na") ?>) AS f_expiration, FALSE AS expired,
+ qtyLocation(location_id, NULL, NULL, NULL,
+ itemsite_id, itemlocdist_order_type, itemlocdist_order_id, itemlocdist_id) AS qty,
+ itemlocdistQty(location_id, itemlocdist_id) AS qtytagged
+ FROM itemlocdist, location, itemsite
+ WHERE ( (itemlocdist_itemsite_id=itemsite_id)
+ AND (itemsite_loccntrl)
+ AND (itemsite_warehous_id=location_warehous_id)
+ AND (validLocation(location_id, itemsite_id))
+ AND (itemlocdist_id=<? value("itemlocdist_id") ?>) )
+ <? elseif exists("cIncludeLotSerial") ?>
+ SELECT itemloc_id AS id, <? value("itemlocType") ?> AS type,
+ COALESCE(formatLocationName(location_id),
+ <? value("undefined") ?>) AS locationname,
+ (location_id IS NOT NULL AND
+ CASE WHEN (<? value("transtype") ?> = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE
+ WHEN (<? value("transtype") ?> = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE
+ WHEN (<? value("transtype") ?> = 'O' AND location_id=itemsite_location_id) THEN TRUE
+ ELSE FALSE
+ END) AS defaultlocation,
+ COALESCE(location_netable, true) AS location_netable,
+ COALESCE(location_usable, true) AS location_usable,
+ ls_number AS lotserial,
+ CASE WHEN (itemsite_perishable) THEN formatDate(itemloc_expiration)
+ ELSE <? value("na") ?>
+ END AS f_expiration,
+ CASE WHEN (itemsite_perishable) THEN (itemloc_expiration < CURRENT_DATE)
+ ELSE FALSE
+ END AS expired,
+ qtyLocation(itemloc_location_id, itemloc_ls_id, itemloc_expiration,
+ itemloc_warrpurc, itemsite_id, itemlocdist_order_type,
+ itemlocdist_order_id, itemlocdist_id) AS qty,
+ ( SELECT COALESCE(SUM(target.itemlocdist_qty), 0)
+ FROM itemlocdist AS target
+ WHERE ( (target.itemlocdist_source_type='I')
+ AND (target.itemlocdist_source_id=itemloc_id)
+ AND (target.itemlocdist_itemlocdist_id=source.itemlocdist_id)) ) AS qtytagged
+ FROM itemlocdist AS source, itemsite, itemloc
+ LEFT OUTER JOIN location ON (itemloc_location_id=location_id)
+ LEFT OUTER JOIN ls ON (itemloc_ls_id=ls_id)
+ WHERE ( (source.itemlocdist_itemsite_id=itemsite_id)
+ AND (itemloc_itemsite_id=itemsite_id)
+ AND (source.itemlocdist_id=<? value("itemlocdist_id") ?>) )
+ UNION
+ SELECT location_id AS id, <? value("locationType") ?> AS type,
+ formatLocationName(location_id) AS locationname,
+ CASE WHEN (<? value("transtype") ?> = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE
+ WHEN (<? value("transtype") ?> = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE
+ WHEN (<? value("transtype") ?> = 'O' AND location_id=itemsite_location_id) THEN TRUE
+ ELSE FALSE
+ END AS defaultlocation,
+ COALESCE(location_netable, true) AS location_netable,
+ COALESCE(location_usable, true) AS location_usable,
+ TEXT('') AS lotserial,
+ TEXT(<? value("na") ?>) AS f_expiration, FALSE AS expired,
+ qtyLocation(location_id, NULL, NULL, NULL,
+ itemsite_id, itemlocdist_order_type, itemlocdist_order_id, itemlocdist_id) AS qty,
+ itemlocdistQty(location_id, itemlocdist_id) AS qtytagged
+ FROM itemlocdist, location, itemsite
+ WHERE ( (itemlocdist_itemsite_id=itemsite_id)
+ AND (itemsite_loccntrl)
+ AND (itemsite_warehous_id=location_warehous_id)
+ AND (validLocation(location_id, itemsite_id))
+ AND (itemsite_id=<? value("itemsite_id") ?> )
+ AND (location_id NOT IN (SELECT DISTINCT itemloc_location_id
+ FROM itemloc
+ WHERE (itemloc_itemsite_id=itemsite_id)))
+ AND (itemlocdist_id=<? value("itemlocdist_id") ?>) )
+ <? endif ?>
+ ) AS data
+WHERE ((TRUE)
+ AND ( (<? value("transtype") ?> != 'I') OR (<? value("transtype") ?> = 'I' AND location_usable) )
+<? if exists("showOnlyTagged") ?>
+ AND (qtytagged != 0)
+<? endif ?>
+<? if exists("showQtyOnly") ?>
+ AND (qty > 0)
+<? endif ?>
+) ORDER BY locationname;
GROUP BY cohead_id, item_number, cust_number,
cust_name, cohead_orderdate, pack_id, coitem_scheddate
<? if exists("onlyShowShortages") ?>
- HAVING (MIN(noNeg(qtyNetable(itemsite_id)) +
+ HAVING (MIN(noNeg(qtyAvailable(itemsite_id)) +
qtyOrdered(itemsite_id, coitem_scheddate) -
qtyAllocated(itemsite_id, coitem_scheddate)) < 0
- OR MIN(noNeg(qtyNetable(itemsite_id)) +
+ OR MIN(noNeg(qtyAvailable(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(qtyNetable(itemsite_id)) AS qoh,
+ uom_name, noNeg(qtyAvailable(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(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))
+ AND ((noNeg(qtyAvailable(itemsite_id)) + qtyOrdered(itemsite_id, coitem_scheddate) - qtyAllocated(itemsite_id, coitem_scheddate) < 0)
+ OR (noNeg(qtyAvailable(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 qtyNetable(itemsite_id) FROM itemsite WHERE (itemsite_id=coitem_itemsite_id))))
+ (SELECT qtyAvailable(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(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))
+ AND ((noNeg(qtyAvailable(itemsite_id)) + qtyOrdered(itemsite_id, coitem_scheddate) - qtyAllocated(itemsite_id, coitem_scheddate) < 0)
+ OR (noNeg(qtyAvailable(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,
- qtyNetable(itemsite_id) AS qoh,
+ qtyAvailable(itemsite_id) AS qoh,
CASE WHEN itemsite_useparams THEN itemsite_reorderlevel
ELSE 0.0
END AS reorderlevel,
SELECT *,
<? value("na") ?> AS locationname_xtnullrole,
<? value("na") ?> AS netable_xtnullrole,
+ <? value("na") ?> AS usable_xtnullrole,
<? value("na") ?> AS lotserial_xtnullrole,
<? value("na") ?> AS expiration_xtnullrole,
<? value("na") ?> AS warranty_xtnullrole,
END AS locationname,
CASE WHEN (location_id IS NOT NULL) THEN location_netable
END AS netable,
+ CASE WHEN (location_id IS NOT NULL) THEN location_usable
+ END AS usable,
CASE WHEN (itemsite_controlmethod IN ('L', 'S')) THEN
formatlotserialnumber(itemloc_ls_id)
END AS lotserial,
itemsite_warrpurc, NULL AS itemloc_warrpurc,
NULL AS locationname,
NULL AS netable,
+ NULL AS usable,
NULL AS lotserial,
NULL AS expiration,
NULL AS warranty,
ib.*,
((bomdata_qtyreq::NUMERIC * <? value("buildQty") ?>) * (1 + bomdata_scrap::NUMERIC)) AS pendalloc,
qtyAllocated(itemsite_id, DATE(<? value("buildDate") ?>)) AS totalalloc,
- noNeg(qtyNetable(itemsite_id)) AS qoh,
+ noNeg(qtyAvailable(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(qtyNetable(itemsite_id)) AS qoh,
+ noNeg(qtyAvailable(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
defaultlocation,
reorderlevel, formatQty(reorderlevel) AS f_reorderlevel,
qoh, formatQty(qoh) AS f_qoh,
+ availqoh, formatQty(availqoh) AS f_availqoh,
+ nonavailqoh, formatQty(nonavailqoh) AS f_nonavailqoh,
netqoh, formatQty(netqoh) AS f_netqoh,
- nonnetqoh, formatQty(netqoh) AS f_nonnetqoh,
+ nonnetqoh, formatQty(nonnetqoh) AS f_nonnetqoh,
cost, (cost * qoh) AS value,
+ (cost * availqoh) AS availvalue,
+ (cost * nonavailqoh) AS nonavailvalue,
(cost * netqoh) AS netvalue,
(cost * nonnetqoh) AS nonnetvalue,
CASE WHEN(itemsite_costmethod='A') THEN 'Average'
END AS costmethod,
<? if exists("showValue") ?>
formatMoney(cost) AS f_cost, (cost * qoh) AS f_value,
+ formatMoney(cost * availqoh) AS f_availvalue,
+ formatMoney(cost * nonavailqoh) AS f_nonavailvalue,
formatMoney(cost * netqoh) AS f_netvalue,
formatMoney(cost * nonnetqoh) AS f_nonnetvalue,
CASE WHEN(itemsite_costmethod='A') THEN 'Average'
<? endif ?>
'qty' AS reorderlevel_xtnumericrole,
'qty' AS qoh_xtnumericrole,
+ 'qty' AS availqoh_xtnumericrole,
+ 'qty' AS nonavailqoh_xtnumericrole,
'qty' AS netqoh_xtnumericrole,
'qty' AS nonnetqoh_xtnumericrole,
0 AS qoh_xttotalrole,
+ 0 AS availqoh_xttotalrole,
+ 0 AS nonavailqoh_xttotalrole,
0 AS netqoh_xttotalrole,
0 AS nonnetqoh_xttotalrole,
'cost' AS cost_xtnumericrole,
'curr' AS value_xtnumericrole,
+ 'curr' AS availvalue_xtnumericrole,
+ 'curr' AS nonavailvalue_xtnumericrole,
'curr' AS netvalue_xtnumericrole,
'curr' AS nonnetvalue_xtnumericrole,
0 AS value_xttotalrole,
+ 0 AS availvalue_xttotalrole,
+ 0 AS nonavailvalue_xttotalrole,
0 AS netvalue_xttotalrole,
0 AS nonnetvalue_xttotalrole,
+ <? value("na") ?> AS availqoh_xtnullrole,
+ <? value("na") ?> AS nonavailqoh_xtnullrole,
+ <? value("na") ?> AS availvalue_xtnullrole,
+ <? value("na") ?> AS nonavailvalue_xtnullrole,
<? value("na") ?> AS netqoh_xtnullrole,
<? value("na") ?> AS nonnetqoh_xtnullrole,
<? value("na") ?> AS netvalue_xtnullrole,
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_qoh_ending,0) AS availqoh,
+ COALESCE(invbal_nn_ending,0) AS nonavailqoh,
COALESCE(invbal_qoh_ending,0) AS netqoh,
COALESCE(invbal_nn_ending,0) AS nonnetqoh,
<? else ?>
itemsite_qtyonhand AS qoh,
+ qtyAvailable(itemsite_id) AS availqoh,
+ qtyAvailable(itemsite_id, FALSE) AS nonavailqoh,
qtyNetable(itemsite_id) AS netqoh,
qtyNetable(itemsite_id, FALSE) AS nonnetqoh,
<? endif ?>
SELECT orderid, altorderid, ordertype, ordernumber, sequence, item_number,
duedate, amount, qtyordered, qtyreceived, balance,
- balance AS runningavail,
+ balance AS runningavail, balance AS runningnetable,
<? if exists("isReport") ?>
CASE WHEN duedate = startOfTime() THEN ''
ELSE formatDate(duedate) END AS f_duedate,
<? endif ?>
notes,
1 AS runningavail_xtrunningrole,
+ 1 AS runningnetable_xtrunningrole,
<? value("qoh") ?> AS runningavail_xtrunninginit,
+ <? value("netableqoh") ?> AS runningnetable_xtrunninginit,
CASE WHEN late THEN 'error' END AS duedate_qtforegroundrole,
CASE WHEN duedate = startOfTime() THEN '' END AS duedate_qtdisplayrole,
CASE WHEN ordertype ~ <? value("plannedPo") ?> OR
ELSE 1
END AS xtindentrole,
spplytype, ordrnumbr,
- netableqoh,
- 'qty' AS netableqoh_xtnumericrole
+ availableqoh,
+ 'qty' AS availableqoh_xtnumericrole
<? if exists("includeReservations") ?>
,
reserved,
WHEN coitem_order_type='R' THEN (pr_number || '-' || pr_subnumber)
ELSE TEXT (' ')
END AS ordrnumbr,
- qtyNetable(itemsite_id) AS netableqoh
+ qtyAvailable(itemsite_id) AS availableqoh
<? if exists("includeReservations") ?>
,
coitem_qtyreserved AS reserved,
- qtyNetable(itemsite_id) - qtyreserved(itemsite_id) AS reservable
+ qtyAvailable(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,
- netableqoh, reorderlevel, leadtime, itemsub_rank,
+ availableqoh, reorderlevel, leadtime, itemsub_rank,
allocated, ordered, available,
- 'qty' AS netableqoh_xtnumericrole,
+ 'qty' AS availableqoh_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") ?>
- (qtyNetable(sub.itemsite_id) * itemsub_uomratio) AS netableqoh,
+ (qtyAvailable(sub.itemsite_id) * itemsub_uomratio) AS availableqoh,
(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 ?>
- (qtyNetable(sub.itemsite_id)) AS netableqoh,
+ (qtyAvailable(sub.itemsite_id)) AS availableqoh,
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,
- ((qtyNetable(sub.itemsite_id) * itemsub_uomratio)
+ ((qtyAvailable(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,
- (qtyNetable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime)
+ (qtyAvailable(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,
- ((qtyNetable(sub.itemsite_id) * itemsub_uomratio) + (qtyOrdered(sub.itemsite_id, <? value("days") ?>) * itemsub_uomratio)
+ ((qtyAvailable(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,
- (qtyNetable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, <? value("days") ?>)
+ (qtyAvailable(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,
- ((qtyNetable(sub.itemsite_id) * itemsub_uomratio) + (qtyOrdered(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE)) * itemsub_uomratio)
+ ((qtyAvailable(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,
- (qtyNetable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id,
+ (qtyAvailable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id,
(<? value("date") ?> - CURRENT_DATE)) - qtyAllocated(sub.itemsite_id, (<? value("date") ?> - CURRENT_DATE))) AS available
<? endif ?>
<? endif ?>
END AS altId,
item_number, item_descrip1, item_descrip2, item_inv_uom_id,
warehous_id, warehous_code, itemsite_leadtime,
- qtyNetable(itemsite_id) AS qtyonhand,
+ qtyAvailable(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(qtyNetable(itemsite_id)) AS qoh,
+ noNeg(qtyAvailable(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 qtyNetable(itemsite_id) AS netableqoh FROM itemsite WHERE (itemsite_id=coitem_itemsite_id))))
+ (SELECT qtyAvailable(itemsite_id) AS availableqoh 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(qtyNetable(itemsite_id)) AS qoh,
+ noNeg(qtyAvailable(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 qtyNetable(itemsite_id) AS netableqoh FROM itemsite WHERE (itemsite_id=coitem_itemsite_id))))
+ (SELECT qtyAvailable(itemsite_id) AS availableqoh 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,
- qtyNetable(itemsite_id) AS qtyonhand,
+ qtyAvailable(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 (qtyNetable(itemsite_id) - qtyAllocated(itemsite_id, CURRENT_DATE)
+ WHEN (coitem_status='O' AND (qtyAvailable(itemsite_id) - qtyAllocated(itemsite_id, CURRENT_DATE)
+ qtyOrdered(itemsite_id, CURRENT_DATE))
>= (coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned)) THEN 'R'
ELSE coitem_status
<querysource>
<name>info</name>
<sql>SELECT
- formatQty(qtyNetable(itemsite_id)) AS qoh,
+ formatQty(qtyAvailable(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(qtyNetable(sub.itemsite_id)) AS f_qtyonhand,
+ formatQty(qtyAvailable(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(qtyNetable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, <? value("byDays") ?>) - qtyAllocated(sub.itemsite_id, <? value("byDays") ?>)) as f_avail
+ formatQty(qtyAvailable(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(qtyNetable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, (<? value("byDate") ?> - CURRENT_DATE)) - qtyAllocated(sub.itemsite_id, (<? value("byDate") ?> - CURRENT_DATE))) as f_avail
+ formatQty(qtyAvailable(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(qtyNetable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime) - qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime)) as f_avail
+ formatQty(qtyAvailable(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)
projects / xtuple / commitdiff