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.