Differences

This shows you the differences between two versions of the page.

Link to this comparison view

compare_on-order_po_line_items_and_on-order_inventory_quantites [2019/01/30 09:22] (current)
Line 1: Line 1:
 +====== ​ ======
 +
 +
 +
 +===== Explanation of SQL =====
 +
 +
 +
 +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.
 +
 +
 +
 +===== Risk of Data Corruption if Run Improperly =====
 +
 +**None**. This is a selection query and no data is modified in the running of it.
 +
 +
 +
 +===== SQL =====
 +
 +
 +
 +<code sql>
 +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>​
 +
 +
 +
 +===== Version Information =====
 +  * Entered : 02/2011
 +  * Version : 4.5+
 +
 +
 +