December 8, 2008 
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):
And the GL distributions that are shown when you click on the Distributions button:
~~~~~
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?
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:
And then clicking on the Distributions button:
You will see not only the Inventory and COGS distribution accounts, but any other line item specific account numbers:
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:
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):
If you have chosen to use Posting Accounts from Item, then the system looks for the accounts in this order:
- Inventory Item setup (Cards > Inventory > Item > click on Accounts)
- 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:
- Customer setup (Cards > Sales > Customer > Accounts)
- 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:
- You are missing setup information so GP cannot determine what accounts to use.
- 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:
When you click Default you will get the following message:
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:
Outlined in red are the most common totals that I have seen issues with. Let’s go through them one at a time:
- Subtotal: all the SALES distributions must total up to a credit equal to the Subtotal.
- Trade Discount: all the TRADE distributions must total up to a debit equal to the Trade Discount.
- Tax: all the TAXES distributions must total up to a credit equal to the Tax.
- Amount Received: all the CASH distributions must total up to a debit equal to the Amount Received.
- 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.
Victoria Yudin 
Hello there,
thank you for all your informative articles!
I was having a question regarding the trade discount amount GL distribution, we have different brands and for each brand different GL account should hit for the discounts; can we automate the trade discount distribution to come from the item master?
p.s: our item master accounts are defined properly but we are facing this issue these days which the accounts are not coming automatically to the sales transaction entry!
thanks in advance.
LikeLike
Hi Sitara,
Trade discounts are global, so they cannot take the account number from the item or even the customer. You can only have one default trade discount GL account for the entire company. Markdowns are item specific and can take the account from the item. However, trade discounts are calculated automatically at the percentage set up for the customer, whereas the markdown is something that needs to be manually applied to each line item and each transaction. If you are importing transactions, markdowns are probably the way to go. If you are manually entering them, markdowns may be very time consuming to enter every time.
Two alternatives if the out-of-the-box GP behavior does not do exactly what you want:
(1) You can create a customization to change the GL accounts, there are various ways to do this, but none of them will be very easy. Usually customizing around SOP is pretty complicated.
(2) Reclass afterwards. This may be a lot easier to accomplish than a customization, but will involve some additional steps and will also not have all the “correct” accounts on one transaction, which could be problematic depending on what kind of reporting and analysis you’re doing.
-Victoria
LikeLike
Hi Victoria! Thank you so much for your great Blog, you are a real asset to the entire planet! (Well at least my part of it) 🙂
I have hopefully a simple question regarding distributions. I am importing Sales via SmartConnect and now want to import distributions also. I have it all working but every sale I import already has assigned the entire sale amount (RECV) to a Particular account.
I am importing all the accounts and amounts as well as the same account with the remainder after subtracting the amounts from the other accounts.
Is there any way to get GP NOT to generate the SALES line matching the RECV amount?
I would really like it to do this for just one customer number, not all.
Smartconnect has ways to run SQL commands after successful import, but I don’t like the idea of tracking down the line I want removed and deleting it. I feel it would be much cleaner if it never existed in the first place.
Please let me know any thoughts or suggestions.
Thanks!
LikeLike
Hi Dan,
Thank you for your very kind words.
I don’t work with SmartConnect myself, but I am thinking there is probably an option you can select for whether or not the default distributions should be created. Perhaps if that is set to no, that would help with what you’re doing?
I am not sure about making this work differently for one customer, this may be a global setting for the integration, so your choices might be to either do this for all customers or maybe set up a separate integration for this one customer.
If this is not enough to help, I would recommend contacting eOne support – they are very good and should be able to guide you on this if it’s at all possible to do with SmartConnect.
-Victoria
LikeLike
Nailed it! I did find an option in SmartConnect to NOT create distributions on an import job. This works perfectly for my needs. Once again thanks for your help, Im so happy you maintain an interest for the rest of us! 🙂
LikeLike
Awesome, glad that worked! 🙂
LikeLike
Hi Victoria! Long time reader, first time poster; but occasional caller. You actually walked us through our first year-end close. Your blog is always my number 1 resource.
I have this issue occasionally, yet persistently. And it can be about various accounts: Cash, Receivables, Freight (which is always a weird one); which indicates to me, the issues are coming from different sources. Also, it does not occur every transfer/post routine. The point being that I just tend to deal with it via the default/delete option in your original post.
Also, we are posting upwards of 2500 invoices a week, and I may encounter this on 70 of those invoices. A 3% error rate is a bit on the high side, but when there are more hours available in the day is when I will get to the RCA.
My question is are you familiar with a means or mechanism that can perform the default/delete action noted above on a bulk scale? Or a way to call a distribution rebuild at a batch level; or, honestly, even if it did the whole table – i don’t care.
I have tried:
Reconcile – Open Document Amounts (eventually learned this was not about SOP)
Reconcile – Remove for just the one document
Utilities – Remove Distributions
Check Links – Sales Work & Sales Distributions
Basically, I can solve my issue; it’s just tedious work, often during a crunch period.
Thanks,
JP
LikeLike
Hi JP,
I am not quite clear on the issue – are you saying the GL distributions get out of wack and you have to manually fix them on 3% of your SOP invoices?
The short answer is that I am not aware of any way to automate the default/delete of the GL distributions. Primarily because this is not an issue that’s come up before. Which leads me to the longer answer:
This is definitely not a ‘known issue’ or something that I have seen routinely happen. Certainly not at a regular rate of 3%. Usually this happens once or twice a year at our bigger volume clients and almost never for most companies I’ve worked with. That makes me think there is something else causing it in your GP that I would recommend identifying, as it might be causing other problems as well, just not ones that are as visible. Yet.
Are these transactions imported together with the GL distributions? Are there customizations/modifications/3rd party products involved? Are users manually changing the GL distributions? While I know that you’ve been able to ‘fix’ the issue by updating the GL distributions, that is just a band-aid and I would recommend getting to the bottom of what is causing this to happen.
-Victoria
LikeLike
We are having a problem with Distributions on Receivables transactions (not Sales). We are getting the error: The COGS distribution(s) does not equal the actual amount. The AP clerk can edit the DISTTYPE from COGS 14 to INV 15 and the error goes away. What is the system looking at when producing this error? THANKS!
LikeLike
Hi Lori,
This works similarly to what I go over in this blog post – all the totals on the main transaction entry window must match the totals/types on the GL distributions. If you look at the Receivables Transaction Entry window, there is a field for Cost (above Sales). The debit total of the COGS distributions needs to match the amount in this field. And the credit total for the INV distributions needs to match that total as well.
From what you write, I am guessing you are entering Inventory and Cost of Sales distributions, but your Cost field is zero, as most people do not use it. It’s fine not to use it, but then the Type of your Inventory and Cost of Goods account distributions should be the same to result in a total of 0 when they are added up.
Besides this kind of ‘double check’ on the totals of the GL distributions and the amounts on the main transaction entry window, GP does not use the distribution Type for anything.
So – you could either enter the cost in the Cost field and then enter a COGS and INV distributions. Or you can leave the Cost field blank and enter the Inventory and Cost of Sales account distributions with the same Type – most people use OTHER in this case to make it clear, but you can use COGS or INV, or whatever makes more sense to you.
Hope that helps.
-Victoria
LikeLike
Hi Victoria,
Not sure if this blog is still active or not, but I have a perplexing problem related to this post. My company uses the drop ship check boxes, is there a reason why if the drop ship check box is checked and a cost is entered why it wouldn’t flow through to the gl?
LikeLike
Hi Mike,
On a drop ship, there is no inventory, so I believe the Cost of Goods is going to use what is on the vendor invoice, regardless of what may be populated in the cost on the SOP invoice. I believe it looks like this:
POP Vendor Invoice posting:
debit Drop Ship Items account 300
credit Accounts Payable 300
SOP Customer Invoice posting:
debit Accounts Receivable (or Cash) 500
credit Sales 500
debit COGS 300
credit Drop Ship Items account 300
The COGS amount has to match what was booked to the Drop Ship Items account from the Vendor invoice, or that account will never reconcile.
-Victoria
LikeLike
We have some tax distribution accounts that aren’t automatically populating. I checked the Tax Detail Maintenance and the account is set there, but it’s not being populated when an invoice with that tax detail id is created.
We are manually typing it in after the invoice is created, but after manually typing it in, if I press “Default” it is cleared again indicating that there is still a problem for future invoices with this tax detail id. I’m not sure where else to look and would appreciate any insight.
Thanks,
Justin
LikeLike
Hi Justin,
This is pretty difficult to troubleshoot without actually seeing it. I would recommend working with your GP Partner to get this fixed.
-Victoria
LikeLike
Thank you for the prompt reply.
Our GP Partner has never been able to help with these sorts of issues in the past, and we are in the process of transitioning to Dynamics 365 so we aren’t really in search of a better one at this point for GP.
I did find that the TaxPostToAcct was set to 0 in TX00201. I have updated that to 1 for all the taxes that were having problems. I am hoping that will fix it.
Do you know where to find that TaxPostToAcct field in the front end? I don’t see it in the Tax Detail Maintenance window.
LikeLike
I’ve not seen that ever be anything other than 1. Do you have an ISV product or customization that might be doing something different with the taxes?
-Victoria
LikeLike
We do an import of sales tax rates via SQL with new rates each month, so it may have been an oversight when adding newer tax rates. It does seem to only be a problem with the newer rates we have entered in the past few months. I’ll check to see if our older scripts for sales tax imports have a different value for TaxPostToAcct.
LikeLike
By the way Victoria, I really appreciate your site and everything you do for the Dynamics GP community. I haven’t posted here before today, but I have been using your blog for probably 10 years or more and it has saved me countless times.
Thanks again for your replies and incredibly useful site.
Justin
LikeLike
Thanks Justin, I really appreciate that! 🙂
-Victoria
LikeLike
HI Victoria
I have a question that sort of comes from your above example. We use the method of getting the sales accounts from the customer so that when our sales clerk is entering invoices she does not need to look up the region “province” that the customer is in and it automatically populates the sales code per province and the tax %. The only problem is that with this method we cannot get the sales codes to separate. All of our sales are going to the same account and it is making it very hard to do an analysis. Our controller seems to think that GP 2013 is only able to pull the account codes from the customer or item not both. We went through a lengthy process of creating service items that we could use in purchasing and sales in order to get an accurate automated report on profit but it failed when we realized the sales were not grabbing the customers info. In our purchasing end of things we also enter everything manual when I use our preexisting items it never grabs the item code it always auto corrects back to the standard purchasing account 5200-xx. I am unsure if this makes sense but we are looking into getting an external support person to help us but at this time we do not even believe gp 2013 will ever be able to grab both of these things in order to bill and report the way we would like. Please advise. Thanks
LikeLike
Hi Jennifer,
The short answer is that your controller is correct, you can either use the accounts from the customer, which will be one account per invoice, regardless of items, or use the accounts from the items, which can be different for each item.
The longer answer is that there are many creative ways to accomplish correct coding for GL accounts. I don’t mean to brush you off, but it’s really not something that I would attempt to solve in a blog comment. You need to have an in-depth conversation with someone knowledgeable about GP setup so that you can explain to them what you want to do and they can help you determine if GP can do it and/or if there is a 3rd party or customization needed/available to accomplish what you need.
Same for purchasing, there are several different ways of setting things up and they will vary greatly depending on your specific situation.
Sounds like you are already on your way to getting some expert help, that is what I would recommend. 🙂
-Victoria
LikeLike
Hi Victoria! Love the blog. I’m a newbie to GP and have a question about Freight. Is it possible to change the account used for Freight to a Depatrment Code based on the Customer ID? I’ve looked everywhere but I’m stumped. Thanks!
LikeLike
Hi Chris,
In out-of-the-box Dynamics GP you can only specify the Freight account at the company level, so short answer – no.
You would need a customization or 3rd party product to accomplish this of you wanted to use the Freight amount at the bottom of the invoice. Alternately, I have seen companies set up an item (or multiple items) for Freight and then add it as a line item to invoices instead of using the Freight field. That could offer some additional options for you, but you would need to test that out and see if it works with the rest of your GP workflow.
-Victoria
LikeLike
Victoria, I can’t thank you enough:)
LikeLike
Hi again Victoria. I have what is hopefully a quick question. We have multiple stores that use a POS system that will be integrated with GP on a nightly basis. Sometimes registers will be over or short cash at the end of the day. Where is this information typically entered in GP? Thank you so much for your help!
LikeLike
Hi Katie,
There is really no such concept in GP as over/short on cash, since there is no ‘cash register’ concept. That said, I always say that the easiest way to deal with situations that are not specifically provided for with GP functionality is to break down what needs to happen at the GL level. From there you can decide whether to simply enter it in the GL, or if you need to involve one of the subledgers.
For example, let’s say you’re $1.25 short on cash on a particular day. What needs to happen? We need to decrease (credit) Cash by 1.25 and the other side of that is a debit of 1.25 to an account you likely already have for this called something like Over/Short on Cash Register, or whatever makes more sense in your business. We could simply make a GL entry to record this. That’s one option.
However, let’s think about subledgers in GP. If you are using the Bank Rec subledger/functionality in GP to reconcile your Bank Statements, a GL entry may not be the best option, since the Bank Rec module will not know anything about GL entries. In this case you can enter the same thing as a Bank Transaction – it would be an Increase or Decrease Adjustment, one side is the Cash account, the other your Over/Short on Cash Register account. This is probably what you want, as most companies are using Bank Rec in GP.
Of course, this is all generic. I don’t know your business or how your GP is set up. Your GP partner should be able to suggest the best solution for this situation given their knowledge of your specific circumstances. And it may differ from what I am suggesting.
Hope that helps. 🙂
-Victoria
LikeLike
Hi Victoria,
I am new to GP and your blog posts have been incredibly helpful. I have a question about Sales Transaction Entries…does it ever make sense to have each transaction be a separate entry (with a unique Document # but with the same Batch ID), as opposed to having every transaction for that day on the same Document?
Thank you!
LikeLike
Hi Katie,
Thanks for your kind words.
I suppose it depends on what you’re trying to achieve. If you’re sending invoices to customers, each transaction has to be separate. If you need to keep track of separate customers and who bought what, again, each transaction should be separate. But if you are simply re-entering sales made elsewhere, for example, at a retail store, there is typically no reason to have multiple transactions, simpler to enter one per day (or even month, for some businesses).
-Victoria
LikeLike
Thanks so much for your quick reply, Victoria.
We are entering sales from our POS system, via an automatic integration. Since it sounds like either way will work, I am trying to figure out which way makes reconciliation the easiest. Any other insight you have on this would be much appreciated!
LikeLike
Katie,
Without seeing the details of what you’re doing and what you need to reconcile to, this is really difficult to give advice on. Have you asked your Dynamics GP partner for help on this? They may be in a better position to help you answer this.
-Victoria
LikeLike
Thanks Victoria! Contacting our GP partner will be my next step.
LikeLike
When I change the distribution it defaults back to the previous GL distribution after saving SOP entry, how do we get around this issue
LikeLike
Mark,
Out-of-the-box GP will let you make and same changes. If your changes are not saving, I suspect there is a customization or 3rd party product causing this.
-Victoria
LikeLike
While the SOP setup requires a choice of Posting Accounts from Item OR Customer, is it possible to select one and have the other option apply to certain circumstances?
For example, we have a customer set up for “selling” inventory items to the Marketing Department. Because we track COGS by item, we appropriately have the default posting accounts come from the item. However, we want these marketing items to post to Marketing Expense instead of COGS. As far as I know, that means we have to change distributions for each item on the invoices. Is there a way for this customer to override the Item distributions with Customer distributions?
LikeLike
Tom,
You cannot do what you’re asking without either a customization or possibly a 3rd party product.
-Victoria
LikeLike
I am stumped. I have a cash receipt transaction that is defaulting to the incorrect AR account. The correct account is set up on the class (and customer). If an invoice was created with the wrong AR account, does the cash receipt take it out of that account used on the invoice, regardless of what is set up as a default on the class/customer? Or alternatively does the checkbook have an AR account default?
LikeLike
Lynda,
When you apply a cash receipt to an invoice, the invoice AR account is used for the cash receipt. If you don’t want this to happen, you can post the cash receipt without applying it to an invoice first, then it will use the AR account on the customer. After posting the cash receipt, you can apply it to the invoice.
-Victoria
LikeLike
I absolutely cannot figure out a weird integration issue. I am integrating Sales Transaction data into GP – Live and Test databases. Exact same integration and data for both. The Header contains the line item descriptions, all of which end in a date. For example “XYZ Sales 05/01/2015”. In the sample data I’m uploading there are 8 line items, all with dates in May. When I print the resulting invoice from both databases, the Test database’s line items have all been sorted by the data part of the line item description – which is what I’m looking to see. But in the live database, the exact same data appears not be sorted at all. So it looks like the integration is fine, but why would both databases organize the same data differently?
LikeLike
Andrew,
Not sure if this will help, but have you seen this KB article: https://support.microsoft.com/en-us/kb/924857/en-us?
-Victoria
LikeLike
Thanks for the quick reply, Victoria! I will look into that!
LikeLike
Yep, that worked. Apparently this is a client machine based solution, not a server-side one. Thanks, Victoria!!
LikeLike
Hi Victoria,
I have a situation where a Return will not post. The error says “This Cost of Goods Sold Distribution amount is incorrect and will not be posted.” I reviewed your article above and all of the numbers match, and I clicked on “Default” in the Sales Distribution Entry screen. I’m not sure where to go next.
Jelana
LikeLike
Hi Jelana,
Try reconciling the transaction (Microsoft Dynamics GP | Tools | Utilities | Sales | Reconcile – Remove Sales Documents). Make sure to only select the one return in the From and To. Sometimes that will fix this issue. If not, this may need to be fixed directly in SQL, which is not something I would feel comfortable advising on unless I could see your data. In this case you might be better off deleting the return and re-entering it.
-Victoria
LikeLike
I’m not sure if this will be seen, but it seemed worth a shot. I have a return transaction and the original sale transaction that have brought up an issue I don’t understand.
The product cost (offsetting COGS and Inventory accounts in the distribution) are different between the two transactions. I would expect that the product cost would be the same. If it left our inventory at one dollar per unit, why in the return would it be different from that original cost?
LikeLike
Dan,
What inventory valuation is the item set up to use?
-Victoria
LikeLike
FIFO perpetual. We think we found it (working on confirmation now), and are hoping to perhaps find a setting that can be changed.
The inventory valuation for the incoming return is being pulled from the last POP receipt of the item. It doesn’t make immediate sense to me why this would ever be desirable, but there it is.
Even more oddly, it seems to be the last POP receipt in any location.
So, we have the following series of transactions.
Item A is received at cost X in location 1.
Item A is sold at cost X in location 1.
Item A is received from a PO at cost Y in location 2.
Item A’s transaction 2 is returned using the valuation cost Y from transaction 3.
So, the exact same inventory is valued differently than it was when it was originally sold.
LikeLike
Dan,
Yes, what you describe is correct. Here is the problem – how does GP know which sale the return goes against? If you were using serial or lot numbers that may address it, but might be much more cumbersome for data entry.
The only think I can think of is allowing the user to override the unit cost on returns (this is an option when setting up the SOP Document) and then manually entering the correct cost every time.
-Victoria
LikeLike
You are correct. Our inventory with serial/lot information does not behave this way.
I am a GP newbie, and I assumed that the returns transaction would be linked to a sales document, as one should not exist without the other. Thanks for the convo! Your blog has been very useful.
LikeLike
Hi Victoria, I’m trying to troubleshoot a sales order that will not post. I’ve created sales orders, back orders, and invoices and non will post. GP displays a blank sales posting journal (all zeros, no accounts). I revisited my sales transaction entry pages and the “Distributions” button is grayed out on all of the orders. Any advice?
LikeLike
Alicia,
Based on what you’re describing it sounds like there is a bigger issue – possibly with your GP installation. I would recommend working with your GP Partner or with Microsoft Dynamics GP Support so they can look at your computer and diagnose the issue.
-Victoria
LikeLike
Thanks Victoria!
LikeLike
Hi Victoria, thank you very much for the information you share about GP. I’m trying to find a way to configure SALES type Distribution Account , depending not on Customer or Iventory , but from Tax Detail applied to each line or from Shipping Mehtod. I know this might sound queer, the problem is that the Sales Account should be different depending on the taxes details resulting for each line. I have tried many combinations of settings, but no luck; before giving up, I’d appreciate you opinion, if you think the only way is a customized development . Thank you very much
LikeLike
Hi Noela,
I cannot think of any way to do this out-of-the-box, there are no settings in GP to base anything on the tax detail or shipping method. You would need to customize this to accomplish what you’re looking for. It should not be too bad, we have done similar customizations before, however, make sure to take into account both the line item accounts as well as the summary, already calculated distributions when creating your customizations.
-Victoria
LikeLike
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?
LikeLike
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
LikeLike
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
LikeLike
Taking your advice on this one. Too many tables found and probably don’t even have all of them. Thanks again for all you do for us.
LikeLike
Steven,
Glad to help, and thanks for your kind words!
-Victoria
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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…?
LikeLike
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
LikeLike
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?
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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. ?!
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
Thanks, it is an imported transactions
LikeLike
Are you importing the GL distributions? If so, then you will probably need to change the import so it is combining the GL distributions in the way you want.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
Could you tell me exactly which distribution accounts are required for an Invoice posting?
LikeLike
Chris,
At the very least the system must have accounts for the following set up:
Inventory
Cost of Sales
Sales
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
Peter,
If you click on ‘Default’ on the Sales Distribution Entry window, does that make any difference?
-Victoria
LikeLike
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
LikeLike
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.
LikeLike
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.
LikeLike
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.
LikeLike
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
LikeLike
how to compute for the Inventory and Cost of Good Solds per line item in the Sales Transaction Entry Module?
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
Very very helpful. I am so happy to see a blog directed to Dynamics 10!
LikeLike
As usual something from Victoria that is clear and easy to understand.
Thankx
LikeLike
Awesome article Victoria!! This is so useful to users. Just incredible.
LikeLike
We never find such a nice article on world wide web…
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike