i have created this backorder stored proc that blocks all orders that will take the item into negative, ie selling more than actually available, it works 99% well, issue is i dont know where and why the stored proc still allows certain unknown scenarios to go through. i think i might have to amend it slightly and add " where t0.onhand - t1.quantity < 0", this could block all orders that will make the onhand negative. and yes this is only sold from one warehouse. Thanks
if( @object_type = '17' and(@transaction_type = 'A' or @transaction_type = 'U'))
set @backorder = (select top(1) T0.itemcode from rdr1 T0 inner join oitw T1 on T0.WhsCode = T1.WhsCode where
(T0.Quantity >= T1.OnHand) and T0.itemcode = T1.itemcode and T0.docentry = @list_of_cols_val_tab_del )
if(@backorder is not null)
set @error = 1
set @error_message = N'Item '@backorder' exceeds available quantity'