Tables and Data Flow of the Sage 300 ERP System Manager Module
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:
- 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:
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.
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.
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.
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.
CSSKAP (CS0032): Schedule Application Links.
CSSKCB (CS0034): Schedules.
CSSKID (CS0036): Access to Scheduling.
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.
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.
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.
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.
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.