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 9:58 AM
My Badges

Hey Jav,

When an item is entered into the physical inventory, it has a count date and time attached to it (you can right click the headers and go to show/hide columns to display this value). When you click calculate, RMS takes the entered quantity and applies any item movement that has occurred since the attached date in order to determine the new quantity. I suspect what happened is that when you performed a second physical inventory to zero out your values, RMS considered it to be item movement, and so it is applying the decrease in quantity to the entered values. I Luckily, it sounds like you performed the whole inventory in one swoop, so it should be easy enough to roll back these changes. I think the best way to do this is probably going to be a sql update statement in Store Ops administrator. Let me think about what the most thorough and comprehensive method would be, and I'll update you in a second.

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
javhaines responded on 8 Jan 2014 10:28 AM
My Badges

First, let me thank you for responding with an offer of help. I am in dire straits here!

I have actually tried some things since I posted the above:

-I rolled back the RMS program using Monday's backup, in order to re-open the inventory files (we had 4 in total)

-on the Physical Inventory screen, I right-clicked to allow me to Show/Hide Columns. I selected Count Date, which gave me the option to change the count date. I changed it to after the zero inventory change. 3 of the 4 inventories now work and update with the counted amount. There were a handful of items that still had an expected quantity, but they updated the stock with the counted amount anyway, not the Delta.

-unfortunately, the final PI, containing over 7000 SKUs will not oblige me in this manner. It is still using the Delta as the new stock number.

-I have also tried copying that PI and committing it, with all 0s for the count. The Calculation on that one showed nothing in the expected qty column. However, nothing changed with the actual count after this adjustment

One added potential complication is that we uploaded some stock into this file (and the others) from a handheld scanner. I have no way to recover that data once it has been uploaded, unfortunately.

I look forward to your response! Thanks!

Reply
Spencer McCandless responded on 8 Jan 2014 10:41 AM
My Badges

Hm. Ok, well, that's progress. Let's try this. On the final PI, open it, calculate it, and right click the headers and click Show/Hide columns. Place checks next to Sold, Returned, Xfer In, Xfer Out, To Offline, From Offline, and Adjusted, and click OK. This should represent all of the Item Movement that RMS is using to calculate current quantities. Find where the values that are not 0 are, because these are what are causing RMS to update with quantities differing from those counted. Let me know what you find here, and we should be able to address the problem based on this.

Reply
javhaines responded on 8 Jan 2014 10:47 AM
My Badges

So, it got about halfway through the import and spat out this error:

"Error #-2147217900

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AI" in the equal to operation

(Source: Microsoft OLE DB Provider for SQL Server)

(SQL State: 42000)

(NativeError: 468)

No Help file available

UPDATE[##ST_Validation_1]

SET Import Status = 3

, ItemID = Item.ID

FROM Item INNER JOIN [##ST_Validation_1] ON Item.ItemLookupCode = [##ST_Validation_1] .ItemLookupCode"

This was followed by Runtime error '5', and the whole SO Manager shutting down.

Reply
javhaines responded on 8 Jan 2014 11:04 AM
My Badges

The only column with anything other than 0 is the Adjust column which has a variety of numbers. So, for example, we counted an item at 1 copy, the Adjust column says -4, the Expected Qty says 4, and the Delta is -3.

Reply
Spencer McCandless responded on 8 Jan 2014 11:11 AM
My Badges

Ok, so it is still detecting some movement from a physical inventory being committed. Is the PI still open at this point, or has it been committed? If it's open, we can just find a date after the movement by going through the item movement history and enter that as the count date. If it's closed, I think I'm ready to just say screw it and brute force it with a sql statement.

Reply
javhaines responded on 8 Jan 2014 11:12 AM
My Badges

It's open! Should I just pick an item and run a Movement History report?

Reply
Spencer McCandless responded on 8 Jan 2014 11:18 AM
My Badges

Yep. Unfortunately, the reports like to truncate the dates so that you can't see the time, but there should be a physical inventory adjustment at some point after the current count date you have entered. If you can change your count day/time to a time after this, then the quantities should commit correctly.

Reply
Spencer McCandless responded on 8 Jan 2014 11:19 AM
My Badges

Oh, if you right click the date in the item movement history report and copy the value, then paste it into a text document, it should show you the time that isn't being displayed.

Reply
javhaines responded on 8 Jan 2014 11:21 AM
My Badges

On the Movement History report, it shows one of the items as having a physical inventory adjustment of -4 copies on January 6th, 2014.

I have gone into the PI and changed the count date to January 7th and then Calculated again. It shows the count date as 1/7/14, but is still showing the same numbers in the Adjust column.

Reply
javhaines responded on 8 Jan 2014 11:31 AM
My Badges

Now, I have just discovered that rolling the database back to where it currently is (ie. the day before we closed the inventory files) actually removes everything uploaded from the handheld. So.... what can I do if the inventory files are already closed?

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 12:27 PM
My Badges

Oh, replace [the reference code] with the code from the PI you want to commit. No brackets, but leave the single quotes.

Reply
javhaines responded on 8 Jan 2014 12:36 PM
My Badges

You are a lifesaver!! Seriously!!! That completely fixed everything and we're back up and running. I cannot even tell you how relieved and grateful I am for your assistance. Thank you SO much for taking the time to work with me on finding a solution to my problem. I promise I will not make the same mistakes again!

You are the best!

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