====== {$page} ======



===== Explanation of SQL =====



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.



===== Risk of Data Corruption if Run Improperly =====

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



==SQL for Control 4.5 - Summary Results==



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



==SQL for Control 4.5 - Detailed Results==



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



===== SQL for Control 4.3 =====



[[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