YellowOrangeJigsaw

Sales Transaction Entry GL distributions in Dynamics GP


The topic of General Ledger distributions for Sales Order Processing transactions has been known to cause a fair amount of confusion for Dynamics GP users.  I would like to address some of the typical questions we receive on this. Please click on the screen shots to see them larger/clearer. 

Just to set the stage, I am talking about the window titled Sales Transaction Entry (Transactions > Sales > Sales Transaction Entry):

sop01

And the GL distributions that are shown when you click on the Distributions button:

sop11

~~~~~

Question:  Why are there no distributions for Inventory and Cost of Goods (COGS)? 

Answer:  The distributions for Inventory and COGS are always based on the cost of the inventory items at the time the invoice is posted. Since the cost can keep changing with other transactions being posted, GP does not actually calculate the cost (and thus the Inventory and COGS distributions) until the invoice is posted.

~~~~~

Question:  If I expand the line item to see more detail I see a cost, but it’s not correct. Why is that?

sop02

Answer:  This cost is filled in at the time the invoice is created. This is informational only at this point and will not be what is used as the cost when the invoice is posted. At the time of posting the cost will be recalculated. Once posted, if you look at the invoice and drill into the detail, it will show the correct cost the invoice was posted with.  

~~~~~

Question:  How do I see what GL account numbers will be used for Inventory and COGS when the invoice is posted?

Answer:  Since these accounts can be different for every item, you need to drill down on each item by clicking on the item to highlight it, clicking on the blue arrow next to the Item Number hyperlink:

sop03

And then clicking on the Distributions button:

sop04

You will see not only the Inventory and COGS distribution accounts, but any other line item specific account numbers:

sop05

Since these accounts can be different for every line item this can get quite cumbersome to look through if you have a lot of line items. If you wanted to see the Inventory and COGS accounts for all line items on a transaction you could use the Sales Line Items SmartList and bring in the COGS Account Number and Inventory Account Number columns. [Note: When testing this on versions 9.0 and 10.0 of GP I see 3 columns available with the same exact names. In my experience, the first one of these 3 will be the correct one, but since there any many various setups out there, you should test yours to make sure that you're looking at the correct columns before relying on them.]

~~~~~

Question:  Can I change these distribution accounts? 

Answer:  Yes, you can change these for every transaction line – the changes will be saved for this transaction line only and will not effect any other lines or transactions. No setup will be changed by making changes to individual transactions or lines. When you make a change and save the line item, you will get the following message:

sop06

Make sure to say Yes to this.

~~~~~

Question:  How does the system decide what GL account numbers to use? 

Answer:  Take a look at your Sales Order Processing Setup (Tools > Setup > Sales > Sales Order Processing):

sop07

If you have chosen to use Posting Accounts from Item, then the system looks for the accounts in this order:

  1. Inventory Item setup (Cards > Inventory > Item > click on Accounts)
  2. Posting Accounts for Inventory series (Tools > Setup > Posting > Posting Accounts > choose Inventory for Display)

If you have chosen to use Posting Accounts from Customer, then the system looks for the accounts in this order:

  1. Customer setup (Cards > Sales > Customer > Accounts)
  2. Posting Accounts for Sales series (Tools > Setup > Posting > Posting Accounts > choose Sales for Display)

Important note:  Changing any of the setup options will not have any impact on already saved transactions. 

~~~~~

Question:  I am getting an error saying something similar to “This Sales distribution amount is incorrect and will not be posted.  Do you want to save with errors?” What cases this and what do I do?

Answer:  There are typically 2 reasons why you will get distributions errors:  

  1. You are missing setup information so GP cannot determine what accounts to use.
  2. You have manually changed something in the distributions and it does not conform to the totals that GP is expecting.

To fix missing account numbers, you can just fill them in manually on the transaction or change the setup and have the system regenerate the distributions by clicking Default on the Sales Distribution Entry Screen:

sop081

When you click Default you will get the following message:

sop09

This is warning you that if you have made any manual changes they may be lost by having the system regenerate the default distributions. If you are not sure, write down or take a screen shot of the current distributions and amounts, in case you need to re-do your changes. To have the system regenerate the distributions, click Delete. (Sounds a little counter-intuitive, I know.)

Regenerating the default GL distributions will also fix most of the issues with manual changes, but I would like to explain that in a little more detail. What happens is that GP is automatically matching up the various totals on the transaction with the types and totals of the GL distributions. When it finds a mismatch, it does not allow the posting of the transaction. This is a good thing and will typically prevent reconciliation issues. Here is an example of what GP is trying to match:

sop10

Outlined in red are the most common totals that I have seen issues with. Let’s go through them one at a time:

  1. Subtotal: all the SALES distributions must total up to a credit equal to the Subtotal.
  2. Trade Discount: all the TRADE distributions must total up to a debit equal to the Trade Discount.
  3. Tax: all the TAXES distributions must total up to a credit equal to the Tax.
  4. Amount Received: all the CASH distributions must total up to a debit equal to the Amount Received.
  5. On Account: all the RECV distributions must total up to a debit equal to On Account.

You can find a complete list of all the distribution types and their explanations in Accolade Publications‘  Understanding Sales Order Processing book.

~~~~~

My 2 cents:  For me one of the main benefits of using a product like Dynamics GP is that it can do a lot of the work for you. If you are finding yourself changing the GL distributions for Sales Order Processing transactions frequently (or need to often go back and reclass), I would recommend a review of your setup. In most situations the setup can be done so that you will not need to make any changes to the distributions for 99% of your transactions.

53 Responses to “Sales Transaction Entry GL distributions in Dynamics GP”

  1. Thank you for all of this great information, it is very helpful to a non accountant dealing with technical issue.

    I have put in place a Late Fee line item in our invoices so that invoices do not need to be returned and regenerated when the late fee comes due. Our billing is done in 3 very large runs at specific times annually for different member types. I now plan to update these records in place by doing the following:

    Update Debit Amount in existing SOP10102 record
    Insert Distribution record into SOP10102 for Late Fee
    Update Transaction Amount in RM20101
    Insert Distribution record into RM10101 for Late Fee
    Update Totals in SOP30200
    Update Price in SOP30300

    My concern is the TRXSORCE column in SOP10102. I would like to use a string to mark these rows as inserted manually by this process. Is this possible or should I use the existing value for each invoice that is present in the table?

    Like

    • Hi Steven,

      I would strongly advise AGAINST updating posted transactions in this way. There are many other tables in multiple modules where transactions are propagated to once posted in SOP. You will be throwing the data integrity of all those transactions out the window by doing this. Inserting transactional records directly into SQL this way may also cause your GP system to be ‘unsupportable’.

      Another option for late fees is to create a new transaction, just for the late fee, then ‘associate’ the transactions together when printing them. There are probably a few different options and you may want to collaborate with your GP partner to find the best approach.

      -Victoria

      Like

      • I appreciate the response and advice, unsuportable is definitely not an option.

        I have actually identified about 20 tables using SOPNUMBE, DOCNUMBR, CUSTNMBR, and TRXSORCE columns accross database using specific data to find populated values. I am performing the testing of this process in a DEV environment at this time. I have adjusted a single invoice and was able to apply payment to it and see the records update across these tables as well as others.

        Creating another transaction is not an option, we will need to revert to returning all of these invoices and reissuing new invoices with the late fee populated with actual dollar values.

        -Steven

        Like

  2. Hi Victoria,

    I have Doubt After posting Sale order, i would like to see that transaction in General Ledger
    but its not available that Transaction.

    This is very good article which you provided.

    Thanks

    Like

  3. Hi! Victoria. I just want to ask, if you know where the GP picks the Unit Price for an Item on Sales Transaction Entry. It is dependent also on Customer ID entered? Thanks in advance.

    Like

    • Hi Ryan,

      No way to answer this generically, as this really depends on how you have set up your pricing. Standard Pricing looks at the Price Level, which can be set up by Customer, although most companies do not do this. Extended Pricing can be set up by Customer, as well, but does not have to be.

      -Victoria

      Like

      • Thanks Victoria. Just for example that we are using Extended Price Level always, on what table and fields on GP, does the Unit Price comes? Because it is automatically generated upon entering an Item Number. I try, if I could find it on GP tables and fields but no luck. Please help, if you have any idea, where I could find it on the database.

        Like

        • Ryan,

          If you mean where is the unit price stored, it will be in the SOP10200 table. If you mean what Extended Pricing tables does GP look at to calculate the unit price, I cannot answer that. If you are not able to determine this using something like SQL Profiler, you may want to talk to Dynamics GP Support to see if they can help you with all the questions you have on Extended Pricing.

          -Victoria

          Like

          • Hi Victoria,

            What I mean is where the GP look to calculate the Unit Price, because it is automatically populated upon entering of Item Number. Anyway thanks again. I will try your suggestion, but if you have any idea on this, please let me know.

            Like

  4. Hai Victoria,
    I have an issue; in my GP2010 system- sales transaction entry and purchase reciept entries are posted automatically. The users only save it, but it gets posted. Is this any setup issue…?

    Like

    • Fazil,

      There is nothing in GP out-of-the-box that will post actual sales invoices or purchase receipt transactions automatically, no matter what your setup. There are some things that will print what looks like a posting report – for example voids or deposits received on sales orders. I would recommend working with your users to monitor what they are doing and seeing what is actually happening. If you have any customizations, examine those to see if they could be causing unexpected behavior.

      -Victoria

      Like

  5. Hi Victoria. We are receiving an error in Sales Transaction Entry when entering a return: “This Cost of Goods Sold amount is incorrect and will not be posted. Do you want to save with errors?” I believe GP thinks this return has been entered in Returns Processing module, however, we are not registered for this module; we are entering it straight into Sales Transaction Entry. This error is also reproduced when opening an existing return and selecting the Default Accounts on an existing return. I found reference to this error in the knowledge base but cannot find the hotfix: http://support.microsoft.com/kb/869056. Any ideas?

    Like

    • Katie,

      That link you’re pointing to is for version 7.5 of Dynamics GP, is that what you’re using? You will not be able to get a hot fix for a version that has not been supported for almost 4 years, at least from MS, nor would I expect that to work for any version other than 7.5.

      Does this happen to all returns or just one? Have you tried to reconcile the amounts on this transaction by going to GP | Tools | Utilities | Sales | Reconcile – Remove Sales Documents and entering the transaction number in the To/From range?

      -Victoria

      Like

  6. Hi Victoria,

    I was wondering it there a way to make Sales Invoice get the line item unit cost from the Item lot number insted of Item Card current cost.

    I mean i can already see Item lot cost @ Inquiry > Inventory > Lot Number Inquiry

    But when i post an invoice item cost is calculated from the item card

    Thanks
    Alfred

    Like

    • Alfred,

      What valuation method are you using? I just tested this in the sample company using FIFO Perpetual and the when I post the SOP Invoice the cost is taken correctly from the cost of the lot.

      -Victoria

      Like

      • Thanks for responding
        I’m using Avarage Perpetual could this be the problem?, if so should it be a problem?!!
        Also i’m on GP 2010 SP 3 but i searched customer source well there’s no hotfix or a KB about such problem.

        Like

        • Yep, that’s the reason and it’s working as designed. Take a look at the definition and explanations for the average perpetual valuation method in the Dynamics GP Inventory manual.

          You will not find a KB or hotfix for something that is working as designed. In my experience very few business use the average perpetual inventory valuation method anymore. However, this is something that should be decided on by your accountants and/or auditors.

          -Victoria

          Like

          • Humm
            OK i will try to change the Item Valiuation method (Tools > Utilities > Inventory > Change Valiuation) on a local copy and see what will happen on SOP.

            Thanks for your time and efforts will keep you posted on the results.

            Like

            • Thanks Victoria change Item Valiuation method fixed it, although in order for the Utility to work right all Trx related to the Item must be posted before using the utility.

              But i wonder what is the idea behind disable geting the Lot cost if the Item Valiuation Method is Avg. ?!

              Like

  7. Hi Victoria,

    I have a problem with the Sales Invoice’s distribution. I have posted an Invoice , but there is no INV & COGS account distribution for it. What could be the reason?? Please help.

    Like

    • Hi Divya,

      I can think of a number of reasons for this:
      – The item was not recognized as an inventory item by GP.
      – The item type was one that does not post Inventory or COGS.
      – GP calculated the cost of the item as 0.

      -Victoria

      Like

  8. The question and answers helped me solve an issue with respect to the GL account selected by default in Cash Receipt Distribution entry. Also I refer to your list of tables in the GP database. Thanks a lot for everything.

    Like

  9. Gustavo Ballesteros Reply February 22, 2010 at 6:03 pm

    Thanks, it is an imported transactions

    Like

  10. Gustavo Ballesteros Reply February 22, 2010 at 4:46 pm

    Victoria

    How can post by document and not by item?, our GP posts by item instead of document and is creating a lot of transactions in the database. Where I need to do this change?

    Thanks

    Like

    • Gustavo,

      Are you entering transactions manually or importing them? In manually entered transactions, I have not seen GP post ‘by item’, it should combine entries for the same GL account number by default. I cannot think of a setting for this, it should be doing this automatically. The only posting setting I can think of determined whether to create one GL transaction for each sales transaction or one GL transaction for each sales batch. If you want one GL transaction per batch, you can change that on the Posting Setup window (Tools > Setup > Posting > Posting).

      -Victoria

      Like

  11. Could you tell me exactly which distribution accounts are required for an Invoice posting?

    Like

  12. Victoria,

    Great information. Thanks for writing. I was wondering if there is a way to be sure that an invoice would post the INV & COG. We have a lot of custom and I often find things that don’t work quite right. So I try to catch everything by running exception scripts prior to posting (at night in a batch). I check for unmatched distributions while it’s an invoice but is there a way to see COG before hand?
    Thanks, Jerry

    Like

    • Jerry,

      There is no easy way to do this. If you were doing it manually, you would need to go into each item and check the distribution accounts set up for it. Since the sales transaction will not post without the Inventory and COGS accounts it is not a question of whether they are there, but more of whether they are correct. If you are looking to check the amounts that will be used, that is a lot more difficult, since it will depend on the order that invoices are posted and your cost layers at that exact point in time. All in all, not an easy task.

      My 2 cents would be that rather than checking the distributions before posting, check the setup in GP and the logic of any imports or customizations to confirm they are correct. Set up a test environment and test everything. I know this is sometimes easier said than done, but that is the best way to do it.

      -Victoria

      Like

  13. No, it doesn´t, I also deleted the row and entered it again, the error message is still the same. I found out that the “on order amount” in sales header is different to the invoice amount. I guess it is caused by the copy of the original invoice and the deletion of some rows.

    Like

    • Peter,

      What is the transaction type you are looking at? I don’t think the ‘on order amount’ matters at all for invoices. However, other amounts, like markdown, on account, tax, subtotal, etc. will matter a great deal. Typically using the GP functionality to copy a transaction will not cause any issues with these, but I have seen customizations as well as connectivity issues while transferring orders to invoices cause problems with distributions.

      -Victoria

      Like

  14. Victoria,
    thanks for the great explanations.
    Unfortunately I have the error message: ”This Sales distribution amount is incorrect and will not be posted. Do you want to save with errors?” All numbers are matching but the message still appears. I copied this invoice from and old one, deleted some rows and wonder if there is an amount hidden somewhere from the original invoice that causes this error. Thank you in advance,
    Peter

    Like

  15. Manikandan,

    There is so much logic going into the posting process for SOP transactions that I truly don’t believe you’re going to be easily able to accomplish what you’re looking to do. And I am not sure VBA is enough, I believe you’re looking at a pretty complicated Dexterity customization.

    I am unclear why you have to use SOP? Why not just an Inventory transaction? My recommendation would be to re-evaluate what exactly you’re trying to accomplish and see if there is a different way to do it that is not so invasive to the internal logic of GP. It may be worthwhile to pay for some consulting time with someone knowledgeable in what you’re trying to do and GP so they can help you come up with a comprehensive solution.

    -Victoria

    Like

  16. Hi Victoria,

    In continuation with my previous post, was just wondering if u knew from where GP picks the cost while posting in SOP. I have customised the post routine, to delete the distribution table and also to mark the extended prices and other amount fields to zero. Also i changed the current cost of the product to zero. Now i c that there are no distri created for sales and recv, but though the cost was manually updated to zero in iv00101 tables, i c it has created distri for inv and cogs. Do u have any idea from where it picks this cost for the product. I even changed the cost in sop10200 tables to zero b4 posting.

    Like

  17. Hi Victoria,

    Thnks for ur immed reply. I am actually doing customised by writing VBA Codes. I wanted to avoid this and think if something the product could be handled with. Anyways, tks for ur assitance. The reason for me to have asked is bcos our co. uses this as the delivery note n not invoice. We book the sales at a much future date, but just want our inventories to be updated. Morover, this would also appear as uninvoiced DN. Much appreciate ur assistance n look forward to more interactions with u.

    Like

  18. Hi Victoria,

    This is just a wonderful post. Got to know many newer things. I have a qn to u pls. I want to create invoices in SOP module but do not want it to get posted to RM or GL Tables but just the inventory module and SOP30200 tables. Is there any way to achieve this? Thanks for your early reply.

    Like

    • Manikandan,

      Thanks for the kind words! You can stop any transaction from posting to the GL in the posting settings (Tools > Setup > Posting > Posting). However, I cannot think of any way to stop SOP invoices from posting to RM. I suppose just about anything is possible with a customization, but I don’t think this would be a simple one.

      -Victoria

      Like

  19. how to compute for the Inventory and Cost of Good Solds per line item in the Sales Transaction Entry Module?

    Like

    • Felix,

      There is no one answer to this…it will depend on each individual inventory item settings for item type and valuation method. For example, for an item of type Sales Inventory and valuation of FIFO perpetual, you’d have to find the older cost layer not sold yet. However, if you have multiple cost layers and multiple unposted sales invoices, there is no way to predict what cost each invoice will use, since it will depend on the order the invoices are posted in.

      -Victoria

      Like

  20. Gi Jeet,

    That is an excellent question! I did not notice this before, but I must have picked an Invoice with some back-ordered items. If items are not entered as fulfilled on an Invoice they can still be printed and viewed on the screen, but they will not be billed for, which is what you’re seeing here.

    -Victoria

    Like

  21. why are some of the Extended Price amounts zero in the first screen shot? (Sales Transaction Entry) I’m trying to learn GP and every discrepancy makes one ask “why”.

    Thanks
    gi

    Like

  22. Very very helpful. I am so happy to see a blog directed to Dynamics 10!

    Like

  23. As usual something from Victoria that is clear and easy to understand.
    Thankx

    Like

  24. Awesome article Victoria!! This is so useful to users. Just incredible.

    Like

  25. We never find such a nice article on world wide web…

    Like

  26. Hi Victoria,

    This is a very good post, Vaidy has mentioned it in his blog. Even I was wondering why this INV&COGS created after the Invoice is posted. The explanation was really clear and good and expecting more in future like this.

    Shan

    Like

  27. Victoria,

    I saw comments about this article in vaidy’s blog.Now I read out ..It is really very useful.

    we welcome the more functionalitywise article like it.

    Thanks
    Jeganee

    Like

  28. Victoria,

    This is a simple yet complete article on Sales Trx distributions.

    For instance, I know the INV & COGS account distribution lines are going to be created only when we post the transaction, but I have often wondered WHY so.

    Thank you so much for such an informative article.

    Thanks
    Vaidy

    Like

  29. A friend of mine just emailed me one of your articles from a while back. I read that one a few more. Really enjoy your blog. Thanks

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,566 other followers

%d bloggers like this: