AP Clearing Account - How to Reconcile~

jbryant · August 31, 2020, 5:18pm

Hell to everyone,
Looking for advise on how to analyze the AP Clearing Account. We are a little more than a year into our ERP implementation and the AP Clearing account seems to be out of control. My expectation is, for the most part, that if there are items on the “Received Not Invoiced” report that there would be a Credit Balance on our balance sheet. In the beginning months, this seemed to be the case. Then about four months in, it took a 180 degree turn and now continues to maintain a Debit Balance.

Does anyone have a way to “reconcile” the AP Clearing account? In our case, it seems like the Debits are far exceeding the Credits and I’m not sure how to track it down. I’m wondering if there isn’t some way to create dashboard/tracker that would show the transactions and to what they’re linked to. What are the best tables to look at for these transactions?

Any and all help is greatly appreciated.

BTW, we are a dedicated/public cloud tenant on ver. 10.2.600.9

Carson · August 31, 2020, 7:37pm

I would start with the Chart Tracker. In general all the debits should be tied to an invoice and all the credits should be “Periodic Posting Process”. You can see the details of the transaction on the cost tab or with the INV/WIP recon report. For a BAQ you would start with the TranGLC table and join to partTran. (There should be a post on doing that)
You can also look through your GL control codes to make sure that a different AP clearing account isn’t assigned for some situations.

Also group by your Journal Transaction. I would expect only two types.

ckrusen · August 31, 2020, 7:57pm

One of the biggest culprits of GL accounts not being what was expected, was form our accounting dept “fixing” things that E10 “missed”

AP clearing is a system account and should never have an Journal Entries on it. But you get an someone that jumps the gun and “fixes” something. Then when the E10 process actually happens, the prior “fix” now needs to be undone.

For example:

A PO receipt is created, but the user only marks it as Arrived, and not yet received.
Someone from accounting sees that receipt didn’t hit the GL, so they do a JE for how much it should have been.
The receipt is later corrected (marking it received). Now the GL is over stated, because the JE and the Receipt are both included.
A JE opposite of what was done in step 2 is now needed.

So use the Chart Tracker, and check for any entries not from the IJ or PJ.

jbryant · August 31, 2020, 8:10pm

Thanks for all the responses.

So far all transactions are IJ and PJ.

I started with the chart tracker and have been drilling into the details. Issue here is thousands of transactions. I have a concern that someone may have chosen the AP Clearing account during invoice entry for some reason and not linked to a receipt. I haven’t found a way to create a BAQ which might show if this has happened.

I currently only show the AP Clearing account being used in the company AP Account and INV WIP COS control codes.

jbryant · August 31, 2020, 8:15pm

I’m thinking if someone chose the AP Clearing account during a non-receipt invoice entry in AP invoice entry, that would force it to the AP Clearing account thru the Purchase Journal. Because of that, it wouldn’t stand out in chart tracker. It would still show as a PJ. Correct?

Arul · August 31, 2020, 10:29pm

When you run Received Not Invoiced Report, please run for longer range as there is a chance that report will miss some receipts. There are many occasion, Epicor does not mark as invoiced item as Invoiced in RcvDtl. You can write a BAQ by linking RcvDtl to APInvDtl and check it.
.
Below is the SQL script which I use for AP Clearing reconciliation if there is a problem. Please modify the company and AP reconciliation account.

WITH MyCTE AS (SELECT Erp.POHeader.Company, Erp.POHeader.OrderDate, Erp.PORel.PONum, Erp.POHeader.VendorNum, Erp.PORel.POLine, Erp.PORel.PORelNum, Erp.RcvDtl.PackSlip, Erp.RcvDtl.PackLine,
Erp.RcvDtl.ReceiptDate, ISNULL(SubQuery1.ReceiptAmt, 0) AS ReceiptAmt, ISNULL(SubQuery2.InvoiceAmt, 0) AS InvoiceAmt
FROM Erp.POHeader INNER JOIN
Erp.PORel ON Erp.PORel.Company = Erp.POHeader.Company AND Erp.PORel.PONum = Erp.POHeader.PONum INNER JOIN
Erp.RcvDtl ON Erp.RcvDtl.Company = Erp.PORel.Company AND Erp.RcvDtl.PONum = Erp.PORel.PONum AND Erp.RcvDtl.POLine = Erp.PORel.POLine AND
Erp.RcvDtl.PORelNum = Erp.PORel.PORelNum AND Erp.RcvDtl.ReceiptDate IS NOT NULL LEFT OUTER JOIN
(SELECT pt.Company, pt.PONum, pt.POLine, pt.PORelNum, pt.PackSlip, pt.PackLine, ISNULL(SUM(t.BookDebitAmount - t.BookCreditAmount), 0) AS ReceiptAmt
FROM Erp.PartTran AS pt INNER JOIN
Erp.TranGLC AS t WITH (NOLOCK) ON pt.Company = t.Company AND t.RelatedToFile = ‘PartTran’ AND CONVERT(VarChar, pt.SysTime) = t.Key2 AND CONVERT(VarChar, pt.TranNum)
= t.Key3 AND t.SegValue1 = ‘3000’
WHERE (pt.Company = ‘ABC’) AND (pt.PONum = pt.PONum) AND (pt.POLine = pt.POLine) AND (pt.PORelNum = pt.PORelNum)
GROUP BY pt.Company, pt.PONum, pt.POLine, pt.PORelNum, pt.PackSlip, pt.PackLine) AS SubQuery1 ON Erp.POHeader.Company = SubQuery1.Company AND
Erp.PORel.PONum = SubQuery1.PONum AND Erp.PORel.POLine = SubQuery1.POLine AND Erp.PORel.PORelNum = SubQuery1.PORelNum AND Erp.RcvDtl.PackSlip = SubQuery1.PackSlip AND
Erp.RcvDtl.PackLine = SubQuery1.PackLine LEFT OUTER JOIN
(SELECT AP.Company, AP.PONum, AP.POLine, AP.PORelNum, AP.PackSlip, AP.PackLine, ISNULL(SUM(t.BookDebitAmount - t.BookCreditAmount), 0) AS InvoiceAmt
FROM Erp.APInvDtl AS AP INNER JOIN
Erp.TranGLC AS t WITH (NOLOCK) ON AP.Company = t.Company AND t.RelatedToFile = ‘APInvExp’ AND CONVERT(VarChar, AP.VendorNum) = t.Key1 AND
AP.InvoiceNum = t.Key2 AND CONVERT(VarChar, AP.InvoiceLine) = t.Key3 AND t.SegValue1 = ‘3000’
WHERE (AP.Company = ‘MJ0001’) AND (AP.PONum = AP.PONum) AND (AP.POLine = AP.POLine) AND (AP.PORelNum = AP.PORelNum)
GROUP BY AP.Company, AP.PONum, AP.POLine, AP.PORelNum, AP.PackSlip, AP.PackLine) AS SubQuery2 ON Erp.POHeader.Company = SubQuery2.Company AND
Erp.PORel.PONum = SubQuery2.PONum AND Erp.PORel.POLine = SubQuery2.POLine AND Erp.PORel.PORelNum = SubQuery2.PORelNum AND Erp.RcvDtl.PackSlip = SubQuery2.PackSlip AND
Erp.RcvDtl.PackLine = SubQuery2.PackLine
WHERE (Erp.POHeader.Company = ‘ABC’) AND (Erp.POHeader.PONum > 0) AND (Erp.POHeader.OrderDate >= ‘2014-04-01’))
SELECT TOP (100) PERCENT Company, OrderDate, PONum, VendorNum, POLine, PORelNum, PackSlip, PackLine, ReceiptDate, ReceiptAmt, InvoiceAmt, ReceiptAmt + InvoiceAmt AS NetAmt
FROM MyCTE AS MyCTE_1
WHERE (ReceiptAmt + InvoiceAmt <> 0)

tmcgrady · September 1, 2020, 12:55pm

Calvin is spot on, this is one of the biggest items we see when this happens. Also i would check your process. Is accounting getting invoices and just entering them and not matching against the reciept. i had a customer where they received the goods then entered the invoice as a misc invoice rather than a receipt match. takes time to dig into it and find the issues.

jbryant · September 1, 2020, 1:11pm

Thanks everyone, I appreciate all the comments.

Currently, I don’t see anything that appears to be a journal entry. (GJ)
Correct me if I’m wrong here but, if accounting was entering misc invoices instead of matching receipts, then I would see a Credit balance, correct? The issue we see is a large Debit balance in the account. Somehow invoices being entered are clearing (Debiting) more than the receipts. It’s like I need to review the Debit transactions in AP Clearing invoice’s amount to the purchase order. That’s if it had a PO.

ckrusen · September 1, 2020, 2:32pm

Another often overlooked issue is creating or changing a GL Control.

Like if I use a Part Class GL control to specify the Inventor account, and change the Part Class of a part that has QOH.

Prior to the Part Class change the the receipts & issues hit GL Acct 1100-00.
After the change, receipts and issues hit GL Acct 1105-00.

Acct 1100-00 would include the value of any QOH prior to the change. And that would not be offest by any reduction in QOH. I.E. - 1100-00 would be overstated by the value at the time of the change, and 1105-00 woul be understated by that same amount.

ckrusen · September 1, 2020, 3:07pm

jeff - In the end, all the GL’s must balance. So dig into what happens (i.e.- which GL Acct’s are hit) when an AP Invoice is posted.

The clearing acct is set in a AP Account type of GL Control. Check to make sure that the GLC used for AP (specified in Company Config -> Modules -> All Modules -> GL Control -> List) is as expected.

If someone changed this while there was a balance in the accounts used (before or after the change), then there would be orphaned amounts in those accts.

jbryant · September 1, 2020, 3:12pm

Hi Calvin,
Luckily, I created a BAQ when you helped me before on a similar check. I show that I’m only using our account 2000 in two places. GLC AP Account and Inventory COS and WIP.

Would this be correct?

ckrusen · September 1, 2020, 3:18pm

And they’ve been that way since day one?

Any where else that Acct 2000-000 is being used?

Also, only 2 GL segments? (Making sure you’re not multi-site, and dynamically setting the Div segment)

edit

I just checked to see what GLC’s specify our AP Clearing acct and found two I wasn’t expecting:

jbryant · September 1, 2020, 3:24pm

Yes, as far as I recall, it’s been that way since the start. I was the one who set it up.
Yes, only two segments.

I done a number of test scenarios in our Pilot system which is only about a month or so behind Live and all the test flow perfectly thru the expected accounts.

There’s got to be something happening here. I just don’t see how the clearing account could flip. If it was too many receipts, you’d have a Credit balance. This would seem to indicate that there are more AP invoices than receipts.

ckrusen · September 1, 2020, 3:26pm

If someone “un-receives” a PO Receipt, it would remove whatever the original hit to the AP Clearing acct was. But this can’t happen if an invoice has already been applied against the receipt.

jbryant · September 1, 2020, 4:00pm

Could this have to do with receipt dates versus AP invoice apply date?

ckrusen · September 1, 2020, 4:12pm

Maybe.

If the dates are in closed periods, and “Capture Outdated Transactions” is not checked on the “Capture COS/WIP” program when it is run. Or if it is checked, but those dates precede the "Outdated Trans From: " value.

If either of those cases exist, then there could be unposted transactions.

Does running the WIP Recon report With:

Date Range (as long as you’ve been live)
Your specific GL acct
Include Offsetting Accts selected
Only un-Posted selected

show anything odd?

jbryant · September 1, 2020, 4:35pm

Typically, I don’t see anything odd. I also am the one doing capture and post and do my best to see that everything is okay.

This all came about do to end of year balances. I just looked at this GL’s totals at year end and only see roughly an 81k debit balance. Really minimal for the millions of dollars that flow thru there.

Next, I want to see if I find an AP invoice that may have been posted to 2019 with maybe a 2020 receipt. Did this occur around end of year which would throw off the balances at that time.

Does this seem realistic to you?

ckrusen · September 1, 2020, 4:45pm

If you run the WIP Recon for just that account (both posted and unposted), you should see every part transaction that hit that account. Should pretty much just be PUR-STK, PUR-MTL, and PUR-UKN

What does the GL report for that Acct show? Anything besides “Periodic Posting Process” and the individual Invoice postings?

When did it “Flip” ? And what was the transaction(s) that cuased it to go the other way?

jbryant · September 1, 2020, 4:53pm

I’m running the IWR as I write.
It flipped in November of last year. I wish I knew which specific transaction caused it. There are hundreds of them.

ckrusen · September 1, 2020, 4:55pm

If you run the GL report for that period, you can see when the running balance changes direction. And whether it was PPP trans (from Capt COS/WIP) or AP Invc posting.