(Back to A-AD Administration Module SQL Posted to Wiki Listing - A-AD Administration Module SQL Posted to Wiki )
TYPE: Frequently Requested SQL
AUTHOR NAME: Zal Parchem
PURPOSE AND BACKGROUND: This SQL was created in Version 8.8 PL16. This SQL shows those products which have a negative inventory balance in at least one warehouse within the company. If a product has negative figues in two warehouses, the SQL will show the product two times with the different negative "In Stock" for each warehouse. SQL also includes a total of all warehouse in stock figures (in case an Inventory Transfer can be done to resolve the situation). You can use "copy and paste" to move this Alert Message into your system and set the Alert Message to "Hourly" or whatever frequency is good for your company.
Here is a screen print of the SQL results:
Copy and Paste this SQL:
--A-AD Alert Mess Negative Inventory In Stock by Warehouse and Total Ver 1 ZP 2011 02 19
--DESCRIPTION: SQL shows only those products having a negative balance in a warehouse and another balance for the entire company.
--Version 1 Zal Parchem 2011 02 19
T0.ItemCode AS 'Product',
T1.ItemName AS 'Description',
T0.WhsCode AS 'Whs',
T0.OnHand AS 'Whs Qty',
T1.OnHand AS 'Comp Qty'
FROM OITW T0
LEFT OUTER JOIN OITM T1
ON T0.ItemCode = T1.ItemCode
T0.OnHand < 0