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]
 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+
 +
 +
 +