Stephen Smith's Blog

All things Sage 300…

Tables and Data Flow of the Accpac General Ledger Module

with 16 comments

This blog posting will be looking at the structure of some of the parts of the General Ledger 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 60 tables in G/L, 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 G/L can be categorized as:

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

General Ledger is the foundational accounting module in Sage ERP Accpac. All numbers eventually flow into G/L (sometimes by a round-about route). Below is a data flow diagram showing how all the other modules feed G/L. Then G/L has sufficient information to provide a complete audit history of a company as well as all the data to prepare Financial Reports.


The Setup Tables are:

GL01 (GL0005): Options. Various options for the G/L modules plus some contact information.

GLABK (GL0022): Segments. Defines all the account segments. For instance account segment region is 4 characters long. These are then the building blocks to build G/L account structures out of.

GLASV (GL0021): Segment codes. Holds the valid values for segments that are validated.

GLABRX (GL0023): Structure Codes. Builds G/L account structures out of combinations of segments.

GLSRC (GL0002): Source Codes. Defines the G/L source codes including a description.

GLSJN (GL0019): Source Journal Profile. Used to configure the source journals report. Specify which sources you want in a report.

GLRVAL (GL0020): Revaluation Codes. Configuration for performing currency revaluations.

GLGSSEG (GL0052): Segment Permissions. Defines segments a user has access to.

GLGSACC (GL0053): Account Permissions. Defines accounts a user has access to.

GLGSUSR (GL0054): GL Users. Users defined to G/L security.

GLACGRP (GL0055): Account Groups. Define the G/L Account Groups.

GLACHD (GL0057): Rollup Groups. Define the G/L Rollup Groups.

GLADSD (GL0058): Rollup Group Relationships.

Many of these setup tables are used to define the GL account structure. How these are used and interact is laid out in the following diagram:

Master Data

The main master data file is the GL chart of accounts table (GLAMF). This table has several detail tables also described below. The other master file is the GL fiscal sets files (GLAFS) which holds all the fiscal set balances by account, year and period. The fiscal sets are then used in financial reporting.

GLAMF  (GL0001): Chart of Accounts. The main list of all the G/L accounts. With detail views:

GLAIS (GL0004): Account Allocation

GLAVC (GL0012): Account Valid Currencies

GLCAS (GL0107): Sub-ledger Control Accounts

GLAMFO (GL0400): Account optional fields

GLAMFTO (GL0401): Account transaction optional fields

GLAFS (GL0003): Fiscal sets. Summarized fiscal set data, usually used for financial reporting. Strictly speaking this is also a detail of GLAMF, but tends to be used as the header when doing reporting.

Data Entry

All data that flows into G/L is via the set of Views/Tables: GLBCTL, GLJEH, GLJED (and some other composites).  All data from everywhere else in the system feeds into these tables. These are the unposted batches. Data is never written directly into the posted transaction or audit table files. All data goes here first and then posting is controlled by G/L.

GLBCTL (GL0008): GL Batch Control. One of these for each G/L Batch

GLJEH (GL0006): Journal Headers. One of these for each entry in a batch.

GLJED (GL0010): Journal Details. One of these for each detail line in an entry.

GLJEC (GL0007): Journal Comments. Holds the detail comment if there is one.

GLJEDO (GL0402): Journal Detail Optional Fields. Hold the optional fields for each detail line.

Limited validation is performed on these items as they are entered. This is because they are usually posted in their source application and cannot be edited there. Then in G/L data errors can be corrected by someone with sufficient privileges to allow the batch to be posted. Generally posting is the data integrity gate keeper here and not data entry.

Data Flow

Below is a diagram showing the data flow of data from outside of G/L into the G/L data entry views. Then it shows how posting takes that data and distributes it to the audit and other tables.

Data Processing

The GLJPST (GL0030) superview is the Accpac View that performs G/L batch posting. This view processes the specified batches, one entry at a time and posts them to the audit files and the fiscal set records. It posts each entry in a batch as a separate transaction. Some of the Views and Tables involved in posting are:

GLJPST  (GL0030): Batch Posting. Not a table, but the superview that performs the posting function.

Posting Journal Audit

GLPJD  (GL0016): Posting Journal Details.

GLPJC  (GL0017): Posting Journal Comments.

GLPERR (GL0015): Posting Errors.

GLPJDO (GL0404): Posting Journal Optional Fields.                            –

Posting Provisional Posting Audit. Provisional posting lets you provisionally (“temporarily”) post a batch to see what affect it has on Financial Reports or other G/L reports. These provisional posts don’t hit the regular posting audit files, but instead hit a set of provisional posting files. These are cleared the next time posting is run.

GLPPD  (GL0015): Provisional Posting Journal Details.

GLPPDO  (GL0406): Provisional Posting Journal Optional Fields.

GLPPER  (GL0013): Provisional Posting Errors.

Main Posted Transactions:

GLPOST (GL0018): Posted Transactions.

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 create new year or currency revaluation are quite major operations in their own right. Some of these include:

GLYRM (GL0024): Period End Maintenance. Clears history and/or detail.

GLINTCK  (GL0034): G/L Integrity checker (called from the Admin Service Integrity Checker).

GLINIT  (GL0106): Activation. Called from the Admin Services Activation program.

GLJAL  (GL0029): Auto Allocation Processor.

GLRVLU (GL0027): Currency Revaluation.

GLYRN (GL0026): Create New Year.

GLCSL  (GL0025): Consolidation of History.


Hopefully this blog posting provides a bit more insight into how G/L operates and hopefully helps when you use G/L, interface to G/L or are creating custom G/L reports.


Written by smist08

July 3, 2011 at 9:01 pm

Posted in sage 300

Tagged with , ,

16 Responses

Subscribe to comments with RSS.

  1. It would be nice if we were able to have this for all the other modules

    Solomon Muwori

    July 6, 2011 at 9:42 am

    • Eventually. I’ll probably get to them all over time.


      July 6, 2011 at 3:12 pm

  2. […] blogged on the general structure of G/L here. This blog posting is going to look a bit more in depth into the structure of G/L Accounts and how […]

  3. Very useful, Thanks !

    Ludovic Fuzellier

    September 13, 2013 at 9:18 pm

  4. Would you happen to know of a query that will recalculate the GLAFS values so they are true to the GL. I have reason to believe an external process through these off.

    Ed Dewsnap

    May 7, 2014 at 9:31 pm

    • Nasty. Is running Data Integrity with fix minor errors any help?


      May 8, 2014 at 1:30 am

  5. Hi Steve,

    Is there a way to check that Year End maintenance is completed?

    Need to check for Year end activity is completed for a custom work after which the history trans will be purged. Right just ask user to confirm before purge.



    March 11, 2015 at 4:25 pm

    • Create new year creates a restart record, perhaps you could test of the presence of the restart record.


      March 11, 2015 at 5:27 pm

  6. Hi Steve,

    Thank you for the quick response.

    one more thing.. I could delete history trans from GLPOST but I couldn’t delete it from a GLAFS why? I couldn’t even trace the error. It didn’t show in neither in AccpacReport object or in Accpacsession object.

    Please explain when and how to delete the records from GLAFS table. I guess it is a flat view. please confirm.



    March 11, 2015 at 6:16 pm

    • You have to set the GLAFS view’s SystemAccess property to Posting.


      March 11, 2015 at 6:25 pm

  7. hi Steve,

    Thanks I did that and i noticed that it didn’t delete the record anyways.
    I am trying to delete all the records that are Income statement accounts.

    please help..



    March 11, 2015 at 6:34 pm

  8. Steve,

    Do i need to make the GL Account (Income Statement accounts) in GL master to “In Active” before deleting the records in “GLAFS” ?



    March 11, 2015 at 6:37 pm

    • The other thing is that this is a revisioned view which means you have to call Post after a series of operations to commit them to the database.


      March 11, 2015 at 7:49 pm

  9. Thank you very much Steve. After calling post method the operations are effected/worked pretty well.


    March 24, 2015 at 3:46 am

  10. we have a problem in the GL level which is the following:

    we have a batch with 151 entry in GL, we posted the batch with no errors but we surprised that GL transaction listing report shows only the first 81 entry while the others doesn’t appear. we checked both the GLPJD and GLPOST tables and found that they didn’t contain the remianing entries. could you please help us to know the reason behind this and how we can solve it.

    In the meanwhile we’re running the DIC for GL to guide us for any tip that could solve this issue.

    Nayef Soghayer

    June 3, 2015 at 8:19 am

    • Not sure. G/L does each entry as a separate database transaction. Usually if some don’t succeed you would get an error and then check the posting journal errors report for details. Even though you didn’t get errors, it might be worth checking this report. Also perhaps some of the entries didn’t have any detail lines? Did an error batch get created?


      June 8, 2015 at 5:57 pm

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: