You can see the specific transactions that make up a “PPP” entry, by running WIP/Recon using the Journal info from the GL report
1 Like
Sorry, I got pulled away.
Following your instructions on the GL Report, it appears the transition, in that period, occurred after the last PPP. There were 17 more invoices posted to that period.
So invoices started to exceed receipts?
What I would do is export the GL Report for that Acct, and then the same for WIP Recon(just that acct, no offsetting accts). Then try to match up the PUR-xxx lines to Invoices
Thanks, sounds like a plan.
In looking at the IWR, just for the clearing account and offsetting transactions, I see a number of PUR-STK debits. What causes PPP to create a debit for PUR-STK?
When a PO Receipt is entered, a PUR-STK is created. Typically, debiting Inventory, and crediting AP Clearing.
If that Receipt line is deleted (or even just marked un-received), it will create another PUR-STK tran, but with the opposite CR & DB.
I believe that even just going back and lowering a Qty on a Receipt, would create the “reverse” PUR-STK.
We still run into issues where the PO price is wrong. And the only way to fix it is to “un-receive” it, change the PO, then “re-receive” (or is it “un-un-receive”?) it.
1 Like
True that. My suspicions are similar to that.
1 Like
Thanks again, really appreciate all the help!
1 Like
Okay, for the most part, the mystery has been solved. With all the advice here, I looked closer at the transactions and discovered an issue. Our AP department is entering invoices and applying the invoice to the supplier’s invoice date regardless of when the invoice was physically received in the mail.
I created a BAQ with APIinvHed, APInvDtl and RcvDtl and compared the AP invoice post date to the Receipt date. I discovered invoices posted in 2019 linked to receipts from 2020. Thus increasing the Debit balance ahead of the receipt Credit. When I calculated the balances correctly in excel, I came within a few dollars of Received Not Invoiced at period end.
Again, thanks to all your advice and guidance.
4 Likes
If there are no manual GL transactions against the account and the AP Clearing account is not on any other GL Control Code, then there are 2 ways other ways this could get out of whack. First, all control accounts like Cash accounts tied to Bank Accounts, A/R Trade, Unapplied Cash, All Inventory accounts (including WIP), A/P Pre-Payments/Advance Payment, A/R Invoiced Deposits/Pre-Payments, A/P Trade, A/P Clearing, and Retained Earnings, should have Restricted Functions tied on those Account Segment Values on the Natural Account Options > Restrictions sheet:
Second, check to see if anyone in A/P is using the “New Unreceived Line” option in A/P Invoice Entry. With this option, you can enter an invoice for a PO Line(s) that hasn’t yet been received. This will Debit A/P Clearing and Credit A/P Trade, thus potentially resulting in a debit balance in the A/P Clearing account. If that is done, then someone needs to also go into the Invoice/Receipt Match screen, in A/P General Operations, and match receipts, when they finally do come in, with the invoice that was already entered. I briefly discuss this option when training customers on A/P Invoice Entry but discourage them from using it. I don’t like it because it creates extra work and allows for things to go wrong. My logic is, why would you pay an invoice for something you haven’t received yet? It’s better to enter it as an Advanced Payment.
If you put in Account Restrictions and ensure that they’re not using the Unreceived Lines option, I think you’ll have better controls in place.
1 Like
We have been struggling with AP Clearing since implementing Vantage in 2005. I reckon we must have found at least 20 variations of ways hang costs up in AP clearing, such is the downside of Epicor’s wonderful flexibility.
Contrary to Laurice’s advice, we do raise AP Invoices using the “New Unreceived Line” option in preference to Advance Payments if we are pre-paying in full. You still need to clear an Advance Payment after receipt just as you need to perform an Invoice/Receipt Match to clear an Unreceived AP Invoice line .
The root of the problem is that Epicor and its predecessors do not record the matching of transactions in and out of AP Clearing and as a consequence there is no AP Clearing Report telling you what is in AP clearing. With the high volume of transactions we have going through that account it quickly becomes the stuff of needle in the haystack and we have other things to do with our time.
I have developed as set of Excel Power Queries on the database tables to perform the matching. Here is the broad flow which so far picks up everything in our implementation:
TranGLC is the table that everything revolves around. I wish I knew of its existence and function in 2005:
- Link TranGLC to PartTran
- Link TranGLC to APInvExp and APInvDtl
- Link TranGLC to APInvJob and APInvDtl
- Merge the 3 datasets back together
- Run a pivotable over the merged dataset summing debits, credits and balances by the following rows:
- PO Number
- PO Line
- PO Release Number
- Invoice Number
- Invoice Line
If your PO Balance is zero all is good. You are only worried about out of balances. Where there is no PO and an AP invoice has been raised direct against a job or expense account then it should balance at the invoice level.
We can still burn a day tracking down the various issues but AP Clearing now balances with its subledger which is a solid starting point.