alert for particular item

[Former Member]

Hi,

friends. could you give the alert query for particular item group. in the raw material group we are maintaining minimum stock .

For example

grease is raw material ----- minimum stock 4500 for all ware house not separate warehouse

in stock       committed      ordered                   available      

3000             2000               3300                     4300

in-stock qty < minimum stock ( 3000< 4500) alert will be need is it possible???

[kothandaraman_nagarajan · Accepted solution]

Hi Ramesh,

Try below query:

SELECT     dbo.OITW.WhsCode, dbo.OITW.ItemCode, dbo.OITM.ItemName AS ItemDescription, dbo.OITW.OnHand, dbo.OITW.IsCommited, dbo.OITW.OnOrder,

                      dbo.OITW.MinStock, dbo.OITW.MinOrder, dbo.OITW.MaxStock, dbo.OITW.OnHand - dbo.OITW.MinStock AS QtyAboveMin

FROM         dbo.OITW INNER JOIN

                      dbo.OITM ON dbo.OITW.ItemCode = dbo.OITM.ItemCode

WHERE     (dbo.OITW.OnHand - dbo.OITW.MinStock <= 0) AND (dbo.OITW.MinStock > 0)

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

only need raw material item group

[Former Member · Accepted solution]

default is there in sap . minimum stock deviation because we using for all item . but i need only one item group

[Former Member · Accepted solution]

I put warehouse code in where condition

[kothandaraman_nagarajan · Accepted solution]

Hi Ramesh,

Try this:

SELECT T1.[ItemCode], T1.[ItemName], T0.[OnHand], T0.[IsCommited], T0.[OnOrder], T0.[MinStock], T0.[MaxStock], T0.[MinOrder], T0.[OnHand]+ T0.[OnOrder] -  T0.[IsCommited] as AVA FROM OITW T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod WHERE T2.[ItmsGrpNam] = [%0] and  T0.[OnHand] < T0.[MinStock]

Thanks & Regards,

Nagarajan