Stephen Smith's Blog

Musings on Machine Learning…

Archive for July 2011

A Quick Tour of Sage ERP Accpac Order Entry 6.1A

with 5 comments

The initial release of Sage ERP Accpac 6.1A will include all the Web Screens for the Order Entry module. We started showing these Web Screens publicly at the North American Sage Summit conference which I blogged on here. We had a number of sessions where we ran through the Order Entry process, entering an order in SageCRM using our Quote to Order features, then bringing that order up in the new Web Screen, charging a credit card using Sage Exchange all run from  a Web Browser. We got quite a good reception to what we were showing. We received lots of suggestions, many of which will be incorporated in the final product.

At Summit, we had a usability lab where we assigned partners and customers tasks in Order Entry and then observed how well they did completing these tasks. Out of this testing we will be making a number of adjustments to the screens shown below. Things like moving the disclosure panels for Customer Info, Custom Fields and Sale Split up above the table (right now they are below and don’t show on the screen shots below). Move the Ship-via Code out of the Customer disclosure panel and into the regular header fields. Users had a bit of trouble because Tabbing isn’t quite working and if you hit F5 the browser refreshed, but these problems will be fixed by release. Generally the Users we tested at Summit were quite good and we set a record in the percentage of tasks completed.

All the screen shots in this article are from the July 26, 2011 build of Accpac 6.1A. The product is rapidly evolving as we finish the functionality and refine the design.  We make extensive user of Cascading Style Sheets (CSS) that allow us to change the product quite dramatically in a simple manner.

All the screen shots were made running in the Google Chrome browser. Mostly because this is my favorite browser, but also to show that we now support more than IE. The main goal of this multi-browser support is so we can run on tablets like the iPad along with Macs as well as the regular Windows PCs we’ve always supported. I like the Chrome browser because it’s very fast and seems to display web pages the best.

The first screen shot shows the new Order Entry screen running in the Sage ERP Accpac Portal. For regular Accpac users this screen should still look familiar, but updated to a more modern appearance. This is the screen and notebook tab where you would do most of your data entry. Here you would enter all the detail lines that make up the order.

Once you have entered all your detail lines, then you would go on to possibly confirm the sales tax information from the Taxes tab. And then go to the Totals tab to confirm the order totals and possibly give a discount as shown in the screen shot below:

Generally as a mid-market ERP product there are quite a lot of fields in Accpac’s various records. For instance in the Order Detail record which is displayed in the table, potentially a user could have to do quite a bit of horizontal scrolling to access all the fields. Plus we now have the detail optional fields in line in the table rather than in a popup resulting in even more columns. Of course you can customize your table to set the field widths to anything you wish and from the View menu on the upper right corner of the table you can re-arrange the fields and hide fields you don’t use. However Accpac has always had the concept of a “Zoom” form where you can get a form view of all the fields in the table. In fact the zoom form gives you access to a bit more information than is available directly in the table. With 6.1A and the Web UIs we have preserved that and you can bring up a form or zoom view of the table as show in the following screen shot:

As we’ve designed the new Order Entry screens we’ve tried to keep the information you need as handy as possible without completely cluttering up the screen. We’ve used a number of techniques to accomplish this including notebook tabs, disclosure panels and popup forms. The popup forms can be run from buttons or from hyperlinks. Below is a screen shot where the user clicked on the quantity available link to get a popup showing the quantity available by location.


We are very excited to be finally rolling out Web Based Accounting Applications. We’re hoping that only requiring an URL to run will reduce TCO for all our users. We are hoping that the new improved designs will make learning and using the product much easier.

Written by smist08

July 30, 2011 at 11:04 pm

Posted in sage 300

Tagged with , ,

Tables and Data Flow of the Accpac Order Entry Module

with 14 comments

This blog posting will be looking at the structure of some of the parts of the Order Entry (O/E) 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 94 tables in O/E, 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 O/E can be categorized as:

  • Setup
  • Data Entry
  • Processing
  • Periodic Processing

Order Entry is one of the main entry points for data into the Accpac system. Notice that unlike other modules, O/E has no master data files. Typically Order Entry will have the most users, often with hundreds of people entering orders at one time. There are multiple sources of orders including the Quote to Orders screen inside SageCRM, the main Accpac Order Entry screen and many ISV solutions (such as Point of Sale retail solutions or Web Stores) that feed transactions into Order Entry. Documents from Order Entry then flow into A/R, inventory is updated in I/C and all financial numbers end up in G/L. Below is a diagram showing this flow:


The Setup Tables are:

OEOPT (OE0480): OE Options. Various global options for the O/E module.

OEPLAT (OE0540): Templates.

OEMISC (OE0440): Miscellaneous Charges.

OEMISCT (OE0460): Misc Charge Taxes.

OEMISCO (OE0450): Misc Charge Optional Fields.

OEVIA (OE0760): Ship Via Codes.

OEOFH (OE0475): Optional Field Locations.

OEOFD (OE0470): Optional Fields.

OEGLREF (OE0272): G/L Reference Integration.

OEMSG (OE0465): E-mail Messages.

Besides the standard tables that each application has, O/E has very few setup tables. However beware that O/E uses the setup tables in A/R and I/C extensively so look to these as well when considering what affects O/E.

Data Entry

Data Entry is the largest part of O/E. There are many tables involved in just entering an order. Then you have shipments, invoices, and credit/debit notes. Each of these involve tracking serial numbers, lot numbers, kitting information, bill of material information and optional fields. Many of the tables (like OEORDH) are quite large records with many fields.

All the data entry tables rely heavily on header/detail relationships. Each level of indentation indicates that the indented level is a detail of the less indented level. For more on header detail relationships check out this. The reason the Order Details table has sub-details is that say you order a widget with quantity 10 which has serial numbers, then the system needs to record the 10 serial numbers in this sub-detail. Similarly if the widget is subject to Lot Tracking then there may be several Lots that the 10 items come from which needs to be tracked.


OEORDH (OE0520): Order Headers. There is one of these for each order. Note that this is one View that is spread over two physical tables: OEORDH and OEORDH1. Each OEORDH record has a matching OEORDH1 record, logically these combine to form one large record.

OEORDD (OE0500): Order Details. There is one of these for each detail line in an order.

OEORDDO (OE0501): Order Detail Optional Fields

OEORDDB (OE0503): Order BOM Details

OEORDDD (OE0502): Order Kitting Details

OEORDDDS (OE0504): Order Kitting Serial Numbers

OEORDDDL (OE0506): Order Kitting Lot Numbers

OEORDDS (OE0508): Order Detail Serial Numbers.

OEORDDL (OE0507): Order Detail Lot Numbers

OECOINO (OE0180): Order Comments

OETERMO (OE0740): Order Payment Schedules

OEORDQ (OE0526): Order from Quotes

OEORDHO (OE0522): Order Header Optional Fields


Whenever you ship anything whether from the Shipment Entry screen or by entering Quantity Shipped in the Order Entry screen then one of these Shipment documents is created either by copying all the relevant information from the Order Entry document that is being shipped or by creating a new stand-alone shipment that has no matching Order.

OESHIH (OE0692): Shipment Headers. One of these for each Shipment.

OESHID (OE0691): Shipment Details

OESHTD (OE0697): Shipment Day End Details

OESHIDO (OE0702): Shipment Detail Optional Fields

OESHIDB (OE0705): Shipment BOM Details

OESHIDD (OE0703): Shipment Kitting Details

OESHIDDS (OE0706): Shipment Kitting Serial Numbers

OESHTDD (OE0699): Shipment Day End Details of Details

OESHIDDL (OE0707): Shipment Kitting Detail Lot Numbers

OESHIDS (OE0709): Shipment Detail Serial Numbers

OESHTDS (OE0671): Shipment Day End Serial Numbers

OESHIDL (OE0708): Shipment Detail Lot Numbers

OESHTDL (OE0670): Shipment Day End Lot Numbers

OECOINS (OE0190): Comments and Instructions

OESHIR (OE0694): Multiple Orders to Shipment

OETERMS (OE0745): Shipment Payment Schedules

OESHIHO (OE0704): Shipment Header Optional Fields


When you Invoice a shipment then one of these documents is created by copying the relevant information from the shipment document. These can be created from the Invoice screen or by specifying “Create Invoice” in the Order Entry screen (as long as you ship something).

OEINVH (OE0420): Invoice Headers

OEINVD (OE0400): Invoice Details

OEINVDO (OE0401): Invoice Detail Optional Fields

OEINVDB (OE0403): Invoice BOM Details

OEINVDD (OE0402): Invoice Kitting Details

OEINVDDS (OE0404): Invoice Kitting Detail Serial Numbers

OEINVDDL (OE0405): Invoice Kitting Detail Lot Numbers

OEINVDS (OE0407): Invoice Detail Serial Numbers

OEINVDL (OE0406): Invoice Detail Lot Numbers

OECOINI (OE0160): Invoice Comments

OETERMI (OE0720): Invoice Payment Schedules

OEINVR (OE0427): Multiple Shipments to Invoice

OEINVHO (OE0422): Invoice Header Optional Fields

Return / Credit Notes

These are the Credit/Debit note documents that are created and much be applied to invoices.

OECRDH (OE0240): Credit Note Headers

OECRDD (OE0220): Credit Note Details

OECRDDO (OE0221): Credit/Debit Detail Optional Fields

OECRDDB (OE0223): Credit/Debit BOM Details

OECRDDD (OE0222): Credit/Debit Kitting Details

                OECRDDDS (OE0224): Credit/Debit Kitting Detail Serial Numbers

                OECRDDDL (OE0225): Credit/Debit Kitting Detail Lot Numbers

OECRDDS (OE0227): Credit/Debit Detail Serial Numbers

OECRDDL (OE0226): Credit/Debit Detail Lot Numbers

OECOINC (OE0140): Credit Note Comments

OECRDHO (OE0242): Credit/Debit Note Optional Fields

Data Flow

Below is a diagram showing the data flow of data from the O/E entry views through Day End into the various history and statistics files.

Data Processing

Most data process in O/E happens at Day End time, whether this is initiated from I/C Day End Processing or you have the perform day end during posting option set. You can also choose to split the operation and do costing during posting and the rest (like sub-ledger batch generation) during I/C Day End. I/C Day End will call the Day End processing View in each application that subscribes to Day End (including O/E and P/O).

Day End Processing

Invoked by the master day end processing superview, ICDEP.

OEDEND (OE0260): OE Day End Master

The following are called to process each type of document

OEPOSTO (OE0600): OE Order Posting

OEPOSTS (OE0605): OE Shipment Posting

OEPOSTI (OE0580): OE Invoice Posting

OEPOSTC (OE0560): OE Credit Note Posting

Audit tables

OEAUDH (OE0120): Posting Journals

OEAUDD (OE0100): Posting Journal Details

OEAUDDP (OE0110): Posting Journal Detail Optional Fields

OEAUDDD (Oe0105): Posting Journal Detail-Detail

OEAUDHP (OE0123): Posting Journal Header Optional Fields

History tables

OECOMM (OE0200): Commissions

OESHHD (OE0690): Sales History

OESHDT (OE0685): Sales History Details


OESTATS (OE0700): Sales Statistics

Periodic Processing

There are a small set of superviews that perform various periodic processing functions. These Views typically don’t have tables behind them and perform maintenance type operations. There are also processing functions built into the entry Views where you set a number of fields and then call viewProcess to perform the operation. Some of the stand-alone superviews include:

OECH (OE0130): Clear History and Entries

OEINTCK (OE0280): OE Integrity Check

OEDLDN (OE0265): Drill Down (called by AR and IC to drill down to OE documents).


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


Written by smist08

July 23, 2011 at 8:09 pm

Posted in sage 300

Tagged with , , ,

Sage Summit 2011

with 2 comments

This year Sage North America merged its Partner Insights conference into its Customer Summit conference to have one large Summit conference in Washington DC at the Gaylord Convention Center.  The conference ran from July 10 to 15 with the first half being for partners and then with the customers joining on Tuesday. This was the first major conference to feature our new CEO Pascal Houillon who gave the initial keynote address and spent quite a bit of time mingling and chatting with attendees. The event night was the best ever at the Smithsonian Aerospace Museum on the Washington Mall. There were many announcements, demos, town halls, tutorials, labs and presentations. This blog posting looks at a few of the items that I felt were most significant (at least to me).


In Pascal Houillon’s opening keynote, the first big announcement was the product branding announcement. Basically we have been marketing our products as things like Sage ERP MAS, Sage ERP Accpac, Sage Simply Accounting, Sage Peachtree, Sage Act!, Sage Saleslogix, etc. Now we are going to drop all the legacy product names (the MAS, Accpac, Peachtree, Act! part) and now combine all the separate marketing programs into one that will be focused on promoting the “Sage” brand. When partners are pursuing a new leads, often they will be competing with Microsoft, SAP or Oracle. All these companies have a strong brand and the customer will have heard of them. Having to establish who Sage is and what the company does puts you at a disadvantage. The primary goal of this change is to make Sage a well-known and respected company within North America like it is in many other parts of the world. The branding change allows us to combine our separate product marketing resources and launch large scale campaigns across North America that promote the Sage brand and as a consequence all the separate Sage products. Now hopefully when you are pursuing a new customer, they will already know who Sage is, and be willing to enter into a business relationship based on that knowledge. The other thing this does is help with cross-sell. Often if a customer has a Sage ERP product then we would like to sell them a Sage CRM or Sage HR product. But as it stands today the customer thinks they have MAS or Accpac and then selling them Abra or Saleslogix appears like completely unrelated products. We would rather they think they have Sage ERP and adding Sage CRM or Sage HR is just a natural thing to do.

Anyway there is a lot of emotion associated with all the individual Sage product brands, but it seems like here at the conference once people have a chance to think about these changes they see how they can benefit them.

Sage is a Customer Company

Pascal also mentioned that when he asked people about Sage, then if they have heard of Sage then they think we are a technology company. Pascal said he was quite surprised by this. I was also surprised by this, but perhaps for a different reason.

As part of this Pascal unveiled our new slogan: “Making your business life easier”. This represents our true focus on the customer and making their life easier. Then technology is one of many tools that we will bring to bear to accomplish this goal. This is to remind the technology people in the company, like myself, that the technology isn’t an end in itself, it is a means to an end and that we have to always keep that end in mind in everything we do.

Accpac Roadmap

I blogged on the Accpac roadmap here. In the session “Sage ERP Accpac: The Road Ahead” a new roadmap was un-veiled:

You might notice that the roadmap has changed. The marketing spin is that you are getting Order Entry in the web sooner than the old road map. The reality is that the project is taking longer than anticipated and we are switching from an all in one first release, to releasing when the first module is available and then releasing all the other modules as a stream of product updates over the next year. As a consequence of this we are also adding a number of feature enhancements as we go along. Another advantage is that we will be more feedback as we go along so we should have a far stronger product when we’ve completed all the modules, than we would have had with one single large release.

Product Demos

Now that we are starting to see Accpac accounting modules in the web, we can give quite a good demo, entirely running in the browser from front office functions in SageCRM to back office functions running in Accpac to even charging credit cards from the Web based interface to Sage Exchange. I’ll blog about these individual components in more detail in future posts, but here is the cliff notes version.

The demo started in the front office with SageCRM 7.1. Here we showed a number of add-on components that we will be releasing for free on our web site over the coming weeks. Below is a screen shot of our new collections dashboard where you see the aged data for how much is overdue and you see a list of customers with overdue invoices. When you select a given customer in the left grid you get the list of overdue invoices in the right grid. We then showed a number of these add-ons.

Then we went into the Accpac Quote to Order feature with-in SageCRM. Here we showed someone entering an Accpac order from the Accpac web screen embedded in the SageCRM frame (you can do this part with the released 6.0A product).

Then we went to the back office, where an accounting clerk picks up the order that the salesperson entered in SageCRM and performs some edits to the order. Below is a screen shot of the new Order Entry screen:

We then showed a number of the zoom forms, popups and hyperlinks that make up this screen. We pointed out how the screen is modernized and updated but still familiar to long time Accpac users. We ran it in the Chrome browser to emphasis how version 6.1A supports all the major browsers and hence will run on Macs and tablet devices as well as Windows.

Then we entered a pre-payment and pressed the button to charge a credit card. The screen was then re-directed to where the credit card was processed (this link won’t work directly from this blog posting).

The cool things here is that we did front office tasks in SageCRM, back office tasks in Sage ERP Accpac and then processed a credit card transaction with Sage Exchange all in web pages, all from the browser. No Windows desktop programs involved.

Other Notables

Sage is continuing its strategy of releasing connected services. All the Sage products are starting to have cloud versions similar to The Sage Advisor technology that appeared originally in Sage Peachtree will be coming to all products. Frictionless upgrade is a high priority to reduce TCO at upgrade time.


This was a very quick overview of some of the goings on at Sage Summit. I’m sure these themes along with others will be intertwined in all my blog postings over the coming year. Looking forward to Sage Summit 2012 in Nashville next August.

Written by smist08

July 16, 2011 at 1:01 am

Posted in Business, sage 300

Tagged with , , ,

Tables and Data Flow of the Accpac General Ledger Module

with 17 comments

This blog posting will be looking at the structure of some of the parts of the General Ledger 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 60 tables in G/L, 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 G/L can be categorized as:

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

General Ledger is the foundational accounting module in Sage ERP Accpac. All numbers eventually flow into G/L (sometimes by a round-about route). Below is a data flow diagram showing how all the other modules feed G/L. Then G/L has sufficient information to provide a complete audit history of a company as well as all the data to prepare Financial Reports.


The Setup Tables are:

GL01 (GL0005): Options. Various options for the G/L modules plus some contact information.

GLABK (GL0022): Segments. Defines all the account segments. For instance account segment region is 4 characters long. These are then the building blocks to build G/L account structures out of.

GLASV (GL0021): Segment codes. Holds the valid values for segments that are validated.

GLABRX (GL0023): Structure Codes. Builds G/L account structures out of combinations of segments.

GLSRC (GL0002): Source Codes. Defines the G/L source codes including a description.

GLSJN (GL0019): Source Journal Profile. Used to configure the source journals report. Specify which sources you want in a report.

GLRVAL (GL0020): Revaluation Codes. Configuration for performing currency revaluations.

GLGSSEG (GL0052): Segment Permissions. Defines segments a user has access to.

GLGSACC (GL0053): Account Permissions. Defines accounts a user has access to.

GLGSUSR (GL0054): GL Users. Users defined to G/L security.

GLACGRP (GL0055): Account Groups. Define the G/L Account Groups.

GLACHD (GL0057): Rollup Groups. Define the G/L Rollup Groups.

GLADSD (GL0058): Rollup Group Relationships.

Many of these setup tables are used to define the GL account structure. How these are used and interact is laid out in the following diagram:

Master Data

The main master data file is the GL chart of accounts table (GLAMF). This table has several detail tables also described below. The other master file is the GL fiscal sets files (GLAFS) which holds all the fiscal set balances by account, year and period. The fiscal sets are then used in financial reporting.

GLAMF  (GL0001): Chart of Accounts. The main list of all the G/L accounts. With detail views:

GLAIS (GL0004): Account Allocation

GLAVC (GL0012): Account Valid Currencies

GLCAS (GL0107): Sub-ledger Control Accounts

GLAMFO (GL0400): Account optional fields

GLAMFTO (GL0401): Account transaction optional fields

GLAFS (GL0003): Fiscal sets. Summarized fiscal set data, usually used for financial reporting. Strictly speaking this is also a detail of GLAMF, but tends to be used as the header when doing reporting.

Data Entry

All data that flows into G/L is via the set of Views/Tables: GLBCTL, GLJEH, GLJED (and some other composites).  All data from everywhere else in the system feeds into these tables. These are the unposted batches. Data is never written directly into the posted transaction or audit table files. All data goes here first and then posting is controlled by G/L.

GLBCTL (GL0008): GL Batch Control. One of these for each G/L Batch

GLJEH (GL0006): Journal Headers. One of these for each entry in a batch.

GLJED (GL0010): Journal Details. One of these for each detail line in an entry.

GLJEC (GL0007): Journal Comments. Holds the detail comment if there is one.

GLJEDO (GL0402): Journal Detail Optional Fields. Hold the optional fields for each detail line.

Limited validation is performed on these items as they are entered. This is because they are usually posted in their source application and cannot be edited there. Then in G/L data errors can be corrected by someone with sufficient privileges to allow the batch to be posted. Generally posting is the data integrity gate keeper here and not data entry.

Data Flow

Below is a diagram showing the data flow of data from outside of G/L into the G/L data entry views. Then it shows how posting takes that data and distributes it to the audit and other tables.

Data Processing

The GLJPST (GL0030) superview is the Accpac View that performs G/L batch posting. This view processes the specified batches, one entry at a time and posts them to the audit files and the fiscal set records. It posts each entry in a batch as a separate transaction. Some of the Views and Tables involved in posting are:

GLJPST  (GL0030): Batch Posting. Not a table, but the superview that performs the posting function.

Posting Journal Audit

GLPJD  (GL0016): Posting Journal Details.

GLPJC  (GL0017): Posting Journal Comments.

GLPERR (GL0015): Posting Errors.

GLPJDO (GL0404): Posting Journal Optional Fields.                            –

Posting Provisional Posting Audit. Provisional posting lets you provisionally (“temporarily”) post a batch to see what affect it has on Financial Reports or other G/L reports. These provisional posts don’t hit the regular posting audit files, but instead hit a set of provisional posting files. These are cleared the next time posting is run.

GLPPD  (GL0015): Provisional Posting Journal Details.

GLPPDO  (GL0406): Provisional Posting Journal Optional Fields.

GLPPER  (GL0013): Provisional Posting Errors.

Main Posted Transactions:

GLPOST (GL0018): Posted Transactions.

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

GLYRM (GL0024): Period End Maintenance. Clears history and/or detail.

GLINTCK  (GL0034): G/L Integrity checker (called from the Admin Service Integrity Checker).

GLINIT  (GL0106): Activation. Called from the Admin Services Activation program.

GLJAL  (GL0029): Auto Allocation Processor.

GLRVLU (GL0027): Currency Revaluation.

GLYRN (GL0026): Create New Year.

GLCSL  (GL0025): Consolidation of History.


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


Written by smist08

July 3, 2011 at 9:01 pm

Posted in sage 300

Tagged with , ,