{$page}

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.

code_formatsql

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

code

code_formatsql

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

code_formatsql

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