======  ======



===== 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 sql>
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 sql>
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 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