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]
 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+
 +
 +
 +