Stephen Smith's Blog

Musings on Machine Learning…

Posts Tagged ‘administrative services

Tables and Data Flow of the Sage 300 ERP System Manager Module

with 8 comments

This blog posting will be looking at the structure of some of the parts of the System Manager modules for the Sage 300 ERP product (previously known as Sage ERP Accpac). You can find the structure of the individual tables in the Sage 300 ERP 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. The System Manager tables consist of those of the mini-application modules that used to be bundled with System Manager (now just bundled with Sage 300 ERP). These include the 22 tables in Common Services, the 30 tables in Bank Services, the 9 tables in Tax Services and the 5 tables in G/L Sub-ledger Services. 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 Sage 300 ERP applications can be categorized as:

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

The System Manager tables contain information that is used in common by many of the main large Sage 300 ERP accounting modules like Account Receivable or Purchase Orders. The main concentration of tables is of the setup variety since these are setting up the company wide options and properties for the rest of the system.

Administrative Services keeps a few tables outside of the main database. Typically these are required to sign-on to the main database. These include the list of companies you can sign on to and the file with all the user ids and passwords. These are stored in a proprietary ISAM format derived from the old DOS based Accpac Plus database system.

A number of Currency and Security related tables are stored in the System Database. A System Database is shared by a number of Company Databases so that you can update this information in one place and have it used by many companies. The data in the System Database is replicated into each Company Database; so that when we are running we don’t need to do cross-database joins or open extra database connections to get this information. This way we get the advantages of sharing without the overhead of using extra database related resources.


There are many setup tables across the System Manager functions:

Bank Services

BKOPT (BK0010): Options.

BKTT (BK0003): Distribution Codes (formally known as transaction types).

BKTTX (BK0860): Bank Distribution Codes Tax Data.

BKDISTH (BK0445): Bank Distribution Set Headers.

BKDISTD (BK0440): Bank Distribution Set Details.

BKCCTYP (BK0240): Credit Card Types.

BKGLREF (BK0470): Bank G/L Integration.

Tax Services

TXAUTH (TX0002): Tax authorities.

TXCLASS (TX0001): Tax classes.

TXGRP (TX0003): Tax groups.

TXRATE (TX0004): Tax rates.

TXMATX (TX0902): Tax rate matrix. TXMATX controls interaction with TXRATE.

Administrative Services

A4WUSER (AS0003): Users. This is an ISAM file and not in the database.

A4WCUST (AS0004): Customization directories. This is an ISAM file and not in the database.

ASORGS (AS0020): Companies.

CSSEC (AS0001): Security groups. This is in the system database and cloned to all attached company databases.

CSAUTH (AS0002): User authorizations. This is in the system database and cloned to all attached company databases.

CSUICSH (AS0005): UI Cust. Profile Headers. This is in the system database and cloned to all attached company databases.

CSUICST (AS0006): UI Cust. Profile Details. This is in the system database and cloned to all attached company databases.

CSUSCST (AS0007):  User UI Customizations. This is in the system database and cloned to all attached company databases.

Common Services

CSCOM, CSCOM2 (CS0001): Company profile. (One view, two database tables).

CSFSC (CS0002): Fiscal calendars.

CSOPTFH (CS0011): Optional field headers.

CSOPTFD (CS0012): Optional field values.

All the currency tables are in the system database and cloned to all attached company databases.

CSCCD (CS0003): Currency codes.

CSCRT (CS0004): Rate types.

CSCRH (CS0005): Currency tables.

CSCRD (CS0006): Currency rates.

CSEUR (CS0010): Euro fixed conversion rates. This was only used during the transition period to the Euro. If the Euro falls apart, we may need one to transition out of the Euro.

CSSKTB (CS0030): Schedules.

Optional Details:

CSSKAP (CS0032): Schedule Application Links.

CSSKCB (CS0034): Schedules.

CSSKID (CS0036): Access to Scheduling.

Master Data

The only master data in System Manager is the always important Bank Accounts.

BKACCT (BK0001): Bank Accounts.

BKCUR (BK0002): Currencies.

BKFORM (BK0008): Check stocks.

BKACCT has many more detail views to do with checks, deposits, entries and NSFs; but, we’ll cover those in the following sections.

Data Entry

Reconcile Statements

Most documents like checks, deposits, NSFs, etc. actually come from the sub-ledgers. The Bank module is mostly to reconcile these once your bank statements come in. However you do need to enter Bank entries and to start you need to enter some manual checks and deposits to setup the Bank balances correctly. This section covers all the tables involved in the process of entering bank documents and reconciling them. In Bank nearly everything is a detail of BKACCT, so this appears again at the top.

BKACCT (BK0001): Banks.

BKTRANH (BK0845): Transaction Headers (can be withdrawal or deposit).

                BKTRAND (BK0840): Transaction Details.

BKENTH (BK0665): Bank Entry Headers.

                BKENTD (BK0660): Bank entry details.

BKDCI (BK0104): Direct clearing interface. Superview on top of BKTRAN to help the UI reconcile withdrawals and deposits.

Journal Entry

The entry function in G/L Sub-ledger Services handles the case of whether G/L is activated or not. Rather than sub-ledgers writing to G/L directly, they write to these views. Then sub-ledger services either writes these through to G/L, or if G/L isn’t present then it writes them to CSV files that can be imported into another G/L.

GPGLPO (GP0950): Transaction transfer. The superview stores data in the following views:

GPGLBB (GP0100): Subledger transactions.

GPGLBH (GP0120): Subledger entries.

GPGLBD (GP0110): Subledger details.

Data Processing

Bank has two big processing functions, namely printing checks and then posting reconciliations. Tax Services then has calculating sales tax and keeping sales tax records.

BKREG (BK0009): Check register. Sub-ledgers put the checks they want printed here, then printing of checks is done through an icon in bank BK5000 – Check print User Interface.

BKPOST (BK0101): Reconciliation posting.

BKJCTL (BK0020): Posting journal control.

BKJRNL (BK0011): Posting journal.

BKJTRANH (BK0655): Bank journal transaction header.

BKJTRAND (BK0650): Bank journal transaction details.

BKJENTH (BK0665): Entry posting journal header.

                BKJENTD (BK0660): Entry posting journal details.

BKJERR (BK0012): Posting journal errors.

TXCALC (TX0901): Tax calculation (low level engine).

TXDCALC (TX0910): Tax document calculation (higher level engine).

TXALLOC (TX0904): Tax allocation (superview).

TXAUDH (TX0011): Tax tracking.

TXAUDD (TX0012): Tax tracking details.

Periodic Processing

There are a set of superviews that perform various periodic processing functions. These Views typically don’t have tables behind them and perform maintenance type operations. Some of these include:

BKCLEAR (BK0103): Clear reconciliation posting journals.

BKDCHK (BK0100): Bank Integrity checker.

TXCLEAR (TX0903): Tax audit clearing.

TXDCHK (TX0990): Tax integrity checker.

ASINTCHK (AS0010): Admin Services Integrity Checker.

ASINTPR (AS0023): Data Integrity checker superview.

ASRST (AS0021): Restart Records. (Actually an ISAM file and not in the database).

CSDCHK (CS0009): Common Services Integrity checker.

GPGLFMT (GP0800): Segment substitution.

GPGLPE (GP0930): Transaction transfer.

GPDCHK (GP0099): G/L Sub-ledger Services Integrity checker.


Hopefully this blog posting provides a bit more insight into how the various System Manager modules operates and hopefully helps when you use them, interface to them or are creating custom reports.


Written by smist08

December 17, 2011 at 5:40 pm