From 4cccf56853c0d8561a0040c10f1dbe2395b032ac Mon Sep 17 00:00:00 2001 From: garyhgohoos Date: Fri, 6 Jun 2014 11:49:48 -0400 Subject: [PATCH] Issue #23658:ensure itemsite_value is 0 when qtyonhand is 0 --- foundation-database/public/functions/postinvtrans.sql | 6 +++++- 1 file changed, 5 insertions(+), 1 deletion(-) diff --git a/foundation-database/public/functions/postinvtrans.sql b/foundation-database/public/functions/postinvtrans.sql index 66251698f..9afe786f8 100644 --- a/foundation-database/public/functions/postinvtrans.sql +++ b/foundation-database/public/functions/postinvtrans.sql @@ -125,7 +125,11 @@ BEGIN _invhistid, itemsite_id, pTransType, _timestamp, pQty, itemsite_qtyonhand, (itemsite_qtyonhand + (_sense * pQty)), - itemsite_costmethod, itemsite_value, itemsite_value + (_r.cost * _sense * pQty), + itemsite_costmethod, itemsite_value, + -- sanity check to ensure that value = 0 when qtyonhand = 0 + CASE WHEN ((itemsite_qtyonhand + (_sense * pQty)) + itemsite_nnqoh) = 0.0 THEN 0.0 + ELSE itemsite_value + (_r.cost * _sense * pQty) + END, pOrderType, pOrderNumber, pDocNumber, pComments, uom_name, _r.cost, _xferwhsid, FALSE, pItemlocSeries FROM itemsite, item, uom -- 2.39.2