Stephen Smith's Blog

Musings on Machine Learning…

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.

Setup

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.

Summary

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.

 

Advertisements

Written by smist08

December 17, 2011 at 5:40 pm

8 Responses

Subscribe to comments with RSS.

  1. Hi,
    (1) Is this technical information available with the software?
    (2) Can you send me the download link of the evaluation copy of ERP 300, 2012 ? I don’t know from where to get it.

    Abbas Malik

    October 31, 2012 at 2:47 pm

    • There isn’t additional information with the product, its all here, on our website around the AOM or part of the SDK. To get the product you would need to contact the Sage office for your region, which depends where you are located.

      smist08

      November 3, 2012 at 10:56 pm

  2. Subject : “Journal Detail Attempt to change read-only field ‘Currency Rate” problem.

    Hi Stephen,

    I am migrating my Platinum 5.9 to Sage 300 ERP 2014 using Sage ERP Migrator. Journal entries process cannot complete because fo the following error: Journal entries attempt to change read-only field “Currency Rate” while migrating the Mulit-currency type Compay with Euro type currency.

    GLBCTLBB and other journal entries tables.

    Can you adivse me the possible cause? Thanks.

    Regards
    Jourden

    Jourden Lin

    April 28, 2015 at 4:13 pm

    • I don’t think you want to check the “Euro” button in Sage 300’s company profile. This was only for the Euro conversion period when you had to use triangulation to calculate currency exchanges. This option should really be removed now. If you check this then it will use all sorts of extra rules on exchange rates which you might be running afoul of.

      smist08

      April 28, 2015 at 5:05 pm

      • Hi Stephen,

        Thanks for your quick response.

        I did not check the “Euro” checkbox though. It is diabled.

        Our Europe Company will enter data in Euro in Europe while US Company will enter data in U.S. Dollar. They are all in one SQL database.

        When I activate the Europe Company in Sage 300 ERP 2014, I identify the Europe Comany as a multi-currency company. Because, the Company use the BU rate type to convert some its tranacations in current Platinum 5.9. I confirmed the following info in the Company profile->options.
        Multicurrency – checked,
        funtional currency: EUR.
        Default Rate Type: BU.
        Gain/Loss: Realized and Unrealized Gain/Loss.
        Euro: unchecked and disabled

        Defaults value in Migrator:

        Multicurrency – checked.

        Reulsts:
        Migrator tried to chenge the read only currate rate filed in Migration database and/or Sage300 database. Migrator could not load the journal enteries and I cannot post the transactions.

        Regards
        Jourden

        Jourden Lin

        April 28, 2015 at 6:01 pm

  3. Hi Stephen, sorry for the typing errors…

    Thanks for your quick response.

    I did not check the “Euro” checkbox though. It is diabled.

    Our Europe Company will enter data in Euro in Europe while US Company will enter data in U.S. Dollar. They are all in one SQL database.

    When I activate the Europe Company in Sage 300 ERP 2014, I identify the Europe Comany as a multi-currency company. Because, the Company use the BU rate type to convert some its tranacations in current Platinum 5.9. I confirmed the following info in the Company profile->options.
    Multicurrency – checked,
    funtional currency: EUR.
    Default Rate Type: BU.
    Gain/Loss: Realized and Unrealized Gain/Loss.
    Euro: unchecked and disabled

    Defaults value in Migrator:

    Multicurrency – checked.

    Results –

    Migrator tried to change the read only currency rate field in the Migration database and/or Sage300 database. Migrator could not load the journal enteries and I cannot post the transactions.

    Regards
    Jourden

    Jourden Lin

    April 28, 2015 at 6:04 pm

    • OK, some things to check:
      1. G/L is set to multi-currency in G/L Options
      2. The G/L Accounts being used are set to multi-currency
      3. The source currency entered is different from the home (funcitonal) currency.

      Plus if you try to do the same thing in G/L Journal Entry you might be able to get a more helpful error message. Similarly if posting fails, check the posting error report for any hints.

      smist08

      April 28, 2015 at 8:59 pm

      • Thanks, Stephen.

        I am checking the SQL database, I will share the findings with you. Appreciate for your help.

        Regards
        Jourden

        Jourden Lin

        April 29, 2015 at 12:08 pm


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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: