Tables and Data Flow of the Accpac Accounts Payable Module
This blog posting will be looking at the structure of some of the parts of the Accounts Payable module of the Sage ERP Accpac product. You can find the structure of the individual tables in the Accpac Application Object Model (AOM), which must be viewed in IE. However this doesn’t tell you how the tables are related or how data flows from one table to another as you post various transactions. There are 78 tables in A/P, so obviously we can’t describe each in a single blog post. So we’ll just look at a few interesting cases. Understanding these relationships can be especially important to people writing sophisticated custom reports or BI Views.
The main types of database tables in A/P can be categorized as:
- Master Data
- Data Entry
- Data Processing
- Periodic Processing
Accounts Payable controls and tracks paying all a company’s vendors. The Purchase Order module will feed documents into A/P when vendors need to be paid. A/P will then feed all monetary transactions into G/L for recording. A/P also interacts closely with Bank services for printing checks and handling the whole check printing/reconciliation cycle.
The Setup Tables are:
A/P has 4 options tables. The reason is to separate out the functionality to reduce contention on these files. This way updating the various invoices and other document numbers doesn’t affect the other tables.
APP01 (AP0001): Company Options.
APP02 (AP0002): Invoicing and Numbering.
APP03 (AP0003): Payment and Aging.
APP04 (AP0004): Integration.
Other Setup Tables
APCLX (AP0007): 1099 Class Codes.
APCCS (AP0013): 1099/CPRS Amounts.
APRTA (AP0012): Terms.
APRTB (AP0011): Terms Payment Schedules.
APSLH (AP0035): Payment Selection Codes. Selection Criteria Header.
APSLD (AP0036): Selection Code Details. Selection Criteria Details.
APSLHO (AP0411): Selection Criteria Header Optional Fields.
APRAS (AP0006): Account Sets.
APRDC (AP0005): Distribution Codes.
APDSH (AP0009): Distribution Sets Headers.
APDSD (AP0008): Distribution Set Details.
APPTP (AP0010): Payment Codes.
APMSG (AP0120): E-mail Messages.
APGLREF (AP0121): G/L Reference Integration.
APOFH (AP0501): Optional Field Locations
APOFD (AP0500): Optional Fields.
The main master data file for A/P is the Vendors. Then secondarily we have Vendor Groups. I included the open documents under vendors since they are details, even though they are more a part of posting.
APVGR (AP0016): Vendor Groups.
APVGRO (AP0408): Vendor Group Optional Fields Values.
APVGS (AP0017): Vendor Group Statistics.
APVEN (AP0015): Vendors.
APVENO (AP0407): Vendor Optional Field Values.
APVCM (AP0014): Vendor Comments.
APVSM (AP0019): Vendor Statistics.
APVNR (AP0018): Remit To Locations.
APVNRO (AP0409): Remit-To Location Optional Fields.
APOBL (AP0025): Open Documents (updated only by postings).
APOBS (AP0026): Open Payment Schedules.
APOBP (AP0027): Document Payments.
Generally data flows into A/P starting as an Invoice. Whether it is entered in A/P or fed in from another application like P/O. Then as the invoice is paid, payment batches record this and mark payments against the Invoices.
APIBC (AP0020): Invoice Batch Control.
APIBH (AP0021): Invoices Headers.
APIBD (AP0022): Invoice Details.
APIBT (AP0024): Invoice Detail Comments.
APIBDO (AP0401): Invoice Detail Optional Fields.
APIBS (AP0023): Invoice Payment Schedules.
APIBHO (AP0402): Invoice Optional Fields.
Payment and Adjustment Entry
Both adjustments and payments share the same set of database tables. In the APBTA Batch record the first field is PAYMTYPE which controls whether the batch is payment or adjustment. It is AD for Adjustment and PY for Payment.
APBTA (AP0030): Payment and Adjustment Batches.
APTCR (AP0031): Payments/Adjustments
APTCP (AP0033): Applied Payments.
APTCU (AP0034): Adjustment G/L Distributions.
APTCN (AP0032): Miscellaneous Payments.
APTCRO (AP0406): Payment/Adjustment Optional Fields.
APPOOP (AP0048): Populates Payment List.
APCTRL (AP0057): Payment Control.
APSYGN (AP0056): Generate Payments Batch.
Batches are posted entry by entry. All the audit files are added to while posting and all statistics are updated along with all the various posted document tables.
APIVPT (AP0039): Superview to Post invoices.
APPYPT (AP0040): Superview to Post payments/adjustments
APTRK (AP0037): Payment GL Transactions.
APPJS (AP0512): Posting Journals.
APPJH (AP0511): Posting Journal Headers.
APPJHO (AP0514): Posting Journal Header Optional Fields.
APPJD (AP0510): Posting Journal Details.
APPJDO (AP0513): Posting Journal Detail Optional Fields.
APPTER (AP0038): Posting Errors Messages.
There are a large set of superviews that perform various periodic processing functions. These Views typically don’t have tables behind them and perform maintenance type operations. Some like year end or currency revaluation are quite major operations in their own right. Some of these include:
APPREN (AP0049): Fully Paid Docs and Journals.
APPGCM (AP0050): Vendor comments.
APPGST (AP0051): Stats and 1099/CPRS amts.
APBCTU (AP0059): Deleted and posted batches.
APPGIA (AP0052): Delete inactive records.
Year End and Revaluation
APYREN (AP0044): Year End.
APUNGL (AP0041): Revaluation.
APRVL (AP0063): Revaluation Details.
APRVLO (AP0410): Revaluation Optional Fields.
The whole process of printing checks is fairly complicated. To print checks, A/P writes the check information into the BKCHK table in Bank Services. Then it invokes the BK5000 UI to actually drive the check printing. The process works like this so it can be shared by other check printing applications like U.S. and Canadian Payroll. BKCHK doesn’t contain all the check information, only the main header information then the A/P Check form joins back to the A/P tables to get the full set of information the check and advice forms.
APCHKS (AP0058): Print Checks.
APADV (AP0060): Payment/Check Advices.
APBPPU (AP0055): Update Print Status.
Statistics and Aging
APSTAT (AP0047): Update Statistics.
APAGE (AP0043): Age Documents.
Create G/L Batch
APGLTR (AP0042): Create GL batch.
APINTCK (AP0045): Integrity Checker.
APINIT (AP0100): Activation.
APDLDN (AP0062): Drill Down. Called from G/L to allow G/L to drill down into A/P source documents.
Hopefully this blog posting provides a bit more insight into how A/P operates and hopefully helps when you use A/P, interface to A/P or are creating custom A/P reports.