Stephen Smith's Blog

Musings on Machine Learning…

Archive for October 2011

Sage 300 ERP Web UIs and MVC

with 2 comments


We spent the last couple of blog posts talking about our new Sage 300 ERP (the product formally known as Sage ERP Accpac) server side programming framework and gave some examples. This week we are going to start turning our attention to the client side. We will be looking at how to structure the code in a UI form to create a well-structured Web Form, using SWT. We’ve refactored the client side SWT (Sage Web Toolkit) framework quite a bit since I wrote this blog post, so we’ll cover how that all works in detail over following blog posts.

Model-View-Controller (MVC) is a common design pattern in software architecture for designing UI programs. People tend to adopt the MVC design pattern, since it enforces some order on the creation of a UI program separating out the various parts into separate classes. This make the program more maintainable, but there are some other goals as well including allowing the UI look to be changed easier and to make the UI form easier to test.

In the diagram above, the dashed lines represent actions that happen directly from events, either from a user interaction with a control on the View or via something changing in the model perhaps due to receiving an SData data source event. Everyone draws this diagram a bit differently and no one really ever likes how data binding fits into this framework. But data binding is just too useful to do away with, so often the model is representing excluding any data binding. In some cases the model doesn’t generate events, but for us it does since changes in one part of the screen will generate changes for another through the model. So in a way this is the Sage 300 MVC design pattern.

We’ve adopted the MVC design pattern for all the new Sage 300 ERP Web Based UIs. We use the same design pattern for the main form as well as for popup forms. Within Sage 300 this gets a bit confusing since we use the name “View” for our business logic. However in this discussion a “View” has nothing to do with business logic and instead represents the actual visual part of the form with all its controls and such. The Model represents the Business Logic and is the part that communicates with the real Sage 300 Business Logic (in other discussions called Views).

There are quite a few other design patterns that are variations on this such as MPP, and several others. Sometimes the differences are subtle, but in our case we find it more convenient to stick with call it MVC model and frame things in regard to this. In a way our application programmers really program in a more MPP model, since all the direct communication between the View and Model is really handled by the SWT framework as part of our data binding support.

One of the key goals of these design patterns is to move as much code as possible away from the actual user interaction part. This is because testing user interaction is hard to unit test and requires specialized tools like Selenium to simulate real users pressing buttons and entering text. Anything that doesn’t directly interact with a user can be unit tested using regular tools like JUnit. Tests using JUnit are easier to include as part of the build process and so catch errors much quicker. So this approach allows much more of our code to be unit tested and facilitates test driven development.

Sage 300 View

Our View part of MVC is the declarative layout. This is the XML file that defines the form layout. This file defines all the controls, their layout and their CSS styles. We also provide a WidgetHelper class as part of SWT to simplify interacting with the controls on the form. The WidgetHelper class isolates the Controller code from having to check for controls being null (say because they were removed due to customization) and other details.

By keeping the View isolated from the other code, it makes it easier to replace the declarative layout either due to customization or due to special needs, like providing an iPhone version of a layout that is optimized for touch and small screen size.

Within our framework we have the ability for the View to create and define “Actions”. These actions can then be bound to the controls in the declarative layout. These actions are then the events of the View. This allows a greater level of customizability, since people can change the action that is called in the declarative layout to their own action to customize the behavior. Then the Action is the event that the controller receives rather than an underlying control event. So rather than the controller being notified of a button click, it’s notified of say a “save” action. This then further separates the details of the View from the controller (i.e. it isn’t tied to an exact specific control event).

Sage 300 Model

The Model manages the behavior and data of the application. It answers questions about its state and responds to requests to change its state. The Model encompasses the business logic, its state and the SData data source (including events). The model doesn’t just send data source events to the controller, rather it translates them into something higher level that is meaningful for the controller of this process.

We provide a couple of base Model classes in SWT that you can extend for your own applications needs. These base classes include support for interacting with the SData data sources and setting up the eventing mechanism.

Sage 300 Controller

The Controller responds to user and model events, informing the other to change as appropriate. Basically the Controller orchestrates the whole process and is usually responsible for creating and initializing everything as well as tearing down and releasing everything.

If things are written correctly then you can almost think of the controller as a workflow manager, which is controlling the workflow as things happen in the Model and the View.


When you press the “Ship All” button on the Order Entry screen, you would think it simply issues an SData service request to the server to call the Order Entry header view to process the request. However there is a bit of error checking first and then afterwards things need to be updated accordingly. Still this is about the simplest example there is, and can serve as a simple example of the MVC process.

Below is a UML process diagram showing, in gory detail, the whole “Ship All” process. The heading show the various components involved and whether they are part of the View, Model or Controller.

Keep in mind that many of the steps here are automatically handled either by GWT or SWT, so most of the application programming is in the ship-all controller to orchestrate the process between the View (User Interface Form) and the Model (wrapping the Order Entry SData feeds).


You might think that a screen only really requires one model or one controller. This is the case for many popup screen and many simple UI forms. However for larger screens like the main Order Entry screen this would lead to an extremely big model and controller. So conceptually we break down the main OE controller into many smaller controllers and similarly even though we have the main Order model, there can be many sub-models under this. Basically we don’t want our classes and objects getting too big. We want them all to stay maintainable and testable. We want to be able to work on one without breaking unrelated functionality elsewhere.

We also don’t want to create too big an MVC framework so that it requires a lot of boiler plate code to create these; we want the creation simple and if a part isn’t required in a certain situation, then it can be safely omitted entirely.


Hopefully this gives an idea of how we are using MVC as the model for our new Sage 300 ERP Web UIs. Perhaps this discussion is a bit theoretical at this point, but hopefully this will set the stage for looking at the mechanic of this in detail over some following posts and starting to look at some code.

Written by smist08

October 29, 2011 at 5:23 pm

Sage 300 Server Side Programming Examples

with 4 comments


Now that the official rebranding announcement has happened, I’m going to start using the new branding name of Sage 300 ERP. Perhaps now and then referring to it as the product formerly known as Sage ERP Accpac or perhaps formerly known as CA-ACCPAC/2000.

Last week I blogged on the structure of our new Sage 300 server side programming framework. This week I’ll present a few examples of the sorts of things you can do. Of course from this framework you can do anything you can do from Java as well as have easy full access to all the Sage 300 Business Logic, this gives you a lot of power to program what you need.

The Java classes in this article have all been shortened to keep the length of this posting down and so that the main points don’t get lost in a lot of comments or other incidental functionality. So all the imports’, most comments and then all methods which don’t represent the main point, have been removed.

Fun with Fields

You don’t actually need to create a new class for every field you want to adapt the behavior of. You can actually use the same field class for all the fields, or a group of fields. So for example in the Order Header, there are many fields to do with source currency where we need to set the correct number of decimal places and similarly there are many fields to do with PJC, many fields to do with tax reporting currency, etc. So in the Order Header view we can get all these processed by one class for all the header fields (except for one):

   protected SDataViewField createField(int index, int id, String name)
      switch (id)
            return new PJCProjectInvoicingField(this, index, id, name);
         default: // All the fields except for the one above.
            return new OEOrdersHeaderViewField(this, index, id, name);

We set one special field to do with PJC and then we have the OEOrdersHeaderViewField class extend all other fields in the Order Header View.

public class OEOrdersHeaderViewField extends SDataViewField
   private static final List<Integer> MONEY_FIELDS_SOURCE_CURRENCY;
      MONEY_FIELDS_SOURCE_CURRENCY = new ArrayList<Integer>();
   private static final List<Integer> MONEY_FIELDS_TAXREPORTING_CURRENCY;
                  MONEY_FIELDS_TAXREPORTING_CURRENCY = new ArrayList<Integer>();
   private static final List<Integer> NON_FRACTION_FIELDS;
      NON_FRACTION_FIELDS = new ArrayList<Integer>();
   private static Short               OIP_PROCESSCMD_INSERT_OF = 1;
   protected OEOrdersHeaderViewField(OEOrdersHeaderView view,
       int index, int id, String name)
      super(view, index, id, name);
   public boolean isEditable()
      switch (getID())
         case OEORDH.IDX_TEMPLATE:
            return !super.getView().getExists();
            return super.isEditable();
   public int getDecimalPlaces()
      int decimals;
      Integer fieldIdx = getID();
      if (MONEY_FIELDS_SOURCE_CURRENCY.contains(fieldIdx))
         final String currencyCode = (String)getView().get(OEORDH.IDX_ORSOURCURR);
         decimals = getView().getParent().getCurrency(currencyCode).getDecimals();
      else if (MONEY_FIELDS_TAXREPORTING_CURRENCY.contains(fieldIdx))
         final String currencyCode = (String)getView().get(OEORDH.IDX_OTRCURRNCY);
         decimals = getView().getParent().getCurrency(currencyCode).getDecimals();
      else if (NON_FRACTION_FIELDS.contains(fieldIdx))
         decimals = 0;
         decimals = super.getDecimalPlaces();
      return decimals;
   public int setValue(Object newValue, boolean verify)
      Integer fieldIdx = getID();
      final View view = getView();
      int retVal = super.setValue(newValue, verify);
      if (retVal == 0)
         if ((fieldIdx == OEORDH.IDX_CUSTOMER) || (fieldIdx == OEORDH.IDX_SHIPTO))
               //Set optional field defaults coming from the item or miscellaneous charge.
            catch (ViewException ex)
               throw new RuntimeException(
                  "OEORDH returned from a view call with error code " +
                  ex.getReason(), ex);
         else if ((fieldIdx == OEORDH.IDX_INVDISCPER) || (fieldIdx == OEORDH.IDX_INVDISCAMT)
               || (fieldIdx == OEORDH.IDX_IDISONMISC))
            retVal = view.getFields().get(OEORDH.IDX_RECALCTAX).setValue(true, false);
            if (retVal == 0)
               retVal = view.getFields().get(OEORDH.IDX_GOCALCTAX).setValue(true, false);
               if (retVal == 0)
                  retVal = view.process();
      return retVal;

If you are familiar with the Sage 300 business logic, you know that all monetary fields have 3 decimals. This then holds all money amounts since the most decimals a currency has is 3 (like the Bahraini Dinar). There are other currencies with 0 decimals (like the Japanese Yen), and others with 2 (like the Canadian Dollar). The only currency, that I know of, with 1 decimal is the Macanese Pataca. So in the database we store 3 decimals to hold any of these. However in the UI you don’t want to allow people to enter the wrong number of decimal places, but the View always tells you 3. So typically the UI needs to know which the corresponding currency field is, look up its number of decimals and then set the numeric edit control to only allow these. In the Web world we certainly don’t want to do a whole lot of server calls to look up currency information, so we override the number of decimals in the server logic and set it to the number of decimals for the corresponding currency code. This is the main purpose of the listed class. There is a getDecimalPlaces() method that we override and in which we calculate the correct decimal places for the field and return that. If we actually put too many decimals to a view field for the given source currency code, it will truncate them to keep things correct, but this would be very misleading to the user.

We also override the isEditable() method which lets us adjust the View field attributes. Hopefully the View tells correctly when fields are editable or not. However there are exceptions and this gives us a place to fix these without changing the View. In this case to make the template code field disabled after the order is first posted.

We then override the setValue() method. We could override this method to change or massage the data before it goes to the View. However, in this case we put the value to the View and if that is successful, for any fields that affect the optional fields, like customer and ship-to, we then re-initialized the optional fields.

Fun with View Operations

The next example is of the OEOrdersQuoteView which handles processing for quotes. I included this example, because it does lots of other View processing, so you can see how to work with Views inside this framework. These View calls are accessed via our SAJava API layer. There is complete JavaDoc for this API included with the Sage 300 SDK. Notice that using this layer is very similar to both the AccpacCOMAPI and the Accpac .Net Interface.

public class OEOrdersQuoteView extends SDataView
   private SDataView           oeordq              = null;
   private View                oeordh              = null;
   private SDataView           header              = null;
   private List<String>        selectedQ           = null;
   private List<Short>         selectedL           = null;
   private String              customer            = null;
   private final SDataViewSet  viewSet;
   private boolean             inCreateOrder       = false;
   private boolean             bHasJob             = false;
    * Restore Quotes Table after order creation failed.
   public void restoreQuotesTable()
      oeordq.browse("", true);
      while (oeordq.goNext())

      //re-populate the selected line no list as it has changed after copyQuotes.
      String quote;
      Short lineNo;
      oeordq.browse("", true);
      while (oeordq.goNext())
         quote = (String)oeordq.get(OEORDQ.IDX_QUONUMBER);
         if (selectedQ.contains(quote))
            lineNo = (Short)oeordq.get(OEORDQ.IDX_LINENUM);
    * Copy quotes if there is no record in the quote detail table (OEORDQ)
   public boolean goTop()
      boolean result = super.goTop();
      if (!result)
         inCreateOrder = true;
         if (copyQuotes())
            result = this.goTop();
         return result;
      return result;
    * copyQuotes copy quotes to table OEORDQ
    * @return true indicate that there are quotes available for the customer
    *         false indicate that there is no quote available for the customer
   private boolean copyQuotes()
      if (null == oeordh)
         oeordh = viewSet.getProgram().openView(OEORDH.VIEW, OpenModes.None, 0, OpenDirectives.InstanceOpen);
      header = viewSet.get("OE0520");
      customer = (String)header.get(OEORDH.IDX_CUSTOMER);
      if (customer.isEmpty())
         return false;
      customer = customer.trim();
      boolean exist = false;
         oeordh.set(OEORDH.IDX_CUSTOMER, customer);
      catch (RuntimeException e)
         //Setting customer here always throws exception(error)with existing view behavior.
         //For this case, we just want to set customer to position the current record so the error can safely ignored.
      String filter = "CUSTOMER = \"&\" AND TYPE = 4 AND (COMPLETE = 1 OR COMPLETE = 2)";
      filter = filter.replaceAll("&", customer);
      oeordh.browse(filter, false);
      while (oeordh.goNext())
         this.set(OEORDQ.IDX_QUONUMBER, oeordh.get(OEORDH.IDX_ORDNUMBER));
         exist = true;
      return exist;

Another Calculated Field

Here is a virtual field. This one has no corresponding View field; it implements the whole field itself. Its value is a fairly simple calculation as the sum of two other fields.

public class OEOrderHeaderSumOfDiscountsField extends VirtualFieldAdapter
   private final SDataView     view;
   private static final String OEORDH_DTLDISCTOT           = "DTLDISCTOT";
   private static final String OEORDH_INVDISCAMT           = "INVDISCAMT";
   public OEOrderHeaderSumOfDiscountsField(SDataView view, ResourceVirtualField resourceField)
      super(view, resourceField);
      this.view = view;
   public PropertyType getType()
      return PropertyType.DECIMAL;
   public boolean specifiesFractionDigits()
      return true;
   public int getFractionDigits()
      final String currencyCode = (String)getView().getFields().get(OEORDH.IDX_ORSOURCURR).getValue();
      return view.getParent().getCurrency(currencyCode).getDecimals();
   public Object getValue()
      BigDecimal detailDiscount = (BigDecimal)view.getFields().get(OEORDH_DTLDISCTOT).getValue();
      BigDecimal orderDiscount = (BigDecimal)view.getFields().get(OEORDH_INVDISCAMT).getValue();
      return detailDiscount.add(orderDiscount);
   public boolean setValue(Object value)
      return true; //Do nothing
   public boolean isReadOnly()
      return true;

Notice that this field sets the number of decimals, field type and controls the attributes like whether its read-only (which it always is).


This blog posting provides a few examples of doing server side programming in the Web version of Sage 300 ERP. Besides being in Java, most of the concepts and type of activities should be fairly familiar to experienced Sage 300 developers.

Written by smist08

October 22, 2011 at 4:18 pm

Posted in sage 300

Tagged with , ,

The Accpac Server Side Programming Model

with 7 comments


I previously blogged on server side programming for the new Web version of Sage ERP Accpac here. However we’ve improved how you do this quite a bit making it much easier to interact with the Accpac Views (Business Logic) and to hook in your logic where you need it.

We are currently in the process of moving all our VB6 based UI forms to the web. I described this process here. The VB UI Forms talked directly to our Accpac Business Logic using our View API via the Accpac COM API. This interface worked well since it gave complete control over the Business Logic for both our UI Forms as well as for ISV’s integrating to Accpac. However this API doesn’t work well in the Web, it is too fine grained and results in far too much communication between the client and server. Over the Internet this then becomes quite slow.

To address the problem with the Accpac View API, we have adopted SData as our exclusive client/server communications protocol for our Web version. This protocol is a true REST based modern Web Services interface that will have the same role that the Accpac COM API had in the VB world. I’ve blogged on SData  a few times: here, here, here and here. Basically we want to limit ourselves to at most one server call per user interaction. The VB UIs would often make quite a few. So what do we do with all the VB logic that does all this Business Logic communication?

Basically if you find a number of Accpac COM API calls in a VB program, then this code needs to be moved to the server and programmed there. Then the client should be setup so it can call this code either as a function using an SData service operation, or accessed via a virtual field (calculated field) in the SData feed. JavaScript and AJAX actually make it hard to make several calls in a row to the server, since due to the asynchronous nature, you have to send a request, set a callback and then in that callback make the next call and so on. Chaining asynchronous calls in a row this way is rather tedious and error prone programming. In a way, this is a good thing since it discourages making more than one call at a time, which is what you want for maximum efficiency.

You might ask why we don’t just move all this code into the Views? The main reason for Sage is that we are maintaining both the Web UIs and VB UIs in parallel for quite some time. As a result of this we don’t want to have to change or re-test the VB UIs, so we don’t want to change the Views at all. Plus a lot of this logic that exists in the VB UIs, really doesn’t belong in the Views, but it doesn’t belong in the Web browser either. Hence we have this solution. It’s also nice because it moves all new programming out of C into the nice object oriented world of Java.

Also remember that your SData feeds aren’t only used by your UI forms. They are also a general programming interface for any third party ISV or integrator to use to tie into Accpac. So when designing your SData feeds keep third parties integrating via SData in mind as well as your Web UI programmers.

Server Side Architecture

There are basically a couple of ways to structure this. You can return one or more virtual fields which are calculated on the server by doing regular Accpac View calls and other programming logic, these are good if the data is always relevant, or relevant when the other fields are relevant. The other way is to access the code via an SData service call. An SData service call is basically a custom call to the server to do some processing, you can send in any number of parameters you may require and you can receive back a number of fields with the results. You can add service calls to regular data SData feeds, then these operate in a similar manner to how Views like OEORDH behave. The OEORDH view is a data view on the order header database tables, but additionally it can do processing like calculate taxes which is invoked by its viewProcess entry point. So if you have a data feed you can add some virtual fields and/or you can add some service processing calls. The nice things about these processing calls is that they can use any existing view fields and view state automatically. Additionally you can also create standalone SData service calls which have no data feed associated with them.

Inside our SData processing program, at one end SData requests come in. These are translated into View operations and then at the other end, the Views are called through our Java View API (SAJava). SData feeds correspond to a whole set of header/detail Views, so each SData request could potentially be operating on a whole set of Accpac Views via the normal header/detail View protocols.

So we have the SData request come and this is processed by the ResourceKind. The ResourceKind operates on an SDataViewSet, think of this as all the header/detail views composed together. In the SDataViewSet are a number of SDataViews and in each SDataView there are a whole bunch of SDataViewFields. Then the SDataView and SDataViewField talk to the Java class versions of our View API in the SAJava layer to do the real work. Basically we allow you to either extend or create any of the classes in the middle and give an organized way to have your classes used either in conjunction with our classes or instead of our classes. Since we are in the object oriented world of Java, when you extend one of our classes you only need to do the bare minimum work required to accomplish your goal, there is no extra overhead like we had in the C and VB world with our template approach to things. Typically you would create brand new SDataViewFields with no associated ViewField for virtual or calculated fields. If you want to just slightly change the behavior of the existing ViewField then you can extend that existing one. Similarly you can extend the SDataView class for a View to change or enhance the behavior of the base View. Generally this is much easier than sub-classing Views in C.

Running Your Classes

How do we get our classes to be run in this framework from the SData service running under Tomcat? It all starts with the classmap/resourcemap files. All the SData feeds for an application are defined in its classmap.xml file which is located in C:\”Program Files (x86)\Common Files\Sage\Sage Accpac\Tomcat6\portal\sageERP\application” where application is something like oe61a. The classmap.xml file provides the name of the SData feed, an additional configuration file for the feed called a resoucemap.xml file and the name of the class to run for this feed. This class is then loaded dynamically by our SData service when this feed is first accessed. Since it is Tomcat that loads the class for us, it must be located somewhere in the classpath for Tomcat which is typically the Tomcat\lib folder (usually in a jar file containing all your classes).

<?xml version="1.0" encoding="UTF-8"?>
            <contract name="accpac">
                     <resource name="oeorders"
                             <parameter name="ResourceMapFile" value="OEOrderViewMapping.xml" />

This is then your resourceKind. This will be a Java class that extends one of our standard resourceKinds in our SDK (such as ViewResourceKind). From here you can then load other classes to act as your SDataViewSets, SDataViews and SDataFields. So for instance to provide your own SDataViewSet you would override the createSDataViewSet method:

    * provides a {@link OEOrdersSDataViewSet} subclass of the required class as
    * described by the superclass documentation:
    * <p>
    * {@inheritDoc}
   protected SDataViewSet createSDataViewSet(Program program, Resource resource)
      if (null == oeordhQ)
         oeordhQ = getResourceContext().getAccpacProgram().openView(OEORDH.VIEW,
               OpenModes.None, 0, OpenDirectives.InstanceOpen);
      return new OEOrdersSDataViewSet(program, resource, oeordhQ);

Then in the SDataViewSet (OEOrdersSDataViewSet in this case), you can override any individual SDataView components you like:

    * provides application-specific subclasses of the {@link SDataView} class as
    * described by the superclass documentation:
    * <p>
    * {@inheritDoc}
   protected SDataView createView(final String rid, final Resource resource)
      switch (A4wapi.utlObjStrToID(rid))
         case OEORDH.VIEWNUMBER:
            return new OEOrdersHeaderView(this, rid, resource);
         case OEORDD.VIEWNUMBER:
            return new OEOrdersDetailView(this, rid, resource);
            return new OEOrdersKittingDetailView(this, rid, resource);
            return new OEOrdersBOMDetailView(this, rid, resource);
            return new OEOrdersDetailSerialView(this, rid, resource);
         case OEORDQ.VIEWNUMBER:
            return new OEOrdersQuoteView(this, rid, resource, oeordhQ);
            return super.createView(rid, resource);

Similarly from the SDataView you can create all your virtual SDataViewFields:

   public synchronized VirtualField createVirtualField(ResourceVirtualField field)
      if (PMConstants.EntryDetailPropertyNames.CALC_PJC_RESOURCE_DESC.equals(field.getName()))
         pjcResourceDescriptionAdapter = new PJCResourceDescriptionAdapter(getSet().getProgram(),
                this, field);
         return pjcResourceDescriptionAdapter;
      else if (PMConstants.EntryDetailPropertyNames.UFMTCONTNO.equals(field.getName()))
         //We have to manually calculated the unformatted contract number
         // - because the UFMTCONTNO in the Order
         //Details view only gets calculated by the view when the Contract field is PUT
         // - not when you are browsing through the details.
         return new PJCUnformattedContractAdapter(getSet().getProgram(), this, field);
      else if (VIRTUALFLD_ITEMNO_MISCCHARGE.equals(field.getName()))
         return new CalcItemNoMiscCharge(this, field);
      else if (CALC_COMPLETE.equals(field.getName()))
         return new OEOrdersDetailCompleteVirtualField(this, field);
         return super.createVirtualField(field);

For any virtual fields that you add, you also need to add then to the <virtualFields> section of the resourceMap.xml file for the SData feed. Otherwise they won’t be included in the feed.

SDataView extends the SAJava View object which is a complete rendition of the View API as a Java class. This means you have full use of that View automatically. Plus you can then open and use any other Views you wish, plus use any other methods in the SAJava interface. Plus use any other Java programming libraries or facilities you like.

This blog posting is already getting quite long and all we’ve done so far is show how to setup the framework so you can start to do your work. I planned to include a number of examples of some things you can do here, but I think I’ll leave that for another posting so I can give a few more varieties of examples.


This server framework is intended to allow you a great deal of power to extend and customize the Accpac Business Logic as it is fed into SData feeds. It gives you very fine grained control over the existing Business Logic allowing you to change and extend the behavior. The previous VB UIs were architected using a standard three-tier client/server model; however, on the Web, much of the previous VB UI programming moves to the server. We don’t want to put this logic in the Views since it really is UI logic and not true business logic, so now we have the UI layer split between the server and client and then the Business Logic and Database Layers the same as before.

In the Web world, now that the Browsers have become quite powerful rich client environments, there is a lot of debate as to how much logic should run in the Browser and how much on the server. Generally we’ve kept quite a bit of UI logic in the Browser, but we move anything to the server that would require more than one round trip to the server to do the job.

Written by smist08

October 15, 2011 at 9:10 pm

Tables and Data Flow of the Accpac Accounts Payable Module

with 10 comments

This blog posting will be looking at the structure of some of the parts of the Accounts Payable 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 78 tables in A/P, 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 A/P can be categorized as:

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

Accounts Payable controls and tracks paying all a company’s vendors. The Purchase Order module will feed documents into A/P when vendors need to be paid. A/P will then feed all monetary transactions into G/L for recording. A/P also interacts closely with Bank services for printing checks and handling the whole check printing/reconciliation cycle.


The Setup Tables are:


A/P has 4 options tables. The reason is to separate out the functionality to reduce contention on these files. This way updating the various invoices and other document numbers doesn’t affect the other tables.

APP01 (AP0001): Company Options.

APP02 (AP0002): Invoicing and Numbering.

APP03 (AP0003): Payment and Aging.

APP04 (AP0004): Integration.

Other Setup Tables

APCLX (AP0007): 1099 Class Codes.

APCCS (AP0013): 1099/CPRS Amounts.

APRTA (AP0012): Terms.

APRTB  (AP0011): Terms Payment Schedules.

APSLH (AP0035): Payment Selection Codes. Selection Criteria Header.

APSLD (AP0036): Selection Code Details. Selection Criteria Details.

APSLHO (AP0411): Selection Criteria Header Optional Fields.

APRAS (AP0006): Account Sets.

APRDC (AP0005): Distribution Codes.

APDSH (AP0009): Distribution Sets Headers.

APDSD (AP0008): Distribution Set Details.

APPTP (AP0010): Payment Codes.

APMSG (AP0120): E-mail Messages.

APGLREF (AP0121): G/L Reference Integration.

APOFH (AP0501): Optional Field Locations

APOFD (AP0500): Optional Fields.

Master Data

The main master data file for A/P is the Vendors. Then secondarily we have Vendor Groups. I included the open documents under vendors since they are details, even though they are more a part of posting.

APVGR (AP0016): Vendor Groups.

APVGRO (AP0408): Vendor Group Optional Fields Values.

APVGS (AP0017): Vendor Group Statistics.

APVEN (AP0015):  Vendors.

APVENO (AP0407): Vendor Optional Field Values.

APVCM (AP0014): Vendor Comments.

APVSM (AP0019): Vendor Statistics.

APVNR (AP0018): Remit To Locations.

APVNRO (AP0409): Remit-To Location Optional Fields.

APOBL (AP0025): Open Documents (updated only by postings).

APOBS (AP0026): Open Payment Schedules.

APOBP (AP0027): Document Payments.

Data Entry

Generally data flows into A/P starting as an Invoice. Whether it is entered in A/P or fed in from another application like P/O. Then as the invoice is paid, payment batches record this and mark payments against the Invoices.

Invoice Entry

APIBC (AP0020): Invoice Batch Control.

APIBH (AP0021): Invoices Headers.

APIBD (AP0022): Invoice Details.

APIBT (AP0024): Invoice Detail Comments.

APIBDO (AP0401): Invoice Detail Optional Fields.

APIBS (AP0023): Invoice Payment Schedules.

APIBHO (AP0402): Invoice Optional Fields.

Payment and Adjustment Entry

Both adjustments and payments share the same set of database tables. In the APBTA Batch record the first field is PAYMTYPE which controls whether the batch is payment or adjustment. It is AD for Adjustment and PY for Payment.

APBTA (AP0030):  Payment and Adjustment Batches.

APTCR (AP0031): Payments/Adjustments

APTCP (AP0033): Applied Payments.

APTCU (AP0034): Adjustment G/L Distributions.

APTCN (AP0032): Miscellaneous Payments.

APTCRO (AP0406): Payment/Adjustment Optional Fields.

APPOOP (AP0048): Populates Payment List.

APCTRL (AP0057): Payment Control.

APSYGN (AP0056): Generate Payments Batch.

Data Processing

Posting SuperViews

Batches are posted entry by entry. All the audit files are added to while posting and all statistics are updated along with all the various posted document tables.

APIVPT (AP0039): Superview to Post invoices.

APPYPT (AP0040): Superview to Post payments/adjustments

APTRK (AP0037): Payment GL Transactions.

Posting Journal

APPJS (AP0512): Posting Journals.

APPJH (AP0511): Posting Journal Headers.

APPJHO (AP0514): Posting Journal Header Optional Fields.

APPJD (AP0510): Posting Journal Details.

APPJDO (AP0513): Posting Journal Detail Optional Fields.

APPTER (AP0038): Posting Errors Messages.

Periodic Processing

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 year end or currency revaluation are quite major operations in their own right. Some of these include:


APPREN (AP0049): Fully Paid Docs and Journals.

APPGCM (AP0050): Vendor comments.

APPGST (AP0051): Stats and 1099/CPRS amts.

APBCTU (AP0059): Deleted and posted batches.

APPGIA (AP0052): Delete inactive records.

Year End and Revaluation

APYREN (AP0044): Year End.

APUNGL (AP0041): Revaluation.

APRVL (AP0063): Revaluation Details.

APRVLO (AP0410): Revaluation Optional Fields.


The whole process of printing checks is fairly complicated. To print checks, A/P writes the check information into the BKCHK table in Bank Services. Then it invokes the BK5000 UI to actually drive the check printing. The process works like this so it can be shared by other check printing applications like U.S. and Canadian Payroll. BKCHK doesn’t contain all the check information, only the main header information then the A/P Check form joins back to the A/P tables to get the full set of information the check and advice forms.

APCHKS (AP0058): Print Checks.

APADV (AP0060): Payment/Check Advices.

APBPPU (AP0055): Update Print Status.

Statistics and Aging

APSTAT (AP0047): Update Statistics.

APAGE (AP0043): Age Documents.

Create G/L Batch

APGLTR (AP0042): Create GL batch.

Data Integrity

APINTCK (AP0045): Integrity Checker.


APINIT (AP0100): Activation.

Drill Down

APDLDN (AP0062): Drill Down. Called from G/L to allow G/L to drill down into A/P source documents.


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


Written by smist08

October 8, 2011 at 5:03 pm