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  - 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.


Last Updated: May 04, 2011

463 Responses to “SOP Tables”

  1. 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.

    Like this

    • 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

      Like this

  2. 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

    Like this

    • Julien,

      Are you using Sales Workflow in GP? What is your definition of ‘Active orders’?

      -Victoria

      Like this

      • 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

        Like this

        • 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

          Like this

        • 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.

          Like this

  3. 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

    Like this

    • 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

      Like this

  4. is there a way you can see deleted transactions in SOP?

    Like this

    • 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:

      • Void transactions instead of deleting them – all voided transactions are stored in the same tables as regular transactions.
      • Create custom SQL triggers and tables that will store deleted transaction information.
      • Look into an IVS product that can track these for you.

      -Victoria

      Like this

  5. 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

    Like this

    • 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

      Like this

  6. Hi Victoria,

    I want to see the comments for each line item for all posted invoices. What table should I be looking at?

    Like this

  7. 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

    Like this

    • 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

      Like this

  8. 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?

    Like this

    • 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

      Like this

  9. Hi Victoria, Thank you so much.

    Like this

  10. 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

    Like this

    • 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

      Like this

  11. 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

    Like this

    • 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

      Like this

      • 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.

        Like this

        • 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

          Like this

          • 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.

            Like this

            • 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

              Like this

  12. 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

    Like this

    • 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

      Like this

  13. 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

    Like this

    • 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

      Like this

      • 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

        Like this

        • 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

          Like this

          • 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

            Like this

  14. 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,

    Like this

    • Haitao,

      Look at SOP10100, not SOP10200. SOP10100 and SOP30200 are the header tables. SOP10200 and SOP30300 are the detail tables.

      -Victoria

      Like this

    • 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.

      Like this

  15. Is there a field on the SOP tables that track that an Item has Override Quantities? I need to know that information. Thanks.

    Like this

  16. What’s the difference between DOCDATE and INVODATE?

    Like this

  17. 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

    Like this

  18. 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!

    Like this

    • 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

      Like this

  19. 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?

    Like this

    • 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

      Like this

      • 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.

        Like this

        • 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.

          Like this

    • 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.

      Like this

    • 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.

      Like this

  20. Sorry,
    GL00100 and GL20000 (which i have joined by ACTINDX)

    Like this

    • 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

      Like this

      • 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!

        Like this

  21. 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.

    Like this

  22. 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?

    Like this

    • 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

      Like this

      • 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.

        Like this

        • 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

          Like this

          • Well, shoot. Thank you for the replies!

            Like this

            • 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.

              Like this

              • 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

                Like this

  23. 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?

    Like this

    • 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

      Like this

      • 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

        Like this

        • 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

          Like this

          • 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.

            Like this

          • 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?

            Like this

  24. Carleesha Silvera Reply June 13, 2013 at 10:55 am

    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?

    Like this

    • 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

      Like this

  25. 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)?

    Like this

    • 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

      Like this

      • 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?

        Like this

        • 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

          Like this

          • 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

            Like this

            • 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

              Like this

      • 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!

        Like this

  26. 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

    Like this

  27. 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

    Like this

    • 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:

      1. Customize the Sales Transaction Entry window to calculate the subtotal and document amounts differently, based on the logic that you want. I would not expect this to be a trivial customization since there is so much stuff going on in SOP and on that window in particular, expecially if you have 3rd party products involved. But this would give your customer exactly what they want.
      2. Change the process so that the quantity is actually updated, instead of using the cancelled quantity, but then provide some other way of capturing and reporting on the cancelled quantities – maybe using Extender or another customization? I would expect this to be easier to do than the 1st option, but it might also involve changing/customizing whatever reporting is already in place looking at the cancelled quantities.

      Hope that helps,
      -Victoria

      Like this

      • 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

        Like this

  28. 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!)

    Like this

    • 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

      Like this

  29. 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

    Like this

  30. 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!!

    Like this

    • 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

      Like this

  31. 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.

    Like this

    • 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

      Like this

      • 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.

        Like this

        • 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

          Like this

  32. 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!

    Like this

    • 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

      Like this

  33. 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!

    Like this

    • CindyLu,

      If that field is what it sounds like (User Defined 1) then it should be going to the USERDEF1 field in SOP10106.

      -Victoria

      Like this

      • 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!

        Like this

        • 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

          Like this

  34. 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.

    Like this

  35. 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

    Like this

    • 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

      Like this

  36. 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

    Like this

    • 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

      Like this

      • - Victoria

        Even if the batch is posted can it be deleted in GP? This batch has about 1200 invoices in it.

        - Steven

        Like this

        • 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

          Like this

  37. 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.

    Like this

  38. 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?

    Like this

    • 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

      Like this

  39. 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.

    Like this

  40. 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?

    Like this

    • 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

      Like this

  41. 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

    Like this

  42. Hi Victoria,
    Is it possible to find who deleted an order in GP?

    Like this

  43. Hi Victoria, can we disable or password protect the customer name field in sales transaction entry in GP 10. Thanks.

    Like this

  44. Do you know where Shipping Methods are stored in the database?

    Like this

  45. 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.

    Like this

    • 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

      Like this

    • 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

      Like this

      • 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

        Like this

  46. 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

    Like this

  47. Hi victoria,
    Kindly Thanks for your response.. I wil check it out and update you…

    Like this

  48. 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.

    Like this

    • 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

      Like this

      • 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

        Like this

        • 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

          Like this

  49. 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

    Like this

    • 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

      Like this

  50. 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

    Like this

  51. 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

    Like this

    • 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

      Like this

      • 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.

        Like this

      • 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.

        Like this

  52. 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

    Like this

    • 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

      Like this

  53. 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.

    Like this

    • 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:

      1. On the Document Type ID Setup window – this allows you to have individual numbering schemes for each Document Type ID, so, for example, you can have invoices with multiple numbering schemes at the same time. To get to these, navigate to GP | Tools | Setup | Sales | Sales Order Processing, then click on the Sales Document Setup button and pick the appropriate document type. On the next window, select the ID you are looking to make a change for. If nothing is in the ‘…Next Number’ field at the top, then you are using the global numbering schemes for this (see the next option below).
      2. On the global Sales Order Processing Numbers Setup window. (This is used if nothing is specified for the Document Type ID.) To navigate to this, go to GP | Tools | Setup | Sales | Sales Order Processing, then click on the Numbers button.

      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

      Like this

  54. 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.

    Like this

    • 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

      Like this

      • 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

        Like this

        • 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

          Like this

  55. 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

    Like this

    • 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

      Like this

  56. 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

    Like this

    • 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

      Like this

  57. 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

    Like this

    • 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

      Like this

  58. 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.

    Like this

    • 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

      Like this

  59. 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

    Like this

    • 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

      Like this

  60. 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

    Like this

    • Hi Scot,

      From what you’re describing a couple of thoughts come to mind:

      • If the only objective is to print individual packing slips per ship to address, why not create all the ‘customers’ as ship to addresses under one customer (Groupon), put the appropriate ship to address on each sales order line and then make sure your packing slips print one report per ship to address on the line item level? This may actually be the default behavior in GP, you’d just need to double check this.
      • There is a product from Trinity called consolidated invoicing that may be able to help: http://www.trinitypartner.com/products_and_modules/consolidated_invoicing.htm

      -Victoria

      Like this

  61. 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

    Like this

    • 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

      Like this

      • 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

        Like this

        • 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

          Like this

  62. 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?

    Like this

    • 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

      Like this

  63. 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.

    Like this

  64. 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

    Like this

    • 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

      Like this

  65. 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.

    Like this

  66. 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

    Like this

    • 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

      Like this

  67. 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.

    Like this

    • 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

      Like this

  68. 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

    Like this

  69. 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

    Like this

    • 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

      Like this

  70. 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

    Like this

    • 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

      Like this

      • 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

        Like this

        • 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

          Like this

          • 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.

            Like this

            • 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

              Like this

  71. Thank you so much Victoria, I have just posted the question on GP Community Forum.

    Like this

  72. 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.

    Like this

  73. 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.

    Like this

  74. 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!

    Like this

    • 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

      Like this

  75. 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

    Like this

    • 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:

      1. Change the number of decimal places for your items to more than 2.
      2. Change the unit of measure schedule to accommodate your pricing. For example, if you’re currently setting a quantity of 1000 each at 53.55 per 1000 (where each is unit of measure), you could instead sell a quantity of 1 thousand at 53.55 (where thousand the unit of measure and thousand = 1000 eaches).

      -Victoria

      Like this

  76. 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

    Like this

  77. 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.

    Like this

  78. 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

    Like this

    • 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

      Like this

  79. 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!

    Like this

  80. Victoria,

    I have a user with a SOP Batch that he is trying to edit before posting but gets message “Your previous transaction level posting session has not finished processing…..”.
    I have cleaned up the SY00800 table, DEX_LOCK, DEX_SESSION, ran Check Links, restarted user workstation, restarted SQL server and term server GP resides on but as soon as user attempts to get back into batch, same error appears. Any suggestions on how to fix this or if you have come across this situation before.

    Thanks

    Like this

  81. Hi Victoria,

    Such great info, thanks so much for posting!

    I’ve been looking around for an answer to this, and I guess I’m posting here to see if you or your following community might have a solution:

    I’ve added a trigger to SOP10200 to automatically add comment lines to sales line items. It works great, except when I convert to invoice from sales order, the line items disappear. The total price is still there, but the line items display 0 cost, with no item number.

    Any Ideas?

    Like this

  82. Hi Victoria,
    Thank you so much.

    Like this

  83. Hi Victoria,
    Thanks again. We have no issues with invoices because we realized and fixed the freight errors before posting the invoices, however, we could not catch it before transferring the orders to invoices and as a result the freight and all other relevant data went wrong for the orders (sop type =2) in historical table. From accounting point of view we are fine, however , we want to keep the data correct for the orders as some of our customer order status reports depend on historical data for sales orders

    Like this

  84. Hi Victoria,
    Thanks for your prompt response. Kindly advsie which GP user interface should be used to edit the freights for historical orders (SOP300200).

    Like this

    • Zafar,

      Sorry, I did not realize that this was historical. There is no way to do this and I would, again, not recommend changing it directly in SQL without a lot of testing, not only in the directly related SOP tables, but also in the GL, RM and any other modules or 3rd party products that may be integrating with SOP. To do this in the user interface, you could either return the original invoice and re-enter it, or enter a separate invoice for the freight.

      -Victoria

      Like this

  85. Hi Victoria,
    Is it safe to manually change the FRTAMNT and all other correponding data such as FRTTXAMT, DOCAMNT,……………… for some sales orders in SOP30200. We relaized the error in freights after transferring the orders to invoices and have already corrected the freight amount in invoices before posting.

    Like this

    • Hi Zafar,

      I would not recommend making this kind of change directly in SQL without a HUGE amount of testing. Unless you have thousands of records that needed this, I would probably do this manually in the GP user interface, or maybe even with a macro. Just my 2 cents.

      -Victoria

      Like this

  86. Victoria,
    I am trying to write a report that has line item with cancel dates. Looking at SOP10100, SOP10200, SOP30200 and SOP30300, I cannot see a specific date call out for cancels. The only date fields I can find (per line) are CONTSTARTDTE, CONTENDDTE, ReqShipDate, FUFILDAT, ACTLSHIP and I know that the last 3 are not the dates I am looking for. Any help? Is there another table I can join to get that date?

    Like this

    • Andrew,

      I am doing this from memory, but I don’t believe that GP tracks cancel dates for line items, so I am not sure you would be able to report on this without first creating something to track the cancel dates.

      -Victoria

      Like this

  87. Hi Victoria
    I got a big big problem, the users have posted 46 thousand SOP transactions with a wrong tax schedule id (TAXSCHID), what would you do to fix that? make returns to void those sop transactions would not be something fast, in this company post 1 thousand sop transactions takes about a couple hours, and there is no time to post all 46 thousand, would you make updates (via sql) to amount fields in GL, IV, RM and SOP tables (I have identified all tables that involved those SOP Trx) ?
    What would you recommend me to do please

    Thanks a lot

    Like this

    • FAC,

      The fix would depend on what specifically you need as far as reporting, etc, in the future. It may also depend on whether or not you charged the incorrect tax, as you would then need to potentially refund or charge additional tax to your customers. I would recommend discussing this in some detail with your accountants and/or your GP partner and getting advice specific to your situation. The reason for this is that you may face some penalties for incorrect tax reporting and it may be important to get this done right as opposed to quick. It may turn out that the only true fix is to enter returns and re-enter the transactions correctly or you may be able to enter GL tax corrections only. I would generally not recommend making these changes directly in SQL as that may make matters even worse, but again, in your specific situation this may be OK.

      -Victoria

      Like this

  88. Hi Victoria,
    Is there a smartList which can give all the information available through Customer Yearly Summary Inquiry Window?

    Like this

    • Hi Victoria,
      It seems we can get all the info from Customer Collection Management Aging Notes. Kindly advise/confirm.

      Like this

      • Hi Victoria,
        I tried to create the smartList however it is not giving correct data. For instance, it is showing 0 for all Last year values. In addition, it is showing same numbers for year to date values and the corresponding Life to date values. Also Can you please advise which GP table/s can have these information.

        Like this

        • Hi Zafar,

          Consider that on the Customer Yearly Summary Inquiry window you can enter a year (and optionally a range of periods) and calculate the totals. If you want to show totals for any given year in your SmartList, you will need to calculate that yourself, no GP table will store that. You can also show fiscal or calendar years (in case they are different in your GP company). I have created similar reports/SmartLists in the past – the tables I used were RM00101, RM00103 and RM00104.

          -Victoria

          Like this

      • Zafar, I would be really surprised if this was the case. What you’re looking for will be in RM tables.

        -Victoria

        Like this

    • Zafar,

      There is no out-of-the-box SmartList that can do this.

      -Victoria

      Like this

  89. Hi Victoria

    I am searching for the table in which the column “SOP HDR Errors 2″ exists. This column could be found in smartlist for GP ver 9.0 under Sales Line Items.

    You help is appreciated.
    Regards
    - Jubith

    Like this

  90. Victoria,
    Thanks so much for your awesome field references. I searched all over for the SOPSTATUS field values and this is the only source I’ve found. They’re not in the Resource Descriptions for the SOP10100 or SOP30200. You rock!
    Constance

    Like this

  91. Hi Victora

    We have an order that is in our sop30 series tables that no invoice got created for. We have apayment we cannot post. Any ideas on how we might create an invoice?

    Thanks
    Vic

    Like this

    • Vic,

      I have seen this happen when the order is transferred to an invoice, but the invoice gets deleted or voided for some reason. If you are positive the invoice is nowhere to be found, you can just manually enter one. Keep an eye on inventory quantity issues that may be caused by this.

      Also, you can enter and post a payment (Cash Receipt) for a customer whether you have an invoice to apply it to or not – there is no rule (at least in GP) that requires the invoice to be entered first. So you can enter the payment today, deposit it to the bank, then enter the invoice tomorrow, then apply the payment to the invoice as a separate step.

      -Victoria

      Like this

      • Victoria

        thanks again for the quick and easy solution!!!

        Vic

        Like this

      • Hi Victoria

        How would you go about filtering out voided Invoices after they are posted. The void status in the sop still shows as a 0 when a that invoice gets voided.

        Thanks
        Vic

        Like this

        • Hi Vic,

          I guess that means that you are entering and posting invoices in SOP, but voiding them in Receivables? And clicking through the message that warns that the invoice did not originate in Receivables? :-) If so, that is not typically recommended and what you’re running into is just one consequence of this. If you are doing this with full knowledge of the implications, then the only way I can think of to filter these out of reports is to link to the invoices in the RM tables (RM20101 and RM30101) and to check their status.

          -Victoria

          Like this

          • Victoria

            Our Var was not very together so we had to figure out how to void a posted invoice and this is the only way we could find. Can you point us in the right direction on how to void a posted invoice that was created in SOP

            thanks as always

            Vic

            Like this

            • Vic,

              There is no way to void a posted invoice in SOP. If you are not tracking inventory quantities/sales, etc., then it is typically ok to void SOP invoices in RM. (Except you run into reporting issues like you’re seeing and you need to work around them.) However, if you are actually tracking inventory, then the best way to ‘void’ an invoice is to create a return transaction that mirrors it and apply that return to the invoice. This correctly puts inventory back and adjusts the customer’s account. You do need to apply the return to the invoice as a separate step after posting the return. Here is a blog post by Christina Phillips about this: http://dynamicsgpland.blogspot.com/2009/11/dance-with-one-who-brung-ya-correcting.html.

              -Victoria

              Like this

  92. Hi I am looking to store some info in the sop10200 and am looking for extra fields. In the SDK documentation it says that sop10200.ItemCode is used to store the UPC code but on another site I see a post saying that is for future use. Any idea if it’s extra or by setting it I won’t cause the world to fall apart?

    Thanks

    Aryeh

    Like this

    • Aryeh,

      There are a few fields like this throughout GP. I do not think the world will fall apart if you start using them, but you might run into trouble if Microsoft decides to start using that field in a future release/service pack. What kind of trouble is hard to say and it may be years, if ever, before this is an issue. So you are probably ok to use it for now, but you need to check future updates and upgrades before installing them to make sure that they will not cause an issue with this.

      -Victoria

      Like this

  93. Hi Victoria

    We closed late and I ran the fix for that from Microsoft. Now I am being told some of my reports do not tie out to the GL. Do you have a view that can compare the data in RM00104 and the data in the SOP30 series tables by period by customer.

    Thanks
    Vic

    Like this

    • Hi Vic,

      There are many reasons why the RM00104 table would not show the same totals as the GL (and SOP):

      1. RM00104 will include data from more than just SOP. When SOP transactions are posted they update RM (Receivables Management). However when RM transactions are posted they do not go back to SOP. So if you are trying to reconcile numbers in RM00104, I would recommend looking at RM tables. Maybe try my view for all posted RM transactions: http://victoriayudin.com/2009/04/24/sql-view-with-all-posted-receivables-transactions/.
      2. Something that many people do not know – the ‘sales total’ tracked and summarized for sales transactions is determined by the settings on the Receivables Setup Options window (GP | Tools | Setup | Sales | Receivables | Options). If all the checkboxes at the bottom of that screen are checked, then all of those amounts are being added into the SMRYSALS amount.
      3. The RM00104 table is using Document Dates from RM. The GL is using GL posting dates from RM. It is entirely possible that these two are in different periods.

      So…it is entirely plausible and not necessarily wrong that the RM00104 data would not match the GL. And I didn’t even mention any adjustments done outside of the RM module.

      -Victoria

      Like this

      • Victoria

        THANK YOU!!!!! I have been trying explain to my CFO the fact that sales and GL are not going to tie since last JULY!!! Every month he calls me into his office and says MY numbers are off and asks why they don’t “tie” to the GL and EVERY MONTH I explain EXACTLY what you said. Maybe now he will stop asking.(If I print your reply and staple it to his forehead would that be wrong????) ;-)

        Thanks again so much

        Vic

        Like this

  94. Victoria,

    I want to know the last invoice date for different customers who are taking particular item . Please give guidance for writing query

    Like this

  95. Hi Victoria,

    I am having problem when using Integration Manager to upload the sales transaction from txt file which also contains ship-to-address but it gives me error “Address ID does not exist”.
    Is there any way to force IM create new record if it does not exist?

    Regards,
    Lukman

    Like this

  96. Hi Victoria,
    I have a problem when I’m posting a SOP batch it will give error in edit list.
    ” **one or more line items contain warning or errors.

    Is it will be resolved with reconciliation or not?
    or any other solution.

    thanks in advance.
    jamil shah afridi

    Like this

    • Jamil,

      Hard to say if Reconcile will fix the error until we know what the error actually is. Read through the edit list – typically there will be an error message right above the line with the issue.

      -Victoria

      Like this

  97. hi Victoria,

    do you happen to know if there was a manufacturing transaction flow type of document aside from the SDK document for GP2010 or GP10? Thank you.

    Like this

  98. Victoria,
    Have a user who lost connection to GP in the middle of an invoice without all fields being populated. After restablishing connectivity try pull invoice up and message said it is already posted. Looked in the SOP10100 table, both PSTGSTUS and SOPSTATUS = 0, also tried reconciling item as well as check link but with no luck in resolving. Also looked in SOP30200 & SOP30300 but it’s not in either of these tables. Any help would be greatly appreciated.

    Regards,
    Raul

    Like this

    • Hi Raul,

      If you are positive that the invoice is not in the SOP30200 and SOP30300 tables, the easiest thing to do may be to delete the invoice from the SOP10100 and SOP10200 tables and then run check links. This will clear out the invoice from all other related tables and the user can re-enter it from scratch.

      -Victoria

      Like this

      • Victoria,

        Thanks, worked like a charm! Once I deleted, ran check links then user was able to start from scratch, worked perfectly. Great site you have here, helpful in so many ways. Keep up the great work!

        Regards,
        Raul

        Like this

  99. Hi Victoria,
    I am trying to created a report of all the outstanding payments on SOP invoices?

    I have been looking at the sop30200([DEPRECVD]=0) and sop10103 to find the invoices which don’t have a payment.
    Is this the right way to get it? is there a simpler way to get this information based on the RM tables?

    Could you point me in the right direction?

    Thanks for your help.
    Aaron

    Like this

  100. Victoria –

    I’m hoping you can help answer a question regarding SOP and MO linking.

    In GP 9.0, there is a link between the SOP Number to Manufacturing Order to Picklist using SOP10200 -> IS010001 -> PK010033. However, when a manufacturing order is closed and the Sales Order posted, the reference in IS010001 is removed, breaking the link between the SOP document and the Picklist.

    This is preventing us from referencing exactly what components were consumed when researching historical documents (there is no link between SOP30300 -> IS010001). Are you aware of any method of either preserving that link, or pulling the configured Picklist through another method?

    Like this

  101. Victoria,

    Let me start by thanking you for providing an excellent resource for GP information.

    I’m running GP V9.0 and I found a disturbing problem today. We’ve written COGS reports based on invoice line items UNIT COST found in SOP30300. However, we found that the original order in the same table doesn’t always have the same unit cost as the invoice. Can you explain what could cause the difference?

    Like this

    • Hi Fred,

      If I am understanding your question, you have costs on an invoice that are different from the price you paid on the receipt of inventory? If that is the case, here is one scenario where that might happen using FIFO inventory valuation:

      1. You receive qty 10 of Item A at $38 each.
      2. You enter a Sales Order for qty 50 of Item A.
      3. Since you only have 10 in stock and since you can get a better price for ordering 50 from the vendor, you place an order for qty 50 of Item A at $32 each.
      4. You receive qty 50 of Item A at $32 each (the 10 you received earlier is still in stock, so you have a total of 60).
      5. You fulfill the qty of 50 on the Sales Order, transfer it to an Invoice and post the Invoice.
      6. Since you are using FIFO, GP ‘fulfills’ this order with the 10 items you already had in stock, at a cost of $38 each and 40 items from the new receipt, at $32 each. Total cost of the items = $1660 (10×38 + 40×32). The per item cost becomes $1660/50 = $33.20. If you look at the GP invoice in SOP30300 you will see an item cost of $33.20.

      Not sure if this is exactly what you’re seeing, but hope that helps explain how such a discrepancy might happen.

      -Victoria

      Like this

  102. Victoria -

    I’m hoping you can help me resolve an issue. I have a client who has a document in the SOP10100 table with a PSTGSTUS of 14 and an SOPSTATUS of 0. When they attempt to pull the document up in Sales Transaction Entry a message displays saying the document number already exists. It was at one time associated with a batch ID, but is now longer. I could use some guidance in resolving this.

    What would happen if I changed the PSTGSTUS? I ran Check LInks but it didn’t fix the issue.

    Any help would be appreciated.

    Thanks,
    Sean

    Like this

    • Sean,

      When I see the message that the document number already exists, most of the time tat means the transaction is already posted and just didn’t get cleared out of the unposted tables. My fist step would be to check the SOP30200 and SOP30300 tables to see if it is there and if all the fields are correct. If so, you can delete the doc out of SOP10100 and SOP10200 and run check links.

      -Victoria

      Like this

      • Victoria -

        Many thanks! The solution was perfect and I’m embarrassed to admit something I should have known to look for.

        Thanks for the help!

        Sean

        Like this

  103. We recently had an order with a Check Deposit transfer to invoice and drop the check deposit from the invoice. The deposit is still listed in sop10103 for the order but there is no entry for the invoice in sop10103. The problem arises that we want to void the invoice and are unable to as ‘You cannot void a document that contains posted deposits.’” How should we go about voiding this order, and making sure the check does not end up being reflected in our GL.

    Like this

    • Hi Josh,

      This is actually one of the reasons that some companies do not like to enter deposits on orders. At this point, you may need to post the invoice and enter a return for it. If the deposit hits the customer account and/or GL, you will need to void or reverse it. Let me know if you need more help on this, and if so, what GP version you’re on. I would need to test out this exact scenario to see what happens to the payment and how to best clear it out.

      -Victoria

      Like this

  104. Hi victoria

    I need to delete 26 sales orders that where imported by mistake. Can i just delete the entries for those sales orders in sop10100 and sop10200?

    thanks

    Like this

    • Hi Vic,

      Since there will be entries in more tables than that, you will need to run check links if you delete from the SOP10100 and SOP10200 tables to clear up all the related records in other tables. For 26 entries, it might actually be a lot faster to delete them one at a time manually…or move them into the same batch and delete the entire batch.

      -Victoria

      Like this

  105. Hi Victoria

    My VP of sales has changed his mind once again and decided he wants to change how the territorys and salesperson are assigned and of course he wants the history in the system changed. Besides SOP10100 and SOP30200 and RM00101 RM00301 and What ever table hold the manager for the territory(Not sure which one that is) is there any other place in the database that will need to be updated with the new territories/salesperson/manager

    Thanks

    Vic

    Like this

  106. Hi Victoria

    You probably already have this view but as an IT guy accounting confuses me :-)

    I need to see what sales invoices we have applied payments on and the date we applied that payment. Which one of your outstanding views gives me that info??

    thanks
    Vic

    Like this

    • Hi Vic,

      If you are looking for payments applied to posted invoices, then most likely you’re looking for the AR Apply Detail: http://victoriayudin.com/2010/02/15/sql-view-with-ar-apply-detail/.

      If you are looking for payments applied to unposted invoices, then I do not have a view published for this, but they would be in the SOP10103 table.

      Hope that helps.
      -Victoria

      Like this

      • Another question are there any tools or methods of bringing in item history sales data from another system?

        Like this

        • Vic,

          There are various tools and methods of bringing in historical sales into GP, but nothing that I would recommend without finding out the ‘whole story’ because there are so many possibilities.

          There may be many different things to consider, including:
          - type/format of data you have
          - volume
          - what GP modules you are using
          - how the historical data will be used – lookups/reporting
          - if reporting, what reporting will be needed
          - numbering (are items/customers numbered the same way in GP and the other system)

          Do you have someone at your GP Partner company that you can discuss this with? That would be my first step.

          -Victoria

          Like this

  107. Victoria,

    Could you help me with an SRS issue with SOP? First of all, when our end users enter anything incorrectly in SOP (date, amount, customer, etc.) they do a return. We have to provide sales reports to the actual clients and do not want them to see the ins and outs of the returns.

    So how would I do that in SRS? I am running my report off of a Stored Proc, but not sure if it would be done in the Proc or within the report itself. I feel like I should do something with the Apply table and then conditionally suppress it within the report, but cannot get a grip on what I should do!

    Thanks Victoria!!

    Like this

    • Kat,

      My first question would be, what logic can you use to determine whether a return was applied as a ‘fix’ or for another reason? Are there legitimate returns that DO need to be shown on the sales report? How do the users differentiate between them?

      Second, is your report by line item? If so, this will most likely not be possible, or at least you will have to come up with workarounds for a lot of situations, as GP does not do a line by line apply process. So if you have 20 items on an invoice and a return with 1 item applied to it, there is no way to link (in GP) what Invoice line item the Return goes with.

      These are not necessarily answers to your question so much as a way to illustrate that you may need to sit down and write a more detailed spec for the report before even asking questions like whether to use a stored procedure or something else. That’s actually a no brainer, I would get all my data in SQL first, then use that in SRS, so stored proc is probably best. :-)

      -Victoria

      Like this

  108. Hi Victoria – Do you happen to know anything about the FLAGS & TIMESPRT column in the SOP30200 table?

    Thanks!
    Lauren

    Like this

  109. First of all — great site. You provide a lot of very valuable information. I do have one question though: for SOPSTATUS, are 3 and 5 supposed to both say “Unconfirmed Pick” or is this a typo?

    Like this

    • Dominick,

      Thank you for the kind words! And thank you very much for catching this – it is a typo and should have been “Unconfirmed Pick” for SOPSTATUS of 3 and “Unconfirmed Pack” for SOPSTATUS of 5. I have fixed this.

      -Victoria

      Like this

  110. 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

    Like this

    • 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

      Like this

  111. 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

    Like this

    • 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

      Like this

  112. Stephanie Burkhard Reply August 31, 2010 at 3:44 pm

    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

    Like this

    • 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

      Like this

      • 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

        Like this

  113. 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

    Like this

    • 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

      Like this

      • 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???

        Like this

        • 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

          Like this

          • 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.

            Like this

            • 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

              Like this

              • well it is more than several….it is like over 10,000

                Like this

                • 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

                  Like this

  114. 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.

    Like this

    • 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

      Like this

  115. 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.

    Like this

    • 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

      Like this

      • 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

        Like this

        • 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 SOP10100
          SET 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

          Like this

          • 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

            Like this

  116. 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

    Like this

    • 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

      Like this

      • 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

        Like this

  117. 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

    Like this

    • 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

      Like this

  118. 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?

    Like 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

      Like this

  119. 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

    Like this

    • 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

      Like this

  120. 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

    Like this

    • 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

      Like this

  121. 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.

    Like this

    • 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

      Like this

  122. 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

    Like this

    • 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

      Like this

  123. 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

    Like this

    • 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

      Like this

  124. 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

    Like this

    • 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

      Like this

  125. 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

    Like this

    • 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

      Like this

  126. 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

    Like this

    • 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

      Like this

  127. 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

    Like this

    • 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

      Like this

  128. 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

    Like this

    • 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:

      select DOCNUMBR from RM20101
      where VOIDSTTS = 0 --not voided
      and CURTRXAM  0 -- amount owed  0
      and DOCDATE >= '1/1/2000' --change to your criteria
      and RMDTYPAL = 1 --invoices only
      

      Then you can link by the invoice number to the SOP30200 and SOP30300 tables to get the details.

      -Victoria

      Like this

  129. I am facing strange problem, Line Items go missing from Sales Document specially from Fulfilment, What can be reason.

    Like this

    • 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

      Like this

  130. 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

    Like this

  131. 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?

    Like 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

      Like this

  132. 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

    Like this

    • 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

      Like this

  133. 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

    Like this

    • 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

      Like this

  134. 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

    Like this

    • 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

      Like this

  135. what u mean by consulting project

    Like this

  136. can you help me in this issue
    i never make any report in smartlist builder

    Like this

    • 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

      Like this

  137. 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?

    Like this

  138. 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

    Like this

  139. 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

    Like this

    • 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

      Like this

      • 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

        Like this

  140. 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

    Like this

    • 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

      Like this

  141. 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.

    Like this

    • 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

      Like this

    • 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

      Like this

      • 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

        Like this

      • 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…

        Like this

        • 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

          Like this

  142. 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

    Like this

    • 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

      Like this

  143. 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

    Like this

    • 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

      Like this

      • 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

        Like this

  144. 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…..

    Like this

    • 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

      Like this

  145. 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.

    Like this

    • 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

      Like this

  146. Thargelia Romero Reply June 16, 2009 at 9:14 pm

    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

    Like this

    • 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

      Like this

  147. Victoria,

    This is an absolutely fabulous collection of information!

    Leslie

    Like this

  148. 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.

    Like this

    • 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

      Like this

    • David,

      I am having a company write an integration for Shiprush shipping software to talk to my Dynamics data, and I want to do exactly what you mentioned in this post. I’d love to ask you a few questions… would you email me at clay@ghann.com? If we can’t make the modification remove the record with the tracking number when an order is voided, I’m wondering if I can at least manually run a query to clean out SOP10107 on a monthly basis. Thanks in advance….

      Clay

      Like this

  149. 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

    Like this

  150. 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

    Like this

  151. 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

    Like this

    • 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

      Like this

  152. 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

    Like this

    • 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

      Like this

  153. 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

    Like this

    • 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

      Like this

  154. Thanks Victoria! I’ll follow your suggestions.

    Hector

    Like this

  155. 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

    Like this

    • 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

      Like this

  156. Thank you, I got it!

    Like this

  157. 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

    Like this

    • 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

      Like this

      • 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

        Like this

        • 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

          Like this

  158. 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

    Like this

    • 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

      Like this

  159. 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

    Like this

    • 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

      Like this

  160. 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

    Like this

  161. 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.

    Like this

    • 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

      Like this

  162. Victoria,

    Thank you so much!!!!!

    Best Regards,
    Nikki

    Like this

  163. 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.

    Like this

    • 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

      Like this

  164. 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

    Like this

    • 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

      Like this

  165. 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! ;-)

    Like this

  166. 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

    Like this

  167. 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.

    Like this

  168. 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

    Like this

  169. how can I link the SOP number to the PO number associated with the sales order?

    Like this

  170. 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

    Like this

  171. 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?

    Like this

  172. 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

    Like this

  173. 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

    Like this

Trackbacks/Pingbacks

  1. SQL view for Dynamics GP sales quantities and amounts by item by site by month | Victoria Yudin - October 15, 2013

    […] SOP Tables […]

    Like this

  2. SQL view for sales quantities by item by site by month in Dynamics GP - Victoria Yudin - Microsoft Dynamics GP - Microsoft Dynamics Community - June 28, 2013

    […] Sales Order Processing (SOP) commonly used tables […]

    Like this

  3. SQL view for sales quantities by item by site by month in Dynamics GP | Victoria Yudin - June 14, 2013

    […] SOP Tables […]