I have this query that returns available quantity that takes into account (from PO) on order and on commit (SO) based on dates of order and receive.
declare @onhand as float, @openqty_s_order as float, @openqty_p_order as float set @onhand = (select oitw.onhand from oitw where oitw.whscode = $[$38.24.0] and oitw.itemcode = $[$38.1.0]) set @openqty_s_order = (select sum(openqty) from rdr1 where (rdr1.shipdate <= $[$38.25.0]) and (rdr1.itemcode = $[$38.1.0]) and (rdr1.whsCode = $[$38.24.0])) set @openqty_p_order = (select sum(openqty) from por1 where (por1.shipdate <= $[$38.25.0]) and (por1.itemcode = $[$38.1.0]) and (por1.whsCode = $[$38.24.0])) select (@openqty_p_order + @Onhand - @openqty_s_order)
Whenever the SO ShipDate is <= PO Ship Date, the value returned by entire query is 0.0 instead of OnHand - Qty committed by SO's.
This query works when SO ShipDate is >= PO ShipDate, it returns OnHand-Qty Committed by SOs + Available through POs
To repeat the problem:
BASICALLY whenever @openqty_p_order has no value returned then the entire select -> select (@openqty_p_order + @Onhand - @openqty_s_order) returns 0.0, even though @onhand - @openqty_s_order have values that is not zero.
Appreciate your help. Thank you.