Stephen Smith's Blog

All things Sage 300…

Accpac A/R Database Structure

with 10 comments

This blog posting will be looking at the structure of some of the parts of the Accounts Receivable module of the Sage ERP Accpac product. You can find the structure of the individual tables in the Accpac Application Object Model (AOM) which you must view 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 103 tables in A/R, 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/R can be categorized as:

  • Setup
  • Master Data
  • Data Entry
  • Processing
  • Periodic Processing

Setup Tables

Setup tables tend to be fairly simple and their usage can be easily discerned from the AOM. These include things like ARDUN (Dunning Messages), ARRBC (Billing Cycles), ARRDC (Distribution Codes), ARRTA (Terms Codes) and such.

Master Data

The main master data table in A/R is the Customer table ARCUS.  ARITH (Items) is another master data table. The A/R Customers table is sync’ed with the companies table in SageCRM if the integration has been activated. The A/R Customers Optional Fields table (ARCUSO) is a detail under the ARCUS table. A detail means that there are multiple records for each ARCUS record. Some other tables are closely related to the ARCUS table and linked to it, namely:

  • ARCSM                 AR0022                 Customer Statistics
  • ARCSP                   AR0023                 Ship-to Locations
  • ARCMM               AR0021                 Customer Comments

Master data tables are like setup tables, but tend to contain the main data for the application, as opposed to a few records that configure the application.

Data Entry

Invoice Entry is the main set of tables for holding A/R Invoices. There is a table for the batch and then a table for the headers (there are multiple headers per batch) and a table for the invoice detail lines (again multiple per header). There are other detail views shown below where indentation indicates that there are multiple records for the table above.

  • ARIBC                    AR0031                 Invoice batches
    • ARIBH   AR0032                 Invoices
      • ARIBD                   AR0033                 Invoice Details
        • ARIBDO                                AR0401                 Invoice Detail Optional Fields
    • ARIBS                    AR0034                 Invoice Payment Schedules
    • ARIBT                    AR0035                 Invoice Detail Comments
    • ARIBHO                                AR0402                 Invoice Optional Fields

Receipt and Adjustment Entry is the set of tables for holding both receipt and adjustment batches. The first field of each batch record indicates the batch type.

  • ARBTA                  AR0041                 Receipt/Adj. Batches
    • ARTCR   AR0042                 Receipts/Adjustments
      • ARTCP   AR0044                 Applied Receipts/Adj.
      • ARTCU  AR0045                 Adjustment GL Distributions
      • ARTCN  AR0043                 Miscellaneous Receipts
      • ARTCRO               AR0406 Receipt/Adj Optional Fields

Refunds are the set of tables that refund documents are entered into.

  • ARRFB                   AR0140                 Refund Batches
    • ARRFH  AR0141                 Refund Entries
      • ARRFD  AR0142 Refund Details
        • ARRFDJ AR0145                 Refund Detail Jobs
    • ARRFHO               AR0143                 Refund Options Fields


Posted documents end up in the following tables:

  • AROBL                  AR0036                 Documents
    • AROBS  AR0037                 Document Sched Payments
      • AROBP  AR0038                 Document Payments
      • AROBLJ AR0200                 Open Document Details

When you post documents you get the following general data flows.





Note that for each ARTCP record you get 2 AROBP records. One attached to the Invoice’s AROBL which is positive to reduce the invoice and one attached to the Receipt’s AROBL which is negative to reduce the receipt.


Let’s look at an example where we enter and post an Invoice and then enter and post a Receipt for that Invoice. Sometime it helps to do small transactions in Accpac and then look at the records produced in a database tool like the SQL Server Management Studio.

First let’s enter an Invoice, here are the main Invoice tables showing the values of a few key fields:

ARIBC    Batch     Batch Total               Number of Entries
         36              .44                        1

ARIBH   Batch     Entry     Customer            Document
        36           1      1100                IN0000012

ARIBD Batch       Entry     Line        Item         Quantity         Price
       36           1       20          CA-78           1             2.25

Now we post the Invoice which generates the following AROBL/AROBLJ records:

AROBL  Customer       Document Batch Entry Invoice Amount
        1100          IN0000012 36   1        2.44
AROBLJ Customer Document Line       Item      Quantity
        1100    IN0000012           CA-78       1

Now let’s enter a receipt for this Invoice:

ARBTA  BatchType     Batch    Entries          Total
         CA           40         1             2.44
ARTCR Type Batch     Entry Check#     Customer
      CA     40       1     1111       1100
ARTCP Type Batch       Entry Line     Customer Document    Amt
      CA    40           1   20       1100      IN000012   2.44

Now we post the Receipt applying it to the previous Invoice and get:

AROBL  Customer      Document Batch Entry   Invoice Amount
        1100         PY000077  40   1         -2.44
AROBP  Customer     Document Line Check#           Amt
       1100         IN0000012      1111           -2.44
       1100         PY0000077      1111            2.44

Audit Files

Below are the main audit files that contain the A/R Audit History:

  • Posting Journals/GL Transactions: ARPJS → ARPJH → ARPJD
  • Item Sales History: ARITH → ARITS
  • Customer Statistics: ARCUS → ARCSM


Statements are generated by the ARSTMT processing View (AR0077), this isn’t a table but it generates the data for a statements run. The data that it generates are in the following tables:

  • ARSTRUN                            AR0110                 Reprint Statement Header
    • ARSTCUS             AR0111                 Reprint Statement Customers
      • ARSTOBL              AR0112 Reprint Statement Customer Invoices
        • ARSTOBP             AR0113 Reprint Statement Customer Receivables
    • ARSTNAT             AR0114 Reprint Statement NAT Customers

This data is only cleared by Clear History. When you generate the report the View returns the STMTSEQ which you have to set as a report parameter to get the correct statement.


Aging data is generated by the ARAGE (AR0055) Processing View. The data is generated each time aging is run and cleared the next time aging is run. To run Aging: set fields with options and call .Process on ARAGE. When done copy the AGESEQ field from the View to the AGESEQ report parameter. The tables that are populated are:

Aged Trial Balance: ARSLCUS → ARAGED


Customer statistics are stored in the ARCSM table which is a detail of the ARCUS table:


Once collecting statistics, do not change the accumulate statistics options as this will mess up the calculations since we don’t refactor already collected statistics. But if you do, they will eventually flush through the system.

If creating summary/dashboard type reports then it’s easier to get the data here than to add up totals from the posted transaction tables.

Searching Based on Document Number

A/R is document centered, given a document number; you can retrieve all the relevant information from Sage ERP Accpac.

For example, given an invoice IN0000001:

To find out its current balance (source and functional), original balance (source and functional), document date, discount, due date, etc:

  • Search AROBL table by the index IDINVC.

To find out the Invoice entry data

  • Search AROBL by the index IDINVC. The CNTBTCH/CNTITEM (BATCH and ENTRY) fields of AROBL point to the corresponding record(s) in ARIBC/ARIBH/ARIBD (BATCH/ENTRY are the primary key fields for those tables), which store the invoice entry.

To find out all the payments or adjustments against IN0000001:

  • Search AROBP by the IDINVC index, and you will find all the payments against this document.

To find out the G/L entries for this document:

  • First look up AROBL by IDINVC index to find out the posting sequence#, batch#, entry# for this document, and then search ARPJH table on the primary key  (TYPEBTCH,POSTSEQNCE,CNTBTCH,CNTITEM). TYPEBTCH is ‘IN’ for invoice, ‘AD’ for adjustment, ‘CA’ for payments. And the fields GLBATCH/GLENTRY in ARPJH point to the record in GLJEH.


The database schema structure of the Accpac Accounting modules can be quite complicated, but hopefully this article gives you an idea of how the main flow of data processing works in A/R.

Written by smist08

April 9, 2011 at 5:13 pm

10 Responses

Subscribe to comments with RSS.

  1. These types of posts are extremely helpful, Stephen, and many thanks for them.

    One question on the ARAGED table you mention. I have wondered before why the field names seem to imply ageing up to period 9. Is this correct?

    Steve Bagnall

    April 12, 2011 at 12:57 pm

    • Actually these aren’t fiscal periods they are the aging buckets. Only the first 5 are used and they correspond to the aging you set up in A/R Options on the statements tab. Usually something like Current, 0-30, 31-60, 61-90, over 90. I don’t know what the original intent of the other 4 was.


      April 12, 2011 at 5:43 pm

      • Thanks very much, that’s what I thought. The reason for the question is that we are always being asked by clients for more than 5 ageing buckets, so I hoped we could use them. Never mind.

        Sorry to ask such detailed questions which are probably outside the scope of this blog, but when the chance is there …

        Steve Bagnall

        April 13, 2011 at 10:25 am

    • If you look for aging report with additional aging buckets, filter by account sets at transaction level or by ship-to location, you might want to take a look at this link.


      June 28, 2013 at 4:08 am

  2. Hi Stephen,

    Great overview on data flow – am I correct in assuming that there is no way to use (vanilla) sData to enter a receipt into the system? I am using sData to enter orders, invoices, and shipping manifests, but am unable to enter a payment so far.

    Your overview on how this looks database-side is nice, but it doesn’t mention how things like auto-generated bank deposit sequence numbers and document numbers are created – this is where sData really shines, as it is able to perfectly mimic the auto-generation, etc.

    Is there a way to use sData to generate AR receipts? If not, is there a way to auto-generate these numbers without it?



    March 15, 2012 at 4:58 pm

  3. […] For a bit more information on the structure of the A/R tables used, check out this blog post. […]

  4. How does Accpac calculate the Total Days to Pay in the Activity Tab of A/R Customers?


    January 14, 2016 at 6:51 pm

    • Its the DAYSTOPAY field in the AR0024 (ARCUS) View.


      January 15, 2016 at 9:51 pm

  5. Hi my cash book is failing to post batches and is giving me this error unable to open table Accpac ARRFH aro141


    June 29, 2016 at 11:02 am

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: