This SQL pulls all inventoried parts and computes a valuation for each one. It may be used to form the basis of a query to match the balance in GL inventory asset accounts.

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

SELECT 
  AssetAccountID
  , (SELECT AccountName FROM GLAccount WHERE ID = AssetAccountID) AS AccountName
  , InventoryValue
  , GLAssetAmount
  , (COALESCE(InventoryValue,0) - COALESCE(GLAssetAmount,0)) AS Adjustment
FROM
(    SELECT 
            SUM(Inventory.QuantityBilled * Inventory.AverageCost) AS InventoryValue,
            Inventory.AssetAccountID
     FROM Part
     LEFT JOIN Inventory ON Part.ID = Inventory.PartID AND Inventory.ClassTypeID = 12200
     WHERE Part.TrackInventory = 1 AND Part.AccrueCosts = 1
     GROUP BY Inventory.AssetAccountID
) AS TempInv
FULL OUTER JOIN
(
SELECT GLAccountID, SUM(amount) AS GLAssetAmount
FROM GL
WHERE GLAccountID IN (SELECT ID FROM GLAccount WHERE GLClassificationType = 1003)
GROUP BY GLAccountID
) TempAllGL
ON TempInv.AssetAccountID = TempAllGL.GLAccountID
ORDER BY AccountName

SELECT 
  COALESCE((SELECT AccountName FROM GLAccount WHERE ID = AssetAccountID), '') AS [GL Account]
  , COALESCE((SELECT ItemName FROM Part WHERE ID = GLPartID), '') AS [GL Part]
  , COALESCE((SELECT ItemName FROM Part WHERE ID = InvPartID), '') AS [Inventory Part]
  , COALESCE(InventoryValue, 0) AS [Inventory VALUE]
  , COALESCE(GLAssetAmount, 0) AS [GL VALUE]
  , (COALESCE(InventoryValue,0) - COALESCE(GLAssetAmount,0)) AS Adjustment
  , AssetAccountID
  , InvPartID
  , GLPartID
FROM
(    SELECT 
            SUM(Inventory.QuantityBilled * Inventory.AverageCost) AS InventoryValue,
            Inventory.AssetAccountID,
			PartID AS InvPartID
     FROM Part
     LEFT JOIN Inventory ON Part.ID = Inventory.PartID AND Inventory.ClassTypeID = 12200
     WHERE Part.TrackInventory = 1 AND Part.AccrueCosts = 1
     GROUP BY Inventory.AssetAccountID, PartID
 
 
) AS TempInv
FULL OUTER JOIN
(
SELECT GLAccountID, SUM(amount) AS GLAssetAmount, PartID AS GLPartID
FROM GL
WHERE GLAccountID IN (SELECT ID FROM GLAccount WHERE GLClassificationType = 1003)
GROUP BY GLAccountID, PartID
) TempAllGL
ON TempInv.AssetAccountID = TempAllGL.GLAccountID AND TempInv.InvPartID = TempAllGL.GLPartID
WHERE (COALESCE(InventoryValue,0)  0 OR COALESCE(GLAssetAmount,0)  0 )
ORDER BY [GL Account], [GL Part], [Inventory Part]

<code sql> select

      Part.ItemName as [Part Name],
      (case when coalesce(Part.AccrueCosts, 0)=1 
             then 'Real' 
             else 'Computed' end) as [Effective Cost Type],
      (case when coalesce(Part.AccrueCosts, 0)=1 and Part.AssetAccountClassTypeID  8001
            then 'Invalid Asset Account'
            when Inventory.QuantityAvailable < 0
            then 'Negative Inventory Balance'
            when Part.UnitCost