Inventory and Item Windows in Dynamics GP. For the most common tables used in this module go here. The Inventory tables have a lot more going on than at first glance. The data in these windows needs to be scrutinized because if proper procedure is not taking place in POP and SOP the info in some of these fields are going to be way off.
–All check boxes in GP: unchecked is a 0 (zero) in the DB and checked is a 1.
–All drop down menus on windows, just count down the selection in the drop down: the first item listed is a 1 in the DB, second a 2, etc.
Item Maintenance Window
- On the Item Maintenance window Quantity On Hand and Quantity Available are sums across ALL sites (from iv00102)
- Current Cost fluctuates. Current cost is actually the last landed cost of an item. If you want to have a replacement cost either use Standard cost, create a price level for the item, or write a job that updates Current Cost nightly from a user created table.
- If you ever want to see the real cost of your inventory use the IV10200 table. This table contains all the LIFO/FIFO layers of your items as well as how many pieces are remaining in each layer AND the age of the items.
- Currency Decimals. There is only one currency decimal for an item. So if you buy it or sell it it uses those same decimals. This can be frustrating.
Item Maintenance Options Window
Item Price List Maintenance Window
- If you have multiple price breaks and multiple price levels for each item this window can be a real nightmare to update using Integration Manager when you have price increase. I have some SQL update queries listed to help but using % of list as your price method can really help get around this problem.
- Again the Currency Decimals is just the same as from Item Maintenance. I do not know why GP doesn’t offer two different options for decimals, one for purchasing and one for sales they just don’t. It can be very frustrating.
Item Vendors Maintenance
- On Order Amount. I have seen this way off. If your purchasing dept. isn’t clearing out POs and leaving line items open that have been cancelled or backordered it can cause a real mess. If this is a problem I have some queries that can help keep things locked down.
- Average Lead Time. This is computed by using the PO origination date to the date the item was received. Because it is an average this can get out of whack easily. Items being bought without POs, not cancelling items on POs, one large delay on an item…they can all lead to the problem. If you are using 3rd party procurement software it can be a mess. One idea around it is creating a table with estimated lead time per vendor and use that as your lead time in purchasing.
Item Transaction Entry
- What’s the difference between an Adjustment and a Variance? Well, nothing really. You can tell it to go to different GL accounts. Many people I have worked with used Adjustments when they add or take out inventory permanently and they know really why it is getting taken out and then use Variances when stuff is just lost or found. Going to separate GL accounts can help keep tabs on things and avoid nasty surprises at month or year end.
- If you do transaction level posting on this window it will not get pushed thru to GL until the window is closed. If you are doing batch level posting (which I recommend) make sure the checkbox is checked “Post to General Ledger”. If you don’t you will only see your physical inventory move and there will not be a distribution pushed thru to GL. Sometimes you may want this but most of the time if you lost or found some product you really want it to reflect on your books or you will get a surprise when you reconcile your inventory at month or year end.