This query totals the inventory value for inventoried parts for each GL Asset Account. It is based on the inventory_valuation_sql query.

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

SELECT AssetAccountID, AssetAccountName, SUM(InventoryValue) AS InventoryValue,
   (SELECT SUM(Amount) FROM GL WHERE GLAccountID = AssetAccountID) AS GLAssetValue,
   SUM(InventoryValue) - (SELECT SUM(Amount) FROM GL WHERE GLAccountID = AssetAccountID) AS AssetDifference
FROM
(    SELECT 
            Part.ItemName AS PartName,
            Part.AccrueCosts,
            Part.UnitCost,
            Inventory.QuantityBilled * Inventory.AverageCost AS InventoryValue,
            Inventory.AverageCost,
            Inventory.QuantityBilled,
            Inventory.QuantityReceivedOnly,
            Inventory.QuantityOnHand,
            Inventory.QuantityReserved,
            Inventory.QuantityAvailable,
            Inventory.QuantityOnOrder,
            Inventory.QuantityExpected,
            Inventory.UnitType,
            Inventory.UnitID,
 
            AssetGLAccount.AccountName AS AssetAccountName,
            AssetGLAccount.ID AS AssetAccountID,
            AssetGLAccount.ClassTypeID AS AssetAccountClassTypeID,
 
            ExpenseGLAccount.AccountName AS DefaultBillExpenseAccountName,
            Part.ExpenseAccountID AS DefaultBillExpenseAccountID,
            Part.ExpenseAccountClassTypeID AS DefaultBillExpenseAccountClassTypeID,
 
            Part.ID AS PartID,
            Inventory.ID AS InventoryID,
            Inventory.ModifiedDate AS LastInventoryChangeDate
 
 
    FROM Part
    LEFT JOIN Inventory ON Part.ID = Inventory.PartID AND Inventory.ClassTypeID = 12200
    LEFT JOIN GLAccount AssetGLAccount 
            ON AssetGLAccount.ID = Inventory.AssetAccountID 
    LEFT JOIN GLAccount ExpenseGLAccount 
            ON ExpenseGLAccount.ID = Part.ExpenseAccountID
 
    WHERE Part.TrackInventory = 1 AND Part.AccrueCosts = 1
) AS Temp
GROUP BY AssetAccountID, AssetAccountName
ORDER BY AssetAccountName

SELECT AssetAccountName, SUM(InventoryGLValue) AS InventoryGLValue
FROM
(	SELECT 
			Part.ItemName AS PartName,
			Part.AccrueCosts,
			Part.UnitCost,
			(CASE WHEN COALESCE(Part.AccrueCosts, 0)=1 
				   THEN 'Real' 
				   ELSE 'Computed' END) AS EffectiveCostType,
			(Inventory.QuantityBilled * Inventory.AverageCost) AS InventoryValue,
			(CASE WHEN COALESCE(Part.AccrueCosts, 0)=1 
				  THEN (Inventory.QuantityBilled * Inventory.AverageCost) 
				  ELSE 0 END) AS InventoryGLValue,
			Inventory.AverageCost,
			Inventory.QuantityBilled,
			Inventory.QuantityReceivedOnly,
			Inventory.QuantityOnHand,
			Inventory.QuantityReserved,
			Inventory.QuantityAvailable,
			Inventory.QuantityOnOrder,
			Inventory.QuantityExpected,
			Inventory.UnitType,
			Inventory.UnitID,
			AssetGLAccount.AccountName AS AssetAccountName,
			AssetGLAccount.ID AS AssetAccountID,
			AssetGLAccount.ClassTypeID AS AssetAccountClassTypeID,
			EffectiveGLAccount.AccountName AS EffectiveAccountName,
			EffectiveGLAccount.ID AS EffectiveCostAccountID,
			EffectiveGLAccount.ClassTypeID AS EffectiveCostAccountClassTypeID,
			ExpenseGLAccount.AccountName AS DefaultBillExpenseAccountName,
			Part.ExpenseAccountID AS DefaultBillExpenseAccountID,
			Part.ExpenseAccountClassTypeID AS DefaultBillExpenseAccountClassTypeID,
			Part.ID AS PartID,
			Inventory.ID AS InventoryID,
			Inventory.ModifiedDate AS LastInventoryChangeDate
 
	FROM Part
	LEFT JOIN Inventory ON Part.ID = Inventory.PartID
	LEFT JOIN GLAccount EffectiveGLAccount 
			ON EffectiveGLAccount.ID = (CASE WHEN COALESCE(Part.AccrueCosts, 0)=1 
											 THEN Part.ExpenseAccountID 
											 ELSE Part.ComputedCostAccountID END)
	LEFT JOIN GLAccount AssetGLAccount 
			ON AssetGLAccount.ID     = (CASE WHEN COALESCE(Part.AccrueCosts, 0)=1 
											 THEN Inventory.AssetAccountID 
											 ELSE NULL END)
	LEFT JOIN GLAccount ExpenseGLAccount 
			ON ExpenseGLAccount.ID   = Part.ExpenseAccountID
	WHERE Part.TrackInventory = 1
) AS Temp
GROUP BY AssetAccountName
ORDER BY AssetAccountName

• Entered : 7/16/2009
• Version : 4.3