SOP Tables
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
- 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 Pick (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.
–
Last Updated: Jan 08, 2010
Hi Victoria
we posted quite a few invoices to 8/11/2010 when they should have been posted to 9/1/2010. Is there a way to change those dates via sql?
thanks
Vic
Vic,
I do not recommend changing this in SQL. I can go into a long rant on why, but let’s just leave it at saying that I would not even do this myself 99.5% of the time.
If it’s only the General Ledger transaction dates that are an issue, you could enter a summary adjustment directly in the General Ledger to update the numbers.
If you have reporting that is tied to the SOP dates, you would want to enter returns for all the invoices on 8/11 and re-invoice them on 9/1. You can use the Copy feature to save some time on this.
-Victoria
Hello Victoria,
We are using Dynamics GP Professional version 9.00.0281 and SQL Server Management Studio version 9.00.3042.00.
Friday I was in the process of applying payments to the invoices when our power shut off with a bang. The UPS failed to protect our computers and server.
I was in the Cash receipts entry window and I had checked off all the invoices but one in the batch when this happened.
Now when I go into Transaction/ cash receipts entry window, I select the batch I was working on and click apply. In the apply sales document window I try to click the one last invoice and I receive the message, “document is fully applied”.
I go to that invoice and click on documents I receive the message, “Document does not exist” and the cash receiving zoom shows everything is blank except the payment number and the dates fields show, “0/0/0000”. I am unable to process the payment. The payments on the other invoices I did check off before we lost power was able to post.
Can you tell me what I should do to free up the invoice so I can apply the payment?
Thank you in advance!!
Cindy
Hi Cindy,
First things I would do is run check links (first) and reconcile (second) on the sales series. Have you done that already? If not, that’s what I would start with. If check links comes back with messages/errors, run it again until it comes back with no errors.
-Victoria
Victoria,
First off, I’d like to say thank you and I appreciate this and other sites that offer this type of useful information.
I am new to your site and don’t possess the technical acumen that many of your readers have, so please excuse my lack of knowledge on this subject. With that said, I have a situation that occured today that I have been asked to correct.
I’m not sure if you need this, but just in case, we are using GP 9.00.0281. An open sales order was being transferred to an invoice and the user received an error. She proceeded to cancel the error message. Unfortunately I can’t tell you what the error message was because she can’t tell me. What I can tell you is that the result is that the sales order is now complete but there is no associated invoice. I found the sales order in SOP30200, but I’m not exactly sure what to do to open it back up so we can transfer it to an invoice appropriately.
Any suggestions would be greatly appreciated. Happy month-end!
Stephanie
Hi Stephanie,
Thanks for the kind words.
First I would recommend double checking that the invoice is not sitting somewhere stuck. Probably the easiest way to do that would be to look in SmartList under Sales Transactions – look either for the same Master Number as the Order, or for a transaction with the Original Number = your Order Number. (If you prefer, you can look in the SOP10100 table in SQL instead.)
If you are certain that the invoice is not there, then probably the easiest thing to do is to enter it manually. You can use the Copy feature to copy all the line items from the order to a new invoice. You don’t get any automatic links to the original order this way…and if that’s important you could possibly add them directly in the database, but in most cases, this is not too important for just one invoice.
-Victoria
Hi Victoria,
Thank you so much for the information. I checked the SmartList and found the sales order number. Then just as a double-check I also looked for it in the SOP10100 table but did not find it there. I think we’re just going to enter a direct invoice and call it a day and put notes in the comment field cross-referencing it to the original order. Luckily it’s only two line items.
Thanks again for your assistance!
Stephanie
Hi Victoria!!
Great Blog!!! and congrats on the MVP!!!
I am writing a net sales report in SRS. I need to take invoices amount without freight minus credits AND minus returns totaled up by year and then by month. Is there any views available that gie me this info. I have tried RM00104 but that only has returns not credits.
Thanks
Vic
GP 2010
Hi Vic,
Thanks for the kind words!
Unfortunately, it sounds like you’re not going to be able to use any of the existing summary tables and need to build your own summaries. I would probably use RM20101 and RM30101, or maybe start with this view: http://victoriayudin.com/2009/04/24/sql-view-with-all-posted-receivables-transactions/.
Also, make sure you determine if you want document dates or GL posting dates – if these are not the same for all transactions (and I have rarely seen a system where they are), then you’re going to want to know that upfront before you write the report and then have to rewrite all the totals formulas.
-Victoria
WOW thanks for the quick response!!!!
I am using GLPost date. Basically i need to make the totals tie out to the GL any thing there I might be able to use???
Vic,
Is the idea to to just get the detail behind what’s in the GL? Or to verify that the GL is correct (ie, get the same total from 2 different sources)? If the former, then I would use the GL tables, so maybe this: http://victoriayudin.com/2009/08/11/sql-view-for-all-posted-gl-transactions-in-dynamics-gp/. If the latter, you should have everything you need in the Receivables view I pointed you to in my last response.
-Victoria
Thanks!!! I managed to talk my CFO into just taking out returns for the sales report. But the other views made him very happy!!!
Another question, if i need to move several documents to another batch do i just update the bachnum field in sop10100? Or do i have to update anything else. The are all back orders for a certain customer and the same po number.
Vic,
You said ‘several’ – if it’s not too many, I would recommend changing the batch number via the user interface. That’s the preferable way of moving transactions into another batch, as it then updates the batch counts and totals correctly.
-Victoria
well it is more than several….it is like over 10,000
Ah, well…that IS a few more than I would want to do manually.
Make sure you have the batch already created and saved in GP and then change the BACHNUMB in SOP10100…that should be fine, but as always, best to test it – try it with one or two transactions to make sure there is no issue.
-Victoria
Hi Victoria,
I have a error happening for only 1 user in only 1 company…
No other users have this issue “sa” do not have any issues either…
The user was using Customer/Vendor Consolidation screen under
Transactions –> Sales –> Customer/Vendor Consolidation….
Power was abruptlly switch off… when User tries to log in back to the same screen
User gets this Error
“Your Previous Transaction-Level posting session has not finished processing, Please allow time for it to finish. If you belive it has failed, log out of Microsoft Dynamics GP and log back into recover transactions”
I have checked this error in Knowledge Base but found the same error for Sales Transactions Screen.. i have checked all the process there are no blank batches in system…
I have performed the Checklinks, Reconcile and restarted the SQL Server
but to no use… the user still facing the same problem…
any ideas where to look for these issues…
Thanks
Akram.
Akram,
Get all the users our of GP and delete all records from the SY00800 and SY00801 tables in the DYNAMICS database. (It is very important to get all users out first!)
If the issue is still there, check for any batches in the company database SY00500 table with the user’s login ID as the BACHNUMB.
-Victoria
I Updated the BachNumb on sop10100 but, when i check order on the sales transaction entry it does not reflect my changes. Is there an another table i need to updated the BachNumb? Thanks for your help.
Aaron,
I just quickly tested this and could not find a way to make it not work. If I had the SOP transaction open while making the change in SQL Server, switching to another transaction and going back to the one I changed showed the change. If I used a batch number that didn’t exist, it would make the Batch ID on the transaction blank when I brought it up in GP, but it would still changed from my previous Batch ID. The only thing I can think of is that if your SQL Server is using binary sort, you have to use BACHNUMB, not ‘BachNumb’, but that should have given you an error in SQL if this was the case.
If this is still an issue, can you write back with exactly how you are updating this in SQL and what, if any, messages you get back when you make your update?
-Victoria
Victoria,
Thanks for your reply. I tried to update the BACHNUMB using (UPDATE [SOP10100] SET [BACHNUMB] where sopnumbe =@sopnumbe) … the bachnumb does not exist. After i update the SOP10100 and pull up the sales transaction entry screen, on the batch id it shows something else.
I do not get any error message when i try to update.
Thanks for your help.
-Aaron
Aaron,
Can you try creating the batch first? This is part of the reason changes are not recommended directly in SQL. If you were doing this directly in GP, it would not let you put a transaction into a batch that does not exist. For anything that you do directly in the database you have to triple check that you are not violating any of the business rules of the application. Otherwise you are risking your data integrity.
Also, not sure if you’re just abbreviating your code? If not, I don’t see what you are setting the batch number to in your code. Here is code that I have tested and works for me:
UPDATE SOP10100SET BACHNUMB = 'New Batch ID'
WHERE SOPNUMBE = 'SOP Doc Number'
AND SOPTYPE = 3 --this is for invoices, change as needed
Specifying the SOPTYPE is a precaution, but in SOP you can have the same SOP number for different transaction types (for example a Quote # 12345 and an Order # 12345 and an Invoice #12345), so it’s important to be careful with this.
-Victoria
Victoria,
Thanks for your reply. when creating a new batch from sql , can I just do an insert into the sy00500 or should I be aware of any other business rules? or do you think this is a bad idea to do it?
Thanks,
Aaron
Aaron,
I would recommend doing this in the UI to create new batches if possible. If you must create them directly in SQL, there should be documentation in the SDK on what is needed to do this.
-Victoria
Thank you for all your help.
-Aaron
Hello Victoria,
First af all, I just want to say thank you for your web site. It is very very useful!
I need your advice about SOP Posting Status.
You mentioned that SOP Posting Status is as below:
PSTGSTUS (Posting Status):
0 – Not posted
2 – Posted
3 – Error
I belive this is correct. However, I realized a strange code in SQL database function when I created a new view for a new SmartList.
Here is an issue:
Check the following function on SQL server management studio:
>> Databases >> your GP database >> Programmability >> Functions >> Scalar-valued Functions >> dbo.DYN_FUNC_Posting_Status_SOP_Line_Items
This scrpit shows that “when @iIntEnum = 0 then ‘Unposted’ when @iIntEnum = 1 then ‘Posted’ when @iIntEnum = 2 then ‘Posted With Error’ else ‘ ‘ “.
This means,
0 – Not posted
1 – Posted
2 – Posted with Error.
* Value 1 and Value 2 show the difference with yours.
Therefore, my smatlist shows that all posted transaction are “Post with Error” as posting status.
I’d like to know how we can fix this descrepany. I’m afraid to change this script, because I have no idea how this change will effect to other functions in GP. If you have any idea, please advise.
Best Regards,
Hayato
Hi Hayato,
Thank you for your kind words!
Before I get to your specific question, I would strongly advise against changing any existing GP functions, as that could easily break something in GP.
Looking at the function you mention, I notice that it has Line_Items in the name. When I am talking about the posting status, I am talking about the SOP header level, which is not the same as the line item level. So this function may be for something completely different than what you are using it for. (I don’t actually know what it is used for, sorry, you would need to ask GP Support if you want the answer to that.)
This is one of the dangers of using the functions you might find in SQL and one of the reasons that I have compiled the lists on my blog, so that it would be easy to build a case statement with the values inside any code being written. In many cases, you don’t even have to write the code yourself – you can copy it from what I have already written. For example, I have the case statement for Posting Status in my SQL view with all SOP line items.
-Victoria
Hello Victoria,
Thank you for your advice. It is very helpful! I understood about the Posting Status for Line Item and header. I thought both have the same value for the posting status.
I will copy your code for the Posting Status to my code and try to get the correct the Posting Status.
Thank you very much!!!
Hayato
Hello Victoria:
I have a situation. I put a wrong SLPRSNID in a lot of SOP transactions, posted transactions, in the current year. I need to change that SLPRSNID value. I tried with PSTL but that tool change one value for other value in the whole history and I need to update current year only.
I’m thinking to use a TSQL update on SOP30200 table but i’m not sure if that is correct or even possible.
What would happen if I use a TSQL update on SOP30200 to change the SLPRSNID value in some transactions?
Thanks very much for your help, greatly appreciate it.
emiranda
Hi Emiranda,
The answer to this depends on what you do with the salesperson ID as far as reporting and what other things you might have in GP tied to the salesperson ID. For example – if your SOP transactions had commissions tied to the salesperson, simply changing the ID in the SOP30200 table is not enough, you’d need to look at the commissions tables. If you are using any summary information for salespeople in GP, it will not automatically be updated by changing the ID in SOP30200, so that may need to be updated manually. Also, each line item can have a different salesperson in GP – if you are doing any kind of reporting on line items, or if you need to take that into consideration, you should also consider updating the SOP30300.
So….I would recommend consulting with someone (maybe your GP Partner or GP Support) who will be able to go through all the different things that the salesperson ID is used for in your situation and be able to suggest the best course of action for you.
-Victoria
Dear Victoria,
I’m trying to see all orders that are in GP that were not invoiced or that are partially invoiced? Is there a small script that I can use for this?
Hi Archie,
As soon as an SOP order is fully invoiced or transferred to another transaction type (like a back order, for example), it moved out of the SOP10100 and SOP10200 tables. So…any order that is in the SOP10100 and SOP10200 tables would fit your criteria.
For a script you could use the code I have posted for a SQL view with all SOP line items and just use everything prior to the UNION ALL in the middle.
-Victoria
hi,
In the sales batch entry screen, what table is this data in? Is it sop10100? I am trying to update the batch on over 500 sales orders to one, all of these are open, unposted sales orders. So I was wondering what tables I would need to update this batch number field in other than the sop10100 table? I ran the update on 2 sales orders which in GP it shows them in the batch I updated, but when I go to the sales batch entry screen and enter the batch it just shows transaction 1 when there are 3. So I was thinking there is another table that needs to be updated as well. I appreciate any assistance. Thank you
MC,
The batch totals are stored in the SY00500 table – this is all batches in the company, not just SOP batches. If you wanted to you could update the batch totals in this table. However, even if the batch screen does not show the right totals on the window in GP, the transactions will post just fine (unless you have turned on the verification of totals in your posting setup).
-Victoria
Hello Victoria:
I am attempting to print the Primary Ship to Address Code from the actual transaction onto the RM Detail Historical Age Trial Balance. The difficulty exists in linking the Document Number from the RM HATB file to a SOP table that contains the Primary Ship to Address Code.
Alan
Alan,
If you’re trying to do this in Report Writer, I am not sure this is possible without additional custom coding, which is not my area of expertise. I know this would be a piece of cake in Crystal or SQL, however getting the historical aged trial report may not be so easy. I would recommend posting your question on the Dynamics GP forum to see if any others can offer you some suggestions.
-Victoria
Victoria, thank you for posting such valuable information
great work and please keep it up.
I’m new about GP table and would like to find what tables would be for the Sales User-Defined table that can be accessed from Open Sales Order.
In the open Sales Order, we can access this Sales User-Defined fields from the ‘User-Defined’ button on the bottom of the Sales Order.
Thanks very much for your help, greatly appreciate it.
-Elisabeth K.
Hi Elisabeth,
Thank you for the kind words! All the SOP user-defined fields are in the SOP10106 table – for open as well as historical orders. Hope that helps.
-Victoria
Victoria, thanks so much for your response.
We are running parallel with an AS400 system, and we are working to detach from that system in about 3 months…
- We do report on sales and do manual entries into GL.. as they did with the AS400 reports.
You are very kind in responding so quickly and your website is very informative. We really appreciate your insight. So far, these are the affected tables I’ve identified..
Just looking to change the Posting Date : GLPOSTDT
RM20101
SOP30200
SOP10100
IV30101 – Sales Summary History
IV30102= Sales Summary Period History
IV30200 – Transaction History (header)
IV30300 – Transaction Amounts History (line detail)
The invoice dates, and postED dates will remain. We did do some work-around changes on our reports to grab those invoices in our reports just to have a look. We changed our query from just GLPostingDate values to Invoice Date beginning of period to report on it.
Trying it in test, if we are not successful, we would jut leave it for another month (with adjustments to the reports) and use the date utility to clean up practices by month 3..
Comments…? again, thank you so much… Troy
Troy,
Thanks for the additional detail. It looks like you have researched this, thought it through, and have a plan for testing as well as contingency. Without testing it myself, I would not have anything to add to your plan. I say go for it. I suspect that it will work just fine.
-Victoria
Victoria… great article…
We have an issue , 1st month live with SOP for us. GP has an open door, where it does not warn you about posting SOP doc in a new unopend fiscal period. We did do just that… posted a few batches into the new fiscal period. We DO NOT have sales/sop setup currently to post to GL in posting setup. Do you know what tables the GL Posting Date for the doc/batches are kept in.. We are considering editing that date for the subset of affected documents and seting it back to the EOP date…?
Thanks in advance.. Troy Kent
Troy,
As a general rule I do not advocate changing transaction dates directly in the database because there could be many other tables affected and unless you know and update them all, you could be causing more problems that you are fixing. And I not just talking about GL – there are summary tables in SOP and RM, there are inventory transaction and summary tables, etc.
The best fix for your situation would depend on a lot of other factors. For example, I would want to understand why you do not have SOP set up to post to GL – are you bringing in historical data for reporting purposes? If so, changing the dates in the tables would most likely have an impact on the reporting.
Without knowing the specifics, generally I would recommend entering returns that mirror what the invoices did and re-entering the invoices correctly.
Also, for the future, there is a free add-in that you can get from Microsoft (through your GP Partner) called Document Date Verify. This will prevent posting to GL dates that are not in existing or open fiscal periods in most of the core modules including SOP.
-Victoria
Hi Victoria,
For Invoice’s DueDate, generally do most companies display this to their customer on a printed invoice? By default, I don’t see GP displays the ‘DueDate’ so I’m wondering if it’s a common thing. Thanks!
Best Regards,
Nikki
Nikki,
Most companies we have worked with like to display a due date on customer invoices. But definitely not all. Some companies have all invoices ‘due upon receipt’, so there is no need for due dates. Others feel that having the terms on the invoice is enough.
My 2 cents is that unless your invoice is due upon receipt, there should be a due date shown to take any guesswork out of it.
-Victoria
Thanks so much, Victoria!
Victoria,
We transferred the wrong sales order to an invoice. We have not posted the invoice yet though. Is there a way to revert back (i.e. delete the invoice and have everything go back to the way it was)?
Regards,
Dennis
Dennis,
Unfortunately, unless you can restore from a backup because this was the last thing you did, there is no way to easily get everything back to where it was. You will need to void or delete the invoice, that’s the easy part. But then you will have to re-enter or adjust the ‘wrong’ order, depending on what the status of it is right now.
-Victoria
Love your info. I use it often.
I would like to know about the FLAGS in SOP10100 (sopHDRFL). What are these representative of and where are the dox for these bits?
Thanks,
–Chad L
Hi Chad,
Thanks! According to the SOPTRx document in the GP SDK, the SOPHDRFL field shows if the freight, misc amount and trade discount for an SOP transaction have been transferred.
1 – Freight Transferred
2 – Misc Transferred
3 – Trade Discount Transferred
Hope this helps.
-Victoria
Hello, just wana ask,,, i have a problem with one item, doesnt have an ‘override shortage’ option in the pop up window during entry of sales invoice in SOP module (no on hand quantity). While all items have that option.
I know there is only one set up for that.
I checked the item, doesnt have on hand quantity.
Thanks for the help.
Janet
Janet,
Is there anything different about this item? Is this the only item that does not have on hand inventory available? What do you have set for Override Quantity Shortages when you go to Tools > Setup > Sales > Sales Order Processing > Options?
-Victoria
Hi Victoria,
Great site with very helpful information, thank you.
Our team wants to get only valid invoices from Dynamics GP that have the following conditions:
1) Amount owed > 0
2) Status not void or canceled
OR
1) Invoice date within X days from current date
We are joining SOP30200 and SOP30300 to get the order and line items. We are also joining with RM00101 to get customer information. Are we grabbing the correct information?
Is there a status or amount field that lets me know that an invoice has outstanding payments?
Thanks,
John
John,
It sounds like you are on the right track, however, since a lot of your criteria will only be in the RM20101 table, perhaps you want to start there? The code below will give you the list of all invoice numbers matching the criteria you listed above:
Then you can link by the invoice number to the SOP30200 and SOP30300 tables to get the details.
-Victoria
I am facing strange problem, Line Items go missing from Sales Document specially from Fulfilment, What can be reason.
Khalil,
Line items going missing can mean a great many things, and a lot of them may depend on your GP setup and workflow. Could be someone is deleting them, could be they are being moved to other transactions (like backorders) or could be there is an issue with the connectivity to your server. If this is a recurring issue, I would recommend getting your GP Partner involved so they can provide more specific troubleshooting for your situation.
-Victoria
Victoria,
I always needed a cheat sheet for the GP tables thank you! One question: Is there a Date table of when the order in GP gets allocated? I see the one for fulfillment but I can find nothing for allocation. Thanks!
-Andrew
Hi Andrew,
I do not believe that GP tracks a separate ‘allocation date’.
-Victoria
I have a couple of users getting this error occassionally.
An open operation on table ‘SOP_Report_TEMP’ failed because the path does not exist.
Do you know what i can do to fix this?
Larry,
I see at least a dozen KB articles with a similar error message, but most refer to old GP versions. What version of GP are you on? Also, what exactly are the users doing when they get this error? Is GP being run locally or on a Terminal Server?
-Victoria
Victoria,
I have been asked to come up with all invoices based on state in the ship to field. Question is does all information reside in the SOP30200 table or do other tables need to be joined in order to accomplish this task?
Thanks in advance for your assistance,
Raul
Raul,
The SOP30200 table should be enough if you only have one ship to address per invoice. If you have different ship to addresses per line item (which is possible in GP, but most companies do not do this), then you would need to look at the SOP30300 table.
-Victoria
Victoria:
We have a need to track freight cost from Sales Order to Invoices, i.e. multiple invoices based on one Sales Order and separate item freight that may belong to different invoices.
Do you know of a way to do this?
1. Is there a way to link items in SOP30300 soptype 3 to an individual freight entry on the original order?
2. Is there a way to move items left on a sales order after a partial invoice to a new sales order?
Thanks for any insight,
Lien
Lien,
Sorry, there is no way to allocate freight in any way with standard GP functionality. You’re most likely looking at a customization to accomplish this. For your second question – you can transfer items from a partially filled order to a Back Order, but not to a new Order. However, Freight would not transfer over if it has already been transferred to an Invoice. Using Back Orders (if you are not already) may require changes to your SOP module setup, so I would look to test it all first before trying it in a production environment.
-Victoria
Victoria,
First off congrats on your 6th MVP award!
I’m fairly new to GP, although I’m and experienced SQL and .NET developer.
I’m doing a QuickBooks to GP Migration, and some of our systems defaults were not set up correctly prior to use. Here is the current problem.
I have pushed in Hundreds of SOP Transactions using Econnect. Our “Inventory” and “Cost of Sales” accounts were not setup prior to me pushing in these transactions……..and now they won’t post because at the line level……there isn’t a “Inventory” or “Cost of Goods Account at the line level.
Normally, I would just delete the batch and reimport, but we are using a third party “sales tax” product that makes this problematic.
Now to the question.
Is there a relatively easy way to assign line item detail distribution accounts using SQL?
Thanks
Thanks Todd!
You should be able to populate the accounts directly in SQL into the SOP10200 table. The inventory one is INVINDX and the COGS is CSLSINDX. You will need to populate these with account indexes (as opposed to actual account numbers) which you can get from the GL00100 table.
-Victoria
what u mean by consulting project
Hossam,
By consulting project I mean that we could create the report you need for you for a fee.
-Victoria
can you help me in this issue
i never make any report in smartlist builder
Hossam,
I actually would not ‘code’ this in SmartList Builder, I would create a SQL Server view. Then use that view in SmartList Builder, or any other reporting tool that you want.
If you are interested in having this created for you as a consulting project, please let me know.
-Victoria
Hi Victoria,
Thank you for this great support
i want to make total for quantities in SOP invoice for posted and unposted invoices by site and by customer class, is it possible?
Hossam,
Yes this is absolutely possible. You’d need to use SOP10100, SOP10200, SOP30200, SOP30300 and RM00101 tables.
-Victoria
Hi Victoria,
Your information on SOP tables is very helpful.
We have GP 7.5 version. We have the following error when we try to post a sales invoice, the sales posting journal shows the following error:
“Unable to obtain Receivables document number”
Will you please help
Thanks,
Sri Sankara
Sri,
This sounds like the issue described in Dynamics GP KB article 856940. I would try the resolution provided there.
-Victoria
Victoria,
In short:
Is it possible to see the GL Distribution per Line Item?
Explained:
SOP30200 = SOP Header
SOP30300 = SOP Detail (the detail holds Line Items)
SOP10102 = Holds the GL Distribution
What I try to achieve is to get the GL Distribution for each line item. So far I have been able to join SOP30200 to SOP10102 on SOPTYPE and SOPNUMBE which gives me the GL Distribution of the SOP Header. What I really want to see is the distribution per Line Item.
So when I have two line items on an Invoice being FEE1 and FEE2 get something like:
FEE1
700000-0000-00000-000 , 0 as DebitAmount , 1000 as CreditAmount
700100-0000-00000-000 , 0 as DebitAmount , 200 as CreditAmount
FEE2
700000-0000-00000-000 , 0 as DebitAmount , 2000 as CreditAmount
700100-0000-00000-000 , 0 as DebitAmount , 400 as CreditAmount
Rather than
INVOICE X
700000-0000-00000-000 , 0 as DebitAmount , 3000 as CreditAmount
700100-0000-00000-000 , 0 as DebitAmount , 600 as CreditAmount
I hope this makes sence.
Thanks John
John,
GP does not store distributions on a line item level, so that would not be easy to do. You could see what accounts were supposed to be used on each line item in SOP30300, but most of these could have been overridden on the document level before the transaction was posted and the changes would not show here. Also, certain things (for example commissions, taxes, etc.) will only have distributions at the summary/document level. But if you wanted to attempt this, I would use the SOP30300 table and the accounts on each line item and see what you come up with. Can I ask why you are looking for for something like this?
-Victoria
Thanks for you quick response.
I was already under the impression that GP didn’t maintain the GL Distribution on Line Item level but I had to ask the expert
Well, the company I work for has multiple Sales Branches and Sales Divisions. These all have different codes, Branches are hold in the third segment of the GL distribution where Divisions are in the second segment. Sometimes a sale is split into 2 SalesPersons one from branch A and one from branch B. The way finance post these sales in the SOP might be wrong but unfortunate I got to deal with it.
Finance creates multiple line items for each SalesPerson.
So going back to my previous example:
FEE1 is for SalesPerson1 Branch A
FEE2 is for SalesPerson2 Branch B
I try to get the Branch and Division from the GL Distribution rather then some free text fields finance use in SOP30300.
So, in the above example things might be easy to do, but when there are multiple items (Fee, expenses, other expenses etc) or the split is 50-50, it becomes rather difficult.
John
Are there GP table schema’s for each module available to find out the relations between tables?
John,
There is an SDK on disk 2 of the GP installation disks that should have some of what you’re looking for. Also, take a look at this blog post on Finding Table and Field Information in Microsoft Dynamics GP: http://blogs.msdn.com/developingfordynamicsgp/archive/2008/10/05/finding-table-and-field-information-in-microsoft-dynamics-gp.aspx.
-Victoria
Hi Victoria,
We are using partial shipments and I am facing issues in freight amount distribution. Let us assume, I have an sales order with 5 quantities with freight amount of $250. I have only 2 quantities on hand in inventory right now. I am allocating the sales order to for the available on hand 2 quantities. Now the order will have quantity ordered as 5 and allocated/ quantity to invoice as 2 and freight amount as $250. I am transferring this order to fulfillment document. The fulfillment document will be created successfully for the allocated 2 quantities. Since I have transferred only 2 quantities, the fulfillment document will have quantity 2 and original order will have remaining 3 (total quantity 5, previously invoiced 2 and quantity remaining 3). Also both document will be in SOP10100 and SOP10200. When I verify the freight amount, it is $250 for the order and $250 for the fulfillment document If I invoice both documents then customer is paying two times of the freight amount.
Is there any way to do the following in GP
1. Split the freight amount based on the allocated quantity while transferring? (Or)
2. Have the freight amount only on the transferred fulfillment document? And have 0 on the original document after it was partially transferred,.
Kindly advice.
Thank you inadvance.
Solomon
Hi Solomon,
GP behaves in the following way:
When you transfer an order to an invoice/fulfillment order there is a checkbox to Include Totals and Deposits. When this is checked, the entire deposit, freight amount, and any amounts other than the line items will come over to the invoice/fulfillment order in full the very first time that option is checked. If you transfer from the same order to the invoice/fulfillment order a second time, even if you check that box to Include Totals and Deposits again, nothing will come over to the second invoice/fulfillment order.
The freight amount is still showing on the order because that’s how it was entered, so this is informational only at this point. Since you may need to show a record of what was on the order originally, I would think that most users would not want the freight amount to be removed from the original transaction. Just like you want to still see the total ordered by the customer, even though you have partially shipped the order.
There is no way without a customization that GP would be able to do any kind of prorating/splitting up of the freight amount. And I would imagine this would be a pretty complicated customization, so I would not entertain this unless it was absolutely critical.
Hope this helps.
-Victoria
Hi Victoria,
I am facing a strange error during posting of cash receipt,
“An error occurred when updating permanent records”
“Transaction cannot be posted”
Client is running Ver 8.0.
there is nothing unusual happened after the week end, client post batches every week end.
i tried to delete and recreate few batches different customers and user ID, the error happens only in one company and to all customers and userid’s too,, i have tried sa as a posting user…
Any help on this will be of great help….
Thanks,
Akram.
Akram,
While I have not seen this error before, it sounds like there may be a problem with the database itself or with updating the database. I would verify that there is no issue on the SQL server, either with available disk space or any other errors/locks. If you have Payment Document Management enabled, disable it to see if that makes a difference. If there are any customizations or 3rd party products, disable them to see if that makes any difference. As a next step you can turn on the DEXSQL.log to see if there are any clues in there.
-Victoria
Hello Akram & Victoria,
I am having the exact same problem described by Akram and I am unable to post cash receipts.
After receiving this error when trying to post a cash receipt, the outstanding cutomer balance for the selected customer is increased by the amount of the attempted payment. So, if the customer balance was $5,000 and we attempted to enter a cash receipt for $2,000, the error message is displayed and the customer balance becomes $7,000.
The customer balance is only corrected again after i reconcile current customer information from the reconcile receivables amounts window.
Please advise on how you have resolved this problem as our systems are currently suspended due to this error and I need to resolve this as soon as possible.
Thanks & Regards,
Ramy
Hi Ramy,
I would re-iterate my advice to Akram: “If you have Payment Document Management enabled, disable it to see if that makes a difference. If there are any customizations or 3rd party products, disable them to see if that makes any difference. As a next step you can turn on the DEXSQL.log to see if there are any clues in there.”
Also, what version and service pack of GP are you on?
-Victoria
Hi Ramy/Victoria,
Here is the script given by Mr. Mariano Gomez, to run against that company where i was facing the same issue…
This resolved my problem….
Hope this helps you too……
You can try running the following script on your company database:
/*Count : 1 */
declare @cStatement varchar(255)
declare G_cursor CURSOR for select ‘grant select,update,insert,delete on [' + convert(varchar(64),name) + '] to DYNGRP’ from sysobjects
where (type = ‘U’ or type = ‘V’) and uid = 1
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
declare G_cursor CURSOR for select ‘grant execute on [' + convert(varchar(64),name) + '] to DYNGRP’ from sysobjects
where type = ‘P’
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
/* End of Script */
Take Care…
Akram,
Thanks for sharing this. This is the GRANT script which will ensure that all the objects in your database have proper permissions for the GP DYNGRP role in SQL Server. If it fixed your problem then there was a database permission issue in your situation.
-Victoria
Victoria
I am dealing with data being imported into Great Plains with SCRIBE. One area that is causing a problem is with Credit Card orders. The Credit Card orders are paid immediatly so the invoice is closed. But if later a Credit is put through to reverse the invoice there is no open invoice for it to go against. The reverse Credit is showing as a history item rather then as an open item. I hope this makes sense and that you can suggest a way to handle the credit on credit cards. Other credits (not against credit cards) work fine.
thanks
Larry
Hi Larry,
I do not have any experience with Scribe, so I cannot offer any advice for that, but as far as ‘vanilla’ GP is concerned, these would be entered as Returns with the credit card refund amount as a payment. Exactly the same as the invoices, except the transaction type is different.
-Victoria
Hi Victoria,
I wasn’t sure where to post this…under SOP or POP. I have a SOP type ‘Order’ that was linked to 2 Purchase Orders. All but one item was linked on the Order. I’m guessing when the order was created, the items weren’t in stock (except for the 1) so the QTY to Back Order = 1 and QTY to Deliver = 0. The Order was transferred to a Fulfillment Order/Invoice and the original Order remains open. Thus, an invoice was posted for just the one item and is now history. So, I have an open Order that is linked to a PO and the PO items have all been received and posted…as far as I can tell. The two POs show up in POP10100 and POP10110. In POP10100, the POSTATUS = 4 for both and the STATGRP = 1 for both. The two Receipts for these POs both show up in the history tables POP30300 and POP30310. Thus, I believe the Sales Order QTY to Back Order should = 0 and QTY to Deliver should = 1. However, this is not the case. Each time I try to change the value in one of these fields, I get the message “You can’t change the Billed Quantity because the quantities for this line item are committed to a purchase order”. We obviously want the link to remain intact, but what should we do next to allocate the received PO items to this Order so we can transfer it to a Fulfillment Order and Invoice? I’m open to all suggestions.
Thanks so much in advance for your help.
Brent
Hi Brent,
My next step would be to try reconciling the SOP transaction to see if that changes the quantities. I would also recommend posting your question on the Dynamics GP newsgroup, there are a lot of very knowledgeable people monitoring it and they might have more experience with this.
-Victoria
Victoria,
I ran the reconcile for this Sales Document and it fixed it! Thanks so much for the suggestion. As always, thank you very much for your time and assistance. It is greatly appreciated.
Brent
Hi Victoria,
I have one query, i have searched the knowledge base but could not find any article on this….
I would like to add new aging buckets in both Sales and Purchasing modules presently i am using only 4 buckets in each modules,
I would like now to use all 7 buckets….
Since i am middle of my year, how can i change the aging periods and what steps i have to perform so that all my aging reports are perfect.
I have collected all the articles related to adding new aging buckets in reports using report writer…
Can you please guide what steps should i follow to change aging periods in both modules.
Regards…..
Hi Akram,
Typically the only thing you need to do is change the setup for both the payables and receivables modules and then run reconcile on both those modules. Fairly straightforward, except that you need to get all the users out before running reconcile and it might take some time depending on the size of your data. It’s always a good idea to test changes like this in a test environment if you have one available.
-Victoria
On SOP10200, how/when are the qty fields populated? I’m trying to use report writer to customize a report and placing qtyonhnd into the report, but I keep getting 0. I did a quick query on the table, and sure enough SOP10200 is showing 0, even though I have many thousands of units in stock.
Mike,
The QTYONHND field in SOP10200 is used for Returns – when you enter a return quantity the system asks where that quantity should go, the choices are On Hand, Returned, In Use, In Service or Damaged. If you are looking for Inventory Quantities on hand, use the IV00102 (item quantity master) table.
-Victoria
I was wondering if somebody can helkp me with this. I need to create a SOP Invoice that shows subtotals by item type, and the the total documents amount.
I am not sure if I can accomplish that in Reports writer by adding a section by doc type, but can try to do in Crystal.
any suggestions?
Thanks
Thargelia,
I am not sure you can accomplish something like this in Report Writer. And frankly, if you can, I am not the right person to help with that, as I try to avoid Report Writer for anything except the simplest or cosmetic changes. I can absolutely tell you that this is possible using Crystal Reports, although depending on the complexity of your logic you may want to create a SQL stored procedure to use in Crystal. If you have some specific questions, please post back with as much detail as possible. If you’re looking for someone to create this for you, this is something my company offers on a consulting basis.
-Victoria
Victoria,
This is an absolutely fabulous collection of information!
Leslie
I am using odbc to integrate a shipping system with GP database. We are reading the SOP10100 table to get the ship to address. And we are writing back the shipment tracking number to the SOP10107 table. All works fine. But we want to delete the record in the SOP10107 table if the shipment is voided. We have not been able to delete the record. I have 2 questions.
Is Deleting the record a sound process, or is there a better approach?
The delete sql statement worked when using a db tool, but not in the program. Is there anything that I might be overlooking? (commit statements?, time between insert and delete?) We are stumped.
David,
From an application/logic standpoint, I don’t see any problem with deleting the tracking number out of SOP10107 if the shipment is voided. However, since I don’t code this stuff myself, I am really not the best person to help with why it’s not working for you. Perhaps you can post this in one of the GP newsgroups to see if anyone there can help you?
-Victoria
Thank you for the quick reply. I’ll follow your suggestion.
William,
There is really no other natural link between SOP and UPR tables that I can think of. If there are no SLPRSNID values set up, then you would probably need to find out from someone in your company that does the invoicing how you can tell what employee a particular invoice ‘belongs’ to. Write back with what they tell you and we’ll see if we can come up with something.
-Victoria
Thanks! I would not have been able to come up with that one. Although my SLPRSNID seems to be empty in both SOP30300 and RM00301. Might you know of an alternate method? Thanks so much!!!
- W
I am trying to build a report that would show one column with the the rate, hours, and total billed vs the rate, hours, and total cost. The attempt is to see the profit made. Thanks!
- W
William,
So on each line of an SOP invoice you have a Salesperson ID that has an Employee ID set up on the Salesperson Maintenance window? If so, you’d want to use the SOP30300 table for the line item detail and link on the Salesperson ID (SLPRSNID) to the RM00301 table to get the Employee ID (EMPLOYID). Once you have the Employee ID, you can link on that to the UPR tables that you need.
Hope that helps.
-Victoria
Hey Victoria!
Your information is very helpful. Do you know of a way to link SOP tables with UPR tables? I can not seem to figure out how to do so. Thanks for your help!
- W
Hi William,
What specifically would you be looking to link? The salesperson? It sometimes helps to understand what the end goal is when trying to answer questions like this.
-Victoria
Hi, Victoria,
Thank you for a wonderful site, a lot of helpful information!
I am trying to join sales order (or invoice) to project id assigned to customer (pc… tables). Can you advise what columns I should use?
Thank you,
Julia
Hi Julia,
I am not familiar with ‘PC’ tables…is that a 3rd party product? I believe GP’s Project Accounting tables start with ‘PA’… Typically, when we need to report against tables in a 3rd party product we either ask them for a technical manual/reference or use the System Resources in GP to get a list of all the tables.
-Victoria
Thanks Victoria! I’ll follow your suggestions.
Hector
Hi,
I have a Sales Order that has a document status as posted, but its posting status is unposted, and the batch id is still in OPEN. When I open it through SmartList > Sales > Sales Transactions, it opens in read only mode because its document status is posted (or completed).
This is an order that hasn’t been fulfilled, and since it appears as posted when we open it through SmartList, we don’t know how to do the invoicing.
any ideas?
Thanks,
Hector
Hector,
It sounds like something has gone wrong with this sales order, but it is very difficult for me to give advice on this situation without looking at your data. At the very least I would suggest running Check Links on the Sales series and Reconcile on Sales Documents to see if either of those helps. If the order is still not appearing correctly, you may need to re-enter it to be able to fulfill and invoice it. For support in these types of situations I would look to your GP partner or GP Support, where they can look at your data and be better able to help you.
-Victoria
Thank you, I got it!
It does help! But I need more help.
My impression is that SOP10103 holds payments made at the time of purchase. When a SOP Invoice is charged to a customer’s account where can I find the payment records (Payment Date, Payment Type, Check Number, Credit Card Type, Amount)?
Christian
Christian,
SOP10103 holds payments entered directly on SOP transactions. This is only valuable to you for unposted SOP transactions (those in SOP10100), since they will not be in the RM tables yet.
Once SOP transactions are posted, they go to RM20101. Once paid and moved to history (via a manual process in GP) they will move to RM30101. Payments will also be stored in RM20101 and eventually RM30101, so either RM20101 or RM30101 will hold payment date, payment type, check number, and credit card. The apply ‘links’ between invoices and payments showing the amount applied from a payment to an invoice and the apply dates will be in RM20201 and RM30201. The payment will be the ‘apply from’ transaction and the invoice will be the ‘apply to’ transaction.
-Victoria
Victoria,
Ah, such a helpful tidbit to Christian, and a great site in general. I learned what’s in a bunch of the SOP/RM tables from a few days of spelunking GP tables with the aid of the scant documentation in Feb. of this year.
Our RM30101 table is empty. Is this normal? Are we not periodically doing a manual process that we ought to be? RM20101 is where the bulk of our transactions live.
John
John,
If your RM30101 table is empty, I believe there may be 2 reasons: either you’re not set up to track any history for receivables transactions or you have never run the manual process to move paid receivables transactions to history (Tools > Routines > Sales > Paid Transaction Removal). Before running this process the first time, make sure you’re set to keep track of history, it would be very difficult to get it back once deleted.
-Victoria
Thanks for the advice!
I typed that (CM20300) from memory and… well so much for my memory.
I meant to reference SOP10301 where I am getting Payment Type, Check Number, Card Name, & Amount.
I am looking to report on all SOP Invoices (Posted & Unposted).
However, I will also report on posted SOP Invoices by SOP10100.PSTGSTUS = 2. I will exclude all voids by SOP10100.VOIDSTTS = 0 for both.
Christian
Christian,
SOP10100 only holds unposted transactions and as soon as something is voided in SOP it moves to the historical tables. So no need to check for posting status or voids in SOP10100. You might want to take the approach of first creating 2 separate queries or views. One for all posted invoices, which will only require RM tables, since every posted SOP transaction will have a corresponding record in RM. Then separately, use SOP10100 and SOP10103 for unposted transactions. Once you’ve got that put together, you could look to combine them (if needed). Hope this helps get you started.
-Victoria
Victoria,
I have enjoyed reading your posts for a while now (They have been a great help) and now have a situation I cannot seem to resolve.
I am trying to create a SQL view that contains SOPNUMBR, amounts received, payment type (check, cash, credit card), payment date, & Card Type.
I have created unions on SOP10100 & sOP30200, RM20201 & 30201. Those tables along with CM20300 are what I think I need.
How can I get a view that shows me all payments relevant to a particular SOP transaction along with the payment information.
Thanks for your help in advance!
Christian
Christian,
Thanks for the kind words!
Question for you – are you looking to report on payments entered against unposted SOP invoices? Or posted only? Also, what information are you getting from the CM20300 table? Just wondering why it’s included on your list?
-Victoria
Hi Victoria,
I was creating a new company with new set of database tables using Greatplains 8.0 utilities.
Then I used the new database as the initial catalog in the sample eConnect application provided by eConnect8.0.
But it thrown an exception as
“System.Data.SqlClient.SqlException: Could not find stored procedure ‘taInitialize’ “.
I also could not locate this stored procedure in my new created company database, though it is there for the default Fabricam, Inc. database.
The exception is throwing in the following line.
eConCall.eConnect_EntryPoint(sConnectionString,
EnumTypes.ConnectionStringType.SqlClient, salesOrderDocument,
EnumTypes.SchemaValidationType.None,”");
Please help me on this.
Thanks
PD
PD,
Sorry, I do not work with eConnect myself, so I am not a good resource for this. I would recommend posting this to the GP Developer Newsgroup or starting a support incident with GP Support.
-Victoria
hi Victoria, thanks for the above helpful informations. Can you suggest me what all tables I need to use to import minimum Order data from an external file to GP 8.0 tables.
Thank you.
PD,
I would strongly advise against importing SOP transactions directly into tables. There are existing tools, such as Integration Manager and eConnect that greatly reduce the amount of work needed and possible issues encountered when importing directly into tables. That said, I believe the basic information for an order would need to be imported into the SOP10100 and SOP10200 tables.
-Victoria
Victoria,
Thank you so much!!!!!
Best Regards,
Nikki
Victoria,
Thanks so much for the awesome resources. Your given list of tables & details across various modules are definitely great help.
I’m trying to get a list of posted oustanding invoices from GP based on the aging period. Instead of joining various tables like (SOP30200 & RM20101, etc.) based on Due Date, is there a table with ready calculation just like the Collection Management Window?
Your help is greatly appreciated.
Nikki,
Thanks for the kind words!
If you wanted to get a current total balance for each customer, you could use the RM00103 table, which has the balance and a total for each aging bucket. One caveat with using this table – you need to run the Aging routine in GP for the buckets to be calculated correctly. If you need to show each transaction, then you have to use the RM20101 table. Make sure you’re excluding voids. And there is no need to use the SOP tables….anything posted in SOP will be in the RM tables. Hope that helps.
-Victoria
Victoria,
I am attempting to create a report that would show Revenue broken out into two fields: Commercial and Residential. Since SBF does not have a job costing feature, I tried to enter either Commercial or Residential in the User Defined Field #1 in the customer records under the Customer button. However, I can not seem to link the two together. Am I doing something wrong? Do you have any ideas on how to do this, or possibly an alternate? Thanks for any advice you might have.
- William
William,
The user defined field values are stored in the RM00101 (Customer Master) table. The field for User Defined 1 is USERDEF1. Whatever report you’re creating, you will need to link to the RM00101 table on Customer ID (CUSTNMBR) and then group your report by the USERDEF1 field. Hope that helps.
-Victoria
You’re welcome for the help! I would have posted in the original conversation in the newsgroup, but somehow MS messed up my profile and posted the message so it only showed up in Google Groups and not in the Microsoft Discussions.
Thanks for replying and letting me know that I wasn’t going crazy either!
Vanessa,
There is no other way that GP internally supports to link the SOP and POP modules. Is it possible that your company is doing something different? Maybe there is a customization or they are entering the PO number in a user defined field on the Sales Order? Can you check with the users that are actually performing this linking? That may help track down exactly what they’re doing.
-Victoria
Yes, that is what I mean, but is there any other way to link them besides the SOP-POP Link table? I am new to GP, I have found that my company does not have the SOP60100 table current, so the latest data that I see on this table is back from last year. I need the new data.
Vanessa,
Do you mean your Purchase Orders that you’ve linked to your Sales Orders? If so, the SOP-POP Link table is SOP60100. You will need to link to the orders on the following:
SOPNUMBE
SOPTYPE
LNITMSEQ
-Victoria
how can I link the SOP number to the PO number associated with the sales order?
Don,
Deposits are not line item specific. They are entered against the entire order or invoice. If there are multiple deposits on one SOP transaction (for example, a $500 cash deposit and a $1000 credit card deposit) then, and only then, will there be multiple entries in the SOP10103 table with the same SOPNUMBE and SOPTYPE combination and you’ll see that the SEQNUMBR has different values.
So, the answer is no, you do not include SEQNUMBR in the join. However, if you have multiple deposits against an order, you will get multiple lines in your results with the same order number, so you need to make sure you’re taking that into account in whatever grouping or totaling you’re doing.
-Victoria
Joining SOP10103
I am trying to track deposits made (amounts paid) for SOP orders. Does AMNTPAID apply to Line Items? Then I would join like this:
LEFT OUTER
JOIN SOP10103 (nolock)
ON SOP10200.SOPTYPE = SOP10103.SOPTYPE
AND SOP10200.SOPNUMBE = SOP10103.SOPNUMBE
QUESTION: Shouldn’t I include a sequence number (SEQNUMBR) in the join?
Don,
The original order (SOP type 2) always stays in the system. Once it’s completed (all line items fully transferred or cancelled or voided), the order moves from the SOP10100 and SOP10200 (work) tables to the SOP30200 and SOP30300 (history) tables. All the other data for the order (comments, lots, serial numbers, user-defined fields, etc.), stay in the same tables. For example – SOP10201 holds the lot and serial numbers for both work and history transactions. For the Invoice, a new transaction is created in the SOP10100 and SOP10200 tables with an SOP type of 3 and showing that the original type (ORIGTYPE) was 2, meaning Order, and what the order number was (ORIGNUMB) so it can be traced back.
–Victoria
I enjoy your SOP table layout, Victoria, and have been using it to try to track the flow of data as I process orders. Can you please tell me what tables are affected when I use the Sales TRX Entry window to transfer an order to be invoiced? Does SOPTYPE change from 2 to 3?
Regards, Don Cooper