Skip to Content
Former Member
Sep 26, 2011 at 05:10 PM

How to fix this QtyOnHand FMS query with SO and PO ShipDates.


Hi Forum,

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.

@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.