Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control_sql_-_identify_manual_postings_to_inventory_gl_accounts [2019/01/27 11:29] (current)
Line 1: Line 1:
 +=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.
 +
 +
 +
 +===== Risk of Data Corruption if Run Improperly =====
 +
 +**None**. This is a selection query and no data is modified in the running of it.
 +
 +
 +
 +===== SQL =====
 +
 +
 +
 +<code sql>
 +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
 +</​code>​
 +
 +
 +
 +===== Version Information =====
 +  * Entered : 11/2011
 +  * Version : 4.5+
 +
 +
 +