From ec479960b6c115b5ee8e2a6eb19202468742db43 Mon Sep 17 00:00:00 2001 From: garyhgohoos Date: Fri, 22 Aug 2014 17:02:02 -0400 Subject: [PATCH] Issue #23507:implement location_usable --- .../public/functions/getsoitemstatus.sql | 2 +- .../public/functions/indentedwo.sql | 8 +- .../public/functions/indentedwomatl.sql | 8 +- .../public/functions/qtyavailable.sql | 63 +++++- .../public/functions/qtynetable.sql | 4 +- .../sufficientinventorytoshipitem.sql | 6 +- .../public/functions/woinvavail.sql | 6 +- .../public/functions/woinvavailmatl.sql | 2 +- .../public/tables/location.sql | 10 + .../metasql/distributeInventory-locations.mql | 196 +++++++++--------- .../inventoryAvailability-byCustOrSO.mql | 16 +- .../metasql/inventoryAvailability-general.mql | 2 +- .../metasql/inventoryLocator-detail.mql | 4 + .../metasql/pendingAvailability-detail.mql | 4 +- .../public/tables/metasql/qoh-detail.mql | 24 ++- .../metasql/runningAvailability-detail.mql | 4 +- .../tables/metasql/salesOrderItems-list.mql | 8 +- .../metasql/substituteAvailability-detail.mql | 20 +- .../tables/report/InventoryAvailability.xml | 2 +- .../InventoryAvailabilityByCustomerType.xml | 4 +- .../InventoryAvailabilityBySalesOrder.xml | 4 +- .../InventoryAvailabilityBySourceVendor.xml | 2 +- .../tables/report/PackingList-Shipment.xml | 2 +- .../tables/report/RunningAvailability.xml | 2 +- .../SubstituteAvailabilityByRootItem.xml | 8 +- 25 files changed, 251 insertions(+), 160 deletions(-) diff --git a/foundation-database/public/functions/getsoitemstatus.sql b/foundation-database/public/functions/getsoitemstatus.sql index 4f47de051..a9cd679eb 100644 --- a/foundation-database/public/functions/getsoitemstatus.sql +++ b/foundation-database/public/functions/getsoitemstatus.sql @@ -22,7 +22,7 @@ BEGIN 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 diff --git a/foundation-database/public/functions/indentedwo.sql b/foundation-database/public/functions/indentedwo.sql index aa1fc7a8d..7c407587a 100644 --- a/foundation-database/public/functions/indentedwo.sql +++ b/foundation-database/public/functions/indentedwo.sql @@ -24,7 +24,7 @@ BEGIN --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 @@ -47,7 +47,7 @@ BEGIN _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; @@ -149,7 +149,7 @@ BEGIN _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 @@ -184,7 +184,7 @@ BEGIN _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; diff --git a/foundation-database/public/functions/indentedwomatl.sql b/foundation-database/public/functions/indentedwomatl.sql index 6d2619d4c..8155994a7 100644 --- a/foundation-database/public/functions/indentedwomatl.sql +++ b/foundation-database/public/functions/indentedwomatl.sql @@ -40,7 +40,7 @@ BEGIN _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, @@ -78,11 +78,11 @@ BEGIN _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; diff --git a/foundation-database/public/functions/qtyavailable.sql b/foundation-database/public/functions/qtyavailable.sql index fef9048df..2145d7b1c 100644 --- a/foundation-database/public/functions/qtyavailable.sql +++ b/foundation-database/public/functions/qtyavailable.sql @@ -1,35 +1,78 @@ -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; diff --git a/foundation-database/public/functions/qtynetable.sql b/foundation-database/public/functions/qtynetable.sql index e5c9f2cb9..cb2a6fb98 100644 --- a/foundation-database/public/functions/qtynetable.sql +++ b/foundation-database/public/functions/qtynetable.sql @@ -28,14 +28,14 @@ BEGIN 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; diff --git a/foundation-database/public/functions/sufficientinventorytoshipitem.sql b/foundation-database/public/functions/sufficientinventorytoshipitem.sql index e173be37f..14280db41 100644 --- a/foundation-database/public/functions/sufficientinventorytoshipitem.sql +++ b/foundation-database/public/functions/sufficientinventorytoshipitem.sql @@ -37,7 +37,7 @@ BEGIN 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 + @@ -57,7 +57,7 @@ BEGIN 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) @@ -72,7 +72,7 @@ BEGIN 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) diff --git a/foundation-database/public/functions/woinvavail.sql b/foundation-database/public/functions/woinvavail.sql index e312da05f..52bfd3961 100644 --- a/foundation-database/public/functions/woinvavail.sql +++ b/foundation-database/public/functions/woinvavail.sql @@ -46,7 +46,7 @@ BEGIN 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, @@ -113,7 +113,7 @@ BEGIN 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, @@ -202,7 +202,7 @@ BEGIN 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, diff --git a/foundation-database/public/functions/woinvavailmatl.sql b/foundation-database/public/functions/woinvavailmatl.sql index 3c4e1a22d..3ede5e42d 100644 --- a/foundation-database/public/functions/woinvavailmatl.sql +++ b/foundation-database/public/functions/woinvavailmatl.sql @@ -41,7 +41,7 @@ BEGIN 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, diff --git a/foundation-database/public/tables/location.sql b/foundation-database/public/tables/location.sql index 5fc955721..09447d091 100644 --- a/foundation-database/public/tables/location.sql +++ b/foundation-database/public/tables/location.sql @@ -8,3 +8,13 @@ select xt.add_column('location','location_formatname', 'TEXT', NULL, 'public'); 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 diff --git a/foundation-database/public/tables/metasql/distributeInventory-locations.mql b/foundation-database/public/tables/metasql/distributeInventory-locations.mql index 7a95ea03a..baa6a6697 100644 --- a/foundation-database/public/tables/metasql/distributeInventory-locations.mql +++ b/foundation-database/public/tables/metasql/distributeInventory-locations.mql @@ -5,96 +5,106 @@ -- 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 ( - - SELECT location_id AS id, AS type, - formatLocationName(location_id) AS locationname, - CASE WHEN ( = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE - WHEN ( = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE - WHEN ( = 'O' AND location_id=itemsite_location_id) THEN TRUE - ELSE FALSE - END AS defaultlocation, - location_netable, - TEXT('') AS lotserial, - TEXT() 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=) ) - - SELECT itemloc_id AS id, AS type, - COALESCE(formatLocationName(location_id), - ) AS locationname, - (location_id IS NOT NULL AND - CASE WHEN ( = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE - WHEN ( = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE - WHEN ( = '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 - 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=) ) - UNION - SELECT location_id AS id, AS type, - formatLocationName(location_id) AS locationname, - CASE WHEN ( = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE - WHEN ( = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE - WHEN ( = 'O' AND location_id=itemsite_location_id) THEN TRUE - ELSE FALSE - END AS defaultlocation, - location_netable, - TEXT('') AS lotserial, - TEXT() 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= ) - AND (location_id NOT IN (SELECT DISTINCT itemloc_location_id FROM itemloc WHERE (itemloc_itemsite_id=itemsite_id))) - AND (itemlocdist_id=) ) - - ) AS data - WHERE ((TRUE) - - AND (qtytagged != 0) - - - AND (qty > 0) - - ) 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 ( + + SELECT location_id AS id, AS type, + formatLocationName(location_id) AS locationname, + CASE WHEN ( = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE + WHEN ( = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE + WHEN ( = '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() 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=) ) + + SELECT itemloc_id AS id, AS type, + COALESCE(formatLocationName(location_id), + ) AS locationname, + (location_id IS NOT NULL AND + CASE WHEN ( = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE + WHEN ( = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE + WHEN ( = '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 + 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=) ) + UNION + SELECT location_id AS id, AS type, + formatLocationName(location_id) AS locationname, + CASE WHEN ( = 'R' AND location_id=itemsite_recvlocation_id) THEN TRUE + WHEN ( = 'I' AND location_id=itemsite_issuelocation_id) THEN TRUE + WHEN ( = '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() 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= ) + AND (location_id NOT IN (SELECT DISTINCT itemloc_location_id + FROM itemloc + WHERE (itemloc_itemsite_id=itemsite_id))) + AND (itemlocdist_id=) ) + + ) AS data +WHERE ((TRUE) + AND ( ( != 'I') OR ( = 'I' AND location_usable) ) + + AND (qtytagged != 0) + + + AND (qty > 0) + +) ORDER BY locationname; diff --git a/foundation-database/public/tables/metasql/inventoryAvailability-byCustOrSO.mql b/foundation-database/public/tables/metasql/inventoryAvailability-byCustOrSO.mql index 25bff3a80..69969875e 100644 --- a/foundation-database/public/tables/metasql/inventoryAvailability-byCustOrSO.mql +++ b/foundation-database/public/tables/metasql/inventoryAvailability-byCustOrSO.mql @@ -94,10 +94,10 @@ FROM ( GROUP BY cohead_id, item_number, cust_number, cust_name, cohead_orderdate, pack_id, coitem_scheddate - 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 ) @@ -108,7 +108,7 @@ FROM ( 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, @@ -138,8 +138,8 @@ FROM ( WHERE(custtype_code ~ ))) - 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)) ) @@ -167,7 +167,7 @@ FROM ( 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) @@ -184,8 +184,8 @@ FROM ( WHERE(custtype_code ~ ))) - 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)) ) diff --git a/foundation-database/public/tables/metasql/inventoryAvailability-general.mql b/foundation-database/public/tables/metasql/inventoryAvailability-general.mql index a60634ca3..53ab1ce60 100644 --- a/foundation-database/public/tables/metasql/inventoryAvailability-general.mql +++ b/foundation-database/public/tables/metasql/inventoryAvailability-general.mql @@ -37,7 +37,7 @@ FROM (SELECT 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, diff --git a/foundation-database/public/tables/metasql/inventoryLocator-detail.mql b/foundation-database/public/tables/metasql/inventoryLocator-detail.mql index b607ba4c1..cfc41cd23 100644 --- a/foundation-database/public/tables/metasql/inventoryLocator-detail.mql +++ b/foundation-database/public/tables/metasql/inventoryLocator-detail.mql @@ -8,6 +8,7 @@ SELECT *, AS locationname_xtnullrole, AS netable_xtnullrole, + AS usable_xtnullrole, AS lotserial_xtnullrole, AS expiration_xtnullrole, AS warranty_xtnullrole, @@ -25,6 +26,8 @@ FROM (SELECT itemloc_id, 1 AS type, warehous_code, 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, @@ -88,6 +91,7 @@ FROM (SELECT itemloc_id, 1 AS type, warehous_code, itemsite_warrpurc, NULL AS itemloc_warrpurc, NULL AS locationname, NULL AS netable, + NULL AS usable, NULL AS lotserial, NULL AS expiration, NULL AS warranty, diff --git a/foundation-database/public/tables/metasql/pendingAvailability-detail.mql b/foundation-database/public/tables/metasql/pendingAvailability-detail.mql index ff91158a3..ec1447434 100644 --- a/foundation-database/public/tables/metasql/pendingAvailability-detail.mql +++ b/foundation-database/public/tables/metasql/pendingAvailability-detail.mql @@ -38,7 +38,7 @@ SELECT itemsite_id, reorderlevel, ib.*, ((bomdata_qtyreq::NUMERIC * ) * (1 + bomdata_scrap::NUMERIC)) AS pendalloc, qtyAllocated(itemsite_id, DATE()) AS totalalloc, - noNeg(qtyNetable(itemsite_id)) AS qoh, + noNeg(qtyAvailable(itemsite_id)) AS qoh, qtyOrdered(itemsite_id, DATE()) AS ordered FROM indentedBOM(, getActiveRevId('BOM', ), @@ -77,7 +77,7 @@ FROM ( SELECT itemsite_id, bomitem_seqnumber, item_number, ((itemuomtouom(bomitem_item_id, bomitem_uom_id, NULL, (bomitem_qtyfxd + bomitem_qtyper * ) * (1 + bomitem_scrap)))) AS pendalloc, qtyAllocated(itemsite_id, DATE()) AS totalalloc, - noNeg(qtyNetable(itemsite_id)) AS qoh, + noNeg(qtyAvailable(itemsite_id)) AS qoh, qtyOrdered(itemsite_id, DATE()) AS ordered, CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel FROM itemsite, item, bomitem(), uom diff --git a/foundation-database/public/tables/metasql/qoh-detail.mql b/foundation-database/public/tables/metasql/qoh-detail.mql index 72032e80c..8862e3984 100644 --- a/foundation-database/public/tables/metasql/qoh-detail.mql +++ b/foundation-database/public/tables/metasql/qoh-detail.mql @@ -159,9 +159,13 @@ SELECT itemsite_id, detail,warehous_code, 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' @@ -172,6 +176,8 @@ SELECT itemsite_id, detail,warehous_code, END AS costmethod, 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' @@ -183,18 +189,30 @@ SELECT itemsite_id, detail,warehous_code, '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, + AS availqoh_xtnullrole, + AS nonavailqoh_xtnullrole, + AS availvalue_xtnullrole, + AS nonavailvalue_xtnullrole, AS netqoh_xtnullrole, AS nonnetqoh_xtnullrole, AS netvalue_xtnullrole, @@ -213,10 +231,14 @@ FROM ( CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel, 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, 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, diff --git a/foundation-database/public/tables/metasql/runningAvailability-detail.mql b/foundation-database/public/tables/metasql/runningAvailability-detail.mql index a5ccea5a0..c94d8528c 100644 --- a/foundation-database/public/tables/metasql/runningAvailability-detail.mql +++ b/foundation-database/public/tables/metasql/runningAvailability-detail.mql @@ -6,7 +6,7 @@ SELECT orderid, altorderid, ordertype, ordernumber, sequence, item_number, duedate, amount, qtyordered, qtyreceived, balance, - balance AS runningavail, + balance AS runningavail, balance AS runningnetable, CASE WHEN duedate = startOfTime() THEN '' ELSE formatDate(duedate) END AS f_duedate, @@ -17,7 +17,9 @@ SELECT orderid, altorderid, ordertype, ordernumber, sequence, item_number, notes, 1 AS runningavail_xtrunningrole, + 1 AS runningnetable_xtrunningrole, AS runningavail_xtrunninginit, + AS runningnetable_xtrunninginit, CASE WHEN late THEN 'error' END AS duedate_qtforegroundrole, CASE WHEN duedate = startOfTime() THEN '' END AS duedate_qtdisplayrole, CASE WHEN ordertype ~ OR diff --git a/foundation-database/public/tables/metasql/salesOrderItems-list.mql b/foundation-database/public/tables/metasql/salesOrderItems-list.mql index 525e53108..e689296d9 100644 --- a/foundation-database/public/tables/metasql/salesOrderItems-list.mql +++ b/foundation-database/public/tables/metasql/salesOrderItems-list.mql @@ -57,8 +57,8 @@ SELECT coitem_id, coitem_altid, groupby, ELSE 1 END AS xtindentrole, spplytype, ordrnumbr, - netableqoh, - 'qty' AS netableqoh_xtnumericrole + availableqoh, + 'qty' AS availableqoh_xtnumericrole , reserved, @@ -120,11 +120,11 @@ SELECT coitem_id, 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 , coitem_qtyreserved AS reserved, - qtyNetable(itemsite_id) - qtyreserved(itemsite_id) AS reservable + qtyAvailable(itemsite_id) - qtyReserved(itemsite_id) AS reservable FROM cohead JOIN coitem ON (coitem_cohead_id=cohead_id) diff --git a/foundation-database/public/tables/metasql/substituteAvailability-detail.mql b/foundation-database/public/tables/metasql/substituteAvailability-detail.mql index 495441559..a92db1e26 100644 --- a/foundation-database/public/tables/metasql/substituteAvailability-detail.mql +++ b/foundation-database/public/tables/metasql/substituteAvailability-detail.mql @@ -5,9 +5,9 @@ -- 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, @@ -17,14 +17,14 @@ FROM (SELECT sub.itemsite_id AS s_itemsite_id, warehous_code, item_number, (item_descrip1 || ' ' || item_descrip2) AS itemdescrip, - (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, - (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, @@ -34,37 +34,37 @@ FROM (SELECT sub.itemsite_id AS s_itemsite_id, (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 (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 (qtyAllocated(sub.itemsite_id, ) * itemsub_uomratio) AS allocated, (qtyOrdered(sub.itemsite_id, ) * itemsub_uomratio) AS ordered, - ((qtyNetable(sub.itemsite_id) * itemsub_uomratio) + (qtyOrdered(sub.itemsite_id, ) * itemsub_uomratio) + ((qtyAvailable(sub.itemsite_id) * itemsub_uomratio) + (qtyOrdered(sub.itemsite_id, ) * itemsub_uomratio) - (qtyAllocated(sub.itemsite_id, ) * itemsub_uomratio)) AS available (qtyAllocated(sub.itemsite_id, )) AS allocated, (qtyOrdered(sub.itemsite_id, )) AS ordered, - (qtyNetable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, ) + (qtyAvailable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, ) - qtyAllocated(sub.itemsite_id, )) AS available (qtyAllocated(sub.itemsite_id, ( - CURRENT_DATE)) * itemsub_uomratio) AS allocated, (qtyOrdered(sub.itemsite_id, ( - CURRENT_DATE)) * itemsub_uomratio) AS ordered, - ((qtyNetable(sub.itemsite_id) * itemsub_uomratio) + (qtyOrdered(sub.itemsite_id, ( - CURRENT_DATE)) * itemsub_uomratio) + ((qtyAvailable(sub.itemsite_id) * itemsub_uomratio) + (qtyOrdered(sub.itemsite_id, ( - CURRENT_DATE)) * itemsub_uomratio) - (qtyAllocated(sub.itemsite_id, ( - CURRENT_DATE)) * itemsub_uomratio)) AS available (qtyAllocated(sub.itemsite_id, ( - CURRENT_DATE))) AS allocated, (qtyOrdered(sub.itemsite_id, ( - CURRENT_DATE))) AS ordered, - (qtyNetable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, + (qtyAvailable(sub.itemsite_id) + qtyOrdered(sub.itemsite_id, ( - CURRENT_DATE)) - qtyAllocated(sub.itemsite_id, ( - CURRENT_DATE))) AS available diff --git a/foundation-database/public/tables/report/InventoryAvailability.xml b/foundation-database/public/tables/report/InventoryAvailability.xml index a04aabcf4..0fdcad7ab 100644 --- a/foundation-database/public/tables/report/InventoryAvailability.xml +++ b/foundation-database/public/tables/report/InventoryAvailability.xml @@ -51,7 +51,7 @@ 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, diff --git a/foundation-database/public/tables/report/InventoryAvailabilityByCustomerType.xml b/foundation-database/public/tables/report/InventoryAvailabilityByCustomerType.xml index 591fe61a5..9be2630af 100644 --- a/foundation-database/public/tables/report/InventoryAvailabilityByCustomerType.xml +++ b/foundation-database/public/tables/report/InventoryAvailabilityByCustomerType.xml @@ -73,7 +73,7 @@ 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, @@ -95,7 +95,7 @@ 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) diff --git a/foundation-database/public/tables/report/InventoryAvailabilityBySalesOrder.xml b/foundation-database/public/tables/report/InventoryAvailabilityBySalesOrder.xml index 6f58f1fdd..251dcf415 100644 --- a/foundation-database/public/tables/report/InventoryAvailabilityBySalesOrder.xml +++ b/foundation-database/public/tables/report/InventoryAvailabilityBySalesOrder.xml @@ -62,7 +62,7 @@ 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, @@ -83,7 +83,7 @@ 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) diff --git a/foundation-database/public/tables/report/InventoryAvailabilityBySourceVendor.xml b/foundation-database/public/tables/report/InventoryAvailabilityBySourceVendor.xml index d46ee40fa..c4571a90c 100644 --- a/foundation-database/public/tables/report/InventoryAvailabilityBySourceVendor.xml +++ b/foundation-database/public/tables/report/InventoryAvailabilityBySourceVendor.xml @@ -55,7 +55,7 @@ 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, diff --git a/foundation-database/public/tables/report/PackingList-Shipment.xml b/foundation-database/public/tables/report/PackingList-Shipment.xml index c49f82e28..c5478342d 100644 --- a/foundation-database/public/tables/report/PackingList-Shipment.xml +++ b/foundation-database/public/tables/report/PackingList-Shipment.xml @@ -136,7 +136,7 @@ SELECT shiphead_number, 'T/O #:' AS ordertype, 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 diff --git a/foundation-database/public/tables/report/RunningAvailability.xml b/foundation-database/public/tables/report/RunningAvailability.xml index c910f2418..cd649f0b1 100644 --- a/foundation-database/public/tables/report/RunningAvailability.xml +++ b/foundation-database/public/tables/report/RunningAvailability.xml @@ -41,7 +41,7 @@ info 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 diff --git a/foundation-database/public/tables/report/SubstituteAvailabilityByRootItem.xml b/foundation-database/public/tables/report/SubstituteAvailabilityByRootItem.xml index 64f283c24..4a5c328c6 100644 --- a/foundation-database/public/tables/report/SubstituteAvailabilityByRootItem.xml +++ b/foundation-database/public/tables/report/SubstituteAvailabilityByRootItem.xml @@ -31,21 +31,21 @@ 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) -- 2.39.2