This query shows any discrepencies between the On-Order Quantity of an inventoried Part and the total of all line items in Purchase Orders that are On-Order.

None. This is a selection query and no data is modified in the running of it.

SELECT 
    COALESCE(POPartName, InvPartName) AS PartName,
    COALESCE(InventoryData.PartID, POData.PartID) AS PartID,
    COALESCE(InventoryData.DivisionID, POData.DivisionID) AS DivisionID,
    COALESCE(InventoryData.WarehouseID, POData.WarehouseID) AS WarehouseID,
    InventoryData.InventoryID,
    COALESCE(InvQuantityOnOrder, 0) AS InvQuantityOnOrder,
    COALESCE(POQuantityOnOrder, 0) AS POQuantityOnOrder,
    COALESCE(POQuantityOnOrder, 0) - COALESCE(InvQuantityOnOrder, 0) AS Delta
FROM
(
    SELECT 
        ID AS InventoryID
        , PartID
        , WarehouseID
        , DivisionID
        , QuantityOnOrder AS InvQuantityOnOrder
        , ItemName AS InvPartName
    FROM Inventory
    WHERE QuantityOnOrder  0
    AND ClassTypeID = 12200
) InventoryData
FULL OUTER JOIN 
(
    SELECT 
        (SELECT ItemName FROM Part WHERE ID = TD.ItemID) AS POPartName
        , SUM(TD.Quantity) AS POQuantityOnOrder
        , COALESCE(TD.WarehouseID, 10) AS WarehouseID
        , COALESCE(TH.DivisionID, 10) AS DivisionID
        , TD.ItemID AS PartID
 
    FROM VendorTransDetail TD
    JOIN TransHeader TH ON TH.ID = TD.TransHeaderID
    WHERE TH.TransactionType = 7
    AND TD.StationID IN (SELECT ID FROM Station WHERE StationName LIKE 'Ordered')
    AND TD.ItemID IN (SELECT ID FROM Part WHERE TrackInventory = 1)
 
    GROUP BY TD.ItemID, COALESCE(TD.WarehouseID, 10), COALESCE(TH.DivisionID, 10)
 
) POData
ON InventoryData.PartID = POData.PartID 
AND InventoryData.WarehouseID = POData.WarehouseID 
AND InventoryData.DivisionID = POData.DivisionID 
WHERE COALESCE(InvQuantityOnOrder, 0)  COALESCE(POQuantityOnOrder, 0) 
ORDER BY PartName

• Entered : 02/2011
• Version : 4.5+