Tables and Data Flow of the Accpac General Ledger Module
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:
- Master Data
- Data Entry
- 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:
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.
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.
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.
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.
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.