Physical Inventory in Binned and Lotted warehouse

[JayTheGeek]

Hi folks, I have a dilemma on my hands and would like to see if there is experience online that I can leverage.

Our environment is Macola ES 360 V4.0.0.323 on SQL2000. We are a distribution company with four stocking locations in our primary company. Each warehouse is multibin. Most items are assigned dye lots. We have approx. 2700 stocked items.

One of our locations is a distribution hub and gets most of the PO Receiving. Three other locations get most stock via 'Transit' inventory. All four locations sell and 'confirm ship' inventory. We move inventory in significant volume.

We did our first inventory back in December 2006, things did not go well. After posting count batches, close to half our inventory was in error. Location quantities were wrong compared to count quantities and location quantities did not match the sum of bin and/or lot quantities.

19 man days of data entry later, we got things back in Balance.

We are scheduled to do inventory again at the end of May. I have spent the last three weeks testing.

I have created count batches for each of my physical warehouses; so I have four batches to test, each with different combinations of post dates and the use freeze quantities flag. I created the test batches in our live database to capture a few days of inventory movement before migrating the database to our test environment. I have done data entry for a select few hundred items for each batch. I have posted twice using different values for the post date. (That was based on my theory of what I did wrong in December)

I had hoped to prove that the posting date or freeze date was the source of my problems. I have posted six distinctly different batches I have not had one post 'clean'.

Although the pre post and post register report 'no errors found' what I am left with is similar to the December results where a significant portion of my database is out of balance.

Has anyone out there done physical inventory in a similar environment? Have you seen similar posting quantity errors?


Regards,

Jay

 

[JayTheGeek]

Tpllc,

“I'm very interested in the result of the non frozen inventory results”

This option did not really change much in my test cases. Both settings seem to keep track of inventory movement prior to the freeze date, providing said movement was related to a bin/lot record with a count tag assigned. Perhaps if my test scenarios had movement ‘after’ the freeze date prior to posting, I would see some differences.

However, I can say with certainty that this option does not seem to have any impact on the occurrences of the issue that I have encountered.

But that is starting to make sense. More to follow…

 

[JayTheGeek]

NEmacGuy,

“Do you have a variance report of Item Location to Bin to Serial/Lot_Bin.”

This one plays into my strengths. I am quite comfortable with SQL and Crystal reports. We have been managing issues related to Location/Bin/Lot quantities for quite some time, so I did have some good queries and reports for this.

I am happy to report that you guys have helped me to refine those queries and reports. We do regularly clean up some of our zero quantity bins. That can, and does, cause problems if a Lot record with quantity is related to a zero quantity bin. That certainly was the case with more than one item in my test data.

Well, my initial join went Location – Bin – Lot, so I missed these stranded lot records. It is rewritten now and hopefully bullet proof.

 

[JayTheGeek]

I have been informed that the primary issue may be addressed in ES372:
“Bug report Macola ES 19.132.890 System updating iminvloc on hand qty incorrectly if new binned added on the fly during tag entry”

Unfortunately, I am unable to see the detail information behind this issue. However, preliminary information does indicate that the issue has been identified.

I am expecting to review a copy of my database posted with this new release. So I will be able to identify if this catches all or most of the issues that I have encountered.


My Count activities start Wednesday, so essentially I have little or no time to commit to testing a new release of software. The silver lining in my situation today, is that I understand the nature of the issue and can predict the outcome. So given time or the right tools (or a small army of data entry staff) I could manage this count process to success. I am not comfortable with introducing new variables into this equation.

That said, if testing proves to be successful, I may feel motivated to twist some arms and do a shotgun update next weekend.

I will update this thread once I download and review my updated test data (posted with ES372).

 

[tpllc]

Jay, Have you abandoned the idea of a non frozen count. That may be your best bet in the limited time constraints.


Steve Henley
Trianglepartners.com
Exact Software consulting, sales and implementations.

If the only tool you can use is a hammer then all your problems look like nails.

 

[JayTheGeek]

Steve,

I certainly have not abandoned the idea. It does seem to make sense to me. However it also goes against the procedures that our reseller recommended. Unfortunately I don't think my testing gave me a good opportunity to examine the option.

I believe that I need to follow the past procedure in the count that we are starting today. I am familiar with the results (i.e. errors) and will need to rely on that familiarity to resolve the issues.

But I think that I will be testing this option in more detail for next time around.

It does make sense.

 

[JayTheGeek]

The bottom line...

I think that I have reached conclusion on this issue.

With respect to Bug Report 19.132.890; that was a swing and a miss for us. This does correctly identify and resolve what we see as a small portion of our problems. It fixes these at the Location Qty level. But leave bin and lot quantity in error.

So after a bit of back and forth with support, I have another notch in my keyboard. Bug report 22.762.422 was created today to deal with this issue.

The sample procedure proposed for testing 22.762.422 looks far more complex than what was documented in 19.132.890. It should address the majority of issues that I have brought forward.

Thanks for your input and support guys. Hopefully I am able to contribute in some way to return the courtesy.

Regards,

Jay