June 25, 2010 
I have made a number of updates to my SQL view to show all GL distributions for AP transactions since I first published it. Some of these were in response to comments asking for additional fields, others were added when I came across new data to test with. Rather than publish another post with the latest revisions, I have updated the code in my original blog post, but I wanted to let everyone know it’s there. Some of the updates are:
- Added Due To and Due From distribution types for anyone using Intercompany transactions.
- Added Realized Gain and Realized Loss distribution types for Multicurrency transactions.
- Added Currency ID, Exchange Rate and Originating Debit/Credit fields.
- Added Distribution Reference, Batch ID and Transaction Description fields.
- Added Voucher Number.
As always, if you find any issues or would like any additional updates, please let me know.
There are also enough Payables SQL scripts that I have moved them to their own page. The page can also be accessed from the main navigation menu on this blog under GP Reports and on my GP Reports page.
Victoria Yudin 
Hi,
Will this help in Reco GL to AP? Or can we have a query that it will do our GL and AP reconcelation task.
I am sure currently people are doing this way.
1. Extract all AP control account transaction from GL (Account transaction Smartlist)
2. Extract all AP transaction (control account) from AP module (Payable Transaction Smartlist)
3. Compare GL data based on GL original source document in AP transaction ideally by Vlookup feature in excel.
4. Extract all unmatch trx as in reconcile process.
Can this be done using SQL query. Or do we have such query ready. I am sure this will save lots of time in reco process.
Regards,
Santosh
Regards,
Santosh
LikeLike
Hi Santosh,
Creating your own SQL query for the reconciliation is much more difficult than you may think, especially on a historical basis. Doable, but way beyond the scope of a blog post and would probably cost more in time and resources than buying a product that can do this for you.
If this is not a huge need and only comes up once in a while, you can use the Reconcile to GL feature that was introduced in GP 10.0 (GP > Tools > Routines > Financial > Reconcile to GL). This tool is not the greatest, but may help in most simple situations.
If this is a more common need and/or there are typically large differences, or if you need this for modules other than AP and AR, consider purchasing a 3rd party product designed specifically for this: The Closer.
-Victoria
LikeLike
Dear Victoria,
Thanks so much for your posts, there were very helpfull to me..
I have a problem that GL distribution is missing for one of the payments.
I inquire PM30200 which shows all transaction that create distributions in tbale PM30600, but only one transaction i have in PM30200 ,its distribution is missing in PM30600 so how can i create the distribution back.
I need your help Victoria, Thanks alot again for your valuble information.
Regards,
HF
LikeLike
Hi HF,
If a payment was entered at the same time as the invoice (so on the Payables Transaction Entry window you have entered both a purchase amount on the left and a payment amount on the right), the resulting payment transaction will not have any GL distributions. This is proper and expected behavior in GP. The invoice will contain both the invoice and payment GL distributions together.
To check if this is the case on your transaction, check the invoice your payment is applied to. If it shows an On Account amount of $0.00, then the payment was entered at the same time as the invoice and will not have its own GL distributions.
-Victoria
LikeLike
Dear Victoira,
Thanks a lot for your reply.Actually, we did not enter the invoice and payment at same time, what we have done is that when we make an apply against invoice, it didn’t went to history( which is strange), so we uncheck apply against invoice and save the window then we check it again and save the window , then it moves to histroy.
That certain invoice was in MAY 2010,when we run the GL Reconcilation again in June , we found a difference between payable and GL because opening balance of June 2010 (which is Ending balance of May 2010 ) shows a difference which is not the case before the above incident.
I am quite sure that the difference is because of that specific transaction which its distrinbutions GL was not showing in the first place.
Moreover, when I run your inquiry “SQL view to show all GL distributions for AP transactions” , i saw the invoices and payments of all transactions against that customer, but for that specific invoice, the payments is missing which we already applied before and finish.
That’s what make me to dig in the tables one by one , and I found what I explained in my previous post.
Sorry for telling you my big story.I hope I am not bothering you..But this case make me drink Coffee a lot :)..Any idea to find a solution ??!!
Thanks again.
LikeLike
HF,
It sounds like something went wrong with the GL distributions on this transactions. This could have been caused by someone manually changing the distributions before posting the transaction or some kind of a system glitch at the time of transaction entry or posting. In either case, if the payables transaction looks fine except for the missing distributions and the only issue at this point is that the GL does not reflect this payment, you can enter a manual GL transaction to correct this. If you are using the Bank Rec module and the transaction is not there either, a Bank Transaction instead of a GL transaction may be better.
-Victoria
LikeLike
Hi Victoria,
Another awesome post! You are such a valuable contributor. I hope you don’t mind that I have been handing your blog address out to freely to everyone in my Dexterity classes as a rich source of incredibly helpful information. I’ve got a class going this week – were your ears burning yesterday? I don’t suppose you know Spanish?
Great Job as always!
Leslie
LikeLike
Hi Leslie,
I was wondering why my ears were all red yesterday. 🙂 Thank you so much for your kind words and helping drive traffic to my blog! The more the merrier. I know enough Spanish to order a drink, but that’s about it…
Thanks again,
-Victoria
LikeLike