Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How to find transactions that are posted to Inventory but not to General Ledger in Microsoft Dynamics GP

How to find transactions that are posted to Inventory but not to General Ledger in Microsoft Dynamics GP

View products that this article applies to.

INTRODUCTION

This article describes how to find transactions that are posted to Inventory in Microsoft Dynamics GP, but that are not posted to General Ledger in Microsoft Dynamics GP. These transactions are not posted to General Ledger because the Post to General Ledger check box in the Inventory Batch Entry window is not selected.

↑ Back to the top

More Information

To find transactions that are posted to Inventory, but that are not posted to General Ledger, use one of the following methods.

Method 1

  1. Reprint the inventory posting journals. To do this, follow these steps:
    1. On the Reports menu, point to Inventory, and then click Posting Journals.
    2. In the Reprint Inventory Journals window, click New to create a report option.
    3. In the Reprint Inventory Journal Options window, type a name in the Option box, select Date Posted in the Ranges list, set the starting date in the From field, set the ending date in the To field, and then click Insert.
    4. Click Save.
    5. Close the Inventory Journal Options window.
    6. In the Reprint Inventory Journals window, select the new option that you created in step 1c in the Options section, click Insert, and then click Print. You can see the amount that was posted. You can also see the accounts that should have been posted to General Ledger.
  2. Reprint distributions that were posted to General Ledger. To do this, follow these steps:
    1. On the Reports menu, point to Inventory, and then click History.
    2. In the Inventory History Reports window, select Distribution History in the Reports list.
    3. Click New to create a report option.
    4. In the Inventory History Report Options window, type a name in the Option box, select Date Posted in the Ranges list, and then set the same date range that you set in step 1c.
    5. Click Save.
    6. Close the Inventory History Report Options window.
    7. In the Inventory History Reports window, select the new option that you created in step 2c in the Options section, click Insert, and then click Print.
  3. Compare the transactions that are posted to Inventory and the transactions that are posted to General Ledger, and then reprint the cross reference journals. To do this, follow these steps:
    1. On the Reports menu, point to Financial, and then click Cross-Reference.
    2. Create a new report option that uses the same date restriction as the previous inventory transactions.


      Note If these are all Inventory Adjustment transactions, you can restrict the report results to the same inventory transactions. To do this, select Source Document in the Range list, and then select IVADJ in the From field and in the To field.

Method 2

Note Before you follow the instructions in this article, make sure that you have a complete backup copy of the database that you can restore if a problem occurs.

To find the transactions, run a script in Microsoft SQL Server 2005 Management Studio or in SQL Query Analyzer.

SQL Server Management Studio

  1. Click Start, point to All Programs, point to Microsoft SQL Server 2008 or Microsoft SQL Server 2005, and then click SQL Server Management Studio.
  2. In the "Connect to Server" window, select the server that is running SQL Server in the Server name list, select SQL Authentication in the Authentication list, type sa in the User name box, type the password for the sa user in the Password box, and then click Connect.
  3. On the File menu, point to New, and then click Query with Current Connection.
  4. Type the following script. 
    select * from IV30100 where TRXSORCE
    NOT IN (select ORTRXSRC from GL20000) and BCHSOURC = 'IV_Trxent'
  5. Click Query, and then click Execute.

SQL Query Analyzer

  1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
  2. In the "Connect to Server" window, select the server that is running SQL Server in the Server name list, select SQL Authentication in the Connect using list, type sa in the User name box, type the password for the sa user in the Password box, and then click OK.
  3. In the Query window, type the following script.
    select * from IV30100 where TRXSORCE
    NOT IN (select ORTRXSRC from GL20000) and BCHSOURC = 'IV_Trxent'
  4. Click Query, and then click Execute.
The script in Method 2 can be used to look in the "Year-to-Date Transaction Open" table (GL20000). The "Year-to-Date Transaction Open" table (GL20000) contains open posted transactions in Financials and in General Ledger. If you want to look in the Transaction Work table (GL10000) or in the Account Transaction History table (GL30000), change GL20000 to GL10000 or to GL30000 in the script.

If you want to look for inventory transfers, change "BCHSOURC = 'IV_Trxent'" to "BCHSOURC='IV_Trans'." "BCHSOURC='IV_Trans'" indicates that the transactions originate from the Inventory Transfer Entry window and that the transactions are batch posted.

If you change "BCHSOURC = 'IV_Trxent'" to "BCHSOURC='IV_Trans'," the complete script resembles the following script. 
select * from IV30100 where TRXSORCE
NOT IN (select ORTRXSRC from GL20000) and BCHSOURC=’IV_Trans’

↑ Back to the top

Keywords: kbnosurvey, kbtshoot, kbexpertisebeginner, kbexpertiseinter, kbmbspartner, kbmbsmigrate, kbhowto, kb, kbarchive

↑ Back to the top

Article Info
Article ID : 932023
Revision : 1
Created on : 1/7/2017
Published on : 4/4/2016
Exists online : False
Views : 178