Min Inventory alert query

[too_muihwee]

Hi all,

I have a requirement below:

Item A - warehouse MAIN- Min inventory level = 5pcs, In stock status now = 7pcs

            - warehouse SERVICE - Min inventory level = 0pcs, In stock status now = 0pc

My user wants when anyone transfer this Item A from MAIN to SERVICE that causes MAIN warehouse Instock < Min Inventory, then prompt the alert only for this item.

Current standard system alert will prompt only if total quantity from all warehouses fall below min inventory. So if I transfer 3 pcs from MAIN to SERVICE, it will not trigger the alert. Only when i invoice 3 pcs from SERVICE or MAIN warehouse then only system will prompt user.

I have tried using SQL query, but everytime when it prompt, it will list out all other items as well which is unrelated to the stock transfer, in addition, the query only list out max 300 to 500 records, if i execute the same query in SQL, it might have results of 2000 plus records.

Anyone know the standard min inventory alert query? Thought of using the standard min inv query and then change a bit for the total by warehouse instead of by item.

My items are managed by warehouses and at document settings screen, i have set 'Block negative inventory by' --> Warehouse.

Anyone have idea how to make it prompt when I transfer from MAIN to SERVICE and Instock in MAIN is fall below Min Inventory? Appreciate if anyone could help. Thanks in advance.

[frank_wang6]

In this case, the best way to write workflow to do this for u.

The workflow can be triggered by condition, and then execute the inventory transfer for u.

Frank

[too_muihwee]

Hi,

Not sure what are you referring to, can you elaborate? Workflow?

[frank_wang6]

Check workflow section.

[BattleshipCobra]

Hello,

Assuming it doesn't matter why the MAIN warehouse drops below the minimum.  And assuming you are using "Manage Inventory by Warehouse" then this should work:

SELECT T0.[ItemCode], T1.[ItemName], T0.[WhsCode], T0.[OnHand], T0.[MinStock] 
FROM OITW T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode 
WHERE T0.[OnHand] < T0.[MinStock] AND T0.[WhsCode] = '01'

(Replace 01 with your MAIN warehouse code)

The query will return any item that has an in stock quantity less than a minimum stock quantity in the 01 (MAIN) warehouse.  So as soon as the item falls below the inventory level then it will trigger the alert.  You could put the alert on a faster repeat so it comes up quickly.

The key is the OITW table which has all the information you need.

Let me know if this works for you, M

[too_muihwee]

Hi Mike,

This does not work as i expected.

1. When execute at SQL - result is 800 records, but when set it at Alert, it only generates 350 records, so if Item code starting with Z... will not be in the list.

2. When i transfer Item Z from Main to Service warehouse and the Main whse balance for this item will fall below min, i want alert for item Z only not alert with lists of the rest of 300 items who all fall below min inventory.

The difference between system Min inv alert is that it only alert the item that falls below and which document causes it to fall below.

Our own query will generate all items that fall below regardless which items that we just perform the transaction that force it to fall below and users cannot tell which transactions that cause it to fall below.

These are so far the main difference that i can notice. Unable to achive the same result as per sytem alert.

[Johan_H]

Hi,

The alert functionality only shows a limited amount of records. This is by design, although I do not know why.

You can only achieve what you want with a query and an alert, and unfortunately you cannot pass specific parameters to the query, like you can with a FMS.

So you must either accept that you get all items (using the query Mike Taylorsuggested), or you must use another approach altogether, like FMS or stored procedure.

Regards,

Johan

[BattleshipCobra]

Alerts are much easier to implement than workflow which is a javascript nightmare and much more complex.  I provided a simple SQL alert.  If you want something more complex, please contact your consultant because it will be a much more complex query.

The way I would do it would be to isolate which documents you use that would cause the inventory to be reduced (invoice, delivery, inventory transfer, goods issue, issue to production, etc.) and then you have to code in some sort of time horizon like say within the last couple of days.  IE, you could make the query just look for items that are below the minimum that have one or more of these documents in the last 1,2 or 3 days or something.  Then as the query is running it will see the item is below minimums and see that there was a document in the recent past that triggered it.  When you run the alert say every 15 minutes this will give you the timing you would need.

Alternatively you could search out the tables used in the inventory posting transactions (historical inventory movements) and do the query based on this table but I don't have this on hand right now.

OR, you could just adjust your existing minimum inventory levels so that you don't have 800+ items that are already below their minimum.  If the items don't need a minimum, maybe you could filter out items where [MinStock] = 0.  So then it would only be scanning for items with an actual minimum set in the system.  Then my query would work, you just need to filter out WHERE COALESCE([MinStock],0) <> 0.

[frank_wang6]

I believe his purpose to generate an alert and let people do the inventory transfer based on the alert, that's where workflow play.

Basically the alert is not necessary, since the workflow will be triggered based on same condition and do the inventory transfer based on the condition, no need human intervention, that's the purpose.

Frank

[BattleshipCobra]

Also, as a follow-up, the custom alerts will only give you the first 350 rows.  So you need to narrow your query as I mentioned.

Either filter out 0 qty miniums or increase the complexity of your query to consider documents that would cause a decrease in inventory over the last few days.

[BattleshipCobra]

Hi Frank, to quote the original thread:

"My user wants when anyone transfer this Item A from MAIN to SERVICE that causes MAIN warehouse Instock < Min Inventory, then prompt the alert only for this item."

He also refers to invoices, so any document which causes an inventory decrease would be included.  Anything that drops an inventory item below minimum stock he wants to know but only from that warehouse, not all warehouses like the system alert query.  BUT, given the query I wrote it doesn't consider the timing factor, it just looks at all items and their respective stock and minimum levels in the service warehouse.

As I mentioned, if you either filter out 0 minimums to reduce the data or make a derived table including deliveries, invoices, inventory transfers, goods issues, issues for production, etc. then you can filter for the last couple of days dynamically by using something like DATEDIFF(DocDate,GETDATE()) < 2 for example.

Workflow is a good solution but in this case it's clear he is looking for an alert query.

[too_muihwee]

Hi Mike,

Thanks for the advice, i am also looking at the system standard min stock alert view/query, not sure anyone knows which is the view, if can get that then will be good, just need to add in the warehouse = MAIN, then it should solve my problem.