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:

Setup

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.

Setup

The Setup Tables are:

Options

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.

Master Data

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.

Data Entry

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.

Invoice Entry

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.

Data Processing

Posting SuperViews

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.

Posting Journal

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.

Periodic Processing

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:

Clearing

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.

Printing

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.

Data Integrity

APINTCK (AP0045): Integrity Checker.

Activation

APINIT (AP0100): Activation.

Drill Down

APDLDN (AP0062): Drill Down. Called from G/L to allow G/L to drill down into A/P source documents.

Summary

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.

4 Responses

We don’t define the SData resources to do this, since we haven’t got to A/R yet. Even if you create the XML files to define these, I suspect you might have problems since the system may not handle the A/R style of Batch/Header/Detail protocol yet.