You’re offline. This is a read only version of the page.
I just found that that some of my posted payables do not have a distribution on the GL side. Is there a script that I can run to see which all transactions are missing the GL distributions. I found it out after running inquiry on the account number posted on the payable transactions.
Thanks!
*This post is locked for comments
Hi nlo
If the AP reconcile did not throw any records, then Mick's SQL scripts are a good starting point.
Victoria Yudin also has a view to see all AP distributions: victoriayudin.com/.../sql-view-to-show-all-gl-distributions-for-ap-transactions
You may be able to join that to the GL20000 table to try and see what is missing.
In the bad old days before I knew anything about SQL, and before the AP reconcile, I would download a SmartList from AP of all the transactions with Document Amount and all the transactions from GL and include Originating Document Number, Originating Master ID and use vlookups to find all the transactions that existed in both.
So if you don't have the SQL skills, that is a more manual way of achieving the same outcome.
Once you isolate, if they truly don't exist in the GL, then a journal will be your only option to correct.
Cheers
Heather
Hi, I think you should look at the distribution tables. Please be advised that this tables is shared between work and open (PM10100). If the invoice is posted and paid, you may look into the history distribution table (Pm30600). If you could not find (the probability of which is very less, it must be somewhere if entered), you may try to enter the same voucher and copy the distribution using SQL query for this document. Now copying where ..? The answer is the status of the invoice - if it is paid then in history if not then in PM10100. If the GL distribution is missing you might want to copy there as well. Please do not copy blindly and take a backup of all the relevant tables. When copied you may delete the voucher entered for copying. Or you may check your GL distribution and copy from there. Either way, this can be done by using SQL query only.
Heather, I tried that as well. It is not showing up in smartlist as well.
Thanks!
If you mean the enquiry on the Payable document - sometimes the distribution record will drop off - but it has actually posted to the GL. If you know of one transaction, then you could run a SmartList from the Account Transactions under Financial and search on:
- GL Code = Accounts Payable Control Account
- Originating Master ID = the Vendor ID
And then narrow it down using the Amount or Originating Document Number.
That would at least help you verify whether your issue is that the distributions were never posted or it is an issue in the distributions held against the AP transaction.
These payable transactions have been posted and paid as well. But when, we do an inquiry on the distribution, they are not there.
We have had occasions where GP 'hiccups' and the payables side of the transaction runs but not the GL side, we realized that there were Financial batches with odd Batch ID's. We just posted these batches then the GL entries posted.
Hi Leslie,
I ran the reconcile for payable accounts (batches), reconcile to GL and checklinks which did not fix the issue.
Reconcile to GL did not give me any output.
Thanks!
The below queries are samples of how to start, not including fully applied documents and only at Invoices, you can get the idea from here. Run against the company database.
First check, see if any Unposted Journals:
SELECT a.VENDORID, a.DOCNUMBR, b.JRNENTRY FROM PM20000 a LEFT OUTER JOIN GL10001 b ON a.DOCNUMBR = b.ORDOCNUM WHERE b.JRNENTRY IS NOT NULL AND a.DOCTYPE = 1
Second Check, only looking at Unapplied PM Transactions, and in the Current Financial Year:
SELECT a.VENDORID, a.DOCNUMBR, b.JRNENTRY FROM PM20000 a LEFT OUTER JOIN GL20000 b ON a.DOCNUMBR = b.ORDOCNUM WHERE b.JRNENTRY IS NULL AND a.DOCTYPE = 1
Mick
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,228
Super User 2024 Season 2
#2
Martin Dráb
230,056
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (