Recently I was working on a Purchases Clearing reconciliation as a favor to the Controller at my company. It wasn’t a very fun project, but we needed to make sure that the Purchases Clearing account agreed to the General Ledger so our auditors would be happy. I was asked by the Controller to help out and so I went to work.
In Sage 500 ERP, the individual debit and credit transactions to the General Ledger maintain a link back to the source transaction that they came from. For example, if you post an Accounts Payable Invoice, the individual debit and credit transactions that get created in the General Ledger maintain a link back to the Accounts Payable Invoice in the Accounts Payable module. This allows you to go into a screen that lists General Ledger transactions, select an Accounts Payable Invoice debit/credit, and click a button to have Sage 500 ERP automatically open the related invoice screen. It’s a nice feature and I thought that I would be able to leverage it to help me reconcile the Purchases Clearing account to the General Leger. HOWEVER, I quickly discovered that the link back the Accounts Payable Invoice is only to the invoice itself, not to the specific line on the invoice. So, if there were five lines on the invoice which generated five General Ledger transactions, each of the five General Ledger transactions would have the same reference value, that of the Accounts Payable Invoice (but not the specific line).
Eventually, I had to group my Purchases Clearing transactions by day and compare them to the General Ledger (also grouped by day) in order to do the reconciliation. It would have been nice if I had the link back to specific lines because I could have reconciled things by Purchase Order Line level instead of by day. But, alas, the world of ERP isn’t perfect and many times you are forced to choose the best option available, even if it isn’t the most elegant.
Now, this is a post on an Acumatica blog so I should probably say something about Acumatica. This little reconciliation project that I did got me wondering how I would have approached this in Acumatica. So, I took a look at the General Ledger table that holds the debits and credits. I was very pleased to find TWO relevant columns. Not only does Acumatica store the header information (in the GLTran table). It also stores the line information (in the GLTran table). So, in my example above, it would not only have stored the Accounts Payable Invoice, but also the specific LINE in the Accounts Payable Invoice that triggered the individual debit/credit to be posted to the General Ledger. I haven’t looked at this specifically for Purchase Order receipts, but it definitely looks promising.