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

[Former Member]

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.

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.

[Former Member · Accepted solution]

Hi Syed Aleem,

Modify the Query as follows :

   Select @OpenQty=isNull(sum(openqty),0) 
   From RDR1 
   where (rdr1.shipdate <= $[$38.25.0]) and (rdr1.itemcode = $[$38.1.0]) and (rdr1.whsCode = $[$38.24.0]))

   Select @openqty_p_order=isNull(Sum(OpenQty),0)
   from por1 
   where (por1.shipdate <= $[$38.25.0]) and (por1.itemcode = $[$38.1.0]) and (por1.whsCode = $[$38.24.0]))

The problem occurs when there is no row to select then aggregate clause returns NULL which cannot be used in any Operation .

Hence use isNull function to avoid this error.

Thanks and Regards,

Pooja Singh.

[Former Member · Accepted solution]

Thank you Pooja.