This query is still a work-in-process.

The purpose of this query is to produce a historical tracking of all inventory movements for a part. It combines entries from the GL, InventoryLog, Journal, and PartUsage tables.

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

DECLARE @PartID INT;  
SET @PartID = 1378
SELECT Journal.PartID 
       ,(SELECT ItemName FROM Part WHERE ID = @PartID) AS PartName
       , QueryStartDateTime AS TheDate
       , COALESCE(QuantityBilled, DetailAmount) AS QuantityBilled
       , COALESCE(InventoryLog.QuantityReceivedOnly, 0) AS QuantityReceivedOnly
       , COALESCE(InventoryLog.QuantityReserved, 0) AS QuantityReserved
       , COALESCE(InventoryLog.QuantityOnOrder, 0) AS QuantityOnOrder
       , InventoryLog.QuantityBilled * InventoryLog.UnitCost AS Cost
       , InventoryLog.UnitCost
       , Journal.Description
       , CASE 
           WHEN Journal.ClassTypeID = 20520 THEN 'Order Usage' 
           WHEN Journal.ClassTypeID = 20530 THEN 'Inv. Adjustment' 
           ELSE 'Journal' 
         END AS TheSource
       , CAST(Notes AS VARCHAR(255)) AS Notes
       , (SELECT OrderNumber FROM TransHeader WHERE ID = TransactionID) AS OrderNumber
       , TransactionID AS TransHeaderID
       , EmployeeID
       , Journal.ID, Journal.ClassTypeID
       , StartGLGroupID AS GLGroupID
       , CASE 
           WHEN Journal.ClassTypeID = 20520 THEN 2
           WHEN Journal.ClassTypeID = 20530 THEN 1
           ELSE 0
         END AS SortOrder
FROM Journal
LEFT JOIN InventoryLog ON Journal.ID = InventoryLog.ID
WHERE Journal.PartID = @PartID
UNION 
SELECT PartID
       ,(SELECT ItemName FROM Part WHERE ID = @PartID) AS PartName
       , EntryDateTime AS TheDate
       , Quantity AS QuantityBilled
       , 0 AS QuantityReceivedOnly
       , 0 AS QuantityReserved
       , 0 AS QuantityOnOrder
       , Amount AS Cost
       , CASE WHEN COALESCE(Quantity, 0) = 0 THEN NULL ELSE Amount / Quantity END AS UnitCost
       , Description
       , 'GL' AS TheSource
       , CAST(Notes AS VARCHAR(255)) AS Notes
       , (SELECT OrderNumber FROM TransHeader WHERE ID = TransactionID) AS OrderNumber
       , TransactionID AS TransHeaderID
       , EmployeeID
       , ID, ClassTypeID
       , GroupID AS GLGroupID
       , 4 AS SortOrder
FROM GL
WHERE PartID = @PartID AND GLClassificationType = 1003 -- only pull changes to inventory
UNION
SELECT PartID
       ,(SELECT ItemName FROM Part WHERE ID = @PartID) AS PartName
       , PostDate AS TheDate
       , Amount AS QuantityBilled
       , 0 AS QuantityReceivedOnly
       , 0 AS QuantityReserved
       , 0 AS QuantityOnOrder
       , Cost
       , CASE WHEN COALESCE(Amount, 0) = 0 THEN NULL ELSE Cost / Amount END AS UnitCost
       , COALESCE(Description, 'Usage Card') AS Description
       , 'Part Usage' AS TheSource
       , NULL AS Notes
       , (SELECT OrderNumber FROM TransHeader WHERE ID = TransHeaderID) AS OrderNumber
       , TransHeaderID
       , EmployeeID
       , ID, ClassTypeID
       , NULL AS GLGroupID
       , 3 AS SortOrder
FROM PartUsageCard
WHERE PartID = @PartID 
ORDER BY TheDate DESC, SortOrder

• Entered : 12/2009
• Version : Control 4.3+