Posted AP transactions missing distribution on GL side

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (10)

Answers (0)

Mick Egan 3,561 on at

Like (0)

Report

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

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

Hi,

What does the Reconcile to GL routine give you?

Leslie

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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!

Was this reply helpful? Yes No
Rosemary 10 on at

Like (0)

Report

Hi,

I have a question.

If there were no distribution on the GL side, how did the batch balance, i.e. if I am understanding this correctly.

Regards

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

These payable transactions have been posted and paid as well. But when, we do an inquiry on the distribution, they are not there.

Was this reply helpful? Yes No
Heather Roggeveen 9,146 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Heather, I tried that as well. It is not showing up in smartlist as well.

Thanks!

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Heather Roggeveen 9,146 on at

Like (0)

Report

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

Was this reply helpful? Yes No