Explanation of SQL
SQL finds any discrepancies between the actual inventory record numbers vs the actual inventory log entries that have been posted. This query will show you if any of the parts or inventory records are out of sync.
Note: This query is only written for a single warehouse & division. It needs to be modified before running on a multi-division or multi-warehouse system.
Risk of Data Corruption if Run Improperly
Low. This is a select query only, the data will not be modified.
SQL #1 - Identify Discrepencies
SELECT * FROM( SELECT INV.ID AS InventoryID , INV.PartID AS PartID , P.ItemName , SUM(INVL.QuantityBilled) AS QuantityBilled , SUM(INVL.QuantityReceivedOnly) AS QuantityReceivedOnly , SUM(INVL.QuantityOnHand) AS QuantityOnHand , SUM(INVL.QuantityReserved) AS QuantityReserved , SUM(INVL.QuantityAvailable) AS QuantityAvailable , SUM(INVL.QuantityOnOrder) AS QuantityOnOrder , SUM(INVL.QuantityExpected) AS QuantityExpected , INV.QuantityBilled AS INV_QuantityBilled , INV.QuantityReceivedOnly AS INV_QuantityReceivedOnly , INV.QuantityOnHand AS INV_QuantityOnHand , INV.QuantityReserved AS INV_QuantityReserved , INV.QuantityAvailable AS INV_QuantityAvailable , INV.QuantityOnOrder AS INV_QuantityOnOrder , INV.QuantityExpected AS INV_QuantityExpected FROM InventoryLog INVL LEFT JOIN Inventory INV ON INV.ID = INVL.InventoryID LEFT JOIN Part P ON P.ID = INV.PartID WHERE INV.ID IS NOT NULL GROUP BY P.ItemName , INV.PartID , INV.QuantityBilled , INV.QuantityReceivedOnly , INV.QuantityOnHand , INV.QuantityReserved , INV.QuantityAvailable , INV.QuantityOnOrder , INV.QuantityExpected , INV.ID ) Tmp WHERE (ROUND(QuantityBilled,2) != ROUND(INV_QuantityBilled,2)) OR (ROUND(QuantityReceivedOnly,2) != ROUND(INV_QuantityReceivedOnly,2)) OR (ROUND(QuantityOnHand,2) != ROUND(INV_QuantityOnHand,2)) OR (ROUND(QuantityReserved,2) != ROUND(INV_QuantityReserved,2)) OR (ROUND(QuantityAvailable,2) != ROUND(INV_QuantityAvailable,2)) OR (ROUND(QuantityOnOrder,2) != ROUND(INV_QuantityOnOrder,2)) OR (ROUND(QuantityExpected,2) != ROUND(INV_QuantityExpected,2))
Version Information
- Revised : 10/2012
- Version : Control 4.5+
Related SQLs
You could leave a comment if you were logged in.