SBX - Search With Button

SBX - Forum Post Title

Help! Physical Inventory updating numbers with Delta value

Microsoft Dynamics RMS Forum

javhaines asked a question on 8 Jan 2014 6:49 AM
My Badges

Question Status

Verified

I need some serious and quick help! We completed our year-end inventory by inputting items into a blank inventory file. During the process, we ran an additional Physical Inventory, which zeroed out all previous expected numbers using the Physical Inventory tool and calculating the full stock at 0 inventory.

We then completed the physical inventory and closed out each inventory. It was then that I noticed that certain items were still showing an expected value that was greater than 0, specifically items that had been scanned prior to running the 0 inventory.

Now, instead of using the counted values in my inventory, the system has inputted all the Delta values, basically undoing about 15 hours of inventory taking! I want to know if there's any way to, I don't know, force the system to use the counted numbers.

I understand that I've caused a glitch up by not running the 0 inventory before we started everything, but at the same time, shouldn't the system use the Counted numbers by default? Why would it use the Delta amount? And if I don't zero out inventory in the future, will it continue to use the Delta amount?

I would be greatly appreciative of some help here! Thanks!

Reply
Spencer McCandless responded on 8 Jan 2014 12:42 PM
My Badges

No problem, glad I could help. Let me know if you run into anything screwy, and I'll see what I can do.

Reply
SolarboticsLtd responded on 7 Jun 2019 4:53 PM
Suggested Answer

I found that migrating up to SQLExpress12 uses the newer SQL_Latin1... over the older Latin1_General... so anytime a temporary table is built under the MASTER database, it's using the older collation. I had to update the collation of the master database using the tip here. Essentially, you have to stop the SQL server instance, run a command line collation upgrade command, and restart the instance. I tested it on my duplicate VM of the production server, and it solved the error.

Unlike other tables, the master database needs some gentler handling to shift it's collation setting.

Reply
Spencer McCandless responded on 8 Jan 2014 12:24 PM
My Badges
Verified Answer

Ok, well, I was trying to avoid touching the database itself, but it looks like this is turning into more trouble than its worth. Note that this is simply going to overwrite the current quantities with those counted during the inventory. Any movement since the inventory will be in sales history, but will not be accounted for in the new quantities, so anything that has sold or otherwise moved since it was counted will probably need to be recounted. Also, if you are in a headquarters environment, the change will not automatically be uploaded, so they will have to request a full item count and perform a reconciliation at HQ. Backup the database before going any further, then write down the reference number of the PI's that you want to commit. Open up Store Operation administrator, connect to your database, open a new query, and run the following command:

SELECT item.itemlookupcode, item.description, item.quantity AS oldquantity, physicalinventoryentry.quantitycounted AS newquantity FROM physicalinventoryentry JOIN item ON physicalinventoryentry.itemid = item.id JOIN physicalinventory ON physicalinventoryentry.physicalinventoryid = physicalinventory.id WHERE physicalinventory.code = '[the reference code]'

This will list all of the items with their current quantities and the new quantities that they will be replaced with. Review this and make sure that everything is accurate and as it should be. There is no easy undo for this short of restoring a backup, so be careful and be aware.

Once you are satisfied, create a second new query window and run the following command:

UPDATE item SET item.quantity = physicalinventoryentry.quantitycounted FROM physicalinventoryentry JOIN item ON physicalinventoryentry.itemid = item.id JOIN physicalinventory ON physicalinventoryentry.physicalinventoryid = physicalinventory.id WHERE physicalinventory.code = '[the reference code]'

This will pull the trigger on the operation, replacing current quantities with those in the inventory.

Reply
Spencer McCandless responded on 8 Jan 2014 10:21 AM
My Badges
Suggested Answer

Actually, scratch my first suggestions. Lets avoid messing with the database if possible. I have a better idea. This is going to be a little involved, but I think it should fix the issue.

Open Store Ops, go to Physical Inventory, and find the correct inventory that you committed. Click "Print". On the next screen, add a filter of "Qty Adjusted Not Equal 0". This should bring up a list of all items that had item movement from the zeroing inventory applied to them. Drag the column "Item" into the first column, then "Counted to date" into the second column. Right click on the headers and select "Show/Hide Columns".  Uncheck everything except for Item, Counted to Date, and count date. You should now have a list of upcs, counted quantities, and dates, in that order. At the top, click the Export, then select File, and Comma Separated, and click OK. Choose a place to save the file. Create a new, blank physical inventory, and click "Import". Find the file that you just created, and click next. Select comma for field delimiter, enter 2 for start import at line, and enter 1,2, and 3 next to itemlookupcode, Quantity, and Count Date, respectively. Click Next.  Select "Update existing items and add new items". Click Next. It should display a bunch of items to import. Click import. Right click on the headers, select "Show/Hide columns", and place a check next to count date and hit OK. Click the new "Set Dates" button, and enter a time immediately AFTER you committed the CORRECT inventory (not the one you used to zero items out). Click OK. Hit Calculate, and commit the counts as you would with a normal inventory. Your counts should now all be corrected.

So, like I said, a little involved, but let me know if it works out.

Reply
SolarboticsLtd responded on 7 Jun 2019 4:53 PM
Suggested Answer

I found that migrating up to SQLExpress12 uses the newer SQL_Latin1... over the older Latin1_General... so anytime a temporary table is built under the MASTER database, it's using the older collation. I had to update the collation of the master database using the tip here. Essentially, you have to stop the SQL server instance, run a command line collation upgrade command, and restart the instance. I tested it on my duplicate VM of the production server, and it solved the error.

Unlike other tables, the master database needs some gentler handling to shift it's collation setting.

Reply

SBX - Two Col Forum

SBX - Migrated JS