Store Operation upload to HQ quantity issues

(0) Share

Report

Posted on by Grier Fleischhauer

782

Something screwy is occuring with quantiy sold items uploading to HQ. I have two stores, one store uploads quantities correctly, the other store uploads random quantities. Today the one store with issues sold exactly 100 items, when I ran a HQ descrepency report I had 98.65 total item descrepencies. Looking at each item in the descrepency report mostly the items in HQ were off by one or two. But the really strange behavior is that I had many items decrement by a fraction, so an item would be 1.1 or 2.65 etc in HQ. All of the items should be whole numbers. What could possibly be going wrong and how can I correct this?

Recent changes to my system: our server software went to sql2008 express r2 which houses both HQ and the one store that the inventory is getting screwed up. I installed the backwards compatibility software on the server for sql2008/2005. RMS SO is 2.0.1004 and HQ is 2.0.1004. The other store is running sql2005 express.

*This post is locked for comments

I have the same question (0)

All responses (17)

Answers (1)

Community Member on at

Like (0)

Report

Hi Grier,

That's strange behavior that I have never seen with RMS HQ. I would certainly verify that all the POS and HQ Client at the "bad" store are running the same version as the rest of the system. Also, your report on fractional quantities made me think of Regional Settings - any chance that one location is set for something other than US? Have you run item movement on one of the fractional items? Lastly, any customizations?

Was this reply helpful? Yes No
Grier Fleischhauer 782 on at

Like (0)

Report

All machines are running with RMS 2.0.1004. I looked into regional settings and I don't see an issue. We have no customizations or add-ins. Further information: I had to re-install all of my server software and rms software due to SBS2003 Server software issues. So I did a complete fresh install. I saved back ups of both HQ and SO databases that are housed on this one server and then restored them. When I set up the HQclient, I had accidentily referenced the HQ database instead of the "bad" store's database. So for two days I was getting errors on HQ Client. I researched that and found my mistake and pointed tthe HQClient to the proper store database. Could this have corrupted my HQ database? Today I am still getting HQ random fractional decrements and other whole number decrements that do not match the store database quantities.

Was this reply helpful? Yes No
Spencer McCandless 2,087 on at

Like (0)

Report

We had a similar issue with HQ quantities decrementing about a year ago, though I never experienced any fractional quantities. In fact, if you had asked me an hour ago, I would've said that the quantity field was int data type, but I just checked, and sure enough it's float .

Anyway, in our case it turned out to be a few different factors.

One was that transaction numbers were being reused following the restoration of a backup, which was leading to all kinds of weird results. Is it possible that transactions from the bad store that occurred after the backup that you restored from was made were upload to HQ?

The other big issue was that we were running XP and hadn't installed a necessary hotfix that addressed a problem that resulted in random data being uploaded. The KB article on it only mentions SQL 2005 and variants, but to cover all the bases, are you running XP, Vista, or Windows Server 2008?

I know this isn't much to go on, but I hope it helps.

Was this reply helpful? Yes No
Jeff @ Check Point ... 13,382 on at

Like (0)

Report

Compare the 2 stores Options Settings in Manager | File | Configuration | Options tab

Particularly the one that says Require Decimal entry.

Also if XP, need to install support.microsoft.com/.../961451

Was this reply helpful? Yes No
Grier Fleischhauer 782 on at

Like (0)

Report

I checked and compared the Option's settings and both stores are the same. The "require Decimal" setting are both unchecked. I do use XP-sp3 on my registers and most back office machines (1 Windows 7Pro). I will try the KB961451 mentioned above today and report back. I will load this on all machines. I see this KB article is for Windows Server 2003 too, however I am running Windows Small Business Server 2003, so I assume this KB fix will not work on that software.

Thanks Jeff

Grier

Was this reply helpful? Yes No
Grier Fleischhauer 782 on at

Like (0)

Report

Today I installed the KB Hotfix on all of my XP machines with no good results. I decided to run a detail sales report on the "bad" store and I had another bizarre result. It seems that not only is the HQ upload decrementing incorrectly, but also showing items that were not sold, and on top of that missing department, category descriptions etc. The transaction # in HQ does correspond to the one in Store Operations, but the items in HQ are not the same as in Store Operations.

Was this reply helpful? Yes No
Spencer McCandless 2,087 on at

Like (0)

Report

Grier,

Based on this I'm really leaning toward your store ops reusing transaction numbers that have previously been uploaded to headquarters. Try this: Open headquarters administrator and run

Select * from transactionentry where storeid = {bad store id} and transactionnumber = '{one of the bad transaction numbers}'

Compare the values in the transactiontime field. If more than one date is present, then this is definitely the case. To fix this problem for future transactions, I believe you can run

Select Max(transactionnumber) from transactionentry where storeid = {bad store id}

and then go to Store Operations Administrator and set the next transaction number as one more than the value that was returned. As for fixing your past sales data, that's going to be a bit more involved.

Was this reply helpful? Yes No
Jeff @ Check Point ... 13,382 on at

Like (0)

Report

The hotfix will only work going forward, not fix the existing issues. You need to fix the store,s info and HQ needs a 190 worksheet after a 501 has been received.

yes, you need the hot fix on you sbs machine too.

Was this reply helpful? Yes No
Grier Fleischhauer 782 on at

Like (0)

Report

Okay I think there was two issues going on at the same time. Spencerm's suggestion fixed the detail sales issues because somehow the restored Store Operations database arbitrarily set the transaction number about 150,000 transactions less than the largest transaction number. So I corrected that and now atleast the detail sales in HQ and SO line up perfectly.

I'm still having the screwy decrements of inventory. I installed the XP and SBS hotfixes that Jeff suggested, but today I am still having the same screwy random type changes in inventory (fractional, too many and too less decrements). I am wondering if somehow the HQ database internal number that it assigns to the products and the SO database internal number it assigns are not lining up with each other. Is there a way to verify this? I'm at a loss here. If someone has any other suggestions I'm all ears.

Thank you Jeff and Spencerm for your suggestions.

Grier

Was this reply helpful? Yes No
Spencer McCandless 2,087 on at

Like (0)

Report

As for the internal number in HQ vs Store Ops, the value in Items.ID in the HQ database should match the value in Items.HQID in the Store Ops database. It should be pretty easy to compare, since both databases are on the same machine. You can reference other databases on the same server using using the {databasename}.dbo.{tablename} format. So to test if these values are the same, you could open HQ administrator and run something like

select * from item as HQitem join {store ops databasename}.dbo.item as SOitem on HQitem.itemlookupcode = SOitem.itemlookupcode where HQitem.id <> SOitem.HQID

I don't know that this would be your problem, though, as a mismatch between the two databases would most likely result in discrepancies between the detailed sales reports, which you said are lining up now.

Have you tried running an Item movement report on one of the weirdly decremented items and seeing if it reflects the change thats supposed to occur or the screwy change that is occurring?

Was this reply helpful? Yes No