Forums

Use the AP Historical Aged Trial balance to get the AP subledger balance and compare it to the GL balance for the same period using the GL Summary Inquiry or the GL Trial Balance.

Tools->Routines->Financial->Reconcile to GL and then choose Payables Management. You can ener you GL AP account number and attmept the find what is causing the difference.

Thank you Mark & Richard,

Actually the issue that I have is kind of strange

I know exactly where is the difference but not sure how to fix it

I have the following transactions:

-Credit note of $1000  on July 2012

-Invoice of $600  on Jan 2013

-Invoice of $300 on Jan 2013

In Jan 2013, The credit not has been applied against the 2 invoices above

When attempt to reconcile AP for Jan 2012 it shows the difference of $100

When I checked all the earlier months back from Dec 2012 back to July 2012, The same difference even though the AP previously was reconciled perfectlty for those periods  

When I look at the credit note form the GUI,  it suppose to show $100 unapplied but in my case it show the remaining amount of $0

How do I fix this, and which table I should use

Thank you,

The first thing I would do is use the Reconcile Payables Accounts window to reconcile payables.

Taken from the Help file:

In the reconcile process, you'll verify that unapplied payments, unpaid finance charges, and current balance totals accurately reflect the transaction amounts posted for them.

If Payables Management is integrated with Purchase Order Processing, reconcile Purchase Order Processing before reconciling Payables Management. You should verify purchase order information first because reconciling Purchase Order Processing might change information reconciled in this window.

Back to me:

After that I would check links payables.

The field that indicates what might be unapplied is the Current Transaction Amount. If the current trx amount is zero, then you won't have any remaining amount to apply. Before monkeying with the tables, be sure the amount is a mistake. To 'open' the vendor credit back up if it's apply amount got hosed, you'll need to get the transaction back into the PM20XXX tables. Look at the details in the PM30XXX tables and figure out what went wrong.

You may be better served to give up on this one. Can you void the historical transaction?

Leslie

Thank you  Leslie,

I went through all the steps that you referred to from the reconcile to check links, still showing the exact difference

More interesting is when I run the Aged trail balance without any date option(up to date) it match the up-to-date balance in the GL and this what makes me reluctant of looking at the option of moving  back the records from 3xxxxx tables into 2xxxx tables

I cannot void the subject issue (credit note transaction as it belong to a closed period)

Alaa, you may have already reached this conclusion but if it were me, for $100 difference, and considering that the balances match today, I would ignore that difference and move on.  It apparently was a transaction which effect has been flushed out with time.

I coould not ignore this ssue simply because it is more than $100, secondly the difference shows now with the periods that previously has been reconciled

When i said it match with the up-to-date balance i meant whenever i run the AP aged report without giving any date

but the moment i specify the date even it is future date then it shows the difference

My apologies Alaa.  I misunderstood what you were saying.

Hi again,

I paused when you said that everything balances when you do not put a date on it, but when you do, it's off $100. It has me thinking along the lines of the apply dates. The apply date is used by the HATB to determine what the outstanding balance was at a point in time. If you inquire on the transaction and then zoom back on the unapplied amount, do you see two or three apply notes?

So when you inquire, it looks like this:

When you zoom back on the Unapplied Amount, you will see something like this:

Do you see all $1,000 applied, or is there still an outstanding $100?

If you look up each of the applys, what are the apply dates? For instance, if you go onto the PM10200, PM20100 and/or PM30300 table you have several dates:

DOCDATE
DATE1
GLPSTDT
APTODCDT
APPLYFROMGLPOSTDATE

Does the analysis of these dates reveal anything?

No worries Frank, I am sure part of it the way i explained

Thank you Leslie for your effort.  i looked again at those windows.

There are 2 apply notes only for the that credit note

the remaining amount is zero even though the total applied amount is less than the documet amount !

PM20100  showing the two applied invoices

PM10200 has no records related

PM30300 showoing the two applied invoices

PM30200 showing 3 records on for the credit note, 2 for the invoices applaied to

there is nothing alarming in the dates in those tables

the credit note date was in July 2012

the 2 invoices created on Jan 23, 25 2013

the apply applied on the same date the invoices created

The issue appeared only after the apply happened!

***I made the following changes in a test company has the same data as live, i want to make sure if it is safe to apply on production

- I removed the credit note trx from PM30200 and  Inserted it back into the PM20000

- I update it the CURTRXAM fields for the same trx in PM20000 to show the remaining unapplied amount which reflect the difference

When i look at it from the GUI now, all looks fine to me.

The TB matched between GL & AP as Jan 31st, and the Credit Note trx is now showing the remaining amount (unapplied amount)

Do i miss any thing  else?

Thanks again!

Hi,

In your test company, run check links and reconcile again. Also, make sure you can do an apply for the remaining $100.

I think you have covered the bases. The two documents that are fully applied are still in history, each has an apply record. The credit memo is back in the open table with a current transaction amount equal to the unapplied balance.

Oh, I just thought of something else, make sure there were no entries to the GL from the original apply transaction (shouldn't have any).

If your reports, current ATB and HATB are in balance, you should be good to go.

Kind regards,

Leslie

Yes i ran the check links and the HATB still matching with GL Control Account

the only thing i found after that ATB still off by the same amount when i ran without giving any date

I looked closely at the open transactions for that particular vendor that originally cause the issue

finally i found the last piece of mystery

There is another credit note applied partially to another invoice

-Credit Note is $2000 and invoice is $1500

-The amount applied is $1400

-The remaining unapplied amount for the invoice should be $100 but again it shows zero

That explain why AP balance match when running without date option as the unapplied amount from one credit note offset another unapplied amount from another Invoice !

I could not beleive how this could even happen?!

Does the auto-apply  create such mess?

Anyway i updated remaining unapplied amount for that invoice and all reports speaks the same numbers

one last thing you have mentioned in your post :

"Oh, I just thought of something else, make sure there were no entries to the GL from the original apply transaction (shouldn't have any). "

The apply process should not generate any GL transaction, Am i right? correct me if i am wrong. there is nothing to do with GL transaction at this point

Thank you so much Lesli

Ah ha!

Doesn't it feel good when the mystery is solved! (kind of).

You are right about the GL, the apply process should not generate any GL transaction. If it didn't, then yay, working as designed.

The auto-apply will apply any open credits or payments in the order you specified in the Payables Management setup window. Your choices were to apply it by due date or by document date. In my world, clients never allow the auto-apply to rule. They always apply a credit specifically to an invoice rather than accepting the default.

I hope everything is back on track for you.

Kind regards,

Leslie

Actually it feel amazing!

Most of the credit to your guidance which provided me with confidence while dealing with those tables

Forgot to mention about this: even all TB reports in subledger and GL matching  each other

Only  the reconcile utility for AP singing solo!  it rarely worked for me , always have its own difference between AP & GL even it they both matches

Any suggestion?

The reconciliation utility is comparing transactions for a specific time period. If you begin the period at a time when both were in balance, you should find any differences. Are you including all of the ap accounts that make up the balance? For instance, if you account for discounts in the general ledger, you would need to include both the AP account and the discount account in the reconcile.

The utility has been very accurate in my experience. I'm with you though. So long as the ending balance works, I'm happy.

Kind regards,

Leslie