Commonly Used Tables:
- SOP10100 – Unposted/Work Transactions (header) – one record per unposted sales transaction
- SOP10200 – Unposted/Work Transactions (line detail) – one record per line item on unposted sales transactions (for kits, there will be one record per kit component)
- SOP10101 – Commissions Work and History
- SOP10102 – GL Distributions Work and History – GL distributions for posted and unposted Invoices, Returns and Fulfillment Orders
- SOP10103 – Payment Work and History – one record per payment entered on sales transaction
- SOP10104 – Process Holds Work and History
- SOP10105 – Sales Taxes Work and History
- SOP10106 – User Defined Work and History – this table will also have the transaction level comment
- SOP10107 – Tracking Numbers Work and History
- SOP10201 – Serial/Lot Work and History
- SOP10202 – Line Comment Work and History
- SOP30200 – Historical Transactions (header) – one record per posted sales transaction
- SOP30300 – Historical Transactions (line detail) – one record per line item on posted sales transactions (for kits, there will be one record per kit component)
- SOP60100 – SOP-POP Link – one record for each link to a PO line, unique link on: SOPNUMBE, SOPTYPE, LNITMSEQ
- SOP60300 – Customer Item Numbers
SOPTYPE (SOP Document Types):
1 – Quote
2 – Order
3 – Invoice
4 – Return
5 – Back Order
6 – Fulfillment Order
Distribution Types:
1 – SALES
2 – RECV
3 – CASH
4 – TAKEN
5 – AVAIL
6 – TRADE
7 – FREIGHT
8 – MISC
9 – TAXES
10 – MARK
11 – COMMEXP
12 – COMMPAY
13 – OTHER
14 – COGS
15 – INV
16 – RETURN
17 – IN USE
18 – IN SERVICE
19 – DAMAGED
20 – UNIT
21 – DEPOSITS
22 – ROUND
23 – REBATE
PYMTTYPE (Payment Type) in SOP10103:
1 – Cash Deposit
2 – Check Deposit
3 – Credit Card Deposit
4 – Cash Payment
5 – Check Payment
6 – Credit Card Payment
[Note: The PYMTTYPE values in the GP SDK are not correct, at least for the recent versions of GP – thank you to Beth Lott for bringing that to my attention. I have confirmed the values above with GP Support.]
Decimal Places (for both Quantity and Currency):
1 – 0
2 – 1
3 – 2
4 – 3
5 – 4
6 – 5
Transaction Frequency:
1 – Weekly
2 – Biweekly
3 – Semimonthly
4 – Monthly
5 – Bimonthly
6 – Quarterly
7 – Miscellaneous
PURCHSTAT (Purchasing Status):
1 – None (will always be this for Quote, Return and Invoice)
2 – Needs Purchase
3 – Purchased
4 – Partially Received
5 – Fully Received
QTYTYPE (Quantity Type):
1 – On Hand
2 – Returned
3 – In Use
4 – In Service
5 – Damaged
PROSPECT:
0 – Existing Customer
1 – Prospect
COMAPPTO (Commission Applied To):
0 – Sales
1 – Invoice Total
VOIDSTTS (Void Status):
0 – Not Voided
1 – Voided
DROPSHIP (Drop Ship):
0 – No Drop Ship
1 – Drop Ship
NONINVEN (Non-Inventory):
0 – Inventory item
1 – Non-inventory item
SOPSTATUS (SOP Status):
1 – New (Order)
2 – Ready to Print Pick Ticket (Fulfillment Order)
3 – Unconfirmed Pick (Fulfillment Order)
4 – Ready to Print Pack Slip (Fulfillment Order)
5 – Unconfirmed Pack (Fulfillment Order)
6 – Shipped (Fulfillment Order)
7 – Ready to Post (Invoice)
8 – In Process (Order)
9 – Complete (Order)
DELETE1 (Delete) in SOP10104:
0 – Not deleted
1 – Hold has been removed
PSTGSTUS (Posting Status):
0 – Not posted
2 – Posted
3 – Error
7 – checking for Duplicate Document numbers
12 – verifying accuracy of amounts between detail & header
14 – verifying total detail amounts match header amount
508 – transferring record from SOP10100 to SOP30200
600 – validating detail & header records match in history tables
Notes on reporting in SOP:
- Remember to filter out voided transactions.
- When linking SOP tables, always link on the SOPNUMBE and SOPTYPE. While it doesn’t happen too often in real life, the system will allow you to have the same SOP number for different SOP types.
- On Invoices and Returns, QUANTITY is what was billed/credited, however, if you want to see the quantity in the ‘Base’ Unit of Measure (what the Inventory module keeps the quantities in), you will need to multiply the QUANTITY by the QTYBSUOM (Quantity in Base U of M).
- Returns will show up as positive numbers – you’ll need to change the sign if you are adding all the transactions together.
- Try not to use DEXROWID for any reporting logic, as there are times where these get repopulated, for example when you upgrade to a new version.
—
Last Updated: November 06, 2016
Hi Victoria
What are the repercussions of updating SAP30200 DOCDATE and GLPOSTDT?
LikeLike
Hi Albert,
I assume you mean SOP30200. Those dates are tied to MANY other tables throughout the GP database. I would strongly recommend not doing this. If you feel that you must do it, I would argue that all related tables should be updated, as well. And this should be after a very careful study/testing of the implications with a consultant that is knowledgeable not only in Dynamics GP and how it stores data but also in your company operations and reporting; and with management signoff that you are potentially compromising the integrity of your accounting data.
-Victoria
LikeLike
Hi Albert, I too have run into a situation where I was questioning updating the GLPOSTDT. Can you tell me why you need yours changed?
LikeLike
HI Victoria…working with the SOP10100 open orders table. Trying to pull all open orders YTD thru August via SQL. However, numbers aren’t matching up to what I pull in Great Plains. I think I’m missing some sort of date filter, but any help would be appreciated. Thanks for the great resources!!
LikeLike
Hi Andy, how/where are you pulling these in GP? Do you have a SQL query you are using? Can you post it here? -Victoria
LikeLike
My guess would be one of two things without seeing the SQL.
Inclusion of voids in one data set and not the other.
If you are pulling from SQL I believe all amounts are positive in the SOP table so you gave to look at doctype.
LikeLike
Hello Victoria, great resource that I have used for years. Just a quick question for you. In your experience how is the performance posting large volumes of SOP Sales Invoices? We just picked up a client who has batches of about 20,000 invoices with 1 or 2 line items. All Service items. The invoices seem to be posting at a rate of 1200 per hour. Seems very slow to me and they always post over night. Any normal numbers that you are aware of?
Thanks!
LikeLike
Hi Mike,
I’ve not done any specific testing on numbers this large, but 1200 an hour seems incredibly slow to me, especially if they are doing this overnight, where presumably traffic on the network and the SQL server is lower. Impossible to say if the culprit is the large batch size or other stuff going on without a lot more information. There could be a lot of other contributors, including customizations, 3rd party add-ins, server configuration, etc.
That said, in general, my experience is that very large batches in GP are not ideal both for speed and troubleshooting. I imagine with that kind of volume, they are being imported. It’s typically a fairly straightforward thing to change the import to bring this into 20 batches of 1,000 invoices instead of one batch of 20,000. Maybe even 40 batches of 500 invoices. I would expect that to be better performance-wise (or at the very least not worse) and would definitely start out trying that. I also imagine that breaking it up into smaller batches would make troubleshooting of any posting issues a lot easier.
Would love to hear how it turns out if you make changes. In the meantime, I will ask some of my clients that I know are posting large numbers of SOP invoices how long their posting usually takes.
-Victoria
LikeLike
Thanks for the quick reply! So we split the transactions up to 500 invoices per batch. This increased our performance 3x. We are now posting about 3500 invoices an hour up from 1200. We are going to give 250 per batch a shot tomorrow and see how that goes. We are also going to investigate the network connectivity to see if we can find anything there. I will report back.
LikeLike
Hi Mike,
That’s great, thanks so much for the update. Curious to see if the 250 vs 500 makes a difference. My gut feeling is that it probably won’t be much if at all. Certainly not as much as 3x.
The other things I would look at are settings on both the SQL server and whatever computers are doing the posting. Things like page file size (that most companies never change from the defaults) and available resources would probably be first.
-Victoria
LikeLike
THe DBA looked at SQL server and we are good there. I was running directly on the APP Server but I suspect they have some network issues. We will see. I will let you know what else we find.
LikeLike
HI, i just have a question, Can oneself create a custom GL distribution on an order transaction (SOPTYPE=2) and expect that the same GL distribution gets inherited when order is fullfilled and transferred to invoice?
I guess i should start with basic questions, Is GL Sop distribution generated at order level or at invoice?
Regards
LikeLike
Hi Byron,
While there are some things that can be preset at the time of the order, I believe the SOP distributions get calculated and created at invoice time. So if you are looking to add additional distributions, it would have to be to the invoice, I think. There are also some calculations and distributions that are updated and added at the time of posting. So…I think the answer will really depend on the specific workflow for your transactions and what you’re trying to achieve.
-Victoria
LikeLike
Here’s a bizarre issue. When entering an invoice thru Sales Transaction Entry, we’re seeing an issue defaulting the RECV distribution for multi-line transactions. The customer has default accounts set. The items have default accounts set. It doesn’t matter which items we use, the first line will pick up the default for RECV, then the second line item will not. Again, this is regardless of item number selection, so even if I swap item numbers between lines, the first one entered always works, and the second doesn’t. This doesn’t happen in our test company which was last refreshed from production in October. I’ve compared everything I can think of to find what has changed in prod that is causing this behavior. I’ve looked at item setup, item class setup, customer setup, customer class setup, sales order processing setup, document type setup and everything appears to match between test and prod. This all happens using the SA account as well, so permission issues are out. When it happens in prod, using the Default button on the Distributions window will fix it, which is even stranger. The items I’m using should use same RECV account and the Default button recognizes this, but for some reason when entering it doesn’t do it automatically. The 2 items are of type “Services”. Thoughts?
LikeLiked by 1 person
Hi J.J.,
What you’re describing makes me feel like there is a customization of modification of some sort causing it. The RECV account should be coming in from the customer, regardless of items. And if it’s missing on the customer, it would come from the company posting accounts. So if it’s coming in for one item, it should come in for all of them because it’s customer specific, not item specific. And the item type should not matter either. If you have something set up where the RECV account is coming from items, then that’s not out-of-the-box GP.
-Victoria
LikeLike
Believe me, I know it sounds impossible. Everything defaults fine when I only enter 1 item per transaction. There is something about the second line (regardless of item number). We do not have any customizations. The customer has AR account default set. We do not have AR account setup in the Setup –> Posting Accounts for AR because it varies by customer. This is also true in the test company where everything is working fine. The test and production companies share same DYNAMICS DB. I know it sounds crazy, but this truly is the behavior I’m seeing.
LikeLike
J.J.,
As I mentioned, I have never seen out-of-the-box GP do this. There has to be something else preventing the regular GP process from working properly. Next steps to troubleshooting this would probably be either creating a DEXSQL.Log or running SQL Profiler or both to try to track the details of what is being called when you’r entering the second line item. Both of those produce painfully lengthy logs, but they will hopefully offer more detail as to what is going on.
-Victoria
LikeLike
Thanks. I’m deep into DEXSQL.log analysis. I believe I found the call causing the issue, but I can’t explain why it’s being made. The first line doesn’t call zDP_SY01100SS_1 to check for default system level AR account before entering the record into SOP10102. It knows to take it from customer. However, for the second line zDP_SY01100SS_1 is called and then the ACTINDX is overwritten with 0 because we don’t set default system posting AR account. Because this anomaly doesn’t happen in the test company. I’ll next be comparing the logs from performing the same exact actions in each to see where things deviate and if I can determine why. Just thought I’d share the update.
LikeLike
FYI – customization it is! SPS Commerce to be exact. I need to work with them to figure why and why it doesn’t happen in test which has seemingly the same settings.
LikeLike
J.J.,
Glad you were able to track it down!
-Victoria
LikeLike
We have an issue where the TIMESPRT field is indicating an order has been printed when users are saying it has not, Normally I would chalk it up to user error but we have identified 50 such orders in the database for the last 3 days. it is unlikely that a very small team would forget printing on order (either to the printer or the screen 50 times).
Are you aware of anything else that might cause that counter to increment?
LikeLike
Hi Tom,
If you don’t have anything custom going on and if those orders are all in the same batch then it’s possible someone printed the whole batch, so it’s really only someone doing something once instead of 50 times. I’ve not heard of issues with that field being incorrectly updated, so I do not think it’s a known or common issue.
If you have anything custom going on, then it could be just about anything…for example if those orders are being imported from somewhere else maybe the import is updating that field for some reason. Impossible to say for sure without knowing a lot more about your environment and workflow.
If this is an ongoing issue, you could possibly add a trigger to the table or monitor it some other way to see what/when is updating that field to track down the cause.
-Victoria
LikeLike
Thank you for responding so quick. These are orders coming over from CRM (and have been doing so for years) using scribe. When the order is printed by shipping and the times printed is updated to 1 Scribe fires and updates the CRM order to processed by shipping. The orders in CRM have been updated. If the orders came over with a times printed of 1 I would not think that scribe would update that field in CRM because nothing has changed in that table/ field to fire the code. My guess si something is happening in GP but i cannot guess what.
All the orders coming into GP are in one batch so if someone by mistake did print the batch to the screen that would do it but unless that was prat of their daily routine that they just started doing lately that would not explain it happening day after day. possible though. I will go into SQL and run a query to see if all orders are flagged as printed though new orders would have appeared during the night.
I will be able to take a better look on the weekend as orders still come in to GP but the shipping department doe not work.
LikeLike
In looking at the smartlist used to identify orders that need to be processed we are looking for orders with a blank PICTICNU. I will need to figure out when that field populates and why the process has changed now (as it has been working for 5 plus years). when it populates it is populating the order number.
LikeLike
Hi Victoria,
Thanks for this amazing blog post!
I have an integration where I import orders from an external system using direct queries (in SOP10100 and SOP10200). Everything seems to be working properly, the only issue I can see is when I try to change item price in the order, subtotal and total are calculated now without shipping amount. If I try just to save order immediately after it is imported via query without any changes and then try to update item prices, prices are re-calculated correctly. Maybe I’m missing something in my query ?
Thanks!
LikeLike
Hi John,
I am not sure it’s possible to answer this question without seeing your data and seeing what you’re doing with the updates. If you have a knowledgeable GP partner, I would recommend asking them for help on this.
For what it’s worth, in general, I strongly advise against importing transactional data directly into tables. There are a lot of dependencies on other data and a lot of business logic used when entering SOP transactions in the Dynamics GP user interface. Bypassing all of that by putting the data directly into SQL compromises your data integrity.
-Victoria
LikeLike
Victoria, I am trying to change the price level for items on a sales order in the GP interface. I can change the price level for each item and see that it has changed in SOP10200. But I cannot find where in GP to change the price level in the header (SOP10100). Do you know where I can change this through the GP interface?
LikeLike
Hi Sam,
On the Sales Transaction Entry window, bring up a transaction and click the little blue arrow to the right of the Customer ID – that will open the Sales Customer Detail Entry window. The Price Level is on the right side – that’s what is in SOP10100.
-Victoria
LikeLike
Is it Safe to delete duplicate orders (Reimported by accident via SmartConnect) directly from the SOP10100 and SOP10200 tables?
LikeLike
Hi Dan,
The short answer is no.
The somewhat longer answer is that it may be ok in very special circumstances, where it has no impact on other modules (like inventory, bank rec, etc.) and no data in any other tables. However, without examining your data and GP setup in detail this is impossible to know.
Usually imported transactional data is imported into a unique batch. If there are no other transactions in that batch, you can just delete the whole batch in the GP application – that should delete everything in it all at once and should be pretty fast. And if it has both the original and duplicate imported orders, if you delete the whole batch, you can then re-import the orders again. Might be a lot faster than deleting them individually.
-Victoria
LikeLike
Hi Victoria!
I have a return on sop10100 and I would like to update the follows columns: TAXSCHID, TAXAMNT, BACHNUMB, DOCAMNT I use the sp: taSopHdrIvcInsert (with UpdateExisting = 1) after run it I can see the new values on TAXSCHID and BACHNUMB but not in the tax amont and document amount, I would like to know if I miss something or it is not the correct sp to do that.
thank you in advance.
LikeLike
Hi Kenny,
This is not the kind of change that should be made directly in SQL. There are other fields in the SOP10100 table and, more importantly, there are many other tables that may be impacted by this change. How they are impacted is determined by business logic that is run when these changes are made in the GP application.
If you have to do thousands of these updates, and it’s not feasible to do manually, then you may want to get a GP consultant or Microsoft to work with on you this to determine the correct logic. This will involve a lot of testing and data validation and will not be a trivial process.
-Victoria
LikeLike
Hi Victoria,
I hope you are doing well.
I have a situation where I need to void the payment fully applied to a posted SOP Invoice. Now since I ran the remove paid transaction removal utility in Sales module, both Invoice and payment were moved to HIST from OPEN status. Now when I go and try to void the payment so I can apply the Invoice to another payment, the payment does not show up in Apply Sales Document window (I assume because the payment is sitting in HIST table).
I would really appreciate if you could help me.
Looking forward to hearing from you.
LikeLike
Hi adibaig,
If the payment is already in History, you can use the RM Unapply tool in the Professional Services Tools Library (PSTL) to unapply it. That will move it to an Open status and then you will be able to void it. If you do not already have the PSTL or are not familiar with it, your GP Partner can help you with it.
-Victoria
LikeLike
Hi Victoria,
Thank you for getting back to me.
The company I work in uses Dynamics GP 9.0 version. Would PSTL be compatible with version 9.0?
If not, then would there be any other way for my query?
Your feedback is greatly appreciated
LikeLike
Hi Adnan,
I believe back then PSTL had to be purchased (now it is free). So if your company owns the RM Unapply Tool or the entire PSTL, then yes, you could use it – the PSTL is specific to your version of GP, so you would need to find the correct media. Perhaps your GP Partner can help you with this. If you do not own it, at this point I do not believe there is still a way to purchase it for GP 9.0. In that case you may need to deal with the situation in a different way, maybe with debits, credits, etc. But there is no way to unapply or void a GP Receivables transaction once it is in history without that RM Unapply Tool.
-Victoria
LikeLike
Victoria – you are such a wealth of useful information. Thanks so much. I have run into an issue with the SOP30300 . In a SQL Query, how do I exclude the item rows on a kit SO? I get the kit item which has the sales rev and cogs. I also get the kit components, which have zero revenue but also have cogs, resulting in a double counting of the cogs for kits. Any thoughts? Thanks in advance.
LikeLike
Hi Harry,
You can differentiate between the kit items and the components by using the component sequence (CMPNTSEQ) column in SOP30300. For the kit item itself the CMPNTSEQ will be 0. The component items will have a non-zero value in CMPNTSEQ.
Hope that helps.
-Victoria
LikeLike
Victoria – thanks. That is really helpful. Harry
LikeLike
Victoria,
I am trying specify what GL Account should pay the ‘Trade Discount’ field
for each sales order. From what I understand, the Account number normally
comes from the Customer Level (Customer card => Accounts), but for some orders I may want an account number different from the one stored there. I need to set this field through the database or econnect, not using the gp ui. I invisioned
there being an entry for ‘TRADE’ in the sales order’s distribution list
with my entered account number, but I can’t figure out where the data
for that distribution list is stored in the GP database. Can you
provide any advice on how to accomplish this? Or if I am misunderstanding
how this process works, provide any clarification?
LikeLike
Hi Sam,
It sounds like this is for SOP transactions? (If it’s not, please let me know.) If SOP, then the GL distributions are stored in table SOP10102. Link to it from the transaction header (SOP10100) by SOPTYPE and SOPNUMBE in both tables.
In SOP10102, the TRADE distribution will have a value of 6 in the DISTTYPE field. The account index will be in the ACTINDX field. You can link the index to the actual account number in table GL00105 – link on the ACTINDX field in both tables. The full account number is the ACTNUMST in GL00105.
Hope that helps.
-Victoria
LikeLike
Hello Victoria,
I was searching through your excellent blog and couldn’t find this and was wondering if you could confirm something I’ve seen through working some integrations on GP2016: In SOP10200, the SOPNUMBE on a SO Line will get updated with the Invoice number during posting, correct? When does this record get deleted or “disappear” when the posting happens? Having an odd issue here and there where we cannot find the associated SO for a SO Line. Troubleshooting revealed the SOPNUMBE in SO10200 was updated with the Invoice#. It seems like it’s a “timing” issue related to when the GP tables are updated (And with what) during posting.
Thanks.
LikeLike
Hi Shawn,
Once assigned in GP the SOPNUMBE should NEVER change. If that’s happening, then something is going wrong. If this is caused by an “integration” then I suggest examining what it’s doing and why.
-Victoria
LikeLike
Hello Victoria,
GP froze in the middle of transferring orders to invoices on a Remote Desktop Session to a terminal server. We restarted the remote desktop session and went back into GP. It seemed to create all the invoices, and we can see the invoices by going in through the print sales documents window. But the invoices should be in the sales batch “SHIPPED” and we can’t access them through the Sales Batches window. Also if we try and look at an invoice through the Sales Transaction Entry window it tells us “This document has been posted.” We have not done any posting of these transactions.
We ran checklinks last night on most of the choices in the sales module and that didn’t seem to correct the issue.
In SOP10100 the Invoices show up with a SOPStatus=0, PSTGSTUS=0 and a GLPOSTDT=3/18/2019 (the day we ran the transfer). I notice that everything else in the SOP10100 table shows a GLPOSTDT=1/1/1900.
By using the Print Sales Documents window we were able to print out, email all these invoices. They also showed up in our EDI export window so they were exported for EDI. How can we now get them to a state where we can actually post these records? Can we set GLPOSTDT=1/1/1900 for these records?
Thanks for your work on this great site with lots of helpful detail. I’m new to Dynamics GP and find getting a real feel for the flow of information through the system a challenge.
Thanks again,
Matt
LikeLike
Hi Matt,
This is likely a lot more complicated than simply updating one field. And I can’t see how changing the GLPOSTDT would make a difference, that should not be 1/1/1900 for invoices anyway, only for orders.
There could also be other implications in many other tables depending on your specific situation and usage of GP. If you actually find someone to give you advice about this online – don’t take it! I would recommend getting on a remote session with either your GP Partner or Microsoft support and having them look at your data and help you with this.
-Victoria
LikeLike
An update, after running the check links we then transferred orders to invoices. It took a while longer than usual but it fixed our problem. After running that the invoices from the day before could be opened in the Sales Transaction Entry window. Then when we posted the transactions in the SHIPPED batch it did the transactions from the day before and all the new transactions.
I’m not sure if just rerunning the transfer orders to invoices is what fixed the problem or if first we had to run checklinks.
LikeLike
That is great, thanks for the update!
-Victoria
LikeLike
I am seeing a hold in the table SOP10104 with PRCHLDID = NOCOMPHOLD. Does anyone know what causes this specific hold?
LikeLike
Sam,
ALL holds in SOP are custom. So there is no GP process that would create or apply a particular HOLD ID. You would need to examine your system to determine the answer to this.
-Victoria
LikeLike
Holds can be automatically setup to be assigned in the Sales Process Holds Assignment window. Tools > Setup > Sales > Sales Order Processing. Click the Sales Document Setup button the select the Order ID. From that window click the Holds button and it will show you any holds that are automatically applied to that document ID when a new transaction is created.
Hope this helps.
LikeLike
Hi Jordan,
Unless you are entering Deposits directly against orders in GP, there is no way to link a payment to an order. So…the short answer is it depends. There might also be a situation where one order becomes multiple invoices in your system, then each invoice could potentially be paid individually. I would recommend discussing this in more detail with your Dynamics GP partner or whoever is doing the data integrations for you to get the best answer possible that is specific to your company.
-Victoria
LikeLike
Hi Victoria,
We are using Smart Connect to integrate sop invoices. We are getting an error from one invoice. Cannot insert the value NULL into column ‘ACTINDX’, table ‘dbo.SOP10102’; column does not allow nulls. UPDATE fails. The statement has been terminated. – Generic error.
Other similar invoices integrated without errors. All the accounts exist and are active. Any idea what might be wrong?
LikeLike
I found the problem. There was hidden custom code that was changing the account.
LikeLiked by 1 person
Hi Victoria,
Is it possible to use SQL to update part numbers in unposted sales orders?
We have a few hundred sales orders, that have not been posted, with a stock Part Number that we need to change. This new part number is a non-stock item, but if it makes it easier we can always add it to stock. As of now, the clearest way to do this is create a macro that changes the part number for a single sales order. However, that still requires someone going in and changing sales orders one by one.
If I wanted to run an update query in the database to change the part numbers, what GP tables would I need to alter in order to make sure the database does not get corrupted? Is there a stored procedure that I would be able to run to help me with this task?
Thanks for the help!
LikeLike
Aaron,
There are a LOT of tables that may need to be updated, and not just with the item number, but with other details. And that’s just in out-of-the-box GP, most companies have other products and customizations linked to SOP.
If this was something you needed all the time, you can come up with a process, test it, etc. to make sure all your data updated properly. However, for a one time project, you’re going to spend more time doing this than updating each order manually.
If the item number itself is what really needs to be changed and the item is not on any other transactions (ie, the item number is ABC, but it should be XYZ), or it should be changed globally everywhere in GP, consider the PSTL Item Modifier tool – this will change the item number in all GP tables all at once. However, I am not sure if this is what your situation is.
-Victoria
LikeLike
Victoria,
This may be a simple question or even a silly one but my organization rarely uses the actual forms in GP to create SOP transactions. Here is the code we use from our old Transportation Management System (TMS):
GPService.ReceivablesInvoice inv = new GPService.ReceivablesInvoice();
inv.Key = key;
inv.Date = Convert.ToDateTime(lb_cus_date_time.ToShortDateString());
inv.BatchKey = batKey;
inv.CustomerKey = cst.Key;
inv.DocumentAmount = amt;
inv.Description = desc;
inv.SalesAmount = amt;
inv.Distributions = dist;
gp.CreateReceivablesInvoice(inv, context, customerPolicy);
GPService.ReceivablesInvoice invD = gp.GetReceivablesInvoiceByKey(key, context);
gpKey = invD.Key.Id;
We have no idea which form we should use; Invoice Entry or Sales order processing in order to manually input our invoices to create the proper receivables entries. We are in the process of changing the TMS ofor one of our operations and in the interim need to manually input a few entries in order to process our Dec-17 receivables.
Thanks,
Dan
LikeLike
Hi Dan,
It looks like you are actually importing Receivables Transactions, which is neither Sales Order Processing nor Invoicing. Receivables Transactions can be found by navigating to Transactions | Sales | Transaction Entry. Note that this is NOT the same as the “Sales Transaction Entry” option at the top of that menu, it is usually about 1/3 of the way down on that menu and the window will be called Receivables Transaction Entry once opened.
Hope that helps,
Victoria
LikeLike
Hi Victoria,
We have some data duplication in the SOP tables for Sales Order Invoices that ARE posted to the sub ledger, but the work tables still have the documents in them.
If we verify that the sub ledger has the correct amount for each posted document that is in both places, is it safe to delete the work table records from SOP10100 and SOP10200?
My understanding is that checklinks/reconcile will not resolve this issue. Leaving me no option other than to surgically remove them with SQL.
Thank you for your help!
-Gail
LikeLike
Hi Gail,
In this situation, it seems like the only option is deleting from the SOP10100 and SOP10200 tables. That said, I am always very leery of telling someone to delete data from SQL without having seen it myself. If you are not 100% comfortable or if there is any question/doubt, I would recommend having someone that is experienced with these tables take a look at the data together with you.
-Victoria
LikeLike
Hi Victoria. I am currently working on a process to apply cash to invoices automatically through eConnect. However, I want to check for a remaining balance on the invoice and notify my user if the payment being issued does not pay the invoice completely. Can you please tell me what tables that are being used on the Inquiry -> Sales -> Transaction by document window? That window does contain the Amount Remaining on the document. If you know of an easier way to get this information, I am open to that suggestions as well. Thanks for you help!
LikeLike
Hi Bill,
You should be able get the Amount Remaining for a sales invoice from the CURTRXAM column in the RM20101 table. Any posted sales transaction with a balance should be in that table.
-Victoria
LikeLike
That shows me what is remaining on a Payment document, but not on the individual invoice. For instance, if I have invoice 12345 in Document Number PYMNT0234831, that shows me what is remain on the entire document number but not invoice 12345. Unless I am missing something, which might be the case.
LikeLike
Bill,
If invoice 12345 is posted it will be in one of 2 tables – RM20101 or RM30101 – with a DOCNUMBR of ‘12345’ and RMDTYPAL of 1. If the invoice is fully paid and has been moved to history it will be in RM30101. If it is not fully paid or if it’s fully paid but had not been moved to history, it will be in RM20101. The CURTRXAM field in RM20101 will show the amount still due/open on the invoice. Since invoices in RM30101 will always be fully paid (or voided), no need to check there if you’re looking for the amount due on an invoice. It will either be in RM20101 with a CURTRXAM or there is nothing due on it.
Hope that helps.
-Victoria
LikeLike
Victoria,
I hope this finds you well.
We are having an issue where random SOP10100.CUSTNMBR entries are showing up lower case. It is happening on various SOP10100.DOCID types and with various SOP10100.USER2ENT logins. The RM00101.CUSTNMBR records are not lower case.
While a few of the records are imported from our web store, the others are hand keyed by users.
It is creating an issue when we try to apply credits/payments/returns to the invoices if the returns or invoices are lower case and the document applying is not. Some returns have lower case SOP10100.CUSTNMBR, but the invoices do not, so the returns cannot be applied.
Have you ever seen this or know where this could be allowing the lower case entry of customer numbers?
Thank you so much,
Cat Douglas
LikeLike
Hi Cat,
I have not come across this before. Out-of-the-box GP will not allow lower case letters to be typed in during regular data entry, so unless you have some kind of customization that is allowing these to be typed in, I would think that these transactions are coming from your imports. I am not 100% sure that USER2ENT is reliable enough to go by. I would have to do some testing to be convinced that is accurate.
Short term, you can rectify this by updating the data directly in SQL to be all upper case for that field. Longer term, I would really want to figure out why this is happening, as it may be a symptom of a bigger issue. It may be worthwhile putting a trigger on the SOP10100 table to track exactly when and how these are coming in.
-Victoria
LikeLike
Hi Victoria,
Our user is looking for Sales Multiple Orders to Invoices Transfer. Can’t find this in GP 2016. Is this no longer available in the 2016 version?
Thanks.
LikeLike
Hi Rinno,
It looks like it’s still there – under Transactions | Sales | Transfer Multiple Orders. It’s possible that the user does not have permissions to it or you may not be licensed for it – I believe this is part of the Extended Pack in GP 2016.
-Victoria
LikeLike
Hi Victoria. Is there a setting in GP2016 that will allow multiple users into a sales document?
LikeLike
Hi Mary Ellen, do you mean into the same sales document? At the same time? To edit it?
-Victoria
LikeLike
Yes, an example would be: a user in GP2016 is in an uposted sales document and a user on a hand held pulling the order for shipment. This was possible in GP10. Thanks.
LikeLike
Mary Ellen,
In my recollection this should not have been possible in GP 10, at least not using out of the box GP functionality. It’s also very dangerous. If someone is still editing an order, how can you pull it for shipment?
-Victoria
LikeLike
Hey Victoria,
I’m trying to make some better optimized queries for my workplace. One issue I’m having is that I’m trying to join on the Account Number (COGS for example) that was affected by a Sales Transaction. I can see that SalesLineItems view does something like this by default, but I’m not clear on how it pulls this off.
What should I use to join an SOP record to an account number?
LikeLike
Hi Andrew,
A few things that might help:
If you need additional help beyond this, it would be helpful to know some more details about what you are trying to accomplish with your code/report.
-Victoria
LikeLike
Hey Victoria, we were wondering if it is possible to view at what times sales documents are printed. Is that possible and where would that information be located?
LikeLike
Hi Jordan,
To my knowledge, Dynamics GP does not store this kind of information. 😦
-Victoria
LikeLike
Hi Victoria,
We are having an issue with Serial Numbers, and I am looking to try and correct some errors in a procedure. Do you know where the SLTSQNUM column originates for SOP10201? From what I have gathered, it seems regardless of the LNITMSEQ number the SLTSQNUM is being generated by multiplying 16384 by the DTSEQNUM. Will that always hold true?
LikeLike
Hi Aaron,
Usually the line sequence number for most GP tables starts with 16384 and increments by 16384 for each new line. If lines are inserted inbetween existing lines, they will be assigned a sequence number halfway between the two lines around it. I don’t believe the code actually calculates these based on another field, like DTSEQNUM. There are a lot of blog posts and forum discussions online if you search for ‘Dynamics GP line sequence number’. Here is one that might be interesting: https://community.dynamics.com/gp/f/32/t/114186
-Victoria
LikeLike
Hi Victoria,
Apparently there is another issue stemming from our serial number problem. Sometimes we have “ghost” invoices that will be generated with no header information and can’t be pulled up or deleted by GP, however, GP still allocates the inventory. The only tables that I seem to be able to find any information regarding said invoices is in SOP10200, SOP10201 where if I know the SOPNUMBE I can look it up. These have not been posted and cannot be reconciled and are sort of just hanging out, with us unable to void the transaction.
Instead of just adjusting the inventory to the correct level I would like to remove these from our database itself. My concern is what would might happen to other db items if I just delete the existing rows it won’t necessarily fix the allocated quantity. What might I need to do if I wanted to remove all information with regards to a specific SOPNUMBE and have the allocated inventory adjust itself?
LikeLiked by 1 person
Hi Aaron,
Sounds like you have a lot of stuff going on in GP and I would not feel comfortable giving you a quick answer on this, as it’s likely not going to be enough. It also sounds like you are treating the symptom and not the problem. If it were me, I would prefer to find out why you have these “ghost” invoices showing up and find a way to not have that happen. Then for the extremely rare instance that it does happen, the proper way to clean it up would depend on all the modules you are using in GP and possibly any 3rd party products you have.
-Victoria
LikeLike
Hi Victoria
Is there a setting that will allow notes on the sales order transfer over to notes on a purchase order? The header not line items
LikeLike
Hi Vic,
I am not aware of any setting that would allow this. 😦
-Victoria
LikeLike
Hi Victoria,
I have another SOP question…
We move our Invoices and Returns into GP via eConnect. Sometimes, there will be a $0.01 difference between our Amount Received and our Total because of Sales Tax calculations/rounding. This doesn’t seem to create any issue for Invoices (we just see $0.01 On Account), but Returns where the Amount Received is greater than the Total get rejected and have to be entered manually. Is there any way to keep this from happening?
As always, thank you so much for your help!
LikeLike
Hi Katie,
Typically there are 2 ways to address this (besides having to do it manually):
Hope that helps.
-Victoria
LikeLike
Hi Victoria,
Thanks for all your good work.
We are using GP 2016 and would like to know if there is any option to do the sales forecasting using GP itself or any other 3rd part tool. Please note that we are not using the Manufacturing module as through some google search I could only find the Sales Forecasting and MRP In Dynamics GP Manufacturing.
LikeLike
Hi Zafar,
I don’t believe there is any forecasting capability in out-of-the-box GP. There are a number of 3rd party products that offer this, but I have not worked with any personally.
-Victoria
LikeLike
Is there a table which shows you all the transactions in a particular Batch? I know SY00500 shows the summary info.
LikeLike
Hi David,
There is no one table with the transactions. Each type of transaction will be in a different set of tables. Are you looking for something specific?
-Victoria
LikeLike
Is it possible to unpost historical Sales Orders and move them back into the work tables? My assumption (and logic) is that it is not. But, as a matter of Due Diligence I agreed to ask the question on a forum where if it was at all possible someone would be able to tell me how. Thanks! Love your blog Victoria .. always helpful!
LikeLike
Hi Gail,
Your instincts are right – there is no way to ‘unpost’ in Dynamics GP.
-Victoria
LikeLike
Hello Victoria, I am trying to find out who entered the Sales Order in. I am looing in SOP30200.USER2ENT, but this seems to change to the person who posted the invoice. Is there a way to see who created the Order ?
LikeLike
Hi Mike,
I do not think you can definitively track this without something custom/additional in place to do so.
-Victoria
LikeLike
Victoria,
I did not see definition for SOPSTATUS = 0 and I’m finding records in my data, SOP10100 and SOP30200. Do you by chance know what actually means?
LikeLike
Hi Raul,
I believe if you’re not using SOP Workflow, most of your SOP records will have a status of 0.
-Victoria
LikeLike
Hi Victoria,
I would like to add access to customer cards for one of my users. I have checked off security task id: CARD_0201 in the security role setup screen, but they still are not seeing “customer” link under sales->cards. Is there another task ID that needs to be added?
Thank you so much for all the help and support you offer everyday.
LikeLike
Hi Alex,
That is the right security task, however that will only work if your security task has not been changed which is impossible for me to know – you may want to make sure the Customer Maintenance window is part of that task. Also, if the user was already logged in when you made the change, they will need to log out of GP and log back in to see the menu option.
Hope that helps,
-Victoria
LikeLike
Hi Victoria,
I have an interesting permissions issue with GP 2013 R2 (version 12.0.2038) where my user specifically cannot void sales documents. Upon clicking Actions > Void, I receive a message stating that “User [username] is not allowed to process Void transaction.” What is strange is that I am set up with POWERUSER credentials, and I have already confirmed that all sales types/type IDs are set up to allow documents to be voided. Additionally, other users with lesser security privileges are allowed to void sales documents without issue. I’ve already deleted my GP username and have recreated it with POWERUSER credentials to no avail.
Have you seen this message before, and if so, do you have a solution to this problem? From my experience, GP typically does not throw informational messages that are user specific, and I am not aware of any security tasks that would disallow users to void a sales document. Any insight you can share on this matter would be appreciated. Thanks!
LikeLike
Hi Nate,
Yes, I agree it is unusual to see a GP message like this and I have not seen this particular message before in all my years of working with GP. Is it possible you have a 3rd party product or customization that is causing this?
-Victoria
LikeLike
We try to stay away from GP customizations, so I don’t believe there are any at play here that would be effecting the sales series. If there were any, they would have been put into place prior to my taking over GP close to 5 years ago.
I cannot recall the last time that I was able to void sales transactions successfully, but I would guess that it would have been within the last month or so.
We do have a handful of 3rd party products installed, one of which was recently updated named Operations Core by a company called Blue Moon Industries. I’ll get in touch with them to see if they know anything about this. I appreciate the help, Victoria.
For what it’s worth, I found one other instance of this error online from 2/2/2016 at the following link, but no responses were listed.
https://www.gpug.com/communities/community-home/digestviewer/viewthread?GroupId=247&MID=23548&tab=digestviewer&CommunityKey=4754a624-39c5-4458-8105-02b65a7e929e
LikeLike
Nate, I saw that too, but didn’t mention it as there was no response. Perhaps you can reach out to them and see if they ever got this resolved. Out of curiosity – if you create a brand new user and give them the same permissions (really just Poweruser is probably enough), do they have the same problem?
-Victoria
LikeLike
I created a new user with identical security credentials and company access as my current user and was able to successfully void sales orders. At this point, the issue seems to be tied directly to my current user ID.
LikeLike
Nate,
I have seen that before with GP users that were created many versions ago and have gone through several upgrades. Once in a while it seems like the permissions for the user get corrupted. In over 20 years working with GP I have seen this no more than 10 times. Not sure if there is a way to clean this up without additional tools. If you’re ok with creating a new user ID, then you are good to go. If not, it may be worthwhile to either buy GP Power Tools or maybe talk to GP Support to see if they can help you track this down using the DexSQL logs and/or SQL Profiler.
-Victoria
LikeLike
Creating a new user ID is likely the route I’ll take then. I appreciate the quick and detailed responses, Victoria!
LikeLike
Hi Victoria,
Do you have any more information on the ORGSEQNM field in the SOP10200 and SOP30300 table? What it’s used for and where it’s value comes from.
LikeLike
Hi Timothy,
I am not seeing documentation for this anywhere and I also only see values of 0 for that field in our data. Do you actually have data in there? You might need to reach out to Microsoft if you need to understand where it’s coming from. Or maybe post a question on the Dynamics GP forum to see if someone else has an answer for you?
-Victoria
LikeLike
Looking up the field, it is called “Original Sequence Number Corrected”
Doing a search revealed a resource that described this: “The Original Sequence Number being corrected.”
What that all means? I’m not sure, but it sounds like there is some Correction procedure that got interrupted (“being corrected” is present tense).
Hope that helps point you down the right path.
LikeLike
Hi Victoria,
Can you tell me where credit memos hide in GP? I need to report state sales net of credit memos and I can’t seem to find them.
Thanks,
Kevin
LikeLike
Also – can you tell me what SOP type they are – I’m using the tables you suggested but can’t seem to locate them.
Thanks again!!
LikeLike
Hi Kevin,
Many companies use the term “credit memos” for different things, so it would be very important to make sure we’re all using the same terminology. What Dynamics GP calls credit memos are receivables transactions entered on the Receivables Transactions Entry window with a Document Type of “Credit Memos”. These are RECEIVABLES transactions, not SOP transactions. So…can you please clarify what you mean by credit memos? 🙂 How are they entered into GP by the users?
-Victoria
LikeLike
Hi Victoria,
They are entered through the receivables transaction window. They post to the AR and sales accounts on the GL. So – yes they are receivables transactions.
Thanks,
Kevin
LikeLike
Hi Kevin,
Thanks for the confirmation. These will be in the RM tables only (RM20101 and RM30101) with a DOCTYPE of 7.
SOP posts to RM, but RM does not post back to SOP…SOP is like a subset of RM.
Hope that helps,
-Victoria
LikeLike
Thanks!
LikeLike
I have to insert Sales Transaction information from ERP system to GP automatically. Right now I am trying to manual run some SQL script to see if all the data is display on the Sales Transaction Entry screen. The thing is some sequence number and Master number are missing not able to find which table that information is:
My quick question is :
Is there a way I can automatically insert the sales order information to GP automatically ?
Right now I am referring SOP10100 & SOP10200 tables
–
LikeLike
bgara,
Inserting transactional data directly into SQL tables is not a good idea. First of all, you need a lot more tables than just SOP10100 and SOP10200. Then you have the issue with master numbers, like you’re finding. Most importantly, you’re throwing all of the application level data and business rule validation completely out the window.
To properly import SOP transactions into Dynamics GP I would recommend using one of the tools that are created for this: Integration Manager or eConnect.
-Victoria
LikeLike
I am stumped – so a few months ago we used the GP utility to change from having 5 sales person IDs to only having two… then we deleted the three so that there was no confusion. Lately tho somehow the old/deleted codes have been resurfacing in the SOP10101 (commision) table on certain customers even though on the SOP10100 and the RM record it is just fine. so my trick knee tells me that there is another place that it is pulling from when the people are creating the orders or invoices — btw – we do not use commisions – so you have any thoughts as to what table the MS/GP utility missed when we were combining from 5 to 2??
LikeLike
Hi Jim,
I am not aware of anything out-of-the-box that would cause what you’re describing. Is it possible you have a 3rd party add-on or customization that may be doing something unexpected?
Also, if you’re not using commissions that table should be blank anyway. Or is it that you have commissions set up, but just not using them?
-Victoria
LikeLike
Hi Victoria – I’m getting an error that I’m out of space for table dbo.SOP10102 when I try to post a batch of invoices. I thought I could use the remove sales history tool in GP to free up some space but when I run the utility it tells me there is still an open transaction for the period I’m trying to remove 2006 – I can’t find anything on my aging that shows an open customer transaction so I don’t know how to eliminate the problem so I can run the utility. Any ideas? Also – am I going in the right direction to free up space in the database?
Thanks a bunch
LikeLike
Kevin,
I would not recommend deleting data as the first step , then you’re going to run into trouble when you need it. If you’re getting errors about SQL server space, you really don’t want to fool around with that. Talk to your network/SQL administrator and/or your Dynamics GP partner as soon as possible to get this resolved.
-Victoria
LikeLike
thank you very much, yes, it hurts to not change is that in my country is a new standard document number, so try.
LikeLike
Hi Victoria,
I need to have 19 characters in the field SOPNUMBER (which only has 17) not modify the field with the modifier, but does not take automatic entry in the sales window, you know what else should I do?
LikeLike
Elisa,
Typically the SOP number needs to be this long because you’re storing additional information in there. A better approach is to store the additional information in a different field.
-Victoria
LikeLike
Hello Victoria
I’m trying to insert an Order (SOPTYPE=2) with eConnect. I’m setting the location of the lines to a generic location that is then updated by the user manually. When I try to insert the line (with taSopLineIvcInsert) it throws the “The Quantity entered for this Lot is not available in the IV00300”. I checked that table and my Location is not associated with the ITEMNMBR and LOTNUMBR but I want to insert the line anyways. Is there any way to skip this validation? I tried with the Quantity Shortage option set to Avoid Shortage and it wasn’t successful.
Thanks a lot
LikeLike
Ivan,
I do not work with eConnect myself, but here is a good rule of thumb: if you cannot do it in the user interface, you cannot do it with eConnect. Anytime you’re asking questions like this, try entering the transaction directly in GP to see if its possible.
When you are using lot numbers and allocating orders right away, GP will do validation on the site/lot number combination. You cannot sell a lot from Site A if that lot is at Site B. I think you have a couple of options:
-Victoria
LikeLike
Victoria
Thanks for the fast answer. Let me add some more info, I created a new doc type before starting this project and I copied the information from an existing one. After doing this I tried creating an order that was exactly the same as the one I was trying to insert through eConnect (Used the same item, lot and location) and it was created without any problems. Both doc type id’s have line allocation so it’s strange I can’t reproduce the same behaviour.
You first suggestion is the simplest as you say but I can’t enforce it, yet, on my client.
Thanks a lot again
LikeLike
Ivan,
If you’re able to do it in the user interface, you should typically be able to do it with eConnect. You might need some more specific eConnect help, which I cannot provide. You could try posting your question on the Dynamics GP Community Forum to see if someone there can help you.
-Victoria
LikeLike
Hello, we are planning to use the drop-ship feature of GP so that we are sure that a PO from a certain Service Order does have an equivalent Sales Invoice. Thus, we would like to see the PO, SOP (order), and SOP (invoice) for a specific related transaction. Is there anyway in the smartlist that we could see the link between those three, (just like in the Receiving Line Items where we can see the related PO for a specific receiving transaction)? Thank you for your help.
LikeLike
Hi Kim,
I don’t think any out-of-the-box SmartList will help you with this. You’d have to create a custom one. You could probably start with this code for SOP-POP links.
-Victoria
LikeLike
perfect – I see the MKDIINDX now – simple join from there – thanks much!
LikeLike
Hey Victoria – first thanks for being one of the first resources that I use for GP answers… so tell me about SOP10102 (the sales GL Distributions) – I use it a fair amount for invoices and verification of account numbers in the background (we have certain sales items going to certain Account codes in the GL) – but in our databases there are no entries for SOPTYPE 2 or 1 — just for 3 or 4 — Problem I am running into is that I want to verify a markdown account before the invoice is posted (and in an ideal world before the order becomes an invoice) and although I can identify the mark down accounts on invoices – I can not find the distributions for Orders (or quotes)… so is there a comparable table to SOP10102 for SOPTypes other than 3 & 4 ? Thanks in advance!
LikeLike
Hi Jim,
Orders and Quotes are not transactions that you can actually post, so the distributions for them are not stored in the same way as they are for invoices and returns. In fact, they are actually not calculated – if you open an Order in GP the Distributions button is grayed out. What you can do if you need to check these is use the account numbers on the individual line items in the SOP10200 table.
Hope that helps.
-Victoria
LikeLike
so tell me this – if I am in a sop transaction and on a line – then hit the little blue arrow above the item (which takes you to the ‘Sales Item Detail Entry’ screen – the distributions button lights up – I understand that these are stored on the SOP102 but do you happen to know where they default from? The Mark Down account is not defined at the customer level and for a number of example items I do not see an markdown acount defined for them… this mean someone went in and entered this ‘stuff’?
LikeLike
Jim,
Take a look at this blog post. Towards the bottom there is a section that starts with: “Question: How does the system decide what GL account numbers to use?” Hopefully that will help.
-Victoria
LikeLike
THanks again – I knew about the settings, but also know that since we are set to ‘Customer’ that I get ‘confused’ because there is no default on Customer Account Maintenance for MarkDown accounts (and a few others) that ARE on the Item Account Maintenace – so where SOP defaults those accounts has always been a mystery to me
LikeLike
Hi Victoria,
I found an order number that seems to be in both header tables (SOP10100 and SOP30200). Why might this be?
Thank you
LikeLike
Matt,
This sometimes happens when posting or transfers to invoices do not complete properly. You could run Checklinks to see if that will fix these. If not, you might need to fix the tables manually. I would recommend looking at the data together with someone like your GP Partner or Microsoft Dynamics GP support to confirm everything if you’re not 100% certain.
-Victoria
LikeLike
We looked into it and found that it had been voided, but apart from that, we can only guess as to why it happened. Doing some other digging, it appears to be the only instance of this happening. Considering how many entries there are in these tables, it’s a drop in the ocean.
Thank you for the response.
LikeLike
Victoria:
Thanks for all the stuff you’ve put on the web. It’s very useful.
We use the SOP module for processing simple invoices. We also use the project module to track professional services projects in WIP. We’re now looking at starting to use the project module’s billing function to charge off the WIP, and invoice the customer.
This means we’ll now be using both SALES000xxx and BILL000xxx items as customer invoices.
Is there a way to link these so that they both follow a single invoice number sequence? ie,
SALES00014365
BILL0000000014366
SALES00014367
BILL0000000014368
etc?
Thanks.
LikeLike
Ed,
Sorry, I can’t help with this. I do not use Project Accounting at all and have not even looked at it in at least 6 years. You might want to post your question on the Dynamics GP Community Forum: https://community.dynamics.com/gp/f/32.aspx. There are lots of very knowledgeable folks on there.
-Victoria
LikeLike
OK, thanks.
LikeLike
Victoria,
I have a project where our VP of sales wants to be able to send a single document with all of the customer’s individual invoice header information for a specific date range. It was explained to me that they want an AR statement that looks like an invoice. Have you heard of any 3rd party that might do this. I feel that this is going to be so customized, I might just have to piece it together myself. I was wondering if you have heard of such a request.
Thanks
Tim
LikeLike
Hi Tim,
You could create a custom report using Crystal Reports or SSRS and use our product (GP Reports Viewer) to print/email it from GP. I have done similar projects a number of times for customers.
-Victoria
LikeLike
Hi Victoria,
Can we void a posted sales invoice? if yes, then how to ensure that it is not causing any other inconsistencies
LikeLike
Zafar,
Once posted, SOP transactions cannot be voided in SOP. The proper way to reverse them is to enter a return to reverse an invoice or an invoice to reverse a return.
-Victoria
LikeLike
Hi Victoria, Thank you so much.
LikeLike
Hi there – We are having an issue with our Quotes in GP. We have about 40 for the past few days that are showing up in the Smart list and Navigation lists for the customers but when we look them up in the Sales Transaction inquiry window they do not appear. Document Type ID is fine and we are leaving out batch so we can see all but they are not there. We are trying to transfer these to Orders but can’t find them. Help!
LikeLike
Hi Chas,
What happens if you double click on one of these from the SmartList?
-Victoria
LikeLike
Victoria,
Do you know if I can add the Note Field to a SOP Invoice Form.
We have a very long (non inventory item) Item Descriptions and would like to print them on the SOP Invoice form. The comment field is not big enough so we would like to use the note field.
LikeLike
Hi Jeff,
If you’re looking to do this with the GP Report Writer, I think the problem is going to be that you will have to make the section big enough to hold the biggest note there might be. 😦 Here is a blog post that may help with additional information and resources: http://blogs.msdn.com/b/developingfordynamicsgp/archive/2011/01/13/working-with-text-fields-in-report-writer.aspx
For what it’s worth, this is something that can be done relatively simply with something like Crystal Reports. 🙂
-Victoria
LikeLike
Victoria,
Thank you for your insight. Would it be possible to do in the Word Templates or is it better to go straight to Crustal to include the note?
-Jeff
LikeLike
Hi Jeff,
To accomplish it in the Word Templates, you would first have to do it in Report Writer, so it’s really the same thing.
-Victoria
LikeLike
Hello. I’m relatively new to troubleshooting GP 2010. I have several order numbers that still show as active in the SOP10100 table, while all having already invoiced with the associated invoice numbers populating to the SOP30200 table. How would I go about moving the order numbers to SOP30200 as well, to get them off of our Open Orders Report?
thank you for your help.
LikeLike
Chris,
There are two reasons that an order would not move to the history table: (1) not all the line items have been moved to an invoice or backorder (or canceled) and (2) something went wrong when GP should have moved the order to history. The first is much more common in my experience, however, the second does happen periodically.
If in your case all the line items are taken care of and you need to force GP to move the order to history, there is a utility in GP to help with this. (GP | Tools | Utilities | Sales | Reconcile – Remove Sales Documents). Enter your order number in the From and To fields and select Remove Completed Documents under Options, then click Process.
Hope that helps.
-Victoria
LikeLike
I realise it’s been a while since this was discussed … however.
I have a series of Sales Documents that are not sequential, is there a way of running the utility from inside of SQL or a way of running it over a batch?
Thanks
John.
LikeLike
(oops – Hi 🙂 )
LikeLike
Hi John,
Sorry, I am not aware of any way to do that. If there are too many documents, you could open a support case with Microsoft and see if they can recommend something. 😦
-Victoria
LikeLike
Hello,
I am performing experiments sending invoices ( sop type 3 messages) into GP via eConnect and msmq.
They appear to be formed OK, but they still have to be posted to the ledger. Is there an sop type or mechanism that can send an invoice straight to the posted ledger or is human verification always required?
Thanks Stephen
LikeLike
Hi Stephen,
There is no import tool that will import into historical tables, nor one that will post for you. However, there is a tool that can post selected/imported batches for you automatically – Post Master by Envisage: http://envisagesoftware.com/post-master-enterprise-for-dynamics-gp/.
-Victoria
LikeLike
Thanks!
Do you think triggers on SOP10100 and SOP10200 to copy lines into SOP30200 and SOP30300 respectively is too low level to perform a similar operation?
Stephen
LikeLike
Stephen,
Moving lines from SOP10100 and SOP10200 to SOP30200 and SOP30300 is only one of about 50 (maybe more) things that happen during posting. 🙂 Once you post in SOP, RM transactions get created and RM summary tables get updated. If you are using inventory items, inventory tables get updated. Then GL transactions get created and depending on your posting settings the GL transactions might also post right away, updating yet another set of tables.
-Victoria
LikeLike
Hello,
Is it possible to send Proforma invoices into GP via eConnect?
I do not see a document type mapping to this type of invoice.
Stephen
LikeLike
Stephen,
There is no ‘proforma invoice’ option in Dynamics GP. Typically I find that the need for a proforma invoice can be met with a custom report run from an order or invoice transaction in SOP.
-Victoria
LikeLike
There is a product called SOP Auto Invoice by Blue Moon Industries that will Auto Post Invoices for you to the GL without anyone having to touch them. It will even convert them from orders to invoices and then post them.
LikeLike
Hi! We’ve encountered a problem with entering customer deposits. Right now we are not using SOP – all of our sales are going in as Sales Transaction Entries. Often, we will receive a deposit for an event possibly a year in the future. We enter these as cash receipts, and distribute to our GL account (XXX-2010) for customer deposits as type RECV. Let’s say that results in a credit to XXX-2010 of $500.
When the event takes place, we prepare an invoice in Sales Transaction Entry and distribute the AR side to two RECV line items, one that offsets the deposit amount from the prior cash receipt and one for the remaining AR balance due. This creates a debit in XXX-2010 for the $500
But when the final payment comes in and we apply it to the open invoice, we have found that GP will apply it against both RECV line items on the invoice, which then offsets the deposit account line item from the invoice, resulting in a debit to XXX-2010 of $500. Now we have a debit balance of $500 in XXX-2010 of $500, when it should be zero.
I’m open to suggestions! Thanks!
LikeLike
Hi Andrew,
The other issue I see with your approach is that you have AR transactions not hitting the AR GL account, so I would imagine this can cause problems for your AR/GL reconciliation. Here is an alternate way to handle this:
Hope that helps.
-Victoria
LikeLike
Thank you so much for your answer, Victoria! On the matter of that deposit, do you suggest we use the SALES type on the distribution, then offsetting it on the invoice distribution?
LikeLike
Andrew, yes, for both the invoices (in my #1 and #3).
-Victoria
LikeLike
Terrific! Thanks for the quick confirmation. Appreciate you taking time to answer questions from all of us like this!
LikeLike
Hi Victoria,
I was wondering if there is a way to get the Order on hand at a specified date in the past.
Regards,
Julien
LikeLike
Julien,
This may be possible, but it would depend greatly on the specific workflow your company has for SOP documents. You would need to examine this to determine if there is repeatable logic you can use to determine what date an order is not an order anymore. This can be very complicated if you’re using Back Orders, partial fulfillment, etc.
-Victoria
LikeLike
Hi Victoria,
You are very very awesome, all your post are very interesting.
We are trying to make this for years but until now is a MUST in one client that I have, the thing is that Client need POST a INVOICE at the time is created, this need is because I developed a Integration with a particular POS (client development) and they need Apply some credit memo or Cash Receipt at the moment is created.
Sometimes they have a cash receipt with an amount pending to apply, and when in POS they create the INVOICE we need to make possible apply this document.
How I can do this? Is there a way?
Thanks a lot.
Simon
LikeLike
Simon,
Another approach may be to allow applying cash receipts or credit memos to an unposted SOP invoice. I know Microsoft Professional Services created this as a customization for one of my customers a long time ago. Maybe this is something they can do for you, as well (or you can create your own).
-Victoria
LikeLike
Thanks Victoria, We made some customizations in order to apply on POS and later when INVOCE is posted a “job” apply cash receipt or credit memo.
Thanks,
LikeLike
Your site is really helpful. Thanks.
We use the Integration Manager to pull EDI information and convert it to a Sales Order in SOP10100. The SOPNUMBE field seems to take the value of the field we use in the EDI information, which is what we expect. However, after posting the orders and transferring them to Invoices, the SOPNUMBE on the Invoice sometimes comes up different if we have had problems integrating orders. The Accounting department wants them to be in sync all the time. It appears that creating an Invoice from an Order sets its own Invoice #, but I can’t seem to figure out how to tell what it is using for its “counter”. Is there a query I can use to tell where the Invoice number counter is at?
LikeLike
Hi Blair,
Are you looking for where the next invoice number is stored? Is so, that actually depends on your setup. If you are using a specific numbering scheme for the DOCID for your EDI transactions, the next invoice number for that DOCID will be stored in SOP40200. If it’s not there, the next invoice number from SOP40300 will be used. Hope that helps.
-Victoria
LikeLike
That was EXACTLY what I was hoping to find and it perfectly addressed my need. THANK YOU, THANK YOU, THANK YOU!
LikeLike
Victoria, Do you know of a way we can order our sales orders to be printed by the number or products ordered. in lowest to highest order? The reason for this is when our shipping fulfillment department is picking orders we would like to have the single item orders picked first.
Regards,
Jonathan
LikeLiked by 1 person
Hi Jonathan,
If you were using something like Crystal Reports or SSRS for your sales order reports, this would be very straightforward. With the GP out-of-the-box Report Writer, I am thinking you’d have to create a custom function to accomplish this since it would need to first calculate the number of products for each order, then use that calculation when printing the report. Since Report Writer is a single pass report, you need that calculation to be in place ahead of time. You might want to post this question on the Dynamics GP Community Forum to see if anyone there has any ideas for you. If you’re interested in using Crystal Reports or SSRS for your reports, please let me know.
-Victoria
LikeLike
Victoria,
Thank you! We do have SSRS configured so I guess that would be the best route to go. Any information you could provide me to start would be greate.
Thank you again,
Jonathan
LikeLike
Is there a table and field that tells me the date when a credit card was authorized for a prepaid order?? The order is still open and in our warehouse to be shipped. I am trying create report that flags and ages these so our warehouse can prioritize
LikeLike
This will depend on what product you are using to process credit cards.
-Victoria
LikeLike
Hi Guys,
I’m trying to find a way of pulling out the SOP number (and all line items on the RCT) from a RCT number.
Can you give me an idea of where to look. I’m currently using SOP10600, POP300310, SOP10100 & POP10500.
Thanks
Heath
LikeLike
Hi Heath,
I am copying a reply from a few years ago for you, hopefully it will help:
-Victoria
LikeLike
We use Nodus and ePay to process credit card payments. Is there a way to capture those orders with an open cc transaction that have not yet shipped? Thank you again for your help. BTW, your site is my life vest in this sea of GP-isms. 🙂
LikeLike
Hi Katherine,
Thanks for your kind words! 🙂
Unfortunately, I do not know Nodus tables, your best bet is to reach out to them and ask about this. I believe their email is support@nodus.com. They should be able to give you a list of their tables and from there, I think you can create a custom report (maybe using SmartList Builder?) that can show you the information you’re looking for.
-Victoria
LikeLike
Victoria didn’t you have a report showing the aging for quotes at one time? I have been looking for one that shows the aging on quotes
LikeLike
Hi Michael,
I am not sure I understand what you’re looking for – a report of what all open quotes were at a particular point in time? Or a report on how old all quotes in the system are currently? Or something else?
-Victoria
LikeLike
hi Victoria,
i have a sale order somehow move to historical table w/o creating an invoice. Please let me know your thoughts on how to fix this. I need to invoice and post this order.
thanks.
LikeLike
Aaron,
You can either enter the invoice manually, or re-enter the order and transfer it to an invoice. Unless you have some customizations or 3rd party products that prevent this, you should be able to use the Copy feature so you do not have to manually type in all the line items again.
-Victoria
LikeLike
Hi Victoria,
What SOP Status should I filter if I want to get only « active » orders?
1 Only?
What does « In Process » means?
SOPSTATUS (SOP Status):
1 – New (Order)
2 – Ready to Print Pick Ticket (Fulfillment Order)
3 – Unconfirmed Pick (Fulfillment Order)
4 – Ready to Print Pack Slip (Fulfillment Order)
5 – Unconfirmed Pack (Fulfillment Order)
6 – Shipped (Fulfillment Order)
7 – Ready to Post (Invoice)
8 – In Process (Order)
9 – Complete (Order)
Regards,
Julien
LikeLike
Julien,
Are you using Sales Workflow in GP? What is your definition of ‘Active orders’?
-Victoria
LikeLike
Hi Victoria,
No, I’m not using workflow. My point is if I look into SOP10100, I’ll see orders that have already been taken care of. So I need to filter orders so I get only the orders that are still « active » in the system.
Regards,
Julien
LikeLike
Julien,
If you’re not using Workflow then the SOPSTATUS will not help you. Any order that is completely fulfilled should normally move out of the SOP10100 table into the SOP30200 table. So every order in the SOP10100 table should still be open, with at least one line item not fully fulfilled. If this is not your definition of ‘active’, then for me to help you, you first need to define what ‘active’ means to you, since that’s not a generic term that is the same for everyone.
-Victoria
LikeLike
Hi Julien, just a small clarification: SOP10100 table does not show the orders that have already been taken care. It shows open orders and will continue to show the order unless all the items are completely invoiced. So even if the item is fulfilled yet not invoiced you will see the qty remaining field greater than 0.
LikeLike
Hi Victoria, Thanks for your continued assistance.
We are having a situation where after posting a return, the corresponding records are not not showing in Financial data. The SOP10102 table is showing all the corresponding credit and debit distribution for that return, however, the GL20000 is not showing any corresponding records. When we ran the Reconcile to GL routine for the Receivable Management module for Account 12000, the receivable transaction part of the output is showing the return transactions, but the corresponding part in GL transactions is showing blank records with header as Unmatched Transactions.
The Returns which were posted before and after this specific return are all showing in GL20000 table. Kindly advise how we should proceed to resolve this issue
LikeLike
Zafar,
If everything looks correct in the SOP tables and you’re not seeing this transaction in any of the GL tables (including GL10000 and GL10001), then it’s possible the transaction either didn’t make its way to the GL tables, or (more likely) was deleted from the GL before it was posted. To resolve the issue you can manually enter a GL entry using the information from the SOP10102 table.
-Victoria
LikeLike
Hi Victoria,Thank you so much.
LikeLike
Hi Victoria, when I suggested the above solution to our Account manger, she showed her concerned in following words”In this JE there is another sub ledger Inventory 14000. This JE can be entered if this sub ledger is off also same as 12000. I also need to understand what happened. This can happen again.”
In addition, kindly advise what is the best way to enter these GL without affecting sub ledger.
LikeLike
Zafar,
In this case, I would recommend working with your GP Partner or Microsoft support so they can take a look at your system and SQL tables together with you. It’s not really something that you can troubleshoot in a blog comments.
-Victoria
LikeLike
Hi Victoria,
Thank you.
LikeLike
is there a way you can see deleted transactions in SOP?
LikeLike
Out of the box GP does not store deleted transactions, in any module. If you want to be able to see deleted transactions later, you can:
-Victoria
LikeLike
Hi Victoria, I have been following your blog for years, and always manage to find the answer to a question somewhere, however I am wondering if it is at all possible to see the GL account a line item has been posted to, to analyse revenue per product for instance
LikeLike
Hi Nela,
Unfortunately, GP posts sales transactions in summary, so it is not always possible to tie a GL account to an individual item. You can see what accounts were set up per transaction line item, but there is no guarantee that those are the accounts that were posted to. Also, if you have things like markdowns or trade discounts in the mix, that would make it more difficult. Typically I see companies use the items themselves to do reporting like this, for example, take a look at my .
-Victoria
LikeLike
Thank you so much for the quick reply!! It almost feel as if I am talking to a celebrity!!… awesome site and I use it every time I need to write a SQL script…
LikeLike
Thanks Nela,
I appreciate your kind words! 🙂
-Victoria
LikeLike
Hi Victoria,
I want to see the comments for each line item for all posted invoices. What table should I be looking at?
LikeLike
Bob,
That’s in SOP10202.
-Victoria
LikeLike
Ok, thank you. I also need the item number for each line as well…how do I match the comment to the item number?
LikeLike
Bob,
You can link the SOP10202 table to SOP10200 (or SOP30300) by SOPTYPE, SOPNUMBE and LNITMSEQ (line item sequence).
-Victoria
LikeLike
Hi Victoria,
Our sales person were preparing the Sales Invoice and mean while power failure, the Invoice is inaccessible now and shows as posted but still available in SOP10100 and SOP10200 but not in SOP30200 and SOP30300. And still showing in Allocated, I have tried Reconcile Sales Documents and Reconcile Inventory by Item but no result. you have any idea how to recover the invoice or at lease how to release from Allocated. Thanks in advance
-Ibrahim
LikeLike
Ibrahim,
Next thing I would try is running Check Links on the sales series. See if it either deletes or brings back that invoice. If it comes back with anything other than ‘No Errors’, you will need to run it again.
-Victoria
LikeLike
Hi, Victoria,
It is advisable to delete table records SOP10100CaptureShadow, this grows much. This table contains a record of the different processes for each document.
What is your recommendation to keep this smaller table?
LikeLike
Hi Luis,
Is ‘SOP10100CaptureShadow’ the actual name of the table? If so, that is something custom that you have, not out-of-the-box GP. You will need to ask whoever created that for you about the best way to handle it.
-Victoria
LikeLike
Hi Victoria, Thank you so much.
LikeLike
Hi Victoria, is it fine to change the province in SOP30300 directly if it contains wrong values in some invoices. For example one invoice has postal code instead of provinve in the state field
LikeLike
Zafar,
I think this should be fine if you do not have anything other than out-of-the-box GP. I do not believe anything is tied to that State field. However, I have never done this, so I would recommend some testing before doing this in a production environment.
-Victoria
LikeLike
Hi Victoria.
I would want to extract all information with the sales of the day. I have a task scheduled to run everyday at 10pm (after close operations). So far I have been using the next query, but i do not know if that is correct:
DECLARE @FECHA AS DATE
SET @FECHA=GETDATE()
SELECT
*
FROM SOP30200 (NOLOCK)
where
CAST(DOCDATE AS DATE)= @FECHA
SELECT
D.*
FROM SOP30300 (NOLOCK) D, SOP30200 (NOLOCK) H
WHERE D.SOPNUMBE=H.SOPNUMBE
AND D.SOPTYPE=H.SOPTYPE
AND CAST(H.DOCDATE AS DATE)= @FECHA
Please help me.
Regards.
Gus
LikeLike
Hi Gus,
Is the idea to pull out anything that with a transaction date / document date of today? If so, your code should work. Are you not getting the expected results?
-Victoria
LikeLike
Hi Victoria.
The idea is to have all information about the sales of the day everyday. Basically, I have to know how is the sales everyday.
I am not sure if i have to make the query with the DOCDATE field to extract information from SOP30200 and SOP30300.
I will use the result of those queries to build a little datamart.
Let me know your comments.
Thanks a lot.
Regards.
Guss.
LikeLike
Guss,
The answer to your question actually depends on exactly how your users are entering and updating transactions (what dates are used where, the workflow, etc.) and what constitutes a sale that you want to capture. While it may sound simple, there are usually many moving parts that may change what you want to capture.
For example, for some companies a ‘sale’ might mean a posted invoice and the sale date will always be the Document (or Invoice) Date. In that case, using DOCDATE in the SOP30200 table will work. On the other hand, some companies may consider a customer order they received (whether shipped or not) a sale. Still others may consider the sale date the ‘fulfillment’ or ‘actual ship’ date, while the invoice date can be completely different from the fulfillment or ship date.
So you need to very narrowly define what constitutes a sale for your needs and what date in GP corresponds to what you want to capture.
Also consider that is is possible to backdate things. So today I can enter an invoice with a DOCDATE of 11/30/2013. If you are only capturing the data for one day at a time, you will miss anything that is backdated. Finally, SOP30200 and SOP30300 may contain voided transactions, so you may want to filter them out.
Hope that gives you some additional ideas.
-Victoria
LikeLike
Hi Victoria.
Thanks for your answer.
I have another question…
How can I to see the SQL Code of a SmartList? Is that possible?
I hope your help.
Regards,
GB.
LikeLike
Guss,
No, not usually. I believe in GP 2013 SP 2 you might be able to see this using SmartList Designer, but I have not played with that too much yet, so I cannot say that definitively. If there is a specific SmartList you need help with, I can try to help…let me know which one.
-Victoria
LikeLike
Victoria,
I really like your blog. It’s been very helpful. I am creating a sales report using excel report builder using the Sales Transaction History (SOP30200), Sales Transaction Amount History (SOP30300), and Item Master (IV00101) tables. When the report runs it returns duplicate Line items for each item on an invoice invoice. Do you know how I can prevent this from occurring?
Thanks,
AS
LikeLike
Hi Alan,
It’s hard to say exactly what is wrong without looking at it, but typically doubling of data is the result of how you are linking the tables. Take a look at that – perhaps you’re including too much, or not enough?. For what you’re describing, SOP30200 and SOP30300 should be linked by SOPTYPE (SOP Type) and SOPNUMBE (SOP Number). Then SOP30300 should be linked to IV00101 by ITEMNMBR (Item Number).
-Victoria
LikeLiked by 1 person
Hi Victoria,
Good Day, Is there a possibility that when you modify a report in GPD using report writer it also changes the data inside the table, example in SOP reports.?
We really need your advice, thanks and more power.
RolanVH
LikeLike
Rolan,
I think to accomplish this you would have to customize the code behind to report to launch a SQL stored procedure that causes the data to change. Are you looking to do this? Or want to make sure it’s not done?
-Victoria
LikeLike
Hi Victoria,
Good day, just making sure it is not done. thanks vic,
Another thing in sales trx entry, the overall total is in dollar with invoice type=QTE but during report print out the amount is converted to peso. Is this due to system/customer settings or it is by default?.
Thanks and more power.
Rolan
LikeLike
Rolan,
When you go to print any SOP document the Sales Document Print Options window has a Currency To Print choice that defaults to Functional. I am guessing your functional currency is Peso…to print in USD you would want to change this option to Originating.
-Victoria
LikeLike
Hello Victoria,
your guess is right Victoria the functional currency is Peso, I try also changing the currency choice to originating amount but same output, I think because the amount in the corresponding table is really converted to peso.
So I just modify the report change the Docmant to Originating amount. Thank you so much Victoria god bless and more power.
– Rolan vh
LikeLike
Hi, Victoria.
I have a question about the table SOP10200&SOP30200 in Dyanmics GP 2010.
When you void a sales order, system generated a line in SOP30200 as a history transaction, but it still keeps it in SOP10200. And there is no “VOIDSTATUS” in SOP10200, but in SOP30200. So, how do you tell in SOP10200 which one is voided?
I know you can map two tables together to do it, but is there any reason GP keeps it in Open Sales Order Table, even it is already transferred as a voided transaction in History table.
Thanks,
LikeLike
Haitao,
Look at SOP10100, not SOP10200. SOP10100 and SOP30200 are the header tables. SOP10200 and SOP30300 are the detail tables.
-Victoria
LikeLike
Haitao,
I have noticed this as well – and Victoria is correct – see SOP10100 for the VOIDSTATUS field.
Chances are though you haven’t closed the Sales Transaction Entry window and allowed the void process to run to completion. I have noticed that until the void report is run (shown after closing this window), the transaction will show up in both the Open and History tables.
LikeLike
Is there a field on the SOP tables that track that an Item has Override Quantities? I need to know that information. Thanks.
LikeLike
Hi Jesus,
I do not believe this is tracked in GP.
-Victoria
LikeLike
What’s the difference between DOCDATE and INVODATE?
LikeLike
Jeremy,
INVODATE is the Invoice Date on the Sale Date Entry and Sale Date Inquiry Zoom windows. On invoices, it will always be the same as the DOCDATE, however for transactions like quotes and orders, it could be different.
-Victoria
LikeLike
Thank you for the verification.
LikeLike
Hi Victoria, Can we safely delete those duplicate invoices from SOP10100 and SOP10200 which due to some reason have the same entry in SOP30200 and SOP30300. We used an autmated tool known as FindSOPDuplicates10.exe and found that there are 5 duplicates with SOPTYPE = 3. We confirm it through Sales Document inquiry as well. The same documents are showing with both the Unposted and Historical options
LikeLike
Hi Zafar,
Yes, if you gave confirmed that they are duplicates, you can delete them from SOP10100 and SOP10200 in SQL. Then I would recommend running check links on the sales tables to clear these out of any other related tables they might be in.
-Victoria
LikeLike
Thank you so much Victoria
LikeLike
Hi Victoria! Would you happen to have any suggestions or resources you could point me towards for managing the size of our GP database? It’s currently sitting at ~30gb and just keeps growing. I am not quite certain if there exists a best practices maintenance plan or not. Andy advice is appreciated!
thanks!
LikeLike
Hi Steve,
There are probably a few different things to look at – for some you would want to talk to your GP partner or consultant to make sure that the database growth you’re seeing makes sense with your volume of transactions. Separately, you might want to talk to a SQL DBA for more SQL specific recommendations.
We have a maintenance plan that we put in place for all our customers. If you’re interested in seeing that, I go over the steps to create it, as well as other recommended SQL and database settings specific to GP, in my book in chapter 5. Here is the link for GP 2010 and SQL 2008: https://www.packtpub.com/microsoft-dynamics-gp-2010-implementation/book. If you’re on SQL 2012, my new book for GP 2013 and SQL 2012 is coming out in a few weeks.
-Victoria
LikeLike
Victoria,
I have a user that has erroneously entered ~ 700 sales orders. Is there a way to delete these other than going into Sales Transaction Entry and deleting one-by-one?
LikeLike
Nick,
Ouch! If these are all in the same batch (or even a few batches), you can delete the batch – that will delete all the orders in it (if your setup rules allow that).
-Victoria
LikeLike
These orders are currently in a mixed batch with other good sales orders. Is there a way to easily move them to a separate batch and then delete from that batch? I’m not as familiar with the Macros in GP, so I wouldn’t feel comfortable with Zafar’s suggestion.
LikeLike
Nick,
You have a few options:
– You can move the orders to another batch in SQL, you’d want to first create the new batch in GP, then update the BACHNUMB column in SOP10100 for these transactions.
– You can delete these orders from the SOP10100 and SOP10200 tables, then run check links on the sales series (everyone would need to be out of GP for this).
As Akram mentions, you probably want to test this before running it on live data. And, of course, you want to make sure you have backed up your data.
LikeLike
Hi Victoria, just to add, usually we do it using GP macro. Record the macro by first deleting an order manually and then use Excel to extend the macro to all the orders and then run the macro from GP to delete all the orders.
LikeLike
WOW! if they are in sequence then best is to create a macro and start deleting, For Example: you can have a macro for 100 deletions at one time thus you need to run 7 times each time you run you can make sure its deleted correctly.
else removing data from sales transactions table then check links followed by Reconcile sales module might help (you need to 100% sure what you deleting from transaction entry) having done in test environment first will help you a lot.
hope this helps.
LikeLike
Sorry,
GL00100 and GL20000 (which i have joined by ACTINDX)
LikeLike
Don,
You can join on GL20000.ORDOCNUM = SOP30200.SOPNUMBE and GL20000.ORTRXTYP = SOP30200.SOPTYPE.
You’ve already got GL00100 joined to GL20000.
Since both SOP30300 and GL20000 can have multiple lines for the same transaction, but there is no direct link by line, you may not want to join them directly. You can join SOP30200 to SOP30300 on SOP30200.SOPTYPE = SOP30300.SOPTYPE and SOP30200.SOPNUMBE = SOP30300.SOPNUMBE.
Hope that helps.
-Victoria
LikeLike
Great, thanks so much.
I got the SOP info on what to join from you great example “Dynamics GP SOP line items with serial numbers and comments”.
Thanks again, you’ve been a tremendous help!
LikeLike
I would also make sure there aren’t any remaining quantities in SOP10200 @ the line item level holding you up in any of the quantity types; and are there any remaining $ amounts not accounted for in the header record.
LikeLike
Hi Victoria,
I just want to say thank you for such an amazing site. It has really helped me out! I have what is hopefully a quick question regarding the SO/Invoice process.
I’ve got a situation where an order has been invoiced (the SO# is showing up as the ORIGNUMB in SOP30200 for the invoice) but the order itself didn’t move into history. Usually this wouldn’t be an issue but there were cash deposits made on the order and they aren’t showing up on the customer’s account now. I’ve run check links and reconciles and nothing has fixed it. Any ideas on how to get the deposits moved to the customer’s account and the sales order closed?
LikeLike
Hi Mike,
Normally the deposits would have come over to the invoice when you transferred the order – did that not happen?
Also, have you tried ‘Reconcile – Remove Sales Documents’?
-Victoria
LikeLike
No, the deposits did not come over. I’ve tried the Reconcile – Remove Sales Documents utility as well. The only thing I can think to do now is void the posted invoice, adjust the inventory back in and then re-invoice. I’d prefer not to have to do this if possible though.
LikeLike
Mike,
Sorry, I’ve not run into this situation before. Sounds like something went wrong with the transfer of the order, but I am not sure of the best way to fix it. You could try posting this on the Dynamics GP Community Forum to see if anyone else has some ideas for you.
-Victoria
LikeLike
Well, shoot. Thank you for the replies!
LikeLike
So, if anyone is interested here is what I did to fix this. I added a services part we have to the sales order at $0 and re-invoiced only fulfilling the new service part line. You can’t actually fulfill the original line items as they’ve already been invoiced. At this point you can just post the invoice and all of your deposits will be applied to the customer. It’s not exactly clean because anyone coming behind you trying to find details on this particular order will wonder why there are two invoices, but the money gets where it needs to go.
LikeLike
Mike, great, thanks for sharing your solution with us. While not exactly clean, as you say, it’s probably the best option. You can always add notes to the two invoices explaining what happened for anyone looking at this later.
-Victoria
LikeLike
Hi Victoria,
I’m trying to join the SOP tables SOP30200 and line detail (SOP30200) to PM (PM20000, PM30200) tables to get related information from a sales order (I did this with POP to PM with your help). To join the POP tables i basically joined pm.VENDORID = pop.VENDORID and pm.DOCNUMBR = pop.VNDDOCNM. Is there a similar way to join pm to sop?
LikeLike
Hi Don,
There is not typically a relationship between PM and SOP transactions. Can you give me some more information on what data related information you’re looking for?
-Victoria
LikeLike
I have a sales transaction that i need to get its associated freight costs and other shipping related information (for reports). I’m looking at the SOP “Sales transaction inquiry zoom”, “Sales Document detail inquiry zoom” and “Sales user-defined fields inquiry zoom” in GP and trying to match anything to the PM tables that relate to information in the following PM windows: “Payables Transaction entry zoom” and “BOL-Freight” window. Anything else i can provide for clues?
Thank you
LikeLike
Don,
“BOL-Freight” is not a window I am familiar with – is that a custom window? Unless you have something on the PM documents to link to the SOP documents (or vice versa), I am not sure how you can link them. Maybe if the PM documents link to POP documents you could link PM to POP, then POP to SOP?
Without that, there is no relationship between PM and SOP. You may be creating this link or relationship with a customization or some additional logic you’re manually entering, but that’s not something I can help with without that knowledge. Sorry not to be of more help.
-Victoria
LikeLike
Thanks for the guidance as always. Yes, i believe this is linked by custom info that i’m missing and haven’t been informed about yet. I’ll dig deeper.
LikeLike
Hi Victoria,
I figured out how i can link the 2 tables together but i’m having a difficult time finding where the “Sales Document Details Inquiry Zoom” is storing the data related to quote and order numbers.
specifically i’m trying to get an Orig. Number field from items that are in this window’s ListView control where the columns are: Type, Type Id, Document Number, Date, Orig. Type and Orig. Number. Do you know what table contains this info?
LikeLike
Don,
This is all in the SOP30200 and SOP10100 tables. All ‘related’ transactions will have the same master number (MSTRNUMB). Transactions that were created by transferring from another one will have an originating number (ORIGNUMB) and type (ORIGTYPE) that you can link on.
-Victoria
LikeLike
Thank you!
Is there a way to join the GL tables to the SOP unposted and history tables (i don’t see similar fields like the POP tables contain)?
LikeLike
Don, you’re not going to see unposted SOP transactions in the GL tables, as they won’t be there until you post in SOP.
For the posted SOP transactions, what table are you looking to join?
-Victoria
LikeLike
tables SOP30200 and SOP30300.
Thank you
LikeLike
And what GL tables do you want to link to?
-Victoria
LikeLike
Hi Victoria,
Just wanted to let you know that I appreciate your advice.
I hope you will be able to provide me with another resolve. We normally use SOP comments on our credit notes but we recently started doing RMAs and we would like to have RMA reason descriptions being printed on the credit notes instead. Unfortunately I am not seeing the possibility of getting the field service table to link with the GP SOP table in report writer. Is there anyway to achieve this?
LikeLike
Hi Carleesha,
I am not aware of any way to do this in Report Writer without writing custom code. This is not something that I work with, so you might be better served asking about this on the Dynamics GP Community Forum.
Another option would be to create your invoices and/or credit memos in SSRS or Crystal Reports. There you can easily link in any additional data you need from any table, view, etc. However, with this you would need something like our GP Reports Viewer to replace the SOP reports with your SSRS or Crystal ones.
-Victoria
LikeLike
Thank you for your prompt response as usual Victoria, we will look into that.
LikeLike
Hi Victoria,
Firstly, thank you for all your work you have done, it makes my job that much easier!
I need to obtain the entire sales order/invoice with just the SOPNUMBE from table SOP10100. What tables will i need to join on (what do i join on) and query to also get all the line items relating to the SO (all the info relating to the specified SO)?
LikeLike
Hi Don,
Part of the difficulty with answering your question generically is that I do not know what information you are entering for your sales orders. The basic information is in SOP10100 and SOP10200 for unposted transactions and SOP30200 and SOP30300 for posted or completed ones. However, you may need to add many additional tables, depending on what data is being entered. As a start, you can take a look at my view for Dynamics GP SOP line items with serial numbers and comments. If you need more, let me know specifically what you’re looking for and I will try to help.
-Victoria
LikeLike
Hi Victoria:
I am trying to pull tracking numbers for posted invoices and am not having any luck finding the documents in SOP10107. Is there another table this number is stored in after posting?
LikeLike
Hi Chris,
There is no other table for this, but that does not mean your users are not entering the tracking information somewhere else. I have seen many companies put the tracking numbers in the comments, for example. Have you confirmed where exactly your users are entering the tracking numbers?
-Victoria
LikeLike
Hi Victoria:
Thanks for the quick response. The tracking numbers are being stored in the Sales User-Defined Fields Entry form in the field called 1st Tracking No. I have an order that has not been posted yet that has tracking information in that field but the order does not show up in SOP10107. Do you know what tables the user defined forms tie to?
Thanks.
Chris
LikeLike
Chris,
If the field is actually called ‘1st Tracking No.’, then I suspect your users are simply using a user-defined text field and not the actual fields set up for the tracking numbers at the top left corner of the Sales User-Defined Fields Entry window. The user-defined text fields can be found in SOP10106 for both unposted and posted transactions.
-Victoria
LikeLike
Thank you Victoria! We solved the issue. Your help is greatly appreciated.
Chris Thornton
EDI Coordinator
Browntrout Publishers
cthornton@browntrout.com
1-800-777-7812 ext 193
http://www.browntrout.com
LikeLike
Thanks Victoria.
I’m utilizing the econnect outgoing service to obtain posted transactions (SOP30200 and SOP30300). i was concerned that the generated xml file that is moved into MSMQ from the GP outgoing table wouldn’t contain the order line items and other crucial data needed for my particular project. Your comment helped me determine that i was monitoring the wrong tables (SOP10100 and SOP10200). The goal is to obtain all the information pertaining to the (posted) order so that it can be re-formatted and sent to a client of ours. So far things are working well, if i need more information i’ll post another question. Thank you for your help!
LikeLike
Hi Don,
Great, thanks for the update!
-Victoria
LikeLike
Hello Victoria, I am noob in this forum, it’s an excellent website to learn about the Microsoft Dynamics GP tips. My question it’s simple: ¿Why an order has posted in table sop30200, if it is not transferred to an invoice or another document class?
Thanks so much
LikeLike
Hi Fabian,
It is possible that the order was cancelled or voided and thus moved to the SOP30200 table without having been transferred to another document.
-Victoria
LikeLike
Hi Victoria:
Thank you for the great resources. I was wondering if you could share some thoughts on a question I have? The SUBTOTAL (and as a result DOCAMNT) fields of SOP10100 are calculated from SOP10200.XTNDPRCE, which is in turn calculated from QUANTITY * UNITPRCE. I have a client that is going to begin cancelling quantities on orders rather than adjusting the QUANTITY field in order to measure fulfillment percentage and with the hope that more product can be fulfilled during the order process.
Because of this, there is a disparity between the actual amount that will go to invoice and the SUBTOTAL field displayed to GP users. For example, if an order has a single line with QUANTITY = 10, QTYCANCE = 5, UNITPRCE = 20.00, then XTNDPRCE and SUBTOTAL will show 200.00, rather than the 100.00 that is actual getting ordered. This is especially significant as they capture credit cards through a 3rd party plugin that uses DOCAMNT.
Is there a way to change the way GP sums up orders, or some other workaround you recommend?
Best,
Casey Kuhn
LikeLike
Hi Casey,
I am guessing this is a continuation of this discussion: https://community.dynamics.com/gp/f/32/p/28004/53898.aspx#53898 ?
There may be others, but I can think of 2 options for what you’re describing:
Hope that helps,
-Victoria
LikeLike
Hi Victoria,
I just wanted to reply back in case this is useful for others. What I ended up doing was creating a custom add-in with VS Tools for GP that shows all the order header amounts in a separate window (accessible via the Additional menu or a short cut key). This automatically updates as the user pages through orders, etc., and gets the data from a sproc that is used throughout the order fulfillment process.
Let me know if you’d like further details.
Thanks again!
Casey Kuhn
LikeLike
Hello Victoria!
Quick one: Would you happen to know how the field EXTDCOST from SOP30300 is derived? I would like to use it for gross margin / item calculations and as we have skus being sold from multiple vendors it would need to be coming from the vendor invoice as opposed to a standard cost.
Any help is appreciated, thanks!
Steve Lugovsky
(Big fan of your book!)
LikeLike
Hi Steve,
Thanks for your kind words!
There is no one formula for extended cost because it will depend on a lot of different setup elements in your GP, including inventory valuation method, tracking options, etc. Also, if you have a lot of situations where the item cost is adjusted after the sales invoice is posted, then what is in SOP30300 will not be correct, as it is not updated after posting.
-Victoria
LikeLike
Hi Victoria, how can I change the SOPTYPE label in dynamics GP through SQL, I mean, I don´t want to use modifier. I know there is a function but I don´t find it. Thanks
LikeLike
Edgar,
I am not aware of any way to change this in SQL.
-Victoria
LikeLike
Hello Victoria! Looking forward to getting through some of your book on the plane today! I’m trying to determine how I can see which Dynamics GP user account entered a specific line item that ends up on either SOP30200 or SOP30300. I found a process improvement that needs to be made, but I can’t seem to see which user needs to make the change.
Thanks for all your help!!
LikeLike
Hi Taylor,
Thanks for buying my book!
For your question – GP does not store the user at the line item level. So the best you can get (out-of-the-box) is the user who entered (USER2ENT) and user who posted (PTDUSRID) in SOP30200. Typically the same user will enter all the line items on a particular transaction, but I can see that not being the case is some environments. One other thing to consider – there may be one user who is entering Orders, while another is transferring from Orders to Invoices. I am not sure which of these users will be stored in SOP30200 – you would need to understand your particular workflow to see if this is the case and possibly do some testing to confirm what is stored in the database.
All that said, my 2 cents would be to talk to ALL users that are entering SOP transactions about any process changes/improvements. So that they all have the same understanding of what should be done and how to do it. If the issue is that someone is entering something incorrectly and you need to find out who (and they will not admit to it), you may need to create some SQL triggers to trap who is doing what you do not want them to do.
Hope that helps,
-Victoria
LikeLike
We are looking to enhance our use of GP and all of the Account Segments to drive better internal reporting. Additionally, we are doing a number of things in the business to streamline how we operate, including looking to eliminate a large number of warehouse and ship directly to customers from centralized warehouses.
Please note, each “Branch” has its own 1st segment in the GL and all Customers in that branch are tied to the 1st segment by customer class
What we would like to be able to do is to take an order for Product A from a Customer in Ft. Lauderdale (1st Segment 5259) and then have it shipped from the warehouse in Chicago (1st segment of 5144) – yes, it may be less expensive from a landed cost perspective to ship a low turn product rather than stock in a warehouse in Ft. Lauderdale.
We would like the Revenue (Credit) to be booked to the Fort Lauderdale P&L based as follows:
1) 1st Segment = 5259 (coming from Customer Card)
2) 2nd Segment = 47500 (coming from the Item Card)
3) 3rd Segment = 5100 (???? – we are flexible, but would prefer to come from Customer Card)
The Debit to Receivables would be booked from the Customer Card as well
Debit to COGS should be similar
1) 1st Segment = 5259 (coming from Customer Card)
2) 2nd Segment = 57500 (coming from the Item Card)
3) 3rd Segment = 5100 (???? – we are flexible, but would prefer to come from Customer Card)
Credit to the Inventory in Chicago when Shipped would be
1) 1st Segment = 5144 (coming from Warehouse Location from which fulfilled)
2) 2nd Segment = 13000 (coming from the Item Card for Inventory)
3) 3rd Segment = 5100 (???? – we are flexible, but would prefer to come from Customer Card)
Given that SOP is set up with Distributions coming from the Customer OR the Item, but not necessarily both, we are a little confused.
We certainly do not want to have multiple Item numbers (the same for each location).
Further, it would be very helpful to not have to transfer the Item to “phantom” Ft. Lauiderdale” warehouse first and then relieve (But perhaps that is doable).
Any thoughts are greatly appreciated.
Thx.
LikeLike
Tom,
Reading through this briefly, I do not believe you will be able to do this out of the box in GP. You may want to look at either FlexiCoder from eOne or a customization to accomplish this.
-Victoria
LikeLike
Thank you very much.
The two I have found are FlexiCoder and Advanced SOP/GL Defaulter from M4 systems in the UK
Shame that GP does not have something that you would think is so basic.
Appreciate all the time you put into your site
Regards.
LikeLike
Tom,
For what it is worth, this is not something most would consider ‘basic’. Of all the companies I have worked with over the years, maybe one or two needed something like this. All the others were fine with what GP could do out of the box. 🙂
Happy New Year!
-Victoria
LikeLike
This maybe a little tough but is there a way to conenct the dots between a receiving and a sale? I want to know what Inventory receiving and item was taken from and used on a sales document… Basically I need to track my FIFO… any ideas or help would be amazing!
LikeLike
Hi B,
I have not done a report like this, but this may be possible using the IV10200 and IV10201 tables. IV10200 will have the receipts with the receipt number or inventory adjustment in the RCPTNMBR field. IV10201 will have the sales with the sales doc or inventory adjustment in the ORIGINDOCID field. I think that you should be able to link on IV10201.SRCRCTSEQNM = IV10200.RCTSEQNM and the item number.
Hope that helps,
-Victoria
LikeLike
Here’s an easy one for some kind soul – using eConnect, when creating an SOP document (type Invoice), one of the available fields is USRDEFND1 for the Sales Order Work detail. I’ve put a breakpoint in the code to verify that the field truly has data in it before going on its merry way to GP, but for the life of me can’t find which table that lives in. I’ve gone thru Victoria’s awesome list of tables, checking the likely suspects, including SOP10200, SOP10106, and SOP10202, but verified thru SQL that the field isn’t going to any of those. Anyone care to share some holiday cheer and point me in the right direction? Many thanks!
LikeLike
CindyLu,
If that field is what it sounds like (User Defined 1) then it should be going to the USERDEF1 field in SOP10106.
-Victoria
LikeLike
Wow Victoria – say it again backwards – woW! Very gracious and kind of you to answer so quickly. Unfortunately though, there are a grand total of zero records in SOP10106. SOP10100 and SOP10200 look great for showing the record that was added, but it’s pretty lonely in SOP10106. I was thinking of running a SQL Profiler session to try and figure it out if you don’t have an alternate idea… Thanks again!
LikeLike
Hi CindyLu,
I can’t really imagine anywhere else that data would go unless I am misinterpreting the field name. Is it possible that the data is just not making its way into GP? If you look at the imported transaction in the GP application, when you go to the User Defined window is there anything there?
I think SQL Profiler would be the next logical step if you are not finding it. Please let me know what you find out.
-Victoria
LikeLike
Hi Victoria,
Thank you so much. I realized afterwards that I was able to do it by first creating a SmartList using SQL server and SmartList Builder for all the open orders having FRGTTXBL =1 (Taxable) and then creating a GP Macro using GP and Excel for all those orders to change the FRGTTXBL to 3 (Base on customer). Your reply helped me in convincing the top management that it cannot be done using utilities, routines or CheckLinks.
Regards.
LikeLike
Hi Victoria,
How can we apply the new changes done in Sales order processing setup to all the existing open orders. For example, we have changed the tax option from “Taxable” to “Based on Customer”. Now we want all the existing open orders to have freight taxes change from “Taxable” to “Based on Customer”.
Thanks
LikeLike
Zafar,
There is no easy way to do this. First, changing setup does not change anything on existing unposted transactions. Second, even if you were to change the setting itself using something like a SQL update, the amounts would not get recalculated this way. The best way to make this change is to go through all the open orders one at a time in GP, make the change there, and have GP recalculate the tax. While it is a pain to do if you have a lot of open orders, it may actually be less time than having to write and test code to update everything in SQL. Unless you have thousands of open orders, of course. In that case, it may be worthwhile to spend the time/money to write and test a SQL update for this.
-Victoria
LikeLike
Hi Victoria,
We have an issue where an invoice batch was posted and caused duplication. Is there a way in SQL to void this full batch instead of going through each invoice in GP?
– Steven
LikeLike
Hi Steven,
There is really no way to void in SQL, at least not easily. You’re talking about moving records from one set of tables to another. Doable? Yes. But would require some testing and good knowledge of GP tables and SQL. All in all might be faster and easier to void in the GP application.
What you could easily do in SQL is delete the duplicates. However, you can delete the whole batch in GP as well, without going through SQL.
-Victoria
LikeLike
– Victoria
Even if the batch is posted can it be deleted in GP? This batch has about 1200 invoices in it.
– Steven
LikeLike
Steven,
We’re talking SOP batch? Once posted, that can be neither voided nor deleted in GP. Nor would I recommend doing that in the tables, as you would still have issues to fix in related tables. I would recommend talking to your GP Partner so they can help you fix this properly. There are a lot of variables that would make the fix different, so it would be best if someone that knows your environment well helped you with it.
-Victoria
LikeLike
Hi Victoria,
I have been searching your site extensively to find a solution to my problem. I am currently working in SmartList Builder to prepare a report that combines “open orders/invoices” and posted “Invoices/Returns”. Unfortunately my combination of SOP10100/SOP10200 with SOP30200/SOP30300 only returns “open orders/invoices”. I am seeking your guidance on how to complete this report to include the posted information as well.
LikeLike
Hi Carleesha,
Here is an example of code that combines the open/unposted and historical/posted SOP transactions: https://victoriayudin.com/2009/05/17/sql-view-with-all-sop-line-items/.
Let me know if you have additional questions on this.
-Victoria
LikeLike
Thank you Victoria, I will look into it and keep you posted.
LikeLike
Hi Victoria,
We need a report to show the dropship and stockship sales separately. Now, there are invoices which have both dropship and stockship sales lines. Is there any way to calculate document amount from the sales line level, considering the invoice may have tax amount, freight, amount, trade discount, miscellaneous amount associated with it. Can you help us with any SQL query to do this job?
LikeLike
Hi Prakash,
You would most likely need to do some kind of prorating / weighted average logic. That’s a bit past the level of code that I typically post on my blog, as it can get very complicated, very quickly. It will also greatly depend of a lot of other components of your code. This kind of coding is something that my company can provide as a consulting project if you are interested.
-Victoria
LikeLike
Much appreciate your prompt reply.
LikeLike
Hi Vitoria, Thank you very much. Yes, you are right, it seems we need to focus more on users’ training. In fact, the users are changing the name accidently to certain characters which are either the most commonly used buttons such as Back space , Enter or Space bar or the buttons next to these bottons . So the customer name are showing as + (plus) , ” (single quote) or space.
LikeLike
Hi Victoria, Thanks again. It seems we do not own the field level security module. Regarding Dexterity customization, is it related to VBA file or something else?
LikeLike
Hi Zafar,
Dexterity is the proprietary application development environment that Dynamics GP is written in and many customizations are best using Dexterity, although there is a bit of a learning curve and it is typically better to have someone with training and experience create Dexterity customizations for you. If you are not too familiar with GP customizations, I would recommend talking to your GP Partner about this.
For what it’s worth – my 2 cents on customizations is that they should be limited to something that either enhances the functionality of GP or saves a lot of time for users. This is because every customization you add to your environment causes it to be more difficult (and thus, usually, more costly) to maintain, support and upgrade. If users are changing the Customer Name when they should not be, that is more of a user training issue.
-Victoria
LikeLike
Thanks for you promp reply. I was trying to find Field level Security through Tools>Setup>System but could not find it. How can we access field level security in GP 10.0
LikeLike
Zafar,
You may first need to make sure that you own this module and that it is registered.
-Victoria
LikeLike
Hi Victoria,
Is it possible to find who deleted an order in GP?
LikeLike
Zafar,
Not unless you’ve set up something to track this kind of event ahead of time.
-Victoria
LikeLike
Hi Victoria, can we disable or password protect the customer name field in sales transaction entry in GP 10. Thanks.
LikeLike
Zafar,
You might be able to do that with Field Level Security or Modifier, although my preference would be to do this using a Dexterity customization.
-Victoria
LikeLike
Do you know where Shipping Methods are stored in the database?
LikeLike
Jon,
That should be SY03000.
-Victoria
LikeLike
Thanks so much, I ended up finding it and was going to update when I saw that you responded. You’ve got a great site here and we should all be thankful.
LikeLike
I ran into an issue where the extd cost of a service item is showing in the SOP30300 table but it isn’t showing in the SOP30200 table. Is this normal? Our commissions are figured from the header so this causes an issue for our payroll dept.
LikeLike
Hi Robert,
It looks like you also posted your question on the Dynamics GP Community Forum and got an answer there. Let me know if you need any help with changing your reporting.
-Victoria
LikeLike
Error message when you try to open the Cash Receipts Entry window in Sales in Microsoft Dynamics GP: “Your previous transaction-level posting session has not finished processing”
I can see the batch in the SY00500 table with the batch ID that matches the user – I am assuming this is so because this window does not actually have a batch – and that when I close out the window the transactions all post. I went into GP and verified that the transactions were listed in the sub ledger as well as in the general ledger so I am not sure why this is still in the SY00500 table and why the user can not go into the cash receipts entry form.
I saw from several post to make sure everyone is out of GP – run the following SQL Scripts:
SELECT * FROM DYNAMICS..ACTIVITY
SELECT * FROM DYNAMICS..SY00800
SELECT * FROM DYNAMICS..SY00801
SELECT * FROM TEMPDB..DEX_LOCK
SELECT * FROM TEMPDB..DEX_SESSION
DELETE DYNAMICS..ACTIVITY
DELETE DYNAMICS..SY00800
DELETE DYNAMICS..SY00801
DELETE TEMPDB..DEX_LOCK
DELETE TEMPDB..DEX_SESSION
and I did – but that batch that contains those transactions is still there. I saw another post where someone said to look for lock spids so I ran the following script and got the following results:
sp_lock
spid dbid ObjId IndId Type Resource Mode Status
—— —— ———– —— —- ——————————– ——– ——
51 4 0 0 DB S GRANT
53 7 0 0 DB S GRANT
53 1 1115151018 0 TAB IS GRANT
54 17 0 0 DB S GRANT
59 4 0 0 DB S GRANT
60 17 0 0 DB S GRANT
61 17 0 0 DB S GRANT
So now I don’t know what I need to do… should I delete the SY00500 with the user id as the batch number? Will that fix things? Or what should I do – we need to enter cash receipts.
Thanks,
John
LikeLike
John,
If you are confident that the transactions have been posted properly to the receivables as well as the GL tables, then you can delete the record for this batch in SY00500. Most likely it has a transaction count of 0 anyway.
As you suspect, the reason the batch ID is the user’s name is that there was no batch created. However, that’s not because the Cash Receipts Entry window will not allow a batch. It’s because the user never entered one. To avoid these kinds of issues in the future, my recommendation is to either always use batches or to close each window you are working on as soon as you are done, so the posting can complete right away.
-Victoria
LikeLike
Hi Victoria,
At want point in document life cycle the QTY on hand is being updated?
Is it once the item is allocated, shipped or invoiced?
Thanks,
Martin
LikeLike
Martin,
I believe quantity on hand only gets updated when the invoice is posted.
-Victoria
LikeLike
Hi victoria,
Kindly Thanks for your response.. I wil check it out and update you…
LikeLike
Hi victoria;
If i create an partial invoice in SOP transaction. what are the tables will be updated.. And will it affect the PM and RM tables.
LikeLike
Hi Sathya,
I am not sure what you mean by ‘create a partial invoice’, but there is no way to give you a definitive list of tables updated without knowing a lot more detail about your GP setup. You can find information about transaction flows and tables in the GP SDK. Or you can turn on SQL Profiler and see what tables are updated.
RM tables only get updated when SOP transactions get posted. PM tables do not get updated by SOP transactions unless you have something custom in place to do that.
-Victoria
LikeLike
Hi victoria,
Thank you for your Response. but What ‘DEPSTATS’ define in SOP10103 table?.
I got DEPSTATS as 0 and 2 in my Sql table.. can you tell what does it means..
Regards,
sathya
LikeLike
Sathya,
DEPSTATS is Deposit Status, although I think that field name is a little misleading. From what I can tell looking at our data and doing some quick testing, unposted transactions will have a 0 in this field and posted ones will have a 2. So this is acting more like Posting Status.
-Victoria
LikeLike
Hi Victoria,
What a wonderful resource you provided!
I am new to dynamic GP and I got one question. I need to create a report for refund since customer partial/fully cancelled some products they have paid.
Where should I look this information?
Do I use SOP30300 where SOPTYPE equal ‘Return’?
Or
Do I use RM30101 where RMDTYPAL= ‘Credit Memo’, or I should use RMDTYPAL=’Return’
Thanks in Advance
LikeLike
Hi Wilson,
You’re looking to report on returns/refunds you have entered in GP? If so, the answer will depend on how you have entered those refunds and what kind of information you need to show on the report.
For example, if you need to show detail about the item itself and the refund was entered through Sales Order Processing, you will want tables SOP30200 and SOP30300. If it was entered as a Credit Memo directly in Receivables, then you want to use tables RM20101 and RM30101.
-Victoria
LikeLike
Hi Victoria,
It’s always a pleasure to read your articles! We are a Canadian company and a lot of our transactions in SOP are done in USD. Do you know of a way for GP to always display the originating currency in the Sales Transaction Entry / Inquiry windows?
Thanks in advance
LikeLike
Hi David,
Thanks for the kind words! I don’t believe you can ‘force’ the display to always show originating currency without a customization. 😦
-Victoria
LikeLike
hi Victoria,
what does it mean when invoices in SOP10100 has “**Sales Entry” value in BCHSOURC? Can i just simply remove the “**” in front? And how can i fix this problem?
Thank you,
Aaron
LikeLike
Hi Aaron,
I have not seen this before, so I am not sure what would cause that. In the SOP30200 table (and in general in GP) when you have a single * in front of the BCHSOURC that indicates that the transaction was posted without a batch. I would not recommend deleting the *’s until you figure out what is causing them – you may easily break something else if those are expected to be there. Do all your invoices have this or only some? What is different about those invoices from ones that do not have the *’s? Do you have any customizations or 3rd party products that may be creating SOP invoices?
-Victoria
LikeLike
Yes, we are using 3rd party application to generate SOP invoices. and for some reasons, yesterday was the only day that all invoices has ** in front of the BCHSOURC. and today invoices are fine after we restarted that 3rd party tool.
LikeLike
Aaron,
I would ask the 3rd party application company about the ** to see if it is safe to delete them and/or what would have caused them.
-Victoria
LikeLike
Hai Victoria,
I have an issue some what similar.
I have a invoice in SOP10100 with “*Sales Entry” value in BCHSOURC field.(here single star). There is some quantity allocated in this and the quantity is shown allocated in inventory item inquiry. But item allocation window shows no invoice.
How to solve this…I tried reconcile but didn’t solved.
LikeLike
Fazil,
Have you tried Check Links on the Sales Series?
-Victoria
LikeLike
Hi Victoria, is there any way to update invoice date to follow actual ship date or order fulfillment date? i need this because invoice date is somehow always follow the date when we transfer document from Order to Fulfillment Order, regardless of when we shipped out the goods to customer. Thank you
LikeLike
Hi Lukman,
I am not aware of any way to do this in GP out-of-the-box. Part of the reason this may not be so simple is that theoretically it is possible for the actual ship date to be different on every line item, so you would need additional logic to determine what to do in that case. If this is important to your workflow, you may want to talk to your GP Partner about a customization for this.
-Victoria
LikeLike
hi Victoria,
Do you know the table name that store value or prefix for Document No.? for example, when i select type ID = Order, then prefix should be Oxxxxxxxx. if back order then prefix should be BOxxxxx. I need to change prefixes but could not remember how. please help.
thanks,
Aaron N.
LikeLike
Hi Aaron,
The prefixes for sales transaction numbers are actually part of the number, they are not stored separately. Generally, I would recommend making changes to these inside GP, rather than in the tables. If you are using Sales Order Processing, there are 2 places the next available numbers are set:
If you absolutely must do this in the tables, the global Sales Order Processing numbers are in SOP40300, the Document Type ID setup is in SOP40200.
-Victoria
LikeLike
Thanks Victoria. This info is really help me to fix the problem we’re facing with. Thanks again.
Aaron
LikeLike
Hi Victoria,
Do you know any table in GP that show who edit document last? i am looking for something to show me like which user transfer invoice from sale order or back order to order?
i posted something like that yesterday and somehow it did not show up today. i am not sure whether you got my question.
thanks,
Aaron N.
LikeLike
Aaron,
I do not believe you can definitively track this information in GP without first setting something up to track it. Nothing out of the box will do this. Maybe the Audit Trails module will, but I am not certain.
-Victoria
LikeLike
Hi Victoria,
Will GP performance slow down when i have something to track that kind of info?
do you know or have any documents/tutorials on how to set that up?
Thanks
LikeLike
Hi Aaron,
That all depends on what you put in place to track it. It also depends on your transaction volume and infrastructure/resources. If this is the only thing you want to track, you could probably do it with a SQL trigger and a custom table without seeing an impact on performance. I do not have code or documentation for something like that available, sorry. I would recommend talking to your GP Partner or SQL DBA for help on setting it up.
-Victoria
LikeLike
hi Victoria,
Is there a field in any of the SOP tables that flag for cancel status? i am looking for this field to run a count see how many cancelled, voided orders in a specific time frame.
Thanks,
Aaron
LikeLike
Hi Aaron,
Voiding is done at the transaction/header level, so for that you can check the VOIDSTTS field in SOP10100 and SOP30200. Cancelling is done at the line item/detail level (and can be partial!) – for that you can check the QTYCANCE in SOP10200 and SOP30300.
-Victoria
LikeLike
Victoria,
When a user loses connection in middle of PO, I usually can go and populate the fields that are blank and user can continue with the PO, does this not apply in SOP as I can see the order in the unposted tables but when I try to access in GP, it tells me document is already posted but I can see fields that are blank in the tables. I’ve tried reconcilining to no avail, do I need to delete from tables and start from scratch?
Thanks,
Raul
LikeLike
Raul,
I have done this both ways, either filling in the missing information (in the tables), or deleting from the tables and starting over. I personally would opt for the deleting and starting over.
-Victoria
LikeLike
Hi Victoria,
I was wondering if it would be possible to find the actual receipt or PONumber from which an item was sold on the SOPNumbe level/ItemNumber Level ?? If so, which table would have that information? Thanks for your Help
Thanks.
Aaron
LikeLike
Hi Aaron,
It may be possible, but will depend on the specifics of how you enter data into GP. Table IV30301 has a list of ‘related’ receipts and sales of inventory which you can link to table IV30300 for more detail. If the way you enter receipts and your inventory item set up allow it, you should be able to use these tables to determine what receipts of inventory went to which sales invoices. From there you can link back to the POP10500 table to determine the PO numbers for your inventory receipts.
-Victoria
LikeLike
VICTORIA,
Quick question. I used your table list above to get the serial numbers of my line items. but I am not sure how to print them on the Invoice (a Crystal Report). if I tried to add the field from SOP10201 in the details section, it repeates the same lineitem entry for each serial (duh). do you know of a quick trick to pull the several serial numbers for a single line item, (seperated by commas) and print them on that line item.
LikeLike
Hi Raouf,
To have the line item not repeated for each serial number you would need to have the serial numbers in a separate section of the Crystal report – take a look at the sample SOP reports included with GP Reports Viewer for an example of this. The serial numbers are in the Details a section. The section will grow as needed based on however many serial numbers you have for each line and is a quick and easy way to add serial numbers to your report.
I am not sure if it is possible to put multiple serial numbers on a single line separated by commas directly in Crystal. The way we have done this in the past is to create a SQL function to loop through the serial numbers and concatenate then into one string. Then that function is used in a stored procedure that the Crystal report is based on.
-Victoria
LikeLike
Victoria, I created a subreport in the details section for the serial numbers, which prints the serials in a list after the description. That worked fine for two days, till we came across an invoice with Qty 36, which took three pages to print in a list of serials of that line item.
As a SQL newbie, I dont know how to do a SQL function then used it in a stored procedure. Do you have an example?
Thanks
Raouf
LikeLike
Victoria,
Thanks to your original suggestion, I researched online, and found this link to exactly do what I need.
http://stackoverflow.com/questions/7154873/concatenate-a-single-database-fields-multiple-values
Thanks
Raouf
LikeLike
Hi Raouf,
Excellent – glad you were able to find a solution.
-Victoria
LikeLike
Hi Raouf,
That’s a bit past the level of code that I typically post on my blog, as it can get very complicated, very quickly. 🙂 This kind of coding is something that my company can provide as a consulting project if you are interested.
-Victoria
LikeLike
Victoria,
I’ve been using your site for several years now, and appreciate the quality and quantity of information you provide. Thank you for providing such a valuable resource.
I’ve encountered an issue where I have 126 blank invoices showing up in the SOP10100 table dated for 1/1/1900. If I try to edit these, I get the message that they are already posted. I can write a query to simply delete these records from SOP10100, but was slightly concerned about side effects. Do you have any suggestions on a different way to handle these?
Thanks,
Chris
LikeLike
Hi Chris,
I suspect these are ‘orphaned’ records….so they probably should be deleted, but a much safer way of cleaning this kind of stuff up would be to run Check Links on the Sales series.
-Victoria
LikeLike
Good morning Victoria,
We are still fairly new to GP and are on GP2010. We do a lot of business with .coms and recently ran a Groupon for our products. When we do this, we ship from our warehouses to the consumer but we invoice the retailer. We have to enter the orders separately to generate packing slips for the warehouse but then we end up creating an invoice for every sales order. For Groupon, this means a thousand invoices to a customer we made up. We have to invoice obviously to update inventory but these invoices never go out to either the consumer or to Groupon who pay us automatically. Is there a way to group a whole bunch of sales orders to the same customer id on one invoice? We would like to be able to do all for a day or even a week at a time.
Thank you very much!
Scot
LikeLike
Hi Scot,
From what you’re describing a couple of thoughts come to mind:
-Victoria
LikeLike
Victoria,
We have been dealing with an odd situation at our company for years. It is impossible to duplicate using any set of criteria that I can come up with but still the issue occurrs several times a week. We have several people entering sales orders all day long. Every once in a while, the line items (SOP10200) from one order will end up associated with the order (SOP10100) that was entered by a different user. Have you ever seen this and if so, is there something I can do to stop this from happening?
Thanks,
Lonnie Nelson
LikeLike
HI Lonnie,
Sounds like you have tried to duplicate this and are not able to, so this may be a really tough one to troubleshoot. 😦 I have not seen this happen with out-of-the-box GP under optimal conditions. However, I have seen similar strangeness happen once in a while in environments that were heavily customized and/or were experiencing connectivity issues on the network or resource issues on the server. I know this is a very general statement, but it is really impossible to get into specifics without a lot more details. If you said that this happens once a year with high transaction volume, I would maybe write if off as some crazy glitch. But if it is happening several times a week, then there is definitely something that is routinely causing this.
Is the issue actually seen if you look at the tables in SQL? In other words, do the line items actually make their way onto the incorrect transaction? Or is it simply a display issue where the data the users are seeing on the screen is not the same as what is in the tables?
Do you have any modifications, customizations or 3rd party products installed? Is the data being entered manually on the Sales Transaction Entry window? What version of GP, by the way?
-Victoria
LikeLike
Victoria,
Thank you for your prompt response. We are currently on Dynamics GP 10.0 SP5 but have had this same issue since at least version 8.0. As for are we heavily customized, unfortunatley the answer is yes. I am new here at the company as their GP developer and I am currently reviewing the code for anywhere the SOP10100 and or the SOP10200 tables are modified or updated by the code. Before I got too deep I though I would check and see if this was a known issue that had a fix or process in place to stop it from happening.
Thanks again,
Lonnie
LikeLike
Hi Lonnie,
Definitely not a known issue and unfortunately in a heavily customized environment that is most likely the culprit. It may also not be something in the code that modifies the SOP10100 or SOP10200 tables directly, you may be looking at too limited of a scope. 😦
-Victoria
LikeLike
Victoria – I have a customer who had a few sales orders which have no customer id associated with them. they had some terminal server connection issues last summer which may be the culprit. However, these orders have put an allocation on some inventory items. I have updated some of the tables to correct the inventory Allocations, but the Order header info still shows when you pull up the Sales Document inquiry. Just the header, no details. What table holds the info when you open the Sales Document inquiry screen?
LikeLike
Brian,
Typically orders with no customer ID are orphaned records that just need to get cleared out…. Are these orders in SOP10100 or SOP30200? Have you run Check Links on the Sales Series to see if that clear these up?
-Victoria
LikeLike
Hi Victoria,
In a sales transaction entry window a salesperson can see invoices of other salesperson when they perform look up of invoices. Is there a way to block the other salesperson invoices?
Thanks,
Waseem.
LikeLike
Waseem,
Not without a customization. This is really not how GP is meant to behave, so I suspect it would be quite a challenge to accomplish.
-Victoria
LikeLike
Victoria, As a new user to GP your sight was a wonderful roadmap!
I have been trying to create a query for the last inventory transaction date. this date needs to include shipments to customers, receipts, returns, issues to manufactruing, scrap, etc…..Could you please help me figure out what tables I need to link?
Many thanks,
Candice
LikeLike
Hi Candice,
Thank you for your kind words. I do not work with manufacturing tables much, so I am not familiar with them, however, I suspect that you may be able to get everything you need from one of the inventory tables – I would start with IV30300. You should be able to pretty quickly determine if that does not include manufacturing data, if so you may need to add that in separately. Hope this helps.
-Victoria
LikeLike
Hi Victoria,
Thanks again for explaining. Yes, it is 1 Agent = 1 ea. We are planning to do it in our test system first and if everything goes file we will do it in the production sysetm.
LikeLike
Hi Victoria, thank you so much. As there are almost more than 300 such orders so the option of fixing the orders either deleting and reentering the order or the effected line item will be comparitively a time consuming task so we try with the option of changing the values in SOP10200, checklinks and reconcile. Just for my understanding, will it require some extra steps if the quantity is other than 1. Or the only requirement is to have the same quantity
LikeLike
Zafar,
The qty comment was simply to make sure that you’re not actually changing what the UofM ‘base’ is. So that 1 Agent = 1 ea. If you had 1 Agent = 10 ea or something like that, there are a lot of other fields that would need to be looked at and possibly updated in the SOP10200 table.
-Victoria
LikeLike
Good Morning Victoria,
We have recently changed the U of M for some items from ‘Agent’ to ‘ea’. Now when we are trying to transfer the old open orders to invoice through an automated process we are getting error message as ‘U of M does not exist for the Unit of Measure Schedule being used’. When we checked SOP10200 we found that all the old orders which are causing the issue have UOFM as agent whereas the new orders have UOFM as ‘ea’. Can we change UOFM from ‘Agent’ to ‘ea’ for all the old open order directly in SOP10200 to resolve the issue without corrupting the database? Kindly advise.
LikeLike
Zafar,
I have not tried this myself, but at this point, I don’t see that you really have any choice but to try changing this in the SOP10200 table. The only other alternative would be deleting/voiding the existing orders and re-entering them, so I would definitely try fixing them first. After making the changes in SOP10200 I would recommend running check links and reconcile (GP | Tools | Utilities | Sales | Reconcile – Remove Sales Documents) on the orders to make sure that everything is still ok with them. Btw, all of this is assuming that qty 1 ‘Agent’ = qty 1 ‘ea’.
-Victoria
LikeLike
Thanks for the quick reply. We have a 3rd party add-on (Grain Acctg) that when printing invoices doesn’t include that option. So we don’t print Invoices throught the GP side. Data does go to SOP tables but we don’t process on the GP side.
So I was trying to add the table via Report Writer. The invoice for our third party uses the same SOP tables plus some of their own (I didn’t list them). Any way sounds like I need to request an enhancement.
Thanks
Dedra
LikeLike
Or….use something other than Report Writer for creating/printing your invoices. 😉
-Victoria
LikeLike
SOP60300 – trying to add to SOP Invoice as many customer are going to EDI and require their Item number be listed. Tables available in Invoice are SOP50200, SOP10100, RM00102, SOP10106, SOP10200, IV00101, SOP10202, SOP10201. Having trouble figuring which table is best to link it to. Any suggstions would be appreciated.
Thanks
Dedra
LikeLike
Dedra,
Are you asking about doing this in Report Writer? If so, the Customer Numbers are already built into it – you just need to select Print Customer Item on the Sales Documents Print Options window when printing invoices.
If I misunderstood your question, can you please give more specifics on what you’re trying to do and where?
-Victoria
LikeLike
hi Victoria,
is there a sql script that i can view the “freight account distributions” where soptype = 2?
i just need to know the table and field name. SOP10102 will show all distributions but only when transfer to invoice.
Hope to here back from you asap.
thanks,
Aaron Nguyen
LikeLike
Hi Aaron,
There is no table that stores this information, because GL distributions do not get created for Orders. An Order does give you the ability to change what accounts numbers will be used when the Invoice gets created for some things (like sales and cost of goods), however the freight account is not one of these and cannot actually be changed on an Order. At this stage, the only account GP knows about for freight is what is set up for the company posting accounts (GP | Tools | Setup | Posting | Posting Accounts | select Sales under Series), so there will be one account possible per company for this.
-Victoria
LikeLike
Hello again Victoria,
In that case, how can GP smart enough to pick which freight account based on “ship to tax schedule ID” or on one of the user defined field, sop10106.usrdef03? We have several company IDs setup in GP and orders imported to GP based on that two fields. We are using eConnect to post orders and right now, all companies are using the same default freight distribution account.
Please let me know your thoughts and/or solutions.
thanks,
Aaron
LikeLike
Aaron,
I don’t believe that GP can do this out of the box. Are you saying that’s what is happening right now, or that’s what you would like to happen? If the former, then possibly you have a customization or 3rd party product doing this. If the latter, then you would most likely need a customization or 3rd party product to accomplish this.
-Victoria
LikeLike
Victoria,
Yes, it’s the latter. How does GP know which freight account to use when order transfer to invoice (i have 3 different freight account set up in GP). Do you think there is a setup in GP that i can alter that?
thanks,
Aaron.
LikeLike
Aaron,
I am not aware of any way that out-of-the-box GP can change the account used for the GL distribution of the Freight field at the bottom of the SOP transaction based on something else. You could possibly a line item instead of the Freight field for this, but whether that is viable depends on many other things. I would recommend discussing this in detail with your GP Partner so they can make sure they understand everything you’re doing and what you are trying to achieve and can help you come up with the best solution.
-Victoria
LikeLike
Thank you so much Victoria, I have just posted the question on GP Community Forum.
LikeLike
Hi Victoria,
Thanks for the reply. Any suggestion how to resolve this issue? Originally we were getting a vba related error while trying to open the sales transaction entry. Upon some research we found that it was due to office 2010 and re-installing the VBA 6.4 core components from the following link had been shown to resolve the issue.· https://mbs2.microsoft.com/fileexchange/downloadfile.aspx?fileid=2208ac6f-d575-4f34-8b19-40340fe3d20c”
We downloaded and ran the files and were able to resolve the issue of opening the Sales Transaction Entry, however this new issue of Transfer is sttill unresolved.
LikeLike
Sorry Zafar,
This is not something I have experience with. I would recommend posting your question on the GP Community Forum to see if others have some advice for you on this.
-Victoria
LikeLike
Hi Victoria, We are getting error message with the title as ‘Microsoft Visual Basic’ and body as “run time error ’70’: permission denied, when clicking Transfer for transferring individual order to invoice.It is happening just after upgrading our operating system from Windows XP to Windows 7 and Office XP to Office 2010. Please note that we are not getting the error when tranferring the batch. Please also note that we are able to transfer the order to invoice without any issues on computers who are still running Windows XP and Office XP or 2003.
LikeLike
Hi Zafar,
Sounds like you may have some VBA customizations and are running into Windows permissions issues because of them.
-Victoria
LikeLike
Hi Victoria,
I’ve frequented your site for some time now and find it so helpful! I now have a question of my own – I have hundreds of lines on hundreds of open Sales Orders that need qtys changed from “Qty to Backorder” over to “Qty to Cancel”. Would love to use IM to accomplish this, but as you know, IM only allows Insert and not Update on the Sales Transaction destination. Is there any easy way around this? I considered a before integration or before document script, but don’t know where to begin. Thanks in advance!
LikeLike
Hi Brynn,
I am not aware of an easy way to do this. Since you can’t do it with IM, I probably would not bother with before/after scripts and try to do this directly in SQL. It is possible you can accomplish this with updating the SOP10100 and SOP10200 tables and then running Reconcile on SOP transactions, however I have not done this myself, so I would recommend a lot of testing if you do decide to try something like this.
-Victoria
LikeLike
When entering Orders, we enter the Quantity and the Extended Price on the line item. GP automatically calculates the Unit Price. This works fine until you transfer the Order to an Invoice. During the transfer, GP recalculates the Extended Price using the Quantity and Unit Price. We use two decimal places for the Unit Price. So I assume GP is only writing a two decimal value to the Work table rather than five. Any ideas to get around this other than starting with an Invoice? Thank you
LikeLike
This is something that you should have an in-depth discussion with your GP Partner about so that they can assess all the various ways you are buying and selling your items. Typically, rounding differences are going to cause issues in GP – partly what you’re describing, but also other possible issues that might not be so readily seen. So it is better to be able to enter the unit cost and have GP correctly calculate the extended cost, not the other way around. 2 ways that come to mind for possibly addressing this are:
-Victoria
LikeLike
here is the link to download automated solutions for GP 10.0 on customer source. Links are also available for other version of GP
https://mbs.microsoft.com/Cms/Templates/document/General.aspx?NRMODE=Published&NRNODEGUID=16CA414A-7E31-4996-BF47-780C0C954166&NRORIGINALURL=/customersource/support/selfsupport/automatedsolutions/automatedsolutionsR10.htm&NRCACHEHINT=Guest&wa=wsignin1.0
LikeLike
Thanks Zafar!
-Victoria
LikeLike
Hi Todd/Victotia,
We usually resolve these types of issues by running an automated solution UpdatePostedSOPDoc.exe ,
avail for free download.
when you run this application after entering GP login info you will be able to fix the invoice by selecting it from a dropdown list.
LikeLike
Victoria,
we had a user entering an SOP invoice. We had a power outage. The invoice is there, although not all information had been entered. It is in the work tables but when they try to enter anything it says that the invoice has been posted. When looking at the SOP tables there is no posting date. I can run check links but they are in a hurry and sales work needs to have no one working in the tables to run check links. Is there someplace I can look to use SQL to fix this quickly?
Thanks in advance
Todd
LikeLike
Todd, this is probably not something you can find a generic quick fix for, because there are various solutions possible depending on the exact state of the data. Check links is a good idea, but may not fix this either. I would recommend that you work with your partner or GP Support to help you fix this if Check Links does not resolve it.
-Victoria
LikeLike
Victoria, worked like a champ! I identified a batch user had with no transactions, deleted the batch and error disappeared. I knew you would have a solution. I use your site on a daily basis, great wealth of useful information. Thanks once again for your help, much appreciated!
LikeLike