I'm working directly with the database tables and am trying to reconcile the AP Invoices (using APIBD, APIBH and APIBC) to the GLPOST data on an account- and transaction-level basis.
Right now, I'm using DRILLDWNLK (GLPOST) and ACCTFMTTD (GLAMF), and tying these to POSTSEQN (APIBC), CNTBTCH (APIBD) and GLACCTID (APIBD), There are a couple of issues I'm coming across:
1. The AP Invoices tables don't seem to account for debits and credits - most entries are listed as positive amounts, despite the fact that a number of them are credits in the GL. Is there an intermediary table that adds whether the transaction is a debit or a credit?
2. There are a few situations where the batch number varies slightly between the AP Invoices files and the GL. For example:
GL: The drill down link starts with 33987120AP: The posting sequence is 987, but the specific transactions have a batch number of 121.
The GL's 987+120 reconciles with the AP's 987+121, but I'm just not sure why the batch number values are different.These are the same transactions, but for some reason the batch number is changed somewhere along the way. In the GL, there's no batch 121 at all, and in the AP, batch 120 has a posting sequence of 988. 988+120 reconcile between the two files.
Does anyone know what causes these discrepancies in posting, and is there a table that keeps track of them?
Thanks so much for any help.
Why are you doing this?
In order to create a solid link between the two tables for quicker review of the additional details that are found in the AP file that aren't directly in GLPOST (tax treatment, vendor details, remit to locations, etc) when our staff is reviewing a client's data.
You can't rely on a direct link from GL to APIxxx because batches can post but error out, hence not be in the GL. Go from GL to APPJH, then to APOBL.
Thanks for the input.
As suggested, I tried this reconciliation using the posting journals instead. That solves the positive/negative variances that I was experiencing, but item #2 from my initial post is still a concern.
Any ideas why the second part of the DRILLDWNLK is sometimes differing from APPJH's CNTBTCH?
I appreciate the help,
Any relationship between GL posting sequences and AP posting sequences, or GL batch numbers and AP batch numbers, is coincidental. DRILLDWNLK takes you back to APIBH but not any further, i.e., APIBD.
In reply to Jay Converse Arxis:
Why was this moved to the CRM Forum? There's no CRM in this question.
We discontinued one of our less used Sage 300 ERP CRM forums and this post was originally in there, and as such, it was migrated to one of the remaining Sage 300 ERP CRM forums. I'll look at moving it somwhere else more appropriate within the Sage 300 ERP Support Group.
© 2016 The Sage Group plc, its licensors or its affiliated companies. Sage, Sage logos, and Sage product and service names mentioned herein are the trademarks of The Sage Group plc, its licensors, or its affiliated companies. All other trademarks are the property of their respective owners. For more information, visit www.sage.com.
Community Terms of Service | Community Guidelines | Blog List | Community Help