Min Inventory alert query
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.