Sales Order & Minimum Invertory Query

[former_member573912]

Scenario,

I have kept minimum inventory level on item a,b,c,d as 5000,5000,5000,5000.

I want alert when the min inventory level gets below the given qty .I got the query for it.

We manufacture make to stock, if inventory goes below minimum level.We use Customer M1 for make to Stock for creating sales order.

If i create sales order with M1 customer then only ,the item should disappears form alert.

If i create Sales order with other than M1 , it should not disappear from alert. I have created query but it didnt works .Please check the query:-

SELECT a.docentry,T0.[ItemCode], T1.[ItemName], T0.[WhsCode],t0.IsCommited, T0.[OnHand], T0.[MinStock],a.basecard FROM OITW T0

INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN RDR1 A ON A.ITEMCODE=T1.ItemCode

WHERE T0.[OnHand]+t0.IsCommited < T0.[MinStock] and t0.whscode='04' and a.LineStatus='o' and a.BaseCard='m1'

I want query such that ,if Sales order is created with M1 and minimum qty is equal to onhand +iscommited with customer M1 <minimum inventory.

Please provide the query

[Johan_H]

Hi,

Your query looks good, but I am not sure why you used the BaseCard field instead of CardCode. Please try this, hopefully it works for your scenario:

SELECT a.docentry
      ,T0.[ItemCode]
      ,T1.[ItemName]
      ,T0.[WhsCode]
      ,t0.IsCommited
      ,T0.[OnHand]
      ,T0.[MinStock]
      ,a.CardCode
      ,a.CardName 
FROM OITW T0
     INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
     INNER JOIN RDR1 A ON A.ItemCode = T1.ItemCode
WHERE T0.[OnHand] + T0.IsCommited < T0.[MinStock]
  and T0.whscode='04'
  and a.LineStatus='o'
  and a.CardCode='m1'

Regards,

Johan