=Explanation of SQL=

The total of all GL Accounts that are for Inventory Tracking (GLClassificationType 1003) should match the total value of inventory for that specific period. However, certain user actions may result in these numbers no longer matching. These actions include:

  • Using the Inventory GL Account as the “expense” account for an expense or non-accrued part in a Bill or Vendor Credit Memo.
  • Making manual Journal Entries into those accounts.
  • Extra GL entries in Orders due to a bug in 2009.

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

SELECT 
  EntryDateTime
  , Amount
  , (SELECT AccountName FROM GLAccount WHERE ID = GLAccountID) AS [Account Name]
  , (SELECT BillNumber FROM TransHeader WHERE ID = TransactionID) AS [Bill NUMBER]
  , (SELECT OrderNumber FROM TransHeader WHERE ID = TransactionID) AS [ORDER NUMBER]
  , Description
  , TransDetailID
-- , * 
FROM GL 
WHERE PartID IS NULL AND GLClassificationType = 1003
-- and TransactionID in (select ID from TransHeader where TransactionType in (1,6)) -- orders
-- and TransactionID in (select ID from TransHeader where TransactionType = 9) -- bills
-- and TransactionID is null -- manual entries
ORDER BY EntryDateTime
  • Entered : 11/2011
  • Version : 4.5+
You could leave a comment if you were logged in.