Calling the Inventory Experts

(0) Share

Report

Posted on by Heather Roggeveen

9,146

I am in the process of implementing inventory at one of my client sites. While they have not be "tracking" inventory as such, because of the way we were doing the billing, we had inventory quantity tracking turned on - even though no purchases / stocktakes / adjustments were being done.

Inventory and Cost of Sales have been going to the same GL code to have no GL impact.

We are now turning the inventory on fully - but I have a bit of a problem. We have 4 & 1/2 years worth of inventory transactions (sales) in the system. So my plan was to zero all the balances (with the Inventory and COGS offsetting still). Then enter opening balances with the GL all pointing to the correct place and away we go.

As part of that, I wanted to run Inventory Reconcile, Checklinks etc to make sure the "house is tidy".

This is where I hit my problem. The inventory reconcile took 44 hours to run - 42 of which were for a single product. I used the Manual Logging feature in the Support Debugging Tool (David Musgrave, you are a legend) to continually check on it. What I found was that the majority of that time was going through the records in IV10200 and IV10201.

There are 385700 records in IV10200 of which 280940 relate to one item and IV10201 is similar.

I am now doing a stock enquiry on that item (Enquiry --> Inventory --> Item Stock Enquiry) and it is taking ages to load.

So questions:

- Is this an unwieldy amount of data in these tables - do you have clients with similar data loads and do they experience this?

- Now that I have run inventory reconcile once, will it take that long every time?

- What is the difference with having Item History ticked or not?

- If this is an unmanageable amount of data - what options are there for tidying it? We don't want to lose the sales history, but that is held in sales tables - can I effectively archive data in these tables without impact?

Would love to hear your thoughts on this!

Cheers

Heather

*This post is locked for comments

I have the same question (0)

All responses (16)

Answers (3)

Heather Roggeveen 9,146 on at

Like (0)

Report

Oh - and just to clarify - they use an external billing programme. We tracked inventory prior because we were using kits - and we wanted the invoices printed from GP to be the same as the external system. That requirement is now gone and we are bringing over individual line items.

Was this reply helpful? Yes No
Suggested answer

Frank Hamelly | MVP... 46,625 Super User 2025 Season 2 on at

Like (0)

Report

Heather, I've seen one client of mine that took about the same amount of time to reconcile due the volume of transactions they post. You can expect it to take a similar amount of time each time you reconcile. However, if you use the Reconcile routine in PSTL as opposed to the regular Inventory Reconcile, the PSTL version is faster because it only reconciles those items it deems 'needs' reconciled. I'm not sure of the logic differences between the two routines.

Reconciling History means all historical inventory transactions are reconciled as well. I would think once you've reconciled history once, you would not need to do it again if you reconcile on a regular basis, and this would speed up the reconcile process.

Just for fun, I would try using PSTL Inventory Reconcile in a test database to see how long it takes compared to your production reconcile.

Was this reply helpful? Yes No
Verified answer

L Vail 65,271 on at

Like (0)

Report

Hi Heather,

The IV10200 table details your Purchase Receipts the IV10201 table is the line detail for items sold or negative inventory adjustments.

What I would consider here is to lose those records. An easy way to do it is to execute an Inventory Year-End Close routine. You'll probably want to do this anyway to get your beginning balances set correctly. When you run the Inventory close, you have the option to remove all sold purchase receipts and historical cost changes for items that use Average Perpetual, Last In, First Out (LIFO) Perpetual, or the First In, First Out (FIFO) Periodic valuation method. You get to pick a date. The window looks like this:

To get an idea of what will be deleted, run the following script, but change the date to whatever date makes sense for you.

SELECT ITEMNMBR, TRXLOCTN, DATERECD, RCTSEQNM, RCPTSOLD, QTYRECVD, QTYSOLD

FROM IV10200

WHERE (QTYSOLD = QTYRECVD) AND (DATERECD <= CONVERT(DATETIME, '2018-12-31 00:00:00', 102))

I'd back up the database and take it for a spin before I actually would run this on Production. If you're just starting inventory, and you want your beginning balances to be zero - or want to zero them out so that you can put in new quantities and values, I would create stock count schedule that included all of my items. I would then start the count and, using SQL, change the counted value to zero in the IV10301 and the VERIFIED value to 1. Once you process the count, you'll get an inventory adjustment that will change the quantities to zero. A long answer to a short question, what do you think?

Kind regards,

Leslie

Was this reply helpful? Yes No
Heather Roggeveen 9,146 on at

Like (0)

Report

Thanks Leslie

That sounds like a plan! I am currently posting the "zero" adjustment on the one inventory item. We are about 14 hours into the posting and I am estimating about 10 hours to go!

Year end close sounds like a great idea - but I just want to confirm something. The client's actual year end is the end of March (we don't like year end at Christmas time in New Zealand). Can I do the year end now and then again at the end of March? It doesn't seem date driven.

Doing the year end is obviously then what makes the difference between reconciling history or not.

Would is also impact the 'Item Stock Enquiry' screen? That took 6 hours to load the screen yesterday and didn't appear to have everything in it.

I have worked with Inventory for quite a number of years - but it isn't until you hit something like this that you really need to question functionality.

Cheers

Heather

Was this reply helpful? Yes No
Heather Roggeveen 9,146 on at

Like (0)

Report

Thanks Frank - I will give that a try.

Not sure I like your idea of "fun" - 42 hours monitoring..... :-)

You definitely learn more about processes though when you deal with something like this!

Was this reply helpful? Yes No
Verified answer

L Vail 65,271 on at

Like (0)

Report

Hi Heather,

As for Inventory Year-end, you can do that close as many times as you want. You are correct in that it isn't date driven. When you close inventory it WILL start the buckets over again as far as Quantities Sold or Returned. Those fields are on the Item inquiry window. Also, the receipts that were deleted will no longer show up in the Purchase Receipts inquiry window. Those transaction records (the receipts) will no longer be in the database. Your Purchase Receipts report will not show that data either. It's gone, never to return. It is not 'archived'.

Though I can't find the table right now, somewhere in the system it keeps track of quantities sold/returned 'this year' and 'last year'. As soon as you run the inventory close, it zeros out the 'this year' column and moves those values into the 'last year' column. If you close, and then close again, both of those columns will end up being zero. But hey, it'll be a clean start! You can always get your sales information from SOP.

Regarding reconciling history. You will still need to do this once. Your historical inventory transactions will still exist, just not the Purchase Receipts. Once you've got it cleaned up, you shouldn't really have to do it again. However, if you choose to delete Item Transaction History (see below) the utility shouldn't take long.

The Item Sock Enquiry screen you mentioned might not be faster, because the table that holds the Purchase Receipts isn't used in that window. I think the Inquiry window is pulling its data from the Item Transaction History table. Check the IV30300 and see how many records are in there. There are probably a ton. If the Item transaction numbers of the old transactions are not relevant, you'll need to run Inventory Utilities to delete the Item Transaction History.

Oh, one more thing - you'll not be able to use the PO return for the item receipts that have been deleted.

For the benefit of anyone following this thread, if you only want to create Inventory Cards to 'store' item numbers and descriptions (and not have all of these transaction records), go to the System Registration screen and Uncheck the Inventory checkbox

I think that addressed your questions. Please let me know if you have any questions or if this doesn't seem to flesh out.

Kind regards,

Leslie

Was this reply helpful? Yes No
Heather Roggeveen 9,146 on at

Like (0)

Report

Wonderful - thanks so much Leslie. I have a way to go this week with getting the client up and running properly. Once I have any further relevant details, I will update for future readers and mark the appropriate responses as answers.

Will consider the implications of the purchase receipts - sounds like it might pay to keep some around for a while, although purchase returns are less likely in my clients line of business.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Heather, my question below.

1. Is the data load issues occurs only in inventory module? How about AR, AP and GL?

2. What is your Sql server configuration?

Can you try recreating indexes for Inventory tables in you Test server? Sometimes indexes would have been fragmented. Just a thought.

Was this reply helpful? Yes No
Justin Thorp 2,265 on at

Like (0)

Report

Hi,

I just wanted to throw this idea into the mix (it may or may not be applicable depending on your exact requirements):

1.) Make a backup of the company (essentially this is the archive)

2.) Run clear data on: "Inventory Goods Received" and "Inventory Transaction History" (Also Item Serial Number Master and Item Lot Number Master if you've been using Serials/Lots).

3.) Run reconcile on all items, it should be quite fast now, and this should set all quantities to zero.

4.) Enter/post opening inventory balances.

======================================

Of course, test all of the above thoroughly in a test company before implementing on live.

Thanks,
Justin

Was this reply helpful? Yes No
Heather Roggeveen 9,146 on at

Like (0)

Report

Thanks Justin and Arunprasath.

I am pretty sure it is not the server itself causing issues. Reconcile on all the other items together took only 4 hours. It is ticking away at quite a fast pace, it is just the sheer volume of transactions.

I have considered the idea of clearing data somehow, but would not do it at this stage. I am not a fan of the clear data routines as they can break links in the database and create other issues. I would probably choose to create a new item code rather than clear data on the old.

Those options I would consider if after completing year end close etc the day to day operation of inventory is unwieldy.

Thanks for your feedback - I appreciate your time.

Cheers

Heather

Was this reply helpful? Yes No