Featured Post

Dynamics GP 2018 is now Released

It is officially published that Microsoft Dynamics GP 2018 is available, the download link is provided below: Product download page ...

Saturday, April 26, 2014

Clear Data for Inventory Module –The Proper approach


Although this process is not something you would do not even once, but if you had to do it, there are essential information that should be considered for comprehensive inventory cleaning.

In addition to the above, This could be important for the implementers as in early stages of the project, clear data is a valid option.

Helping Note !

In case you have upgraded from previous version and have not deployed the HITB reset tool, this article would be of no benefit for you. In this essence, deploying the HITB reset tool would be of a higher priority for you, HITB Essentials Series – HITB Reset Tool Deployment

Case Study

In the following GP company; Fabrikam, considerable number of inventory transactions is available. Go to SQL management studio and run a simple select statement on the following tables;

  • IV30300 | Inventory Transaction History
  • IV10200 | Purchase receipt work
  • IV10201 | Purchase receipt work details
  • SEE30303 | Historical Inventory Trial Balance


Tables before


Now, going to the clear data, we have several options to choose from. On the clear data window (Microsoft Dynamics GP > Maintenance > Clear Data). Click on “Display” menu and ensure that the “Logical” option is checked.

When choosing “ inventory series “, you will have the following options to choose from:

  • Bill of Materials Cards
  • Bill of Materials Setup
  • Bill of Materials Transaction History
  • Bill of Materials Transactions
  • Inventory Control
  • Inventory Landed Cost Cards
  • Inventory Purchase Receipts
  • Inventory Reports Options
  • Inventory Sales Summary
  • Inventory Site Setup
  • Inventory Transaction History
  • Inventory Transaction Work
  • Inventory UOM Schedule Setup
  • Item Category Setup
  • Item Class Setup
  • Item Lot Attribute Master
  • Item Lot Category Master
  • Item Master
  • Item Serial Number Master
  • Stock Calendar
  • Stock Count
  • Stock Count History

In our scenario, I will choose to clear all of the above. So, once inserting all the above into the “selected tables” area, click on “Ok”

Clear Data


Now, going back to run the select statements on the inventory tables, we can clearly see that the HITB table is not affect at all with the clear data process.

tables after

In case this has not been noticed in initial stages after the inventory clear data, the following symptoms might be encountered;

  1. Inconsistent numbers among Inventory reports, inquiries and smart lists. In terms of cost and quantity balance
  2. Inventory Reconcile to General Ledger will not tie



The HITB tables should be cleared out in order for the process to be comprehensive and complete.

  • Go to ( Dynamics GP > Maintenance > SQL )
  • Choose the company database
  • Under product, choose “HITB report”
  • Highlight the “Inventory Transaction History Detail”
  • On the right side, check the options to “drop and create table”
  • Process

SQL Maintenance

drop data HITB


Now, the final result for the inventory tables can be seen below, as all tables have been cleared out



Helping Note !

In case you are not clearing all the inventory data, just the transactions and keeping the setup, cards , reports options, site setup and other configuration, you should apply the same approach mentioned above. Additional step is required upon completion, which is Inventory Reconcile (In order to reconcile the balances in IV00102 against the inventory transactions and come down to zero)


Best Regards,
Mahmoud M. AlSaadi

No comments:

Post a Comment