{$page}

Explanation of SQL

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