{$page}

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.

code_formatsql

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

code

• Entered : 02/2011
• Version : 4.5+

• Backlinks include_pagepage_componentbacklinks