Maintenance of SOP Tables in Dynamics GP (Video)

Find out what happens in SOP to cause orphan records in Dynamics GP, and why we care. Learn about removing these transactions and how that affects inventory allocations.

Transcript below:

– [Katie] Hello, everyone. Thank you for joining us today for our Dynamics GP Coffee Break, Maintenance of SOP Tables. My name is Katie and I’ll be the facilitator today. If you have any questions during the presentation, please type them into the question area in the control panel on the right-hand side of your screen, and we’ll answer questions at the end. The session will be recorded and posted on our blog, as well as shared with you later this week.

Now, I would like to introduce our presenter today, Don McNulty, who is a Solutions Architect at Encore. Don, I’ll pass it over to you.

– [Don] Perfect. Thanks very much. Well, good morning, everyone. So today we’re going to talk a little bit more technically than most of my other coffee breaks and talk more about some maintenance on some SOP tables. So, what are the problems with SOP? So Sales Order Processing or SOP, we have problems with Orphan Records and allocations. And there’s a couple of reasons why this happens, which we’ll go over.

But, basically, what it is is we have header issues and we have detail line issues. The header issues are not a big deal. They just leave transactions that cannot be moved to history and cannot be removed easily using the software. The bigger problem is when you have detail lines. When you have a detail line that you’ve entered that has been orphaned without a header or without a valid header, then what happens is that the allocation occurs for the inventory item for that product.

So that product is now no longer available to sell, which makes it very difficult when you’re trying to maintain your inventory. What causes this? Well, what causes it is the way that GP is designed, is that when we open up sales order processing, we open up a header, which I’ll show in a second, and then it doesn’t store that header until you hit the save button.

However, it does store, it does reserve a record in the database and then it does store the detail lines individually prior to storing the header line separately. Why it’s done that way? I’m not sure. Okay. The correction of the issue. So what we need to do is we need to remove the records for the header and we need to remove the record lines. And what we also need to do is unallocate the items by just removing the records.

So we’re going to be using a little bit of SQL Server Manager and a little bit of GP to show you how this works. So let me pop into my demo. So inside Great Plains, what happens when we go in through here, we go to Sales Transaction Entry.

And I’m just going to grab 2241. So this is just a new… We put it in a batch and we go to here. And at this point here, we can now put in a one ear piece like so.

Now, once I’ve tabbed off of this, so at this point now I have allocated one accessory headset one ear and I have a header record of order st 2241. The problem is that the header has not been saved completely. If something was to happen to my machine, power failure, somebody turns it off, I lose my connection to the internet or whatever it happens to be, that record will be in the database but will not be accessible to the user.

So to see that, what I’m going to quickly do is I’m going to quickly go into my smart list here. I’ve got two that I already set up. This is what they look like. These two orders have been opened but not closed properly. They come up with no customer, no customer name, no currency, no batch, no batch source. Now if I refresh this.

You see that I’ve got three and there’s that order 2241. You’ll see order 2241 looks exactly the same… Looks exactly like this. So but none of the header information about Aaron Fitz is actually attached. When I click over here and I click onto my sales transaction lines, so I’ve also got one written for that.

Let me just… Sales lines. And here we are here. So when I click on this one here you’ll see that 2239 has two lines 12 and 9 and you can see there’s my one headset. You’ll see that it’s been allocated and its quantity is there.

Now, if something was to happen to my machine I was to turn off GP without exiting gracefully, the record would be caught as an orphan record. So what we want to do is we want to be able to remove this. So as I go through here, so if I was to then go into here and go, oh, I don’t want this, when I go Actions, when I close this it says what do I want to do?

Save or delete? If I hit save then this will remove itself from this line because it will save the full document. If I hit delete, it will delete it and unallocate it. So if I hit the delete here. And now if I go back to my refresh here, you’ll see that I’m back down to the two pieces. But I still have these two.

So I don’t have any way of getting these things up. If I tried to bring up order status 2239, which I will do right now, I go into here… Get this, this document has been posted but it’s not posted because it’s sitting in my open file so I know that it’s not posted.

So I kind of go, “Well, that’s weird.” So if I was to go and take a look at my item. So these are the symptoms that you see. So if I go into my inventory item and I go into my item card, and I grab my two ear, which is the item that was on there. When I go to my quantities and sites.

and you’ll see here that I’ve got allocated 24. So when I click on my 24, bring up my allocation list. You’ll see here that although the allocation here says 24, the allocation listed, i.e. the orders that it finds, is only 12. And you’ll notice that there are 12 two-ears sitting here.

So I need to make this come back to balance. That’s the object of today’s exercise. So what we need to do is we have to be able to fix this. So how do we fix that? So what we do is we go back into SQL Server Studio Manager. So when I’m in SQL Server Studio Manager, I have, these will be provided to you, all of the scripting, the script will be provided to you.

This is part of a blog that’s already out there. I had a few questions on it, which is why we’re doing the coffee break today. So what we want to do first is we want to go into here. And we want to, we just want to run this particular piece right here. So we’re just going to execute that. And…

Wrong database. There we are. And you’ll see there what I’ve done is I’ve added sales or type, which tells me whether it’s an invoice or a return. So SOP number, there it is there, which you can see the customer number, the currency ID, the batch source, and the batch number.

Now, when I look at this without…and where I take this and I just look at it like this. There’s a list of all of the sales orders that I have outstanding in my system that are unposted. Okay. These are all unposted either quotes, orders, invoices, returns, or back orders. Now when I add this, when I add where the customer number equals blank, I now get to see all the ones that have been caught 2229, 2240, and 2241.

And 2241 is reserved at this point because I have sales order processing open. So if I go into here 2241 is sitting right here, that’s my reserve record. If I close this and I go back to my SQL here and I execute, I’ll be down to one. It just automatically reduces that one by one.

So you go, well, how do I fix this? So there are two methods of fixing it. If you go on to the Microsoft website, what it tells you to do is it tells you to come into SQL and it tells you to run this script here, which is delete from SOP 100 where the customer number equals blank.

You kick everybody out of GP so that you don’t get what I just had. You saw that 2241. I want to make sure that there’s nobody has one open on their screen. I don’t want to delete a record that they’re working on. So I do this. So I delete this… Once I’ve deleted this.

If I delete that, I must run the check links and a reconcile for the inventory or for the sales order processing in order to remove all the other records in the system. And the other records include the things like the header detail, the detail information, the header, the detail information, as well as the transaction details.

SOP has got like seven or eight different tables that need to be updated. That works well when you’ve got a smaller system. So with a smaller system, it’s not a big problem. And then what we do from there. So then you go, okay, how do I do that? If I’ve got lots of people in SOP at the time, and I just want a single one that needs to be released, I can delete SOP 100 where the SOP number equals this.

And what that will do is that will remove the individual record rather than the orphan ones. Okay. The problem is that it still requires a check link and the check links on the sales order processing file can take hours. Not only do you have to do a reconcile, the check links, but you have to reconcile the inventory to remove the allocation.

And for those of us who have done reconciles on items, if you don’t know what items are on the sales order because you can’t open the sales order to look at it, you go, well, what do I need to do? So you say… If I run a Reconcile, the Reconcile can take literally six to eight hours. And that means that you have to do this on a weekend, and that means that I’m done without that inventory for the week.

So we need a method to be able to do this when somebody says, well, I need to sell this product. So what we do is we do some setting. We update the information inside here and the things that we need to update are the customer number, the currency ID, the batch source, and the batch number. Those are the ones that I’ve got on the select statement.

As you can see down below, they are blank. And what we’re going to do here is we do this for an individual order number right in here. So what we do here is we type in the ORD ST 239, like so. So by doing this, it’s going to populate this, this, this, and this.

And when it does that, the batch called recovery will be… you’ll put this information into the system. So I’m going to run this just for this one order so we can see what happens. and we can just go execute.

Whoops. Sorry about that. I forgot the extra comma there. So it showed us that, but now if I go back and look at this again, and I go execute, you’ll see that 22, because the customer number is no longer zero, I can now go into 2239 and look at the transaction.

So then I go into here, I go back to my sales order processing. And if you remember, we couldn’t see that before. Now when I hit the tab it will open this up.

So you’ll see here it’s now open and you’ll see most importantly here it has opened it with these two attached. So this is the 12 units that we were looking for. We got rid of those… These 12 are now accessible. This is a non-recoverable state. So the only thing we can do here is delete or void. So if I hit the Delete key here, Are you sure I want to delete the record?

Yes, I am. Now when I delete that record, it’s deleted the record completely, but it’s also updated all the other tables instead of having to do a check links and a reconcile. So by doing this, this allows me to easily get rid of the single one. So now when I go back to my item… Oops. And I click on my Go To [inaudible]

and sites, and let me just go forward one and back one, you’ll see that the allocated number has automatically been changed to 12.

And then when I drill down on the allocated number, I get this and I get the 12 that are actually allocated on valid sales orders. So this has solved that problem of having to do a check links and a reconcile, which takes a long time, especially on larger systems. And it allows me to easily get those items out of the way.

So now when I go back to my SQL and I look at this, if I wanted to do 40, I could do that one as well. Now I go back to my smart list here, there’s 2239 and I go refresh. I have none.

This particular smart list is just listing off the batch, batch number, customer currency, item number, description, quantity, etc. And what I’ve done on the search criteria, as I’ve said, where the customer number is equal to blank. So that way we can see what these transactions before we know whether we’ve done anything. So on something like this one here, what I sometimes will do with people is this, we will… to say, well, do I have any?

So we write this smart list. And then what we do is we save this, and we save it and we can add it as a favorite and a reminder. So when I add it as a favorite and a reminder, it will then push it onto the front page of my home page.

Every morning it will check my SOP tables to find out if I’ve got any orphan records. You have to be careful that the orphan records aren’t just people currently doing data entry, because it could be. So if the numbers are all grouped together in the current numbers, then we know that these are order numbers that are currently in use. So usually we look at them and we move back. We don’t get to see a date because there’s no date stored until you hit the save button.

And then by that time, it’s too late for it to be orphaned. So that’s what we can do on site this one. Now, this is on the line items. We’ve got none. But if I go back to here and just go click cancel this. And if I go back to my SOP transactions, I now still have 2240. 2240 has no items on it, but the transaction is there.

Okay. So if I wanted to get rid of 2240, again, I can do exactly the same thing. I can go 2240 here and now I click the Execute. And now when I execute again, I’ll get nothing down below because it’s now been fixed.

And if I go back to my Great Plains and I click on my sale transaction entry. And I go 2240, you’ll see that I’ve got Aaron Fitz. Notice there’s no customer name. This is not a valid record.

Now this one was just a header that got caught, not both. So I can still do exactly the same thing. I can go Delete and it’ll delete. And now that’s perfect. So we’re good. Now the only thing that we’ve got left now is the recovery batch. So the recovery batch itself looks like this.

And you’ll see here that it says minus two. Okay, if I hit Delete, Are you sure you want to delete the batch? We can delete the batch.

Now depending on where it is and how things have recovered themselves and what you’ve done with them, What happens with this is that in a lot of cases you cannot delete this batch. Some documents in this batch could not be deleted. The reason it can’t be deleted is because you see that there’s an actual count here of minus two.

And that’s because we never saved anything to the recovery batch. I created that recovery batch before today, but you would create it right before. So, we need to get rid of this batch, otherwise it’s going to be there forever, because it’s going to keep getting negatives. If I recover into it, it’ll go back up to zero.

So, if I close this and I close this, I can close these two. So then down here, this is what we need to do here. So this, right, is what happens with the batch header itself. So if I go into here, and I should actually add one in here, It’s a table called SY00500.

Oops. And I’m just going to select everything there. It’s not that big of a table. And…Like so. And if I select this, I can go execute.

There’s my recovery batch that I’ve been working on. So here’s the sales entry. There’s the recovery batch and if I go across to here you’ll see that I’ve got transactions and you’ll notice that the number of transactions here is minus two. So what we want to do is we want to remove this record which will remove the batch.

Now we can either remove the batch or we can change all the things so that it makes it accessible. I find the easiest thing to do is to delete the batch. This command here, the delete like this, will delete this batch. So all I have to do is I have to highlight this.

I don’t want to run everything and you go execute. And it’s affected one row. And now when I go back to here, can I execute this? You’ll see that I’ve got nothing left. So now when I go into my Great Plains, and I go into Sales Batches, and I do a look up for sales batches, you will find that recovery is gone.

All the other ones are still there, no problem, but I have gotten rid of that one batch. That’s the only piece of cleanup that you need to do after the fact. It does take the administrators a little bit longer to do it this way, but it is significantly faster if you’ve got large amounts of data. Now, one of the things I’ll just quickly mention while I’m here is the way that I’ve written this particular script here.

You’ll notice that all my delete queries that I say you can run, I put behind comments, and that’s so that if I hit the button by mistake, I don’t delete things when I don’t want to, and that I’m forcing myself to either remove the two dashes at the front for the comment or highlight the actual text of the comment inside. So there it is there.

Okay. So that was really all we had to cover this morning. I wanted to just make sure that we’ve got an idea of what’s going on inside here. Again, if you look online, there’s a check link. There is a blog on this as well that shows both. If you go to the Microsoft site, they suggest that you delete the record using SQL Server, using the delete from SOP where one, when everybody’s out of Great Plains, we do this one here, right here, and we follow it with the check links and reconcile must be done after the step to remove and recalculate related table information.

And the most important thing there is to get the allocations back to the way they’re supposed to be, which is without anything embedded. All right, so, do we have any questions?

– I don’t see any questions coming in, Don. If you do have any questions, though, please put them into the question panel on the right-hand side of your screen.

– So just kind of a thing, so if you’re interested, we’ve got some more, got a couple more coming up. We’ve got, What’s New in Dynamics GP.X (4, 5, and 6.) Version 18.6 was released in October and the tax tables for Canadian payroll and the tax tables for the US will be coming out in December this month, the next couple of weeks.

So we’ll be talking about what’s new in 18.X from that point of view. Some changes…and then in March we’ll be doing how to change your chart of accounts, your vendors, your customers and items in GP using the professional service tools and some built-in functionality inside there so that you can clean up some of that. And then, April, we’re going to do some time-saving tips, keystrokes, macros, and things like that.

And then on May, we’re going to be doing setting up ACH and or EFT in Great Plains depending on whether you’re from Canada, the United States. U.S. tends to use ACHs and Canada tends to use EFTs. So we hope to see you then.

And if there’s no questions, I think that’s all we’ve got for today.

– Thanks, Don. So as Don just mentioned, we do have a few upcoming coffee breaks scheduled for the new year. I’ll add the link to our events page in the chat if you’d like to register for those sessions. I will also add the link where you can set your email preferences and opt into our monthly and quarterly emails so you can stay in the know about upcoming events and see our latest blog articles.

All right. Thank you, Don, for the information, and thank you everyone for joining us today. Have a good rest of your day.

– Thank you. Have a nice day everyone.


What Is New in Dynamics GP?

Get 8 premium pieces of content that will help you plan a Dynamics GP upgrade!

Get the Upgrade Guide

What Is New in Dynamics GP?

Get the Upgrade Guide