Stephen Smith's Blog

Musings on Machine Learning…

Archive for December 2011

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.

 

Written by smist08

December 17, 2011 at 5:40 pm

Tables and Data Flow of the Sage 300 ERP Payroll Module

with 2 comments

This blog posting will be looking at the structure of some of the parts of the Canadian and U.S. Payroll 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. There about 99 database tables in either the U.S. or Canadian Payroll module, so obviously we can’t describe each in a single blog post. 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 Payroll can be categorized as:

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

The Payroll modules feed data into both General Ledger and Project and Job Costing. Payroll also interacts very closely with the Bank module for the processing of paychecks. Each Payroll module contains a main common portion which is mostly shared; these contain all the files that start with cp or up. Then for each Payroll there is a Payroll calculation module which has the country specific tables, reports and calculations; these include the tables that start with: CT or UT. Often as the government changes the Payroll rules and rates, we just need to updated and distribute updated CT and UT applications rather than the larger core CP or UP application.

In the descriptions below we will list the U.S. Payroll table name and View IDs. However to get the Canadian Payroll versions, just replace the UP with CP and you have it. Most of these are compiled from the same source code (which uses PR) and then UP or CP are substituted in as part of the product build process.

Incidentally this is the same Payroll module that is used in the Sage HRMS SQL Payroll product, so everything here applies there as well.

Setup

Setting up Payroll correctly is the most crucial task to have things run smoothly. If all the employees and their earnings and deductions are configured correctly then processing Payroll will go much smoother. The Setup Tables are:

UPOPTS (UP0023): Options.

UPCLAS (UP0006): Class codes.

UPDTLM (UP0007): Earnings/deductions. (UPDTLX, UP0099 is UP0007, without some of its functionality for faster read access.)

UPINCL (UP0015): Include list. Used by UPTXMS, too. Holds the applicable taxes for each E/D.

UPDIST (UP0009): Distribution codes.

UPWCCH (UP0036): Worker’s compensation master.

UPWCCD (UP0037): Worker’s compensation codes.

UPOTSC (UP0022): Overtime schedules.

UPSHFT (UP0025): Shift differential schedules.

UPSHFD (UP0038): Shift Differential Details.

UPSHFB (UP0039): Shift Differential Billing Details.

UPTXMS (UP0029): Company payroll taxes.

UPINCL (UP0015): Include list.

UPDIST (UP0009): Distribution codes.

UPTXMO (UP0124): Taxes Optional Field Values.

UPWRKC (UP0027): Work Classification Codes.

UPOFH (UP0120): Optional Field Locations.

UPOFD (UP0121): Optional Field Values.

UPGLREF (UP0057): G/L Reference Intergration.

Master Data

The main master data file for Payroll is the Employees. Then secondarily we have Employee selection lists.

UPEMPL (UP0014): Employees

UPEMPD (UP0008): Employee earnings/deductions.

UPEMPT (UP0010): Employee taxes.

UPEMPC (UP0053): Employee notes.

UPEMBK (UP0201): Employee EFT Banks.

UPEMPO (UP0122): Employee Optional Field Values.

UPEMTF (UP0062): Employee Tax Fields (Implicitly handled by UP0014).

UPESLH                (UP0045): Employee selection lists.

UPESLD (UP0046): Employee selection list members.

There is no master of monetary balances as GL has.  The detail for all checks issued is kept in the following, which serves as a master and detail archive all in one.

UPCHKH (UP0048): Check Header. (Holds completed and un-posted checks.)

UPCHKD (UP0049): Check Details.

                UPCHDO (UP0134): Check detail optional field values.

                UPCHJB (UP0056): Check job details.

                                UPCHJO (UP0144): Check job details optional field values.

UPCHKC (UP0052): Check comment details.

UPCHKE (UP0202): Check EFT details.

UPCHHO (UP0133): Check header optional field values.

Data Entry

The main data entry task for Payroll is the entry of Timecards. A common integration is for ISVs to feed Timecard data into Payroll from an external system (like a time clock check-in system). You may also need to enter some manual checks.

UPTCHD (UP0031): Timecard headers.

UPTCDT (UP0032): Timecard details.

                UPTCDO (UP0128): Timecard detail optional field values.

                UPTCJB (UP0042): Timecard job details.

                                UPTCJO (UP0141): Timecard jobs optional field values.

UPTCHO (UP0127): Timecard optional field values.

UPMCHD (UP0019): Manual check headers

UPMCDT (UP0020): Manual check details.

                UPMCDO (UP0130): Manual check detail optional field values.

                UPMCJB (UP0043): Manual check job details.

                                UPMCJO (UP0142): Manual check job detail optional field values.

UPMCHO (UP0129): Manual check optional field values.

Data Processing

The Calculate Payroll process is the central part of Payroll. This takes all the setup, employee and timecard data and calculates the Payroll. This includes all taxes and deductions, sets up everything to print checks and advice and record all the values that need to be accrued.

UPCALC (UP0083): Superview to calculate payroll.

UPCLCO (UP0137): Calculate payroll optional field values. Since UPCALC has no data, these optional fields have no header.

UPCALE  (UP0084): E/D/T List, helper to UP0083

UPTXMC (UP0100): Include List, table helper, used internally in conjunction with Tax Master by UP0084.

UPCALT (UP0085): Timecard List, helper to UP0083.

UPCALA (UP0086): Cost Center Allocations (there is data here).

UPCHKR (UP0088): Check Run Header, temporary table helper to UP0083.

UPCTRL (UP0026): Concurrency Control.

UPXCPT (UP0064): Calculate Payroll Exceptions, produced by UP0083 if errors detected.

Once the calculate Payroll is performed, the next big process is printing all the checks.

UPPMAN (UP0065): Process Manual Checks (called by UP0078 , UI ‘Process’ button, and Process Icon).

UPCTRL (UP0026): Concurrency Control.

UPXCPT (UP0064): Calc Payroll Exceptions, produced by UP0065 if errors detected.

UPHHAU (UP0021): History Audit Headers.

UPHHOA (UP0135): History audit header optional field values.

UPHDAU (UP0024): History Audit Details.

                UPHDOA (UP0136): History Audit detail optional field values.

UPPCKS (UP0078): Print Checks, controls the Print process and calls Post (UP0050).

UPPCKF                (UP0089): Print Checks Trans, temporary helper to UP0078.

Updates the following staging tables for printing. When you print checks, part of the header is transfer to the BKCHK table which drives check printing. Then the Payroll Crystal check forms will join back to the following two tables to get all the Payroll specific data they need for the check and advice.

UPPCKH (UP0079): Print Checks Header.

UPPCKD (UP0080): Print Checks Details.

Post All Checks

Called internally by UP0078, UP0083 and UP0012.

UPPOST (UP0050): Post checks.

Updates the following tables as needed:

UPMCHD (UP0019): Manual Checks Header

UPMCDT (UP0020): Manual Checks Detail

UPCHKH (UP0048): Check Header

UPCHKD (UP0049): Check Detail

UPYTDS                (UP0047): Employee YTD summaries

CPEUIC (UP0030): Employee EI history (Canadian payroll only).

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:

UPMAIN (UP0018):  Delete inactive records.

UPDCHK: (UP0016): Integrity checker.

Update All employees:

UPGMET (UP0058): Globally modify process.

Uses the following table:

UPGLBM (UP0051): Globally modify list.

Summary

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

 

Written by smist08

December 10, 2011 at 7:45 pm

Unit Testing Web UIs in Sage 300 ERP

leave a comment »

Introduction

Unit Testing is a technique to test individual units of source code. Usually in the Java world this means a set of tests to test an individual class. The idea is to test the class (or unit) in complete isolation from the rest of the system, so calls to other parts of the system would be stubbed or mocked out. Unit test are typically written by the software developer to perform white box testing for their class. Unit testing is only one sort of testing there would be other types of testing for various integration testing, user testing, load testing, manual testing, etc.

The goal of unit testing is to show that the individual parts of a program are correct and then to deliver the following benefits:

  • Establish a well-defined written contract of what the code must do.
  • Find problems early in the development cycle.
  • Facilitate change and refactoring since you can rely on the unit tests to prove things still work.
  • Simplify integration since you know the building blocks work correctly.
  • Provide documentation, the unit tests show examples of actually using the class being tested.

In Extreme Programming and Test Driven Development, the unit tests are written first and then passing the unit tests acts as an acceptance criterion that the code is complete.

JUnit

JUnit is a unit testing framework for Java programs. It is integrated into most IDEs like Eclipse and it is easy to run the tests from build tools like Ant. Usually in Eclipse you have a test tree with all the test classes, usually a test class for each real class and the test class containing all the tests for the real class. JUnit support a number of annotations that you put on the methods in the test class to specify which ones are tests to run as well as optionally specifying any order or dependency requirements (the best unit tests run completely independently of each other). Then it adds a number of methods to call to test for pass and failure of the tests (via various forms of assert) and to report the results back to the test framework.

GWT

The Google Web Toolkit (GWT) supports unit testing with something called GWTTestCase which bridges GWT to JUnit. If you want to interact with GWT controls then you have to use this. However it’s relatively slow. You want unit tests to run blazingly fast. A unit test should only take a few milliseconds to execute. If it takes longer then it won’t be run often. You want it so your unit tests can be run every time you compile and so they don’t slow you down as you work.

We use GWTTestCase to test our extension of the GWT CellTable widget. These tests take 10 minutes to run. This is way too slow to expect them to be run on a regular basis. Hence they don’t provide the immediate feedback to a developer working on this widget that we would like.

Fortunately since in GWT you are writing all your classes in Java, if you structure your program correctly you can have most of your classes not interact with GWT or easily mock the GWT part of it. Then you can use JUnit directly to unit test your program.

MVC

One of the key goals of us enforcing MVC design patterns on our developers creating UIs is to facilitate good unit testing. This way if most of the code that needs testing is the Model and Controller, these can be tested with JUnit and we don’t need to use GWTTestCase. This greatly simplifies unit testing and greatly improves the productivity and speed of the tests. Usually our View part is very small and mostly implemented in the SWT framework, so the unit testing is then in framework unit tests (many of which are GWTTestCase), and not in the actual UIs.

EasyMock

EasyMock is a library that can dynamically “mock” classes that aren’t part of the test. With unit tests we only want to test the one class at a time and we want the tests to run quickly. We don’t want to require a particular database be in place and then have to wait for the tests to run as it makes database queries. We want the tests to run in milliseconds and we want the tests to run in spite of what might be happening in the rest of the system. To this end the test framework needs to replace the real classes that the class being tested calls and interacts with, with something appropriate for testing. These test replacement classes then have the ability to return unexpected or rare results such as network disconnection errors, or other rare hard to setup type of scenarios. One way to do this is to write “stub” classes that replace the real classes and then only have code to do what is required for the testing. Writing and maintaining stub classes is difficult since you need to keep them in sync with the classes they are stubbing; keeping these correct can become a major amount of work.

EasyMock offers an easier way. It generates the classes on the fly using Java’s proxy mechanism. This way the class interface is always up to date since it is generated from the real class. You first run in a “recording” mode where you record what you want the various methods to return to the test cases. Then you switch to “play” mode and run the tests.

For this to work effectively, you must design classes so classes they use can be mocked. This usually means creating them externally and then passing them to the constructor rather than creating secret internal instances. Generally this is good object oriented design anyway and usually difficulty in constructing unit tests is a “code smell” that something is wrong with the design.  For simple utility classes or simple classes in the same package, it’s often easier to leave them in place rather than mock them, then they get a bit of testing as well and as long as they run quickly, shouldn’t be a problem.

Example

Below is one of the tests from the BOM Detail popup form in Order Entry. This shows the structure of a JUnit test and shows how to mock a big class (namely the Model) that this class interacts with. Notice that the Model is created externally to this class and passed into the constructor, so it is easy to mock. First we record what the mock class should do and then we switch to replay mode to do the actual test.

public class NestedViewBOMDetailsCallbackTest
{
private static final int TEST_PARENT_COMPONENT_NUMBER = 42;

/**
* Tests that the callback’s onPopupClosed handler will filter on the child
* BOMs of the parent component number that was passed in at callback
    * creation time.
*/
@Test
public void testOnPopupClosedFiltersOnChildrenOfCallerParentBOMNumber()
{
// Set up the mock model and the expected method calls that should be
// made on it by the class under test.
OEDocumentBOMDetailsModel mockModel = EasyMock.createMock(OEDocumentBOMDetailsModel.class);
mockModel.filterOnChildBOMsOf(EasyMock.eq(TEST_PARENT_COMPONENT_NUMBER), EasyMock.isA(EntryFilterCallback.class));
EasyMock.expectLastCall();

// Tell EasyMock we’ve finished our setup so that any time we interact
// with the mock, EasyMock records what calls were made on the mock.
EasyMock.replay(mockModel);

// Call the class under test to exercise the method under test.
NestedViewBOMDetailsCallback callback = new NestedViewBOMDetailsCallback(mockModel, EasyMock
            .createMock(BOMDetailsView.class), TEST_PARENT_COMPONENT_NUMBER);
callback.onPopupClosed(null);

// Check that the expected method calls on the mock happened when we
// exercised the method under test.
EasyMock.verify(mockModel);
}
}

Summary

Unit testing is an effective form of testing that does find quite a few bugs and helps keep code running smoothly as a system is developed and refactored going forwards. Sometimes the discipline of unit testing forces programmers to ensure they fully understand a problem before coding resulting in a better solution. Certainly this can’t be the only form of testing, but it is an important building block to a quality software system.