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

 

 

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”

 

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.

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

 

Resolution

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

 

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